Oracle vs. SQL Server – a never ending story

11.1 vs. 2008


10.2 vs. 2005

but always two things to keep in mind;

– Oracle is not cheap and getting more expensive with its new options on recent releases.
– SQL Server is cheaper but does not run on any other operating system but just Microsoft platform.

so why not going after another path :)

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/ $BACKUP_USERNAME/$BACKUP_PASSWORD 
DUMPFILE=daily_full_norows_dp_backup.dmp.$BACKUP_DATE LOGFILE=daily_full_norows_dp_backup.log.$BACKUP_DATE 

A traditional export norows backup example

/dwh0/app/oracle/product/ $BACKUP_USERNAME/$BACKUP_PASSWORD 

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/ -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
@/ods01/backup/daily_norows/backup_control_file_to_trace.sql $BACKUP_DATE 
exit ;

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              
spool /ods01/backup/daily_norows/
-- available after 11.1
select value from v$diag_info where name like 'Default Trace%' ;
spool off

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/ > /ods01/backup/daily_norows/daily_norows.log 
2> /ods01/backup/daily_norows/daily_norows.err
00 18 * * * /ods01/backup/ > /ods01/backup/backup_repos.log 
2> /ods01/backup/backup_repos.err

Which tool is better to save the valuable information, forums or blogs?

Ravi R. is someone I admire because he puts a lot of his efforts in understanding the beautiful feature of 10g, the Scheduler, on OTN Database » Scheduler Forum. His two sticky like posts helped me before;

Answers to “Why are my jobs not running?”


There are more sticky like posts in other OTN forums like;

When your query takes too long of Rob van Wijk

Introduction to regular expressions of cd

One can reference these kind of sticky like posts to “please before asking search in this forum or documentation” type of questions like a documentation reference. But I was thinking of what if these were a blog post on wordpress or blogspot? I think they may get a higher return for the community, so this post may be a startup than :)

Some Useful Oracle Database Utilities and Errorlogging Feature of 11g’s SQL*Plus

Some Oracle utilities are well documented in the Oracle Database Utilities Guide but some are not. Some are very familiar to us; exp, imp, expdp, impdp, sqlldr, dbverify, csscan, tnsping, lsnrctl, emctl, tkprof etc. But some may be not; trcroute, oradebug, dbshut, dbstart, oerr, orakill etc. Here are some simple examples I use frequently on Linux.

[oracle@tonghost bin]$ oerr ora 600
00600, 00000, "internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]"
// *Cause:  This is the generic internal error number for Oracle program
//         exceptions.  This indicated that a process encountered an
//         exceptional condition.
// *Action: Report as a bug - the first argument is the internal error number.

[oracle@tonghost bin]$ dbhome

[oracle@tonghost bin]$ dbfsize /u01/app/oracle/oradata/tong/example01.dbf

Database file: /u01/app/oracle/oradata/tong/example01.dbf
Database file type: file system
Database file size: 12800 8192 byte blocks

[oracle@tonghost bin]$ dbshut /u01/app/oracle/product/11.1.0/db_1
Processing Database instance "tong": log file /u01/app/oracle/product/11.1.0/db_1/shutdown.log

[oracle@tonghost bin]$ dbstart /u01/app/oracle/product/11.1.0/db_1
Processing Database instance "tong": log file /u01/app/oracle/product/11.1.0/db_1/startup.log

With 11g sql*plus’s errorlogging feature we can store any SQL, PL/SQL or SQL*Plus errors by default in table SPERRORLOG.

conn / as sysdba
create user tong identified by tong;
grant create session to tong;
grant unlimited tablespace to tong;
create table tong.tong_sperrorlog(username   varchar(256),                  
				timestamp  TIMESTAMP,                     
				script     varchar(1024),                 
				identifier varchar(256),                  
				message    CLOB,                          
				statement  CLOB);                         
connect tong/tong
set linesize 1200
show errorlogging
set errorlogging on table tong_sperrorlog   

create table tong ( c1 number ) ;

set errorlogging off

select timestamp, statement, message from tong_sperrorlog;

TIMESTAMP       22-JUN-08 PM                                                                                                                                     
STATEMENT		create table tong ( c1 number )
MESSAGE			ORA-01031: insufficient privileges

