Today, I would like to discuss a strange issue which was experienced during schema refresh activity. Everything was normal and as expected in the environment until the April 2017 patching has been applied in the Env.

Due to the Q-PSU patching ,there were some system views which got invalid and the imports job was aborting with flood of errors ,mainly stating ORA-600

Below are the errors :

ORA-39014: One or more workers have prematurely exited.
ORA-39029: worker 1 with process name "DW00" prematurely terminated
ORA-31671: Worker process DW00 had an unhandled exception.
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-00600: internal error code, arguments: [klxpr_22], [2], [30], [36053], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_SQL", line 1707
ORA-06512: at "SYS.KUPD$DATA", line 2495
ORA-06512: at "SYS.KUPD$DATA", line 3503
ORA-06512: at "SYS.KUPD$DATA", line 3764
ORA-06512: at "SYS.KUPW$WORKER", line 19325
ORA-06512: at "SYS.KUPW$WORKER", line 24290
ORA-06512: at "SYS.KUPW$WORKER", line 20708
ORA-06512: at "SYS.KUPW$WORKER", line 4561
ORA-06512: at "SYS.KUPW$WORKER", line 12079
ORA-06512: at "SYS.KUPW$WORKER", line 2097
ORA-06512: at line 2
ORA-39014: One or more workers have prematurely exited.
ORA-39029: worker 2 with process name "DW01" prematurely terminated
ORA-31671: Worker process DW01 had an unhandled exception.
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-00600: internal error code, arguments: [klxpr_22], [2], [30], [36053], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_SQL", line 1707
ORA-06512: at "SYS.KUPD$DATA", line 2495
ORA-06512: at "SYS.KUPD$DATA", line 3503
ORA-06512: at "SYS.KUPD$DATA", line 3764
ORA-06512: at "SYS.KUPW$WORKER", line 19325
ORA-06512: at "SYS.KUPW$WORKER", line 24290
ORA-06512: at "SYS.KUPW$WORKER", line 20708
ORA-06512: at "SYS.KUPW$WORKER", line 4561
ORA-06512: at "SYS.KUPW$WORKER", line 12079
ORA-06512: at "SYS.KUPW$WORKER", line 2097
ORA-06512: at line 2

Upon further investigation, It was found that there are some SYS views which are in INVALID state due to which IMPORT operations is getting aborted.

set lines 140
col status for a9
col object_type for a20;
col owner.object for a50
select con_id, status, object_id, object_type,owner||'.'||object_name "OWNER.OBJECT" from cdb_objects where object_name like '%KU$%' and status='INVALID' order by 4,1;


OWNER OBJECT_NAME OBJECT_TYPE
---------- ------------------------------ ---------------
SYS KU$_M_VIEW_H_VIEW VIEW
SYS KU$_ZM_VIEW_H_VIEW VIEW
SYS KU$_M_VIEW_PH_VIEW VIEW
SYS KU$_ZM_VIEW_PH_VIEW VIEW
SYS KU$_M_VIEW_FH_VIEW VIEW
SYS KU$_ZM_VIEW_FH_VIEW VIEW
SYS KU$_M_VIEW_PFH_VIEW VIEW
SYS KU$_ZM_VIEW_PFH_VIEW VIEW
SYS KU$_M_VIEW_IOT_VIEW VIEW
SYS KU$_ZM_VIEW_IOT_VIEW VIEW
SYS KU$_M_VIEW_PIOT_VIEW VIEW
SYS KU$_ZM_VIEW_PIOT_VIEW VIEW
SYS KU$_M_ZONEMAP_H_VIEW VIEW
SYS KU$_M_ZONEMAP_PH_VIEW VIEW
SYS KU$_M_ZONEMAP_FH_VIEW VIEW
SYS KU$_M_ZONEMAP_PFH_VIEW VIEW
SYS KU$_M_ZONEMAP_IOT_VIEW VIEW
SYS KU$_M_ZONEMAP_PIOT_VIEW VIEW



My work around for the issue is to validate the view using ALTER which was not helpful in my case.
Upon more search in Oracle Metalink Doc ID 2289785.1its been found as
Bug 25297023 : INVALID OBJECTS AFTER UPGRADING OR APLYING DATAPATCH


And the solutions which was suggested by Oracle was :

1. alter SYSTEM set nls_length_semantics=byte
2. ORACLE_HOME/rdbms/admin/dpload.sql
3. ORACLE_HOME/rdbms/admin/utlrp.sql 

This Instinct resolve the Issue and import operation got completed with out any errors 

 

ORA-00060-DEADLOCK DETECTED

Thu Dec 01 14:53:28 2016
ORA-00060: Deadlock detected. More info in file /u01/app/oracle/diag/rdbms/db_name/db_name/trace/db_name_ora_120412.trc.
Thu Dec 01 14:53:40 2016
ORA-00060: Deadlock detected. More info in file /u01/app/oracle/diag/rdbms/db_name/db_name/trace/db_name_
ora_107905.trc.
[oracle@ trace]$



*** 2016-12-01 14:53:37.906
DEADLOCK DETECTED ( ORA-00060 )

[Transaction Deadlock]

The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:


Deadlock condition : generally this situations can be seen at OS perspective at a common pace , but when it occurs at the DB level then surely it involves developers and DBA to look kindle at the code and infrastructure manipulation perspective ..

Usual cause of deadlocks is due to poor coding skills and improper management of DB resources 

DBA need to work with the Apps teams closely to resolve this issue. 

Let me give an example here : 

If a query is accessing the block,which is accessed by another query might cause contention & result in the conditions of dead lock ..
The same condition can be ruled out at OS perspective using Mutual exclusion and Semaphores.
Where as with DB perspective we need to change the parameter for a particular table which causing dead lock 

There are 2 parameter with respect to concurrent access of blocks 

ini_trans
max_trans  which states mini and max amount of users can access the DB in a given single point of time

ini_trans is 1 by default and max_trans 255

To avoid issues with deadloack ini_trans can be tune as per the requirement of apps and load on the DB for a particular block

Issue while patching the database post to 12c upgrade

Connecting to database...OK
catcon: ALL catcon-related output will be written to /u02/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_2556730_2016_11_25_10_54_39/sqlpatch_catcon__catcon_2556730.lst
catcon: See /u02/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_2556730_2016_11_25_10_54_39/sqlpatch_catcon_*.log files for output generated by scripts
catcon: See /u02/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_2556730_2016_11_25_10_54_39/sqlpatch_catcon__*.lst files for spool files, if any
Bootstrapping registry and package to current versions...done
  Error in bootstrap log /u02/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_2556730_2016_11_25_10_54_39/bootstrap_db_name.log:
    Error at line 26: ORA-00406: COMPATIBLE parameter needs to be 12.0.0.0.0 or greater
    Error at line 76: Warning: Package Body created with compilation errors.
    Error at line 82: 78/5     PL/SQL: SQL Statement ignored
    Error at line 83: 78/12    PL/SQL: ORA-00942: table or view does not exist
    Error at line 84: 80/5     PL/SQL: SQL Statement ignored
    Error at86767 line 85: 80/17    PL/SQL: ORA-00942: table or view does not exist
    Error at line 86: 117/5    PL/SQL: SQL Statement ignored
    Error at line 87: 132/10   PL/SQL: ORA-00942: table or view does not exist
    Error at line 88: 151/5    PL/SQL: SQL Statement ignored
    Error at line 92: 151/12   PL/SQL: ORA-00942: table or view does not exist
Prereq check failed, exiting without installing any patches.


Now,The patching has failed with the above errors 

There are 3 solutions for the issues
1.Change compatible parameter in the pfile or spfile
  --It has been observed the changing the compatible to 12.1.0 will create performance issues and may cause problems with final deliveries to the client.
--And once the Compatible parameter has been changed to 12c , then the downgrade is not possible unless you're restoring it from 11g pre-upgrade backup

2. As per MOS it's bug ; Bug no :24386767 and MOS doc id 24386767.8
According to which Bugfix is developed to resolve this issues

3. The same solution is provided in 2016 Q4 PSU patch release 

The feasible solution will be to apply PSU patch and rule-out the error

Enjoy working ...!!!

Oracle Upgrade from 11gR2 to 12cR1

Oracle Upgrade from 11gR2 to 12cR1
-------------------------------------------------

1.Install the oracle binaries in the server .

2. copy the required files(pfile,orapwd,incase snap file as well) from old Ora_home/dbs (11g) to New ORA_HOME/dbs (12c)

3.Run the pre-upgrade script for the 11g database from 12c home

In 11g DB
-----------------

This file should be from 12c home

?@NEW_ORA_HOME/rdbms/admin/preupgrd.sql

This execution of file generated 3 more files in Current oracle_base/cfgtoolslogs/DB_NAME/

it will contains 

i. Pre-upgrade.log

ii.Preupgrade_fixups.sql

iii.Postupgrade_fixups.sql

Now the next thing on the move is to check the pre-upgrad e.log file and execute the pre-upgrade_fixups.sql file in 11g DB ..

It happens some time that the pre-upgrade_fixup.sql script may not be able to fix all the recommended and required issues suggested we need to do it manually as of in my case the manual actions were

job_queue_processess need to be more than 24

resource_limit should be TRUE and it was FALSE

Once this is done then

4. shutdown the DB in 11g 

5.Change the entries in oratab from current version to new version for DB

6. Set the environment for the DB 

Post the environment is set , do a explicit verification of below things

