[Oracle-l] infoworld call

Well that certainly does sound potentially ugly. And it describes the
reason behind the 16K/s transaction limitation that was discussed here a few
years back.

At least I have my SCN growth patterns for about a year now and we won't
even hit 80B in the next 10 years. At current rates. Provided nothing else
changes. Ever...

Marking that patch down for my upgrade to 11g. Thanks Ray!

Rich

I think it is ugly - I was able to prepare new XE database with quite huge SCN
(MOS check from [ID 1393363.1] reported Result: C - SCN Headroom is low)
and after that simple DB link allow me to propagate that SCN to other database
using user account.

regards,

I felt bored and did some calculations.. found a presentation from Mark
Aultwhere he suggested a machine that can do 2.4 million IOPS per second.
Suppose that could do 2.4 million SCN updates (Moore's law suggests that may
happen any moment now...) For the gedanken experiment, imagine a vast
environmental monitoring network with thousands of probes, one near each
suburb, village and hamlet on a densely populated continent perhaps, each of
which every second update/insert one record in a central datawarehouse for,
say, wind speed, dry- and wet bulb temperature, relative humidity and
surfacepressure. For added fun, that is 5 commits per probe per second
(different tables for each metric...).
Just by inserting data as fast as possible, it seems Mark's solution would
give the hypothetical meteo about 3 months of joy before the sanity check
kicks in, and only 4 years before the database is completely full. But
allegedly with an Exadata X2 that soft limit could already be hit in just 12
years :)

I would like to think that the hard limit in future versions will be put at
abit higher than a 48-bit integer...

tx/sec |years to:
============|=============|===========
1 | ~ | 8,919,892
1,000 | ~ | 8,920
3,328.9 | ~ | 2,679 Exadata 1
10,000 | ~ | 892
16,385 | 393,216 | 544
50,000 | 12 | 178 Exadata X2
1,000,000 | 0.4 | 9
2,400,000 | 0.2 | 4 RamSan - IOPS (not necessarily SCN
increases)
sources:

* exadata1 performance:
http://www.oracle.com/us/industries/utilities/performance-benchmark-exadata
-wp-161572.pdf[1]
* exadata2 performance:
http://www.oracle.com/us/industries/communications/brm-exadata-performance
-wp-362789.pdf[2]
* RamSan estimate :
http://www.slideshare.net/texasmemorysystems/oracle-exadata-x2-8rev1[3]

The calculation that I used for the soft limit is (hope I reduced that
right.. long time since high school..;) ):

[email protected]/1/2012 yearly_incr 12,408,273,764,352
517,011,406,848
yrs = -------------- + -------------- * yrs = ------------------- +
--------------- * yrs
sec_in_year *t sec_in_year *t 31,555,872 * t
31,555,872 * t

which I reduced to

yrs = 393,216 / ( t - 16384 )

Where t = transactions (SCN increases) per second and yrs = years to hit the
limit.

The numbers are derived as:

* soft limit @1/1/2012: 12,408,273,764,352 = ( 2012 - 1988 ) * 1
year_in_sec * 214
yearly increase: 517,011,406,848 = 1 year_in_sec * 214
* hard limit : 281,474,976,710,656 = 248
and 1 year = 365.23 * 24 * 3600 = 31,555,872 sec


(back to reality now.),
Tony

On 18/01/12 04:00, Rich Jesse wrote: Ray writes: infoworld say this bug is
really ugly. Well that certainly does sound potentially ugly. And it
describes the reason behind the 16K/s transaction limitation that was
discussed here a few years back. At least I have my SCN growth patterns for
about a year now and we won't even hit 80B in the next 10 years. At current
rates. Provided nothing else changes. Ever... Marking that patch down for my
upgrade to 11g. Thanks Ray! Rich --
http://www.freelists.org/webpage/oracle-l[4]


--- Links ---
1 http://www.oracle.com/us/industries/utilities/performance-benchmark-exadata-wp-161572.pdf
2 http://www.oracle.com/us/industries/communications/brm-exadata-performance-wp-362789.pdf
3 http://www.slideshare.net/texasmemorysystems/oracle-exadata-x2-8rev1
4 http://www.freelists.org/webpage/oracle-l

