Feed aggregator

Free Oracle Cloud: 10. Running SQLcl and Datapump from the Compute Instance (VM) to ATP

Dimitri Gielis Blog - Tue, 10/01/2019 - 10:30
This post is part of a series of blog posts on the Best and Cheapest Oracle APEX hosting: Free Oracle Cloud.

In this post, we will install and use SQLcl and Datapump from the Compute Instance (VM) connecting to our Oracle Database in the Autonomous Transaction Processing (ATP) Cloud.
Although I use most of the time SQL Developer to connect to the database, I find it important to be able to use command-line tools too, as this is what you can automate and it's really fast.
In the previous post, we installed the command line tools of the Oracle Cloud on our own machine, but for the Oracle Tools, I prefer to install them on our Compute Instance in the Cloud. Especially when we want to automate something, it's easier to do this from another machine in the cloud. It also makes it easier to follow as we only have to focus on how to install the Oracle Tools on Linux.

Oracle Instant Client

In order to connect to an Oracle database from a machine, we will use the Oracle Instant Client software. You can download the software for the different operating systems, but as our VM is running Oracle Linux we can install it with just a few commands:

First, update yum so it's smarter where to find Oracle software:

yum install oracle-release-el7

Next, we can search for the Oracle Instant Client version we need:

yum search oracle-instant


We want to install the Oracle Instant Client version of the system we want to connect to. For the Free Oracle Database on ATP, it's Oracle Database Release 18.4, so we will pick Oracle Instant Client 18.5. To be honest, typically I take the latest version of the software, but when I tried that, the Oracle Instant Client complained the libraries were not compatible with the version we wanted to connect to. I always thought you could use newer versions of the Oracle tools against previous databases, but apparently, that is no longer the case (at least not during my tests). Anyway, it's good to have the version of the same tool as the version you connect to.
Install the Instant Client basic and tools packages:
yum install oracle-instantclient18.5-basic.x86_64
yum install oracle-instantclient18.5-tools.x86_64


As a last step we set some environment variables:

export PATH=/usr/lib/oracle/18.5/client64/bin:$PATH
export LD_LIBRARY_PATH=/usr/lib/oracle/18.5/client64/lib
export TNS_ADMIN=/usr/lib/oracle/18.5/client64/lib/network/admin

That's it! We can now use the Oracle tools. Note there's also a SQL Plus package, which allows you to connect from a command line to the database, but I prefer to use SQLcl as it has some cool features for Oracle APEX (e.g. exporting your app). Download SQLcl now.

Before we move on to installing SQLcl, make sure you still have the credentials (wallet) file we used when connecting with SQL Developer to our database. Just like with SQL Developer, we also need this with SQLcl to connect to our database. As a reminder here's the screenshot I'm talking about:


Upload both the SQLcl and Credentials zip file to the Compute Instance (VM):
scp -i .ssh/oraclecloud /Users/dgielis/Downloads/wallet_DBDIMI.zip opc@132.145.215.55:/tmp
scp -i .ssh/oraclecloud /Users/dgielis/Downloads/sqlcl-19.2.1.206.1649.zip opc@132.145.215.55:/tmp


Connect to your VM and unzip the files:

ssh -i .ssh/oraclecloud opc@132.145.215.55

unzip /tmp/wallet_DBDIMI.zip -d /usr/lib/oracle/18.5/client64/lib/network/admin

unzip /tmp/sqlcl-19.2.1.206.1649.zip -d /opt


Before we can run SQLcl we also need to make sure we have JAVA installed, as SQLcl depends on that:
yum install java

To make it easier to run SQLcl from anywhere we will create a symbolic link:

ln -s /opt/sqlcl/bin/sql /usr/lib/oracle/18.5/client64/bin/sql

Now we are ready to connect to our database on ATP:

sql admin@dbdimi_high


There we go... we can connect from our VM to our ATP database.

The next thing we want to do is export the data from our ATP database. We will use Datapump that came with the installation of the tools.

Run the command to export the schema CLOUD:

expdp admin@dbdimi_high \
exclude=index,cluster,indextype,materialized_view,materialized_view_log,materialized_zonemap,db_link \
data_options=group_partition_table_data  \
parallel=1 \
schemas=cloud \
dumpfile=export%u.dmp


So where did this export go? To the default DATA_PUMP_DIR directory we don't have direct access to... but to list the files in the directory we can do:

SELECT * FROM DBMS_CLOUD.LIST_FILES('DATA_PUMP_DIR');


