OOW 2019 – AutoUpgrade to Oracle 19c

  OOW 2019 Hands-On Lab

Welcome to our Oracle Open World 2019 UPGRADE Hands-On Lab.

If you need slides or if you’d like to download the lab by yourself, please note the blog’s address:

Please follow the lab instructions – you can always copy/paste but this shouldn’t be necessary.


1. Inside the lab

Username and Passwords

All passwords are set to: oracle

This applies to the OS user root and oracle as well as to the database accounts for SYS and SYSTEM.

Switching environments

You can switch between the different environments by typing: . db12
This reads: dot <space/blank> db12

There is an additional environment variable $OH19 defined for convenience. This points to the 19c Oracle Home, and will be used several times in the lab.

Notation conventions

This is VERY IMPORTANT for the entire lab.

This color means: Execute on the OS level in an xterm:

. db12
sqlplus / as sysdba

This color means: Execute in SQL*Plus:

shutdown immediate
startup open read only;

And the third color means: Execute in the job console upg>:

help

This way you can copy&paste from the instructions if necessary without having trouble with prompts.


2. Hands-On Lab Overview

Today you will upgrade the DB12 database from Oracle 12.2.0.1 to Oracle 19c with the new AutoUpgrade utility. The idea of the lab is to make you familiar with the new AutoUpgrade and its capabilities.

In the lab we use already the newest version from MOS Note: 2485457.1 – AutoUpgrade Tool.
With this tool, you can upgrade multiple Oracle databases (minimum version: 11.2.0.4) to:

  • Oracle 12.2.0.1 with Release Update January 2019 or newer
  • Oracle 18.5.0 or newer
  • Oracle 19.3.0 or newer

3. Start here with the Lab

3.1. Preparation

At first, you will need to clean up a bit.

Please shutdown the CDB2 database and startup the database DB12.

Open an xterm (double-click) and follow the instructions below.

Shutdown CDB2 at first:

cd
. cdb2
sqlplus / as sysdba

shutdown abort
exit

Then startup the database DB12 and enable ARCHIVELOG mode.
In this particular case you need to shut it down with SHUTDOWN IMMEDIATE at first, then MOUNT it..

. db12
sqlplus / as sysdba

startup
shutdown immediate
startup mount
alter database archivelog;
alter database open;
exit

Then you will need to cleanup a bit as otherwise you may run out of archive space later on.
Just issue:

rm -rf /u02/oradata/CDB1
rm -rf /u02/oradata/CDB2
rm -rf /u02/oradata/FTEX

.

3.2. Generate and edit the config file

For the AutoUpgrade you will need to create only a config file. Everything else is handled by the tool. You’ll start generating a sample config file.

java -jar $OH19/rdbms/admin/autoupgrade.jar -create_sample_file config

You will need to edit it – and then pass it to the AutoUpgrade utility.

Created sample configuration file /home/oracle/sample_config.cfg

Open the file /home/oracle/sample_config.cfg in your preferred editor (text or graph mode)

  • Text mode:

    vi /home/oracle/sample_config.cfg

  • Graphical mode:

    kwrite /home/oracle/sample_config.cfg &

Adjust the following things:

Generated standard config.cfg Make the following adjustments:
#Global configurations
#Autoupgrade's global directory, ...
#temp files created and other ...
#send here
global.autoupg_log_dir=/default/...

#
# Database number 1 
#
upg1.dbname=employee
upg1.start_time=NOW
upg1.source_home=/u01/...
upg1.target_home=/u01/...
upg1.sid=emp
upg1.log_dir=/scratch/auto
upg1.upgrade_node=hol1.localdomain
upg1.target_version=19
#upg1.run_utlrp=yes
#upg1.timezone_upg=yes
#Global configurations
#Autoupgrade's global directory, ...
#temp files created and other ...
#send here
global.autoupg_log_dir=/home/oracle/upg_logs


#
# Database number 1 
#
upg1.dbname=DB12
upg1.start_time=NOW
upg1.source_home=/u01/app/oracle/product/12.2.0.1
upg1.target_home=/u01/app/oracle/product/19
upg1.sid=DB12
upg1.log_dir=/home/oracle/upg_logs
upg1.upgrade_node=hol1.localdomain
upg1.target_version=19
#upg1.run_utlrp=yes
upg1.timezone_upg=no

