DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Friday, March 31, 2017

Internal Concurrent Manager Issue In R12.2 due to ORA 4061 Error

We've recently encountered ICM process showing Actual=0, Target=1 in Oracle EBS R12.2.
 

Internal concurrent manager may goes down due to invalid objects. We noticed the following error in the internal concurrent manager log file.

Error:

............................................................................
_ 1 _
Error in DBMS lock request by handle. (ROUTINE=AFPGMG) (REASON=ORACLE
error 4068 in afpdlrh
Cause: afpdlrh failed due to ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "APPS.FND_DCP" has been invalidate
............................................................................

Fix:
1. Compile invalid objects showed in the log file
Connect to database as apps user and compile invalid object
alter package APPS.FND_DCP compile;
alter package APPS.FND_DCP compile body;
2. Stop concurrent managers
Login to middle tier where concurrent managers are running
cd $ADMIN_SCRIPTS_HOME
adcmctl.sh stop apps/apps_pwd
Note: Make sure all the managers are down. 
3. Start concurrent managers.
cd $ADMIN_SCRIPTS_HOME
adcmctl.sh start apps/apps_pwd

Reference:  
ORA-04061 Internal Concurrent Manager Going Down (Doc ID 1068908.1)
Happy EBS Troubleshooting .. 

Thursday, March 30, 2017

Correcting our RAC Services UNKNOWN / OFFLINE problem


I was having some trouble while running my RAC on RHEL4 (I think the problem primarily arose because I supplied my virtual machines with inadequate memory. 

When I noticed that I was getting some alerts , I looked at the status of my RAC.

oracle@hpora01 ~]$ cd /u01/app/oracle/product/10.2.0/crs/bin
[oracle@hpora01 bin]$ crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora.crmrac.db application    ONLINE    ONLINE    hpora02
ora....c1.inst application    ONLINE    ONLINE    hpora01
ora....c2.inst application    OFFLINE   UNKNOWN   hpora02
ora....serv.cs application    ONLINE    ONLINE    hpora02
ora....ac1.srv application    ONLINE    ONLINE    hpora01
ora....ac2.srv application    ONLINE    OFFLINE
ora....SM1.asm application    ONLINE    ONLINE    hpora01
ora....H4.lsnr application    ONLINE    ONLINE    hpora01
ora....rh4.gsd application    ONLINE    UNKNOWN   hpora01
ora....rh4.ons application    ONLINE    UNKNOWN   hpora01
ora....rh4.vip application    ONLINE    ONLINE    hpora01
ora....SM2.asm application    OFFLINE   UNKNOWN   hpora02
ora....H4.lsnr application    OFFLINE   UNKNOWN   hpora02
ora....rh4.gsd application    ONLINE    UNKNOWN   hpora02
ora....rh4.ons application    OFFLINE   UNKNOWN   hpora02
ora....rh4.vip application    ONLINE    ONLINE    hpora02


As you can see above, some of the applications are UNKNOWN or OFFLINE, either of which is not good for my RAC.
The crs_stat command gives you the names of the applications, which you might need to shut down some applications manually, in order to shut the whole cluster down and restart it.

[oracle@hpora01 bin]$ crs_stat
NAME=ora.crmrac.db
TYPE=application
TARGET=ONLINE
STATE=ONLINE on hpora02
NAME=ora.crmrac.crmrac1.inst
TYPE=application
TARGET=ONLINE
STATE=ONLINE on hpora01
NAME=ora.crmrac.crmrac2.inst
TYPE=application
TARGET=OFFLINE
STATE=OFFLINE
NAME=ora.crmrac.crmracserv.cs
TYPE=application
TARGET=ONLINE
STATE=ONLINE on hpora02
NAME=ora.crmrac.crmracserv.crmrac1.srv
TYPE=application
TARGET=ONLINE
STATE=ONLINE on hpora01
NAME=ora.crmrac.crmracserv.crmrac2.srv
TYPE=application
TARGET=ONLINE
STATE=OFFLINE
NAME=ora.hpora01.ASM1.asm
TYPE=application
TARGET=ONLINE
STATE=ONLINE on hpora01
NAME=ora.hpora01.LISTENER_hpora01.lsnr
TYPE=application
TARGET=ONLINE
STATE=ONLINE on hpora01
NAME=ora.hpora01.gsd
TYPE=application
TARGET=ONLINE
STATE=UNKNOWN on hpora01
NAME=ora.hpora01.ons
TYPE=application
TARGET=ONLINE
STATE=UNKNOWN on hpora01
NAME=ora.hpora01.vip
TYPE=application
TARGET=ONLINE
STATE=ONLINE on hpora01
NAME=ora.hpora02.ASM2.asm
TYPE=application
TARGET=OFFLINE
STATE=UNKNOWN on hpora02
NAME=ora.hpora02.LISTENER_hpora02.lsnr
TYPE=application
TARGET=OFFLINE
STATE=UNKNOWN on hpora02
NAME=ora.hpora02.gsd
TYPE=application
TARGET=ONLINE
STATE=UNKNOWN on hpora02
NAME=ora.hpora02.ons
TYPE=application
TARGET=OFFLINE
STATE=UNKNOWN on hpora02
NAME=ora.hpora02.vip
TYPE=application
TARGET=ONLINE
STATE=ONLINE on hpora02

I could have also attempted to stop them all using crs_stop –all, but it normally throws enough errors to force you do it manually one by one.

[oracle@hpora01 bin]$ crs_stop -all
Attempting to stop `ora.hpora01.ons` on member `hpora01`
Attempting to stop `ora.hpora02.ons` on member `hpora02`
`ora.hpora02.ons` on member `hpora02` has experienced an unrecoverable failure.
Human intervention required to resume its availability.
Stop of `ora.hpora01.ons` on member `hpora01` succeeded.
Attempting to stop `ora.hpora01.ASM1.asm` on member `hpora01`
Attempting to stop `ora.crmrac.crmrac2.inst` on member `hpora02`
`ora.crmrac.crmrac2.inst` on member `hpora02` has experienced an unrecoverable failure.
Human intervention required to resume its availability.
Attempting to stop `ora.hpora02.ASM2.asm` on member `hpora02`
`ora.hpora02.ASM2.asm` on member `hpora02` has experienced an unrecoverable failure.
Human intervention required to resume its availability.
Attempting to stop `ora.hpora02.LISTENER_hpora02.lsnr` on member `hpora02`
`ora.hpora02.LISTENER_hpora02.lsnr` on member `hpora02` has experienced an unrecoverable failure.
Human intervention required to resume its availability.
Attempting to stop `ora.crmrac.crmrac2.inst` on member `hpora02`
`ora.crmrac.crmrac2.inst` on member `hpora02` has experienced an unrecoverable failure.
Human intervention required to resume its availability.
Attempting to stop `ora.hpora02.ASM2.asm` on member `hpora02`
`ora.hpora02.ASM2.asm` on member `hpora02` has experienced an unrecoverable failure.
Human intervention required to resume its availability.
Attempting to stop `ora.hpora02.vip` on member `hpora02`
Stop of `ora.hpora02.vip` on member `hpora02` succeeded.
Stop of `ora.hpora01.ASM1.asm` on member `hpora01` succeeded.
Attempting to stop `ora.hpora01.LISTENER_hpora01.lsnr` on member `hpora01`
Stop of `ora.hpora01.LISTENER_hpora01.lsnr` on member `hpora01` succeeded.
Attempting to stop `ora.hpora01.vip` on member `hpora01`
Stop of `ora.hpora01.vip` on member `hpora01` succeeded.
CRS-0216: Could not stop resource 'ora.hpora02.ASM2.asm'.
CRS-0216: Could not stop resource 'ora.hpora02.ons'.
CRS-0216: Could not stop resource 'ora.hpora02.vip'.


For the very same reason we will go ahead and do it our way. Therefore, we need to stop our instances first.

[oracle@hpora01 bin]$ srvctl stop instance -d crmrac -i crmrac1
[oracle@hpora01 bin]$ srvctl stop instance -d crmrac -i crmrac2
Check our status
[oracle@hpora01 bin]$ crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora.crmrac.db application    OFFLINE   OFFLINE
ora....c1.inst application    OFFLINE   OFFLINE
ora....c2.inst application    OFFLINE   OFFLINE
ora....serv.cs application    ONLINE    UNKNOWN   hpora02
ora....ac1.srv application    OFFLINE   OFFLINE
ora....ac2.srv application    OFFLINE   OFFLINE
ora....SM1.asm application    OFFLINE   OFFLINE
ora....H4.lsnr application    OFFLINE   OFFLINE
ora....rh4.gsd application    ONLINE    UNKNOWN   hpora01
ora....rh4.ons application    OFFLINE   OFFLINE
ora....rh4.vip application    OFFLINE   OFFLINE
ora....SM2.asm application    OFFLINE   UNKNOWN   hpora02
ora....H4.lsnr application    OFFLINE   UNKNOWN   hpora02
ora....rh4.gsd application    ONLINE    UNKNOWN   hpora02
ora....rh4.ons application    OFFLINE   UNKNOWN   hpora02
ora....rh4.vip application    OFFLINE   OFFLINE

Stop the service

[oracle@hpora01 bin]$ srvctl stop service -d crmrac -s crmracserv
Check status again
[oracle@hpora01 bin]$ crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora.crmrac.db application    OFFLINE   OFFLINE
ora....c1.inst application    OFFLINE   OFFLINE
ora....c2.inst application    OFFLINE   OFFLINE
ora....serv.cs application    OFFLINE   OFFLINE
ora....ac1.srv application    OFFLINE   OFFLINE
ora....ac2.srv application    OFFLINE   OFFLINE
ora....SM1.asm application    OFFLINE   OFFLINE
ora....H4.lsnr application    OFFLINE   OFFLINE
ora....rh4.gsd application    ONLINE    UNKNOWN   hpora01
ora....rh4.ons application    OFFLINE   OFFLINE
ora....rh4.vip application    OFFLINE   OFFLINE
ora....SM2.asm application    OFFLINE   UNKNOWN   hpora02
ora....H4.lsnr application    OFFLINE   UNKNOWN   hpora02
ora....rh4.gsd application    ONLINE    UNKNOWN   hpora02
ora....rh4.ons application    OFFLINE   UNKNOWN   hpora02
ora....rh4.vip application    OFFLINE   OFFLINE


OK, so we need to stop those applications now.

[oracle@hpora01 bin]$ crs_stop ora.hpora01.gsd
Attempting to stop `ora.hpora01.gsd` on member `hpora01`
Stop of `ora.hpora01.gsd` on member `hpora01` succeeded.
[oracle@hpora01 bin]$ crs_stop ora.hpora02.ASM2.asm
Attempting to stop `ora.hpora02.ASM2.asm` on member `hpora02`
Stop of `ora.hpora02.ASM2.asm` on member `hpora02` succeeded.
[oracle@hpora01 bin]$ crs_stop ora.hpora02.LISTENER_hpora02.lsnr
Attempting to stop `ora.hpora02.LISTENER_hpora02.lsnr` on member `hpora02`
Stop of `ora.hpora02.LISTENER_hpora02.lsnr` on member `hpora02` succeeded.
[oracle@hpora01 bin]$ crs_stop ora.hpora02.gsd
Attempting to stop `ora.hpora02.gsd` on member `hpora02`
Stop of `ora.hpora02.gsd` on member `hpora02` succeeded.
[oracle@hpora01 bin]$ crs_stop ora.hpora02.ons
Attempting to stop `ora.hpora02.ons` on member `hpora02`
Stop of `ora.hpora02.ons` on member `hpora02` succeeded.

Check status

[oracle@hpora01 bin]$ crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora.crmrac.db application    OFFLINE   OFFLINE
ora....c1.inst application    OFFLINE   OFFLINE
ora....c2.inst application    OFFLINE   OFFLINE
ora....serv.cs application    OFFLINE   OFFLINE
ora....ac1.srv application    OFFLINE   OFFLINE
ora....ac2.srv application    OFFLINE   OFFLINE
ora....SM1.asm application    OFFLINE   OFFLINE
ora....H4.lsnr application    OFFLINE   OFFLINE
ora....rh4.gsd application    OFFLINE   OFFLINE
ora....rh4.ons application    OFFLINE   OFFLINE
ora....rh4.vip application    OFFLINE   OFFLINE
ora....SM2.asm application    OFFLINE   OFFLINE
ora....H4.lsnr application    OFFLINE   OFFLINE
ora....rh4.gsd application    OFFLINE   OFFLINE
ora....rh4.ons application    OFFLINE   OFFLINE
ora....rh4.vip application    OFFLINE   OFFLINE

OK all set , now lets bring them all online.

[oracle@hpora01 bin]$ crs_start -all
Attempting to start `ora.hpora02.vip` on member `hpora02`
Attempting to start `ora.hpora01.vip` on member `hpora01`
Start of `ora.hpora02.vip` on member `hpora02` succeeded.
Start of `ora.hpora01.vip` on member `hpora01` succeeded.
Attempting to start `ora.hpora01.ASM1.asm` on member `hpora01`
Attempting to start `ora.hpora02.ASM2.asm` on member `hpora02`
Start of `ora.hpora02.ASM2.asm` on member `hpora02` succeeded.
Attempting to start `ora.crmrac.crmrac2.inst` on member `hpora02`
Start of `ora.hpora01.ASM1.asm` on member `hpora01` succeeded.
Attempting to start `ora.crmrac.crmrac1.inst` on member `hpora01`
Start of `ora.crmrac.crmrac2.inst` on member `hpora02` succeeded.
Attempting to start `ora.hpora02.LISTENER_hpora02.lsnr` on member `hpora02`
Start of `ora.crmrac.crmrac1.inst` on member `hpora01` succeeded.
Attempting to start `ora.hpora01.LISTENER_hpora01.lsnr` on member `hpora01`
Start of `ora.hpora02.LISTENER_hpora02.lsnr` on member `hpora02` succeeded.
Start of `ora.hpora01.LISTENER_hpora01.lsnr` on member `hpora01` succeeded.
CRS-1002: Resource 'ora.hpora02.ons' is already running on member 'hpora02'
CRS-1002: Resource 'ora.hpora01.ons' is already running on member 'hpora01'
Attempting to start `ora.crmrac.crmracserv.crmrac1.srv` on member `hpora01`
Attempting to start `ora.hpora01.gsd` on member `hpora01`
Attempting to start `ora.crmrac.db` on member `hpora01`
Attempting to start `ora.crmrac.crmracserv.crmrac2.srv` on member `hpora02`
Attempting to start `ora.crmrac.crmracserv.cs` on member `hpora02`
Attempting to start `ora.hpora02.gsd` on member `hpora02`
Start of `ora.crmrac.crmracserv.crmrac2.srv` on member `hpora02` succeeded.
Start of `ora.crmrac.crmracserv.cs` on member `hpora02` succeeded.
Start of `ora.crmrac.db` on member `hpora01` succeeded.
Start of `ora.hpora02.gsd` on member `hpora02` succeeded.
Start of `ora.hpora01.gsd` on member `hpora01` succeeded.
Start of `ora.crmrac.crmracserv.crmrac1.srv` on member `hpora01` succeeded.
*CRS-0223: Resource 'ora.hpora01.ons' has placement error.
CRS-0223: Resource 'ora.hpora02.ons' has placement error.
*Don’t bother about those errors, as they just did not report back to us in the sequence they were started by the clusterware.

[oracle@hpora01 bin]$ crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora.crmrac.db application    ONLINE    ONLINE    hpora01
ora....c1.inst application    ONLINE    ONLINE    hpora01
ora....c2.inst application    ONLINE    ONLINE    hpora02
ora....serv.cs application    ONLINE    ONLINE    hpora02
ora....ac1.srv application    ONLINE    ONLINE    hpora01
ora....ac2.srv application    ONLINE    ONLINE    hpora02
ora....SM1.asm application    ONLINE    ONLINE    hpora01
ora....H4.lsnr application    ONLINE    ONLINE    hpora01
ora....rh4.gsd application    ONLINE    ONLINE    hpora01
ora....rh4.ons application    ONLINE    ONLINE    hpora01
ora....rh4.vip application    ONLINE    ONLINE    hpora01
ora....SM2.asm application    ONLINE    ONLINE    hpora02
ora....H4.lsnr application    ONLINE    ONLINE    hpora02
ora....rh4.gsd application    ONLINE    ONLINE    hpora02
ora....rh4.ons application    ONLINE    ONLINE    hpora02
ora....rh4.vip application    ONLINE    ONLINE    hpora02
[oracle@hpora01 bin]$

Conclusion:-

This article has discussed how the CRS commands can be used to fix/troubleshoot the quirkiness of our RAC.

Enable Users To View Workflow Notifications

Some times we get a email from customers that they are not able to retrigger PO approve notifications, we need to change this setup to view workflow for all users.
 
Actually only SYSADMIN user can view other users notifications,  if a requirement comes to enable everyone/certain group of people to view everyones notifications.
 
Login as SYSADMIN User
 
Navigation:
    System Administrator - Workflow : Administrator Workflow - Administration.
 
-> In the admin page you will see this message “Set * to grant admin privilege to all users”
-> Put * in the text box next to “Workflow System Administrator” and click Apply.  
 

Just in case if stil issue exist, we can also use below sql from APPS user  to update the workflow administrator.

update wf_resources set text='*'
where name='WF_ADMIN_ROLE'
and text<>'*';

commit; 

Happy EBS Learning !!

Wednesday, March 29, 2017

Startup Fails With ORA-01012: Not Logged On

Problem Description
Users cant able to login to database server. Though it allow sysdba to login, it does not allow to run any query. Sometimes it will show database is connected to an idle instance. But when we startup database it shall throw error ORA-01081: cannot start already-running ORACLE - shut it down first.

 $ sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.3.0 Production on Sat Mar 14 07:53:11 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select instance_name,status from v$instance;
ERROR at line 1:
ORA-01012: not logged on

SQL> desc v$instance
ERROR:
ORA-01012: not logged on

SQL> startup
ORA-01012: not logged on


Changes
Oracle Database has been shutdown , after applying GST Patch.

 CAUSE
An orphaned shared memory segment belonging to the ORACLE_SID still exists from a previous instance startup.

The command
ps -ef | grep $ORACLE_SID

shows no processes but with ORACLE_SID set the Oracle 'sysresv' utility shows a shared memory segment for a non-existing instance, e.g.

 $ sysresv

IPC Resources for ORACLE_SID "UAT" :
Shared Memory:
ID              KEY
4194312         0xffffffff
4194313         0xffffffff
4194314         0x060ca1c4
Oracle Instance not alive for sid "UAT"

Solution
On OS level, remove the orphaned shared memory segment using:

ipcrm -m <problem shared memory id>
$ ipcrm -m 4194312
$ ipcrm -m 4194313
$ ipcrm -m 4194314

$sqlplus '/as sysdba'

Connected to an idle instance.

SQL> startup;
ORACLE instance started.

Total System Global Area 3206836224 bytes
Fixed Size                  2225776 bytes
Variable Size            1962936720 bytes
Database Buffers         1224736768 bytes
Redo Buffers               16936960 bytes
Database mounted.

Oracle Database Opened.
FND_DCP.REQUEST_SESSION_LOCK/DBMS_LOCK.Request resultCall to establish_icm failed/function FND_DCP .Request_Session_Lock.


Error in Concurrent manager logfile
========================================================================
Starting UAT_0329@UAT Internal Concurrent Manager -- shell process ID 28966920

          logfile=/DEVPWEB/oracle/UAT/inst/apps/UAT_Development/logs/appl/conc/log/UAT_0329.mgr
          PRINTER=noprint
           mailto=appluat
          restart=N                                 
             diag=N
            sleep=30
             pmon=4
           quesiz=1
          Reviver is ENABLED

Routine &ROUTINE has attempted to start the internal concurrent manager.  The ICM is already running.  Contact you system administrator for further assistance.afpdlrq received an unsuccessful result from PL/SQL procedure or function FND_DCP.Request_Session_Lock.
Routine FND_DCP.REQUEST_SESSION_LOCK received a result code of 1 from the call to DBMS_LOCK.Request.
Possible DBMS_LOCK.Request resultCall to establish_icm failed
The Internal Concurrent Manager has encountered an error.

Review concurrent manager log file for more detailed information. : 29-MAR-2017 19:15:53 -

You can resolve above issue in two ways

1)bounce the database



