oracle 11.2.0.1以及oracle 11.2.0.4数据库的时区time zone相关概念之一

前言
  近期需要实施oracle 11.2.0.1迁移数据库至11.2.0.4,涉及数据库的time zone时区概念,数据库迁移升级涉及概念及组件诸多,且复杂多变,必须整合全面,才能制订科学
高效的数据库迁移技术方案,减少数据库停机时间及相关故障。  

参考资料
oracle upgrade guide

结论
1,v$timezone_file可以查询数据库的时区版本

2,11.2.0.1与11.2.0.4之数据库时区逻辑相同

3,11g数据库时区文件路径在ORACLE_HOME/oracore/zoneinfo

4,ORACLE_HOME/oracore/zoneinfo与ORACLE_HOME/oracore/zoneinfo/big和ORACLE_HOME/oracore/zoneinfo/little的内容相同
   

5,其所属内容又分为2种类型,一则为timezlrg_version.dat,二则为timezone_version.dat

6,数据库的时区文件千万不能删除,否则重启数据库会报错ora-600错误

SQL> startup
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size                  2254952 bytes
Variable Size            2080376728 bytes
Database Buffers           50331648 bytes
Redo Buffers                4923392 bytes
Database mounted.
ORA-00600: internal error code, arguments: [opiodr: call 2], [], [], [], [],
[], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [qcisSetPlsqlCtx:tzi init], [], [],
[], [], [], [], [], [], [], [], []

7,如果删除了数据库当前使用的时区文件,数据库仍可以正常登陆且DML 时区相关的表

测试明细

1,数据库版本
SQL> select * from v$version where rownum=1;

BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production

2,11.2.0.4数据库的时区版本
SQL> select filename,version from v$timezone_file;

FILENAME                VERSION
——————– ———-
timezlrg_14.dat              14

3, 11.2.0.4数据库的时区文件
[oracle@mygirl ~]$ locate timezlrg_14.dat
/oracle/product/11.2.0/db_1/oracore/zoneinfo/timezlrg_14.dat
/oracle/product/11.2.0/db_1/oracore/zoneinfo/big/timezlrg_14.dat
/oracle/product/11.2.0/db_1/oracore/zoneinfo/little/timezlrg_14.dat

4,11.2.0.4数据库的时区文件所在路径,总结如下:
   1,ORACLE_HOME/oracore/zoneinfo与ORACLE_HOME/oracore/zoneinfo/big和ORACLE_HOME/oracore/zoneinfo/little的内容相同
   2, 其所属内容又分为2种类型,一则为timezlrg_version.dat,二则为timezone_version.dat
[oracle@mygirl zoneinfo]$ pwd
/oracle/product/11.2.0/db_1/oracore/zoneinfo
[oracle@mygirl zoneinfo]$ ll
total 13340
drwxr-xr-x. 2 oracle oinstall   4096 May  4 23:47 big
drwxr-xr-x. 2 oracle oinstall   4096 May  4 23:47 little
-rw-r–r–. 1 oracle oinstall   6068 Jun  7  2010 readme.txt
-rw-r–r–. 1 oracle oinstall  31525 Jun  7  2010 timezdif.csv
-rw-r–r–. 1 oracle oinstall 792894 Jul 16  2013 timezlrg_10.dat
-rw-r–r–. 1 oracle oinstall 787272 Jul 16  2013 timezlrg_11.dat
-rw-r–r–. 1 oracle oinstall 785621 Jul 16  2013 timezlrg_12.dat
-rw-r–r–. 1 oracle oinstall 782475 Jul 16  2013 timezlrg_13.dat
-rw-r–r–. 1 oracle oinstall 791430 Jul 16  2013 timezlrg_14.dat
-rw-r–r–. 1 oracle oinstall 493675 Jul 16  2013 timezlrg_1.dat
-rw-r–r–. 1 oracle oinstall 507957 Jul 16  2013 timezlrg_2.dat
-rw-r–r–. 1 oracle oinstall 527717 Jul 16  2013 timezlrg_3.dat
-rw-r–r–. 1 oracle oinstall 531137 Jul 16  2013 timezlrg_4.dat
-rw-r–r–. 1 oracle oinstall 587487 Jul 16  2013 timezlrg_5.dat
-rw-r–r–. 1 oracle oinstall 586750 Jul 16  2013 timezlrg_6.dat
-rw-r–r–. 1 oracle oinstall 601242 Jul 16  2013 timezlrg_7.dat
-rw-r–r–. 1 oracle oinstall 616723 Jul 16  2013 timezlrg_8.dat
-rw-r–r–. 1 oracle oinstall 801410 Jul 16  2013 timezlrg_9.dat
-rw-r–r–. 1 oracle oinstall 345637 Jul 16  2013 timezone_10.dat
-rw-r–r–. 1 oracle oinstall 345356 Jul 16  2013 timezone_11.dat
-rw-r–r–. 1 oracle oinstall 345024 Jul 16  2013 timezone_12.dat
-rw-r–r–. 1 oracle oinstall 344425 Jul 16  2013 timezone_13.dat
-rw-r–r–. 1 oracle oinstall 344448 Jul 16  2013 timezone_14.dat
-rw-r–r–. 1 oracle oinstall 274427 Jul 16  2013 timezone_1.dat
-rw-r–r–. 1 oracle oinstall 274900 Jul 16  2013 timezone_2.dat
-rw-r–r–. 1 oracle oinstall 286651 Jul 16  2013 timezone_3.dat
-rw-r–r–. 1 oracle oinstall 286264 Jul 16  2013 timezone_4.dat
-rw-r–r–. 1 oracle oinstall 286310 Jul 16  2013 timezone_5.dat
-rw-r–r–. 1 oracle oinstall 286217 Jul 16  2013 timezone_6.dat
-rw-r–r–. 1 oracle oinstall 286815 Jul 16  2013 timezone_7.dat
-rw-r–r–. 1 oracle oinstall 302100 Jul 16  2013 timezone_8.dat
-rw-r–r–. 1 oracle oinstall 351525 Jul 16  2013 timezone_9.dat
[oracle@mygirl zoneinfo]$ 