Remember my previous blog post about the Object Storage, in which we set up a Backups bucket?
Oracle allows you to connect your Object Storage to your ATP database and that is exactly what we will do further on :)

We will use the same user we created earlier for CLI. In order to connect to ATP we need to set up an Auth Token. Go to the User Details of cliUser and click the Auth Tokens:


Click the Generate Token button:


There's the token... you only see it once, so make sure to copy it:


Next, connect to your ATP database and run the script to add the credentials to the ATP database:

begin
  dbms_cloud.create_credential(
    credential_name => 'DEF_CRED_NAME'
    , username => 'cliUser'
    , password => 'Frx}R9lD0O}dIgZRGs{:'
  );
end;
/


Now that the DBMS_CLOUD package has credentials, we can do other calls with this package.
To add the Datapump export files to the Object Storage, we can use the PUT_OBJECT procedure.

I created a small script to take all the files from the DATA_PUMP_DIR and put them in the backups Bucket in the Object Storage:

begin
  for r in (select object_name, bytes
              from dbms_cloud.list_files('DATA_PUMP_DIR'))
  loop
   dbms_cloud.put_object(credential_name => 'DEF_CRED_NAME',
     object_uri => 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/id9u4qbhnjxj/b/backups/o/'||r.object_name,
     directory_name => 'DATA_PUMP_DIR',
     file_name => r.object_name);
  end loop;     
end;
/

And when we check our bucket, we see the Datapump export files! Yay!


We also want to export our Oracle APEX apps. In some projects, I use the APEXExport utility, but now we will use SQLcl to export our APEX app 101:

apex export 101

In real life I typically create a few scripts which I can run one-by-one or combined in a general backup script. The script will export the Oracle schemas, the APEX apps and save the files to another location, in our case the Object Storage.

vi make_backup.sh


 Here are the details of the scripts which are called in the main backup script:


You can schedule this script with crontab, for example, every day at 2AM:


The above is just an example of what you can do to automate your backups. You have to decide how frequently you want to do those backups.


If you want to move your existing Oracle database and APEX apps to the Oracle Cloud, the steps are similar to above. You upload your Datapump export file to your Object Storage. Next, run the Data Pump Import with the dump file parameter set to the list of file URLs on your Cloud Object Storage and the credential parameter set to the name of the credential you created earlier. For example:

impdp admin/password@dbdimi_high \  
     directory=data_pump_dir \  
     credential=def_cred_name \
     dumpfile= https://objectstorage.us-ashburn-1.oraclecloud.com/n/adwc/b/adwc_user/o/export%u.dmp \
     parallel=1 \
     partition_options=merge \
     transform=segment_attributes:n \
     transform=dwcs_cvt_iots:y transform=constraint_use_default_index:y \
exclude=index,cluster,indextype,materialized_view,materialized_view_log,materialized_zonemap,db_link

Next, you would create your APEX workspace and import the APEX apps.


In the next post, we dive again in Oracle APEX and how to send emails from your APEX app.

Free Oracle Cloud: 9. Setup Object Storage and use for File Share and Backups

Dimitri Gielis Blog - Sat, 09/28/2019 - 16:22
This post is part of a series of blog posts on the Best and Cheapest Oracle APEX hosting: Free Oracle Cloud.

In this post, we will look into how we can store and share files, with ourselves, with others, but also with our ATP Database, we set up earlier. The Oracle Cloud provides for this purpose Object Storage, and we get 20GB for free forever. This storage is also being used when you want to make backups of the database, and use Datapump to export and import data. In the previous blog post, when you followed the installation of the on-premises version of AOP, you actually connected to my Object Storage as the AOP zip file is on it.

Now we know the purpose of this Object Storage, let's get started to set it up.

Log in to the Oracle Cloud and navigate in the menu to Object Storage:

You arrive at the screen where you have to pick a compartment in the dropdown on the left. Compartments are used to organize your resources.


After selecting my compartment "dimi (root)", we get an overview of Buckets and we can create a new one by clicking on the Create Bucket button:


In the Create Bucket screen, you enter a name and the type of storage you want to add in that bucket.
First, we create an archive bucket, in which to store backups of our database:


In the next screen we create a standard bucket to share files with others:


We can now see we have two buckets: apexofficeprint (standad storage) and backups (archive storage). Note the 3 dots on the right of the bucket, which give you the ability to view details or perform some other actions:


As I wanted to share the AOP zip file in my object storage, in the menu I click the Edit Visibility link to make the bucket accessible for everybody (public).


Next, we will add a file to the bucket. Click the View Bucket Details:


