Oracle DBA, a practical approach !!: 2012

RMAN - Restore database in alternate/ secondary location using image copy backups

Objective:

To list the restore procedure in the event of complete loss of database files.

Assumption is that all database files (control file, datafiles, online redo logs, parameter file, password file) are lost in the primary location where the database is running.

Task is to restore and recover the database in a new/alternate/secondary location.

Assumption is that usable RMAN backups are available.

In the exercise, I have used image copy backups of datafiles(from netapp snap backup) to avoid the time delay in restoring the datafiles.

Environment:

RACPROD is a RAC database comprising of 3 instances running on servers’ rachost04/05/10. It is an 11.2.0.2 database.

Files needed to restore/ recover and open the database

Parameter file

Password file - for password file authentication. Here we will be using OS authentication.

Control files

Data files

Online redo log files

Archived Logs

Testing Restore/Recovery:

Decide the alternate location/server. Make sure necessary storage is available. Make sure the backups are accessible.

Restoring Parameter File from Control File autobackup

temphost | RACPROD | /export/oracle

> export ORACLE_SID=RACPROD

temphost | RACPROD | /export/oracle

> export PATH=$ORACLE_HOME/bin:$PATH

temphost | RACPROD | /export/oracle

> rman nocatalog

Recovery Manager: Release 11.2.0.2.0 - Production on Wed Oct 10 15:45:44 2012

RMAN>

Obtain Database ID from control file autobackups. The highlighted part below is the DBID of the database.

temphost | orcl | /export/RACPROD/conv_oradata/RACPROD/ctl

> ls -ltr

total 165016

-rw-r----- 1 oracle dba 10911744 2012-10-18 05:03 c-2058292391-20121018-00

-rw-r----- 1 oracle dba 10911744 2012-10-18 06:02 c-2058292391-20121018-01

-rw-r----- 1 oracle dba 10911744 2012-10-18 07:13 c-2058292391-20121018-02

-rw-r----- 1 oracle dba 1228800 2012-10-19 05:04 control_file_bk_OraSnap.2012-10-19

-rw-r----- 1 oracle dba 10911744 2012-10-19 05:04 c-2058292391-20121019-00

-rw-r----- 1 oracle dba 10911744 2012-10-19 06:03 c-2058292391-20121019-01

-rw-r----- 1 oracle dba 10911744 2012-10-19 07:17 c-2058292391-20121019-02

-rw-r----- 1 oracle dba 1228800 2012-10-20 05:05 control_file_bk_OraSnap.2012-10-20

RMAN> CONNECT TARGET /

connected to target database (not started)

RMAN> SET DBID 2058292391

executing command: SET DBID

RMAN> STARTUP NOMOUNT

startup failed: ORA-01078: failure in processing system parameters

LRM-00109: could not open parameter file '/export/oracle/product/11.2.0.2/MAN11G/dbs/initRACPROD.ora'

starting Oracle instance without parameter file for retrieval of spfile

Oracle instance started

Total System Global Area 158662656 bytes

Fixed Size 2224584 bytes

Variable Size 96472632 bytes

Database Buffers 54525952 bytes

Redo Buffers 5439488 bytes

RMAN> RUN

2> {

3> ALLOCATE CHANNEL c1 DEVICE TYPE disk ;

4> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/export/RACPROD/conv_oradata/RACPROD/ctl/%F';

5> RESTORE SPFILE

6> TO PFILE '/export/oracle/product/11.2.0.2/MAN11G/dbs/initRACPROD.ora'

7> FROM AUTOBACKUP;

8> SHUTDOWN ABORT;

9> }

allocated channel: c1

channel c1: SID=67 device type=DISK

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

Starting restore at 10-OCT-12

channel c1: looking for AUTOBACKUP on day: 20121010

channel c1: looking for AUTOBACKUP on day: 20121009

channel c1: looking for AUTOBACKUP on day: 20121008

channel c1: looking for AUTOBACKUP on day: 20121007

channel c1: AUTOBACKUP found: /export/RACPROD/conv_oradata/RACPROD/ctl/c-2058292391-20121007-01

channel c1: restoring spfile from AUTOBACKUP /export/RACPROD/conv_oradata/RACPROD/ctl/c-2058292391-20121007-01

channel c1: SPFILE restore from AUTOBACKUP complete

Finished restore at 10-OCT-12

Oracle instance shut down

RMAN>

Edit the restored parameter file to make changes to reflect directories on the new server. Remove/Edit RAC based parameters like cluster_database, remote_listener, RACPROD1.thread, RACPROD2.undo_tablespace, etc. We will restore and open a single instance database and add instances later on if needed. Adding instances is not covered here. I will do that in another post.

After editing the restored parameter file

temphost | RACPROD | /export/oracle/product/11.2.0.2/MAN11G/dbs

> cat initRACPROD.ora

*.audit_file_dest='/export/oracle/diag/rdbms/DPROD/DPROD/adump'

*.cluster_database=false

*.compatible='11.2.0.2'

*.control_files='/export/RACPROD/conv_oradata/DPROD/control01.ctl','/export/RACPROD/conv_oradata/DPROD/control02.ctl','/export/RACPROD/conv_oradata/DPROD/control03.ctl'

*.db_block_size=8192

*.db_name='DPROD'

*.diagnostic_dest='/export/oracle'

*.open_cursors=300

*.parallel_max_servers=285

*.parallel_servers_target=128

*.pga_aggregate_target=400M

*.processes=300

*.remote_login_passwordfile='EXCLUSIVE'

*.sessions=335

*.sga_target=1G

*.undo_tablespace='UNDOTBS1'

