sqlt tool (create sqltxplain plan using sqlt tool)

First Login to the server
Go to directory where sqlt tool is installed
There u will find one directory name sqlt.
Inside sqlt directory you will find directories like RUN,INPUT etc...
Go to INPUT directory
Create one sql file.. e.g. q1.sql
Just paste the Query for which you want the sqltxtplain to be generated and save it in q1.sql
Come back to sqlt directory. sqltNow connect as apps user (sqlplus apps/apps)
Just run the following command.
Command to generate sqltxplan plan for given or provided sql
sql > start run/sqltxplain.sql input/q1.sqlIt will prompt for Enter value for sqlt_method: just give XPLAINThen it will prompt for SQLTXPLAIN password (required)
Password for user SQLTXPLAIN: Give SQLTXPLAIN as password
Then one Zip file will get created.
That is what SQLTXTPLAIN!!!

Create Application users across instances with same responsibilities

Find responsibilities attached to a user
SELECT frt.RESPONSIBILITY_NAME, furg.end_date
FROM
fnd_user_resp_groups furg,
FND_RESPONSIBILITY fr,fnd_responsibility_tl frt,fnd_user fu
WHERE fu.user_name = '&&username'
AND   fu.user_id = furg.user_id
AND   furg.responsibility_id = fr.RESPONSIBILITY_ID
AND   frt.responsibility_id = fr.RESPONSIBILITY_ID
ORDER BY 1;

SIMPLEST WAY TO Create application users with same responsibilties  across instances

Downloading Application User Responsibilities

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct XX_FND_USER_PASSI.ldt FND_USER USER_NAME='DC128877'

Uploading Application user Responsibilites in another instance

FNDLOAD apps/torpedor12 0 Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct XX_FND_USER_PASSI.ldt

Create another Application user with same Responsibilities as the existing application user.

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct XX_FND_USER_PASSI.ldt FND_USER USER_NAME='DC128877'

Uploading Application user Responsibilites to another Applicatioon user in same instance

do vi *.ldt file
Change username to new username
vi XX_FND_USER_PASSI.ldt

In this ldt file change USER_NAME to new user - then upload the ldt file

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct XX_FND_USER_PASSI.ldt

Note : If Application user does not exists it will create it and if it exists it will add the responsibilities its missing.

CUSTOM.pll not picking from Custom_top/resource.

By default CUSTOM.pll is in AU_TOP/resource.

If customisation is there some clients will create CUSTOM_TOP and place it in CUSTOM_TOP/resource directory

solution :

Modify the below files as give in 1 and 2.

1)
login to apps as applmgr user

In R12 edit default.env ($INST_TOP/ora/10.1.2/forms/server/)
Backup the existing default.env file first before modifying it.
change form_path
From FORMS_PATH = '/../../AU_TOP/resource to
FORMS_PATH=/u002/oracle/ERP12XYZ/apps/apps_st/appl/abc_custom/au_g/1.0.0/forms/US:/u002/oracle/ERP12XYZ/apps/apps_st/appl/abc_custom/au_g/1.0.0/resource:/u002/oracle/ERP12XYZ/apps/apps_st/appl/au/12.0.0/resource:/u002/oracle/ERP12XYZ/apps/apps_st/appl/au/12.0.0/resource/stub:/u002/oracle/ERP12XYZ/apps/apps_st/appl/au/forms/US:/u002/oracle/ERP12XYZ/apps/apps_st/appl/abc_custom/abcx/1.0.0/forms/US
comment the existing FORMS_PATH and write the new FORMS_PATH as given above.

**************
# Begin Customization
# APPLFULL="${APPLFULL}abcX INFX IEXX TRNX MIGX"
# export APPLFULL
# Mention CUSTOM_TOP also as given below
CUSTOM_TOP=/u002/oracle/ERP12XYZ/apps/apps_st/appl/abc_custom
abcX_TOP=/u002/oracle/ERP12XYZ/apps/apps_st/appl/abc_custom/abcx/1.0.0
AUG_TOP=/u002/oracle/ERP12XYZ/apps/apps_st/appl/abc_custom/au_g/1.0.0

#END Customization

2)

GO to APPL_TOP backup the existing $TWO_TASK.env file starting with SID_HOSTNAME.env
comment the existing FORMS_PATH

Change FORMS_PATH what you have mentioned in default.env
export FORMS_PATH
bounce opmctl.sh
logout and login back
check echo $FORMS_PATH

Now you will be able to see the new FORMS_PATH

Create Zipped TARBALL

nohup tar cEf - apps | gzip -c > /u806/oracle/ERP12XYZ_051211/apps.tar.gz &
nohup tar cEf - inst | gzip -c > /u806/oracle/ERP12XYZ_051211/inst.tar.gz &

Check :

ps -ef | grep tar

Create Compressed Zipped Tar ball in one step:

nohup tar -czvf /backup/EBSapps_date.tgz EBSapps > EBSappstar.log &

the job will run in background.

Unzip the compressed tar ball