This feature can be also used with 11g client against 9i and 10g servers. Check $ORACLE_HOME/sqlplus/doc/elgsetup.txt for details.

Like in the old fairy tales if I could have 3 wish chances from the big boss.

1. Support Issues

I would wish Oracle to segment its customers due to both their financial value and Oracle know-how.

I really do want to see someone who knows more than me when I open an SR, after all those years of experience if I am opening a SR this really means something is wrong and I need immediate, to the point support.

In my company if one of our VIP customer calls our call-center we recognize him and guide him to a special team of call-center agents. There he experiences the SEVERITY 0 type of support, this is 21st century basic need for any company to full fill its customer loyalty.

2. Licensing Issues

I would wish Oracle to enlarge XEs limitations, enrich APEX and SQL Developer to the ends.

MS is so active at Universities that nearly no Computer Science student can have the chance to meet Oracle Database software until he is recruited. But after XE things started to change.

3. Acquired Software Company Issues

I would wish Oracle to provide “Oracle Database Friendly” versions of acquired softwares starting with Siebel and BEA.

I want to utilize my database investment, I do not want to experience another common database independent solution because I am not running MySQL but Oracle.

So what would you wish? :)

Oracle 10g flashback features’ limitations and restrictions

Tonight as usual I was checking the Oracle blogs and popular OTN forum threads, Why the dropped table not in recyclebin? thread got my attention.

Especially after Oracle 10g below flashback features makes a big difference in Oracle DBAs’ and Developers’ lives;
• Flashback Database
• Flashback Drop
• Flashback Table
• Flashback Versions Query
• Flashback Transaction Query

Once upon a time I tried to summaries these features with my post; Oracle 10g Flashback Versions Query, Drop Table and Recyclebin Management But good things, especially new features, have their own alternative costs like bugs, limitations or restrictions which are usually lived and learned. These kind of information you can not find in the documentation or in the blog posts like mine above which usually try to give basic samples which are only designed to demonstrate the related features but not to stress them.

So lets start with some quick reminders; Flashback Table, Flashback Query, Flashback Transaction Query and Flashback Version Query are all based on undo(rollback) data which primarily provides read consistency and rolling back transactions in Oracle. But Flashback Database is based on before images in the flashback logs. And Flashback Drop is based on another 10g new feature called Recycle Bin which is there to manage dropped database objects until their space is needed to store new data. Each Flashback option has lots of interesting limitations or restrictions, I tried to summaries them from the related metalink notes, this place is like an unlimited gold mine for an Oracle man.

Note 249319.1 – Configure flashback database
Note 270060.1 – Use Flashback Table Feature and Resolve errors
Note 270535.1 – Restrictions on Flashback Table Feature
Note:435998.1 – What Do All 10g Flashback Features Rely on and what are their Limitations
Note 317499.1 – 10G Oracle Flashback Transaction Query – Introduction and usage
Note 369755.1 – Flashback Logs-Space management

Limitations of Flashback Database

• Flashback Database can only undo changes to a datafile made by an Oracle database. It cannot be used to repair media failures, or to recover from accidental deletion of datafiles.
• You cannot use Flashback Database to undo a shrink datafile operation.
• If the database control file is restored from backup or re-created, all accumulated flashback log information is discarded. You cannot use FLASHBACK DATABASE to return to a point in time before the restore or re-creation of a control file.
• When using Flashback Database with a target time at which a NOLOGGING operation was in progress, block corruption is likely in the database objects and datafiles affected by the NOLOGGING operation. If you expect to use Flashback Database to return to a point in time during an operation such as a direct-path INSERT, consider performing the operation in LOGGING mode.
• Shrinking a datafile or dropping a tablespace can prevent flashing back the Database

Limitations and Restrictions on Flashback Drop

