Standby Database Information
These sections describe procedures to manage physical standby databases. These notes were originally created to document and help support Oracle standby databases and Data Guard environments in Oracle 9i. They have been updated to incorporate changes through 11.2, but they are still a work in progress. Currently only physical standby databases and Data Guard topics are covered.
In Oracle terminology a physical standby database uses redo apply to stay in sync with the primary, and a logical database uses SQL apply.
There were several notable changes to Data Guard in release 11.1:
- Real time apply - applies the redo data immediately rather than waiting for current standby redo log file to be archived. Requires the "USING CURRENT LOGFILE" clause in the managed recovery command. (11.1)
- Active Data Guard - Allows the database to be opened in read-only mode while redo apply is active. This is also known as real-time query. (11.1)
- Snapshot standby - Allows the standby to be opened for read-write operations and then flashed back and reset to a physical standby database. This process can be repeated indefinitely.
- RMAN active duplication - Allows the creation of a standby database from the current primary database. No backup is needed for instantiation.
Most of the guidelines and notes describe the setup of symmetrical standby database environments for high availability and disaster recovery, but creating a standby database can be an excellent way to migrate to a new server with a minimum of down time. Details for RAC environments is also covered.
There are four types of physical standby configurations covered:
- single instance -> single instance
- single instance -> RAC
- RAC -> RAC
- RAC -> single instance
To create the standby database the following basic steps must be performed:
- Configure Oracle server software
- Create standby database environment
- Configure network files
- Configure the primary database init.ora parameters
- Configure the standby database init.ora parameters
- Start the standby instance
- Create the standby database with RMAN
- Configure the standby database for operation
- Place the standby database in recovery mode
- Configure DataGuard
Events That Affect the Standby Database
The following events can cause standby database issues:
- Add datafile of create tablespace
- Drop or delete a tablespace or datafile
- Rename a datafile
- Alter the primary database control file (using the SQL ALTER DATABASE CREATE CONTROLFILE statement)
- Perform a DML or DDL operation using the NOLOGGING or UNRECOVERABLE clause
- Change initialization parameter
See the Oracle Data Guard Concepts and Administration manual for more details.
Enable Forced Logging
Place the primary database in FORCE LOGGING mode after database creation using the following SQL statement:
SQL> ALTER DATABASE FORCE LOGGING;
This prevents the use of the "nologging" feature, which would not be replicated in the redo logs, and therefore applied on the standby database.
Create A Password File
This is required to enable RMAN to create the standby database. After creating the password file with the orapwd utility, the following init.ora parameter must be set:
Standby Redo Log Files
Standby redo log files are required for higher protection levels where LGWR is used to write redo records. There is no downside to creating them for both the primary and standby and this allows a switchover back to the primary. If created on the primary before the standby is instantiated then they will already exist and will not have to be added to the standby. They must be created as the same size and number of members on both the primary and the standby, but there should be one more standby log group than archive log file groups.
Create A Standby Controlfile (Optional)
If not using RMAN active duplication then a backup controlfile from the primary database is required when performing a standby duplication using RMAN:
SQL> alter database create standby controlfile as '<file>';
This step can be included as part of an RMAN backup.
RMAN Backup With Standby Controlfile
The following option was added to the RMAN "backup database" command to insure that a controlfile was available in standby format for RMAN to create the standby database. This is important to also allow for future re-instantiation of the standby database through RMAN.
Appended to the backup command:
include current controlfile for standby
11.2 Parameter Changes
The FAL_CLIENT parameter is no longer required in 11.2.
Create An SPFILE For OEM Support
Oracle's Enterprise Manager requires SPFILE support for remote administration of DataGuard installations. This can be created with the following command:
SQL> create spfile from pfile;
This will create an SPFILE in the default $ORACLE_HOME/dbs directory and will take affect on the next database startup.
For ASM the following can be used which will create an OMF SPFILE in a directory of the format: <disk group>/<db unique name>/PARAMETERFILE/spfile.xxx.yyyyyyyyy
SQL> create spfile='+DATA' from pfile;
Flashback Database Options – 10g/11g
If intending to use the “fast-start failover” feature then the “Flashback Database” feature must be enabled.
The password file needs to be synced between all of the primary database instances and then copied to each of the standby database instances. There is no automatic sync which is why they can diverge on the primary instances. It appears that the password encryption is different if you recreate the password file for the standby, even if the passwords are the same, and this is why a consistent password file must be copied from a primary instance.
The spfile must be on shared storage, this can be on NFS, OCFS, shared raw device or ASM.
Network File Configuration
Oracle RAC on 11.2 uses SCAN listeners so the network configuration changes. The initialization parameters LOCAL_LISTENER and REMOTE_LISTENERS need to be set and corresponding tnsnames.ora definitions added. The full connect strings can be added to the initialization parameters but this is not recommended because of the syntax complexity and to insure consistent definitions across all nodes.
On the primary database explicit entries should be made for the listener and the primary database. The following examples show the entries that should be added to the listener.ora file:
(ADDRESS = (PROTOCOL = TCP)(HOST = oradb1.appsdba.com)(PORT = 1521))
(GLOBAL_DBNAME = oradb_DGMGRL.appsdba.com)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(SID_NAME = oradb_dg1)
Note the use of the GLOBAL_DBNAME parameter. This specifies a static database service and is used for RAC standby duplication and Data Guard brokers.
When connecting to the auxiliary database it's possible to see the following message:
RMAN-04006: error from auxiliary database: ORA-12528: TNS:listener: all appropriate instances are blocking new connections
This can be fixed in one of two ways. Either the duplication can be run from the standby database or the "UR=A" parameter can be added to the standby database entry on the primary database:
(ADDRESS = (PROTOCOL = TCP)(HOST = oradb2.appsdba.com)(PORT = 1521))
(UR = A)
(SERVER = DEDICATED)
(SERVICE_NAME = oradb_dg2)
The standby database is sensitive to the init.ora parameters being copied from the primary database when performing the duplication. These parameters must all translate correctly. The following shows the parameters for an example duplication using active duplication. The highlighted parameters are required but the rest are dependent on the primary database.
duplicate target database
from active database
set db_unique_name = 'ORADB_DG1'
set fal_server = 'ORADB'
set log_archive_dest_2 = 'SERVICE=ORADB ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORADB COMPRESSION=ENABLE'
Standby redo log files are required for higher protection levels where LGWR is used to write redo records. If they were not created in the primary database prior to instantiation of the standby database then they should be created now. They must be created as the same size and number of members as the primary online redo logs and have the same number of groups plus one.
Tempfile Creation (Optional)
Until Oracle 10g Release 2 RMAN will not re-create tempfiles, and any temporary tablespaces defined in the primary database will not have corresponding tempfiles associated with those tablespaces in the standby database. It is possible to “pre-create” the tempfiles since these are needed for sorting when using the “read-only” feature of physical standby databases. The following will detail the procedures to pre-create tempfiles for the standby database. This is then one less thing that has to be done when actually failing over to a standby database.
Enable Redo Log Shipping
Once the standby database has been instantiated then redo log apply can be enabled:
SQL> alter system set LOG_ARCHIVE_DEST_state_2=enable scope=both;
SQL> alter system switch logfile;
Verify the archive destination:
SQL> select * from v$archive_dest where dest_id=2;
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIME NEXT_TIME
---------- --------------- ---------------
1 19-APR-07 20-APR-07
2 20-APR-07 20-APR-07
3 20-APR-07 20-APR-07
4 20-APR-07 20-APR-07
Verifying Apply on the Standby
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIME NEXT_TIME APP
---------- --------------- --------------- ---
3 20-APR-07 11:07 20-APR-07 11:10 YES
4 20-APR-07 11:10 20-APR-07 11:47 YES
5 20-APR-07 11:47 20-APR-07 11:49 YES
6 20-APR-07 11:49 20-APR-07 11:50 YES
Deletion of Applied Archivelogs
If the archivelog files are being managed in the Fast Recovery Area then automatic deletion of backed up and applied archivelog files can be setup by issuing the following RMAN configure command on the primary database:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY BACKED UP <#> TIMES TO DISK;
Snapshot Standby Database
In Release 11g Oracle supports a snapshot standby database. A snapshot standby database can be opened in read/write mode and changed. It can then be closed and resynced with the primary database and then reopened in read/write mode again. Oracle accomplishes this with guaranteed restore points and the flashback database feature.
The following documentation and notes are available:
Oracle® Data Guard Concepts and Administration,
(11.2), (11.1), (10.2)
Oracle® Data Guard Broker,
(11.2), (11.1), (10.2)
Oracle® Database Backup and Recovery User's Guide,
Backup and Recovery Advanced User's Guide (10.2)
Using Recovery Manager with Oracle Data Guard in Oracle Database 10g
MAA / Data Guard 10g Setup Guide – Creating a Single Instance Physical Standby for a RAC Primary
MAA / Data Guard 10g Setup Guide – Creating a RAC Physical Standby for a RAC Primary
Rapid Oracle RAC Standby Deployment: Oracle Database 11g Release 2
Data Guard 11g Installation and Configuration
On Oracle RAC Systems
Step by Step Guide on Creating Physical Standby Using RMAN DUPLICATE...FROM ACTIVE DATABASE [ID