Turn off Archive Logs in 10g RAC Database

In 8i and 9i database, the archive log can be turned off by
1. set LOG_ARCHIVE_START = false
2. startup mount
3. alter database noarchivelog;

In the RAC environment, all instances have to be in shutdown, before "alter database noarchivelog;" can be executed successfully in the mounted instance. Otherwise, you will get ORA-1126 error "database must be mounted exclusive and not open for this operation".

1. Make sure LOG_ARCHIVE_START: set to FALSE, CLUSTER_DATABASE: set to FALSE
2. Shut down all instances
3. Mount the database, but not open, by an exclusive instance
4. Enter "alter database noarchivelog;"
5. Set LOG_ARCHIVE_START=FALSE for each of the other instances
6. Shut down the exclusive instance and reset its CLUSTER_DATABASE to TRUE
7. Restart all instances using the modified parameters

CLUSTER_DATABASE is an identical setting in RAC and has to be restored back to TRUE so that there will not be problems starting the other RAC instances. "alter system set cluster_database=false scope=spfile" before shutdown will do it, if SPFILE shows up on "show parameter spfile;" command in Sql*Plus.

We spent hours in truning off archivelog mode because we did not learn there were guaranteed restore points in the database. If there are records in view v$restore_point, you need to run a DROP command to delete the guaranteed restore point. If you do not do that, you will hit error:
ORA-38781: cannot disable media recovery - have guaranteed restore points
or "ARCHIVELOG mode cannot be turned off because a guaranteed restore point exists." in Grid Control.

Once archive log mode is turned off, all instances in the RAC will in noarchive log mode!

"alter system archive log stop;" is obsolte in 10G.

SQL script to Enable/Disable the maintenance mode

Run below line to change the maintenance mode (without using adadmin):

$ sqlplus apps/appsPWD @$AD_TOP/patch/115/sql/adsetmmd.sql ENABLE | DISABLE

Must pass one of the following arguments:

   ENABLE - Enable Maintenance Mode
   DISABLE - Disable Maintenance Mode

A quick way to verify if the application is on Maintenance Mode or not, is by running the following as APPS user:

SQL> select fnd_profile.value('APPS_MAINTENANCE_MODE') from dual;

"NORMAL" -- Maintenance Mode is disabled
"MAINT" -- Maintenance Mode is enabled

After you disable the Maintenance Mode, you have to bounce the Apache server.

If the Maintenance Mode is not disabled, EBS page will direct to a warning message (instead of the login page):

Warning
The system has not been taken off maintenance mode completely. Please contact your System Administrator.

And, log $LOG_HOME/ora/10.1.3/j2ee/oacore/oacore_default_group_1/application.log may give error:

javax.servlet.ServletException
        at com.evermind[Oracle Containers for J2EE 10g (10.1.3.5.0) ].server.http.EvermindPageCont
ext.handlePageThrowable(EvermindPageContext.java:911)
        at com.evermind[Oracle Containers for J2EE 10g (10.1.3.5.0) ].server.http.EvermindPageCont
ext.handlePageException(EvermindPageContext.java:828)
        at _jsp._fnd._fnderror._jspService(_fnderror.java:491)

~~~~~~~~~~~~~~~~~~  enable maintenance mode ~~~~~~~~~~~~~~~~~
#!/bin/ksh
# tested in Linux OS
Passwd='appsPWD'

if [ -n "$CONTEXT_FILE" ]; then
  echo 'Running'
else
  echo 'environment variable $CONTEXT_FILE does not exit. Exit ...'
  exit 1;
fi;

$ORACLE_HOME/bin/sqlplus -s /nolog <<-ENDSQL
connect apps/${Passwd}
show user
@$AD_TOP/patch/115/sql/adsetmmd.sql ENABLE
ENDSQL
echo 'Confirm mode:'
$ORACLE_HOME/bin/sqlplus -S /nolog <<-ENDSQL1
connect apps/${Passwd}
select fnd_profile.value('APPS_MAINTENANCE_MODE') from dual;
exit;
ENDSQL1
date
exit 0

~~~~~~~~~~~~~~~~~~  disable maintenance mode ~~~~~~~~~~~~~~~~~
#!/bin/ksh
Passwd='appsPWD'