Then save the file and name it as DB12.cfg in /home/oracle.

If you saved it under its original name, sample_config.cfg, rename it as shown below:

mv /home/oracle/sample_config.cfg /home/oracle/DB12.cfg

.

3.3. Analyze Mode

It is best practice to run AutoUpgrade in analyze mode at first. Once the analyze phase is passed without issues, the database can be upgraded automatically (the below command is a one-line command!).

java -jar $OH19/rdbms/admin/autoupgrade.jar -config /home/oracle/DB12.cfg -mode analyze

You will see this output:

Autoupgrade tool launched with default options
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
1 databases will be analyzed
Type 'help' to list console commands
upg>

You can monitor the analyze phase in the upg> job console with:

lsj

status -job 100

Shortly after, the console will reply:

upg> Job 100 completed
------------------- Final Summary --------------------
Number of databases [ 1 ]

Jobs finished successfully [1]
Jobs failed [0]
Jobs pending [0]
------------- JOBS FINISHED SUCCESSFULLY -------------
Job 100 FOR DB12

The database can be upgraded automatically.

.

3.4. Deploy Mode – Upgrade

When you initiate the upgrade with -mode deploy, the tool will repeat the analyze phase, but add the fixups, upgrade and postupgrade steps.

java -jar $OH19/rdbms/admin/autoupgrade.jar -config /home/oracle/DB12.cfg -mode deploy

You will see this output:

Autoupgrade tool launched with default options
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
1 databases will be processed
Type 'help' to list console commands
upg>

At this point you can monitor the upgrade now – enlarge the xterm‘s width a bit to see no line wraps.

Type help on the upg> job console to see an overview of available commands.

help

upg> help
exit                          // To close and exit
help                          // Displays help
lsj [(-r|-f|-p|-e) | -n ]     // 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  Display up to n jobs.
lsr                           // Displays the restoration queue
lsa                           // Displays the abort queue
tasks                         // Displays the tasks running
clear                         // Clears the terminal
resume -job                   // Restarts a previous job that was running
status [-job  [-long]]        // Lists all the jobs or a specific job
restore -job                  // 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
abort -job                    // Aborts the specified job
h[ist]                        // Displays the command line history
/[]                           // Executes the command specified from the history. The default is the last command

The most important commands are:

    • lsj – this lists the job number and overview information about each active job.
      Please note that the job number has now changed for the -mode deploy run.

      • lsj

        +----+-------+---------+---------+-------+--------------+--------+--------+-------+
        |Job#|DB_NAME|    STAGE|OPERATION| STATUS|    START_TIME|END_TIME| UPDATED|MESSAGE|
        +----+-------+---------+---------+-------+--------------+--------+--------+-------+
        | 101|   DB12|DBUPGRADE|EXECUTING|RUNNING|19/09/12 21:44|     N/A|21:46:38|Running|
        +----+-------+---------+---------+-------+--------------+--------+--------+-------+
        
    • status -job <number> – this gives you more information about a specific job.
      It displays you also where the log files are located.

      • status -job 101

        Progress
        -----------------------------------
        Start time:      19/09/10 17:52
        Elapsed (min):   3
        End time:        N/A
        Last update:     2019-09-10T17:53:27.572
        Stage:           PREFIXUPS
        Operation:       EXECUTING
        Status:          RUNNING
        Pending stages:  6
        Stage summary: 
            SETUP             <1 min 
            PREUPGRADE        <1 min 
            PRECHECKS         <1 min 
            GRP               <1 min 
            PREFIXUPS         1 min (IN PROGRESS)
        
        Job Logs Locations
        -----------------------------------
        Logs Base:    /home/oracle/upg_logs/DB12
        Job logs:     /home/oracle/upg_logs/DB12/101
        Stage logs:   /home/oracle/upg_logs/DB12/101/prefixups
        TimeZone:     /home/oracle/upg_logs/DB12/temp
        
        Additional information
        -----------------------------------
        Details:
        +--------+----------------+-------+
        |DATABASE|           FIXUP| STATUS|
        +--------+----------------+-------+
        |    DB12|DICTIONARY_STATS|STARTED|
        +--------+----------------+-------+
        
        Error Details:
        None
    • logs – displays the logs folder
      • logs

        Autoupgrade logs folder [/home/oracle/upg_logs/cfgtoollogs/upgrade/auto/config_files]
        logs folder [DB12][/home/oracle/upg_logs/DB12]

        Please open a second xterm tab and go to the logs folder.

        cd /home/oracle/upg_logs/DB12/101

        Explore the subdirectories, especially /home/oracle/upg_logs/DB12/101 and below.

      • Check the /home/oracle/upg_logs/DB12/101/preupgrade subdirectory. It contains an HTML file with the preupgrade check overview:

        cd prechecks
        firefox db12_preupgrade.html &

        Check also the preupgrade.log within the same directory:

        more db12_preupgrade.log

      • Now change the directoy and see whether the dbupgrade directory has been created. This usually takes up to 4 minutes until the prechecks and fixups have been completed. You will find the 4 upgrade worker’s logs in cd /home/oracle/upg_logs/DB12/101/dbupgrade.These 4 subdirectories get created before dbupgrade:
        * prechecks
        * prefixups
        * preupgrade
        * drainYou can tail -f especially the main worker’s (ending with 0) log to display the upgrade progress.

        cd ../dbupgrade
        tail -f catupgrd*0.log

        Interrupt the tail command with CTRL+C.

