How to avoid ORA-01565 on database STARTUP due to failure to open spfile

We have an init.ora file points to the spfile:

oracle@pind41(PROD1/DBTier):$ cat initPROD1.ora
spfile='+DSKGRP1/PROD/PARAMETERFILE/spfilePROD.ora'

we started up the database using the actual initPROD1.ora.final file

oracle@pind41(PROD1/DBTier):$ ls -l initPROD1.ora.final
-rw-r--r-- 1 oracle oinstall 5438 Feb 21 08:07 initPROD1.ora.final

Then create spfile using following command:

SQL> create spfile='+DSKGRP1/PROD/PARAMETERFILE/spfilePROD.ora' using pfile;

SQL> shutdown immediate;
SQL> startup nomount
ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '+DSKGRP1/PROD/PARAMETERFILE/spfilePROD.ora'
ORA-17503: ksfdopn:2 Failed to open file +DSKGRP1/PROD/PARAMETERFILE/spfilePROD.ora
ORA-01000: maximum open cursors exceeded

The above issue is due to creating spfile without specify the pfile so the default pfile was used to create the spfile. The default pfile is a pointer to the spfile.

Two ways to avoid the problem:

1. Follow Metalink Note 419509.1, specify the pfile with full path and file name when creating spfile.

2. rename initPROD1.ora to be initPROD1.ora.spfile and copy initPROD1.ora.final to initPROD1.ora, then startup database and create spfile. Copy initPROD1.ora.spfile to initPROD1.ora and then startup force.


REF:

ORA-01565 on database STARTUP due to failure to pen SPFILE[ID 419509.1].

schedule downtime and monitor adpatch, adadmin etc

1. schedule downtime: sitemap->maintenance->manage downtime schedules->schedule downtime

downtime page is at: http://{host}:{port}/OA_HTML/oam/nonUix/launchMode/restricted/downtime.html

2. OAM restricted mode

2.1 update contextfile, set s_trusted_admin_client_nodes to be the desktop ip address. (need to add load balancer ip address to if it is used).
2.2 Run adautocfg
2.3 Unlock database account ad_monitor and change password. Default password is lizard.
2.4 Run adadmin to put system in maintenance mode.
2.5 Start apache in restricted mode: adaprstctl.sh start|stop
2.6 login to OAM: http://{host}:{port}/servlets/weboam/oam/oamLogin
use ad_monitor to login to monitor the progress of adpatch / adadmin

login to the system using http://{host}:{port}/ to see the downtime maintenance page.

3. run adpatch or adadmin for system maintenance

4. remember to disable maintenance mode and run adaprstctl.sh stop

5. need to apply patch 4584326 if step 2.6 hangs.

6. Metalink Notes: 364236.1: Managing Downtime in Restricted Mode from OAM; 368503.1: Not able to login to Oracle applications manager

How to fix invalid database components

End users got invalid objects error and database eventually crashed. After starting up database, following components are invalidated: AMD (OLAP Catalog), CATPROC (Oracle Database Packages and Types), CONTEXT (Oracle Text) and ODM (Oracle Data Mining).

Attempt 1: use utlrp.sql to compile invalid objects: does not work.

Attempt 2: startup restrict and then run following: (does not work).
SQL>@?/rdbms/admin/catalog.sql
SQL>@?/rdbms/admin/catproc.sql
SQL>@?/rdbms/admin/utlrp.sql

Attempt 3: startup upgrade and then run following:
SQL>@?/rdbms/admin/catalog.sql
SQL>@?/rdbms/admin/catproc.sql
SQL>@?/rdbms/admin/utlrp.sql

It worked.

Here is the note from 10gR2 upgrade guide:

Note: The UPGRADE keyword allows you to open a pre-10.2
database. It also restricts logons to AS SYSDBA sessions, disables
system triggers, and performs additional operations that prepare the
environment for the upgrade.

Thus the upgrade keyword includes restricted mode. Furthermore, disables system triggers etc. Normally, upgrade key is used during an upgrade session. I believe we need to set _system_trig_enabled to be false under restricted mode before running catalog.sql, catproc.sql.

*._system_trig_enabled = TRUE # set FALSE for upgrades/migrations

Check and validate individual components:

SQL> Rem Indicate CATPROC load complete and check validity
SQL> BEGIN
2 dbms_registry.update_schema_list('CATPROC',
3 dbms_registry.schema_list_t('SYSTEM', 'OUTLN', 'DBSNMP'));
4 dbms_registry.loaded('CATPROC');
5 dbms_registry_sys.validate_catproc;
6 dbms_registry_sys.validate_catalog;
7 END;
8 /

Upgrade JDK to 1.6.0_017 in EBS 11i (11.5.10.2)

1. upgrade client plug-in to Jinitiator 1.3.1.x or JRE 1.5.0.x (native plug-in) before upgrading java to JDK 6.
2. down jdk 6.0 from http://java.sun.com/javase/downloads/index.jsp and run chmod
chmod +x $PATCH_TOP/jdk-6u17-linux-i586.bin
3. install jdk 6
mkdir $COMMON_TOP/util/java/1.6
cd $COMMON_TOP/util/java/1.6
$PATCH_TOP/jdk-6u17-linux-i586.bin
The installer will create a directory: jdk1.6.0_17 under $COMMON_TOP/util/java/1.6
4. apply ATG_PF.H.RUP5 5473858.
5. apply JDK 6 consolidated patch and other EBS interoperability patches
for 11.5.10.2: jdk 6 consolidated patch for 11.5.10CU2, patch 5622511 and contract core patch 5977502.
6. run the JDK 6 upgrade script to update configuration file
txkrun.pl –script=SetJDKCfg –contextfile=$CONTEXT_FILE –runautoconfig=Yes –appspass=appspwd –jdktop=$COMMON_TOP/util/java/1.6/jdk1.6.0_17
7. source env and check JRE related env variables are point to jdk1.6.0_17
8. replace $iAS_ORACLE_HOME/Apache/jdk to link to new jdk.
9. run adadmin to regenerate appsborg2.zip and product JAR files.

