Since 11g is not longer supported, there are high chances people are looking for different and easy ways to upgrade their old Databases to a supported version.
If that is the case, there is no easiest method than Autoupgrade.
Now, with the latest version (at the time of writing this is version 21.1.2 / date 2021/02/24) you can just migrate your old 11g RAC Database to your newer CDB as a PDB.

If you don’t know much about Autoupgrade, you can check the other articles I have about it, or even better, go to the sources and sorceress that are Mike and Daniel where you can find the latest information about it, and more importantly, slides and webinars to help you in your task.

Now, lets test this out to show you how simple it is.

Let’s create a 11g Database that we are going to upgrade.
First requirement for Autoupgrade, is to make sure is set in archive mode and we have enough FRA. The reason for this is because creates Guarantee Restore Points for the cases where we need to rollback the operation

[oracle@rac1-node1 ~]$ /u01/app/oracle/product/11.2.0/dbhome_1/bin/dbca \
> -silent \
> -createDatabase \
> -templateName General_Purpose.dbc \
> -gdbName upg11  \
> -sid upg11  \
> -SysPassword Welcome1 \
> -SystemPassword Welcome1 \
> -emConfiguration NONE \
> -storageType ASM \
> -redoLogFileSize 50  \
> -recoveryAreaDestination RECO \
> -diskGroupName DATA \
> -characterSet AL32UTF8 \
> -nationalCharacterSet AL32UTF8 \
> -automaticMemoryManagement true \
> -memoryPercentage 20 \
> -databaseType MULTIPURPOSE \
> -nodelist rac1-node1,rac1-node2
Copying database files
1% complete
3% complete
9% complete
[....]

94% complete
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/upg11/upg11.log" for further details.
[oracle@rac1-node1 ~]$

[oracle@rac1-node1 ~]$ srvctl stop database -d upg11 ; srvctl start database -d upg11 -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='+RECO' 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> 
[oracle@rac1-node1 ~]$ srvctl stop database -d upg11 ; srvctl start database -d upg11
[oracle@rac1-node1 ~]$

Create now the 19c CDB that we will use as destination/target

[oracle@rac1-node1 ~]$ dbca -silent -createDatabase \
> -templateName General_Purpose.dbc \
> -gdbname upg19 \
> -sid upg19 \
> -createAsContainerDatabase true \
> -numberOfPdbs 1 \
> -pdbName pdbupg19 \
> -responseFile NO_VALUE \
> -pdbAdminPassword Welcome1 \
> -SysPassword Welcome1 \
> -SystemPassword Welcome1 \
> -databaseType MULTIPURPOSE \
> -storageType ASM \
> -diskGroupName DATA \
> -redoLogFileSize 50 \
> -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 \
> -nodelist rac1-node1,rac1-node2
[WARNING] [DBT-10331] Specified SID Prefix (upg19) may have a potential conflict with an already existing database on the system.
   CAUSE: The specified SID Prefix without the trailing numeric characters (upg) may have a potential conflict with an already existing database on the system.
   ACTION: Specify a different SID Prefix that does not conflict with existing databases on the system.
Prepare for db operation
7% complete
[...]
Executing Post Configuration Actions
100% complete
Database creation complete. For details check the logfiles at:
 /u01/app/oracle/cfgtoollogs/dbca/upg19.
Database Information:
Global Database Name:upg19
System Identifier(SID) Prefix:upg19
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/upg19/upg191.log" for further details.
[oracle@rac1-node1 ~]$

Create a configuration file but before that, remember to always check that you have the latest version.
Every upgrade so far has been getting rid of different errors and adding new features, so always double check against AutoUpgrade Tool (Doc ID 2485457.1)

[oracle@rac1-node1 AutoUpgrade]$ java -jar /home/oracle/AutoUpgrade/autoupgrade.jar -version
build.hash 59fbf3e
build.version 21.1.2
build.date 2021/02/24 17:11:08
build.max_target_version 21
build.supported_target_versions 12.2,18,19,21
build.type production

[oracle@rac1-node1 AutoUpgrade]$

Create the config file making sure has all required options as per Documentation Understanding Non-CDB to PDB Upgrades with AutoUpgrade

You can even ignore some of the options like the "Target PDB name" (will source DB name as PDB name directly), or "Start Time"

[oracle@rac1-node1 AutoUpgrade]$ cat upg11_to_upg19_as_PDB.cfg
#
# Sample config file for AutoUpgrade
#
# build version 21.1.2
# build date    2021/02/24 17:11:08
#
global.autoupg_log_dir=/home/oracle/AutoUpgrade/autoupg
upg1.log_dir=/home/oracle/AutoUpgrade/upg_logs/upg11

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=upg11
upg1.sid=upg111
upg1.target_version=19.3.0
upg1.target_cdb=upg191
upg1.target_pdb_name=pdbupg11
upg1.start_time=NOW
upg1.timezone_upg=no
[oracle@rac1-node1 AutoUpgrade]$

Let’s analyse the upgrade and check for errors in the precheck logs 


[oracle@rac1-node1 ~]$ . oraenv
ORACLE_SID = [upg191] ? upg111
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/11.2.0/dbhome_1
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rac1-node1 ~]$ 

[oracle@rac1-node1 ~]$ java -jar /home/oracle/AutoUpgrade/autoupgrade.jar -config /home/oracle/AutoUpgrade/upg11_to_upg19_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 upg111

[oracle@rac1-node1 ~]$ 

[oracle@rac1-node1 ~]$ grep -i error /home/oracle/AutoUpgrade/upg_logs/upg11/upg111/100/prechecks/upg11_preupgrade.log 
[oracle@rac1-node1 ~]$

