Shulou(Shulou.com)06/02 Report--

Http://blog.csdn.net/dodola/article/details/223235

The time to modify the system has no effect on the database itself, the database has been recorded by the scn number.

In-depth Research on the relationship between Oracle DB Server system time Modification problem and SCN; in-depth study on the relationship between Oracle DB Server system time Modification problem and SCN

A friend in the forum said that the time of the DB server system was often modified for 3 months (from 11 years to 10 years), and the error of starting DB was reported.

one。 Do a test. 1. 1. Close DB SQL > shutdown immediate Database closed. Database dismounted. ORACLE instance shut down.

1.2 modify system time 1.2.1 now time [root@singledb ~] # date Tue Jan 25 11:05:32 EST 2011

1.2.2 the modification time adjusts the time forward: [root@singledb ~] # date-s 1 Sat Jan 1 00:00:00 EST 2011 [root@singledb ~] # date Sat Jan 1 00:00:22 EST 2011

1.3 start 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 was no problem with startup, and there was no 600 error from netizens.

two。 SCN first explains the knowledge of SCN here. It is also stated in the previous blog: the relationship between RedoLog Checkpoint and SCN http://blog.csdn.net/tianlesoftware/archive/2010/01/24/5251916.aspx

SCN is a number that is automatically maintained by DBMS to accumulate increments when Oracle data is updated. When a transaction commit, LGWR writes the log buffer to redo log file, as well as the SCN synchronization of the transaction to redo log file (wait-until-completed). So when you commit transaction, the LGWR must complete the above behavior before the successful message is returned, otherwise you will not see the successful response message.

There is a table between the system time stamp and scn, that is, SMON_SCN_TIME under SYS.

Is the SQL > desc sys.smon_scn_time name 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 5 minutes, the system generates a match between the system timestamp and scn and stores it in the SYS.SMON_SCN_TIME table (Update operations are performed by the SMON process), which records the last 1440 matching records of the system timestamp and scn, because the table maintains only the last 1440 records, that is, the records in the last 5 days.

Explain that the system timestamp matches scn every 5 minutes. For example, SCN:339988 corresponds to 2011-01-25 17:00:00 SCN:339989 corresponds to 2011-01-25 17:05:00, and when querying SCN from 2011-01-25 17:00:00 to 2011-01-25 17:04:59, oracle will match it as SCN:339988.

View the correspondence between SCN and timestamp: select scn,to_char (time_dp,'yyyy-mm-dd hh34:mi:ss') from sys.smon_scn_time order by 2

Query the latest SCN: select dbms_flashback.get_system_change_number from dual of the current system

Two methods for the interchange between timestamp and SCN: select timestamp_to_scn (to_date ('2011-01-25 12 select timestamp_to_scn (' 2011-01-25 12 select timestamp_to_scn 1015 00mm hh34:mi:ss')) from dual

Select scn_to_timestamp (351277605) from dual

An article about the mapping relationship between Timestamp and SCN was found in Metalink. The 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.-- start with a WRAP of 0, that is, SCN_WRP=0. When BASE reaches its maximum, SCN_BAS becomes 0. 5%. At the same time, SCN_WRP increased 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-- the calculation formula of SCN. Here we see that SCN is calculated based on SCN_BAS. It has nothing to do with the system time. It's just convenient for us to perform operations, such as flashback recovery, and map SCN and system time every 5 minutes. After the mapping is completed, the SMON process writes the mapping to the SMON_SCN_ time table. You can think of SCN as time within Oracle.

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 be written before 10g. After searching metalink for a long time, there is only one difference, that is, more than 1440 records can be stored in smon_scn_time under 11g.

To insert a word here, 10g g means Grid.

three。 The relationship between modification system time and SCN through the analysis of the previous two sections, we can see that there is no direct relationship between modification system time and SCN. Tests are also done in the first section. For the DB server how to modify the time: (1) stop the application (2) stop the database (3) modify the time, but if it is not a special case, it is not recommended to modify. Stability comes first. Especially in the RAC environment, the time requirement is more stringent.

As I said here, modifying the system time and SCN have no direct impact, but in my tests, I found that they are related.

SQL > alter session set nls_date_format='yyyy-mm-dd hh34: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 hh34:mi:ss') time from sys.smon_scn_time order by 2 desc) where rownum select dbms_flashback.get_system_change_number from dual; GET_SYSTEM_CHANGE_NUMBER-945394

From the above query results, we find two problems: (1) the SCN (943223) in the smon_scn_ time table is less than the current SCN value of the system (945394). (2) the last update time in the smon_scn_ time table (2012-01-01 02:43:33) is greater than the current system time (2010-01-01 01:21:58).

From this we draw a conclusion: when the last update time of smon_scn_time is longer than the system time, SMON will not write the mapping result of SCN to TIMESTAMP to the sys.smon_scn_ time table.

If the mapping between SCN and TIMESTAMP cannot be written to the smon_scn_ time table, we cannot convert SCN to TIMESTAMP, and we cannot use timestamp for related operations, such as recovery. Reference: http://blog.csdn.net/tianlesoftware/archive/2010/12/30/6106178.aspx for recovery of different failures of Oracle

In order to verify the above conclusion, we change the system to be greater than the last update time of smon_scn_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 hh34:mi:ss') time from sys.smon_scn_time order by 2 desc) where rownum

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.