Try again, somehow the list server seemed to have stripped the html formatting first time round.. I'm sorry if you can't read this properly..
...
I felt bored and did some calculations.. found a presentation from Mark Ault where he suggested a machine that can do 2.4 million IOPS per second. Suppose that could do 2.4 million SCN updates (Moore's law suggests that may happen any moment now...) For the gedanken experiment, imagine a vast environmental monitoring network with thousands of probes, one near each suburb, village and hamlet on a densely populated continent perhaps, each of which every second update/insert one record in a central datawarehouse for, say, wind speed, dry- and wet bulb temperature, relative humidity and surface pressure. For added fun, that is 5 commits per probe per second (different tables for each metric...).

Just by inserting data as fast as possible, it seems Mark's solution would give the hypothetical meteo about 3 months of joy before the sanity check kicks in, and only 4 years before the database is completely full. But allegedly with an Exadata X2 that soft limit could already be hit in just 12 years :)

I would like to think that the hard limit in future versions will be put at a bit higher than a 48-bit integer...

tx/sec |years to:
============|=============|===========
1 | ~ | 8,919,892
1,000 | ~ | 8,920
3,328.9| ~ | 2,679 *Exadata 1*
10,000 | ~ | 892
16,385 | 393,216 | 544
50,000 | 12 | 178 *Exadata X2*
1,000,000 | 0.4 | 9
2,400,000 | 0.2 | 4 *RamSan* - IOPS (not necessarily SCN increases)

sources:

* exadata1 performance: http://www.oracle.com/us/industries/utilities/performance-benchmark-exadata-wp-161572.pdf
* exadata2 performance: http://www.oracle.com/us/industries/communications/brm-exadata-performance-wp-362789.pdf
* RamSan estimate : http://www.slideshare.net/texasmemorysystems/oracle-exadata-x2-8rev1


The calculation that I used for the soft limit is (hope I reduced that right.. long time since high school..;) ):

[email protected]/1/2012 yearly_incr 12,408,273,764,352 517,011,406,848
yrs = -------------- + -------------- * yrs = ------------------- + --------------- * yrs
sec_in_year *t sec_in_year *t 31,555,872 * t 31,555,872 * t

which I reduced to

yrs = 393,216 / ( t - 16384 )

Where t = transactions (SCN increases) per second and yrs = years to hit the limit.

The numbers are derived as:

* soft limit @1/1/2012: 12,408,273,764,352 = ( 2012 - 1988 ) * 1 year_in_sec * 2^14

yearly increase: 517,011,406,848 = 1 year_in_sec * 2^14

* hard limit : 281,474,976,710,656 = 2^48

and 1 year = 365.23 * 24 * 3600 = 31,555,872 sec


(back to reality now.),
Tony

They do indicate that this is in the plans at the bottom of MOS 1376995.1

I'm guessing this will be on the list of improvements for 12c.

It's about time that version gets 'aired' ... based on histerical
cycles, we should be starting to hear rumblings about (DB) 12c within
the next few weeks anyway.

UNtil then, Iwonder how many customers this really impacts.

/Hans

If Marcin's test case is valid about a desktop XE instance being able to drive up the SCN of any other instance it connects to, then it stands to reason that this has the *potential* to affect EVERY Oracle database customer that allows users to create sessions and install software on their workstations.

Right?

Chris Taylor
Sr. Oracle DBA
Ingram Barge Company
Nashville, TN 37205

"Quality is never an accident; it is always the result of intelligent effort."
-- John Ruskin (English Writer 1819-1900)

CONFIDENTIALITY NOTICE: This e-mail and any attachments are confidential and may also be privileged. If you are not the named recipient, please notify the sender immediately and delete the contents of this message without disclosing the contents to anyone, using them for any purpose, or storing or copying the information on any medium.

Right. Any user that has create session privilege can have a db link created to it, right?


Chris Taylor
Sr. Oracle DBA
Ingram Barge Company
Nashville, TN 37205

"Quality is never an accident; it is always the result of intelligent effort."
-- John Ruskin (English Writer 1819-1900)

CONFIDENTIALITY NOTICE: This e-mail and any attachments are confidential and may also be privileged. If you are not the named recipient, please notify the sender immediately and delete the contents of this message without disclosing the contents to anyone, using them for any purpose, or storing or copying the information on any medium.

