DEV Community

loading...
Cover image for Upgrade a Single Instance 11g Database to 19c as a PDB with Autoupgrade

Upgrade a Single Instance 11g Database to 19c as a PDB with Autoupgrade

project42 profile image Project-42 ・7 min read

I already mentioned that Autoupgrade allows now to be used in RAC Databases, but something that was also added recently (Autoupgrade 19.9.0 if I'm not mistaken) is the capacity to upgrade a Non-CDB database and plug-in it into a higher version Multitenant Database you already have in the system.

You can see the needed configuration file parameters needed:

Database Upgrade Guide

I tried couple of times to combine both things and upgrade a 11g RAC system into a 19c CDB as a PDB... but I failed... few times actually.

I thought I had something wrong in my system (happen more often than I would like to admit..) but I got "good news" from Daniel who made me realize my issue was not my system, but the fact that I didn't read the Document correctly (yes... happens to me too often)

https://twitter.com/dohdatabase/status/1300296828643049472?ref_src=twsrc%5Etfw

https://support.us.oracle.com/oip/faces/secure/km/DocumentDisplay.jspx?id=2485457.1
https://support.us.oracle.com/oip/faces/secure/km/DocumentDisplay.jspx?id=2485457.1

Yes, is clear in the document :)

So, for now, and until we get the new Autoupgrade version with the option to do the same in RAC systems (I'm sure won't be long) lets see how easy is to Upgrade single instance 11g Database to 19c as a PDB using Autoupgrade.

First, create the 11g Database are going to upgrade and make sure is set in archive mode

[oracle@rac1-node1 ~]$ /u01/app/oracle/product/11.2.0/dbhome_1/bin/dbca -silent -createDatabase \
> -templateName General_Purpose.dbc \
> -gdbname u11 -sid u11 -responseFile NO_VALUE \
> -characterSet AL32UTF8 \
> -sysPassword Welcome1 \
> -systemPassword Welcome1 \
> -databaseType MULTIPURPOSE \
> -totalMemory 1536 \
> -storageType FS \
> -datafileDestination "/u01/app/oracle/oradata/" \
> -redoLogFileSize 150 \
> -emConfiguration NONE 

Copying database files
1% complete
3% complete
[....]
96% complete
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/u11/u11.log" for further details.
[oracle@rac1-node1 ~]$
[oracle@rac1-node1 ~]$ srvctl stop database -d u11 ; srvctl start database -d u11 -o mount
[oracle@rac1-node1 ~]$ sqlplus / as sysdba

SQL> ALTER SYSTEM set db_recovery_file_dest_size=10G scope=both sid='*' ;

System altered.

SQL> ALTER SYSTEM SET db_recovery_file_dest='/u01/oradata/nfs1/fra' sid='*';

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1 = 'LOCATION=USE_DB_RECOVERY_FILE_DEST' scope=BOTH;

System altered.

SQL> ALTER DATABASE ARCHIVELOG;

Database altered.

SQL> 

Create the 19c Multitenant (CDB) Destination

[oracle@rac1-node1 ~]$ /u01/app/oracle/product/19.3.0/dbhome_1/bin/dbca -silent -createDatabase \
> -templateName General_Purpose.dbc \
> -gdbname u19 \
> -sid u19 \
> -createAsContainerDatabase true \
> -numberOfPdbs 1 \
> -pdbName pdbu19 \
> -responseFile NO_VALUE \
> -pdbAdminPassword Welcome1 \
> -SysPassword Welcome1 \
> -SystemPassword Welcome1 \
> -databaseType MULTIPURPOSE \
> -storageType FS \
> -datafileDestination "/u01/app/oracle/oradata/" \
> -redoLogFileSize 150 \
> -emConfiguration NONE \
> -characterSet AL32UTF8 \
> -nationalCharacterSet AL16UTF16 \
> -sampleschema false \
> -ignorePreReqs \
> -dbOptions JSERVER:true,ORACLE_TEXT:false,IMEDIA:false,CWMLITE:false,SPATIAL:false,OMS:false,APEX:false,DV:false
[WARNING] [DBT-10331] Specified SID Name (u19) may have a potential conflict with an already existing database on the system.
   CAUSE: The specified SID Name without the trailing numeric characters (u) may have a potential conflict with an already existing database on the system.
   ACTION: Specify a different SID Name that does not conflict with existing databases on the system.
Prepare for db operation
[....]
Executing Post Configuration Actions
100% complete
Database creation complete. For details check the logfiles at:
 /u01/app/oracle/cfgtoollogs/dbca/u19.
Database Information:
Global Database Name:u19
System Identifier(SID):u19
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/u19/u19.log" for further details.
[oracle@rac1-node1 ~]$ 

