otak-otak

the 1st time 3 nodes RAC

2007-10-29T19:41:00.000+07:00

it was a chance to modify existing RAC environment, especially on production.

existing environment is 2 nodes rac on solaris 9 with oracle clusterware 10.2.0.3
users asks me to add it become 3 nodes.

the preparation was taken about 1 months, it was testing environment trying to test with some workloads...etc.

and finally they said...it is ok than pls do it on production...
starting from 00:00AM I played with the node addition....
some small trouble was found due to corruption on 2nd node's inventory.
we fixed it by simply copy an xml file from 1st node to 2nd node, and continue.

but after a days we successfully add the 3rd node, I got an message that the node 1 & 2 has been restarting .... hmmmmmmmm amazing.....

digging to the alert log and crs log... open metalink and start browsing .... found some nice bugs......

huh..mann live so so so hard :(

ujang
jakarta - indonesia

hints - how it could help ??

2007-10-29T19:28:00.000+07:00

I have a database with 2 nodes RAC on Linux Itanium 64 bit of course.

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".

I found I nice SQL which is something like this:

select .....
function_get(....).....
function_get(....).....
function_get(....).....
from (select ........
from ( select .....
from a, b)
)

it almost full day I played with the query....
trial and error was done... by add some hints ....

and finally I resolved by some hints with add a cumulative index ....

/*+ first_rows leading(a b) use_nl(a b) use_index(a a_xxxx_idx_0011) use_concat NO_PUSH_SUBQ(a) */

wowww.... it's so long.... look like train....

I dont think so this is due to oracle database, but it should rely on application design problem :(

ujang
jakarta - indonesia

TAF - Transparent Application Fail Over on RAC 9i

2007-03-21T12:46:00.000+07:00

One benefit of Oracle RAC is TAF - transparent aplication failover,
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
instance failure.

Here I will talk about simple network naming method by local naming (tnsnames)
to accomodate TAF functioning.

in this scenario I am using the environment:
1. Oracle 9.2.0.4 cluster manager as clusterware or CRS
2. Oracle 9.2.0.4 as oracle RAC database
3. Linux CentOS Enterprise 2.1
4. VMWare (we do testing on a limited resources)

The installation of the above environment is quite easy and comfortable
for almost DBA, who have the knowledge about the concept of cluster as well as oracle database itself.

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.

Real Application Clusters Failover, involve several works inside, there are:
• Detect failure by monitoring the heartbeat
• Reorganize instance membership
• Perform instance recovery
• Reestablish failed client connections

TAF on Oracle RAC Can be used with the "available" nodes or with primary/secondary instance configurations (application partitioning against RAC database)

TAF Failover Mode Options

• Must add failover options manually to TNS configuration files
• They are part of the CONNECT_DATA section of a connect descriptor
• Failover options include
– TYPE – SESSION: Failover to an alternate session only
– SELECT: Failover and continue with any ongoing query
– NONE: Prevent failover

– METHOD • BASIC: Establishes no contact with the failover
instance prior to failure
• PRECONNECT: Creates mirror connections on the
standby instance for the connections on the primary
instance

– BACKUP: Identify an alternate net service name
Specify a different net service name for backup connections.
• Use with the PRECONNECT method to identify the
service to initiate your failover preconnections

– RETRIES: Limit the number of times a reconnection will be attempted
Specify the number of times to attempt to connect after a failover.

– DELAY: Specify how long to wait between reconnection attempts
Specify the amount of time in seconds to wait between connect attempts.

herewith a snippet of initial parameter to comply with TAF:

*.cluster_database_instances=3
*.cluster_database=TRUE
*.db_domain=''
*.db_name='racdb'
racdb2.local_listener='LISTENER_RACDB2'
racdb1.local_listener='LISTENER_RACDB1'
racdb3.local_listener='LISTENER_RACDB3'
*.remote_listener='LISTENERS_RACDB'

those LISTENER_* are entries in the tnsnames.ora file which point to each instance as well as an "FAILOVER" ready in the remote_listener:

here tnsnames.ora file to accomodate the above initial parameter complying TAF:

LISTENER_RACDB3 =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.3.91)(PORT = 1521))

#see racdb3.local_listener in the initial parameter and 10.1.3.91 is
the public IP of the third Node

LISTENER_RACDB2 =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.3.81)(PORT = 1521))

#see racdb2.local_listener in the initial parameter and 10.1.3.81 is
the public IP of the second Node

LISTENER_RACDB1 =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.3.71)(PORT = 1521))

#see racdb1.local_listener in the initial parameter and 10.1.3.71 is
the public IP of the first Node

