Using new Oracle Database 11g ADRCI utility example

ADRCI is a command line utility that can be used in order to help find and fix problems in database.
The name stands for Automatic Diagnostic Repository Command Interpreter.

Command line exaples:

adrci> SHOW PROBLEMS --Used to show problems founded on databases
ADR Home = /u01/app/oracle/diag/rdbms/dbms/dbms:
*************************************************************************
PROBLEM_ID PROBLEM_KEY LAST_INCIDENT LASTINC_TIME
-------------------- ----------------------------------------------------------- -------------------- ----------------------------------------
9 ORA 7445 [jsv_udf_init()+62] 102465 2010-05-05 22:01:26.759000 -03:00
8 ORA 7445 [ktspfsall()+1079] 99828 2010-04-30 15:02:42.091000 -03:00
7 ORA 1578 91582 2010-04-27 21:05:44.602000 -03:00


adrci> show base --Used to show current ORACLE_BASE
ADR base is "/u01/app/oracle"

adrci> SET BASE /u01/app/oraebs --Uset to change ORACLE_BASE of database to be analyzed


adrci> set homepath diag/rdbms/dbms/dbms --Setting just one homepath

--To show ADR information use this command below
adrci> show control
ADRID SHORTP_POLICY LONGP_POLICY LAST_MOD_TIME LAST_AUTOPRG_TIME LAST_MANUPRG_TIME ADRDIR_VERSION ADRSCHM_VERSION ADRSCHMV_SUMMARY ADRALERT_VERSION CREATE_TIME
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------
1824989551 720 8760 2009-10-27 11:01:08.204025 -02:00 2010-05-03 09:49:12.845517 -03:00 2009-11-16 12:57:16.833107 -02:00 1 2 76 1 2009-10-27 11:01:08.204025 -02:00
1 rows fetched

Blank page when trying to access forms on Oracle Applications

--Blank page when trying to access forms on Oracle Applications

Steps to reproduce the problem:
1-Login to Oracle Applications
2-Go to any Responsibility
3-Try to access any form and nothing happens
4-If you try to start or stop forms (adfrmctl.sh) or reports (adrepctl.sh), errors like these one are displayed.

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

Executing service control script:
/u01/app/r11i/r11comn/admin/scripts/hmlr11_surucucu/adrepctl.sh start
script returned:
****************************************************

You are running adrepctl.sh version 115.33

Cannot reconnect to gateway

Cause: Application Object Library is unable to reconnect to your gateway ORACLE account after you unsuccessfully attempted to sign-on.

Action: Check that your gateway environment variable is set correctly.
starting Reports Server for hmlr11 on port 7070.
Cannot reconnect to gateway

Cause: Application Object Library is unable to reconnect to your gateway ORACLE account after you unsuccessfully attempted to sign-on.

Action: Check that your gateway environment variable is set correctly.

adrepctl.sh: exiting with status 0


.end std out.

.end err out.

#######################################################################
The first thing to check is if GUEST account is working fine using the SQL command below:

SQL> select fnd_web_sec.validate_login('GUEST','GUEST') from dual;

FND_WEB_SEC.VALIDATE_LOGIN('GUEST','GUEST')
--------------------------------------------------------------------------------
N


As you note, GUEST LOGIN is not working. Check the GUEST password in file $FND_TOP/secure/hostname_SID.dbc
# grep -i GUEST_USER_PWD /u01/app/r11i/r11appl/fnd/11.5.0/secure/surucucu_hmlr11.dbc

Check the GUEST user and password on your CONTEXT_FILE:
# grep guest $CONTEXT_FILE
GUEST
ORACLE

After checking and changing everything, run autoconfig.
cd $COMMON_TOP/admin/scripts/SID_hostname
./adautocfg.sh


Try to access your forms again.

Best Regards,
Paulo Portugal

Checking Dependency Objects using dba_dependencies or utldtree.sql

If I need to find out recursively dependent objects from an object I usually execute one of these two methods:

########################################################
1-Use the dba_dependencies view
col owner for a15
col name for a30
col type for a10
col referenced_owner for a15
col referenced_name for a30
col referenced_link_name for a10

select
*
from
dba_dependencies
where
name='TAB_EMP' and owner='PKG';

OWNER NAME TYPE REFERENCED_OWNE REFERENCED_NAME REFERENCED_TYPE REFERENCED DEPENDENCY_TYPE
--------------- ------------------------------ ---------- --------------- ------------------------------ ------------------ ---------- ---------------
PKG TAB_EMP TABLE SYS STANDARD PACKAGE HARD
PKG TAB_EMP TABLE PKG EMP_PERSON_TYP TYPE HARD

2-Use the utldtree.sql package. This package creates a procedure named deptree_fill and can be used as follows:

--run utldtree to create the procedure and view (user must be SYS in order to see all dependencies)
SQL>@?/rdbms/admin/utldtree.sql
SQL> exec deptree_fill(type =>'TABLE',schema => 'PKG',name => 'EMP_PERSON_OBJ_TABLE');

PL/SQL procedure successfully completed

SQL> select * from deptree order by seq#;

NESTED_LEVEL TYPE SCHEMA NAME SEQ#
------------ ---------- ------------------------------ ------------------------------ ----------
0 TABLE PKG EMP_PERSON_OBJ_TABLE 0

SQL> select * from ideptree;

DEPENDENCIES
--------------------------------------------------------------------------------
TABLE PKG.EMP_PERSON_OBJ_TABLE


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


Regards,
Paulo Portugal

Cloning Oracle Home and Changing User and Group Installation Owner

"If you are cloning your Oracle Home and need to change the user and/or group owner execute the following steps:

1-First, tar your source ORACLE_HOME:
tar cvhf OH_Source.tar $ORACLE_HOME

2-Then, move the tar to the new host and untar it.
cd ORACLE_HOME
tax xvf OH_Source.tar

3-Use the command below to change the owner of files. (login as root)

find . -user ora10g -exec chown oraprd {} \; --where ora10g is the old user and oraprd is the new user

4-Change the group owner using the following command: (login as root)
find . -group dba10g -exec dba11g {} \; --where the old group is dba10g and new is dba11g

5-Now, change information on config.o file that controls which user is the installation owner.
5.1- For Linux
cd $ORACLE_HOME/rdbms/lib
cp config.o config.o_backup
vi config.c
--Find and replace the old group name (in our case is dba10g) to the new value (dba11g)
--Old value
#define SS_DBA_GRP "dba10g"
#define SS_OPER_GRP "dba10g"
--New value
#define SS_DBA_GRP "dba11g"
#define SS_OPER_GRP "dba11g"

5.2- For AIX
cd $ORACLE_HOME/rdbms/lib
mv config.o config.o_backup
vi config.c
--Old value
.csect H.12.NO_SYMBOL{RO}, 3
.string "dba10g"
:
.csect H.14.NO_SYMBOL{RO}, 3
.string "dba10g"
--Old value
.csect H.12.NO_SYMBOL{RO}, 3
.string "dba11g"
:
.csect H.14.NO_SYMBOL{RO}, 3
.string "dba11g"

6-Finally, execute the clone.pl script to clone the database or use the runInstaller as follows:
--Using clone.pl script
cd $ORACLE_HOME/clone/bin
perl clone.pl ORACLE_HOME="/u01/app/oracle/product/11.2.0/db11g" ORACLE_HOME_NAME="OraDB11g"
--Or runInstaller
./runInstaller -clone -silent -ignorePreReq ORACLE_HOME="/u01/app/oracle/product/11.2.0/db11g" ORACLE_HOME_NAME="OraDB11g"

7-Run the root.sh script (loged in as root user)
$ORACLE_HOME/root.sh

8-Run ChangePerm script
$ORACLE_HOME/install/changePerm.sh


Your clone is done. Just check environment variables and start database and listener.


"

Start init.cssd reboot server

Hi,

If you are experiencing problems when trying to start CSS and the server goes down, try to find old init.css files from previous installations and delete these files or any reference to then :

--Run localconfig delete

--Delete files
$cd /etc
$rm init.crs init.crsd init.cssd init.evmd

$vi /etc/inittab (delete any reference to css)

--Run localconfig add

--Check for css
crsctl check crs (Just CSS should be online for non-RAC installations)


Regards,
Paulo Portugal

Patch Reporting - Oracle E-Business

There are 4 methods that you can use to generate patch report in Oracle EBS:

-> Using $AD_TOP/patch/115/sql/adphrept.sql.
Example:
$sqlplus apps/apps_password @adphrept.sql 2 ALL ALL ALL ALL ALL \
ALL ALL ALL ALL N N N N N my_patches.txt

->Using patchsets.sh script (download it updated using Oracle Metalink Note 139684.1.
Example:
patchsets.sh connect=apps_user/apps_pwd

-> By querying database using this query below:
set verify off
set pages 999
column patch_name format a20
column patch_type format a10
column creation_date format a20
column last_update_date format a20

undef 1

select
patch_name,
patch_type,
to_char(creation_date, 'yyyy-mm-dd hh24:mi') creation_date,
to_char(last_update_date, 'yyyy-mm-dd hh24:mi') last_update_date
from ad_applied_patches
where patch_name like upper('%&&1%')
union all
select
bug_number patch_name,
'BUG' patch_type,
to_char(creation_date, 'yyyy-mm-dd hh24:mi') creation_date,
to_char(last_update_date, 'yyyy-mm-dd hh24:mi') last_update_date
from ad_bugs
where bug_number like upper('%&1%')
order by patch_name
/



-> And finally, using the OAM (Oracle Application Manager) - Applied Patches session.


I hope that it help you find your applied patches.

Regards,
Paulo Portugal

Oracle EBS 12 "Function not Available to this Responsibility. Change responsibilities or contact your System Administrator."

If you are trying to access a custom forms in Oracle EBS and are receiveing this message below:
"Function not Available to this Responsibility. Change responsibilities or contact your System Administrator."

Fisrt, check if responsabilities, menus and forms were configured right.

In my case, I had to include the CUSTOM_TOP at the end of $ORA_CONFIG_HOME/10.1.2/forms/server/default.env file.

Example:
#############################
.
.
.
CUSTOMXXX_TOP=/ocfs02/appebs/PEBS/apps/apps_st/appl/f2c/12.0.0
#############################

Then, just bounce your forms using commands below:
./adformsctl.sh stop
./adformsctl.sh start

And see if it works. For me it works very well.

I hope that it could help you.

Regards,
Paulo Portugal