DBMS_COMPARISON supplied package after 11g Release 1

I discussed 10g’s ORA_HASH SQL function to determine if two Oracle tables’ data are equal or not with this post before, especially if you are working with huge tables and they are on different locations this method is very effective.

In the same post I also mentioned some favorite schema and data comparison methods used within the community, so this one is a following post to that post to mention the new 11g supplied package; DBMS_COMPARISON.

After a fast startup demonstration based on psoug’s library I saw that this new feature also uses ORA_HASH SQL function. :) Also for the schema comparison part which dictionary checks are made can be followed inside a 10046 SQL trace as demonstrated below;

conn hr/hr

DROP TABLE depts PURGE ;

CREATE TABLE depts NOLOGGING AS SELECT * FROM departments ;

CREATE UNIQUE INDEX ui_depts ON depts(department_id) NOLOGGING ;

DELETE FROM depts WHERE department_id = 10;

1 row deleted.

COMMIT;

ALTER SESSION SET max_dump_file_size=unlimited;
ALTER SESSION SET timed_statistics = true;
ALTER SESSION SET statistics_level = ALL ;

ALTER SESSION SET tracefile_identifier = dbms_comparison ;

ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 8';

exec dbms_comparison.create_comparison(comparison_name=>'compare_test', schema_name=>'HR', object_name=>'DEPTS', dblink_name=>NULL,  remote_schema_name=>'HR', remote_object_name=>'DEPARTMENTS',  scan_percent=>90);

set serveroutput on

DECLARE
  retval dbms_comparison.comparison_type;
BEGIN
  IF dbms_comparison.compare('compare_test', retval, perform_row_dif=>TRUE) THEN
    dbms_output.put_line('No Differences');
  ELSE
    dbms_output.put_line('Differences Found');
  END IF;
END;
/
Differences Found

PL/SQL procedure successfully completed.

ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';

select value from v$diag_info where name = 'Default Trace File' ;

VALUE
----------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/tong/tong/trace/tong_ora_4402_DBMS_COMPARISON.trc

CMD > tkprof /u01/app/oracle/diag/rdbms/tong/tong/trace/tong_ora_4402_DBMS_COMPARISON.trc tong_ora_4402_DBMS_COMPARISON.txt sys=yes waits=yes sort=execpu

set null @
set colsep |
set linesize 2500

select *  FROM  user_comparison_row_dif ;

COMPARISON_NAME               |   SCAN_ID|LOCAL_ROWID       |REMOTE_ROWID

INDEX_VALUE

STA|LAST_UPDATE_TIME

COMPARE_TEST                  |         4|@                 |AAARADAAFAAAAA3AAA
10
DIF|04-OCT-08 02.58.03.306182 PM

exec dbms_comparison.drop_comparison(comparison_name=>'compare_test') ;

There are several constraints to use this method like the local database that runs the subprograms in the DBMS_COMPARISON package must be at least 11.1 and the remote database must be 10.1 at least. Non-Oracle databases are not supported and the database character sets must be the same for the databases being compared. But I am pretty sure with feature releases this package will be enhanced and be used with other database features.

ps: During my unconference session at OOW08 – APEX Test Drive – I mentioned that Apex 3 on 11.1 under Utilities tab also has a GUI support to use this new supplied package.

11.1 Flashback Database Test and Flashback Tablespace Off Options Drawback

Oracle’s flashback features are one of the most loved, read posts on my blog. The three studies I have done before were;

Oracle 10g Flashback Versions Query, Drop Table and Recyclebin Management

Oracle 10g flashback features’ limitations and restrictions

Flashback database and NOLOGGING operations problem demo

Today I tested flashback database feature on 11.1 and my experiences were as following;

connect / as sysdba

create smallfile tablespace flashtbs
datafile 'flashtbs1.dbf' size 500K
logging
extent management local
segment space management auto;

create user flash identified by flash
default tablespace flashtbs
temporary tablespace temp;

grant connect, resource, dba to flash;

-- check if database is in archivelog mode
archive log list

-- check if flashback option is enabled
select flashback_on from v$database;

