Restoring RMAN Incremental Backup for Standby with Added datafile on Primary

-

Pre-requisites: Already had RMAN Incremental backup from required SCN from Primary Database and transferred backup pieces from Primary Server to Standby Server.


I've had situation where there was huge gap between Production & Standby and I didn't have any other option but to take RMAN Incremental backup from Primary and apply it to Standby to minimize the Gap.

Here I had challenge is that On Primary database, one datafile was added due to space crunch and as I knew RMAN Incremental backup doesn't support it, also some of the datafiles are in OMF & Non-OMF Format.

Below are the steps given from Restoration of RMAN Incremental Backup and issues/solutions faced/used. I hope this would be useful in some cases,

[inmumdcdbadm01.tajhotels.com -> oracle:/dbfsmnt/oradbfs/backup/Incr_EBSPRD] :rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Sat Sep 6 02:48:32 2014

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

connected to target database (not started)

RMAN> startup nomount

Oracle instance started

Total System Global Area   14965264384 bytes

Fixed Size                     2242944 bytes
Variable Size               3523217024 bytes
Database Buffers           11408506880 bytes
Redo Buffers                  31297536 bytes

RMAN> restore standby controlfile from '/dbfsmnt/oradbfs/backup/Incr_EBSPRD/ctrl_EBSPRD.f';

Starting restore at 06-SEP-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2506 instance=EBSPRD1 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output file name=+DATA_DC/ebsprd/controlfile/current.1007.841346665
output file name=+RECO_DC/ebsprd/controlfile/current.3235.841346665
output file name=+DATA_DC/ebsprd/controlfile/current.1008.841346667
Finished restore at 06-SEP-14

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> crosscheck backup;

using channel ORA_DISK_1
crosschecked backup piece: found to be 'EXPIRED'
...

...

...

crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/dbfsmnt/oradbfs/stdby/ForStandby_8dpbp6ls_1_1 RECID=2159 STAMP=851227782
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/dbfsmnt/oradbfs/backup/Incr_EBSPRD/ForStandby_t0pho2gl_1_1 RECID=2160 STAMP=857475609
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/dbfsmnt/oradbfs/backup/Incr_EBSPRD/ForStandby_svpho2gl_1_1 RECID=2161 STAMP=857475607
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/dbfsmnt/oradbfs/backup/Incr_EBSPRD/ForStandby_t1pho2gn_1_1 RECID=2162 STAMP=857475609
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/dbfsmnt/oradbfs/backup/Incr_EBSPRD/ForStandby_t6pho2it_1_1 RECID=2163 STAMP=857475704
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/dbfsmnt/oradbfs/backup/Incr_EBSPRD/ForStandby_t4pho2hp_1_1 RECID=2164 STAMP=857475676
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/dbfsmnt/oradbfs/backup/Incr_EBSPRD/ForStandby_t2pho2gn_1_1 RECID=2165 STAMP=857475628
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/dbfsmnt/oradbfs/backup/Incr_EBSPRD/ForStandby_t3pho2h2_1_1 RECID=2166 STAMP=857475657
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/dbfsmnt/oradbfs/backup/Incr_EBSPRD/ForStandby_t5pho2if_1_1 RECID=2167 STAMP=857475700
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/dbfsmnt/oradbfs/backup/Incr_EBSPRD/ForStandby_t7phocea_1_1 RECID=2168 STAMP=857485771
Crosschecked 18 objects


RMAN> delete expired  backup;

Deleted 9 EXPIRED objects


RMAN> crosscheck backup;

