系统：Red Hat Enterprise Linux 6.4
数据库：Oracle 10.2.0.5.0 Patch Set 4
主机：10dg1 192.168.1.91
10dg2192.168.1.92
内存：1G
虚拟机：VBox 4.2.14
一，操作系统部分(略)
能够參考我之前写的博客 http://blog.csdn.net/aaron8219/article/details/38239065
二，数据库部分
*****主库配置*****
1. 开启归档，force logging SQL> archive log list SQL> shutdown immediate SQL> startup mount SQL> alter database archivelog; SQL> shutdown immediate SQL> startup SQL> alter database force logging; SQL> select log_mode,force_logging from v$database; SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 1 Next log sequence to archive 3 Current log sequence 3 2. 在线改动DG所需參数 SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(zlm,zlmdg)' scope=both; SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=zlm' scope=both; SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=zlm10gdg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=zlmdg' scope=both; SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both; SQL> alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both; SQL> alter system set FAL_SERVER=zlmdg scope=both; SQL> alter system set FAL_CLIENT=zlm scope=both; SQL> alter system set STANDBY_FILE_MANAGEMENT='AUTO' scope=both;
这里仅仅做2个说明：
①. SERVICE相应的是NET SERVICE NAME，而不是tnsnames.ora中配置的SERVICE NAME
②. 因为db_unique_name无法在线改动，如果生产库之前就设置过这个參数，一般会和db_name一致，那么最好了，能够直接使用它，这样就不用停库，我这里如果原来的db_unique_name设置过，而且就是和db_name一样的zlm，
能够说，这是零停机窗体搭建DG环境的必要条件
3. 创建备份文件夹，開始备份主库，准备在备库上做rman异机恢复 mkdir /u01/rman_bak run{ allocate channel c1 device type disk; allocate channel c2 device type disk; backup incremental level=0 database format '/u01/rman_bak/full_lev0_%d_%I_%T_%U.bak' tag 'full_level_0'include current controlfile for standby; release channel c1; release channel c2; } 查看刚才备份后生成的文件 [oracle@10dg1 u01]$ cd rman_bak/ [oracle@10dg1 rman_bak]$ ll total 608976 -rw-r-----. 1 oracle oinstall 383500288 Jul 30 13:22 full_lev0_ZLM_3930348306_20140730_04pemmkd_1_1.bak -rw-r-----. 1 oracle oinstall 232873984 Jul 30 13:22 full_lev0_ZLM_3930348306_20140730_05pemmkd_1_1.bak -rw-r-----. 1 oracle oinstall 7110656 Jul 30 13:23 full_lev0_ZLM_3930348306_20140730_06pemmlq_1_1.bak -rw-r-----. 1 oracle oinstall 98304 Jul 30 13:23 full_lev0_ZLM_3930348306_20140730_07pemmlr_1_1.bak 4. 复制初始化參数initSID.ora和备份集到备库$ORACLE_HOME/dbs scp $ORACLE_HOME/dbs/initzlm.ora 10dg2:$ORACLE_HOME/dbs scp /u01/rman_bak/*.bak 10dg2:/u01/rman_bak --注意必须先在备库主机上创建/u01/rman_bak文件夹
5. 复制tnsnames.ora和listener.ora到备库$ORACLE_HOME/dbs
这里贴一下我主库这2个文件的配置，推荐直接改动成备库的内容以后再复制，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.ZLM10G = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.91)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = zlm_SN) ) )ZLM10GDG = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.92)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = zlm_SN1) ) ) # listener.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora # Generated by Oracle configuration tools. SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1) (PROGRAM = extproc) ) (SID_DESC = (GLOBAL_DBNAME = zlm_SN) (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1) (SID_NAME = zlm) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.91)(PORT = 1521)) ) (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0)) ) )
还记得怎么区分GLOBAL_DBNAME，SID_NAME，SERVICE_NAME，NET SERVICE NAME这些概念吗？请參考我的还有一篇博客 http://blog.csdn.net/aaron8219/article/details/38267145
-------------------------------------------------切割线 -----------------------------------------------------
*****备库配置*****
1. 创建初始化參数中必要的文件夹
[oracle@10dg2 ~]$ mkdir /u01/app/oracle/admin/zlm/adump -p [oracle@10dg2 ~]$ mkdir /u01/app/oracle/admin/zlm/bdump -p [oracle@10dg2 ~]$ mkdir /u01/app/oracle/admin/zlm/cdump -p [oracle@10dg2 ~]$ mkdir /u01/app/oracle/admin/zlm/udump -p [oracle@10dg2 ~]$ mkdir /u01/app/oracle/oradata/zlm -p [oracle@10dg2 ~]$ mkdir /u01/app/oracle/flash_recovery_area -p sqlplus / as sysdba SQL> startup nomountSQL> exit [oracle@10dg2 ~]$ ls -lrt /u01/rman_bak total 608976 -rw-r-----. 1 oracle oinstall 383500288 Jul 30 13:39 full_lev0_ZLM_3930348306_20140730_04pemmkd_1_1.bak -rw-r-----. 1 oracle oinstall 232873984 Jul 30 13:40 full_lev0_ZLM_3930348306_20140730_05pemmkd_1_1.bak -rw-r-----. 1 oracle oinstall 7110656 Jul 30 13:40 full_lev0_ZLM_3930348306_20140730_06pemmlq_1_1.bak -rw-r-----. 1 oracle oinstall 98304 Jul 30 13:40 full_lev0_ZLM_3930348306_20140730_07pemmlr_1_1.bak [oracle@10dg2 ~]$ rman target /
2. 依次用rman恢复备库控制文件，数据文件
RMAN> restore controlfile from'/u01/rman_bak/full_lev0_ZLM_3930348306_20140730_06pemmlq_1_1.bak'; Starting restore at 30-JUL-14 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=156 devtype=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:03output filename=/u01/app/oracle/oradata/zlm/control01.ctl output filename=/u01/app/oracle/oradata/zlm/control02.ctl output filename=/u01/app/oracle/oradata/zlm/control03.ctl Finished restore at 30-JUL-14 RMAN> alter database mount; database mounted released channel: ORA_DISK_1 RMAN> restore database; Starting restore at 30-JUL-14 Starting implicit crosscheck backup at 30-JUL-14 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=156 devtype=DISK Crosschecked 2 objects Finished implicit crosscheck backup at 30-JUL-14 Starting implicit crosscheck copy at 30-JUL-14 using channel ORA_DISK_1 Finished implicit crosscheck copy at 30-JUL-14 searching for all files in the recovery area cataloging files... no files cataloged using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile backupset restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set restoring datafile 00002 to /u01/app/oracle/oradata/zlm/undotbs01.dbf restoring datafile 00003 to /u01/app/oracle/oradata/zlm/sysaux01.dbf restoring datafile 00005 to /u01/app/oracle/oradata/zlm/example01.dbf channel ORA_DISK_1: reading from backup piece /u01/rman_bak/full_lev0_ZLM_3930348306_20140730_05pemmkd_1_1.bak channel ORA_DISK_1: restored backup piece 1 piece handle=/u01/rman_bak/full_lev0_ZLM_3930348306_20140730_05pemmkd_1_1.bak tag=FULL_LEVEL_0 channel ORA_DISK_1: restore complete, elapsed time: 00:00:25 channel ORA_DISK_1: starting datafile backupset restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set restoring datafile 00001 to /u01/app/oracle/oradata/zlm/system01.dbf restoring datafile 00004 to /u01/app/oracle/oradata/zlm/users01.dbf channel ORA_DISK_1: reading from backup piece /u01/rman_bak/full_lev0_ZLM_3930348306_20140730_04pemmkd_1_1.bak channel ORA_DISK_1: restored backup piece 1 piece handle=/u01/rman_bak/full_lev0_ZLM_3930348306_20140730_04pemmkd_1_1.bak tag=FULL_LEVEL_0 channel ORA_DISK_1: restore complete, elapsed time: 00:00:25 Finished restore at 30-JUL-14 RMAN>
--检查是否恢复完毕
[oracle@10dg2 ~]$ cd /u01/app/oracle/oradata/zlm [oracle@10dg2 zlm]$ ll total 865540 -rw-r-----. 1 oracle oinstall 7061504 Jul 30 13:52 control01.ctl -rw-r-----. 1 oracle oinstall 7061504 Jul 30 13:52 control02.ctl -rw-r-----. 1 oracle oinstall 7061504 Jul 30 13:52 control03.ctl -rw-r-----. 1 oracle oinstall 104865792 Jul 30 13:47 example01.dbf -rw-r-----. 1 oracle oinstall 262152192 Jul 30 13:47 sysaux01.dbf -rw-r-----. 1 oracle oinstall 461381632 Jul 30 13:47 system01.dbf -rw-r-----. 1 oracle oinstall 31465472 Jul 30 13:47 undotbs01.dbf -rw-r-----. 1 oracle oinstall 5251072 Jul 30 13:47 users01.dbf
--查看备库归档情况(此时和主库一致，由于刚从主库恢复过来)
SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 1 Next log sequence to archive 3 Current log sequence 3
3. 启动监听
--主库
[oracle@10dg1 ~]$ lsnrctl start ... Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.91)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0))) Services Summary... Service "PLSExtProc" has 1 instance(s). Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service... Service "zlm_SN" has 1 instance(s). Instance "zlm", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully --备库[oracle@10dg2 ~]$ lsnrctl start
... Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.92)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0))) Services Summary... Service "PLSExtProc" has 1 instance(s). Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service... Service "zlm_SN1" has 1 instance(s). Instance "zlm", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully 4. 调整备库的參数(基本就是主库參反过来设置)
db_unique_name='ZLMDG'
fal_client='ZLMDG' fal_server='ZLM' log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=zlmdg' log_archive_dest_2='SERVICE=zlm10g LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=zlm'5. 查看归档路径状态
--主库
SQL> set lin 120 pages 120
SQL> col dest_name for a20 SQL> col destination for a15 SQL> col error for a40 SQL> select dest_id,dest_name,destination,status,error from v$archive_dest_status; DEST_ID DEST_NAME DESTINATION STATUS ERROR ---------- -------------------- --------------- --------- ---------------------------------------- 1 LOG_ARCHIVE_DEST_1 VALID 2 LOG_ARCHIVE_DEST_2 zlm10gdg ERROR ORA-01031: insufficient privileges 3 LOG_ARCHIVE_DEST_3 INACTIVE 4 LOG_ARCHIVE_DEST_4 INACTIVE 5 LOG_ARCHIVE_DEST_5 INACTIVE 6 LOG_ARCHIVE_DEST_6 INACTIVE 7 LOG_ARCHIVE_DEST_7 INACTIVE 8 LOG_ARCHIVE_DEST_8 INACTIVE 9 LOG_ARCHIVE_DEST_9 INACTIVE 10 LOG_ARCHIVE_DEST_10 INACTIVE 10 rows selected.
注意，DESTINATION写的一定是tnsnames.ora中开头的那个大写名称，即NET SERVICE NAME(ZLM10GDG)
--备库
SQL> set lin 120 pages 120 SQL> col dest_name for a20 SQL> col destination for a15 SQL> col error for a40 SQL> select dest_id,dest_name,destination,status,error from v$archive_dest_status; DEST_ID DEST_NAME DESTINATION STATUS ERROR ---------- -------------------- --------------- --------- ---------- 1 LOG_ARCHIVE_DEST_1 VALID 2 LOG_ARCHIVE_DEST_2 zlm10g VALID 3 LOG_ARCHIVE_DEST_3 INACTIVE 4 LOG_ARCHIVE_DEST_4 INACTIVE 5 LOG_ARCHIVE_DEST_5 INACTIVE 6 LOG_ARCHIVE_DEST_6 INACTIVE 7 LOG_ARCHIVE_DEST_7 INACTIVE 8 LOG_ARCHIVE_DEST_8 INACTIVE 9 LOG_ARCHIVE_DEST_9 INACTIVE 10 LOG_ARCHIVE_DEST_10 INACTIVE 11 STANDBY_ARCHIVE_DEST VALID 11 rows selected. --备库远程归档正常，主库报一个权限不足，于是考虑又一次复制主库password到备库
SQL> !scp $ORACLE_HOME/dbs/orapwzlm 10dg2:$ORACLE_HOME/dbs
oracle@10dg2's password: orapwzlm 100% 1536 1.5KB/s 00:00--主库tnsping一下备库
[oracle@10dg1 rman_bak]$ tnsping zlm10gdg --NET SERVICE NAME TNS Ping Utility for Linux: Version 10.2.0.5.0 - Production on 30-JUL-2014 14:50:45 Copyright (c) 1997, 2010, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.92)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = zlm_SN1)))OK (0 msec)
--备库tnsping一下主库
[oracle@10dg2 zlm]$ tnsping zlm10g --NET SERVICE NAME TNS Ping Utility for Linux: Version 10.2.0.5.0 - Production on 30-JUL-2014 14:50:56 Copyright (c) 1997, 2010, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.91)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = zlm_SN)))OK (10 msec)
--再次查看主库归档路径状态
SQL> select dest_id,dest_name,destination,status,error from v$archive_dest_status; DEST_ID DEST_NAME DESTINATION STATUS ERROR ---------- -------------------- --------------- --------- ---------------------------------------- 1 LOG_ARCHIVE_DEST_1 VALID 2 LOG_ARCHIVE_DEST_2 zlm10gdg VALID 3 LOG_ARCHIVE_DEST_3 INACTIVE 4 LOG_ARCHIVE_DEST_4 INACTIVE 5 LOG_ARCHIVE_DEST_5 INACTIVE 6 LOG_ARCHIVE_DEST_6 INACTIVE 7 LOG_ARCHIVE_DEST_7 INACTIVE 8 LOG_ARCHIVE_DEST_8 INACTIVE 9 LOG_ARCHIVE_DEST_9 INACTIVE 10 LOG_ARCHIVE_DEST_10 INACTIVE 10 rows selected.
主库远程归档路径正常了，切记备库開始应用redo log前，先查看这个条件是否满足
6. 主备库创建standby redo logfile(假设仅仅採用arch方式归档，这步能够省略，推荐用lgwr方式)
SQL> alter database add standby logfile group 4 ('/u01/app/oracle/oradata/zlm/std04.redo') size 100m; SQL> alter database add standby logfile group 5 ('/u01/app/oracle/oradata/zlm/std05.redo') size 100m; SQL> alter database add standby logfile group 6 ('/u01/app/oracle/oradata/zlm/std06.redo') size 100m; SQL> alter database add standby logfile group 7 ('/u01/app/oracle/oradata/zlm/std07.redo') size 100m;
主库创建的时候没问题，备库创建到第2个文件的时候报错
SQL> alter database add standby logfile group 4 ('/u01/app/oracle/oradata/zlm/std04.redo') size 100m; Database altered. SQL> alter database add standby logfile group 5 ('/u01/app/oracle/oradata/zlm/std05.redo') size 100m; alter database add standby logfile group 5 ('/u01/app/oracle/oradata/zlm/std05.redo') size 100m * ERROR at line 1: ORA-19502: write error on file "/u01/app/oracle/oradata/zlm/std05.redo", blockno 198657 (blocksize=512) ORA-27072: File I/O error Linux-x86_64 Error: 9: Bad file descriptor --错误的描写叙述符 Additional information: 4 Additional information: 198657 Additional information: 790016
開始还以为是磁盘有损坏，原来是可用空间不够了，初始仅仅给了15G，想想装个10g足够了，Oracle软件和patch的安装文件用完后没及时删除，占用掉不少空间
--再次查看一下是否生成了standby redo logfile
SQL> !ls -lrt /u01/app/oracle/oradata/zlm total 1275164 -rw-r-----. 1 oracle oinstall 31465472 Jul 30 13:47 undotbs01.dbf -rw-r-----. 1 oracle oinstall 104865792 Jul 30 13:47 example01.dbf -rw-r-----. 1 oracle oinstall 262152192 Jul 30 13:47 sysaux01.dbf -rw-r-----. 1 oracle oinstall 5251072 Jul 30 13:47 users01.dbf -rw-r-----. 1 oracle oinstall 461381632 Jul 30 13:47 system01.dbf -rw-r-----. 1 oracle oinstall 104858112 Jul 30 15:07 std04.redo -rw-r-----. 1 oracle oinstall 104858112 Jul 30 15:25 std05.redo -rw-r-----. 1 oracle oinstall 104858112 Jul 30 15:25 std06.redo -rw-r-----. 1 oracle oinstall 104858112 Jul 30 15:25 std07.redo -rw-r-----. 1 oracle oinstall 7061504 Jul 30 15:25 control01.ctl -rw-r-----. 1 oracle oinstall 7061504 Jul 30 15:25 control02.ctl -rw-r-----. 1 oracle oinstall 7061504 Jul 30 15:25 control03.ctl
7. 备库開始redo apply
SQL> recover managed standby database using current logfile disconnect from session; Media recovery complete.
--查看备库数据文件文件夹，发现并未生成redo01.log,redo02.log,redo03.log这3个文件
SQL> !ls -lrt /u01/app/oracle/oradata/zlm total 1275164 -rw-r-----. 1 oracle oinstall 104858112 Jul 30 15:07 std04.redo -rw-r-----. 1 oracle oinstall 104858112 Jul 30 15:25 std05.redo -rw-r-----. 1 oracle oinstall 104858112 Jul 30 15:25 std06.redo -rw-r-----. 1 oracle oinstall 104858112 Jul 30 15:25 std07.redo -rw-r-----. 1 oracle oinstall 461381632 Jul 30 15:27 system01.dbf -rw-r-----. 1 oracle oinstall 31465472 Jul 30 15:27 undotbs01.dbf -rw-r-----. 1 oracle oinstall 262152192 Jul 30 15:27 sysaux01.dbf -rw-r-----. 1 oracle oinstall 5251072 Jul 30 15:27 users01.dbf -rw-r-----. 1 oracle oinstall 104865792 Jul 30 15:27 example01.dbf -rw-r-----. 1 oracle oinstall 7061504 Jul 30 15:27 control01.ctl -rw-r-----. 1 oracle oinstall 7061504 Jul 30 15:27 control02.ctl -rw-r-----. 1 oracle oinstall 7061504 Jul 30 15:27 control03.ctl
--查看备库alertlog，提示无法得到文件状态，找不到该文件
ALTER DATABASE RECOVER managed standby database using current logfile disconnect from session Wed Jul 30 15:27:18 CST 2014 Attempt to start background Managed Standby Recovery process (zlm) MRP0 started with pid=19, OS id=16199 Wed Jul 30 15:27:18 CST 2014 MRP0: Background Managed Standby Recovery process started (zlm) Managed Standby Recovery starting Real Time Apply parallel recovery started with 2 processes Wed Jul 30 15:27:24 CST 2014 Waiting for all non-current ORLs to be archived... Wed Jul 30 15:27:24 CST 2014Errors in file /u01/app/oracle/admin/zlm/bdump/zlm_mrp0_16199.trc: ORA-00313: open failed for members of log group 1 of thread 1 ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/zlm/redo01.log'ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 Wed Jul 30 15:27:24 CST 2014 Errors in file /u01/app/oracle/admin/zlm/bdump/zlm_mrp0_16199.trc: ORA-00313: open failed for members of log group 1 of thread 1 ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/zlm/redo01.log' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 Clearing online redo logfile 1 /u01/app/oracle/oradata/zlm/redo01.log Clearing online log 1 of thread 1 sequence number 1 Wed Jul 30 15:27:24 CST 2014 Errors in file /u01/app/oracle/admin/zlm/bdump/zlm_mrp0_16199.trc: ORA-00313: open failed for members of log group 1 of thread 1 ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/zlm/redo01.log' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 Wed Jul 30 15:27:24 CST 2014 Errors in file /u01/app/oracle/admin/zlm/bdump/zlm_mrp0_16199.trc:ORA-19527: physical standby redo log must be renamed ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/zlm/redo01.log'Clearing online redo logfile 1 complete --尽管提示complete，但并未成功，也就是说，没有生成对应文件 Media Recovery Waiting for thread 1 sequence 3 Wed Jul 30 15:27:24 CST 2014 Completed: ALTER DATABASE RECOVER managed standby database using current logfile disconnect from session
在之前的文章中提到过 http://blog.csdn.net/aaron8219/article/details/37902793，对于10g，须要要设置一个參数，那就是log_file_name_convert，哪怕主备库的路径全然一样，并且因为这个參数无法在线改动，必须重新启动数据库才干生效，也就是要停库。因为创建过standby redo logfile，还是能够应用日志的，对DG配置没有影响，之后又做了个測试，发现主库和备库做一次正常的switchover并又一次open后，会自己主动生成和原主库一致的3个online redo logfile，即redo01.log,redo02.log,redo03.log，既然备库switchover成主库后，能够正常生成online redo logfile，那么就能够考虑不设置log_file_name_convert參数了，但前提必须是主备库的路径确实是一致的，否则这个參数还是不能少
通过查看备库switchover成主库以后跟踪到的日志得到这一结论
Thu Jul 31 12:58:42 CST 2014 alter database commit to switchover to primary Thu Jul 31 12:58:42 CST 2014 ALTER DATABASE SWITCHOVER TO PRIMARY (zlm) Thu Jul 31 12:58:42 CST 2014 If media recovery active, switchover will wait 900 seconds SwitchOver after complete recovery through change 492782 Thu Jul 31 12:58:42 CST 2014 Errors in file /u01/app/oracle/admin/zlm/udump/zlm_ora_4548.trc: ORA-00313: open failed for members of log group 1 of thread 1 ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/zlm/redo01.log'ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 Thu Jul 31 12:58:43 CST 2014 Errors in file /u01/app/oracle/admin/zlm/udump/zlm_ora_4548.trc: ORA-00313: open failed for members of log group 2 of thread 1 ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/zlm/redo02.log'ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 Thu Jul 31 12:58:44 CST 2014 Errors in file /u01/app/oracle/admin/zlm/udump/zlm_ora_4548.trc: ORA-00313: open failed for members of log group 3 of thread 1 ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/zlm/redo03.log'ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 Standby became primary SCN: 492780 Converting standby mount to primary mount. Thu Jul 31 12:58:46 CST 2014 Switchover: Complete - Database mounted as primary (zlm) Thu Jul 31 12:58:46 CST 2014 ARC1: STARTING ARCH PROCESSES Thu Jul 31 12:58:46 CST 2014 Completed: alter database commit to switchover to primary Thu Jul 31 12:58:46 CST 2014 ARC2: Archival started ARC1: STARTING ARCH PROCESSES COMPLETE ARC2 started with pid=18, OS id=4703 Thu Jul 31 12:59:24 CST 2014 ARC0: Becoming the 'no SRL' ARCH Thu Jul 31 13:01:03 CST 2014alter database open Thu Jul 31 13:01:03 CST 2014 Assigning activation ID 3930494507 (0xea46962b) LNS1 started with pid=19, OS id=4724 Thu Jul 31 13:01:06 CST 2014 Thread 1 advanced to log sequence 14 (thread open) Thu Jul 31 13:01:06 CST 2014 ****************************************************************** LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2 ****************************************************************** Thu Jul 31 13:01:06 CST 2014 Thread 1 opened at log sequence 14 Current log# 2 seq# 14 mem# 0: /u01/app/oracle/oradata/zlm/redo02.logSuccessful open of redo thread 1
注意看这3个online redo logfile生成的时间，即改动时间，因为当前使用的是redo02.log，随着时间的推移，redo02.log的最后改动时间会不断地添加，直到切换到其它日志文件上
SQL> !ls -lrt /u01/app/oracle/oradata/zlmtotal 1428848-rw-r-----. 1 oracle oinstall 20979712 Jul 31 10:56 temp01.dbf-rw-r-----. 1 oracle oinstall 52429312 Jul 31 13:01 redo03.log-rw-r-----. 1 oracle oinstall 52429312 Jul 31 13:01 redo01.log-rw-r-----. 1 oracle oinstall 5251072 Jul 31 13:01 users01.dbf-rw-r-----. 1 oracle oinstall 104865792 Jul 31 13:01 example01.dbf-rw-r-----. 1 oracle oinstall 104858112 Jul 31 13:01 std04.redo-rw-r-----. 1 oracle oinstall 104858112 Jul 31 13:01 std05.redo-rw-r-----. 1 oracle oinstall 104858112 Jul 31 13:01 std06.redo-rw-r-----. 1 oracle oinstall 104858112 Jul 31 13:01 std07.redo-rw-r-----. 1 oracle oinstall 262152192 Jul 31 13:12 sysaux01.dbf-rw-r-----. 1 oracle oinstall 461381632 Jul 31 13:12 system01.dbf-rw-r-----. 1 oracle oinstall 31465472 Jul 31 13:13 undotbs01.dbf-rw-r-----. 1 oracle oinstall 52429312 Jul 31 13:13 redo02.log-rw-r-----. 1 oracle oinstall 7061504 Jul 31 13:13 control01.ctl-rw-r-----. 1 oracle oinstall 7061504 Jul 31 13:13 control02.ctl-rw-r-----. 1 oracle oinstall 7061504 Jul 31 13:13 control03.ctl
能够看到，reod02.log的最后改动时间变成13:13了
--备库初始状态
SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 1 Next log sequence to archive 0 Current log sequence 3
--主库初始状态
SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 1 Next log sequence to archive 3 Current log sequence 3
--主库切换3次日志
SQL> alter system switch logfile; System altered. SQL> alter system switch logfile; System altered. SQL> alter system switch logfile; System altered.
--备库归档日志状态
SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 4 Next log sequence to archive 0 Current log sequence 6
--查看日志应用情况
SQL> select sequence#,applied from v$archived_log; SEQUENCE# APP ---------- --- 3 YES 4 YES 5 YES
--备库停止redo apply
SQL> recover managed standby disconnect from session;
--主库再切2次归档后查看归档
SQL> alter system switch logfile; System altered. SQL> alter system switch logfile; System altered. SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 7 Next log sequence to archive 9Current log sequence 9
--此时能够看到，备库接收到了2个新切的归档7,8，但未应用
SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 7 Next log sequence to archive 0Current log sequence 9 SQL> select sequence#,applied from v$archived_log; SEQUENCE# APP ---------- --- 3 YES 4 YES 5 YES 6 YES 7 NO 8 NO
--备库再启用redo apply，这次不使用using crrent logfile
SQL> recover managed standby database disconnect from session;
--跟踪备库的alertlog日志
Wed Jul 30 15:47:03 CST 2014 ALTER DATABASE RECOVER managed standby database cancel Wed Jul 30 15:47:03 CST 2014 MRP0: Background Media Recovery cancelled with status 16037 Wed Jul 30 15:47:03 CST 2014 Errors in file /u01/app/oracle/admin/zlm/bdump/zlm_mrp0_16199.trc: ORA-16037: user requested cancel of managed recovery operation Managed Standby Recovery not using Real Time Apply Recovery interrupted! Recovered data files to a consistent state at change 465658 Wed Jul 30 15:47:04 CST 2014 Errors in file /u01/app/oracle/admin/zlm/bdump/zlm_mrp0_16199.trc: ORA-16037: user requested cancel of managed recovery operation Wed Jul 30 15:47:04 CST 2014 MRP0: Background Media Recovery process shutdown (zlm) Wed Jul 30 15:47:04 CST 2014 Managed Standby Recovery Canceled (zlm) Wed Jul 30 15:47:04 CST 2014 Completed: ALTER DATABASE RECOVER managed standby database cancel Wed Jul 30 15:47:13 CST 2014 Primary database is in MAXIMUM PERFORMANCE mode RFS[2]: Successfully opened standby log 4: '/u01/app/oracle/oradata/zlm/std04.redo' Primary database is in MAXIMUM PERFORMANCE mode RFS[2]: Successfully opened standby log 5: '/u01/app/oracle/oradata/zlm/std05.redo' Wed Jul 30 15:47:42 CST 2014 ALTER DATABASE RECOVER managed standby database disconnect from session Wed Jul 30 15:47:42 CST 2014 Attempt to start background Managed Standby Recovery process (zlm) MRP0 started with pid=19, OS id=16248 Wed Jul 30 15:47:42 CST 2014 MRP0: Background Managed Standby Recovery process started (zlm) Managed Standby Recovery not using Real Time Apply parallel recovery started with 2 processes Wed Jul 30 15:47:47 CST 2014 Waiting for all non-current ORLs to be archived... Wed Jul 30 15:47:47 CST 2014 Errors in file /u01/app/oracle/admin/zlm/bdump/zlm_mrp0_16248.trc: ORA-00313: open failed for members of log group 1 of thread 1 ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/zlm/redo01.log'ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 Wed Jul 30 15:47:47 CST 2014 Errors in file /u01/app/oracle/admin/zlm/bdump/zlm_mrp0_16248.trc: ORA-00313: open failed for members of log group 1 of thread 1 ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/zlm/redo01.log' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 Clearing online redo logfile 1 /u01/app/oracle/oradata/zlm/redo01.log Clearing online log 1 of thread 1 sequence number 7 Wed Jul 30 15:47:47 CST 2014 Errors in file /u01/app/oracle/admin/zlm/bdump/zlm_mrp0_16248.trc: ORA-00313: open failed for members of log group 1 of thread 1 ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/zlm/redo01.log' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 Wed Jul 30 15:47:47 CST 2014 Errors in file /u01/app/oracle/admin/zlm/bdump/zlm_mrp0_16248.trc: ORA-19527: physical standby redo log must be renamed ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/zlm/redo01.log' Clearing online redo logfile 1 complete Media Recovery Log /u01/app/oracle/flash_recovery_area/ZLMDG/archivelog/2014_07_30/o1_mf_1_7_9xk8r16v_.arc Media Recovery Log /u01/app/oracle/flash_recovery_area/ZLMDG/archivelog/2014_07_30/o1_mf_1_8_9xk8r1jc_.arc Wed Jul 30 15:47:48 CST 2014 Completed: ALTER DATABASE RECOVER managed standby database disconnect from session Wed Jul 30 15:47:48 CST 2014 Media Recovery Waiting for thread 1 sequence 9 (in transit) 发现online redo logfile还是无法获取，仍然会提示standby redo log须要重命名，可是去能够成功地应用刚才剩下的2个归档7,8 SQL> select sequence#,applied from v$archived_log; SEQUENCE# APP ---------- --- 3 YES 4 YES 5 YES 6 YES 7 YES 8 YES 6 rows selected. 查看一下数据文件存放的文件夹，的确依然没有生成我么所期待的online redo logfile(redo01.log,redo02.log,redo03.log) SQL> !ls -lrt /u01/app/oracle/oradata/zlm total 1275164 -rw-r-----. 1 oracle oinstall 104858112 Jul 30 15:25 std06.redo -rw-r-----. 1 oracle oinstall 104858112 Jul 30 15:25 std07.redo -rw-r-----. 1 oracle oinstall 104858112 Jul 30 15:47 std04.redo -rw-r-----. 1 oracle oinstall 31465472 Jul 30 15:47 undotbs01.dbf -rw-r-----. 1 oracle oinstall 461381632 Jul 30 15:47 system01.dbf -rw-r-----. 1 oracle oinstall 262152192 Jul 30 15:47 sysaux01.dbf -rw-r-----. 1 oracle oinstall 5251072 Jul 30 15:47 users01.dbf -rw-r-----. 1 oracle oinstall 104865792 Jul 30 15:47 example01.dbf -rw-r-----. 1 oracle oinstall 104858112 Jul 30 15:56 std05.redo -rw-r-----. 1 oracle oinstall 7061504 Jul 30 15:56 control01.ctl -rw-r-----. 1 oracle oinstall 7061504 Jul 30 15:56 control02.ctl -rw-r-----. 1 oracle oinstall 7061504 Jul 30 15:56 control03.ctl 关闭主库，加入log_file_name_convert參数后，这种DG才算是完整的配置
转载于:https://www.cnblogs.com/bhlsheji/p/4314298.html
