DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Sunday, December 31, 2017

Query to check Stale statistics

                                         Query to check Stale statistics


---Using Anonymous PL/SQL block


SQL> SET SERVEROUTPUT ON
  DECLARE
ObjList dbms_stats.ObjectTab;
BEGIN
dbms_stats.gather_database_stats(objlist=>ObjList, options=>’LIST STALE’);
FOR i in ObjList.FIRST..ObjList.LAST
LOOP
dbms_output.put_line(ObjList(i).ownname || ‘.’ || ObjList(i).ObjName || ‘ ‘ || ObjList(i).ObjType || ‘ ‘ || ObjList(i).partname);
END LOOP;
END;
/


Oracle 10g Onwards.

col TABLE_NAME for a30
col PARTITION_NAME for a20
col SUBPARTITION_NAME for a20
select OWNER,TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,NUM_ROWS,LAST_ANALYZED from dba_TAB_STATISTICS where STALE_STATS='YES';


Query to check the object timeout in the database.

                                            Query to check the object timeout in the database. 


Query
--------

select /*+ ordered */ w1.sid waiting_session,
h1.sid holding_session,
w.kgllktype lock_or_pin,
w.kgllkhdl address,
decode(h.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
'Unknown') mode_held, 
decode(w.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
'Unknown') mode_requested
from dba_kgllock w, dba_kgllock h, v$session w1, v$session h1
where
(((h.kgllkmod != 0) and (h.kgllkmod != 1)
and ((h.kgllkreq = 0) or (h.kgllkreq = 1)))
and
(((w.kgllkmod = 0) or (w.kgllkmod= 1))
and ((w.kgllkreq != 0) and (w.kgllkreq != 1))))
and w.kgllktype = h.kgllktype
and w.kgllkhdl = h.kgllkhdl
and w.kgllkuse = w1.saddr
and h.kgllkuse = h1.saddr
/

Query to find out the sql which used high CPU.

Please use the below.

set lines 500;
set pages 500;
set long 1000000;
SELECT X.SQL_ID, X.CPU_TIME, X.EXECUTIONS, T.SQL_TEXT
FROM
   DBA_HIST_SQLTEXT T,
   (
      SELECT
         S.SQL_ID SQL_ID,
         SUM(S.CPU_TIME_DELTA/1000000) CPU_TIME,
         SUM(S.EXECUTIONS_DELTA) EXECUTIONS
      FROM
         DBA_HIST_SQLSTAT S,
         DBA_HIST_SNAPSHOT P
      WHERE
         S.SNAP_ID = P.SNAP_ID AND
         P.BEGIN_INTERVAL_TIME >= TO_DATE('&beginTime', 'MM/DD/YYYY HH24:MI') AND
         P.END_INTERVAL_TIME <= TO_DATE('&endTime', 'MM/DD/YYYY HH24:MI')
      GROUP BY S.SQL_ID
   ) X
WHERE T.SQL_ID = X.SQL_ID
ORDER BY X.CPU_TIME DESC;

Query to find the blocking session in time interval.

Please use the below query to get the output.

set lines 170
set pages 10000
col event format a40
col sample_time format a40
select to_char(sample_time,'DD-MON-YY HH24:MI:SS'),SESSION_ID,SESSION_SERIAL#,SQL_ID,BLOCKING_SESSION,BLOCKING_SESSION_SERIAL#,BLOCKING_SESSION_STATUS,EVENT from dba_hist_active_sess_history where to_char(sample_time,'DD-MON-YY HH12:MI:SS') between '22-DEC-17 00:05 AM' and '22-DEC-17 09:10 AM' and event like '%enq:%' and BLOCKING_SESSION is not null order by 1 ;

Query to find out which process is consuming more and more memory

This can be found by using the following query.

COLUMN alme     HEADING "Allocated MB" FORMAT 99999D9
COLUMN usme     HEADING "Used MB"      FORMAT 99999D9
COLUMN frme     HEADING "Freeable MB"  FORMAT 99999D9
COLUMN mame     HEADING "Max MB"       FORMAT 99999D9
COLUMN username                        FORMAT a15
COLUMN program                         FORMAT a22
COLUMN sid                             FORMAT a5
COLUMN spid                            FORMAT a8
SET LINESIZE 300
SELECT s.username, SUBSTR(s.sid,1,5) sid, p.spid, logon_time,
       SUBSTR(s.program,1,22) program , s.process pid_remote,
       s.status,
       ROUND(pga_used_mem/1024/1024) usme,
       ROUND(pga_alloc_mem/1024/1024) alme,
       ROUND(pga_freeable_mem/1024/1024) frme,
       ROUND(pga_max_mem/1024/1024) mame
FROM  v$session s,v$process p
WHERE p.addr=s.paddr
ORDER BY pga_max_mem,logon_time;


To get more detailed information in which component is growing can view from V$PROCESS_MEMORY be used.

Take the value for SID from above output.


COLUMN category      HEADING "Category"
COLUMN allocated     HEADING "Allocated bytes"
COLUMN used          HEADING "Used bytes"
COLUMN max_allocated HEADING "Max allocated bytes"
SELECT pid, category, allocated, used, max_allocated
FROM   v$process_memory
WHERE  pid = (SELECT pid
              FROM   v$process
              WHERE  addr= (select paddr
                            FROM   v$session

                            WHERE  sid = 141));
         WEB LOGIC UNPACK COMMAND FAILS WITH ERROR Version 12.2.1.1.0 to 12.2.1.1.0



Domain is packed on weblogic machine 1 and unpacked on to the second node weblogic machine 2:-