We get the details of the Bucket and see an overview of the Objects in this Bucket:


Click the Upload Objects button:


Drag a file in the Drop files area and hit the Upload Objects button:


We arrive back in the overview screen:


Just like in the Buckets overview, in the Objects Overview next to the objects you have the 3 dots on the right to perform actions on the object:


Click the View Object Details and you find next to some info, the URL where your object is accessible from:


So to conclude, an Object Storage exists out of Buckets which live in a certain Compartment and a Bucket exists out of Objects. (Object Storage => Compartments => Buckets => Objects)


Above we used the Oracle Cloud website to work with our Object Storage, but I'm also interested to do this from the command line. For example, we automated our build process when we make a new release of APEX Office Print. In the final step, we want to upload the new zip file to the cloud. When we can do everything from the command line, we can script and automate it.


You can follow the documentation on how to install the Command Line Interface (CLI).

I did the following on OSX (from Terminal) to install CLI:

bash -c "$(curl -L https://raw.githubusercontent.com/oracle/oci-cli/master/scripts/install/install.sh)"

The install script asked a few questions (where to install etc.) but I used all defaults and at the end, all was installed fine and I got the following screen:


At the end I moved everything from the bin directory in my default directory, so it's easier to call the CLI:

mv bin/* .

Next, configure CLI. We have to let the CLI know who we are. This is done by creating a config file and specifying the user CLI can connect as. Here we go:

./oci setup keys


Go to the Oracle Cloud website and set up a User based on the keys you created with CLI.

Go to Identity - Users in the menu and click the Create User button:


Enter a new user, as I wanted to create a user-specific for CLI I called it cliUser. Note that the email address you provide needs to be unique. I initially used the same email, but that didn't work.


Now we will need to add the key to this user. Click the View User Details button in the action menu of the user:


Click the Add Public Key button:


And copy the content of the ~/.oci/oci_api_key_public.pem file:


Next, we want to give this user Administrator privileges. To do so, go to Groups:


Click the Add User to Group button:


Select Administrators and hit the Add button:


Now, this user is all set to work with the Command Line Interface (CLI):


We will now configure the CLI to connect as the cliUser we just created.
In your terminal run in your home directory:

./oci setup config


The wizard asks for some OCIDs. Below I walk you through where to find them.

The user OCID, go to Identity > Users and the details of the user. Next to OCID click the Copy link:


The tenancy OCID you find in Administration > Tenancy Details:





















The location you find when you scroll down in the Regions section of the Tenancy screen. Yours will be highlighted in green.


The final question of the wizard is if you want to create a new RSA key, answer No and point to the file (and not the directory like I first did). This is how it looks like after answering the questions:


Once the wizard is complete, you are all set. You can view the config file by doing:

cat .oci/config


Next, we want to create a Bucket in a Compartment and add a file through the CLI to that bucket.

Before we can run the command, we have to know the OCI of the Compartment. Here're the steps to get to that. Identity > Compartments:


 In the Details you find the OCI:


Now that we have everything we need, we can run the command to create a bucket called clibucket:

./oci os bucket create -c ocid1.tenancy.oc1..aaaaaaaakmf6mlauyaqmkkcikiuu2ckmklhffxf2weheu3qtfnsvcuzfuiuq --name clibucket


On success, we get a JSON back with the details of the bucket. If it errors, you will get an error back with details in JSON format.

Just to make sure the bucket is there, go to the Oracle Cloud website and check if you see the bucket we created with CLI:


To add an object to the bucket, you can do:

./oci os object put -bn clibucket --file test1.txt


Other commands I use more frequently:

# Get a list of the objects:
./oci os object list -bn clibucket

# Download a file called test1.txt and save it on your system as test2.txt
./oci os object get -bn clibucket --file test2.txt --name test1.txt

A useful resource to know which commands the CLI understands is in this documentation.
I did the above as an administrator, but if you want to read more about restricted use, you can read about that in Lawrence Gabriel's blog post.

I believe now you have a good understanding of the Object Storage and how to work with it through the website or through CLI. One of the reasons I mentioned was to use the Object Storage as a place for backups... let's look into that now.

When we go to our Autonomous Database, select your database and go into details. In the Resources section you find a link Backups:


Normally backups are automatically taken in ATP, but you can also create a manual backup:


When you create a manual backup, you have to specify the Object Storage bucket you want the backup to be saved in. But, when I tried to create a manual backup it told me that this option is not available in the Always Free Tier. Also, it says that restore is not possible, so not sure what happens when you want to restore an automated taken backup... for now, I'm not relying on those backups, in my next blog post I will tell you what I do for backups.


In the next blog post of this series, I will walk you how to use the Object Storage with the ATP Oracle Database to export and import data.

Presenting at UKOUG Techfest19 Conference in Brighton, UK

Richard Foote's Blog - Fri, 09/13/2019 - 00:07
I’m very excited to be attending my 3rd UKOUG Conference, this year re-badged as Techfest19. The fact it’s being held in Brighton is a little disconcerting for a Crystal Palace fan, but really looking forward nonetheless to what has always been one of the very best Oracle conferences on the yearly calendar. I have a […]

Announcement: Australia/NZ “Let’s Talk Database” Events October 2019 !!

Richard Foote's Blog - Thu, 09/12/2019 - 03:49
I’ve very excited to announce the next series of Oracle “Let’s Talk Database” events to be run throughout Australia and New Zealand in October 2019. I’ll be discussing two exciting topics this series, “Oracle Database 19c New Features” and “Oracle Exadata X8“. As always, these sessions run between 9am-1pm, include a networking lunch and are free, […]

Oracle Database 19c Automatic Indexing: Default Index Column Order Part II (Future Legend)

Richard Foote's Blog - Wed, 09/11/2019 - 01:59
In Part I, we explored some options that Oracle might adopt when ordering the columns within an Automatic Index by default, in the absence of other factors where there is only the one SQL statement to be concerned with. A point worth making is that if all columns of an index are specified within SQL […]

London March 2020: “Oracle Indexing Internals and Best Practices” and “Oracle Performance Diagnostics and Tuning” Seminars !!

Richard Foote's Blog - Tue, 09/03/2019 - 11:44
It’s with great excitement that I announce I’ll finally be returning to London, UK in March 2020 to run both of my highly acclaimed seminars. The dates and registration links are as follows: 23-24 March 2020: “Oracle Indexing Internals and Best Practices” seminar – Tickets and Registration Link 25-26 March 2020: “Oracle Performance Diagnostics and […]

Oracle Database 19c Automatic Indexing: Default Index Column Order Part I (Anyway Anyhow Anywhere)

Richard Foote's Blog - Mon, 09/02/2019 - 13:06
The next thing I was curious about regarding Automatic Indexing was in which order would Oracle by default order the columns within an index. This can be a crucial decision with respect to the effectiveness of the index (but then again, may not be so crucial as well). Certainly one would expect the index column […]

Announcement: New “Oracle Indexing Internals and Best Practices” Webinar – 19-23 November 2019 in USA Friendly Time Zone

Richard Foote's Blog - Mon, 09/02/2019 - 04:54
I’m very excited to announce a new Webinar series for my highly acclaimed “Oracle Indexing Internals and Best Practices” training event, running between 19-23 November 2019 !! Indexes are fundamental to every Oracle database and are crucial for optimal performance. However, there’s an incredible amount of misconception, misunderstanding and pure myth regarding how Oracle indexes function […]

Oracle 19c Automatic Indexing: How Many Executions Does It Take? (One Shot)

Richard Foote's Blog - Thu, 08/29/2019 - 04:16
One of the first questions I asked when playing with the new Oracle Database 19c Automatic Indexing feature was how many executions of an SQL does it take for a new index to be considered? To find out, I create the following table: I then ran the following query just once and checked to see […]

Speaking at Trivadis Performance Days 2019

Richard Foote's Blog - Wed, 08/28/2019 - 11:36
I’ll again be speaking at the wonderful Trivadis Performance Days 2019 conference in Zurich, Switzerland on 26-27 September. There’s again another fantastic lineup of speakers, including: CHRISTIAN ANTOGNINI IVICA ARSOV MARK ASHDOWN SHASANK CHAVAN EMILIANO FUSAGLIA STEPHAN KÖHLER JONATHAN LEWIS FRANCK PACHOT TANEL PODER DANI SCHNIDER   I’ll be presenting two papers: “Oracle 18c and […]

Speaking at Oracle OpenWorld 2019

Richard Foote's Blog - Thu, 08/22/2019 - 08:23
It’s been remarkably 9 years since I’ve been to Oracle OpenWorld, but will finally get the opportunity to present there again this year (with many thanks to the Oracle ACE Director program for making this possible). Details of my presentation are as follows: Conference: Oracle OpenWorld Session Type: Conference Session Session ID: CON1432 Session Title: […]

Pages

Subscribe to Better Logic LLC aggregator