1.Environment
|
Item |
Primary database |
standby database |
|
Platform |
Redhat 5.4 |
Redhat 5.4 |
|
Hostname |
gc1 |
gc2 |
|
Database |
Oracle 11.2.0.1.0 |
Oracle 11.2.0.1.0 |
|
ORACLE_SID |
PROD |
standby |
|
DB_UNIQUE_NAME |
primary |
standby |
2.Preparing the PrimaryDatabase for Standby Database Creation
2.1 EnableForced Logging
主库启动到MOUNT
查看是否开启Force logging：
select force_logging fromv$database;
FOR
---
YES
开启Force logging：
SQL> ALTER DATABASE FORCELOGGING;
2.2 Enable Archiving
查看是否开启归档：
SQL>archive log list
Databaselog mode Archive Mode
Automaticarchival Disabled
Archivedestination /u01/app/oracle/arch
Oldest online log sequence 18
Next log sequence to archive 20
Current logsequence 20
开启归档：
SQL> ALTER DATABASE ARCHIVELOG
2.3 Set Primary DatabaseInitialization Parameters
|
Database |
DB_UNIQUE_NAME |
Oracle Net Service Name(TNS名) |
|
Primary |
primary |
primary |
|
Physical standby |
standby |
standby |
|
DB_NAME=chicago |
库名 |
|
DB_UNIQUE_NAME=chicago |
主库备库的唯一标识名 |
|
LOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)' |
这里是主备库的db unique name |
|
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 ASYNC |
最大性能模式为缺省模式,能够使用LGWR ASYNC或ARCH实现 最大保护模式和最大可用性模式要求standby databse必须配置standby redo log必须设置LGWR SYNC AFFIRM方式 在11G文档中特意强调,redo方式的日志传递不是通过LGWR进程传递的,而是由LNS进程传递的,因此属于也从10g的LGWR SYNC、LGWR ASYNC编程了SYNC和ASYNC AFFIRM—specifies that a redo transport destination acknowledges received redo data after writing it to the standby redo log. NOAFFIRM—specifies that a redo transport destination acknowledges received redo data before writing it to the standby redo log. 缺省NOAFFIRM |
|
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 |
|
FAL_SERVER=boston |
Fetch Archive log 这里指明当出现GAP时,从哪里Fetch |
|
DB_FILE_NAME_CONVERT='boston','chicago' |
先对方,再自己 |
|
LOG_FILE_NAME_CONVERT= |
先对方,再自己 |
|
'/arch1/boston/','/arch1/chicago/','/arch2/boston/','/arch2/chicago/' |
|
|
STANDBY_FILE_MANAGEMENT=AUTO |
列子：
*.db_name='PROD'
*.db_recovery_file_dest_size=4294967296
*.db_recovery_file_dest='/u01/app/oracle/flash'
*.diagnostic_dest='/u01/app/oracle/oradata/PROD/dump'
*.DISPATCHERS='(PROTOCOL=TCP)(DISPATCHERS=3)'
*.job_queue_processes=15
*.local_listener=''
*.max_dispatchers=10
*.max_shared_servers=30
*.memory_target=800m
*.processes=150
*.recyclebin='on'
*.sessions=300
*.shared_server_sessions=200
*.undo_retention=5400
*.undo_tablespace='undotbs'
*.utl_file_dir='/u01/app/oracle/utl'
DB_UNIQUE_NAME=primary
LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary,standby)'
LOG_ARCHIVE_DEST_1=
'LOCATION=/u01/app/oracle/arch
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=primary'
LOG_ARCHIVE_DEST_2=
'SERVICE=standby SYNC AFFIRM NET_TIMEOUT=30
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=standby'
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=3
FAL_SERVER=standby
DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/standby/disk3','/u01/app/oracle/oradata/PROD/disk3','/u01/app/oracle/oradata/standby/disk4','/u01/app/oracle/oradata/PROD/disk4','/u01/app/oracle/oradata/standby/disk5','/u01/app/oracle/oradata/PROD/disk5'
LOG_FILE_NAME_CONVERT=
'/u01/app/oracle/oradata/standby/disk1','/u01/app/oracle/oradata/PROD/disk1','/u01/app/oracle/oradata/standby/disk2','/u01/app/oracle/oradata/PROD/disk2'
STANDBY_FILE_MANAGEMENT=AUTO
2.4 Create aControl File for the Standby Database
SQL> STARTUP MOUNT;
SQL> ALTER DATABASECREATE STANDBY CONTROLFILE AS '/home/oracle/std_control01.ctl';
2.5 Create a ParameterFile for the Standby Database
将主库写好的pfile传输到备库
改动pfile
样例：
*.db_name='PROD'
*.db_recovery_file_dest_size=4294967296
*.db_recovery_file_dest='/u01/app/oracle/flash'
*.diagnostic_dest='/u01/app/oracle/oradata/standby/dump'
*.DISPATCHERS='(PROTOCOL=TCP)(DISPATCHERS=3)'
*.job_queue_processes=15
*.local_listener=''
*.max_dispatchers=10
*.max_shared_servers=30
*.processes=150
*.recyclebin='on'
*.sessions=300
*.shared_server_sessions=200
*.undo_retention=5400
*.undo_tablespace='undotbs'
*.utl_file_dir='/u01/app/oracle/utl'
DB_UNIQUE_NAME=standby
LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary,standby)'
LOG_ARCHIVE_DEST_1=
'LOCATION=/u01/app/oracle/arch
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=standby'
LOG_ARCHIVE_DEST_2=
'SERVICE=primary SYNC AFFIRM NET_TIMEOUT=30
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=primary'
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=3
FAL_SERVER=primary
DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/PROD/disk3','/u01/app/oracle/oradata/standby/disk3','/u01/app/oracle/oradata/PROD/disk4','/u01/app/oracle/oradata/standby/disk4','/u01/app/oracle/oradata/PROD/disk5','/u01/app/oracle/oradata/standby/disk5'
LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/PROD/disk1','/u01/app/oracle/oradata/standby/disk1','/u01/app/oracle/oradata/PROD/disk2','/u01/app/oracle/oradata/standby/disk2'
STANDBY_FILE_MANAGEMENT=AUTO
2.6 Copy Files from thePrimary System to the Standby System
将standby controlfile,数据文件和password文件复制到备库(注意password文件名要相应SID)
3.配置监听和TNS
Listener：
LISTENER=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.8.152)(PORT=1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=extproc))
)
)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=plsextproc)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
(PROGRAM=extproc)
)
)
TNS：
primary=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.8.150)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=PRIMARY)
(SERVER=DEDICATED)
)
)
standby=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.8.152)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=STANDBY)
(SERVER=DEDICATED)
)
)
两边启动到MOUNT开启监听,用sqlplus sys/oracle@primary as sysdb測试能否够连通
4.加入standby redo log
idle>selectgroup#,MEMBERS,BYTES/1024/1024 m from v$log;
GROUP# MEMBERS M
-------------------- ----------
1 2 100
2 2 100
3 2 100
standby redo log仅仅少应比redo log多一组。且大小相等
备库加入(启动到MOUNT)：
alter database addstandby logfile('/u01/app/oracle/oradata/standby/disk1/std_redo01.log','/u01/app/oracle/oradata/standby/disk2/std_redo01.log')size 100m;
alter database addstandby logfile('/u01/app/oracle/oradata/standby/disk1/std_redo02.log','/u01/app/oracle/oradata/standby/disk2/std_redo02.log')size 100m;
alter database addstandby logfile('/u01/app/oracle/oradata/standby/disk1/std_redo03.log','/u01/app/oracle/oradata/standby/disk2/std_redo03.log')size 100m;
alter database addstandby logfile('/u01/app/oracle/oradata/standby/disk1/std_redo04.log','/u01/app/oracle/oradata/standby/disk2/std_redo04.log')size 100m;
主库加入(由于主库有可能会切换成备库)：
alter database addstandby logfile('/u01/app/oracle/oradata/PROD/disk1/std_redo01.log','/u01/app/oracle/oradata/PROD/disk2/std_redo01.log')size 100m;
alter database addstandby logfile('/u01/app/oracle/oradata/PROD/disk1/std_redo02.log','/u01/app/oracle/oradata/PROD/disk2/std_redo02.log')size 100m;
alter database addstandby logfile('/u01/app/oracle/oradata/PROD/disk1/std_redo03.log','/u01/app/oracle/oradata/PROD/disk2/std_redo03.log')size 100m;
alter database addstandby logfile('/u01/app/oracle/oradata/PROD/disk1/std_redo04.log','/u01/app/oracle/oradata/PROD/disk2/std_redo04.log')size 100m;
5.开库
open主库后
备库：
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECTFROM SESSION;
假设有STANDBY REDO LOG：
SQL> ALTER DATABASE RECOVER MANAGED STANDBYDATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
11g能够open备库,10g备库仅仅能在MOUNT
RECOVER MANAGED STANDBY DATABASE CANCEL后再OPEN
6.模式切换
MAXIMUM AVAILABILITY
idle>alterdatabase set standby database to maximize AVAILABILITY;
Database altered.
idle>selectname,dbid,database_role,protection_mode from v$database;
NAME DBIDDATABASE_ROLE PROTECTION_MODE
--------- -------------------------- --------------------
PROD 239015079PRIMARY MAXIMUM AVAILABILITY
MAXIMUMPROTECTION：
idle>alterdatabase set standby database to maximize protection;
Database altered.
idle>selectname,dbid,database_role,protection_mode from v$database;
NAME DBIDDATABASE_ROLE PROTECTION_MODE
--------- -------------------------- --------------------
PROD 239015079PRIMARY MAXIMUM PROTECTION
Note that the data protection mode can be set toMAXIMUM PROTECTION on an open database only if the current data protection modeis MAXIMUM AVAILABILITY and if there is at least one synchronized standbydatabase.
11g在OPEN状态下就能够切换到MAXIMIZE PROTECTION
Configuring an Oracle Database toSend Redo Data
This section describes how toconfigure an Oracle database to send redo data to a redo transportdestination.
The LOG_ARCHIVE_DEST_n database initialization parameter (where n is an integer from 1 to 31) isused to specify the location of a local archive redo log or to specify a redotransport destination. This section describes the latter use of this parameter.
There is a LOG_ARCHIVE_DEST_STATE_n databaseinitialization parameter (where n is an integer from 1 to 31) thatcorresponds to each LOG_ARCHIVE_DEST_nparameter. This parameter is used toenable or disable the corresponding redo destination. Table 6-1 shows the validvalues that can be assigned to this parameter.
Table 6-1 LOG_ARCHIVE_DEST_STATE_nInitialization Parameter Values
|
Value |
Description |
|
ENABLE |
Redo transport services can transmit redo data to this destination. This is the default. |
|
DEFER |
Redo transport services will not transmit redo data to this destination. |
|
ALTERNATE |
This destination will become enabled if communication to its associated destination fails. |
A redo transportdestination is configured by setting the LOG_ARCHIVE_DEST_n parameter to acharacter string that includes one or more attributes. This section brieflydescribes the most commonly used attributes. See Chapter 15 for a fulldescription of all LOG_ARCHIVE_DEST_n parameter attributes.
The SERVICE attribute, which is a mandatory attribute for a redo transportdestination, must be the first attribute specified in the attribute list. TheSERVICE attribute is used to specify the Oracle Net service name used toconnect to the redo transport destination. The service name must be resolvablethrough an Oracle Net naming method to an Oracle Net connect descriptor thatmatches the Oracle Net listener(s) at the redo transport destination. Theconnect descriptor must specify that a dedicated server connection be used,unless that is the default connection type for the redo transport destination.
See Also:
Oracle Database Net ServicesAdministrator's Guide forinformation about Oracle Net service names, connect descriptors, listeners, andnetwork security
The SYNC attribute is used to specify that thesynchronous redo transport mode be used to send redo data to a redo transportdestination.
The ASYNC attribute is used to specify that theasynchronous redo transport mode be used to send redo data to a redo transportdestination. The asynchronous redo transport mode will be used if neitherthe SYNC nor the ASYNC attribute is specified.
The NET_TIMEOUT attribute is used to specify how long the LGWR process will block waiting for anacknowledgement that redo data has been successfully received by a destinationthat uses the synchronous redo transport mode. If an acknowledgement is notreceived within NET_TIMEOUT seconds, the redo transportconnection is terminated and an error is logged.
Oracle recommends thatthe NET_TIMEOUT attribute bespecified whenever the synchronous redo transport mode is used, so that themaximum duration of a redo source database stall caused by a redo transportfault can be precisely controlled. See Section 6.4.2 for informationabout monitoring synchronous redo transport mode response time.
The AFFIRM attributeis used to specify that redo received from a redo source database is notacknowledged until it has been written to the standby redo log. The NOAFFIRM attribute is used to specify that received redois acknowledged without waiting for received redo to be written to the standbyredo log.
The DB_UNIQUE_NAME attribute is used to specify the DB_UNIQUE_NAME of a redo transportdestination. The DB_UNIQUE_NAME attribute must be specified iftheLOG_ARCHIVE_CONFIG database initializationparameter has been defined and its value includes a DG_CONFIG list.
If the DB_UNIQUE_NAME attribute is specified, itsvalue must match one of the DB_UNIQUE_NAME values in the DG_CONFIG list. It must also match the value of theDB_UNIQUE_NAME database initializationparameter at the redo transport destination. If either match fails, an error islogged and redo transport will not be possible to that destination.
The VALID_FOR attribute is used to specify when redo transport servicestransmits redo data to a redo transport destination. Oracle recommends that theVALID_FOR attribute be specified for eachredo transport destination at every site in a Data Guard configuration so thatredo transport services will continue to send redo data to all standbydatabases after a role transition, regardless of which standby database assumesthe primary role.
The REOPEN attribute is used to specify the minimum numberof seconds between automatic reconnect attempts to a redo transport destinationthat is inactive because of a previous error.
The COMPRESSION attribute is used to specify that redo data is transmitted to aredo transport destination in compressed form. Redo transport compression cansignificantly improve redo transport performance on network links with lowbandwidth and high latency.
Redo transport compression is afeature of the Oracle Advanced Compression option. You must purchase a licensefor this option before using the redo transport compression feature.
The following example uses all of the LOG_ARCHIVE_DEST_n attributes described in thissection. A DB_UNIQUE_NAME has been specified for bothdestinations, as has the use of compression. If a redo transport fault occursat either destination, redo transport will attempt to reconnect to thatdestination, but not more frequently than once every 60 seconds.
DB_UNIQUE_NAME=BOSTON
LOG_ARCHIVE_CONFIG='DG_CONFIG=(BOSTON,CHICAGO,HARTFORD)'
LOG_ARCHIVE_DEST_2='SERVICE=CHICAGOASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILE,
PRIMARY_ROLE)REOPEN=60 COMPRESSION=ENABLE DB_UNIQUE_NAME=CHICAGO'
LOG_ARCHIVE_DEST_STATE_2='ENABLE'
LOG_ARCHIVE_DEST_3='SERVICE=HARTFORDSYNC AFFIRM NET_TIMEOUT=30
VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE)REOPEN=60 COMPRESSION=ENABLE
DB_UNIQUE_NAME=HARTFORD'
LOG_ARCHIVE_DEST_STATE_3='ENABLE'