If APPL_TOP = /u01/apps/apps_st/appl, the main env file that shall be included in .profile (on Linux server) is
. /u01/apps/apps_st/appl/APPS< CONTEXT_NAME >.env
It calls three files:
. $APPL_TOP/custom< CONTEXT_NAME >.env (if there is one)
. $ORACLE_CONFIG_HOME/< CONTEXT_NAME >.env (or $INST_TOP/ora/10.1.2/< CONTEXT_NAME >.env)
. $APPL_TOP/< CONTEXT_NAME >.env
In database server, there is a < CONTEXT_NAME >.env under $ORACLE_HOME.
Area env files:
adovars_< CONTEXT_NAME >.env file
The adovars.env file, located in $APPL_TOP/admin, specifies the location of various files such as
Java files, HTML files, and JRE (Java Runtime Environment) files. The adovars.env file includes commentson the purpose and recommended setting of each variable. In a R12 environment, this file is maintained by AutoConfig, and should not be edited manually. It is called from the main
applications environment file, $APPL_TOP/< CONTEXT_NAME >.env
fndenv.env file
Located in the FND_TOP directory, this file sets additional environment variables used by the Application Object Library. For example, it sets APPLBIN as the name of the subdirectory where product executabl eprograms and shell scripts are stored (bin). This file is called by $APPL_TOP/< CONTEXT_NAME >.env and is maintained by AutoConfig. But, custom env variables can be put in this file by using below format to keep custom variables untouched by AutoConfig:
#Begin Customizations
CUST_TOP=/u01/apps/apps_st/appl/cust; export CUST_TOP
#End Customizations
devenv.env file
This file sets variables for linking third-party software and custom-developed applications with Oracle E-Business Suite, and for allowing you to compile and link custom OracleForms user exits and concurrent programs with Oracle EBS. This script is located in $FND_TOP/usrxit, and is automatically called by fndenv.env.
Other files:
adconfig.txt file
This file stores configuration information when Oracle E-Business Suite is installed. It is for AD utilities to run successfully, and is in the $APPL_TOP/admin directory. AD utility programs perform a variety of database and file management tasks.
dbc file
There is a .dbc file under $FND_SECURE containing various parameters which are responsible for the connection to the database upon receiving a request from Apache Jserv. AutoConfig generates this file after reading variables from $CONTEXT_FILE. See Doc ID 362851.1
default.env for Form
There is a default.env file under $INST_TOP/ora/10.1.2/forms/server for Form applications. If you have custom-developed forms, you can add lines to this file to tell the location for custom form files:
#Begin Customizations
CUST_TOP=/u01/apps/apps_st/appl/cust
#End Customizations
Note: This file does not define env variables. If the path
is wrong in this file, Oracle EBS will give error:
FRM-40010 Cannot read from
/....../xxx.fmx
10GiAS home:
To apply patch to or work on 10gAS (10.1.3.X) in EBS R12.1, go to directory $INST_TOP/ora/10.1.3 and source the .env file there. Now, the ORACLE_HOME points to a different location (with 10.1.3 in the path), which holds10gAS components, such as Apache/modplsql, oc4j, etc.
Thursday, December 4, 2014
Monday, October 27, 2014
Apache httpd services fail to start
R12 Apache server does not start. But the log file reports exiting status 0, which means normal.
Error
--> Process (index=1,uid=41488097,pid=14132)
failed to start a managed process after the maximum retry limit
Log:
/u02/app/EBSPROD/inst/apps/<CONTEXT name>/logs/ora/10.1.3/opmn/HTTP_Server~1.log
10/26/2014-10:04:39 : : adapcctl.sh: existing with status 0
HTTP_Server~1.log file does not give any useful information as well:
14/10/26 10:04:39 Start process
--------
/u02/app/EBSPROD/inst/apps/<CONTEXT name>/ora/10.1.3/Apache/Apache/bin/apachectl startssl: execing httpd
After deleted httpd.pid file in $INST_TOP/pids/10.1.3/Apache and added below debug options to httpd.conf file in $ORA_CONFIG_HOME/10.1.3/Apache/Apache/conf, "adapcctl.sh start" can start the httpd services. See Doc ID 422419.1 on how to enable debug logs.
LogLevel debug
OraLogMode oracle
OraLogSeverity TRACE:32
OraLogDir /u02/app/EBSPROD/inst/apps/<CONTEXT name>/logs/ora/10.1.3/Apache/oracle
But, The real fix seems to delete Apache folder under $LOG_HOME/ora/10.1.3 with all httpd log files, and then re-create it (and sub-folder oracle). I believe the cause was some log file exceeded 2GB limit in size.
Troubleshooting:
Logs for troubleshooting on startup:
$LOG_HOME/ora/10.1.3/Apache
$LOG_HOME/ora/10.1.3/opmn/ <= Check file HTTP_Server~1.log here if Apache does not start & no logs
$LOG_HOME/ora/10.1.3/j2ee/oacore/oacore_default_group_1/application.log <= Notes: For example, this file got 8GB in size due to all 10.1.3.5.0 Container error entries (bug 10126440 ? )
$LOG_HOME/ora/10.1.3/j2ee/forms/forms_default_group_1/application.log
Other files can be checked with egrep -i 'fail|error' to see if any issues, when getting "Page not found":
$LOG_HOME/ora/10.1.3/opmn/oacore_default_group_1/oacorestd.err
$LOG_HOME/appl/rgf/javacache.log (Java Object Cache (JOC) log, $APPLRGF)
- start APACHE by adapcctl.sh start
Make sure below lines do not give back any error:
./adapcctl.sh status
./adalnctl.sh status
If Apache is up and running with enabled ssl, below URL shall work
https://siteName.domian.com:ssl_port
siteName.domain.com can be replaced by its DNS ip address for troubleshooting (with possible "Certificate error" because the certs file is issued for the original site URL).
By this time, the re-direction to login page will not work because it needs oacore process running.
- start OACORE process (in 10.1.3 Oracle_Home) by adoacorectl.sh start
If the login page is unavailable immediately, check the opmn log first
$LOG_HOME/ora/10.1.3/opmn/default_group~oacore~default_group~1.log
If the login page gets internal errors or hangs, but below URLs work (in R12):
https://ip_address:ssl_port/OA_HTML/ServletPing
https://ip_address:ssl_port/OA_HTML/jsp/fnd/aoljtest.jsp (it may not be configured in some sites)
https://siteName.domian.com:ssl_port/OA_HTML/jsp/fnd/aoljtest.jsp -- bring up a test HTML form
check log files in $LOG_HOME/ora/10.1.3/j2ee/oacore/oacore_default_group_1 to get the real error.
-start FORMS process by adformsctl.sh start
After forms services are running, you shall be able to open forms by JRE popup. If forms does not open, check logs in $LOG_HOME/ora/10.1.3/j2ee/forms/forms_default_group_1 for any error.
- start OAFM OC4J by adoafmctl.sh start
If Apache is running on a server with IP 188.xx.67.77, TELNET on port s_webport (8020) returns below message in my R12.1 environment:
$ telnet 188.xx.67.77 8020
Trying...
Connected to 188.xx.67.77.
Escape character is '^]'.
GET
<!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML 2.0//EN">
<HTML><HEAD>
<TITLE>400 Bad Request</TITLE>
</HEAD><BODY>
<H1>Bad Request</H1>
Your browser sent a request that this server could not understand.<P>
invalid request-URI <P>
</BODY></HTML>
Connection closed.
But "$ telnet 188.xx.67.77 4463" and GET return only "Connection closed." (for unknown reason). 4463 is the ssl_prot in the ssl-enabled configuration.
Most of times, Apache works fine. The problem may come from company Security policy change, fire wall or F5 network settings. All you need is to provide evidence as proof.
NOTE1: after using "adapcctl.sh start" to start Apache services, do not use "adapcctl.sh stop" to stop httpd services, which may leave file httpd.pid on the file system and leave opmn processes still running. Instead, Use "adopmnctl.sh stopall" to stop opmn processes and httpd processes.
NOTE2: All Apaches logs and opmn logs (or log directories) can be deleted safely, including
$LOG_HOME/appl/admin/log/adapcctl.text
$LOG_HOME/ora/10.1.3/opmn/HTTP_Server~1.log
See Doc ID 1964851.1 to clean the logs and enable debug.
NOTE3: If a port defined for Apache is not available, Apache will not start. One day, "adstrtal.sh" gave a similar message above. After added debug options to Apache conf file, I found the problem "make_sock: could not bind to port 4482". Usually profile option APPS_SERVLET_AGENT has the site URL (name and port).
Error
--> Process (index=1,uid=41488097,pid=14132)
failed to start a managed process after the maximum retry limit
Log:
/u02/app/EBSPROD/inst/apps/<CONTEXT name>/logs/ora/10.1.3/opmn/HTTP_Server~1.log
10/26/2014-10:04:39 : : adapcctl.sh: existing with status 0
HTTP_Server~1.log file does not give any useful information as well:
14/10/26 10:04:39 Start process
--------
/u02/app/EBSPROD/inst/apps/<CONTEXT name>/ora/10.1.3/Apache/Apache/bin/apachectl startssl: execing httpd
After deleted httpd.pid file in $INST_TOP/pids/10.1.3/Apache and added below debug options to httpd.conf file in $ORA_CONFIG_HOME/10.1.3/Apache/Apache/conf, "adapcctl.sh start" can start the httpd services. See Doc ID 422419.1 on how to enable debug logs.
LogLevel debug
OraLogMode oracle
OraLogSeverity TRACE:32
OraLogDir /u02/app/EBSPROD/inst/apps/<CONTEXT name>/logs/ora/10.1.3/Apache/oracle
But, The real fix seems to delete Apache folder under $LOG_HOME/ora/10.1.3 with all httpd log files, and then re-create it (and sub-folder oracle). I believe the cause was some log file exceeded 2GB limit in size.
Troubleshooting:
Logs for troubleshooting on startup:
$LOG_HOME/ora/10.1.3/Apache
$LOG_HOME/ora/10.1.3/opmn/ <= Check file HTTP_Server~1.log here if Apache does not start & no logs
$LOG_HOME/ora/10.1.3/j2ee/oacore/oacore_default_group_1/application.log <= Notes: For example, this file got 8GB in size due to all 10.1.3.5.0 Container error entries (bug 10126440 ? )
$LOG_HOME/ora/10.1.3/j2ee/forms/forms_default_group_1/application.log
Other files can be checked with egrep -i 'fail|error' to see if any issues, when getting "Page not found":
$LOG_HOME/ora/10.1.3/opmn/oacore_default_group_1/oacorestd.err
$LOG_HOME/appl/rgf/javacache.log (Java Object Cache (JOC) log, $APPLRGF)
- start APACHE by adapcctl.sh start
Make sure below lines do not give back any error:
./adapcctl.sh status
./adalnctl.sh status
If Apache is up and running with enabled ssl, below URL shall work
https://siteName.domian.com:ssl_port
siteName.domain.com can be replaced by its DNS ip address for troubleshooting (with possible "Certificate error" because the certs file is issued for the original site URL).
By this time, the re-direction to login page will not work because it needs oacore process running.
- start OACORE process (in 10.1.3 Oracle_Home) by adoacorectl.sh start
If the login page is unavailable immediately, check the opmn log first
$LOG_HOME/ora/10.1.3/opmn/default_group~oacore~default_group~1.log
If the login page gets internal errors or hangs, but below URLs work (in R12):
https://ip_address:ssl_port/OA_HTML/ServletPing
https://ip_address:ssl_port/OA_HTML/jsp/fnd/aoljtest.jsp (it may not be configured in some sites)
https://siteName.domian.com:ssl_port/OA_HTML/jsp/fnd/aoljtest.jsp -- bring up a test HTML form
check log files in $LOG_HOME/ora/10.1.3/j2ee/oacore/oacore_default_group_1 to get the real error.
-start FORMS process by adformsctl.sh start
After forms services are running, you shall be able to open forms by JRE popup. If forms does not open, check logs in $LOG_HOME/ora/10.1.3/j2ee/forms/forms_default_group_1 for any error.
- start OAFM OC4J by adoafmctl.sh start
If Apache is running on a server with IP 188.xx.67.77, TELNET on port s_webport (8020) returns below message in my R12.1 environment:
$ telnet 188.xx.67.77 8020
Trying...
Connected to 188.xx.67.77.
Escape character is '^]'.
GET
<!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML 2.0//EN">
<HTML><HEAD>
<TITLE>400 Bad Request</TITLE>
</HEAD><BODY>
<H1>Bad Request</H1>
Your browser sent a request that this server could not understand.<P>
invalid request-URI <P>
</BODY></HTML>
Connection closed.
But "$ telnet 188.xx.67.77 4463" and GET return only "Connection closed." (for unknown reason). 4463 is the ssl_prot in the ssl-enabled configuration.
Most of times, Apache works fine. The problem may come from company Security policy change, fire wall or F5 network settings. All you need is to provide evidence as proof.
NOTE1: after using "adapcctl.sh start" to start Apache services, do not use "adapcctl.sh stop" to stop httpd services, which may leave file httpd.pid on the file system and leave opmn processes still running. Instead, Use "adopmnctl.sh stopall" to stop opmn processes and httpd processes.
NOTE2: All Apaches logs and opmn logs (or log directories) can be deleted safely, including
$LOG_HOME/appl/admin/log/adapcctl.text
$LOG_HOME/ora/10.1.3/opmn/HTTP_Server~1.log
See Doc ID 1964851.1 to clean the logs and enable debug.
NOTE3: If a port defined for Apache is not available, Apache will not start. One day, "adstrtal.sh" gave a similar message above. After added debug options to Apache conf file, I found the problem "make_sock: could not bind to port 4482". Usually profile option APPS_SERVLET_AGENT has the site URL (name and port).
Wednesday, September 17, 2014
Trun on trace in EBS forms and Forms Node
EBS R12.1 has different ways to trace a user's session. Usually, apps tracing will help address issues on performance.
1. Trace a concurrent program with database .trc file
1) As System Administrator, go to Concurrent-> programs -> Define, and query program PROGRAM_NAME ie ARRGTA, or RAXTRX
2) Enable (or make sure) the check box called 'Enable Trace' and save
3) Before starting trace, make sure the following DB parameters are set:
MAX_DUMP_FILE_SIZE = UNLIMITED
TIMED_STATISTICS = TRUE
STATISTICS_LEVEL = ALL
If they are not set as above, run the following as SYS:
SQL> alter system set MAX_DUMP_FILE_SIZE=UNLIMITED;
SQL> alter system set TIMED_STATISTICS=TRUE;
SQL> alter system set STATISTICS_LEVEL=ALL;
4) Ask the user to rerun the report reproducing the issue
5) After the process finishes, run tkprof for the trace file with sort=exeela,prsela,fchela and
explain=<apps owner>/<apps password>. Example:
tkprof <sourcefile> <outputfile> sys=no explain=apps/<pw> sort=exeela,prsela,fchela
Please make sure the process already finished. If the process has not finished the rows column in tkprof will be all zeros and useless. Be sure to use the TKPROF under the RDBMS Oracle Home /bin directory. The TKPROF under the Applications Oracle Home will not yield accurate results.
2. Oracle Support steps to trace just one session (when trace can not be turned on on a program) using SQL code.
1) Make sure trace is NOT enabled for any of the concurrent programs we will be running:
System Administrator > Concurrent > Programs > Define
Query the program ARRGTA
Make sure trace is DISABLED (It is important trace is disabled for the program so it does not interfere with the rest of the steps)
2) Create the following Procedure as database user APPS:
CREATE OR REPLACE PROCEDURE cp_level12_trace_util AS
req_id number:=fnd_global.conc_request_id;
prog_id number:=fnd_global.conc_program_id;
appl_id number:=fnd_global.RESP_APPL_ID;
prog_name varchar2(128);
trc_label varchar2(128);
BEGIN
if req_id > 0 then
select max(concurrent_program_name) into prog_name
from fnd_concurrent_programs
where concurrent_program_id=prog_id
and application_id=appl_id;
trc_label:='CP_'||prog_name||'_REQ_'||req_id;
FND_CTL.FND_SESS_CTL(''
,''
,''
,'TRUE'
,''
,'ALTER SESSION SET MAX_DUMP_FILE_SIZE=UNLIMITED');
FND_CTL.FND_SESS_CTL(''
,''
,''
,'TRUE'
,''
,'ALTER SESSION SET TIMED_STATISTICS=TRUE');
FND_CTL.FND_SESS_CTL(''
,''
,''
,'TRUE'
,''
,'ALTER SESSION SET STATISTICS_LEVEL=ALL');
FND_CTL.FND_SESS_CTL(''
, ''
, ''
, 'TRUE'
, ''
, 'ALTER SESSION SET TRACEFILE_IDENTIFIER='||''''
|| trc_label
|| ''''
|| ' EVENTS ='
|| ''''
|| ' 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12 '
|| '''');
end if;
END;
/
3) Set the following profile option at user level, only for the user who reproduces the issue:
Profile option: Initialization SQL Statement - Custom
Value: begin cp_level12_trace_util; end;
NOTES: Be careful, as sometimes it may block user from login, and also see FND_INIT_SQL may cause error.
4) Run the concurrent request by the user, and upload all trace files, which have the request_id in their name (It will have something like CP_PROGNAME_REQ_999999, where PROGNAME is the short name of the program, and 999999 is the request id). If Trace files are too big, do not upload them for now
5) Run tkprof for the above trace files, and upload the output:
tkprof <trace file> <output file> sort=exeela,prsela,fchela explain=apps/<password>
Make sure the process already finished (if it hangs, please leave it running for a few hours before canceling). If the process has not finished the rows column in tkprof will be all zeros.
6) Upload also the log files for the concurrent requests
7) Do not forget to set the profile option “Initialization SQL Statement – Custom” back to BLANK afterwards.
3. Trace EBS Forms session
1) Change Profile option Utilities:Diagnostics to "Yes" at user level
2) Help --> Diagnostics --> Trace --> to choose a tracing level. Then, a popup box will tell where the trace file .trc is saved.
4. Find which node an EBS R12 session is connecting to
1) Change Profile option FND: Diagnostics to "Yes" at user level
2) Help -> About Oracle Applications (below section will tell which form node you have connected):
----------------------------------------
Forms Server
----------------------------------------
Oracle Forms Version : 10.1.2.3.0
Application Object Library : 12.0.0
Machine : node_name.domain.COM
NOTES:
- Turn "FND: Diagnostics" profile to Yes at user level will also add "About this Page" to EBS Homepage's left bottom after login and add "Diagnostics" on the right top of OA Framework pages. Clean the cache if they do not show up immediately after re-login.
- This profile option is different from turning "Utilities:Diagnostics" to Yes, which enables tracing on sub-menus of Help on GUI forms.
- I found that even "FND: Diagnostics" profile is set to No, "About this Page" may still shows up. That could be controlled by other things, such as other profile options for Personalization:
1. Trace a concurrent program with database .trc file
1) As System Administrator, go to Concurrent-> programs -> Define, and query program PROGRAM_NAME ie ARRGTA, or RAXTRX
2) Enable (or make sure) the check box called 'Enable Trace' and save
3) Before starting trace, make sure the following DB parameters are set:
MAX_DUMP_FILE_SIZE = UNLIMITED
TIMED_STATISTICS = TRUE
STATISTICS_LEVEL = ALL
If they are not set as above, run the following as SYS:
SQL> alter system set MAX_DUMP_FILE_SIZE=UNLIMITED;
SQL> alter system set TIMED_STATISTICS=TRUE;
SQL> alter system set STATISTICS_LEVEL=ALL;
4) Ask the user to rerun the report reproducing the issue
5) After the process finishes, run tkprof for the trace file with sort=exeela,prsela,fchela and
explain=<apps owner>/<apps password>. Example:
tkprof <sourcefile> <outputfile> sys=no explain=apps/<pw> sort=exeela,prsela,fchela
Please make sure the process already finished. If the process has not finished the rows column in tkprof will be all zeros and useless. Be sure to use the TKPROF under the RDBMS Oracle Home /bin directory. The TKPROF under the Applications Oracle Home will not yield accurate results.
2. Oracle Support steps to trace just one session (when trace can not be turned on on a program) using SQL code.
1) Make sure trace is NOT enabled for any of the concurrent programs we will be running:
System Administrator > Concurrent > Programs > Define
Query the program ARRGTA
Make sure trace is DISABLED (It is important trace is disabled for the program so it does not interfere with the rest of the steps)
2) Create the following Procedure as database user APPS:
CREATE OR REPLACE PROCEDURE cp_level12_trace_util AS
req_id number:=fnd_global.conc_request_id;
prog_id number:=fnd_global.conc_program_id;
appl_id number:=fnd_global.RESP_APPL_ID;
prog_name varchar2(128);
trc_label varchar2(128);
BEGIN
if req_id > 0 then
select max(concurrent_program_name) into prog_name
from fnd_concurrent_programs
where concurrent_program_id=prog_id
and application_id=appl_id;
trc_label:='CP_'||prog_name||'_REQ_'||req_id;
FND_CTL.FND_SESS_CTL(''
,''
,''
,'TRUE'
,''
,'ALTER SESSION SET MAX_DUMP_FILE_SIZE=UNLIMITED');
FND_CTL.FND_SESS_CTL(''
,''
,''
,'TRUE'
,''
,'ALTER SESSION SET TIMED_STATISTICS=TRUE');
FND_CTL.FND_SESS_CTL(''
,''
,''
,'TRUE'
,''
,'ALTER SESSION SET STATISTICS_LEVEL=ALL');
FND_CTL.FND_SESS_CTL(''
, ''
, ''
, 'TRUE'
, ''
, 'ALTER SESSION SET TRACEFILE_IDENTIFIER='||''''
|| trc_label
|| ''''
|| ' EVENTS ='
|| ''''
|| ' 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12 '
|| '''');
end if;
END;
/
3) Set the following profile option at user level, only for the user who reproduces the issue:
Profile option: Initialization SQL Statement - Custom
Value: begin cp_level12_trace_util; end;
NOTES: Be careful, as sometimes it may block user from login, and also see FND_INIT_SQL may cause error.
4) Run the concurrent request by the user, and upload all trace files, which have the request_id in their name (It will have something like CP_PROGNAME_REQ_999999, where PROGNAME is the short name of the program, and 999999 is the request id). If Trace files are too big, do not upload them for now
5) Run tkprof for the above trace files, and upload the output:
tkprof <trace file> <output file> sort=exeela,prsela,fchela explain=apps/<password>
Make sure the process already finished (if it hangs, please leave it running for a few hours before canceling). If the process has not finished the rows column in tkprof will be all zeros.
6) Upload also the log files for the concurrent requests
7) Do not forget to set the profile option “Initialization SQL Statement – Custom” back to BLANK afterwards.
3. Trace EBS Forms session
1) Change Profile option Utilities:Diagnostics to "Yes" at user level
2) Help --> Diagnostics --> Trace --> to choose a tracing level. Then, a popup box will tell where the trace file .trc is saved.
4. Find which node an EBS R12 session is connecting to
1) Change Profile option FND: Diagnostics to "Yes" at user level
2) Help -> About Oracle Applications (below section will tell which form node you have connected):
----------------------------------------
Forms Server
----------------------------------------
Oracle Forms Version : 10.1.2.3.0
Application Object Library : 12.0.0
Machine : node_name.domain.COM
NOTES:
- Turn "FND: Diagnostics" profile to Yes at user level will also add "About this Page" to EBS Homepage's left bottom after login and add "Diagnostics" on the right top of OA Framework pages. Clean the cache if they do not show up immediately after re-login.
- This profile option is different from turning "Utilities:Diagnostics" to Yes, which enables tracing on sub-menus of Help on GUI forms.
- I found that even "FND: Diagnostics" profile is set to No, "About this Page" may still shows up. That could be controlled by other things, such as other profile options for Personalization:
Personalize Self-Service Defn = YES
FND: Personalization Region Link Enabled = YES
Disable Self-Service Personal = NO (Only at Site)
Monday, August 18, 2014
Compile Oracle EBS forms and/or libraries
1. Use below steps as Applmgr to compile one R12 form in $CUSTOM_TOP/forms/US directory. Note frmcmp_batch.sh shall exist in $ORACLE_HOME/bin.
$ export FORMS_PATH=$FORMS_PATH:$AU_TOP/forms/US:$AU_TOP/resource:$CUSTOM_TOP/forms/US
$ cd $CUSTOM_TOP/forms/US
$ mv FORM_NAME.fmx FORM_NAME.fmx_BK (or delete it)
$ chmod 664 FORM_NAME.fmb (to make it -rw-rw-r--. This step seems optional).
$ frmcmp_batch.sh module=FORM_NAME.fmb userid=apps/XXXpwd output_file=FORM_NAME.fmx module_type=form compile_all=yes
If it works, it shall say "Created form file FORM_NAME.fmx". And new time stamp for two files FORM_NAME.fmb and FORM_NAME.fmx.
2. To compile only library (.pll) files, env variable $FORMS_PATH is not needed. Go to $AU_TOP/resources and run the command:
$ cd $AU_TOP/resource
$ frmcmp_batch.sh module=FORM_NAME.pll userid=apps/XXXpwd module_type=library compile_all=special batch=yes
After it completes, new timestamp will be on FORM_NAME.pll and FORM_NAME.plx without doing anything on .fmb and .fmx files (in $CUSTOM_TOP/forms/US if it is a custom form). The file size on FORM_NAME.pll may become bigger. Use command to check and compare contents:
$ export FORMS_PATH=$FORMS_PATH:$AU_TOP/forms/US:$AU_TOP/resource:$CUSTOM_TOP/forms/US
$ cd $CUSTOM_TOP/forms/US
$ mv FORM_NAME.fmx FORM_NAME.fmx_BK (or delete it)
$ chmod 664 FORM_NAME.fmb (to make it -rw-rw-r--. This step seems optional).
$ frmcmp_batch.sh module=FORM_NAME.fmb userid=apps/XXXpwd output_file=FORM_NAME.fmx module_type=form compile_all=yes
If it works, it shall say "Created form file FORM_NAME.fmx". And new time stamp for two files FORM_NAME.fmb and FORM_NAME.fmx.
2. To compile only library (.pll) files, env variable $FORMS_PATH is not needed. Go to $AU_TOP/resources and run the command:
$ cd $AU_TOP/resource
$ frmcmp_batch.sh module=FORM_NAME.pll userid=apps/XXXpwd module_type=library compile_all=special batch=yes
After it completes, new timestamp will be on FORM_NAME.pll and FORM_NAME.plx without doing anything on .fmb and .fmx files (in $CUSTOM_TOP/forms/US if it is a custom form). The file size on FORM_NAME.pll may become bigger. Use command to check and compare contents:
$ adident Header $AU_TOP/resource/FORM_NAME.pll
~~~~~~~~~~~~~~ Script to compile one custom form ~~~~~~~~~~~~~~
# Call: $ ./script_name.sh forms_name
#
export CUSTOM_TOP=$APPL_TOP/custom # where the custom form .fmb file is saved under
export FORMS_PATH=$FORMS_PATH:$AU_TOP/forms/US:$AU_TOP/resource:$CUSTOM_TOP/forms/US
Passwd='appsPWD'
custom_top=$CUSTOM_TOP/forms/US
cd $custom_top
custom_form=$1
frmcmp_batch module=$custom_form.fmb userid=apps/${Passwd} output_file=$custom_form.fmx module_type=form compile_all=yes batch=yes
exit 0
~~~~~~~~~~~~~~~~ Compile multiple forms ~~~~~~~~~~~~~~~~~
/path/to/script_name.sh forms_name1
/path/to/script_name.sh forms_name2
/path/to/script_name.sh forms_name3
UPDATES: If frmcmp_batch.sh in R12.1.3 gets ORA-12154 error (or error relates to listener), modify it with workaround described in Dec ID 431324.1 on $TNS_ADMIN:
TNS_ADMIN=${TNS_ADMIN:-$ORACLE_HOME/network/admin}
export TNS_ADMIN
Notes:
(1). If option "batch=yes" is used, progress and error messages will not echo out on the screen when the commend runs. But they will be written to .err file in current directory.
(2). If it gets ORA-12154 error in R12, the workaround is to modify $TNS_ADMIN variable in frmcmp_batch.sh (Doc ID 431324.1).
export TNS_ADMIN=${TNS_ADMIN:-$ORACLE_HOME/network/admin}
(3). For a non-Applmgr user to compile a R12 form, it is necessary to change permission on a set of files (Doc ID 561334.1). Make sure ORACLE_HOME points to 10.1.2 location.
chmod +rx $ORACLE_HOME
chmod +rx $ORACLE_HOME/bin
chmod +rx $ORACLE_HOME/bin/frm*
chmod -R +rx $ORACLE_HOME/lib
chmod +rx $ORACLE_HOME/forms
chmod -R +rx $ORACLE_HOME/forms/mesg
chmod -R +rx $ORACLE_HOME/forms/admin
chmod -R +rx $ORACLE_HOME/oracore
chmod +rx $ORACLE_HOME/nls
chmod -R +rx $ORACLE_HOME/nls/data
chmod +rx $ORACLE_HOME/rdbms
chmod -R +rx $ORACLE_HOME/rdbms/mesg
chmod +rx $ORACLE_HOME/ldap
chmod -R +rx $ORACLE_HOME/ldap/mesg
chmod -R +rx $ORACLE_HOME/ldap/admin
chmod +rx $ORACLE_HOME/network
chmod -R +rx $ORACLE_HOME/network/admin
chmod -R +rx $ORACLE_HOME/jre
chmod -R +rx $ORACLE_HOME/procbuilder
(also go to $CUSTOM_TOP/forms/US, and give 666 to .fmx and .fmb files so that user can re-create the form files.)
Note: after above grants, sqlplus is still not available to other users but form compile shall work.
3. Use ADADMIN to compile (or re-generate) multiple forms. The steps are documented in very good document: How To Regenerate Forms Library Files Using "adadmin" (11i & R12) (Doc ID 141118.1)
A) Select the "1. Generate Applications Files menu" option.
B) Then select the "2. Generate forms files" option.
C) Choose the number of workers (default is the # of CPUs in the server).
D) Enter "yes" to regenerate PL/SQL library files (.pll extension)
E) Enter "yes" to regenerate Forms Menu files (.mmb -> .mmx extension)
F) Enter "yes" to regenerate Forms Executable files (.fmb->.fmx extension)
G) Enter the list of products, by short name as they are listed in "$APPL_TOP/" (all OR ont, po, fnd, ar, gl)
H) Enter "No" to generate specific files for each product. If you like to generate specific files for each product, you may answer "Yes" here and a list of files will be displayed
I) Enter "all" to generate all the previously selected items for all languages, or only some of the installed languages.
J) Shows your default and/or selected languages
K) Enter "yes" if the languages selected are the desired languages
A yes on F) will give new timestamp on .fmx files. After answered yes on D), some .plx files get new timestamp, but no .pll files get new timestamp. I used adadmin to compile all forms in an instance and got one error:
The following Oracle Forms objects did not generate successfully:
ap forms/US APXWCARD.fmx
An error occurred while generating Oracle Forms objects.
Continue as if it were successful [No] :
adadmin log does not give much details on the error. So, I ran the command to compile this form individually:
$ cd $AP_TOP/forms/US
$ mv APXWCARD.fmx APXWCARD.fmx_BK_04292015
$ export FORMS_PATH=$FORMS_PATH:$AU_TOP/forms/US:$AU_TOP/resource:$AP_TOP/forms/US
$ frmcmp_batch.sh module=$AU_TOP/forms/US/APXWCARD.fmb userid=apps/APPS_PWD output_file=APXWCARD.fmx module_type=form compile_all=yes
FRM-30064: Unable to parse statement select distinct full_name, employee_number, date_of_birth, pa.person_id
from per_all_people_f pap,
financials_system_parameters fsp,
per_assignments_f pa
where (trunc(sysdate) between pap.effective_start_date and pap.effective_end_date)
and (trunc(sysdate) between pa.effective_start_date and pa.effective_end_date)
and pap.business_group_id = fsp.business_group_id
and pa.business_group_id = fsp.business_group_id
and pap.person_id = pa.person_id
and pa.assignment_type = 'E'
order by upper(full_name).
ORA-01791: not a SELECTed expression
Record Group FULL_NAME
Form: APXWCARD
FRM-30085: Unable to adjust form for output.
Form not created
To fix the problem, check the file version:
$ strings $AU_TOP/forms/US/APXWCARD.fmb | grep '$Header'
FDRCSID('$Header: APPSTAND.fmb 120.6.12010000.6 2010/03/10 12:58 dbowles ship $');
FDRCSID('$Header: APXWCARD.fmb 120.16.12010000.14 2010/02/22 22:27 meesubra ship $');
Doc ID 1314734.1 says it hits a database bug. So, there is always a risk of failures from compiling all forms.
4. Write a script to compile forms file
(1). If option "batch=yes" is used, progress and error messages will not echo out on the screen when the commend runs. But they will be written to .err file in current directory.
(2). If it gets ORA-12154 error in R12, the workaround is to modify $TNS_ADMIN variable in frmcmp_batch.sh (Doc ID 431324.1).
export TNS_ADMIN=${TNS_ADMIN:-$ORACLE_HOME/network/admin}
(3). For a non-Applmgr user to compile a R12 form, it is necessary to change permission on a set of files (Doc ID 561334.1). Make sure ORACLE_HOME points to 10.1.2 location.
chmod +rx $ORACLE_HOME
chmod +rx $ORACLE_HOME/bin
chmod +rx $ORACLE_HOME/bin/frm*
chmod -R +rx $ORACLE_HOME/lib
chmod +rx $ORACLE_HOME/forms
chmod -R +rx $ORACLE_HOME/forms/mesg
chmod -R +rx $ORACLE_HOME/forms/admin
chmod -R +rx $ORACLE_HOME/oracore
chmod +rx $ORACLE_HOME/nls
chmod -R +rx $ORACLE_HOME/nls/data
chmod +rx $ORACLE_HOME/rdbms
chmod -R +rx $ORACLE_HOME/rdbms/mesg
chmod +rx $ORACLE_HOME/ldap
chmod -R +rx $ORACLE_HOME/ldap/mesg
chmod -R +rx $ORACLE_HOME/ldap/admin
chmod +rx $ORACLE_HOME/network
chmod -R +rx $ORACLE_HOME/network/admin
chmod -R +rx $ORACLE_HOME/jre
chmod -R +rx $ORACLE_HOME/procbuilder
(also go to $CUSTOM_TOP/forms/US, and give 666 to .fmx and .fmb files so that user can re-create the form files.)
Note: after above grants, sqlplus is still not available to other users but form compile shall work.
3. Use ADADMIN to compile (or re-generate) multiple forms. The steps are documented in very good document: How To Regenerate Forms Library Files Using "adadmin" (11i & R12) (Doc ID 141118.1)
A) Select the "1. Generate Applications Files menu" option.
B) Then select the "2. Generate forms files" option.
C) Choose the number of workers (default is the # of CPUs in the server).
D) Enter "yes" to regenerate PL/SQL library files (.pll extension)
E) Enter "yes" to regenerate Forms Menu files (.mmb -> .mmx extension)
F) Enter "yes" to regenerate Forms Executable files (.fmb->.fmx extension)
G) Enter the list of products, by short name as they are listed in "$APPL_TOP/" (all OR ont, po, fnd, ar, gl)
H) Enter "No" to generate specific files for each product. If you like to generate specific files for each product, you may answer "Yes" here and a list of files will be displayed
I) Enter "all" to generate all the previously selected items for all languages, or only some of the installed languages.
J) Shows your default and/or selected languages
K) Enter "yes" if the languages selected are the desired languages
A yes on F) will give new timestamp on .fmx files. After answered yes on D), some .plx files get new timestamp, but no .pll files get new timestamp. I used adadmin to compile all forms in an instance and got one error:
The following Oracle Forms objects did not generate successfully:
ap forms/US APXWCARD.fmx
An error occurred while generating Oracle Forms objects.
Continue as if it were successful [No] :
adadmin log does not give much details on the error. So, I ran the command to compile this form individually:
$ cd $AP_TOP/forms/US
$ mv APXWCARD.fmx APXWCARD.fmx_BK_04292015
$ export FORMS_PATH=$FORMS_PATH:$AU_TOP/forms/US:$AU_TOP/resource:$AP_TOP/forms/US
$ frmcmp_batch.sh module=$AU_TOP/forms/US/APXWCARD.fmb userid=apps/APPS_PWD output_file=APXWCARD.fmx module_type=form compile_all=yes
FRM-30064: Unable to parse statement select distinct full_name, employee_number, date_of_birth, pa.person_id
from per_all_people_f pap,
financials_system_parameters fsp,
per_assignments_f pa
where (trunc(sysdate) between pap.effective_start_date and pap.effective_end_date)
and (trunc(sysdate) between pa.effective_start_date and pa.effective_end_date)
and pap.business_group_id = fsp.business_group_id
and pa.business_group_id = fsp.business_group_id
and pap.person_id = pa.person_id
and pa.assignment_type = 'E'
order by upper(full_name).
ORA-01791: not a SELECTed expression
Record Group FULL_NAME
Form: APXWCARD
FRM-30085: Unable to adjust form for output.
Form not created
To fix the problem, check the file version:
$ strings $AU_TOP/forms/US/APXWCARD.fmb | grep '$Header'
FDRCSID('$Header: APPSTAND.fmb 120.6.12010000.6 2010/03/10 12:58 dbowles ship $');
FDRCSID('$Header: APXWCARD.fmb 120.16.12010000.14 2010/02/22 22:27 meesubra ship $');
Doc ID 1314734.1 says it hits a database bug. So, there is always a risk of failures from compiling all forms.
4. Write a script to compile forms file
~~~~~~~~~~~~~~ Script to compile one custom form ~~~~~~~~~~~~~~
# Call: $ ./script_name.sh forms_name
#
export CUSTOM_TOP=$APPL_TOP/custom # where the custom form .fmb file is saved under
export FORMS_PATH=$FORMS_PATH:$AU_TOP/forms/US:$AU_TOP/resource:$CUSTOM_TOP/forms/US
Passwd='appsPWD'
custom_top=$CUSTOM_TOP/forms/US
cd $custom_top
custom_form=$1
frmcmp_batch module=$custom_form.fmb userid=apps/${Passwd} output_file=$custom_form.fmx module_type=form compile_all=yes batch=yes
exit 0
~~~~~~~~~~~~~~~~ Compile multiple forms ~~~~~~~~~~~~~~~~~
/path/to/script_name.sh forms_name1
/path/to/script_name.sh forms_name2
/path/to/script_name.sh forms_name3
UPDATES: If frmcmp_batch.sh in R12.1.3 gets ORA-12154 error (or error relates to listener), modify it with workaround described in Dec ID 431324.1 on $TNS_ADMIN:
TNS_ADMIN=${TNS_ADMIN:-$ORACLE_HOME/network/admin}
export TNS_ADMIN
Relinek apps executable file
1. Relink the AutoInvoice Master program (report) as follows: (refer to Doc ID 1209514.1)
a) Log into the Operating System as APPLMGR (on CM node)
b) $ cd $AR_TOP/bin
c) Make a backup copy on file RAXMTR
d) Make sure that Autoinvoice is not running (by checking concurrent jobs)
e) Run the relink script
$ adrelink.sh force=y ranlib=y "ar RAXMTR"
=====log on the screen ==============================
.....
Done with link of ar executable 'RAXMTR' on Fri Jul 18 12:51:41 EDT 2014
Done relinking module RAXMTR in product ar
Done with link of product 'ar' on Fri Jul 18 12:51:41 EDT 2014
adrelink is exiting with status 0
End of adrelink session
Date/time is Fri Jul 18 12:51:41 EDT 2014
2. To relink 10.1.2 reports executables:
a) $ cd $ORACLE_HOME/reports/lib (or $ORACLE_HOME/reports/lib32)
b) $ make -f ins_reports.mk install
3.To relink 10.1.2 forms executables:
a) $ cd $ORACLE_HOME/forms/lib (or $ORACLE_HOME/forms/lib32)
b) $ make -f ins_forms.mk install
4. Use adadmin to relink all apps programs
choices: 2 => 1
If all worked, you will see " adrelink is exiting with status 0 " at the end.
Log file is $APPL_TOP/admin/${TWO_TASK}/log/adrelink.log
5. Regenerate the jar files with force:
$ adadmin
1 => 4. Make sure that you choose force equals Yes.
NOTES/UPDATES
1: Oracle usually suggests "regenerate the jar files with force" after "relink" all form excutables.
2: If below errors exist in the make log file:
/usr/lib/libXtst.so.6: undefined reference to `__stack_chk_fail@GLIBC_2.4'
/usr/lib/libXtst.so.6: undefined reference to `__fprintf_chk@GLIBC_2.3.4'
/usr/lib/libXtst.so.6: undefined reference to `__sprintf_chk@GLIBC_2.3.4'
The fix is to update a required link to a Motif library (as root)
# unlink /usr/lib/libXtst.so.6
# ln -s /usr/X11R6/lib/libXtst.so.6.1 /usr/lib/libXtst.so.6
Before the change
$ ls -al /usr/lib/libXtst.so.6
lrwxrwxrwx 1 root root 16 Oct 11 2012 /usr/lib/libXtst.so.6 -> libXtst.so.6.1.0
After the fix, the result shall be
$ ls -al /usr/lib/libXtst.so.6
lrwxrwxrwx 1 root root 29 Jun 18 2014 /usr/lib/libXtst.so.6 -> /usr/X11R6/lib/libXtst.so.6.1
$ ls -al /usr/X11R6/lib/libXtst*
lrwxrwxrwx 1 root root 14 Oct 18 2011 /usr/X11R6/lib/libXtst.so -> libXtst.so.6.1
lrwxrwxrwx 1 root root 14 Oct 18 2011 /usr/X11R6/lib/libXtst.so.6 -> libXtst.so.6.1
-rwxr-xr-x 1 root root 17204 Feb 26 2008 /usr/X11R6/lib/libXtst.so.6.1
3: On Linux RHEL 6 and 7, R12.1 adadmin relink and/or adpatch may hit error
libgcc_s.so: undefined reference to `__stack_chk_fail@GLIBC_2.4'
The fix is to apply patch 12415211 to 10.1.2 AND 10.1.3 ORACLE HOMES manually (see Doc ID 1525823.1). Below script shall do the job.
#!/bin/ksh
# Steps from Doc ID 761566.1 - search keyword 12415211 (patch number)
# copy file libgcc_s-2.3.2-stub.so from patch folder ($HOME/RHEL6 in this script) to
# $ORACLE_HOME/lib/stubs and $IAS_ORACLE_HOME/lib/stubs
echo $ORACLE_HOME
cd $ORACLE_HOME/lib
cp -p -R stubs stubsORIG_`date +%H%M`
pwd
cd stubs
ls -al libgcc_s-2.3.2-stub.so*
cp -p $HOME/RHEL6/libgcc_s-2.3.2-stub.so .
ln -s libgcc_s-2.3.2-stub.so libgcc_s.so.1
ln -s libgcc_s.so.1 libgcc_s.so
ls -al libgcc_s*.*
# -rw-r--r-- 1 id xxx 12246 Jul 27 2011 $ORACLE_HOME/lib/stubs/libgcc_s-2.3.2-stub.so
# lrwxrwxrwx 1 id xxx 13 Jun 2 16:32 $ORACLE_HOME/lib/stubs/libgcc_s.so -> libgcc_s.so.1
# lrwxrwxrwx 1 if xxx 22 Jun 2 16:32 $ORACLE_HOME/lib/stubs/libgcc_s.so.1 -> libgcc_s-2.3.2-stub.so
# ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
echo $IAS_ORACLE_HOME
cd $IAS_ORACLE_HOME/lib
cp -p -R stubs stubsORIG_`date +%H%M`
pwd
cd stubs
ls -al libgcc_s-2.3.2-stub.so*
cp -p $HOME/RHEL6/libgcc_s-2.3.2-stub.so .
ln -s libgcc_s-2.3.2-stub.so libgcc_s.so.1
ln -s libgcc_s.so.1 libgcc_s.so
ls -al libgcc_s*.*
4: One day, after I applied patches to ORACLE HOMEs and applied an AR patch, some AR forms hang and get frozen forever without any error or warning message. The issue was fixed by running steps 3 and 5 above (on each Web node).
a) Log into the Operating System as APPLMGR (on CM node)
b) $ cd $AR_TOP/bin
c) Make a backup copy on file RAXMTR
d) Make sure that Autoinvoice is not running (by checking concurrent jobs)
e) Run the relink script
$ adrelink.sh force=y ranlib=y "ar RAXMTR"
=====log on the screen ==============================
.....
Done with link of ar executable 'RAXMTR' on Fri Jul 18 12:51:41 EDT 2014
Done relinking module RAXMTR in product ar
Done with link of product 'ar' on Fri Jul 18 12:51:41 EDT 2014
adrelink is exiting with status 0
End of adrelink session
Date/time is Fri Jul 18 12:51:41 EDT 2014
2. To relink 10.1.2 reports executables:
a) $ cd $ORACLE_HOME/reports/lib (or $ORACLE_HOME/reports/lib32)
b) $ make -f ins_reports.mk install
3.To relink 10.1.2 forms executables:
a) $ cd $ORACLE_HOME/forms/lib (or $ORACLE_HOME/forms/lib32)
b) $ make -f ins_forms.mk install
4. Use adadmin to relink all apps programs
choices: 2 => 1
If all worked, you will see " adrelink is exiting with status 0 " at the end.
Log file is $APPL_TOP/admin/${TWO_TASK}/log/adrelink.log
5. Regenerate the jar files with force:
$ adadmin
1 => 4. Make sure that you choose force equals Yes.
NOTES/UPDATES
1: Oracle usually suggests "regenerate the jar files with force" after "relink" all form excutables.
2: If below errors exist in the make log file:
/usr/lib/libXtst.so.6: undefined reference to `__stack_chk_fail@GLIBC_2.4'
/usr/lib/libXtst.so.6: undefined reference to `__fprintf_chk@GLIBC_2.3.4'
/usr/lib/libXtst.so.6: undefined reference to `__sprintf_chk@GLIBC_2.3.4'
The fix is to update a required link to a Motif library (as root)
# unlink /usr/lib/libXtst.so.6
# ln -s /usr/X11R6/lib/libXtst.so.6.1 /usr/lib/libXtst.so.6
Before the change
$ ls -al /usr/lib/libXtst.so.6
lrwxrwxrwx 1 root root 16 Oct 11 2012 /usr/lib/libXtst.so.6 -> libXtst.so.6.1.0
After the fix, the result shall be
$ ls -al /usr/lib/libXtst.so.6
lrwxrwxrwx 1 root root 29 Jun 18 2014 /usr/lib/libXtst.so.6 -> /usr/X11R6/lib/libXtst.so.6.1
$ ls -al /usr/X11R6/lib/libXtst*
lrwxrwxrwx 1 root root 14 Oct 18 2011 /usr/X11R6/lib/libXtst.so -> libXtst.so.6.1
lrwxrwxrwx 1 root root 14 Oct 18 2011 /usr/X11R6/lib/libXtst.so.6 -> libXtst.so.6.1
-rwxr-xr-x 1 root root 17204 Feb 26 2008 /usr/X11R6/lib/libXtst.so.6.1
3: On Linux RHEL 6 and 7, R12.1 adadmin relink and/or adpatch may hit error
libgcc_s.so: undefined reference to `__stack_chk_fail@GLIBC_2.4'
The fix is to apply patch 12415211 to 10.1.2 AND 10.1.3 ORACLE HOMES manually (see Doc ID 1525823.1). Below script shall do the job.
#!/bin/ksh
# Steps from Doc ID 761566.1 - search keyword 12415211 (patch number)
# copy file libgcc_s-2.3.2-stub.so from patch folder ($HOME/RHEL6 in this script) to
# $ORACLE_HOME/lib/stubs and $IAS_ORACLE_HOME/lib/stubs
echo $ORACLE_HOME
cd $ORACLE_HOME/lib
cp -p -R stubs stubsORIG_`date +%H%M`
pwd
cd stubs
ls -al libgcc_s-2.3.2-stub.so*
cp -p $HOME/RHEL6/libgcc_s-2.3.2-stub.so .
ln -s libgcc_s-2.3.2-stub.so libgcc_s.so.1
ln -s libgcc_s.so.1 libgcc_s.so
ls -al libgcc_s*.*
# -rw-r--r-- 1 id xxx 12246 Jul 27 2011 $ORACLE_HOME/lib/stubs/libgcc_s-2.3.2-stub.so
# lrwxrwxrwx 1 id xxx 13 Jun 2 16:32 $ORACLE_HOME/lib/stubs/libgcc_s.so -> libgcc_s.so.1
# lrwxrwxrwx 1 if xxx 22 Jun 2 16:32 $ORACLE_HOME/lib/stubs/libgcc_s.so.1 -> libgcc_s-2.3.2-stub.so
# ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
echo $IAS_ORACLE_HOME
cd $IAS_ORACLE_HOME/lib
cp -p -R stubs stubsORIG_`date +%H%M`
pwd
cd stubs
ls -al libgcc_s-2.3.2-stub.so*
cp -p $HOME/RHEL6/libgcc_s-2.3.2-stub.so .
ln -s libgcc_s-2.3.2-stub.so libgcc_s.so.1
ln -s libgcc_s.so.1 libgcc_s.so
ls -al libgcc_s*.*
4: One day, after I applied patches to ORACLE HOMEs and applied an AR patch, some AR forms hang and get frozen forever without any error or warning message. The issue was fixed by running steps 3 and 5 above (on each Web node).
Friday, August 8, 2014
R12 Patchset Level, R12 Apps code Level
1. As applmgr or OS environment owner
(a) source environment
(b) login as apps
(c) SQL> @$AD_TOP/sql/adutconf.sql
The adutconf.sql script will create a text file adutconf.lst containing version information and product top information. There is a section->”Product Installation Status, Version Info and Patch Level”, e.g.
Product Appl Status Version Patchset Level
AD Shared 12.0.0 R12.AD.B.3
FND Installed 12.0.0 R12.FND.B.3 <-- call this "FND code level" also
- Implemented product has status Installed.
- Not installed Product has the Status Inactive.
- Products with status Shared are not fully implemented - only partially installed for dependent Product(s).
AD = Applications DBA, FND = Application Object Library (AOL, i.e. Foundation tables).
2. Doc ID 550654.1 (for R12.1)
Login to OAM. Choose menu “OAM Support Cart” in upper right hand corner
Navigate to :
(a) Support Cart –> Applications Signature –> Collect –> Check “Product Information” box
(b) Click on “View” (eyeglasses) to get the patchset level report
3. Script (similar to the one for 11i in Doc ID 443699.1)
SELECT a.application_name, a.application_short_name, a.product_code, b.patch_level, DECODE (b.status, 'I', 'Installed', 'S', 'Shared', 'Not Installed - N/A') status, b.last_update_date, b.tablespace
FROM apps.fnd_product_installations b, apps.fnd_application_vl a,
WHERE a.application_id = b.application_id
-- and b.status=’I’
order by a.application_short_name asc;
-- Not sure what is in this table
SELECT app_short_name, MAX(patch_level)
FROM apps.ad_patch_driver_minipks
GROUP BY app_short_name order by app_short_name;
Applications code level?
SQL> select release_name from apps.fnd_product_groups;
12.1.3
Tuesday, July 22, 2014
WFLOAD, FNDLOAD
1. Oracle EBS provides a UNIX command to get workflow definition file.
WFLOAD <apps_user_name>/<password>@db 0 Y DOWNLOAD apinvapr.wft APINVAPR
apinvapr.wft will be created in the same directory that this command was ran from. APINVAPR is the workflow name.
2. FNDLOAD utility:
FNDLOAD apps/<PWD> O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct filename.ldt REQUEST_GROUP REQUEST_GROUP_NAME='GL Concurrent Program Group' APPLICATION_SHORT_NAME='SQLGL'
Doc ID 2058269.1 is about Transaction Analyzer. But it can serve as a good example on how to use FNDLOAD to register a concurrent program (Doc ID 1587455.1 EBS Payments Funds Disbursement Analyzer does also):
$FND_TOP/bin/FNDLOAD apps/apps_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct artrxanalzr.ldt CUSTOM_MODE=FORCE UPLOAD_MODE=REPLACE
NOTES additional steps to assign / grant "Transaction Analyzer" to custom responsibility AR_SupperUser:
Security --> Responsibilites --> Define --> "AR_SuperUser" to find it belongs to which "Request Group": AR SuperUser
Security --> Responsibilitie --> Request --> "AR SuperUser" --> add "Transaction Analyzer" to the group.
Now, responsibility AR_SupperUser can submit the new concurrent job "Transaction Analyzer".
WFLOAD <apps_user_name>/<password>@db 0 Y DOWNLOAD apinvapr.wft APINVAPR
apinvapr.wft will be created in the same directory that this command was ran from. APINVAPR is the workflow name.
2. FNDLOAD utility:
FNDLOAD apps/<PWD> O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct filename.ldt REQUEST_GROUP REQUEST_GROUP_NAME='GL Concurrent Program Group' APPLICATION_SHORT_NAME='SQLGL'
Doc ID 2058269.1 is about Transaction Analyzer. But it can serve as a good example on how to use FNDLOAD to register a concurrent program (Doc ID 1587455.1 EBS Payments Funds Disbursement Analyzer does also):
$FND_TOP/bin/FNDLOAD apps/apps_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct artrxanalzr.ldt CUSTOM_MODE=FORCE UPLOAD_MODE=REPLACE
NOTES additional steps to assign / grant "Transaction Analyzer" to custom responsibility AR_SupperUser:
Security --> Responsibilites --> Define --> "AR_SuperUser" to find it belongs to which "Request Group": AR SuperUser
Security --> Responsibilitie --> Request --> "AR SuperUser" --> add "Transaction Analyzer" to the group.
Now, responsibility AR_SupperUser can submit the new concurrent job "Transaction Analyzer".
Thursday, June 12, 2014
du / find to check space & Linux commands
More OS commands are used as an EBS apps admin than a database DBA. More notes are added to this post after I feel they are useful.
Below command will give the disk space used by first level of each sub-directory in RH Linux:
$ du -h --max-depth=1
To get sizes of current directory:
$ du -sh * <== List space used by each file and subdirectory
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
- Get the list of directories. Below were tested in K Shell
$ echo $SHELL
/bin/ksh
1.
$ find /u01/app/patches/* -prune -type d (Or go to the directory first, $ find * -prune -type d )
/u01/app/patches/11902981
/u01/app/patches/13006289
/u01/app/patches/16367827
Note: if just want the list of files, use "find * -prune -type f"
2. -- List directories older than two days
$ find * -prune -type d -mtime +2
3.
$ ls -l |egrep "^drw" |awk '{print $9}'
-- Loop through the list to do whatever you want
$ for x in `ls -l |egrep "^drw" |awk '{print $9}'`
do
echo $x;
done
4.
-- Note: FS is used to define the delimiter in AWK
The default is defined by OS env variable $FS
$ ls -1d */ | awk 'BEGIN {FS="/"};{print $1}'
~~~~~~~~~~~~~~ script to check file size ~~~~~~~~~~~~~~
Below command will give the disk space used by first level of each sub-directory in RH Linux:
$ du -h --max-depth=1
To get sizes of current directory:
$ du -sh * <== List space used by each file and subdirectory
$ du -sh (or, $ du -sk ) <== Get the total space used in current location
$ du -k . <== List all details under current location. Not useful.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Find the big files under a directory in Linux, which is useful when disk space is filling up:
$
For example
$ find /u01/app -type f -size +1000000 -exec ls -alh {} \;
-rw-rw-r-- 1 ebs oracle 953M Aug 9 2011 /path/to/ftp/out/gl_journal_to_adam.txt
$ du -k . <== List all details under current location. Not useful.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Find the big files under a directory in Linux, which is useful when disk space is filling up:
$
find /home -size +1000000 -exec ls -alh {} \;For example
$ find /u01/app -type f -size +1000000 -exec ls -alh {} \;
-rw-rw-r-- 1 ebs oracle 953M Aug 9 2011 /path/to/ftp/out/gl_journal_to_adam.txt
-rw-r----- 1 ebs oracle 1.8G Sep 27 2013 /path/to/ora/10.1.3/Apache/ssl_engine_log
-rw-r--r-- 1 ebs oracle 8.3G Mar 30 13:24 /path/to/ora/10.1.3/j2ee/oacore/oacore_default_group_1/application.log
You can search for exact file size, or just for bigger (+) or smaller (–) files. For example all bigger than 512k files would be found with command:
$
units:
b – for 512-byte blocks (this is the default if no suffix is used)
c – for bytes
w – for two-byte words
k – for Kilobytes (units of 1024 bytes)
M – for Megabytes (units of 1048576 bytes)
G – for Gigabytes (units of 1073741824 bytes)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Find the total number of files under a directory:
$
$
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Find and sort by timestamp:
$ find /path/. -name '*.*' -printf "%T+\t%s\t%p\n" | sort | more
%T+ - modification time (and date)
%s - size
%p - path of file
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Use below line to replace wrong file owner under a directory:
# find /u01 -user wrongUserID -print | wc -l
-rw-r--r-- 1 ebs oracle 8.3G Mar 30 13:24 /path/to/ora/10.1.3/j2ee/oacore/oacore_default_group_1/application.log
You can search for exact file size, or just for bigger (+) or smaller (–) files. For example all bigger than 512k files would be found with command:
$
find /home -type f -size +512k -exec ls -lh {} \; units:
b – for 512-byte blocks (this is the default if no suffix is used)
c – for bytes
w – for two-byte words
k – for Kilobytes (units of 1024 bytes)
M – for Megabytes (units of 1048576 bytes)
G – for Gigabytes (units of 1073741824 bytes)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Find the total number of files under a directory:
$
find folder_name -type f | wc -l$
find folder_name -maxdepth 1 -type f | wc -l <== to exclude subdirectories~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Find and sort by timestamp:
$ find /path/. -name '*.*' -printf "%T+\t%s\t%p\n" | sort | more
%T+ - modification time (and date)
%s - size
%p - path of file
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Use below line to replace wrong file owner under a directory:
# find /u01 -user wrongUserID -print | wc -l
# find /u01 -user wrongUserID -exec chown rightUserID:userGroup {} \;
To find/list all files under current directory tree that is not owned by you (applMgr):
To find/list all files under current directory tree that is not owned by you (applMgr):
$ find . ! -user applMgr
$ find . ! -user applMgr -exec ls -altr {} \;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Use find to delete files:
One day, CM log file and Output file can not be opened by browser with error "Authentication failed." Found the disk space was full with 11GB by Apache folder. Use "find" to delete old files (-r will also delete sub-folders):
$ cd $LOG_HOME/ora/10.1.3/Apache
$ find *.* -type f -mtime +7 -exec rm -rf {} \;
NOTES: If there is a huge pile of files, above command may get error. Try a different way by placing * inside the single quotes so that it is used as a matching wildcard and not for shell filename expansion:
$ find . -name '*.tmp' | wc -l
$ find . -name '*.tmp' -mtime +7 -exec rm -f {} \;
Below first line worked for me to delete files from a folder with more than 15K files. But the 2nd line failed.
$ /usr/bin/find /u01/path/utl_dir/. -name '*.tmp' -mtime +60 -exec rm -f {} \;
$ /usr/bin/find /u01/path/utl_dir/*.tmp -mtime +60 -exec rm -f {} \;
-ksh: /usr/bin/find: /usr/bin/find: cannot execute [Argument list too long]
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
To Zip up a folder, including all subdirectories:
$ cd /path/to/parent_folderName <= go to the upper folder first
$ zip -r file_name.zip folderName
this is useful when use "admrgpch -s source_path -d target_path -manifest manifest.txt" to merge patches and then copy the merged folder to different node.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
To find multiple words in a text file:
$ /bin/grep -i -e 'drop ' -e 'alter ' -e 'grant ' -e 'commit ' ascii_file.sql
$ egrep -i 'fail|error' long_file_name.log
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
lsof to find open files. it is useful when the system acts strangely
$ lsof /home | wc -l
$ lsof | grep deleted | wc -l (files in being deleted queue)
Use find to delete files:
One day, CM log file and Output file can not be opened by browser with error "Authentication failed." Found the disk space was full with 11GB by Apache folder. Use "find" to delete old files (-r will also delete sub-folders):
$ cd $LOG_HOME/ora/10.1.3/Apache
$ find *.* -type f -mtime +7 -exec rm -rf {} \;
NOTES: If there is a huge pile of files, above command may get error. Try a different way by placing * inside the single quotes so that it is used as a matching wildcard and not for shell filename expansion:
$ find . -name '*.tmp' | wc -l
$ find . -name '*.tmp' -mtime +7 -exec rm -f {} \;
Below first line worked for me to delete files from a folder with more than 15K files. But the 2nd line failed.
$ /usr/bin/find /u01/path/utl_dir/. -name '*.tmp' -mtime +60 -exec rm -f {} \;
$ /usr/bin/find /u01/path/utl_dir/*.tmp -mtime +60 -exec rm -f {} \;
-ksh: /usr/bin/find: /usr/bin/find: cannot execute [Argument list too long]
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
To Zip up a folder, including all subdirectories:
$ cd /path/to/parent_folderName <= go to the upper folder first
$ zip -r file_name.zip folderName
this is useful when use "admrgpch -s source_path -d target_path -manifest manifest.txt" to merge patches and then copy the merged folder to different node.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
To find multiple words in a text file:
$ /bin/grep -i -e 'drop ' -e 'alter ' -e 'grant ' -e 'commit ' ascii_file.sql
$ egrep -i 'fail|error' long_file_name.log
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
lsof to find open files. it is useful when the system acts strangely
$ lsof /home | wc -l
$ lsof | grep deleted | wc -l (files in being deleted queue)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
To find what is using a port in Linux:
To find what is using a port in Linux:
Below line will tell if a port is used or not on the server:
$ netstat -tuanp | grep 6230
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
tcp 0 0 167.69.109.82:6230 0.0.0.0:* LISTEN 31876/opmn
Then the process owner (or root) can use below line to see more details on using the port:
$ lsof -i :6230
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
opmn 31876 ebsdev 9u IPv4 42886593 0t0 TCP server3d.domain.com:6230 (LISTEN)
Below line may also works (Note: "lsof" may show result only to root or process owner):
$ ls -l /proc/31876/exe <= use the PID from above line
lrwxrwxrwx 1 ebiz user 0 Mar 5 11:19 /proc/31876/exe -> /u05/app/EBSDEV/apps/tech_st/10.1.3/opmn/bin/opmn
~~~~~~~~~~~~~~~~~ rsync ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
To copy a folder to another folder on the SAME server and preserve exactly all attributes. For example, in EBS R12.2, to synchronize custom folder xxap from PATCH file system to RUN file system:
$ ls -l /proc/31876/exe <= use the PID from above line
lrwxrwxrwx 1 ebiz user 0 Mar 5 11:19 /proc/31876/exe -> /u05/app/EBSDEV/apps/tech_st/10.1.3/opmn/bin/opmn
~~~~~~~~~~~~~~~~~ rsync ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
To copy a folder to another folder on the SAME server and preserve exactly all attributes. For example, in EBS R12.2, to synchronize custom folder xxap from PATCH file system to RUN file system:
$ rsync -a --delete $PATCH_BASE/EBSapps/appl/xxap $RUN_BASE/EBSapps/appl
Notes: add rsync -a --delete %s_current_base%/EBSapps/appl/xxap %s_other_base%/EBSapps/appl to file $APPL_TOP_NE/ad/custom/adop_sync.drv
If contents of a folder are static, add rsync -zr %s_current_base%/EBSapps/appl/xxe4a %s_other_base%/EBSapps/appl to file $APPL_TOP_NE/ad/custom/adop_sync.drv
The option -a is very powerful, it will copy the directory and all its sub-directories recursively, and preserve symbolic links, modification times, group, ownership, and permissions.
-a option is a combination of a bunch of other options:
-r Recurse into directories
-l Copy symlinks as symlinks
-p Preserve file permissions
-t Preserve modification times
-g Preserve group
-o Preserve owner
-D Preserve device and special files
"-- delete" will DELETE files in Target folder but not in Source folder.
Below line will copy the folder structure with same permissions to current location, without copying files:
$ cd /path/to/target
$ rsync -av -f"+ */" -f"- *" /path1/to/source .
$ cd /path/to/target
$ rsync -av -f"+ */" -f"- *" /path1/to/source .
Copy a folder to a different server (and preserve exactly all attributes):
$ cd /to/path/source_FOLDER
$ rsync --progress -avze ssh applmgr@nodeName:/to/path/target_FOLDER
~~~~~~~~~~~~~~~~~~ Stickybit ~~~~~~~~~~~~~~~~~~~~~
If your ID own /path/to/LOG directory and you want to allow users in other Groups to rename the files in that directory, you need to remove Stickybit on LOG directory (by chmod -t), because of stickybit enables other users are unable to rename/delete the files under LOG directory.
$ ls -al /path/to/LOG
drwxrwxrwt 10 user group 134 Aug 14 09:57
$ chmod -t /path/to/LOG
$ ls -al /path/to/LOG
drwxrwxrwx 10 user group 134 Aug 14 09:57
Right permission is needed for others to write/modify your file. To get 664 permissions (-rw-rw-r--) on all files created by your ID, add one line to .bashrc or .kshrc or .profile of your OS ID, or add the line to the shell script for generating the file:
set umask to 0002 (or 002 ?)
or
umask u=rwx,g=rwx,o=rx
- To list all folders (sub-directories called "fs_clone") under a location:
$ find . -name fs_clone -type d -print
- Get the list of directories. Below were tested in K Shell
$ echo $SHELL
/bin/ksh
1.
$ find /u01/app/patches/* -prune -type d (Or go to the directory first, $ find * -prune -type d )
/u01/app/patches/11902981
/u01/app/patches/13006289
/u01/app/patches/16367827
Note: if just want the list of files, use "find * -prune -type f"
2. -- List directories older than two days
$ find * -prune -type d -mtime +2
3.
$ ls -l |egrep "^drw" |awk '{print $9}'
-- Loop through the list to do whatever you want
$ for x in `ls -l |egrep "^drw" |awk '{print $9}'`
do
echo $x;
done
4.
-- Note: FS is used to define the delimiter in AWK
The default is defined by OS env variable $FS
$ ls -1d */ | awk 'BEGIN {FS="/"};{print $1}'
~~~~~~~~~~~~~~ script to check file size ~~~~~~~~~~~~~~
#/!bin/sh
# check the file size. If it is over 1800MB, send email out
file=$APPLCSF/log/reports.log
warningsize=1800000
actualsize=`du -k "$file" | cut -f 1`
echo $actualsize
if [ $actualsize -gt $warningsize ] ; then
echo "File size is over $actualsize kilobytes now. 2 GB is the limit." | mailx -s "warning: $file s
ize is big" emailid@xxxx.com
# mailx -s "warning: $file size is $warningsize MB now" emailid@xxxx.com < /dev/null
fi
exit 0
# check the file size. If it is over 1800MB, send email out
file=$APPLCSF/log/reports.log
warningsize=1800000
actualsize=`du -k "$file" | cut -f 1`
echo $actualsize
if [ $actualsize -gt $warningsize ] ; then
echo "File size is over $actualsize kilobytes now. 2 GB is the limit." | mailx -s "warning: $file s
ize is big" emailid@xxxx.com
# mailx -s "warning: $file size is $warningsize MB now" emailid@xxxx.com < /dev/null
fi
exit 0
Monday, April 7, 2014
Apply Oracle database 11G Security Patch Update (SPU) on AIX
January 2014 Database SPU Installation steps:
1. Check/install opatch:
Above patches require opatch version 11.2.0.3. Check current version of opatch and install correct version if required. To check:
$ cd /app/oracle/product/OPatch
$ ./opatch version
To install opatch newer version 11.2.0.3, download patch 6880880 and unzip it:
$ cd /app/oracle/product
$ rm -rf OPatch
$ unzip opatch_p6880880_112000_AIX64-5L.zip
$ rm opatch_p6880880_112000_AIX64-5L.zip
$ export PATH=/app/oracle/product/OPatch:$PATH
$ opatch version <= to verify opatch version. It should be 11.2.0.3
2. FTP/unzip SPU patch:
$ mkdir $ORACLE_BASE/patches/spu_jan14_11203
$ cd $ORACLE_BASE/patches/spu_jan14_11203
$ unzip p17478415_112030_AIX64-5L.zip
$ rm p17478415_112030_AIX64-5L.zip
3. Check/set environment:
- Set correct ORACLE_HOME
Set ORACLE_BASE
$ export ORACLE_BASE=/app/oracle
Set LIBPATH
$ export LIBPATH=$ORACLE_HOME/lib
Set LD_LIBRARY_PATH
$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib
- Ensure that the $PATH has the following executables: make, ar, ld, and nm.
$ which make
$ which ar
$ which ld
$ which nm
The location of these executables depends on your operating system. On many operating systems, they are located in /usr/ccs/bin, in which case you can set your PATH as follows:
$ export PATH=/usr/ccs/bin:$PATH
- Check jdk version: It should be version 1.4.2 or higher.
$ java -version
If java version is lower, check if version 1.4.2 or higher is available on the host.
$ lslpp -L | grep Java
(or)
$ lslpp -L | grep Java14
$ export PATH=/usr/java14/bin:$PATH
If available, adjust PATH accordingly and check again.
If java version 1.4.2 or higher is not available, work with System Admin to have it installed.
- Check location of oracle inventory in /etc/oraInst.loc
- Run oracle inventory
$ cd /app/oracle/product/OPatch
$ ./opatch lsinventory
Make sure that Oracle version is 11.2.0.3.
- Check XDB: XDB must be VALID.
SQL> select status from dba_registry where comp_id='XDB'
If XDB is invalid, re-install DB – reference Oracle Support note 1292089.1
4. List invalid objects:
For each database on the server, save a list of invalid objects for comparison after SPU patch.
SQL> select owner, object_name, object_type from dba_objects
where status= 'INVALID';
5. Stop database replication tool if any.
6. Stop database monitoring tools, such as Grid Control agent.
7. Shutdown databases for the Oracle Home being patched and stop listener.
8. Check processes:
Make sure that there are no oracle processes running except aioserver process.
$ ps -fu'oracle’ | grep -v aioserver
$ ps -ef | grep sqlplus
Make sure that there are no oraggs processes running except aioserver process.
$ ps -fu'oragg’
9. Check shared file in use:
Following commands should not return any file name:
$ genld -l | grep $ORACLE_HOME
$ genkld | grep $ORACLE_HOME
If these return any file name, have system administrator run following command
$ /usr/sbin/slibclean
Check again.
10. Apply patch
$ cd $ORACLE_BASE/patches/spu_jan14_11203/17478415
$ /app/oracle/product/OPatch/opatch napply -skip_subset -skip_duplicate
An observation: During installation on one host, it might as for Oracle Support login ID / password to make an internet connection to Oracle Support website for any latest information relating patch. Just press return.
Patch will possible complete with warnings, as listed below. Per Oracle these can safely be ignored:
ld: 0711-319 WARNING: Exported symbol not defined: ****
Symbol xxxxxx is already exported.
ld: 0711-773 WARNING: Object ***, imported symbol ***
Symbol was expected to be local.
ld: 0711-783 WARNING: TOC overflow. TOC size: Maximum size:
0711-224 WARNING: Duplicate symbol
Check the following log files in $ORACLE_BASE/cfgtoollogs/catbundle for any errors:
catbundle_CPU_<database SID>_APPLY_<TIMESTAMP>.log
catbundle_CPU_<database SID>_GENERATE_<TIMESTAMP>.log
11. Check inventory again:
$ /app/oracle/product/OPatch/opatch lsinventory
12. Load modified SQL files into databases:
For each database instance running on the Oracle home being patched, connect to the database using SQL*Plus. Connect as SYSDBA and run the catbundle.sql script as follows:
$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> STARTUP
SQL> @catbundle.sql cpu apply
SQL> @utlrp.sql
SQL> QUIT
Note: Start the LISTENER
For information about the catbundle.sql script, see My Oracle Support Note 605795.1 Introduction to Oracle Database catbundle.sql.
Check the following log files in $ORACLE_BASE/cfgtoollogs/catbundle for any errors:
catbundle_CPU_<database SID>_APPLY_<TIMESTAMP>.log
catbundle_CPU_<database SID>_GENERATE_<TIMESTAMP>.log
13. Invalid objects check
For each database on the server, make a list of invalid objects and compare with the list saved before patching. Take appropriate action if there are new invalid objects
14. Check XDB
If XDB is invalid, there is a possibility that LIBPATH has not been set correctly.
To check status of XDB:
SQL> select status from DBA_REGISTRY where comp_id='XDB'
If status is INVALID:
$ export LIBPATH=$ORACLE_HOME/lib
Shutdown database
Startup database
Run utlrp
If XDB is still invalid, re-install DB – reference Oracle Support note 1292089.1
15. Final check on registry:
$ sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> SELECT * FROM sys.registry$history where comments='CPUJan2014';
It should return one line for CPUJan2014.
1. Check/install opatch:
Above patches require opatch version 11.2.0.3. Check current version of opatch and install correct version if required. To check:
$ cd /app/oracle/product/OPatch
$ ./opatch version
To install opatch newer version 11.2.0.3, download patch 6880880 and unzip it:
$ cd /app/oracle/product
$ rm -rf OPatch
$ unzip opatch_p6880880_112000_AIX64-5L.zip
$ rm opatch_p6880880_112000_AIX64-5L.zip
$ export PATH=/app/oracle/product/OPatch:$PATH
$ opatch version <= to verify opatch version. It should be 11.2.0.3
2. FTP/unzip SPU patch:
$ mkdir $ORACLE_BASE/patches/spu_jan14_11203
$ cd $ORACLE_BASE/patches/spu_jan14_11203
$ unzip p17478415_112030_AIX64-5L.zip
$ rm p17478415_112030_AIX64-5L.zip
3. Check/set environment:
- Set correct ORACLE_HOME
Set ORACLE_BASE
$ export ORACLE_BASE=/app/oracle
Set LIBPATH
$ export LIBPATH=$ORACLE_HOME/lib
Set LD_LIBRARY_PATH
$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib
- Ensure that the $PATH has the following executables: make, ar, ld, and nm.
$ which make
$ which ar
$ which ld
$ which nm
The location of these executables depends on your operating system. On many operating systems, they are located in /usr/ccs/bin, in which case you can set your PATH as follows:
$ export PATH=/usr/ccs/bin:$PATH
- Check jdk version: It should be version 1.4.2 or higher.
$ java -version
If java version is lower, check if version 1.4.2 or higher is available on the host.
$ lslpp -L | grep Java
(or)
$ lslpp -L | grep Java14
$ export PATH=/usr/java14/bin:$PATH
If available, adjust PATH accordingly and check again.
If java version 1.4.2 or higher is not available, work with System Admin to have it installed.
- Check location of oracle inventory in /etc/oraInst.loc
- Run oracle inventory
$ cd /app/oracle/product/OPatch
$ ./opatch lsinventory
Make sure that Oracle version is 11.2.0.3.
- Check XDB: XDB must be VALID.
SQL> select status from dba_registry where comp_id='XDB'
If XDB is invalid, re-install DB – reference Oracle Support note 1292089.1
4. List invalid objects:
For each database on the server, save a list of invalid objects for comparison after SPU patch.
SQL> select owner, object_name, object_type from dba_objects
where status= 'INVALID';
5. Stop database replication tool if any.
6. Stop database monitoring tools, such as Grid Control agent.
7. Shutdown databases for the Oracle Home being patched and stop listener.
8. Check processes:
Make sure that there are no oracle processes running except aioserver process.
$ ps -fu'oracle’ | grep -v aioserver
$ ps -ef | grep sqlplus
Make sure that there are no oraggs processes running except aioserver process.
$ ps -fu'oragg’
9. Check shared file in use:
Following commands should not return any file name:
$ genld -l | grep $ORACLE_HOME
$ genkld | grep $ORACLE_HOME
If these return any file name, have system administrator run following command
$ /usr/sbin/slibclean
Check again.
10. Apply patch
$ cd $ORACLE_BASE/patches/spu_jan14_11203/17478415
$ /app/oracle/product/OPatch/opatch napply -skip_subset -skip_duplicate
An observation: During installation on one host, it might as for Oracle Support login ID / password to make an internet connection to Oracle Support website for any latest information relating patch. Just press return.
Patch will possible complete with warnings, as listed below. Per Oracle these can safely be ignored:
ld: 0711-319 WARNING: Exported symbol not defined: ****
Symbol xxxxxx is already exported.
ld: 0711-773 WARNING: Object ***, imported symbol ***
Symbol was expected to be local.
ld: 0711-783 WARNING: TOC overflow. TOC size: Maximum size:
0711-224 WARNING: Duplicate symbol
Check the following log files in $ORACLE_BASE/cfgtoollogs/catbundle for any errors:
catbundle_CPU_<database SID>_APPLY_<TIMESTAMP>.log
catbundle_CPU_<database SID>_GENERATE_<TIMESTAMP>.log
11. Check inventory again:
$ /app/oracle/product/OPatch/opatch lsinventory
12. Load modified SQL files into databases:
For each database instance running on the Oracle home being patched, connect to the database using SQL*Plus. Connect as SYSDBA and run the catbundle.sql script as follows:
$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> STARTUP
SQL> @catbundle.sql cpu apply
SQL> @utlrp.sql
SQL> QUIT
Note: Start the LISTENER
For information about the catbundle.sql script, see My Oracle Support Note 605795.1 Introduction to Oracle Database catbundle.sql.
Check the following log files in $ORACLE_BASE/cfgtoollogs/catbundle for any errors:
catbundle_CPU_<database SID>_APPLY_<TIMESTAMP>.log
catbundle_CPU_<database SID>_GENERATE_<TIMESTAMP>.log
13. Invalid objects check
For each database on the server, make a list of invalid objects and compare with the list saved before patching. Take appropriate action if there are new invalid objects
14. Check XDB
If XDB is invalid, there is a possibility that LIBPATH has not been set correctly.
To check status of XDB:
SQL> select status from DBA_REGISTRY where comp_id='XDB'
If status is INVALID:
$ export LIBPATH=$ORACLE_HOME/lib
Shutdown database
Startup database
Run utlrp
If XDB is still invalid, re-install DB – reference Oracle Support note 1292089.1
15. Final check on registry:
$ sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> SELECT * FROM sys.registry$history where comments='CPUJan2014';
It should return one line for CPUJan2014.
Thursday, February 27, 2014
Move objects from one schema to another schema
All database objects of a home-grown business application were created in a wrong schema. After year and year, it became a very large database with more than 2,000GB data. The problem never got corrected until getting urgent calls for violating company auditing standard. I used transport tablespaces concept in Doc ID 371556.1 to move those objects, mainly tables, to a new dedicated schema within the same 11G database.
Pre-steps:
1. create new schema owner EDI in the database
create user edi identified by "ediPassword"
default tablespace TBS_MED_DT1
temporary tablespace temp
quota unlimited on TBS_MED_DT1
profile STANDARD;
alter user edi quota unlimited on TBS_LRG_DT1; -- do the same with other tablespaces
grant ALTER ANY MATERIALIZED VIEW to edi;
grant CREATE SESSION to edi;
grant GLOBAL QUERY REWRITE to edi;
grant CREATE TABLE to edi;
2. Create DIRECTORY for datapump
SQL> create or replace directory dpump_dir as '/path/to/export';
SQL> grant read, write on directory dpump_dir to system;
3. Identify 5 tablespaces that hold all targeted objects for moving. ORADBA is the schema where objects will be moved out. Save the counts
.
SQL> select distinct tablespace_name, count(*) from dba_tables
where owner = 'ORADBA' group by tablespace_name;
SQL> select distinct tablespace_name, count(*) from dba_indexes where owner = 'ORADBA' group by tablespace_name;
4. Get and save the list of data files for IMPDP script (142 data files)
SQL> select ''''||file_name|| ''',' from dba_data_files where tablespace_name in
('TBS_LRG_DT1','TBS_MED_DT1','TBS_DT_LOB','ORADBA_IX1','ORADBA_IX2');
5. compile invalid objects and record down the invalid list
SQL> @?/rdbms/admin/utlrp.sql
SQL> select owner, object_type, object_name from dba_objects where status = 'INVALID';
... ... ...
29 rows selected.
6. statements for re-creating sequences
SQL> select 'create sequence edi.' || sequence_name || ' start with ' || last_number || ' INCREMENT BY 1;' FROM dba_sequences where sequence_owner = 'ORADBA';
7. statement for re-creating synonyms
select 'create public synonym ' || synonym_name || ' for EDI.' || table_name || ';' from dba_synonyms where table_owner = 'ORADBA' and owner='PUBLIC'
union
select 'create synonym ' || owner||'.'||synonym_name || ' for EDI.' || table_name ||';' from dba_synonyms where table_owner = 'ORADBA' and owner!='PUBLIC'
8. query dba_tab_privs to get all necessary "grant" statements.
9. extract affected codes of packages, functions, triggers, views, materialized views, db links.
Moving steps:
1. Prepare for exporting the tablespace (Doc 371556.1)
SQL> execute sys.dbms_tts.transport_set_check(
'TBS_LRG_DT1,TBS_MED_DT1,TBS_DT_LOB,ORADBA_IX1,ORADBA_IX2', true);
SQL> select * from sys.transport_set_violations;
2. Make fixes to 6 violations from Step 1. Such as:
SQL> ALTER TABLE ORADBA.AIS_DATA DROP PRIMARY KEY CASCADE drop index;
SQL> alter table ORADBA.AIS_DATA move tablespace TBS_DT_LOB;
SQL> ALTER TABLE ORADBA.AI_DATA_SET ADD
CONSTRAINT AIS_DATA_PK
PRIMARY KEY (KEY, FORMAT, STATUS_CD)
USING INDEX tablespace ORADBA_IX1 ;
... ... ...
Repeat Step 1 until no violation in sys.transport_set_violations
3. Drop two materialized views from TBS_MED_DT1, and drop other objects
SQL> drop materialized view oradba.xxxxxx;
SQL> drop MATERIALIZED VIEW LOG ON ORADBA.STATUS_TAB;
Drop triggers and synonyms
SQL> @drop_trigger.sql
SQL> @drop_synonym.sql
4. Alter 5 tablespaces to READ only
$ more tbs_read_only.sql
alter tablespace TBS_LRG_DT1 read only;
alter tablespace TBS_MED_DT1 read only;
alter tablespace TBS_DT_LOB read only;
alter tablespace ORADBA_IX1 read only;
alter tablespace ORADBA_IX2 read only;
SQL> @tbs_read_only.sql
confirm by
SQL> select * from dba_tablespaces where status = 'READ ONLY';
4. Run EXPDP script: No "TRANSPORT_FULL_CHECK=Y"
$ more expdp_5tbs.par
## $ expdp system/PSWD parfile=expdp_8tbs.par
DUMPFILE=expdp_5tbs.dmp
LOGFILE=expdp_5tbs.log
DIRECTORY=dpump_dir
TRANSPORT_TABLESPACES=TBS_LRG_DT1,TBS_MED_DT1,TBS_DT_LOB,ORADBA_IX1,ORADBA_IX2
# TRANSPORT_FULL_CHECK=Y
$ expdp system/PSWD parfile=expdp_5tbs.par
5. Drop tablespaces. The KEY is all datafiles will not be deleted and untouched!
$ more tbs_drop.sql
drop tablespace TBS_LRG_DT1 INCLUDING CONTENTS;
drop tablespace TBS_MED_DT1 INCLUDING CONTENTS;
drop tablespace TBS_DT_LOB INCLUDING CONTENTS;
drop tablespace ORADBA_IX1 INCLUDING CONTENTS;
drop tablespace ORADBA_IX2 INCLUDING CONTENTS;
SQL> @tbs_drop.sql
confirm by
SQL> select * from dba_data_files where tablespace_name in
('TBS_LRG_DT1','TBS_MED_DT1','TBS_DT_LOB','ORADBA_IX1','ORADBA_IX2');
0 row returned
6. Run IMPDP script
$ more impdp_5tbs.par
# impdp system/PSWD parfile=impdp_8tbs.par
#
DUMPFILE=expdp_5tbs.dmp
LOGFILE=impdp_5tbs.log
DIRECTORY=dpump_dir
TRANSPORT_DATAFILES='/path/to/data/sml_data_001.dbf',
'/path/to/index/sml_index_001.dbf',
'/path/to/index/med_index_001.dbf',
'..... list all 142 files, See Pre-setp 4',
REMAP_SCHEMA=ORADBA:EDI
$ impdp system/PSWD parfile=impdp_8tbs.par
7. Bring them back "READ WRITE"
alter tablespace TBS_LRG_DT1 read write;
alter tablespace TBS_MED_DT1 read write;
alter tablespace TBS_DT_LOB read write;
alter tablespace ORADBA_IX1 read write;
alter tablespace ORADBA_IX2 read write;
SQL> @tbs_read_write.sql
Verify:
SQL> select * from dba_tablespaces where status = 'READ ONLY';
-- Make sure the object counts match what the database had before the move
SQL> select distinct tablespace_name, count(*) from dba_tables where owner = 'EDI' group by tablespace_name;
SQL> select distinct tablespace_name, count(*) from dba_indexes where owner = 'EDI' group by tablespace_name;
-- The old schema still keeps some objects as needed
SQL> select distinct tablespace_name, count(*) from dba_tables where owner = 'ORADBA' group by tablespace_name;
TABLESPACE_NAME COUNT(*)
------------------------------------- ----------
2
TOOLS 34
SQL> select distinct tablespace_name, count(*) from dba_indexes where owner = 'ORADBA' group by tablespace_name;
TABLESPACE_NAME COUNT(*)
------------------------------------ ----------
TOOLS 3
8. Compile objects
SQL> @?/rdbms/admin/utlrp.sql
9.Sync a MVIEW log
SQL> exec SYS.DBMS_SNAPSHOT_UTL.SYNC_UP_LOG('EDI','STATU_TAB');
PL/SQL procedure successfully completed.
10. Re-create views, sequences.
11. Re-create synonyms, triggers, etc
12. Run grant statements.
Pre-steps:
1. create new schema owner EDI in the database
create user edi identified by "ediPassword"
default tablespace TBS_MED_DT1
temporary tablespace temp
quota unlimited on TBS_MED_DT1
profile STANDARD;
alter user edi quota unlimited on TBS_LRG_DT1; -- do the same with other tablespaces
grant ALTER ANY MATERIALIZED VIEW to edi;
grant CREATE SESSION to edi;
grant GLOBAL QUERY REWRITE to edi;
grant CREATE TABLE to edi;
2. Create DIRECTORY for datapump
SQL> create or replace directory dpump_dir as '/path/to/export';
SQL> grant read, write on directory dpump_dir to system;
3. Identify 5 tablespaces that hold all targeted objects for moving. ORADBA is the schema where objects will be moved out. Save the counts
.
SQL> select distinct tablespace_name, count(*) from dba_tables
where owner = 'ORADBA' group by tablespace_name;
SQL> select distinct tablespace_name, count(*) from dba_indexes where owner = 'ORADBA' group by tablespace_name;
4. Get and save the list of data files for IMPDP script (142 data files)
SQL> select ''''||file_name|| ''',' from dba_data_files where tablespace_name in
('TBS_LRG_DT1','TBS_MED_DT1','TBS_DT_LOB','ORADBA_IX1','ORADBA_IX2');
5. compile invalid objects and record down the invalid list
SQL> @?/rdbms/admin/utlrp.sql
SQL> select owner, object_type, object_name from dba_objects where status = 'INVALID';
... ... ...
29 rows selected.
6. statements for re-creating sequences
SQL> select 'create sequence edi.' || sequence_name || ' start with ' || last_number || ' INCREMENT BY 1;' FROM dba_sequences where sequence_owner = 'ORADBA';
7. statement for re-creating synonyms
select 'create public synonym ' || synonym_name || ' for EDI.' || table_name || ';' from dba_synonyms where table_owner = 'ORADBA' and owner='PUBLIC'
union
select 'create synonym ' || owner||'.'||synonym_name || ' for EDI.' || table_name ||';' from dba_synonyms where table_owner = 'ORADBA' and owner!='PUBLIC'
8. query dba_tab_privs to get all necessary "grant" statements.
9. extract affected codes of packages, functions, triggers, views, materialized views, db links.
Moving steps:
1. Prepare for exporting the tablespace (Doc 371556.1)
SQL> execute sys.dbms_tts.transport_set_check(
'TBS_LRG_DT1,TBS_MED_DT1,TBS_DT_LOB,ORADBA_IX1,ORADBA_IX2', true);
SQL> select * from sys.transport_set_violations;
2. Make fixes to 6 violations from Step 1. Such as:
SQL> ALTER TABLE ORADBA.AIS_DATA DROP PRIMARY KEY CASCADE drop index;
SQL> alter table ORADBA.AIS_DATA move tablespace TBS_DT_LOB;
SQL> ALTER TABLE ORADBA.AI_DATA_SET ADD
CONSTRAINT AIS_DATA_PK
PRIMARY KEY (KEY, FORMAT, STATUS_CD)
USING INDEX tablespace ORADBA_IX1 ;
... ... ...
Repeat Step 1 until no violation in sys.transport_set_violations
3. Drop two materialized views from TBS_MED_DT1, and drop other objects
SQL> drop materialized view oradba.xxxxxx;
SQL> drop MATERIALIZED VIEW LOG ON ORADBA.STATUS_TAB;
Drop triggers and synonyms
SQL> @drop_trigger.sql
SQL> @drop_synonym.sql
4. Alter 5 tablespaces to READ only
$ more tbs_read_only.sql
alter tablespace TBS_LRG_DT1 read only;
alter tablespace TBS_MED_DT1 read only;
alter tablespace TBS_DT_LOB read only;
alter tablespace ORADBA_IX1 read only;
alter tablespace ORADBA_IX2 read only;
SQL> @tbs_read_only.sql
confirm by
SQL> select * from dba_tablespaces where status = 'READ ONLY';
4. Run EXPDP script: No "TRANSPORT_FULL_CHECK=Y"
$ more expdp_5tbs.par
## $ expdp system/PSWD parfile=expdp_8tbs.par
DUMPFILE=expdp_5tbs.dmp
LOGFILE=expdp_5tbs.log
DIRECTORY=dpump_dir
TRANSPORT_TABLESPACES=TBS_LRG_DT1,TBS_MED_DT1,TBS_DT_LOB,ORADBA_IX1,ORADBA_IX2
# TRANSPORT_FULL_CHECK=Y
$ expdp system/PSWD parfile=expdp_5tbs.par
5. Drop tablespaces. The KEY is all datafiles will not be deleted and untouched!
$ more tbs_drop.sql
drop tablespace TBS_LRG_DT1 INCLUDING CONTENTS;
drop tablespace TBS_MED_DT1 INCLUDING CONTENTS;
drop tablespace TBS_DT_LOB INCLUDING CONTENTS;
drop tablespace ORADBA_IX1 INCLUDING CONTENTS;
drop tablespace ORADBA_IX2 INCLUDING CONTENTS;
SQL> @tbs_drop.sql
confirm by
SQL> select * from dba_data_files where tablespace_name in
('TBS_LRG_DT1','TBS_MED_DT1','TBS_DT_LOB','ORADBA_IX1','ORADBA_IX2');
0 row returned
6. Run IMPDP script
$ more impdp_5tbs.par
# impdp system/PSWD parfile=impdp_8tbs.par
#
DUMPFILE=expdp_5tbs.dmp
LOGFILE=impdp_5tbs.log
DIRECTORY=dpump_dir
TRANSPORT_DATAFILES='/path/to/data/sml_data_001.dbf',
'/path/to/index/sml_index_001.dbf',
'/path/to/index/med_index_001.dbf',
'..... list all 142 files, See Pre-setp 4',
REMAP_SCHEMA=ORADBA:EDI
$ impdp system/PSWD parfile=impdp_8tbs.par
7. Bring them back "READ WRITE"
alter tablespace TBS_LRG_DT1 read write;
alter tablespace TBS_MED_DT1 read write;
alter tablespace TBS_DT_LOB read write;
alter tablespace ORADBA_IX1 read write;
alter tablespace ORADBA_IX2 read write;
SQL> @tbs_read_write.sql
Verify:
SQL> select * from dba_tablespaces where status = 'READ ONLY';
-- Make sure the object counts match what the database had before the move
SQL> select distinct tablespace_name, count(*) from dba_tables where owner = 'EDI' group by tablespace_name;
SQL> select distinct tablespace_name, count(*) from dba_indexes where owner = 'EDI' group by tablespace_name;
-- The old schema still keeps some objects as needed
SQL> select distinct tablespace_name, count(*) from dba_tables where owner = 'ORADBA' group by tablespace_name;
TABLESPACE_NAME COUNT(*)
------------------------------------- ----------
2
TOOLS 34
SQL> select distinct tablespace_name, count(*) from dba_indexes where owner = 'ORADBA' group by tablespace_name;
TABLESPACE_NAME COUNT(*)
------------------------------------ ----------
TOOLS 3
8. Compile objects
SQL> @?/rdbms/admin/utlrp.sql
9.Sync a MVIEW log
SQL> exec SYS.DBMS_SNAPSHOT_UTL.SYNC_UP_LOG('EDI','STATU_TAB');
PL/SQL procedure successfully completed.
10. Re-create views, sequences.
11. Re-create synonyms, triggers, etc
12. Run grant statements.
Friday, January 24, 2014
11G database does not start up or ORA-600 error during startup
First of all, two ways to connect to the database:
a. Make a bequeath connection to the database
sqlplus / as sysdba
b. Make a connection to the database via listener:
sqlplus userid/pwd@tnsname
============================================
a. Make a bequeath connection to the database
sqlplus / as sysdba
b. Make a connection to the database via listener:
sqlplus userid/pwd@tnsname
Case: Database does not start after a fail-over test did not work
on a two-nodes cluster.
SQL> startup nomount
ORA-24324: service handle not initialized
ORA-24323: value not allowed
ORA-03113: end-of-file on communication channel
1. checked and did not find any background processes related
to this $ORACLE_SID
$ ps -ef | grep
ora_ | grep $ORACLE_SID
Note: Doc ID
794293.1 recommends two more steps to check other OS processes.
I did not do
those because there are other Oracle instances were running on the same server.
2. $ sqlplus sqlplus "/ as sysdba"
SQL>
shutdown abort
3. Remove/rename three files belong to the $ORACLE_SID under
$ORACLE_HOME/dbs, such as lk{$ORACLE_SID}
4. SQL> startup nomount;
SQL> alter
database mount;
SQL> alter
database open;============================================
Case: ORA-00600: internal error code, arguments: [3712], [1], [1],
[574], [3989605836], [574], [3989605835],[]
LGWR: terminating instance due to error 600
Instance terminated by LGWR, pid = 6487
SQL> startup mount
SQL> select group#, status from v$log;
GROUP# STATUS
---------- ----------------
1 UNUSED
2 CURRENT
3 UNUSED
4 UNUSED
SQL> alter database clear unarchived logfile group 1;
Database altered.
SQL> alter database clear unarchived logfile group 3;
Database altered.
SQL> alter database clear unarchived logfile group 4;
Database altered.
SQL> select group#, status from v$log;
GROUP# STATUS
---------- ----------------
1 UNUSED
2 CURRENT
3 UNUSED
4 UNUSED
SQL> alter database clear unarchived logfile group 2;
Database altered.
SQL> select group#, status from v$log;
GROUP# STATUS
---------- ----------------
1 CURRENT
2 UNUSED
3 UNUSED
4 UNUSED
SQL> shutdown immediate
SQL> startup
SQL> select group#, status from v$log;
GROUP# STATUS
---------- ----------------
1 CURRENT
2 INACTIVE
3 INACTIVE
4 INACTIVE
===========================================
Case: ora-600 errors after SAN server crashed
SQL> startup;
ORACLE instance started.
Total System Global Area 3240689664 bytes
Fixed Size 2225608 bytes
Variable Size 1543506488 bytes
Database Buffers 1610612736 bytes
Redo Buffers 84344832 bytes
Database mounted.
ORA-00600: internal error code, arguments: [kcratr_scan_lastbwr], [], [], [],
[], [], [], [], [], [], [], []
Fix: It needed media recovery
SQL> startup mount;
ORACLE instance started.
Total System Global Area 3240689664 bytes
Fixed Size 2225608 bytes
Variable Size 1543506488 bytes
Database Buffers 1610612736 bytes
Redo Buffers 84344832 bytes
Database mounted.
SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.
===========================================
Case: database did not start up
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1],
[54278], [610128], [610297], [], [], [], [], [], [], []
Fix: recover control file. Doc. 1296264.1 gives two methods. One of them is:
SQL>Startup mount ;
SQL>Show parameter control_files
Query 1
------------
SQL> select a.member, a.group#, b.status from v$logfile a ,v$log b where a.group#=b.group# and b.status='CURRENT' ;
Note down the name of the redo log
SQL> Shutdown abort ;
Take a OS Level backup of the controlfile (This is to ensure we have a backup of current state of controlfile)
SQL> Startup mount ;
SQL> recover database using backup controlfile until cancel ;
Enter location of redo log shown as current in Query 1 when prompted for recovery
Hit Enter
SQL> Alter database open resetlogs ;
============================================
Case: Error in alert log file - ORA-00600: internal error code, arguments: [4194], [#], [#], [], [], [], [], []
This error indicates that a mismatch has been detected between redo records and rollback (undo) records.
ARGUMENTS:
Arg [a] - Maximum Undo record number in Undo block
Arg [b] - Undo record number from Redo block
Fix: It is related undo segment corruption. Doc ID 1428786.1 :
Best practice to create a new undo tablespace.
This method includes segment check.
Create pfile from spfile to edit
SQL> create pfile from spfile;
1. Shutdown the instance
2. set the following parameters in the pfile
undo_management = manual
event = '10513 trace name context forever, level 2'
3. SQL> startup restrict pfile=<initsid.ora>
4. SQL> select tablespace_name, status, segment_name from dba_rollback_segs where status != 'OFFLINE';
This is critical - we are looking for all undo segments to be offline - System will always be online.
If any are 'PARTLY AVAILABLE' or 'NEEDS RECOVERY' - Please open an issue with Oracle Support or update the current SR. There are many options from this moment and Oracle Support Analyst can offer different solutions for the bad undo segments.
If all offline then continue to the next step
5. Create new undo tablespace - example
SQL> create undo tablespace <new undo tablespace> datafile <datafile> size 2000M;
6. Drop old undo tablespace
SQL> drop tablespace <old undo tablespace> including contents and datafiles;
7. SQL> shutdown immediate;
8. SQL> startup mount;
9. modify the pfile with the new undo tablespace name
SQL> alter system set undo_tablespace = '<new tablespace>' scope=pfile;
10. SQL> shutdown immediate;
11. SQL> startup;
Then, startup using the normal spfile
SQL> startup;
ORACLE instance started.
Total System Global Area 3240689664 bytes
Fixed Size 2225608 bytes
Variable Size 1543506488 bytes
Database Buffers 1610612736 bytes
Redo Buffers 84344832 bytes
Database mounted.
ORA-00600: internal error code, arguments: [kcratr_scan_lastbwr], [], [], [],
[], [], [], [], [], [], [], []
Fix: It needed media recovery
SQL> startup mount;
ORACLE instance started.
Total System Global Area 3240689664 bytes
Fixed Size 2225608 bytes
Variable Size 1543506488 bytes
Database Buffers 1610612736 bytes
Redo Buffers 84344832 bytes
Database mounted.
SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.
===========================================
Case: database did not start up
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1],
[54278], [610128], [610297], [], [], [], [], [], [], []
Fix: recover control file. Doc. 1296264.1 gives two methods. One of them is:
SQL>Startup mount ;
SQL>Show parameter control_files
Query 1
------------
SQL> select a.member, a.group#, b.status from v$logfile a ,v$log b where a.group#=b.group# and b.status='CURRENT' ;
Note down the name of the redo log
SQL> Shutdown abort ;
Take a OS Level backup of the controlfile (This is to ensure we have a backup of current state of controlfile)
SQL> Startup mount ;
SQL> recover database using backup controlfile until cancel ;
Enter location of redo log shown as current in Query 1 when prompted for recovery
Hit Enter
SQL> Alter database open resetlogs ;
============================================
Case: Error in alert log file - ORA-00600: internal error code, arguments: [4194], [#], [#], [], [], [], [], []
This error indicates that a mismatch has been detected between redo records and rollback (undo) records.
ARGUMENTS:
Arg [a] - Maximum Undo record number in Undo block
Arg [b] - Undo record number from Redo block
Fix: It is related undo segment corruption. Doc ID 1428786.1 :
Best practice to create a new undo tablespace.
This method includes segment check.
Create pfile from spfile to edit
SQL> create pfile from spfile;
1. Shutdown the instance
2. set the following parameters in the pfile
undo_management = manual
event = '10513 trace name context forever, level 2'
3. SQL> startup restrict pfile=<initsid.ora>
4. SQL> select tablespace_name, status, segment_name from dba_rollback_segs where status != 'OFFLINE';
This is critical - we are looking for all undo segments to be offline - System will always be online.
If any are 'PARTLY AVAILABLE' or 'NEEDS RECOVERY' - Please open an issue with Oracle Support or update the current SR. There are many options from this moment and Oracle Support Analyst can offer different solutions for the bad undo segments.
If all offline then continue to the next step
5. Create new undo tablespace - example
SQL> create undo tablespace <new undo tablespace> datafile <datafile> size 2000M;
6. Drop old undo tablespace
SQL> drop tablespace <old undo tablespace> including contents and datafiles;
7. SQL> shutdown immediate;
8. SQL> startup mount;
9. modify the pfile with the new undo tablespace name
SQL> alter system set undo_tablespace = '<new tablespace>' scope=pfile;
10. SQL> shutdown immediate;
11. SQL> startup;
Then, startup using the normal spfile
Subscribe to:
Posts (Atom)