By Joel Peréz ACED.gifSkant Gupta

 

This article is brought by en.enmotech.com

 

Full Index of Oracle Cloud Articles:

Sir.CloudDBaaSjoelperez

 

If you want to be updated with all our articles send us the Invitation or Follow us:

Joel Perez’s LinkedIn: www.linkedin.com/in/SirDBaaSJoelPerez

Skant Gupta's LinkedIn: www.linkedin.com/in/skantali/

or Join our LinkedIn group: Oracle Cloud DBaaS

 

This method can be used only if the on-premises platform is little endian, and the database character sets of your on-premises database and Oracle Database Cloud Service database are compatible.

 

To migrate an Oracle Database 12c PDB to a PDB in an Oracle Database 12c database on an Oracle Database Cloud Service deployment using the RMAN cross-platform transportable PDB method, you perform these tasks:

 

1.- On the on-premises database host, invoke SQL*Plus and close the on-premises PDB.

2.- On the on-premises database host, execute the ALTER PLUGGABLE DATABASE UNPLUG command to generate an XML file containing the list of datafiles that will be plugged in on the cloud database.

3.- On the on-premises database host, invoke RMAN and connect to the root. Execute the BACKUP FOR TRANSPORT PLUGGABLE DATABASE command.

4.- Creating the Database Cloud Service database

5.- Use a secure copy utility to transfer the XML file and the backup set to the Database Cloud Service compute node.

6.- On the Database Cloud Service compute node, invoke RMAN and connect to the root. Execute the RESTORE ALL FOREIGN DATAFILES command.

7.- the Database Cloud Service compute node, invoke SQL*Plus and connect to the root. Execute the CREATE PLUGGABLE DATABASE command.

8.- the Database Cloud Service compute node, execute the ALTER PLUGGABLE DATABASE OPEN command.

 

On-premises database to Cloud Using RMAN Cross-Platform Transportable PDB: Example

This example is to migrate an On-premises database to Cloud Using RMAN Cross-Platform Transportable PDB

In this example, the on-premises database is on a Linux host.

 

1.- On the on-premises database host, invoke SQL*Plus and close the on-premises PDB.

 

a) On the on-premises database host, invoke SQL*Plus and log in to the on-premises database as the SYS user.

[oracle@cloud ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun Jun 4 11:47:11 2017

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>

 

b) Close the on-premises PREM_PDB PDB.

SQL> ALTER PLUGGABLE DATABASE pdb_prem CLOSE;

Pluggable database altered.

SQL>

 

2. On the on-premises database host, execute the ALTER PLUGGABLE DATABASE UNPLUG command to generate an XML file containing the list of datafiles that will be plugged in on the cloud database.

 

a) Create a directory for creating the xml and backup file.

[oracle@cloud ~]$ mkdir –p /u01/app/dpump/

[oracle@cloud ~]$

 

b) Unplug the PDB. The following command generates the XML file.

SQL> ALTER PLUGGABLE DATABASE pdb_prem UNPLUG INTO '/u01/app/dpump/PDB_PREM.xml';

Pluggable database altered.

 

SQL>

 

3.- On the on-premises database host, invoke RMAN and connect to the root. Execute the BACKUP FOR TRANSPORT PLUGGABLE DATABASE command.

 

a) On the on-premises database host, invoke RMAN and log in to the on-premises database as the SYS user.

[oracle@cloud admin]$ rman target /

 

Recovery Manager: Release 12.1.0.2.0 - Production on Thu Jun 8 22:33:05 2017

 

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

 

connected to target database: CDBPREM (DBID=1893698667)

 

RMAN>

 

b) Take the backup

RMAN> BACKUP FOR TRANSPORT AS COMPRESSED BACKUPSET PLUGGABLE  DATABASE  'PDB_PREM'  FORMAT  '/u01/app/dpump/PDB_PREM.dfb';

Starting backup at 08-JUN-17

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=82 device type=DISK