RACDB =
(DESCRIPTION=
(ADDRESS_LIST=
(LOAD_BALANCE=ON)
(FAILOVER=ON)
(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.3.71)(PORT=1521))
(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.3.81)(PORT=1521))
(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.3.91)(PORT=1521))
)
(CONNECT_DATA=
(SERVICE_NAME=racdb)
(FAILOVER_MODE=
(TYPE=SELECT)
(METHOD=BASIC)
(RETRIES=500)
(DELAY=5)
)
)
)

#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.
# this entry also can handle load balancing and client connection time failover as well.

LISTENERS_RACDB =
(DESCRIPTION =
(ADDRESS_LIST =
(LOAD_BALANCE=ON)
(FAILOVER=ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.3.71)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.3.81)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.3.91)(PORT = 1521))
(LOAD_BALANCE = yes)
)
(CONNECT_DATA =
(SERVICE_NAME = racdb)
(FAILOVER_MODE=
(TYPE=SELECT)
(METHOD=BASIC)
(RETRIES=500)
(DELAY=5)
)
)
)

# this entry also seem like for the client connection, which has FAILOVER option to comply remote_listener in the initial parameter.

this simple listener.ora file (here for node1)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.3.71)(PORT = 1521))
)
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/9.2.0)
(PROGRAM = extproc)
)
(SID_DESC =
(ORACLE_HOME = /u01/app/oracle/product/9.2.0)
(SID_NAME = racdb1)
)
)

Now its time to investigate TAF

connect from any client by SQL*Plus :

sqlplus /nolog
SQL> conn system/oracle@racdb
SQL> SELECT machine, failover_type, failover_method, failed_over, COUNT(*)
FROM v$session
GROUP BY machine, failover_type, failover_method, failed_over;

the row should indicated failover_type, failover_method match with the entries in the tnsnames.ora file.

check which instance you are currently in:
SQL> select instance_name from v$instance;

now grab all rows in dba_objects:
SQL> select * from dba_objects;

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.

sqlplus '/as sysdba'
SQL> shutdown abort;

it should the above query select * from dba_objects, hold for a while and continue.

Parallel Query

2007-02-19T14:25:00.001+07:00

Parallel Query

1. Introduced since Oracle 7.1 as Oracle Parallel Query Option (PQO) Parallel operations include:
* SELECT, FTS or index range scan spanning multiple partitions
* Creating or rebuilding an index
* Partition operations such as moving or splitting partitions
* CREATE TABLE AS SELECT
* INSERT INTO . . . SELECT
* Update and delete operations on partitioned tables

2. To parallelize a SELECT statement, the following conditions must be met:
* FTS or Index range scan involving multiple partitions.
* PARALLEL hint in FTS or PARALLEL on table definition.
* On an index PARALLEL_INDEX hint or index must have a parallel definition.

Example
alter table emp parallel (degree 4);
select degree from user_tables where table_name = 'EMP';
select count(*) from emp;
alter table emp noparallel;
SELECT /*+ PARALLEL(emp,4) */ COUNT(*) FROM emp;

3. Parallel DML
* Only when multiple partitions are involved.
* Oracle can parallelize only INSERT . . . SELECT . . . FROM statements.

Examples
alter session enable parallel dml;
insert /*+ parallel (emp_big,4,1) */ into emp_big select * from emp;
commit;
alter session disable parallel dml;

4. Parallel DDL
Parallel DDL works for both tables and indexes, whether partitioned or nonpartitioned.

CREATE TABLE...AS SELECT
CREATE INDEX
ALTER INDEX...REBUILD

Not all tables allow these operations to be executed in parallel.
Tables with object columns or LOB columns don't allow parallel DDL.

5. Parallel Data Loading::
SQLLOAD scott/tiger CONTROL=con1.ctl DIRECT=TRUE PARALLEL=TRUE
SQLLOAD scott/tiger CONTROL=con2.ctl DIRECT=TRUE PARALLEL=TRUE
SQLLOAD scott/tiger CONTROL=con3.ctl DIRECT=TRUE PARALLEL=TRUE
SQLLOAD scott/tiger CONTROL=con4.ctl DIRECT=TRUE PARALLEL=TRUE

6. Parallel Recovery
The RECOVERY_PARALLELISM initialization parameter controls
the degree of parallelism to use for a recovery.
You can override that setting for a specific situation
by using the RECOVER command's PARALLEL clause.

Example
RECOVER TABLESPACE tab PARALLEL (DEGREE 4);
RECOVER DATABASE PARALLEL (DEGREE DEFAULT);

A value of or 1 indicates serial recovery, no parallelism will be used.
The RECOVERY_PARALLELISM parameter setting cannot exceed
the PARALLEL_MAX_SERVERS setting.