if [ -n "$CONTEXT_FILE" ]; then
  echo 'Running'
else
  echo 'environment variable $CONTEXT_FILE does not exit. Exit ...'
  exit 1;
fi;

$ORACLE_HOME/bin/sqlplus -s /nolog <<-ENDSQL
connect apps/${Passwd}
show user
@$AD_TOP/patch/115/sql/adsetmmd.sql DISABLE
ENDSQL
echo 'Confirm mode:'
$ORACLE_HOME/bin/sqlplus -S /nolog <<-ENDSQL1
connect apps/${Passwd}
select fnd_profile.value('APPS_MAINTENANCE_MODE') from dual;
exit;
ENDSQL1
date
exit 0
 

Version of Various Products

Discoverer 10g (iAS 10.1.2) version on UNIX platforms
1. Go to the mid tier $ORACLE_HOME/bin
2. Run the following command: strings -a dis51ws | grep -i 'Discoverer version'

Notes:
(1) For Discoverer 9i (iAS version 9.0.2), go to $ORACLE_HOME/discoverer902/bin)
(2) To find the iAS version, go to
% cd $ORACLE_HOME/config
% grep -i version ias.properties

11i comes with Discoverer 4i. To check Discoverer 4i version,
1. Login as applmgr, and go to $ORACLE_HOME/discwb4/bin
2. Execute the command: strings -a dis4ws grep -i 'Discoverer version'

Jinitiator version:
- Login as applmgr user
- Source the env file under $APPL_TOP (APPSORA.env)
- cd $APPL_TOP/admin
- "strings -a <SID>_<hostname>.xml | grep -i jinit_ver_dot"
This should return something like:
"<jinit_ver_dot oa_var="s_jinit_ver_dot">1.3.1.28</jinit_ver_dot>"

Jinitiator on standalone Discoverer is defined in files:
- $ORACLE_HOME/jinit/us/jinit-version.ini
$ more jinit-version.ini
[jinitInfo]
object_classid=clsid:CAFECAFE-0013-0001-0028-ABCDEFABCDEF
object_executable=jinit.exe#Version=1,3,1,28
mimetype=application/x-jinit-applet;version=1.3.1.28

- $ORACLE_HOME/discoverer/config/configuration.xml
<jvm name="jinitiator" classid="clsid:CAFECAFE-0013-0001-0028-ABCDEFABCDEF" plugin_setup="http://auohsjy02.com:11569/disco/jinit/jinit13125.exe" version="1.3.1.28" versionie="1,3,1,28" type="application/x-jinit-applet" plugin_page="/PlusPluginPage.uix" disco_archive="disco5i.jar" d4o_archive="d4o_doubl
e.jar"/>

Use Apache server for PL/SQL package / webpages

Apache server can be used to display PL/SQL package or PL/SQL Server Pages (PSP) in a database as webpages.

A.  owa_util.showpage to display HTML output in SQL*Plus

1. Make sure PL/SQL Web Toolkit is installed in the database.
SQL> select owa_util.get_version from dual;
GET_VERSION
---------------------------------
10.1.2.1.9

SQL> exec owa_util.showpage;
BEGIN owa_util.showpage; END;
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.OWA_UTIL", line 354
ORA-06512: at "SYS.HTP", line 665
ORA-06512: at "SYS.HTP", line 758
ORA-06512: at "SYS.HTP", line 1165
ORA-06512: at "SYS.OWA_UTIL", line 345
ORA-06512: at line 1

2. After variable initialization, it shall work
SQL> DECLARE
 name_arr OWA.VC_ARR;
 value_arr OWA.VC_ARR;
 BEGIN
 OWA.INIT_CGI_ENV(0, NAME_ARR, VALUE_ARR);
 END;
 /
PL/SQL procedure successfully completed.

SQL> declare
 begin
 htp.init;
 htp.htmlOpen;
 htp.headOpen;
 htp.title('Hello ');
 htp.headClose;
 htp.bodyOpen;
 htp.print('Hello World');
 htp.bodyClose;
 htp.htmlClose;
 end;
 /
PL/SQL procedure successfully completed.

SQL> set serveroutput on
SQL> exec owa_util.showpage;
Content-type: text/html
Content-length: 79
<html>
<head>
<title>Hello </title>
</head>
<body>
Hello World
</body>
</html>

