Where ORACLE is not just another database

GATHER_TABLE_STATS and ORA-01652

2011-11-20T21:20:00.000-08:00

We had ORA-01652 error from one production database recently. The culprit is GATHER_TABLE_STATS job. By the way, this DB is 10.2.0.4

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
*** 2011-11-20 20:28:51.105
GATHER_STATS_JOB: GATHER_TABLE_STATS('"L53"','"L_CARD"','""', ...)
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

Originally it was error out and paging at 4AM which is really not preferred timing for On Call DBA.
Since this DB tend to have higher load during early morning any way. I changed maintenance windows to the late afternoon.
To change this auto statistics collection job time use this command.

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE (
name => 'GATHER_STATS_JOB',
attribute => 'repeat_interval',
value => 'freq=daily;byday=SUN,MON,TUE,WED,THU,FRI,SAT;byhour=17;byminute=0; bysecond=0');
END;

However this didn't address the root cause of the issue apparently. A couple of days later the job failed again with same error.

Increase TEMP tablespace is not an option. The TEMP TBS on this DB is 95G. This job run for 3 hours and used them all. Adding more TEMP will only delay the inevitable.

I decide to changed estimate percent from auto sampling to 1% hope this will fix the issue. I did some research on google about this but there's not much useful past discussion.
Only found this asktom thread pretty helpful by pointing the right statement to change the default GATHER_TABLE_STATS job
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:652425700346984666

ops$tkyte%ORA10GR2> select dbms_stats.get_param( 'estimate_percent' ) from dual;

DBMS_STATS.GET_PARAM('ESTIMATE_PERCENT')
-------------------------------------------------------------------------------
DBMS_STATS.AUTO_SAMPLE_SIZE

reset or set them with these:

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_stats.htm#i1047505

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_stats.htm#i1048566

How to connect wireless router to another wireless router

2011-09-07T21:20:00.000-07:00

This post is not related to Oracle. It just a hint for a problem I found while I was setting up my home network by connecting a new wireless router to an existing one.

In beginning, this task seems super easy and no brainer to me. Just connect 'Internet' port of new router to any Local ethernet port on existing router and setup new router and Wala!

Oh well, it doesn't work. The new router keeps complaining it's not connected to internet. The Internet setup page show it get 127.0.0.1 (localhost) as DHCP address from old router and that doesn't work obviously. Actually some network guru probably already figured out of the problem when they saw this.

So why it's get a 127.0.0.1 address instead of a valid DHCP release? Well, the trick is most router by default using 192.168.1.1 address and subnet. So if two routers sharing the same address, of course the new one will get localhost as address thinking he is 192.168.1.1

The solution is easy, change the new router's default subnet to 192.168.2.1 etc. or change new router's IP to something like 192.168.1.10

ORA-01555 with Query Duration=0 sec

2011-08-29T11:44:00.000-07:00

Most DBAs know that ORA-1555 is caused by long running query. And in alert.log file it will tell you which SQL caused ORA-1555 and run for how long.
However from time to time you will see errors like this. It's basically tell you that the query failed right away. So why's the case?

Mon Aug 29 06:39:09 2011

ORA-01555 caused by SQL statement below (SQL ID: 0jc2g6km899ps, Query Duration=0 sec, SCN: 0x00ae.75483a06):

Mon Aug 29 06:39:09 2011

SELECT.xxxx

I ran a query to find out the time stamp of this query's SCN and found out that the query has a time stamp of 6AM. But i was failed 40 min later. That could only mean one thing that it was in a transaction that started 6AM and Oracle already over written the data in UNDO.

SYS@VAULTPROD>select scn_to_timestamp(749291977222) from dual;

SCN_TO_TIMESTAMP(749291977222)

---------------------------------------------------------------------------

29-AUG-11 06.00.01.000000000 AM

There’s a couple of ways to help improve the situation.

Does all the statements in this job need to be in single transaction? If not, don’t put them into single transaction.
Increase undo retention of DB, Oracle will try to honor this retention subject to UNDO space.
Increase the UNDO tablespace to mitigate the potential space squeeze but remember the reason we got this error is not from UNDO space limitation.

Data pump expdp failed with DMSYS related errors ORA-39126 ORA-06512 etc

2011-03-09T10:17:00.000-08:00

Today one of our data pump export/import jobs failed with errors attached at the bottom. The process working fine before our 11g upgrade.
I did a little research and found metalink doc 304449.1 has perfect solution.

The problem is we removed some unused database options before we upgrade from 10g to 11g.
The reason is because with all these unnecessary options, the upgrade scripts will run almost two hours. Removing them the upgrade will finish in 15 minutes.

It turns out DMSYS data mining option is among them, but somehow Oracle didn't cleanly remove the option with some left over records in data pump export table.

The solution in this case is delete these records,

SQL> DELETE FROM exppkgact$ WHERE SCHEMA='DMSYS';
SQL> commit;

There are other potential causes for the same error. You can check the metalink doc for more info.

Database Data Pump Export fails with PLS-00201 identifier DMSYS.DBMS_MODEL_EXP must be declared [ID 304449.1]

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_11": userid=system/********@TEST parfile=/home/oracle/dba/sql/DWS.par
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.GET_TABLE_DATA_OBJECTS []
ORA-31642: the following SQL statement fails:
BEGIN "DMSYS"."DBMS_DM_MODEL_EXP".SCHEMA_CALLOUT(:1,0,1,'11.02.00.00.00'); END;
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_METADATA", line 1245
ORA-04063: package body "DMSYS.DBMS_DM_MODEL_EXP" has errors
ORA-06508: PL/SQL: could not find program unit being called: "DMSYS.DBMS_DM_MODEL_EXP"
ORA-06512: at "SYS.DBMS_METADATA", line 5300
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.KUPW$WORKER", line 8159
----- PL/SQL Call Stack -----
object line object
handle number name
70000007ddbc258 19028 package body SYS.KUPW$WORKER
70000007ddbc258 8191 package body SYS.KUPW$WORKER
70000007ddbc258 12728 package body SYS.KUPW$WORKER
70000007ddbc258 4618 package body SYS.KUPW$WORKER
70000007ddbc258 8902 package body SYS.KUPW$WORKER
70000007ddbc258 1651 package body SYS.KUPW$WORKER
70000007eaf9060 2 anonymous block

