R12.2 installation failed with Cannot find oraInventory error

Issue: R12.2.0 installation failed with Cannot find oraInventory error on Linux

    Trying local inventory in Oracle Home...
    Cannot find oraInventory
    Please create oraInst.loc in the proper directory for your operating system.
    The contents should look like this:
    inventory_loc=/oracle/oraInventory
    where "/oracle/oraInventory" is the direcotry where the central inventory is to be located.

When I checked the oraInst.loc in the /etc location, it was not there. So, I have manually created.

In cases where the OraInventory is missing or otherwise corrupt, recreate the oraInventory directory on UNIX systems, using the following steps.

[root@Test]# vi /etc/oraInst.loc
inventory_loc=/oracle/product/oraInventory
inst_group=dba

Change the inventory location as the per your directory structure.

[root@Test]# ls -lrt /etc/oraInst.loc
-rw-r--r--. 1 root root 58 Dec  9 00:26 /etc/oraInst.loc

[root@Test]# chmod 644 /etc/oraInst.loc

[root@Test]# ls -lrt /etc/oraInst.loc
-rw-r--r--. 1 root root 58 Dec  9 00:26 /etc/oraInst.loc

Retry the R12 installation

Limitation of ILM Assistant tool

Few days back I got chance to work on ILM Assistant tool.

I faced lots of challenges in ILM Assistant tool. Few are mentioned below:
1) There is no support for ILM Assistant tool on My Oracle Support site (SR)
2) There are lots of bugs in ILM Assistant
3) ILM Assistant can manage one database at a time
4) ILM Assistant can not move data automatically.It only recommends data to be moved
5) ILM Assistant would not archive data.Its only recommend when it is time to archive
6) We can not manage all the EBS tables by ILM Assistant.Only range partitioned by date tables can be managed
7) ILM Assistant would not purge/delete data
8) Modifying pre-partitioned Oracle E-Business Suite indexes and tables is not recommended or supported as it can cause application errors
9) ILM Assistant recommends partitioning only on DATE column.

ORA-04031

Alert log:
Tue Oct 28 15:02:36 2014
Errors in file /oracle/db/tech_st/11.1.0/admin/ORCL_orcl12db/diag/rdbms/ORCL/ORCL/trace/ORCL_ora_6476.trc (incident=6176661):
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","INSERT INTO APPS.XXcustom_MODI...","sql area:PLSQL","tmp")
Tue Oct 28 15:02:36 2014
Errors in file /oracle/db/tech_st/11.1.0/admin/ORCL_orcl12db/diag/rdbms/ORCL/ORCL/trace/ORCL_ora_19831.trc (incident=6178405):
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","INSERT INTO APPS.XXcustom_MODI...","sql area","tmp")
Tue Oct 28 15:02:36 2014
Errors in file /oracle/db/tech_st/11.1.0/admin/ORCL_orcl12db/diag/rdbms/ORCL/ORCL/trace/ORCL_ora_16403.trc (incident=6177549):
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","INSERT INTO APPS.XXcustom_MODI...","sql area","tmp")
Tue Oct 28 15:02:36 2014
Errors in file /oracle/db/tech_st/11.1.0/admin/ORCL_orcl12db/diag/rdbms/ORCL/ORCL/trace/ORCL_ora_2108.trc (incident=6178773):
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","INSERT INTO APPS.XXcustom_MODI...","sql area","tmp")
Incident details in: /oracle/db/tech_st/11.1.0/admin/ORCL_orcl12db/diag/rdbms/ORCL/ORCL/incident/incdir_6176661/ORCL_ora_6476_i6176661.trc
Incident details in: /oracle/db/tech_st/11.1.0/admin/ORCL_orcl12db/diag/rdbms/ORCL/ORCL/incident/incdir_6178405/ORCL_ora_19831_i6178405.trc
...


Trace File :
The ORCL_ora_19831.trc trace file shows:

*** 2014-10-28 15:02:35.455
*** SESSION ID:(1843.3362) 2014-10-28 15:02:35.455
*** CLIENT ID:(RSOW BHUSHAN) 2014-10-28 15:02:35.455
*** SERVICE NAME:(SYS$USERS) 2014-10-28 15:02:35.455
*** MODULE NAME:(e:SQLAP:frm:APXINWKB) 2014-10-28 15:02:35.455
*** ACTION NAME:(SQLAP/PAY_USER_INV_BGPPL- OSSB_W) 2014-10-28 15:02:35.455

=================================
Begin 4031 Diagnostic Information
=================================
The following information assists Oracle in diagnosing
causes of ORA-4031 errors. This trace may be disabled
by setting the init.ora _4031_dump_bitvec = 0
=====================================
Allocation Request Summary Informaton
=====================================
Current information setting: 04014fff
SGA Heap Dump Interval=3600 seconds
Dump Interval=300 seconds
Last Dump Time=10/28/2014 15:02:34
Dump Count=1
Allocation request for: tmp
Heap: 0x5cec12f48, size: 32
******************************************************
HEAP DUMP heap name="sga heap" desc=0x60001170
extent sz=0x9800 alt=216 het=32767 rec=9 flg=-126 opc=0
parent=(nil) owner=(nil) nex=(nil) xsz=0x1 heap=(nil)
fl2=0x60, nex=(nil)
ds for latch 1: 0x60047bd8 0x60049430 0x6004ac88 0x6004c4e0
ds for latch 2: 0x60051400 0x60052c58 0x600544b0 0x60055d08
ds for latch 3: 0x6005ac28 0x6005c480 0x6005dcd8 0x6005f530
ds for latch 4: 0x60064450 0x60065ca8 0x60067500 0x60068d58
ds for latch 5: 0x6006dc78 0x6006f4d0 0x60070d28 0x60072580
ds for latch 6: 0x600774a0 0x60078cf8 0x6007a550 0x6007bda8
reserved granule count 0 (granule size 268435456)
******************************************************
HEAP DUMP heap name="sql area" desc=0x5cec12f48
extent sz=0xfe8 alt=32767 het=336 rec=0 flg=2 opc=2
parent=0x60001170 owner=0x5cec12e30 nex=(nil) xsz=0x10000000 heap=(nil)
fl2=0x26, nex=(nil)