• The recycle bin functionality is only available for non-system, locally managed tablespaces. If a table is in a non-system, locally managed tablespace, but one or more of its dependent segments(objects) is in a dictionary-managed tablespace, then these objects are also protected by the recycle bin.
• There is no fixed amount of space allocated to the recycle bin, and no guarantee as to how long dropped objects remain in the recycle bin. Depending upon system activity, a dropped object may remain in the recycle bin for seconds, or for months.
• While Oracle permits queries against objects stored in the recycle bin, you cannot use DML or DDL statements on objects in the recycle bin.
• You can perform Flashback Query on tables in the recycle bin, but only by using the recycle bin name. You cannot use the original name of the table.
• A table and all of its dependent objects (indexes (except for bitmap join indexes), LOB segments, nested tables, triggers, constraints and so on) go into the recycle bin together, when you drop the table. Likewise, when you perform Flashback Drop, the objects are generally all retrieved together , It is possible, however, that some dependent objects such as indexes may have been reclaimed due to space pressure. In such cases, the reclaimed dependent objects are not retrieved from the recycle bin.
• Due to security concerns, tables which have Fine-Grained Auditing (FGA) and Virtual Private Database (VPD) policies defined over them are not protected by the recycle bin.
• Partitioned index-organized tables are not protected by the recycle bin.
• The recycle bin does not preserve referential constraints on a table (though other constraints will be preserved if possible). If a table had referential constraints before it was dropped (that is, placed in the recycle bin), then re-create any referential constraints after you retrieve the table from the recycle bin with Flashback Drop.
• When you drop a table, all materialized view logs defined on the table are also dropped but are not placed in the recycle bin. Therefore, the materialized view logs cannot be flashed back along with the table.
• When you drop a table, any indexes on the table are dropped and put into the recycle bin along with the table. If subsequent space pressures arise, then the database reclaims space from the recycle bin by first purging indexes. In this case, when you flash back the table, you may not get back all of the indexes that were defined on the table.
• You cannot flash back a table if it has been purged, either by a user or by Oracle Database as a result of some space reclamation operation.
• Oracle Database retrieves all indexes defined on the table retrieved from the recycle bin except for bitmap join indexes. (Bitmap join indexes are not put in the recycle bin during a DROP TABLE operation, so cannot be retrieved.)

Limitations and Restrictions on Flashback Table

• Flashback Table operations are not valid for the following type objects: tables that are part of a cluster, materialized views, Advanced Queuing (AQ) tables, static data dictionary tables, system tables, remote tables, object tables, nested tables, or individual table partitions or subpartitions.
• The following DDL operations change the structure of a table, so that you cannot subsequently use the TO SCN or TO TIMESTAMP clause to flash the table back to a time preceding the operation: upgrading, moving, or truncating a table; adding a constraint to a table, adding a table to a cluster; modifying or dropping a column; adding, dropping, merging, splitting, coalescing, or truncating a partition or subpartition (with the exception of adding a range partition).
• You cannot rollback a FLASHBACK TABLE statement. However, you can issue another FLASHBACK TABLE statement and specify a time just prior to the current time. Therefore, it is advisable to record the current SCN before issuing a FLASHBACK TABLE clause.

Limitations and Restrictions on Flashback Query

• Flashback Query is Not available after restarting the database.
• You cannot specify a subquery in the expression of the AS OF clause.
• You cannot use the VERSIONS clause in flashback queries to temporary , external tables, fixed tables, or tables that are part of a cluster.
• You cannot use the VERSIONS clause in flashback queries to views. However, you can use the VERSIONS syntax in the defining query of a view.
• You cannot specify this clause if you have specified query_name in the query_table_expression.
• Flashback Query does not undo anything. It is only a query mechanism. You can take the output from a Flashback Query and perform an undo yourself in many circumstances.
• Flashback Query does not tell you what changed. LogMiner does that.
• Flashback Query can undo changes and can be very efficient if you know the rows that need to be moved back in time. You can use it to move a full table back in time, but this is very expensive if the table is large since it involves a full table copy.
• Flashback Query does not work through DDL operations that modify columns, or drop or truncate tables.
• LogMiner is very good for getting change history, but it gives you changes in terms of deltas (insert, update, delete), not in terms of the before and after image of a row. These can be difficult to deal with in some applications.

ps: football is a very interesting game, I believe we played good at most only 30 minutes at the first 3 games and this was enough for this young team to the semi-finals. Czech game was one of the most dramatic games I have ever watched, we came back from 2-0 against Chelsea’s goal keeper Petr Čech.

