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
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,
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
[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.
Thank you for your valuable content , Easy to understand and follow. As said, the migration to cloud is very essential for the protection of the database. Cloud Migration Services
ReplyDelete