Home > 11g > RMAN COLD BACKUP AND RESTORE METHOD-1

RMAN COLD BACKUP AND RESTORE METHOD-1

July 16, 2013

I have been thinking to document “RMAN COLD BACKUP AND RESTORE METHOD.”  since long time but some i could not. On 10th Dec 2011 one of my friend was in need of the same so i prepared for him and uploading the same to blog. However i have noticed that many people accessed this Article from my blog, hence today(20th Jan 2014) i thought of doing refinement like adding more details for better clarity. I hope this helps.

Assumptions:-

1) Tests done under linux.
2) DB version 11gR2.
3) Restoration happened to same server and backup is available in the same location where it was placed.(If you have different server with same folder structure also this will work).  If there is different structure, i am going to cover in different post.

Backup:-

1) Take the backup of the database using below script. Please note that “CONFIGURE CONTROLFILE AUTOBACKUP ON;”

run{
CONFIGURE CONTROLFILE AUTOBACKUP ON;
shutdown immediate;
startup mount;
backup database;
alter database open;
}

For your reference i am here with attaching the output in the below separately.

Replicate crash:-

1) delete the entire database using any of the methods. for your reference, i used “drop database command”.

Steps to restore database:-
1) Start the database with force options in nomount stage.

a) set ORACLE_SID
export ORACLE_SID=MYORCL

b) conn to DB through sqlplus.
sqlplus / nolog

c) start the db in nomount state with force option and supply init.ora as a pfile.
startup nomount pfile= force;

d) sample contents of init.ora file.

db_name=’MYORCL’
processes = 150
audit_trail =’db’
db_block_size=8192
db_domain=”
db_recovery_file_dest_size=2G
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)’
open_cursors=300
remote_login_passwordfile=’EXCLUSIVE’
undo_tablespace=’UNDOTBS1′
compatible =’11.2.0′

e) please note that while starting the DB with “nomount force” option if any erros, please solve them manually.

2) Connect to RMAN(from different session)
a) Set the DBID( you need to get the details from previous log files or alert log file etc..).
set dbid=257910304(here DB id, you can collect it from alert.log or rman backup output).
b) Restore the spfile from ;
(you need to refer to RMAN backup output. if you are not sure, you need to try with all files one by one).
restore spfile from ‘FILENAME’;
3) Restart the database with restored spfile and place db in nomount stage.
a) first shutdown the DB(Since we have started the DB with force option).
SQL> shutdown immediate;
b) startup the database
SQL> startup nomount;
c) Restore the control files from autobackup(from RMAN session).
restore controlfile from ‘FILENAME’;
4) Restart the database(bring to mount state) to mount state.
a) shutdown the db.
shutdown immediate
b) start the database in mount state.
startup mount

5) Restore the database from backup.
a) connect to DB through RMAN.
b) catalog start with ”;(This step is required only when BACKUP IS IN DIFFERENT LOCATION).
c) restore database(Please note no need to specify if you place the backup files in the same location where those taken backup).
d) exit(from RMAN session).

6) Now open the database with resetlogs.
a) connect to DB through SQLPLUS.
sqlplus / nocatalog
b) open the database with RESETLOGS.
SQL> alter database open resetlogs;

For your easiness i am here with providing the entire log which i got in my local system.

Output:-

=====================================================================
==================== 1st Step =======================
=====================================================================

$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1/dbs
$echo $ORACLE_SID
MYORCL
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 20 09:57:25 2014

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

Connected to an idle instance.

SQL> startup nomount pfile=’/u01/app/oracle/product/11.2.0/dbhome_1/dbs/init.ora’ force;
ORACLE instance started.

Total System Global Area 242208768 bytes
Fixed Size 2227176 bytes
Variable Size 184550424 bytes
Database Buffers 50331648 bytes
Redo Buffers 5099520 bytes
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
$

=======================================================================
==================== 2nd STEP. =========================
=======================================================================

$ rman target / nocatalog

Recovery Manager: Release 11.2.0.3.0 – Production on Mon Jan 20 09:58:21 2014

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

connected to target database: MYORCL (not mounted)
using target database control file instead of recovery catalog

RMAN> set dbid=257910304

