RMAN BACKUP FORMAT and Commands

always do pre-measures:-

cat /etc/oratab or cat /var/opt/oracle/oratab 

export ORACLE_HOME=

export ORACLE_SID=

export PATH=$ORACLE_HOME/bin:$PATH

echo $ORACLE_SID 

rman target /

RMAN> backup database format '/path/to/backup/location/db_backup_%U';

RMAN> backup database format '/path/to/backup/location/%d_%t_%U';

Note:- 

"backup database format '/path/to/backup/location/%d_%t_%U';" will take backup of the entire database including data files, control files, and archived logs. The command is called Backup database.

if you want to speed up the backup you can mention "parallel n"

example :-

RMAN> backup database format '/path/to/backup/location/%d_%t_%U' parallel 4;

this will make four files

some flags used in rman :-

%d :- database name

%t :- backup set 

%s :- backup set number

%p :- piece number

%U :- unique backup piece number

%F :- filename with full name

%T :- backup piece tag 

for example :- %d_%t  means  $ORACLE_SID_$(DATE '+%Y%m%d')  

pune_20230516  ---> this will come after using %d_%t 

RMAN > backup archivelog all format '/path/to/backup/location/arc_%d_%t_%U';

To delete archivelog older :-

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

RMAN> list archivelog until time 'sysdate - n';

RMAN > delete noprompt archivelog until time 'sysdate - n';

RMAN > crosscheck archivelog all;

sqlplus / as sysdba 

SQL> shutdown immediate;

SQL> startup nomount;

SQL> alter database mount;

RMAN > catalog start with '/path/to/backup/location';

RMAN > restore database;

RMAN > recover database;

sqlplus / as sysdba 

SQL> alter database open resetlogs;

Restore and Recovery :-

backup current controlfile format '/path/to/bkp_controlfile.bkp';

alter database nomount;

restore controlfile from '/path/to/bkp_controlfile.bkp';

or 

restore controlfile from autobackup;

alter database mount;

recover database;

alter database open resetlogs;

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

++++++++++++++++++ RMAN FILES DETAILS ++++++++++++++++++++++++++++++

select fname,to_char(completion_time,'DD-Mon-YYYY HH:MI:SS') "completion_time" from v$backup_files where completion_time is not null and fname like '%dbf%' order by completion_time desc;

select * from v$rman_configuration;

select * from v$rman_backup_job_details;

select * from v$rman_backup_type;

to_char(date,'DD-MONTH-YYYY HH12:MI:SS') :- 08-September-2023 10:02:32

to_char(date,'DD-MONTH-YYYY HH24:MI:SS') :- 08-September-2023 21:02:32 

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

rman target /

RMAN > run {

delete noprompt archivelog until time 'sysdate-2';

crosscheck archivelog all;

}

Incremental backups :-

Level 0 : full backup

Level 1 : incremental backup

To perform a level 0 backup (full backup), you can use:

backup incremental level 0 database;

To perform a level 1 backup ( incremental backup), you can use:

backup incremental level 1 database;

To perform cumulative incremental backup, you can use:

backup incremental level 1 cumulative database;

Specify the backup location:

backup incremental level 1 database tag 'level1' format '/path/to/backup/%U';

The increment backup in RMAN is done if you specify a LEVEL <n> option, where n can by either 0 or 1

A LEVEL 0 incremental backup means complete backup just like Full backup
A LEVEL 1 incremental backup copies all blocks changed since last LEVEL 0 or LEVEL 1 backup.

A LEVEL 1 COMULATIVE backup copies all blocks changed since last LEVEL 0 backup.

In incremental backup strategy you will first need to take complete backup i.e. LEVEL 0 backup

++++++++++++++++++++++++++++++++++++++++++++++++++++++

Point in time recovery in Oracle using RMAN (recovery manager) allows you to recover your database to a specific point in time, i.e. any time you would like to make the database available present in database backup. 

