Oracle SCN issue

Past few day, since Oracle has lunched Jan security Patch there is amazing sort of hype in the market regading the issue revealed with Oracle Architecture. 


ISSUE: 


At the core of the issue is the System Change Number (SCN) in Oracle. This is a number that increments sequentially with every database commit: inserts, updates, and deletes.  Its like a clock timer to check all synchonizatio. Its very crucial to normal Oracle database operation. SCN is the key to maintaining data consistency. So it keep on increasing everytime. 


When Oracle databases link to each other, maintaining data consistency requires them to synchronize to a common SCN. This is necessarily the highest SCN carried by any participating Oracle database instance because the SCN clock cannot run backward -- so database linking causes the SCN in many databases to jump during normal operations. And only very basic permissions are required to make a connection that can cause one database to increment the SCN on another.


For normal  system add keeps on adding 16,384 SCN per second to highest soft limit. The architects of Oracle's flagship database application must have been well aware the SCN needed to be a massive integer. It is: a 48-bit number (281,474,976,710,656).In addition to that hard limit, there's a soft limit imposed by Oracle itself to ensure the SCN value at any given moment is not unreasonably high, which would indicate a database malfunction. 


Bug: 


Oracle has feature to support hot back. it is by ' begin backup mode'. The problem is that, due to a unknown reason, issuing the 'BEGIN BACKUP' command causes the SCN for the database instance to increase dramatically, so that the SCN continues to increase at an accelerated rate even after the 'END BACKUP' command is given. Thus, performing a hot backup can increase the SCN value by millions or billions quite quickly -- and that elevated growth continues unabated. Admins are unlikely even to notice this problem.


So when you have number of databases connected with database link (as already discussed higher SCN value will be maintain) and some connected DB is started backup, so it will make high increase in SCN for for just one DB that is having backup but also to all the DB's that is connect to it through DB link. 


Imagine for the data centre where we have 1000 of inter-connected DB's.  For RMAN, oracle this as bug as ID: 12371955. 


Patch: 


So on Oracle Security patch-Jan2012CPU, Oracle has provided patch for SCN issue. Oracel has also provided scnhealthcheck.sql which is will test with speed of SCN increment. 


You can also use below script to get current and Max limit SCN status. 


set line 120
col "%" for 999.9
col SOFT_LIMIT for 999,999,999,999,999
col CURRENT_SCN  for 999,999,999,999,999


select
   version,
   to_char(SYSDATE,'YYYY/MM/DD HH24:MI:SS') DATE_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)) Soft_limit,
    dbms_flashback.get_system_change_number CURRENT_SCN,
    dbms_flashback.get_system_change_number /
    (( ((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)) * 100 "%"
   from v$instance;