Restore database from a RMAN backup taken on the OCI Object Storage to new Host

Restore database from a RMAN backup taken on the OCI Object Storage to new host

Earlier I shared on how can we take RMAN backup on a cloud object storage in OCI.

Refer how to take RMAN Backup on OCI Object Storage by clicking here

In this post I am going to share on how can we restore that backup on a new host. The OCI bucket act as a TAPE backup.

Pre-requisites

We must know the following:

DBID of the source database
Password used to encrypt the backup if password-based encryption was used
TDE encryption wallet from the source database if TDE encryption was used

OCI backup:

Steps:

1) Install the Oracle Database Cloud Backup Module for OCI . I have already shared in below link.

Refer how to take RMAN Backup on OCI Object Storage by clicking here

2) Get the DB id from source. In OCI bucket a folder will be present with same DBID

SQL> select dbid from v$database;

DBID

----------

2716435786

3) Take pfile from source and create a target pfile.

SQL> create pfile='/tmp/test.ora' from spfile;

File created.

edit and make changes for target system

Create adump directory and local_listener entry in tnsnames.ora

mkdir -p /u01/app/oracle/admin/FOA12_CL/adump

[oracle@foadbserver tmp]$ grep -i local test.ora

*.local_listener='LISTENER_FOA12'

Add in tnsnames.ora

LISTENER_FOA12 =

(ADDRESS = (PROTOCOL = TCP)(HOST = foadbserver.gold1db1.gold1vcn.oraclevcn.com)(PORT = 1521))

4) Copy the modified pfile to ORACLE_HOME/dbs folder of target system

cp /tmp/test.ora $ORACLE_HOME/dbs/initFOA12.ora

5) Export the Oracle SID

[oracle@foadbserver dbs]$ export ORACLE_SID=FOA12

[oracle@foadbserver dbs]$ echo $ORACLE_SID

DUP

6) Start the restoration of the control file

If encryption was used during backup then use

SET DECRYPTION IDENTIFIED BY 'my_bkup_pwd'; in RMAN.

[oracle@foadbserver dbs]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Sat Jan 6 11:22:27 2024

connected to target database (not started)

RMAN> STARTUP NOMOUNT;

Oracle instance started

Total System Global Area 6979321856 bytes

Fixed Size 2940328 bytes

Variable Size 1224737368 bytes

Database Buffers 5603590144 bytes

Redo Buffers 148054016 bytes

DB ID same as source system.

RMAN> SET DBID=2716435786;

executing command: SET DBID

RMAN> RUN {

ALLOCATE CHANNEL c1 DEVICE TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/home/oracle/oci/lib/libopc.so, SBT_PARMS=(OPC_PFILE=/home/oracle/config)';

RESTORE CONTROLFILE FROM AUTOBACKUP;

}2> 3> 4>

using target database control file instead of recovery catalog

allocated channel: c1

channel c1: SID=19 device type=SBT_TAPE

channel c1: Oracle Database Backup Service Library VER=19.0.0.1

Starting restore at 06-JAN-24

channel c1: looking for AUTOBACKUP on day: 20240106

channel c1: AUTOBACKUP found: c-2716435786-20240106-01

channel c1: restoring control file from AUTOBACKUP c-2716435786-20240106-01

channel c1: control file restore from AUTOBACKUP complete

output file name=+RECO/FOA12_CL/CONTROLFILE/current.259.1157541847

Finished restore at 06-JAN-24

released channel: c1

RMAN> ALTER DATABASE MOUNT;

Statement processed

RMAN>

Now identify the SCN/Until time for restore and recovery

After controlfile is restored from backupset.

- Check last sequence available in backupset of archive log taken

RMAN>LIST BACKUP OF ARCHIVELOG ALL;

List of Archived Logs in backup set 75

Thrd Seq Low SCN Low Time Next SCN Next Time

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

1 24 17402904 06-JAN-24 17403464 06-JAN-24

Use Next SCN Number.

7) Start Database Restore and Recover.

RUN {

SET UNTIL SCN 17403464;

ALLOCATE CHANNEL c1 DEVICE TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/home/oracle/oci/lib/libopc.so, SBT_PARMS=(OPC_PFILE=/home/oracle/config)';

RESTORE DATABASE;

RECOVER DATABASE;

RELEASE CHANNEL c1;

}

Output:

RMAN> RUN {
SET UNTIL SCN 17403464;
ALLOCATE CHANNEL c1 DEVICE TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/home/oracle/oci/lib/libopc.so, SBT_PARMS=(OPC_PFILE=/home/oracle/config)';
RESTORE DATABASE;
RECOVER DATABASE;
RELEASE CHANNEL c1;
}2> 3> 4> 5> 6> 7>