7. Check ORA_HOME is mapping or not 

echo $ORACLE_HOME

8.check $PATH

9. check $LD_LIBRARY_PATH as this is the important of all this sdhould map to 12c else the upgrade will fail and it will apply the interim patch during the upgrade uses this location as patch executable library.(check phase no. 65 & 69 ) 

10. Start the DB in upgrade mode 

startup upgrade

exit

11. From OS level issue the command for upgrade 

cd $ORACLE_HOME/rdbms/admin

$ORACLE_HOME/perl/bin/perl catctl.pl catupgrd.sql

below is the upgrade Screen shot

Argument list for [catctl.pl]
SQL Process Count     n = 0
SQL PDB Process Count N = 0
Input Directory       d = 0
Phase Logging Table   t = 0
Log Dir               l = 0
Script                s = 0
Serial Run            S = 0
Upgrade Mode active   M = 0
Start Phase           p = 0
End Phase             P = 0
Log Id                i = 0
Run in                c = 0
Do not run in         C = 0
Echo OFF              e = 1
No Post Upgrade       x = 0
Reverse Order         r = 0
Open Mode Normal      o = 0
Debug catcon.pm       z = 0
Debug catctl.pl       Z = 0
Display Phases        y = 0
Child Process         I = 0

catctl.pl version: 12.1.0.2.0
Oracle Base           = /u02/app/oracle

Analyzing file catupgrd.sql
Log files in /u02/app/oracle/product/12102/rdbms/admin
catcon: ALL catcon-related output will be written to catupgrd_catcon_29032460.lst
catcon: See catupgrd*.log files for output generated by scripts
catcon: See catupgrd_*.lst files for spool files, if any
Number of Cpus        = 12
SQL Process Count     = 0
New SQL Process Count = 4

------------------------------------------------------
Phases [0-73]
Serial   Phase #: 0 Files: 1     Time: 262s
Serial   Phase #: 1 Files: 5     Time: 91s
Restart  Phase #: 2 Files: 1     Time: 1s
Parallel Phase #: 3 Files: 18    Time: 21s
Restart  Phase #: 4 Files: 1     Time: 1s
Serial   Phase #: 5 Files: 5     Time: 29s
Serial   Phase #: 6 Files: 1     Time: 22s
Serial   Phase #: 7 Files: 4     Time: 13s
Restart  Phase #: 8 Files: 1     Time: 0s
Parallel Phase #: 9 Files: 62    Time: 69s
Restart  Phase #:10 Files: 1     Time: 1s
Serial   Phase #:11 Files: 1     Time: 23s
Restart  Phase #:12 Files: 1     Time: 0s
Parallel Phase #:13 Files: 91    Time: 25s
Restart  Phase #:14 Files: 1     Time: 0s
Parallel Phase #:15 Files: 111   Time: 46s
Restart  Phase #:16 Files: 1     Time: 0s
Serial   Phase #:17 Files: 3     Time: 3s
Restart  Phase #:18 Files: 1     Time: 1s
Parallel Phase #:19 Files: 32    Time: 50s
Restart  Phase #:20 Files: 1     Time: 0s
Serial   Phase #:21 Files: 3     Time: 18s
Restart  Phase #:22 Files: 1     Time: 1s
Parallel Phase #:23 Files: 23    Time: 184s
Restart  Phase #:24 Files: 1     Time: 0s
Parallel Phase #:25 Files: 11    Time: 101s
Restart  Phase #:26 Files: 1     Time: 1s
Serial   Phase #:27 Files: 1     Time: 1s
Restart  Phase #:28 Files: 1     Time: 0s
Serial   Phase #:30 Files: 1     Time: 0s
Serial   Phase #:31 Files: 257   Time: 40s
Serial   Phase #:32 Files: 1     Time: 0s
Restart  Phase #:33 Files: 1     Time: 0s
Serial   Phase #:34 Files: 1     Time: 8s
Restart  Phase #:35 Files: 1     Time: 0s
Restart  Phase #:36 Files: 1     Time: 1s
Serial   Phase #:37 Files: 4     Time: 97s
Restart  Phase #:38 Files: 1     Time: 0s
Parallel Phase #:39 Files: 13    Time: 119s
Restart  Phase #:40 Files: 1     Time: 1s
Parallel Phase #:41 Files: 10    Time: 17s
Restart  Phase #:42 Files: 1     Time: 1s
Serial   Phase #:43 Files: 1     Time: 11s
Restart  Phase #:44 Files: 1     Time: 1s
Serial   Phase #:45 Files: 1     Time: 14s
Serial   Phase #:46 Files: 1     Time: 0s
Restart  Phase #:47 Files: 1     Time: 1s
Serial   Phase #:48 Files: 1     Time: 1s
Restart  Phase #:49 Files: 1     Time: 1s
Serial   Phase #:50 Files: 1     Time: 1s
Restart  Phase #:51 Files: 1     Time: 0s
Serial   Phase #:52 Files: 1     Time: 1s
Restart  Phase #:53 Files: 1     Time: 0s
Serial   Phase #:54 Files: 1     Time: 496s
Restart  Phase #:55 Files: 1     Time: 2s
Serial   Phase #:56 Files: 1     Time: 2s
Restart  Phase #:57 Files: 1     Time: 0s
Serial   Phase #:58 Files: 1     Time: 1s
Restart  Phase #:59 Files: 1     Time: 1s
Serial   Phase #:60 Files: 1     Time: 1s
Restart  Phase #:61 Files: 1     Time: 0s
Serial   Phase #:62 Files: 1     Time: 2s
Restart  Phase #:63 Files: 1     Time: 0s
Serial   Phase #:64 Files: 1     Time: 3s
Serial   Phase #:65 Files: 1 Calling sqlpatch with LD_LIBRARY_PATH=/u02/app/oracle/product/12102/lib; export LD_LIBRARY_PATH; LIBPATH=/u02/app/oracle/product/12102/lib; export LIBPATH; LD_LIBRARY_PATH_64=/u02/app/oracle/product/12102/lib; export LD_LIBRARY_PATH_64; DYLD_LIBRARY_PATH=/u02/app/oracle/product/12102/lib; export DYLD_LIBRARY_PATH; /u02/app/oracle/product/12102/perl/bin/perl -I /u02/app/oracle/product/12102/rdbms/admin -I /u02/app/oracle/product/12102/rdbms/admin/../../sqlpatch /u02/app/oracle/product/12102/rdbms/admin/../../sqlpatch/sqlpatch.pl -verbose -upgrade_mode_only > catupgrd_datapatch_upgrade.log 2> catupgrd_datapatch_upgrade.err
returned from sqlpatch
    Time: 79s
