« PREVIOUS «

Welcome to our AutoUpgrade Hands-On Lab.

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

https://MikeDietrichDE.com

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:

example: RED means you’ll type this in an xterm

This color means: Execute in SQL*Plus:

example: GREY means you’ll type this in SQL*Plus

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

example: BLUE means you’ll type this in the autouopgrade job console

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 two databases, FTEX and DB12 database from Oracle 11.2.0.4 and 12.2.0.1 to Oracle 19c by using 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
3.2. Generate and edit the config file
3.3. Analyze Mode
3.4. Deploy Mode
3.5. Plugin
3.6 Cleanup

3.1. Preparation

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

Execute the cleanup script – it will free up space and switch one of the databases into ARCHIVELOG mode for the lab.

. /home/oracle/scripts/clean.sh

Then set the environment to start the lab.

. db12
cd

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

#
# Database number 2
#
#upg2.dbname=database name
#upg2.start_time=17/10/2019 22:09:43
#upg2.source_home=Path to source 
#upg2.target_home=Path to target 
#upg2.sid=Value of Oracle SID
#upg2.log_dir=Path to log dir
#upg2.upgrade_node=hol.localdomain # y
#upg2.target_version=12.2
#upg2.run_utlrp=[yes|no]
#upg2.timezone_upg=[yes|no]

#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=localhost
upg1.target_version=19
#upg1.run_utlrp=yes
upg1.timezone_upg=no

#
# Database number 2
#
upg2.dbname=FTEX
upg2.start_time=NOW
upg2.source_home=/u01/app/oracle/product/11.2.0.4 
upg2.target_home=/u01/app/oracle/product/19
upg2.sid=FTEX
upg2.log_dir=/home/oracle/upg_logs
upg2.upgrade_node=localhost
upg2.target_version=19
upg2.timezone_upg=no
upg2.restoration=no
upg2.target_cdb=CDB2

Then save the file and name it as UP19.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/UP19.cfg

In case you have issues with editing the file, there is a copy stored already in: /home/oracle/scripts/UP19.cfg. You can use this one then instead.
Just copy it one folder up: cp /home/oracle/scripts/UP19.cfg /home/oracle
.

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!).

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

You will see this output:

Autoupgrade tool launched with default options
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
2 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
Job 101 completed
------------------- Final Summary --------------------
Number of databases            [ 2 ]

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

Both databases 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/UP19.cfg -mode deploy

You will see this output:

Autoupgrade tool launched with default options
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
2 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

upg> lsj
+----+-------+---------+---------+--------+--------------+--------+--------+-------------+
|Job#|DB_NAME|    STAGE|OPERATION|  STATUS|    START_TIME|END_TIME| UPDATED|      MESSAGE|
+----+-------+---------+---------+--------+--------------+--------+--------+-------------+
| 102|   FTEX|PREFIXUPS|EXECUTING| RUNNING|19/10/17 23:15|     N/A|23:15:28|Remaining 3/3|
| 103|   DB12|    SETUP|PREPARING|FINISHED|19/10/17 23:16|     N/A|23:15:13|    Scheduled|
+----+-------+---------+---------+--------+--------------+--------+--------+-------------+
Total jobs 2

status -job <number> – this gives you more information about a specific job.
It displays you also where the log files are located.

status -job 102

upg> status -job 103
Progress
-----------------------------------
Start time:      19/10/17 23:16
Elapsed (min):   1
End time:        N/A
Last update:     2019-10-17T23:16:58.468
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/103
Stage logs:   /home/oracle/upg_logs/DB12/103/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 [FTEX][/home/oracle/upg_logs/FTEX]
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/FTEX/102
  
  Explore the subdirectories, especially /home/oracle/upg_logs/FTEX/102 and below.
- Check the /home/oracle/upg_logs/FTEX/102/prechecks subdirectory. It contains an HTML file with the preupgrade check overview:
  cd prechecks
  firefox ftex_preupgrade.html &
  
  Check also the preupgrade.log within the same directory:
  
  more ftex_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/FTEX/102/dbupgrade.These 4 subdirectories get created before dbupgrade:
```
prechecks
prefixups
preupgrade
drain
```
- You 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 will take about 25-35 minutes. You don’t have to wait but instead we will do some exercises now with the AutoUpgrade tool.

3.5. AutoUpgrade Exercises

Before going forward with the exercise, make sure, the UPGRADE phase for database DB12 has been reached already. lsj should display something like this – the DB12 database has already completed 8% Upgrade Progress. You may need to wait approximately 6-7 minutes after you started the deploy phase.

upg> lsj
+----+-------+---------+---------+-------+--------------+--------+--------+------------+
|Job#|DB_NAME|    STAGE|OPERATION| STATUS|    START_TIME|END_TIME| UPDATED|     MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+--------+------------+
| 102|   FTEX|DBUPGRADE|EXECUTING|RUNNING|19/10/17 23:15|     N/A|23:22:42|10%Upgraded |
| 103|   DB12|DBUPGRADE|EXECUTING|RUNNING|19/10/17 23:16|     N/A|23:23:45| 8%Upgraded |
+----+-------+---------+---------+-------+--------------+--------+--------+------------+
Total jobs 2

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

abort -job 103

You will see this response on the upg> console:

upg> abort -job 103
Are you sure you want to abort job [103] ? [y|N] y
Abort job: [103][DB12]
-------------------------------------------------
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/103/autoupgrade_20191017_user.log]

-------------------------------------------------
Logs: [/home/oracle/upg_logs/DB12/103/autoupgrade_20191017_user.log]
-------------------------------------------------

Hit RETURN to get back to the upg> console.

Check what the console tells you:

status -job 103

upg> status -job 103
Progress
-----------------------------------
Start time:      19/10/17 23:16
Elapsed (min):   11
End time:        N/A
Last update:     2019-10-17T23:27:10.143
Stage:           DBUPGRADE
Operation:       STOPPED
Status:          ABORTED
Pending stages:  4
Stage summary: 
    SETUP             <1 min 
    PREUPGRADE        <1 min 
    PRECHECKS         <1 min 
    GRP               <1 min 
    PREFIXUPS         2 min 
    DRAIN             <1 min 
    DBUPGRADE         6 min 

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

