Today I got a ticket from a developer where he claimed Oracle has a bug somewhere. The reason for this claim was a statement similar to
SELECT *
FROM table1
WHERE tab1col1 IN
( SELECT tab1col1
FROM table2)
This query returned rows.
But when he run the inner query on it's own, he received
ORA-00904: "TAB1COL1": invalid identifier
00904. 00000 - "%s: invalid identifier"
as there is no column TAB1COL1 in TABLE2.
So why should there be any result for the full query when part of it fails already? Is oracle somehow ignoring the query on table2 totally due to some errors in optimization?
After some research and a 10053 trace (yes, it was not obvious for me) I saw Oracle doing a Cost‑based predicate pushdown (JPPD) and translates it to something like
AND EXISTS (SELECT 0 FROM "TABLE2" WHERE "TABLE1"."TAB1COL1"="TABLE1"."TAB1COL1")
With this hint (for me, not a /*+ syntax thing) it was obvious.
So my proposal was to be more specific and use alias like this:
SELECT g1.*
FROM table1 t1
WHERE t1.tab1col1 IN
( SELECT t2.tab1col1
FROM table2 t2)
to generate the expected ORA-904
2016-02-10
2016-02-08
access to CHM raw data - without manipulating the -MGMTDB
In Version 12.1 Oracle introduced the Grid Infrastructure Management Repository (GIMR) called ‑MGMTDB.
This self managed pluggable database is a required component of Grid Infrastructure and should never require direct interactions. (there can be some interactions when you want to migrate to different diskgroups, but also those activities are covered within wrapper scripts provided by Oracle).
Every interaction with the data stored in this DB is done through applications - oclumon might be the best know. (If you prefer a graphical interface, you can check chmosg instead). Just as these are interfaces for the data stored on CHM user, Oracle does not provide any information how to connect to the database directly and query the data.
Of course I can always do a bequeath connection to the CDB, do a
and query the tables directly. But sometimes I prefer to do queries from my PC, using sqldeveloper. So I was searching for CHMs password.
The probably most complicated way is to identify where the password could be stored. After some internet research I was sure it would be worth to check for a wallet stored in OCR. Even the docu does not show it, crsctl query wallet can show some details about a wallet called MGMTDB.
So I know there is a wallet and it contains 2 credentials, but crsctl does not provide their passwords - at least I did not find out how.
So I have to go the longer way:
First get the proper entry from OCR:
Then some transformation to get from
to a wallet which can be used by mkstore:
Not that complicated at all. Now it's mkstore to query the details:
It's quite similar to the previous result from crsctl query wallet - so we are nearly there!
Here I have the passwords I'm looking for!
To connect to the -MGMTDB I need the listener information:
With all those bits and pieces I am able to connect to CHM@-MGMTDB:
It's not very practical, especially as the pasword can change for several reasons without notification, but still for any deeper data analysis it's handy to access the tables directly.
This self managed pluggable database is a required component of Grid Infrastructure and should never require direct interactions. (there can be some interactions when you want to migrate to different diskgroups, but also those activities are covered within wrapper scripts provided by Oracle).
Every interaction with the data stored in this DB is done through applications - oclumon might be the best know. (If you prefer a graphical interface, you can check chmosg instead). Just as these are interfaces for the data stored on CHM user, Oracle does not provide any information how to connect to the database directly and query the data.
Of course I can always do a bequeath connection to the CDB, do a
alter session set container = <cluster_name>;
and query the tables directly. But sometimes I prefer to do queries from my PC, using sqldeveloper. So I was searching for CHMs password.
The probably most complicated way is to identify where the password could be stored. After some internet research I was sure it would be worth to check for a wallet stored in OCR. Even the docu does not show it, crsctl query wallet can show some details about a wallet called MGMTDB.
crsctl query wallet -type MGMTDB -all CRS-10252: Aliases present in the wallet 'MGMTDB' are: CHM PCMRADMIN
So I know there is a wallet and it contains 2 credentials, but crsctl does not provide their passwords - at least I did not find out how.
So I have to go the longer way:
First get the proper entry from OCR:
ocrdump -keyname SYSTEM.WALLET.MGMTDB -xml /tmp/ocrdump.SYSTEM.WALLET.MGMTDB.xml
Then some transformation to get from
<name>SYSTEM.WALLET.MGMTDB</name> <value_type>BYTESTREAM (16)</value_type> <value><![CDATA[a1f84e370000000600000021............. </value>
to a wallet which can be used by mkstore:
echo "cat //OCRDUMP/KEY/VALUE/text()" | \ xmllint --nocdata --shell /tmp/ocrdump.SYSTEM.WALLET.MGMTDB.xml | \ head -n -1 | tail -n -1 | \ xxd -r -p > /tmp/ocrdump.SYSTEM.WALLET.MGMTDB.bin
Not that complicated at all. Now it's mkstore to query the details:
mkstore -wrl /tmp/ocrdump.SYSTEM.WALLET.MGMTDB.bin -list Oracle Secret Store Tool : Version 12.1.0.2 Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved. Oracle Secret Store entries: CHM PCMRADMIN
It's quite similar to the previous result from crsctl query wallet - so we are nearly there!
mkstore -wrl /tmp/ocrdump.SYSTEM.WALLET.MGMTDB.bin \ -viewEntry CHM Oracle Secret Store Tool : Version 12.1.0.2 Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved. CHM = cRlu7yvFd7gZoYmqEl2Ye6jx143Iji mkstore -wrl /tmp/ocrdump.SYSTEM.WALLET.MGMTDB.bin \ -viewEntry PCMRADMIN Oracle Secret Store Tool : Version 12.1.0.2 Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved. PCMRADMIN = mfqO8gPGFfzQZWPgyZhwO0pZk8zgSu
Here I have the passwords I'm looking for!
To connect to the -MGMTDB I need the listener information:
lsnrctl status MGMTLSNR LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 08-FEB-2016 10:52:00 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=MGMTLSNR))) STATUS of the LISTENER ------------------------ Alias MGMTLSNR Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 24-JAN-2016 11:21:26 Uptime 14 days 23 hr. 30 min. 33 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /some_path/grid/grid_12102/network/admin/listener.ora Listener Log File /some_path/logs/grid/diag/tnslsnr/av3l958t/mgmtlsnr/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=MGMTLSNR))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=1.2.3.4)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=5.6.7.8)(PORT=1521))) Services Summary... Service "-MGMTDBXDB" has 1 instance(s). Instance "-MGMTDB", status READY, has 1 handler(s) for this service... Service "_mgmtdb" has 1 instance(s). Instance "-MGMTDB", status READY, has 1 handler(s) for this service... Service "crs908" has 1 instance(s). Instance "-MGMTDB", status READY, has 1 handler(s) for this service... The command completed successfully
With all those bits and pieces I am able to connect to CHM@-MGMTDB:
CHM/cRlu7yvFd7gZoYmqEl2Ye6jx143Iji@1.2.3.4:1521/crs908
It's not very practical, especially as the pasword can change for several reasons without notification, but still for any deeper data analysis it's handy to access the tables directly.
Abonnieren
Posts (Atom)