Sunday, January 8, 2012

No error detected to change OS time for oracle DB running on 11.2

[oracle@vmxdb05b ~]$ vi trigger_changes.sql
[oracle@vmxdb05b ~]$ sqlplus donghua/donghua

SQL*Plus: Release 11.2.0.3.0 Production on Sun Jan 8 12:42:07 2012

Copyright (c) 1982, 2011, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> truncate table t;

Table truncated.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@vmxdb05b ~]$ vi trigger_changes.sql
[oracle@vmxdb05b ~]$ sqlplus donghua/donghua

SQL*Plus: Release 11.2.0.3.0 Production on Sun Jan 8 12:42:26 2012

Copyright (c) 1982, 2011, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> @trigger_changes.sql
SQL> col CURR_TIME for a30
SQL> col next_time for a30
SQL> insert into t values (t_s1.nextval,systimestamp);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select ora_rowscn,id,curr_time from t;

ORA_ROWSCN ID CURR_TIME
---------- ---------- ------------------------------
1228384 23 08-JAN-12 12.42.30.484001 PM

SQL> select * from (
2 select sequence#,to_char(next_time,'dd-mon-yy hh24.mi.ss') next_time,rownum rn from v$archived_log order by sequence#)
3 where rn<11;

SEQUENCE# NEXT_TIME RN
---------- ------------------------------ ----------
3 25-nov-11 08.09.55 1
4 27-nov-11 17.25.37 2
5 27-nov-11 21.00.08 3
6 07-dec-11 22.05.11 4
7 11-dec-11 10.41.06 5
8 11-dec-11 11.11.00 6
9 08-jan-12 09.46.15 7
10 08-jan-12 09.57.38 8
11 08-jan-12 11.08.41 9
12 08-jan-12 11.09.56 10

10 rows selected.

SQL>

[grid@vmxdb05b ~]$ crsctl stop has

[root@vmxdb05b ~]# date
Sun Jan 8 12:45:02 SGT 2012
You have new mail in /var/spool/mail/root
[root@vmxdb05b ~]# date 01081446
Sun Jan 8 14:46:00 SGT 2012
[root@vmxdb05b ~]# date
Sun Jan 8 14:46:01 SGT 2012
[root@vmxdb05b ~]#


[grid@vmxdb05b ~]$ crsctl start has

SQL> @trigger_changes.sql
SQL> col CURR_TIME for a30
SQL> col next_time for a30
SQL> insert into t values (t_s1.nextval,systimestamp);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select ora_rowscn,id,curr_time from t;

ORA_ROWSCN ID CURR_TIME
---------- ---------- ------------------------------
1228384 23 08-JAN-12 12.42.30.484001 PM
1248897 24 08-JAN-12 02.48.55.368836 PM


SQL> select * from (
2 select sequence#,to_char(next_time,'dd-mon-yy hh24.mi.ss') next_time from v$archived_log order by sequence# desc )
3 where rownum<11
4 /

SEQUENCE# NEXT_TIME
---------- ------------------------------
19 08-jan-12 14.48.55
18 08-jan-12 14.48.55
17 08-jan-12 14.47.34
16 08-jan-12 12.42.30
15 08-jan-12 12.42.30
14 08-jan-12 12.17.48
13 08-jan-12 10.18.42
12 08-jan-12 11.09.56
11 08-jan-12 11.08.41
10 08-jan-12 09.57.38

10 rows selected.

[grid@vmxdb05b ~]$ crsctl stop has


[root@vmxdb05b ~]# date
Sun Jan 8 14:59:13 SGT 2012
[root@vmxdb05b ~]# date 01081259
Sun Jan 8 12:59:00 SGT 2012
[root@vmxdb05b ~]# date
Sun Jan 8 12:59:02 SGT 2012
[root@vmxdb05b ~]#

[grid@vmxdb05b ~]$ crsctl start has

SQL> @trigger_changes.sql
SQL> col CURR_TIME for a30
SQL> col next_time for a30
SQL> insert into t values (t_s1.nextval,systimestamp);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select ora_rowscn,id,curr_time from t;

ORA_ROWSCN ID CURR_TIME
---------- ---------- ------------------------------
1228384 23 08-JAN-12 12.42.30.484001 PM
1269374 25 08-JAN-12 01.04.57.367069 PM
1248897 24 08-JAN-12 02.48.55.368836 PM

SQL> select * from (
2 select sequence#,to_char(next_time,'dd-mon-yy hh24.mi.ss') next_time from v$archived_log order by sequence# desc)
3 where rownum<11;

SEQUENCE# NEXT_TIME
---------- ------------------------------
20 08-jan-12 13.01.12
19 08-jan-12 14.48.55
18 08-jan-12 14.48.55
17 08-jan-12 14.47.34
16 08-jan-12 12.42.30
15 08-jan-12 12.42.30
14 08-jan-12 12.17.48
13 08-jan-12 10.18.42
12 08-jan-12 11.09.56
11 08-jan-12 11.08.41

10 rows selected.

[root@vmxdb05b ~]# date
Sun Jan 8 13:06:06 SGT 2012
[root@vmxdb05b ~]# date 01091306
Mon Jan 9 13:06:00 SGT 2012
[root@vmxdb05b ~]# date
Mon Jan 9 13:06:01 SGT 2012


SQL>
SQL> @trigger_changes.sql
SQL> set echo on
SQL> col CURR_TIME for a30
SQL> col next_time for a30
SQL> insert into t values (t_s1.nextval,systimestamp);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select ora_rowscn,id,curr_time from t;

ORA_ROWSCN ID CURR_TIME
---------- ---------- ------------------------------
1228384 23 08-JAN-12 12.42.30.484001 PM
1269605 25 08-JAN-12 01.04.57.367069 PM
1269605 26 09-JAN-12 01.06.31.790207 PM
1248897 24 08-JAN-12 02.48.55.368836 PM

SQL> select * from (
2 select sequence#,to_char(next_time,'dd-mon-yy hh24.mi.ss') next_time from v$archived_log order by sequence# desc)
3 where rownum<11;

SEQUENCE# NEXT_TIME
---------- ------------------------------
22 08-jan-12 13.04.57
21 08-jan-12 13.04.57
20 08-jan-12 13.01.12
19 08-jan-12 14.48.55
18 08-jan-12 14.48.55
17 08-jan-12 14.47.34
16 08-jan-12 12.42.30
15 08-jan-12 12.42.30
14 08-jan-12 12.17.48
13 08-jan-12 10.18.42

10 rows selected.

SQL>
SQL> @trigger_changes.sql
SQL> set echo on
SQL> col CURR_TIME for a30
SQL> col next_time for a30
SQL> insert into t values (t_s1.nextval,systimestamp);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select ora_rowscn,id,curr_time from t;

ORA_ROWSCN ID CURR_TIME
---------- ---------- ------------------------------
1228384 23 08-JAN-12 12.42.30.484001 PM
1269683 25 08-JAN-12 01.04.57.367069 PM
1269683 26 09-JAN-12 01.06.31.790207 PM
1269683 27 09-JAN-12 01.06.59.125860 PM
1248897 24 08-JAN-12 02.48.55.368836 PM

SQL> select * from (
2 select sequence#,to_char(next_time,'dd-mon-yy hh24.mi.ss') next_time from v$archived_log order by sequence# desc)
3 where rownum<11;

SEQUENCE# NEXT_TIME
---------- ------------------------------
25 09-jan-12 13.07.01
24 09-jan-12 13.06.34
23 09-jan-12 13.06.34
22 08-jan-12 13.04.57
21 08-jan-12 13.04.57
20 08-jan-12 13.01.12
19 08-jan-12 14.48.55
18 08-jan-12 14.48.55
17 08-jan-12 14.47.34
16 08-jan-12 12.42.30

10 rows selected.

SQL>

[root@vmxdb05b ~]# date
Mon Jan 9 13:09:48 SGT 2012
[root@vmxdb05b ~]# date 01081310
Sun Jan 8 13:10:00 SGT 2012
[root@vmxdb05b ~]# date
Sun Jan 8 13:10:00 SGT 2012


SQL> @@trigger_changes.sql
SQL> col CURR_TIME for a30
SQL> col next_time for a30
SQL> insert into t values (t_s1.nextval,systimestamp);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select ora_rowscn,id,curr_time from t;

ORA_ROWSCN ID CURR_TIME
---------- ---------- ------------------------------
1228384 23 08-JAN-12 12.42.30.484001 PM
1269683 25 08-JAN-12 01.04.57.367069 PM
1269683 26 09-JAN-12 01.06.31.790207 PM
1269683 27 09-JAN-12 01.06.59.125860 PM
1248897 24 08-JAN-12 02.48.55.368836 PM
1270330 28 08-JAN-12 01.10.21.538665 PM

6 rows selected.

SQL> select * from (
2 select sequence#,to_char(next_time,'dd-mon-yy hh24.mi.ss') next_time from v$archived_log order by sequence# desc)
3 where rownum<11;

SEQUENCE# NEXT_TIME
---------- ------------------------------
26 09-jan-12 13.07.04
25 09-jan-12 13.07.01
24 09-jan-12 13.06.34
23 09-jan-12 13.06.34
22 08-jan-12 13.04.57
21 08-jan-12 13.04.57
20 08-jan-12 13.01.12
19 08-jan-12 14.48.55
18 08-jan-12 14.48.55
17 08-jan-12 14.47.34

10 rows selected.

SQL>
SQL>
SQL> @trigger_changes.sql
SQL> set echo on
SQL> col CURR_TIME for a30
SQL> col next_time for a30
SQL> insert into t values (t_s1.nextval,systimestamp);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select ora_rowscn,id,curr_time from t;

ORA_ROWSCN ID CURR_TIME
---------- ---------- ------------------------------
1228384 23 08-JAN-12 12.42.30.484001 PM
1269683 25 08-JAN-12 01.04.57.367069 PM
1269683 26 09-JAN-12 01.06.31.790207 PM
1269683 27 09-JAN-12 01.06.59.125860 PM
1248897 24 08-JAN-12 02.48.55.368836 PM
1270344 28 08-JAN-12 01.10.21.538665 PM
1270344 29 08-JAN-12 01.10.24.453826 PM

7 rows selected.

SQL> select * from (
2 select sequence#,to_char(next_time,'dd-mon-yy hh24.mi.ss') next_time from v$archived_log order by sequence# desc)
3 where rownum<11;

SEQUENCE# NEXT_TIME
---------- ------------------------------
28 08-jan-12 13.10.21
27 08-jan-12 13.10.21
26 09-jan-12 13.07.04
25 09-jan-12 13.07.01
24 09-jan-12 13.06.34
23 09-jan-12 13.06.34
22 08-jan-12 13.04.57
21 08-jan-12 13.04.57
20 08-jan-12 13.01.12
19 08-jan-12 14.48.55

10 rows selected.

SQL>
SQL>

[oracle@vmxdb05b trace]$ cat orcl05b_vktm_9981.trc
Trace file /u01/app/oracle/diag/rdbms/orcl05b/orcl05b/trace/orcl05b_vktm_9981.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1
System name: Linux
Node name: vmxdb05b.lab.dbaglobe.com
Release: 2.6.32-71.29.1.el6.x86_64
Version: #1 SMP Mon Jun 27 19:49:27 BST 2011
Machine: x86_64
VM name: VMWare Version: 6
Instance name: orcl05b
Redo thread mounted by this instance: 0
Oracle process number: 4
Unix process pid: 9981, image: oracle@vmxdb05b.lab.dbaglobe.com (VKTM)


*** 2012-01-08 13:01:04.109
*** SESSION ID:(4.1) 2012-01-08 13:01:04.109
*** CLIENT ID:() 2012-01-08 13:01:04.109
*** SERVICE NAME:() 2012-01-08 13:01:04.109
*** MODULE NAME:() 2012-01-08 13:01:04.109
*** ACTION NAME:() 2012-01-08 13:01:04.109

kstmmainvktm: succeeded in setting elevated priority
highres_enabled

*** 2012-01-08 13:01:04.109
VKTM running at (1)millisec precision with DBRM quantum (100)ms
[Start] HighResTick = 1325998864109366
kstmrmtickcnt = 0 : ksudbrmseccnt[0] = 1325998864

*** 2012-01-09 13:06:00.000
kstmchkdrift (kstmhighrestimecntkeeper:highres): Time jumped forward by (86384713465)usec at (1326085560000441) whereas (1000000) is allowed
[End] HighResTick = 1326085758255964

*** 2012-01-09 13:09:18.257
kstmrmtickcnt = 5066 : ksudbrmseccnt[510] = 1326085758