Yes, test case and doc reference below:

http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5005.htm:

"To create a private database link, you must have the CREATE DATABASE LINK system privilege. To create a public database link, you must have the CREATE PUBLIC DATABASE LINK system privilege. Also, you must have the CREATE SESSION system privilege on the remote Oracle database."


##### TEST03 DATABASE ######

[email protected]>create user db_link_test identified by temporary112233;

User created.

[email protected]>grant create session to db_link_test;

Grant succeeded.



##### DEMO DATABASE ######

SYS@demo>create user db_link_test identified by temporary112233;

User created.

[email protected]>grant create session to db_link_test;

Grant succeeded.

[email protected]>conn db_link_test
Enter password:
Connected.

[email protected]>create database link db_link_test connect to db_link_test identified by temporary112233 using 'test03';
create database link db_link_test connect to db_link_test identified by temporary112233 using 'test03'
*
ERROR at line 1:
ORA-01031: insufficient privileges

[email protected]>conn / as sysdba
Connected.

[email protected]>grant create database link to db_link_test;

Grant succeeded.

@>conn db_link_test/temporary112233
Connected.

[email protected]>conn / as sysdba
Connected.

[email protected]>select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
869661524

[email protected]>conn db_link_test/temporary112233
Connected.

[email protected]>create database link db_link_test connect to db_link_test identified by temporary112233 using 'test03';

Database link created.



##### TEST03 DATABASE ######

[email protected]>select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
721203624



##### DEMO DATABASE ######

DB_[email protected]>select count(*) from [email protected]_link_test;

COUNT(*)
----------




##### TEST03 DATABASE ######

[email protected]>/

GET_SYSTEM_CHANGE_NUMBER
------------------------
869661584

Raising the limit to 2^64 won't help unless they also remove the feature
that allows you to set the current SCN to an arbitrary multiple of 2^32, or
scale up the number of SCN increments so that a "genuine" job can't
possibly make them happen fast enough.

My laptop can advance the SCN about 150,000 times per second - which means
it will take slightly less than 8 hours to get through 4 billion - which
means that's the longest time it will take for me to prepare my laptop to
be a threat. Your only protection comes from knowing confident that your
system can't increment the SCN faster than Oracle's limiting rate because I
have to start by injecting a value that is "behind" a critical value and
let you run on from there.


Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com
Oracle Core (Apress 2011)
http://www.apress.com/9781430239543

Jonathan,

Can you clarify what you meant about 'your only protection'. I didn't quite follow that piece.

Thanks!

Chris Taylor
Sr. Oracle DBA
Ingram Barge Company
Nashville, TN 37205

"Quality is never an accident; it is always the result of intelligent effort."
-- John Ruskin (English Writer 1819-1900)

CONFIDENTIALITY NOTICE: This e-mail and any attachments are confidential and may also be privileged. If you are not the named recipient, please notify the sender immediately and delete the contents of this message without disclosing the contents to anyone, using them for any purpose, or storing or copying the information on any medium.

Chris
JL is talking about SCN soft limit. I blogged about SCN here:
http://orainternals.wordpress.com/2012/01/19/scn-what-why-and-how/
I think, to fix this issue completely, Oracle code should limit
allowable SCN jump; Limit the jump to a day of SCN activity
(24*16384*60*60). Risk with that approach is that a less activity database
might not connect to highly active database for a day or week. At that time
of a distributed transaction between these two databases, SCN jump might
exceed few billions in the less active database and there will be ORA-600
errors.

Or may be that we need to have few thresholds: 4 hours of SCN jump (235
million) a warning written to alert log, 12 hours of SCN jump ~700 million,
an ORA- error written to alert log but not to the user, 24 hours of SCN
jump reject it. There is a problem with this approach also, as a malicious
intruder will trigger the SCN jumps in increment of 200 million injecting
the poison slowly and consistently. May be, another control that will not
allow a cumulative SCN jumps over a certain limit :)

Further, that SCN allocation part of the code executed highly frequently.
Any slowness will have massive performance issues too.

Since, there are many (mostly unknown?) background processes anyway, we
should get one more background process to monitor SCN growth :)

Cheers

