Nitish's Oracle Apps DBA Forum

AutoConfig is exiting with status 1

2014-01-13T02:36:00.003-08:00

At the time of running the Autoconfig it is exiting with status 1.

The content of Autoconfig log file is as follows :

Perl lib version (v5.8.8) doesn't match executable version (v5.10.0) at /usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/Config.pm line 46.
Compilation failed in require at /DB/CRP1/db/tech_st/11.2.0/appsutil/clone/ouicli.pl line 35.
BEGIN failed--compilation aborted at /DB/CRP1/db/tech_st/11.2.0/appsutil/clone/ouicli.pl line 35.

autoconfig then AutoConfig could not successfully execute the following scripts:
Directory: /DB/CRP1/db/tech_st/11.2.0/perl/bin/perl -I /DB/CRP1/db/tech_st/11.2.0/perl/lib/5.8.3 -I /DB/CRP1/db/tech_st/11.2.0/perl/lib/site_perl/5.8.3 -I /DB/CRP1/db/tech_st/11.2.0/appsutil/perl /DB/CRP1/db/tech_st/11.2.0/appsutil/clone
ouicli.pl INSTE8_APPLY 255

AutoConfig is exiting with status 1
RC-50013: Fatal: Instantiate driver did not complete successfully.

Solution :

Check the version of perl

perl -v

It could return - This is perl, v5.8.8

Now set env to use the db_home perl

export PATH=$ORACLE_HOME/perl/bin:$PATH:/usr/ccs/bin

and check again.

It should return - This is perl, v5.10.0 ....

Now re-run adcfgclone.

It should work fine now.

Deallocating Unused Space

2013-04-19T06:01:00.000-07:00

When you deallocate unused space, the database frees the unused space at the unused (high water mark) end of the database segment and makes the space available for other segments in the tablespace.

Prior to deallocation, you can run the UNUSED_SPACE procedure of the DBMS_SPACE package, which returns information about the position of the high water mark and the amount of unused space in a segment. For segments in locally managed tablespaces with automatic segment space management, use theSPACE_USAGE procedure for more accurate information on unused space.

The following statements deallocate unused space in a segment (table, index or cluster):

ALTER TABLE table DEALLOCATE UNUSED KEEP integer;

ALTER INDEX index DEALLOCATE UNUSED KEEP integer;

ALTER CLUSTER cluster DEALLOCATE UNUSED KEEP integer;

The KEEP clause is optional and lets you specify the amount of space retained in the segment. You can verify that the deallocated space is freed by examining the DBA_FREE_SPACE view.

How to check free space in ASM

2013-04-16T06:07:00.003-07:00

1. Connect to asm instance:

select name, state, total_mb, free_mb from v$asm_diskgroup;

NAME STATE TOTAL_MB FREE_MB

—————————— ———– ———- ———-

ORADATA MOUNTED 65536 53439

SELECT name, type, ceil (total_mb/1024) TOTAL_GB , ceil (free_mb/1024) FREE_GB, required_mirror_free_mb,

ceil ((usable_file_mb)/1024) FROM V$ASM_DISKGROUP;

NAME TYPE TOTAL_GB FREE_GB REQUIRED_MIRROR_FREE_MB CEIL((USABLE_FILE_MB)/1024)

ORACLE_DATA1 EXTERN 466 236 0 236

ORACLE_FLASH1 EXTERN 131 124 0 124

ORACLE_FLASH2 EXTERN 10 9 0 9

2.Using ASMCMD tool

Make sure to setup ORACLE_SID and ORACLE_HOME environment variables to ASM instance.

-bash-3.2$ asmcmd -p

ASMCMD [+]>; du

Used_MB Mirror_used_MB

12011 12011

ASMCMD [+] >;

or run

lsdg command

State Type Rebal Unbal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Name

MOUNTED EXTERN N N 1024 4096 1048576 40960 37628 0 37628 0 ORADATA/

MOUNTED EXTERN N N 1024 4096 1048576 32768 32675 0 32675 0 ORADATA1/

3. Using Grid Control

Go to asm host and select asm instance. Go Administration tab and input asm administration password:

ORA-00980: synonym translation is no longer valid

2013-04-14T22:57:00.001-07:00

This error occurs when you have a public synonym defined and the object referencing or owning the synonym has been dropped or deleted. You can check for the invalid synonyms using below query.

select * from dba_synonyms s

where table_owner not in(‘SYSTEM’,'SYS’)

and db_link is null

and not exists

(select 1

from dba_objects o

where s.table_owner=o.owner

and s.table_name=o.object_name);

Solution:

Replace the synonym with the name of the object it references or re- create the synonym so that it refers to a valid table, view, or synonym.
The Invalid synonyms must be dropped and recreated with the right owner. I normally do this using below query.(In this example the owner of the synonym was dropped and I am altering the synonym to reference the new owner)

DROP PUBLIC SYNONYM SYNONYM_NAME;
CREATE PUBLIC SYNONYM SYNONYM_NAME FOR NEW_OWNER.OBJECT_NAME;

Parallel Concurrent Processing(PCP)

2013-03-22T03:07:00.001-07:00

PCP makes sense on a RAC environment.

POINT1: Check if you require PCP to load balance or to be used as failover.

If both the nodes then, Change the names of the concurrent managers say SM:Node A and Assign PRIMARY and SECONDARY Nodes to that manager.

Then duplicate the record and change the name to SM:Node B for the 2nd manager and change the PRIMARY and SECONDARY Nodes
Note: Ensure the hostname should be only the name of the host from uname -a command, not the virtual hostname.

Do it for all the managers in the instance including tx mgr, internal mgr EXCEPT Internal Monitor.

Following configuration details are from metalink document 362135.1 section 3.12.

Check PCP Prerequisites

It is assumed that you have more than one Concurrent processing tiers in your environment to setup PCP. If you do not have this refer to OracleMetaLink Note: 230672.1 for cloning of Applications tier.

Set Up PCP

1. Execute AutoConfig by using $COMMON_TOP/admin/scripts//adautocfg.sh on all concurrent nodes.
2. Source the application environment by using $APPL_TOP/APPSORA.env
3. Check the configuration files tnsnames.ora and listener.ora located under 8.0.6 ORACLE_HOME at $ORACLE_HOME

/network/admin/. Ensure that you have information of all the other concurrent nodes for FNDSM and FNDFS entries.
4. Restart the application listener processes on each application node.
5. Logon to Oracle E-Business Suite 11i Applications using SYSADMIN in login and System Administrator Responsibility.

Navigate to Install > Nodes screen and ensure that each node in the cluster is registered.
6. Verify whether the Internal Monitor for each node is defined properly with correct primary and secondary node

specification and work shift details. Also make sure the Internal Monitor manager is activated by going into Concurrent ->

Manager -> Administrator and activate the manager.
(e.g. Internal Monitor: Host2 must have primary node as host2 and secondary node as host3)
7. Set the $APPLCSF environment variable on all the CP nodes pointing to a log directory on a shared file system.
8. Set the $APPLPTMP environment variable on all the CP nodes to the value of the UTL_FILE_DIR entry in init.ora on the

database nodes. This value should be pointing to a directory on a shared file system.
9. Set profile option ' Concurrent: PCP Instance Check' to OFF if DB instance sensitive failover is not required. By setting

it to 'ON' Concurrent Managers will failover to a secondary middle-tier node when database instance it is connected goes down.

Set Up Transaction Managers

1. Shut down the application tiers on all the nodes.
2. Shut down all the database instances cleanly in RAC environment using
SQL>shutdown immediate;
3. Edit $ORACLE_HOME/dbs/_ifile.ora. Add the following parameters:
* _lm_global_posts=TRUE
* _immediate_commit_propagation=TRUE
4. Start the instances on the database nodes, one by one.
5. Start up the Application tier on all the nodes.
6. Log on to Oracle E-Business Suite 11i Applications using SYSADMIN in login and System Administrator Responsibility.
Navigate to Profile > System and change the profile option ‘Concurrent: TM Transport Type' to ‘QUEUE' and verify the

transaction manager works across the RAC instance.
7. Navigate to Concurrent > Manager > Define screen, and set up the primary and secondary node names for transaction

managers.
8. Restart the concurrent managers.

Load Balance the Concurrent Processing Tiers

1. Create a load balancing alias similar to _806_balance as shown sample in
Appendix C.
2. Edit the applications context file through Oracle Applications Manager interface and set the value of Concurrent Manager

TWO_TASK to load balancing alias created in previous step.
3. Execute AutoConfig by using $COMMON_TOP/admin/scripts//adautocfg.sh on all concurrent nodes.

Appendix C: Example for Concurrent Processing Tiers

Sample for Concurrent Processing Tiers

_806_BALANCE=
(DESCRIPTION_LIST=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=))
(CONNECT_DATA=
(SERVICE_NAME=)
(INSTANCE_NAME=)
)
)
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=))
(CONNECT_DATA=
(SERVICE_NAME=)
(INSTANCE_NAME=)
)
)
)