Last world cup finalists France, last European Champion Greece or cradle of football England are not, but my country Türkiye is now in the first 8 and all 70 million are locked to the Croatia game. After that game if Germans let us we want Portugal back! :)

What about today’s Oracle database developers’ business process and data modeling needs?

It was the good old days of Client-Server applications and at that times my company was migrating our applications from Powerbuilder to Oracle Forms. I was so fan of Powerbuilder and also so young, this change was making me sad and crazy.

Last week I met and worked with Power Designer, a very good data modeling tool to my first impressions. This success reminded me how I was miserable after Forms migrations and still after 10+ years Oracle’s only answer to data modeling seems to be Designer.

I did several searches on OTN and Oracle documentation, only alternative poped up to be Jdeveloper which Oracle database developer’s rarely installs after all those years;

Today’s Designer is still inside Oracle Developer Suite 10g and is still seems to be closely integrated with Oracle Forms only.

We now have APEX inside 11g database installation, cool
We now have OWB inside 11g database installation, cool+
We now have SQL Developer inside 11g database installation, cool++

But what about today’s Oracle database developers’ business process and data modeling needs? I hope Oracle has plans for SQL Developer at least to have some designer capabilities soon.

Until that day Power Designer rocks! :)

PS : one need, several solutions, so who says SQL is always faster than PL/SQL? :)

Not to miss these kind of discussions you may register to OTN Forums > SQL and PL/SQL Forum > Popular Threads RSS listed here;

DATABASE is my home and SQL is my path

Excel is one of the all time best tools one can develop, I totally agree.

But if you are a database developer and like benefiting from the power of SQL for any kind of your daily need, you also most probably try to accomplish whatever you do inside the your database. For example you also prefer DBMS_SCEDULER to unix cron, in order to query whatever happened by simple SQLs over ALL_SCHEDULER_JOB_LOG and ALL_SCHEDULER_JOB_RUN_DETAILS. Or you also use V$RMAN_BACKUP_JOB_DETAILS to query your RMAN backup logs, not telnet and grep error messages frmo the unix log files.

This morning I was lost inside an Excel document which was designed to guide a warehouse builder developer to map the Siebel source tables and columns. So I decided to attach the information I hardly mined onto my target tables’ and columns’ comments for future just in case :)

create table  SBL_ASSET  (
   ASSET_ID             VARCHAR2(60),
   GSM                  VARCHAR2(200),
   ACCOUNT_ID           VARCHAR2(60), 
   NPRD                 VARCHAR2(400)  

comment on table  SBL_ASSET is 'S_ASSET, S_PROD_INT' ;
comment on column SBL_ASSET.ASSET_ID is 'S_ASSET.ROW_ID' ; 
comment on column SBL_ASSET.GSM is 'S_ASSET.X_GSM_NUMBER' ;
comment on column SBL_ASSET.NPRD is 'S_ASSET.ASSET_NUM' ; 

So what is the good for all of these extra commenting? Here is the catch, with a simple query now anyone who can SQL will get the mapping informations;

SELECT a.column_name || ',' column_name, a.comments || ',' comments
  FROM dba_col_comments a
 WHERE a.owner = 'ODS'
   AND a.table_name IN ('SBL_ASSET');
COLUMN_NAME                     COMMENTS
------------------------------- --------------------------------------------------------------------------------
ASSET_ID,                       S_ASSET.ROW_ID,
GSM,                            S_ASSET.X_GSM_NUMBER,
ACCOUNT_ID,                     S_ASSET.OWNER_ACCNT_ID,
NPRD,                           S_ASSET.ASSET_NUM,  

For example you can consume this information in order to build a simple OWB map or SQL loading script, just like PL/SQL commenting; on database documentation.


INSERT /*+ APPEND */ INTO ods.sbl_asset
   (asset_id, gsm, account_id, 
   SELECT s_asset.row_id,
     FROM src_siebel.s_asset, src_siebel.s_prod_int
    WHERE s_asset.prod_id = s_prod_int.row_id
      AND = 'TC_PRODUCT';


Like Tom Kyte advices, we, database developers, do things “in” the database. ;)