Oracle Autonomous Database Migration Using Datapump.

Reviewing Migration Options

The best method for moving a database of medium or larger size to the Autonomous Database Cloud Service is DATAPUMP import.  

Sql*Loader is also an option, but the unload is slow, takes awhile, is error-prone, and does not gaurantee data consistency, as each table is unloaded at different points of time.

A final, somewhat viable method is using Goldengate, which is an additional licensing cost (in most cases), and requires complex configuration and expertise to implement properly.  Even then, Goldengate is best used in conjunction with datapump for larger databases, and will be quite costly in terms of licensing and duration.  

SQL*Loader and Goldengate can work for some use cases, but are not the subject of this post.  

A faster method might involve the use of Oracle's backup/recovery product, known as RMAN, but it is not available as a tool in Autonomous at this point in 2024. 

(This may change in the future - and I predict it will - stay tuned for a new blog should this happen!).  

Why is datapump the best alternative?

It is important to understand that Oracle's Autonomous Database is a serverless implementation.  Due to this, you will not have direct access to the back end linux server and OS hosting the database.  This is standard with other Oracle Database implementations. 

Please also understand that this offers Oracle and the customer some great economies - operating system infrastructure is a variable cost and maintenance headache on it's own - and by taking care of this for you - and in a consistent way - time and money can be saved.  

It also means the customer can get by without even needing these skills.  

Also understand that this is the way the industry is headed.  Unless you pay more, all the major cloud vendors are pushing towards the serverless model for databases - and other services as well.

Using DATAPUMP with Autonomous Database in OCI:

Oracle has put their best foot forward in making enhancements to facilitate the use of datapump for loading ADB.  A new configuration parameter, called CREDENTIALS, has been added.  You are also now allowed to specify a web URL for the DUMPFILES parameter to indicate where your source files are located.  

Important Note: 

It is now MANDATORY that the dumpfiles location contains a URL with the storage location of datafiles.  

In the past, a local or network file system on the database server was the only supported option.  

This does not work anymore!

In order to load data from a specified URL to an autonomous database, several steps must be taken.  The first of which is to assure the storage location specified is supported by OCI ADB.  At this writing, there are several option.  Of course, Oracle's object storage is among them, but Amazon S3, Microsoft Azure, and Google Cloud Storage are also supported.  This represents a multicloud initiative from Oracle which changes by the day, so more may be coming soon!  

Check here for the latest features and changes, including multi-cloud options for hosting the database itself!

Connecting ADB and Object Storage    

Because of the nature of Cloud infrastructure and the need for security above and beyond requirements from just a few years ago, your Database will not have immediate access to your object storage by default, even if they are in the same tenancy/region/compartment.  This connection requires both a security configuration in your cloud and the target database.  

First, your account in OCI must have permissions to access the storage.  This can be implied with the proper level of permissions in the IAM of the tenancy for your account.  This configuration and it's details are covered separately, but more information is also in the documentation link above.  

Secondly, the database needs credentials explicitly detailing the URL of the storage.  This is done by using the DBMS_CLOUD package, which comes pre-built and installed with the database.  

DBMS_CLOUD.CREATE_CREDENTIAL is the first procedure which will be needed.

DBMS_CLOUD.PUT_OBJECT might also be needed to load a local file to cloud object storage.

Depending upon the Oracle version, more commands may be required, one of which includes setting defaults for the storage location.  

Important Notes:  All users to be imported must be precreated, and given a large quota to the main supported tablespace DATA.  

Finally, the special datapump configuration parameters (credentials/dumpfiles URL) will validate supplied credentials and storage URL.  

The background and purpose of this is all about security.  Since the Cloud and the internet are public, all vendors are required (by law, in many cases), that access and movement of all data be authenticated.  

The purpose of this blog is to present a conceptual overview and understand of how Autonomous Database is different and decidedly new for most Oracle Database professionals.  A very good summary set of documentation, with current syntax for the entire process, is available here. 

Please respond in the comments if you would like more information, more details, or to discuss this complex topic.  

