RMAN restore scenarios

Full restore and recover Database

RUN {
     ALLOCATE CHANNEL CH1 TYPE 'SBT_TAPE' PARMS 'SBT_PARMS=(NSR_SERVER=datadomain.domain.local,NSR_CLIENT=host.domain.local)';
     ALLOCATE CHANNEL CH2 TYPE 'SBT_TAPE' PARMS 'SBT_PARMS=(NSR_SERVER=datadomain.domain.local,NSR_CLIENT=host.domain.local)';
     ALLOCATE CHANNEL CH3 TYPE 'SBT_TAPE' PARMS 'SBT_PARMS=(NSR_SERVER=datadomain.domain.local,NSR_CLIENT=host.domain.local)';
     ALLOCATE CHANNEL CH4 TYPE 'SBT_TAPE' PARMS 'SBT_PARMS=(NSR_SERVER=datadomain.domain.local,NSR_CLIENT=host.domain.local)';

     RESTORE DATABASE;

     RECOVER DATABASE;

     RELEASE CHANNEL CH1;
     RELEASE CHANNEL CH2;
     RELEASE CHANNEL CH3;
     RELEASE CHANNEL CH4;
     ALTER DATABASE OPEN;

}

Restore only one tablespace

RUN {
     ALLOCATE CHANNEL CH1 TYPE 'SBT_TAPE' PARMS 'SBT_PARMS=(NSR_SERVER=datadomain.domain.local,NSR_CLIENT=host.domain.local)';
     ALLOCATE CHANNEL CH2 TYPE 'SBT_TAPE' PARMS 'SBT_PARMS=(NSR_SERVER=datadomain.domain.local,NSR_CLIENT=host.domain.local)';
     ALLOCATE CHANNEL CH3 TYPE 'SBT_TAPE' PARMS 'SBT_PARMS=(NSR_SERVER=datadomain.domain.local,NSR_CLIENT=host.domain.local)';
     ALLOCATE CHANNEL CH4 TYPE 'SBT_TAPE' PARMS 'SBT_PARMS=(NSR_SERVER=datadomain.domain.local,NSR_CLIENT=host.domain.local)';

     SQL 'ALTER TABLESPACE TABLESPACE_NAME OFFLINE IMMEDIATE';
     RESTORE TABLESPACE TABLESPACE_NAME;

     RECOVER TABLESPACE TABLESPACE_NAME;

     SQL 'ALTER TABLESPACE TABLESPACE_NAME ONLINE';

     RELEASE CHANNEL CH1;
     RELEASE CHANNEL CH2;
     RELEASE CHANNEL CH3;
     RELEASE CHANNEL CH4;

}

Point in time recovery of full database

RUN {
     ALLOCATE CHANNEL CH1 TYPE 'SBT_TAPE' PARMS 'SBT_PARMS=(NSR_SERVER=datadomain.domain.local,NSR_CLIENT=host.domain.local)';
     ALLOCATE CHANNEL CH2 TYPE 'SBT_TAPE' PARMS 'SBT_PARMS=(NSR_SERVER=datadomain.domain.local,NSR_CLIENT=host.domain.local)';
     ALLOCATE CHANNEL CH3 TYPE 'SBT_TAPE' PARMS 'SBT_PARMS=(NSR_SERVER=datadomain.domain.local,NSR_CLIENT=host.domain.local)';
     ALLOCATE CHANNEL CH4 TYPE 'SBT_TAPE' PARMS 'SBT_PARMS=(NSR_SERVER=datadomain.domain.local,NSR_CLIENT=host.domain.local)';

     RESTORE DATABASE UNTIL TIME = "TO_DATE('14/09/2016-13:23:42', 'DD/MM/YYYY-HH24:MI:SS')";

     RECOVER DATABASE UNTIL TIME = "TO_DATE('14/09/2016-13:23:42', 'DD/MM/YYYY-HH24:MI:SS')";

     RELEASE CHANNEL CH1;
     RELEASE CHANNEL CH2;
     RELEASE CHANNEL CH3;
     RELEASE CHANNEL CH4;

}

***************************************************************************
 Point in time recovery duplicate

At target

rman
connect  target sys/Password1@pdwh
connect  auxiliary /

run
{
set until time "to_date('Feb 02 2018 04:30:00','Mon DD YYYY HH24:MI:SS')";
duplicate
target database to xdwh;
}



Tablespace PITR

rman
connect  target sys/passwd1@phods
connect  auxiliary /


run
{
set until time "to_date('Aug 20 2018 16:00:00','Mon DD YYYY HH24:MI:SS')";
duplicate
target database to crit undo tablespace 'UNDOTBS1' tablespace 'HMA_ESB_DATA','HMA_ESB_INDEX';
}

Recovery manager (RMAN) Sample backup scripts for oracle

Rman setting for non-asm

RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name dbname are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/loc/host/dbname/rman/ctrl_bkp/dbname_ctl_auto_%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 5 G;
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/loc/dbs/snapcf_dbname.f';

Rman backup script non-asm (Full and Incremental)

https://gist.github.com/sravanrox/d2ac7544c5757b8a0cf054ca844ce040

Rman setting for asm

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name dbname are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 1 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '+RECO_DG';
CONFIGURE DEVICE TYPE DISK PARALLELISM 8 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '+RECO_DG';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/snap_dbname.cf';

Rman backup script asm (Full and Incremental)

https://gist.github.com/sravanrox/6e2bacd5413da1276ce8009742c9a1eb

Archive log backup script non-asm

https://gist.github.com/sravanrox/933bb8586706359004349d791dd8c953

Archive log backup script asm

https://gist.github.com/sravanrox/7bbef3f96883334892c1dafb73c858ee

In the ASM script for archive backup, We need to call following script which will check current ASM filesystem usage

https://gist.github.com/sravanrox/c59bc723dad939501a227b0d9d399707

Note:

Non ASM scripts are from Solaris

ASM scripts are from RHEL

Rman Estimated time query

To check estimated run time of a currently running backup please use the following queries. If its a rac us gv$ instead of v$

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,
       ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM   V$SESSION_LONGOPS
WHERE  OPNAME LIKE 'RMAN%'
--AND    PNAME NOT LIKE '%aggregate%'
AND    TOTALWORK != 0
AND    SOFAR <> TOTALWORK;


select sl.sid, sl.opname,
       to_char(100*(sofar/totalwork), '990.9')||'%' pct_done,
       sysdate+(TIME_REMAINING/60/60/24) done_by
  from v$session_longops sl, v$session s
 where sl.sid = s.sid
   and sl.serial# = s.serial#
   and sl.sid in (select sid from v$session where module like 'backup%' or module like 'restore%' or module like 'rman%')
   and sofar != totalwork
        and totalwork > 0
/