Oracle won't do partition pruning on MAX/MIN query of partition key.

2011-01-31T17:29:00.000-08:00

Oracle doesn’t do a partition pruning on MAX/MIN query on partition key. Even it makes perfect sense for Oracle to scan only the partition that has MAX/MIN value. And this is not something new, the user community certainly noticed this.

http://www.oramoss.com/blog/2009/06/no-pruning-for-minmax-of-partition-key.html

Right now, all we can do is some work around. For example one of our database use this query to figure out MAX AGG_DATE as part of daily ETL process. AGG_DATE is partition key of the table and not indexed.
So the plan looks like this,
Ouch and yes, the Pstart is 1 and Pstop is 1149. Oracle scan all 1149 partitions of the table and it took a long time to run this query.

SQL> explain plan for SELECT max(AGG_DATE) from (SELECT "A1"."AGG_DATE" FROM "WEB_APPS"."COUNTER_DAY_AGG" "A1" order by AGG_DATE desc );
Explained.
SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
------------------------------------
Plan hash value: 4125776214
----------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 10M (2)| 34:45:45 | | |
| 1 | SORT AGGREGATE | | 1 | 8 | | | | |
| 2 | PARTITION RANGE ALL| | 5196M| 38G| 10M (2)| 34:45:45 | 1 | 1149 |
| 3 | TABLE ACCESS FULL | COUNTER_DAY_AGG | 5196M| 38G| 10M (2)| 34:45:45 | 1 | 1149 |
----------------------------------------------------------------------------------

Since this our daily job, the work around I put in is where clause.

The plan looks better after that, Pstart is now KEY instead 1. In our case it will scan 7 daily partitions.

The stats give bogus running time estimate. The actual run time benefit we had is reduce from 20 minutes to 1 minute.

SQL> explain plan for SELECT MAX("A1"."AGG_DATE") FROM "ODS_WEB_APPS"."COUNTER_DAY_AGG" "A1" where AGG_DATE > sysdate-7;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------Plan hash value: 1669369268

----------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |

------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 8 | 10M (4)| 36:09:51 | | |

| 1 | SORT AGGREGATE | | 1 | 8 | | | | |

| 2 | PARTITION RANGE ITERATOR| | 6919K| 52M| 10M (4)| 36:09:51 | KEY | 1149 |

|* 3 | TABLE ACCESS FULL | COUNTER_DAY_AGG | 6919K| 52M| 10M (4)| 36:09:51 | KEY | 1149 |

----------------------------------------------------------------------------------

Of course there's one trade off of this work around. It will limit the script ability of catch up loading. The script use this query to find out maxium loading date and catch up load from that date. So if our loading didn't run for more than 7 days our script won't be able to catchup. I guess that's something we can live with, it's not possible that we didn't notice our daily ETL job was not running for past 7 days :) Even that really happens, we can still treat it individually.

ORA-01591 and quick solution

2010-10-29T09:38:00.000-07:00

One of the user reported they got this error from application.

ORA-01591: lock held by in-doubt distributed transaction 4.7.533420

We don't really see this error often. So I did a little research.

The error message doc from Oracle has pretty good explanation but didn't provide a solution how to resolve this.

ORA-01591:	lock held by in-doubt distributed transaction string
Cause:	Trying to access resource that is locked by a dead two-phase commit transaction that is in prepared state.
Action:	DBA should query the pending_trans$ and related tables, and attempt to repair network connection(s) to coordinator and commit point. If timely repair is not possible, DBA should contact DBA at commit point if known or end user for correct outcome, or use heuristic default if given to issue a heuristic commit or abort command to finalize the local portion of the distributed transaction.

What I end up did is pretty easy, rollback force didn't do the trick. The DBMS_TRANSACTION helped.

SQL> select local_tran_id from dba_2pc_pending;

LOCAL_TRAN_ID

----------------------

4.7.533420

SQL> rollback force '4.7.533420';

Rollback complete.

SQL> select local_tran_id from dba_2pc_pending;

LOCAL_TRAN_ID

----------------------

4.7.533420

SQL> exec dbms_transaction.purge_lost_db_entry('4.7.533420');

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select local_tran_id from dba_2pc_pending;

no rows selected

ORA-12547 and procmap error while running sqlplus

2010-09-28T15:50:00.000-07:00

If you got following error message while trying to run sqlplus on IBM AIX 5L

This is an exact match of

ORA-12547 connecting to sqlplus / as sysdba on IBM AIX 5L [ID 372143.1]

Basically because the /proc is not mounted on your server.

/DB/../10204-64/network/admin PROD 341 >sqlplus / as sysdba
/usr/bin/procmap : no such process : 373080
/usr/bin/procmap : no such process : 373080
/usr/bin/procmap : no such process : 373080

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Sep 28 15:39:02 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

ERROR:
ORA-12547: TNS:lost contact

Hot Backup datafile copy problem with CIO mount option on AIX

2010-08-18T17:47:00.000-07:00

We encountered some Hot Backup error after we followed IBM's suggestion to remove CIO mount option from our JFS2 data volumes.

The error is like follows:

scp /DB/VPROD/data01/sysaux01.dbf .