Check test case below:

1. All tnsnames.ora files in the $TNS_ADMIN(806)_HOME's and IAS_ORACLE_HOME/network/admin/ should be identical.

2. The following is an example:

DEV =
(DESCRIPTION =
(ENABLE=BROKEN)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(PORT = 1521)(HOST = hostname1))
(ADDRESS = (PROTOCOL = TCP)(PORT = 1521)(HOST = hostname2))
)
(CONNECT_DATA = (SERVICE_NAME = devdb)(SERVER=DEDICATED)
)

DEV1= (DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST= hostname1)(PORT=1521))
(CONNECT_DATA=(INSTANCE_NAME=dev1)(SERVICE_NAME=devdb))
)

DEV2= (DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST= p201)(PORT=1521))
(CONNECT_DATA=(INSTANCE_NAME=dev2)(SERVICE_NAME=devdb))
)

3. DEV is your service_name alias defined for the RAC environment.
DEV1 and DEV2 are the actual database instances. The value of INSTANCE_NAME correlates to the value of INSTANCE_NAME defined in the database init.ora files.

4. For the alias DEV, the hostname definitions need to be ordered by local host.
In the above example, this entry would exist for the tnsnames.ora for hostname1.
For the tnsnames.ora on hostname2, the entry would look like:

(ADDRESS = (PROTOCOL = TCP)(PORT = 1521)(HOST = hostname2))
(ADDRESS = (PROTOCOL = TCP)(PORT = 1521)(HOST = hostname1))

5. Please ensure that parameter name INSTANCE_NAME is used instead of SID.
INSTANCE_NAME is what is recognized in the 11i RAC environment. SID value definition seems to cause problems with how the

database connection is being recognized when one of the database is shutdown.

6. After these settings start up all database instances. Make the following connections from all host machines in the 11i

Applications environment:

a. apps/@dev
b. apps/@dev1
c. apps/@dev2

NOTE: If you setup autoconfig then you do not have to manually edit 806 tnsnames. Follow metlaink doc 362135.1 section 3.11

Oracle table reorganization: Online or Offline

2013-02-24T00:43:00.000-08:00

When using tables with large amounts of rows and especially after a lot of rows have been deleted from such a table, reorganizing the table may improve the performance of the table.

Oracle knows two types of table reorganizations.

Rebuilding the table by means of recreating it
Rebuilding the table by shrinking its free space ( Oracle 10g and up )

Below the two methods are explained.

Rebuilding the table by means of recreating it

There are two ways of rebuilding a table by recreating it. The first option is to export all the data into a file using the export utility.
After that truncate ( of recreate ) the table and reload the data back into it. The disadvantage here however is a long downtime.

Another method is moving the table either to another tablespace or within the same tablespace.

The advantage here is that the downtime will me much less, but it also has some disadvantages:

The tablespace needs to be able to store this second copy of the table
The tables indexes, any depending objects like packages, procedures can become INVALID and might need to be rebuild
There will be a table-level exclusive lock on the table involved. Any application using that table will be affected by this lock.

By moving the table to another tablespace temporarily, the DBA is also able to reorganize the tablespace.

Rebuilding the table by shrinking its free space
Starting with Oracle 10g all the above is replaced by a very nice feature called 'SHRINK SPACE';
As of this release, tables can be reorganized online, without affecting the users functionality.
That is:

* The table itself must be in an ASMM tablespace
* The table must have row movement enabled
* At the end of the action a shortt table level lock takes place to adjust the HWM (Highwater Mark) of the table.

And it's easy !!
Just enable 'row movement' and start shrinking tables:

SQL> ALTER TABLE MYTABLE ENABLE ROW MOVEMENT;
This enables row movement of the table. THis is necessary so that oracle is able to actually move the rows to other free extents in order to shrink the space needed.

SQL> ALTER TABLE MYTABLE SHRINK SPACE COMPACT;
This shrinks the used space of the table, but does not update the HWM. This is usefull if you don't want to have a table-level lock during business hours.

SQL> ALTER TABLE MYTABLE SHRINK SPACE;
This command shrinks the contents of the table and subsequently updates the HWM. This statement can also be used after a shrink with 'compact' option has been done, just to update the HWM.

SQL> ALTER TABLE MYTABLE SHRINK SPACE CASCADE;
This command shrinks the contents of the table and all dependent objects like indexes.

High Water Mark:

Now we have mentioned the High Water Mark (HWM) a couple of times. A short explanation:
The HWM is the pointer to that location of a table where on any point in history the table has grown to. If a lot of rows are deleted from a table, a lot of free space exists before the HWM. Doing a full table scan Oracle will not only read the actual rows, but also the empty space up to the HWM.
This is wasted time and is worsening the performance of getting data from that table.
So if from a large table a lot of rows are deleted, e.g. a count(*) will still take as long as before the delete.

Triggers and indexes:

In contradiction to moving a table, or exporting/importing its data, triggers and indexes are NOT affected by a shrink space command. They will remain valid and functional during and after the operation.

Session6 RAC11g Database Creation using ASM

2013-02-11T21:14:00.001-08:00

R12.1.2 New Feature of EBS cloning (With Rapid Clone)

2013-01-30T06:43:00.004-08:00

As many of you know a cloning is one of the most DBA time consuming operations. For the first few times an Apps DBA may enjoy it. However, after the 10th cloning it becomes a boring routine.

If you have tried to automate EBS cloning then you definitely know that you can’t get through without customizing the Oracle-provided Rapid Clone utility. One of the areas we should customize to make it running in batch scripts is the services startup bit. The problem is that by default straight after Rapid Clone is completed Oracle starts all the services on the Apps node and until now there wasn’t a way to change it.

There are many reasons why you wouldn’t start Apps processes straight after Rapid Clone is completed.

Among them are APPS user password change (this is typical operation for cloning)
SSO integration
CM/CR disabling etc.

R12.1.2 brings on board a small but very nice feature. From now on, Rapid Clone will ask you if you would like to start services at the end of Rapid Clone call:

Do you want to startup the Application Services for EBSPRD? (y/n) [y] : n

How great is this? Now we can specify that we don’t want to starup services and run after cloning steps before we are ready to start the environment.

General Forms Related Issues in Oracle 11i/R12

2013-01-22T02:40:00.004-08:00

1) FRM-92050 FAILED TO CONNECT TO SERVER...
********************************************

Resolution:
***********

UNDER THE oracle\jinitiator xxx\jcache directory you have the cache files residing there.
Clear the cache by removing files that have a .d00 or .i00 extension.

2) FRM-92050: Failed to connect to the Server: [hostname:port]
**************************************************************

Resolution:
***********

Permissions are not set correctly in the MS Internet explorer.
Correct the permissions\security in IE

1. Launch Microsoft Internet Explorer 8.0.
2. Select the Tools -> Internet Options from the top level menu.
3. Click the SECURITY Tab:

- Choose Local Intranet Zone from the list.
- Click Custom Level.
- Select Java Permissions -> Custom from the list.
- Click Java Custom Settings.
- Select the Edit Permissions tab.
- Choose Enable for Run Unsigned Content.
- Choose Enable for Run Signed Content.

4. Select the ADVANCED tab.

- Scroll down to Java VM.
- Check the Java console enabled option.

5. Select the CONNECTIONS tab.

- Click the LAN Settings button.
- If IE is using a proxy server:
- Check the Bypass proxy server for local addresses check box.
- Click the Advanced button, and enter the local domain in the Exceptions box. Use the form *.domain.com, where
domain.com maps to your local domain.
- Click OK to exit the dialog.