[oracle@weblogic bin]$ scp  /u01/app/oracle/primay.jar oracle@weblogic2:/u01/app/oracle
The authenticity of host 'weblogic2 (192.168.88.137)' can't be established.
RSA key fingerprint is 9b:29:29:d9:d2:ca:6e:2d:02:cd:29:2f:7a:ef:33:60.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'weblogic2,192.168.88.137' (RSA) to the list of known hosts.
oracle@weblogic2's password: 
primay.jar                                                                                                                                                         100%   67KB  67.1KB/s   00:00    

[oracle@weblogic bin]$


[oracle@weblogic bin]$ ./unpack.sh -domain=/u01/app/oracle/product/12.2.1/mv_home/user_projects/domains/base_domain/ -template=/u01/app/oracle/primay.jar -log=/u01/app/oracle/unpack.log 



CFGFWK-60550: ENCOUNTERED ERROR: "ATTRIBUTE "PASSWORD" IS NOT SET FOR USER "WEBLOGIC"". ERROR IS LOGGED TO "NULL".
CFGFWK-60550: SEE LOG FOR DETAILS.
AT COM.ORACLE.CIE.WIZARD.DOMAIN.SILENT.TASKS.RUNSCRIPTTASK.RUNSCRIPTWITHEXECUTOR(RUNSCRIPTTASK.JAVA:645)
AT COM.ORACLE.CIE.WIZARD.DOMAIN.SILENT.TASKS.RUNSCRIPTTASK.EXECUTE(RUNSCRIPTTASK.JAVA:363)
AT COM.ORACLE.CIE.WIZARD.INTERNAL.CONT.SILENTTASKCONTAINER$TASKRUNNER.RUN(SILENTTASKCONTAINER.JAVA:97)
AT JAVA.LANG.THREAD.RUN(THREAD.JAVA:745)
CAUSED BY: COM.ORACLE.CIE.DOMAIN.SCRIPT.SCRIPTEXCEPTION: ATTRIBUTE "PASSWORD" IS NOT SET FOR USER "WEBLOGIC"

SOLUTION:-

Password should be passed into unpack via a wallet using the "-walletDir=dir" command line option. The wallet can be created using the new command "configWallet.sh" located 

[oracle@weblogic2 bin]$ cd ORACLE_HOME/oracle_common/common/bin
[oracle@weblogic2 bin]$ ls
clonedunpack.sh  commEnv.sh     config_builder.sh  configWallet.sh  pack.sh                   printJarVersions.sh  reconfig.sh     unpack.sh
commBaseEnv.sh   commExtEnv.sh  config.sh          getproperty.sh   prepareCustomProvider.sh  qs_config.sh         setHomeDirs.sh  wlst.sh
[oracle@weblogic2 bin]$  ./configWallet.sh -walletDir /u01/app/oracle/wallet -create admin_name weblogic
admin_name:weblogic
Confirm admin_name:weblogic
weblogic:<oracle123> (passowrd)

Confirm weblogic:<oracle123>  (passowrd)

[oracle@weblogic2 bin]$ ./unpack.sh -domain=/u01/app/oracle/product/12.2.1/mv_home/user_projects/domains/base_domain/ -template=/u01/app/oracle/primay.jar -log=/u01/app/oracle/unpack.log -walletDir=/u01/app/oracle/wallet/

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

OUTPUT:- succeed: close template



Disabling SSL for EBS R12

Disabling SSL for EBS R12 by doyenblog on Scribd

To create new application user from existing user with same responsibility

To Create New Application User From Existing User With Same Responsibility by doyenblog on Scribd