executing command: SET until clause

allocated channel: c1
channel c1: SID=177 device type=SBT_TAPE
channel c1: Oracle Database Backup Service Library VER=19.0.0.1

Starting restore at 06-JAN-24
Starting implicit crosscheck backup at 06-JAN-24
Crosschecked 1 objects
Finished implicit crosscheck backup at 06-JAN-24

Starting implicit crosscheck copy at 06-JAN-24
Finished implicit crosscheck copy at 06-JAN-24

searching for all files in the recovery area
cataloging files...
no files cataloged

channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00001 to +DATA/FOA12_CL/DATAFILE/system.260.1141287925
channel c1: reading from backup piece PROD_full_292fta7a_73_1
channel c1: piece handle=PROD_full_292fta7a_73_1 tag=WEEEKLY_FULL_DB_MANUAL
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:25
channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00004 to +DATA/FOA12_CL/DATAFILE/undotbs1.262.1141287985
channel c1: reading from backup piece PROD_full_2c2fta92_76_1
channel c1: piece handle=PROD_full_2c2fta92_76_1 tag=WEEEKLY_FULL_DB_MANUAL
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:25
channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00006 to +DATA/FOA12_CL/F88928160802F303E0531405F40A09FE/DATAFILE/sysaux.264.1141288085
channel c1: reading from backup piece PROD_full_2b2fta7a_75_1
channel c1: piece handle=PROD_full_2b2fta7a_75_1 tag=WEEEKLY_FULL_DB_MANUAL
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:35
channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00008 to +DATA/FOA12_CL/FFA625F0B25727A2E0538D02000AD2F8/DATAFILE/sysaux.269.1141288455
channel c1: reading from backup piece PROD_full_2a2fta7a_74_1
channel c1: piece handle=PROD_full_2a2fta7a_74_1 tag=WEEEKLY_FULL_DB_MANUAL
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:35
channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00009 to +DATA/FOA12_CL/DATAFILE/users.271.1141288711
channel c1: reading from backup piece PROD_full_2f2ftaau_79_1
channel c1: piece handle=PROD_full_2f2ftaau_79_1 tag=WEEEKLY_FULL_DB_MANUAL
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:01
channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00010 to +DATA/FOA12_CL/FFA625F0B25727A2E0538D02000AD2F8/DATAFILE/users.272.1141288711
channel c1: reading from backup piece PROD_full_2g2ftab1_80_1
channel c1: piece handle=PROD_full_2g2ftab1_80_1 tag=WEEEKLY_FULL_DB_MANUAL
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:01
channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00005 to +DATA/FOA12_CL/F88928160802F303E0531405F40A09FE/DATAFILE/system.265.1141288085
channel c1: reading from backup piece PROD_full_2e2ftaaq_78_1
channel c1: piece handle=PROD_full_2e2ftaaq_78_1 tag=WEEEKLY_FULL_DB_MANUAL
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:07
channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00007 to +DATA/FOA12_CL/FFA625F0B25727A2E0538D02000AD2F8/DATAFILE/system.268.1141288455
channel c1: reading from backup piece PROD_full_2d2ftaap_77_1
channel c1: piece handle=PROD_full_2d2ftaap_77_1 tag=WEEEKLY_FULL_DB_MANUAL
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:07
channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00003 to +DATA/FOA12_CL/DATAFILE/sysaux.261.1141287949
channel c1: reading from backup piece PROD_full_282fta7a_72_1
channel c1: piece handle=PROD_full_282fta7a_72_1 tag=WEEEKLY_FULL_DB_MANUAL
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:55
Finished restore at 06-JAN-24

Starting recover at 06-JAN-24

starting media recovery

archived log for thread 1 with sequence 24 is already on disk as file +RECO/FOA12_CL/ARCHIVELOG/2024_01_06/thread_1_seq_24.292.1157540209
archived log file name=+RECO/FOA12_CL/ARCHIVELOG/2024_01_06/thread_1_seq_24.292.1157540209 thread=1 sequence=24
media recovery complete, elapsed time: 00:00:01
Finished recover at 06-JAN-24

released channel: c1

7) Open the database with the RESETLOGS option after restore and recovery is complete

SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

Database would be successfully up now.

Change DB id and name as needed.

cd $ORACLE_HOME/bin

$ nid TARGET=SYS/<password>@<service> DBNAME=<NEW DB name>

$ nid TARGET=SYS/<password> DBNAME=<NEW DB name>

If you like please follow and comment

Restore database from a RMAN backup taken on the OCI Object Storage to new Host