Dangerous autoextensive tempfile

Developer feedback running a sql in a recently cloned database 11.2.0.2 ADMB2 and hit funny error ora-01114.

Error as follows.

ORA-01114: IO error writing block to file (block # )

ORA-01114: IO error writing block to file 201 (block # 2321408)

ORA-27072: File I/O error

Additional information: 4

Additional information: 2321408

Additional information: -1

Tested it in source database ,there is no such error.

Invoked oerr to lookup the error message, the file id complained is 201,  which is not found in dba_data_files.


dbsvr9:ADMB2:/software/oracle1/admin/scripts/performance> oerr ora 1114
01114, 00000, "IO error writing block to file %s (block # %s)"
// *Cause:  The device on which the file resides is probably offline. If the
//          file is a temporary file, then it is also possible that the device
//          has run out of space. This could happen because disk space of
//          temporary files is not necessarily allocated at file creation time.
// *Action: Restore access to the device or remove unnecessary files to free
//          up space.

Then focuse on checking temporary files.

However, the tempfile is about 18GB already and almost 100% free as show below.
and, the mount pointing of tempfile is 100% used.


SYS@ADMB2> select * from dba_temp_files;

FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------
   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS  RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY
---------- ------------------------------ ---------- ---------- ------- ------------ --- ---------- ---------- ------------
USER_BYTES USER_BLOCKS
---------- -----------
/admadm/ADMB2/temp01.dbf
         1 TEMP                           1.9018E+10    2321536 ONLINE             1 YES 3.4360E+10    4194302           80
1.9017E+10     2321408


SYS@ADMB2> @chk_temp_by_user.sql
check tempoary space used by session

no rows selected


no rows selected

SYS@ADMB2> @chk_temp_usage.sql

percent used
------------
           0


TABLESPACE             MB_TOTAL    MB_USED    MB_FREE
-------------------- ---------- ---------- ----------
TEMP                      18137          0      18137



Funny.
What does file_id 201 stand for ?
No session is hogging the temp tablespace.


The next intuition  is why the tempfile is antoextensive.

Trying to turn id off, and shank it down.

SYS@ADMB2> alter database tempfile '/admadm/ADMB2/temp01.dbf' autoextend off;

Database altered.

SYS@ADMB2> alter database tempfile '/admadm/ADMB2/temp01.dbf' resize 2g;

Database altered.

At last, run the sql again. wow, 15429 rows selected, and right close 7pm, and luckily this is not a production database.



From this case, it seems to even when there is free space in temporary tablespace, Oracle database always acquires space from file system, although there is no more disk space in the file system, and can't look from existing tablespace.  Such behaviour is "silly" and dangerous.

Problematic erase key in Oracle datafile running on unix

In Unix like shell command line, to erase a letter before current cursor, ^H (CTRL+H) or ^? (BACKSPACE) you'd like to use?

1. Introduction

"stty -a" helps to see current keyboard mapping the BACKSPACE key.

dev07:B.11:TESTDB:/software/oracle> stty -a                            
speed 38400 baud; line = 0;
rows = 24; columns = 80
min = 4; time = 0;
intr = ^C; quit = ^\; erase = DEL; kill = ^U
eof = ^D; eol ; eol2 ; swtch
stop = ^S; start = ^Q; susp = ^Z; dsusp
werase = ^W; lnext = ^V
-parenb -parodd cs8 -cstopb hupcl cread -clocal -loblk -crts
-ignbrk brkint ignpar -parmrk -inpck -istrip -inlcr -igncr icrnl -iuclc
ixon -ixany ixoff imaxbel -rtsxoff -ctsxon -ienqak
isig icanon -iexten -xcase echo echoe echok -echonl -noflsh
echoctl -echoprt echoke -flusho -pendin
opost -olcuc onlcr -ocrnl -onocr -onlret -ofill -ofdel -tostop tab3
dev07:B.11:TESTDB:/software/oracle> stty erase (here pressed CTRL+h)


dev07:B.11:TESTDB:/software/oracle> stty -a
speed 38400 baud; line = 0;
rows = 24; columns = 80
min = 4; time = 0;
intr = ^C; quit = ^\; erase = ^H; kill = ^U
eof = ^D; eol ; eol2 ; swtch
stop = ^S; start = ^Q; susp = ^Z; dsusp
werase = ^W; lnext = ^V
-parenb -parodd cs8 -cstopb hupcl cread -clocal -loblk -crts
-ignbrk brkint ignpar -parmrk -inpck -istrip -inlcr -igncr icrnl -iuclc
ixon -ixany ixoff imaxbel -rtsxoff -ctsxon -ienqak
isig icanon -iexten -xcase echo echoe echok -echonl -noflsh
echoctl -echoprt echoke -flusho -pendin
opost -olcuc onlcr -ocrnl -onocr -onlret -ofill -ofdel -tostop tab3



dev07:B.11:TESTDB:/software/oracle> lss^?    #attemp to use Backspace to delete extra "s" , instead shows as ^?
ksh: lss^?:  not found
dev07:B.11:TESTDB:/software/oracle> lss (followd by pressed CTRL+h)
10g                adhoc              jre                oradata
References:

2. Create a problem file

dev07:B.11:TESTDB:/software/oracle> touch test1^?.txt   #pressed CTRL+h after "1"
dev07:B.11:TESTDB:/software/oracle> ls -l test*.txt
-rw-r--r--   1 oracle     dba              0 Jul 31 17:22 test1.txt  #^? is invisible to "ls -l" command

dev07:B.11:TESTDB:/software/oracle> ls -ll test1^?.txt    # press escape key after "1", can auto-complete the filename, but still invisible
-rw-r--r--   1 oracle     dba              0 Jul 31 17:22 test1.txt

dev07:B.11:TESTDB:/software/oracle> ls -lb test1^?.txt    #use "b" option to see its octal number is 177, which is DEL in ASCII code table
-rw-r--r--   1 oracle     dba              0 Jul 31 17:22 test1\177.txt

--now play with ^H to create another problematic file

dev07:B.11:TESTDB:/software/oracle> stty -a
speed 38400 baud; line = 0;
rows = 24; columns = 80
min = 4; time = 0;
intr = ^C; quit = ^\; erase = DEL; kill = ^U
eof = ^D; eol ; eol2 ; swtch
stop = ^S; start = ^Q; susp = ^Z; dsusp
werase = ^W; lnext = ^V
-parenb -parodd cs8 -cstopb hupcl cread -clocal -loblk -crts
-ignbrk brkint ignpar -parmrk -inpck -istrip -inlcr -igncr icrnl -iuclc
ixon -ixany ixoff imaxbel -rtsxoff -ctsxon -ienqak
isig icanon -iexten -xcase echo echoe echok -echonl -noflsh
echoctl -echoprt echoke -flusho -pendin
opost -olcuc onlcr -ocrnl -onocr -onlret -ofill -ofdel -tostop tab3

dev07:B.11:TESTDB:/software/oracle> touch test.txt   #type test2^H.txt
dev07:B.11:TESTDB:/software/oracle> ls -lb test*.txt
-rw-r--r--   1 oracle     dba              0 Jul 31 17:22 test1\177.txt  #177 in octal in ASCII is "DEL"
-rw-r--r--   1 oracle     dba              0 Jul 31 17:40 test2\010.txt   #010 in octal in ASCII is "backspace"
dev07:B.11:TESTDB:/software/oracle>
ksh: ^H^H^H^H^H^H^H^H:  not found
dev07:B.11:TESTDB:/software/oracle> ll test*.txt
-rw-r--r--   1 oracle     dba              0 Jul 31 17:22 test1.txt
-rw-r--r--   1 oracle     dba              0 Jul 31 17:40 test.txt




3. Make it more troublesome in oracle datafile now.

3.1 first set stty erase as ^H
SQL>  create tablespace testbs datafile '/oracle/UAT/sapdata1/system_1/test1.dbf' size 20m;

Tablespace created.


SQL> alter tablespace testbs  add datafile '/oracle/UAT/sapdata1/system_1/test1^?2.dbf' size 20m;

Tablespace altered.
--  type test1+(Backspace key)+2.dbf in order to create the problem.
SQL> select * from dba_data_files where tablespace_name='TESTBS';

FILE_NAME
--------------------------------------------------------------------------------
   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
ONLINE_
-------
/oracle/UAT/sapdata1/system_1/test1.dbf
        74 TESTBS                           20971520       2560 AVAILABLE
          74 NO           0          0            0   20905984        2552
ONLINE

/oracle/UAT/sapdata1/system_1/test1^?2.dbf
        75 TESTBS                           20971520       2560 AVAILABLE
          75 NO           0          0            0   20905984        2552
ONLINE
At OS, it shows :
m1sapuat:orauat 9> ls -lb
total 81952
-rw-r-----   1 orauat     dba        20979712 Jul 30 16:45 test1.dbf
-rw-r-----   1 orauat     dba        20979712 Jul 30 18:42 test1\1772.dbf

3.2 Next, we need to rename it correctly to avoid other impact may arise.

# for database running in ARCHIVELOG mode
alter database datafile 75 offline

 SQL> alter database datafile 75 offline;   
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled


  # this testing database runs in NOARCHIVELOG mode.

SQL> alter database datafile 75 offline drop;

Database altered.
-- type the exactly datafile name : test1+(Backspace key)+2.dbf in order
SQL>  alter database rename file  '/oracle/UAT/sapdata1/system_1/test1^?2.dbf' to '/oracle/UAT/sapdata1/system_1/test2.dbf';

Database altered.

-- at OS level, back up the datafile and do the same way to rename it to test2.dbf

-- now recover and online it back

SQL> recover datafile '/oracle/UAT/sapdata1/system_1/test2.dbf';
Media recovery complete.
SQL>  alter database datafile  '/oracle/UAT/sapdata1/system_1/test2.dbf' online;

Database altered.

SQL>  select * from dba_data_files where tablespace_name='TESTBS';

FILE_NAME
--------------------------------------------------------------------------------
   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
ONLINE_
-------
/oracle/UAT/sapdata1/system_1/test1.dbf
        74 TESTBS                           20971520       2560 AVAILABLE
          74 NO           0          0            0   20905984        2552
ONLINE

/oracle/UAT/sapdata1/system_1/test2.dbf
        75 TESTBS                           20971520       2560 AVAILABLE
          75 NO           0          0            0   20905984        2552
ONLINE


SQL> select * from v$recover_file;

no rows selected

-- Note that this RENAME approach is preferred than drop the datafile , as drop datafile requires it is a empty file.

References:

Setting terminal characteristics with stty

Unix Tip: Using stty to Your Advantage

ASCII Table and Description

How to Drop a Datafile from a Tablespace

 

 

Rename datafile on-the-flying

Imaging you accidentally append a space to the datafile...
Luckily, you have the database in archivelog mode.

Examples
1. Resize datafile if possible (for backup before rename: save space and faster copy)

SQL> ALTER DATABASE DATAFILE 'F:\oradata\live\Mydb02.ora ' RESIZE 50m; 

2. Take datafile offline:

SQL> ALTER DATABASE DATAFILE 'F:\oradata\live\Mydb02.ora ' OFFLINE;

Database altered.

3. at OS level, copy (preferable) or rename the file

cd  F:\oradata\live\

copy 'Mydb02.ora ' 'Mydb02.ora'
#cp -p 'Mydb02.ora ' 'Mydb02.ora'  (for unix like platform)

or move 'Mydb02.ora ' 'Mydb02.ora'
 

4. Rename the datafile in database to update control file 

ALTER DATABASE RENAME FILE 'F:\oradata\live\Mydb02.ora ' TO 'F:\oradata\live\Mydb02.ora
';

5. Take datafile online, when doing this, file recovery is needed to update the timestamp in the offline datafile header.:

SQL> ALTER DATABASE DATAFILE 'F:\oradata\live\Mydb02.ora' ONLINE;

ALTER DATABASE DATAFILE 'F:\oradata\live\Mydb02.ora' ONLINE
* ERROR at line 1: ORA-01113: file 16 needs media recovery
ORA-01110: data file 16: 'F:\ORADATA\LIVE\Mydb02.ORA'

SQL> RECOVER DATAFILE 'F:\oradata\live\Mydb02.ora';
 Media recovery complete.

SQL> ALTER DATABASE DATAFILE 'F:\oradata\live\Mydb02.ora' ONLINE;

Database altered

Ref:

1. ALTER DATABASE

2. Renaming or Moving Oracle Files

3. Move/rename datafiles in Oracle

Using advantage of partition elimination

[The problem]

    Application team asked for help:

• Daily financial report delayed near one month because job running very slow while DB to shutdown everyday for cold backup. 
• My estimation is about 30 hours for the job to completed. 
• Vendor not able to provide solution even tried changing code a few times.

[Diagnostic]
    This range partitioning table is about 140Gb big, with 500+ partitions, even partition stores 5 values of job_id.
    From execution plan, partition is performed but no parallelism regardless PARALLEL server enabled, and no performing full table scan, how ever generating lots of I/O , consist gets requires , physical reads etc.
    Total consist gets is about 5 times of partitions needed for scan.
    The statement is like this:
     select ... from p_table where part_key_col in (select distinct job_id from job_table...);

    The execution plan shows NEST LOOP for each value return from sub-query, caused redundantly access to partitions.
    ie.
     for each job_id returned from sub-query (110 distinct job_id)
       do
         full partition scan  ( one of total 22 partitions )
       done.
  Hence, each partition is scanned 5 times in worst situation. Total times: 110
  While parallelized scan can't happen in single partition, which only occurs for simultaneoustly access to multiple partitions.

   
[Solution]
    Rewrite the code , to make partition elimination happen.
    select  ... from p_table where part_key_col between (select min(distinct job_id) from job_table ...) and (select max(distinct job_id) from job_table ...);
    Not that the logic slightly changed, but applicable in this case.

     After make this change according to my suggestion, job finished within 30 minutes, while observing 12 parallel processes running happily to scan 22 partitions once only.
    Cheers!

[Update on 17-Nov]

One more think,  SQL logic should not be changed.  Studied more about partition pruning from data warehousing guide, found USE_HASH hint achieved same effects without rewrite SQL.

changes/bugs may fail your 11g upgrade

For OLAP type databases or running  complicated batch jobs.
1. here are two bugs 8477973 and 4926618

8477973 Multiple open DB links / ORA-2020 / distributed deadlock possible 

Impact: hang your sql session

4926618 Excessive CPU on HASH UNIQUE when repeated

Good news, both of them are fixed in 11.2.0.2

Impact: x times slower your UPDATE statement.  Especially updating more than 1 million rows. The more rows more greater times.  In my case, 21 minutes becomes  8 hours .



2. new parameter db_ultra_safe:  DATA_AND_INDEX comes with significant overhead. For my INSERT case, it is 7x slower.


Spent weeks of effort to come to above findings.  Hope it help preventing from happening to you . Cheers!

prerequsite of using sql loader direct path loading

Be careful with using sql*loader, if the target table :

is big and having indexes during data loading  with logging = YES.

Reason being during data loading, index becomes UNUSABLE in dba_indexes (remains VALID in dba_objects). After data loading, then start rebuild indexes. When logging=YES, image FULL TABLE SCAN on a huge table, how many archived log we shall we ?  multiply by number of indexes ...  It is a big shock.  and how much more time it will take , assuming the job loads multiple files , each invoke of sql*loader loads one file only .

The common understanding of sql*load 

Use Direct Path Loads - The conventional path loader essentially loads the data by using standard insert statements.  The direct path loader (direct=true) loads directly into the Oracle data files and creates blocks in Oracle database block format.  The fact that SQL is not being issued makes the entire process much less taxing on the database.  There are certain cases, however, in which direct path loads cannot be used (clustered tables).  To prepare the database for direct path loads, the script $ORACLE_HOME/rdbms/admin/catldr.sql.sql must be executed.



In my case, apps team is not happy with 5 minutes performance, hence added "direct=ture", in the end caused archive log disk space full, job can't finish after running for 3 hours, generated 40+Gb archived log until archiver log hang.

During indexes rebuild after "direct=ture", see tremendous I/O incurred.

Report of conventional loading
Top 5 Timed Events

Event    Waits    Time(s)    Avg Wait(ms)    % Total Call Time    Wait Class
db file parallel write    6,358    240    38    97.5    System I/O
db file sequential read    44,238    128    3    52.0    User I/O
CPU time         115         46.7   
log file parallel write    11,538    79    7    32.1    System I/O
log file sync    11,094    76    7    30.9    Commit

Tablespace    Reads    Av Reads/s    Av Rd(ms)    Av Blks/Rd    Writes    Av Writes/s    Buffer Waits    Av Buf Wt(ms)
IDX     40,216     11     2.84     1.00     99,853     28     0     0.00

report of direct path loading
Top 5 Timed Events

Event    Waits    Time(s)    Avg Wait(ms)    % Total Call Time    Wait Class
db file sequential read    2,901,803    1,610    1    42.4    User I/O
log file parallel write    22,845    1,410    62    37.1    System I/O
CPU time         729         19.2    
Log archive I/O    46,028    455    10    12.0    System I/O
log file sequential read    22,795    87    4    2.3    System I/O


Tablespace IO Stats

    * ordered by IOs (Reads + Writes) desc

Tablespace    Reads    Av Reads/s    Av Rd(ms)    Av Blks/Rd    Writes    Av Writes/s    Buffer Waits    Av Buf Wt(ms)
IDX     2,866,984     794     0.52     1.00     427,225     118     0     0.00  
  

rebuild indexes  takes more time than gained from data loading part. Below is the figure I just used to rebuild unusable indexes for surepay04 tables, took near 10 minutes for nonvoice04 for each invoke of sql*loader, event with rebuild parallel.


    For the remedy, after I remove those backed up archived log, set relevant tables and indexes to NOLOGGING mode. Ask application team to remove "deirct=true".

> select index_name, status from dba_indexes where index_name like 'M1_SUREPAY_%04%_IDX%' ;

INDEX_NAME                     STATUS
------------------------------ --------
NONVOICE04_IDX1     UNUSABLE
NONVOICE04_IDX2     UNUSABLE
NONVOICE04_IDX4     UNUSABLE
VOICE04_IDX1        VALID
VOICE04_IDX2        VALID
VOICE04_IDX3        VALID
VOICE04_IDX4        VALID
OTHER04_IDX2        VALID
OTHER04_IDX3        VALID
OTHER04_IDX1        VALID
NONVOICE04_IDX3     UNUSABLE

11 rows selected.

> alter index NONVOICE04_IDX1 rebuild parallel 3 ;

Index altered.

Elapsed: 00:02:43.39
> alter index NONVOICE04_IDX2 rebuild parallel 3 ;

Index altered.

Elapsed: 00:02:40.96
> alter index NONVOICE04_IDX3 rebuild parallel 3 ;

Index altered.

Elapsed: 00:02:23.70
> alter index NONVOICE04_IDX4 rebuild parallel 3 ;

Index altered.

alter index NONVOICE04_IDX1 noparallel  ;
alter index NONVOICE04_IDX2 noparallel  ;
alter index NONVOICE04_IDX3 noparallel  ;
alter index NONVOICE04_IDX4 noparallel  ;

Temporary segments do not span tablespaces of Temporary Tablespaces Group

I took for granted that single session with one big sort can benefit from tablespace group.
However, it is wrong ! No wonder our data warehouse often hit ora-1652.
--before make change , temp1,2,3 size is 16gb,16gb,19gb respectively
--This is means available temporary range is 16gb to 19gb.

The relevant metalink doc is 245645.1 and 248712.1

Reason is quite simple , Temporary segments do not span tablespaces. This easy to understand, same as other segment.


-- change support id to use small temp3 , which is the default database temporary tablespace (check from database_properties table)

-- remove temp1, temp2,temp3 from temp_group

alter tablespace TEMP1 tablespace group '';
alter tablespace TEMP2 tablespace group '';
alter tablespace TEMP3 tablespace group '';

--drop temp2

drop tablespace temp2 including contents and datafiles;

--epxand temp1;

--shrink temp3

--after make change , temp1,3 size is 40gb,10gb respectively
--assign application id to use big temporary tablespace


Since we don't parallel DML & have limited diskspace, tablespace group does't help.

From this practice, I think tablespace group is only good if you have lots of tablespace to create tablespaces with same size for round-robin assignment fashion.
Ideally, can create them on separate disks to reduce I/O contention.


How come important things is missed out in many articles searched by Google !?

ORA-00322: log 1 of thread 1 is not current copy

Fri Apr 24 11:27:38 2009
Errors in file /software/oradcms/product/9.2.0/rdbms/log/dcmss_ora_10640.trc:
ORA-00322: log 1 of thread 1 is not current copy
ORA-00312: online log 1 thread 1: '/dcms002/oradata/DCMSS/redo01b.log'
ORA-00322: log 1 of thread 1 is not current copy
ORA-00312: online log 1 thread 1: '/dcms001/oradata/DCMSS/redo01a.log'


In my case, it is caused by forget to backup control file , hence can't use current/future control file to manager old datafiles.
-- database is running in noarchivelog mode.

[Solution]

re-create the controfile.


during recreation of controfile, I hit below error, as get size of redo log from "ls" in bytes, which is not correct, after I change to 10240K . it passed through .
Cheers!


CREATE CONTROLFILE REUSE DATABASE "DCMSS" NORESETLOGS NOARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01163: SIZE clause indicates 10241 (blocks), but should match header 10240
ORA-01517: log member: '/dcms001/oradata/DCMSS/redo01a.log'

Use fsck to fix "grub error 21"

-- Last Night when I shutdown down windows XP, it hung, so as usually I pressed the Power off button to turn off it by force.

Oops, today when I powered it on. "Grub Error 21" is threw to me.

Googled that , "Grub Error 21" means can't find the disk.


-- I installed SUSE , Centos and Windows XP on this PC.
-- Grub Loader is the boot loader
-- Partition for XP is FAT32. Another FAT32 partition has my data near 100Gb . (created using Magic Partition)
-- The Disk is about two years old.
-- It is a SATA2 hard disk, but the motherboard only supports SATA-1. So jump is set to run at SATA-1 mode
-- Never have such problem ever with press power off button by force.

Goto BIOS setup, there is no such 320GB SATA hard disk detected by defaul. When Pressed Enter to search it manually. The size detected is 136Gb.

--Uninstalled the HD, and connect to my another PC , which is running 3 other linuxs: SUSE 10.3, CentOS(RHEL) 5.2, and Ubuntu 8.4 .

-- Attach the HD to it, luckily that BIOS can detect it . But when booting failed with all three linuxs. I am disapointed. Why ?!

Warned my girlfriend that all data might lost, including mine. The most important thing to her is she can't IPTV while lying on bed and may lose many digital photoes.

She is sad. :(

Actually, I have more concerns as I have 100Gb data there for ten years. Not all has backup.



Watching TV and thought again, what else I haven't tried.


Finally, I had the clue. The motherboard is new and support SATA2. Why I stilled set the jump to SATA-1 ?


Trying again ...
--Booting Failed with CentOS
--Booting Failed with Suse
--Ubuntu brought to rescue mode .

Do a fdisk -l /dev/sda

All partitions are there. Good!

-- Mount my data partition and move to another 500Gb hard disk without error. (This partition's filesystem looks ok).
-- Now I suspect there is some logical corruption (c:\ filesystem), instead of hardware corruption.
-- fsck come to my mind.
-- unmount my data partition /dev/sda8 and ensure no mounting for /dev/sda1
-- fsck /dev/sda1 . Waw there is many illegale file name there. I just drop or auto-rename them one by one.
-- About 15 mins past, the fsck finished.
-- Now verify with CentOS, which boot successfully.


Set back to SATA-1 and attach to orignal PC.
-- BIOS is able to detect is properly.
-- Continue to boot, no more grub error 21 and I see the XP Flag is flying again.

Cheers! Thanks to Ubuntu fsck.



Lession learned and doubts:

-- Press power off button with caution. This may corrupt the boot sector. (or caused by virus ?)
-- What is enhanced inside Ubuntu fsck? As can't work with suse /rhel linux ?
-- Does fsck support NTFS ? It looks FAT32 still support partition large than 32Gb well. (use magicPartition)

Merge Join caused Cartesian Product

--Merge Join caused Cartesian Product

1591 RPTOPR CREATE TABLE tmp_aio_acct_tp1 as SELECT distinct(a.customer_id),
1591 RPTOPR decode((c.bld_stat_id),1,'Blacklisted') blist_status, d.bdr_evs
1591 RPTOPR dt start_blist, d.bdr_blist_resn_type_id blist_reason FROM tmp_a
1591 RPTOPR io_acct_tb a, blist_cust b, blist_dtls c, blist_dtls_resn_code d
1591 RPTOPR -- WHERE rtrim(a.customer_id) = rtrim(b.blc_cust_id) and WHERE
1591 RPTOPR rtrim(a.customer_id) = b.blc_cust_id and b.bld_blist_id=c.bld_bl
1591 RPTOPR ist_id and b.bld_blist_id=d.bld_blist_id and d.bdr_evedt is NULL
1591 RPTOPR


--this query can't finish event after 10 hours.
--expect 16 hours to completed.
--notice the executions is exterm high.

^LSQL ordered by Executions DB/Inst: PRXP/PRXRP Snaps: 581-587
-> Total Executions: 96,444,779
-> Captured SQL account for 100.0% of Total

CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s) SQL Id
------------ --------------- -------------- ---------- ----------- -------------
96,183,306 0 0.0 0.00 0.00 b9ag273wzuhbx
Module: oracleODSP@ods01 (TNS V1-V3)
SELECT "BLC_CUST_ID","BLD_BLIST_ID" FROM "DBO"."BLIST_CUST" "B" WHERE "BLC_CUST_
ID"=:1 AND "BLD_BLIST_ID"=:2


Elapsed CPU Elap per % Total
Time (s) Time (s) Executions Exec (s) DB Time SQL Id
---------- ---------- ------------ ---------- ------- -------------
2,900 2,898 96,183,306 0.0 14.4 b9ag273wzuhbx
Module: oracleODSP@ods01 (TNS V1-V3)
SELECT "BLC_CUST_ID","BLD_BLIST_ID" FROM "DBO"."BLIST_CUST" "B" WHERE "BLC_CUST_
ID"=:1 AND "BLD_BLIST_ID"=:2


^LSQL ordered by Gets DB/Inst: PRXP/PRXRP Snaps: 581-587
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> Total Buffer Gets: 719,230,530
-> Captured SQL account for 99.9% of Total

Gets CPU Elapsed
Buffer Gets Executions per Exec %Total Time (s) Time (s) SQL Id
-------------- ------------ ------------ ------ -------- --------- -------------
319,369,588 96,183,306 3.3 44.4 2897.99 2900.45 b9ag273wzuhbx
Module: oracleODSP@ods01 (TNS V1-V3)
SELECT "BLC_CUST_ID","BLD_BLIST_ID" FROM "DBO"."BLIST_CUST" "B" WHERE "BLC_CUST_ID"=:1 AND "BLD_BLIST_ID"=:2



1591 RPTOPR 1 0 1 LOAD AS SELECT
1591 RPTOPR 2 1 1 SORT
1591 RPTOPR 3 2 1 NESTED LOOPS
1591 RPTOPR 4 3 1 NESTED LOOPS
1591 RPTOPR 5 4 1 MERGE JOIN
1591 RPTOPR 6 5 1 REMOTE BLIST_DTLS_RESN_CODE
1591 RPTOPR 7 5 2 BUFFER
1591 RPTOPR 8 7 1 TABLE ACCESS TMP_AIO_ACCT_TB
1591 RPTOPR 9 4 2 REMOTE BLIST_CUST
1591 RPTOPR 10 3 2 REMOTE BLIST_DTLS


-- the rows of these two tables explains the high number of executions.
-- 3 millions rows in BLIST_DTLS_RESN_CODE and one quarter records meet "d.bdr_evedt is NULL", 7k rows in TMP_AIO_ACCT_TB
-- 3200k/4 * 7k = 5600k * k = 5600 Millions



SELECT distinct(a.customer_id),
decode((c.bld_stat_id),1,'Blacklisted') blist_status, d.bdr_evsdt start_blist, d.bdr_blist_resn_type_id blist_reason
FROM t1 a, blist_cust b, blist_dtls c, blist_dtls_resn_code d
WHERE rtrim(a.customer_id) = b.blc_cust_id and b.bld_blist_id=c.bld_blist_id and b.bld_blist_id=d.bld_blist_id and d.bdr_evedt is NULL;

SELECT /*+ ordered use_nl(b c d) */
distinct(a.customer_id),
decode((c.bld_stat_id),1,'Blacklisted') blist_status, d.bdr_evsdt start_blist, d.bdr_blist_resn_type_id blist_reason
FROM t1 a, blist_cust b, blist_dtls c, blist_dtls_resn_code d
WHERE rtrim(a.customer_id) = b.blc_cust_id and b.bld_blist_id=c.bld_blist_id
and b.bld_blist_id=d.bld_blist_id and d.bdr_evedt is NULL;

1601 RPTOPR 0 17168 SELECT STATEMENT
1601 RPTOPR 1 0 1 SORT
1601 RPTOPR 2 1 1 NESTED LOOPS
1601 RPTOPR 3 2 1 NESTED LOOPS
1601 RPTOPR 4 3 1 NESTED LOOPS
1601 RPTOPR 5 4 1 TABLE ACCESS T1
1601 RPTOPR 6 4 2 REMOTE BLIST_CUST
1601 RPTOPR 7 3 2 REMOTE BLIST_DTLS
1601 RPTOPR 8 2 2 REMOTE BLIST_DTLS_RESN_CODE



-- finished within 1 mins



--try this hint (bad plan same as orginal)
SELECT /*+ use_nl(b c d) */
distinct(a.customer_id),
decode((c.bld_stat_id),1,'Blacklisted') blist_status, d.bdr_evsdt start_blist, d.bdr_blist_resn_type_id blist_reason
FROM t1 a, blist_cust b, blist_dtls c, blist_dtls_resn_code d
WHERE rtrim(a.customer_id) = b.blc_cust_id and b.bld_blist_id=c.bld_blist_id
and b.bld_blist_id=d.bld_blist_id and d.bdr_evedt is NULL;


1601 RPTOPR 0 4506 SELECT STATEMENT
1601 RPTOPR 1 0 1 SORT
1601 RPTOPR 2 1 1 NESTED LOOPS
1601 RPTOPR 3 2 1 NESTED LOOPS
1601 RPTOPR 4 3 1 MERGE JOIN
1601 RPTOPR 5 4 1 REMOTE BLIST_DTLS_RESN_CODE
1601 RPTOPR 6 4 2 BUFFER
1601 RPTOPR 7 6 1 TABLE ACCESS T1
1601 RPTOPR 8 3 2 REMOTE BLIST_CUST
1601 RPTOPR 9 2 2 REMOTE BLIST_DTLS

-
--tried ordered only

SELECT /*+ ordered */
distinct(a.customer_id),
decode((c.bld_stat_id),1,'Blacklisted') blist_status, d.bdr_evsdt start_blist, d.bdr_blist_resn_type_id blist_reason
FROM t1 a, blist_cust b, blist_dtls c, blist_dtls_resn_code d
WHERE rtrim(a.customer_id) = b.blc_cust_id and b.bld_blist_id=c.bld_blist_id
and b.bld_blist_id=d.bld_blist_id and d.bdr_evedt is NULL;

1601 RPTOPR 0 9458 SELECT STATEMENT
1601 RPTOPR 1 0 1 SORT
1601 RPTOPR 2 1 1 HASH JOIN
1601 RPTOPR 3 2 1 REMOTE BLIST_DTLS_RESN_CODE
1601 RPTOPR 4 2 2 HASH JOIN
1601 RPTOPR 5 4 1 TABLE ACCESS T1
1601 RPTOPR 6 4 2 REMOTE


--okay



--think driving_site should also work, but have no chance to test again.


The join operations group of hints controls how joined tables merge data together. A join
operation may direct the optimizer to choose the best path for retrieving all rows for a query
(throughput) or for retrieving the first row (response time).
while ORDERED tells the optimizer to join the tables based on their
order in the FROM clause using the first table listed as the driving table (accessed first).

--tuned queries from hours to seconds using this method


However, as to root casue , optimizer is still a black box to us!

structure of redo.log is exactly the same as its archived log

What happens when issue below command in the wrong database

recover data using backup control file until cancel;

This could be happened on Windows platform, if you unfortunately have the following settings.

1. Has below setting on both client & server node in sqlnet.ora
SQLNET.AUTHENTICATION_SERVICES = (NTS) #allow local login as sysdba
2. has the tnsnames.ora to point to remote database.

As REMOTE_OS_AUTHENT has no affect on Windows Native Authentication. The same information is passed
regardless of this setting.
-- bad control caused the disaster


Hence, after press enter, the database is likely become recovery mode. (You may not know immediately ...).

--think control file status changed

Finally you will know ...


When you try to recover it :

--you can't use real backuped control file without restore all backed up datafiles
--startup mount
--RECOVER database using backup controlfile until cancel ; (this control file is current file which just corrupted by the command)


it complains can't find the last archived log file . e.g. TEST10P_001_0627748383_17328.ARC
actually, it is the CURRENT redo log.


So what I did is , copy the redo.log to archived directory and rename it to TEST10P_001_0627748383_17328.ARC


then tried the command again


ORA-279 signalled during: ALTER DATABASE RECOVER database using backup controlfile until cancel ...
Fri May 23 16:16:58 2008
ALTER DATABASE RECOVER CONTINUE DEFAULT
Fri May 23 16:16:58 2008
Media Recovery Log G:\TESTDB\ORAARCH2\TEST10P\TEST10P_001_0627748383_17328.ARC


RECOVER database using backup controlfile until cancel ;

cheers ! I saw the message: Media Recovered.

Finally, "alter database open resetlogs;"


We are saved!

Heart beat dropped from 200 to 65.



From this case, I guess the file structure of redo.log is exactly the same as its archived log.

ORA-16014: log 2 sequence# 12 not archived, no available destinations

Mon May 12 16:43:32 2008

ORA-16014: log 2 sequence# 12 not archived, no available destinations

ORA-00312: online log 2 thread 1: '/apps/oracle/oradata/OCP10G/redo02.log'

Mon May 12 16:43:32 2008

Errors in file /apps/oracle/admin/OCP10G/bdump/ocp10g_arc0_10273.trc:

ORA-16014: log 2 sequence# 12 not archived, no available destinations

ORA-00312: online log 2 thread 1: '/apps/oracle/oradata/OCP10G/redo02.log'

Mon May 12 16:43:51 2008

Read of flashback database logfile 33, block 685 found corrupted data.

See trace file for more information.

Reread of flashback database logfile 33, block 685 found same corrupted data

Read of flashback database logfile 33, block 685 found corrupted data.

See trace file for more information.
Reread of flashback database logfile 33, block 685 found same corrupted data



Above error observed in my test database, verified that disk space still has enough space.

-- only 3 groups of redo log;
--the 2nd group looks corrupted and can't be resued.

alter system archive log all to '/home/oracle/';


SQL> alter database clear unarchived logfile group 2;

Database altered.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 18 52428800 1 NO CURRENT
1402127 12-MAY-08

2 1 0 52428800 1 YES UNUSED
1311896 29-APR-08

3 1 17 52428800 1 YES INACTIVE
1401025 12-MAY-08


SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 21 52428800 1 NO CURRENT
1405111 12-MAY-08

2 1 19 52428800 1 YES ACTIVE
1405094 12-MAY-08

3 1 20 52428800 1 YES ACTIVE
1405099 12-MAY-08


SQL> alter system switch logfile;

System altered.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 27 52428800 1 YES ACTIVE
1405140 12-MAY-08

2 1 28 52428800 1 NO CURRENT
1406092 12-MAY-08

3 1 26 52428800 1 YES INACTIVE
1405138 12-MAY-08



REF: http://www.dbforums.com/archive/index.php/t-1119443.html

http://www.databasedesign-resource.com/oracle-log-files.html



Review: This is a rare encountered scenario of redo log corrupted.

Clear A Log File If It Has Become Corrupt And Avoid Archiving

ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP ;

another relevant command is

1. This statement overcomes two situations where dropping redo logs is not possible: If there are only two log groups and if the corrupt redo log file belongs to the current group:
ALTER DATABASE CLEAR LOGFILE GROUP 4;


Clearing Online Redo Log Files
Under certain circumstances, a redo log group member (or all members of a log group) can
become corrupted. To solve this problem, you can drop and re-add the log file group or group
member. It is much easier, however, to use the ALTER DATABASE CLEAR LOGFILE command. The
following example clears the contents of redo log group 3 in the database:
ALTER DATABASE CLEAR LOGFILE GROUP 3;
Another distinct advantage of this command is that you can clear a log group even if the database
has only two log groups and only one member in each group. You can also clear a log
group member even if it has not been archived by using the UNARCHIVED keyword. In this case,
it is advisable to do a full database backup at the earliest convenience, because the unarchived
redo log file is no longer usable for database recovery.

large pool for shared server + sga_target

Tue May 6 11:43:54 2008
Errors in file /software/oraprx/admin/PRXP/bdump/prxp_s001_26546.trc:
ORA-00600: internal error code, arguments: [17183], [0xC0000000651B8AF8], [], [], [], [], [], []
ORA-02071: error initializing capabilities for remote database CATP.WORLD
ORA-04031: unable to allocate 27168 bytes of shared memory ("large pool","SELECT ACCOUNT_NO FROM IVOC_...","session heap","oper caps")

Tue May 6 13:10:35 2008
Errors in file /software/oraprx/admin/PRXP/bdump/prxp_s003_26550.trc:
ORA-00600: internal error code, arguments: [17285], [0xC000000065C3DE20], [1], [0xC0000000A78B0EC8], [], [], [], []
ORA-04031: unable to allocate 2520 bytes of shared memory ("large pool","unknown object","session heap","koh-kghu session heap")


- Set SORT_AREA_SIZE, SORT_AREA_RETAINED_SIZE and HASH_AREA_SIZE to much lower
values, say, 64K – 1MB to resolve this problem. The smaller value for these
parameters will be OK for an OLTP system.

When we use Shared Servers (MTS), memory used for sort and hash area will be
allocated from the large pool for the MTS connections even if you are configuring
Automatic PGA feature by setting PGA_AGGREGATE_TARGET and WORKAREA_SIZE_POLICY.

If you also set PGA_AGGREGATE_TARGET parameter to a non-zero value and
WORKAREA_SIZE_POLICY to auto, it will be used by dedicated server connections
in the database for sort and hash area memory but not the MTS connections.

Check current sql execution plan and historical plan

get sql_id from v$sqlarea

alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';

--for current execution plan
select operation,object_name,id,parent_id, position,cost,timestamp from v$sql_plan where sql_id='6y9n667q3wgb6';

--for historical plan
select operation,object_name,id,parent_id, position,cost,timestamp from dba_hist_sql_plan where sql_id ='01y6hmraf8kz6' order by timestamp, id,parent_id,position;

--based on count(distinct(timestamp))>1 , you should be alerted that execution plan is changed.

alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';

select sql_id, count(distinct(timestamp)) from dba_hist_sql_plan where object_owner not in ('SYS','SYSMAN','MGMT_BSLN','DBSNMP') group by sql_id having count(distinct(timestamp))>1;

Import statistics failed due to object out of synch

Elapsed: 00:00:05.57
SQL> EXEC DBMS_STATS.IMPORT_SCHEMA_STATS('DBO','STAT_TABLE',statid=>'run_new',statown=>'DBO');
BEGIN DBMS_STATS.IMPORT_SCHEMA_STATS('DBO','STAT_TABLE',statid=>'run_new',statown=>'DBO'); END;

*
ERROR at line 1:
ORA-20000: partition "CCE_OCT2004" of table "DBO"."CUSTOMER_CONTACT_EVENT" does not exist or insufficient privileges
ORA-06512: at "SYS.DBMS_STATS", line 6712
ORA-06512: at "SYS.DBMS_STATS", line 6738
ORA-06512: at "SYS.DBMS_STATS", line 7270
ORA-06512: at line 1


Elapsed: 00:00:12.92
SQL>
SQL> spool off

select * FROM dbo.stat_table where c5 = 'DBO' and c2='CCE_OCT2004';


SQL> delete FROM dbo.stat_table where c5 = 'DBO' and c2='CCE_OCT2004';

29 rows deleted.

Elapsed: 00:00:00.05

SQL> EXEC DBMS_STATS.IMPORT_SCHEMA_STATS('DBO','STAT_TABLE',statid=>'run_new',statown=>'DBO');
SQL> commit;

sga_target and its compoents

Although we see Oracle make it easier to dynamically manager its compoments: shared pool, large pool, buffer cache , java pool .

However, based on my bleeding experience, it is better estimate & set a minimal value for them, especially for:
1. shared pool
2. large pool if you are using shared server.

monitor its historical size and set at least 85% of maximum size as minimal size, is my recommendation.

Too small value of shared pool may result in execution plan changed.

Too small large pool may caused ora-4031 related to large pool (shared server connection)

Below two statements help.
--check on the breakdown of sga usage.
set linesize 132 pagesize 5000
col snap_time format a30
select s2.begin_interval_time snap_time,pool sga_component,sum(bytes) bytes from
dba_hist_sgastat s1, dba_hist_snapshot s2
where s1.pool is not null
and s1.snap_id=s2.snap_id
group by s2.begin_interval_time,pool
union
select s2.begin_interval_time snap_time,name sga_component, bytes from dba_hist_sgastat s1, dba_hist_snapshot s2
where name ='buffer_cache'
and s1.snap_id=s2.snap_id
ORDER BY 2,1
/

select component,current_size from v$sga_dynamic_components;

COMPONENT CURRENT_SIZE
---------------------------------------------------------------- ------------
shared pool 671088640
large pool 536870912
java pool 16777216
streams pool 0
DEFAULT buffer cache 1493172224
KEEP buffer cache 0
RECYCLE buffer cache 0
DEFAULT 2K buffer cache 0
DEFAULT 4K buffer cache 0
DEFAULT 8K buffer cache 0
DEFAULT 16K buffer cache 0
DEFAULT 32K buffer cache 0
ASM Buffer Cache 0

13 rows selected.

SQL> alter system set shared_pool_size=750M;

System altered.

SQL> set pages 1000
SQL> select component,current_size from v$sga_dynamic_components;

COMPONENT CURRENT_SIZE
---------------------------------------------------------------- ------------
shared pool 788529152
large pool 536870912
java pool 16777216
streams pool 0
DEFAULT buffer cache 1375731712
KEEP buffer cache 0
RECYCLE buffer cache 0
DEFAULT 2K buffer cache 0
DEFAULT 4K buffer cache 0
DEFAULT 8K buffer cache 0
DEFAULT 16K buffer cache 0
DEFAULT 32K buffer cache 0
ASM Buffer Cache 0

13 rows selected.

WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK

Hit below error, symptoms:
1. cpu 100% or server almost hang.
2. can't connect /as sysdba

solution:
ps -ef |grep -i beq
and kill those accordingly

root cause:
so far don't know. why it is ora-4031?


Sat Apr 19 03:50:02 2008
Errors in file /software/oracle/admin/CUST33B/bdump/cust33b_cjq0_15634.trc:
ORA-07445: exception encountered: core dump [kghpir()+32] [SIGSEGV] [Address not mapped to object] [0xFFFFFFFFFFFFFFD0] [] []
Sat Apr 19 03:51:15 2008
Restarting dead background process CJQ0
CJQ0 started with pid=10, OS id=18756
Sat Apr 19 04:00:32 2008
Thread 1 advanced to log sequence 9558
Current log# 2 seq# 9558 mem# 0: /bkp003/oradata/CUST33B/redoCUST33B02a.log
Current log# 2 seq# 9558 mem# 1: /bkp003/oradata/CUST33B/redoCUST33B02b.log
Sat Apr 19 04:16:46 2008
Errors in file /software/oracle/admin/CUST33B/bdump/cust33b_mmon_15636.trc:
ORA-00600: internal error code, arguments: [17148], [0xC0000004C22A2648], [], [], [], [], [], []
Sat Apr 19 04:17:13 2008
ORA-600 encountered when generating server alert SMG-3503
Sat Apr 19 04:42:18 2008
Errors in file /software/oracle/admin/CUST33B/bdump/cust33b_j000_22062.trc:
ORA-00600: internal error code, arguments: [17148], [0xC0000004C2604248], [], [], [], [], [], []
Sat Apr 19 05:23:14 2008
Errors in file /software/oracle/admin/CUST33B/bdump/cust33b_s000_15642.trc:
ORA-00600: internal error code, arguments: [17148], [0xC0000004C37E10E0], [], [], [], [], [], []
Sat Apr 19 06:03:54 2008
Errors in file /software/oracle/admin/CUST33B/bdump/cust33b_j000_29503.trc:
ORA-12012: error on auto execute of job 23935
ORA-04031: unable to allocate ORA-04031: unable to allocate 61912 bytes of shared memory ("shared pool","DBMS_STATS","PL/SQL MPCODE","BAMIMA: Bam Buffer")
bytes of shared memory ("","","","")
Sat Apr 19 07:21:52 2008
Thread 1 advanced to log sequence 9559
Current log# 3 seq# 9559 mem# 0: /bkp003/oradata/CUST33B/redoCUST33B03a.log
Current log# 3 seq# 9559 mem# 1: /bkp003/oradata/CUST33B/redoCUST33B03b.log
Sat Apr 19 07:51:32 2008
Errors in file /software/oracle/admin/CUST33B/bdump/cust33b_mmon_15636.trc:
ORA-00600: internal error code, arguments: [17148], [0xC0000004C3D88198], [], [], [], [], [], []
Sat Apr 19 07:51:44 2008
ORA-600 encountered when generating server alert SMG-3503
Sat Apr 19 08:04:08 2008
Errors in file /software/oracle/admin/CUST33B/bdump/cust33b_q000_15727.trc:
ORA-00600: internal error code, arguments: [17148], [0xC0000004C2D63BD0], [], [], [], [], [], []
Sat Apr 19 08:04:37 2008
Errors in file /software/oracle/admin/CUST33B/bdump/cust33b_q000_15727.trc:
ORA-00600: internal error code, arguments: [17148], [0xC0000004C2D63BD0], [], [], [], [], [], []
Sat Apr 19 08:09:41 2008
Errors in file /software/oracle/admin/CUST33B/bdump/cust33b_m000_13077.trc:
ORA-07445: exception encountered: core dump [kghpir()+32] [SIGSEGV] [Address not mapped to object] [0xFFFFFFFFFFFFFFD0] [] []
Sat Apr 19 08:32:51 2008
System State dumped to trace file /software/oracle/admin/CUST33B/bdump/cust33b_reco_15630.trc
Sat Apr 19 08:34:38 2008
Errors in file /software/oracle/admin/CUST33B/bdump/cust33b_reco_15630.trc:
ORA-00604: error occurred at recursive SQL level 2
ORA-44203: timeout waiting for lock on cursor
Sat Apr 19 09:23:09 2008
System State dumped to trace file /software/oracle/admin/CUST33B/bdump/cust33b_reco_15630.trc
Sat Apr 19 09:24:35 2008
Errors in file /software/oracle/admin/CUST33B/bdump/cust33b_reco_15630.trc:
ORA-00604: error occurred at recursive SQL level 2
ORA-44203: timeout waiting for lock on cursor
Sat Apr 19 10:13:17 2008
System State dumped to trace file /software/oracle/admin/CUST33B/bdump/cust33b_reco_15630.trc
Sat Apr 19 10:14:50 2008
Errors in file /software/oracle/admin/CUST33B/bdump/cust33b_reco_15630.trc:
ORA-00604: error occurred at recursive SQL level 2
ORA-44203: timeout waiting for lock on cursor
Sat Apr 19 11:03:24 2008
System State dumped to trace file /software/oracle/admin/CUST33B/bdump/cust33b_reco_15630.trc
Sat Apr 19 11:04:34 2008
Errors in file /software/oracle/admin/CUST33B/bdump/cust33b_reco_15630.trc:
ORA-00604: error occurred at recursive SQL level 2
ORA-44203: timeout waiting for lock on cursor
Sat Apr 19 11:53:29 2008
System State dumped to trace file /software/oracle/admin/CUST33B/bdump/cust33b_reco_15630.trc
Sat Apr 19 11:54:38 2008
Errors in file /software/oracle/admin/CUST33B/bdump/cust33b_reco_15630.trc:
ORA-00604: error occurred at recursive SQL level 2
ORA-44203: timeout waiting for lock on cursor
Sat Apr 19 12:43:15 2008
System State dumped to trace file /software/oracle/admin/CUST33B/bdump/cust33b_reco_15630.trc
Sat Apr 19 12:44:27 2008
Errors in file /software/oracle/admin/CUST33B/bdump/cust33b_reco_15630.trc:
ORA-00604: error occurred at recursive SQL level 2
ORA-44203: timeout waiting for lock on cursor
Sat Apr 19 13:33:12 2008
System State dumped to trace file /software/oracle/admin/CUST33B/bdump/cust33b_reco_15630.trc
Sat Apr 19 13:34:34 2008
Errors in file /software/oracle/admin/CUST33B/bdump/cust33b_reco_15630.trc:
ORA-00604: error occurred at recursive SQL level 2
ORA-44203: timeout waiting for lock on cursor

Sun Apr 20 13:05:58 2008
Errors in file /software/oracle/admin/CUST33B/bdump/cust33b_reco_15630.trc:
ORA-00604: error occurred at recursive SQL level 2
ORA-44203: timeout waiting for lock on cursor
Sun Apr 20 13:11:11 2008
>>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=23
System State dumped to trace file /software/oracle/admin/CUST33B/udump/cust33b_ora_22232.trc

Mon Apr 21 11:38:25 2008
>>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=27
dev07:B.11:CUST33B:/software/oracle/admin/CUST33B/bdump> ps -ef |grep CUST33B
oracle 15636 1 0 Apr 13 ? 64:47 ora_mmon_CUST33B
oracle 15620 1 0 Apr 13 ? 5:18 ora_dbw0_CUST33B
oracle 15640 1 255 Apr 13 ? 2627:20 ora_d000_CUST33B
oracle 13858 13857 0 Apr 20 ? 2:26 oracleCUST33B (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 16786 16783 0 10:48:08 ? 0:47 oracleCUST33B (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 26564 26563 0 14:51:25 ? 1:36 oracleCUST33B (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 15614 1 0 Apr 13 ? 4:16 ora_psp0_CUST33B
oracle 127 1 0 13:00:36 ? 0:01 ora_q000_CUST33B
oracle 13246 1 0 Apr 19 ? 1:27 ora_q002_CUST33B
oracle 7012 16816 0 14:12:11 pts/2 0:00 grep CUST33B
oracle 18756 1 0 Apr 19 ? 54:59 ora_cjq0_CUST33B
oracle 15676 1 0 Apr 13 ? 2:08 ora_qmnc_CUST33B
oracle 3746 3742 0 Apr 20 ? 2:03 oracleCUST33B (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 9931 1 255 Apr 20 ? 841:35 oracleCUST33B (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 15730 1 0 Apr 13 ? 7:29 ora_q001_CUST33B
oracle 15638 1 0 Apr 13 ? 20:15 ora_mmnl_CUST33B
oracle 15628 1 0 Apr 13 ? 19:33 ora_smon_CUST33B
oracle 22232 1 0 Apr 20 ? 2:01 oracleCUST33B (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 15630 1 0 Apr 13 ? 48:06 ora_reco_CUST33B
oracle 15624 1 0 Apr 13 ? 5:25 ora_lgwr_CUST33B
oracle 15618 1 0 Apr 13 ? 3:23 ora_mman_CUST33B
oracle 28226 1 0 15:04:15 ? 1:47 oracleCUST33B (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 15626 1 0 Apr 13 ? 10:31 ora_ckpt_CUST33B
oracle 7404 1 255 Apr 19 ? 2063:13 ora_j000_CUST33B
oracle 15612 1 255 Apr 13 ? 2045:52 ora_pmon_CUST33B
dev07:B.11:CUST33B:/software/oracle/admin/CUST33B/bdump> kill 3746 9931 22232 2822613858 16786 26564

browser crashed while invoke Java applet

Today I can't access all my internet bankings. all browsers (firefox, IE, safari) crashed when I access banking.

Tried on other computer, it works. The problem must be within my laptop.

What was the change ?
-- Upgraded OpenOffiice from 2.3 to 2.4 ?
-- firefox upgraded to 2.0.0.13 to 2.0.0.14 ?
-- any stubbed file in temporary directory ?
-- Because of spyware software ?

After one day's hard thinking. I think it must related to JAVA.

Checked the folder, oh my godness on 16-Apr it is automatically update from jre1.6.0_03 to jre1.6.0_04.

Suspected jre1.6.0_04 has problem. Tried to check any update there, yes there is jre1.6.0_05 ready for update, but as of this moment, has problem with the upgrade (some error related to can't find file for copy)

Rollback to jre1.6.0_03 (uninstall jre1.6.0_04) , everything works fine now

Cheers!

Something learned, don't upgrade to latest version without notification or eager to do so. Let it stable first !!!


--this morning find SUN gave the solution of offline installation.
http://java.com/en/download/help/index.xml

lost domain controller leads to SQL server 26-Error

[THE PROBLEM
]TITLE: Connect to Server
------------------------------

Cannot connect to m1dwdev\dw_dev.

------------------------------
ADDITIONAL INFORMATION:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)



Many are discussed in http://blogs.msdn.com/sql_protocols/archive/2007/05/13/sql-network-interfaces-error-26-error-locating-server-instance-specified.aspx

but none is related to domain controller.

The error message in event viewer is:

Event Type: Error
Event Source: NETLOGON
Event Category: None
Event ID: 5719
Date: 4/18/2008
Time: 4:47:37 AM
User: N/A
Computer: M1DWDEV
Description:
No Windows NT or Windows 2000 Domain Controller is available for domain M1. The following error occurred:
There are currently no logon servers available to service the logon request.
Data:
0000: 5e 00 00 c0 ^..à


[THE SOLUTION]
Disable/Enable network. Problem solved.

How to find the SQL use massive shared pool

Live case learned today.

One user query used up to 740Mb shared pool. Surprising ? ! You can guess the impact.
Of course I am suspecting we are hitting a bug. The only things to note is that there about 260 values inside IN clause.

--Finding statement/s which use lots of shared pool memory:

SELECT substr(sql_text,1,40) "Stmt", count(*),
sum(sharable_mem) "Mem",
sum(users_opening) "Open",
sum(executions) "Exec"
FROM v$sql
GROUP BY substr(sql_text,1,40)
HAVING sum(sharable_mem) > 10485760 ;


--then join with v$session to find out the user id, program

set pagesize 24
set newpage 1

set linesize 125

column sql_text format a100

column user_name format a12

select

u.sid, serial#

substr(u.username,1,12) user_name,

s.sql_text

from

v$sql s,

v$session u

where

s.hash_value = u.sql_hash_value

and

sql_text like '%%'

;

--terminate the DB session
alter system kill session ',';

--although the status in v$session may be marked as KILLED
-- Monitor the shared pool used by the session may still keep growing , as the OS process is still alive

--join with v$process to get OS pid
--use kill -6 to kill the process gracefully, even it is a shared server process. This may take up to 15 minutes to finish, as process rollback in the background. The CPU could 100% for this process. Worth and safe to wait.
-- after this plan downtime to restart database as soon as possible , since we killed a SHARED SERVER. Otherwise may still face ora-00600 and ora-00745