This post is continuation of the fourth part of the post : Part IV
Following activities to be performed before going ahead with
upgrade
|
If invalid
objects exist, try to validate them by running utlrp.sql from the 12c SQL>@?/rdbms/admin/utlrp.sql Purge
Recyclebin. SQL>
PURGE DBA_RECYCLEBIN; For Best Practice, enable restore
point . We will take snapshot backup of after some time from oci console. To enable
restore point , set db_recovery_file_dest location. SQL>alter
system set db_recovery_file_dest_size=100G scope=both; SQL>alter
system set db_recovery_file_dest='/dbtest/archive' scope=both; SQL> create restore point pre_upgrade guarantee
flashback database; The
archivelog must be enabled before to create restore point. |
Running
the Pre-Upgrade Information Tool
|
To check
your system and database to see if it is ready for upgrade, we use the
Pre-Upgrade Information Tool (preupgrade.jar) [oraprod@non-prod-db
bin]$ /dbdata/erp/12.1.0/jdk/bin/java -jar
/dbdata/erp/19.3.0/rdbms/admin/preupgrade.jar TERMINAL TEXT SQL>
@/dbdata/erp/cfgtoollogs/EBS/preupgrade/preupgrade_fixups.sql Executing Oracle PRE-Upgrade Fixup Script Auto-Generated by: Oracle Preupgrade Script Version: 19.0.0.0.0
Build: 13 Generated on: 2025-06-02 10:09:54 For Source Database: EBS Source Database Version: 12.1.0.2.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_obsolete NO Manual fixup recommended. 2. invalid_objects_exist NO Manual fixup recommended. 3. amd_exists NO Manual fixup recommended. 4. duplic_sys_system_objs NO Manual fixup recommended. 5. exclusive_mode_auth NO Manual fixup recommended. 6. case_insensitive_auth NO Manual fixup recommended. 7. mv_refresh NO Manual fixup recommended. 8. hidden_params NO Informational only.
Further action is optional. 9. underscore_events NO Informational only.
Further action is optional. 10. dictionary_stats YES None. 11. component_info NO Informational only.
Further action is optional. 12. parameter_deprecated NO Informational only.
Further action is optional. 13. min_archive_dest_size NO Informational only.
Further action is optional. 14. rman_recovery_version NO Informational only.
Further action is optional. 15. invalid_ora_obj_info NO Informational only.
Further action is optional. 16. invalid_app_obj_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. |
Following actions to be taken as per preupgrade_fixup.log
|
1. Remove following parameters from
pfile and create spfile out of it and bounce the database optimizer_adaptive_features O7_DICTIONARY_ACCESSIBILITY utl_file_dir SQL>shu immediate SQL>create pfile from spfile; Comment out above 3 parameters from
pfile. SQL>create spfile from pfile; SQL>startup 1. Resolve all the invalid objects by
running utlrp.sql In our
case following objects were invalid which were not part of sys or system
user.So we didn’t take any action on these.
2. Remove OLAP Catalog by running the
12.1.0.2.0 SQL script $ORACLE_HOME/olap/admin/catnoamd.sql script.
SQL>@$ORACLE_HOME/olap/admin/catnoamd.sql 3. Remove duplicate objects SQL>DROP
PACKAGE SYSTEM."WPG_DOCLOAD";
SQL> DROP INDEX
SYSTEM.HELP_TOPIC_SEQ; SQL> DROP TABLE SYSTEM.HELP CASCADE CONSTRAINTS
PURGE; 4. set
SQLNET.ALLOWED_LOGON_VERSION_SERVER in the 19c SQLNET.ORA
to a non-Exclusive Mode value, such as "10". Add
following in 19c sqlnet.ora SQLNET.ALLOWED_LOGON_VERSION_SERVER=10 5. Remove the SEC_CASE_SENSITIVE_LOGON
instance initialization parameter setting, SQL>
alter system set sec_case_sensitive_logon=false scope=both; 6. Please make sure that all the MVs are
refreshed and sys.sumdelta$ becomes empty before doing
upgrade. Run the following to refresh all the MV’s SQL> declare num_failures integer(3) :=0; begin DBMS_MVIEW.REFRESH_ALL_MVIEWS(num_failures,'C','', TRUE, FALSE); end; /
SQL> select count(1) from sumdelta$; COUNT(1) ---------- 0
Make sure sumdelta$ returns 0 rows. 7.
Remove
hidden parameters before database upgrade unless your application vendors and/or Oracle
Support state differently. Changes
will need to be made in the
pfile/spfile. We didn’t remove any hidden/under score parameter. 8. Review and remove any unnecessary
EVENTS. SQL>
show parameter event NAME TYPE VALUE ------------------------------------
----------- ------------------------------ event string 10946 trace name context forever, level
8454144
This event was
added earlier as mentioned in the Doc ID
2580629.1 |
Now rerun the preupgrade_fix.sql again and it should reflect
the changes done above.
SQL>
@/dbdata/erp/cfgtoollogs/EBS/preupgrade/preupgrade_fixups.sql
Start 12c listener
|
[oraprod@non-prod-db ~]$ lsnrctl
start EBS LSNRCTL for Linux: Version 12.1.0.2.0
- Production on 02-JUN-2025 18:38:22 Copyright (c) 1991, 2014,
Oracle. All rights reserved. Starting
/dbtest/dbdata/erp/12.1.0/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 12.1.0.2.0
- Production System parameter file is
/dbtest/dbdata/erp/12.1.0/network/admin/TEST_non-prod-db/listener.ora Log messages written to
/dbtest/dbdata/erp/12.1.0/admin/TEST_non-prod-db/diag/tnslsnr/non-prod-db/TEST/alert/log.xml Listening on:
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=non-prod-db.soumya.com)(PORT=1541))) Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=non-prod-db.sub09181217051.selvcndc.oraclevcn.com)(PORT=1541))) STATUS of the LISTENER ------------------------ Alias TEST Version TNSLSNR for Linux: Version
12.1.0.2.0 - Production Start Date 02-JUN-2025 18:38:22 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File
/dbtest/dbdata/erp/12.1.0/network/admin/TEST_non-prod-db/listener.ora Listener Log File
/dbtest/dbdata/erp/12.1.0/admin/TEST_non-prod-db/diag/tnslsnr/non-prod-db/TEST/alert/log.xml Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=non-prod-db.soumya.com)(PORT=1541))) Services Summary... Service "TEST" has 1
instance(s).
Instance "TEST", status UNKNOWN, has 1 handler(s) for this
service... The command completed successfully |
Run the DBUA and
start upgrade.
Please note the source database should be up and running.
Invoke 19c environment. Run dbua from 19c home.
In case your environment is on premise, you must take backup using either RMAN or you can create restore point before starting upgrade.
|
[oraprod@non-prod-db ~]$ 19cdb_env [oraprod@non-prod-db ~]$ which dbua /dbdata/erp/19.3.0/bin/dbua [ebsTEST@ccuine105
~]$ export _JAVA_OPTIONS='-Dsun.java2d.xrender=false' [ebsTEST@ccuine105 ~]$
$ORACLE_HOME/bin/dbua -keepEvents |
select the source database, and provide password for sys user,then click the “Next” button.
The warnings are those points which were reported earlier by
preupgrade.log. It can be safely ignored as we already have taken care of these
earlier.
Choose the
option shown below and click on next
We choose rman backup as a fallback strategy. However I
created one restore point before upgrade , we can use that as well.
The 12c listener should be up and running and “migrate”
status should be shown as yes.
No comments:
Post a Comment