Oracle Dataguard Switchover

Oracle Dataguard Switchover steps

1. Initiate the switchover on the primary database PRIM:
SQL>connect / as sysdba
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

2. After step 1 finishes, Switch the original physical standby db STAN to primary role;
Open another prompt and connect to SQLPLUS:
SQL>connect / as sysdba
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

3. Immediately after issuing command in step 2, shut down and restart the former primary instance PRIM:
SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP MOUNT;

4. After step 3 completes:
- If you are using Oracle Database 10g release 1, you will have to Shut down and restart the new primary database STAN.
SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP;

- If you are using Oracle Database 10g release 2, you can open the new Primary database STAN:
SQL>ALTER DATABASE OPEN;

STAN is now transitioned to the primary database role.

5. On the new primary database STAN, perform a SWITCH LOGFILE to start sending redo data to the standby database PRIM.
SQL>ALTER SYSTEM SWITCH LOGFILE;

ORACLE DATA GUARD (Disaster Recovery)

                               
                        ********************************
                                   DATA GUARD
                        ********************************                                 
                                

DATA GUARD:
Data Guard is the name for Oracle's standby database solution, used for disaster recovery and high availability. Its nothing but a consistent copy of a database.

types of standby database:
a.Physical Standby Database
b.Logical Standby Database

A.PHYSICAL STANDBY DATABASE CREATION
====================================
prim1ary database : " prim1 "
Standby database : " stan1 "

Step  1: Make sure that o/s and architecture on both prim1ary and Secondary are same.

Step  2: Install oracle same version software for prim1ary and Secondary.

Step  3: Check whether prim1ary Database is in archive logmode.(Must)

        To Check : archive log list;
    If not enable then,
     >shut immediate
     >startup mount
     >alter database archivelog;
     >alter database open;
      
  
Step  4: Change the database in logging Mode:

           > alter database force logging;

Step  5: Check whether this database have Password File:
        ( If it doesnt exit create Password File and both the passwords should be same )

          > select * from v$pwfile_users;
    
Step  6: Create Three Redo-log files to standby database(prim1ary Side):
         
          >alter database add standby logfile group 4 '/u01/app/oracle/oradata/prim1/redo04.log' size 50m;
          >alter database add standby logfile group 5 '/u01/app/oracle/oradata/prim1/redo05.log' size 50m;
          >alter database add standby logfile group 6 '/u01/app/oracle/oradata/prim1/redo06.log' size 50m;
      
        Check the standby logfile : select * from v$standby_log;

Step  7: create pfile:
      
        >create pfile from spfile;
       
        >shut immediate;

    >exit

Step  8: Edit Pfile and Parameters in prim1ary:

         cd /u01/app/oracle/product/10.2.0/db_1/dbs

    ls -lrt

    vi initprim1.ora
      
        Adding parameters in Pfile(primary):add at the end
      
     db_unique_name=prim1
         LOG_ARCHIVE_CONFIG='DG_CONFIG=(prim1,stan1)'
            LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prim1'
     LOG_ARCHIVE_DEST_2='SERVICE=stan1 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stan1'
     LOG_ARCHIVE_DEST_STATE_1=ENABLE
     LOG_ARCHIVE_DEST_STATE_2=ENABLE
     LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
     LOG_ARCHIVE_MAX_PROCESSES=30
     remote_login_passwordfile='EXCLUSIVE'
     FAL_SERVER=stan1
     FAL_CLIENT=prim1
     STANDBY_FILE_MANAGEMENT=AUTO
     DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/stan1','/u01/app/oracle/oradata/prim1'
     LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/stan1','/u01/app/oracle/oradata/prim1'

Step  9: Shutdown and Startup the prim1ary database using Pfile.
        
         >sqlplus "/as sysdba"

         >startup nomount pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initprim1.ora';


Step 10: Create spfile from pfile and shutdown the prim1ary database;
       
      >create spfile from pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initprim1.ora';

     >Shut immediate;

Step 11: Create necessary directorires for Standby database:
       
         cd /u01/app/oracle/admin   
     mkdir stan1
         cd /u01/app/oracle/admin/stan1
     mkdir adump bdump cdump udump

         cd /u01/app/oracle/oradata  
     mkdir stan1


Step 12: Copy the database file, log file from prim1ary to standby database:

        cd /u01/app/oracle/oradata/prim1
       
         $cp *.dbf *.log /u01/app/oracle/oradata/stan1


Step 13: startup the prim1ary database;
   
    >sqlplus "/as sysdba"
   
    > Startup mount
      
        >alter database create standby controlfile as '/u01/app/oracle/oradata/stan1/control01.ctl';

        > alter database open;

Step 14: Create Password file for standby database:
       
         $cd /u01/app/oracle/product/10.2.0/db_1/dbs
  
         cp orapwprim1 orapwstan1
   
Step 15: Check the Listener

    ps -ef|grep tns
       (or)
    $lsnrctl status

    if its down then start the listener

    $lsnrctl start
       
Steps 16: Check the tns entries

    tnsping <Database name>

    if not working then Edit and Put the TNS Entry for databases.

         $vi /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora


Step 17: Copy the pfile from prim1ary to standby database:

         $cd  /u01/app/oracle/product/10.2.0/db_1/dbs
         $cp initprim1.ora initstan1.ora
       
       edit the pfile for standby, replace with :%s/prim1/stan1 except dba_name parameter because db_name should be same for primary     and standby and add below

parameters without any change.

vi initstan1.ora

    db_name='prim1'
    db_unique_name=stan1
    LOG_ARCHIVE_CONFIG='DG_CONFIG=(prim1,stan1)'
    LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stan1'
    LOG_ARCHIVE_DEST_2='SERVICE=prim1 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prim1'
    LOG_ARCHIVE_DEST_STATE_1=ENABLE
    LOG_ARCHIVE_DEST_STATE_2=ENABLE
    LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
    LOG_ARCHIVE_MAX_PROCESSES=30
    FAL_SERVER=prim1
    FAL_CLIENT=stan1
    remote_login_passwordfile='EXCLUSIVE'
    DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/prim1','/u01/app/oracle/oradata/stan1'
    LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/prim1','/u01/app/oracle/oradata/stan1'
    STANDBY_FILE_MANAGEMENT=AUTO

     
  NOTE: Mention proper standby controlfile location(since you have only one controlfile, so delete 2,3 control files in standby pfile)

Step 18: edit oratab and Startup the standby database in nomount stage and create spfile:
     

       edit the oratab for standby
       vi /etc/oratab
          
      . oraenv
       stan1  
    sqlplus "/as sysdba"
 
         startup nomount pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initstan1.ora';
      
         >create spfile from pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initstan1.ora';

         > shut immediate

         > startup mount

Step 19: Start MRP process in Standby database Side:
 
        stan1:
    ======
         >alter database recover managed standby database disconnect from session;

      
Step 20: Check the Sequence and applied in standby database:

          prim1:
         =======
         > archive log list
         >alter system switch logfile; - do it for few times

        stan1:
    ======

         >select sequence#,applied from v$archived_log order by sequence#;


**************************** END**************************