oracle11g dataguard中standby库文件坏块的修复过程

来源:互联网 时间:1970-01-01

问题描述:

机房断电了,所以primary和standby库都是直接断电,然后我都设置了开机自启动oracle,所以第二天我来看的时候,primary和standby都启动了,归档日志也传输到standby了,但是日志应用后报错,有文件坏块,所以需要修复。

1,查看alert日志报警信息

Recovered data files to a consistent state at change 11550152086Errors in file /oracle/app/oracle/diag/rdbms/pddgunq/powerdes/trace/powerdes_pr00_22925.trc:ORA-00448: normal completion of background processWed Oct 14 19:34:41 2015Errors in file /oracle/app/oracle/diag/rdbms/pddgunq/powerdes/trace/powerdes_mrp0_22923.trc:ORA-00600: internal error code, arguments: [kcbr_apply_change_11], [], [], [], [], [], [], [], [], [], [], []MRP0: Background Media Recovery process shutdown (powerdes)Wed Oct 14 21:32:47 2015Standby controlfile consistent with primaryWed Oct 14 21:32:48 2015Archived Log entry 4608 added for thread 1 sequence 38105 ID 0xca2ab4eb dest 3:RFS[3]: Selected log 4 for thread 1 sequence 38106 dbid -903205653 branch 821708334

2,查看powerdes_pr00_22925.trc文件

[[email protected] ~]$ more /oracle/app/oracle/diag/rdbms/pddgunq/powerdes/trace/powerdes_pr00_22925.trcTrace file /oracle/app/oracle/diag/rdbms/pddgunq/powerdes/trace/powerdes_pr00_22925.trcOracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith the Partitioning, Oracle Label Security, OLAP, Data Mining,Oracle Database Vault and Real Application Testing optionsORACLE_HOME = /oracle/app/oracle/product/11.2.0/dbhome_1System name: LinuxNode name: localhost.localdomainRelease: 2.6.39-400.17.1.el6uek.x86_64Version: #1 SMP Fri Feb 22 18:16:18 PST 2013Machine: x86_64Instance name: powerdesRedo thread mounted by this instance: 1Oracle process number: 45Unix process pid: 22925, image: [email protected] (PR00)*** 2015-10-14 19:34:30.199*** SESSION ID:(1251.3) 2015-10-14 19:34:30.199*** CLIENT ID:() 2015-10-14 19:34:30.199*** SERVICE NAME:(SYS$USERS) 2015-10-14 19:34:30.199*** MODULE NAME:() 2015-10-14 19:34:30.199*** ACTION NAME:() 2015-10-14 19:34:30.199Started Parallel Media Recovery*** 2015-10-14 19:34:30.206 4132 krsh.cManaged Standby Recovery not using Real Time ApplyDumping database incarnation table:Resetlogs 0 scn and time: 0x0000.000e6c20 07/25/2013 12:18:54Recovery target incarnation = 2, activation ID = -903170837Influx buffer limit = 100000 min(50% x 213003, 100000)*** 2015-10-14 19:34:30.676Start recovery at thread 1 ckpt scn 11550151590 logseq 0 block 0*** 2015-10-14 19:34:30.827Media Recovery add redo thread 1*** 2015-10-14 19:34:30.950Media Recovery Log /data/oracle/oradgdata/standby_archive/1_38052_821708334.dbfLog read is SYNCHRONOUS though disk_asynch_io is enabled!*** 2015-10-14 19:34:32.628*** 2015-10-14 19:34:32.628 4132 krsh.cMRP0: Background Media Recovery terminated with error 448ORA-00448: normal completion of background process----- Redo read statistics for thread 1 -----Read rate (SYNC): 32690Kb in 1.90s => 16.80 Mb/secTotal redo bytes: 32690Kb Longest record: 14Kb, moves: 25/43465 moved: 0Mb (0%)Longest LWN: 904Kb, reads: 5685 Last redo scn: 0x0002.b0715c4c (11550153804)Change vector header moves = 5271/83624 (6%)----------------------------------------------*** 2015-10-14 19:34:32.728Media Recovery drop redo thread 1Waiting for ksv slaves to exit Waiting for ksv slaves to exit Waiting for ksv slaves to exit Waiting for ksv slaves to exit Waiting for ksv slaves to exit Waiting for ksv slaves to exit Waiting for ksv slaves to exit Waiting for ksv slaves to exit Waiting for ksv slaves to exit Waiting for ksv slaves to exit KCBR: Redo cache copies/changes = 2/2*** 2015-10-14 19:34:33.133Completed Media RecoveryChecking to start in-flux buffer recovery from SCN 2.2960216998 to SCN (non-inclusive) 2.2960217494Influx recovery found in-flux buffers*** 2015-10-14 19:34:33.136Influx Media Recovery add redo thread 1*** 2015-10-14 19:34:41.722*** 2015-10-14 19:34:41.722 1266 krsm.cManaged Recovery: Not Active posted.ORA-00448: normal completion of background process[[email protected] ~]$ 