Error when trying to generate the iLDT [Custom Integration Repository Loader] [Can't locate Class/MethodMaker.pm]

Error faced :

Can't locate Class/MethodMaker.pm in @INC (@INC contains: /d01/oracle/ZV2A/apps/apps_st/appl/fnd/12.0.0/perl /d01/oracle/ZV2A/apps/apps_st/appl/fnd/12.0.0/xml/orc115 /d01/oracle/ZV2A/apps/apps_st/appl/fnd/12.0.0/perl/FND/irep/repo /d01/oracle/ZV2A/apps/apps_st/appl/fnd/12.0.0/bin/ /d01/oracle/ZV2A/apps/tech_st/10.1.3/perl/lib/5.8.3/i686-linux-thread-multi /d01/oracle/ZV2A/apps/tech_st/10.1.3/perl/lib/5.8.3 /d01/oracle/ZV2A/apps/tech_st/10.1.3/perl/lib/site_perl/5.8.3/i686-linux-thread-multi /d01/oracle/ZV2A/apps/tech_st/10.1.3/perl/lib/site_perl/5.8.3 /d01/oracle/ZV2A/apps/apps_st/appl/au/12.0.0/perl /d01/oracle/ZV2A/apps/tech_st/10.1.3/Apache/Apache/mod_perl/lib/site_perl/5.8.3/i686-linux-thread-multi /ade/smayer_perl58_main_linux/perl58/bin/Linux/Opt/lib/5.8.3/i686-linux-thread-multi /ade/smayer_perl58_main_linux/perl58/bin/Linux/Opt/lib/5.8.3 /ade/smayer_perl58_main_linux/perl58/bin/Linux/Opt/lib/site_perl/5.8.3/i686-linux-thread-multi /ade/smayer_perl58_main_linux/perl58/bin/Linux/Opt/lib/site_perl/5.8.3 /ade/smayer_perl58_main_linux/perl58/bin/Linux/Opt/lib/site_perl .) at /d01/oracle/ZV2A/apps/apps_st/appl/fnd/12.0.0/perl/FND/irep/repo/Rep/Parser.pm line 24.


BEGIN failed--compilation aborted at /d01/oracle/ZV2A/apps/apps_st/appl/fnd/12.0.0/perl/FND/irep/repo/Rep/Parser.pm line 24.Compilation failed in require at /d01/oracle/ZV2A/apps/apps_st/appl/fnd/12.0.0/bin/irep_parser.pl line 37.BEGIN failed--compilation aborted at /d01/oracle/ZV2A/apps/apps_st/appl/fnd/12.0.0/bin/irep_parser.pl line 37.



Solution :

1.       Source the 10.1.3 Oracle Home


2.       Add directory $FND_TOP/perl to variable PERL5LIB in the 10.1.3 Oracle Home environment file after taking a backup.



Path added : /u01/applebs/EBSNEW/apps/apps_st/appl/fnd/12.0.0/perl


3.       Modify the file $IAS_ORACLE_HOME/perl/lib/5.8.3/i686-linux-thread-multi/Config.pm
Note: All occurrences of the above string in "FROM" need to be replaced.

replace FROM : /ade/smayer_perl58_main_linux/perl58/bin/Linux/Opt
              TO : /u01/applebs/EBSNEW/apps/tech_st/10.1.3/perl

4.       Apply patch 13602850

Source 10.1.3 OH again. (IMPORTANT)
Upload patch 13602850.
Unzip the file p13602850_R12_GENERIC.zip

Method to apply the patch : Cd to each of the below folders & run three commands one by one.

cd Compress-Raw-Zlib-2.009
---------------------------
perl Makefile.PL
 make
 make install


cd Compress-Zlib-2.009
----------------------
perl Makefile.PL
 make
 make install


cd Class-MethodMaker-1.12
--------------------------
perl Makefile.PL
 make
 make install




Oracle EBS 12.2 upgrade using rapidwiz fails, Error : "Fatal Error: TXK Install Service oracle.apps.fnd.txk.config.ProcessStateException: FileSys OS COMMAND Failed"

Tried the below steps & it worked

1. Check the 10.1.2 opatch relink log (/fs/inst/apps/logs/ora/10.1.2/install/make_06102020.log) for found fatal relink errors related to /usr/lib/libcryptoutil.so.1.

    Usually several binaries related to OEM will show the above fatal errors.

    For these binaries the system records a warning, and moves on until it reaches the forms relinking, then rapidwiz stops without clear error message.

    After setting LD_LIBRARY_PATH correctly by copying setting from a working 12.2.5 instance, the same error related to libcryptoutil.so.1 will also occur when relinking forms.

2. If you meet similar criteria as described above, check for missing symbolic links on the instance with an issue.

   For example in the above case creating the below symbolic links allowed rapidwiz to proceed past the 10.1.2 home patching:
   cd /usr/lib
   ln -s ../../lib/libcryptoutil.so.1 libcryptoutil.so.1
   ln -s ../../lib/libcryptoutil.so.1 libcryptoutil.so

3. After creating the missing symbolic links, retest the installation and confirm the error is resolved.

T2P CopyConfig.sh gives CLONE-84017 Exception While Exporting Password Policy Data




Tried the below steps & it worked.

1. Delete (or backup) existing generated "config_oam.jar" file from archive location (-archiveLoc) before re-running.

2. In the same command prompt set also CLASSPATH like below:
export CLASSPATH=<OAM_HOME>/oam/server/pswdservice/lib/pswdmgmt-api.jar:$CLASSPATH

3. Re-run copyConfig command.

CLLRIFOI.fmb Form Return FRM-10043 Error When Generated In Languages Other Than English, Brazillian Portugese Or South American Spanish?


The CLL forms are part of the Latin America localizations, have English language as default. It is then translated in Brazillian Portuguese and Latin American Spanish languages only as these are the languages of South America. Therefore there is NO translation available for other languages.

Attempt to generate the CLL forms in languages other than those is designed for results in the FRM-10043 error.

The workaround to have this form present in languages other than the ones it is designed for is to copy the base US language form into each of the non translated language directories.

Connect to Operating System as the APPLMGR user.
$ cd $CLL_TOP/forms/US
$ cp CLLRIFOI.fmx $CLL_TOP/forms/<Other Language>

Alternatively, you can just ignore this compilation error, as these forms should ONLY be accessed in US (English), ESA (Latin America Spanish) and PTB (BrazilPortuguese).

java.io.FileNotFoundException When Invoking Web Services in ISG after cloning

The below steps were done & the issue was fixed :

1. Regenerate the services in the target instance.

After the clone, services have to be re-generated. Its mandatory step as the SOA artifacts will have the end point URL which is instance specific. Only re-generate WSDL will update instance specific details. This is the expected behavior

soagenerate.sh can be used to generate multiple WSDL.

a. Prepare a file with the name of all the interfaces you want to regenerated

Example:

vi /tmp/services.lst
FND_USER_PKG
FND_USER_RESP_GROUPS_API

$FND_TOP/bin/soagenerate.sh
Enter the single or multiple generate as S/M :
M
Enter the filename of the interface to be generated :
/tmp/services.lst



2.Re-deploy using the soadeploy.sh

Example:

$FND_TOP/bin/soadeploy.sh
Enter the single or multiple deploy as S/M :
M
Enter the filename of the interface to be deployed:

/tmp/services.lst

Integrated SOA Gateway related post steps after Cloning

1. Reset the ASADMIN password (required step)
A. Reset the password in the Application
Navigation path:
a. System Administrator Responsibility
b. Security
c. User
d. Define
e. Query the ASADMIN user and change the password

B. Reset the "ASADMIN" password in the file system after "ASADMIN" user is enabled from inside EBS by updating the file:

a. vi $ORA_CONFIG_HOME/10.1.3/j2ee/oafm/config/system-jazn-data.xml

<user>
<name>ASADMIN</name>
<display-name>Default Apps SOA User</display-name>
<description>Used by SOAProvider for DB connection</description>
<credentials>!<NEW PASSWORD></credentials>
</user>

NOTE: The password should be preceded by a '!' (Exclamation) so that when OAFM is started, it gets encrypted. For example, if the password is "welcome", then you have to change it to "!welcome"

b. Restart the appsTier.

$ADMIN_SCRIPTS_HOME/adstpall.sh apps/apps_pw
$ADMIN_SCRIPTS_HOME/adstrtal.sh apps/apps_pw

2. Re-generate all the services

soagenerate.sh can be used to generate multiple WSDL.

a. Prepare a file with the name of all the interfaces you want to regenerated

Example:

vi /tmp/services.lst
FND_USER_PKG
FND_USER_RESP_GROUPS_API

$FND_TOP/bin/soagenerate.sh
Enter the single or multiple generate as S/M :
M
Enter the filename of the interface to be generated :
/tmp/services.lst

3.Re-deploy using the soadeploy.sh

Example:

$FND_TOP/bin/soadeploy.sh
Enter the single or multiple deploy as S/M :
M
Enter the filename of the interface to be deployed:

/tmp/services.lst

4.Make sure that CLASSPATH references the below files:
•soa-jca-client.jar, present in $ORACLE_HOME/jlib/soaprov/lib
•bpm-infra.jar, present in $ORACLE_HOME/jlib/soaprov/lib
•commons-lang-2.0.jar, present in $ORACLE_HOME/jlib/soaprov/lib
•log4j_1.2.8.jar, present in $ORACLE_HOME/jlib/soaprov/lib
•connector.jar, present in $ORACLE_HOME/j2ee/home/lib
•http_client.jar, present in $ORACLE_HOME/j2ee/home/lib
•dms.jar, present in $ORACLE_HOME/lib
•orawsdl.jar, present in $ORACLE_HOME/webservices/lib
•commons-logging.jar, present in $ORACLE_HOME/webservices/lib
•AppsAdapter.jar present in $ORACLE_HOME/j2ee/oafm/connectors/AppsSOAAdapter/AppsSOAAdapter
•DBAdapter.jar present in $ORACLE_HOME/j2ee/oafm/connectors/AppsSOAAdapter/AppsSOAAdapter
•AqAdapter.jar file present in $ORACLE_HOME/j2ee/oafm/connectors/AppsSOAAdapter/AppsSOAAdapter

Saturday, December 30, 2017

Disk Heartbeat Lost in RAC

reboot-less node fencing when disk heartbeat is lost.
– Check that clusterware version

[root@test02 ~]# crsctl query crs activeversion

Oracle Clusterware active version on the cluster is [11.2.0.4.0]

– check that both the nodes in the cluster are active

[root@test02 ~]# olsnodes -s
test01  Active
test02  Active

– Stop ISCSI service on node2

[root@test02 ~]# service iscsi stop

Logging out of session [sid: 1, target: iqn.2006-01.com.openfiler:tsn.e55ea88d0212, portal:
192.9.201.182,3260]
Logout of [sid: 1, target: iqn.2006-01.com.openfiler:tsn.e55ea88d0212, portal: 192.9.201.182,3260]:
successful
Stopping iSCSI daemon:

– Alert log of node2 –

– Note that instead of rebooting the node, CRSD resources are cleaned up

[cssd(2876)]CRS-1649:An I/O error occured for voting file: ORCL:ASMDISK013; details at (:CSSNM00059:)
...
[cssd(2876)]CRS-1606:The number of voting files available, 0, is less than the minimum number of
voting files required, 1, resulting in CSSD termination to ensure data integrity;
[cssd(2876)]CRS-1656:The CSS daemon is terminating due to a fatal error;
[cssd(2876)]CRS-1652:Starting clean up of CRSD resources.
2017-12-09 11:04:30.795
...
[cssd(2876)]CRS-1654:Clean up of CRSD resources finished successfully.
2017-12-09 11:04:31.914

— Check that OHAS service is still up on test02

[root@test02 ~]# crsctl check crs

CRS-4638: Oracle High Availability Services is online
CRS-4535: Cannot communicate with Cluster Ready Services
CRS-4530: Communications failure contacting Cluster Synchronization Services daemon
CRS-4534: Cannot communicate with Event Manager

– Check that resources cssd , crsd and HAIP are down on test02

[root@test02 ~]# crsctl stat res -t -init

--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS      
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.asm
      1        ONLINE  OFFLINE                                                  
ora.cluster_interconnect.haip
      1        ONLINE  OFFLINE                                                  
ora.crf
      1        ONLINE  ONLINE       test02                                      
ora.crsd
      1        ONLINE  OFFLINE                                                  
ora.cssd
      1        ONLINE  OFFLINE                               STARTING           
ora.cssdmonitor
      1        ONLINE  ONLINE       test02                                      
ora.ctssd
      1        ONLINE  OFFLINE                                                  
ora.diskmon
      1        OFFLINE OFFLINE                                                  
ora.drivers.acfs
      1        ONLINE  ONLINE       test02                                      
ora.evmd
      1        ONLINE  OFFLINE                                                  
ora.gipcd
      1        ONLINE  ONLINE       test02                                      
ora.gpnpd
      1        ONLINE  ONLINE       test02                                      
ora.mdnsd
      1        ONLINE  ONLINE       test02

–Check that test02 is no longer a part of the cluster

[root@test01 cluster01]# olsnodes -s
test01  Active
test02  Inactive

– Restart ISCSI service on test02


[root@test02 ~]# service iscsi start

iscsid dead but pid file exists

Turning off network shutdown.

Starting iSCSI daemon:                                     [  OK  ]
                                                           [  OK  ]

Setting up iSCSI targets: Logging in to [iface: default, target: iqn.2006-

01.com.openfiler:tsn.e55ea88d0212, portal: 192.9.201.182,3260]

Login to [iface: default, target: iqn.2006-01.com.openfiler:tsn.e55ea88d0212, portal:

192.9.201.182,3260]: successful
                                                           [  OK  ]
- Alert log of test02

– Note that as soon as ISCSI service is started, CSSD service starts immediately and test02 joins the cluster

[cssd(5481)]CRS-1714:Unable to discover any voting files, retrying discovery in 15 seconds; Details
...
2017-12-09 11:10:43.897

[cssd(5481)]CRS-1707:Lease acquisition for node test02 number 2 completed
2017-12-09 11:10:47.629

[cssd(5481)]CRS-1605:CSSD voting file is online: ORCL:ASMDISK013; details in
/u01/app/11.2.0/grid/log/test02/cssd/ocssd.log.
2017-12-09 11:10:54.652

[cssd(5481)]CRS-1601:CSSD Reconfiguration complete. Active nodes are test01 test02 .

– check that resources haip, cssd and crsd have started on test02

[root@test02 ~]# crsctl stat res -t -init

--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS      
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.asm
      1        ONLINE  ONLINE       test02                   Started            
ora.cluster_interconnect.haip
      1        ONLINE  ONLINE       test02                                      
ora.crf
      1        ONLINE  ONLINE       test02                                      
ora.crsd
      1        ONLINE  ONLINE       test02                                      
ora.cssd
      1        ONLINE  ONLINE       test02                                      
ora.cssdmonitor
      1        ONLINE  ONLINE       test02                                      
ora.ctssd
      1        ONLINE  ONLINE       test02                   OBSERVER           
ora.diskmon
      1        OFFLINE OFFLINE                                                  
ora.drivers.acfs
      1        ONLINE  ONLINE       test02                                      
ora.evmd
      1        ONLINE  ONLINE       test02                                      
ora.gipcd
      1        ONLINE  ONLINE       test02                                      
ora.gpnpd
      1        ONLINE  ONLINE       test02                                      
ora.mdnsd
      1        ONLINE  ONLINE       test02

– Check that test02 has joined the cluster

[root@test02 ~]# olsnodes -s
test01  Active
test02  Active

Playing with GPNP profile.

Current scenario:

2 Node cluster

Cluster name : testdb01.example.com

Host names : test01, test02

Overview:


- Rename GPnP profile on a node (test02)

- Stop and restart crs

- File pending.xml is created in GRID_HOME/gpnp//profiles/peer

- Change ASM_DISKSTRING parameter which causes GPnP profile to be updated

- As soon as the parameter is changed, pending.xml is renamed to profile.xml and Discoverystring for ASM is updated in it.

– Rename GPnP profile on test02

   [root@test02 cdata]# mv /u01/app/11.2.0/grid/gpnp/test02/profiles/peer/profile.xml /u01/app/11.2.0/grid/gpnp/test02/profiles/peer/profile.bak

- Stop and restart crs on test02

     [root@test02 cdata]#crsctl stop crs
     [root@test02 cdata]#crsctl start crs

-- Check the alert log simultaneously and note that just prior to the start of CSSD daemon,

GPnP daemon on node2 copies the GPnP profile from test01 and file pending.xml is created in GRID_HOME/gpnp//profiles/peer

[ohasd(24187)]CRS-2772:Server ‘test02′ has been assigned to pool ‘Free’.
2013-01-17 01:35:14.635

[cssd(24416)]CRS-1713:CSSD daemon is started in clustered mode

[root@test02 cdata]# ls /u01/app/11.2.0/grid/gpnp/test02/profiles/peer/pending*

/u01/app/11.2.0/grid/gpnp/test02/profiles/peer/pending.xml

—- Open the file pending.xml and note that DiscoveryString for ASM is “”


DiscoveryString=””SPFile=”+DATA/testdb01/asmparameterfile/registry.253.804837235″/>

– Change ASM_DISKSTRING parameter which causes GPnP profile to be updated

 SQL>alter system set ASM_DISKSTRING='ORCL:*';

- As soon as the parameter is changed, pending.xml is renamed to profile.xml and Discoverystring for ASM is updated in it.

[root@test02 cdata]# ls /u01/app/11.2.0/grid/gpnp/test02/profiles/peer/profile.xml

/u01/app/11.2.0/grid/gpnp/test02/profiles/peer/profile.xml

—- Open the file profile.xml and note that DiscoveryString for ASM has been updated

DiscoveryString=”ORCL:*” SPFile=”+DATA/testdb01/asmparameterfile/registry.253.804837235″/>
———————————————————————————-

Note : Whenever GPnP profile is not present, it is automatically copied from existing nodes by GPnPd as pending.xml . Whenever, profile is updated, it is automatically renamed to profile.xml.

Reparing the OCR

we can use ocrconfig -repair command to repair ocr configuration on a node which was not up when the configuration was modified on the other nodes.

Current scenario:

3 node cluster

Nodes: testdb01, testdb02, testdb03

Nodes testdb02 and testdb03 are up

Node testdb01 is down

OCR is stored on ASM diskgroup DATA

Overview:

- Store OCR on additionally on FRA diskgroup

- This information is modified in /etc/oracle/ocr.loc on nodes testdb02 and testdb03 which are up

- This information is not modified in /etc/oracle/ocr.loc on node testdb01 which is down.

– Startup Node testdb01

– Clusterware does not come up on testdb01

– Check alert log and crsd log on testdb01

– Repair OCR configuration on testdb01 so that /etc/oracle/ocr.loc on testdb01 gets updated

– STart clusterware on testdb01 – succeeds

Implementation:

- Store OCR on additionally on FRA diskgroup

[root@testdb02 ~]# ocrconfig -add +FRA

- Check that new OCR location is added  in /etc/oracle/ocr.loc on nodes testdb02 and testdb03 which are up

[root@testdb02 ~]# cat /etc/oracle/ocr.loc
#Device/file  getting replaced by device +FRA
ocrconfig_loc=+DATA
ocrmirrorconfig_loc=+FRA

[root@testdb03 ~]# cat /etc/oracle/ocr.loc

#Device/file  getting replaced by device +FRA
ocrconfig_loc=+DATA
ocrmirrorconfig_loc=+FRA

- Check that new OCR location is not added  in /etc/oracle/ocr.loc on node testdb01 which was down

[root@testdb01 ~]# cat /etc/oracle/ocr.loc
ocrconfig_loc=+DATA
local_only=FALSE

– Bring up testdb01 .

– Check that clusterware has not come up there

[root@testdb01 testdb01]# crsctl stat res -t
- Check the alert log of testdb01
[root@testdb01 testdb01]# tailf /u01/app/11.2.0/grid/log/testdb01/alerttestdb01.log
[ohasd(4914)]CRS-2765:Resource ‘ora.crsd’ has failed on server ‘testdb01‘.
2017-12-18 23:35:01.950

- Check the crsd  log of testdb01 – Indicates that local and master information of OCR configuration does not match

[root@testdb01 crsd]# vi /u01/app/11.2.0/grid/log/testdb01/crsd/crsd.log

[  OCRMAS][2876611472]th_calc_av:5′: Rturn persisted AV [186646784] [11.2.0.1.0]
2017-12-18 23:35:13.931: [  OCRSRV][2876611472]th_not_master_change: Master change callback not registered
2017-12-18 23:35:13.931: [  OCRMAS][2876611472]th_master:91: Comparing device hash ids between local and master failed
2017-12-18 23:35:13.931: [  OCRMAS][2876611472]th_master:91 Local dev (1862408427, 1028247821, 0, 0, 0)
2017-12-18 23:35:13.931: [  OCRMAS][2876611472]th_master:91 Master dev (1862408427, 1897369836, 0, 0, 0)
2017-12-18 23:35:13.931: [  OCRMAS][2876611472]th_master:9: Shutdown CacheLocal. my hash ids don’t match

– Repair OCR configuration on testdb01

[root@testdb01 crsd]# ocrconfig -repair -add +FRA

- Check that new OCR location is added  in /etc/oracle/ocr.loc on node testdb01

[root@testdb01 crsd]# cat /etc/oracle/ocr.loc

#Device/file  getting replaced by device +FRA
ocrconfig_loc=+DATA
ocrmirrorconfig_loc=+FRA

– Shutdown and restart cluster on testdb01

[root@testdb01 crsd]# crsctl stop crs -f

[root@testdb01 crsd]# crsctl start crs

[root@testdb01 crsd]# crsctl start cluster

– Check that crsd is started on testdb03

[root@testdb01 testdb01]# tailf /u01/app/11.2.0/grid/log/testdb01/ alerttestdb01.log

[crsd(7297)]CRS-1012:The OCR service started on node testdb01.

2017-12-18 23:46:07.609

[crsd(7297)]CRS-1201:CRSD started on node testdb01.

Identifing the Master node In RAC

In RAC, every data block is mastered by an instance. Mastering a block simply means that master instance keeps track of the state of the block until the next reconfiguration event.
– Remastering can be triggered as result of
    – Manually
    – Resource affinity
    – Instance crash
- Method – I gets info about master node from v$gcspfmaster_info   using data_object_id
- Method – II gets info about master node from v$dlm_ress and v$ges_enqueue   using resource name in hexadecimal format
- Method – III gets info about master node from x$kjbl with x$le using resource name in hexadecimal format
– CURRENT SCENARIO -
- 3 node setup
- name of the database – orcl
— SETUP –
SYS@NODE1>create table scott.emp1 as
             select * from scott.emp;
– Get data_object_id for scott.emp1
SQL> col owner for a10
col data_object_id for 9999999
col object_name for a15
select owner, data_object_id, object_name
from dba_objects
where owner = ‘SCOTT’
and object_name = ‘EMP1′;
OWNER      DATA_OBJECT_ID OBJECT_NAME
———- ————– —————
SCOTT               74652 EMP1

For Method-II and Method-III, we need to find out file_id and block_id and hence GCS  resource name in hexadecimal format

 — Get File_id and range of block_ids of emp1 table
– It can be seen that emp1 lies in block 523 of file 4.
SQL>select dbms_rowid.rowid_relative_fno(rowid) FILE_NO,
min(dbms_rowid.rowid_block_number(rowid)) MIN_BLOCK_ID,
max(dbms_rowid.rowid_block_number(rowid))  MAX_BLOCK_ID
from scott.emp1
group by dbms_rowid.rowid_relative_fno(rowid);

   FILE_NO MIN_BLOCK_ID MAX_BLOCK_ID
———- ———— ————
         4          523          523

– Find the GCS resource name to be used in  the query using blodk_id and data_object_id retrieved above.

   x$kjbl.kjblname = resource name in hexadecimal format([id1],[id2],[type]
   x$kjbl.kjblname2 = resource name in decimal format
   Hexname will be used to query resource master using v$dlm_ress , v$ges_enqueue, $kjbl
   and x$le

SQL> col hexname for a25
col resource_name for a15
select b.kjblname hexname, b.kjblname2 resource_name
from x$le a, x$kjbl b
where a.le_kjbl=b.kjbllockp
and a.le_addr = ( select le_addr
from x$bh
where dbablk = 524
and obj    = 74653
and class  = 1
and state   <> 3);

HEXNAME                   RESOURCE_NAME
————————- —————
[0x21b][0x4],[BL]         524,4,BL

– Manually master the EMP table to node2 –

SYS@NODE1>oradebug lkdebug -m pkey <objectid>

SYS@NODE1>oradebug lkdebug -m pkey 74653

—- GET RESOURCE MASTER NAME ———-

Method – I gets info about master node from v$gcspfmaster_info using data_object_id
– ——-
– Note that current master is node2 (Node numbering starts from 0)
SYS@node1>col object_name for A10
        select o.object_name, m.CURRENT_MASTER
from   dba_objects o, v$gcspfmaster_info m
where o.data_object_id=74653
and m.data_object_id = 74653 ;
OBJECT_NAM CURRENT_MASTER
———- ————–
EMP1                    1


—- Method II gets info about master node from v$dlm_ress and v$ges_enqueue using resource name in hexadecimal format
– check that master node is node2 (node numbering starts with 0)
SYS@NODE1>col resource_name for a22 select a.resource_name,  a.master_node
from   v$dlm_ress a, v$ges_enqueue b
where upper(a.resource_name) = upper(b.resource_name1)
and a.resource_name like ‘%[0x21b][0x4],[BL]%';

RESOURCE_NAME          MASTER_NODE
———————- ———–
[0x20b][0x4],[BL]                1

Method – III gets info about master node from x$kjbl with x$le   using resource name in hexadecimal format
–  This SQL joins   x$kjbl with x$le to retrieve resource master for a block
– Note that current master is node2(MASTER=1)
SYS@NODE1> select kj.kjblname, kj.kjblname2, kj.kjblmaster master
from (select kjblname, kjblname2, kjblowner, kjblmaster, kjbllockp
from x$kjbl
where kjblname = ‘[0x21b][0x4],[BL]’
) kj, x$le le
where le.le_kjbl = kj.kjbllockp
order by le.le_addr;
KJBLNAME                       KJBLNAME2                          MASTER
—————————— —————————— ———-
[0x20b][0x4],[BL]              524,4,BL                                     1

Friday, December 29, 2017

ORA-01157: cannot identify/lock data file 201 - see DBWR trace file



Fix: ORA-01157: cannot identify/lock data file 201 - see DBWR trace file


Issue:

If you are using XML in your database and you are trying to update any XML data, then above error may come.

Fix:

1) Add below line in your parameter file and bounce the database if not there.

*.dispatchers='(PROTOCOL=TCP) (SERVICE=PRODXDB)'

2) Unlock the XDB user if locked.