channel ORA_DISK_1: starting compressed full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00011 name=/u01/app/oracle/oradata/CDBPREM/PDB_PREM/example01.dbf

input datafile file number=00009 name=/u01/app/oracle/oradata/CDBPREM/PDB_PREM/sysaux01.dbf

input datafile file number=00008 name=/u01/app/oracle/oradata/CDBPREM/PDB_PREM/system01.dbf

input datafile file number=00010 name=/u01/app/oracle/oradata/CDBPREM/PDB_PREM/SAMPLE_SCHEMA_users01.dbf

input datafile file number=00012 name=/u01/app/oracle/oradata/CDBPREM/fsindex01.dbf

input datafile file number=00013 name=/u01/app/oracle/oradata/CDBPREM/fsdata01.dbf

channel ORA_DISK_1: starting piece 1 at 08-JUN-17

channel ORA_DISK_1: finished piece 1 at 08-JUN-17

piece handle=/u01/app/dpump/PDB_PREM.dfb tag=TAG20170608T223310 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:01:35

Finished backup at 08-JUN-17

 

RMAN>

 

4.- Creating the new Cloud Database Service

a) Login to your Oracle cloud services account, go to the "Oracle Database Cloud Service" page and create a new service.

  • For Service Name, select Cloud-Mig12c.
  • From the Service Level list, select Oracle Database Cloud Service.
  • From the Metering Frequency list, select whatever frequency is appropriate for your environment.
  • From the Software Release list, select Oracle Database 12c Release1.
  • From the Software Edition list, select Enterprise Edition.
  • From the Database Type list, select Single Instance.

 

Then click Next to continue.

fig 01.JPG

Figure1. Creating a new service (Cloud-Mig12c)

 

b) In the Service Details screen, do the following:

  • For DB Name (SID), enter PRODDB.
  • Set an administrative password of your choice and confirm the password (this will be your sys password).
  • For Usable Database Storage (GB), enter 25.
  • From the Compute Shape list, select OC3 -1 OCPU, 7.5GB RAM (this is the bare minimum required).
  • For SSH Public Key, enter rsa-key-20170111.pub.

 

Then click Next to continue.

fig 02.JPG

Figure 2. Specifying the service details

 

c) Finally, review the configuration and click Create to create your cloud database.

fig 03.JPG

Figure 3. Creating the cloud database instance

 

After a few minutes, the cloud database instance has been created successfully.

fig 04.JPG

Figure 4. The cloud database has been created

 

d) Click the service name (Cloud-Mig12c) to open the main page of the database.

fig 05.JPG

Figure 5. Main page of the cloud database

 

e) Before trying to connect to the primary database instance on the cloud machine, you have to enable the dblistener access rule. Do the following:

a. Open the database service and select Access Rules from the menu.

fig 06.JPG

Figure 6. Selecting the Access Rules item

 

b. For the ora_p2_dblistener rule, select Enable from the Actions menu.

fig 07.JPG

Figure 7. Enabling the ora_p2_dblistener rule

 

Connect the Cloud Database

1. Open the instance of the PuTTY executable and connect to the machine using SSH public key.

fig 08.JPG

Figure 8. Connecting to machine using PuTTY

 

5.- Use a secure copy utility to transfer the XML file and the backup set to the Database Cloud Service compute node.

 

a) On the Database Cloud Service compute node, create a directory for the backup set and dump file.

[oracle@Cloud-Mig12c ~]$ mkdir –p /u01/app/on_premise/

[oracle@Cloud-Mig12c ~]$

 

b) Before using the scp command to copy files, make sure the SSH private key that provides access to the Database Cloud Service compute node is available on your on-premises host.

 

c) On the on-premises database host, use the SCP utility to transfer the backup set and the dump file to the Database Cloud Service compute node.

[oracle@cloud ~]$ scp -i rsa-key-20170111.ssh /u01/app/dpump/PDB_PREM.xml oracle@129.144.149.23:/u01/app/on_premise

