Skip to Content
Technical Articles

Oracle Upgrade at a glance;

                            10g,11g db UPGRADE TO ORACLE 12C DATABASE(SAP)

============================================================================

https://wrongcodes.blogspot.com/2019/01/oracle-10g11g-db-upgrade-to-oracle-12c.html

  • Login Using SID ADM.
  • Create a new windows user for oracle software installation with power roles,
  • Install Oracle 12c software, when it prompts for the user, provide the user created before.
  • Change the Home directory path in Environment variable( ORACLE_HOME_old), and map to new home to (ORACLE_HOME) value = new oracle home) (before patching)
  • Rename ORACLE_HOME into ORACLE_HOME_before_upgrade, and TNS_ADMIN to TNS_ADMIN_before_upgrade.
  • Add Administrator permission to installation user.(before patching)
  • Take copy of new oralce (optional) (before patching)
  • Take copy of oracle\network\admin.
  • Take copy of oracle\database
  • Shutdown the database (before patching) stop all services related to oracle
  • Download Latest bundle patch from below path ( Apply bundle patch using latest Opatch version)
  • http://support.sap.com/software/databases.html >>OracleORACLE >>PATCHESORACLE >>PATCHES >>12.1.0.2.
  • Apply all required patches.
  • — Follow the recommendations and instructions. However, you can safely ignore warnings about the setting of database parameter>   remote_os_authent=TRUE.

 

PATCHES

====================================

P9584028GEN12102P_2-20012303.zip

PATCHBUNDLE12102P_1702-20012303.zip

PATCHBUNDLE12102P_1704-20012303.zip

 

O:\12cpacthc\P9584028GEN12102P_2-20012303\9584028>E:\oracle\GEP\12102\OPatch\opatch apply

 

RUN PRE-UPGRADE SCRIPT

 

Snote : 1915315 – Database Upgrade Scripts for 12.1.0.2

==================

OS> cd< NEW_ORACLE_HOME>\sap\ora_upgrade\pre_upgrade

 

OS> sqlplus / as sysdba

SQL> @pre_upgrade_tasks.sql

SQL> @pre_upgrade_checks_ora.sql

SQL> @pre_upgrade_checks_sap.sql

 

=======================OPTIONAL==================================================================

If you create a guaranteed restore point (GRP) before starting the upgrade, then you can easily reset the database back to this point (FLASHBACK DATABASE) in case of errors during the upgrade.

 

Please note the following:

 

Creating a guaranteed restore point before the upgrade is optional.

A guaranteed restore point is no replacement for a database backup.

You can not automatically increase database parameter COMPATIBLE while a guaranteed restore point is defined. You need to drop the guaranteed restore point first (see below: post-upgrade tasks: set_compatible.sql).

 

The following script will create a guaranteed restore point ‘GRP_BEFORE_UPGRADE_12102’.

 

SQL> @grp_create_restore_point.sql

SQL> @grp_list_restore_point.sql

 

==================================================================================================

Close all command prompts or power shell windows that have the

old Oracle environment from before the upgrade (upgrade and pre-upgrade).

 

RUN DUBA from new oracle home (SIDADM)

 

CHECK IS THERE ANY INVALID OBJECT FROM

 

SELECT OBJECT_NAME FROM DBA_OBJECT WHERE STATUS=’INVALID’;

 

IF ANY OBJECT RETRIVEED COMPILE USING OLD ORACALE HOME\ rdbms\admin\utlrp.sql

 

AFTER DUBA >>>>>>>>>>

 

-> Start listener from the new environment

— LSNRCTL STATUS

— LSNRCTL START

(if listener is not started create listner to new dbhome )

 

– COPY INIT(SID).ORA,INIT(SID).SAP, INIT(SID).utl to new oralce\database( from old directory if not exisit)

– CHECK DIRECTORY> NETWORK/ADMIN>, TNSADMIN, SQLNET, LISTNER files IF NOT CORRECT COPY FROM OLD       ORACLE HOME TO NEW

 

-> Start database instance from the new environment

 

Go to directory ‘post_upgrade’ to run the post-upgrade scripts.

 