using channel ORA_DISK_1
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/dbfsmnt/oradbfs/backup/Incr_EBSPRD/ForStandby_t0pho2gl_1_1 RECID=2160 STAMP=857475609
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/dbfsmnt/oradbfs/backup/Incr_EBSPRD/ForStandby_svpho2gl_1_1 RECID=2161 STAMP=857475607
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/dbfsmnt/oradbfs/backup/Incr_EBSPRD/ForStandby_t1pho2gn_1_1 RECID=2162 STAMP=857475609
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/dbfsmnt/oradbfs/backup/Incr_EBSPRD/ForStandby_t6pho2it_1_1 RECID=2163 STAMP=857475704
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/dbfsmnt/oradbfs/backup/Incr_EBSPRD/ForStandby_t4pho2hp_1_1 RECID=2164 STAMP=857475676
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/dbfsmnt/oradbfs/backup/Incr_EBSPRD/ForStandby_t2pho2gn_1_1 RECID=2165 STAMP=857475628
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/dbfsmnt/oradbfs/backup/Incr_EBSPRD/ForStandby_t3pho2h2_1_1 RECID=2166 STAMP=857475657
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/dbfsmnt/oradbfs/backup/Incr_EBSPRD/ForStandby_t5pho2if_1_1 RECID=2167 STAMP=857475700
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/dbfsmnt/oradbfs/backup/Incr_EBSPRD/ForStandby_t7phocea_1_1 RECID=2168 STAMP=857485771
Crosschecked 9 objects


RMAN> catalog start with '+DATA_DC/EBSPRD/DATAFILE';

searching for all files that match the pattern +DATA_DC/EBSPRD/DATAFILE

List of Files Unknown to the Database
=====================================
File Name: +data_dc/EBSPRD/DATAFILE/APPS_TS_QUEUES.1080.841608639
File Name: +data_dc/EBSPRD/DATAFILE/TAJD.1081.841608679
File Name: +data_dc/EBSPRD/DATAFILE/APPS_TS_TX_IDX.1082.841608711
File Name: +data_dc/EBSPRD/DATAFILE/APPS_TS_TX_DATA.1083.841608743
File Name: +data_dc/EBSPRD/DATAFILE/SYSTEM.1084.841608999
File Name: +data_dc/EBSPRD/DATAFILE/APPS_UNDOTS2.1085.841717505
File Name: +data_dc/EBSPRD/DATAFILE/APPS_UNDOTS2.1117.847381215
File Name: +data_dc/EBSPRD/DATAFILE/APPS_TS_TX_DATA.1116.855958105

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_dc/EBSPRD/DATAFILE/APPS_TS_QUEUES.1080.841608639
File Name: +data_dc/EBSPRD/DATAFILE/TAJD.1081.841608679
File Name: +data_dc/EBSPRD/DATAFILE/APPS_TS_TX_IDX.1082.841608711
File Name: +data_dc/EBSPRD/DATAFILE/APPS_TS_TX_DATA.1083.841608743
File Name: +data_dc/EBSPRD/DATAFILE/SYSTEM.1084.841608999
File Name: +data_dc/EBSPRD/DATAFILE/APPS_UNDOTS2.1085.841717505
File Name: +data_dc/EBSPRD/DATAFILE/APPS_UNDOTS2.1117.847381215
File Name: +data_dc/EBSPRD/DATAFILE/APPS_TS_TX_DATA.1116.855958105

RMAN> switch datafile 52 to copy;

datafile 52 switched to datafile copy "+DATA_DC/ebsprd/datafile/apps_ts_queues.1080.841608639"

RMAN> switch datafile 53 to copy;

datafile 53 switched to datafile copy "+DATA_DC/ebsprd/datafile/tajd.1081.841608679"

RMAN> switch datafile 54 to copy;

datafile 54 switched to datafile copy "+DATA_DC/ebsprd/datafile/apps_ts_tx_idx.1082.841608711"

RMAN> switch datafile 55 to copy;

datafile 55 switched to datafile copy "+DATA_DC/ebsprd/datafile/apps_ts_tx_data.1083.841608743"

RMAN> switch datafile 56 to copy;

datafile 56 switched to datafile copy "+DATA_DC/ebsprd/datafile/system.1084.841608999"

RMAN> switch datafile 57 to copy;

datafile 57 switched to datafile copy "+DATA_DC/ebsprd/datafile/apps_undots2.1085.841717505"

RMAN> switch datafile 58 to copy;

datafile 58 switched to datafile copy "+DATA_DC/ebsprd/datafile/apps_undots2.1117.847381215"

RMAN> switch datafile 59 to copy;

datafile 59 switched to datafile copy "+DATA_DC/ebsprd/datafile/apps_ts_tx_data.1116.855958105"

RMAN> switch datafile 60 to copy;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of switch to copy command at 09/06/2014 02:55:10
RMAN-06571: datafile 60 does not have recoverable copy

RMAN>