6. Click OK to save the changes and exit IE.

7. Restart the computer.

3) FRM-92101 a failure has occured on the server.
**********************************************

You will need to reestablish your session.

OR

FRM-92050 when using the working Directory parameter improperly
***************************************************************

Resolution:
***********

Error comes up after changing working Directory parameter in the formsweb.cfg file in Forms 6i and Forms 9i using Forms
ListenerServlet.

Javaconsoleoutput:

CMa3iK no proxyoracle.forms.net.ConnectionException:Forms session <1> failed during startup: no response from runtime
process at oracle.forms.net.ConnectionException.createConnectionE xception(Unknown Source) at
oracle.forms.net.HTTPNStream.getResponse(Unknown Source)...

and

Jserv.log or OC4J.log:

[26/11/2002 19:42:17:921 GMT+05:30] Forms session <1> aborted: runtime process failed during startup with errors
Unable to switch to Working Directory:d:\test;d:\f60
Forms session <11> aborted: runtime process failed during startup with errors
Unable to switch to Working Directory:<invalid working directory>

Set workingDirectory parameter to a single, accessible, directory instead of a directory list or a non-accessible directory.

FRM-92050 failed to connect to Server /forms/servlet -1 in 11i and R12 with IE8

2013-01-22T00:35:00.000-08:00

If you are trying to connect to Apps 11i/R12 instance from IE 8 and hitting error “FRM-92050 failed to connect to Server /forms/servlet -1“ while opening forms (as shown in figure below)

Then check version of IE

If version of IE is 8 then disable XSS filter

Steps to disable XSS filter

FRM-902050: Failed to connect to the server: /forms/lservlet:-1

2013-01-22T00:32:00.002-08:00

FRM-902050: Failed to connect to the server: /forms/lservlet:-1

Not able to access oracle e-business suite R12 forms from internet explorer . When any form link is clicked this error is thrown out.

\

Just add the address in local intranet zone's trusted sites zone

Do it this way (Internet explorer 8)

Tools>Internet Options>Security>Local Intranet > Sites > Advanced > Add - "add oracle applications address here"

How to Create database on Oracle 11g RAC with ASM

2013-01-07T06:04:00.001-08:00

Happy New Year Everyone

Please check the following link:

http://youtu.be/Xhjzdhm4amw

Oracle Global Temporary Table

2012-12-21T02:19:00.006-08:00

These are special kind of tables in which Oracle have structure and definition same as other usual Oracle tables but where data is not persistent.Data is only session specific or transaction specific. This means that users can insert data into the table simultaneously but they will never see each others data.

Syntax:

CREATE GLOBAL TEMPORARY TABLE SCHEMA_NAME.TABLE_NAME
(
COL1 DATATYPE,
COL2 DATATYPE,
.
.
.
COLNn DATATYPE
)
ON COMMIT DELETE ROWS
NOCACHE;

There are two options available for ON COMMIT clause

DELETE ROWS: truncate data after each commit.

PRESERVE ROWS: truncate table when session ends.

Example of usage of GLOBAL TEMPORARY TABLE:

There can be several scenarios where using a PL/SQL table is not suitable or may be more complicated than using a temporary table.For example suppose you have some data coming into a remote database at regular intervals. You want to transfer those data into your local database but only after processing. Once you process your data you may want to join these data with other tables in your local database and then based on the results you will insert them into your local database.

In this case you can create a global temporary table and perform the steps as follows:

1. Get your remote data into a cursor using database link

2. Processes your data based on your requirement (such as filtering/modifying).

3. Insert the changed data into a global temporary table.

4. Join the global temporary table with other local oracle tables based on your requirement.

5. Put the resultant data into its final destination table in your local database or to another remote database.

Limitations:

However there are some limitations in using global temporary table. They can not have any foreign keys; they can not have
any storage clause and they can not be partitioned.

Oracle 11g RAC Training with ASM

2012-12-10T03:24:00.003-08:00

For new batch of Oracle 11g RAC Training with ASM contact me at imnitish@gmail.com

For more details check the following URL:

http://www.youtube.com/watch?v=y9Wgo0XpuUs&feature=colike

All About Apache Issues ...

2012-11-26T04:15:00.000-08:00

0)General Information to Troubleshoot Apache Issues.

************************************************************************************************************************

Sometimes there will be problem in our Apache Server where although the status shown is not running, there was still httpd running on the background.

What you can do is shutdown the Apache listener process and clear all the httpd background process.

$ ps -ef | grep Apache | wc -l

$ cd $COMMON_TOP/admin/scripts/$CONTEXT_NAME

$ ./adapcctl.sh status

Apache Web Server Listener is not running.

Apache Web Server Listener (PLSQL) is not running.

adapcctl.sh: exiting with status 1

$ ./adapcctl.sh stop

adapcctl.sh version 115.55

Apache Web Server Listener is not running.

Apache Web Server Listener (PLSQL) is not running.

adapcctl.sh: exiting with status 0

$ ps -ef | grep Apache | wc -l

$ kill -9 `ps -ef | grep Apache | grep applprod | grep -v grep | awk '{print $2}'`

$ ps -ef | grep Apache | wc -l

$ ./adapcctl.sh start

adapcctl.sh version 115.55

Apache Web Server Listener is not running.

Starting Apache Web Server Listener (dedicated HTTP in SSL mode) ...

Apache Web Server Listener (PLSQL) is not running.

Starting Apache Web Server Listener (dedicated PLSQL) ...

adapcctl.sh: exiting with status 0

$ ps -ef | grep Apache | wc -l

***************************************************************************************************************************************

1)If you mistype any directive in Apache (httpd.conf or any conf file), Apache will not start & it will not write even in error_log. In these case it will report any error message to file

$COMMON_TOP/admin/log/$CONTEXT_NAME/adapcctl.txt

So first check in the above file

***************************************************************************************************************************************

2)Found *.pid file in Apache_Top/Apache/logs/ even after bounce of an Apache web server (When Apache starts it creates .pid file in logs directory and when you shutdown apache it cleans this .pid file). So if this file is already there then Apache will not start.

Fix: Simply move this file

***************************************************************************************************************************************

3)Could not bind port (Port already in Use) or Sometimes apache and concurrent

Manager will interchange their port no.’s while starting while results error and both of them will not up

Fix: To overcome this up the services in this manner

1)Listener

2)Apache web server

3)Concurrent manager

4)adstrtal.sh

***************************************************************************************************************************************

4)Error 500 - Internal server error

Fix: 1) Clear Cache (Application Server Cache)and start the services

***************************************************************************************************************************************

5)If there are no error messages in error_log or error_log_pls

then there might be issues in starting Jserv component of web server (which caters Java requests by mod_jserv)

***************************************************************************************************************************************

6)Mobile is enabled in zone.properties disable it if you are not using it.

***************************************************************************************************************************************

7)If you don't know port number of your web server you can check it in file $IAS_ORACLE_HOME/Apache/Apache/conf/httpd.conf with directive Listen & Port

***************************************************************************************************************************************

8)Once you are able to access http://hostname.domainname:port and if you are getting any error message that means your web server (Core Apache) is working but there are various components called as modules and there may be issues with those modules which we will check these modules . few modules of our interest are

-mod_jserv

-mod_pls

-mod_oprocmgr

-mod_ossl (If you have ssl enabled on web server)

-mod_osso (If single sign on server is enabled on web server)

***************************************************************************************************************************************

9)Once in a while adapcctl.sh start will not work then ultimate you have to go and

invoke apachectl script file in Apache top/apache/bin to start Apache webserver.

***************************************************************************************************************************************

10)If we remove _pages from COMMON_TOP it will be created for the next time only when we bounce and up the Apache web server.

***************************************************************************************************************************************

11)Jsp pages are complied with the file ojspcompile.pl executable. It is located at $JSP_TOP/bin/ and in R12 it is there in

$FND_TOP/patch/115/bin/

***************************************************************************************************************************************

12)Error : Your session is no longer valid while trying to loging to applications.

The issue is resolved by changing the profile option session_cookie_domain

to null from domain.com

***************************************************************************************************************************************

13)HTTP-403 forbidden message after entering the username and password in the "Apps Logon Links" page. [Not authorised to view this page]