Logfiles In OCI - Part 1

 This post will dive into what can turn into an intimidating and frustrating topic - troubleshooting failures in OCI.  While it is fantastic that you can literally "press a button" and create a database, what happens when that action fails?  

You can actually create a dataguard copy of an existing OCI database using the console.  We will delve into the exact steps to do so separately.  However, in one case, after building several dataguard instances successfully, we recieved the message "Dataguard Build Failed - Contact Oracle Support".  

If you do this action, you will be prompted to open a support request.  The process to work through the request will involve collecting logs and other information.  To save time, there is documentation which will show you the information Oracle will need to help troubleshoot the issue.  Reading these documents yourself might just pinpoint the problem!

Logfiles for OCI operations will be created in addition to the general information contained in the Oracle alert logs and normal database locations.  

For example, OCI dataguard processing first step is the deployer, and it's logfiles will be located in the following directory on the target server:

/var/opt/oracle/log/<dbname>/dgdeployer/dgdeployer.log

The deployer will call the dbaasapi utility, and it's logs will appear in:

/var/opt/oracle/log/dbaasapi/db/dg/<job_ID>.log. 

Look for entries that contain dg_api.  A primary and standby log should be found in this location.

Any additional standby logs will be located here:

/var/opt/oracle/log/<dbname>/dgcc/dgcc.log

The "Oracle Cloud Deployment Engine" will also generate operational logs.  These will be located in:

/var/opt/oracle/log/<dbname>/ocde/ocde.log

Also, a Data Guard configuration log file is in:

/var/opt/oracle/log/<dbname>/dgcc/dgcc.log.

Each node of a RAC system will need to be reviewed and these logs will need to be examined and collected (if present) from all of them.  

These logs are in addition to standard database logs such as the alert log, trace files, and perhaps listener and sqlnet logs, as dataguard build are typically network-related.  

I know this is complicated, and daunting.  Oracle support may be needed to unravel the more complex situations.  The "dbaascli diag collect" utility can bundle needed artifacts from a failed process.   You may be asked by Oracle to collect these, as well as AHF and other log collection tools.

The next post will cover some of the issues and solutions associated with dataguard builds.  Just knowing where to look for analyzing the problem is a good first step!

SCREEN Unix/Linux Command: An Oldie But A Goody!

 Back in the day, when listening to AM Radio to hear all the latest and greatest pop hits, every now and then, the DJ would announce that he would play "an oldie but a goody" - which was a song no longer on the charts, but still very popular.  Here are a few to listen to if you have the time: 

OLDIES_BUT_GOODIES

I recently ran into a spurious issue where normally stable terminal sessions started to detach for no known reason.  It is probably some kind of network instability.  We had the network timeout settings for our sessions set to zero (no maximum). See Keep Alive or Never time out

 Keep in mind that on some systems, security may limit this.  

So what do you do when you have a long running process and there is no way to keep it in the background?  For instance - the process runs for quite some time, but occasionally requires user input such as a password or application information.  

I recently ran into such a situation.  Trying to build a rather large database from a backup can take hours.  Building this database via a tooling script from a cloud provider required intermittent responses with passwords and wallet keys, but no way to input them as parameters in advance (poorly written, if you ask me).  

This brings us to using the SCREEN command.  It certainly serves the purpose, but it can be a little "kludgey" and may be different in each linux/unix variant.  In fact, it is not always present and may need to be installed:

sudo apt install screen

An example of it's use is hard to depict, since it involves a session gone awry, but step-by-step:

1) Create a (saved) screen session:

screen -S mysavedsession

2) Verify its presence:

$ screen -ls

There is a screen on:

61450.savethis (Detached)

1 Socket in /var/run/screen/S-opc.

3) Run a command with continuous output:

ping cnn.com

From 192.168.100.2 (192.168.100.2) icmp_seq=1 Packet filtered

From 192.168.100.2 (192.168.100.2) icmp_seq=2 Packet filtered

From 192.168.100.2 (192.168.100.2) icmp_seq=3 Packet filtered

From 192.168.100.2 (192.168.100.2) icmp_seq=4 Packet filtered

From 192.168.100.2 (192.168.100.2) icmp_seq=5 Packet filtered

