编程知识 cdmana.com

In depth study on the relationship between Oracle DB server system time modification and SCN

   A friend in the forum said he would DB  The server system is often modified after time 3 Months ( from 11 Year changed to 10 year ), start-up DB newspaper 600 Error of .

 

One Do a test first

1.1  close DB

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

 

1.2  Modify system time

1.2.1  present time

[root@singledb ~]# date

Tue Jan 25 11:05:32 EST 2011

 

1.2.2  Modification time

Adjust the time ahead :

[root@singledb ~]# date -s  1/1/2011      

Sat Jan  1 00:00:00 EST 2011

[root@singledb ~]# date

Sat Jan  1 00:00:22 EST 2011

 

1.3 start-up DB

SQL> startup

ORACLE instance started.

Total System Global Area  360710144 bytes

Fixed Size                  1219424 bytes

Variable Size             117441696 bytes

Database Buffers          239075328 bytes

Redo Buffers                2973696 bytes

Database mounted.

Database opened.

 

There is no problem starting , I haven't met any netizens 600 error .

 

Two .  SCN

        Right here first SCN  Explain your knowledge . Previous blog It's also explained in :

       RedoLog Checkpoint  and  SCN Relationship

       http://blog.csdn.net/tianlesoftware/archive/2010/01/24/5251916.aspx

 

 

 

       SCN When Oracle After data update , from DBMS Automatically maintain to accumulate an increasing number .  When a business commit when ,LGWR Will log buffer write in redo log file, At the same time, the of the transaction will also be  SCN Synchronous write to redo log file Inside (wait-until-completed). So when you commit transaction when ,  Before the successful message returns ,LGWR You must complete the above actions first , Otherwise, you will not see the response message of successful submission .

 

System time stamp and scn  There is a table between , namely SYS  Under the SMON_SCN_TIME.

 

SQL> desc sys.smon_scn_time

  name                         Is it empty ?  type

 ----------------------------------------- --------    ---------------------------

 THREAD                               NUMBER

 TIME_MP                              NUMBER

 TIME_DP                              DATE

 SCN_WRP                             NUMBER

 SCN_BAS                              NUMBER

 

 NUM_MAPPINGS                       NUMBER

 TIM_SCN_MAP                         RAW(1200)

 SCN                                   NUMBER

 ORIG_THREAD                         NUMBER

 

        every other minute , The system generates a system time stamp and scn  And store it in SYS.SMON_SCN_TIME  surface ( from SMON  Process Update operation ), This table records the latest 1440 A system time stamp and scn  Matching records of , Because this table only maintains the most recent 1440  Bar record , The recent 5  Records within days .

 

        Yes   System time stamp and scn  Every Every minute   Make a statement , such as :

       SCN:339988  Corresponding  2011-01-25 17:00:00

       SCN:339989 Corresponding 2011-01-25 17:05:00,

        When the query 2011-01-25 17:00:00  To 2011-01-25 17:04:59  At this point in time SCN when ,oracle  Will match it to SCN:339988.

 

 

see SCN  and  timestamp  The correspondence between :

select scn,to_char(time_dp,'yyyy-mm-dd hh24:mi:ss') from sys.smon_scn_time order by 2;

 

 

Query the latest information in the current system SCN:

       select dbms_flashback.get_system_change_number from dual;

 

timestamp  And  SCN  Interchangeable 2 A way :

select timestamp_to_scn(to_date('2011-01-25 12:10:00','yyyy-mm-dd hh24:mi:ss')) from dual;

 

select scn_to_timestamp(351277605) from dual;

 

 

stay Metalink  I found an article Timestamp And SCN  Articles on the mapping relationship between , Original reference :

       How to map SCN with Timestamp before 10g [ID 365536.1]

       http://blog.csdn.net/tianlesoftware/archive/2011/01/25/6163757.aspx

 

       However, in earlier releases, while there is a system object - SYS.SMON_SCN_TIME that will provide the SCN to TIME mapping information.   There is no conversion facility provided. 

 

       SYS.SMON_SCN_TIME will have a maximum of 1440 rows and each record will be for a 5 minute period.  Oracle maintains this information for maximum of 5 days after which the records will be recycled.

 

This means that data is stored 12 times per hour * 24 hours * 5 days=1440 rows. 

 

SCN value is stored internally as :

i. SCN_wrap

ii. SCN_base

 

       Whenever the SCN is incremented, the BASE component is incremented first unil it reaches it maximum.  Once the BASE reaches the maximum value allowed, it is initialized to zero again after incrementing the WRAP by 1.

-- At the beginning of the WRAP  by 0, namely SCN_WRP=0.  When BASE After growing to the maximum ,SCN_BAS Turn into 0.  meanwhile SCN_WRP  Growth to 1

 

Using this logic, we can calculate the timestamp of the SCN as follows:

(SCN_WRP * 4294967296) + SCN_BAS should give us the SCN in the number format

