Cloud – Page 5 – LUXOUG – LUXEMBOURG ORACLE USERS GROUP

Cloud

Moving PDB from On-Premise to Cloud

Category: Cloud Author: Carlos Magno de Andrade Junior Date: 7 years ago Comments: 0

Moving PDB from On-Premise to Cloud

Be sure that the BUG Fix is applied on the Source Database:

– Be sure that the BUG Fix (18633374) is applied on the Source Database:

– The PSU 12.1.0.2.190416 or higher should be applied also on the source.

On-Premise

1.) Check Status of the Source Database:

SQL> select * from gv$pdbs where con_id=13;

INST_ID CON_ID DBID CON_UID GUID NAME OPEN_MODE RES OPEN_TIME CREATE_SCN TOTAL_SIZE BLOCK_SIZE RECOVERY SNAPSHOT_PARENT_CON_ID

———- ———- ———- ———- ——————————– —————————— ———- — ————————————————————————— ———- ———- ———- ——– ———————-

1 13 168895093 168895093 8837DFB294B8214DE054020820D8D4F1 PDBTEST READ WRITE NO 06-MAY-19 02.03.32.098 PM +02:00 8042252890 907018240 8192 ENABLED 0

2 13 168895093 168895093 8837DFB294B8214DE054020820D8D4F1 PDBTEST READ WRITE NO 06-MAY-19 02.04.32.119 PM +02:00 8042252890 907018240 8192 ENABLED 0

2.) GLobal user for DB LInk:

create user c##_link identified by link001#;

grant dba to c##_link container=all;

grant create pluggable database to c##_link container=all;

3.) Close the database:

SQL> alter pluggable database pdbtest close instances=all;

Pluggable database altered.

SQL> select * from gv$pdbs where con_id=13;

INST_ID CON_ID DBID CON_UID GUID NAME OPEN_MODE RES OPEN_TIME CREATE_SCN TOTAL_SIZE BLOCK_SIZE RECOVERY SNAPSHOT_PARENT_CON_ID

1 13 168895093 168895093 8837DFB294B8214DE054020820D8D4F1 PDBTEST MOUNTED 06-MAY-19 02.06.27.877 PM +02:00 8042252890 907018240 8192 ENABLED 0

2 13 168895093 168895093 8837DFB294B8214DE054020820D8D4F1 PDBTEST MOUNTED 06-MAY-19 02.06.28.166 PM +02:00 8042252890 907018240 8192 ENABLED 0

4.) open the Database in Just on Node in READ ONLY MODE:

SQL> alter pluggable database pdbtest open read only;

Pluggable database altered.

SQL> select * from gv$pdbs where con_id=13;

INST_ID CON_ID DBID CON_UID GUID NAME OPEN_MODE RES OPEN_TIME CREATE_SCN TOTAL_SIZE BLOCK_SIZE RECOVERY SNAPSHOT_PARENT_CON_ID

1 13 168895093 168895093 8837DFB294B8214DE054020820D8D4F1 PDBTEST READ ONLY NO 06-MAY-19 02.10.32.513 PM +02:00 8042252890 886046720 8192 ENABLED 0

2 13 168895093 168895093 8837DFB294B8214DE054020820D8D4F1 PDBTEST MOUNTED 06-MAY-19 02.06.28.166 PM +02:00 8042252890 886046720 8192 ENABLED 0

CLOUD:

5.) Create the TNS Entry on the TNSNAMES.ORA

PDBTEST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.165.13)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = PDBTEST)

)

[oracle@vits-racdb1 admin]$ tnsping pdbtest

TNS Ping Utility for Linux: Version 12.2.0.1.0 – Production on 06-MAY-2019 12:06:29

Used parameter files:

/u01/app/oracle/product/12.2.0.1/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.165.13)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PDBTEST)))

OK (40 msec)

[oracle@vits-racdb1 admin]$

[oracle@vits-racdb1 admin]$ sqlplus c##_link/link001#@pdbtest

SQL*Plus: Release 12.2.0.1.0 Production on Mon May 6 12:07:16 2019

Last Successful login time: Tue Apr 02 2019 13:26:37 +00:00

Connected to:

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

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Advanced Analytics and Real Application Testing options

SQL>

conn / as sysdba

6.) Create a database link from CLOUD Database to ON-Premise:

create public database link lnk_PDBTEST connect to c##_link identified by link001# using ‘PDBTEST’;

SQL> select * from v$pdbs@lnk_PDBTEST;

CON_ID DBID CON_UID GUID NAME OPEN_MODE RES OPEN_TIME CREATE_SCN TOTAL_SIZE BLOCK_SIZE RECOVERY SNAPSHOT_PARENT_CON_ID