[oracle@mygirl zoneinfo]$ cd big
[oracle@mygirl big]$ ll
total 13292
-rw-r–r–. 1 oracle oinstall 792894 May 26  2009 timezlrg_10.dat
-rw-r–r–. 1 oracle oinstall 787272 Jun 12  2009 timezlrg_11.dat
-rw-r–r–. 1 oracle oinstall 785621 Sep 17  2009 timezlrg_12.dat
-rw-r–r–. 1 oracle oinstall 782475 Nov 30  2009 timezlrg_13.dat
-rw-r–r–. 1 oracle oinstall 791430 Jun  7  2010 timezlrg_14.dat
-rw-r–r–. 1 oracle oinstall 493675 Apr 29  2008 timezlrg_1.dat
-rw-r–r–. 1 oracle oinstall 507957 Apr 29  2008 timezlrg_2.dat
-rw-r–r–. 1 oracle oinstall 527717 Jan 30  2009 timezlrg_3.dat
-rw-r–r–. 1 oracle oinstall 531137 Apr 29  2008 timezlrg_4.dat
-rw-r–r–. 1 oracle oinstall 587487 Apr 29  2008 timezlrg_5.dat
-rw-r–r–. 1 oracle oinstall 586750 Apr 29  2008 timezlrg_6.dat
-rw-r–r–. 1 oracle oinstall 601242 Apr 29  2008 timezlrg_7.dat
-rw-r–r–. 1 oracle oinstall 616723 Apr 29  2008 timezlrg_8.dat
-rw-r–r–. 1 oracle oinstall 801410 May 23  2009 timezlrg_9.dat
-rw-r–r–. 1 oracle oinstall 345637 May 26  2009 timezone_10.dat
-rw-r–r–. 1 oracle oinstall 345356 Jun 12  2009 timezone_11.dat
-rw-r–r–. 1 oracle oinstall 345024 Sep 17  2009 timezone_12.dat
-rw-r–r–. 1 oracle oinstall 344425 Nov 30  2009 timezone_13.dat
-rw-r–r–. 1 oracle oinstall 344448 Jun  7  2010 timezone_14.dat
-rw-r–r–. 1 oracle oinstall 274427 Apr 29  2008 timezone_1.dat
-rw-r–r–. 1 oracle oinstall 274900 Apr 29  2008 timezone_2.dat
-rw-r–r–. 1 oracle oinstall 286651 Jan 30  2009 timezone_3.dat
-rw-r–r–. 1 oracle oinstall 286264 Apr 29  2008 timezone_4.dat
-rw-r–r–. 1 oracle oinstall 286310 Apr 29  2008 timezone_5.dat
-rw-r–r–. 1 oracle oinstall 286217 Apr 29  2008 timezone_6.dat
-rw-r–r–. 1 oracle oinstall 286815 Apr 29  2008 timezone_7.dat
-rw-r–r–. 1 oracle oinstall 302100 Apr 29  2008 timezone_8.dat
-rw-r–r–. 1 oracle oinstall 351525 May 23  2009 timezone_9.dat
[oracle@mygirl big]$ 

