Yesterday I attended #cluboracle event in London (I highly recommend this event! ... but I don't recommend Thameslink train service :) ). In this event one interesting discussion popped up regarding 12c. I've already written about this feature, so in this post I'll clarify some points as I understand them and test out a few more things.
Here is a quote from my last post summing up my findings so far:
This new column seems (more...)
I gave a talk about my highlights from Oracle Open World 2014 and how to use CloneDB in Oracle User Group Estonia Meetup #3. It was a really good meetup and thanks to everybody who attended.
Here you can find the slides
Got an interesting question today: There are tables TABLE1, TABLE2 and a junction table that joins them called JUNCTABLE. Need the following output as XML
I know the output could be aggregated using XMLAGG, but I have never looked into how to format the tag names and attributed in the output like requested.
Data in my very simplified sample tables:
SQL> select * from table1;
SQL> select * from table2;
If you are in Estonia, then join our Oracle User Group Estonia Meetup #3. I'll talk there about my latest trip to Oracle Open World, what I heard there and also I'll present how to use CLONEDB feature.
Facebook or Meetup.com.
This post is for those who google the errors and maybe it will help to save some time. I've been playing around with clonedb for some time now and every time I hit these errors it takes me some time to figure out what actually the problem is :) The error messages are not really helpful at all. Maybe I'll remember it now.
When creating a new clone database using clonedb then after creating the (more...)
12c gave us two new features among others: DBMS_UTILITY.EXPAND_SQL_TEXT and temporal validity. DBMS_UTILITY.EXPAND_SQL_TEXT is advertised as a means to expand SQL text that is using views, but it is also very useful to see how Oracle internally rewrites some SQL queries. Here I'm going to use it to see how temporal validity queries are actually executed. All tests are done using 22.214.171.124 Enterprise Edition.
First I'll create a table for (more...)
Before 11g if you added a new column to a table then Oracle had to lock and physically update all rows in the table. Possibly a painful process :) 11g helped with that a little, giving metadata only default values for NOT NULL columns, so the change was done only in data dictionary, not in the table data.
SQL> create table default_values (
2 id number primary key
SQL> insert into (more...)
When cloning pluggable databases Oracle gives you also SNAPSHOT COPY clause to utilize storage system snapshot capabilities to save on storage space. But it works only when datafiles are placed on ACFS, ZFS Appliance over DNFS or Netapp (Oracle Multitenant Pluggable Database Snapshot Cloning: Use Cases and Supported Platforms Doc ID 1597027.1) and you also cannot unplug/drop the source PDB and you also cannot unplug the clone PDB.
Recently I've started to like BTRFS (more...)
I started to write this blog post on how 12c gave us new multitenant architecture (pluggable databases) and with that also a new possibility to do database upgrades. But during writing I discovered how catctl.pl can fail the upgrade if you have modified the SQL plus prompt with custom login.sql.
I'm starting with these already created databases:
|cdb1||/u01/app/oracle/product/126.96.36.199/db||The initial container with version 12.1.0. (more...)|
Fre MySQL seminar on 27. august 2014 @ 13:00. Announcement by Oracle User Group Estonia:
Developing modern applications using MySQL.
In this seminar series learn how to best use MySQL for your existing and new development requirements with leading MySQL expert and Oracle Ace Director Ronald Bradford.
These presentations provide a detailed review of the essential lifecycle components for developing a successful software application and offer a checklist for your company to review the (more...)
I know it is a little late announcement, but Oracle User Group Estonia is having its first meetup after many years tonight. I'm presenting there "Making MySQL highly available with Oracle Grid Infrastructure".
More info and registration here: http://www.meetup.com/Oracle-User-Group-Estonia/events/165539962/.
So if anyone is in Tallinn today, then join us!
I'm also speaking at Oracle User Group Norway 2014 Spring conference (3-5 April 2014), topic "Making MySQL highly available with Oracle Grid Infrastructure". It (more...)
I was looking for PL/SQL examples to use metadata and data filtering in datapump API, but I didn't find any. So here is one example. It uses table reload_dev_tables to specify what schemas/tables should be exported using data pump and what where clause should be set.
Structure for reload_dev_tables:
There are a few Oracle database replication solutions on the market:
- Oracle Streams (powerful, included with RDBMS license (Oracle SE has trigger-based capture, EE mines redo logs and log buffer), but deprecated - no longer developed, complex to manage)
- Oracle GoldenGate (powerful, but very expensive)
- Tungsten (heterogeneous, but from Oracle (more...)
One link that I have to send to developers quite frequently is how to use XMLTABLE in SQL queries to bind comma separated list of values instead of generating large IN list directly to the query (and this way avoid new sqlid/cursor/wasted memory for each different value combination provided). The link that I usually send is this, but in this post I'd like to expand it a little, so it would work even when the string contains special XML characters.
For numbers, the usage is simple:
> var num_list varchar2(100)
> exec :num_list := '2668,2669,2670'
PL/SQL procedure successfully completed.
Starting from 11.2 its possible to use SSL client certificates to authenticate yourself to a remote web service using SSL client certificates. I did not find much information on it using Google or documentation, that is why I'm writing this post.
Please refer to this post by Tim Hall to get started on connecting to HTTPS service using UTL_HTTP, all of this is needed before continuing with SSL client certificate authentication.
The first thing you need is to generate user certificate request inside Oracle Wallet, sign it by CA and load the returned certificate back to Wallet. I'm not (more...)
Back-story: A developer came to me and wanted explanation for a weird behavior in MySQL. They inserted a record (to InnoDB table), committed, and after receiving a message (on another application) tried to read that inserted record immediately, but the newly inserted record was not found. Problem only happened in production, but not always (quite frequently).
After comparing the MySQL parameter files between production and development environments I discovered that in production autocommit was disabled to make MySQL behave more like Oracle. This setting was removed from development after we rebuilt the environment (to use multiple MySQL instances with Oracle (more...)
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 188.8.131.52).
First, load the Java source into database. (more...)
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
-rw-r--r-- 1 oracle oinstall 11629 May (more...)
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 (more...)
I've been doing lately quite many database clustering implementations; Oracle RAC and since we have many MySQL instances in production, had to find a good way to make MySQL instances highly available also.
One good solution for this is managing MySQL instances with clusterware and since we are planning to use Oracle RAC on Oracle Enterprise Linux anyway, then Oracle Clusterware is an excellent candidate for this task. Also... Oracle Clusterware is included with Oracle Enterprise Linux at no additional charge.
Requirements I had:
- Multiple MySQL instances running in the same cluster, in case of node failure affected MySQL instances (more...)