Condition:
All copies of the control file have been deleted
The data is in the order of files and online redo logs
Our control file has backups
Database archive works in log mode
First we look at where our control files are located
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DB11G/control01.ctl
/u01/app/oracle/oradata/DB11G/control02.ctl
We then move all control files to another directory to simulate the loss of control poems:
SQL> !mv /u01/app/oracle/oradata/DB11G/*.ctl /u01/
To check deletion of the control file, we ask for information from the control file. An error should occur because there is no data. But before you do that, you need to reconnect to SQLplus again, because an error may not occur if you remain connected to the previous session.
SQL> exit
[oracle@orcl]$ sqlplus / as sysdba
SQL> select * from v$database;
select * from v$database
*ERROR at line 1:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/DB11G/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
All of our control files have been lost and now we need to take the following steps to get back to this situation.
It is necessary to quench the base and set it to nomount mode. We will then revert our control files from autobackup:
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
::::::::::::::output trimmed::::::::::::::
RMAN> restore controlfile from autobackup;
:::::::::::::: output trimmed ::::::::::::::
output file name=/u01/app/oracle/oradata/DB11G/control01.ctl
output file name=/u01/app/oracle/oradata/DB11G/control02.ctl
Finished restore at 28-APR-15
Then bring the database mount state and recover the database
RMAN> alter database mount;
RMAN> recover database;
Issue resetlogs command and open database
RMAN> alter database open resetlogs;
database opened
https://www.youtube.com/watch?v=M9VrdfRaO38 Situation:
- All copies of control file have been lost
- Data files and online redo logs are fine
- We have a backup of the control file
- Database is running in archive log mode
First, we look where is located our control files
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DB11G/control01.ctl
/u01/app/oracle/oradata/DB11G/control02.ctlThen remove all the control files to another directory which is equivalent to losing all of the control files.
SQL> !mv /u01/app/oracle/oradata/DB11G/*.ctl /u01/Make sure that an error occurs, access something which would only come from the control file. But firs reconnect to SQLplus , because errors may not be seen when still being connected to previous session.
SQL> exit
[oracle@orcl]$ sqlplus / as sysdba
SQL> select * from v$database;
select * from v$database
*ERROR at line 1:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/DB11G/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
So there are no more control files there.
For recovery follow the steps bellow:
Shut down database, then start the database in nomount state and restore the controlfile from autobackup:
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
::::::::::::::output trimmed::::::::::::::
RMAN> restore controlfile from autobackup;
:::::::::::::: output trimmed ::::::::::::::
output file name=/u01/app/oracle/oradata/DB11G/control01.ctl
output file name=/u01/app/oracle/oradata/DB11G/control02.ctl
Finished restore at 28-APR-15
Bring the database mount state and recover the database. Then issue resetlogs command and open database:
RMAN> alter database mount;
RMAN> recover database;
RMAN> alter database open resetlogs;
database opened