Additional information
-----------------------------------
Details:
Job [103][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/103/autoupgrade_20191017_user.log

List the abort queue:

lsa

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

Now resume the job again:

resume -job 103

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

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

Please wait for another x minutes until you will 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|
+----+-------+---------+---------+-------+--------------+--------+--------+------------+
| 102|   FTEX|DBUPGRADE|EXECUTING|RUNNING|19/10/17 23:15|     N/A|23:31:49|39%Upgraded |
| 103|   DB12|DBUPGRADE|EXECUTING|RUNNING|19/10/17 23:16|     N/A|23:32:16|21%Upgraded |
+----+-------+---------+---------+-------+--------------+--------+--------+------------+
Total jobs 2

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

abort -job 103

upg> abort -job 103
Are you sure you want to abort job [103] ? [y|N] y
Abort job: [103][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/103/autoupgrade_20191017_user.log]

-------------------------------------------------
Logs: [/home/oracle/upg_logs/DB12/103/autoupgrade_20191017_user.log]
-------------------------------------------------

Check the current situation:

lsj

upg> lsj
+----+-------+---------+---------+-------+--------------+--------+--------+--------------------+
|Job#|DB_NAME|    STAGE|OPERATION| STATUS|    START_TIME|END_TIME| UPDATED|             MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+--------+--------------------+
| 102|   FTEX|DBUPGRADE|EXECUTING|RUNNING|19/10/17 23:15|     N/A|23:31:49|        39%Upgraded |
| 103|   DB12|DBUPGRADE|  STOPPED|ABORTED|19/10/17 23:16|     N/A|23:33:55|Job 103 killed succe|
+----+-------+---------+---------+-------+--------------+--------+--------+--------------------+
Total jobs 2

status -job 103

upg> status -job 103
Progress
-----------------------------------
Start time:      19/10/17 23:16
Elapsed (min):   19
End time:        N/A
Last update:     2019-10-17T23:33:55.292
Stage:           DBUPGRADE
Operation:       STOPPED
Status:          ABORTED
Pending stages:  4
Stage summary: 
    SETUP             <1 min 
    PREUPGRADE        <1 min 
    PRECHECKS         <1 min 
    GRP               <1 min 
    PREFIXUPS         2 min 
    DRAIN             <1 min 
    DBUPGRADE         4 min 

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

Additional information
-----------------------------------
Details:
Job [103][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/103/autoupgrade_20191017_user.log

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

restore -job 103

upg> restore -job 103
The job 103[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|
+----+-------+----------+---------+-------+--------------+--------+--------+----------------+
| 102|   FTEX| DBUPGRADE|EXECUTING|RUNNING|19/10/17 23:15|     N/A|23:34:51|    52%Upgraded |
| 103|   DB12|GRPRESTORE|EXECUTING|RUNNING|19/10/17 23:16|     N/A|23:36:27|Restore Phase[5]|
+----+-------+----------+---------+-------+--------------+--------+--------+----------------+
Total jobs 2

status -job 103

upg> status -job 103
Progress
-----------------------------------
Start time:      19/10/17 23:16
Elapsed (min):   20
End time:        N/A
Last update:     2019-10-17T23:36:34.114
Stage:           SETUP
Operation:       STOPPED
Status:          FINISHED
Pending stages:  10
Stage summary: 
    SETUP             -19 min 
    PREUPGRADE        <1 min 
    PRECHECKS         <1 min 
    GRP               <1 min 
    PREFIXUPS         2 min 
    DRAIN             <1 min 
    DBUPGRADE         7 min 
    GRPRESTORE        <1 min 

Job Logs Locations
-----------------------------------
Logs Base:    /home/oracle/upg_logs/DB12
Job logs:     /home/oracle/upg_logs/DB12/103
Stage logs:   /home/oracle/upg_logs/DB12/103
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

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

Please note:
The upgrade of FTEX is still running!

Monitor again 1 minute later:

lsj

upg> lsj
+----+-------+---------+---------+--------+--------------+--------+--------+------------+
|Job#|DB_NAME|    STAGE|OPERATION|  STATUS|    START_TIME|END_TIME| UPDATED|     MESSAGE|
+----+-------+---------+---------+--------+--------------+--------+--------+------------+
| 102|   FTEX|DBUPGRADE|EXECUTING| RUNNING|19/10/17 23:15|     N/A|23:34:51|52%Upgraded |
| 103|   DB12|    SETUP|  STOPPED|FINISHED|19/10/17 23:16|     N/A|23:36:34| DB Restored|
+----+-------+---------+---------+--------+--------------+--------+--------+------------+
Total jobs 2

The FTEX database will shortly complete the database upgrade.

lsj

upg> lsj
+----+-------+---------+---------+--------+--------------+--------+--------+------------+
|Job#|DB_NAME|    STAGE|OPERATION|  STATUS|    START_TIME|END_TIME| UPDATED|     MESSAGE|
+----+-------+---------+---------+--------+--------------+--------+--------+------------+
| 102|   FTEX|DBUPGRADE|EXECUTING| RUNNING|19/10/17 23:15|     N/A|23:37:53|91%Upgraded |
| 103|   DB12|    SETUP|  STOPPED|FINISHED|19/10/17 23:16|     N/A|23:36:34| DB Restored|
+----+-------+---------+---------+--------+--------------+--------+--------+------------+
Total jobs 2

Please check what FTEX is doing:

status -job 102

upg> status -job 102
Progress
-----------------------------------
Start time:      19/10/17 23:15
Elapsed (min):   27
End time:        N/A
Last update:     2019-10-17T23:41:17.960
Stage:           DBUPGRADE
Operation:       EXECUTING
Status:          RUNNING
Pending stages:  5
Stage summary: 
    SETUP             <1 min 
    PREUPGRADE        <1 min 
    PRECHECKS         <1 min 
    PREFIXUPS         <1 min 
    DRAIN             <1 min 
    DBUPGRADE         26 min (IN PROGRESS)

Job Logs Locations
-----------------------------------
Logs Base:    /home/oracle/upg_logs/FTEX
Job logs:     /home/oracle/upg_logs/FTEX/102
Stage logs:   /home/oracle/upg_logs/FTEX/102/dbupgrade
TimeZone:     /home/oracle/upg_logs/FTEX/temp

Additional information
-----------------------------------
Details:
[Compiling] is [0%] completed for [ftex] objects remaining is [5716]
                 +---------+------------+
                 |CONTAINER|  PERCENTAGE|
                 +---------+------------+
                 |     FTEX|COMPILE [0%]|
                 +---------+------------+

Error Details:
None

The FTEX database entered the recompilation phase. As the console does get refreshed only every 180 seconds, it will take a bit until you will see progress.

lsj

upg> lsj   
+----+-------+---------+---------+--------+--------------+--------+--------+------------+
|Job#|DB_NAME|    STAGE|OPERATION|  STATUS|    START_TIME|END_TIME| UPDATED|     MESSAGE|
+----+-------+---------+---------+--------+--------------+--------+--------+------------+
| 102|   FTEX|DBUPGRADE|EXECUTING| RUNNING|19/10/17 23:15|     N/A|23:44:20|92%Compiled |
| 103|   DB12|    SETUP|  STOPPED|FINISHED|19/10/17 23:16|     N/A|23:36:34| DB Restored|
+----+-------+---------+---------+--------+--------------+--------+--------+------------+
Total jobs 2

Shortly after, the FTEX database will be restarted to apply the Post Upgrade Fixups and refresh dictionary statistics:

lsj

upg> lsj
+----+-------+---------+---------+--------+--------------+--------+--------+-------------------+
|Job#|DB_NAME|    STAGE|OPERATION|  STATUS|    START_TIME|END_TIME| UPDATED|            MESSAGE|
+----+-------+---------+---------+--------+--------------+--------+--------+-------------------+
| 102|   FTEX|DBUPGRADE|EXECUTING|FINISHED|19/10/17 23:15|     N/A|23:46:16|Restarting Database|
| 103|   DB12|    SETUP|  STOPPED|FINISHED|19/10/17 23:16|     N/A|23:36:34|        DB Restored|
+----+-------+---------+---------+--------+--------------+--------+--------+-------------------+
Total jobs 2

lsj

upg> lsj
+----+-------+----------+---------+--------+--------------+--------+--------+-------------+
|Job#|DB_NAME|     STAGE|OPERATION|  STATUS|    START_TIME|END_TIME| UPDATED|      MESSAGE|
+----+-------+----------+---------+--------+--------------+--------+--------+-------------+
| 102|   FTEX|POSTCHECKS|PREPARING| RUNNING|19/10/17 23:15|     N/A|23:47:55|Remaining 0/9|
| 103|   DB12|     SETUP|  STOPPED|FINISHED|19/10/17 23:16|     N/A|23:36:34|  DB Restored|
+----+-------+----------+---------+--------+--------------+--------+--------+-------------+
Total jobs 2

But this is not the end of the Hands-On Lab.

3.5. Plug into a CDB

In the UP19.cfg file you used one parameter which we didn’t explain in the talk:

upg2.target_cdb=CDB2

The container database CDB2 is already started – and FTEX will be now plugged into the CDB2 using the NOCOPY option. The datafiles will stay in place.

lsj

upg> lsj
+----+-------+-----------+---------+--------+--------------+--------+--------+--------------------+
|Job#|DB_NAME|      STAGE|OPERATION|  STATUS|    START_TIME|END_TIME| UPDATED|             MESSAGE|
+----+-------+-----------+---------+--------+--------------+--------+--------+--------------------+
| 102|   FTEX|NONCDBTOPDB|EXECUTING| RUNNING|19/10/17 23:15|     N/A|23:50:43|Changing database co|
| 103|   DB12|      SETUP|  STOPPED|FINISHED|19/10/17 23:16|     N/A|23:36:34|         DB Restored|
+----+-------+-----------+---------+--------+--------------+--------+--------+--------------------+
Total jobs 2

To generate the XML manifest file for the plugin with DBMS_PDB.DESCRIBE, FTEX will be started READ ONLY.

lsj

upg> lsj
+----+-------+-----------+---------+--------+--------------+--------+--------+-------------------+
|Job#|DB_NAME|      STAGE|OPERATION|  STATUS|    START_TIME|END_TIME| UPDATED|            MESSAGE|
+----+-------+-----------+---------+--------+--------------+--------+--------+-------------------+
| 102|   FTEX|NONCDBTOPDB|EXECUTING| RUNNING|19/10/17 23:15|     N/A|23:51:26|Executing describe.|
| 103|   DB12|      SETUP|  STOPPED|FINISHED|19/10/17 23:16|     N/A|23:36:34|        DB Restored|
+----+-------+-----------+---------+--------+--------------+--------+--------+-------------------+
Total jobs 2

To complete the plugin operation, the sanity script noncdb_to_pdb.sql must be run by autoupgrade.jar. Please expect a runtime of about 10 minutes until completion.

lsj

upg> lsj
+----+-------+-----------+---------+--------+--------------+--------+--------+--------------------+
|Job#|DB_NAME|      STAGE|OPERATION|  STATUS|    START_TIME|END_TIME| UPDATED|             MESSAGE|
+----+-------+-----------+---------+--------+--------------+--------+--------+--------------------+
| 102|   FTEX|NONCDBTOPDB|EXECUTING| RUNNING|19/10/17 23:15|     N/A|23:52:58|Executing noncdb_to_|
| 103|   DB12|      SETUP|  STOPPED|FINISHED|19/10/17 23:16|     N/A|23:36:34|         DB Restored|
+----+-------+-----------+---------+--------+--------------+--------+--------+--------------------+
Total jobs 2

When you get the message “job 102 completed” then the plugin has been finished.

lsj

upg> lsj
+----+-------+-----------+---------+--------+--------------+--------+--------+--------------------+
|Job#|DB_NAME|      STAGE|OPERATION|  STATUS|    START_TIME|END_TIME| UPDATED|             MESSAGE|
+----+-------+-----------+---------+--------+--------------+--------+--------+--------------------+
| 102|   FTEX|NONCDBTOPDB|EXECUTING| RUNNING|19/10/17 23:15|     N/A|23:52:58|Executing noncdb_to_|
| 103|   DB12|      SETUP|  STOPPED|FINISHED|19/10/17 23:16|     N/A|23:36:34|         DB Restored|
+----+-------+-----------+---------+--------+--------------+--------+--------+--------------------+
Total jobs 2
upg> Job 102 completed

Repeat another lsj command:

lsj

upg> lsj
+----+-------+-----------+---------+--------+--------------+--------------+--------+-----------------+
|Job#|DB_NAME|      STAGE|OPERATION|  STATUS|    START_TIME|      END_TIME| UPDATED|          MESSAGE|
+----+-------+-----------+---------+--------+--------------+--------------+--------+-----------------+
| 102|   FTEX|NONCDBTOPDB|  STOPPED|FINISHED|19/10/17 23:15|19/10/18 00:00|00:00:17|Completed job 102|
| 103|   DB12|      SETUP|  STOPPED|FINISHED|19/10/17 23:16|           N/A|23:36:34|      DB Restored|
+----+-------+-----------+---------+--------+--------------+--------------+--------+-----------------+
Total jobs 2

You may recognize that there’s now an END_TIME as well. But the job 103 you aborted and restored is still active. We will clean up soon. But before, let us check if FTEX really got plugged in correctly and is ready to be used.

First exit the tool and confirm with y.

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            [ 2 ]

Jobs finished successfully     [1]
Jobs failed                    [0]
Jobs pending                   [1]
------------- JOBS FINISHED SUCCESSFULLY -------------
Job 102 FOR FTEX
-------------------- JOBS PENDING --------------------
Job 103 FOR DB12

Exiting

Check the status of the FTEX pluggable database:

. cdb2
sqlplus / as sysdba

show pdbs

You will see the following output – FTEX is now a PDB and open in READ/WRITE mode.

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Oct 18 00:07:24 2019
Version 19.5.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.5.0.0.0

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 FTEX 			  READ WRITE NO

Exit SQL*Plus:

exit

AutoUpgrade did not only upgrade the FTEX database from Oracle 11.2.0.4 to Oracle 19.5.0, it plugged it into the existing CDB in as well and converted it into a PDB.

3.6. Cleanup

Let us clean up the leftover information from the aborted upgrade of DB12 – then we can restart it.

“Cleanup” means, we cleanup 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.

But as the FTEX database is now plugged into CDB2 already, you will have to remove all entries for upg2 from the config file. Otherwise the AutoUpgrade will error out as it would expect the FTEX database to be up and running.

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

Text mode:
vi /home/oracle/UP19.cfg
Graphical mode:
kwrite /home/oracle/UP19.cfg &

Adjust the following things:

UP19.cfg – remove all lines marked in RED:

This is how the file looks like now:

#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=localhost
upg1.target_version=19
#upg1.run_utlrp=yes
upg1.timezone_upg=no

#
# Database number 2
#
upg2.dbname=FTEX
upg2.start_time=NOW
upg2.source_home=/u01/app/oracle/product/11.2.0.4 
upg2.target_home=/u01/app/oracle/product/19
upg2.sid=FTEX
upg2.log_dir=/home/oracle/upg_logs
upg2.upgrade_node=localhost
upg2.target_version=19
upg2.timezone_upg=no
upg2.restoration=no
upg2.target_cdb=CDB2

#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=localhost
upg1.target_version=19
#upg1.run_utlrp=yes
upg1.timezone_upg=no

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

The AutoUpgrade tool will reset the state:

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

$ java -jar $OH19/rdbms/admin/autoupgrade.jar -config /home/oracle/UP19.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/UP19.cfg -mode analyze

lsj

upg> lsj
+----+-------+---------+---------+-------+--------------+--------+--------+---------------+
|Job#|DB_NAME|    STAGE|OPERATION| STATUS|    START_TIME|END_TIME| UPDATED|        MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+--------+---------------+
| 104|   DB12|PRECHECKS|PREPARING|RUNNING|19/10/18 08:11|     N/A|08:11:27|Loading DB info|
+----+-------+---------+---------+-------+--------------+--------+--------+---------------+
Total jobs 1

You can now invoke another upgrade 🙂

Congratulations – you completed our AutoUpgrade to Oracle 19c Hands-On Lab successfully!

« PREVIOUS «

MAIN INDEX 19C HOL

» MAIN INDEX HOL 19c »

AutoUpgrade Hands-On Lab to Oracle 19c

1. Inside the lab

Username and Passwords

Switching environments

Notation conventions

2. Hands-On Lab Overview

3. Start here with the Lab

3.1. Preparation

3.2. Generate and edit the config file

3.3. Analyze Mode

3.4. Deploy Mode – Upgrade

3.5. AutoUpgrade Exercises

3.5. Plug into a CDB

3.6. Cleanup

Archives

Categories

Upgrade @YouTube

RSS Feed for Upgrade your Database – NOW!

Subscribe via Email

Follow me on Twitter

Recent Posts