Search My Oracle Blog

Custom Search

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.

.
.
.

No comments:

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