Check for Apps password in $APACHE_TOP/modplsql/cfg/wdbsvr.app ,Comment out custom_auth entry in $APACHE_TOP/modplsql/cfg/wdbsvr.app and bounce apache.

***************************************************************************************************************************************

14)Error message: You have insufficient privileges for the current operation.

Verify and update SESSION_COOKIE_DOMAIN in ICX_parameters if it is wrong.

Example :

update icx_parameters set SESSION_COOKIE_DOMAIN='NEW_VALUE' where SESSION_COOKIE_DOMAIN='OLD_VALUE'

***************************************************************************************************************************************

15)AppsLocalLogin?.jsp page error outs

Edit the $IAS_ORACLE_HOME/Apache/Jserv/etc/jserv.properties

Set wrapper.bin.parameters=-DLONG_RUNNING_JVM=false

add the line wrapper.bin.parameters=-DCACHEMODE=LOCAL

Restarted Apache for these changes to take effect and retested the issue

16) Login to the applications URL fails with

500 Internal server Error

java.lang.NoClassDefFoundError at

oracle.apps.fnd.sso.AppsLoginRedirect.AppsSetting(AppsLoginRedirect.java:126) at

oracle.apps.fnd.sso.AppsLoginRedirect.init(AppsLoginRedirect.java:170) at com.evermind[Oracle

Containers for J2EE 10g (10.1.3.0.0)

].server.http.HttpApplication.loadServlet(HttpApplication.java:2231) at com.evermind[Oracle

Containers for J2EE 10g (10.1.3.0.0)

].server.http.HttpApplication.findServlet(HttpApplication.java:4617) at com.evermind[Oracle

Containers for J2EE 10g (10.1.3.0.0)

].server.http.HttpApplication.findServlet(HttpApplication.java:4541) at com.evermind[Oracle

Containers for J2EE 10g (10.1.3.0.0)

Performed the below steps, but it did not resolved the issue

1) Bounced Apache server, cleared cache and validated ports

2) Bounced all Middle tier services and killed defunct processes

3) Bounced Unix Server

Solution

=======

Java object Caching Configuration parameter was Enabled in oc4j.properties

$INST_TOP/ora/10.1.3/j2ee/oacore/config

Disabled LONG_RUNNING_JVM=false from True

Bounced Apache server and could login to the applications URL without issues

ISSUES AND SOLUTIONS

ISSUE 1:

Description

When Apache script adapcctl.sh is issued.It shows that Apache is started.On checking the status Apache is stopped

Cause

The error log file in the folder

cd $APACHE_TOP/Apache/logs

Showed

fopen: No such file or directory httpd: could not log pid to file /var/opt/_/Apache/logs/httpd_pls.pid

SOLUTION 1

Created the missing directories /_/Apache/logs under /var/opt folder

Bounced Apache

ISSUE 2:

Description

oacore was not comming up in the configurator node.

opmnctl status

ias-component process-type pid status

OC4J oacore N/A Down

Because of this configurator cannot be invoked.

Cause

Check the oacore error log.

Example:

Below Log file gives root cause. //mtlog/_/logs/ora/10.1.3/opmn/oacore_default_group_2/oacorestd.err

14:11:56 Error initializing server: //inst/apps/_/ora/10.1.3/j2ee/oacore/config/server.xml,

Fatal error at line 70 offset 1 in file://inst/apps/_/ora/10.1.3/j2ee/oacore/config/server.xml: .:

XML-20100: (Fatal Error) Expected 'EOF'.

SOLUTION 2

Edit the /sid/inst/apps/SID_hostname/ora/10.1.3/j2ee/oacore/configserver.xml file and remove the extra line in the file. Restart and check oacore will be starting. This sudden change in server.xml seems to be because of a bug.

Bug - 6702510

ISSUE 3

AppsLocalLogin?.jsp page was displaying errors

Cause

Extract from the Jserv log file =========================== [27/02/2008 16:57:14:447 CST] weboamLocal/oracle.apps.fnd.oam. servlet.ui.OAMServlet:

Sucessfully initialize oaosu.OAMServlet [27/02/2008 16:57:14:447 CST] Servlet Zone rootauohstrzt11 initia lization complete [27/02/2008 16:57:15:101 CST]

Creating noun for: oracle.jsp.JspServlet [27/02/2008 16:57:15:102 CST] Creating noun for: AppsLocalLogin?.jsp

[27/02/2008 16:57:15:120 CST] oracle.jsp.JspServlet: init [27/02/2008 16:57:28:066 CST] Creating noun for: oracle.jsp.JspServlet

[27/02/2008 16:57:28:067 CST] Creating noun for: AppsLocalLogin?.jsp [27/02/2008 16:57:28:078 CST] oracle.jsp.JspServlet: init

[27/02/2008 16:57:29:753 CST] JspServlet?: unable to dispatch to requested page: Exception:java.lang.NoClassDefFoundError

SOLUTION 3:

Edited the $IAS_ORACLE_HOME/Apache/Jserv/etc/jserv.properties

a) Set wrapper.bin.parameters=-DLONG_RUNNING_JVM=false

b) Add the line wrapper.bin.parameters=-DCACHEMODE=LOCAL

c) Restarted Apache for these changes to take effect and retested the issue

ISSUE 4

New Message when starting Apache using adapcctl.sh start

Cause

It takes 7/10 minutes to clear this cache directory if present

apache Web Server Listener is not running.

Starting Apache Web Server Listener (dedicated HTTP) ...

Removing gantt cache directory

Successfully removed the gantt cache directory:/tcge1i/applmgr/common/html/cabo/images/cache/gantt

Apache Web Server Listener (PLSQL) is not running.

Starting Apache Web Server Listener (dedicated PLSQL) ...

SOLUTION 4

Wait for atleast 7 to 10 minutes and this cache directory will be cleared and Apache will be started successfully

ISSUE 5

Apache server is up and running.

But adapcctl.sh status shows

adapcctl.sh version 115.54

Apache Web Server Listener is not running. Apache Web Server Listener (PLSQL) is not running.

adapcctl.sh: exiting with status 1

Cause

Incorrect Lock file path given in httpd.conf

LockFile? $APACHE_TOP/Apache/log/PUPONI_httpd.lock

and

Incorrect path given in adapcctl.sh script.

SOLUTION 5:

Modified the path of lock file in $APACHE_TOP/Apache/conf/httpd.conf file.

LockFile? /var/opt/SID_/httpd.lock

and

Modified the path of adapcctl.sh file by commenting the wrong path

ISSUE 6

After selecting the E-Business home page got internal server error Internal Server Error The server encountered an internal error or misconfiguration and was unable to complete your request. Please contact the server administrator, and inform them of the time the error occurred, and anything you might have done that may have caused the error.

More information about this error may be available in the server error log.

Cause

Found that all the properties files as below

jserv.properties jserv_restrict.properties zone.properties zone_restrict.properties

were pointing to a dbc file name _.dbc

which was not physically present at the path

/sid/applmgr/11510/fnd/11.5.0/secure/_

$ cd //applmgr/11510/fnd/11.5.0/secure/_

$ ls .dbc

SOLUTION 6:

copied the file

twwyai.dbc

which was physically present at path

//applmgr/11510/fnd/11.5.0/secure/_

to the dbc file name which was being pointed by all the properties files

$ cp .dbc _>sid>.dbc

$ ls

_>sid>.dbc .dbc

This resolved the issue.

ISSUE 7

Instance is very slow / inaccessible with a warning on login page:

Warning

Low-level logging is currently enabled. Your application will not perform as well while Low-level logging is on.

Cause

Logging was enabled through profile values:

FND: Debug Log Enabled -- Yes

FND: Debug Log Level -- Statement

FND: Debug Log Module -- %

SLA: Enable Diagnostics -- Yes

FND Validation Level -- Error

SOLUTION 7

1. Change these values to:

FND: Debug Log Enabled -- No

FND: Debug Log Level -- ( Blank )

FND: Debug Log Module -- ( Blank )

SLA: Enable Diagnostics -- No

FND Validation Level -- None

2. Bounce Apache on all the MTs and clear Apache cache.

ISSUE 8

