How to Recover Oracle Database using RMAN(Recovery Manager)



Oracle Data Base Recovery Using Recovery Manager(RMAN)

Recovery manager is an important & useful utility for coordinating our database and restoration procedures across the multiple servers.As per my experience it is very useful to recover or restore the whole database using the RMAN utility.
The functionality of RMAN is too diverse to be covered in this article so I will be focus on the basic backup and recovery functionality.
1.            Create Recovery Catalog
2.            Register Database
3.            Cold Backup(Offline Databse)
4.            Hot Backup(Online Database)
5.            Restore & Recover the Complete Database
6.            Restore & Recover a subpart of the Database
7.            Incomplete Recovery


Create Recovery Catalog
First create a user to hold the recovery catalog:
CONNECT sys/password@DBASE

 Create tablepsace to hold repository
CREATE TABLESPACE "TOOLS"
DATAFILE 'E:\ORACLE\ORADATA\DDBA1\TOOLS01.DBF' SIZE 10M
AUTOEXTEND ON NEXT 1024K
EXTENT MANAGEMENT LOCAL;

 Create rman schema owner
CREATE USER rman IDENTIFIED BY rman
TEMPORARY TABLESPACE temp
DEFAULT TABLESPACE tools
QUOTA UNLIMITED ON tools;

GRANT connect, resource, recovery_catalog_owner TO rman;
Then create the recovery catalog:
c:> rman catalog rman/rman@Dbase

Recovery Manager: Release 8.1.7.0.0 - Production

RMAN-06008: connected to recovery catalog database
RMAN-06428: recovery catalog is not installed

RMAN> create catalog tablespace tools;

RMAN-06431: recovery catalog created

RMAN> exit


Recovery Manager complete.

C:>
Register Database
Each database to be backed up by RMAN must be registered:
C:>rman target sys/password@Dbase rcvcat rman/rman@dba1 msglog 'C:OracleBackupDBASEDBASE_Daily_Backup.log'

Recovery Manager: Release 8.1.7.0.0 - Production

RMAN-06005: connected to target database: DBASE (DBID=955315395)
RMAN-06008: connected to recovery catalog database

RMAN> register database;

RMAN-03022: compiling command: register
RMAN-03023: executing command: register
RMAN-08006: database registered in recovery catalog
RMAN-03023: executing command: full resync
RMAN-08002: starting full resync of recovery catalog
RMAN-08004: full resync complete

RMAN>
Existing user-created backups can be added to the catalog using:
catalog datafilecopy 'D:\Oracle\Oradata\mkr1.dbf';
catalog archivelog 'log1', 'log2', 'log3', ... 'logN';
Cold Backup
This RMAN script starts by doing a a clean mount of the database. It then backs up the datafiles, controlfile and archivelogs, with old archive logs deleted in the process. Finally the database is opened.
replace script 'DBASE_daily_backup' {

  * make sure database is shutdown cleanly
  shutdown immediate;
  startup force dba pfile=D:\Oracle\Admin\DBASE\pfile\init.ora;
  shutdown immediate;
 
 
  *Mount the database and start backup
  startup mount pfile=D:\Oracle\Admin\DBASE\pfile\init.ora;

 
  * Backup datafile, controlfile and archivelogs
  allocate channel ch1 type
    disk format 'D:\Oracle\Backup\DBASE\%d_DB_%u_%s_%p';
  backup database include current controlfile
    tag = 'DBASE_daily_backup';
  release channel ch1;
 
  * Open the database
  alter database open;


  * Archive all logfiles including current
  sql 'ALTER SYSTEM ARCHIVE LOG CURRENT';


  * Backup outdated archlogs and delete them
  allocate channel ch1 type
    disk format 'D:\Oracle\Backup\DBASE\%d_ARCH_%u_%s_%p';
  backup archivelog
    until time 'Sysdate-2' all
    delete input;
  release channel ch1;

 
  * Backup remaining archlogs
  allocate channel ch1 type
    disk format 'D:\Oracle\Backup\DBASE\%d_ARCH_%u_%s_%p';
  backup archivelog all;
  release channel ch1;
 
}
The file can be loaded as a stored script and run using the following commands:
RMAN> @D:\Oracle\Backup\DBASE_daily_backup.txt
RMAN> run {execute script DBASE_daily_backup; }
The RMAN output can be a bit unnerving at first. You should expect something like:
RMAN> run {execute script DBASE_daily_backup; }
RMAN-03021: executing script: DBASE_daily_backup