Call Stack Trace:
... kghnospc kghalf kksLoadChild kxsGetRuntimeLock kksfbc opiexe opiefn0 kpoal8 opiodr ttcpip opitsk opiino opiodr opidrv sou2o opimai_real ssthrdmain main __libc_start_main _start
----- End of Call Stack Trace -----

   
This is due to unpublished bug:9267837 fixed in 11.2.0.2 and documented in Note:9267837.8.
   
Download and apply one-off patch number 9267837 for 11.1.0.7.0 on Linux x86-64.

Invalid record in Event name in Audit vault

Issue: If we set audit_trail=DB,extended then audit vault report is showing correct data in Event Name. But if we change it to XML,extended then somtimes it shows Invalid record in Event name in Audit vault.Attached audit vault report for your reference.
Also,there is some time lag while getting the data into the audit vault report.


SQL> select command_class, event_name, length(command_text) from avsys.event_log where event_name like '%INVALID%';

COMMAND_CLASS EVENT_NAME LENGTH(COMMAND_TEXT)
-------------------------------------------------- -------------------------------------------------- --------------------
UNKNOWN EVENT INVALID RECORD 1311
UNKNOWN EVENT INVALID RECORD 1311
UNKNOWN EVENT INVALID RECORD 1311
UNKNOWN EVENT INVALID RECORD 1311
UNKNOWN EVENT INVALID RECORD 176
UNKNOWN EVENT INVALID RECORD 51
UNKNOWN EVENT INVALID RECORD 112
UNKNOWN EVENT INVALID RECORD 249
UNKNOWN EVENT INVALID RECORD
UNKNOWN EVENT INVALID RECORD
UNKNOWN EVENT INVALID RECORD
UNKNOWN EVENT INVALID RECORD
UNKNOWN EVENT INVALID RECORD
UNKNOWN EVENT INVALID RECORD
UNKNOWN EVENT INVALID RECORD 171
UNKNOWN EVENT INVALID RECORD 171


Impact of changing the DB to XML:
1) Parsing XML files is slower than reading data from a table like AUD$. There is no surprise that the data is not collected very fast when the audit_trail parameter was set to XML,EXTENDED.

2) Using XML rather than DB is riskier. There were many bugs in the past because of which the XML tags were not set correctly and as such the parsers had issues. Furthermore , parsing an XML file is much slower than reading a row of the AUD$ table.


This issue is caused by bug 17048003. The developers are trying to solve this in 12.1.3. As an workaround we can set the audit_trail parameter to DB,EXTENDED and have table trail collecting the data from the AUD$ table.

Oracle Audit Vault and Database Firewall 12.1.2 New Features

Couple of months back,Oracle has released a new feature of AVDF which is 12.1.2. One of the main feature is that you can configure external iSCSI SAN server to store the audit data.

According to the Release Notes, the following features are available as of 12.1.2:

• Configure the Audit Vault Server to use an external iSCSI SAN server to store the audit event repository and system data
• The Audit Vault Agent is updated automatically when the Audit Vault Server is upgraded or a patch is applied
• Store archive data in a Network File Share (NFS) location
• Entitlement reports include data specific to Oracle Database 12c
• Database Vault is automatically enabled and configured in the Oracle Database embedded in the Audit Vault Server. This further strengthens security by restricting privileged access to the Oracle Database for all users including those with administrative access
• Password hashing has been upgraded to a more secure standard. Change your passwords after upgrade to take advantage of the more secure hash
• The Audit Vault Agent deployment procedure has been simplified. Registering a host in the Audit Vault Server automatically generates an Agent activation key, and therefore, the step requesting Agent activation is no longer required
• Adding and updating a secured target location has been simplified in the Audit Vault Server administrator console UI
• Define policy alerts to be forwarded to syslog
• Download diagnostics log files from the Audit Vault Server UI
• The Audit Vault Agent is supported on 32-bit Linux and Windows platforms
• Oracle Database 9i is supported for Database Firewall
• MySQL 5.6 is supported on the Database Firewall
• Migration Path to Migrate Oracle Audit Vault 10.3 to AVDF 12.1.2. See MOS Note1666742.1

VKTM detected a time drift. Please check trace file for more details.

Issue: Frequent warning message in alert log
VKTM detected a time drift. Please check trace file for more details.

Error in trace file->
ksesethighpri: (ksb.c:4178) Failed to elevate VKTM's priority from 0 to 1
Error Info: Category(-2), Opname(skgdism_create), Loc(sp.c:1553), ErrMsg(Error 0) Dism(-1038804382)
*** SESSION ID:(1.1) 2014-06-10 20:15:51.823
*** SERVICE NAME:() 2014-06-10 20:15:51.823

kstmmainvktm: failed in setting elevated priority
Verify: SETUID is set on ORADISM and restart the instance
highres_enabled
VKTM running at (100ms) precision
kstmrmtickcntkeeper: param _dbrm_quantum will not be effective

*** 2014-06-10 20:15:51.823
[Start] HighResTick = 1402445751823794
kstmrmtickcnt = 0, ksudbrmseccnt[0] = 1402445751
kstmchkdrift (kstmrmtickcntkeeper:lowres): Time jumped forward by (8000000)usec at (1402445759) whereas (5000000) is allowed


Cause:
OSDBA has the wrong permissions on /etc/privgroup.


Solution:
1.Log in as the root user.
2.Using any text editor, open the /etc/privgroup file, or create it if
necessary.
3.Add or edit the following line, which begins with the name of the OSDBA
group, specifying the privileges RTPRIO and RTSCHED that you want to grant to
this group every time the system restarts (here dba is the OSDBA group):
dba RTPRIO RTSCHED
4.Save the file, and quit the text editor.
5.Enter the following command to grant the privileges to the OSDBA group:
# /usr/sbin/setprivgrp -f /etc/privgroup
6.Enter the following command to verify that the privileges are set
correctly:
# /usr/sbin/getprivgrp dba

