Oracle restoration case: incomplete restoration after a rename Data File

Source: Internet
Author: User

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>

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.