Tuesday, December 3, 2019

Resolving Gaps in Data Guard Apply Using Incremental RMAN BACKUP

Arup Nanda had written a blogpost on this in 2009 and I highly recommend all of you read this.

http://arup.blogspot.com/2009/12/resolving-gaps-in-data-guard-apply.html

The basic technique of using  SCN of dataguard and taking backup from primary is well documented in his blog and I am not going to repeat that.

Unfortunately the blog was written using normal filesystems and did not take into account about ASM with oracle managed files .


Example is production might have all the files under

+DATA/<prod db name>/DATAFILE

whereas dataguard will have files under

+DATA/<DR db name>/DATAFILE

Since the path do NOT match , you will have to tweak his technique a bit.

In this example , I will put in my perspective of how to fix this part.

PLEASE NOTE TO REFER TO ARUP NANDA'S ORIGINAL BLOG FOR THE RECOVERY PORTION USING RMAN.



PRIMARY
++++++

oracle@prodhost orclpr1 trace: sqlplus '/ as sysdba'

SQL*Plus: Release 12.1.0.2.0 Production on Tue Mar 7 10:21:58 2017

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 create standby controlfile as '/tmp/control01.ctl';

Database altered.

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@prodhost orclpr1 trace: scp /tmp/control01.ctl gchandra@drhost:/tmp/.
This computer system is for authorized users only. Unauthorized users are
prohibited. Users (authorized or unauthorized) have no explicit or implicit
expectation of privacy. Discontinue access immediately if you do not agree to
the conditions stated in this notice
Password:
control01.ctl                                                                                                                                                  100%   23MB  23.3MB/s   00:01


Data Guard
++++++++++

[root@drhost AKA ws-dr-bmodb01 tmp]# chown oracle:oinstall control01.ctl

oracle@drhost orcldr1 trace: srvctl stop database -d orcldr
oracle@drhost orcldr1 trace: sqlplus '/ as sysdba'

SQL*Plus: Release 12.1.0.2.0 Production on Tue Mar 7 10:26:28 2017

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

starConnected to an idle instance.

SQL> tup nomount
ORACLE instance started.

Total System Global Area 4731174912 bytes
Fixed Size                  4506744 bytes
Variable Size            1291848584 bytes
Database Buffers         3422552064 bytes
Redo Buffers               12267520 bytes
SQL> exit


oracle@drhost orcldr1 trace: rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Tue Mar 7 10:26:49 2017

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

connected to target database: FI9219PR (not mounted)

RMAN> restore controlfile from '/tmp/control01.ctl';

Starting restore at 07-MAR-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=774 instance=orcldr1 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=+DATA/orcldr/control01.ctl
Finished restore at 07-MAR-17






RMAN> sql 'alter database mount';

sql statement: alter database mount
released channel: ORA_DISK_1

RMAN> catalog start with '+data/orcldr/datafile';

Starting implicit crosscheck backup at 07-MAR-17
allocated channel: ORA_DISK_1
Finished implicit crosscheck backup at 07-MAR-17

Starting implicit crosscheck copy at 07-MAR-17
using channel ORA_DISK_1
Finished implicit crosscheck copy at 07-MAR-17

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: +RECO/orcldr/ARCHIVELOG/2017_03_07/thread_1_seq_793.2753.937994585
File Name: +RECO/orcldr/ARCHIVELOG/2017_03_07/thread_2_seq_714.5482.937994587
File Name: +RECO/orcldr/ARCHIVELOG/2017_03_07/thread_2_seq_715.672.937994749
File Name: +RECO/orcldr/ARCHIVELOG/2017_03_07/thread_1_seq_794.2341.937994749Production

searching for all files that match the pattern +data/orcldr/datafile

List of Files Unknown to the Database
=====================================
File Name: +DATA/orcldr/DATAFILE/system.468.929733757
File Name: +DATA/orcldr/DATAFILE/sysaux.469.929733759
File Name: +DATA/orcldr/DATAFILE/undotbs2.470.929733765
File Name: +DATA/orcldr/DATAFILE/admin.471.929733767
File Name: +DATA/orcldr/DATAFILE/archive.472.929733769
File Name: +DATA/orcldr/DATAFILE/hubload.473.929733771
File Name: +DATA/orcldr/DATAFILE/hubsys.474.929733773
File Name: +DATA/orcldr/DATAFILE/intplat.475.929733779
File Name: +DATA/orcldr/DATAFILE/keystore.476.929733781
File Name: +DATA/orcldr/DATAFILE/undotbs1.477.929733787
File Name: +DATA/orcldr/DATAFILE/users.478.929733787
File Name: +DATA/orcldr/DATAFILE/webinit.479.929733789
File Name: +DATA/orcldr/DATAFILE/webrpt.480.929733789
File Name: +DATA/orcldr/DATAFILE/websys.481.929733797
File Name: +DATA/orcldr/DATAFILE/wpdata.482.929733797
File Name: +DATA/orcldr/DATAFILE/system.536.937132397
File Name: +DATA/orcldr/DATAFILE/sysaux.537.937132397
File Name: +DATA/orcldr/DATAFILE/undotbs2.538.937132413
File Name: +DATA/orcldr/DATAFILE/admin.539.937132417
File Name: +DATA/orcldr/DATAFILE/archive.540.937132425
File Name: +DATA/orcldr/DATAFILE/hubload.541.937132431
File Name: +DATA/orcldr/DATAFILE/hubsys.542.937132433
File Name: +DATA/orcldr/DATAFILE/intplat.543.937132441
File Name: +DATA/orcldr/DATAFILE/keystore.544.937132441
File Name: +DATA/orcldr/DATAFILE/undotbs1.545.937132449
File Name: +DATA/orcldr/DATAFILE/users.546.937132449
File Name: +DATA/orcldr/DATAFILE/webinit.547.937132453
File Name: +DATA/orcldr/DATAFILE/webrpt.548.937132453
File Name: +DATA/orcldr/DATAFILE/websys.549.937132461
File Name: +DATA/orcldr/DATAFILE/wpdata.550.937132461

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

