Step-by-Step Instructions for Creating a Physical Standby Database

Step-by-Step Instructions for Creating a Physical Standby Database

This section describes the tasks you perform to create a physical standby database.Steps for Creating a Physical database standby database : Create a Backup Copy of the Primary Database Data Files Create a Control File for the Standby Database Prepare an Initialization Parameter File for the Standby Database Copy Files from the Primary System to the Standby System Set Up the Environment to Support the Standby Database Start the Physical Standby Database Verify the Physical Standby Database Is Performing Properly Oracle Data Guard - Create a Backup Copy of the Primary Database Data Files You can use any backup copy of the primary database to create the physical standby database, as long as you have the necessary archived redo log files to completely recover the database. Oracle recommends that you use the Recovery Manager utility (RMAN). Oracle Data Guard - Create a Control File for the Standby Database If the backup procedure required you to shut down the primary database, issue the following SQL*Plus statement to start the primary database:SQL> STARTUP MOUNT; Then, create the control file for the standby database, and open the primary database to user access, as shown in the following example: SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/boston.ctl'; SQL> ALTER DATABASE OPEN; Oracle Data Guard - Prepare an Initialization Parameter File for the Standby Database Step 1 Copy the primary database parameter file to the standby database.Create a text initialization parameter file (PFILE) from the server parameter file (SPFILE) used by the primary database; a text initialization parameter file can be copied to the standby location and modified. For example: SQL> CREATE PFILE='/tmp/initboston.ora' FROM SPFILE; Step 2 Set initialization parameters on the physical standby database. Although most of the initialization parameter settings in the text initialization parameter file that you copied from the primary system are also appropriate for the physical standby database, some modifications need to be made. In addition, ensure the COMPATIBLE initialization parameter is set to the same value on both the primary and standby databases. If the values differ, redo transport services may be unable to transmit redo data from the primary database to the standby databases. In a Data Guard configuration, COMPATIBLE must be set to a minimum of 9.2.0.1.0. However, if you want to take advantage of new Oracle Database 10g features, set the COMPATIBLE parameter to 10.2.0.0 or higher. It is always a good practice to use the SHOW PARAMETERS command to verify no other parameters need to be changed. Oracle Data Guard - Copy Files from the Primary System to the Standby System Use an operating system copy utility to copy the following binary files from the primary system to the standby system:Backup data files Standby control file Initialization parameter file Oracle Data Guard - Set Up the Environment to Support the Standby Database Perform the following steps to create a Windows-based service, create a password file, set up the Oracle Net environment, and create a SPFILE.Step 1 Create a Windows-based service. If the standby system is running on a Windows-based system, use the ORADIM utility to create a Windows Service and password file. For example: WINNT> oradim -NEW -SID boston -INTPWD password -STARTMODE manual Step 2 Create a password file. On platforms other than Windows, create a password file, and set the password for the SYS user to the same password used by the SYS user on the primary database. The password for the SYS user on every database in a Data Guard configuration must be identical for redo transmission to succeed. See Oracle Database Administrator's Guide. Step 3 Configure listeners for the primary and standby databases. On both the primary and standby sites, use Oracle Net Manager to configure a listener for the respective databases. To restart the listeners (to pick up the new definitions), enter the following LSNRCTL utility commands on both the primary and standby systems: % lsnrctl stop % lsnrctl start Step 4 Create Oracle Net service names. On both the primary and standby systems, use Oracle Net Manager to create a network service name for the primary and standby databases that will be used by redo transport services. The Oracle Net service name must resolve to a connect descriptor that uses the same protocol, host address, port, and service that you specified when you configured the listeners for the primary and standby databases. The connect descriptor must also specify that a dedicated server be used. Step 5 Create a server parameter file for the standby database. On an idle standby database, use the SQL CREATE statement to create a server parameter file for the standby database from the text initialization parameter file that was edited in Step 2. For example: SQL> CREATE SPFILE FROM PFILE='initboston.ora'; Oracle Data Guard - start the Physical Standby Database Perform the following steps to start the physical standby database and Redo Apply.Step 1 Start the physical standby database. On the standby database, issue the following SQL statement to start and mount the database: SQL> STARTUP MOUNT; Step 2 Start Redo Apply. On the standby database, issue the following command to start Redo Apply: SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; The statement includes the DISCONNECT FROM SESSION option so that Redo Apply runs in a background session. Step 3 Test archival operations to the physical standby database. In this example, the transmission of redo data to the remote standby location does not occur until after a log switch. A log switch occurs, by default, when an online redo log file becomes full. To force a log switch so that redo data is transmitted immediately, use the following ALTER SYSTEM statement on the primary database. For example: SQL> ALTER SYSTEM SWITCH LOGFILE; Oracle Data Guard - Verify the Physical Standby Database Is Performing Properly Once you create the physical standby database and set up redo transport services, you may want to verify database modifications are being successfully transmitted from the primary database to the standby database..To see that redo data is being received on the standby database, you should first identify the existing archived redo log files on the standby database, force a log switch and archive a few online redo log files on the primary database, and then check the standby database again. The following steps show how to perform these tasks.. Step 1 Identify the existing archived redo log files.. On the standby database, query the V$ARCHIVED_LOG view to identify existing files in the archived redo log. For example:. SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME 2 FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;. SEQUENCE# FIRST_TIME NEXT_TIME ---------- ------------------ ------------------ 8 11-JUL-02 17:50:45 11-JUL-02 17:50:53 9 11-JUL-02 17:50:53 11-JUL-02 17:50:58 10 11-JUL-02 17:50:58 11-JUL-02 17:51:03 3 rows selected. Step 2 Force a log switch to archive the current online redo log file..On the primary database, issue the ALTER SYSTEM SWITCH LOGFILE statement to force a log switch and archive the current online redo log file group:. SQL> ALTER SYSTEM SWITCH LOGFILE;. Step 3 Verify the new redo data was archived on the standby database.. On the standby database, query the V$ARCHIVED_LOG view to verify the redo data was received and archived on the standby database:. SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME 2> FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;. SEQUENCE# FIRST_TIME NEXT_TIME ---------- ------------------ ------------------ 8 11-JUL-02 17:50:45 11-JUL-02 17:50:53 9 11-JUL-02 17:50:53 11-JUL-02 17:50:58 10 11-JUL-02 17:50:58 11-JUL-02 17:51:03 11 11-JUL-02 17:51:03 11-JUL-02 18:34:11 4 rows selected. The archived redo log files are now available to be applied to the physical standby database..Step 4 Verify new archived redo log files were applied. On the standby database, query the V$ARCHIVED_LOG view to verify the archived redo log files were applied. SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG 2 ORDER BY SEQUENCE#; SEQUENCE# APP --------- --- 8 YES 9 YES 10 YES 11 YES 4 rows selected. Oracle Data Guard - Post-Creation Steps At this point, the physical standby database is running and can provide the maximum performance level of data protection. The following list describes additional preparations you can take on the physical standby database: Upgrade the data protection mode The Data Guard configuration is initially set up in the maximum performance mode (the default). Enable Flashback Database Flashback Database removes the need to re-create the primary database after a failover. Flashback Database is similar to conventional point-in-time recovery in its effects, enabling you to return a database to its state at a time in the recent past.