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

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016