Oracle Database Backup Recovery (RMAN Commands)

RMAN Commands

Backup

rman target /

show all;

--autback control file should be on if off then on

list backup summary;

backup database;


backup database plus archivelog;


backup incrementatal level 0 database;

--above is same as backup database (but durin recovery a minor diff)

backup incrementatal level 1 database;

---backup only the changes from last backup;

Restore

rman target /

startup nomount

restore conrolfile from 'd:\backup\DB_CF_C_01-02-0B'


alter database mount;

restore database;

---no need to specify as read from control file restored


recover database;

alter database open resetlogs;

How to create Recovery Catalog

There should be separate oracle database server  for Oracle Recovery catalog (Database Backup Meta Data not Backup itself)


If not use recovery catalog (Default)

then all metadata of rman stored in target database controlfile

and if used recovery catalog then metadata stored in 2 places (target db conrolfile and recovery catalog)


set ORACLE_SID=rcdb

sqlplus / as sysdba

select name from v$database;

create user rcat_user identificated rcat123;

select username from dba_user where username='RACT_USER';

grant connect, resource, recovery_catalog_owner to rcat_user;

create tablespace rcate_tb datafile '\opt\oracle\db\apps_st\rcat_df01.dbf size 20m'


select tablespace_name from dba_tablespaces;

select name from v$databafile;

alter user rcat_user default tablespace rcat_tb;

---if not specify table space default permanent space will be system

alter user rcat_user quota unlimited on rcat_tb;

select table_name form dba_table where tablespace_name ='RCAT_TB';

no rows


select view_name from dba_views where view_name like 'RC_%'


now rows

set ORACLE_SID=targetdb


rman target /

--it will connect to target database, and if we take backup it will show  using target database control file

rman catalog=rcat_user/rcat123@rcdb

create catalog;

set ORACLE_SID=rcdb

sqlplus / as sysdba

select table_name form dba_table where tablespace_name ='RCAT_TB';

44 tables

select view_name from dba_views where view_name like 'RC_%'

56 views


set long 2000;

select text from dba_views where view_name = 'RC_SITE';


select table_name, tablespace_name from dba_tables where table_name='NODE'

set ORACLE_SID=targetdb

rman target / catalog=rcat_user/rcat123@rcdb

--- rman connect to target database using recovery catalog

register database;

exit --from rman

set ORACLE_SID=rcdb


sqlplus / as sysdba

desc rcat_user.rc_database

select name from crat_user.rc_datebase;

set ORACLE_SID=targetdb


rman target / catalog=rcat_user/rcat123@rcdb


backup database;

Scripts

FULL BACKUP

fullbk.sh

. /d01/oracle/RMAN.env
/d01/oracle/bin/rman TARGET sys/oracle@192.1.1.1 CATALOG rman/rman cmdfile /d01/oracle/compbk.sh



  
compbk.sh

run
{
allocate channel ch1 type DISK;
allocate channel ch2 type DISK;
backup incremental level=0 filesperset 10 format '/rmanbkup/bkp_df_%t_%s' database;
release channel ch1;
release channel ch2;
allocate channel ch3 type DISK;
sql 'alter system archive log current';
release channel ch3;
allocate channel ch4 type DISK;
backup format '/rmanbkup/al_%t' archivelog all delete input;
release channel ch4;
}


inc1.sh

. /d01/oracle/RMAN.env
/d01/oracle/bin/rman TARGET sys/oracle@192.1.1.1 CATALOG rman/rman cmdfile /d01/oracle/incremental1.sh


incremental1.sh

run
{
allocate channel ch1 type DISK;
allocate channel ch2 type DISK;
backup incremental level=1 filesperset 10 format '/hdb/backup/bkp_df_%t_%s' database;
release channel ch1;
release channel ch2;
allocate channel ch3 type DISK;
sql 'alter system archive log current';
release channel ch3;
allocate channel ch4 type DISK;
backup format '/hdb/backup/al_%t' archivelog all delete input;
release channel ch4;
}




inc2.sh

. /d01/oracle/RMAN.env
/d01/oracle/bin/rman TARGET sys/oracle@192.1.1.1 CATALOG rman/rman cmdfile /d01/oracle/incremental2.sh


incremental2.sh

