Search My Oracle Blog

Custom Search

30 May, 2012

SQL written by Lisbeth Salander

A review of the SQL that Lisbeth Salander [in the movie "The Girl With The Dragon Tattoo"] writes.
.
.
.

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 :

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> 
I then backup the tablespace :

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 :

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 :






(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
.
.
.
.

"Debugging" stories

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.
.
.
.

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.


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).
.
.
.

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com