Thursday 27 February 2014

Loss of all controlfile when database is in archive log mode?

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE
SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/d02/oracle/oradata/orcl/control01.ctl
/d02/oracle/flash_recovery_area/orcl/control02.ctl
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@wasiq orcl]$ rm /d02/oracle/oradata/orcl/control01.ctl /d02/oracle/flash_recovery_area/orcl/control02.ctl   ==>Very pricarious command !!! (Don' t this in live environments) 
++++++++++++++++++++
shutdown abort the database
++++++++++++++++++++
SQL> shutdown abort
ORACLE instance shut down.
SQL>

+++++++++++++++++++++++++++++++++
start the instance in nomount and create controlfile
++++++++++++++++++++++++++++++++++++++
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1372651520 bytes
Fixed Size                  1336428 bytes
Variable Size             385878932 bytes
Database Buffers          973078528 bytes
Redo Buffers               12357632 bytes
SQL>

[oracle@wasiq orcl]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Jan 1 12:43:08 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect target /;
connected to target database: ORCL (not mounted)
RMAN> restore controlfile from '/d02/oracle/flash_recovery_area/ORCL/backupset/2014_01_01/o1_mf_ncnnf_TAG20140101T123654_9d7hnhvf_.bkp';  ==>or  from autobackup
Starting restore at 01-JAN-14
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/d02/oracle/oradata/orcl/control01.ctl
output file name=/d02/oracle/flash_recovery_area/orcl/control02.ctl
Finished restore at 01-JAN-14
RMAN>

+++++++++++++++++++++++++++++++++
change the database stage to mount
+++++++++++++++++++++++++++++++++
RMAN> sql 'alter database mount';
sql statement: alter database mount
released channel: ORA_DISK_1
RMAN> recover database;
Starting recover at 01-JAN-14
Starting implicit crosscheck backup at 01-JAN-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=129 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=193 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=6 device type=DISK
Crosschecked 7 objects
Finished implicit crosscheck backup at 01-JAN-14
Starting implicit crosscheck copy at 01-JAN-14
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
Finished implicit crosscheck copy at 01-JAN-14
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /d02/oracle/flash_recovery_area/ORCL/backupset/2014_01_01/o1_mf_ncnnf_TAG20140101T123654_9d7hnhvf_.bkp
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
starting media recovery
archived log for thread 1 with sequence 13 is already on disk as file /d02/oracle/flash_recovery_area/ORCL/archivelog/2014_01_01/o1_mf_1_13_9d7hlvxo_.arc
archived log for thread 1 with sequence 14 is already on disk as file /d02/oracle/oradata/orcl/redo02.log
archived log file name=/d02/oracle/flash_recovery_area/ORCL/archivelog/2014_01_01/o1_mf_1_13_9d7hlvxo_.arc thread=1 sequence=13
archived log file name=/d02/oracle/oradata/orcl/redo02.log thread=1 sequence=14
media recovery complete, elapsed time: 00:00:00
Finished recover at 01-JAN-14
RMAN> sql 'alter database open resetlogs';
sql statement: alter database open resetlogs
RMAN>


Note : Take backup of the full database .

No comments:

Post a Comment