The following is the detail steps of how to create a physical standby database:
This case is created, operated and followed on the steps from oracle online help documentation.
The configuration of the two sites server as following:
Primary Database:
[root@vmoel5u4 ~]# uname -a
Linux vmoel5u4.oracle.com 2.6.18-164.el5 #1 SMP Thu Sep 3 02:16:47 EDT 2009 i686 i686 i386 GNU/Linux
[root@vmoel5u4 ~]# hostname
vmoel5u4.oracle.com
IP: 192.168.92.100
Standby Database:
[root@even ~]# uname -a
Linux even.oracle.com 2.6.18-164.el5 #1 SMP Thu Sep 3 02:16:47 EDT 2009 i686 i686 i386 GNU/Linux
[root@even ~]# hostname
even.oracle.com
IP: 192.168.92.200
The followings are operated on Primary database:
SQL> ALTER DATABASE FORCE LOGGING; /* This operation can be done on open database status */
alter database add standby logfile group 4 (‘/u01/app/oracle/oradata/chicago/sbredo04.log‘) size 50m;
alter database add standby logfile group 5 (‘/u01/app/oracle/oradata/chicago/sbredo05.log‘) size 50m;
alter database add standby logfile group 6 (‘/u01/app/oracle/oradata/chicago/sbredo06.log‘) size 50m;
alter database add standby logfile group 7 (‘/u01/app/oracle/oradata/chicago/sbredo07.log‘) size 50m;
/* How to drop the standby by logfile? */
alter database drop standby logfile group 4;
/* How to drop the standby by logfile? */
alter database backup controlfile to trace as ‘$ORACLE_HOME/dbs/control.bak‘;
mkdir -p /u01/app/oracle/arch1/chicago
mkdir -p /u01/app/oracle/arch2/chicago
vi initorcl.ora
DB_NAME=chicago
DB_UNIQUE_NAME=chicago
LOG_ARCHIVE_CONFIG=‘DG_CONFIG=(chicago,boston)‘
CONTROL_FILES=‘/u01/app/oracle/chicago/control1.ctl‘, ‘/u01/app/oracle/arch2/chicago/control2.ctl‘
LOG_ARCHIVE_DEST_1=‘LOCATION=/u01/app/oracle/chicago/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=chicago‘
LOG_ARCHIVE_DEST_2=‘SERVICE=boston LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=boston‘
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=10
/* The following configurations will be functioned when this primary database reverse to standby database */
FAL_SERVER=boston
FAL_CLIENT=chicago
DB_FILE_NAME_CONVERT=‘/u01/app/oracle/oradata/boston‘,‘/u01/app/oracle/oradata/chicago‘
LOG_FILE_NAME_CONVERT= ‘/u01/app/oracle/oradata/boston/‘,‘/u01/app/oracle/oradata/chicago/‘
STANDBY_FILE_MANAGEMENT=AUTO
/* The above configurations will be functioned when this primary database reverse to standby database */
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> create spfile from pfile;
File created.
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS ‘/u01/app/oracle/oradata/chicago/boston.ctl‘;
SQL> shutdown immediate;
/* make related directory on the standby database server */
/* The followings ara operated on Standby database: */
mkdir -p /u01/app/oracle/arch1/boston
mkdir -p /u01/app/oracle/oradata/boston
mkdir -p /u01/app/oracle/arch2/boston
mkdir -p /u01/app/oracle/admin/boston/{a,b,c,u,dp,p}dump
/* end for make related directory on the standby database server */
/* Copy the primary database‘s related files to standby database server */
scp /u01/app/oracle/oradata/chicago/boston.ctl boston:/u01/app/oracle/oradata/boston/control01.ctl
scp /u01/app/oracle/oradata/chicago/boston.ctl boston:/u01/app/oracle/oradata/boston/control02.ctl
scp /u01/app/oracle/oradata/chicago/boston.ctl boston:/u01/app/oracle/oradata/boston/control03.ctl
scp /u01/app/oracle/product/10.2.0/db_1/dbs/initchicago.ora boston:/u01/app/oracle/product/10.2.0/db_1/dbs/
scp /u01/app/oracle/product/10.2.0/db_1/dbs/orapwchicago boston:/u01/app/oracle/product/10.2.0/db_1/dbs/
scp /u01/app/oracle/oradata/chicago/*.dbf boston:/u01/app/oracle/oradata/boston/
scp /u01/app/oracle/oradata/chicago/redo*.log boston:/u01/app/oracle/oradata/boston/
/* The following operations are on standby database server */
[oracle@boston dbs]$ vi initboston.ora
boston.__db_cache_size=188743680
boston.__java_pool_size=4194304
boston.__large_pool_size=4194304
boston.__shared_pool_size=83886080
boston.__streams_pool_size=0
*.audit_file_dest=‘/u01/app/oracle/admin/boston/adump‘
*.background_dump_dest=‘/u01/app/oracle/admin/boston/bdump‘
*.compatible=‘10.2.0.1.0‘
*.control_files=‘/u01/app/oracle/oradata/boston/control01.ctl‘,‘/u01/app/oracle/oradata/boston/control02.ctl‘,‘/u01/app/oracle/oradata/boston/control03.ctl‘
*.core_dump_dest=‘/u01/app/oracle/admin/boston/cdump‘
*.db_block_size=8192
*.db_domain=‘‘
*.db_file_multiblock_read_count=16
*.db_name=‘boston‘
*.dispatchers=‘(PROTOCOL=TCP) (SERVICE=bostonXDB)‘
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_login_passwordfile=‘EXCLUSIVE‘
*.sga_target=285212672
*.undo_management=‘AUTO‘
*.undo_tablespace=‘UNDOTBS1‘
*.user_dump_dest=‘/u01/app/oracle/admin/boston/udump‘
DB_UNIQUE_NAME=boston
LOG_ARCHIVE_CONFIG=‘DG_CONFIG=(boston,chicago)‘
LOG_ARCHIVE_DEST_1=‘LOCATION=/u01/app/oracle/arch1/boston VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=boston‘
LOG_ARCHIVE_DEST_2=‘SERVICE=chicago LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=chicago‘
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
# When boston is reversed as the standby database, the following configuration will be useful then.
FAL_SERVER=chicago
FAL_CLIENT=boston
DB_FILE_NAME_CONVERT=‘/u01/app/oracle/oradata/chicago‘,‘/u01/app/oracle/oradata/boston‘
LOG_FILE_NAME_CONVERT= ‘/u01/app/oracle/oradata/chicago‘,‘/u01/app/oracle/oradata/boston‘
SQL>create spfile from pfile
SQL>startup mount;
alter database add standby logfile group 4 (‘/u01/app/oracle/oradata/boston/sbredo04.log‘) size 50m;
alter database add standby logfile group 5 (‘/u01/app/oracle/oradata/boston/sbredo05.log‘) size 50m;
alter database add standby logfile group 6 (‘/u01/app/oracle/oradata/boston/sbredo06.log‘) size 50m;
alter database add standby logfile group 7 (‘/u01/app/oracle/oradata/boston/sbredo07.log‘) size 50m;
/* The following is Configured on primary database for the listener.ora and the tnsname.ora */
[oracle@boston admin]$ vi listener.ora
# 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 = (PROTOCOL = TCP)(HOST = 192.168.92.200)(PORT = 1521))
)
)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=boston)
(ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1)
(SID_NAME=boston)
)
(SID_DESC=
(GLOBAL_DBNAME=chicago)
(ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1)
(SID_NAME=chicago)
)
)
[oracle@chicago admin]$ vi tnsnames.ora
BOSTON =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = tcp)(HOST = boston.oracle.com)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = boston)
(SERVER = dedicated)
)
)
CHICAGO =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.92.100)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = chicago)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
lsnrctl start
lsnrctl status
/* The following is Configured on standby database for the listener.ora and the tnsname.ora */
[oracle@boston admin]$ vi listener.ora
# 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 = (PROTOCOL = TCP)(HOST = 192.168.92.200)(PORT = 1521))
)
)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=boston)
(ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1)
(SID_NAME=boston)
)
(SID_DESC=
(GLOBAL_DBNAME=chicago)
(ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1)
(SID_NAME=chicago)
)
)
[oracle@boston admin]$ vi tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
chicago =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.92.100)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = chicago)
)
)
boston =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.92.200)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = boston)
)
)
lsnrctl start
lsnrctl status
/* switch logfile on primary database */
alter system switch logfile;
/*
Start Redo Apply.On the standby database, issue the following command to start Redo Apply:
execute the following sql stament on standby database
*/
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
alter database recover managed standby database cancel;
alter database open;
/* on standby database
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
2 19-DEC-12 19-DEC-12
3 19-DEC-12 19-DEC-12
4 19-DEC-12 19-DEC-12
5 19-DEC-12 19-DEC-12
6 19-DEC-12 19-DEC-12
7 19-DEC-12 19-DEC-12
8 19-DEC-12 19-DEC-12
9 19-DEC-12 19-DEC-12
10 19-DEC-12 19-DEC-12
11 19-DEC-12 19-DEC-12
12 19-DEC-12 19-DEC-12
SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
13 19-DEC-12 20-DEC-12
14 20-DEC-12 20-DEC-12
15 20-DEC-12 20-DEC-12
14 rows selected.
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# APP
---------- ---
2 YES
3 YES
4 YES
5 YES
6 YES
7 YES
8 YES
9 YES
10 YES
11 YES
12 YES
SEQUENCE# APP
---------- ---
13 YES
14 YES
15 YES
14 rows selected.
*/
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
(primary)(修改成MAXIMUM PROTECTION)
alter system set log_archive_dest_2=‘SERVICE=boston LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=boston NODELAY MAX_CONNECTIONS=2 REOPEN=300 NOMAX_FAILURE‘;
(standby)
alter system set log_archive_dest_2=‘SERVICE=chicago LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=chicago NODELAY MAX_CONNECTIONS=2 REOPEN=300 NOMAX_FAILURE‘;
(primary)
shutdown immediate
startup mount
alter database set standby to maximize protection;
alter database open;
(primary 和 standby都看一下是否是maximun protection)
SQL> select open_mode, database_role, switchover_status, protection_mode, protection_level from v$database;
OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS PROTECTION_MODE PROTECTION_LEVEL
---------- ---------------- -------------------- -------------------- --------------------
READ WRITE PRIMARY TO STANDBY MAXIMUM PROTECTION MAXIMUM PROTECTION
SQL> select open_mode, database_role, switchover_status, protection_mode, protection_level from v$database;
OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS PROTECTION_MODE PROTECTION_LEVEL
---------- ---------------- -------------------- -------------------- --------------------
MOUNTED PHYSICAL STANDBY NOT ALLOWED MAXIMUM PROTECTION MAXIMUM PROTECTION
On maximum protection status, when you switch log file on primary database, then at the same time, the standby logfile also archive the standby logfile to archive log file path. At this mode, you must have standby redo log on standby
database,which is directly recevied primary online redo log.
(primary)(修改成MAXIMUM AVAILABLITY)
alter system set log_archive_dest_2=‘SERVICE=boston LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=boston NODELAY MAX_CONNECTIONS=2 REOPEN=300 NOMAX_FAILURE‘;
(standby)
alter system set log_archive_dest_2=‘SERVICE=chicago LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=chicago NODELAY MAX_CONNECTIONS=2 REOPEN=300 NOMAX_FAILURE‘;
(primary)
shutdown immediate
startup mount
alter database set standby to maximize availability;
alter database open;
RFS(Remote File Server)
(primary)(修改成MAXIMUM PERFORMANCE)
alter system set log_archive_dest_2=‘SERVICE=boston LGWR ASYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=boston NODELAY MAX_CONNECTIONS=2 REOPEN=300 NOMAX_FAILURE‘;
(standby)
alter system set log_archive_dest_2=‘SERVICE=chicago LGWR ASYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=chicago NODELAY MAX_CONNECTIONS=2 REOPEN=300 NOMAX_FAILURE‘;
(primary)
shutdown immediate
startup mount
alter database set standby to maximize performance;
alter database open;
RFS(Remote File Server)
SQL> select open_mode, database_role, switchover_status, protection_mode, protection_level from v$database;
OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS PROTECTION_MODE PROTECTION_LEVEL
---------- ---------------- -------------------- -------------------- --------------------
READ WRITE PRIMARY TO STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
SQL> select open_mode, database_role, switchover_status, protection_mode, protection_level from v$database;
OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS PROTECTION_MODE PROTECTION_LEVEL
---------- ---------------- -------------------- -------------------- --------------------
MOUNTED PHYSICAL STANDBY NOT ALLOWED MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
/* What we should do before switchover to make sure the success of them*/
SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
Use the above sql statment to investigate whether all the arhived log files are applied.
/* Switchover */
primary and standby switchover
（备库一定要是disconnect）
(primary:
shutdown immediate
startup restrict;
select switchover_status from v$database;(switchover_status 是to standby 状态就可以切换了)
alter database commit to switchover to physical standby;
shutdown immediate
startup mount;(startup 是起不来的,因为是2个备库)
)
(standby:如果是to primary就正常了,否则就问题大了)
select switchover_status from v$database;
alter database commit to switchover to primary;
shutdown immediate;(因为控制文件主备格式不一样,必须shutdown)
/* Check the status again to see whether the role transformation is successful */
SQL> select dbid,name,DB_UNIQUE_NAME,switchover_status from v$database;
DBID NAME DB_UNIQUE_NAME SWITCHOVER_STATUS
---------- --------- ------------------------------ --------------------
1412188811 CHICAGO chicago TO PRIMARY
SQL> select dbid,name,DB_UNIQUE_NAME,switchover_status from v$database;
DBID NAME DB_UNIQUE_NAME SWITCHOVER_STATUS
---------- --------- ------------------------------ --------------------
1412188811 CHICAGO boston TO STANDBY
We can know the transformation is successful through the above information.
Creating a Physical Standby Database
This chapter steps you through the process of creating a physical standby database. It includes the following main topics:
■ Preparing the Primary Database for Standby Database Creation
■ Step-by-Step Instructions for Creating a Physical Standby Database
■ Post-Creation Steps
The steps described in this chapter configure the standby database for maximum performance mode, which is the default data protection mode.Chapter 5provides information about configuring the different data protection modes. The discussions in this chapter assume that you specify initialization parameters in a server parameter file (SPFILE), instead of a text initialization parameter file (PFILE).
See also:
■ Oracle Database Administrator‘s Guidefor information about creating and using server parameter files
■ Oracle Data Guard Brokerand the Enterprise Manager online help system for information about using the graphical user interface to automatically create a physical standby database
3.1 Preparing the Primary Database for Standby Database Creation
Before you create a standby database you must first ensure the primary database is properly configured.
Table 3–1 provides a checklist of the tasks that you perform on the primary database to prepare for physical standby database creation. There is also a reference to the section that describes the task in more detail.
Table 3–1 Preparing the Primary Database for Physical Standby Database Creation
Reference Task
Section 3.1.1 Enable Forced Logging
Section 3.1.2 Create a Password File
Section 3.1.3 Configure a Standby Redo Log
Section 3.1.4 Set Primary Database Initialization Parameters
Section 3.1.5 Enable Archiving
Note: Perform these preparatory tasks only once. After you complete these steps, the database is prepared to serve as the primary database for one or more standby databases.
3.1.1 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 statement can take a considerable amount of time to complete, because it waits for all unlogged direct write I/O to finish.
3.1.2 Create a Password File
Create a password file if one does not already exist. Every database in a Data Guard configuration must use a password file, and the password for the SYS user must be identical on every system for redo data transmission to succeed. SeeOracle Database Administrator‘s Guide.
3.1.3 Configure a Standby Redo Log
A standby redo log is required for the maximum protection and maximum availability modes and the LGWR ASYNC transport mode is recommended for all databases. Data Guard can recover and apply more redo data from a standby redo log than from archived redo log files alone. You should plan the standby redo log configuration and create all required log groups and group members when you create the standby database. For increased availability, consider multiplexing the standby redo log files, similar to the way that online redo log files are multiplexed.
Perform the following steps to configure the standby redo log.
Step 1 Ensure log file sizes are identical on the primary and standby databases.
The size of the current standby redo log files must exactly match the size of the current primary database online redo log files. For example, if the primary database uses two online redo log groups whose log files are 200K, then the standby redo log groups should also have log file sizes of 200K.
Step 2 Determine the appropriate number of standby redo log file groups.
Minimally, the configuration should have one more standby redo log file group than the number of online redo log file groups on the primary database. However, therecommendednumber of standby redo log file groups is dependent on the number of threads on the primary database. Use the following equation to determine an appropriate number of standby redo log file groups:
( maximum number of logfiles for each thread + 1) * maximum number of threads
Using this equation reduces the likelihood that the primary instance’s log writer (LGWR) process will be blocked because a standby redo log file cannot be allocated on the standby database. For example, if the primary database has 2 log files for each thread and 2 threads, then 6 standby redo log file groups are needed on the standby database.
Note: Logical standby databases may require more standby redo log files (or additional ARCnprocesses) depending on the workload. This is because logical standby databases also write to online redo log files, which take precedence over standby redo log files. Thus, the standby redo log files may not be archived as quickly as the online redo log files. Also, seeSection 5.7.3.1.
Step 3 Verify related database parameters and settings.
Verify the values used for the MAXLOGFILESandMAXLOGMEMBERSclauses on the SQLCREATE DATABASEstatement will not limit the number of standby redo log file groups and members that you can add. The only way to override the limits specified by theMAXLOGFILESandMAXLOGMEMBERSclauses is to re-create the primary
database or control file. How to know the number of these two types of parameters’?
alter database backup controlfile to trace as ‘$ORACLE_HOME/dbs/control.bak‘;
Step 4 Create standby redo log file groups.
To create new standby redo log file groups and members, you must have theALTER DATABASEsystem privilege. The standby database begins using the newly createdstandby redo data the next time there is a log switch on the primary database.
Example 3–1 andExample 3–2show how to create a new standby redo log file group using theALTER DATABASEstatement with variations of theADD STANDBYLOGFILE GROUPclause.
Example 3–1 Adding a Standby Redo Log File Group to a Specific Thread
The following statement adds a new standby redo log file group to a standby database and assigns it toTHREAD 5:
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 5
2> (‘/oracle/dbs/log1c.rdo‘,‘/oracle/dbs/log2c.rdo‘) SIZE 500M;
The THREAD clause is required only if you want to add one or more standby redo log file groups to aspecificprimary database thread. If you do not include theTHREAD clause and the configuration uses Real Application Clusters (RAC), Data Guard will automatically assign standby redo log file groups to threads at runtime as they are
needed by the various RAC instances.
Example 3–2 Adding a Standby Redo Log File Group to a Specific Group Number
You can also specify a number that identifies the group using theGROUPclause:
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 10
2> (‘/oracle/dbs/log1c.rdo‘,‘/oracle/dbs/log2c.rdo‘) SIZE 500M;
Using group numbers can make administering standby redo log file groups easier. However, the group number must be between 1 and the value of theMAXLOGFILES clause. Do not skip log file group numbers (that is, do not number groups 10, 20, 30, and so on), or you will use additional space in the standby database control file.
Note: Although the standby redo log is only used when the database is running in the standby role, Oracle recommends that you create a standby redo log on the primary database so that the primary database can switch over quickly to the standby role without the need for additional DBA intervention. Consider using Oracle Enterprise Manager to automatically configure standby redo log on both your primary and standby databases.
Step 5 Verify the standby redo log file groups were created.
To verify the standby redo log file groups are created and running correctly, invoke a log switch on the primary database, and then query either theV$STANDBY_LOGview or theV$LOGFILEview on the standby database once it has been created. For example:
SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
GROUP# THREAD# SEQUENCE# ARC STATUS
---------- ---------- ---------- --- ----------
3 1 16 NO ACTIVE
4 0 0 YES UNASSIGNED
5 0 0 YES UNASSIGNED
3.1.4 Set Primary Database Initialization Parameters
On the primary database, you define initialization parameters that control redo transport services while the database is in the primary role. There are additional parameters you need to add that control the receipt of the redo data and log apply services when the primary database is transitioned to the standby role.
Example 3–3 shows the primary role initialization parameters that you maintain on the primary database. This example represents a Data Guard configuration with a primary database located in Chicago and one physical standby database located in Boston. The parameters shown inExample 3–3are valid for the Chicago database when it is running in either the primary or the standby database role. The configuration examples use the names shown in the following table:
Database DB_UNIQUE_NAME Oracle Net Service Name
Primary chicago chicago
Physical standby boston boston
Example 3–3 Primary Database: Primary Role Initialization Parameters
DB_NAME=chicago
DB_UNIQUE_NAME=chicago
LOG_ARCHIVE_CONFIG=‘DG_CONFIG=(chicago,boston)‘
CONTROL_FILES=‘/arch1/chicago/control1.ctl‘, ‘/arch2/chicago/control2.ctl‘
LOG_ARCHIVE_DEST_1=‘LOCATION=/arch1/chicago/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=chicago‘
LOG_ARCHIVE_DEST_2=‘SERVICE=boston LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=boston‘
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
These parameters control how redo transport services transmit redo data to the standby system and the archiving of redo data on the local file system. Note that the example specifies theLGWRprocess and asynchronous (ASYNC) network transmission to transmit redo data on theLOG_ARCHIVE_DEST_2initialization parameter. These are the recommended settings and require standby redo log files (seeSection 3.1.3,"Configure a Standby Redo Log"on page 3-2).Example 3–4shows the additional standby role initialization parameters on the primary database. These parameters take effect when the primary database is transitioned to the standby role.
Example 3–4 Primary Database: Standby Role Initialization Parameters(当chicago变为standby的时候就用下面的参数,这个时候boston是主库，chicago是备库，所以(先主备)DB_FILE_NAME_CONVERT=’boston’,’chicago’
FAL_SERVER=boston
FAL_CLIENT=chicago
DB_FILE_NAME_CONVERT=
‘/u01/app/oracle/oradata/boston‘,‘/u01/app/oracle/oradata/chicago‘
LOG_FILE_NAME_CONVERT= ‘/arch1/boston/‘,‘/arch1/chicago/‘,‘/arch2/boston/‘,‘/arch2/chicago/‘
STANDBY_FILE_MANAGEMENT=AUTO
Specifying the initialization parameters shown in Example 3–4 sets up the primary database to resolve gaps, converts new datafile and log file path names from a new primary database, and archives the incoming redo data when this database is in the standby role. With the initialization parameters for both the primary and standby roles set as described, none of the parameters need to change after a role transition. The following table provides a brief explanation about each parameter setting shown in Example 3–3 andExample 3–4.
Parameter Recommended Setting
DB_NAME Specify an 8-character name. Use the same name for all standby databases.
DB_UNIQUE_NAME Specify a unique name for each database. This name stays with the database and
does not change, even if the primary and standby databases reverse roles.
LOG_ARCHIVE_CONFIG Specify theDG_CONFIGattribute on this parameter to list theDB_UNIQUE_
NAME of the primary and standby databases in the Data Guard configuration;
this enables the dynamic addition of a standby database to a Data Guard
configuration that has a Real Application Clusters primary database running in
either maximum protection or maximum availability mode. By default, the
LOG_ARCHIVE_CONFIG parameter enables the database to send and receive
redo; after a role transition, you may need to specify these settings again using
theSEND,NOSEND,RECEIVE, orNORECEIVEkeywords.
CONTROL_FILES Specify the path name for the control files on the primary database.
Example 3–3 shows how to do this for two control files. It is recommended that
a second copy of the control file is available so an instance can be easily restarted after
copying the good control file to the location of the bad control file.
LOG_ARCHIVE_DEST_n Specify where the redo data is to be archived on the primary and standby
systems. InExample 3–3:
■LOG_ARCHIVE_DEST_1archives redo data generated by the primary
database from the local online redo log files to the local archived redo log
files in /arch1/chicago/.
■LOG_ARCHIVE_DEST_2is valid only for the primary role. This destination
transmits redo data to the remote physical standby destinationboston.
Note: If a flash recovery area was configured (with theDB_RECOVERY_FILE_
DEST initialization parameter) and you have not explicitly configured a local
archiving destination with the LOCATIONattribute, Data Guard automatically
uses the LOG_ARCHIVE_DEST_10initialization parameter as the default
destination for local archiving. See Section 5.2.3for more information. Also, see
Chapter 14 for completeLOG_ARCHIVE_DEST_ninformation.
LOG_ARCHIVE_DEST_STATE_n SpecifyENABLEto allow redo transport services to transmit redo data to the
specified destination.
REMOTE_LOGIN_PASSWORDFILE Set the same password forSYSon both the primary and standby databases. The
recommended setting is eitherEXCLUSIVEorSHARED.
LOG_ARCHIVE_FORMAT Specify the format for the archived redo log files using a thread (%t), sequence
number (%s), and resetlogs ID (%r). SeeSection 5.7.1for another example.
LOG_ARCHIVE_MAX_PROCESSES =integer
Specify the maximum number (from 1 to 30) of archiver (ARCn) processes youwant Oracle software to invoke initially. The default value is 4. SeeSection 5.3.1.2for more information aboutARCnprocessing.
FAL_SERVER Specify the Oracle Net service name of the FAL server (typically this is the
database running in the primary role). When the Chicago database is running in
the standby role, it uses the Boston database as the FAL server from which to
fetch (request) missing archived redo log files if Boston is unable to
automatically send the missing log files. See Section 5.8.
FAL_CLIENT Specify the Oracle Net service name of the Chicago database. The FAL server
(Boston) copies missing archived redo log files to the Chicago standby database.
See Section 5.8.
DB_FILE_NAME_CONVERT Specify the path name and filename location of the primary database datafiles
followed by the standby location. This parameter converts the path names of the
primary database datafiles to the standby datafile path names. If the standby
database is on the same system as the primary database or if the directory
structure where the datafiles are located on the standby site is different from the
primary site, then this parameter is required. Note that this parameter is used
only to convert path names for physical standby databases. Multiple pairs of
paths may be specified by this parameter.
LOG_FILE_NAME_CONVERT Specify the location of the primary database online redo log files followed by
the standby location. This parameter converts the path names of the primary
database log files to the path names on the standby database. If the standby
database is on the same system as the primary database or if the directory
structure where the log files are located on the standby system is different from
the primary system, then this parameter is required. Multiple pairs of paths may
be specified by this parameter.
STANDBY_FILE_MANAGEMENT Set toAUTOso when datafiles are added to or dropped from the primary
database, corresponding changes are made automatically to the standby database.
Caution: Review the initialization parameter file for additional parameters that may need to be modified. For example, you may need to modify the dump destination parameters (BACKGROUND_DUMP_DEST, CORE_DUMP_DEST, USER_DUMP_DEST) if the directory location on the standby database is different from those
specified on the primary database. In addition, you may have to create directories on the standby system if they do not already exist.
3.1.5 Enable Archiving
If archiving is not enabled, issue the following statements to put the primary database in ARCHIVELOG mode and enable automatic archiving:
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
See Oracle Database Administrator‘s Guidefor information about archiving.
3.2 Step-by-Step Instructions for Creating a Physical Standby Database
This section describes the tasks you perform to create a physical standby database.Table 3–2provides a checklist of the tasks that you perform to create a physical standby database and the database or databases on which you perform each task. There is also a reference to the section that describes the task in more detail.
Reference Task Database
Section 3.2.1 Create a Backup Copy of the Primary Database Datafiles Primary
Section 3.2.2 Create a Control File for the Standby Database Primary
Section 3.2.3 Prepare an Initialization Parameter File for the Standby Database Primary
Section 3.2.4 Copy Files from the Primary System to the Standby System Primary
Section 3.2.5 Set Up the Environment to Support the Standby Database Standby
Section 3.2.6 Start the Physical Standby Database Standby
Section 3.2.7 Verify the Physical Standby Database Is Performing Properly Standby
3.2.1 Create a Backup Copy of the Primary Database Datafiles
You can use any backup copy of the primary database to create the physical standby database, as long as you have the necessary archived redo log files to completely recover the database. Oracle recommends that you use the Recovery Manager utility (RMAN).
See Oracle High Availability Architecture and Best Practicesfor backup recommendations andOracle Database Backup and Recovery Advanced User‘s Guideto perform an RMAN backup operation.
3.2.2 Create a Control File for the Standby Database
If the backup procedure required you to shut down the primary database, issue the following SQL*Plus statement to start the primary database:
SQL> STARTUP MOUNT;
Then, create the control file for the standby database, and open the primary database to user access, as shown in the following example:
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS ‘/tmp/boston.ctl‘;
SQL> ALTER DATABASE OPEN;
Note: You cannot use a single control file for both the primary and standby databases.
3.2.3 Prepare an Initialization Parameter File for the Standby Database
Perform the following steps to create a standby initialization parameter file.
Step 1 Copy the primary database parameter file to the standby database.
Create a text initialization parameter file (PFILE) from the server parameter file (SPFILE) used by the primary database; a text initialization parameter file can be copied to the standby location and modified. For example:
SQL> CREATE PFILE=‘/tmp/initboston.ora‘ FROM SPFILE;
Later, in Section 3.2.5, you will convert this file back to a server parameter file after it is modified to contain the parameter values appropriate for use with the physical standby database.
Step 2 Set initialization parameters on the physical standby database.
Although most of the initialization parameter settings in the text initialization parameter file that you copied from the primary system are also appropriate for the physical standby database, some modifications need to be made.
Example 3–5 shows the portion of the standby initialization parameter file where values were modified for the physical standby database. Parameter values that are different fromExample 3–3 andExample 3–4are shown in bold typeface. The parameters shown inExample 3–5are valid for the Boston database when it is running in either the primary or the standby database role.
Example 3–5 Modifying Initialization Parameters for a Physical Standby Database
.
.
.
DB_NAME=chicago
DB_UNIQUE_NAME=boston
LOG_ARCHIVE_CONFIG=‘DG_CONFIG=(chicago,boston)‘
CONTROL_FILES=‘/arch1/boston/control1.ctl‘, ‘/arch2/boston/control2.ctl‘
DB_FILE_NAME_CONVERT=‘chicago‘,‘boston‘
LOG_FILE_NAME_CONVERT=
‘/arch1/chicago/‘,‘/arch1/boston/‘,‘/arch2/chicago/‘,‘/arch2/boston/‘
LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc
LOG_ARCHIVE_DEST_1=
‘LOCATION=/arch1/boston/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=boston‘
LOG_ARCHIVE_DEST_2=
‘SERVICE=chicago LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=chicago‘
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER=chicago
FAL_CLIENT=boston
.
.
.
Note that the example assumes the use of the LGWR process to transmit redo data to both the local and remote destinations on theLOG_ARCHIVE_DEST_2initialization parameter. In addition, ensure theCOMPATIBLEinitialization parameter is set to the same value on both the primary and standby databases. If the values differ, redo transport services may be unable to transmit redo data from the primary database to the standby databases. In a Data Guard configuration,COMPATIBLE must be set to a minimum of 9.2.0.1.0. However, if you want to take advantage of new Oracle Database 10gfeatures, set theCOMPATIBLEparameter to 10.2.0.0 or higher.
It is always a good practice to use the SHOW PARAMETERScommand to verify no other parameters need to be changed. The following table provides a brief explanation about the parameter settings shown inExample 3–5that have different settings from the primary database.
Parameter Recommended Setting
DB_UNIQUE_NAME Specify a unique name for this database. This name stays with the database and
does not change even if the primary and standby databases reverse roles.
CONTROL_FILES Specify the path name for the control files on the standby database.Example 3–5
shows how to do this for two control files. It is recommended that a second
copy of the control file is available so an instance can be easily restarted after
copying the good control file to the location of the bad control file.
DB_FILE_NAME_CONVERT Specify the path name and filename location of the primary database datafiles
followed by the standby location.This parameter converts the path names of the
primary database datafiles to the standby datafile path names. If the standby
database is on the same system as the primary database or if the directory
structure where the datafiles are located on the standby site is different from the
primary site, then this parameter is required.
(DB_FILE_NAME_CONVERT=’主库路径’,’备库路经’)
LOG_FILE_NAME_CONVERT Specify the location of the primary database online redo log files followed by
the standby location. This parameter converts the path names of the primary
database log files to the path names on the standby database. If the standby
database is on the same system as the primary database or if the directory
structure where the log files are located on the standby system is different from
the primary system, then this parameter is required.
LOG_ARCHIVE_DEST_n Specify where the redo data is to be archived. InExample 3–5:
■ LOG_ARCHIVE_DEST_1archives redo data received from the primary
database to archived redo log files in /arch1/boston/.
■ LOG_ARCHIVE_DEST_2is currently ignored because this destination is
valid only for the primary role. If a switchover occurs and this instance
becomes the primary database, then it will transmit redo data to the remote
Chicago destination.
Note: If a flash recovery area was configured (with theDB_RECOVERY_FILE_DESTinitialization parameter) and you have not explicitly configured a localarchiving destination with theLOCATIONattribute, Data Guard automaticallyuses theLOG_ARCHIVE_DEST_10initialization parameter as the defaultdestination for local archiving. SeeSection 5.2.3for more information. Also, seeChapter 14for complete information aboutLOG_ARCHIVE_DEST_n.
FAL_SERVER Specify the Oracle Net service name of the FAL server (typically this is the
database running in the primary role). When the Boston database is running in
the standby role, it uses the Chicago database as the FAL server from which to
fetch (request) missing archived redo log files if Chicago is unable to
automatically send the missing log files. See Section 5.8.
FAL_CLIENT Specify the Oracle Net service name of the Boston database. The FAL server
(Chicago) copies missing archived redo log files to the Boston standby database.
See Section 5.8.
Caution: Review the initialization parameter file for additional parameters that may need to be modified. For example, you may need to modify the dump destination parameters (BACKGROUND_DUMP_DEST, CORE_DUMP_DEST, USER_DUMP_DEST) if the directory location on the standby database is different from those
specified on the primary database. In addition, you may have to create directories on the standby system if they do not already exist.
3.2.4 Copy Files from the Primary System to the Standby System
Use an operating system copy utility to copy the following binary files from the primary system to the standby system:
■ Backup datafiles created inSection 3.2.1
■ Standby control file created inSection 3.2.2
■ Initialization parameter file created inSection 3.2.3
3.2.5 Set Up the Environment to Support the Standby Database
Perform the following steps to create a Windows-based service, create a password file, set up the Oracle Net environment, and create a SPFILE.
Step 1 Create a Windows-based service.
If the standby system is running on a Windows-based system, use the ORADIM utility to create a Windows Service and password file. For example:
WINNT> oradim -NEW -SID boston -INTPWD password-STARTMODE manual
See Oracle Database Platform Guide for Microsoft Windows (32-Bit)for more information about using the ORADIM utility.
Step 2 Create a password file.
On platforms other than Windows, create a password file, and set the password for theSYSuser to the same password used by theSYSuser on the primary database. The password for theSYSuser on every database in a Data Guard configuration must be identical for redo transmission to succeed. SeeOracle Database Administrator‘s Guide.
Step 3 Configure listeners for the primary and standby databases.
On both the primary and standby sites, use Oracle Net Manager to configure a listener for the respective databases.
To restart the listeners (to pick up the new definitions), enter the following LSNRCTL utility commands on both the primary and standby systems:
% lsnrctl stop
% lsnrctl start
See Oracle Database Net Services Administrator‘s Guide.
Step 4 Create Oracle Net service names.
On both the primary and standby systems, use Oracle Net Manager to create a network service name for the primary and standby databases that will be used by redo transport services. The Oracle Net service name must resolve to a connect descriptor that uses the same protocol, host address, port, and service that you specified when you configured the listeners for the primary and standby databases. The connect descriptor must also specify that a dedicated server be used. See theOracle Database Net Services Administrator‘s Guideand theOracle Database
Administrator‘s Guide.
Step 5 Create a server parameter file for the standby database.
On an idle standby database, use the SQL CREATEstatement to create a server parameter file for the standby database from the text initialization parameter file that was edited in Step 2 on page 3-8. For example:
SQL> CREATE SPFILE FROM PFILE=‘initboston.ora‘;
3.2.6 Start the Physical Standby Database
Perform the following steps to start the physical standby database and Redo Apply.
Step 1 Start the physical standby database.
On the standby database, issue the following SQL statement to start and mount the database:
SQL> STARTUP MOUNT;
Step 2 Start Redo Apply.
On the standby database, issue the following command to start Redo Apply:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
The statement includes the DISCONNECT FROM SESSIONoption so that Redo Apply runs in a background session. SeeSection 6.3, "Applying Redo Data to PhysicalStandby Databases"for more information.
Step 3 Test archival operations to the physical standby database.
In this example, the transmission of redo data to the remote standby location does not occur until after a log switch. A log switch occurs, by default, when an online redo log file becomes full. To force a log switch so that redo data is transmitted immediately, use the following ALTER SYSTEM statement on the primary database. For example:
SQL> ALTER SYSTEM SWITCH LOGFILE;
3.2.7 Verify the Physical Standby Database Is Performing Properly
Once you create the physical standby database and set up redo transport services, you may want to verify database modifications are being successfully transmitted from the primary database to the standby database. To see that redo data is being received on the standby database, you should first identify the existing archived redo log files on the standby database, force a log switch and archive a few online redo log files on the primary database, and then check the standby database again. The following steps show how to perform these tasks.
Step 1 Identify the existing archived redo log files.
On the standby database, query the V$ARCHIVED_LOG view to identify existing files in the archived redo log. For example:
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME
2 FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIME NEXT_TIME
---------- ------------------ ------------------
8 11-JUL-02 17:50:45 11-JUL-02 17:50:53
9 11-JUL-02 17:50:53 11-JUL-02 17:50:58
10 11-JUL-02 17:50:58 11-JUL-02 17:51:03
3 rows selected.
Step 2 Force a log switch to archive the current online redo log file.
On the primary database, issue the ALTER SYSTEM SWITCH LOGFILE statement to force a log switch and archive the current online redo log file group:
SQL> ALTER SYSTEM SWITCH LOGFILE;
Step 3 Verify the new redo data was archived on the standby database.
On the standby database, query the V$ARCHIVED_LOG view to verify the redo data was received and archived on the standby database:
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME
2> FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIME NEXT_TIME
---------- ------------------ ------------------
8 11-JUL-02 17:50:45 11-JUL-02 17:50:53
9 11-JUL-02 17:50:53 11-JUL-02 17:50:58
10 11-JUL-02 17:50:58 11-JUL-02 17:51:03
11 11-JUL-02 17:51:03 11-JUL-02 18:34:11
4 rows selected.
The archived redo log files are now available to be applied to the physical standby database.
Step 4 Verify new archived redo log files were applied.
On the standby database, query the V$ARCHIVED_LOGview to verify the archived redo log files were applied.
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG
2 ORDER BY SEQUENCE#;
SEQUENCE# APP
--------- ---
8 YES
9 YES
10 YES
11 YES
4 rows selected.
See Section 5.9.1, "Monitoring Log File Archival Information"andSection 8.5.4, "Monitoring Log Apply Services on Physical Standby Databases"to verify redotransport services and log apply services are working correctly.
3.3 Post-Creation Steps
At this point, the physical standby database is running and can provide the maximum performance level of data protection. The following list describes additional preparations you can take on the physical standby database:
■ Upgrade the data protection mode
The Data Guard configuration is initially set up in the maximum performance mode (the default). SeeSection 5.6for information about the data protection modes and how to upgrade or downgrade the current protection mode.
■ Enable Flashback Database
Flashback Database removes the need to re-create the primary database after a failover. Flashback Database enables you to return a database to its state at a time in the recent past much faster than traditional point-in-time recovery, because it does not require restoring datafiles from backup nor the extensive application of redo data. You can enable Flashback Database on the primary database, the standby database, or both. SeeSection 12.4andSection 12.5for scenarios showing how to use Flashback Database in a Data Guard environment. Also, seeOracle Database Backup and Recovery Advanced User‘s Guidefor more information about Flashback Database.
If you wish to failover to this standby database, you should use the following command to manually register the archivelog for recovery:
ALTER DATABASE REGISTER LOGFILE ‘/u01/app/oracle/arch1/boston/1_33_802470095.arc‘;