Back from Open World 2013

I am back from Oracle Open World 2013.

This was my first Open World conference. I knew it was big but I was still unprepared for the overall size. Approximately 60,000 people attended the conference. I really doubt that it is possible to take in all that the conference offers in one week’s time. There is simply too much to see and do.

All of the sessions I attended were great. The speakers were well prepared and everyone had very good technical content. There were lots of vendors in the exhibit hall. I even took the opportunity to speak with one vendor of a product I use that I was experiencing a technical problem with. Even though this product is for SQL Server and I was at an Oracle conference, the vendor was more than happy to answer my questions.

The exhibit hall also featured Oracle’s Demo Grounds. I thought this was a real neat idea. In the Demo Grounds were small kiosks manned by Oracle employees that discussed one specific feature or product from Oracle’s portfolio. So if you always wanted to see Oracle’s Business Intelligence Suite in action, you could go up to that kiosk and get a personal, one-on-one demonstration. I took this as an opportunity to dive deeper into the feature. For instance, one kiosk was talking about Edition Based Redefinition. I already knew what the topic was about. But I wanted more details on one specific facet of this feature. I was able to go up and get my questions answered even though it wasn’t a part of their canned presentation.

I really liked the in-memory database option that was discussed in the keynote address by Larry Ellison. But I’m not sure if my company will purchase this or not. I also attended a session on a future backup appliance which can help many companies with their Oracle backup needs.

All in all, it was a great conference. I’ll be sure to attend again.

Off to Open World

I am off to my first Open World. It should be exciting and fun.

12c Move Datafiles Online

Prior to Oracle 12c, if you wanted to move a database’s file, you either had to shutdown the database, or take the datafile/tablespace offline. Here is an example of the steps you might take:

  1. ALTER TABLESPACE my_ts OFFLINE;
  2. !mv /old_dir/my_ts01.dbf /new_dir/my_ts01.dbf
  3. ALTER DATABASE RENAME FILE ‘/old_dir/my_ts01.dbf’ TO ‘/old_dir/my_ts01.dbf’;
  4. ALTER TABLESPACE my_ts ONLINE;

Now in Oracle 12c, you can do this quite simply with one command and another user can be accessing the file at the same time. To illustrate this, I will create a tablespace, and while moving it have another session access the file.

SQL> create tablespace move_me
2  datafile '/u01/app/oracle/oradata/ora12c/move_me01.dbf' size 5g;
Tablespace created.
SQL> create table system.test_tab (id number) tablespace move_me;
Table created.
SQL> insert into system.test_tab values (10);
1 row created.
SQL> commit;
Commit complete.

Now let’s begin the move of this file.

SQL> alter database move datafile
  2  '/u01/app/oracle/oradata/ora12c/move_me01.dbf' to
  3  '/u02/app/oracle/oradata/ora12c/move_me01.dbf';

At the same time, in another session, let’s insert a row into the only table in this tablespace.

SQL> insert into system.test_tab values (20);
1 row created.
SQL> commit;
Commit complete.

As you can see, the transaction was allowed to complete even though we are in the middle of moving the file.

While the file was moving I looked at the contents of both mount points.

[oracle@msp-test-ora12 ora12c]$ ls -l /u01/app/oracle/oradata/ora12c
total 8769660
-rw-r----- 1 oracle oinstall   10043392 Sep 17 10:52 control01.ctl
-rw-r----- 1 oracle oinstall   10043392 Sep 17 10:52 control02.ctl
-rw-r----- 1 oracle oinstall 5368717312 Sep 17 10:52 move_me01.dbf
-rw-r----- 1 oracle oinstall   52429312 Sep 17 10:52 redo01.log
-rw-r----- 1 oracle oinstall   52429312 Sep 16 22:00 redo02.log
-rw-r----- 1 oracle oinstall   52429312 Sep 17 02:00 redo03.log
-rw-r----- 1 oracle oinstall 1090527232 Sep 17 10:52 sysaux01.dbf
-rw-r----- 1 oracle oinstall  734011392 Sep 17 10:52 system01.dbf
-rw-r----- 1 oracle oinstall   68165632 Sep 17 10:45 temp01.dbf
-rw-r----- 1 oracle oinstall  471867392 Sep 17 10:52 undotbs01.dbf
-rw-r----- 1 oracle oinstall 1073750016 Sep 17 02:06 users01.dbf
[oracle@msp-test-ora12 ora12c]$  ls -l /u02/app/oracle/oradata/ora12c
total 684044
-rw-r----- 1 oracle oinstall 5368717312 Sep 17 10:52 move_me01.dbf

As you can see, the file is located in both spots, temporarily.
After some time, the MOVE command completes and we see our table has all of the data.
Database altered.
SQL> select * From system.test_tab;
        ID
----------
        10
        20

This new feature will be a great time saver for me. Many times, I need to relocate a file for a number of reasons and I will be using this new feature.

12c Adaptive Plans

