Recover Oracle database


Recover Oracle database:

This posting describes the steps to recover Oracle database at disaster recovery site in case of major disaster in the data center.  The disaster recovery plan assumes a regular backup of oracle database and it is control file done, also the production server is windows server. If there is a major disaster of the Oracle database server locate the hardware replacement of the server and the latest Oracle backup set which have at least one full backup, Install windows, Install Oracle 10g, restore the latest control to generate a SQL trace which will be used to restore the database using RMAN. the reason to create SQL trace from the control file is the ability to edit the file and change the path of oracle data file since the original path might be different for the path in the new server and use it to restore the database.

1. Build Windows 2003 Server 64bit

1.1        The server should have 16GB of RAM and 4 Xeon CPU at 3.0GHZ

1.2        Operating system Partitions

Partition Description Size
C:\ OS and Oracle will be installed in this partition 30 GB
E:\ Oracle data files will be stored in this partition 300GB

1.3        Install Windows 2003 64bit with sp2

1.4        Change the machine Name

2. Install Oracle Standard Edition 64bit

2.1        Install Oracle 1og

    • Login to Company-ifsdb1 with admin rights
    • Download Oracle Standard Edition installation files from Oracle website
    • Start the setup
    • Oracle universal installer will show up
    • Set installation type to Standard Edition
    • Click Next as in Fig. 1

Fig. 1

  • Verify all the checks completed successfully and Click Next
  • Summary page will show up
  • Click Install and wait, the installer might take 45 minutes
  • The installer will install the database engine
  • End of the installation page will show up
  • Click Exit >> Click Yes
  • Verify the install completed successfully without errors

2.2        Install Patch Set 3 (6810189)

  • Download patch set 3 from Oracle website to folder p6810189_10204_MSWIN-x86-64
  • Navigate to Disk1 Folder and Double click setup
  • Once the welcome screen Click Next
  • Click Next as in Fig. 2

Fig. 2

  • Click Install
  • Click Exit and Click Yes
  • Restart the machine

2.3        Apply Bundle Patch(10.2.0.4 Patch 35) 9481771 to DB home

  • Download the patch from Oracle websie then Apply it

Start>>Run>>cmd
Set ORACLE_HOME= C:\oracle\product\10.2.0\db_1
Set path= C:\oracle\product\10.2.0\db_1\OPatch
C:\disks\9481771>opatch apply

  • For more details see readme file

2.4        Create new oracle database

    • Start>>All Programs>>Oracle – OraDb10g_home>>Configuration and Migration Tools>>Database Configuration Assistant
    • Welcome screen will show up and Click Next
    • Select Create database and Click Next
    • Select general database purpose and Click Next
    • Set the database name as in Fig. 3

Fig. 3

  • Click Next
  • Leave the default setting and Click Next
  • Set the password as in Fig. 4

 Fig. 4

  • Leave the default settings and Click next
  • Set the database files path as in Fig. 5

 Fig. 5

  • Set recovery options for the database as in Fig. 6

Fig.6

  • Leave the default settings and Click next
  • Leave the default settings and Click next
  • Leave the default settings and Click next
  • Leave the default settings and Click next
  • Leave the default settings and Click Finish
  • Leave the default settings and Click OK
  • The database creation might take 45 minutes
  • Click Exit

2.5        Verify connectivity to the database server from outside  

3. Oracle database Recovery Steps

3.1        Restore backup folder from backup system to the new server, you should restore the latest full and differential backup for the folder to E:\orabkp\DatabaseName  

3.2        Restore the control file

  • Navigate to the backup folder at  E:\orabkp\DatabaseName and sort the files by date as in Fig. 6 

Fig. 6

  • Locate the latest control file; the file format will be like this C-DBID-Date-XX where DBID is the database id number. in Fig. 6 the latest control file was C-425570525-20080515-0E
  • Restore the control file using command line

