To back up the control file to specified location
alter database backup controlfile to trace as '$ORACLE_HOME/dbs/controlfile.ora.bck';
To back up the control file to trace location
alter database backup controlfile to trace;
show parameter core_dump_dest;
/u01/app/oracle/diag/rdbms/standby/standby/cdump
you have to move to trace directory to find the backup trace
/u01/app/oracle/diag/rdbms/standby/standby/trace
To restore the Control File from trace
SQL> startup
ORACLE instance started.
Total System Global Area 369098752 bytes
Fixed Size 2924544 bytes
Variable Size 289406976 bytes
Database Buffers 71303168 bytes
Redo Buffers 5464064 bytes
ORA-00205: error in identifying control file, check alert log for more info
SQL> select status from v$instance;
STATUS
------------
STARTED
steps for restoring
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "AGAP" NORESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
'/u01/app/oracle/oradata/standby/redo1/STANDBY/onlinelog/o1_mf_1_dv0tdxbl_.log',
'/u01/app/oracle/oradata/standby/redo2/STANDBY/onlinelog/o1_mf_1_dv0tdxmc_.log'
) SIZE 50M BLOCKSIZE 512,
GROUP 2 (
'/u01/app/oracle/oradata/standby/redo1/STANDBY/onlinelog/o1_mf_2_dv0tdxwp_.log',
'/u01/app/oracle/oradata/standby/redo2/STANDBY/onlinelog/o1_mf_2_dv0tdy5y_.log'
) SIZE 50M BLOCKSIZE 512,
GROUP 3 ( '/u01/app/oracle/oradata/standby/redo1/STANDBY/onlinelog/o1_mf_3_dv0tdyh4_.log',
'/u01/app/oracle/oradata/standby/redo2/STANDBY/onlinelog/o1_mf_3_dv0tdypq_.log'
) SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
-- GROUP 4 (
-- '/u01/app/oracle/oradata/standby/redo1/STANDBY/onlinelog/o1_mf_4_dv0tdyz3_.log',
-- '/u01/app/oracle/oradata/standby/redo2/STANDBY/onlinelog/o1_mf_4_dv0tdz7l_.log'
-- ) SIZE 50M BLOCKSIZE 512,
-- GROUP 5 (
-- '/u01/app/oracle/oradata/standby/redo1/STANDBY/onlinelog/o1_mf_5_dv0tdzj3_.log',
-- '/u01/app/oracle/oradata/standby/redo2/STANDBY/onlinelog/o1_mf_5_dv0tdzqq_.log'
-- ) SIZE 50M BLOCKSIZE 512,
-- GROUP 6 (
-- '/u01/app/oracle/oradata/standby/redo1/STANDBY/onlinelog/o1_mf_6_dv0tf01d_.log',
-- '/u01/app/oracle/oradata/standby/redo2/STANDBY/onlinelog/o1_mf_6_dv0tf09r_.log'
-- ) SIZE 50M BLOCKSIZE 512
DATAFILE
'/u01/app/oracle/oradata/STANDBY/datafile/o1_mf_system_dv0tdfz4_.dbf',
'/u01/app/oracle/oradata/STANDBY/datafile/o1_mf_sysaux_dv0tdg07_.dbf',
'/u01/app/oracle/oradata/STANDBY/datafile/o1_mf_undotbs1_dv0tdg0d_.dbf',
'/u01/app/oracle/oradata/STANDBY/datafile/o1_mf_users_dv0tdg0h_.dbf'
CHARACTER SET WE8MSWIN1252
;
-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');
-- Configure RMAN configuration record 2
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('COMPRESSION ALGORITHM','''HIGH'' AS OF RELEASE ''DEFAULT'' OPTIMIZE FOR LOAD TRUE');
-- Configure RMAN configuration record 3
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CHANNEL','DEVICE TYPE DISK FORMAT ''/backup_rman/agap%U''');
ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/fast_recovery_area/standby/STANDBY/archivelog/2017_09_15/o1_mf_1_1_%u_.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE
-- All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
-- Database can now be opened normally.
ALTER DATABASE OPEN;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/STANDBY/datafile/o1_mf_temp_dv28hjm6_.tmp' REUSE;
-- End of tempfile additions.
vi restore_control_file.sql # in this case no resetlogs
[oracle@ol7-122 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Tue Sep 19 14:14:02 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> @restore_control_file.sql
ORA-01081: cannot start already-running ORACLE - shut it down first
Control file created.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required
System altered.
Database altered.
Tablespace altered.
SQL>
