tag:blogger.com,1999:blog-155742152024-03-07T10:41:49.042+03:00Oracle TodayStuff about the Oracle database.Yasin Baskanhttp://www.blogger.com/profile/07642821755164831104noreply@blogger.comBlogger67125tag:blogger.com,1999:blog-15574215.post-36581885827072243262012-05-24T15:30:00.000+03:002012-05-24T16:01:17.452+03:00Strange ORA-14404, or not?I was trying to drop a tablespace which I know there were no segments in it. A simple query from dba_segments returns no rows which means there are no segments allocated in this tablespace. But strangely I got this:<br />
<br />
<br />
<span style="font-family: 'Courier New', Courier, monospace;">SQL> drop tablespace psapsr3old including contents and datafiles;</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">drop tablespace psapsr3old including contents and datafiles</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">*</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">ERROR at line 1:</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">ORA-14404: partitioned table contains partitions in a different tablespace</span><br />
<br />
<b>How come I cannot drop a tablespace with no segments in it?</b><br />
<br />
Enter deferred segment creation. Things were simpler on 9i or 10g. The database I get this error on is an 11.2.0.2 database. Starting with 11.2.0.1 things may fool you when it comes to segments in the database. 11.2 brought us the feature called "deferred segment creation" which means no segments are created if you do not insert any data into the tables you created. Have a look at the note <span style="font-family: inherit;">"<span style="background-color: white; text-align: -webkit-left;">11.2 Database New Feature Deferred Segment Creation [Video] (Doc ID 887962.1)" about this feature. It is there to save disk space in case you have lots of tables without data in them. In 11.2.0.1 it was only for non-partitioned heap tables, starting with 11.2.0.2 it is also used for partitioned tables.</span></span><br />
<span style="font-family: inherit;"><span style="background-color: white; text-align: -webkit-left;"><br /></span></span><br />
<div style="text-align: -webkit-left;">
Coming back to my problem, even if there were no segments reported in dba_segments there are tables and partitions created in this tablespace without their segments created yet. If we look at the tables and partitions in that tablespace:</div>
<div style="text-align: -webkit-left;">
<br /></div>
<div style="text-align: -webkit-left;">
</div>
<span style="font-family: 'Courier New', Courier, monospace;">SQL> select segment_created,count(*) from dba_tables</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> 2 where tablespace_name='PSAPSR3OLD'</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> 3 group by segment_created;</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span><br />
<span style="font-family: 'Courier New', Courier, monospace;">SEG COUNT(*)</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">--- ----------</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">NO 13482</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span><br />
<span style="font-family: 'Courier New', Courier, monospace;">SQL> select segment_created,count(*) from dba_tab_partitions</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> 2 where tablespace_name='PSAPSR3OLD'</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> 3 group by segment_created;</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span><br />
<span style="font-family: 'Courier New', Courier, monospace;">SEGM COUNT(*)</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">---- ----------</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">NO 1237</span><br />
<br />
There are thousands of objects in there.<br />
<br />
<b>What is the solution then?</b><br />
<br />
Obviously it is to get rid of these objects by moving them to a different tablespace. The standard "alter table move" and "alter table move partition" commands do the job. Then the question becomes; will a move table operation create the segment in the new tablespace? If you are on 11.2.0.1, yes it will, defeating the whole purpose of this feature. If you are on 11.2.0.2 it will not create the segments. This is explained in the note <span style="font-family: inherit;">"Bug 8911160 - ALTER TABLE MOVE creates segment with segment creation deferred table (Doc ID 8911160.8)".</span><br />
<span style="font-family: inherit;"><br /></span><br />
<span style="font-family: inherit;">After everything is moved you can safely drop the tablespace without this error.</span><br />
<span style="font-family: inherit;"><br /></span><br />
<span style="font-family: inherit;">UPDATE: <a href="http://www.gokhanatil.com/">Gokhan Atil</a> made me aware of Randolf Geist's post about the same issue. See that post <a href="http://oracle-randolf.blogspot.com/2011/04/ora-14404-14405-and-deferred-segment.html">here</a>.</span><br />
<br />Yasin Baskanhttp://www.blogger.com/profile/07642821755164831104noreply@blogger.com0tag:blogger.com,1999:blog-15574215.post-34913817001620493142011-02-24T14:47:00.000+02:002011-02-24T14:47:06.848+02:00Direct NFS ClonedbDirect NFS Clonedb is a feature in 11.2 that you can use to clone your databases. Kevin Closson explains what it is in <a href="http://kevinclosson.wordpress.com/2010/12/23/oracle-database-11g-direct-nfs-clonedb-feature-part-i/">this post</a>. In his demo videos he is using a perl script to automate the process of generating the necessary scripts. That script is not publicly available as of today but the MOS note 1210656.1 explains how to do the clone manually without the perl script.<br />
<br />
Tim Hall also has a step by step guide on how to do the cloning in <a href="http://oracle-base.com/articles/11g/Clonedb_11gR2.php">this post</a>. He also uses the perl script mentioned above.<br />
<br />
We have been testing backups and clones on Exadata connected to a 7410 ZFS Storage Appliance, I wanted to share our test on Direct NFS Clonedb. This test is on a quarter rack x2-2 connected to a 7410 storage via Infiniband. A RAC database will be cloned as a single instance database and the clone database will opened in one db node.<br />
<br />
<span class="Apple-style-span" style="font-size: large;"><b>Enable Direct NFS on Exadata</b></span><br />
<span class="Apple-style-span" style="font-size: large;"><b><br />
</b></span><br />
For security, as of today default Exadata installation disables some services needed by NFS. To use NFS on Exadata db nodes we enabled those services first.<br />
<br />
<br />
<div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">service portmap start</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">service nfslock start</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">chkconfig --level 345 portmap on</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">chkconfig --level 345 nfslock on</span></div><div class="code"><br />
</div><div class="code">Recent Exadata installations come with Direct NFS (dNFS) enabled, you can check if you have it enabled by looking at the database alert log. When the database is started you can see this line in the alert log if you have dNFS enabled.</div><div class="code"><br />
</div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">Oracle instance running with ODM: Oracle Direct NFS ODM Library Version 3.0</span></div><div class="code"><br />
</div><div class="code">If it is not enabled you can use this command after stopping the database to enable it.</div><div class="code"><br />
</div><div class="code"></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">make -f $ORACLE_HOME/rdbms/lib/ins_rdbms.mk dnfs_on</span></div><div class="code"><br />
</div><div class="code"><span class="Apple-style-span" style="font-size: large;"><b>Mount the NFS share</b></span></div><div class="code"><span class="Apple-style-span" style="font-size: large;"><b><br />
</b></span></div><div class="code">I am assuming 7410 is configured and NFS sharing is up on it at this point. To mount the NFS share you can use a mount command like this on Exadata db nodes.</div><div class="code"><br />
</div><div class="code"></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">mount 192.168.27.105:/export/backup1 /backup -o rw,bg,hard,intr,rsize=131072,wsize=131072,intr,nodev,nosuid,actimeo=0</span></div><div class="code"><br />
</div><div class="code"><span class="Apple-style-span" style="font-size: large;"><b>Back up the source database</b></span></div><div class="code"><span class="Apple-style-span" style="font-size: large;"><b><br />
</b></span></div><div class="code">You can use OS copies or RMAN image copies to back up the database for use in the cloning process. Here are the commands we used, do not forget to create the target directory before.</div><div class="code"><br />
</div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">sql 'alter database begin backup';</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">backup as copy database format '/backup/clone_backup/%U';</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">sql 'alter database end backup';</span></div><div class="code"><br />
</div><div class="code"><span class="Apple-style-span" style="font-size: large;"><b>Prepare the clone db</b></span></div><div class="code"><span class="Apple-style-span" style="font-size: large;"><b><br />
</b></span></div><div class="code">To start the clone database we need an init.ora file and a create controlfile script. You can back up the source database's control file to a text file and use it. In the source database run this to get the script, this will produce a script under the udump directory (/u01/app/oracle/diag/rdbms/dbm/dbm1/trace in Exadata).</div><div class="code"><br />
</div><div class="code"></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">SQL> alter database backup controlfile to trace;</span></div><div class="code"><br />
</div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">Database altered.</span></div><div class="code"><br />
</div><div class="code">After editing the script this is the one we can use for the clone database.</div><div class="code"><br />
</div><div class="code"></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">CREATE CONTROLFILE REUSE SET DATABASE "clone" RESETLOGS NOARCHIVELOG</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> MAXLOGFILES 1024</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> MAXLOGMEMBERS 5</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> MAXDATAFILES 32767</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> MAXINSTANCES 32</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> MAXLOGHISTORY 33012</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">LOGFILE</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> GROUP 1 (</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> '/u01/app/oradata/clone/redo01.log'</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> ) SIZE 4096M BLOCKSIZE 512,</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> GROUP 2 (</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> '/u01/app/oradata/clone/redo02.log'</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> ) SIZE 4096M BLOCKSIZE 512,</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> GROUP 3 (</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> '/u01/app/oradata/clone/redo03.log'</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> ) SIZE 4096M BLOCKSIZE 512</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">DATAFILE</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">'/backup/clone_backup/data_D-DBM_I-1222199824_TS-UNDOTBS1_FNO-3_bnm5ajrp',</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">'/backup/clone_backup/data_D-DBM_I-1222199824_TS-SYSTEM_FNO-1_blm5ajro',</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">'/backup/clone_backup/data_D-DBM_I-1222199824_TS-SYSAUX_FNO-2_bmm5ajro',</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">'/backup/clone_backup/data_D-DBM_I-1222199824_TS-UNDOTBS2_FNO-4_bom5ajrp',</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">'/backup/clone_backup/data_D-DBM_I-1222199824_TS-GNS_DATA01_FNO-7_bqm5ajrp',</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">'/backup/clone_backup/data_D-DBM_I-1222199824_TS-USERS_FNO-5_bpm5ajrp',</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">'/backup/clone_backup/data_D-DBM_I-1222199824_TS-USERS_FNO-9_bsm5ajrp',</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">'/backup/clone_backup/data_D-DBM_I-1222199824_TS-USERS_FNO-10_btm5ajrp',</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">'/backup/clone_backup/data_D-DBM_I-1222199824_TS-USERS_FNO-8_brm5ajrp'</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">CHARACTER SET AL32UTF8</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">;</span></div><div class="code"><br />
</div><div class="code">/u01/app/oradata/clone is a directory on the local disks, you can also use NFS for redo logs if you want to. The DATAFILE section lists the image copies we have just produced using RMAN. You can get this list using this sql, be careful about the completion time because you may have previous image copies in the same directory.</div><div class="code"><br />
</div><div class="code"></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">select name,completion_time from V$BACKUP_COPY_DETAILS;</span></div><div class="code"><br />
</div><div class="code">Now we need an init.ora file, we can just copy the source database's file and edit it.</div><div class="code"><br />
</div><div class="code"></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">SQL> create pfile='/backup/clone.ora' from spfile;</span></div><div class="code"><br />
</div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">File created.</span></div><div class="code"><br />
</div><div class="code">Since the source database is a RAC database you need to remove parameters related to RAC (like cluster_database, etc...). You also need to change the paths to reflect the new clone database, like in the parameter control_files. Here is the control_files parameter in this test.</div><div class="code"><br />
</div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">*.control_files='/u01/app/oradata/clone/control.ctl'</span></div><div class="code"><br />
</div><div class="code">I also use a local directory, not NFS, for the control file.</div><div class="code"><br />
</div><div class="code">There is one parameter you need to add when cloning a RAC database to a single instance database.</div><div class="code"><br />
</div><div class="code"><span style="font-size: 11pt; line-height: 115%;"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">_no_recovery_through_resetlogs=TRUE</span></span></div><div class="code"><span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt; line-height: 115%; mso-ansi-language: EN-US; mso-bidi-font-family: "Times New Roman"; mso-bidi-language: AR-SA; mso-fareast-font-family: Calibri; mso-fareast-language: EN-US;"><br />
</span></div><div class="code"><span style="font-size: 11pt; line-height: 115%;"><span class="Apple-style-span" style="font-family: inherit;">If you do not set this parameter you will get an error when you try to open the clone database with resetlogs. MOS note 334899.1 explains why we need to set this. If you do not set this this is the error you will get when opening the database.</span></span></div><div class="code"><span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt; line-height: 115%; mso-ansi-language: EN-US; mso-bidi-font-family: "Times New Roman"; mso-bidi-language: AR-SA; mso-fareast-font-family: Calibri; mso-fareast-language: EN-US;"><br />
</span></div><div class="code"><span style="line-height: 115%;"></span></div><div class="code" style="font-size: 11pt;"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">RMAN> sql 'alter database open resetlogs';</span></div><div class="code" style="font-size: 11pt;"><br />
</div><div class="code" style="font-size: 11pt;"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">sql statement: alter database open resetlogs</span></div><div class="code" style="font-size: 11pt;"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">RMAN-00571: ===========================================================</span></div><div class="code" style="font-size: 11pt;"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============</span></div><div class="code" style="font-size: 11pt;"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">RMAN-00571: ===========================================================</span></div><div class="code" style="font-size: 11pt;"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">RMAN-03009: failure of sql command on default channel at 02/22/2011 16:13:07</span></div><div class="code" style="font-size: 11pt;"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">RMAN-11003: failure during parse/execution of SQL statement: alter database open resetlogs</span></div><div class="code" style="font-size: 11pt;"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">ORA-38856: cannot mark instance UNNAMED_INSTANCE_2 (redo thread 2) as enabled</span></div><div class="code" style="font-family: Calibri, sans-serif; font-size: 11pt;"><br />
</div><div class="code" style="font-size: 11pt;"><span class="Apple-style-span" style="font-family: inherit;">Now we are ready to create the clone database.</span></div><div class="code" style="font-family: Calibri, sans-serif; font-size: 11pt;"><br />
</div><div class="code" style="font-family: Calibri, sans-serif;"><span class="Apple-style-span" style="font-size: large;"><b>Create the clone db</b></span></div><div class="code" style="font-family: Calibri, sans-serif;"><span class="Apple-style-span" style="font-size: large;"><b><br />
</b></span></div><div class="code"><span class="Apple-style-span" style="font-family: inherit;">After preparing the init.ora file and the create controlfile script we can create the database.</span></div><div class="code" style="font-family: Calibri, sans-serif;"><br />
</div><div class="code"></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">export ORACLE_SID=clone</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">SQL> startup nomount pfile='/backup/clone.ora';</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">ORACLE instance started.</span></div><div class="code"><br />
</div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">Total System Global Area 3991842816 bytes</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">Fixed Size 2232648 bytes</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">Variable Size 754978488 bytes</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">Database Buffers 3087007744 bytes</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">Redo Buffers 147623936 bytes</span></div><div class="code"><br />
</div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">SQL> @cr_control</span></div><div class="code"><br />
</div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">Control file created.</span></div><div class="code" style="font-family: Calibri, sans-serif;"><br />
</div><div class="code"><span class="Apple-style-span" style="font-family: inherit;">Now we need to rename the datafiles and point them to a NFS location we want. dbms_dnfs is the package needed for this.</span></div><div class="code" style="font-family: Calibri, sans-serif;"><br />
</div><div class="code"></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">begin</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">dbms_dnfs.clonedb_renamefile('/backup/clone_backup/data_D-DBM_I-1222199824_TS-UNDOTBS1_FNO-3_bnm5ajrp','/backup/oradata/undotbs1.263.740679581');</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">dbms_dnfs.clonedb_renamefile('/backup/clone_backup/data_D-DBM_I-1222199824_TS-SYSTEM_FNO-1_blm5ajro','/backup/oradata/system.261.740679559');</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">dbms_dnfs.clonedb_renamefile('/backup/clone_backup/data_D-DBM_I-1222199824_TS-SYSAUX_FNO-2_bmm5ajro','/backup/oradata/sysaux.262.740679571');</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">dbms_dnfs.clonedb_renamefile('/backup/clone_backup/data_D-DBM_I-1222199824_TS-UNDOTBS2_FNO-4_bom5ajrp','/backup/oradata/undotbs2.265.740679601');</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">dbms_dnfs.clonedb_renamefile('/backup/clone_backup/data_D-DBM_I-1222199824_TS-GNS_DATA01_FNO-7_bqm5ajrp','/backup/oradata/gns_data01.264.741356977');</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">dbms_dnfs.clonedb_renamefile('/backup/clone_backup/data_D-DBM_I-1222199824_TS-USERS_FNO-5_bpm5ajrp','/backup/oradata/users.266.740679611');</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">dbms_dnfs.clonedb_renamefile('/backup/clone_backup/data_D-DBM_I-1222199824_TS-USERS_FNO-9_bsm5ajrp','/backup/oradata/users.274.741357097');</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">dbms_dnfs.clonedb_renamefile('/backup/clone_backup/data_D-DBM_I-1222199824_TS-USERS_FNO-10_btm5ajrp','/backup/oradata/users.275.741357121');</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">dbms_dnfs.clonedb_renamefile('/backup/clone_backup/data_D-DBM_I-1222199824_TS-USERS_FNO-8_brm5ajrp','/backup/oradata/users.273.741357075');</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">end;</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">/</span></div><div class="code" style="font-family: Calibri, sans-serif;"><br />
</div><br />
<br />
<br />
<div class="code">The first parameter to dbms_dnfs is the backup image copy name we set in the controlfile script, the second parameter is the target filename which should reside in NFS. You can create this script using the following sql on the source database.</div><div class="code"><br />
</div><div class="code"></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">select 'dbms_dnfs.clonedb_renamefile('''||b.name||''',''/backup/oradata/'||</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> substr(d.file_name,instr(d.file_name,'/',-1)+1)||''');' </span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">from v$backup_copy_details b,dba_data_files d</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">where b.file#=d.file_id</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">and b.completion_time>sysdate-1/24;</span></div><div class="code"><br />
</div><div class="code">If you have multiple image copies be careful about the completion_time predicate. In this example I am looking at the image copies of the last hour.</div><div class="code"><br />
</div><div class="code">If the target location in the dbms_dnfs call is not on NFS here is what you will get:</div><div class="code"><br />
</div><div class="code"></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">SQL> exec dbms_dnfs.clonedb_renamefile('/backup/clone_backup/data_D-DBM_I-1222199824_TS-USERS_FNO-10_b1m59uem','/u01/app/oradata/clone/users.275.741357121');</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">BEGIN dbms_dnfs.clonedb_renamefile('/backup/clone_backup/data_D-DBM_I-1222199824_TS-USERS_FNO-10_b1m59uem','/u01/app/oradata/clone/users.275.741357121'); END;</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><br />
</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">*</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">ERROR at line 1:</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">ORA-01511: error in renaming log/data files</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">ORA-17513: dNFS package call failed</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">ORA-06512: at "SYS.X$DBMS_DNFS", line 10</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">ORA-06512: at line 1</span></div><div class="code"><br />
</div><div class="code">I got this error in my first try and searched in MOS and Google for some time with no results. Then I realized that my target locations was not on NFS but was on local disk.</div><div class="code"><br />
</div><div class="code">As we renamed the datafiles and they are pointing at the NFS location, now we can open the clone database.</div><div class="code"><br />
</div><div class="code"></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">SQL> alter database open resetlogs;</span></div><div class="code"><br />
</div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">Database altered.</span></div><div class="code"><br />
</div><div class="code">At this point we have a clone database ready to use.</div><div class="code"><br />
</div><div class="code">The target directory we used shows our files.</div><div class="code"><br />
</div><div class="code"></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">[oracle@dm01db01 oradata]$ ls -l</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">total 98523</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">-rw-r-----+ 1 oracle dba 10737426432 Feb 22 16:44 gns_data01.264.741356977</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">-rw-r-----+ 1 oracle dba 17179877376 Feb 22 16:44 sysaux.262.740679571</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">-rw-r-----+ 1 oracle dba 17179877376 Feb 22 16:44 system.261.740679559</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">-rw-r-----+ 1 oracle dba 17179877376 Feb 22 16:44 undotbs1.263.740679581</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">-rw-r-----+ 1 oracle dba 17179877376 Feb 22 16:44 undotbs2.265.740679601</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">-rw-r-----+ 1 oracle dba 10737426432 Feb 22 16:44 users.266.740679611</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">-rw-r-----+ 1 oracle dba 2147491840 Feb 22 16:44 users.273.741357075</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">-rw-r-----+ 1 oracle dba 2147491840 Feb 22 16:44 users.274.741357097</span></div><span style="font-size: 11pt; line-height: 115%;"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">-rw-r-----+ 1 oracle dba 2147491840 Feb 22 16:44 users.275.741357121</span></span><br />
<div class="code"><span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt; line-height: 115%; mso-ansi-language: EN-US; mso-bidi-font-family: "Times New Roman"; mso-bidi-language: AR-SA; mso-fareast-font-family: Calibri; mso-fareast-language: EN-US;"><br />
</span></div><div class="code">Even though ls shows their size equal to the source database file sizes, du shows us the truth.</div><div class="code"><br />
</div><div class="code"></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">[oracle@dm01db01 oradata]$ du -hs *</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">129K gns_data01.264.741356977</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">74M sysaux.262.740679571</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">2.8M system.261.740679559</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">15M undotbs1.263.740679581</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">5.4M undotbs2.265.740679601</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">129K users.266.740679611</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">129K users.273.741357075</span></div><div class="code"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">129K users.274.741357097</span></div><span style="font-size: 11pt; line-height: 115%;"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">129K users.275.741357121</span></span><br />
<div class="code"><span style="font-size: 11pt; line-height: 115%;"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><br />
</span></span></div><div class="code">The datafile sizes are minimal since we did not do any write activity on the clone database yet. These sizes will get bigger after some activity.</div>Yasin Baskanhttp://www.blogger.com/profile/07642821755164831104noreply@blogger.com0tag:blogger.com,1999:blog-15574215.post-27184689411061661532011-01-04T11:12:00.000+02:002011-01-04T11:12:01.055+02:00Database smart flash cache wait eventsWhen you enable the database smart flash cache and start using it you will see new wait events related to that. These events help to find out if the problem is about the flash cache or not.<br />
<br />
The ones I faced till now are "db flash cache single block physical read", "db flash cache multiblock physical read" and "write complete waits: flash cache". These are from a 11.2.0.1 database using the<a href="http://www.oracle.com/us/products/servers-storage/storage/disk-storage/043967.html"> F5100 flash array</a> as the database smart flash cache.<br />
<br />
<b>db flash cache single block physical read</b><br />
<br />
"db flash cache single block physical read" is the flash cache equivalent of "db file sequential read". Read waits from the flash cache are not accounted for in the "db file sequential read" event and have their own wait event. The following is from an AWR report of 30 mins from a database using the database smart flash cache.<br />
<br />
<br />
<div style="font-family: Arial, Helvetica, Geneva, sans-serif; font-size: 13px; font-weight: bold;">Top 5 Timed Foreground Events</div><div style="font-family: Arial, Helvetica, Geneva, sans-serif; font-size: 13px; font-weight: bold;"></div><ul style="font-family: Arial, Helvetica, Geneva, sans-serif; font-size: 13px; font-weight: bold;"></ul><table border="1" style="font-family: Arial, Helvetica, Geneva, sans-serif; font-size: 13px; font-weight: bold;"><tbody>
<tr><th class="awrbg" style="background-attachment: initial; background-clip: initial; background-color: #0066cc; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: white; font: normal normal bold 8pt/normal Arial, Helvetica, Geneva, sans-serif; padding-bottom: 2px; padding-left: 4px; padding-right: 4px;">Event</th><th class="awrbg" style="background-attachment: initial; background-clip: initial; background-color: #0066cc; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: white; font: normal normal bold 8pt/normal Arial, Helvetica, Geneva, sans-serif; padding-bottom: 2px; padding-left: 4px; padding-right: 4px;">Waits</th><th class="awrbg" style="background-attachment: initial; background-clip: initial; background-color: #0066cc; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: white; font: normal normal bold 8pt/normal Arial, Helvetica, Geneva, sans-serif; padding-bottom: 2px; padding-left: 4px; padding-right: 4px;">Time(s)</th><th class="awrbg" style="background-attachment: initial; background-clip: initial; background-color: #0066cc; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: white; font: normal normal bold 8pt/normal Arial, Helvetica, Geneva, sans-serif; padding-bottom: 2px; padding-left: 4px; padding-right: 4px;">Avg wait (ms)</th><th class="awrbg" style="background-attachment: initial; background-clip: initial; background-color: #0066cc; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: white; font: normal normal bold 8pt/normal Arial, Helvetica, Geneva, sans-serif; padding-bottom: 2px; padding-left: 4px; padding-right: 4px;">% DB time</th><th class="awrbg" style="background-attachment: initial; background-clip: initial; background-color: #0066cc; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: white; font: normal normal bold 8pt/normal Arial, Helvetica, Geneva, sans-serif; padding-bottom: 2px; padding-left: 4px; padding-right: 4px;">Wait Class</th></tr>
<tr><td class="awrc" style="background-attachment: initial; background-clip: initial; background-color: #ffffcc; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: black; font: normal normal normal 8pt/normal Arial, Helvetica, Geneva, sans-serif; vertical-align: top;">db file sequential read</td><td align="right" class="awrc" style="background-attachment: initial; background-clip: initial; background-color: #ffffcc; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: black; font: normal normal normal 8pt/normal Arial, Helvetica, Geneva, sans-serif; vertical-align: top;">649,576</td><td align="right" class="awrc" style="background-attachment: initial; background-clip: initial; background-color: #ffffcc; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: black; font: normal normal normal 8pt/normal Arial, Helvetica, Geneva, sans-serif; vertical-align: top;">15,469</td><td align="right" class="awrc" style="background-attachment: initial; background-clip: initial; background-color: #ffffcc; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: black; font: normal normal normal 8pt/normal Arial, Helvetica, Geneva, sans-serif; vertical-align: top;">24</td><td align="right" class="awrc" style="background-attachment: initial; background-clip: initial; background-color: #ffffcc; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: black; font: normal normal normal 8pt/normal Arial, Helvetica, Geneva, sans-serif; vertical-align: top;">47.33</td><td class="awrc" style="background-attachment: initial; background-clip: initial; background-color: #ffffcc; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: black; font: normal normal normal 8pt/normal Arial, Helvetica, Geneva, sans-serif; vertical-align: top;">User I/O</td></tr>
<tr><td class="awrnc" style="background-attachment: initial; background-clip: initial; background-color: white; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: black; font: normal normal normal 8pt/normal Arial, Helvetica, Geneva, sans-serif; vertical-align: top;">DB CPU</td><td align="right" class="awrnc" style="background-attachment: initial; background-clip: initial; background-color: white; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: black; font: normal normal normal 8pt/normal Arial, Helvetica, Geneva, sans-serif; vertical-align: top;"> </td><td align="right" class="awrnc" style="background-attachment: initial; background-clip: initial; background-color: white; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: black; font: normal normal normal 8pt/normal Arial, Helvetica, Geneva, sans-serif; vertical-align: top;">10,451</td><td align="right" class="awrnc" style="background-attachment: initial; background-clip: initial; background-color: white; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: black; font: normal normal normal 8pt/normal Arial, Helvetica, Geneva, sans-serif; vertical-align: top;"> </td><td align="right" class="awrnc" style="background-attachment: initial; background-clip: initial; background-color: white; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: black; font: normal normal normal 8pt/normal Arial, Helvetica, Geneva, sans-serif; vertical-align: top;">31.98</td><td class="awrnc" style="background-attachment: initial; background-clip: initial; background-color: white; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: black; font: normal normal normal 8pt/normal Arial, Helvetica, Geneva, sans-serif; vertical-align: top;"> </td></tr>
<tr><td class="awrc" style="background-attachment: initial; background-clip: initial; background-color: #ffffcc; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: black; font: normal normal normal 8pt/normal Arial, Helvetica, Geneva, sans-serif; vertical-align: top;">db flash cache single block physical read</td><td align="right" class="awrc" style="background-attachment: initial; background-clip: initial; background-color: #ffffcc; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: black; font: normal normal normal 8pt/normal Arial, Helvetica, Geneva, sans-serif; vertical-align: top;">11,217,303</td><td align="right" class="awrc" style="background-attachment: initial; background-clip: initial; background-color: #ffffcc; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: black; font: normal normal normal 8pt/normal Arial, Helvetica, Geneva, sans-serif; vertical-align: top;">5,044</td><td align="right" class="awrc" style="background-attachment: initial; background-clip: initial; background-color: #ffffcc; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: black; font: normal normal normal 8pt/normal Arial, Helvetica, Geneva, sans-serif; vertical-align: top;">0</td><td align="right" class="awrc" style="background-attachment: initial; background-clip: initial; background-color: #ffffcc; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: black; font: normal normal normal 8pt/normal Arial, Helvetica, Geneva, sans-serif; vertical-align: top;">15.43</td><td class="awrc" style="background-attachment: initial; background-clip: initial; background-color: #ffffcc; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: black; font: normal normal normal 8pt/normal Arial, Helvetica, Geneva, sans-serif; vertical-align: top;">User I/O</td></tr>
<tr><td class="awrnc" style="background-attachment: initial; background-clip: initial; background-color: white; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: black; font: normal normal normal 8pt/normal Arial, Helvetica, Geneva, sans-serif; vertical-align: top;">buffer busy waits</td><td align="right" class="awrnc" style="background-attachment: initial; background-clip: initial; background-color: white; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: black; font: normal normal normal 8pt/normal Arial, Helvetica, Geneva, sans-serif; vertical-align: top;">71,323</td><td align="right" class="awrnc" style="background-attachment: initial; background-clip: initial; background-color: white; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: black; font: normal normal normal 8pt/normal Arial, Helvetica, Geneva, sans-serif; vertical-align: top;">1,845</td><td align="right" class="awrnc" style="background-attachment: initial; background-clip: initial; background-color: white; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: black; font: normal normal normal 8pt/normal Arial, Helvetica, Geneva, sans-serif; vertical-align: top;">26</td><td align="right" class="awrnc" style="background-attachment: initial; background-clip: initial; background-color: white; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: black; font: normal normal normal 8pt/normal Arial, Helvetica, Geneva, sans-serif; vertical-align: top;">5.65</td><td class="awrnc" style="background-attachment: initial; background-clip: initial; background-color: white; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: black; font: normal normal normal 8pt/normal Arial, Helvetica, Geneva, sans-serif; vertical-align: top;">Concurrency</td></tr>
<tr><td class="awrc" style="background-attachment: initial; background-clip: initial; background-color: #ffffcc; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: black; font: normal normal normal 8pt/normal Arial, Helvetica, Geneva, sans-serif; vertical-align: top;">log file switch (checkpoint incomplete)</td><td align="right" class="awrc" style="background-attachment: initial; background-clip: initial; background-color: #ffffcc; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: black; font: normal normal normal 8pt/normal Arial, Helvetica, Geneva, sans-serif; vertical-align: top;">2,305</td><td align="right" class="awrc" style="background-attachment: initial; background-clip: initial; background-color: #ffffcc; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: black; font: normal normal normal 8pt/normal Arial, Helvetica, Geneva, sans-serif; vertical-align: top;">251</td><td align="right" class="awrc" style="background-attachment: initial; background-clip: initial; background-color: #ffffcc; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: black; font: normal normal normal 8pt/normal Arial, Helvetica, Geneva, sans-serif; vertical-align: top;">109</td><td align="right" class="awrc" style="background-attachment: initial; background-clip: initial; background-color: #ffffcc; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: black; font: normal normal normal 8pt/normal Arial, Helvetica, Geneva, sans-serif; vertical-align: top;">0.77</td><td class="awrc" style="background-attachment: initial; background-clip: initial; background-color: #ffffcc; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; color: black; font: normal normal normal 8pt/normal Arial, Helvetica, Geneva, sans-serif; vertical-align: top;">Configuration</td></tr>
</tbody></table><br />
<br />
There are over 11 million "db flash cache single block physical read" waits which took about 0.44ms on average (AWR reports it as 0ms). "db file sequential read" waits are over 600,000. This means we had a high flash cache hit ratio, most of the reads were coming from the flash cache, not the disks.<br />
<br />
This is the wait event histogram from the same AWR report.<br />
<br />
<br />
<table border="1" cellpadding="0" class="MsoNormalTable" style="mso-cellspacing: 1.5pt; mso-yfti-tbllook: 1184;"><tbody>
<tr style="mso-yfti-firstrow: yes; mso-yfti-irow: 0;"> <td style="padding: .75pt .75pt .75pt .75pt;"> <div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><br />
</div></td> <td style="padding: .75pt .75pt .75pt .75pt;"> <div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><br />
</div></td> <td colspan="8" style="background: #0066CC; padding: .75pt 3.0pt 1.5pt 3.0pt;"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><b><span style="color: white; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">% of Waits<o:p></o:p></span></b></div></td> </tr>
<tr style="mso-yfti-irow: 1;"> <td style="background: #0066CC; padding: .75pt 3.0pt 1.5pt 3.0pt;"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><b><span style="color: white; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">Event<o:p></o:p></span></b></div></td> <td style="background: #0066CC; padding: .75pt 3.0pt 1.5pt 3.0pt;"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><b><span style="color: white; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">Total Waits<o:p></o:p></span></b></div></td> <td style="background: #0066CC; padding: .75pt 3.0pt 1.5pt 3.0pt;"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><b><span style="color: white; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";"><1ms<o:p></o:p></span></b></div></td> <td style="background: #0066CC; padding: .75pt 3.0pt 1.5pt 3.0pt;"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><b><span style="color: white; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";"><2ms<o:p></o:p></span></b></div></td> <td style="background: #0066CC; padding: .75pt 3.0pt 1.5pt 3.0pt;"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><b><span style="color: white; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";"><4ms<o:p></o:p></span></b></div></td> <td style="background: #0066CC; padding: .75pt 3.0pt 1.5pt 3.0pt;"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><b><span style="color: white; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";"><8ms<o:p></o:p></span></b></div></td> <td style="background: #0066CC; padding: .75pt 3.0pt 1.5pt 3.0pt;"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><b><span style="color: white; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";"><16ms<o:p></o:p></span></b></div></td> <td style="background: #0066CC; padding: .75pt 3.0pt 1.5pt 3.0pt;"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><b><span style="color: white; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";"><32ms<o:p></o:p></span></b></div></td> <td style="background: #0066CC; padding: .75pt 3.0pt 1.5pt 3.0pt;"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><b><span style="color: white; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";"><=1s<o:p></o:p></span></b></div></td> <td style="background: #0066CC; padding: .75pt 3.0pt 1.5pt 3.0pt;"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><b><span style="color: white; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">>1s<o:p></o:p></span></b></div></td> </tr>
<tr style="mso-yfti-irow: 2; mso-yfti-lastrow: yes;"> <td style="background: white; padding: .75pt .75pt .75pt .75pt;" valign="top"> <div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: black; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">db flash cache single block physical read<o:p></o:p></span></div></td> <td style="background: white; padding: .75pt .75pt .75pt .75pt;" valign="top"> <div align="right" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: right;"><span style="color: black; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">11.2M<o:p></o:p></span></div></td> <td style="background: white; padding: .75pt .75pt .75pt .75pt;" valign="top"> <div align="right" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: right;"><span style="color: black; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">99.0<o:p></o:p></span></div></td> <td style="background: white; padding: .75pt .75pt .75pt .75pt;" valign="top"> <div align="right" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: right;"><span style="color: black; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">.9<o:p></o:p></span></div></td> <td style="background: white; padding: .75pt .75pt .75pt .75pt;" valign="top"> <div align="right" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: right;"><span style="color: black; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">.1<o:p></o:p></span></div></td> <td style="background: white; padding: .75pt .75pt .75pt .75pt;" valign="top"> <div align="right" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: right;"><span style="color: black; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">.0<o:p></o:p></span></div></td> <td style="background: white; padding: .75pt .75pt .75pt .75pt;" valign="top"> <div align="right" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: right;"><span style="color: black; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">.0<o:p></o:p></span></div></td> <td style="background: white; padding: .75pt .75pt .75pt .75pt;" valign="top"> <div align="right" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: right;"><span style="color: black; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">.0<o:p></o:p></span></div></td> <td style="background: white; padding: .75pt .75pt .75pt .75pt;" valign="top"> <div align="right" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: right;"><span style="color: black; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">.0<o:p></o:p></span></div></td> <td style="border: none; padding: .75pt .75pt .75pt .75pt;"></td> </tr>
</tbody></table><br />
<br />
99% of all flash cache single block reads were under 1ms, none of them are over 4ms.<br />
<br />
<b>db flash cache multiblock physical read</b><br />
<b><br />
</b><br />
"db flash cache multiblock physical read" is the flash cache equivalent of "db file scattered read". It is the event we see when we are reading multiple blocks from the flash cache. The AWR report I am using in this post does not contain many multiblock operations but here is the event from the foreground wait events section.<br />
<br />
<br />
<table border="1" cellpadding="0" class="MsoNormalTable" style="mso-cellspacing: 1.5pt; mso-yfti-tbllook: 1184;"><tbody>
<tr style="mso-yfti-firstrow: yes; mso-yfti-irow: 0;"> <td style="background: #0066CC; padding: .75pt 3.0pt 1.5pt 3.0pt;"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><b><span style="color: white; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">Event<o:p></o:p></span></b></div></td> <td style="background: #0066CC; padding: .75pt 3.0pt 1.5pt 3.0pt;"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><b><span style="color: white; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">Waits<o:p></o:p></span></b></div></td> <td style="background: #0066CC; padding: .75pt 3.0pt 1.5pt 3.0pt;"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><b><span style="color: white; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">%Time -outs<o:p></o:p></span></b></div></td> <td style="background: #0066CC; padding: .75pt 3.0pt 1.5pt 3.0pt;"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><b><span style="color: white; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">Total Wait Time (s)<o:p></o:p></span></b></div></td> <td style="background: #0066CC; padding: .75pt 3.0pt 1.5pt 3.0pt;"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><b><span style="color: white; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">Avg wait (ms)<o:p></o:p></span></b></div></td> <td style="background: #0066CC; padding: .75pt 3.0pt 1.5pt 3.0pt;"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><b><span style="color: white; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">Waits /txn<o:p></o:p></span></b></div></td> <td style="background: #0066CC; padding: .75pt 3.0pt 1.5pt 3.0pt;"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><b><span style="color: white; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">% DB time<o:p></o:p></span></b></div></td> </tr>
<tr style="mso-yfti-irow: 1; mso-yfti-lastrow: yes;"> <td style="background: white; padding: .75pt .75pt .75pt .75pt;" valign="top"> <div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: black; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">db flash cache multiblock physical read<o:p></o:p></span></div></td> <td style="background: white; padding: .75pt .75pt .75pt .75pt;" valign="top"> <div align="right" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: right;"><span style="color: black; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">1,048<o:p></o:p></span></div></td> <td style="background: white; padding: .75pt .75pt .75pt .75pt;" valign="top"> <div align="right" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: right;"><span style="color: black; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">0<o:p></o:p></span></div></td> <td style="background: white; padding: .75pt .75pt .75pt .75pt;" valign="top"> <div align="right" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: right;"><span style="color: black; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">1<o:p></o:p></span></div></td> <td style="background: white; padding: .75pt .75pt .75pt .75pt;" valign="top"> <div align="right" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: right;"><span style="color: black; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">1<o:p></o:p></span></div></td> <td style="background: white; padding: .75pt .75pt .75pt .75pt;" valign="top"> <div align="right" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: right;"><span style="color: black; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">0.00<o:p></o:p></span></div></td> <td style="background: white; padding: .75pt .75pt .75pt .75pt;" valign="top"> <div align="right" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: right;"><span style="color: black; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">0.00<o:p></o:p></span></div></td> </tr>
</tbody></table><br />
<br />
We had 1048 waits of 1ms on average.<br />
<br />
<br />
<table border="1" cellpadding="0" class="MsoNormalTable" style="mso-cellspacing: 1.5pt; mso-yfti-tbllook: 1184;"><tbody>
<tr style="mso-yfti-firstrow: yes; mso-yfti-irow: 0;"> <td style="padding: .75pt .75pt .75pt .75pt;"> <div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"><br />
<o:p></o:p></span></div></td> <td colspan="8" style="background: #0066CC; padding: .75pt 3.0pt 1.5pt 3.0pt;"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><b><span style="color: white; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">% of Waits<o:p></o:p></span></b></div></td> </tr>
<tr style="mso-yfti-irow: 1;"> <td style="background: #0066CC; padding: .75pt 3.0pt 1.5pt 3.0pt;"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><b><span style="color: white; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">Event<o:p></o:p></span></b></div></td> <td style="background: #0066CC; padding: .75pt 3.0pt 1.5pt 3.0pt;"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><b><span style="color: white; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">Total Waits<o:p></o:p></span></b></div></td> <td style="background: #0066CC; padding: .75pt 3.0pt 1.5pt 3.0pt;"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><b><span style="color: white; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";"><1ms<o:p></o:p></span></b></div></td> <td style="background: #0066CC; padding: .75pt 3.0pt 1.5pt 3.0pt;"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><b><span style="color: white; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";"><2ms<o:p></o:p></span></b></div></td> <td style="background: #0066CC; padding: .75pt 3.0pt 1.5pt 3.0pt;"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><b><span style="color: white; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";"><4ms<o:p></o:p></span></b></div></td> <td style="background: #0066CC; padding: .75pt 3.0pt 1.5pt 3.0pt;"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><b><span style="color: white; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";"><8ms<o:p></o:p></span></b></div></td> <td style="background: #0066CC; padding: .75pt 3.0pt 1.5pt 3.0pt;"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><b><span style="color: white; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";"><16ms<o:p></o:p></span></b></div></td> <td style="background: #0066CC; padding: .75pt 3.0pt 1.5pt 3.0pt;"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><b><span style="color: white; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";"><32ms<o:p></o:p></span></b></div></td> <td style="background: #0066CC; padding: .75pt 3.0pt 1.5pt 3.0pt;"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><b><span style="color: white; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";"><=1s<o:p></o:p></span></b></div></td> <td style="background: #0066CC; padding: .75pt 3.0pt 1.5pt 3.0pt;"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><b><span style="color: white; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">>1s<o:p></o:p></span></b></div></td> </tr>
<tr style="mso-yfti-irow: 2; mso-yfti-lastrow: yes;"> <td style="background: #FFFFCC; padding: .75pt .75pt .75pt .75pt;" valign="top"> <div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: black; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">db flash cache multiblock physical read<o:p></o:p></span></div></td> <td style="background: #FFFFCC; padding: .75pt .75pt .75pt .75pt;" valign="top"> <div align="right" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: right;"><span style="color: black; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">1171<o:p></o:p></span></div></td> <td style="background: #FFFFCC; padding: .75pt .75pt .75pt .75pt;" valign="top"> <div align="right" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: right;"><span style="color: black; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">83.2<o:p></o:p></span></div></td> <td style="background: #FFFFCC; padding: .75pt .75pt .75pt .75pt;" valign="top"> <div align="right" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: right;"><span style="color: black; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">12.4<o:p></o:p></span></div></td> <td style="background: #FFFFCC; padding: .75pt .75pt .75pt .75pt;" valign="top"> <div align="right" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: right;"><span style="color: black; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">3.2<o:p></o:p></span></div></td> <td style="background: #FFFFCC; padding: .75pt .75pt .75pt .75pt;" valign="top"> <div align="right" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: right;"><span style="color: black; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">.9<o:p></o:p></span></div></td> <td style="background: #FFFFCC; padding: .75pt .75pt .75pt .75pt;" valign="top"> <div align="right" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: right;"><span style="color: black; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">.3<o:p></o:p></span></div></td> <td style="background: #FFFFCC; padding: .75pt .75pt .75pt .75pt;" valign="top"> <div align="right" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: right;"><br />
</div></td> <td style="background: #FFFFCC; padding: .75pt .75pt .75pt .75pt;" valign="top"> <div align="right" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: right;"><br />
</div></td> <td style="border: none; padding: .75pt .75pt .75pt .75pt;"></td> </tr>
</tbody></table><br />
<br />
The wait event histogram shows that again most of the waits are below 1ms with some being up to 16ms.<br />
<br />
<b>write complete waits: flash cache</b><br />
<br />
This is the wait event we see when DBWR is trying to write a block from the buffer cache to the flash cache and a session wants to modify that block. The session waits on this event and goes on with the update after DBWR finishes his job. You can find a good explanation and a good example for this in <a href="http://guyharrison.squarespace.com/blog/2010/1/24/flash-tablespace-vs-db-flash-cache.html">Guy Harrison's post</a>. Here is the event and its histogram in the same AWR report I have.<br />
<br />
<br />
<table border="1" cellpadding="0" class="MsoNormalTable" style="mso-cellspacing: 1.5pt; mso-yfti-tbllook: 1184;"><tbody>
<tr style="mso-yfti-firstrow: yes; mso-yfti-irow: 0;"> <td style="background: #0066CC; padding: .75pt 3.0pt 1.5pt 3.0pt;"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><b><span style="color: white; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">Event<o:p></o:p></span></b></div></td> <td style="background: #0066CC; padding: .75pt 3.0pt 1.5pt 3.0pt;"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><b><span style="color: white; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">Waits<o:p></o:p></span></b></div></td> <td style="background: #0066CC; padding: .75pt 3.0pt 1.5pt 3.0pt;"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><b><span style="color: white; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">%Time -outs<o:p></o:p></span></b></div></td> <td style="background: #0066CC; padding: .75pt 3.0pt 1.5pt 3.0pt;"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><b><span style="color: white; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">Total Wait Time (s)<o:p></o:p></span></b></div></td> <td style="background: #0066CC; padding: .75pt 3.0pt 1.5pt 3.0pt;"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><b><span style="color: white; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">Avg wait (ms)<o:p></o:p></span></b></div></td> <td style="background: #0066CC; padding: .75pt 3.0pt 1.5pt 3.0pt;"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><b><span style="color: white; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">Waits /txn<o:p></o:p></span></b></div></td> <td style="background: #0066CC; padding: .75pt 3.0pt 1.5pt 3.0pt;"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><b><span style="color: white; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">% DB time<o:p></o:p></span></b></div></td> </tr>
<tr style="mso-yfti-irow: 1; mso-yfti-lastrow: yes;"> <td style="background: white; padding: .75pt .75pt .75pt .75pt;" valign="top"> <div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: black; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">write complete waits: flash cache<o:p></o:p></span></div></td> <td style="background: white; padding: .75pt .75pt .75pt .75pt;" valign="top"> <div align="right" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: right;"><span style="color: black; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">345<o:p></o:p></span></div></td> <td style="background: white; padding: .75pt .75pt .75pt .75pt;" valign="top"> <div align="right" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: right;"><span style="color: black; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">0<o:p></o:p></span></div></td> <td style="background: white; padding: .75pt .75pt .75pt .75pt;" valign="top"> <div align="right" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: right;"><span style="color: black; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">1<o:p></o:p></span></div></td> <td style="background: white; padding: .75pt .75pt .75pt .75pt;" valign="top"> <div align="right" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: right;"><span style="color: black; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">4<o:p></o:p></span></div></td> <td style="background: white; padding: .75pt .75pt .75pt .75pt;" valign="top"> <div align="right" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: right;"><span style="color: black; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">0.00<o:p></o:p></span></div></td> <td style="background: white; padding: .75pt .75pt .75pt .75pt;" valign="top"> <div align="right" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: right;"><span style="color: black; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">0.00<o:p></o:p></span></div></td> </tr>
</tbody></table><br />
<br />
<br />
<table border="1" cellpadding="0" class="MsoNormalTable" style="mso-cellspacing: 1.5pt; mso-yfti-tbllook: 1184;"><tbody>
<tr style="mso-yfti-firstrow: yes; mso-yfti-irow: 0;"> <td style="padding: .75pt .75pt .75pt .75pt;"> <div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><br />
</div></td> <td style="padding: .75pt .75pt .75pt .75pt;"> <div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><br />
</div></td> <td colspan="8" style="background: #0066CC; padding: .75pt 3.0pt 1.5pt 3.0pt;"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><b><span style="color: white; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">% of Waits<o:p></o:p></span></b></div></td> </tr>
<tr style="mso-yfti-irow: 1;"> <td style="background: #0066CC; padding: .75pt 3.0pt 1.5pt 3.0pt;"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><b><span style="color: white; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">Event<o:p></o:p></span></b></div></td> <td style="background: #0066CC; padding: .75pt 3.0pt 1.5pt 3.0pt;"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><b><span style="color: white; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">Total Waits<o:p></o:p></span></b></div></td> <td style="background: #0066CC; padding: .75pt 3.0pt 1.5pt 3.0pt;"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><b><span style="color: white; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";"><1ms<o:p></o:p></span></b></div></td> <td style="background: #0066CC; padding: .75pt 3.0pt 1.5pt 3.0pt;"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><b><span style="color: white; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";"><2ms<o:p></o:p></span></b></div></td> <td style="background: #0066CC; padding: .75pt 3.0pt 1.5pt 3.0pt;"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><b><span style="color: white; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";"><4ms<o:p></o:p></span></b></div></td> <td style="background: #0066CC; padding: .75pt 3.0pt 1.5pt 3.0pt;"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><b><span style="color: white; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";"><8ms<o:p></o:p></span></b></div></td> <td style="background: #0066CC; padding: .75pt 3.0pt 1.5pt 3.0pt;"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><b><span style="color: white; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";"><16ms<o:p></o:p></span></b></div></td> <td style="background: #0066CC; padding: .75pt 3.0pt 1.5pt 3.0pt;"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><b><span style="color: white; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";"><32ms<o:p></o:p></span></b></div></td> <td style="background: #0066CC; padding: .75pt 3.0pt 1.5pt 3.0pt;"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><b><span style="color: white; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";"><=1s<o:p></o:p></span></b></div></td> <td style="background: #0066CC; padding: .75pt 3.0pt 1.5pt 3.0pt;"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><b><span style="color: white; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">>1s<o:p></o:p></span></b></div></td> </tr>
<tr style="mso-yfti-irow: 2; mso-yfti-lastrow: yes;"> <td style="background: #FFFFCC; padding: .75pt .75pt .75pt .75pt;" valign="top"> <div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: black; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">write complete waits: flash cache<o:p></o:p></span></div></td> <td style="background: #FFFFCC; padding: .75pt .75pt .75pt .75pt;" valign="top"> <div align="right" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: right;"><span style="color: black; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">345<o:p></o:p></span></div></td> <td style="background: #FFFFCC; padding: .75pt .75pt .75pt .75pt;" valign="top"> <div align="right" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: right;"><span style="color: black; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">22.9<o:p></o:p></span></div></td> <td style="background: #FFFFCC; padding: .75pt .75pt .75pt .75pt;" valign="top"> <div align="right" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: right;"><span style="color: black; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">23.2<o:p></o:p></span></div></td> <td style="background: #FFFFCC; padding: .75pt .75pt .75pt .75pt;" valign="top"> <div align="right" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: right;"><span style="color: black; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">27.2<o:p></o:p></span></div></td> <td style="background: #FFFFCC; padding: .75pt .75pt .75pt .75pt;" valign="top"> <div align="right" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: right;"><span style="color: black; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">19.7<o:p></o:p></span></div></td> <td style="background: #FFFFCC; padding: .75pt .75pt .75pt .75pt;" valign="top"> <div align="right" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: right;"><span style="color: black; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">5.5<o:p></o:p></span></div></td> <td style="background: #FFFFCC; padding: .75pt .75pt .75pt .75pt;" valign="top"> <div align="right" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: right;"><span style="color: black; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">.3<o:p></o:p></span></div></td> <td style="background: #FFFFCC; padding: .75pt .75pt .75pt .75pt;" valign="top"> <div align="right" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: right;"><span style="color: black; font-family: "Arial","sans-serif"; font-size: 8.0pt; mso-fareast-font-family: "Times New Roman";">1.2<o:p></o:p></span></div></td> <td style="border: none; padding: .75pt .75pt .75pt .75pt;"></td> </tr>
</tbody></table><br />
<br />
I had 345 waits in 30 mins with an average time of 4ms. But the important thing is the contribution of this event to DB time was only 1 second in a 30 minute workload.<br />
<br />
You can see that this event starts climbing up in some situations, especially if you are having a problem with your flash cache device and writes to it start to take longer.<br />
<br />
Here is one case where poor DBWR processes are struggling to write to the flash cache. Database control was showing high waits in the "Configuration" class and those waits were "write complete waits", "free buffer waits" and "write complete waits: flash cache". This was all because my flash cache device was a single conventional HDD, not even an SSD. After changing the db_flash_cache_file parameter to use the F5100 array this picture was not seen anymore.<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://4.bp.blogspot.com/_yTgTgKuEjrc/TRuPvfykpVI/AAAAAAAAOXE/h6tqmxwOvok/s1600/em_performance_page_with_asm.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="161" src="http://4.bp.blogspot.com/_yTgTgKuEjrc/TRuPvfykpVI/AAAAAAAAOXE/h6tqmxwOvok/s640/em_performance_page_with_asm.jpg" width="640" /></a></div><br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://2.bp.blogspot.com/_yTgTgKuEjrc/TRuPyBYtKsI/AAAAAAAAOXI/i4pZKpCmqQc/s1600/em_configuration_waits_with_asm.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="144" src="http://2.bp.blogspot.com/_yTgTgKuEjrc/TRuPyBYtKsI/AAAAAAAAOXI/i4pZKpCmqQc/s640/em_configuration_waits_with_asm.jpg" width="640" /></a></div>Yasin Baskanhttp://www.blogger.com/profile/07642821755164831104noreply@blogger.com0tag:blogger.com,1999:blog-15574215.post-71241067397083179782010-12-30T15:32:00.000+02:002010-12-30T15:32:10.028+02:00Sizing the SGA or the buffer cache when enabling the database flash cache<a href="http://download.oracle.com/docs/cd/E11882_01/server.112/e17120/memory005.htm#BABIDBAI">The documentation</a> about using the database flash cache feature recommends increasing db_cache_size or sga_target or memory_target, whichever you are using, to account for the metadata for the blocks kept in the flash cache. This is because for each block in the flash cache, some metadata is kept in the buffer cache. The recommendation is to add 100 bytes for a single instance database and 200 bytes for RAC multiplied by the number of blocks that can fit in the flash cache. So you need to calculate how many blocks the flash cache can keep (by dividing db_flash_cache_size in bytes with the block size) and increase the buffer cache.<br />
<br />
If you do not do this and the buffer cache size is too low, it is automatically increased to cater for this metadata. It also means you will have less space for actual data blocks if you do not increase your buffer cache size.<br />
<br />
I happened to learn this adjustment by chance and it gave me a chance to calculate exactly how much space the flash cache metadata needs.<br />
<br />
This is on a single instance 11.2.0.1 database on an M4000 server running Solaris 10.<br />
<br />
I start with db_cache_size=320m (I am not using sga_target or memory_target because I want to control the size of the buffer cache explicitly) and db_flash_cache_size=20g, the instance starts up without any errors or warnings but the alert log shows:<br />
<br />
<br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">The value of parameter db_cache_size is below the required minimum</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">The new value is 4MB multiplied by the number of cpus plus the memory required for the L2 cache.</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">WARNING: More than half the granules are being allocated to the L2 cache when db_cache_size is set to 335544320. Either decrease the size of L2 cache, or increase the pool size to</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">671088640</span><br />
<br />
If you look at the db_cache_size at this point it shows 448m. The database automatically increased 320m to 448m. It also warns me that most of this space will be used for the flash cache metadata. This is a server with 32 CPUs (cores actually) so I multiply this by 4m, it makes 128m which is the space that will be used for actual data blocks. The remaining 320m will be used for the flash cache metadata. I have 20g of flash cache and my block size is 8K, this means 2,621,440 blocks can fit in there. Let's see how much space is needed for the metadata on one block, since I have 320m for the metadata I convert it to bytes and divide by the number of blocks, 320*1024*1024/2621440, which gives me 128 bytes.<br />
<br />
The documentation states 100 bytes for a single instance database but it is actually a little bit higher.<br />
<br />
Another case to verify. This time I start with db_cache_size=448m and db_flash_cache_size=60g. Similar messages are written to the alert log again.<br />
<br />
<br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">The value of parameter db_cache_size is below the required minimum</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">The new value is 4MB multiplied by the number of cpus plus the memory required for the L2 cache.</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">WARNING: More than half the granules are being allocated to the L2 cache when db_cache_size is set to 469762048. Either decrease the size of L2 cache, or increase the pool size to</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">2013265920</span><br />
<br />
When I look at db_cache_size now I see that it is increased to 1088m.<br />
<br />
Of the 1088m buffer cache, again 128m will be used for data blocks, the remaining 960m is for the flash cache metadata. 60g of flash can hold 7,864,320 blocks, doing the math again tells me that the metadata for a single block is again 128 bytes.<br />
<br />
If you are starting with a small buffer cache, remember to check the alert log and the current size of the buffer cache. If it is already high and you do not see any adjustments be aware that you will use 128 bytes for the metadata for each block. This means you will have less memory for data blocks. It is a good practice to calculate this need beforehand and size the buffer cache accordingly.Yasin Baskanhttp://www.blogger.com/profile/07642821755164831104noreply@blogger.com0tag:blogger.com,1999:blog-15574215.post-68172113579469441462010-12-29T22:04:00.000+02:002010-12-29T22:04:21.512+02:00Using an ASM diskgroup as flash cacheWe have been testing the <a href="http://www.oracle.com/us/products/servers-storage/storage/disk-storage/043967.html">F5100 flash array</a> in our humble lab (borrowed that term from a colleague, he knows who he is). There are two ways to use it, one is to place your datafiles on it, the other is to use it as the database flash cache.<br />
<br />
The <a href="http://download.oracle.com/docs/cd/E11882_01/server.112/e17120/memory005.htm#ADMIN13391">database flash cache</a> feature came with 11.2 and is a way to extend the SGA. It is not the same thing as the flash cache in Exadata, read Kevin Closson's <a href="http://kevinclosson.wordpress.com/2009/12/10/pardon-me-where-is-that-flash-cache-part-i/">this post</a> to find out what the difference is. F5100 is one of the products you can use as the flash cache, the other is the <a href="http://www.oracle.com/us/products/servers-storage/storage/disk-storage/043966.html">F20 card</a>.<br />
<br />
It is possible and (may be the best option) to use ASM to configure the flash cache. <a href="http://download.oracle.com/docs/cd/E11882_01/server.112/e17110/initparams057.htm#REFRN10315">The documentation</a> states that you can use a filename or an ASM diskgroup name for db_flash_cache_file parameter which is the parameter to enable the flash cache feature.<br />
<br />
<b>So, how do we do this?</b><br />
<br />
The first step is creating an ASM diskgroup on the flash devices. In my test, for simplicity I use one flash device which is /dev/rdsk/c1t9d0s0. This is just one flash module (of size 24GB) from F5100. The process for creating the diskgroup is no different than creating a diskgroup on conventional disks, just make sure your asm_diskstring includes the flash device path. By using asmca I created a diskgroup named FLASH using external redundancy on this single flash device.<br />
<br />
Then following the documentation I set the parameters to enable the flash cache.<br />
<br />
<br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">SQL> alter system set db_flash_cache_size=20G scope=spfile;</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">System altered.</span><br />
<div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><br />
</span></div><div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">SQL> alter system set db_flash_cache_file='+FLASH' scope=spfile;</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><br />
</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">System altered.</span></div></div><div><br />
</div><div>Now time to restart to make the new parameters effective.</div><div><br />
</div><div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">SQL> startup force;</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">ORACLE instance started.</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><br />
</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">Total System Global Area 2606465024 bytes</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">Fixed Size 2150840 bytes</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">Variable Size 2113932872 bytes</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">Database Buffers 469762048 bytes</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">Redo Buffers 20619264 bytes</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">Database mounted.</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">ORA-03113: end-of-file on communication channel</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">Process ID: 17560</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">Session ID: 2471 Serial number: 3</span></div></div><div><br />
</div><div>The instance fails to start. Looking at the alert log file we see this.</div><div><br />
</div><div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">Errors in file /u01/app/oracle/diag/rdbms/flash/flash/trace/flash_dbw0_18053.trc:</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">ORA-01565: error in identifying file '+FLASH'</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">ORA-17503: ksfdopn:2 Failed to open file +FLASH</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">ORA-15045: ASM file name '+FLASH' is not in reference form</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">DBW0 (ospid: 18053): terminating the instance due to error 1565</span></div></div><div><br />
</div><div>So it seems that we need to specify the actual file name, not the name of the diskgroup. Let's see what we have in the diskgroup FLASH using amscmd.</div><div><br />
</div><div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">ASMCMD> pwd</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">+FLASH/FLASH/FLASHFILE</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">ASMCMD> ls</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">bufpool#2147472020.256.739041793</span></div></div><div><br />
</div><div>Even if we got an error when starting the instance there is a file created under +FLASH/FLASH/FLASHFILE. The first FLASH in the path is the diskgroup name, the second is my database name.</div><div><br />
</div><div>What we need to do is set this file as the db_flash_cache_file parameter to be able to start the instance with flash cache enabled. I edit my text init.ora file and change the parameter.</div><div><br />
</div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">db_flash_cache_file='+FLASH/FLASH/FLASHFILE/bufpool#2147472020.256.739041793'</span></div><div><br />
</div><div>Now we can start the instance.</div><div><br />
</div><div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">SQL> create spfile from pfile='/tmp/x.ora';</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><br />
</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">File created.</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><br />
</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">SQL> startup</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">ORACLE instance started.</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><br />
</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">Total System Global Area 2606465024 bytes</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">Fixed Size 2150840 bytes</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">Variable Size 2113932872 bytes</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">Database Buffers 469762048 bytes</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">Redo Buffers 20619264 bytes</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">Database mounted.</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">Database opened.</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">SQL> show parameter flash_cache</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><br />
</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">NAME TYPE VALUE</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">------------------------------------ ----------- ------------------------------</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">db_flash_cache_file string +FLASH/flash/flashfile/bufpool</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> #2147472020.256.739041793</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">db_flash_cache_size big integer 20G</span></div></div><div><br />
</div><div>The instance is up and I can see that the flash cache is enabled and using the correct ASM diskgroup.</div><div><br />
</div><div>The documentation states that we can use the diskgroup name but as we saw it needs some correction.</div><div><br />
<b>A problem and a lesson</b><br />
<br />
</div><div>The path I followed to this point is a little embarrassing and teaches a lesson about setting parameters.</div><div><br />
</div><div>At first I used db_flash_cache_file='FLASH' assuming it would use the ASM diskgroup. After restarting the instance I immediately started a workload on the database to see the effects of the flash cache. Here is what I saw in the Enterprise Manager performance page.</div><div><br />
</div><div class="separator" style="clear: both; text-align: center;"></div><br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://4.bp.blogspot.com/_yTgTgKuEjrc/TRuPvfykpVI/AAAAAAAAOXE/h6tqmxwOvok/s1600/em_performance_page_with_asm.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="161" src="http://4.bp.blogspot.com/_yTgTgKuEjrc/TRuPvfykpVI/AAAAAAAAOXE/h6tqmxwOvok/s640/em_performance_page_with_asm.jpg" width="640" /></a></div><div>The waits of class "Configuration" were holding back the database. When I clicked on "Configuration" link I saw this.</div><div><br />
</div><div class="separator" style="clear: both; text-align: center;"><a href="http://2.bp.blogspot.com/_yTgTgKuEjrc/TRuPyBYtKsI/AAAAAAAAOXI/i4pZKpCmqQc/s1600/em_configuration_waits_with_asm.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="144" src="http://2.bp.blogspot.com/_yTgTgKuEjrc/TRuPyBYtKsI/AAAAAAAAOXI/i4pZKpCmqQc/s640/em_configuration_waits_with_asm.jpg" width="640" /></a></div><div><br />
</div><div><br />
</div><div>The system was mostly waiting on "free buffer waits", "write complete waits" and "write complete waits: flash cache". This is because of DBWR. Someone has to write the blocks from the buffer cache to the flash when the buffer cache is full. This process is DBWR. Since I love using the <a href="http://www.brendangregg.com/dtrace.html#DTraceToolkit">DTrace toolkit</a> I used the iosnoop script in that toolkit to find out what DBWR was doing. iosnoop can show you what file a process reading from or writing to most. So I ran iosnoop for one of the DBWR processes.</div><div><br />
</div><div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">bash-3.00# ./iosnoop -p 16631</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> UID PID D BLOCK SIZE COMM PATHNAME</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> 101 16631 W 175311 8704 oracle <none></none></span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> 101 16631 W 246883104 8192 oracle /u01/app/oracle/product/11.2.0/dbhome_1/dbs/FLASH</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> 101 16631 W 246883104 8192 oracle /u01/app/oracle/product/11.2.0/dbhome_1/dbs/FLASH</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> 101 16631 W 175328 8704 oracle <none></none></span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> 101 16631 W 246883120 8192 oracle /u01/app/oracle/product/11.2.0/dbhome_1/dbs/FLASH</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> 101 16631 W 246883120 8192 oracle /u01/app/oracle/product/11.2.0/dbhome_1/dbs/FLASH</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> 101 16631 W 175345 8704 oracle <none></none></span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> 101 16631 W 246883136 8192 oracle /u01/app/oracle/product/11.2.0/dbhome_1/dbs/FLASH</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> 101 16631 W 246883136 8192 oracle /u01/app/oracle/product/11.2.0/dbhome_1/dbs/FLASH</span></div></div><div><br />
</div><div>The file it is trying to write to is $ORACLE_HOME/dbs/FLASH and since that directory is on a slow local disk it is taking a long time and causing the waits.</div><div><br />
</div><div>If I had looked at the db_flash_cache_file parameter after restarting the database to see if it was alright I would have seen this before starting the workload. So, once more it teaches to check if the parameter was set the way you want it before taking further action.</div>Yasin Baskanhttp://www.blogger.com/profile/07642821755164831104noreply@blogger.com0tag:blogger.com,1999:blog-15574215.post-62044431145010681152010-12-27T11:32:00.000+02:002010-12-27T11:32:25.207+02:00What does ISDEFAULT mean in v$parameter?There is a common misunderstanding among DBAs about the column ISDEFAULT in the view v$parameter. Some think that when this column is TRUE it means the current value of the parameter is the default value. This leads to wrong conclusions and sometimes wrong settings for even production environments.<br />
<br />
<a href="http://download.oracle.com/docs/cd/E11882_01/server.112/e17110/dynviews_2087.htm#REFRN30176">The documentation</a> says this about this column:<br />
<br />
<span class="Apple-style-span" style="border-collapse: collapse; font-family: Tahoma, sans-serif; font-size: x-small;">"Indicates whether the parameter is set to the default value (</span><span class="Apple-style-span" style="border-collapse: collapse; font-family: Tahoma, sans-serif; font-size: x-small;"><code style="font-family: monospace; font-size: 12px;">TRUE</code></span><span class="Apple-style-span" style="border-collapse: collapse; font-family: Tahoma, sans-serif; font-size: x-small;">) or the parameter value was specified in the parameter file (</span><span class="Apple-style-span" style="border-collapse: collapse; font-family: Tahoma, sans-serif; font-size: x-small;"><code style="font-family: monospace; font-size: 12px;">FALSE</code></span><span class="Apple-style-span" style="border-collapse: collapse; font-family: Tahoma, sans-serif; font-size: x-small;">)"</span><br />
<br />
This explanation is not a clear one and different people may understand different things from it.<br />
<br />
This column is dependent on the setting in the parameter file when the instance is started. It does not show if the current value is the default or not. It only shows if the parameter is set in the parameter file or not. When it is TRUE it means that you did not set this in the parameter file when starting the instance. When it is FALSE it means this parameter was set in the parameter file when starting the instance.<br />
<br />
Here is a real life case about this. When changing a few parameters an Exadata DBA accidentally sets the parameter cell_offload_processing to FALSE using an alter system command. When he tries to take back the settings he looks at v$parameter.ISDEFAULT to find out if cell_offload_processing=FALSE is the default setting. He sees that ISDEFAULT=TRUE and arrives at the wrong conclusion that cell_offload_processing=FALSE is the default value and leaves the parameter that way. This causes all Exadata storage offloading to be disabled and may cause query times to go over the roof.<br />
<br />
Let's look at this with an example on 11.2.0.2. This is from a database created with default parameters.<br />
<br />
<br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">SQL> select name,VALUE,ISDEFAULT,ISMODIFIED from v$parameter</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> 2 where name='cell_offload_processing';</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">NAME VALUE ISDEFAULT ISMODIFIED</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">------------------------------ ------------------------------ --------- ----------</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">cell_offload_processing TRUE TRUE FALSE</span><br />
<br />
We see that the parameter is set to TRUE, ISMODIFIED is false meaning we did not modify the parameter and ISDEFAULT is TRUE meaning we did not set this parameter in the parameter file.<br />
<br />
Now let's change it.<br />
<br />
<br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">SQL> alter system set cell_offload_processing=FALSE;</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">System altered.</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">SQL> select name,VALUE,ISDEFAULT,ISMODIFIED from v$parameter</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> 2 where name='cell_offload_processing';</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">NAME VALUE ISDEFAULT ISMODIFIED</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">------------------------------ ------------------------------ --------- ----------</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">cell_offload_processing FALSE TRUE SYSTEM_MOD</span><br />
<br />
After we set it FALSE we see that ISMODIFIED reflected the change, but ISDEFAULT is still TRUE. From this if a DBA concludes that this is the default value and takes action based on that, the result will be wrong. As we did not set this parameter in the parameter file when starting the instance, ISDEFAULT still shows TRUE.<br />
<br />
Let's do a restart and look at v$parameter again.<br />
<br />
<br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">SQL> startup force;</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">ORACLE instance started.</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">Total System Global Area 839282688 bytes</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">Fixed Size 2231128 bytes</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">Variable Size 583009448 bytes</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">Database Buffers 251658240 bytes</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">Redo Buffers 2383872 bytes</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">Database mounted.</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">Database opened.</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">SQL> select name,VALUE,ISDEFAULT,ISMODIFIED from v$parameter</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> 2 where name='cell_offload_processing';</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">NAME VALUE ISDEFAULT ISMODIFIED</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">------------------------------ ------------------------------ --------- ----------</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">cell_offload_processing FALSE FALSE FALSE</span><br />
<br />
Now we see that ISDEFAULT is FALSE because the parameter was in the parameter file.<br />
<br />
What if we set it the parameter to the default value.<br />
<br />
<br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">SQL> alter system set cell_offload_processing=TRUE;</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">System altered.</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">SQL> select name,VALUE,ISDEFAULT,ISMODIFIED from v$parameter</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> 2 where name='cell_offload_processing';</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">NAME VALUE ISDEFAULT ISMODIFIED</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">------------------------------ ------------------------------ --------- ----------</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">cell_offload_processing TRUE FALSE SYSTEM_MOD</span><br />
<br />
As you see even if the parameter has the default value now, ISDEFAULT still shows FALSE.<br />
<br />
Again let's restart and look again.<br />
<br />
<br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">SQL> startup force;</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">ORACLE instance started.</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">Total System Global Area 839282688 bytes</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">Fixed Size 2231128 bytes</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">Variable Size 583009448 bytes</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">Database Buffers 251658240 bytes</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">Redo Buffers 2383872 bytes</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">Database mounted.</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">Database opened.</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">SQL> select name,VALUE,ISDEFAULT,ISMODIFIED from v$parameter</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> 2 where name='cell_offload_processing';</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">NAME VALUE ISDEFAULT ISMODIFIED</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">------------------------------ ------------------------------ --------- ----------</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">cell_offload_processing TRUE FALSE FALSE</span><br />
<br />
The parameter has the default value and ISDEFAULT is still FALSE after a restart. This is again because even if we set the parameter to the default value it was in the parameter file anyway.<br />
<br />
What's the correct way to fix this? If we reset the parameter instead of setting it explicitly it will revert back to the default value.<br />
<br />
<br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">SQL> alter system reset cell_offload_processing;</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">System altered.</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">SQL> select name,VALUE,ISDEFAULT,ISMODIFIED from v$parameter</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> 2 where name='cell_offload_processing';</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">NAME VALUE ISDEFAULT ISMODIFIED</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">------------------------------ ------------------------------ --------- ----------</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">cell_offload_processing TRUE FALSE FALSE</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">SQL> startup force;</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">ORACLE instance started.</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">Total System Global Area 839282688 bytes</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">Fixed Size 2231128 bytes</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">Variable Size 583009448 bytes</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">Database Buffers 251658240 bytes</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">Redo Buffers 2383872 bytes</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">Database mounted.</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">Database opened.</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">SQL> select name,VALUE,ISDEFAULT,ISMODIFIED from v$parameter</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> 2 where name='cell_offload_processing';</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">NAME VALUE ISDEFAULT ISMODIFIED</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">------------------------------ ------------------------------ --------- ----------</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">cell_offload_processing TRUE TRUE FALSE</span><br />
<br />
Now we are back to the defaults as the reset command removed the parameter from the parameter file.<br />
<br />
So, do not count on the ISDEFAULT column when you are trying to find if the current value of a parameter is the default value or not. The documentation is the most reliable source to find out the default values of the parameters.Yasin Baskanhttp://www.blogger.com/profile/07642821755164831104noreply@blogger.com0tag:blogger.com,1999:blog-15574215.post-60257957398722193782010-12-24T15:23:00.000+02:002010-12-24T15:23:32.956+02:00Creating listeners on the private network interfaces of RAC nodes or use that Exadata Infiniband networkIn general usage, everyone configures tns listeners on the public interfaces of RAC nodes so that clients can connect through the public network. Conventionally the private network interfaces are used for the interconnect traffic so most people do not open them to the application because the application traffic may interfere with the interconnect messaging.<br />
<br />
But what if we have a high-speed and high-bandwidth interconnect network that some applications can also use for fast communication to the nodes? Can we create tns listeners on the private interfaces, if we can how? This high-speed interconnect network is especially true for Exadata where we have an Infiniband private network that is used for RAC interconnect and for the communication between the storage nodes and the database nodes. Since it is a high-speed low-latency network the application servers or ETL servers can connect to that network and use it for fast data transfer.<br />
<br />
To do this we need to create tns listeners on the private interfaces so that clients can connect through sqlnet. The following steps will show how I did this. I did this configuration on a half rack Database Machine but since I do not have access to that now the example here is based on a test Virtualbox system (which runs 11.2.0.2 RAC) so Infiniband is not here but the steps are the same for Exadata. Also DNS is not used, all addresses are defined in the /etc/hosts files of the nodes.<br />
<br />
Here is my /etc/hosts file for this setup.<br />
<br />
<br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">127.0.0.1 localhost.localdomain localhost</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">::1 localhost6.localdomain6 localhost6</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"># Public</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">192.168.56.2 rac1.localdomain rac1</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">192.168.56.3 rac2.localdomain rac2</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"># Private</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">192.168.0.101 rac1-priv.localdomain rac1-priv</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">192.168.0.102 rac2-priv.localdomain rac2-priv</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"># Virtual</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">192.168.56.4 rac1-vip.localdomain rac1-vip</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">192.168.56.5 rac2-vip.localdomain rac2-vip</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"># SCAN</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">192.168.56.6 rac-scan.localdomain rac-scan</span><br />
<br />
I have two nodes, rac1 and rac2. The private network is 192.168.0 and the public network is 192.168.56. After the default installation I have the default listeners created on the public interfaces, there is only one SCAN address as this is a test setup.<br />
<br />
Assume I have an ETL server that is connected to the private network which needs to connect to the database through the private interface. What I need is a listener per node that is listening on the private IP addresses.<br />
<br />
If you start with netca ( from the grid home because that is where the listeners run on 11.2) and try to create the listeners you will see that you will not be able to select the private network.<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://2.bp.blogspot.com/_yTgTgKuEjrc/TRSAFExd6RI/AAAAAAAAN4A/LeJRAz3aFH4/s1600/netca_with_default_subnet.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="212" src="http://2.bp.blogspot.com/_yTgTgKuEjrc/TRSAFExd6RI/AAAAAAAAN4A/LeJRAz3aFH4/s320/netca_with_default_subnet.jpg" width="320" /></a></div><br />
It will show only the public network because this selection is based on your virtual IP definitions. Since I do not have any VIPs on the private network I do not see it.<br />
<br />
So the first step is to create VIPs on the private interfaces. I start by adding the new VIPs to the /etc/hosts files. These lines should be added to both nodes' /etc/hosts file.<br />
<br />
<br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"># Private VIPs</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">192.168.0.103 rac1-ib.localdomain rac1-ib</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">192.168.0.104 rac2-ib.localdomain rac2-ib</span><br />
<br />
<br />
With root I run "srvctl add vip" from the grid home to create the VIPs.<br />
<br />
<br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">[root@rac1 ~]# cd /u01/app/11.2.0/grid/bin/</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">[root@rac1 bin]# ./srvctl add vip -n rac1 -A 192.168.0.103/255.255.255.0/eth1 -k 2</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">[root@rac1 bin]# ./srvctl add vip -n rac2 -A 192.168.0.104/255.255.255.0/eth1 -k 2</span><br />
<br />
I created a VIP for each node on the eth1 interface which is the private interface and I have specified "-k 2" to indicate that the network number is 2. You can use "srvctl add vip -h" to see what options you have.<br />
<br />
At this step if you look at the "ifconfig -a" output you will not see the new VIPs up because we have not started them up yet. Let's start them now.<br />
<br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">[root@rac1 bin]# ./srvctl start vip -i rac1-ib</span><br />
<br />
Now you will see the new IP up in the "ifconfig -a" output. This is the related line.<br />
<br />
<br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">eth1:2 Link encap:Ethernet HWaddr 08:00:27:8B:69:FB</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"> inet addr:192.168.0.103 Bcast:192.168.0.255 Mask:255.255.255.0</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"> UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1</span><br />
<br />
We need to start the new VIP for the second node also. Go to the second node and repeat the same for that.<br />
<br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">[root@rac2 bin]# ./srvctl start vip -i rac2-ib</span><br />
<br />
We can now start netca and we see the new subnet there. You can create the listener by selecting the new subnet. I named mine as LISTENER_IB and chose 1522 as the port number.<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://3.bp.blogspot.com/_yTgTgKuEjrc/TRSFDtxzyqI/AAAAAAAAN4E/H3zFfGab5J0/s1600/netca_with_new_subnet.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="213" src="http://3.bp.blogspot.com/_yTgTgKuEjrc/TRSFDtxzyqI/AAAAAAAAN4E/H3zFfGab5J0/s320/netca_with_new_subnet.jpg" width="320" /></a></div><br />
After netca completes here is what I see on rac1.<br />
<br />
<br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">[oracle@rac1 ~]$ lsnrctl status listener_ib</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 24-DEC-2010 13:36:20</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">Copyright (c) 1991, 2010, Oracle. All rights reserved.</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_IB)))</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">STATUS of the LISTENER</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">------------------------</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">Alias LISTENER_IB</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">Start Date 24-DEC-2010 13:34:46</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">Uptime 0 days 0 hr. 1 min. 35 sec</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">Trace Level off</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">Security ON: Local OS Authentication</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">SNMP OFF</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">Listener Parameter File /u01/app/11.2.0/grid/network/admin/listener.ora</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">Listener Log File /u01/app/oracle/diag/tnslsnr/rac1/listener_ib/alert/log.xml</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">Listening Endpoints Summary...</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"> (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_IB)))</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"> (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.103)(PORT=1522)))</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">The listener supports no services</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">The command completed successfully</span><br />
<br />
The listener is up and running but it does support any services yet. Here is my init.ora parameters related to the listener.<br />
<br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">SQL> show parameter listen</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">NAME TYPE VALUE</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">------------------------------------ ----------- ------------------------------</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">listener_networks string</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">local_listener string (DESCRIPTION=(ADDRESS_LIST=(AD</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"> DRESS=(PROTOCOL=TCP)(HOST=192.</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"> 168.56.4)(PORT=1521))))</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">remote_listener string rac-scan:1521</span><br />
<br />
I can change the local_listener parameter and add this listener so that my database registers with it. After that as the default installation sets the SCAN listener as the remote_listener SCAN will be able to direct connections to this listener as well. But, there is a problem with this. What happens if SCAN directs the connection from the ETL server to the public interface instead of the private one? Or vice-versa, what happens if it directs connections from the public network clients to the private interface? Users will get errors because they cannot reach the private network and the ETL server will get errors because it cannot reach the public network.<br />
<br />
The correct way to register my database to the listeners is to use the listener_networks parameter. <a href="http://download.oracle.com/docs/cd/E11882_01/network.112/e10836/listenercfg.htm#NETAG1470">listener_networks</a> is a new 11.2 parameter and serves the purpose of cross-registration when you have listeners on multiple networks. Basically with it you can say, "register my local listeners on the public interfaces to the SCAN listener, register my local listeners on the private interface to each other".<br />
<br />
This way clients using SCAN will connect to the public interfaces and clients using the private network will connect to the private interfaces. Let's do it now.<br />
<br />
Not to clutter the listener parameters with long tns descriptions let's add the definitions to the tnsnames.ora file and use the names instead. On both nodes's tnsnames.ora file residing in the database home I add these lines. Remember to change the hostnames to rac2 for the ones other ORCL_IB when editing the file on rac2.<br />
<br />
<br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">ORCL_IB =</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"> (DESCRIPTION =</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"> (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-ib)(PORT = 1522))</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"> (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-ib)(PORT = 1522))</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"> )</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">ORCL_PUBLIC_LOCAL=</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"> (DESCRIPTION =</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"> (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521))</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"> )</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">ORCL_PRIVATE_LOCAL=</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"> (DESCRIPTION =</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"> (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-ib)(PORT = 1522))</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"> )</span><br />
<div><br />
</div><div>ORCL_PUBLIC_LOCAL will be used as the local listener for the public network, ORCL_PRIVATE_LOCAL will be used as the local listener for the private network, ORCL_IB will be used as the remote listener for the private network so that the local private listeners can register to each other and the SCAN address will be used as the remote listener for the public network.</div><div><br />
</div><div>Now time to change the parameters.</div><div><br />
</div><div><div><div><div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">SQL> alter system set listener_networks='((name=public_network)(local_listener=orcl_public_local)(remote_listener=rac-scan:1521))','((name=priv_network)(local_listener=orcl_private_local)(remote_listener=orcl_ib))';</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"><br />
</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">System altered.</span></div></div></div></div></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"><br />
</span></div><div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">SQL> alter system set remote_listener='';</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"><br />
</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">System altered.</span></div></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"><br />
</span></div><div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">SQL> show parameter listen</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"><br />
</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">NAME TYPE VALUE</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">------------------------------------ ----------- ------------------------------</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">listener_networks string ((name=public_network)(local_l</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"> istener=orcl_public_local)(rem</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"> ote_listener=rac-scan:1521)),</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"> ((name=priv_network)(local_lis</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"> tener=orcl_private_local)(remo</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"> te_listener=rac-scan:1521))</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">local_listener string (DESCRIPTION=(ADDRESS_LIST=(AD</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"> DRESS=(PROTOCOL=TCP)(HOST=192.</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"> 168.56.4)(PORT=1521))))</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">remote_listener string</span></div></div><div><br />
</div><div>Now let's see what is registered to the new listener listener_ib.</div><div><br />
</div><div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">[oracle@rac1 ~]$ lsnrctl status listener_ib</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"><br />
</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 24-DEC-2010 15:03:17</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"><br />
</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">Copyright (c) 1991, 2010, Oracle. All rights reserved.</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"><br />
</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_IB)))</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">STATUS of the LISTENER</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">------------------------</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">Alias LISTENER_IB</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">Start Date 24-DEC-2010 13:34:46</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">Uptime 0 days 1 hr. 28 min. 32 sec</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">Trace Level off</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">Security ON: Local OS Authentication</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">SNMP OFF</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">Listener Parameter File /u01/app/11.2.0/grid/network/admin/listener.ora</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">Listener Log File /u01/app/oracle/diag/tnslsnr/rac1/listener_ib/alert/log.xml</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">Listening Endpoints Summary...</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"> (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_IB)))</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"> (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.103)(PORT=1522)))</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">Services Summary...</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">Service "orcl" has 2 instance(s).</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"> Instance "orcl1", status READY, has 2 handler(s) for this service...</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"> Instance "orcl2", status READY, has 1 handler(s) for this service...</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">The command completed successfully</span></div></div><div><br />
</div><div>Both instances are registered and can be served by this listener. Also have a look at the scan listener to make sure we did not make a mess.</div><div><br />
</div><div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">[oracle@rac1 ~]$ lsnrctl status listener_scan1</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"><br />
</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 24-DEC-2010 15:04:47</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"><br />
</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">Copyright (c) 1991, 2010, Oracle. All rights reserved.</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"><br />
</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">STATUS of the LISTENER</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">------------------------</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">Alias LISTENER_SCAN1</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">Start Date 24-DEC-2010 12:06:02</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">Uptime 0 days 2 hr. 58 min. 45 sec</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">Trace Level off</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">Security ON: Local OS Authentication</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">SNMP OFF</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">Listener Parameter File /u01/app/11.2.0/grid/network/admin/listener.ora</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">Listener Log File /u01/app/11.2.0/grid/log/diag/tnslsnr/rac1/listener_scan1/alert/log.xml</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">Listening Endpoints Summary...</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"> (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"> (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.6)(PORT=1521)))</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">Services Summary...</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">Service "orcl" has 2 instance(s).</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"> Instance "orcl1", status READY, has 1 handler(s) for this service...</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"> Instance "orcl2", status READY, has 1 handler(s) for this service...</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">The command completed successfully</span></div></div><div><br />
</div><div>It is OK too.</div><div><br />
</div><div>Now we can use this tnsnames entry to connect through the private network.</div><div><br />
</div><div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">ORCL_ETL =</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"> (DESCRIPTION =</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"> (LOAD_BALANCE=on)</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"> (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-ib)(PORT = 1522))</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"> (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-ib)(PORT = 1522))</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"> (CONNECT_DATA =</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"> (SERVER = DEDICATED)</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"> (SERVICE_NAME = orcl)</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"> )</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"> )</span></div></div><div><br />
</div><div>It took quite a while for me to get this in writing, if you use this procedure and see problems or if you think there are points not explained clearly, let me know so I can edit the post, this way it will be more useful to others who may need it.</div>Yasin Baskanhttp://www.blogger.com/profile/07642821755164831104noreply@blogger.com0tag:blogger.com,1999:blog-15574215.post-43571812255900262142010-12-01T15:17:00.000+02:002010-12-01T15:17:56.697+02:00Applying Grid Infrastructure Patch Set 1 (GI PSU1) to non-RAC installationsIf you are trying to patch an existing 11.2.0.1 installation on a non-RAC GI (also called single instance HA) system be aware that some steps will be different from the patch readme or the documentation.<br />
<br />
Here is what I experienced.<br />
<br />
The <a href="http://download.oracle.com/docs/cd/E11882_01/install.112/e17212/procstop.htm#BABEIBEE">documentation for Grid Infrastructure 11.2</a> states that if you want to upgrade from 11.2.0.1 to 11.2.0.2 you need to apply some patches first.<br />
<br />
<span class="Apple-style-span" style="font-family: inherit;"><span class="Apple-style-span" style="font-size: x-small;">"To upgrade existing 11.2.0.1 Oracle Grid Infrastructure installations to Oracle Grid Infrastructure 11.2.0.2, you must first do at least one of the following:</span></span><br />
<ul><li><span class="Apple-style-span" style="font-family: inherit;"><span class="Apple-style-span" style="font-size: x-small;">Patch the release 11.2.0.1 Oracle Grid Infrastructure home with the 9413827 and 9706490 patches.</span></span></li>
<li><span class="Apple-style-span" style="font-family: inherit;"><span class="Apple-style-span" style="font-size: x-small;">Install Oracle Grid Infrastructure Patch Set 1 (GI PSU1) or Oracle Grid Infrastructure Patch Set 2 (GI PSU2)."</span></span></li>
</ul>If you choose to apply GI PSU1 and follow the README there is a section that tells you to prepare the GI home.<br />
<br />
<span class="Apple-style-span" style="font-family: inherit;"><span class="Apple-style-span" style="font-size: x-small;">"2.2.5 Prepare GI home for patch</span></span><br />
<span class="Apple-style-span" style="font-family: inherit;"><span class="Apple-style-span" style="font-size: x-small;"><br />
</span></span><br />
<span class="Apple-style-span" style="font-family: inherit;"><span class="Apple-style-span" style="font-size: x-small;"> As an owner of Oracle GI home software owner, run the command:</span></span><br />
<span class="Apple-style-span" style="font-family: inherit;"><span class="Apple-style-span" style="font-size: x-small;"> %<gi_home>/bin/srvctl stop home -o <gi_home> -s <stat_file_location> -n <node_name>"</node_name></stat_file_location></gi_home></gi_home></span></span><br />
<br />
When I ran this on a single instance system I got:<br />
<br />
<span class="Apple-style-span" style="font-size: x-small;">[oracle@oel5 patches]$ srvctl stop home -o /u01/app/product/11.2.0/grid -s /tmp/stat.txt</span><br />
<span class="Apple-style-span" style="font-size: x-small;">PRCH-1002 : Failed to stop resources running from crs home /u01/app/product/11.2.0/grid</span><br />
<span class="Apple-style-span" style="font-size: x-small;">PRCH-1030 : one or more resources failed to stop PRCH-1026 : Failed to stop ASM</span><br />
<span class="Apple-style-span" style="font-size: x-small;">PRCD-1027 : Failed to retrieve database orcl</span><br />
<span class="Apple-style-span" style="font-size: x-small;">PRCD-1035 : orcl is the database unique name of a single instance database, not a cluster database</span><br />
<br />
This is a problem with srvctl and this step must be skipped for non-RAC GI installations as the next step will stop GI. After this README tells you to unlock the grid home:<br />
<br />
<span class="Apple-style-span" style="font-size: x-small;">"You must invoke this script as root user to open protection on GI software files for patch application</span><br />
<span class="Apple-style-span" style="font-size: x-small;"> #<gi_home>/crs/install/rootcrs.pl -unlock"</gi_home></span><br />
<br />
rootcrs.pl is the script used for RAC environments. If you have non-RAC GI the script you need to use is roothas.pl. After changing rootcrs.pl with roothas.pl and unlocking the grid home you can continue with applying the patch.<br />
<br />
MOS note 1089476.1 explains the steps to patch a non-RAC GI home. Keep this note in mind when you are working on such a system.Yasin Baskanhttp://www.blogger.com/profile/07642821755164831104noreply@blogger.com0tag:blogger.com,1999:blog-15574215.post-11295295381165269722010-11-09T11:22:00.000+02:002010-11-09T11:22:09.262+02:00Do not forget to set gpgkey when installing the oracle-validated rpmIn my <a href="http://oracletoday.blogspot.com/2010/11/how-to-use-oracle-enterprise-linux.html">previous post</a> I had five lines added to /etc/yum.conf.<br />
<br />
<br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">[local]</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">name="media"</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">baseurl=file:///media/Enterprise%20Linux%20dvd%2020090127/Server</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">enabled=1</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">gpgkey=file:///media/Enterprise%20Linux%20dvd%2020090127/RPM-GPG-KEY-oracle</span></span><br />
<br />
The ones except gpgkey are self-explanatory. The parameter gpgkey is used to point to a file that contains the public key for the packages you install so that yum can verify the package's authenticity if needed. The file I use is the key file that contains the public key to verify the oracle-validated rpm.<br />
<br />
oracle-validated rpm is used to install the necessary packages for Oracle installations, it also updates the kernel parameters and creates a default oracle user. Using it is an easy way to prepare your server for Oracle installations, the other option is to check the installation prerequisites from the documentation and install the packages, update the kernel parameters and create the user yourself.<br />
<br />
MOS Note 579101.1 explains how to install the oracle-validated rpm.<br />
<br />
I tried to install this rpm without checking the note and I did not use the gpgkey parameter in /etc/yum.conf initially. This is what you get if you do not set it.<br />
<br />
<br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">[root@oeltest tmp]# yum install oracle-validated-1.0.0-18.el5.i386.rpm</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">Loaded plugins: security</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">Setting up Install Process</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">Parsing package install arguments</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">Examining oracle-validated-1.0.0-18.el5.i386.rpm: oracle-validated-1.0.0-18.el5.i386</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">Marking oracle-validated-1.0.0-18.el5.i386.rpm to be installed</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">Resolving Dependencies</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">--> Running transaction check</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">---> Package oracle-validated.i386 0:1.0.0-18.el5 set to be updated</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">--> Processing Dependency: compat-db for package: oracle-validated</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">--> Processing Dependency: compat-gcc-34 for package: oracle-validated</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">--> Processing Dependency: compat-gcc-34-c++ for package: oracle-validated</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">--> Processing Dependency: elfutils-libelf-devel for package: oracle-validated</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">--> Processing Dependency: gcc for package: oracle-validated</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">--> Processing Dependency: gcc-c++ for package: oracle-validated</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">--> Processing Dependency: gdb for package: oracle-validated</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">--> Processing Dependency: glibc-devel for package: oracle-validated</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">--> Processing Dependency: glibc-headers for package: oracle-validated</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">--> Processing Dependency: kernel-headers for package: oracle-validated</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">--> Processing Dependency: libXp for package: oracle-validated</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">--> Processing Dependency: libaio-devel for package: oracle-validated</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">--> Processing Dependency: libstdc++-devel for package: oracle-validated</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">--> Processing Dependency: sysstat for package: oracle-validated</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">--> Processing Dependency: unixODBC for package: oracle-validated</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">--> Processing Dependency: unixODBC-devel for package: oracle-validated</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">--> Running transaction check</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">---> Package kernel-headers.i386 0:2.6.18-128.el5 set to be updated</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">---> Package libaio-devel.i386 0:0.3.106-3.2 set to be updated</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">---> Package compat-gcc-34-c++.i386 0:3.4.6-4 set to be updated</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">---> Package gdb.i386 0:6.8-27.el5 set to be updated</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">---> Package libXp.i386 0:1.0.0-8.1.el5 set to be updated</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">---> Package compat-db.i386 0:4.2.52-5.1 set to be updated</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">---> Package unixODBC-devel.i386 0:2.2.11-7.1 set to be updated</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">---> Package gcc.i386 0:4.1.2-44.el5 set to be updated</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">--> Processing Dependency: libgomp >= 4.1.2-44.el5 for package: gcc</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">---> Package compat-gcc-34.i386 0:3.4.6-4 set to be updated</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">---> Package glibc-headers.i386 0:2.5-34 set to be updated</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">---> Package sysstat.i386 0:7.0.2-3.el5 set to be updated</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">---> Package elfutils-libelf-devel.i386 0:0.137-3.el5 set to be updated</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">--> Processing Dependency: elfutils-libelf-devel-static-i386 = 0.137-3.el5 for package: elfutils-libelf-devel</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">---> Package unixODBC.i386 0:2.2.11-7.1 set to be updated</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">---> Package glibc-devel.i386 0:2.5-34 set to be updated</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">---> Package libstdc++-devel.i386 0:4.1.2-44.el5 set to be updated</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">---> Package gcc-c++.i386 0:4.1.2-44.el5 set to be updated</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">--> Running transaction check</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">---> Package libgomp.i386 0:4.3.2-7.el5 set to be updated</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">---> Package elfutils-libelf-devel-static.i386 0:0.137-3.el5 set to be updated</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">--> Finished Dependency Resolution</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;"><br />
</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">Dependencies Resolved</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;"><br />
</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">=================================================================================================================================================================================</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;"> Package Arch Version Repository Size</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">=================================================================================================================================================================================</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">Installing:</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;"> oracle-validated i386 1.0.0-18.el5 oracle-validated-1.0.0-18.el5.i386.rpm 15 k</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">Installing for dependencies:</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;"> compat-db i386 4.2.52-5.1 local 1.7 M</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;"> compat-gcc-34 i386 3.4.6-4 local 4.1 M</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;"> compat-gcc-34-c++ i386 3.4.6-4 local 11 M</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;"> elfutils-libelf-devel i386 0.137-3.el5 local 24 k</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;"> elfutils-libelf-devel-static i386 0.137-3.el5 local 66 k</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;"> gcc i386 4.1.2-44.el5 local 5.2 M</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;"> gcc-c++ i386 4.1.2-44.el5 local 3.4 M</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;"> gdb i386 6.8-27.el5 local 3.3 M</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;"> glibc-devel i386 2.5-34 local 2.0 M</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;"> glibc-headers i386 2.5-34 local 612 k</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;"> kernel-headers i386 2.6.18-128.el5 local 926 k</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;"> libXp i386 1.0.0-8.1.el5 local 22 k</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;"> libaio-devel i386 0.3.106-3.2 local 11 k</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;"> libgomp i386 4.3.2-7.el5 local 67 k</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;"> libstdc++-devel i386 4.1.2-44.el5 local 2.9 M</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;"> sysstat i386 7.0.2-3.el5 local 170 k</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;"> unixODBC i386 2.2.11-7.1 local 830 k</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;"> unixODBC-devel i386 2.2.11-7.1 local 743 k</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;"><br />
</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">Transaction Summary</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">=================================================================================================================================================================================</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">Install 19 Package(s)</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">Update 0 Package(s)</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">Remove 0 Package(s)</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;"><br />
</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">Total download size: 37 M</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">Is this ok [y/N]: y</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">Downloading Packages:</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">Total 2.6 GB/s | 37 MB 00:00</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">warning: rpmts_HdrFromFdno: Header V3 DSA signature: NOKEY, key ID 1e5e0159</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;"><br />
</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;"><br />
</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">Public key for oracle-validated-1.0.0-18.el5.i386.rpm is not installed</span></span><br />
<br />
<br />
The error indicates that yum cannot verify this rpm so it does not install it. When you update /etc/yum.conf with the gpgkey parameter the error goes away.Yasin Baskanhttp://www.blogger.com/profile/07642821755164831104noreply@blogger.com0tag:blogger.com,1999:blog-15574215.post-60338010177516931652010-11-09T10:48:00.000+02:002010-11-09T10:48:15.377+02:00How to use the Oracle Enterprise Linux installation media as the yum repositoryIf you are using Oracle Enterprise Linux (OEL) 5, the installation media comes with a yum repository on it. The repository is in the directory /media/Enterprise Linux dvd 20090127/Server/repodata for OEL 5.3 (the location may change).<br />
<br />
It is possible to use that repository when installing new packages or components locally without accessing a remote repository or without having the need to copy the rpms to a local directory. If you did a base installation the yum package is already installed, if not you need to install it first. After yum is in place edit /etc/yum.conf to insert lines related to the repository on the installation media.<br />
<br />
<br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">[main]</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">cachedir=/var/cache/yum</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">keepcache=0</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">debuglevel=2</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">logfile=/var/log/yum.log</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">distroverpkg=redhat-release</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">tolerant=1</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">exactarch=1</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">obsoletes=1</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">gpgcheck=1</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">plugins=1</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;"><br />
</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;"># Note: yum-RHN-plugin doesn't honor this.</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">metadata_expire=1h</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;"><br />
</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;"># Default.</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;"># installonly_limit = 3</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;"><br />
</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;"># PUT YOUR REPOS HERE OR IN separate files named file.repo</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;"># in /etc/yum.repos.d</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">[local]</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">name="media"</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">baseurl=file:///media/Enterprise%20Linux%20dvd%2020090127/Server</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">enabled=1</span></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">gpgkey=file:///media/Enterprise%20Linux%20dvd%2020090127/RPM-GPG-KEY-oracle</span></span><br />
<br />
You need to add only the last 5 lines, the other ones are already there.<br />
After this you can use yum to add packages or components when needed. An example to install the oracle-validated rpm from this repository will be in my next post.Yasin Baskanhttp://www.blogger.com/profile/07642821755164831104noreply@blogger.com0tag:blogger.com,1999:blog-15574215.post-75996579146261163272010-11-08T15:24:00.000+02:002010-11-08T15:24:44.652+02:00Starting a vm in VirtualBox hangsWhen trying to start a new vm in Oracle VM VirtualBox 3.2.10 it hot hung at progress 0%. There is no way to cancel the startup without killing the process from the host OS.<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://3.bp.blogspot.com/_yTgTgKuEjrc/TNf3cPYq3yI/AAAAAAAAM9E/oM4XqtUeSUQ/s1600/vbox_starting.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="341" src="http://3.bp.blogspot.com/_yTgTgKuEjrc/TNf3cPYq3yI/AAAAAAAAM9E/oM4XqtUeSUQ/s400/vbox_starting.jpg" width="400" /></a></div><br />
<br />
The first thing to look at is the VBox.log file which resides under C:\Documents and Settings\<username>\.VirtualBox\Machines\OELTest\Logs in Windows XP. Or you can access the log using the menu like below.</username><br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://3.bp.blogspot.com/_yTgTgKuEjrc/TNf4I14rCFI/AAAAAAAAM9I/puxPX0onGQQ/s1600/Vbox_log_menu.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="280" src="http://3.bp.blogspot.com/_yTgTgKuEjrc/TNf4I14rCFI/AAAAAAAAM9I/puxPX0onGQQ/s320/Vbox_log_menu.jpg" width="320" /></a></div><br />
I had these lines at the bottom of the log file.<br />
<br />
<br />
00:00:04.228 AIOMgr: Endpoint for file 'E:\vm\OELTest\OELTest_boot.vdi' (flags 000c0723) created successfully<br />
00:00:04.228 AIOMgr: I/O manager 0x3f3f898 encountered a critical error (rc=VERR_INVALID_PARAMETER) during operation. Falling back to failsafe mode. Expect reduced performance<br />
00:00:04.228 AIOMgr: Error happened in D:\tinderbox\win-3.2\src\VBox\VMM\PDMAsyncCompletionFileNormal.cpp:(1631){pdmacFileAioMgrNormal}<br />
00:00:04.228 AIOMgr: Please contact the product vendor<br />
<br />
This seems like the problem is related to the virtual disk I created as the root disk. I am using the SATA interface and in the virtual machine storage settings there is an option named "Use host I/O cache" which is unchecked in my case. Checking it and starting up the vm again resolves the issue.<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://4.bp.blogspot.com/_yTgTgKuEjrc/TNf5IwMccbI/AAAAAAAAM9M/4wCWMx4IRyM/s1600/vbox_disk_settings.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="292" src="http://4.bp.blogspot.com/_yTgTgKuEjrc/TNf5IwMccbI/AAAAAAAAM9M/4wCWMx4IRyM/s400/vbox_disk_settings.jpg" width="400" /></a></div><br />
<br />
<br />
There are lots of Google results when you search for messages in the log file but the issue has been explained in <a href="http://www.virtualbox.org/ticket/7363">http://www.virtualbox.org/ticket/7363</a>.Yasin Baskanhttp://www.blogger.com/profile/07642821755164831104noreply@blogger.com0tag:blogger.com,1999:blog-15574215.post-24841664385326648762009-06-19T16:21:00.003+03:002009-06-19T16:30:38.230+03:00opatch problem on WindowsThere is a <a href="https://metalink2.oracle.com/metalink/plsql/f?p=130:14:9322567782614068423::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,390761.1,1,1,1,helvetica">note in Metalink</a> that explains that on Windows having space characters in your ORACLE_HOME variable, the patch location or JDK location causes an error when running opatch. Yesterday I saw a strange problem that is similar to the above case.<br /><br />If your opatch directory contains space characters you get a strange error. Even if the above conditions were not present we got an error like this:<br /><br /><span style="font-style: italic;">C:\Documents and Settings\test\Desktop\OPatch>opatch lsinventory</span><br /><span style="font-style: italic;">Exception in thread "main" java.lang.NoClassDefFoundError: and</span><br /><br /><span style="font-style: italic;">OPatch failed with error code = 1</span><br /><br />Metalink returns no results for this error. This error is caused by the space characters in "Documents and Settings". When you move the opatch directory to another directory which does not contain space in its name opatch runs without this problem.<br /><br />Just a note to help in case someone gets the same error.<span style="font-style: italic;"></span><span style="font-style: italic;"></span>Yasin Baskanhttp://www.blogger.com/profile/07642821755164831104noreply@blogger.comtag:blogger.com,1999:blog-15574215.post-90933426697213455842009-06-19T13:25:00.002+03:002009-06-19T13:32:02.954+03:00DBFSYesterday I attended <a href="http://kevinclosson.wordpress.com/">Kevin Closson</a>'s Exadata technical deep dive webcast series part 4. It is now available to download <a href="http://kevinclosson.wordpress.com/2009/06/18/recorded-webcast-available-exadata-storage-server-technical-deep-dive-part-iv/">here</a>. In there he talks about DBFS which is a filesystem on top of the Oracle database which can store normal files like text files. DBFS is provided with Exadata and is used to store staging files for the ETL/ELT process. This looks very promising, he sites several tests he conducted and gives performance numbers too. Watch the webcast if you haven't yet.Yasin Baskanhttp://www.blogger.com/profile/07642821755164831104noreply@blogger.comtag:blogger.com,1999:blog-15574215.post-9189179907977282882009-06-09T09:39:00.002+03:002009-06-09T09:58:48.199+03:00Tablespace selection in interval partitioning11G brought <a href="http://download.oracle.com/docs/cd/B28359_01/server.111/b28279/chapter1.htm#FEATURENO07076">interval partitioning</a> which is a new partitioning method to ease the maintenance burden of adding new partitions manually. <a href="http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_7002.htm#BABJDACD">The interval partition clause</a> in the create table statement has an option to list tablespace names to be used for interval partitioning. <a href="http://download.oracle.com/docs/cd/B28359_01/server.111/b32024/part_avail.htm#sthref493">The documentation</a> states that the tablespaces in the list you provide are used in a round-robin manner for new partitions:<br /><br /><span style="font-style: italic;">Interval partitions are created in the provided list of tablespaces in a round-robin manner.</span><br /><br />This does not mean that any newly created partition will reside in the tablespace which is next on the list. The tablespaces may be skipped if partitions map to more than one interval. Here is a test case that shows how the list is used.<br /><pre><br />set lines 200<br />SQL> r<br /> 1 create table t(col1 date,col2 varchar2(100))<br /> 2 partition by range (col1)<br /> 3 interval(numtoyminterval(1,'MONTH')) store in (tbs1,tbs2,tbs3)<br /> 4* (PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2009', 'DD-MM-YYYY')) tablespace tbs1)<br /><br />Table created.<br /><br />SQL> r<br /> 1 select PARTITION_NAME,HIGH_VALUE,TABLESPACE_NAME<br /> 2* from user_Tab_partitions where table_name='T'<br /><br />PARTITION_NAME HIGH_VALUE TABLESPACE_NAME<br />------------------------------ -------------------------------------------------------------------------------- ------------------------------<br />P0 TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS1<br /></pre><br />The "store in" clause lists tablespaces tbs1, tbs2 and tbs3 to be used for interval partitioning. After the above create table command I now have one partition which resides in tbs1. Let's insert a row which needs to be inserted into a new partition and see which tablespace the partition will be created in.<br /><pre><br />SQL> insert into t values(to_date('15.01.2009','dd.mm.yyyy'),'jan');<br /><br />1 row created.<br /><br />SQL> commit;<br /><br />Commit complete.<br /><br />SQL> select PARTITION_NAME,HIGH_VALUE,TABLESPACE_NAME<br /> 2 from user_Tab_partitions where table_name='T';<br /><br />PARTITION_NAME HIGH_VALUE TABLESPACE_NAME<br />------------------------------ -------------------------------------------------------------------------------- ------------------------------<br />P0 TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS1<br />SYS_P61 TO_DATE(' 2009-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS2<br /></pre><br />The row I inserted maps to one interval, which is one month, it does not have a date value which is more than one month higher than the current maximum value. So the next tablespace, tbs2, is used for the new partition.<br /><pre><br />SQL> insert into t values(to_date('15.02.2009','dd.mm.yyyy'),'feb');<br /><br />1 row created.<br /><br />SQL> commit;<br /><br />Commit complete.<br /><br />SQL> select PARTITION_NAME,HIGH_VALUE,TABLESPACE_NAME<br /> 2 from user_Tab_partitions where table_name='T';<br /><br />PARTITION_NAME HIGH_VALUE TABLESPACE_NAME<br />------------------------------ -------------------------------------------------------------------------------- ------------------------------<br />SYS_P62 TO_DATE(' 2009-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS3<br />P0 TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS1<br />SYS_P61 TO_DATE(' 2009-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS2<br /></pre><br />Again I inserted a row for the next month and the partition is created in tbs3, which is the next one on the list.<br /><br />What happens if I insert a row with a date value that is more than one month after the current maximum partitioning key?<br /><pre><br />SQL> insert into t values(to_date('15.04.2009','dd.mm.yyyy'),'apr');<br /><br />1 row created.<br /><br />SQL> commit;<br /><br />Commit complete.<br /><br />SQL> select PARTITION_NAME,HIGH_VALUE,TABLESPACE_NAME<br /> 2 from user_Tab_partitions where table_name='T';<br /><br />PARTITION_NAME HIGH_VALUE TABLESPACE_NAME<br />------------------------------ -------------------------------------------------------------------------------- ------------------------------<br />SYS_P62 TO_DATE(' 2009-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS3<br />P0 TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS1<br />SYS_P61 TO_DATE(' 2009-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS2<br />SYS_P63 TO_DATE(' 2009-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS2<br /></pre><br />I skipped March and inserted a value for April. The current maximum key becomes May 1st, we do not see a partition with a maximum value of Apr 1st. The next tablespace on the list was tbs1 but we see that the new partition is on tbs2, not tbs1. Tbs1 would be used if I did not skip an interval when inserting rows.<br /><br />So, the tablespaces on the list are used in a round-robin manner but each is used for only one interval. If you skip intervals the tablespaces related to that interval are skipped too.<br /><br />This is something to keep in mind if you want to strictly decide which tablespace will hold which partition.Yasin Baskanhttp://www.blogger.com/profile/07642821755164831104noreply@blogger.comtag:blogger.com,1999:blog-15574215.post-80854945244333900832009-03-04T14:04:00.002+02:002009-03-04T14:08:52.089+02:00Different plans for a sql with the rule hintI was trying to find out why a query with the RULE hint produces different plans. I got stuck so I posted the problem to the <a href="http://forums.oracle.com/forums/forum.jspa?forumID=61&start=0">OTN database forum</a> and Randolf Geist provided <a href="http://forums.oracle.com/forums/thread.jspa?threadID=865799&tstart=0">a good answer</a> and starting point for it. A second eye on the problem can remove the mind blockage and clear your way.Yasin Baskanhttp://www.blogger.com/profile/07642821755164831104noreply@blogger.comtag:blogger.com,1999:blog-15574215.post-39625931408186534872009-02-23T15:28:00.002+02:002009-02-23T16:58:15.568+02:00Remote dependenciesWhen changing plsql code in a production system the dependencies between objects can cause some programs to be invalidated. Making sure all programs are valid before opening the system to users is very important for application availability. Oracle automatically compiles invalid programs on their first execution, but a successfull compilation may not be possible because the error may need code correction or the compilation may cause library cache locks when applications are running.<br /><br />Dependencies between plsql programs residing in the same database are easy to handle. When you compile a program the programs dependent on that one are invalidated right away and we can see the invalid programs and compile or correct them before opening the system to the users.<br /><br />If you have db links and if your plsql programs are dependent on programs residing in other databases you have a problem handling the invalidations. There is an initialization parameter named <a href="http://download.oracle.com/docs/cd/B10501_01/server.920/a96536/ch1175.htm#REFRN10182">remote_dependencies_mode</a> that handles this dependency management. If it is set to TIMESTAMP the timestamp of the local program is compared to that of the remote program. If the remote program's timestamp is more recent than the local program, the local program is invalidated in the first run. If the parameter is set to SIGNATURE the remote program's signature (number and types of parameters, subprogram names, etc...) is checked, if there has been a change the local program is invalidated in the first run.<br /><br />The problem here is; if you change the remote program's signature or timestamp you cannot see that the local program is invalidated because it will wait for the first execution to be invalidated. If you open the system to the users before correcting this you may face a serious application problem leading to downtime.<br /><br />Here is a simple test case to see the problem.<br /><br /><br /><br /><br />I start by creating a plsql package in the database TEST.<br /><br /><pre>SQL> create package test_pack as<br /> 2 procedure test_proc(a number);<br /> 3 end;<br /> 4 /<br /><br />Package created.<br /><br />SQL> create package body test_pack as<br /> 2 procedure test_proc(a number) is<br /> 3 begin<br /> 4 null;<br /> 5 end;<br /> 6 end;<br /> 7 /<br /><br />Package body created.<br /></pre><br /><br />On another database let's create a database link pointing to the remote database TEST, create a synonym for the remote package and create a local package calling the remote one.<br /><br /><pre>SQL> create database link test.world connect to yas identified by yas using 'TEST';<br /><br />Database link created.<br /><br />SQL> select * from dual@yas.world;<br /><br />D<br />-<br />X<br /><br />SQL> create synonym test_pack for test_pack@yasbs.world;<br /><br />Synonym created.<br /><br />SQL> create package local_pack as<br /> 2 procedure local_test(a number);<br /> 3 end;<br /> 4 /<br /><br />Package created.<br /><br />SQL> r<br /> 1 create or replace package body local_pack as<br /> 2 procedure local_test(a number) is<br /> 3 begin<br /> 4 test_pack.test_proc(1);<br /> 5 end;<br /> 6* end;<br /><br />Package body created.<br /></pre><br /><br />If we look at the status of this local package we see that it is valid.<br /><br /><pre><br />SQL> col object_name format a30<br />SQL> r<br /> 1* select status,object_name,object_type from user_objects where object_name='LOCAL_PACK'<br /><br />STATUS OBJECT_NAME OBJECT_TYPE<br />------- ------------------------------ ------------------<br />VALID LOCAL_PACK PACKAGE<br />VALID LOCAL_PACK PACKAGE BODY<br /></pre><br /><br />We can execute it without any problems.<br /><br /><pre><br />SQL> exec LOCAL_PACK.local_test(1);<br /><br />PL/SQL procedure successfully completed.<br /></pre><br /><br />Now, let's change the remote package code including the package specification.<br /><br /><pre><br />SQL> create or replace package test_pack as<br /> 2 procedure test_proc(b number,a number default 1);<br /> 3 end;<br /> 4 /<br /><br />Package created.<br /><br />SQL> create or replace package body test_pack as<br /> 2 procedure test_proc(b number,a number default 1) is<br /> 3 begin<br /> 4 null;<br /> 5 end;<br /> 6 end;<br /> 7 /<br /><br />Package body created.<br /></pre><br /><br />When we look at the local package we see its status as valid.<br /><br /><pre><br />SQL> r<br /> 1* select status,object_name,object_type from user_objects where object_name='LOCAL_PACK'<br /><br />STATUS OBJECT_NAME OBJECT_TYPE<br />------- ------------------------------ ------------------<br />VALID LOCAL_PACK PACKAGE<br />VALID LOCAL_PACK PACKAGE BODY<br /></pre><br /><br />But when it is executed we get an error.<br /><br /><pre><br />SQL> exec LOCAL_PACK.local_test(1);<br />BEGIN LOCAL_PACK.local_test(1); END;<br /><br />*<br />ERROR at line 1:<br />ORA-04068: existing state of packages has been discarded<br />ORA-04062: signature of package "YASBS.TEST_PACK" has been changed<br />ORA-06512: at "YASBS.LOCAL_PACK", line 4<br />ORA-06512: at line 1<br /><br /><br />SQL> select status,object_name,object_type from user_objects where object_name='LOCAL_PACK';<br /><br />STATUS OBJECT_NAME OBJECT_TYPE<br />------- ------------------------------ ------------------<br />VALID LOCAL_PACK PACKAGE<br />INVALID LOCAL_PACK PACKAGE BODY<br /><br />SQL> exec LOCAL_PACK.local_test(1);<br /><br />PL/SQL procedure successfully completed.<br /><br />SQL> select status,object_name,object_type from user_objects where object_name='LOCAL_PACK';<br /><br />STATUS OBJECT_NAME OBJECT_TYPE<br />------- ------------------------------ ------------------<br />VALID LOCAL_PACK PACKAGE<br />VALID LOCAL_PACK PACKAGE BODY<br /></pre><br /><br />The second execution compiles the package and returns the status to valid.<br /><br />How can we know beforehand that the local program will be invalidated on the first execution? The only way I can think of is to check the dependencies across all databases involved. By collecting all rows from dba_dependencies from all databases we can see that when the remote program is changed the programs on other databases that use this remote program will be invalidated when they are executed. Then we can compile these programs and see if they compile without errors.<br /><br />Database links may be very annoying sometimes, this case is just one of them.Yasin Baskanhttp://www.blogger.com/profile/07642821755164831104noreply@blogger.comtag:blogger.com,1999:blog-15574215.post-16004347128974613882008-08-06T09:43:00.002+03:002008-08-06T10:18:32.172+03:00Index block split bug in 9iIn his famous <a href="http://richardfoote.files.wordpress.com/2007/12/index-internals-rebuilding-the-truth.pdf">index internals presentation</a> <a href="http://richardfoote.wordpress.com/">Richard Foote</a> mentions a bug in 9i about index block splits when rows are inserted in the order of the index columns. Depending on when you commit your inserts the index size changes dramatically.<br /><br />While I was trying to find out why a 3-column primary key index takes more space than its table I recalled that bug and it turned out that was the reason of the space issue. The related bug is 3196414 and it is fixed in 10G.<br /><br />Here is the test case Richard presents in his paper.<br /><pre><br /><span style="font-size:90;"><span style="font-family:courier new;">SQL> create table t(id number,value varchar2(10));</span><br /><br /><span style="font-family:courier new;">Table created.</span><br /><br /><span style="font-family:courier new;">SQL> create index t_ind on t(id);</span><br /><br /><span style="font-family:courier new;">Index created.</span><br /><br /><span style="font-family:courier new;">SQL> @mystat split</span><br /><br /><span style="font-family:courier new;">NAME VALUE</span><br /><span style="font-family:courier new;">------------------------------ ----------</span><br /><span style="font-family:courier new;">leaf node splits 0</span><br /><span style="font-family:courier new;">leaf node 90-10 splits 0</span><br /><span style="font-family:courier new;">branch node splits 0</span><br /><br /><span style="font-family:courier new;">SQL> ed</span><br /><span style="font-family:courier new;">Wrote file afiedt.buf</span><br /><br /><span style="font-family:courier new;"> 1 begin</span><br /><span style="font-family:courier new;"> 2 for i in 1..10000 loop</span><br /><span style="font-family:courier new;"> 3 insert into t values(i,'test');</span><br /><span style="font-family:courier new;"> 4 commit;</span><br /><span style="font-family:courier new;"> 5 end loop;</span><br /><span style="font-family:courier new;"> 6* end;</span><br /><span style="font-family:courier new;">SQL> r</span><br /><span style="font-family:courier new;"> 1 begin</span><br /><span style="font-family:courier new;"> 2 for i in 1..10000 loop</span><br /><span style="font-family:courier new;"> 3 insert into t values(i,'test');</span><br /><span style="font-family:courier new;"> 4 commit;</span><br /><span style="font-family:courier new;"> 5 end loop;</span><br /><span style="font-family:courier new;"> 6* end;</span><br /><br /><span style="font-family:courier new;">PL/SQL procedure successfully completed.</span><br /><br /><span style="font-family:courier new;">SQL> @mystat2 split</span><br /><br /><span style="font-family:courier new;">NAME VALUE DIFF</span><br /><span style="font-family:courier new;">------------------------------ ---------- ----------</span><br /><span style="font-family:courier new;">leaf node splits 35 35</span><br /><span style="font-family:courier new;">leaf node 90-10 splits 0 0</span><br /><span style="font-family:courier new;">branch node splits 0 0</span><br /><br /><span style="font-family:courier new;">SQL> analyze index t_ind validate structure;</span><br /><br /><span style="font-family:courier new;">Index analyzed.</span><br /><br /><span style="font-family:courier new;">SQL> select lf_blks, pct_used from index_stats;</span><br /><br /><span style="font-family:courier new;"> LF_BLKS PCT_USED</span><br /><span style="font-family:courier new;">---------- ----------</span><br /><span style="font-family:courier new;"> 36 51</span><br /><br /><span style="font-family:courier new;">SQL> drop table t;</span><br /><br /><span style="font-family:courier new;">Table dropped.</span></span><br /></pre><br /><br />I am trying to insert the rows in the order of the primary key column, so what I expect to see is that when an index block fills there will be a 90-10 split and the index will grow in size. But as the number of leaf block splits show there are 35 block splits and none of them are 90-10 splits meaning all are 50-50 block splits. I have 36 leaf blocks but half of each one is empty.<br /><br />If we try the same inserts but commit after the loop the result changes.<br /><pre><br /><span style="font-size:90;"><span style="font-family:courier new;">SQL> create table t(id number,value varchar2(10));</span><br /><br /><span style="font-family:courier new;">Table created.</span><br /><br /><span style="font-family:courier new;">SQL> create index t_ind on t(id);</span><br /><br /><span style="font-family:courier new;">Index created.</span><br /><br /><span style="font-family:courier new;">SQL> @mystat split</span><br /><br /><span style="font-family:courier new;">NAME VALUE</span><br /><span style="font-family:courier new;">------------------------------ ----------</span><br /><span style="font-family:courier new;">leaf node splits 35</span><br /><span style="font-family:courier new;">leaf node 90-10 splits 0</span><br /><span style="font-family:courier new;">branch node splits 0</span><br /><br /><span style="font-family:courier new;">SQL> ed</span><br /><span style="font-family:courier new;">Wrote file afiedt.buf</span><br /><br /><span style="font-family:courier new;"> 1 begin</span><br /><span style="font-family:courier new;"> 2 for i in 1..10000 loop</span><br /><span style="font-family:courier new;"> 3 insert into t values(i,'test');</span><br /><span style="font-family:courier new;"> 4 end loop;</span><br /><span style="font-family:courier new;"> 5 commit;</span><br /><span style="font-family:courier new;"> 6* end;</span><br /><span style="font-family:courier new;">SQL> r</span><br /><span style="font-family:courier new;"> 1 begin</span><br /><span style="font-family:courier new;"> 2 for i in 1..10000 loop</span><br /><span style="font-family:courier new;"> 3 insert into t values(i,'test');</span><br /><span style="font-family:courier new;"> 4 end loop;</span><br /><span style="font-family:courier new;"> 5 commit;</span><br /><span style="font-family:courier new;"> 6* end;</span><br /><br /><span style="font-family:courier new;">PL/SQL procedure successfully completed.</span><br /><br /><span style="font-family:courier new;">SQL> @mystat2 split</span><br /><br /><span style="font-family:courier new;">NAME VALUE DIFF</span><br /><span style="font-family:courier new;">------------------------------ ---------- ----------</span><br /><span style="font-family:courier new;">leaf node splits 53 53</span><br /><span style="font-family:courier new;">leaf node 90-10 splits 18 18</span><br /><span style="font-family:courier new;">branch node splits 0 0</span><br /><br /><span style="font-family:courier new;">SQL> analyze index t_ind validate structure;</span><br /><br /><span style="font-family:courier new;">Index analyzed.</span><br /><br /><span style="font-family:courier new;">SQL> select lf_blks, pct_used from index_stats;</span><br /><br /><span style="font-family:courier new;"> LF_BLKS PCT_USED</span><br /><span style="font-family:courier new;">---------- ----------</span><br /><span style="font-family:courier new;"> 19 94</span></span><br /></pre><br />In this case we see that there have been 18 block splits and all were 90-10 splits as expected. We have 19 leaf blocks and all are nearly full. Depending on where the commit is we can get an index twice the size it has to be. When I ran the same test in 10G it did not matter where the commit was. I got 19 leaf blocks in both cases.<br /><br />I did not test if this problem happens when several sessions insert a single row and commit just like in an OLTP system but I think it is likely because we have indexes showing this behavior in OLTP systems.Yasin Baskanhttp://www.blogger.com/profile/07642821755164831104noreply@blogger.comtag:blogger.com,1999:blog-15574215.post-4451490499720741082008-08-01T11:25:00.004+03:002008-08-01T11:44:36.598+03:00OTN members, don't change your e-mail account!I am regular user of <a href="http://www.oracle.com/technology/index.html">OTN</a> and its <a href="http://forums.oracle.com/forums/forum.jspa?forumID=61">forums</a>. Last week I was trying to login to OTN from a public computer and I got the "invalid login" error everytime I tried. I was sure I was typing my password correct but I could not get in anyway. So, I tried to get my password reset and sent to my e-mail address. Then I remembered that the e-mail address I used to register for OTN was from my previous employer meaning I did not have access to it anymore. As OTN does not allow changing the registration e-mail address I was stuck. I send a request from OTN to get my password delivered to my current e-mail address. Here is the reply I got:<br /><pre style="font-style: italic;"><span><span style="font-family:Times New Roman;font-size:130%;">Resolution: Oracle's membership management system does not currently support<br />the editing of the email address or username in your membership profile.<br />(It will support this capability in a future release.)<br />Please create a new account with the new email address you wish to use. However,<br />it is possible to change the email at which you receive<br />Discussion Forum "watch" emails (see "Your Control Panel" when logged in).</span></span></pre>They tell me to create a new user and forget about my history, watch list, everything. What a user centric approach this is.<br /><br />If you are an OTN member do not lose your password and your e-mail account at the same time, you will not find anybody from OTN who is willing to solve your problem and help you to recover your password.<br /><br />I am used to bad behavior and unwillingness to solve problems in Metalink, now I get the same behavior in OTN. Whatever, just wanted to let you know about it.Yasin Baskanhttp://www.blogger.com/profile/07642821755164831104noreply@blogger.comtag:blogger.com,1999:blog-15574215.post-33872981649143383572008-04-17T10:21:00.003+03:002008-04-17T11:33:50.810+03:00Materialized view refresh change in 10G and sql tracingComplete refresh of a single materialized view used to do a truncate and insert on the mview table until 10G. Starting with 10G the refresh does a delete and insert on the mview table. This guarantees that the table is never empty in case of an error, the refresh process became an atomic operation.<br /><br />There is another difference between 9.2 and 10G in the refresh process, which I have realized when trying to find out why a DDL trigger prevented a refresh operation. In 9.2 the refresh process runs an ALTER SUMMARY statement against the mview while in 10G it does not.<br /><br />We have a DDL trigger in some of the test environments (all 9.2) for preventing some operations. The trigger checks the type of the DDL and allows or disallows it based on some conditions. After this trigger was put in place some developers started to complain about some periodic mview refresh operations failing a few weeks ago. I knew it was not because of the TRUNCATE because the trigger allowed truncate operations.<br /><br />So I enabled sql tracing and found out what the refresh process was doing. Here is a simplified test case for it in 10G.<br /><pre><br /><span style="font-family:courier new;">YAS@10G>create table master as select * from all_objects where rownum<=5;</span><br /><br /><span style="font-family:courier new;">Table created.</span><br /><br /><span style="font-family:courier new;">YAS@10G>alter table master add primary key (owner,object_name,object_type);</span><br /><br /><span style="font-family:courier new;">Table altered.</span><br /><br /><span style="font-family:courier new;">YAS@10G>create materialized view mview as select * from master;</span><br /><br /><span style="font-family:courier new;">Materialized view created.</span><br /><br /><span style="font-family:courier new;">YAS@10G>exec dbms_mview.refresh('MVIEW');</span><br /><br /><span style="font-family:courier new;">PL/SQL procedure successfully completed.</span><br /><br /><span style="font-family:courier new;">YAS@10G>create or replace trigger ddl_trigger before ddl on schema</span><br /><span style="font-family:courier new;"> 2 begin</span><br /><span style="font-family:courier new;"> 3 if ora_sysevent<>'TRUNCATE' then</span><br /><span style="font-family:courier new;"> 4 raise_application_error(-20001,'DDL NOT ALLOWED');</span><br /><span style="font-family:courier new;"> 5 end if;</span><br /><span style="font-family:courier new;"> 6 end;</span><br /><span style="font-family:courier new;"> 7 /</span><br /><br /><span style="font-family:courier new;">Trigger created.</span><br /><br /><span style="font-family:courier new;">YAS@10G>exec dbms_mview.refresh('MVIEW');</span><br /><br /><span style="font-family:courier new;">PL/SQL procedure successfully completed.</span><br /></pre><br />The refresh was successful in 10G even after the DDL trigger was active. In 9.2 it errors out after the DDL trigger is enabled.<br /><pre><br /><span style="font-family:courier new;">SQL> exec dbms_mview.refresh('MVIEW');</span><br /><span style="font-family:courier new;">BEGIN dbms_mview.refresh('MVIEW'); END;</span><br /><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-04045: errors during recompilation/revalidation of YAS.MVIEW</span><br /><span style="font-family:courier new;">ORA-20001: DDL NOT ALLOWED</span><br /><span style="font-family:courier new;">ORA-06512: at "SYS.DBMS_SNAPSHOT", line 820</span><br /><span style="font-family:courier new;">ORA-06512: at "SYS.DBMS_SNAPSHOT", line 877</span><br /><span style="font-family:courier new;">ORA-06512: at "SYS.DBMS_SNAPSHOT", line 858</span><br /><span style="font-family:courier new;">ORA-06512: at line 1</span><br /></pre><br />After enabling sql trace and running the refresh again, the trace file shows the problem statement.<br /><pre><br /><span style="font-family:courier new;">SQL> alter session set sql_trace=true;</span><br /><br /><span style="font-family:courier new;">Session altered.</span><br /><br /><span style="font-family:courier new;">SQL> exec dbms_mview.refresh('MVIEW');</span><br /><span style="font-family:courier new;">BEGIN dbms_mview.refresh('MVIEW'); END;</span><br /><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-04045: errors during recompilation/revalidation of YAS.MVIEW</span><br /><span style="font-family:courier new;">ORA-20001: DDL NOT ALLOWED</span><br /><span style="font-family:courier new;">ORA-06512: at "SYS.DBMS_SNAPSHOT", line 820</span><br /><span style="font-family:courier new;">ORA-06512: at "SYS.DBMS_SNAPSHOT", line 877</span><br /><span style="font-family:courier new;">ORA-06512: at "SYS.DBMS_SNAPSHOT", line 858</span><br /><span style="font-family:courier new;">ORA-06512: at line 1</span><br /></pre><br />The last statement in the trace file is this:<br /><pre><br /><span style="font-family:courier new;">ALTER SUMMARY "YAS"."MVIEW" COMPILE</span><br /></pre><br />So, in 9.2 refresh process runs an ALTER SUMMARY command against the mview. After changing the trigger to allow DDL operations on SUMMARY objects I could refresh the mview without errors.<br /><pre><br /><span style="font-family:courier new;">SQL> r</span><br /><span style="font-family:courier new;"> 1 create or replace trigger ddl_trigger before ddl on schema</span><br /><span style="font-family:courier new;"> 2 begin</span><br /><span style="font-family:courier new;"> 3 if ora_sysevent<>'TRUNCATE' and ora_dict_obj_type<>'SUMMARY' then</span><br /><span style="font-family:courier new;"> 4 raise_application_error(-20001,'DDL NOT ALLOWED');</span><br /><span style="font-family:courier new;"> 5 end if;</span><br /><span style="font-family:courier new;"> 6* end;</span><br /><br /><span style="font-family:courier new;">Trigger created.</span><br /><br /><span style="font-family:courier new;">SQL> exec dbms_mview.refresh('MVIEW');</span><br /><br /><span style="font-family:courier new;">PL/SQL procedure successfully completed.</span><br /></pre>Tonguc Yilmaz had <a href="http://tonguc.wordpress.com/2008/04/16/event-10046-as-an-asprin/">a recent post</a> about using 10046 for purposes other than performance tuning, like finding out what statements a procedure runs. I find uses for sql tracing nearly everyday and the above case is one of them. When you are not able to understand why an error happens, it is sometimes very useful to turn on sql tracing and examine the trace file. Errors related to specific bind values, errors raised from a "when others" block (you know <a href="http://tkyte.blogspot.com/2008/01/why-do-people-do-this.html">we do not like "when others"</a>, right?) are a couple of things you can dig and analyze with sql tracing.Yasin Baskanhttp://www.blogger.com/profile/07642821755164831104noreply@blogger.comtag:blogger.com,1999:blog-15574215.post-16147366859478462682008-04-11T17:21:00.003+03:002008-04-11T20:26:03.627+03:00DisqusLast week I read a <a href="http://www.nbrightside.com/blog/2008/04/02/resurrection-of-disqus-comments">post in Andy C's blog</a> about the service called <a href="http://disqus.com/">Disqus</a>. It is a service to keep track of comments in blogs. Later he made <a href="http://www.nbrightside.com/blog/2008/04/09/25-reasons-you-should-use-disqus">another post about it</a>.<br /><br />I have been looking for a solution to keep track of blog comments, both mine and other people's. Not all blogs have the option to subscribe to the comments, when you comment on a post you need to check later if someone commented further. I want a central repository where I can see all my comments on a post, all comments made by others on the same post and all comments made on my own blog.<br /><br />I tried <a href="http://www.cocomment.com/">Cocomment</a> before but I was not satisfied with it. So I decided to give Disqus a try and enabled it on this blog. <a href="http://laurentschneider.com/">Laurent Schneider</a> decided to try it too.<br /><br />Then Andy made <a href="http://www.nbrightside.com/blog/2008/04/11/the-thorny-issue-of-blog-comment-ownership#comment-324547">another post</a> about the concerns of some people about Disqus (one being <a href="http://www.oracle-base.com/blog/">Tim Hall</a>). Their concerns make sense. Who owns the comments one makes, the commenter or the blog owner? Is it sensible to store the comments to your blog not in your blog database, but elsewhere? What if Disqus is not there next year, what if Disqus is inaccessible for some time? Is it possible to export the comments from Disqus and import them back to the blog?<br /><br />Some of these may be irrelevant if you are using a public blogging service, like Blogger, because it means you are already storing your posts and comments somewhere else. The question "What if Blogger is not there next year?" comes to mind for example.<br /><br />A solution suggested by Tim Hall is to dual post the comments. The comments will be in the blog and on Disqus also, this need the blogging service to provide a way to do it.<br /><br />Another solution can be a strong export-import utility in Disqus. That way you can export the comments and put them back to the blog whenever you want. Disqus currently has an export utility but as far as I have read it is not reliable for now.<br /><br />While I agree with these concerns I liked what Disqus provides. The one-stop page for all comments, the threading of comments, being able to follow other users are primary features I like. So, I will stick with it, at least for now.Yasin Baskanhttp://www.blogger.com/profile/07642821755164831104noreply@blogger.comtag:blogger.com,1999:blog-15574215.post-7256837349899025162008-04-11T10:58:00.004+03:002008-04-11T12:29:18.142+03:00Collect in 10GCollections can be a great help in speeding up the PL/SQL programs. <a href="http://oracletoday.blogspot.com/2005/11/bulk-collect_15.html">By using bulk collect operations</a> it is possible to get great performance improvements.<br /><br />In 9.2 we needed to use the <a href="http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96624/13_elems45.htm#36089">bulk collect</a> clause to fetch rows into a collection. 10G brings a new function called <a href="http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/functions020.htm#i1271564">COLLECT</a>, which takes a column as a parameter and returns a nested table containing the column values. Using this we can get the data into a collection without using bulk collect.<br /><br />Here is a very simple demo of this new function.<br /><pre><br /><span style="font-family:courier new;">YAS@10G>create table t as select * from all_objects;<br /><br />Table created.<br /><br />YAS@10G>create or replace type name_type as table of varchar2(30);<br />2 /<br /><br />Type created.<br /><br />YAS@10G>set serveroutput on<br /><br />YAS@10G>r<br />1 declare<br />2 v_names name_type;<br />3 begin<br />4 select cast(collect(object_name) as name_type) into v_names from t;<br />5 dbms_output.put_line(v_names.count);<br />6 dbms_output.put_line(v_names(1));<br />7* end;<br />42268<br />ICOL$<br /><br />PL/SQL procedure successfully completed.</span><span style="font-family:courier new;"></span></pre><br /><br />One difference between this and bulk collect is, since this a sql function we need a sql type for this, it cannot be used with local PL/SQL types.<br /><pre><br /><span style="font-family:courier new;">YAS@10G>r</span><br /><span style="font-family:courier new;"> 1 declare</span><br /><span style="font-family:courier new;"> 2 type name_type is table of varchar2(30);</span><br /><span style="font-family:courier new;"> 3 v_names name_type;</span><br /><span style="font-family:courier new;"> 4 begin</span><br /><span style="font-family:courier new;"> 5 select collect(object_name) into v_names from t;</span><br /><span style="font-family:courier new;"> 6* end;</span><br /><span style="font-family:courier new;"> select collect(object_name) into v_names from t;</span><br /><span style="font-family:courier new;"> *</span><br /><span style="font-family:courier new;">ERROR at line 5:</span><br /><span style="font-family:courier new;">ORA-06550: line 5, column 35:</span><br /><span style="font-family:courier new;">PLS-00642: local collection types not allowed in SQL statements</span><br /><span style="font-family:courier new;">ORA-06550: line 5, column 9:</span><br /><span style="font-family:courier new;">PL/SQL: ORA-00932: inconsistent datatypes: expected CHAR got -</span><br /><span style="font-family:courier new;">ORA-06550: line 5, column 2:</span><br /><span style="font-family:courier new;">PL/SQL: SQL Statement ignored</span></pre><br /><br />Another difference, and a more important one, is the performance difference between these two constructs. Using <a href="http://asktom.oracle.com/tkyte/runstats.html">Tom Kyte's runstats package</a>, I did a test to compare the two. I ran the test several times, the results were similar.<br /><pre><br /><span style="font-family:courier new;">YAS@10G>r</span><br /><span style="font-family:courier new;"> 1 declare</span><br /><span style="font-family:courier new;"> 2 v_names name_type;</span><br /><span style="font-family:courier new;"> 3 begin</span><br /><span style="font-family:courier new;"> 4 runStats_pkg.rs_start;</span><br /><span style="font-family:courier new;"> 5</span><br /><span style="font-family:courier new;"> 6 for i in 1..1000 loop</span><br /><span style="font-family:courier new;"> 7 select object_name bulk collect into v_names from t;</span><br /><span style="font-family:courier new;"> 8 end loop;</span><br /><span style="font-family:courier new;"> 9</span><br /><span style="font-family:courier new;"> 10 runStats_pkg.rs_middle;</span><br /><span style="font-family:courier new;"> 11</span><br /><span style="font-family:courier new;"> 12 for i in 1..1000 loop</span><br /><span style="font-family:courier new;"> 13 select cast(collect(object_name) as name_type) into v_names from t;</span><br /><span style="font-family:courier new;"> 14 end loop;</span><br /><span style="font-family:courier new;"> 15</span><br /><span style="font-family:courier new;"> 16 runStats_pkg.rs_stop;</span><br /><span style="font-family:courier new;"> 17</span><br /><span style="font-family:courier new;"> 18* end;</span><br /><span style="font-family:courier new;">Run1 ran in 1329 hsecs</span><br /><span style="font-family:courier new;">Run2 ran in 4060 hsecs</span><br /><span style="font-family:courier new;">run 1 ran in 32.73% of the time</span><br /><br /><span style="font-family:courier new;">Name Run1 Run2 Diff</span><br /><span style="font-family:courier new;">LATCH.qmn state object latch 0 1 1</span><br /><span style="font-family:courier new;">STAT...redo entries 9 10 1</span><br /><span style="font-family:courier new;">LATCH.JS slv state obj latch 1 0 -1</span><br /><span style="font-family:courier new;">LATCH.qmn task queue latch 5 6 1</span><br /><span style="font-family:courier new;">LATCH.transaction branch alloc 0 1 1</span><br /><span style="font-family:courier new;">LATCH.sort extent pool 0 1 1</span><br /><span style="font-family:courier new;">LATCH.resmgr:actses change gro 1 0 -1</span><br /><span style="font-family:courier new;">LATCH.ncodef allocation latch 0 1 1</span><br /><span style="font-family:courier new;">LATCH.slave class 0 1 1</span><br /><span style="font-family:courier new;">LATCH.archive control 0 1 1</span><br /><span style="font-family:courier new;">LATCH.FAL subheap alocation 0 1 1</span><br /><span style="font-family:courier new;">LATCH.FAL request queue 0 1 1</span><br /><span style="font-family:courier new;">STAT...heap block compress 6 5 -1</span><br /><span style="font-family:courier new;">LATCH.session switching 0 1 1</span><br /><span style="font-family:courier new;">LATCH.ksuosstats global area 1 2 1</span><br /><span style="font-family:courier new;">LATCH.event group latch 1 0 -1</span><br /><span style="font-family:courier new;">LATCH.threshold alerts latch 0 1 1</span><br /><span style="font-family:courier new;">LATCH.list of block allocation 2 0 -2</span><br /><span style="font-family:courier new;">LATCH.transaction allocation 2 0 -2</span><br /><span style="font-family:courier new;">LATCH.dummy allocation 3 1 -2</span><br /><span style="font-family:courier new;">LATCH.user lock 2 0 -2</span><br /><span style="font-family:courier new;">LATCH.Consistent RBA 4 2 -2</span><br /><span style="font-family:courier new;">STAT...calls to kcmgcs 4 6 2</span><br /><span style="font-family:courier new;">STAT...active txn count during 4 6 2</span><br /><span style="font-family:courier new;">STAT...cleanout - number of kt 4 6 2</span><br /><span style="font-family:courier new;">STAT...consistent gets 587,009 587,011 2</span><br /><span style="font-family:courier new;">STAT...consistent gets from ca 587,009 587,011 2</span><br /><span style="font-family:courier new;">STAT...consistent gets - exami 4 6 2</span><br /><span style="font-family:courier new;">LATCH.resmgr:free threads list 3 0 -3</span><br /><span style="font-family:courier new;">LATCH.PL/SQL warning settings 3 0 -3</span><br /><span style="font-family:courier new;">LATCH.OS process 6 3 -3</span><br /><span style="font-family:courier new;">LATCH.compile environment latc 3 0 -3</span><br /><span style="font-family:courier new;">LATCH.slave class create 0 3 3</span><br /><span style="font-family:courier new;">LATCH.resmgr:actses active lis 3 0 -3</span><br /><span style="font-family:courier new;">LATCH.cache buffers lru chain 0 3 3</span><br /><span style="font-family:courier new;">LATCH.OS process allocation 10 14 4</span><br /><span style="font-family:courier new;">LATCH.session state list latch 4 0 -4</span><br /><span style="font-family:courier new;">LATCH.redo allocation 50 46 -4</span><br /><span style="font-family:courier new;">STAT...consistent changes 17 22 5</span><br /><span style="font-family:courier new;">LATCH.resmgr group change latc 5 0 -5</span><br /><span style="font-family:courier new;">STAT...db block gets 17 22 5</span><br /><span style="font-family:courier new;">STAT...db block gets from cach 17 22 5</span><br /><span style="font-family:courier new;">LATCH.library cache pin alloca 6 0 -6</span><br /><span style="font-family:courier new;">STAT...db block changes 26 32 6</span><br /><span style="font-family:courier new;">STAT...session logical reads 587,026 587,033 7</span><br /><span style="font-family:courier new;">LATCH.In memory undo latch 23 16 -7</span><br /><span style="font-family:courier new;">LATCH.session idle bit 10 3 -7</span><br /><span style="font-family:courier new;">LATCH.mostly latch-free SCN 6 14 8</span><br /><span style="font-family:courier new;">LATCH.KMG MMAN ready and start 5 13 8</span><br /><span style="font-family:courier new;">LATCH.lgwr LWN SCN 6 14 8</span><br /><span style="font-family:courier new;">LATCH.simulator hash latch 34,010 34,001 -9</span><br /><span style="font-family:courier new;">LATCH.simulator lru latch 34,010 34,001 -9</span><br /><span style="font-family:courier new;">LATCH.session timer 5 14 9</span><br /><span style="font-family:courier new;">LATCH.dml lock allocation 10 1 -9</span><br /><span style="font-family:courier new;">LATCH.undo global data 20 10 -10</span><br /><span style="font-family:courier new;">LATCH.post/wait queue 10 0 -10</span><br /><span style="font-family:courier new;">LATCH.active checkpoint queue 4 15 11</span><br /><span style="font-family:courier new;">LATCH.session allocation 13 2 -11</span><br /><span style="font-family:courier new;">LATCH.archive process latch 4 15 11</span><br /><span style="font-family:courier new;">LATCH.library cache lock alloc 16 0 -16</span><br /><span style="font-family:courier new;">LATCH.object queue header oper 8 32 24</span><br /><span style="font-family:courier new;">LATCH.client/application info 25 0 -25</span><br /><span style="font-family:courier new;">LATCH.redo writing 24 51 27</span><br /><span style="font-family:courier new;">LATCH.active service list 37 81 44</span><br /><span style="font-family:courier new;">STAT...undo change vector size 2,124 2,200 76</span><br /><span style="font-family:courier new;">LATCH.channel operations paren 60 197 137</span><br /><span style="font-family:courier new;">LATCH.cache buffers chains 1,174,359 1,174,189 -170</span><br /><span style="font-family:courier new;">LATCH.JS queue state obj latch 108 288 180</span><br /><span style="font-family:courier new;">LATCH.messages 108 291 183</span><br /><span style="font-family:courier new;">STAT...redo size 2,772 2,964 192</span><br /><span style="font-family:courier new;">LATCH.checkpoint queue latch 80 282 202</span><br /><span style="font-family:courier new;">LATCH.enqueue hash chains 269 652 383</span><br /><span style="font-family:courier new;">LATCH.enqueues 248 645 397</span><br /><span style="font-family:courier new;">LATCH.SQL memory manager worka 276 944 668</span><br /><span style="font-family:courier new;">LATCH.shared pool 42 1,029 987</span><br /><span style="font-family:courier new;">LATCH.library cache lock 170 2,018 1,848</span><br /><span style="font-family:courier new;">LATCH.library cache pin 2,130 4,064 1,934</span><br /><span style="font-family:courier new;">STAT...Elapsed Time 1,330 4,061 2,731</span><br /><span style="font-family:courier new;">STAT...CPU used by this sessio 1,334 4,083 2,749</span><br /><span style="font-family:courier new;">STAT...recursive cpu usage 1,268 4,064 2,796</span><br /><span style="font-family:courier new;">LATCH.library cache 2,264 5,074 2,810</span><br /><span style="font-family:courier new;">LATCH.row cache objects 49 9,015 8,966</span><br /><span style="font-family:courier new;">STAT...session pga memory 2,097,152 1,441,792 -655,360</span><br /><br /><span style="font-family:courier new;">Run1 latches total versus runs -- difference and pct</span><br /><span style="font-family:courier new;">Run1 Run2 Diff Pct</span><br /><span style="font-family:courier new;">1,248,536 1,267,073 18,537 98.54%</span><br /><br /><span style="font-family:courier new;">PL/SQL procedure successfully completed.</span></pre><br /><br />As you see the bulk collect method completes in 1/3rd of the time of the collect function method. Using the collect function hits the library cache harder and uses more latch operations.<br /><br />The COLLECT function can be used in sql statements to compare collections of columns. In <a href="http://laurentschneider.com/">Laurent Schneider</a>'s <a href="http://oracletoday.blogspot.com/2008/04/relational-algebra-division-in-sql.html#comment-319653">comment on my previous post</a> you can find an example of it.Yasin Baskanhttp://www.blogger.com/profile/07642821755164831104noreply@blogger.comtag:blogger.com,1999:blog-15574215.post-40105254111961627282008-04-09T12:16:00.003+03:002008-04-09T12:42:49.079+03:00Relational algebra: division in sqlThere was a question in one of the Turkish Oracle mailing lists which got me interested. The question simply was:<br /><br /><span style="font-style: italic;">I have a table holding the parts of a product and another table holding the suppliers of these parts. How can I find the suppliers which supply all the parts?</span><br /><br />Someone suggested using the division operation of relational algebra but did not provide how to do it in Oracle. So I started with the <a href="http://en.wikipedia.org/wiki/Relational_algebra#Division">Wikipedia link</a> he provided to solve the problem in sql.<br /><br />Here are the tables:<br /><pre><br /><span style="font-size:85%;"><span style="font-family:courier new;">SQL> create table parts (pid number);</span><br /><br /><span style="font-family:courier new;">Table created.</span><br /><br /><span style="font-family:courier new;">SQL> create table catalog (sid number,pid number);</span><br /><br /><span style="font-family:courier new;">Table created.</span><br /><br /><span style="font-family:courier new;">SQL> insert into parts select rownum from all_objects where rownum<=5;</span><br /><br /><span style="font-family:courier new;">5 rows created.</span><br /><br /><span style="font-family:courier new;">SQL> insert into catalog values (10,1);</span><br /><br /><span style="font-family:courier new;">1 row created.</span><br /><br /><span style="font-family:courier new;">SQL> insert into catalog select 1,pid from parts;</span><br /><br /><span style="font-family:courier new;">5 rows created.</span><br /><br /><span style="font-family:courier new;">SQL> select * from catalog;</span><br /><br /><span style="font-family:courier new;"> SID PID</span><br /><span style="font-family:courier new;">---------- ----------</span><br /><span style="font-family:courier new;"> 10 1</span><br /><span style="font-family:courier new;"> 1 1</span><br /><span style="font-family:courier new;"> 1 2</span><br /><span style="font-family:courier new;"> 1 3</span><br /><span style="font-family:courier new;"> 1 4</span><br /><span style="font-family:courier new;"> 1 5</span><br /></span><br /></pre><br />So, the supplier which has all the parts is 1. How do we find that?<br /><br /><span style="font-weight: bold;font-size:130%;" ></span>The division in relational algebra is done by some steps which are explained in the link. Let's follow those:<br /><br /><p style="font-style: italic;">The simulation of the division with the basic operations is as follows. We assume that a<sub>1</sub>,...,a<sub>n</sub> are the attribute names unique to R and b<sub>1</sub>,...,b<sub>m</sub> are the attribute names of S. In the first step we project R on its unique attribute names and construct all combinations with tuples in S:</p> <dl style="font-style: italic;"><dd>T := π<sub>a<sub>1</sub>,...,a<sub>n</sub></sub>(R) × S</dd></dl><span style="font-style: italic;"></span><br />In our case we have the table CATALOG as R, the table PARTS as S. So if we write a sql to find out the above relation we need a cartesian join.<br /><pre><br /><span style="font-size:85%;"><span style="font-family:courier new;">select sid,pid</span><br /><span style="font-family:courier new;">from (select sid from catalog) ,parts</span><br /><span style="font-family:courier new;">;</span></span><br /></pre><br />This give us all suppliers combined with all parts.<br /><pre><br /><span style="font-size:85%;"><span style="font-family:courier new;">SID PID</span><br /><span style="font-family:courier new;">---------- ----------</span><br /><span style="font-family:courier new;"> 10 1</span><br /><span style="font-family:courier new;"> 1 1</span><br /><span style="font-family:courier new;"> 1 1</span><br /><span style="font-family:courier new;"> 1 1</span><br /><span style="font-family:courier new;"> 1 1</span><br /><span style="font-family:courier new;"> 1 1</span><br /><span style="font-family:courier new;"> 10 2</span><br /><span style="font-family:courier new;"> 1 2</span><br /><span style="font-family:courier new;"> 1 2</span><br /><span style="font-family:courier new;"> 1 2</span><br /><span style="font-family:courier new;"> 1 2</span><br /><br /><span style="font-family:courier new;"> SID PID</span><br /><span style="font-family:courier new;">---------- ----------</span><br /><span style="font-family:courier new;"> 1 2</span><br /><span style="font-family:courier new;"> 10 3</span><br /><span style="font-family:courier new;"> 1 3</span><br /><span style="font-family:courier new;"> 1 3</span><br /><span style="font-family:courier new;"> 1 3</span><br /><span style="font-family:courier new;"> 1 3</span><br /><span style="font-family:courier new;"> 1 3</span><br /><span style="font-family:courier new;"> 10 4</span><br /><span style="font-family:courier new;"> 1 4</span><br /><span style="font-family:courier new;"> 1 4</span><br /><span style="font-family:courier new;"> 1 4</span><br /><br /><span style="font-family:courier new;"> SID PID</span><br /><span style="font-family:courier new;">---------- ----------</span><br /><span style="font-family:courier new;"> 1 4</span><br /><span style="font-family:courier new;"> 1 4</span><br /><span style="font-family:courier new;"> 10 5</span><br /><span style="font-family:courier new;"> 1 5</span><br /><span style="font-family:courier new;"> 1 5</span><br /><span style="font-family:courier new;"> 1 5</span><br /><span style="font-family:courier new;"> 1 5</span><br /><span style="font-family:courier new;"> 1 5</span><br /></span></pre><br />We now have all the possibilities for the supplier-part relation.<br /><br />The second step is:<br /><br /><span style="font-style: italic;">In the next step we subtract R from this relation:</span><br /><br /><span style="font-style: italic;"> </span><span style="font-style: italic;">U := T - R</span><br /><br />To subtract the table CATALOG we need the MINUS operator.<br /><pre><br /><span style="font-size:85%;"><span style="font-family:courier new;">select sid,pid</span><br /><span style="font-family:courier new;">from (select sid from catalog) ,parts</span><br /><span style="font-family:courier new;">minus</span><br /><span style="font-family:courier new;">select sid,pid from catalog;</span><br /><br /><span style="font-family:courier new;"> SID PID</span><br /><span style="font-family:courier new;">---------- ----------</span><br /><span style="font-family:courier new;"> 10 2</span><br /><span style="font-family:courier new;"> 10 3</span><br /><span style="font-family:courier new;"> 10 4</span><br /><span style="font-family:courier new;"> 10 5</span></span><br /><br /><br /></pre><br /><br />After we had all the possibilities we subtracted the ones which are already in the CATALOG table and we got the ones which are not present in the table.<br /><br />On to the next step:<br /><br /><span style="font-style: italic;">Note that in U we have the possible combinations that "could have" been in R, but weren't. So if we now take the projection on the attribute names unique to R then we have the restrictions of the tuples in R for which not all combinations with tuples in S were present in R:</span><br /><br /><span style="font-style: italic;"> V := πa1,...,an(U)</span><br /><br />This step just gets the supplier id's from the previous query.<br /><pre><br /><span style="font-size:85%;"><span style="font-family:courier new;">select sid from (</span><br /><span style="font-family:courier new;">select sid,pid</span><br /><span style="font-family:courier new;">from (select sid from catalog) ,parts</span><br /><span style="font-family:courier new;">minus</span><br /><span style="font-family:courier new;">select sid,pid from catalog</span><br /><span style="font-family:courier new;">);</span><br /><br /><span style="font-family:courier new;"> SID</span><br /><span style="font-family:courier new;">----------</span><br /><span style="font-family:courier new;"> 10</span><br /><span style="font-family:courier new;"> 10</span><br /><span style="font-family:courier new;"> 10</span><br /><span style="font-family:courier new;"> 10</span></span><br /></pre><br /><br />Now we have the supplier id which does not supply all of the parts. The next step is obvious, to find the other suppliers (the remaining ones supply all the parts).<br /><br /><span style="font-style: italic;">So what remains to be done is take the projection of R on its unique attribute names and subtract those in V:</span><br /><br /><span style="font-style: italic;"> W := πa1,...,an(R) - V</span><br /><br />To do this we need to subtract the previous query from the table CATALOG.<br /><pre><br /><span style="font-size:85%;"><span style="font-family:courier new;">select sid from catalog</span><br /><span style="font-family:courier new;">minus</span><br /><span style="font-family:courier new;">select sid from (</span><br /><span style="font-family:courier new;">select sid,pid</span><br /><span style="font-family:courier new;">from (select sid from catalog) ,parts</span><br /><span style="font-family:courier new;">minus</span><br /><span style="font-family:courier new;">select sid,pid from catalog</span><br /><span style="font-family:courier new;">);</span><br /><br /><span style="font-family:courier new;"> SID</span><br /><span style="font-family:courier new;">----------</span><br /><span style="font-family:courier new;"> 1</span></span><br /></pre><br />In some pages (like <a href="http://www.tc.umn.edu/%7Ehause011/code/SQLexample.txt">this one</a>) the same operation is done using a different sql (obtained by using "not exists" instead of "minus").<br /><pre><br /><span style="font-size:85%;"><span style="font-family:courier new;">select distinct sid from catalog c1</span><br /><span style="font-family:courier new;">where not exists (</span><br /><span style="font-family:courier new;"> select null from parts p</span><br /><span style="font-family:courier new;"> where not exists (select null from catalog where pid=p.pid and c1.sid=sid));</span></span><br /></pre><br />This one is harder for me to understand in the first look, I am more comfortable following the steps above.<br /><br />It is great to follow the steps of relational algebra to solve a problem in sql. It helps very much in understanding the solution. Relational algebra rocks!Yasin Baskanhttp://www.blogger.com/profile/07642821755164831104noreply@blogger.comtag:blogger.com,1999:blog-15574215.post-29659755435792152552008-02-05T12:13:00.001+02:002008-02-05T12:13:58.775+02:00Local vs. remote connection performance<p>It has been asked several times in several places; is there a performance difference between running a query locally in a client on the server and running the same query in a remote client? </p> <p>The obvious answer given by the respondents including myself is: "if you do not return thousands of rows through the network, there must not be any difference". This type of response is opposed to what I believe; even if the answer seems obvious test it before you make any suggestions.</p> <p><a href="http://blog.tanelpoder.com/">Tanel Poder</a> got the same question and did what is needed to be done, he tested it and showed that there was a difference. <a href="http://blog.tanelpoder.com/2008/02/05/oracle-hidden-costs-revealed-part-1/">In this great post of his</a>.</p> <p>His tests use a database on Solaris, sqlplus clients on Windows and Linux. I have tested the same using a database on Linux and the same behavior is observed there too.</p> <p>Lesson learned again and again: test your suggestion even if the answer seems obvious.</p>Yasin Baskanhttp://www.blogger.com/profile/07642821755164831104noreply@blogger.com1tag:blogger.com,1999:blog-15574215.post-28908835402615327322008-02-04T15:33:00.001+02:002008-02-04T15:33:50.377+02:00Database version control<p><a href="http://www.codinghorror.com/blog">Coding horror</a> is one of the software development blogs I keep a close eye on. <p>Jeff Atwood posted <a href="http://www.codinghorror.com/blog/archives/001050.html">a nice piece about database version</a> control recently. Database version control is maybe one of the most important and unfortunately most overlooked things in software development. The post is a good read including the links he provides.</p>Yasin Baskanhttp://www.blogger.com/profile/07642821755164831104noreply@blogger.com3tag:blogger.com,1999:blog-15574215.post-66656759999226889772008-01-11T14:34:00.000+02:002008-01-11T14:50:38.899+02:00The blog tagging thingDuring the last few days lots of Oracle bloggers have been busy tagging each other and posting eight unknown things about themselves. I was also tagged by some friends and was asked to post eight things about myself. I have never forwarded any chain e-mails or messages to anyone and in parallel to that I have not written anything about myself after this either.<br /><br />What I think about this blog tagging thing is very similar to what Howard Rogers thought about it. He shut his site down for some time and you can read what he thinks when you go to his blog. My thoughts on this are <a href="http://awads.net/wp/2008/01/10/i-am-a-spammer-and-so-are-you-if-you-played-the-tag-game/#comment-51890">here </a>in the comments to <a href="http://awads.net/wp/2008/01/10/i-am-a-spammer-and-so-are-you-if-you-played-the-tag-game/">an Eddie Awad post</a>. Howard has also posted <a href="http://awads.net/wp/2008/01/10/i-am-a-spammer-and-so-are-you-if-you-played-the-tag-game/#comment-51893">a comment there</a> to explain further.Yasin Baskanhttp://www.blogger.com/profile/07642821755164831104noreply@blogger.com0