Enter passphrase for key 'rsa-key-20170111.ssh':

PDB_PREM.xml                                                                                                                          100% 7681     7.5KB/s   00:00

[oracle@cloud ~]$ scp -i rsa-key-20170111.ssh /u01/app/dpump/PDB_PREM.dfb oracle@129.144.149.23:/u01/app/on_premise

Enter passphrase for key 'rsa-key-20170111.ssh':

PDB_PREM.dfb                                                                                                                          100%  190MB  55.8KB/s   58:06

[oracle@cloud ~]$

 

6.- On the Database Cloud Service compute node, invoke RMAN and connect to the root. Execute the RESTORE ALL FOREIGN DATAFILES command.

 

a) Invoke RMAN and log in to the PDB as a user that has been granted the SYSDBA or SYSBACKUP privilege.

[oracle@Cloud-Mig12c ~]$ rman target /

 

Recovery Manager: Release 12.1.0.2.0 - Production on Thu Jun 8 18:08:20 2017

 

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

 

connected to target database: PRODDB (DBID=685826250)

 

RMAN>

 

b) Execute the RESTORE command.

RMAN> RESTORE ALL FOREIGN DATAFILES TO NEW FROM BACKUPSET  '/u01/app/on_premise/PDB_PREM.dfb';

Starting restore at 08-JUN-17

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=36 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore

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

channel ORA_DISK_1: restoring all foreign files in backup piece

channel ORA_DISK_1: reading from backup piece /u01/app/on_premise/PDB_PREM.dfb

channel ORA_DISK_1: restoring foreign file 11 to /u02/app/oracle/oradata/PRODDB/datafile/o1_mf_example_dmm4nwql_.dbf

channel ORA_DISK_1: restoring foreign file 9 to /u02/app/oracle/oradata/PRODDB/datafile/o1_mf_sysaux_dmm4nwt1_.dbf

channel ORA_DISK_1: restoring foreign file 8 to /u02/app/oracle/oradata/PRODDB/datafile/o1_mf_system_dmm4nwx9_.dbf

channel ORA_DISK_1: restoring foreign file 10 to /u02/app/oracle/oradata/PRODDB/datafile/o1_mf_users_dmm4nx06_.dbf

channel ORA_DISK_1: restoring foreign file 12 to /u02/app/oracle/oradata/PRODDB/datafile/o1_mf_fsindex_dmm4nx1z_.dbf

channel ORA_DISK_1: restoring foreign file 13 to /u02/app/oracle/oradata/PRODDB/datafile/o1_mf_fsdata_dmm4nx3c_.dbf

channel ORA_DISK_1: foreign piece handle=/u01/app/on_premise/PDB_PREM.dfb

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:01:46

Finished restore at 08-JUN-17

 

c) Exit from RMAN.

 

7.- the Database Cloud Service compute node, invoke SQL*Plus and connect to the root. Execute the CREATE PLUGGABLE DATABASE command.

 

a) On the on-premises database host, invoke SQL*Plus and log in to the on-premises database as the SYS user.

[oracle@cloud ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun Jun 4 11:47:11 2017

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>

 

b) Execute the CREATE PLUGGABLE DATABASE command.

SQL> CREATE PLUGGABLE DATABASE PDB_PREM as clone USING '/u01/app/on_premise/PDB_PREM.xml'

