Data Guard: Switchover to a Physical Standby Database

(Step 1) Verify that the primary database can be switched to a physical standby database

• A value of TO STANDBY or SESSIONS ACTIVE indicates that the primary can be switched to a standby role.

SQL> select name, db_unique_name, switchover_status from v$database;

NAME   DB_UNIQUE_NAME   SWITCHOVER_STATUS
--------- ------------------------------ --------------------
DG11G   atlanta    TO STANDBY

(Step 2) Initiate the switchover of the primary database to a physical standby database

• After completion of this statement, the Data Guard configuration will have two physical standby databases and no primary.

SQL> alter database commit to switchover to physical standby with session shutdown;

Database altered.

(Step 3) Shutdown and then mount the former primary database

• After completion of this statement, the original primary is a standby database.
• You can see a graphical view of the switchover process here.

SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
...
Database mounted.

SQL> select name, db_unique_name, switchover_status, database_role from v$database;

NAME   DB_UNIQUE_NAME   SWITCHOVER_STATUS    DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
DG11G   atlanta    TO PRIMARY       PHYSICAL STANDBY

(Step 4) Check that the switchover target is ready to be switched to the primary role

• After completion of this statement, the original standby is a primary database.

SQL> select name, db_unique_name, switchover_status, database_role from v$database;


NAME   DB_UNIQUE_NAME   SWITCHOVER_STATUS    DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
DG11G   fresno    TO PRIMARY       PHYSICAL STANDBY

(Step 5) Switch the target physical standby database role to the primary role

SQL> alter database commit to switchover to primary with session shutdown;

Database altered.

SQL> select name, db_unique_name, switchover_status, database_role from v$database;

NAME   DB_UNIQUE_NAME   SWITCHOVER_STATUS    DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
DG11G   fresno    NOT ALLOWED       PRIMARY

(Step 6) Open the new primary database

SQL> alter database open;

Database altered.

(Step 7) Start Redo Apply on the new physical standby database

• You can see a graphical view of the switchover process here.

SQL> select name, db_unique_name, switchover_status, database_role from v$database;

NAME   DB_UNIQUE_NAME   SWITCHOVER_STATUS    DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
DG11G   atlanta    RECOVERY NEEDED      PHYSICAL STANDBY


SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

Data Guard: Switchover to a Physical Standby (II)

Snapshot Standby: Role Transition cycle

Create, test, resynchronize with production, create again

Q&As: Data Guard, Role Transitions (I)

(Q) Which 6 steps must be performed to prepare for a role transition?

(1) Verify that each database is properly configured for the role that it is about to assume

• Check that necessary initialization parameters are properly configured in each database
• FAL_SERVER, DB_FILE_NAME_CONVERT, LOG_FILE_NAME_CONVERT
• LOG_ARCHIVE_DEST_n and LOG_ARVHIVE_DEST_STATE_n
• Check that ONLINE REDO LOGs and STANDBY REDO LOGs exist and are properly configured on all databases

SQL> select status, type, member, group#
  2* from v$logfile;

STATUS TYPE MEMBER         GROUP#
------- ------- ------------------------------------------------------------ ----------
 ONLINE /u01/app/oracle/oradata/fresno/redo03.log         3
 ONLINE /u01/app/oracle/oradata/fresno/redo02.log         2
 ONLINE /u01/app/oracle/oradata/fresno/redo01.log         1
 STANDBY /u01/app/oracle/oradata/fresno/standby_redo01.log        4
 STANDBY /u01/app/oracle/oradata/fresno/standby_redo02.log        5
 STANDBY /u01/app/oracle/oradata/fresno/standby_redo03.log        6
 STANDBY /u01/app/oracle/oradata/fresno/standby_redo04.log        7

7 rows selected.

(2) Verify that there are no Redo Transport errors or Redo Gaps at the standby database

• For LOG_ARCHIVE_DEST_2, STATUS should be VALID and GAP_STATUS should be NO GAP

(On the Physical Standby:)
SQL> select dest_name, status, type, gap_status
  2  from v$archive_dest_status;

DEST_NAME            STATUS    TYPE            GAP_STATUS
------------------- --------- --------------- ----------
LOG_ARCHIVE_DEST_1   VALID     LOCAL
LOG_ARCHIVE_DEST_2   VALID     UNKNOWN         NO GAP
...
STANDBY_ARCHIVE_DEST VALID     UNKNOWN         NO GAP

(On the primary database)
SQL> select dest_name, status, type, gap_status
  2* from v$archive_dest_status;

DEST_NAME            STATUS    TYPE           GAP_STATUS
-------------------- --------- -------------- ------------------------
LOG_ARCHIVE_DEST_1   VALID     LOCAL
LOG_ARCHIVE_DEST_2   VALID     PHYSICAL       NO GAP
LOG_ARCHIVE_DEST_3   INACTIVE  LOCAL

(3) Verify that temporary files exist on the Standby that match the temporary files on the primary database