-- check for the flashback log files
host ls -lt $ORACLE_BASE/flash_recovery_area/*/flashback

connect flash/flash

create table emp
tablespace flashtbs
as select * from hr.employees;

select sum(salary) from emp;

SUM(SALARY)
-----------
     691400

-- note as scn2
select current_scn from v$database;

CURRENT_SCN
-----------
    1713454

select undoblks from v$undostat;

  UNDOBLKS
----------
        49
        42

select * from V$FLASHBACK_DATABASE_LOG;

OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- --------- ---------------- -------------- ------------------------
             1664754 31-MAY-08             1440      516628480                338558976

select * from V$FLASHBACK_DATABASE_STAT;

BEGIN_TIM END_TIME  FLASHBACK_DATA    DB_DATA  REDO_DATA ESTIMATED_FLASHBACK_SIZE
--------- --------- -------------- ---------- ---------- ------------------------
04-SEP-08 04-SEP-08        4726784    1294336    1688064                        0

begin
 for i in 1..10000 loop
   update emp set salary=salary+1;
 end loop;
 commit;
end;
/

select undoblks from v$undostat;

  UNDOBLKS
----------
      6177
        42

select * from V$FLASHBACK_DATABASE_LOG;

OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- --------- ---------------- -------------- ------------------------
             1664754 31-MAY-08             1440      516628480                378716160

select * from V$FLASHBACK_DATABASE_STAT;

BEGIN_TIM END_TIME  FLASHBACK_DATA    DB_DATA  REDO_DATA ESTIMATED_FLASHBACK_SIZE
--------- --------- -------------- ---------- ---------- ------------------------
04-SEP-08 04-SEP-08       55263232    3080192  105243648                        0

-- check for new flashback log files
host ls -lt $ORACLE_BASE/flash_recovery_area/*/flashback

[ 14 new 3989504 sized flb files ]

begin
 for i in 1..10000 loop
   update emp set salary=salary+1;
 end loop;
 commit;
end;
/

select undoblks from v$undostat;

  UNDOBLKS
----------
     12328
        42

select * from V$FLASHBACK_DATABASE_LOG;

OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- --------- ---------------- -------------- ------------------------
             1664754 31-MAY-08             1440      516628480                417447936

select * from V$FLASHBACK_DATABASE_STAT;

BEGIN_TIM END_TIME  FLASHBACK_DATA    DB_DATA  REDO_DATA ESTIMATED_FLASHBACK_SIZE
--------- --------- -------------- ---------- ---------- ------------------------
04-SEP-08 04-SEP-08      105086976    4974310  210600960                        0

