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

Create Autonomous Database

This article provides a run through of creating a new Autonomous Database service on the Oracle Cloud.
 
Log into Oracle Cloud and click the “Create Instance” link.
Click on the “Create” button in the Autonomous Transaction Processing

 

Select the compartment you want to build the service in, then click the “Create Autonomous Database” button.

 

Enter the details of the service you want to create. The default sizes are 1 CPU core and 1TB of storage. Remember to select the appropriate licensing model. Click the “Create Autonomous Database” button.

 

Wait while the service is provisioned. You will see the state is marked as “Provisioning”.
The details screen allows you to perform some basic operations with the service, including scale up/down, manual backups and restores from backups. Click on the “Service Console” button.

 

You are presented with the dashboard, which will look quite empty as the service has just been provisioned. Click the “Activity” link on the left menu.

 

 

You are presented with the activity screen, which will look relatively quiet as the service has just been provisioned. Click the “Administration” link on the left menu.

 

The administration screen allows you to perform some basic administration of the service.

 

Connecting to the Autonomous Database Service Using SQL Developer
Go to the administration screen for the service and click the “Download Client Credentials (Wallet)”.

 

Enter the password to protect the credentials store.

 

Open SQL Developer and create a new connection. Use the username and password specified when you provisioned the service. Use a connection type of “Cloud Wallet” and enter the zip file location. You can now click the “Test” or “Connect” button.

 

Now all ready to use

 

Hope this helps. See you !!!
 
André  Ontalba  – www.dbadutra.com
 
Rodrigo Mufalani (OCM)
Oracle ACE Member  –  https://mufalani.wordpress.com

Generate dinamically commands to add the services in GI
Category: Database Author: Rodrigo Mufalani (Board Member) Date: 7 years ago Comments: 0

Generate dinamically commands to add the services in GI

 
During one migration activity we must copy all defined services associated with pluggable database from one cluster to another one. This task is not dificult, you can list the service in one side and add the services on your new cluster. But, we are humans and we can forget to add some service or even occurs some types during the creation of services, so, to be more accurate and avoid issues we decided to write a small shell script, I called gen_add_service.sh to add generate dinamically commands to add the services from the source GI to destination GI.

 

 

 
 
 
The script by itself is not rocket science, but some tricks can help you to solve some other  difficulties that I have during the construction of this small piece of code.
 
To reproduce this environment, I will create some services to PDB associated with it.
 
 

[oracle@ora18c3 ~]$ srvctl add service -d db01 -s pdb1_svc1 -pdb pdb1

[oracle@ora18c3 ~]$ srvctl add service -d db01 -s pdb1_svc2 -pdb pdb1

[oracle@ora18c3 ~]$ srvctl add service -d db01 -s pdb1_svc3 -pdb pdb1

[oracle@ora18c3 ~]$ srvctl add service -d db01 -s pdb2_svc1 -pdb pdb2

[oracle@ora18c3 ~]$ srvctl add service -d db01 -s pdb2_svc2 -pdb pdb2

[oracle@ora18c3 ~]$ srvctl add service -d db01 -s pdb2_svc3 -pdb pdb2

As you probably know, when you create the services you need to start them after..
 

[oracle@ora18c3 ~]$ srvctl status service -d db01

Service pdb1_svc1 is not running.

Service pdb1_svc2 is not running.

Service pdb1_svc3 is not running.

Service pdb2_svc1 is not running.

Service pdb2_svc2 is not running.

Service pdb2_svc3 is not running.

 

 Now, let’s start the services, after the creation…
 

 [oracle@ora18c3 ~]$ srvctl start service -s pdb1_svc1 -d db01

[oracle@ora18c3 ~]$ srvctl start service -s pdb1_svc2 -d db01

[oracle@ora18c3 ~]$ srvctl start service -s pdb1_svc3 -d db01

[oracle@ora18c3 ~]$ srvctl start service -s pdb2_svc3 -d db01

[oracle@ora18c3 ~]$ srvctl start service -s pdb2_svc2 -d db01

[oracle@ora18c3 ~]$ srvctl start service -s pdb2_svc1 -d db01

 

 

Now, after start, let’s check the service again..

 

[oracle@ora18c3 ~]$ srvctl status service -d db01

Service pdb1_svc1 is running

Service pdb1_svc2 is running

Service pdb1_svc3 is running

Service pdb2_svc1 is running

Service pdb2_svc2 is running

