Monitoring Primary and Physical Standby Databases
There are a number of Oracle background processes that play a key role, first the primary database
- LGWR - log writer process flushes from the SGA to the ORL files
- LNS - LogWriter Network Service reads redo being flushed from the redo buffers by the LGWR and performs a network send of the redo to the standby
- ARCH - archives the ORL files to archive logs, that also used to fulfill gap resolution requests, one ARCH processes is dedicated to local redo log activity only and never communicates with a standby database
- RFS - Remote File Server process performs a network receive of redo transmitted from the primary and writes the network redo to the standby redo log (SRL) files.
- ARCH - performs the same as the primary but on the standby
- MRP - Managed Recover Process coordinates media recovery management, recall that a physical standby is in perpetual recovery mode
- LSP - Logical Standby Process coordinates SQL apply, this process only runs in a logical standby
- PR0x - recovery server process reads redo from the SRL or archive log files and apply this redo to the standby database.
the above mentioned process can be verified using the SQL script below. Corresponding process related to primary and standby will be displayed depends where this SQL is executed i.e. Primary or Standby
Monitor Log shipping on Primary database
SET PAGESIZE 124
COL DB_NAME FORMAT A8
COL HOSTNAME FORMAT A12
COL LOG_ARCHIVED FORMAT 999999
COL LOG_APPLIED FORMAT 999999
COL LOG_GAP FORMAT 9999
COL APPLIED_TIME FORMAT A12
SELECT DB_NAME,
HOSTNAME,
LOG_ARCHIVED,
LOG_APPLIED,
APPLIED_TIME,
LOG_ARCHIVED - LOG_APPLIED LOG_GAP
FROM (SELECT NAME DB_NAME FROM V$DATABASE),
(SELECT UPPER (
SUBSTR (
HOST_NAME,
1,
(DECODE (INSTR (HOST_NAME, '.'),
0, LENGTH (HOST_NAME),
(INSTR (HOST_NAME, '.') - 1)))))
HOSTNAME
FROM V$INSTANCE),
(SELECT MAX (SEQUENCE#) LOG_ARCHIVED
FROM V$ARCHIVED_LOG
WHERE DEST_ID = 1 AND ARCHIVED = 'YES'),
(SELECT MAX (SEQUENCE#) LOG_APPLIED
FROM V$ARCHIVED_LOG
WHERE DEST_ID = 2 AND APPLIED = 'YES'),
(SELECT TO_CHAR (MAX (COMPLETION_TIME), 'DD-MON/HH24:MI') APPLIED_TIME
FROM V$ARCHIVED_LOG
WHERE DEST_ID = 2 AND APPLIED = 'YES');
see if the MRP process is running on standby
SELECT PROCESS from V$MANAGED_STANDBY where PROCESS like 'MRP%';
last log applied and received along with log time on Standby database
SELECT 'Last Applied : ' Logs,
TO_CHAR (next_time, 'DD-MON-YY:HH24:MI:SS') Time
FROM v$archived_log
WHERE sequence# = (SELECT MAX (sequence#)
FROM v$archived_log
WHERE applied = 'YES')
UNION
SELECT 'Last Received : ' Logs,
TO_CHAR (next_time, 'DD-MON-YY:HH24:MI:SS') Time
FROM v$archived_log
WHERE sequence# = (SELECT MAX (sequence#) FROM v$archived_log);
Last sequence # received and applied on the standby database
SELECT al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"
FROM ( SELECT thread# thrd, MAX (sequence#) almax
FROM v$archived_log
WHERE resetlogs_change# = ( SELECT resetlogs_change# FROM v$database)
GROUP BY thread#) al,
( SELECT thread# thrd, MAX (sequence#) lhmax
FROM v$log_history
WHERE first_time = ( SELECT MAX (first_time) FROM v$log_history)
GROUP BY thread#) lh
WHERE al.thrd = lh.thrd;
Apply rate: To find out the speed of media recovery in a standby database, you can use this query:
Archive Lag Histogram: The V$STANDBY_EVENT_HISTOGRAM view came with 11gR2 and shows the historical occurance of archive lags in terms of seconds.
Apply rate: To find out the speed of media recovery in a standby database, you can use this query:
set lines 200
col type format a30
col ITEM format a20
col comments format a20
select * from v$recovery_progress;
SQL> col name format a10
SQL> select * from V$STANDBY_EVENT_HISTOGRAM;
Verify from Primary if the Real-time apply is being used
SQL> SELECT DEST_ID, RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID=2;
DEST_ID RECOVERY_MODE
------- ------------------------
2 MANAGED REAL TIME APPLY
Managing a Physical Standby Database
Below steps are related to day-to-day operations of the Oracle Data Guard environment. These operations include starting the standby database, enabling managed recovery, opening the standby in read-only, as well as general maintenance tasks.
Starting a Physical Standby
Before Oracle 10g
SQL> startup nomount;
SQL> alter database mount standby database;
Starting with Oracle 10g, the start-up task can be done in single step
SQL> startup mount;
During startup, Oracle will read the controlfile when mounting the database to make the determination to mount the database as astandby database or as a primary database.
Starting Managed Recovery:
Once the standby database has been started, it will begin receiving redo data from the primary database. This redo data will stack up in the form of archivelogs until we instruct the standby database to begin applying the redo data to the standby database. For a physical standby, the redo application is performed via the MRP.
SQL> alter database recovery managed standby database;
This above command will appear to hang because MRP is part of the session that it was started in. If we cancel out of this session, the MRP will also exit. To avoid this we need to run the MRP in the background as below
SQL> alter database recover managed standby database disconnect;
If the physical standby has standby redo logs configured, it is possible to have the MRP begin applying changes as soon as they arrive to the standby instead of waiting for the standby redo log to be archived. This functionality was introduced in 10g and is called Real-Time Apply. Real-Time Apply can shorten the role transition time by minimizing the redo that needs to be applied
To start Real-Time Apply, you initiate MRP by issuing the following command:
SQL> alter database recover managed standby database using current logfile disconnect;
Stopping Managed Recovery:
We could simply perform a normal shutdown on the database, or we could cancel managed recovery,
leaving the standby database up and running. To cancel managed recovery, issue the following:
SQL> alter database recover managed standby database cancel;
Starting and Stopping Active Data Guard:
The actual process of enabling Active Data Guard is simple: Open the physical standby database in read-only mode and start Redo Apply. A physical standby database instance cannot be opened if Redo Apply is active on a mounted instance of that database. The Data Guard physical standby should be in
one of two states prior to enabling Active Data Guard:
1. The standby database is mounted and Redo Apply is running.
2. The standby database has been shut down cleanly and Redo Apply was stopped.
In the first scenario, proceed as follows using SQL*Plus
1. Stop Redo Apply:
SQL> RECOVER MANAGED STANDBY DATABASE CANCEL;
2. Open the database read-only:
SQL> ALTER DATABASE OPEN READ ONLY;
3. Restart Redo Apply:
SQL> RECOVER MANAGED STANDBY DATABASE DISCONNECT USING CURRENT LOGFILE;
In the second scenario, where the physical standby and Redo Apply are already shut down,
proceed as follow using SQL*Plus alone,
1. Start the physical standby in read-only mode.
SQL> STARTUP
2. Start Redo Apply.
SQL> RECOVER MANAGED STANDBY DATABASE DISCONNECT USING CURRENT LOGFILE;
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
Starting a Physical Standby
Before Oracle 10g
SQL> startup nomount;
SQL> alter database mount standby database;
Starting with Oracle 10g, the start-up task can be done in single step
SQL> startup mount;
During startup, Oracle will read the controlfile when mounting the database to make the determination to mount the database as astandby database or as a primary database.
Starting Managed Recovery:
Once the standby database has been started, it will begin receiving redo data from the primary database. This redo data will stack up in the form of archivelogs until we instruct the standby database to begin applying the redo data to the standby database. For a physical standby, the redo application is performed via the MRP.
SQL> alter database recovery managed standby database;
This above command will appear to hang because MRP is part of the session that it was started in. If we cancel out of this session, the MRP will also exit. To avoid this we need to run the MRP in the background as below
SQL> alter database recover managed standby database disconnect;
If the physical standby has standby redo logs configured, it is possible to have the MRP begin applying changes as soon as they arrive to the standby instead of waiting for the standby redo log to be archived. This functionality was introduced in 10g and is called Real-Time Apply. Real-Time Apply can shorten the role transition time by minimizing the redo that needs to be applied
To start Real-Time Apply, you initiate MRP by issuing the following command:
SQL> alter database recover managed standby database using current logfile disconnect;
Stopping Managed Recovery:
We could simply perform a normal shutdown on the database, or we could cancel managed recovery,
leaving the standby database up and running. To cancel managed recovery, issue the following:
SQL> alter database recover managed standby database cancel;
Starting and Stopping Active Data Guard:
The actual process of enabling Active Data Guard is simple: Open the physical standby database in read-only mode and start Redo Apply. A physical standby database instance cannot be opened if Redo Apply is active on a mounted instance of that database. The Data Guard physical standby should be in
one of two states prior to enabling Active Data Guard:
1. The standby database is mounted and Redo Apply is running.
2. The standby database has been shut down cleanly and Redo Apply was stopped.
In the first scenario, proceed as follows using SQL*Plus
1. Stop Redo Apply:
SQL> RECOVER MANAGED STANDBY DATABASE CANCEL;
2. Open the database read-only:
SQL> ALTER DATABASE OPEN READ ONLY;
3. Restart Redo Apply:
SQL> RECOVER MANAGED STANDBY DATABASE DISCONNECT USING CURRENT LOGFILE;
In the second scenario, where the physical standby and Redo Apply are already shut down,
proceed as follow using SQL*Plus alone,
1. Start the physical standby in read-only mode.
SQL> STARTUP
2. Start Redo Apply.
SQL> RECOVER MANAGED STANDBY DATABASE DISCONNECT USING CURRENT LOGFILE;
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
Snapshot standby database
Oracle 11g has a feature in this area called : Snapshot standby database. A Snapshot Standby Database is a fully update-able standby database that is created by converting a physical standby database into a snapshot standby database for read and write operation for a short period of time thus making it possible to process transactions independently of the primary database
As per the MOS ID 443720.1 Snapshot database has following characteristics
1. Snapshot standby database continues to receive and archive redo data from primary but does not archive it
2. Redo data received from the primary database is applied automatically once it is converted back into a physical standby database.
3. Data from the primary database is always protected as the archives are being received and stored in place.
4. All local updates will be discarded when snapshot database is converted back to physical standby database.
5. If the primary database moves to new database branch (for example, because of a Flashback Database or an OPEN RESETLOGS), the snapshot standby database will continue accepting redo from new database branch.
6. Snapshot standby database cannot be the target of a switchover or failover. A snapshot standby database must first be converted back into a physical standby database before performing a role transition to it.
7. After a switchover or failover between the primary database and one of the physical or logical standby databases in a configuration, the snapshot standby database can receive redo data from the new primary database after the role transition.
8. Snapshot standby database cannot be the only standby database in a Maximum Protection Data Guard configuration.
Steps to convert the Physical Standby to Snapshot Standby Database
1) If not already configured , configure flash recovery area as given below
These steps applies to Oracle Server - Enterprise Edition - Version 11.1.0.6 to 11.2.0.2 [Release 11.1 to 11.2]
1. Snapshot standby database continues to receive and archive redo data from primary but does not archive it
2. Redo data received from the primary database is applied automatically once it is converted back into a physical standby database.
3. Data from the primary database is always protected as the archives are being received and stored in place.
4. All local updates will be discarded when snapshot database is converted back to physical standby database.
5. If the primary database moves to new database branch (for example, because of a Flashback Database or an OPEN RESETLOGS), the snapshot standby database will continue accepting redo from new database branch.
6. Snapshot standby database cannot be the target of a switchover or failover. A snapshot standby database must first be converted back into a physical standby database before performing a role transition to it.
7. After a switchover or failover between the primary database and one of the physical or logical standby databases in a configuration, the snapshot standby database can receive redo data from the new primary database after the role transition.
8. Snapshot standby database cannot be the only standby database in a Maximum Protection Data Guard configuration.
1) If not already configured , configure flash recovery area as given below
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/u01/ora/flashback'
2) Bring the physical standby database to mount stage.
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
3) Stop managed recovery if it is active.
4) Convert physical standby database to snapshot standby database.
ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
The database is dismounted during conversion and must be restarted.
SQL> STARTUP
Once the database is restarted any transaction can be executed .
SQL> select open_mode,database_role from v$database;
OPEN_MODE DATABASE_ROLE---------- ----------------READ WRITE SNAPSHOT STANDBY
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;SQL>ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> select open_mode,database_role from v$database;
OPEN_MODE DATABASE_ROLE
---------- ----------------
MOUNTED PHYSICAL STANDBY
Start the media recovery process.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
Or for Active dataguard
SQL> alter database recover managed standby database disconnect from session using current logfile;
Open Physical Standby For Read Write Testing and Flashback
Here I am discussing the steps to open the Standby database in read write mode testing purposes and then move it back to standby database using the flashback technology.
In Standby database
A ) Set up a flash recovery area.
B ) Make sure to give enough space for to FRA
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=5G;
B ) Cancel Redo Apply and create a guaranteed restore point.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> CREATE RESTORE POINT restore_point_standby GUARANTEE FLASHBACK DATABASE;
To Confirim the details of restore point, query the below
SQL> select NAME,SCN,TIME from v$restore_point;
In Primary Database
Switch logs so the SCN of the restore point is archived on the physical standby database
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
Defer log archive destinations pointing to the standby
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;
In Standby database
A ) Activate the physical standby database:
SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
SQL> select CONTROLFILE_TYPE from v$database;
CONTROL
-------
CURRENT
B) Then open the database.
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;
SQL> ALTER DATABASE OPEN;
Revert the active standby database back to Physical standby database
Once done with your testing
A1. Mount the database.
A2. Flashback the database to restore point.
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> FLASHBACK DATABASE TO RESTORE POINT restore_point_standby ;
SQL> select controlfile_type from v$database;
CONTROL
--------------
BACKUP
B ) Convert to Standby database
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
SQL> select controlfile_type from v$database;
CONTROL
--------------
STANDBY
A ) Put the standby database in managed recovery mode. Let archive gap resolution fetch all missing archived redo log files and allow Redo Apply to apply the gap
In Primary database
A ) Re-enable archiving for the physical standby database:
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
In Standby database
A ) Open the database in Read only mode
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE OPEN READ ONLY;
B ) Drop the restore point
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
SQL> DROP RESTORE POINT restore_point_standby ;
In Standby database
A ) Set up a flash recovery area.
B ) Make sure to give enough space for to FRA
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=5G;
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/u01/ora/flashback'B ) Cancel Redo Apply and create a guaranteed restore point.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> CREATE RESTORE POINT restore_point_standby GUARANTEE FLASHBACK DATABASE;
To Confirim the details of restore point, query the below
SQL> select NAME,SCN,TIME from v$restore_point;
In Primary Database
Switch logs so the SCN of the restore point is archived on the physical standby database
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
Defer log archive destinations pointing to the standby
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;
In Standby database
A ) Activate the physical standby database:
SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
SQL> select CONTROLFILE_TYPE from v$database;
CONTROL
-------
CURRENT
B) Then open the database.
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;
SQL> ALTER DATABASE OPEN;
Revert the active standby database back to Physical standby database
Once done with your testing
A1. Mount the database.
A2. Flashback the database to restore point.
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> FLASHBACK DATABASE TO RESTORE POINT restore_point_standby ;
SQL> select controlfile_type from v$database;
CONTROL
--------------
BACKUP
B ) Convert to Standby database
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
SQL> select controlfile_type from v$database;
CONTROL
--------------
STANDBY
A ) Put the standby database in managed recovery mode. Let archive gap resolution fetch all missing archived redo log files and allow Redo Apply to apply the gap
In Primary database
A ) Re-enable archiving for the physical standby database:
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
In Standby database
A ) Open the database in Read only mode
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE OPEN READ ONLY;
B ) Drop the restore point
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
SQL> DROP RESTORE POINT restore_point_standby ;