> cp: /DB/VPROD/data01/sysaux01.dbf: A system call received a parameter that is not valid.

This happens after we put database in hot backup mode and trying to copy datafiles.

So this approves what IBM told us, Oracle will use CIO no matter if the file system is mounted using CIO option. But this created another problem that AIX will not allow non-CIO system operation to access file opened with CIO.

There you go we back to square one, mounted the data volume back with CIO option in order to facilitate our Hot Backup. Another reason to use RMAN backup I guess.

ORA-00064: object is too large to allocate on this O/S (1,16777216)

2010-04-09T15:57:00.000-07:00

Not sure how many of you run into this problem. It happens to one of our production database after we trying to increase the SGA size to 320G.

Oracle version is 10.2.0.4, AIX 5.3 L6 32CPUs 750G RAM

The error is

  ORA-00064: object is too large to allocate on this O/S (1,16777216)

It seems pretty conclusive OS limit problem. However our OS ulimit for Oracle user is set to unlimited.
Actually the problem is _ksmg_granule_size SGA units of granules

Granule size is determined by total SGA size. On most platforms, the size of a granule is 4 MB if the total SGA size is less than 1 GB, and granule size is 16MB for larger SGAs.

In our case since we increased our SGA so big, even 16MB is not big enough to fix our needs.
After increased _ksmg_granule_size to 32MB, we are able to start the instance with 350MB SGA.

alter system set "_ksmg_granule_size"=33554432 scope=spfile;

Oracle instance slow startup

2010-03-18T11:46:00.000-07:00

Recently we noticed one of our production database take longer than usual to startup.
In some cases, it took 3 to 4 hours for alter database open to complete.
The case is particularly bad for our TEST and DEV database after they got refreshed with production. Our production is very powerful 32 CPUs box, when production took like 20 to 30 minutes to open. TEST and DEV will take hours.

Sat Feb 27 06:48:55 2010
alter database open
-snip-
Sat Feb 27 09:54:22 2010
Completed: alter database open

We engaged Oracle support and they suggested to do a trace.

SQL> conn / as sysdba
SQL> startup mount
SQL> alter session set events '10046 trace name context forever, level 12';
SQL> alter database open;
Once the instance is opened, immediately turn off the 10046 tracing through that session.
SQL> alter session set events '10046 trace name context off';

The trace revealed that, database is querying two advanced queue tables used by STREAMS. The two tables are highly fragmented, for example table aq$_qt_cap_st_D had just 150 records and had 8000 + extents.

strmadmin.aq$_qt_cap_st_p
strmadmin.aq$_qt_cap_st_d

For TEST and DEV we can easily go around the issue by truncating the two tables because we are not using STREAMS on them. For production, table re-org are in order, for that we choose to use Online Redefinition.

tnsnames.ora is picky

2010-01-27T17:42:00.000-08:00

Recently we added some TNS entries into our Remote Desktop servers that shared by all our users. The user report newly added entries are not working.
I did a tnsping on them and got these errors.

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION =
TNS-12533: TNS:illegal ADDRESS parameters

I then check the entry in tnsnames.ora file, it reads

VTEST.NET.COM =
(DESCRIPTION =
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = vqadb01.netxx.com)(PORT = 1521))
)
(CONNECT_DATA =
(SID = VTEST)
)
)

Do you find anything wrong with them?

Not sure about you, but it took me a while to figure out that tnsnames.ora is pretty sensitive to the format. The problem is the new entry description all flatten out to the left instead of having indentation like this one just below.

FPROD.NET.COM=
--(DESCRIPTION =
----(ADDRESS_LIST =
-----(ADDRESS = (PROTOCOL = TCP)(HOST = FPROD)(PORT = 1528))
----)
----(CONNECT_DATA =
-----(SID = FPROD)
----)
--)

-- Oh well, I think we had same problem here on blogspot, it left align all my postings. Let me use dash instead.

So Oracle think "(DESCRIPTION" is a new entry in the tnsnames.ora instead of the description of previous entry. Because we DBA don't have control over the remote desktop server, I need to create tickets to add new entry. When I pasted the text into ticket, the ticket system left aligned them hence lost indentation.

Well, it's a small trivial problem but sometime it's hard to figure if you don't already know. Because the actual configure text are correct and worked on other hosts.

Stop Datapump Job

2009-09-02T10:31:00.000-07:00

If you have a data pump job running and you want to stop it, there's a couple of ways to stop them.

First run a statement to find out the running jobs,

SELECT JOB_NAME, OWNER_NAME , STATE FROM DBA_DATAPUMP_JOBS

JOB_NAME OWNER_NAME STATE
------------------------------ ------------------------------ ------------------------------
SYS_IMPORT_FULL_02 SYSTEM EXECUTING
SYS_IMPORT_FULL_03 SYSTEM STOP PENDING
SYS_EXPORT_SCHEMA_01 SYSTEM NOT RUNNING
SYS_IMPORT_FULL_01 SYSTEM NOT RUNNING

then you can either use DBMS_DATAPUMP.ATTACH and DBMS_DATAPUMP.STOP_JOB to stop them from SQL

or

impdp userid=system/password attach=SYS_IMPORT_FULL_03
Import> STOP_JOB=IMMEDIATE
Are you sure you wish to stop this job ([yes]/no): yes

Hint, Check patch history

2009-08-31T17:09:00.000-07:00

One of the method to check DB patch history is query registry$history

SYS>select ACTION_TIME,ACTION,NAMESPACE,VERSION,BUNDLE_SERIES from registry$history

