主、备库日志中断。而且归档日志被删掉的处理方法
Steps to perform for Rolling forward a standby database using RMAN incremental backup when datafile is added to primary (文档 ID 1531031.1)
1.在备库停止MRP进程
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
2.在备库查询scn。
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
SQL> select min(checkpoint_change#) from v$datafile_header;
CHECKPOINT_CHANGE#
---------------------
3162298
3.在主库查询scn(步骤2查询出的scn)。确认这个scn之后增加的数据文件
SQL> SELECT FILE#, NAME FROM V$DATAFILE WHERE CREATION_CHANGE# > <SCN_NUMBER_FROM_STEP 2>;
4.备份文件+scn后的日志
RMAN> backup datafile #, #, #, # format '/tmp/ForStandby_%U' tag 'FORSTANDBY';
RMAN> backup incremental from SCN 3162298 database format '/tmp/ForStandby_%U' tag 'FORSTANDBY';
RMAN> backup current controlfile for standby format '/tmp/ForStandbyCTRL.bck';
5.发送到备库
scp /tmp/ForStandby* standby:/tmp
6.在备库还原控制文件
RMAN> shutdown;
RMAN> startup nomount;
RMAN> restore standby controlfile from '/tmp/ForStandbyCTRL.bck';
RMAN> alter database mount;
RMAN> CATALOG START WITH '/tmp/ForStandby';
using target database control file instead of recovery catalog
searching for all files that match the pattern /tmp/ForStandby
List of Files Unknown to the Database
=====================================
File Name: /tmp/ForStandby_2lkglss4_1_1
File Name: /tmp/ForStandby_2mkglst8_1_1
Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /tmp/ForStandby_2lkglss4_1_1
File Name: /tmp/ForStandby_2mkglst8_1_1
7.还原主库建立的文件
run
{
set newname for datafile X to '+DISKGROUP';
set newname for datafile Y to '+DISKGROUP';
set newname for datafile Z to '+DISKGROUP';
etc.
restore datafile x,y,z,....;
}
8.备库进行switch 操作
RMAN> CATALOG START WITH '+DATA/mystd/datafile/';
List of Files Unknown to the Database
=====================================
File Name: +data/mystd/DATAFILE/SYSTEM.309.685535773
File Name: +data/mystd/DATAFILE/SYSAUX.301.685535773
File Name: +data/mystd/DATAFILE/UNDOTBS1.302.685535775
File Name: +data/mystd/DATAFILE/SYSTEM.297.688213333
File Name: +data/mystd/DATAFILE/SYSAUX.267.688213333
File Name: +data/mystd/DATAFILE/UNDOTBS1.268.688213335
Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: +data/mystd/DATAFILE/SYSTEM.297.688213333
File Name: +data/mystd/DATAFILE/SYSAUX.267.688213333
File Name: +data/mystd/DATAFILE/UNDOTBS1.268.688213335
Once all files have been cataloged, switch the database to copy:
RMAN> SWITCH DATABASE TO COPY;
datafile 1 switched to datafile copy "+DATA/mystd/datafile/system.297.688213333"
datafile 2 switched to datafile copy "+DATA/mystd/datafile/undotbs1.268.688213335"
datafile 3 switched to datafile copy "+DATA/mystd/datafile/sysaux.267.688213333"
9.recover备库
RMAN> RECOVER DATABASE NOREDO;
starting recover at 03-JUN-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=28 devtype=DISK
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: +DATA/mystd/datafile/system.297.688213333
destination for restore of datafile 00002: +DATA/mystd/datafile/undotbs1.268.688213335
destination for restore of datafile 00003: +DATA/mystd/datafile/sysaux.267.688213333
channel ORA_DISK_1: reading from backup piece /tmp/ForStandby_2lkglss4_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/tmp/ForStandby_2lkglss4_1_1 tag=FORSTANDBY
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
Finished recover at 03-JUN-09
10.打开flashback(可选)
SQL> ALTER DATABASE FLASHBACK OFF;
SQL> ALTER DATABASE FLASHBACK ON;
11.清理备库日志
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;
12.开启MRP进程
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
—————————————————————————————————————————————————————————————
Steps to perform for Rolling forward a standby database using RMAN incremental backup when datafile is added to primary (文档 ID 1531031.1)
In this Document
| In ideal situation the above 2 queries will return the almost same SCN. However if there is huge difference its better to take backup using the SCN from second query (lesser SCN), as one of the datafile may be behind. |
APPLIES TO:
Oracle Database - Enterprise Edition - Version 10.2.0.1 to 11.2.0.3 [Release 10.2 to 11.2]
Oracle Database - Enterprise Edition - Version 11.2.0.4 to 11.2.0.4 [Release 11.2]
Oracle Database - Enterprise Edition - Version 12.1.0.2 to 12.1.0.2 [Release 12.1]
Information in this document applies to any platform.
*** Checked for relevance 16-OCT-2016 ***
GOAL
The steps in this section can used to resolve problems if a physical standby database has lost or corrupted archived redo data or has an unresolveable archive gap.
SOLUTION
1) On the standby database, stop the managed recovery process (MRP)
2) On the standby database, find the SCN which will be used for the incremental backup at the primary database:
SQL> select min(checkpoint_change#) from v$datafile_header;
---------------------
3162298
3) In sqlplus, connect to the primary database and identify datafiles added:
SQL> SELECT FILE#, NAME FROM V$DATAFILE WHERE CREATION_CHANGE# > <SCN_NUMBER_FROM_STEP 2>;
4) Using rman, create backup of missing datafiles and an incremental backup using the SCN derived in the previous step:
RMAN> backup datafile #, #, #, # format '/tmp/ForStandby_%U' tag 'FORSTANDBY';
RMAN> backup incremental from SCN 3162298 database format '/tmp/ForStandby_%U' tag 'FORSTANDBY';
RMAN> backup current controlfile for standby format '/tmp/ForStandbyCTRL.bck';
5) Transfer all backup sets created on the primary system to the standby system.
6) Restore new controlfile and catalog the backup transfered in step #5:
RMAN> shutdown;
RMAN> startup nomount;
RMAN> restore standby controlfile from '/tmp/ForStandbyCTRL.bck';
RMAN> alter database mount;
RMAN> CATALOG START WITH '/tmp/ForStandby';
using target database control file instead of recovery catalog
searching for all files that match the pattern /tmp/ForStandby
List of Files Unknown to the Database
=====================================
File Name: /tmp/ForStandby_2lkglss4_1_1
File Name: /tmp/ForStandby_2mkglst8_1_1
Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /tmp/ForStandby_2lkglss4_1_1
File Name: /tmp/ForStandby_2mkglst8_1_1
7) Restore missing datafiles:
run
{
set newname for datafile X to '+DISKGROUP';
set newname for datafile Y to '+DISKGROUP';
set newname for datafile Z to '+DISKGROUP';
etc.
restore datafile x,y,z,....;
}
8) Rename the datafiles in new standby controlfile
Since the controlfile is restored from PRIMARY the datafile locations in STANDBY controlfile will be same as PRIMARY database, so catalog datafiles in STANDBY will do the necessary rename operations.
Perform the below step in STANDBY for each diskgroup where the datafile directory structure between primary and standby are different.
List of Files Unknown to the Database
=====================================
File Name: +data/mystd/DATAFILE/SYSTEM.309.685535773
File Name: +data/mystd/DATAFILE/SYSAUX.301.685535773
File Name: +data/mystd/DATAFILE/UNDOTBS1.302.685535775
File Name: +data/mystd/DATAFILE/SYSTEM.297.688213333
File Name: +data/mystd/DATAFILE/SYSAUX.267.688213333
File Name: +data/mystd/DATAFILE/UNDOTBS1.268.688213335
Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: +data/mystd/DATAFILE/SYSTEM.297.688213333
File Name: +data/mystd/DATAFILE/SYSAUX.267.688213333
File Name: +data/mystd/DATAFILE/UNDOTBS1.268.688213335
Once all files have been cataloged, switch the database to copy:
datafile 1 switched to datafile copy "+DATA/mystd/datafile/system.297.688213333"
datafile 2 switched to datafile copy "+DATA/mystd/datafile/undotbs1.268.688213335"
datafile 3 switched to datafile copy "+DATA/mystd/datafile/sysaux.267.688213333"
9) Recover the standby database with the cataloged incremental backup:
starting recover at 03-JUN-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=28 devtype=DISK
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: +DATA/mystd/datafile/system.297.688213333
destination for restore of datafile 00002: +DATA/mystd/datafile/undotbs1.268.688213335
destination for restore of datafile 00003: +DATA/mystd/datafile/sysaux.267.688213333
channel ORA_DISK_1: reading from backup piece /tmp/ForStandby_2lkglss4_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/tmp/ForStandby_2lkglss4_1_1 tag=FORSTANDBY
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
Finished recover at 03-JUN-09
10) If the standby database needs to be configured for FLASHBACK use the below step to enable.
SQL> ALTER DATABASE FLASHBACK ON;
11) On standby database, clear all standby redo log groups:
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;
....
12) On the standby database, start the MRP
This note assumes all files are at the same recovery scn or very close. If nologging operations have been made or files are at different or widely varying scns see the online documentation:
10.2: http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/scenarios.htm#CIHIAADC
11.1: http://download.oracle.com/docs/cd/B28359_01/server.111/b28294/rman.htm#SBYDB00759
11.2: http://download.oracle.com/docs/cd/E11882_01/server.112/e17022/rman.htm#CIHIAADC
REFERENCES
NOTE:836986.1 - Steps to perform for Rolling Forward a Physical Standby Database using RMAN Incremental Backup.
浙公网安备 33010602011771号