In Oracle : RMAN incomplete recovery (in NOARCHIVELOG mode)

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

Oracle DBA

Real Application Cluster (RAC) Maintenance tasks Backup and Recovery Database Architecture Replication Oracle Performance Tuning Oracle DBA - Other articles Oracle scripts/ Selects for DBA Oracle Errors (ORA-nnnnn)

The last articles in the site (HOT)

The most visited articles in the site

In-Oracle.com  -> Oracle DBA -> Oracle Database Backup and Recovery

-> RMAN incomplete recovery (in NOARCHIVELOG mode)

 

 RMAN incomplete recovery (in NOARCHIVELOG mode)

 

 

Here are the main topics for this article:

 

RMAN Incomplete Recovery Overview

 

An Incomplete Recovery is a Recovery which is done without restoring the database up to the failure time. A typical case incomplete recovery is when the database is not in ARCHIVELOG. In this article I will show how a backup could be taken for a database in NOARCHIVELOG mode and how a database could be restored in case of failure.   

Take a Full Backup with RMAN (database in NOARCHIVELOG)

 

Because the database is in NOARCHIVELOG mode, there is no need to take the archive log files and the log files in backup. To take a backup with RMAN for a database in NOARCHIVELOG, the instance must be in MOUNT state. However, if the database is OPEN state, the instance could be put in mount state for the backup, and after that the database will be opened. Here is the script (RMAN_NOARCHIVELOG_backup.sh) we can use to take an backup for a database in NOARCHIVELOG mode:

 

#/usr/bin/ksh

ORACLE_HOME=/DB1

export ORACLE_HOME

ORACLE_SID=db1

export ORACLE_SID

RMAN_LOG_FILE=/home/oracle/Desktop/Backup_rman/log/hot_database_backup.`date +%y%m%d%H%M`.out

echo >> $RMAN_LOG_FILE

chmod 666 $RMAN_LOG_FILE

echo Script $0 >> $RMAN_LOG_FILE

echo ==== started on `date` ==== >> $RMAN_LOG_FILE

echo >> $RMAN_LOG_FILE

ORACLE_USER=oracle

TARGET_CONNECT_STR=sys/s

RMAN=$ORACLE_HOME/bin/rman

 

BACKUP_TYPE="INCREMENTAL LEVEL 0"

 

echo >> $RMAN_LOG_FILE

echo "RMAN: $RMAN" >> $RMAN_LOG_FILE

echo "ORACLE_SID: $ORACLE_SID" >> $RMAN_LOG_FILE

echo "ORACLE_USER: $ORACLE_USER" >> $RMAN_LOG_FILE

echo "ORACLE_HOME: $ORACLE_HOME" >> $RMAN_LOG_FILE

echo "BACKUP_TYPE: $BACKUP_TYPE" >> $RMAN_LOG_FILE

echo >> $RMAN_LOG_FILE

CMD_STR=""

 

$RMAN target $TARGET_CONNECT_STR catalog rman/r@dbr << EOF

shutdown immediate;

startup mount;

 

RUN {

ALLOCATE CHANNEL ch00 TYPE disk ;

ALLOCATE CHANNEL ch01 TYPE disk ;

ALLOCATE CHANNEL ch02 TYPE disk ;

BACKUP

$BACKUP_TYPE

SKIP INACCESSIBLE

TAG hot_db_bk_level0

FILESPERSET 5

# recommended format

FORMAT '/home/oracle/Desktop/Backup_rman/backup/bk_%s_%p_%t'

DATABASE;

RELEASE CHANNEL ch00;

RELEASE CHANNEL ch01;

RELEASE CHANNEL ch02;

ALLOCATE CHANNEL ch00 TYPE disk;

BACKUP

# recommended format

FORMAT '/home/oracle/Desktop/Backup_rman/backup/cntrl_%s_%p_%t'

CURRENT CONTROLFILE;

RELEASE CHANNEL ch00;

}

 

alter database open;

 

EOF

            When the RMAN_NOARCHIVELOG_backup.sh script will run, it will generate the following log information:

[oracle@PROD scripts]$ ./RMAN_NOARCHIVELOG_backup.sh

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Mar 26 22:59:41 2008

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

connected to target database: DB1 (DBID=1244100437)
connected to recovery catalog database

RMAN>
database closed
database dismounted
Oracle instance shut down

RMAN>
connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area 922746880 bytes

Fixed Size 1222624 bytes
Variable Size 268437536 bytes
Database Buffers 645922816 bytes
Redo Buffers 7163904 bytes

RMAN>
RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20> 21> 22> 23>
allocated channel: ch00
channel ch00: sid=157 devtype=DISK

allocated channel: ch01
channel ch01: sid=155 devtype=DISK

allocated channel: ch02
channel ch02: sid=154 devtype=DISK