ACTION_TIME ACTION NAMESPACE VERSION BUNDLE_SERIES
------------------------------ ---------- ---------- ------------------------------ --------------------
12-SEP-08 10.28.24.895804 PM CPU SERVER 10.2.0.3.0
12-SEP-08 10.30.59.512031 PM CPU
30-MAY-09 11.34.14.809290 AM UPGRADE SERVER 10.2.0.4.0
30-MAY-09 01.13.24.879514 PM APPLY SERVER 10.2.0.4 CPU
30-MAY-09 01.21.04.024997 PM CPU

OEM Grid Control Agent issue

2009-08-26T09:48:00.000-07:00

This morning we keep getting OEM agent message from one of our production DB server. 10.2.0.4 HP-UX

It sent this Agent unreachable alert and clear alert repeatedly.

Severity=Unreachable StartMessage=Agent is Unreachable (REASON = javax.net.ssl.SSLException: SSL handshake failed: SSLSessionNotFoundErr) but the host is UP.

Severity=Unreachable ClearMessage=Agent Unreachability is cleared. The current status of the target is UP.

When check on the hosts, we observed a number of emdprocstats.pl processes taking high CPU usage and memory and running for a couple of hours.

11466 /oracle/xxx/agent10g/perl/bin/perl /oracle/xxx/agent10g/sysman/admin/scripts/emdprocstats.pl 29011 14180 /oracle/xxx/agent10g/perl/bin/perl /oracle/xxx/agent10g/sysman/admin/scripts/emdprocstats.pl 32100

It's Symptoms of BUG 5908032 described in metalink doc,
Doc ID:
437305.1

The immediate solution is to stop/start agent, or kill these processes if agent can't be stop gracefully.
The long term Solution is to apply Patch 5908032

an update on this, the other DBA told me the agent on this server was not patched after DB was upgraded from 10.2.0.3 to 10.2.0.4, the agent is still 10.2.0.3 in this case.
Also, stop agent will not remove the hung process. manually killed them.

A gotcha of Data Pump export

2009-02-03T15:34:00.000-08:00

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

2009-01-22T16:34:00.000-08:00

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

create stats table to hold exported stats data.
EXEC DBMS_STATS.create_stat_table('SCHEMA1','ST_TABLE');
export stats to stats table
EXEC DBMS_STATS.export_schema_stats('SCHEMA1','ST_TABLE',NULL,'SCHEMA1');
transfer the stats table ST_TABLE to destinate location using method of choice, like exp/imp, create tabel as select ..., sqlplus copy etc.
import the stats into schema
EXEC DBMS_STATS.import_schema_stats('SCHEMA1','ST_TABLE',NULL,'SCHEMA1');
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!

2009-01-06T20:18:00.001-08:00

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

2008-10-22T11:43:00.000-07:00

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

2008-10-06T12:27:00.000-07:00

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

2008-10-03T13:46:00.000-07:00

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

2008-07-02T11:51:00.000-07:00

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.

Oracle RMAN bug

2008-06-30T18:10:00.000-07:00

I just hit an Oracle RMAN Bug while revising one of my RMAN backup scripts. I had a typo in my ORACLE_SID setting. So RMAN started without a target database connection, the script subsequently issued,

sql "alter system switch logfile";

Which crashed RMAN with ORA-600 numbers

corpdb 15 oracle %setenv ORACLE_SID ctest
corpdb 16 oracle %rman catalog rmancat/rman@rman target /
Recovery Manager: Release 10.2.0.3.0 - Production on Mon Jun 30 18:10:10 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)connected to recovery catalog database
RMAN> sql "alter system switch logfile";
sql statement: alter system switch logfile
DBGANY: CMD type=sql id=1 status=NOT STARTED
DBGANY: 1 STEP id=1 status=NOT STARTED chid=default
DBGANY: 1 TEXTNOD = -- sql
DBGANY: 2 TEXTNOD = begin
DBGANY: 3 TEXTNOD = krmicd.execSql(
DBGANY: 4 PRMVAL = stmt=>'alter system switch logfile'
DBGANY: 5 TEXTNOD = );
DBGANY: 6 TEXTNOD = end;
RMAN-00571:===========================================================
RMAN-00569:=============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571:===========================================================
RMAN-00601: fatal error in recovery manager
RMAN-03004: fatal error during execution of command
RMAN-00600: internal error, arguments [6000] [] [] [] []
corpdb 17 oracle %

While runing other RMAN Command should result following errors,

RMAN>
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of delete command at 06/30/2008 18:04:48
RMAN-06403: could not obtain a fully authorized session
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
HPUX-ia64 Error: 2: No such file or directory

ORA-01882: timezone region %s not found

2008-06-12T14:25:00.000-07:00

ORA-01882: timezone region %s not found

I got this error while running

select * from dba_scheduler_jobs;

The error message itself turns out not very informative.

01882, 00000, "timezone region %s not found"
// *Cause: The specified region name was not found.
// *Action: Please contact Oracle Customer Support.

A little research on metalink help solved the problem. Metalink has a Doc specifically explain how to fix this error. In short, the error is because there are 7 timezone region IDs changed from version 3 and above. If you have old Timezone data from Version 2 that using one of these IDs the error raises.
The Doc provided a convenience script to fix the problem. After running the script problem gone. For more information check,

Time Zone IDs for 7 Time Zones Changed in Time Zone Files Version 3 and Higher, Possible ORA-1882 After Upgrade
Doc ID: Note:414590.1

Oracle licensing on multicore processor

2008-05-21T15:55:00.000-07:00

With the increasing popularity of multi-core processor nowadays, it's unclear for a lot of people including DBAs how Oracle calculate the required license for certain server configuration.
A quick guideline is, for one server one core has processor licensing factor of .50, a quad core CPU require 2 processor licenses.
However there's a catch here, this equation is not apply to more than one multicore servers, for example RAC setup. Two multicore servers installed and/or running the program on 8 cores will need 8 multiplied by a core processor licensing factor of .75 equals 6. Therefore instead of 4 processor licenses this require 6 processor licenses.
Also note, When licensing Oracle programs with Standard Edition One or Standard Edition in the product name, a processor is counted equivalent to an occupied socket; however, in the case of multi-chip modules, each chip in the multi-chip module is counted as one occupied socket.