Start>>Run>>cmd
C:\>rman target/ nocatalog
Recovery Manager: Release 10.2.0.3.0 – Production on Fri May 16 15:42:13 2008
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
connected to target database (not started)
RMAN> startup force nomount;
RMAN> restore controlfile from ‘E:\oradbkp\DatabaseName\C-425570525-20080515-0E’;
Starting restore at 16-MAY-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
output filename=E:\ORADATA\DATABASENAME\CONTROL01.CTL
output filename=E:\ORADATA\DATABASENAME\CONTROL02.CTL
output filename=E:\ORADATA\DATABASENAME\CONTROL03.CTL
Finished restore at 16-MAY-08
RMAN> alter database mount;

3.3        Create Oracle database restore script

      • Get the database structure from the control file

 C:\>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 – Production on Fri May 16 16:22:52 2008
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
SQL> startup force mount
SQL> alter database backup control file to trace as ‘E:\DatabaseNametrace.sql’;

      • Generate restore script

Open the file at E:\DatabaseNametrace.sql , this file will have the database file structure, datafiles and logfile. You need to change the path for all the data files in the restore script to reflect the new path. You can use copy/paste/replace in notepad to generate the restore script

      • Restore script template

Please note the script is just a template, in my case I have to change the path of the data file since production server were using ASM and recovery server use File system to store the data files and your database will be different.  Anyways verify the database structure using E:\DatabaseNametrace.sql file:

RMAN> Run {
  set newname for datafile '+DB_GROUP/DatabaseName/datafile/system01.dbf' to 'E:\oradata\DATABASENAME\system01.dbf';
  set newname for datafile '+DB_GROUP/DatabaseName/datafile/undotbs1_01.dbf'  to 'E:\oradata\DATABASENAME\undotbs1_01.dbf';
  set newname for datafile '+DB_GROUP/DatabaseName/datafile/sysaux01.dbf'  to 'E:\oradata\DATABASENAME\sysaux01.dbf';
  set newname for datafile '+DB_GROUP/DatabaseName/datafile/undotbs2_01.dbf'  to 'E:\oradata\DATABASENAME\undotbs2_01.dbf';
  set newname for datafile '+DB_GROUP/DatabaseName/datafile/users01.dbf'  to 'E:\oradata\DATABASENAME\users01.dbf';
  set newname for datafile '+DB_GROUP/DatabaseName/datafile/ial_data.dbf'  to 'E:\oradata\DATABASENAME\ial_data.dbf';
  set newname for datafile '+DB_GROUP/DatabaseName/datafile/ial_index.dbf'  to 'E:\oradata\DATABASENAME\ial_index.dbf';
  set newname for datafile '+DB_GROUP/DatabaseName/datafile/ifsapp_archive_data.dbf'  to 'E:\oradata\DATABASENAME\ifsapp_archive_data.dbf';
  set newname for datafile '+DB_GROUP/DatabaseName/datafile/ifsapp_archive_index.dbf'  to 'E:\oradata\DATABASENAME\ifsapp_archive_index.dbf';
  set newname for datafile '+DB_GROUP/DatabaseName/datafile/ifsapp_data.dbf'  to 'E:\oradata\DATABASENAME\ifsapp_data.dbf';
  set newname for datafile '+DB_GROUP/DatabaseName/datafile/ifsapp_data02.dbf'  to 'E:\oradata\DATABASENAME\ifsapp_data02.dbf';
  set newname for datafile '+DB_GROUP/DatabaseName/datafile/ifsapp_data03.dbf'  to 'E:\oradata\DATABASENAME\ifsapp_data03.dbf';
  set newname for datafile '+DB_GROUP/DatabaseName/datafile/ifsapp_index.dbf'  to 'E:\oradata\DATABASENAME\ifsapp_index.dbf';
  set newname for datafile '+DB_GROUP/DatabaseName/datafile/ifsapp_index02.dbf'  to 'E:\oradata\DATABASENAME\ifsapp_index02.dbf';
  set newname for datafile '+DB_GROUP/DatabaseName/datafile/ifsapp_index03.dbf'  to 'E:\oradata\DATABASENAME\ifsapp_index03.dbf';
  set newname for datafile '+DB_GROUP/DatabaseName/datafile/ifsapp_lob.dbf'  to 'E:\oradata\DATABASENAME\ifsapp_lob.dbf';
  set newname for datafile '+DB_GROUP/DatabaseName/datafile/ifsapp_report_data.dbf'  to 'E:\oradata\DATABASENAME\ifsapp_report_data.dbf';
  set newname for datafile '+DB_GROUP/DatabaseName/datafile/ifsapp_report_index.dbf'  to 'E:\oradata\DATABASENAME\ifsapp_report_index.dbf';
  set newname for datafile '+DB_GROUP/DatabaseName/datafile/ifsapp_data04.dbf'  to 'E:\oradata\DATABASENAME\ifsapp_data04.dbf';
  set newname for datafile '+DB_GROUP/DatabaseName/datafile/ifsapp_index04.dbf'  to 'E:\oradata\DATABASENAME\ifsapp_index04.dbf';
  set newname for datafile '+DB_GROUP/DatabaseName/datafile/migration_data.dbf'  to 'E:\oradata\DATABASENAME\migration_data.dbf';
  set newname for datafile '+DB_GROUP/DatabaseName/datafile/migration_index.dbf'  to 'E:\oradata\DATABASENAME\migration_index.dbf';
  set newname for datafile '+DB_GROUP/DatabaseName/datafile/radley.dbf'to 'E:\oradata\DATABASENAME\radley.dbf';
restore database;switch datafile all;recover database;}  

