alter database backup controlfile to ['filename' | trace]
This command comes in two versions. One backs up the control file in a binary format while the other backs it up in a human readable form. It is required if the database is running inarchive log mode and a structural change was made to the database.
Backing it up in a binary format:
alter database backup controlfile to '/some/arbitrary/path'; alter database backup controlfile to '/some/arbitrary/path' reuse;
Backing it up in a human readable format:
alter database backup controlfile to trace; alter database backup controlfile to trace as '/some/arbitrary/path'; alter database backup controlfile to trace as '/some/arbitrary/path' reuse;
Re-create lost controlfile (demo/testing)Here in this demo i deleted all control files and re-created using trace file.1. check your database status SQL> select name from v$controlfile;
NAME -----------------------------------------------------------------
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ASSAD\CONTROL01.CTLC:\ORACLE\PRODUCT\10.2.0\ORADATA\ASSAD\CONTROL02.CTLC:\ORACLE\PRODUCT\10.2.0\ORADATA\ASSAD\CONTROL03.CTL
SQL> select name from v$datafile;
NAME-----------------------------------------------------------------
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ASSAD\SYSTEM01.DBFC:\ORACLE\PRODUCT\10.2.0\ORADATA\ASSAD\UNDOTBS01.DBFC:\ORACLE\PRODUCT\10.2.0\ORADATA\ASSAD\SYSAUX01.DBFC:\ORACLE\PRODUCT\10.2.0\ORADATA\ASSAD\USERS01.DBFC:\ORACLE\PRODUCT\10.2.0\ORADATA\ASSAD\EXAMPLE01.DBFC:\ORACLE\PRODUCT\10.2.0\ORADATA\ASSAD\EMPC:\ORACLE\PRODUCT\10.2.0\ORADATA\ASSAD\EMPDATA
7 rows selected.
2. remove the control files(control01.ctl,control02.ctl,control03.ctl) from the location C:\ORACLE\PRODUCT\10.2.0\ORADATA\ASSAD\ for testing purpose to re-create it.
3. run the script below to recreate a lost control files
CREATE CONTROLFILE REUSE DATABASE "assad" RESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ASSAD\redo01.log' SIZE 5M,
GROUP 2 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ASSAD\redo02.log' SIZE 5M,
GROUP 3 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ASSAD\redo03.log' SIZE 5M
-- STANDBY LOGFILE
DATAFILE
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ASSAD\SYSTEM01.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ASSAD\UNDOTBS01.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ASSAD\SYSAUX01.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ASSAD\USERS01.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ASSAD\EXAMPLE01.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ASSAD\EMP',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ASSAD\EMPDATA'
CHARACTER SET WE8ISO8859P1
;
Control file created.
SQL> alter database open resetlogs;
Database altered.
and this is the end of your test.