Issues after 12.2 upgrade

Here i have listed all issue that occurred after 12.2 upgrade from 11.2.0.4

Problem:

12.2 DBCA does not update oratab with the database name in RAC environment.

Solution:

It is expected behaviour in RAC Database creations from 12.2.0.1. Use srvctl to get instance name and oracle home information.

For single instance, database entry gets updated in the /etc/oratab.

12.2 Real Application Cluster DBCA Do Not Update Oratab (Doc ID 2278584.1)

Problem:

Application hangs for one particular user with his own schema when tries to open application

Solution 1:

Deploy new application with this sql build in while log in

alter session set optimizer_features_enable=’11.2.0.4′;

it worked after application deploy with this code.

Solution 2:

Another options is to create a sql patch for sqlid 31xs7d7yj3km4. 
How to Create a SQL Patch to add Hints to Application SQL Statements ( Doc ID 1931944.1 ) 

Problem

Background process SCM0 consuming excessive CPU

Solution

Disable SCM proccess

alter system set “_dlm_stats_collect” = 0 scope = spfile sid = ‘*’;

show parameter _dlm_stats_collect;

12.2 RAC DB Background process SCM0 consuming excessive CPU (Doc ID 2373451.1)

Problem

Acknowledge over PGA limit’ Wait Event

Solution

reset parameter

ALTER SYSTEM SET PGA_AGGREGATE_LIMIT=0 SID=’*’ SCOPE=BOTH;

show parameter pga;

12c: ‘acknowledge over PGA limit’ Wait Event (Doc ID 2138882.1)

Problem

Change .profile

Problem

Check dblink and fix if any issue

Problem

log in issue

Solution

Connect with container database and change parameter

alter system set sec_case_sensitive_logon=false scope=both sid=’*’;

Upgrade set sec_case_sensitive_logon to TRUE –so set to FALSE

Problem

Modify sqlnet.ora for 10g invalid password issue after upgrade

Solution

Modify sqlnet.ora on server side according to client version users are using

SQLNET.ALLOWED_LOGON_VERSION_CLIENT=10

SQLNET.ALLOWED_LOGON_VERSION_SERVER=10

Problem

.NET application return strange result

Solution

.NET application return truncated string with 12c client. But it works with 11g client.  In toad it works with 12c though so it strange issue.

Problem

Connection issue who have 11g client –ORA-28040 : No matching authentication protocol

Solution:

Add below entry on server side of sqlnet.ora on both node

SQLNET.ALLOWED_LOGON_VERSION_CLIENT=10

SQLNET.ALLOWED_LOGON_VERSION_SERVER=10

Problem

Invalids in SYS schema – V$XS_SESSION_ROLE in PDB but not in CDB

Solution:

bash-4.2$ sqlplus sys/@db12 as sysdba

SQL> alter session set container=db11g;

Session altered.

SQL> show con_name;

CON_NAME
——————————
db11g
SQL>
SQL> DROP VIEW SYS.V$XS_SESSION_ROLE; 
DROP VIEW SYS.V$XS_SESSION_ROLE
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views

If you get above error then do option 2 recommended by oracle development. At this time it is not documented in oracle

SQL> update obj$ set status = 1 where name = ‘V$XS_SESSION_ROLE’ and subname = ‘$DEPRECATED$’; 

1 row updated.

select owner,OBJECT_NAME,OBJECT_TYPE,status from dba_objects where object_name = ‘V$XS_SESSION_ROLE’;

SYS V$XS_SESSION_ROLE VIEW VALID
PUBLIC V$XS_SESSION_ROLE SYNONYM VALID

Problem:

UTL_FILE directory

The UTL_FILE_DIR initialization parameter is deprecated in Oracle Database 12c Release 2 (12.2.0.1) and may not be supported in a future release.

In the past, accessible directories for PL/SQL file I/O were specified in the initialization file using the UTL_FILE_DIR parameter. However, UTL_FILE_DIR access is no longer recommended. Oracle recommends that you instead use the directory object feature, which replaces UTL_FILE_DIR. Directory objects offer more flexibility and granular control to the UTL_FILE application administrator, can be maintained dynamically (that is, without shutting down the database), and are consistent with other Oracle tools.

