Oracle APPS DBA pieces: upgrade EBS 12.2 database 12c(12.1.0.2) to 19c(19.3)-Part 3

upgrade EBS 12.2 database 12c(12.1.0.2) to 19c(19.3)-Part 3

In Continuation to my previous posts https://samappsdba.blogspot.com/2020/12/upgrade-EBS-12.2-database-12c12.1.0.2-to-19c19.3-Part-2.html, in this post, we will see the steps once the database is upgraded.

Run adgrants.sql

Copy $APPL_TOP/admin/adgrants.sql (adgrants_nt.sql for Windows) from the administration server node to the database server node. Use SQL*Plus to connect to the database as SYSDBA, and run the script using the following command:

sqlplus "/ as sysdba" @adgrants.sql apps;

Note: When running adgrants.sql, you may get ORA-00942 errors. These are caused by adgrants.sql trying to create grants on non-existent objects. The errors can be ignored.

Grant create procedure privilege on CTXSYS

Copy $AD_TOP/patch/115/sql/adctxprv.sql from the administration server node to the database server node. Use SQL*Plus to connect to the database as apps and run the script using the following command:

$ sqlplus apps/<apps password> @adctxprv.sql <SYSTEM password> CTXSYS

[oracle@apps sql]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Dec 18 05:19:37 2020

Version 19.3.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> @adctxprv.sql manager CTXSYS;

Connecting to SYSTEM

Connected.

PL/SQL procedure successfully completed.

Commit complete.

Compile invalid objects

Use SQL*Plus to connect to the database as SYSDBA and run the $ORACLE_HOME/rdbms/admin/utlrp.sql script to compile invalid objects.

Grant datastore access

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> grant text datastore access to public;

Grant succeeded.

Gather statistics for the SYS schema

SQL> alter system enable restricted session;

System altered.

SQL> @adstats.sql;

Connected.

--------------------------------------------------

--- adstats.sql started at 2020-12-19 09:05:01 ---

Checking for the DB version and collecting statistics ...

PL/SQL procedure successfully completed.

------------------------------------------------

--- adstats.sql ended at 2020-12-19 09:27:23 ---

Commit complete.

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

[oracle@apps ~]$

[oracle@apps ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Dec 19 09:29:28 2020

Version 19.3.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> alter system disable restricted session;

System altered.

SQL>

Convert Database to Multitenant Architecture

SID of PDB will same as of NON CDB of source which is ebsdb.

Source SID=ebsdb

Database name-ebscdb

CDB sid=ebscdb

perl txkOnPremPrePDBCreationTasks.pl -dboraclehome=$ORACLE_HOME -outdir=$ORACLE_HOME/appsutil/log -appsuser=apps -dbsid=ebsdb

Update the CDB initialization parameters

On the database server node, copy the <source SID>_initparam.sql and <source SID>_datatop.txt files from the source $ORACLE_HOME/dbs directory to the new $ORACLE_HOME/dbs directory. Then use SQL*Plus to connect to the CDB as SYSDBA, and run the following commands to update the CDB initialization parameters:

[oracle@apps dbs]$ cd $ORACLE_HOME/appsutil

[oracle@apps appsutil]$ . ./txkSetCfgCDB.env dboraclehome=/u01/install/APPS/19.1.3

Oracle Home being passed: /u01/install/APPS/19.1.3

[oracle@apps appsutil]$ export ORACLE_SID=ebscdb

[oracle@apps appsutil]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Dec 19 12:14:16 2020

Version 19.3.0.0.0

Connected to an idle instance.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 4647286504 bytes

Fixed Size 9144040 bytes

Variable Size 939524096 bytes

Database Buffers 3690987520 bytes

Redo Buffers 7630848 bytes

SQL> @/u01/install/APPS/19.1.3/dbs/ebsdb_initparam.sql;

System altered.

SQL> alter system set LOCAL_LISTENER="apps:1521" scope=both;

System altered.

SQL> shutdown;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup;

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 2147482344 bytes

Fixed Size 9136872 bytes

Variable Size 436207616 bytes

Database Buffers 1677721600 bytes

Redo Buffers 24416256 bytes

Database mounted.

Database opened.

SQL>

Check for PDB violations

export ORACLE_SID=ebscdb

[oracle@apps bin]$ perl txkChkPDBCompatability.pl -dboraclehome=/u01/install/APPS/19.1.3 -outdir=/u01/install/APPS/19.1.3/appsutil/log -cdbsid=ebscdb -pdbsid=ebsdb -servicetype=onpremise

Create the PDB

Make sure datatop.txt file exist under new oracle home/dbs. If not then copy it from old OH/dbs.

[oracle@apps appsutil]$ . ./txkSetCfgCDB.env dboraclehome=/u01/install/APPS/19.1.3

Oracle Home being passed: /u01/install/APPS/19.1.3

[oracle@apps appsutil]$ cd $ORACLE_HOME/appsutil/bin

[oracle@apps bin]$ /u01/install/APPS/19.1.3/perl/bin/perl txkCreatePDB.pl -dboraclehome=/u01/install/APPS/19.1.3 -outdir=/u01/install/APPS/19.1.3/appsutil/log -cdbsid=ebscdb -pdbsid=ebsdb -dbuniquename=ebscdb -servicetype=onpremise

Note:Till the time this script will run, the status of the PDB will in read/write along with restricted=YES. Once the script completes, the status will change to restricted=NO.

Check the status of PDB now:-

SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED READ ONLY NO

3 ebsdb READ WRITE NO

Run the post PDB script

[oracle@apps appsutil]$ perl $ORACLE_HOME/appsutil/bin/txkPostPDBCreationTasks.pl -dboraclehome=/u01/install/APPS/19.1.3 -outdir=/u01/install/APPS/19.1.3/appsutil/log -cdbsid=ebscdb -pdbsid=ebsdb -appsuser=apps -dbport=1521 -servicetype=onpremise

This will also run autoconfig in the database tier.

Modify initialization parameters

SQL> alter system set event='10946 trace name context forever, level 8454144' scope=spfile;

System altered.

SQL> alter system set sec_case_sensitive_logon=FALSE scope=spfile;

System altered.

SQL>

Modify the tnsnamnes.ora file in application patch and run file system

Add the following content of CDB to application run and patch tnsnames.ora

ebscdb=

(DESCRIPTION=

(ADDRESS=(PROTOCOL=tcp)(HOST=apps.example.com)(PORT=1521))

(CONNECT_DATA=

(SERVICE_NAME=ebsdb)--pdb sid

(INSTANCE_NAME=ebscdb)--cdb sid

)

Modify the context file of application tier

In the context file

Replace

s_dbportNew database port

s_apps_jdbc_connect_descriptorNULL

s_applptmpDirectory (not /usr/tmp) defined in UTL_FILE_DIR

To identify the allowable directories for s_applptmp use, connect to the Oracle E-Business Suite database instance as the apps user and run the following query:

SQL> select value from v$parameter where name='utl_file_dir';

I replace it with

/u01/install/APPS/19.1.3/temp/ebsdb

Remember to do the changes in patch file system also

Now run autoconfig

start the application tier

This completes the step by step approach on how we can upgrade EBS 12.2 database 12c(12.1.0.2) to 19c(19.3). Hope it helps someone.

Oracle APPS DBA pieces

Pages

Sunday, December 27, 2020

upgrade EBS 12.2 database 12c(12.1.0.2) to 19c(19.3)-Part 3

upgrade EBS 12.2 database 12c(12.1.0.2) to 19c(19.3)-Part 3

Convert Database to Multitenant Architecture

11 comments:

Traffic