DOYENSYS Knowledge Portal




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




Thursday, January 31, 2019

Active transactions in oracle database:-

col name format a10
col username format a8
col osuser format a8
col start_time format a17
col status format a12
tti 'Active transactions'

select s.sid,username,t.start_time, r.name, t.used_ublk "USED BLKS",
decode(t.space, 'YES', 'SPACE TX',
decode(t.recursive, 'YES', 'RECURSIVE TX',
decode(t.noundo, 'YES', 'NO UNDO TX', t.status)
)) status
from sys.v_$transaction t, sys.v_$rollname r, sys.v_$session s
where t.xidusn = r.usn
and t.ses_addr = s.saddr

/

Friday, January 25, 2019

How to Resolve “ORA-29548: Java system class reported” in 12c?

After applying PSU to 12.1.0.2.0, the application gets following error.

[Error] Execution (1: 1): ORA-29548: Java system class reported: release of classes.bin in the database does not match that of the oracle executable
ORA-06512: at “MDSYS.SDO_JAVA_STP”, line 370
ORA-06512: at “MDSYS.SDO_UTIL”, line 3188
ORA-06512: at “MDSYS.SDO_UTIL”, line 3211

When I checked the alert log file, it puts following messages.

release mismatch 12.1.0.2.160719 1.6 in database (classes.bin) vs 12.1.0.2.0 1.7 in executable

I checked any INVALID objects. But everything was OK. I did a small test about Java.

SQL> select dbms_java.get_jdk_version() from dual
*
ERROR at line 1:
ORA-29548: Java system class reported: release of classes.bin in the database
does not match that of the oracle executable

There was a problem with my java.

Please run below script as sysdba.

SQL> @?/javavm/install/update_javavm_db.sql
SQL> SET FEEDBACK 1
SQL> SET NUMWIDTH 10
SQL> SET LINESIZE 80
SQL> SET TRIMSPOOL ON
SQL> SET TAB OFF
SQL> SET PAGESIZE 100
SQL>
SQL> alter session set “_ORACLE_SCRIPT”=true;

Session altered.

SQL>
SQL> — If Java is installed, do CJS.
SQL>
SQL> — If CJS can deal with the SROs inconsistent with the new JDK,
SQL> — the drop_sros() call here can be removed.
SQL> call initjvmaux.drop_sros();

Call completed.

SQL>
SQL> create or replace java system;
2 /

Java created.