(4) Remove any delay in applying redo that may be in effect on the standby database that will become the new primary database

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY DISCONNECT FROM SESSION;

(5) Before performing a switchover from an Oracle RAC primary databsae to a physical standby, shut down all but one primary db instance


(6) If the physical standby that will become primary on the switchover is in real-time query mode, consider bringing all instances of the standby database to the mounted but not open state. This will allow:

• (a) the fastest possible role transition and/li>
  
• (b) to cleanly terminate any user sessions connected to the physical standby prior to the role transition

DataGuard: Configuring a Physical Standby

Configure Primary Server

Primary Server Setup

(1) Is database in Archivelog Mode?

Home -> Availability -> Recovery Settings

SQL> SELECT log_mode FROM v$database;

LOG_MODE
------------
ARCHIVELOG

If database is in Noarchivelog mode, you need to switch it to Archivelog mode.

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;

(2) Is Force Logging enabled?

SQL> select force_logging from v$database;
FOR
---
NO

SQL> alter database force logging;
Database altered.

(3) Edit initialization parameters for the primary role

db_name	db_unique_name	log_archive_config	log_archive_dest_1
log_archive_dest_2	log_archive_dest_state_1	log_archive_dest_state_2	fal_server
db_file_name_convert	log_file_name_convert	remote_login_passwordfile

Home -> Server -> Initialization Parameters

SQL> select name, db_unique_name from v$database;

NAME   DB_UNIQUE_NAME
--------- ------------------------------
DG11G   atlanta

SQL> show parameter db_name;

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
db_name         string  dg11g

SQL> show parameter db_unique_name;

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
db_unique_name        string  atlanta

SQL> alter system set log_archive_config='dg_config=(atlanta, fresno)';  
System altered.    
    
SQL> show parameter log_archive_config    
    
NAME                         TYPE    VALUE    
---------------------------- ------- ------------------------------    
log_archive_config           string  dg_config=(atlanta, fresno)    
    
Obs.    
   In this configuration, primary=> atlanta; standby=> fresno    

SQL> show parameter log_archive_dest_

NAME        TYPE  VALUE
---------------------------- ----------- ----------------------------
log_archive_dest_            string  location=use_db_recovery_file_
             dest valid_for=(all_logfiles,
      all_roles) db_unique_name=atla
      nta
...
NAME              TYPE    VALUE
--------------------------- ----------- ------------------------------
log_archive_dest_2          string  service=fresno async valid_for
      =(online_logfiles, primary_rol
      e) db_unique_name=fresno
...

Home -> Server -> Initialization Parameters

SQL> show parameter fal_server 
 
NAME         TYPE  VALUE 
------------------------------------ ----------- ------------------------------ 
fal_server        string  fresno 

Home -> Server -> Initialization Parameters

SQL> show parameter remote_login_pa

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile      string  EXCLUSIVE

SQL> alter system set db_file_name_convert='fresno', 'atlanta' scope=spfile; 
System altered. 

SQL> alter system set log_file_name_convert='fresno', 'atlanta' scope=spfile; 
System altered. 

SQL> alter system set standby_file_management=auto; 
System altered. 

Home -> Server -> Initialization Parameters -> Standby Database

(4) Configure Oracle Net for Redo Transport

Redo Transport moves archived redo logs from the primary to the standby database.
The two databases need to be connected through Oracle Net.
For this you can edit tnsnames.ora files in each server. i.e.

# Generated by Oracle configuration tools.

ATLANTA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oe5srv.localdomain)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = DG11G)
    )
  )

FRESNO =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oe5clt.localdomain)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = DG11G)
    )
  )

(5) create a backup copy of the primary database datafiles

Using RMAN:

Home -> Server -> Availability -> Schedule Backup

• Select a whole Database backup and enter host credentials

Home -> Server -> Availability -> Schedule Customized Backup -> Options

Home -> Server -> Availability -> Schedule Customized Backup -> Settings

Home -> Server -> Availability -> Schedule Backup -> Schedule

Home -> Server -> Availability -> Schedule Backup -> Review

Home -> Server -> Availability -> Schedule Backup -> Summary

(6) Create a Control file for the Physical Standby Database

SQL> alter database create standby controlfile as '/tmp/fresno_stdby.ctl';

Database altered.

(7) Create a Parameter File for the Physical Standby Database.
Edit the Parameter file to configure the settings for the Standby

(3.1) Create a PFILE from the SPFILE used by the Primary DB

SQL> create pfile= '/tmp/initfresno.ora' from spfile;
File created.

(3.2) Modify the PFILE to be used on a Physical Standby
$ cat initfresno.ora 
dg11g.__db_cache_size=67108864
dg11g.__java_pool_size=4194304
dg11g.__large_pool_size=4194304
dg11g.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
dg11g.__pga_aggregate_target=150994944
dg11g.__sga_target=205520896
dg11g.__shared_io_pool_size=0
dg11g.__shared_pool_size=117440512
dg11g.__streams_pool_size=4194304
*.audit_file_dest='/u01/app/oracle/admin/fresno/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/fresno/control01.ctl',
                '/u01/app/oracle/flash_recovery_area/fresno/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='atlanta', 'fresno'