SQL> alter user XDB account unlock;


3) If you have created a duplicate database or open standby database r/w mode, then source TEMP tablespaces will no more valid. drop your TEMP tablespace and create new TEMP tablespace and make it default.

SQL> DROP TABLESPACE TEMP  INCLUDING CONTENTS AND DATAFILES;

SQL> create temporary tablespace TEMP02 tempfile '/u02/PROD/ORADATA/temp_02.dbf' size 1024M autoextend on;

SQL> alter database default temporary tablespace TEMP02;

adapcctl.sh: exiting with status 150

adapcctl.sh: exiting with status 150

After resolve adoafmctl.sh: exiting with status 204, when i try to start application tier services then getting below error message:
adapcctl.sh: exiting with status 150

Cause:
There is a states file called .opmndat at /oracle/apps/PROD/inst/apps/ora/10.1.3/opmn/logs/states which gets created when services are started.
Whenever services are started this file gets created or updated.
Unfortunately for some reason this file is not updated when services are started.

Solution:
1. shutdown all application services on application tier (applmgr) user
2. nevigate to $INST_TOP/ora/10.1.3/opmn/logs/states directory
3. delete this file .opmndat ( this is hidden file)
#rm .opmndat
4. restart all application services
5. re-test issue

ORA-00824 cannot set sga_target with statistics_level=BASIC

