Migrating Oracle Databases to Oracle Database Cloud Service

The idea of this post is to give an overview on how we can migrate an existing on premise Oracle Database to Oracle Cloud using different methods. Here, i have not explained all the methods. If you need to know in details, visiting https://docs.oracle.com/en/cloud/paas/database-dbaas-cloud/csdbi/mig-migrating-premises-oracle-db-cloud.html should be the first choice.  Before jumping into the migration there are some factors which are important to consider at the very first place.

1.The most important factor which comes into play while doing the migration is the size of the database.
2. The CPU, memory, storage, IOPS and the I/O throughoutput should be identified in advance and the database in cloud should be provisioned that is large enough to contain the requirements.
3. Database characterset

4. Database Version and Edition-Cloud providers doesn't support all versions like 10g or below. So these information's should be captured in advance.

5. Archivelog mode-For some of the methods like rman hot backup, Dataguard based migration, database should be in Archivelog method.

6. OS Endianness- Cloud providers doesn't support all endianness. Thus the information's should have been in place before the migration process.

There are lots of other factors such as law of the land, type of deployment model etc which is beyond the scope of this post.

Now let's jump to the actual migration methods.

First create a database instance in the cloud. Within 30 minutes, my cloud instance is now ready

                                     
                                     Below is my Cloud instance session and On premise DB session

1. Now i will try to migrate one table created on OnPremise database and using sql developer, i will migrate the same to Oracle Cloud instance.

Now, we will create one table in Onpremise database.

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> create table test_migration as select * from all_objects;

Table created

Now in the sql developer, go to tools,

  Verify if the table exists in cloud or not

Thus, to copy a table of 12mb in size, sql developer took 9 mins. If we have a smaller chunk of database, sql developer can come handy but with larger db, definitely not.



2. Using Data Pump utility

We will be using the sample just for one table TEST_MIGRATION. The same method can be used for full database also.

In the on premises databases, create the directory /u01/12c/export

[oracle@rac1 export]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sat Aug 18 01:59:08 2018

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

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> create directory onprem_cloud as '/u01/12c/export';

Directory created.

SQL>

SQL*Plus: Release 12.1.0.2.0 Production on Sat Aug 18 02:02:46 2018

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

Last Successful login time: Sat Aug 18 2018 02:01:54 -04:00

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> create table TEST_MIGRATION as select * from all_objects;

Table created.

