Case: Rename a data file after doing incomplete recovery
sql>startup mount;//boot to Mount state
Sql> Show parameter Control_files//view location information for control files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string +data/metro/controlfile/backup
. 268.848861257
sql> select name from V$datafile;
//View information for data files
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 ';
//change information for data files
sql> Select status from V$instance;
//View the current status of the database
STATUS
------------
Mounted
sql> ALTER DATABASE open; Try to start the library and report the need for Resetlogs tips
ALTER DATABASE open
*
ERROR at line 1:
ora-01589:must use resetlogs or noresetlogs option for database open
sql> ALTER DATABASE open resetlogs;//in resetlogs form, prompting the data file 1 does not
ALTER DATABASE open Resetlogs
*
ERROR at line 1:
Ora-01152:file 1 was wasn't 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 data files in the control file SCN
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 is done
sql> recover datafile 1 using Backup controlfile;
ora-00274:illegal recovery Option USING
sql> recover database; Prompt to use Backup control files
ora-00283:recovery session canceled due to errors
ora-01610:recovery using the BACKUP controlfile option must is done
sql> recover database using Backup controlfile; Recovering a database using a backed-up control file
ora-00279:change 861998 generated at 05/29/2014 15:02:17 needed for thread 1
ora-00289:suggestion:/u01/app/oracle/arch1/1_15_848741495.dbf
ora-00280:change 861998 for thread 1 are in sequence #15//prompt recovery requires sequence number 15 log
Specify log: {<ret>=suggested | AUTO | CANCEL}
Ora-00308:cannot Open archived log '/u01/app/oracle/arch1/1_15_848741495.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 52428800 2 YES INACTIVE
859720 29-may-14
3 1 52428800 2 YES INACTIVE
858472 29-may-14
2 1 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/1_15_848741495.dbf
ora-00280:change 861998 for thread 1 are in sequence #15
Specify log: {<ret>=suggested | AUTO | CANCEL}
/disk1/metro/redofile/redo02a.log//input finds the log corresponding to the sequence number 15
Log applied.
Media recovery complete.
sql> Select checkpoint_change# from V$datafile_header; SCN of checkpoint after recovery complete
checkpoint_change#
------------------
863385
863385
863385
863385
863385
sql> ALTER DATABASE open
2;
ALTER DATABASE open
*
ERROR at line 1:
ora-01589:must use resetlogs or noresetlogs option for database open
sql> ALTER DATABASE open resetlogs; To open a library using Resetlogs
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>