step 1:- Connect to the RMAN 
echo $ORACLE_SID
rman target /
or 
rman target sys/passwd@database_netservice
step 2:- restore the control file to the location desired

RMAN > 
run {
allocate channel ch1 type disk;
restore controlfile to '/path/to/new/controlfile.ctl';
}

step 3:- Mount the database since backup/restore can only perform in mount mode

run {
allocate channel ch1 type disk;
sql 'alter database mount';
}

step 4:- Specify the point in time i.e. mention the time 

run {
set until time 'to_date('yyyy-mm-dd hh24:mi:ss','yyyy-mm-dd hh24:mi:ss')';
}

step 5:- Do Incomplete Recovery to open database with reset logs option since we don't want to use old redo logs.

run {
allocate channel ch1 type disk;
recover database;
}

step 6:- Open the database with reset logs option.

run {
sql 'alter database open resetlogs';
}

step 7:- Complete the recovery now 

run {
allocate channel ch1 type disk;
backup database format '/path/to/backup/%d_%t.dbf';
}

+++++++++++RMAN DETAIL SCRIPT ++++++++++++++++++++

RMAN BACKUP STATUS DETAILS SCRIPTS

######## RMAN Backup logs details will show which are files completed #############

cd logs/voyager/backups

voyager:/home/oracle/ $ cd logs/voyager/backups

voyager:logs/voyager/backups/ $ ls -lrth

total 3218

-rw-r-----   1 oracle   dba          54K Feb 21 08:17 DB_Full_Backup_202202210158.log

-rw-r-----   1 oracle   dba          54K Feb 21 08:18 DB_Full_Backup_202202210155.log

-rw-r-----   1 oracle   dba         103K Feb 21 23:27 DB_Full_Backup_202202212300.log

-rw-r-----   1 oracle   dba         155K Feb 23 01:09 DB_Full_Backup_202202222300.log

-rw-r-----   1 oracle   dba         132K Feb 24 01:13 DB_Full_Backup_202202232300.log

-rw-r-----   1 oracle   dba          29K Feb 25 00:25 DB_Full_Backup_202202242300.log

voyager:logs/voyager/backups/ $ more DB_Full_Backup_202202242300.log

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

SQL> col STATUS format a9

SQL> col hrs format 999.99

SQL> select SESSION_KEY, INPUT_TYPE, STATUS,

to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,

to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,

elapsed_seconds/3600 hrs from V$RMAN_BACKUP_JOB_DETAILS

order by session_key;

SQL> /

SESSION_KEY  INPUT_TYPE    STATUS    START_TIME     END_TIME       HRS

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

 29          DB FULL       RUNNING   01/07/14 10:28 01/07/14 10:28 .00

SQL> /

SESSION_KEY  INPUT_TYPE    STATUS    START_TIME     END_TIME       HRS

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

 29          DB FULL       RUNNING   01/07/14 10:28 01/07/14 10:28 .01

SQL> /

SESSION_KEY  INPUT_TYPE    STATUS    START_TIME     END_TIME       HRS

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

 29          DB FULL       COMPLETED 01/07/14 10:28 01/07/14 10:29 .03

============================================================

next script

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

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK, 

ROUND (SOFAR/TOTALWORK*100, 2) "% COMPLETE"

FROM V$SESSION_LONGOPS

WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%'

AND TOTALWORK! = 0 AND SOFAR <> TOTALWORK;

 SID       SERIAL#    CONTEXT    SOFAR      TOTALWORK  %COMPLETE

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

 18        29         1          9115569    19258880   47.33

===========================================================================

next script

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

You can also check historical backup status with the help of following script:

set linesize 500 pagesize 2000

col Hours format 9999.99

col STATUS format a10

select SESSION_KEY, INPUT_TYPE, STATUS,

to_char(START_TIME,'mm-dd-yyyy hh24:mi:ss') as RMAN_Bkup_start_time,

