Thursday, July 4, 2024

Installing Oracle 23ai Database and APEX 24ai

Everyone is jumping on the AI bandwagon, including Oracle who have just released Oracle Database 23ai and APEX 24ai.

In this post I'll go over the steps to install Oracle Database 23ai and APEX 24ai but won't touch on the AI features, I'll save that for another post.

I'll be installing into a VM; I use Proxmox, a really good alternative to VMWare ESXi. If you have an old box around I'd recommend setting it up, it's really useful as a lab.

I used OEL 8 from here: https://yum.oracle.com/oracle-linux-isos.html

I tried OEL9 but had issues, so I wouldn't recommend it.

Oracle 23ai Free from here: https://www.oracle.com/database/free/get-started/

Oracle APEX 24ai: https://www.oracle.com/tools/downloads/apex-downloads/

You will also need ORDS. I tried the latest ORDS but couldn't get it to work, so used a previous version: https://download.oracle.com/otn_software/java/ords/ords-23.4.0.346.1619.zip

So, install OEL8 to a VM, then login to it and download the database, apex and ords.

Navigate to the download directory, make sure you are root and install the database

dnf install -y oracle-database-free*



This will install all the pre-requisites as well. Next, run the configure step.

/etc/init.d/oracle-free-23ai configure

It will prompt for a password, give it one. It will then run through the steps to install and create the database.


For some reason this process doesn't create the oracle user properly, so as root, create a home directory for oracle and set a password

mkdir /home/oracle

chown oracle:oinstall /home/oracle

passwd oracle

Once installed, login as the oracle user and use the default pdb. It creates one called FREEPDB1 so you can use that.

. oraenv

FREE

sqlplus / as sysdba

show pdbs


You can either use SYSAUX for the APEX files, or create 2 tablespaces - APEX and FILES.

For simplicity here I'll use SYSAUX.

I like to create a directory in /tmp and work from that so I know where everything is

as the oracle user - 

mkdir /tmp/apex
mkdir /tmp/apex/ords

Then as root:

cd /home/andy/Downloads
cp apex* /tmp/apex
cp ords* /tmp/apex/ords
chown oracle:oinstall /tmp/apex/*
chown oracle:oinstall /tmp/apex/ords/*


Log back in as oracle and cd to the /tmp/apex directory and unzip the apex file.

It will create an APEX directory. Navigate to that and set the environment and sqlplus.

cd apex
. oraenv
FREE
export ORACLE_PDB_SID=FREEPDB1
sqlplus / as sysdba
show con_name


Run the command to install apex

@apexins sysaux sysaux temp /i/

It will take around 7-10 minutes to run

Once that's done, run the command to set the password.

@apxchpwd

Let the username and email default, enter a password which needs to be complex (like Welcome#123).


Next, run apex_rest_config - enter the Welcome#123 password again for the APEX_LISTENER and the APEX_REST_PUBLIC_USER accounts.

You also need to unlock and set the password for these accounts:


You don't need to change the router one.

alter user apex_listener identified by Welcome#123 account unlock;
alter user apex_rest_public_user identified by Welcome#123 account unlock;
alter user apex_public_user identified by Welcome#123 account unlock;
alter user apex_240100 identified by Welcome#123 account unlock;


Exit from SQLPlus and cd to the ords directory and extract the ords zip file.

You need to make sure the JAVA executable is in your path:

export PATH=$ORACLE_HOME/jdk/bin:$PATH
export JAVA_HOME=$ORACLE_HOME/jdk
which java
java -version


Copy the images folder from the apex folder to the ords folder.


Run the command to install ords

java -jar ords.war install

Ignore the warning and follow the prompts. At the first prompt, let it default to 2.


At the second prompt, let it default to 1


Let it default to localhost, then 1521.

At the service name, you need to enter FREEPDB1


Enter sys at the next prompt, and the sys password


It will connect to the database, let the rest default by hitting Enter.


Let the next prompts default to 1 (Standalone, http, 8080)


This next prompt is important - you need to enter the location of the copied images directory. If you followed this guide, it will be

/tmp/apex/ords/images


The ORDS install will start.

Ignore the warnings about a production environment, you should see this


Don't exit from this terminal session or ORDS will stop and you won't be able to connect to APEX.

Open a browser and go to 

http://localhost:8080/ords/apex_admin

You should see this


Login with admin / Welcome#123 (or whatever you used for the ADMIN password).


In the next APEX post I'll look at creating a sample app and using the AI features.































Posted by at

Wednesday, July 3, 2024

Upgrading from Oracle V11 to Oracle V19 using different methods

 We're seeing a lot of clients finally deciding to upgrade to V19 after years of nagging from us. I've done several now, so I've put together a guide to go through the different methods available.

I recommend setting up a VM, install V11 (you can still get the V11 software from edelivery.oracle.com) and then go through the steps. I would also recommend backing up the VM once you have installed V11 so that you can restore it for when you need to start again unless you want to practice re-installing V11 a lot.

This document assumes -

V11 is 11.2.0.4 

V19 zip file has been uploaded to the host 

cd  

cd Downloads 

ls 

 

export ORACLE_BASE=/u01/app/oracle 

export ORACLE_HOME=$ORACLE_BASE/product/19.0.0/dbhome_1 

export ORA_INVENTORY=/u01/app/oraInventory 

mkdir -p ${ORACLE_HOME} 

cd $ORACLE_HOME 

pwd 

/u01/app/oracle/product/19.0.0/dbhome_1 

unzip -oq ~/Downloads/LINUX.X64_193000_db_home.zip 

Takes a couple of minutes 

Once complete, run this – change EE to SE if it’s Standard Edition to be installed 

./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 

 

If you get a message saying the ORACLE_HOME is already registered, run this: 

 

$ORACLE_HOME/oui/bin/runInstaller -silent -detachHome ORACLE_HOME="/u01/app/oracle/product/19.0.0/dbhome_1" 

 

Or just ./runInstaller and use the GUI. 

Graphical user interface, text, application, email Description automatically generated 

You may see “successfully setup software with warnings” – check the log 

 

Once done 

As a root user, execute the following script(s): 

        /u01/app/oracle/product/19.0.0/dbhome_1/root.sh 

Optionally patch to the latest PSU now, or after the database has been upgraded 

Using the Auto upgrade method 

This uses the auto upgrade script as explained here 

https://docs.oracle.com/en/database/oracle/oracle-database/19/upgrd/about-oracle-database-autoupgrade.html#GUID-3FCFB2A6-4617-4783-828A-41BD635FC88C 

Download the latest version of the tool - AutoUpgrade Tool (Doc ID 2485457.1) and copy it to $ORACLE_HOME/rdbms/admin of 19C 

[oracle@orapri ]$ cd /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin 

Create the sample config file with this command 

[oracle@orapri admin]$ java -jar /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/autoupgrade.jar -create_sample_file config 

 

Edit the config file to reflect the upgrade info – the SID, ORACLE_HOMEs, temp dir and hostname 

# 

# sample config file 

# 

# build version 19.9.1 

# build date    2020/07/10 11:20:49 

# 

# 

# Global configurations 

# 

# This directory will include the following 

#   (1) AutoUpgrade's global directory 

#   (2) Any logs, not directly tied to a job 

#   (3) Config files 

#   (4) progress.json and status.json 

global.autoupg_log_dir=/tmp/upgrade_log 

 

# 

# Database number 1  

# 

upg1.log_dir=/tmp/upgrade_log            

upg1.sid=ORCL                                               

upg1.source_home=/u01/app/oracle/product/11.2.0/dbhome_1   

upg1.target_home=/u01/app/oracle/product/19/0.0/dbhome_1   

upg1.start_time=NOW                                        

upg1.upgrade_node=orapri.localdomain                     # run ‘hostname’ command 

#upg1.run_utlrp=[yes|no]                                  # Optional. Whether or not to run utlrp after upgrade 

#upg1.timezone_upg=[yes|no]                               # Optional. Whether or not to run the timezone upgrade 

#upg1.target_version=[12.2|18|19|21]                      # Oracle version of the target ORACLE_HOME.  Only required when the target Oracle database version is 12.2 

create the log directory 

[oracle@orapri admin]$ mkdir /tmp/upgrade_log 

run the script in analyze mode (database needs to be open) 

[oracle@orapri admin]$ java -jar autoupgrade.jar -config sample_config.cfg -mode analyze 

It will start a process and open a console – you can let it run or enter some commands – lsj and tasks 

AutoUpgrade 23.4.230921 launched with default internal options 

Processing config file ... 

+--------------------------------+ 

| Starting AutoUpgrade execution | 

+--------------------------------+ 

1 Non-CDB(s) will be analyzed 

Type 'help' to list console commands 

upg> lsj 

+----+-------+---------+---------+-------+----------+-------+----------------------------+ 

|Job#|DB_NAME|    STAGE|OPERATION| STATUS|START_TIME|UPDATED|                     MESSAGE| 

+----+-------+---------+---------+-------+----------+-------+----------------------------+ 

| 100|   ORCL|PRECHECKS|EXECUTING|RUNNING|  10:33:51| 0s ago|Loading database information| 

+----+-------+---------+---------+-------+----------+-------+----------------------------+ 

Total jobs 1 

 

upg> Job 100 completed 

------------------- Final Summary -------------------- 

Number of databases            [ 1 ] 

 

Jobs finished                  [1] 

Jobs failed                    [0] 

 

Please check the summary report at: 

/u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.html 

/u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.log 

 

Once complete, go to the log directory and open the html file with a browser (ctrl-o) 

/u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.html 

 

A screenshot of a computer Description automatically generated 

 

A screenshot of a computer Description automatically generated 

Click on the ‘Checks Report’ for details 

A screenshot of a computer Description automatically generated 

A screenshot of a computer Description automatically generated 

If ‘FixUp Available’ is ‘YES’, the tool will perform it automatically. 

Any ERRORS (red) that have ‘FixUp Available’ as ‘NO’ need to be done manually before the upgrade can start. 

Any with ‘WARNING’ or ‘INFO’ and ‘NO’ can be left, they are optional fixes. 

The config file can be edited to prevent any of the fixes from being applied if you want to do them manually. 

If you do any manual fixes, run the analyze again. 

If you want the job to do the fixes, run the tool in ‘fixups’ mode. 

Note that this makes changes to the database, so make sure you have a backup. 

This might take a while – check the status with ‘lsj’ 

[oracle@orapri admin]$ java -jar autoupgrade.jar -config sample_config.cfg -mode fixups 

AutoUpgrade 23.4.230921 launched with default internal options 

Processing config file ... 

+--------------------------------+ 

| Starting AutoUpgrade execution | 

+--------------------------------+ 

1 Non-CDB(s) will be processed 

Type 'help' to list console commands 

upg> lsj 

+----+-------+---------+---------+-------+----------+-------+-------+ 

|Job#|DB_NAME|    STAGE|OPERATION| STATUS|START_TIME|UPDATED|MESSAGE| 

+----+-------+---------+---------+-------+----------+-------+-------+ 

| 101|   ORCL|PREFIXUPS|EXECUTING|RUNNING|  10:43:51| 0s ago|       | 

+----+-------+---------+---------+-------+----------+-------+-------+ 

Total jobs 1 

 

upg> tasks 

+---+--------------+-------------+ 

| ID|          NAME|         Job#| 

+---+--------------+-------------+ 

|  1|          main|      WAITING| 

| 42|    event_loop|TIMED_WAITING| 

| 43|       console|     RUNNABLE| 

| 44|  queue_reader|      WAITING| 

| 46|         cmd-0|      WAITING| 

| 47| job_manager-0|      WAITING| 

|221|orcl-puifxl0-0|      WAITING| 

|222|orcl-puifxl0-1|      WAITING| 

|223|orcl-puifxl0-2|      WAITING| 

|224|orcl-puifxl0-3|      WAITING| 

|231|     exec_loop|      WAITING| 

|240|    sql-7E0BFF|     RUNNABLE| 

+---+--------------+-------------+  

 

It will take a few minutes to run, but should return this 

upg> Job 101 completed 

------------------- Final Summary -------------------- 

Number of databases            [ 1 ] 

 

Jobs finished                  [1] 

Jobs failed                    [0] 

 

Please check the summary report at: 

/u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.html 

/u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.log 

 

Open the status.html file in a browser, and select the ‘fixup report’ 

A screenshot of a computer Description automatically generated 

A screenshot of a computer Description automatically generated 

If there are errors, fix them manually 

If all OK, run the command with the ‘deploy’ mode – this will perform all the operations and upgrade the database, so make sure apps are aware and are shutdown first. 

You can track the progress with ‘lsj’, or by opening this html file in a browser (ctrl-o) 

/u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/state.html 

 

The page auto refreshes 

This will take a while – it took 3.5 hours in a test VM, 

[oracle@orapri admin]$ java -jar autoupgrade.jar -config sample_config.cfg -mode deploy 

 

AutoUpgrade 23.4.230921 launched with default internal options 

Processing config file ... 

+--------------------------------+ 

| Starting AutoUpgrade execution | 

+--------------------------------+ 

1 Non-CDB(s) will be processed 

Type 'help' to list console commands 

upg> lsj 

+----+-------+---------+---------+-------+----------+-------+----------------------------+ 

|Job#|DB_NAME|    STAGE|OPERATION| STATUS|START_TIME|UPDATED|                     MESSAGE| 

+----+-------+---------+---------+-------+----------+-------+----------------------------+ 

| 102|   ORCL|PRECHECKS|EXECUTING|RUNNING|  10:58:54| 1s ago|Loading database information| 

+----+-------+---------+---------+-------+----------+-------+----------------------------+ 

Total jobs 1 

 

upg> tasks 

+---+-------------+-------------+ 

| ID|         NAME|         Job#| 

+---+-------------+-------------+ 

|  1|         main|      WAITING| 

| 43|   event_loop|TIMED_WAITING| 

| 44|      console|     RUNNABLE| 

| 45| queue_reader|      WAITING| 

| 47|        cmd-0|      WAITING| 

| 48|job_manager-0|      WAITING| 

|118|  orcl-puic-0|      WAITING| 

|119|  orcl-puic-1|      WAITING| 

|120|  orcl-puic-2|      WAITING| 

|121|  orcl-puic-3|      WAITING| 

|122|  orcl-puic-4|      WAITING| 

|123|  orcl-puic-5|      WAITING| 

|124|  orcl-puic-6|      WAITING| 

|125|  orcl-puic-7|      WAITING| 

|143|   sql-3C0AFD|     RUNNABLE| 

|148|   sql-B5AC35|     RUNNABLE| 

|151|   sql-41ADBC|     RUNNABLE| 

|152|   sql-BB8D11|     RUNNABLE| 

|153|   sql-B7E20B|     RUNNABLE| 

|154|   sql-AF4981|     RUNNABLE| 

|155|   sql-B9CCEE|     RUNNABLE| 

|156|   sql-EF6528|     RUNNABLE| 

+---+-------------+-------------+ 

 

You can also run ‘status’ 

upg> status 

 

Config 

 

        User configuration file    [/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/sample_config.cfg] 

        General logs location      [/u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto] 

        Mode                       [DEPLOY] 

Jobs Summary 

 

        Total databases in configuration file [1] 

        Total Non-CDB being processed         [1] 

        Total Containers being processed      [0] 

 

        Jobs finished successfully            [0] 

        Jobs finished/stopped                 [0] 

        Jobs in progress                      [1] 

 

Progress 

        +---+---------------------------------------------------------+ 

        |Job|                                                 Progress| 

        +---+---------------------------------------------------------+ 

        |102|[||||||||||||||||||||||||||                        ] 50 %| 

        +---+---------------------------------------------------------+ 

‘help’ shows all available commands: 

upg> help 

exit                            // To close and exit 

help                            // Displays help 

lsj [<option>] [-a <number>]    // list jobs by status up to n elements. 

        -f                Filter by finished jobs. 

        -r                Filter by running jobs. 

        -e                Filter by jobs with errors. 

        -p                Filter by jobs being prepared. 

        -n <number>       Display up to n jobs. 

        -a <number>       Repeats the command (in <number> seconds). 

lsr                             // Displays the restoration queue 

lsa                             // Displays the stop queue 

tasks                           // Displays the tasks running 

clear                           // Clears the terminal 

resume -job <number> [-ignore_errors=<ORA-#####,ORA-#####>] // Restarts a job with option to ignore errors 

status [<option>] [-a <number>] // Summary of current execution 

        -config                     Show Config Information 

        -job <number>               Summary of a given job 

        -job <number> -c <dbname>   Show details of container 

        -a [<number>]               Repeats the command (in <number> seconds). 

restore -job <number>           // Restores the database to its state prior to the upgrade 

restore all_failed              // Restores all failed jobs to their previous states prior to the upgrade 

logs                            // Displays all the log locations 

stop -job <number>              // Stops the specified job 

h[ist]                          // Displays the command line history 

/[<number>]                     // Executes the command specified from the history. The default is the last command 

meta                            // Displays Internal latch count 

hwinfo                          // Displays additional information 

fxlist -job <number> [<option>]                // FixUps summary 

        -c <dbname>                                  Container specific FixUps 

        -c <dbname> alter <check> run <yes|no|skip>  Update Run Configuration 

 

It should move on to the fixups stage: 

A red and white rectangular object Description automatically generated with medium confidence 

 

After a while you should see this: 

upg> Job 102 completed 

------------------- Final Summary -------------------- 

Number of databases            [ 1 ] 

 

Jobs finished                  [1] 

Jobs failed                    [0] 

Jobs restored                  [0] 

Jobs pending                   [0] 

 

---- Drop GRP at your convenience once you consider it is no longer needed ---- 

Drop GRP from ORCL: drop restore point AUTOUPGRADE_9212_ORCL112040 

 

 

Please check the summary report at: 

/u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.html 

/u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.log 

 

The upgrade is complete – you can check the alert log and drop the GRP 

 

Make sure you reset your environment 

[oracle@orapri ~]$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1 

[oracle@orapri ~]$ export PATH=$ORACLE_HOME/bin:$PATH  

[oracle@orapri ~]$ sqlplus / as sysdba 

 

[oracle@orapri admin]$ sqlplus / as sysdba 

 

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Dec 6 12:22:47 2023 

Version 19.3.0.0.0 

 

Copyright (c) 1982, 2019, Oracle.  All rights reserved. 

 

 

Connected to: 

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production 

Version 19.3.0.0.0 

 

SQL> select * from v$version; 

 

BANNER 

-------------------------------------------------------------------------------- 

BANNER_FULL 

-------------------------------------------------------------------------------- 

BANNER_LEGACY 

-------------------------------------------------------------------------------- 

    CON_ID 

---------- 

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production 

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production 

Version 19.3.0.0.0 

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production 

         0 

 

BANNER 

-------------------------------------------------------------------------------- 

BANNER_FULL 

-------------------------------------------------------------------------------- 

BANNER_LEGACY 

-------------------------------------------------------------------------------- 

    CON_ID 

---------- 

 

 

SQL> drop restore point AUTOUPGRADE_9212_ORCL112040; 

 

Restore point dropped. 

 

Check what is needed to complete the upgrade – open this file in a browser 

/tmp/upgrade_log/ORCL/n/postchecks/orcl_postupgrade.html 

Where n is the job number 

  

 

In a real environment, perform these steps to complete the upgrade.  

Stop the listener because it’s running from the V11 home, and restart it from the V19 home 

[oracle@orapri ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 

[oracle@orapri ~]$ export PATH=$ORACLE_HOME/bin:$PATH 

[oracle@orapri ~]$ lsnrctl stat 

 

LSNRCTL for Linux: Version 11.2.0.4 - Production on 31-JUL-2020 13:55:09 

 

Copyright (c) 1991, 2014, Oracle.  All rights reserved. 

 

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orapri.localdomain)(PORT=1521))) 

STATUS of the LISTENER 

------------------------ 

Alias                     LISTENER 

Version                   TNSLSNR for Linux: Version 11.2.0.4 – Production 

 

[oracle@orapri ~]$ lsnrctl stop 

 

LSNRCTL for Linux: Version 11.2.0.4 - Production on 31-JUL-2020 13:55:15 

 

Copyright (c) 1991, 2014, Oracle.  All rights reserved. 

 

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orapri.localdomain)(PORT=1521))) 

The command completed successfully 

 

[oracle@orapri ~]$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1 

[oracle@orapri ~]$ export PATH=$ORACLE_HOME/bin:$PATH 

[oracle@orapri ~]$ lsnrctl start 

 

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 31-JUL-2020 13:55:28 

 

Copyright (c) 1991, 2019, Oracle.  All rights reserved. 

 

Starting /u01/app/oracle/product/19.0.0/dbhome_1/bin/tnslsnr: please wait... 

 

TNSLSNR for Linux: Version 19.0.0.0.0 - Production 

System parameter file is /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora 

Log messages written to /u01/app/oracle/diag/tnslsnr/orapri/listener/alert/log.xml 

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))) 

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) 

 

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orapri.localdomain)(PORT=1521))) 

STATUS of the LISTENER 

------------------------ 

Alias                     LISTENER 

Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production 

 

You should now run the stats gathering steps 

execute dbms_stats.gather_fixed_object_stats 

Upgrade should be done. 

Upgrade method with dbua 

Once the software is installed, run the pre-upgrade step 

Set the ORACLE_HOME to the V11 home but run the preupgrade.jar file from the V19 home: 

. oraenv 

ORCL 

echo $ORACLE_HOME 

/u01/app/oracle/product/11.2.0/dbhome_1 

Run this on one line 

/u01/app/oracle/product/19.0.0/dbhome_1/jdk/bin/java -jar /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar TERMINAL TEXT 

It will produce a report – see the appendix for the full report. Apply the fixes that don’t have (AUTOFIXUP) next to them: 

Recovery file dest size 

Processes (needs outage) 

EM remove 

OLAP catalogue remove 

e.g 

  1.  Set DB_RECOVERY_FILE_DEST_SIZE initialization parameter to at least 4981 

      MB.  Check alert log during the upgrade to ensure there is remaining free 

      space available in the recovery area. 

       

      DB_RECOVERY_FILE_DEST_SIZE is set at 4182 MB.  There is currently 4151 MB 

      of free space remaining, which may not be adequate for the upgrade. 

 

I had to set this to 8GB – setting it to 5GB failed. 

Text Description automatically generated 

Processes 

Text Description automatically generated 

EM remove 

 

 

OLAP catalogue remove 

@$ORACLE_HOME/olap/admin/catnoamd.sql 

 

I didn’t bother with APEX. 

Run the pre-upgrade sql: 

@/u01/app/oracle/cfgtoollogs/ORCL/preupgrade/preupgrade_fixups.sql 

 

 

 

If any fail, run them manually. 

Perform the upgrade 

Shut the database 

Copy the config files to the new home 

 

Copy the listener.ora, tnsnames.ora and sqlnet.ora files to the new HOME, and update any references to the ORACLE_HOME 

If the V11 passwords are not complex, then this is needed in the sqlnet.ora file until they can be reset  

SQLNET.ALLOWED_LOGON_VERSION_SERVER=11 

Stop the V11 listener and restart from the V19 home 

lsnrctl stop 

export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1 

export PATH=$ORACLE_HOME/bin:$PATH 

lsnrctl start 

Either run the dbua gui, or run the dbupgrade shell script, or run the perl and sql scripts – one of these: 

Either  

dbua 

or 

$ORACLE_HOME/bin/dbupgrade 

or 

cd $ORACLE_HOME/rdbms/admin 

$ORACLE_HOME/perl/bin/perl catctl.pl catupgrd.sql 

If running the scripts, start the database from the V19 home using ‘startup upgrade’ – this is not needed for the dbua 

 

If running the dbua you need to stop the database and run the GUI from the V19 home.  

 

dbua 

Graphical user interface, text, application, email Description automatically generated 

 

The dbua can do some of the pre and post upgrade steps. 

Saw this, it failed on the last step, so clicked ‘Ignore’ 

 

The log screen showed 21 hours upgrade time but this included waiting for the input to skip the last step – so I suspect the time for that if monitoring would only be a few minutes. 

 

If running the scripts you may need to do this after the upgrade – it might be worth doing it anyway - 

@/u01/app/oracle/cfgtoollogs/ORCL/preupgrade/postupgrade_fixups.sql 

 

 

You should now run the stats gathering steps 

execute dbms_stats.gather_fixed_object_stats 

Upgrade should be done. 

Upgrade using script 

Once the software is installed, run the pre-upgrade step 

Set the ORACLE_HOME to the V11 home but run the preupgrade.jar file from the V19 home: 

. oraenv 

ORCL 

echo $ORACLE_HOME 

/u01/app/oracle/product/11.2.0/dbhome_1 

Run this on one line 

/u01/app/oracle/product/19.0.0/dbhome_1/jdk/bin/java -jar /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar TERMINAL TEXT 

It will produce a report – see the appendix for the full report. Apply the fixes that don’t have (AUTOFIXUP) next to them: 

Recovery file dest size 

Processes (needs outage) 

EM remove 

OLAP catalogue remove 

e.g 

  1.  Set DB_RECOVERY_FILE_DEST_SIZE initialization parameter to at least 4981 

      MB.  Check alert log during the upgrade to ensure there is remaining free 

      space available in the recovery area. 

       

      DB_RECOVERY_FILE_DEST_SIZE is set at 4182 MB.  There is currently 4151 MB 

      of free space remaining, which may not be adequate for the upgrade. 

 

I had to set this to 8GB – setting it to 5GB failed. 

Text Description automatically generated 

Processes 

Text Description automatically generated 

EM remove 

 

 

OLAP catalogue remove 

@$ORACLE_HOME/olap/admin/catnoamd.sql 

 

I didn’t bother with APEX. 

Once the manual steps have been done, run the pre-upgrade sql: 

@/u01/app/oracle/cfgtoollogs/ORCL/preupgrade/preupgrade_fixups.sql 

 

If any fail, run them manually. 

Run utlrp to compile any invalid objects 

@$ORACLE_HOME/rdbms/admin/utlrp 

Perform the upgrade 

Shut the database 

Copy the config files to the new home 

 

Copy the listener.ora, tnsnames.ora and sqlnet.ora files to the new HOME, and update any references to the ORACLE_HOME 

If the V11 passwords are not complex, then this is needed in the sqlnet.ora file until they can be reset  

SQLNET.ALLOWED_LOGON_VERSION_SERVER=11 

Stop the V11 listener and restart from the V19 home 

lsnrctl stop 

export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1 

export PATH=$ORACLE_HOME/bin:$PATH 

lsnrctl start 

 

Start the database from the V19 home in upgrade mode 

 

 

Exit from SQLPlus and run the upgrade script from the shell 

Graphical user interface, text Description automatically generated 

Graphical user interface, text, application, email Description automatically generated 

Graphical user interface, text, application Description automatically generated 

 

Once complete, restart the database and run the post upgrade steps – make sure you are in the V19 home 

Graphical user interface, text, application Description automatically generated 

Check the timezone file and update if needed 

A picture containing table Description automatically generated 

Restart in upgrade mode and run the SQL to update the TZ file 

 

Stop and restart the database normally, then run the TZ upgrade command 

 

A picture containing chart Description automatically generated 

Run the post-fixup script 

@/u01/app/oracle/cfgtoollogs/ORCL/preupgrade/postupgrade_fixups.sql 

 

 

Edit the /etc/oratab to reflect the new ORACLE_HOME 

Upgrade complete 

 

Appendix A – preupgrade report 

Report generated by Oracle Database Pre-Upgrade Information Tool Version  

19.0.0.0.0 Build: 1 on 2022-08-03T10:53:16 

 

Upgrade-To version: 19.0.0.0.0 

 

======================================= 

Status of the database prior to upgrade 

======================================= 

      Database Name:  ORCL 

     Container Name:  Not Applicable in Pre-12.1 database 

       Container ID:  Not Applicable in Pre-12.1 database 

            Version:  11.2.0.4.0 

     DB Patch Level:  No Patch Bundle applied 

         Compatible:  11.2.0.4.0 

          Blocksize:  8192 

           Platform:  Linux x86 64-bit 

      Timezone File:  14 

  Database log mode:  ARCHIVELOG 

           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 

  ================ 

  1.  Set DB_RECOVERY_FILE_DEST_SIZE initialization parameter to at least 4981 

      MB.  Check alert log during the upgrade to ensure there is remaining free 

      space available in the recovery area. 

       

      DB_RECOVERY_FILE_DEST_SIZE is set at 4182 MB.  There is currently 4151 MB 

      of free space remaining, which may not be adequate for the upgrade. 

       

      Currently: 

       Fast recovery area :  /u03/fast_recovery_area  

       Limit              :  4182 MB  

       Used               :  31 MB  

       Available          :  4151 MB  

       

      The database has archivelog mode enabled, and the upgrade process will 

      need free space to generate archived logs to the recovery area specified 

      by initialization parameter DB_RECOVERY_FILE_DEST.  The logs generated 

      must not overflow the limit set by DB_RECOVERY_FILE_DEST_SIZE, as that 

      can cause the upgrade to not proceed. 

 

  RECOMMENDED ACTIONS 

  =================== 

  2.  Update NUMERIC INITIALIZATION PARAMETERS to meet estimated minimums.  

      This action may be done now or when starting the database in upgrade mode 

      using the 19 ORACLE HOME. 

       

       Parameter                                 Currently  19 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. 

 

  3.  Remove the EM repository. 

       

      - Copy the $ORACLE_HOME/rdbms/admin/emremove.sql script from the target 

      19 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. 

 

  4.  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. 

 

  5.  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 18.2.0.00.12. 

       

      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. 

 

  6.  (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. 

 

  7.  (AUTOFIXUP) 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. 

 

  8.  (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 

  ================ 

  9.  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                             490 MB       722 MB 

      SYSTEM                             740 MB      1171 MB 

      TEMP                                20 MB       150 MB 

      UNDOTBS1                            30 MB       446 MB 

       

      Minimum tablespace sizes for upgrade are estimates. 

 

  10. Run $ORACLE_HOME/rdbms/admin/catnoexf.sql located in the new Oracle 

      Database Oracle home to remove both EXF and RUL. 

       

      Expression Filter (EXF) or Rules Manager (RUL) exist in the database. 

       

      Starting with Oracle Database release 12.1, the Expression Filter (EXF) 

      and Database Rules Manager (RUL) features are desupported, and are 

      removed during the upgrade process.  This step can be manually performed 

      before the upgrade to reduce downtime. 

 

  11. Check the Oracle Backup and Recovery User's Guide for information on how 

      to manage an RMAN recovery catalog schema. 

       

      If you are using a version of the recovery catalog schema that is older 

      than that required by the RMAN client version, then you must upgrade the 

      catalog schema. 

       

      It is good practice to have the catalog schema the same or higher version 

      than the RMAN client version you are using. 

 

  ORACLE GENERATED FIXUP SCRIPT 

  ============================= 

  All of the issues in database ORCL 

  which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by 

  executing the following 

 

    SQL>@/u01/app/oracle/cfgtoollogs/ORCL/preupgrade/preupgrade_fixups.sql 

 

============= 

AFTER UPGRADE 

============= 

 

  REQUIRED ACTIONS 

  ================ 

  None 

 

  RECOMMENDED ACTIONS 

  =================== 

  12. Upgrade the database time zone file using the DBMS_DST package. 

       

      The database is using time zone file version 14 and the target 19 release 

      ships with time zone file version 32. 

       

      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 19 Oracle Database 

      Globalization Support Guide. 

 

  13. To identify directory objects with symbolic links in the path name, run 

      $ORACLE_HOME/rdbms/admin/utldirsymlink.sql AS SYSDBA after upgrade. 

      Recreate any directory objects listed, using path names that contain no 

      symbolic links. 

       

      Some directory object path names may currently contain symbolic links. 

       

      Starting in Release 18c, symbolic links are not allowed in directory 

      object path names used with BFILE data types, the UTL_FILE package, or 

      external tables. 

 

  14. (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. 

 

  15. 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 

  ================ 

  16. 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 ORCL 

  which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by 

  executing the following 

 

    SQL>@/u01/app/oracle/cfgtoollogs/ORCL/preupgrade/postupgrade_fixups.sql 

 

 

================== 

PREUPGRADE SUMMARY 

================== 

  /u01/app/oracle/cfgtoollogs/ORCL/preupgrade/preupgrade.log 

  /u01/app/oracle/cfgtoollogs/ORCL/preupgrade/preupgrade_fixups.sql 

  /u01/app/oracle/cfgtoollogs/ORCL/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/ORCL/preupgrade/preupgrade_fixups.sql 

 

After the upgrade: 

 

Log into the database and execute the postupgrade fixups 

@/u01/app/oracle/cfgtoollogs/ORCL/preupgrade/postupgrade_fixups.sql 

 

Preupgrade complete: 2022-08-03T10:53:16 

 

 

 

 

 










Posted by at
Subscribe to: Posts (Atom)