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.

.
.
.

11 July, 2013

Interesting Bugs in 12cR1

Oracle has documented known bugs in 12.1

Some that I find interesting (without having tested them, but only relying on the documentation) are :

Bug 16933305
Adding a nullable column with a default value and then later unsetting the default value in an Oracle Database 12c Release 1 (12.1) environment will not unset the default (back to NULL) despite the data dictionary reflecting the change to a default value of NULL. The statements that are impacted are ALTER TABLE x ADD (y NUMBER DEFAULT 99) followed by a subsequent ALTER TABLE x MODIFY (y DEFAULT NULL) where y is a nullable column.
Workaround: Unset the default by using the ALTER TABLE x MODIFY (y DEFAULT TRIM('')) statement which has the same semantic result as unsetting the default.


Bug 16873173
It is possible for an index fast full scan of a prefix compressed index to fail with ORA-600[6033] error.
Workaround: Retry the query or force an alternative access path with an appropriate hint.


Bug 16788520 and Bug 15968058
If shared server is enabled, queries on object link views in a PDB may crash. Object link views are all Oracle supplied and are mostly DBA_HIST views. A full list can be found using the following command:
SELECT OWNER, OBJECT_NAME FROM ALL_OBJECTS WHERE SHARING='OBJECT LINK' AND OBJECT_TYPE='VIEW'
Workaround: Disable shared server when querying these views.


Bug 16571451
SQL plan directives are not used when the OPTIMIZER_DYNAMIC_SAMPLING initialization parameter is set to the default value of2.
Workaround: Set the OPTIMIZER_DYNAMIC_SAMPLING initialization parameter to a value greater than 2.


Bug 16047938
Concurrent UNION ALL is automatically invoked for qualifying statements only when the UNION ALL statement is in a subselect statement. For example, the following command will execute all branches concurrently:
SELECT * FROM (SELECT FROM ... UNION ALL ... UNION ALL)
However, the exact same UNION ALL statement not executed as a subselect statement will not.
Workaround: Either embed the UNION ALL construct as a subselect statement or use the following statement to disable legacy code constraints:
ALTER SESSION SET "_fix_control"='6748058:0';


Bug 14851837
Tables containing TIMESTAMP WITH LOCAL TIME ZONE data cannot be moved between databases using transportable tablespace technology when the time zones of the source and target databases are different. Each affected table will be flagged during import with the following error:
ORA-39360, Table ""."" skipped due to transportable import and TSLTZ issues. 
Workaround: Convert the target database to the same time zone as the source database or move the affected tables with the conventional Data Pump Export and Import.
Bug 14621745
Certain errors raised while using the ALTER TABLE MOVE ONLINE statement can be ignored.
Workaround: None.
Bug 13877504
When threaded execution is enabled (threaded_execution=TRUE) in the initialization parameter file on UNIX platforms, native authentication (that is, connect / as sysdba or connect /) is not supported.
Workaround: Use password-based authentication. Native authentication will not work for connections where the server execution is a thread.
Bug 14575666
In 12.1, the default value for the SQLNET.ALLOWED_LOGON_VERSION parameter has been updated to 11. This means that database clients using pre-11g JDBC thin drivers cannot authenticate to 12.1 database servers unless theSQLNET.ALLOWED_LOGON_VERSION parameter is set to the old default of 8.
This will cause a 10.2.0.5 Oracle RAC database creation using DBCA to fail with the ORA-28040: No matching authentication protocol error in 12.1 Oracle ASM and Oracle Grid Infrastructure environments.
Workaround:  Set SQLNET.ALLOWED_LOGON_VERSION=8 in the $crs_home/network/admin/sqlnet.ora file.
Use the workaround before running 10.2.0.5 DBCA to create a database using 12.1 Oracle ASM and Oracle Grid Infrastructure.
Bug 16177073
Mixed case hostnames are not supported by the Oracle Universal Installer (OUI) for Oracle RAC or Cluster Ready Services (CRS) homes.
Workaround:  Do not use mixed case host names.
Bug 16903140
A new feature was added in Oracle Database 12c Release 1 (12.1) to support change tracking across resetlogs during the execution of an ALTER DATABASE OPEN RESETLOGS statement. This is achieved by deleting bitmaps that are not required because they cover changes that were made after the specified resetlogs point-in-time. There is an issue with the bitmap deleting that can cause error ORA-600 [krccchs_1] during OPEN RESETLOGS.
Workaround: Disable and reenable change tracking. You must disable change tracking before executing the ALTER DATABASE OPEN RESETLOGS statement, and then re-enable change tracking after the open resetlogs operation is complete.
.
.
.



07 July, 2013

Concepts / Features overturned in 12c

Here are some of our knowledge of concepts / features of Oracle till 11g that seem to be overturned in 12c :

1.       Redo Log files are part of the database.   Now, in 12c, they are not part of the Pluggable Database.

2.      A database can have multiple Instances (RAC) but an Instance can access only one Database.  Now we have multiple PDBs in an Instance.

3.      Datafiles cannot be moved without taking them (or their Tablespace) offline.  Now, 12c allows an online Move.

4.      Oracle does not have Auto-Increment attached to a table but you need to use a Sequence.  Now you can have Auto-Increment in a table.

5.      DDL on a Partition with UPDATE GLOBAL INDEXES required a Rebuild of the Global Indexes.  Now, updates to the Global Indexes are deferred.

6.      The same Column or set of Columns cannot have more than 1 Index. Now you can have additional Indexes but only one is visible.

7.      You have to use SYSDBA to run RMAN Backups.  12c now allows a SYSBACKUP privilege.

8.     RMAN is a physical backup. It is not aware of the logical concept of a table.  It cannot restore and recover a table.  Now it can (but it uses an auxiliary instance  an actual database to extract the table from).

9.     Once Oracle starts executing an SQL it cannot change the execution plan midway (only the next execution can use updated statistics or cardinality feedback).  Now, Oracle can change the plan and restart resume execution midway.

Send in your suggestions for the next 3 to make a dozen.

Three additional ones from Jonathan Lewis :

10.    Dynamic Sampling statistics are for the execution of that SQL only.  Now, the optimizer can "save" dynamically sampled statistics.

11.    GTT Statistics are the same for all sessions.  Now, global temporary tables can have session-based statistics (see dbms_stats.set_table_prefs).

12.    Tables cannot be moved online.  Now you can move tables online, provided it's partitioned and you do one partition at a time.

.
.
.

06 July, 2013

12c RMAN Restrictions -- when connected to a PDB

From the 12c documentation, it is clear that a Pluggable Database does not have (a) it's own instance (b) it's own redo and archivelogs.
Also, there are a number of restrictions in RMAN when you configure a Multi-Tenanted Database and run RMAN against one of the PDBs.

Here are the documented restrictions :
Restrictions When Connected to a PDB
The following operations are not available when you connect as target directly to a PDB:
·       Back up archived logs
·       Delete archived logs
·       Delete archived log backups
·       Restore archived logs (RMAN does restore archived logs when required during media recovery.)
·       Point-in-time recovery (PITR)
·       TSPITR
·       Table recovery
·       Duplicate database
·       Flashback operations
·       Running Data Recovery Advisor
·       Report/delete obsolete
·       Register database
·       Import catalog
·       Reset database
·       Configuring the RMAN environment (using the CONFIGURE command)
Note:
When you connect as TARGET to a PDB, you cannot connect to a recovery catalog.


What does this mean  ?  For most Backup scenarios, you would run Backups from the *CONTAINER* Database.  You can connect to the Container Database and Backup an individual PDB.
.
.