to_char(END_TIME,'mm-dd-yyyy hh24:mi:ss') as RMAN_Bkup_end_time,

elapsed_seconds/3600 Hours from V$RMAN_BACKUP_JOB_DETAILS

order by session_key;

SESSION_KEY INPUT_TYPE    STATUS     RMAN_BKUP_START_TIM RMAN_BKUP_END_TIME  HOURS

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

 137764     DB FULL       COMPLETED  06-06-2017 02:00:32 06-06-2017 04:12:13 2.19

 137770     ARCHIVELOG    COMPLETED  06-06-2017 04:00:29 06-06-2017 04:01:05 .01

 137778     ARCHIVELOG    COMPLETED  06-06-2017 06:00:27 06-06-2017 06:00:35 .00

 137782     ARCHIVELOG    COMPLETED  06-06-2017 08:00:32 06-06-2017 08:03:36 .05

 137786     ARCHIVELOG    COMPLETED  06-06-2017 10:00:30 06-06-2017 10:02:03 .03

 137790     ARCHIVELOG    COMPLETED  06-06-2017 12:00:30 06-06-2017 12:02:34 .03

 137794     ARCHIVELOG    COMPLETED  06-06-2017 14:00:30 06-06-2017 14:02:58 .04

=======================================================

next script 

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

 select OUTPUT from V$RMAN_OUTPUT;

=======================================================

RMAN COMMANDS:---

=======================

To show all configuration details :-  RMAN > show all;

To show backups details:- RMAN > list backup;

To show archivelog details:- RMAN > list archivelog until time 'sysdate - 12';

To show backup summary:- RMAN > list backup summary;

To delete expired backups:- RMAN > delete noprompt expired backup;

To crosscheck backups:- RMAN > crosscheck backup;

To delete obsolute backups:- RMAN > delete noprompt obsolute;

To list backups for sysdate:- RMAN > list backup completed before 'sysdate-7';

To delete backup after time:- RMAN > list backup completed after 'sysdate-8';

To delete backup between time:- 

RMAN > list backup completed between 'sysdate-7' and 'sysdate-3'; 

To delete backup before time:- RMAN > delete backup completed before 'sysdate-7';

To delete backup after time:- RMAN > delete backup completed after 'sysdate-8';

To delete backup between time:- 

RMAN > delete backup completed between 'sysdate-7' and 'sysdate-3'; 

+++++++ RMAN BKUP Location command ++++++++++++++++++

echo $(echo "select fname from v\$backup_files where file_type='PIECE' order by completion_time desc fetch first 1 rows only;" | sqlplus \/ as sysdba | dirname $(grep -e "^/[[:graph:]]*"))

another best script that will work on standalone DB :-

cd $(echo "select name from v\$recovery_file_dest;" | sqlplus \/ as sysdba | grep -E "^/[[:graph:]]*")

another best script that will work on RAC DB :-

echo $(echo "select name from v\$recovery_file_dest;" | sqlplus \/ as sysdba | grep -e "^+")

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

[oracle@pm latestFolder]$ cat example.sh

#!/bin/sh

# below command will extract timing of latest rman backup

timing=$(echo "select to_char(newest_backup_time,'YYYY-MM-DD HH:MI:SS') "latest_backup_time" from v\$backup_set_summary order by newest_backup_time desc;" | sqlplus \/ as sysdba | grep -v "[[:alpha:]]" | grep -E "^[[:digit:]]{4}+-+[[:digit:]]{0,2}+-[[:digit:]]{0,2}+[[:space:]]+[[:digit:]]{0,2}+:+[[:digit:]]{0,2}+:+[[:digit:]]{0,2}")

# below command will extract backup location

backup_location=$(echo "select name from v\$recovery_file_dest;" | sqlplus \/ as sysdba | grep -E "^/[[:graph:]]*")

# below command will fine files

