Jephe Wu - http://linuxtechres.blogspot.com
Objective: rebuild database controlfile without any backup
Environment: Oracle database enterprise edition 11.2.0.3 64bit, lost controlfile without any RMAN backup, datafile and online redo log are okay. need to rebuild control file manually.
Steps:
Preparation:
- Find out all files path such as redo log, datafile
- Find out database SID from /etc/oratab
- calculate redo log file size
1. prepare rebuild script.
-- 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.
-- 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.
$ more controlfile_rebuild.sql
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "sm" NORESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 1200
MAXINSTANCES 8
MAXLOGHISTORY 584
LOGFILE
GROUP 1 (
'/u01/oradata/sm/redo01.log'
) SIZE 50M BLOCKSIZE 512,
GROUP 2 (
'/u01/oradata/sm/redo02.log'
) SIZE 50M BLOCKSIZE 512,
GROUP 3 (
'/u01/oradata/sm/redo03.log'
) SIZE 50M BLOCKSIZE 512
DATAFILE
'/u02/oradata/sm/system01.dbf',
'/u02/oradata/sm/sysaux01.dbf',
'/u02/oradata/sm/undotbs01.dbf',
'/u02/oradata/sm/users01.dbf'
CHARACTER SET AL32UTF8
;
RECOVER DATABASE
ALTER SYSTEM ARCHIVE LOG ALL;
ALTER DATABASE OPEN;
[oracle@oratest sm]$ sqlplus / as sysdba @controlrebuild.sql
SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 3 15:50:39 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
ORACLE instance started.
Total System Global Area 4275781632 bytes
Fixed Size 2235208 bytes
Variable Size 2315257016 bytes
Database Buffers 1946157056 bytes
Redo Buffers 12132352 bytes
Control file created.
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required
System altered.
Database altered.
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u02/oradata/sm/system01.dbf
/u02/oradata/sm/sysaux01.dbf
/u02/oradata/sm/undotbs01.dbf
/u02/oradata/sm/users01.dbf
SQL> show parameter control
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /u01/oradata/sm/control01.ctl,
/u01/oradata/sm/control02.ctl
control_management_pack_access string DIAGNOSTIC+TUNING
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/oradata/sm/temp02.dbf' size 10m autoextend on next 1m;
Tablespace altered.
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/u02/oradata/sm/temp02.dbf
sql> !rm -f /u02/oradata/sm/temp01.dbf
Posts
