Oracle restoration case: incomplete restoration after a rename Data File

Case: a data file in rename cannot be completely restored.


SQL> startup mount; // starts to mount.
SQL> show parameter control_files // view the location information of the Control File
NAME TYPE VALUE
-----------------------------------------------------------------------------
Control_files string + DATA/metro/controlfile/backup
. 268.848861257
SQL> select name from v $ datafile;
// View the data file information
NAME
--------------------------------------------------------------------------------
/U01/app/oracle/oradata/metro/system01.dbf
/U01/app/oracle/oradata/metro/undotbs01.dbf
/U01/app/oracle/oradata/metro/sysaux01.dbf
/U01/app/oracle/oradata/metro/users01.dbf
/U01/app/oracle/oradata/metro/example01.dbf


SQL> alter database rename file '/u01/app/oracle/oradata/metro/system01.dbf' to '+ DATA/METRO/DATAFILE/SYSTEM.257.848858337 ';
// Modify the data file information


SQL> select status from v $ instance;
// View the current database status
STATUS
------------
MOUNTED


SQL> alter database open; // The system attempts to open the database and reports a resetlogs prompt.
Alter database open
*
ERROR at line 1:
The ORA-01589: must use RESETLOGS or NORESETLOGS option for database open




SQL> alter database open resetlogs; // starts the database in the form of resetlogs, prompting that the data file 1 is not
Alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '+ DATA/metro/datafile/system.257.848858337'


SQL> select checkpoint_change # from v $ datafile; // check the SCN of each data file in the control file


CHECKPOINT_CHANGE #
------------------
862849
862849
862849
862849
862849


SQL> select checkpoint_change # from v $ datafile_header; // check the SCN Number of the actual file


CHECKPOINT_CHANGE #
------------------
861998
863385
863385
863385
863385


SQL> recover datafile 1;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the backup controlfile option must be done




SQL> recover datafile 1 using backup controlfile;
ORA-00274: illegal recovery option USING




SQL> recover database; // The system prompts you to use the backup control file.
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the backup controlfile option must be done




SQL> recover database using backup controlfile; // use the backup control file to restore the database
ORA-00279: change 861998 generated at 05/29/2014 15:02:17 needed for thread 1
ORA-00289: suggestion:/u01/app/oracle/arch1/10915_837941495.dbf
ORA-00280: change 861998 for thread 1 is in sequence #15 // you are prompted to restore logs that require a sequence number of 15




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


ORA-00308: cannot open archived log '/u01/app/oracle/arch1/41515_837941495.dbf'
ORA-27037: unable to obtain file status
Ibm aix risc System/6000 Error: 2: No such file or directory
Additional information: 3




SQL> select * from v $ log; // view log group information


GROUP # THREAD # SEQUENCE # BYTES MEMBERS ARC STATUS
---------------------------------------------------------------------
FIRST_CHANGE # FIRST_TIM
----------------------
1 1 14 52428800 2 YES INACTIVE
859720 29-MAY-14


3 1 13 52428800 2 YES INACTIVE
858472 29-MAY-14


2 1 15 52428800 2 NO CURRENT
859846 29-MAY-14




SQL> select group #, member from v $ logfile;


GROUP #
----------
MEMBER
--------------------------------------------------------------------------------
3
/U01/app/oracle/oradata/metro/redo03.log


2
/U01/app/oracle/oradata/metro/redo02.log


1
/U01/app/oracle/oradata/metro/redo01.log




GROUP #
----------
MEMBER
--------------------------------------------------------------------------------
1
/Disk1/metro/redofile/redo01a. log


2
/Disk1/metro/redofile/redo02a. log


3
/Disk1/metro/redofile/redo03a. log




6 rows selected.


SQL> recover database using backup controlfile;
ORA-00279: change 861998 generated at 05/29/2014 15:02:17 needed for thread 1
ORA-00289: suggestion:/u01/app/oracle/arch1/10915_837941495.dbf
ORA-00280: change 861998 for thread 1 is in sequence #15




Specify log: {<RET> = suggested | filename | AUTO | CANCEL}
/Disk1/metro/redofile/redo02a. log // enter the log with the sequence number 15.
Log applied.
Media recovery complete.


SQL> select checkpoint_change # from v $ datafile_header; // The SCN of the checkpoint after the restoration is complete.


CHECKPOINT_CHANGE #
------------------
863385
863385
863385
863385
863385


SQL> alter database open
2;
Alter database open
*
ERROR at line 1:
The ORA-01589: must use RESETLOGS or NORESETLOGS option for database open




SQL> alter database open resetlogs; // use resetlogs to open the database


Database altered.


SQL> select name from v $ datafile // view Data File Information
2;


NAME
--------------------------------------------------------------------------------
+ DATA/metro/datafile/system.257.848858337
/U01/app/oracle/oradata/metro/undotbs01.dbf
/U01/app/oracle/oradata/metro/sysaux01.dbf
/U01/app/oracle/oradata/metro/users01.dbf
/U01/app/oracle/oradata/metro/example01.dbf


SQL>