Purge Inactive Sessions

Run "Purge Inactive Sessions" concurrent program in Oracle E-Business suite R12.1

This program will purge all session which are not active.

"Purge Inactive Sessions" concurrent program is not included in request group of  system administrator responsibility.

So first we need to add this request to system administrator responsibility request group.


Navigation:

login as sysadmin >

System Administrator > Security > Responsibility > Request

- Here search for Group "System Administrator"
Type: Program

- Add concurrent program "Purge Inactive Sessions"

- save

- Submit new request "Purge Inactive Sessions"

Or,

To Manually Purge the ICX tables:

E-Business suite 11i: $ICX_TOP/sql/ICXDLTMP.sql

E-Business suite R12: $FND_TOP/sql/FNDDLTMP.sql

ORA-39083: Object type PROCACT_SYSTEM failed to create with error

Issue:
While Import full database, few sql profiles were not imported

Error in Import File:
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
ORA-39083: Object type PROCACT_SYSTEM failed to create with error:
ORA-21560: argument 2 is null, invalid, or out of range
Failing sql is:
BEGIN
DECLARE
  sp_name VARCHAR2(30);
  sp_desc VARCHAR2(500);
  sp_category VARCHAR2(30);
  sp_force_match VARCHAR2(3);
  sp_sqltext CLOB;
  sp_hints CLOB;
  profile_already_exists exception;
  pragma EXCEPTION_INIT(profile_already_exists, -13829);
BEGIN
  sp_name := 'SYS_SQLPROF_014232aec76c0029';
  sp_desc := '';
  sp_category := 'DEFAULT';
  sp_force_match := 'NO';
     Completed 5 PROCACT_SYSTEM objects in 69 seconds


Cause:
Import of sql profile SYS_SQLPROF_014232aec76c0029 is causing the problem.


Solution:
Currently there is No Permanent Solution of this issue.Bug 18137408 is under development team.


Workaround:
Note# 457531.1 - How to Move SQL Profiles from One Database to Another (Including to Higher Versions) 

afcpctx.sh failed during autoconfig.sh with INSTE8_PRF 137

afcpctx.sh failed during autoconfig.sh with INSTE8_PRF 137

While cloning of Oracle E-Business suite R12.1.3 application on Sun Solaris 10 64-bit sparc platform I have encountered this Error

afcpctx.sh              INSTE8_PRF         137


Solution:
bash-3.00$ cd $FND_TOP/bin

bash-3.00$ ldd -d FNDCPUCF
        libnnz10.so =>   /data/R12base/apps/tech_st/10.1.2/lib32/libnnz10.so
        libkstat.so.1 =>         /lib/libkstat.so.1
        libnsl.so.1 =>   /lib/libnsl.so.1
        libsocket.so.1 =>        /lib/libsocket.so.1
        libgen.so.1 =>   /lib/libgen.so.1
        libdl.so.1 =>    /lib/libdl.so.1
        libsched.so.1 =>         /usr/lib/libsched.so.1
        libaio.so.1 =>   /lib/libaio.so.1
        librt.so.1 =>    /lib/librt.so.1
        libm.so.1 =>     /lib/libm.so.1
        libclntsh.so.10.1 =>     /data/R12base/apps/tech_st/10.1.2/lib/libclntsh.so.10.1  - wrong ELF class: ELFCLASS64
        libpthread.so.1 =>       /lib/libpthread.so.1
        libc.so.1 =>     /lib/libc.so.1



bash-3.00$ echo $ORACLE_HOME
bash-3.00$ ln -s $ORACLE_HOME/lib/ldflags $ORACLE_HOME/lib32/ldflags
bash-3.00$ ls -ltr $ORACLE_HOME/lib32/ldflags
bash-3.00$ genclntsh
bash-3.00$ relink all


- After performing all above steps check again:


bash-3.00$ cd $FND_TOP/bin
bash-3.00$ ldd -d FNDCPUCF
        libnnz10.so =>   /appl/R12base/apps/tech_st/10.1.2/lib32/libnnz10.so
        libkstat.so.1 =>         /lib/libkstat.so.1
        libnsl.so.1 =>   /lib/libnsl.so.1
        libsocket.so.1 =>        /lib/libsocket.so.1
        libgen.so.1 =>   /lib/libgen.so.1
        libdl.so.1 =>    /lib/libdl.so.1
        libsched.so.1 =>         /usr/lib/libsched.so.1
        libaio.so.1 =>   /lib/libaio.so.1
        librt.so.1 =>    /lib/librt.so.1
        libm.so.1 =>     /lib/libm.so.1
        libclntsh.so.10.1 =>     /appl/R12base/apps/tech_st/10.1.2/lib32/libclntsh.so.10.1
        libpthread.so.1 =>       /lib/libpthread.so.1
        libc.so.1 =>     /lib/libc.so.1
        libmp.so.2 =>    /lib/libmp.so.2
        libmd.so.1 =>    /lib/libmd.so.1
        libscf.so.1 =>   /lib/libscf.so.1


Now, run autoconfig again

Concurrent Managers Status shows ”Target node/queue unavailable”

Issue: Concurrent Managers Status shows  ”Target node/queue unavailable”  in Concurrent–>Manager–>Administer Screen form.

Solution:
Ensure Database is Running and Middle Tier Services are down.

Connect sqlplus as apps user and run the following :
EXEC FND_CONC_CLONE.SETUP_CLEAN;
commit;


Run AutoConfig on all tiers, firstly on the DB tier and then the APPS tiers and webtier to repopulate the required systemtables

Run the CMCLEAN.SQL script from the referenced note below (don’t forget to commit).

Note.134007.1 – ‘CMCLEAN.SQL – Non Destructive Script to Clean Concurrent  Manager Tables‘

Start the middle tier services including your concurrent manager.

Retest the issue.

The Default Rulesets "Allow Oracle Data Pump Operation" and "Allow Scheduler Job"