[oracle@mygirl big]$ cd ../little/
[oracle@mygirl little]$ pwd
/oracle/product/11.2.0/db_1/oracore/zoneinfo/little
[oracle@mygirl little]$ ll
total 13292
-rw-r–r–. 1 oracle oinstall 792894 May 26  2009 timezlrg_10.dat
-rw-r–r–. 1 oracle oinstall 787272 Jun 12  2009 timezlrg_11.dat
-rw-r–r–. 1 oracle oinstall 785621 Sep 17  2009 timezlrg_12.dat
-rw-r–r–. 1 oracle oinstall 782475 Nov 30  2009 timezlrg_13.dat
-rw-r–r–. 1 oracle oinstall 791430 Jun  7  2010 timezlrg_14.dat
-rw-r–r–. 1 oracle oinstall 493675 Apr 29  2008 timezlrg_1.dat
-rw-r–r–. 1 oracle oinstall 507957 Apr 29  2008 timezlrg_2.dat
-rw-r–r–. 1 oracle oinstall 527717 Jan 30  2009 timezlrg_3.dat
-rw-r–r–. 1 oracle oinstall 531137 Apr 29  2008 timezlrg_4.dat
-rw-r–r–. 1 oracle oinstall 587487 Apr 29  2008 timezlrg_5.dat
-rw-r–r–. 1 oracle oinstall 586750 Apr 29  2008 timezlrg_6.dat
-rw-r–r–. 1 oracle oinstall 601242 Apr 29  2008 timezlrg_7.dat
-rw-r–r–. 1 oracle oinstall 616723 Apr 29  2008 timezlrg_8.dat
-rw-r–r–. 1 oracle oinstall 801410 May 23  2009 timezlrg_9.dat
-rw-r–r–. 1 oracle oinstall 345637 May 26  2009 timezone_10.dat
-rw-r–r–. 1 oracle oinstall 345356 Jun 12  2009 timezone_11.dat
-rw-r–r–. 1 oracle oinstall 345024 Sep 17  2009 timezone_12.dat
-rw-r–r–. 1 oracle oinstall 344425 Nov 30  2009 timezone_13.dat
-rw-r–r–. 1 oracle oinstall 344448 Jun  7  2010 timezone_14.dat
-rw-r–r–. 1 oracle oinstall 274427 Apr 29  2008 timezone_1.dat
-rw-r–r–. 1 oracle oinstall 274900 Apr 29  2008 timezone_2.dat
-rw-r–r–. 1 oracle oinstall 286651 Jan 30  2009 timezone_3.dat
-rw-r–r–. 1 oracle oinstall 286264 Apr 29  2008 timezone_4.dat
-rw-r–r–. 1 oracle oinstall 286310 Apr 29  2008 timezone_5.dat
-rw-r–r–. 1 oracle oinstall 286217 Apr 29  2008 timezone_6.dat
-rw-r–r–. 1 oracle oinstall 286815 Apr 29  2008 timezone_7.dat
-rw-r–r–. 1 oracle oinstall 302100 Apr 29  2008 timezone_8.dat
-rw-r–r–. 1 oracle oinstall 351525 May 23  2009 timezone_9.dat
[oracle@mygirl little]$ 

5,oracle 11.2.0.1数据库版本
SQL> select * from v$version where rownum=1;

BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production

6,oracle 11.2.0.1之时区版本
SQL> select filename,version from v$timezone_file;

FILENAME                VERSION
——————– ———-
timezlrg_11.dat              11

7,oracle 11.2.0.1的时区文件逻辑同于11.2.0.4
[oracle@shanghai dbs]$ locate timezlrg_11.dat
/oracle/product/11.2.0/db_1/oracore/zoneinfo/timezlrg_11.dat
/oracle/product/11.2.0/db_1/oracore/zoneinfo/big/timezlrg_11.dat
/oracle/product/11.2.0/db_1/oracore/zoneinfo/little/timezlrg_11.dat

