29 July 2019

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?

19 July 2019

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

17 May 2019

.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
 

15 October 2018

create a manual backup for an Autonomous Transaction Processing database

When creating a manual backup for Autonomous Transaction Database (on Oracle Cloud Infrastructure) you will encounter a number of requirements. Once setup, it will be pretty straightforward. I will walk you through all steps. 

Note that I'm not an Oracle employee, and things will change in this world. Always consult the documentation to verify the steps you need to take.

Create een Autonomous Transaction Processing Database
  • Login to you Oracle Cloud Infrastructure
  • Go to Autonomous Transaction Processing
  • Click the button "[ Create Autonomous Transaction Processing Database ]"
  • Supply a name, password (1) and select your licensing model
  • Creating the database will take a few minutes, once it shows "Available" you can go on to the next step.

Download Client Credentials
  • Click the name of the database
  • Click on "[ Service Console ]"
  • Click on "[ Administration ]"
  • Click on "Download a Connection Wallet" and safe it in Downloads folder 
  • Note down the password (2) you create here

Connect with SQL Developer
  • Start up SQL Developer
  • Create a new connection
  • Username is Admin and password (1) 
  • Connection type is Cloud PDB
  • Configuration File is the wallet zip you downloaded (don't unzip)
  • Keystore password is password (2)
  • Service can be a service as contained in the wallet, e.g. the dbname_low

Create a Service User
  • In Oracle Cloud Infrastructure, go to Menu>Identity>Users
  • Create a user, sa_backup  (or some name for backups)
  • Select that user when created, and click Groups
  • Add the user to group Administrators
  • Click Authority Tokens, create token
  • Note down the token password (3)

Settings for defaults
  • Create credential in SQL Developer
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(credential_name => 'DEF_CRED_NAME',
username => 'sa_backup',password => 'password as set above in (3)');
END;
/

alter database property set default_credential = 'ADMIN.DEF_CRED_NAME';
  • Set the default bucket, change according to your environment:
ALTER DATABASE PROPERTY SET default_bucket='https://swiftobjectstorage.eu-frankfurt-1.oraclecloud.com/v1/tenancy_name';


Create a bucket
  • Create a bucket with the correct name:
  • Menu>Object Storage>Object Storage>"[ Create Bucket ]"
  • Give the bucket the exact name backup_databasename, for example backup_mydb01 
Now go back to the ATP database in the Oracle Cloud Infrastructure, and create a manual backup with the button, this will run for a few minutes.