I have just released Kafka Oracle source connector , in order to import changed data from Oracle database to Kafka. Main logic is based on Oracle Logminer solution and all details and instructions can be found at following URL
stuff about db,apps ...
This blog is related with mostly Oracle database , Oracle EBS and other Oracle tools . I will also try to share my experiences about other databases. Thanks for reading this blog and your feedback is appreciated.
Thursday, August 9, 2018
Thursday, April 19, 2018
Create Materialized View Fails With ORA-30372
ORA-30372 error has been declared when user would like to refresh materialized view . Detailed explanation of error ,
Oracle Error: ORA-30372
Error Description:
Fine grain access policy conflicts with materialized view
Error Cause:
A fine grain access control procedure has applied a non-null policy to the query for the materialized view.
Action:
In order for the materialized view to work correctly, any fine grain access control procedure in effect for the query must return a null policy when the materialized view is being created or refreshed. This may be done by ensuring that the usernames for the creator, owner, and invoker of refresh procedures for the materialized view all receive a null policy by the user-written fine grain access control procedures.
If any table used in materialized view has VPD policy should return null while refreshing. This causes error and in order to solve this Oracle suggests to define policy function to user which will do operations to return NULL policy during create or refresh operations on materialized view.But it can cause security issues for this user who has some data access restrictions.
There is another solution .Following code can be put into security policy function and only while refreshing policy returns null.
if dbms_mview.i_am_a_refresh then
return null;
end if;
Wednesday, April 4, 2018
SQL Profile ignores hints
Sometimes , hints can be very powerful to let optimizer choose proper execution plans. This lets application execute with good performance. But sometimes given hints to sqls can be harmful choosing wrong execution plans.At this situations , development can take care of this sqls and by removing hints problems can be solved. But if development process can not be done immediately . DBA can solve this issues by telling optimizer to ignore hints.
alter session set "_optimizer_ignore_hints"=true
command ignores all hints during session. This can solve your problem but also can be trouble.At sql level ignoring hints can be done by import_sql_profile procedure in dbms_sqltune package.
For sample below sql is used . Firstly given hint for sql tell optimizer not to use index. But for some reason hint losed its validity and index should be used for query.
For sample below sql is used . Firstly given hint for sql tell optimizer not to use index. But for some reason hint losed its validity and index should be used for query.
SQL> explain plan for select /*+no_index(s) */ count(1) from xdba_dcyear s where cust_name like 'ER%';
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3491557762
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 252 | 2 (0)| 0
| 1 | SORT AGGREGATE | | 1 | 252 | |
|* 2 | TABLE ACCESS FULL| XDBA_DCYEAR | 20 | 5040 | 2 (0)| 0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("CUST_NAME" LIKE 'ER%')
Note
-----
- dynamic sampling used for this statement (level=2)
18 rows selected
Executed in 0.076 seconds
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3491557762
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 252 | 2 (0)| 0
| 1 | SORT AGGREGATE | | 1 | 252 | |
|* 2 | TABLE ACCESS FULL| XDBA_DCYEAR | 20 | 5040 | 2 (0)| 0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("CUST_NAME" LIKE 'ER%')
Note
-----
- dynamic sampling used for this statement (level=2)
18 rows selected
Executed in 0.076 seconds
Now dbms_sqltune.import_sql_profile procudure is used with attribute "IGNORE_OPTIM_EMBEDDED_HINTS".
begin
dbms_sqltune.import_sql_profile(
name => 'SQLPROF1',
category => 'DEFAULT',
sql_text => 'select /*+no_index(s) */ count(1) from xdba_dim_cust_year s where cust_name like ''ER%''',
profile => sqlprof_attr('IGNORE_OPTIM_EMBEDDED_HINTS'));
end;
/
SQL> explain plan for select /*+no_index(s) */ count(1) from xdba_dcyear s where cust_name like 'ER%';
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2253536563
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 252 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 252 | | |
|* 2 | INDEX RANGE SCAN| X1 | 20 | 5040 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CUST_NAME" LIKE 'ER%')
filter("CUST_NAME" LIKE 'ER%')
Note
-----
- dynamic sampling used for this statement (level=2)
- SQL profile "SQLPROF1" used for this statement
20 rows selected
Executed in 0.126 seconds
As a result no_index hint is ignored and index is being used according to explain plan. Also usage of "SQLPROF1" profile can be observe from explain plan notesdbms_sqltune.import_sql_profile(
name => 'SQLPROF1',
category => 'DEFAULT',
sql_text => 'select /*+no_index(s) */ count(1) from xdba_dim_cust_year s where cust_name like ''ER%''',
profile => sqlprof_attr('IGNORE_OPTIM_EMBEDDED_HINTS'));
end;
/
SQL> explain plan for select /*+no_index(s) */ count(1) from xdba_dcyear s where cust_name like 'ER%';
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2253536563
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 252 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 252 | | |
|* 2 | INDEX RANGE SCAN| X1 | 20 | 5040 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CUST_NAME" LIKE 'ER%')
filter("CUST_NAME" LIKE 'ER%')
Note
-----
- dynamic sampling used for this statement (level=2)
- SQL profile "SQLPROF1" used for this statement
20 rows selected
Executed in 0.126 seconds
In order to remove sql profile following procedure is used.
begin
dbms_sqltune.import.drop_sql_profile('SQLPROF1');
end;
/
dbms_sqltune.import.drop_sql_profile('SQLPROF1');
end;
/
Note : dbms_sqltune package is under Oracle Tuning Pack license.This must be considered.
Wednesday, March 21, 2018
CRS-4228: Value of attribute ')' is missing
After changing disk group of database which is configured on cluster ready service , start and stop dependency attributes configured for old disk group must be changed in order to provide proper configuration.
$ crsctl status res ora.test.db -p
NAME=ora.test.db
TYPE=ora.database.type
ACL=owner:oracle:rwx,pgrp:oinstall:rwx,other::r--
...
START_DEPENDENCIES=hard(ora.TESTRECO.dg,ora.TESTDATADG.dg) weak(type:ora.listener.type,global:type:ora.scan_listener.type,uniform:ora.ons,global:ora.gns) pullup(ora.TESTRECO.dg,ora.TESTDATADG.dg)
STOP_DEPENDENCIES=hard(intermediate:ora.asm,shutdown:ora.TESTRECO.dg,shutdown:ora.TESTDATADG.dg)
...
Disk group TESTDATADG is not using anymore and all data files have been moved another disk group named TESTDATADG2.
Change the attributes related with old disk to indicate new disk group name.
$ crsctl modify res ora.test.db -attr START_DEPENDENCIES="hard(ora.TESTRECO.dg.ora, ora.TESTDATADG2.dg).........."
gives CRS-4228 error.
The comma is used as a delimiter between attributes , so string after comma "ora.TESTDATADG2.dg)" is viewed as resource which does not exist actually. In order to prevent this error , single quotes is used between atrribute value like this,
$ crsctl modify res ora.test.db -attr START_DEPENDENCIES="'hard(ora.TESTRECO.dg.ora, ora.TESTDATADG2.dg)..........'"
Thursday, March 15, 2018
SOA Gateway soagenerate.sh gives error server.java.io.FileNotFoundException
After clone of Live E-Business System existing web services on SOA Gateway should be regenerated and redeployed to use them for test purposes. In order to do this , a file is prepared which contains all web service interface names line by line.
vi /tmp/services.lst
XX_TEST_PKG
XX_TEST2_PKG
soagenerate.sh under $FND_TOP/bin is executed to generate WSDLs
$FND_TOP/bin/soagenerate.sh
Enter the single or multiple generate as S/M :
M
Enter the filename of the interface to be generated :
/tmp/services.lst
If you encounter following error ,
Wed Feb 21 07:54:56 EET 2018 : oracle.apps.fnd.soa.provider.DBConnection.getJDBCConnection : Obtained new Jdbc Connection via WebAppsContext
Wed Feb 21 07:54:56 EET 2018 : oracle.apps.fnd.soa.provider.DBConnection.getJDBCConnection : Obtained new Jdbc Connection via WebAppsContext
Wed Feb 21 07:54:56 EET 2018 : oracle.apps.fnd.soa.provider.util.IRepAccess.getClassId : Class Id for irep_name XX_TEST_PKG = 4283
Wed Feb 21 07:54:56 EET 2018 : oracle.apps.fnd.soa.ui.ProviderAccess.generateWSDL : Generating Service : 4283
Wed Feb 21 07:54:56 EET 2018 : oracle.apps.fnd.soa.provider.util.ServerAccess.sendMessageToServer : Sending Message : Generate
Wed Feb 21 07:54:56 EET 2018 : oracle.apps.fnd.soa.provider.util.ServerAccess.sendMessageToServer : For Class Id : 4283
Wed Feb 21 07:54:56 EET 2018 : oracle.apps.fnd.soa.provider.util.ServerAccess.sendMessageToServer : Encoded ClassId : 4283
Wed Feb 21 07:54:56 EET 2018 : oracle.apps.fnd.soa.provider.util.ServerAccess.sendMessageToServer : SOA_SERVER_URL : http://ebstest.localdomain.com.tr:8010
Wed Feb 21 07:54:56 EET 2018 : oracle.apps.fnd.soa.provider.util.ServerAccess.sendMessageToServer : Host : http://ebstest.localdomain.com.tr:8010
Wed Feb 21 07:54:56 EET 2018 : oracle.apps.fnd.soa.provider.util.ServerAccess.sendMessageToServer : Port : 8010
Wed Feb 21 07:54:56 EET 2018 : oracle.apps.fnd.soa.provider.util.ServerAccess.sendMessageToServer : ticket is null : false
Wed Feb 21 07:54:56 EET 2018 : oracle.apps.fnd.soa.provider.util.ServerAccess.sendMessageToServer : Error while sending message to server.java.io.FileNotFoundException: http://ebstest.localdomain.com.tr:8010/webservice
s/SOAProvider/EbizAuth?Generate=4283&soa_ticket=EgsTINbmlZ7ofmxqvGD-gw..
you must reset ASADMIN password which explained before in this blog post
http://erdemcer.blogspot.com.tr/2014/04/oracle-ebs-soa-gateway-testquery-does.html
After resetting ASADMIN password , reexecute soagenerate.sh .
As last step execute re-deploy interfaces
$FND_TOP/bin/soadeploy.sh
Enter the single or multiple deploy as S/M :
M
Enter the filename of the interface to be deployed :
/tmp/services.lst
vi /tmp/services.lst
XX_TEST_PKG
XX_TEST2_PKG
soagenerate.sh under $FND_TOP/bin is executed to generate WSDLs
$FND_TOP/bin/soagenerate.sh
Enter the single or multiple generate as S/M :
M
Enter the filename of the interface to be generated :
/tmp/services.lst
If you encounter following error ,
Wed Feb 21 07:54:56 EET 2018 : oracle.apps.fnd.soa.provider.DBConnection.getJDBCConnection : Obtained new Jdbc Connection via WebAppsContext
Wed Feb 21 07:54:56 EET 2018 : oracle.apps.fnd.soa.provider.DBConnection.getJDBCConnection : Obtained new Jdbc Connection via WebAppsContext
Wed Feb 21 07:54:56 EET 2018 : oracle.apps.fnd.soa.provider.util.IRepAccess.getClassId : Class Id for irep_name XX_TEST_PKG = 4283
Wed Feb 21 07:54:56 EET 2018 : oracle.apps.fnd.soa.ui.ProviderAccess.generateWSDL : Generating Service : 4283
Wed Feb 21 07:54:56 EET 2018 : oracle.apps.fnd.soa.provider.util.ServerAccess.sendMessageToServer : Sending Message : Generate
Wed Feb 21 07:54:56 EET 2018 : oracle.apps.fnd.soa.provider.util.ServerAccess.sendMessageToServer : For Class Id : 4283
Wed Feb 21 07:54:56 EET 2018 : oracle.apps.fnd.soa.provider.util.ServerAccess.sendMessageToServer : Encoded ClassId : 4283
Wed Feb 21 07:54:56 EET 2018 : oracle.apps.fnd.soa.provider.util.ServerAccess.sendMessageToServer : SOA_SERVER_URL : http://ebstest.localdomain.com.tr:8010
Wed Feb 21 07:54:56 EET 2018 : oracle.apps.fnd.soa.provider.util.ServerAccess.sendMessageToServer : Host : http://ebstest.localdomain.com.tr:8010
Wed Feb 21 07:54:56 EET 2018 : oracle.apps.fnd.soa.provider.util.ServerAccess.sendMessageToServer : Port : 8010
Wed Feb 21 07:54:56 EET 2018 : oracle.apps.fnd.soa.provider.util.ServerAccess.sendMessageToServer : ticket is null : false
Wed Feb 21 07:54:56 EET 2018 : oracle.apps.fnd.soa.provider.util.ServerAccess.sendMessageToServer : Error while sending message to server.java.io.FileNotFoundException: http://ebstest.localdomain.com.tr:8010/webservice
s/SOAProvider/EbizAuth?Generate=4283&soa_ticket=EgsTINbmlZ7ofmxqvGD-gw..
you must reset ASADMIN password which explained before in this blog post
http://erdemcer.blogspot.com.tr/2014/04/oracle-ebs-soa-gateway-testquery-does.html
After resetting ASADMIN password , reexecute soagenerate.sh .
As last step execute re-deploy interfaces
$FND_TOP/bin/soadeploy.sh
Enter the single or multiple deploy as S/M :
M
Enter the filename of the interface to be deployed :
/tmp/services.lst
Monday, March 5, 2018
ORA-27154: post/wait create failed / ORA-27301: OS failure message: No space left on device
After SAN migration of database everything gone fine and system worked for 1 days successfully . But in an instant because of multipath configuration db has crashed due to dbwr failure.After fixing storage unit failure whenever would like to open database below error occured. Actually there was no configuration change on database but immediately following error occured.
ORA-27154: post/wait create failed
ORA-27300: OS system dependent operation:semget failed with status: 28
ORA-27301: OS failure message: No space left on device
ORA-27302: failure occurred at: sskgpcreates
Mon Mar 05 12:32:54 2018
According to notes inadequate setting of semaphore can cause not to accommodate the request. Setting semaphore to higher value resolved issue
In /etc/sysctl.conf
Before change
kernel.sem = 250 32000 100 128
After change
kernel.sem = 250 32000 100 256
After applying settings as root execute "sysctl -p" command
Friday, February 23, 2018
OBIEE Error ADF_FACES-60097 ADF_FACES-60096
When selecting "Coreapplication" in enterprise manager of OBIEE following error occured,
[2018-02-22T21:17:13.868+02:00] [AdminServer] [NOTIFICATION] [] [oracle.bi.management.adminservices] [tid: DmsThread-2] [userId: <WLS Kernel>] [ecid: 7326609a41c1cc90:101d0126:161bee9fb6b:-8000-000000000000007d,1:16628]
[APP: biadminservices#11.1.1] BIInstanceStatusMetricMBeanImpl; getServiceStatus: currentPrincipal is null
[2018-02-22T21:17:14.512+02:00] [AdminServer] [NOTIFICATION] [OBI-SYSMAN-1230] [oracle.bi.management.sysmancommon] [tid: DmsThread-2] [userId: <WLS Kernel>] [ecid: 7326609a41c1cc90:101d0126:161bee9fb6b:-8000-00000000000000
7d,1:16628] [APP: biadminservices#11.1.1] Summary of current system component statuses; Up: 6; Down: 0; Up(HA): 0; Down(HA): 0; Error: 0
[2018-02-22T21:17:14.514+02:00] [AdminServer] [NOTIFICATION] [] [oracle.bi.management.adminservices] [tid: DmsThread-3] [userId: <WLS Kernel>] [ecid: 7326609a41c1cc90:101d0126:161bee9fb6b:-8000-000000000000007d,1:16626] [A
PP: biadminservices#11.1.1] BIInstanceStatusMetricMBeanImpl; getServiceStatus: currentPrincipal is null
[2018-02-22T21:17:15.158+02:00] [AdminServer] [NOTIFICATION] [OBI-SYSMAN-1230] [oracle.bi.management.sysmancommon] [tid: DmsThread-3] [userId: <WLS Kernel>] [ecid: 7326609a41c1cc90:101d0126:161bee9fb6b:-8000-00000000000000
7d,1:16626] [APP: biadminservices#11.1.1] Summary of current system component statuses; Up: 6; Down: 0; Up(HA): 0; Down(HA): 0; Error: 0
[2018-02-22T21:17:15.236+02:00] [AdminServer] [WARNING] [] [oracle.mds] [tid: [ACTIVE].ExecuteThread: '6' for queue: 'weblogic.kernel.Default (self-tuning)'] [userId: weblogic] [ecid: 7326609a41c1cc90:101d0126:161bee9fb6b:
-8000-0000000000000137,0] [APP: em] A call to flushChanges on the current MDSSession does not specify the correct transaction key
[2018-02-22T21:17:15.241+02:00] [AdminServer] [ERROR] [ADFC-50018] [oracle.adfinternal.controller.application.AdfcExceptionHandler] [tid: [ACTIVE].ExecuteThread: '6' for queue: 'weblogic.kernel.Default (self-tuning)']
[userId: weblogic] [ecid: 7326609a41c1cc90:101d0126:161bee9fb6b:-8000-0000000000000137,0] [APP: em] ADFc: No exception handler was found for an application exception.
[[javax.faces.FacesException: java.io.IOException: Stream closed
[2018-02-22T21:17:15.243+02:00] [AdminServer]
[WARNING] [] [oracle.adfinternal.view.faces.lifecycle.LifecycleImpl] [tid: [ACTIVE].ExecuteThread: '6' for queue: 'weblogic.kernel.Default (self-tuning)'] [userId: weblogic] [ecid: 7326609a41c1cc90:101d0126:161bee9fb6b:-8000-0000000000000137,0] [APP: em] ADF_FACES-60098:Faces lifecycle receives unhandled exceptions in phase RENDER_RESPONSE 6[[javax.faces.FacesException: java.io.IOException: Stream closed
Error can be observed from AdminServer-diagnostic logs.
According to metalink if your system ran out of disk space in any time this corrupted some files and caused this issue.In order to solve this issue,
1.Stop all OBIEE services and weblogic server
2.Find 0 byte files under $DOMAIN_HOME/sysman/mds directory
cd $DOMAIN_HOME/sysman/mds
find . -name "*" -size 0
3.delete these files
4.Open weblogic and OBIEE services
Now "Coreapplication" should be accessible again and deleted files will be recreated.
Subscribe to:
Posts (Atom)