PL/SQL procedure successfully completed.

B.  Display P/L SQL package in browsers

1. Configure Apache dads.conf file on the server (e.g. for Grid Control) by adding below lines to it (Reference: How to Obtain the SSO Username for a PL/SQL Application Doc ID: 269688.1). For EBS, the DAD file is at $IAS_ORACLE_HOME/Apache/modplsql/conf (See part C below).
<Location /pls/dba>
  SetHandler pls_handler
  Order deny,allow
  Allow from all
  AllowOverride None
  PlsqlDatabaseUsername          dbadmin
  PlsqlDatabasePassword          PSWD
  PlsqlDatabaseConnectString    dbServerName:1522:dbname
  PlsqlAuthenticationMode         Basic
  PlsqlDefaultPage                     dbadmin.home
  PlsqlDocumentTablename        dbadmin.wwdoc_document
  PlsqlDocumentPath                 docs
  PlsqlDocumentProcedure        dbadmin.wwdoc_process.process_download
</Location>

$ sqlplus system/PWD@dbname
SQL> create user dbadmin identified by PSWD
default tablespace users
temporary tablespace temp
quota unlimited on users;

SQL> grant resource to dbadmin;
SQL> grant create session to dbadmin;

$ sqlplus dbadmin/PSWD@dbname
SQL> CREATE or REPLACE procedure helloworld is BEGIN
owa.num_cgi_vars := 0;
htp.init;
htp.htmlOpen;
htp.headOpen;
htp.title('Hello ');
htp.headClose;
htp.bodyOpen;
htp.print('Hello World');
htp.bodyClose;
htp.htmlClose;
end;
/

2. Test the simple page:
http://gridcontrolserver:7777/pls/dba/dbadmin.helloworld

Notes: if password PSWD is not in the line " PlsqlDatabasePassword " of the dads.conf file, the page will ask for a login password.

3. For more complex package, use synonym to hide the schema name.

Assume a more complicated package is created in the database
SQL> CREATE OR REPLACE PACKAGE MSSQL AS
procedure home (modify varchar2 DEFAULT null);
... ... ...

SQL> show user
USER is "SYSTEM"
SQL> create role custom_pack NOT IDENTIFIED;
SQL> grant custom_pack to John;
SQL> grant custom_pack to Ray;
-- create synonym
SQL> create public synonym mssql for dbateam.mssql;
Synonym created.

SQL> conn dbateam/PSWD@dbname
Connected.
SQL> grant execute on mssql to custom_pack;

Now, http://gridcontrolserver:7777/pls/dba/mssql.home
works with John and Ray's database credentials.

C.  Run modplsql with Oracle EBS 11i & R12.1 Apache (Notes: mod_plsql is not supported in R12. See Doc ID 726711.1)

1. add one line (with real path) to $ORA_CONFIG_HOME/10.1.3/Apache/Apache/conf/oracle_apache.conf

include $IAS_ORACLE_HOME/Apache/modplsql/conf/plsql.conf

Notes on EBS R12.2.10 in 2021:  I did try below lines in R12.2.10, even MOD_PLSQL is not supported in R12 by default.

a). $ cd $FMW_HOME/webtier/instances/EBS_web_OHS1/config/OHS/EBS_web 

and add one line to file oracle_apache.conf

include ${ORACLE_INSTANCE}/config/${COMPONENT_TYPE}/${COMPONENT_NAME}/plsql.conf

b). Copy plsql.conf to here:

$ cp -p $ORACLE_HOME/Apache/modplsql/conf/plsql.conf .

It shall have lines like

# Configure Oracle HTTP Server to load mod_plsql

LoadModule plsql_module $ORACLE_HOME/Apache/modplsql/bin/modplsql.so

c).  The problem is now "adapcctl.sh start" will not start Apache service and give status 204. Log $FMW_HOME/webtier/instances/EBS_web_OHS1/diagnostics/logs/OHS/EBS_web/console~OHS~1.log has error:

Cannot load $ORACLE_HOME/Apache/modplsql/bin/modplsql.so into server: $ORACLE_HOME/Apache/modplsql/bin/modplsql.so: wrong ELF class: ELFCLASS32

