Restore Duplicate Oracle Database from RMAN Offline Backup

June 5, 2020 9-minute read

Oracle • RMAN • Backup • Restore

Introduction

A common task for a DBA is to restore databases from a past time backup for testing or investigation, so normally you have to find your backup and restore it in another machine. We are going to perform this task using a Offline Backup, that is taken with the database not running, so we can skip the RECOVER part and avoid using archive logs.

Prerequisites

We have a recently created Oracle 19c multitenant instance patodb with one pluggable database taller running in a Virtual Machine Instance on Google Cloud Platform that we are going to use as our source.

For database creation steps check Create Oracle Multitenant Database in Silent Mode.
For RMAN configuration steps go to RMAN Backup Oracle Database to Local Disk.

Also we need another Virtual Machine Instance with the same operating system, and same oracle software version installed that will be our recovery destination.

RMAN Offline Backup

As we need no activity in the database for this backup, first we need to shutdown the instance.
So we connect to our target (local) database using RMAN to shutdown and then mount the instance to be able to access the data files for the backup:

[oracle@patoracle ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Fri Jun 5 15:32:10 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PATODB (DBID=1820073908)

RMAN> shutdown immediate

using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down

RMAN> startup mount

Oracle instance started
database mounted

Backup Database

Now we can proceed to backup our database with backup database command:

RMAN> backup database ;

Starting backup at 05-JUN-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=259 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=00001 name=/oradata/cdbs/PATODB/system01.dbf
input datafile file number=00003 name=/oradata/cdbs/PATODB/sysaux01.dbf
input datafile file number=00013 name=/oradata/cdbs/PATODB/undotbs.dbf
input datafile file number=00007 name=/oradata/cdbs/PATODB/users01.dbf
channel ORA_DISK_1: starting piece 1 at 05-JUN-20
channel ORA_DISK_1: finished piece 1 at 05-JUN-20
piece handle=/rman/respaldo/PATODB_20200605_0rv232un.bkp tag=TAG20200605T153759 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/oradata/cdbs/PATODB/pdbseed/sysaux01.dbf
input datafile file number=00005 name=/oradata/cdbs/PATODB/pdbseed/system01.dbf
input datafile file number=00008 name=/oradata/cdbs/PATODB/pdbseed/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 05-JUN-20
channel ORA_DISK_1: finished piece 1 at 05-JUN-20
piece handle=/rman/respaldo/PATODB_20200605_0sv232vr.bkp tag=TAG20200605T153759 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00010 name=/oradata/pdbs/taller/sysaux01.dbf
input datafile file number=00009 name=/oradata/pdbs/taller/system01.dbf
input datafile file number=00012 name=/oradata/pdbs/taller/datos.dbf
channel ORA_DISK_1: starting piece 1 at 05-JUN-20
channel ORA_DISK_1: finished piece 1 at 05-JUN-20
piece handle=/rman/respaldo/PATODB_20200605_0tv2330a.bkp tag=TAG20200605T153759 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 05-JUN-20

Starting Control File and SPFILE Autobackup at 05-JUN-20
piece handle=/rman/controlfile/c-1820073908-20200605-06.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 05-JUN-20

RMAN>

We can see RMAN backed up the CDB root, the seed database and our pluggable database taller.
Once the backup finished we can startup the database:

RMAN> alter database open;

Statement processed

RMAN> exit

Recovery Manager complete.

and finally validate our backup is into our RMAN disk

[oracle@patoracle ~]$ ls /rman/controlfile
c-1820073908-20200605-06.bkp

[oracle@patoracle ~]$ ls /rman/respaldo
PATODB_20200605_0rv232un.bkp  PATODB_20200605_0sv232vr.bkp  PATODB_20200605_0tv2330a.bkp

Prepare Destination Virtual Machine

In order to restore our database into the new Virtual Machine we need to provide:

The database parameter file init.ora from the source.
The same directory structure (otherwise we need to tell RMAN to do file name conversion)
The offline backup available to read from the new machine.
Setup the ORACLE_SID variable to the name of our instance.

Copy init file from source

[oracle@patorestore ~]$ cd $ORACLE_HOME/dbs

[oracle@patorestore dbs]$ gcloud compute scp patoracle:$ORACLE_HOME/dbs/initpatodb.ora .
initpatodb.ora                                                100% 1350   776.7KB/s   00:00

Create directory structure

Create the directories where your data files are stored in your source and also directories referred by your init.ora file:

[oracle@patorestore ~]$ sudo mkdir /oradata
[oracle@patorestore ~]$ sudo chown oracle:oinstall /oradata

[oracle@patorestore ~]$ mkdir /oradata/cdbs
[oracle@patorestore ~]$ mkdir /oradata/pdbs
[oracle@patorestore ~]$ mkdir /oradata/recovery
[oracle@patorestore ~]$ mkdir /oradata/cdbs/PATODB
[oracle@patorestore ~]$ mkdir /oradata/recovery/PATODB

[oracle@patorestore ~]$ mkdir -p /opt/oracle/admin/patodb/adump

Mount RMAN disk

Add our RMAN disk to the new Virtual Machine and then mount it to /rman directory:

[oracle@patorestore ~]$ sudo mkdir /rman
[oracle@patorestore ~]$ sudo chown oracle:oinstall /rman

[oracle@patorestore ~]$ sudo mount -o discard,defaults /dev/sdb /rman

[oracle@patorestore ~]$ cd /rman
[oracle@patorestore rman]$ ls
controlfile  lost+found  respaldo

For more information on the RMAN disk we are using here please check Google Disk for RMAN backups

Setup Oracle Variables

Export the following Oracle variables, for persistence include them in your .bash_profile:

[oracle@patorestore ~]$ vi .bash_profile

# Database Variables
export ORACLE_BASE=/opt/oracle
export ORACLE_HOME=/opt/oracle/product/19c/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export ORACLE_SID=patodb

and validate they got setup as environment variables:

[oracle@patorestore ~]$ env | grep ORA
ORACLE_SID=patodb
ORACLE_BASE=/opt/oracle
ORACLE_HOME=/opt/oracle/product/19c/dbhome_1

RMAN Restore

Now that our environment is prepared we need to connect to RMAN to perform the restore.

Restore ControlFile

As this is a new empty installation we need to restore the controlfile so we can access the information about the backups and datafiles.
We startup the database with nomount option:

[oracle@patorestore ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Fri Jun 5 17:48:11 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

RMAN> startup nomount

Oracle instance started

and issue the command restore controlfile from pointing to the controlfile backup inside our RMAN disk:


RMAN> restore controlfile from "/rman/controlfile/c-1820073908-20200605-06.bkp";

Starting restore at 05-JUN-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=253 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/cdbs/PATODB/control01.ctl
output file name=/oradata/recovery/PATODB/control02.ctl
Finished restore at 05-JUN-20

we can see the controlfile was restored in the locations indicated by the init.ora

Review backup availability

With access to the controlfile we can check the backups registered to validate that our offline backup is listed. We need to mount the database and view the today’s backup with the command list backup:

RMAN> alter database mount;

released channel: ORA_DISK_1
Statement processed

RMAN> LIST BACKUP OF DATABASE COMPLETED AFTER '(SYSDATE-1)';

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
24      Full    288.94M    DISK        00:00:29     05-JUN-20
        BP Key: 24   Status: AVAILABLE  Compressed: YES  Tag: TAG20200605T153759
        Piece Name: /rman/respaldo/PATODB_20200605_0rv232un.bkp
  List of Datafiles in backup set 24
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  1       Full 2183244    05-JUN-20              NO    /oradata/cdbs/PATODB/system01.dbf
  3       Full 2183244    05-JUN-20              NO    /oradata/cdbs/PATODB/sysaux01.dbf
  7       Full 2183244    05-JUN-20              NO    /oradata/cdbs/PATODB/users01.dbf
  13      Full 2183244    05-JUN-20              NO    /oradata/cdbs/PATODB/undotbs.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
25      Full    119.47M    DISK        00:00:12     05-JUN-20
        BP Key: 25   Status: AVAILABLE  Compressed: YES  Tag: TAG20200605T153759
        Piece Name: /rman/respaldo/PATODB_20200605_0sv232vr.bkp
  List of Datafiles in backup set 25
  Container ID: 2, PDB Name: PDB$SEED
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  5       Full 2165760    05-JUN-20              NO    /oradata/cdbs/PATODB/pdbseed/system01.dbf
  6       Full 2165760    05-JUN-20              NO    /oradata/cdbs/PATODB/pdbseed/sysaux01.dbf
  8       Full 2165760    05-JUN-20              NO    /oradata/cdbs/PATODB/pdbseed/undotbs01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
26      Full    101.73M    DISK        00:00:11     05-JUN-20
        BP Key: 26   Status: AVAILABLE  Compressed: YES  Tag: TAG20200605T153759
        Piece Name: /rman/respaldo/PATODB_20200605_0tv2330a.bkp
  List of Datafiles in backup set 26
  Container ID: 3, PDB Name: TALLER
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  9       Full 2182707    05-JUN-20              NO    /oradata/pdbs/taller/system01.dbf
  10      Full 2182707    05-JUN-20              NO    /oradata/pdbs/taller/sysaux01.dbf
  12      Full 2182707    05-JUN-20              NO    /oradata/pdbs/taller/datos.dbf

We see this is our Full backup with tag TAG20200605T153759.
Then we want to validate those backupset listed in the Piece Name field are in our RMAN disk:

[oracle@patorestore ~]$ ls /rman/respaldo/PATO*
/rman/respaldo/PATODB_20200605_0rv232un.bkp  /rman/respaldo/PATODB_20200605_0tv2330a.bkp
/rman/respaldo/PATODB_20200605_0sv232vr.bkp

Restore Database

Finally we restore our database using the TAG from our offline backup:


RMAN> restore database from tag 'TAG20200605T153759';

Starting restore at 05-JUN-20
using channel ORA_DISK_1

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/cdbs/PATODB/system01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /oradata/cdbs/PATODB/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /oradata/cdbs/PATODB/users01.dbf
channel ORA_DISK_1: restoring datafile 00013 to /oradata/cdbs/PATODB/undotbs.dbf
channel ORA_DISK_1: reading from backup piece /rman/respaldo/PATODB_20200605_0rv232un.bkp
channel ORA_DISK_1: piece handle=/rman/respaldo/PATODB_20200605_0rv232un.bkp tag=TAG20200605T153759
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
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 00005 to /oradata/cdbs/PATODB/pdbseed/system01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /oradata/cdbs/PATODB/pdbseed/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00008 to /oradata/cdbs/PATODB/pdbseed/undotbs01.dbf
channel ORA_DISK_1: reading from backup piece /rman/respaldo/PATODB_20200605_0sv232vr.bkp
channel ORA_DISK_1: piece handle=/rman/respaldo/PATODB_20200605_0sv232vr.bkp tag=TAG20200605T153759
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
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 00009 to /oradata/pdbs/taller/system01.dbf
channel ORA_DISK_1: restoring datafile 00010 to /oradata/pdbs/taller/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00012 to /oradata/pdbs/taller/datos.dbf
channel ORA_DISK_1: reading from backup piece /rman/respaldo/PATODB_20200605_0tv2330a.bkp
channel ORA_DISK_1: piece handle=/rman/respaldo/PATODB_20200605_0tv2330a.bkp tag=TAG20200605T153759
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 05-JUN-20

RMAN> exit

Recovery Manager complete.

Open Database Resetlogs

Once the restore finished successfully we connect to our instance using sqlplus / as sysdba and open the database with the option resetlogs:

[oracle@patodgprmy dbs]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 5 18:16:04 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> alter database open resetlogs;

Database altered.

SQL> create spfile from pfile ;

File created.

And that’s it, we have successfully restored the database.

Validate Pluggable Database

Additionally validate we can access our pluggable database taller to check everything is OK:

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 TALLER                         READ WRITE NO

SQL> alter session set container = taller;

Session altered.

SQL> select count(*) from pato.t1 ;

  COUNT(*)
----------
         5

Our database is restored and working!

Conclusion

We were able to restore our Oracle multitenant database into a different Virtual Machine using RMAN and Google Cloud Platform functionalities.
Using an offline backup is an easy and consistent way to restore or duplicate our database into a different location.