Scott Jesse has worked for Oracle since 1996 and is currently Customer Support Senior Manager for the RAC Assurance Team. He has served as the Global Team Lead and as a member of the Advanced Resolution and Escalations team within Oracle Support. Scott is the coauthor of Oracle Database 10g High Availability with RAC, Flashback, and Data Guard and Oracle9i for Windows 2000 Tips & Techniques, both from Oracle Press.
Bill Burton joined Oracle in 1998 and is currently a member of the Development RAC Assurance Team. He co-wrote and presented the “Upgrading to 11gR2” session at Oracle OpenWorld 2010.
Bryan Vongray is a member of the RAC Assurance Team within Oracle Global Customer Support. He specialized in Oracle High Availability with specific focus on Oracle Real Application Clusters [RAC], Oracle Data Guard, Oracle Streams, backup and recovery, and Oracle Grid Control. Bryan has planned and implemented Oracle HA Solution for several high profile Oracle customers.
Data Guard is the name for Oracle's standby database solution, used for disaster recovery and high availability. This article contains an updated version of the 9i physical standby setup method posted here.
You should probably be using the Data Guard Broker to configure and manage your standby database, as described here.
Related articles.
TL;DR
If you already know about Data Guard and want to quickly set up a demo environment using VirtualBox and Vagrant you can follow the instructions in my GitHub repository here.
Assumptions
- You have two servers [physical or VMs] with an operating system and Oracle installed on them. In this case I've used Oracle Linux 5.6 and Oracle Database 11.2.0.2.
- The primary server has a running instance.
- The standby server has a software only installation.
Primary Server Setup
Logging
Check that the primary database is in archivelog mode.
SELECT log_mode FROM v$database; LOG_MODE ------------ NOARCHIVELOG SQL>
If it is noarchivelog mode, switch is to archivelog mode.
SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE ARCHIVELOG; ALTER DATABASE OPEN;
Enabled forced logging by issuing the following command.
ALTER DATABASE FORCE LOGGING;
Initialization Parameters
Check the setting for the
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=[DB11G,DB11G_STBY]';5 and
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=[DB11G,DB11G_STBY]';6 parameters. In this case they are both set to "DB11G" on the primary database.
SQL> show parameter db_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string DB11G SQL> show parameter db_unique_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_unique_name string DB11G SQL>
The
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=[DB11G,DB11G_STBY]';5 of the standby database will be the same as that of the primary, but it must have a different
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=[DB11G,DB11G_STBY]';6 value. The
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=[DB11G,DB11G_STBY]';6 values of the primary and standby database should be used in the
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby NOAFFIRM ASYNC VALID_FOR=[ONLINE_LOGFILES,PRIMARY_ROLE] DB_UNIQUE_NAME=DB11G_STBY'; ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;0 setting of the
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby NOAFFIRM ASYNC VALID_FOR=[ONLINE_LOGFILES,PRIMARY_ROLE] DB_UNIQUE_NAME=DB11G_STBY'; ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;1 parameter. For this example, the standby database will have the value "DB11G_STBY".
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=[DB11G,DB11G_STBY]';
Set suitable remote archive log destinations. In this case I'm using the fast recovery area for the local location, but you could specify an location explicitly if you prefer. Notice the
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby NOAFFIRM ASYNC VALID_FOR=[ONLINE_LOGFILES,PRIMARY_ROLE] DB_UNIQUE_NAME=DB11G_STBY'; ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;2 and the
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=[DB11G,DB11G_STBY]';6 for the remote location reference the standby location.
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby NOAFFIRM ASYNC VALID_FOR=[ONLINE_LOGFILES,PRIMARY_ROLE] DB_UNIQUE_NAME=DB11G_STBY'; ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
The
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby NOAFFIRM ASYNC VALID_FOR=[ONLINE_LOGFILES,PRIMARY_ROLE] DB_UNIQUE_NAME=DB11G_STBY'; ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;4 and
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby NOAFFIRM ASYNC VALID_FOR=[ONLINE_LOGFILES,PRIMARY_ROLE] DB_UNIQUE_NAME=DB11G_STBY'; ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;5 parameters must be set to appropriate values and the
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby NOAFFIRM ASYNC VALID_FOR=[ONLINE_LOGFILES,PRIMARY_ROLE] DB_UNIQUE_NAME=DB11G_STBY'; ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;6 must be set to exclusive.
ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE; ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30; ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;
In addition to the previous setting, it is recommended to make sure the primary is ready to switch roles to become a standby. For that to work properly we need to set the following parameters. Adjust the
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby NOAFFIRM ASYNC VALID_FOR=[ONLINE_LOGFILES,PRIMARY_ROLE] DB_UNIQUE_NAME=DB11G_STBY'; ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;7 parameters to account for your filename and path differences between the servers.
ALTER SYSTEM SET FAL_SERVER=DB11G_STBY; --ALTER SYSTEM SET DB_FILE_NAME_CONVERT='DB11G_STBY','DB11G' SCOPE=SPFILE; --ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='DB11G_STBY','DB11G' SCOPE=SPFILE; ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
Remember, some of the parameters are not modifiable, so the database will need to be restarted before they take effect.
Service Setup
Entries for the primary and standby databases are needed in the "$ORACLE_HOME/network/admin/tnsnames.ora" files on both servers. You can create these using the Network Configuration Utility [netca] or manually. The following entries were used during this setup.
DB11G =
[DESCRIPTION =
[ADDRESS_LIST =
[ADDRESS = [PROTOCOL = TCP][HOST = ol5-112-dga1][PORT = 1521]]
]
[CONNECT_DATA =
[SERVICE_NAME = DB11G.WORLD]
]
]
DB11G_STBY =
[DESCRIPTION =
[ADDRESS_LIST =
[ADDRESS = [PROTOCOL = TCP][HOST = ol5-112-dga2][PORT = 1521]]
]
[CONNECT_DATA =
[SERVICE_NAME = DB11G.WORLD]
]
]Backup Primary Database
If you are planning to use an active duplicate to create the standby database, then this step is unnecessary. For a backup-based duplicate, or a manual restore, take a backup of the primary database.
$ rman target=/ RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
Create Standby Controlfile and PFILE
Create a controlfile for the standby database by issuing the following command on the primary database.
SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE ARCHIVELOG; ALTER DATABASE OPEN;0
Create a parameter file for the standby database.
SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE ARCHIVELOG; ALTER DATABASE OPEN;1
Amend the PFILE making the entries relevant for the standby database. I'm making a replica of the original server, so in my case I only had to amend the following parameters.
SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE ARCHIVELOG; ALTER DATABASE OPEN;2
Standby Server Setup [Manual]
Copy Files
Create the necessary directories on the standby server.
SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE ARCHIVELOG; ALTER DATABASE OPEN;3
Copy the files from the primary to the standby server.
SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE ARCHIVELOG; ALTER DATABASE OPEN;4
Notice, the backups were copied across to the standby server as part of the FRA copy. If your backups are not held within the FRA, you must make sure you copy them to the standby server and make them available from the same path as used on the primary server.
Start Listener
Make sure the listener is started on the standby server.
SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE ARCHIVELOG; ALTER DATABASE OPEN;5
Restore Backup
Create the SPFILE form the amended PFILE.
SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE ARCHIVELOG; ALTER DATABASE OPEN;6
Restore the backup files.
SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE ARCHIVELOG; ALTER DATABASE OPEN;7
Create Redo Logs
Create online redo logs for the standby. It's a good idea to match the configuration of the primary server.
SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE ARCHIVELOG; ALTER DATABASE OPEN;8
In addition to the online redo logs, you should create standby redo logs on both the standby and the primary database [in case of switchovers]. The standby redo logs should be at least as big as the largest online redo log and there should be one extra group per thread compared the online redo logs. In my case, the following standby redo logs must be created on both servers.
SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE ARCHIVELOG; ALTER DATABASE OPEN;9
Once this is complete, we can start the apply process.
Standby Server Setup [DUPLICATE]
Copy Files
Create the necessary directories on the standby server.
SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE ARCHIVELOG; ALTER DATABASE OPEN;3
Copy the files from the primary to the standby server.
ALTER DATABASE FORCE LOGGING;1
Start Listener
When using active duplicate, the standby server requires static listener configuration in a "listener.ora" file. In this case I used the following configuration.
ALTER DATABASE FORCE LOGGING;2
Make sure the listener is started on the standby server.
SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE ARCHIVELOG; ALTER DATABASE OPEN;5
Create Standby Redo Logs on Primary Server
The
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby NOAFFIRM ASYNC VALID_FOR=[ONLINE_LOGFILES,PRIMARY_ROLE] DB_UNIQUE_NAME=DB11G_STBY'; ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;8 command automatically creates the standby redo logs on the standby. To make sure the primary database is configured for switchover, we must create the standby redo logs on the primary server.
ALTER DATABASE FORCE LOGGING;4
Create Standby Using DUPLICATE
Start the auxillary instance on the standby server by starting it using the temporary "init.ora" file.
ALTER DATABASE FORCE LOGGING;5
Connect to RMAN, specifying a full connect string for both the TARGET and AUXILLARY instances. DO not attempt to use OS authentication.
ALTER DATABASE FORCE LOGGING;6
Now issue the following DUPLICATE command.
ALTER DATABASE FORCE LOGGING;7A brief explanation of the individual clauses is shown below.
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby NOAFFIRM ASYNC VALID_FOR=[ONLINE_LOGFILES,PRIMARY_ROLE] DB_UNIQUE_NAME=DB11G_STBY'; ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
9: This tells theALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby NOAFFIRM ASYNC VALID_FOR=[ONLINE_LOGFILES,PRIMARY_ROLE] DB_UNIQUE_NAME=DB11G_STBY'; ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
8 command is to be used for a standby, so it will not force a DBID change.ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE; ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30; ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;
1: TheALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby NOAFFIRM ASYNC VALID_FOR=[ONLINE_LOGFILES,PRIMARY_ROLE] DB_UNIQUE_NAME=DB11G_STBY'; ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
8 will be created directly from the source datafile, without an additional backup step.ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE; ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30; ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;
3: TheALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby NOAFFIRM ASYNC VALID_FOR=[ONLINE_LOGFILES,PRIMARY_ROLE] DB_UNIQUE_NAME=DB11G_STBY'; ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
8 will include the recovery step, bringing the standby up to the current point in time.ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE; ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30; ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;
5: Allows us to reset values in the spfile when it is copied from the source server.ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE; ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30; ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;
6: Destination file locations are not checked.
Once the command is complete, we can start the apply process.
Start Apply Process
Start the apply process on standby server.
ALTER DATABASE FORCE LOGGING;8
If you need to cancel the apply process, issue the following command.
ALTER DATABASE FORCE LOGGING;9
If you prefer, you can set a delay between the arrival of the archived redo log and it being applied on the standby server using the following commands.
SQL> show parameter db_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string DB11G SQL> show parameter db_unique_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_unique_name string DB11G SQL>0
Provided you have configured standby redo logs, you can start real-time apply using the following command.
ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE; ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30; ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;7
Test Log Transport
On the primary server, check the latest archived redo log and force a log switch.
SQL> show parameter db_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string DB11G SQL> show parameter db_unique_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_unique_name string DB11G SQL>1
Check the new archived redo log has arrived at the standby server and been applied.
SQL> show parameter db_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string DB11G SQL> show parameter db_unique_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_unique_name string DB11G SQL>2
Protection Mode
There are three protection modes for the primary database:
- Maximum Availability: Transactions on the primary do not commit until redo information has been written to the online redo log and the standby redo logs of at least one standby location. If no standby location is available, it acts in the same manner as maximum performance mode until a standby becomes available again.
- Maximum Performance: Transactions on the primary commit as soon as redo information has been written to the online redo log. Transfer of redo information to the standby server is asynchronous, so it does not impact on performance of the primary.
- Maximum Protection: Transactions on the primary do not commit until redo information has been written to the online redo log and the standby redo logs of at least one standby location. If not suitable standby location is available, the primary database shuts down.
By default, for a newly created standby database, the primary database is in maximum performance mode.
SQL> show parameter db_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string DB11G SQL> show parameter db_unique_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_unique_name string DB11G SQL>3
The mode can be switched using the following commands. Note the alterations in the redo transport attributes.
SQL> show parameter db_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string DB11G SQL> show parameter db_unique_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_unique_name string DB11G SQL>4
Database Switchover
A database can be in one of two mutually exclusive modes [primary or standby]. These roles can be altered at runtime without loss of data or resetting of redo logs. This process is known as a Switchover and can be performed using the following statements.
SQL> show parameter db_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string DB11G SQL> show parameter db_unique_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_unique_name string DB11G SQL>5
On the original standby database issue the following commands.
SQL> show parameter db_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string DB11G SQL> show parameter db_unique_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_unique_name string DB11G SQL>6
Once this is complete, test the log transport as before. If everything is working fine, switch the primary database back to the original server by doing another switchover. This is known as a switchback.
Failover
If the primary database is not available the standby database can be activated as a primary database using the following statements.
SQL> show parameter db_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string DB11G SQL> show parameter db_unique_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_unique_name string DB11G SQL>7
Since the standby database is now the primary database it should be backed up immediately.
The original primary database can now be configured as a standby. If Flashback Database was enabled on the primary database, then this can be done relatively easily []. If not, the whole setup process must be followed, but this time using the original primary server as the standby.
Flashback Database
It was already mentioned in the previous section, but it is worth drawing your attention to once more. Although a switchover/switchback is safe for both the primary and standby database, a failover renders the original primary database useless for converting to a standby database. If flashback database is not enabled, the original primary must be scrapped and recreated as a standby database.
An alternative is to enable flashback database on the primary [and the standby if desired] so in the event of a failover, the primary can be flashed back to the time before the failover and quickly converted to a standby database. That process is .
Read-Only Standby and Active Data Guard
Once a standby database is configured, it can be opened in read-only mode to allow query access. This is often used to offload reporting to the standby server, thereby freeing up resources on the primary server. When open in read-only mode, archive log shipping continues, but managed recovery is stopped, so the standby database becomes increasingly out of date until managed recovery is resumed.
To switch the standby database into read-only mode, do the following.
SQL> show parameter db_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string DB11G SQL> show parameter db_unique_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_unique_name string DB11G SQL>8
To resume managed recovery, do the following.
SQL> show parameter db_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string DB11G SQL> show parameter db_unique_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_unique_name string DB11G SQL>9
In 11g, Oracle introduced the Active Data Guard feature. This allows the standby database to be open in read-only mode, but still apply redo information. This means a standby can be available for querying, yet still be up to date. There are licensing implications for this feature, but the following commands show how active data guard can be enabled.
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=[DB11G,DB11G_STBY]';0
Since managed recovery continues with active data guard, there is no need to switch back to managed recovery from read-only mode in this case.
Snapshot Standby
Introduced in 11g, snapshot standby allows the standby database to be opened in read-write mode. When switched back into standby mode, all changes made whilst in read-write mode are lost. This is achieved using flashback database, but the standby database does not need to have flashback database explicitly enabled to take advantage of this feature, thought it works just the same if it is.
If you are using RAC, turn off all but one of the RAC instances. Make sure the instance is in MOUNT mode.
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=[DB11G,DB11G_STBY]';1
Make sure managed recovery is disabled.
ALTER DATABASE FORCE LOGGING;9
Convert the standby to a snapshot standby. The following example queries the
ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE; ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30; ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;8 view to show that flashback database is not enabled prior to the conversion operation.
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=[DB11G,DB11G_STBY]';3
You can now do treat the standby like any read-write database.
To convert it back to the physical standby, losing all the changes made since the conversion to snapshot standby, issue the following commands.
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=[DB11G,DB11G_STBY]';4
The standby is once again in managed recovery and archivelog shipping is resumed. Notice that flashback database is still not enabled.