How to find the last RMAN backup’s log?

set pagesize 2000

set linesize 2000

select

output

from

GV$RMAN_OUTPUT

where

session_recid =

(

select

session_recid

from

V$RMAN_BACKUP_JOB_DETAILS

where

session_key=(select max(session_key) from v$RMAN_BACKUP_JOB_DETAILS)

);

OUTPUT

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

connected to target database: TEST (DBID=3221280546)

connected to recovery catalog database

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=2817 devtype=DISK

validation succeeded for archived log

archive log filename=/data/TEST/arch/TEST1_1243_891780004.arc recid=2465 stamp=893720321

validation succeeded for archived log

archive log filename=/backup/data/TEST/arch/TEST1_1243_891780004.arc recid=2466 stamp=893720321

validation succeeded for archived log

archive log filename=/data/TEST/arch/TEST1_1244_891780004.arc recid=2467 stamp=893720321

validation succeeded for archived log

archive log filename=/backup/data/TEST/arch/TEST1_1244_891780004.arc recid=2468 stamp=893720321

validation succeeded for archived log

archive log filename=/data/TEST/arch/TEST1_1245_891780004.arc recid=2469 stamp=893720326

validation succeeded for archived log

archive log filename=/backup/data/TEST/arch/TEST1_1245_891780004.arc recid=2470 stamp=893720326

validation succeeded for archived log

archive log filename=/data/TEST/arch/TEST1_1246_891780004.arc recid=2471 stamp=893720326

validation succeeded for archived log

archive log filename=/backup/data/TEST/arch/TEST1_1246_891780004.arc recid=2472 stamp=893720326

validation succeeded for archived log

archive log filename=/data/TEST/arch/TEST1_1247_891780004.arc recid=2475 stamp=893720329

validation succeeded for archived log

archive log filename=/backup/data/TEST/arch/TEST1_1247_891780004.arc recid=2476 stamp=893720329

validation succeeded for archived log

archive log filename=/data/TEST/arch/TEST1_1248_891780004.arc recid=2473 stamp=893720329

validation succeeded for archived log

archive log filename=/backup/data/TEST/arch/TEST1_1248_891780004.arc recid=2474 stamp=893720329

archive log filename=/backup/data/TEST/arch/TEST1_1251_891780004.arc recid=2482 stamp=893722157

validation succeeded for archived log

archive log filename=/data/TEST/arch/TEST1_1252_891780004.arc recid=2483 stamp=893723957

validation succeeded for archived log

archive log filename=/backup/data/TEST/arch/TEST1_1252_891780004.arc recid=2484 stamp=893723957

validation succeeded for archived log

archive log filename=/data/TEST/arch/TEST1_1253_891780004.arc recid=2485 stamp=893725758

validation succeeded for archived log

archive log filename=/backup/data/TEST/arch/TEST1_1253_891780004.arc recid=2486 stamp=893725758

validation succeeded for archived log

archive log filename=/data/TEST/arch/TEST1_1254_891780004.arc recid=2487 stamp=893727558

validation succeeded for archived log

archive log filename=/backup/data/TEST/arch/TEST1_1254_891780004.arc recid=2488 stamp=893727558

.

.

.

input archive log thread=1 sequence=1305 recid=2589 stamp=893806765

channel CH1: starting piece 1 at 22-OCT-15

channel CH1: finished piece 1 at 22-OCT-15

piece handle=/backup/data/TEST/rman_backup/arch/FULL_TEST_1_5nqkcq6k_1_1 tag=TAG20151022T234001 comment=NONE

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

Finished backup at 22-OCT-15

released channel: CH1

released channel: CH2

released channel: CH3

967 rows selected.

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!

Move a datafile with RMAN

Using RMAN is the best way before 12c to relocate a datafile with a minimum downtime. The only downtime is for switching from the old datafile to the new one and recover it. That means that the datafile size as no impact on the downtime.

Check schmea report before moving datafile.