[oracle@shanghai zoneinfo]$ pwd
/oracle/product/11.2.0/db_1/oracore/zoneinfo
[oracle@shanghai zoneinfo]$ ll
total 10196
drwxr-xr-x 2 oracle oinstall   4096 Jul  2  2014 big
drwxr-xr-x 2 oracle oinstall   4096 Jul  2  2014 little
-rw-r–r– 1 oracle oinstall   5725 Jun 12  2009 readme.txt
-rw-r–r– 1 oracle oinstall  25681 Jul 16  2009 timezdif.csv
-rw-r–r– 1 oracle oinstall 792894 Aug  2  2009 timezlrg_10.dat
-rw-r–r– 1 oracle oinstall 787272 Aug  2  2009 timezlrg_11.dat
-rw-r–r– 1 oracle oinstall 493675 Aug  2  2009 timezlrg_1.dat
-rw-r–r– 1 oracle oinstall 507957 Aug  2  2009 timezlrg_2.dat
-rw-r–r– 1 oracle oinstall 527717 Aug  2  2009 timezlrg_3.dat
-rw-r–r– 1 oracle oinstall 531137 Aug  2  2009 timezlrg_4.dat
-rw-r–r– 1 oracle oinstall 587487 Aug  2  2009 timezlrg_5.dat
-rw-r–r– 1 oracle oinstall 586750 Aug  2  2009 timezlrg_6.dat
-rw-r–r– 1 oracle oinstall 601242 Aug  2  2009 timezlrg_7.dat
-rw-r–r– 1 oracle oinstall 616723 Aug  2  2009 timezlrg_8.dat
-rw-r–r– 1 oracle oinstall 801410 Aug  2  2009 timezlrg_9.dat
-rw-r–r– 1 oracle oinstall 345637 Aug  2  2009 timezone_10.dat
-rw-r–r– 1 oracle oinstall 345356 Aug  2  2009 timezone_11.dat
-rw-r–r– 1 oracle oinstall 274427 Aug  2  2009 timezone_1.dat
-rw-r–r– 1 oracle oinstall 274900 Aug  2  2009 timezone_2.dat
-rw-r–r– 1 oracle oinstall 286651 Aug  2  2009 timezone_3.dat
-rw-r–r– 1 oracle oinstall 286264 Aug  2  2009 timezone_4.dat
-rw-r–r– 1 oracle oinstall 286310 Aug  2  2009 timezone_5.dat
-rw-r–r– 1 oracle oinstall 286217 Aug  2  2009 timezone_6.dat
-rw-r–r– 1 oracle oinstall 286815 Aug  2  2009 timezone_7.dat
-rw-r–r– 1 oracle oinstall 302100 Aug  2  2009 timezone_8.dat
-rw-r–r– 1 oracle oinstall 351525 Aug  2  2009 timezone_9.dat
[oracle@shanghai zoneinfo]$ 

[oracle@shanghai zoneinfo]$ ll big
total 10136
-rw-r–r– 1 oracle oinstall 792894 May 26  2009 timezlrg_10.dat
-rw-r–r– 1 oracle oinstall 787272 Jun 12  2009 timezlrg_11.dat
-rw-r–r– 1 oracle oinstall 493675 Apr 29  2008 timezlrg_1.dat
-rw-r–r– 1 oracle oinstall 507957 Apr 29  2008 timezlrg_2.dat
-rw-r–r– 1 oracle oinstall 527717 Jan 30  2009 timezlrg_3.dat
-rw-r–r– 1 oracle oinstall 531137 Apr 29  2008 timezlrg_4.dat
-rw-r–r– 1 oracle oinstall 587487 Apr 29  2008 timezlrg_5.dat
-rw-r–r– 1 oracle oinstall 586750 Apr 29  2008 timezlrg_6.dat
-rw-r–r– 1 oracle oinstall 601242 Apr 29  2008 timezlrg_7.dat
-rw-r–r– 1 oracle oinstall 616723 Apr 29  2008 timezlrg_8.dat
-rw-r–r– 1 oracle oinstall 801410 May 23  2009 timezlrg_9.dat
-rw-r–r– 1 oracle oinstall 345637 May 26  2009 timezone_10.dat
-rw-r–r– 1 oracle oinstall 345356 Jun 12  2009 timezone_11.dat
-rw-r–r– 1 oracle oinstall 274427 Apr 29  2008 timezone_1.dat
-rw-r–r– 1 oracle oinstall 274900 Apr 29  2008 timezone_2.dat
-rw-r–r– 1 oracle oinstall 286651 Jan 30  2009 timezone_3.dat
-rw-r–r– 1 oracle oinstall 286264 Apr 29  2008 timezone_4.dat
-rw-r–r– 1 oracle oinstall 286310 Apr 29  2008 timezone_5.dat
-rw-r–r– 1 oracle oinstall 286217 Apr 29  2008 timezone_6.dat
-rw-r–r– 1 oracle oinstall 286815 Apr 29  2008 timezone_7.dat
-rw-r–r– 1 oracle oinstall 302100 Apr 29  2008 timezone_8.dat
-rw-r–r– 1 oracle oinstall 351525 May 23  2009 timezone_9.dat
[oracle@shanghai zoneinfo]$ 