See Doc ID 1492343.1 (Error occurs because the software has been compiled using 32 Bit Java and you are using 64 Bit Java in your environment) for Wrong ELF Class ELFCLASS32. R12.2 file system has only one modplsql.so file, and it is identical to the one in R12.1 (which may be copied over to R12.2 by the upgrade process). Seems it does not work with 64Bit JDK. The question for a workaround becomes: does modplsql.so have a version for 64Bit Java?

2. add lines to $IAS_ORACLE_HOME/Apache/modplsql/conf/dads.conf

<location /pls/EBSTRAIN>
 SetHandler pls_handler
 Order allow,deny
 Allow from All
 AllowOverride None
 PlsqlDatabaseUsername apps
 PlsqlDatabasePassword appsPWD
 plsqlDatabaseConnectString dbServerName.domain.com:1526:dbname
 PlsqlAuthenticationMode Basic
 PlsqlDefaultPage fnd_web.ping
</Location>

3. bounce Apache by adapcctl.sh! Then, below EBS built-in page shall work (assume 4453 is the ssl listener port).

https://EBSsiteName.domain.com:4453/pls/EBSTRAIN/fnd_web.ping

NOTES: To troubleshoot / trace mod plsql errors (specially HTTP-403 or 403 Forbidden error), turn "PlsqlLogEnable On" in file $IAS_ORACLE_HOME/Apache/modplsql/conf/plsql.conf.

4. test a custom page
$ sqlplus apps/appsPWD
SQL> show user
USER is "APPS"
SQL> CREATE or REPLACE procedure jy_helloworld is
 begin
 owa.num_cgi_vars := 0;
 htp.init;
 htp.htmlOpen;
 htp.headOpen;
 htp.title('Hello ');
 htp.headClose;
 htp.bodyOpen;
 htp.print('Hello World');
 htp.bodyClose;
 htp.htmlClose;
 end;
 / 

https://EBSsiteName.domain.com:4453/pls/EBSTRAIN/jy_helloworld
shall work and display "Hello World"

In addition, $FND_TOP/admin/template/httpd_conf_1013.tmp, which is used to generate $INST_TOP/ora/10.1.3/Apache/Apache/conf/httpd.conf, can define a custom URL for an OS directory path.

5.  To register your own PLSQL procedures or packages, you can go to System Admin Responsibility
Security > "Web PL/SQL" (it will also add a row to table fnd_enabled_plsql).
(Not sure exactly what this does)

Collect statistics on database level

When the performance on database monitoring tools, such as Grid Control, is poor, it may help by collecting statistics on database level because they select data from sys or DBA views.

SQL> exec DBMS_STATS.GATHER_SCHEMA_STATS('SYS');
SQL> exec DBMS_STATS.GATHER_SCHEMA_STATS('SYSTEM');
SQL> exec DBMS_STATS.GATHER_DICTIONARY_STATS;

-- script to gather database statistics
begin
DBMS_STATS.GATHER_DATABASE_STATS (
estimate_percent =>100,
block_sample=>FALSE,
method_opt=>'for all columns size auto',
degree=>null,
cascade=>true,
no_invalidate=>false,
options=>'GATHER STALE',
gather_sys=>FALSE);

DBMS_STATS.GATHER_DATABASE_STATS (
estimate_percent =>100,
block_sample=>FALSE,
method_opt=>'for all columns size auto',
degree=>null,
cascade=>true,
no_invalidate=>false,
options=>'GATHER EMPTY',
gather_sys=>FALSE);
end;

Connect Oracle Discoverer Administrator to 11i DB

Oracle Discoverer Administrator is a client tool. After it is installed on the PC, there are setup steps for you to connect it to the 11i database using 11i account and password. One of the steps (and maybe the last step if you have trouble in making the connection) is to create a system variable FND_SECURE on your PC and point it to the folder where you have saved the .dbc file(s).

Other steps (for Discoverer Administrator client version 10.1.2.48.18) include:
. Copy hostname_sid.dbc file from $FND_TOP/secure on the MT server to a folder on your PC.
. Discoverer Administrator --> Tools --> Connection --> Check "Connect to both standard and application EULs", and fill two fields there.
. Test your TNSNAME configuration by using SQL*Pluse to connect a database account.

