Oracle一步步搭建DataGuard
-
DataGuard环境:
OS: SuSe 10 Primary DB: IP address:1.1.1.1 user:root passwd:****** Oracle_SID:TEST DB_UNIQUE_NAME:TEST_PD Standby DB: IP address:2.2.2.2 user:root passwd:****** Oracle_SID:TEST DB_UNIQUE_NAME:TEST_ST -
Primary端的配置
-
Primary db设置force logging模式
SQL> select force_logging from v$database; FOR --- YES如果非force Logging模式, 则如下设置
SQL> alter database force logging; -
Primary db设置归档模式
SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /data/u/arch/ Oldest online log sequence 64 Next log sequence to archive 66 Current log sequence 66如果非归档, 则如下设置
SQL> shutdown immediate SQL> startup mount SQL> alter database archivelog; SQL> alter database open; -
创建Standy口令文件
oracle@test_mem_issue:~> orapwd file='/u/rman/orapwTEST' password=test123 entries=10scp口令文件到standby对应的位置:
oracle@test_mem_issue:~> scp /u/rman/orapwdTEST oracle@2.2.2.2#36000:<$ORACLE_HOME>/dbs<$ORACLE_HOME>: oracle软件目录 /u/ora11g/product/11.2.0/db_1
-
修改初始化参数文件
pfile默认的位置在$ORACLE_HOME/dbs目录下, 当然也可以自己执行:
SQL> create pfile from spfile;在initTEST.ora中添加一下内容:
*.DB_UNIQUE_NAME='TEST_PD' *.log_archive_dest_1='location=/u/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=TEST_PD' *.log_archive_dest_2='SERVICE=STANDBY lgwr async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TEST_ST' *.LOG_ARCHIVE_DEST_STATE_1=ENABLE *.LOG_ARCHIVE_DEST_STATE_2=ENABLE *.standby_file_management='AUTO' *.FAL_SERVER='STANDBY' *.FAL_CLIENT='PRIMARY' *.log_archive_config='DG_CONFIG=(TEST_PD,TEST_ST)' *.log_archive_format='TEST_%t_%s_%r.arc' *.ARCHIVE_LAG_TARGET=900如果Primary和standby的数据文件位置不同, 还需要加如下2个参数:
*.log_file_name_convert='/u/oradata/TEST/','/u/oradata1/TEST/' *.db_file_name_convert='/u/oradata/TEST/','/u/oradata1/TEST/'--注意:
*.FAL_SERVER='STANDBY' *.FAL_CLIENT='PRIMARY'STANDBY,PRIMARY是tnsname.ora文件中配置的描述符
用刚修改过的pfile启动primary db,并生成spfile.
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup pfile='/u/ora11g/product/11.2.0/db_1/dbs/initTEST.ora'; ORACLE instance started. Total System Global Area 1603411968 bytes Fixed Size 2226912 bytes Variable Size 486540576 bytes Database Buffers 1107296256 bytes Redo Buffers 7348224 bytes Database mounted. Database opened. SQL> create spfile from pfile='/u/ora11g/product/11.2.0/db_1/dbs/initTEST.ora'; File created. -
修改listener.ora和tnsnams.ora文件
Listener.ora文件
LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 1.1.1.1)(PORT = 1521)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (ORACLE_HOME = /u/ora11g/product/11.2.0/db_1) (SID_NAME = TEST) ) )Tnsnames.ora文件
STANDBY = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 2.2.2.2)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = TEST) ) ) PRIMARY = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 1.1.1.1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = TEST) ) )查看监听状态.
oracle@test_mem_issue:/u/ora11g/product/11.2.0/db_1/dbs> lsnrctl status LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 09-JUL-2013 10:16:34 Copyright (c) 1991, 2010, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=1.1.1.1)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production Start Date 08-JUL-2013 17:20:22 Uptime 0 days 16 hr. 56 min. 12 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u/ora11g/product/11.2.0/db_1/network/admin/listener.ora Listener Log File /u/ora11g/diag/tnslsnr/test_mem_issue/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=1.1.1.1)(PORT=1521))) Services Summary... Service "TEST" has 1 instance(s). Instance "TEST", status UNKNOWN, has 1 handler(s) for this service... Service "TESTXDB" has 1 instance(s). Instance "TEST", status READY, has 1 handler(s) for this service...如果没有启动, 则start.
oracle@test_mem_issue:/u/ora11g/product/11.2.0/db_1/dbs>lsnrctl start这里先stop, 然后start.
oracle@test_mem_issue:/u/ora11g/product/11.2.0/db_1/dbs> lsnrctl stop LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 09-JUL-2013 10:23:05 Copyright (c) 1991, 2010, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=1.1.1.1)(PORT=1521))) The command completed successfully再次查看监听状态.
oracle@test_mem_issue:/u/ora11g/product/11.2.0/db_1/dbs> lsnrctl status LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 09-JUL-2013 10:23:13 Copyright (c) 1991, 2010, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=1.1.1.1)(PORT=1521))) TNS-12541: TNS:no listener TNS-12560: TNS:protocol adapter error TNS-00511: No listener Linux Error: 111: Connection refusedok.启动监听.
oracle@test_mem_issue:/u/ora11g/product/11.2.0/db_1/dbs> lsnrctl start LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 09-JUL-2013 10:25:35 Copyright (c) 1991, 2010, Oracle. All rights reserved. Starting /u/ora11g/product/11.2.0/db_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 11.2.0.2.0 - Production System parameter file is /u/ora11g/product/11.2.0/db_1/network/admin/listener.ora Log messages written to /u/ora11g/diag/tnslsnr/test_mem_issue/listener/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=1.1.1.1)(PORT=1521))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=1.1.1.1)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production Start Date 09-JUL-2013 10:25:35 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u/ora11g/product/11.2.0/db_1/network/admin/listener.ora Listener Log File /u/ora11g/diag/tnslsnr/test_mem_issue/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=1.1.1.1)(PORT=1521))) Services Summary... Service "TEST" has 1 instance(s). Instance "TEST", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully此时登录session,动态注册。
SQL> alter system register; System altered.查看监听状态.
oracle@test_mem_issue:/u/ora11g/product/11.2.0/db_1/dbs> lsnrctl status LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 09-JUL-2013 10:25:57 Copyright (c) 1991, 2010, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.132.24.177)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production Start Date 09-JUL-2013 10:25:35 Uptime 0 days 0 hr. 0 min. 22 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u/ora11g/product/11.2.0/db_1/network/admin/listener.ora Listener Log File /u/ora11g/diag/tnslsnr/test_mem_issue/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=1.1.1.1)(PORT=1521))) Services Summary... Service "TEST" has 1 instance(s). Instance "TEST", status UNKNOWN, has 1 handler(s) for this service... Service "TESTXDB" has 1 instance(s). Instance "TEST", status READY, has 1 handler(s) for this service... Service "TEST_PD" has 1 instance(s). Instance "TEST", status READY, has 1 handler(s) for this service... The command completed successfully -
RMAN备份主库
rman target / nocatalog run{ backup tag hot_db_bak_full filesperset 2 format '/u/rman/%d_%s_%p_%t_%T.bkd' (database); backup current controlfile format '/u/rman/%d_control_%s_%p_%t_%T.bkc'; }如果出现ORA-28365: wallet is not open错误, 则需要配置rman取消加密:
rman target / nocatalog CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO COMPRESSED BACKUPSET; CONFIGURE ENCRYPTION FOR DATABASE OFF;将/u/rman/目录下的*.bkd *.bkc scp到standby对应的目录
rsync -a --progress --bwlimit=102400 *.bkd oracle@2.2.2.2:/u/rman/ rsync -a --progress --bwlimit=102400 *.bkc oracle@2.2.2.2:/u/rman/
-
-
Standby端的配置
-
创建相关数据目录和归档目录
cd /u mkdir arch mkdir rman -
修改初始化参数文件
从Primary端copy过来.
scp /u/ora11g/product/11.2.0/db_1/dbs/initTEST.ora oracle@2.2.2.2#36000:/u/ora11g/product/11.2.0/db_1/dbs修改如下:
*.DB_UNIQUE_NAME='TEST_ST' *.log_archive_dest_1='location=/u/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=TEST_ST' *.log_archive_dest_2='SERVICE=PRIMARY lgwr async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TEST_PD' *.LOG_ARCHIVE_DEST_STATE_1=ENABLE *.LOG_ARCHIVE_DEST_STATE_2=ENABLE *.standby_file_management='AUTO' *.FAL_SERVER='PRIMARY' *.FAL_CLIENT='STANDBY' *.log_archive_config='DG_CONFIG=(TEST_PD,TEST_ST)' *.log_archive_format='TEST_%t_%s_%r.arc' *.ARCHIVE_LAG_TARGET=900 -
用pfile将standby db启动到nomount状态
SQL> startup nomount pfile='/u/ora11g/product/11.2.0/db_1/dbs/initTEST.ora'; ORACLE instance started. Total System Global Area 1603411968 bytes Fixed Size 2226912 bytes Variable Size 486540576 bytes Database Buffers 1107296256 bytes Redo Buffers 7348224 bytes -
创建spfile
SQL> create spfile from pfile='/u/ora11g/product/11.2.0/db_1/dbs/initTEST.ora'; File created. -
设置standby的listener.ora和tnsnames.ora文件
Listener.ora文件
LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 2.2.2.2)(PORT = 1521)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (ORACLE_HOME = /u/ora11g/product/11.2.0/db_1) (SID_NAME = TEST) ) )Tnsnames.ora文件
STANDBY = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 2.2.2.2)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = TEST) ) ) PRIMARY = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 1.1.1.1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = TEST) ) )查看监听状态.
[oracle@TENCENT64 /u/ora11g/product/11.2.0/db_1/network/admin]$ lsnrctl status LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 09-JUL-2013 11:13:49 Copyright (c) 1991, 2010, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=2.2.2.2)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production Start Date 09-JUL-2013 10:47:33 Uptime 0 days 0 hr. 26 min. 16 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u/ora11g/product/11.2.0/db_1/network/admin/listener.ora Listener Log File /u/ora11g/diag/tnslsnr/TENCENT64/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=2.2.2.2)(PORT=1521))) Services Summary... Service "TEST" has 1 instance(s). Instance "TEST", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully如果没有启动,则start.
[oracle@TENCENT64 /u/ora11g/product/11.2.0/db_1/dbs]$ lsnrctl start这里先stop,然后start.
[oracle@TENCENT64 /u/ora11g/product/11.2.0/db_1/network/admin]$ lsnrctl stop LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 09-JUL-2013 11:14:20 Copyright (c) 1991, 2010, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=2.2.2.2)(PORT=1521))) The command completed successfullyok.启动监听.
[oracle@TENCENT64 /u/ora11g/product/11.2.0/db_1/network/admin]$ lsnrctl start LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 09-JUL-2013 11:14:39 Copyright (c) 1991, 2010, Oracle. All rights reserved. Starting /u/ora11g/product/11.2.0/db_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 11.2.0.2.0 - Production System parameter file is /u/ora11g/product/11.2.0/db_1/network/admin/listener.ora Log messages written to /u/ora11g/diag/tnslsnr/TENCENT64/listener/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=2.2.2.2)(PORT=1521))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=2.2.2.2)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production Start Date 09-JUL-2013 11:14:39 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u/ora11g/product/11.2.0/db_1/network/admin/listener.ora Listener Log File /u/ora11g/diag/tnslsnr/TENCENT64/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=2.2.2.2)(PORT=1521))) Services Summary... Service "TEST" has 1 instance(s). Instance "TEST", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully登录session动态注册.
SQL> alter system register; System altered.查看监听状态.
[oracle@TENCENT64 /u/ora11g/product/11.2.0/db_1/network/admin]$ lsnrctl status LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 09-JUL-2013 14:42:06 Copyright (c) 1991, 2010, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=2.2.2.2)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production Start Date 09-JUL-2013 13:30:07 Uptime 0 days 1 hr. 11 min. 58 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u/ora11g/product/11.2.0/db_1/network/admin/listener.ora Listener Log File /u/ora11g/diag/tnslsnr/TENCENT64/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=2.2.2.2)(PORT=1521))) Services Summary... Service "TEST" has 1 instance(s). Instance "TEST", status UNKNOWN, has 1 handler(s) for this service... Service "TESTXDB" has 1 instance(s). Instance "TEST", status READY, has 1 handler(s) for this service... Service "TEST_ST" has 1 instance(s). Instance "TEST", status READY, has 1 handler(s) for this service... The command completed successfully -
恢复standby控制文件
[oracle@TENCENT64 /u/rman]$ rman target / nocatalog Recovery Manager: Release 11.2.0.2.0 - Production on Wed Jul 3 16:50:04 2013 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database: TEST (not mounted) using target database control file instead of recovery catalog RMAN> restore standby controlfile from '/u/rman/TEST_control_20_1_819823252_20130703.bkc'; Starting restore at 07/03/2013 16:50:32 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=572 device type=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 output file name=/u/ora11g/oradata/TEST/control01.ctl output file name=/u/ora11g/oradata/TEST/control02.ctl Finished restore at 07/03/2013 16:50:34 RMAN> sql 'alter database mount standby database'; sql statement: alter database mount standby database released channel: ORA_DISK_1 -
恢复standby数据文件
RMAN> restore database; Starting restore at 07/09/2013 14:48:25 Starting implicit crosscheck backup at 07/09/2013 14:48:25 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=572 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=1142 device type=DISK allocated channel: ORA_DISK_3 channel ORA_DISK_3: SID=1711 device type=DISK allocated channel: ORA_DISK_4 channel ORA_DISK_4: SID=8 device type=DISK Crosschecked 6 objects Crosschecked 8 objects Finished implicit crosscheck backup at 07/09/2013 14:48:26 Starting implicit crosscheck copy at 07/09/2013 14:48:26 using channel ORA_DISK_1 using channel ORA_DISK_2 using channel ORA_DISK_3 using channel ORA_DISK_4 Finished implicit crosscheck copy at 07/09/2013 14:48:26 searching for all files in the recovery area cataloging files... no files cataloged using channel ORA_DISK_1 using channel ORA_DISK_2 using channel ORA_DISK_3 using channel ORA_DISK_4 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00003 to /u/ora11g/oradata/TEST/undotbs01.dbf channel ORA_DISK_1: reading from backup piece /u/rman/TEST_16_1_820317132_20130709.bkd channel ORA_DISK_2: starting datafile backup set restore channel ORA_DISK_2: specifying datafile(s) to restore from backup set channel ORA_DISK_2: restoring datafile 00004 to /u/ora11g/oradata/TEST/users01.dbf channel ORA_DISK_2: reading from backup piece /u/rman/TEST_17_1_820317132_20130709.bkd channel ORA_DISK_3: starting datafile backup set restore channel ORA_DISK_3: specifying datafile(s) to restore from backup set channel ORA_DISK_3: restoring datafile 00002 to /u/ora11g/oradata/TEST/sysaux01.dbf channel ORA_DISK_3: reading from backup piece /u/rman/TEST_15_1_820317132_20130709.bkd channel ORA_DISK_4: starting datafile backup set restore channel ORA_DISK_4: specifying datafile(s) to restore from backup set channel ORA_DISK_4: restoring datafile 00001 to /u/ora11g/oradata/TEST/system01.dbf channel ORA_DISK_4: reading from backup piece /u/rman/TEST_14_1_820317132_20130709.bkd channel ORA_DISK_2: piece handle=/u/rman/TEST_17_1_820317132_20130709.bkd tag=HOT_DB_BAK_FULL channel ORA_DISK_2: restored backup piece 1 channel ORA_DISK_2: restore complete, elapsed time: 00:00:00 channel ORA_DISK_1: piece handle=/u/rman/TEST_16_1_820317132_20130709.bkd tag=HOT_DB_BAK_FULL channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:07 channel ORA_DISK_3: piece handle=/u/rman/TEST_15_1_820317132_20130709.bkd tag=HOT_DB_BAK_FULL channel ORA_DISK_3: restored backup piece 1 channel ORA_DISK_3: restore complete, elapsed time: 00:00:25 channel ORA_DISK_4: piece handle=/u/rman/TEST_14_1_820317132_20130709.bkd tag=HOT_DB_BAK_FULL channel ORA_DISK_4: restored backup piece 1 channel ORA_DISK_4: restore complete, elapsed time: 00:00:35 Finished restore at 07/09/2013 14:49:02 RMAN> recover database; Starting recover at 07/09/2013 14:58:18 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=8 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=574 device type=DISK allocated channel: ORA_DISK_3 channel ORA_DISK_3: SID=1144 device type=DISK allocated channel: ORA_DISK_4 channel ORA_DISK_4: SID=1714 device type=DISK starting media recovery archived log for thread 1 with sequence 69 is already on disk as file /u/arch/TEST_1_69_730313063.arc ...... archived log for thread 1 with sequence 90 is already on disk as file /u/arch/TEST_1_90_730313063.arc archived log file name=/u/arch/TEST_1_68_730313063.arc thread=1 sequence=68 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 07/09/2013 14:58:20 RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/u/arch/TEST_1_68_730313063.arc' ORA-00328: archived log ends at change 1033153, need later change 1069832 ORA-00334: archived log: '/u/arch/TEST_1_68_730313063.arc' RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-00558: error encountered while parsing input commands RMAN-01006: error signaled during parse RMAN-02003: unrecognized character:这里recover时报错,提示68号归档日志找不到.
需要将primary端的相应归档日志scp到执行目录下,再次执行recover.RMAN> recover database; Starting recover at 07/09/2013 14:59:26 using channel ORA_DISK_1 using channel ORA_DISK_2 using channel ORA_DISK_3 using channel ORA_DISK_4 starting media recovery archived log for thread 1 with sequence 69 is already on disk as file /u/arch/TEST_1_69_730313063.arc archived log for thread 1 with sequence 70 is already on disk as file /u/arch/TEST_1_70_730313063.arc ...... archived log for thread 1 with sequence 89 is already on disk as file /u/arch/TEST_1_89_730313063.arc archived log for thread 1 with sequence 90 is already on disk as file /u/arch/TEST_1_90_730313063.arc archived log file name=/u/arch/TEST_1_68_730313063.arc thread=1 sequence=68 archived log file name=/u/arch/TEST_1_69_730313063.arc thread=1 sequence=69 archived log file name=/u/arch/TEST_1_70_730313063.arc thread=1 sequence=70 ...... archived log file name=/u/arch/TEST_1_89_730313063.arc thread=1 sequence=89 archived log file name=/u/arch/TEST_1_90_730313063.arc thread=1 sequence=90 unable to find archived log archived log thread=1 sequence=91 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 07/09/2013 14:59:30 RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 91 and starting SCN of 1091265 -
确认standby相关参数
SQL> show parameter log_archive_config; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_config string DG_CONFIG=(TEST_ST,TEST_PD ) SQL> show parameter fal_server; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ fal_server string PRIMARY SQL> show parameter fal_client; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ fal_client string STANDBY若不正确, 需要修改:
SQL> alter system set log_archive_config='DG_CONFIG=(TEST_PD,TEST_ST)'; System altered. SQL> alter system set fal_server=PRIMARY; System altered. SQL> alter system set fal_client=STANDBY; System altered. -
添加standby redolog files
standby redofile count=(online redofile + 1)*2 standby redofile size=online redofile size alter database add standby logfile group 34 ('/u/arch/TEST/stby/db_stb_redo04.log') size 50m; alter database add standby logfile group 33 ('/u/arch/TEST/stby/db_stb_redo03.log') size 50m; alter database add standby logfile group 32 ('/u/arch/TEST/stby/db_stb_redo02.log') size 50m; alter database add standby logfile group 31 ('/u/arch/TEST/stby/db_stb_redo01.log') size 50m; alter database add standby logfile group 30 ('/u/arch/TEST/stby/db_stb_redo10.log') size 50m; alter database add standby logfile group 39 ('/u/arch/TEST/stby/db_stb_redo09.log') size 50m; alter database add standby logfile group 38 ('/u/arch/TEST/stby/db_stb_redo08.log') size 50m; alter database add standby logfile group 37 ('/u/arch/TEST/stby/db_stb_redo07.log') size 50m; alter database add standby logfile group 36 ('/u/arch/TEST/stby/db_stb_redo06.log') size 50m; alter database add standby logfile group 35 ('/u/arch/TEST/stby/db_stb_redo05.log') size 50m; -
DataGuard启动
--注意:DataGuard的启动顺序
启动顺序: 先standby后primary
关闭顺序: 先primary后standby(1). 在standby db上执行
SQL> select open_mode from v$database; OPEN_MODE -------------------- MOUNTED SQL> alter database open read only; Database altered.--注意:
如果没有处理前面recover的报错(找不到归档日志), 这里可能启动的时候报system表空间和控制文件记录的内容出现不一致, 这个时候最好的方法就是, 去主库拷贝那些丢失的归档.
SQL> alter database open read only; alter database open read only * ERROR at line 1: ORA-10458: standby database requires recovery ORA-01152: file 1 was not restored from a sufficiently old backup ORA-01110: data file 1: '/u/ora11g/oradata/TEST/system01.dbf'之前recover的时候注意到,有ORA提示到归档日志有缺失.
RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 07/09/2013 14:58:20 RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/u/arch/TEST_1_68_730313063.arc' ORA-00328: archived log ends at change 1033153, need later change 1069832 ORA-00334: archived log: '/u/arch/TEST_1_68_730313063.arc'从primary上scp缺失的arc文件,然后再recover database一下.
RMAN> recover database; Starting recover at 07/09/2013 14:59:26 using channel ORA_DISK_1 using channel ORA_DISK_2 using channel ORA_DISK_3 using channel ORA_DISK_4 starting media recovery archived log for thread 1 with sequence 69 is already on disk as file /u/arch/TEST_1_69_730313063.arc archived log for thread 1 with sequence 70 is already on disk as file /u/arch/TEST_1_70_730313063.arc ...... archived log for thread 1 with sequence 89 is already on disk as file /u/arch/TEST_1_89_730313063.arc archived log for thread 1 with sequence 90 is already on disk as file /u/arch/TEST_1_90_730313063.arc archived log file name=/u/arch/TEST_1_68_730313063.arc thread=1 sequence=68 archived log file name=/u/arch/TEST_1_69_730313063.arc thread=1 sequence=69 archived log file name=/u/arch/TEST_1_70_730313063.arc thread=1 sequence=70 ...... archived log file name=/u/arch/TEST_1_89_730313063.arc thread=1 sequence=89 archived log file name=/u/arch/TEST_1_90_730313063.arc thread=1 sequence=90 unable to find archived log archived log thread=1 sequence=91 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 07/09/2013 14:59:30 RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 91 and starting SCN of 1091265再次以read only方式启动standby db就可以了.
SQL> alter database open read only; Database altered. SQL> alter database recover managed standby database disconnect from session using current logfile; Database altered.(2).在primay db上检查如下参数,如果没有则设置.
SQL> alter system set log_archive_dest_2='SERVICE=STANDBY ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TEST_ST'; System altered. SQL> alter system set log_archive_dest_state_2=ENABLE; System altered. SQL> alter system set archive_lag_target=900; System altered.(3).查询是否有出错信息(primary和standby上均执行下)
primary上执行:
SQL> select dest_id,status,destination,error from v$archive_dest where dest_id<=5; DEST_ID STATUS DESTINATIO ERROR ---------- --------- ---------- ---------- 1 VALID /u/arch 2 VALID STANDBY 3 INACTIVE 4 INACTIVE 5 INACTIVEstandby上执行:
SQL> select dest_id,status,destination,error from v$archive_dest where dest_id<=5; DEST_ID STATUS DESTINATIO ERROR ---------- --------- ---------- ---------- 1 VALID /u/arch 2 VALID PRIMARY 3 INACTIVE 4 INACTIVE 5 INACTIVE -
验证DataGuard：
(1).在standby查看
SQL> select sequence#,first_time,next_time from v$archived_log order by sequence#; SEQUENCE# FIRST_TIME NEXT_TIME ---------- ------------------- ------------------- 68 07/09/2013 11:54:31 07/09/2013 12:09:31 68 07/09/2013 09:49:35 07/09/2013 10:04:35 68 07/09/2013 11:54:31 07/09/2013 12:09:31 ...... 91 07/09/2013 14:56:01 07/09/2013 15:11:00 92 07/09/2013 15:11:00 07/09/2013 15:19:42 93 07/09/2013 15:19:42 07/09/2013 15:34:42 28 rows selected.(2).在primary上执行归档命令并查看已归档的日志
SQL> alter system archive log current; System altered. SQL> select sequence#,first_time,next_time from v$archived_log order by sequence#; SEQUENCE# FIRST_TIME NEXT_TIME ---------- ------------------- ------------------- 62 09/25/2010 03:20:17 07/08/2013 21:10:42 63 07/08/2013 21:10:42 07/08/2013 21:10:45 64 07/08/2013 21:10:45 07/08/2013 21:10:47 ...... 93 07/09/2013 15:19:42 07/09/2013 15:34:42 94 07/09/2013 15:34:42 07/09/2013 15:40:44 94 07/09/2013 15:34:42 07/09/2013 15:40:44 61 rows selected.(3).再次在standby上看当前已归档的日志
SQL> select sequence#,first_time,next_time from v$archived_log order by sequence#; SEQUENCE# FIRST_TIME NEXT_TIME ---------- ------------------- ------------------- 68 07/09/2013 11:54:31 07/09/2013 12:09:31 68 07/09/2013 09:49:35 07/09/2013 10:04:35 68 07/09/2013 11:54:31 07/09/2013 12:09:31 ...... 91 07/09/2013 14:56:01 07/09/2013 15:11:00 92 07/09/2013 15:11:00 07/09/2013 15:19:42 93 07/09/2013 15:19:42 07/09/2013 15:34:42 94 07/09/2013 15:34:42 07/09/2013 15:40:44 29 rows selected.另外, 同样可以从alert文件里也看到了94号日志应用.
$ tail -f alert_TEST.log Tue Jul 09 15:40:44 2013 Expanded controlfile section 11 from 28 to 280 records Requested to grow by 252 records; added 9 blocks of records Archived Log entry 29 added for thread 1 sequence 94 ID 0xc8c4a8e7 dest 1: Tue Jul 09 15:40:44 2013 RFS[3]: Selected log 30 for thread 1 sequence 95 dbid -926624281 branch 730313063 Tue Jul 09 15:40:44 2013 Media Recovery Waiting for thread 1 sequence 95 (in transit) Recovery of Online Redo Log: Thread 1 Group 30 Seq 95 Reading mem 0 Mem# 0: /u/arch/TEST/stby/db_stb_redo10.log
-
--END--