From 192.168.100.2 (192.168.100.2) icmp_seq=6 Packet filtered

From 192.168.100.2 (192.168.100.2) icmp_seq=7 Packet filtered

From 192.168.100.2 (192.168.100.2) icmp_seq=8 Packet filtered

From 192.168.100.2 (192.168.100.2) icmp_seq=9 Packet filtered

From 192.168.100.2 (192.168.100.2) icmp_seq=10 Packet filtered

From 192.168.100.2 (192.168.100.2) icmp_seq=11 Packet filtered

From 192.168.100.2 (192.168.100.2) icmp_seq=12 Packet filtered

From 192.168.100.2 (192.168.100.2) icmp_seq=13 Packet filtered

From 192.168.100.2 (192.168.100.2) icmp_seq=14 Packet filtered

From 192.168.100.2 (192.168.100.2) icmp_seq=15 Packet filtered

From 192.168.100.2 (192.168.100.2) icmp_seq=16 Packet filtered

From 192.168.100.2 (192.168.100.2) icmp_seq=17 Packet filtered

From 192.168.100.2 (192.168.100.2) icmp_seq=18 Packet filtered

From 192.168.100.2 (192.168.100.2) icmp_seq=19 Packet filtered

From 192.168.100.2 (192.168.100.2) icmp_seq=40 Packet filtered

4) terminate the session....(do not cntrl-c!) ...literally close the terminal window while it is running.

5) Start another session with the same user account: 

ssh opc@myserver

6) Again (optionally) verify existence of the session.  It should appear as "(Detached)" which is what we want!:

screen -ls

There is a screen on:

61450.savethis (Detached)

1 Socket in /var/run/screen/S-opc.

$ screen -r savethis. # output should resume:

From 192.168.100.2 (192.168.100.2) icmp_seq=30 Packet filtered

From 192.168.100.2 (192.168.100.2) icmp_seq=31 Packet filtered

From 192.168.100.2 (192.168.100.2) icmp_seq=32 Packet filtered

From 192.168.100.2 (192.168.100.2) icmp_seq=33 Packet filtered

From 192.168.100.2 (192.168.100.2) icmp_seq=34 Packet filtered

From 192.168.100.2 (192.168.100.2) icmp_seq=35 Packet filtered

From 192.168.100.2 (192.168.100.2) icmp_seq=36 Packet filtered

From 192.168.100.2 (192.168.100.2) icmp_seq=37 Packet filtered

From 192.168.100.2 (192.168.100.2) icmp_seq=38 Packet filtered

From 192.168.100.2 (192.168.100.2) icmp_seq=39 Packet filtered

From 192.168.100.2 (192.168.100.2) icmp_seq=40 Packet filtered

This can be useful whether you have a long running job, or simply decide to get up for a cup of coffee.  You never know when your connection could be lost, either intentionally or due to an unforeseen event!

HOW TO: Cloud Exadata Timezone Change

 A client recently realized that over the years, they had timezone inconsistencies across their database systems.  They had more or less adopted and learned to live with them.  While this can be done, complications can arise when using Data-guard, or doing timestamp recovery.  

As they were migrating to Oracle Cloud Infrastructure and CloudExa, I explained this would be an excellent time to make all of their systems consistent.  

They had already built out their test system using an inherited timezone, so we had to figure out how to change it.  There were no cloud-specific documents in the support area on this issue, so we decided to open a Support Request with Oracle.  This is not always known, but an "SR" can be opened to document or verify a procedure when the platform is newer and documents do not specifically mention it.  

It took a few days, but after some research and re-checking with development, a support analyst said that it was believed that an on-premise document and procedure could be followed, but he will leave the request open should we run into problems.  This document link is HERE.  

Unfortunately, while very close to our situation, we found an anamoly or difference on Cloud Exadata that made it impossible to follow verbatim.  It required the existence of the file: 

/etc/sysconfig/clock 

Our system did not have this file on either of it's two nodes!

So we notified Oracle.  Once again, they went back to cloud development to seek direction due to this unplanned situation.  Development was unaware that cloud systems could be delivered without this file, and they considered it a bug and will be opening one to address this.  

