About 8 years ago I wrote a blog named: It’s so Easy to use RMAN for Backups. So it’s time for a revisit and to show how easy it is to restore a database.
Oracle Managed Files (OMF)
Let’s start with some easy tasks: the naming and location of Oracle database files. There might be some few reasons (e.g. Oracle 12.1 with Multitenant) why you want to explicitly name the data files. But for the majority of database its more useful to handle over naming to the Oracle database.
There are three parameters:
db_create_file_dest: The parameter specifies the location of data files, one control file and the first member of the redo logs.db_recovery_file_dest: This parameter specified the location for the second control file, the second member of redo logs and all backup related files, as there are:- Backups
- Archived Redo Logs
- Flashback Logs
db_recovery_file_dest_size: And this parameter reflexes the maximum size of the directory db_recovery_file_dest. Using this parameter the database processes are able to check the required space vs. the available space. In the event that there is insufficient space a cleanup might occur. I will blog about this issue separately.
Example:
SQL> show parameter file_dest
NAME TYPE VALUE
------------------------------------ ----------- -------------------------
db_create_file_dest string /u02/oradata
db_recovery_file_dest string /u03/orabackup
db_recovery_file_dest_size big integer 12918M Please do not specify the name of the database because that’s added automatcially.
The result looks like this:
SQL> SELECT name FROM v$datafile;
NAME
-----------------------------------------------------------------------------------------
/u02/oradata/PAUL/datafile/o1_mf_system_gfqxv3xm_.dbf
/u02/oradata/PAUL/datafile/o1_mf_sysaux_gfqxy3cn_.dbf
/u02/oradata/PAUL/datafile/o1_mf_undotbs1_gfqxztmj_.dbf
/u02/oradata/PAUL/datafile/o1_mf_system_gfqy2zho_.dbf
/u02/oradata/PAUL/datafile/o1_mf_sysaux_gfqy2zhk_.dbf
/u02/oradata/PAUL/datafile/o1_mf_users_gfqxzvw6_.dbf
/u02/oradata/PAUL/datafile/o1_mf_undotbs1_gfqy2zhs_.dbf
/u02/oradata/PAUL/88ECC48AE4632772E0530D63A8C04AEF/datafile/o1_mf_system_gfqzc581_.dbf
/u02/oradata/PAUL/88ECC48AE4632772E0530D63A8C04AEF/datafile/o1_mf_sysaux_gfqzc582_.dbf /u02/oradata/PAUL/88ECC48AE4632772E0530D63A8C04AEF/datafile/o1_mf_undotbs1_gfqzc583_.dbf
/u02/oradata/PAUL/88ECC48AE4632772E0530D63A8C04AEF/datafile/o1_mf_users_gfqznp2m_.dbf You might wonder why there are three SYSTEM, SYSAUX and UNDO Tablespaces and why 4 Data Files are located in a sub directory called 88ECC48AE4632772E0530D63A8C04AEF. The reason is the usage of Multitenant database with one pluggable database. So we have the files of the CDB (e.g. /u02/oradata/PAUL/datafile/o1_mf_system_gfqxv3xm_.dbf), the ones of the PDB$SEED, strangly not in a sub directory (/u02/oradata/PAUL/datafile/o1_mf_system_gfqy2zho_.dbf) and the data files belonging the the PDB. The GUID of the PDB is used for the subdirectory name.
Archive Log Mode
It’s mandatory to run the database in archive mode first because otherwise you are unable to do online backups. Because we have set the parameter db_recovery_file_dest we do not need to specify any archive log destination. Instead it’s sufficient to stop the database, start it in mound mode and switch on archiving:
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
SQL> ARCHIVE LOG LIST
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2 The Oracle database tells us where it stores the archive logs: USE_DB_RECOVERY_FILE_DEST. The db_recovery_file_dest is also called Fast Recovery Area or FRA.
To check the correct archiving we initiate a log switch:
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> SELECT name, sequence# FROM v$archived_log;
NAME SEQUENCE#
---------------------------------------------------------------------- ----------
/u03/orabackup/PAUL/archivelog/2019_05_15/o1_mf_1_2_gfr0hol6_.arc 2
SQL> !ls -l /u03/orabackup/PAUL/archivelog/2019_05_15
total 133064
-rw-r----- 1 oracle oinstall 136255488 May 15 14:11 o1_mf_1_2_gfr0hol6_.arc You can see the OMF structure again: a sub directory named “archivelog” will be created automatically with the current date as subdirectory.
Now all requirements are fulfilled and we can do a backup.
RMAN Backup
oracle@simon[PAUL]% rman target /
Recovery Manager: Release 18.0.0.0.0 - Production on Wed May 15 12:40:38 2019
Version 18.4.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
connected to target database: PAUL (DBID=1975738546)
RMAN> BACKUP DATABASE PLUS ARCHIVELOG; That’s easy – isn’t it? This simple command will backup the database and with the option „PLUS ARCHIVELOG“ all archived logs generated in the meantime will be backed up as well. This option is mandatory as well because it guarantees a constitent backup.
Here are some details about the backup:
Part 1: Archive Log Backup
Starting backup at 15-MAY-19
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=2 RECID=1 STAMP=1008339097
input archived log thread=1 sequence=3 RECID=2 STAMP=1008339575
channel ORA_DISK_1: starting piece 1 at 15-MAY-19
channel ORA_DISK_1: finished piece 1 at 15-MAY-19
piece handle=/u03/orabackup/PAUL/backupset/2019_05_15/o1_mf_annnn_TAG20190515T141936_gfr0yrdn_.bkp tag=TAG20190515T141936 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 15-MAY-19 The first part is to backup the current archive logs. They are not required for the new backup but for the older ones because they guarantee that a past backup is able to be restored to any time until the next backup becomes valid.
Part 2: Data File Backup
Starting backup at 15-MAY-19
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u02/oradata/PAUL/datafile/o1_mf_system_gfqxv3xm_.dbf
…
channel ORA_DISK_1: starting piece 1 at 15-MAY-19
channel ORA_DISK_1: finished piece 1 at 15-MAY-19
piece handle=/u03/orabackup/PAUL/backupset/2019_05_15/o1_mf_nnndf_TAG20190515T141943_gfr0z05c_.bkp tag=TAG20190515T141943 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00014 name=/u02/oradata/PAUL/88ECC48AE4632772E0530D63A8C04AEF/datafile/o1_mf_sysaux_gfqzc582_.dbf
…
channel ORA_DISK_1: starting piece 1 at 15-MAY-19
channel ORA_DISK_1: finished piece 1 at 15-MAY-19
piece handle=/u03/orabackup/PAUL/88ECC48AE4632772E0530D63A8C04AEF/backupset/2019_05_15/o1_mf_nnndf_TAG20190515T141943_gfr103o6_.bkp tag=TAG20190515T141943 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/u02/oradata/PAUL/datafile/o1_mf_sysaux_gfqy2zhk_.dbf
…
channel ORA_DISK_1: starting piece 1 at 15-MAY-19
channel ORA_DISK_1: finished piece 1 at 15-MAY-19
piece handle=/u03/orabackup/PAUL/88EC7E4263971E50E0530D63A8C0199C/backupset/2019_05_15/o1_mf_nnndf_TAG20190515T141943_gfr10m8y_.bkp tag=TAG20190515T141943 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 15-MAY-19 For the CDB and each PDB a separate backup set will be generated.
Part 3: Archivelogs
Starting backup at 15-MAY-19
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=4 RECID=3 STAMP=1008339650
channel ORA_DISK_1: starting piece 1 at 15-MAY-19
channel ORA_DISK_1: finished piece 1 at 15-MAY-19
piece handle=/u03/orabackup/PAUL/backupset/2019_05_15/o1_mf_annnn_TAG20190515T142050_gfr1134p_.bkp tag=TAG20190515T142050 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 15-MAY-19Now the archive logs which are required to make the most current data file backup constitent are backed up.
Part 4: Controlfile and spfile
Starting Control File and SPFILE Autobackup at 15-MAY-19
piece handle=/u03/orabackup/PAUL/autobackup/2019_05_15/o1_mf_s_1008339652_gfr114nk_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 15-MAY-19 Finally the current control file and spfile will be backed up.
We are done with the backup. In the next blog I will describe how to restore and recover a faulty data file.