find ${backup_location}-maxdepth 1 -type f -not -newermt "${timing}"

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

or we can find backups older than today 

find . -type f -not -newermt "$(date -d '0 days ago' +'%Y-%m-%d %H:%M:%S')"

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

du -b *2023_12_30_dbf_* | awk '{x+=$1};END{print x/(1024*1024*1024),"GB"}' 

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

How to kill ORACLE RMAN backup job:-

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

SQL> 

select b.sid, b.serial#, a.spid, b.client_info from gv$process a, gv$session b
where a.addr=b.paddr and client_info like 'rman%';

SID    SERIAL#    SPID    CLIENT_INFO

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

590    452                888        rman channel=full_chanel

SQL >

 alter system kill session '&sid, &serial' ;

=============================================

-- 1. Full Level 0 Backup with KEEP FOREVER and TAG

BACKUP INCREMENTAL LEVEL 0 

  DATABASE 

  TAG 'PREUPGRADE_LEVEL0_BKP' 

  KEEP FOREVER;

-- 2. Full Backup + Control File + Archivelogs + SPFILE

BACKUP INCREMENTAL LEVEL 0 

  DATABASE 

  INCLUDE CURRENT CONTROLFILE 

  PLUS ARCHIVELOG 

  TAG 'PREUPGRADE_FULL_BKP' 

  KEEP FOREVER;

-- 3. Separate Backup for SPFILE and Control File

BACKUP SPFILE TAG 'PREUPGRADE_SPFILE_BKP';

BACKUP CURRENT CONTROLFILE TAG 'PREUPGRADE_CONTROLFILE_BKP';

-- 4. Mark Existing Backup as KEEP FOREVER

CHANGE BACKUP TAG 'PREUPGRADE_FULL_BKP' KEEP FOREVER;

-- 5. Remove KEEP FOREVER Status

CHANGE BACKUP TAG 'PREUPGRADE_FULL_BKP' NOKEEP;

-- 6. Force Delete Backup (Even with KEEP FOREVER)

DELETE FORCE BACKUP TAG 'PREUPGRADE_FULL_BKP';

-- 7. Crosscheck and Validate Backups

CROSSCHECK BACKUP;

CROSSCHECK ARCHIVELOG ALL;

VALIDATE BACKUPSET TAG 'PREUPGRADE_FULL_BKP';

-- 8. Backup All Datafiles into a Single File

RUN {

  ALLOCATE CHANNEL ch1 DEVICE TYPE DISK;

  BACKUP AS BACKUPSET 

    DATABASE 

    FORMAT '/u01/backup/full_db_single_file.bkp';

  RELEASE CHANNEL ch1;

}

=======================================

How to restore & recover database using tag 

STARTUP NOMOUNT;

-- Restore control file from the tagged backup

RESTORE CONTROLFILE FROM TAG 'FULL_BKP_11Jul2025';

-- Mount the database using restored controlfile

ALTER DATABASE MOUNT;

-- Restore all datafiles from the backup with the given tag

RESTORE DATABASE FROM TAG 'FULL_BKP_11Jul2025';

-- Recover database using available archive logs and redo

RECOVER DATABASE;

-- Open the database

ALTER DATABASE OPEN;

If needs to recover control file also 

Set Unitl uses in Oracle RMAN command:

Incremental backup on standby db:

-- Step 1: Start the standby instance without mounting (control file is missing)

STARTUP NOMOUNT;

-- 🔎 Reason: Required before restoring control file. DB can't mount without it.

-- Step 2: Restore control file from a known TAG or path (taken from primary)

RESTORE CONTROLFILE FROM TAG 'FULL_BKP_11Jul2025';

-- 🔎 Reason: Replaces lost/corrupted standby control file with a consistent one from backup.

-- Step 3: Mount the database to allow access to datafile metadata

ALTER DATABASE MOUNT;

-- 🔎 Reason: Required before restoring or recovering datafiles. Now RMAN can read file structure.