OS> cd< NEW_ORACLE_HOME>\sap\ora_upgrade\post_upgrade

 

 

Oracle Database 12.1 Post-Upgrade Status Tool

You can check the upgrade status and upgrade time for database components by running the Oracle Database 12.1 Post-Upgrade Status Tool. A similar information can be found in the DBUA Upgrade Results log file.

 

–SQL> @?/rdbms/admin/utlu121s.sql

 

Oracle Database 12.1 Post-Upgrade Invalid Objects Tool

You can check the database for invalid objects that were not invalid prior to upgrade:

 

SQL> @?/rdbms/admin/utluiobj.sql

 

 

<\Post upgrade steps> 1915315 – Database Upgrade Scripts for 12.1.0.2

======================================================================

 

Go to directory ‘post_upgrade’ to run the post-upgrade scripts.

 

–SQL> @post_upgrade_tasks.sql

 

Recompile invalid objects

 

–SQL> @?/rdbms/admin/utlrp.sql

 

Run post-upgrade checks

 

–SQL> @post_upgrade_checks.sql

 

Check whether guaranteed restore point ‘GRP_BEFORE_UPGRADE_12102’ exists:

==========================================================================

 

OS> cd< NEW_ORACLE_HOME>/sap/ora_upgrade/pre_upgrade

OS> sqlplus / as sysdba

 

-SQL> @grp_list_restore_point.sql

 

you need to drop this restore point and set ‘COMPATIBLE’ to the new value.

=============================================================================

 

OS> cd< NEW_ORACLE_HOME>/sap/ora_upgrade/pre_upgrade

OS> sqlplus / as sysdba

 

–SQL> @grp_drop_restore_point.sql

 

OS> cd< NEW_ORACLE_HOME>/sap/ora_upgrade/post_upgrade

OS> sqlplus / as sysdba

 

–SQL> @set_compatible.sql

 

 

After setting compatible to a new value, ‘RESTART THE DATABASE.’

 

Ensure that parameter ‘compatible’ is set to ‘12.1.0.2.0’ or ‘12.1.0.2’:

 

-SQL> SHOW PARAMETER COMPATIBLE

 

–SQL> SELECT VALUE “COMPATIBLE” FROM V$PARAMETER WHERE NAME = ‘COMPATIBLE’;

 

Before you start the remaining database post-upgrade tasks you can create a new guaranteed restore point (–OPTIONAL)

==================================================================================================

 

-SQL> CREATE RESTORE POINT “‘GRP_AFTER_UPGRADE_12102′” GUARANTEE FLASHBACK DATABASE ;

 

–SQL> SELECT NAME FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE = ‘YES’;

 

drop V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE = ‘YES’;

 

Checking for the invalid objects:

===========================

 

–SQL> select owner, object_name, object_type from dba_objects where status = ‘INVALID’;

 

If the object ‘DBMS_METADATA’ (package body) is still invalid (Ref: patch 17871192), run utlrp to recompile the package.

==================================================================================================

–SQL> @?\rdbms\admin\utlrp.sql

 

–SQL> select owner, object_name, object_type from dba_objects where status = ‘INVALID’;

 

 

RUN BELOW SCRIPTS ON COMMENAD PROMPT

 

brconnect -u / -c -f stats -t system_stats

 

brconnect -u / -c -f stats -t oradict_stats

 

brconnect -u / -c -f stats -t all -f collect -p 4

 

/*if needed */{}{}{}{}{}{}{}{}{}{}{}{}{}{}{}

=================

sqlplus / as sysdba @sapuprof_profile

 

Insufficent privillege

 

134592 – Import of SAPDBA role (sapdba_role.sql)

==========================
https://www.dropbox.com/s/t9dpct4d3r1vsls/sapdba_role.sql?dl=0

 

sqlplus /nolog @sapdba_role SAPSR3

 

–CHANGE PARAMTERS ONCE UPGRADE COMPLETE.

 

===========================================================================

 

DEINSTALL THE ORALCE 11G

Be the first to leave a comment
You must be Logged on to comment or reply to a post.