Archives: Calling Java procedure from database
Oracle Database has had the possibility to run Java code inside the database for a long time. It’s a very rare occasion when you need to use it but still. Here is one example I used to download content from HTTPS website that required user certificates for authentication. Please take the code below more as an example how to put simple Java code inside the database, not as a solution for user certificates authentication, because UTL_HTTP can do the same thing (although I wasn’t successful in implementing it under 184.108.40.206).
First, load the Java source into database. The code below shows:
- How to return simple datatype (int) from Java function – makeConnection
- How to return Oracle CLOB datatype from Java – makeConnectionClob
- How to execute SQL from Java, in the same calling session
Note that method main is just added for testing from command line.
Then you need to create a wrapper package in database. This declares the PL/SQL wrapper function names and input/output parameters.
When you first execute the code, you will most likely get some privilege errors, but the error message will tell you how to grant the needed privileges. For example, for this code the following grants were needed:
exec dbms_java.grant_permission( 'OWNER', 'SYS:java.util.PropertyPermission', 'javax.net.ssl.keyStore', 'write' ); exec dbms_java.grant_permission( 'OWNER', 'SYS:java.util.PropertyPermission', 'javax.net.ssl.trustStore', 'write' ); exec dbms_java.grant_permission( 'OWNER', 'SYS:java.util.PropertyPermission', 'javax.net.ssl.keyStorePassword', 'write' ); exec dbms_java.grant_permission( 'OWNER', 'SYS:java.net.SocketPermission', 'site.that.requires.user.cert', 'resolve' ); exec dbms_java.grant_permission( 'OWNER', 'SYS:java.net.SocketPermission', '220.127.116.11:443', 'connect,resolve' );
Archives: Extracting private key from Oracle Wallet
Oracle Wallet Manager and orapki do not let you extract the private key associated with user certificate located in Oracle Wallet. If you need it for some reason, for example testing with external tools like wget, then its possible to extract the private key using openssl, since Orale Wallet (ewallet.p12 file) is just PKCS#12 file.
[oracle@jfadboc1n01 wallet]$ openssl pkcs12 -in /home/oracle/wallet/ewallet.p12 -nocerts -out private_key.pem Enter Import Password: MAC verified OK Warning unsupported bag type: secretBag Enter PEM pass phrase: Verifying - Enter PEM pass phrase: [oracle@jfadboc1n01 wallet]$ ls -l total 16 -rw-r--r-- 1 oracle oinstall 11629 May 15 11:38 ewallet.p12 -rw-r--r-- 1 oracle oinstall 1879 May 17 08:53 private_key.pem
Here private_key.pem contains the private key extracted from Oracle Wallet.
The first password that openssl asks (Enter Import Password) is the wallet password, the other password (Enter PEM pass phrase) is used to protect the exported key.
Archives: Scipt to automatically move RAC 11gR2 services back to preferred instances
When instance fails in Oracle RAC, the services that were using this instance as a preferred instance are automatically relocated to instances marked as available for this service. But after the failed instance recovers and starts up again, the relocated services are not moved back and need manual srvctl relocate service command from administrator to move them back.
Here is a little Bash script to automate this process. Oracle Clusterware (Grid Infrastructure) can execute user callout scripts on FAN events, like INSTANCE up/down. Place this script under $GRID_HOME/racg/usrco/ and set the execute bits on the file. Then clusterware will execute that script for all FAN events, but the script will start processing only for instance up event.
Why is it needed? We just switched over to 4-node RAC consisting of many different applications, almost each of them connecting to its own schema. We created each application its own service restricting it to 1 (or max 2) nodes (1 or 2 nodes as preferred, all other nodes listed as available). After the first rolling patching, I noticed that the connection count and load on each node was very unbalanced, vast majority of the connections were connected to node1 and the last patched node had almost none and it did not get better over a few hours. This was because most of the services ended up on node1 and I had to manually look over each service and relocate it back where it belongs. This script attempts to automate this process.
Tested on Oracle Linux 5.8 with Oracle Grid Infrastructure 18.104.22.168 and Oracle Database 22.214.171.124 and 126.96.36.199.