問題描述:
機房斷電了，所以primary和standby庫都是直接斷電，然後我都設置了開機自啟動oracle，所以第二天我來看的時候，primary和standby都啟動了，歸檔日志也傳輸到standby了，但是日志應用後報錯，有文件壞塊，所以需要修復。
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: [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.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 遠程恢復。
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 bytes
Fixed Size 2213136 bytes
Variable Size 3489663728 bytes
Database Buffers 1811939328 bytes
Redo Buffers 40914944 bytes
SQL>
4.5 在primary上操作主庫上
rman target sys/[email protected] auxiliary sys/[email protected]
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 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 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從庫啟動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 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上查詢數據：
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，日志應該應用過來了，然後去查詢數據臨時表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 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 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）最後在晚上進行恢復
