Import in Autonomous Transaction Processing Database

In this article, I would like to show the easy steps to import a Oracle datapump exportfile into an (existing) ATP database. Note, you are always responsible yourself to check the documentation and make your own decisions. We all know that documentation can be confusing, showing too many details and referring you back and forth to other locations. I hope these simple steps will provice some help.

Create a credential

Connect with SQL*Developer to the ATP database, and run (set parameters how you like)

BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'MY_CRED_NAME',
    username => 'john.doe@mycompany.com',
    password => 'password'
  );
END;
/

Upload the datapump file to a bucket

Go to Object Storage>Object Storage.

Create a bucket for holding the dumpfile.

Once the bucket is created, upload your dumpfile into the bucket.

In the menu to the right (three dots) on this dumpfile, you can Create a Pre-authenticated request.

This will give you a “Pre-Authenticated Request URL” that you need to copy/save.

Run the import

Now with a local Oracle Client (I used Oracle 18c client on windows 10), you can import.

The client_home needs the files from the wallet-zip (unzipped) in network/admin folder.

You can now check connectivity with the Oracle Client:

sqlplus admin/<admin_pw>@db01_high

The import can be run as:

impdp admin/<admin_pw>@db01_high directory=data_pump_dir credential=my_cred_name dumpfile=<pre-authenticated_request URL>

You may add extra impdp parameters as you wish.

dbua not showing Recovery Options

I noted a strange situation recently. I was able to recreate this today in a test environment. I will briefly explain.

I use the dbua (Database Upgrade Assistant) to upgrade an database (18.5.0.0.0) to 19.4.0.0.0
When the source database orcl01 is running in OPEN mode on the old home I get in the dbua the screens:
1. Select Database
2. Prerequisite Checks
3. Select Upgrade Options
4. Select Recovery Options
5. Configure Management
6. Summary
7. Progress
8. Results.

When the source database orcl01 is started with STARTUP UPGRADE in the new home, and then I start the dbua, I get the screens:
1. Select Database
2. Select Upgrade Options
3. Summary
4. Progess
5. Results.

So there are essential screens missing, like the Recovery Options. The backup or restore point is NOT made, but the upgrade is performed without issues.

When asked on MOS, I got the response that the first setup is the correct one. I never really thought about this, strange difference right?

Some notes on upgrade - with physical standby in place

These are just quick & dirty steps for upgrade, with a physical standby in place. Use them with caution, at your own risk. Also, consult the Oracle documentation and Mike Dietrich's input for upgrade.

Move listener

cp sqlnet.ora, tnsnames.ora en listener.ora  to newhome/network/admin
stop listener on the old home
set environment variable $ORACLE_HOME for the new home
start the listener on the new home

Stop broker

disable broker config (dgmgrl > disable configuration)
set  dg_broker_start=false  on both primary and standby

Get ready

shutdown immediate primary
shutdown immediate standby
change  /etc/oratab on both primary and standby host
cp the spfile and orapw to the newhome/dbs
startup mount the standby (from the new home)
startup upgrade the primary (from the new home)
[or startup the primary on the old home, which should work as well when using dbua]

change the listener.ora on both nodes for the new oracle_home
reload the listeners

Upgrade

run dbua on the primary
(during the upgrade, you can run on the standby RMAN>recover database to make some progress. You can do this a few times)

After upgrade

cp the dr*.dat broker files to the newhome/dbs folder
set (2x) dg_broker_start=true
enable de broker config
check if logswitches get applied

.patch_storage growing in Oracle Home

Starting with Oracle 18, the Oracle Home is growing more than expected. It appears that the .patch_storage folder is taking up quite some storage. There are notes on this on MOS and one of them is mentioning that you can also check if there are subfolders, which are of patches that are not installed (anymore). (Doc ID 550522.1)

You will see under section 3) of that note a description.

The regular opatch util cleanup does not clean all that you want it seems.

I have written the following script, which you may use at your OWN RISK.

#!/bin/sh
# cleanup the patch storage for Opatch
# not all things are cleaned, so you may want to delete old patch directories that are backed up but not installed

# parameter: $ORACLE_HOME

if [ $# -ne 1 ];then
  echo "Usage: clean_opatch_storage.sh "
  exit
else
  OH=$1
fi

# where to put the rm commands
rmscript=/tmp/rmdirs.sh
echo > $rmscript

# show size before
du -s $OH/.patch_storage

# list contents
ls -l $OH/.patch_storage

# save inventory
$OH/OPatch/opatch lsinventory > /tmp/lsinv.log
export LOG=/tmp/lsinv.log

# list folders that are of patches:
cd $OH/.patch_storage

# make a list of the patch numbers in this directory
mylist=$(find .  -mindepth 1 -maxdepth 1 -type d -name '*[0-9]*_*' -printf '%P\n' | awk '{split($0,a,"_"); print a[1]}' )

# check if those patches are in inventory log, if not remove the directory
for var1 in $mylist
do
  FOUND=$(cat $LOG | grep -w $var1  | wc -l)
  if [ $FOUND -eq 0 ]; then
    echo $var1  Not found in inventory, so this directory should go
    mydel=$(find .  -mindepth 1 -maxdepth 1 -type d -name $var1'_*' -printf '%P\n' )
    echo rm -rf $mydel >> $rmscript
  else
    echo $var1  Found in inventory, so this directory should be kept
  fi;
done

# cat the script
echo This will be done:
cat $rmscript

read -r -p "Are you sure? [y/N] " response
case "$response" in
    [yY][eE][sS]|[yY])
        chmod 740 $rmscript
        $rmscript
        ;;
    *)
        echo "Cancelled deletion!"
        ;;
esac