Please refer to following licensing document from Oracle for more detail.

Oracle Licensing

ORA-01466 and flashback query

2008-05-06T16:58:00.000-07:00

Someone brought this up in one OTN forum post.

I did a quick search on google and found this,
http://www.adp-gmbh.ch/ora/err/ora_01466.html

It seems a viable explanation. However I really need some sort reference to verify the 5 minutes time frame. Why 5 minutes? Not 1 minutes for example.

Then I found metalink note,

Error ORA-01466 while executing a flashback query.
Doc ID:
Note:281510.1

It explains everything.
The reason is because smon_scn_time is updated every 5 minutes in 9i, please note, in 10g smon_scn_time is updated every 6 seconds which is much more reasonable and making it very unlikely to hit the error.

ORA-03115 unsupported network datatype

2008-05-02T15:36:00.000-07:00

ORA-03115: unsupported network datatype or representation

This is an annoying error that comes and goes during one of your implementation project. The application is using an utility that connecting to database from Excel. The client is version 9.2.0.1 and database is 10.2.0.3

After a quick search on internet,
Metalink note
OCI Application Errors with ORA-3115
Doc ID: Note:460498.1
seems a hit. After I applied the workaround, the problem seems go away for now. The thing I don't like about this is

It applies not only between 9i and 10g, according to the notes, the problem exists between 10.2.0.3 and 11g as well.
The workaround is set CURSOR_SHARING to EXACT, which is not preferred setting if you having a lot of customized SQL running on your database.

Looks like Oracle didn't have a patch specifically for this bug yet. If anyone knows otherwise please drop a line.

ORA-39082 error while import using data pump

2008-03-31T17:30:00.000-07:00

When you use data pump import (impdp) doing database migration, sometime you will get ORA-39082 error, for example,

ORA-39082: Object type ALTER_PROCEDURE:"DX"."UPDATE_MKT"
created with compilation warnings
ORA-39082: Object type ALTER_PROCEDURE:"DX"."UPDATE_SALES"
created with compilation warnings
ORA-39082: Object type ALTER_PROCEDURE:"DX"."CHECK_CURRENT"
created with compilation warnings
ORA-39082: Object type ALTER_PROCEDURE:"DX"."DELETE_PAST"
created with compilation warnings

After import, when issue

alter procedure UPDATE_MKT compile;
alter procedure DELETE_PAST compile;
etc.

They compiled successfully without any errors or warning.

The reason behind this is because data pump import create procedures before views, if your procedure have dependency on views then you will have the ORA-39082 compilation errors at import.

Even the problem can be easily fixed by recompile all procedures after import. This is a little inconvenience for DBA that need to do DB refresh regularly.

Or perhaps this is the way Oracle suggest us don't reference views in procedures :)

Also there are Bugs return similar error

Impdp Returns ORA-39082 When Importing Wrapped Procedures

Doc ID:
Note:460267.1

update gone wild

2008-03-04T15:29:00.000-08:00

Currently our company is implementing a Supply Chain Application to replace our out-dated I2. Needless to say, I am the person who's going to setup and maintain the Oracle database for the project. Oracle version is 10.2.0.3. The consultants keep having some performance problem with one of the procedures they build. I spent time to look into it, and found out how easy it is to screw up Oracle database.

The first problem was runaway session, the procedure had some sort of dead loop and doing massive updates without commit, at one time the undo tablespace grew all the way up to 11G. After the session was killed, Oracle need to recover all these 1.4 million undo blocks. As you have expected, it's not going to be a fast process and you literally can't skip it in anyway. (unless drop and recreate database of course) My instance is recovering at ~150 blocks/sec rate. You can check the recovering progress by query this view v$fast_start_transactions. I felt it's kinda of slow, not sure if other people had similar experience can share.

The interesting thing is while SMON doing the recovery, the consultant is trying to access the database from the application, whenever the application touch the subject tables, SMON fire up whole bunch of parallel processes in the effort to speed up the rollback processes. It's called Parallel Rollback. However in this case, it's not helping the situation at all, on the contrary, with so many processes trying to recover the same transaction, they created quite some contention on undo blocks. Each of these processes plus SMON spend a lot of time waiting for each other, buffer busy waits events on Undo blocks sky rocketing. Parallel rollback mostly improve performance on parallel transaction recovery. In this case, better disable the parallel rollback by

alter system set fast_start_parallel_rollback = false;

The second problem is one of the update statement keep hung the session and do all sort of crazy things in database. The simple update query generated millions of buffer gets and huge number of db file scattered read and latch:cache buffers chains events. With help of some query,

select CHILD# "cCHILD",
ADDR "sADDR",
GETS "sGETS",
MISSES "sMISSES",
SLEEPS "sSLEEPS"
from v$latch_children where name = 'cache buffers chains'
order by 5, 1, 2, 3 desc

select /*+ RULE */ e.owner '.' e.segment_name
segment_name, e.extent_id extent#,
x.dbablk - e.block_id + 1 block#,
x.tch, l.child#from sys.v$latch_children l,
sys.x$bh x, sys.dba_extents e
where x.hladdr = 'ADDR' and
e.file_id = x.file# and
x.hladdr = l.addr and
x.dbablk between e.block_id and
e.block_id + e.blocks -1
order by x.tch desc ;

