Friday, May 27, 2016

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


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

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

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

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

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
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
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;

        ID
----------
        33
        77
        88

3 rows selected.

SQL> insert into test values (110);

1 row created.

SQL> commit;

Commit complete.

SQL> select  * from test;

        ID
----------
        33
        77
        88
       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

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

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

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

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

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


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
ARCH      CONNECTED             0          0          0
ARCH      CONNECTED             0          0          0
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
RFS       IDLE                  0          0          0
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;

        ID
----------
        33
        77
        88

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

PROCESS   STATUS        SEQUENCE#     BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ----------
ARCH      CONNECTED             0          0          0
ARCH      CONNECTED             0          0          0
ARCH      CONNECTED             0          0          0
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
RFS       IDLE                  0          0          0
RFS       IDLE                  0          0          0
MRP0      WAIT_FOR_LOG        362          0          0

11 rows selected.


SQL> select  * from test;


        ID
----------
        33
        77
        88


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

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


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#
----------
         1
         2
         3
         4

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#
----------
         5
         6
         7
         8
         9
        10

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

Copyright (c) 1982, 2014, Oracle.  All rights reserved.
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;

        ID
----------
        33
        77
        88


SQL> recover managed standby database using current logfile disconnect ;
Media recovery complete.
SQL>
SQL>  select  * from test;

        ID
----------
        33
        77
        88
       110

SQL>


Posted by Mahesh Karthya at No comments:
Labels:

Tuesday, May 24, 2016

How to do a switchover from data guard broker


Without the Broker, Data Guard Role Changes require a complex sequence of steps (versions before 12c) on both sides that differ between Logical and Physical Standby.When using DGMGRL, you need to issue only one SWITCHOVER command to specify the name of the standby database that you want to change into the primary role.

How the Broker Performs a Switchover
Once you start the switchover, the broker:
  1. Verifies that the primary and the target standby databases are in the following states:
    1. The primary database is enabled and is in the TRANSPORT-ON state.
    2. The target standby database is enabled and is in the APPLY-ON state.
The broker allows the switchover to proceed as long as there are no errors for the primary database and the standby database that you selected to participate in the switchover operation. Errors occurring for any other bystander standby databases will not impede the switchover.
  1. Shuts down all instances except one, if required.
If you are switching over to a physical standby database, the broker shuts down all but one instance on the current primary database. No instances will be shut down on the target physical standby database.
No instances will be shut down if switching over to a logical standby database. You cannot switch over to a snapshot standby database.
  1. Switches roles between the primary and standby databases.
The broker first converts the original primary database to run in the standby role. Then, the broker transitions the target standby database to the primary role.Updates the broker configuration file to record the change in roles.
This ensures that each database will run in the correct role and state should it be restarted later for any reason.
  1. Restarts the new standby (former primary) database if the switchover occurs to a physical standby database, and Redo Apply begins applying redo data from the new primary database. If this is an Oracle RAC physical standby database, the broker directs Oracle Clusterware to restart the instances that were shut down prior to the switchover. In a configuration operating in maximum protection mode, the new primary database will also be restarted.
  2. The new primary database is opened in read/write mode and redo transport services are started.
If the former physical standby database was running with real-time query enabled, the new physical standby database will run with real-time query enabled.

C:\Users\mahesh>dgmgrl
DGMGRL for 64-bit Windows: Version 11.2.0.1.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/Orcl1234@dstudy -->   You can connect to any of the participant database.
Connected.
DGMGRL> show configuration ;
Configuration - DgStudyConfig
Protection Mode: MaxPerformance
  Databases:
    dstudy - Primary database
    study  - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL>

DGMGRL> show database verbose 'dstudy';
Database – dstudy
  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    dstudy
    Properties:
    DGConnectIdentifier             = 'dstudy'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'optional'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '4'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = ''
    LogFileNameConvert              = ''
    FastStartFailoverTarget         = 'study'
    StatusReport                    = '(monitor)'
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = 'BDC7-L-2JRRWQ1'
    SidName                         = 'dstudy'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=BDC7-L-2JRRWQ1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DSTUDY_DGMGRL)(INSTANCE_NAME=dstudy)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = 'ARC%S_%R.%T'
    TopWaitEvents                   = '(monitor)'
Database Status:
SUCCESS
DGMGRL>
 
DGMGRL> show database verbose 'study';
Database - study
  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: ON
  Instance(s):
    study
 Properties:
    DGConnectIdentifier             = 'study'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'OPTIONAL'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'ENABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'MANUAL'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '4'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = ''
    LogFileNameConvert              = ''
    FastStartFailoverTarget         = 'dstudy'
    StatusReport                    = '(monitor)'
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = 'BDC7-L-2JRRWQ1'
    SidName                         = 'study'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=BDC7-L-2JRRWQ1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=study_DGMGRL)(INSTANCE_NAME=study)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'C:\oradata\archive'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = 'log%t_%s_%r_%d.arc'
    TopWaitEvents                   = '(monitor)'
Database Status:
SUCCESS
DGMGRL>
 
Everything seems to be good now , so we will do a switchover 

DGMGRL> switchover to study;
Performing switchover NOW, please wait...
New primary database "study" is opening...
Operation requires shutdown of instance "dstudy" on database "dstudy"
Shutting down instance "dstudy"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "dstudy" on database "dstudy"
Starting instance "dstudy"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "study"
DGMGRL>
 
Switchover is completed , verify the current configuration 
DGMGRL> show configuration ;
Configuration - DgStudyConfig
Protection Mode: MaxPerformance
  Databases:
    study  - Primary database
    dstudy - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL>

Try that with SQL commands and you spend a significant amount of time reading the documentation in order to get these steps right. Furthermore, I don’t need to bother about LOG_ARCHIVE_DEST_2 because the Broker sets it correctly – without manual intervention and without VALID_FOR. The other Role Changes are also one-liners with the Broker:





Posted by Mahesh Karthya at No comments:
Labels:
Subscribe to: Posts (Atom)