-- Step 4: Catalog the incremental backup if standby doesn't know about it

CATALOG START WITH '/u01/backup/';

-- 🔎 Reason: Tells RMAN about external backup files if they weren’t already registered in the control file.

-- Step 5: Restore the database (optional - only if full restore needed)

RESTORE DATABASE FROM TAG 'INCR_STANDBY_BKP_11Jul2025';

-- 🔎 Reason: Reapplies full or incremental image copies of the datafiles if the standby is outdated.

-- Step 6: Recover the database using archived redo or incremental changes

RECOVER DATABASE;

-- 🔎 Reason: Applies redo to bring the datafiles to a consistent state.

-- Step 7: Restart standby recovery (if part of Data Guard)

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

-- 🔎 Reason: Starts real-time redo apply in standby mode, reconnects to primary for log shipping.

Summary:

Rolling forward Standby db:

-- STEP 1: Get SCN from Standby

-- Run on STANDBY SQL*Plus

sqlplus / as sysdba

SELECT CURRENT_SCN FROM V$DATABASE;

-- Assume output SCN is: 123456789

-- STEP 2: Take Incremental Backup on PRIMARY

-- Run on PRIMARY in RMAN

rman target /

RUN {

  ALLOCATE CHANNEL c1 DEVICE TYPE DISK;

  BACKUP INCREMENTAL FROM SCN 123456789 DATABASE FORMAT '/backup/inc_for_stby_%U.bkp' TAG 'INC_FOR_STANDBY';

  BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/backup/standby_ctrl.bkp';

  RELEASE CHANNEL c1;

}

-- STEP 3: Transfer Backup Files to STANDBY

-- Run on OS shell (PRIMARY)

scp /backup/inc_for_stby_*.bkp oracle@standby_host:/backup/

scp /backup/standby_ctrl.bkp oracle@standby_host:/backup/

-- STEP 4: Restore and Apply on STANDBY

-- Run on STANDBY in RMAN

rman target /

SHUTDOWN IMMEDIATE;

STARTUP NOMOUNT;

RESTORE CONTROLFILE FROM '/backup/standby_ctrl.bkp';

ALTER DATABASE MOUNT;

-- STEP 5: Catalog and Recover

CATALOG START WITH '/backup/';

RECOVER DATABASE;

-- STEP 6: Start Standby Recovery (MRP)

-- Run on STANDBY SQL*Plus

sqlplus / as sysdba

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Recover spfile from backup:

-- STEP 1: Connect to RMAN

rman target /

-- STEP 2: (Optional) Set DBID if database is down

SET DBID = 123456789;

-- STEP 3: Startup NOMOUNT (use PFILE if needed)

-- If no spfile or pfile exists, create a minimal init file manually:

echo "db_name='ORCL'" > $ORACLE_HOME/dbs/initORCL.ora

-- Start database with temporary PFILE

sqlplus / as sysdba

STARTUP NOMOUNT PFILE='$ORACLE_HOME/dbs/initORCL.ora';

-- STEP 4: Restore SPFILE from autobackup

-- Run in RMAN

RESTORE SPFILE FROM AUTOBACKUP;

-- or restore to temporary PFILE location (if you want to edit first)

RESTORE SPFILE TO PFILE '/tmp/init_temp.ora' FROM AUTOBACKUP;

-- STEP 5: (Optional) Edit temp PFILE if needed

vi /tmp/init_temp.ora

-- STEP 6: Create SPFILE from edited PFILE

sqlplus / as sysdba

STARTUP NOMOUNT PFILE='/tmp/init_temp.ora';

CREATE SPFILE FROM PFILE='/tmp/init_temp.ora';

-- STEP 7: Restart Database with SPFILE

SHUTDOWN IMMEDIATE;

STARTUP NOMOUNT;

-- ✅ SPFILE is now restored and database is using it

=======================================================