500 Internal Server Error java.lang.NoClassDefFoundError at oracle.apps.fnd.sso.AppsLoginRedirect.AppsSetting(AppsLoginRedirect?.java:126) at oracle.apps.fnd.sso.AppsLoginRedirect.init(AppsLoginRedirect?.java:170) at com.evermind[Oracle Containers for J2EE? 10g (10.1.3.0.0) ].server.http.HttpApplication.loadServlet(HttpApplication?.java:2231) at com.evermind

Performed the below steps, but it did not resolved the issue

1) Bounced Apache server, cleared cache and validated ports

2) Bounced all Middle tier services and killed defunct processes

3) Bounced Unix Server

Cause

No Cause Entered

SOLUTION 8

Java object Caching Configuration parameter was Enabled in oc4j.properties $INST_TOP/ora/10.1.3/j2ee/oacore/config

Disabled LONG_RUNNING_JVM=false from True

Bounced Apache server and could login to the applications URL without issues

Java Caching is a mechanism used to improve performance by storing data in the Middle Tier JVM memory, instead of retrieving the data from the database every time it is requested.

ISSUE 9

When trying to access the URL (Either Big IP or the node level), it hangs for a long time and getting redirected to a different URL and gives a blank page. For example, When try to access the ipayments URL which is hosted on node2, it hangs for a long time and its getting redirected towards the Sales URL which is hosted on node3, and displays a blank page.

Cause

All the host reference and the URL directives are pointing towards node3 and sales URL respectively, in the below files. Hence every attempt tries to launch the sales URL and get fails as the apache configuration files and parameters are pointing towards the correct one.

jserv.conf forms.properties zone.properties jserv.properties

SOLUTION 9

Stop Apache. Edit the files jserv.conf, forms.properties, zone.properties, jserv.properties, jserv.conf to replace the host reference and the URL directives to the correct one (In our case, its node3 . Clear the cache and restart the Apache.

ISSUE 10

Apache login issues occurs in multi MT environment. Unable to login to oracle applications.

Cause

Issue due to distributed cache.

SOLUTION 10

Set is_distributed to false in javacache.xml Bounce Apache.

ISSUE 11

Apache server is down. Apache logfile did not provide useful information.

Started the Apache server, but the Apache Web listener did not started.

1) Verified no log files (Apache,Jserv) crossed 2GB limit. error_log, access.log, mod_jserv

2) Veified that the port is listening (netstat -an |grep ) Apache port(httpd.conf), pls listener(httpd_pls.conf)

3) Verified that the Apache and Jserv configuration files are not changed recently httpd.conf & Jserv.conf

4) Shutdown all the Middle tier services and check for ipcs (semaphores) did not resolve the outage.

$ ipcs on application middle tier as Application user ap

Cause

No Cause Entered

SOLUTION 11

Rebooted the server and started the services. All services came back normal.

ISSUE 12

Unable to login to the home page URL. Login fails with the error:

Error Page

You have encountered an unexpected error. Please contact the system administrator for assistance.

Click here for exception details

Checks:

Upon clicking the link, can observe the error:

oracle.apps.fnd.framework.OAException: Application: FND, Message Name: SQL_PL SQL_ERROR. Tokens: ROUTINE = createSession(int)(userId=6,sessionMode='null','2D8 FCCC57459B66EE040558CDBC6118E44964630721347149122084914143412?'); REASON = java.s ql.SQLException: ORA-01422: exact fetch returns more than requested number of ro ws ORA-06512: at "APPS.FND_SESSION_MANAGEMENT", line 295 ORA-06512: at "APPS.FND_SESSION_MANAGEMENT", line 455 ORA-06512: at line 1 ; ERRNO = 1422;

Cause

FND_NODES table was updated with the same server_id for all Nodes.

SOLUTION 12

Update the table FND_NODES with correct server_ids for all nodes as in $FND_TOP/secure/sid_host/sid.dbf

Bounced apache on all apache nodes.

and Run select node_name, server_id from fnd_nodes;

to confirm the values.

Below is the example for the solution:

Found three hosts using same server_id:

SQL> select node_name, server_id from fnd_nodes; NODE_NAME SERVER_ID

AUOHSACPS06 2D8FCCC57459B66EE040558CDBC6118E44964630721347149122084914143412

AUOHSACPS03 2D8FCCC57459B66EE040558CDBC6118E44964630721347149122084914143412

AUOHSACPS02 2D8FCCC57459B66EE040558CDBC6118E44964630721347149122084914143412

reset server_id with original server_id from each hosts .dbc file @ $FND_TOP/secure:

SQL> select node_name, server_id from fnd_nodes; NODE_NAME SERVER_ID

AUOHSACPS06 2D8FF21843830F82E040558CDBC6195C27690802237516122831445560236127

AUOHSACPS03 2D90005D5A13C774E040558CDBC61D9436546148971863330582284123371818

AUOHSACPS02 2D8FCCC57459B66EE040558CDBC6118E44964630721347149122084914143412

ISSUE 13

Users getting '404 page not found errors' when tyring to access the URL

(OR)

Getting Exception in thread "main" java.lang.ClassFormatError: Truncated class file

(OR)

adstrtal.sh or adstpall.sh is throwing below error message

You are running adstrtal.sh version 115.16

Exception in thread "main" java.lang.ClassFormatError: oracle/apps/ad/context/AppsContext (Truncated class file) at java.lang.ClassLoader.defineClass0(Native Method) at java.lang.ClassLoader.defineClass(ClassLoader?.java:539) at java.security.SecureClassLoader.defineClass(SecureClassLoader?.java:123) at java.net.URLClassLoader.defineClass(URLClassLoader?.java:251) at java.net.URLClassLoader.access$100(URLClassLoader?.java:55) at java.net.URLClassLoader$1.run(URLClassLoader?.java:194) at java.security.AccessController.doPrivileged(Native Method) at java.net.URLClassLoader.findClass(URLClassLoader?.java:187) at java.lang.ClassLoader.loadClass(ClassLoader?.java:289) at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:274) at java.lang.ClassLoader.loadClass(ClassLoader?.java:235) at java.lang.ClassLoader.loadClassInternal(ClassLoader?.java:302) at oracle.apps.ad.autoconfig.ServiceControl.(ServiceControl?.java:319) at oracle.apps.ad.autoconfig.ServiceControl.main(ServiceControl?.java:662) Check logfile //applmgr/common/admin/log/_/08061104.log for details

Exiting with status 1

(OR)

[06/08/2007 11:03:12:181] (ERROR) an error returned handling request via protocol "ajpv12" [06/08/2007 11:03:12:181] (EMERGENCY) ajp12: can not connect to host140.85.188.36:20021 [06/08/2007 11:03:13:184] (EMERGENCY) ajp12: can not connect to host 140.85.188.36:20021 [06/08/2007 11:03:15:038] (EMERGENCY) ajp12: auth did not receive challenge size [06/08/2007 11:03:15:038] (EMERGENCY) ajp12: auth fail

Cause

Extract from the JVM log file: //product/iAS/Apache/Jserv/logs/jvm/OACoreGroup.0.stderr

Exception in thread "main" java.lang.ClassFormatError: Truncated class file at org.apache.jserv.JServServletManager.load_init(JServServletManager?.java:765) at org.apache.jserv.JServServletManager.loadServlet(JServServletManager?.java:659) at org.apache.jserv.JServServletManager.loadStartupServlets(JServServletManager?.java:789) at org.apache.jserv.JServServletManager.init(JServServletManager?.java:447) at org.apache.jserv.JServ.start(JServ.java:625)

Verified that there are no log files that exceed 2GB limit (OS limit).

Clearing the Apache and Jserv cache did not resolve the issue. Unmount / mount product specific file system did not resolve

SOLUTION 13:

Try each of these solutions until the problem is resolved, in order to have the least impact on the system.

1. If you can determine the specific class file from the log files, use the 'touch' command.

e.g. touch classfile

If necessary, restart Apache and clear server cache.

2. Shut down all MT services, exit the Apps owner and ask the System Admin to unmount and remount the /sid/applmgr file system (mount point above $JAVA_TOP). Ask the System Admin to kill any processes that may be holding the file system open. Then log in to the Apps owner and restart MT services.

3. Shutdown all the MT services and ask System Admin to reboot the server if the actions above don't resolve the outage.

ISSUE 14:

Got login error - Internal Server Error after bounced all services on . find following message from Jserv log file:

$ tail -8 mod_jserv.log

