Creating a Physical Standby Database 11g

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.

SBYDB5028

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.