ORA-00824 cannot set sga_target with statistics_level=BASIC


This will happen, when your statistics_level parameter is set to BASIC but you define SGA_TARGET

SQL> startup
ORA-00824: cannot set sga_target due to existing internal settings, see alert log for more information

In alert log you can find this

Cannot set sga_target with statistics_level=BASIC

solution:-

Change either one of these value of statistics_level to TYPICAL or SGA_TARGET =0

ORA-01153: an incompatible media recovery is active

ORA-01153: an incompatible media recovery is active

Want to enable flashback on DR but getting the error.

Reason is because MRP process is running. So we stop MRP process and then try enabling flashback. After that start MRP process.


Steps:

SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO

SQL> ALTER DATABASE FLASHBACK ON;
ALTER DATABASE FLASHBACK ON
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active

Stop MRP:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.

Enable flashback:

SQL> ALTER DATABASE FLASHBACK ON;
Database altered.

Start MRP:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
Database altered.

SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES

ORA-65114: space usage in container is too high

ORA-65114: space usage in container is too high

While an import for a pluggable database and it ran out of space so I tried to resize one of the datafile and got below error while doing it :-

SQL> ALTER DATABASE DATAFILE '/u02/PROD/ORADATA/User01.dbf' RESIZE 10g;
ALTER DATABASE DATAFILE '/u02/PROD/ORADATA/User01.dbf' RESIZE 10g
*
ERROR at line 1:

