DROP A Tablespace After a Backup

What happens if you drop a tablespace after it is backed up ?

Here's the backup :

RMAN> backup as compressed backupset database;

Starting backup at 16-FEB-14

using channel ORA_DISK_1

channel ORA_DISK_1: starting compressed full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00002 name=/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf

input datafile file number=00001 name=/home/oracle/app/oracle/oradata/orcl/system01.dbf

input datafile file number=00004 name=/home/oracle/app/oracle/oradata/orcl/users01.dbf

input datafile file number=00003 name=/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf

input datafile file number=00011 name=/home/oracle/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_hemant_8pnowslc_.dbf

input datafile file number=00005 name=/home/oracle/app/oracle/oradata/orcl/example01.dbf

input datafile file number=00010 name=/home/oracle/app/oracle/oradata/orcl/APEX_2614203650434107.dbf

channel ORA_DISK_1: starting piece 1 at 16-FEB-14

channel ORA_DISK_1: finished piece 1 at 16-FEB-14

piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2014_02_16/o1_mf_nnndf_TAG20140216T080419_8vvnzn37_.bkp tag=TAG20140216T080419 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:02:36

channel ORA_DISK_1: starting compressed full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

including current SPFILE in backup set

channel ORA_DISK_1: starting piece 1 at 16-FEB-14

channel ORA_DISK_1: finished piece 1 at 16-FEB-14

piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2014_02_16/o1_mf_ncsnf_TAG20140216T080419_8vvo4k8w_.bkp tag=TAG20140216T080419 comment=NONE

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

Finished backup at 16-FEB-14

RMAN>

Here's the subsequent DROP TABLESPACE :

SQL> drop tablespace APEX_2614203650434107 including contents and datafiles;

Tablespace dropped.

SQL>

What happens when I try to LIST the BACKUP of the datafile / tablespace ? 

RMAN> list backup of datafile 10;

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

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

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

RMAN-03002: failure of list command at 02/16/2014 08:07:44

RMAN-20201: datafile not found in the recovery catalog

RMAN-06010: error while looking up datafile: 10

RMAN>

RMAN> list backup of tablespace APEX_2614203650434107;

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

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

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

RMAN-03002: failure of list command at 02/16/2014 08:12:12

RMAN-20202: Tablespace not found in the recovery catalog

RMAN-06019: could not translate tablespace name "APEX_2614203650434107"

RMAN>

What does a full LIST BACKUP OF DATABASE show ?

RMAN> list backup of database ;

List of Backup Sets

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

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

4       Full    799.58M    DISK        00:02:30     16-FEB-14     

        BP Key: 4   Status: AVAILABLE  Compressed: YES  Tag: TAG20140616T080419

        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2014_02_16/o1_mf_nnndf_TAG20140216T080419_8vvnzn37_.bkp

  List of Datafiles in backup set 4

  File LV Type Ckp SCN    Ckp Time  Name

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

  1       Full 14093203   16-FEB-14 /home/oracle/app/oracle/oradata/orcl/system01.dbf

  2       Full 14093203   16-FEB-14 /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf

  3       Full 14093203   16-FEB-14 /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf

  4       Full 14093203   16-FEB-14 /home/oracle/app/oracle/oradata/orcl/users01.dbf

  5       Full 14093203   16-FEB-14 /home/oracle/app/oracle/oradata/orcl/example01.dbf

  10      Full 14093203   16-FEB-14

  11      Full 14093203   16-FEB-14 /home/oracle/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_hemant_8pnowslc_.dbf

RMAN>

Datafile 10 appears as a NULL entry. It cannot be restored as it no longer belongs to the database. 

A RESTORE obviously fails : 

RMAN> restore datafile 10;

Starting restore at 16-FEB-14

using channel ORA_DISK_1

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

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

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

RMAN-03002: failure of restore command at 02/16/2014 08:15:32

RMAN-20201: datafile not found in the recovery catalog

RMAN-06010: error while looking up datafile: 10

RMAN>

So, there you have it.  Once a datafile doesn't belong to the database it cannot be restored.

BACKUP CURRENT CONTROLFILE creates a Snapshot Controlfile

Recently, there was a discussion on forums about when a Snapshot controlfile is created.  It is known that the snapshot controlfile is created to re-synchronise with the Catalog.

But in my test database environment, I don't use a Catalog.  I only use the controlfile (and backups of it).

One poser said that he ran BACKUP DATABASE followed by BACKUP CURRENT CONTROLFILE. He asked if the same snapshot controlfile was used [through the two commands].  I pointed out that since they were two *separate* BACKUP calls, Oracle would create a separate Snapshot Controlfile for the second BACKUP command.

Here is a simple demo (note : I don't use a Catalog)

First the current state of the directory ($ORACLE_HOME/dbs in my case) where the Snapshot Controlfile would be created :

[oracle@localhost dbs]$ pwd

/home/oracle/app/oracle/product/11.2.0/dbhome_2/dbs

[oracle@localhost dbs]$ date

Thu Mar 20 08:17:46 PDT 2014

[oracle@localhost dbs]$ ls -ltr|tail -3

-rw-r----- 1 oracle oracle 1536 Oct  2  2010 orapworcl

-rw-rw---- 1 oracle oracle 1544 Mar 20 08:16 hc_orcl.dat

-rw-r----- 1 oracle oracle 3584 Mar 20 08:16 spfileorcl.ora

There is no snapshot currently present. 

Next, I run a BACKUP CURRENT CONTROLFILE :

[oracle@localhost dbs]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Thu Mar 20 08:17:53 2014

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

connected to target database: ORCL (DBID=1229390655)

RMAN> backup current controlfile;

Starting backup at 20-MAR-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=69 device type=DISK

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 20-MAR-14

channel ORA_DISK_1: finished piece 1 at 20-MAR-14

piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2014_03_20/o1_mf_ncnnf_TAG20140520T081800_8snhob80_.bkp tag=TAG20140320T081800 comment=NONE

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

Finished backup at 20-MAR-14

RMAN> quit

Recovery Manager complete.

The controlfile backup got written to the FRA.

 Is there a snapshot controlfile ?

[oracle@localhost dbs]$ ls -ltr|tail -3

-rw-rw---- 1 oracle oracle    1544 Mar 20 08:16 hc_orcl.dat

-rw-r----- 1 oracle oracle    3584 Mar 20 08:16 spfileorcl.ora

-rw-rw---- 1 oracle oracle 9748480 Mar 20 08:18 snapcf_orcl.f

[oracle@localhost dbs]$

Yes, a snapshot controlfile did get created ! 

What happens if a database (or tablespace) is left in BACKUP mode

What if a database (or a tablespace) is left in [BEGIN] BACKUP mode and an RMAN Backup is made.

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     628

Next log sequence to archive   630

Current log sequence           630

SQL> set time on

08:19:45 SQL> alter database begin backup;

Database altered.

08:19:51 SQL>

08:22:00 SQL> alter system archive log current;

System altered.

08:22:06 SQL> create table hemant.dummy_1 as select * from dba_source;

Table created.

08:22:35 SQL> alter system archive log current;

System altered.

08:22:47 SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     632

Next log sequence to archive   634

Current log sequence           634

08:22:50 SQL>

[oracle@localhost ~]$ set NLS_DATE_FORMAT=DD_MON_RR_HH24_MI;export NLS_DATE_FORMAT

[oracle@localhost ~]$ date

Mon Jan 29 08:23:47 PDT 2014

[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Mon Jan 29 08:23:59 2014

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

connected to target database: ORCL (DBID=1229390655)

RMAN> backup database plus archivelog delete input;

Starting backup at 29-JAN-14

current log archived

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=52 device type=DISK

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=621 RECID=6 STAMP=818238417

input archived log thread=1 sequence=622 RECID=7 STAMP=818238426

input archived log thread=1 sequence=623 RECID=8 STAMP=820627686

input archived log thread=1 sequence=624 RECID=9 STAMP=820657899

input archived log thread=1 sequence=625 RECID=10 STAMP=820915708

input archived log thread=1 sequence=626 RECID=11 STAMP=821863415

input archived log thread=1 sequence=627 RECID=12 STAMP=821935812

input archived log thread=1 sequence=628 RECID=13 STAMP=821952099

input archived log thread=1 sequence=629 RECID=14 STAMP=822039518

input archived log thread=1 sequence=630 RECID=15 STAMP=822039726

input archived log thread=1 sequence=631 RECID=16 STAMP=822039751

input archived log thread=1 sequence=632 RECID=17 STAMP=822039756

input archived log thread=1 sequence=633 RECID=18 STAMP=822039767

input archived log thread=1 sequence=634 RECID=19 STAMP=822039899

channel ORA_DISK_1: starting piece 1 at 29-JAN-14

channel ORA_DISK_1: finished piece 1 at 29-JAN-14

piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2014_01_29/o1_mf_annnn_TAG20140129T082500_8zf2bdkc_.bkp tag=TAG20140129T082500 comment=NONE

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

channel ORA_DISK_1: deleting archived log(s)

archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2014_06_16/o1_mf_1_621_8vvpb13c_.arc RECID=6 STAMP=818238417

archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2014_06_16/o1_mf_1_622_8vvpbbf6_.arc RECID=7 STAMP=818238426

archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2014_01_13/o1_mf_1_623_8y1z6obz_.arc RECID=8 STAMP=820627686

archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2014_01_13/o1_mf_1_624_8y2wpst6_.arc RECID=9 STAMP=820657899

archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2014_01_16/o1_mf_1_625_8ybrhcgp_.arc RECID=10 STAMP=820915708

archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2014_01_27/o1_mf_1_626_8z7oz70c_.arc RECID=11 STAMP=821863415

archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2014_01_28/o1_mf_1_627_8z9wombb_.arc RECID=12 STAMP=821935812

archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2014_01_28/o1_mf_1_628_8zbdll9r_.arc RECID=13 STAMP=821952099

archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2014_01_29/o1_mf_1_629_8zf1yfv9_.arc RECID=14 STAMP=822039518

archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2014_01_29/o1_mf_1_630_8zf24yk0_.arc RECID=15 STAMP=822039726

archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2014_01_29/o1_mf_1_631_8zf25lt4_.arc RECID=16 STAMP=822039751

archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2014_01_29/o1_mf_1_632_8zf25vw1_.arc RECID=17 STAMP=822039756

archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2014_01_29/o1_mf_1_633_8zf2675w_.arc RECID=18 STAMP=822039767

archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2014_01_29/o1_mf_1_634_8zf2bcd9_.arc RECID=19 STAMP=822039899

Finished backup at 29-JAN-14

Starting backup at 29-JAN-14

using channel ORA_DISK_1

RMAN-06554: WARNING: file 1 is in backup mode

RMAN-06554: WARNING: file 2 is in backup mode

RMAN-06554: WARNING: file 3 is in backup mode

RMAN-06554: WARNING: file 4 is in backup mode

RMAN-06554: WARNING: file 5 is in backup mode

RMAN-06554: WARNING: file 11 is in backup mode

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=/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf

input datafile file number=00001 name=/home/oracle/app/oracle/oradata/orcl/system01.dbf

input datafile file number=00004 name=/home/oracle/app/oracle/oradata/orcl/users01.dbf

input datafile file number=00003 name=/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf

input datafile file number=00011 name=/home/oracle/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_hemant_8pnowslc_.dbf

input datafile file number=00005 name=/home/oracle/app/oracle/oradata/orcl/example01.dbf

channel ORA_DISK_1: starting piece 1 at 29-JAN-14

channel ORA_DISK_1: finished piece 1 at 29-JAN-14

piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2014_01_29/o1_mf_nnndf_TAG20140129T082504_8zf2bj83_.bkp tag=TAG20140129T082504 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:02:06

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

including current SPFILE in backup set

channel ORA_DISK_1: starting piece 1 at 29-JAN-14

channel ORA_DISK_1: finished piece 1 at 29-JAN-14

piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2014_01_29/o1_mf_ncsnf_TAG20140129T082504_8zf2gk3r_.bkp tag=TAG20140129T082504 comment=NONE

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

Finished backup at 29-JAN-14

Starting backup at 29-JAN-14

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=635 RECID=20 STAMP=822039962

input archived log thread=1 sequence=636 RECID=21 STAMP=822039979

input archived log thread=1 sequence=637 RECID=22 STAMP=822039996

input archived log thread=1 sequence=638 RECID=23 STAMP=822040002

input archived log thread=1 sequence=639 RECID=24 STAMP=822040006

input archived log thread=1 sequence=640 RECID=25 STAMP=822040015

input archived log thread=1 sequence=641 RECID=26 STAMP=822040023

input archived log thread=1 sequence=642 RECID=27 STAMP=822040028

input archived log thread=1 sequence=643 RECID=28 STAMP=822040037

channel ORA_DISK_1: starting piece 1 at 29-JAN-14

channel ORA_DISK_1: finished piece 1 at 29-JAN-14

piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2014_01_29/o1_mf_annnn_TAG20140129T082717_8zf2govj_.bkp tag=TAG20140129T082717 comment=NONE

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

channel ORA_DISK_1: deleting archived log(s)

archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2014_01_29/o1_mf_1_635_8zf2d7mb_.arc RECID=20 STAMP=822039962

archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2014_01_29/o1_mf_1_636_8zf2drd5_.arc RECID=21 STAMP=822039979

archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2014_01_29/o1_mf_1_637_8zf2f4p3_.arc RECID=22 STAMP=822039996

archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2014_01_29/o1_mf_1_638_8zf2fjqo_.arc RECID=23 STAMP=822040002

archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2014_01_29/o1_mf_1_639_8zf2fnqo_.arc RECID=24 STAMP=822040006

archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2014_01_29/o1_mf_1_640_8zf2fybs_.arc RECID=25 STAMP=822040015

archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2014_01_29/o1_mf_1_641_8zf2g6hn_.arc RECID=26 STAMP=822040023

archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2014_01_29/o1_mf_1_642_8zf2gdlj_.arc RECID=27 STAMP=822040028

archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2014_01_29/o1_mf_1_643_8zf2gom0_.arc RECID=28 STAMP=822040037

Finished backup at 29-JAN-13

RMAN>

08:25:43 SQL> delete hemant.dummy_1;

689472 rows deleted.

08:26:57 SQL> alter system archive log current;

System altered.

08:27:08 SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     641

Next log sequence to archive   643

Current log sequence           643

08:27:13 SQL>

08:28:21 SQL> shutdown immediate

ORA-01149: cannot shutdown - file 1 has online backup set

ORA-01110: data file 1: '/home/oracle/app/oracle/oradata/orcl/system01.dbf'

08:28:38 SQL> shutdown abort;

ORACLE instance shut down.

08:28:46 SQL>

[oracle@localhost ~]$ cd /home/oracle/app/oracle/oradata/orcl

[oracle@localhost orcl]$ ls

afiedt.buf     ORCL        redo03.log    temp01.dbf

control01.ctl  redo01.log  sysaux01.dbf  undotbs01.dbf

example01.dbf  redo02.log  system01.dbf  users01.dbf

[oracle@localhost orcl]$ rm *.dbf

[oracle@localhost orcl]$

[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Mon Jan 29 08:29:45 2014

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

connected to target database (not started)

RMAN> startup mount;

Oracle instance started

database mounted

Total System Global Area     456146944 bytes

Fixed Size                     1344840 bytes

Variable Size                394267320 bytes

Database Buffers              54525952 bytes

Redo Buffers                   6008832 bytes

RMAN> restore database;

Starting restore at 29-JAN-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=18 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 /home/oracle/app/oracle/oradata/orcl/system01.dbf

channel ORA_DISK_1: restoring datafile 00002 to /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf

channel ORA_DISK_1: restoring datafile 00003 to /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf

channel ORA_DISK_1: restoring datafile 00004 to /home/oracle/app/oracle/oradata/orcl/users01.dbf

channel ORA_DISK_1: restoring datafile 00005 to /home/oracle/app/oracle/oradata/orcl/example01.dbf

channel ORA_DISK_1: restoring datafile 00011 to /home/oracle/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_hemant_8pnowslc_.dbf

channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2014_01_29/o1_mf_nnndf_TAG20140129T082504_8zf2bj83_.bkp

channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2014_01_29/o1_mf_nnndf_TAG20140129T082504_8zf2bj83_.bkp tag=TAG20140129T082504

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:02:16

Finished restore at 29-JAN-14

RMAN>

[oracle@localhost ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.2.0 Production on Mon Jan 29 08:33:01 2014

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set time on

08:33:08 SQL> recover database;

ORA-00279: change 14271579 generated at 01/29/2014 08:19:51 needed for thread 1

ORA-00289: suggestion :

/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2014_01_29/o1_mf_1_6

30_%u_.arc

ORA-00280: change 14271579 for thread 1 is in sequence #630

08:33:25 Specify log: {=suggested | filename | AUTO | CANCEL}

RMAN> restore archivelog from sequence 630 until sequence 644;

Starting restore at 29-JAN-14

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log restore to default destination

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=630

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=631

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=632

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=633

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=634

channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2014_01_29/o1_mf_annnn_TAG20140129T082500_8zf2bdkc_.bkp

channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2014_01_29/o1_mf_annnn_TAG20140129T082500_8zf2bdkc_.bkp tag=TAG20140129T082500

channel ORA_DISK_1: restored backup piece 1

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

channel ORA_DISK_1: starting archived log restore to default destination

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=635

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=636

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=637

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=638

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=639

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=640

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=641

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=642

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=643

channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2014_01_29/o1_mf_annnn_TAG20140129T082717_8zf2govj_.bkp

channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2014_01_29/o1_mf_annnn_TAG20140129T082717_8zf2govj_.bkp tag=TAG20140129T082717

channel ORA_DISK_1: restored backup piece 1

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

Finished restore at 29-JAN-14

RMAN>

SQL> set time on

08:36:50 SQL> recover database;

ORA-00279: change 14271579 generated at 01/29/2014 08:19:51 needed for thread 1

ORA-00289: suggestion :

/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2014_01_29/o1_mf_1_6

30_8zf2ycv9_.arc

ORA-00280: change 14271579 for thread 1 is in sequence #630

08:36:54 Specify log: {=suggested | filename | AUTO | CANCEL}

AUTO

ORA-00279: change 14271656 generated at 01/29/2014 08:22:06 needed for thread 1

ORA-00289: suggestion :

/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2014_01_29/o1_mf_1_6

31_8zf2ybyw_.arc

ORA-00280: change 14271656 for thread 1 is in sequence #631

ORA-00279: change 14271770 generated at 01/29/2014 08:22:26 needed for thread 1

ORA-00289: suggestion :

/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2014_01_29/o1_mf_1_6

32_8zf2yc12_.arc

ORA-00280: change 14271770 for thread 1 is in sequence #632

ORA-00279: change 14271813 generated at 01/29/2014 08:22:35 needed for thread 1

ORA-00289: suggestion :

/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2014_01_29/o1_mf_1_6

33_8zf2ydhs_.arc

ORA-00280: change 14271813 for thread 1 is in sequence #633

ORA-00279: change 14271822 generated at 01/29/2014 08:22:47 needed for thread 1

ORA-00289: suggestion :

/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2014_01_29/o1_mf_1_6

34_8zf2ydg0_.arc

ORA-00280: change 14271822 for thread 1 is in sequence #634

ORA-00279: change 14271905 generated at 01/29/2014 08:24:59 needed for thread 1

ORA-00289: suggestion :

/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2014_01_29/o1_mf_1_6

35_8zf2yldb_.arc

ORA-00280: change 14271905 for thread 1 is in sequence #635

ORA-00279: change 14273457 generated at 01/29/2014 08:25:58 needed for thread 1

ORA-00289: suggestion :

/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2014_01_29/o1_mf_1_6

36_8zf2yldh_.arc

ORA-00280: change 14273457 for thread 1 is in sequence #636

ORA-00279: change 14275520 generated at 01/29/2014 08:26:14 needed for thread 1

ORA-00289: suggestion :

/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2014_01_29/o1_mf_1_6

37_8zf2yldt_.arc

ORA-00280: change 14275520 for thread 1 is in sequence #637

ORA-00279: change 14276886 generated at 01/29/2014 08:26:28 needed for thread 1

ORA-00289: suggestion :

/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2014_01_29/o1_mf_1_6

38_8zf2yln3_.arc

ORA-00280: change 14276886 for thread 1 is in sequence #638

ORA-00279: change 14278245 generated at 01/29/2014 08:26:40 needed for thread 1

ORA-00289: suggestion :

/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2014_01_29/o1_mf_1_6

39_8zf2ylfp_.arc

ORA-00280: change 14278245 for thread 1 is in sequence #639

ORA-00279: change 14279601 generated at 01/29/2014 08:26:44 needed for thread 1

ORA-00289: suggestion :

/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2014_01_29/o1_mf_1_6

40_8zf2ylgk_.arc

ORA-00280: change 14279601 for thread 1 is in sequence #640

ORA-00279: change 14281402 generated at 01/29/2014 08:26:50 needed for thread 1

ORA-00289: suggestion :

/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2014_01_29/o1_mf_1_6

41_8zf2ylok_.arc

ORA-00280: change 14281402 for thread 1 is in sequence #641

Log applied.

Media recovery complete.

08:37:19 SQL>

Media Recovery Log /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2014_01_29/o1_mf_1_641_8zf2ylok_.arc

Recovery of Online Redo Log: Thread 1 Group 3 Seq 642 Reading mem 0

  Mem# 0: /home/oracle/app/oracle/oradata/orcl/redo03.log

Recovery of Online Redo Log: Thread 1 Group 1 Seq 643 Reading mem 0

  Mem# 0: /home/oracle/app/oracle/oradata/orcl/redo01.log

Recovery of Online Redo Log: Thread 1 Group 2 Seq 644 Reading mem 0

  Mem# 0: /home/oracle/app/oracle/oradata/orcl/redo02.log

Mon Jan 29 08:37:18 2014

Media Recovery Complete (orcl)

Completed: ALTER DATABASE RECOVER    CONTINUE DEFAULT 

08:38:07 SQL> alter database open;

Database altered.

08:38:23 SQL>

Mon Jan 29 08:38:12 2014

alter database open

Beginning crash recovery of 1 threads

Started redo scan

Completed redo scan

 read 0 KB redo, 0 data blocks need recovery

Started redo application at

 Thread 1: logseq 644, block 292, scn 14283635

Recovery of Online Redo Log: Thread 1 Group 2 Seq 644 Reading mem 0

  Mem# 0: /home/oracle/app/oracle/oradata/orcl/redo02.log

Completed redo application of 0.00MB

Completed crash recovery at

 Thread 1: logseq 644, block 292, scn 14303636

 0 data blocks read, 0 data blocks written, 0 redo k-bytes read

Mon Jan 29 08:38:13 2014

LGWR: STARTING ARCH PROCESSES

Mon Jan 29 08:38:13 2014

ARC0 started with pid=30, OS id=3541

ARC0: Archival started

LGWR: STARTING ARCH PROCESSES COMPLETE

ARC0: STARTING ARCH PROCESSES

Mon Jan 29 08:38:14 2014

ARC1 started with pid=29, OS id=3543

Mon Jan 29 08:38:14 2014

ARC2 started with pid=31, OS id=3545

Thread 1 advanced to log sequence 645 (thread open)

ARC1: Archival started

ARC2: Archival started

Mon Jan 29 08:38:14 2014

ARC3 started with pid=32, OS id=3547

ARC1: Becoming the 'no FAL' ARCH

ARC1: Becoming the 'no SRL' ARCH

Thread 1 opened at log sequence 645

  Current log# 3 seq# 645 mem# 0: /home/oracle/app/oracle/oradata/orcl/redo03.log

Successful open of redo thread 1

MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set

ARC2: Becoming the heartbeat ARCH

Mon Jan 29 08:38:14 2014

SMON: enabling cache recovery

Deleted Oracle managed file /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2014_06_16/o1_mf_ncnnf_TAG20140616T082716_8vvpbo4l_.bkp

ARC3: Archival started

ARC0: STARTING ARCH PROCESSES COMPLETE

Archived Log entry 43 added for thread 1 sequence 644 ID 0x49469b3f dest 1:

[3530] Successfully onlined Undo Tablespace 2.

Undo initialization finished serial:0 start:298264 end:299004 diff:740 (7 seconds)

Verifying file header compatibility for 11g tablespace encryption..

Verifying 11g file header compatibility for tablespace encryption completed

SMON: enabling tx recovery

Re-creating tempfile /home/oracle/app/oracle/oradata/orcl/temp01.dbf

Database Characterset is AL32UTF8

No Resource Manager plan active

replication_dependency_tracking turned off (no async multimaster replication found)

Starting background process QMNC

Mon Jan 29 08:38:21 2014

QMNC started with pid=33, OS id=3549

Mon Jan 29 08:38:23 2014

Completed: alter database open

Mon Jan 29 08:38:26 2014

Starting background process CJQ0

Mon Jan 29 08:38:26 2014

CJQ0 started with pid=41, OS id=3575

Mon Jan 29 08:38:28 2014

db_recovery_file_dest_size of 3852 MB is 69.95% used. This is a

user-specified limit on the amount of space that will be used by this

database for recovery-related files, and does not reflect the amount of

space available in the underlying filesystem or ASM diskgroup.

The RMAN BACKUP began when the oldest online log  sequence was 632.  Yet the RECOVER Database after the RESTORE required sequence 630 -- which had been archived before the RMAN BACKUP began.  Technically, sequence 630 would not have been required for the RECOVER.  Yet, it was required because the database was in BEGIN BACKUP mode when the RMAN Backup was made.

Beware of  leaving a database or tablespace in BEGIN BACKUP mode beyond the end of the OS-scripted backup -- whether the backup ran successfully or not, the database should have been taken out of BACKUP mode.