Shutdown while upgrading to 18c

My colleague and friend Gleb Otochkin has already blogged about installing 18c – Installing Oracle 18c using command line – but I thought that I would share my experience.

My plan was to upgrade my 12.2 pluggable database to 18c (18c for on-premise was made available 23rd July – When will Oracle Database 18c be available on-prem?).

Some things were a bit different with the install process and I did have some difficulty running the upgrade. Here’s what I did.

The first thing that has changed is the installation process. Instead of placing the software into a holding location and then running runInstaller which goes to copy that software to your Oracle Home location, now the install is an image copy and the process includes unzipping the downloaded file directly into the Oracle Home location. Once there, you run the runInstaller process with a slimmed-down version of the response file. All this does essentially is register the Oracle Home into the Inventory and then perform a relink to recompile the Oracle Software.

Installation

mkdir -p /u01/app/oracle/product/18.0.0.0/db1
cd /u01/app/oracle/product/18.0.0.0/db1
unzip /tmp/LINUX.X64_180000_db_home.zip

The contents of my response file (loracle.rsp)

oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v18.0.0
oracle.install.option=INSTALL_DB_SWONLY
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/u01/app/oraInventory
ORACLE_HOME=/u01/app/oracle/product/18.0.0.0/db1
ORACLE_BASE=/u01/app/oracle
oracle.install.db.InstallEdition=EE
oracle.install.db.OSDBA_GROUP=dba
oracle.install.db.OSOPER_GROUP=dba
oracle.install.db.OSBACKUPDBA_GROUP=dba
oracle.install.db.OSDGDBA_GROUP=dba
oracle.install.db.OSKMDBA_GROUP=dba
oracle.install.db.OSRACDBA_GROUP=dba

Installation command

$ORACLE_HOME/runInstaller -silent -responseFile $ORACLE_HOME/install/response/loracle.rsp

Once the software was successfully installed, I chose to follow this upgrade path – unplug my 12.2 pluggable database and plug it into a new 18c container and upgrade.

First, I needed to create a new container database. I chose to do this manually.

Create new container database
Copy initialization file to new home updating contents to reflect new database name and controlfile names.

Copy password file to new home.

Edit /etc/oratab to include an entry for the new database name and Oracle Home.

Start database to NOMOUNT mode.

Issue Create statement.

CREATE DATABASE LUKE18
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 1024
DATAFILE '/u01/oradata/LUKE18/system01.dbf' SIZE 700M REUSE
  AUTOEXTEND ON NEXT  10240K MAXSIZE UNLIMITED
  EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/u01/oradata/LUKE18/sysaux01.dbf' SIZE 550M REUSE
  AUTOEXTEND ON NEXT  10240K MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/oradata/LUKE18/temp01.dbf' SIZE 20M REUSE
  AUTOEXTEND ON NEXT  640K MAXSIZE UNLIMITED
UNDO TABLESPACE "UNDOTBS1" DATAFILE  '/u01/oradata/LUKE18/undotbs01.dbf' SIZE 200M REUSE
  AUTOEXTEND ON NEXT  5120K MAXSIZE UNLIMITED
DEFAULT TABLESPACE USERS
  DATAFILE '/u01/oradata/LUKE18/users01.dbf'
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
LOGFILE GROUP 1 ('/u01/oradata/LUKE18/redo01.log') SIZE 250M,
GROUP 2 ('/u01/oradata/LUKE18/redo02.log') SIZE 250M,
GROUP 3 ('/u01/oradata/LUKE18/redo03.log') SIZE 250M
USER SYS IDENTIFIED BY "&&sysPassword" USER SYSTEM IDENTIFIED BY "&&systemPassword"
enable pluggable database
FILE_NAME_CONVERT = ('/u01/oradata/LUKE18/','/u01/oradata/LUKE18/pdbseed/')
SYSTEM DATAFILES SIZE 125M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
SYSAUX DATAFILES SIZE 100M;

Then run the catcdb script to run catalog and catproc

@?/rdbms/admin/catcdb

Now I need to unplug my 12.2 database.

Prior to unplugging run the preupgrade script.

$ORACLE_HOME_12.2/jdk/bin/java -jar $ORACLE_HOME_18/rdbms/admin/preupgrade.jar dir /tmp -c LUKEPDB1

Run the fixup scripts.

