Unexpected SYSAUX tablespace growth because of WRH$_LATCH_CHILDREN

For a while , I have seen unexpected growth at SYSAUX tablespace . When listed objects ordered by size , WRH$_LATCH_CHILDREN table is on the top and as a result of examination , it was caused from statistics_level parameter. For a performance issue i have changed this parameter from TYPICAL to ALL to get more detail information . But after performance issue resolved , i have not changed parameter back and it affected tablespace SYSAUX increased to bigger values.

After changing parameter to old value "TYPICAL" , growth decreased to expected values.


Not only statistics_level parameter can cause this issue according to metalink note, also low snapshot interval can cause. For interval 60 minutes is ideal.

MapViewer Runtime Failure After Upgrading BI from Release 11.1.1.6 to Release 11.1.1.7

After upgrade of current OBIEE to 11.1.1.7 from 11.1.1.6 when trying to access mapviewer URL (http://localhost:7001/mapviewer  below error occured.

"HTTP 500 Internal Server Error" 

Mapviewer was deployed into bi_server1 and related logfile were examined located at $DOMAIN_HOME/bifoundation_domain/servers/bi_server1/logs. Log files had entries like

WatchRuleType: Log
WatchRule: (SEVERITY = 'Error') AND ((MSGID = 'WL-101020') OR (MSGID = 'WL-101017') OR (MSGID = 'WL-000802') OR (MSGID = 'BEA-101020') OR (MSGID = 'BEA-101017') OR (MSGID = 'BEA-000802'))
WatchData: DATE = Dec 5, 2014 8:49:56 PM EET SERVER = bi_server1 MESSAGE = [ServletContext@1774584388[app:mapviewer module:/mapviewer path:/mapviewer spec-version:2.5 version:11.1.1]] Root cause of ServletException.
java.lang.NoClassDefFoundError: oracle/adfinternal/view/faces/taglib/html/HtmlHtmlTag
        at java.lang.Class.getDeclaredConstructors0(Native Method)
        at java.lang.Class.privateGetDeclaredConstructors(Class.java:2389)
        at java.lang.Class.getConstructor0(Class.java:2699)
        at java.lang.Class.newInstance0(Class.java:326)
        at java.lang.Class.newInstance(Class.java:308)

As a solution Oracle  suggests to delete some files of MapViewer deployment and delete the Weblogic internal cache of the MapViewer application. 

1. Stop Managed server (bi_server1)
2. Delete the following files from MapViewer deployment MW_HOME/Oracle_BI1/bifoundation/jee/mapviewer.ear

• web.war/WEB-INF/lib/adf-faces-api.jar
• web.war/WEB-INF/lib/adf-faces-impl.jar
• web.war/WEB-INF/lib/commons-digester.jar
• web.war/WEB-INF/lib/commons-logging-1.1.jar
• web.war/WEB-INF/lib/jsf-api.jar
• web.war/WEB-INF/lib/jsf-impl.jar
• web.war/WEB-INF/lib/jsr173_1.0_api.jar
• web.war/WEB-INF/lib/mvutil.jar
• web.war/WEB-INF/lib/xdb.jar
• web.war/WEB-INF/adf-faces-config.xml
• web.war/WEB-INF/orion-web.xml

 3. Delete the directory
 MW_HOME/user_projects/domains/bifoundation_domain/servers/bi_server1/tmp/_WL_user/mapviewer_11.1.1

4. Restart the managed server

Source 
http://docs.oracle.com/cd/E28280_01/doc.1111/e14770/biee.htm#CHDJIDBD

Tips & Tricks 4 : Conditional index

Client asked me if it is possible to create unique index on any table on condition. For example only 1 record is acceptable for any document which has active_flag as '1'. For active_flag '0' there can be more than 1 record.

In order to do this  we will create unique index with condition

SQL> create table t1 (document_id number,active_flag char(1));

Table created.

SQL> create unique index t1_idx on t1(case when active_flag='1' then document_id else null end);



For active_flag as '1' index will be populated with document_id , and for '0' not.
 
SQL> insert into t1 values (1,'1');

1 row created.

SQL> insert into t1 values (2,'1');

1 row created.

SQL> insert into t1 values (1,'0');

1 row created.

SQL> insert into t1 values (1,'0');

1 row created. -- able to insert same record

SQL> insert into t1 values (1,'1');
insert into t1 values (1,'1')
*
ERROR at line 1:
ORA-00001: unique constraint (TEST.T1_IDX) violated

-- Not able to insert same record for active_flag '1' value .

Tips & Tricks 3 : Exclude table ,schema from Expdp

From command line if you would like to exclude table , tables or schema following command can be used .

For specific table(s)

expdp system/pass directory=dp_dir dumpfile=sample.dmp exclude=TABLE:\ "IN \(\'TAB1\',\'TAB2\',\'TAB3\'\)\"

For schema(s)

expdp system/pass directory=dp_dir dumpfile=sample.dmp exclude=SCHEMA:\ "IN \(\'SCHEMA_NAME1\',\'SCHEMA_NAME2\'\)\"

Tips & Tricks 2 : Disable autostart of High Availability Service

Whenever  any components like ASM,listener,database is down, or when server restarted, component called HAS (High Availability Service) restarts the failed components and bring them up. This is done automatically. Sometimes it is not wanted to start itself automatically. In order to disable these property below command is executed ,

[root@test01 bin]# ./crsctl disable has
CRS-4621: Oracle High Availability Services autostart is disabled.
[root@test01 bin]#

http://docs.oracle.com/cd/E18283_01/server.112/e17120/restart004.htm#CIHGFJEB

Tips & Tricks 1 : Give debug privilege to user

Sometimes small tips and tricks may be useful for administrating db, apps etc... I would like to add related posts into my blog contains tips and tricks.

Tips & Tricks 1 : Give debug privilege to user.

Any user with whom you need debug procedure,function or package , related user must have

DEBUG CONNECT SESSION

DEBUG ANY PROCEDURE

privileges.

In order to give these ,

grant DEBUG CONNECT SESSION userx;

grant DEBUG ANY PROCEDURE userx

UPGAST-00224:The specified database does not contain any schemas for Oracle MDS ...

From Oracle Error Message documentation,

UPGAST-00224: The specified database does not contain any schemas for {0} or the database user lacks privilege to view the schemas.

Cause: The database you have specified does not contain any schemas registered as belonging to the component you are upgrading, or else the current database user lacks privilege to query the contents of the schema version registry.

Action: Verify that the database contains schema entries in schema version registry. If it does not, specify a different database. Verify that the user has DBA privilege. Connect to the database as DBA.

Level: 1

Type: ERROR

Impact: Upgrade

While upgrading OBIEE from 11.1.1.6 to 11.1.1.7 i have encountered UPGAST-00224 error at stage of PSA for the update of MDS schema .

I was doing upgrade process at TEST enviroment and related MDS + BIPLATFORM schemas have been imported from LIVE database to TEST database by changing schema names in order to sepearate TEST and LIVE . My original schema names were OBI_MDS and OBI_BIPLATFORM  and changed to OBIUPG_MDS ,OBIUPG_BIPLATFORM After done this process system.SCHEMA_VERSION_REGISTRY$ must be updated with new schema information.

To do this , system.SCHEMA_VERSION_REGISTRY$ can be exported and imported from live into TEST , or related MDS and BIPLATFORM data can be inserted or updated  at TEST enviroment.