———- ———- ———- ——————————– —————————— ———- — ————————————————————————— ———- ———- ———- ——– ———————-

13 168895093 168895093 8837DFB294B8214DE054020820D8D4F1 PDBTEST READ ONLY NO 06-MAY-19 02.10.32.513 PM +02:00 8042252890 886046720 8192 ENABLED 0

7.) Create the new PDB on the Oracle Cloud:

SQL> show parameter tables

NAME TYPE VALUE

———————————— ———– ——————————

encrypt_new_tablespaces string DDL

CREATE PLUGGABLE DATABASE PDBTEST FROM PDBTEST@lnk_PDBTEST;

SQL> SQL> SQL> SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

———- —————————— ———- ———-

2 PDB$SEED READ ONLY NO

3 CONFLUENCE READ WRITE NO

4 JIRA02 READ WRITE NO

5 PITBITBUCKET01 READ WRITE NO

6 PDBTEST MOUNTED

SQL> alter pluggable database PDBTEST open;

Warning: PDB altered with errors.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

———- —————————— ———- ———-

2 PDB$SEED READ ONLY NO

3 CONFLUENCE READ WRITE NO

4 JIRA02 READ WRITE NO

5 PITBITBUCKET01 READ WRITE NO

6 PDBTEST MIGRATE YES

8.) Check the violations

*** In case of errors with APEX

cd /u01/oracle/product/12.1.0/dbhome_1/apex

How to Uninstall Oracle HTML DB / Application Express from a 10G/11G Database (Doc ID 558340.1)

Deinstall the APEX from the PDB is is not in use.

*** Violations

set lin 1000

set pages 10000

select * from PDB_PLUG_IN_VIOLATIONS where status=’PENDING’ and TYPE=’ERROR’;

1* select * from PDB_PLUG_IN_VIOLATIONS where status=’PENDING’ and TYPE=’ERROR’

SQL> /

TIME NAME CAUSE TYPE ERROR_NUMBER LINE MESSAGE STATUS ACTION CON_ID

—————————— ————— —————————— ——— ———— ———- ———————————————————————————————————————— ——— ——————————————————————————– ———-

06-MAY-19 02.11.16.853559 PM PDBTEST VSN not match ERROR 0 1 PDB’s version does not match CDB’s version: PDB’s version 12.1.0.2.0. CDB’s version 12.2.0.1.0. PENDING Either upgrade the PDB or reload the components in the PDB. 6

9.) Upgrade the PDB:

cd $ORACLE_HOME/rdbms/admin

$ORACLE_HOME/perl/bin/perl catctl.pl -c ‘PDBTEST’ catupgrd.sql

sqlplus / as sysdba

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

———- —————————— ———- ———-

2 PDB$SEED READ ONLY NO

3 CONFLUENCE READ WRITE NO

4 JIRA02 READ WRITE NO

5 PITBITBUCKET01 READ WRITE NO

6 PDBTEST MOUNTED

alter pluggable database pdbtest open instances=all;

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

———- —————————— ———- ———-

2 PDB$SEED READ ONLY NO

3 CONFLUENCE READ WRITE NO

4 JIRA02 READ WRITE NO

5 PITBITBUCKET01 READ WRITE NO

6 PDBTEST READ WRITE YES

exit;

10.) Apply Datapatch

cd $ORACLE_HOME/OPatch

./datapatch -verbose -pdbs PDBTEST

sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue May 7 14:09:42 2019

Connected to:

Oracle Database 12c EE Extreme Perf Release 12.2.0.1.0 – 64bit Production

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

———- —————————— ———- ———-

2 PDB$SEED READ ONLY NO

3 CONFLUENCE READ WRITE NO

4 JIRA02 READ WRITE NO

5 PITBITBUCKET01 READ WRITE NO

6 PDBTEST READ WRITE YES

SQL> alter pluggable database PDBTEST close instances=all;

Pluggable database altered.

SQL> alter pluggable database PDBTEST open instances=all;

Pluggable database altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

———- —————————— ———- ———-

2 PDB$SEED READ ONLY NO

3 CONFLUENCE READ WRITE NO

4 JIRA02 READ WRITE NO

5 PITBITBUCKET01 READ WRITE NO

6 PDBTEST READ WRITE NO

11.) Update the backup TDE information for the PDB

After create the PDB on the cloud

In order to have the backups running, you must update the TDE Key for the new PDB Created/Attached.

DBCS OCI: How to create a new PDB in an OCI CDB? (Doc ID 2438598.1)

as root user on the CLoud DB Server:

dbcli list-databases

dbcli update-tdekey -i 040e267b-2719-453a-8c86-52844dcd3032 -p -n PDBTEST

