Upgrading to Oracle Database 18c (Non-CDB) - 11g to 18c
This article provides an overview of upgrading an existing non-CDB database to Oracle 18c. Upgrades can be very complicated, so you must always read the upgrade manual, and test thoroughly before considering an upgrade of a production environment.
This article also includes the conversion of the upgraded database to a pluggable database. You don't have to do this step if you want a non-CDB instance.
Remember, 18c is essentially 12.2.0.2, so the possible upgrade options will be similar to those described in upgrading to Oracle Database 12c.
- Assumptions
- Prerequisities
- Install 18c Software
- Run preupgrade.jar
- Perform Pre-Upgrade Actions
- Upgrade the Database
- Perform Post-Upgrade Actions
- Create New Container Database (CDB)
- Convert Non-CDB to PDB
- Final Steps
Assumptions
This article is focused on upgrading a non-CDB database. If your starting point is a database using the multitenant architecture, you should be reading one of the following articles instead.
This article assumes your source database is of a version supported for direct upgrade to 18c.
11.2.0.3, 11.2.0.4, 12.1.0.1, 12.1.0.2, 12.2.0.1
In this example we are doing an upgrade from 11.2 to 18c. The process is very similar for all supported versions, but the pre-upgrade and post-upgrade fixup actions may vary a little.
It's important to have backups of everything before you start! Some of these steps are destructive, and if something goes wrong you have no alternative but to restore from backups and start again.
Remember, this article is not a replacement for reading the upgrade documentation. Each upgrade has the potential to be different, depending on what options are installed.
Prerequisities
Make sure you have all the OS prerequisites in place by running the 18c 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-18c yum update -y
Install 18c Software
You can read about the installation process in more detail here, but for this example I'll keep it brief. The following commands will perform a silent installation of the 18c software.
export ORACLE_HOME=$ORACLE_BASE/product/18.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_180000_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=true
Run the root scripts when prompted.
As a root user, execute the following script(s):
1. /u01/app/oracle/product/18.0.0/dbhome_1/root.sh
At 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 from MOS 884522.1. If you don't have MOS access you can miss out the next step.
Put the latest "preupgrade.jar" into the 18c Oracle home.
export ORACLE_HOME=$ORACLE_BASE/product/18.0.0/dbhome_1 cd $ORACLE_HOME/rdbms/admin unzip -o /u01/software/preupgrade_181_cbuild_5_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/18.0.0/dbhome_1/jdk/bin/java -jar $ORACLE_BASE/product/18.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/18.0.0/dbhome_1/jdk/bin/java -jar $ORACLE_BASE/product/18.0.0/dbhome_1/rdbms/admin/preupgrade.jar TERMINAL TEXT
Report generated by Oracle Database Pre-Upgrade Information Tool Version
18.0.0.0.0 Build: 5 on 2018-11-24T10:21:34
Upgrade-To version: 18.0.0.0.0
=======================================
Status of the database prior to upgrade
=======================================
Database Name: DB11G
Container Name: Not Applicable in Pre-12.1 database
Container ID: Not Applicable in Pre-12.1 database
Version: 11.2.0.4.0
Compatible: 11.2.0.4.0
Blocksize: 8192
Platform: Linux x86 64-bit
Timezone File: 14
Database log mode: NOARCHIVELOG
Readonly: FALSE
Edition: EE
Oracle Component Upgrade Action Current Status
---------------- -------------- --------------
Oracle Server [to be upgraded] VALID
JServer JAVA Virtual Machine [to be upgraded] VALID
Oracle XDK for Java [to be upgraded] VALID
Oracle Workspace Manager [to be upgraded] VALID
OLAP Analytic Workspace [to be upgraded] VALID
Oracle Enterprise Manager Repository [to be upgraded] VALID
Oracle Text [to be upgraded] VALID
Oracle XML Database [to be upgraded] VALID
Oracle Java Packages [to be upgraded] VALID
Oracle Multimedia [to be upgraded] VALID
Oracle Spatial [to be upgraded] VALID
Expression Filter [to be upgraded] VALID
Rule Manager [to be upgraded] VALID
Oracle OLAP API [to be upgraded] VALID
==============
BEFORE UPGRADE
==============
REQUIRED ACTIONS
================
None
RECOMMENDED ACTIONS
===================
1. Update NUMERIC INITIALIZATION PARAMETERS to meet estimated minimums.
This action may be done now or when starting the database in upgrade mode
using the 18.0.0.0.0 ORACLE HOME.
Parameter Currently 18.0.0.0.0 minimum
--------- --------- ------------------
processes 150 300
The database upgrade process requires certain initialization parameters
to meet minimum values. The Oracle upgrade process itself has minimum
values which may be higher and are marked with an asterisk. After
upgrading, those asterisked parameter values may be reset if needed.
2. Remove the EM repository.
- Copy the $ORACLE_HOME/rdbms/admin/emremove.sql script from the target
18.0.0.0.0 ORACLE_HOME into the source 11.2.0.4.0 ORACLE_HOME.
Step 1: If database control is configured, stop EM Database Control,
using the following command
$> emctl stop dbconsole
Step 2: Connect to the database using the SYS account AS SYSDBA
SET ECHO ON;
SET SERVEROUTPUT ON;
@emremove.sql
Without the set echo and serveroutput commands, you will not be able to
follow the progress of the script.
The database has an Enterprise Manager Database Control repository.
Starting with Oracle Database 12c, the local Enterprise Manager Database
Control does not exist anymore. The repository will be removed from your
database during the upgrade. This step can be manually performed before
the upgrade to reduce downtime.
3. Remove OLAP Catalog by running the 11.2.0.4.0 SQL script
$ORACLE_HOME/olap/admin/catnoamd.sql script.
The OLAP Catalog component, AMD, exists in the database.
Starting with Oracle Database 12c, the OLAP Catalog (OLAP AMD) is
desupported and will be automatically marked as OPTION OFF during the
database upgrade if present. Oracle recommends removing OLAP Catalog
(OLAP AMD) before database upgrade. This step can be manually performed
before the upgrade to reduce downtime.
4. Upgrade Oracle Application Express (APEX) manually before the database
upgrade.
The database contains APEX version 3.2.1.00.12. Upgrade APEX to at least
version 5.1.3.00.05.
Starting with Oracle Database Release 18, APEX is not upgraded
automatically as part of the database upgrade. Refer to My Oracle Support
Note 1088970.1 for information about APEX installation and upgrades.
5. (AUTOFIXUP) Gather stale data dictionary statistics prior to database
upgrade in off-peak time using:
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
Dictionary statistics do not exist or are stale (not up-to-date).
Dictionary statistics help the Oracle optimizer find efficient SQL
execution plans and are essential for proper upgrade timing. Oracle
recommends gathering dictionary statistics in the last 24 hours before
database upgrade.
For information on managing optimizer statistics, refer to the 11.2.0.4
Oracle Database Performance Tuning Guide.
6. Directly grant ADMINISTER DATABASE TRIGGER privilege to the owner of the
trigger or drop and re-create the trigger with a user that was granted
directly with such. You can list those triggers using "SELECT OWNER,
TRIGGER_NAME FROM DBA_TRIGGERS WHERE TRIM(BASE_OBJECT_TYPE)='DATABASE'
AND OWNER NOT IN (SELECT GRANTEE FROM DBA_SYS_PRIVS WHERE
PRIVILEGE='ADMINISTER DATABASE TRIGGER')"
There is one or more database triggers whose owner does not have the
right privilege on the database.
The creation of database triggers must be done by users granted with
ADMINISTER DATABASE TRIGGER privilege. Privilege must have been granted
directly.
7. (AUTOFIXUP) Gather statistics on fixed objects prior the upgrade.
None of the fixed object tables have had stats collected.
Gathering statistics on fixed objects, if none have been gathered yet, is
recommended prior to upgrading.
For information on managing optimizer statistics, refer to the 11.2.0.4
Oracle Database Performance Tuning Guide.
INFORMATION ONLY
================
8. To help you keep track of your tablespace allocations, the following
AUTOEXTEND tablespaces are expected to successfully EXTEND during the
upgrade process.
Min Size
Tablespace Size For Upgrade
---------- ---------- -----------
SYSAUX 510 MB 720 MB
SYSTEM 740 MB 1183 MB
TEMP 29 MB 150 MB
UNDOTBS1 70 MB 446 MB
Minimum tablespace sizes for upgrade are estimates.
ORACLE GENERATED FIXUP SCRIPT
=============================
All of the issues in database DB11G
which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following
SQL>@/u01/app/oracle/cfgtoollogs/db11g/preupgrade/preupgrade_fixups.sql
=============
AFTER UPGRADE
=============
REQUIRED ACTIONS
================
None
RECOMMENDED ACTIONS
===================
9. Upgrade the database time zone file using the DBMS_DST package.
The database is using time zone file version 14 and the target 18.0.0.0.0
release ships with time zone file version 31.
Oracle recommends upgrading to the desired (latest) version of the time
zone file. For more information, refer to "Upgrading the Time Zone File
and Timestamp with Time Zone Data" in the 18.0.0.0.0 Oracle Database
Globalization Support Guide.
10. (AUTOFIXUP) Gather dictionary statistics after the upgrade using the
command:
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
Oracle recommends gathering dictionary statistics after upgrade.
Dictionary statistics provide essential information to the Oracle
optimizer to help it find efficient SQL execution plans. After a database
upgrade, statistics need to be re-gathered as there can now be tables
that have significantly changed during the upgrade or new tables that do
not have statistics gathered yet.
11. Gather statistics on fixed objects after the upgrade and when there is a
representative workload on the system using the command:
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
This recommendation is given for all preupgrade runs.
Fixed object statistics provide essential information to the Oracle
optimizer to help it find efficient SQL execution plans. Those
statistics are specific to the Oracle Database release that generates
them, and can be stale upon database upgrade.
For information on managing optimizer statistics, refer to the 11.2.0.4
Oracle Database Performance Tuning Guide.
INFORMATION ONLY
================
12. Check the Oracle documentation for the identified components for their
specific upgrade procedure.
The database upgrade script will not upgrade the following Oracle
components: OLAP Catalog,OWB
The Oracle database upgrade script upgrades most, but not all Oracle
Database components that may be installed. Some components that are not
upgraded may have their own upgrade scripts, or they may be deprecated or
obsolete.
ORACLE GENERATED FIXUP SCRIPT
=============================
All of the issues in database DB11G
which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following
SQL>@/u01/app/oracle/cfgtoollogs/db11g/preupgrade/postupgrade_fixups.sql
==================
PREUPGRADE SUMMARY
==================
/u01/app/oracle/cfgtoollogs/db11g/preupgrade/preupgrade.log
/u01/app/oracle/cfgtoollogs/db11g/preupgrade/preupgrade_fixups.sql
/u01/app/oracle/cfgtoollogs/db11g/preupgrade/postupgrade_fixups.sql
Execute fixup scripts as indicated below:
Before upgrade log into the database and execute the preupgrade fixups
@/u01/app/oracle/cfgtoollogs/db11g/preupgrade/preupgrade_fixups.sql
After the upgrade:
Log into the database and execute the postupgrade fixups
@/u01/app/oracle/cfgtoollogs/db11g/preupgrade/postupgrade_fixups.sql
Preupgrade complete: 2018-11-24T10:21:36
$
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/18.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/18.0.0/dbhome_1/network/admin # Add this to $ORACLE_BASE/product/18.0.0/dbhome_1/network/admin/sqlnet.ora # Need to correct password versions and remove this. cat >> $ORACLE_BASE/product/18.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/18.0.0/dbhome_1/dbs/ cp $ORACLE_HOME/dbs/spfiledb11g.ora $ORACLE_BASE/product/18.0.0/dbhome_1/dbs/
Switch to the 18c listener.
lsnrctl stop
export ORACLE_HOME=$ORACLE_BASE/product/18.0.0/dbhome_1
export PATH=${ORACLE_HOME}/bin:$PATH
lsnrctl start
Start the database using the 18c 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
Argument list for [/u01/app/oracle/product/18.0.0/dbhome_1/rdbms/admin/catctl.pl]
Run in c = 0
Do not run in C = 0
Input Directory d = 0
Echo OFF e = 1
Simulate E = 0
Forced cleanup F = 0
Log Id i = 0
Child Process I = 0
Log Dir l = 0
Priority List Name L = 0
Upgrade Mode active M = 0
SQL Process Count n = 0
SQL PDB Process Count N = 0
Open Mode Normal o = 0
Start Phase p = 0
End Phase P = 0
Reverse Order r = 0
AutoUpgrade Resume R = 0
Script s = 0
Serial Run S = 0
RO User Tablespaces T = 0
Display Phases y = 0
Debug catcon.pm z = 0
Debug catctl.pl Z = 0
catctl.pl VERSION: [18.0.0.0.0]
STATUS: [Production]
BUILD: [RDBMS_18.3.0.0.0DBRU_LINUX.X64_180627]
/u01/app/oracle/product/18.0.0/dbhome_1/rdbms/admin/orahome = [/u01/app/oracle/product/18.0.0/dbhome_1]
/u01/app/oracle/product/18.0.0/dbhome_1/bin/orabasehome = [/u01/app/oracle/product/18.0.0/dbhome_1]
catctlGetOrabase = [/u01/app/oracle/product/18.0.0/dbhome_1]
Analyzing file /u01/app/oracle/product/18.0.0/dbhome_1/rdbms/admin/catupgrd.sql
Log file directory = [/tmp/cfgtoollogs/upgrade20181124104231]
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/tmp/cfgtoollogs/upgrade20181124104231/catupgrd_catcon_4970.lst]
catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20181124104231/catupgrd*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20181124104231/catupgrd_*.lst] files for spool files, if any
Number of Cpus = 2
Database Name = db11g
DataBase Version = 11.2.0.4.0
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/app/oracle/product/18.0.0/dbhome_1/cfgtoollogs/db11g/upgrade20181124104232/catupgrd_catcon_4970.lst]
catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/18.0.0/dbhome_1/cfgtoollogs/db11g/upgrade20181124104232/catupgrd*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/18.0.0/dbhome_1/cfgtoollogs/db11g/upgrade20181124104232/catupgrd_*.lst] files for spool files, if any
Log file directory = [/u01/app/oracle/product/18.0.0/dbhome_1/cfgtoollogs/db11g/upgrade20181124104232]
Parallel SQL Process Count = 4
Components in [db11g]
Installed [APEX APS CATALOG CATJAVA CATPROC CONTEXT JAVAVM ORDIM OWM SDO XDB XML XOQ]
Not Installed [DV EM MGW ODM OLS RAC WK]
------------------------------------------------------
Phases [0-108] Start Time:[2018_11_24 10:42:33]
------------------------------------------------------
*********** Executing Change Scripts ***********
Serial Phase #:0 [db11g] Files:1 Time: 106s
*************** Catalog Core SQL ***************
Serial Phase #:1 [db11g] Files:5 Time: 63s
Restart Phase #:2 [db11g] Files:1 Time: 0s
*********** Catalog Tables and Views ***********
Parallel Phase #:3 [db11g] Files:19 Time: 18s
Restart Phase #:4 [db11g] Files:1 Time: 0s
************* Catalog Final Scripts ************
Serial Phase #:5 [db11g] Files:7 Time: 21s
***************** Catproc Start ****************
Serial Phase #:6 [db11g] Files:1 Time: 15s
***************** Catproc Types ****************
Serial Phase #:7 [db11g] Files:2 Time: 12s
Restart Phase #:8 [db11g] Files:1 Time: 0s
**************** Catproc Tables ****************
Parallel Phase #:9 [db11g] Files:66 Time: 29s
Restart Phase #:10 [db11g] Files:1 Time: 0s
************* Catproc Package Specs ************
Serial Phase #:11 [db11g] Files:1 Time: 76s
Restart Phase #:12 [db11g] Files:1 Time: 0s
************** Catproc Procedures **************
Parallel Phase #:13 [db11g] Files:94 Time: 10s
Restart Phase #:14 [db11g] Files:1 Time: 0s
Parallel Phase #:15 [db11g] Files:117 Time: 23s
Restart Phase #:16 [db11g] Files:1 Time: 0s
Serial Phase #:17 [db11g] Files:17 Time: 3s
Restart Phase #:18 [db11g] Files:1 Time: 0s
***************** Catproc Views ****************
Parallel Phase #:19 [db11g] Files:32 Time: 22s
Restart Phase #:20 [db11g] Files:1 Time: 0s
Serial Phase #:21 [db11g] Files:3 Time: 10s
Restart Phase #:22 [db11g] Files:1 Time: 1s
Parallel Phase #:23 [db11g] Files:24 Time: 136s
Restart Phase #:24 [db11g] Files:1 Time: 0s
Parallel Phase #:25 [db11g] Files:12 Time: 80s
Restart Phase #:26 [db11g] Files:1 Time: 0s
Serial Phase #:27 [db11g] Files:1 Time: 0s
Serial Phase #:28 [db11g] Files:3 Time: 4s
Serial Phase #:29 [db11g] Files:1 Time: 0s
Restart Phase #:30 [db11g] Files:1 Time: 0s
*************** Catproc CDB Views **************
Serial Phase #:31 [db11g] Files:1 Time: 0s
Restart Phase #:32 [db11g] Files:1 Time: 1s
Serial Phase #:34 [db11g] Files:1 Time: 0s
***************** Catproc PLBs *****************
Serial Phase #:35 [db11g] Files:288 Time: 24s
Serial Phase #:36 [db11g] Files:1 Time: 0s
Restart Phase #:37 [db11g] Files:1 Time: 0s
Serial Phase #:38 [db11g] Files:2 Time: 7s
Restart Phase #:39 [db11g] Files:1 Time: 0s
*************** Catproc DataPump ***************
Serial Phase #:40 [db11g] Files:3 Time: 52s
Restart Phase #:41 [db11g] Files:1 Time: 0s
****************** Catproc SQL *****************
Parallel Phase #:42 [db11g] Files:13 Time: 95s
Restart Phase #:43 [db11g] Files:1 Time: 1s
Parallel Phase #:44 [db11g] Files:11 Time: 9s
Restart Phase #:45 [db11g] Files:1 Time: 0s
Parallel Phase #:46 [db11g] Files:3 Time: 2s
Restart Phase #:47 [db11g] Files:1 Time: 0s
************* Final Catproc scripts ************
Serial Phase #:48 [db11g] Files:1 Time: 6s
Restart Phase #:49 [db11g] Files:1 Time: 0s
************** Final RDBMS scripts *************
Serial Phase #:50 [db11g] Files:1 Time: 26s
************ Upgrade Component Start ***********
Serial Phase #:51 [db11g] Files:1 Time: 0s
Restart Phase #:52 [db11g] Files:1 Time: 0s
********** Upgrading Java and non-Java *********
Serial Phase #:53 [db11g] Files:2 Time: 507s
***************** Upgrading XDB ****************
Restart Phase #:54 [db11g] Files:1 Time: 0s
Serial Phase #:56 [db11g] Files:3 Time: 28s
Serial Phase #:57 [db11g] Files:3 Time: 6s
Parallel Phase #:58 [db11g] Files:9 Time: 4s
Parallel Phase #:59 [db11g] Files:25 Time: 4s
Serial Phase #:60 [db11g] Files:4 Time: 6s
Serial Phase #:61 [db11g] Files:1 Time: 0s
Serial Phase #:62 [db11g] Files:31 Time: 5s
Serial Phase #:63 [db11g] Files:1 Time: 0s
Parallel Phase #:64 [db11g] Files:6 Time: 3s
Serial Phase #:65 [db11g] Files:2 Time: 25s
Serial Phase #:66 [db11g] Files:3 Time: 86s
**************** Upgrading ORDIM ***************
Restart Phase #:67 [db11g] Files:1 Time: 1s
Serial Phase #:69 [db11g] Files:1 Time: 0s
Parallel Phase #:70 [db11g] Files:2 Time: 47s
Serial Phase #:71 [db11g] Files:1 Time: 55s
Restart Phase #:72 [db11g] Files:1 Time: 0s
Parallel Phase #:73 [db11g] Files:2 Time: 12s
Serial Phase #:74 [db11g] Files:2 Time: 1s
***************** Upgrading SDO ****************
Restart Phase #:75 [db11g] Files:1 Time: 1s
Serial Phase #:77 [db11g] Files:1 Time: 47s
Serial Phase #:78 [db11g] Files:1 Time: 1s
Restart Phase #:79 [db11g] Files:1 Time: 0s
Serial Phase #:80 [db11g] Files:1 Time: 21s
Restart Phase #:81 [db11g] Files:1 Time: 0s
Parallel Phase #:82 [db11g] Files:3 Time: 67s
Restart Phase #:83 [db11g] Files:1 Time: 0s
Serial Phase #:84 [db11g] Files:1 Time: 5s
Restart Phase #:85 [db11g] Files:1 Time: 0s
Serial Phase #:86 [db11g] Files:1 Time: 8s
Restart Phase #:87 [db11g] Files:1 Time: 1s
Parallel Phase #:88 [db11g] Files:4 Time: 62s
Restart Phase #:89 [db11g] Files:1 Time: 1s
Serial Phase #:90 [db11g] Files:1 Time: 0s
Restart Phase #:91 [db11g] Files:1 Time: 0s
Serial Phase #:92 [db11g] Files:2 Time: 7s
Restart Phase #:93 [db11g] Files:1 Time: 0s
Serial Phase #:94 [db11g] Files:1 Time: 1s
Restart Phase #:95 [db11g] Files:1 Time: 0s
******* Upgrading ODM, WK, EXF, RUL, XOQ *******
Serial Phase #:96 [db11g] Files:1 Time: 29s
Restart Phase #:97 [db11g] Files:1 Time: 0s
*********** Final Component scripts ***********
Serial Phase #:98 [db11g] Files:1 Time: 2s
************* Final Upgrade scripts ************
Serial Phase #:99 [db11g] Files:1 Time: 231s
******************* Migration ******************
Serial Phase #:100 [db11g] Files:1 Time: 63s
*** End PDB Application Upgrade Pre-Shutdown ***
Serial Phase #:101 [db11g] Files:1 Time: 1s
Serial Phase #:102 [db11g] Files:1 Time: 0s
Serial Phase #:103 [db11g] Files:1 Time: 123s
***************** Post Upgrade *****************
Serial Phase #:104 [db11g] Files:1 Time: 26s
**************** Summary report ****************
Serial Phase #:105 [db11g] Files:1 Time: 1s
*** End PDB Application Upgrade Post-Shutdown **
Serial Phase #:106 [db11g] Files:1 Time: 0s
Serial Phase #:107 [db11g] Files:1 Time: 0s
Serial Phase #:108 [db11g] Files:1 Time: 31s
------------------------------------------------------
Phases [0-108] End Time:[2018_11_24 11:22:04]
------------------------------------------------------
Grand Total Time: 2374s
LOG FILES: (/u01/app/oracle/product/18.0.0/dbhome_1/cfgtoollogs/db11g/upgrade20181122104232/catupgrd*.log)
Upgrade Summary Report Located in:
/u01/app/oracle/product/18.0.0/dbhome_1/cfgtoollogs/db11g/upgrade20181122104232/upg_summary.log
Grand Total Upgrade Time: [0d:0h:39m:34s]
$
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.
# 9) 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
# 10) AUTOFIXUP
# 11) 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;
EOF
Assuming 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 \
-automaticMemoryManagement false \
-totalMemory 2000 \
-storageType FS \
-datafileDestination "/u02/oradata/" \
-redoLogFileSize 500 \
-emConfiguration NONE \
-ignorePreReqs
For 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;
EOF
Create 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;
EOF
Final 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/18.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.
If you are using APEX or ORDS, you probably want to validate them (validate APEX, validate ORDS).
No hay comentarios:
Publicar un comentario