RMAN-03022: compiling command: shutdown
RMAN-06405: database closed
RMAN-06404: database dismounted
RMAN-06402: Oracle instance shut down

RMAN-03022: compiling command: startup
RMAN-06196: Oracle instance started
RMAN-06199: database mounted
RMAN-06400: database opened

Total System Global Area      13375516 bytes

Fixed Size                       75804 bytes
Variable Size                 12402688 bytes
Database Buffers                819200 bytes
Redo Buffers                     77824 bytes

RMAN-03022: compiling command: shutdown
RMAN-06405: database closed
RMAN-06404: database dismounted
RMAN-06402: Oracle instance shut down

RMAN-03022: compiling command: startup
RMAN-06193: connected to target database (not started)
RMAN-06196: Oracle instance started
RMAN-06199: database mounted

Total System Global Area      13375516 bytes

Fixed Size                       75804 bytes
Variable Size                 12402688 bytes
Database Buffers                819200 bytes
Redo Buffers                     77824 bytes

RMAN-03022: compiling command: allocate
RMAN-03023: executing command: allocate
RMAN-08030: allocated channel: ch1
RMAN-08500: channel ch1: sid=14 devtype=DISK

RMAN-03022: compiling command: backup
RMAN-03023: executing command: backup
RMAN-08008: channel ch1: starting full datafile backupset
RMAN-08502: set_count=51 set_stamp=437320255 creation_time=09-AUG-01
RMAN-08010: channel ch1: specifying datafile(s) in backupset
RMAN-08522: input datafile fno=00001 name=C:\ORACLE\ORADATA\DBASE\SYSTEM01.DBF
RMAN-08011: including current controlfile in backupset
RMAN-08522: input datafile fno=00009 name=C:\ORACLE\ORADATA\DBASE\DES601.DBF
RMAN-08522: input datafile fno=00002 name=C:\ORACLE\ORADATA\DBASE\RBS01.DBF
RMAN-08522: input datafile fno=00008 name=C:\ORACLE\ORADATA\DBASE\OEM_REPOSITORY.ORA
RMAN-08522: input datafile fno=00003 name=C:\ORACLE\ORADATA\DBASE\USERS01.DBF
RMAN-08522: input datafile fno=00004 name=C:\ORACLE\ORADATA\DBASE\TEMP01.DBF
RMAN-08522: input datafile fno=00006 name=C:\ORACLE\ORADATA\DBASE\INDX01.DBF
RMAN-08522: input datafile fno=00007 name=C:\ORACLE\ORADATA\DBASE\DR01.DBF
RMAN-08522: input datafile fno=00005 name=C:\ORACLE\ORADATA\DBASE\TOOLS01.DBF
RMAN-08013: channel ch1: piece 1 created
RMAN-08503: piece handle=C:\ORACLE\BACKUP\DBASE\DBASE_DB_1JD11UHV_51_1 comment=NONE
RMAN-08525: backup set complete, elapsed time: 00:05:52
RMAN-03023: executing command: partial resync
RMAN-08003: starting partial resync of recovery catalog
RMAN-08005: partial resync complete

RMAN-03022: compiling command: release
RMAN-03023: executing command: release
RMAN-08031: released channel: ch1

RMAN-03022: compiling command: alter db
RMAN-06400: database opened

RMAN-03022: compiling command: sql
RMAN-06162: sql statement: ALTER SYSTEM ARCHIVE LOG CURRENT
RMAN-03023: executing command: sql

RMAN-03022: compiling command: allocate
RMAN-03023: executing command: allocate
RMAN-08030: allocated channel: ch1
RMAN-08500: channel ch1: sid=14 devtype=DISK

RMAN-03022: compiling command: backup
RMAN-03025: performing implicit partial resync of recovery catalog
RMAN-03023: executing command: partial resync
RMAN-08003: starting partial resync of recovery catalog
RMAN-08005: partial resync complete
RMAN-03023: executing command: backup
RMAN-08009: channel ch1: starting archivelog backupset
RMAN-08502: set_count=52 set_stamp=437320626 creation_time=09-AUG-01
RMAN-08014: channel ch1: specifying archivelog(s) in backup set
RMAN-08504: input archivelog thread=1 sequence=226 recid=11 stamp=437307841
RMAN-08504: input archivelog thread=1 sequence=227 recid=12 stamp=437309722
RMAN-08504: input archivelog thread=1 sequence=228 recid=13 stamp=437316806
RMAN-08504: input archivelog thread=1 sequence=229 recid=14 stamp=437317665
RMAN-08504: input archivelog thread=1 sequence=230 recid=15 stamp=437319111
RMAN-08504: input archivelog thread=1 sequence=231 recid=16 stamp=437320622
RMAN-08013: channel ch1: piece 1 created
RMAN-08503: piece handle=C:\ORACLE\BACKUP\DBASE\DBASE_ARCH_1KD11UTI_52_1 comment=NONE
RMAN-08525: backup set complete, elapsed time: 00:00:04
RMAN-03023: executing command: partial resync
RMAN-08003: starting partial resync of recovery catalog
RMAN-08005: partial resync complete