So now we needed a new plan.  Between development, the analyst and ourselves, we checked on the format of the file (it was actually very simple).  it consists of this verbiage for our case:

ZONE="America/New_York"  Oracle support did not give us this exact syntax, however, there is a group of files and directories with examples in /usr/share/zoneinfo.  Under subdirectory "America" is a file named "New_York", and that exact set of characters was consistent with the example in the documentation referenced above.  

The system is currently running in Mountain Time, which can be seen by executing this command:

$ date

Tue Jul 25 07:57:53 MDT 2023

So, nothing is changed yet.  We just needed to verify how to proceed.  After arranging for a complete database outage with interested parties, the next step was to shutdown all services in the Grid Infrastructure stack as user root:

$ crsctl stop crs

Messages returned:

CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'exa-skyze1'

.........

CRS-4133: Oracle High Availability Services has been stopped.

Next we should verify everything is stopped:

$ crsctl stat res -t

Messages returned:

CRS-4535: Cannot communicate with Cluster Ready Services

CRS-4000: Command Status failed, or completed with errors.

Above errors are expected results - the CRS is not running!

Now onto the timezone specific changes:

As root, the file /etc/sysconfig/clock was created with the "ZONE=America/New_York":

[root@exa ~]# date

Tue Jul 25 07:38:51 MDT 2023

[root@exa- ~]# vi /etc/sysconfig/clock

[root@exa ~]# date

Tue Jul 25 07:39:31 MDT 2023

[root@exa- ~]# cat /etc/sysconfig/clock

ZONE="America/New_York"

(please note full system name is obfuscated to maintain client confidentiality).  

This next command will actually change the timezone on the nodes:

ln -sf /usr/share/zoneinfo/America/New_York /etc/localtime

Now for the results!

[root@exa ~]]# date

Tue Jul 25 09:45:14 EDT 2023

Above steps must be repeated on other node(s).  

Once both nodes show the correct local time, we can restart services:

# crsctl start crs

CRS-4123: Oracle High Availability Services has been started.

(We had to wait several minutes until all services were restared).  

Now the OS and all services are running on the new timezone.  

However, when we logged into the oracle account and the databases, we found the task was still incomplete!!!

$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jul 25 07:54:59 2023

Version 19.18.0.0.0

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

Connected to:

Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production

Version 19.18.0.0.0

SQL> select systimestamp from dual;

SYSTIMESTAMP

---------------------------------------------------------------------------

25-JUL-23 07.55.08.703743 AM -06:00

The above time is still in the Mountain Timezone.

What could cause this?  The OS shows the correct time.  

Looking at the environment for the "oracle" user account discloses the reason:

cat .bash_profile

# .bash_profile

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

. ~/.bashrc

fi

# User specific environment and startup programs

export PATH

export TZ=US/Mountain

At this point, we realize the database is only reflecting a local environment variable setting, so we modify the TZ attribute to "US/Eastern" with the desired result.

$ vi .bash_profile

[oracle@exa ~]$ date

Tue Jul 25 07:57:53 MDT 2023

[oracle@exa ~]$ source .bash_profile

Enter DB Name (TOOLUAT/ACMUAT/FMUAT): TOOLUAT

[oracle@exa1 ~]$ date

Tue Jul 25 09:58:03 EDT 2023

[oracle@exa ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jul 25 09:58:10 2023

Version 19.18.0.0.0

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

Connected to:

Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production

Version 19.18.0.0.0

SQL> select systimestamp from dual;

SYSTIMESTAMP

---------------------------------------------------------------------------

25-JUL-23 09.58.16.964305 AM -04:00

So as we can see in this vivid example, Oracle Support can only help so much when you have a process change.  They do not know about localized information for your environment.  They can only provide general guidelines.  In our case, we had to look at several configurable attributes to complete the effort.  

So far, testing with the timezone change is going well.  We may still run into an issue or two with the data and backups.  Time will tell, but at least, going forward, all of the systems will be running the same.  

This is a good argument for running all systems in the UTC (greenwich mean time) zone.  However, that would have required massive application changes and procedural adjustments for this particular system.