Data Guard - Physical Standby Maintenance

A physical standby database is an identical, block-for-block copy of the primary database and is kept in sync with the primary using media recovery. As redo gets generated on the primary database, it gets transferred to the standby database where an RFS process receives the primary redo and applies the change vectors directly to the standby database. A physical standby database is an excellent choice for disaster recovery. Hardware & OS configurations Starting up and Shutting down a physical standby database Role Transition   1. Hardware & OS configurations Primary Database - Oracle Release 11g (11.2.0) - Hostname: rhel5401.shannura.com (192.168.1.4) - Operating System: RHEL 5.4 (x64) - Database Name [DB_NAME]: 'dbuat01' - Database domain [DB_DOMAIN]: 'shannura.com' - Instance name [INSTANCE_NAME]: 'dbuat01' - Database Unique Name [DB_UNIQUE_NAME]: 'dbuat01' - TNS Name: 'dbuat01srv' - File structure: /u02/oradata/dbuat01 Standby Database - Oracle Release 11g (11.2.0) - Hostname: rhel5402.shannura.com (192.168.1.5) - Operating System: RHEL 5.4 (x64) - Database Name [DB_NAME]: 'dbuat01' - Database domain [DB_DOMAIN]: 'shannura.com' - Instance name [INSTANCE_NAME]: 'dbtau01' - Database Unique Name [DB_UNIQUE_NAME]: 'dbtau01' - TNS Name: 'dbtau01srv' - File structure: /u02/oradata/dbtau01   2. Starting up and Shutting down a physical standby database   Starting up a physical standby database when it is currently shutdown:   SQL> STARTUP MOUNT;   To start Redo Apply services:   SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;   To start for Read-Only access when it is currently shutdown:   SQL> STARTUP;   To open a standby database for Read-Only when it is currently runing in Redo-Apply or Real-Time Apply:   SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; SQL> ALTER DATABASE OPEN;   To open the standby database from Read-Only to Redo-Apply:   SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;   Shutting down a physical standby: - If the primary database is up and running, first 'DEFER' the destination on the primary database and perform a manual log switch before shutting down the standby database.   SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2 = 'DEFER' SCOPE = BOTH; SQL> ALTER SYSTEM SWITCH LOGFILE;   Issue the following query to verify if the standby database is performing Redo Apply or Real-Time Apply. If the MRP0 or MRP process exists, then the standby database is currently applying Redo.   SQL> SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;   If the Log Apply services are still running, cancel them using:   SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; SQL> SHUTDOWN;     3. Role Transition: Switchover Failover   Switchover: This operation is a reversible role transition. With switchover, primary can be made as standby and standby can be made as primary. A switchover operation takes place in two phases: the existing primary database is transitioned to a standby role. that means, temporarily the DG configuration will have two standby databases then the original standby database is transitioned to the primary role Switchover preparation:   Ensure following initialization parameters are already set:   [ Primary ]:   *.LOG_ARCHIVE_DEST_2 = 'SERVICE=dbtau01srv VALID_FOR=(online_logfiles,primary_role) DB_UNIQUE_NAME=dbtau01'   [ Standby ]:   *.LOG_ARCHIVE_DEST_2 = 'SERVICE=dbuat01srv VALID_FOR=(online_logfiles,primary_role) DB_UNIQUE_NAME=dbuat01'   If the DG configuration contains more than one standby databases, verify that the new primary database contains a LOG_ARCHIVE_DEST_n definition for each standby database. This ensures each participating standby database will continue to receive redo data from the new primary database.   - FAL_SERVER: FAL_SERVER parameter is set equal to all databases (primary and standby databases) - FAL_CLIENT: FAL_CLIENT parameter is set on each database to the TNS alias for the database requesting gap resolution. This value is used by the receiver of the gap request (FAL_SERVER) so that the archive process on the FAL server database can connect back to the requester.   [ Primary ]:   *.FAL_SERVER = 'dbuat01','dbtau01' *.FAL_CLIENT = 'dbuat01'   [ Standby ]:   *.FAL_SERVER = 'dbuat01','dbtau01' *.FAL_CLIENT = 'dbtau01'   To ensure a quick and efficient switchover, disconnect all user connections. If that is not possible, attempt to restrict user activity as much as possible. During the transition of the primary database to a standby, you can use the 'WITH SESSION SHUTDOWN' clause of the SWITCHOVER command to kill any user connections.   Verify the tempfiles on both primary and standby   [ Primary ]:   SQL> SELECT TS.NAME AS "TABLESPACE", TF.NAME AS "TEMPFILE", TF.STATUS AS "STATUS" FROM V$TABLESPACE TS JOIN V$TEMPFILE TF USING (TS#); TABLESPACE TEMPFILE STATUS ----------- ------------------------------------------------------- ------- TEMP /u02/oradata/dbuat01/data/ts_temp01.dbf ONLINE   [ Standby ]:   SQL> SELECT TS.NAME AS "TABLESPACE", TF.NAME as "TEMPFILE", TF.STATUS AS "STATUS" FROM V$TABLESPACE TS JOIN V$TEMPFILE TF USING (TS#); TABLESPACE TEMPFILE STATUS ----------- ------------------------------------------------------- ------- TEMP /u02/oradata/dbuat01/data/ts_temp01.dbf' ONLINE   Verify Redo is current with primary. Perform a couple of log switches from the primary and issue the following:   SQL> SELECT status, error FROM V$ARCHIVE_DEST WHERE dest_id = 2; STATUS ERROR -------- --------------------------------------------------------- VALID   Issue the following query on the standby to verify Redo Apply by querying the status of the MRP0 process in V$MANAGED_STANDBY.   SQL> SELECT CLIENT_PROCESS, PROCESS, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY; CLIENT_PROCESS PROCESS SEQUENCE# STATUS -------------- --------- ---------- ------------ ARCH ARCH 0 CONNECTED ARCH ARCH 0 CONNECTED ARCH ARCH 0 CONNECTED ARCH ARCH 0 CONNECTED N/A MRP0 122 WAIT_FOR_LOG N/A RFS 0 IDLE   If MRP0 record is not returned in the above query, then Redo Apply is not running. In order to ensure an efficient switchover process, Redo Apply should be started and caught with the current Redo stream:   SQL> STARTUP MOUNT SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;   [ or ]   SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;   If the MRP0 record is returned but shows a status as 'WAIT_FOR_GAP', then the switchover operation cannot continue until the Redo gap has been resolved. If the status shows as 'APPLYING_LOG', then the standby is current and using real-time apply and current with the primary database. Finally, if the status is returned as 'WAIT_FOR_LOG', the standby is not running in real-time apply (which is fine) or the DELAY qualifier was specified for the destination.   Perform Switchover operation with a Physical standby From the parimary database, query the V$DATABASE view: SQL> SELECT switchover_status FROM V$DATABASE; SWITCHOVER_STATUS ------------------------ TO STANDBY   If the status returns as 'TO STANDBY', then everything is good and a switchover can occur. Otherwise returned with 'SESSIONS ACTIVE', the SWITCHOVER command will need to be issed with the 'WITH SESSION SHUDOWN' clause. Cover the primary database into a physical standby database SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;   [ or ]   SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;   Shutdown and restart former primary as a new standby database. SQL> SHUT IMMEDIATE SQL> STARTUP MOUNT;   At this point, there are two standby databases. Now, we have to move the current standby as primary. Now, query on the former standby database. it should show as under: SQL> SELECT switchover_status FROMm V$DATABASE; SWITCHOVER_STATUS -------------------------- TO PRIMARY Now, convert the former standby to a primary database. SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;   [ or ]   SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;   To complete the transition of the standby to primary, the new primary database needs to be shutdown and restarted. SQL> SHUT IMMEDIATE SQL> STARTUP;   If you query STATUS, SWITCHOVER_STATUS in the new primary database, it will briefly show the status as:   SQL> select status, switchover_status from v$database, v$instance; STATUS SWITCHOVER_STATUS ------------ -------------------- OPEN RESOLVABLE GAP   Once the gap is resolved, it should automatically return as:   SQL> select status, switchover_status from v$database, v$instance; STATUS SWITCHOVER_STATUS ------------ -------------------- OPEN TO STANDBY   On the other hand, the new physical standby database will indicate the STATUS and SWITCHOVER_STATUS as:   SQL> select status, switchover_status from v$database, v$instance; STATUS SWITCHOVER_STATUS ------------ -------------------- OPEN RECOVERY NEEDED As a final step, issue either of the following statements from the new physical standby database to begin managed recovery operations: SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;   [ or ]   SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;   Once again query the instance STATUS and SWITHOVER_STATUS in the new standby database.   SQL> select status, switchover_status from v$database, v$instance; STATUS SWITCHOVER_STATUS ------------ -------------------- MOUNTED NOT ALLOWED   Now, perform a couple of log switches in the new primary and query the follwoing:   SQL> select status, error from v$archive_dest where dest_id = 2; STATUS ERROR --------- ----------------------------------------------------------------- VALID     Failover: This operation involves a standby database to the primary role and is typically used in response to a failure on the primary database. Query V$ARCHIVE_GAP on the target standby database to determine if there are gaps in the archive sequence. SQL> SELECT * FROM V$ARCHIVE_GAP;   If the above query against V$ARCHIVE_GAP returns no rows, there are no known archive log gaps. If it is missing, it might look like this:   SQL> SELECT * FROM V$ARCHIVE_GAP; THREAD# LOW_SEQUENCE# HIGH_SEQUENCE# ----------- ---------------- -------------- 1 134 139   From the above output, the physical standby database is currently missing arhived logs from sequence 134 - 139. Copy the missing archive logs must be registered with the target standby database: SQL> ALTER DATABASE REGISTER LOGFILE '/u02/oradata/dbuat01/archived_logs/dbuat01_1_134_4546546545.arc'; Perform terminal recovery on the target standby by issuing managed recovery mode with the FINISH keyword. If the physical standby database is configured with active STANDBY redo logs and you have not registered any partial archived redo logs, issue the following: SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH; If the standby database was not configured with the standby redo logs, or they are not active, you must enter the following: SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH SKIP STANDBY LOGFILE;   The above command will perform an incomplete recovery until the last SCN included in the latest archived redo logs available at the physical standby database. Finally convert the standby into primary: SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;   After issuing "COMMIT TO SWITCHOVER", we can no longer use this database as a standby database. If "COMMIT TO SWITCHOVER" fails, then try:   SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE; To complete the failover operation: SQL> SHUTDOWN IMMEDIATE SQL> STARTUP Perform a full backup of the new primary database!!!   [ Top ]