RMAN-03022: compiling command: release
RMAN-03023: executing command: release
RMAN-08031: released channel: ch1

RMAN> exit

Recovery Manager complete.
The recovery catalog should be resyncronized on a regular basis so that changes to the database structure and presence of new archive logs is recorded. Some commands perform partial and full resyncs implicitly, but a full resync should be scheduled at regular intervals.
resync catalog;
Hot Backup
Hot backups using RMAN are very simple. There is no need to alter the tablespace or database mode.
run {
  allocate channel ch1 type disk format 'd:\oracle\backup%d_DB_%u_%s_%p';
  backup database;
  backup archivelog all;
  release channel ch1;
}
Restore & Recover The Whole Database
Recovering from a media failure is as simple as:
run {
    startup mount pfile=c:\Oracle\Admin\DBASE\pfile\init.ora;

    allocate channel ch1 type disk;

    restore database;
    recover database;
 
    release channel ch1;
}
This will result in all datafiles being restored then recovered. RMAN will apply archive logs as necessary until the recovery is complete. The sort of results you should expect are:
Recovery Manager: Release 8.1.7.0.0 - Production

RMAN-06005: connected to target database: DBASE (DBID=9837465659)
RMAN-06008: connected to recovery catalog database

RMAN> run {
2>     startup mount pfile=c:\Oracle\Admin\DBASE\pfile\init.ora;
3>
4>     allocate channel ch1 type disk;
5>
6>     restore database;
7>     recover database;
8>  
9>     release channel ch1;
10>}

RMAN-03022: compiling command: startup
RMAN-06193: connected to target database (not started)
RMAN-06196: Oracle instance started
RMAN-06199: database mounted

Total System Global Area      13375516 bytes

Fixed Size                       75804 bytes
Variable Size                 12402688 bytes
Database Buffers                819200 bytes
Redo Buffers                     77824 bytes

RMAN-03022: compiling command: allocate
RMAN-03023: executing command: allocate
RMAN-08030: allocated channel: ch1
RMAN-08500: channel ch1: sid=14 devtype=DISK

RMAN-03022: compiling command: restore

RMAN-03022: compiling command: IRESTORE
RMAN-03023: executing command: IRESTORE
RMAN-08016: channel ch1: starting datafile backupset restore
RMAN-08502: set_count=51 set_stamp=437320255 creation_time=09-AUG-01
RMAN-08089: channel ch1: specifying datafile(s) to restore from backup set
RMAN-08523: restoring datafile 00001 to C:\ORACLE\ORADATA\DBASE\SYSTEM01.DBF
RMAN-08523: restoring datafile 00002 to C:\ORACLE\ORADATA\DBASE\RBS01.DBF
RMAN-08523: restoring datafile 00003 to C:\ORACLE\ORADATA\DBASE\USERS01.DBF
RMAN-08523: restoring datafile 00004 to C:\ORACLE\ORADATA\DBASE\TEMP01.DBF
RMAN-08523: restoring datafile 00005 to C:\ORACLE\ORADATA\DBASE\TOOLS01.DBF
RMAN-08523: restoring datafile 00006 to C:\ORACLE\ORADATA\DBASE\INDX01.DBF
RMAN-08523: restoring datafile 00007 to C:\ORACLE\ORADATA\DBASE\DR01.DBF
RMAN-08523: restoring datafile 00008 to C:\ORACLE\ORADATA\DBASE\OEM_REPOSITORY.ORA
RMAN-08523: restoring datafile 00009 to C:\ORACLE\ORADATA\DBASE\DES601.DBF
RMAN-08023: channel ch1: restored backup piece 1
RMAN-08511: piece handle=C:\ORACLE\BACKUP\DBASE\DBASE_DB_1JD11UHV_51_1 tag=DBASE_DAILY_BACKUP params=NULL
RMAN-08024: channel ch1: restore complete
RMAN-03023: executing command: partial resync
RMAN-08003: starting partial resync of recovery catalog
RMAN-08005: partial resync complete