Solution:

Change all the PL/SQL code with Database Directory

• Check instance, database, version

select * from gv$instance;

select * from gv$database;

select * from gv$version;

Problem

• Alert log error

Buffer Cache Full DB Caching mode changing from FULL CACHING ENABLED to FULL CACHING DISABLED

Full DB Caching disabled: DEFAULT_CACHE_SIZE should be at least 283902 MBs bigger than current size.

db11g(3):Errors in file /acfs_mnt/diag/rdbms/trace/1_j006_17433166.trc:

ORA-12012: error on auto execute of job “abc”.”PRC_JOB”

ORA-29280: invalid directory path

ORA-06512: at “SYS.UTL_FILE”, line 41

ORA-06512: at “SYS.UTL_FILE”, line 478

ORA-06512: at “MED.PRC_JOB”, line 63

Errors in file /acfs_mnt/diag/rdbms///trace/1_j001_9961876.trc:

ORA-12012: error on auto execute of job “SYS”.”ORA$AT_OS_OPT_SY_37″

ORA-20001: Statistics Advisor: Invalid task name for the current user

ORA-06512: at “SYS.DBMS_STATS”, line 47207

ORA-06512: at “SYS.DBMS_STATS_ADVISOR”, line 882

ORA-06512: at “SYS.DBMS_STATS_INTERNAL”, line 20059

ORA-06512: at “SYS.DBMS_STATS_INTERNAL”, line 22201

ORA-06512: at “SYS.DBMS_STATS”, line 47197

Solution

Please refer
ORA-12012 Error on auto execute of job “SYS”.”ORA$AT_OS_OPT_SY_<NN> in 12.2 Database ( Doc ID 2127675.1 )

Next Steps:- 

Please run dbms_stats.init_package() in the container database to create the tasks correctly:

$ sqlplus / as sysdba

EXEC dbms_stats.init_package();

Once done, please execute below query ..

column name format A35
set linesize 120

select name, ctime, how_created
from sys.wri$_adv_tasks
where owner_name = ‘SYS’
and name in (‘AUTO_STATS_ADVISOR_TASK’,’INDIVIDUAL_STATS_ADVISOR_TASK’);

Output of the query will look like this:
NAME CTIME HOW_CREATED
———————————– ———- ——————————
AUTO_STATS_ADVISOR_TASK 14-APR-16 CMD
INDIVIDUAL_STATS_ADVISOR_TASK 14-APR-16 CMD

Problem

• db11g(3):ERROR: Unable to normalize symbol name for the following short stack (at offset 494):

dbgexProcessError()+228<-dbgeExecuteForError()+116<-dbgePostErrorKGE()+2056<-dbkePostKGE_kgsf()+88<-kgeade()+444<-kgesev()+148<-kgesec1()+60<-pppjmpl_local()+328<-pppjmp()+708<-pdz2Mz2_Asser

t_Print()+1364<-pdz2M14_AssertO()+176<-pdy3M90_Allocate_One_Constant()+996<-pdy3M02_Allocate_Constants()+1460<-pdy1F82_Write_MCode()+164<-pdy1F01_Driver()+264<-pdw0F82_Run_Code_Gen()+208<-pd

w0F01_Code_Gen()+372<-phpcog()+28<-phpcmp()+1616<-pcicmp0()+472<-kkxcmp0()+968<-rpiswu2()+580<-kkxcmp()+248<-IPRA.$kkpalt()+2892<-kkpalt()+160<-opiexe()+20268<-opiosq0()+4972<-kpooprx()+412<

-kpoal8()+828<-opiodr()+1248<-ttcpip()+616<-opitsk()+1888<-opiino()+948<-opiodr()+1248<-opidrv()+996<-sou2o()+184<-opimai_real()+680<-ssthrdmain()+460<-main()+208<-__start()+112