Concurrent Processing - The Concurrent Manager Fails to Start on GSM Enabled Due to DBMS_LOCK.Request ResultCall Failed to Establish ICM [ID 245563.1]
=====================================================================================================================================================

Metalink says:


fact: Oracle Application Object Library
fact: Concurrent:GSM Enabled = Y
symptom: The Concurrent Manager fails to start
symptom: Routine &ROUTINE has attempted to start the internal
concurrent manager.
symptom: The ICM is already running.  Contact you system administrator for
further assistance.
symptom: a fpdlrq received an unsuccessful result from PL/SQL procedure or
function FND_DCP.Request_Session_Lock.
symptom: Routine FND_DCP.REQUEST_SESSION_LOCK received a result code of 1
from the call to DBMS_LOCK.Request.
symptom: Possible DBMS_LOCK.Request resultCall to establish_icm failed
symptom: The Internal Concurrent Manager has encountered an error.
change: Cloned Instance
cause: Database Locking issue



fix:

1. Stop all services and concurrent managers

2. Stop and restart the database
3. Restart the services and concurrent managers
4. Verify if the issue remains


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

2) Without bouncing the database.
i)Bring down Concurrent Manager but there are no (FND)process that are runing after getting above issue in CM logfile.
ii)check for locks related to CM if any please kill it and run cmclean.sql with apps user and start Concurrent manager.
iii)above issue will fix.