3,看到是文件1_38052_821708334.dbf坏块,通过dbv检查

[[email protected] ~]$ dbv /data/oracle/oradgdata/standby_archive/1_38052_821708334.dbfDBVERIFY: Release 11.2.0.1.0 - Production on Wed Oct 14 22:46:20 2015Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.LRM-00108: invalid positional parameter value '/data/oracle/oradgdata/standby_archive/1_38052_821708334.dbf'DBV-00001: Illegal command line syntax - parse error = (108)[[email protected] ~]$ 

Google出来的解决方案:shutdown the ADG standby, then mount it, and do media recovery until it has recovered past all the redo generated during the hot backup taken on the primary. Then stop media recovery, and open the database read only, and restart media recovery again.

采用解决方案:duplicate 远程恢复。

4, 关闭standby库

4.1 开始关闭standby库

SQL> shutdown immediate ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL>

4.2 在standby上删除数据文件和控制文件

去数据文件目录和控制文件目录删除数据文件控制文件,如果一时不记得目录位置,可以通过以下方式查询

select * from v$controlfile;select * from v$datafile;

4.3 再standby上删除归档目录下的旧归档文件

cd /data/oracle/oradgdata/standby_archive/rm -rf *.dbf 

4.4 在standby上启动nomount

SQL> startup nomount ORACLE instance started. Total System Global Area 5344731136 bytesFixed Size 2213136 bytesVariable Size 3489663728 bytesDatabase Buffers 1811939328 bytesRedo Buffers 40914944 bytesSQL> 

4.5 在primary上操作

主库上
rman target sys/[email protected] auxiliary sys/[email protected]
rman sys/你的sys密码@46是主库tns auxiliary sys/你的sys密码@54是备库tns

