DATABASE RECOVERY AFTER CRASHING USING CONTROL FILE

Preapare .bash_profile

STEP 1: (This step is before crash of  DB1) On the DB1 Database , go into SQL*Plus, sign on as SYSDBA and issue: “alter database backup controlfile to trace”. This will put the create database syntax in the trace file directory. The trace keyword tells oracle to generate a script containing a create controlfile command and store it in the trace directory identified in the user_dump_dest parameter of the init.ora file. It will look something like this:

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "BCBLDB" RESETLOGS  NOARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 '/u01/oradata/bcbldb/redo01.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 2 '/u01/oradata/bcbldb/redo02.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 3 '/u01/oradata/bcbldb/redo03.log'  SIZE 50M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

  '/u01/oradata/bcbldb/system01.dbf',

  '/u01/oradata/bcbldb/sysaux01.dbf',

  '/u01/oradata/bcbldb/undotbs01.dbf',

  '/u01/oradata/bcbldb/users01.dbf',

  '/u01/oradata/bcbldb/tbacnts.dbf',

  '/u01/oradata/bcbldb/trbfes.dbf',

  '/u01/oradata/bcbldb/tbfes.dbf',

  '/u01/oradata/bcbldb/tbaml.dbf',

  '/u01/oradata/bcbldb/tbstran.dbf',

  '/u01/oradata/bcbldb/data.dbf'

CHARACTER SET WE8MSWIN1252;

-- 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/fast_recovery_area/BCBLDB/archivelog/2013_07_15/o1_mf_1_1_%u_.arc';

-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/fast_recovery_area/BCBLDB/archivelog/2013_07_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 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/oradata/bcbldb/temp01.dbf'

  -   SIZE 27262976  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

-- End of tempfile additions.

# Recovery is required if any of the datafiles are restored
# backups, or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# Database can now be opened normally.
ALTER DATABASE OPEN;

STEP 2: Shutdown the DB1 database (In our scenario, after crash this will automatically shut down)

STEP 3: Copy all data files & Redolog files into the new directories on the new server (10.0.200.51). You may change the file names if you want, but you must edit the controlfile to reflect the new data files names on the new server.