Errors in file /acfs_mnt/diag/rdbms//trace/_1_ora_53674430.trc  (incident=66072) (PDBNAME=db11g):

ORA-06544: PL/SQL: internal error, arguments: [*** ASSERT at file pdy3.c, line 1931; Layout Mismatch – 2 < 16; PKG_FORM_PARM___76748[1625, 1]], [], [], [], [], [], [], []

db11g(3):Incident details in: /acfs_mnt/diag/rdbms///incident/incdir_66072/1_ora_53674430_i66072.trc

Errors in file /acfs_mnt/diag/rdbms///trace/1_ora_53674430.trc  (incident=66074) (PDBNAME=db11g):

ORA-06544: PL/SQL: internal error, arguments: [*** ASSERT at file pdy3.c, line 1931; Layout Mismatch – 2 < 16; PKG_FORM_76748[1625, 1]], [], [], [], [], [], [], []

db11g(3):Incident details in: /acfs_mnt/diag/rdbms///incident/incdir_66074/1_ora_53674430_i66074.trc

2018-01-12T10:55:32.311247-06:00

WARNING: too many parse errors, count=600 SQL hash=0x50366ef0

PARSE ERROR: ospid=50987508, error=942 for statement:

2018-01-12T10:55:32.316163-06:00

SELECT av.version_dt FROM rob.application_version av WHERE av.app_cd = :1

Additional information: hd=70001008dae60b8 phd=70001007196ea38 flg=0x101476 cisid=157 sid=157 ciuid=157 uid=157

2018-01-12T10:55:32.321188-06:00

—– PL/SQL Call Stack —–

object      line  object

handle    number  name

Solution:

Developer require to change SQL or code

Problem

Redo warning

*****************************************************************
An internal routine has requested a dump of selected redo.
This usually happens following a specific internal error, when
analysis of the redo logs will help Oracle Support with the
diagnosis.
It is recommended that you retain all the redo logs generated (by
all the instances) during the past 12 hours, in case additional
redo dumps are required to help with the diagnosis.
*****************************************************************

SRDC – Required Diagnostic Data Collection for Redo Log Corruption ( Doc ID 1671530.1 )

Kindly take a look at – An Internal Routine Has Requested A Dump Of Selected Redo – Message Appears In Alert Log ( Doc ID 2295361.1 )

Solution:

ignore it if there is no error after this message. Also monitor log if error occurs sometime

 Problem

Partition message in alert log – as per SR

TABLE SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY: ADDED INTERVAL PARTITION SYS_P

These are partitions of an internal table that collects AWR statistics – Alert Log Shows Messages Starting “TABLE WRI$_OPTSTAT_* ADDED INTERVAL PARTITION…” ( Doc ID 2018434.1 ) –

 

ORA-00060: deadlock resolved; details in file /acfs_mnt/diag/rdbms//1/trace/_1_j002_32178392.trc
2018-03-10T10:12:17.283105-06:00
Errors in file /acfs_mnt/diag/rdbms//_1/trace/_1_j002_32178392.trc:
ORA-12012: error on auto execute of job “SYS”.”ORA$AT_SA_SPC_SY_5115″
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at “SYS.DBMS_SPACE”, line 2741
ORA-06512: at “SYS.DBMS_HEAT_MAP_INTERNAL”, line 716
ORA-06512: at “SYS.DBMS_HEAT_MAP_INTERNAL”, line 1164
ORA-06512: at “SYS.DBMS_HEAT_MAP”, line 228
ORA-06512: at “SYS.DBMS_SPACE”, line 2747
2018-03-10T10:32:13.788957-06:00

SOLUTION / ACTION PLAN

Your issue looks similar to Doc ID 2321020.1

In 12.2 Auto Space Advisor Job Fails With ORA-60 ( Doc ID 2321020.1 )

Until Bug 24687075 gets fixed, you can disable the space advisory job as

workaround:
SQL> EXEC DBMS_AUTO_TASK_ADMIN.DISABLE(‘AUTO SPACE ADVISOR’,NULL, NULL);