RMAN-03022: compiling command: recover

RMAN-03022: compiling command: recover(1)

RMAN-03022: compiling command: recover(2)

RMAN-03022: compiling command: recover(3)
RMAN-03023: executing command: recover(3)
RMAN-08054: starting media recovery
RMAN-08055: media recovery complete

RMAN-03022: compiling command: recover(4)

RMAN-03022: compiling command: release
RMAN-03023: executing command: release
RMAN-08031: released channel: ch1

RMAN> exit

Recovery Manager complete.
Once this process us complete the database can be opened using the ALTER DATABASE OPEN; command.

Restore & Recover A Subset Of The Database
A subset of the database can be restored in a similar fashion:
run {
    sql 'ALTER TABLESPACE users OFFLINE IMMEDIATE';
    restore tablespace users;
    recover tablespace users;
    sql 'ALTER TABLESPACE users ONLINE';
}
Incomplete Recovery
As you would expect, RMAN allows incomplete recovery to a specified time, SCN or sequence number:
run
{
  set until time 'Dec 31 2011 04:00:00';
  * set until scn 1000;       * alternatively, you can specify SCN
  * set until sequence 9923;  * alternatively, you can specify log sequence number
  restore database;
  recover database;
}

Alter database open resetlogs;
The incomplete recovery requires the database to be opened using the RESETLOGS option.



Comments

Post a Comment

Popular posts from this blog

How to Bind Multiple SMPP Acounts Using Kannel SMS Gateway.

Hi guys,              Below I have written the script of complete kannel configuration in which I have binded the multiple SMPP accounts with mysql database. Please change your configuration according to your requirement. It is 100 % worked for me and hope it will work for you also. # CORE # # There is only one core group and it sets all basic settings # of the bearerbox (and system). You should take extra notes on # configuration variables like 'store-file' (or 'store-dir'), # 'admin-allow-ip' and 'access.log' group = core admin-port = 13000 smsbox-port = 13001 admin-password = bar dlr-storage = mysql log-file = "/var/log/kannel/kannel.log" access-log = "/var/log/kannel/bearerbox-access.log" access-log-clean = true log-level = 0 box-deny-ip = "*.*.*.*" box-allow-ip = "127.0.0.1" # SMSC CONNECTIONS # # SMSC connections are created in bearerbox and they handle SMSC specific # protocol and m...
Read more

Window could not start the Apache tomcat 6 on Local Computer.For more information, review the System Events Logs.If this is a non-Microsoft service,contact service vendor, and refer to service-specific error code 0.

Image
Hi Guys,               Several times we have faced the problem with Apche Tomcat 6 service, It seems fail to start the service of Tomcat Apache and gettting error code 0 even its not work on installation again below is the snapshot of error. So I am writing technique how can we start the Tomcat Apache Services , Just copy one file & paste into Tomcat Apache bin folder please follow the some steps. Follow the below mentioned steps:  1. Copy the file(msvcr71.dll) from location  C:\Program Files\Java\jdk1.6.0_02\bin\  2. Paste the (msvcr71.dll) file into location      C:\Program Files\Apache Software Foundation\Tomcat6.0\bin  3.  After paste start the tomcat service and it will be started and works.  4.  For testing the apache hit the browser with http://localhost:8080/ it display the index page of Apache tomcat it means apache is working fine. ...
Read more

ORACLE DBA INTERVIEW QUESTION & ANSWERS

ORACLE DBA INTERVIEW QUESTION & ANSWERS 1. Which of the following does not affect the size of the SGA. a) Database buffer b) Redo log buffer c) Stored procedure d) Shared pool Answer: c 2. A set of Dictionary tables are created a) Once for the Entire Database b) Every time a user is created c) Every time a Tablespace is created d) None of the above Answer: a 3. The order in which Oracle processes a single SQL statement is ? A. execute, parse, fetch B. execute, fetch, parse C. parse, execute, fetch D. parse, fetch, execute Answer: c 4. What are the mandatory datafiles to create a database in Oracle 11g? A. SYSTEM, SYSAUX B. SYSTEM, USERDATA, TEMP C. SYSTEM, SYSAUX, UNDO Answer: c 5. In one server can we have different oracle versions? A. No B. Yes Answer: b 6. How do sessions communicate with database? A. Server processes use oracle net to connect to the instance. B. Background processes use oracle n...
Read more