[[email protected] ~]$ rlwrap rman target / auxiliary sys/[email protected]_DGrlwrap: warning: your $TERM is 'xterm' but rlwrap couldn't find it in the terminfo database. Expect some problems.Recovery Manager: Release 11.2.0.1.0 - Production on Mon Oct 19 17:33:15 2015Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.connected to target database: POWERDES (DBID=3391761643) connected to auxiliary database: POWERDES (not mounted) RMAN> duplicate target database for standby from active database nofilenamecheck;RMAN> duplicate target database for standby from active database nofilenamecheck; Starting Duplicate Db at 19-OCT-15 using target database control file instead of recovery catalog allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=482 device type=DISKcontents of Memory Script:{ backup as copy reuse targetfile '/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/orapwpowerdes' auxiliary format '/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/orapwpowerdes' ;}executing Memory ScriptStarting backup at 19-OCT-15 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=494 device type=DISKFinished backup at 19-OCT-15 contents of Memory Script:{ backup as copy current controlfile for standby auxiliary format '/home/oradata/powerdes/control01.ctl'; restore clone controlfile to '/oracle/app/oracle/flash_recovery_area/powerdes/control02.ctl' from '/home/oradata/powerdes/control01.ctl';}executing Memory ScriptStarting backup at 19-OCT-15 using channel ORA_DISK_1channel ORA_DISK_1: starting datafile copy copying standby control fileoutput file name=/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_powerdes.f tag=TAG20151019T173556 RECID=3 STAMP=893525757 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03Finished backup at 19-OCT-15Starting restore at 19-OCT-15 using channel ORA_AUX_DISK_1channel ORA_AUX_DISK_1: copied control file copy Finished restore at 19-OCT-15contents of Memory Script:{ sql clone 'alter database mount standby database';}executing Memory Scriptsql statement: alter database mount standby databasecontents of Memory Script:{ set newname for tempfile 1 to "/home/oradata/powerdes/temp01.dbf"; switch clone tempfile all; set newname for datafile 1 to "/home/oradata/powerdes/system01.dbf"; set newname for datafile 2 to "/home/oradata/powerdes/sysaux01.dbf"; set newname for datafile 3 to "/home/oradata/powerdes/undotbs01.dbf"; set newname for datafile 4 to "/home/oradata/powerdes/users01.dbf"; set newname for datafile 5 to "/home/oradata/powerdes/powerdesk01.dbf"; set newname for datafile 6 to "/home/oradata/powerdes/plas01.dbf"; set newname for datafile 7 to "/home/oradata/powerdes/pl01.dbf"; set newname for datafile 8 to "/home/oradata/powerdes/help01.dbf"; set newname for datafile 9 to "/home/oradata/powerdes/adobelc01.dbf"; set newname for datafile 10 to "/home/oradata/powerdes/sms01.dbf"; set newname for datafile 11 to "/home/oradata/powerdes/plcrm01.dbf"; set newname for datafile 12 to "/home/oradata/powerdes/powerdesk02.dbf"; set newname for datafile 13 to "/home/oradata/powerdes/datagm01.dbf"; backup as copy reuse datafile 1 auxiliary format "/home/oradata/powerdes/system01.dbf" datafile 2 auxiliary format "/home/oradata/powerdes/sysaux01.dbf" datafile 3 auxiliary format "/home/oradata/powerdes/undotbs01.dbf" datafile 4 auxiliary format "/home/oradata/powerdes/users01.dbf" datafile 5 auxiliary format "/home/oradata/powerdes/powerdesk01.dbf" datafile 6 auxiliary format "/home/oradata/powerdes/plas01.dbf" datafile 7 auxiliary format "/home/oradata/powerdes/pl01.dbf" datafile 8 auxiliary format "/home/oradata/powerdes/help01.dbf" datafile 9 auxiliary format "/home/oradata/powerdes/adobelc01.dbf" datafile 10 auxiliary format "/home/oradata/powerdes/sms01.dbf" datafile 11 auxiliary format "/home/oradata/powerdes/plcrm01.dbf" datafile 12 auxiliary format "/home/oradata/powerdes/powerdesk02.dbf" datafile 13 auxiliary format "/home/oradata/powerdes/datagm01.dbf" ; sql 'alter system archive log current';}executing Memory Scriptexecuting command: SET NEWNAMErenamed tempfile 1 to /home/oradata/powerdes/temp01.dbf in control file executing command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEStarting backup at 19-OCT-15 using channel ORA_DISK_1channel ORA_DISK_1: starting datafile copy input datafile file number=00005 name=/home/oradata/powerdes/powerdesk01.dbfoutput file name=/home/oradata/powerdes/powerdesk01.dbf tag=TAG20151019T173607 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:07:45channel ORA_DISK_1: starting datafile copy input datafile file number=00006 name=/home/oradata/powerdes/plas01.dbfoutput file name=/home/oradata/powerdes/plas01.dbf tag=TAG20151019T173607 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:15channel ORA_DISK_1: starting datafile copy input datafile file number=00003 name=/home/oradata/powerdes/undotbs01.dbfoutput file name=/home/oradata/powerdes/undotbs01.dbf tag=TAG20151019T173607 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:15channel ORA_DISK_1: starting datafile copy input datafile file number=00012 name=/home/oradata/powerdes/powerdesk02.dbfoutput file name=/home/oradata/powerdes/powerdesk02.dbf tag=TAG20151019T173607 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:15channel ORA_DISK_1: starting datafile copy input datafile file number=00001 name=/home/oradata/powerdes/system01.dbfoutput file name=/home/oradata/powerdes/system01.dbf tag=TAG20151019T173607 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05channel ORA_DISK_1: starting datafile copy input datafile file number=00002 name=/home/oradata/powerdes/sysaux01.dbfoutput file name=/home/oradata/powerdes/sysaux01.dbf tag=TAG20151019T173607 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05channel ORA_DISK_1: starting datafile copy input datafile file number=00011 name=/home/oradata/powerdes/plcrm01.dbfoutput file name=/home/oradata/powerdes/plcrm01.dbf tag=TAG20151019T173607 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35channel ORA_DISK_1: starting datafile copy input datafile file number=00007 name=/home/oradata/powerdes/pl01.dbfoutput file name=/home/oradata/powerdes/pl01.dbf tag=TAG20151019T173607 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25channel ORA_DISK_1: starting datafile copy input datafile file number=00004 name=/home/oradata/powerdes/users01.dbfoutput file name=/home/oradata/powerdes/users01.dbf tag=TAG20151019T173607 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15channel ORA_DISK_1: starting datafile copy input datafile file number=00008 name=/home/oradata/powerdes/help01.dbfoutput file name=/home/oradata/powerdes/help01.dbf tag=TAG20151019T173607 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03channel ORA_DISK_1: starting datafile copy input datafile file number=00009 name=/home/oradata/powerdes/adobelc01.dbfoutput file name=/home/oradata/powerdes/adobelc01.dbf tag=TAG20151019T173607 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03channel ORA_DISK_1: starting datafile copy input datafile file number=00010 name=/home/oradata/powerdes/sms01.dbfoutput file name=/home/oradata/powerdes/sms01.dbf tag=TAG20151019T173607 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03channel ORA_DISK_1: starting datafile copy input datafile file number=00013 name=/home/oradata/powerdes/datagm01.dbfoutput file name=/home/oradata/powerdes/datagm01.dbf tag=TAG20151019T173607 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03Finished backup at 19-OCT-15sql statement: alter system archive log currentcontents of Memory Script:{ switch clone datafile all;}executing Memory Scriptdatafile 1 switched to datafile copy input datafile copy RECID=3 STAMP=893526478 file name=/home/oradata/powerdes/system01.dbfdatafile 2 switched to datafile copy input datafile copy RECID=4 STAMP=893526478 file name=/home/oradata/powerdes/sysaux01.dbfdatafile 3 switched to datafile copy input datafile copy RECID=5 STAMP=893526479 file name=/home/oradata/powerdes/undotbs01.dbfdatafile 4 switched to datafile copy input datafile copy RECID=6 STAMP=893526479 file name=/home/oradata/powerdes/users01.dbfdatafile 5 switched to datafile copy input datafile copy RECID=7 STAMP=893526479 file name=/home/oradata/powerdes/powerdesk01.dbfdatafile 6 switched to datafile copy input datafile copy RECID=8 STAMP=893526479 file name=/home/oradata/powerdes/plas01.dbfdatafile 7 switched to datafile copy input datafile copy RECID=9 STAMP=893526479 file name=/home/oradata/powerdes/pl01.dbfdatafile 8 switched to datafile copy input datafile copy RECID=10 STAMP=893526479 file name=/home/oradata/powerdes/help01.dbfdatafile 9 switched to datafile copy input datafile copy RECID=11 STAMP=893526479 file name=/home/oradata/powerdes/adobelc01.dbfdatafile 10 switched to datafile copy input datafile copy RECID=12 STAMP=893526479 file name=/home/oradata/powerdes/sms01.dbfdatafile 11 switched to datafile copy input datafile copy RECID=13 STAMP=893526479 file name=/home/oradata/powerdes/plcrm01.dbfdatafile 12 switched to datafile copy input datafile copy RECID=14 STAMP=893526479 file name=/home/oradata/powerdes/powerdesk02.dbfdatafile 13 switched to datafile copy input datafile copy RECID=15 STAMP=893526479 file name=/home/oradata/powerdes/datagm01.dbfFinished Duplicate Db at 19-OCT-15 RMAN> 

