• Arun Kumar

RMAN DB Full Backup & Recovery

Taking DB FULL Backup

RMAN> backup database;
RMAN> backup database plus archivelog;
RMAN> backup database plus archivelog format "/ora_backup/proddb_ud/proddb_bkp_%U";

DB FULL Backup With Channels

RMAN> run{
allocate channel ch1 device type disk format "/ora_backup/proddb/proddb_bkp_%U";
allocate channel ch2 device type disk format "/ora_backup/proddb_ud/proddb_bkp_%U";
allocate channel ch3 device type disk format "/ora_backup/proddb/proddb_bkp_%U";
backup database plus archivelog;
release channel ch1;
release channel ch2;
release channel ch3;
}

Checking DB Backups

RMAN> list backup of database summary;
RMAN> list backup tag;

You can check DB backup details via below SQLPLUS command also

SQL> set linesize 500
col BACKUP_SIZE for a20
SELECT
INPUT_TYPE "BACKUP_TYPE",
--NVL(INPUT_BYTES/(1024*1024),0)"INPUT_BYTES(MB)",
--NVL(OUTPUT_BYTES/(1024*1024),0) "OUTPUT_BYTES(MB)",
STATUS,
TO_CHAR(START_TIME,'MM/DD/YYYY:hh24:mi:ss') as START_TIME,
TO_CHAR(END_TIME,'MM/DD/YYYY:hh24:mi:ss') as END_TIME,
TRUNC((ELAPSED_SECONDS/60),2) "ELAPSED_TIME(Min)",
--ROUND(COMPRESSION_RATIO,3)"COMPRESSION_RATIO",
--ROUND(INPUT_BYTES_PER_SEC/(1024*1024),2) "INPUT_BYTES_PER_SEC(MB)",
--ROUND(OUTPUT_BYTES_PER_SEC/(1024*1024),2) "OUTPUT_BYTES_PER_SEC(MB)",
--INPUT_BYTES_DISPLAY "INPUT_BYTES_DISPLAY",
OUTPUT_BYTES_DISPLAY "BACKUP_SIZE",
OUTPUT_DEVICE_TYPE "OUTPUT_DEVICE"
--INPUT_BYTES_PER_SEC_DISPLAY "INPUT_BYTES_PER_SEC_DIS",
--OUTPUT_BYTES_PER_SEC_DISPLAY "OUTPUT_BYTES_PER_SEC_DIS"
FROM V$RMAN_BACKUP_JOB_DETAILS
where start_time > SYSDATE -10
and INPUT_TYPE != 'ARCHIVELOG'
ORDER BY END_TIME DESC
/

Complete DB Recovery With FULL Backup


Trigger DB Full Backup


Connect to the target DB and catalog

Take DB full backup


RMAN> backup database plus archivelog format "<backup_location>";

Once backup is completed, check backup tag via below command:

RMAN> list backup of database summary;

Create User and Table

SQL> create user mgr2 identified by mgr2;
SQL> grant connect, resource, create session to mgr2;
SQL> conn mgr2/mgr2
SQL> create table test(serial number(2),name varchar2(5));
SQL> insert into test values(1,'one');
SQL> insert into test values(2,'Two');
SQL> insert into test values(3,'Three');
SQL> insert into test values(4,'Four');
SQL> commit;

Simulate Failure


Get the location of PF, SPF, DF & CF

SQL> select name from v$controlfile;
SQL> select name from v$datafile;

Delete all the spfile, datafiles & Control files from server:

rm -rf <DF locations> <control file locations> <spfile location> <pfile location>

Start Complete DB Recovery


Kill the DB instance, if running. You can do shut abort or kill pmon at OS level

Connect to RMAN and issue below command:

RMAN> STARTUP FORCE NOMOUNT;
RMAN> Restore spfile from autobackup;
RMAN> STARTUP FORCE NOMOUNT;
RMAN> Restore controlfile from autobackup;
RMAN> sql 'alter database mount';
RMAN> Restore database from tag TAG20160618T204340;
RMAN> Recover database;
RMAN> sql 'alter database open RESETLOGS';

Restore SPFILE & Control File (AUTOBACKUP OFF)

RMAN> list backup of spfile summary;
RMAN> list backup of controlfile summary;
RMAN> list backup tag <>;
RMAN> Restore spfile from <Backup Piece Location>;
RMAN> Restore controlfile from <Backup Piece Location>;

DB Recovery With Manual Channels

  • Fast recovery

  • Un-monitored recovery

RMAN> startup force nomount;

run{
allocate channel ch1 device type disk;
allocate channel ch2 device type disk;
restore spfile from tag TAG20160618T205807;
startup force nomount;
restore controlfile from autobackup;
sql 'alter database mount';
restore database from tag TAG20160618T205739;
recover database;
sql 'alter database open RESETLOGS';
release channel ch1;
release channel ch2;
}


Recent Posts

See All

MySQL Installation on Linux

This article demonstrates MySQL installation on Oracle Enterprise Linux 6.5 version. Installation of MySQL Make sure you are able to connect internet via virtual machine. Try to ping google.com and pr

Add ISO image storage repository on XenServer

If you have used VMware ESXI, its very easy to upload ISO files to the data store and create VMs. With XenServer, you must create your own ISO repository to upload ISO files. Create ISO directory Acce

Perform Manual Switchover on Physical Standby

Connect to proddb database via client: Take a new putty session and connect to proddb database via client machine. Keep querying below sqlplus sys/sys@proddb as sysdba SQL> select name, open_mode, db

  • Facebook
  • Twitter
  • YouTube
  • Instagram

© 2020 by DBA Genesis

Feedback