The default rulesets "Allow Oracle Data Pump Operation" and "Allow Scheduler Job" are not present in 11.2.0.3.

However, the restrictions on using data pump and database scheduler in a database vault environment still hold good. This means that these restrictions can no longer be disabled by disabling the rule sets "Allow Oracle Data Pump Operation" and "Allow Scheduler Job".

The statement SELECT ENABLED FROM FROM DVSYS.DBA_DV_RULE_SET WHERE RULE_SET_NAME = 'Allow Oracle Data Pump Operation'; from product documentation does not work.

SQL> SELECT ENABLED
FROM DVSYS.DBA_DV_RULE_SET
WHERE RULE_SET_NAME = 'Allow Oracle Data Pump Operation';
no rows selected


Cause
This is expected behavior starting with 11.2.0.3 .

The restrictions that were present in the pre-11203 Database Vault on the use of data pump and database scheduler in a Database Vault environment are present in 11.2.0.3 , but not implemented through rule sets in 11.2.0.3.


Solution
References to these rule sets in 11.2.0.3 product documentation have been classified as documentation BUG 13700537.

To perform datapump operations and use scheduler in a DV enabled environment, use the guidelines mentioned in Database Vault Administrator Guide 11.2

The Default Rulesets "Allow Oracle Data Pump Operation" and "Allow Scheduler Job" Are Absent in 11.2.0.3. (Doc ID 1424133.1)

ORA-31626 ORA-31633 ORA-06512 ORA-47401

Datapump Export with Database Vault Fails for SYSTEM User

expdp directory=expimp dumpfile=expimp.dmp logfile=expimp.log full=Y

Export: Release 11.2.0.3.0 - Production on Apr 7 07:09:19 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Username: system/manager
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
ORA-31626: job does not exist
ORA-31633: unable to create master table "SYSTEM.SYS_EXPORT_FULL_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1020
ORA-47401: Realm violation for CREATE TABLE on SYSTEM.SYS_EXPORT_FULL_05


Solution
Add SYSTEM user to the "Oracle Data Dictionary" realm

BEGIN
DVSYS.DBMS_MACADM.ADD_AUTH_TO_REALM(
realm_name => 'Oracle Data Dictionary',
grantee => 'SYSTEM');
END;
/

How to Check and Enable/Disable Oracle Binary Options using Oracle 11.2 new feature – chopt utility

From Oracle 11R2, any specific components can be enabled/disabled with the help of chopt utility. The chopt tool is a command-line utility that is located in the ORACLE_HOME\bin directory.

The syntax for chopt tool:

chopt [ enable | disable] db_option

bash-3.00$ chopt

usage:

chopt <enable|disable> <option>

options:
dm = Oracle Data Mining RDBMS Files
dv = Oracle Database Vault option
lbac = Oracle Label Security
olap = Oracle OLAP
partitioning = Oracle Partitioning
rat = Oracle Real Application Testing


Steps:

1. Shut down the database SID=orcl with srvctl or SQL*Plus:

srvctl stop database -d myDb -> clusterware env

SQL> shu immediate -> single instance

2. Stop the database service, OracleServiceSID, using the services.msc.

3. Run the following commands:

cd %ORACLE_HOME%/bin

chopt enable lbac

- to enable Oracle Label Security

4. Start the database service, OracleServiceSID, using the services.msc.

5. Start up the database:

srvctl start database -d myDb

SQL> startup

example:

bash-3.00$ chopt enable lbac

Writing to /oracle11g/u01/app/oracle/product/11.2.0/install/enable_lbac.log...
%s_unixOSDMakePath% -f /oracle11g/u01/app/oracle/product/11.2.0/rdbms/lib/ins_rdbms.mk lbac_on
%s_unixOSDMakePath% -f /oracle11g/u01/app/oracle/product/11.2.0/rdbms/lib/ins_rdbms.mk ioracle
s_unixOSDMakePath is nothing but make command

To verify options is enable/disable, enter:

bash-3.00$ cd $ORACLE_HOME/rdbms/lib
bash-3.00$ /usr/ccs/bin/ar -t libknlopt.a | grep -c kzlibac.o
1
bash-3.00$
where kzlibac.o denotes Oracle Label Security

If the output is 0, the option is disabled.
If the output is 1, the option is enabled.

The output are maintained in “$ORACLE_HOME/install/” path

bash-3.00$ pwd
/oracle11g/u01/app/oracle/product/11.2.0/install
bash-3.00$ ls -lrt
-rwxrwxr-x   1 ora11g   dba11     259989 Dec 16 21:21 make.log
-rw-r--r--   1 ora11g   dba11        190 Dec 29 18:47 enable_lbac.log
The installed component at the time of oracle home installation can be found in $ORACLE_HOME/install/make.log path.

How to Check and Enable/Disable Oracle Binary Options [ID 948061.1]

How to Disable and Enable a Database vault Realm

By default, when we create a realm, it is enabled. We can disable a realm (for example, for system maintenance such as patch updates) and then enable it again afterward.

To disable or enable a realm:

a) In the Oracle Database Vault Administration page, select Realms.

b) In the Realms page, select the realm you want to disable or enable, and then select Edit.

c) In the Edit Realm page, under Status in the General section, select either Disabled or Enabled.

d) Click OK.

How To Uninstall Or Reinstall Database Vault in 11gR2

1) Stop Database Control, the listener and the database

2) Disable Database Vault :
chopt disable dv

3) Start the listener and the database.

4) Identify the DV_OWNER and DV_ACCTMGR users that were configured:
select unique GRANTEE from dba_role_privs
where GRANTED_ROLE in ('DV_ACCTMGR','DV_OWNER')
and grantee <> 'DVSYS';

5) Run script dvremov.sql as SYSDBA after setting parameter recyclebin to OFF:
conn / as sysdba
alter system set recyclebin=off scope=spfile;

shutdown immediate
startup
run ?/rdbms/admin/dvremov.sql