Depending on the hardware, the upgrade would take 25 minutes to upgrade the database. You don’t have to wait but instead we will do some exercises now with the AutoUpgrade tool.

.

3.5. AutoUpgrade Exercises

Make sure, the UPGRADE phase has been reached already before going forward. lsj should display something like this:

upg> lsj
+----+-------+---------+---------+-------+--------------+--------+--------+------------+
|Job#|DB_NAME|    STAGE|OPERATION| STATUS|    START_TIME|END_TIME| UPDATED|     MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+--------+------------+
| 101|   DB12|DBUPGRADE|EXECUTING|RUNNING|19/09/10 19:19|     N/A|19:28:44|10%Upgraded |
+----+-------+---------+---------+-------+--------------+--------+--------+------------+

You will abort the job in the upg> job console:

abort -job 101

You will see this response on the upg> console:

upg> abort -job 101
Are you sure you want to abort job [101] ? [y|N] y
Abort job: [101][DB12]
upg> 
-------------------------------------------------
Errors in database [DB12]
Stage     [DBUPGRADE]
Operation [STOPPED]
Status    [ERROR]
Info    [
Error: UPG-1419
[Unexpected Exception Error]
Cause: This indicates that the upgrade has been killed, by an abort or restore command.
For further details, see the log file located at /home/oracle/upg_logs/DB12/101/autoupgrade_20190910_user.log]

-------------------------------------------------
Logs: [/home/oracle/upg_logs/DB12/101/autoupgrade_20190910_user.log]
-------------------------------------------------

Hit RETURN to get back to the upg> console.

Check what the console tells you:

status -job 101

upg> status -job 101
Progress
-----------------------------------
Start time:      19/09/10 19:19
Elapsed (min):   15
End time:        N/A
Last update:     2019-09-10T19:32:36.960
Stage:           DBUPGRADE
Operation:       STOPPED
Status:          ABORTED
Pending stages:  4
Stage summary: 
    SETUP             <1 min 
    PREUPGRADE        <1 min 
    PRECHECKS         <1 min 
    GRP               <1 min 
    PREFIXUPS         4 min 
    DRAIN             <1 min 
    DBUPGRADE         7 min 

Job Logs Locations
-----------------------------------
Logs Base:    /home/oracle/upg_logs/DB12
Job logs:     /home/oracle/upg_logs/DB12/101
Stage logs:   /home/oracle/upg_logs/DB12/101/dbupgrade
TimeZone:     /home/oracle/upg_logs/DB12/temp

Additional information
-----------------------------------
Details:
Job [101][DB12] successfully aborted

Error Details:

Error: UPG-1419
[Unexpected Exception Error]
Cause: This indicates that the upgrade has been killed, by an abort or restore command.
For further details, see the log file located at /home/oracle/upg_logs/DB12/101/autoupgrade_20190910_user.log

List the abort queue:

lsa

upg> lsa
+----+--------+
|Job#|  STATUS|
+----+--------+
| 101|FINISHED|
+----+--------+
Total 1

Now resume the job again:

resume -job 101

upg> resume -job 101
Resuming job: [101][DB12]

This sequence allows you to fix something – or pause the upgrade – and resume it afterwards.