I’ve been meaning to write something up on Oracle 12c’s new Adaptive Plans. When I do have poorly performing SQL statements, it seems that the issue is often due to bad optimizer stats. Recalculate the stats and performance is improved. I like the new 12c feature of Adaptive Plans where the database engine will reformulate the execution plan on-the-fly if it determines the intial plan is suboptimal.

Here is a nice video from Oracle Corp featuring Tom Kyte on this new 12c feature.

12c Data Redaction

Oracle 12c’s Advanced Security option now includes Data Redaction. This is a great way to secure your data , transparently to the application, from prying eyes. If an end user does not need the entire tax id, they can be presented with just the last four digits, as an example. To me, this is just a next logical extension of the Virtual Private Database and FGAC. Once you could define security policies to limit rows of data a user could see, no matter which application is used, then you could also redact the data as well. Even your lovable DBA will see redacted data in SQL*Plus.

http://www.oracle.com/technetwork/database/options/advanced-security/index-1964787.html

http://www.oracle.com/technetwork/database/options/advanced-security/advanced-security-wp-12c-1896139.pdf

ORA-01097

Every once in a while, a person comes across something that has been around forever but is a new experience for them. Just today, I hit the ORA-1097 error for the first time in my career. I was in a test database and needed to shut it down.

SQL> shutdown immediate
ORA-01097: cannot shutdown while in a transaction - commit or rollback first

The solution is obvious. Either commit my transaction or roll it back and then I can proceed.
SQL> commit;
Commit complete.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
With such a low error number, this must have been around for a very long time. At first I was taken aback by the error. What do you mean I can’t shutdown my instance? Then I read the error message and realized what was happening. I guess this makes sense. Oracle wants to know what to do with your transaction before the shutdown can commence. If you do a SHUTDOWN ABORT, Oracle won’t give you this message but will rollback the transaction on instance recovery.
The moral of the story is even if you have been around a long time and seen a lot, you haven’t seen it all.

Oracle 11.2.0.4 Released

I had a short vacation last week and while I was gone, Oracle released the 11.2.0.4 patchset. This is patch number 13390677.

It seems that as soon as I get caught up, I have a new version to contend with. Some time ago, I was two weeks away from completing upgrades of all of our databases to 11.2.0.2 when 11.2.0.3 came out. This year, right before I got everything upgraded to 11.2.0.3, Oracle 12c was released. Then about six weeks after I finished the upgrades, 11.2.0.4 came out. It’s not a bad thing to be one patchset behind, but I do always seem to be chasing the releases.

SQL Developer 4 Explain Plan Options

I often use SQL Developer as a quick and easy way to generate an Explain Plan for a query. I see that the new version of SQL Developer 4 contains a few more options than than its predecessor. In SQL Developer 3, you could go to Tools –> Preferences and then in Database –> Autotrace/Explain Plan, you could turn on or off certain columns from the output of these two tracing features.

In SQL Developer 4, there are a few more columns that you can include in your output.

SQL Developer 4

As time goes on, I am becoming more and more of a big fan of SQL Developer. I use this product daily and I often have many windows open to do my work.

SQL Developer 4 is in Early Adopter phase. You can get a copy of it here: http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/sqldev-download-v4-1925679.html

My only complaint with the download is that it does not come bundled with a JRE so you also need to have Java installed on your workstation.

I have two new features of SQL Developer 4, which I love. The first is the ability to color code the border of my window depending on my connection. Right click on the connection and choose Properties. Right next to the box you check to Save Password is a Connection Color pulldown menu. I choose red for my production connections. When I connect to a production database, I get a red edge around that connection’s SQL Worksheet, giving me a visual indicator that I am in production. If I had my dreams come true, I would also color the background of the text window, but this is better than nothing. I do something similar with other products too, i.e. in Putty, my production connections have red/pink text on a black background. So it was nice to see this make its way into SQL Developer.

The other new feature I love is the ability to search PL/SQL code. Go to View –> Find DB Object. You will get a window. Make sure you check All Schemas, All Object Types, and All Source Lines. I recently needed to know which package had code referencing “DBMS_MVIEW.REFRESH”. I put this as my search text and got my answer quickly. Not only can you see which PL/SQL objects reference your search text in this manner, if you click on a result, it takes you right to that location in the code!

If you do not follow Jeff Smith’s blog, and you are a fan or user of SQL Developer, you might want to do so. Here is the link: http://www.thatjeffsmith.com/

This blog contains tons of tips and tricks for SQL Developer. I see that Jeff Smith is giving a presentation and Open World next month and I will be in attendance for that!

oratop

I was recently made aware of a new Oracle-supplied utility named oratop. This professed to be a tool similar to the Unix/Linux top utility, but for Oracle databases. I have now had a chance to use this and play around with it a bit.

If you want to download the utility, visit Metalink Note 1500864.1 oratop – utility for near-realtime monitoring of databases, RAC and Single Instance

The note gives you links to download the utility and a link to a user’s guide.

Now that I’ve play around with it, I’m not sure how much I would really use it. I get this same information in Enterprise Manager and in EM, I can drill down into performance problems. I see this utility to be more helpful when I do not have access to EM.