Check for CM lock using below query
==================================


SQL> SELECT v$access.sid, v$session.serial#
FROM v$session,v$access
WHERE v$access.sid = v$session.sid and v$access.object = 'FND_CP_FNDSM'
GROUP BY v$access.sid, v$session.serial#; 2 3 4

SID SERIAL#
---------- ----------
1639 19

SQL> alter system kill session '1639,19';

System altered.

SQL>



Run cmclean.sql
===========

SQL> show user
USER is "APPS"
SQL> @/u01/.wasiq/scripts/cmclean.sql
DOC>
DOC>WARNING : Do not run this script without explicit instructions
DOC>from Oracle Support
DOC>
DOC>
DOC>*** Make sure that the managers are shut down ***
DOC>*** before running this script ***
DOC>
DOC>*** If the concurrent managers are NOT shut down, ***
DOC>*** exit this script now !! ***
DOC>
DOC>#
If you wish to continue type the word 'dual':
If you wish to continue type the word 'dual': dual



-----------------------------------------------------------------------
-- Updating invalid process status codes in FND_CONCURRENT_PROCESSES

0 rows updated.


-----------------------------------------------------------------------
-- Updating running processes in FND_CONCURRENT_QUEUES
-- Setting running_processes = 0 and max_processes = 0 for all managers

