RMAN COLD BACKUP
Note: Cold backup is a consistent backup when the database has been shutdown immediate or Shutdown Normal.If the database is shutdown with abort option then its NOT a consistent backup.
Cold backup can be taken by Rman in mount stage after database has been shutdown immediate.
===============================================================================
1. Ensure all files are online and none of the files are in recovery status:
===============================================================================
>>> oracle@srv-general-db1
$ sqlplus / as sysdba
SQL> set lines 222 pages 999
select distinct(status), count(*) from v$datafile group by status;
++++++++++
STATUS COUNT(*)
------- ----------
ONLINE 4
SYSTEM 1
++++++++++
---> (OPTIONAL) Once confirmed all files are online. Spool details of the database
SQL> set linesize 150
column name format a80
column member format a80
spool /tmp/DB_Details.log
select file#, ts#, status, name from v$datafile;
select * from v$recover_file;
Select * from v$logfile;
select * from v$controlfile;
clear columns
spool off
========================================================
2. Shutdown immediate the database and Startup mount:
========================================================
>>> oracle@srv-general-db1
$ sqlplus / as sysdba
SQL> shu immediate;
SQL> startup mount;
Note: If database is RAC, ensure you shutdown immediate all the instance and startup mount only one instance after that.
========================================================
3. Starting and Exiting RMAN;
4. Check the rman setting:
========================================================
>>> oracle@srv-general-db1
$ rman target /
Note: Ensure controlfile autobackup is set to on. If not set it to On
RMAN> show all;
++++++++++
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name TESTDB are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
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 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 RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/12.1.0.2/db_1/dbs/snapcf_TESTDB.f'; # default
++++++++++
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
++++++++++
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
++++++++++
========================================================
5. Take the database backup:
========================================================
>>> oracle@srv-general-db1
$ rman target /
RMAN> run {
SET COMMAND ID TO 'RMAN COLD BACKUP';
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO COMPRESSED BACKUPSET;
BACKUP AS COMPRESSED BACKUPSET DATABASE TAG TESTDB FORMAT '/oradata/backup/TESTDB/%d_%T_%s_%p_cold';
BACKUP TAG TESTDB_control CURRENT CONTROLFILE FORMAT '/oradata/backup/TESTDB/%d_%T_%s_%p_ctl';
}
++++++++++
executing command: SET COMMAND ID
old RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO COMPRESSED BACKUPSET;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO COMPRESSED BACKUPSET;
new RMAN configuration parameters are successfully stored
released channel: ORA_DISK_1
released channel: ORA_DISK_2
Starting backup at 09-JAN-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=1 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/oradata/TESTDB/example01.dbf
input datafile file number=00003 name=/oradata/TESTDB/sysaux01.dbf
channel ORA_DISK_1: starting piece 1 at 09-JAN-20
channel ORA_DISK_2: starting compressed full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00001 name=/oradata/TESTDB/system01.dbf
input datafile file number=00004 name=/oradata/TESTDB/undotbs01.dbf
input datafile file number=00006 name=/oradata/TESTDB/users01.dbf
channel ORA_DISK_2: starting piece 1 at 09-JAN-20
channel ORA_DISK_2: finished piece 1 at 09-JAN-20
piece handle=/oradata/backup/TESTDB/TESTDB_20200109_5_1_cold tag=TESTDB comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:02:16
channel ORA_DISK_1: finished piece 1 at 09-JAN-20
piece handle=/oradata/backup/TESTDB/TESTDB_20200109_4_1_cold tag=TESTDB comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:26
Finished backup at 09-JAN-20
Starting backup at 09-JAN-20
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 09-JAN-20
channel ORA_DISK_1: finished piece 1 at 09-JAN-20
piece handle=/oradata/backup/TESTDB/TESTDB_20200109_6_1_ctl tag=TESTDB_CONTROL comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 09-JAN-20
Starting Control File and SPFILE Autobackup at 09-JAN-20
piece handle=/u01/app/oracle/fast_recovery_area/TESTDB/autobackup/2020_01_09/o1_mf_s_1029212890_h1fxys92_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 09-JAN-20
++++++++++
$ ls -ltr /oradata/backup/TESTDB/*
++++++++++
-rw-r-----. 1 oracle oinstall 246571008 Jan 9 04:52 /oradata/backup/TESTDB/TESTDB_20200109_5_1_cold
-rw-r-----. 1 oracle oinstall 133816320 Jan 9 04:52 /oradata/backup/TESTDB/TESTDB_20200109_4_1_cold
-rw-r-----. 1 oracle oinstall 1097728 Jan 9 04:52 /oradata/backup/TESTDB/TESTDB_20200109_6_1_ctl
++++++++++
========================================================
6. Verify the backup validity:
========================================================
>>> oracle@srv-general-db1
$ rman target /
RMAN> list backup tag 'TESTDB';
++++++++++
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4 Full 235.14M DISK 00:02:09 09-JAN-20
BP Key: 4 Status: AVAILABLE Compressed: YES Tag: TESTDB
Piece Name: /oradata/backup/TESTDB/TESTDB_20200109_5_1_cold
List of Datafiles in backup set 4
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1691482 09-JAN-20 /oradata/TESTDB/system01.dbf
4 Full 1691482 09-JAN-20 /oradata/TESTDB/undotbs01.dbf
6 Full 1691482 09-JAN-20 /oradata/TESTDB/users01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5 Full 127.61M DISK 00:02:19 09-JAN-20
BP Key: 5 Status: AVAILABLE Compressed: YES Tag: TESTDB
Piece Name: /oradata/backup/TESTDB/TESTDB_20200109_4_1_cold
List of Datafiles in backup set 5
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
3 Full 1691482 09-JAN-20 /oradata/TESTDB/sysaux01.dbf
5 Full 1691482 09-JAN-20 /oradata/TESTDB/example01.dbf
++++++++++
RMAN> validate backupset 4,5;
++++++++++
Starting validate at 09-JAN-20
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting validation of datafile backup set
channel ORA_DISK_1: reading from backup piece /oradata/backup/TESTDB/TESTDB_20200109_5_1_cold
channel ORA_DISK_2: starting validation of datafile backup set
channel ORA_DISK_2: reading from backup piece /oradata/backup/TESTDB/TESTDB_20200109_4_1_cold
channel ORA_DISK_2: piece handle=/oradata/backup/TESTDB/TESTDB_20200109_4_1_cold tag=TESTDB
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: validation complete, elapsed time: 00:00:35
channel ORA_DISK_1: piece handle=/oradata/backup/TESTDB/TESTDB_20200109_5_1_cold tag=TESTDB
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:45
Finished validate at 09-JAN-20
++++++++++
RESTORE FROM RMAN COLD BACKUP
========================================================
1. Startup Nomount the database:
========================================================
>>> oracle@srv-general-db1
$ sqlplus / as sysdba
SQL> startup nomount;
Note: If running RAC database, mount one instance only.
========================================================
2. Restore controlfile from the backuppiece:
========================================================
>>> oracle@srv-general-db1
$ rman target /
RMAN> restore primary controlfile from '/oradata/backup/TESTDB/TESTDB_20200109_6_1_ctl';
++++++++++
Starting restore at 09-JAN-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/oradata/TESTDB/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/TESTDB/control02.ctl
Finished restore at 09-JAN-20
++++++++++
========================================================
3. Mount the database:
========================================================
>>> oracle@srv-general-db1
RMAN> alter database mount;
++++++++++
Statement processed
released channel: ORA_DISK_1
++++++++++
========================================================
(OPTIONAL): Re-name redo log files:
========================================================
Note: If you have same location this step can be skipped, otherwise redo log files should be re-named with correct location paths.
SQL> set lines 222 pages 999
select 'alter database rename file '''|| member ||''' to '''|| replace(member,'/oradata/TESTDB','/oradata/TESTDB_new')||'''' as "Re-name"
from v$logfile
where type <> 'STANDBY';
========================================================
4. Restore database:
========================================================
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO COMPRESSED BACKUPSET;
RMAN> catalog start with '/oradata/backup/TESTDB/';
RMAN> crosscheck backup;
RMAN> run{
set newname for database to '/oradata/TESTDB/%b';
restore database from tag TESTDB;
}
++++++++++
executing command: SET NEWNAME
Starting restore at 09-JAN-20
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /oradata/TESTDB/system01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /oradata/TESTDB/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /oradata/TESTDB/users01.dbf
channel ORA_DISK_1: reading from backup piece /oradata/backup/TESTDB/TESTDB_20200109_5_1_cold
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00003 to /oradata/TESTDB/sysaux01.dbf
channel ORA_DISK_2: restoring datafile 00005 to /oradata/TESTDB/example01.dbf
channel ORA_DISK_2: reading from backup piece /oradata/backup/TESTDB/TESTDB_20200109_4_1_cold
channel ORA_DISK_1: piece handle=/oradata/backup/TESTDB/TESTDB_20200109_5_1_cold tag=TESTDB
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:36
channel ORA_DISK_2: piece handle=/oradata/backup/TESTDB/TESTDB_20200109_4_1_cold tag=TESTDB
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:01:57
Finished restore at 09-JAN-20
++++++++++
========================================================
5. Open the database with resetlogs:
========================================================
RMAN> alter database open resetlogs;
++++++++++
Statement processed
++++++++++
========================================================
* Sources:
========================================================
How to take a Cold Backup of Database Using Rman (Doc ID 1391357.1)
How to restore cold backup of database taken using Rman (Doc ID 1391384.1)