tag:blogger.com,1999:blog-81144088301898799892024-02-02T10:41:07.188-08:00Yet Another Oracle DBA BlogChandra Pabbahttp://www.blogger.com/profile/15514315184841775273noreply@blogger.comBlogger32125tag:blogger.com,1999:blog-8114408830189879989.post-73438126643847661442010-04-12T10:18:00.000-07:002010-04-12T10:47:17.192-07:00Converting time values in trace files into wall-clock timeThere may be cases where you would like to know the exact wall-clock time when a particular wait-event occurred or the SQL was executed by looking at the trace file.<br />Oracle captures the wall-clock time in the trace files and represents it in the following format:<br /><br />AIT #2: nam='SQL*Net message from client' ela= 16445143 driver id=1650815232 #bytes=1 p3=0 obj#=-1 <span style="font-weight: bold;">tim=1271092519750890<br /></span><span><br />The value, we are interested in is the last one (</span><span>tim=1271092519750890).<br /><br /><br />The tim value can be converted into wall-clock time by using the following:<br /><br />Take the first 10 digits, and then:<br /><br />$date -d @1271092519 +"%m-%d-%Y %T"<br /><br />Example:</span><span> </span><span>tim=1271092519750890, first 10 characters - </span><span>1271092519<br /><br /><span style="color: rgb(255, 0, 0);">$date -d @1271092519 +"%m-%d-%Y %T"</span><span style="color: rgb(255, 0, 0);"><br /><br />04-12-2010 12:15:19</span></span><span style="font-weight: bold;"><br /><br /></span><span>This could be useful in situations where you would like to find out when a user session completed execution, went back to the client to do some other processing and when it came back to database to execute subsequent SQLs.</span><span style="font-weight: bold;"><span style="font-weight: bold;"><span style="font-weight: bold;"><span style="font-weight: bold;"><br /><br /></span></span></span></span><span><span><span><span>BTW, there seems to be a difference in how this value is represented in 11g compared previous versions. I could not get the accurate wall-clock time for a tim value from 10g database. </span></span></span></span><span style="font-weight: bold;"><span style="font-weight: bold;"><span style="font-weight: bold;"><span style="font-weight: bold;"><br /><br /></span></span></span><br /></span>Chandra Pabbahttp://www.blogger.com/profile/15514315184841775273noreply@blogger.com0tag:blogger.com,1999:blog-8114408830189879989.post-46082028468771500022010-02-28T11:37:00.001-08:002010-02-28T11:40:44.636-08:00ORA-27504 while starting 11gR1 database with 11gR2 Grid InfrastructureI have Oracle11g R2 (11.2.0.1) Grid infrastructure on a two node RAC. Installed 11.1.0.7 binaries and when attempting to create a database, encountered the following errors:<br /><br />ORA-27504: IPC error creating OSD context<br />ORA-27300: OS system dependent operation:skgxnqtsz failed with <br /><br />status: 0<br />ORA-27301: OS failure message: Error 0<br />ORA-27302: failure occurred at: SKGXN not av<br />clsssinit ret = 21<br />interconnect information is not available from OCR<br /><br />On researching, found the following solution to fix those errors and was able to create the 11.1.0.7 database with 11gR2 grid infrastructure:<br /><br />As root:<br /><br />[root@eletrac01 bin]# ./olsnodes -t -n<br /><br />eletrac01 1 Unpinned<br />eletrac02 2 Unpinned<br /><br /><br />[root@eletrac01 bin]# ./crsctl pin css -n eletrac01 eletrac02<br />CRS-4664: Node eletrac01 successfully pinned.<br />CRS-4664: Node eletrac02 successfully pinned.<br />[root@eletrac01 bin]#<br /><br />[root@eletrac01 bin]# ./olsnodes -t -n<br />eletrac01 1 Pinned<br />eletrac02 2 Pinned<br />[root@eletrac01 bin]#Chandra Pabbahttp://www.blogger.com/profile/15514315184841775273noreply@blogger.com1tag:blogger.com,1999:blog-8114408830189879989.post-50184572429386461572010-01-16T19:34:00.000-08:002010-01-16T19:53:17.982-08:00Failed Switchover - Oracle11gI recently had a situation where we had a failed switchover process. Was able to switch the Primary to Standby successfully, but encountered some issues while switching over standby to primary - therefore ended up having two standbys! The easiest alternative to get out of the situation would be to ACTIVATE one of the standbys and then rebuild the primary - but it's not always desirable (size, efforts, unprotected primary etc..). <br /> <br />We even attempted the oracle provided command to cancel the switchover, but encountered the following (On original primary):<br /> <br />SQL> ALTER DATABASE PREPARE TO SWITCHOVER CANCEL;<br />ALTER DATABASE PREPARE TO SWITCHOVER CANCEL<br />*<br />ERROR at line 1:<br />ORA-00600: internal error code, arguments: [adbdrv_switch], [384], [], [], [],<br />[], [], [], [], [], [], []<br /> <br />Here is I ended up doing:<br /> <br />All of these have been performed on original primary:<br />1. Startup Mount;<br />SQL> startup force nomount;<br />ORACLE instance started.<br /> <br />Total System Global Area 238530560 bytes<br />Fixed Size 1335724 bytes<br />Variable Size 150998612 bytes<br />Database Buffers 83886080 bytes<br />Redo Buffers <br /> <br />2. Recreate the controlfile using the trace (got from standby)<br />SQL> @scripts/ccf.sql<br /> <br />Control file created.<br /> <br />SQL> alter database open;<br />alter database open<br />*<br />ERROR at line 1:<br />ORA-01589: must use RESETLOGS or NORESETLOGS option for database open<br /> <br /> <br />SQL> alter database open resetlogs;<br /> <br />Database altered.<br /> <br />SQL> <br />3. That's it…that did it!<br /> <br />SQL> Select open_mode, database_role, switchover_status from V$database;<br /> <br />OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS<br />-------------------- ---------------- --------------------<br />READ WRITE PRIMARY TO STANDBY<br /> <br />SQL><br /> <br />4. Here are the entries from standby - didn't have to do anything - it continued through RESETLOGS!<br /> <br />RFS[18]: New Archival REDO Branch(resetlogs_id): 708469623 Prior: 708454293<br />RFS[18]: Archival Activation ID: 0x6fece72a Current: 0x0<br />RFS[18]: Effect of primary database OPEN RESETLOGS<br />RFS[18]: Incarnation entry added for Branch(resetlogs_id): 708469623 (ETSI01)<br />Sat Jan 16 21:08:09 2010<br />Setting recovery target incarnation to 4<br />Sat Jan 16 21:08:09 2010<br />Archived Log entry 33 added for thread 1 sequence 1 ID 0x6fece72a dest 1:<br />Sat Jan 16 21:08:09 2010<br />RFS[19]: Assigned to RFS process 5022Chandra Pabbahttp://www.blogger.com/profile/15514315184841775273noreply@blogger.com0tag:blogger.com,1999:blog-8114408830189879989.post-90864066082970381152009-12-15T07:19:00.001-08:002009-12-15T07:19:53.796-08:00Segment Comparison (structure) in Oracle11gThere are bunch third party products in the market which do a pretty good job at finding out the structual differences between objects or schemas. Toad comes to my mind which does an outstanding job...but it costs $$$! With Oracle11g, it's quite easy to figure out differences between two objects. Please note that use of this feature needs Change Management pack license. This is available with 11.1.0.7, but not documented in 11gR1 docs, but does have a mentioned in 11gR2 docs. The API or the package is DBMS_METADATA. Here is how it works:<br /><br />Let's say we have two schemas in different databases and would like to compare the table structures:<br /><br /><span style="font-size:85%;"><span style="font-family:courier new;">SQL> Select dbms_metadata_diff.compare_alter('TABLE','APP_DATA','APP_DATA_TEMP', 'CHANDRA','CHANDRA',null,'BDBE01') DIFF from dual;</span><br /><br /><span style="font-family:courier new;">DIFF</span><br /><span style="font-family:courier new;">--------------------------------------------------------------------------------</span><br /><span style="font-family:courier new;">ALTER TABLE "CHANDRA"."APP_DATA" ADD ("CREATE_DATE" DATE)</span><br /><span style="font-family:courier new;"> ALTER TABLE "CHANDRA"."APP_DATA" ADD ("UPDATE_DATE" DATE)</span><br /><span style="font-family:courier new;"> ALTER TABLE "CHANDRA"."APP_DATA" RENAME TO "APP_DATA_TEMP"</span></span><br /><br />Explanation for the argument used in the function call: Object_type, table_name, table_name, schema_owner, schema_owner, db_link_for_source (if null, then local), db_link_for_target.<br /><br />If the order of table names, source and target are changed:<br /><br /><span style="font-size:85%;"><span style="font-family:courier new;">SQL> Select dbms_metadata_diff.compare_alter('TABLE','APP_DATA_TEMP','APP_DATA', 'CHANDRA','CHANDRA','BDBE01',null) DIFF from dual;</span><br /><br /><span style="font-family:courier new;">DIFF</span><br /><span style="font-family:courier new;">--------------------------------------------------------------------------------</span><br /><span style="font-family:courier new;">ALTER TABLE "CHANDRA"."APP_DATA_TEMP" DROP ("CREATE_DATE")</span><br /><span style="font-family:courier new;"> ALTER TABLE "CHANDRA"."APP_DATA_TEMP" DROP ("UPDATE_DATE")</span><br /><span style="font-family:courier new;"> ALTER TABLE "CHANDRA"."APP_DATA_TEMP" RENAME TO "APP_DATA"</span><br /><br /></span>Interesting, it even has an ALTER to rename the table to make both same in all respects.<br /><br />I don't see an option to include or exclude various segment attributes such as storage, tablespace etc...may be because the intent of this API seems to be different. Thought this oracle provided utility would come in handy to quickly find out the structural differences.Chandra Pabbahttp://www.blogger.com/profile/15514315184841775273noreply@blogger.com0tag:blogger.com,1999:blog-8114408830189879989.post-59661876588012805062009-09-14T12:23:00.000-07:002009-09-14T12:48:46.490-07:00Oracle11gR2: Deferred Segment Creation and export behavior!I guess by now most of us know what Oracle11gR2's feature - Deferred Segment Creation really means. This post is about the impact this feature going to have while exporting a schema or tables using the traditional export. By default, any new tables created would have SEGMENT CREATION DEFERRED option set (unless you are using CTAS). This default behavior could be turned off by setting DEFERRED_SEGMENT_CREATION to false. The impact with this is - if you happen to export any of the tables, which do not have any rows (meaning empty tables without segments) - they will NOT be exported. This is only true with the legacy/old export and not with the export pump utility(10g and above). Something we have to remember if we can't get rid of the old habit of using old export! Of course, export is deprecated.Chandra Pabbahttp://www.blogger.com/profile/15514315184841775273noreply@blogger.com1tag:blogger.com,1999:blog-8114408830189879989.post-7169063645821192522009-09-14T10:31:00.000-07:002009-09-22T07:51:37.599-07:00Oracle11gR2 Table / Tablespace CompressionAs far as I remember, oracle introduced the basic compression feature with Oracle9i, but what's new with 11g is the ability to compress data generated as a result of OLTP transactions (almost all DMLs) as well - previously it was only available for bulk operations. Another neat feature of 11g is the ability to evaluate whether not a table is a right candidate for compression , if yes what would be compression benefits in regards space utilization. This is done using DBMS_COMPRESSION (using Compression Advisor). I wanted to use this procedure to see how accurately it could give us the space savings and also see what would be impact on performance, in general.
<br />
<br />I am using a 18million record table for this example.
<br />
<br />1. Space usage for the uncompressed table:
<br /><strong style="font-weight: normal;"></strong>
<br /><pre id="line98">
<br /></pre><meta equiv="CONTENT-TYPE" content="text/html; charset=utf-8"><title></title><meta name="GENERATOR" content="OpenOffice.org 3.1 (Win32)"><style type="text/css"> <!-- @page { margin: 0.79in } P { margin-bottom: 0.08in } --></style><span style="font-size:85%;"><span style="font-family:courier new;"> SQL> select segment_name, bytes, a.blocks, compression, compress_for from </span>
<br /><span style="font-family:courier new;"> 2 dba_segments a, dba_tables b</span>
<br /><span style="font-family:courier new;"> 3 where a.segment_name = b.table_name and a.segment_name='T1';</span>
<br /></span>
<br /><span style="font-size:85%;"><span style="font-family:courier new;">SEGMENT_NAME BYTES BLOCKS COMPRESS COMPRESS_FOR</span>
<br /><span style="font-family:courier new;">------------------------------ ---------- ---------- -------- ------------</span>
<br /><span style="font-family:courier new;">T1 1476395008 180224 DISABLED</span></span>
<br /><code></code>
<br />
<br />2. Execute the DBMS_COMPRESSION procedure to get information on space savings:
<br />
<br />(Note that this procedure example works only in 11gR2, the function names and the arguments are different in 11gR1.)
<br />
<br /><span style=";font-family:courier new;font-size:85%;" >SQL> declare
<br />2 lv_cmp_ratio number;
<br />3 lv_comptype_str varchar2(300);
<br />4 lv_BLKCNT_CMP number;
<br />5 lv_BLKCNT_UNCMP number;
<br />6 lv_ROW_CMP number;
<br />7 lv_ROW_UNCMP number;
<br />8 begin
<br />9 dbms_compression.GET_COMPRESSION_RATIO(
<br />10 SCRATCHTBSNAME=>'USERS',
<br />11 OWNNAME=>'CHANDRA',
<br />12 TABNAME=>'T1',
<br />13 PARTNAME =>null,
<br />14 COMPTYPE =>2, ---2 means OLTP
<br />15 BLKCNT_CMP=>lv_BLKCNT_CMP,
<br />16 BLKCNT_UNCMP=>lv_BLKCNT_UNCMP,
<br />17 ROW_CMP =>lv_ROW_CMP,
<br />18 ROW_UNCMP =>lv_ROW_UNCMP,
<br />19 CMP_RATIO=>lv_cmp_ratio,
<br />20 COMPTYPE_STR=>lv_COMPTYPE_STR);
<br />21 dbms_output.put_line('====================================================');
<br />22 dbms_output.put_line('1. Compression Ratio :'||lv_cmp_ratio);
<br />23 dbms_output.put_line('2. Block Count :'||lv_blkcnt_cmp);
<br />24 dbms_output.put_line('3. Compression Type :'||lv_comptype_str);
<br />25 dbms_output.put_line('4. Blk Count Compressed :'||lv_BLKCNT_CMP);
<br />26 dbms_output.put_line('5. Blk Count Un-compressed:'||lv_BLKCNT_UNCMP);
<br />27 dbms_output.put_line('6. Row Count Compressed :'||lv_row_cmp);
<br />28 dbms_output.put_line('4. Row Count Un-Compressed:'||lv_row_uncmp);
<br />29 dbms_output.put_line('====================================================');
<br />30 end;
<br />31 /</span>
<br /><span style="color: rgb(255, 102, 102);">====================================================</span>
<br /><span style="color: rgb(255, 102, 102);">1. Compression Ratio :2.38995215311004784688995215311004784689</span>
<br /><span style="color: rgb(255, 102, 102);">2. Block Count :836</span>
<br /><span style="color: rgb(255, 102, 102);">3. Compression Type :"Compress For OLTP"</span>
<br /><span style="color: rgb(153, 0, 0); font-weight: bold;">4. Blk Count Compressed :836</span>
<br /><span style="color: rgb(153, 0, 0); font-weight: bold;">5. Blk Count Un-compressed:1998</span>
<br /><span style="color: rgb(255, 102, 102);"><span style="font-style: italic;">6. Row Count Compressed</span> :<span style="color: rgb(51, 204, 0); font-weight: bold;">256</span></span>
<br /><span style="color: rgb(255, 102, 102);"><span style="font-style: italic;">4. Row Count Un-Compressed</span>:<span style="font-weight: bold;">107</span></span>
<br /><span style="color: rgb(255, 102, 102);">====================================================</span>
<br />
<br />PL/SQL procedure successfully completed.
<br />
<br />According to the above information, if we look at the compressed (836 blocks) and uncompressed blocks(1998) counts (4 and 5 above), we should at least 50% gain!
<br />
<br />3. Create a new tablespace with COMPRESS option. Unless I am missing something obvious, I couldn't get the correct syntax in both 11gR1 and R2 documentation. BTW, when I executed the same SQL on 11gR1 - I got - ORA-14464: Compression Type not specified
<br />
<br />SQL> create tablespace USER_DATA_COMPRESSED default compress for OLTP datafile '/u01/oracle/oradata/DET01/user_data_compressed.dbf' size 10M autoextend on;
<br />
<br />Tablespace created.
<br />
<br />4. Create copy of T1 table (our example table) in COMPRESSED tablespace:
<br />
<br />SQL> create table chandra.t1_compressed tablespace USER_DATA_COMPRESSED
<br />2 as Select * from chandra.t1;
<br />
<br />Table created.
<br />
<br />5. Let's see how much space does the compressed table take when compared to the uncompressed/original table:
<br /><span style="font-size:85%;">
<br /><span style="font-family:courier new;">SQL> select segment_name, bytes, a.blocks, compression, compress_for from dba_segments a,</span>
<br /><span style="font-family:courier new;"> 2 dba_tables b</span>
<br /><span style="font-family:courier new;"> 3 where a.segment_name = b.table_name and a.segment_name in ('T1','T1_COMPRESSED');</span>
<br />
<br /><span style="font-family:courier new;">SEGMENT_NAME BYTES BLOCKS COMPRESS COMPRESS_FOR</span>
<br /><span style="font-family:courier new;">------------------------------ ---------- ---------- -------- ------------</span>
<br /><span style="font-family:courier new;">T1_COMPRESSED 612368384 <span style="color: rgb(51, 204, 0); font-weight: bold;">74752</span> ENABLED OLTP</span>
<br /><span style="font-family:courier new;">T1 1476395008 <span style="color: rgb(255, 0, 0); font-weight: bold;">180224</span> DISABLED</span></span>
<br />
<br />As theDBMS_COMPRESSION procedure indicated, we did see less than 50% space utilization with the OLTP compression, so far so good. How about performance? I performed such generic tests, involving SELECTS and other DMLs and here are the results:
<br />
<br />Any activity involving Full Table Scan, the performance was <span style="font-weight: bold; color: rgb(0, 153, 0);">4 times faster</span> on compressed tables compared with uncompressed table.
<br />Inserts and Updates took close to twice longer on compressed table compared with uncompressed table and deletes about 10% more.
<br />
<br />When we only talk about other benefits of compression - they would be lesser memory utilization, redo generation etc.. comes to my mind. Of course, at the cost of higher CPU consumption.
<br />Chandra Pabbahttp://www.blogger.com/profile/15514315184841775273noreply@blogger.com2tag:blogger.com,1999:blog-8114408830189879989.post-47486846321350119392009-09-11T22:24:00.001-07:002009-09-14T05:44:40.822-07:00Oracle11gR2 ASM New Feature - Intelligent Data PlacementIntelligent Data Placement, a new feature of Oracle11gR2 ASM is aimed at optimizing data access. This is achieved by giving us the capability to place the data on those sectors of the hard drive where the I/O performance is high/efficient. These sectors are the outer sectors. Before going into the details of the ASM's feature, let's attempt to understand briefly how and why the outer sector's i/o performance is better compared to the inner sectors.
<br />
<br />The above mentioned efficiencies come from a technique used in hard drives called "Zoned Bit Recording". This method or technique used to increase capacity and data access speeds on hard disks by improving the utilization of the larger, outer tracks of the disk. Outer tracks contain more sectors per track than the one before. This makes more efficient use of the larger tracks on the outside of the disk. The data transfer rate is higher when reading outside cylinders than reading the inside ones, since outer cylinders contain more data.
<br />
<br />Coming back to the original topic - ASM now classifies reads/writes into two - COLD and HOT. As you guessed, HOT read/writes are those coming from the blocks which are classified as HOT - meaning placed in the outer sectors. COLD is the default - meaning data placed in inner sectors. Data blocks of most often accessed segments are candidates for placement in HOT region. This is done at the datafile level in an ASM disk.
<br />
<br />You could use SQL*Plus or DBConsole to make these changes.
<br />
<br />
<br />
<br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiLrxvCW4n5nCIl3UAAJhSv5DEAAeNteSfVf2oPtsU_xoPsdIgV1vXrf1qD77xZA1jpfxw0ScK55poqytmbw6YVYUcSam4FmUeNcC8ew8BfDjpeSFD3M3M-K6ri7VIlwzbotra2looA6TXb/s1600-h/hotcoldregion.png"><img style="cursor: pointer; width: 380px; height: 155px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiLrxvCW4n5nCIl3UAAJhSv5DEAAeNteSfVf2oPtsU_xoPsdIgV1vXrf1qD77xZA1jpfxw0ScK55poqytmbw6YVYUcSam4FmUeNcC8ew8BfDjpeSFD3M3M-K6ri7VIlwzbotra2looA6TXb/s320/hotcoldregion.png" alt="" id="BLOGGER_PHOTO_ID_5380462074421669010" border="0" /></a>
<br />It's just matter of picking the appropriate radio-button depending on the needs/access patterns to switch between HOT and COLD regions.
<br />
<br />We know what the feature means - but I wanted to put the feature to test to really see the benefit in regards to I/O rates/speeds using some general test cases. Here is how the I/O rates or performance was for a set of tests (comprising a mix of read/writes(deletes/inserts/updates) with the files being in the default zone (COLD). Then ran the same tests while the datafile was placed in the HOT region. Here are the test results. Note that when you move a file from one region to another, you have to initiate a re-balance to make the change effective.
<br />
<br />
<br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhnNqRPZ6RwbASVBeXqt_YF6IqRMFoQYU6AGUfMBjpRWXg4ktsdN_zPdGvaPhOT8CgoNzYmqcflJ8K2XEz9KNHa99AfOgkIrbpBr7dFcNwFTc8lxIcki4m_J-h-Lei0c2cw4BKmwdGOp3nd/s1600-h/COLD-iorates.jpg"><img style="cursor: pointer; width: 320px; height: 102px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhnNqRPZ6RwbASVBeXqt_YF6IqRMFoQYU6AGUfMBjpRWXg4ktsdN_zPdGvaPhOT8CgoNzYmqcflJ8K2XEz9KNHa99AfOgkIrbpBr7dFcNwFTc8lxIcki4m_J-h-Lei0c2cw4BKmwdGOp3nd/s320/COLD-iorates.jpg" alt="" id="BLOGGER_PHOTO_ID_5380657728316302514" border="0" /></a>
<br />
<br />With Intelligent Data Placement:
<br /><meta equiv="CONTENT-TYPE" content="text/html; charset=utf-8"><title></title><meta name="GENERATOR" content="OpenOffice.org 3.1 (Win32)"><style type="text/css"> <!-- @page { margin: 0.79in } P { margin-bottom: 0.08in } --> </style> <p><tt><span style="color: rgb(68, 68, 221);"><span style="font-size:85%;"><b>ALTER</b></span></span></tt><tt><span style="color: rgb(0, 0, 0);"> </span></tt><tt><span style="color: rgb(0, 0, 0);"><span style="font-size:85%;">DISKGROUP</span></span></tt><tt><span style="color: rgb(0, 0, 0);"> </span></tt><tt><span style="color: rgb(0, 0, 0);"><span style="font-size:85%;">ASMDISK01</span></span></tt><tt><span style="color: rgb(0, 0, 0);"> </span></tt><tt><span style="color: rgb(68, 68, 221);"><span style="font-size:85%;"><b>MODIFY</b></span></span></tt><tt><span style="color: rgb(0, 0, 0);"> </span></tt><tt><span style="color: rgb(68, 68, 221);"><span style="font-size:85%;"><b>FILE</b></span></span></tt><tt><span style="color: rgb(0, 0, 0);"> </span></tt><tt><span style="color: rgb(204, 0, 0);"><span style="font-size:85%;">'+ASMDISK01/BDBE01/DATAFILE/APP_DATA.256.697380955'</span></span></tt><tt><span style="color: rgb(0, 0, 0);"> </span></tt><tt><span style="color: rgb(68, 68, 221);"><span style="font-size:85%;"><b>ATTRIBUTES</b></span></span></tt><tt><span style="color: rgb(0, 0, 0);"> </span></tt><tt><span style="color: rgb(0, 0, 0);"><span style="font-size:85%;">(HOT)</span></span></tt> </p>
<br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgzC30wNIMD_ZkoWpRVc9KkoqEJnmI1uh_ElBIuTZUTXx1D2Y2PNBE0wD4yqpOFjQoUUI8DztDcJQxJzfSltFfHv6-4q_SYGy2JB2QqXOl5XGAjvu5R2bh5YIGHyIzwFIdzX3qJ0TizxUnu/s1600-h/HOT-iorates.jpg"><img style="cursor: pointer; width: 320px; height: 103px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgzC30wNIMD_ZkoWpRVc9KkoqEJnmI1uh_ElBIuTZUTXx1D2Y2PNBE0wD4yqpOFjQoUUI8DztDcJQxJzfSltFfHv6-4q_SYGy2JB2QqXOl5XGAjvu5R2bh5YIGHyIzwFIdzX3qJ0TizxUnu/s320/HOT-iorates.jpg" alt="" id="BLOGGER_PHOTO_ID_5380657782596292610" border="0" /></a>
<br />
<br />
<br />I executed the above mentioned test cases multiple times to make sure I see or notice consistent (and thus reliable and dependable) I/O performance. During my tests I saw that there was consistently an increase of about 10% to 15% in "Average Throughput" and about (-)3% to +5% in the "Average Response Times". Of course, we may have to perform more realistic tests with application specific I/O patterns.
<br />
<br />I think we can say that with this new feature, Oracle (with ASM) will be able to successfully exploit the Zoned Bit Recording technique of hard drives for optimizing I/O performance. Again, not sure if this really going to make a huge impact given the large cache and disk sizes.
<br />Chandra Pabbahttp://www.blogger.com/profile/15514315184841775273noreply@blogger.com1tag:blogger.com,1999:blog-8114408830189879989.post-62341138006298657552009-09-09T08:28:00.000-07:002009-09-10T01:32:56.996-07:00Oracle11g R2 - impressive and intelligent CRS installer (OUI)!I was really impressed with the enhancements made to the installer, apart from the change in the look and feel. Prior to Oracle11gR2, installer would allow us to place the redundant copies of OCR/Voting devices/files unto the same file system (of course it doesn't make any sense though). But with 11gR2, it prevents us from placing the mirrors unto the same file system. There is no IGNORE option as well...either you have to place it on different file systems/locations or use a single copy. So the installer has "best practices" built into it.<br /><br />Here is how I first attempt to use - placing all the three unto the same file system:<br /><br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEibZcIv8V9AFEX19W46DgZk-lY1Nx_sO4cU66W75JnIqZrcyLiCZsXjfUUQZ3A3Ejbtk5ki5vTp45p7IXFWta3eQqv013iUWLnDabuU61UK4Ujv5BVW_9CoLwdmCLsQsPtBK-b0R7QOvVZO/s1600-h/Error1.gif"><img style="cursor: pointer; width: 320px; height: 155px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEibZcIv8V9AFEX19W46DgZk-lY1Nx_sO4cU66W75JnIqZrcyLiCZsXjfUUQZ3A3Ejbtk5ki5vTp45p7IXFWta3eQqv013iUWLnDabuU61UK4Ujv5BVW_9CoLwdmCLsQsPtBK-b0R7QOvVZO/s320/Error1.gif" alt="" id="BLOGGER_PHOTO_ID_5379492535959677394" border="0" /></a><br />And here is the error message:<br /><br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh3hfBV6XNl-iv11-Hm46hzYpHqZd69rDjQVju52Mc97V0S1_4jtaqSIpbT5lpd2jQq9BVP1vaAmlx-gu0a9Yg3c_U2gD7ItCox8s_Rpp2iESdpAo9EPn9JULachuI11qZIUNPshxoqMZrt/s1600-h/Error.gif"><img style="cursor: pointer; width: 320px; height: 197px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh3hfBV6XNl-iv11-Hm46hzYpHqZd69rDjQVju52Mc97V0S1_4jtaqSIpbT5lpd2jQq9BVP1vaAmlx-gu0a9Yg3c_U2gD7ItCox8s_Rpp2iESdpAo9EPn9JULachuI11qZIUNPshxoqMZrt/s320/Error.gif" alt="" id="BLOGGER_PHOTO_ID_5379492680282251698" border="0" /></a><br /><br />I ended up using a single copy for now:<br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjvRICDs4oGxRT0E53Wg1i8QJNiGvN8Yp8U61lU3hOsmRSEgvUkskzUAgO3q0KgrEaG7-y4fjbdP_R_NW_ArB4w0vDics2UGAOuDHDvKd9lxAf9WzGrjZ7HqyxBoO3edBTTpIZ9zP_KJdbV/s1600-h/Error3.gif"><img style="cursor: pointer; width: 320px; height: 245px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjvRICDs4oGxRT0E53Wg1i8QJNiGvN8Yp8U61lU3hOsmRSEgvUkskzUAgO3q0KgrEaG7-y4fjbdP_R_NW_ArB4w0vDics2UGAOuDHDvKd9lxAf9WzGrjZ7HqyxBoO3edBTTpIZ9zP_KJdbV/s320/Error3.gif" alt="" id="BLOGGER_PHOTO_ID_5379492758293260050" border="0" /></a><br /><br />BTW, the installer allows us to configure SSH, test SSH, option to use ASM or shared file system for OCR/Vote etc... The best of all, it now provides a script to fix any of the fix-able requirements such as kernel parameters etc..Chandra Pabbahttp://www.blogger.com/profile/15514315184841775273noreply@blogger.com0tag:blogger.com,1999:blog-8114408830189879989.post-39463040542538147592009-09-08T18:45:00.000-07:002009-09-09T05:48:04.422-07:00ORA-03001 - while trying to rename tables owned by others - workaroundThere could be situations where you may like to rename the tables owned by others, but do not have access to the password (of the schema owner) or don't want to change the password (using "by values" etc...). You could perform almost all the activities by using <span style="font-family: courier new; font-style: italic;">"</span><span style="font-family: courier new;">ALTER SESSION SET CURRENT_SCHEMA=<schema_name><schema_name></schema_name></span><span style="font-style: italic; font-family: courier new;"><schema_name>;</schema_name></span><span style="font-family: courier new; font-style: italic;">"</span>, but rename table wouldn't work and following error message would be encountered:<br /><br /><span style="font-family: courier new;">chandra@DOE01:11g> show user</span><br /><span style="font-family: courier new;">USER is "CHANDRA"</span><br /><span style="font-family: courier new;">chandra@DOE01:11g> alter session set current_schema=CP_ADMIN;</span><br /><br /><span style="font-family: courier new;">Session altered.</span><br /><br /><span style="font-family: courier new;">chandra@DOE01:11g> rename APP_DATA to APP_DATA_BAK;</span><br /><span style="font-family: courier new;">rename APP_DATA to APP_DATA_BAK</span><br /><span style="font-family: courier new;">*</span><br /><span style="font-family: courier new;">ERROR at line 1:</span><br /><span style="color: rgb(204, 0, 0); font-family: courier new;">ORA-03001: unimplemented feature</span><br /><br /><br /><span style="font-family: courier new;">chandra@DOE01:11g></span><br /><br />To get around this, we could create a simple procedure similar to the following:<br /><br /><span style="font-size:85%;"><span style="font-family: courier new;">create or replace procedure rename_others_tab (orig_tab_name in varchar2, new_tab_name in varchar2)</span><br /><span style="font-family: courier new;">as</span><br /><span style="font-family: courier new;">lv_sql_str varchar2(100);</span><br /><span style="font-family: courier new;">lv_exists number(1);</span><br /><span style="font-family: courier new;">begin</span><br /><span style="font-family: courier new;">select count(*) into lv_exists from user_tables where table_name=orig_tab_name;</span><br /><span style="font-family: courier new;">if lv_exists = 1 then</span><br /><span style="font-family: courier new;">lv_sql_str := 'rename '||orig_tab_name||' to '||new_tab_name;</span><br /><span style="font-family: courier new;">dbms_output.put_line (lv_sql_str);</span><br /><span style="font-family: courier new;">execute immediate lv_sql_str;</span><br /><span style="font-family: courier new;">else</span><br /><span style="font-family: courier new;">dbms_output.put_line('ERROR:'||orig_tab_name||' does not exist!');</span><br /><span style="font-family: courier new;">end if;</span><br /><span style="font-family: courier new;">end;</span><br /><span style="font-family: courier new;">/</span></span><br /><br />Note that you would be creating the above procedure while your current_schema is set to the owner of the table.<br /><br />Then:<br /><br /><span style="font-size:85%;"><span style="font-family: courier new;">chandra@DOE01:11g> exec rename_others_tab('APP_DATA','APP_DATA_BAK');</span><br /><br /><span style="font-family: courier new;">PL/SQL procedure successfully completed.</span><br /><br /></span><span style="font-family: courier new;"><span style="font-size:85%;">chandra@DOE01:11g</span>></span><br /><br />The table name would be renamed....Of course, use the above procedure (work-around) with caution and when ABSOLUTELY needed. <br /><br />Thought would be of use to others....Chandra Pabbahttp://www.blogger.com/profile/15514315184841775273noreply@blogger.com6tag:blogger.com,1999:blog-8114408830189879989.post-636256334131723262009-08-07T18:21:00.000-07:002009-08-07T18:28:38.320-07:00ORA-01620 while creating a RAC standby for non-RAC primaryToday I was trying to test some thing and had to create a RAC standby for a Non-RAC (single-instance) Primary database. I know, it doesn't make sense to have a RAC standby for a single-instance primary, but that's not the point. First instance was mounted without any problems, but when attempted to mount the second standby instance got the following error message:<br /><br /><span style="font-size:85%;"><span style="font-family:courier new;">SQL> alter database mount standby database;</span><br /><span style="font-family:courier new;">alter database mount standby database</span><br /><span style="font-family:courier new;">*</span><br /><span style="font-family:courier new;">ERROR at line 1:</span><br /><span style="font-family:courier new;">ORA-01620: no public threads are available for mounting</span></span><br /><br />I couldn't find quick notes on the web to get around this issue and therefore had to find my own for this unique situation (having a RAC standby for SI-Primary). Here is what I did on Primary:<br /><br /><span style="font-size:85%;"><span style="font-family:courier new;">SQL> ALTER DATABASE ADD LOGFILE THREAD 2 group 13 size 5M;</span><br /><br /><span style="font-family:courier new;">Database altered.</span><br /><br /><span style="font-family:courier new;">SQL> ALTER DATABASE ADD LOGFILE THREAD 2 group 14 size 5M;</span><br /><br /><span style="font-family:courier new;">Database altered.</span><br /><br /><span style="font-family:courier new;">SQL> alter database enable public thread 2;</span><br /><br /><span style="font-family:courier new;">Database altered.</span></span><br /><br />Then created new copy of standby controlfile and shipped it over to standby and attempted to mount both the instances, it worked fine! Of course, had the primary been a RAC, we wouldn't have seen this in first place :-) BTW, this is on Oracle11g 11.1.0.7Chandra Pabbahttp://www.blogger.com/profile/15514315184841775273noreply@blogger.com0tag:blogger.com,1999:blog-8114408830189879989.post-10052215561513747112009-08-04T09:52:00.000-07:002009-08-04T10:48:38.718-07:00Tablespace Point-in-time Recovery (TSPITR) using RMAN<span style="font-weight: bold;">Scenario</span>: A logical corruption occurred due to an user error and have to quickly get back to the state just before the time of the error. Possible options: Database point-in-time recovery, but the downside is the entire database has to be unavailable and other transactions after the PITR would be lost. The other option would be Tablespace Point-in-time-Recovery (TSPITR) - the obvious choice here. I had known that RMAN could be used to perform TSPITR, but I realized just today how powerful, simple, <span style="font-weight: bold;">automated</span>, error-free and fast it could be to perform TSPITR using RMAN - it's really amazing. The intent of this post is just to high-light and appreciate the usefulness of RMAN to perform TSPITR.<br /><br />Assuming we have the latest backup, just executing the following statement would do everything needed to get the entire tablespace to a point-in-time in the past. Believe me, you don't have to do anything other than just this statement!<br /><br /><br /><span style="font-family:courier new;">recover tablespace APP_DATA until time</span><br /><span style="font-family:courier new;">"to_date('2009-08-04 12:15:00’,’YYYY-MM-DD HH24:MI:SS')"</span><br /><span style="font-family:courier new;">auxiliary destination ’/opt/oracle/temp’;</span><br /><br />Once the above statement is executed, RMAN does the following for us:<br /><br /><ul><li>Creates auxiliary instance (including the pfile etc..)</li><li>Mounts the auxiliary instance</li><li>Makes the candidate tablespace into OFFLINE</li><li>Restores the basic tablespace UNDO, SYTEM,SYSAUX and then the required tablespace</li><li>Applies archives (completes recovery)<br /></li><li>Opens the database</li><li>Performs an export of the objects residing in the tablespace</li><li>Shutdown aux instance</li><li>Import the objects into the target database</li><li>Remove aux instance and cleanup</li></ul>That's it....you now have all the objects back in the tablespace!.<br /><br />You can view the output of the RMAN session by downloading this <a href="http://chandra.pabba.googlepages.com/TSPITR.pdf"> file.. </a>which is quite self-explanatory and informative too!Chandra Pabbahttp://www.blogger.com/profile/15514315184841775273noreply@blogger.com1tag:blogger.com,1999:blog-8114408830189879989.post-19215210207768086172009-07-30T17:38:00.000-07:002009-07-30T18:05:37.058-07:00ORA-27090: Message 27090 not found on 10.2.0.4Lately we have been noticing ORA-27090 on our 10.2.0.4 databases. Here is the complete error message, as appeared in the alert log file. This is observed on Suse Linux, Red hat and OEL 5.3:<br /><br />===============================================================<br />Errors in file /tmp/test_dbw0_4400.trc:<br />ORA-27090: Message 27090 not found; product=RDBMS; facility=ORA<br />Additional information: 3<br />Additional information: 128<br />Additional information: 1100 <span id="{C664965A-4F57-42B4-9E5B-93AEBF226D60}" style="color: rgb(255, 0, 0);">#--This is the value for the parameter aio-max-nr</span><br />===============================================================<br />Looks like this error condition is only encountered or reported in databases using 10.2.0.4 oracle version. On research found that this has something to do with the kernel setting fs.aio-max-nr. aio-max-nr sets the systemwide maximum number of AIO requests. Apparently, when the number of requests reach this number the error is reported. I guess the question would be, how can we determine where we are at currently in relation to the max limit, right? This is answered by aio-nr, which maintains a cumulative/running total of number of aio events used.<br /><br />I was able to consistently reproduce the error condition by playing around with the settings for aio-max-nr parameter. As such, no apparent problems are noticed on the database side, but did did notice that the standby databases would fail to continue with the managed recovery process.<br /><br />Here is how it looked like before starting up an 10.2.0.4 instance:<br /><br />#/sbin/sysctl -a |grep aio<br />fs.aio-max-nr = 1100 #---I reset it to this value from default.<br />fs.aio-nr = 0<br /><br />When the instance was started, the value looked like this:<br />#/sbin/sysctl -a |grep aio<br />fs.aio-max-nr = 1100 #---I reset it to this value from default.<br />fs.aio-nr = 1060<br /><br />As soon as it hit the ceiling, the above mentioned error was reported in the alert log. When I raised the value, the error message disappeared.<br /><br />As soon as the instances are shutdown the value for fs.aio-nr gets reset.<br /><br />Hope this information would come in handy.Chandra Pabbahttp://www.blogger.com/profile/15514315184841775273noreply@blogger.com0tag:blogger.com,1999:blog-8114408830189879989.post-16471047178599819202009-07-15T08:36:00.001-07:002009-07-16T05:05:26.412-07:00How to check if CRS is set to auto-start on rebootsSometime back, some one asked me a question - "how to confirm that CRS is set to restart on reboot?"<br /><br />CRS can be configured to either to restart or not to restart on reboots. Disable restarts would be typically useful for troubleshooting CRS reboots or validating any system level changes.<br /><br />To enable restarts (default):<br /><span style="font-weight: bold;">$CRS_HOME/bin/crsctl enable crs</span><br /><br />To disable restarts of crs on reboots:<br /><span style="font-weight: bold;">$CRS_HOME/bin/crsctl disable cr</span>s<br /><br />Note that the above needs access to root.<br /><br />Whenever the above commands are run, it modifies the following file (on linux), which indicates whether or not the CRS is set to auto-restart on reboots:<br /><br />/etc/oracle/scls_scr/<span style="font-style: italic;">node_name</span><hostname><host_name><node_name>/root/crsstart<br /><br />It contains either "enable" or "disable" keywords.</node_name></host_name></hostname>Chandra Pabbahttp://www.blogger.com/profile/15514315184841775273noreply@blogger.com0tag:blogger.com,1999:blog-8114408830189879989.post-5552213393967106262009-06-20T21:28:00.000-07:002009-06-27T07:16:47.591-07:00ORA-29702 - Starting RAC instance in non-rac modeAssume the following situation: Due to some OS or network related problem you are not able to start-up the CRS (this does happen!) before it impacts the database availability and subsequently your business. Your primary goal now is to make the database available as soon as possible irrespective of its mode (RAC or Non-RAC). The reason being you would like to reduce the impact to your business. If you attempt to startup the instance without CRS, you get the following error:<br /><br /><span style="font-family:courier new;">SQL> startup;</span><br /><span style="font-family:courier new;">ORA-29702: error occurred in Cluster Group Service operation</span><br /><span style="font-family:courier new;">SQL></span><br /><br />This error message indicates that it can't start the instance since CRS is not available. Here is the quick work-around to startup the instance. The option is to remove RAC option from your binaries and start the instance. Once you have resolved the CRS related issues, you can turn-on the RAC option back (of course, your instance needs to be down while to turn-on or turn-off the options). Here is how we turn-off:<br /><br /><span style="font-family:courier new;">cd $ORACLE_HOME/rdbms/lib</span><br /><span style="font-family:courier new;">make -f ins_rdbms.mk rac_off ioracle</span><br /><br /><span style="font-family:courier new;">Set cluster_database=false</span><br /><br />You should now be able to startup the instance without CRS stack being up and running.<br /><br /><span style="font-family:courier new;">SQL> Select * from v$option where parameter like 'Real%';</span><br /><br /><span style="font-family:courier new;">PARAMETER VALUE</span><br /><span style="font-family:courier new;">---------------------------------------- ----------</span><br /><span style="color: rgb(255, 102, 102);font-family:courier new;" >Real Application Clusters FALSE</span><br /><span style="font-family:courier new;">Real Application Testing TRUE</span><br /><br />Turn-on the RAC option with:<br /><br /><span style="font-family:courier new;">make -f ins_rdbms.mk rac_on ioracle</span><br /><br />Alternatively, you can install a non-rac oracle binaries to quickly start the instance.<br /><br /><br />By the way, you turn off other options in similar way such as Partitioning (part_on/part_off), DB Vault (dv_on/dv_off) etc..Chandra Pabbahttp://www.blogger.com/profile/15514315184841775273noreply@blogger.com6tag:blogger.com,1999:blog-8114408830189879989.post-30075366380685300662009-06-16T07:22:00.000-07:002009-06-16T07:29:31.680-07:00Oracle11g - Killing session in RAC (in remote instance)Prior to Oracle11g, whenever you want to kill a session connected to a non-local (remote) instance, you had to make an explicit connection to that particular instance and then attempt to kill.<br /><br />Example:<br /><br />SQL> Select instance_number from v$instance;<br /><br />INSTANCE_NUMBER<br />---------------<br /> 1 <span style="font-weight: bold;">---> I am connected to instance 1 (Local Instance)</span><br /><br />1 row selected.<br /><br />SQL> Select inst_id, sid, serial# from gv$session where username='CHANDRA';<br /><br /> INST_ID SID SERIAL#<br />---------- ---------- ----------<br /> <span style="font-weight: bold;">2</span> 125 7526 <span style="font-weight: bold;">---> CHANDRA is connected to Inst# 2 (Non-local/remote Instance)</span><br /><br />1 row selected.<br /><br />With 11g, you could kill the session which is connected to instance 2, while you are connected to instance 1:<br /><br />SQL> Alter system kill session '125,7526,<span style="font-weight: bold;">@2</span>' immediate; <span style="font-weight: bold;">#--@2 indicates remote instance.</span><br /><br />System altered.<br /><br />SQL> Select inst_id, sid, serial# from gv$session where username='CHANDRA';<br /><br />no rows selected<br /><br />This really makes life easy - especially you are using SQL*Plus and not any other front-end tool.Chandra Pabbahttp://www.blogger.com/profile/15514315184841775273noreply@blogger.com1tag:blogger.com,1999:blog-8114408830189879989.post-69267345420632653982009-05-25T19:55:00.000-07:002009-05-25T20:02:45.263-07:00Errors while installing Oracle9i in Redhat 5.3 (libstdc++-libc6.1-1.so.2)Today, I was trying to install Oracle9i on Redhat 5.3 (2.6.18 kernel), got the following error when runInstaller was involved:<br /><br />$ Initializing Java Virtual Machine from /tmp/OraInstall2009-05-09_06-44-18AM/jre/bin/java. Please wait...<br />/tmp/OraInstall2009-05-09_06-44-18AM/jre/bin/i386/native_threads/java: error while <span id="{9484494D-1CC2-413E-BAF9-A93F3490B6CB}" style="font-weight: bold;">loading shared libraries: libstdc++-libc6.1-1.so.2</span>: cannot open shared object file: No such file or directory<br /><br />When check for libstdc++- library, I found the following to be installed by default on RH 5.3:<br /><br />[root@alps tmp]# rpm -qa |grep libstdc++-<br />libstdc++-4.1.2-44.el5<br />libstdc++-devel-4.1.2-44.el5<br />compat-libstdc++-296-2.96-138<br />compat-libstdc++-33-3.2.3-61<br />[root@alps tmp]#<br /><br />Looks like it is looking for libstdc++-lib6.1-1.<br /><br />When I did the following the initial error message disappeared:<br /><br /># cd /usr/lib<br /># ln -s libstdc++-3-libc6.2-2-2.10.0.so libstdc++-libc6.1-1.so.2<br /><br />Then I got the following error:<br /><br />$ ./runInstaller<br />$ Initializing Java Virtual Machine from /tmp/OraInstall2009-05-09_06-50-11AM/jre/bin/java. Please wait...<br />Error occurred during initialization of VM<br />Unable to load native library: /tmp/OraInstall2009-05-09_06-50-11AM/jre/lib/i386/libjava.so: symbol __libc_wait, version GLIBC_2.0 not defined in file libc.so.6 with link time reference<br /><br /><br />When applied the oracle patch: 3006854 - it fixed it:<br /><br />[root@alps 3006854]# sh rhel3_pre_install.sh<br />Applying patch...<br />Ensuring permissions are correctly set...<br />Done.<br />Patch successfully applied<br />[root@alps 3006854]#Chandra Pabbahttp://www.blogger.com/profile/15514315184841775273noreply@blogger.com1tag:blogger.com,1999:blog-8114408830189879989.post-43021955081136405592009-05-19T18:30:00.000-07:002009-05-19T19:00:47.780-07:00Adaptive/Intelligent Cursor Sharing in 11g - turning-offHere is how the cursor sharing works at a high level with bind variables, especially in conjunction with Histograms (prior to oracle 11g):<br /><ul><li>Optimizer peeks value for the bind on initial parse</li><li>Initial value of the bind determines the plan</li><li>Same plan is used/shared regardless of future bind values</li><li>One plan not always appropriate and optimal for all bind values and results in following problems or side-effects:</li></ul> - Performance problems<br /> - Instability issues<br /> - Unhappy end users<br /><br />Adaptive Cursor Sharing address this problem. it shares execution plans ONLY when bind values are "equivalent" - means if it doesn't result in performance degradation. This is the default behavior in Oracle11g.<br /><br />More information on adaptive cursor sharing can be found at: http://optimizermagic.blogspot.com/2009/04/update-on-adaptive-cursor-sharing.html - The intention of this post is not to talk about this feature - but how to turn-off this default behavior. I can't really think of a reason why anyone would like to turn this feature off, but it is possible, if at all we need it for some reason. This can be achieved by using NO_BIND_AWARE hint.<br /><br />I believe this hint is meant to be an alternative for using the hidden parameter - <em>_optim_peek_user_binds=false which is used to turn-off bind peeking. </em><br /><br />Here is the example:<br /><br />The default behavior:<br /><br />SQL> variable lv_id number;<br /><br />SQL> exec :lv_id :=100;<br /><br />PL/SQL procedure successfully completed.<br /><br />SQL> Select count(*) from sh.sales where prod_id = :lv_id;<br /><br />COUNT(*)<br />----------<br /> 0<br /><br />----if the bind values are peeked, we would see records in V$SQL_CS_STATISTICS:<br /><br />SQL> Select address, child_number, peeked, executions from v$sql_cs_statistics;<br /><br />ADDRESS CHILD_NUMBER P EXECUTIONS<br />-------- ------------ - ----------<br />34C54884 0 Y 1<br /><br /><br />Now, let's turn off bind peeking.<br />======================<br /><br />SQL> alter system flush shared_pool;<br /><br />System altered.<br /><br />SQL> exec :lv_id :=1000;<br /><br />PL/SQL procedure successfully completed.<br /><br />SQL> Select /*+ <span id="{355F78D4-AEB9-462E-8AE5-859F12E19BE7}" style="font-weight: bold;">NO_BIND_AWARE</span> */ count(*) from sh.sales where prod_id = :lv_id;<br /><br />COUNT(*)<br />----------<br /> 0<br />----With the above hint, the sql is not bind aware, therefore we don't see any records in v$SQL_CS_STATISTICS view.<br /><br />SQL> Select address, child_number, peeked, executions from v$sql_cs_statistics;<br /><br />no rows selected<br /><br />SQL><br /><br />I guess, this is a better and elegant way to turn-off bind-peeking at the SQL level instead of turning-off bind-peeking at the instance level using the hidden parameter - _optim_peek_user_binds. Again, this parameter is typically used to prevent the side/ill-effects of bind-peeking and the same is now (with 11g) obviated with the introduction of Adaptive Cursor Sharing feature in 11g.<br /><br />BTW, this hint is available in 11.1.0.7.<br /><br /><em><br /></em>Chandra Pabbahttp://www.blogger.com/profile/15514315184841775273noreply@blogger.com0tag:blogger.com,1999:blog-8114408830189879989.post-80138132803260455162009-05-18T17:56:00.000-07:002009-05-19T12:15:15.827-07:00Oracle11g CRS new commandJust found out that the functionality of the "crsctl" command has been expanded in Oracle11g. Prior to Oracle11g, we did not have the ability to check the status of the cluster on other (non-local) nodes in the cluster using "crsctl" - but oracle11g makes it now possible:<br /><br />[root@racnode01 bin]# ./crsctl check cluster<br />racnode01 ONLINE<br />racnode02 ONLINE<br />[root@racnode01 bin]#<br /><br />Of course, we could very well use "crsstat" command to determine the status of CRS on non-local nodes - but not straight-forward and simple.Chandra Pabbahttp://www.blogger.com/profile/15514315184841775273noreply@blogger.com0tag:blogger.com,1999:blog-8114408830189879989.post-80601414879783361682009-04-22T12:15:00.000-07:002009-04-22T12:16:22.973-07:00Oracle Support Bug Status Codes...When you have an SR open with Oracle needing a bug fix, most of the times the status would be indicated in codes. Here is the list of codes and what it means...thought would come in handy:<br /><br />10 - Description Phase: Development is requesting more information.<br />16 - Support bug screening: Bug is being reviewed by our Bug Diagnostics group.<br />11 - Code Bug (Response/Resolution): Bug is being worked by Development.<br />30 - Additional Information Requested: Bug is being worked by Support and/or more information was requested by Development.<br />37 - To Filer for Review/Merge Required: Bug has been fixed but the patch will be merged into the next patchset.<br />80 - Development to Q/A: Bug is being regression tested for future release.<br />81 - Q/A to Dev/Patch or Workaround Avble: Patch released via Metalink.<br />90 - Closed, Verified by Filer: Bug has been fixed and is closed.<br />91 - Closed, Could Not Reproduce: Bug is closed as not reproducible.<br />92 - Closed, Not a Bug: Bug is closed as not a bug (not reproducible or setup issue).<br />93 - Closed, Not Verified by Filer: Bug has been fixed and is closed.<br />95 - Closed, Vendor OS Problem: Bug is closed as an OS problem.<br />96 - Closed, Duplicate Bug: Bug is closed as a duplicate bug.Chandra Pabbahttp://www.blogger.com/profile/15514315184841775273noreply@blogger.com0tag:blogger.com,1999:blog-8114408830189879989.post-60761239150937671712009-04-17T04:54:00.000-07:002009-04-17T04:58:06.507-07:00ORA-38856 - while opening a cloned RAC databaseI was attempting to open a cloned RAC database and got the following error:<br /><br />SYS@NETFRD1> alter database open resetlogs;<br />alter database open resetlogs<br />*<br />ERROR at line 1:<br />ORA-38856: cannot mark instance UNNAMED_INSTANCE_2 (redo thread 2) as enabled<br /><br />The fix was to use the undocumented parameter - _no_recovery_through_resetlogs=TRUE to open the database. I guess this is one of those scenarios where Oracle suggests using this undocumented/unsupported parameter. Here is what I did to get pass the problem:<br /><br />1. Shutdown;<br />2. Startup mount (with the parameter _no_recovery_through_resetlogs=TRUE set in init.ora)<br />3. Open the database with RESETLOGS;<br /><br />SYS@NETFRD1> alter database open resetlogs;<br />alter database open resetlogs<br />*<br />ERROR at line 1:<br />ORA-38856: cannot mark instance UNNAMED_INSTANCE_2 (redo thread 2) as enabled<br /><br /><br />SYS@NETFRD1> shutdown immediate;<br />ORA-01109: database not open<br /><br /><br />Database dismounted.<br />ORACLE instance shut down.<br />SYS@NETFRD1> !vi /tmp/init$ORACLE_SID.ora<br /><br />SYS@NETFRD1> startup mount pfile=/tmp/init$ORACLE_SID.ora<br />ORACLE instance started.<br /><br />Total System Global Area 1577058304 bytes<br />Fixed Size 2084232 bytes<br />Variable Size 385876600 bytes<br />Database Buffers 1174405120 bytes<br />Redo Buffers 14692352 bytes<br />Database mounted.<br />SYS@NETFRD1> alter database open resetlogs;<br /><br />Database altered.Chandra Pabbahttp://www.blogger.com/profile/15514315184841775273noreply@blogger.com15tag:blogger.com,1999:blog-8114408830189879989.post-23886164167830376182009-02-16T08:03:00.000-08:002009-02-16T08:06:43.174-08:00ORA-39006, ORA-39065,ORA-25306,ORA-39079 while exporting using EXPDPEncountered the following error messages which attempting to do an export of a database using Data Pump utility with 10.2.0.4:<br /><br /><div> <p class="MsoNormal"><span style="font-family:Arial;font-size:85%;"><span style="font-size: 10pt; font-family: Arial;">ORA-39006: internal error<br />ORA-39065: unexpected master process exception in DISPATCH<br />ORA-39079: unable to enqueue message DG,KUPC$S_3_20090213124234,MCP, ,1,Y<br />ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86<br />ORA-06512: at "SYS.KUPC$QUE_INT", line 924<br />ORA-25306: Cannot connect to buffered queue's owner instance</span></span><o:p></o:p></p></div> <div> <p class="MsoNormal"><span style="font-family:Times New Roman;font-size:100%;"><span style="font-size: 12pt;"> <o:p></o:p></span></span></p></div> <div> <p class="MsoNormal"><span style="font-family:Arial;font-size:85%;"><span style="font-size: 10pt; font-family: Arial;">ORA-39097: Data Pump job encountered unexpected error -39079<br />ORA-39065: unexpected master process exception in KUPC$QUEUE_INT.PUT_STATUS<br />ORA-39079: unable to enqueue message DG,KUPC$S_3_20090213124234,MCP, ,1,Y<br />ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86<br />ORA-06512: at "SYS.KUPC$QUE_INT", line 924<br />ORA-25306: Cannot connect to buffered queue's<br /></span></span></p><p class="MsoNormal">This seems to be a bug but couldn't find any published information in metalink. Setting the value for the parameters aq_tm_processes to 4 from a value of 0 worked. There are several other databases with the default setting of 0, but they don't seem to have problems with expdp. Hopefully, this will have a fix in 10.2.0.5.</p><p class="MsoNormal">Hope this helps others who encounter similar errors.<br /></p></div>Chandra Pabbahttp://www.blogger.com/profile/15514315184841775273noreply@blogger.com0tag:blogger.com,1999:blog-8114408830189879989.post-40412080494711000622009-02-08T20:18:00.000-08:002009-04-15T08:41:00.773-07:00Bypassing/ignoring or skipping ORA-1555 errorsI was researching a corruption issue using various Metalink link notes and stumbled upon a note/bug which provides a way to ignore or skip ORA-1555 errors for queries. Obviously, this will result in getting inconsistent data/result-set. As of now, I am not sure as to what type situations will have a practical use of this feature. This is available in Oracle 11g 11.1.0.7. This is done using a hint - SCN_ASCENDING.<br /><br />Example:<br /><br /><span style="font-weight: bold;">In Session 1:</span><br /><br />chandra@AEDB01:11g> Select Distinct phone from acs_emp;<br /><br />PHONE<br />--------------------<br />972-500-8999<br /><br />1 row selected.<br /><br /><span style="font-weight: bold;">In Session 2: </span><br /><br />chandra@AEDB01:11g> Select /*+ scn_ascending */ * from acs_emp;<br /><br /><span style="font-style: italic;">While this session is in progress, I have updated the phone column:</span><br /><br />Back in Session 1:<br /><br />chandra@AEDB01:11g> update acs_emp set phone='888-888-8888';<br /><br />100000 rows updated.<br /><br />chandra@AEDB01:11g> commit;<br /><br />Commit complete.<br /><br /><span style="font-weight: bold;">Back to Session 2</span> where our select was in progress: You will notice that we got data which was NOT consistent with the data which existed at the point in time when the query was initiated. The SCN got changed in the middle - meaning inconsistent / questionable data!. Without the above mentioned HINT, it wouldn't have been possible to see such inconsistent result set. Hopefully, this hint is a well-intentioned one.<br /><br /> <span style="color: rgb(0, 153, 0);">650831 972-500-8999</span><br /><span style="color: rgb(0, 153, 0);"> 650831 972-500-8999</span><br /><span style="color: rgb(0, 153, 0);"> 650831 972-500-8999</span><br /><span style="color: rgb(0, 153, 0);"> 650831 972-500-8999</span><br /><span style="color: rgb(0, 153, 0);"> 650831 972-500-8999</span><br /><span style="color: rgb(0, 153, 0);"> 650831 972-500-8999</span><br /><span style="color: rgb(0, 153, 0);"> 650831 972-500-8999</span><br /><span style="color: rgb(0, 153, 0);"> 650831 972-500-8999</span><br /><span style="color: rgb(0, 153, 0);"> 650831 972-500-8999</span><br /><span style="color: rgb(0, 153, 0);"> 650831 972-500-8999</span><br /><span style="color: rgb(0, 153, 0);"> 650831 972-500-8999</span><br /><span style="font-weight: bold;"> </span><span style="color: rgb(255, 0, 0);">652088 888-888-8888</span><br /><span style="color: rgb(255, 0, 0);"> 652088 888-888-8888</span><br /><span style="color: rgb(255, 0, 0);"> 652088 888-888-8888</span><br /><span style="color: rgb(255, 0, 0);"> 652088 888-888-8888</span><br /><span style="color: rgb(255, 0, 0);"> 652088 888-888-8888</span><br /><span style="color: rgb(255, 0, 0);"> 652088 888-888-8888</span><br /><span style="color: rgb(255, 0, 0);"> 652088 888-888-8888</span><br /><span style="color: rgb(255, 0, 0);"> 652088 888-888-8888</span><br /><span style="color: rgb(255, 0, 0);"> 652088 888-888-8888</span><br /><span style="color: rgb(255, 0, 0);"> 652088 888-888-8888</span>Chandra Pabbahttp://www.blogger.com/profile/15514315184841775273noreply@blogger.com0tag:blogger.com,1999:blog-8114408830189879989.post-3350811775677143632008-09-03T11:01:00.000-07:002008-09-03T11:03:40.912-07:00java.lang.UnsatisfiedLinkError: libawt.so: libXp.so.6 during 10g Install on OEL 5.0Today, while I attempting to install Oracle10gR2 (10.2.0.1) on a new server with Oracle Enterprise Linux 5.0, got the following error when OUI was invoked. <br /><br /><span style="font-style: italic;">java.lang.UnsatisfiedLinkError: libawt.so: libXp.so.6: cannot open shared object file: No such file or directory</span><br /><br />Later, found that the package libXp-1.0.0-8.i386.rpm doesn't get installed by default with OEL 5.0. Once the package was installed, the OUI was good to go!<br /><br /><span style="font-style: italic;">[root@rhvm01 RPMs]# rpm -ivh libXp-1.0.0-8.i386.rpm</span><br /><span style="font-style: italic;">warning: libXp-1.0.0-8.i386.rpm: Header V3 DSA signature: NOKEY, key ID 37017186</span><br /><span style="font-style: italic;">Preparing... ########################################### [100%]</span><br /><span style="font-style: italic;"> 1:libXp ########################################### [100%]</span><br /><span style="font-style: italic;">[root@rhvm01 RPMs]#</span>Chandra Pabbahttp://www.blogger.com/profile/15514315184841775273noreply@blogger.com1tag:blogger.com,1999:blog-8114408830189879989.post-75548929382166371332008-05-19T07:39:00.001-07:002008-05-19T07:57:09.497-07:00Oracle Home Cloning For RACA short note on the steps for cloning Oracle Home for RAC:<br /><br />1. On all Nodes: Copy existing ORACLE_HOME binaries to a new OH location<br /> $cp -pr /opt/oracle/product/10.2.0.2 /opt/oracle/product/10.2.0.4<br /><br />2. Add entries for the new Oracle_HOME into the inventory<br /><ul><li> CD to the new OH: cd /opt/oracle/product/10.2.0.4/clone/bin</li><li>perl clone.pl ORACLE_HOME=/opt/oracle/product/10.2.0.4 ORACLE_HOME_NAME=Ora10g_10204 "CLUSTER_NODES=alps,everest" "LOCAL_NODE=alps"<br /></li><li>Execute /opt/oracle/product/10.2.0.4/root.sh script as root.</li><li>Apply the 10.2.0.4 patchset unto the newly created OH</li></ul>Chandra Pabbahttp://www.blogger.com/profile/15514315184841775273noreply@blogger.com0tag:blogger.com,1999:blog-8114408830189879989.post-44472298025975364002008-04-27T11:48:00.000-07:002008-04-27T12:31:16.769-07:00APEX Configuration - 11gAPEX is included with the default Oracle11g installation. You can check if the APEX component is included in the installation by verifying the existence of apex directory under $OH and also by checking DBA_REGISTRY:<br /><br />SQL> Select comp_name, version, status from dba_registry where comp_name<br />2 like '%Express%';<br /><br />COMP_NAME VERSION STATUS<br />------------------------------ ------------------------------ -----------<br />Oracle Expression Filter 11.1.0.6.0 VALID<br />Oracle Application Express 3.0.1.00.08 VALID<br /><br /><strong>Configuring APEX</strong><br /><br />1. Login as SYSDBA and run the script $OH/apex/apxconf<br /><br />SQL> @apxconf<br /><br />PORT<br />----------<br />8080<br /><br />Enter values below for the XDB HTTP listener port and the password for the Application Express ADMIN user.<br />Default values are in brackets [ ].<br />Press Enter to accept the default value.<br /><br /><br />Enter a password for the ADMIN user [] chandra<br />Enter a port for the XDB HTTP listener [ 8080]<br />...changing HTTP Port<br /><br />PL/SQL procedure successfully completed.<br /><br /><br />PL/SQL procedure successfully completed.<br /><br /><br />Session altered.<br /><br />...changing password for ADMIN<br /><br />PL/SQL procedure successfully completed.<br /><br /><br />Commit complete.<br /><br />SQL><br /><br />2. Set the port number:<br /><br />SQL> exec dbms_xdb.sethttpport(8080);<br /><br />PL/SQL procedure successfully completed.<br /><br />3. Unlock the ANONYMOUS ACCOUNT:<br /><br />4. Logon to APEX using http://hostname:8080/apex/apex_admin<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjdVDDKRwlMMpBGLgFYmBtnyKkvwDbyvw_E8q0ZiJVHJFINr8a5WpY_lQgjMudTIDCtqar_6QOhcby0VNgvmjWldFK5FqruxP_NW5AK05hSsuL8KkPjVAM1nDoxA9JgFce2hTeKCoamYDu-/s1600-h/APEX_Login.gif"><img id="BLOGGER_PHOTO_ID_5194007568300027250" style="CURSOR: hand" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjdVDDKRwlMMpBGLgFYmBtnyKkvwDbyvw_E8q0ZiJVHJFINr8a5WpY_lQgjMudTIDCtqar_6QOhcby0VNgvmjWldFK5FqruxP_NW5AK05hSsuL8KkPjVAM1nDoxA9JgFce2hTeKCoamYDu-/s400/APEX_Login.gif" border="0" /></a><br /><br />Use ADMIN as the username and the password which we used at step one above.<br /><br /><br /><br />5. Create a WORKSPACE<br /><br /><br /><br /><br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhtNlhqfHiBl9fkpUPgmVRYiAtVNdEL93ihhSll8od4qq_2dHvlTWpTAAYYDtT1t3Ga86yfwMkDJw69adb7hnzjAvItruFgLPq7Dvyv3nq8kl4JFpDMFV_MMAKWEVhVfwUTFqtG5M32Cu1j/s1600-h/Cr_workspace1.gif"><img id="BLOGGER_PHOTO_ID_5194009629884329346" style="CURSOR: hand" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhtNlhqfHiBl9fkpUPgmVRYiAtVNdEL93ihhSll8od4qq_2dHvlTWpTAAYYDtT1t3Ga86yfwMkDJw69adb7hnzjAvItruFgLPq7Dvyv3nq8kl4JFpDMFV_MMAKWEVhVfwUTFqtG5M32Cu1j/s320/Cr_workspace1.gif" border="0" /></a><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhNTBXzsO8Ac-JAFuFM-oSkUpmHhmIf7akXvrVZD-oFAK9YWeXeOBvLvUmhuFHCWQehh09LkoEfLX3TqcQ2sNgjhYaWpeMZbeGyC7qeQ0ODHZVfCot6dGQHmr4X4IV9k3qvMO25Rqh6Uc40/s1600-h/Cr_workspace2.gif"><img id="BLOGGER_PHOTO_ID_5194009634179296658" style="CURSOR: hand" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhNTBXzsO8Ac-JAFuFM-oSkUpmHhmIf7akXvrVZD-oFAK9YWeXeOBvLvUmhuFHCWQehh09LkoEfLX3TqcQ2sNgjhYaWpeMZbeGyC7qeQ0ODHZVfCot6dGQHmr4X4IV9k3qvMO25Rqh6Uc40/s320/Cr_workspace2.gif" border="0" /></a><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhrRKgfntQjUThlvIZmq3OutyRhIO2SQHLla_K3mlotZMVvLzavleik9gz-Y3R5amSNkkbPihjhURO1k49fkEMnchpPIfOIcFC2wUp_dR5nwk-gQFgEYNMLe4oAAafTpBMOcClGyY92iCzz/s1600-h/Cr_workspace3.gif"><img id="BLOGGER_PHOTO_ID_5194009638474263970" style="CURSOR: hand" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhrRKgfntQjUThlvIZmq3OutyRhIO2SQHLla_K3mlotZMVvLzavleik9gz-Y3R5amSNkkbPihjhURO1k49fkEMnchpPIfOIcFC2wUp_dR5nwk-gQFgEYNMLe4oAAafTpBMOcClGyY92iCzz/s320/Cr_workspace3.gif" border="0" /></a><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEieDYpfaUz2IP8RiNhg2Y8VvvQPEBeaMs5Uqpph9qnFjXdh9HQ2iYOk9XTr-w5IiD1-6MhbNP0ciuGFD5HCfrfLNgxJYAB3OaGUM0H6WLk1s662HsivoPEUhem1yxSDxWpGSXdm-hP3EqMV/s1600-h/Cr_workspace4.gif"><img id="BLOGGER_PHOTO_ID_5194009642769231282" style="CURSOR: hand" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEieDYpfaUz2IP8RiNhg2Y8VvvQPEBeaMs5Uqpph9qnFjXdh9HQ2iYOk9XTr-w5IiD1-6MhbNP0ciuGFD5HCfrfLNgxJYAB3OaGUM0H6WLk1s662HsivoPEUhem1yxSDxWpGSXdm-hP3EqMV/s320/Cr_workspace4.gif" border="0" /></a><br /><br />With this we will have a WORKSPACE to start creating our own applications.Chandra Pabbahttp://www.blogger.com/profile/15514315184841775273noreply@blogger.com0