54 rows updated.


-----------------------------------------------------------------------
-- Updating invalid control_codes in FND_CONCURRENT_QUEUES

Manager short name Control code
-------------------- ------------
FNDCRM N
FNDSCH N
STANDARD N
PODAMGR N
RCVOLTM N
PASMGR N
GEPSNOTML N
INVMGR N
INVTMRPM N
MRPMGR N
CRPINQMGR N
WFWSSVC N
WFALSNRSVC N
WFMLRSVC N
FNDCPOPP N
FNDSM_CTSSD N

16 rows updated.


54 rows updated.


-----------------------------------------------------------------------
-- Updating any Running or Terminating requests to Completed/Error

0 rows updated.


-----------------------------------------------------------------------
-- Updating any Runalone flags to 'N'

Updated 0 rows of runalone_flag in fnd_conflicts_domain to 'N'

-----------------------------------------------------------------------
Updates complete.
Type commit now to commit these updates, or rollback to cancel.
-----------------------------------------------------------------------

SQL> commit;

Commit complete.

SQL>

Start concurrent manager and retest the issue.Issue will fix now no need of db bounce !!!


DBUPGDIAG script to check integrity of database before 11g upgrade

col TODAY NEW_VALUE _DATE
col VERSION NEW_VALUE _VERSION
set termout off
select to_char(SYSDATE,’fmMonth DD, YYYY’) TODAY from DUAL;
select version from v$instance;
set termout on
set echo off
set feedback off
set head off
set verify off
Prompt
PROMPT Enter location for Spooled output:
Prompt
DEFINE log_path = &1
column timecol new_value timestamp
column spool_extension new_value suffix
SELECT to_char(sysdate,’dd-Mon-yyyy_hhmi’) timecol,’.log’ spool_extension FROM
sys.dual;
column output new_value dbname
SELECT value || ‘_’ output FROM v$parameter WHERE name = ‘db_name’;
spool &log_path/db_upg_diag_&&dbname&&timestamp&&suffix
set linesize 150
set pages 100
set trim on
set trims on
col Compatible for a35
col comp_id for a12
col comp_name for a40
col org_version for a11
col prv_version for a11
col owner for a12
col object_name for a40
col object_type for a40
col Wordsize for a25
col Metadata for a8
col ‘Initial DB Creation Info’ for a35
col ‘Total Invalid JAVA objects’ for a45
col ‘Role’ for a30
col ‘User Existence’ for a27
col “JAVAVM TESTING” for a15
Prompt
Prompt
set feedback off head off
select LPAD(‘*** Start of LogFile ***’,50) from dual;
select LPAD(‘Oracle Database Upgrade Diagnostic Utility’,44)||
LPAD(TO_CHAR(SYSDATE, ‘MM-DD-YYYY HH24:MI:SS’),26) from dual;
Prompt
Prompt ===============
Prompt Database Uptime
Prompt ===============
SELECT to_char(startup_time, ‘HH24:MI DD-MON-YY’) “Startup Time”
FROM v$instance;
Prompt
Prompt =================
Prompt Database Wordsize
Prompt =================
SELECT distinct(‘This is a ‘ || (length(addr)*4) || ‘-bit database’) “WordSize”
FROM v$process;
Prompt
Prompt ================
Prompt Software Verison
Prompt ================
SELECT * FROM v$version;
Prompt
Prompt =============
Prompt Compatibility
Prompt =============
SELECT ‘Compatibility is set as ‘||value Compatible
FROM v$parameter WHERE name =’compatible’;
Prompt
Prompt ================
Prompt Component Status
Prompt ================
Prompt
SET SERVEROUTPUT ON;
DECLARE