*.db_name='dg11g'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4039114752
*.db_unique_name='fresno'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dg11gXDB)'
*.fal_server='atlanta'#In case of a switchover, fetch records from the fal_server
*.log_archive_config='dg_config=(atlanta, fresno)'#db_unique_names of the databases in the DG configuration
*.log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles, all_roles) 
                            db_unique_name=fresno'#configure local redo log archiving. Use Fast recovery Area
*.log_archive_dest_2='service=atlanta async valid_for=(online_logfiles, primary_role) 
        db_unique_name=atlanta'#configure remote log archiving. Configure redo transport to physical standby
*.log_archive_format='%t_%s_%r.dbf'
*.log_file_name_convert='atlanta', 'fresno'
*.memory_target=356515840
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

Standby Server Setup

(1) Create appropriate directories in the Standby server. Copy files from the prymary to the standby system.

(8.0) Create directories for fresno database files
$ mkdir -p oradata/fresno
$ mkdir -p flash_recovery_area/fresno
$ mkdir -p admin/fresno/adump

(8.1) Copy the standby control files to all locations (in the same server)
$ scp oracle@oe5srv:/tmp/fresno.ctl /u01/app/oracle/oradata/fresno/control01.ctl
$ cp /u01/app/oracle/oradata/fresno/control01.ctl /u01/app/oracle/flash_recovery_area/fresno/control02.ctl

(8.2) Copy Archivelogs and backups (in the same server)
$ scp -r oracle@oe5srv:/u01/app/oracle/flash_recovery_area/ATLANTA/archivelog  /u01/app/oracle/flash_recovery_area/fresno
$ scp -r oracle@oe5srv:/u01/app/oracle/flash_recovery_area/ATLANTA/backupset  /u01/app/oracle/flash_recovery_area/fresno

(8.3) Copy passwordfile and parameter file
$ scp -r oracle@oe5srv:$ORACLE_HOME/dbs/orapwdg11g $ORACLE_HOME/dbs
$ scp -r oracle@oe5srv:/tmp/initfresno.ora /tmp/initfresno.ora

(2) Create SPFILE from the edited parameter file

oracle@oe5clt:$ echo $ORACLE_SID  
dg11g

oracle@oe5clt:$ sqlplus / as sysdba 
SQL*Plus: Release 11.2.0.1.0 Production on Thu Mar 8 00:37:44 2012
Copyright (c) 1982, 2009, Oracle.  All rights reserved.  
  
Connected to an idle instance.  

SQL> create spfile from pfile='/tmp/initfresno.ora';
  
File created.  

(3) Restore backup

RMAN> startup mount; 
 
Oracle instance started 
database mounted 

RMAN> restore database; 
 
Starting restore at 08-MAR-12 
using channel ORA_DISK_1 
 
channel ORA_DISK_1: starting datafile backup set restore 
channel ORA_DISK_1: specifying datafile(s) to restore from backup set 
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/fresno/system01.dbf 
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/fresno/sysaux01.dbf 
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/fresno/undotbs01.dbf 
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/fresno/users01.dbf 
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/FRESNO/backupset/o1_mf_nnndf_BACKUP_ATLANTA_000_0_7ojnc7g1_.bkp 
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/FRESNO/backupset/o1_mf_nnndf_BACKUP_ATLANTA_000_0_7ojnc7g1_.bkp tag=BACKUP_ATLANTA_000_030712102725 
channel ORA_DISK_1: restored backup piece 1 
channel ORA_DISK_1: restore complete, elapsed time: 00:02:37 
Finished restore at 08-MAR-12 

(4) Create Online and Standby Redo logs on the Standby Database

SQL> alter system set standby_file_management=manual;

System altered.

SQL> alter database add logfile('/u01/app/oracle/oradata/fresno/online_redo01.log') size 50m;

Database altered.

SQL> alter database add logfile('/u01/app/oracle/oradata/fresno/online_redo02.log') size 50m;

Database altered.

SQL> alter database add logfile('/u01/app/oracle/oradata/fresno/online_redo03.log') size 50m;

Database altered.

SQL> alter system set standby_file_management=auto;

System altered.


SQL> alter database add standby logfile('/u01/app/oracle/oradata/fresno/standby_redo01.log') size 50m;

Database altered.

SQL> alter database add standby logfile('/u01/app/oracle/oradata/fresno/standby_redo02.log') size 50m;

Database altered.

SQL> alter database add standby logfile('/u01/app/oracle/oradata/fresno/standby_redo03.log') size 50m;

Database altered.

SQL> alter database add standby logfile('/u01/app/oracle/oradata/fresno/standby_redo04.log') size 50m;

Database altered.

Start Apply Service

SQL> Alter database recover managed standby database disconnect from session;

Database altered.