ORA-65114: space usage in container is too high

Cause :-

This error generally comes when total size of PDB after resize of the datafile is more than the size you have allocated to a PDB while creating.

Solution :-

Change the allocation storage for a PDB and then try to resize the datafile.


Allocation storage of a PDB can be changed by logging in to the PDB :-

SQL> alter session set container=PROD;

Session altered.

Either change the allocation storage to a required value or change it to unlimited.

SQL> ALTER PLUGGABLE DATABASE STORAGE UNLIMITED;

Pluggable database altered.

Oracle EBS table types

                                             Table-types and names used in Oracle Apps


_ALL are multi-org tables.

_TL are tables corresponding to another table with the same name minus the _TL. These tables provide multiple language support. For each item in the table without _TL there can be many rows in the _TL table, but all with different values in the LANGUAGE column.

_F these are date tracked tables which occur in HR and Payroll. For these there are two date columns EFFECTIVE_START_DATE and EFFECTIVE_END_DATE which together with the PK identifies a row uniquely. The date intervals cannot overlap.

_B these are the BASE tables.
They are very important and the data is stored in the these table with all validations.
It is supposed that these table will always contain the perfect format data.
If anything happens to the BASE table data, then it is a data corruption issue.

_V are views.

_VL are views for multi language tables which combines the row of the base table with the corresponding row of the _TL table where the LANGUAGE = USERENV('LANG').

