Oracle Backup and Restore
Case Scenarios - Case 3
Case 3: When All Control
files binary copies are lost.
Steps followed:
1. Create trace backup of control file.
2. Shutdown database
3. Try to startup (which will fail)
4. Create new copy of control file from steps in trace
file
5. Open database in RESETLOGS or NORESETLOGS mode
6. Create new Temp file
Situation when to
follow this process.
•
If you have lost all of your control files, and you don’t have a good binary
backup of the control file.
•
If you want to change a database setting that can be modified only by
re-creating the control file.
Set trace file
identifier to easily identify the trace file.
SQL>
alter session set tracefile_identifier='control';
Session
altered.
Create backup of
control file.
SQL>
alter database backup controlfile to trace;
Database
altered.
Check the location
where control file trace will be located.
SQL>
show parameter user_dump_dest
NAME TYPE
VALUE
------------------------------------
----------- ------------------------
user_dump_dest string
/u01/app/oracle/diag/rdbms/oradb1/ORADB1/trace
cd
/u01/app/oracle/diag/rdbms/oradb1/ORADB1/trace
[oracle@localhost
trace]$ pwd
/u01/app/oracle/diag/rdbms/oradb1/ORADB1/trace
Check the trace
file creation.
[oracle@localhost
trace]$ ls -ltr *control*
-rw-r-----.
1 oracle oinstall 61 Sep 22 00:34
ORADB1_ora_9206_control.trm
-rw-r-----.
1 oracle oinstall 6920 Sep 22 00:34 ORADB1_ora_9206_control.trc
You can see
control file creation commands in trace file created.
[oracle@localhost
trace]$ cat ORADB1_ora_9206_control.trc
...
...
--
--
Below are two sets of SQL statements, each of which creates a new
--
control file and uses it to open the database. The first set opens
--
the database with the NORESETLOGS option and should be used only if
--
the current versions of all online logs are available. The second
--
set opens the database with the RESETLOGS option and should be used
--
if online logs are unavailable.
--
The appropriate set of statements can be copied from the trace into
--
a script file, edited as necessary, and executed when there is a
--
need to re-create the control file.
--
-- Set #1. NORESETLOGS case
--
--
The following commands will create a new control file and use it
--
to open the database.
--
Data used by Recovery Manager will be lost.
--
Additional logs may be required for media recovery of offline
--
Use this only if the current versions of all online logs are
--
available.
--
After mounting the created controlfile, the following SQL
--
statement will place the database in the appropriate
--
protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO
MAXIMIZE PERFORMANCE
STARTUP
NOMOUNT
CREATE
CONTROLFILE REUSE DATABASE "ORADB1" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/ORADB1/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2
'/u01/app/oracle/oradata/ORADB1/redo02.log'
SIZE 50M BLOCKSIZE 512,
GROUP 3
'/u01/app/oracle/oradata/ORADB1/redo03.log'
SIZE 50M BLOCKSIZE 512
--
STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/ORADB1/system01.dbf',
'/u01/app/oracle/oradata/ORADB1/sysaux01.dbf',
'/u01/app/oracle/oradata/ORADB1/undotbs01.dbf',
'/u01/app/oracle/oradata/ORADB1/users01.dbf',
'/u01/app/oracle/oradata/ORADB1/example01.dbf'
CHARACTER
SET AL32UTF8
;
--
Commands to re-create incarnation table
--
Below log names MUST be changed to existing filenames on
--
disk. Any one log file from each branch can be used to
--
re-create incarnation records.
--
ALTER DATABASE REGISTER LOGFILE
'/u01/app/oracle/oradata/ORADB1/archive/1_1_824297850.dbf';
--
ALTER DATABASE REGISTER LOGFILE
'/u01/app/oracle/oradata/ORADB1/archive/1_1_949505021.dbf';
--
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/ORADB1/temp01.dbf'
SIZE 30408704 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
--
End of tempfile additions.
--
-- Set #2. RESETLOGS case
--
--
The following commands will create a new control file and use it
--
to open the database.
--
Data used by Recovery Manager will be lost.
--
The contents of online logs will be lost and all backups will
--
be invalidated. Use this only if online logs are damaged.
--
After mounting the created controlfile, the following SQL
--
statement will place the database in the appropriate
--
protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO
MAXIMIZE PERFORMANCE
STARTUP
NOMOUNT
CREATE
CONTROLFILE REUSE DATABASE "ORADB1" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1
'/u01/app/oracle/oradata/ORADB1/redo01.log'
SIZE 50M BLOCKSIZE 512,
GROUP 2
'/u01/app/oracle/oradata/ORADB1/redo02.log'
SIZE 50M BLOCKSIZE 512,
GROUP 3
'/u01/app/oracle/oradata/ORADB1/redo03.log'
SIZE 50M BLOCKSIZE 512
--
STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/ORADB1/system01.dbf',
'/u01/app/oracle/oradata/ORADB1/sysaux01.dbf',
'/u01/app/oracle/oradata/ORADB1/undotbs01.dbf',
'/u01/app/oracle/oradata/ORADB1/users01.dbf',
'/u01/app/oracle/oradata/ORADB1/example01.dbf'
CHARACTER
SET AL32UTF8
;
--
Commands to re-create incarnation table
--
Below log names MUST be changed to existing filenames on
--
disk. Any one log file from each branch can be used to
--
re-create incarnation records.
--
ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/oradata/ORADB1/archive/1_1_824297850.dbf';
--
ALTER DATABASE REGISTER LOGFILE
'/u01/app/oracle/oradata/ORADB1/archive/1_1_949505021.dbf';
--
Recovery is required if any of the datafiles are restored backups,
--
or if the last shutdown was not normal or immediate.
RECOVER
DATABASE USING BACKUP CONTROLFILE
--
Database can now be opened zeroing the online logs.
ALTER
DATABASE OPEN RESETLOGS;
--
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/ORADB1/temp01.dbf'
SIZE 30408704 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
--
End of tempfile additions.
--
[oracle@localhost
trace]$
There are two SQL statements in the trace file: One with noresetlogs
and another with
resetlogs. If you still have good online redo log files, then use the
NORESETLOGS
option. If you are missing your online redo log files, then use the
resetlogs option.
Since we have lost
only control files and all other files are intact, we will use NORESETLOGS
option.
Connect to
SQLPLUS.
[oracle@localhost
trace]$ sqlplus / as sysdba
SQL*Plus:
Release 11.2.0.4.0 Production on Fri Sep 22 00:40:49 2017
Copyright
(c) 1982, 2013, Oracle. All rights
reserved.
Connected
to:
Oracle
Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With
the Partitioning, OLAP, Data Mining and Real Application Testing options
Check control
files to verify later after restore.
SQL>
show parameter control_files
NAME TYPE
VALUE
------------------------------------
----------- ------------------------
control_files string
/u01/app/oracle/oradata/ORADB1/control01.ctl,
/u01/app/oracle/fast_recovery_area/ORADB1/control02.ctl
Shutdown the
database.
SQL>
shut immediate;
Database
closed.
Database
dismounted.
ORACLE
instance shut down.
SQL>
exit
Disconnected
from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit
Production
With
the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost
trace]$
Simulate failure
by renaming both control files.
[oracle@localhost
trace]$ mv /u01/app/oracle/oradata/ORADB1/control01.ctl
/u01/app/oracle/oradata/ORADB1/control01.bkup
[oracle@localhost
trace]$ mv /u01/app/oracle/fast_recovery_area/ORADB1/control02.ctl
/u01/app/oracle/fast_recovery_area/ORADB1/control02.bkup
Connec to sqlplus.
[oracle@localhost
trace]$ sqlplus / as sysdba
SQL*Plus:
Release 11.2.0.4.0 Production on Fri Sep 22 00:55:36 2017
Copyright
(c) 1982, 2013, Oracle. All rights
reserved.
Connected
to an idle instance.
Try to start the
database which will fail due to lack of control files.
SQL>
startup
ORACLE
instance started.
Total
System Global Area 835104768 bytes
Fixed
Size 2257840 bytes
Variable
Size 520096848 bytes
Database
Buffers
310378496 bytes
Redo
Buffers 2371584 bytes
ORA-00205:
error in identifying control file, check alert log for more info
Shutdown the
database.
SQL>
shut abort;
ORACLE
instance shut down.
Since control
files are not there, database should be started in NOMOUNT.
SQL>
startup nomount;
ORACLE
instance started.
Total
System Global Area 835104768 bytes
Fixed
Size 2257840 bytes
Variable
Size 520096848 bytes
Database
Buffers
310378496 bytes
Redo
Buffers 2371584 bytes
Create control
file as per commands in trace file.
SQL>
CREATE CONTROLFILE REUSE DATABASE "ORADB1" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/ORADB1/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2
'/u01/app/oracle/oradata/ORADB1/redo02.log'
SIZE 50M BLOCKSIZE 512,
GROUP 3
'/u01/app/oracle/oradata/ORADB1/redo03.log'
SIZE 50M BLOCKSIZE 512
--
STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/ORADB1/system01.dbf',
'/u01/app/oracle/oradata/ORADB1/sysaux01.dbf',
'/u01/app/oracle/oradata/ORADB1/undotbs01.dbf',
'/u01/app/oracle/oradata/ORADB1/users01.dbf',
'/u01/app/oracle/oradata/ORADB1/example01.dbf'
CHARACTER
SET AL32UTF8
;
Control
file created.
Follow other
commands as present in script.
SQL>
RECOVER DATABASE;
ORA-00283:
recovery session canceled due to errors
ORA-00264:
no recovery required
SQL>
ALTER SYSTEM ARCHIVE LOG ALL;
System
altered.
SQL>
ALTER DATABASE OPEN;
Database
altered.
Addition of temp
file as new control file is created.
SQL>
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/ORADB1/temp01.dbf'
SIZE 30408704 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M; 2
Tablespace
altered.
SQL>
select name, open_mode from v$database;
NAME
OPEN_MODE
---------
--------------------
ORADB1
READ WRITE
Check whether all
control files are restored.
SQL>
show parameter control_files
NAME TYPE
VALUE
------------------------------------
----------- ------------------------
control_files string
/u01/app/oracle/oradata/ORADB1/control01.ctl,
/u01/app/oracle/fast_recovery_area/ORADB1/control02.ctl
No comments:
Post a Comment
Leave a Reply...