The culprit object quickly discovered, but why?
After a quick analyze on the table the problem went away.
I suspect this is some sort of Oracle bug. The table causing the problem only has 20k rows. It was truncated and inserted some data earlier in the procedure, however after truncate Oracle reset the statistics of this table and didn't update it after insert. When the update kick in, optimizer was using the wrong information and went into some dead loop situation. Currently I suggested the consultant do an analyze after each insert as work around, however I would like to find if more organic and permanent solution for this from Oracle.

Reference,

How To Identify a Hot Block Within The Database Buffer Cache.
Doc ID:
Note:163424.1

LATCH: CACHE BUFFERS CHAINS
Doc ID:
Note:42152.1

Parallel Rollback may hang database, Parallel query servers get 100% cpu
Doc ID:
Note:144332.1

Bypass buffer cache for Full Table Scans

2008-02-26T22:54:00.000-08:00

Paypal DBA Saibabu mentioned an interesting undocumented parameter to bypass buffer cache for full table scans in his blog http://sai-oracle.blogspot.com/

alter session set "_serial_direct_read" = true;

I found it's particular useful in OLTP environment where you need to occasionally run a FTS query against a large table.

Install Oracle 10gR2 on RHEL5 or OEL5

2008-02-21T11:11:00.000-08:00

It comes to my attention that a lot of people still having problem installing Oracle 10gR2 on RedHat Enterprise Linux/Oracle Enterprise Linux 5.

Actually Oracle already posted a series of metalink notes covering all aspect of such issue. By following the procedures listed in these notes you should have a success installation.

These notes contains links to each other, you shouldn't have problem to find all of them once you got one.

Requirements For Installing Oracle10gR2 On RHEL/OEL 5 (x86_64)
Doc ID: Note:421308.1

Note 376183.1 - Defining a "default RPMs" installation of the RHEL OS
Note 419646.1 - Requirements For Installing Oracle 10gR2 On RHEL5 (x86)
Note 456634.1 - Installer Is Failing on Prereqs for Redhat-5 - RHEL5

11g surprises

2007-12-17T16:01:00.000-08:00

The first two surprises after my first 11g installation was the new location of alert.log file and AMM changed yet again. I didn't run 11g beta program, so the two surprises could be old story for many others.

Just in case you wonder where's my alert.log files and what is memory_target parameter. Check following two metalink docs for full explanation.

Automatic Memory Management(AMM) on 11g
Doc ID:
Note:443746.1

Finding alert.log file in 11g
Doc ID:
Note:438148.1

explain plan - a note for myself

2007-11-04T15:34:00.000-08:00

Just a quick note for myself. I used to generate explain plan from sqlplus using autotrace, it's time to switch to use dbms_xplan package instead. Well it's never too late to do the right thing anyway.

SQL> explain plan for select empno,sal from emp;
Explained.
SQL> select * from table(dbms_xplan.display);

LOG Miner by Example

2007-11-01T10:31:00.000-07:00

Come across a post in OTN forum today where DBMS Direct has a pretty good demonstration of how to use Logminer. Thought it's might be a good idea to post it here as future quick reference for myself or anyone need it.

First check if you have SUPPLEMENTAL Logging enabled,

SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

If not,

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Choose the Dictionary Option, there're three options you can choose.

Tell Logminer to use current online catalog as dictionary,

EXECUTE DBMS_LOGMNR.START_LOGMNR(-
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

Or extract Dictionary to the Redo Log Files,
you set this option if you can't access sourcce database while you do log mining.

EXECUTE DBMS_LOGMNR_D.BUILD( -
OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);

Or extract Dictionary to a Flat File,
this option is for backward compatibility and not recommended if you can use other two.

EXECUTE DBMS_LOGMNR_D.BUILD('dictionary.ora', -
'/oracle/database/', -
DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);

Now find a list of recent redo logfiles you want to mine,
you could choose to let Logminer use control file automatically build the list of redo logfiles needed.

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

EXECUTE DBMS_LOGMNR.START_LOGMNR( -
STARTTIME => '01-Jan-2007 08:30:00', -
ENDTIME => '01-Jan-2007 08:45:00', -
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
DBMS_LOGMNR.CONTINUOUS_MINE);

Or manually pick which one you want to analysis

SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE COMPLETION_TIME > TRUNC(SYSDATE);

/oracle/edb/oraarch/edb/1_3703_608486264.dbf
/oracle/edb/oraarch/edb/1_3704_608486264.dbf
/oracle/edb/oraarch/edb/1_3705_608486264.dbf
/oracle/edb/oraarch/edb/1_3706_608486264.dbf
/oracle/edb/oraarch/edb/1_3707_608486264.dbf
/oracle/edb/oraarch/edb/1_3708_608486264.dbf
/oracle/edb/oraarch/edb/1_3710_608486264.dbf
/oracle/edb/oraarch/edb/1_3709_608486264.dbf
/oracle/edb/oraarch/edb/1_3711_608486264.dbf
/oracle/edb/oraarch/edb/1_3712_608486264.dbf

10 rows selected.

Add the logfiles for analysis.

EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oracle/edb/oraarch/edb/1_3712_608486264.dbf',OPTIONS => DBMS_LOGMNR.NEW);

EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oracle/edb/oraarch/edb/1_3711_608486264.dbf');

Start LogMiner to populate the V$LOGMNR_CONTENTS view

EXECUTE DBMS_LOGMNR.START_LOGMNR();

Format the SQLplus output for better viewing,

SQL> set lines 200
SQL> set pages 0
SQL> col USERNAME format a10
SQL> col SQL_REDO format a30
SQL> col SQL_UNDO format a30

And there we go,

1 SELECT username,
2 SQL_REDO, SQL_UNDO ,to_char(timestamp,'DD/MM/YYYY HH24:MI:SS') timestamp
3* FROM V$LOGMNR_CONTENTS where rownum< username="'TEST'">

SQL> /

USERNAME SQL_REDO SQL_UNDO
---------- ------------------------------ ------------------------------
TIMESTAMP
-------------------
TEST insert into "TEST"."KI_TEST delete from "TEST"."KI_TEST
_RES7"("MASTER_ID","WAFER_ID", _RES7" where "MASTER_ID" = '18
"DIE_NUM","TEST_NUM","RESULT") 8199' and "WAFER_ID" = '15' an
values ('188199','15','0,144' d "DIE_NUM" = '0,144' and "TES
,'4','.83555'); T_NUM" = '4' and "RESULT" = '.
83555' and ROWID = 'AAAOnaAAZA
AAECZAEV';
01/11/2007 09:23:59