_S are sequences, used for finding new values for the primary key of a table.

_A are Audit Shadow Tables.

_AVn and _ACn are Audit Shadow Views (when data was changed, and with what values).

Enable SQL Trace in Oracle Apps R12

Enable SQL Trace in Oracle Apps R12

SQL trace can be enabled in any E-Business module via different ways. The following table shows all possible ways for enabling trace in a form, self-service page, concurrent program, specific application user, instance wide, etc.

Applications Form:
1) Set the value for profile option Utilities: Diagnostics to 'Yes' at the user-level
2) Navigate to form where you want to trace
3) Turn on Tracing by using the menu option: Home > Diagnostics > Trace > Trace with waits
4) A pop-up with the trace file name and location gets displayed. Note down the trace filename
5) Proceed with steps that need to be traced. Once done tracing, exit the Application
6) Retrieve the raw trace file using the filename (from Step 4) located on the db server

Self Service Page:

1) Set the value for the profile option FND: DIAGNOSTICS to 'Yes' at user-level.
2) Navigate to the Self-Service page where you want to trace
3) Click the Diagnostics icon at the top-right of the page
4) Select Set Trace Level radio button and click 'Go'
5) Select Trace with  waits (recommended) and click 'Save'  
6) Select 'Home' and proceed with performing your screen processing
7) Disable trace once you are done: click on Diagnostics > Set Trace Level > Disable Trace
8) Write down the 'Trace Ids' provided on the left side of the screen
9) Logout/Exit from the application
10) Retrieve raw trace file using the Trace Ids (from step 8) and/or the tracefile_identifier (set by default to the userid)


