Detail
Detailed and ed and simple steps for simple steps for Schema Schema Migration using exp/Migration using exp/imp,pipe,gzimp,pipe,gz between tw
between tw o databaseso databases
Detailed and simple steps for Schema Migration using exp/imp,pipe,gz between two Detailed and simple steps for Schema Migration using exp/imp,pipe,gz between two databases
databases
Start of Migration Start of Migration On the Sou
On the Sou rce Databarce Databasese ================== ==================
SQL> select name,open_mode from v$database; SQL> select name,open_mode from v$database; NAME OPEN_MODE
NAME OPEN_MODE --- --- ---SOURCE
SOURCE READ READ WRITEWRITE
1. SQL> select owner,sum(bytes)/1024/1024 from dba_segments where owner in 1. SQL> select owner,sum(bytes)/1024/1024 from dba_segments where owner in ('SCHEMA1','SCHEMA2','S
('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4','SCHECHEMA3','SCHEMA4','SCHEMA5') MA5') group by group by owner;owner; 2. SQL>
2. SQL> select tablespace_nameselect tablespace_name,sum(bytes)/1024/1024 from dba_segments where owner ,sum(bytes)/1024/1024 from dba_segments where owner inin ('SCHEMA1','SCHEMA2','
('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4','SCSCHEMA3','SCHEMA4','SCHEMA5') HEMA5') group group by by tablespace_name;tablespace_name; 3. SQL> select default_tablespace,username from dba_users where username in 3. SQL> select default_tablespace,username from dba_users where username in ('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4','SCHEMA5');
('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4','SCHEMA5');
4. SQL> select owner,status,object_type,count(*) from dba_objects where owner in 4. SQL> select owner,status,object_type,count(*) from dba_objects where owner in ('SCHEMA1','SCHEMA2','S
('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4','SCHECHEMA3','SCHEMA4','SCHEMA5') MA5') group group by by object_type,owneobject_type,owner,status;r,status;
July (1) July (1) May (1) May (1)
Labels
Labels
Apps DBA - Concurrent Managers (4) Apps DBA - Concurrent Managers (4) Apps DBA Issues (24) Apps DBA Issues (24) Flashback Technology features (6) Flashback Technology features (6) Imporant Metalink Doc IDs (2) Imporant Metalink Doc IDs (2) Important Database Tables (2) Important Database Tables (2) Important I Papers (3) Important I Papers (3) Interview Questions (8) Interview Questions (8) Oracle Apps DBA cloning (1) Oracle Apps DBA cloning (1) Oracle Apps DBA Patching (2) Oracle Apps DBA Patching (2) Oracle Apps R12 (3) Oracle Apps R12 (3) Oracle DBA - Interview Questions (4) Oracle DBA - Interview Questions (4) Oracle DBA - Wait Events (1) Oracle DBA - Wait Events (1) Oracle DBA Issues (42) Oracle DBA Issues (42) Oracle DBA Scripts (14) Oracle DBA Scripts (14) RAC(Real Applications Cluster) (3) RAC(Real Applications Cluster) (3) Unix Important (5) Unix Important (5) Upgradations (4) Upgradations (4)Blog Archive
Blog Archive
ShareShare Report AbuseReport Abuse NNeexxttBBlloogg»» CCrreeaatteeBBlloogg Sign InSign In
T h i s b l o g i s d e s i g n e d f o r t T h i s b l o g i s d e s i g n e d f o r t h e g u yh e g u y s ws w h o n e e d rh o n e e d r e a l t i me a l t i m e a s s i se a s s i st a n c e f o r t a n c e f o r O r a c l e DO r a c l e DB A i sB A i ss u e s a n d O r a c l e As u e s a n d O r a c l e Ap p s D B A i sp p s D B A i ss u e s . Ns u e s . No t o no t o n l y tl y t h a t h a t s o m e l a t e s t u p d a t e s w i l l b e i n c l u d e d . P s o m e l a t e s t u p d a t e s w i l l b e i n c l u d e d . Pl a n n i n g t o p ul a n n i n g t o p u t e v e r y tt e v e r y t h i n g r e l a t e d t o yh i n g r e l a t e d t o y o u t h , wo u t h , w o r k , c a r eo r k , c a r ee r a n d e n t e r te r a n d e n t e r t a i n m e n t a i n m e n t a l sa l so N o t e : Po N o t e : Pl e a sl e a se e D o n t d e p e n d c o m p l e t e l y o n D o n t d e p e n d c o m p l e t e l y o n t h i s b l o g f o r t h i s b l o g f o r f i x e s ,f i x e s ,ww o r k a r o u n d s a n d o r k a r o u n d s a n d s o l u t i o n s . Ts o l u t i o n s . Th e s e ah e s e ar e j ur e j u s t c l u e ss t c l u e s// a d v i c ea d v i c es /s / s u g g e ss u g g e ss i o n ss i o n s. P. Pl e a sl e a se d o g i v e e d o g i v e c o m m c o m m e n t s f o r t he n t s f o r t h e u p d a t e s f o r t h i s . Te u p d a t e s f o r t h i s . Th o s e w i l l b e h i g l y a p p r e c i a t e d .h o s e w i l l b e h i g l y a p p r e c i a t e d .
April (1) April (1) March (1) March (1) October (1) October (1) August (3) August (3) March (2) March (2) February (2) February (2) January (1) January (1) July (3) July (3) May (2) May (2) April (2) April (2) February (3) February (3) January (8) January (8) December (5) December (5) November (11) November (11) October (10) October (10) September (51) September (51)
Oracle DBA Forums
Oracle DBA Forums
Tusc.com Tusc.com TeachMeOracle.com TeachMeOracle.com OraCode.com OraCode.com OracleTuning.com OracleTuning.com OraclePower.com OraclePower.com OracleDB OracleDB OracleBase.com OracleBase.com LazyDBA.com LazyDBA.com IPBHost.com IPBHost.com HotOracle.com HotOracle.com DbaZine.com DbaZine.com 5. SQL> select object_type,count(*) from dba_objects where owner in5. SQL> select object_type,count(*) from dba_objects where owner in ('SCHEMA1','SCHEMA2','S
('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4','SCCHEMA3','SCHEMA4','SCHEMA5') HEMA5') group by group by object_type;object_type; 6. SQL> select count(*),status from
6. SQL> select count(*),status from dba_objects where owner indba_objects where owner in ('SCHEMA1','SCHEMA2'
('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4','SCHE,'SCHEMA3','SCHEMA4','SCHEMA5') MA5') group by group by status;status; 7. SQL> select object_type,count(*),status from dba_objects where owner in 7. SQL> select object_type,count(*),status from dba_objects where owner in ('SCHEMA1','SCHEMA2','
('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4','SCSCHEMA3','SCHEMA4','SCHEMA5') HEMA5') group by group by status,object_type;status,object_type; 8. SQL> select tablespa
8. SQL> select tablespace_name,sum(bytes)/1024/10ce_name,sum(bytes)/1024/1024/1000 from dba_data_fi24/1000 from dba_data_files les wherewhere tablespace_n
tablespace_name ame in('TS1','TS2','Tin('TS1','TS2','TS3','TS4','TS5','TS3','TS4','TS5','TS6','TS7') group S6','TS7') group by by tablespace_ntablespace_name;ame; 9. Export Schemas:
9. Export Schemas: vi
vi exp_SOURCE_schexp_SOURCE_schemas.shemas.sh #!/bin/ksh
#!/bin/ksh export
export NLS_LANG=AMERNLS_LANG=AMERICAN_AMERICA.UTF8ICAN_AMERICA.UTF8 mknod /tmp/exp_pipe1 p
mknod /tmp/exp_pipe1 p
gzip -cNf SOURCE_schemas.dmp.gz & gzip -cNf SOURCE_schemas.dmp.gz & exp
exp system/sysmic32 file=/tmp/exp_pipe1 buffer=2097152 system/sysmic32 file=/tmp/exp_pipe1 buffer=2097152 log=exp_SOURlog=exp_SOURCE_schemas.logCE_schemas.log statistics=none
statistics=none owner=SCHEMA1,SCHEMowner=SCHEMA1,SCHEMA2,SCHEMA3,SCHEMA4,SCHEMA2,SCHEMA3,SCHEMA4,SCHEMA5A5 rm -f
rm -f /tmp/exp_pip/tmp/exp_pipe1e1 unset NLS_LANG unset NLS_LANG
10. nohup ./exp_SOURCE_schemas.sh >
10. nohup ./exp_SOURCE_schemas.sh > exp_SOURCE_schexp_SOURCE_schemas.log 2>&1emas.log 2>&1 11. Copy the SOURCE_schemas.dmp.gz to Target
11. Copy the SOURCE_schemas.dmp.gz to Target Database server.Database server. scp -p
scp -p SOURCE_schemas.dmpSOURCE_schemas.dmp.gz .gz ganesh@target_server:/organesh@target_server:/oracle/exportacle/export
On the Target Database On the Target Database ================== ==================
SQL> select name,open_mode from v$database; SQL> select name,open_mode from v$database; NAME OPEN_MODE
NAME OPEN_MODE --- --- ---TARGET
April (1) April (1) March (1) March (1) October (1) October (1) August (3) August (3) March (2) March (2) February (2) February (2) January (1) January (1) July (3) July (3) May (2) May (2) April (2) April (2) February (3) February (3) January (8) January (8) December (5) December (5) November (11) November (11) October (10) October (10) September (51) September (51)
Oracle DBA Forums
Oracle DBA Forums
Tusc.com Tusc.com TeachMeOracle.com TeachMeOracle.com OraCode.com OraCode.com OracleTuning.com OracleTuning.com OraclePower.com OraclePower.com OracleDB OracleDB OracleBase.com OracleBase.com LazyDBA.com LazyDBA.com IPBHost.com IPBHost.com HotOracle.com HotOracle.com DbaZine.com DbaZine.com 5. SQL> select object_type,count(*) from dba_objects where owner in5. SQL> select object_type,count(*) from dba_objects where owner in ('SCHEMA1','SCHEMA2','S
('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4','SCCHEMA3','SCHEMA4','SCHEMA5') HEMA5') group by group by object_type;object_type; 6. SQL> select count(*),status from
6. SQL> select count(*),status from dba_objects where owner indba_objects where owner in ('SCHEMA1','SCHEMA2'
('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4','SCHE,'SCHEMA3','SCHEMA4','SCHEMA5') MA5') group by group by status;status; 7. SQL> select object_type,count(*),status from dba_objects where owner in 7. SQL> select object_type,count(*),status from dba_objects where owner in ('SCHEMA1','SCHEMA2','
('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4','SCSCHEMA3','SCHEMA4','SCHEMA5') HEMA5') group by group by status,object_type;status,object_type; 8. SQL> select tablespa
8. SQL> select tablespace_name,sum(bytes)/1024/10ce_name,sum(bytes)/1024/1024/1000 from dba_data_fi24/1000 from dba_data_files les wherewhere tablespace_n
tablespace_name ame in('TS1','TS2','Tin('TS1','TS2','TS3','TS4','TS5','TS3','TS4','TS5','TS6','TS7') group S6','TS7') group by by tablespace_ntablespace_name;ame; 9. Export Schemas:
9. Export Schemas: vi
vi exp_SOURCE_schexp_SOURCE_schemas.shemas.sh #!/bin/ksh
#!/bin/ksh export
export NLS_LANG=AMERNLS_LANG=AMERICAN_AMERICA.UTF8ICAN_AMERICA.UTF8 mknod /tmp/exp_pipe1 p
mknod /tmp/exp_pipe1 p
gzip -cNf SOURCE_schemas.dmp.gz & gzip -cNf SOURCE_schemas.dmp.gz & exp
exp system/sysmic32 file=/tmp/exp_pipe1 buffer=2097152 system/sysmic32 file=/tmp/exp_pipe1 buffer=2097152 log=exp_SOURlog=exp_SOURCE_schemas.logCE_schemas.log statistics=none
statistics=none owner=SCHEMA1,SCHEMowner=SCHEMA1,SCHEMA2,SCHEMA3,SCHEMA4,SCHEMA2,SCHEMA3,SCHEMA4,SCHEMA5A5 rm -f
rm -f /tmp/exp_pip/tmp/exp_pipe1e1 unset NLS_LANG unset NLS_LANG
10. nohup ./exp_SOURCE_schemas.sh >
10. nohup ./exp_SOURCE_schemas.sh > exp_SOURCE_schexp_SOURCE_schemas.log 2>&1emas.log 2>&1 11. Copy the SOURCE_schemas.dmp.gz to Target
11. Copy the SOURCE_schemas.dmp.gz to Target Database server.Database server. scp -p
scp -p SOURCE_schemas.dmpSOURCE_schemas.dmp.gz .gz ganesh@target_server:/organesh@target_server:/oracle/exportacle/export
On the Target Database On the Target Database ================== ==================
SQL> select name,open_mode from v$database; SQL> select name,open_mode from v$database; NAME OPEN_MODE
NAME OPEN_MODE --- --- ---TARGET
DBAToolz.com DBAToolz.com DBASupport DBASupport DBAPool.com DBAPool.com DBAClick.com DBAClick.com DatabaseAnswers.com DatabaseAnswers.com CreateForum.com CreateForum.com CertCities.com CertCities.com
Oracle Corpora
Oracle Corporation Link
tion Link s
s
Oracle University Learning Oracle University Learning Metalink MetalinkEssential Software Chat
Essential Software Chat
Messengers
Messengers
Skype Messenger Chat Skype Messenger Chat MSN Messenger Chat MSN Messenger Chat AOL Messenger Chat AOL Messenger Chat Rediffbol Messgenger Chat Rediffbol Messgenger Chat Yahoo Beta Messenger Chat Yahoo Beta Messenger Chat Google Chat Messenger Google Chat MessengerOracle DB Arch.
Oracle DB Arch.
1. Create tablespaces as per the output from Step 7 you got on1. Create tablespaces as per the output from Step 7 you got on Source databaseSource database 2. Create users on Target database
2. Create users on Target database Run the following script on the
Run the following script on the source database and execute the resultant script on source database and execute the resultant script on thethe Target database Target database set linesize 300 set linesize 300 set pagesize 300 set pagesize 300 spool create_users.sql spool create_users.sql
select 'create user '||username||' identified by
select 'create user '||username||' identified by values '''||password||''' default tablespacevalues '''||password||''' default tablespace '||default_t
'||default_tablespace||' temporary tablespace temp ablespace||' temporary tablespace temp profile '||profile||';' from profile '||profile||';' from dba_usersdba_users where username
where username in in ('SCHEMA1','SCHEMA2','('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4','SCSCHEMA3','SCHEMA4','SCHEMA5');HEMA5'); spool off
spool off
3. Grant Quotas on Target database 3. Grant Quotas on Target database
Run the following script on the
Run the following script on the source database and execute the resultant script on source database and execute the resultant script on thethe Target database
Target database spool
spool create_tablecreate_tablespace_quota.sqlspace_quota.sql select 'alter user '||username||'
select 'alter user '||username||' quota unlimited on '||tablespace_name||';' fromquota unlimited on '||tablespace_name||';' from dba_ts_quotas where MAX_BYTES='-1' and username in
dba_ts_quotas where MAX_BYTES='-1' and username in ('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4','SCHEMA5'); ('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4','SCHEMA5');
select 'alter user
select 'alter user '||username||' quota '||max_bytes||' on '||tablespace_name||';' from'||username||' quota '||max_bytes||' on '||tablespace_name||';' from dba_ts_quotas where MAX_BYTES!='-1' and username in
dba_ts_quotas where MAX_BYTES!='-1' and username in ('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4','SCHEMA5'); ('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4','SCHEMA5');
spool off spool off
4. Grant Roles on Target database 4. Grant Roles on Target database Run the following script on the
Run the following script on the source database and execute the resultant script on source database and execute the resultant script on thethe Target database
Target database spool
spool create_grant_rolcreate_grant_roles.sqles.sql
select 'grant '||GRANTED_ROLE||' to '||GRANTEE||';' from
select 'grant '||GRANTED_ROLE||' to '||GRANTEE||';' from dba_role_privs wheredba_role_privs where ADMIN_OPTION='NO' and grantee in
ADMIN_OPTION='NO' and grantee in
('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4','SCHEMA5'); ('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4','SCHEMA5');
select 'grant '||GRANTED_ROLE||' to '||GRANTEE||' with admin option;' from select 'grant '||GRANTED_ROLE||' to '||GRANTEE||' with admin option;' from dba_role_privs where ADMIN_OPTION='YES' and grantee in
dba_role_privs where ADMIN_OPTION='YES' and grantee in ('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4','SCHEMA5'); ('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4','SCHEMA5');
spool off spool off
5. Grant System privs on Target database 5. Grant System privs on Target database
spool create_sys_privs.sql spool create_sys_privs.sql
Oracle E-Business Suite
Oracle E-Business Suite
R
R1
12
2
Oracle 11g
Oracle 11g
Run the following script on theRun the following script on the source database and execute the resultant script on source database and execute the resultant script on thethe Target database
Target database
select 'grant '||PRIVILEGE||' to '||GRANTEE||';' from dba_sys_privs where select 'grant '||PRIVILEGE||' to '||GRANTEE||';' from dba_sys_privs where ADMIN_OPTION='NO' and grantee
ADMIN_OPTION='NO' and grantee in ('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4','Sin ('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4','SCHEMA5');CHEMA5'); select 'grant '||PRIVIL
select 'grant '||PRIVILEGE||' to '||GRANTEE||' EGE||' to '||GRANTEE||' with admin option;' from dbawith admin option;' from dba_sys_privs_sys_privs where ADMIN_OPTION='YES' and grantee in
where ADMIN_OPTION='YES' and grantee in
('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4','SCHEMA5'); ('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4','SCHEMA5'); spool off spool off 6. Import Schemas 6. Import Schemas vi imp_TARGET_schemas.sh vi imp_TARGET_schemas.sh #!/bin/ksh #!/bin/ksh export
export NLS_LANG=AMERNLS_LANG=AMERICAN_AMERICA.AL32UTF8ICAN_AMERICA.AL32UTF8 mknod /tmp/imp_pipe1 p
mknod /tmp/imp_pipe1 p
gunzip -c SOURCE_schemas.dmp.gz >/tmp/imp_pipe1 & gunzip -c SOURCE_schemas.dmp.gz >/tmp/imp_pipe1 & imp system/manage
imp system/manager99 file=/tr99 file=/tmp/imp_pipe1 mp/imp_pipe1 buffer=209715buffer=2097152020 log=imp_TARGET
log=imp_TARGET_schemas.log _schemas.log full=yfull=y rm -f /tmp/imp_pipe1
rm -f /tmp/imp_pipe1 unset NLS_LANG unset NLS_LANG 7. Verify the logs 7. Verify the logs 8. Do step 1 to
8. Do step 1 to step 7 that you have step 7 that you have done on the source database.done on the source database. 9.
9. Grant Table Grant Table privs on privs on the Targetthe Target Run the following script on the source
Run the following script on the source database and execute the resultant script on thedatabase and execute the resultant script on the Target database
Target database spool
spool create_tab_pricreate_tab_privs.sqlvs.sql
select 'grant '||privilege||' on '||owner||'.'||table_name||' to '||grantee||';' from select 'grant '||privilege||' on '||owner||'.'||table_name||' to '||grantee||';' from dba_tab_privs where GRANTABLE='NO' and grantee in
dba_tab_privs where GRANTABLE='NO' and grantee in ('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4','SCHEMA5'); ('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4','SCHEMA5');
select 'grant '||privilege||' on '||owner||'.'||table_name||' to '||grantee||' with grant select 'grant '||privilege||' on '||owner||'.'||table_name||' to '||grantee||' with grant option;' from dba_tab_privs where GRANTABLE='YES' and grantee in
option;' from dba_tab_privs where GRANTABLE='YES' and grantee in ('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4','SCHEMA5');
('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4','SCHEMA5'); spool off
spool off
9. EXEC DBMS_UTILITY.COMPILE_S
Add to Plusmo
Oracle Apps DBA Blogs
Oracle Apps documentation OnlineAppsDBA NavdeepSainiBlog BlackGeekBlog AtulKumarsBlog SenthilRajendranBlog OracleMagicByFadiHasweh StevenChanBlog imported10. exec dbms_stats.gather_schema_stats('SCHEMA1', cascade=>TRUE); -> Do for all schemas that you have imported
End of Migration
0 comments Links to this post
W here are the log files located in R12?
Where are the log files located in R12?
For DBA’s who mostly worked on 11i environments, finding the log files (Concurrent
manager log, apache log etc.,) on a R12 environment might pose a threat initially, because these log files no longer reside in their old location ie., $APPLCSF/$APPLLOG or
$APACHE_TOP/Apache/logs.
In R12, the log files are located in $LOG_HOME (which translates to $INST_TOP/logs) Concurrent Reqeust related logs
$LOG_HOME/appl/conc -> location for concurrent requests log and out files $LOG_HOME/appl/admin -> location for mid tier startup scripts log files Apache Logs(10.1.3 Oracle Home which is equivalent to iAS Oracle Home) $LOG_HOME/ora/10.1.3/Apache -> Location for Apache Error and Access log files $LOG_HOME/ora/10.1.3/j2ee -> location for j2ee related log files
$LOG_HOME/ora/10.1.3/opmn -> location for opmn related log files
Forms & Reports related logs(10.1.2 Oracle home which is equivalent to 806 Oracle Home) $LOG_HOME/ora/10.1.2/forms
$LOG_HOME/ora/10.1.2/reports
Related metalink notes to enable additional debugging
419839.1 – How to enable Apache, OC4J and OPMN logging in Oracle Applications R12 422419.1 – R12 – How To Enable and Collect Debug for HTTP, OC4J and OPMN
Source : http://oraclepitstop.wordpress.com 0 comments Links to this post
How to enable Apache and Jser v Debugging in Oracle Apps 11i?
PURPOSE:-If a user experiences an error, request a screen shot of the error and any further error details that they can report.If the error is reproducible, enable the following middle
Posts
Oracle E-Business Suite
R12
Online Debit Card Banking
State Bank Of India Online ICICI Bank Online HDFC Bank Online Deutsche Bank Online Andhra Bank Online ABN Amro Bank OnlinePersonal I nterests
HotGreetings tier debug settings and collect diagnostic information appropriately and upload to supportfor troubleshooting.
Looking for Jserv.log at $IAS_ORACLE_HOME/Apache/Jserv/logs then enable… For Temporary purpose manually editing configuration files.
1) Shut Down Apache Server
2) Backup the existing files in the following files: $IAS_ORACLE_HOME/Apache/Apache/logs $IAS_ORACLE_HOME/Apache/Jserv/logs $IAS_ORACLE_HOME/Apache/Jserv/logs/jvm 3) Enable debug logging for Apache
s_apache_loglevel=debug (In Context File) Or
loglevel debug (In $IAS_ORACLE_HOME/Apache/Apache/conf/httpd.conf) 4) Enable debug logging for mod_jserv
s_apjservloglevel=debug (In Context File) Or
ApJServLogLevel debug (In $IAS_ORACLE_HOME/Apache/Jserv/etc/jserv.conf) 5) Enable debug logging for Jserv
s_oacorelog=true (In Context File) Or
log=true log.channel=true log.channel.info=true log.channel.debug=true (In $IAS_ORACLE_HOME/Apache/Jserv/etc/jserv.properties)
6) Enable debug logging for Forms Listener Servlet JVM. (Optional) s_oaformslog=true (In Context File)
Or
log=true log.channel=true log.channel.info=true log.channel.debug=true (In $IAS_ORACLE_HOME/Apache/Jserv/etc/forms.properties)
7) Restart Apache
(If Context File had been edited in above steps then run autoconfig to update the configuration files.) 8) Replicate the Problem.
9) Review the following files from the $IAS_ORACLE_HOME/Apache/Jserv/logs directory a) mod_jserv.log
b) jserv.log
c) All files from the $IAS_ORACLE_HOME/Apache/Jserv/logs/jvm directory These will have filenames of the form jvm.jvm.stdoutstderr For example,
OACoreGroup.0.stderr
d) $COMMON_TOP/rgf/instance_hostname/javacache.log Source: http://leenus2b.wordpress.com
BodyScapes
powered by
Visite d Site..? Fou nd
Good..?
Visited Site..? Found Good..? Completly Yes Completely No Partially Yes Votes so far: 7 Poll closed 4 (57%) 2 (28%) 1 (14%) For R1 2: How To : How to enable Apache, OC4J and OPMN logging in Oracle Applications
R12 use Doc id [ID 419839.1] 0 comments Links to this post
Form s Servlet mode to Socket mode conver sion on Release 12 Apps
How to convert Forms server to Socket mode from Servlet mode in Oracle Apps R12 Run the following command to enable Forms Socket Mode on Forms/web nodes (Place correct context file name and port value)
$FND_TOP/bin/txkrun.pl -script=ChangeFormsMode -contextfile=$CONTEXT_FILE -mode=socket -port=9030 -runautoconfig=No
Where port = forms port and contextfile=your environment contextfile To Start/Stop/check Status of forms serv ers use following scripts cd $ADMIN_SCRIPTS_HOME
./adformsrvctl.sh status/stop/start
0 comments Links to this post
MSG-00102: Err or Mess age :ORA-20100: File o000 0071.tmp crea tion for FND_FILE failed.
Troubleshooting Details
If a PL/SQL Concurrent Program can't write to an external file, you will receive an error message similar to:
MSG-00102: Error Message :ORA-20100: File o0000071.tmp creation for FND_FILE failed. You will find more information on the cause of the error in request log.
ORA-06512: at "APPS.FND_FILE", line 378 ORA-06512: at "APPS.FND_FILE", line 473
powered by
Slideshow
Mobile??
ORA-06512: at "APPS.AP_TRIAL_BALANCE_PKG", line 192REP-1419: 'beforereport': PL/SQL program aborted.
NOTE: Applications also produces temporary PL/SQL output files used in concurrent processing. These files are written to a location on the database server node specified by the APPLPTMP environment setting. The APPLPTMP directory must be the same directory as specified by the utl_file_dir parameter in your database initialization file.
.
Rapid Install sets both APPLPTMP and the utl_file_dir parameter to the same default directory. As the temporary files placed in this directory may contain context sensitive information, it should be a secure directory on the database server node with read and write access for the database server owner. In a multi-node system, the directory defined by APPLPTMP does not need to exist on the application tier servers. During an upgrade with AutoUpgrade, you must provide the utl_file_dir parameter value for the APPLPTMP
environment setting.
To isolate where the problem is, verify the following:
1) Make sure that the name of the file is valid (the file name should not include characters like "^")
2) Make sure that APPLPTMP is set to a valid directory and that BOTH the applmgr user and the database user have read and write permissions on that directory (normally, it can be set to the same directory as APPLTMP)
3) Make sure that the file does not exit on the directory pointed by APPLPTMP
4) Make sure the directory pointed by APPLPTMP is the first entry on the utl_file_dir. Also, verify that all the entries on the utl_file_dir are valid and that the applmgr has read/write permissions.
If using an spfile, verify the proper syntax to set utl_file_dir:
Eg: ALTER SYSTEM SET UTL_FILE_DIR='directory1','directory2' scope=spfile;
5) If still having problems, check if you can write a file directly using FND_FILE, which is the package used by the Application. From sqlplus, connected as the apps user, run:
Wanna own??.
Not Eye??
How is the Shoe??
Like it? This should dump a file on APPLPTMP.
If this test works, it would indicate that FND_FILE is ok and the problem is possibly with the Application.
You may want to leave only one entry on utl_file_dir for this test.
6) If still having problems, check if you can write a file using UTL_FILE, which is used by FND_FILE.
Run the PL/SQL below, changing to the first entry on utl_file_dir (you may want to leave just one entry on utl_file_dir for this test).
set serveroutput on DECLARE
file_location VARCHAR2(256) := '';
file_name VARCHAR2(256) := 'utlfile1.lst'; file_text VARCHAR2(256) := 'THIS IS A TEST'; file_id UTL_FILE.file_type;
BEGIN
file_id := UTL_FILE.fopen(file_Location, file_name, 'W'); UTL_FILE.put_line(file_id, file_text);
UTL_FILE.fclose(file_id); EXCEPTION
WHEN UTL_FILE.INVALID_PATH
THEN dbms_output.put_line('Invalid path ' || SQLERRM); WHEN OTHERS
THEN dbms_output.put_line('Others '|| SQLCODE || ' ' || SQLERRM); END;
/
This program should dump a file on the requested directory. If the test fails, the problem is probably on the Database side.
If it works, the problem is probably on FND_FILE. In this scenario, check the versions of AFCPPIOS.pls and AFCPPIOB.pls.
Source :Troubleshooting ORA-20100 on Concurrent Processing [ID 261693.1] (suppot.oracle.com)
Happy Ness.
You want to?
Digg
Top News My News Settings Obama: Bush put us in a hole
566 Comments
585
Federal Court Says You Can Record the Cops
51 Comments
481
Valve: DRM is Backwards and Piracy is Just Not an Issue for Us
86 Comments
420
How Bill O'Reilly Tried to Get His Wife's Boyfriend Investigated By the Cops
68 Comments
367
Mermaid arrested out of water: Daryl Hannah handcuffed, arrested at White
288
How to drop and r ecreate TEMP Tablespace in Or acle 9i/10g/11g
How to drop and recreate TEMP Tablespace in Oracle 9i/10g/11g 1. Create Temporary Tablespace Temp
CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE ‘/u01/app/oradata/temp01′ SIZE 2000M,
‘/u01/app/oradata/temp02′ SIZE 2000M';
2. Move Default Database temp tablespace
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2; 3. Make sure No sessions are using your Old Temp tablespace
a. Find Session Number from V$SORT_USAGE:
SELECT USERNAME, SESSION_NUM, SESSION_ADDR FROM V$SORT_USAGE; b. Find Session ID from V$SESSION:
If the resultset contains any tows then your next step will be to find the SID from the V$SESSION view. You can find session id by using SESSION_NUM or SESSION_ADDR from previous resultset.
SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SERIAL#=SESSION_NUM; OR
SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SADDR=SESSION_ADDR; c. Kill Session:
Now kill the session with IMMEDIATE.
ALTER SYSTEM KILL 'SID,SERIAL#' IMMEDIATE; 4. Drop temp tablespace
DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES; 5. Recreate Tablespace Temp
CREATE TEMPORARY TABLESPACE TEMP TEMPFILE /u01/app/temp/temp01′ SIZE 2000M;
6 Move Tablespace Temp, back to new temp tablespace ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp; 7. Drop temporary for tablespace temp
DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;
No need to do shutdown when drop temp tablespace and the recreate it. If something happen with temp tablespaces e.g. : crash, corrupt, etc. Oracle database will ignore the error, but DML (insert,update,delete) and SELECT Query will suffer.
0 comments Links to this post
How to drop and r ecreate UNDO Tablespace in Or acle 9i/10g/11g
How to drop and recreate UNDO Tablespace in Oracle 9i/10g/11g? Solution:
1. Determine the size of your undo tablespace
SQL> select sum(bytes)/1024/1024/1024 from dba_data_files where tablespace_name='UNDOTBS1';
SUM(BYTES)/1024/1024/1024
---12.09375
2. Create a new undo tablespace of the same size (larger or smaller) depending on your database requirements.
SQL> create undo tablespace UNDOTBS2 datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA \ORCL2\UNDOTBS02.DBF' size 400M;
Tablespace created.
3. Edit your init.ora file and change the parameter "undo_tablespace=UNDOTBS2" so it points to the newly created tablespace.
Change undo_management=MANUAL
Setting undo_management now ensures the old rollback segments can be taken offline and avoids editing the pfile and restarting the instance again in Step 7.
4. Ar range a time when the database can be shutdown cleanly and perform a shutdown immediate.
5. Startup the database
6. Confirm the new tablespace is in use: SQL> show parameter undo_tablespace
NAME TYPE VALUE
--- --- ---undo_tablespace string UNDOTBS2
7. Check the status of the undo segments and determine if all the segments in the old undo tablespace are offline. The segments in the new tablespace may also show offline.
SQL>select owner, segment_name, tablespace_name, status from dba_rollback_segs order by 3;
OWNER SEGMENT_NAME TABLESPACE_NAME STATUS --- --- ---
---PUBLIC _SYSSMU3$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU2$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU19$ UNDOTBS2 OFFLINE
....etc.
If the old segments are online, then they must be taken offline: SQL>alter rollback segment "_SYSSMU3$" offline;
SQL>alter rollback segment "_SYSSMU2$" offline;
This should be executed for all online rollback segments in the old tablespace.
8. Provided all the segments in the old undo tablespace are offline, you can now drop the old undo tablespace:
SQL>drop tablespace UNDOTBS1 including contents and datafiles; Tablespace dropped.
KudoSurf Me! 9. Edit your init.ora file do the following changes.
undo_management='AUTO' undo_tablespace='UNDOTBS2'
10. Shutdown the database (shutdown immediate) and restart it. 11. Monitor the alert log simultaneously during all steps.
Reference Metalink Note: [ID 431652.1] 0 comments Links to this post
"Erro r Occurr ed W hile Attempting to Establish an Applications File Server Connection"
Error:
" An error occurred while attempting to establish an Applications F ile Server
connection. There may be a network configuration problem, or the TNS listener may not be running on "
Solutions to Check
1. Is this a Multinode Install or a Single Node Install.
- If this is a Multinode Install, you will need access to the Middle Tier and the Backend Tier (Concurrent Processor) 8.0.6 ORACLE_HOME/network/admin/tnsnames.ora files
- If this is a Single Node Install, you need access to the Concurrent Processor 8.0.6 ORACLE_HOME/network/admin/tnsnames.ora file
2. Run a report, is there actually a Report Output and Log file being produced at the Operating System level?
- Check the $APPLCSF/out and the $APPLCSF/log directories for the Request_ID out and log file
- If there are no log and output file being produces there Check the permissions on those directories (APPLMGR should have write)
- If $APPLCSF is not set, check the $FND_TOP/log and out for output and logs
3. Confirm that there are 2 listener processes actively running for the Instance that you cannot view reports on -- 1 from 8.0.6 and from 8.1.6 directory structures
- type the following: ps -ef | grep tnslsnr
- If there is only 1 listener running from the 8.1.6 directory structure That is the Database Listener
The FNDFS listener needs to be started separately from the Database Listener - To start the FNDFS Listener:
a. Log the Operating System Level as the APPLMGR account user
b. Set the environment for the $APPL_TOP & 8.0.6 $ORACLE_HOME (APPSORA.env) c. Go to the $OAD_TOP/admin/scripts (Common_top area)
d. Run the following script: adalnctl.sh start APPS_SID
(you can confirm the FNDFS Listener name from viewing the 8.0.6 ORACLE_HOME/network /admin/listener.ora file)
4. Check the actual machine name for the Concurrent Processor Server - type the following:
uname -n
5. Go to the Applications and check the CONCURRENT > MANAGER > ADMINISTER screen in the System Administrator Responsibility The INTERNAL MANAGER NODE NAME; is it the same as what was returned from question 4?
- If it is the same then continue with next step - If it is not the same,
Shutdown Concurrent Managers and update the FND table: a. Go to the $OAD_TOP/admin/scripts
b. adcmctl.sh apps/apps stop c. Connect to SQL*PLUS apps/apps
d. SQL > select target_node from FND_CONCURRENT_QUEUES;
---This may return the name of an old machine
e. SQL > update FND_CONCURRENT_QUEUES set target_node='' where target_node=''; f. SQL > commit;
g. SQL > exit
h. adcmctl.sh apps/apps start
Is the hostname value the same as is in questions 4?
- The Rapid Install creates 2 or sometimes 3 FNDFS entries in the TNSNAMES.ora a. 1 with the FNDFS_
b. 1 with the FNDFS_ c. 1 with the FNDFS_
- The one that is the correct entry is FNDFS_ (hostname being what is returned from a "uname -n")
- If this file does not contain the correct Hostname value Make a backup of it and edit it to change the FNDFS entry.
7. Check the Network Connection for the FNDFS entry that was defined by the Rapid Install. - At the Operating System Level
a. tnsping FNDFS_
- This should return the Hostname name and Port information identical to the FNDFS entry in the TNSNAMES.ora file
8. Check the Following PROFILE > SYSTEM Options in The System Administrator Responsibility
CONCURRENT: Report Access Level (this must be set to USER or RESPONSIBILITY) RRA: Service Prefix (this should be BLANK)
RRA: Enabled (this must be set to YES)
VIEWER: Text (this should be BLANK to view with the default text viewer) - Viewer: Text can Be set to "browser" to view with Netscape or IE
9. This step should be performed in a case where there might have been patches applied and possibly the FNDFS executable was not Relinked, or it may be missing from the file system or corrupted.
- Shutdown the FNDFS Listener
a. $OAD_TOP/admin/scripts/adalnctl.sh stop APPS_SID
- Rename or Move the Current FNDFS executable before relinking a. $FND_TOP/bin
mv FNDFS FNDFS.bak b. As APPLMGR account user adrelink.sh force=y "fnd FNDFS" - Start the FNDFS Listener
a. $OAD_TOP/admin/scripts/adalnctl.sh start APPS_SID Metalink Note :ID 117012.1
Backup verification Statemen ts/Steps
sqlplus "/as sysdba" startup mount;
prompt The following should return zero rows select count(*) from v$recover_file;
select count(*) from v$recovery_log; select count(*) from v$recovery_status; select count(*) from v$recovery_file_status;
select name,status from v$datafile where (name like '%MISS%' or status not in ('ONLINE', 'SYSTEM'))
/
select count(*) from v$backup where status != 'NOT ACTIVE' ; col checkpoint_change# format 999999999999999
prompt The following should return one distinct number Select distinct checkpoint_change# from v$datafile ;
select distinct to_char(CHECKPOINT_TIME,'DD-MON-YYYY HH24:MI:SS') from v$datafile_header;
prompt This should return "0" and "8192" as output select distinct fhsta from x$kcvfh;
1 comments Links to this post
Oracle Inventory Creationg/Attach/Dettach/Clone/Relink Code tree
1. Point the inv entory to correct location
Make sure the file ./etc/oraInst.loc., ./var/opt/oracle/oraInst.loc. &
inventory_loc=/oracle/product/oraInventory inst_group=dba
2. Clone the code tree + cd $ORACLE_HOME/oui/bin
+ ./runInstaller -silent -clone -invPtrLoc $ORACLE_HOME/oraInst.loc -ignorePreReq ORACLE_HOME=$ORACLE_HOME ORACLE_HOME_NAME=Ora10204_TEST
ORACLE_HOST_NAME=test-01
!!! Please wait until the command is successful. Do not interrupt. !!!
Please check for .cloning is successful. message and will display message to run .root.sh.. Sometime it may wait for you see this message. Press .enter. to get the .command prompt..
Run the following ,
+ cp /oracle/product/10.2.0.4//root.sh /oracle/product/10.2.0.4 +sudo /oracle/product/10.2.0.4//root.sh
Accept the default options for the prompts and location of the files. 3. Detach the code tree from oracle central inventory
+ cd $ORACLE_HOME/oui/bin
+./runInstaller -silent -detachHome -local -noClusterEnabled -invPtrLoc $ORACLE_HOME/oraInst.loc -ignorePreReq ORACLE_HOME=$ORACLE_HOME ORACLE_HOME_NAME=Ora10204_TEST
4. Attach the code tree to oracle central inventory . This will add correct host to the inventory
+ cd $ORACLE_HOME/oui/bin
+./runInstaller -silent -attachHome -local -noClusterEnabled -invPtrLoc $ORACLE_HOME/oraInst.loc -ignorePreReq ORACLE_HOME=$ORACLE_HOME ORACLE_HOME_NAME=Ora10204_TEST CLUSTER_NODES=test-01
find . -type l -exec ls -l {} \; | grep -i [oldsid] | awk '{print "rm -f "$9";ln -s "$11" "$9}' execute the above command to check any softlinks are pointing to the old env. If y es then edit the output of the above command and run them.
Then,
oraenv ( enter SID at the prompt ). Check the above variables are set. Make sure correct values are set for ORACLE_HOME, PATH & LD_LIBRARY_PATH Make sure .SHLIB_PATH. variable is NOT set.
+ cd $ORACLE_HOME/bin + relink all
1 comments Links to this post
FAQs - How do w e know ho w m any users are connected to Oracle Applications.
FAQs - How do w e know h ow many user s are connected to Oracle Applications.
1. Enable Profile Option "Sign-On Audit" at "Form" level. 2. Run "Purge Signon Audit" request.
3. Security:Users -> Monitor" option
4 or with the below sql query mentioned below.
select distinct fu.user_name User_Name,fr.RESPONSIBILITY_KEY Responsibility from fnd_user fu, fnd_responsibility fr, icx_sessions ic
where fu.user_id = ic.user_id AND
fr.responsibility_id = ic.responsibility_id AND ic.disabled_flag='N' AND
ic.responsibility_id is not null AND ic.last_connect like sysdate;
5. Can use this SQL statement to count concurrent_users in Oracle apps: select count(distinct d.user_name) from apps.fnd_logins a,
v$session b, v$process c, apps.fnd_user d where b.paddr = c.addr
and a.pid=c.pid and a.spid = b.process and d.user_id = a.user_id
0 comments Links to this post
DMT v/s LMT
Difference between Dictionary managed tablespace (DMT) and Locally managed tablespace (LMT)
Introduction:
The CREATE TABLESPACE command has a new clause introduced in Oracle8i, the "extent management clause", that specifies how the extents of the tablespace are managed. This clause uses one of the following parameters:
- DICTIONARY:
Specifies that the tablespace is managed using dictionary tables. This is the default in Oracle8i.
- LOCAL:
Specifies that tablespace is locally managed. This is the default in . Exception for the SYSTEM tablespace
Locally Managed Tablespaces:
A tablespace that manages its own extents maintains a bitmap in each datafile to keep track of the free or used status of blocks in that datafile. Each bit in the bitmap
corresponds to a group of blocks. When an extent is allocated or freed for reuse, Oracle changes the bitmap values to show the new status of the blocks. These changes do not generate rollback information because they do not update tables (like sys.uet$, sys.fet$) in the data dictionary (except for special cases such as tablespace quota information).
When you create a locally managed tablespace, header bitmaps are created for each datafile. If more datafiles are added, new header bitmaps are created for each added file. Local management of extents automatically tracks adjacent , eliminating the need to coalesce free extents. The sizes of extents that are managed locally can be determined automatically by the . Alternatively, all extents can have the same size in a locally managed tablespace.
Dictionary Managed Tablespaces:
In DMT, to keep track of the free or used status of blocks, oracle uses data dictionry tables. Oracle9i
free space system
When an extent is allocated or freed for reuse, free space is recorded in the SYS.FET$ table, and used space in the SYS.UET$ table. Whenever space is required in one of these tablespaces, the ST (space transaction) enqueue latch must be obtained to do inserts and deletes agianst these tables. As only one process can acquire the ST enque at a given time, this often lead to contention. These changes generate rollback information because they update tables (like sys.uet$, sys.fet$) in the data dictionary.
Advantages of Locally Managed Tablespaces(LMT) over Dictionary Managed Tablespaces(DMT):
1. Reduced recursive space management
2. Reduced contention on data dictionary tables 3. No rollback generated
4. No coalescing required Converting DMT to LMT: SQL> exec
dbms_space_admin.Tablespace_Migrate_TO_Local('ts1'); PL/SQL procedure successfully completed.
Converting LMT to DMT: SQL> exec
dbms_space_admin.Tablespace_Migrate_FROM_Local('ts2'); PL/SQL procedure successfully completed.
Important Points:
1. LMTs can be created as
a) AUTOALLOCATE: specifies that the tablespace is system managed. Users cannot specify an extent size.
b) UNIFORM: specifies that the tablespace is managed with uniform extents of SIZE bytes. The default SIZE is 1 megabyte.
2. One cannot create a locally managed SYSTEM tablespace in 8i.
3. This is possible with in 9.2.0.X, where SYSTEM tablespace is created by DBCA as locally managed by default. With a locally managed SYSTEM tablespace, the rest of the tablespaces in such
have to be locally managed as well.
4. Locally managed temporary tablespaces can not be of type "permanent". database
Source : dbapool.com 0 comments Links to this post
ORACLE RA C ON/RAC OFF
Oracle's RAC: ON and OFF
In some cases, you may want to disable the RAC options for testing purposes -- perhaps to run a benchmark or convert the RAC binaries to single instance binaries. In such a case, you can use the following procedure to convert the RAC installation to non-RAC. Disabling and enabling RAC options are available only for UNIX platforms. Windows installations do not support relinking binaries with RAC ON and OFF.
Use the following steps to disable RAC (known as RAC OFF):
1. Log in as the Oracle software owner (which is typically the UNIX account oracle) in all nodes.
2. Shut down all the instances from all the nodes using a NORMAL or IMMEDIATE option. 3. Change the working directory to $ORACLE_HOME/lib: cd $ORACLE_HOME/lib
4. Run the following make command to relink the Oracle binaries without the RAC option: make -f ins_rdbms.mk rac_off
This normally runs for few minutes and should not pose any errors. 5. . Now relink the Oracle binaries: make -f ins_rdbms.mk ioracle
Now the Oracle binaries are relinked with the RAC OFF option. You may have to edit the init.ora or SPFILE parameters accordingly. If errors occur in step 4, you may need to contact Oracle Support and log a service request with the trace and log files.
Use the following steps to enable RAC (known as RAC ON):
1. Log in as the Oracle software owner (typically the UNIX account oracle) in all nodes. 2. Shut down all the instances from all the nodes using a NORMAL or IMMEDIATE option. 3. Change the working directory to $ORACLE_HOME/lib:
4. cd $ORACLE_HOME/lib Run the following make command to relink the Oracle binaries without the RAC option:
make -f ins_rdbms.mk rac_on
5. Now relink the Oracle binaries: make -f ins_rdbms.mk ioracle
Now the Oracle binaries are relinked with the RAC ON option. You may need to edit the init.ora or SPFILE parameters accordingly. If any errors occur in step 4, you may need to contact Oracle Support and log a service request with the trace and log files.
1 comments Links to this post
Tracing in Oracle 10g
Trace in Oracle 10g
SQL> EXEC DBMS_MONITOR.session_trace_enable;
SQL> EXEC DBMS_MONITOR.session_trace_enable(waits=>TRUE, binds=>FALSE); SQL> EXEC DBMS_MONITOR.session_trace_disable;
SQL> EXEC DBMS_MONITOR.session_trace_enable(session_id=>1234, serial_num=>1234); SQL> EXEC DBMS_MONITOR.session_trace_enable(session_id =>1234, serial_num=>1234, waits=>TRUE, binds=>FALSE);
SQL> EXEC DBMS_MONITOR.session_trace_disable(session_id=>1234, serial_num=>1234); SQL> EXEC DBMS_MONITOR.client_id_trace_enable(client_id=>'tim_hall');
SQL> EXEC DBMS_MONITOR.client_id_trace_enable(client_id=>'tim_hall', waits=>TRUE, binds=>FALSE); SQL> EXEC DBMS_MONITOR.client_id_trace_disable(client_id=>'tim_hall'); SQL> EXEC DBMS_MONITOR.serv_mod_act_trace_enable(service_name=>'db10g', module_name=>'test_api', action_name=>'running'); SQL> EXEC DBMS_MONITOR.serv_mod_act_trace_enable(service_name=>'db10g', module_name=>'test_api', action_name=>'running', -> waits=->TRUE, binds=->FALSE); SQL> EXEC DBMS_MONITOR.serv_mod_act_trace_disable(service_name=>'db10g', module_name=>'test_api', action_name=>'running');
0 comments Links to this post
NOTE.167000.1 : eBusiness Suite Support - Oracle Diagnostics Support Pack NOTE.235307.1 : OSS Application Diagnostics Tools : FAQ and Troubleshooting NOTE.231142.1 : About Oracle Diagnostics version 2.1
NOTE.262006.1 : About Oracle Diagnostics 2.2
NOTE.300976.1 : Support Diagnostics Newsletter for Applications Core NOTE.357223.1 : "This test can be executed only after logging into NOTE.363759.1 : How To Add Diagnostic Tools To Responsibility Menu Installation
*********
NOTE.215868.1 : 11i Release Notes
NOTE.77219.1 : Applications Release 11.0.3 One-Hour Install for Unix NOTE.287453.1 : Oracle Applications 11.5.10 - Installation Update Notes for NOTE.292424.1 : Cleaning your Windows System After a Failed Oracle NOTE.110372.1 : 11i : How to Clean up the UNIX Environment After Install NOTE.169402.1 : How To Cleanup After A Failed Applications Installation On NOTE.48602.1 : How do I determine which products are fully installed, and NOTE.124353.1 : WIN : Manually Removing all Oracle Components on Microsoft NOTE.275493.1 : Removing 10g Database and Software from AIX, HP-UX, Linux, NOTE.279519.1 : How to completely remove 8i / 9i Database and Software from NOTE.232831.1. : Oracle Applications NLS Release Notes 11.5.9 - B10846-01 NOTE.287453.1 : Oracle Applications 11.5.10 - Installation Update Notes for NOTE.187240.1 : Applications AOL Scenarios : Is It Supported ?
NOTE.287453.1 : Oracle Applications 11.5.10 - Installation Update Notes for NOTE.316806.1 : Oracle Applications Installation Update Notes, Rel 11.5.10.2 NOTE.360079.1 : Global and Local Inventory explained
NOTE.295185.1 : How to Recreate the Global oraInventory
NOTE.197028.1 : Software Requirements and Tools for Oracle Applications NOTE.201392.1 : Visual C++ Requirement for Release 11i on Windows NOTE.268776.1 : GNU Make Requirement for Release 11i For Windows
NOTE.181244.1 : Configuring VNC Or XVFB As The X Server For Applications 11i NOTE.153960.1 : FAQ : X Server testing and troubleshooting
Patching *******
NOTE.174436.1 : Oracle Applications Patching FAQ
NOTE.175485.1 : How to Apply an 11i Patch When adpatch is Already Running NOTE.231701.1 : How to Find Patching History (10.7, 11.0, 11i)
NOTE.316366.1 : 11.5.10 Oracle E-Business Suite Consolidated Update 2 (CU2) NOTE.259484.1 : Release 11.5.10 Maintenance Pack Installation Instructions NOTE.316365.1 : Oracle Applications Release 11.5.10.2 Maintenance Pack NOTE.139684.1 : Oracle Applications Current Patchset Comparison Utility -NOTE.181665.1 : Release 11i Adpatch Basics
NOTE.358247.1 : ORA-00001 : unique constraint violated error while applying NOTE.358417.1 : Unable to Start Concurrent Managers After Applying 11i. NOTE.353414.1 : Application Technology Group (ATG) Patching Policy NOTE.359198.1 : XDF files fail to load on HP-UX, Memory Fault(Coredump) NOTE.232834.1 : Oracle Applications Release 11.5.9 Maintenance Pack
NOTE.337274.1 : About Oracle Applications Technology 11i.ATG_PF.H Rollup 3 NOTE.244040.1 : Oracle E-Business Suite Recommended Performance Patches NOTE.367756.1 : Uploading Java Objects To Patch History Tables Fails While NOTE.76708.1 : Using ADSPLICE To Add Products To The APPL_TOP In Cloning
******
NOTE.230672.1 : Cloning Oracle Applications Release 11i with Rapid Clone NOTE.216664.1 : FAQ : Cloning Oracle Applications Release 11i
NOTE.242123.1 : Create new middle tier node in existing Apps 11i NOTE.238276.1 : Migrating to Linux with Oracle Applications Release 11i NOTE.364565.1 : Troubleshoot RapidClone or OAM Clone issues
Upgrade *******
NOTE.200963.1 : R11.5.3/R11.5.4/R11.5.5 Upgrade And Install Issues
NOTE.289765.1 : Oracle Applications Release 11i (11.5.10) Upgrade Assistant NOTE.289788.1 : Upgrading Oracle Applications
Autoconfig **********
NOTE.165195.1 : Using AutoConfig to Manage System Configurations with NOTE.218089.1 : Autoconfig FAQ
NOTE.270519.1 : Customizing an AutoConfig Environment
NOTE.260887.1 : Steps to Clean Nonexistent Nodes or IP Addresses from NOTE.341322.1 : How to change the hostname of an Applications Tier using NOTE.338003.1 : How to change the hostname and/or port of the Database Tier NOTE.108865.1 : How To Create a Database Connection(DBC) File and
AD Utilities **********
NOTE.233038.1 : AD Command Line Options Relinking
********
NOTE.69798.1 : Basics of Relinking an Executable or Binary in an Oracle
NOTE.1009722.6 : How to relink Oracle Concurrent Program Executables on Unix OAM
****
NOTE.210062.1 : Generic Service Management (GSM) in Oracle Applications 11i NOTE.117264.1 : FAQ (Oracle Applications Manager)
NOTE.177089.1 : OAM11i Standalone Mode Setup and Configuration
NOTE.214962.1 : How To Determine The Version Of OAM (Oracle Application NOTE.258330.1 : About Oracle Applications Manager Minipack 11i.OAM.H NOTE.166115.1 : Oracle Applications Manager 11i integrated with Oracle NOTE.185431.1 : Troubleshooting Oracle Applications Manager OAM 2.0 for 11i NOTE.73959.1 : Installation and Configuration of Oracle Application Manager NOTE.225024.1 : Oracle Applications Manager 11i Documentation Roadmap NOTE.338317.1 : Basic Information on Cloning An 11.5.10 System Using OAM NLS/MLS
*******
NOTE.15389.1 : NLS DEBUGGING SUCCESS GUIDE ** INTERNAL ONLY ** NOTE.227332.1 : NLS considerations in Import/Export - Frequently Asked NOTE.15095.1 : Export/Import and NLS Considerations
NOTE.175300.1 : A Guide to Debugging Globalization (NLS) Support Issues NOTE.124721.1 : Migrating an Applications Installation to a New Character Forms
*****
NOTE.130686.1 : How to Generate Form, Library and Menu for Oracle NOTE.177610.1 : Oracle Forms in Applications FAQ
Apps Database *************
NOTE.174605.1 : bde_chk_cbo.sql - Reports Database Initialization NOTE.186125.1 : Applications 11i and Standby Databases
NOTE.248857.1 : Oracle Applications Tablespace Model Release 11i -NOTE.216205.1 : Database Initialization Parameters for Oracle Applications NOTE.285267.1 : Oracle E-Business Suite 11i and Database FAQ
NOTE.230627.1 : 9i Export/Import Process for Oracle Applications Release 11i NOTE.183078.1 : Recreating Applications 11i JAVA objects in the database NOTE.216212.1 : Business Continuity for Oracle Applications Release 11i, NOTE.282038.1 : Oracle Applications Release 11i with Oracle Database 10g NOTE.340859.1 : Upgrading Oracle Applications 11i Database to 10g with NOTE.174605.1 : bde_chk_cbo.sql - Reports Database Initialization NOTE.216211.1. : Nologging in the E-Business Suite
NOTE.282038.1 : Oracle Applications Release 11i with Oracle Database 10g RAC / Apps RAC
**************
NOTE.220970.1 : RAC : Frequently Asked Questions
NOTE.312731.1 : Configuring Oracle Applications Release 11i with 10g RAC NOTE.270901.1 : How to Dynamically Add a New Node to an Existing 9.2.0 RAC NOTE.294652.1 : E-Business Suite 11i on RAC : Configuring Database Load balancing & Failover
NOTE.312731.1 : Configuring Oracle Applications Release 11i with 10g RAC NOTE.279956.1 : Oracle E-Business Suite Release 11i with 9i RAC :
NOTE.277825.1 : How to setup Tnsnames.ora (806) for 11i and RAC Database
********
NOTE.1012933.6 : General Information : Alert Logs and Trace Files NOTE.209870.1 : How to Reload the JVM in 9.2.0.X
NOTE.175472.1 : How to Reload the JVM in 8.1.7.X
NOTE.159143.1 : Separating Multiple 8i or 9i Oracle Versions to Avoid NOTE.307349.1 : OUI tips for Oracle RDBMS 10.1.X on OpenVMS NOTE.130814.1 : How to move LOB Data to Another Tablespace System Administration
*******************
NOTE.189457.1 : Oracle Applications Systems Administration Setup and Usage NOTE.290525.1 : Oracle User Management FAQ
NOTE.316277.1 : Unable To Change Guest User Password In Oracle Applications NOTE.311552.1 : How to optimize the purge process in a high transaction
NOTE.154850.1 : How to Run the Purge Concurrent Request and/or Manager Data Jserver
******
NOTE.295484.1 : Clear Server Cache and Bounce Apache (Web Server) NOTE.220188.1 : Oracle Applications Release 11i apps.zip Re-architect NOTE.230688.1 : Basic ApacheJServ Troubleshooting with IsItWorking.class Invalid Objects
*************
NOTE.60558.1 : Troubleshooting the Source of Invalid Objects
NOTE.266910.1 : How To Resolve IES Invalid Java Classes After Loading JAR NOTE.113947.1 : Step by Step Troubleshooting Guide to Solve APPS Invalid UNIX / LINUX / WINDOWS
=======================
NOTE.1007808.6 : HOW TO HANDLE CORE DUMPS ON UNIX NOTE.1812.1 : TECH : Getting a Stack Trace from a CORE file NOTE.28588.1 : TECH : Using Truss / Trace on Unix
Advanced Configuration: ======================
NOTE.217368.1 : Advanced Configurations and Topologies for Enterprise NOTE.226880.1 : Configuration of Load Balancing and Transparent Application NOTE.97926.1 : Failover Issues and Limitations [Connect-time failover and NOTE.123718.1 : 11i : A Guide to Understanding and Implementing SSL for Techstack:
=========
NOTE.162488.1 : Complete Guide to JInitiator 1.1.8 Setup & Troubleshooting NOTE.312572.1 : About Oracle Applications Technology Updates for Release 11. NOTE.246105.1 : Upgrading to J2SE 1.4.2 with Oracle Applications 11i
NOTE.94091.1 : Example : Identifying Connection String Problems in JDBC Workflow:
========
NOTE.298550.1 : Troubleshooting Workflow Data Growth Issues General:
=======
NOTE.184977.1 : Certify FAQ, Product Navigation & User's Guide
NOTE.224882.1 : Common Commands asked by Oracle Support for Troubleshooting NOTE.110415.1 : Payables MRC Frequently Asked Questions
NOTE.285218.1 : Recommended Browsers for Oracle Applications 11i NOTE.257650.1 : Resolving Problems with Connection Idle Timeout
NOTE.270523.1 : How To Find the Oracle Applications Framework and Rollup NOTE.301504.1 : "Cannot Complete Applications Logon" Error After Selecting NOTE.315094.1 : JTF Login Page - an operational assessment for CRM
NOTE.242490.1 : How To Configure The "/etc/hosts" File On Linux
NOTE.282038.1 : Oracle Applications Release 11i with Oracle Database 10g Release 1 NOTE.342861.1 : Java Version Conflict When Migrating Platforms
NOTE.209999.1 : Oracle Applications Manager in Oracle Applications 11.5.8 NOTE.198160.1 : Summary note to LOB's/BLOB's/CLOB's/NCLOB's and BFILES
NOTE.159244.1 : How To Use FNDCPASS to Change The Oracle Users, APPS, APPLSYS and Application Module Passwords (INV, AR, AP, etc.) For Applications 11.5 in Unix
NOTE.186981.1 : Oracle Application Server with Oracle E-Business Suite NOTE.333436.1 : Oracle E-Business Suite 11i on Windows Server 2003 Service NOTE.356878.1 : How to relink the whole Applications 11i Installation NOTE.197031.1 : 32-bit/64-bit Certification/Conversion Issues on Oracle NOTE.158577.1 : NLS_LANG Explained (How does Client-Server Character NOTE.227331.1 : Setting NLS Parameters - Frequently Asked Questions NOTE.124721.1 : Migrating an Applications Installation to a New Character NOTE.43208.1 : Certified Compilers
NOTE.66320.1 : Changing the Database Character Set or the Database NOTE.279956.1 : Oracle E-Business Suite Release 11i with 9i RAC : NOTE.260393.1 : Java Mailer and Other 11.5.9/OWF G Current Issues in NOTE.232313.1 : Information on Previous Versions of Developer 6i Patchsets NOTE.125767.1 : Upgrading Developer 6i with Oracle Applications 11i NOTE.371438.1 : adpreclone.pl dbTier fails with RC-50409 : Topology
NOTE.135949.1 : Troubleshooting the Personal Home Page Login Problems in 11. NOTE.164317.1 : Upgrading JDBC drivers with Oracle Applications 11i
NOTE.302035.1 : How to Test a JDBC Thin Driver Connection From the SSO NOTE.365735.1 : How to use Digital Certificates for 11i Applications NOTE.357922.1 : Autoconfig Reverts to old Context File Values.
NOTE.406376.1 : Oracle E-Business Tax Release 12 Known Issues
NOTE.577713.1 : On Windows, After 10g Upgrade, Running Autoconfig on Apps NOTE.343917.1 : Frequently Asked Questions : Oracle E-Business Suite Support NOTE.553831.1 : java.lang.ArrayIndexOutOfBoundsException Error when
NOTE.119319.1 : How to Replace Oracle Logo with Company Logo on Applications 11i Sign-On Screen
NOTE.554336.1 : How Do You Manually Generate A Form In Release 12 (frmcmp) NOTE.444286.1 : How to manually generate a R12 report on Unix
NOTE.356433.1 : Using Oracle Applications Release 11i with Virtual Hostnames and Business Continuity
NOTE.555081.1 : Concurrent Manager Does Not Start if the Profile Option "Concurrent : GSM Enabled" is Set "Y" at Site Level
NOTE.149124.1 : Creating a StatsPack performance report
NOTE.471566.1 : Migrating Oracle E-Business Suite R12 from Linux 32-bit to Linux 64-bit NOTE.397757.1 : How to Speed Up Index Creation on FND_LOBS by indexing Only FND_HELP Data
NOTE.375127.1 : How to restart the adworker having status fixed,restart,wait NOTE.353150.1 : OPatch Failing Validation Phase When Archiving Really Did Not Fail NOTE.215527.1 : Maintenance Wizard Overview
NOTE.564465.1 : Sysadmin And User Responsibility Not Available
NOTE.219968.1 : SQL*Net, Net8, Oracle Net Services - Tracing and Logging at a Glance NOTE.417122.1 : Resolving the Library Cache Locks
NOTE.419475.1 : Removing Credentials from a Cloned EBS Production Database 0 comments Links to this post
Impor tant Wait Events In Oracle Database SQL Execution Level
Here are some of the most common wait events, and what they mean: enqueue
The process is waiting on an enqueue (a lock you can see in v$lock). This commonly occurs when one user is trying to update a row in a table that is currently being updated by another user.
library cache pin
The process wants to pin an object in memory in the library cache for examination, ensuring no other processes can update the object at the same time. This happens when you are compiling or parsing a PL/SQL object or a view.
library cache load lock
The process is waiting for the opportunity to load an object or a piece of an object into the library cache. (Only one process can load an object or a piece of an object at a time.) latch free
The process is waiting for a latch held by another process. (This wait event does not apply to processes that are spinning while waiting for a latch; when a process is s pinning, it is not waiting.)
buffer busy waits
The process wants to access a data block that is currently not in memory, but another process has already issued an I/O request to read the block into memory. (The process is waiting for the other process to finish bringing the block into memory.)
control file sequential read
The process is waiting for blocks to be read from a control file. control file parallel write
The process has issued multiple I/O requests in parallel to write blocks to all control files, and is waiting for all of the writes to complete.
log buffer space
The process is waiting for space to become available in the log buffer (Space becomes available only after LGWR has written the current contents of the log buffer to disk.) This typically happens when applications generate redo faster than LGWR can write it to disk. log file sequential read
The process is waiting for blocks to be read from the online redo log into memory. This primarily occurs at instance startup and when the ARCH process archives filled online redo logs.
The process is waiting for blocks to be written to all online redo log members in one group. LGWR is typically the only process to see this wait event. It will wait until all blocks have been written to all members.
log file sync
The process is waiting for LGWR to finish flushing the log buffer to disk. This occurs when a user commits a transaction. (A transaction is not considered committed until all of the redo to recover the transaction has been successfully written to disk.)
db file scattered read
The process has issued an I/O request to read a series of contiguous blocks from a data file into the buffer cache, and is waiting for the operation to complete. This typically happens during a full table scan or full index scan.
db file sequential read
The process has issued an I/O request to read one block from a data file into the buffer cache, and is waiting for the operation to complete. This typically happens during an index lookup or a fetch from a table by ROWID when the required data block is not already in memory. Do not be misled by the confusing name of this wait event!
db file parallel read
The process has issued multiple I/O requests in parallel to read blocks from data files into memory, and is waiting for all requests to complete. The documentation says this wait event occurs only during recovery, but in fact it also occurs during regular activity when a process batches many single block I/O requests together and issues them in parallel. (In spite of the name, you will not see this wait event during parallel query or parallel DML. In those cases wait events with PX in their names occur instead.)
db file parallel write
The process, typically DBWR, has issued multiple I/O requests in parallel to write dirty blocks from the buffer cache to disk, and is waiting for all requests to complete. direct path read, direct path write
The process has issued asynchronous I/O requests that bypass the buffer cache, and is waiting for them to complete. These wait events typically involve sort segments.
There are several wait events that we call "idle events" because each of these wait events typically occurs when the Oracle process has nothing to do and is waiting for somebody to give it a task. Idle events are usually not very interesting from a tuning standpoint, so we usually overlook them when evaluating data extracted from the wait event interface. The common idle events are as follows:
Idle Wait Events
client message:PX Idle Wait
dispatcher timer rdbms ipc message
lock manager wait for remote message smon timer Null event SQL*Net message from client
parallel query dequeue SQL*Net message to client pipe getSQL*Net more data from client
PL/SQL lock timer virtual circuit status pmon timerwakeup time manager
0 comments Links to this post
Scripts Used in Critical Production Biz Time M onitoring
=== Temp Usage ===
select sum(blocks)/1024*8 "Size in MB" FROM v$sort_usage;
SELECT ss.sid, sum(st.blocks)/1024*8 FROM v$sort_usage st, v$session ss where ss.saddr=st.session_addr group by ss.sid having sum(st.blocks)/1024*8 > 100 order by 2;
SELECT /*+ RULE */ s.username, s.osuser, s.sid, NVL(s.machine,'N/A'), NVL(s.module,'N/A'), NVL(s.action,'N/A'), NVL(s.program,'N/A'),
s.status ||' for '||
LPAD(((last_call_et/60)-mod((last_call_et/60),60)) /60,2,'0') ||':'||
LPAD(ROUND(mod((last_call_et/60),60)),2,'0') ||' Hr' ,
u.tablespace, u.contents, u.extents, round((u.blocks*8)/1024),
s.sql_address, s.sql_hash_value FROM v$session s, v$sort_usage u WHERE s.saddr = u.session_addr AND u.contents = 'TEMPORARY'
AND s.audsid != USERENV('sessionid') AND (u.blocks*8)/1024 >= 1000
ORDER BY 1,2,3,4,5 Desc; === High Redo ===
SELECT s.inst_id,s.sid, s.serial#, s.username, s.program, i.block_changes FROM gv$session s, gv$sess_io i
WHERE s.sid = i.sid
AND i.block_changes > 10000000 ORDER BY 6 desc, 1, 2, 3, 4; === Rollback Used ===
SELECT rn.name, ROUND(rs.rssize/1024/1024),
s.username, s.osuser, s.sid, NVL(s.machine,'N/A'), NVL(s.module,'N/A'), NVL(s.action,'N/A'),
NVL(s.program,'N/A'),
s.sql_address, s.sql_hash_value, p.spid, s.status ||' for '|| LPAD(((last_call_et/60)-mod((last_call_et/60),60)) /60,2,'0') ||':'|| LPAD(ROUND(mod((last_call_et/60),60)),2,'0') ||' Hr' , round(ceil((t.used_ublk*8)/1024),1) FROM v$rollname rn, v$rollstat rs,
v$session s, v$transaction t, v$process p WHERE rn.usn = rs.usn
AND round((t.used_ublk*8)/1024) >= 1000 AND rs.usn = t.xidusn
AND s.sid = p.pid (+) AND s.taddr = t.addr
ORDER BY 2 desc, s.sid ,s.status === Roll back segement Information ====
select tablespace_name, status segment_status, count(extent_id) "Extent Count", sum(blocks) "Total Blocks",
sum(blocks)*8/(1024*1024) "Total Space in GB" from dba_undo_extents where tablespace_name like '%UNDO%'
group by tablespace_name, status; === Shared Pool Usage ===
SELECT INST_ID,ROUND(bytes/1024/1024, 2)||' MB' FROM gv$sgastat WHERE name='free memory' AND pool='shared pool';
=== Archive Generation for last 5 hours ===
SELECT TO_CHAR(first_time, 'DD-MM-YY') AS Day,TO_CHAR(first_time, 'HH24') AS Hour,COUNT(*) FROM v$log_history WHERE TO_CHAR(first_time, 'DD-MM-YY') = TO_CHAR(sysdate, 'DD-MM-YY') AND TO_CHAR(first_time, 'HH24') >=
TO_CHAR(sysdate, 'HH24') - 5 GROUP BY TO_CHAR(first_time, 'DD-MM-YY'), TO_CHAR(first_time, 'HH24') ORDER BY 2;
=== High Memory ===
select s.inst_id, s.sid, name, round(value/1024/1024) Mem_in_mb, p.* from gv$sesstat s, v$statname n, gv$session p
where n.statistic# = s.statistic# and s.inst_id = p.inst_id
and s.sid = p.sid
and name like '%pga memory%' and value > 1*1024*1024*512;
=== Performance ===
select sql_hash_value, count(*) from v$session where event like 'enq%' group by sql_hash_value;
select sql_hash_value,username,osuser, count(*) from v$session where event like 'enq%' and SQL_HASH_VALUE='&event' group by
sql_hash_value,username,osuser;
select sql_text from v$sqlarea where hash_value = '&hash_value'; select s1.sid,FETCHES,ROWS_PROCESSED from v$sql s,v$session s1 where s.HASH_VALUE=s1.SQL_HASH_VALUE and s1.sid=4885;
col EVENT for a50; col OSUSER for a20; col USERNAME for a20; set linesize 175;
select s.sid,s.serial#,s.status,s.username,s.module,s.osuser,x.event from v$session s,v$session_wait x where s.paddr in
( select addr from v$process where spid in (2340,23869,13827,18261,14880,2381))
and x.sid=s.sid; col EVENT for a50; col OSUSER for a20; col USERNAME for a20; set linesize 175;
select s.sid,s.serial#,s.status,s.username,s.module,s.osuser,x.event from v$session s,v$session_wait x
where x.sid=s.sid and x.event like '&event_name';
=== Top 10 Memory Process at OS level ===
Older Posts tail -10
=== Other Script to update the daily report ===
select status,count(1) from gv$session group by status;
STATUS COUNT(1) ---
---ACTIVE 22
INACTIVE 155
select count(1) from dba_tables where logging='NO'; COUNT(1)
---919
select distinct status,count(1) from dba_indexes group by status; STATUS COUNT(1)
---
---N/A 1639
UNUSABLE 1
VALID 6162
select count(1) from dba_objects where status='INVALID'; '
COUNT(1) ---681
0 comments Links to this post