Here is the situation before moving the datafile

RMAN> report schema
2> ;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name XE

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 360 SYSTEM *** C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF
2 650 SYSAUX *** C:\ORACLEXE\APP\ORACLE\ORADATA\XE\UNDOTBS1.DBF
3 25 UNDOTBS1 *** C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSAUX.DBF
4 100 USERS *** C:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS.DBF


List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 C:\ORACLEXE\APP\ORACLE\ORADATA\XE\TEMP.DBF


1.Create a copy of datafile to new destination.

The first step is to create a copy of your datafile in the new destination. Here, we will copy the USERS datafile to
C:\ORACLEXE\APP\ORACLE\ORADATA2\XE:


RMAN> copy datafile 4 to 'C:\ORACLEXE\APP\ORACLE\ORADATA2\XE\USERS.DBF';

Starting backup at 14-01-27
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=50 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=C:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS.DBF
output file name=C:\ORACLEXE\APP\ORACLE\ORADATA2\XE\USERS.DBF tag=TAG20140127T132133 RECID=1 STAMP=837955296
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 14-01-27

2.Take tablespace offline.

Now I hae a copy of my datafile in the new destination, I have to switch from the old datafile to the new one. Of course this operation cannot be done online, I’ll have to put my tablespace offline, this is the begining of the downtime.

RMAN> SQL 'ALTER TABLESPACE USERS OFFLINE';

sql statement: ALTER TABLESPACE USERS OFFLINE

3.Switch old datafile to new datafile.

Now I can switch from the old datafile to the new one:

RMAN> SWITCH DATAFILE 4 TO COPY;

datafile 4 switched to datafile copy "C:\ORACLEXE\APP\ORACLE\ORADATA2\XE\USERS.DBF"

4.Recover the datafile.

Now we have to recover the datafile:

RMAN> RECOVER DATAFILE 4;

Starting recover at 14-01-27
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 14-01-27

5.Put tablespace back to online.

And we put back the tablespace USERS online:

RMAN> SQL 'ALTER TABLESPACE USERS ONLINE';

sql statement: ALTER TABLESPACE USERS ONLINE

6. Check status now.

Here is the new situation:

Report of database schema for database with db_unique_name XE

RMAN> report schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 360 SYSTEM *** C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF
2 650 SYSAUX *** C:\ORACLEXE\APP\ORACLE\ORADATA\XE\UNDOTBS1.DBF
3 25 UNDOTBS1 *** C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSAUX.DBF
4 100 USERS *** C:\ORACLEXE\APP\ORACLE\ORADATA2\XE\USERS.DBF

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 C:\ORACLEXE\APP\ORACLE\ORADATA\XE\TEMP.DBF

The old datafile is kept as copy:

7.Check old datafile copy.

RMAN> list copy of datafile 4;

List of Datafile Copies
=======================

Key File S Completion Time Ckp SCN Ckp Time
------- ---- - --------------- ---------- ---------------
2 4 A 14-01-27 379024 14-01-27
Name: C:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS.DBF

Check 8.Remove old copy if do not need

We can remove it as we no longer need it:

RMAN> DELETE DATAFILECOPY 2;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=50 device type=DISK
List of Datafile Copies
=======================

Key File S Completion Time Ckp SCN Ckp Time
------- ---- - --------------- ---------- ---------------
2 4 A 14-01-27 379024 14-01-27
Name: C:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS.DBF

Do you really want to delete the above objects (enter YES or NO)? yes
deleted datafile copy
datafile copy file name=C:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS.DBF RECID=2 STAMP=837955601

Deleted 1 objects


And its done :)




I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!

How to catalog backups / archivelogs / datafile copies / control file copies

RMAN Catalog:It is mainly used for following:

Can store database backup scripts and backup information to safe place(RMAN Repository)apart from target database control file.
Can add backup pieces and image copies of backup on disk to the RMAN repository.
Can record a data file copy as a level 0 incremental backup in the RMAN repository and that can be used foran incremental backup.
Can record latest user-managed datafile copies to RMAN repository made after the clean shutdown of database.