TEST insert into "TEST"."KI_TEST delete from "TEST"."KI_TEST
_RES7"("MASTER_ID","WAFER_ID", _RES7" where "MASTER_ID" = '18
"DIE_NUM","TEST_NUM","RESULT") 8199' and "WAFER_ID" = '15' an
values ('188199','15','0,144' d "DIE_NUM" = '0,144' and "TES
,'5','.70222'); T_NUM" = '5' and "RESULT" = '.
70222' and ROWID = 'AAAOnaAAZA
AAECZAEW';
01/11/2007 09:23:59

TEST insert into "TEST"."KI_TEST delete from "TEST"."KI_TEST
_RES7"("MASTER_ID","WAFER_ID", _RES7" where "MASTER_ID" = '18
"DIE_NUM","TEST_NUM","RESULT") 8199' and "WAFER_ID" = '15' an
values ('188199','15','0,144' d "DIE_NUM" = '0,144' and "TES
,'6','.82502'); T_NUM" = '6' and "RESULT" = '.
82502' and ROWID = 'AAAOnaAAZA
AAECZAEX';
01/11/2007 09:23:59

TEST insert into "TEST"."KI_TEST delete from "TEST"."KI_TEST
_RES7"("MASTER_ID","WAFER_ID", _RES7" where "MASTER_ID" = '18
"DIE_NUM","TEST_NUM","RESULT") 8199' and "WAFER_ID" = '15' an
values ('188199','15','0,144' d "DIE_NUM" = '0,144' and "TES
,'7','1.4818'); T_NUM" = '7' and "RESULT" = '1
.4818' and ROWID = 'AAAOnaAAZA
AAECZAEY';
01/11/2007 09:23:59

Reference:
Oracle Document
Using LogMiner to Analyze Redo Log Files

Orginal Post in OTN

LOGGING/NOLOGGING

2007-10-09T15:09:00.000-07:00

One of the common misconception is if you set NOLOGGING on a table or index, then no future DML operations (insert, update, delete etc) on this object will be recorded in logfiles.

Actually the real meaning of NOLOGGING is whatever operations are performed on the object with the NOLOGGING option, will NOT be recorded in logfiles.

However, not all operations support NOLOGGING mode, the following is a list of operations that support NOLOGGING:

direct load (SQL*Loader)
direct-load
INSERT CREATE TABLE ... AS SELECT
CREATE INDEX
ALTER TABLE ... MOVE PARTITION
ALTER TABLE ... SPLIT PARTITION
ALTER INDEX ... SPLIT PARTITION
ALTER INDEX ... REBUILD
ALTER INDEX ... REBUILD PARTITION
INSERT, UPDATE, and DELETE on LOBs in NOCACHE NOLOGGING mode stored out of line

It also recommended to do a backup of subject object after NOLOGGING operation. When you do media recovery of the object using backup copy before NOLOGGING operation, the extent invalidation records mark a range of blocks as logically corrupt, because the redo data is not fully logged. The similar situation apply to Data Guard setup. That's also one of reason why Data Guard setup require you to set force logging.

Howard has been talking about the use of _disable_logging parameter to temporary disable redo logging while bulking loading. However this parameter should be used with extreme caution.

Howard's post about _disable_logging

Some thing about Shared Pool

2007-09-21T16:30:00.000-07:00

Here's a good presentation, explaining the internal structure of shared pool and how lock, pin and latch are handled.

http://www.perfvision.com/papers/unit6_shared_pool.ppt

Set SQL*Plus Prompt

2007-07-06T16:03:00.000-07:00

Since most DBAs will use SQL*Plus more than any tools else, it will be convenience if you can set your SQL*Plus prompt to show your current login user and SID instead of this "SQL>". Especially if you have mulitple instances running under same Oracle installation.

To do this, you need to include following in your glogin.sql

set term off
define sql_prompt=idle
column user_sid new_value sql_prompt
select lower(user) '@' '&_CONNECT_IDENTIFIER' user_sid from dual;
set sqlprompt '&sql_prompt> '
set term on

When you login you will see this,

sqlplus scott/tiger
scott@dev

If you are on 10g, it becomes much easier by only this,

set sqlprompt '_user@&_connect_identifier>'

Getting a job as a Junior DBA - series

2007-06-25T13:57:00.000-07:00

