Tuesday, June 16, 2015

Recover database after lost redo+control files (with database clean shutdown)

oracle@solaris112:/u01/app/oracle/oradata/orcl2$ rm redo0*
oracle@solaris112:/u01/app/oracle/oradata/orcl2$ rm control01.ctl
oracle@solaris112:/u01/app/oracle/oradata/orcl2$ rm "/u01/app/oracle/fast_recovery_area/orcl2/control02.ctl"

oracle@solaris112:/u01/app/oracle/oradata/orcl2$ ls -ltr
total 5650988
-rw-r-----   1 oracle   oinstall 62922752 Jun 17 07:24 temp01.dbf
-rw-r-----   1 oracle   oinstall 817897472 Jun 17 07:25 system01.dbf
-rw-r-----   1 oracle   oinstall 639639552 Jun 17 07:25 sysaux01.dbf
-rw-r-----   1 oracle   oinstall 83894272 Jun 17 07:25 undotbs01.dbf
-rw-r-----   1 oracle   oinstall 1340874752 Jun 17 07:25 example01.dbf
-rw-r-----   1 oracle   oinstall 5251072 Jun 17 07:25 users01.dbf
oracle@solaris112:/u01/app/oracle/oradata/orcl2$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jun 17 07:30:20 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1258291200 bytes
Fixed Size                  3003176 bytes
Variable Size             838864088 bytes
Database Buffers          402653184 bytes
Redo Buffers               13770752 bytes
SQL> set echo on
SQL> @/tmp/recreate_control.sql
SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL2" RESETLOGS  NOARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 '/u01/app/oracle/oradata/orcl2/redo01.log'  SIZE 50M BLOCKSIZE 512,
  9    GROUP 2 '/u01/app/oracle/oradata/orcl2/redo02.log'  SIZE 50M BLOCKSIZE 512,
 10    GROUP 3 '/u01/app/oracle/oradata/orcl2/redo03.log'  SIZE 50M BLOCKSIZE 512
 11  -- STANDBY LOGFILE
 12  DATAFILE
 13    '/u01/app/oracle/oradata/orcl2/system01.dbf',
 14    '/u01/app/oracle/oradata/orcl2/sysaux01.dbf',
 15    '/u01/app/oracle/oradata/orcl2/undotbs01.dbf',
 16    '/u01/app/oracle/oradata/orcl2/example01.dbf',
 17    '/u01/app/oracle/oradata/orcl2/users01.dbf'
 18  CHARACTER SET WE8MSWIN1252
 19  ;

Control file created.

SQL>
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01100: database already mounted


SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
oracle@solaris112:/u01/app/oracle/oradata/orcl2$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Wed Jun 17 07:32:31 2015

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

connected to target database: ORCL2 (DBID=846932035, not open)

RMAN> alter database open resetlogs;

using target database control file instead of recovery catalog
Statement processed

RMAN> exit
Recovery Manager complete.

racle@solaris112:/u01/app/oracle/oradata/orcl2$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jun 17 07:33:19 2015

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> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE