Command line to compile invalid objects

You can have a script to compile invalid 11i database objects, and put the script in cron.

1. Script

#!/bin/ksh
APPSPASS="pwd1"
SYSTEMPASS="pwd2"

. /path/to/EBSSITE_servername.env

echo ${APPSPASS}
sqlplus -s APPS @${AD_TOP}/admin/sql/adutlrcmp.pls APPLSYS ${APPSPASS} APPS ${APPSPASS} ${SYSTEMPASS} 8 0 NONE FALSE

2. Log will look like:

Enter password:
Arguments are:
AOL_schema = APPLSYS, AOL_password = *****,Schema_to_compile = APPS, Schema_to_compile_pw = *****,SYSTEM_password = *****, Total_workers = 8, Logical_worker_num = 0
Object_type_to_not_compile = NONE
Use_stored_dependencies = FALSE

Connected.
Running utl_recomp.recomp_parallel(8), if it exists
OWNER NUM_INVALID
------------------ -----------
BOLINF 3
APPS 1
Elapsed: 00:00:00.68
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.05
STATUS_MESSAGE
------------------------------------------------------------------------------
Running UTL_RECOMP.RECOMP_PARALLEL...
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.65
STATUS_MESSAGE
------------------------------------------------------------------------------
Successfully ran UTL_RECOMP.RECOMP_PARALLEL
no rows selected
Elapsed: 00:00:00.28
Done running utl_recomp.recomp_parallel
Commit complete.
Elapsed: 00:00:00.00

3. Cron line (like below)
0 * * * * /path/scriptname.sh > /path/to_log.log 2>&1 &

UPDATE: ${AD_TOP}/admin/sql/adutlrcmp.pls does not exist in R12.2. Use adadmin to compile APPS schema.

Find the target on a host for Grid Control

One database disappeared from Grid Control. I executed "agentca -d" on the target server to re-run the agent configuration assistant and look for new targets. It did bring the database back to Grid Control.

/u01/app/oracle/product/agent10g/bin ORA$ ./emctl status agent
Oracle Enterprise Manager 10g Release 10.2.0.1.0.
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent Version : 10.2.0.1.0
OMS Version : 10.2.0.1.0
Protocol Version : 10.2.0.0.0
Agent Home : /u01/app/oracle/product/agent10g
Agent binaries : /u01/app/oracle/product/agent10g
Agent Process ID : 585838
Parent Process ID : 569446
Agent URL : http://dssqa:3872/emd/main/
Repository URL : http://dssgrid:4889/em/upload/
Started at : 2008-08-15 19:44:26
Started by user : oracle
Last Reload : 2008-08-16 01:26:09
Last successful upload : 2008-08-16 13:42:01
Total Megabytes of XML files uploaded so far : 94.10
Number of XML files pending upload : 0
Size of XML files pending upload(MB) : 0.00
Available disk space on upload filesystem : 25.83%
Last successful heartbeat to OMS : 2008-08-16 13:41:51
---------------------------------------------------------------
Agent is Running and Ready

/u01/app/oracle/product/agent10g/bin ORA$ ./agentca -d
Stopping the agent using /u01/app/oracle/product/agent10g/bin/emctl stop agent
Oracle Enterprise Manager 10g Release 10.2.0.1.0.Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
Stopping agent ... stopped.
Running agentca using /u01/app/oracle/product/agent10g/oui/bin/runConfig.sh ORACLE_HOME=/u01/app/oracle/product/agent10g
ACTION=Configure MODE=Perform
RESPONSE_FILE=/u01/app/oracle/product/agent10g/response_file
RERUN=TRUE
INV_PTR_LOC=/etc/oraInst.loc

Perform - mode is starting for action: Configure
Perform - mode finished for action: Configure

You can see the log file: /u01/app/oracle/product/agent10g/cfgtoollogs/oui/confi gActions2008-08-16_01-43-06-PM.log

/u01/app/oracle/product/agent10g/bin ORA$ ls -al agentca
-rwxr-xr-x 1 oracle oinstall 657 Oct 19 2007 agentca

"alter profile ..." could shut down entire 11i applications

Co-worker misunderstood the concept of database password management when tried to set the length of 11i Application password expiration, and ran two lines as sysdba:

SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_GRACE_TIME 1;
SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME 2;

That action expired APPS password, and even SYSTEM password:
SQL> conn system/xxx@tns
ERROR:ORA-28002: the password will expire within 0 days
Connected.