[oracle@shanghai zoneinfo]$ ll little/
total 10136
-rw-r–r– 1 oracle oinstall 792894 May 26  2009 timezlrg_10.dat
-rw-r–r– 1 oracle oinstall 787272 Jun 12  2009 timezlrg_11.dat
-rw-r–r– 1 oracle oinstall 493675 Apr 29  2008 timezlrg_1.dat
-rw-r–r– 1 oracle oinstall 507957 Apr 29  2008 timezlrg_2.dat
-rw-r–r– 1 oracle oinstall 527717 Jan 30  2009 timezlrg_3.dat
-rw-r–r– 1 oracle oinstall 531137 Apr 29  2008 timezlrg_4.dat
-rw-r–r– 1 oracle oinstall 587487 Apr 29  2008 timezlrg_5.dat
-rw-r–r– 1 oracle oinstall 586750 Apr 29  2008 timezlrg_6.dat
-rw-r–r– 1 oracle oinstall 601242 Apr 29  2008 timezlrg_7.dat
-rw-r–r– 1 oracle oinstall 616723 Apr 29  2008 timezlrg_8.dat
-rw-r–r– 1 oracle oinstall 801410 May 23  2009 timezlrg_9.dat
-rw-r–r– 1 oracle oinstall 345637 May 26  2009 timezone_10.dat
-rw-r–r– 1 oracle oinstall 345356 Jun 12  2009 timezone_11.dat
-rw-r–r– 1 oracle oinstall 274427 Apr 29  2008 timezone_1.dat
-rw-r–r– 1 oracle oinstall 274900 Apr 29  2008 timezone_2.dat
-rw-r–r– 1 oracle oinstall 286651 Jan 30  2009 timezone_3.dat
-rw-r–r– 1 oracle oinstall 286264 Apr 29  2008 timezone_4.dat
-rw-r–r– 1 oracle oinstall 286310 Apr 29  2008 timezone_5.dat
-rw-r–r– 1 oracle oinstall 286217 Apr 29  2008 timezone_6.dat
-rw-r–r– 1 oracle oinstall 286815 Apr 29  2008 timezone_7.dat
-rw-r–r– 1 oracle oinstall 302100 Apr 29  2008 timezone_8.dat
-rw-r–r– 1 oracle oinstall 351525 May 23  2009 timezone_9.dat
[oracle@shanghai zoneinfo]$ 

8,创建一个与时区相关的表

[oracle@mygirl zoneinfo]$ ll
total 13340
drwxr-xr-x. 2 oracle oinstall   4096 May  4 23:47 big
drwxr-xr-x. 2 oracle oinstall   4096 May  4 23:47 little
-rw-r–r–. 1 oracle oinstall   6068 Jun  7  2010 readme.txt
-rw-r–r–. 1 oracle oinstall  31525 Jun  7  2010 timezdif.csv
-rw-r–r–. 1 oracle oinstall 792894 Jul 16  2013 timezlrg_10.dat
-rw-r–r–. 1 oracle oinstall 787272 Jul 16  2013 timezlrg_11.dat
-rw-r–r–. 1 oracle oinstall 785621 Jul 16  2013 timezlrg_12.dat
-rw-r–r–. 1 oracle oinstall 782475 Jul 16  2013 timezlrg_13.dat
-rw-r–r–. 1 oracle oinstall 791430 Jul 16  2013 timezlrg_14.dat
-rw-r–r–. 1 oracle oinstall 493675 Jul 16  2013 timezlrg_1.dat
-rw-r–r–. 1 oracle oinstall 507957 Jul 16  2013 timezlrg_2.dat
-rw-r–r–. 1 oracle oinstall 527717 Jul 16  2013 timezlrg_3.dat
-rw-r–r–. 1 oracle oinstall 531137 Jul 16  2013 timezlrg_4.dat
-rw-r–r–. 1 oracle oinstall 587487 Jul 16  2013 timezlrg_5.dat
-rw-r–r–. 1 oracle oinstall 586750 Jul 16  2013 timezlrg_6.dat
-rw-r–r–. 1 oracle oinstall 601242 Jul 16  2013 timezlrg_7.dat
-rw-r–r–. 1 oracle oinstall 616723 Jul 16  2013 timezlrg_8.dat
-rw-r–r–. 1 oracle oinstall 801410 Jul 16  2013 timezlrg_9.dat
-rw-r–r–. 1 oracle oinstall 345637 Jul 16  2013 timezone_10.dat
-rw-r–r–. 1 oracle oinstall 345356 Jul 16  2013 timezone_11.dat
-rw-r–r–. 1 oracle oinstall 345024 Jul 16  2013 timezone_12.dat
-rw-r–r–. 1 oracle oinstall 344425 Jul 16  2013 timezone_13.dat
-rw-r–r–. 1 oracle oinstall 344448 Jul 16  2013 timezone_14.dat
-rw-r–r–. 1 oracle oinstall 274427 Jul 16  2013 timezone_1.dat
-rw-r–r–. 1 oracle oinstall 274900 Jul 16  2013 timezone_2.dat
-rw-r–r–. 1 oracle oinstall 286651 Jul 16  2013 timezone_3.dat
-rw-r–r–. 1 oracle oinstall 286264 Jul 16  2013 timezone_4.dat
-rw-r–r–. 1 oracle oinstall 286310 Jul 16  2013 timezone_5.dat
-rw-r–r–. 1 oracle oinstall 286217 Jul 16  2013 timezone_6.dat
-rw-r–r–. 1 oracle oinstall 286815 Jul 16  2013 timezone_7.dat
-rw-r–r–. 1 oracle oinstall 302100 Jul 16  2013 timezone_8.dat
-rw-r–r–. 1 oracle oinstall 351525 Jul 16  2013 timezone_9.dat

