OraFAQ Forum: SQL & PL/SQL » Sequence alternate

Home » SQL & PL/SQL » SQL & PL/SQL » Sequence alternate (19C)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Sequence alternate [message #687525]

Tue, 28 March 2023 08:54

deepakdot
Messages: 89
Registered: July 2015

Member

Hi

I have a scenario. we have two Data Center with active-Passive. First six month DC1 will be active and next six month DC2 will be active. we have sequences for table. As we are using both the DC , to eliminate duplicate values from sequence, we use lets say even/odd number as below. So that when we switch to other DC, we don't get a duplicate values.

DC1 : 1,3,5,7,..
DC2 : 2,4,6,8,...

Question: Now we have a requirement that we can not skip any number, table should have values as 1,2,3,4 .. Is there any solution / idea to address this. we need to have unique numbers across both the data center. we can not have identity column on the table. Please suggest.

cheers,
Deepak

Report message to a moderator

Re: Sequence alternate [message #687526 is a reply to message #687525]

Tue, 28 March 2023 10:13

Barbara Boehmer
Messages: 9058
Registered: November 2002
Location: California, USA

Senior Member

I don't know what your process is for switching every 6 months, but perhaps you could select the maximum sequence number from the one that is stopping, then drop the sequence from the one that is starting, then recreate the new sequence to start with the maximum sequence + 1 of the old sequence.

Report message to a moderator

Re: Sequence alternate [message #687528 is a reply to message #687526]

Tue, 28 March 2023 15:39

Solomon Yakobson
Messages: 3244
Registered: January 2010
Location: Connecticut, USA

Senior Member

Quote:

Question: Now we have a requirement that we can not skip any number

Not possible in general - user can get value from the sequence but then rollback transaction. If sequence has cache then each time database is restarted (say for maintenance or it chashes) unused portion of cached out sequence numbers is lost.

SY.

Report message to a moderator

Re: Sequence alternate [message #687530 is a reply to message #687528]

Tue, 28 March 2023 23:11

mathguy
Messages: 77
Registered: January 2023

Member

What are you using the sequence values for? (And, do you mean "sequence" in the sense of Oracle-provided sequences?)

In general, a sequence would be used to generate an artificial (but very useful!) primary key to a table. The same sequence may be used for more than one table, although I don't think it can be shared between different "data centers". I confess I have no idea what a "data center" is! Wikipedia is of no help - it says a "data center" is a building, a dedicated space within a building, or a group of buildings, used to house computer systems; that is what I thought, too, but I don't see what that would have to do with what you explained.

Anyway - if a "sequence" (in the technical sense as defined by Oracle) is used to generate values for an artificial primary key, then requiring that all the values be consecutive - without gaps - is just plain meaningless; anyone asking for such a thing has no business asking for it, since they have no clue what they are talking about. The values themselves aren't supposed to have any kind of real-life meaning; so why does it matter if there are gaps? This is a very common and very basic misconception, shared largely among incompetent managers who don't understand computing.

So - I'm confused. Can you please explain what a "data center" is in your terminology? What is a "sequence", and what it is used for? And how "data centers" are "sharing" a "sequence"?

Report message to a moderator

Re: Sequence alternate [message #687531 is a reply to message #687530]

Wed, 29 March 2023 00:30

Barbara Boehmer
Messages: 9058
Registered: November 2002
Location: California, USA

Senior Member

I found this:

Quote:

da·ta cen·ter
noun
a large group of networked computer servers typically used by organizations for the remote storage, processing, or distribution of large amounts of data.

So, I guess they are switching from one server to another every 6 months for some unknown reason.

If you search the web, you will find all sorts of discussions about gap-free sequences. They will all tell you it is a bad idea. However, in some cases it is a legal requirement. So, there are a few methods out there with vaious drawbacks. Here is a link to one of them:

https://blog.sql-workbench.eu/post/gapless-sequence/

Report message to a moderator

Re: Sequence alternate [message #687532 is a reply to message #687531]

Wed, 29 March 2023 05:41

deepakdot
Messages: 89
Registered: July 2015

Member

Data Center: Sorry for the confusions. Just to Explain high level, we have two servers, used for Data Replications (Active - passive ) for Disaster Recovery. Data is replicated using Oracle Golden Gate. So when we upgrade our product ( schema objects changes ) for every release , to minimize the down time, first we upgrade the secondary (passive) database server and make that as primary and the current primary will become secondary ( passive ). We have a process to minimize the downtime for any upgrade.

Back to my Question: Yes I am talking about the Oracle sequences. we have so many sequences in the schema. Having Gap in the sequence values for most of the sequence (used for primary keys) does not impact. But few sequences, we have a business requirement to have no gaps between number. When we switch the Database servers primary / secondary, we can drop the sequence and start from the values which was last used. But this is a manual process. So we have even /odd numbers across DB servers. Now I am looking for a solution only for those few sequences where numbers should not have any gap and can be used with out any manual process.

cheers,
Deepak

Report message to a moderator

Re: Sequence alternate [message #687533 is a reply to message #687532]

Wed, 29 March 2023 05:49

deepakdot
Messages: 89
Registered: July 2015

Member

Even I thought of creating a table which will have a row having the last value used. subsequently we can use value +1 for next numbers. The Table data will get replicated to the other server with Golder gate. When we switch to the other server as primary, this table can be used to get the next numbers. But this would be a bad design with no cache . Number of database call will increase.

cheers,
Deepak

Report message to a moderator

Re: Sequence alternate [message #687534 is a reply to message #687532]

Wed, 29 March 2023 05:52

Michel Cadot
Messages: 68500
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

So we have even /odd numbers across DB servers. Now I am looking for a solution only for those few sequences where numbers should not have any gap and can be used with out any manual process.

This is conflicting.
If one server takes even numbers and the other one odd numbers then you have gaps in the sequence unless you take exactly one number during each shift.

Quote:

So when we upgrade our product ( schema objects changes ) for every release , to minimize the down time, first we upgrade the secondary (passive) database server and make that as primary and the current primary will become secondary ( passive ). We have a process to minimize the downtime for any upgrade.

You should use Oracle editions, with no downtime at all.

Report message to a moderator

Re: Sequence alternate [message #687535 is a reply to message #687534]

Wed, 29 March 2023 06:40

deepakdot
Messages: 89
Registered: July 2015

Member

Currently we have Even / Odd numbers with using oracle sequences. So it has Gaps in the number. But the Business requirement is not to have any Gap. So I am looking for a solution to address this.

Report message to a moderator

Re: Sequence alternate [message #687536 is a reply to message #687535]

Wed, 29 March 2023 07:49

Michel Cadot
Messages: 68500
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

To address your downtime issue then:

Quote:

You should use Oracle editions, with no downtime at all.

To address the no gap sequence then you have to use some lock or counter solution: table lock, row lock on counter, dbms_lock, or any other known solution as the one Barbara's link shows.

[Updated on: Wed, 29 March 2023 07:52]

Report message to a moderator

Previous Topic:	sql
Next Topic:	difficult task

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Sep 23 08:39:20 CDT 2023