Serial   Phase #:66 Files: 1     Time: 511s
Serial   Phase #:68 Files: 1     Time: 0s
Serial   Phase #:69 Files: 1 Calling sqlpatch with LD_LIBRARY_PATH=/u02/app/oracle/product/12102/lib; export LD_LIBRARY_PATH; LIBPATH=/u02/app/oracle/product/12102/lib; export LIBPATH; LD_LIBRARY_PATH_64=/u02/app/oracle/product/12102/lib; export LD_LIBRARY_PATH_64; DYLD_LIBRARY_PATH=/u02/app/oracle/product/12102/lib; export DYLD_LIBRARY_PATH; /u02/app/oracle/product/12102/perl/bin/perl -I /u02/app/oracle/product/12102/rdbms/admin -I /u02/app/oracle/product/12102/rdbms/admin/../../sqlpatch /u02/app/oracle/product/12102/rdbms/admin/../../sqlpatch/sqlpatch.pl -verbose > catupgrd_datapatch_normal.log 2> catupgrd_datapatch_normal.err
returned from sqlpatch
    Time: 98s
Serial   Phase #:70 Files: 1     Time: 210s
Serial   Phase #:71 Files: 1     Time: 2s
Serial   Phase #:72 Files: 1     Time: 0s
Serial   Phase #:73 Files: 1     Time: 27s

Grand Total Time: 2716s

LOG FILES: (catupgrd*.log)

Upgrade Summary Report Located in:
/u02/app/oracle/product/12102/cfgtoollogs/fisan02/upgrade/upg_summary.log

Grand Total Upgrade Time:    [0d:0h:45m:16s]

once upgrade is successful then execute below script in DB

@utlrp.sql

@/OLD_ORACLE_BASE/postupgrade_fixups.sql

Post-upgrade Checks
---------------------------------

1.Select comp_name,version,status from dba_registry;

 All should be valid 

2. Select banner from v$version

Issues during Oracle upgrade from 11.2.0.4 to 12c
-------------------------------------------------------

1. TEMP got filled , So its better to extend TEMP prior to upgrade 

2. During the upgrade perl file creates a its own readable pfile for multiple startups during the upgrade as you can see in screen shot (phase #44 & #47)

Upgrade was stopped as the process created pfile was not readable to open theDB  ,so DB has been started manually (startup) and conitnue  the upgrade process

Benefits of 12c Upgrade process :

1. Upgrade can be continued from where it has been stopped 

i.e., resume capability has been added to it

Suppose my Upgrade has failed at Phase#20 then i need to issue the command

$ORACLE_HOME/perl/bin/perl catctl.pl -p 20 -l ../../LOG_FILE_LOCATION catupgrd.sql

-p = For channel allocation 

-l = For Log location 

How resume works is 

it takes the information from logfile till the point of execution and then resumes from there

!!!....Hope this information helps....