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.3 and 11.2.0.4
• 12.1.0.1 and 12.1.0.2
• 12.2.0.1
Steps for upgrade from 12.1.0.2 to 18.3.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 18.0.0.0.0 ORACLE_HOME into the source 12.1.0.2 ORACLE_HOME.
SQL> @emremove.sql
old 70: IF (upper('&LOGGING') = 'VERBOSE')
new 70: IF (upper('VERBOSE') = 'VERBOSE')
PL/SQL procedure successfully completed.
Remove OLAP Catalog :-
[oracle@test admin]$ cd $ORACLE_HOME/olap/admin/ SQL> @catnoamd.sql
Start the 18c listener for dbupgrade :
[oracle@test preupgrade]$ export ORACLE_HOME=/u02/app/oracle/product/18.3.0/db_1 [oracle@test preupgrade]$ export PATH=/u02/app/oracle/product/18.3.0/db_1/bin:$PATH [oracle@test ]$ lsnrctl start LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 12-FEB-2019 17:42:09 Copyright (c) 1991, 2018, Oracle. All rights reserved. Starting /u02/app/oracle/product/18.3.0/db_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 18.0.0.0.0 - Production System parameter file is /u02/app/oracle/product/18.3.0/db_1/network/admin/listener.ora Log messages written to /u02/app/oracle/diag/tnslsnr/test/listener/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test.localdomain.com)(PORT=1521))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test.localdomain.com)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 18.0.0.0.0 - Production Start Date 12-FEB-2019 17:42:09 Uptime 0 days 0 hr. 0 min. 1 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u02/app/oracle/product/18.3.0/db_1/network/admin/listener.ora Listener Log File /u02/app/oracle/diag/tnslsnr/test/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test.localdomain.com)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) The listener supports no services The command completed successfully
Pre-upgrade checks :
Run the preupgrade tool.
The preupgrade.jar Pre-Upgrade Information Tool is supplied with Oracle Database 18c.
This tool has reside in new oracle home.
/u02/app/oracle/product/18.3.0/db_1/rdbms/admin/preupgrade.jar
[oracle@test ~]$ /u01/app/oracle/product/12.1.0/db_1/jdk/bin/java -jar /u02/app/oracle/product/18.3.0/db_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-02-12T23:36:18
Preupgrade fixups :
[oracle@test preupgrade]$ export ORACLE_SID=dbwr18c [oracle@test preupgrade]$ export ORACLE_HOME=u01/app/oracle/product/12.1.0/db_1 [oracle@test preupgrade]$ export PATH=u01/app/oracle/product/12.1.0/db_1/bin:$PATH SQL> @/u01/preupgrade/preupgrade_fixups.sql Executing Oracle PRE-Upgrade Fixup Script Auto-Generated by: Oracle Preupgrade Script Version: 18.0.0.0.0 Build: 1 Generated on: 2019-02-12 23:36:16 For Source Database: TEST18C Source Database Version: 12.1.0.2.0 For Upgrade to Version: 18.0.0.0.0 Preup Preupgrade Action Issue Is Number Preupgrade Check Name Remedied Further DBA Action ------ ------------------------ ---------- -------------------------------- 1. purge_recyclebin YES None. 2. apex_manual_upgrade NO Manual fixup recommended. 3. dictionary_stats YES None. 4. tablespaces_info 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.
Check INVALID objects count before upgrade :
SQL> select count(*) from dba_objects where status='INVALID'; COUNT(*) -------- 0
Upgrade Database from 12.1.0.2 to 18c using DBCA :-
DBUA performs some of the checks before actually starting the database upgrade. Some of the checks can be done manually to reduce downtime for the upgrade.
DBUA provides below options:
– Upgrade timezone.
– Gather dictionary statistics before upgrade.
– Make user tablespaces read only.
– Take RMAN backup before upgrade.
– Create Restore Point for Database Flashback
– Restore database backup to rollback upgrade
– Option to execute Custom scripts before and after upgrade
– show the location of DBUA logs and Alert log files.
– Option to upgrade existing listener to 18.x home or create a new listener in 18.x target home.
[oracle@test ~]$ export ORACLE_SID=dbwr18c [oracle@test~]$ export ORACLE_HOME=/u02/app/oracle/product/18.3.0/db_1/ [oracle@test ~]$ export PATH=/u02/app/oracle/product/18.3.0/db_1/bin/:$PATH [oracle@test ~]$ dbua
Select database name to upgrade to 18c version.
Preupgrade checks :
If you want to upgrade APEX version to 18.2,download APEX software 18.2 and run apexins.sql and upgrade it.
@apexins.sql APEX APEX TEMP /i/
After preupgrade warnings are resolved,select upgrade options to upgrade the database.
Select Recovery options to recover the database in case of issues.
Create listener or use existing listener which is running.
Select option if you want to configure EM express and port number.
Post Upgrade checks :
[oracle@test ~]$ cd /u01/preupgrade/ [oracle@test preupgrade]$ sqlplus / as sysdba SQL*Plus: Release 18.0.0.0.0 - Production on Wed Feb 13 01:56:58 2019 Version 18.3.2.0.0 Copyright (c) 1982, 2018, Oracle. All rights reserved. Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.3.2.0.0 SQL> @postupgrade_fixups.sql Session altered. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. Package created. No errors. Package body created. PL/SQL procedure successfully completed. No errors. Package created. No errors. Package body created. No errors. Executing Oracle POST-Upgrade Fixup Script Auto-Generated by: Oracle Preupgrade Script Version: 18.0.0.0.0 Build: 1 Generated on: 2019-02-12 23:36:17 For Source Database: TEST18C Source Database Version: 12.1.0.2.0 For Upgrade to Version: 18.0.0.0.0 Preup Preupgrade Action Issue Is Number Preupgrade Check Name Remedied Further DBA Action ------ --------------------- ------------ ----------------------------- 5. old_time_zones_exist YES None. 6. post_dictionary YES None. 7. post_fixed_objects NO Informational only.Further action is optional. The fixup scripts have been run and resovled 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 upgrade is not fully complete. To resolve the outstanding issues, start by reviewing the postupgrade_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. Session altered.
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 ----------- ------- ---------- 18.0.0.0.0 TEST18C READ WRITE
SQL> SELECT * FROM v$timezone_file; FILENAME VERSION CON_ID -------------------- ---------- ---------- timezlrg_31.dat 31 0 SQL> select TZ_VERSION from registry$database; TZ_VERSION ---------- 31
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