RMAN> recover database noredo;

Starting recover at 06-SEP-14
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 09/06/2014 02:58:01
RMAN-06094: datafile 60 must be restored

RMAN> exit

################ From Primary Database Output###############

SQL> select count(1) from v$datafile;

  COUNT(1)
----------
        60

SQL> select file#,name,creation_time,bytes/1024/1024/1024 from  v$Datafile where file#=60;

     FILE# NAME                                                         CREATION_ BYTES/1024/1024/1024
---------- ------------------------------------------------------------ --------- --------------------
        60 +DATA_DR/ebsprddr/datafile/apps_ts_tx_data.1647.857404571    04-SEP-14                   20



Confirmed that Datafile# 60 existin on Production Database.

################ From Standby Database Output###############


SQL> select count(1) from v$datafile;

  COUNT(1)
----------
        60

SQL> select file#,name,creation_time,bytes/1024/1024/1024 from  v$Datafile where file#=60;

     FILE# NAME                                                         CREATION_ BYTES/1024/1024/1024
---------- ------------------------------------------------------------ --------- --------------------
        60 +DATA_DC/ebsprddr/datafile/apps_ts_tx_data.1647.857404571    04-SEP-14                   20

Showing false information as, newly created standby controlfile has just restored, but physically file doesn't exist in the ASM Diskgroup and path is too showing 

wrong.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA_DC/ebsprd/system01.dbf
+DATA_DC/ebsprd/system02.dbf
+DATA_DC/ebsprd/system03.dbf
+DATA_DC/ebsprd/system04.dbf
+DATA_DC/ebsprd/system05.dbf
+DATA_DC/ebsprd/system06.dbf
+DATA_DC/ebsprd/system07.dbf
+DATA_DC/ebsprd/system08.dbf
+DATA_DC/ebsprd/system09.dbf
+DATA_DC/ebsprd/system10.dbf
+DATA_DC/ebsprd/system11.dbf
+DATA_DC/ebsprd/sysaux01.dbf
+DATA_DC/ebsprd/undo01.dbf
+DATA_DC/ebsprd/undo02.dbf
+DATA_DC/ebsprd/a_archive01.dbf
+DATA_DC/ebsprd/a_int01.dbf
+DATA_DC/ebsprd/a_media01.dbf
+DATA_DC/ebsprd/a_nolog01.dbf
+DATA_DC/ebsprd/a_queue01.dbf
+DATA_DC/ebsprd/a_queue02.dbf
+DATA_DC/ebsprd/a_queue03.dbf
+DATA_DC/ebsprd/a_ref01.dbf
+DATA_DC/ebsprd/a_ref02.dbf
+DATA_DC/ebsprd/a_summ01.dbf
+DATA_DC/ebsprd/a_summ02.dbf
+DATA_DC/ebsprd/apps_ts_tools01.dbf
+DATA_DC/ebsprd/a_txn_data01.dbf
+DATA_DC/ebsprd/a_txn_data02.dbf
+DATA_DC/ebsprd/a_txn_data03.dbf
+DATA_DC/ebsprd/a_txn_data05.dbf
+DATA_DC/ebsprd/a_txn_data06.dbf
+DATA_DC/ebsprd/a_txn_data07.dbf
+DATA_DC/ebsprd/a_txn_data08.dbf
+DATA_DC/ebsprd/a_txn_data09.dbf
+DATA_DC/ebsprd/a_txn_data10.dbf
+DATA_DC/ebsprd/a_txn_data4.dbf
+DATA_DC/ebsprd/a_txn_ind01.dbf
+DATA_DC/ebsprd/a_txn_ind02.dbf
+DATA_DC/ebsprd/a_txn_ind03.dbf
+DATA_DC/ebsprd/a_txn_ind04.dbf
+DATA_DC/ebsprd/a_txn_ind05.dbf
+DATA_DC/ebsprd/a_txn_ind06.dbf
+DATA_DC/ebsprd/a_txn_ind07.dbf
+DATA_DC/ebsprd/ctxd01.dbf
+DATA_DC/ebsprd/discoverer01.dbf
+DATA_DC/ebsprd/odm.dbf
+DATA_DC/ebsprd/olap.dbf
+DATA_DC/ebsprd/owad01.dbf
+DATA_DC/ebsprd/portal01.dbf
+DATA_DC/ebsprd/tajd01.dbf
+DATA_DC/ebsprd/tajx01.dbf
+DATA_DC/ebsprd/datafile/apps_ts_queues.1080.841608639      ### Datafile 52, OMF which I've just cataloged.
+DATA_DC/ebsprd/datafile/tajd.1081.841608679                ### Datafile 53, OMF which I've just cataloged.
+DATA_DC/ebsprd/datafile/apps_ts_tx_idx.1082.841608711      ### Datafile 54, OMF which I've just cataloged.
+DATA_DC/ebsprd/datafile/apps_ts_tx_data.1083.841608743     ### Datafile 55, OMF which I've just cataloged.
+DATA_DC/ebsprd/datafile/system.1084.841608999              ### Datafile 56, OMF which I've just cataloged.
+DATA_DC/ebsprd/datafile/apps_undots2.1085.841717505        ### Datafile 57, OMF which I've just cataloged.
+DATA_DC/ebsprd/datafile/apps_undots2.1117.847381215        ### Datafile 58, OMF which I've just cataloged.
+DATA_DC/ebsprd/datafile/apps_ts_tx_data.1116.855958105     ### Datafile 59, OMF which I've just cataloged.
+DATA_DC/ebsprddr/datafile/apps_ts_tx_data.1647.857404571   ### Datafile 60, Doesn't exist, also showing wrong path.

