Best Way to generate Primary Keys in Java, Oracle RAC web application (Sequences?)

Environment: Oracle 11G, Web Portal Application development, Java, DataGuard available if needed

What is the best/recommended/proven way to generate integer surrogate keys for App development with Oracle as back-end in multi node RAC environment. There may be relatively frequent node switch-over.

Q. IS CACHED, non-ordered sequence is best preferred way in above environment?

Factors:
security
performance,
data maintenance and quality,
Data ETL/Refreshing between environments,
failover
trackable sequential PK IDs
Data Auditing
Cache size (100?)

I don't really prefer guid. Should we use table based or app-generated IDs but then performance and seed data generated outside app can be an issue.

Please advice.

-D

Quote:

---

Originally Posted by daljitsb

Environment: Oracle 11G, . . . E t c

Q. IS CACHED, non-ordered sequence is best preferred way in above environment?

Factors:
security
performance,
data maintenance and quality,
Data ETL/Refreshing between environments,
failover
trackable sequential PK IDs
Data Auditing
Cache size (100?)

I don't really prefer guid. Should we use table based or app-generated IDs but then performance and seed data generated outside app can be an issue.

Please advice.

-D

---

A. Yes, CACHED, non-ordered sequence is best preferred way, but unfortunately there may be gaps in cached sequences. Cache size of 100 may be too large, from my experience perhaps 10 or 20 is enough.

The above is acceptable practice, but better check thus article:

Conceptual Muddling and Database Kludges

Quote:

---

Originally Posted by LKBrwn_DBA

A. Yes, CACHED, non-ordered sequence is best preferred way, but unfortunately there may be gaps in cached sequences. Cache size of 100 may be too large, from my experience perhaps 10 or 20 is enough.

The above is acceptable practice, but better check thus article:

Conceptual Muddling and Database Kludges

---

So how does CACHED, non-ordered sequence behave, here is the scenario,
Cache size =10
Node 1 Active - Records created 1,2,3 (3 records total in table)
Node Changes - 2 is Active - Records created 11,12,13, 14 (7 records total in table)
Node Changes - 1 is Active - Records created 21, 22 (or are PKs going to be 4, 5) (9 records total in table)
Node Changes - 2 is Active - No new record added (9 records total in table)
Node Changes - 1 is Active - Records created - Is it going to be 23, 24 OR or is it going to be 6,7 OR or is it going to be 31, 32 (9 records total in table)

Also, What happens to PK numbers at:
* shutdown
* hotbackup
* mirroring whole schema or database to another separate db using storage mirroring
* mirroring whole schema or database to another separate db using datapump

-D