您當(dāng)前位置：首頁 > 數(shù)據(jù)庫 > 數(shù)據(jù)庫應(yīng)用 > oracle11g dataguard中standby庫文件壞塊的修復(fù)過程

oracle11g dataguard中standby庫文件壞塊的修復(fù)過程

來源：程序員人生發(fā)布時(shí)間：2016-03-31 08:50:39 閱讀次數(shù)：4418次

問題描寫:

機(jī)房斷電了，所以primary和standby庫都是直接斷電，然后我都設(shè)置了開機(jī)自啟動(dòng)oracle，所以第2天我來看的時(shí)候，primary和standby都啟動(dòng)了，歸檔日志也傳輸?shù)絪tandby了，但是日志利用后報(bào)錯(cuò)，有文件壞塊，所以需要修復(fù)。

1，查看alert日志報(bào)警信息

Recovered data files to a consistent state at change 11550152086 Errors in file /oracle/app/oracle/diag/rdbms/pddgunq/powerdes/trace/powerdes_pr00_22925.trc: ORA-00448: normal completion of background process Wed Oct 14 19:34:41 2015 Errors 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 2015 Standby controlfile consistent with primary Wed Oct 14 21:32:48 2015 Archived 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文件

[oracle@localhost ~]$ more /oracle/app/oracle/diag/rdbms/pddgunq/powerdes/trace/powerdes_pr00_22925.trc Trace file /oracle/app/oracle/diag/rdbms/pddgunq/powerdes/trace/powerdes_pr00_22925.trc Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options ORACLE_HOME = /oracle/app/oracle/product/11.2.0/dbhome_1 System name: Linux Node name: localhost.localdomain Release: 2.6.39-400.17.1.el6uek.x86_64 Version: #1 SMP Fri Feb 22 18:16:18 PST 2013 Machine: x86_64 Instance name: powerdes Redo thread mounted by this instance: 1 Oracle process number: 45 Unix process pid: 22925, image: oracle@localhost.localdomain (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.199 Started Parallel Media Recovery *** 2015-10-14 19:34:30.206 4132 krsh.c Managed Standby Recovery not using Real Time Apply Dumping database incarnation table: Resetlogs 0 scn and time: 0x0000.000e6c20 07/25/2013 12:18:54 Recovery target incarnation = 2, activation ID = -903170837 Influx buffer limit = 100000 min(50% x 213003, 100000) *** 2015-10-14 19:34:30.676 Start recovery at thread 1 ckpt scn 11550151590 logseq 0 block 0 *** 2015-10-14 19:34:30.827 Media Recovery add redo thread 1 *** 2015-10-14 19:34:30.950 Media Recovery Log /data/oracle/oradgdata/standby_archive/1_38052_821708334.dbf Log 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.c MRP0: Background Media Recovery terminated with error 448 ORA-00448: normal completion of background process ----- Redo read statistics for thread 1 ----- Read rate (SYNC): 32690Kb in 1.90s => 16.80 Mb/sec Total 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.728 Media Recovery drop redo thread 1 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 Waiting for ksv slaves to exit KCBR: Redo cache copies/changes = 2/2 *** 2015-10-14 19:34:33.133 Completed Media Recovery Checking to start in-flux buffer recovery from SCN 2.2960216998 to SCN (non-inclusive) 2.2960217494 Influx recovery found in-flux buffers *** 2015-10-14 19:34:33.136 Influx Media Recovery add redo thread 1 *** 2015-10-14 19:34:41.722 *** 2015-10-14 19:34:41.722 1266 krsm.c Managed Recovery: Not Active posted. ORA-00448: normal completion of background process [oracle@localhost ~]$

3，看到是文件1_38052_821708334.dbf壞塊，通過dbv檢查

[oracle@localhost ~]$ dbv /data/oracle/oradgdata/standby_archive/1_38052_821708334.dbf DBVERIFY: Release 11.2.0.1.0 - Production on Wed Oct 14 22:46:20 2015 Copyright (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) [oracle@localhost ~]$

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 遠(yuǎn)程恢復(fù)。

4，關(guān)閉standby庫

4.1 開始關(guān)閉standby庫

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

4.2 在standby上刪除數(shù)據(jù)文件和控制文件

去數(shù)據(jù)文件目錄和控制文件目錄刪除數(shù)據(jù)文件控制文件，如果1時(shí)不記得目錄位置，可以通過以下方式查詢

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

4.3 再standby上刪除歸檔目錄下的舊歸檔文件

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

4.4 在standby上啟動(dòng)nomount

