This blog post describes the lift and shift of an on-prem Oracle 11g Enterprise Edition to Oracle Cloud Infrastructure by using Oracle RMAN paired with OCI Object Storage. Works for other versions > Oracle 11g / Enterprise Edition too (the tablespace encryption method may change).
Architecture
Q&A
What’s the motivation of the lift and shift of a good old on-prem Oracle 11g database to Oracle Database Service?
- Hardware runs out of lifecycle
- Out scoping of an on-prem database which is only used for several times to compare old data
- Regulatory, for example we have the keep and access the data for 10 yrs
- Changing from on-prem licenses to the license included model
- Upgrade tests for 19c and convert to Multitenancy Architecture
- Part of company’s cloud strategy
Why using the OCI database service instead of a cheaper compute instance?
Only the database service allows tablespace encryption without any additional costs in the license included model. From my point of view, this is a must when running Oracle databases outside of the on-prem datacenter in any cloud. And for this case, 11g to 11g, it fit’s best without any configuration overhead. And finally, the root OS access gives me flexibility.
Are the other methods available than using RMAN backup/restore/recovery with the Oracle Database Backup Service?
Sure, according the Oracle docs:
- Golden Gate
- Data Transfer Service
- Oracle Data Pump
- Database Migration Service
- RMAN Transportable Tablespaces
Why I like the Oracle Database Backup Cloud Service?
- The on-prem RMAN backups have to be encrypted, no encryption, no Database Backup Service.
- The configuration of the Backup Service Module is not really complicated, well documented and can be used 1:1 in on-prem and in the cloud. For 11.2.0.4,I need to install on-prem a patch to encrypt the backups (18339044 – RMAN-06770: backup encryption requires Enterprise Edition).
- The backup configuration can be done in advanced without any pressure, the target database can be recovered at any time. This reduces once the restore is done the downtime when the final move should be done. Backing up an Oracle database by RMAN locally and to the cloud can co-exist without any problems.
- The backup is stored in the Object Storage. With a replication policy, I can rebuild a new database in another region in an easy way.
- Oracle Database Backup Module allows proxy configurations to backup to the cloud.
Links and My Oracle Support Notes
- BACKUP AND RECOVERY IN ORACLE CLOUD FOR DOCKER CONTAINER DEPLOYED DATABASE (Doc ID 2772375.1)
- Oracle Database Tablespace Encryption Behavior in Oracle Cloud (Doc ID 2359020.1)
- https://docs.oracle.com/en-us/iaas/Content/Database/Tasks/mig-11g-11g.htm
- https://docs.oracle.com/en-us/iaas/Content/Database/Tasks/mig-onprembackup.htm
- https://docs.oracle.com/en/cloud/paas/db-backup-cloud/csdbb/installing-oracle-database-cloud-backup-module-oci-classic.html
- https://docs.oracle.com/en/cloud/paas/db-backup-cloud/csdbb/restoring-new-database-host.html
Migration Steps
- Create OCI Object Storage
- Configure on-prem database to use the Oracle Database Backup Service
- Create a new OCI Database Virtual Machine
- Configure OCI cloud database to use the Oracle Database Backup Service
- Clean up OCI cloud database
- Restore OCI cloud database from Object Store
- Encrypt tablespace
1. OCI Object Storage
A Object storage bucked called onprem-bucket is created in region eu-zurich-1:
2. Configure on-prem database to use the Oracle Database Backup Service
The installation of the Backup Service is described in this link here, you can download the OCI Backup Service Module: www.oracle.com/technetwork/database/availability/oracle-cloud-backup-2162729.html
On-Prem Database – Specifications
- 11.2.0.4 Enterprise Edition
- Database name DB11
- Oracle Linux 7.9
- About 60GB size
- Oracle Backup Service to Object Storage enabled and configured
- Regular RMAN inc0, inc1c and arc backups to the Cloud
- Enabled RMAN compression and encryption (for the usage of the Backup Service the license is included)
- Backup encrypted by password
On-Prem Database – Tablespaces and Datafiles
SQL> COL tablespace_name FORMAT a20 SQL> COL file_name FORMAT a70 SQL> SET LINES 300 SQL> SELECT tablespace_name, file_name 2 FROM dba_data_files 3 ORDER BY 1; TABLESPACE_NAME FILE_NAME -------------------- ---------------------------------------------------------------------- HRDATA /u02/oradata/DB11/DB11/datafile/o1_mf_hrdata_jq2x77dc_.dbf SHDATA /u02/oradata/DB11/DB11/datafile/o1_mf_shdata_jq4mqyyl_.dbf SOEDATA /u02/oradata/DB11/DB11/datafile/o1_mf_soedata_jqrfp7sy_.dbf SOEDATA /u02/oradata/DB11/DB11/datafile/o1_mf_soedata_jq2x7f6v_.dbf SOEDATA /u02/oradata/DB11/DB11/datafile/o1_mf_soedata_jqrfp4ol_.dbf SYSAUX /u02/oradata/DB11/DB11/datafile/o1_mf_sysaux_jpdvb315_.dbf SYSTEM /u02/oradata/DB11/DB11/datafile/o1_mf_system_jpdvb09z_.dbf UNDOTBS1 /u02/oradata/DB11/DB11/datafile/o1_mf_undotbs1_jpdvb513_.dbf USERS /u02/oradata/DB11/DB11/datafile/o1_mf_users_jpdvbbh5_.dbf 9 rows selected.
On-Prem Database – RMAN Backup to Oracle Cloud Infrastructure
Example RMAN Output where the media is the Oracle Cloud Infrastructure Object Storage in Zurich / Switzerland.
RMAN> LIST BACKUPSET 542; List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 542 Incr 0 13.23G SBT_TAPE 00:15:29 31-OCT-21 BP Key: 542 Status: AVAILABLE Compressed: YES Tag: TAG20211031T043005 Handle: /u01/app/oracle/admin/DB11/backup/inc0_DB11_2021-10-31_04-30-03_s544_p1 Media: objectstorage.eu-zurich-..cloud.com/n/zrq012abcaw/onprem-bucket List of Datafiles in backup set 542 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 1 0 Incr 1720448 31-OCT-21 /u02/oradata/DB11/DB11/datafile/o1_mf_system_jpdvb09z_.dbf 2 0 Incr 1720448 31-OCT-21 /u02/oradata/DB11/DB11/datafile/o1_mf_sysaux_jpdvb315_.dbf 3 0 Incr 1720448 31-OCT-21 /u02/oradata/DB11/DB11/datafile/o1_mf_undotbs1_jpdvb513_.dbf 4 0 Incr 1720448 31-OCT-21 /u02/oradata/DB11/DB11/datafile/o1_mf_users_jpdvbbh5_.dbf 5 0 Incr 1720448 31-OCT-21 /u02/oradata/DB11/DB11/datafile/o1_mf_hrdata_jq2x77dc_.dbf 6 0 Incr 1720448 31-OCT-21 /u02/oradata/DB11/DB11/datafile/o1_mf_soedata_jq2x7f6v_.dbf 7 0 Incr 1720448 31-OCT-21 /u02/oradata/DB11/DB11/datafile/o1_mf_shdata_jq4mqyyl_.dbf 8 0 Incr 1720448 31-OCT-21 /u02/oradata/DB11/DB11/datafile/o1_mf_soedata_jqrfp4ol_.dbf 9 0 Incr 1720448 31-OCT-21 /u02/oradata/DB11/DB11/datafile/o1_mf_soedata_jqrfp7sy_.dbf
3. Create a new OCI Database Virtual Machine
A new 11g Oracle Database virtual machine is created, license included. 11g is not available for Logical Volumes unfortunately, therefore we need to use Grid Infrastructure.
Cloud Database – Specifications
- 11.2.0.4 Enterprise Edition Database Virtual Machine
- Oracle Linux 7.9
- Grid Infrastructure (11g is not available for Logical Volumes unfortunately)
- Database Name is DB11
- Connected from the on-prem data center a) by VPN or b) by SSH-Tunnel via Bastion Host
Cloud Database – Controlfile, Tablespaces and Datafiles
Login as OS user opc, sudo to oracle and set environment.
[oracle@db11-cloud-12 ~]$ . oraenv ORACLE_SID = [DB11] ? The Oracle base has been set to /u01/app/oracle
Login as SYSDBA.
[oracle@db11-cloud-12 ~]$ sqlplus / as sysdba
Query controlfile information.
SQL> SELECT name FROM v$controlfile; NAME -------------------------------------------------------------------------------- +RECO/db11_zrh1ws/controlfile/current.256.1087481145
Query datafile information. The existing datafiles will be dropped later before the restore starts.
SQL> COL tablespace_name FORMAT a20 SQL> COL file_name FORMAT a70 SQL> SET LINES 300 SQL> SELECT tablespace_name, file_name 2> FROM dba_data_files 3> ORDER BY 1; TABLESPACE_NAME FILE_NAME -------------------- ---------------------------------------------------------------------- SYSAUX +DATA/db11_zrh1ws/datafile/sysaux.262.1087481085 SYSTEM +DATA/db11_zrh1ws/datafile/system.261.1087481085 UNDOTBS1 +DATA/db11_zrh1ws/datafile/undotbs1.263.1087481085 USERS +DATA/db11_zrh1ws/datafile/users.266.1087481469
Query tablespace information for encryption, the USERS tablespace is already encrypted. Encryption of SYSTEM, SYSAUX etc. is introduced in a later Oracle version and not available in 11g.
SQL> SELECT tablespace_name, encrypted FROM dba_tablespaces; TABLESPACE_NAME ENC ------------------------------ --- SYSTEM NO SYSAUX NO UNDOTBS1 NO TEMP NO USERS YES
4. Configure OCI Cloud Database to use the Oracle Database Backup Service
Note: When the database is located in a VCN private subnet, to install and using the Oracle Database Backup Service module you have to configure these two resources:
- Service Gateway for Object Storage access
- NAT Gateway used by the Database Backup Service Module Library installation routine
For the installation the SSH private key from the existing on-prem Database Backup Service configuration is required and has to be transferred to cloud server. The fingerprint of the public ssh key is required.
A list of object storage endpoints is avaliable here: Object Storage Service API | Oracle Cloud Infrastructure API Reference and Endpoints
Cloud Database – Oracle Database Backup Service Installation and Configuration
Login to cloud virtual machine and sudo to OS user oracle
[opc@db11-cloud-12 ~]$ sudo su - oracle
Create directory for the Database Backup Module installation, transfer and extract it.
[oracle@db11-cloud-12 ~]$ mkdir -p /u01/app/oracle/oci [oracle@db11-cloud-12 ~]$ cd /u01/app/oracle/oci [oracle@db11-cloud-12 oci]$ ll total 1776 -rw-r--r-- 1 oracle oinstall 1816114 Nov 1 14:29 opc_installer.zip [oracle@db11-cloud-12 oci]$ unzip opc_installer.zi
Create directories for wallet and library.
[oracle@db11-cloud-12 ~]$ mkdir -p /u01/app/oracle/oci/oci_wallet/ [oracle@db11-cloud-12 ~]$ mkdir -p /u01/app/oracle/oci/lib/
Transfer the SSH key from on-prem to the cloud virtual machine into oci_wallet directory.
[oracle@db11-cloud-12 oci]$ ll /u01/app/oracle/oci/oci_wallet/ total 4 -rw-r--r-- 1 oracle oinstall 1703 Nov 1 14:31 oci_pvt
Install Oracle Backup Service Module, use the private key and public fingerprint from the on-prem installation. Set environment and start the installer. Use the bucket name from OCI Object Storage bucket which you have created first.
[oracle@db11-cloud-12 stage]$ . oraenv ORACLE_SID = [DB11] ? The Oracle base has been set to /u01/app/oracle
[oracle@db11-cloud-12 oci]$ java -jar /u01/app/oracle/oci/opc_installer/oci_installer/oci_install.jar \ -host https://objectstorage.eu-zurich-1.oraclecloud.com \ -uOCID ocid1.user.oc1..aaaaaaaaeif6sfiwfjukvylfs6ps34gypvep4703Mohnweg \ -tOCID ocid1.tenancy.oc1..aaaaaaaaxuk4je4t3aorovuzmwyeaq5sftqv3nkyz64sni4703Mohnweg \ -walletDir /u01/app/oracle/oci/oci_wallet \ -libDir /u01/app/oracle/oci/lib \ -pvtKeyFile /u01/app/oracle/oci/oci_wallet/oci_pvt \ -pubfingerprint 16:48:7b:21:f0:31:74:9e:t3:u8:v2:w9:x1:y9:zd:77 \ -bucket onprem-bucket
Installer Output.
Oracle Database Cloud Backup Module Install Tool, build 19.3.0.0.0DBBKPCSBP_2019-10-16 Oracle Database Cloud Backup Module credentials are valid. Backups would be sent to bucket onprem-bucket. Oracle Database Cloud Backup Module wallet created in directory /u01/app/oracle/oci/oci_wallet. Oracle Database Cloud Backup Module initialization file /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/opcDB11.ora created. Downloading Oracle Database Cloud Backup Module Software Library from Oracle Cloud Infrastructure. Download complete.
A new parameter file is created which contains the OCI Object Storage information. This configuration file is used later in RMAN.
[oracle@db11-cloud-12 oci]$ cat $ORACLE_HOME/dbs/opcDB11.ora OPC_HOST=https://objectstorage.eu-zurich-1.oraclecloud.com/n/zrq07abcdefgh OPC_WALLET='LOCATION=file:/u01/app/oracle/oci/oci_wallet CREDENTIAL_ALIAS=alias_oci' OPC_CONTAINER=onprem-bucket OPC_COMPARTMENT_ID=ocid1.compartment.oc1..aaaaaaaat5uo2xh77edws4huwvqorengp7x4xdv6x3giw3vr4703Mohnweg OPC_AUTH_SCHEME=BMC
5. Clean up OCI Cloud Database and restart NOMOUNT
Login to cloud database virtual machine as OS user grid.
[opc@db11-cloud-12 ~]$ sudo su - grid
Set environment to database DB11.
[grid@db11-cloud-12 ~]$ . oraenv ORACLE_SID = [+ASM1] ? DB11 The Oracle base has been set to /u01/app/oracle
Shutdown the database instance by using the database unique name.
[grid@db11-cloud-12 ~]$ srvctl status database -d db11_zrh1ws Instance DB11 is running on node db11-cloud-12 [grid@db11-cloud-12 ~]$ srvctl stop database -d db11_zrh1ws [grid@db11-cloud-12 ~]$ srvctl status database -d db11_zrh1ws Instance DB11 is not running on node db11-cloud-12
Error message when environment for +ASM is set:
[grid@db11-cloud-12 ~]$ srvctl stop database -d db11_zrh1ws PRCD-1229 : An attempt to access configuration of database db11_zrh1ws was rejected because its version 11.2.0.4.0 differs from the program version 19.0.0.0.0. Instead run the program from /u01/app/oracle/product/11.2.0.4/dbhome_1.
ASM Cleanup with environment +ASM – you have to add ORACLE_HOME manually
[grid@db11-cloud-12 ~]$ . oraenv ORACLE_SID = [DB11] ? +ASM ORACLE_HOME = [/home/oracle] ? /u01/app/19.0.0.0/grid The Oracle base has been changed from /u01/app/oracle to /u01/app/grid
Login in ASM and remove existing controlfiles, datafiles, tempfiles and directories.
[grid@db11-cloud-12 ~]$ asmcmd ASMCMD> cd RECO ASMCMD> rm -rf DB11_ZRH1WS/ ASMCMD> cd .. ASMCMD> cd DATA ASMCMD> cd DB11_ZRH1WS
ASMCMD> ls -l Type Redund Striped Time Sys Name Y DATAFILE/ Y PARAMETERFILE/ Y TEMPFILE/ PARAMETERFILE UNPROT COARSE NOV 01 14:00:00 N spfileDB11.ora => +DATA/DB11_ZRH1WS/PARAMETERFILE/spfile.265.1087481321
ASMCMD> rm -rf DATAFILE/ ASMCMD> rm -rf TEMPFILE/
Set environment to DB11.
[grid@db11-cloud-12 ~]$ . oraenv ORACLE_SID = [+ASM] ? DB11 The Oracle base has been changed from /u01/app/grid to /u01/app/oracle
Start database NOMOUNT.
[grid@db11-cloud-12 ~]$ srvctl start database -d db11_zrh1ws -o nomount [grid@db11-cloud-12 ~]$ srvctl status database -d db11_zrh1ws Instance DB11 is running on node db11-cloud-12
6. Restore OCI cloud database from Object Store
Restore and recovery of the database, the database has same name as the on-prem database. The datafile is migrated from file system to ASM. Required information for restore and recovery:
- On-prem database DBID
- Encryption password
Cloud Database – Login in RMAN and set Decryption Password set Source DBID
[opc@db11-cloud-12 ~]$ sudo su - oracle Last login: Mon Nov 1 14:40:05 CET 2021
[oracle@db11-cloud-12 ~]$ . oraenv ORACLE_SID = [DB11] ? The Oracle base has been set to /u01/app/oracle
[oracle@db11-cloud-12 ~]$ rman target / RMAN> SET DECRYPTION IDENTIFIED BY '<my-onprem-encryption-password-here'; RMAN> SET DBID=1617630071;
Cloud Database – Restore Controlfile from Object Storage
Use the library path and the path to the configuration file (OPC_FILE) properly.
RMAN> RUN {
2> ALLOCATE CHANNEL t1 DEVICE TYPE sbt PARMS 'SBT_LIBRARY=/u01/app/oracle/oci/lib/libopc.so ENV=(OPC_PFILE=/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/opcDB11.ora)';
3> RESTORE CONTROLFILE FROM AUTOBACKUP;
4> }
allocated channel: t1
channel t1: SID=176 device type=SBT_TAPE
channel t1: Oracle Database Backup Service Library VER=21.0.0.1
Starting restore at 01-NOV-21
channel t1: looking for AUTOBACKUP on day: 20211101
channel t1: AUTOBACKUP found: c-1617637023-20211101-04
channel t1: restoring control file from AUTOBACKUP c-1617637023-20211101-04
channel t1: control file restore from AUTOBACKUP complete
output file name=+RECO/db11_zrh1ws/controlfile/current.256.1087483283
Finished restore at 01-NOV-21
released channel: t1
Cloud Database – Mount Instance
RMAN> ALTER DATABASE MOUNT;
Cloud Database – Restore Instance
Allocate channel for maintenance first.
RMAN> ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE DISK; allocated channel: ORA_MAINT_DISK_1 channel ORA_MAINT_DISK_1: SID=318 device type=DISK
Start restore.
RMAN> RUN {
2> SET NEWNAME FOR DATABASE TO '+DATA';
3> ALLOCATE CHANNEL t1 DEVICE TYPE sbt PARMS 'SBT_LIBRARY=/u01/app/oracle/oci/lib/libopc.so ENV=(OPC_PFILE=/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/opcDB11.ora)';
4> ALLOCATE CHANNEL t2 DEVICE TYPE sbt PARMS 'SBT_LIBRARY=/u01/app/oracle/oci/lib/libopc.so ENV=(OPC_PFILE=/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/opcDB11.ora)';
5> RESTORE DATABASE;
6> SWITCH DATAFILE ALL;
7> }
executing command: SET NEWNAME
allocated channel: t1
channel t1: SID=177 device type=SBT_TAPE
channel t1: Oracle Database Backup Service Library VER=21.0.0.1
allocated channel: t2
channel t2: SID=13 device type=SBT_TAPE
channel t2: Oracle Database Backup Service Library VER=21.0.0.1
Starting restore at 01-NOV-21
Starting implicit crosscheck backup at 01-NOV-21
Crosschecked 2 objects
Finished implicit crosscheck backup at 01-NOV-21
Starting implicit crosscheck copy at 01-NOV-21
Finished implicit crosscheck copy at 01-NOV-21
searching for all files in the recovery area
cataloging files...
no files cataloged
channel t1: starting datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
channel t1: restoring datafile 00001 to +DATA
channel t1: restoring datafile 00002 to +DATA
channel t1: restoring datafile 00003 to +DATA
channel t1: restoring datafile 00004 to +DATA
channel t1: restoring datafile 00005 to +DATA
channel t1: restoring datafile 00006 to +DATA
channel t1: restoring datafile 00007 to +DATA
channel t1: restoring datafile 00008 to +DATA
channel t1: restoring datafile 00009 to +DATA
channel t1: reading from backup piece /u01/app/oracle/admin/DB11/backup/inc0_DB11_2021-11-01_04-30-04_s569_p1
channel t1: piece handle=/u01/app/oracle/admin/DB11/backup/inc0_DB11_2021-11-01_04-30-04_s569_p1 tag=TAG20211101T043005
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:16:45
Finished restore at 01-NOV-21
datafile 1 switched to datafile copy
input datafile copy RECID=10 STAMP=1087484376 file name=+DATA/db11_zrh1ws/datafile/system.267.1087483371
datafile 2 switched to datafile copy
input datafile copy RECID=11 STAMP=1087484376 file name=+DATA/db11_zrh1ws/datafile/sysaux.268.1087483371
datafile 3 switched to datafile copy
input datafile copy RECID=12 STAMP=1087484376 file name=+DATA/db11_zrh1ws/datafile/undotbs1.261.1087483371
datafile 4 switched to datafile copy
input datafile copy RECID=13 STAMP=1087484376 file name=+DATA/db11_zrh1ws/datafile/users.262.1087483371
datafile 5 switched to datafile copy
input datafile copy RECID=14 STAMP=1087484376 file name=+DATA/db11_zrh1ws/datafile/hrdata.269.1087483371
datafile 6 switched to datafile copy
input datafile copy RECID=15 STAMP=1087484376 file name=+DATA/db11_zrh1ws/datafile/soedata.266.1087483371
datafile 7 switched to datafile copy
input datafile copy RECID=16 STAMP=1087484376 file name=+DATA/db11_zrh1ws/datafile/shdata.270.1087483443
datafile 8 switched to datafile copy
input datafile copy RECID=17 STAMP=1087484376 file name=+DATA/db11_zrh1ws/datafile/soedata.263.1087483371
datafile 9 switched to datafile copy
input datafile copy RECID=18 STAMP=1087484377 file name=+DATA/db11_zrh1ws/datafile/soedata.264.1087483371
released channel: t1
released channel: t2
Recover database – ignore the last line of the incomplete recovery.
RMAN> RUN {
2> ALLOCATE CHANNEL t1 DEVICE TYPE sbt PARMS 'SBT_LIBRARY=/u01/app/oracle/oci/lib/libopc.so ENV=(OPC_PFILE=/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/opcDB11.ora)';
3> RECOVER DATABASE;
4> }
allocated channel: t1
channel t1: SID=177 device type=SBT_TAPE
channel t1: Oracle Database Backup Service Library VER=21.0.0.1
Starting recover at 01-NOV-21
starting media recovery
channel t1: starting archived log restore to default destination
channel t1: restoring archived log
archived log thread=1 sequence=1009
channel t1: reading from backup piece /u01/app/oracle/admin/DB11/backup/arc_DB11_2021-11-01_04-30-04_s570_p1
channel t1: piece handle=/u01/app/oracle/admin/DB11/backup/arc_DB11_2021-11-01_04-30-04_s570_p1 tag=TAG20211101T044520
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:00:03
archived log file name=+RECO/db11_zrh1ws/archivelog/2021_11_01/thread_1_seq_1009.259.1087484649 thread=1 sequence=1009
channel default: deleting archived log(s)
archived log file name=+RECO/db11_zrh1ws/archivelog/2021_11_01/thread_1_seq_1009.259.1087484649 RECID=964 STAMP=1087484650
channel t1: starting archived log restore to default destination
channel t1: restoring archived log
archived log thread=1 sequence=1010
channel t1: restoring archived log
archived log thread=1 sequence=1011
channel t1: reading from backup piece /u01/app/oracle/admin/DB11/backup/arc_DB11_2021-11-01_06-00-03_s574_p1
channel t1: piece handle=/u01/app/oracle/admin/DB11/backup/arc_DB11_2021-11-01_06-00-03_s574_p1 tag=TAG20211101T060005
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:00:01
archived log file name=+RECO/db11_zrh1ws/archivelog/2021_11_01/thread_1_seq_1010.259.1087484653 thread=1 sequence=1010
channel default: deleting archived log(s)
archived log file name=+RECO/db11_zrh1ws/archivelog/2021_11_01/thread_1_seq_1010.259.1087484653 RECID=966 STAMP=1087484653
archived log file name=+RECO/db11_zrh1ws/archivelog/2021_11_01/thread_1_seq_1011.258.1087484653 thread=1 sequence=1011
channel default: deleting archived log(s)
archived log file name=+RECO/db11_zrh1ws/archivelog/2021_11_01/thread_1_seq_1011.258.1087484653 RECID=965 STAMP=1087484653
channel t1: starting archived log restore to default destination
channel t1: restoring archived log
archived log thread=1 sequence=1012
channel t1: restoring archived log
archived log thread=1 sequence=1013
channel t1: reading from backup piece /u01/app/oracle/admin/DB11/backup/arc_DB11_2021-11-01_10-00-03_s578_p1
channel t1: piece handle=/u01/app/oracle/admin/DB11/backup/arc_DB11_2021-11-01_10-00-03_s578_p1 tag=TAG20211101T100005
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:00:01
archived log file name=+RECO/db11_zrh1ws/archivelog/2021_11_01/thread_1_seq_1012.258.1087484655 thread=1 sequence=1012
channel default: deleting archived log(s)
archived log file name=+RECO/db11_zrh1ws/archivelog/2021_11_01/thread_1_seq_1012.258.1087484655 RECID=968 STAMP=1087484654
archived log file name=+RECO/db11_zrh1ws/archivelog/2021_11_01/thread_1_seq_1013.259.1087484655 thread=1 sequence=1013
channel default: deleting archived log(s)
archived log file name=+RECO/db11_zrh1ws/archivelog/2021_11_01/thread_1_seq_1013.259.1087484655 RECID=967 STAMP=1087484654
channel t1: starting archived log restore to default destination
channel t1: restoring archived log
archived log thread=1 sequence=1014
channel t1: restoring archived log
archived log thread=1 sequence=1015
channel t1: reading from backup piece /u01/app/oracle/admin/DB11/backup/arc_DB11_2021-11-01_14-00-03_s582_p1
channel t1: piece handle=/u01/app/oracle/admin/DB11/backup/arc_DB11_2021-11-01_14-00-03_s582_p1 tag=TAG20211101T140005
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:00:01
archived log file name=+RECO/db11_zrh1ws/archivelog/2021_11_01/thread_1_seq_1014.259.1087484657 thread=1 sequence=1014
channel default: deleting archived log(s)
archived log file name=+RECO/db11_zrh1ws/archivelog/2021_11_01/thread_1_seq_1014.259.1087484657 RECID=970 STAMP=1087484656
archived log file name=+RECO/db11_zrh1ws/archivelog/2021_11_01/thread_1_seq_1015.258.1087484657 thread=1 sequence=1015
channel default: deleting archived log(s)
archived log file name=+RECO/db11_zrh1ws/archivelog/2021_11_01/thread_1_seq_1015.258.1087484657 RECID=969 STAMP=1087484656
unable to find archived log
archived log thread=1 sequence=1016
released channel: t1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/01/2021 15:04:18
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 1016 and starting SCN of 1782476
Cloud Database – Open RESETLOGS
RMAN> ALTER DATABASE OPEN RESETLOGS; database opened
7. Encrypt Tablespace
Login as OS user oracle / SYS as SYSDBA to verify the existing situation.
Cloud Database – Verify Tablespaces
SQL> COL tablespace_name FORMAT a20 SQL> COL file_name FORMAT a70 SQL> SET LINES 300 SQL> SELECT tablespace_name, file_name 2 FROM dba_data_files 3 ORDER BY 1; TABLESPACE_NAME FILE_NAME -------------------- ---------------------------------------------------------------------- HRDATA +DATA/db11_zrh1ws/datafile/hrdata.269.1087483371 SHDATA +DATA/db11_zrh1ws/datafile/shdata.270.1087483443 SOEDATA +DATA/db11_zrh1ws/datafile/soedata.264.1087483371 SOEDATA +DATA/db11_zrh1ws/datafile/soedata.266.1087483371 SOEDATA +DATA/db11_zrh1ws/datafile/soedata.263.1087483371 SYSAUX +DATA/db11_zrh1ws/datafile/sysaux.268.1087483371 SYSTEM +DATA/db11_zrh1ws/datafile/system.267.1087483371 UNDOTBS1 +DATA/db11_zrh1ws/datafile/undotbs1.261.1087483371 USERS +DATA/db11_zrh1ws/datafile/users.262.1087483371 9 rows selected.
Cloud Database – Tablespace Encryption
We use the existing wallet and add a new TDE master key to the configuration. Show parameter for tablespace encryption.
SQL> show parameter encrypt_new_tablespaces NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ encrypt_new_tablespaces string CLOUD_ONLY
SQL> col WRL_TYPE format a10 SQL> col WRL_PARAMETER format a60 SQL> col STATUS format a10 SQL> set lines 300 SQL> SELECT * FROM v$encryption_wallet; WRL_TYPE WRL_PARAMETER STATUS ---------- ------------------------------------------------------------ ---------- file /opt/oracle/dcs/commonstore/wallets/tde/$ORACLE_UNQNAME OPEN
Verify existing actual encryption situation, no tablespaces are encrypted.
SQL> select TABLESPACE_NAME,ENCRYPTED from dba_tablespaces; TABLESPACE_NAME ENC ------------------------------ --- SYSTEM NO SYSAUX NO UNDOTBS1 NO TEMP NO USERS NO HRDATA NO SOEDATA NO SHDATA NO 8 rows selected.
Take user tablespaces offline – a small syntax provider script.
SQL> SELECT 'ALTER TABLESPACE '||tablespace_name|| ' offline;'
2> FROM dba_tablespaces
3> WHERE tablespace_name NOT IN ('SYSTEM','SYSAUX','TEMP','UNDOTBS1');
'ALTERTABLESPACE'||TABLESPACE_NAME||'OFFLINE;'
--------------------------------------------------------
ALTER TABLESPACE USERS offline;
ALTER TABLESPACE HRDATA offline;
ALTER TABLESPACE SOEDATA offline;
ALTER TABLESPACE SHDATA offline;
As SYS AS SYSDBA, set new Master Key
SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "<your_TDE_Master_Key_here>";
Encrypt tablespaces – a small syntax provider script.
SQL> SELECT 'ALTER DATABASE DATAFILE '''|| file_name||''' encrypt;'
2> FROM dba_data_files
3> WHERE tablespace_name NOT IN ('SYSTEM','SYSAUX','TEMP','UNDOTBS1');
'ALTERDATABASEDATAFILE'''||FILE_NAME||'''ENCRYPT;'
--------------------------------------------------------------------------------------
ALTER DATABASE DATAFILE '+DATA/db11_zrh1ws/datafile/users.262.1087483371' encrypt;
ALTER DATABASE DATAFILE '+DATA/db11_zrh1ws/datafile/hrdata.269.1087483371' encrypt;
ALTER DATABASE DATAFILE '+DATA/db11_zrh1ws/datafile/soedata.266.1087483371' encrypt;
ALTER DATABASE DATAFILE '+DATA/db11_zrh1ws/datafile/shdata.270.1087483443' encrypt;
ALTER DATABASE DATAFILE '+DATA/db11_zrh1ws/datafile/soedata.263.1087483371' encrypt;
ALTER DATABASE DATAFILE '+DATA/db11_zrh1ws/datafile/soedata.264.1087483371' encrypt;
Take encrypted tablespaces online, the encryption starts and the taking online action needs some time (depends on CPU and I/O).
SQL> SELECT 'ALTER TABLESPACE '||tablespace_name|| ' online;'
2> FROM dba_tablespaces
3> WHERE tablespace_name NOT IN ('SYSTEM','SYSAUX','TEMP','UNDOTBS1');
'ALTERTABLESPACE'||TABLESPACE_NAME||'ONLINE;'
--------------------------------------------------------
ALTER TABLESPACE USERS online;
ALTER TABLESPACE HRDATA online;
ALTER TABLESPACE SOEDATA online;
ALTER TABLESPACE SHDATA online;
A few minutes later, the user tablespaces are shown as encrypted.
SQL> select TABLESPACE_NAME,ENCRYPTED from dba_tablespaces; TABLESPACE_NAME ENC ------------------------------ --- SYSTEM NO SYSAUX NO UNDOTBS1 NO TEMP NO USERS YES HRDATA YES SOEDATA YES SHDATA YES 8 rows selected.
Verify encrypted tablespaces by DBVerify – as you can see here, Total Pages Encrypted is shown.
[oracle@db11-cloud-12 trace]$ dbv file=+DATA/db11_zrh1ws/datafile/soedata.263.1087483371 USERID=system/<your_SYSTEM_password_here> DBVERIFY: Release 11.2.0.4.0 - Production on Mon Nov 1 15:20:43 2021 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. DBVERIFY - Verification starting : FILE = +DATA/db11_zrh1ws/datafile/soedata.263.1087483371 DBVERIFY - Verification complete Total Pages Examined : 1600000 Total Pages Processed (Data) : 0 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 0 Total Pages Failing (Index): 0 Total Pages Processed (Other): 84893 Total Pages Processed (Seg) : 0 Total Pages Failing (Seg) : 0 Total Pages Empty : 6273 Total Pages Marked Corrupt : 0 Total Pages Influx : 0 Total Pages Encrypted : 1508834 Highest block SCN : 0 (0.0)
Summary and what’s next
Migration of an on-prem database to Oracle Cloud Infrastructire by RMAN and Object Storage is a very nice method to bring not only older databases into the Oracle Cloud Infrastructure. Once there, you can leverage of OCI features like Data Safe, monitoring, backup to Object Storage and many more.
#ilikeit