We appreciate the author Carlos Magno – EzDBA for the contribution.

Creating a Compute Instance in OCI

Category: Cloud Author: Andre Luiz Dutra Ontalba (Board Member) Date: 7 years ago Comments: 0

Creating a Compute Instance on Oracle OCI

Let’s start our journey in the Oracle cloud

First we must log into the console the cloud.
In my case I created my account in Frankfurt, because I am using the services here in Europe.

You will be directed to the login of your Tenancy. Enter your user name and password and click “Sign in”.

Now that you are logged in, let’s start the creation of the Compute Instance.

Click Compute and then select Instances.

Click in Create Instance.

First of a name to your Compute Instance, I put Database_19c.

Then select in which Availability domain you want to create your Compute Instance. I chose the AD2.

After that we choose the Instance type, we can choose VM (Virtual Machine) or Bare Metal Machine (dedicated computing instance).

I chose the VM because it is cheaper in terms of using credits and also now I do not need to in a dedicated computing instance.

I will write an article later, giving an overview of the main principles and basic understandings to work with OCI.

Now we will generate the keys to use in our compute instance

I recommend you generate a key using the Putty Key Generator.

Click Generate and move the mouse until the key is created.

After it was created save a copy as Public Key and another as Private Key.

Now let’s add the public key to access the server after Compute Instance is created through SSH. Click Choose Files.

Select the file saved as Public Key, in my case Public_Keys.

After that, if you did not create any Virtual Network Circuit (VNC), it will be created automatically. In my case I already created then it has already been selected.

Now click Create and wait a few minutes.

Screen while creating the resource.

Ready your compute instance is OK.

Now we will access the VM through SSH.

Take the IP that looks like for you in this item and open in an SSH client, remembering that we should use the private key now to make the connection.

In my case I access using mobaXterm, in Remote host I put the IP, select the username and type “OPC” and select Use the Private Key that was generated.

Moving PDB from On-Premise to Cloud

Be sure that the BUG Fix is applied on the Source Database:

– Be sure that the BUG Fix (18633374) is applied on the Source Database:

– The PSU 12.1.0.2.190416 or higher should be applied also on the source.

On-Premise

1.) Check Status of the Source Database:

2.) GLobal user for DB LInk:

3.) Close the database:

4.) open the Database in Just on Node in READ ONLY MODE:

CLOUD:

5.) Create the TNS Entry on the TNSNAMES.ORA

6.) Create a database link from CLOUD Database to ON-Premise:

7.) Create the new PDB on the Oracle Cloud:

8.) Check the violations

9.) Upgrade the PDB:

10.) Apply Datapatch

11.) Update the backup TDE information for the PDB

We appreciate the author Carlos Magno – EzDBA for the contribution.

Creating a Compute Instance on Oracle OCI

Let’s start our journey in the Oracle cloud

First we must log into the console the cloud.In my case I created my account in Frankfurt, because I am using the services here in Europe.

You will be directed to the login of your Tenancy. Enter your user name and password and click “Sign in”.

Now that you are logged in, let’s start the creation of the Compute Instance.

Click Compute and then select Instances.

Click in Create Instance.

First of a name to your Compute Instance, I put Database_19c.

Then select in which Availability domain you want to create your Compute Instance. I chose the AD2.

After that we choose the Instance type, we can choose VM (Virtual Machine) or Bare Metal Machine (dedicated computing instance).

I chose the VM because it is cheaper in terms of using credits and also now I do not need to in a dedicated computing instance.

I will write an article later, giving an overview of the main principles and basic understandings to work with OCI.

Now we will generate the keys to use in our compute instance

I recommend you generate a key using the Putty Key Generator.

Click Generate and move the mouse until the key is created.

After it was created save a copy as Public Key and another as Private Key.

Now let’s add the public key to access the server after Compute Instance is created through SSH. Click Choose Files.

Select the file saved as Public Key, in my case Public_Keys.

After that, if you did not create any Virtual Network Circuit (VNC), it will be created automatically. In my case I already created then it has already been selected.

Now click Create and wait a few minutes.

Screen while creating the resource.

Ready your compute instance is OK.

Now we will access the VM through SSH.

In my case I access using mobaXterm, in Remote host I put the IP, select the username and type “OPC” and select Use the Private Key that was generated.

Ready server connected and ready to install Oracle Database.

Hope this helps. See you !!!

André Ontalba – www.dbadutra.com

Rodrigo Mufalani (OCM)

Oracle ACE Member – https://mufalani.wordpress.com

First we must log into the console the cloud.
In my case I created my account in Frankfurt, because I am using the services here in Europe.