Riyaj Shamsudeen
Principal DBA,
Ora!nternals - http://www.orainternals.com - Specialists in Performance,
RAC and EBS
Blog: http://orainternals.wordpress.com
OakTable member http://www.oaktable.com and Oracle ACE Director

Co-author of the books: Expert Oracle
Practices<http://tinyurl.com/book-expert-oracle-practices/>,
Pro Oracle SQL, Expert PL/SQL
Practices<http://tinyurl.com/book-expert-plsql-practices>

Riyaj,

Thanks for the link - I think that probably answers Chris' question.

The point I was making, without spelling out the details, is that I can set
my SCN to any arbitrarily high value that doesn't quite reach the breaking
point(which - ignoring timezone effects - is a universal breaking point for
all oracle systems) on my system and then push that value into your
system - but your system still won't break unless it then carries on
generating new SCNs at a rate higher than the limit that Oracle has coded,
which is 16,384 per second.

So it doesn't matter what the absolute value of the limit is, what matters
is whether or not one of your systems generates SCNs faster than Oracle
expects.

Riyaj,

On a trivial note - we don't need a special process, we already have the
AWR capture going on reading lots of data from dynamic performance views on
a regular basis; adding in a query against v$database would be sufficient,
recording the result and reporting it in the AWR would be a useful
diagnostic, and getting an alert if the jump is too high (in the log, and
via OEM) would be sufficient warning.

I don't think you can set an arbitrary limit at which the a jump will be
disallowed - but if you tried you would have to decide (implicitly) how
many SCNs per second was the legal maximum, so the current strategy (but
using a much larger limit) ought to be sense. A 64-bit SCN with an assumed
maximum of 1 billion (2^30) SCNs per second would give you 2^34 seconds of
life - which is about 545 years.


Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com
Oracle Core (Apress 2011)
http://www.apress.com/9781430239543

Hello,
Please also see Oracle MOS document 1376995.1.

Thanks,
Jenny
Jenny Tsai-Smith | Senior Director |
650.506.4661 | 650.743.8272
OracleServer Technologies Curriculum Development
<http://www.oracle.com/goto/oll>
400 Oracle Parkway, 4OP1088 | Redwood Shores, CA 94065

Thanks. Need to update this doc:
Reference List of Critical Patch Update Availability(CPU) and Patch Set Update (PSU) Documents For Oracle Database and Fusion Middleware Product [ID 783141.1] Nothing on cpujan2012 here.

Found it here:
Critical Patch Update January 2010 Patch Availability Document for Oracle Products [ID 967472.1]

followup article:
http://www.infoworld.com/d/security/the-oracle-flaw-clarifications-and-more-information-184775?page=0,0&source=IFWNLE_nlt_wrapup_2012-01-23

When I looked at this last week (and again after readings Riyaj's excellent
blog regarding the soft limit) I was able to bump my SCN to 12 trillion
(the number of seconds since 1/1/2088 * 16384) . After which my SCN growth
was throttled at 16K per second. Fine, I feel safe. But then I thought to
myself what happens if I fast forward my system date so far in the future I
no longer have a limit and then increase my SCN accordingly?
SQL> select to_date('01/01/1988', 'MM/DD/YYYY') +
((281474976710656/16384)/24/60/60) newscn from dual;

NEWSCN
--------------------
2532-MAY-29 01:53:04 <-- theoretical date in which I can advance my SCN to
the max

SQL>

[[email protected] ~]# date -s "29 MAY 2532 01:53:04"
Thu May 29 01:53:04 EST 2532

SQL> select sysdate from dual;

SYSDATE
--------------------
1988-JAN-01 00:01:32

SQL> !date
Thu May 29 01:54:56 EST 2532

1988 again. Deja vu. My date wrapped. I lowered my time a bit:

[[email protected] ~]# date -s "29 MAY 2400 01:53:04"
Mon May 29 01:53:04 EST 2400

SQL> select sysdate from dual;

SYSDATE
--------------------
1992-FEB-07 06:28:53

Definitely wrapping, but then I remembered there was an "end date" to
Oracle. Found my way to :

*OERR: ORA 1513 invalid current time returned by operating system [ID
18913.1]*

Which implies that I should get an error if the OS date is not between 1988
and 2121. I did not get an error but my database SCNs seem to be safe for
now....

Andy