CONNECT / AS SYSDBA
ALTER SESSION SET CONTAINER=lukepdb1;
@/tmp/preupgrade_fixups_LUKEPDB1.sql

Close the pluggable database.

ALTER PLUGGABLE DATABASE LUKEPDB1 CLOSE;

Unplug the database.

ALTER PLUGGABLE DATABASE LUKEPDB1 UNPLUG INTO '/home/oracle/lukepdb1.xml';

Drop the pluggable database.

DROP PLUGGABLE DATABASE LUKEPDB1 KEEP DATAFILES;

At this stage, I really want to make sure that I can plug it back into my 12.2 environment as well as plug it into the 18c environment. So I copied the datafiles to a new location and amended the xml file to refer to the new locations.

Then I tried to plug it into the new 18c but received an error ORA-65346

An oerr shows the following

oerr ORA 65346
65346, 00000, "The PDB version is lower and components (%s) are missing in CDB."
// *Cause:  An attempt was made to plug in a pluggable database (PDB) that
//          has a lower version and has more components than the multitenant
//          container database (CDB) or the application root.
// *Action: Install the missing components in CDB or the application root
//          before plugging in the PDB.
//

I had omitted to install the Oracle Text module into the 18c container database as I had previously installed it into my pluggable database.

So I installed Oracle Text into the new container –

@?/ctx/admin/catctx CTXSYS SYSAUX TEMP NOLOCK

This allowed me to successfully plug in the database

CREATE PLUGGABLE DATABASE LUKEPDB1 USING '/home/oracle/lukepdb1.xml' NOCOPY;

I am now at the stage where I can upgrade my pluggable database to 18c. I can either call the parallel upgrade perl or use the dbupgrade script which calls this perl script anyway.

Upgrade pluggable database

CONNECT / as sysdba
ALTER SESSION SET CONTAINER=LUKEPDB1;
ALTER PLUGGABLE DATABASE OPEN UPGRADE;

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catctl.pl -d $ORACLE_HOME/rdbms/admin -c 'LUKEPDB1' -l $ORACLE_BASE catupgrd.sql

But the process finished very quickly and with an error and consistently shut down the entire container even though I specifically stated to ignore CDB$ROOT container by only upgrading my pluggable database. This was a bit of a surprise as this process was expected to keep the container open just in case other services were required in this container (of course this did not matter in my test environment).

Checking the upgrade logs, I found the following entry in the catupgrd0.log

SQL>
BEGIN_RUNNING
--------------------------------------------------------------------------------
==== @/u01/app/oracle/product/18.0.0.0/db1/rdbms/admin/catshutdown.sql Container
:CDB$ROOT Id:1 18-08-15 02:24:35 Proc:0 ====

...

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

After some debugging, I found that there was a procedure within the catctl.pl called – catctlReadLogFiles

This procedure checked log files produced to see if there were any errors, and if there were any errors, then shut down everything. One of the errors checked was – SP2-0640: Not connected

There were plenty of these messages in my log files and they appeared to be harmless because immediately following these errors was the output – Connected. This indicated that a successful connect command was issued.

So I amended the catctl.pl perl script to remove the check for SP2-0640 errors. This allowed my upgrade to complete successfully.

To do this, I commented out line 7507 as follows

my @TAGS = ("ORA-03114",      # Not Connected
            #"SP2-0640:",      # Not Connected
            "ORA-03113",      # End of Communication
            "ORA-00600",      # Internal Error
            "ORA-01012",      # Not Log in
            "ORA-01034",      # Not Available
            "ORA-01092",      # Instance Teminated
            "ORA-01119",      # Error Creating Database file
            "SP2-1519:",      # Can't write to registry$error
            "ORA-07445");     # Exception Encountered

I then needed to run the datapatch (I probably should have run this before) to bootstrap the latest patches (July 2018 – which were in-built already in the download).

$ORACLE_HOME/OPatch/datapatch -verbose

Then I needed to do the final steps that I would ordinarily do:

1. Copy and update new listener.ora file and stop 12.2 listener and starting 18c listener.
2. Copy and update new tnsnames.ora file.
3. Put DB into archive log mode.
4. Add block change tracking.
5. Upgrade RMAN catalog.
6. Take Level 0 backup.
7. Update /etc/oratab with correct settings.
8. Update bash profile for default 18c database.
9. Update Oracle backup schedule to include 18c database.

Also, I plugged the database files I had previously copied back into my 12.2 database – just so I have both :)