4.6,去standby从库启动open

SQL> alter database open; Database altered.SQL> 

4.7 去standby从库开始应用日志

SQL> alter database recover managed standby database disconnect from session; Database altered.SQL> 

4.8 check 主备一致性

从库standby 上check

SQL> select sequence#,applied from v$archived_log order by sequence# asc; SEQUENCE# APPLIED---------- --------- 38268 YES 38269 YES 38270 YES 38271 YESSQL> 

主库primary上check

SQL> select sequence#,applied from v$archived_log order by sequence# asc; SEQUENCE# APPLIED---------- --------- 38265 NO 38265 YES 38266 NO 38267 NO 38268 YES 38268 NO 38269 NO 38269 YES 38270 NO 38270 YES 38271 YES SEQUENCE# APPLIED---------- --------- 38271 NO5600 rows selected.SQL> 

4.9 check

主库上primary上操作

SQL> create table z_z_test(id number); Table created.SQL> insert into z_z_test values(1); 1 row created.SQL> commit; Commit complete.SQL> archive log list; Database log mode Archive ModeAutomatic archival EnabledArchive destination /oracle/app/oracle/flash_recovery_area/archivelogOldest online log sequence 38270Next log sequence to archive 38272Current log sequence 38272SQL> SQL> SQL> alter system switch logfile; System altered.SQL> 

