tag:blogger.com,1999:blog-76151006912851502102024-03-14T00:37:49.766+07:00otak-otakmakanan favourit selepas magribUjanghttp://www.blogger.com/profile/04962906686661927128noreply@blogger.comBlogger4125tag:blogger.com,1999:blog-7615100691285150210.post-85310408417939076402007-10-29T19:41:00.000+07:002007-10-29T19:56:57.213+07:00the 1st time 3 nodes RACit was a chance to modify existing RAC environment, especially on production.<br /><br />existing environment is 2 nodes rac on solaris 9 with oracle clusterware 10.2.0.3<br />users asks me to add it become 3 nodes.<br /><br />the preparation was taken about 1 months, it was testing environment trying to test with some workloads...etc.<br /><br />and finally they said...it is ok than pls do it on production...<br />starting from 00:00AM I played with the node addition....<br />some small trouble was found due to corruption on 2nd node's inventory.<br />we fixed it by simply copy an xml file from 1st node to 2nd node, and continue.<br /><br />but after a days we successfully add the 3rd node, I got an message that the node 1 & 2 has been restarting .... hmmmmmmmm amazing.....<br /><br />digging to the alert log and crs log... open metalink and start browsing .... found some nice bugs......<br /><br />huh..mann live so so so hard :(<br /><br />ujang<br />jakarta - indonesiaUjanghttp://www.blogger.com/profile/04962906686661927128noreply@blogger.com0tag:blogger.com,1999:blog-7615100691285150210.post-53804790021186947482007-10-29T19:28:00.000+07:002007-10-29T19:35:13.583+07:00hints - how it could help ??I have a database with 2 nodes RAC on Linux Itanium 64 bit of course.<br /><br />users always ask me " why my query so slow, when it is on 9i database it is not too slow". the I'm digging into "what make it slow".<br /><br />I found I nice SQL which is something like this:<br /><br />select .....<br />function_get(....).....<br />function_get(....).....<br />function_get(....).....<br />from (select ........<br /> from ( select .....<br /> from a, b)<br /> )<br /><br />it almost full day I played with the query....<br />trial and error was done... by add some hints ....<br /><br />and finally I resolved by some hints with add a cumulative index ....<br /><br />/*+ first_rows leading(a b) use_nl(a b) use_index(a a_xxxx_idx_0011) use_concat NO_PUSH_SUBQ(a) */ <br /><br />wowww.... it's so long.... look like train....<br /><br />I dont think so this is due to oracle database, but it should rely on application design problem :(<br /><br /><br />ujang<br />jakarta - indonesiaUjanghttp://www.blogger.com/profile/04962906686661927128noreply@blogger.com0tag:blogger.com,1999:blog-7615100691285150210.post-49227797651666583842007-03-21T12:46:00.000+07:002007-03-21T12:47:12.769+07:00TAF - Transparent Application Fail Over on RAC 9iOne benefit of Oracle RAC is TAF - transparent aplication failover, <br />which could guarantee continuing its session and query (only select that can be handled, DML, DDL still in my dream, perhaps oracle 11g could have it) when <br />instance failure.<br /><br />Here I will talk about simple network naming method by local naming (tnsnames)<br />to accomodate TAF functioning.<br /><br />in this scenario I am using the environment:<br />1. Oracle 9.2.0.4 cluster manager as clusterware or CRS<br />2. Oracle 9.2.0.4 as oracle RAC database<br />3. Linux CentOS Enterprise 2.1<br />4. VMWare (we do testing on a limited resources)<br /><br />The installation of the above environment is quite easy and comfortable<br />for almost DBA, who have the knowledge about the concept of cluster as well as oracle database itself.<br /><br />Because TAF provides failover for query (select statement) and the tool which I have tested is SQL*Plus, another drivers also have TAF ready which mainly OCI base.<br /><br />Real Application Clusters Failover, involve several works inside, there are:<br />• Detect failure by monitoring the heartbeat<br />• Reorganize instance membership<br />• Perform instance recovery<br />• Reestablish failed client connections<br /><br />TAF on Oracle RAC Can be used with the "available" nodes or with primary/secondary instance configurations (application partitioning against RAC database)<br /><br /><br />TAF Failover Mode Options<br /><br />• Must add failover options manually to TNS configuration files<br />• They are part of the CONNECT_DATA section of a connect descriptor<br />• Failover options include<br /> – TYPE – SESSION: Failover to an alternate session only<br /> – SELECT: Failover and continue with any ongoing query<br /> – NONE: Prevent failover<br /><br /> – METHOD • BASIC: Establishes no contact with the failover <br /> instance prior to failure<br /> • PRECONNECT: Creates mirror connections on the<br /> standby instance for the connections on the primary<br /> instance<br /><br /> – BACKUP: Identify an alternate net service name<br /> Specify a different net service name for backup connections.<br /> • Use with the PRECONNECT method to identify the<br /> service to initiate your failover preconnections<br /><br /> – RETRIES: Limit the number of times a reconnection will be attempted<br /> Specify the number of times to attempt to connect after a failover.<br /><br /> – DELAY: Specify how long to wait between reconnection attempts<br /> Specify the amount of time in seconds to wait between connect attempts.<br /><br /><br />herewith a snippet of initial parameter to comply with TAF:<br /><br />*.cluster_database_instances=3<br />*.cluster_database=TRUE<br />*.db_domain=''<br />*.db_name='racdb'<br />racdb2.local_listener='LISTENER_RACDB2'<br />racdb1.local_listener='LISTENER_RACDB1'<br />racdb3.local_listener='LISTENER_RACDB3'<br />*.remote_listener='LISTENERS_RACDB'<br /><br />those LISTENER_* are entries in the tnsnames.ora file which point to each instance as well as an "FAILOVER" ready in the remote_listener:<br /><br />here tnsnames.ora file to accomodate the above initial parameter complying TAF:<br /><br />LISTENER_RACDB3 =<br /> (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.3.91)(PORT = 1521))<br /><br />#see racdb3.local_listener in the initial parameter and 10.1.3.91 is <br />the public IP of the third Node<br /><br />LISTENER_RACDB2 =<br /> (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.3.81)(PORT = 1521))<br /><br />#see racdb2.local_listener in the initial parameter and 10.1.3.81 is <br />the public IP of the second Node<br /><br />LISTENER_RACDB1 =<br /> (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.3.71)(PORT = 1521))<br /><br />#see racdb1.local_listener in the initial parameter and 10.1.3.71 is <br />the public IP of the first Node<br /><br />RACDB =<br /> (DESCRIPTION=<br /> (ADDRESS_LIST=<br /> (LOAD_BALANCE=ON)<br /> (FAILOVER=ON)<br /> (ADDRESS=(PROTOCOL=TCP)(HOST=10.1.3.71)(PORT=1521))<br /> (ADDRESS=(PROTOCOL=TCP)(HOST=10.1.3.81)(PORT=1521))<br /> (ADDRESS=(PROTOCOL=TCP)(HOST=10.1.3.91)(PORT=1521))<br /> )<br /> (CONNECT_DATA=<br /> (SERVICE_NAME=racdb)<br /> (FAILOVER_MODE=<br /> (TYPE=SELECT)<br /> (METHOD=BASIC)<br /> (RETRIES=500)<br /> (DELAY=5)<br /> )<br /> )<br /> )<br /><br />#the racdb entry is aimed for client connection, see here we use FAILOVER option and service_name derive from service_name in initial parameter or it could derived from db_name.db_domain.<br /># this entry also can handle load balancing and client connection time failover as well.<br /><br />LISTENERS_RACDB =<br /> (DESCRIPTION =<br /> (ADDRESS_LIST =<br /> (LOAD_BALANCE=ON)<br /> (FAILOVER=ON)<br /> (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.3.71)(PORT = 1521))<br /> (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.3.81)(PORT = 1521))<br /> (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.3.91)(PORT = 1521))<br /> (LOAD_BALANCE = yes)<br /> )<br /> (CONNECT_DATA =<br /> (SERVICE_NAME = racdb)<br /> (FAILOVER_MODE=<br /> (TYPE=SELECT)<br /> (METHOD=BASIC)<br /> (RETRIES=500)<br /> (DELAY=5)<br /> )<br /> )<br /> )<br /><br /># this entry also seem like for the client connection, which has FAILOVER option to comply remote_listener in the initial parameter.<br /><br /><br /><br />this simple listener.ora file (here for node1)<br /><br />LISTENER =<br /> (DESCRIPTION_LIST =<br /> (DESCRIPTION =<br /> (ADDRESS_LIST =<br /> (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.3.71)(PORT = 1521))<br /> )<br /> )<br /> )<br /><br />SID_LIST_LISTENER =<br /> (SID_LIST =<br /> (SID_DESC =<br /> (SID_NAME = PLSExtProc)<br /> (ORACLE_HOME = /u01/app/oracle/product/9.2.0)<br /> (PROGRAM = extproc)<br /> )<br /> (SID_DESC =<br /> (ORACLE_HOME = /u01/app/oracle/product/9.2.0)<br /> (SID_NAME = racdb1)<br /> )<br /> )<br /><br /><br />Now its time to investigate TAF<br /><br />connect from any client by SQL*Plus :<br /><br />sqlplus /nolog<br />SQL> conn system/oracle@racdb<br />SQL> SELECT machine, failover_type, failover_method, failed_over, COUNT(*)<br />FROM v$session<br />GROUP BY machine, failover_type, failover_method, failed_over;<br /><br />the row should indicated failover_type, failover_method match with the entries in the tnsnames.ora file.<br /><br />check which instance you are currently in:<br />SQL> select instance_name from v$instance;<br /><br />now grab all rows in dba_objects:<br />SQL> select * from dba_objects;<br /><br />while the above query running, on the node which instance your connection run in, try shutdown abort to simulate instance crash failure, by using sqlplus or srvctl.<br /><br />sqlplus '/as sysdba'<br />SQL> shutdown abort;<br /><br />it should the above query select * from dba_objects, hold for a while and continue.Ujanghttp://www.blogger.com/profile/04962906686661927128noreply@blogger.com0tag:blogger.com,1999:blog-7615100691285150210.post-13862918046313101072007-02-19T14:25:00.001+07:002007-10-29T19:38:37.758+07:00Parallel QueryParallel Query<br /><br />1. Introduced since Oracle 7.1 as Oracle Parallel Query Option (PQO) Parallel operations include:<br />* SELECT, FTS or index range scan spanning multiple partitions<br />* Creating or rebuilding an index<br />* Partition operations such as moving or splitting partitions<br />* CREATE TABLE AS SELECT<br />* INSERT INTO . . . SELECT<br />* Update and delete operations on partitioned tables<br /><br />2. To parallelize a SELECT statement, the following conditions must be met:<br />* FTS or Index range scan involving multiple partitions.<br />* PARALLEL hint in FTS or PARALLEL on table definition.<br />* On an index PARALLEL_INDEX hint or index must have a parallel definition.<br /><br />Example<br />alter table emp parallel (degree 4);<br />select degree from user_tables where table_name = 'EMP';<br />select count(*) from emp;<br />alter table emp noparallel;<br />SELECT /*+ PARALLEL(emp,4) */ COUNT(*) FROM emp;<br /><br />3. Parallel DML<br />* Only when multiple partitions are involved.<br />* Oracle can parallelize only INSERT . . . SELECT . . . FROM statements.<br /><br />Examples<br />alter session enable parallel dml;<br />insert /*+ parallel (emp_big,4,1) */ into emp_big select * from emp;<br />commit;<br />alter session disable parallel dml;<br /><br />4. Parallel DDL<br />Parallel DDL works for both tables and indexes, whether partitioned or nonpartitioned.<br /><br />CREATE TABLE...AS SELECT<br />CREATE INDEX<br />ALTER INDEX...REBUILD<br /><br />Not all tables allow these operations to be executed in parallel.<br />Tables with object columns or LOB columns don't allow parallel DDL.<br /><br />5. Parallel Data Loading::<br />SQLLOAD scott/tiger CONTROL=con1.ctl DIRECT=TRUE PARALLEL=TRUE<br />SQLLOAD scott/tiger CONTROL=con2.ctl DIRECT=TRUE PARALLEL=TRUE<br />SQLLOAD scott/tiger CONTROL=con3.ctl DIRECT=TRUE PARALLEL=TRUE<br />SQLLOAD scott/tiger CONTROL=con4.ctl DIRECT=TRUE PARALLEL=TRUE<br /><br />6. Parallel Recovery<br />The RECOVERY_PARALLELISM initialization parameter controls<br />the degree of parallelism to use for a recovery.<br />You can override that setting for a specific situation<br />by using the RECOVER command's PARALLEL clause.<br /><br />Example<br />RECOVER TABLESPACE tab PARALLEL (DEGREE 4);<br />RECOVER DATABASE PARALLEL (DEGREE DEFAULT);<br /><br />A value of or 1 indicates serial recovery, no parallelism will be used.<br />The RECOVERY_PARALLELISM parameter setting cannot exceed<br />the PARALLEL_MAX_SERVERS setting.Ujanghttp://www.blogger.com/profile/04962906686661927128noreply@blogger.com0