executing command: SET DBID

RMAN> restore spfile from ‘/u01/app/oracle/fast_recovery_area/MYORCL/autobackup/2014_01_20/o1_mf_s_837337392_9ftflh13_.bkp’;

Starting restore at 20-JAN-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/app/oracle/fast_recovery_area/MYORCL/autobackup/2014_01_20/o1_mf_s_837337392_9ftflh13_.bkp
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 20-JAN-14

RMAN> exit

Recovery Manager complete.
$ ls -ltr
total 16
drwxr-xr-x 2 oracle oinstall 4096 Jan 20 08:29 dump
-rw-r–r– 1 oracle oinstall 2858 Jan 20 09:56 init.ora
-rw-rw—- 1 oracle oinstall 1544 Jan 20 09:58 hc_MYORCL.dat
-rw-r—– 1 oracle oinstall 2560 Jan 20 09:59 spfileMYORCL.ora
$

=======================================================================
==================== 3rd STEP. =========================
=======================================================================

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 20 10:04:42 2014

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 20 10:05:09 2014

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

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 839282688 bytes
Fixed Size 2233000 bytes
Variable Size 494931288 bytes
Database Buffers 339738624 bytes
Redo Buffers 2379776 bytes
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
$ rman target / nocatalog

Recovery Manager: Release 11.2.0.3.0 – Production on Mon Jan 20 10:08:09 2014

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

connected to target database: MYORCL (not mounted)
using target database control file instead of recovery catalog

RMAN> restore controlfile from ‘/u01/app/oracle/fast_recovery_area/MYORCL/autobackup/2014_01_20/o1_mf_s_837337392_9ftflh13_.bkp’;

Starting restore at 20-JAN-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oradata_new/MYORCL/MYORCL/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/MYORCL/control02.ctl
Finished restore at 20-JAN-14

RMAN> exit

Recovery Manager complete.
$

=======================================================================
==================== 4th STEP. =========================
=======================================================================

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 20 10:22:42 2014

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 20 10:22:54 2014

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

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 839282688 bytes
Fixed Size 2233000 bytes
Variable Size 494931288 bytes
Database Buffers 339738624 bytes
Redo Buffers 2379776 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
$

=======================================================================
==================== 5th STEP. =========================
=======================================================================

$ rman target / nocatalog

Recovery Manager: Release 11.2.0.3.0 – Production on Mon Jan 20 10:25:22 2014

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

connected to target database: MYORCL (DBID=257910304, not open)
using target database control file instead of recovery catalog

RMAN> restore database;

Starting restore at 20-JAN-14
Starting implicit crosscheck backup at 20-JAN-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
Crosschecked 2 objects
Finished implicit crosscheck backup at 20-JAN-14

Starting implicit crosscheck copy at 20-JAN-14
using channel ORA_DISK_1
Finished implicit crosscheck copy at 20-JAN-14

searching for all files in the recovery area
cataloging files…
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/MYORCL/autobackup/2014_01_20/o1_mf_s_837337392_9ftflh13_.bkp

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oradata_new/MYORCL/MYORCL/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oradata_new/MYORCL/MYORCL/undotbs01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/MYORCL/backupset/2014_01_20/o1_mf_nnndf_TAG20140120T094321_9ftfjb9j_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/MYORCL/backupset/2014_01_20/o1_mf_nnndf_TAG20140120T094321_9ftfjb9j_.bkp tag=TAG20140120T094321
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oradata_new/MYORCL/MYORCL/system01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oradata_new/MYORCL/MYORCL/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/MYORCL/backupset/2014_01_20/o1_mf_nnndf_TAG20140120T094321_9ftfjb8l_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/MYORCL/backupset/2014_01_20/o1_mf_nnndf_TAG20140120T094321_9ftfjb8l_.bkp tag=TAG20140120T094321
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 20-JAN-14

RMAN> exit

Recovery Manager complete.

=======================================================================
==================== 6th STEP. =========================
=======================================================================

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 20 10:28:58 2014

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database open resetlogs;

Database altered.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
$

=======================================================================
==================== THE END =========================
=======================================================================

Advertisements
Categories: 11g
%d bloggers like this: