29 July, 2013

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 Jul 29 08:23:47 PDT 2013
[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Mon Jul 29 08:23:59 2013

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-JUL-13
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-JUL-13
channel ORA_DISK_1: finished piece 1 at 29-JUL-13
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_07_29/o1_mf_annnn_TAG20130729T082500_8zf2bdkc_.bkp tag=TAG20130729T082500 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/2013_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/2013_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/2013_07_13/o1_mf_1_623_8y1z6obz_.arc RECID=8 STAMP=820627686
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_13/o1_mf_1_624_8y2wpst6_.arc RECID=9 STAMP=820657899
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_16/o1_mf_1_625_8ybrhcgp_.arc RECID=10 STAMP=820915708
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_27/o1_mf_1_626_8z7oz70c_.arc RECID=11 STAMP=821863415
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_28/o1_mf_1_627_8z9wombb_.arc RECID=12 STAMP=821935812
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_28/o1_mf_1_628_8zbdll9r_.arc RECID=13 STAMP=821952099
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_29/o1_mf_1_629_8zf1yfv9_.arc RECID=14 STAMP=822039518
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_29/o1_mf_1_630_8zf24yk0_.arc RECID=15 STAMP=822039726
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_29/o1_mf_1_631_8zf25lt4_.arc RECID=16 STAMP=822039751
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_29/o1_mf_1_632_8zf25vw1_.arc RECID=17 STAMP=822039756
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_29/o1_mf_1_633_8zf2675w_.arc RECID=18 STAMP=822039767
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_29/o1_mf_1_634_8zf2bcd9_.arc RECID=19 STAMP=822039899
Finished backup at 29-JUL-13

Starting backup at 29-JUL-13
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-JUL-13
channel ORA_DISK_1: finished piece 1 at 29-JUL-13
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_07_29/o1_mf_nnndf_TAG20130729T082504_8zf2bj83_.bkp tag=TAG20130729T082504 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-JUL-13
channel ORA_DISK_1: finished piece 1 at 29-JUL-13
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_07_29/o1_mf_ncsnf_TAG20130729T082504_8zf2gk3r_.bkp tag=TAG20130729T082504 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 29-JUL-13

Starting backup at 29-JUL-13
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-JUL-13
channel ORA_DISK_1: finished piece 1 at 29-JUL-13
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_07_29/o1_mf_annnn_TAG20130729T082717_8zf2govj_.bkp tag=TAG20130729T082717 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/2013_07_29/o1_mf_1_635_8zf2d7mb_.arc RECID=20 STAMP=822039962
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_29/o1_mf_1_636_8zf2drd5_.arc RECID=21 STAMP=822039979
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_29/o1_mf_1_637_8zf2f4p3_.arc RECID=22 STAMP=822039996
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_29/o1_mf_1_638_8zf2fjqo_.arc RECID=23 STAMP=822040002
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_29/o1_mf_1_639_8zf2fnqo_.arc RECID=24 STAMP=822040006
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_29/o1_mf_1_640_8zf2fybs_.arc RECID=25 STAMP=822040015
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_29/o1_mf_1_641_8zf2g6hn_.arc RECID=26 STAMP=822040023
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_29/o1_mf_1_642_8zf2gdlj_.arc RECID=27 STAMP=822040028
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_29/o1_mf_1_643_8zf2gom0_.arc RECID=28 STAMP=822040037
Finished backup at 29-JUL-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 Jul 29 08:29:45 2013

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-JUL-13
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/2013_07_29/o1_mf_nnndf_TAG20130729T082504_8zf2bj83_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_07_29/o1_mf_nnndf_TAG20130729T082504_8zf2bj83_.bkp tag=TAG20130729T082504
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:16
Finished restore at 29-JUL-13

RMAN> 

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

SQL*Plus: Release 11.2.0.2.0 Production on Mon Jul 29 08:33:01 2013

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 07/29/2013 08:19:51 needed for thread 1
ORA-00289: suggestion :
/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_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-JUL-13
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/2013_07_29/o1_mf_annnn_TAG20130729T082500_8zf2bdkc_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_07_29/o1_mf_annnn_TAG20130729T082500_8zf2bdkc_.bkp tag=TAG20130729T082500
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/2013_07_29/o1_mf_annnn_TAG20130729T082717_8zf2govj_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_07_29/o1_mf_annnn_TAG20130729T082717_8zf2govj_.bkp tag=TAG20130729T082717
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 29-JUL-13

RMAN> 



SQL> set time on
08:36:50 SQL> recover database;
ORA-00279: change 14271579 generated at 07/29/2013 08:19:51 needed for thread 1
ORA-00289: suggestion :
/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_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 07/29/2013 08:22:06 needed for thread 1
ORA-00289: suggestion :
/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_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 07/29/2013 08:22:26 needed for thread 1
ORA-00289: suggestion :
/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_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 07/29/2013 08:22:35 needed for thread 1
ORA-00289: suggestion :
/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_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 07/29/2013 08:22:47 needed for thread 1
ORA-00289: suggestion :
/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_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 07/29/2013 08:24:59 needed for thread 1
ORA-00289: suggestion :
/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_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 07/29/2013 08:25:58 needed for thread 1
ORA-00289: suggestion :
/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_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 07/29/2013 08:26:14 needed for thread 1
ORA-00289: suggestion :
/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_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 07/29/2013 08:26:28 needed for thread 1
ORA-00289: suggestion :
/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_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 07/29/2013 08:26:40 needed for thread 1
ORA-00289: suggestion :
/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_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 07/29/2013 08:26:44 needed for thread 1
ORA-00289: suggestion :
/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_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 07/29/2013 08:26:50 needed for thread 1
ORA-00289: suggestion :
/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_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/2013_07_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 Jul 29 08:37:18 2013
Media Recovery Complete (orcl)
Completed: ALTER DATABASE RECOVER    CONTINUE DEFAULT  


08:38:07 SQL> alter database open;

Database altered.

08:38:23 SQL> 



Mon Jul 29 08:38:12 2013
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 Jul 29 08:38:13 2013
LGWR: STARTING ARCH PROCESSES
Mon Jul 29 08:38:13 2013
ARC0 started with pid=30, OS id=3541 
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Mon Jul 29 08:38:14 2013
ARC1 started with pid=29, OS id=3543 
Mon Jul 29 08:38:14 2013
ARC2 started with pid=31, OS id=3545 
Thread 1 advanced to log sequence 645 (thread open)
ARC1: Archival started
ARC2: Archival started
Mon Jul 29 08:38:14 2013
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 Jul 29 08:38:14 2013
SMON: enabling cache recovery
Deleted Oracle managed file /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_06_16/o1_mf_ncnnf_TAG20130616T082716_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 Jul 29 08:38:21 2013
QMNC started with pid=33, OS id=3549 
Mon Jul 29 08:38:23 2013
Completed: alter database open
Mon Jul 29 08:38:26 2013
Starting background process CJQ0
Mon Jul 29 08:38:26 2013
CJQ0 started with pid=41, OS id=3575 
Mon Jul 29 08:38:28 2013
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.

.
.
.

2 comments:

Anonymous said...

Nice test scenario Hemant !!

Marko Sutic said...

Excellent blog post Hemant!

I haven't performed many tests with BEGIN/END BACKUP modes so this is very valuable information for me.

I would assume that the oldest sequence required for recover would be 632 when full backup was taken not 630.

Keep on good work ;)

Regards,
Marko