Since the upgrade can take long time to complete, I will use "-noconsole" option and "nohup" to avoid any interruption of the process

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

[oracle@rac1-node1 AutoUpgrade]$ 

[oracle@rac1-node1 AutoUpgrade]$ date
Thu Mar 11 17:15:56 GMT 2021

We can followup the progress from the log directory

[oracle@rac1-node1 AutoUpgrade]$ tail -150f /home/oracle/AutoUpgrade/upg_logs/upg11/upg111/101/autoupgrade_20210311.log
[....]
twork/admin), (LANG=en_US.UTF-8), (LDAP_ADMIN=N/A), (PERL5LIB=N/A)] - ExecutionEnv.addEnvToProcess 
2021-03-11 17:16:12.904 INFO Starting - ExecuteProcess.setLibsForSqlplus 
2021-03-11 17:16:12.905 INFO Finished - ExecuteProcess.setLibsForSqlplus 
2021-03-11 17:16:12.905 INFO End Setting Oracle Environment - ExecuteProcess.startSqlPlusProcess 
2021-03-11 17:16:12.905 INFO Begin Creating process - ExecuteProcess.startSqlPlusProcess 
2021-03-11 17:16:12.906 INFO End Creating process - ExecuteProcess.startSqlPlusProcess 
2021-03-11 17:16:12.906 INFO Executing SQL [CREATE RESTORE POINT AUTOUPGRADE_9212_UPG11112040 GUARANTEE FLASHBACK DATABASE;] in [upg111, container:null] - ExecuteSql$SQLClient.run

And voila! we got our old 11g database as a PDB in our 19c Container:

-- Autoupgrade Logs

2021-03-11 21:29:57.019 INFO SUCCESSFULLY COMPILED [upg11] 
2021-03-11 21:33:17.314 INFO Analyzing upg111, 14 checks will run using 14 threads 
2021-03-11 21:33:18.699 INFO Return status is SUCCESS 
2021-03-11 21:33:18.702 INFO Database State
  Resetting the databases state: [SUCCESS] [None]

2021-03-11 21:33:18.703 INFO No postupgrade user action defined 
2021-03-11 21:36:15.115 INFO No entry was found for [upg111:/u01/app/oracle/product/11.2.0/dbhome_1] in /etc/oratab 
2021-03-11 21:36:15.119 INFO Removing entry for database upg11 from oratab file /etc/oratab completed with success 
2021-03-11 22:09:46.254 INFO The pdb(s) created in database upg111 were successfully restarted 
2021-03-11 22:09:48.421 INFO Checking if we need to remove the Windows Service created by AutoUpgrade 
2021-03-11 22:09:48.422 INFO Not Windows, skipping further actions 
2021-03-11 22:09:48.422 INFO After The upgrade AUtoUpgrade will re-enable RAC on the database 
2021-03-11 22:09:48.423 INFO Not RAC, skipping further actions 

-- Console information:

Type 'help' to list console commands
upg> ------------------- Final Summary --------------------
Number of databases            [ 1 ]

Jobs finished                  [1]
Jobs failed                    [0]
Jobs pending                   [0]

Please check the summary report at: 
/home/oracle/AutoUpgrade/autoupg/cfgtoollogs/upgrade/auto/status/status.html
/home/oracle/AutoUpgrade/autoupg/cfgtoollogs/upgrade/auto/status/status.log
[oracle@rac1-node1 ~]$ 

-- PDB mounted since I dont have services running for it

DB_NAME INSTANCE_NAME  CDB  HOST_NAME
------  -------------- ---- -------------------------
UPG19   upg192         YES  rac1-node2.raclab.local
UPG19   upg191         YES  rac1-node1.raclab.local

SQL> SQL> SQL>   2    3    4  
INST_ID     CON_ID     NAME           OPEN_MODE 
---------- ---------- --------------- ------------
   1        2          PDB$SEED       READ ONLY
   2        2          PDB$SEED       READ ONLY
   1        3          PDBUPG11       MOUNTED 
   2        3          PDBUPG11       MOUNTED 

SQL>  

SQL> alter pluggable database all open instances=all;

Pluggable database altered.

SQL> 

   INST_ID     CON_ID NAME       OPEN_MODE  OPEN_TIME              STATUS
---------- ---------- -------------------- ---------- ---------------------------------------- ----------
   1      2 PDB$SEED       READ ONLY  15-MAR-21 10.18.30.477 AM +00:00         NORMAL
   2      2 PDB$SEED       READ ONLY  15-MAR-21 10.19.33.689 AM +00:00         NORMAL
   1      3 PDBUPG11       READ WRITE 15-MAR-21 11.33.12.405 AM +00:00         NORMAL
   2      3 PDBUPG11       READ WRITE 15-MAR-21 11.34.26.508 AM +00:00         NORMAL

This is of course a simple test scenario, but I really recommend you to start digging into Mike Dietrich’s website and recent Youtube Videos, specially if you are one of those people that still think Upgrade Oracle Database are just way to difficult to do, and remember to always "try this at home!!"

Also, check the Documentation, can be really useful 😛

Lets summarise steps done for this test:

  • Create a new 11.2 demo Database
  • Create Autoupgrade configuration file to upgrade to 19.3
  • Analyse the system for Upgrade in search for any errors
  • Set the system in archive mode
  • Deploy the Upgrade
  • Enjoy a good and deserved Company Bonus!
Last modified: 6 April 2021

Author

Comments

Write a Reply or Comment

Your email address will not be published.