SQL> exit;

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[oracle@rac1 export]$ expdp system/*****@ORCL tables=TEST_MIGRATION directory=onprem_cloud dumpfile=TEST_MIGRATION.dmp

Export: Release 12.1.0.2.0 - Production on Sat Aug 18 02:03:35 2018

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

WARNING: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.

Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/********@ORCL tables=TEST_MIGRATION directory=onprem_cloud dumpfile=TEST_MIGRATION.dmp

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 12 MB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

. . exported "SYSTEM"."TEST_MIGRATION"                   10.18 MB   89191 rows

Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:

  /u01/12c/export/TEST_MIGRATION.dmp

Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Sat Aug 18 02:03:49 2018 elapsed 0 00:00:13

In the cloud database, create the directory

[oracle@Cloud import]$ pwd

/u01/12c/import

[oracle@Cloud import]$

Now in the on premise database.

Copy the dump file from on premise to Cloud

[oracle@rac1 export]$ scp TEST_MIGRATION.dmp oracle@129.150.114.131:/u01/12c/import

TEST_MIGRATION.dmp (SSH was already established)                                                                                                                  100%   10MB   1.2MB/s   00:09

[oracle@rac1 export]$

in the cloud database

SQL> create directory cloud_import as '/u01/12c/import';

Directory created.

SQL>

[oracle@Cloud import]$ impdp system/*****@ORCL directory=cloud_import dumpfile=TEST_MIGRATION.dmp

Import: Release 12.1.0.2.0 - Production on Sun Aug 26 11:38:03 2018

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics

and Real Application Testing options

WARNING: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.

Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/********@ORCL directory=cloud_import dumpfile=TEST_MIGRATION.dmp

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "SYSTEM"."TEST_MIGRATION"                   10.18 MB   89191 rows

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Sun Aug 26 11:38:08 2018 elapsed 0 00:00:04

[oracle@Cloud import]$

verify the contents now.

in the cloud

SQL>  select count(*) from TEST_MIGRATION;

  COUNT(*)

----------

     89191

SQL>

in the on premise database

SQL>  select count(*) from TEST_MIGRATION;

  COUNT(*)

----------

     89191

Thus, we can see that data pump is much faster.

3. Next, method would be to create the database instance in cloud using the backups of on premise db stored in oracle cloud storage container.

take the backup first of on premise database

In my previous post https://samappsdba.blogspot.com/2018/08/performing-disaster-recovery-in-oracle.html, i have explained how to install he backup module and configure the rman settings.

take the backup first of on premise database

RMAN> set encryption on identified by "welcome123" only;

executing command: SET encryption

RMAN> backup database plus archivelog;

Starting backup at 18-AUG-18

current log archived

allocated channel: ORA_SBT_TAPE_1

channel ORA_SBT_TAPE_1: SID=66 device type=SBT_TAPE

channel ORA_SBT_TAPE_1: Oracle Database Backup Service Library VER=12.2.0.2

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=15 RECID=1 STAMP=984399125

input archived log thread=1 sequence=16 RECID=2 STAMP=984399307

input archived log thread=1 sequence=17 RECID=3 STAMP=984399439

input archived log thread=1 sequence=18 RECID=4 STAMP=984401375

input archived log thread=1 sequence=19 RECID=5 STAMP=984423617

input archived log thread=1 sequence=20 RECID=6 STAMP=984434626

input archived log thread=1 sequence=21 RECID=7 STAMP=984434952

input archived log thread=1 sequence=22 RECID=8 STAMP=984435162

input archived log thread=1 sequence=23 RECID=9 STAMP=984442198

input archived log thread=1 sequence=24 RECID=10 STAMP=984450160

input archived log thread=1 sequence=25 RECID=11 STAMP=984454974

channel ORA_SBT_TAPE_1: starting piece 1 at 18-AUG-18

channel ORA_SBT_TAPE_1: finished piece 1 at 18-AUG-18

piece handle=0dtar5ql_1_1 tag=TAG20180818T034317 comment=API Version 2.0,MMS Version 12.2.0.2

channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:05:45

Finished backup at 18-AUG-18

Starting backup at 18-AUG-18

using channel ORA_SBT_TAPE_1

channel ORA_SBT_TAPE_1: starting full datafile backup set

channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/12c/oracledb/oradata/ORCL/datafile/o1_mf_system_fp6rjmdo_.dbf

input datafile file number=00003 name=/u01/12c/oracledb/oradata/ORCL/datafile/o1_mf_sysaux_fp6rh696_.dbf

input datafile file number=00004 name=/u01/12c/oracledb/oradata/ORCL/datafile/o1_mf_undotbs1_fp6rlfo4_.dbf

input datafile file number=00006 name=/u01/12c/oracledb/oradata/ORCL/datafile/o1_mf_users_fp6rlbld_.dbf

channel ORA_SBT_TAPE_1: starting piece 1 at 18-AUG-18

channel ORA_SBT_TAPE_1: finished piece 1 at 18-AUG-18

piece handle=0etar65e_1_1 tag=TAG20180818T034902 comment=API Version 2.0,MMS Version 12.2.0.2

channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:23:05

channel ORA_SBT_TAPE_1: starting full datafile backup set

channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set

input datafile file number=00009 name=/u01/12c/oracledb/oradata/ORCL/727A6ADF059B4459E0530538A8C0A88D/datafile/o1_mf_sysaux_fp6scqtr_.dbf

input datafile file number=00008 name=/u01/12c/oracledb/oradata/ORCL/727A6ADF059B4459E0530538A8C0A88D/datafile/o1_mf_system_fp6scqtd_.dbf

input datafile file number=00010 name=/u01/12c/oracledb/oradata/ORCL/727A6ADF059B4459E0530538A8C0A88D/datafile/o1_mf_users_fp6sj6ln_.dbf

channel ORA_SBT_TAPE_1: starting piece 1 at 18-AUG-18

channel ORA_SBT_TAPE_1: finished piece 1 at 18-AUG-18

piece handle=0ftar7go_1_1 tag=TAG20180818T034902 comment=API Version 2.0,MMS Version 12.2.0.2

channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:09:35

channel ORA_SBT_TAPE_1: starting full datafile backup set

channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set

input datafile file number=00012 name=/u01/12c/oracledb/oradata/ORCL/727A73A056B045EFE0530538A8C01006/datafile/o1_mf_sysaux_fp6sjfrr_.dbf

input datafile file number=00011 name=/u01/12c/oracledb/oradata/ORCL/727A73A056B045EFE0530538A8C01006/datafile/o1_mf_system_fp6sjfrq_.dbf

input datafile file number=00013 name=/u01/12c/oracledb/oradata/ORCL/727A73A056B045EFE0530538A8C01006/datafile/o1_mf_users_fp6snb58_.dbf

channel ORA_SBT_TAPE_1: starting piece 1 at 18-AUG-18

channel ORA_SBT_TAPE_1: finished piece 1 at 18-AUG-18

piece handle=0gtar82n_1_1 tag=TAG20180818T034902 comment=API Version 2.0,MMS Version 12.2.0.2

channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:09:55

channel ORA_SBT_TAPE_1: starting full datafile backup set

channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set

input datafile file number=00007 name=/u01/12c/oracledb/oradata/ORCL/datafile/o1_mf_sysaux_fp6rnbb1_.dbf

input datafile file number=00005 name=/u01/12c/oracledb/oradata/ORCL/datafile/o1_mf_system_fp6rnbb5_.dbf

channel ORA_SBT_TAPE_1: starting piece 1 at 18-AUG-18

channel ORA_SBT_TAPE_1: finished piece 1 at 18-AUG-18

piece handle=0htar8la_1_1 tag=TAG20180818T034902 comment=API Version 2.0,MMS Version 12.2.0.2

channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:10:05

Finished backup at 18-AUG-18

Starting backup at 18-AUG-18

current log archived

using channel ORA_SBT_TAPE_1

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=26 RECID=12 STAMP=984458503

channel ORA_SBT_TAPE_1: starting piece 1 at 18-AUG-18

channel ORA_SBT_TAPE_1: finished piece 1 at 18-AUG-18

piece handle=0itar988_1_1 tag=TAG20180818T044144 comment=API Version 2.0,MMS Version 12.2.0.2

channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:01:15

Finished backup at 18-AUG-18

Starting Control File and SPFILE Autobackup at 18-AUG-18

piece handle=c-1510897456-20180818-00 comment=API Version 2.0,MMS Version 12.2.0.2

Finished Control File and SPFILE Autobackup at 18-AUG-18

now lets connect to our storage container in the cloud and verify it

Now we have the valid backups of the on premise in place at Cloud storage container.

In the dbaas console, we have the option to replace the existing cloud database instance to replace with the backups stored in the storage container.

  

Database id- ID of the on premise database

Username-Identity domain email id

Now at the DbaaS console, we can see that instance is on maintenance mode.

Check the view activity

This method is quite faster and requires minimal dowtime.

Well, there are many other methods which are described in the Oracle Docs. Based upon your requirement, you can choose the method which suits best for you. My future posts will have many other contents related to Oracle Cloud and also i am going to explore AWS, Devops etc. Stay tuned

Happy Learning.

Performing disaster recovery In oracle Cloud using on premise backups stored in Oracle cloud backup storage

In this post, i am going to illustrate how we can  create a database instance in cloud using the on premise database backups stored in Oracle Cloud. This covers the restore scenarios where in case of any disaster in on premise, we can have instance up and running in Oracle cloud.

The first step would be to install the backup model to the on premise database. Download the opc_installer.zip  from the website "http://www.oracle.com/technetwork/database/availability/oracle-cloud-backup-2162729.html" and unzip the contents.

In the on premise database, create the following directories lib and wallet, extract the zip file and install it. I have created it under /home/oracle.

Download the jdk-7u80-linux-x64.tar.gz.

-rw-r--r-- 1 oracle oinstall 153530841 Aug  7  2018 jdk-7u80-linux-x64.tar.gz

tar zxvf jdk-7u80-linux-x64.tar.gz

[oracle@rac1 ~]$ cd jdk1.7.0_80

[oracle@rac1 jdk1.7.0_80]$ ls

bin  COPYRIGHT  db  include  jre  lib  LICENSE  man  README.html  release  src.zip  THIRDPARTYLICENSEREADME-JAVAFX.txt  THIRDPARTYLICENSEREADME.txt

[oracle@rac1 jdk1.7.0_80]$ cd bin

[oracle@rac1 bin]$ ls -lrt java

-rwxr-xr-x 1 oracle oinstall 7718 Apr 10  2015 java

[oracle@rac1 bin]$ pwd

/home/oracle/jdk1.7.0_80/bin

[oracle@rac1 bin]$ /home/oracle/jdk1.7.0_80/bin/java -version

java version "1.7.0_80"

Java(TM) SE Runtime Environment (build 1.7.0_80-b15)

Java HotSpot(TM) 64-Bit Server VM (build 24.80-b11

Also, before installing the backup module make sure, we have the valid container in place in Oracle Cloud to store the backups. 

Now, install the backup module.

/home/oracle/jdk1.7.0_80/bin/java -jar opc_install.jar -host https://*****.us.storage.oraclecloud.com/v1/Storage-********* -opcId email id -opcPass password -walletDir /home/oracle/wallet -libDir /home/oracle/lib

Oracle Database Cloud Backup Module Install Tool, build 12.2.0.1.0DBBKPCSBP_2018-06-12

Oracle Database Cloud Backup Module credentials are valid.

Oracle Database Cloud Backup Module wallet created in directory /home/oracle/wallet.

Oracle Database Cloud Backup Module initialization file /u01/12c/oracledb/ORCLuct/12c/db_1/dbs/opcORCL.ora created.

Downloading Oracle Database Cloud Backup Module Software Library from file opc_linux64.zip.

Download complete.

Verify the contents of wallet and lib directory.

[oracle@rac1 ~]$ cd wallet/

[oracle@rac1 wallet]$ ls -lrt

total 12

-rw------- 1 oracle oinstall    0 Aug  2 00:28 cwallet.sso.lck

-rw------- 1 oracle oinstall 1645 Aug  2 00:28 cwallet.sso

[oracle@rac1 wallet]$ cd ../lib

[oracle@rac1 lib]$ ls -lrt

total 86360

-rw-r--r-- 1 oracle oinstall    16801 Aug  2 00:40 bulkimport.pl

-rw-r--r-- 1 oracle oinstall    11140 Aug  2 00:40 perl_readme.txt

-rw-r--r-- 1 oracle oinstall    40661 Aug  2 00:40 odbsrmt.pm

-rw-r--r-- 1 oracle oinstall    13730 Aug  2 00:40 odbsrmt.pl

-rw-r--r-- 1 oracle oinstall      286 Aug  2 00:40 metadata.xml

-rw-r--r-- 1 oracle oinstall 88215837 Aug  2 00:40 libopc.so

[oracle@rac1 lib]$

The name of on-premises database is ORCL. Now connect to RMAN and change the following configurations. As Oracle uses libopc.so library we need to configure the SBT_TAPE channel to use this library in all RMAN backups. 

Note: Oracle Cloud uses the encryption. which is mandatory for storing the backups. The rman encryption methods available are • Password encryption • Transparent Data Encryption (TDE) • Dual-mode encryption (combination of password and TDE)

For more details, visit the Oracle documentation https://docs.oracle.com/en/cloud/paas/db-backup-cloud/csdbb/backing-oracle-database-backup-cloud-service.html

In my notes, I have used the password encryption method. 

Now configure the rman settings.

RMAN> CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS

'SBT_LIBRARY=/home/oracle/lib/libopc.so

ENV=(OPC_PFILE=/u01/12c/oracledb/ORCLuct/12c/db_1/dbs/opcORCL.ora)';2> 3>

new RMAN configuration parameters:

CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS  'SBT_LIBRARY=/home/oracle/lib/libopc.soENV=(OPC_PFILE=/u01/12c/oracledb/ORCLuct/12c/db_1/dbs/opcORCL.ora)';

new RMAN configuration parameters are successfully stored

configure autobackup of controlfile

RMAN>  CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters:

CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters are successfully stored

Change the default channel to tape (media -> Oracle Cloud Backup Storage)

RMAN>  CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';

new RMAN configuration parameters:

CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';

new RMAN configuration parameters are successfully stored

verify the contents again

RMAN> show all;

RMAN configuration parameters for database with db_unique_name ORCL are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

CONFIGURE BACKUP OPTIMIZATION OFF; # default

CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%F'; # default

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

CONFIGURE DEVICE TYPE SBT_TAPE PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS  'SBT_LIBRARY=/home/oracle/lib/libopc.so ENV=(OPC_PFILE=/u01/12c/oracledb/ORCLuct/12c/db_1/dbs/opcORCL.ora)';

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default

CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/12c/oracledb/ORCLuct/12c/db_1/dbs/snapcf_ORCL.f'; # default

I have created a table at on premise database which i will query to verify after the disaster recovery

 in cloud.

create a table in the on premise db

SQL> create table sam as select * from dba_objects where rownum<=70;

Table created.

SQL>

SQL>  create table sam as select * from all_objects;

 > select count(1) from sam;

  COUNT(1)

----------

     89384

Now take the backup.after enabling the encryption.

RMAN>  set encryption on identified by "welcome123" only;

note:If you forget or lose the password, you won’t be able to restore the backup.

RMAN> backup database plus archivelog;

Starting backup at 02-AUG-18

current log archived

allocated channel: ORA_SBT_TAPE_1

channel ORA_SBT_TAPE_1: SID=50 device type=SBT_TAPE

channel ORA_SBT_TAPE_1: Oracle Database Backup Service Library VER=12.2.0.2

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=10 RECID=1 STAMP=983052020

input archived log thread=1 sequence=11 RECID=2 STAMP=983052050

input archived log thread=1 sequence=12 RECID=3 STAMP=983052099

input archived log thread=1 sequence=13 RECID=4 STAMP=983055646

input archived log thread=1 sequence=14 RECID=5 STAMP=983062553

input archived log thread=1 sequence=15 RECID=6 STAMP=983062688

channel ORA_SBT_TAPE_1: starting piece 1 at 02-AUG-18

Starting backup at 02-AUG-18

current log archived

using channel ORA_SBT_TAPE_1

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=16 RECID=7 STAMP=983063596

channel ORA_SBT_TAPE_1: starting piece 1 at 02-AUG-18

channel ORA_SBT_TAPE_1: finished piece 1 at 02-AUG-18

piece handle=03t9gn1c_1_1 tag=TAG20180802T011316 comment=API Version 2.0,MMS Version 12.2.0.2

input datafile file number=00006 name=/u01/12c/oracledb/oradata/ORCL/datafile/o1_mf_users_fp2wkc6g_.dbf

channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:25

Finished backup at 02-AUG-18

Starting Control File and SPFILE Autobackup at 02-AUG-18

piece handle=c-394828052-20180802-00 comment=API Version 2.0,MMS Version 12.2.0.2

Finished Control File and SPFILE Autobackup at 02-AUG-18

Now the backups are stored in the Cloud.

Next would be to configure the cloud instance. Copy the opc installer to cloud instance and install it the same way we did it for on premise database. Configure ssh connectivity from on premise and drop the cloud database instance if any. Start the database in no mount and restore the files as illustrated below.

SQL> startup force mount exclusive restrict;

ORACLE instance started.

Total System Global Area 2768240640 bytes

Fixed Size                  2928248 bytes

Variable Size             704643464 bytes

Database Buffers         1979711488 bytes

Redo Buffers               80957440 bytes

Database mounted.

SQL>  drop database;

Database dropped.

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit ORCLuction

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> exit;

[oracle@ORCL ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 ORCLuction on Sat Aug 11 13:29:38 2018

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

Connected to an idle instance.

SQL> startup nomount;

ORACLE instance started.

Now restore the files.

[oracle@ORCL ~]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - ORCLuction on Thu Aug 23 15:26:12 2018

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

connected to target database: ORCL (not mounted)

RMAN> set decryption identified by "welcome123";

executing command: SET decryption

using target database control file instead of recovery catalog

RMAN> run

2> {

3> allocate channel t1 type 'SBT_TAPE' PARMS

4> 'SBT_LIBRARY=/home/oracle/lib/libopc.so ENV=(OPC_PFILE=/u01/app/oracle/ORCLuct/12.1.0/dbhome_1/dbs/opcORCL.ora)';

5> set dbid=1510897456;

6> restore spfile to pfile '/tmp/pfile.ora' from autobackup;

7> }

allocated channel: t1

channel t1: SID=12 device type=SBT_TAPE

channel t1: Oracle Database Backup Service Library VER=12.2.0.2

executing command: SET DBID

Starting restore at 23-AUG-18

channel t1: looking for AUTOBACKUP on day: 20180823

channel t1: looking for AUTOBACKUP on day: 20180822

channel t1: looking for AUTOBACKUP on day: 20180821

channel t1: looking for AUTOBACKUP on day: 20180820

channel t1: looking for AUTOBACKUP on day: 20180819

channel t1: looking for AUTOBACKUP on day: 20180818

channel t1: looking for AUTOBACKUP on day: 20180817

channel t1: AUTOBACKUP found: c-1510897456-20180817-00

channel t1: restoring spfile from AUTOBACKUP c-1510897456-20180817-00

channel t1: SPFILE restore from AUTOBACKUP complete

Finished restore at 23-AUG-18

released channel: t1

Now the pfile which got created under /tmp, i copied the same to $ORACL_HOME/dbs and altered the directories path to point to the current cloud instance.(It was pointing to the on premise path)

now restore the control file from autobackup

[oracle@ORCL dbs]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - ORCLuction on Thu Aug 23 16:24:59 2018

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

connected to target database: ORCL (not mounted)

RMAN> set decryption identified by "welcome123";

executing command: SET decryption

using target database control file instead of recovery catalog

RMAN> run

2> {

3> allocate channel t1 type 'SBT_TAPE' PARMS 'SBT_LIBRARY=/home/oracle/lib/libopc.so ENV=(OPC_PFILE=/u01/app/oracle/ORCLuct/12.1.0/dbhome_1/dbs/opcORCL.ora)';

4> set dbid=1510897456;

5> restore controlfile from autobackup;

6> }

allocated channel: t1

channel t1: SID=12 device type=SBT_TAPE

channel t1: Oracle Database Backup Service Library VER=12.2.0.2

executing command: SET DBID

Starting restore at 23-AUG-18

channel t1: looking for AUTOBACKUP on day: 20180823

channel t1: looking for AUTOBACKUP on day: 20180822

channel t1: looking for AUTOBACKUP on day: 20180821

channel t1: looking for AUTOBACKUP on day: 20180820

channel t1: looking for AUTOBACKUP on day: 20180819

channel t1: looking for AUTOBACKUP on day: 20180818

channel t1: looking for AUTOBACKUP on day: 20180817

channel t1: AUTOBACKUP found: c-1510897456-20180817-00

channel t1: restoring control file from AUTOBACKUP c-1510897456-20180817-00

channel t1: control file restore from AUTOBACKUP complete

output file name=/u02/app/oracle/oradata/ORCL/o1_mf_fp6rmgrq_.ctl

output file name=/u03/app/oracle/fast_recovery_area/ORCL/o1_mf_fp6rmjo0_.ctl

Finished restore at 23-AUG-18

released channel: t1

RMAN>

 Control file are restored. Start the database in MOUNT mode and restore the datafiles. Specify a new folder using SET NEWNAME FOR DATABASE TO command as follows:

SQL> shut immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 2768240640 bytes

Fixed Size                  2928248 bytes

Variable Size             704643464 bytes

Database Buffers         2046820352 bytes

Redo Buffers               13848576 bytes

Database mounted.

SQL>

Now restore the datafiles.

RMAN> set decryption identified by "welcome123";

executing command: SET decryption

using target database control file instead of recovery catalog

RMAN> run

2> {

3> allocate channel t1 type 'SBT_TAPE' PARMS 'SBT_LIBRARY=/home/oracle/lib/libopc.so ENV=(OPC_PFILE=/u01/app/oracle/ORCLuct/12.1.0/dbhome_1/dbs/opcORCL.ora)';

4> set newname for database to '/u01/12c/oracledb/oradata//%U.dbf';

5> restore database;

6> switch datafile all;

7> }

allocated channel: t1

channel t1: SID=12 device type=SBT_TAPE

channel t1: Oracle Database Backup Service Library VER=12.2.0.2

executing command: SET NEWNAME

Starting restore at 23-AUG-18

channel t1: starting datafile backup set restore

channel t1: specifying datafile(s) to restore from backup set

channel t1: restoring datafile 00001 to /u01/12c/oracledb/oradata//data_D-ORCL_TS-SYSTEM_FNO-1.dbf

channel t1: restoring datafile 00003 to /u01/12c/oracledb/oradata//data_D-ORCL_TS-SYSAUX_FNO-3.dbf

channel t1: restoring datafile 00004 to /u01/12c/oracledb/oradata//data_D-ORCL_TS-UNDOTBS1_FNO-4.dbf

channel t1: restoring datafile 00006 to /u01/12c/oracledb/oradata//data_D-ORCL_TS-USERS_FNO-6.dbf

channel t1: reading from backup piece 03tapfjj_1_1

channel t1: piece handle=03tapfjj_1_1 tag=TAG20180817T121754

channel t1: restored backup piece 1

channel t1: restore complete, elapsed time: 00:00:25

channel t1: starting datafile backup set restore

channel t1: specifying datafile(s) to restore from backup set

channel t1: restoring datafile 00008 to /u01/12c/oracledb/oradata//data_D-ORCL_TS-SYSTEM_FNO-8.dbf

channel t1: restoring datafile 00009 to /u01/12c/oracledb/oradata//data_D-ORCL_TS-SYSAUX_FNO-9.dbf

channel t1: restoring datafile 00010 to /u01/12c/oracledb/oradata//data_D-ORCL_TS-USERS_FNO-10.dbf

channel t1: reading from backup piece 04tapgas_1_1

channel t1: piece handle=04tapgas_1_1 tag=TAG20180817T121754

channel t1: restored backup piece 1

channel t1: restore complete, elapsed time: 00:00:15

channel t1: starting datafile backup set restore

channel t1: specifying datafile(s) to restore from backup set

channel t1: restoring datafile 00011 to /u01/12c/oracledb/oradata//data_D-ORCL_TS-SYSTEM_FNO-11.dbf

channel t1: restoring datafile 00012 to /u01/12c/oracledb/oradata//data_D-ORCL_TS-SYSAUX_FNO-12.dbf

channel t1: restoring datafile 00013 to /u01/12c/oracledb/oradata//data_D-ORCL_TS-USERS_FNO-13.dbf

channel t1: reading from backup piece 05tapgmj_1_1

channel t1: piece handle=05tapgmj_1_1 tag=TAG20180817T121754

channel t1: restored backup piece 1

channel t1: restore complete, elapsed time: 00:00:15

channel t1: starting datafile backup set restore

channel t1: specifying datafile(s) to restore from backup set

channel t1: restoring datafile 00005 to /u01/12c/oracledb/oradata//data_D-ORCL_TS-SYSTEM_FNO-5.dbf

channel t1: restoring datafile 00007 to /u01/12c/oracledb/oradata//data_D-ORCL_TS-SYSAUX_FNO-7.dbf

channel t1: reading from backup piece 06taph20_1_1

channel t1: piece handle=06taph20_1_1 tag=TAG20180817T121754

channel t1: restored backup piece 1

channel t1: restore complete, elapsed time: 00:00:15

Finished restore at 23-AUG-18

datafile 1 switched to datafile copy

input datafile copy RECID=15 STAMP=984933730 file name=/u01/12c/oracledb/oradata/data_D-ORCL_TS-SYSTEM_FNO-1.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=16 STAMP=984933731 file name=/u01/12c/oracledb/oradata/data_D-ORCL_TS-SYSAUX_FNO-3.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=17 STAMP=984933731 file name=/u01/12c/oracledb/oradata/data_D-ORCL_TS-UNDOTBS1_FNO-4.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=18 STAMP=984933731 file name=/u01/12c/oracledb/oradata/data_D-ORCL_TS-SYSTEM_FNO-5.dbf

datafile 6 switched to datafile copy

input datafile copy RECID=19 STAMP=984933731 file name=/u01/12c/oracledb/oradata/data_D-ORCL_TS-USERS_FNO-6.dbf

datafile 7 switched to datafile copy

input datafile copy RECID=20 STAMP=984933731 file name=/u01/12c/oracledb/oradata/data_D-ORCL_TS-SYSAUX_FNO-7.dbf

datafile 8 switched to datafile copy

input datafile copy RECID=21 STAMP=984933732 file name=/u01/12c/oracledb/oradata/data_D-ORCL_TS-SYSTEM_FNO-8.dbf

datafile 9 switched to datafile copy

input datafile copy RECID=22 STAMP=984933732 file name=/u01/12c/oracledb/oradata/data_D-ORCL_TS-SYSAUX_FNO-9.dbf

datafile 10 switched to datafile copy

input datafile copy RECID=23 STAMP=984933732 file name=/u01/12c/oracledb/oradata/data_D-ORCL_TS-USERS_FNO-10.dbf

datafile 11 switched to datafile copy

input datafile copy RECID=24 STAMP=984933732 file name=/u01/12c/oracledb/oradata/data_D-ORCL_TS-SYSTEM_FNO-11.dbf

datafile 12 switched to datafile copy

input datafile copy RECID=25 STAMP=984933733 file name=/u01/12c/oracledb/oradata/data_D-ORCL_TS-SYSAUX_FNO-12.dbf

datafile 13 switched to datafile copy

input datafile copy RECID=26 STAMP=984933733 file name=/u01/12c/oracledb/oradata/data_D-ORCL_TS-USERS_FNO-13.dbf

released channel: t1

RMAN>

now rename the redo log file

first determine the redo log file location

select member from v$logfile;

MEMBER

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

/u01/12c/oracledb/oradata/ORCL/onlinelog/o1_mf_3_fp6rmzho_.log

 /u01/12c/oracledb/fast_recovery_area/ORCL/onlinelog/o1_mf_3_fp6rmzkn_.log

 /u01/12c/oracledb/oradata/ORCL/onlinelog/o1_mf_2_fp6rmsyf_.log

 /u01/12c/oracledb/fast_recovery_area/ORCL/onlinelog/o1_mf_2_fp6rmt2k_.log

 /u01/12c/oracledb/oradata/ORCL/onlinelog/o1_mf_1_fp6rmmmf_.log

 /u01/12c/oracledb/fast_recovery_area/ORCL/onlinelog/o1_mf_1_fp6rmmpr_.log

 RMAN> alter database rename file '/u01/12c/oracledb/oradata/ORCL/onlinelog/o1_mf_3_fp6rmzho_.log' to '/u01/12c/oracledb/oradata/o1_mf_3_fp6rmzho_.log';

Statement processed

RMAN> alter database rename file '/u01/12c/oracledb/fast_recovery_area/ORCL/onlinelog/o1_mf_3_fp6rmzkn_.log' to '/u01/12c/oracledb/oradata/o1_mf_3_fp6rmzkn_.log';

Statement processed

RMAN> alter database rename file '/u01/12c/oracledb/oradata/ORCL/onlinelog/o1_mf_2_fp6rmsyf_.log' to '/u01/12c/oracledb/oradata/o1_mf_2_fp6rmsyf_.log';

Statement processed

RMAN> alter database rename file '/u01/12c/oracledb/fast_recovery_area/ORCL/onlinelog/o1_mf_2_fp6rmt2k_.log' to '/u01/12c/oracledb/oradata/o1_mf_2_fp6rmt2k_.log';

Statement processed

RMAN> alter database rename file '/u01/12c/oracledb/oradata/ORCL/onlinelog/o1_mf_1_fp6rmmmf_.log' to '/u01/12c/oracledb/oradata/o1_mf_1_fp6rmmmf_.log';

Statement processed

RMAN>  alter database rename file '/u01/12c/oracledb/fast_recovery_area/ORCL/onlinelog/o1_mf_1_fp6rmmpr_.log' to '/u01/12c/oracledb/oradata/o1_mf_1_fp6rmmpr_.log';

Statement processed

RMAN>

now recover the database

connected to target database: ORCL (DBID=1510897456, not open)

RMAN> set decryption identified by "welcome123";

executing command: SET decryption

using target database control file instead of recovery catalog

RMAN> run

2> {

3> allocate channel t1 type 'SBT_TAPE' PARMS 'SBT_LIBRARY=/home/oracle/lib/libopc.so ENV=(OPC_PFILE=/u01/app/oracle/ORCLuct/12.1.0/dbhome_1/dbs/opcORCL.ora)';

4> recover database;

5> }

allocated channel: t1

channel t1: SID=12 device type=SBT_TAPE

channel t1: Oracle Database Backup Service Library VER=12.2.0.2

Starting recover at 23-AUG-18

starting media recovery

channel t1: starting archived log restore to default destination

channel t1: restoring archived log

archived log thread=1 sequence=18

channel t1: reading from backup piece 07taphf1_1_1

channel t1: piece handle=07taphf1_1_1 tag=TAG20180817T124937

channel t1: restored backup piece 1

channel t1: restore complete, elapsed time: 00:00:03

archived log file name=/u03/app/oracle/fast_recovery_area/ORCL/archivelog/2018_08_23/o1_mf_1_18_fqxsmb6c_.arc thread=1 sequence=18

channel default: deleting archived log(s)

archived log file name=/u03/app/oracle/fast_recovery_area/ORCL/archivelog/2018_08_23/o1_mf_1_18_fqxsmb6c_.arc RECID=5 STAMP=984934378

unable to find archived log

archived log thread=1 sequence=19

released channel: t1

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 08/23/2018 16:53:01

RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 19 and starting SCN of 1850362

RMAN> alter database open resetlogs;

Statement processed

RMAN> select open_mode from v$database;

OPEN_MODE

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

READ WRITE

RMAN>

Query the table

select count(1) from sam;

  COUNT(1)

----------

     89384

Thus, we have now have successfully performed a disaster recovery of on-premises database to the cloud using RMAN backups stored in Oracle Cloud Backup Storage.