Description:-
In this article we are going to explain the step by step configuration details to duplicate a database from oci object storage.
Environment Details:-
On-Permise (dbwr19c) – > Oci object storage -> Onpermise(ociclone)
Backup Source Database using rman from On-premise to oci cloud object storage:-
[oracle@Prod21 ~]$ . .19c.env
[oracle@Prod21 ~]$ echo $ORACLE_SID
dbwr19c
[oracle@Prod21 ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 – Production on Tue Jul 28 21:03:32 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: DBWR19C (DBID=3036258574)
RMAN> SET ENCRYPTION ON IDENTIFIED BY ‘Admin#123’ ONLY;
executing command: SET encryption
using target database control file instead of recovery catalog
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;
}2> 3> 4> 5> 6> 7> 8> 9>
allocated channel: c1
channel c1: SID=58 device type=SBT_TAPE
channel c1: Oracle Database Backup Service Library VER=19.0.0.1
Starting backup at 28-JUL-20
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
channel c1: starting piece 1 at 28-JUL-20
channel c1: finished piece 1 at 28-JUL-20
piece handle=0jv6fdtr_1_1 tag=TAG20200728T210354 comment=API Version 2.0,MMS Version 19.0.0.1
channel c1: backup set complete, elapsed time: 00:00:25
Finished backup at 28-JUL-20
Starting backup at 28-JUL-20
channel c1: starting compressed full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u02/app/oracle/oradata/DBWR19C/system01.dbf
input datafile file number=00003 name=/u02/app/oracle/oradata/DBWR19C/sysaux01.dbf
input datafile file number=00004 name=/u02/app/oracle/oradata/DBWR19C/undotbs01.dbf
input datafile file number=00007 name=/u02/app/oracle/oradata/DBWR19C/users01.dbf
channel c1: starting piece 1 at 28-JUL-20
channel c1: finished piece 1 at 28-JUL-20
piece handle=0kv6fdul_1_1 tag=TAG20200728T210421 comment=API Version 2.0,MMS Version 19.0.0.1
channel c1: backup set complete, elapsed time: 00:05:35
Finished backup at 28-JUL-20
Starting backup at 28-JUL-20
current log archived
channel c1: starting archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=5 RECID=1 STAMP=1046814775
input archived log thread=1 sequence=6 RECID=2 STAMP=1046820960
input archived log thread=1 sequence=7 RECID=3 STAMP=1046821979
input archived log thread=1 sequence=8 RECID=4 STAMP=1046822154
input archived log thread=1 sequence=9 RECID=5 STAMP=1046822157
input archived log thread=1 sequence=10 RECID=6 STAMP=1046822193
input archived log thread=1 sequence=11 RECID=7 STAMP=1046822275
input archived log thread=1 sequence=12 RECID=8 STAMP=1046822840
channel c1: starting piece 1 at 28-JUL-20
channel c1: finished piece 1 at 28-JUL-20
piece handle=0lv6fe96_1_1 tag=TAG20200728T210958 comment=API Version 2.0,MMS Version 19.0.0.1
channel c1: backup set complete, elapsed time: 00:06:05
channel c1: starting archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=9 STAMP=1046982351
input archived log thread=1 sequence=2 RECID=10 STAMP=1046984997
channel c1: starting piece 1 at 28-JUL-20
channel c1: finished piece 1 at 28-JUL-20
piece handle=0mv6fekj_1_1 tag=TAG20200728T210958 comment=API Version 2.0,MMS Version 19.0.0.1
channel c1: backup set complete, elapsed time: 00:00:15
Finished backup at 28-JUL-20
Starting Control File and SPFILE Autobackup at 28-JUL-20
piece handle=c-3036258574-20200728-00 comment=API Version 2.0,MMS Version 19.0.0.1
Finished Control File and SPFILE Autobackup at 28-JUL-20
released channel: c1
RMAN>
Target Database Pre-steps:-
Create PFILE from source db and Edit for Target db
Copy Password for Clone Database
[oracle@Prod21 dbs]$ cp orapwdbwr19c orapwociclone
Create PFILE from source db and Edit for Target db
[oracle@Prod21 dbs]$ cp initdbwr19c.ora initociclone.ora
[oracle@Prod21 dbs]$ cat initociclone.ora
*.audit_file_dest=’/u02/app/oracle/admin/ociclone/adump’
*.audit_trail=’db’
*.compatible=’19.0.0′
*.control_files=’/u02/app/oracle/oradata/ociclone/control01.ctl’
*.db_block_size=8192
*.db_name=’ociclone’
*.db_recovery_file_dest=’/u02/app/oracle/fast_recovery_area’
*.db_recovery_file_dest_size=8256m
*.diagnostic_dest=’/u02/app/oracle’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ocicloneXDB)’
*.log_archive_format=’%t_%s_%r.dbf’
*.nls_language=’AMERICAN’
*.nls_territory=’AMERICA’
*.open_cursors=300
*.pga_aggregate_target=397m
*.processes=300
*.remote_login_passwordfile=’EXCLUSIVE’
*.sga_target=1188m
*.undo_tablespace=’UNDOTBS1′
*.db_file_name_convert=’/u02/app/oracle/oradata/DBWR19C/’,’/u02/app/oracle/oradata/ociclone/’
*.log_file_name_convert=’/u02/app/oracle/oradata/DBWR19C/’,’/u02/app/oracle/oradata/ociclone/’
Create any directories necessary for start the duplicate database.
[oracle@Prod21 dbwr19c]$ cd /u02/app/oracle/admin
[oracle@Prod21 admin]$ mkdir ociclone
[oracle@Prod21 admin]$ cd ociclone/
[oracle@Prod21 ociclone]$ mkdir adump pfile dpdump
[oracle@Prod21 ociclone]$ mkdir -p /u02/app/oracle/oradata/ociclone/
[oracle@Prod21 ociclone]$
Add TNS entry and listener
[oracle@Prod21 ~]$ lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 – Production on 28-JUL-2020 22:20:25
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Prod21)(PORT=1521)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 – Production
Start Date 28-JUL-2020 21:23:12
Uptime 0 days 0 hr. 57 min. 13 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u02/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Listener Log File /u02/app/oracle/diag/tnslsnr/Prod21/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Prod21)(PORT=1521)))
Services Summary…
Service “dbwr19c” has 2 instance(s).
Instance “dbwr19c”, status UNKNOWN, has 1 handler(s) for this service…
Instance “dbwr19c”, status READY, has 1 handler(s) for this service…
Service “dbwr19cXDB” has 1 instance(s).
Instance “dbwr19c”, status READY, has 1 handler(s) for this service…
Service “ociclone” has 2 instance(s).
Instance “ociclone”, status UNKNOWN, has 1 handler(s) for this service…
Instance “ociclone”, status READY, has 1 handler(s) for this service…
Service “ocicloneXDB” has 1 instance(s).
Instance “ociclone”, status READY, has 1 handler(s) for this service…
The command completed successfully
[oracle@Prod21 ~]$ tnsping ociclone
TNS Ping Utility for Linux: Version 19.0.0.0.0 – Production on 28-JUL-2020 22:20:48
Copyright (c) 1997, 2019, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = prod21)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ociclone)))
OK (0 msec)
[oracle@Prod21 ~]$ tnsping dbwr19c
TNS Ping Utility for Linux: Version 19.0.0.0.0 – Production on 28-JUL-2020 22:20:54
Copyright (c) 1997, 2019, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = prod21)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = dbwr19c)))
OK (10 msec)
Connect to the duplicate instance.
[oracle@Prod21 ~]$ . .19c.env
[oracle@Prod21 ~]$ export ORACLE_SID=ociclone
[oracle@Prod21 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 – Production on Tue Jul 28 20:52:07 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
Start the database in NOMOUNT mode.
SQL> startup nomount
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
SQL>
Execute the duplicate command
[oracle@Prod21 admin]$ export ORACLE_SID=ociclone
[oracle@Prod21 admin]$ rman target sys/oracle@dbwr19c auxiliary sys/oracle@ociclone
Recovery Manager: Release 19.0.0.0.0 – Production on Tue Jul 28 21:23:28 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: DBWR19C (DBID=3036258574)
connected to auxiliary database: OCICLONE (not mounted)
RMAN> set decryption identified by ‘Admin#123’;
executing command: SET decryption
RMAN>
run
{
allocate AUXILIARY 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 AUXILIARY 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)’;
duplicate database dbwr19c dbid 3036258574 to ociclone;
}
allocated channel: c1
channel c1: SID=39 device type=SBT_TAPE
channel c1: Oracle Database Backup Service Library VER=19.0.0.1
allocated channel: c2
channel c2: SID=40 device type=SBT_TAPE
channel c2: Oracle Database Backup Service Library VER=19.0.0.1
Starting Duplicate Db at 28-JUL-20
current log archived
contents of Memory Script:
{
sql clone “create spfile from memory”;
}
executing Memory Script
sql statement: create spfile from memory
contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
Oracle instance shut down
connected to auxiliary database (not started)
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
allocated channel: c1
channel c1: SID=34 device type=SBT_TAPE
channel c1: Oracle Database Backup Service Library VER=19.0.0.1
allocated channel: c2
channel c2: SID=36 device type=SBT_TAPE
channel c2: Oracle Database Backup Service Library VER=19.0.0.1
contents of Memory Script:
{
sql clone “alter system set db_name =
”DBWR19C” comment=
”Modified by RMAN duplicate” scope=spfile”;
sql clone “alter system set db_unique_name =
”ociclone” comment=
”Modified by RMAN duplicate” scope=spfile”;
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile;
alter clone database mount;
}
executing Memory Script
sql statement: alter system set db_name = ”DBWR19C” comment= ”Modified by RMAN duplicate” scope=spfile
sql statement: alter system set db_unique_name = ”ociclone” comment= ”Modified by RMAN duplicate” scope=spfile
Oracle instance shut down
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
allocated channel: c1
channel c1: SID=34 device type=SBT_TAPE
channel c1: Oracle Database Backup Service Library VER=19.0.0.1
allocated channel: c2
channel c2: SID=36 device type=SBT_TAPE
channel c2: Oracle Database Backup Service Library VER=19.0.0.1
Starting restore at 28-JUL-20
channel c1: starting datafile backup set restore
channel c1: restoring control file
channel c1: reading from backup piece c-3036258574-20200728-00
channel c1: piece handle=c-3036258574-20200728-00 tag=TAG20200728T211618
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:07
output file name=/u02/app/oracle/oradata/ociclone/control01.ctl
Finished restore at 28-JUL-20
database mounted
contents of Memory Script:
{
sql clone “alter system set db_name =
”DBWR19C” comment=
”Modified by RMAN duplicate” scope=spfile”;
sql clone “alter system set db_unique_name =
”ociclone” comment=
”Modified by RMAN duplicate” scope=spfile”;
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile;
alter clone database mount;
}
executing Memory Script
sql statement: alter system set db_name = ”DBWR19C” comment= ”Modified by RMAN duplicate” scope=spfile
sql statement: alter system set db_unique_name = ”ociclone” comment= ”Modified by RMAN duplicate” scope=spfile
Oracle instance shut down
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
allocated channel: c1
channel c1: SID=34 device type=SBT_TAPE
channel c1: Oracle Database Backup Service Library VER=19.0.0.1
allocated channel: c2
channel c2: SID=36 device type=SBT_TAPE
channel c2: Oracle Database Backup Service Library VER=19.0.0.1
Starting restore at 28-JUL-20
channel c1: starting datafile backup set restore
channel c1: restoring control file
channel c1: reading from backup piece c-3036258574-20200728-00
channel c1: piece handle=c-3036258574-20200728-00 tag=TAG20200728T211618
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:07
output file name=/u02/app/oracle/oradata/ociclone/control01.ctl
Finished restore at 28-JUL-20
database mounted
contents of Memory Script:
{
set until scn 2302185;
set newname for datafile 1 to
“/u02/app/oracle/oradata/ociclone/system01.dbf”;
set newname for datafile 3 to
“/u02/app/oracle/oradata/ociclone/sysaux01.dbf”;
set newname for datafile 4 to
“/u02/app/oracle/oradata/ociclone/undotbs01.dbf”;
set newname for datafile 7 to
“/u02/app/oracle/oradata/ociclone/users01.dbf”;
restore
clone database
;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 28-JUL-20
channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00001 to /u02/app/oracle/oradata/ociclone/system01.dbf
channel c1: restoring datafile 00003 to /u02/app/oracle/oradata/ociclone/sysaux01.dbf
channel c1: restoring datafile 00004 to /u02/app/oracle/oradata/ociclone/undotbs01.dbf
channel c1: restoring datafile 00007 to /u02/app/oracle/oradata/ociclone/users01.dbf
channel c1: reading from backup piece 0kv6fdul_1_1
channel c1: piece handle=0kv6fdul_1_1 tag=TAG20200728T210421
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:02:05
Finished restore at 28-JUL-20
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=1046986691 file name=/u02/app/oracle/oradata/ociclone/system01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=1046986691 file name=/u02/app/oracle/oradata/ociclone/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=7 STAMP=1046986691 file name=/u02/app/oracle/oradata/ociclone/undotbs01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=8 STAMP=1046986691 file name=/u02/app/oracle/oradata/ociclone/users01.dbf
contents of Memory Script:
{
set until scn 2302185;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 28-JUL-20
starting media recovery
archived log for thread 1 with sequence 2 is already on disk as file /u02/app/oracle/fast_recovery_area/DBWR19C/archivelog/2020_07_28/o1_mf_1_2_hl0kpfsm_.arc
archived log for thread 1 with sequence 3 is already on disk as file /u02/app/oracle/fast_recovery_area/DBWR19C/archivelog/2020_07_28/o1_mf_1_3_hl0ltt58_.arc
archived log for thread 1 with sequence 4 is already on disk as file /u02/app/oracle/fast_recovery_area/DBWR19C/archivelog/2020_07_28/o1_mf_1_4_hl0m539x_.arc
archived log file name=/u02/app/oracle/fast_recovery_area/DBWR19C/archivelog/2020_07_28/o1_mf_1_2_hl0kpfsm_.arc thread=1 sequence=2
archived log file name=/u02/app/oracle/fast_recovery_area/DBWR19C/archivelog/2020_07_28/o1_mf_1_3_hl0ltt58_.arc thread=1 sequence=3
archived log file name=/u02/app/oracle/fast_recovery_area/DBWR19C/archivelog/2020_07_28/o1_mf_1_4_hl0m539x_.arc thread=1 sequence=4
media recovery complete, elapsed time: 00:00:01
Finished recover at 28-JUL-20
released channel: c1
released channel: c2
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
contents of Memory Script:
{
sql clone “alter system set db_name =
”OCICLONE” comment=
”Reset to original value by RMAN” scope=spfile”;
sql clone “alter system reset db_unique_name scope=spfile”;
}
executing Memory Script
sql statement: alter system set db_name = ”OCICLONE” comment= ”Reset to original value by RMAN” scope=spfile
sql statement: alter system reset db_unique_name scope=spfile
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
sql statement: CREATE CONTROLFILE REUSE SET DATABASE “OCICLONE” RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( ‘/u02/app/oracle/oradata/ociclone/redo01.log’ ) SIZE 200 M REUSE,
GROUP 2 ( ‘/u02/app/oracle/oradata/ociclone/redo02.log’ ) SIZE 200 M REUSE,
GROUP 3 ( ‘/u02/app/oracle/oradata/ociclone/redo03.log’ ) SIZE 200 M REUSE
DATAFILE
‘/u02/app/oracle/oradata/ociclone/system01.dbf’
CHARACTER SET AL32UTF8
contents of Memory Script:
{
set newname for tempfile 1 to
“/u02/app/oracle/oradata/ociclone/temp01.dbf”;
switch clone tempfile all;
catalog clone datafilecopy “/u02/app/oracle/oradata/ociclone/sysaux01.dbf”,
“/u02/app/oracle/oradata/ociclone/undotbs01.dbf”,
“/u02/app/oracle/oradata/ociclone/users01.dbf”;
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u02/app/oracle/oradata/ociclone/temp01.dbf in control file
cataloged datafile copy
datafile copy file name=/u02/app/oracle/oradata/ociclone/sysaux01.dbf RECID=1 STAMP=1046986715
cataloged datafile copy
datafile copy file name=/u02/app/oracle/oradata/ociclone/undotbs01.dbf RECID=2 STAMP=1046986715
cataloged datafile copy
datafile copy file name=/u02/app/oracle/oradata/ociclone/users01.dbf RECID=3 STAMP=1046986717
datafile 3 switched to datafile copy
input datafile copy RECID=1 STAMP=1046986715 file name=/u02/app/oracle/oradata/ociclone/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=2 STAMP=1046986715 file name=/u02/app/oracle/oradata/ociclone/undotbs01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=3 STAMP=1046986717 file name=/u02/app/oracle/oradata/ociclone/users01.dbf
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Cannot remove created server parameter file
Finished Duplicate Db at 28-JUL-20
Verify DB connectivity
[oracle@Prod21 admin]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 – Production on Tue Jul 28 21:39:31 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> select name,open_mode from v$database;
NAME OPEN_MODE
——— ——————–
OCICLONE READ WRITE
Rman Backup-Based Duplication from oci Object storage is completed successfully
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