After this only DVSYS and DVF users and DV roles are removed, but the security admin and account manager accounts are not removed as they are considered custom database accounts. They have to be removed manually, use the usernames obtained in step 5. (take care not to drop any users that you may have granted these roles manually):

conn / as sysdba
drop user &DV_OWNER cascade;
drop user &DV_ACCTMGR cascade;

dvremov.sql just removes the DV components and does not affect in any way OLS.

6) Turn on recycle bin if it was turned off before


At this stage Database Vault is removed from the database and the binaries are relinked with dv_off. If Database Vault is not needed (all you wanted to do was to remove/uninstall it) then stop here and restart the database. If Database Vault needs to be enabled then do the following:

1) Stop Database Control, listener and database.

2) Enable DV :
chopt enable dv

3) Start the listener and the database

4) Run DBCA again to register Database Vault. If it is not possible to run the DBCA GUI then run dbca silently:
dbca -silent -configureDatabase
-sourceDB <source database sid>
[-sysDBAUserName <user name with SYSDBA privileges>
-sysDBAPassword <password for sysDBAUserName user name>]
-addDBOption DV,OMS
-dvUserName <DV owner>
-dvUserPassword <DV owner passwd>
-dvAccountManagerName <DV acctmgr>
-dvAccountManagerPassword <DV acctmgr passwd>

How To Uninstall Or Reinstall Database Vault in 11gR1 and 11.2.0.1

Sometimes you require to uninstall or reinstall the database vault.These Steps are valid for 11gR1 and 11.2.0.1 database.For 11gR2(11.2.0.2 & others) please check my another Blog How To Uninstall Or Reinstall Database Vault in 11gR2

1) Stop Database Control, the listener and the database

2) Disable Database Vault :
cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk dv_off ioracle

3) Start the listener and the database.

4) Disable the Database Vault triggers :
conn / as sysdba
alter trigger dvsys.dv_before_ddl_trg disable;
alter trigger dvsys.dv_after_ddl_trg disable;

5) Identify the DV_OWNER and DV_ACCTMGR users that were configured:
select unique GRANTEE from dba_role_privs
where GRANTED_ROLE in ('DV_ACCTMGR','DV_OWNER')
and grantee <> 'DVSYS';

6) Run script dvremov.sql as SYSDBA after setting parameter recyclebin to OFF:
Note: In 11.2.0.1 run the following statements before running dvremov.sql:

update dvsys.config$ set status=0;
commit;

conn / as sysdba
alter system set recyclebin=off scope=spfile;

shutdown immediate
startup

run ?/rdbms/admin/dvremov.sql

After this only DVSYS and DVF users and DV roles are removed, but the security admin and account manager accounts are not removed as they are considered custom database accounts. They have to be removed manually, use the usernames obtained in step 5. (take care not to drop any users that you may have granted these roles manually):

conn / as sysdba
drop user &DV_OWNER cascade;
drop user &DV_ACCTMGR cascade;

dvremov.sql just removes the DV components and does not affect in any way OLS.

7) Run dbms_network_acl_admin.drop_acl (In 11gR2 this is included in dvremov.sql) :
conn / as sysdba
exec DBMS_NETWORK_ACL_ADMIN.DROP_ACL ('/sys/acls/dvsys-network-privileges.xml');
commit;

8) Turn on recycle bin if it was turned off before


At this stage Database Vault is removed from the database and the binaries are relinked with dv_off. If Database Vault is not needed (all you wanted to do was to remove/uninstall it) then stop here and restart the database. If Database Vault needs to be enabled then do the following:

1) Stop Database Control, listener and database.

2) Enable DV :
sqlplus / as sysdba
shutdown immediate

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk dv_on ioracle

3) Start the listener and the database

4) Run DBCA again to register Database Vault. If it is not possible to run the DBCA GUI then run dbca silently:

dbca -silent -configureDatabase
-sourceDB <source database sid>
[-sysDBAUserName <user name with SYSDBA privileges>
-sysDBAPassword <password for sysDBAUserName user name>]
-addDBOption DV,OMS
-dvUserName <DV owner>
-dvUserPassword <DV owner passwd>
-dvAccountManagerName <DV acctmgr>
-dvAccountManagerPassword <DV acctmgr passwd>

Script to check the Database Vault Realms, Command Rules and Rule Sets

1) Listing the Database Vault realms: 

set linesize 2000
set lines 1000 pages 499
column realm_name format a40
column col1 format a30
column col2 format a30 Heading "Owner / Grantee "
column col3 format a30 Heading "Object Type/Rule Set Name"
column col4 format a30 Heading "Object Name/Auth Options"
break on realm_name skip 3

select * from (
SELECT realm_Name , 'protected objects' col5, owner col2 , object_type col3 ,object_name col4
FROM dvsys.dba_dv_realm_object
union
select REALM_NAME ,'authorizations' col5, GRANTEE col2 ,AUTH_RULE_SET_NAME col3 , AUTH_OPTIONS col4
from dvsys.dba_dv_realm_auth )
order by realm_name asc ,col5 desc
/


2) Listing the Database Vault command rules: 

set linesize 2000
set lines 1000 pages 499
column COMMAND format a30
column RULE_SET_NAME format a30
column OBJECT_OWNER format a30
column OBJECT_NAME format a30
column ENABLED format a30
column PRIVILEGE_SCOPE format a30
select * from dvsys.DBA_DV_COMMAND_RULE;


3) Listing the Database Vault rules and rule sets:

column RULE_SET_NAME format a30
column RULE_NAME format a50
column RULE_EXPR format a60
column ENABLED format a8
column RULE_ORDER format 9999
break on RULE_SET_NAME skip 3

select * from dvsys.DBA_DV_RULE_SET_RULE;

Script To List The Database Vault Realms, Command Rules And Rule Sets (Doc ID 1352556.1)

How to Enable and Disable Oracle Database vault

How to Enable the database vault in Oracle 11gR2

1) Stop the database, Database Control console process, and listener
SQL> SHUTDOWN IMMEDIATE
SQL> EXIT
$ emctl stop dbconsole
$ lsnrctl stop [listener_name]