60 rows selected.



SQL> ALTER DATABASE CREATE DATAFILE '+DATA_DC/ebsprddr/datafile/apps_ts_tx_data.1647.857404571' AS '+DATA_DC';
ALTER DATABASE CREATE DATAFILE '+DATA_DC/ebsprddr/datafile/apps_ts_tx_data.1647.857404571' AS '+DATA_DC'
*
ERROR at line 1:
ORA-01275: Operation CREATE DATAFILE is not allowed if standby file management
is automatic.


SQL> SHOW PARAMETER STANDBY

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest                 string      ?/dbs/arch
standby_file_management              string      AUTO
SQL> ALTER SYSTEM SET standby_file_management='MANUAL' SID='*' SCOPE=BOTH;

System altered.

SQL> SHOW PARAMETER STANDBY

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest                 string      ?/dbs/arch
standby_file_management              string      MANUAL
SQL> ALTER DATABASE CREATE DATAFILE '+DATA_DC/ebsprddr/datafile/apps_ts_tx_data.1647.857404571' AS '+DATA_DC';
ALTER DATABASE CREATE DATAFILE '+DATA_DC/ebsprddr/datafile/apps_ts_tx_data.1647.857404571' AS '+DATA_DC'
*
ERROR at line 1:
ORA-01136: specified size of file 60 (12800 blocks) is less than original size
of 1310720 blocks
ORA-01110: data file 60:
'+DATA_DC/ebsprddr/datafile/apps_ts_tx_data.1647.857404571'


SQL> ALTER DATABASE CREATE DATAFILE '+DATA_DC/ebsprddr/datafile/apps_ts_tx_data.1647.857404571' AS '+DATA_DC' SIZE 20G;

Database altered.

SQL> ALTER SYSTEM SET standby_file_management='AUTO' SID='*' SCOPE=BOTH;

System altered.

SQL> SHOW PARAMETER STANDBY

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest                 string      ?/dbs/arch
standby_file_management              string      AUTO

SQL> SELECT NAME FROM V$DATAFILE where FILE#=60;

NAME
--------------------------------------------------------------------------------
+DATA_DC/ebsprd/datafile/apps_ts_tx_data.681.857531541      #### (Here blank datafile actually got created in proper location with different OMF Name)


[inmumdcdbadm01.tajhotels.com -> oracle:/dbfsmnt/oradbfs] :rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Sat Sep 6 03:13:14 2014

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

connected to target database: EBSPRD (DBID=642087017, not open)

RMAN> run {
2> allocate channel c1 type disk;
3> allocate channel c2 type disk;
4> allocate channel c3 type disk;
5> allocate channel c4 type disk;
6> allocate channel c5 type disk;
7> allocate channel c6 type disk;
8> allocate channel c7 type disk;
9> allocate channel c8 type disk;
10> recover database noredo;
11> release channel c1;
12> release channel c2;
13> release channel c3;
14> release channel c4;
15> release channel c5;
16> release channel c6;
17> release channel c7;
18> release channel c8;
}19>

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=6888 instance=EBSPRD1 device type=DISK