SQL>
SQL> update dependency$
2 set p_timestamp=(select stime from obj$ where obj#=p_obj#)
3 where (select stime from obj$ where obj#=p_obj#)!=p_timestamp and
4 (select type# from obj$ where obj#=p_obj#)=29 and
5 (select owner# from obj$ where obj#=p_obj#)=0;

0 rows updated.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> alter session set “_ORACLE_SCRIPT”=false;

Session altered.

SQL>

Now, let’s do a test again.

SQL> select dbms_java.get_jdk_version() from dual;

DBMS_JAVA.GET_JDK_VERSION()
——————————————————————————–
1.7.0_51

1 row selected.

SQL> select dbms_java.longname(‘TEST’) from dual
2 ;

DBMS_JAVA.LONGNAME(‘TEST’)
——————————————————————————–
TEST

1 row selected.

SQL>



Hope this will helpful .

EBS R12 Login Page Is Not Coming Up - java.lang.RuntimeException: Guest user/pwd does not exist or match: GUEST/ORACLE


Acual Behavior
E-Business Suite (EBS) Production Instance Login page is not coming up, Instead a blank page is being displayed.

The issue can be reproduced at will with the following steps:
1. Type the EBS URL on browser.
2. It redirects to EBS login page.
3. But a blank page is displayed.



CHANGES


CAUSE
The root cause of the issue is with GUEST User and Password not in sync.

Running the below query, confirmed that GUEST user and password was not in sync


select fnd_web_sec.validate_login('GUEST','ORACLE') from dual;

FND_WEB_SEC.VALIDATE_LOGIN('GUEST','ORACLE')
--------------------------------------------------------------------------------
N


Run following SQL to see more details on why above SQL reported No

select fnd_message.get from dual;
Also, confirmed with the below error on the oacore log

javax.servlet.ServletException: java.lang.RuntimeException: Guest user/pwd does not exist or match: GUEST/ORACLE



SOLUTION
Please perform the below steps

1. Shutdown the EBS services.

2. The only supported way to change the Guest user password is to update the context variable s_guest_pass and run AutoConfig, which runs the AdminAppServer utility internally.

Note : Restriction on the GUEST User Password : The GUEST User password cannot include the special character "#".

 3. Run Autoconfig on DB Node and then application node.

4. Execute the below sql again:

select fnd_web_sec.validate_login('GUEST','ORACLE') from dual;

Check whether query output is showing Y.

If no,  Please check whether the below error is seen on autoconfig log:

Unable to update GUEST_USER_PWD in database to GUEST/ORACLE - Password was not changed, this point to the DB parameter JAVA_JIT_ENABLED which is set as TRUE.

5. On 11g DB you need to have the below settings for EBS specifically, run the below sql

alter system set JAVA_JIT_ENABLED= FALSE scope = both;
6. Follow the steps 2 and 3 again.

7. Run the below command:

perl $FND_TOP/patch/115/bin/ojspCompile.pl --compile --flush -p 2

7. Check whether the compile completed successfully.

8. Restart the application services.

9. Retest the issue.

10. Migrate the solution to appropriate environments.


Reference :(Doc ID 1673030.1)

How to attach ebs 10.1.2 oracle home to central inventory

This post is to define "How to attach ebs 10.1.2 oracle home to central inventory"


bash-3.2$ opatch apply

Oracle Interim Patch Installer version 1.0.0.0.64
Copyright (c) 2011 Oracle Corporation. All Rights Reserved..

Oracle recommends you to use the latest OPatch version
and read the OPatch documentation available in the OPatch/docs
directory for usage. For information about the latest OPatch and
other support-related issues, refer to document ID 293369.1
available on My Oracle Support (https://myoraclesupport.oracle.com)

Oracle Home           : /a01/appstier/UAT/fs2/EBSapps/10.1.2
Oracle Home Inventory : /a01/appstier/UAT/fs2/EBSapps/10.1.2/inventory
Central Inventory     : /var/opt/oracle/oraInventory
   from               : /var/opt/oracle/oraInst.loc
OUI location          : /a01/appstier/UAT/fs2/EBSapps/10.1.2/oui
OUI shared library    : /a01/appstier/UAT/fs2/EBSapps/10.1.2/oui/lib/solaris/liboraInstaller.so
Java location         : /a01/appstier/UAT/fs2/EBSapps/10.1.2/jdk/jre/bin/java
Log file location     : /a01/appstier/UAT/fs2/EBSapps/10.1.2/.patch_storage/<patch ID>/*.log

Creating log file "/a01/appstier/UAT/fs2/EBSapps/10.1.2/.patch_storage/18752577/Apply_18752577_11-25-2017_09-57-56.log"

The Oracle Home /a01/appstier/UAT/fs2/EBSapps/10.1.2 is not registered with the Central Inventory.  OPatch was not able to get details of the home from the inventory.

ERROR: OPatch failed because of Inventory problem.

When we apply patch in oracle home, OPatch checks for the inventory to understand the patch detail which already present. It gives the error above if it does not find any patch details. Since oracle home is not registered with central inventory, opatch could nt get any of the patch details thus it threw an error. We need to attach this particular oracle home to central inventory in order to overcome this error. This is one of the method to overcome this error.

bash-3.2$ cat /var/opt/oracle/oraInst.loc
inventory_loc=/var/opt/oracle/oraInventory
inst_group=dba

Source env file

bash-3.2$ cd $ORACLE_HOME/oui/bin/

Command to be executed :

./runInstaller -silent -attachHome -invPtrLoc /var/opt/oracle/oraInst.loc ORACLE_HOME="/a01/appstier/UAT/fs2/EBSapps/10.1.2" ORACLE_HOME_NAME="UAT_TOOLS_fs2_EBSapps_10_1_2"

bash-3.2$ ./runInstaller -silent -attachHome -invPtrLoc /var/opt/oracle/oraInst.loc ORACLE_HOME="/a01/appstier/UAT/fs2/EBSapps/10.1.2" ORACLE_HOME_NAME="UAT_TOOLS_fs2_EBSapps_10_1_2"


Starting Oracle Universal Installer...
No pre-requisite checks found in oraparam.ini, no system pre-requisite checks will be executed.
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2017-11-25_10-27-20AM. Please wait ...bash-3.2$

How To Create An OCM Response File.

During patching, Opatch tool may ask for an OCM (Oracle configuration manager) response file.

OCM can collect the configuration of your servers and send it to Oracle support.  Oracle support staff will use it when you open a service request. With OCM, you’ll get better and faster response from Oracle support. You can also view your configuration on metalink. Oracle can suggest you updates or patches according to your configuration. You can even use OCM in disconnected systems (where there is no internet connection).

Although it is useful, using OCM is not mandatory. You can still get support from Oracle without OCM. However, during patching, opatch utility may ask for an OCM response file.

How to create an OCM response file?

Opatch utility ships with a script file named “emocmrsp” which is used for creating a response file. “emocmrsp” script will ask your metalink account information and your internet connection information and store it in a response file named “ocm.rsp”.

“emocmrsp” script is found  under “$ORACLE_HOME/OPatch/ocm/bin/” directory. Simply execute this script (as root or oracle user) and enter the required information when prompted.

$ cd $ORACLE_HOME/OPatch/ocm/bin/

$ ./emocmrsp
 OCM Installation Response Generator 10.3.4.0.0 - Production
 Copyright (c) 2005, 2010, Oracle and/or its affiliates.  All rights reserved.
 Provide your email address to be informed of security issues, install and
initiate Oracle Configuration Manager. Easier for you if you use your My
Oracle Support Email address/User Name.
Visit http://www.oracle.com/support/policies.html for details.
Email address/User Name: somireddy.tetali@test.com(Which we use to connect to metalink)

Opatch fails with Lock file error


While applying Oracle Patch, Opatch reports the below error :

OUI-67064:OPatchSession cannot load inventory for the given Oracle Home /ora01/app/oracbr/product/11.2.0/dbhome_1. Possible causes are:
                               No read or write permission to ORACLE_HOME/.patch_storage
                               Central Inventory is locked by another OUI instance
                               No read permission to Central Inventory
                               The lock file exists in ORACLE_HOME/.patch_storage
                               The Oracle Home does not exist in Central Inventory
[Dec 20, 2013 8:29:05 AM]    OUI-67073:UtilSession failed: Lock file left by a different patch, OPatch will not try re-using the lock file.




Note : It has been noticed that opatch lock error also leads to the error "OPatch failed with error code 255"



In earlier releases, error used to look like:

Problems with the lock file
Lock file exists, details are:
Interim Patch is holding the lock from this patch xxxxxx,
probably due to previous unsuccessful operation

ERROR: OPatch failed during pre-reqs check



CAUSE
 When Opatch is invoked, it creates patch_locked file in $ORACLE_HOME/.patch_storage during its execution.

 If the patch application is interrupted or cancelled, this file gets retained under /.patch_storage directory. Hence, On an an attempt to re-apply the patch, the presence of patch_locked file doesnt allow further processing and throw error.


SOLUTION
1. Check if the file "patch_locked" is present in the directory $ORACLE_HOME/.patch_storage

2. If present, rename, move or remove the patch_locked file from $ORACLE_HOME/.patch_storage

3. Re-apply the patch


Reference : 257800.1

INCOIN The concurrent program for your request exited without calling standard Application Object Library exit routines or the process could have been terminated by the operating system

1.Check with the DBA about the field LFILE_SIZE in the table FND_CONCURRENT_REQUESTS is set to what value.
2.Check if log file is created properly with LFILE_SIZE > 0.
  If LFILE_SIZE is not set then set the value >0 and check if issue can be replicated.
3.Check with their DBA to delete old trace/log files so that enough space is present on the slot.
4. Check if issue can be replicated now.

5.Either apply the latest version of (invprg.ldt), with patch <27656545:R12.INV.C> OR action below
A. Take the back up of the existing file invprg.ldt
($INV_TOP/patch/115/import/US).
B. download the newly uploaded file in binary format.
C. Apply the newly downloaded file to the instance using fndload:

FNDLOAD apps/apps@mm1dv210 0 Y UPLOAD $fnd/patch/115/import/afcpprog.lct
invprg.ldt
change the instance uid/password and instance accordingly.

D. again Go to System Administrator Responsibility => concurrent=> program
=>Executable, query (press F11) for "Import Items" and query (ctrl+f11).
Now get us the screen captures.

E. Again go to System Administrator Responsibility => concurrent=> program =>
define, query (F11) for Program as "Import Items" and query (ctrl+f11). get us screen capture for this also. Now Click "Parameters" and get us the screen
capture of all parameters.

F. Also check the version of the invprg.ldt file present at $INV_TOP/patch/115/import/US.
strings -a $INV_TOP/patch/115/import/US/invprg.ldt | grep \$Header

G. Now try to replicate the problem and let us know if issue can be replicated. Also check if error message is still the same.


Refernce Doc Id :2456140.1

Query To Get Internal Manager (ICM) Log File Location

Query To Get Internal Manager (ICM) Log File Location
-----------------------------------------------------------------------

SELECT 'ICM_LOG_NAME=' || fcp.logfile_name
FROM    fnd_concurrent_processes fcp, fnd_concurrent_queues fcq
WHERE   fcp.concurrent_queue_id = fcq.concurrent_queue_id
AND     fcp.queue_application_id = fcq.application_id
AND     fcq.manager_type = '0'
AND     fcp.process_status_code = 'A';

TNS-00583: Valid node checking: unable to parse configuration parameters

 TNS-12560: TNS:protocol adapter error  TNS-00583: Valid node checking: unable to parse configuration parameters


I  Received following errors when trying to startup my listener. I verified the listener.ora and sqlnet.ora files and everything seemed to look normal.


[oracle@Linux03 encryption_keystore]$ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 12-JAN-2019 10:05:32

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/12.1.0.2/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/Linux03/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))
TNS-12560: TNS:protocol adapter error
 TNS-00583: Valid node checking: unable to parse configuration parameters



Listener failed to start. See the error message(s) above...

[oracle@Linux03 encryption_keystore]$ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 12-JAN-2019 10:07:41

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/12.1.0.2/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/Linux03/listener/alert/log.xml
Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))
TNS-12560: TNS:protocol adapter error
 TNS-00583: Valid node checking: unable to parse configuration parameters



Listener failed to start. See the error message(s) above...

  
I had below line in my sqlnet.ora file.

[oracle@Linux03 admin]$ cat sqlnet.ora_bak
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

ENCRYPTION_WALLET_LOCATION =
  (SOURCE =(METHOD = FILE)(METHOD_DATA =
    (DIRECTORY = /u01/app/oracle/admin/$ORACLE_SID/encryption_keystore/)
   

Solution (in my case): Removing the ENCRYPTION_WALLET_LOCATION info did the trick for me.


[oracle@Linux03 encryption_keystore]$ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 12-JAN-2019 10:31:41

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/12.1.0.2/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/Linux03/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Linux03)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                12-JAN-2019 10:31:41
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/Linux03/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Linux03)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully

Troubleshooting ASM Proxy instance startup

I had trouble starting ASM proxy instance on  one of the nodes in my  2 node flex cluster having nodes host01 and host02. As a result I could not access the volume I created on an ASM  diskgroup.  This post explains  how I resolved it.
While connected to host01, I created a volume VOL1 on DATA diskgroup with corresponding volume device /dev/asm/vol1-106 .

[grid@host01 root]$ asmcmd volcreate -G DATA -s 300m VOL1

[grid@host01 root]$ asmcmd volinfo -G DATA VOL1

Diskgroup Name: DATA

Volume Name: VOL1
Volume Device: /dev/asm/vol1-106
State: ENABLED
Size (MB): 320
Resize Unit (MB): 32
Redundancy: MIRROR
Stripe Columns: 4
Stripe Width (K): 128
Usage: ACFS                                                             Mountpath:
I created  ACFS file system on the newly created volume

[root@host01 ~]# mkfs -t acfs /dev/asm/vol1-106
I also created corresponding mount point /mnt/acfsmounts/acfs1 on both the nodes in the cluster.
root@host01 ~]# mkdir -p /mnt/acfsmounts/acfs1

root@host02 ~]# mkdir -p /mnt/acfsmounts/acfs1
When I tried to mount the volume device, I could mount the volume device on host01 but not on host02 .

[root@host01 ~]#mount -t acfs /dev/asm/vol1-106 /mnt/acfsmounts/acfs1

[root@host01 ~]# mount | grep vol1

/dev/asm/vol1-106 on /mnt/acfsmounts/acfs1 type acfs (rw)

[root@host02 ~]# mount -t acfs /dev/asm/vol1-106 /mnt/acfsmounts/acfs1

mount.acfs: CLSU-00100: Operating System function: open64 failed with error data: 2
mount.acfs: CLSU-00101: Operating System error message: No such file or directory
mount.acfs: CLSU-00103: error location: OOF_1
mount.acfs: CLSU-00104: additional error information: open64 (/dev/asm/vol1-106)                                                                    mount.acfs: ACFS-02017: Failed to open volume /dev/asm/vol1-106. Verify the volume exists.

The corresponding volume device was visible on host01 but not on host02
[root@host01 ~]# cd /dev/asm
[root@host01 asm]# ls
vol1-106

[root@host02 ~]# cd /dev/asm
[root@host02 asm]# ls
Since ADVM / ACFS utilize an ASM Proxy instance in a flex cluster to access metadata from a local /  remote  ASM instance ,  I checked whether ASM Proxy instance was running on both the nodes and realized that whereas ASM Proxy instance was running on host01, it  was not running on host02
[root@host01 ~]# ps -elf | grep pmon | grep APX
0 S grid 27782 1 0 78 0 – 350502 – 10:09 ? 00:00:00 apx_pmon_+APX1

[root@host02 asm]# ps -elf | grep pmon | grep APX
[root@host01 ~]# srvctl status asm -proxy
ADVM proxy is running on node host01
[root@host01 ~]# crsctl stat res ora.proxy_advm -t——————————————————————————–                                           Name Target State Server State details——————————————————————————–                                                  Local Resources                                          ——————————————————————————–     
ora.proxy_advm
ONLINE ONLINE host01 STABLE
ONLINE OFFLINE host02 STABLE

I tried to start ASM  proxy instance manually on host02

[grid@host02 ~]$ . oraenv
ORACLE_SID = [grid] ? +APX2
The Oracle base has been set to /u01/app/grid

[grid@host02 ~]$ sqlplus / as sysasm

SQL*Plus: Release 12.1.0.1.0 Production on Sat May 2 10:31:45 2015

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup

ORA-00099: warning: no parameter file specified for ASMPROXY instance
ORA-00443: background process "VUBG" did not start

SQL> ho oerr ORA 00443

00443, 00000, "background process \"%s\" did not start"
// *Cause: The specified process did not start.
// *Action: Ensure that the executable image is in the correct place with
// the correct protections, and that there is enough memory.
I checked the memory allocated to VM for host02 – It was 1.5 GB as against 2.5 GB assigned to VM for host01. I  increased the memory of host02 to 2.5 GB and ASM proxy instance started automatically.
[root@host01 ~]# crsctl stat res ora.proxy_advm -t
--------------------------------------------------------------------------------
Name Target State Server State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.proxy_advm
ONLINE ONLINE host01 STABLE
ONLINE ONLINE host02 STABLE

Thursday, January 24, 2019

How to get the output xml file (.out) file of the specific concurrent request in application R12.

How to get the output xml file (.out) file of the specific concurrent request in application R12.

Requirements:

1.Concurrent program Request ID.
2.Output file destination.

Steps:

1.End user ask for xml output file for specific request ID.

2.login as APPS user.

3.The following table contains the details of the output file.

DESC FND_CONCURRENT_REQUESTS

APPS@TEST> select REQUEST_ID,LOGFILE_NAME,LOGFILE_NODE_NAME,OUTFILE_NAME,OUTFILE_NODE_NAME from fnd_concurrent_requests where REQUEST_ID=&a;

Enter value for a: 122639731

old   1: select REQUEST_ID,LOGFILE_NAME,LOGFILE_NODE_NAME,OUTFILE_NAME,OUTFILE_NODE_NAME from fnd_concurrent_requests where REQUEST_ID=&a
new   1: select REQUEST_ID,LOGFILE_NAME,LOGFILE_NODE_NAME,OUTFILE_NAME,OUTFILE_NODE_NAME from fnd_concurrent_requests where REQUEST_ID=122639731

REQUEST_ID LOGFILE_NAME LOGFILE_NODE_NAME OUTFILE_NAME OUTFILE_NODE_NAME
------------------------------------------------------------------------------------
 122639731 /u01/oracle/PROD/inst/apps/test/logs/appl/conc/log/l122639731.req sssssss
           /u01/oracle/PROD/inst/apps/test/logs/appl/conc/out/o122639731.out sssssss

4.Connect through winscp and go to the out file location and download the file and give the file to end user.

Wednesday, January 23, 2019

Query to check assigned responsibility of FND User.

Query to check assigned responsibility of FND User.
----------------------------------------------------------------

select usr.user_id, usr.user_name, res.RESPONSIBILITY_ID, res.RESPONSIBILITY_NAME
from apps.FND_USER usr, apps.FND_RESPONSIBILITY_TL res, apps.FND_USER_RESP_GROUPS grp
where upper(res.RESPONSIBILITY_NAME) like upper('%' || NVL('&EnterResponsibilityName', 'INV')|| '%')
and grp.responsibility_id = res.responsibility_id
and grp.user_id = usr.user_id;

Query for High CPU consuming queries since past one day

set pages 50000 lines 32767

select * from (
select SQL_ID, sum(CPU_TIME_DELTA),sum(DISK_READS_DELTA),count(*)
from DBA_HIST_SQLSTAT a, dba_hist_snapshot s
where
s.snap_id = a.snap_id
and s.begin_interval_time > sysdate -1
group by SQL_ID
order by sum(CPU_TIME_DELTA) desc);

ORA-19566: Exceeded limit of 0 corrupt blocks for file:

**ORA-19566: Exceeded limit of 0 corrupt blocks for file:                       
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================

RMAN-03009: failure of backup command on t3 channel at 01/21/2019 23:20:55
ORA-19566: exceeded limit of 0 corrupt blocks for file /u01/app/oracle/product/11.2.0.4/oradata/REDHAT/system01.dbf

Solution:-

SQL> select * from V$DATABASE_BLOCK_CORRUPTION;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTION
---------- ---------- ---------- ------------------ ---------
       352     165657          1                  0             ALL ZERO
       352     165656          1                  0              CORRUPT

RMAN> blockrecover datafile 352 block 165656;

Retry.......i hope issue resolved...............

Check block corruption

[oracle@oracle REDHAT]$ dbv file=/u01/app/oracle/product/11.2.0.4/oradata/REDHAT/system01.dbf

DBVERIFY: Release 11.2.0.4.0 - Production on Mon Jan 21 23:34:45 2019

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = /u01/app/oracle/product/11.2.0.4/oradata/REDHAT/system01.dbf


DBVERIFY - Verification complete

Total Pages Examined         : 101120
Total Pages Processed (Data) : 64789
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 13790
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 8426
Total Pages Processed (Seg)  : 1
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 14115
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 6143140 (0.6143140)

ORA-02374: Conversion error loading table during impdp

Error:

ORA-02374: conversion error loading table  "SCHEMA"."TABLENAME"
ORA-12899: value too large for column ENAME (actual: 66, maximum: 64)
ORA-02372: data for row: ENAME : 0X'45737061C3B16F6C2020202020202020202020202020202020'


More About the Error:

1.        Used expdp and created a dump for the table: SCHEMA.TABLENAME
2.        While doing a impdp on the target database :
ORA-02374: conversion error loading table "SCHEMA"."TABLENAME"
ORA-12899: value too large for column ENAME (actual: 66, maximum: 64)
ORA-02372: data for row: ENAME : 0X'45737061C3B16F6C2020202020202020202020202020202020'

Solution:

When you describe the table, you will see that the column ENAME is char(64):

SQL> desc  SCHEMA.TABLENAME
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ENUM                                     NOT NULL  NUMBER(5)
 ENUM1                                    NOT NULL  NUMBER(5)
 ENUM2                                              NUMBER(5)
 ENUM3                                              NUMBER(5)
 ENAME                                              CHAR(64)

Execute the below to modify the column of the table:

SQL> alter table SCHEMA.TABLENAME modify ENAME CHAR(66);

Table altered.

Now perform the import using the below command:

impdp directory=DATA_PUMP_DIR dumpfile=ENAME.dmp logfile=ENAME.log tables=SCHEMA.TABLENAME table_exists_action=truncate

ORA-27125: unable to create shared memory segment

When try to start up an Oracle database, the below errors occur:

ORA-27125: unable to create shared memory segment
Linux-x86_64 Error: 28: No space left on device
Additional information: 3773
Additional information: 3221225472

CAUSE

Kernel parameter kernel.shmall is not set properly.

RESOLUTION

Make sure kernel.shmall  is given the appropriate value.

1)  Calculate the value of shmall.

# getconf PAGE_SIZE 4096 Shmall=total size of the SGAs /PAGE_SIZE.

If the total SGA size is 480GB, then it would be 1024 * 1024 * 1024 * 480 / 4096 = 125829120

2) Edit /etc/sysctl.conf

kernel.shmall = 125829120

3) Apply the change

# sysctl -p   4) Check shmall value after change.
# sysctl -A | grep shmall

5) Start up database successfully.

Query to Moving data files between disks

set trimspool on wrap off
set heading off
set verify off
set pages 1000 lines 1000
spool rename.sql

select    'alter database rename file ''' ||
     name || ''' to ''' ||
     replace(name || ''';', '/u01', '/u02')
from     v$datafile
/

select    'alter database rename file ''' ||
    member || ''' to ''' ||
    replace(member || ''';', '/u01', '/u02')
from    v$logfile
/
spool off

Query for performance stats for the instance

select  n.name,
   s.value
from   v$statname n,
           v$sysstat s
where n.statistic# = s.statistic#
order by n.class,n.name
/

Database Security Assessment Tool.

Database Security Assessment Tool.

The Oracle Database Security Assessment Tool (DBSAT) analyzes database configurations, users, their entitlements, security policies and identifies where sensitive data resides to uncover security risks and improve the security posture of Oracle Databases.

The Database Security Assessment Tool (DBSAT) consists of the following components:

1. Collector
2. Reporter
3. Discoverer

1. Collector.

The collector should be run on the database server.
Collector gathers information from the database and generates a JSON file containing all the information.

2. Reporter

DBSAT Reporter will take as input the file generated by the collector (json or zip file).
The Reporter and will produce one zip file containing three reports in different formats: HTML, spreadsheet, and text.

3. Discoverer

The discoverer is essentially a separate Java-based tool that checks for sensitive data in the database.
First you need to create a dbsat.config file. You can call it anything you want as you will be referencing it explicitly later.


1. DBSAT INSTALLATION:

Requirements:
1. DBSAT Tool zip.
2. Python 2.5 and above.
3. Java 1.8 and above.

A) Create specific DBSAT Directory.

mkdir DBSAT
cd /home/oracle/DBSAT

B) Move DBSAT Tool into the location and unzip.

pwd
/home/oracle/DBSAT

[oracle@ssssss DBSAT]$ unzip dbsat.zip
Archive:  dbsat.zip
  inflating: dbsat                   
  inflating: dbsat.bat               
  inflating: sat_reporter.py         
  inflating: sat_analysis.py         
  inflating: sat_collector.sql       
  inflating: xlsxwriter/app.py       
  inflating: xlsxwriter/chart_area.py  
  inflating: xlsxwriter/chart_bar.py  
  inflating: xlsxwriter/chart_column.py  
  inflating: xlsxwriter/chart_doughnut.py  
  inflating: xlsxwriter/chart_line.py  
  inflating: xlsxwriter/chart_pie.py  
  inflating: xlsxwriter/chart.py     
  inflating: xlsxwriter/chart_radar.py  
  inflating: xlsxwriter/chart_scatter.py  
  inflating: xlsxwriter/chartsheet.py  
  inflating: xlsxwriter/chart_stock.py  
  inflating: xlsxwriter/comments.py  
  inflating: xlsxwriter/compat_collections.py  
  inflating: xlsxwriter/compatibility.py  
  inflating: xlsxwriter/contenttypes.py  
  inflating: xlsxwriter/core.py      
  inflating: xlsxwriter/custom.py    
  inflating: xlsxwriter/drawing.py   
  inflating: xlsxwriter/format.py    
  inflating: xlsxwriter/__init__.py  
  inflating: xlsxwriter/packager.py  
  inflating: xlsxwriter/relationships.py  
  inflating: xlsxwriter/shape.py     
  inflating: xlsxwriter/sharedstrings.py  
  inflating: xlsxwriter/styles.py    
  inflating: xlsxwriter/table.py     
  inflating: xlsxwriter/theme.py     
  inflating: xlsxwriter/utility.py   
  inflating: xlsxwriter/vml.py       
  inflating: xlsxwriter/workbook.py  
  inflating: xlsxwriter/worksheet.py  
  inflating: xlsxwriter/xmlwriter.py  
  inflating: xlsxwriter/LICENSE.txt  
  inflating: Discover/bin/discoverer.jar  
  inflating: Discover/lib/ojdbc8.jar  
  inflating: Discover/lib/oraclepki.jar  
  inflating: Discover/lib/osdt_cert.jar  
  inflating: Discover/lib/osdt_core.jar  
  inflating: Discover/conf/sample_dbsat.config  
  inflating: Discover/conf/sensitive_en.ini  

Unzip was Completed , The following files are showing in the unzipped directory.


[oracle@ssssss DBSAT]$ ls -lrt
total 4880
-r-xr-xr-x. 1 oracle oracle   28216 Dec 21  2017 sat_analysis.py
-r-xr-xr-x. 1 oracle oracle  248371 Apr 13  2018 sat_reporter.py
-r-xr-xr-x. 1 oracle oracle   12839 May  1  2018 dbsat
-r-xr-xr-x. 1 oracle oracle   12771 May 18  2018 dbsat.bat
-r-xr-xr-x. 1 oracle oracle   43796 May 31  2018 sat_collector.sql
-rwxrw-r--. 1 oracle oracle 4630125 Jan 22 17:05 dbsat.zip
drwxrwxr-x. 2 oracle oracle    4096 Jan 22 17:05 xlsxwriter
drwxrwxr-x. 5 oracle oracle    4096 Jan 22 17:05 Discover


1. Running DBSAT Collector.


1.Specify the parameters.

$ dbsat collect <connect_string> <destination>

Example: $ dbsat collect system@PRDCLN /DBSAT

Database Security Assessment Tool version 2.0.2 (May 2018)
This tool is intended to assist in you in securing your Oracle database
system. You are solely responsible for your system and the effect and
results of the execution of this tool (including, without limitation,
any damage or data loss). Further, the output generated by this tool may
include potentially sensitive system configuration data and information
that could be used by a skilled attacker to penetrate your system. You
are solely responsible for ensuring that the output of this tool,
including any generated reports, is handled in accordance with your
company's policies.
Connecting to the target Oracle database...
SQL*Plus: Release 12.1.0.2.0 Production on Thu Jul 5 00:05:08 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Enter password:
Last Successful login time: Wed Jun 20 2018 23:51:14 +05:30
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Setup complete.
SQL queries complete.
OS commands complete.
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
DBSAT Collector completed successfully.
Calling /u01/app/oracle/product/12.1.0.2/db_1/bin/zip to encrypt report.json...
Enter password:
Verify password:
adding:report.json (deflated 86%)
zip completed successfully.


2. Running DBSAT Collector.

$ ./dbsat report /DBSAT

[oracle@ssssss DBSAT]$ ./dbsat report /DBSAT

Database Security Assessment Tool version 2.0.2 (May 2018)
This tool is intended to assist in you in securing your Oracle database
system. You are solely responsible for your system and the effect and
results of the execution of this tool (including, without limitation,
any damage or data loss). Further, the output generated by this tool may
include potentially sensitive system configuration data and information
that could be used by a skilled attacker to penetrate your system. You
are solely responsible for ensuring that the output of this tool,
including any generated reports, is handled in accordance with your
company's policies.
Archive: report.zip
[report.zip] report.json password:
inflating: report.json
DBSAT Reporter ran successfully.
Calling /usr/bin/zip to encrypt the generated reports...
Enter password:
Verify password:

adding: report_report.txt (deflated 78%)
adding: report_report.html (deflated 84%)
adding: report_report.xlsx (deflated 3%)
adding: report_report.json (deflated 82%)
zip completed successfully.

3. Running the Discoverer.

Running the Discoverer

export JAVA_HOME=/usr/java/jdk1.8.0_178
[oracle@ssssss DBSAT]$ ./dbsat discover -c Discover/conf/dbsat.config PRODCLN

Database Security Assessment Tool version 2.0.2 (May 2018)
This tool is intended to assist in you in securing your Oracle database
system. You are solely responsible for your system and the effect and
results of the execution of this tool (including, without limitation,
any damage or data loss). Further, the output generated by this tool may
include potentially sensitive system configuration data and information
that could be used by a skilled attacker to penetrate your system. You
are solely responsible for ensuring that the output of this tool,
including any generated reports, is handled in accordance with your
company's policies.
Enter username: system
Enter password:
DBSAT Discover ran successfully.
Calling /usr/bin/zip to encrypt the generated reports...
Enter password:
Verify password:
zip warning: prime_report.zip not found or empty
adding: PRODCLN_discover.html (deflated 84%)
adding: PRODCLN_discover.csv (deflated 82%)
Zip completed successfully.
Unzip the Report

[oracle@ssssss DBSAT]$ ls -lrt
total 400
-r-xr-xr-x. 1 oracle oinstall 28216 Dec 21 2017 sat_analysis.py
-r-xr-xr-x. 1 oracle oinstall 248371 Apr 13 05:03 sat_reporter.py
-r-xr-xr-x. 1 oracle oinstall 12839 May 1 20:07 dbsat
-r-xr-xr-x. 1 oracle oinstall 12771 May 18 12:32 dbsat.bat
-r-xr-xr-x. 1 oracle oinstall 43796 May 31 14:53 dbsat_collector.sql
drwxr-xr-x. 5 oracle oinstall 4096 Jul 4 23:49 Discover
-r--------. 1 oracle oinstall 25095 Jul 5 00:34 dbsatsat_analysis.pyc
drwxr-xr-x. 2 oracle oinstall 4096 Jul 5 00:34 xlsxwriter
-rw-------. 1 oracle oinstall 68 Jul 5 01:27 discoverer.log
-rw-------. 1 oracle oinstall 9330 Jul 5 01:28 PRODCLN_report.zip

[oracle@ssssss DBSAT]$ unzip PRODCLN_report.zip
Archive: PRODCLN_report.zip
[prime_report.zip] PRODCLN_discover.html password:
inflating: PRODCLN_discover.html
inflating: PRODCLN_discover.csv

[oracle@ssssss DBSAT]$ ls -lrt
total 460
-r-xr-xr-x. 1 oracle oinstall 28216 Dec 21 2017 sat_analysis.py
-r-xr-xr-x. 1 oracle oinstall 248371 Apr 13 05:03 sat_reporter.py
-r-xr-xr-x. 1 oracle oinstall 12839 May 1 20:07 dbsat
-r-xr-xr-x. 1 oracle oinstall 12771 May 18 12:32 dbsat.bat
-r-xr-xr-x. 1 oracle oinstall 43796 May 31 14:53 sat_collector.sql
drwxr-xr-x. 5 oracle oinstall 4096 Jul 4 23:49 Discover
-r--------. 1 oracle oinstall 25095 Jul 5 00:34 sat_analysis.pyc
drwxr-xr-x. 2 oracle oinstall 4096 Jul 5 00:34 xlsxwriter
-rw-------. 1 oracle oinstall 68 Jul 5 01:27 discoverer.log
-rw-------. 1 oracle oinstall 41026 Jul 5 01:28 PRODCLN_discover.html
-rw-------. 1 oracle oinstall 13076 Jul 5 01:28 PRODCLN_discover.csv
-rw-------. 1 oracle oinstall 9330 Jul 5 01:28 PRODCLN_report.zip