2) Enable Oracle Database Vault as follows:
$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk dv_on lbac_on ioracle
or
chopt enable lbac
chopt enable dv


3) Restart the database, Database Control console process, and listener
SQL> STARTUP
SQL> EXIT
$ emctl start dbconsole
$ lsnrctl start [listener_name]


How to Disable the database vault in Oracle 11gR2

1) Stop the database, Database Control console process, and listener.
SQL> SHUTDOWN IMMEDIATE
SQL> EXIT
$ emctl stop dbconsole
$ lsnrctl stop


2) Disable Oracle Database Vault as follows:
$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk dv_off ioracle
or
chopt disable dv
chopt disable lbac

3) Restart the database, Database Control console process, and listener
SQL> STARTUP
SQL> EXIT
$ emctl start dbconsole
$ lsnrctl start [listener_name]

How to check the status of Database Vault

We can check the status of database vault either from the database or from Oracle home binaries


1) select * from v$option where parameter like '%Oracle Database Vault%';

PARAMETER                VALUE
------------------------- ----------
Oracle Database Vault     TRUE

TRUE means DV is enabled



2) $ cd $ORACLE_HOME/rdbms/lib
   $ ar -t libknlopt.a | grep dv

kzvndv.o shows that DV option is DISABLED
kzvidv.o shows that DV option is ENABLED



3) $ ar -t libknlopt.a | grep -c kzvidv.o

For IBM AIX
$ ar -X64 -t libknlopt.a | grep -c kzvidv.o

If the output is 0, the DV option is disabled.
If the output is 1, the DV option is enabled.

APP-FND-02704: Unable to alter user APPS to change password

Error while changing the APPS password using FNDCPASS after integrating Database vault with E-Business suite

FNDCPASS apps/apps 0 Y system/manager SYSTEM APPLSYS welcome123

[appltest@TEST ~]$ cat L3312766.log
+---------------------------------------------------------------------------+
Application Object Library: Version : 12.0.0
Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.
 module:
+---------------------------------------------------------------------------+
Current system time is 01-MAR-2014 09:58:07
+---------------------------------------------------------------------------+
Working...
APP-FND-02704: Unable to alter user APPS to change password.
+---------------------------------------------------------------------------+
Concurrent request completed
Current system time is 01-MAR-2014 09:58:35
+---------------------------------------------------------------------------+

Solution:
Use DBVACCTMGR instead of system

FNDCPASS apps/apps 0 Y DBVACCTMGR/DBV123 SYSTEM APPLSYS welcome123

opatch prereq CheckConflictAgainstOHWithDetail -ph ./

Issue: Error while checking the Conflict of the opatch
[oracle@TEST 17478415]$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 11.2.0.3.5
Copyright (c) 2013, Oracle Corporation.  All rights reserved.
PREREQ session
Oracle Home       : /data/oracle/db/tech_st/11.2.0
Central Inventory : /data/oracle/db/tech_st/11.2.0/admin/oui/CLONE_test/oraInventory
   from           : /data/oracle/db/tech_st/11.2.0/oraInst.loc
OPatch version    : 11.2.0.3.5
OUI version       : 11.2.0.3.0
Log file location : /data/oracle/db/tech_st/11.2.0/cfgtoollogs/opatch/opatch2014-03-27_08-27-56AM_1.log
Invoking prereq "checkconflictagainstohwithdetail"
Prereq "checkConflictAgainstOHWithDetail"not executed
Unable to create Patch Object.
Exception occured : PatchObject constructor: Input file "/data/oracle/17478415/etc/config/actions" or "/data/oracle/17478415/etc/config/inventory" does not exist.


Solution:
[oracle@TEST 17478415]$ export PATH=$PATH:/usr/ccs/bin
[oracle@TEST1 17478415]$ export PATH=$PATH:/data/oracle/db/tech_st/11.2.0/OPatch
[oracle@TEST 17478415]$ opatch prereq CheckConflictAmongPatchesWithDetail -phBaseDir /data/oracle/17478415
Oracle Interim Patch Installer version 11.2.0.3.5
Copyright (c) 2013, Oracle Corporation.  All rights reserved.
PREREQ session
Oracle Home       : /data/oracle/db/tech_st/11.2.0
Central Inventory : /data/oracle/db/tech_st/11.2.0/admin/oui/CLONE_test/oraInventory
   from           : /data/oracle/db/tech_st/11.2.0/oraInst.loc
OPatch version    : 11.2.0.3.5
OUI version       : 11.2.0.3.0
Log file location : /data/oracle/db/tech_st/11.2.0/cfgtoollogs/opatch/opatch2014-03-27_08-34-55AM_1.log
Invoking prereq "checkconflictamongpatcheswithdetail"
Prereq "checkConflictAmongPatchesWithDetail" passed.
OPatch succeeded.

Steps to apply CPUJan2014 patch

Here are the steps to apply CPUJan2014(17478415) patch

1) Set Environment
export PATH=$PATH:/usr/ccs/bin
export PATH=$PATH:/data/oracle/db/tech_st/11.2.0/OPatch
which opatch
/data/oracle/db/tech_st/11.2.0/OPatch

2) Download the CPU patch.Eg.,Download CPUJan2014 patch for 11.2.0.3

3) To apply CPUJan2014, OPatch utility version 11.2.0.1.0 or later to apply this patch. Oracle recommends that you use
the latest released OPatch 11.2, which is available for download from My Oracle Support patch 6880880 by selecting the 11.2.0.0.0 release

4) Shutdown the Application and Database

5) Backup the Database,Oracle Home,Inventory

6) Check Confliction
bash-3.2$ cd 17478415
bash-3.2$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./

7) Apply opatch
unzip p17478415_112030_<platform>.zip
cd 17478415
opatch napply -skip_subset -skip_duplicate

6) Post Installation
Loading Modified SQL Files into the Database
conn "/ as sysdba"
@?/rdbms/admin/catbundle.sql cpu apply

The catbundle.sql execution is reflected in the dba_registry_history view by a row associated with bundle series CPU.