Please wait a bit before you go forward with the lab exercise.
Check the job progress (before: 10% Upgraded – now: 21% Upgraded):

lsj

upg> lsj
+----+-------+---------+---------+-------+--------------+--------+--------+------------+
|Job#|DB_NAME|    STAGE|OPERATION| STATUS|    START_TIME|END_TIME| UPDATED|     MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+--------+------------+
| 101|   DB12|DBUPGRADE|EXECUTING|RUNNING|19/09/10 19:19|     N/A|19:38:41|21%Upgraded |
+----+-------+---------+---------+-------+--------------+--------+--------+------------+
Total jobs 1

As next exercise, you will stop the job again. But now you will restore to the previous (before upgrade!) state.

abort -job 101

upg> abort -job 101
Are you sure you want to abort job [101] ? [y|N] y
Abort job: [101][DB12]
upg> help
-------------------------------------------------
Errors in database [DB12]
Stage     [DBUPGRADE]
Operation [STOPPED]
Status    [ERROR]
Info    [
Error: UPG-1419
[Unexpected Exception Error]
Cause: This indicates that the upgrade has been killed, by an abort or restore command.
For further details, see the log file located at /home/oracle/upg_logs/DB12/101/autoupgrade_20190910_user.log]

Check the current situation:

lsj

upg> lsj          
+----+-------+---------+---------+-------+--------------+--------+--------+--------------------+
|Job#|DB_NAME|    STAGE|OPERATION| STATUS|    START_TIME|END_TIME| UPDATED|             MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+--------+--------------------+
| 101|   DB12|DBUPGRADE|  STOPPED|ABORTED|19/09/10 19:19|     N/A|19:42:34|Job 101 killed succe|
+----+-------+---------+---------+-------+--------------+--------+--------+--------------------+
Total jobs 1

status -job 101

upg> status -job 101
Progress
-----------------------------------
Start time:      19/09/10 19:19
Elapsed (min):   25
End time:        N/A
Last update:     2019-09-10T19:42:34.045
Stage:           DBUPGRADE
Operation:       STOPPED
Status:          ABORTED
Pending stages:  4
Stage summary: 
    SETUP             <1 min 
    PREUPGRADE        <1 min 
    PRECHECKS         <1 min 
    GRP               <1 min 
    PREFIXUPS         4 min 
    DRAIN             <1 min 
    DBUPGRADE         4 min 

Job Logs Locations
-----------------------------------
Logs Base:    /home/oracle/upg_logs/DB12
Job logs:     /home/oracle/upg_logs/DB12/101
Stage logs:   /home/oracle/upg_logs/DB12/101/dbupgrade
TimeZone:     /home/oracle/upg_logs/DB12/temp

Additional information
-----------------------------------
Details:
Job [101][DB12] successfully aborted

Error Details:

Error: UPG-1419
[Unexpected Exception Error]
Cause: This indicates that the upgrade has been killed, by an abort or restore command.
For further details, see the log file located at /home/oracle/upg_logs/DB12/101/autoupgrade_20190910_user.log

Restore the database to its previous (before upgrade!) state:

restore -job 101

upg> restore -job 101
The job 101[DB12] on stage [DBUPGRADE] has the status [ABORTED]
Are you sure you want to restore? all progress will be lost [y/N] y

Monitor what’s happening:

lsj

upg> lsj
+----+-------+----------+---------+-------+--------------+--------+--------+----------------+
|Job#|DB_NAME|     STAGE|OPERATION| STATUS|    START_TIME|END_TIME| UPDATED|         MESSAGE|
+----+-------+----------+---------+-------+--------------+--------+--------+----------------+
| 101|   DB12|GRPRESTORE|EXECUTING|RUNNING|19/09/10 19:19|     N/A|19:46:13|Restore Phase[4]|
+----+-------+----------+---------+-------+--------------+--------+--------+----------------+
Total jobs 1

status -job 101

upg> status -job 101
Progress
-----------------------------------
Start time:      19/09/10 19:19
Elapsed (min):   27
End time:        N/A
Last update:     2019-09-10T19:46:23.123
Stage:           GRPRESTORE
Operation:       EXECUTING
Status:          RUNNING
Pending stages:  -1
Stage summary: 
    SETUP             <1 min 
    PREUPGRADE        <1 min 
    PRECHECKS         <1 min 
    GRP               <1 min 
    PREFIXUPS         4 min 
    DRAIN             <1 min 
    DBUPGRADE         8 min 
    GRPRESTORE        <1 min (IN PROGRESS)

