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.
mkdir -p /u01/app/oracle/product/22.214.171.124/db1 cd /u01/app/oracle/product/126.96.36.199/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/188.8.131.52/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
$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.
/etc/oratab to include an entry for the new database name and Oracle Home.
Start database to
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
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
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/184.108.40.206/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 –
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).
Then I needed to do the final steps that I would ordinarily do:
- Copy and update new listener.ora file and stop 12.2 listener and starting 18c listener.
- Copy and update new tnsnames.ora file.
- Put DB into archive log mode.
- Add block change tracking.
- Upgrade RMAN catalog.
- Take Level 0 backup.
- Update /etc/oratab with correct settings.
- Update bash profile for default 18c database.
- 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 :)
Interested in working with Luke? Schedule a tech call.