How to get product code and application short name

Run below query to get product code and application short name for General Ledger. Replace "General Ledger" with your application module name.

SELECT A.product_code, A.application_short_name, T.DESCRIPTION
FROM FND_APPLICATION A,
FND_APPLICATION_TL T
WHERE A.APPLICATION_ID = T.APPLICATION_ID
AND T.APPLICATION_NAME = 'General Ledger';

Navigation:

Sysadmin (or Implementation Analyst) --> Application: Register

Trace Table and Column Name from a Field in a Form

Please note that not all fields on a form directly reference columns in the database. Some fields are derived from database procedures, code in the forms or libraries, etc.

1. Use Help > Record History
- Query the records in a given form
- Place the cursor on a field that you are interested in
- Choose Help > Record History
- The name of the base view used displays as Table Name:
- Go to the Sql*Plus
- Type desc , for example, desc mtl_system_items_fvl
- The columns of the table are displayed
- Review the columns and see if one matches the field that you are reviewing
(Note: if you see "Record history is not available here", it is normal per note 313678.1)

2. Use Help > Diagnostics > Examine
- Query the records in a given form
- Place the cursor on a field that you are interested in
- Choose Help > Diagnostics > Examine (enter APPS password)
- The block and field name that contain the data in the form are displayed as well as the value in the field.
- Many times the block name is similar to the table name in the database
- Many times the field name is similar to the column name in the database
- Also you can go back to the view in #1 and see if the column can be found

Above are copied from Metalink Note 259722.1, but usually extra steps are needed to get what you want.

When I tried to trace a sales order by navigation:
a). log into application with Responsibility = Order Mgmt Super User
b). Navigate Orders,Returns > Order Organinzer
c). Enter sales order 1000xxx, press Find
d). See Order Organizer with one row returned.
e). Then with cursor on row for order, press Open. Get Sales Order form
f). Use Help > Record History, About This Record window shows "Table Name: OE_ORDER_HEADERS_V".

I ran "select * from apps.OE_ORDER_HEADERS_V" and got 0 row returned. By using Toad, I saw this view is created by joining 37 tables! My bet on getting information on the sales order was a table called oe_order_headers_all.

g). The Sales Order form shows Salesperson, But I need the SALESREP_ID. So I place the cursor on Salesperson field
h). Select Help >Diagnostics> Examine, enter apps password
i). The new window gives "Block: ORDER, Field: SALESREP_MIR, Value: a_name". Note that ORDER is not close to a table or view name at all.
j). Place cursor next to Field and click on LOV, then find SALEREP_ID and press OK.
k). The retured value is for SALESREP_ID. It tells me that ORDER is a block name on the Oracle Form and there are other data fields in the block without being displayed.

Below are other ways to do the trace by the Metalin Note 259722.1:

4. Research the view
- Sometimes the steps in #1 and #2 lead you to a view
- Get more details about the view as well as the underlying tables and columns
with the following SQL:
set long 100000
col text format a70
set pages 100
spool view.lst
SELECT VIEW_NAME, TEXT
FROM DBA_VIEWS WHERE VIEW_NAME = UPPER('&VIEW_NAME')
and owner = 'APPS';
spool off

5. Use SYSTEM.LAST_QUERY
- Query the records in a given form
- Choose Help > Diagnostics > Examine > Select LOV: Block = SYSTEM, Field= LAST_QUERY
- The query run to get the data shown is displayed.
- You can look at the various columns and tables used and research further

6. Run a trace
- Open the form that you are interested in but do not run a query
- Choose Help > Diagnostics > Trace > Trace with binds
- The trace file name and location are listed. Note them down
- Query the records
- Immediately shut trace off with Help > Diagnostics > Trace > No Trace
- The trace file name and location are listed
- Go to the database machine and get the trace file
- Create a tkprof of the trace file with the command, tkprof sys=no
- The various queries run are displayed.
- You can look at the various columns and tables used and research further

7. Open the form in Oracle Developer
- Open the form that you are interested in the Oracle Applications
- Choose Help > About
- Note the form name
- You have to first configure Oracle Developer to open forms with their associated libraries
- Once this is done Open Developer
- Open the form in question
- Review the block, pl/sql procedures and libraries associated to the form.