Create a config file with the mentioned options in the Database Upgrade Documentation.
Basically, what we are setting is the 11g source Database, the 19c CDB target and the name of the 11g Database as PDB inside the 19c CDB

[oracle@rac1-node1 ~]$ cat /home/oracle/AutoUpgrade/u11_to_u19_as_PDB.cfg
#
# sample config file
#
# build version 19.9.0
# build date    2020/04/23 15:01:36
#
# Database number 1
#
global.autoupg_log_dir=/home/oracle/AutoUpgrade/glogs_u11
upg1.log_dir=/home/oracle/AutoUpgrade/upg_logs/u11

upg1.upgrade_node=localhost
upg1.source_home=/u01/app/oracle/product/11.2.0/dbhome_1
upg1.target_home=/u01/app/oracle/product/19.3.0/dbhome_1
upg1.dbname=u11
upg1.sid=u11
upg1.target_version=19.3.0
upg1.target_cdb=u19
upg1.target_pdb_name=pdbu11
upg1.start_time=NOW
upg1.timezone_upg=no
[oracle@rac1-node1 ~]$ 

Analyze and check for errors just in case

[oracle@rac1-node1 ~]$ java -jar /home/oracle/AutoUpgrade/autoupgrade.jar -config /home/oracle/AutoUpgrade/u11_to_u19_as_PDB.cfg -mode analyze
AutoUpgrade tool launched with default options
Processing config file ...
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
1 databases will be analyzed
Type 'help' to list console commands
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 u11

[oracle@rac1-node1 ~]$ 

[oracle@rac1-node1 ~]$ cd /home/oracle/AutoUpgrade/upg_logs/u11/u11/100/prechecks/
[oracle@rac1-node1 prechecks]$ ll
total 248
-rwx------ 1 oracle oinstall 173597 Aug 28 17:10 prechecks_u11.log
-rwx------ 1 oracle oinstall   3177 Aug 28 17:11 u11_checklist.cfg
-rwx------ 1 oracle oinstall  12685 Aug 28 17:11 u11_checklist.json
-rwx------ 1 oracle oinstall  12080 Aug 28 17:11 u11_checklist.xml
-rwx------ 1 oracle oinstall  28847 Aug 28 17:11 u11_preupgrade.html
-rwx------ 1 oracle oinstall  12265 Aug 28 17:11 u11_preupgrade.log
[oracle@rac1-node1 prechecks]$ grep -i error u11_preupgrade.log
[oracle@rac1-node1 prechecks]$

And lets Upgrade the Database.
Since my system is not the fasted, I decided to use the noconsole option and add nohup, so I avoid any issues if my session gets disconnected

[oracle@rac1-node1 ~]$ nohup java -jar /home/oracle/AutoUpgrade/autoupgrade.jar -config /home/oracle/AutoUpgrade/u11_to_u19_as_PDB.cfg -mode deploy -noconsole &
[1] 867
[oracle@rac1-node1 ~]$ nohup: ignoring input and appending output to nohup.out

[oracle@rac1-node1 ~]$ 

and voila!, we have now a our old 11g Database as PDB inside the 19c Multitenant Database without any further interaction needed from our side

[oracle@rac1-node1 ~]$ . oraenv
ORACLE_SID = [u11] ? u19
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rac1-node1 ~]$ sqlplus / as sysdba

SQL> show pdbs

    CON_ID CON_NAME           OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
     2 PDB$SEED           READ ONLY  NO
     3 PDBU19             READ WRITE NO
     4 PDBU11             READ WRITE NO
SQL> 

If we check the Log, we can see all the progress completed

[oracle@rac1-node1 ~]$ tail -400 /home/oracle/AutoUpgrade/upg_logs/u11/u11/101/autoupgrade_20200828_user.log
2020-08-28 17:17:11.783 INFO 
build.hash:255dd7d
build.version:19.9.0
build.date:2020/04/23 15:01:36
build.max_target_version:19
build.supported_target_versions:12.2,18,19
build.type:production
build.label:HEAD