[07/05/2007 18:41:06:915] (ERROR) ajp12: Servlet Error: java.lang.NoClassDefFoundError: null

[07/05/2007 18:41:06:916] (ERROR) an error returned handling request via protocol "ajpv12"

[07/05/2007 18:41:06:916] (ERROR) balance: 11906 internal servlet error in serverauohsnops03.oracleoutsourcing.com:20720

[07/05/2007 18:41:06:916] (ERROR) an error returned handling request via protocol "balance"

[07/05/2007 20:07:52:732] (ERROR) ajp12: Servlet Error: java.lang.NoClassDefFoundError: null

No runaway, no lock on port 20720.

SOLUTION 14:

Shutdown all services on MT. Removed all log file (standard error...) from Jserv/jvm.

Started All services with successful login.

ISSUE 15

Customer reported Not able to login with “Internal Server Error” on .

We could not start some of services during bounced All Services with following error message: adapcctl.sh version 115.47

Cannot complete applications logon. You may have entered an invalid applications password, or there may have been a database connect error.

Apache Web Server Listener is not running (dedicated HTTP) ...

Cannot complete applications logon. You may have entered an invalid applications password, or there may have been a database connect error.

Apache Web Server Listener is already stopped.

Cannot complete applications logon. You may have entered an invalid applications password, or there may have been a database connect error.

adapcctl.sh: exiting with status 2

Unable to login the application URL, facing the error while login:

Internal Server Error The server encountered an internal error or misconfiguration and was unable to complete your request. Please contact the server administrator, ap@domain.comand inform them of the time the error occurred, and anything you might have done that may have caused the error.

Cause:

SQL> select END_DATE from fnd_user where user_name='GUEST';

END_DATE

---------

05-OCT-10

SOLUTION 15

We found out the apps and applsys were not in sync.

Just manual change the apps password from sqlplus.

Restarted all services and login successfully.

Update the FND_USER table with END_DATE=null and bounced apache

SQL> update fnd_user set END_DATE=null where user_name='GUEST';

1 row updated.

SQL> commit;

Commit complete.

SQL> select END_DATE from fnd_user where user_name='GUEST';

END_DATE

-----------

ISSUE 16

Unable to login to oracle applications:

error message: You have insufficient privileges for the current operation.

Checks

Unable to login to oracle applications:

error message: You have insufficient privileges for the current operation.

SOLUTION 16

Verify and update SESSION_COOKIE_DOMAIN in ICX_parameters if it is wrong.

Example :

update icx_parameters set SESSION_COOKIE_DOMAIN='NEW_VALUE' where SESSION_COOKIE_DOMAIN='OLD_VALUE';

ISSUE 17

Apache home page is not coming up even after bounced

Found in jvm log asking for java version

SOLUTION 17

//product/iAS/Apache/Apache/bin/java.sh: /usr/java/jdk1.3.1_15/bin/java: not found

Check the version in /usr/java and update that version in java.sh file and Bounced Apache.

ISSUE 18

Unable to access applications

cause

All applications are up and running.

"Solaris Error: 12: Not enough space" errors are encounterd in database listener logfile.

SOLUTION 18:

Check if the OS is SUN Solaris.

Check if /tmp filesystem is 100% full.

Check Swap space utilization.

Check the application process that is hagging resources. (Check if any sql sessions are hagging resources)

Clear the process in issue after getting required approval.

NOTE:

The above is applicable only for environments on Solaris OS.

ISSUE 19

Not able to login to APPS. JSP Error:

Unable to create an user session or ORA-01403 : no data found has been detected in FND.SIGNON.AUDIT_USER

Checks

Check number of rows in dual table.

Select * from dual;

DUAL table should always contain only one row.

The table named DUAL is a small table in the data dictionary that Oracle and

user-written programs can reference to guarantee a known result.

This table has one column called DUMMY and one row containing the value X.

SOLUTION 19

DUAL table should always contain only one row and one column.

Insert a row with value X into dual table if the dual table contains 0 rows.

If the dual table contains more than one row then delete the rows by keeping one row.

The rows in dual table not equal to zero.

ISSUE 20

Not able to launch forms from PHP

Checks

Select system administrator responsibility and click on any menu option.

The page shows "done" without launching forms (jinit)

Check the apps version.

If the apps version is 11.5.10 then check the Check the file version of $FND_TOP/admin/template/appsweb.cfg

If the file version is 115.148, then verify that "!WindowsDPI= " exists in $FORMS60_WEB_CONFIG_FILE

The windowsDPI value should be 96. If the value for WindowsDPI is not set in the

$FORMS60_WEB_CONFIG_FILE then update PROFILE_OPTION_VALUE.

SOLUTION 20

select PROFILE_OPTION_VALUE from fnd_profile_option_values where PROFILE_OPTION_ID=3769;

Update the tar with the output of the above command.

UPDATE fnd_profile_option_values

set PROFILE_OPTION_VALUE='https://.domainname.com/dev60cgi/f60cgi?windowsDPI=96' where PROFILE_OPTION_ID=3769;

This should update only one row.

commit;

ISSUE 21

Signature for "https URL redirecting to http URL"

Issue:

https URL redirecting to http URL and the page cannot be displayed error occurs.

SOLUTION 21

Check the httpd.conf and url_fw.conf, in url_fw.conf the Rewrite rule was

RewriteRule ^/$/OA_HTML/AppsLocalLogin.jsp.

Changed into

RewriteRule https://: /OA_HTML/AppsLocalLogin.jsp.

***************************************************************************************************************************************

What is Oracle RAC One Node ?

2012-11-16T01:44:00.002-08:00

Oracle introduced a new option called RAC One Node with the release of 11gR2 in late 2009. This option is available with Enterprise edition only. Basically, it provides a cold failover solution for Oracle databases. It’s a single instance of Oracle RAC running on one node of the cluster while the 2nd node is in a cold standby mode. If the instance fails for some reason, then RAC One Node detects it and first tries to restart the instance on the same node. The instance is relocated to the 2nd node in case there is a failure or fault in 1st node and the instance cannot be restarted on the same node. The benefit of this feature is that it automates the instance relocation without any downtime and does not need a manual intervention. It uses a technology called Omotion, which facilitates the instance migration/relocation. RAC One is Oracle’s answer or solution to OS clustering solution like Veritas Storage Foundation, Sun Solaris cluster, IBM HACMP, and HP Service guard etc.

Purpose

Its Oracle’s attempt to tie customers to a single vendor by eliminating the need to buy 3rd party OS cluster solutions. First, it introduced Oracle Clusterware with 10g and stopped the need to rely on 3rd party cluster software and now it intends to conquer the rest who are still using HACMP, Sun Solaris cluster etc. for cold failover.

Benefits

The Oracle RAC One node provides the following benefits:
Built-in cluster failover for high availability
Rolling patches for single instance database
Proactive migration / failover of the instance
Live migration of instances across servers
Online upgrade to RAC

The rolling upgrade is really useful. Upgrade to the OS, and Database can be done without any downtime unless upgrade requires some scripts to be run against the database. With RAC One Node, the DBA’s and Sys admins can be proactive and migrate/failover the instance to another node to perform any critical maintenance activity.

What it's not suited for

According to me the RAC one node is not a viable or recommended solution in the following scenarios:

To load balance unlike regular RAC
A true high availability solution
As a DR solution; Data guard best suits the bill
For mission critical applications

Cost

It is definitely not FREE. Oracle has priced RAC one at par with Active Data Guard. The RAC One node is priced separately and costs $10,000 per processor as against $23,000 for regular RAC. The licensing cost is required for ONE node only (in a 2-node setup). RAC one node is eligible for 10-day rule, allowing a customer to migrate to another without the need to buy additional license up to 10-days in a calendar year. People arguing against paying a license fee for resources they are not using will still lament.

Conclusion

I am still not very convinced on the usefulness of RAC one node. I think customers invest in RAC for their mission critical applications and achieving high availability and load balancing at the same time. Those who don’t go for RAC rely on Data Guard and now with 11g, on Active Data Guard. So don’t see a huge requirement for RAC One except seamless failover within a data center. The licensing is a bit disappointing; they are making clients pay $10 K. Moreover RAC is free with Standard edition though one doesn’t get enterprise features and limited to 4 CPU sockets only. So, thinking RAC One will be popular among customers who are currently using standard edition and want to switch to enterprise will be wrong.