host ls -lt $ORACLE_BASE/flash_recovery_area/*/flashback

[ again 14 new 3989504 sized flb files ]

Undo and Redo stats are very similar for both runs, as expected.

V$UNDOSTAT.UNDOBLKS
1st) 6177 – 49 = 6128
2nd) 12328 – 6177 = 6151

V$FLASHBACK_DATABASE_LOG.ESTIMATED_FLASHBACK_SIZE
1st) 378716160 – 338558976 = 40157184
2nd) 417447936 – 378716160 = 38731776

V$FLASHBACK_DATABASE_STAT.FLASHBACK_DATA
1st) 55263232 – 4726784 = 50536448
2nd) 105086976 – 55263232 = 49823744

V$FLASHBACK_DATABASE_STAT.REDO_DATA
1st) 105243648 – 1688064 = 103555584
2nd) 210600960 – 105243648 = 105357312

V$FLASHBACK_DATABASE_STAT.DB_DATA
1st) 3080192 – 1294336 = 1785856
2nd) 4974310 – 3080192 = 1894118

Now lets repeat the test with a tablespace which does not use flashback option.

connect / as sysdba

create smallfile tablespace flashtbs2
datafile 'flashtbs2.dbf' size 500K
logging
extent management local
segment space management auto;

alter tablespace flashtbs2 flashback off;

select name, flashback_on from v$tablespace;

NAME                           FLA
------------------------------ ---
..
FLASHTBS                       YES
FLASHTBS2                      NO
..

connect flash/flash

create table emp2
tablespace flashtbs2
as select * from hr.employees;

select undoblks from v$undostat;

  UNDOBLKS
----------
         6
        11
         0
     12334
        42

select * from V$FLASHBACK_DATABASE_LOG;

OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- --------- ---------------- -------------- ------------------------
             1664754 31-MAY-08             1440      516628480                414031872

select * from V$FLASHBACK_DATABASE_STAT;

BEGIN_TIM END_TIME  FLASHBACK_DATA    DB_DATA  REDO_DATA ESTIMATED_FLASHBACK_SIZE
--------- --------- -------------- ---------- ---------- ------------------------
04-SEP-08 04-SEP-08      106602496  107986944  211301888                        0

begin
 for i in 1..10000 loop
   update emp2 set salary=salary+1;
 end loop;
 commit;
end;
/

select undoblks from v$undostat;

  UNDOBLKS
----------
      6138
        11
         0
     12334
        42

select * from V$FLASHBACK_DATABASE_LOG;

OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- --------- ---------------- -------------- ------------------------
             1664754 31-MAY-08             1440      516628480                453746688

select * from V$FLASHBACK_DATABASE_STAT;

BEGIN_TIM END_TIME  FLASHBACK_DATA    DB_DATA  REDO_DATA ESTIMATED_FLASHBACK_SIZE
--------- --------- -------------- ---------- ---------- ------------------------
04-SEP-08 04-SEP-08      157245440  113696768  314953216                        0

[ this time just 5 new 3989504 sized flb files are created ]

Other than the flashback log files undo and Redo stats are again very similar for the third run as well, interesting.

V$UNDOSTAT.UNDOBLKS
3rd) 6138 – 6 = 6132

V$FLASHBACK_DATABASE_LOG.ESTIMATED_FLASHBACK_SIZE
3rd) 453746688 – 414031872 = 39714816

V$FLASHBACK_DATABASE_STAT.FLASHBACK_DATA
3rd) 157245440 – 106602496 = 50642944

V$FLASHBACK_DATABASE_STAT.REDO_DATA
3rd) 314953216 – 211301888 = 103651328

but this time DB_DATA stats has a significant increase,

V$FLASHBACK_DATABASE_STAT.DB_DATA
3rd) 113696768 – 107986944 = 5709824

Now lets try to flashback the database with a tablespace which this feature is disabled.

select sum(salary) from emp;

SUM(SALARY)
-----------
    2831400

-- note as scn1
select current_scn from v$database;

CURRENT_SCN
-----------
    1775827

-- temporary action
insert into emp select * from emp;

commit;

select sum(salary) from emp;

SUM(SALARY)
-----------
    5662800

connect / as sysdba

shutdown immediate;

startup mount;

-- flashback to scn1
flashback database to scn 1775827 ;

*
ERROR at line 1:
ORA-38753: Cannot flashback data file 7; no flashback log data.
ORA-01110: data file 7: '/u01/app/oracle/product/11.1.0/db_1/dbs/flashtbs2.dbf'

-- in order to flashback we need to get rid of the datafiles which have not this feature enabled
alter database
datafile '/u01/app/oracle/product/11.1.0/db_1/dbs/flashtbs2.dbf' offline ;

-- try again flashback to scn1
flashback database to scn 1775827;

alter database open read only;

select sum(salary) from flash.emp;

SUM(SALARY)
-----------
    2831400

shutdown immediate;

startup mount;

alter database
datafile '/u01/app/oracle/product/11.1.0/db_1/dbs/flashtbs2.dbf' offline ;

-- flashback to scn2
flashback database to scn 1713454;

alter database open resetlogs;

select sum(salary) from flash.emp;

SUM(SALARY)
-----------
     691400

So the bottom line is if you think you will be using any flashback options be careful and get prepared about their limitations(mentioned in my previous post linked above), for this case flashback disabled tablespace was the drawback and workaround was to offline it(of course if this is acceptable).

ps: If you want to clean up the mess;

shutdown immediate;

startup 

drop user flash cascade;

drop tablespace flashtbs including contents and datafiles;

-- this one is not needed because of the second flashback database
-- drop tablespace flashtbs2 including contents and datafiles;

MAX_RUN_DURATION attribute of the SCHEDULER to setup a sniper job

Many thanks to Ravi R. of OTN Scheduler Forum for the idea, below is the code for a general purpose sniper job that snipes any job that has exceeded its maximum duration.

--
-- this test was done on 11.1 EE
--
-- ensure that the values of are both non-NULL and sufficiently high for your need
select value
  from v$parameter
 where name='job_queue_processes';

VALUE
-----
1000

-- if needed dbms_scheduler.set_scheduler_attribute('MAX_JOB_SLAVE_PROCESSES', 10); can be used to set
select value
  from all_scheduler_global_attribute
 where ATTRIBUTE_NAME= 'MAX_JOB_SLAVE_PROCESSES';

VALUE
-----
10 

-- create a table for output
create table job_output (a timestamp with time zone, b varchar2(1000));

-- add an event queue subscriber for this user's messages
exec dbms_scheduler.add_event_queue_subscriber('myagent')

-- create a sniper procedure
create or replace procedure sniper_proc
    (message IN sys.scheduler$_event_info) as
begin

    -- if this is not a JOB_OVER_MAX_DUR message, error out
    if message.event_type != 'JOB_OVER_MAX_DUR' then
      raise PROGRAM_ERROR;
    end if;

    -- stop the job
    dbms_scheduler.stop_job('"'||message.object_owner||'"."'||
      message.object_name ||'"');

    -- insert into job output
    insert into job_output values (systimestamp,
     'sniper job sniped '||'"'||message.object_owner||'"."'||
      message.object_name ||'"');
end;
/

-- create a sniper program
begin
    dbms_scheduler.create_program (
        program_name => 'sniper_prog',
        program_action=> 'sniper_proc',
        program_type => 'stored_procedure',
        number_of_arguments => 1,
        enabled => FALSE) ;

    dbms_scheduler.define_metadata_argument ( 'sniper_prog','event_message',1);
    dbms_scheduler.enable('sniper_prog');
end;
/

-- create a general purpose sniper job to kill any job that has
-- exceeded its max_run_duration
begin
    dbms_scheduler.create_job('sniper_job',
      program_name=>'sniper_prog',
      event_condition =>
        'tab.user_data.event_type = ''JOB_OVER_MAX_DUR''',
      queue_spec =>'sys.scheduler$_event_queue,myagent',
      enabled=>true);
end;
/

-- create two jobs to test the sniper job
begin
    dbms_scheduler.create_job
      ( 'first_job', job_action =>
          'insert into job_output values(systimestamp, ''first job begins'');
           commit; dbms_lock.sleep(120);
           insert into job_output values(systimestamp, ''first job ends'');',
        job_type => 'plsql_block',
        enabled => false ) ;
    dbms_scheduler.set_attribute
      ( 'first_job' , 'max_run_duration' , interval '60' second);

    dbms_scheduler.create_job
      ( 'second_job', job_action =>
          'insert into job_output values(systimestamp, ''second job begins'');
          commit; dbms_lock.sleep(120);
          insert into job_output values(systimestamp, ''second job ends'');',
        job_type => 'plsql_block',
      enabled => false ) ;
    dbms_scheduler.set_attribute
      ( 'second_job' , 'max_run_duration' , interval '60' second);

    dbms_scheduler.enable('first_job');
    dbms_lock.sleep(10);
    dbms_scheduler.enable('second_job');
end;
/

SELECT * FROM job_output ORDER BY a ;

A                                                 B
------------------------------------------------- --------------------------------------------------------------------------------
03/09/2008 11:45:29,585914 +03:00                 first job begins
03/09/2008 11:45:40,019922 +03:00                 second job begins
03/09/2008 11:46:34,226044 +03:00                 sniper job sniped "SH"."FIRST_JOB"
03/09/2008 11:46:44,344798 +03:00                 sniper job sniped "SH"."SECOND_JOB"

One limitation of this is that it only works for jobs in the same schema as the sniper job.

Also there is a similar code example of a job e-mail notification package on the OTN Scheduler page, here.

How to transfer LONG datatype over DBLINK as VARCHAR?

Siebel as one of sources for our ETL and customer requested S_NOTE_SR.NOTE column with LONG datatype, not a smart idea to transfer this data to DW but customer is usually the king here.

SYS.DBMS_METADATA_UTIL.LONG2VARCHAR($ORACLE_HOME/rdbms/admin/dbmsmetu.sql) function is an undocumented option for this need. It worked for us on 10.2 and I hope if you need it somehow you test it if it will again work for your case. :)

SQL> SELECT column_function
  2    FROM tta01ext_table_cols t
  3   WHERE table_name = 'S_NOTE_SR'
  4     AND table_column = 'NOTE'
  5  ;

COLUMN_FUNCTION
------------------------------------------------------------------------------------------------------------
SYS.dbms_metadata_util.long2varchar@DBLK_SIEBEL(length=>4000,tab=>'SIEBEL.S_NOTE_SR',col=>'NOTE',row=>rowid)

ps: TO_CLOB and DBLINK problem –

-- local test is fine with TO_CLOB
SELECT priv_flg, created, to_char(to_clob(note)), note_type
  FROM src_siebel.s_note_sr
 WHERE rownum < 2; 

-- remote test fails with TO_CLOB
SELECT priv_flg, created, to_char(to_clob(note)), note_type
  FROM siebel.s_note_sr@dblk_siebel
 WHERE rownum < 2;

ORA-00932: inconsistent datatypes: expected NUMBER got LONG
ORA-02063: önceki line, kaynağı DBLK_SIEBEL

-- remote test is fine with DBMS_METADATA_UTIL.LONG2VARCHAR
SELECT priv_flg,
       created,
       sys.dbms_metadata_util.long2varchar@dblk_siebel(4000,
                                                       'SIEBEL.S_NOTE_SR',
                                                       'NOTE',
                                                       ROWID)
  FROM siebel.s_note_sr@dblk_siebel
 WHERE rownum < 2;

The Rijndael(Advanced Encryption Standard-AES) algorithm to secure TBs of data

The Need Definition -

There are some TBs of your Call Data Records(CDR) to be shared with an external company. Some of the columns in this dataset must be encrypted and this encryption method must guarantee that this columns only can be decrypted by the you, the company who owns the data.

The Solution Advised -

After 10g we have DBMS_CRYPTO supplied package and inside this package there is the Rijndael(AES) algorithm which was selected by NIST in October 2000 to become the new official Advanced Encryption Standard(AES) for use within the US Government.

AES is available in two strengths; 128 and 256 bits, and of course the 256-bit version is approximately %50 slower than the 128-bit version, so for the optimum encryption performance 128 bit strength will be enough for this need since to break AES128 encryption one will need 2 ^ 100 amount of keys which will mean months of time even with a super-computer.

And why to do this inside the database but not with a custom C program on operating system for example, the answer is simple as usual; you have the PARALLEL QUERY, HASH JOIN, PARTITIONING, COMPRESSION type of VLDB options already available inside the database you paid for so for the other path you will most probably be re-inventing a dumper wheel within more time and this wheel will born with its maintanance costs on long-term.

A Simple Demostration based on the 10g EM Data Encryption Wizard -

AES128 demo with DBMS_CRYPTO

Some additional reading and references –

Advanced Encryption Standard

Encrypt Your Data Assets By Arup Nanda

How To Encrypt Data in Oracle Using PHP by Larry Ullman

Protect from Prying Eyes: Encryption in Oracle 10g by Arup Nanda

May Dynamic Sampling be an Answer for OLAP Systems’ Statistics Needs?

It is very important to determine when and how often to gather new statistics for Cost Based Optimizer. Especially after 10g since Rule Based Optimizer is now obsolete as a result the Automatics Statistics Gathering mechanism keeps all the statistics current by default after a fresh installation with a scheduled job.

-- Verifying Automatic Statistics Gathering Job
SELECT owner, job_name, enabled
  FROM DBA_SCHEDULER_JOBS
 WHERE JOB_NAME = 'GATHER_STATS_JOB';

The default gathering interval is nightly but you can customize this interval for your needs. The frequency of collection intervals should balance the task of providing accurate statistics and the processing overhead incurred by the statistics collection process. If you want you can disable this job and have your own strategy of course.

-- Disabling Automatic Statistics Gathering Job
BEGIN
  DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');
END;
/ 

Or you can manually gather and lock statistics at the table or schema level(LOCK_SCHEMA_STATS) as another option.

-- Manual statistics strategy example
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES');
  DBMS_STATS.LOCK_TABLE_STATS('HR', 'EMPLOYEES');
END;
/

You can query the STATTYPE_LOCKED column of DBA_TAB_STATISTICS view to determine if the statistics on the table are locked or not and you may use the UNLOCK_TABLE/SCHEMA_STATS procedures to unlock the statistics on a specified table. An important note here is when you lock the statistics on a table all of the dependent statistics are considered locked like table statistics, column statistics, histograms and dependent index statistics.

In an OLAP environment for tables which are being substantially modified in batch operations such as bulk loads usually DBMS_STATS procedure is called as soon as the load operation completes as a part of that ETL step. Also statistics are needed to be gathered manually after new object creation until the configured custom or Oracle’s Automatic Statistics Gathering job process takes action. The statistics in certain cases on these highly volatile tables can be set to NULL and when Oracle encounters a table with no statistics optimizer dynamically gathers the necessary statistics as part of the query optimization. This 10g new feature is called dynamic sampling and is controlled by the OPTIMIZER_DYNAMIC_SAMPLING parameter which should be set to a value of 2 or higher(the default value is 2).

-- Setting statistics to NULL by deleting and then locking the statistics for Dynamic Sampling
BEGIN
  DBMS_STATS.DELETE_TABLE_STATS('HR', 'EMPLOYEES');
  DBMS_STATS.LOCK_TABLE_STATS('HR', 'EMPLOYEES');
END;
/

Dynamic sampling offers the opportunity to collect statistics for segments with either missing statistics or stale statistics. Where as this approach involves an overhead at query optimization time and this action is repeated for the same objects unless statistics are gathered. This usually is not acceptable for OLTP applications but when it comes to OLAP usually parsing time can be discarded compared to the execution time and also avoiding statistics collection overhead on huge tables may be a high motivation for most of the time.

A 100% dynamic sampling-based OLAP system topic was raised and discussed before by David Aldridge here, so any further comments are welcomed.

A Little Story on Selectivity Concept

Oracle’s Query Optimizer’s functions are:
1. Transforming queries
2. Estimating
3. Generating plans

The input to the query transformer is a parsed query block which can be as a complete query, nested subquery or nonmerged view. The main objective of the query transformer is to determine if it is advantageous to change the form of the query so that it enables generation of a better query plan. The transformed query is then sent to the estimator which generates three types of measures:
• Cardinality
• Cost
• And Selectivity

The end goal of the estimator is to estimate the overall cost of a given plan. If statistics are available then the estimator uses them to compute these measures. The information required by the optimizer is higly dependent to accurate statistics which are stored in the data dictionary. If no statistics are available then the optimizer uses either dynamic sampling or an internal default value, depending on the value of the OPTIMIZER_DYNAMIC_SAMPLING initialization parameter.

Here Cardinality represents the number of rows in a row source which can be a base table, a view or the result of a join or GROUP BY operator.

And Cost represents the number of units of work or resources that are used. The query optimizer uses disk I/O, CPU usage and memory usage as units of work.

So lets come to Selectivity, this represents a fraction of rows from a row set which can be a base table, a view or the result of a join or a GROUP BY operator.

When statistics are available the estimator uses them to estimate selectivity, for an equality predicate selectivity is set to the reciprocal of the number of n distinct values of last_name, but if a histogram is available on the equality column then the estimator uses it instead of the number of distinct values. The selectivity depends on the predicate, such as last_name = ‘YILMAZ’ or a combination of predicates such as last_name = ‘YILMAZ’ AND first_name = ‘HASAN TONGUÇ’.

And here comes my little story, my first name Hasan is a very popular religious name in Muslim World, and it was given to me as of my grandfather’s name. My middle name Tonguç is a form of Turkish word Doğuş meaning birth. And my last name Yılmaz means simply who never give up.

During my last two abroad travels to USA and UK I had trouble with my name, I was told that there is a similar person they are after with my first name and last name living in Turkey, shock! But my middle name was the surviving filter in both cases, the Selectivity of my middle name was unique with together my first and last names, thanks to god I was only held for an hour in a room lonely and then left to go in both cases. Now OOW is near and I am nearly sure that I will again be hold and then will thank to my mother for resisting in giving me this Selective middle name :)

New OTN Database Upgrade Forum

After Metalink’s 10g and 11g Upgrade Companions now we also have a dedicated OTN database upgrade forum :)

Forum: Database – Upgrade
- Discuss all aspects of database upgrade from planning and testing through execution and troubleshooting. Also, information about helpful tools and utilities, documentation, and other information to make your database upgrade easier, faster, and less risky. -

Note:601807.1
11g Upgrade Companion

Note:466181.1
10g Upgrade Companion

Some Best Practices of Upgrading Oracle Database
http://tonguc.wordpress.com/2007/08/31/some-best-practices-of-upgrading-oracle-database/

How to use metalink more effectively?

This was my question on OTN forums, thanks for some hints from Hemant K Chitale, but still very poor for today’s web 2.0 era isn’t it?

If you have any comments please reply to the thread here; http://forums.oracle.com/forums/thread.jspa?forumID=29&threadID=687039

Additional Backup Needs and Some Thoughts Which May Help

If you are a DBA of a mission critical database which usually mean there is no tolerance of downtime, data loss and mean time to recover should be minimum, ARCHIVELOG mode and a RMAN hot backup will be a mandatory starting point for a strategy.

The easiest way of scheduling this kind of a backup is done by Database(or Grid) Control, after 10.2 using Oracle’s advised backup strategy option which is a Flash Recovery Area based incremental RMAN backup will be what I advice if you already have enough disk space for FRA. Lutz is not blogging for a while and I miss Lutz’s RMAN posts so I guess this one can be another good quick guide.

Why restore before recover? The new Oracle 10g Backup Strategy

But what if you only want to restore a parameter table with lets say 86 rows, or an apex or some pl/sql application’s previous release, or just a metadata of a simple database object which is now changed or dropped? Here we all have our own workarounds mostly depending on the database version and tools we use around the database. Data Guard and Flashback options if you do have may help or you can customize your Backup strategy for these kind of alternative needs.

In order to restore a parameter table with 86 rows it should be throwing a huge rock to a frog if you go with your RMAN backup, so choosing these kind of parameter tables and including them into a daily binary table level export may be the quickest way to revert them back.

What about a PL/SQL based need, here again a daily norows binary export backup may help a lot.

A data-pump norows backup example

# CREATE or REPLACE DIRECTORY backup_dir AS '/ods01/backup/daily_norows/dp';
/dwh0/app/oracle/product/11.1.0.1/bin/expdp $BACKUP_USERNAME/$BACKUP_PASSWORD
DUMPFILE=daily_full_norows_dp_backup.dmp.$BACKUP_DATE LOGFILE=daily_full_norows_dp_backup.log.$BACKUP_DATE
DIRECTORY=backup_dir FULL=y EXCLUDE=TABLE_DATA

A traditional export norows backup example

/dwh0/app/oracle/product/11.1.0.1/bin/exp $BACKUP_USERNAME/$BACKUP_PASSWORD
FILE=/ods01/backup/daily_norows/exp/daily_full_norows_dp_backup.dmp.$BACKUP_DATE
LOG=/ods01/backup/daily_norows/exp/daily_full_norows_dp_backup.log.$BACKUP_DATE
FULL=y ROWS=n

And what if you are using Apex applications or Warehouse Builder repository, then again taking daily tool level export backups will help.

OWB 11g Repository Shell Script Backup Example

There can be additional thoughts of course, like saving database structure as a SQL file daily or immediately after any structure change.

A Simple Backup Control File to Trace Shell Script Example

/dwh0/app/oracle/product/11.1.0.1/bin/sqlplus -s > /ods01/backup/daily_norows/backup_control_file_to_trace.log.$BACKUP_DATE
2> /ods01/backup/daily_norows/backup_control_file_to_trace.err.$BACKUP_DATE <<EOF
$BACKUP_USERNAME/$BACKUP_PASSWORD
@/ods01/backup/daily_norows/backup_control_file_to_trace.sql $BACKUP_DATE
exit ;
EOF

fatih@oracle $ cat /ods01/backup/daily_norows/backup_control_file_to_trace.sql
alter session set tracefile_identifier = backup_controlfile_to_trace;
alter database backup controlfile to trace;
SET ECHO OFF
SET FEEDBACK OFF
SET PAGESIZE 0
SET LINESIZE 1000
spool /ods01/backup/daily_norows/backup_control_file_to_trace.name.&1
-- available after 11.1
select value from v$diag_info where name like 'Default Trace%' ;
spool off
SET FEEDBACK ON
SET PAGESIZE 24
SET ECHO ON

But I think critical point of all is that whatever you do do not forget to use a scheduler to automatize these jobs and control the logs of them periodically :)

Simple unix cron settings to automatize daily backups

fatih@oracle $ crontab -l
40 17 * * * /ods01/backup/daily_norows/daily_norows.sh > /ods01/backup/daily_norows/daily_norows.log
2> /ods01/backup/daily_norows/daily_norows.err
00 18 * * * /ods01/backup/backup_repos.sh > /ods01/backup/backup_repos.log
2> /ods01/backup/backup_repos.err