Starting backup at 26-MAR-08
channel ch00: starting incremental level 0 datafile backupset
channel ch00: specifying datafile(s) in backupset
input datafile fno=00001 name=/DB1/oradata/db1/system01.dbf
channel ch00: starting piece 1 at 26-MAR-08
channel ch01: starting incremental level 0 datafile backupset
channel ch01: specifying datafile(s) in backupset
input datafile fno=00003 name=/DB1/oradata/db1/sysaux01.dbf
input datafile fno=00004 name=/DB1/oradata/db1/users01.dbf
channel ch01: starting piece 1 at 26-MAR-08
channel ch02: starting incremental level 0 datafile backupset
channel ch02: specifying datafile(s) in backupset
input datafile fno=00005 name=/DB1/oradata/db1/example01.dbf
input datafile fno=00002 name=/DB1/oradata/db1/undotbs01.dbf
channel ch02: starting piece 1 at 26-MAR-08
channel ch00: finished piece 1 at 26-MAR-08
piece handle=/home/oracle/Desktop/Backup_rman/backup/bk_45_1_650415601 tag=HOT_DB_BK_LEVEL0 comment=NONE
channel ch00: backup set complete, elapsed time: 00:00:16
channel ch00: starting incremental level 0 datafile backupset
channel ch00: specifying datafile(s) in backupset
including current control file in backupset
channel ch00: starting piece 1 at 26-MAR-08
channel ch01: finished piece 1 at 26-MAR-08
piece handle=/home/oracle/Desktop/Backup_rman/backup/bk_46_1_650415601 tag=HOT_DB_BK_LEVEL0 comment=NONE
channel ch01: backup set complete, elapsed time: 00:00:16
channel ch01: starting incremental level 0 datafile backupset
channel ch01: specifying datafile(s) in backupset
including current SPFILE in backupset
channel ch01: starting piece 1 at 26-MAR-08
channel ch02: finished piece 1 at 26-MAR-08
piece handle=/home/oracle/Desktop/Backup_rman/backup/bk_47_1_650415602 tag=HOT_DB_BK_LEVEL0 comment=NONE
channel ch02: backup set complete, elapsed time: 00:00:15
channel ch00: finished piece 1 at 26-MAR-08
piece handle=/home/oracle/Desktop/Backup_rman/backup/bk_48_1_650415617 tag=HOT_DB_BK_LEVEL0 comment=NONE
channel ch00: backup set complete, elapsed time: 00:00:01
channel ch01: finished piece 1 at 26-MAR-08
piece handle=/home/oracle/Desktop/Backup_rman/backup/bk_49_1_650415617 tag=HOT_DB_BK_LEVEL0 comment=NONE
channel ch01: backup set complete, elapsed time: 00:00:01
Finished backup at 26-MAR-08

released channel: ch00

released channel: ch01

released channel: ch02

allocated channel: ch00
channel ch00: sid=157 devtype=DISK

Starting backup at 26-MAR-08
channel ch00: starting full datafile backupset
channel ch00: specifying datafile(s) in backupset
including current control file in backupset
channel ch00: starting piece 1 at 26-MAR-08
channel ch00: finished piece 1 at 26-MAR-08
piece handle=/home/oracle/Desktop/Backup_rman/backup/cntrl_50_1_650415621 tag=TAG20080326T230021 comment=NONE
channel ch00: backup set complete, elapsed time: 00:00:01
Finished backup at 26-MAR-08

released channel: ch00

RMAN>
RMAN>
database opened

RMAN>
RMAN>

Recovery Manager complete.
[oracle@PROD scripts]$

 

Restore the database backup  

 

Here is the way the database could be restored if all the files (+control files) are lost:

 

$rman target /  catalog rman/r@dbr;

 

RMAN> startup nomount;

connected to target database (not started)
Oracle instance started

Total System Global Area 922746880 bytes

Fixed Size 1222624 bytes
Variable Size 272631840 bytes
Database Buffers 641728512 bytes
Redo Buffers 7163904 bytes

RMAN> restore controlfile;

Starting restore at 26-MAR-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: reading from backup piece /home/oracle/Desktop/Backup_rman/backup/cntrl_50_1_650415621
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/Desktop/Backup_rman/backup/cntrl_50_1_650415621 tag=TAG20080326T230021
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/DB1/oradata/db1/control01.ctl
output filename=/DB1/oradata/db1/control02.ctl
output filename=/DB1/oradata/db1/control03.ctl
Finished restore at 26-MAR-08

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> restore database;

Starting restore at 26-MAR-08
Starting implicit crosscheck backup at 26-MAR-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 36 objects
Finished implicit crosscheck backup at 26-MAR-08

Starting implicit crosscheck copy at 26-MAR-08
using channel ORA_DISK_1
Finished implicit crosscheck copy at 26-MAR-08

searching for all files in the recovery area
cataloging files...
no files cataloged

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to /DB1/oradata/db1/undotbs01.dbf
restoring datafile 00005 to /DB1/oradata/db1/example01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/Desktop/Backup_rman/backup/bk_47_1_650415602
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/Desktop/Backup_rman/backup/bk_47_1_650415602 tag=HOT_DB_BK_LEVEL0
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /DB1/oradata/db1/system01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/Desktop/Backup_rman/backup/bk_45_1_650415601
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/Desktop/Backup_rman/backup/bk_45_1_650415601 tag=HOT_DB_BK_LEVEL0
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00003 to /DB1/oradata/db1/sysaux01.dbf
restoring datafile 00004 to /DB1/oradata/db1/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/Desktop/Backup_rman/backup/bk_46_1_650415601
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/Desktop/Backup_rman/backup/bk_46_1_650415601 tag=HOT_DB_BK_LEVEL0
channel ORA_DISK_1: restore complete, elapsed time: 00:00:16
Finished restore at 26-MAR-08

RMAN> alter database open resetlogs;

database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN> exit;

NOTE:  For a database which is in NOARCHIVELOG mode a cold backup by copying the file is more appropriate. This example is just to illustrate how RMAN work. 

 

 

In-Oracle.com  -> Oracle DBA -> Oracle Database Backup and Recovery

-> RMAN incomplete recovery (in NOARCHIVELOG mode)

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

 

     Copyright (c) 2011-2015  www.in-oracle.com  |  Disclaimer: The views expressed on this web site are my own and do not reflect the views of Oracle Corporation. You may use the information from this site only at your risk.