Khurram Siddiqui

I am an IT professional with 10 years of professional experience,I have good proficiency on Oracle technologies, and at last 2 years of my career to study Real Application Clusters,data guard and participate actively on Oracle community ,If you want to hire me on Contract or to quote on project basis contact me at khurrampc@hotmail.com.

Friday, April 10, 2009

Media recovery start point

Media Recovery will always start from the most earliest restored datafile header CKPT SCN. 
SQL> select controlfile_change#,checkpoint_change#
  2    from v$database
  3  /

CONTROLFILE_CHANGE# CHECKPOINT_CHANGE#
------------------- ------------------
             675815             675666

SQL> select substr(name,1,51) fname,checkpoint_change#
  2    from v$datafile_header
  3  /

FNAME                                               CHECKPOINT_CHANGE#
--------------------------------------------------- ------------------
F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\SYSTEM01.DBF              675666
F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\UNDOTBS01.DBF             675666
F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\SYSAUX01.DBF              675666
F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\USERS01.DBF               675666
F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\USERS02.DBF               675666
Shutdown immediate the database and then make a cold copy of user01.dbf datafiles to folder A 
F:\oracle\product\10.1.0\oradata>cd prod

F:\oracle\product\10.1.0\oradata\prod>mkdir A

F:\oracle\product\10.1.0\oradata\prod>copy users01.dbf A
        1 file(s) copied.
Start the database 
SQL> startup
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145750508 bytes
Database Buffers           25165824 bytes
Redo Buffers                 262144 bytes
Database mounted.
Database opened.

SQL> select controlfile_change#,checkpoint_change#
  2    from v$database
  3  /

CONTROLFILE_CHANGE# CHECKPOINT_CHANGE#
------------------- ------------------
             676283             676131

SQL> select substr(name,1,51) fname,checkpoint_change#
  2    from v$datafile_header
  3  /

FNAME                                               CHECKPOINT_CHANGE#
--------------------------------------------------- ------------------
F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\SYSTEM01.DBF              676131
F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\UNDOTBS01.DBF             676131
F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\SYSAUX01.DBF              676131
F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\USERS01.DBF               676131
F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\USERS02.DBF               676131
shutdown immediate again the database and make copy of system01.dbf datafiles to folder B,every shutdown immediate itself make a checkpoint and increment the CKPT SCN at datafile header with controlfile. 
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.


F:\oracle\product\10.1.0\oradata\prod>mkdir B

F:\oracle\product\10.1.0\oradata\prod>copy system01.dbf B
        1 file(s) copied.
Start the database 
SQL> startup
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145750508 bytes
Database Buffers           25165824 bytes
Redo Buffers                 262144 bytes
Database mounted.
Database opened.
SQL> select controlfile_change#,checkpoint_change#
  2    from v$database
  3  /

CONTROLFILE_CHANGE# CHECKPOINT_CHANGE#
------------------- ------------------
             676623             676473

SQL> select substr(name,1,51) fname,checkpoint_change#
  2    from v$datafile_header
  3  /

FNAME                                               CHECKPOINT_CHANGE#
--------------------------------------------------- ------------------
F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\SYSTEM01.DBF              676473
F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\UNDOTBS01.DBF             676473
F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\SYSAUX01.DBF              676473
F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\USERS01.DBF               676473
F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\USERS02.DBF               676473
Shutdown immediate the database again and then make a cold copy of user02.dbf datafiles to folder C. 
F:\oracle\product\10.1.0\oradata\prod>mkdir C

F:\oracle\product\10.1.0\oradata\prod>copy users02.dbf C
        1 file(s) copied.

F:\oracle\product\10.1.0\oradata\prod>
Dont startup the database let it be remain shutdown,copy users01.dbf from folder A,system01 from folder B and users02 from folder C to the folder F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD respectivley. After copying those file to prod folder from A,B and C startup the database 
==========================================================================
Copy user01.dbf from folder A (F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\A) 
to folder PROD (F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD)
==========================================================================

F:\oracle\product\10.1.0\oradata\prod>copy F:\oracle\product\10.1.0\oradata\prod\A\users01.dbf  
                                      F:\oracle\product\10.1.0\oradata\prod\
Overwrite F:\oracle\product\10.1.0\oradata\prod\USERS01.DBF? (Yes/No/All): yes
        1 file(s) copied.

===========================================================================
Copy system01.dbf from folder B (F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\B) 
to folder PROD (F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD)
===========================================================================

F:\oracle\product\10.1.0\oradata\prod>copy F:\oracle\product\10.1.0\oradata\prod
\B\system01.dbf  F:\oracle\product\10.1.0\oradata\prod\
Overwrite F:\oracle\product\10.1.0\oradata\prod\SYSTEM01.DBF? (Yes/No/All): yes
        1 file(s) copied.

===========================================================================
Copy users02.dbf from folder C (F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\C) 
to folder PROD (F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD)
===========================================================================

F:\oracle\product\10.1.0\oradata\prod>copy F:\oracle\product\10.1.0\oradata\prod
\C\users02.dbf  F:\oracle\product\10.1.0\oradata\prod\
Overwrite F:\oracle\product\10.1.0\oradata\prod\USERS02.DBF? (Yes/No/All): yes
        1 file(s) copied.

SQL> startup
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145750508 bytes
Database Buffers           25165824 bytes
Redo Buffers                 262144 bytes
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\SYSTEM01.DBF'


SQL> select controlfile_change#,checkpoint_change#
  2    from v$database
  3  /

CONTROLFILE_CHANGE# CHECKPOINT_CHANGE#
------------------- ------------------
             678259             678859

SQL> select substr(name,1,51) fname,checkpoint_change#
  2    from v$datafile_header
  3  /

FNAME                                               CHECKPOINT_CHANGE#
--------------------------------------------------- ------------------
F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\SYSTEM01.DBF              676472
F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\UNDOTBS01.DBF             678859
F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\SYSAUX01.DBF              678859
F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\USERS01.DBF               676130
F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\USERS02.DBF               678859
See above you will find that 676130 of users01.dbf is the earliest/oldest CKPT SCN with others datafile system01.dbf 676472 and users02.dbf 678859 recovery will start from that point 676130 which is earliest and oldest within restored datafiles. 
SQL> recover database until cancel
ORA-00279: change 676130 generated at 04/11/2009 04:02:23 needed for thread 1
ORA-00289: suggestion :
F:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\PROD\ARCHIVELOG\2009_04_11\O1_MF_1_
1_%U_.ARC
ORA-00280: change 676130 for thread 1 is in sequence #1


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
Posted by at

3 comments:

nayyares said...

Hi,

Could you put some context of this post? my bad, i can't get the idea properly :(

thanks

May 11, 2009 at 12:06 AM
Khurram Siddiqui said...

i just put an idea that media recovery will start from the earliest ckpt scn of restored database files,re read my post you will get it , if not then ask again what are you not getting?

May 14, 2009 at 12:15 AM
daspeac said...
This comment has been removed by a blog administrator.
November 26, 2010 at 10:24 AM

Post a Comment

Subscribe to: Post Comments (Atom)

Followers

About Me

My photo
Melbourne, Victoria, Australia