SQL> conn user_dead/system
Connected.

SQL> create table t_timezone(a timestamp with local time zone);

Table created.

SQL> insert into t_timezone values(systimestamp);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t_timezone;

A
—————————————————————————
04-JUL-17 07.14.33.896489 PM

9,可见如果移动或删除$ORACLE_HOME/oracore/zoneinfo目录相关的时区文件,则登陆数据库会报错
注:包括$ORACLE_HOME/oracore/zoneinfo以及其下属big和little
[oracle@mygirl zoneinfo]$ pwd
/oracle/product/11.2.0/db_1/oracore/zoneinfo

[oracle@mygirl zoneinfo]$ mv -f big/ big_bak
[oracle@mygirl zoneinfo]$ mv -f little/ little_bak

[oracle@mygirl zoneinfo]$ ll
total 13340
drwxr-xr-x. 2 oracle oinstall   4096 May  4 23:47 big_bak
drwxr-xr-x. 2 oracle oinstall   4096 May  4 23:47 little_bak
-rw-r–r–. 1 oracle oinstall   6068 Jun  7  2010 readme.txt
-rw-r–r–. 1 oracle oinstall  31525 Jun  7  2010 timezdif.csv
-rw-r–r–. 1 oracle oinstall 792894 Jul 16  2013 timezlrg_10.dat
中间略
-rw-r–r–. 1 oracle oinstall 286651 Jul 16  2013 timezone_3.dat
-rw-r–r–. 1 oracle oinstall 286264 Jul 16  2013 timezone_4.dat
-rw-r–r–. 1 oracle oinstall 286310 Jul 16  2013 timezone_5.dat
-rw-r–r–. 1 oracle oinstall 286217 Jul 16  2013 timezone_6.dat
-rw-r–r–. 1 oracle oinstall 286815 Jul 16  2013 timezone_7.dat
-rw-r–r–. 1 oracle oinstall 302100 Jul 16  2013 timezone_8.dat
-rw-r–r–. 1 oracle oinstall 351525 Jul 16  2013 timezone_9.dat
[oracle@mygirl zoneinfo]$ 

SQL> conn user_dead/system
Connected.
SQL>  select * from t_timezone;

A
—————————————————————————
04-JUL-17 07.14.33.896489 PM

[oracle@mygirl zoneinfo]$ tar -cvf timezone.tar *dat
timezlrg_10.dat
timezlrg_11.dat
timezlrg_12.dat
timezlrg_13.dat
timezlrg_14.dat
中间略
timezone_5.dat
timezone_6.dat
timezone_7.dat
timezone_8.dat
timezone_9.dat
[oracle@mygirl zoneinfo]$ 

[oracle@mygirl zoneinfo]$ rm -Rf *dat
[oracle@mygirl zoneinfo]$ ll
total 13308
drwxr-xr-x. 2 oracle oinstall     4096 May  4 23:47 big_bak
drwxr-xr-x. 2 oracle oinstall     4096 May  4 23:47 little_bak
-rw-r–r–. 1 oracle oinstall     6068 Jun  7  2010 readme.txt
-rw-r–r–. 1 oracle oinstall    31525 Jun  7  2010 timezdif.csv
-rw-r–r–. 1 oracle oinstall 13578240 Jul  4 19:19 timezone.tar

