创建10gR2最大保护模式DataGuard
一、设置主库归档
设置主库为force logging
SQL> alter database force logging;
设置主库为归档模式：
SQL> archive log list
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> archive log list
为主数据库添加"备用联机日志文件"，这里要保证备日志文件与主库联机日志文件相同大小。
添加备用日志文件是规则：
备用日志最少应该比redo log 多一个。推荐的备重做日志数依赖于主数据库上的线程数。
(每线程日志文件最大数目 + 1 ) * 线程数
alter database add standby logfile
group 4 ('/data/oradata/std_redo04a.log','/data/oradata/std_redo04b.log') size 50m,
group 5 ('/data/oradata/std_redo05a.log','/data/oradata/std_redo05b.log') size 50m,
group 6 ('/data/oradata/std_redo06a.log','/data/oradata/std_redo06b.log') size 50m,
group 7 ('/data/oradata/std_redo07a.log','/data/oradata/std_redo08b.dbf') size 50m;
二、修改主库参数文件
SQL> create pfile from spfile;
主库initorcl.ora
======================
*.audit_file_dest='/opt/oracle/admin/orcl/adump'
*.background_dump_dest='/opt/oracle/admin/orcl/bdump'
*.core_dump_dest='/opt/oracle/admin/orcl/cdump'
*.user_dump_dest='/opt/oracle/admin/orcl/udump'
*.compatible='10.2.0.1.0'
*.control_files='/data/oradata/control01.ctl','/data/oradata/control02.ctl','/data/oradata/control03.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.job_queue_processes=10
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=167772160
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
#修改的部分
*.DB_UNIQUE_NAME=orcl
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,bforcl)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/data/archive/ MANDATORY REOPEN=60 MAX_FAILURE=3 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl'
*.LOG_ARCHIVE_DEST_2='SERVICE=bforcl LGWR SYNC AFFIRM VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=bforcl'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
*.LOG_ARCHIVE_FORMAT=orcl_%t_%s_%r.dbf
*.#以下为了角色切换设置
*.FAL_CLIENT = orcl
*.FAL_SERVER = bforcl
*.STANDBY_FILE_MANAGEMENT =AUTO
~> mv $ORACLE_HOME/dbs/spfileorcl.ora $ORACLE_HOME/dbs/spfileorcl.ora.bak
~> sqlplus "/as sysdba"
SQL> create spfile from pfile;
三、配置主数据库listener及tnsnames
主库listener.ora
=======================
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME = /opt/oracle/10.2.0.1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.142)(PORT = 1521))
)
)
)
tnsnames.ora
=============
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.142)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
BFORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.143)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = bforcl)
)
)
生成备用库控制文件
alter database create standby controlfile as '/tmp/standby_ctl01.ctl';
四、复制文件到备机
~> scp /data/oradata/*
dbs> scp initorcl.ora :` pwd `
admin> scp listener.ora tnsnames.ora :` pwd `
tmp> scp standby_ctl01.ctl
生成备用库密码文件(保证SYS密码与主库相同)
orapwd file=orapwbforcl.ora password=admin entries=10
多重备用库控制文件
cp standby_ctl01.ctl standby_ctl02.ctl
mkdir -p /data/{oradata,archvie}
mkdir -p /opt/oracle/admin/bforcl/{adump,bdump,cdump,pfile,udump}
五、修改备用库参数文件及listener
*.audit_file_dest='/opt/oracle/admin/bforcl/adump'
*.background_dump_dest='/opt/oracle/admin/bforcl/bdump'
*.core_dump_dest='/opt/oracle/admin/bforcl/cdump'
*.user_dump_dest='/opt/oracle/admin/bforcl/udump'
#修改的部分
*.control_file=('/data/oradata/standby_ctl01.ctl','/data/oradata/standby_ctl02.ctl')
*.DB_UNIQUE_NAME=bforcl
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,bforcl)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/data/archive/ MANDATORY REOPEN=60 MAX_FAILURE=3 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl'
*.LOG_ARCHIVE_DEST_2='SERVICE=orcl LGWR SYNC AFFIRM VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
*.LOG_ARCHIVE_FORMAT=bforcl_%t_%s_%r.dbf
*.FAL_CLIENT = bforcl
*.FAL_SERVER = orcl
*.STANDBY_FILE_MANAGEMENT =AUTO #此参数保证主库上对表空间/数据文件的操作会自动应用到备用库上。
备库listener.ora
=======================
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = bforcl)
(ORACLE_HOME = /opt/oracle/10.2.0.1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.143)(PORT = 1521))
)
)
)
tnsnames.ora
=============
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.142)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
BFORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.143)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = bforcl)
)
)
六、测试主备机之间网络联通性
tnsping orcl
tnsping bforcl
tnsping并不能保证可以互相登陆。
这里要确保sqlplus 可以互相登陆对方数据库。
七、打开备用库到mount状态
查看备用库是否包含备用日志。如果不包含备用日志文件，不能运行在"最大保护"或"最大可用性"模式。
SQL> select * from v$logfile;
SQL> select * from v$standby_log;
正式运行中v$standy_log中的某一个日志文件同步接收主库LGWR进程传送过来的重做条目。
八、打开主库，修改主库DataGuard保护模式
SQL> startup mount
SQL> select name,db_unique_name,protection_mode from v$database;
NAME DB_UNIQUE_NAME PROTECTION_MODE
----- --------------- --------------------
ORCL orcl MAXIMUM PERFORMANCE
SQL> alter database set standby database to maximize protection;
打开主库到OPEN状态，监控alert日志文件，查看是否配置成功。
切换主库保护模式的语法：
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE {PROTECTION | AVAILABILITY | PERFORMANCE }
九、打开备用库恢复进程
recover managed standby database diconnect from session;
执行上面这条语句，备用库会在主库日志文件切换(备用日志文件切换)归档后，使用归档文件恢复数据库。
recover managed standby database using current logfile disconnect from session;
这条语句与上面不同的是，备用日志文件切换，生成归档日志前，先恢复数据库。
recover managed standby database finish;
这条语句是在做切换时，尽量多的保护数据。从备用日志文件中恢复数据
recover managed standby database cancel;
取消备用库自动恢复
可以在使用上面两条语句时，查看v$managed_standby的不同。
SQL> recover managed standby database disconnect;
Media recovery complete.
SQL> select process,status from v$managed_standby;
PROCESS STATUS
--------- ------------
ARCH CLOSING
ARCH CLOSING
MRP0 WAIT_FOR_LOG
RFS IDLE
RFS IDLE
RFS IDLE
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.
SQL> select process,status from v$managed_standby;
PROCESS STATUS
--------- ------------
ARCH CLOSING
ARCH CLOSING
MRP0 APPLYING_LOG
RFS IDLE
RFS IDLE
RFS IDLE
在恢复过程中，可以查看v$standby_log,此视图中会有日志文件与生产机的当前日志文件是同步的。
十、需要注意
最大保护模式下，至少要有一个备用库的设置满足LOG_ARCHIVE_DEST_2='SERVICE=bforcl LGWR SYNC AFFIRM'并且，主库设置为MAXIMUM PROTECTION模式。
最大保护模式下，备用库还是会等待备用库切换日志后归档时或归档前才会恢复备用系统。而不是主数据库的操作适时反映在备用库。适时的仅仅是日志文件合而已。这一点要注意。
在主备库切换的时候可以执行recover managed standby database finish应用适时同步过来的日志信息，这样可以最大限度的保证数据。
测试：
1.在主库打开情况下关闭备库：
oracle 10g R2中，运行在LGWR最大保护模式下的备库，主库没有关闭的情况下不允许关闭备库。
也就是说，每当关闭数据库时，必须先关闭主库才能关闭备用库。
打开数据库情况刚好相反。备库没有MOUNT而直接打开主库是不允许的。在数据库打开阶段会报错
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 71305460 bytes
Database Buffers 92274688 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
alter中错误信息见下："错误"
打开数据库必须是先mount备用库，才能打开主数据库。
SQL> shutdown immediate
ORA-01154: database busy. Open, close, mount, and dismount not allowed now
alter日志中信息：
Completed: ALTER DATABASE RECOVER managed standby database cancel
Wed Jul 4 06:47:40 2007
Attempt to shut down Standby Database
Standby Database operating in NO DATA LOSS mode
Detected primary database alive, shutdown primary first, shutdown aborted
2.网络中断
测试断开备用机网络
$ifdown eth0
在主数据库做操作
SQL> create table t (id number);
操作一直处于挂起状态。
查看主数据库alter日志显示如下错误，
如果网络断开，主库LGWR进程会一直挂起一直探测是否可以传输数据到备用机，直到备用机网络再次连接。
主数据库alter错误信息：
Thu Jul 5 14:52:22 2007
ORA-16198: LGWR received timedout error from KSR
LGWR: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (16198)
ORA-16198: LGWR received timedout error from KSR
LGWR: Error 16198 disconnecting from destination LOG_ARCHIVE_DEST_2 standby host 'bforcl'
LNSb started with pid=16, OS id=12830
Error 12560 received logging on to the standby
Thu Jul 5 14:52:52 2007
LGWR: Error 12560 attaching to RFS for reconnect
LNSb started with pid=16, OS id=12834
Error 12560 received logging on to the standby
Thu Jul 5 14:53:17 2007
LGWR: Error 12560 attaching to RFS for reconnect
LNSb started with pid=16, OS id=12836
Error 12560 received logging on to the standby
Thu Jul 5 14:53:42 2007
LGWR: Error 12560 attaching to RFS for reconnect
LNSb started with pid=16, OS id=12838
Error 12560 received logging on to the standby
......
Thu Jul 5 14:56:13 2007
LGWR: Error 12560 attaching to RFS for reconnect
LNSb started with pid=16, OS id=12863
Thu Jul 5 14:56:33 2007
LGWR: RFS network connection re-established at host 'bforcl'
LGWR: RFS destination opened for reconnect at host 'bforcl'
附：
打开数据库时的错误信息：
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
主库alter日志错误：
alter database open
Wed Jul 4 04:07:55 2007
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=14, OS id=7898
Wed Jul 4 04:07:57 2007
ARC0: Archival started
ARC1: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
LGWR: Primary database is in MAXIMUM PROTECTION mode
Wed Jul 4 04:07:57 2007
Destination LOG_ARCHIVE_DEST_2 is SYNCHRONIZED
LGWR: Destination LOG_ARCHIVE_DEST_1 is not serviced by LGWR
LNSb started with pid=16, OS id=7902
ARC1 started with pid=15, OS id=7900
Wed Jul 4 04:08:05 2007
Errors in file /opt/oracle/admin/orcl/bdump/orcl_lgwr_7882.trc:
ORA-16086: standby database does not contain available standby log files
Wed Jul 4 04:08:05 2007
LGWR: Error 16086 verifying archivelog destination LOG_ARCHIVE_DEST_2
Wed Jul 4 04:08:05 2007
Destination LOG_ARCHIVE_DEST_2 is UNSYNCHRONIZED
LGWR: Error 16086 disconnecting from destination LOG_ARCHIVE_DEST_2 standby host 'bforcl'
LGWR: Continuing...
LGWR: Minimum of 1 applicable standby database required
Wed Jul 4 04:08:09 2007
Errors in file /opt/oracle/admin/orcl/bdump/orcl_lgwr_7882.trc:
ORA-16072: a minimum of one standby database destination is required
Wed Jul 4 04:08:09 2007
Errors in file /opt/oracle/admin/orcl/bdump/orcl_lgwr_7882.trc:
ORA-16072: a minimum of one standby database destination is required
LGWR: terminating instance due to error 16072
Instance terminated by LGWR, pid = 7882
备库alter错误：
Primary database is in MAXIMUM PROTECTION mode
Changing standby controlfile to MAXIMUM PROTECTION mode
RFS[4]: No standby redo logfiles selected (reason:1)
Wed Jul 4 04:02:58 2007
Errors in file /opt/oracle/admin/bforcl/udump/bforcl_rfs_9362.trc:
ORA-16086: standby database does not contain available standby log files
Wed Jul 4 04:09:51 2007