allocated channel: c2
channel c2: SID=7514 instance=EBSPRD1 device type=DISK

allocated channel: c3
channel c3: SID=7827 instance=EBSPRD1 device type=DISK

allocated channel: c4
channel c4: SID=8140 instance=EBSPRD1 device type=DISK

allocated channel: c5
channel c5: SID=9079 instance=EBSPRD1 device type=DISK

allocated channel: c6
channel c6: SID=9392 instance=EBSPRD1 device type=DISK

allocated channel: c7
channel c7: SID=942 instance=EBSPRD1 device type=DISK

allocated channel: c8
channel c8: SID=2192 instance=EBSPRD1 device type=DISK

Starting recover at 06-SEP-14
channel c1: starting incremental datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
...
...
...
...
...
channel c8: reading from backup piece /dbfsmnt/oradbfs/backup/Incr_EBSPRD/ForStandby_t5pho2if_1_1
channel c1: piece handle=/dbfsmnt/oradbfs/backup/Incr_EBSPRD/ForStandby_t0pho2gl_1_1 tag=FOR_STANDBY
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:04:27
channel c2: piece handle=/dbfsmnt/oradbfs/backup/Incr_EBSPRD/ForStandby_svpho2gl_1_1 tag=FOR_STANDBY
channel c2: restored backup piece 1
channel c2: restore complete, elapsed time: 00:05:07
channel c3: piece handle=/dbfsmnt/oradbfs/backup/Incr_EBSPRD/ForStandby_t1pho2gn_1_1 tag=FOR_STANDBY
channel c3: restored backup piece 1
channel c3: restore complete, elapsed time: 00:05:37
channel c4: piece handle=/dbfsmnt/oradbfs/backup/Incr_EBSPRD/ForStandby_t6pho2it_1_1 tag=FOR_STANDBY
channel c4: restored backup piece 1
channel c4: restore complete, elapsed time: 00:06:07
channel c5: piece handle=/dbfsmnt/oradbfs/backup/Incr_EBSPRD/ForStandby_t4pho2hp_1_1 tag=FOR_STANDBY
channel c5: restored backup piece 1
channel c5: restore complete, elapsed time: 00:15:17
channel c6: piece handle=/dbfsmnt/oradbfs/backup/Incr_EBSPRD/ForStandby_t2pho2gn_1_1 tag=FOR_STANDBY
channel c6: restored backup piece 1
channel c6: restore complete, elapsed time: 00:16:27
channel c7: piece handle=/dbfsmnt/oradbfs/backup/Incr_EBSPRD/ForStandby_t3pho2h2_1_1 tag=FOR_STANDBY
channel c7: restored backup piece 1
channel c7: restore complete, elapsed time: 00:22:37
channel c8: piece handle=/dbfsmnt/oradbfs/backup/Incr_EBSPRD/ForStandby_t5pho2if_1_1 tag=FOR_STANDBY
channel c8: restored backup piece 1
channel c8: restore complete, elapsed time: 00:31:54

Finished recover at 06-SEP-14

released channel: c1

released channel: c2

released channel: c3

released channel: c4

released channel: c5

released channel: c6

released channel: c7

released channel: c8

RMAN> exit

Recovery Manager complete.


[inmumdcdbadm01.tajhotels.com -> oracle:/dbfsmnt/oradbfs] :sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.3.0 Production on Sat Sep 6 03:53:21 2014

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> alter database recover managed standby database using current logfile disconnect parallel 8;

Database altered.


SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

   THREAD# MAX(SEQUENCE#)
---------- --------------
         1          23306
         2          22623