SQL> startup nomount ORACLE instance started. Total System Global Area 5344731136 bytes Fixed Size 2213136 bytes Variable Size 3489663728 bytes Database Buffers 1811939328 bytes Redo Buffers 40914944 bytes SQL>

4.5 在primary上操作

主庫上
rman target sys/oraclepwd@XTTTESTDB.46 auxiliary sys/oraclepwd@XTTTESTDB.54
rman sys/你的sys密碼@46是主庫tns auxiliary sys/你的sys密碼@54是備庫tns

[oracle@localhost ~]$ rlwrap rman target / auxiliary sys/sysplxxx58@PD_DG rlwrap: warning: your $TERM is xterm but rlwrap couldnt 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 2015 Copyright (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=DISK contents 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 Script Starting backup at 19-OCT-15 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=494 device type=DISK Finished 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 Script Starting backup at 19-OCT-15 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy copying standby control file output 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:03 Finished backup at 19-OCT-15 Starting restore at 19-OCT-15 using channel ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: copied control file copy Finished restore at 19-OCT-15 contents of Memory Script: { sql clone alter database mount standby database; } executing Memory Script sql statement: alter database mount standby database contents 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 Script executing command: SET NEWNAME renamed tempfile 1 to /home/oradata/powerdes/temp01.dbf in control file executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME Starting backup at 19-OCT-15 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00005 name=/home/oradata/powerdes/powerdesk01.dbf output file name=/home/oradata/powerdes/powerdesk01.dbf tag=TAG20151019T173607 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:07:45 channel ORA_DISK_1: starting datafile copy input datafile file number=00006 name=/home/oradata/powerdes/plas01.dbf output file name=/home/oradata/powerdes/plas01.dbf tag=TAG20151019T173607 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:15 channel ORA_DISK_1: starting datafile copy input datafile file number=00003 name=/home/oradata/powerdes/undotbs01.dbf output file name=/home/oradata/powerdes/undotbs01.dbf tag=TAG20151019T173607 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:15 channel ORA_DISK_1: starting datafile copy input datafile file number=00012 name=/home/oradata/powerdes/powerdesk02.dbf output file name=/home/oradata/powerdes/powerdesk02.dbf tag=TAG20151019T173607 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:15 channel ORA_DISK_1: starting datafile copy input datafile file number=00001 name=/home/oradata/powerdes/system01.dbf output file name=/home/oradata/powerdes/system01.dbf tag=TAG20151019T173607 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05 channel ORA_DISK_1: starting datafile copy input datafile file number=00002 name=/home/oradata/powerdes/sysaux01.dbf output file name=/home/oradata/powerdes/sysaux01.dbf tag=TAG20151019T173607 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05 channel ORA_DISK_1: starting datafile copy input datafile file number=00011 name=/home/oradata/powerdes/plcrm01.dbf output file name=/home/oradata/powerdes/plcrm01.dbf tag=TAG20151019T173607 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35 channel ORA_DISK_1: starting datafile copy input datafile file number=00007 name=/home/oradata/powerdes/pl01.dbf output file name=/home/oradata/powerdes/pl01.dbf tag=TAG20151019T173607 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25 channel ORA_DISK_1: starting datafile copy input datafile file number=00004 name=/home/oradata/powerdes/users01.dbf output file name=/home/oradata/powerdes/users01.dbf tag=TAG20151019T173607 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15 channel ORA_DISK_1: starting datafile copy input datafile file number=00008 name=/home/oradata/powerdes/help01.dbf output file name=/home/oradata/powerdes/help01.dbf tag=TAG20151019T173607 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03 channel ORA_DISK_1: starting datafile copy input datafile file number=00009 name=/home/oradata/powerdes/adobelc01.dbf output file name=/home/oradata/powerdes/adobelc01.dbf tag=TAG20151019T173607 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03 channel ORA_DISK_1: starting datafile copy input datafile file number=00010 name=/home/oradata/powerdes/sms01.dbf output file name=/home/oradata/powerdes/sms01.dbf tag=TAG20151019T173607 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03 channel ORA_DISK_1: starting datafile copy input datafile file number=00013 name=/home/oradata/powerdes/datagm01.dbf output file name=/home/oradata/powerdes/datagm01.dbf tag=TAG20151019T173607 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03 Finished backup at 19-OCT-15 sql statement: alter system archive log current contents of Memory Script: { switch clone datafile all; } executing Memory Script datafile 1 switched to datafile copy input datafile copy RECID=3 STAMP=893526478 file name=/home/oradata/powerdes/system01.dbf datafile 2 switched to datafile copy input datafile copy RECID=4 STAMP=893526478 file name=/home/oradata/powerdes/sysaux01.dbf datafile 3 switched to datafile copy input datafile copy RECID=5 STAMP=893526479 file name=/home/oradata/powerdes/undotbs01.dbf datafile 4 switched to datafile copy input datafile copy RECID=6 STAMP=893526479 file name=/home/oradata/powerdes/users01.dbf datafile 5 switched to datafile copy input datafile copy RECID=7 STAMP=893526479 file name=/home/oradata/powerdes/powerdesk01.dbf datafile 6 switched to datafile copy input datafile copy RECID=8 STAMP=893526479 file name=/home/oradata/powerdes/plas01.dbf datafile 7 switched to datafile copy input datafile copy RECID=9 STAMP=893526479 file name=/home/oradata/powerdes/pl01.dbf datafile 8 switched to datafile copy input datafile copy RECID=10 STAMP=893526479 file name=/home/oradata/powerdes/help01.dbf datafile 9 switched to datafile copy input datafile copy RECID=11 STAMP=893526479 file name=/home/oradata/powerdes/adobelc01.dbf datafile 10 switched to datafile copy input datafile copy RECID=12 STAMP=893526479 file name=/home/oradata/powerdes/sms01.dbf datafile 11 switched to datafile copy input datafile copy RECID=13 STAMP=893526479 file name=/home/oradata/powerdes/plcrm01.dbf datafile 12 switched to datafile copy input datafile copy RECID=14 STAMP=893526479 file name=/home/oradata/powerdes/powerdesk02.dbf datafile 13 switched to datafile copy input datafile copy RECID=15 STAMP=893526479 file name=/home/oradata/powerdes/datagm01.dbf Finished Duplicate Db at 19-OCT-15 RMAN>

4.6，去standby從庫啟動(dòng)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 主備1致性

從庫standby 上check

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

主庫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 NO 5600 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 Mode Automatic archival Enabled Archive destination /oracle/app/oracle/flash_recovery_area/archivelog Oldest online log sequence 38270 Next log sequence to archive 38272 Current log sequence 38272 SQL> SQL> SQL> alter system switch logfile; System altered. SQL>

從庫standby上查詢數(shù)據(jù)：

SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /data/oracle/oradgdata/standby_archive Oldest online log sequence 38271 Next log sequence to archive 0 Current log sequence 38272 SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /data/oracle/oradgdata/standby_archive Oldest online log sequence 38271 Next log sequence to archive 0 Current log sequence 38273 SQL> SQL> select * from z_z_test; ID ---------- 1 SQL>

看到log sequence已增長到38273，日志應(yīng)當(dāng)利用過來了，然后去查詢數(shù)據(jù)臨時(shí)表z_z_test數(shù)據(jù)也已過來了，證明修復(fù)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 file ORA-01110: data file 201: /home/oradata/powerdes/temp01.dbf ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 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 File 201 not verified due to error ORA-01157 Dictionary check complete Re-creating tempfile /home/oradata/powerdes/temp01.dbf Database Characterset is ZHS16GBK No Resource Manager plan active ********************************************************** WARNING: Files may exists in db_recovery_file_dest that are not known to the database. Use the RMAN command CATALOG RECOVERY AREA to re-catalog any such files. If files cannot be cataloged, then manually delete them using 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 open Mon Oct 19 17:53:06 2015 alter database recover managed standby database disconnect from session Attempt to start background Managed Standby Recovery process (powerdes) Mon Oct 19 17:53:06 2015 MRP0 started with pid=48, OS id=15063 MRP0: Background Managed Standby Recovery process started (powerdes) started logmerger process Mon Oct 19 17:53:11 2015 Managed Standby Recovery not using Real Time Apply Parallel Media Recovery started with 16 slaves Waiting 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.dbf Completed: alter database recover managed standby database disconnect from session Media Recovery Waiting for thread 1 sequence 38272 (in transit) Mon Oct 19 18:01:33 2015 Standby controlfile consistent with primary Mon Oct 19 18:01:34 2015 Archived 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 821708334 Mon Oct 19 18:01:39 2015 Media Recovery Log /data/oracle/oradgdata/standby_archive/1_38272_821708334.dbf Media Recovery Waiting for thread 1 sequence 38273 (in transit)

看到有ERROR信息：
Errors

生活不易，碼農(nóng)辛苦
如果您覺得本網(wǎng)站對您的學(xué)習(xí)有所幫助,可以手機(jī)掃描二維碼進(jìn)行捐贈
程序員人生