oracle| Recovery | control | data
---------------------------------------------
Experimental platform: Windows + Oracle 10.1.0.2.0
Author:ningoo 2005-03-26
--------------------------------------------
After the control file is backed up, the data file is added to the database, and the current control file is corrupted and needs to be recovered using the previous backup control file.
1. Backup Control files
sql> ALTER DATABASE backup Controlfile to ' E:\control.ctl ';
The database has changed.
2. Add a data file to the database using create tablespace or alter tablespace add datafile
sql> Create tablespace Test
2 DataFile ' E:\ORACLE\ORADATA\NING\TEST01. DBF ' Size 10M,
3 ' E:\ORACLE\ORADATA\NING\TEST02. DBF ' size 10M;
The table space has been created.
Remove the current control file after 3.shutdown
4.startup Database
Error: Ora-00205:error in identifying Controlfile, check alert log for more info
5. Restore the control file for backup
6. Use Backup control file for recovery
sql> Recover database using Backup controlfile;
ORA-00283: Recovery session canceled due to error
ORA-01244: Unnamed data files are added to the control file by media Restore
ORA-01110: Data file 5: ' E:\ORACLE\ORADATA\NING\TEST01. DBF '
ORA-01110: Data file 6: ' E:\ORACLE\ORADATA\NING\TEST02. DBF '
At this point, view V$datafile can see that there are two unname files
Sql> select name from V$datafile;
NAME
-------------------------------------------
E:\ORACLE\ORADATA\NING YSTEM01. Dbf
E:\ORACLE\ORADATA\NING\UNDOTBS01. Dbf
E:\ORACLE\ORADATA\NING YSAUX01. Dbf
E:\ORACLE\ORADATA\NING\USERS01. Dbf
C:\WINDOWS ystem32\unnamed00005
C:\WINDOWS ystem32\unnamed00006
Viewing Alert_sid.log, you can see the following
File #5 added to control file as ' UNNAMED00005 '. Originally created as:
' E:\ORACLE\ORADATA\NING\TEST01. DBF '
File #6 added to control file as ' UNNAMED00006 '. Originally created as:
' E:\ORACLE\ORADATA\NING\TEST02. DBF '
Know UNNAMED00005 corresponds to ' E:\ORACLE\ORADATA\NING\TEST01. DBF '
UNNAMED00006 corresponds to ' E:\ORACLE\ORADATA\NING\TEST02. DBF '
7. Renaming data files
sql> ALTER DATABASE rename file ' C:\WINDOWS ystem32\unnamed00005 ' to
2 ' E:\ORACLE\ORADATA\NING\TEST01. DBF ';
The database has changed.
sql> ALTER DATABASE rename file ' C:\WINDOWS ystem32\unnamed00006 ' to
2 ' E:\ORACLE\ORADATA\NING\TEST02. DBF ';
The database has changed.
8. Then use Backup to control file recovery
sql> Recover database using Backup controlfile;
Complete media recovery.
9. To open a database using the Resetlogs option
sql> ALTER DATABASE open;
ALTER DATABASE Open
*
Line 1th Error:
ORA-01589: You must use the Resetlogs or Noresetlogs option to open the database
sql> ALTER DATABASE open noresetlogs;
ALTER DATABASE open Noresetlogs
*
Line 1th Error:
ORA-01588: You must use the Resetlogs option to open the database
sql> ALTER DATABASE open resetlogs;
The database has changed.
10. Add temp file
View Alert_sid.log at this point, and you can see the following warning
***************************************************************
Warning:the following temporary tablespaces contain no files.
This condition can occur when a backup controlfile has
been restored. It may is necessary to add files to
Tablespaces. That can is done using the SQL statement:
ALTER tablespace <tablespace_name> ADD tempfile
Alternatively, if these temporary tablespaces are no longer
Needed, then they can be dropped.
Empty Temporary Tablespace:temp
***************************************************************
Temp table space is empty after backup control file recovery
Sql> select name from V$tempfile;
No rows selected
Sql>alter tablespace Temp Add tempfile
2 ' E:\ORACLE\ORADATA\NING\TEMP01. DBF ';
The table space has changed.
