Oracle’s CPU2012Jan addresses this problem. Oracle Document 1374524.1
To understand the problem it is necessary to define SCN headroom.
The SCN value is an ever increasing value for a database. The threshold for this SCN value is also ever increasing number too. The max limit of SCN is defined by the number of seconds past the year 1988 X 16k, hence as time passes this value also keeps increasing. This is the soft-limit. There is also a hard limit of 218 trillion.
It is also necessary to understand that when a DB link operation between two databases, Oracle automatically selects the higher SCN out of the two databases and syncs them between the two databases.
--On first database
SQL> select name from V$database;
NAME
---------
Database1
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
398829972857 -- note the SCN number
--On second database
SQL> select name from V$database;
NAME
---------
Database2
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
391409782850
SQL> select sysdate from dual@Database1.WORLD; -- DB link query
SYSDATE
---------
19-JAN-12
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
398829973230 -- notice the significant increase for synchronizing the SCNs
InfoWorld also documents that Oracle Hot backups can significantly increase the scn generation. To find the headroom at any given time use the below query.
select
version,
to_char(SYSDATE,'YYYY/MM/DD HH24:MI:SS') CURR_TIME,
((((
((to_number(to_char(sysdate,'YYYY'))-1988)*12*31*24*60*60) + ((to_number(to_char(sysdate,'MM'))-1)*31*24*60*60) +
(((to_number(to_char(sysdate,'DD'))-1))*24*60*60) + (to_number(to_char(sysdate,'HH24'))*60*60) +
(to_number(to_char(sysdate,'MI'))*60) + (to_number(to_char(sysdate,'SS')))) * (16*1024))
- dbms_flashback.get_system_change_number)/ (16*1024*60*60*24)) scn_headroom
from v$instance;
See Oracle document : ID 1393363.1
Reading through Oracle’ patch if the headroom is less than 62, urgent attention is required. i.e. Apply patch immediately and contact Oracle.
To ensure that the headroom is not being reached rapidly, run the below query.
select max(first_change#)-min(first_change#), to_char(first_time,'YYYY-MM-DD') from V$log_history
group by to_char(first_time,'YYYY-MM-DD')
order by 2 desc;
Sometimes there may be a significant jump in the SCN which can be associated with a DBlink activity. Hot backups can also significantly increase the SCN generation rate as detailed by InfoWorld
If you notice that the SCN rate is very high there is plenty to worry about. If the high SCN rate database is interconnected via DBlink with other databases it can bring down a lot of databases together.
Along with the patch, Oracle have also added a hidden parameter _external_scn_rejection_threshold_hours which needs to be set to 24. There is not much details about this hidden parameter , but it might have something to do with the distributed transaction SCN synch.
As always please ensure you have read and understood the Oracle documentation before applying.