List of Cataloged Files
=======================
File Name: +DATA/orcldr/DATAFILE/system.468.929733757
File Name: +DATA/orcldr/DATAFILE/sysaux.469.929733759
File Name: +DATA/orcldr/DATAFILE/undotbs2.470.929733765
File Name: +DATA/orcldr/DATAFILE/admin.471.929733767
File Name: +DATA/orcldr/DATAFILE/archive.472.929733769
File Name: +DATA/orcldr/DATAFILE/hubload.473.929733771
File Name: +DATA/orcldr/DATAFILE/hubsys.474.929733773
File Name: +DATA/orcldr/DATAFILE/intplat.475.929733779
File Name: +DATA/orcldr/DATAFILE/keystore.476.929733781
File Name: +DATA/orcldr/DATAFILE/undotbs1.477.929733787
File Name: +DATA/orcldr/DATAFILE/users.478.929733787
File Name: +DATA/orcldr/DATAFILE/webinit.479.929733789
File Name: +DATA/orcldr/DATAFILE/webrpt.480.929733789
File Name: +DATA/orcldr/DATAFILE/websys.481.929733797
File Name: +DATA/orcldr/DATAFILE/wpdata.482.929733797
File Name: +DATA/orcldr/DATAFILE/system.536.937132397
File Name: +DATA/orcldr/DATAFILE/sysaux.537.937132397
File Name: +DATA/orcldr/DATAFILE/undotbs2.538.937132413
File Name: +DATA/orcldr/DATAFILE/admin.539.937132417
File Name: +DATA/orcldr/DATAFILE/archive.540.937132425
File Name: +DATA/orcldr/DATAFILE/hubload.541.937132431
File Name: +DATA/orcldr/DATAFILE/hubsys.542.937132433
File Name: +DATA/orcldr/DATAFILE/intplat.543.937132441
File Name: +DATA/orcldr/DATAFILE/keystore.544.937132441
File Name: +DATA/orcldr/DATAFILE/undotbs1.545.937132449
File Name: +DATA/orcldr/DATAFILE/users.546.937132449
File Name: +DATA/orcldr/DATAFILE/webinit.547.937132453
File Name: +DATA/orcldr/DATAFILE/webrpt.548.937132453
File Name: +DATA/orcldr/DATAFILE/websys.549.937132461
File Name: +DATA/orcldr/DATAFILE/wpdata.550.937132461

RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DATA/orcldr/DATAFILE/system.536.937132397"
datafile 2 switched to datafile copy "+DATA/orcldr/DATAFILE/sysaux.537.937132397"
datafile 3 switched to datafile copy "+DATA/orcldr/DATAFILE/undotbs2.538.937132413"
datafile 4 switched to datafile copy "+DATA/orcldr/DATAFILE/admin.539.937132417"
datafile 5 switched to datafile copy "+DATA/orcldr/DATAFILE/archive.540.937132425"
datafile 6 switched to datafile copy "+DATA/orcldr/DATAFILE/hubload.541.937132431"
datafile 7 switched to datafile copy "+DATA/orcldr/DATAFILE/hubsys.542.937132433"
datafile 8 switched to datafile copy "+DATA/orcldr/DATAFILE/intplat.543.937132441"
datafile 9 switched to datafile copy "+DATA/orcldr/DATAFILE/keystore.544.937132441"
datafile 10 switched to datafile copy "+DATA/orcldr/DATAFILE/undotbs1.545.937132449"
datafile 11 switched to datafile copy "+DATA/orcldr/DATAFILE/users.546.937132449"
datafile 12 switched to datafile copy "+DATA/orcldr/DATAFILE/webinit.547.937132453"
datafile 13 switched to datafile copy "+DATA/orcldr/DATAFILE/webrpt.548.937132453"
datafile 14 switched to datafile copy "+DATA/orcldr/DATAFILE/websys.549.937132461"
datafile 15 switched to datafile copy "+DATA/orcldr/DATAFILE/wpdata.550.937132461"

RMAN> exit


Recovery Manager complete.


oracle@drhost orcldr1 trace: sqlplus '/ as sysdba'

SQL*Plus: Release 12.1.0.2.0 Production on Tue Mar 7 10:30:15 2017

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> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

NOW WE HAVE SUCESSFULLY GOT THE STANDBY CONTROLFILE FROM PRODUCTION INTO DATAGUARD AND SUCESSFULLY CHANGED THE PATH OF ALL THE FILE NAMES.



Once the above is done, you can start from step 10 in his blog .


Note to confused myself
++++++++++++++++++

You might be wondering what hapenned to the redo logs under +DATA/orcldr/onlinelog directory as we never renamed them but oracle will automatically clear them up for all the groups once you open the database.

example of alert log  -
ORA-17503: ksfdopn:2 Failed to open file +DATA/FI9219DR/onlinelog/group_3.414.929526809
ORA-15173: entry 'group_3.414.929526809' does not exist in directory 'onlinelog'
Deleted Oracle managed file +DATA/orcldr/onlinelog/group_3.414.929526809
Deleted Oracle managed file +RECO/orcldr/onlinelog/group_3.562.929526811
Clearing online redo logfile 3 complete



Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)