Service pdb2_svc3 is running     

 

When we check the service, I will just pickup one, as an example to analyze it

[oracle@ora18c3 ~]$ srvctl config service -d db01 -s pdb1_svc1

Service name: pdb1_svc1

Cardinality: SINGLETON

Service role: PRIMARY

Management policy: AUTOMATIC

DTP transaction: false

AQ HA notifications: false

Global: false

Commit Outcome: false

Failover type:

Failover method:

Failover retries:

Failover delay:

Failover restore: NONE

Connection Load Balancing Goal: LONG

Runtime Load Balancing Goal: NONE

TAF policy specification: NONE

Edition:

Pluggable database name: pdb1

Hub service:

Maximum lag time: ANY

SQL Translation Profile:

Retention: 86400 seconds

Replay Initiation Time: 300 seconds

Drain timeout:

Stop option:

Session State Consistency: DYNAMIC

GSM Flags: 0

Service is enabled

 

Now, let me explain the issues that I’ve got to wrote this script:
 

for SERVICE in `srvctl status service -d ${DBNAME} | awk -F” ” ‘{print $2}’`

do

   srvctl config service -d ${DBNAME} -s ${SERVICE} | grep “Service name\|Pluggable database name” | column -c 500 >> /tmp/pdbs.txt

done

 

cat /tmp/pdbs.txt | awk -F” ” ‘{print “srvctl add service -d ‘$DBNAME’ -s ” $3 ” -e SELECT -m BASIC -j LONG -P BASIC -pdb ” $7 ” -r ‘$DBNAME’1 -a ‘$DBNAME’2″}’

 

1 ) To list the service names, we used srvctl status serice -d <<DBNAME>> and then used srvctl config service to detailed output for each service.

 

Solution: Used a for to list and then grep all services and pdb names.
 
2) The lines Service Name and Pluggable database name are in separated lines on output.

 

Solution: To solve this, I user grep  “Service name\|Pluggable database name”
 
3) The output using grep with multiple values break the lines and I need to put all outputs on same line.

 

Solution: Used the column command to aggregate the output in just one line
 
4) Even with the aggregation, after some characters the output of my file /tmp/pdbs.txt got wrong results due to break in the line.

 

Solution: Used column -c 500
 
5) The last obstacle was the awk to print linux variables

 

Solution: To solve this and print variables is just use single quotes on variable names and with the command that I need to print at the screen.

 

[oracle@ora18c3 ~]$ sh gen_add_service.sh

Enter dbname to generate add services cmd

db01

srvctl add service -d db01 -s  -e SELECT -m BASIC -j LONG -P BASIC -pdb  -r db011 -a db012

srvctl add service -d db01 -s pdb1_svc1 -e SELECT -m BASIC -j LONG -P BASIC -pdb pdb1 -r db011 -a db012

srvctl add service -d db01 -s pdb1_svc2 -e SELECT -m BASIC -j LONG -P BASIC -pdb pdb1 -r db011 -a db012

srvctl add service -d db01 -s pdb1_svc3 -e SELECT -m BASIC -j LONG -P BASIC -pdb pdb1 -r db011 -a db012

srvctl add service -d db01 -s pdb2_svc1 -e SELECT -m BASIC -j LONG -P BASIC -pdb pdb2 -r db011 -a db012

srvctl add service -d db01 -s pdb2_svc2 -e SELECT -m BASIC -j LONG -P BASIC -pdb pdb2 -r db011 -a db012

srvctl add service -d db01 -s pdb2_svc3 -e SELECT -m BASIC -j LONG -P BASIC -pdb pdb2 -r db011 -a db012

 

I just put on the script whatever I wanted to formula the desired output to add my services on the new cluster. In my laptop I don’t have the RAC to add the services with multiple instances, but I hope that you got the idea and maybe use the tricks that I used to solve issues on your environment.

 

I hope this script help you on your next clone.
 
Best Regards,
Rodrigo Mufalani (OCM)
Oracle ACE Member  –  https://mufalani.wordpress.com       

 

 

 

 

 


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

 

Copyright (c) 1997, 2016, Oracle.  All rights reserved.

 

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

 

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

 

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

 

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

 

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.
 
 
 
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
 
 

 


1 … 29 30 31 32
LUXOUG – LUXEMBOURG ORACLE USERS GROUP
© 2026 LUXOUG – LUXEMBOURG ORACLE USERS GROUP