Job Logs Locations
-----------------------------------
Logs Base:    /home/oracle/upg_logs/DB12
Job logs:     /home/oracle/upg_logs/DB12/101
Stage logs:   /home/oracle/upg_logs/DB12/101
TimeZone:     /home/oracle/upg_logs/DB12/temp

Additional information
-----------------------------------
Details:
Restoration, phase 5/6 - Flashing back to GRP   

Error Details:
None

The database DB12 will be flashed back to the Guaranteed Restore Point created automatically by default by the AutoUpgrade tool.

Monitor again 1 minute later:

lsj

upg> lsj
+----+-------+-----+---------+--------+--------------+--------+--------+-----------+
|Job#|DB_NAME|STAGE|OPERATION|  STATUS|    START_TIME|END_TIME| UPDATED|    MESSAGE|
+----+-------+-----+---------+--------+--------------+--------+--------+-----------+
| 101|   DB12|SETUP|  STOPPED|FINISHED|19/09/10 19:19|     N/A|19:46:35|DB Restored|
+----+-------+-----+---------+--------+--------------+--------+--------+-----------+
Total jobs 1

status -job 101

upg> status -job 101
Progress
-----------------------------------
Start time:      19/09/10 19:19
Elapsed (min):   30
End time:        N/A
Last update:     2019-09-10T19:46:35.511
Stage:           SETUP
Operation:       STOPPED
Status:          FINISHED
Pending stages:  10
Stage summary: 
    SETUP             -27 min 
    PREUPGRADE        <1 min 
    PRECHECKS         <1 min 
    GRP               <1 min 
    PREFIXUPS         4 min 
    DRAIN             <1 min 
    DBUPGRADE         8 min 
    GRPRESTORE        <1 min 

Job Logs Locations
-----------------------------------
Logs Base:    /home/oracle/upg_logs/DB12
Job logs:     /home/oracle/upg_logs/DB12/101
Stage logs:   /home/oracle/upg_logs/DB12/101
TimeZone:     /home/oracle/upg_logs/DB12/temp

Additional information
-----------------------------------
Details:
This database was restored and left in its original state.  Check the log files for further details about the incident that caused the restoration   

Error Details:
None

Final task is now to clean up:

exit

upg> exit
There is 1 job in progress. if you exit it will stop
Are you sure you wish to leave? [y|N] y
------------------- Final Summary --------------------
Number of databases            [ 1 ]

Jobs finished successfully     [0]
Jobs failed                    [0]
Jobs pending                   [1]
-------------------- JOBS PENDING --------------------
Job 101 FOR DB12

Exiting

You need to cleanup now the internal state of the AutoUpgrade tool. This way, the logs will be retained – but you can run the upgrade again. In case you’d like the logs from the previous runs to be removed, please feel free to delete the log directory trees manually.

The AutoUpgrade tool will reset the state:

java -jar $OH19/rdbms/admin/autoupgrade.jar -config /home/oracle/DB12.cfg -mode analyze -clear_recovery_data

$ java -jar $OH19/rdbms/admin/autoupgrade.jar -config /home/oracle/DB12.cfg -mode analyze -clear_recovery_data
The recovery data was removed, now you can start from scratch the AutoUpgrade

Now invoke an -mode analyze run again and check the job number.

java -jar $OH19/rdbms/admin/autoupgrade.jar -config /home/oracle/DB12.cfg -mode analyze

lsj

upg> lsj
+----+-------+-----+---------+--------+--------------+--------+--------+--------+
|Job#|DB_NAME|STAGE|OPERATION|  STATUS|    START_TIME|END_TIME| UPDATED| MESSAGE|
+----+-------+-----+---------+--------+--------------+--------+--------+--------+
| 103|   DB12|SETUP|PREPARING|FINISHED|19/09/10 21:07|     N/A|21:07:15|Starting|
+----+-------+-----+---------+--------+--------------+--------+--------+--------+
Total jobs 1

You can now invoke another upgrade 🙂

Congratulations – you completed our OOW 2019 Database AutoUpgrade to 19c Hands-On Lab successfully!


 
Share this: