Article on Fast Incremental Backups published

Today I received a copy of the latest Newsletter of the Swiss Oracle Usergroup, which is a special issue for the 20th. aniversary of the foundation of teh Swiss Oracle Usergroup. Nice one!

 with my article on
Fast incremental backups with block change tracking in Oracle 10g.
This article is also available on my blog.

I am not so happy with this publication since it has been quite a while that I had writen it and I have modified some things in between.

This version of the article has a misleading formulation in it about incremental levels in 10g. In 10g it is still possible to make incrementals of multiple levels, but Oracle only uses incremental levels 0 & 1 for the suggested backup strategy.  But the incremental level 1-4 are still available with RMAN!

Oracle 11g the self-managing database?

There is an interesting interview with Mrs. Leng Tan, Oracle’s vice president of database manageability and diagnosability available in the web.
In the interview Mrs. Tan tell us about her vision of the self-managing database.
She talks about enhancements in Oracle Database 11g, such as the
– Automatic Database Diagnostic Monitor (ADDM) & RAC
– automatic sql tuning using sql profiles which are generated automatically by the server.
 – change management features like recording a workload on one system and run it on another system for testing
– enhencement in Enterprise Manager (database control & grid control)
– the role of the DBA in the future

I will travel to San Francisco next week and take part in the first pilot course at Oracle University and hopefully deliver the first 11g courses sometime not very far in the future.
I will let you know about details ASAP!

How to gather statistics on data dictionary objects in Oracle 10g

Before Oracle database 10g Oracle explicitly recommeded not to gather statistics on data dictionary objects.
As of Oracle database 10g Oracle explicitly does recommend to gather statistics on data dictionary objects. As you might know, there is an automatically created SCHEDULER JOB in every 10g database which runs every night and checks for object which have either no statistics at all or for which the statistics have become STALE (which means stat at least 10% of the values have changed). This job is call GATHER_STATS_JOB and belongs to the autotask job class. It uses a program which again call a procedure from built in package DBMS_STATS which does the statistics collection. This feature only works if the initialization parameter STATISTICS_LEVEL is set to TYPICAL at least (which is the DEFAULT in 10g) and it utilizes the TABLE MONITORING feature. TABLE MONITORING is enabled for all tables in 10g by DEFAULT. One question which pops uo in my seminars frequently is “Does this job also collect statistics on the data dictionary objects as well?”  The answer is not 42 but “YES, it does!” and here is the proof for this: 
– first let us check if dbms_stats.gather_database_stats collect statistics for the data dictionary:
SQL> select count(*) from tab$;

  COUNT(*) ———-       1227

SQL> create table t2 (col1 number);

Table created.

SQL> select count(*) from tab$;

  COUNT(*) ———-       1228

SQL> select NUM_ROWS from dba_tables where table_name=’TAB$’;

  NUM_ROWS ———-       1213

SQL> exec dbms_stats.gather_database_stats;

PL/SQL procedure successfully completed.

SQL> select NUM_ROWS from dba_tables where table_name=’TAB$’;   NUM_ROWS ———-       1228 – IT DOES! – and now let’s see if the job does also: SQL> create table t3 (col1 number);

Table created.

SQL> create table t4 (col1 number);

Table created.

SQL> select NUM_ROWS from dba_tables where table_name=’TAB$’;

  NUM_ROWS ———-       1228

– gather_stats_job run manually from DATABASE CONTROL !!! SQL> select NUM_ROWS from dba_tables where table_name=’TAB$’;

  NUM_ROWS ———-       1230

– and IT ALSO DOES! — even though there were not even 0.1% of the values changed it did! So when should we gahter statistics for the data dictionary manually? Oracle recommends to collect them when a significat nnumber of changes were applied to the data dictionary, like dropping significant numbers of partions and creating new ones dropping tables, indexes, creating new ones and so on. But this only if it is a signifcant number of changes and you cannot wait for the next automatically scheduled job run.

goodby Istanbul

The last day for this time in Istanbul I spent relaxing in a hamam.
For this time I chose to take the full program (suitable for a sultan)at Cağaloğlu Hamamı  which is one of the things you should have done before you die.

I felt like I was newly born afterwards.

 After this I took a lst coffee at the Mandabatmaz Kahvesi
which is an absolute MUST for every fan of sofisticated coffee.
    I just had to congratulate the kahveçi for his brilliant coffee. The bull will not sink in the cup, this is for sure! One more very good reason to come back definitely!

Is there a CHECKPOINT with SHUTDOWN IMMEDIATE?

Today I presented a little 2 hours presentation for some DBAs at Turkcell Academy.

I introduced them to the infrastructure of ASM and explained Oracle’s unique locking concept.

After this  Tonguç and his brothers invited me for a drink and dinner downtown and we had some intersting discussions.

One interesting questions we talked about was what happens exactly with a SHUTDOWN IMMEDIATE – is there a checkpoint before shutdown or is it just a flushing of the redolog buffer to the logfiles?

Here is the proof for the CHECKPOINT happening before shutdown:

SYS AS SYSDBA @ lutz1 SQL> create table t as select * from all_objects;

Table created.

SYS AS SYSDBA @ lutz1 SQL> select current_scn from v$database;

CURRENT_SCN
———–
     202799

SYS AS SYSDBA @ lutz1 SQL> select file#, checkpoint_change# from v$datafile;

     FILE# CHECKPOINT_CHANGE#
———- ——————
         1             197655
         2             197655
         3             197655
         4             197655

SYS AS SYSDBA @ lutz1 SQL> select file#, checkpoint_change# from v$datafile_header;

     FILE# CHECKPOINT_CHANGE#
———- ——————
         1             197655
         2             197655
         3             197655
         4             197655

SYS AS SYSDBA @ lutz1 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS AS SYSDBA @ lutz1 SQL> startup mount
ORACLE instance started.

Total System Global Area  507510784 bytes
Fixed Size                  1220264 bytes
Variable Size             155189592 bytes
Database Buffers          348127232 bytes
Redo Buffers                2973696 bytes
Database mounted.
SYS AS SYSDBA @ lutz1 SQL> select file#, checkpoint_change# from v$datafile_header;

     FILE# CHECKPOINT_CHANGE#
———- ——————
         1             203071
         2             203071
         3             203071
         4             203071

SYS AS SYSDBA @ lutz1 SQL> alter database open;

Database altered.

SYS AS SYSDBA @ lutz1 SQL>  select file#, checkpoint_change# from v$datafile_header;

     FILE# CHECKPOINT_CHANGE#
———- ——————
         1             203072
         2             203072
         3             203072
         4             203072

SYS AS SYSDBA @ lutz1 SQL>

#######here is the content of the alertlog for the startup
####### as you can see there is no instance recovery!!!
####### there was a checkpoint before shutdown!!

Completed: ALTER DATABASE   MOUNT
Fri May 18 01:13:36 2007
alter database open
Fri May 18 01:13:36 2007
Thread 1 opened at log sequence 12
  Current log# 3 seq# 12 mem# 0: +DATA/lutz1/onlinelog/group_3.286.622819249
  Current log# 3 seq# 12 mem# 1: +FRA/lutz1/onlinelog/group_3.259.622819253
Successful open of redo thread 1
Fri May 18 01:13:36 2007
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Fri May 18 01:13:36 2007
SMON: enabling cache recovery
Fri May 18 01:13:38 2007
Successfully onlined Undo Tablespace 1.
Fri May 18 01:13:38 2007
SMON: enabling tx recovery
Fri May 18 01:13:38 2007
Database Characterset is WE8ISO8859P1
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=18, OS id=4284
Fri May 18 01:13:44 2007
Completed: alter database open

Hope that you liked the seminar -… I did!

Finished my INSIDE ORACLE Seminar
            with 35 delegates
yesterday @ The Moevenpick Hotel

     in Istanbul

I would like to say thank you very much to Leyla Alpaslan & Gökhan Uğuroğlu
from Oracle University Turkey for inviting me to Istanbul and organizing this event.
I had not expected to find such such a well organized event in such a brilliant location
- they had produces  real Oracle Courseware of my sysdba slides  

- there was always coffee and deliciouse paistries available
It was only that at the end it got a little hot in the room, but this was my fault because I had told the hotel staff that it is too cold instead of too warm in Turkish.
I must polish up my Turkish again. Forgot so much over the years. 

AUTHID CURRENT_USER and surprizing effects in PL/SQL

One most interesting aspect of INVOCER RIGHTs for PL/SQL procedures I learned  in Steven Feuersteins lectures:

As you might know a PL/SQL procedure runs with the privileges of the developer by default which has ever been this way.

Since Oracle 8i it is possible to use AUTHID CURRENT_USER to create programs runnig with the AUTHORITY of the USER of the program.

Steven demonstrated  how a procedure A running under AUTHORITY of the AUTHOR calls another procedure B running under AUTHORITY of the USER, which again calls a procedure C, running under AUTHORITY of the AUTHOR again.

Strangely enough we learned that once procedure B is invoked also procedure C uses the privileges of the CURRENT USER, because CURRENT USER “is set to the owner of the definer rights program..”
.. which is in other words, that as soon as you run a program running with USERS s AUTHORITY calling other programs running with the DEFAULT ,AUTHOR’s rights, you can end up with surprises waiting for you there.

Getting introduced to Istanbul

Today a 9:30 my phone in the hotel rang.
It was Tonguç Yilmaz who invited me to take a tour around Istanbul with him. I was very pleased to meet him and we had a greate day. He spent full eight hours to host me and show me around his town and explain everything to me!

When he was waiting in th lobby of the hotel he ran into a comrade he had studied togeter with and whom he had not met since 10 years. Both of them were very surprised to meet each other after such a long time, standing in front of the lift waiting for an Oracle guy to come down. And the best thing was that they were waiting for different people, one for me and the other one for Paul Dorsey, an Oracle expert from Iselin NJ in the U.S.
What an incident! Two friends meet in a hotel by accident ,
both waiting in the same hotel in front of a lift for two different people,
who also are both linked with Oracle!

 Istanbul must be a village I thought! Incredible!

We first went to Dolmabahçe, from where we took a taxi to Taksim place, which is the center of the European side of Istanbul. We strolled down Istiklal Caddesi until we came to location of the German Goethe Institut, we a very fancy restaurant is also located on the roof of the building.
It is called Litera , from where you get a
wounderful view 
across the Golden Horn to the old city of Istanbul.
We had a cold drink and after this we went down to a tunnel from where we took a kind of tram to Karaköy from where we took a ferry to the Anatolian side of Istanbul.
Tonguç showed me the train station HaydarpaŞa
from where the famous Orient Express starts all the way to Teheran,
and explained geography, history and strategic situation of Istanbul to me.  

On the way to the busstop we needed Tonguç asked a taxi driver for directions and to my surprize he said ” …you are walking into the wrong direction. Jump right into my taxi and I will give you a free ride to the busstop”, can you believe this?  
We took a middibus to Fenerbahçe, passing along the footballstadion.
Tonight is an historical football match and if the Fenerbahçe team wins against Trabzonspor there will be large party going on in all the town, because the team will be winner of the championship and this in their 100th. year! 
In Fenerbahçe I had tavuk göğsü, a very special Turkish desert which I like very much. And I guess that Tonguç took me all the way to very best place to have it.

Tonguç,  you are a very good tourist guide!
And I appreciated it very much that you spent your valuable time at the weekend to introduce me to your city!
Thank you very much for the wonderfull day!

Ya Istanbul, ne kadar güzelsin sen!

I had my first walk around the area around my hotel.

I always like to find out what normal life is like in other countries,
not only what is shown to tourists .
I experienced  that Istanbul people are generally very friendly and kind people.

I had a first kebab  which was very good.

Arrived in Istanbul

I have just arrived in Istanbul, the only city in the world which is located in two different continents.
 I will deliver my INSIDE ORACLE seminar on May 14th./15th.

My hotel is on the European side of the Bosporus.
After my seminar I will have a look at the oriental side of the city.