rcp /u01/oradata/bcbldb/* 10.0.200.51:/u01/oradata/bcbldb

STEP 4: Copy and Edit the Control file – Using the output syntax from STEP 1, modify the controlfile creation script by changing the following:

DB1:

CREATE CONTROLFILE REUSE DATABASE "bcbldb" NORESETLOGS

DB2:

CREATE CONTROLFILE SET DATABASE "bcbldb" RESETLOGS

STEP 5: Remove the “recover database” and “alter database open resetlogs” syntax

# Recovery is required if any of the datafiles are restored
# backups, or if the last shutdown was not normal or immediate.
#RECOVER DATABASE
# Database can now be opened normally.
ALTER DATABASE OPEN RESETLOGS;

STEP 6: Re-names of the data files names that have changed.

Save as db_create_controlfile.sql.

DB1:

DATAFILE
  '/u01/oradata/bcbldb/system01.dbf',

  '/u01/oradata/bcbldb/sysaux01.dbf',

  '/u01/oradata/bcbldb/undotbs01.dbf',

  '/u01/oradata/bcbldb/users01.dbf',

  '/u01/oradata/bcbldb/tbacnts.dbf',

  '/u01/oradata/bcbldb/trbfes.dbf',

  '/u01/oradata/bcbldb/tbfes.dbf',

  '/u01/oradata/bcbldb/tbaml.dbf',

  '/u01/oradata/bcbldb/tbstran.dbf',

  '/u01/oradata/bcbldb/data.dbf'

DB2:

DATAFILE
  '/u01/oradata/bcbldb/system01.dbf',

  '/u01/oradata/bcbldb/sysaux01.dbf',

  '/u01/oradata/bcbldb/undotbs01.dbf',

  '/u01/oradata/bcbldb/users01.dbf',

  '/u01/oradata/bcbldb/tbacnts.dbf',

  '/u01/oradata/bcbldb/trbfes.dbf',

  '/u01/oradata/bcbldb/tbfes.dbf',

  '/u01/oradata/bcbldb/tbaml.dbf',

  '/u01/oradata/bcbldb/tbstran.dbf',

  '/u01/oradata/bcbldb/data.dbf'

STEP 7: Create the adump, FRA

[oracle@localhost ~]$ su - root

Password:

[root@localhost ~]# mkdir  /u01/app/oracle/fast_recovery_area/bcbldb/

[root@localhost ~]# mkdir  /u01/app/oracle/admin/bcbldb/adump/

[root@localhost ~]# chown -R oracle:oinstall /u01/app/oracle/fast_recovery_area/bcbldb/

[root@localhost ~]# chmod -R 0775 chown -R /u01/app/oracle/fast_recovery_area/bcbldb/

[root@localhost ~]# chown -R oracle:dba /u01/app/oracle/fast_recovery_area/bcbldb/

[root@localhost ~]# chmod -R 0775 /u01/app/oracle/fast_recovery_area/bcbldb/

[root@localhost ~]# chown -R oracle:oinstall /u01/app/oracle/admin/bcbldb/adump/

[root@localhost ~]# chown -R oracle:dba /u01/app/oracle/admin/bcbldb/adump/

[root@localhost ~]# chmod -R 0775 /u01/app/oracle/admin/bcbldb/adump/

[root@localhost ~]# chown -R oracle:oinstall /u01/app/ora

oracle/       oraInventory/

[root@localhost ~]# chown -R oracle:oinstall /u01/oradata/bcbldb/

[root@localhost ~]# chown -R oracle:dba /u01/oradata/bcbldb/

[root@localhost ~]# chmod -R 0775 /u01/oradata/bcbldb/

STEP 8: Copy-over the old init.ora file

rcp  /u01/app/oracle/product/11.2.0/db_1/dbs/initbcbldb.ora

                  DB2host /u01/app/oracle/product/11.2.0/db_1/dbs/initbcbldb.ora

STEP 9:  Now, start up the database(DB2) in no mount stage with this pfile.

SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initbcbldb.ora'   ;

STEP 10: Start the new(DB2) database

@db_create_controlfile.sql

CREATE CONTROLFILE SET DATABASE "bcbldb" RESETLOGS  NOARCHIVELOG   2      MAXLOGFILES 16   3      MAXLOGMEMBERS 3   4      MAXDATAFILES 100   5      MAXINSTANCES 8   6      MAXLOGHISTORY 292   7  LOGFILE   GROUP 1 '/u01/oradata/bcbldb/redo01.log'  SIZE 50M BLOCKSIZE 512,   8    9    GROUP 2 '/u01/oradata/bcbldb/redo02.log'  SIZE 50M BLOCKSIZE 512,  10    GROUP 3 '/u01/oradata/bcbldb/redo03.log'  SIZE 50M BLOCKSIZE 512  11  DATAFILE  12    '/u01/oradata/bcbldb/system01.dbf',  13    '/u01/oradata/bcbldb/sysaux01.dbf',  14    '/u01/oradata/bcbldb/undotbs01.dbf',  15    '/u01/oradata/bcbldb/users01.dbf',  16    '/u01/oradata/bcbldb/tbacnts.dbf',   '/u01/oradata/bcbldb/trbfes.dbf',  17   18    '/u01/oradata/bcbldb/tbfes.dbf',  19    '/u01/oradata/bcbldb/tbaml.dbf',  20    '/u01/oradata/bcbldb/tbstran.dbf',  21    '/u01/oradata/bcbldb/data.dbf'  22  CHARACTER SET WE8MSWIN1252;

New Control file create..

STEP 10: 

SQL> alter database open resetlogs;

STEP 10: Place the new(DB2)  database in archivelog mode