ORG_VERSION varchar2(12);
PRV_VERSION varchar2(12);
P_VERSION VARCHAR2(10);

BEGIN

SELECT version INTO p_version
FROM registry$ WHERE cid=’CATPROC’ ;

IF SUBSTR(p_version,1,5) = ‘9.2.0’ THEN

DBMS_OUTPUT.PUT_LINE(RPAD(‘Comp ID’, 8) ||RPAD(‘Component’,35)||
RPAD(‘Status’,10) ||RPAD(‘Version’, 15));

DBMS_OUTPUT.PUT_LINE(RPAD(‘ ‘,8,’-‘) ||RPAD(‘ ‘,35,’-‘)||
RPAD(‘ ‘,10,’-‘) ||RPAD(‘ ‘,15,’-‘));

FOR x in (SELECT SUBSTR(dr.comp_id,1,8) comp_id,
SUBSTR(dr.comp_name,1,35) comp_name,
dr.status Status,SUBSTR(dr.version,1,15) version
FROM dba_registry dr,registry$ r
WHERE dr.comp_id=r.cid and dr.comp_name=r.cname
ORDER BY 1)

LOOP

DBMS_OUTPUT.PUT_LINE(RPAD(SUBSTR(x.comp_id,1,8),8) ||
RPAD(SUBSTR(x.comp_name,1,35),35)||
RPAD(x.status,10) || RPAD(x.version, 15));
END LOOP;

ELSIF SUBSTR(p_version,1,5) != ‘9.2.0’ THEN

DBMS_OUTPUT.PUT_LINE(RPAD(‘Comp ID’, 8) ||RPAD(‘Component’,35)||
RPAD(‘Status’,10) ||RPAD(‘Version’, 15)||
RPAD(‘Org_Version’,15)||RPAD(‘Prv_Version’,15));

DBMS_OUTPUT.PUT_LINE(RPAD(‘ ‘,8,’-‘) ||RPAD(‘ ‘,35,’-‘)||
RPAD(‘ ‘,10,’-‘)||RPAD(‘ ‘,15,’-‘)||RPAD(‘ ‘,15,’-‘)||
RPAD(‘ ‘,15,’-‘));

FOR y in (SELECT SUBSTR(dr.comp_id,1,8) comp_id,
SUBSTR(dr.comp_name,1,35) comp_name, dr.status Status,
SUBSTR(dr.version,1,11) version,org_version,prv_version
FROM dba_registry dr,registry$ r
WHERE dr.comp_id=r.cid and dr.comp_name=r.cname
ORDER BY 1)

LOOP

DBMS_OUTPUT.PUT_LINE(RPAD(substr(y.comp_id,1,8), 8) ||
RPAD(substr(y.comp_name,1,35),35)||RPAD(y.status,10) ||
RPAD(y.version, 15)||RPAD(y.org_version,15)||RPAD(y.prv_version,15));

END LOOP;

END IF;
END;
/
SET SERVEROUTPUT OFF
Prompt
Prompt
Prompt ======================================================
Prompt List of Invalid Database Objects Owned by SYS / SYSTEM
Prompt ======================================================
Prompt
set head on
SELECT case count(object_name)
WHEN 0 THEN ‘There are no Invalid Objects’
ELSE ‘There are ‘||count(object_name)||’ Invalid objects’
END “Number of Invalid Objects”
FROM dba_objects
WHERE status=’INVALID’
AND owner in (‘SYS’,’SYSTEM’);
Prompt
DOC
################################################################

If there are no Invalid objects below will result in zero rows.

################################################################
#
Prompt
set feedback on
SELECT owner,object_name,object_type
FROM dba_objects
WHERE status=’INVALID’
AND owner in (‘SYS’,’SYSTEM’)
ORDER BY owner,object_type;
set feedback off
Prompt
Prompt ================================
Prompt List of Invalid Database Objects
Prompt ================================
Prompt
set head on
SELECT case count(object_name)
WHEN 0 THEN ‘There are no Invalid Objects’
ELSE ‘There are ‘||count(object_name)||’ Invalid objects’
END “Number of Invalid Objects”
FROM dba_objects
WHERE status=’INVALID’
AND owner not in (‘SYS’,’SYSTEM’);
Prompt
DOC
################################################################

If there are no Invalid objects below will result in zero rows.

################################################################
#
Prompt
set feedback on
SELECT owner,object_name,object_type
FROM dba_objects
WHERE status=’INVALID’
AND owner not in (‘SYS’,’SYSTEM’)
ORDER BY owner,object_type;
set feedback off
Prompt
Prompt ==============================================================
Prompt Identifying whether a database was created as 32-bit or 64-bit
Prompt ==============================================================
Prompt
DOC
###########################################################################

Result referencing the string ‘B023’ ==> Database was created as 32-bit
Result referencing the string ‘B047’ ==> Database was created as 64-bit
When String results in ‘B023’ and when upgrading database to 10.2.0.3.0
(64-bit) , For known issue refer below articles

