Netsoftmate

Category: Uncategorized

  • Apply Patchset Update (PSU) on Oracle Database 11.2.0.4 (NON RAC)

    Overview:

    In this article we performing PSU6 upgrade on Oracle 11.2.0.4 using slient mode on NON RAC.

    Environment Details
    Hostname
    Primary server :–lnx-nsm-prod
    Operating system
    Linux
    Database Environment
    NSMDB



    Software requirement:
    https://support.oracle.com/epmos/common/images/save_ena.pngp6880880_112000_Linux-x86-64.zip Latest OPatch utility
    https://support.oracle.com/epmos/common/images/save_ena.pngp20299013_112040_Linux-x86-64.zip PSU6

    Download Oracle Opatch and PSU from MOS
    Copy downloaded softwares to staging area.
    $ cd /u01/app/oracle/software/
    $ mkdir 11204

    OPactch upgrade
    Before applying PSU we need to have latest version of OPatch utility
    $ cd /u01/app/oracle/software/11204

    $ls -ltr
    -rw-r—–  1 oracle oinstall   71580583 Oct 27 17:11 p20299013_112040_Linux-x86-64.zip

    -rw-r—–  1 oracle oinstall   98352086 Oct 27 17:11 p6880880_112000_Linux-x86-64.zip


    Upgrade OPatch utility enter A when it prompt.

    $unzip p6880880_112000_Linux-x86-64.zip -d /u01/app/oracle/product/11.2.0.4/db/


    Archive:p6880880_112000_Linux-x86-64.zip
    replace /u01/app/oracle/product/11.2.0.4/db/OPatch/opatch.bat? [y]es, [n]o, [A]ll, [N]one, [r]ename: A
    inflating: /u01/app/oracle/product/11.2.0.4/db/OPatch/opatch.bat


    Verify the OPatch version after upgrade


    $cd / u01/app/oracle/product/11.2.0.4/db/OPatch/
    lnx-nsm-prod : ./opatch version
    OPatch Version: 11.2.0.3.15
    OPatch succeeded. 


    Now we have to perform the PSU6 Upgrade.


    PSU6 Upgrade


    Unizip the software and go to 20299013 Directory

    $unizip p20299013_112040_Linux-x86-64.zip
    $cd 20299013
    $ls -ltr
    total 88
    drwxr-xr-x 4 oracle oinstall  4096 Mar  4  2015 17478514
    drwxr-xr-x 4 oracle oinstall  4096 Mar  4  2015 18522509
    drwxrwxr-x 4 oracle oinstall  4096 Mar  4  2015 20299013
    drwxr-xr-x 4 oracle oinstall  4096 Mar  4  2015 19121551
    drwxr-xr-x 4 oracle oinstall  4096 Mar  4  2015 18031668
    drwxrwxr-x 4 oracle oinstall  4096 Mar  4  2015 19769489
    -rw-r–r– 1 oracle oinstall  3068 Mar  4  2015 patchmd.xml
    -rw-r–r– 1 oracle oinstall    21 Mar  4  2015 README.txt
    -rw-rw-r– 1 oracle oinstall 54747 Apr 12  2015 README.html
    Check the opatch version and path is set .

    $export PATH=$PATH:$ORACLE_HOME/OPatch
    $which opatch
    /u01/app/oracle/product/11.2.0.4/db/OPatch/opatch

    lnx-nsm-prod: opatch version
    OPatch Version: 11.2.0.3.15

    OPatch succeeded.

    Make DB and Listener down on the server.

    $ps -ef |grep smon
    oracle   15230 18477  0 10:27 pts/0    00:00:00 grep smon

    $ps -ef |grep tns
    oracle   15230 18477  0 10:27 pts/0    00:00:00 grep smon

    If DB and Listener are ruining.Perform the following step.


    Stop DB

    $sqlplus /  as sysdba
    sql> shutdown immediate.

    Stop Listener

    $lsnrctl stop



    Now updrade the PSU
    $cd /u01/app/oracle/software/11204/20299013
    $opatch apply
    Patching component oracle.rdbms.deconfig, 11.2.0.4.0…
    Composite patch 20299013 successfully applied.
    Log file location: /u01/app/oracle/product/11.2.0.4/db/cfgtoollogs/opatch/opatch2016-10-28_10-28-01AM_1.log

    OPatch succeeded.

    Check the PSU Upgrade.

    $opatch lspatches
    20299013;Database Patch Set Update: 11.2.0.4.6 (20299013)

    OPatch succeeded.

    Conclusion:

    In this article we have learnt how to apply PSU6 on 11.2.0.4 software on NON RAC using oracle response file in silent mode.

  • Oracle 11.2.0.4 installation using Silent Mode on NON RAC

    Overview:

    In this article we performing installaiton of Oracle 11.2.0.4 installation on NON RAC.

    Environment
    Details
    Hostname
    Primary server :–lnx-nsm-prod
    Operating
    system
    Linux
    Database
    Environment
    NSMDB



    Software requirement:
    https://support.oracle.com/epmos/common/images/save_ena.pngp13390677_112040_Linux-x86-64_1of7.zip
    1.3 GB
    https://support.oracle.com/epmos/common/images/save_ena.pngp13390677_112040_Linux-x86-64_2of7.zip
    1.1 GB
    https://support.oracle.com/epmos/common/images/save_ena.pngp13390677_112040_Linux-x86-64_3of7.zip
    1.1 GB

    Download
    Oracle 11.2.0.4 from MOS
    11.2.0.4.0 PATCH SET FOR ORACLE DATABASE SERVER (Patchset)
    Copy
    downloaded Oracle 11.2.0.4 software to staging area.
    http://docs.oracle.com/cd/E11882_01/install.112/e24326/toc.htm
    $ cd /u01/app/oracle/software/
    $ mkdir 11204

    Create oracle user and group
    #/usr/sbin/groupadd
    -g oinstall
    #/usr/sbin/groupadd
    -g dba
    #/usr/sbin/useradd
    -u -g oinstall -G dba oracle
    Create Directories structure and give permissions
    #mkdir -p
    /u01/app/oracle
    #mkdir -p
    /u01/app/oracle/product/11.2.0.4/db
    #mkdir -p
    /u01/app/oracle/product/Agent12c/core/12.1.0.4.0
    #mkdir -p
    /u01/app/oracle/product/Agent12c
    #mkdir -p
    /u01/app/11.2.0.4/grid
    Permission
    on directories :

    #chown -R
    oracle:oinstall /u01
    #chmod -R
    775 /u01
    Login with  oracle user and edit Profile

    #su – oracle
    $vi .bash_profile  –> Update the below in bash profile.
    # .bash_profile
    # Get the aliases and functions
    if [ -f ~/.bashrc ]; then
    . ~/.bashrc
    fi
    # User specific environment and startup programs
    PATH=$PATH:$HOME/bin
    export PATH
    export HISTTIMEFORMAT=’%F %T ‘
    #
    PS1=`hostname`’ {$PWD}: ‘; export PS1
    set -o vi
    export ORACLE_BASE=/u01/app/oracle
    export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4/db
    export AGENT_HOME=$ORACLE_BASE/product/Agent12c/core/12.1.0.4.0
    export AGENT_BASE_DIR=$ORACLE_BASE/product/Agent12c
    Unzip the oracle software 

    $cd /u01/app/oracle/software/11204
    $unzip p13390677_112040_Linux-x86-64_1of7.zip
    $unzipp13390677_112040_Linux-x86-64_2of7.zip
    $unzipp13390677_112040_Linux-x86-64_3of7.zip
    Installing Oracle 11.2.0.4 in slient mode
    $cd /u01/app/oracle/software/11204/database
    Update db_rsp file only
    required fields as below.
    ORACLE_HOSTNAME=lnx-nsm-prod
    INVENTORY_LOCATION=/u01/app/oraInventory
    ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/db
    ORACLE_BASE=/u01/app/oracle
    oracle.install.db.InstallEdition=EE
    oracle.install.db.optionalComponents=oracle.rdbms.partitioning:11.2.0.4.0,oracle.oraolap:11.2.0.4.0,oracle.rdbms.dm:11.2.0.4.0,oracle.rdbms.dv:11.2.0.4.0,oracle.rdbms.lbac:11.2.0.4.0,oracle.rdbms.rat:11.2.0.4.0
    oracle.install.db.DBA_GROUP=dba
    oracle.install.db.OPER_GROUP=dba
    Now RunInstaller
    $cd /u01/app/oracle/software/11204/database
    $./runInstaller
    -ignoreSysPrereqs -force -silent -responseFile     /u01/app/oracle/software/11204/database/db_install_nsm.rsp



    Run the sh file at the
    end of installation
    #/u01/app/oraInventory/orainstRoot.sh
    #/u01/app/oracle/product/11.2.0.4/db/root.sh

    Conclusion:
    In this
    article we have learnt how to install oracle 11.2.0.4 software on NON RAC using oracle     response file in silent mode.  

  • Convert Physical Standby to Snapshot Standby using Data Guard Broker

    Overview:

    This article explain the process of converting Physical Standby which is in Read Only mode to a Snapshot Standby which would be in Read Write mode for the needs of disaster recovery testing.  This is to allow the database in DR site to be accessed by applications and perform tests on how the database could be accessed and application purposes be met during a disaster scenario while the actual primary database is not shutdown completely.

    Environment Details:

    Hostname
    Node1 and Node2
    Operating system
    Linux
    Environment
    Production(orcl01 / orcl02) and Standby(orcldr01/orcldr02)
    Oracle Home
    /u01/app/oracle/product/11.2.0/db

    Prerequisites for converting the database
    • Ensure the FRA is configured.
    SQL> sho parameter recover

    Note:- Snapshot standby need not have Flashback enabled at db level but if it is enabled then FRA would have already been configured
    • Ensure the redo transport parameters for the primary and redo apply for the standby are configured:



    SQL> set lin 999
    SQL> column NAME format a30
    SQL> column VALUE format a84
    SQL> select NAME, VALUE from v$parameter
    where NAME in (‘db_name’, ‘db_unique_name’, ‘control_files’, ‘log_archive_config’,’fal_client’, ‘fal_server’,
    ‘log_archive_dest_1’, ‘log_archive_dest_2’, ‘log_archive_dest_3’, ‘log_archive_dest_4’,
    ‘log_archive_dest_state_1′,’log_archive_dest_state_2’,‘log_archive_dest_state_3’, ‘log_archive_dest_state_4′,’log_file_name_convert’, ‘remote_login_passwordfile’);

    • Ensure the standby redo log files are configured on the primary and standby database.



    SQL> column member format a50
    SQL> SELECT a.group#, a.member, b.bytes/1024/1024 bytes_MB FROM v$logfile a, v$log b WHERE a.group# = b.group# union select a.group#, a.member, b.bytes/1024/1024 bytes_MB from v$logfile a, v$standby_log b  WHERE a.group# = b.group#;

    Checks on the day of planned DR Test
    • Run the following query to pull information about the database on both Primary and standby



    SQL> set lin 175
    SQL> column NAME format a10
    SQL> column LOG_MODE format a10
    SQL> column OPEN_MODE format a10
    SQL> COLUMN CONTROLFILE_TYPE format a16
    SQL> column PROTECTION_MODE format a20
    SQL> column PROTECTION_LEVEL format a20
    SQL> column DATABASE_ROLE format a20
    SQL> column GUARD_STATUS format a12
    SQL> column DATAGUARD_BROKER format a16
    SQL> select NAME,DATABASE_ROLE,LOG_MODE,CONTROLFILE_TYPE, OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,GUARD_STATUS,FORCE_LOGGING,DATAGUARD_BROKER from v$database;
    • Verifying That the Standby Has Received All Redo, you can carry your individual steps.



    On Primary and on standby –> To check if the scn’s are close.
    SQL> set numwidth 20
    SQL> select current_scn from v$database;

    On Standby –> To check if the MRP is running

    SQL> select status,process from v$managed_standby where process like ‘%MRP%’;
    STATUS                                           PROCESS
    ———————————————— ————————————
    APPLYING_LOG                                     MRP0

    On standby  –> to Check if all or near to all logs are applied

    SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

    And to check real time apply On primary / standby –> It should say “MANAGED REAL TIME APPLY” in one of the rows.

    SQL> SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS;

    SQL> SELECT CLIENT_PROCESS,PROCESS,SEQUENCE#,STATUS FROM V$MANAGED_STANDBY;

    Compare the sequence# from primary to standby query.  If the standby is not receiving the current redo, you cannot convert/switchover. Note that if your primary is a RAC, you should see multiple LGWR to RFS connections, one for each primary thread. You must validate that each primary thread is caught up.
    • Check the apply is caught up



    Once determined the redo stream is current, ensure all redo has been applied to standby database.
    On the standby database:

    SQL> SELECT CLIENT_PROCESS,PROCESS,SEQUENCE#,STATUS FROM V$MANAGED_STANDBY;

    Note: if you don’t see MRP0, the apply is not running

    To convert/switchover, you must start the apply service and wait for it to catch up with the current redo stream before starting. If you see the MRP0 line but it has a status of WAIT_FOR_GAP, you cannot convert/switchover until the gap has been resolved.
    • Verify there are no large Gaps



    Identify the current sequence number for each thread on the primary database.

    On the Primary:

    SQL> SELECT THREAD#,SEQUENCE#,STATUS FROM V$LOG;

    Verify the target physical standby database has applied up to, but not including the logs from the primary query. On the standby the following query should be within 1 or 2 of the primary query result.

    On the Standby database:

    SQL> SELECT THREAD#, MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE APPLIED = ‘YES’ AND RESETLOGS_CHANGE# = SELECTRESETLOGS_CHANGE# FROM V$DATABASE_INCARNATION WHERE STATUS =’CURRENT’) GROUP BY THREAD#;

    Data Guard Broker Configuration completeness
    check
    • Ensure Server Parameter File (SPFILE) is used on both Primary and standby database so that
      the broker can persistently reconcile values between broker properties and any
      related initialization parameter values:
    SQL> show parameter spfile;
    • Verify value of the DG_BROKER_START initialization
      parameter is set to TRUE on both primary and standby DB.
       SQL> Show parameter DG_BROKER_START;

     The data
    Guard Monitor (DMON)     – Broker-controlled process is the main Broker
    process that is responsible for coordinating all Broker actions as well as
    maintaining the Broker configuration files. 
    This process is enabled and disabled With DG_BROKER_START. To verify if DMON process running on both Primary and standby:
        
     $ps -ef|grep dmon|grep -v
    grep
    •  Verify DG_BROKER_CONFIG_FILEn initialization parameters
      files exists on shared disk for all databases in the Oracle RAC database
      configuration on Primary and standby: 
       SQL> show parameter
    dg_broker_config_file;
    • Check whether StaticConnectIdentifier is configured on
      ALL NODES
    • To enable
      DGMGRL to restart instances during the course of the switchover / conversion, a
      static service is required. This can be supplied by explicitly setting the
      Broker Instance Property       StaticConnectIdentifier. 
         Below static entry should be on all the nodes as below.
      –Primary 

       Node1

      SID_LIST_LISTENER =
     SID_LIST=
     SID_DESC=
     GLOBAL_DBNAME=orcl_DGMGRL.localdomain.com)
     ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/db)
     SID_NAME=orcl01))
       )

      Node 2

      SID_LIST_LISTENER =
     (SID_LIST=
     (SID_DESC=
     (GLOBAL_DBNAME=orcl_DGMGRL.localdomain.com)
     (ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/db)
     (SID_NAME=orcl02))
     )
      
    – Standby  
       
       Node1
      SID_LIST_LISTENER =
     (SID_LIST=
     (SID_DESC=
     (GLOBAL_DBNAME=orcldr_DGMGRL.localdomain.com)
     (ORACLE_HOME=/u01/app/oracle/product/11.2.0.4 db)
     SID_NAME=orcldr1))
     )

      Node 2

      SID_LIST_LISTENER =
     (SID_LIST=
     (SID_DESC=
     (GLOBAL_DBNAME=orcl01_DGMGRL.localdomain.com)
     (ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/db)
     (SID_NAME=orcldr2))
       )
     **This entry
    makes it possible for the Broker to connect to an idle instance using a remote
    SYSDBA connection and perform the necessary startup
    •    Ensure Broker configuration is enabled and state
      of all members as intended is ONLINE
       $ dgmgrl /
     DGMRL> show configuration
     DGMRL> show database verbose orcl
     DGMRL> show database verbose orcldr
           On the standby, ensure PROPERTY ‘DELAYMINS’=’0’
           Ensure property LogXptMode to be same on both Primary and Standby either set to ‘SYNC’ or
    ‘ASYNC’.
        On the standby and primary, ensure all other
    properties are consisted with Data Guard and Data Guard broker
    •    Verify remote_login_passwordfile is set to ‘EXCLUSIVE’
      on both Primary and Standby:
        sql>show parameter remote_login_passwordfile
       
       Conversion to Snapshot Standby and back to Physical standby using Data Guard Broker

    •   Converting a physical Standby to Snapshot Standby
        Once completed the mandatory & prerequisites checks, connect to the primary database and disable all services so the application cannot establish any sessions to the database.

       On standby to be converted to snapshot mode.

       Tail Broker and Alert Logs (optional) on all instances:
      Locate Broker logs by showing database parameter background_dump_dest:

      SQL> SHOW PARAMETER background_dump_dest;

     NAME                                 TYPE   VALUE
    —————————————————-
    background_dump_dest       string  /u01/admin/diag/rdbms/orcldr/orcldr01/trace
                                                                       

      Tail the broker logs:
       tail -f /u01/admin/diag/rdbms/orcldr/orcldr01/trace/dr*

    •   DGMGRL Convert to snapshot standby: Log onto dgmgrl from primary database server

       On standby database, Issue the convert command:

      % dgmgrl
      DGMGRL> CONNECT SYS/password@standby
      DGMGRL> convert database ‘orcldr’  to snapshot standby;

      DGMGRL for Linux: Version 11.1.0.7.0 – 64bit Production

      Copyright (c) 2000, 2005, Oracle. All rights reserved.

      Welcome to DGMGRL, type “help” for information.
      DGMGRL> 

      DGMGRL> connect sys@orcldr
      Password:
      Connected.

      DGMGRL> convert database ‘orcldr’ to snapshot standby;

    •  Convert back to Physical Standby

    1 Make sure all applications connecting to snapshot standby are down
      and then
      Tail the broker logs:
      %tail -f /u01/admin/diag/rdbms/orcldr/orcldr01/trace/dr*

      Convert to Physical standby:

      % dgmgrl *** log into dgmgrl from primary database server

     DGMGRL> CONNECT SYS/password@standby database
     DGMGRL> CONVERT DATABASE orcldr TO PHYSICAL STANDBY ;

     DGMGRL for Linux: Version 11.1.0.7.0 – 64bit Production  Copyright (c) 2000, 2005, Oracle. All rights reserved.
      Welcome to DGMGRL, type “help” for information.
      DGMGRL> 
      DGMGRL> connect sys@orcldr
      Password:
      Connected.

      DGMGRL> convert database ‘orcldr’ to physical standby;

    •  Ensure Broker configuration is enabled and state of all members as intended is ONLINE:

      $ dgmgrl /
      DGMRL> show configuration
      DGMRL> show database verbose orcl
      DGMRL> show database verbose orcldr
    •  Perform the Physical standby check out
     On Primary:

    Check the alert log of primary database and tail the alert.
    Check the archive error destination

    SQL> col DEST_NAME for a40
    SQL> set lines 200
    SQL> select DEST_ID,DEST_NAME,STATUS,ERROR from v$archive_dest;

    Check the current archive log (sequence)

    SQL> select thread#,max(sequence#) from v$log where status=’CURRENT’ group by thread# order by 1;

    On Standby:

    Check the alert log of standby database and tail the alert.
    Check the current archive log (sequence) applied.

    SQL> select thread#,max(sequence#) from v$archived_log where applied=’YES’ group by thread# order by 1;


    Check the status of MRP

    $ps -ef |grep mrp

    SQL> select PROCESS,THREAD#,SEQUENCE#,STATUS from v$managed_standby where process=’MRP0′;

    PROCESS                        THREAD#  SEQUENCE# STATUS
    ————————— ———- ———- ————————————
    MRP0                                 1     279486 WAIT_FOR_LOG


    Conclusion: 


    From this article we have learnt how to convert Physical standby database to Snapshot standby database using Data guard Broker method. 

  • Convert Physical Standby to Snapshot standby

    Overview:

    This article explain the process of converting Physical Standby in Read Only mode to a Snapshot Standby.Snapshot Standby open DB in Read Write mode for disaster recovery testing.  This is to allow the database in DR site to be accessed by applications and perform tests on how the database could be accessed and application purposes be met during a disaster scenario while the actual primary database is not shutdown completely.

    Environment Details:
    Hostname
    Node1 and Node2
    Operating system
    Linux
    Environment
    Production and Standby
    Oracle Home
    /u01/app/oracle/product/11.2.0/db
    Prerequisites for converting the
    database
    • Ensure
      the FRA is configured.

    SQL> sho parameter recover
    Note:- Snapshot standby need not have Flashback enabled at
    db level but if it is enabled then FRA would have already been configured
    • Ensure the redo transport parameters for the
      primary and redo apply for the standby are configured:

    SQL> set lin 999
    SQL> column NAME format
    a30
    SQL> column VALUE format
    a84
    SQL> select NAME, VALUE
    from v$parameter
    where NAME in
    (‘db_name’, ‘db_unique_name’, ‘control_files’,
    ‘log_archive_config’,’fal_client’, ‘fal_server’,
    ‘log_archive_dest_1’,
    ‘log_archive_dest_2’, ‘log_archive_dest_3’, ‘log_archive_dest_4’,
    ‘log_archive_dest_state_1′,’log_archive_dest_state_2’,‘log_archive_dest_state_3’,
    ‘log_archive_dest_state_4′,’log_file_name_convert’,
    ‘remote_login_passwordfile’);

    • Ensure the standby redo log files are
      configured on the primary and standby database.

    SQL> column member
    format a50
    SQL> SELECT a.group#,
    a.member, b.bytes/1024/1024 bytes_MB     FROM v$logfile a,
    v$log b WHERE a.group# = b.group#     union select a.group#,
    a.member, b.bytes/1024/1024 bytes_MB     from v$logfile a,
    v$standby_log b  WHERE a.group# =
    b.group#;
    Checks on DR Test
    • Run the following query to pull information
      about the database on both Primary and standby

    SQL> set lin 175
    SQL> column NAME format
    a10
    SQL> column LOG_MODE
    format a10
    SQL> column OPEN_MODE
    format a10
    SQL> COLUMN CONTROLFILE_TYPE
    format a16
    SQL> column
    PROTECTION_MODE format a20
    SQL> column
    PROTECTION_LEVEL format a20
    SQL> column
    DATABASE_ROLE format a20
    SQL> column GUARD_STATUS
    format a12
    SQL> column
    DATAGUARD_BROKER format a16
    SQL> select NAME,DATABASE_ROLE,LOG_MODE,CONTROLFILE_TYPE, OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,GUARD_STATUS,FORCE_LOGGING,DATAGUARD_BROKER from v$database;
    • Verifying That the Standby Has Received All
      Redo, you can carry your individual steps.

    On Primary and on
    standby –> To check if the scn’s are close.
    SQL> set numwidth 20
    SQL> select current_scn
    from v$database;
    On Standby –>
    To check if the MRP is running

    SQL> select
    status,process from v$managed_standby where process like ‘%MRP%’;
     STATUS                                          
    PROCESS
    ————————————————
    ————————————
    APPLYING_LOG                                     MRP0
    On standby  –> to Check if all or near to all logs
    are applied

    SQL> SELECT
    SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
    And to check real
    time apply On primary / standby –> It should say “MANAGED REAL TIME
    APPLY” in one of the rows.

    SQL> SELECT
    RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS;
    SQL> SELECT
    CLIENT_PROCESS,PROCESS,SEQUENCE#,STATUS FROM V$MANAGED_STANDBY;
    Compare the
    sequence# from primary to standby query. 
    If the standby is not receiving the current redo, you cannot convert/switchover.
    Note that if your primary is a RAC, you should see multiple LGWR to RFS
    connections, one for each primary thread. You must validate that each primary
    thread is caught up.
    • Check the apply is caught up

    Once determined the
    redo stream is current, ensure all redo has been applied to standby database.
    On the standby
    database:
    SQL> SELECT
    CLIENT_PROCESS,PROCESS,SEQUENCE#,STATUS FROM V$MANAGED_STANDBY;

    Note: if you don’t
    see MRP0, the apply is not running
    To
    convert/switchover, you must start the apply service and wait for it to catch
    up with the current redo stream before starting. If you see the MRP0 line but
    it has a status of WAIT_FOR_GAP, you cannot convert/switchover until the gap
    has been resolved.
    • Verify there are no large Gaps

    Identify the
    current sequence number for each thread on the primary database.
    On the Primary:
    SQL> SELECT
    THREAD#,SEQUENCE#,STATUS FROM V$LOG;
    Verify the target
    physical standby database has applied up to, but not including the logs from
    the primary query. On the standby the following query should be within 1 or 2
    of the primary query result.
    On the Standby
    database:
    SQL> SELECT
    THREAD#, MAX(SEQUENCE#) FROM V$ARCHIVED_LOG     WHERE APPLIED = ‘YES’ AND RESETLOGS_CHANGE# = SELECTRESETLOGS_CHANGE# FROM V$DATABASE_INCARNATION WHERE STATUS =’CURRENT’) GROUP BY THREAD#;
    Convert to Physical
    standby to Snapshot Standby manual way
    •  On standby
      to be converted to snapshot mode.

    Tail the Alert Logs (optional) on all
    instances:
    Locate alert logs by showing database parameter
    background_dump_dest:
    SQL> SHOW PARAMETER background_dump_dest;
    % tail -f /u01/app/diag/rdbms/standby/standby1/trace/alert*
    • Stop MRP

    SQL> alter database recover managed standby
    database cancel;


    Database alerted
    • Record SCN
      and timestamp

    SQL>
    col current_scn format 99999999999999
    SQL>
    select current_scn from v$database;
    •  Manual
      SQLPLUS Convert to snapshot standby:

                    For
    standalone
    SQL> shutdown
    immediate
    SQL> startup
    mount

                    For
    RAC
    $srvctl
    stop database -d DBNAME
    $srvctl
    start database -d DBNAME -o mount
         
    SQL>
    select open_mode, database_role from v$database;

    SQL>
    ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

    SQL>
    shutdown immediate

    SQL>
    startup mount

    SQL>
    select open_mode, database_role from v$database;

    SQL>
    ALTER DATABASE OPEN;

    SQL> select open_mode, database_role from v$database;

    Convert to 
    Snapshot Standby back to Physical standby manual way

    For RAC

    $srvctl stop database -d DBNAME
    $srvctl startup database -d DBNAME
    -o mount
    SQL> ALTER
    DATABASE CONVERT TO PHYSICAL STANDBY;

    srvctl stop database -d DBNAME
    For Standalone
    SQL> shutdown immediate

    SQL> startup mount

    SQL> ALTER DATABASE CONVERT TO
    PHYSICAL STANDBY;

    SQL> shutdown immediate
    Restart database
    For standalone
      SQL> startup mount

    For RAC
    srvctl start database -d DBNAME
    -o mount
    • Start MRP and open read only

    SQL> alter
    database open read only;

    SQL> alter
    database recover managed standby database using current logfile disconnect;
    •  Perform the Physical standby check out

     On Primary:

    Check the alert log of primary database and tail the alert.
    Check the archive error destination
    sql>col
    DEST_NAME for a40
    sql>set
    lines 200
    sql>select
    DEST_ID,DEST_NAME,STATUS,ERROR from v$archive_dest;
    Check the current archive log (sequence)
    sql>select thread#,max(sequence#) from v$log where
    status=’CURRENT’ group by thread# order by 1;
    On Standby:
    Check the alert log of standby database and tail the alert.
    Check the current archive log (sequence) applied.
    sql>select thread#,max(sequence#) from
    v$archived_log where applied=’YES’ group by thread# order by 1;
    Check the status of MRP
    $ps -ef |grep mrp
    sql>select PROCESS,THREAD#,SEQUENCE#,STATUS from
    v$managed_standby where process=’MRP0′;
    PROCESS                        THREAD#  SEQUENCE# STATUS
    —————————
    ———- ———- ————————————
    MRP0                                 1     279486 WAIT_FOR_LOG

    Conclusion: 

    From this article we have learnt how to convert Physical standby database to Snapshot standby database using manual method. 

  • Installing and Configuring Oracle GoldenGate Studio 12c

    Overview
    Don’t want to design or build Oracle GoldenGate architecture from Command line? Oracle GoldenGate Studio is the answer for you.
    In 2015, Oracle has released a new tool which provides a Graphical User Interface (GUI) for designing and building Oracle GoldenGate Architecture called “Oracle GoldenGate Studio”.


    Installing Oracle GoldenGate Studio consists of following steps:

    1. Ensure Java JDK 1.8 is installed
    2. Install Oracle GoldenGate Studio
    3. Configure Oracle GoldenGate Studio repository
    4. Start and Connect to Oracle GoldenGate Studio 

    In this artcile I will demonstrate how to Install and Configure Oracle GoldenGate Studio 12c in detail.


    Environment

    • Oracle Enterprise Linux 6.8
    • Oracle RDBMS 11.2.0.4

    Assumption
    It is assumed that you have a database created which will be used as respository for OGG Studio


    Downloading and Installing latest JDK

    • Get the current Java version installed.

    [root@dm01db01 software]# java -version
    java version “1.7.0_91”
    Java(TM) SE Runtime Environment (build 1.7.0_91-b32)
    Java HotSpot(TM) 64-Bit Server VM (build 24.91-b03, mixed mode)


    Java JDK 1.7 and earlier versions doesn’t work with the JAR files for Studio.
    Java JDK 1.8 is required for GoldenGate Studio Installation.

    • To download the required JDK, navigate to the following URL and download the Java SE JDK:

    http://www.oracle.com/technetwork/java/javase/downloads/index.html

    • Copy the downloaded rpm the server using WinScp and apply it.

    [root@dm01db01 ]# cd /u01/app/oracle/software
    [root@dm01db01 software]# ls -ltr
    -rw-r–r– 1 root   root     166040563 Jan  3 03:11 jdk-8u111-linux-x64.rpm


    [root@dm01db01 software]# rpm -i jdk-8u111-linux-x64.rpm
    Unpacking JAR files…
            tools.jar…
            plugin.jar…
            javaws.jar…
            deploy.jar…
            rt.jar…
            jsse.jar…
            charsets.jar…
            localedata.jar…

    • Verify the Java version 

    [root@dm01db01 software]# java -version
    java version “1.8.0_111”
    Java(TM) SE Runtime Environment (build 1.8.0_111-b14)
    Java HotSpot(TM) 64-Bit Server VM (build 25.111-b14, mixed mode)


    Downloading Oracle GoldenGate Studio Software

    • Open a web browser and enter www.oracle.com in the address bar
    • This will bring you to the Oracle website home page
    • On this page hover on the download tab and click on “middleware”
    • Click GoldenGate
    • Accept the agreement and select the desired Oracle GoldenGate version for your platform.
    Here I am downloading “fmw_12.2.1.2.6_oggstudio_Disk1_1of1.zip”
    • Sing in if prompted. The download will begin
    • Copy the zip file from your desktop to the server using WinScp
    • Drag and Drop the file from left to right in to a directory
    Here I am copying the file to /u01/app/oracle/software location
    • File copy is in progress
    • Once the file transfer is completed, you will see the file on server as below
    dm01db01-orcldb1 {/u01/app/oracle/software}:ls -ltr
    -rw-r–r– 1 oracle oinstall 2042373304 Jan  3 04:42 fmw_12.2.1.2.6_oggstudio_Disk1_1of1.zip

    Steps to Install Oracle GoldenGate Studion

    • Make sure Java is set in your path properly.
    dm01db01-orcldb1 {/home/oracle}:whereis java
    java: /usr/bin/java
    • Here I am using Oracle user to install GoldenGate Studio software
    [oracle@dm01db01 software]# java -version
    java version “1.8.0_111”
    Java(TM) SE Runtime Environment (build 1.8.0_111-b14)
    Java HotSpot(TM) 64-Bit Server VM (build 25.111-b14, mixed mode)
    • Unzip the Oracle GoldenGate Studio software
    dm01db01-orcldb1 {/u01/app/oracle/software}:unzip fmw_12.2.1.2.6_oggstudio_Disk1_1of1.zip
    Archive:  fmw_12.2.1.2.6_oggstudio_Disk1_1of1.zip
      inflating: fmw_12.2.1.2.6_oggstudio.jar
      inflating: fmw_122126_readme.htm

    dm01db01-orcldb1 {/u01/app/oracle/software}:ls -ltr
    -rw-r–r– 1 oracle oinstall       9032 Dec  5 16:49 fmw_122126_readme.htm
    -r-xr-xr-x 1 oracle oinstall 2042431392 Dec  5 16:55 fmw_12.2.1.2.6_oggstudio.jar
    -rw-r–r– 1 oracle oinstall 2042373304 Jan  3 04:42 fmw_12.2.1.2.6_oggstudio_Disk1_1of1.zip
    • Start the x-window and set the DISPLAY variable to launch the installation window
    dm01db01-orcldb1 {/u01/app/oracle/software}:export DISPLAY=10.10.193.2:0.0
    • Start the Oracle GoldenGate Studio software installation as follows:
    dm01db01-orcldb1 {/u01/app/oracle/software}:java -jar fmw_12.2.1.2.6_oggstudio.jar
    Launcher log file is /tmp/OraInstall2017-01-03_05-02-43AM/launcher2017-01-03_05-02-43AM.log.
    Extracting the installer . . . . . . . . . . . . . . . . . . . . Done
    Checking if CPU speed is above 300 MHz.   Actual 2527.001 MHz    Passed
    Checking monitor: must be configured to display at least 256 colors.  Actual .   Passed
    Checking swap space: must be greater than 512 MB.   Actual 16378 MB    Passed
    Checking if this platform requires a 64-bit JVM.   Actual 64    Passed (64-bit not required)
    Checking temp space: must be greater than 300 MB.   Actual 8262 MB    Passed

    Preparing to launch the Oracle Universal Installer from /tmp/OraInstall2017-01-03_05-02-43AM
    Log: /tmp/OraInstall2017-01-03_05-02-43AM/install2017-01-03_05-02-43AM.log
    Logs successfully copied to /u01/app/oraInventory/logs.

    • The Installer wizard will be started
    • This is the first step out of 8 steps. Click Next
    • Select “Skip Auto Updates” and Click Next
    • Enter the Oracle GoldenGate Studio home for software installation
    • Select installation type as “Complete Install (Default)”. Click Next
    • Prerequisite checks are performed. Click Next
    • The installation Summary is displayed
    • Now the Software Installation progress

    • Click Finish


    Configure Oracle GoldenGate Studio repository

    The Oracle GoldenGate Studio store entire replication design and deployment details in a database repository. You must install the repository schema on a certified database before using Oracle GoldenGate Studio. The repository is created using the Oracle Repository Creation Utility (RCU).

    Make sure you created a database for storing the Oracle GoldenGate Studio objects before proceeding. Here I already have a database created by the name “orcldb” which I will be using as a repository.
    • Make sure the DISPLAY is set properly and Launch the Repository Creation Utility as follows:
    dm01db01-orcldb1 {/u01/app/oracle/software}:cd /u01/app/oracle/product/oggstudio12c/oracle_common/bin/
    dm01db01-orcldb1 {/u01/app/oracle/product/oggstudio12c/oracle_common/bin}:./rcu

            RCU Logfile: /tmp/RCU2017-01-03_07-10_453443440/logs/rcu.log

    • On the home page. Click next
    • Select “Create Repository” and “System Load and Product Load”. Click Next
    • Enter the Oracle Database server details and Click next

    • Prerequisites checks are performed. Click ok

    •  Enter the “Create New Prefix”. Click next


    • Prerequisite checks are performed. Click Ok

    •  Enter the password for all Schemas. Here I am select “Use same password for all schemas”

    •  Enter the Password for “Supervisor” user. Click Next

    • Click Next

    • Click ok

    • Click ok

    • On this page, tablespace mapping is displayed. Click next

    • System load in progress

    • Click Close to complete the RCU process



    Start and Connect to Oracle GoldenGate Studio 

    Now let’s start the oggstudio and connect to the repository
    Make sure the DISPLAY variable is set properly so the installer can be launced.

    Execute the oggstudio utitilty to start the Oracle GoldenGate Studio 



    dm01db01-orcldb1 {/u01/app/oracle/product/oggstudio12c/oggstudio/bin}:./oggstudio


    Oracle GoldenGate Studio
    Copyright (c) 1997, 2016, Oracle and/or its affiliates. All rights reserved.


    Type the full pathname of a JDK installation (or Ctrl-C to quit), the path will be stored in /home/oracle/.oggstudio/12.2.1.2.0/product.conf
    /usr/java/jdk1.8.0_111


    Note: When you start the oggstudio for the first time, it will ask you for the jdk location and stores in it product.conf file. The furture start will not ask you for the jdk location again.


    The Oracle GoldenGate Studio page appears as below

     For the first time it will ask you, if you would like to import preferences from previous installation. Click No.

    • The load will continue as follows

    • Click on the Plug symbol to connect to the repository database

    • Enter the Supervisor and it’s password



    • Finally the oggstudio is started and you are connected the repository

    Conclusion
    In this article we have learned how to install and configure Oracle GoldenGate Studio 12c. The OGG Studio is very useful for the folks who can to design and build Oracle GoldenGate architecture using GUI interface.

  • Rebuild Physical Standby Using RMAN Incremental Backup

    Overview:
    In my previous article I have shown you how to rebuild a physical standby database using RMAN full database backup. Please refer below link for same.

    Rebuild Physical Standby using RMAN full backup  

    Rolling forward a standby using RMAN full database backup can be time consuming and take several hours based on the size of the database. RMAN offers a flexibility to take the SCN based backup that can be used to incrementally roll forward a standby database. Using RMAN incremental backup to roll forward a standby database is the easiest and quickest way to sync a standby database.


    Environment
    Details

    Hostname
    Primary server :–
    lnx-nsm-prod01/ lnx-nsm-prod02


    Standby Server :– 
    lnx-nsm-dr01/ lnx-nsm-dr02
    Operating
    system
    Linux
    Database
    Environment
    NSMDB
    and NSMDR



    • Get the current SCN from standby database, we will use this SCN for the incremental backup from primary.


    SQL> select current_scn from v$database;
    • Take a control file backup from NSMDB primary.
    $ rman target /
    RMAN> backup current controlfile for standby format ‘/backup/nsmdb/nsmdb_stby_ctl’;
    • Create backup directory and take an incremental backup from primary, use the SCN from standby in step 1.
    $ mkdir -p /backup/nsmdb/
    $ rman target /
    RMAN> run {
    allocate channel c1 device  type disk format ‘/backup/nsmdb/nsmdb_%U’;
    allocate channel c2 device  type disk format ‘/backup/nsmdb/nsmdb_%U’;
    allocate channel c3 device  type disk format ‘/backup/nsmdb/nsmdb_%U’;
    allocate channel c4 device  type disk format ‘/backup/nsmdb/nsmdb_%U’;
    allocate channel c5 device  type disk format ‘/backup/nsmdb/nsmdb_%U’;
    allocate channel c6 device  type disk format ‘/backup/nsmdb/nsmdb_%U’;
    allocate channel c7 device  type disk format ‘/backup/nsmdb/nsmdb_%U’;
    allocate channel c8 device  type disk format ‘/backup/nsmdb/nsmdb_%U’;
    backup incremental from scn scn_from_step_1 database;
    }
    • Get datafile file# and name from primary database, and spool output to text file f1.txt for reference.
    SQL> select file#, name from v$datafile order by 1;
    • Copy the backup pieces to standby hosts in the same location.
    $ mkdir -p  /backup/nsmdb
    • Stop mrp at the standby.
    DGMGRL> edit database nsmdr set state=APPLY-OFF;
    • Get datafile file# and name from standby  database, and spool output to text file f2.txt for reference.
    SQL> select file#, name from v$datafile order by 1;
    • Bring down standby database, all instances.
    $ Srvctl stop database –d nsmdr
    • Start the standby database instance in nomount and restore the controlfile.
    $ rman target /                            
    RMAN> startup nomount
    RMAN> restore standby controlfile from ‘/backup/nsmdb/nsmdr_stby_ctl’;
    • Mount the standby database, stop MRP, catalog the backup pieces.
    SQL> alter database mount standby database
    SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
    $ rman target /
    RMAN> sql ‘alter database mount’;
    RMAN> catalog start with ‘/backup/nsmdb’;
    • Catalog data files and switch the files to copy.
    RMAN> catalog start with ‘+DATA/nsmdr/datafile’;
    RMAN> SWITCH DATABASE TO COPY;
    • Recover the standby database.
    $ rman target /
    RMAN> run {
    allocate channel c1 device  type disk format ‘/backup/nsmdb/nsmdb_%U’;
    allocate channel c2 device  type disk format ‘/backup/nsmdb/nsmdb_%U’;
    allocate channel c3 device  type disk format ‘/backup/nsmdb/nsmdb_%U’;
    allocate channel c4 device  type disk format ‘/backup/nsmdb/nsmdb_%U’;
    allocate channel c5 device  type disk format ‘/backup/nsmdb/nsmdb_%U’;
    allocate channel c6 device  type disk format ‘/backup/nsmdb/nsmdb_%U’;
    allocate channel c7 device  type disk format ‘/backup/nsmdb/nsmdb_%U’;
    allocate channel c8 device  type disk format ‘/backup/nsmdb/nsmdb_%U’;
    recover database noredo;
    }
    • Restart MRP and ensure that recovery is progressing.
    DGMGRL> edit database nsmdr set state=APPLY-ON;

    • Monitoring
    The below query can be used to monitor the progress of RMAN sessions performing backup/restore.

    SQL> set lines 150
    SQL> col MESSAGE for a100
    SQL> col sid for 999999
    SQL> col ELAPSED_SECONDS for 999999
    SQL> col TIME_REMAINING for 999999
    SQL> col comp for 999 heading “%Comp”
    SQL> SELECT sid, MESSAGE, ELAPSED_SECONDS, TIME_REMAINING, round(sofar/totalwork*100) comp
    FROM v$session_longops
    WHERE opname LIKE ‘RMAN%’
    AND opname NOT LIKE ‘%aggregate%’
    AND totalwork != 0
    AND sofar <> totalwork;

    Conclusion
    In this article we have learnt the steps to roll forward a physical standby database using RMAN incremental backup.

  • Rebuild Physical Standby using RMAN full backup

    Overview:
    At times standby database can go out of Sync for many reasons like, accidentally deleting archive log on primary or RMAN backup deleted archive log before shipping to standby database server. When this happens you must rebuild the Standby database using primary database backup.


    In this article we outline the process of rebuild the Physical
    Standby which is out of sync from Primary.

    Environment Details
    Hostname
    Primary server :–
    lnx-nsm-prod01/ lnx-nsm-prod02


    Standby
    Server :– 
    lnx-nsm-dr01/ lnx-nsm-dr02
    Operating
    system
    Linux
    Database
    Environment
    NSMDB
    and NSMDR

    •     Take full
      RMAN backup of primary. Create location if does not exists.

    run {
    allocate channel c1
    device  type disk format ‘/backup/nsmdb/NSMDB_%U’;
    backup database plus
    archivelog tag=”FORSTDBY”;
    }
    •       Take a
      control file backup from primary.
    rman target /
    backup current controlfile for
    standby format ‘/backup/nsmdb/NSMDB_stby_ctl’;

    •       Copy backup pieces
      to standby server. Create location if does not exists on standby

    •       Stop and clean
      up standby Database files from locations
    srvctl stop database
    –d nsmdr

    • Start the standby database instance in nomount
      and restore the controlfile.
    rman target /                            
    startup nomount
    restore standby
    controlfile from ‘/backup/nsmdb/NSMDB_stby_ctl’

    •      Mount the standby database, stop MRP if it is up,
      catalog the backup pieces.
    SQL> alter database
    mount standby database;
    SQL> ALTER DATABASE
    RECOVER MANAGED STANDBY DATABASE CANCEL;
    rman target /
    catalog start with ‘/backup/nsmdb/NSMDB_%U’;

    •      Now restore
      database.
    run {
    allocate channel c1
    device  type disk format ‘/backup/nsmdb/NSMDB_%U’;
    restore database;
    }
    Exit;

    •         Need to shutdown
      the database.
    SQL>  shutdown
    immediate;               

    •        Start the
      database in read only mode.
    $ srvctl start database –d nsmdr      

    •        Restart MRP
      and ensure that recovery is progressing.

    DGMGRL> edit database nsmdr set state=APPLY-ON;
    Monitoring RMAN Progress


    The below query can be used to monitor the progress of RMAN
    sessions performing backup/restore.

    set lines 150
    col MESSAGE for a100
    col sid for 999999
    col ELAPSED_SECONDS
    for 999999
    col TIME_REMAINING for
    999999
    col comp for 999
    heading “%Comp”
    SELECT sid, MESSAGE,
    ELAPSED_SECONDS, TIME_REMAINING, round(sofar/totalwork*100) comp
    FROM v$session_longops
    WHERE opname LIKE
    ‘RMAN%’
    AND opname NOT LIKE
    ‘%aggregate%’
    AND totalwork != 0
    AND sofar <>
    totalwork;

    Conclusion
    In this article we have learnt the steps to rebuild a physical standby database which was out of sync from primary database.

  • Steps to Shutdown Exadata Database Machine

    Overview
    When you perform power maintenance, data center maintenance or moving servers from one data center to another you need to shutdown the Clusterware/Database/Servers. If you have Exadata Database Machines sitting in your data center then you need to shutdown it as well. The process of shutting down Exadata Database machine is slightly different when compared to servers or cluster. We should follow a sequence of steps to shutdown Exadata components. 


    In this article, we will demonstrate the steps to shutdown Exadata Database Machine to carry out maintenance task.


    Environment
    Exadata X5-2 Full Rack


    • Log in to the first Exadata compute node as as root user.
    • Change to root home directory and create the following files

    [root@dm01db01 ~]# cd /root


    [root@dm01db01 ~]# vi dbs_group
    dm01db01
    dm01db02
    dm01db03
    dm01db04
    dm01db05
    dm01db06
    dm01db07
    dm01db08


    [root@dm01db01 ~]# vi cell_group
    dm01cel01
    dm01cel02
    dm01cel03
    dm01cel04
    dm01cel05
    dm01cel06
    dm01cel07
    dm01cel08
    dm01cel09
    dm01cel10
    dm01cel11
    dm01cel12
    dm01cel13
    dm01cel14

    • Check if Grid Infrastructure is enabled for autostart:

    [root@dm01db01 ~]# dcli -g dbs_group -l root /u01/app/11.2.0.4/grid/bin/crsctl config crs
    dm01db01: CRS-4622: Oracle High Availability Services autostart is enabled.
    dm01db02: CRS-4622: Oracle High Availability Services autostart is enabled.
    dm01db03: CRS-4622: Oracle High Availability Services autostart is enabled.
    dm01db04: CRS-4622: Oracle High Availability Services autostart is enabled.
    dm01db05: CRS-4622: Oracle High Availability Services autostart is enabled.
    dm01db06: CRS-4622: Oracle High Availability Services autostart is enabled.
    dm01db07: CRS-4622: Oracle High Availability Services autostart is enabled.
    dm01db08: CRS-4622: Oracle High Availability Services autostart is enabled.

    • Disable the Grid Infrastructure for autostart on the compute nodes if it is currently enabled for autostart.

    [root@dm01db01 ~]# dcli -g dbs_group -l root /u01/app/11.2.0.4/grid/bin/crsctl disable crs
    dm01db01: CRS-4621: Oracle High Availability Services autostart is disabled.
    dm01db02: CRS-4621: Oracle High Availability Services autostart is disabled.
    dm01db03: CRS-4621: Oracle High Availability Services autostart is disabled.
    dm01db04: CRS-4621: Oracle High Availability Services autostart is disabled.
    dm01db05: CRS-4621: Oracle High Availability Services autostart is disabled.
    dm01db06: CRS-4621: Oracle High Availability Services autostart is disabled.
    dm01db07: CRS-4621: Oracle High Availability Services autostart is disabled.
    dm01db08: CRS-4621: Oracle High Availability Services autostart is disabled.


    [root@dm01db01 ~]# dcli -g dbs_group -l root /u01/app/11.2.0.4/grid/bin/crsctl config crs
    dm01db01: CRS-4621: Oracle High Availability Services autostart is disabled.
    dm01db02: CRS-4621: Oracle High Availability Services autostart is disabled.
    dm01db03: CRS-4621: Oracle High Availability Services autostart is disabled.
    dm01db04: CRS-4621: Oracle High Availability Services autostart is disabled.
    dm01db05: CRS-4621: Oracle High Availability Services autostart is disabled.
    dm01db06: CRS-4621: Oracle High Availability Services autostart is disabled.
    dm01db07: CRS-4621: Oracle High Availability Services autostart is disabled.
    dm01db08: CRS-4621: Oracle High Availability Services autostart is disabled.

    • Shutdown the Grid Infrastructure stack on the Exadata compute nodes

    This will take some time to come out
    [root@dm01db01 ~]#dcli -g dbs_group -l root /u01/app/11.2.0.4/grid/bin/crsctl stop crs


    [root@dm01db01 ~]# dcli -g dbs_group -l root /u01/app/11.2.0.4/grid/bin/crsctl check cluster
    dm01db01: CRS-4639: Could not contact Oracle High Availability Services
    dm01db01: CRS-4000: Command Check failed, or completed with errors.
    dm01db02: CRS-4639: Could not contact Oracle High Availability Services
    dm01db02: CRS-4000: Command Check failed, or completed with errors.
    dm01db03: CRS-4639: Could not contact Oracle High Availability Services
    dm01db03: CRS-4000: Command Check failed, or completed with errors.
    dm01db04: CRS-4639: Could not contact Oracle High Availability Services
    dm01db04: CRS-4000: Command Check failed, or completed with errors.
    dm01db05: CRS-4639: Could not contact Oracle High Availability Services
    dm01db05: CRS-4000: Command Check failed, or completed with errors.
    dm01db06: CRS-4639: Could not contact Oracle High Availability Services
    dm01db06: CRS-4000: Command Check failed, or completed with errors.
    dm01db07: CRS-4639: Could not contact Oracle High Availability Services
    dm01db07: CRS-4000: Command Check failed, or completed with errors.
    dm01db08: CRS-4639: Could not contact Oracle High Availability Services
    dm01db08: CRS-4000: Command Check failed, or completed with errors.

    • Ensure that the Grid Infrastructure stack has shutdown successfully on all the Compute nodes. 

    The following command returns no output if the Grid Infrastructure is shutdown:


    [root@dm01db01 ~]# dcli -g dbs_group -l root “ps -ef | grep diskmo[n]”

    • Disable email/SNMP alerts. The following command will show the current notification method:

    [root@dm01db01 ~]# dcli -g cell_group -l root “cellcli -e list cell attributes name,notificationMethod”
    dm01cel01: dm01cel01     none
    dm01cel02: dm01cel02     none
    dm01cel03: dm01cel03     none
    dm01cel04: dm01cel04     none
    dm01cel05: dm01cel05     none
    dm01cel06: dm01cel06     none
    dm01cel07: dm01cel07     none
    dm01cel08: dm01cel08     none
    dm01cel09: dm01cel09     none
    dm01cel10: dm01cel10     none
    dm01cel11: dm01cel11     none
    dm01cel12: dm01cel12     none
    dm01cel13: dm01cel13     none
    dm01cel14: dm01cel14     none

    • Now set the notification method to null to disable alerts:

    [root@dm01db01 ~]# dcli -g cell_group -l root “cellcli -e alter cell notificationMethod=none”
    dm01cel01: Cell dm01cel01 successfully altered
    dm01cel02: Cell dm01cel02 successfully altered
    dm01cel03: Cell dm01cel03 successfully altered
    dm01cel04: Cell dm01cel04 successfully altered
    dm01cel05: Cell dm01cel05 successfully altered
    dm01cel06: Cell dm01cel06 successfully altered
    dm01cel07: Cell dm01cel07 successfully altered
    dm01cel08: Cell dm01cel08 successfully altered
    dm01cel09: Cell dm01cel09 successfully altered
    dm01cel10: Cell dm01cel10 successfully altered
    dm01cel11: Cell dm01cel11 successfully altered
    dm01cel12: Cell dm01cel12 successfully altered
    dm01cel13: Cell dm01cel13 successfully altered
    dm01cel14: Cell dm01cel14 successfully altered

    • Shutdown cell services on all the cells:

    [root@dm01db01 ~]# dcli -g cell_group -l root “cellcli -e alter cell shutdown services all”
    dm01cel01:
    dm01cel01: Stopping the RS, CELLSRV, and MS services…
    dm01cel01: The SHUTDOWN of services was successful.
    dm01cel02:
    dm01cel02: Stopping the RS, CELLSRV, and MS services…
    dm01cel02: The SHUTDOWN of services was successful.
    dm01cel03:
    dm01cel03: Stopping the RS, CELLSRV, and MS services…
    dm01cel03: The SHUTDOWN of services was successful.
    dm01cel04:
    dm01cel04: Stopping the RS, CELLSRV, and MS services…
    dm01cel04: The SHUTDOWN of services was successful.
    dm01cel05:
    dm01cel05: Stopping the RS, CELLSRV, and MS services…
    dm01cel05: The SHUTDOWN of services was successful.
    dm01cel06:
    dm01cel06: Stopping the RS, CELLSRV, and MS services…
    dm01cel06: The SHUTDOWN of services was successful.
    dm01cel07:
    dm01cel07: Stopping the RS, CELLSRV, and MS services…
    dm01cel07: The SHUTDOWN of services was successful.
    dm01cel08:
    dm01cel08: Stopping the RS, CELLSRV, and MS services…
    dm01cel08: The SHUTDOWN of services was successful.
    dm01cel09:
    dm01cel09: Stopping the RS, CELLSRV, and MS services…
    dm01cel09: The SHUTDOWN of services was successful.
    dm01cel10:
    dm01cel10: Stopping the RS, CELLSRV, and MS services…
    dm01cel10: The SHUTDOWN of services was successful.
    dm01cel11:
    dm01cel11: Stopping the RS, CELLSRV, and MS services…
    dm01cel11: The SHUTDOWN of services was successful.
    dm01cel12:
    dm01cel12: Stopping the RS, CELLSRV, and MS services…
    dm01cel12: The SHUTDOWN of services was successful.
    dm01cel13:
    dm01cel13: Stopping the RS, CELLSRV, and MS services…
    dm01cel13: The SHUTDOWN of services was successful.
    dm01cel14:
    dm01cel14: Stopping the RS, CELLSRV, and MS services…
    dm01cel14: The SHUTDOWN of services was successful.
    [root@dm01db01 ~]#

    • Power off all storage cells:

    [root@dm01db01 ~]# dcli -g cell_group -l root poweroff
    or
    [root@dm01db01 ~]# dcli -g cell_group -l root ‘shutdown -h now’

    • Copy the /root/dbs_group to /root/dbs_group1:

    [root@dm01db01 ~]# cp /root/dbs_group /root/dbs_group1

    • Remove the “first compute node name” from the /root/dbs_group1 file (using any editor, e.g. vi).

    [root@dm01db01 ~]# vi /root/dbs_group1


    [root@dm01db01 ~]# cat /root/dbs_group1
    dm01db02
    dm01db03
    dm01db04
    dm01db05
    dm01db06
    dm01db07
    dm01db08

    • Power off all the compute nodes (except compute node # 1):

    [root@dm01db01 ~]# dcli -g dbs_group1 -l root poweroff
    or
    [root@dm01db01 ~]# dcli -g dbs_group1 -l root ‘shutdown -h now’

    • Power off the compute node #1:

    [root@dm01db01 ~]# poweroff


    Broadcast message from root@dm01db01.netsoftmate.com
            (/dev/pts/0) at 7:36 …


    The system is going down for power off NOW!

    • Exadata components are shutdown for maintenance.



    Conclusion
    In this article we have learnt how to Shutdown Exadata Database machine Rack for maintenance.

  • Rman targetless duplication with different DB name and different directory structure using file_name convert

    Overview:

    Oracle Recovery Manager
    (RMAN) provides a comprehensive foundation for efficiently backing up and
    recovering the Oracle databases    , it provides a common interface, via command line and
    Enterprise Manager, for backup tasks across different host operating systems    , automates administration of your
    backup strategies    .

       In this scenario we have different database name and different
    directory structure. And we are transforming the directory structure in pfile
    itself, then no need to give extra parameters in rman duplicate command.

    Target (source)
    details

    Database Name
    Prod
    Hostname
    Netsoftmate
    Ip Address
    172.16.110.18
    OS
    Linux
    Version
    x86_64
    Datafile Location
    /data2/prod
    Backup Location
    /backup/rman_backup

    Destination details

    Database Name
    Cipfile
    Hostname
    Netsoftmate1
    Ip Address
    172.16.110.16
    OS
    Linux
    Version
    x86_64
    Datafile Location
    /data2/cipfile
    Backup Location
    /backup/rman_backup

    Prerequisites

    Password file from target database.

    Sqlnet.ora should have correct parameters.

    Target database should be running through pfile or
    spfile.

    Auxiliary instance should be start with pfile in nomount
    stage    .
    Steps on target (source) server
    1. Set Oracle sid on which backup will perform. 

    [oracle@Netsoftmate dbs]$ hostname

    Netsoftmate

    [oracle@Netsoftmate dbs]$ export ORACLE_SID=prod

    2. Connect to target instance via RMAN.

    [oracle@Netsoftmate dbs]$ rman target sys/oracle

    Recovery Manager: Release 11.2.0.1.0 – Production on
    Mon Apr 13 10:13:01 2015

    Copyright (c) 1982, 2009, Oracle and/or its
    affiliates.  All rights reserved.

    connected to target
    database: PROD (DBID=284539893)

    RMAN>

     3. Issue the following command to initiate backup. Before issue the backup command check the backup location is sat correctly.

    RMAN> show all;

    using target database control file instead of
    recovery catalog

    RMAN configuration parameters for database with
    db_unique_name PROD are:

    CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

    CONFIGURE BACKUP OPTIMIZATION OFF; # default

    CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

    CONFIGURE CONTROLFILE AUTOBACKUP ON;

    CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE
    TYPE DISK TO ‘/backup/rman_backup/controlfile_%F’;

    CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE
    TO BACKUPSET; # default

    CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK
    TO 1; # default

    CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE
    DISK TO 1; # default

    CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   ‘/backup/rman_backup/db_arch_%U.bkp’;

    CONFIGURE MAXSETSIZE TO UNLIMITED; # default

    CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

    CONFIGURE ENCRYPTION ALGORITHM ‘AES128’; # default

    CONFIGURE COMPRESSION ALGORITHM ‘BASIC’ AS OF RELEASE
    ‘DEFAULT’ OPTIMIZE FOR LOAD TRUE ; # default

    CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; #
    default

    CONFIGURE SNAPSHOT CONTROLFILE NAME TO
    ‘/data2/app/oracle/product/11.2.0/dbs/snapcf_prod.f’; # default

    RMAN> list backup;

    specification does not match any backup in the
    repository

     4. Initiate Backup.

    RMAN> backup database plus archivelog;

    Starting backup at 13-APR-15

    current log archived

    allocated channel: ORA_DISK_1

    channel ORA_DISK_1: SID=15 device type=DISK

    channel ORA_DISK_1: starting archived log backup set

    channel ORA_DISK_1: specifying archived log(s) in
    backup set

    input archived log thread=1 sequence=153 RECID=149
    STAMP=876910536

    channel ORA_DISK_1: starting piece 1 at 13-APR-15

    channel ORA_DISK_1: finished piece 1 at 13-APR-15

    piece
    handle=/backup/rman_backup/db_arch_2uq495u9_1_1.bkp tag=TAG20150413T101536
    comment=NONE

    channel ORA_DISK_1: backup set complete, elapsed
    time: 00:00:01

    Finished backup at 13-APR-15

    Starting backup at 13-APR-15

    using channel ORA_DISK_1

    channel ORA_DISK_1: starting full datafile backup set

    channel ORA_DISK_1: specifying datafile(s) in backup
    set

    input datafile file number=00001
    name=/data2/prod/system01.dbf

    input datafile file number=00002
    name=/data2/prod/sysaux01.dbf

    input datafile file number=00005
    name=/data2/prod/example01.dbf

    input datafile file number=00003
    name=/data2/prod/undotbs01.dbf

    input datafile file number=00006
    name=/data2/prod/data01.dbf

    input datafile file number=00004
    name=/data2/prod/users01.dbf

    input datafile file number=00007
    name=/data2/prod/test01.dbf

    input datafile file number=00008
    name=/data2/prod/tools01.dbf

    channel ORA_DISK_1: starting piece 1 at 13-APR-15

    channel ORA_DISK_1: finished piece 1 at 13-APR-15

    piece handle=/backup/rman_backup/db_arch_2vq495ua_1_1.bkp
    tag=TAG20150413T101538 comment=NONE

    channel ORA_DISK_1: backup set complete, elapsed
    time: 00:00:55

    Finished backup at 13-APR-15

    Starting backup at 13-APR-15

    current log archived

    using channel ORA_DISK_1

    channel ORA_DISK_1: starting archived log backup set

    channel ORA_DISK_1: specifying archived log(s) in
    backup set

    input archived log thread=1 sequence=154 RECID=150
    STAMP=876910594

    channel ORA_DISK_1: starting piece 1 at 13-APR-15

    channel ORA_DISK_1: finished piece 1 at 13-APR-15

    piece handle=/backup/rman_backup/db_arch_30q49603_1_1.bkp
    tag=TAG20150413T101635 comment=NONE

    channel ORA_DISK_1: backup set complete, elapsed
    time: 00:00:01

    Finished backup at 13-APR-15

    Starting Control File Autobackup at 13-APR-15

    piece handle=/backup/rman_backup/controlfile_c-284539893-20150413-00
    comment=NONE

    Finished Control File Autobackup at 13-APR-15

     5. List out the backups

    RMAN> list backup;

    List of Backup Sets

    ===================

    BS Key 
    Size       Device Type Elapsed
    Time Completion Time

    ——- ———- ———– ————
    —————

    32     
    3.36M      DISK        00:00:00     13-APR-15

            BP Key:
    32   Status: AVAILABLE  Compressed: NO  Tag: TAG20150413T101536

            Piece
    Name: /backup/rman_backup/db_arch_2uq495u9_1_1.bkp

      List of
    Archived Logs in backup set 32

      Thrd Seq     Low SCN   
    Low Time  Next SCN   Next Time

      —- ——-
    ———- ——— ———- ———

      1    153    
    5172938    13-APR-15 5176490    13-APR-15

    BS Key  Type LV
    Size       Device Type Elapsed Time
    Completion Time

    ——- —- — ———- ———– ————
    —————

    33     
    Full    1.11G      DISK        00:00:54     13-APR-15

            BP Key:
    33   Status: AVAILABLE  Compressed: NO  Tag: TAG20150413T101538

            Piece
    Name: /backup/rman_backup/db_arch_2vq495ua_1_1.bkp

      List of
    Datafiles in backup set 33

      File LV Type
    Ckp SCN    Ckp Time  Name

      —- — —-
    ———- ——— —-

      1       Full 5176498    13-APR-15 /data2/prod/system01.dbf

      2       Full 5176498    13-APR-15 /data2/prod/sysaux01.dbf

      3      
    Full 5176498    13-APR-15
    /data2/prod/undotbs01.dbf

      4       Full 5176498    13-APR-15 /data2/prod/users01.dbf

      5       Full 5176498    13-APR-15 /data2/prod/example01.dbf

      6       Full 5176498    13-APR-15 /data2/prod/data01.dbf

      7       Full 5176498    13-APR-15 /data2/prod/test01.dbf

      8       Full 5176498    13-APR-15 /data2/prod/tools01.dbf

    BS Key 
    Size       Device Type Elapsed
    Time Completion Time

    ——- ———- ———– ————
    —————

    34     
    9.00K      DISK        00:00:00    
    13-APR-15

            BP Key:
    34   Status: AVAILABLE  Compressed: NO  Tag: TAG20150413T101635

            Piece
    Name: /backup/rman_backup/db_arch_30q49603_1_1.bkp

      List of
    Archived Logs in backup set 34

      Thrd Seq     Low SCN   
    Low Time  Next SCN   Next Time

      —- ——-
    ———- ——— ———- ———

      1    154    
    5176490    13-APR-15 5176531    13-APR-15

    BS Key  Type LV
    Size       Device Type Elapsed Time
    Completion Time

    ——- —- — ———- ———– ————
    —————

    35     
    Full    9.52M      DISK        00:00:02     13-APR-15

            BP Key:
    35   Status: AVAILABLE  Compressed: NO  Tag: TAG20150413T101636

            Piece
    Name: /backup/rman_backup/controlfile_c-284539893-20150413-00

      Control File
    Included: Ckp SCN: 5176543      Ckp time:
    13-APR-15

    RMAN>

     6. Create pfile and copy to destination server.

    SQL> create
    pfile=’/backup/rman_backup/initprod.ora’ from spfile;

    File created.

    [oracle@Netsoftmate dbs]$ scp initprod.ora
    oracle@172.16.110.16:/data1/oracle11g/product/dbs/initcipfile.ora

    oracle@172.16.110.16’s password:

    initprod.ora                                                                                                                                                      
    100% 1169     1.1KB/s   00:00

     Before start scp/ftp to destination server create
    directory structure to accommodate Rman backup on destination server.

    [oracle@Netsoftmate1 prod]$ mkdir -p
    /backup/rman_backup/

    7. Copy rman backup from target to destination server.

    RMAN> exit

    Recovery Manager complete.

    [oracle@Netsoftmate dbs]$ cd /backup/rman_backup/

    [oracle@Netsoftmate rman_backup]$ ls -lrt

    total 1176020

    -rw-r—–. 1 oracle dba    3526656 Apr 13 10:15
    db_arch_2uq495u9_1_1.bkp

    -rw-r—–. 1 oracle dba 1190690816 Apr 13 10:16
    db_arch_2vq495ua_1_1.bkp

    -rw-r—–. 1 oracle dba       9728 Apr 13 10:16
    db_arch_30q49603_1_1.bkp

    -rw-r—–. 1 oracle dba    9994240 Apr 13 10:16
    controlfile_c-284539893-20150413-00

    [oracle@Netsoftmate rman_backup]$ scp *
    oracle@172.16.110.16:/backup/rman_backup/

    oracle@172.16.110.16’s password:

    controlfile_c-284539893-20150413-00                                                                                                                               
    100% 9760KB   9.5MB/s   00:01

    db_arch_2uq495u9_1_1.bkp                                                                                                                                           100%
    3444KB   3.4MB/s   00:00

    db_arch_2vq495ua_1_1.bkp                                                                       
                                                                       100%
    1136MB  11.1MB/s   01:42

    db_arch_30q49603_1_1.bkp                                                                                                                                      
        100% 9728     9.5KB/s  
    00:00

      
    Steps on destination server
    8. Create new directory structure. 

    [oracle@Netsoftmate1 dbs]$ mkdir -p /data2/cipfile


     

    9. Edit the pfile.

    [oracle@Netsoftmate1 dbs]$ vi initcipfile.ora




    Here we have modified the pfile by adding these
    parameters as follows.

    *.db_name=’cipfile’

    *.control_files=’/data2/cipfile/control01.ctl’,’/data2/cipfile/control02.ctl’

    *.db_file_name_convert=(‘/data2/prod’,’/data2/cipfile’)

    *.log_file_name_convert=(‘/data2/prod’,’/data2/cipfile’)

    10. Create password file in
    $ORACLE_HOME/dbs directory     

    /data1/oracle11g/product/dbs

    [oracle@Netsoftmate1 dbs]$ orapwd file=orapwcipfile
    password=ora123

    11. Start
    the instance in no mount stage    .     

    [oracle@Netsoftmate1 dbs]$ export ORACLE_SID=cipfile

    [oracle@Netsoftmate1 dbs]$ sqlplus

    SQL*Plus: Release 11.2.0.1.0 Production on Mon Apr 13
    11:59:27 2015

    Copyright (c) 1982, 2009, Oracle.  All rights reserved.

    Enter user-name: sys as sysdba

    Enter password:

    Connected to an idle instance.

    SQL> startup nomount

    ORACLE instance started.

    Total System Global Area 1152450560 bytes

    Fixed Size                  2212696 bytes

    Variable Size             352324776 bytes

    Database Buffers          788529152 bytes

    Redo Buffers                9383936 bytes

    SQL> exit

    Disconnected from Oracle Database 11g Enterprise
    Edition Release 11.2.0.1.0 – 64bit Production

    With the Partitioning, OLAP, Data Mining and Real
    Application Testing options

    [oracle@Netsoftmate1 dbs]$

    12. Connect to
    auxiliary instance via RMAN and issue the duplicate command.     

    [oracle@Netsoftmate1 dbs]$ echo $ORACLE_SID

    cipfile

    [oracle@Netsoftmate1 dbs]$ rman auxiliary sys/ora123

    Recovery Manager: Release 11.2.0.1.0 – Production on
    Mon Apr 13 12:03:23 2015

    Copyright (c) 1982, 2009, Oracle and/or its
    affiliates.  All rights reserved.

    connected to auxiliary
    database: CIPFILE (not mounted)

    RMAN>

    RMAN> DUPLICATE
    TARGET DATABASE TO cipfile BACKUP LOCATION ‘/backup/rman_backup’;

    Starting Duplicate Db at 13-APR-15

    contents of Memory Script:

    {

       sql clone
    “create spfile from memory”;

    }

    executing Memory Script

    sql statement: create spfile from memory

    contents of Memory Script:

    {

       shutdown
    clone immediate;

       startup
    clone nomount;

    }

    executing Memory Script

    Oracle instance shut down

    connected to auxiliary database (not started)

    Oracle instance started

    Total System Global Area    1152450560 bytes

    Fixed Size                     2212696 bytes

    Variable Size                335547560 bytes

    Database Buffers             805306368 bytes

    Redo Buffers                   9383936 bytes

    contents of Memory Script:

    {

       sql clone
    “alter system set  db_name =

     ”PROD”
    comment=

     ”Modified by
    RMAN duplicate” scope=spfile”;

       sql clone
    “alter system set  db_unique_name =

     ”CIPFILE”
    comment=

     ”Modified by
    RMAN duplicate” scope=spfile”;

       shutdown
    clone immediate;

       startup
    clone force nomount

       restore
    clone primary controlfile from 
    ‘/backup/rman_backup/controlfile_c-284539893-20150413-00’;

       alter clone
    database mount;

    }

    executing Memory Script

    sql statement: alter system set  db_name = 
    ”PROD” comment= ”Modified by RMAN duplicate” scope=spfile

    sql statement: alter system set  db_unique_name =  ”CIPFILE” comment= ”Modified by RMAN
    duplicate” scope=spfile

    Oracle instance shut down

    Oracle instance started

    Total System Global Area    1152450560 bytes

    Fixed Size                     2212696 bytes

    Variable Size                335547560 bytes

    Database Buffers             805306368 bytes

    Redo Buffers                   9383936 bytes

    Starting restore at 13-APR-15

    allocated channel: ORA_AUX_DISK_1

    channel ORA_AUX_DISK_1: SID=63 device type=DISK

    channel ORA_AUX_DISK_1: restoring control file

    channel ORA_AUX_DISK_1: restore complete, elapsed
    time: 00:00:03

    output file name=/data2/cipfile/control01.ctl

    output file name=/data2/cipfile/control02.ctl

    Finished restore at 13-APR-15

    database mounted

    released channel: ORA_AUX_DISK_1

    allocated channel: ORA_AUX_DISK_1

    channel ORA_AUX_DISK_1: SID=63 device type=DISK

    contents of Memory Script:

    {

       set until
    scn  5176531;

       set newname
    for datafile  1 to

     “/data2/cipfile/system01.dbf”;

       set newname
    for datafile  2 to

     “/data2/cipfile/sysaux01.dbf”;

       set newname
    for datafile  3 to

     “/data2/cipfile/undotbs01.dbf”;

       set newname
    for datafile  4 to

     “/data2/cipfile/users01.dbf”;

       set newname
    for datafile  5 to

     “/data2/cipfile/example01.dbf”;

       set newname
    for datafile  6 to

     “/data2/cipfile/data01.dbf”;

       set newname
    for datafile  7 to

     “/data2/cipfile/test01.dbf”;

       set newname
    for datafile  8 to

     “/data2/cipfile/tools01.dbf”;

       restore

       clone database

       ;

    }

    executing Memory Script

    executing command: SET until clause

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    Starting restore at 13-APR-15

    using channel ORA_AUX_DISK_1

    channel ORA_AUX_DISK_1: starting datafile backup set
    restore

    channel ORA_AUX_DISK_1: specifying datafile(s) to
    restore from backup set

    channel ORA_AUX_DISK_1: restoring datafile 00001 to
    /data2/cipfile/system01.dbf

    channel ORA_AUX_DISK_1: restoring datafile 00002 to
    /data2/cipfile/sysaux01.dbf

    channel ORA_AUX_DISK_1: restoring datafile 00003 to
    /data2/cipfile/undotbs01.dbf

    channel ORA_AUX_DISK_1: restoring datafile 00004 to
    /data2/cipfile/users01.dbf

    channel ORA_AUX_DISK_1: restoring datafile 00005 to
    /data2/cipfile/example01.dbf

    channel ORA_AUX_DISK_1: restoring datafile 00006 to
    /data2/cipfile/data01.dbf

    channel ORA_AUX_DISK_1: restoring datafile 00007 to
    /data2/cipfile/test01.dbf

    channel ORA_AUX_DISK_1: restoring datafile 00008 to
    /data2/cipfile/tools01.dbf

    channel ORA_AUX_DISK_1: reading from backup piece
    /backup/rman_backup/db_arch_2vq495ua_1_1.bkp

    channel ORA_AUX_DISK_1: piece
    handle=/backup/rman_backup/db_arch_2vq495ua_1_1.bkp tag=TAG20150413T101538

    channel ORA_AUX_DISK_1: restored backup piece 1

    channel ORA_AUX_DISK_1: restore complete, elapsed
    time: 00:00:45

    Finished restore at 13-APR-15

    contents of Memory Script:

    {

       switch clone
    datafile all;

    }

    executing Memory Script

    datafile 1 switched to datafile copy

    input datafile copy RECID=9 STAMP=876917132 file
    name=/data2/cipfile/system01.dbf

    datafile 2 switched to datafile copy

    input datafile copy RECID=10 STAMP=876917133 file
    name=/data2/cipfile/sysaux01.dbf

    datafile 3 switched to datafile copy

    input datafile copy RECID=11 STAMP=876917134 file
    name=/data2/cipfile/undotbs01.dbf

    datafile 4 switched to datafile copy

    input datafile copy RECID=12 STAMP=876917134 file
    name=/data2/cipfile/users01.dbf

    datafile 5 switched to datafile copy

    input datafile copy RECID=13 STAMP=876917135 file
    name=/data2/cipfile/example01.dbf

    datafile 6 switched to datafile copy

    input datafile copy RECID=14 STAMP=876917136 file
    name=/data2/cipfile/data01.dbf

    datafile 7 switched to datafile copy

    input datafile copy RECID=15 STAMP=876917137 file
    name=/data2/cipfile/test01.dbf

    datafile 8 switched to datafile copy

    input datafile copy RECID=16 STAMP=876917138 file
    name=/data2/cipfile/tools01.dbf

    contents of Memory Script:

    {

       set until
    scn  5176531;

       recover

       clone
    database

        delete
    archivelog

       ;

    }

    executing Memory Script

    executing command: SET until clause

    Starting recover at 13-APR-15

    using channel ORA_AUX_DISK_1

    starting media recovery

    channel ORA_AUX_DISK_1: starting archived log restore
    to default destination

    channel ORA_AUX_DISK_1: restoring archived log

    archived log thread=1 sequence=154

    channel ORA_AUX_DISK_1: reading from backup piece
    /backup/rman_backup/db_arch_30q49603_1_1.bkp

    channel ORA_AUX_DISK_1: piece
    handle=/backup/rman_backup/db_arch_30q49603_1_1.bkp tag=TAG20150413T101635

    channel ORA_AUX_DISK_1: restored backup piece 1

    channel ORA_AUX_DISK_1: restore complete, elapsed
    time: 00:00:01

    archived log file name=/backup/archive/1_154_872701561.dbf
    thread=1 sequence=154

    channel clone_default: deleting archived log(s)

    archived log file
    name=/backup/archive/1_154_872701561.dbf RECID=1 STAMP=876917145

    media recovery complete, elapsed time: 00:00:04

    Finished recover at 13-APR-15

    contents of Memory Script:

    {

       shutdown
    clone immediate;

       startup
    clone nomount;

       sql clone
    “alter system set  db_name =

     ”CIPFILE”
    comment=

     ”Reset to
    original value by RMAN” scope=spfile”;

       sql clone
    “alter system reset  db_unique_name
    scope=spfile”;

       shutdown
    clone immediate;

       startup
    clone nomount;

    }

    executing Memory Script

    database dismounted

    Oracle instance shut down

    connected to auxiliary database (not started)

    Oracle instance started

    Total System Global Area    1152450560 bytes

    Fixed Size      
                  2212696 bytes

    Variable Size                335547560 bytes

    Database Buffers             805306368 bytes

    Redo Buffers                   9383936 bytes

    sql statement: alter system set  db_name = 
    ”CIPFILE” comment= ”Reset to original value by RMAN” scope=spfile

    sql statement: alter system reset  db_unique_name scope=spfile

    Oracle instance shut down

    connected to auxiliary database (not started)

    Oracle instance started

    Total System Global Area    1152450560 bytes

    Fixed Size                     2212696 bytes

    Variable Size                335547560 bytes

    Database Buffers             805306368 bytes

    Redo Buffers                   9383936 bytes

    sql statement: CREATE CONTROLFILE REUSE SET DATABASE
    “CIPFILE” RESETLOGS ARCHIVELOG

     
    MAXLOGFILES     16

     
    MAXLOGMEMBERS      3

     
    MAXDATAFILES      100

     
    MAXINSTANCES     8

     
    MAXLOGHISTORY      292

     LOGFILE

      GROUP  1 ( ‘/data2/cipfile/redo01.log’ ) SIZE 50
    M  REUSE,

      GROUP  2 ( ‘/data2/cipfile/redo02.log’ ) SIZE 50
    M  REUSE,

      GROUP  3 ( ‘/data2/cipfile/redo03.log’ ) SIZE 50
    M  REUSE

     DATAFILE

     
    ‘/data2/cipfile/system01.dbf’

     CHARACTER SET
    WE8MSWIN1252

    contents of Memory Script:

    {

       set newname
    for tempfile  1 to

     “/data2/cipfile/temp01.dbf”;

       switch clone
    tempfile all;

       catalog
    clone datafilecopy 
    “/data2/cipfile/sysaux01.dbf”,

     “/data2/cipfile/undotbs01.dbf”,

     “/data2/cipfile/users01.dbf”,

     “/data2/cipfile/example01.dbf”,

     “/data2/cipfile/data01.dbf”,

     “/data2/cipfile/test01.dbf”,

     “/data2/cipfile/tools01.dbf”;

       switch clone
    datafile all;

    }

    executing Memory Script

    executing command: SET NEWNAME

    renamed tempfile 1 to /data2/cipfile/temp01.dbf in
    control file

    cataloged datafile copy

    datafile copy file name=/data2/cipfile/sysaux01.dbf
    RECID=1 STAMP=876917170

    cataloged datafile copy

    datafile copy file name=/data2/cipfile/undotbs01.dbf
    RECID=2 STAMP=876917170

    cataloged datafile copy

    datafile copy file name=/data2/cipfile/users01.dbf
    RECID=3 STAMP=876917171

    cataloged datafile copy

    datafile copy file name=/data2/cipfile/example01.dbf
    RECID=4 STAMP=876917171

    cataloged datafile copy

    datafile copy file name=/data2/cipfile/data01.dbf
    RECID=5 STAMP=876917172

    cataloged datafile copy

    datafile copy file name=/data2/cipfile/test01.dbf
    RECID=6 STAMP=876917172

    cataloged datafile copy

    datafile copy file name=/data2/cipfile/tools01.dbf
    RECID=7 STAMP=876917173

    datafile 2 switched to datafile copy

    input datafile copy RECID=1 STAMP=876917170 file
    name=/data2/cipfile/sysaux01.dbf

    datafile 3 switched to datafile copy

    input datafile copy RECID=2 STAMP=876917170 file name=/data2/cipfile/undotbs01.dbf

    datafile 4 switched to datafile copy

    input datafile copy RECID=3 STAMP=876917171 file
    name=/data2/cipfile/users01.dbf

    datafile 5 switched to datafile copy

    input datafile copy RECID=4 STAMP=876917171 file
    name=/data2/cipfile/example01.dbf

    datafile 6 switched to datafile copy

    input datafile copy RECID=5 STAMP=876917172 file
    name=/data2/cipfile/data01.dbf

    datafile 7 switched to datafile copy

    input datafile copy RECID=6 STAMP=876917172 file
    name=/data2/cipfile/test01.dbf

    datafile 8 switched to datafile copy

    input datafile copy RECID=7 STAMP=876917173 file
    name=/data2/cipfile/tools01.dbf

    contents of Memory Script:

    {

       Alter clone
    database open resetlogs;

    }

    executing Memory Script

    database opened

    Finished Duplicate Db at 13-APR-15

    RMAN>

    Conclusion
    In the preceding scenario based article, we have learned that backup based duplication using Rman utility with different database name and different directory structure, using db_file_name_convert and log_file_name_convert option in pfile

    BY
    Name: Mirza Hidayathullah Baig
    Designation: Senior Database Engineer
    Organization: Netsoftmate IT Solutions.
    Email: info@netsoftmate.com

  • Setup Oracle Golden Gate one way replication on windows server

    Overview:
    Oracle Golden Gate provides very fast replication of data by reading transaction logs and writing the changes to one or more target databases in a homogeneous and heterogeneous environment. It is useful for High Availability Architectures and especially for Data Warehouse and Decision Support Systems. Thus, the variety of techniques and methods spreads from unidirectional environments for query offloading/reporting to bidirectional or Peer-to-Peer architectures in an active-active fashion.

    Prerequisites:
    Oracle Golden Gate Software Installed
    Database should be in archive log mode
    Supplemental logging should be enable.
    Force logging should be set to yes. 


    Environment Details:

    Hostname
    PROD-DB1
    Ip Address
    10.0.0.0
    Operating
    system
    Windows server
    2012 R2
    Environment
    production
    Oracle Home
    D:oracle11204product11.2.0dbhome_1
    Golden Gate
    stage
    D:oraclegg_stage
    Golden Gate
    Home
    D:oraclegg_homeproduct12.1.2.1ggfor11
    OGG Processes
    m_shl01s,m_shl01t,e_shl01s,p_shl01s,r_shl01t

     
    Steps to setup Oracle Golden Gate One way Replication:
     1. Install Oracle Golden Gate Software, refer below link.

    Oracle Golden Gate Installation on windows

    2. Upgrade Oracle Golden gate version to latest release, Refer following link.

    Oracle Golden Gate upgrade on Windows Server


    3. Verify archive log mode, then enable the archive log mode.

    4. Verify force logging and supplemental logging are set to ‘YES’ and Enable goldengate replication.

    5. Create a file ” GOLBALS”

    GGSCI  5> edit param ./GLOBALS

    6. Set db_recovery_file_dest_size parameter.

    7. Create golden gate database tablespace, user account and grant permissions.


    8. Enable DDL replication, as sysdba run the following in sequence providing ggadmin as schema.


    9. Login as sysdba and execute role_setup then grant GGS_GGSUSER_ROLE then ddl_enable and ddl_pin.



    10. Enable sequence replication, as sysdba run the following script from gg home.

    11. Enable trandata for the tables to be replicated, login to database from ggsci.
    GGSCI (  as ggadmin@****) 4> add trandata <Schema_name>.*,

    GGSCI (  as ggadmin@****) 5> add trandata <Schema_name>.*,

    Prepare Source environment
     
    12. Create parameter file for manager

    PORT 7809
    purgeoldextracts ./dirdat/shelldb/sh*, usecheckpoints, minkeepdays 2
    lagreportminutes 1
    laginfominutes 10
    lagcriticalminutes 90

    — delay starting other process after rebooting servers by 30min
    –bootdelayminutes 20
    –autostart ER *

    — auto start pump processes to startup if network failed
    autorestart EXTRACT p*, retries 4, waitminutes 10
    autorestart EXTRACT e*, retries 4, waitminutes 10

    13. Verify manager parameter file.


    14. Create PARAM file for primary extract (e_shl01s).
    EXTRACT e_shl01s
    — add extract e_shl01s, tranlog, begin 2015-04-28, threads 2
    — add exttrail ./dirdat/****/sh, extract e_shl01s, megabytes 50

    — add extract e_shl01s, tranlog, begin now, threads 2
    — add exttrail ./dirdat/****/sh, extract e_shl01s, megabytes 50

    discardfile ./dirrpt/discard/e_shl01s.dsc, append megabytes 50

    SETENV (ORACLE_SID=’****’)
    SETENV (NLS_LANG = “AMERICAN_AMERICA.AR8MSWIN1256”)
    –SETENV (ORACLE_HOME=D:oracle11204product11.2.0dbhome_1)

    userid ggadmin, password ggadmin123

    — to read from asm
    — tranlogoptions dblogreader
    — tranlogoptions ASMUSER SYS@ASM1, ASMPASSWORD GCSasmadmin2015

    — to prevent looping in bidirectional replication or you can user excludetag
    tranlogoptions excludeuser ggadmin

    exttrail ./dirdat/shelldb/sh
    cachemgr cachesize 1GB
    fetchoptions usesnapshot, uselatestversion
    FETCHOPTIONS FETCHPKUPDATECOLS
    dboptions allowunusedcolumn

    include ./dirprm/include_reporting.inc
    –warnlongtrans 3H, chekinterval 1H

    logallsupcols

    ddl include all
    ddloptions addtrandata, report

    –include ./dirprm/HB_Extract.inc

    table ggadmin.ggsync;
    table <Schema_name>.*;
    table <Schema_name>.*;
    table <Schema_name>.*;
    table <Schema_name>.*;
    table <Schema_name>.*;
    table <Schema_name>.*;
    table test_user.*;

    sequence <Schema_name>.*;
    sequence <Schema_name>.*;
    sequence <Schema_name>.*;
    sequence <Schema_name>.*;
    sequence <Schema_name>.*;
    sequence <Schema_name>.*;
    sequence test_user.*;




    15. Create PARAM file for pump extract (p_shl01s).

    extract p_shl01s
    — add extrat p_shl01s, exttrailsource ./dirdat/****/sh
    — add rmttrail ./dirdat/****/th, extract p_shl01s, megabytes 50
    discardfile ./dirrpt/discard/p_shl01s.dsc, append megabytes 50
    passthru
    rmthost 10.10.10.10  mgrport 7809
    rmttrail ./dirdat/****/th
    include ./dirprm/include_reporting.inc

    –include ./dirprm/HB_pmp.inc

    table ggadmin.ggsync;
    table <Schema_name>.*;
    table <Schema_name>.*;
    table <Schema_name>.*;
    table <Schema_name>.*;
    table <Schema_name>.*;
    table <Schema_name>.*;
    table test_user.*;

    sequence <Schema_name>.*;
    sequence <Schema_name>.*;
    sequence <Schema_name>.*;
    sequence <Schema_name>.*;
    sequence <Schema_name>.*;
    sequence <Schema_name>.*;
    sequence test_user.*;


    16. View param file of pump extract.

    17. Add primary extract

    18. Add pump extract.

    19. Add checkpoint table.


    Prepare Target Environment
     
    19. Repeat the steps from 1 to 11 on Target site.

    20. Create parameter file for manager on target site.

    21. Create parameter for replicat process.

    replicat r_shl01t
    — add replicat r_shl01t, exttrail ./dirdat/****/th
    discardfile ./dirrpt/discard/r_shl01t.dsc, append megabytes 50

    SETENV (ORACLE_SID=’****’)
    SETENV (NLS_LANG = “AMERICAN_AMERICA.AR8MSWIN1256”)
    –SETENV (ORACLE_HOME=C:oraclerdbms11gproduct11.2.0dbhome_1)

    userid ggadmin, password ggadmin123
    AssumeTargetDefs
    — for triggers starting from 11.2.0.2 oracle automatically disable trigger fire
    dboptions suppresstriggers
    — for cascading const
    dboptions deferrefconst

    DDL include all
    –DDLSUBST ‘SYS_C0040303’ WITH ‘SYS_C0040298’
    –ddlerror 1031 ignore
    grouptransops 1000

    include ./dirprm/include_reporting.inc

    –include ./dirprm/HB_Rep.inc
    APPLYNOOPUPDATES

    map ggadmin.ggsync, target ggadmin.ggsync;
    Map <Schema_name>.*, Target <Schema_name>.*;
    Map <Schema_name>.*, Target <Schema_name>.*;
    Map <Schema_name>.*, Target <Schema_name>.*;
    Map <Schema_name>.*, Target <Schema_name>.*;
    Map <Schema_name>.*, Target <Schema_name>.*;
    Map <Schema_name>.*, Target <Schema_name>.*;
    Map test_user.*, Target test_user.*;

    22. View replicate parameter and start the process.



    Conclusion
    In above article we have learned that, how to setup Oracle Golden Gate One way Replication on windows Server, where we have prepared Source and Target sides with respective extract process.

    BY
    Name: Mirza Hidayathullah Baig
    Designation: Senior Database Engineer
    Organization: Netsoftmate IT Solutions.
    Email: info@netsoftmate.com