[oracle@mygirl zoneinfo]$ sqlplus user_dead/system
ERROR:
ORA-01804: failure to initialize timezone information

SP2-0152: ORACLE may not be functioning properly

[oracle@mygirl zoneinfo]$ sqlplus ‘/as sysdba’
ERROR:
ORA-01804: failure to initialize timezone information

SP2-0152: ORACLE may not be functioning properly

10,可见只要$ORACLE_HOME/oracore/zoneinfo的时区文件不存在,则数据库会无法登陆
[oracle@mygirl zoneinfo]$ ll
total 13308
drwxr-xr-x. 2 oracle oinstall     4096 May  4 23:47 big_bak
drwxr-xr-x. 2 oracle oinstall     4096 May  4 23:47 little_bak
-rw-r–r–. 1 oracle oinstall     6068 Jun  7  2010 readme.txt
-rw-r–r–. 1 oracle oinstall    31525 Jun  7  2010 timezdif.csv
-rw-r–r–. 1 oracle oinstall 13578240 Jul  4 19:19 timezone.tar
[oracle@mygirl zoneinfo]$ tar -xvf timezone.tar 
timezlrg_10.dat
timezlrg_11.dat
timezlrg_12.dat
中间略
timezone_5.dat
timezone_6.dat
timezone_7.dat
timezone_8.dat
timezone_9.dat
[oracle@mygirl zoneinfo]$ ll
total 26600
drwxr-xr-x. 2 oracle oinstall     4096 May  4 23:47 big_bak
drwxr-xr-x. 2 oracle oinstall     4096 May  4 23:47 little_bak
-rw-r–r–. 1 oracle oinstall     6068 Jun  7  2010 readme.txt
-rw-r–r–. 1 oracle oinstall    31525 Jun  7  2010 timezdif.csv
-rw-r–r–. 1 oracle oinstall   792894 Jul 16  2013 timezlrg_10.dat
-rw-r–r–. 1 oracle oinstall   787272 Jul 16  2013 timezlrg_11.dat
中间略
-rw-r–r–. 1 oracle oinstall   286217 Jul 16  2013 timezone_6.dat
-rw-r–r–. 1 oracle oinstall   286815 Jul 16  2013 timezone_7.dat
-rw-r–r–. 1 oracle oinstall   302100 Jul 16  2013 timezone_8.dat
-rw-r–r–. 1 oracle oinstall   351525 Jul 16  2013 timezone_9.dat
-rw-r–r–. 1 oracle oinstall 13578240 Jul  4 19:19 timezone.tar
[oracle@mygirl zoneinfo]$

[oracle@mygirl zoneinfo]$ sqlplus ‘/as sysdba’

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 4 19:25:14 2017

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> 

[oracle@mygirl zoneinfo]$ mv -f big_bak/ big

[oracle@mygirl zoneinfo]$ mv -f little_bak/ little

[oracle@mygirl zoneinfo]$ rm -rf *dat
[oracle@mygirl zoneinfo]$

[oracle@mygirl zoneinfo]$ ll
total 13308
drwxr-xr-x. 2 oracle oinstall     4096 May  4 23:47 big
drwxr-xr-x. 2 oracle oinstall     4096 May  4 23:47 little
-rw-r–r–. 1 oracle oinstall     6068 Jun  7  2010 readme.txt
-rw-r–r–. 1 oracle oinstall    31525 Jun  7  2010 timezdif.csv
-rw-r–r–. 1 oracle oinstall 13578240 Jul  4 19:19 timezone.tar

[oracle@mygirl zoneinfo]$ sqlplus ‘/as sysdba’
ERROR:
ORA-01804: failure to initialize timezone information

SP2-0152: ORACLE may not be functioning properly

11,可见只要把数据库当前所使用的时区文件重命名,数据库仍会重定位到重命名的时区文件
[oracle@mygirl zoneinfo]$ tar -xvf timezone.tar 
timezlrg_10.dat
timezlrg_11.dat
timezlrg_12.dat
timezlrg_13.dat
timezlrg_14.dat
timezlrg_1.dat
timezlrg_2.dat
timezlrg_3.dat
timezlrg_4.dat
timezlrg_5.dat
timezlrg_6.dat
timezlrg_7.dat
timezlrg_8.dat
timezlrg_9.dat
timezone_10.dat
timezone_11.dat
timezone_12.dat
timezone_13.dat
timezone_14.dat
timezone_1.dat
timezone_2.dat
timezone_3.dat
timezone_4.dat
timezone_5.dat
timezone_6.dat
timezone_7.dat
timezone_8.dat
timezone_9.dat