Note 412271.1 ORA-600 [22635] and ORA-600 [KOKEIIX1] Reported While
Upgrading Or Patching Databases To 10.2.0.3
Note 579523.1 ORA-600 [22635], ORA-600 [KOKEIIX1], ORA-7445 [KOPESIZ] and
OCI-21500 [KOXSIHREAD1] Reported While Upgrading To 11.1.0.6

###########################################################################
#
Prompt
SELECT SUBSTR(metadata,109,4) “Metadata”,
CASE SUBSTR(metadata,109,4)
WHEN ‘B023’ THEN ‘Database was created as 32-bit’
WHEN ‘B047’ THEN ‘Database was created as 64-bit’
ELSE ‘Metadata not Matching’
END “Initial DB Creation Info”
FROM sys.kopm$;
Prompt
Prompt ===================================================
Prompt Number of Duplicate Objects Owned by SYS and SYSTEM
Prompt ===================================================
Prompt
Prompt Counting duplicate objects ….
Prompt
SELECT count(1)
FROM dba_objects
WHERE object_name||object_type in
(SELECT object_name||object_type
from dba_objects
where owner = ‘SYS’)
and owner = ‘SYSTEM’;
Prompt
Prompt =========================================
Prompt Duplicate Objects Owned by SYS and SYSTEM
Prompt =========================================
Prompt
Prompt Querying duplicate objects ….
Prompt
SELECT object_name, object_type
FROM dba_objects
WHERE object_name||object_type in
(SELECT object_name||object_type
FROM dba_objects
WHERE owner = ‘SYS’)
AND owner = ‘SYSTEM’;
Prompt
DOC

################################################################################

If any objects found please follow below article.
Note 1030426.6 How to Clean Up Duplicate Objects Owned by SYS and SYSTEM schema
Read the Exceptions carefully before taking actions.

################################################################################
#
Prompt
Prompt ================
Prompt JVM Verification
Prompt ================
Prompt
SET SERVEROUTPUT ON
DECLARE

V_CT NUMBER;
P_VERSION VARCHAR2(10);

BEGIN

— If so, get the version of the JAVAM component
EXECUTE IMMEDIATE ‘SELECT version FROM registry$ WHERE cid=”JAVAVM”
AND status 99’ INTO p_version;

SELECT count(*) INTO v_ct FROM dba_objects
WHERE object_type LIKE ‘%JAVA%’ AND owner=’SYS’;

IF SUBSTR(p_version,1,5) = ‘8.1.7’ THEN
IF v_ct>=6787 THEN
DBMS_OUTPUT.PUT_LINE(‘JAVAVM – Installed properly’);
ELSE
DBMS_OUTPUT.PUT_LINE(‘JAVAVM – Not Installed properly’);
END IF;
ELSIF SUBSTR(p_version,1,5) = ‘9.0.1’ THEN
IF v_ct>=8585 THEN
DBMS_OUTPUT.PUT_LINE(‘JAVAVM – Installed properly’);
ELSE
DBMS_OUTPUT.PUT_LINE(‘JAVAVM – Not Installed properly’);
END IF;
ELSIF SUBSTR(p_version,1,5) = ‘9.2.0’ THEN
IF v_ct>=8585 THEN
DBMS_OUTPUT.PUT_LINE(‘JAVAVM – Installed properly’);
ELSE
DBMS_OUTPUT.PUT_LINE(‘JAVAVM – Not Installed properly’);
END IF;
ELSIF SUBSTR(p_version,1,6) = ‘10.1.0’ THEN
IF v_ct>=13866 THEN
DBMS_OUTPUT.PUT_LINE(‘JAVAVM – Installed properly’);
ELSE
DBMS_OUTPUT.PUT_LINE(‘JAVAVM – Not Installed properly’);
END IF;
ELSIF SUBSTR(p_version,1,6) = ‘10.2.0’ THEN
IF v_ct>=14113 THEN
DBMS_OUTPUT.PUT_LINE(‘JAVAVM – Installed properly’);
ELSE
DBMS_OUTPUT.PUT_LINE(‘JAVAVM – Not Installed properly’);
END IF;
END IF;

EXCEPTION WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘JAVAVM – NOT Installed. Below results can be ignored’);

END;
/
SET SERVEROUTPUT OFF
Prompt
Prompt ================================================
Prompt Checking Existence of Java-Based Users and Roles
Prompt ================================================
Prompt
DOC

################################################################################

There should not be any Java Based users for database version 9.0.1 and above.
If any users found, it is faulty JVM.

################################################################################
#

Prompt
SELECT CASE count(username)
WHEN 0 THEN ‘No Java Based Users’
ELSE ‘There are ‘||count(*)||’ JAVA based users’
END “User Existence”
FROM dba_users WHERE username LIKE ‘%AURORA%’ AND username LIKE ‘%OSE%’;
Prompt
DOC

###############################################################

Healthy JVM Should contain Six Roles.
If there are more or less than six role, JVM is inconsistent.

###############################################################
#

