Nothing but my learning and experience with Oracle..!: May 2016

A Physical Standby database synchs with Primary by continuous apply of archivelogs from a Primary Database. In case of an archivelog gone missing or corrupt you have to rebuild the standby database from scratch but in 10g, an incremental backup created with BACKUP INCREMENTAL... FROM SCN can be used to refresh the standby database with changes at the primary database since the last SCN at Standby and then managed recovery can resume i.e. Compensate for the missing archivelogs.

In 12c, this procedure has been dramatically simplified. Now you can use the RECOVER DATABASE FROM SERVICE command to synchronize the physical standby database with the primary database. Here I am discussing about how we can Resolve Gaps in Data Guard Apply Using RMAN Incremental backup .

Here is my database details that are configurared in dataguard

TFREC – primary database

TGREC – standby database

when I checked my dataguard sync status,I could seemy standby database is waiting for archive log 324 and it was got deleted from my primary database.

SQL> select process,status,sequence#,block#,blocks from v$managed_standby;

PROCESS STATUS SEQUENCE# BLOCK# BLOCKS

--------- ------------ ---------- ---------- ----------

ARCH CONNECTED 0 0 0

ARCH CLOSING 330 6144 2011

ARCH CONNECTED 0 0 0

ARCH CLOSING 358 8192 1700

RFS IDLE 0 0 0

RFS RECEIVING 359 39 2

RFS RECEIVING 331 9415 1

RFS IDLE 0 0 0

MRP0 WAIT_FOR_GAP 324 0 0

9 rows selected.

On the STANDBY DATABASE, find the SCN which will be used for the incremental backup at the primary database

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

5.9667E+12

SQL> set numw 45

SQL> select current_scn from v$database;

CURRENT_SCN

--------------

5966736651663

While picking the SCN you have to consider smallest SCN , following notes are from oracle note 836986.1 and the output are not from my emviorement

Determine the SCN of the STANDBY database.

On the standby database, find the SCN which will be used for the incremental backup at the primary database:

