This document describes the steps involved in the process of creating a physical standby database in maximum protection mode.
This mode offers no data loss, but there will be some performance overhead . I don't know whether it will be acceptable for you .Test it please .
The three main steps involved are :
The discussions here assume that you specify initialization parameters in a server parameter file (SPFILE)
instead of in a traditional text initialization parameter file (PFILE).
Before you create a standby database you must first ensure that the primary database is properly configured.
Table 3-1 provides a checklist of the tasks that you perform on the primary database to prepare for physical standby database creation. There is also a reference to the section that describes the task in more detail.
Reference | Task |
---|---|
Section 3.1.1 |
Enable Forced Logging |
Section 3.1.2 |
Enable Archiving and Define a Local Archiving Destination |
Place the primary database in FORCE LOGGING
mode after database creation using the following SQL statement:
SQL> ALTER DATABASE FORCE LOGGING;
This statement may take a considerable amount of time to complete, because it waits for all unlogged direct write I/O operations to finish.
Ensure that the primary database is in ARCHIVELOG mode, that automatic archiving is enabled, and that you have defined a local archiving destination.
Set the local archive destination using the following SQL statement:
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/disk1/oracle/oradata/payroll MANDATORY' SCOPE=BOTH;
This section describes the tasks you perform to create a physical standby database.
Table 3-2 provides a checklist of the tasks that you need to perform .The individual sections describes the task in more detail.
On the primary database, query the V$DATAFILE
view to list the files that will be used to create the physical standby database, as follows:
SQL> SELECT NAME FROM V$DATAFILE; NAME ---------------------------------------------------------------------------- /disk1/oracle/oradata/payroll/system01.dbf /disk1/oracle/oradata/payroll/undotbs01.dbf /disk1/oracle/oradata/payroll/cwmlite01.dbf . . .
On the primary database, perform the following steps to make a closed backup copy of the primary database.
Issue the following SQL*Plus statements to shut down the primary database:
SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP; SQL> SHUTDOWN;
Copy the datafiles that you identified in Section 3.2.1 to a temporary location using an operating system utility copy command. The following example uses the UNIX cp
command:
cp /disk1/oracle/oradata/payroll/system01.dbf /disk1/oracle/oradata/payroll/standby/system01.dbf
Copying the datafiles to a temporary location will reduce the amount of time that the primary database must remain shut down.
Issue the following SQL*Plus statement to restart the primary database:
SQL> STARTUP;
On the primary database, create the control file for the standby database, as shown in the following example:
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/disk1/oracle/oradata/payroll/standby/payroll2.ctl';
The filename for the newly created standby control file must be different from the filename of the current control file of the primary database. The control file must also be created after the last time stamp for the backup datafiles.
Create a traditional text initialization parameter file from the server parameter file used by the primary database; a traditional text initialization parameter file can be copied to the standby location and modified. For example:
SQL> CREATE PFILE='/disk1/oracle/dbs/initpayroll2.ora' FROM SPFILE;
Later, in Section 3.2.11, you will convert this file back to a server parameter file after it is modified to contain the parameter values appropriate for use with the physical standby database.
On the primary system, use an operating system copy utility to copy the following binary files from the primary system to the standby system:
DB_NAME=PAYROLL COMPATIBLE=9.2.0.1.0 CONTROL_FILES='/DISK1/ORACLE/ORADATA/PAYROLL/STANDBY/PAYROLL2.CTL' LOG_ARCHIVE_START=TRUE DB_FILE_NAME_CONVERT=('/DISK1/ORACLE/ORADATA/PAYROLL/','/DISK1/ORACLE/ORADATA/PAYROLL/STANDBY/') LOG_FILE_NAME_CONVERT=('/DISK1/ORACLE/ORADATA/PAYROLL/','/DISK1/ORACLE/ORADATA/PAYROLL/STANDBY/') LOG_ARCHIVE_FORMAT=%d_%T_%S.ARC LOG_ARCHIVE_DEST_1='LOCATION=/DISK1/ORACLE/ORADATA/PAYROLL/STANDBY/' STANDBY_ARCHIVE_DEST='/DISK1/ORACLE/ORADATA/PAYROLL/STANDBY' STANDBY_FILE_MANAGEMENT=AUTO INSTANCE_NAME=PAYROLL2 LOCK_NAME_SPACE=PAYROLL2 FAL_SERVER=PROD DG_BROKER_START=TRUE
The following list provides a brief explanation about the parameter settings shown in Example 3-1:
DB_NAME
- The same as that of primary database.COMPATIBLE
- The same as that of primary database.CONTROL_FILES
- Specifies the path name and filename for the standby control file.LOG_ARCHIVE_START
- The same as that of primary database.DB_FILE_NAME_CONVERT
- Specifies the location of datafiles on standby database.The two arguments that this parameter needs are: location of datafiles on primary database , location of datafiles on standby database. This parameter will convert the filename of the primary database datafiles to the filename of the standby datafile filenames. If the standby database is on the same system as the primary database or if the directory structure where the datafiles are located on the standby site is different from the primary site then this parameter is required. See Section 3.2.1 for the location of the datafiles on the primary database.LOG_FILE_NAME_CONVERT
- Specifies the location of redo logfiles on standby database.The two arguments that this parameter needs are: location of redo logfiles on primary database , location of redo logfiles on standby database. This parameter will convert the filename of the primary database log to the filenames of the standby log. If the standby database is on the same system as the primary database or if the directory structure where the logs are located on the standby site is different from the primary site then this parameter is required. See Section 3.2.1 for the location of the logs on the primary database.LOG_ARCHIVE_FORMAT
- Specifies the format for the archived redo logs using a DBID
(%d), thread (%T), and sequence number (%S).LOG_ARCHIVE_DEST_1
- Specifies the location where the redo logs are to be archived on the standby database. (If a switchover occurs and this instance becomes the primary database, then this parameter will specify the location where the online redo logs will be archived.)STANDBY_ARCHIVE_DEST
- Specifies the location of the archived redo logs that will be received from the primary database.INSTANCE_NAME
- specify a different value for the standby database than the primary database.(This parameter is required ONLY when the primary and standby databases reside on the same host)LOCK_NAME_SPACE
- Change the INSTANCE_NAME
parameter to a value other than DB_NAME
value.Then set this LOCK_NAME_SPACE
parameter to the same value that you specified forINSTANCE_NAME
parameter.(This parameter is required ONLY when the primary and standby databases reside on the same host)
FAL_SERVER
- Specifies the net service name of the primary database (check tnsnames.ora on standby database).Let us assume PROD is the net service name of primary database.DG_BROKER_START
- Enables Oracle to determine whether or not the DMON (Data Guard broker) process should be started. ( DRS_START has the same functionality as well and is retained for backward compatibility only ).Oracle recommends that you use DG_BROKER_START.
COMPATIBLE=9.2.0.1.0 LOG_ARCHIVE_START=TRUE LOG_ARCHIVE_FORMAT=%d_%T_%S.ARC LOG_ARCHIVE_DEST_1='LOCATION=/DISK1/ORACLE/ORADATA/PAYROLL/PROD/ MANDATORY' LOG_ARCHIVE_DEST_2='SERVICE=STBY LGWR SYNC AFFIRM MANDATORY REOPEN=180' LOG_ARCHIVE_DEST_STATE_2=ENABLE REMOTE_ARCHIVE_ENABLE=TRUE FAL_CLIENT=STBY DG_BROKER_START=TRUE
The following list provides a brief explanation about the parameter settings shown in Example 3-2:
LOG_ARCHIVE_FORMAT
- Specifies the format for the archived redo logs using a DBID
(%d), thread (%T), and sequence number (%S).LOG_ARCHIVE_DEST_1
- Specifies the location where the redo logs are to be archived on the primary database.LOG_ARCHIVE_DEST_2
- Specifies the net service name of the standby
database (check tnsnames.ora on primary database).You can either per destination use LGWR or ARCH, due to network traffic it
is advised to use LGWR for at most one remote destination.Also the network transmission mode (sync or async) has to be specified
in case primary database modifications are propagated by the LGWR. The NO DATA LOSS situation demands the SYNC mode, control
is not returned to the executing application or user until the redo information is received by the standby site (this can have
impact on the performance as mentioned). FAL_CLIENT
- Specifies the net service name of the standby database (check tnsnames.ora on primary database).Let us assume STBY is the net service name of standby database.DG_BROKER_START
- Enables Oracle to determine whether or not the DMON (Data Guard broker) process should be started. ( DRS_START has the same functionality as well and is retained for backward compatibility only ).Oracle recommends that you use DG_BROKER_START.If the standby system is running on a Windows system, use the ORADIM
utility to create a Windows Service. For example:
WINNT> oradim -NEW -SID payroll2 -STARTMODE manual
On both the primary and standby sites, use Oracle Net Manager to configure a listener for the respective databases. If you plan to manage the configuration using the Data Guard broker, you must configure the listener to use the TCP/IP protocol and statically register service information for each database using the SID for the database instance.
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
Enable dead connection detection by setting the SQLNET.EXPIRE_TIME
parameter to 2 in the SQLNET.ORA
parameter file on the standby system. For example:
SQLNET.EXPIRE_TIME=2
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 log transport services.
The Oracle Net service name must resolve to a connect descriptor that uses the same protocol, host address, port, and SID 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.
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 section 3.2.6.1. For example:
$ SQLPLUS /NOLOG SQL> CONNECT / AS SYSDBA SQL> CREATE SPFILE FROM PFILE='initpayroll2.ora';
On the standby database, issue the following SQL statements to start and mount the database in standby mode:
SQL> STARTUP NOMOUNT; SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
On the standby database, start log apply services as shown in the following example:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
The example includes the DISCONNECT FROM SESSION
option so that log apply services run in a background session.
This section describes the minimum amount of work you must do on the primary database to set up and enable archiving to the physical standby database.
To configure archive logging from the primary database to the standby site the LOG_ARCHIVE_DEST_
n
and LOG_ARCHIVE_DEST_STATE_
n
parameters must be defined.
The following example sets the initialization parameters needed to enable archive logging to the standby site:
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=STBY LGWR SYNC AFFIRM MANDATORY REOPEN=180' SCOPE=BOTH; SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH;
Archiving of redo logs to the remote standby location does not occur until after a log switch. A log switch occurs, by default, when an online redo log becomes full. To force the current redo logs to be archived immediately, use the SQL ALTER SYSTEM
statement on the primary database. For example:
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
LOG_ARCHIVE_DEST_2='SERVICE=STBY LGWR SYNC AFFIRM MANDATORY REOPEN=180'
in section 3.2.6.2 confirms that.CREATE CONTROLFILE
script. To get such a script, run the following command at the standby database :
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
MAXLOGFILES
and MAXLOGMEMBERS
.
The number of existing log file groups should be less than MAXLOGFILES
, else you cannot add a new log group.
Now we can add standby redo log files to the standby database (of course, the standby database must be in the mount state):SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/DISK1/ORACLE/ORADATA/PAYROLL/STANDBY/STBY4A.LOG',
'/DISK1/ORACLE/ORADATA/PAYROLL/STANDBY/STBY4B.LOG') SIZE 5M;
SQL > ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;
Once you create the physical standby database and set up log transport services, you may want verify that database modifications are being successfully shipped from the primary database to the standby database.
V$MANAGED_STANDBY
on physical standby database as below.Repeatedly executing this query shows that BLOCK#
keeps increasing.SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
RFS: Successfully opened standby logfile 4: '/DISK1/ORACLE/ORADATA/PAYROLL/STANDBY/STBY4A.LOG'
To see the new archived redo logs that were received on the standby database, you should first identify the existing archived redo logs on the standby database, archive a few logs on the primary database, and then check the standby database again. The following steps show how to perform these tasks.
On the standby database, query the V$ARCHIVED_LOG
view to identify existing archived redo logs. For example:
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME 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.
On the primary database, archive the current log using the following SQL statement:
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
On the standby database, query the V$ARCHIVED_LOG
view to verify the redo log was received:
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME 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 logs are now available for log apply services to apply redo data to the standby database.
On the standby database, query the V$ARCHIVED_LOG
view to verify the archived redo log was applied.
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#; SEQUENCE# APP --------- --- 8 YES 9 YES 10 YES 11 YES
4 rows selected.