This section describes the setup of a symmetrical physical standby database environment.
Configuration Steps
The following configuration steps will be covered to setup the physical standby database:
Software Installation
Oracle Networking Preparation
Primary Database Preparation
Flashback Recovery Area
Standby Database Creation
Enable Standby Recovery
Software Installation
Oracle Software Directory Setup
Since this environment is being created as a symmetrical installation, all directories should mirror the primary database directories.
The following is the basic directory structure:
ORACLE_BASE = /u01/app/oracle
ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1
Admin directories:
/u01/app/oracle/admin/DBSID/adump
/bdump
/cdump
/pfile
/udump
Database Backup
A new level 0 backup should be taken of the primary database with a standby backup control file.
Database Directories
Data file directories:
/u01/oradata/DBSID
Control file directories:
/u01/oradata/DBSID
Redo log directories:
/u01/oradata/DBSID
Standby archive destination:
/u01/app/oracle/orabkup/DBSID_DG
Oracle Networking Preparation
On the primary and standby database installation there should be network entries that specify how to connect to each database. These are used to transfer archivelog files to the standby database (whichever machine that may be) and to connect to the databases themselves. There should also be explicit entries in the listener.ora file to register the database with the listener and optionally a network entry to specify SQL*Net access to the standby database.
The following examples create a symmetrical environment so that switching back and forth between primary and standby database is transparent to the networking files.
Primary Database
listener.ora File
On the primary database the following entries should be added to the listener.ora file:
# LISTENER.ORA Network Configuration File:
# /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg-1.appsdba.com)(PORT = 1521))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = DBSID.dg.appsdba.com)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(SID_NAME = DBSID)
)
)
tnsnames.ora File
The following example shows the primary and standby database entries in the tnsnames.ora file:
DBSID_DG1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg-1.appsdba.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DBSID.dg.appsdba.com)
)
)
DBSID_DG2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg-2.appsdba.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DBSID.dg.appsdba.com)
)
)
DBSID =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = DBSID))
(ADDRESS = (PROTOCOL = TCP)(HOST = dg-1.appsdba.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DBSID.dg.appsdba.com)
)
)
Standby Database
listener.ora File
On the standby database the following entries should be added to the listener.ora file:
# LISTENER.ORA Network Configuration File:
# /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg-2.appsdba.com)(PORT = 1521))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = DBSID.dg.appsdba.com)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(SID_NAME = DBSID)
)
)
tnsnames.ora File
The following example shows the standby database entry in the standby server's tnsnames.ora file:
DBSID_DG1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg-1.appsdba.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DBSID.dg.appsdba.com)
)
)
DBSID_DG2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg-2.appsdba.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DBSID.dg.appsdba.com)
)
)
DBSID =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = DBSID))
(ADDRESS = (PROTOCOL = TCP)(HOST = dg-2.appsdba.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DBSID.dg.appsdba.com)
)
)
Primary Database Preparation
Enable Forced Logging
Place the primary database in FORCE LOGGING mode after database creation using the following SQL statement:
SQL> ALTER DATABASE FORCE LOGGING;
This prevents the use of the "nologging" feature, which would not be replicated in the redo logs, and therefore applied on the standby database.
Create A Password File
This is required to enable RMAN to create the standby database. After creating the password file with the orapwd utility, the following init.ora parameter must be set:
remote_login_passwordfile='EXCLUSIVE'
Standby Redo Log Files
Standby redo log files are required for higher protection levels where LGWR is used to write redo records. There is no downside to creating them for both the primary and standby and this allows a faster switchover back to the primary. They must be created as the same size, number of groups and members on both the primary and the standby.
We recommend a naming convention of srl[mm][n].rdo, where "srl" is standby redo log, [mm] is 01,02,… for group, and [n] is a,b,c,… for each member.
SQL> show parameter standby_file_management
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string auto
SQL> alter system set standby_file_management=manual;
System altered.
SQL> show parameter standby_file_management
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string MANUAL
SQL> alter database add standby logfile '/u01/oradata/DBSID/srl01a.rdo' size 100m;
Database altered.
SQL> alter database add standby logfile '/u01/oradata/DBSID/srl02a.rdo' size 100m;
Database altered.
SQL> alter database add standby logfile '/u01/oradata/DBSID/srl03a.rdo' size 100m;
Database altered.
SQL> alter database add standby logfile '/u01/oradata/DBSID/srl04a.rdo' size 100m;
Database altered.
SQL> alter system set standby_file_management=auto;
System altered.
SQL>
Create A Standby Controlfile (Optional)
A standby controlfile from the primary database is required when the standby option is specified:
SQL> alter database create standby controlfile as '
where
If using RMAN then this step can be included as part of the backup.
RMAN Backup With Standby Controlfile
The following option was added to the RMAN "backup database" command to insure that a controlfile was available in standby format for RMAN to create the standby database. This is important to also allow for future re-instantiation of the standby database through RMAN.
CONFIGURE CONTROLFILE AUTOBACKUP ON;
Appended to the backup command:
include current controlfile for standby
Configure Database init.ora Parameters
The following init.ora parameters should be set. These should match the standby database parameters to insure symmetric role transitions:
archive_lag_target=0
db_name = DBSID
db_unique_name = DBSID_DG1
instance_name = DBSID
#
# Standby specific parameters
#
#db_file_name_convert =
#('/u01/oradata/DBSID','/oracle/oradata/DBSID',
#'/u01/oradata/DBSID','/oracle/oradata/DBSID',
#'/u01/oradata/DBSID','/oracle/oradata/DBSID')
#
#log_file_name_convert =
#('/u01/oradata/DBSID','/oracle/oradata/DBSID',
#'/u01/oradata/DBSID','/oracle/oradata/DBSID',
#'/u01/oradata/DBSID','/oracle/oradata/DBSID')
#
standby_archive_dest = '/u01/app/oracle/orabkup/DBSID_dg/'
standby_file_management=auto
# Deprecated in 10g
#remote_archive_enable=true
#
# Set to allow log apply services to automatically resolve archive gaps
#
fal_client=DBSID_DG1
fal_server=DBSID_DG2
#
# Set logging Parameters
#
log_archive_config='dg_config=(DBSID_DG1,DBSID_DG2)'
log_archive_dest_1 = 'LOCATION=/u01/app/oracle/admin/DBSID/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DBSID_DG1'
log_archive_dest_2='SERVICE=DBSID_DG2 ARCH SYNC NOAFFIRM delay=0
OPTIONAL max_failure=0 reopen=300 register
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DBSID_DG1'
log_archive_dest_state_1='ENABLE'
log_archive_dest_state_2='DEFER'
log_archive_format = 'DBSID_%t_%s_%r.arc'
log_checkpoint_interval=10000
log_checkpoint_timeout=1800
Note: The standby_archive_dest parameter should match the log_archive_dest_n parameter in 9i and in 10g Data Guard it should not.
log_archive_dest_1 = 'LOCATION=USE_DB_RECOVERY_FILE_DEST valid_for=(ONLINE_LOGFILE,ALL_ROLES)'
log_archive_dest_2 = 'service=DBSID_dg2 LGWR NOAFFIRM delay=0
OPTIONAL max_failure=0 reopen=300 async=61440
db_unique_name="DBSID_DG2" register nonet_timeout valid_for=(online_logfile,primary_role)'
#log_archive_dest_3 = 'LOCATION=USE_RECOVERY_FILE_DEST
# valid_for=(STANDBY_LOGFILE,STANDBY_ROLE)'
standby_archive_dest = USE_DB_RECOVERY_FILE_DEST
Additional 10g init.ora Parameters
Add the “VALID_FOR” parameter to the current log destination:
LOG_ARCHIVE_DEST_1='location=/u01/app/oracle/admin/DBSID/arch/ VALID_FOR=(ONLINE_LOGFILE,ALL_ROLES)'
Add a second log destination for the standby database:
log_archive_dest_2='service=DBSID_DG2 ARCH SYNC NOAFFIRM delay=0
OPTIONAL max_failure=0 reopen=300 register
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=”DBSID_DG2”'
or
log_archive_dest_2='service=DBSID_DG2 LGWR ASYNC NOAFFIRM delay=0
OPTIONAL max_failure=0 reopen=300 register
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=DBSID_DG2'
Add these additional parameters:
DB_UNIQUE_NAME=DBSID_DG1
log_archive_config = "dg_config=(DBSID_DG1,DBSID_DG2)"
log_archive_dest_state_2='ENABLE'
Create An SPFILE For OEM and Data Guard Support
Oracle's Enterprise Manager and Data Guard require an spfile to fully administration of the standby database enviornment. This can be created with the following command:
SQL> create spfile from pfile;
This will create an spfile in the default $ORACLE_HOME/dbs directory and will take affect on the next database startup.
Flashback Recovery Area
Setting init.ora Parameters
When using a flash recovery area with primary/standby databases the following parameters may need to be configured:
#
# Set flash recovery area
#
db_recovery_file_dest='/u01/app/oracle/flashrecovery'
db_recovery_file_dest_size=10G
#
# Set logging parameters
#
log_archive_dest_1 = 'LOCATION=USE_DB_RECOVERY_FILE_DEST
valid_for=(ONLINE_LOGFILE,ALL_ROLES)'
log_archive_dest_state_1='ENABLE'
#
# Set standby parameters
#
standby_archive_dest = 'USE_DB_RECOVERY_FILE_DEST'
NOTE: There seems to be some inconsistency with the way the Data Guard broker deals with these parameters. The log_archive_dest_1 setting requires no comma before the “valid_for” parameter, but the broker will eventually change the setting and add a comma. The broker will also null the standby_archive_dest parameter on the primary database, but will leave the setting in its configuration file.
Data Guard Broker
The “StandbyArchiveLocation” database property will be set by the Data Guard broker (for both databases if creating a symmetrical configuration) to the following:
StandbyArchiveLocation = 'LOCATION=USE_DB_RECOVERY_FILE_DEST'
Note that although the standby_archive_dest parameter does not use the "LOCATION" keyword the Data Guard broker parameter does.
Flashback Database Options – 10g
To set the database into “Flashback Database” mode the following must be enabled:
1. Database must be in archivelog mode
2. Must have a flash recovery area defined.
To enable the flashback database feature:
1. Mount the database:
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
2. Set the flashback retention target:
SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320; # 3 days
3. Enable the Flashback Database feature:
SQL> ALTER DATABASE FLASHBACK ON;
Standby Database Creation
Configure Oracle Server Software
The primary database and the standby database should be running the same version of Oracle software, including patchsets and security patches.
Create Standby Database Environment
Standby Database Password File
A new password file was created for the standby database instance using the orapwd utility.
orapwd file=orapwDBSID password=xxxxxx entries=20
Configure the Standby Database init.ora Parameters
The source database init.ora parameters were copied and the following added or changed:
aq_tm_processes=0
archive_lag_target=0
db_name = DBSID
db_unique_name = DBSID_DG2
instance_name = DBSID
#
control_files = ('/u01/oradata/DBSID/control01.ctl',
'/u02/oradata/DBSID/control02.ctl',
'/u03/oradata/DBSID/control03.ctl')
#
background_dump_dest = '/u01/app/oracle/admin/DBSID/bdump'
core_dump_dest = '/u01/app/oracle/admin/DBSID/cdump'
user_dump_dest = '/u01/app/oracle/admin/DBSID/udump'
#
job_queue_processes=0
#
# Standby specific parameters
#
#db_file_name_convert =
#('/u01/oradata/DBSID','/oracle/oradata/DBSID',
#'/u01/oradata/DBSID','/oracle/oradata/DBSID',
#'/u01/oradata/DBSID','/oracle/oradata/DBSID')
#
#log_file_name_convert =
#('/u01/oradata/DBSID','/oracle/oradata/DBSID',
#'/u01/oradata/DBSID','/oracle/oradata/DBSID',
#'/u01/oradata/DBSID','/oracle/oradata/DBSID')
#
standby_archive_dest = '/u01/app/oracle/orabkup/DBSID_dg/'
standby_file_management=auto
# Deprecated in 10g
#remote_archive_enable=true
#
# Set to allow log apply services to automatically resolve archive gaps
#
fal_client=DBSID_DG2
fal_server=DBSID_DG1
#
# Set logging Parameters
#
log_archive_dest_1 = 'LOCATION=/u01/app/oracle/admin/DBSID/arch/ VALID_FOR(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DBSID_DG2'
log_archive_dest_2='SERVICE=DBSID_DG1 ARCH SYNC NOAFFIRM delay=0
OPTIONAL max_failure=0 reopen=300 register
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DBSID_DG2'
log_archive_dest_state_1='ENABLE'
log_archive_dest_state_2='DEFER'
log_archive_format = 'DBSID_%t_%s_%r.arc'
log_archive_max_processes=2
log_archive_min_succeed_dest=1
log_archive_start=true
log_archive_trace=0
Note: The standby_archive_dest parameter should match the log_archive_dest_n parameter in 9i and in 10g Data Guard it should not.
10g Parameters
Oracle 10g has introduced some new parameters. Specifically:
db_unique_name
Spfile parameters
*.archive_lag_target=0
*.audit_file_dest='/u01/app/oracle/admin/DBSID/adump'
*.background_dump_dest='/u01/app/oracle/admin/DBSID/bdump'
*.compatible='10.1.0.5.0'
*.control_files='/u01/oradata/DBSID/control01.ctl',
'/u01/oradata/DBSID/control02.ctl',
'/u01/oradata/DBSID/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/DBSID/cdump'
*.db_block_size=8192
*.db_domain='dg.appsdba.com'
*.db_file_multiblock_read_count=16
*.db_name='DBSID'
*.db_unique_name='DBSID'
*.dg_broker_start=TRUE
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DBSIDXDB)'
*.fal_client='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)
(HOST=dg-1.appsdba.com)(PORT=1521)))
(CONNECT_DATA=(SERVICE_NAME=DBSID_dg1_XPT.dg.appsdba.com)
(INSTANCE_NAME=DBSID)(SERVER=dedicated)))'
*.fal_server='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)
(HOST=dg-2.appsdba.com)(PORT=1521)))
(CONNECT_DATA=(SERVICE_NAME=DBSID_dg2_XPT.dg.appsdba.com)
(SERVER=dedicated)))'
*.job_queue_processes=0
*.log_archive_config='dg_config=(DBSID_dg1,DBSID_dg2)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/admin/DBSID/arch/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DBSID'
DBSID.log_archive_dest_1='location="/u01/app/oracle/admin/DBSID/arch/"',
'valid_for=(ONLINE_LOGFILE,ALL_ROLES)'
*.log_archive_dest_2='service="(DESCRIPTION=
(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)
(HOST=dg-2.appsdba.com)(PORT=1521)))
(CONNECT_DATA=(SERVICE_NAME=DBSID_DG_XPT.dg.appsdba.com)
(INSTANCE_NAME=DBSID)(SERVER=dedicated)))"',
'LGWR NOAFFIRM delay=0 OPTIONAL max_failure=0 reopen=300 async=61440
db_unique_name="DBSID_DG" register nonet_timeout
valid_for=(online_logfile,primary_role)'
DBSID.log_archive_dest_3=''
*.log_archive_dest_state_2='RESET'
*.log_archive_format='DBSID_%t_%s_%r.arc'
DBSID.log_archive_format='DBSID_%t_%s_%r.arc'
*.log_archive_max_processes=2
*.log_archive_min_succeed_dest=1
DBSID.log_archive_trace=0
*.open_cursors=300
*.pga_aggregate_target=1048576000
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.service_names='DBSID.dg.appsdba.com','DBSID.dg.appsdba.com',
'DBSID_XPT','DBSID_dg1_XPT'
*.sga_target=1572864000
*.standby_archive_dest='/u01/app/oracle/orabkup/DBSID_stby/'
DBSID.standby_archive_dest=''
*.standby_file_management='AUTO'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/DBSID/udump'
Primary Parameters
spfile=/u01/app/oracle/admin/DBSID/pfile/spfileDBSID.ora
db_unique_name=DBSID_dg1
*.fal_client='DBSID_DG1'
*.fal_server='DBSID_DG2'
Standby Parameters
spfile=/u01/app/oracle/admin/DBSID/pfile/spfileDBSID.ora
db_unique_name=DBSID_dg2
*.fal_client='DBSID_DG2'
*.fal_server='DBSID_DG1'
Create spfile
An spfile was created from the init.ora which was used to initially start the standby database. The following will create an spfile from an init.ora file and put it in the default location (i.e. $ORACLE_HOME/dbs):
SQL> create spfile from pfile;
Create the Standby Database with RMAN
The standby database is created using RMAN and the latest online backup and archivelog files from the primary database. The standby creation requires the use of a "standby controlfile" so the primary database backup is made with the additional clause "include current controlfile for standby".
RMAN Primary Database Backup Script
$ORACLE_HOME/bin/rman target / nocatalog <<-EON
set snapshot controlfile name to '$ORACLE_BASE/admin/$ORACLE_SID/pfile/snap_$ORACLE_SID.ctl';
run {
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' FORMAT = '${CFG_BKUP_FILE}.%U';
set command id to 'rman_incr0_tape';
#
$BKUP_CMD
incremental level = 0
filesperset ${CFG_FILESPERSET}
tag incr0_tape_$ORACLE_SID
database
include current controlfile for standby
plus archivelog format '${CFG_ARCH_FILE}.%U'
delete all input;
RMAN Standby Database Creation Script
In order to create the standby database with RMAN the standby instance must be started in nomount mode. RMAN will then create a "duplicate" database for "standby".
#
$ORACLE_HOME/bin/rman nocatalog target sys/xxxxxx@DBNAME log=dupdb.log <<-EOF
connect auxiliary /
#
run {
#
# Note: Set this in the init.ora as well!
#
sql 'ALTER SESSION SET NLS_DATE_FORMAT="DD-MON-YY HH24:MI"';
#set until time = '08-SEP-03 01:00';
#
duplicate target database for standby
nofilenamecheck
dorecover;
}
exit;
EOF
Backup File Availability
Depending on how backups are performed and what the media type is, additional tasks may be involved in making the backup media available for restore on a different server. RMAN will want to access backup files from the same location they were backed up to. In the case of disk this may mean copying the files to a directory that has the same name as the source, or creating a symbolic link. In the case of tape, it may mean insuring that the restore server has permisisons to access the backup tape library and has appropriate environment variables set.
Legato NSR Requirements
For Legato environments, the NSR_CLIENT parameter will need to be set to the primary server’s host name and the standby server will have to be given explicit access permission to the primary server’s backup media in the Networker Administration utility.
Standby Redo Log Files
Create the standby redo log files with the same size and at least the same number of groups (Oracle recommends adding one additional group) and members as the primary online redo log database.
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> show parameter standby_file_management
NAME TYPE VALUE
------------------------------------ ----------- -----------
standby_file_management string auto
SQL> alter system set standby_file_management=manual;
System altered.
SQL> show parameter standby_file_management
NAME TYPE VALUE
------------------------------------ ----------- -----------
standby_file_management string MANUAL
SQL> alter database add standby logfile '/u01/oradata/DBSID/srl01a.rdo' size 100m;
Database altered.
SQL> alter database add standby logfile '/u01/oradata/DBSID/srl02a.rdo' size 100m;
Database altered.
SQL> alter database add standby logfile '/u01/oradata/DBSID/srl03a.rdo' size 100m;
Database altered.
SQL> alter database add standby logfile '/u01/oradata/DBSID/srl04a.rdo' size 100m;
Database altered.
SQL>
Note: If standby log files are already defined (e.g. from the primary database), then they must be dropped and recreated.
SQL> alter database drop standby logfile '/oracle/oradata/DBSID/srl01a.rdo';
Database altered.
SQL> alter database add standby logfile '/oracle/oradata/DBSID/srl01a.rdo' size 500m;
Database altered.
SQL>
Oracle recommends creating one more standby redo log file than there is on the primary database.
SQL> alter system set standby_file_management=auto;
System altered.
SQL> show parameter standby_file_management
NAME TYPE VALUE
------------------------------------ ----------- -----------
standby_file_management string AUTO
SQL>
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
Database altered.
SQL>
To review the newly create standby log files:
SQL> select * from v$standby_log;
GROUP# THREAD# SEQUENCE# BYTES USED ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------
FIRST_CHANGE# FIRST_TIM LAST_CHANGE# LAST_TIME
------------- --------- ------------ ---------
4 0 0 52428800 1024 YES UNASSIGNED
0 0
5 0 0 52428800 1024 YES UNASSIGNED
0 0
6 0 0 52428800 1024 YES UNASSIGNED
0 0
7 0 0 52428800 1024 YES UNASSIGNED
0 0
SQL>
To enable LGWR sending of redo records on the primary database change the definition of log_archive_dest_3:
SQL> l
1* select * from v$archive_dest where dest_id < 4;
DEST_ID
----------
DEST_NAME
---------------------------
STATUS BINDING NAME_SP TARGET ARCHIVER SCHEDULE
--------- --------- ------- ------- ---------- --------
DESTINATION
---------------------------
LOG_SEQUENCE REOPEN_SECS DELAY_MINS NET_TIMEOUT PROCESS REG FAIL_DATE
------------ ----------- ---------- ----------- ---------- --- ---------
FAIL_SEQUENCE FAIL_BLOCK FAILURE_COUNT MAX_FAILURE
------------- ---------- ------------- -----------
ERROR
---------------------------
ALTERNATE
---------------------------
DEPENDENCY
---------------------------
REMOTE_TEMPLATE
---------------------------
QUOTA_SIZE QUOTA_USED MOUNTID TRANSMIT_MOD ASYNC_BLOCKS AFF TYPE
---------- ---------- ---------- ------------ ------------ --- -------
1
LOG_ARCHIVE_DEST_1
VALID OPTIONAL SYSTEM PRIMARY ARCH ACTIVE
/u01/app/oracle/admin/DBSID/arch
4071 300 0 0 ARCH YES
0 0 0 0
NONE
NONE
NONE
0 0 0 SYNCHRONOUS 0 NO PUBLIC
2
LOG_ARCHIVE_DEST_2
VALID OPTIONAL SYSTEM STANDBY ARCH ACTIVE
STBY_PASDB
4071 300 0 0 ARCH YES
0 0 0 0
NONE
NONE
NONE
0 0 0 SYNCHRONOUS 0 NO PUBLIC
3
LOG_ARCHIVE_DEST_3
INACTIVE OPTIONAL SYSTEM PRIMARY ARCH INACTIVE
0 0 0 0 ARCH NO
0 0 0 0
NONE
NONE
NONE
0 0 0 SYNCHRONOUS 0 NO PUBLIC
SQL> alter system set log_archive_dest_3='service=DBSID_DG2
lgwr async noaffirm delay=0 optional max_failure=0 reopen=300
register' scope=both;
System altered.
SQL> alter system set log_archive_dest_state_3='ENABLE' scope=both;
System altered.
SQL> select * from v$archive_dest
2 where dest_id = 3;
DEST_ID
----------
DEST_NAME
---------------------------
STATUS BINDING NAME_SP TARGET ARCHIVER SCHEDULE
--------- --------- ------- ------- ---------- --------
DESTINATION
---------------------------
LOG_SEQUENCE REOPEN_SECS DELAY_MINS NET_TIMEOUT PROCESS REG FAIL_DATE
------------ ----------- ---------- ----------- ---------- --- ---------
FAIL_SEQUENCE FAIL_BLOCK FAILURE_COUNT MAX_FAILURE
------------- ---------- ------------- -----------
ERROR
---------------------------
ALTERNATE
---------------------------
DEPENDENCY
---------------------------
REMOTE_TEMPLATE
---------------------------
QUOTA_SIZE QUOTA_USED MOUNTID TRANSMIT_MOD ASYNC_BLOCKS AFF TYPE
---------- ---------- ---------- ------------ ------------ --- -------
3
LOG_ARCHIVE_DEST_3
VALID OPTIONAL SYSTEM STANDBY LGWR PENDING
DBSID_DG2
0 300 0 0 LGWR YES
0 0 0 0
NONE
NONE
NONE
0 0 0 ASYNCHRONOUS 2048 NO PUBLIC
SQL>
alter system set log_archive_dest_3='service=DBSID_DG2
LGWR ASYNC NOAFFIRM DELAY=0 OPTIONAL max_failure=0
reopen=300 register' scope=both;
alter system set log_archive_dest_state_3='ENABLE' scope=both;
Note that now two definitions have been created. The "log_archive_dest_2" definition defines an archiver path to transfer archived redo log files and the "log_archive_dest_3" definition defines a log writer path. Once the log writer definition is enabled it will become effective at the next log switch. If there is a failure Oracle will fall back to the archiver definition if possible, and Oracle will attempt to use the log writer definition at each log switch.
Tempfile Creation (Optional)
Prior to Oracle 10g Release 2 RMAN will not recreate tempfiles, and any temporary tablespaces defined in the primary database will not have corresponding tempfiles associated with those tablespaces in the standby database. It is possible to “pre-create” the tempfiles since these are needed for sorting when using the “read-only” feature of physical standby databases. The following will detail the procedures to pre-create tempfiles for the standby database. This is then one less thing that has to be done when actually failing over to a standby database.
1. Cancel log apply services and open the physical standby database for read-only access.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE OPEN;
2. Create the appropriate temporary files for the temporary tablespace(s) defined in the database. The easiest way to get these definitions is to use a text based backup controlfile from the primary database. dbcontrol has the ability to create this text based backup controlfile and this is done automatically when using the bkctrl utility on the primary database.
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE –
'/u01/oradata/DBSID/temp01.dbf' SIZE 2000M;
SQL> ALTER TABLESPACE TEMP2 ADD TEMPFILE –
'/u01/oradata/DBSID/temp02.dbf' size 2000M;
3. Shutdown the database and restart in managed recovery mode.
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP NOMOUNT;
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
Enable Standby Recovery
Enable Redo Log Shipping
SQL> alter system set LOG_ARCHIVE_DEST_state_2=enable scope=both;
System altered.
SQL> alter system switch logfile;
System altered.
Verify Archive Destination
SQL> select * from v$archive_dest where dest_id=2;
DEST_ID
----------
DEST_NAME
---------------------------
STATUS BINDING NAME_SP TARGET ARCHIVER SCHEDULE
--------- --------- ------- ------- ---------- --------
DESTINATION
---------------------------
LOG_SEQUENCE REOPEN_SECS DELAY_MINS MAX_CONNECTIONS NET_TIMEOUT PROCESS
REG FAIL_DATE FAIL_SEQUENCE FAIL_BLOCK FAILURE_COUNT MAX_FAILURE
------------ ----------- ---------- --------------- ----------- ----------
--- --------------- ------------- ---------- ------------- -----------
ERROR
---------------------------
ALTERNATE
---------------------------
DEPENDENCY
---------------------------
REMOTE_TEMPLATE
---------------------------
QUOTA_SIZE QUOTA_USED MOUNTID TRANSMIT_MOD ASYNC_BLOCKS AFF TYPE
VALID_NOW VALID_TYPE VALID_ROLE DB_UNIQUE_NAME
---------- ---------- ---------- ------------ ------------ --- -------
---------------- --------------- ------------ ------------------------------
VER
---
2
LOG_ARCHIVE_DEST_2
VALID OPTIONAL SYSTEM STANDBY LGWR ACTIVE
DBSID_DG2
7 300 0 1 180 LGWR
YES 0 0 0 0
NONE
NONE
NONE
0 0 0 ASYNCHRONOUS 61440 NO PUBLIC
YES ONLINE_LOGFILE PRIMARY_ROLE DBSID_DG2
NO
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIME NEXT_TIME
---------- --------------- ---------------
1 19-APR-07 20-APR-07
2 20-APR-07 20-APR-07
3 20-APR-07 20-APR-07
4 20-APR-07 20-APR-07
Verifying Apply on Standby
SQL> l
1* SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#
SQL> /
SEQUENCE# FIRST_TIME NEXT_TIME APP
---------- --------------- --------------- ---
3 20-APR-07 11:07 20-APR-07 11:10 YES
4 20-APR-07 11:10 20-APR-07 11:47 YES
5 20-APR-07 11:47 20-APR-07 11:49 YES
6 20-APR-07 11:49 20-APR-07 11:50 YES
SQL>
No comments:
Post a Comment