Limitations and Advantages of Catalog:

To connect to target database using RMAN target database must be mounted or open.

1. Catalog database must be OPEN if RMAN is connected to catalog database.
2. RMAN can only catalog a datafile copy, control file copy, archived log or backup piece if these areuser-managed copy (OS level copy).
3. Catalogis accessible on disk.
4. File belongs to another database can not be cataloged using CATALOG.
5. All user managed (File system) backups considered as an IMAGE COPIES by RMAN while cataloged.
6. RMAN does not check whether the file was correctly copied by the operating system utility; it just reads and checks the header during cataloging but file should be clean(consistent) while copying through OS utilities so .

Cataloging Backups Examples:

1. Cataloging Archived Logs:Here we assumed that archive logs have been moved to other location using operating system utilities (CP/MV/COPY) and we cataloging those moved Archiveloged to RMAN REPOSITORY.

RMAN > CATALOG ARCHIVELOG ‘?/oradata/backup/arch_1.dbf’, ‘?/oradata/backup/arch_9.dbf’;

If you need to catalog multiple archive files then use below simple script which will generate .rcv file and run newly created .rcv file.

[oracle@test ~] echo > catalog.rcv for archivelog in /u01/test/arch/* ; do echo "catalog archivelog '$archivelog';" >> catalog.rcv done

Now connect to rman promt and run .rcv using below command.

[oracle@test ~] Rman target / cmdfile=catalog.rcv

2.Cataloging consistent copy of datafile as an incremental backup:Here we are cataloging datafile copy data01.dbfas an incremental level 0 backup to RMAN repository,

This datafile copy must be consistent and can be either taken by OS copy commands or by RAMN backup as copy command:

CATALOG DATAFILECOPY ‘?/oracle/oradata/data01.dbf’ LEVEL 0;

3.Cataloging controlfile copy in the RMAN Repository tken by user:

a. Take backup of controle file by using below query:

SQL> alter database backup controlfile to ‘/u01/bkp/controlfile01.dbf’;

b. Catalog above controlfile copy to RMAN REPOSITORY using blow query:

RMAN> catalog controlfilecopy ‘oracle/oradata/control01.ctl’;

4.Cataloging all the database file copies present in one Directory:

Below example will catalog all the archivelog copied using OS command to directory /u01/bkp/archlog:

RMAN> CATALOG START WITH ‘/u01/bkp/archlog’;

5.Cataloging Files in the Flash Recovery Area:Below will catalog Flash Recovery Area without prompting to user:

CATALOG RECOVERY AREA NOPROMPT;

6. Cataloging RMAN Backup Pieces:BELOW WILL CATALOG a RMAN backup piece to RMAN Repository which is manually copied using OS commands to default backup location:

CATALOG BACKUPPIECE ‘/u01/bkp/rman/KOKTSV_ORCL_RMAN_ONL_ST’;

7.Below command will catalog the Backup Pieces present in the Location set for DB_RECOVERY_FILE_DEST Parameter:

RMAN> catalog db_recovery_file_dest;

Note:catalog backuppiece, catalog recovery area, catalog start with, catalog db_recovery_file_dest commands are not available in 9i.

I hope this article helped you. Your suggestions/feedbacks are most welcome.

Keep learning... Have a great day!!!

Recovery from complete loss of all online redo log files using RMAN

STEP 1: Check database details.

SQL> select instance_name,version from v$instance;

INSTANCE_NAME    VERSION

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

TEST           11.2.0.3.0

SQL> select member from v$Logfile;

MEMBER

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

/u02/ORACLE/TEST/redo03.log

/u02/ORACLE/TEST/redo02.log

/u02/ORACLE/TEST/redo01.log

STEP 2: Delete log files from os level to create scenario.

TEST:/u02/ORACLE/TEST>rm redo*.log

STEP 3: find last archive log sequence.

If one or all of the online redo logfiles are delete then the database hangs and in the alert log file we can see the following error message:

Tue Jan 30 00:47:19 2015

ARC1: Failed to archive thread 1 sequence 93 (0)

Tue Jan 30 00:47:24 2007

Errors in file /opt/oracle/admin/TEST/bdump/TEST_arc0_32722.trc:

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

ORA-00312: online log 2 thread 1: '/u02/ORACLE/TEST/redo02.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

The file is missing at the operating system level.

Using RMAN we can recover from this error by restoring the database from the backup and recovering to the last available archived redo logfile.

From the error message in the log file we can get the last archived file in our case it is sequence 92 as the error shows that it fails to archive the log file sequence 93.

SQL> select * from v$Log;

  GROUP#   THREAD#  SEQUENCE#   BYTES    MEMBERS ARC   STATUS    FIRST_CHANGE#   FIRST_TIM

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

    1          1         95   52428800          1 NO  CURRENT     3203078        30-JAN-15

    2          1         93   52428800          1 NO  INACTIVE    3202983        30-JAN-15

    3          1         94   52428800          1 NO  INACTIVE    3203074        30-JAN-15

At the operating system also we can find the last archived logfile:

TEST:/u02/ORACLE/TEST/arch> ls –lrt

total 54824

-rw-r-----    1 oracle   dba        714240 Jan 29 16:02 arch_1_90_613129285.dbf

-rw-r-----    1 oracle   dba      46281216 Jan 30 00:37 arch_1_91_613129285.dbf

-rw-r-----    1 oracle   dba         11264 Jan 30 00:41 arch_1_92_613129285.dbf

STEP 4: Shut down the database and startup in Mount stage.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area  167772160 bytes

Fixed Size                  2069680 bytes

Variable Size              92277584 bytes

Database Buffers           67108864 bytes

Redo Buffers                6316032 bytes

Database mounted.

STEP 5: Connect to rman to Restore and Recover database.

TEST:/u02/ORACLE/TEST>rman target /

Recovery Manager: Release 10.2.0.2.0 - Production on Tue Jan 30 00:53:21 2015

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

connected to target database: TEST (DBID=1493612009, not open)

RMAN> run {

2> set until sequence 93;

3> restore database;

4> recover database;

5>  alter database open resetlogs;

6> }

executing command: SET until clause

Starting restore at 30-JAN-15

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=156 devtype=DISK

allocated channel: ORA_SBT_TAPE_1

channel ORA_SBT_TAPE_1: sid=155 devtype=SBT_TAPE

channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 5.2.4.0

channel ORA_SBT_TAPE_1: starting datafile backupset restore

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

restoring datafile 00001 to /u02/ORACLE/TEST/system01.dbf

restoring datafile 00002 to /u02/ORACLE/TEST/undotbs01.dbf

restoring datafile 00003 to /u02/ORACLE/TEST/sysaux01.dbf

restoring datafile 00004 to /u02/ORACLE/TEST/users01.dbf

restoring datafile 00005 to /u02/ORACLE/TEST/users02.dbf

restoring datafile 00006 to /u02/ORACLE/TEST/users03.dbf

restoring datafile 00007 to /u02/ORACLE/TEST/users05.dbf

restoring datafile 00008 to /u02/ORACLE/TEST/users06.dbf

restoring datafile 00009 to /u02/ORACLE/TEST/users07.dbf

restoring datafile 00010 to /u02/ORACLE/TEST/users04.dbf

restoring datafile 00011 to /u02/ORACLE/TEST/drtbs1.dbf

restoring datafile 00012 to /u02/ORACLE/TEST/drtbs2.dbf

restoring datafile 00013 to /tmp/undonew.dbf

channel ORA_SBT_TAPE_1: reading from backup piece 5mi8ornj_1_1

channel ORA_SBT_TAPE_1: restored backup piece 1

piece handle=5mi8ornj_1_1 tag=TAG20070130T004019

channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:01:06

Finished restore at 30-JAN-15

Starting recover at 30-JAN-15

using channel ORA_DISK_1

using channel ORA_SBT_TAPE_1

starting media recovery

archive log thread 1 sequence 92 is already on disk as file /u02/ORACLE/TEST/arch/arch_1_92_613129285.dbf

archive log filename=/u02/ORACLE/TEST/arch/arch_1_92_613129285.dbf thread=1 sequence=92

media recovery complete, elapsed time: 00:00:01

Finished recover at 30-JAN-15

database opened

RMAN>exit

STEP 6: Check all the files are created physically.

The recovery process creates the online redo logfiles at the operating system level also.

TEST:/u02/ORACLE/TEST>ls -lrt redo*

-rw-r-----    1 oracle   dba      52429312 Jan 30 01:00 redo03.log

-rw-r-----    1 oracle   dba      52429312 Jan 30 01:00 redo02.log

-rw-r-----    1 oracle   dba      52429312 Jan 30 01:00 redo01.log

Since we have done an incomplete recover with open resetlogs, we should take a fresh complete backup of the database.

NOTE: Please make sure you remove all the old archived logfiles from the archived area.

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!

Database Point-In-Time Recovery

 STEP 1: Note the current database clock time

SQL> select to_char(sysdate,'DD-MON-YY:HH24:MI:SS') from dual;

TO_CHAR(SYSDATE,'DD-MON-

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

20-FEB-15:17:45:55

STEP 2: Drop a table

We will now drop the table MYOBJECTS and then perform an RMAN point-in-time recovery to restore the database to a point in time just before the table was dropped. Note the number of rows in the table.

SQL> select count(*) from myobjects;

  COUNT(*)

----------

     13420

SQL> drop table myobjects;

Table dropped.

STEP 3: Shutdown and then mount the database in preparation for restore and recovery.

SQL> conn / as sysdba

Connected.

SQL> shutdown abort

ORACLE instance shut down.

SQL> startup mount;

[oracle@NVMBD1BZY150D00 CLONEDB]$ rman target / catalog rman/rman@rmanp

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Feb 20 17:49:27 2015

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

connected to target database: CLONEDB (DBID=958582256, not open)

connected to recovery catalog database

Note – the incremental backup since available is restored before applying the archive log files. The archive log files that will be applied are those that have been generated after the Level 1 incremental backup.

Since we are restoring the database to a point in the past, we will have to open the database with the RESETLOGS option.

RMAN> run {

2> set until time "to_date('20-FEB-15:17:45:55','dd-MON-yy hh24:mi:ss')";

3> restore database;

4> recover database;

5> }

executing command: SET until clause

Starting restore at 20-FEB-15

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=176 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore

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

channel ORA_DISK_1: restoring datafile 00001 to /data/oracle/app/oracle/oradata/CLONEDB/system.dbf

channel ORA_DISK_1: restoring datafile 00002 to /data/oracle/app/oracle/oradata/CLONEDB/user04.dbf

channel ORA_DISK_1: restoring datafile 00003 to /data/oracle/app/oracle/oradata/CLONEDB/sysaux.dbf

channel ORA_DISK_1: restoring datafile 00004 to /data/oracle/app/oracle/oradata/CLONEDB/undo.dbf

channel ORA_DISK_1: restoring datafile 00005 to /data/oracle/app/oracle/oradata/CLONEDB/test1_tmp.dbf

channel ORA_DISK_1: restoring datafile 00006 to /data/oracle/app/oracle/oradata/CLONEDB/test_corrupt.dbf

channel ORA_DISK_1: restoring datafile 00007 to /data/oracle/app/oracle/oradata/CLONEDB/users_01.dbf

channel ORA_DISK_1: reading from backup piece /data1/backup/clonedb/bkp.38pvodgm_1_1

channel ORA_DISK_1: piece handle=/data1/backup/clonedb/bkp.38pvodgm_1_1 tag=LEVEL 0

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:01:25

Finished restore at 20-FEB-15

Starting recover at 20-FEB-15

using channel ORA_DISK_1

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: /data/oracle/app/oracle/oradata/CLONEDB/system.dbf

destination for restore of datafile 00002: /data/oracle/app/oracle/oradata/CLONEDB/user04.dbf

destination for restore of datafile 00003: /data/oracle/app/oracle/oradata/CLONEDB/sysaux.dbf

destination for restore of datafile 00004: /data/oracle/app/oracle/oradata/CLONEDB/undo.dbf

destination for restore of datafile 00005: /data/oracle/app/oracle/oradata/CLONEDB/test1_tmp.dbf

destination for restore of datafile 00006: /data/oracle/app/oracle/oradata/CLONEDB/test_corrupt.dbf

destination for restore of datafile 00007: /data/oracle/app/oracle/oradata/CLONEDB/users_01.dbf

channel ORA_DISK_1: reading from backup piece /data1/backup/clonedb/bkp.3cpvog7a_1_1

channel ORA_DISK_1: piece handle=/data1/backup/clonedb/bkp.3cpvog7a_1_1 tag=LEVEL 1 BACKUP

channel ORA_DISK_1: restored backup piece 1

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

starting media recovery

archived log for thread 1 with sequence 6 is already on disk as file /data1/clonedb/archive/1_6_872166459.dbf

archived log for thread 1 with sequence 7 is already on disk as file /data1/clonedb/archive/1_7_872166459.dbf

archived log for thread 1 with sequence 8 is already on disk as file /data1/clonedb/archive/1_8_872166459.dbf

archived log for thread 1 with sequence 9 is already on disk as file /data1/clonedb/archive/1_9_872166459.dbf

archived log for thread 1 with sequence 10 is already on disk as file /data1/clonedb/archive/1_10_872166459.dbf

archived log for thread 1 with sequence 11 is already on disk as file /data1/clonedb/archive/1_11_872166459.dbf

archived log for thread 1 with sequence 12 is already on disk as file /data1/clonedb/archive/1_12_872166459.dbf

archived log file name=/data1/clonedb/archive/1_6_872166459.dbf thread=1 sequence=6

archived log file name=/data1/clonedb/archive/1_7_872166459.dbf thread=1 sequence=7

archived log file name=/data1/clonedb/archive/1_8_872166459.dbf thread=1 sequence=8

archived log file name=/data1/clonedb/archive/1_9_872166459.dbf thread=1 sequence=9

archived log file name=/data1/clonedb/archive/1_10_872166459.dbf thread=1 sequence=10

media recovery complete, elapsed time: 00:00:01

Finished recover at 20-FEB-15

STEP 4: Start the database with resetlogs option.

RMAN> sql 'alter database open resetlogs';

sql statement: alter database open resetlogs

STEP 5: Connect to database and confirm that table is present.

We will now connect to the database to confirm that our table which we had earlier dropped has been restored as well as the record count in the table matches that taken before the table drop.

[oracle@NVMBD1BZY150D00 CLONEDB]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Feb 20 17:59:52 2015

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, OLAP, Data Mining and Real Application Testing options

SQL> select count(*) from myobjects;

  COUNT(*)

----------

     13420

STEP 6: Reset the database in recovery catalog.

Because we have done a RESETLOGS we need to reset the database in the recovery catalog.

[oracle@NVMBD1BZY150D00 CLONEDB]$ rman target / catalog rman/rman@rmanp

RMAN> reset database;

new incarnation of database registered in recovery catalog

starting full resync of recovery catalog

full resync complete

STEP 7: Standard Practice take full fresh backup of database.

We will now take a fresh level 0 incremental backup

RMAN> backup incremental level 0 database;

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!