[oracle@mygirl zoneinfo]$ sqlplus ‘/as sysdba’

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 4 19:31:25 2017

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select filename,version from v$timezone_file;

FILENAME                VERSION
——————– ———-
timezlrg_14.dat              14

[oracle@mygirl zoneinfo]$ pwd
/oracle/product/11.2.0/db_1/oracore/zoneinfo
[oracle@mygirl zoneinfo]$ ll -l timezlrg_1
timezlrg_10.dat  timezlrg_11.dat  timezlrg_12.dat  timezlrg_13.dat  timezlrg_14.dat  timezlrg_1.dat   
[oracle@mygirl zoneinfo]$ ll -l timezlrg_14.dat
-rw-r–r–. 1 oracle oinstall 791430 Jul 16  2013 timezlrg_14.dat
[oracle@mygirl zoneinfo]$ mv timezlrg_14.dat timezlrg_14.dat.bak

[oracle@mygirl zoneinfo]$ sqlplus user_dead/system

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 4 19:32:29 2017

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> 

SQL> select filename,version from v$timezone_file;

FILENAME                VERSION
——————– ———-
timezlrg_14.dat.bak          14

12,如果删除了数据库当前使用的时区文件,数据库仍可以正常登陆,但是v$timezone_file无记录显示,且查询与时区相关的表仍可以正常显示,
   且DML操作时区相关的表,也是正常
[oracle@mygirl zoneinfo]$ ll *14*bak
-rw-r–r–. 1 oracle oinstall 791430 Jul 16  2013 timezlrg_14.dat.bak

[oracle@mygirl zoneinfo]$ mkdir -p temp_dir
[oracle@mygirl zoneinfo]$ mv timezlrg_14.dat.bak temp_dir/
[oracle@mygirl zoneinfo]$

[oracle@mygirl zoneinfo]$ pwd
/oracle/product/11.2.0/db_1/oracore/zoneinfo
[oracle@mygirl zoneinfo]$ ll *14*
-rw-r–r–. 1 oracle oinstall 344448 Jul 16  2013 timezone_14.dat

[oracle@mygirl zoneinfo]$ sqlplus ‘/as sysdba’

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 4 19:35:18 2017

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select filename,version from v$timezone_file;

no rows selected

SQL> conn user_dead/system
Connected.
SQL> select * from t_timezone;

A
—————————————————————————
04-JUL-17 07.14.33.896489 PM

SQL> insert into t_timezone values(systimestamp);    

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t_timezone;

A
—————————————————————————
04-JUL-17 07.14.33.896489 PM
04-JUL-17 07.37.41.144110 PM

SQL> conn /as sysdba
Connected.
SQL> select filename,version from v$timezone_file;

no rows selected

13,如果删除了数据库当前使用的时区文件,关闭数据库后且重启数据库会报错ora-600
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size                  2254952 bytes
Variable Size            2080376728 bytes
Database Buffers           50331648 bytes
Redo Buffers                4923392 bytes
Database mounted.
ORA-00600: internal error code, arguments: [opiodr: call 2], [], [], [], [],
[], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [qcisSetPlsqlCtx:tzi init], [], [],
[], [], [], [], [], [], [], [], []

[oracle@mygirl zoneinfo]$ mv temp_dir/timezlrg_14.dat.bak  .
[oracle@mygirl zoneinfo]$ pwd
/oracle/product/11.2.0/db_1/oracore/zoneinfo
[oracle@mygirl zoneinfo]$ ll -l *bak
-rw-r–r–. 1 oracle oinstall 791430 Jul 16  2013 timezlrg_14.dat.bak

[oracle@mygirl zoneinfo]$ mv timezlrg_14.dat.bak timezlrg_14.dat
[oracle@mygirl zoneinfo]$ 

[oracle@mygirl zoneinfo]$ sqlplus ‘/as sysdba’

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 4 19:44:29 2017

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select open_mode from v$database;

OPEN_MODE
——————–
READ WRITE

SQL> 

PS:如果您想和业内技术大牛交流的话,请加qq群(527933790)或者关注微信公众 号(AskHarries),谢谢!

转载请注明原文出处:Harries Blog™ » oracle 11.2.0.1以及oracle 11.2.0.4数据库的时区time zone相关概念之一

赞 (0)

分享到:更多 ()

评论 0

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址