从库standby上查询数据:

SQL> archive log list; Database log mode Archive ModeAutomatic archival EnabledArchive destination /data/oracle/oradgdata/standby_archiveOldest online log sequence 38271Next log sequence to archive 0Current log sequence 38272SQL> archive log list; Database log mode Archive ModeAutomatic archival EnabledArchive destination /data/oracle/oradgdata/standby_archiveOldest online log sequence 38271Next log sequence to archive 0Current log sequence 38273SQL> SQL> select * from z_z_test; ID---------- 1SQL> 

看到log sequence已经增长到38273,日志应该应用过来了,然后去查询数据临时表z_z_test数据也已经过来了,证明修复OK,成功了。

5,后台alert日志有错误信息

Errors in file /oracle/app/oracle/diag/rdbms/pddgunq/powerdes/trace/powerdes_dbw0_14852.trc:ORA-01157: cannot identify/lock data file 201 - see DBWR trace fileORA-01110: data file 201: '/home/oradata/powerdes/temp01.dbf'ORA-27037: unable to obtain file statusLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3Errors in file /oracle/app/oracle/diag/rdbms/pddgunq/powerdes/trace/powerdes_dbw0_14852.trc:ORA-01186: file 201 failed verification testsORA-01157: cannot identify/lock data file 201 - see DBWR trace fileORA-01110: data file 201: '/home/oradata/powerdes/temp01.dbf'File 201 not verified due to error ORA-01157Dictionary check completeRe-creating tempfile /home/oradata/powerdes/temp01.dbfDatabase Characterset is ZHS16GBKNo Resource Manager plan active**********************************************************WARNING: Files may exists in db_recovery_file_destthat are not known to the database. Use the RMAN commandCATALOG RECOVERY AREA to re-catalog any such files.If files cannot be cataloged, then manually delete themusing OS command.One of the following events caused this:1. A backup controlfile was restored.2. A standby controlfile was restored.3. The controlfile was re-created.4. db_recovery_file_dest had previously been enabled and then disabled.**********************************************************replication_dependency_tracking turned off (no async multimaster replication found)Physical standby database opened for read only access.Completed: alter database openMon Oct 19 17:53:06 2015alter database recover managed standby database disconnect from sessionAttempt to start background Managed Standby Recovery process (powerdes)Mon Oct 19 17:53:06 2015MRP0 started with pid=48, OS id=15063 MRP0: Background Managed Standby Recovery process started (powerdes) started logmerger processMon Oct 19 17:53:11 2015Managed Standby Recovery not using Real Time ApplyParallel Media Recovery started with 16 slavesWaiting for all non-current ORLs to be archived...All non-current ORLs have been archived.Media Recovery Log /data/oracle/oradgdata/standby_archive/1_38271_821708334.dbfCompleted: alter database recover managed standby database disconnect from sessionMedia Recovery Waiting for thread 1 sequence 38272 (in transit)Mon Oct 19 18:01:33 2015Standby controlfile consistent with primaryMon Oct 19 18:01:34 2015Archived Log entry 5 added for thread 1 sequence 38272 ID 0xca2ab4eb dest 3:RFS[4]: Selected log 4 for thread 1 sequence 38273 dbid -903205653 branch 821708334Mon Oct 19 18:01:39 2015Media Recovery Log /data/oracle/oradgdata/standby_archive/1_38272_821708334.dbfMedia Recovery Waiting for thread 1 sequence 38273 (in transit)

看到有ERROR信息:
Errors in file /oracle/app/oracle/diag/rdbms/pddgunq/powerdes/trace/powerdes_dbw0_14852.trc:
ORA-01186: file 201 failed verification tests
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: ‘/home/oradata/powerdes/temp01.dbf’

这种错误忽略就可以了,暂时不用考虑它们。

总结:

1)这种恢复必须在standby从库和主库的数据文件目录参数文件目录控制文件目录归档日志文件目录等等都相同的情况下才可以
2)online在线的主库primary库压力比较小,进行远程恢复不影响primary的业务正常使用。
3)最后在晚上进行恢复



相关阅读:
Top