and so the entire Oracle EBS instance went down.

To fix the issue, tried following steps:

1. Run the below sql's as sysdba to change the profile back.

1) ALTER PROFILE DEFAULT LIMIT PASSWORD_GRACE_TIME unlimited;
2) ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME unlimited;

2. Then try to connect as apps user in SQL*Plus. If it is requesting for a password change, cancel it.

3. Use normal approach of changing the apps password.
1) Stop MT services
2) Change APPS password using FNDCPASS
3) Change the password in wdbsvr.app and cgicmd.dat
4) Execute cmclean.sql connected to the database as APPS
5) Start MT services.

But, FNDCPASS keep getting error:
$ FNDCPASS apps/xxxxxx 0 Y system/xxxxxx SYSTEM APPLSYS xxxxxxx
APP-FND-01564: ORACLE error 28001 in AFPCOA
Cause: AFPCOA failed due to ORA-28001: the password has expired

Even, after APPS account is unlocked by SQL*Plus,
$ FNDCPASS apps/*** 0 Y system/*** system APPLSYS ***
fails with these error messages in logfile:
FNDCPASS was not able to decrypt password for ANONYMOUS during applsys password change.
FNDCPASS was not able to decrypt password for AUTOINSTALL during applsys password change.
FNDCPASS was not able to decrypt password for CONCURRENT MANAGER during applsys password change.
... 500 lines generated.

Metalink note 459601.1 suggests to change password manually for 500 users, which doesn't sound like a good solution.

Finally, we have to refresh the instance by using another instance.

Run 11i SQL files to fix an issue on OPP

All concurrent managers are running, but the Output Post Processor does not process requests and its log gives error:

[7/28/08 1:06:08 PM] [main] Starting GSF service with concurrent process id = 93868.
[7/28/08 1:06:08 PM] [main] Initialization Parameters: oracle.apps.fnd.cp.opp.OPPServiceThread:2:0:max_threads=5
[7/28/08 1:06:08 PM] [Thread-4] Service thread starting up.
[7/28/08 1:06:08 PM] [Thread-5] Service thread starting up.
[7/28/08 1:06:18 PM] [EXCEPTION] [OPPServiceThread0] java.sql.SQLException: ORA-00600: internal error code, arguments: [kqludp2],

[0x0A68239A8], [0], [], [], [], [], []
ORA-06512: at "APPS.FND_CP_OPP_IPC", line 85
ORA-06512: at line 1

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:589)

It seems that some OPP packages were deleted after patching (and FND_CP_GSM_OPP_AQ queue was not up and running), because below query only got 12 rows:

SQL> select OBJECT_NAME , CREATED, STATUS from all_objects where OBJECT_NAME like 'FND_CP_GSM%';

Per Metalink Note 311851.1, the fix is to re-create the packages by executing two seeded files under $FND_TOP. After the fix is applied, the same query returns 21 rows and OPP works well.

Here is how to run the two 11i files:

=========================
Ran script: afopp001.sql:
=========================
SQL> select name, user from v$database;

NAME USER
--------- ------------------------------
TJYTI SYSTEM

SQL> !date
Thu Jul 31 04:55:32 MST 2008

SQL> !pwd
/tjyti/applmgr/11510/fnd/11.5.0/patch/115/sql

SQL> !ls -ltr afopp001.sql
-rwxr-xr-x 1 aptjyti aatjyti 2079 Mar 21 2005 afopp001.sql

SQL> @afopp001.sql
Enter value for 1: system
Enter value for 2: ******
Connected.

PL/SQL procedure successfully completed.

Grant succeeded.

Commit complete.

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
[AMD64] aptjyti@auohsjyt02 >

=========================
Ran script: afopp002.sql:
=========================
SQL> select name, user from v$database;

NAME USER
--------- ------------------------------
TJYTI APPLSYS

SQL> !date
Thu Jul 31 04:59:23 MST 2008

SQL> !pwd
/tjyti/applmgr/11510/fnd/11.5.0/patch/115/sql

SQL> !ls -ltr afopp002.sql
-rwxr-xr-x 1 aptjyti aatjyti 4536 Feb 8 2006 afopp002.sql

SQL> @afopp002.sql
Enter value for 1: applsys
Enter value for 2: ******
Connected.

PL/SQL procedure successfully completed.
......