Skip to main content

Sequences in Oracle RAC


Few days back, I had seen some magical behavior of oracle sequences.
We got a complaint from users about missing on some data. When we check database and SQLs, logically it must have traveled in data files. After analyzing the data download plug-ins, file status screen we found that, 17 files have the same name around time frame of 1658Hrs to 1735Hrs.

We are using the Oracle Sequence Number to allocate the file name uniquely, and then also why file names are duplicate?? What's wrong there??
 -- After 1636hrs oracle started allocating the sequence number with lesser value than the current one and continued till 1740Hrs.  
 -- The file allocation logic was looking for max file ID value.
 -- Which coming out to be same for this entire time frame; as new file ID has lesser value than the old file ID.

How this happened ??  
In an Oracle RAC cluster, the sequence numbers returned were not in order...
The reason is that by default sequences in 10g\11g RAC are created without ordering. This is to reduce performance bottleneck as there would be synchronization required across all nodes of the cluster. Each node creates a cache of the sequence as specified in the SQL.

If we need to maintain the order then we need to add the ORDER argument to the sequence.
Example of Sequence Syntax in Oracle RAC:

CREATE SEQUENCE TRNXECTRLMSTSEQ
MINVALUE 1
MAXVALUE 999999999999999999999999999
INCREMENT BY 1
START WITH 1
CACHE 20
ORDER NOCYCLE;

Comments

Post a Comment

Popular posts from this blog

Drop all Objects from Schema In Postgres

To Drop all objects from Postgres Schema there could be following two approaches: Drop Schema with cascade all and re-create it again.  In some cases where you dont want to/not allowed to drop and recreate schema, its easy to look for objects on current schema and drop them. following script would help to do so, Create function which would do the task and then drop that function too. --- CREATE OR REPLACE FUNCTION drop_DB_objects() RETURNS VOID AS $$ DECLARE  rd_object RECORD; v_idx_statement VARCHAR(500);   BEGIN ---1. Dropping all stored functions RAISE NOTICE '%', 'Dropping all stored functions...'; FOR rd_object IN ( SELECT format('%I.%I(%s)', ns.nspname, p.proname, oidvectortypes(p.proargtypes)) as functionDef     FROM pg_proc p     INNER JOIN pg_namespace ns ON (p.pronamespace = ns.oid)    WHERE ns.nspname = current_schema      AND p.proname <...
2 comments
My Other Post >>

Distributed transaction in Oracle ( Over Oracle DBLink)

To fetch the data from one server to and other Oracle server over DBLink, I experienced the following facts of Oracle Distributed transactions: Security issue: -           We cannot create Public synonym for the remote object accessed over Private DBLink of other Database.   -           It’s allowed to create private synonym for remote object, but you cannot grant the access over this synonym to any other schema. If you try to provide the grants to other schema Oracle raises an error:              [ORA-02021: DDL operations are not allowed on a remote database] “In an all you can access remote objects over private DBLink in the same schema where DBLink is created”. Fetching the Ref Cursor at Remote site:                   Let’s say we have two site...
1 comment
My Other Post >>

Vacuum Analyze Full Schema in PostgreSQL Database

To analyze full database schema, you can use following shell script : -------------------------------------------------------------------------------------------------------------------------- #!/bin/sh rm -rf /tmp/vacuum_analyze_MyData.sql dbname="mydata" username="mydatadatamart" namespace="mydatadatamart" # Vacuum only those tables which are fragmented over 30% # Analyze tables only if they are not already # Process CMF and Groups Master table every time as they are master Tables=`psql $dbname $username << EOF SELECT 'VACUUM ANALYZE VERBOSE ' || tablename || ' ;' AS TableList   FROM ( SELECT *,       n_dead_tup > av_threshold AS "av_needed",       CASE WHEN reltuples > 0      THEN round(100.0 * n_dead_tup / (reltuples))       ELSE 0       END AS pct_dead FROM (SELECT C.relname AS TableName, pg_stat_get_tuples_inserted(C.oid) AS n_tup_ins, pg_s...
3 comments
My Other Post >>