Prompt
SELECT CASE count(role)
WHEN 0 THEN ‘No JAVA related Roles’
ELSE ‘There are ‘||count(role)||’ JAVA related roles’
END “Role”
FROM dba_roles
WHERE role LIKE ‘%JAVA%’;
Prompt
Prompt Roles
Prompt
SELECT role FROM dba_roles WHERE role LIKE ‘%JAVA%’;
set head off
Prompt
Prompt =========================================
Prompt List of Invalid Java Objects owned by SYS
Prompt =========================================
SELECT CASE count(*)
WHEN 0 THEN ‘There are no SYS owned invalid JAVA objects’
ELSE ‘There are ‘||count(*)||’ SYS owned invalid JAVA objects’
END “Total Invalid JAVA objects”
FROM dba_objects
WHERE object_type LIKE ‘%JAVA%’
AND status=’INVALID’
AND owner=’SYS’;
Prompt
DOC

#################################################################

Check the status of the main JVM interface packages DBMS_JAVA
and INITJVMAUX and make sure it is VALID.
If there are no Invalid objects below will result in zero rows.

#################################################################
#
Prompt
set feedback on
SELECT owner,object_name,object_type
FROM dba_objects
WHERE object_type LIKE ‘%JAVA%’
AND status=’INVALID’
AND owner=’SYS’;
set feedback off
Prompt
Prompt INFO: Below query should succeed with ‘foo’ as result.
set heading on
select dbms_java.longname(‘foo’) “JAVAVM TESTING” from dual;
set heading off
Prompt

set feedback off head off
select LPAD(‘*** End of LogFile ***’,50) from dual;
set feedback on head on
Prompt
spool off
Prompt
set heading off
set heading off
set feedback off
select ‘Upload db_upg_diag_&&dbname&&timestamp&&suffix from “&log_path” directory’
from dual;
set heading on
set feedback on
Prompt

MOVING OBJECTS

set serveroutput on
set lines 300
DECLARE   
    V_CURRENT_TABLESPACE VARCHAR2(50);
    V_DEST_TABLESPACE VARCHAR2(50);
BEGIN
    V_CURRENT_TABLESPACE := '&current_tablespace';
    V_DEST_TABLESPACE := '&destination_tablespace';

    FOR LIST_OWNER IN (SELECT DISTINCT OWNER FROM DBA_SEGMENTS WHERE TABLESPACE_NAME=V_CURRENT_TABLESPACE)
   
        DBMS_OUTPUT.PUT_LINE('MOVING TABLES:');
        DBMS_OUTPUT.PUT_LINE('------------------');
        FOR LIST_TABLES IN (SELECT SEGMENT_NAME, SEGMENT_TYPE FROM DBA_SEGMENTS WHERE OWNER=LIST_OWNER.OWNER AND TABLESPACE_NAME=V_CURRENT_TABLESPACE AND SEGMENT_TYPE='TABLE')
        LOOP
            DBMS_OUTPUT.PUT_LINE('MOVING ' || LIST_TABLES.SEGMENT_TYPE || ' ' || LIST_TABLES.SEGMENT_NAME || '...');
            V_SQL := 'ALTER TABLE "' || LIST_OWNER.OWNER  || '"."' || LIST_TABLES.SEGMENT_NAME || '" MOVE TABLESPACE ' || V_DEST_TABLESPACE;               
            EXECUTE IMMEDIATE V_SQL;
        END LOOP;
       
        DBMS_OUTPUT.PUT_LINE(' ');
        DBMS_OUTPUT.PUT_LINE('MOVING LOBS:');
        DBMS_OUTPUT.PUT_LINE('------------------');
       
        FOR LIST_LOBS IN (SELECT TABLE_NAME, COLUMN_NAME FROM DBA_LOBS WHERE OWNER=LIST_OWNER.OWNER AND TABLESPACE_NAME=V_CURRENT_TABLESPACE)
        LOOP
            DBMS_OUTPUT.PUT_LINE('MOVING LOB FROM TABLE ' || LIST_LOBS.TABLE_NAME || ' TO TABLESPACE ' || V_DEST_TABLESPACE || '...');
            V_SQL := 'ALTER TABLE ' || LIST_OWNER.OWNER || '.' ||  LIST_LOBS.TABLE_NAME || ' MOVE LOB(' || LIST_LOBS.COLUMN_NAME || ') STORE AS (TABLESPACE ' ||  V_DEST_TABLESPACE || ')';
            EXECUTE IMMEDIATE V_SQL;
        END LOOP;       
       
        DBMS_OUTPUT.PUT_LINE(' ');
        DBMS_OUTPUT.PUT_LINE('MOVING INDEXES:');
        DBMS_OUTPUT.PUT_LINE('------------------');
       
        FOR LIST_INDEXES IN (SELECT SEGMENT_NAME, SEGMENT_TYPE FROM DBA_SEGMENTS WHERE OWNER=LIST_OWNER.OWNER AND TABLESPACE_NAME='USERS' AND SEGMENT_TYPE='INDEX')
        LOOP
            DBMS_OUTPUT.PUT_LINE('MOVING ' || LIST_INDEXES.SEGMENT_TYPE || ' ' || LIST_INDEXES.SEGMENT_NAME || '...');           
            V_SQL := 'ALTER INDEX ' || LIST_OWNER.OWNER  || '."' || LIST_INDEXES.SEGMENT_NAME || '" REBUILD TABLESPACE ' || V_DEST_TABLESPACE;
            EXECUTE IMMEDIATE V_SQL;
        END LOOP;               
       
        DBMS_OUTPUT.PUT_LINE(' ');       
       
    END LOOP;
END;
/