Concurrent Program Definition:

1) Choose an appropriate responsibility and select the Concurrent > Program > Define screen
2) Search for the concurrent program you want to trace
3) Check the Enable Trace box to turn on tracing for the concurrent program
4) Submit and run the concurrent program
5) Write down the request_id of your concurrent program job
6) Go back to the Define screen and un-check the Enable Trace box for this concurrent program
7) Retrieve the raw trace file using the request_id (from step 5) and/or the tracefile_identifier (set by default to the userid)


Concurrent Program Submission:

1) Set the value for the profile option Concurrent: Allow Debugging to 'Yes' at user-level
2) Choose the appropriate responsibility and concurrent program to be executed
3) Click on the Debug Options button
4) Enable tracing by selecting the SQL Trace Check box and choose the desired trace level
5) Confirm your selection by clicking the OK button
6) Submit the concurrent program
7) Write down the request_id of your concurrent program job.
8) Retrieve the raw trace file using the request_id (from step 7) and/or the tracefile_identifier (set by default to the userid)


Profile Option:
1) If you are activating trace for your own account, navigate to Profile > Personal
2) Press F11, type Initialization% in the Profile Name column, then hit CTRL-F11
3) If you are enabling trace for another user, navigate to Profile > System  
4) Check User and Type in the Username to be traced
5) Type Initialization% in the Profile box and Hit 'Find' 
6) In the User box for Initialization SQL Statement – Custom, type the following statement [Quotes in the statement are all 'Single' quotes]:
BEGIN FND_CTL.FND_SESS_CTL('','','TRUE','TRUE','','ALTER SESSION SET TRACEFILE_IDENTIFIER=''User_Trace'' MAX_DUMP_FILE_SIZE=unlimited EVENTS=''10046 TRACE NAME CONTEXT FOREVER, LEVEL 8''');END;
7) Save. Logout then Login back to applications as the user for whom you turned on tracing, and promptly recreate the problem.
8) Go back to the Profile option in the Form application and delete the Initialization SQL statement, and Hit 'Save', exit the Application
9) Identify and retrieve the trace file using the tracefile_identifier specified in Step 6

Session Level:
You can enable trace on session level using the following commands:
-- For current session only
SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
SQL> ALTER SESSION SET EVENTS '10046 trace name context off';
-- For current session / other session
SQL> CONN sys/password AS SYSDBA;   -- user must have SYSDBA
SQL> ORADEBUG SETMYPID;                  -- debug current session
SQL> ORADEBUG SETOSPID 1234;         -- debug session with OS Process ID (SID)
SQL> ORADEBUG SETORAPID 123456;  -- debug session with Oracle Process ID (SPID)
SQL> ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 8; 
SQL> ORADEBUG TRACEFILE_NAME;    -- display the current trace file.
SQL> ORADEBUG EVENT 10046 TRACE NAME CONTEXT OFF;

System Level:

You can enable trace on the entire system (Instance wide) using the following commands:
SQL> alter system set events '10046 trace name context forever,level 8'; 
OR set the following event in init.ora file:
event="10046 trace name context forever,level 8"