8) Check the status from registry$history
select action,version,id,comments,bundle_series from registry$history where bundle_series like '%CPU%';

9) Compile Invalid objects by executing “utlrp.sql”

10) Check the following log files in $ORACLE_BASE/cfgtoollogs/catbundle for any errors:
catbundle_CPU_<database SID>_APPLY_<TIMESTAMP>.log
catbundle_CPU_<database SID>_GENERATE_<TIMESTAMP>.log

Upgrade EBS from 12.1.3 to 12.2.3

To upgrade EBS from 12.1.3 to 12.2.3 on Linux Server, we need to upgrade in two steps.Following are the documents which we should follow:

Step 1 : 
Upgrade from 12.1.3 to 12.2.0 with the following documentation
Note 1330701.1: Oracle E-Business Suite Installation and Upgrade Notes Release 12 (12.2) for Linux x86-64 (Doc ID 1330701.1)

Oracle E-Business Suite Upgrade Guide Release 12.0 and 12.1 to 12.2 Part No. E48839-02
http://docs.oracle.com/cd/V39571_01/current/acrobat/122upg12.pdf

Note 1349240.1: Database Preparation Guidelines for an E-Business Suite Release 12.2 Upgrade (Doc ID 1349240.1)


Step 2 : 
Upgrade from 12.2.0 to 12.2.3 which is currently the latest version of E-Business 12.2.x 
Note 1586214.1 Oracle E-Business Suite Release 12.2.3 Readme 

Check whether Patch Applied is CPU or PSU

It all started in January 2005 with Critical Patch Updates (CPU).  Then Patch Set Updates (PSU) were added as cumulative patches that included priority fixes as well as security fixes.  Starting from October 2012 onwards, Critical Patch Update (CPU) patches will now be called Security Patch Update (SPU) patches. The patches will still be released as part of the overall Oracle Critical Patch Update (CPU) Program.

Critical Patch Update (CPU) now refers to the overall release of security fixes each quarter rather than the cumulative database security patch for the quarter.  Think of the CPU as the overarching quarterly release and not as a single patch.

Patch Set Updates (PSU) are the same cumulative patches that include both the security fixes and priority fixes.  The key with PSUs is they are minor version upgrades (e.g., 11.2.0.1.1 to 11.2.0.1.2).  Once a PSU is applied, only PSUs can be applied in future quarters until the database is upgraded to a new base version.

Security Patch Update (SPU) terminology is introduced in the October 2012 Critical Patch Update as the term for the quarterly security patch. SPU patches are the same as previous CPU patches, just a new name.  For the database, SPUs can not be applied once PSUs have been applied until the database is upgraded to a new base version.

Bundle Patches are the quarterly patches for Windows and Exadata which include both the quarterly security patches as well as recommended fixes.

Check whether Patch Applied is CPU or PSU
lsinv -bugs_fixed | grep -i cpu

lsinv -bugs_fixed | grep -i spu

References: New Patch Nomenclature for Oracle Products [ID 1430923.1]

Download Oracle Audit Vault software

The latest Oracle Audit vault available is 12.1.1.3.0. If you want to install the Audit vault only(Not Firewall) then Download the latest 12.1.1.3.0 Audit Vault Server Software only. 

Steps to download the Oracle Audit Vault software:
a)Go to https://edelivery.oracle.com
b)Select Oracle Database and Plarform Linux x86-64
c)Select Oracle Audit Vault and Database Firewall 12.1.1 Media Pack for Linux x86-64 -> 12.1.1.3 ->Part Number B73420-04
d)Oracle Audit Vault and Database Firewall (12.1.1.3.0) -Server ->V43742-01->3.2G

Download only "Oracle Audit Vault and Database Firewall (12.1.1.3.0) - Server" (V43742-01) ISO file which is of 3.2GB and burn the ISO file into DVD.

Screenshots:

Oracle Audit Vault Pre-Install Requirements

Any user can install Oracle Audit Vault. You do not need administrative privileges to complete the installation.Here are the basic Oracle Audit Vault Pre-Install Hardware Requirements:

Hardware:   x86 64-bit Server compatible with Oracle Linux Release5.8 without OS
Memory:     Min 2 GB
Disk Space: Single hard drive 125 GB
Other:    1 NIC for Audit Vault Server

Script to find duplicate datafiles in Oracle

Query to Check duplicate datafiles in Oracle

select substr ( file_name, instr( file_name, '/', -1)) file_name, count(*)
from dba_data_files
group by substr ( file_name,instr( file_name, '/', -1))
having count(*) > 1
/

XDOloader jobs are extremely slow during EBS Upgrade from11i to 12.1.1

When attempting to upgrade from EBS 11i to R12.1.1 on Linux Server the upgrade patch was extremely slow while processing XDOloader jobs and the following error encountered in Alert log file.

WARNING: inbound connection timed out (ORA-3136)

Solution:

To implement the solution, please execute the following steps:

a - Stop upgrade

b - rename /dev/random to /dev/random_org for example

c - Create a link to /dev/urandom called /dev/random

d - Restart the upgrade

After R12 upgrade when we open the URL Login screen is not coming

After EBS 11i to R12 Upgrade when we open the URL login screen is not coming.
It is because we have SSO Implemented on our Prod 11i Application.

 Solution:

Cannot Login: mod_oc4j: Response status=499 and reason=Oracle SSO, but failed to get mod_osso global context. [ID 563940.1]

Issue while opening the form after R12 Installation

After Fresh Installation of Oracle R12 or After Very First Cloning on Linux Server If you face this issue. Check RPM openmotif21-2.1.30-11.EL5.i3861. If could be one of the reason of this issue.

Error in Logfile:

13/06/13 13:22:22.473 formsweb: 10.1.3.4.0 Started

13/06/13 13:24:40.576 formsweb: ListenerServlet init()

13/06/13 13:24:41.683 formsweb: Forms session <1> aborted: runtime process failed during startup with errors /d01/app/apps/tech_st/10.1.2/bin/frmweb: error while loading shared libraries: libXm.so.2: cannot open shared object file: No such file or directory

 13/06/13 13:24:41.684 formsweb: Forms session <1> exception stack trace:

oracle.forms.engine.RunformException: Forms session <1> failed during startup: no response from runtime process

        at oracle.forms.servlet.RunformProcess.connect(Unknown Source)

        at oracle.forms.servlet.RunformProcess.dataToRunform(Unknown Source)

        at oracle.forms.servlet.RunformSession.dataToRunform(Unknown Source)

        at oracle.forms.servlet.ListenerServlet.doPost(Unknown Source)

        at javax.servlet.http.HttpServlet.service(HttpServlet.java:763)

        at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)

        at com.evermind[Oracle Containers for J2EE 10g (10.1.3.4.0) ].server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:713)

        at com.evermind[Oracle Containers for J2EE 10g (10.1.3.4.0) ].server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:370)

        at com.evermind[Oracle Containers for J2EE 10g (10.1.3.4.0) ].server.http.HttpRequestHandler.doProcessRequest(HttpRequestHandler.java:871)

        at com.evermind[Oracle Containers for J2EE 10g (10.1.3.4.0) ].server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:453)

        at com.evermind[Oracle Containers for J2EE 10g (10.1.3.4.0) ].server.http.AJPRequestHandler.run(AJPRequestHandler.java:313)

        at com.evermind[Oracle Containers for J2EE 10g (10.1.3.4.0) ].server.http.AJPRequestHandler.run(AJPRequestHandler.java:199)

        at oracle.oc4j.network.ServerSocketReadHandler$SafeRunnable.run(ServerSocketReadHandler.java:260)

        at com.evermind[Oracle Containers for J2EE 10g (10.1.3.4.0) ].util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:303)

        at java.lang.Thread.run(Thread.java:619)

Solution: Check RPM openmotif21-2.1.30-11.EL5.i3861

              If missing install it

ASM Background Processes

Like normal database instances ASM instance too have the usual background processes like SMON, PMON, DBWr, CKPT and LGWr. In addition to that the ASM instance also have the following background processes,

RABL - Rebalancer: It opens all the device files as part of disk discovery and coordinates the ARB processes for rebalance activity.

ARBx - Actual Rebalancer: They perform the actual rebalancing activities. The number of ARBx processes depends on the ASM_POWER_LIMIT init parameter.

ASMB - ASM Bridge: This process is used to provide information to and from the Cluster Synchronization Service (CSS) used by ASM to manage the disk resources. Responsible to communicate the database Instance to the ASM Instance, provides the heartbeat to the ASM instance.

ORBn - Rebalance ASM data extent movements. There can be many of this at the same time.

OSMB - (ASM) Any database instance that is using an ASM disk group will contain a background process called OSMB. The OSMB process is responsible for communicating with the ASM instance..Helps to manage the drive storage.

ORA-29874 DRG-10595 DRG-00100 - During Autoconfig Oracle R12

During autoconfig got below error
Error:
Enter value for 1: Enter value for 2: Enter value for 3: Connected.
DECLARE
*
ERROR at line 1:
ORA-29874: warning in the execution of ODCIINDEXALTER routine
ORA-29960: line 1,
DRG-10595: ALTER INDEX JTF_AMV_ITEMS_URL_CTX failed
DRG-00100: internal error, arguments : [51021],[drwaf.c],[1615],[],[]
ORA-06512: at "SYS.DBMS_SQL", line 1199
ORA-06512: at line 96


Solution:
Autoconfig Failed On Apps Tier "jtfictx.sh INSTE8_PRF" . Running manually "jtfictx.sh " 

Failed on "DRG-10758: index owner does not have the privilege" [ID 1357760.1]

1. Apply the note 1081227.1
   (Recommended) Create a new role, grant it to the owners of the impacted indexes, and 

set file_access_role to the new role.
- or -
2. Set file_access_role to 'public'

To change file_access_role, you must be connected as SYSDBA.

SQL> connect as sysdba
SQL> exec ctxsys.ctx_adm.set_parameter('file_access_role', 'public')
3. Rerun Autoconfig on the APPS tier

How to Kill oracle datapump export job

In oracle expdp and impdp we can Kill job at any moment.Below are the steps how can we kill the datapump job.

17:18:37 dev > select * from dba_datapump_jobs;

OWNER_NAME JOB_NAME OPERATION
------------------------------ ------------------------------ ------------------------------
JOB_MODE STATE DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
------------------------------ ------------------------------ ---------- ----------------- -----------------
SYSTEM SYS_EXPORT_FULL_01 EXPORT
FULL EXECUTING 1 0 2


17:19:11 dev > !ps -ef | grep expdp
orauat 1945838 2142428 0 17:19:25 pts/0 0:00 grep expdp



$ expdp system/manager attach=SYSTEM.SYS_EXPORT_FULL_01

Export: Release 11.2.0.1.0 - Production on Fri Dec 9 17:23:04 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

Job: SYS_EXPORT_FULL_01
Owner: SYSTEM
Operation: EXPORT
Creator Privs: TRUE
GUID: B3A5B7EACDCC30C8E0430A0B014030C8
Start Time: Friday, 09 December, 2011 14:09:19
Mode: FULL
Instance: dev
Max Parallelism: 1
EXPORT Job Parameters:
Parameter Name Parameter Value:
CLIENT_COMMAND system/******** directory=EXP_dev dumpfile=fulldp_c lone_091211_system.dmp logfile=fulldp_dev_091211_system.log full=y
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /orabackup/EXP_dev/fulldp_dev_091211_system.dmp
bytes written: 4,096

Worker 1 Status:
Process Name: DW00
State: EXECUTING
Object Schema: APPS
Object Name: EAM_DEPT_APPROVERS_PUB
Object Type: DATABASE_EXPORT/SCHEMA/PACKAGE/PACKAGE_SPEC
Completed Objects: 11,280
Worker Parallelism: 1


Export> KILL_JOB
Are you sure you wish to stop this job ([yes]/no): yes