SQL> select PROCESS,STATUS,THREAD#,SEQUENCE#,BLOCK# from v$managed_standby order by 3;

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#
--------- ------------ ---------- ---------- ----------
ARCH      CONNECTED             0          0          0
RFS       IDLE                  0          0          0
RFS       IDLE                  0          0          0
RFS       IDLE                  0          0          0
RFS       IDLE                  0          0          0
RFS       IDLE                  0          0          0
ARCH      CONNECTED             0          0          0
ARCH      CONNECTED             0          0          0
ARCH      CONNECTED             0          0          0
ARCH      CONNECTED             0          0          0
ARCH      CONNECTED             0          0          0
RFS       IDLE                  0          0          0
RFS       IDLE                  0          0          0
RFS       IDLE                  0          0          0
RFS       IDLE                  0          0          0
RFS       IDLE                  0          0          0
RFS       IDLE                  0          0          0
RFS       IDLE                  0          0          0
RFS       IDLE                  0          0          0
RFS       IDLE                  0          0          0
RFS       IDLE                  1      23392     467617
MRP0      APPLYING_LOG          1      23278     522838
ARCH      CLOSING               1      23391     114688
RFS       IDLE                  2      22696     150921
ARCH      CLOSING               2      22695     923648

25 rows selected.

SQL>

########################## On Primary ##########################

SQL> select thread#,max(sequence#) from v$archived_log where archived='YES' group by thread#;

   THREAD# MAX(SEQUENCE#)
---------- --------------
         1          23391
         2          22694

########################## On Standby ##########################

SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

   THREAD# MAX(SEQUENCE#)
---------- --------------
         1          23390
         2          22694


Now, you could see that Standby is in almost Sync with Primary with the help of RMAN Incremental Backup.

Any Suggestions / Comments are welcome

Comments

Post a Comment

Popular posts from this blog

Step by Step: How to troubleshoot a slow running query in Oracle

-
This is a day to day task of a DBA. Someone from application team comes to our desk and simply says a query is running slow. We may question him on many things but ultimately a DBA has to tune the query at the end. This is also very popular and known question for the DBA's who are attending interviews. This question is simply asked again and again. I have also been asked this question multiple times. After a pause, interviewer simply asks...so suppose a user comes and reports that his query is running slow, what will be your approach to tune this. There is no absolute or concrete answer to this question because there might be multiple way to tune a slow running query. Everyone can have a different approach. So here is my approach: Very first thing we have to find which type of query is running slow. Whether it is SELECT query of DML statements(insert, update, delete and merge). So first we will see how to tune a SELECT query Step 1 – Find the SQL_ID of the
Read more

Register Archive log file manually in standby Database

-
  Some time, the archive logs are not shipping to standby location due to network issue.  There may be  several  archive logs are waiting in standby location for one archive log file. For instance, Archive log sequence # 100 is not shipped to standby location. But other archive logs(sequence#101 to sequence#200) were shipped to standby and it is waiting to ship the sequence# 100.  In this scenario, DBA can copy the archive log file and register manually. Once manually registered, the sequence#101 to sequence#200 will apply automatically on standby database. Step 1 Let us assume, the archive log file devdb12_1_283_801988635.arc is not shipped to standby due to some reason. Copy the archive log file devdb12_1_283_801988635.arc manually from primary to standby archive destination location. Step  2 Run the below command in standby database. ALTER DATABASE REGISTER LOGFILE '/dbArch/oradata/devdb12/ devdb12_1_283_801988635.arc';   Step  3 Verify the standby database and see if the co
Read more

How to check UNDO tablespace usage and who is using more undo

-
Query To check UNDO tablespace usage: ================================ set linesize 152 col tablespace_name for a20 col status for a10 select tablespace_name,status,count(extent_id) "Extent Count", sum(blocks) "Total Blocks",sum(bytes)/(1024*1024*1024) spaceInGB from   dba_undo_extents group by  tablespace_name, status having tablespace_name=upper('&TSNAME') order by tablespace_name; Query To check who is using more UNDO space: ===================================== SET termout ON SET heading ON SET PAGESIZE   6000 SET LINESIZE   200 COLUMN pgm_notes    FORMAT a80        HEADING 'Notes' COLUMN rbs          FORMAT a16         HEADING 'RBS'             JUST center COLUMN oracle_user  FORMAT a12        HEADING 'Oracle|Username' COLUMN sid_serial   FORMAT a12        HEADING 'SID,Serial' COLUMN unix_pid     FORMAT a6         HEADING 'O/S|PID' COLUMN Client_User  FORMAT a14        HEADING 'Cl
Read more