Few days ago, I was installing OEM agent version 126.96.36.199.0 on my DEV box with OS AIX version 7.2. I was using silent method using agentDeploy.sh.
Previously I had installed it successfully on multiple machines without any issues but this one failed with below error:
java.io.IOException: OUI-10039:Unable to access the inventory /u01/app/oraInventory on this system. Please ensure you have the proper permissions to read/write/search the inventory.
I tried to see if there are any permissions issue on folder /u01/app/oraInventory, I found mentioned directory was not present. So this error is expected one. But why Oracle is searching inventory at incorrect location???
When I dug into associated log file, I found more details about these errors.
2019-10-07 12:20:48,465 WARNING  oracle.sysman.oii.oiip.oiipg.OiipgPropertyLoader - The inventory pointer location /var/opt/oracle/oraInst.loc is either not readable or does not exist2019-10-07 12:20:48,475 INFO  oracle.sysman.nextgen.utils.NextGenInventoryUtil - Setting default inventory location to: '/u01/app/oraInventory'2019-10-07 12:20:48,475 WARNING  oracle.sysman.oii.oiip.oiipg.OiipgPropertyLoader - The inventory pointer location /var/opt/oracle/oraInst.loc is either not readable or does not exist2019-10-07 12:20:48,475 WARNING  oracle.sysman.oii.oiip.oiipg.OiipgPropertyLoader - The inventory pointer location /var/opt/oracle/oraInst.loc is either not readable or does not exist2019-10-07 12:20:48,477 SEVERE  oracle.sysman.oii.oiii.OiiiInstallAreaControl - OUI-10039:Unable to access the inventory /u01/app/oraInventory on this system. Please ensure you have the proper permissions to read/write/search the inventory.2019-10-07 12:20:48,477 SEVERE  oracle.sysman.nextgen.impl.NextGenInstallerImpl - java.io.IOException: OUI-10039:Unable to access the inventory /u01/app/oraInventory on this system. Please ensure you have the proper permissions to read/write/search the inventory.
So basically, Oracle tries to check oraInst.loc under folder /var/opt/oracle & if it doesn’t find any, then it sets default inventory location to ‘/u01/app/oraInventory’.
I feel, This is somewhat agent software BUG, as loaction of oraInst.loc in AIX is ‘/etc’ not ‘/var/opt/oracle’.
Now there are two questions. First one, how to resolve this & second one, why my other installations went successful.
Reason behind other installation to be successful was Oracle indeed find oraInventory at its default location. So whenever oraInventory is located at ‘/u01/app/oraInventory’, you wont face this issue.
Now how to resolve this. You can always create softlink oraInst.loc to “/var/opt/oracle”.
Steps are given below:
Login using root:
1. mkdir -p /var/opt/oracle/
2. cd /var/opt/oracle/
3. ln -s /etc/oraInst.loc oraInst.loc
4. ls -lrt oraInst.loc
Once you perform above steps, you will be install OEM agent successfully.
This job was intended to drop temporary tables in application schema. We had written a shell script in which SYS user executes procedure owned by application schema.
Only thing that was changed at DB end, that DB was upgraded from 11g to 12c.
After investigating further on the error, I found this was due to a new 12c security feature.
Before Oracle Database 12c, a PL/SQL code/pacakge/procedure always ran with the privileges of its invoker. If its invoker had higher privileges than its owner, then the code might perform operations unintended by, or forbidden to, its owner. Here we can see security gap.
For example, User A creates a new package and we execute it from users with higher privileges, like SYS. Now user A knows that SYS uses this package regularly, so user A could replace the contents of this package with some malacious code any time and do anything in the database, knowing the code will be ran by SYS sooner or later.
In 12c this behavior can be controlled using INHERITANCE PRIVILEGES.
As of Oracle Database 12c, a PL/SQL code/pacakge/procedure can run with the privileges of its invoker only if its owner has either the INHERIT PRIVILEGES privilege on the invoker or the INHERIT ANY PRIVILEGES privilege.
I was able to resolve the issue after issuing below command:
SQL> grant inherit privileges on user sys to <application schema>;
Moving to next portion of syntax, which lets you controls histogram creation and its type.
Size clause has different options as given below:
You can specify number of buckets. It must be in range of 1 to 254. 1 denotes no histogram with base column statistics (i.e. minimal information required for the Optimizer) while 254 is highest allowable value.
Oracle Histogram is very nice feature to help CBO to make right decision while constructing execution plan. But many of DBA’s do not use it, assuming it is complex concept to use. In next few posts, I will try to simplify it, so that we can achieve maximum benefit from it.
What it Histogram & what is its purpose:
A histogram is a special type of column statistic that provides more detailed information about the data distribution in a table column. In simple words, it is a feature in CBO and it helps optimizer to determine how data are distributed/skewed within column. Predicates with skewed data are good candidates for histogram creation.
Basically CBO assumes uniform data distribution of data inside column across the distinct values. For the columns that contain skewed data, histogram helps to generate more accurate execution plans.
Below example will explain you significance of histograms:
I have a table ADI.SALES_ORDER with 100000rows with index SALES_ORDER_N1 on column GENERATED.
GENERATEDcolumn has 4 distinct values: A, B, Y, N. CBO will assume uniform data distribution across these values. So any query with column GENERATED as predicate will go for FTS, as CBO will assume it has to select 25% data from table.
But in reality data under column generated in skewed as given below:
I gathered fresh statistics without histogram on table as shown below:
Now will compare execution plan with different values for predicate:
As expected this query is going for FTS, as it has to select approx. 80% of data.
But I will expect query to pick index in case of GENERATED=’N’ as it will just select less than 1% of rows from table.
But NO its still going for FTS 😦 & it is due to CBO’s default behavior.
Now here Histogram should come into picture:
Now I will once again compare plans:
As expected following query is going for FTS, as it has to select approx. 80% of data.
But following query has picked up index due to creation of histogram. 🙂
In this post, we focused on what is histogram & why it is used.
Recently I was performing database reorg operation on one of pre-prod environment with traditional way of table/lob movement followed by index rebuild. After moving all segments from current tablespace to new, I was ready to drop original one.
SQL> select count(0) from dba_segments where tablespace_name='MY_TABLESPACE';
And it errored out!!!
SQL> DROP TABLESPACE MY_TABLESPACE INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE MY_TABLESPACE INCLUDING CONTENTS AND DATAFILES
*ERROR at line 1:
ORA-14404: partitioned table contains partitions in a different tablespace
As first step, I tried purging dba_recyclebin, but it didn’t help.
1. An attempt was made to drop a tablespace which contains tables whose partitions or subpartitions are not completely contained in this tablespace.
2. This can happen due to deferred segment creation introduced in 11.2.
First cause was matching my case:
I tried finding out tables whose partitions were spread across other partitions along with tablespace MY_TABLESPACE & moved to it to new tablespace as well:
SELECT 'alter table '|| table_owner|| '.'|| table_name|| ' move partition '|| partition_name|| ' tablespace MY_TABLESPACE_NEW;' FROM (SELECT DISTINCT table_name, partition_name FROM dba_tab_partitions WHERE tablespace_name = 'MY_TABLESPACE') a, dba_tab_partitions b WHERE b.tablespace_name <> 'MY_TABLESPACE' AND a.table_name = b.table_name;
After moving all affected table partitions, I once again tried dropping tablespace: MY_TABLESPACE. It once again failed with same error: ORA-14404.
Then I found same issue with some of index partitions as well, for which I used following command:
Once indexes were rebuild, I was able to drop tablespace MY_TABLESPACE without any issues 🙂
Note: You may also get ORA-14407 is some cases, wherein sub-partitions are the issue, for which you will use above commands just with sub-partitions views.
Just to elaborate seconds possible cause of this error: deferred segment creation:
The Oracle Database 11.2 includes a new space allocation method. When you create a non-partitioned heap table, the table segment creation is deferred to the first row insert. This functionality is enabled by default with the initialization parameter DEFERRED_SEGMENT_CREATION set to TRUE.So in simple words, if you have set this parameter to value TRUE, particular table will be populated in dba_tables once created, but will not be populated in dba_segments unless we insert a row to it. So in the case of defered segments, you will not be able to drop tablespace.