A gotcha of Data Pump export

Today while I am answering one of OTN forum question about consistency of data pump export. I found I was under false impression that Data Pump by default guarantee dump file data consistency among all the tables. Because the Oracle Data Pump document was stating under the section named "How Data Pump Export Parameters Map to Those of the Original Export Utility" ,
http://download.oracle.com/docs/cd/B28359_01/server.111/b28319/dp_export.htm#i1005864
"A parameter comparable to CONSISTENT is not needed"

This statement is kinda misleading, it's easy to give you false impression that Data Pump will guarentee consistency so that CONSISTENT is not needed.
Oracle now revised the document to
"A parameter comparable to CONSISTENT is not needed. Use FLASHBACK_SCN and FLASHBACK_TIME for this functionality."
which is a little better.

To get current SCN use.
select dbms_flashback.get_system_change_number from dual;

To make things worse some expdp has this header imbeded in their output message, which is even more misleading.

Export: Release 10.2.0.3.0 - 64bit Production on Friday, 05 September, 2008 13:59:59 Copyright (c) 2003, 2005, Oracle. All rights reserved. ;;; Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

FLASHBACK automatically enabled to preserve database integrity. Starting

"SYSTEM"."SYS_EXPORT_SCHEMA_02": system/******** directory=flash dumpfile=usr001_1.dmp logfile=exp_usr001_1.log

So that you know that Oracle Data Pump, up until version 11.1.0.6, doesn't guarantee data consistency among tables in a dump file. It's only guarantee point-in-time consistency of the table being exported.

also reference

Expdp Message "FLASHBACK automatically enabled" Does Not Guarantee Export Consistency
Doc ID:
377218.1

Quick Optimizer STATISTICS transfer between schemas

Oracle DBMS_STATS provide a quick way to transfer Optimizer Statistics.
The basic step are:

1. create stats table to hold exported stats data.
   EXEC DBMS_STATS.create_stat_table('SCHEMA1','ST_TABLE');
   
2. export stats to stats table
   EXEC DBMS_STATS.export_schema_stats('SCHEMA1','ST_TABLE',NULL,'SCHEMA1');
   
3. transfer the stats table ST_TABLE to destinate location using method of choice, like exp/imp, create tabel as select ..., sqlplus copy etc.
   
4. import the stats into schema
   EXEC DBMS_STATS.import_schema_stats('SCHEMA1','ST_TABLE',NULL,'SCHEMA1');
   
5. If the schema name is different, for example you need to import schema1's stats to schema2, then you need to update stats table column C5 to change the owner name from schema1 to schema2
   update ST_TABLE set C5='SCHEMA2';
   then do the import.

Actually, DBMS_STATS provided a way to transfer stats between schema. If schema name is not same. use statown

EXEC DBMS_STATS.import_schema_stats('SCHEMA2','ST_TABLE',NULL,'SCHEMA1', statown=>'SCHEMA1');

The importance of proper BACKUP!

Come across a techcrunch.com article today. A blog site called journalspace.com been completely wiped out after few years of operation simply because ex-IT person deliberately overwritten all the data on SQL server. And guess what, they were only using RAID mirror drives as 'Backup'.

http://www.techcrunch.com/2009/01/03/journalspace-drama-all-data-lost-without-backup-company-deadpooled/

This might be one of the extreme cases, but it's certainly a wake up call to many companies that gave up traditional tape backup and rely solely on standby and replication technologies.

In the case of Oracle database, I know a famous financial website didn't backup their RAC production servers. They have setup multiple Data Guard standby servers and replicate data remotely to off site servers. They even setup two days delayed log apply mechanism to counter bad data contamination. But is that enough? Well it seems pretty well covered all potential hardware and system failures. In most events they can bring production servers back relatively quick without painful slow tape restore. Cool huh.

But they over looked one of the most common and a lot of time most deadly form of system failures -- Human errors either accidentally or maliciously
Whatif a developer accidentally introduced an application bug into system, updated some records and wasn't noticed until two days later? Of course you can say let's increased the delay log apply to 7 days. Hmm, whatif you didn't find the bug 8 days later? You can't indefinitely increase the log apply. Besides this particular website has millions of users doing thousands of online transactions every second. It's not hard to imagine the cost of saving all the transaction logs for many days.

Till now, tape backup is still the most cost effective massive long term backup method. A lot of modern technologies like flashback database, Data Guard, RAC, Replication and storage snapshot etc have been introduced in last few years to help ease DBA's burden of database recovery. But so far they can only cover the database failures in the matter of days, they will not completely replace tape backup anytime soon.

Yet another RMAN bug

One of our RMAN backup failed with

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of delete command at 10/22/2008 00:30:31
RMAN-03014: implicit resync of recovery catalog failed
RMAN-03009: failure of full resync command on default channel at 10/22/2008 00:30:31
ORA-00001: unique constraint (RMANCAT.TF_U2) violated

Which is perfect hit for bug

Rman Resync Fails After Adding Temp Data File ORA-00001 on TF_U2
Doc ID:
NOTE:402352.1

According to the doc, this bug supposed to be fixed in 10.2.0.2 but we are using 10.2.0.3.
10.2.0.4 patchset note specifically mentioned this bug in fixed list so your safe bet is upgrade to 10.2.0.4

The dangerous part is the cause of this problem. After you dropped a tempfile and recreated a new one with bigger size, expecting your RMAN backup fail tonight with this error if you are using 10.2.0.3 and earlier.

RMAN catalog table TF has a unique key on ("DBINC_KEY", "TS#", "TS_CREATE_SCN", "FILE#"), it's turned out Oracle used the same FILE# but somehow forget to use new TS_CREATE_SCN

ALTER TABLE "RCAT"."TF" ADD CONSTRAINT "TF_U2" UNIQUE ("DBINC_KEY", "TS#", "TS_CREATE_SCN", "FILE#")

Anyway, just something you need to remember after your changed your TEMP tablespace. Or yet another reason to stay fully patched to terminal release.

Addition,

Looks like there are few other people had the same problem from OTN forum, let me include some steps to tackle this problem. Since you need to remove the duplicate record that causing the error, first you need to identify the problem record.

1. Find out the DBINC_KEY, if your RMAN catalog only serving one database, it's easy. But in most cases, you have multiple instances. You need to find out DBINC_KEY of your instance by DBID. Your DBID will show when you connect to RMAN,

connected to target database: ENGDB (DBID=620206583)
Or, select dbid from v$database;

select DBID,NAME,RESETLOGS_TIME, DBINC_KEY
from rc_database_incarnation where dbid=620206583

DBID NAME RESETLOGS DBINC_KEY
---------- -------- --------- ----------
620206583 EDB 06-DEC-06 21822 620206583 EDB 22-OCT-05 21828

2. Find out the problem file#

select "DBINC_KEY", "TS#", "TS_CREATE_SCN", "FILE#"
from tf where DBINC_KEY=21822;

3. Take a note and remove the record from TF_U2

Do a resync catalog using RMAN after delete. With the duplicate record removed the resync should finish.

RMAN backup failed with ORA-01400

The backup of one of our production servers suddenly has following errors in the log.

RMAN-03014: implicit resync of recovery catalog failed

RMAN-03009: failure of partial resync command on default channel at 10/06/2008 10:54:54

ORA-01400: cannot insert NULL into ("RMANCAT"."ROUT"."ROUT_SKEY")

It looks like this is a hit of Oracle bug

Bug No:5528078

ORA-01400: CANNOT INSERT NULL INTO ("RMAN"."ROUT"."ROUT_SKEY")

The strange thing is I didn't had any changes lately in production. I am not quite sure what event has triggered this bug.

The workaround involve changing script $ORACLE_HOME/rdbms/admin/recover.bsq and UPGRADE CATALOG .

This error usually happens after database migration.

Strange Temporary Tablespace problem

Yesterday morning, one user from Application group sent me an email regarding a failed production procedure of loading process.

The error was
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

From the error itself, it's easy to make you believe this is TEMP tablespace space issue. Or the procedure doing large sorting operation. However, this production database has 32G temporary tablespace the combined production data is only around 10G.

So it's rather something went wrong than real space problem. And this procedure was running ok before.

With the help of OEM Grid Control and AWR report snapshot. I quickly find out the culprit query, which is

SELECT A.PRODUCT_ID,A.VENDOR_ID,A.PROD_AREA,B.ALT_GROUP,B.GRADE_SET,B.GRADE,C.BOG_ID,C.AT_STEP,C.STEP_PRIORITY,C.POWER_SPEED,C.ROUTE,C.TOPMARK, CFI,MIN_LOT_SIZE MINLOT,MAX_LOT_SIZE MAXLOT,STD_LOT_SIZE STDLOT,INCR_LOT_SIZE INCRLOT FROM MDMSCP.SAT_PRODUCT_VENDOR A, MDMSCP.SAT_BOM B, TMP_SAT_BOG CWHERE A.PRODUCT_ID = B.PARENT_PART_ID AND B.CHILD_PART_ID = C.BOG_ID AND B.ALT_GROUP = C.NAME ORDER BY A.PRODUCT_ID,B.GRADE DESC,STEP_PRIORITY

However, it's hard for me to make sense the problem. The execute plan revealed that optimizer has chosen a very bad execute path for this particular query. Instead of join A and B with correct condition, the optimizer used a MERGE JOIN CARTESIAN to join A and C first. Which went terribly wrong, with 150K records in each table, Oracle is merging a whopping 22500000000 records! It's easily defeated our temporary tablespace.
From the wrong plan I noticed that optimizer somehow think table A only have 1 row. A checking on statistics revealed that both A and B has wrong statistics that reporting these two are empty tables. So optimizer just did whatever.
After collection of statistics, the execution plan make a lot more sense, it started join A and B first and refer C later.

It's again approved how important to have correct statistics collected for your schema. Otherwise even a small query can screw up your database big time.

P.S. While doing investigation on this issue, I come accross Janaton's good write up about MERGE JOIN CARTESIAN

http://jonathanlewis.wordpress.com/2006/12/13/cartesian-merge-join/

Large TCP Socket (KGAS) event wait

One of our dev database has a large number of TCP Socket (KGAS) event waits when a piece of PL/SQL code runs.

SYS@dev>
select active_session_history.event,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history
where active_session_history.sample_time between
sysdate - 120/2880 and sysdate
group by active_session_history.event
order by 2 desc;

EVENT TTL_WAIT_TIME
------------------- -------------
TCP Socket (KGAS) 843316255
log file sync 1912981
.....

I check the Oracle reference of TCP Socket wait events, it says,

KGAS is a component in the server which handles TCP/IP sockets which is typically used in dedicated connections in 10.2+ by some
PLSQL built in packages such as UTL_HTTP and UTL_TCP.

However, in this particular piece of code, there's no such package called, Momen blogged about the same event when he's using SMTP package. But it looks like this doesn't apply to us.

http://momendba.blogspot.com/2007/03/tcp-socket-kgas-wait-event.html

I then looked into metalink, I found this Doc,

''TCP Socket (Kgas)'' Waits Present in 10.2
Doc ID:
Note:416451.1

It basically says this event is merely reporting some network related event, it's not threatening performance. The conclusion is this event can be safely ignored :D

Well, I hope Oracle could have fixed the bug in 10.2.0.4 and 11g, so that reporting of event in more DBA comforting method.