tar -xzvf EBSapps_date.tgz

Find Missing Libraries / Find which libraries progam is using

Error : ld.so.1: item_main: fatal: libclntsh.so.1.0: open failed
$ ldd program_name
$ ldd item_main
ldd item_main
        libclntsh.so.1.0 =>     not found
        libnsl.so.1 =>   /lib/libnsl.so.1
        libsocket.so.1 =>        /lib/libsocket.so.1
        libgen.so.1 =>   /lib/libgen.so.1
        libdl.so.1 =>    /lib/libdl.so.1
        libc.so.1 =>     /lib/libc.so.1
        libaio.so.1 =>   /lib/libaio.so.1
        libm.so.2 =>     /lib/libm.so.2
        libmp.so.2 =>    /lib/libmp.so.2
        libmd.so.1 =>    /lib/libmd.so.1
        libscf.so.1 =>   /lib/libscf.so.1
        libdoor.so.1 =>  /lib/libdoor.so.1
        libuutil.so.1 =>         /lib/libuutil.so.1
        /platform/SUNW,SPARC-Enterprise/lib/libc_psr.so.1
sun1287_applmgr_XYZ$
NOte : If some library is missing it will show you not found as shown above
check LD_LIBRARY_PATH
also find the missing library and place it under correct place.

Then run the above command

check for the libraries in the below path :
check in 10.1.2 Oracle_home/lib

check in /usr/lib->

Create Read Only Apps User

In ASM Instance:

SQL> create tablespace APPSREADONLY
  2  datafile '+DATA/xyz/datafile/' size 500M;
Tablespace created.

Non-ASM Instance:

SQL> create tablespace APPSREADONLY
  2  datafile '/xyz/datafile/APPSREADONLY01.dbf' size 500M;
Tablespace created.
SQL> create user APPSREADONLY identified by APPSREADONLY
  2  DEFAULT TABLESPACE APPSREADONLY
  3  TEMPORARY TABLESPACE temp
  4  QUOTA UNLIMITED ON APPSREADONLY;
User created.
SQL> grant connect to APPSREADONLY;
Grant succeeded.

sql> spool appsreadonly.log
select 'grant select on '|| 'APPS.'||object_name ||' to '||'APPSREADONLY; 'from dba_objects where owner='APPS';
sql> spool off;

Now run this appsreadonly.log
sql> @appsreadonly.log
sql> conn APPSREADONLY/APPSREADONLY
sql> select * from APPS.fnd_languages;
it will display the contents from fnd_languages tables.

adbldxml.pl hangs when retrieving information from database.

Check the adbldxml log file:

Message in logfile:

Retrieving information from database.

Solution:

Login to oracle:

Check the optimizer_mode

show parameter optimizer_mode

optimizer_mode will be all_rows.

Change it to rule:

alter system set optimizer_mode=rule;

run adbldxml.pl scripts

Once it prompts for display

Note: Change optimizer_mode back to all_rows;

alter system optimizer_mode=all_rows;

Now enter the diplay information. hit Enter xml file will generated.

Dislpay information can be obtained from database context file (xmlfile)

grep display contextfilename

Create / Drop / Check Database link or DB link

Two ways to create DB link in 11g Database

1. With TNS entry in tnsnames.ora

2. TNS entry given at the time of db link creation.

SQL> desc DBA_DB_LINKS

 Name                                      Null?                              Type

 ----------------------------------------- -------- ----------------------------

 OWNER                                     NOT NULL      VARCHAR2(30)

 DB_LINK                                   NOT NULL       VARCHAR2(128)

 USERNAME                                                                 VARCHAR2(30)

 HOST                                                                          VARCHAR2(2000)

 CREATED                                   NOT NULL     DATE

***FIND ALL DBLINKS created in database:

select * from dba_db_links;

Create DB link

Syntax:

Create public/private database_link linkname connect to username identified by Password using ‘Service Name’; i

Example :

1st method: Create DBLINK with tns entry made in tnsnames.ora

CREATE PUBLIC DATABASE LINK "TO_SPOT.WORLD" CONNECT TO CFS_SPOT IDENTIFIED BY CFS_Passwd_123 USING 'Spotprof.was.abc.com’;

Now make an entry in tnsnames.ora present in $TNS_ADMIN

SPTPS.WAS.ABC.COM

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = abc.xyz.was.com)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SID = SXXX

      (SERVER = DEDICATED)

    )

  )

2^nd Method:

conn / as sysdba issue this command on sqlprompt

CREATE public DATABASE LINK TO_SPOT CONNECT TO CFS_SPOT IDENTIFIED BY password USING

'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname.domainname.com)(PORT=1521)))

(CONNECT_DATA=(SID = SXXX')));

Check DB link is working or not

conn / as sysdba or conn to sql and try the below one :

syntax : select * from dual@linkname;

Example

select * from dual@TO_SPOT;

DROP DBLINK:

DROP PUBLIC DATABASE LINK "TO_SPOT.WORLD";

truncate /drop all database links

truncate table link$;