2020-08-28 17:17:19.820 INFO Analyzing u11, 82 checks will run using 16 threads 
2020-08-28 17:17:26.808 INFO Using /home/oracle/AutoUpgrade/upg_logs/u11/u11/101/prechecks/u11_checklist.cfg to identify required fixups 
2020-08-28 17:17:26.844 INFO Adding fixup PURGE_RECYCLEBIN PRECHECKS YES true ERROR to execution queue of u11 
2020-08-28 17:19:35.516 INFO Updating parameter *.processes=150 to *.processes=330 in /home/oracle/AutoUpgrade/upg_logs/u11/u11/temp/during_upgrade_pfile_u11.ora 
2020-08-28 17:19:35.517 INFO Updating parameter *.processes=150 to *.processes=330 in /home/oracle/AutoUpgrade/upg_logs/u11/u11/temp/after_upgrade_pfile_u11.ora 
2020-08-28 17:19:35.519 INFO Adding parameter cluster_database='FALSE' to /home/oracle/AutoUpgrade/upg_logs/u11/u11/temp/during_upgrade_pfile_u11.ora 
2020-08-28 17:19:52.466 INFO Analyzing u11, 82 checks will run using 16 threads 
2020-08-28 17:20:10.588 INFO Copying password file from /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwu11 to /u01/app/oracle/product/19.3.0/dbhome_1/dbs/orapwu11 
2020-08-28 17:20:10.591 INFO Copying password file completed with success 
2020-08-28 17:20:41.208 INFO Total Number of upgrade phases is 108 
2020-08-28 17:20:41.220 INFO Begin Upgrade on Database [u11] 
2020-08-28 17:20:49.415 INFO [Upgrading] is [0%] completed for [u11] 
+---------+------------+
|CONTAINER|  PERCENTAGE|
+---------+------------+
|      u11|UPGRADE [0%]|
+---------+------------+ 

[.......]

+---------+-------------+
|CONTAINER|   PERCENTAGE|
+---------+-------------+
|      u11|UPGRADE [93%]|
+---------+-------------+ 
2020-08-28 18:13:02.308 INFO SUCCESSFULLY UPGRADED [u11] 
2020-08-28 18:13:02.308 INFO End Upgrade on Database [u11] 
2020-08-28 18:13:26.955 INFO [Compiling] is [0%] completed for [u11] objects remaining is [8716]
+---------+------------+
|CONTAINER|  PERCENTAGE|
+---------+------------+
|      u11|COMPILE [0%]|
+---------+------------+ 
2020-08-28 18:13:29.635 INFO Begin Compiling Invalid Objects on Database [u11] 
2020-08-28 18:16:29.705 INFO End Compiling Invalid Objects on Database [u11] 
2020-08-28 18:16:29.708 INFO SUCCESSFULLY COMPILED [u11] 
2020-08-28 18:16:30.131 INFO [Compiling] is [0%] completed for [u11] objects remaining is [0]
+---------+------------+
|CONTAINER|  PERCENTAGE|
+---------+------------+
|      u11|COMPILE [0%]|
+---------+------------+ 
2020-08-28 18:16:30.158 INFO [Upgrading] is [100%] completed for [u11] 
+---------+---------------------------+
|CONTAINER|                 PERCENTAGE|
+---------+---------------------------+
|      u11|SUCCESSFULLY UPGRADED [u11]|
+---------+---------------------------+ 
2020-08-28 18:16:30.173 INFO Error opening file [/u01/app/oracle/product/19.3.0/dbhome_1/dbs/initu11.ora] for reading 
2020-08-28 18:16:31.221 INFO Creating spfile completed with success 
2020-08-28 18:16:31.222 INFO SUCCESSFULLY UPGRADED [u11] 
2020-08-28 18:16:31.268 INFO u11 Return status is SUCCESS 
2020-08-28 18:17:05.572 INFO Analyzing u11, 11 checks will run using 11 threads 
2020-08-28 18:17:11.561 INFO Using /home/oracle/AutoUpgrade/upg_logs/u11/u11/101/prechecks/u11_checklist.cfg to identify required fixups 
2020-08-28 18:17:11.568 WARNING The fixup for OLD_TIME_ZONES_EXIST will not be executed on database u11 as requested. Failure to upgrade your timezone file can lead to erroreous query results. 
2020-08-28 18:22:13.955 INFO Analyzing u11, 11 checks will run using 11 threads 
2020-08-28 18:22:16.186 INFO Removing entry for database u11 from oratab file /etc/oratab completed with success 
2020-08-28 18:22:16.191 INFO Return status is SUCCESS 
2020-08-28 18:22:16.198 INFO Update of oratab [u11]
    [/etc/oratab] [PENDING] [None]


2020-08-28 18:22:19.353 INFO Guarantee Restore Point (GRP) successfully removed [U11][AUTOUPGRADE_9212_U11112040] 
2020-08-28 18:24:50.785 INFO No entry was found for [u11:/u01/app/oracle/product/19.3.0/dbhome_1] in /etc/oratab 
[oracle@rac1-node1 ~]$ 

As always, if you are thinking about Upgrade your system, please have a look first to Mike Dietrich's blog. You will find there full guides to understand the whole process and even possible issues you can find in the process

Discussion

pic
Editor guide