DG unresolvable Gap Gap sequence Backup Depot recovery

Case

Environment:2-node RAC ASM + Single-instance DG standby library with file system to hold data files, unused Asm;rac a node deployed with Ogg.

Partition the table on the main library by online redefinition, create partition tablespace when creating data file on library failed,

Causes the log to upload to the repository cannot be applied, the standby library stops the log application.

Rman Backup Cleanup Archive script can not clean up the archive in a timely manner to the main library, storage archive disk space is full of phenomena, the Ogg process is not working, the process is normal, just time since chkpt continue to rise.

Once the archive has been cleaned up, the repository can receive logs but not apply them. View DG Switchover_status for Unresolvable Gap. The following is a recovery of the standby repository.

Alert log information in the standby repository is as follows:

managed standby recovery starting real time applytue apr 07  08:39:57 2015errors in file /u01/app/oracle/diag/rdbms/centerdg/center/trace/center_dbw0_ 4645.trc:ora-01186: file 63 failed verification testsora-01157: cannot  Identify/lock data file 63 - see dbwr trace fileora-01111: name  for data file 63 is unknown - rename to correct  fileora-01110: data file 63:  '/u01/app/oracle/product/11.2.0/db_1/dbs/unnamed00063 ' file  63 not verified due to error ora-01157errors in file /u01/ App/oracle/diag/rdbms/centerdg/center/trace/center_dbw0_4645.trc:ora-01186: file 201 failed  verification testsora-01157: cannot identify/lock data file 201 -  see dbwr trace fileora-01110: data file 201:  ' +data ' file 201 not verified due to  error ora-01157mrp0: background media recovery terminated with error  1111errors in file /u01/app/oracle/diag/rdbms/centerdg/center/trace/center_pr00_5770.trc : Ora-01111: name for data file 63 is unknown - rename to  correct fileORA-01110: data file 63:  '/u01/app/oracle/product/11.2.0/db_1/dbs/ UNNAMED00063 ' ORA-01157: CANNOT IDENTIFY/LOCK DATA FILE 63 - SEE DBWR  trace fileora-01111: name for data file 63 is unknown -  rename to correct fileORA-01110: data file 63:  '/u01/app/oracle/ product/11.2.0/db_1/dbs/unnamed00063 ' managed standby recovery not using real  Time applyslave exiting&nBsp;with ora-1111 exception 

Processing ideas:

1. Stop Ogg