How to reclaiming Unused LOB Space ??

2012-11-05T02:57:00.002-08:00

SELECT table_name, column_name, segment_name, a.bytes
FROM dba_segments a JOIN dba_lobs b
USING (owner, segment_name)
WHERE b.table_name = 'NITI_TEST_LOB';

TABLE_NAME COLUMN_NAME SEGMENT_NAME BYTES
------------ -------------- ---------------------------- ----------
NITI_TEST_LOB IMAGE SYS_LOB0000148988C00003$$ 50,331,648

SELECT NVL((SUM(DBMS_LOB.GETLENGTH(image))),0) AS BYTES
FROM niti_test_lob;

BYTES
----------
16,706,000

Use following command to manually shrink the LOB segment to free up the space immediately:

ALTER TABLE niti_test_lob MODIFY LOB (image) (SHRINK SPACE);

Table altered.

SELECT table_name, column_name, segment_name, a.bytes
FROM dba_segments a JOIN dba_lobs b
USING (owner, segment_name)
WHERE b.table_name = 'NITI_TEST_LOB';

TABLE_NAME COLUMN_NAME SEGMENT_NAME BYTES
------------ -------------- ---------------------------- ----------
NITI_TEST_LOB IMAGE SYS_LOB0000148988C00003$$ 65,536

SELECT NVL((SUM(DBMS_LOB.GETLENGTH(image))),0) AS BYTES
FROM niti_test_lob;

BYTES
----------
0

The alter table <table_name> shrink space statement was introduced in Oracle 10g R1.
The ability to extend the SHRINK SPACE command to LOBs was introduced in Oracle 10g R2

How to run APList.sql ?

2012-10-30T22:54:00.001-07:00

This test displays ALL of the Data for ALL of the tables used to process an invoice in AP.

FILE NAME:   APList.sql
PRODUCT:    501 -Oracle Payables
SUBCOMPONENT:  PAYMENT
PRODUCT VERSIONS: 11.5, 11.0, 10.7
PLATFORM:    All Platforms
PARAMETERS:   Invoice/Check Number
                             Supplier Name
                             Invoice ID
                             Print Null Columns Y or N - Default N
                             Details Layout Portrait (P) or Landscape (L) - Default P
                            GL Details Y or N - Default N

Included Files:

CoreApiHtml.sql
APListh.sql (HTML version)
CoreApiTxt.sql
APListt.sql (Text version)

Execution Environment:

SQL*Plus

Access Privileges:

Requires APPS user access

Usage:
sqlplus apps/password @APListh.sql (HTML version)
sqlplus apps/password @APListt.sql (Text version)

Instructions:
The files should be unzipped to a common directory.
For HTML output (recommended), run the file APListh.sql in SQL*Plus in the APPS schema.
Follow the prompts and enter the requested information. The test will create an output file in html
format named APListh_<invoice_ID>_diag.html. Open the file in your browser and review the output.

Note: The HTML version of this test is incompatible NLS Character Set UTF8 for RDBMS version 8.1.7.3 or lower.

Please run the Text version of this test, if you have UTF8 and RDMBS version 8.1.7.3 or lower.
For Text output, run the file APListh.sql in SQL*Plus in the APPS schema.
Follow the prompts and enter the requested information. The test will create an output file in Text
format named APListt_<invoice_id>_<mmddhhmm>_diag.lst. Open the file in a Text editor and review the output.

The test has 3 parameters that allow some control of the appearance of the data and what data is returned.

Parameter 1: Print Null Columns (Y or N) - Default N
This parameter is defaulted to No (N). That is the recommended setting for this parameter.
If set to Y, the test would display all columns in each table, including columns with NULL values.

Parameter 2: Details Layout Portrait (P) or Landscape (L) - Default P (HTML Only)
This parameter is defaulted to P. That is the recommended setting for this parameter.
Setting this option to P, will display all table details in a portrait format.
Setting this option to L, will display all table details in a Landscape format.

Parameter 3: GL Details Y or N - Default N
This parameter is defaulted to N. That is the recommended setting for this parameter.
Setting this option to Y, will display any transaction data transferred to the GL.
WARNING: SETTING THIS OPTION TO Y WILL RESULT IN LONGER RUN TIME FOR THE TEST

Errors in running APList : unable to open file "CoreApiHtmlx.sql"

2012-10-30T22:43:00.003-07:00

Error:
unable to open file "CoreApiHtmlx.sql"
unable to open file "AddOnApiHtml.sql"
begin -- begin1
*
ERROR at line 1:
ORA-06550: line 13, column 1:
PLS-00201: identifier 'SHOW_HEADER' must be declared
ORA-06550: line 13, column 1:
PL/SQL: Statement ignored
ORA-06550: line 29, column 14:
PLS-00201: identifier 'V2T' must be declared
ORA-06550: line 29, column 14:
PL/SQL: Item ignored
ORA-06550: line 30, column 14:
PLS-00201: identifier 'V2T' must be declared
ORA-06550: line 30, column 14:
PL/SQL: Item ignored
ORA-06550: line 54, column 3:
PLS-00201: identifier 'INSERT_HTML' must be declared
ORA-06550: line 54, column 3:
PL/SQL: Statement ignored
ORA-06550: line 58, column 3:
PLS-00201: identifier 'INSERT_HTML' must be declared
ORA-06550: line 58, column 3:
PL/SQL: Statement ignored
ORA-06550: line 61, column 45:
PLS-00201: identifier 'V2T' must be declared
ORA-06550: line 61, column 1:
PL/SQL: Item ignored
ORA-06550: line 86, column 6:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 84, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 107, column 3:
PLS-00201: identifier 'ERRORPRINT' must be declared

Solution:

APlist.sql calls another file - CoreApiHtml.sql that comes with APlist script. Make sure that this file is in the same directory as APList.sql, it contains all the procedures that APList calls.

Oracle RAC 11g Training with ASM

2012-10-23T03:01:00.003-07:00

Oracle RAC Training with ASM

For Oracle RAC Training with ASM Check the following URL:

http://www.youtube.com/watch?v=y9Wgo0XpuUs&feature=colike

FRM-92101: There was a failure in the Forms Server during stratup.

2012-10-19T07:53:00.004-07:00

While trying to run forms forms for the first time hitting error “FRM-92101: There was a failure in the Forms Server during stratup.”

Solution :

1 ) Download openmotif21-2.1.30-11.EL5.i386.rpm from http://oss.oracle.com/projects/compat-oracle/files/Enterprise_Linux/

2 ) Install the rpm in server.

$ rpm -ivh openmotif21-2.1.30-11.EL5.i386.rpm

3 ) Run forms again now it will work fine.

R12 : httpd: error while loading shared libraries: libdb.so.2

2012-10-10T05:38:00.000-07:00

Problem :

After the installation faced issues bringing up the HTTP server.

Processes in Instance: r12test_myhost.myhost.mydomain.com-------------------+--------------------+---------+---------ias-component | process-type | pid | status-------------------+--------------------+---------+---------OC4J | oafm | 11510 | AliveOC4J | forms | 11508 | AliveOC4J | oacore | 11509 | AliveHTTP_Server | HTTP_Server | 0 | NONEASG | ASG | N/A | Down

06/10/12-00:11:48 :: adopmnctl.sh: exiting with status 0

adapcctl.txt showed the error message
06/10/12-00:04:03 :: adapcctl.sh: starting OPMN managed OHS instance
opmnctl: starting opmn managed processes...================================================================================opmn id=myhost.mydomain.com:6205no processes or applications matched this request
06/10/09-00:04:06 :: adapcctl.sh: exiting with status 150
================================================================================

$LOG_HOME/ora/10.1.3/opmn/HTTP_Server~1 was giving the error

--------12/06/10 00:25:49 Start process--------/app/inst/apps/r12test_myhost/ora/10.1.3/Apache/Apache/bin/apachectl startssl: execing httpd/app/apps/tech_st/10.1.3/Apache/Apache/bin/httpd: error while loading shared libraries: libdb.so.2: cannot open shared object file: No such file or directory

Solution:

Download and Install patch "6078836" from MOS:

[root 6078836]# pwd

/u01/sw_home/6078836