*.log_archive_dest_1='LOCATION=/export/RACPROD/conv_oradata/DPROD/archive/'

*.log_archive_format=arch_%t_%s_%r.arc

Create directories as needed.

> mkdir -p /export/oracle/diag/rdbms/RACPROD/RACPROD/trace

> mkdir -p /export/oracle/diag/rdbms/RACPROD/RACPROD/alert

> mkdir -p /export/oracle/diag/rdbms/RACPROD/RACPROD/cdump

> mkdir -p /export/oracle/diag/rdbms/RACPROD/RACPROD/adump

Start an instance using the restored and edited parameter file

> sqlplus “/as sysdba”

SQL*Plus: Release 11.2.0.2.0 Production on Fri Oct 19 20:11:36 2012

Connected to an idle instance.

SQL> startup force nomount pfile='/export/oracle/product/11.2.0.2/MAN11G/dbs/initRACPROD.ora';

ORACLE instance started.

Total System Global Area 1068937216 bytes

Fixed Size 2233336 bytes

Variable Size 281021448 bytes

Database Buffers 780140544 bytes

Redo Buffers 5541888 bytes

SQL>

Restore the control file from an autobackup and then mount the database.

> rman nocatalog

Recovery Manager: Release 11.2.0.2.0 - Production on Fri Oct 19 20:16:00 2012

RMAN> SET DBID 2058292391

executing command: SET DBID

RMAN> connect target /

connected to target database: (not mounted)

using target database control file instead of recovery catalog

RMAN> RUN

2> {

3> ALLOCATE CHANNEL c1 DEVICE TYPE disk;

4> RESTORE CONTROLFILE FROM '/export/RACPROD/conv_oradata/RACPROD/ctl/c-2058292391-20121019-02';

5> ALTER DATABASE MOUNT;

6> }

allocated channel: c1

channel c1: SID=178 device type=DISK

Starting restore at 19-OCT-12

channel c1: restoring control file

channel c1: restore complete, elapsed time: 00:00:01

output file name=/export/RACPROD/conv_oradata/DPROD/control01.ctl

output file name=/export/RACPROD/conv_oradata/DPROD/control02.ctl

output file name=/export/RACPROD/conv_oradata/DPROD/control03.ctl

Finished restore at 19-OCT-12

database mounted

released channel: c1

RMAN>

Catalog the datafile backups(image copy) from the netapp snap restore.

Restore the archives to a subdirectory or directory of your choice and catalog them.

RMAN> CATALOG START WITH '/export/RACPROD/conv_oradata/DPROD/';

searching for all files that match the pattern /export/RACPROD/conv_oradata/DPROD/

List of Files Unknown to the Database

=====================================

File Name: /export/RACPROD/conv_oradata/DPROD/oltp_01.dbf

File Name: /export/RACPROD/conv_oradata/DPROD/oltpidx_01.dbf

File Name: /export/RACPROD/conv_oradata/DPROD/oltp_idx1_01.dbf

File Name: /export/RACPROD/conv_oradata/DPROD/oltp_idx2_01.dbf

File Name: /export/RACPROD/conv_oradata/DPROD/oltp_idx3_01.dbf

File Name: /export/RACPROD/conv_oradata/DPROD/oltp_idx4_01.dbf

File Name: /export/RACPROD/conv_oradata/DPROD/oltp_idx5_01.dbf

File Name: /export/RACPROD/conv_oradata/DPROD/oltp_idx6_01.dbf

File Name: /export/RACPROD/conv_oradata/DPROD/oltp_idx7_01.dbf

File Name: /export/RACPROD/conv_oradata/DPROD/oltp_idx8_01.dbf

File Name: /export/RACPROD/conv_oradata/DPROD/oltp_ts1_01.dbf

File Name: /export/RACPROD/conv_oradata/DPROD/oltp_ts2_01.dbf

File Name: /export/RACPROD/conv_oradata/DPROD/oltp_ts3_01.dbf

File Name: /export/RACPROD/conv_oradata/DPROD/oltp_ts4_01.dbf

File Name: /export/RACPROD/conv_oradata/DPROD/oltp_ts5_01.dbf

File Name: /export/RACPROD/conv_oradata/DPROD/oltp_ts6_01.dbf

File Name: /export/RACPROD/conv_oradata/DPROD/oltp_ts7_01.dbf

File Name: /export/RACPROD/conv_oradata/DPROD/oltp_ts8_01.dbf

File Name: /export/RACPROD/conv_oradata/DPROD/sysaux01.dbf

File Name: /export/RACPROD/conv_oradata/DPROD/system01.dbf

File Name: /export/RACPROD/conv_oradata/DPROD/undotbs01.dbf

File Name: /export/RACPROD/conv_oradata/DPROD/undotbs02.dbf

File Name: /export/RACPROD/conv_oradata/DPROD/undotbs03.dbf

File Name: /export/RACPROD/conv_oradata/DPROD/users01.dbf

File Name: /export/RACPROD/conv_oradata/DPROD/archive/arch_1_166_790190759.arc

File Name: /export/RACPROD/conv_oradata/DPROD/archive/arch_1_167_790190759.arc

File Name: /export/RACPROD/conv_oradata/DPROD/archive/arch_1_168_790190759.arc

File Name: /export/RACPROD/conv_oradata/DPROD/archive/arch_2_112_790190759.arc

File Name: /export/RACPROD/conv_oradata/DPROD/archive/arch_3_160_790190759.arc

Do you really want to catalog the above files (enter YES or NO)? yes

cataloging files...

cataloging done

List of Cataloged Files

=======================