run
{
allocate channel ch1 type DISK;
allocate channel ch2 type DISK;
backup incremental level=2 filesperset 10 format '/hdb/backup/bkp_df_%t_%s' database;
release channel ch1;
release channel ch2;
allocate channel ch3 type DISK;
sql 'alter system archive log current';
release channel ch3;
allocate channel ch4 type DISK;
backup format '/hdb/backup/al_%t' archivelog all delete input;
release channel ch4;
}




recover.sh


run
{
ALLOCATE CHANNEL c1 TYPE DISK;
ALLOCATE CHANNEL c2 TYPE DISK;
RESTORE DATABASE;
ALTER DATABASE OPEN;
}


----------------

Important

KEEP in mind that characterset of all databases should be same i.e (US7ASCII)

TARGET DATABASE

. TARGET database must be remote_login_passwordfile=EXCLUSIVE
. startup mount;
. alter database archivelog;
. shutdown immediate;
. mkdir /archiving; chmod 777 /archiving; chown oracle:oinstall /archiving
. create pfile='qamar.ora' from spfile;
. enter *.log_archive_start=TRUE; and *.log_archive_dest_1='location=/archiving' in qamar.ora file.
. connect / as sysdba
. create spfile from pfile='qamar.ora';
. startup

RMAN DATABASE

. create tablespace rman_ts
   datafile '/d01/oracle/oradata/RMAN/rman_01.dbf' size 1000M
   autoextend on;

. create user rman identified by rman
   default tablespace rman_ts
   temporary tablespace temp
   quota unlimited on rman_ts;

. vi tnsnames.ora and enter the SERVICE (192.1.1.2) for the target database
. rman TARGET sys/oracle@192.1.1.2 CATALOG rman/rman
. drop catalog;
. drop catalog;
. create catalog;
. register database;
. CONFIGURE RETENTION POLICY TO REDUNDANCY 7;
. CONFIGURE CONTROLFILE AUTOBACKUP ON;
. CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/rmanbkup/%F';
. CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/rmanbkup/snapcf_PROD.f';
. resync catalog;
. now run RMAN> @compbk.sh
. it will create all backup files in /rmanbkup folder except current redo logs, password file and initialization parameter file.
. if u want to restore full backup run @recover.sh
. if u want to do Incomplete recovery then run @time.sh (But before it run . RMAN.env and shell prompt so that all parameters are set correctly)
. for DUPLICATE database keep the backup on AUXILIARY database;
. if u want to create DUPLICATE of the database then run @duplicate.sh

DELETE OBSOLETE BACKUPS

. configure retention policy to redundancy 1; (which is default) means every new backup will obsolete previous backup.
. RMAN> CROSSCHECK BACKUP;
. RMAN> CROSSCHECK COPY;
. RMAN> DELETE EXPIRED BACKUP;
. RMAN> DELETE OBSOLETE;

Working Recovery

.    Un Tar proddb_16072010.tar.gz file on destination server with the help of following commands.
cd /
tar –xzvf /opt2/proddb_16072010.tar.gz

When this file Un Tar successfully then 1 folder created in opt directory.
•    /opt/proddb

5.    Make proddata directory on destination server with the help of following command.
mkdir /opt/proddata

.    Set Environment with the help of following command.
cd /opt/proddb/11.2.0/
. PROD_proddb.env

.    If RAM is 4GB then set sga_target=2704M and remove sga_max_size with the help of following commands.
vi /opt/proddb/11.2.0/dbs/initPROD.ora
press i
write /”sga”
set sga_target=2704M
#ifile=………………..     Comment this line with # end of file
write :wq
press enter

.    Open highest proddata folder with the help of following commands.
cd /opt2
ls
cd highest folder (16072010)
Copy name of following file
                                                            c-4267929732-20100716-00
                                                           ----DB ID---- ---Date---
.    rman target /

.    startup nomount;


.    restore controlfile from ‘/opt2/16072010/c-4267929732-20100716-00’;
                                                 --Highest---  -----Paste The File Name Here----

.    startup mount; or alter database mount;

    rman>sql ‘alter database flashback off’;

.    restore database;


.    Open new terminal and set the environment with the help of following command.
cd /opt/proddb/11.2.0
. PROD_proddb.env

Now write the following command to get the sequence#.
sqlplus / as sysdba
Select sequence# from v$log where status = ‘CURRENT’;
Output = “20149”

.     In first terminal write the following commands.
RMAN>recover database until sequence=20149 thread=1;
                                   --2nd Terminal Output--

.    If medial recover complete without warning then write the following command.
RMAN>alter database open resetlog;