2. Partition table Space cdtbs_par2-10, idxtbs_2-10 readonly [table space is derived from the following query create_change#> the current SCN number of the repository, where the cdtbs_par1 of the repository is not, but not found out].

3. Copy the data file corresponding to the partition table space.

4. Prepare for recovery.

Specific operation:

1. The table space is read only:

--Prior to this, check the standby SCN number.

Select Current_scn from v$database;10329528031425

--query on the main library

select  ' alter tablespace  ' | | b.name| | '  read only; '  from v$datafile a,v$tablespace b where a.creation_change#> = 10329528031425and a.ts#=b.ts#;alter tablespace cdtbs_par2 read only;alter  Tablespace cdtbs_par3 read only;alter tablespace cdtbs_par4 read only;alter  tablespace cdtbs_par5 read only;alter tablespace cdtbs_par6 read only ; alter tablespace cdtbs_par7 read only;alter tablespace cdtbs_par8 read  only;alter tablespace cdtbs_par9 read only;alter tablespace cdtbs_par10  read only;alter tablespace idxtbs_2 read only;alter tablespace idxtbs _3 read only;alter tablespace idxtbs_4 read only;alter tablespace  Idxtbs_5 read only;alter tablespace idxtbs_6 read only;alter tablespace IDXTBS_7 read only;alter tablespace IDXTBS_8  Read only;alter tablespace idxtbs_9 read only;alter tablespace idxtbs_10  read only;

--because there is no cdtbs_par1 table space corresponding to the data file, the processing method, first read only after the static copy to the repository.

Alter Tablespace CDTBS_PAR1 Read only;

2. Copy the files to the standby library.

First, copy from ASM to the local file system:

Cp cdtbs_par1.345.875609233 /home/gridcp cdtbs_par2.346.875609279  /home/grid/archcp  cdtbs_par3.347.875609293  /home/grid/archcp cdtbs_par4.348.875609307  /home/ grid/archcp cdtbs_par5.349.875609319  /home/grid/archcp cdtbs_par6.350.875609333   /home/grid/archcp cdtbs_par7.351.875609345  /home/grid/archcp cdtbs_ Par8.352.875609359  /home/grid/archcp cdtbs_par9.353.875609371  /home/grid/archcp  cdtbs_par10.354.875609385 /home/grid/archcp idxtbs_10.363.875609811 /home/grid/archcp  idxtbs_2.355.875609535  /home/grid/archcp idxtbs_3.356.875609679  /home/grid/ archcp idxtbs_4.357.875609687  /home/grid/archcp idxtbs_5.358.875609693  /home/ grid/archcp idxtbs_6.359.875609699  /home/grid/archcp idxtbs_7.360.875609707  / Home/grid/archcp idxtbs_8.361.875609713&nBsp; /home/grid/archcp idxtbs_9.362.875609719  /home/grid/arch 

Grid user Direct SCP Copy, example:

SCP cdtbs_par10.354.875609385 [email protected]:/datadg/center/datafilescp cdtbs_par[3,4,5,6]* [email protected]:/ Datadg/center/datafile

--Multiple simultaneous copies, batch test examples:

SCP idxtbs_[2,3,4]* [email protected]:/datadg/center/datafilescp idxtbs_[5,6,7,8,9]* [email protected]:/datadg/ Center/datafile

PS: The file permissions are right after the copy, but the filenames are all converted to lowercase in order to avoid the Linux system file name case sensitive, renaming the file name to lowercase, but not verifying whether the uppercase file name has an effect.

3. The master repository is based on the SCN number of the repository to make backup backups

--Allocate multiple channels to speed up and take longer to recover without multiple channels.

Add on Main Library:

Rman target/run{allocate Channel C1 type disk;allocate channel C2 type Disk;allocate channel C3 type Disk;allocate Channe l C4 type disk; BACKUP INCREMENTAL from SCN 10329528031425 DATABASE FORMAT '/home/oracle/upstd_%u_%p ' tag ' upstd '; release channel C1;rele ASE Channel C2;release channel C3;release channel C4;} Backup current controlfile for standby format '/home/oracle/upstdctl_%u ';

4. Work on the repository to clean up the problematic data files:

Alter system set Standby_file_management=manual;alter database create DataFile '/u01/app/oracle/product/11.2.0/db_1/    dbs/unnamed00063 ' as '/datadg/center/datafile/cdtbs_par1.345.875609233 ';  ALTER DATABASE datafile '/datadg/center/datafile/cdtbs_par1.345.875609233 ' offline drop; --alter database datafile '/u01/app/oracle/product/11.2.0/db_1/dbs/unnamed00063 ' offline drop;  Use this sentence should be equivalent to the above 2 sentences, not verified. --then static read only copies the corresponding data file over the alter system set Standby_file_management=auto;

5. Recovery on the standby library

rman target / catalog start with  '/home/ Oracle/dgbak '; recover database noredo; shutdown immediate; startup nomount; restore standby controlfile from  '/home/oracle/dgbak/upstdctl_9uq3rv22_1_1 ';alter  database mount; rman> alter database open; rman-00571: ===========================================================rman-00569: ===============  error message stack follows ===============rman-00571: ======================== ===================================rman-03002: failure of alter db command at  04/08/2015 16:32:47ora-10458: standby database requires recoveryora-01194:  file 1 needs more recovery to be consistentORA-01110: data  file 1:  '/datadg/center/datafile/system.260.797342827 ' 

To continue the operation on the library:

sql> Set num 50sql> select CURRENT_SCN from v$database; Apply the log sql>alter database RECOVER MANAGED STANDBY Databa SE DISCONNECT from SESSION;  Sql> select File#,status,name from V$datafile; Sql>select * from V$dataguard_status;  Arc4:beginning to archive thread 1 sequence 171370 (10331448528074-10331448899608) sql>alter DATABASE RECOVER MANAGED STANDBY DATABASE Cancel; Sql>alter database open Read only; Sql>alter database RECOVER MANAGED STANDBY database using current logfile DISCONNECT from SESSION;

Additional post-DG related information

The repository Application log and some information in alert when opening to the Read only state:

Some operations on the library on the alert log prompt information, although the display is an error, but in fact, can not tube, just a message hint.

Mount Status Cancellation log application error Ora 16037:

alter database recover managed standby database  CANCELMRP0: Background Media Recovery cancelled with status  16037errors in file /u01/app/oracle/diag/rdbms/centerdg/center/trace/center_pr00_6282.trc:o Ra-16037: user requested cancel of managed recovery operationthu apr  09 08:32:09 2015recovery interrupted! recovered data files to a consistent state at change  10331906716894thu apr 09 08:32:10 2015mrp0: background media recovery  process shutdown  (center) thu apr 09 08:32:10 2015managed standby  recovery canceled  (center) Completed: alter database recover managed standby  database cancelthu apr 09 08:32:38 2015 

Open the standby prompt the temporary tablespace data file cannot be locked, ignored, and the temp table space is automatically processed:

Data guard broker initializing ... Data guard broker initialization completethu apr 09 08:32:41 2015smon:  enabling cache recoverydictionary check beginningthu apr 09 08:32:46  2015RFS[6]: Selected log 26 for thread 2 sequence 189031  dbid -68775212 branch 797342936thu apr 09 08:32:53 2015errors in  file /u01/app/oracle/diag/rdbms/centerdg/center/trace/center_dbw0_4855.trc:ora-01157: cannot  Identify/lock data file 201 - see dbwr trace fileora-01110: data  file 201:  ' +data/center/tempfile/temp.264.797342939 ' ora-17503: ksfdopn:2 failed  to open file +DATA/center/tempfile/temp.264.797342939ORA-15001: diskgroup  "DATA " does not exist or is not mountedora-15077: could not locate asm instance serving a required diskgroupora-29701:  unable to connect to Cluster Synchronization ServiceErrors in  file /u01/app/oracle/diag/rdbms/centerdg/center/trace/center_dbw0_4855.trc:ora-01186: file 201  failed verification testsORA-01157: cannot identify/lock data file  201 - see dbwr trace fileora-01110: data file 201:  ' +DATA/center/ tempfile/temp.264.797342939 ' File 201 not verified due to error ora-01157thu  Apr 09 08:32:53 2015Dictionary check completeCannot re-create  tempfile +data/center/tempfile/temp.264.797342939, the same name file  Existserrors in file /u01/app/oracle/diag/rdbms/centerdg/center/trace/center_dbw0_4855.trc:o Ra-01157: cannot identify/lock data file 201 - see dbwr trace fileora-01110: data  file 201:  ' +data/center/tempfile/temp.264.797342939 ' ora-17503: ksfdopn:2 failed to  open file +DATA/center/tempfile/temp.264.797342939ORA-15001: diskgroup  "DATA"   does not exist or is not mountedora-15077: could not locate  Asm instance serving a required diskgroupora-29701: unable to connect  to cluster synchronization servicedatabase characterset is zhs16gbkno  Resource Manager plan activereplication_dependency_tracking turned off  (No  async multimaster replication found) thu apr 09 08:32:55 2015archived  Log entry 485 added for thread 2 sequence 189030 ID  0xfd3d5b54 dest 1:physical standby database opened for read only access. Completed: alter database open read only

To this repository has been restored successfully.

Enclosed here is the case of the gap sequence repair based on data guard, except that there is no data file created.

Http://www.xifenfei.com/1176.html

This article is from the Oracle Learning blog, so be sure to keep this source http://2874575.blog.51cto.com/2864575/1630563

DG unresolvable Gap Gap sequence Backup Depot recovery