Finalize the restore script then run it RMAN:
C:\>rman target sys/DbPassword@Oracledbname
Recovery Manager: Release 10.2.0.3.0 – Production on Fri Apr 18 17:27:51 2008
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
connected to target database: LINPROD (DBID=425570525)
RMAN>startup force mount;
RMAN>Paste the restore script generate in the earlier step 

Please note that the restore might long time based on of the size of the database you are restoring

You might get warning message like the one below so don’t worry about it since it will be fixed by renaming the redologs

starting media recovery
unable to find archive log
archive log thread=1 sequence=13550
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/04/2010 08:32:30
RMAN-06054: media recovery requesting unknown log: thread 1 seq 13550 lowscn 102
8415872

3.4        Rename the online redolog files before opening the database

C:\>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 – Production on Fri May 16 16:22:52 2008
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
 SQL>  alter database rename file ‘+DB_GROUP/DatabaseName/redo01.log’ to ‘E:\oradata\DATABASENAME\redo01.log’;
 alter database rename file ‘+DB_GROUP/DatabaseName/redo02.log’ to ‘E:\oradata\DATABASENAME\redo02.log’;
 alter database rename file ‘+DB_GROUP/DatabaseName/redo03.log’ to ‘E:\oradata\DATABASENAME\redo03.log’;
 alter database rename file ‘+DB_GROUP/DatabaseName/redo04.log’ to ‘E:\oradata\DATABASENAME\redo04.log’;
 alter database rename file ‘+DB_GROUP/DatabaseName/redo05.log’ to ‘E:\oradata\DATABASENAME\redo05.log’;
 alter database rename file ‘+DB_GROUP/DatabaseName/redo06.log’ to ‘E:\oradata\DATABASENAME\redo06.log’;
 alter database rename file ‘+DB_GROUP/DatabaseName/redo07.log’ to ‘E:\oradata\DATABASENAME\redo07.log’;
 alter database rename file  ‘+DB_GROUP/DatabaseName/redo08.log’ to ‘E:\oradata\DATABASENAME\redo08.log’;
SQL>  alter database open resetlogs

Advertisements

About Tarek

I have been working in IT for years and I thought it might be good to share my experience using real life scenarios.
This entry was posted in Oracle Database and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s