A review of the SQL that Lisbeth Salander [in the movie "The Girl With The Dragon Tattoo"] writes.
.
.
.
.
.
.
I am an Oracle Database Specialist in Singapore.
Please note that this site uses cookies.
30 May, 2012
20 May, 2012
CHECKPOINT_CHANGE#
In my previous post CURRENT_SCN and CHECKPOINT_CHANGE#, I had asked : "In which scenarios could the checkpoint_change# value differ between V$DATAFILE and V$DATAFILE_HEADER ?"
Here's a little demo :
Here's a little demo :
SQL> create tablespace NEWTBS datafile '/tmp/newtbs.dbf' size 50M;
Tablespace created.
SQL> create table hemant.objcopy tablespace newtbs as select * from dba_objects;
Table created.
SQL> select file_id from dba_data_files where tablespace_name = 'NEWTBS';
FILE_ID
----------
15
SQL> alter system checkpoint;
System altered.
SQL> select f.checkpoint_change#, h.checkpoint_change#
2 from v$datafile f, v$datafile_header h
3 where f.file#=15 and h.file#=15 and f.file#=h.file#;
CHECKPOINT_CHANGE# CHECKPOINT_CHANGE#
------------------ ------------------
5312187 5312187
SQL>
RMAN> backup tablespace newtbs; Starting backup at 20-MAY-12 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=13 device type=DISK channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00015 name=/tmp/newtbs.dbf channel ORA_DISK_1: starting piece 1 at 20-MAY-12 channel ORA_DISK_1: finished piece 1 at 20-MAY-12 piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_05_20/o1_mf_nnndf_TAG20120520T230907_7vl28n6q_.bkp tag=TAG20120520T230907 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 20-MAY-12 Starting Control File Autobackup at 20-MAY-12 piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_05_20/o1_mf_n_783817749_7vl28oln_.bkp comment=NONE Finished Control File Autobackup at 20-MAY-12 RMAN>Next, I update the object(s) in the tablespace.
SQL> connect hemant/hemant Connected. SQL> select segment_name from user_segments where tablespace_name = 'NEWTBS'; SEGMENT_NAME -------------------------------------------------------------------------------- OBJCOPY SQL> insert into objcopy select * from dba_objects; 76670 rows created. SQL> update objcopy set owner = owner || '_1'; 153301 rows updated. SQL> commit; Commit complete. SQL> SQL> alter system switch logfile; System altered. SQL>I then remove and restore the datafile :
SQL> connect / as sysdba Connected. SQL> !rm /tmp/newtbs*.dbf SQL> alter database datafile 15 offline; Database altered. SQL> RMAN> restore datafile 15; Starting restore at 20-MAY-12 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=7 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 00015 to /tmp/newtbs.dbf channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_05_20/o1_mf_nnndf_TAG20120520T230907_7vl28n6q_.bkp channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_05_20/o1_mf_nnndf_TAG20120520T230907_7vl28n6q_.bkp tag=TAG20120520T230907 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:03 Finished restore at 20-MAY-12 RMAN>If I now query the V$DATAFILE and V$DATAFILE_HEADER views, I see :
SQL> select f.checkpoint_change#, h.checkpoint_change#
2 from v$datafile f, v$datafile_header h
3 where f.file#=15 and h.file#=15 and f.file#=h.file#
4 /
CHECKPOINT_CHANGE# CHECKPOINT_CHANGE#
------------------ ------------------
5315901 5313637
SQL>
I then RECOVER the datafile :
SQL> recover datafile 15;
ORA-00279: change 5313637 generated at 05/20/2012 23:09:08 needed for thread 1
ORA-00289: suggestion :
/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_05_20/o1_mf_1_1
3_7vl2f3nf_.arc
ORA-00280: change 5313637 for thread 1 is in sequence #13
Specify log: {=suggested | filename | AUTO | CANCEL}
Log applied.
Media recovery complete.
SQL>
SQL> select f.checkpoint_change#, h.checkpoint_change#
2 from v$datafile f, v$datafile_header h
3 where f.file#=15 and h.file#=15 and f.file#=h.file#
4 /
CHECKPOINT_CHANGE# CHECKPOINT_CHANGE#
------------------ ------------------
5321980 5321980
SQL>
Note : The CHECKPOINT_CHANGE# has been incremented and both the views now show the same value.
Bringing the datafile online again increments the CHECKPOINT_CHANGE#.SQL> select f.checkpoint_change#, h.checkpoint_change#
2 from v$datafile f, v$datafile_header h
3 where f.file#=15 and h.file#=15 and f.file#=h.file#
4 /
CHECKPOINT_CHANGE# CHECKPOINT_CHANGE#
------------------ ------------------
5322278 5322278
SQL>
I could ask the question : Why is the CHECKPOINT_CHANGE# incremented for a datafile that was OFFLINE and [merely] RECOVERed ? But I am sure that you know the answer now..
.
.
15 May, 2012
CURRENT_SCN and CHECKPOINT_CHANGE#
The V$DATABASE view provides the current SCN at the database level.
The V$DATAFILE view provides the SCN at which the last checkpoint was issued for each datafile.
For example :
The V$DATAFILE view provides the SCN at which the last checkpoint was issued for each datafile.
For example :
SQL> select current_scn from v$database;
CURRENT_SCN
---------------
5288289
SQL> select checkpoint_change#, count(*) from v$datafile group by checkpoint_change#;
CHECKPOINT_CHANGE# COUNT(*)
------------------ ---------------
5287941 14
SQL> select checkpoint_change#, count(*) from v$datafile_header group by checkpoint_change#;
CHECKPOINT_CHANGE# COUNT(*)
------------------ ---------------
5287941 14
SQL>
In this case, V$DATABASE's current_scn is ahead of those of the datafiles last checkpoint. We know that this is acceptable -- because the database SCN is continuously being incremented while datafiles are checkpointed only on occasion. The above values are after a database instance startup.What if I checkpoint selective datafiles ?
SQL> alter system checkpoint;
System altered.
SQL> select checkpoint_change#, count(*) from v$datafile group by checkpoint_change#;
CHECKPOINT_CHANGE# COUNT(*)
------------------ ---------------
5288402 14
SQL> select checkpoint_change#, count(*) from v$datafile_header group by checkpoint_change#;
CHECKPOINT_CHANGE# COUNT(*)
------------------ ---------------
5288402 14
SQL> alter tablespace users offline;
Tablespace altered.
SQL> alter tablespace users online;
Tablespace altered.
SQL> select checkpoint_change#, count(*) from v$datafile group by checkpoint_change#;
CHECKPOINT_CHANGE# COUNT(*)
------------------ ---------------
5288402 13
5288445 1
SQL> select checkpoint_change#, count(*) from v$datafile_header group by checkpoint_change#;
CHECKPOINT_CHANGE# COUNT(*)
------------------ ---------------
5288402 13
5288445 1
SQL>
Here I have caused the datafile for the USERS tablespace to be at a higher checkpoint scn than the other datafiles.
Question : In which scenarios could the checkpoint_change# value differ between V$DATAFILE and V$DATAFILE_HEADER ?
.
.
.
11 May, 2012
Index Block Splits
Here are some blog posts about Index Block Splits that I had done earlier :
Index Block Splits : 90-10 (17-May-09)
Index Block Splits : 50-50 (23-May-09)
Index Block Splits and REBUILD (26-May-09)
Index Block Splits with REVERSE KEY Index (20-Feb-11)
(Good to remind myself about these posts)
.
.
.
08 May, 2012
RMAN Tips -- 4
Here I have backups that go backup more then 4 months :
RMAN> list backup summary; List of Backups =============== Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag ------- -- -- - ----------- --------------- ------- ------- ---------- --- 1 B A A DISK 01-JAN-12 1 1 YES TAG20120101T110005 2 B A A DISK 01-JAN-12 1 1 YES TAG20120101T110005 3 B F A DISK 01-JAN-12 1 1 YES TAG20120101T110008 4 B F A DISK 01-JAN-12 1 1 YES TAG20120101T110008 5 B A A DISK 01-JAN-12 1 1 YES TAG20120101T110205 6 B A A DISK 01-JAN-12 1 1 YES TAG20120101T112514 7 B A A DISK 01-JAN-12 1 1 YES TAG20120101T112514 8 B F A DISK 01-JAN-12 1 1 YES TAG20120101T112516 9 B F A DISK 01-JAN-12 1 1 YES TAG20120101T112516 10 B F A DISK 01-JAN-12 1 1 YES TAG20120101T112516 11 B F A DISK 01-JAN-12 1 1 YES TAG20120101T112516 12 B A A DISK 01-JAN-12 1 1 YES TAG20120101T112655 13 B F A DISK 01-JAN-12 1 1 NO TAG20120101T115229 14 B F A DISK 13-FEB-12 1 1 NO TAG20120213T234617 15 B A A DISK 23-MAR-12 1 1 YES TAG20120323T231436 16 B A A DISK 23-MAR-12 1 1 YES TAG20120323T231436 17 B A A DISK 23-MAR-12 1 1 YES TAG20120323T231436 18 B F A DISK 23-MAR-12 1 1 YES TAG20120323T231454 19 B A A DISK 23-MAR-12 1 1 YES TAG20120323T231639 20 B F A DISK 23-MAR-12 1 1 NO TAG20120323T231640 21 B F A DISK 23-MAR-12 1 1 YES TAG20120323T235528 22 B F A DISK 23-MAR-12 1 1 NO TAG20120323T235653 23 B F A DISK 24-MAR-12 1 1 NO TAG20120324T001027 24 B F A DISK 24-MAR-12 1 1 NO TAG20120324T001527 25 B F A DISK 24-MAR-12 1 1 NO TAG20120324T001528 RMAN>And the backups are located in the FRA (what can you surmise about the 13-FEB backup that doesn't exist here ?)
[oracle@linux64 backupset]$ pwd /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset [oracle@linux64 backupset]$ ls -l total 12 drwxrwx--- 2 oracle oracle 4096 Jan 1 11:26 2012_01_01 drwxrwx--- 2 oracle oracle 4096 Mar 23 23:55 2012_03_23 drwxrwx--- 2 oracle oracle 4096 Mar 24 00:15 2012_03_24 [oracle@linux64 backupset]$ [oracle@linux64 backupset]$ du -sh * 1.1G 2012_01_01 1.1G 2012_03_23 1.3M 2012_03_24 [oracle@linux64 backupset]$What if one of the backups was relocated to another mountpoint ?
[oracle@linux64 backupset]$ mv 2012_01_01 /newmountpoint/FRA [oracle@linux64 backupset]$ du -sh /newmountpoint/FRA/* 1.1G /newmountpoint/FRA/2012_01_01 [oracle@linux64 backupset]$ RMAN> crosscheck backup; allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=195 device type=DISK crosschecked backup piece: found to be 'EXPIRED' backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_annnn_TAG20120101T110005_7hzm1p3b_.bkp RECID=1 STAMP=771418806 crosschecked backup piece: found to be 'EXPIRED' backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_annnn_TAG20120101T110005_7hzm1q6l_.bkp RECID=2 STAMP=771418807 crosschecked backup piece: found to be 'EXPIRED' backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T110008_7hzm1rj9_.bkp RECID=3 STAMP=771418808 crosschecked backup piece: found to be 'EXPIRED' backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_ncnnf_TAG20120101T110008_7hzm5dob_.bkp RECID=4 STAMP=771419318 crosschecked backup piece: found to be 'EXPIRED' backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_annnn_TAG20120101T110205_7hzm5g23_.bkp RECID=5 STAMP=771419318 crosschecked backup piece: found to be 'EXPIRED' backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_annnn_TAG20120101T112514_7hznjtnr_.bkp RECID=6 STAMP=771420314 crosschecked backup piece: found to be 'EXPIRED' backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_annnn_TAG20120101T112514_7hznjvr1_.bkp RECID=7 STAMP=771420315 crosschecked backup piece: found to be 'EXPIRED' backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznjx24_.bkp RECID=8 STAMP=771420317 crosschecked backup piece: found to be 'EXPIRED' backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznm84k_.bkp RECID=9 STAMP=771420392 crosschecked backup piece: found to be 'EXPIRED' backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznmq8q_.bkp RECID=10 STAMP=771420407 crosschecked backup piece: found to be 'EXPIRED' backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznmydw_.bkp RECID=11 STAMP=771420414 crosschecked backup piece: found to be 'EXPIRED' backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_annnn_TAG20120101T112655_7hznmzlq_.bkp RECID=12 STAMP=771420415 ..... some lines deleted .... crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_03_23/o1_mf_annnn_TAG20120323T231436_7ps4tww3_.bkp RECID=15 STAMP=778720476 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_03_23/o1_mf_annnn_TAG20120323T231436_7ps4txz4_.bkp RECID=16 STAMP=778720477 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_03_23/o1_mf_annnn_TAG20120323T231436_7ps4vf2f_.bkp RECID=17 STAMP=778720493 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_03_23/o1_mf_nnndf_TAG20120323T231454_7ps4vgmf_.bkp RECID=18 STAMP=778720494 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_03_23/o1_mf_annnn_TAG20120323T231639_7ps4yqqm_.bkp RECID=19 STAMP=778720599 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_03_23/o1_mf_n_778720600_7ps4ys8j_.bkp RECID=20 STAMP=778720601 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_03_23/o1_mf_nnndf_TAG20120323T235528_7ps77jf2_.bkp RECID=21 STAMP=778722928 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_03_23/o1_mf_n_778723013_7ps7b5l1_.bkp RECID=22 STAMP=778723013 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_03_24/o1_mf_n_778723827_7ps83mpw_.bkp RECID=23 STAMP=778723827 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_03_24/o1_mf_nnndf_TAG20120324T001527_7ps8dzm8_.bkp RECID=24 STAMP=778724127 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_03_24/o1_mf_n_778724128_7ps8f0o8_.bkp RECID=25 STAMP=778724128 Crosschecked 25 objects RMAN> RMAN> delete expired backup; using channel ORA_DISK_1 List of Backup Pieces BP Key BS Key Pc# Cp# Status Device Type Piece Name ------- ------- --- --- ----------- ----------- ---------- 1 1 1 1 EXPIRED DISK /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_annnn_TAG20120101T110005_7hzm1p3b_.bkp 2 2 1 1 EXPIRED DISK /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_annnn_TAG20120101T110005_7hzm1q6l_.bkp 3 3 1 1 EXPIRED DISK /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T110008_7hzm1rj9_.bkp 4 4 1 1 EXPIRED DISK /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_ncnnf_TAG20120101T110008_7hzm5dob_.bkp 5 5 1 1 EXPIRED DISK /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_annnn_TAG20120101T110205_7hzm5g23_.bkp 6 6 1 1 EXPIRED DISK /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_annnn_TAG20120101T112514_7hznjtnr_.bkp 7 7 1 1 EXPIRED DISK /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_annnn_TAG20120101T112514_7hznjvr1_.bkp 8 8 1 1 EXPIRED DISK /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznjx24_.bkp 9 9 1 1 EXPIRED DISK /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznm84k_.bkp 10 10 1 1 EXPIRED DISK /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznmq8q_.bkp 11 11 1 1 EXPIRED DISK /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznmydw_.bkp 12 12 1 1 EXPIRED DISK /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_annnn_TAG20120101T112655_7hznmzlq_.bkp Do you really want to delete the above objects (enter YES or NO)? YES deleted backup piece backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_annnn_TAG20120101T110005_7hzm1p3b_.bkp RECID=1 STAMP=771418806 deleted backup piece backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_annnn_TAG20120101T110005_7hzm1q6l_.bkp RECID=2 STAMP=771418807 deleted backup piece backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T110008_7hzm1rj9_.bkp RECID=3 STAMP=771418808 deleted backup piece backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_ncnnf_TAG20120101T110008_7hzm5dob_.bkp RECID=4 STAMP=771419318 deleted backup piece backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_annnn_TAG20120101T110205_7hzm5g23_.bkp RECID=5 STAMP=771419318 deleted backup piece backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_annnn_TAG20120101T112514_7hznjtnr_.bkp RECID=6 STAMP=771420314 deleted backup piece backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_annnn_TAG20120101T112514_7hznjvr1_.bkp RECID=7 STAMP=771420315 deleted backup piece backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznjx24_.bkp RECID=8 STAMP=771420317 deleted backup piece backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznm84k_.bkp RECID=9 STAMP=771420392 deleted backup piece backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznmq8q_.bkp RECID=10 STAMP=771420407 deleted backup piece backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznmydw_.bkp RECID=11 STAMP=771420414 deleted backup piece backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_annnn_TAG20120101T112655_7hznmzlq_.bkp RECID=12 STAMP=771420415 Deleted 12 EXPIRED objects RMAN> RMAN> list backup summary; List of Backups =============== Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag ------- -- -- - ----------- --------------- ------- ------- ---------- --- 13 B F A DISK 01-JAN-12 1 1 NO TAG20120101T115229 14 B F A DISK 13-FEB-12 1 1 NO TAG20120213T234617 15 B A A DISK 23-MAR-12 1 1 YES TAG20120323T231436 16 B A A DISK 23-MAR-12 1 1 YES TAG20120323T231436 17 B A A DISK 23-MAR-12 1 1 YES TAG20120323T231436 18 B F A DISK 23-MAR-12 1 1 YES TAG20120323T231454 19 B A A DISK 23-MAR-12 1 1 YES TAG20120323T231639 20 B F A DISK 23-MAR-12 1 1 NO TAG20120323T231640 21 B F A DISK 23-MAR-12 1 1 YES TAG20120323T235528 22 B F A DISK 23-MAR-12 1 1 NO TAG20120323T235653 23 B F A DISK 24-MAR-12 1 1 NO TAG20120324T001027 24 B F A DISK 24-MAR-12 1 1 NO TAG20120324T001527 25 B F A DISK 24-MAR-12 1 1 NO TAG20120324T001528 RMAN>Apparently, Oracle is no longer "aware" of the 01-Jan-12 backup.
This is the problem statement : What if I were restoring an old backup (say 01-Jan) to another server but I did NOT have the controlfile that was part of the backup ? If I can use my media manager to restore the backup pieces from tape to disk (e.g. to the /newmountpoint/FRA filesystem), how can I restore the database without the controlfile ?
This is the "solution" :
(a) I restore a backup of the current controlfile (or any recent backup of the controlfile) to the new server.
(b) I then CATALOG all the backuppieces of 01-Jan that I have restored to disk.
(c) I can then RESTORE DATABASE --- even though the controlfile I am using is as of 08-May-12 and the backuppieces are from 01-Jan-12.
(d) I can RECOVER DATABASE to any point from 01-Jan to 08-May as long as I have (or can restore) the Archivelogs.
RMAN> catalog start with '/newmountpoint/FRA'; searching for all files that match the pattern /newmountpoint/FRA List of Files Unknown to the Database ===================================== File Name: /newmountpoint/FRA/2012_01_01/o1_mf_annnn_TAG20120101T110005_7hzm1q6l_.bkp File Name: /newmountpoint/FRA/2012_01_01/o1_mf_annnn_TAG20120101T110205_7hzm5g23_.bkp File Name: /newmountpoint/FRA/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznm84k_.bkp File Name: /newmountpoint/FRA/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznjx24_.bkp File Name: /newmountpoint/FRA/2012_01_01/o1_mf_annnn_TAG20120101T112514_7hznjtnr_.bkp File Name: /newmountpoint/FRA/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznmydw_.bkp File Name: /newmountpoint/FRA/2012_01_01/o1_mf_annnn_TAG20120101T112514_7hznjvr1_.bkp File Name: /newmountpoint/FRA/2012_01_01/o1_mf_ncnnf_TAG20120101T110008_7hzm5dob_.bkp File Name: /newmountpoint/FRA/2012_01_01/o1_mf_annnn_TAG20120101T110005_7hzm1p3b_.bkp File Name: /newmountpoint/FRA/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznmq8q_.bkp File Name: /newmountpoint/FRA/2012_01_01/o1_mf_nnndf_TAG20120101T110008_7hzm1rj9_.bkp File Name: /newmountpoint/FRA/2012_01_01/o1_mf_annnn_TAG20120101T112655_7hznmzlq_.bkp Do you really want to catalog the above files (enter YES or NO)? YES cataloging files... cataloging done List of Cataloged Files ======================= File Name: /newmountpoint/FRA/2012_01_01/o1_mf_annnn_TAG20120101T110005_7hzm1q6l_.bkp File Name: /newmountpoint/FRA/2012_01_01/o1_mf_annnn_TAG20120101T110205_7hzm5g23_.bkp File Name: /newmountpoint/FRA/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznm84k_.bkp File Name: /newmountpoint/FRA/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznjx24_.bkp File Name: /newmountpoint/FRA/2012_01_01/o1_mf_annnn_TAG20120101T112514_7hznjtnr_.bkp File Name: /newmountpoint/FRA/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznmydw_.bkp File Name: /newmountpoint/FRA/2012_01_01/o1_mf_annnn_TAG20120101T112514_7hznjvr1_.bkp File Name: /newmountpoint/FRA/2012_01_01/o1_mf_ncnnf_TAG20120101T110008_7hzm5dob_.bkp File Name: /newmountpoint/FRA/2012_01_01/o1_mf_annnn_TAG20120101T110005_7hzm1p3b_.bkp File Name: /newmountpoint/FRA/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznmq8q_.bkp File Name: /newmountpoint/FRA/2012_01_01/o1_mf_nnndf_TAG20120101T110008_7hzm1rj9_.bkp File Name: /newmountpoint/FRA/2012_01_01/o1_mf_annnn_TAG20120101T112655_7hznmzlq_.bkp RMAN> list backup summary; List of Backups =============== Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag ------- -- -- - ----------- --------------- ------- ------- ---------- --- 13 B F A DISK 01-JAN-12 1 1 NO TAG20120101T115229 14 B F A DISK 13-FEB-12 1 1 NO TAG20120213T234617 15 B A A DISK 23-MAR-12 1 1 YES TAG20120323T231436 16 B A A DISK 23-MAR-12 1 1 YES TAG20120323T231436 17 B A A DISK 23-MAR-12 1 1 YES TAG20120323T231436 18 B F A DISK 23-MAR-12 1 1 YES TAG20120323T231454 19 B A A DISK 23-MAR-12 1 1 YES TAG20120323T231639 20 B F A DISK 23-MAR-12 1 1 NO TAG20120323T231640 21 B F A DISK 23-MAR-12 1 1 YES TAG20120323T235528 22 B F A DISK 23-MAR-12 1 1 NO TAG20120323T235653 23 B F A DISK 24-MAR-12 1 1 NO TAG20120324T001027 24 B F A DISK 24-MAR-12 1 1 NO TAG20120324T001527 25 B F A DISK 24-MAR-12 1 1 NO TAG20120324T001528 26 B A A DISK 01-JAN-12 1 1 YES TAG20120101T110005 27 B A A DISK 01-JAN-12 1 1 YES TAG20120101T110205 28 B F A DISK 01-JAN-12 1 1 YES TAG20120101T112516 29 B F A DISK 01-JAN-12 1 1 YES TAG20120101T112516 30 B A A DISK 01-JAN-12 1 1 YES TAG20120101T112514 31 B F A DISK 01-JAN-12 1 1 YES TAG20120101T112516 32 B A A DISK 01-JAN-12 1 1 YES TAG20120101T112514 33 B F A DISK 01-JAN-12 1 1 YES TAG20120101T110008 34 B A A DISK 01-JAN-12 1 1 YES TAG20120101T110005 35 B F A DISK 01-JAN-12 1 1 YES TAG20120101T112516 36 B F A DISK 01-JAN-12 1 1 YES TAG20120101T110008 37 B A A DISK 01-JAN-12 1 1 YES TAG20120101T112655 RMAN>
After using the CATALOG START WITH for the new location of the 01-Jan-12 backuppieces, I can now "see" the backup in RMAN. Therefore, I should also be able to restore this backup.
Questions :
1. What could be the 13-FEB-12 backup that doesn't appear in the directory but does appear in a LIST BACKUP SUMMARY ?
2. What could be the 01-JAN-12 backup that still appears even though I have moved all the backup pieces ? (Note : I have deleted a few line in the "crosscheck backup" output that are about these two "backups".
3. What differences could be present between the 01-Jan-12 backup and the current / latest controlfile that I am using ?
4. Can I use "CROSSCHECK BACKUP" with backuppieces on tape ? 5. What would the COMPLETION_TIME for the 01-JAN-12 Archivelogs in V$ARCCHIVED_LOG ?
.
.
.
Also see RMAN Tips 1, 2, 3
.
.
.
.
07 May, 2012
A Poll on the usage of SQL Plan Management
Dominic Brooks has put up a poll on the usage of SQL Plan Management. If you have used it or tested it, please contribute. Your comments would be useful.
Note : In all honesty, I haven't used it yet.
.
.
.
Note : In all honesty, I haven't used it yet.
.
.
.
01 May, 2012
USER_TAB_MODIFICATIONS -- 1
Last week, conducting a portion of a training program on SQL Tuning, I briefly spoke about USER_TAB_MODIFICATIONS.
Here is a quick example to show how this view maintains information about the level of DML (INSERT / UPDATE / DELETE row counts) that have occurred since the last GATHER_STATS execution against the table. This information is used by the GATHER_STATS call to determine if statistics are stale (by comparing the level of DML with the last known NUM_ROWS in the table).
Notes :
(1) Once a GATHER_STATS is executed, the view is "reset" for the table(s) where Stats have been updated.
(2) The View is updated only by a DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO call which can be executed manually OR is, nevertheless, updated automatically by the background processes -- just as Workload statistics (used for AWRs) are updated automatically.
In view of the above two points, you should not rely on simply querying USER_TAB_MODIFICATIONS to identify the level of DML.
Here is a quick example to show how this view maintains information about the level of DML (INSERT / UPDATE / DELETE row counts) that have occurred since the last GATHER_STATS execution against the table. This information is used by the GATHER_STATS call to determine if statistics are stale (by comparing the level of DML with the last known NUM_ROWS in the table).
Notes :
(1) Once a GATHER_STATS is executed, the view is "reset" for the table(s) where Stats have been updated.
(2) The View is updated only by a DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO call which can be executed manually OR is, nevertheless, updated automatically by the background processes -- just as Workload statistics (used for AWRs) are updated automatically.
In view of the above two points, you should not rely on simply querying USER_TAB_MODIFICATIONS to identify the level of DML.
23:13:45 SQL> REM Demo User_Tab_Modifications
23:13:45 SQL>
23:13:45 SQL> alter session set nls_date_format='DD-MON HH24:MI:SS';
Session altered.
23:13:45 SQL>
23:13:45 SQL> -- create the target table
23:13:45 SQL> drop table UTM_TARGET purge;
Table dropped.
23:13:45 SQL> create table UTM_TARGET
23:13:45 2 as select * from dba_objects
23:13:45 3 where 1=2;
Table created.
23:13:45 SQL> select count(*) from UTM_TARGET;
COUNT(*)
----------
0
23:13:45 SQL>
23:13:45 SQL> exec dbms_lock.sleep(120);
PL/SQL procedure successfully completed.
23:15:45 SQL>
23:15:45 SQL> -- query USER_TAB_MODIFICATIONS
23:15:45 SQL> select timestamp,inserts,updates,deletes
23:15:45 2 from user_tab_modifications
23:15:45 3 where table_name = 'UTM_TARGET';
no rows selected
23:15:45 SQL>
23:15:45 SQL> -- insert rows and commit
23:15:45 SQL> insert into UTM_TARGET
23:15:45 2 select * from dba_objects;
76630 rows created.
23:15:46 SQL> commit;
Commit complete.
23:15:46 SQL>
23:15:46 SQL> exec dbms_lock.sleep(120);
PL/SQL procedure successfully completed.
23:17:46 SQL>
23:17:46 SQL> -- query USER_TAB_MODIFICATIONS
23:17:46 SQL> select timestamp,inserts,updates,deletes
23:17:46 2 from user_tab_modifications
23:17:46 3 where table_name = 'UTM_TARGET';
no rows selected
23:17:46 SQL>
23:17:46 SQL> -- flush monitoring info
23:17:46 SQL> exec dbms_stats.flush_database_monitoring_info;
PL/SQL procedure successfully completed.
23:17:46 SQL>
23:17:46 SQL> -- query USER_TAB_MODIFICATIONS
23:17:46 SQL> select timestamp,inserts,updates,deletes
23:17:46 2 from user_tab_modifications
23:17:46 3 where table_name = 'UTM_TARGET';
TIMESTAMP INSERTS UPDATES DELETES
--------------- ---------- ---------- ----------
01-MAY 23:17:46 76630 0 0
23:17:46 SQL>
23:17:46 SQL> -- what about Direct Path INSERTs ? are they captured
23:17:46 SQL> insert /*+ APPEND */ into UTM_TARGET
23:17:46 2 select * from dba_objects;
76630 rows created.
23:17:46 SQL>
23:17:46 SQL> -- run a query. It should error if Direct Path INSERT has been used
23:17:46 SQL> select count(*) from UTM_TARGET;
select count(*) from UTM_TARGET
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
23:17:46 SQL>
23:17:46 SQL> -- commit so that the Direct Path INSERT is visible
23:17:46 SQL> commit;
Commit complete.
23:17:46 SQL>
23:17:46 SQL> exec dbms_lock.sleep(120);
PL/SQL procedure successfully completed.
23:19:46 SQL>
23:19:46 SQL> -- query USER_TAB_MODIFICATIONS
23:19:46 SQL> select timestamp,inserts,updates,deletes
23:19:46 2 from user_tab_modifications
23:19:46 3 where table_name = 'UTM_TARGET';
TIMESTAMP INSERTS UPDATES DELETES
--------------- ---------- ---------- ----------
01-MAY 23:17:46 76630 0 0
23:19:46 SQL>
23:19:46 SQL> -- flush monitoring info
23:19:46 SQL> exec dbms_stats.flush_database_monitoring_info;
PL/SQL procedure successfully completed.
23:19:46 SQL>
23:19:46 SQL> -- query USER_TAB_MODIFICATIONS
23:19:46 SQL> select timestamp,inserts,updates,deletes
23:19:46 2 from user_tab_modifications
23:19:46 3 where table_name = 'UTM_TARGET';
TIMESTAMP INSERTS UPDATES DELETES
--------------- ---------- ---------- ----------
01-MAY 23:19:46 153260 0 0
23:19:46 SQL>
23:19:46 SQL> -- run an UPDATE
23:19:46 SQL> update UTM_TARGET
23:19:46 2 set owner = 'SYTEM2' where owner = 'SYSTEM';
1058 rows updated.
23:19:47 SQL> commit;
Commit complete.
23:19:47 SQL>
23:19:47 SQL> exec dbms_lock.sleep(120);
PL/SQL procedure successfully completed.
23:21:47 SQL>
23:21:47 SQL> -- flush monitoring info
23:21:47 SQL> exec dbms_stats.flush_database_monitoring_info;
PL/SQL procedure successfully completed.
23:21:47 SQL>
23:21:47 SQL> -- query USER_TAB_MODIFICATIONS
23:21:47 SQL> select timestamp,inserts,updates,deletes
23:21:47 2 from user_tab_modifications
23:21:47 3 where table_name = 'UTM_TARGET';
TIMESTAMP INSERTS UPDATES DELETES
--------------- ---------- ---------- ----------
01-MAY 23:21:47 153260 1058 0
23:21:47 SQL>
23:21:47 SQL>
23:21:47 SQL> -- what happens after we call DBMS_STATS.GATHER_TABLE_STATS ?
23:21:47 SQL> exec dbms_stats.gather_table_stats('HEMANT','UTM_TARGET');
PL/SQL procedure successfully completed.
23:21:47 SQL>
23:21:47 SQL> -- query USER_TAB_MODIFICATIONS
23:21:47 SQL> select timestamp,inserts,updates,deletes
23:21:47 2 from user_tab_modifications
23:21:47 3 where table_name = 'UTM_TARGET';
no rows selected
23:21:47 SQL> -- the GATHER_STATS call has now flushed the view
23:21:47 SQL> -- the DML "counter" has got reset to 0 -- w.r.t. the time when the Gather_Stats is executed
23:21:47 SQL>
23:21:47 SQL>
23:21:47 SQL> -- run a DELETE
23:21:47 SQL> delete UTM_TARGET
23:21:47 2 where owner = 'SYTEM2' ;
1058 rows deleted.
23:21:47 SQL> commit;
Commit complete.
23:21:47 SQL>
23:21:47 SQL> exec dbms_lock.sleep(120);
PL/SQL procedure successfully completed.
23:23:47 SQL>
23:23:47 SQL> -- flush monitoring info
23:23:47 SQL> exec dbms_stats.flush_database_monitoring_info;
PL/SQL procedure successfully completed.
23:23:47 SQL>
23:23:47 SQL> -- query USER_TAB_MODIFICATIONS
23:23:47 SQL> select timestamp,inserts,updates,deletes
23:23:47 2 from user_tab_modifications
23:23:47 3 where table_name = 'UTM_TARGET';
TIMESTAMP INSERTS UPDATES DELETES
--------------- ---------- ---------- ----------
01-MAY 23:23:47 0 0 1058
23:23:47 SQL>
As you can see, even if I populate my target table with rows, USER_TAB_MODIFICATIONS does not reflect this information until and unless DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO is called. Similarly, the last set of commands demnostrates how a DBMS_STATS.GATHER_TABLE_STATS has purged this view of information on my target tble (UTM_TARGET).
.
.
.
Subscribe to:
Comments (Atom)