--SCN  Calculation formula , Here we see SCN  It's based on SCN_BAS Calculated . It has nothing to do with system time .  It's just convenient for us to operate , If flashback recovery , And will be SCN  and   System time every 5 Mapping once a minute .  After mapping , from SMON The process writes the mapping relationship to SMON_SCN_TIME surface .  You can put SCN  As a Oracle  Internal time .

 

To get the time/date for an SCN value in 9i, use the following example:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

-- (a) Get the current SCN base.

SQL>select max(scn_bas) SCN_BASE from smon_scn_time;

 

1603342197

 

--(b) Get the complete SCN and the timestamp.

SQL> alter session set NLS_DATE_FORMAT='DD-MON-YY HH:MI:SS';

 

SQL> select time_dp TIMESTAMP, scn_wrp*4294967296+SCN_bas SCN from smon_scn_time where scn_bas='1603342197';

 

TIMESTAMP                 SCN

------------------            ----------

28-JUL-06 05:31:08        8252235517813

 

This article will focus on 10g Previous .  stay metalink Searched the for a long time , Only one difference was found , Namely 11g Next smon_scn_time  The stored records can exceed 1440 strip .

 

Insert a sentence here ,10g Of g  Express Grid.

 

 

3、 ... and Modify the system time and SCN  Relationship

        Through the front 2 Section analysis , It can be seen that the system modification time and SCN No direct relationship . In the first section, it is also tested .  about DB  How the server modifies the time :

       (1) Stop applying

       (2) Stop database

       (3) Modification time

        however , If it's not special , Modification is not recommended . Stability first . In especial RAC  Environmental Science , More strict on time .

 

        I said here , Modify system time and SCN  There is no direct impact on , But I was in the test , Find that they are connected .

 

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

 

SQL> select sysdate from dual;

SYSDATE

 

-------------------

2010-01-01 01:21:58

 

SQL> select * from ( select scn,to_char(time_dp,'yyyy-mm-dd hh24:mi:ss') time from sys.smon_scn_time order by 2 desc) where rownum<10;

       SCN TIME

---------- -------------------

    943223 2012-01-01 02:43:33

    943058 2012-01-01 02:38:14

    942811 2012-01-01 02:33:02

    922652 2012-01-01 00:01:52

    922182 2012-01-01 00:00:45

    920862 2011-01-25 11:46:21

    920717 2011-01-25 11:41:17

    920571 2011-01-25 11:36:38

    919996 2011-01-25 11:31:21

9 rows selected.

 

SQL>  select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

------------------------

       945394

 

From the above query results, we find two problems :

(1)smon_scn_time In the table SCN(943223)  Less than the current of the system SCN value (945394).

(2)smon_scn_time Last update time in the table (2012-01-01 02:43:33) Greater than the current system time (2010-01-01 01:21:58).

 

        From this, we draw a conclusion : When smon_scn_time When the last update time is greater than the system time ,SMON Will not SCN  And TIMESTAMP The mapping result of is written to sys.smon_scn_time In the table .

 

        If SCN  And TIMESTAMP The mapping of cannot be written to smon_scn_time In the table , We can't do SCN  And TIMESTAMP transformation , You can't take advantage of timestamp Carry out relevant operations , Such as recovery . Reference resources :

       Oracle  Recovery schemes for different faults

       http://blog.csdn.net/tianlesoftware/archive/2010/12/30/6106178.aspx

 

 

 

To verify the above conclusion , We changed the system to be larger than smon_scn_time Last update time .

 

SQL>  select sysdate from dual;

SYSDATE

-------------------

2012-02-01 00:00:16

 

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

------------------------

  946462

 

SQL> select * from ( select scn,to_char(time_dp,'yyyy-mm-dd hh24:mi:ss') time from sys.smon_scn_time order by 2 desc) where rownum<10;

 

       SCN TIME

---------- -------------------

    945872 2012-02-01 00:00:51  

    943223 2012-01-01 02:43:33

    943058 2012-01-01 02:38:14

    942811 2012-01-01 02:33:02

    922652 2012-01-01 00:01:52

    922182 2012-01-01 00:00:45

    920862 2011-01-25 11:46:21

    920717 2011-01-25 11:41:17

    920571 2011-01-25 11:36:38

 

This time successfully written smon_scn_time surface . thus it can be seen :

 1) The system time will be changed later ( If you follow 2011 Year changed to 2012 year ), Yes DB  No impact , Maybe some applications may be affected .

 2) Change the system time forward ( If you follow 2011 Year changed to 2010 year ), that SMON  Will not SCN  And timestamp write in smon_scn_time surface .

 

        So for the production system , Before the system goes online, the school team should have a good system time , Based on the principle of stability first , It is not recommended to modify after going online DB  It's time for the server .

版权声明
本文为[wx5caecf2ed0645]所创,转载请带上原文链接,感谢
https://cdmana.com/2021/08/20210809181600768b.html

Scroll to Top