Prerequisities
Make sure you have all the OS prerequisites in place by running the 19c preinstall package. On Oracle Linux you can do this by installing the preinstall package. It probably makes sense to update the remaining packages also.
yum install -y oracle-database-preinstall-19c yum update -y
Install 19c Software
The following commands will perform a silent installation of the 19c software.
export ORACLE_HOME=$ORACLE_BASE/product/19.0.0/dbhome_1
export SOFTWARE_DIR=/u01/software
export ORA_INVENTORY=/u01/app/oraInventory
mkdir -p ${ORACLE_HOME}
cd $ORACLE_HOME
/bin/unzip -oq ${SOFTWARE_DIR}/LINUX.X64_193000_db_home.zip
./runInstaller -ignorePrereq -waitforcompletion -silent \
-responseFile ${ORACLE_HOME}/install/response/db_install.rsp \
oracle.install.option=INSTALL_DB_SWONLY \
ORACLE_HOSTNAME=${ORACLE_HOSTNAME} \
UNIX_GROUP_NAME=oinstall \
INVENTORY_LOCATION=${ORA_INVENTORY} \
SELECTED_LANGUAGES=en,en_GB \
ORACLE_HOME=${ORACLE_HOME} \
ORACLE_BASE=${ORACLE_BASE} \
oracle.install.db.InstallEdition=EE \
oracle.install.db.OSDBA_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 \
SECURITY_UPDATES_VIA_MYORACLESUPPORT=false \
DECLINE_SECURITY_UPDATES=trueRun the root scripts when prompted.
As a root user, execute the following script(s):
1. /u01/app/oracle/product/19.0.0/dbhome_1/root.shAt this point you should also patch the new Oracle home, but in this case we will forgo that step to keep things simple.
Run preupgrade.jar
Download the latest “preupgrade.jar” file. If you don’t have MOS access you can miss out the next step.
At the time of writing, the latest preupgrade.jar is the one shipped with 19c, so there is no need to download a newer one. I’s worth checking to see if this situation has changed when you do your upgrade.
Put the latest “preupgrade.jar” into the 19c Oracle home.
export ORACLE_HOME=$ORACLE_BASE/product/19.0.0/dbhome_1 cd $ORACLE_HOME/rdbms/admin unzip -o /u01/software/preupgrade_19_cbuild_??_lf.zip
Make sure you are using the original Oracle home and run the “preupgrade.jar”.
export ORACLE_SID=db11g export ORAENV_ASK=NO . oraenv export ORAENV_ASK=YES export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4/db_1 $ORACLE_BASE/product/19.0.0/dbhome_1/jdk/bin/java -jar $ORACLE_BASE/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar TERMINAL TEXT
Here is the output from an example run against an 11.2 database.
$ $ORACLE_BASE/product/19.0.0/dbhome_1/jdk/bin/java -jar $ORACLE_BASE/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar TERMINAL TEXT
Perform Pre-Upgrade Actions
The output from the “preupgrade.jar” lists a number of pre-upgrade recommendations. Some must be manually applied. Others are incorporated into the “preupgrade_fixups.sql” script. In the following example we run all the manual operations as well as the “preupgrade_fixups.sql” script.
# 1) Increase the processes parameter. sqlplus / as sysdba <<EOF alter system set processes=300 scope=spfile; shutdown immediate; startup; exit; EOF # 2) Remove EM DB Console config. cp $ORACLE_BASE/product/19.0.0/dbhome_1/rdbms/admin/emremove.sql $ORACLE_HOME/rdbms/admin/emremove.sql sqlplus / as sysdba <<EOF SET ECHO ON; SET SERVEROUTPUT ON; @$ORACLE_HOME/rdbms/admin/emremove.sql exit; EOF # 3) Remove OLAP catalog. sqlplus / as sysdba <<EOF @$ORACLE_HOME/olap/admin/catnoamd.sql exit; EOF # 4) Ignoring the APEX upgrade. This is a junk test instance. Normally APEX would be at latest version. # 5) Included in AUTOFIXUP. # 6) This will be unnecessary after removal of EM repository above. # 7) Included in AUTOFIXUP. # Recompile invalid objects. sqlplus / as sysdba <<EOF @$ORACLE_HOME/rdbms/admin/utlrp.sql SET SERVEROUTPUT ON; EXECUTE DBMS_PREUP.INVALID_OBJECTS; exit; EOF # Run preupgrade-fixups.sql script. sqlplus / as sysdba <<EOF @/u01/app/oracle/cfgtoollogs/db11g/preupgrade/preupgrade_fixups.sql exit; EOF
Upgrade the Database
With the pre-upgrade actions complete we can start the upgrade. Shutdown the source database.
sqlplus / as sysdba <<EOF shutdown immediate; exit; EOF
Copy the config files from the old to the new Oracle home. You should check the contents of the “listener.ora” file to see if there are any references to the Oracle home path. If there are, amend them.
cp $ORACLE_HOME/network/admin/*.ora $ORACLE_BASE/product/19.0.0/dbhome_1/network/admin # Add this to $ORACLE_BASE/product/19.0.0/dbhome_1/network/admin/sqlnet.ora # Need to correct password versions and remove this. cat >> $ORACLE_BASE/product/19.0.0/dbhome_1/network/admin/sqlnet.ora <<EOF # This should be temporary while you deal with old passwords. SQLNET.ALLOWED_LOGON_VERSION_SERVER=11 EOF cp $ORACLE_HOME/dbs/orapwdb11g $ORACLE_BASE/product/19.0.0/dbhome_1/dbs/ cp $ORACLE_HOME/dbs/spfiledb11g.ora $ORACLE_BASE/product/19.0.0/dbhome_1/dbs/
Switch to the 19c listener.
lsnrctl stop
export ORACLE_HOME=$ORACLE_BASE/product/19.0.0/dbhome_1
export PATH=${ORACLE_HOME}/bin:$PATH
lsnrctl startStart the database using the 19c Oracle home, ready for the upgrade.
sqlplus / as sysdba <<EOF startup upgrade; exit; EOF
You can run the upgrade using either of the following commands. The second is actually just a shorthand for the former.
# Regular upgrade command. cd $ORACLE_HOME/rdbms/admin $ORACLE_HOME/perl/bin/perl catctl.pl catupgrd.sql # Shorthand command. $ORACLE_HOME/bin/dbupgrade
Here is the output from an example run against an 11.2 database.
$ $ORACLE_HOME/bin/dbupgrade
The database is shutdown at the end of the upgrade process, so you need to start it before moving on to the post-upgrade actions.
sqlplus / as sysdba <<EOF startup exit; EOF
Perform Post-Upgrade Actions
The output from the “preupgrade.jar” lists a number of post-upgrade recommendations. Some must be manually applied. Others are incorporated into the “postupgrade_fixups.sql” script. In the following example we run all the manual operations as well as the “postupgrade_fixups.sql” script.
# 11) Time zone file.
sqlplus / as sysdba <<EOF
-- Check current settings.
SELECT * FROM v$timezone_file;
SHUTDOWN IMMEDIATE;
STARTUP UPGRADE;
-- Begin upgrade to the latest version.
SET SERVEROUTPUT ON
DECLARE
l_tz_version PLS_INTEGER;
BEGIN
l_tz_version := DBMS_DST.get_latest_timezone_version;
DBMS_OUTPUT.put_line('l_tz_version=' || l_tz_version);
DBMS_DST.begin_upgrade(l_tz_version);
END;
/
SHUTDOWN IMMEDIATE;
STARTUP;
-- Do the upgrade.
SET SERVEROUTPUT ON
DECLARE
l_failures PLS_INTEGER;
BEGIN
DBMS_DST.upgrade_database(l_failures);
DBMS_OUTPUT.put_line('DBMS_DST.upgrade_database : l_failures=' || l_failures);
DBMS_DST.end_upgrade(l_failures);
DBMS_OUTPUT.put_line('DBMS_DST.end_upgrade : l_failures=' || l_failures);
END;
/
-- Check new settings.
SELECT * FROM v$timezone_file;
COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A20
SELECT property_name, property_value
FROM database_properties
WHERE property_name LIKE 'DST_%'
ORDER BY property_name;
exit;
EOF
# 12) Ignored
# 13) AUTOFIXUP
# 14) Gather fixed object stats.
sqlplus / as sysdba <<EOF
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
exit;
EOF
# AUTOFIXUP
sqlplus / as sysdba <<EOF
@/u01/app/oracle/cfgtoollogs/db11g/preupgrade/postupgrade_fixups.sql
exit;
EOFAssuming you didn’t hit any problems along the way, your database is upgraded and ready to go now. The following steps are only necessary if you want to convert the non-CDB instance to a pluggable database (PDB).
Create New Container Database (CDB)
There are a number of considerations when creating a container database, but for this example we will keep it simple. The command below creates a new container database (CDB) with no user-defined pluggable databases (PDBs).
# Create new instance.
export SYS_PASSWORD=SysPassword1
export ORACLE_SID=cdb1
dbca -silent -createDatabase \
-templateName General_Purpose.dbc \
-gdbname ${ORACLE_SID} -sid ${ORACLE_SID} -responseFile NO_VALUE \
-characterSet AL32UTF8 \
-sysPassword ${SYS_PASSWORD} \
-systemPassword ${SYS_PASSWORD} \
-createAsContainerDatabase true \
-numberOfPDBs 0 \
-databaseType MULTIPURPOSE \
-memoryMgmtType auto_sga \
-totalMemory 2000 \
-storageType FS \
-datafileDestination "/u02/oradata/" \
-redoLogFileSize 500 \
-emConfiguration NONE \
-ignorePreReqsFor a proper conversion we would have to make sure the CDB had all necessary configuration in place, including options and initialisation parameters. We will ignore the rest of the instance setup to keep it simple.
Convert Non-CDB to PDB
Describe the non-CDB instance and turn it off.
export ORACLE_SID=db11g
sqlplus / as sysdba <<EOF
SHUTDOWN IMMEDIATE;
STARTUP OPEN READ ONLY;
BEGIN
DBMS_PDB.DESCRIBE(
pdb_descr_file => '/u01/software/db11g.xml');
END;
/
SHUTDOWN IMMEDIATE;
exit;
EOFCreate a PDB using description of the non-CDB, moving the datafiles into the new location, remembering to run the “noncdb_to_pdb.sql” to clean up before opening the pluggable database.
export ORACLE_SID=cdb1
mkdir -p /u02/oradata/CDB1/pdb1/
sqlplus / as sysdba <<EOF
CREATE PLUGGABLE DATABASE pdb1 USING '/u01/software/db11g.xml'
MOVE
FILE_NAME_CONVERT = ('/u02/oradata/db11g/', '/u02/oradata/CDB1/pdb1/');
ALTER SESSION SET CONTAINER=pdb1;
@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
ALTER PLUGGABLE DATABASE OPEN;
ALTER PLUGGABLE DATABASE SAVE STATE;
exit;
EOFFinal Steps
We need to clean up the remains of the non-CDB instance. We can use the DBCA to do this.
dbca -silent -deleteDatabase -sourceDB db11g -sysDBAUserName sys -sysDBAPassword ${SYS_PASSWORD}We can delete any remaining files.
rm /u01/app/oracle/product/11.2.0.4/db_1/dbs/*db11g* rm /u01/app/oracle/product/19.0.0/dbhome_1/dbs/*db11g* rm -Rf $ORACLE_BASE/admin/db11g rm -Rf /u01/app/oracle/diag/rdbms/db11g rm -Rf /u02/oradata/db11g
Edit the “/etc/oratab” file as required.