You need to use the 'lowest SCN' from the queries below:

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
--------------
3164433
SQL> select min(checkpoint_change#) from v$datafile_header
where file# not in (select file# from v$datafile where enabled = 'READ ONLY');

MIN(F.FHSCN)
----------------
3162298

You need to use the 'lowest SCN' from the queries, in this example is SCN: 3162298. Therefore, From the above you need to backup from SCN 3162298

Cancel managed recovery at the standby database and start it up in mount mode.

SQL> recover managed standby database cancel ;

Media recovery complete.

SQL>

SQL> shut immediate ;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL>

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Advanced Analytics and Real Application Testing options

[oracle@stbyex]:[TGREC1] $ ss

SQL*Plus: Release 12.1.0.2.0 Production on Thu May 26 18:10:47 2016

Connected to an idle instance.

SQL>startup mount;

ORACLE instance started.

Total System Global Area 658505728 bytes

Fixed Size 2927720 bytes

Variable Size 545260440 bytes

Database Buffers 100663296 bytes

Redo Buffers 9654272 bytes

Database mounted.

SQL> exit

On the primary database, create an incremental backup from the above SCN ,I took rman backups on a nfs mount point and is available on my standby side – so no

need to transfer backup piece

[oracle@primex]:[TFREC1] $ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Thu May 26 18:11:36 2016

connected to target database: TFREC (DBID=2863871221)

RMAN> backup incremental from scn 5966736651663 database format '/t4_nfs_2/RECC/TFREC_%d_%T_%s';

Starting backup at 26-MAY-16

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=327 instance=TFREC1 device type=DISK

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00002 name=+ORADATA/tfrec/datafile/sysaux.1782.865941481

input datafile file number=00001 name=+ORADATA/tfrec/datafile/system.306.865941481

input datafile file number=00003 name=+ORADATA/tfrec/datafile/undotbs1.1764.865941481

input datafile file number=00005 name=+ORADATA/tfrec/datafile/undotbs2.1046.865941621

input datafile file number=00006 name=+ORADATA/tfrec/datafile/data_recon_tbs.1432.865942755

input datafile file number=00004 name=+ORADATA/tfrec/datafile/users.1133.865941483

channel ORA_DISK_1: starting piece 1 at 26-MAY-16

channel ORA_DISK_1: finished piece 1 at 26-MAY-16

piece handle=/t4_nfs_2/RECC/TFREC_TFREC_20160526_2403 tag=TAG20160526T181238 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

channel ORA_DISK_1: starting piece 1 at 26-MAY-16

channel ORA_DISK_1: finished piece 1 at 26-MAY-16

piece handle=/t4_nfs_2/RECC/TFREC_TFREC_20160526_2404 tag=TAG20160526T181238 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 26-MAY-16

RMAN>

Create latest control file for standby database

RMAN> backup current controlfile for standby format '/t4_nfs_2/RECC/stnd_%U.ctl';

Starting backup at 26-MAY-16

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including standby control file in backup set

channel ORA_DISK_1: starting piece 1 at 26-MAY-16

channel ORA_DISK_1: finished piece 1 at 26-MAY-16

piece handle=/t4_nfs_2/RECC/stnd_b5r6ituj_1_1.ctl tag=TAG20160526T181339 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 26-MAY-16

RMAN>

RMAN> exit

Recovery Manager complete.

[oracle@primex]:[TFREC1] $ ss

SQL*Plus: Release 12.1.0.2.0 Production on Thu May 26 18:24:38 2016

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,

OLAP, Advanced Analytics, Oracle Database Vault and Real Application Testing options

SQL> select * from test;

----------

3 rows selected.

SQL> insert into test values (110);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test;

----------

110

4 rows selected.

Copy backups to standby

I logged on to my standby database

[oracle@stbyex]:[TGREC1] $ pwd

/t4_nfs_2/RECC

[oracle@stbyex]:[TGREC1] $ ll

total 69608

-rw-r----- 1 test asmadmin 21069824 May 26 18:13 stnd_b5r6ituj_1_1.ctl

-rw-r----- 1 test asmadmin 29138944 May 26 18:13 TFREC_TFREC_20160526_2403

-rw-r----- 1 test asmadmin 21069824 May 26 18:13 TFREC_TFREC_20160526_2404

[oracle@stbyex]:[TGREC1] $

Catalog the Incremental Backup Files at the Standby Database – note that my standy is in mount mode

[oracle@stbyex]:[TGREC1] $ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Thu May 26 18:14:02 2016

connected to target database: TFREC (DBID=2863871221, not open)

RMAN> catalog start with '/t4_nfs_2/RECC';

using target database control file instead of recovery catalog

searching for all files that match the pattern /t4_nfs_2/RECC

List of Files Unknown to the Database

=====================================

File Name: /t4_nfs_2/RECC/TFREC_TFREC_20160526_2404

File Name: /t4_nfs_2/RECC/TFREC_TFREC_20160526_2403

File Name: /t4_nfs_2/RECC/stnd_b5r6ituj_1_1.ctl

Do you really want to catalog the above files (enter YES or NO)? YES

cataloging files...

cataloging done

List of Cataloged Files

=======================

File Name: /t4_nfs_2/RECC/TFREC_TFREC_20160526_2404

File Name: /t4_nfs_2/RECC/TFREC_TFREC_20160526_2403

File Name: /t4_nfs_2/RECC/stnd_b5r6ituj_1_1.ctl

Apply the Incremental Backup to the Standby Database:

RMAN> recover database noredo;

Starting recover at 26-MAY-16

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=2 device type=DISK

channel ORA_DISK_1: starting incremental datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

destination for restore of datafile 00001: +ORADATA/TGREC/DATAFILE/system.2543.912871009

destination for restore of datafile 00002: +ORADATA/TGREC/DATAFILE/sysaux.2542.912871009

destination for restore of datafile 00003: +ORADATA/TGREC/DATAFILE/undotbs1.2544.912871055

destination for restore of datafile 00004: +ORADATA/TGREC/DATAFILE/users.2547.912871105

destination for restore of datafile 00005: +ORADATA/TGREC/DATAFILE/undotbs2.2545.912871089

destination for restore of datafile 00006: +ORADATA/TGREC/DATAFILE/data_recon_tbs.2546.912871097

channel ORA_DISK_1: reading from backup piece /t4_nfs_2/RECC/TFREC_TFREC_20160526_2403

channel ORA_DISK_1: piece handle=/t4_nfs_2/RECC/TFREC_TFREC_20160526_2403 tag=TAG20160526T181238

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:15

Finished recover at 26-MAY-16

RMAN>

Find out the actual location of data files , before restoring the controlfile

SQL> select name from v$datafile ;

NAME

--------------------------------------------------------------------------------

+ORADATA/TGREC/DATAFILE/system.2543.912871009

+ORADATA/TGREC/DATAFILE/sysaux.2542.912871009

+ORADATA/TGREC/DATAFILE/undotbs1.2544.912871055

+ORADATA/TGREC/DATAFILE/users.2547.912871105

+ORADATA/TGREC/DATAFILE/undotbs2.2545.912871089

+ORADATA/TGREC/DATAFILE/data_recon_tbs.2546.912871097

6 rows selected.

SQL> exit

Also you can verify the redo logfiles location

SQL> select member from v$logfile ;

MEMBER

--------------------------------------------------------------------------------

+ORADATA/TGREC/ONLINELOG/group_2.2549.912871325

+ORAFRA/TGREC/ONLINELOG/group_2.24670.912871325

+ORADATA/TGREC/ONLINELOG/group_1.2548.912871323

+ORAFRA/TGREC/ONLINELOG/group_1.24677.912871325

+ORADATA/TGREC/ONLINELOG/group_3.2550.912871325

+ORAFRA/TGREC/ONLINELOG/group_3.24668.912871325

+ORADATA/TGREC/ONLINELOG/group_4.2551.912871325

+ORAFRA/TGREC/ONLINELOG/group_4.24666.912871325

+ORADATA/TGREC/ONLINELOG/group_5.2552.912871325

+ORAFRA/TGREC/ONLINELOG/group_5.24663.912871325

+ORADATA/TGREC/ONLINELOG/group_6.2553.912871325

+ORAFRA/TGREC/ONLINELOG/group_6.24661.912871325

+ORADATA/TGREC/ONLINELOG/group_7.2554.912871325

+ORAFRA/TGREC/ONLINELOG/group_7.24654.912871325

+ORADATA/TGREC/ONLINELOG/group_8.2555.912871325

+ORAFRA/TGREC/ONLINELOG/group_8.24643.912871325

+ORADATA/TGREC/ONLINELOG/group_9.2556.912871327

+ORAFRA/TGREC/ONLINELOG/group_9.24641.912871327

+ORADATA/TGREC/ONLINELOG/group_10.2557.912871327

+ORAFRA/TGREC/ONLINELOG/group_10.24639.912871327

20 rows selected.

SQL> shut abort ;

ORACLE instance shut down.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 658505728 bytes

Fixed Size 2927720 bytes

Variable Size 545260440 bytes

Database Buffers 100663296 bytes

Redo Buffers 9654272 bytes

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics

and Real Application Testing options

Connect to STANDBY database and restore the standby control file:

[oracle@stbyex]:[TGREC1] $ ll

total 69608

-rw-r----- 1 test asmadmin 21069824 May 26 18:13 stnd_b5r6ituj_1_1.ctl

-rw-r----- 1 test asmadmin 29138944 May 26 18:13 TFREC_TFREC_20160526_2403

-rw-r----- 1 test asmadmin 21069824 May 26 18:13 TFREC_TFREC_20160526_2404

[oracle@stbyex]:[TGREC1] $ pwd

/t4_nfs_2/RECC

[oracle@stbyex]:[TGREC1] $ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Thu May 26 18:20:14 2016

connected to target database: TFREC (not mounted)

RMAN> restore standby controlfile from '/t4_nfs_2/RECC/stnd_b5r6ituj_1_1.ctl';

Starting restore at 26-MAY-16

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=834 device type=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:03

output file name=+ORADATA/TGREC/CONTROLFILE/control1.ctl

Finished restore at 26-MAY-16

RMAN>

RMAN> shutdown immediate ;

Oracle instance shut down

RMAN> startup mount ;

connected to target database (not started)

Oracle instance started

database mounted

Total System Global Area 658505728 bytes

Fixed Size 2927720 bytes

Variable Size 545260440 bytes

Database Buffers 100663296 bytes

Redo Buffers 9654272 bytes

RMAN> select member from v$logfile;

MEMBER

--------------------------------------------------------------------------------

+ORADATA/tfrec/onlinelog/group_2.1425.865941559

+ORAFRA/tfrec/onlinelog/group_2.17494.865941559

+ORADATA/tfrec/onlinelog/group_1.338.865941559

+ORAFRA/tfrec/onlinelog/group_1.13643.865941559

+ORADATA/tfrec/onlinelog/group_3.1044.865941691

+ORAFRA/tfrec/onlinelog/group_3.17475.865941691

+ORADATA/tfrec/onlinelog/group_4.1298.865941691

+ORAFRA/tfrec/onlinelog/group_4.17490.865941691

+ORADATA/TFREC/ONLINELOG/group_5.9397.912869649

+ORAFRA/TFREC/ONLINELOG/group_5.11747.912869649

+ORADATA/TFREC/ONLINELOG/group_6.9381.912869649

+ORAFRA/TFREC/ONLINELOG/group_6.18570.912869649

+ORADATA/TFREC/ONLINELOG/group_7.9374.912869649

+ORAFRA/TFREC/ONLINELOG/group_7.9606.912869649

+ORADATA/TFREC/ONLINELOG/group_8.9365.912869655

+ORAFRA/TFREC/ONLINELOG/group_8.23937.912869655

+ORADATA/TFREC/ONLINELOG/group_9.9362.912869655

+ORAFRA/TFREC/ONLINELOG/group_9.2150.912869655

+ORADATA/TFREC/ONLINELOG/group_10.9357.912869655

+ORAFRA/TFREC/ONLINELOG/group_10.5961.912869655

20 rows selected

RMAN> select name from v$datafile ;

NAME

--------------------------------------------------------------------------------

+ORADATA/tfrec/datafile/system.306.865941481

+ORADATA/tfrec/datafile/sysaux.1782.865941481

+ORADATA/tfrec/datafile/undotbs1.1764.865941481

+ORADATA/tfrec/datafile/users.1133.865941483

+ORADATA/tfrec/datafile/undotbs2.1046.865941621

+ORADATA/tfrec/datafile/data_recon_tbs.1432.865942755

RMAN> catalog start with '+ORADATA/TGREC/DATAFILE';

Starting implicit crosscheck backup at 26-MAY-16

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=770 device type=DISK

Crosschecked 37 objects

Finished implicit crosscheck backup at 26-MAY-16

Starting implicit crosscheck copy at 26-MAY-16

using channel ORA_DISK_1

Finished implicit crosscheck copy at 26-MAY-16

searching for all files in the recovery area

cataloging files...

cataloging done

List of Cataloged Files

=======================

File Name: +ORAFRA/TGREC/ARCHIVELOG/2016_05_26/thread_2_seq_317.24739.912871159

File Name: +ORAFRA/TGREC/ARCHIVELOG/2016_05_26/thread_1_seq_346.24731.912871159

File Name: +ORAFRA/TGREC/ARCHIVELOG/2016_05_26/thread_2_seq_319.24728.912871161

File Name: +ORAFRA/TGREC/ARCHIVELOG/2016_05_26/thread_1_seq_345.24720.912871161

File Name: +ORAFRA/TGREC/ARCHIVELOG/2016_05_26/thread_2_seq_318.24718.912871161

File Name: +ORAFRA/TGREC/ARCHIVELOG/2016_05_26/thread_1_seq_347.24687.912871161

File Name: +ORAFRA/TGREC/ARCHIVELOG/2016_05_26/thread_2_seq_320.24685.912871163

File Name: +ORAFRA/TGREC/ARCHIVELOG/2016_05_26/thread_1_seq_348.24637.912871859

File Name: +ORAFRA/TGREC/ARCHIVELOG/2016_05_26/thread_1_seq_349.24633.912872065

File Name: +ORAFRA/TGREC/ARCHIVELOG/2016_05_26/thread_1_seq_350.24630.912872069

File Name: +ORAFRA/TGREC/ARCHIVELOG/2016_05_26/thread_1_seq_351.24626.912872163

File Name: +ORAFRA/TGREC/ARCHIVELOG/2016_05_26/thread_2_seq_321.23304.912872255

File Name: +ORAFRA/TGREC/ARCHIVELOG/2016_05_26/thread_2_seq_322.25065.912872311

File Name: +ORAFRA/TGREC/ARCHIVELOG/2016_05_26/thread_2_seq_323.25061.912872527

File Name: +ORAFRA/TGREC/ARCHIVELOG/2016_05_26/thread_1_seq_352.25055.912872649

File Name: +ORAFRA/TGREC/ARCHIVELOG/2016_05_26/thread_1_seq_354.25052.912872869

File Name: +ORAFRA/TGREC/ARCHIVELOG/2016_05_26/thread_2_seq_327.25048.912872871

File Name: +ORAFRA/TGREC/ARCHIVELOG/2016_05_26/thread_2_seq_328.25034.912873307

File Name: +ORAFRA/TGREC/ARCHIVELOG/2016_05_26/thread_1_seq_355.25032.912873307

File Name: +ORAFRA/TGREC/ARCHIVELOG/2016_05_26/thread_2_seq_329.25021.912877007

File Name: +ORAFRA/TGREC/ARCHIVELOG/2016_05_26/thread_1_seq_356.25013.912877007

File Name: +ORAFRA/TGREC/ARCHIVELOG/2016_05_26/thread_2_seq_330.25010.912878615

File Name: +ORAFRA/TGREC/ARCHIVELOG/2016_05_26/thread_1_seq_357.24996.912878617

File Name: +ORAFRA/TGREC/ARCHIVELOG/2016_05_26/thread_1_seq_358.24990.912881233

File Name: +ORAFRA/TGREC/ARCHIVELOG/2016_05_26/thread_2_seq_331.24983.912881467

File Name: +ORAFRA/TGREC/ARCHIVELOG/2016_05_26/thread_1_seq_359.24977.912881467

File Name: +ORAFRA/TGREC/ARCHIVELOG/2016_05_26/thread_1_seq_360.24974.912881785

File Name: +ORAFRA/TGREC/ARCHIVELOG/2016_05_26/thread_2_seq_332.24973.912881789

searching for all files that match the pattern +ORADATA/TGREC/DATAFILE

List of Files Unknown to the Database

=====================================

File Name: +ORADATA/TGREC/DATAFILE/sysaux.617.877823221

File Name: +ORADATA/TGREC/DATAFILE/system.618.877823223

File Name: +ORADATA/TGREC/DATAFILE/undotbs1.622.877823223

File Name: +ORADATA/TGREC/DATAFILE/data_recon_tbs.623.877823223

File Name: +ORADATA/TGREC/DATAFILE/users.624.877823223

File Name: +ORADATA/TGREC/DATAFILE/sysaux.2542.912871009

File Name: +ORADATA/TGREC/DATAFILE/system.2543.912871009

File Name: +ORADATA/TGREC/DATAFILE/undotbs1.2544.912871055

File Name: +ORADATA/TGREC/DATAFILE/undotbs2.2545.912871089

File Name: +ORADATA/TGREC/DATAFILE/data_recon_tbs.2546.912871097

File Name: +ORADATA/TGREC/DATAFILE/users.2547.912871105

Do you really want to catalog the above files (enter YES or NO)? yes

cataloging files...

cataloging done

List of Cataloged Files

=======================

File Name: +ORADATA/TGREC/DATAFILE/sysaux.2542.912871009

File Name: +ORADATA/TGREC/DATAFILE/system.2543.912871009

File Name: +ORADATA/TGREC/DATAFILE/undotbs1.2544.912871055

File Name: +ORADATA/TGREC/DATAFILE/undotbs2.2545.912871089

File Name: +ORADATA/TGREC/DATAFILE/data_recon_tbs.2546.912871097

File Name: +ORADATA/TGREC/DATAFILE/users.2547.912871105

List of Files Which Were Not Cataloged

=======================================

File Name: +ORADATA/TGREC/DATAFILE/sysaux.617.877823221

RMAN-07519: Reason: Error while cataloging. See alert.log.

File Name: +ORADATA/TGREC/DATAFILE/system.618.877823223

RMAN-07519: Reason: Error while cataloging. See alert.log.

File Name: +ORADATA/TGREC/DATAFILE/undotbs1.622.877823223

RMAN-07519: Reason: Error while cataloging. See alert.log.

File Name: +ORADATA/TGREC/DATAFILE/data_recon_tbs.623.877823223

RMAN-07519: Reason: Error while cataloging. See alert.log.

File Name: +ORADATA/TGREC/DATAFILE/users.624.877823223

RMAN-07519: Reason: Error while cataloging. See alert.log.

RMAN> switch database to copy;

datafile 1 switched to datafile copy "+ORADATA/TGREC/DATAFILE/system.2543.912871009"

datafile 2 switched to datafile copy "+ORADATA/TGREC/DATAFILE/sysaux.2542.912871009"

datafile 3 switched to datafile copy "+ORADATA/TGREC/DATAFILE/undotbs1.2544.912871055"

datafile 4 switched to datafile copy "+ORADATA/TGREC/DATAFILE/users.2547.912871105"

datafile 5 switched to datafile copy "+ORADATA/TGREC/DATAFILE/undotbs2.2545.912871089"

datafile 6 switched to datafile copy "+ORADATA/TGREC/DATAFILE/data_recon_tbs.2546.912871097"

RMAN>

Recovery Manager complete.

Note:-

Every database data file will need to be cataloged in order to create recoverable copies. Attempting to switch the standby database without cataloging every data file will result in an error. For example – its not specific to my environment ,just for reference I took it out from internet .

RMAN> switch database to copy;

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of switch to copy command at 03/01/2011 18:11:57

RMAN-06571: datafile 8 does not have recoverable copy

SQL> select file#, name from v$datafile

2 where name not like '/u02/oradata/TURLOCK/datafile/%';

FILE# NAME

------- ------------------------------------

8 /u05/oradata/TURLOCK/data02.dbf

9 /u05/oradata/TURLOCK/data03.dbf

RMAN> catalog datafilecopy '/u05/oradata/TURLOCK/data02.dbf';

RMAN> catalog datafilecopy '/u05/oradata/TURLOCK/data03.dbf';

RMAN> list copy;

specification does not match any archive log in the recovery catalog

List of Datafile Copies

Key File S Completion Time Ckp SCN Ckp Time Name

------- ---- - -------------------- ---------- -------------------- ----

5 1 A 01-MAR-2011 20:08:55 3752351 01-MAR-2011 20:02:22 /u02/oradata/TURLOCK/datafile/o1_mf_system_6p58khk7_.dbf

1 2 A 01-MAR-2011 20:08:55 3752351 01-MAR-2011 20:02:22 /u02/oradata/TURLOCK/datafile/o1_mf_undotbs1_6p58khol_.dbf

3 3 A 01-MAR-2011 20:08:55 3752351 01-MAR-2011 20:02:22 /u02/oradata/TURLOCK/datafile/o1_mf_sysaux_6p58khm3_.dbf

7 4 A 01-MAR-2011 20:08:55 3752351 01-MAR-2011 20:02:22 /u02/oradata/TURLOCK/datafile/o1_mf_example_6p58khqd_.dbf

2 5 A 01-MAR-2011 20:08:55 3752351 01-MAR-2011 20:02:22 /u02/oradata/TURLOCK/datafile/o1_mf_users_6p58khrt_.dbf

4 6 A 01-MAR-2011 20:08:55 3752351 01-MAR-2011 20:02:22 /u02/oradata/TURLOCK/datafile/o1_mf_data1_6p58kht7_.dbf

6 7 A 01-MAR-2011 20:08:55 3752351 01-MAR-2011 20:02:22 /u02/oradata/TURLOCK/datafile/o1_mf_data2_6p58khvn_.dbf

8 8 A 01-MAR-2011 20:09:14 3752351 01-MAR-2011 20:02:22 /u05/oradata/TURLOCK/data02.dbf

9 9 A 01-MAR-2011 20:09:27 3752351 01-MAR-2011 20:02:22 /u05/oradata/TURLOCK/data03.dbf

[oracle@stbyex]:[TGREC1] $ ss

SQL*Plus: Release 12.1.0.2.0 Production on Thu May 26 18:23:42 2016

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Advanced Analytics and Real Application Testing options

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select process,status,sequence#,block#,blocks from v$managed_standby;

PROCESS STATUS SEQUENCE# BLOCK# BLOCKS

--------- ------------ ---------- ---------- ----------

ARCH CONNECTED 0 0 0

RFS RECEIVING 362 215 1

RFS IDLE 0 0 0

RFS RECEIVING 334 152 1

RFS IDLE 0 0 0

MRP0 WAIT_FOR_LOG 362 0 0

11 rows selected.

SQL>

SQL> select member from v$logfile

2 ;

MEMBER

--------------------------------------------------------------------------------

+ORADATA/TGREC/ONLINELOG/group_2.2560.912882251

+ORAFRA/TGREC/ONLINELOG/group_2.22976.912882251

+ORADATA/TGREC/ONLINELOG/group_1.2559.912882249

+ORAFRA/TGREC/ONLINELOG/group_1.22961.912882251

+ORADATA/TGREC/ONLINELOG/group_3.2561.912882251

+ORAFRA/TGREC/ONLINELOG/group_3.22987.912882251

+ORADATA/TGREC/ONLINELOG/group_4.2562.912882251

+ORAFRA/TGREC/ONLINELOG/group_4.22995.912882251

+ORADATA/TFREC/ONLINELOG/group_5.9397.912869649

+ORAFRA/TFREC/ONLINELOG/group_5.11747.912869649

+ORADATA/TFREC/ONLINELOG/group_6.9381.912869649

+ORAFRA/TFREC/ONLINELOG/group_6.18570.912869649

+ORADATA/TFREC/ONLINELOG/group_7.9374.912869649

+ORAFRA/TFREC/ONLINELOG/group_7.9606.912869649

+ORADATA/TFREC/ONLINELOG/group_8.9365.912869655

+ORAFRA/TFREC/ONLINELOG/group_8.23937.912869655

+ORADATA/TFREC/ONLINELOG/group_9.9362.912869655

+ORAFRA/TFREC/ONLINELOG/group_9.2150.912869655

+ORADATA/TFREC/ONLINELOG/group_10.9357.912869655

+ORAFRA/TFREC/ONLINELOG/group_10.5961.912869655

20 rows selected.

SQL>

SQL> recover managed standby database cancel;

Media recovery complete.

SQL>

SQL> alter database open read only;

Database altered.

SQL>

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select * from test;

----------

SQL> select process,status,sequence#,block#,blocks from v$managed_standby;

PROCESS STATUS SEQUENCE# BLOCK# BLOCKS

--------- ------------ ---------- ---------- ----------

ARCH CONNECTED 0 0 0

RFS RECEIVING 362 393 1

RFS IDLE 0 0 0

RFS RECEIVING 334 309 1

RFS IDLE 0 0 0

MRP0 WAIT_FOR_LOG 362 0 0

11 rows selected.

SQL> select * from test;

----------

My MRP is active but I couldn’t see the change that I made in primary database , so I looked by alert logs file I can see the following error

[

[oracle@stbyex]:[TGREC1] $ cat alert_TGREC1.log

ORA-17503: ksfdopn:2 Failed to open file +ORAFRA/TFREC/ONLINELOG/group_6.18570.912869649

ORA-15173: entry 'TFREC' does not exist in directory '/'

ORA-00312: online log 6 thread 1: '+ORADATA/TFREC/ONLINELOG/group_6.9381.912869649'

ORA-17503: ksfdopn:2 Failed to open file +ORADATA/TFREC/ONLINELOG/group_6.9381.912869649

ORA-15173: entry 'TFREC' does not exist in directory '/'

Completed: ALTER DATABASE CLEAR LOGFILE GROUP 6

ALTER DATABASE CLEAR LOGFILE GROUP 7

Thu May 26 18:55:17 2016

Clearing online log 7 of thread 1 sequence number 0

Thu May 26 18:55:17 2016

Errors in file /u01/app/oracle/diag/rdbms/tgrec/TGREC1/trace/TGREC1_ora_24941.trc:

ORA-00313: open failed for members of log group 7 of thread 1

ORA-00312: online log 7 thread 1: '+ORAFRA/TFREC/ONLINELOG/group_7.9606.912869649'

ORA-17503: ksfdopn:2 Failed to open file +ORAFRA/TFREC/ONLINELOG/group_7.9606.912869649

ORA-15173: entry 'TFREC' does not exist in directory '/'

ORA-00312: online log 7 thread 1: '+ORADATA/TFREC/ONLINELOG/group_7.9374.912869649'

ORA-17503: ksfdopn:2 Failed to open file +ORADATA/TFREC/ONLINELOG/group_7.9374.912869649

ORA-15173: entry 'TFREC' does not exist in directory '/'

Thu May 26 18:55:17 2016

Errors in file /u01/app/oracle/diag/rdbms/tgrec/TGREC1/trace/TGREC1_ora_24941.trc:

ORA-00313: open failed for members of log group 7 of thread 1

ORA-00312: online log 7 thread 1: '+ORAFRA/TFREC/ONLINELOG/group_7.9606.912869649'

ORA-17503: ksfdopn:2 Failed to open file +ORAFRA/TFREC/ONLINELOG/group_7.9606.912869649

ORA-15173: entry 'TFREC' does not exist in directory '/'

ORA-00312: online log 7 thread 1: '+ORADATA/TFREC/ONLINELOG/group_7.9374.912869649'

ORA-17503: ksfdopn:2 Failed to open file +ORADATA/TFREC/ONLINELOG/group_7.9374.912869649

ORA-15173: entry 'TFREC' does not exist in directory '/'

Completed: ALTER DATABASE CLEAR LOGFILE GROUP 7

ALTER DATABASE CLEAR LOGFILE GROUP 8

Clearing online log 8 of thread 2 sequence number 0

Thu May 26 18:55:22 2016

Errors in file /u01/app/oracle/diag/rdbms/tgrec/TGREC1/trace/TGREC1_ora_24941.trc:

ORA-00313: open failed for members of log group 8 of thread 2

ORA-00312: online log 8 thread 2: '+ORAFRA/TFREC/ONLINELOG/group_8.23937.912869655'

ORA-17503: ksfdopn:2 Failed to open file +ORAFRA/TFREC/ONLINELOG/group_8.23937.912869655

ORA-15173: entry 'TFREC' does not exist in directory '/'

ORA-00312: online log 8 thread 2: '+ORADATA/TFREC/ONLINELOG/group_8.9365.912869655'

ORA-17503: ksfdopn:2 Failed to open file +ORADATA/TFREC/ONLINELOG/group_8.9365.912869655

ORA-15173: entry 'TFREC' does not exist in directory '/'

Thu May 26 18:55:22 2016

Errors in file /u01/app/oracle/diag/rdbms/tgrec/TGREC1/trace/TGREC1_ora_24941.trc:

ORA-00313: open failed for members of log group 8 of thread 2

ORA-00312: online log 8 thread 2: '+ORAFRA/TFREC/ONLINELOG/group_8.23937.912869655'

ORA-17503: ksfdopn:2 Failed to open file +ORAFRA/TFREC/ONLINELOG/group_8.23937.912869655

ORA-15173: entry 'TFREC' does not exist in directory '/'

ORA-00312: online log 8 thread 2: '+ORADATA/TFREC/ONLINELOG/group_8.9365.912869655'

ORA-17503: ksfdopn:2 Failed to open file +ORADATA/TFREC/ONLINELOG/group_8.9365.912869655

ORA-15173: entry 'TFREC' does not exist in directory '/'

Completed: ALTER DATABASE CLEAR LOGFILE GROUP 8

ALTER DATABASE CLEAR LOGFILE GROUP 9

Thu May 26 18:55:28 2016

Clearing online log 9 of thread 2 sequence number 0

Thu May 26 18:55:28 2016

Errors in file /u01/app/oracle/diag/rdbms/tgrec/TGREC1/trace/TGREC1_ora_24941.trc:

ORA-00313: open failed for members of log group 9 of thread 2

ORA-00312: online log 9 thread 2: '+ORAFRA/TFREC/ONLINELOG/group_9.2150.912869655'

ORA-17503: ksfdopn:2 Failed to open file +ORAFRA/TFREC/ONLINELOG/group_9.2150.912869655

ORA-15173: entry 'TFREC' does not exist in directory '/'

ORA-00312: online log 9 thread 2: '+ORADATA/TFREC/ONLINELOG/group_9.9362.912869655'

ORA-17503: ksfdopn:2 Failed to open file +ORADATA/TFREC/ONLINELOG/group_9.9362.912869655

ORA-15173: entry 'TFREC' does not exist in directory '/'

Thu May 26 18:55:28 2016

Errors in file /u01/app/oracle/diag/rdbms/tgrec/TGREC1/trace/TGREC1_ora_24941.trc:

ORA-00313: open failed for members of log group 9 of thread 2

ORA-00312: online log 9 thread 2: '+ORAFRA/TFREC/ONLINELOG/group_9.2150.912869655'

ORA-17503: ksfdopn:2 Failed to open file +ORAFRA/TFREC/ONLINELOG/group_9.2150.912869655

ORA-15173: entry 'TFREC' does not exist in directory '/'

ORA-00312: online log 9 thread 2: '+ORADATA/TFREC/ONLINELOG/group_9.9362.912869655'

ORA-17503: ksfdopn:2 Failed to open file +ORADATA/TFREC/ONLINELOG/group_9.9362.912869655

ORA-15173: entry 'TFREC' does not exist in directory '/'

Completed: ALTER DATABASE CLEAR LOGFILE GROUP 9

ALTER DATABASE CLEAR LOGFILE GROUP 10

Clearing online log 10 of thread 2 sequence number 0

Thu May 26 18:55:35 2016

Errors in file /u01/app/oracle/diag/rdbms/tgrec/TGREC1/trace/TGREC1_ora_24941.trc:

ORA-00313: open failed for members of log group 10 of thread 2

ORA-00312: online log 10 thread 2: '+ORAFRA/TFREC/ONLINELOG/group_10.5961.912869655'

ORA-17503: ksfdopn:2 Failed to open file +ORAFRA/TFREC/ONLINELOG/group_10.5961.912869655

ORA-15173: entry 'TFREC' does not exist in directory '/'

ORA-00312: online log 10 thread 2: '+ORADATA/TFREC/ONLINELOG/group_10.9357.912869655'

ORA-17503: ksfdopn:2 Failed to open file +ORADATA/TFREC/ONLINELOG/group_10.9357.912869655

ORA-15173: entry 'TFREC' does not exist in directory '/'

Thu May 26 18:55:35 2016

Errors in file /u01/app/oracle/diag/rdbms/tgrec/TGREC1/trace/TGREC1_ora_24941.trc:

ORA-00313: open failed for members of log group 10 of thread 2

ORA-00312: online log 10 thread 2: '+ORAFRA/TFREC/ONLINELOG/group_10.5961.912869655'

ORA-17503: ksfdopn:2 Failed to open file +ORAFRA/TFREC/ONLINELOG/group_10.5961.912869655

ORA-15173: entry 'TFREC' does not exist in directory '/'

ORA-00312: online log 10 thread 2: '+ORADATA/TFREC/ONLINELOG/group_10.9357.912869655'

ORA-17503: ksfdopn:2 Failed to open file +ORADATA/TFREC/ONLINELOG/group_10.9357.912869655

ORA-15173: entry 'TFREC' does not exist in directory '/'

Completed: ALTER DATABASE CLEAR LOGFILE GROUP 10

ALTER DATABASE CLEAR LOGFILE GROUP 11

ORA-359 signalled during: ALTER DATABASE CLEAR LOGFILE GROUP 11...

Thu May 26 18:56:04 2016

Archived Log entry 35 atested for thread 1 sequence 364 rlc 908381974 ID 0xad3a149d dest 2:

RFS[1]: Selected log 5 for thread 1 sequence 365 dbid 2863871221 branch 908381974

Thu May 26 18:56:05 2016

Archived Log entry 36 atested for thread 2 sequence 334 rlc 908381974 ID 0xad3a149d dest 2:

RFS[2]: Selected log 8 for thread 2 sequence 335 dbid 2863871221 branch 908381974

Basically oracle can’t identify the standby redo log file , as we are using real time apply – standby redo logs are required .

[oracle@stbyex]:[TGREC1] $ ss

SQL*Plus: Release 12.1.0.2.0 Production on Thu May 26 18:36:29 2016

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,

OLAP, Advanced Analytics, Oracle Database Vault and Real Application Testing options

SQL> select member from v$logfile

2 ;

MEMBER

--------------------------------------------------------------------------------

+ORADATA/TGREC/ONLINELOG/group_2.2560.912882251

+ORAFRA/TGREC/ONLINELOG/group_2.22976.912882251

+ORADATA/TGREC/ONLINELOG/group_1.2559.912882249

+ORAFRA/TGREC/ONLINELOG/group_1.22961.912882251

+ORADATA/TGREC/ONLINELOG/group_3.2561.912882251

+ORAFRA/TGREC/ONLINELOG/group_3.22987.912882251

+ORADATA/TGREC/ONLINELOG/group_4.2562.912882251

+ORAFRA/TGREC/ONLINELOG/group_4.22995.912882251

+ORADATA/TFREC/ONLINELOG/group_5.9397.912869649

+ORAFRA/TFREC/ONLINELOG/group_5.11747.912869649

+ORADATA/TFREC/ONLINELOG/group_6.9381.912869649

+ORAFRA/TFREC/ONLINELOG/group_6.18570.912869649

+ORADATA/TFREC/ONLINELOG/group_7.9374.912869649

+ORAFRA/TFREC/ONLINELOG/group_7.9606.912869649

+ORADATA/TFREC/ONLINELOG/group_8.9365.912869655

+ORAFRA/TFREC/ONLINELOG/group_8.23937.912869655

+ORADATA/TFREC/ONLINELOG/group_9.9362.912869655

+ORAFRA/TFREC/ONLINELOG/group_9.2150.912869655

+ORADATA/TFREC/ONLINELOG/group_10.9357.912869655

+ORAFRA/TFREC/ONLINELOG/group_10.5961.912869655

20 rows selected.

SQL>

SQL> select group# from v$log;

GROUP#

----------

SQL> alter database clear logfile group 1;

alter database clear logfile group 1

ERROR at line 1:

ORA-01156: recovery or flashback in progress may need access to files

SQL> recover managed standby database cancel;

Media recovery complete.

SQL> alter database clear logfile group 1;

Database altered.

SQL> alter database clear logfile group 2;

Database altered.

SQL> alter database clear logfile group 3;

Database altered.

SQL> alter database clear logfile group 4;

Database altered.

SQL> select member from v$logfile

2 ;

MEMBER

--------------------------------------------------------------------------------

+ORADATA/TGREC/ONLINELOG/group_2.2560.912882251

+ORAFRA/TGREC/ONLINELOG/group_2.22976.912882251

+ORADATA/TGREC/ONLINELOG/group_1.2559.912882249

+ORAFRA/TGREC/ONLINELOG/group_1.22961.912882251

+ORADATA/TGREC/ONLINELOG/group_3.2561.912882251

+ORAFRA/TGREC/ONLINELOG/group_3.22987.912882251

+ORADATA/TGREC/ONLINELOG/group_4.2562.912882251

+ORAFRA/TGREC/ONLINELOG/group_4.22995.912882251

+ORADATA/TFREC/ONLINELOG/group_5.9397.912869649

+ORAFRA/TFREC/ONLINELOG/group_5.11747.912869649

+ORADATA/TFREC/ONLINELOG/group_6.9381.912869649

+ORAFRA/TFREC/ONLINELOG/group_6.18570.912869649

+ORADATA/TFREC/ONLINELOG/group_7.9374.912869649

+ORAFRA/TFREC/ONLINELOG/group_7.9606.912869649

+ORADATA/TFREC/ONLINELOG/group_8.9365.912869655

+ORAFRA/TFREC/ONLINELOG/group_8.23937.912869655

+ORADATA/TFREC/ONLINELOG/group_9.9362.912869655

+ORAFRA/TFREC/ONLINELOG/group_9.2150.912869655

+ORADATA/TFREC/ONLINELOG/group_10.9357.912869655

+ORAFRA/TFREC/ONLINELOG/group_10.5961.912869655

20 rows selected.

SQL> select group# from v$standby_log;

GROUP#

----------

6 rows selected.

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 5;

Database altered.

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 6;

Database altered.

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 7;

Database altered.

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 8;

Database altered.

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 9;

Database altered.

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 10;

Database altered.

SQL> select member from v$logfile

2 ;

MEMBER

--------------------------------------------------------------------------------

+ORADATA/TGREC/ONLINELOG/group_2.2560.912882251

+ORAFRA/TGREC/ONLINELOG/group_2.22976.912882251

+ORADATA/TGREC/ONLINELOG/group_1.2559.912882249

+ORAFRA/TGREC/ONLINELOG/group_1.22961.912882251

+ORADATA/TGREC/ONLINELOG/group_3.2561.912882251

+ORAFRA/TGREC/ONLINELOG/group_3.22987.912882251

+ORADATA/TGREC/ONLINELOG/group_4.2562.912882251

+ORAFRA/TGREC/ONLINELOG/group_4.22995.912882251

+ORADATA/TGREC/ONLINELOG/group_5.2564.912884107

+ORAFRA/TGREC/ONLINELOG/group_5.23038.912884107

+ORADATA/TGREC/ONLINELOG/group_6.2565.912884113

+ORAFRA/TGREC/ONLINELOG/group_6.23069.912884113

+ORADATA/TGREC/ONLINELOG/group_7.2566.912884117

+ORAFRA/TGREC/ONLINELOG/group_7.23103.912884117

+ORADATA/TGREC/ONLINELOG/group_8.2567.912884123

+ORAFRA/TGREC/ONLINELOG/group_8.23177.912884123

+ORADATA/TGREC/ONLINELOG/group_9.2568.912884129

+ORAFRA/TGREC/ONLINELOG/group_9.23206.912884129

+ORADATA/TGREC/ONLINELOG/group_10.2569.912884135

+ORAFRA/TGREC/ONLINELOG/group_10.23232.912884135

20 rows selected.

[oracle@stbyex]:[TGREC1] $ ss

SQL*Plus: Release 12.1.0.2.0 Production on Thu May 26 18:56:27 2016

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,

OLAP, Advanced Analytics, Oracle Database Vault and Real Application Testing options

SQL> select * from test;

----------

SQL> recover managed standby database using current logfile disconnect ;

Media recovery complete.

SQL>

SQL> select * from test;

----------

110

SQL>

Nothing but my learning and experience with Oracle..!

Pages

Friday, May 27, 2016

Steps by step for Rolling Forward a Physical Standby Database using RMAN Incremental Backup

Connect to STANDBY database and restore the standby control file:

[oracle@stbyex]:[TGREC1] $ ll

total 69608

Popular Posts