Description:-
There are different ways of upgrading to the latest release of Oracle database and Oracle provides multiple methods to upgrade. Few are listed below:
- Database Upgrade Assistant (DBUA)
- Manual Upgrade
- Transportable Tablespaces
- Datapump export/import
- Oracle Streams
- Oracle GoldenGate
Here I am going to choose DBUA to upgrade my database,
DBUA provides a graphical user interface to guide you through the upgrade of Oracle Database.
Oracle Database Releases That Support Direct Upgrade
Review the supported options for direct upgrades to the latest Oracle Database release.
You can perform a direct upgrade to the new release from the following releases:
• 11.2.0.4
• 12.1.0.1 and 12.1.0.2
• 12.2.0.1
Steps for upgrade from 11.2.0.4 to 19.2.0.0 using DBUA :-
Presteps for db upgrade :-
Take RMAN full backups before upgrade.
rman target /
run {
allocate channel test_backup_disk1 type disk format '/u01/backup/%d_%U';
backup incremental level 0 tag 'ORCL_BEFORE_UPG' database;
backup format tag 'ORCL_CONTROL_FILE' current controlfile;
release channel test_backup_disk1;
}
Ensure backup is complete before upgrade.
SQL> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE'; no rows selected
Empty Recycle bin.
SQL> PURGE DBA_RECYCLEBIN ; DBA Recyclebin purged.
Run Gather statistics to finish upgrade soon.
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS; PL/SQL procedure successfully completed.
Remove EM Database Control :-
Copy the $ORACLE_HOME/rdbms/admin/emremove.sql script from the target 19.2.0.0.0 ORACLE_HOME into the source 11.2.0.4.0 ORACLE_HOME.
[oracle@19c ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 14 20:56:29 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @emremove.sql
old 70: IF (upper('&LOGGING') = 'VERBOSE')
new 70: IF (upper('VERBOSE') = 'VERBOSE')
PL/SQL procedure successfully completed.
Remove OLAP Catalog :-
[oracle@19c ~]$ cd $ORACLE_HOME/olap/admin/ SQL> @catnoamd.sql
Purge Recyclebin :-
SQL> purge recyclebin; Recyclebin purged.
Run the preupgrade tool.
The preupgrade.jar Pre-Upgrade Information Tool is supplied with Oracle Database 19c.
This tool has reside in new oracle home.
/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar
oracle@19c ~]$ /u01/app/oracle/product/11.2.0/dbhome_1/jdk/bin/java -jar /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar FILE DIR /u01/preupgrade/ ================== PREUPGRADE SUMMARY ================== /u01/preupgrade/preupgrade.log /u01/preupgrade/preupgrade_fixups.sql /u01/preupgrade/postupgrade_fixups.sql Execute fixup scripts as indicated below: Before upgrade: Log into the database and execute the preupgrade fixups @/u01/preupgrade/preupgrade_fixups.sql After the upgrade: Log into the database and execute the postupgrade fixups @/u01/preupgrade/postupgrade_fixups.sql Preupgrade complete: 2019-03-14T21:28:55
Run preupgrade fixups.sql
SQL> @/u01/preupgrade/preupgrade_fixups.sql Executing Oracle PRE-Upgrade Fixup Script Auto-Generated by: Oracle Preupgrade Script Version: 19.0.0.0.0 Build: 1 Generated on: 2019-03-14 21:28:51 For Source Database: UPG19C Source Database Version: 11.2.0.4.0 For Upgrade to Version: 19.0.0.0.0 Preup Preupgrade Action Issue Is Number Preupgrade Check Name Remedied Further DBA Action ------ ------------------------ ---------- -------------------------------- 1. parameter_min_val NO Manual fixup recommended. 2. em_present NO Manual fixup recommended. 3. amd_exists NO Manual fixup recommended. 4. apex_manual_upgrade NO Manual fixup recommended. 5. dictionary_stats YES None. 6. trgowner_no_admndbtrg YES None. 7. mv_refresh NO Informational only. Further action is optional. 8. pre_fixed_objects YES None. 9. tablespaces_info NO Informational only. Further action is optional. 10. exf_rul_exists NO Informational only. Further action is optional. 11. rman_recovery_version NO Informational only. Further action is optional. The fixup scripts have been run and resolved what they can. However, there are still issues originally identified by the preupgrade that have not been remedied and are still present in the database. Depending on the severity of the specific issue, and the nature of the issue itself, that could mean that your database is not ready for upgrade. To resolve the outstanding issues, start by reviewing the preupgrade_fixups.sql and searching it for the name of the failed CHECK NAME or Preupgrade Action Number listed above. There you will find the original corresponding diagnostic message from the preupgrade which explains in more detail what still needs to be done. PL/SQL procedure successfully completed.
Run DBUA upgrade Utility :
Now run the dbua utility from 19c oracle_home location.
[oracle@19c ~]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@19c ~]$ export ORACLE_SID=upg19c
[oracle@19c ~]$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
[oracle@19c ~]$ dbua
- select the Upgrade Oracle Database option, then click the “Next” button.
- If the prerequisite checks highlight any issues, take the appropriate action to fix the issues. If all the prerequisite checks are passed, click the “Next” button.
- Amend the upgrade options if necessary, then click the “Next” button.
- Select the recovery options for use in the event of an upgrade failure, then click the “Next” button.
- If the database is using the 11g listener and you need to upgraded, use existing listener or create new 19c listener
- If you want configure EM database express enable it and provide port number or uncheck it.
Now the database upgrade is complete and the database is ready for normal use.
SQL> select VERSION,name,open_mode from v$instance,v$database; VERSION NAME OPEN_MODE --------- ------ ---------- 19.0.0.0.0 UPG19C READ WRITE SQL> SELECT * FROM v$timezone_file; FILENAME VERSION CON_ID -------------------- ---------- ---------- timezlrg_32.dat 32 0 SQL> select TZ_VERSION from registry$database; TZ_VERSION ---------- 32
Catch Me On:- Hariprasath Rajaram Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg LinkedIn:https://www.linkedin.com/in/hariprasathdba Facebook:https://www.facebook.com/HariPrasathdba FB Group:https://www.facebook.com/groups/894402327369506/ FB Page: https://www.facebook.com/dbahariprasath/? Twitter: https://twitter.com/hariprasathdba
