Description:-
In this article we are going to see how to restore a database from Object storage to On-premise server.
Step by step Backing Up A Database From On-Premise To Object Storage Using RMAN
Click Here https://oracledbwr.com/backing-up-a-database-from-on-premise-to-object-storage-using-rman/
Step:-1 Prepare backup configuration:
Please note that when you make a backup to the cloud, this SBT library requires encryption of the backupset, so use SET ENCRYPTION when you take a backup.
RMAN> Configure default device type to sbt;
new RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO ‘SBT_TAPE’;
new RMAN configuration parameters are successfully stored
RMAN> configure device type sbt parallelism 4;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE ‘SBT_TAPE’ PARALLELISM 4 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters are successfully stored
RMAN> configure compression algorithm ‘MEDIUM’;
new RMAN configuration parameters:
CONFIGURE COMPRESSION ALGORITHM ‘MEDIUM’ AS OF RELEASE ‘DEFAULT’ OPTIMIZE FOR LOAD TRUE;
new RMAN configuration parameters are successfully stored
RMAN> configure CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
RMAN> set encryption on identified by ‘Admin#123’ only;
executing command: SET encryption
RMAN> configure channel device type sbt parms=’SBT_LIBRARY=/u02/app/oracle/product/19.0.0/dbhome_1/lib/libopc.so,SBT_PARMS=(OPC_PFILE=/u02/app/oracle/product/19.0.0/dbhome_1/dbs/opcdbwr.ora)’;
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE ‘SBT_TAPE’ PARMS ‘SBT_LIBRARY=/u02/app/oracle/product/19.0.0/dbhome_1/lib/libopc.so,SBT_PARMS=(OPC_PFILE=/u02/app/oracle/product/19.0.0/dbhome_1/dbs/opcdbwr.ora)’;
new RMAN configuration parameters are successfully stored
Stpe:-2 Take Fresh Rman backup to object Storage.
RMAN> run
{
allocate channel c1 DEVICE TYPE ‘SBT_TAPE’ PARMS
‘SBT_LIBRARY=/u02/app/oracle/product/19.0.0/dbhome_1/lib/libopc.so,
ENV=(OPC_PFILE=/u02/app/oracle/product/19.0.0/dbhome_1/dbs/opcdbwr.ora)’;
backup current controlfile;
backup as compressed backupset full database;
backup archivelog all;
}
RMAN>
Starting backup at 27-JUL-20
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=90 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Oracle Database Backup Service Library VER=19.0.0.1
allocated channel: ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_2: SID=87 device type=SBT_TAPE
channel ORA_SBT_TAPE_2: Oracle Database Backup Service Library VER=19.0.0.1
allocated channel: ORA_SBT_TAPE_3
channel ORA_SBT_TAPE_3: SID=89 device type=SBT_TAPE
channel ORA_SBT_TAPE_3: Oracle Database Backup Service Library VER=19.0.0.1
allocated channel: ORA_SBT_TAPE_4
channel ORA_SBT_TAPE_4: SID=84 device type=SBT_TAPE
channel ORA_SBT_TAPE_4: Oracle Database Backup Service Library VER=19.0.0.1
channel ORA_SBT_TAPE_1: starting full datafile backup set
channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_SBT_TAPE_1: starting piece 1 at 27-JUL-20
channel ORA_SBT_TAPE_1: finished piece 1 at 27-JUL-20
piece handle=03v6afj1_1_1 tag=TAG20200727T000137 comment=API Version 2.0,MMS Version 19.0.0.1
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:26
Finished backup at 27-JUL-20
Starting Control File and SPFILE Autobackup at 27-JUL-20
piece handle=c-3036258574-20200727-00 comment=API Version 2.0,MMS Version 19.0.0.1
Finished Control File and SPFILE Autobackup at 27-JUL-20
RMAN>
Starting backup at 27-JUL-20
using channel ORA_SBT_TAPE_1
using channel ORA_SBT_TAPE_2
using channel ORA_SBT_TAPE_3
using channel ORA_SBT_TAPE_4
channel ORA_SBT_TAPE_1: starting compressed full datafile backup set
channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u02/app/oracle/oradata/DBWR19C/system01.dbf
channel ORA_SBT_TAPE_1: starting piece 1 at 27-JUL-20
channel ORA_SBT_TAPE_2: starting compressed full datafile backup set
channel ORA_SBT_TAPE_2: specifying datafile(s) in backup set
input datafile file number=00003 name=/u02/app/oracle/oradata/DBWR19C/sysaux01.dbf
channel ORA_SBT_TAPE_2: starting piece 1 at 27-JUL-20
channel ORA_SBT_TAPE_3: starting compressed full datafile backup set
channel ORA_SBT_TAPE_3: specifying datafile(s) in backup set
input datafile file number=00004 name=/u02/app/oracle/oradata/DBWR19C/undotbs01.dbf
channel ORA_SBT_TAPE_3: starting piece 1 at 27-JUL-20
channel ORA_SBT_TAPE_4: starting compressed full datafile backup set
channel ORA_SBT_TAPE_4: specifying datafile(s) in backup set
input datafile file number=00007 name=/u02/app/oracle/oradata/DBWR19C/users01.dbf
channel ORA_SBT_TAPE_4: starting piece 1 at 27-JUL-20
channel ORA_SBT_TAPE_4: finished piece 1 at 27-JUL-20
piece handle=08v6afkc_1_1 tag=TAG20200727T000220 comment=API Version 2.0,MMS Version 19.0.0.1
channel ORA_SBT_TAPE_4: backup set complete, elapsed time: 00:00:24
channel ORA_SBT_TAPE_3: finished piece 1 at 27-JUL-20
piece handle=07v6afkc_1_1 tag=TAG20200727T000220 comment=API Version 2.0,MMS Version 19.0.0.1
channel ORA_SBT_TAPE_3: backup set complete, elapsed time: 00:00:34
channel ORA_SBT_TAPE_2: finished piece 1 at 27-JUL-20
piece handle=06v6afkc_1_1 tag=TAG20200727T000220 comment=API Version 2.0,MMS Version 19.0.0.1
channel ORA_SBT_TAPE_2: backup set complete, elapsed time: 00:02:24
channel ORA_SBT_TAPE_1: finished piece 1 at 27-JUL-20
piece handle=05v6afkc_1_1 tag=TAG20200727T000220 comment=API Version 2.0,MMS Version 19.0.0.1
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:04:44
Finished backup at 27-JUL-20
Starting Control File and SPFILE Autobackup at 27-JUL-20
piece handle=c-3036258574-20200727-01 comment=API Version 2.0,MMS Version 19.0.0.1
Finished Control File and SPFILE Autobackup at 27-JUL-20
RMAN>
Starting backup at 27-JUL-20
current log archived
using channel ORA_SBT_TAPE_1
using channel ORA_SBT_TAPE_2
using channel ORA_SBT_TAPE_3
using channel ORA_SBT_TAPE_4
channel ORA_SBT_TAPE_1: starting archived log backup set
channel ORA_SBT_TAPE_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=5 RECID=1 STAMP=1046814775
channel ORA_SBT_TAPE_1: starting piece 1 at 27-JUL-20
channel ORA_SBT_TAPE_2: starting archived log backup set
channel ORA_SBT_TAPE_2: specifying archived log(s) in backup set
input archived log thread=1 sequence=6 RECID=2 STAMP=1046820960
channel ORA_SBT_TAPE_2: starting piece 1 at 27-JUL-20
channel ORA_SBT_TAPE_3: starting archived log backup set
channel ORA_SBT_TAPE_3: specifying archived log(s) in backup set
input archived log thread=1 sequence=7 RECID=3 STAMP=1046821979
input archived log thread=1 sequence=8 RECID=4 STAMP=1046822154
channel ORA_SBT_TAPE_3: starting piece 1 at 27-JUL-20
channel ORA_SBT_TAPE_4: starting archived log backup set
channel ORA_SBT_TAPE_4: specifying archived log(s) in backup set
input archived log thread=1 sequence=11 RECID=7 STAMP=1046822275
input archived log thread=1 sequence=12 RECID=8 STAMP=1046822840
channel ORA_SBT_TAPE_4: starting piece 1 at 27-JUL-20
channel ORA_SBT_TAPE_4: finished piece 1 at 27-JUL-20
piece handle=0dv6aftp_1_1 tag=TAG20200727T000720 comment=API Version 2.0,MMS Version 19.0.0.1
channel ORA_SBT_TAPE_4: backup set complete, elapsed time: 00:00:07
channel ORA_SBT_TAPE_4: starting archived log backup set
channel ORA_SBT_TAPE_4: specifying archived log(s) in backup set
input archived log thread=1 sequence=9 RECID=5 STAMP=1046822157
input archived log thread=1 sequence=10 RECID=6 STAMP=1046822193
channel ORA_SBT_TAPE_4: starting piece 1 at 27-JUL-20
channel ORA_SBT_TAPE_3: finished piece 1 at 27-JUL-20
piece handle=0cv6aftp_1_1 tag=TAG20200727T000720 comment=API Version 2.0,MMS Version 19.0.0.1
channel ORA_SBT_TAPE_3: backup set complete, elapsed time: 00:00:22
channel ORA_SBT_TAPE_4: finished piece 1 at 27-JUL-20
piece handle=0ev6afu0_1_1 tag=TAG20200727T000720 comment=API Version 2.0,MMS Version 19.0.0.1
channel ORA_SBT_TAPE_4: backup set complete, elapsed time: 00:00:15
channel ORA_SBT_TAPE_2: finished piece 1 at 27-JUL-20
piece handle=0bv6aftp_1_1 tag=TAG20200727T000720 comment=API Version 2.0,MMS Version 19.0.0.1
channel ORA_SBT_TAPE_2: backup set complete, elapsed time: 00:02:22
channel ORA_SBT_TAPE_1: finished piece 1 at 27-JUL-20
piece handle=0av6aftp_1_1 tag=TAG20200727T000720 comment=API Version 2.0,MMS Version 19.0.0.1
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:04:22
Finished backup at 27-JUL-20
Starting Control File and SPFILE Autobackup at 27-JUL-20
piece handle=c-3036258574-20200727-02 comment=API Version 2.0,MMS Version 19.0.0.1
Finished Control File and SPFILE Autobackup at 27-JUL-20
SQL> select dbid from v$database;
DBID
———-
3036258574
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.3.0.0.0
Step:- 3 Delete the database
[oracle@Prod21 ~]$ cd /u02/app/oracle/oradata/DBWR19C/
[oracle@Prod21 DBWR19C]$ ls -lrt
total 2464460
-rw-r—–. 1 oracle oinstall 33562624 Jul 26 21:56 temp01.dbf
-rw-r—–. 1 oracle oinstall 209715712 Jul 26 23:57 redo02.log
-rw-r—–. 1 oracle oinstall 209715712 Jul 27 00:07 redo03.log
-rw-r—–. 1 oracle oinstall 954212352 Jul 27 00:12 system01.dbf
-rw-r—–. 1 oracle oinstall 566239232 Jul 27 00:12 sysaux01.dbf
-rw-r—–. 1 oracle oinstall 5251072 Jul 27 00:12 users01.dbf
-rw-r—–. 1 oracle oinstall 356524032 Jul 27 00:12 undotbs01.dbf
-rw-r—–. 1 oracle oinstall 209715712 Jul 27 00:13 redo01.log
-rw-r—–. 1 oracle oinstall 10600448 Jul 27 00:13 control01.ctl
[oracle@Prod21 DBWR19C]$ rm -rf *
[oracle@Prod21 dbs]$ mv spfiledbwr19c.ora spfiledbwr19c.ora_bkp
Step:- 4 Restore Database From Object storage
RMAN can restore the database. In the example below we assume complete loss of the database, including controlfile and spfile.
Rman Target /
Recovery Manager: Release 19.0.0.0.0 – Production on Mon Jul 27 00:16:53 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
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file ‘/u02/app/oracle/product/19.0.0/dbhome_1/dbs/initdbwr19c.ora’
starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started
Total System Global Area 1073737800 bytes
Fixed Size 8904776 bytes
Variable Size 276824064 bytes
Database Buffers 784334848 bytes
Redo Buffers 3674112 bytes
RMAN> set DBID=3036258574;
executing command: SET DBID
RMAN> set decryption identified by ‘Admin#123’;
executing command: SET decryption
Spfile Restore
RMAN> run
{
allocate channel c1 DEVICE TYPE ‘SBT_TAPE’ PARMS
‘SBT_LIBRARY=/u02/app/oracle/product/19.0.0/dbhome_1/lib/libopc.so,
ENV=(OPC_PFILE=/u02/app/oracle/product/19.0.0/dbhome_1/dbs/opcdbwr.ora)’;
restore spfile from autobackup;
}
allocated channel: c1
channel c1: SID=36 device type=SBT_TAPE
channel c1: Oracle Database Backup Service Library VER=19.0.0.1
Starting restore at 27-JUL-20
channel c1: looking for AUTOBACKUP on day: 20200727
channel c1: AUTOBACKUP found: c-3036258574-20200727-02
channel c1: restoring spfile from AUTOBACKUP c-3036258574-20200727-02
channel c1: SPFILE restore from AUTOBACKUP complete
Finished restore at 27-JUL-20
released channel: c1
RMAN> startup nomount force;
Oracle instance started
Total System Global Area 1258290752 bytes
Fixed Size 8896064 bytes
Variable Size 318767104 bytes
Database Buffers 922746880 bytes
Redo Buffers 7880704 bytes
Controlfile Restore
RMAN> run
{
allocate channel c1 DEVICE TYPE ‘SBT_TAPE’ PARMS
‘SBT_LIBRARY=/u02/app/oracle/product/19.0.0/dbhome_1/lib/libopc.so,
ENV=(OPC_PFILE=/u02/app/oracle/product/19.0.0/dbhome_1/dbs/opcdbwr.ora)’;
restore controlfile from autobackup;
}
allocated channel: c1
channel c1: SID=38 device type=SBT_TAPE
channel c1: Oracle Database Backup Service Library VER=19.0.0.1
Starting restore at 27-JUL-20
channel c1: looking for AUTOBACKUP on day: 20200727
channel c1: AUTOBACKUP found: c-3036258574-20200727-02
channel c1: restoring control file from AUTOBACKUP c-3036258574-20200727-02
channel c1: control file restore from AUTOBACKUP complete
output file name=/u02/app/oracle/oradata/DBWR19C/control01.ctl
output file name=/u02/app/oracle/fast_recovery_area/DBWR19C/control02.ctl
Finished restore at 27-JUL-20
released channel: c1
RMAN> alter database mount;
Statement processed
Database Restore and recover
RMAN> run
{
allocate channel c1 DEVICE TYPE ‘SBT_TAPE’ PARMS
‘SBT_LIBRARY=/u02/app/oracle/product/19.0.0/dbhome_1/lib/libopc.so,
ENV=(OPC_PFILE=/u02/app/oracle/product/19.0.0/dbhome_1/dbs/opcdbwr.ora)’;
allocate channel c2 DEVICE TYPE ‘SBT_TAPE’ PARMS
‘SBT_LIBRARY=/u02/app/oracle/product/19.0.0/dbhome_1/lib/libopc.so,
ENV=(OPC_PFILE=/u02/app/oracle/product/19.0.0/dbhome_1/dbs/opcdbwr.ora)’;
restore database;
recover database;
}
allocated channel: c1
channel c1: SID=44 device type=SBT_TAPE
channel c1: Oracle Database Backup Service Library VER=19.0.0.1
allocated channel: c2
channel c2: SID=45 device type=SBT_TAPE
channel c2: Oracle Database Backup Service Library VER=19.0.0.1
Starting restore at 27-JUL-20
Starting implicit crosscheck backup at 27-JUL-20
Finished implicit crosscheck backup at 27-JUL-20
Starting implicit crosscheck copy at 27-JUL-20
Finished implicit crosscheck copy at 27-JUL-20
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 00007 to /u02/app/oracle/oradata/DBWR19C/users01.dbf
channel c1: reading from backup piece 08v6afkc_1_1
channel c2: starting datafile backup set restore
channel c2: specifying datafile(s) to restore from backup set
channel c2: restoring datafile 00004 to /u02/app/oracle/oradata/DBWR19C/undotbs01.dbf
channel c2: reading from backup piece 07v6afkc_1_1
channel c1: piece handle=08v6afkc_1_1 tag=TAG20200727T000220
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:03
channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00003 to /u02/app/oracle/oradata/DBWR19C/sysaux01.dbf
channel c1: reading from backup piece 06v6afkc_1_1
channel c2: piece handle=07v6afkc_1_1 tag=TAG20200727T000220
channel c2: restored backup piece 1
channel c2: restore complete, elapsed time: 00:00:05
channel c2: starting datafile backup set restore
channel c2: specifying datafile(s) to restore from backup set
channel c2: restoring datafile 00001 to /u02/app/oracle/oradata/DBWR19C/system01.dbf
channel c2: reading from backup piece 05v6afkc_1_1
channel c1: piece handle=06v6afkc_1_1 tag=TAG20200727T000220
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:36
Finished restore at 27-JUL-20
Starting recover at 27-JUL-20
starting media recovery
archived log for thread 1 with sequence 12 is already on disk as file /u02/app/oracle/fast_recovery_area/DBWR19C/archivelog/2020_07_27/o1_mf_1_12_hkvmc0qx_.arc
archived log file name=/u02/app/oracle/fast_recovery_area/DBWR19C/archivelog/2020_07_27/o1_mf_1_12_hkvmc0qx_.arc thread=1 sequence=12
unable to find archived log
archived log thread=1 sequence=13
released channel: c1
released channel: c2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/27/2020 00:33:26
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 13 and starting SCN of 2196474
RMAN> alter database open resetlogs;
Statement processed
Connect with me:-
Telegram App:https://t.me/oracledbwr
LinkedIn:https://www.linkedin.com/in/hariprasathdba
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page : https://www.facebook.com/dbahariprasath/?
Twitter : https://twitter.com/oracledbwr