[root 6078836]# ls

libdb.so.2 README.txt

[root 6078836]# ls -l /usr/lib/libdb.so.2

ls: /usr/lib/libdb.so.2: No such file or directory

[root 6078836]# cp libdb.so.2 /usr/lib/

[root 6078836]# ls -l /usr/lib/libdb.so.2

-rw-r--r-- 1 root root 5825 Nov 27 11:38 /usr/lib/libdb.so.2

[root 6078836]#

R12 MultiNode Installation with VMWare

2012-10-10T05:24:00.000-07:00

Setup Requirement : Node with atleast 1 GB of Ram and 80GB of HardDisk , OS Dump , R12 Dump and VMWare Software

Download a Copy of VMWare WorkStation from http://www.vmware.com/download/ws/ for Linux
You will need an evaluation key
http://www.vmware.com/vmwarestore/newstore/wkst_eval_login.jsp , make sure you have the key before you start the Installation.

I downloaded an Image of Redhat Linux RHEL4-U4-i386-ES

Linux Node Setup Information:
1. Install OS Image on to the Box
2. Name the host , say r12lab01.r12.com and assign a static IP
3. Make sure you have all the latest RPM's in place. For more info refer R12 OS Pre-requisite Notes.
4. Open a VNC Server as Root
5. Download the VMWare RPM Package "VMware-workstation-5.5.3-34685.i386.rpm"
6. Install the RPM Package
7. Configure VMWare using "http://www.vmware.com/support/ws5/doc/ws_install_linux.html"
8. Start VMware Workstation ( /usr/bin/vmware )
9. Enter your serial number.
10. Create a virtual machine using the New Virtual Machine Wizard.
( ref : http://www.vmware.com/support/ws5/doc/newguest_ws.html )
11. Install a guest operating system in the new virtual machine.
You need the installation media CD-ROM for your guest operating system
12. Name the New Virtual Host as r12lab02.r12.com and assign a static IP
13. Make sure that you can communicate between first and the second node.
14. Power Off the Node and Start Fresh , make sure you have no OS Issues.
15. Keep the VNC Session Active. ( If you loose it , your will not have the new node )

R12 Setup Information:
1. Download Dump of R12 from http://edelivery.oracle.com
2. Stage them on the Hard Disk( I did not include the space requirement of the Dump above)
3. Run a Single Node Installation
4. Make sure that the Installation Completed Normal and all the Service are Up and Running.
5. Perform a Complete Health Check.

Adding a Node to the Existing Setup:
1. Power on the Second Node
2. Mount the R12 Mount Points on the Second Node.
3. cd /home1/aptest/test/apps/apps_st/comn/clone/bin/
4. perl adclonectx.pl addnode contextfile=TEST_r12lab01.xml
Target hostname (virtual or normal) [r12lab02]:
5. Make sure that the above process generate the Secondary Node's Context File
6. Make the Service Enabled that you want to run on the Second Node.
Initially have web service configured and later modify according to your need.
7. Move into AD_TOP/bin and run adconfig.sh , context_file=TEST_r12lab01.xml
8. Make sure that Autoconfig Complete's Normal.
9. Source the Environment File
10. Start the Service on the Seconday Node
11. Navigate to OAM and verify that you have the Second Node.

SYSAUX tablespace is growing like anything

2012-09-02T01:44:00.000-07:00

SYSAUX tablespace is growing like anything
##################################

AWR is identified to be consuming more space.

SQL> select * from v$sysaux_occupants ;

SQL> SELECT occupant_name, occupant_desc, space_usage_kbytes FROM v$sysaux_occupants ORDER BY space_usage_kbytes DESC ;

OCCUPANT_NAME OCCUPANT_DESC SPACE_USAGE_KBYTES

SM/AWR Server Manageability - Automatic Workload Repository 19906624
SM/OPTSTAT Server Manageability - Optimizer Statistics History 191808
EM Enterprise Manager Repository 59648

Solution
#########

1.) See the snapshots interval and retention period time

SQL> select * from DBA_HIST_WR_CONTROL;

Check whether the retention period is set too high. In such case we need to modify the retention period.

If it is set to high value, then consider reducing the retention period to avoid growing of space.

By default, it is set to 7 days.

The new retention time is specified in minutes.

SQL> execute DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention => 4320);

Here 4320 is 3*24*60 ( Convert the days to minutes). For 3 days, it 4320 minutes.

2). Next thing is to check the amount of snapshots have been generated and available.

If this is too high, then try to drop the unwanted snapshots to reclaim the space.

SQL> exec dbms_workload_repository.drop_snapshot_range(low_snap_id,high_snap_id);

low_snap_id : The low snapshot id of snapshots to drop.

high_snap_id : The high snapshot id of snapshots to drop.

3) Then check whether the statistics_level parameter is set to typical.

SQL> show parameter statistics

NAME TYPE VALUE

statistics_level string ALL -----> Should be set TYPICAL

If it is set to ALL, please change it to TYPICAL. Because statistics_level=ALL will gather lot of additional information in AWR repository which would consume more space. Most of the cases, if the statistics_level is set to TYPICAL then the growth would be stopped.

SQL> alter system set statistics_level=typical;

What is Split Brain and Node Eviction in RAC ???

2012-08-13T21:21:00.006-07:00

What is Voting Disk ??

Oracle Clusterware uses the voting disk to determine which instances are members of a cluster. The voting disk must reside on a shared disk. Basically all nodes in the RAC cluster register their heart-beat information on thes voting disks. The number decides the number of active nodes in the RAC cluster. These are also used for checking the availability of instances in RAC and remove the unavailable nodes out of the cluster. It helps in preventing split-brain condition and keeps database information intact. The split brain syndrome and its affects and how it has been managed in oracle is mentioned below.
For high availability, Oracle recommends that you have a minimum of three voting disks. If you configure a single voting disk, then you should use external mirroring to provide redundancy. You can have up to 32 voting disks in your cluster. What I could understand about the odd value of the number of voting disks is that a noe should see maximun number of voting disk to continue to function, so with 2, if it can see only 1, its not the maximum value but a half value of voting disk. I am still trying to search more on this concept.

Split Brain in RAC

In a Oracle RAC environment all the instances/servers communicate with each other using high-speed interconnects on the private network. This private network interface or interconnect are redundant and are only used for inter-instance oracle data block transfers. Now talking about split-brain concept with respect to oracle rac systems, it occurs when the instance members in a RAC fail to ping/connect to each other via this private interconnect, but the servers are all pysically up and running and the database instance on each of these servers is also running. These individual nodes are running fine and can conceptually accept user connections and work independently. So basically due to lack of commincation the instance thinks that the other instance that it is not able to connect is down and it needs to do something about the situation. The problem is if we leave these instance running, the sane block might get read, updated in these individual instances and there would be data integrity issue, as the blocks changed in one instance, will not be locked and could be over-written by another instance. Oracle has efficiently implemented check for the split brain syndrome.

Node Eviction in RAC

In RAC if any node becomes inactive, or if other nodes are unable to ping/connect to a node in the RAC, then the node which first detects that one of the node is not accessible, it will evict that node from the RAC group. e.g. there are 4 nodes in a rac instance, and node 3 becomes unavailable, and node 1 tries to connect to node 3 and finds it not responding, then node 1 will evict node 3 out of the RAC groups and will leave only Node1, Node2 & Node4 in the RAC group to continue functioning.
The split brain concepts can become more complicated in large RAC setups. For example there are 10 RAC nodes in a cluster. And say 4 nodes are not able to communicate with the other 6. So there are 2 groups formed in this 10 node RAC cluster ( one group of 4 nodes and other of 6 nodes). Now the nodes will quickly try to affirm their membership by locking controlfile, then the node that lock the controlfile will try to check the votes of the other nodes. The group with the most number of active nodes gets the preference and the others are evicted. Moreover, I have seen this node eviction issue with only 1 node getting evicted and the rest function fine, so I cannot really testify that if thats how it work by experience, but this is the theory behind it.

When we see that the node is evicted, usually oracle rac will reboot that node and try to do a cluster reconfiguration to include back the evicted node.

You will see oracle error: ORA-29740, when there is a node eviction in RAC. There are many reasons for a node eviction like heart beat not received by the controlfile, unable to communicate with the clusterware etc.