Reference:
Metalink Note 401561.1: Using J2SE version 6 with oracle e-business suite 11i

How to run adautocfg.sh and adcfgclone.sh in a script

Running adautocfg.sh and perl adcfgclone.pl will prompt for apps password. Here is the way to run it in non-interactive mode:

1. adautocfg.sh

adautocfg.sh appspass=appspw

using following method will generate invalid argument message but the message can be ignored.

echo appspw |adautocfg.sh

applptch@pind34(PTCH_806_BALANCE/Web:Forms:MWA):$ echo as46bw8 |adautocfg.sh
stty: standard input: Invalid argument
Enter the APPS user password :
stty: standard input: Invalid argument

2. adcfgclone.pl

echo appspw | perl adcfgclone.pl $CONTEXT_FILE

need to specify contextfile to prevent from cloning interview process for creating a new contextfile.

Note:
For suppressing stty error, add following in .bashrc

##to avoid following error
##stty: standard input: Invalid argument
if [ -t 0 ]; then
stty intr ^C
fi

menu_option for adadmin and adctrl

Menu Option Corresponding AD Administration Menu Choice
GEN_MESSAGES Generate message files
GEN_FORMS Generate form files
GEN_GRAPHICS Generate graphics files
GEN_REPORTS Generate reports files
GEN_JARS Generate product JAR files
RELINK Relink Applications programs
CREATE_ENV Create Applications environment file
COPY_FILES Copy files to destinations
CONVERT_CHARSET Convert character set
SCAN_APPLTOP Scan the APPL_TOP for exceptions
SCAM_CUSTOM_DIR Scan a CUSTOM directory for exceptions
LIST_SNAPSHOT List snapshots
UPDATE_CURRENT_VIEW Update current view snapshot
CREATE_SNAPSHOT Create named snapshot
EXPORT_SNAPSHOT Export snapshot to file
IMPORT_SNAPSHOT Import snapshot from file
DELETE_SNAPSHOT Delete named snapshot
CHECK_FILES Check for missing files
CMP_INVALID Compile APPS schema
CMP_MENU C ompile menu information
CMP_FLEXFIELDS Compile flexfield data in AOL tables
RELOAD_JARS Reload JAR files to database
VALIDATE_APPS Validate APPS schema
CREATE_GRANTS Recreate grants and synonyms for APPS schema
MAINTAIN_MLS Maintain multi-lingual tables
CHECK_DUAL Check DUAL table
MAINTAIN_MRC Maintain Multiple Reporting Currencies schema
CONVERT_MCURR Convert to Multiple Reporting Currencies
CONVERT_MULTI_ORG Convert to Multi-Org
ENABLE_MAINT_MODE Enable Maintenance Mode
DISABLE_MAINT_MODE Disable Maintenance Mode


$ adadmin defaultsfile=$APPL_TOP/admin/$TWO_TASK/admydefaults.txt menu_option=ENABLE_MAINT_MODE interactive=n



Menu Option Corresponding AD Control Menu Choice
ACKNOWLEDGE_QUIT Tell manager that a worker acknowledges quit
INFORM_FAILURE Tell manager that a worker failed its job
RESTART_JOB Tell worker to restart a failed job
SHOW_STATUS Show worker status
SHUTDOWN_WORKER Tell worker to quit
START_WORKER Restart a worker on the current machine

Note: Using any menu option on the command line, except for SHOW_STATUS, requires that you also use the worker_range= option. See the AD Controller command line help for details.

$ adctrl interactive=n defaults_file=$APPL_TOP/admin/prod/ctrldefs.txt \ logfile=adctr.log menu_option=SHOW_STATUS

ways to enable or disable or bypass maintenance mode during patching

1. run sql script

sqlplus -s apps/appspass @$AD_TOP/patch/115/sql/adsetmmd.sql ENABLE
sqlplus -s apps/appspass @$AD_TOP/patch/115/sql/adsetmmd.sql DISABLE

2. interactive adadmin

adadmin->(5) Change Maintenance Mode->(1) Enable Maintenance Mode

adadmin->(5) Change Maintenance Mode->(2) Disable Maintenance Mode


3. noninteractive adadmin

3.1 create defaults file:

adadmin defaultsfile=$APPL_TOP/admin/$TWO_TASK/admydefaults.txt

(for email notification prompt, need to answer No. answer Yes to give an email address)

(the passwords for system and applsys are hashed).

3.2 use the defaults file:

adadmin defaultsfile=$APPL_TOP/admin/$TWO_TASK/admydefaults.txt \ menu_option=DISABLE_MAINT_MODE interactive=n

adadmin defaultsfile=$APPL_TOP/admin/$TWO_TASK/admydefaults.txt \ menu_option=ENABLE_MAINT_MODE interactive=n


4. adpatch options=hotpatch to bypass maintenance mode

Need to follow Oracle Support to use this options while the system is up. For other patches, shutdown application first and then use hotpatch option to apply patches without enable maintenance mode.