source_file_name_convert = (

'/u01/app/oracle/oradata/CDBPREM/PDB_PREM/system01.dbf','/u02/app/oracle/oradata/PRODDB/datafile/o1_mf_system_dmm4nwx9_.dbf',

'/u01/app/oracle/oradata/CDBPREM/PDB_PREM/sysaux01.dbf','/u02/app/oracle/oradata/PRODDB/datafile/o1_mf_sysaux_dmm4nwt1_.dbf',

'/u01/app/oracle/oradata/CDBPREM/PDB_PREM/SAMPLE_SCHEMA_users01.dbf','/u02/app/oracle/oradata/PRODDB/datafile/o1_mf_users_dmm4nx06_.dbf',

'/u01/app/oracle/oradata/CDBPREM/PDB_PREM/example01.dbf','/u02/app/oracle/oradata/PRODDB/datafile/o1_mf_example_dmm4nwql_.dbf',

'/u01/app/oracle/oradata/CDBPREM/fsindex01.dbf','/u02/app/oracle/oradata/PRODDB/datafile/o1_mf_fsindex_dmm4nx1z_.dbf',

'/u01/app/oracle/oradata/CDBPREM/PDB_PREM/PDB_PREM_temp012017-05-21_01-01-24-PM.dbf','/u02/app/oracle/oradata/PRODDB/datafile/pdb4_temp01.dbf',

'/u01/app/oracle/oradata/CDBPREM/fsdata01.dbf','/u02/app/oracle/oradata/PRODDB/datafile/o1_mf_fsdata_dmm4nx3c_.dbf')

file_name_convert=NONE NOCOPY TEMPFILE REUSE;

Pluggable database altered.

 

SQL>

 

8.- The Database Cloud Service compute node, execute the ALTER PLUGGABLE DATABASE OPEN command.

 

a) Set the PDB_PREM to READ WRITE

SQL> ALTER PLUGGABLE DATABASE pdb_prem OPEN;

Pluggable database altered.

SQL>

 

b) Exit from SQL*Plus.

 

Well.. Until here, this article. We hope it can be useful for making grow your knowledge about Oracle Database Cloud.

 

If you want to be updated with all our articles send us the Invitation or Follow us:

Joel Perez’s LinkedIn: www.linkedin.com/in/SirDBaaSJoelPerez

Skant Gupta's LinkedIn: www.linkedin.com/in/skantali/

or Join our LinkedIn group: Oracle Cloud DBaaS

 

Full Index of Oracle Cloud Articles:

Sir.CloudDBaaSjoelperez

 

Author Bios:

 

Joel Pérez is an Expert DBA ( Oracle ACE Director, Maximum Availability OCM, OCM Cloud Admin. & OCM12c/11g ) with over 17 years of Real World Experience in Oracle Technology, specialised in design and implement solutions of: High Availability, Disaster Recovery, Upgrades, Replication, Cloud and all area related to Oracle Databases. International consultant with duties, conferences & activities in more than 50 countries and countless clients around the world. Habitual and one of leading writers of Technical Oracle articles for: OTN Spanish, Portuguese, English and more. Regular Speaker in worldwide Oracle events  like: OTN LAD (Latin America), OTN MENA (Middle East & Africa), OTN APAC ( Asian Pacific), DTCC China, Oracle Code.. . Joel has always been known for being a pioneer in Oracle technology since the early days of his career being the first Latin American awarded as “OTN Expert” at year 2003 by Oracle Corp., one of the first “Oracle ACE” globally in the Oracle ACE Program at year 2004. He was honoured as one of the first “OCM Database Cloud Administrator” & Maximum Availability OCM in the world. Currently Joel works as Senior Cloud Database Architect in “Yunhe Enmo (Beijing) Technology Co.,Ltd”., company located in Beijing, China En.enmotech.com

 

Skant Gupta is an Oracle Certified Cloud Professional in Oracle Database 12c, an Oracle Certified Expert in Oracle Real Application Clusters (Oracle RAC) in Oracle Database 11g and 12c, and an Oracle Exadata Certified and an Oracle Certified Professional in Oracle Database 10g, 11g, and 12c. He works at Vodafone Technology in the UK and formerly worked as a senior DBA at Etisalat in Dubai. He has six years of experience with various Oracle technologies, focusing mainly on Cloud, database, and high availability solutions, Oracle WebLogic Suite, Oracle Exadata and Oracle GoldenGate. He has presented at several Oracle user groups worldwide, most recently in the US, the United Arab Emirates, and the India. Skant website link: oracle-help.co