Unfortunately, due to some unspecified events happened early 2008, Howard decide to withdraw his contribution to Oracle community. More info here
The interview series link is no longer valid. I wish I had cut/pasted it :(

One of the most common Q I saw new DBA ask in Oracle forum is what kind of questions employer will ask during a job interview? I tried to sum a few questions I have been asked during job interviews as a job candidate.

Howard Rogers has nice serie in his blog from interviewer's prospective.
Getting a job as a Junior DBA

Frankly if you ask me, I don't want to work for him :) However that doesn't change the fact it's a nice reading.

Heterogeneous Distributed Database Systems

2007-06-25T11:32:00.000-07:00

How to maintain a regular data replication between other type of databases with Oracle is a pretty frequently asked question in DBA world.

Consider setup Heterogeneous Distributed Database Systems to solve the problem.

John Palinski has a good post about it here

Shared Pool Structures

2007-06-23T00:03:00.000-07:00

A useful link to a white page about shared pool

Understanding Shared Pool Memory Structures

Also to toubleshoot any ORA-4031 error, metalink has a good note,

Troubleshooting and Diagnosing ORA-4031 Error (previously FAQ: ORA-4031):
Note:396940.1

Find out Rollback Segment with active transactions

2007-06-21T15:12:00.000-07:00

Use this script to find out how many active transactions link with a rollback segment.

SELECT NAME, XACTS "ACTIVE TRANSACTIONS"
FROM V$ROLLNAME, V$ROLLSTAT
WHERE V$ROLLNAME.USN = V$ROLLSTAT.USN

Secure your Password

2007-06-21T13:35:00.000-07:00

You should run following script $ORACLE_HOME/rdbms/admin/utlpwdmg.sql
to secure your Password via Default Profile after DB creation. Or expect to see a lot of Policy Violations in your EM dbconsole for the instance.
I don't know why Oracle didn't make these setting 'Default' in the first place.
You could modify the CREATE FUNCTION verify_function part to fit your need.

alter profile default
limit failed_login_attempts 3
password_grace_time 10
password_life_time 45
password_lock_time unlimited
password_reuse_max 180
password_reuse_time unlimited password_verify_function VERIFY_FUNCTION;

Quick steps to fix OMS agent problem

2007-06-13T16:14:00.000-07:00

Some time when communication between OMS agent and OMS server is interrupted. You have problem to sync up the connection. Common problem/error include,

getting
EMD upload error: uploadXMLFiles skipped :: OMS version not checked yet..

when run
emctl upload

run
emctl status agent

you will get following lines in result,

Last attempted heartbeat to OMS : 2007-06-07 17:12:30
Last successful heartbeat to OMS : unknown

The quick way to clear the problem is going through following steps:

emctl stop agent
delete all files in $AGENT_HOME/sysman/emd/upload and AGENT_HOME/sysman/emd/state
emctl clearstate agent
emctl secure agent
emctl start agent

2006-06-22T15:51:00.000-07:00

Another recent interview question batch, I will provide the answers to these questions whenever I have time. In most cases, you can find the answers in Oracle Performance Tunning Guide

You have an Oracle database that hung and you can't login. What will you do to troubleshoot the problem?
Which Oracle process will collect the unused temporary tablespace extents?
What're the common wait events you see in statspack? And how to deal with them? (Again)

to be continued

2006-06-09T12:00:00.000-07:00

First batch of questions from my lastest interview. I will provide my suggested answers later when I edit this post. Pretty swamped with projects at work lately.

How LOB is handled/saved in Oracle?
What is DBWR? What it does in Oracle?
When you set Oracle parameter global_name = true, what it means?
How many types of partition in oracle?
What is a latch? What’s difference between latch and lock?
Oracle Metalink Note:22908.1 is a good source for this question.
What is a latch?
Latches are low level serialization mechanisms used to protect shared data structures in the SGA. The implementation of latches is operating system dependent, particularly in regard to whether a process will wait for a latch and for how long.
A latch is a type of a lock that can be very quickly acquired and freed. Latches are typically used to prevent more than one process from executing the same piece of code at a given time. Associated with each latch is a cleanup procedure that will be called if a process dies while holding the latch. Latches have an associated level that is used to prevent deadlocks. Once a process acquires a latch at a certain level it cannot subsequently acquire a latch at a level that is equal to or less than that level (unless it acquires it nowait).
Difference between shared pool latch and library cache latch?
What’s the difference between Library cache bin and library cache lock? Which one is worse to have?
Will a un-commit insert block other insert when no index. What if there’s bitmap index in place?
Usually an insert should never block other insert or update in the table. It's a brand new row in the table there shouldn't be any contention.
But there are 2 senarios, insert could block others,
1. Waits for TX in mode 4 can also occur if a session is waiting due to potential duplicates in UNIQUE index. If two sessions try to insert the same key value thesecond session has to wait to see if an ORA-0001 should be raised or not.
2. Waits for TX in mode 4 is also possible if the session is waiting due to shared bitmap index fragment. Bitmap indexes index key values and a range of ROWIDs. Each ’entry’ in bitmap index can cover many rows in the actual table. If two sessions want to update rows covered by the same bitmap index fragment, then the second session waits for the first transaction to either COMMIT or ROLLBACK by waiting for the TX lock in mode 4
What’s the difference between soft parse and hard parse? Is there any way to get rid of parse all along?
How do you find out the information about blocking locks?
Check the v$lock table. Where BLOCK <> 0 is blocker and REQUEST <> 0 is waiter
List a few common wait events you see in statspack.
When you check v$lock, there’s a lock type is ‘NQ’. What’s lock is it?
This is an interesting one. I think many DBAs probably don't know this. The lock type in DBA_LOCK and v$lock is actually Enqueue name. The complete list of enqueue name can be found here . NQ is one of the library cache pin locks.
How do you recover the database if you lost the control files.
If you know the block_id and file_id of an object which table will be used to find out the name of the object?
DBA_EXTENTS

2006-06-02T19:07:00.000-07:00

I gathered some of the questions while I was having job interviews during the last few years. These questions are for Senior Oracle DBA position and they are from employer of top tier high tech companies. Company like google, yahoo, ebay, Johnson and Johnson just to name a few.
Different companies have different job description of senior DBA, therefore, their questions are focused on quite different area. I will emphasis on technical questions first.

2004-05-03T11:40:00.000-07:00

Hello!

-- This message was posted first time after registered with Blogger. Not a big fun of blogging at the time, my sole purpose of creating an account is to get a chance to try out Gmail beta. Funny heh.