Release 3 (8.1.7)
Part Number A85455-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8i Documentation Addendum Release 3 (8.1.7) Part Number A85455-01 |
|
This chapter describes changes to the 8.1.6 Oracle8i Standby Database Concepts and Administration manual. This chapter contains the following sections:
The 8.1.6 Oracle8i Standby Database Concepts and Administration manual documents a script for identifying the logs in a gap sequence. This script, which is referenced throughout the manual, has been updated for release 8.1.7. The updated script, shown in Figure 12-1, supersedes the previous version of the script. The updated script handles the case where one or more of the datafiles are offline. The previous version of the script gave incorrect results when one or more of the datafiles were offline.
SELECT high.thread#, "LowGap#", "HighGap#" FROM (SELECT thread#, MIN(sequence#)-1 "HighGap#" FROM (SELECT a.thread#, a.sequence# FROM (SELECT * FROM V$ARCHIVED_LOG) a, (SELECT thread#, MAX(next_change#) gap1 FROM V$LOG_HISTORY GROUP BY thread# ) b WHERE a.thread# = b.thread# AND a.next_change# > gap1 ) GROUP BY thread# ) high, (SELECT thread#, MIN(gap2) "LowGap#" FROM (SELECT thread#, sequence#+1 gap2 FROM V$LOG_HISTORY, V$DATAFILE WHERE checkpoint_change# <= next_change# AND checkpoint_change# >= first_change# AND enabled = 'READ WRITE' ) GROUP BY thread# ) low WHERE low.thread# = high.thread# AND "LowGap#" < "HighGap#";
Chapter 1 in the 8.1.6 Oracle8i Standby Database Concepts and Administration manual has a section titled "Compatibility and Operational Requirements." The following section supersedes the section in the 8.1.6 documentation.
Note the following requirements for maintaining a standby database:
Chapter 2 in the 8.1.6 Oracle8i Standby Database Concepts and Administration manual has a section titled, "Enabling Changes to the Initialization Parameter Settings." The following section supersedes the section in the 8.1.6 documentation.
If you configured the primary initialization parameter file to archive to the standby site, you should enable these new parameter settings after starting the standby instance and the listener on the standby site.
You can make changes to the LOG_ARCHIVE_DEST parameters in the primary database initialization parameter file while the database is open, but the changes only take effect when the instance is restarted. If the database is open and you want to avoid restarting it, enable the parameter changes dynamically using ALTER SYSTEM statements.
For example, assume that you made the following changes to the initialization parameter file while the database was open:
LOG_ARCHIVE_DEST_1="LOCATION=/arc_dest/ MANDATORY REOPEN=2"; LOG_ARCHIVE_DEST_2="SERVICE=stby1 MANDATORY REOPEN=2"; LOG_ARCHIVE_DEST_STATE_1=ENABLE; LOG_ARCHIVE_DEST_STATE_2=ENABLE; LOG_ARCHIVE_MIN_SUCCEED_DEST=2;
You can then connect to the primary database using SQL*Plus and issue ALTER SYSTEM statements as follows to enable these settings:
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1="LOCATION=/arc_dest/ MANDATORY REOPEN=2"; ALTER SYSTEM SET LOG_ARCHIVE_DEST_2="SERVICE=stby1 MANDATORY REOPEN=2"; ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE; ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE; ALTER SYSTEM SET LOG_ARCHIVE_MIN_SUCCEED_DEST=2;
Once you have enabled these changes, the primary database can start attempting to archive redo logs to a standby site. Once an archiving attempt has succeeded, archived redo logs continue to transfer from the primary site to the standby site unless the standby instance is shut down, the data communication link goes down, or a destination is manually disabled.
Chapter 3 in the 8.1.6 Oracle8i Standby Database Concepts and Administration manual has a section titled, "Receiving Archived Redo Logs While in Read-Only Mode." The following section supersedes the section in the 8.1.6 documentation.
While the standby database is in read-only mode, the site can still receive archived redo logs from the primary site. Nevertheless, Oracle does not apply these logs automatically, as in managed recovery. Consequently, a read-only standby database is not synchronized with the primary database at the archive level. You should not activate the standby database in a failover situation unless all archived redo logs have been applied.
| See
Also:
Enabling Changes to the Initialization Parameter Settings of the 8.1.6 Oracle8i Standby Database Concepts and Administration manual for examples of initialization parameter settings you need to define to automatically archive from the primary site to the standby site |
Chapter 2 in the 8.1.6 Oracle8i Standby Database Concepts and Administration manual has a section titled, "Creating the Standby Database Files." The following section supersedes the section in the 8.1.6 documentation.
You can create a standby database on the same host as your primary database or on a separate host. If you create your standby database on the same host, follow the creation procedure carefully when creating the standby database files so that you do not overwrite files on the primary database.
The creation of the standby database files occurs in three stages:
First, make backups of your primary database datafiles. You create the standby datafiles from these backups.
You can use any backup of the primary database so long as you have archived redo logs to completely recover the database. The backup can be old or new, consistent or inconsistent. Hot backups have the advantage of allowing you to keep the database open while performing the backup. Nevertheless, you may prefer to make a new closed, consistent backup to prevent the application of a large number of archived redo logs.
SQL> SELECT name FROM v$datafile; NAME ---------------------------------------------------------------------------- /oracle/dbs/tbs_01.f /oracle/dbs/tbs_02.f /oracle/dbs/tbs_03.f /oracle/dbs2/tbs_11.f /oracle/dbs2/tbs_12.f /oracle/dbs3/tbs_21.f /oracle/dbs3/tbs_22.f 7 rows selected.
SQL> SHUTDOWN IMMEDIATE;
/backup temporary directory, enter:
% cp /oracle/dbs/*.f /backup % cp /oracle/dbs2/*.f /backup % cp /oracle/dbs3/*.f /backup
SQL> STARTUP MOUNT pfile=initPROD1.ora;
After you have created the backups that will be used as the standby datafiles, you can create the standby database control file. The control file must have been created at a time later than the latest timestamp for the backup datafiles.
oracle/dbs/stbycf.f on the primary site, enter the following:
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/oracle/dbs/stbycf.f';
Note that the filename for the created standby control file must be different from the filename of the current control file of the primary database.
SQL> CONNECT sys/change_on_install@prod1 AS SYSDBA SQL> ARCHIVE LOG LIST Database log mode Archive Mode Automatic archival Enabled Archive destination /vobs/oracle/work/arc_dest/arc Oldest online log sequence 821 Next log sequence to archive 822 Current log sequence 822
If the output from the ARCHIVE LOG LIST statement displays "No Archive Mode," set the log archive mode as follows:
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
After you have successfully created the standby datafiles and control file, transfer the files to the standby site using an operating system utility. For example, if the standby site and primary site are on the same host, you can use the UNIX cp command to transfer files; if they are on separate hosts, you can use ftp.
| If the standby database is on | Then you |
|---|---|
|
A separate host with the same directory structure as the primary database |
Can use the same path names for the standby files as the primary files. In this way, you do not have to rename the primary datafiles in the standby control file. |
|
The same host as the primary database, or the standby database is on a separate host with a different directory structure |
Must rename the primary datafiles in the standby control file after copying them to the standby site. You can:
|
Transfer the created control file and datafile backups to the standby site using operating system commands or utilities. Use an appropriate method for transferring binary files.
% cp /backup/db.cf /standby/oracle/dbs/db.cf
% cp /backup/*.df /standby/oracle/dbs
% cp /arc_dest/*.arc /standby/arc_dest
Chapter 5 in the 8.1.6 Oracle8i Standby Database Concepts and Administration manual has a section titled, "Scenario 1: Creating a Standby Database on the Same Host." The following section supersedes the section in the 8.1.6 documentation.
This scenario describes the creation of a standby database STANDBY1 on the same host as the primary database PROD1. The host is a UNIX machine 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-host standby database in managed recovery mode, you can keep it continuously up-to-date.
After you set up the standby database on the local host, you plan to create a standby database on a remote host for total disaster protection. In this way, even if all disks of the primary database crash or are destroyed in a disaster, you can fail over to the remote standby database and keep the database open.
Because the host 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 machine as the primary database, you must set the following parameters in the standby database initialization parameter file:
Fortunately, most (but not all) of the primary database datafiles and redo log files are in the same directory and are named consistently. You will have to rename some of the files manually using ALTER DATABASE statements.
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 must go 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.f /fs1/dbs/tbs_02.f /fs1/dbs/tbs_11.f /fs1/dbs/tbs_12.f /fs1/dbs/tbs_21.f /fs1/dbs/tbs_22.f /fs1/dbs/tbs_13.f /fs1/dbs/tbs_23.f /fs1/dbs/tbs_24.f /fs1/dbs/tbs_31.f /fs1/dbs/tbs_32.f /fs1/dbs/tbs_41.f /fs1/dbs2/tbs_42.f /fs1/dbs2/tbs_51.f /fs1/dbs2/tbs_52.f /fs1/dbs2/tbs_03.f /fs1/dbs3/tbs_14.f /fs1/dbs3/tbs_25.f /fs1/dbs3/tbs_33.f /fs1/dbs3/tbs_43.f /fs1/dbs3/tbs_53.f 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 start and mount the database without opening it as follows:
SQL> STARTUP MOUNT PFILE=initPROD1.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.f';
SQL> ARCHIVE LOG LIST
If the output from the ARCHIVE LOG LIST statement displays "No Archive Mode," set the log archive mode as follows:
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
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 transferring of datafiles can take a long time, you first copy the control file, begin copying 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.f /fs2/dbs/cf1.f % cp /fs1/temp/tbs* /fs2/dbs
In order to run a standby database in a managed standby environment, you must configure a Net8 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 host. Because you do not maintain an Oracle Names server, you must create both a tnsnames.ora entry for the primary database and a listener.ora entry for the standby database.
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 PROD1 database:
prod1 = (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(PORT=1521)(HOST=dlsun183)) (CONNECT_DATA=(SID=prod1)) )
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=PROD1)(ORACLE_HOME=/fs3/oracle)) )
Currently, the listener is listening on port 1521 of the host dlsun183 for database PROD1.
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=/vobs/fs3/oracle)))
Now that you have edited the listener.ora file, you must start the listener:
% lsnrctl LSNRCTL for Solaris: Version 8.1.5.0.0 - Production on 05-APR-99 11:39:41 (c) Copyright 1998 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 Net8 Assistant graphical user interface to configure the network files. For additional information, see the Net8 Administrator's Guide.
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 parameter file appears as follows:
db_name=prod1 control_files=(/fs1/dbs/cf1.f,/fs1/dbs/cf2.f) compatible=8.1.6 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
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/initPROD1.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 11-1 play a key role in the standby database recovery process, and decide to edit them.
Edit the standby database parameter file as follows (with edited values in bold):
db_name = prod1 #The same as PRMYinit.ora control_files = (/fs2/dbs/cf1.f) compatible = 8.1.6 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') log_file_name_convert = ('/fs1/dbs','/fs2/dbs') lock_name_space = 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.
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;
Next, write a SQL script to rename the datafiles not captured by the DB_FILE_NAME_CONVERT parameter:
ALTER DATABASE RENAME FILE /fs1/dbs2/tbs_42.f, /fs1/dbs2/tbs_51.f, /fs1/dbs2/tbs_52.f, /fs1/dbs2/tbs_03.f, /fs1/dbs3/tbs_14.f, /fs1/dbs3/tbs_25.f, /fs1/dbs3/tbs_33.f, /fs1/dbs3/tbs_43.f, /fs1/dbs3/tbs_53.f, TO /fs2/dbs/tbs_42.f, /fs2/dbs/tbs_51.f, /fs2/dbs/tbs_52.f, /fs2/dbs/tbs_03.f, /fs2/dbs/tbs_14.f, /fs2/dbs/tbs_25.f, /fs2/dbs/tbs_33.f, /fs2/dbs/tbs_43.f, /fs2/dbs/tbs_53.f /
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@prod1 as sysdba SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2 = 'SERVICE=standby1 OPTIONAL REOPEN=180'; SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2 = ENABLE;
Because you have enabled the changes to the primary database parameter file, the primary database is now able to archive to the standby service name. Before you can perform managed recovery, however, you must synchronize the standby database by applying those logs containing changes made after the primary database backup, but before the first log received by the standby database.
Write the following SQL script and run it on the standby database:
SELECT high.thread#, "LowGap#", "HighGap#" FROM (SELECT thread#, MIN(sequence#)-1 "HighGap#" FROM (SELECT a.thread#, a.sequence# FROM (SELECT * FROM V$ARCHIVED_LOG) a, (SELECT thread#, MAX(next_change#) gap1 FROM V$LOG_HISTORY GROUP BY thread# ) b WHERE a.thread# = b.thread# AND a.next_change# > gap1 ) GROUP BY thread# ) high, (SELECT thread#, MIN(gap2) "LowGap#" FROM (SELECT thread#, sequence#+1 gap2 FROM V$LOG_HISTORY, V$DATAFILE WHERE checkpoint_change# <= next_change# AND checkpoint_change# >= first_change# AND enabled = 'READ WRITE' ) GROUP BY thread# ) low WHERE low.thread# = high.thread# AND "LowGap#" < "HighGap#";
The output of the query is as follows:
SQL> @gap THREAD# LowGap# HighGap# ---------- ---------- ---------- 1 250 252
Hence, you must apply log sequences 250, 251, and 252 to synchronize the standby database before initiating managed recovery.
The archived log filenames generated by gap sequence queries on the standby database are generated by the LOG_ARCHIVE_DEST_1 and LOG_ARCHIVE_FORMAT parameters in the initialization parameter file. Before transmitting the logs from the primary site to the standby site, you must determine the correct filenames.
First, you determine the filenames of the logs in the gap that were archived by the primary database. After connecting to the primary database using SQL*Plus, issue the following SQL query to obtain the names:
SQL> CONNECT sys/change_on_install@prod1 AS SYSDBA SQL> SELECT name FROM v$archived_log WHERE sequence# IN (250,251,252); NAME -------------------------------------------------------------------------------- /fs1/arc_dest/log_1_250.arc /fs1/arc_dest/log_1_251.arc /fs1/arc_dest/log_1_252.arc
The gap sequence in this case consists of log_1_250.arc, log_1_251.arc, and log_1_252.arc. The settings for LOG_ARCHIVE_DEST_1 and LOG_ARCHIVE_FORMAT in the standby database parameter file are as follows:
LOG_ARCHIVE_DEST_1 = 'LOCATION=/fs2/arc_dest/' LOG_ARCHIVE_FORMAT = log_%t_%s.arc
You move the gap sequence logs from the primary file system to the standby file system, renaming them according to values for the LOG_ARCHIVE_DEST_1 and LOG_ARCHIVE_FORMAT initialization parameters at the standby site:
% cp /fs1/arc_dest/log_1_250.arc /fs2/arc_dest/log_1_250.arc % cp /fs1/arc_dest/log_1_251.arc /fs2/arc_dest/log_1_251.arc % cp /fs1/arc_dest/log_1_252.arc /fs2/arc_dest/log_1_252.arc
Now you can apply the gap sequence logs using the RECOVER AUTOMATIC STANDBY DATABASE statement. This statement uses the values of the LOG_ARCHIVE_DEST_1 and LOG_ARCHIVE_FORMAT parameters to construct the target filename. Once the gap sequence logs have been applied, the standby database is synchronized with the primary database and can be placed in managed recovery mode.
While connected to the standby database in SQL*Plus, recover the database using the AUTOMATIC option:
SQL> RECOVER AUTOMATIC STANDBY DATABASE; ORA-00279: change 35083 generated at 08/16/1999 14:08:37 needed for thread 1 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} CANCEL Media recovery cancelled. SQL>
After recovering the gap sequence logs, Oracle prompts you for the name of a log that does not exist. The reason is that the recovery process does not know about the logs archived to the STANDBY service by the primary database. Cancel recovery at this point by entering CANCEL at the prompt.
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 so that Oracle waits the specified number of minutes to 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;
Oracle now begins managed recovery. As the primary database archives redo logs to the standby site, the standby database automatically applies them.
Chapter 5 in the 8.1.6 Oracle8i Standby Database Concepts and Administration manual has a section titled, "Scenario 2: Creating a Standby Database on a Remote Host." The following section supersedes the section in the 8.1.6 documentation.
This scenario describes the creation of a standby database STANDBY1 on a remote host. The following assumptions are being made:
Create the backup that will form the basis for the standby database.
SQL> SELECT name FROM v$datafile; NAME ----------------------------------------------------------------------- /vobs/oracle/dbs/dbf_1.f 1 row selected.
SQL> SHUTDOWN IMMEDIATE;
% cp /vobs/oracle/dbs/dbf_1.f /backup
SQL> STARTUP MOUNT PFILE=PRMYinit.ora;
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/backup/stbycf.f'
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.
SQL> ARCHIVE LOG LIST
If the output from the ARCHIVE LOG LIST statement displays "No Archive Mode," set the log archive mode as follows:
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
Copy 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. This step involves editing the following parameter files:
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)))
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. Note that you have the option of creating a new listener or adding a new address to an existing listener.
The two entries that you added in step 4 define a listener to listen for connections to standby database stdby1 on port 1521. In this step, you must start the stdby1_listener listener. For example:
% lsnrctl start stdby1_listener;
% rcp /vobs/oracle/dbs/PRMYinit.ora STBYHOST:/fs2/oracle/stdby/STBYinit.ora
The STBYinit.ora file appears as follows:
# #parameter file STBYinit.ora # db_name=prod1 # The same as PRMYinit.ora # The following parameter has changed from PRMYinit.ora control_files=/fs2/oracle/stdby/stbycf.f # 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=/vobs/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=('/vobs/oracle/dbs','/fs2/oracle/stdby') log_file_name_convert=('/vobs/oracle/dbs','/fs2/oracle/stdby') log_archive_start=FALSE log_archive_trace=127
% 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 step 4 for information on how to configure the tnsnames.ora file.
Start the standby database to enable archiving.
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;
LOG_ARCHIVE_DEST_2 = 'SERVICE=standby1 MANDATORY REOPEN=60'
LOG_ARCHIVE_DEST_STATE_2 = ENABLE
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=standby1 MANDATORY REOPEN=60'; SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
SELECT high.thread#, "LowGap#", "HighGap#" FROM (SELECT thread#, MIN(sequence#)-1 "HighGap#" FROM (SELECT a.thread#, a.sequence# FROM (SELECT * FROM V$ARCHIVED_LOG) a, (SELECT thread#, MAX(next_change#) gap1 FROM V$LOG_HISTORY GROUP BY thread# ) b WHERE a.thread# = b.thread# AND a.next_change# > gap1 ) GROUP BY thread# ) high, (SELECT thread#, MIN(gap2) "LowGap#" FROM (SELECT thread#, sequence#+1 gap2 FROM V$LOG_HISTORY, V$DATAFILE WHERE checkpoint_change# <= next_change# AND checkpoint_change# >= first_change# AND enabled = 'READ WRITE' ) GROUP BY thread# ) low WHERE low.thread# = high.thread# AND "LowGap#" < "HighGap#"; THREAD# LowGap# HighGap# ---------- ---------- ---------- 1 90 92
SELECT name FROM v$archived_log WHERE thread#=1 AND sequence#<=92 AND sequence#>=90; NAME ----------------------------------------- /vobs/oracle/dbs/r_1_90.arc /vobs/oracle/dbs/r_1_91.arc /vobs/oracle/dbs/r_1_92.arc
% rcp /vobs/oracle/dbs/r_1_90.arc STBYHOST:/fs2/oracle/stdby/stdby_1_90.arc % rcp /vobs/oracle/dbs/r_1_91.arc STBYHOST:/fs2/oracle/stdby/stdby_1_91.arc % rcp /vobs/oracle/dbs/r_1_92.arc STBYHOST:/fs2/oracle/stdby/stdby_1_92.arc
SQL> RECOVER AUTOMATIC STANDBY DATABASE;
On the standby database, enable managed recovery by issuing the following SQL statement:
SQL> RECOVER MANAGED STANDBY DATABASE;
|
 Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
|