Oracle9i Data Guard Concepts and Administration Release 1 (9.0.1) Part Number A88808-01 |
|
This chapter describes the following standby database scenarios:
This scenario describes the creation of a standby database standby1
on the same site as the primary database primary1
. The site is a UNIX system with three file systems, each mounted on a separate disk configuration on a different controller. By placing the standby database on a different file system from the primary database, you protect the primary database from a hard disk failure. By running the same-site standby database in managed recovery mode, you can keep it continuously up-to-date.
After you set up the standby database on the local site, you plan to create a standby database on a remote site for total disaster protection. In this way, even if all disks of the primary database fail or are destroyed in a disaster, you can fail over to the remote standby database and keep the database open.
Because the site uses three file systems, each on its own set of disks with its own controller, you decide to maintain the primary database files on the first file system, the standby database files on the second file system, and the ORACLE_HOME binaries on the third file system. If the primary database disks fail, you can switch to the standby database; if the ORACLE_HOME disks fail, you can switch to the remote standby database.
To host the standby database on the same system as the primary database, you must set the following parameters in the standby database initialization parameter file:
Because the primary database is shut down every Sunday for an hour for maintenance, you decide to use that time to make a cold, consistent backup. You can then restart the database while you make the necessary configurations for the standby database.
The next step in the procedure is to create the backup that will form the basis for the standby database. You know that you can use either an inconsistent or consistent backup, but because the database is shut down every Sunday for maintenance, you decide to make a consistent backup then and use it for the standby database.
On Sunday, before shutting down the primary database, you query the database to determine which datafiles it contains:
SQL> SELECT name FROM v$datafile; NAME ---------------------------------------------------------------------------- /fs1/dbs/tbs_01.dbf /fs1/dbs/tbs_02.dbf /fs1/dbs/tbs_11.dbf /fs1/dbs/tbs_12.dbf /fs1/dbs/tbs_21.dbf /fs1/dbs/tbs_22.dbf /fs1/dbs/tbs_13.dbf /fs1/dbs/tbs_23.dbf /fs1/dbs/tbs_24.dbf /fs1/dbs/tbs_31.dbf /fs1/dbs/tbs_32.dbf /fs1/dbs/tbs_41.dbf /fs1/dbs2/tbs_42.dbf /fs1/dbs2/tbs_51.dbf /fs1/dbs2/tbs_52.dbf /fs1/dbs2/tbs_03.dbf /fs1/dbs3/tbs_14.dbf /fs1/dbs3/tbs_25.dbf /fs1/dbs3/tbs_33.dbf /fs1/dbs3/tbs_43.dbf /fs1/dbs3/tbs_53.dbf 21 rows selected.
After determining which datafiles are in the database, you shut down the database with the IMMEDIATE
option:
SQL> SHUTDOWN IMMEDIATE;
At this point, you decide to back up all of the primary datafiles to a temporary directory as follows:
% cp /fs1/dbs/* /fs1/temp % cp /fs1/dbs2/* /fs1/temp % cp /fs1/dbs3/* /fs1/temp
You perform some other routine maintenance operations and then restart the database as follows:
SQL> STARTUP PFILE=initPRIMARY1.ora;
After a few minutes, you create the standby database control file in the same directory in which you stored the consistent backup:
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/fs1/temp/stbycf.ctl';
After you have successfully created the standby database control file, you can copy the datafiles and the standby database control file from the primary file system to the standby file system.
Because the transfer of datafiles can take a long time, first copy the control file, copy the datafiles, and then proceed to other tasks (such as network configuration). For example, enter the following at the UNIX command shell:
% cp /fs1/temp/stbycf.ctl /fs2/dbs/cf1.ctl % cp /fs1/temp/tbs* /fs2/dbs
To run a standby database in a Data Guard environment, you must configure an Oracle Net connection between the primary and standby databases so that you can archive the redo logs to the standby service.
You use the IPC protocol to connect the primary database to the standby database because both databases are on the same site. Because you are using the local naming method, you must create new entries in the tnsnames.ora
file. You must also add corresponding entries in the listener.ora
file.
Note: If, in the future, you choose to manage this standby database using the Data Guard broker, you must use the TCP/IP protocol instead of the IPC protocol. See Section 6.2.4 for an example using the TCP/IP protocol. |
tnsnames.ora
file.
Your next step is to open the tnsnames.ora
file in a text editor:
% vi /fs3/oracle/network/admin/tnsnames.ora
Currently, only one service name entry exists in the file, a TCP/IP connection to the primary1
database:
primary1 = (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp) (PORT=1521) (HOST=dlsun183)) (CONNECT_DATA=(SID=primary1)) )
To define an IPC connection between the primary and the standby database, you add an entry with the following format:
standby_service_name = (DESCRIPTION= (ADDRESS=(PROTOCOL=ipc) (KEY=keyhandle)) (CONNECT_DATA=(SID=standby_sid)))
Substitute appropriate values for standby_service_name, keyhandle,
and standby_sid,
as the following example shows:
standby1 = (DESCRIPTION= (ADDRESS=(PROTOCOL=ipc) (KEY=kstdby1)) (CONNECT_DATA=(SID=stdby1)))
listener.ora
file.
Your next step is to open the listener.ora
file, which is located on file system /fs3
:
% vi /fs3/oracle/network/admin/listener.ora
You discover the following list of addresses (where on the host the listener is listening) and SIDs (which connections the listener is listening for):
LISTENER = (ADDRESS_LIST= (ADDRESS=(PROTOCOL=tcp) (PORT=1521) (HOST=dlsun183)) ) SID_LIST_LISTENER = (SID_LIST= (SID_DESC=(SID_NAME=primary1) (ORACLE_HOME=/fs3/oracle)) )
Currently, the listener is listening on port 1521
of the host dlsun183
for database primary1
.
You need to edit the listener.ora
file and add two entries with the following format:
STANDBY_LISTENER = (ADDRESS_LIST=(ADDRESS=(PROTOCOL=ipc) (KEY=keyhandle))) SID_LIST_STANDBY_LISTENER = (SID_LIST= (SID_DESC=(SID_NAME=standby_sid) (ORACLE_HOME=/oracle_home)))
The listener.ora
file is typically located in the $ORACLE_HOME/network/admin
directory on the standby site. Substitute appropriate values for keyhandle, standby_sid,
and oracle_home,
as the following example shows:
STBY1_LISTENER=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=ipc) # same node as primary (KEY=kstdby1))) # ORACLE_SID standby instance is started with SID_LIST_STBY1_LISTENER = (SID_LIST= (SID_DESC=(SID_NAME=stdby1) (ORACLE_HOME=/fs3/oracle)))
Now that you have edited the listener.ora
file, you must start the listener:
% lsnrctl LSNRCTL for Solaris: Version 9.0.0.0.0 - Development on 09-MAR-2001 14:13:40 Copyright (c) 1991, 2001, Oracle Corporation. All rights reserved. Welcome to LSNRCTL, type "help" for information. LSNRCTL> start stby1_listener
As an alternative to the steps outlined in this section, you can use the Oracle Net Configuration Assistant graphical user interface to configure the network files.
Now that you have configured the network files, you can edit the primary database initialization parameter file. The primary database is now up and running, so these changes will only be enabled if you restart the instance or issue ALTER SESSION
or ALTER SYSTEM
statements.
The only changes you need to make to the file involve archiving to the standby service. Currently, the primary database initialization parameter file looks as follows:
db_name=primary1 control_files=(/fs1/dbs/cf1.ctl,/fs1/dbs/cf2.ctl) compatible=9.0.1.0.0 log_archive_start = true log_archive_dest_1 = 'LOCATION=/fs1/arc_dest/ MANDATORY REOPEN=60' log_archive_dest_state_1 = ENABLE log_archive_format = log_%t_%s.arc audit_trail=FALSE o7_dictionary_accessibility=false global_names=false db_domain=regress.rdbms.dev.us.oracle.com remote_login_passwordfile = exclusive # default parameters for instance 1 processes=30 sessions=30 transactions=21 transactions_per_rollback_segment=21 distributed_transactions=10 db_block_buffers=1000 db_files=200 shared_pool_size=10000000
Currently, you archive to only one location: a local directory. Because you want to maintain the local standby database in managed recovery mode, you must specify a new archiving location using a service name.
Open the primary database initialization parameter file with a text editor and examine the current archiving location and format:
log_archive_dest_1 = 'LOCATION=/fs1/arc_dest/ MANDATORY REOPEN=60' log_archive_dest_state_1 = ENABLE log_archive_format = log_%t_%s.arc
standby1
, you edit the file, adding the following entries:
log_archive_dest_2 = 'SERVICE=standby1 OPTIONAL REOPEN=180' log_archive_dest_state_2 = ENABLE
After editing the primary database initialization parameter file, create a copy for use by the standby database:
% cp /fs1/temp/initPRIMARY1.ora /fs3/oracle/dbs/initSTANDBY1.ora
If the primary database initialization parameter file contains the IFILE
parameter, you also need to copy the file referred to by the IFILE
parameter to the standby site and, if necessary, make appropriate changes to it.
You know that the initialization parameters shown in Table 6-1 play a key role in the standby database recovery process, and decide to edit them.
Edit the standby database parameter file as follows:
db_name = primary1 #The same as PRMYinit.ora control_files = (/fs2/dbs/cf1.ctl) compatible = 9.0.1.0.0 log_archive_start = true log_archive_dest_1='LOCATION=/fs2/arc_dest/' log_archive_dest_state_1 = ENABLE log_archive_format = log_%t_%s.arc standby_archive_dest = /fs2/arc_dest/ db_file_name_convert = ('/fs1/dbs','/fs2/dbs', '/fs1/dbs2','/fs2/dbs', '/fs1/dbs3','/fs2/dbs') log_file_name_convert = ('/fs1/dbs','/fs2/dbs') lock_name_space = standby1 fal_server=primary1 fal_client=standby1 audit_trail=false o7_dictionary_accessibility=false global_names=false db_domain=regress.rdbms.dev.us.oracle.com remote_login_passwordfile = exclusive # default parameters for instance 1 processes=30 sessions=30 transactions=21 transactions_per_rollback_segment=21 distributed_transactions=10 db_block_buffers=1000 db_files=200 shared_pool_size=10000000
Now that you have configured all network and parameter files, you can enable archiving to the standby database.
ORACLE_SID
environment variable to the same value as the SID
parameter in the tnsnames.ora
file on the primary site and the listener.ora
file on the standby site as follows:
% setenv ORACLE_SID stdby1
First, you start the standby database instance without mounting the standby database control file, as the following example shows:
SQL> CONNECT sys/change_on_install@standby1 AS sysdba SQL> STARTUP NOMOUNT PFILE=/fs3/oracle/dbs/initSTANDBY1.ora; SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
Finally, you enable the changes you made to the primary database parameter file so that the standby database can begin receiving archived redo logs:
SQL> CONNECT sys/change_on_install@primary1 AS sysdba SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2 = 'SERVICE=standby1
2> OPTIONAL REOPEN=180'; SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2 = ENABLE;
You can now enable managed recovery using the RECOVER MANAGED STANDBY DATABASE
statement. You decide to use the TIMEOUT
option of the RECOVER
statement to specify a time interval of 20 minutes that log apply services will wait until log transport services write the requested archived log entry to the directory of the standby database control file. If the requested archived log entry is not written to the standby database control file directory within the specified time interval, the recovery operation is canceled.
While connected to the standby database using SQL*Plus, place the standby database in managed recovery mode:
SQL> RECOVER MANAGED STANDBY DATABASE TIMEOUT 20;
The standby database is now in managed recovery. When you enable managed recovery, log apply services automatically identify and resolve any archive gaps that may exist. As the primary database archives redo logs to the standby site, the standby database automatically applies them.
This scenario describes the creation of a standby database standby1
on a remote site. The following assumptions are being made:
prmyhost
.
stbyhost
.
PRMYinit.ora
is the initialization parameter file for the primary database.
STBYinit.ora
is the initialization parameter file for the standby database.
Create the backup that will form the basis for the standby database.
V$DATAFILE
view to obtain a list of the primary database datafiles, as the following example shows:
SQL> SELECT name FROM v$datafile; NAME ----------------------------------------------------------------------- /oracle/dbs/dbf_1.dbf 1 row selected.
SQL> SHUTDOWN IMMEDIATE;
/backup
):
% cp /oracle/dbs/dbf_1.dbf /backup
SQL> STARTUP PFILE=PRMYinit.ora;
ARCHIVE LOG LIST
statement:
SQL> ARCHIVE LOG LIST
If the output from the ARCHIVE LOG LIST
statement displays "No Archive Mode
," perform the following steps:
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/backup/stbycf.ctl'
The standby database control file and the primary database datafiles are in the same temporary location at the primary site to make copying to the standby site easier.
Transfer the primary database datafiles and the standby control file from the temporary location at the primary site to the standby site, as the following example shows:
% rcp /backup/* stbyhost:/fs2/oracle/stdby
This scenario assumes that the TCP/IP network protocol is used to connect to the primary and the standby databases. To achieve remote archiving of redo log files, Oracle Net must be set up correctly from the primary database to the standby database. To allow the standby database to fetch archive gaps from the primary database, Oracle Net must again be set up correctly from the standby database to the primary database. This step involves editing the following files:
tnsnames.ora
file on the primary site.
You need to edit the tnsnames.ora
file and add an entry with the following format:
standby_service_name = (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp) (PORT=port_number) (HOST=host_name)) (CONNECT_DATA=(SID=standby_sid)))
The tnsnames.ora
file is typically located in the $ORACLE_HOME/network/admin
directory on the primary site. Substitute appropriate values for standby_service_name
, port_number
, host_name
, and standby_sid
, as the following example shows:
standby1 = (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp) (PORT=1521) (HOST=stbyhost)) (CONNECT_DATA=(SID=stdby1)))
listener.ora
file on the standby site.
You need to edit the listener.ora
file and add two entries with the following format:
STANDBY_LISTENER = (ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp) (PORT=port_number) (HOST=host_name))) SID_LIST_STANDBY_LISTENER = (SID_LIST= (SID_DESC=(SID_NAME=standby_sid) (ORACLE_HOME=/oracle_home)))
The listener.ora
file is typically located in the $ORACLE_HOME/network/admin
directory on the standby site. Substitute appropriate values for port_number
, host_name
, standby_sid
, and oracle_home
, as the following example shows:
STDBY1_LISTENER = (ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp) (PORT=1521) (HOST=stbyhost))) SID_LIST_STDBY1_LISTENER = (SID_LIST= (SID_DESC=(SID_NAME=stdby1) (ORACLE_HOME=/oracle)))
Make sure the SID_NAME
in the listener.ora
file matches the SID
in the tnsnames.ora
file. Also, make sure the PORT
and HOST
values are the same in the two files. You can either create a new listener or add a new address to an existing listener.
tnsnames.ora
file on the standby site.
You need to add a new TNS name entry at the standby site to allow the standby database to connect to the primary database. The following entry is added:
primary1 = (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(PORT=1601)(HOST=prmyhost)) (CONNECT_DATA=(SID=prmy)))
listener.ora
file on the primary site.
The following lines are added to the default LISTENER
entry:
LISTENER = ( ADDRESS_LIST=( . . . (ADDRESS=(PROTOCOL=tcp) (PORT=1601) (HOST=prmyhost))) . . . ) SID_LIST_LISTENER = ( SID_LIST= ( . . . (SID_DESC=(SID_NAME=prmy) (ORACLE_HOME=/oracle))) . . . )
Make sure the SID_NAME
in the listener.ora
file matches the SID
in the tnsnames.ora
file in step 3. Also make sure the PORT
and HOST
values are the same in the two files.
See Also:
Oracle9i Net Services Administrator's Guide for detailed directions on using the Oracle Net Manager |
Start the standby listener on the standby site. For example:
% lsnrctl start stdby1_listener
Normally, your default listener is started on your primary site. Restart the default listener on the primary database to pick up the new definitions. For example:
% lsnrctl stop % lsnrctl start
% rcp /oracle/dbs/PRMYinit.ora stbyhost:/fs2/oracle/stdby/STBYinit.ora
STBYinit.ora
). Edit the following parameters:
The STBYinit.ora
file looks as follows:
# #parameter file STBYinit.ora # db_name=primary1 # The same as PRMYinit.ora # The following parameter has changed from PRMYinit.ora control_files=/fs2/oracle/stdby/stbycf.ctl # The following parameters are the same as PRMYinit.ora audit_trail=false o7_dictionary_accessibility=false global_names=false db_domain=regress.rdbms.dev.us.oracle.com commit_point_strength=1 processes=30 sessions=30 transactions=21 transactions_per_rollback_segment=21 distributed_transactions=10 db_block_buffers=100 shared_pool_size=4000000 ifile=/oracle/work/tkinit.ora # Verify that file exists on the standby site # and that the file specification is valid # specific parameters for standby database log_archive_format = stdby_%t_%s.arc standby_archive_dest=/fs2/oracle/stdby/ log_archive_dest_1='LOCATION=/fs2/oracle/stdby/' db_file_name_convert=('/oracle/dbs','/fs2/oracle/stdby') log_file_name_convert=('/oracle/dbs','/fs2/oracle/stdby') log_archive_start=true log_archive_trace=127 fal_server=standby1 fal_client=primary1
STBYinit.ora
file by issuing the following command:
% cp STBYinit.ora Failover.ora
Edit Failover.ora
so if you fail over to the stdby1
standby database, you can use the Failover.ora
file as the initialization parameter file for the new primary database. Make sure you use appropriate values for the LOG_ARCHIVE_DEST_
n
parameters.
tnsnames.ora
file on the standby site in case failover to the standby database occurs. See Section 6.2.4 for information on how to configure the tnsnames.ora
file.
Start the standby database to enable archiving.
ORACLE_SID
environment variable to the same value as the SID
parameter in the tnsnames.ora
file on the primary site and the listener.ora
file on the standby site as follows:
% SETENV ORACLE_SID stdby1
SQL> CONNECT sys/sys_password as sysdba
SQL> STARTUP NOMOUNT PFILE=STBYinit.ora;
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
PRMYinit.ora
file:
LOG_ARCHIVE_DEST_2 = 'SERVICE=standby1 MANDATORY REOPEN=60'
PRMYinit.ora
file:
LOG_ARCHIVE_DEST_STATE_2 = ENABLE
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=standby1 2> MANDATORY REOPEN=60'; SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
On the standby database, enable managed recovery by issuing the following SQL statement:
SQL> RECOVER MANAGED STANDBY DATABASE;
This scenario describes the procedures you should follow when a physical change is made in the primary database. The following topics are covered:
To maintain consistency when you add a datafile to the primary database, you must also add a corresponding datafile to the standby database. Otherwise, changes in the online redo logs that relate to the new datafile in the primary database will not be applied to the standby database. The steps you perform depend on how the datafile was created on the primary database and how the initialization parameter STANDBY_FILE_MANAGEMENT
is defined on the standby database.
Section 6.3.1.1 illustrates the steps to enable the automatic creation of datafiles on the standby database. Section 6.3.1.2 illustrates the manual steps necessary if standby file management is not automatic.
When you create a new datafile on the primary database, the datafile is automatically created on the standby database if the initialization parameter STANDBY_FILE_MANAGEMENT
is set to auto
in the standby database initialization file.
The following steps describe how to set up your standby database to automate the creation of datafiles.
STANDBY_FILE_MANAGEMENT=auto
SQL> SHUTDOWN; SQL> STARTUP NOMOUNT pfile=initSTANDBY.ora SQL> ALTER DATABASE MOUNT STANDBY DATABASE; SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
Alternatively, you can dynamically set the STANDBY_FILE_MANAGEMENT
initialization parameter with an ALTER SYSTEM
statement to avoid shutting down and restarting the instance. For example:
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=auto;
SQL> CREATE TABLESPACE new_ts datafile 't_db2.dbf' 2> SIZE 1m AUTOEXTEND ON MAXSIZE UNLIMITED;
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
SQL> SELECT name FROM v$datafile; NAME ---------------------------------------------------------------------- /oracle/dbs/t_db1.dbf /oracle/dbs/t_db2.dbf
SQL> SELECT name FROM v$datafile; NAME ---------------------------------------------------------------------- /oracle/dbs/s2t_db1.dbf /oracle/dbs/s2t_db2.dbf
The following steps illustrate how to manually create datafiles on the standby database when standby file management is manual.
V$LOG
fixed view on the primary database with the V$MANAGED_STANDBY
fixed view on the standby database.
SQL> -- on the primary SQL> SELECT SEQUENCE#, ARCHIVED, STATUS 2> FROM v$log 3> WHERE STATUS = 'CURRENT'; SEQUENCE# ARC STATUS ---------- --- ---------------- 123 NO CURRENT SQL> -- on the standby SQL> SELECT PROCESS, STATUS, SEQUENCE# 2> FROM v$managed_standby; PROCESS STATUS SEQUENCE# ------- ------------ ---------- MRP0 WAIT_FOR_LOG 123
SQL> SHUTDOWN;
SQL> CREATE TABLESPACE new_ts DATAFILE 't_db2.dbf' 2> SIZE 1m AUTOEXTEND ON MAXSIZE UNLIMITED;
SQL> SELECT name FROM v$datafile; NAME ---------------------------------------------------------------------- /oracle/dbs/t_db1.dbf /oracle/dbs/t_db2.dbf
SQL> ALTER TABLESPACE new_ts OFFLINE; % cp t_db2.dbf s2t_db2.dbf SQL> ALTER TABLESPACE new_ts ONLINE;
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'scf2.ctl' REUSE; % rcp scf2.ctl <standby location>
SQL> STARTUP NOMOUNT pfile=initSTANDBY.ora SQL> ALTER DATABASE MOUNT STANDBY DATABASE; SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
SQL> SELECT name FROM v$datafile; NAME ---------------------------------------------------------------------- /oracle/dbs/s2t_db1.dbf /oracle/dbs/s2t_db2.dbf
When you rename one or more datafiles in the primary database, you also need to rename the corresponding datafiles in the standby database.
SQL> ALTER TABLESPACE tbs_4 OFFLINE;
% mv tbs_4.dbf tbs_x.dbf
SQL> ALTER TABLESPACE tbs_4 RENAME DATAFILE 'tbs_4.dbf' TO 'tbs_x.dbf'; SQL> ALTER TABLESPACE tbs_4 ONLINE;
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'stbycf.ctl';
SQL> RECOVER AUTOMATIC STANDBY DATABASE;
IMMEDIATE
option:
SQL> SHUTDOWN IMMEDIATE;
% rcp stbycf.ctl stbyhost:/fs2/oracle/stdby/
% mv tbs_4.dbf tbs_x.dbf
SQL> STARTUP NOMOUNT PFILE=STBYinit.ora;
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
SQL> RECOVER MANAGED STANDBY DATABASE;
If you do not rename the corresponding file at the standby site, and then attempt to refresh the standby database control file, the standby database will attempt to use the renamed datafile, but it will not find the renamed datafile. Consequently, you will see error messages similar to the following:
ORA-00283: recovery session canceled due to errors ORA-01157: cannot identify/lock data file 4 - see DBWR trace file ORA-01110: data file 4: '/oracle/dbs/stdby/tbs_x.dbf'
When you delete one or more datafiles or drop one or more tablespaces in the primary database, you also need to delete the corresponding datafiles or drop the corresponding tablespaces in the standby database. You also need to refresh the standby database control file.
SQL> DROP TABLESPACE tbs_4; % rm tbs_4.dbf
% rm tbs_4.dbf
If the STANDBY_FILE_MANAGEMENT
initialization parameter is set to auto
, dropped tablespaces and deleted datafiles from the primary database are automatically dropped or deleted from the standby database. However, if the STANDBY_FILE_MANAGEMENT
initialization parameter is set to manual
, or if the parameter is not defined, you must manually drop tablespaces or delete datafiles from the standby database and re-create the standby control file.
One method of tuning available to the database administrator (DBA) is changing the size and number of online redo logs. Consequently, when you add or drop an online redo log at the primary site, it is important that you refresh the standby database control file.
SQL> ALTER DATABASE ADD LOGFILE 'prmy3.log' SIZE 100K;
or
SQL> ALTER DATABASE DROP LOGFILE 'prmy3.log';
If you use the CREATE CONTROLFILE
statement in the primary database to change one or more database parameters, you need to refresh the standby database control file. Some of the parameters you can change with this statement are the maximum number of redo log file groups, redo log file members, archived redo log files, datafiles, or instances that can concurrently have the database mounted and open.
SQL> CREATE CONTROLFILE REUSE DATABASE primary1 NORESETLOGS 2> LOGFILE 'prmy1.log' SIZE 100K, 'prmy2.log' SIZE 100K 3> DATAFILE 'dbf_1.dbf' SIZE 10M MAXLOGFILES 52 ARCHIVELOG;
In some cases, when you make a physical change to the primary database, in addition to making the corresponding change to the standby database, you also need to refresh the standby database control file. The online redo logs do not record changes made to the primary database control file. This section describes the cases where you need to refresh the standby database control file and the steps to follow.
You need to refresh the standby database control file whenever you:
Whether you must refresh the control file after dropping a tablespace depends on the setting of the STANDBY_FILE_MANAGEMENT
initialization parameter. If the parameter is not set, or it is set to manual
, you must refresh the control file after dropping a tablespace. If the parameter is set to auto
, you do not have to refresh the control file after dropping a tablespace, as this is done automatically.
Perform the following steps to keep the standby database control file synchronized with the primary database control file:
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'stbycf.ctl';
SQL> RECOVER MANAGED STANDBY DATABASE CANCEL;
IMMEDIATE
option:
SQL> SHUTDOWN IMMEDIATE;
% rcp stbycf.ctl stbyhost:/fs2/oracle/stdby/
SQL> STARTUP NOMOUNT PFILE=STBYinit.ora;
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
SQL> RECOVER MANAGED STANDBY DATABASE;
You may get the following error messages when you try to enable managed recovery:
ORA-00308: cannot open archived log 'standby1' ORA-27037: unable to obtain file status SVR4 Error: 2: No such file or directory Additional information: 3 ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below ORA-01152: file 1 was not restored from a sufficiently old backup ORA-01110: data file 1: '/oracle/dbs/stdby/tbs_1.dbf'
If you get the ORA-00308 error, cancel recovery by issuing the following statement:
SQL> CANCEL
These error messages are issued when one or more logs in the archive gap have not been successfully applied. If you receive these errors, manually resolve the gaps and repeat step 7.
Some physical changes you make to the primary database can invalidate the standby database. Once a standby database is invalidated, your only option is to rebuild it.
The following clauses of the ALTER DATABASE
statement invalidate the standby database:
In some SQL statements, the user has the option of specifying the NOLOGGING
clause, which indicates that the database operation is not logged in the redo log file. Even though the user specifies the NOLOGGING
clause, a redo log record is still written to the redo log. However, when the redo log file is copied to the standby site and applied to the standby database, a portion of the datafile is unusable and marked as being unrecoverable. When you either activate the standby database, or open the standby database with the read-only option, and attempt to read the range of blocks that are marked as UNRECOVERABLE
, you will see error messages similar to the following:
ORA-01578: ORACLE data block corrupted (file # 1, block # 2521) ORA-01110: data file 1: '/oracle/dbs/stdby/tbs_1.dbf' ORA-26040: Data block was loaded using the NOLOGGING option
To recover after the NOLOGGING
clause is specified, you need to copy the datafile that contains the unjournaled data from the primary site to the standby site. Perform the following steps:
SQL> SELECT name, unrecoverable_change# FROM v$datafile; NAME UNRECOVERABLE ----------------------------------------------------- ------------- /oracle/dbs/tbs_1.dbf 5216 /oracle/dbs/tbs_2.dbf 0 /oracle/dbs/tbs_3.dbf 0 /oracle/dbs/tbs_4.dbf 0 4 rows selected.
SQL> SELECT name, unrecoverable_change# FROM v$datafile; NAME UNRECOVERABLE ----------------------------------------------------- ------------- /oracle/dbs/stdby/tbs_1.dbf 5186 /oracle/dbs/stdby/tbs_2.dbf 0 /oracle/dbs/stdby/tbs_3.dbf 0 /oracle/dbs/stdby/tbs_4.dbf 0 4 rows selected.
Compare the value of the UNRECOVERABLE_CHANGE#
column in both query results. If the value of the UNRECOVERABLE_CHANGE#
column in the primary database is greater than the same column in the standby database, then the datafile needs to be copied from the primary site to the standby site.
In this example, the value of the UNRECOVERABLE_CHANGE#
in the primary database for the tbs_1.dbf
datafile is greater, so you need to copy the tbs_1.dbf
datafile to the standby site.
SQL> ALTER TABLESPACE system BEGIN BACKUP; SQL> EXIT; % cp tbs_1.dbf /backup SQL> ALTER TABLESPACE system END BACKUP;
In the primary database, issue the following statement:
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/backup/stbycf.ctl;
In the standby database, issue the following statement:
SQL> SHUTDOWN IMMEDIATE;
% rcp /backup/* stbyhost:/fs2/oracle/stdby/
SQL> STARTUP NOMOUNT PFILE=STBYinit.ora;
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
SQL> RECOVER MANAGED STANDBY DATABASE;
You may get the following error messages when you try to enable managed recovery:
ORA-00308: cannot open archived log 'standby1' ORA-27037: unable to obtain file status SVR4 Error: 2: No such file or directory Additional information: 3 ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below ORA-01152: file 1 was not restored from a sufficiently old backup ORA-01110: data file 1: '/oracle/dbs/stdby/tbs_1.dbf'
If you get the ORA-00308 error, cancel recovery by issuing the following statement:
SQL> CANCEL
These error messages are issued when one or more logs in the archive gap have not been successfully applied. If you receive these errors, manually resolve the gaps and repeat step 8.
Every standby database is associated with one and only one primary database. A single primary database can, however, support multiple standby databases. This scenario identifies the kind of information you need in order to decide which of the multiple standby databases to activate.
One of the important things to consider in a multiple standby database configuration is whether the archive destination is mandatory or optional. The following table lists an advantage and disadvantage for each destination:
Destination | Advantage | Disadvantage |
---|---|---|
|
All archived redo logs are archived to the mandatory archive destination. After you apply the archived redo logs at the standby site, you can ensure that the standby database is up-to-date. Furthermore, you can activate the standby database as the new primary database with minimum loss of data. |
In some cases, (such as network failure), the archived redo logs cannot reach the mandatory archive destination, causing the archiving of the redo log to stop. In the worst case, if all online redo logs are full and cannot be archived, the primary database instance will stop working. You can issue the following SQL query to determine whether the primary database stopped because it was not able to switch to an online redo log:
If the output from the query displays " See Section 6.8. |
|
The primary database continues to operate normally when archival of the redo logs to the optional archive destination at the standby site is interrupted. |
An archive gap may cause data loss because archive logs that are required to be applied to the standby database are unavailable. This results in the managed recovery operation terminating before all primary data has been applied to the standby database. |
Consider the following recommendations in a multiple standby database configuration:
Suppose the primary database is located in San Francisco and supports five standby databases as follows:
Standby | Location | Type | Description |
---|---|---|---|
1 |
Local directory |
Mandatory |
Local copy of the archived redo logs. |
2 |
San Francisco |
Mandatory |
Fail over to this standby database when there is physical damage at the primary site. This standby site is connected to the primary site by a local area network. |
3 |
Boston |
Optional |
Fail over to this standby database when a disaster occurs that affects San Francisco. |
4 |
Los Angeles |
Optional |
This standby site receives archived redo logs, but does not apply them. See Section 6.10 for a description of this type of configuration. |
5 |
San Francisco |
Optional |
This standby site receives archived redo logs, and applies them after an 8-hour time lag. See Section 6.11 for a description of this type of configuration. |
Assume that a disaster occurs in San Francisco where the primary site is located, and the primary site is damaged. One of the standby databases must be activated. You cannot assume that the database administrator (DBA) who set up the multiple standby database configuration is available to decide which standby database to fail over to. Therefore, it is imperative to have a disaster recovery plan at each standby site, as well as at the primary site. Each member of the disaster recovery team needs to know about the disaster recovery plan and be aware of the procedures to follow. This scenario identifies the kind of information that the person who is making the decision would need when deciding which standby database to activate.
One method of conveying information to the disaster recovery team is to include a ReadMe file at each standby site. This ReadMe file is created and maintained by the DBA and should describe how to:
There may be firewalls between systems. The ReadMe file should include instructions for going through the firewalls.
The following example shows the contents of a sample ReadMe file:
----------------Standby Database Disaster Recovery ReadMe File---------------- Warning: ******************************************************************************** Perform the steps in this procedure only if you are responsible for failing over to a standby database after the primary database fails. If you perform the steps outlined in this file unnecessarily, you may corrupt the entire database system. ******************************************************************************** Multiple Standby Database Configuration: No. Location Type IP Address --- --------------- --------- -------------- 1 San Francisco Primary 128.1.124.25 2 San Francisco Standby 128.1.124.157 3 Boston Standby 136.132.1.55 4 Los Angeles Standby 145.23.82.16 5 San Francisco Standby 128.1.135.24 You are in system No. 3, which is located in Boston. Perform the following steps to fail over to the most up-to-date and available standby database: 1. Log on to the local standby database as a DBA. a) Log on with the following user name and password: username: Standby3 password: zkc722Khn b) Invoke SQL*Plus as follows: % sqlplus c) Connect as the DBA as follows: CONNECT sys/s23LsdIc AS SYSDBA 2. Connect to as many remote systems as possible. You can connect to a maximum of four systems. System 4 does not have a firewall, so you can connect to it directly. Systems 1, 2, and 5 share the same firewall host. You need to go to the firewall host first and then connect to each system. The IP address for the firewall host is 128.1.1.100. Use the following user name and password: username: Disaster password: 82lhsIW32 3. Log on to as many remote systems as possible with the following user names and passwords: Login information: No. Location IP Address username password --- --------------- ------------- ---------- ---------- 1 San Francisco 128.1.124.25 Oracle9i sdd290Ec 2 San Francisco 128.1.124.157 Standby2 ei23nJHb 3 (L o c a l) 4 Los Angeles 145.23.82.16 Standby4 23HHoe2a 5 San Francisco 128.1.135.24 Standby5 snc#$dnc 4. Invoke SQL*Plus on each remote system you are able to log on to as follows: % sqlplus 5. Connect to each remote database as follows: CONNECT sys/password AS SYSDBA The DBA passwords for each location are: No. Location Password --- --------------- ----------- 1 San Francisco x2dwlsd91 2 San Francisco a239s1DAq 3 (L o c a l) 4 Los Angeles owKL(@as23 5 San Francisco sad_KS13x 6. If you are able to log on to System 1, invoke SQL*Plus and issue the following statements: SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP PFILE=PRMYinit.ora; Note: If you are able to execute the STARTUP statement successfully, the primary database has not been damaged. Do not continue with this procedure. 7. Issue the following SQL statements on each standby database (including the one on this system) that you were able to connect to: SQL> RECOVER MANAGED STANDBY DATABASE CANCEL; SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP NOMOUNT PFILE=STBYinit.ora; SQL> ALTER DATABASE MOUNT STANDBY DATABASE; SQL> RECOVER AUTOMATIC STANDBY DATABASE; *** press the Return key for each of the prompts SQL> SELECT THREAD#, MAX(SEQUENCE#) FROM V$LOG_HISTORY GROUP BY THREAD#; Compare the query results of each standby database. Activate the standby database with the largest sequence number. 8. Fail over to the standby database with the largest sequence number. On the standby database with the largest sequence number, invoke SQL*Plus and issue the following SQL statements: SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE; SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP PFILE=Failover.ora; ------------End of Standby Database Disaster Recovery ReadMe File-------------
Reversing the roles of a primary and standby database is sometimes referred to as a switchover or a switchback operation. The following steps outline what commands must be issued to perform a switchover operation.
For this discussion, boston
is initially the primary database and la
is initially the standby database. During the following switchover scenario, la
will become the primary database and boston
will become the standby database.
This scenario assumes that the primary and standby databases have been previously created and initialized.
Exclusive database access is required by the DBA before beginning a switchover operation. Ask users to log off the primary and standby databases or query the V$SESSION
view to identify users that are connected to the databases and close all open sessions except the SQL*Plus session from which you are going to issue the switchover command.
On the primary database, boston
, execute the following statement:
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;
This statement does the following:
Execute the following statement on boston
:
SQL> SHUTDOWN NORMAL; SQL> STARTUP NOMOUNT;
Execute the following statement on boston
:
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
Execute the following statement on la
:
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
This statement does the following:
end-of-redo
marker.
Execute the following statement on la
:
SQL> SHUTDOWN;
Execute the following statement on la
:
SQL> STARTUP;
Execute the following statement on the standby database, boston
, to place it in managed recovery mode:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;
Execute the following statement on the primary database, la
:
SQL> ALTER SYSTEM ARCHIVE LOG START; SQL> ALTER SYSTEM SWITCH LOGFILE;
When a standby database is activated, it becomes a primary database, and is no longer capable of serving as a standby database. Client applications must redirect their connections from the original primary database to the newly activated primary database. This scenario describes the following ways to set up client failover:
The tnsnames.ora
file can be configured for multiple addresses. In a local TNS configuration, at least one of the addresses should be the address of a standby site. Modify the tnsnames.ora
file at each client site to ensure that an address for a standby site has been supplied. The tnsnames.ora
file is typically located in the $ORACLE_HOME/network/admin
directory. You can assign multiple addresses to one TNS name and use the FAILOVER
option.
See Also:
Oracle9i Net Services Administrator's Guide for details about how to set multiple addresses and how to use the |
The following example shows an entry that has an address for a standby site in addition to the address for the primary site:
ProductDB = ( DESCRIPTION= (FAILOVER=ON) (LOAD_BALANCE=OFF) (ADDRESS=(PROTOCOL=tcp)(PORT=1521)(HOST=prmyhost.foo.com)) (ADDRESS=(PROTOCOL=tcp)(PORT=1521)(HOST=stbyhost.foo.com)) (CONNECT_DATA=(SID=db1)) )
In this example, the primary database is located at prmyhost.foo.com
, and the standby database is located at stbyhost.foo.com
. When the client application connects to ProductDB
, it tries to send a connection request to prmyhost.foo.com
first. If there is no response, the client application tries to send another connection request to stbyhost.foo.com
. When the primary database is down and the standby database is activated, the client application can connect to the new primary database automatically.
If the primary database fails after the connection has been established, the client application will not automatically direct the remaining request to the newly activated primary database. You must establish the connection to the ProductDB
database again.
If you are using an Oracle Names Server, you can change the TNS name settings on the server. You can assign multiple addresses to one TNS name and use the FAILOVER
option.
See Also:
Oracle9i Net Services Administrator's Guide for details about how to set multiple addresses and how to use the |
The format for setting up the TNS name on the Oracle Names server is the same as the format for the local tnsnames.ora
file. Therefore, the example in Section 6.7.1 also applies to the Oracle Names server configuration.
The following configurations require that the client reconnect to the database server when the primary database fails over to the standby database:
However, if you are using an Oracle Call Interface (OCI) client, you can use transparent application failover (TAF). TAF is the ability of applications to automatically reconnect to the database if the connection fails. If the client application is not involved in a database transaction, then users may not notice the failure of the primary database server.
The following example shows address information for the ProductDB
database and the STANDBY1
database:
ProductDB=( DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(PORT=1521)(HOST=prmyhost.foo.com)) (CONNECT_DATA=(SID=db1)(FAILOVER_MODE=(BACKUP=Standby1) (TYPE=session) (METHOD=basic))) ) Standby1 =( DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(PORT=1521)(HOST=stbyhost.foo.com)) (CONNECT_DATA=(SID=db1)) )
Sequence of events:
ProductDB
database.
prmyhost.foo.com
fails.
prmyhost.foo.com
, it uses the database specified with the BACKUP
parameter in the FAILOVER_MODE
clause, and automatically connects to stbyhost.foo.com
.
Instead of setting the configurations so that the client can automatically fail over to the new primary database, you can always choose to manually modify the network settings after the standby database is activated.
You can modify the local tnsnames.ora
file. Redirect the TNS name pointing to the original primary database to the newly activated primary database. For example, assume the original TNS setting is as follows:
ProductDB=( DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(PORT=1521)(HOST=prmyhost.foo.com)) (CONNECT_DATA=(SID=db1)) )
When the primary database on prmyhost.foo.com
fails, and the standby database in stbyhost.foo.com
is activated, causing it to become the new primary database, you need to edit the tnsnames.ora
file and change the entry to the following:
ProductDB=( DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(PORT=1521)(HOST=stbyhost.foo.com)) (CONNECT_DATA=(SID=db1)) )
You do not need to change your client application; subsequent connections to the primary database will be sent to the new primary database on stbyhost.foo.com
.
If you are using an Oracle Names server, make a similar change for the corresponding entry. All clients using this Oracle Names server will send their subsequent connections to the new primary database on stbyhost.foo.com
.
You can also change the settings on the DNS server. Change the settings for the domain name, which is used by the clients to locate the primary database.
For example, assume the following:
Change the DNS server entry for the domain name in the DNS server to the following:
ProductDB.foo.com IN A 128.3.151.63
After you change the DNS settings, not all clients know about the change immediately. The old DNS settings may be cached somewhere, causing some clients to continue to use the old settings. The old settings must be replaced with the new settings.
When a standby database is in managed recovery mode, the standby database automatically applies archived redo logs as it receives them from the primary database. When the network goes down, automatic archival from the primary database to the standby database stops.
If the standby database is specified as an optional archive destination, then the primary database continues to operate normally.
When the network is up and running again, automatic archival of the archived redo logs from the primary database to the standby database resumes. However, if the standby database is specified as an optional archive destination, and a log switch occurred at the primary site, the standby database has an archive gap for the time when the network was down. The archive gap is automatically detected and resolved when managed recovery mode is enabled (when FAL_SERVER
and FAL_CLIENT
are defined in the initialization parameter file).
If the standby database is specified as a mandatory archive destination, then the primary database will not archive any redo logs until the network failure is resolved and the primary database is able to archive to the standby site.
The primary database may eventually stall if the network problem is not fixed in a timely manner, because the primary database will not be able to switch to an online redo log that has not been archived. You can issue the following SQL query to determine whether the primary database stalled because it was not able to switch to an online redo log:
SELECT decode(COUNT(*),0,'NO','YES') "switch_possible" FROM V$LOG WHERE ARCHIVED='YES';
If the output from the query displays "Yes
," a log switch is possible; if the output displays "No
," a log switch is not possible.
It is important to specify a local directory at the primary site as a mandatory archive destination, so that all of the archived redo logs reside on the same system as the primary database. When the primary system is unreachable, and the primary database is part of a multiple standby database configuration, you can try to identify the archived redo logs at the other standby sites.
This scenario describes how to recover after a network failure.
The V$ARCHIVE_DEST
view contains the network error and identifies which standby database cannot be reached. On the primary database, issue the following SQL statement for the archived log destination that experienced the network failure. For example:
SQL> SELECT DEST_ID, STATUS, ERROR FROM V$ARCHIVE_DEST WHERE DEST_ID = 2; DEST_ID STATUS ---------- --------- ERROR ----------------------------------------------------------------- 2 ERROR ORA-12224: TNS:no listener
The query results show there are errors archiving to the standby database, and the cause of the error as TNS:no listener
. You should check whether the listener on the standby site is started. If the listener is stopped, then start it.
If you cannot solve the network problem quickly, and if the standby database is specified as a mandatory destination, try to prevent the database from stalling by doing one of the following:
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2 = DEFER;
When the network problem is resolved, you can enable the archive destination again:
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2 = ENABLE;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2 = 'SERVICE=standby1
2> OPTIONAL REOPEN=60';
When the network problem is resolved, you can change the archive destination from optional back to mandatory:
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2 = 'SERVICE=standby1
2> MANDATORY REOPEN=60';
In some cases, you may not be the person responsible for correcting the problem. You can periodically query the V$ARCHIVE_DEST
view to see if the problem has been resolved.
On the primary database, archive the current redo log:
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
When the network is back up again, log apply services can detect and resolve the archive gaps automatically if you place the standby database in managed recovery mode.
This scenario describes the case where you have failed over to a standby database and have begun using it as a normal primary database. After a period of time, you decide you want to fail back to the original primary system and make the primary database the standby database again.
% rcp STBYinit.ora PRMYHOST:fallback.ora
The fallback.ora
file will become the standby initialization parameter file for the standby database at the original primary site.
fallback.ora
file. You need to modify the following parameters:
The fallback.ora
file looks as follows:
# #parameter file fallback.ora # db_name=primary1 #The same as PRMYinit.ora control_files=/fs2/oracle/stdby/cf1.ctl lock_name_space=fallback; audit_trail=false o7_dictionary_accessibility=false global_names=false db_domain=regress.rdbms.dev.us.oracle.com commit_point_strength=1 processes=30 sessions=30 transactions=21 transactions_per_rollback_segment=21 distributed_transactions=10 db_block_buffers=100 shared_pool_size=4000000 ifile=/oracle/work/tkinit.ora # specific parameters for standby database log_archive_format = r_%t_%s.arc standby_archive_dest=/oracle/dbs/ log_archive_dest_1='LOCATION=/oracle/dbs/' log_archive_dest_state_1 = ENABLE db_file_name_convert=('/fs2/oracle/stdby','/oracle/dbs') log_file_name_convert=('/fs2/oracle/stdby','/oracle/dbs') log_archive_start=true log_archive_trace=127
You need to supply appropriate values for the LOG_ARCHIVE_DEST_1
and LOG_ARCHIVE_DEST_STATE_1
initialization parameters.
Note: Section 6.2.7 suggested that you make a copy of the standby database initialization parameter file. If you made a copy, then you can modify the copy in this step. |
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT; SQL> SHUTDOWN IMMEDIATE;
% rcp /fs2/oracle/stdby/stdby_1_102.arc prmyhost:/oracle/dbs/r_1_102.arc % rcp /fs2/oracle/stdby/stdby_1_103.arc prmyhost:/oracle/dbs/r_1_103.arc
SQL> CONNECT sys/sys_password AS SYSDBA SQL> STARTUP NOMOUNT PFILE=fallback.ora; SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE; SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP PFILE=PRMYinit.ora;
Create a new standby database at the original standby site.
This scenario describes what the DBA needs to do when archived online redo logs are automatically copied to the standby site, but they are not automatically applied to the standby database.
For example, suppose a standby database is set up on a site running a Web server. The workload for the site is very heavy, and you do not want to add workload to the site by automatically applying redo logs as they arrive from the primary site. You decide to let the site receive archived redo logs without applying them. Thus, the system resources for applying the redo logs will be saved. Later, if you decide to open the standby database as a read-only database to perform some queries, or switch over the standby database as a primary database, you can stop the Web server and apply all of the redo logs at one time.
This scenario assumes that you already have a standby database set up. Section 6.2 describes how to set up a standby database.
The listener for the standby database should be started. The standby database should be started and mounted, but not in recovery mode. (Thus, the time and resources for applying redo logs will be saved.)
This scenario is similar to Section 6.2. However, in Section 6.2, the standby database is in managed recovery mode. When a standby database is in managed recovery mode, it automatically applies archived redo logs received from the primary site.
When the archived redo logs are not immediately applied to the standby database, there is no ongoing recovery at the standby site. This section describes the tasks a DBA needs to consider when managing a standby database with no ongoing recovery. This section covers the following topics:
When a datafile is copied to the primary site, you need to decide what action to take on the standby site. You have the following options:
When you attempt to archive the redo logs, messages similar to the following are displayed:
ORA-00283: recovery session canceled due to errors ORA-01157: cannot identify/lock data file 4 - see DBWR trace file ORA-01110: data file 4: '/oracle/dbs/stdby/tbs_4.dbf'
Create the correponding new datafile by issuing the following statement:
SQL> ALTER DATABASE CREATE DATAFILE '/oracle/dbs/stdby/tbs_4.dbf';
Note: When you create a datafile in the primary database, you can set up your environment so that a corresponding datafile is also created in the standby database. See Section 6.3.1.1. |
When the control file on the primary site has been altered, if you want to account for it on the standby site, you should apply the redo logs at the standby site by issuing the following statement:
SQL> RECOVER AUTOMATIC STANDBY DATABASE;
After you apply the redo logs at the standby site, refresh the standby database control file.
To recover after the NOLOGGING
clause is specified at the primary site, you must do the following:
SQL> RECOVER AUTOMATIC STANDBY DATABASE;
If you can afford to lose the changes incurred by specifying NOLOGGING
at the primary site, you may choose to do nothing.
Before you activate the standby database, you should apply all applicable redo logs. You must resolve any gaps in the redo log sequence before you activate the standby database, as outlined in the following steps:
V$ARCHIVE_GAP
view on the standby database as follows:
SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP; THREAD# LOW_SEQUENCE# HIGH_SEQUENCE# ---------- ------------- -------------- 1 90 92
The archive gap is the LOW_SEQUENCE#
to the HIGH_SEQUENCE#
. In this example, the gap is 90, 91, and 92 for thread 1. If no gap is selected in this step, go to step 5.
Obtain the filenames of the logs in the archive gap by performing a query on the V$ARCHIVED_LOG
view on the primary database as follows:
SELECT name FROM V$ARCHIVED_LOG WHERE thread#=1 AND sequence#<=92 AND sequence#>=90; NAME ----------------------------------------- /oracle/dbs/r_1_90.arc /oracle/dbs/r_1_91.arc /oracle/dbs/r_1_92.arc
% rcp /oracle/dbs/r_1_90.arc stbyhost:/fs2/oracle/stdby/stdby_1_90.arc % rcp /oracle/dbs/r_1_91.arc stbyhost:/fs2/oracle/stdby/stdby_1_91.arc % rcp /oracle/dbs/r_1_92.arc stbyhost:/fs2/oracle/stdby/stdby_1_92.arc
It is important to specify a local directory at the primary site as a mandatory archive destination so that all of the archived redo logs reside on the same system as the primary database. When the primary system is unreachable, and the primary database is part of a multiple standby database configuration, you can try to identify the archived redo logs at the other standby sites.
SQL> RECOVER AUTOMATIC STANDBY DATABASE;
Repeat steps 1 through 4 until there are no more gaps.
SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
SQL> SHUTDOWN IMMEDIATE;
If necessary, build the parameter file for the new primary instance. You can build it from the parameter file for the standby database. Then, you can issue the following statement at the standby database:
SQL> STARTUP PFILE=FailOver.ora;
By default, in managed recovery mode, the standby database automatically applies redo logs when they arrive from the primary database. But in some cases, you may not want the logs to be applied immediately, because you want to create a time lag between the archiving of a redo log at the primary site and the application of the log at the standby site. A time lag can protect against the transfer of corrupted or erroneous data from the primary site to the standby site.
For example, suppose you run a batch job every night on the primary database. Unfortunately, you accidently ran the batch job twice and you did not realize the mistake until the batch job completed for the second time. Ideally, you need to roll back the database to the point in time before the batch job began. A primary database that has a standby database with a time lag (for example, 8 hours) could help you to recover. You could activate the standby database with the time lag and use it as the new primary database.
To create a standby database with a time lag, use the DELAY
attribute of the LOG_ARCHIVE_DEST_
n
initialization parameters in the primary database initialization parameter file. The archived redo log files are still automatically copied from the primary site to the standby site, but the log files are not immediately applied to the standby database. The log files are applied when the specified time interval has expired.
This scenario use a 4-hour time lag and covers the following topics:
Readers of this scenario are assumed to be familiar with the procedures for creating a typical standby database. The details have been omitted from the steps outlined in this scenario.
Perform the following steps to create a standby database with a time lag:
tnsnames.ora
and listener.ora
network files.
Edit the STANDBY_ARCHIVE_DEST
parameter.
You must use the directory specified by the STANDBY_ARCHIVE_DEST
parameter when performing managed recovery.
For example, assume you have set the parameter to the following value:
STANDBY_ARCHIVE_DEST=/fs2/oracle/stdby_log/
Edit the LOG_ARCHIVE_DEST_
n
initialization parameter to include the DELAY
keyword.
For example, to specify a 4-hour delay, set the parameter as follows:
LOG_ARCHIVE_DEST_2 DELAY=240
The DELAY
attribute indicates that the archived redo logs at the standby site are not available for recovery until the 4-hour time interval has expired. The time interval (expressed in minutes) starts when the archived redo logs are successfully transmitted to the standby site.
The runtime scenario of a standby database with a time lag is slightly different from a standby database with no time lag, because the application of the online redo logs lags behind.
As the DBA, you need to keep the following tasks in mind when managing a standby database with a time lag:
STANDBY_FILE_MANAGEMENT
initialization parameter in the primary database initialization parameter file to ensure that when a datafile is created on the primary site, it is also created on the standby site. Section 6.3.1.1 shows the step-by-step procedure for automatically creating a datafile in the standby database.
% mv /fs2/oracle/stdby_log/*.arc /fs2/oracle/standby/
SQL> RECOVER AUTOMATIC STANDBY DATABASE;
This way, the standby database control file will be updated, but the lag disappears. You must wait for the specified time lag (for example, 4 hours) to reinforce the lag.
NOLOGGING
clause is specified at the primary site, you must apply all of the archived redo logs first. (If you can afford to lose the changes incurred by specifying NOLOGGING
at the primary site, you can choose to do nothing.)
% mv /fs2/oracle/stdby_log/*.arc /fs2/oracle/standby/
SQL> RECOVER AUTOMATIC STANDBY DATABASE;
This way, the standby database control file will be updated, but the lag disappears. You need to wait for the specified time lag (for example, 4 hours) to reinforce the lag.
If this standby site is mandatory, you do not need to do anything at the standby site. If the standby site is optional, you can resolve the gaps immediately by performing the following steps:
% mv /fs2/oracle/stdby_log/*.arc /fs2/oracle/standby/
SQL> RECOVER AUTOMATIC STANDBY DATABASE;
This way, the standby database control file will be updated, but the lag disappears. You need to wait for the specified time lag (for example, 4 hours) to reinforce the lag.
In the case stated at the beginning of this scenario, you may want to take advantage of the time lag, and get a primary database whose status is a specified time (for example, 4 hours) before the current primary database. You can activate the appropriate time-lagged standby database as follows:
SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP PFILE=FailOver.ora;
If you do not want to take advantage of the time lag, you can activate the standby database as a normal standby database with no time lag as follows:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY;
SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP PFILE=FailOver.ora;
This scenario describes using a physical standby database to back up the primary database. This allows the standby site to offload the task of backup at the primary site. The backup at the standby site can be done while the standby database is in the managed recovery mode. When the primary database needs to be restored, you can use the backup created at the standby site.
The standby database can be used to back up the datafiles and the archived redo logs. The primary control file must be backed up at the primary site because the standby database uses a different standby control file. Because the control file is usually much smaller than the datafiles, backing up the control file at the primary site does not significantly affect the performance of the primary database.
To back up a database at the standby site:
NOCATALOG
option to back up the standby datafiles and log files. Assume that stby
is the connect string of the standby database. For example:
% rman target sys/change_on_install@stby nocatalog connected to target database: ADE3 (DBID=1417165739) using target database controlfile instead of recovery catalog
RMAN> run { 2> allocate channel c1 type disk; 3> backup database; 4> } allocated channel: c1 channel c1: sid=13 devtype=DISK Starting backup at 07-NOV-00 channel c1: starting full datafile backupset channel c1: specifying datafile(s) in backupset input datafile fno=00001 name=/oracle/dbs/s3t_db1.dbf channel c1: starting piece 1 at 07-NOV-00 channel c1: finished piece 1 at 07-NOV-00 piece handle=/oracle/dbs/04c9t2ki_1_1 comment=NONE channel c1: backup set complete, elapsed time: 00:00:35 Finished backup at 07-NOV-00 Starting Control File Autobackup at 07-NOV-00 warning - controlfile is not current, controlfile autobackup skipped Finished Control File Autobackup at 07-NOV-00 released channel: c1
After you back up a database, Oracle Corporation recommends that you record the current sequence number and thread number applied at the standby site. This is important because the archived logs, after this backup, may be needed to restore the database at the primary site if the primary database loses its local archived logs.
For example, suppose we have only one thread and the sequence number at the time of database backup is 15. After several new logs are archived to the standby site, you can back up these archived logs using the following commands:
RMAN> run { 2> allocate channel c1 type disk; 3> backup archivelog from logseq 15; 4> } allocated channel: c1 channel c1: sid=9 devtype=DISK Starting backup at 07-NOV-00 channel c1: starting archive log backupset channel c1: specifying archive log(s) in backup set input archive log thread=1 sequence=15 recid=15 stamp=413043150 input archive log thread=1 sequence=16 recid=16 stamp=413043168 input archive log thread=1 sequence=17 recid=17 stamp=413043433 input archive log thread=1 sequence=18 recid=18 stamp=413043442 input archive log thread=1 sequence=19 recid=19 stamp=413043450 input archive log thread=1 sequence=20 recid=20 stamp=413043454 channel c1: starting piece 1 at 07-NOV-00 channel c1: finished piece 1 at 07-NOV-00 piece handle=/oracle/dbs/05c9t3c9_1_1 comment=NONE channel c1: backup set complete, elapsed time: 00:00:03 Finished backup at 07-NOV-00 Starting Control File Autobackup at 07-NOV-00 warning - controlfile is not current, controlfile autobackup skipped Finished Control File Autobackup at 07-NOV-00 released channel: c1
list
command:
RMAN> list backup; List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 4 Full 104M DISK 00:00:29 07-NOV-00 BP Key: 4 Status: AVAILABLE Tag: Piece Name: /oracle/dbs/04c9t2ki_1_1 List of Datafiles in backup set 4 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 1 Full 73158 07-NOV-00 /oracle/dbs/s3t_db1.dbf BS Key Device Type Elapsed Time Completion Time ------- ----------- ------------ --------------- 5 DISK 00:00:02 07-NOV-00 BP Key: 5 Status: AVAILABLE Tag: Piece Name: /oracle/dbs/05c9t3c9_1_1 List of Archived Logs in backup set 5 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 15 73139 07-NOV-00 73156 07-NOV-00 1 16 73156 07-NOV-00 73158 07-NOV-00 1 17 73158 07-NOV-00 73163 07-NOV-00 1 18 73163 07-NOV-00 73165 07-NOV-00 1 19 73165 07-NOV-00 73166 07-NOV-00 1 20 73166 07-NOV-00 73167 07-NOV-00
BACKUP TABLESPACE
and BACKUP DATAFILE
commands to back up individual tablespaces and datafiles.
To restore the backup at the primary site, take the following steps:
Assume that prmy
is the connect string of the primary database, and rcat
is the connect string of the recovery catalog database.
% rman target sys/change_on_install@prmy catalog rman/rman@rcat connected to target database: ADE3 (DBID=1417165739) connected to recovery catalog database RMAN> resync catalog from controlfilecopy 'scf3.ctl';
RMAN> list backup; starting full resync of recovery catalog full resync complete List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 182 Full 104M DISK 00:00:29 07-NOV-00 BP Key: 184 Status: AVAILABLE Tag: Piece Name: /oracle/dbs/04c9t2ki_1_1 List of Datafiles in backup set 182 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 1 Full 73158 07-NOV-00 /oracle/dbs/t_db1.dbf BS Key Device Type Elapsed Time Completion Time ------- ----------- ------------ --------------- 183 DISK 00:00:02 07-NOV-00 BP Key: 185 Status: AVAILABLE Tag: Piece Name: /oracle/dbs/05c9t3c9_1_1 List of Archived Logs in backup set 183 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 15 73139 07-NOV-00 73156 07-NOV-00 1 16 73156 07-NOV-00 73158 07-NOV-00 1 17 73158 07-NOV-00 73163 07-NOV-00 1 18 73163 07-NOV-00 73165 07-NOV-00 1 19 73165 07-NOV-00 73166 07-NOV-00 1 20 73166 07-NOV-00 73167 07-NOV-00
RMAN> run { 2> allocate channel c1 type disk; 3> restore database; 4> } allocated channel: c1 channel c1: sid=11 devtype=DISK Starting restore at 07-NOV-00 channel c1: starting datafile backupset restore channel c1: specifying datafile(s) to restore from backup set restoring datafile 00001 to /oracle/dbs/t_db1.dbf channel c1: restored backup piece 1 piece handle=/oracle/dbs/04c9t2ki_1_1 tag=null params=NULL channel c1: restore complete Finished restore at 07-NOV-00 released channel: c1
After restoring the database, you can try to open the database:
SQL> ALTER DATABASE OPEN; alter database open * ERROR at line 1: ORA-01113: file 1 needs media recovery ORA-01110: data file 1: '/oracle/dbs/t_db1.dbf'
The ORA-01113 error indicates that media recovery is required on the database before you can open it. Issue the manual recovery statement, RECOVER DATABASE
, as follows:
SQL> RECOVER DATABASE; ORA-00279: change 73158 generated at 11/07/2000 14:12:47 needed for thread 1 ORA-00289: suggestion : /oracle/dbs/db11_17.dbf ORA-00280: change 73158 for thread 1 is in sequence #17 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
The resulting error messages indicate that the database needs log sequence #17 for recovery. You can provide logs in several ways:
RMAN> run { 2> allocate channel c1 type disk; 3> restore archivelog from logseq 15; 4> } allocated channel: c1 channel c1: sid=11 devtype=DISK Starting restore at 07-NOV-00 channel c1: starting archive log restore to default destination channel c1: restoring archive log archive log thread=1 sequence=15 channel c1: restoring archive log archive log thread=1 sequence=16 channel c1: restoring archive log archive log thread=1 sequence=17 channel c1: restoring archive log archive log thread=1 sequence=18 channel c1: restoring archive log archive log thread=1 sequence=19 channel c1: restoring archive log archive log thread=1 sequence=20 channel c1: restored backup piece 1 piece handle=/oracle/dbs/05c9t3c9_1_1 tag=null params=NULL channel c1: restore complete Finished restore at 07-NOV-00 released channel: c1
After restoring the archived logs, you can recover the database and then open it.
|
![]() Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|