#su - oradb
$cd /tmp
$ls -l
total 588496
-rw-r--r-- 1 oradb oinstall 314120 Jan 16 10:48 p15897859_112030_SOLARIS64.zip
$unzip p15897859_112030_SOLARIS64.zip
Archive: p15897859_112030_SOLARIS64.zip
creating: 15897859/
inflating: 15897859/README.txt
inflating: 15897859/README.html
creating: 15897859/files/
creating: 15897859/files/oracore/
creating: 15897859/files/oracore/zoneinfo/
inflating: 15897859/files/oracore/zoneinfo/readme_19.txt
creating: 15897859/files/oracore/zoneinfo/little/
inflating: 15897859/files/oracore/zoneinfo/little/timezlrg_19.dat
inflating: 15897859/files/oracore/zoneinfo/little/timezone_19.dat
inflating: 15897859/files/oracore/zoneinfo/timezlrg_19.dat
inflating: 15897859/files/oracore/zoneinfo/timezone_19.dat
creating: 15897859/files/oracore/zoneinfo/big/
inflating: 15897859/files/oracore/zoneinfo/big/timezlrg_19.dat
inflating: 15897859/files/oracore/zoneinfo/big/timezone_19.dat
creating: 15897859/etc/
creating: 15897859/etc/config/
inflating: 15897859/etc/config/inventory.xml
inflating: 15897859/etc/config/actions.xml
inflating: 15897859/etc/config/deploy.xml
creating: 15897859/etc/xml/
inflating: 15897859/etc/xml/GenericActions.xml
inflating: 15897859/etc/xml/ShiphomeDirectoryStructure.xml
$ls -l
drwxr-xr-x 4 oradb oinstall 374 Dec 31 12:31 15897859
-rw-r--r-- 1 oradb oinstall 314120 Jan 16 10:48 p15897859_112030_SOLARIS64.zip
$cd 15897859
$/u01/app/oradb/product/11.2.0.3/dbhome_1/OPatch/opatch apply
$
$export ORACLE_SID=testdb
$sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Fri Feb 22 09:55:57 2013
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> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 4.0899E+10 bytes
Fixed Size 2168160 bytes
Variable Size 3892316832 bytes
Database Buffers 3.6910E+10 bytes
Redo Buffers 95125504 bytes
Database mounted.
SQL> SELECT * FROM v$timezone_file;
FILENAME VERSION
-------------------- ----------
timezlrg_19.dat 19
SQL> alter database open;
Database altered.
SQL> SELECT * FROM v$timezone_file;
FILENAME VERSION
-------------------- ----------
timezlrg_14.dat 14
SQL>
SQL> select u.name || '.' || o.name || '.' || c.name TSLTZcolumn
from sys.obj$ o, sys.col$ c, sys.user$ u
where c.type# = 231
and o.obj# = c.obj#
and u.user# = o.owner#; 2 3 4 5
no rows selected
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME; 2 3 4
PROPERTY_NAME VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION 14
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
SQL> select TZ_VERSION from sys.registry$database;
TZ_VERSION
----------
14
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;
2 3 4
PROPERTY_NAME VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION 14
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
SQL>
SQL> SELECT version FROM v$timezone_file;
VERSION
----------
14
SQL> purge dba_recyclebin;
DBA Recyclebin purged.
SQL> alter session set "_with_subquery"=materialize;
Session altered.
SQL> alter session set "_simple_view_merging"=TRUE;
Session altered.
SQL> exec DBMS_DST.BEGIN_PREPARE(19);
PL/SQL procedure successfully completed.
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME; 2 3 4
PROPERTY_NAME VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION 14
DST_SECONDARY_TT_VERSION 19
DST_UPGRADE_STATE PREPARE
SQL> TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
Table truncated.
SQL> TRUNCATE TABLE sys.dst$affected_tables;
Table truncated.
SQL> TRUNCATE TABLE sys.dst$error_table;
Table truncated.
SQL> set serveroutput on
SQL> BEGIN
DBMS_DST.FIND_AFFECTED_TABLES
(affected_tables => 'sys.dst$affected_tables',
log_errors => TRUE,
log_errors_table => 'sys.dst$error_table');
END;
/ 2 3 4 5 6 7
PL/SQL procedure successfully completed.
SQL> SELECT * FROM sys.dst$affected_tables;
TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
ROW_COUNT ERROR_COUNT
---------- -----------
SYS KET$_AUTOTASK_STATUS
MW_START_TIME
1 0
SYS KET$_CLIENT_TASKS
CURR_WIN_START
3 0
TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
ROW_COUNT ERROR_COUNT
---------- -----------
SYS SCHEDULER$_JOB
LAST_END_DATE
7 0
SYS SCHEDULER$_JOB
LAST_START_DATE
TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
ROW_COUNT ERROR_COUNT
---------- -----------
7 0
SYS SCHEDULER$_JOB
NEXT_RUN_DATE
5 0
SYS SCHEDULER$_JOB
TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
ROW_COUNT ERROR_COUNT
---------- -----------
LAST_ENABLED_TIME
8 0
SYS SCHEDULER$_JOB
START_DATE
5 0
TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
ROW_COUNT ERROR_COUNT
---------- -----------
SYS SCHEDULER$_JOB_RUN_DETAILS
REQ_START_DATE
9 0
SYS SCHEDULER$_JOB_RUN_DETAILS
START_DATE
14 0
TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
ROW_COUNT ERROR_COUNT
---------- -----------
SYS SCHEDULER$_WINDOW
ACTUAL_START_DATE
1 0
SYS SCHEDULER$_WINDOW
LAST_START_DATE
TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
ROW_COUNT ERROR_COUNT
---------- -----------
7 0
SYS SCHEDULER$_WINDOW
NEXT_START_DATE
9 0
SYS SCHEDULER$_WINDOW_DETAILS
TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
ROW_COUNT ERROR_COUNT
---------- -----------
REQ_START_DATE
1 0
SYS SCHEDULER$_WINDOW_DETAILS
START_DATE
1 0
14 rows selected.
SQL> SELECT * FROM sys.dst$error_table;
no rows selected
SQL> SELECT * FROM sys.dst$error_table where ERROR_NUMBER= '1883';
no rows selected
SQL> SELECT * FROM sys.dst$error_table where ERROR_NUMBER= '1878';
no rows selected
SQL> SELECT * FROM sys.dst$error_table where ERROR_NUMBER not in ('1878','1883');
no rows selected
SQL> EXEC DBMS_DST.END_PREPARE;
A prepare window has been successfully ended.
PL/SQL procedure successfully completed.
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME; 2 3 4
PROPERTY_NAME VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION 14
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade;
ORACLE instance started.
Total System Global Area 4.0899E+10 bytes
Fixed Size 2168160 bytes
Variable Size 3892316832 bytes
Database Buffers 3.6910E+10 bytes
Redo Buffers 95125504 bytes
Database mounted.
Database opened.
SQL>
SQL> set serveroutput on
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME; 2 3 4
PROPERTY_NAME VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION 14
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
SQL> purge dba_recyclebin;
DBA Recyclebin purged.
SQL> TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
Table truncated.
SQL> TRUNCATE TABLE sys.dst$affected_tables;
Table truncated.
SQL> TRUNCATE TABLE sys.dst$error_table;
Table truncated.
SQL> alter session set "_with_subquery"=materialize;
Session altered.
SQL> alter session set "_simple_view_merging"=TRUE;
Session altered.
SQL> EXEC DBMS_DST.BEGIN_UPGRADE(19);
An upgrade window has been successfully started.
PL/SQL procedure successfully completed.
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME; 2 3 4
PROPERTY_NAME VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION 19
DST_SECONDARY_TT_VERSION 14
DST_UPGRADE_STATE UPGRADE
SQL> SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES where UPGRADE_IN_PROGRESS='YES';
OWNER TABLE_NAME UPG
------------------------------ ------------------------------ ---
SYSMAN AQ$_MGMT_LOADER_QTABLE_L YES
SYSMAN AQ$_MGMT_LOADER_QTABLE_S YES
SYSMAN AQ$_MGMT_NOTIFY_QTABLE_L YES
SYSMAN AQ$_MGMT_NOTIFY_QTABLE_S YES
SYSMAN MGMT_PROV_ASSIGNMENT YES
SYSMAN MGMT_PROV_BOOTSERVER YES
SYSMAN MGMT_PROV_CLUSTER_NODES YES
SYSMAN MGMT_PROV_DEFAULT_IMAGE YES
SYSMAN MGMT_PROV_IP_RANGE YES
SYSMAN MGMT_PROV_NET_CONFIG YES
SYSMAN MGMT_PROV_OPERATION YES
OWNER TABLE_NAME UPG
------------------------------ ------------------------------ ---
SYSMAN MGMT_PROV_RPM_REP YES
SYSMAN MGMT_PROV_STAGING_DIRS YES
SYSMAN MGMT_PROV_SUITE_INST_MEMBERS YES
14 rows selected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 4.0899E+10 bytes
Fixed Size 2168160 bytes
Variable Size 3892316832 bytes
Database Buffers 3.6910E+10 bytes
Redo Buffers 95125504 bytes
Database mounted.
Database opened.
SQL> set serveroutput on
SQL> VAR numfail number
SQL> BEGIN
2 DBMS_DST.UPGRADE_DATABASE(:numfail,
3 parallel => TRUE,
4 log_errors => TRUE,
5 log_errors_table => 'SYS.DST$ERROR_TABLE',
6 log_triggers_table => 'SYS.DST$TRIGGER_TABLE',
7 error_on_overlap_time => FALSE,
8 error_on_nonexisting_time => FALSE);
9 DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
10 END;
11 /
Table list: "SYSMAN"."MGMT_PROV_SUITE_INST_MEMBERS"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_STAGING_DIRS"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_RPM_REP"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_OPERATION"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_NET_CONFIG"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_IP_RANGE"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_DEFAULT_IMAGE"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_CLUSTER_NODES"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_BOOTSERVER"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_ASSIGNMENT"
Number of failures: 0
Table list: "SYSMAN"."AQ$_MGMT_NOTIFY_QTABLE_S"
Number of failures: 0
Table list: "SYSMAN"."AQ$_MGMT_NOTIFY_QTABLE_L"
Number of failures: 0
Table list: "SYSMAN"."AQ$_MGMT_LOADER_QTABLE_S"
Number of failures: 0
Table list: "SYSMAN"."AQ$_MGMT_LOADER_QTABLE_L"
Number of failures: 0
Failures:0
PL/SQL procedure successfully completed.
SQL>
SQL> VAR fail number
BEGIN
DBMS_DST.END_UPGRADE(:fail);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
END;
/SQL> 2 3 4 5
An upgrade window has been successfully ended.
Failures:0
PL/SQL procedure successfully completed.
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME; 2 3 4
PROPERTY_NAME VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION 19
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
SQL> SELECT * FROM v$timezone_file;
FILENAME VERSION
-------------------- ----------
timezlrg_19.dat 19
SQL> SELECT VERSION FROM v$timezone_file;
VERSION
----------
19
SQL> select TZ_VERSION from registry$database;
TZ_VERSION
----------
14
SQL> update registry$database set TZ_VERSION = (select version FROM v$timezone_file);
1 row updated.
SQL> commit;
Commit complete.
SQL> select TZ_VERSION from registry$database;
TZ_VERSION
----------
19
SQL> create or replace and compile java source named "OffsetFromStandard" as
2 import java.util.Calendar;
3 import java.util.GregorianCalendar;
4 import java.util.TimeZone;
5
6 public class OffsetFromStandard
7 {
8 public static int getDSTOffset(
9 String timezone,
10 int year,
11 int month,
12 int mday,
13 int hour,
14 int min,
15 int sec)
16 {
17 int RetVal = -360000000;
18 String[] TZs = TimeZone.getAvailableIDs();
19 for (int i = 0; i < TZs.length; i++)
20 {
21 if (timezone.equals(TZs[i]))
22 {
23 TimeZone tz = TimeZone.getTimeZone(timezone);
24 GregorianCalendar c = new GregorianCalendar(tz);
25 c.set(year, month-1, mday, hour, min, sec);
26 RetVal = c.get(Calendar.DST_OFFSET);
27 };
28 }
29 return RetVal;
30 }
31 }
32 /
Java created.
SQL> alter java class "OffsetFromStandard" resolve
/ 2
Java altered.
SQL> CREATE OR REPLACE function get_dst_offset (timezone VARCHAR2,
2 year NUMBER,
3 month NUMBER,
4 mday NUMBER,
5 hour NUMBER,
6 min NUMBER,
7 sec NUMBER) RETURN NUMBER AS
8 LANGUAGE JAVA NAME 'OffsetFromStandard.getDSTOffset (java.lang.String, int, int, int, int, int, int) return int';
9 /
Function created.
SQL> CREATE OR REPLACE function test_dst(timezone VARCHAR2,
2 year NUMBER,
3 month NUMBER,
4 mday NUMBER,
5 hour NUMBER,
6 min NUMBER,
7 sec NUMBER) RETURN varchar2 as
8 retval varchar2(40);
9 begin
10 select case get_dst_offset (timezone,year,month,mday,hour,min,sec)
11 when 3600000 then 'Timestamp is *in* DST'
12 when 0 then 'Timestamp is *not* in DST'
13 when -360000000 then 'Time Zone not valid'
14 else 'Error' end into retval from dual;
15 return retval;
16 end;
17 /
Function created.
SQL> set serveroutput on
SQL> begin
2 if test_dst('America/New_York', 2007, 3, 11, 11, 0, 0)= 'Timestamp is *in* DST' then
3 dbms_output.put_line('V3 (or later) Time Zone Update is applied');
4 else
5 dbms_output.put_line('V3 (or later) Time Zone Update is not applied');
6 end if;
7 if test_dst('America/Edmonton', 2007, 3, 11, 11, 0, 0) = 'Timestamp is *in* DST' then
8 dbms_output.put_line('V4 (or later) Time Zone Update is applied');
9 else
10 dbms_output.put_line('V4 (or later) Time Zone Update is not applied');
11 end if;
12 if test_dst('Pacific/Auckland', 2008, 4, 5, 11, 0, 0) = 'Timestamp is *in* DST' then
13 dbms_output.put_line('V6 tzdata2007f (or later) Time Zone Update is applied');
14 else
15 dbms_output.put_line('V6 tzdata2007f (or later) Time Zone Update is not applied');
16 end if;
17 if test_dst('Australia/Adelaide', 2008, 4, 5, 11, 0, 0) = 'Timestamp is *in* DST' then
18 dbms_output.put_line('V7 tzdata2007k (or later) Time Zone Update is applied');
19 else
20 dbms_output.put_line('V7 tzdata2007k (or later) Time Zone Update is not applied');
21 end if;
22 if test_dst('Africa/Casablanca', 2008, 6, 1, 23, 0, 0) = 'Timestamp is *in* DST' then
23 dbms_output.put_line('V9 tzdata2008f (or later) Time Zone Update is applied');
24 else
25 dbms_output.put_line('V9 tzdata2008f (or later) Time Zone Update is not applied');
26 end if;
27 if test_dst('Asia/Damascus', 2008, 10, 15, 10, 0, 0) = 'Timestamp is *in* DST' then
28 dbms_output.put_line('V10 tzdata2008i (or later) Time Zone Update is applied');
29 else
30 dbms_output.put_line('V10 tzdata2008i (or later) Time Zone Update is not applied');
31 end if;
32 if test_dst('Asia/Karachi', 2009, 04, 16, 10, 0, 0) = 'Timestamp is *in* DST' then
33 dbms_output.put_line('V11 tzdata2009g (or later) Time Zone Update is applied');
34 else
35 dbms_output.put_line('V11 tzdata2009g (or later) Time Zone Update is not applied');
36 end if;
37 -- DSTv13 check changed since this gave a false negative when DSTv14 is applied
38 if test_dst('Pacific/Fiji', 2009, 11, 30, 10, 0, 0) = 'Timestamp is *in* DST' then
39 dbms_output.put_line('V13 tzdata2009s (or later) Time Zone Update is applied');
40 else
41 dbms_output.put_line('V13 tzdata2009s (or later) Time Zone Update is not applied');
42 end if;
43 if test_dst('Pacific/Fiji', 2010, 11, 16, 10, 0, 0) = 'Timestamp is *in* DST' then
44 dbms_output.put_line('V14 tzdata2010i (or later) Time Zone Update is applied');
45 else
46 dbms_output.put_line('V14 tzdata2010i (or later) Time Zone Update is not applied');
47 end if;
48 if test_dst('Asia/Hong_Kong', 1977, 07, 01, 10, 0, 0) = 'Timestamp is *in* DST' then
49 dbms_output.put_line('V15 tzdata2010o (or later) Time Zone Update is not applied');
50 else
51 dbms_output.put_line('V15 tzdata2010o (or later) Time Zone Update is applied');
52 end if;
53 if test_dst('Europe/Istanbul', 2011, 03, 27, 10, 0, 0) = 'Timestamp is *in* DST' then
54 dbms_output.put_line('V16 tzdata2011g (or later) Time Zone Update is not applied');
55 else
56 dbms_output.put_line('V16 tzdata2011g (or later) Time Zone Update is applied');
57 end if;
58 if test_dst('Europe/Moscow', 2012, 06, 20, 10, 0, 0) = 'Timestamp is *in* DST' then
59 dbms_output.put_line('V17 tzdata2011i (or later) Time Zone Update is not applied');
60 else
dbms_output.put_line('V17 tzdata2011i (or later) Time Zone Update is applied');
61 62 end if;
63 if test_dst('Europe/Minsk', 2012, 06, 20, 10, 0, 0) = 'Timestamp is *in* DST' then
64 dbms_output.put_line('V18 tzdata2012c (or later) Time Zone Update is not applied');
65 else
66 dbms_output.put_line('V18 tzdata2012c (or later) Time Zone Update is applied');
67 end if;
68 dbms_output.put_line('The TEST_DST function can be used to test your ''own'' time zones');
69 end;
70 /
V3 (or later) Time Zone Update is applied
V4 (or later) Time Zone Update is applied
V6 tzdata2007f (or later) Time Zone Update is applied
V7 tzdata2007k (or later) Time Zone Update is applied
V9 tzdata2008f (or later) Time Zone Update is applied
V10 tzdata2008i (or later) Time Zone Update is applied
V11 tzdata2009g (or later) Time Zone Update is applied
V13 tzdata2009s (or later) Time Zone Update is applied
V14 tzdata2010i (or later) Time Zone Update is applied
V15 tzdata2010o (or later) Time Zone Update is applied
V16 tzdata2011g (or later) Time Zone Update is applied
V17 tzdata2011i (or later) Time Zone Update is not applied
V18 tzdata2012c (or later) Time Zone Update is not applied
The TEST_DST function can be used to test your 'own' time zones
PL/SQL procedure successfully completed.
SQL> DROP function get_dst_offset;
Function dropped.
SQL> DROP function test_dst;
Function dropped.
SQL> DROP java source "OffsetFromStandard";
Java dropped.
SQL> select comp_name, version, status from dba_registry;
COMP_NAME
--------------------------------------------------------------------------------
VERSION STATUS
------------------------------ -----------
OWB
11.2.0.3.0 VALID
Oracle Application Express
3.2.1.00.12 VALID
Oracle Enterprise Manager
11.2.0.3.0 VALID
COMP_NAME
--------------------------------------------------------------------------------
VERSION STATUS
------------------------------ -----------
OLAP Catalog
11.2.0.3.0 VALID
Spatial
11.2.0.3.0 VALID
Oracle Multimedia
11.2.0.3.0 VALID
COMP_NAME
--------------------------------------------------------------------------------
VERSION STATUS
------------------------------ -----------
Oracle XML Database
11.2.0.3.0 VALID
Oracle Text
11.2.0.3.0 VALID
Oracle Expression Filter
11.2.0.3.0 VALID
COMP_NAME
--------------------------------------------------------------------------------
VERSION STATUS
------------------------------ -----------
Oracle Rules Manager
11.2.0.3.0 VALID
Oracle Workspace Manager
11.2.0.3.0 VALID
Oracle Database Catalog Views
11.2.0.3.0 VALID
COMP_NAME
--------------------------------------------------------------------------------
VERSION STATUS
------------------------------ -----------
Oracle Database Packages and Types
11.2.0.3.0 VALID
JServer JAVA Virtual Machine
11.2.0.3.0 VALID
Oracle XDK
11.2.0.3.0 VALID
COMP_NAME
--------------------------------------------------------------------------------
VERSION STATUS
------------------------------ -----------
Oracle Database Java Packages
11.2.0.3.0 VALID
OLAP Analytic Workspace
11.2.0.3.0 VALID
Oracle OLAP API
11.2.0.3.0 VALID
18 rows selected.
SQL> select owner, status, count(*) from all_objects where object_type like '%JAVA%' group by owner, status;
OWNER STATUS COUNT(*)
------------------------------ ------- ----------
EXFSYS VALID 48
ORDSYS VALID 1948
MDSYS VALID 538
SYS VALID 21885
SQL> select role from dba_roles where role like '%JAVA%';
ROLE
------------------------------
JAVA_DEPLOY
JAVAUSERPRIV
JAVAIDPRIV
JAVASYSPRIV
JAVADEBUGPRIV
JAVA_ADMIN
6 rows selected.
SQL> select currently_used, name from dba_feature_usage_statistics where name like '%Java%';
CURRE NAME
----- ----------------------------------------------------------------
FALSE Oracle Java Virtual Machine (user)
TRUE Oracle Java Virtual Machine (system)
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
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
$unzip p15897884_112030_Generic.zip
$cd 15897884
$/u01/app/oradb/product/11.2.0.3/dbhome_1/OPatch/opatch apply
$sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Fri Feb 22 10:32:06 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 4.0899E+10 bytes
Fixed Size 2168160 bytes
Variable Size 4294970016 bytes
Database Buffers 3.6507E+10 bytes
Redo Buffers 95125504 bytes
Database mounted.
Database opened.
SQL> @?/javavm/admin/fixTZa.sql
Session altered.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Java created.
No errors.
Java altered.
No errors.
Package created.
No errors.
Package body created.
No errors.
PL/SQL procedure successfully completed.
Call completed.
Testing various timezone DST attributes
FAILED for America/Creston 1325401200000 -25200000 0
FAILED for America/Resolute 1325397600000 -21600000 0
FAILED for America/Resolute 1331452799999 -21600000 0
FAILED for America/Resolute 1331452800000 -21600000 3600000
FAILED for America/Resolute 1352012399999 -21600000 3600000
FAILED for America/Resolute 1352012400000 -21600000 0
FAILED for America/Resolute 1362902399999 -21600000 0
FAILED for America/Resolute 1362902400000 -21600000 3600000
FAILED for America/Resolute 1383461999999 -21600000 3600000
FAILED for America/Resolute 1383462000000 -21600000 0
FAILED for America/Resolute 1394351999999 -21600000 0
FAILED for America/Resolute 1394352000000 -21600000 3600000
FAILED for America/Resolute 1414911599999 -21600000 3600000
FAILED for America/Resolute 1414911600000 -21600000 0
FAILED for America/Resolute 1425801599999 -21600000 0
FAILED for America/Resolute 1425801600000 -21600000 3600000
FAILED for America/Resolute 1446361199999 -21600000 3600000
FAILED for America/Resolute 1446361200000 -21600000 0
FAILED for America/Resolute 1457855999999 -21600000 0
FAILED for America/Resolute 1457856000000 -21600000 3600000
FAILED for America/Resolute 1478415599999 -21600000 3600000
FAILED for America/Resolute 1478415600000 -21600000 0
FAILED for Chile/EasterIsland 1335668399999 -21600000 3600000
FAILED for Chile/EasterIsland 1346558400000 -21600000 3600000
FAILED for Pacific/Easter 1335668399999 -21600000 3600000
FAILED for Pacific/Easter 1346558400000 -21600000 3600000
FAILED for America/Havana 1333256399999 -18000000 0
FAILED for America/Havana 1352005199999 -18000000 3600000
FAILED for America/Havana 1383454799999 -18000000 3600000
FAILED for America/Havana 1414904399999 -18000000 3600000
FAILED for America/Havana 1446353999999 -18000000 3600000
FAILED for America/Havana 1478408399999 -18000000 3600000
FAILED for America/Port-au-Prince 1331449200000 -18000000 3600000
FAILED for America/Port-au-Prince 1352008799999 -18000000 3600000
FAILED for Cuba 1333256399999 -18000000 0
FAILED for Cuba 1352005199999 -18000000 3600000
FAILED for Cuba 1383454799999 -18000000 3600000
FAILED for Cuba 1414904399999 -18000000 3600000
FAILED for Cuba 1446353999999 -18000000 3600000
FAILED for Cuba 1478408399999 -18000000 3600000
FAILED for America/Goose_Bay 1331445599999 -14400000 0
FAILED for America/Goose_Bay 1352005199999 -14400000 3600000
FAILED for America/Goose_Bay 1362895199999 -14400000 0
FAILED for America/Goose_Bay 1383454799999 -14400000 3600000
FAILED for America/Goose_Bay 1394344799999 -14400000 0
FAILED for America/Goose_Bay 1414904399999 -14400000 3600000
FAILED for America/Goose_Bay 1425794399999 -14400000 0
FAILED for America/Goose_Bay 1446353999999 -14400000 3600000
FAILED for America/Goose_Bay 1457848799999 -14400000 0
FAILED for America/Goose_Bay 1478408399999 -14400000 3600000
FAILED for America/Kralendijk 1325390400000 -14400000 0
FAILED for America/Lower_Princes 1325390400000 -14400000 0
FAILED for America/Santiago 1335668399999 -14400000 3600000
FAILED for America/Santiago 1346558400000 -14400000 3600000
FAILED for Antarctica/Palmer 1335668399999 -14400000 3600000
FAILED for Antarctica/Palmer 1346558400000 -14400000 3600000
FAILED for Chile/Continental 1335668399999 -14400000 3600000
FAILED for Chile/Continental 1346558400000 -14400000 3600000
FAILED for America/St_Johns 1331443799999 -12600000 0
FAILED for America/St_Johns 1352003399999 -12600000 3600000
FAILED for America/St_Johns 1362893399999 -12600000 0
FAILED for America/St_Johns 1383452999999 -12600000 3600000
FAILED for America/St_Johns 1394342999999 -12600000 0
FAILED for America/St_Johns 1414902599999 -12600000 3600000
FAILED for America/St_Johns 1425792599999 -12600000 0
FAILED for America/St_Johns 1446352199999 -12600000 3600000
FAILED for America/St_Johns 1457846999999 -12600000 0
FAILED for America/St_Johns 1478406599999 -12600000 3600000
FAILED for CNT 1331443799999 -12600000 0
FAILED for CNT 1352003399999 -12600000 3600000
FAILED for CNT 1362893399999 -12600000 0
FAILED for CNT 1383452999999 -12600000 3600000
FAILED for CNT 1394342999999 -12600000 0
FAILED for CNT 1414902599999 -12600000 3600000
FAILED for CNT 1425792599999 -12600000 0
FAILED for CNT 1446352199999 -12600000 3600000
FAILED for CNT 1457846999999 -12600000 0
FAILED for CNT 1478406599999 -12600000 3600000
FAILED for Canada/Newfoundland 1331443799999 -12600000 0
FAILED for Canada/Newfoundland 1352003399999 -12600000 3600000
FAILED for Canada/Newfoundland 1362893399999 -12600000 0
FAILED for Canada/Newfoundland 1383452999999 -12600000 3600000
FAILED for Canada/Newfoundland 1394342999999 -12600000 0
FAILED for Canada/Newfoundland 1414902599999 -12600000 3600000
FAILED for Canada/Newfoundland 1425792599999 -12600000 0
FAILED for Canada/Newfoundland 1446352199999 -12600000 3600000
FAILED for Canada/Newfoundland 1457846999999 -12600000 0
FAILED for Canada/Newfoundland 1478406599999 -12600000 3600000
FAILED for America/Araguaina 1350788400000 -10800000 3600000
FAILED for America/Araguaina 1361066399999 -10800000 3600000
FAILED for America/Araguaina 1382238000000 -10800000 3600000
FAILED for America/Araguaina 1392515999999 -10800000 3600000
FAILED for America/Araguaina 1413687600000 -10800000 3600000
FAILED for America/Araguaina 1424570399999 -10800000 3600000
FAILED for America/Araguaina 1445137200000 -10800000 3600000
FAILED for America/Araguaina 1456019999999 -10800000 3600000
FAILED for America/Araguaina 1476586800000 -10800000 3600000
FAILED for America/Bahia 1325383200000 -10800000 3600000
FAILED for America/Bahia 1330221599999 -10800000 3600000
FAILED for Atlantic/Stanley 1325386800000 -10800000 0
FAILED for Africa/Casablanca 1335664800000 0 3600000
FAILED for Africa/Casablanca 1342749599999 0 3600000
FAILED for Africa/Casablanca 1345428000000 0 3600000
FAILED for Africa/Casablanca 1348970399999 0 3600000
FAILED for Africa/Casablanca 1367114400000 0 3600000
FAILED for Africa/Casablanca 1380419999999 0 3600000
FAILED for Africa/Casablanca 1398564000000 0 3600000
FAILED for Africa/Casablanca 1411869599999 0 3600000
FAILED for Africa/Casablanca 1430013600000 0 3600000
FAILED for Africa/Casablanca 1443319199999 0 3600000
FAILED for Africa/Casablanca 1461463200000 0 3600000
FAILED for Africa/Casablanca 1474768799999 0 3600000
FAILED for Africa/Tripoli 1352505600000 3600000 0
FAILED for Africa/Tripoli 1364515199999 3600000 0
FAILED for Africa/Tripoli 1364515200000 3600000 3600000
FAILED for Africa/Tripoli 1382659199999 3600000 3600000
FAILED for Africa/Tripoli 1382659200000 3600000 0
FAILED for Africa/Tripoli 1395964799999 3600000 0
FAILED for Africa/Tripoli 1395964800000 3600000 3600000
FAILED for Africa/Tripoli 1414713599999 3600000 3600000
FAILED for Africa/Tripoli 1414713600000 3600000 0
FAILED for Africa/Tripoli 1427414399999 3600000 0
FAILED for Africa/Tripoli 1427414400000 3600000 3600000
FAILED for Africa/Tripoli 1446163199999 3600000 3600000
FAILED for Africa/Tripoli 1446163200000 3600000 0
FAILED for Africa/Tripoli 1458863999999 3600000 0
FAILED for Africa/Tripoli 1458864000000 3600000 3600000
FAILED for Africa/Tripoli 1477612799999 3600000 3600000
FAILED for Africa/Tripoli 1477612800000 3600000 0
FAILED for Libya 1352505600000 3600000 0
FAILED for Libya 1364515199999 3600000 0
FAILED for Libya 1364515200000 3600000 3600000
FAILED for Libya 1382659199999 3600000 3600000
FAILED for Libya 1382659200000 3600000 0
FAILED for Libya 1395964799999 3600000 0
FAILED for Libya 1395964800000 3600000 3600000
FAILED for Libya 1414713599999 3600000 3600000
FAILED for Libya 1414713600000 3600000 0
FAILED for Libya 1427414399999 3600000 0
FAILED for Libya 1427414400000 3600000 3600000
FAILED for Libya 1446163199999 3600000 3600000
FAILED for Libya 1446163200000 3600000 0
FAILED for Libya 1458863999999 3600000 0
FAILED for Libya 1458864000000 3600000 3600000
FAILED for Libya 1477612799999 3600000 3600000
FAILED for Libya 1477612800000 3600000 0
FAILED for Asia/Damascus 1333058400000 7200000 3600000
FAILED for Asia/Damascus 1364508000000 7200000 3600000
FAILED for Asia/Damascus 1395957600000 7200000 3600000
FAILED for Asia/Damascus 1427407200000 7200000 3600000
FAILED for Asia/Damascus 1458856800000 7200000 3600000
FAILED for Asia/Gaza 1333058400000 7200000 3600000
FAILED for Asia/Gaza 1348178399999 7200000 3600000
FAILED for Asia/Hebron 1325368800000 7200000 0
FAILED for Asia/Hebron 1333058399999 7200000 0
FAILED for Asia/Hebron 1333058400000 7200000 3600000
FAILED for Asia/Hebron 1348178399999 7200000 3600000
FAILED for Asia/Hebron 1348178400000 7200000 0
FAILED for Asia/Jerusalem 1381013999999 7200000 3600000
FAILED for Asia/Jerusalem 1412463599999 7200000 3600000
FAILED for Asia/Jerusalem 1443913199999 7200000 3600000
FAILED for Asia/Jerusalem 1458864000000 7200000 3600000
FAILED for Asia/Jerusalem 1475362800000 7200000 0
FAILED for Asia/Tel_Aviv 1381013999999 7200000 3600000
FAILED for Asia/Tel_Aviv 1412463599999 7200000 3600000
FAILED for Asia/Tel_Aviv 1443913199999 7200000 3600000
FAILED for Asia/Tel_Aviv 1458864000000 7200000 3600000
FAILED for Asia/Tel_Aviv 1475362800000 7200000 0
FAILED for Israel 1381013999999 7200000 3600000
FAILED for Israel 1412463599999 7200000 3600000
FAILED for Israel 1443913199999 7200000 3600000
FAILED for Israel 1458864000000 7200000 3600000
FAILED for Israel 1475362800000 7200000 0
FAILED for Africa/Juba 1325365200000 10800000 0
FAILED for Europe/Kaliningrad 1325365200000 10800000 0
FAILED for Europe/Minsk 1325365200000 10800000 0
FAILED for Europe/Moscow 1325361600000 14400000 0
FAILED for Europe/Samara 1325361600000 14400000 0
FAILED for Europe/Volgograd 1325361600000 14400000 0
FAILED for W-SU 1325361600000 14400000 0
FAILED for Asia/Yekaterinburg 1325354400000 21600000 0
FAILED for Antarctica/Davis 1325358000000 18000000 0
FAILED for Antarctica/Davis 1329854399999 18000000 0
FAILED for Asia/Novokuznetsk 1325350800000 25200000 0
FAILED for Asia/Novosibirsk 1325350800000 25200000 0
FAILED for Asia/Omsk 1325350800000 25200000 0
FAILED for Antarctica/Casey 1325336400000 39600000 0
FAILED for Antarctica/Casey 1329843599999 39600000 0
FAILED for Asia/Krasnoyarsk 1325347200000 28800000 0
FAILED for Asia/Irkutsk 1325343600000 32400000 0
FAILED for Asia/Yakutsk 1325340000000 36000000 0
FAILED for Asia/Sakhalin 1325336400000 39600000 0
FAILED for Asia/Vladivostok 1325336400000 39600000 0
FAILED for Asia/Anadyr 1325332800000 43200000 0
FAILED for Asia/Kamchatka 1325332800000 43200000 0
FAILED for Asia/Magadan 1325332800000 43200000 0
FAILED for Pacific/Fiji 1325329200000 43200000 3600000
FAILED for Pacific/Fiji 1327154399999 43200000 3600000
FAILED for Pacific/Fiji 1350741600000 43200000 3600000
FAILED for Pacific/Fiji 1358603999999 43200000 3600000
FAILED for Pacific/Fiji 1382191200000 43200000 3600000
FAILED for Pacific/Fiji 1390053599999 43200000 3600000
FAILED for Pacific/Fiji 1413640800000 43200000 3600000
FAILED for Pacific/Fiji 1421503199999 43200000 3600000
FAILED for Pacific/Fiji 1445090400000 43200000 3600000
FAILED for Pacific/Fiji 1453557599999 43200000 3600000
FAILED for Pacific/Fiji 1477144800000 43200000 3600000
FAILED for MIT 1325325600000 46800000 3600000
FAILED for MIT 1333202399999 46800000 3600000
FAILED for MIT 1333202400000 46800000 0
FAILED for MIT 1348927199999 46800000 0
FAILED for MIT 1348927200000 46800000 3600000
FAILED for MIT 1365256799999 46800000 3600000
FAILED for MIT 1365256800000 46800000 0
FAILED for MIT 1380376799999 46800000 0
FAILED for MIT 1380376800000 46800000 3600000
FAILED for MIT 1396706399999 46800000 3600000
FAILED for MIT 1396706400000 46800000 0
FAILED for MIT 1411826399999 46800000 0
FAILED for MIT 1411826400000 46800000 3600000
FAILED for MIT 1428155999999 46800000 3600000
FAILED for MIT 1428156000000 46800000 0
FAILED for MIT 1443275999999 46800000 0
FAILED for MIT 1443276000000 46800000 3600000
FAILED for MIT 1459605599999 46800000 3600000
FAILED for MIT 1459605600000 46800000 0
FAILED for MIT 1474725599999 46800000 0
FAILED for MIT 1474725600000 46800000 3600000
FAILED for Pacific/Apia 1325325600000 46800000 3600000
FAILED for Pacific/Apia 1333202399999 46800000 3600000
FAILED for Pacific/Apia 1333202400000 46800000 0
FAILED for Pacific/Apia 1348927199999 46800000 0
FAILED for Pacific/Apia 1348927200000 46800000 3600000
FAILED for Pacific/Apia 1365256799999 46800000 3600000
FAILED for Pacific/Apia 1365256800000 46800000 0
FAILED for Pacific/Apia 1380376799999 46800000 0
FAILED for Pacific/Apia 1380376800000 46800000 3600000
FAILED for Pacific/Apia 1396706399999 46800000 3600000
FAILED for Pacific/Apia 1396706400000 46800000 0
FAILED for Pacific/Apia 1411826399999 46800000 0
FAILED for Pacific/Apia 1411826400000 46800000 3600000
FAILED for Pacific/Apia 1428155999999 46800000 3600000
FAILED for Pacific/Apia 1428156000000 46800000 0
FAILED for Pacific/Apia 1443275999999 46800000 0
FAILED for Pacific/Apia 1443276000000 46800000 3600000
FAILED for Pacific/Apia 1459605599999 46800000 3600000
FAILED for Pacific/Apia 1459605600000 46800000 0
FAILED for Pacific/Apia 1474725599999 46800000 0
FAILED for Pacific/Apia 1474725600000 46800000 3600000
FAILED for Pacific/Fakaofo 1325329200000 46800000 0
##
Bug is in fact present, so this patch is needed
##
(Perhaps) modifying ild_ub1$ for rehotloading of sun/util/calendar/ZoneInfoFile
obj# = 45849
nbytes %d = 64512, 1st read = 64512, 2nd read = -1
96 397776 385632 12144 joxbtflag>>8 = 3
===
PL/SQL procedure successfully completed.
########################################################
Bug is in fact present, so this patch is needed
Proceed by restarting the database and running script
fixTZb
########################################################
PL/SQL procedure successfully completed.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup migrate
ORACLE instance started.
Total System Global Area 4.0899E+10 bytes
Fixed Size 2168160 bytes
Variable Size 4294970016 bytes
Database Buffers 3.6507E+10 bytes
Redo Buffers 95125504 bytes
Database mounted.
Database opened.
SQL> @?/javavm/admin/fixTZb.sql
Session altered.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Call completed.
Testing various timezone DST attributes
########################################################
Bug is no longer present. Patch succeeded.
########################################################
PL/SQL procedure successfully completed.
drop java source fixTZ
drop package fixTZ
PL/SQL procedure successfully completed.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 4.0899E+10 bytes
Fixed Size 2168160 bytes
Variable Size 4294970016 bytes
Database Buffers 3.6507E+10 bytes
Redo Buffers 95125504 bytes
Database mounted.
Database opened.
SQL>
Всё, DST установлен.
Более коротко делаем так:
Проверить Timezone Version в БД:
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;
SQL> SELECT version FROM v$timezone_file; VERSION ---------- 19
Для установки DST обновления Oracle JVM (Patch 15897884) необходимо:
1) Выполнить скрипт:
SQL> @?/javavm/admin/fixTZa.sql
2) Выполнить:
SQL> shutdown immediate
SQL> startup migrate
3) Выполнить скрипт:
SQL> @?/javavm/admin/fixTZb.sql
4) Выполнить:
SQL> shutdown immediate
SQL> startup
$cd /tmp
$ls -l
total 588496
-rw-r--r-- 1 oradb oinstall 314120 Jan 16 10:48 p15897859_112030_SOLARIS64.zip
$unzip p15897859_112030_SOLARIS64.zip
Archive: p15897859_112030_SOLARIS64.zip
creating: 15897859/
inflating: 15897859/README.txt
inflating: 15897859/README.html
creating: 15897859/files/
creating: 15897859/files/oracore/
creating: 15897859/files/oracore/zoneinfo/
inflating: 15897859/files/oracore/zoneinfo/readme_19.txt
creating: 15897859/files/oracore/zoneinfo/little/
inflating: 15897859/files/oracore/zoneinfo/little/timezlrg_19.dat
inflating: 15897859/files/oracore/zoneinfo/little/timezone_19.dat
inflating: 15897859/files/oracore/zoneinfo/timezlrg_19.dat
inflating: 15897859/files/oracore/zoneinfo/timezone_19.dat
creating: 15897859/files/oracore/zoneinfo/big/
inflating: 15897859/files/oracore/zoneinfo/big/timezlrg_19.dat
inflating: 15897859/files/oracore/zoneinfo/big/timezone_19.dat
creating: 15897859/etc/
creating: 15897859/etc/config/
inflating: 15897859/etc/config/inventory.xml
inflating: 15897859/etc/config/actions.xml
inflating: 15897859/etc/config/deploy.xml
creating: 15897859/etc/xml/
inflating: 15897859/etc/xml/GenericActions.xml
inflating: 15897859/etc/xml/ShiphomeDirectoryStructure.xml
$ls -l
drwxr-xr-x 4 oradb oinstall 374 Dec 31 12:31 15897859
-rw-r--r-- 1 oradb oinstall 314120 Jan 16 10:48 p15897859_112030_SOLARIS64.zip
$cd 15897859
$/u01/app/oradb/product/11.2.0.3/dbhome_1/OPatch/opatch apply
$
$export ORACLE_SID=testdb
$sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Fri Feb 22 09:55:57 2013
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> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 4.0899E+10 bytes
Fixed Size 2168160 bytes
Variable Size 3892316832 bytes
Database Buffers 3.6910E+10 bytes
Redo Buffers 95125504 bytes
Database mounted.
SQL> SELECT * FROM v$timezone_file;
FILENAME VERSION
-------------------- ----------
timezlrg_19.dat 19
SQL> alter database open;
Database altered.
SQL> SELECT * FROM v$timezone_file;
FILENAME VERSION
-------------------- ----------
timezlrg_14.dat 14
SQL>
SQL> select u.name || '.' || o.name || '.' || c.name TSLTZcolumn
from sys.obj$ o, sys.col$ c, sys.user$ u
where c.type# = 231
and o.obj# = c.obj#
and u.user# = o.owner#; 2 3 4 5
no rows selected
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME; 2 3 4
PROPERTY_NAME VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION 14
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
SQL> select TZ_VERSION from sys.registry$database;
TZ_VERSION
----------
14
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;
2 3 4
PROPERTY_NAME VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION 14
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
SQL>
SQL> SELECT version FROM v$timezone_file;
VERSION
----------
14
SQL> purge dba_recyclebin;
DBA Recyclebin purged.
SQL> alter session set "_with_subquery"=materialize;
Session altered.
SQL> alter session set "_simple_view_merging"=TRUE;
Session altered.
SQL> exec DBMS_DST.BEGIN_PREPARE(19);
PL/SQL procedure successfully completed.
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME; 2 3 4
PROPERTY_NAME VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION 14
DST_SECONDARY_TT_VERSION 19
DST_UPGRADE_STATE PREPARE
SQL> TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
Table truncated.
SQL> TRUNCATE TABLE sys.dst$affected_tables;
Table truncated.
SQL> TRUNCATE TABLE sys.dst$error_table;
Table truncated.
SQL> set serveroutput on
SQL> BEGIN
DBMS_DST.FIND_AFFECTED_TABLES
(affected_tables => 'sys.dst$affected_tables',
log_errors => TRUE,
log_errors_table => 'sys.dst$error_table');
END;
/ 2 3 4 5 6 7
PL/SQL procedure successfully completed.
SQL> SELECT * FROM sys.dst$affected_tables;
TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
ROW_COUNT ERROR_COUNT
---------- -----------
SYS KET$_AUTOTASK_STATUS
MW_START_TIME
1 0
SYS KET$_CLIENT_TASKS
CURR_WIN_START
3 0
TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
ROW_COUNT ERROR_COUNT
---------- -----------
SYS SCHEDULER$_JOB
LAST_END_DATE
7 0
SYS SCHEDULER$_JOB
LAST_START_DATE
TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
ROW_COUNT ERROR_COUNT
---------- -----------
7 0
SYS SCHEDULER$_JOB
NEXT_RUN_DATE
5 0
SYS SCHEDULER$_JOB
TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
ROW_COUNT ERROR_COUNT
---------- -----------
LAST_ENABLED_TIME
8 0
SYS SCHEDULER$_JOB
START_DATE
5 0
TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
ROW_COUNT ERROR_COUNT
---------- -----------
SYS SCHEDULER$_JOB_RUN_DETAILS
REQ_START_DATE
9 0
SYS SCHEDULER$_JOB_RUN_DETAILS
START_DATE
14 0
TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
ROW_COUNT ERROR_COUNT
---------- -----------
SYS SCHEDULER$_WINDOW
ACTUAL_START_DATE
1 0
SYS SCHEDULER$_WINDOW
LAST_START_DATE
TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
ROW_COUNT ERROR_COUNT
---------- -----------
7 0
SYS SCHEDULER$_WINDOW
NEXT_START_DATE
9 0
SYS SCHEDULER$_WINDOW_DETAILS
TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
ROW_COUNT ERROR_COUNT
---------- -----------
REQ_START_DATE
1 0
SYS SCHEDULER$_WINDOW_DETAILS
START_DATE
1 0
14 rows selected.
SQL> SELECT * FROM sys.dst$error_table;
no rows selected
SQL> SELECT * FROM sys.dst$error_table where ERROR_NUMBER= '1883';
no rows selected
SQL> SELECT * FROM sys.dst$error_table where ERROR_NUMBER= '1878';
no rows selected
SQL> SELECT * FROM sys.dst$error_table where ERROR_NUMBER not in ('1878','1883');
no rows selected
SQL> EXEC DBMS_DST.END_PREPARE;
A prepare window has been successfully ended.
PL/SQL procedure successfully completed.
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME; 2 3 4
PROPERTY_NAME VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION 14
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade;
ORACLE instance started.
Total System Global Area 4.0899E+10 bytes
Fixed Size 2168160 bytes
Variable Size 3892316832 bytes
Database Buffers 3.6910E+10 bytes
Redo Buffers 95125504 bytes
Database mounted.
Database opened.
SQL>
SQL> set serveroutput on
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME; 2 3 4
PROPERTY_NAME VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION 14
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
SQL> purge dba_recyclebin;
DBA Recyclebin purged.
SQL> TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
Table truncated.
SQL> TRUNCATE TABLE sys.dst$affected_tables;
Table truncated.
SQL> TRUNCATE TABLE sys.dst$error_table;
Table truncated.
SQL> alter session set "_with_subquery"=materialize;
Session altered.
SQL> alter session set "_simple_view_merging"=TRUE;
Session altered.
SQL> EXEC DBMS_DST.BEGIN_UPGRADE(19);
An upgrade window has been successfully started.
PL/SQL procedure successfully completed.
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME; 2 3 4
PROPERTY_NAME VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION 19
DST_SECONDARY_TT_VERSION 14
DST_UPGRADE_STATE UPGRADE
SQL> SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES where UPGRADE_IN_PROGRESS='YES';
OWNER TABLE_NAME UPG
------------------------------ ------------------------------ ---
SYSMAN AQ$_MGMT_LOADER_QTABLE_L YES
SYSMAN AQ$_MGMT_LOADER_QTABLE_S YES
SYSMAN AQ$_MGMT_NOTIFY_QTABLE_L YES
SYSMAN AQ$_MGMT_NOTIFY_QTABLE_S YES
SYSMAN MGMT_PROV_ASSIGNMENT YES
SYSMAN MGMT_PROV_BOOTSERVER YES
SYSMAN MGMT_PROV_CLUSTER_NODES YES
SYSMAN MGMT_PROV_DEFAULT_IMAGE YES
SYSMAN MGMT_PROV_IP_RANGE YES
SYSMAN MGMT_PROV_NET_CONFIG YES
SYSMAN MGMT_PROV_OPERATION YES
OWNER TABLE_NAME UPG
------------------------------ ------------------------------ ---
SYSMAN MGMT_PROV_RPM_REP YES
SYSMAN MGMT_PROV_STAGING_DIRS YES
SYSMAN MGMT_PROV_SUITE_INST_MEMBERS YES
14 rows selected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 4.0899E+10 bytes
Fixed Size 2168160 bytes
Variable Size 3892316832 bytes
Database Buffers 3.6910E+10 bytes
Redo Buffers 95125504 bytes
Database mounted.
Database opened.
SQL> set serveroutput on
SQL> VAR numfail number
SQL> BEGIN
2 DBMS_DST.UPGRADE_DATABASE(:numfail,
3 parallel => TRUE,
4 log_errors => TRUE,
5 log_errors_table => 'SYS.DST$ERROR_TABLE',
6 log_triggers_table => 'SYS.DST$TRIGGER_TABLE',
7 error_on_overlap_time => FALSE,
8 error_on_nonexisting_time => FALSE);
9 DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
10 END;
11 /
Table list: "SYSMAN"."MGMT_PROV_SUITE_INST_MEMBERS"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_STAGING_DIRS"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_RPM_REP"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_OPERATION"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_NET_CONFIG"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_IP_RANGE"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_DEFAULT_IMAGE"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_CLUSTER_NODES"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_BOOTSERVER"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_ASSIGNMENT"
Number of failures: 0
Table list: "SYSMAN"."AQ$_MGMT_NOTIFY_QTABLE_S"
Number of failures: 0
Table list: "SYSMAN"."AQ$_MGMT_NOTIFY_QTABLE_L"
Number of failures: 0
Table list: "SYSMAN"."AQ$_MGMT_LOADER_QTABLE_S"
Number of failures: 0
Table list: "SYSMAN"."AQ$_MGMT_LOADER_QTABLE_L"
Number of failures: 0
Failures:0
PL/SQL procedure successfully completed.
SQL>
SQL> VAR fail number
BEGIN
DBMS_DST.END_UPGRADE(:fail);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
END;
/SQL> 2 3 4 5
An upgrade window has been successfully ended.
Failures:0
PL/SQL procedure successfully completed.
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME; 2 3 4
PROPERTY_NAME VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION 19
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
SQL> SELECT * FROM v$timezone_file;
FILENAME VERSION
-------------------- ----------
timezlrg_19.dat 19
SQL> SELECT VERSION FROM v$timezone_file;
VERSION
----------
19
SQL> select TZ_VERSION from registry$database;
TZ_VERSION
----------
14
SQL> update registry$database set TZ_VERSION = (select version FROM v$timezone_file);
1 row updated.
SQL> commit;
Commit complete.
SQL> select TZ_VERSION from registry$database;
TZ_VERSION
----------
19
SQL> create or replace and compile java source named "OffsetFromStandard" as
2 import java.util.Calendar;
3 import java.util.GregorianCalendar;
4 import java.util.TimeZone;
5
6 public class OffsetFromStandard
7 {
8 public static int getDSTOffset(
9 String timezone,
10 int year,
11 int month,
12 int mday,
13 int hour,
14 int min,
15 int sec)
16 {
17 int RetVal = -360000000;
18 String[] TZs = TimeZone.getAvailableIDs();
19 for (int i = 0; i < TZs.length; i++)
20 {
21 if (timezone.equals(TZs[i]))
22 {
23 TimeZone tz = TimeZone.getTimeZone(timezone);
24 GregorianCalendar c = new GregorianCalendar(tz);
25 c.set(year, month-1, mday, hour, min, sec);
26 RetVal = c.get(Calendar.DST_OFFSET);
27 };
28 }
29 return RetVal;
30 }
31 }
32 /
Java created.
SQL> alter java class "OffsetFromStandard" resolve
/ 2
Java altered.
SQL> CREATE OR REPLACE function get_dst_offset (timezone VARCHAR2,
2 year NUMBER,
3 month NUMBER,
4 mday NUMBER,
5 hour NUMBER,
6 min NUMBER,
7 sec NUMBER) RETURN NUMBER AS
8 LANGUAGE JAVA NAME 'OffsetFromStandard.getDSTOffset (java.lang.String, int, int, int, int, int, int) return int';
9 /
Function created.
SQL> CREATE OR REPLACE function test_dst(timezone VARCHAR2,
2 year NUMBER,
3 month NUMBER,
4 mday NUMBER,
5 hour NUMBER,
6 min NUMBER,
7 sec NUMBER) RETURN varchar2 as
8 retval varchar2(40);
9 begin
10 select case get_dst_offset (timezone,year,month,mday,hour,min,sec)
11 when 3600000 then 'Timestamp is *in* DST'
12 when 0 then 'Timestamp is *not* in DST'
13 when -360000000 then 'Time Zone not valid'
14 else 'Error' end into retval from dual;
15 return retval;
16 end;
17 /
Function created.
SQL> set serveroutput on
SQL> begin
2 if test_dst('America/New_York', 2007, 3, 11, 11, 0, 0)= 'Timestamp is *in* DST' then
3 dbms_output.put_line('V3 (or later) Time Zone Update is applied');
4 else
5 dbms_output.put_line('V3 (or later) Time Zone Update is not applied');
6 end if;
7 if test_dst('America/Edmonton', 2007, 3, 11, 11, 0, 0) = 'Timestamp is *in* DST' then
8 dbms_output.put_line('V4 (or later) Time Zone Update is applied');
9 else
10 dbms_output.put_line('V4 (or later) Time Zone Update is not applied');
11 end if;
12 if test_dst('Pacific/Auckland', 2008, 4, 5, 11, 0, 0) = 'Timestamp is *in* DST' then
13 dbms_output.put_line('V6 tzdata2007f (or later) Time Zone Update is applied');
14 else
15 dbms_output.put_line('V6 tzdata2007f (or later) Time Zone Update is not applied');
16 end if;
17 if test_dst('Australia/Adelaide', 2008, 4, 5, 11, 0, 0) = 'Timestamp is *in* DST' then
18 dbms_output.put_line('V7 tzdata2007k (or later) Time Zone Update is applied');
19 else
20 dbms_output.put_line('V7 tzdata2007k (or later) Time Zone Update is not applied');
21 end if;
22 if test_dst('Africa/Casablanca', 2008, 6, 1, 23, 0, 0) = 'Timestamp is *in* DST' then
23 dbms_output.put_line('V9 tzdata2008f (or later) Time Zone Update is applied');
24 else
25 dbms_output.put_line('V9 tzdata2008f (or later) Time Zone Update is not applied');
26 end if;
27 if test_dst('Asia/Damascus', 2008, 10, 15, 10, 0, 0) = 'Timestamp is *in* DST' then
28 dbms_output.put_line('V10 tzdata2008i (or later) Time Zone Update is applied');
29 else
30 dbms_output.put_line('V10 tzdata2008i (or later) Time Zone Update is not applied');
31 end if;
32 if test_dst('Asia/Karachi', 2009, 04, 16, 10, 0, 0) = 'Timestamp is *in* DST' then
33 dbms_output.put_line('V11 tzdata2009g (or later) Time Zone Update is applied');
34 else
35 dbms_output.put_line('V11 tzdata2009g (or later) Time Zone Update is not applied');
36 end if;
37 -- DSTv13 check changed since this gave a false negative when DSTv14 is applied
38 if test_dst('Pacific/Fiji', 2009, 11, 30, 10, 0, 0) = 'Timestamp is *in* DST' then
39 dbms_output.put_line('V13 tzdata2009s (or later) Time Zone Update is applied');
40 else
41 dbms_output.put_line('V13 tzdata2009s (or later) Time Zone Update is not applied');
42 end if;
43 if test_dst('Pacific/Fiji', 2010, 11, 16, 10, 0, 0) = 'Timestamp is *in* DST' then
44 dbms_output.put_line('V14 tzdata2010i (or later) Time Zone Update is applied');
45 else
46 dbms_output.put_line('V14 tzdata2010i (or later) Time Zone Update is not applied');
47 end if;
48 if test_dst('Asia/Hong_Kong', 1977, 07, 01, 10, 0, 0) = 'Timestamp is *in* DST' then
49 dbms_output.put_line('V15 tzdata2010o (or later) Time Zone Update is not applied');
50 else
51 dbms_output.put_line('V15 tzdata2010o (or later) Time Zone Update is applied');
52 end if;
53 if test_dst('Europe/Istanbul', 2011, 03, 27, 10, 0, 0) = 'Timestamp is *in* DST' then
54 dbms_output.put_line('V16 tzdata2011g (or later) Time Zone Update is not applied');
55 else
56 dbms_output.put_line('V16 tzdata2011g (or later) Time Zone Update is applied');
57 end if;
58 if test_dst('Europe/Moscow', 2012, 06, 20, 10, 0, 0) = 'Timestamp is *in* DST' then
59 dbms_output.put_line('V17 tzdata2011i (or later) Time Zone Update is not applied');
60 else
dbms_output.put_line('V17 tzdata2011i (or later) Time Zone Update is applied');
61 62 end if;
63 if test_dst('Europe/Minsk', 2012, 06, 20, 10, 0, 0) = 'Timestamp is *in* DST' then
64 dbms_output.put_line('V18 tzdata2012c (or later) Time Zone Update is not applied');
65 else
66 dbms_output.put_line('V18 tzdata2012c (or later) Time Zone Update is applied');
67 end if;
68 dbms_output.put_line('The TEST_DST function can be used to test your ''own'' time zones');
69 end;
70 /
V3 (or later) Time Zone Update is applied
V4 (or later) Time Zone Update is applied
V6 tzdata2007f (or later) Time Zone Update is applied
V7 tzdata2007k (or later) Time Zone Update is applied
V9 tzdata2008f (or later) Time Zone Update is applied
V10 tzdata2008i (or later) Time Zone Update is applied
V11 tzdata2009g (or later) Time Zone Update is applied
V13 tzdata2009s (or later) Time Zone Update is applied
V14 tzdata2010i (or later) Time Zone Update is applied
V15 tzdata2010o (or later) Time Zone Update is applied
V16 tzdata2011g (or later) Time Zone Update is applied
V17 tzdata2011i (or later) Time Zone Update is not applied
V18 tzdata2012c (or later) Time Zone Update is not applied
The TEST_DST function can be used to test your 'own' time zones
PL/SQL procedure successfully completed.
SQL> DROP function get_dst_offset;
Function dropped.
SQL> DROP function test_dst;
Function dropped.
SQL> DROP java source "OffsetFromStandard";
Java dropped.
SQL> select comp_name, version, status from dba_registry;
COMP_NAME
--------------------------------------------------------------------------------
VERSION STATUS
------------------------------ -----------
OWB
11.2.0.3.0 VALID
Oracle Application Express
3.2.1.00.12 VALID
Oracle Enterprise Manager
11.2.0.3.0 VALID
COMP_NAME
--------------------------------------------------------------------------------
VERSION STATUS
------------------------------ -----------
OLAP Catalog
11.2.0.3.0 VALID
Spatial
11.2.0.3.0 VALID
Oracle Multimedia
11.2.0.3.0 VALID
COMP_NAME
--------------------------------------------------------------------------------
VERSION STATUS
------------------------------ -----------
Oracle XML Database
11.2.0.3.0 VALID
Oracle Text
11.2.0.3.0 VALID
Oracle Expression Filter
11.2.0.3.0 VALID
COMP_NAME
--------------------------------------------------------------------------------
VERSION STATUS
------------------------------ -----------
Oracle Rules Manager
11.2.0.3.0 VALID
Oracle Workspace Manager
11.2.0.3.0 VALID
Oracle Database Catalog Views
11.2.0.3.0 VALID
COMP_NAME
--------------------------------------------------------------------------------
VERSION STATUS
------------------------------ -----------
Oracle Database Packages and Types
11.2.0.3.0 VALID
JServer JAVA Virtual Machine
11.2.0.3.0 VALID
Oracle XDK
11.2.0.3.0 VALID
COMP_NAME
--------------------------------------------------------------------------------
VERSION STATUS
------------------------------ -----------
Oracle Database Java Packages
11.2.0.3.0 VALID
OLAP Analytic Workspace
11.2.0.3.0 VALID
Oracle OLAP API
11.2.0.3.0 VALID
18 rows selected.
SQL> select owner, status, count(*) from all_objects where object_type like '%JAVA%' group by owner, status;
OWNER STATUS COUNT(*)
------------------------------ ------- ----------
EXFSYS VALID 48
ORDSYS VALID 1948
MDSYS VALID 538
SYS VALID 21885
SQL> select role from dba_roles where role like '%JAVA%';
ROLE
------------------------------
JAVA_DEPLOY
JAVAUSERPRIV
JAVAIDPRIV
JAVASYSPRIV
JAVADEBUGPRIV
JAVA_ADMIN
6 rows selected.
SQL> select currently_used, name from dba_feature_usage_statistics where name like '%Java%';
CURRE NAME
----- ----------------------------------------------------------------
FALSE Oracle Java Virtual Machine (user)
TRUE Oracle Java Virtual Machine (system)
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
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
$unzip p15897884_112030_Generic.zip
$cd 15897884
$/u01/app/oradb/product/11.2.0.3/dbhome_1/OPatch/opatch apply
$sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Fri Feb 22 10:32:06 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 4.0899E+10 bytes
Fixed Size 2168160 bytes
Variable Size 4294970016 bytes
Database Buffers 3.6507E+10 bytes
Redo Buffers 95125504 bytes
Database mounted.
Database opened.
SQL> @?/javavm/admin/fixTZa.sql
Session altered.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Java created.
No errors.
Java altered.
No errors.
Package created.
No errors.
Package body created.
No errors.
PL/SQL procedure successfully completed.
Call completed.
Testing various timezone DST attributes
FAILED for America/Creston 1325401200000 -25200000 0
FAILED for America/Resolute 1325397600000 -21600000 0
FAILED for America/Resolute 1331452799999 -21600000 0
FAILED for America/Resolute 1331452800000 -21600000 3600000
FAILED for America/Resolute 1352012399999 -21600000 3600000
FAILED for America/Resolute 1352012400000 -21600000 0
FAILED for America/Resolute 1362902399999 -21600000 0
FAILED for America/Resolute 1362902400000 -21600000 3600000
FAILED for America/Resolute 1383461999999 -21600000 3600000
FAILED for America/Resolute 1383462000000 -21600000 0
FAILED for America/Resolute 1394351999999 -21600000 0
FAILED for America/Resolute 1394352000000 -21600000 3600000
FAILED for America/Resolute 1414911599999 -21600000 3600000
FAILED for America/Resolute 1414911600000 -21600000 0
FAILED for America/Resolute 1425801599999 -21600000 0
FAILED for America/Resolute 1425801600000 -21600000 3600000
FAILED for America/Resolute 1446361199999 -21600000 3600000
FAILED for America/Resolute 1446361200000 -21600000 0
FAILED for America/Resolute 1457855999999 -21600000 0
FAILED for America/Resolute 1457856000000 -21600000 3600000
FAILED for America/Resolute 1478415599999 -21600000 3600000
FAILED for America/Resolute 1478415600000 -21600000 0
FAILED for Chile/EasterIsland 1335668399999 -21600000 3600000
FAILED for Chile/EasterIsland 1346558400000 -21600000 3600000
FAILED for Pacific/Easter 1335668399999 -21600000 3600000
FAILED for Pacific/Easter 1346558400000 -21600000 3600000
FAILED for America/Havana 1333256399999 -18000000 0
FAILED for America/Havana 1352005199999 -18000000 3600000
FAILED for America/Havana 1383454799999 -18000000 3600000
FAILED for America/Havana 1414904399999 -18000000 3600000
FAILED for America/Havana 1446353999999 -18000000 3600000
FAILED for America/Havana 1478408399999 -18000000 3600000
FAILED for America/Port-au-Prince 1331449200000 -18000000 3600000
FAILED for America/Port-au-Prince 1352008799999 -18000000 3600000
FAILED for Cuba 1333256399999 -18000000 0
FAILED for Cuba 1352005199999 -18000000 3600000
FAILED for Cuba 1383454799999 -18000000 3600000
FAILED for Cuba 1414904399999 -18000000 3600000
FAILED for Cuba 1446353999999 -18000000 3600000
FAILED for Cuba 1478408399999 -18000000 3600000
FAILED for America/Goose_Bay 1331445599999 -14400000 0
FAILED for America/Goose_Bay 1352005199999 -14400000 3600000
FAILED for America/Goose_Bay 1362895199999 -14400000 0
FAILED for America/Goose_Bay 1383454799999 -14400000 3600000
FAILED for America/Goose_Bay 1394344799999 -14400000 0
FAILED for America/Goose_Bay 1414904399999 -14400000 3600000
FAILED for America/Goose_Bay 1425794399999 -14400000 0
FAILED for America/Goose_Bay 1446353999999 -14400000 3600000
FAILED for America/Goose_Bay 1457848799999 -14400000 0
FAILED for America/Goose_Bay 1478408399999 -14400000 3600000
FAILED for America/Kralendijk 1325390400000 -14400000 0
FAILED for America/Lower_Princes 1325390400000 -14400000 0
FAILED for America/Santiago 1335668399999 -14400000 3600000
FAILED for America/Santiago 1346558400000 -14400000 3600000
FAILED for Antarctica/Palmer 1335668399999 -14400000 3600000
FAILED for Antarctica/Palmer 1346558400000 -14400000 3600000
FAILED for Chile/Continental 1335668399999 -14400000 3600000
FAILED for Chile/Continental 1346558400000 -14400000 3600000
FAILED for America/St_Johns 1331443799999 -12600000 0
FAILED for America/St_Johns 1352003399999 -12600000 3600000
FAILED for America/St_Johns 1362893399999 -12600000 0
FAILED for America/St_Johns 1383452999999 -12600000 3600000
FAILED for America/St_Johns 1394342999999 -12600000 0
FAILED for America/St_Johns 1414902599999 -12600000 3600000
FAILED for America/St_Johns 1425792599999 -12600000 0
FAILED for America/St_Johns 1446352199999 -12600000 3600000
FAILED for America/St_Johns 1457846999999 -12600000 0
FAILED for America/St_Johns 1478406599999 -12600000 3600000
FAILED for CNT 1331443799999 -12600000 0
FAILED for CNT 1352003399999 -12600000 3600000
FAILED for CNT 1362893399999 -12600000 0
FAILED for CNT 1383452999999 -12600000 3600000
FAILED for CNT 1394342999999 -12600000 0
FAILED for CNT 1414902599999 -12600000 3600000
FAILED for CNT 1425792599999 -12600000 0
FAILED for CNT 1446352199999 -12600000 3600000
FAILED for CNT 1457846999999 -12600000 0
FAILED for CNT 1478406599999 -12600000 3600000
FAILED for Canada/Newfoundland 1331443799999 -12600000 0
FAILED for Canada/Newfoundland 1352003399999 -12600000 3600000
FAILED for Canada/Newfoundland 1362893399999 -12600000 0
FAILED for Canada/Newfoundland 1383452999999 -12600000 3600000
FAILED for Canada/Newfoundland 1394342999999 -12600000 0
FAILED for Canada/Newfoundland 1414902599999 -12600000 3600000
FAILED for Canada/Newfoundland 1425792599999 -12600000 0
FAILED for Canada/Newfoundland 1446352199999 -12600000 3600000
FAILED for Canada/Newfoundland 1457846999999 -12600000 0
FAILED for Canada/Newfoundland 1478406599999 -12600000 3600000
FAILED for America/Araguaina 1350788400000 -10800000 3600000
FAILED for America/Araguaina 1361066399999 -10800000 3600000
FAILED for America/Araguaina 1382238000000 -10800000 3600000
FAILED for America/Araguaina 1392515999999 -10800000 3600000
FAILED for America/Araguaina 1413687600000 -10800000 3600000
FAILED for America/Araguaina 1424570399999 -10800000 3600000
FAILED for America/Araguaina 1445137200000 -10800000 3600000
FAILED for America/Araguaina 1456019999999 -10800000 3600000
FAILED for America/Araguaina 1476586800000 -10800000 3600000
FAILED for America/Bahia 1325383200000 -10800000 3600000
FAILED for America/Bahia 1330221599999 -10800000 3600000
FAILED for Atlantic/Stanley 1325386800000 -10800000 0
FAILED for Africa/Casablanca 1335664800000 0 3600000
FAILED for Africa/Casablanca 1342749599999 0 3600000
FAILED for Africa/Casablanca 1345428000000 0 3600000
FAILED for Africa/Casablanca 1348970399999 0 3600000
FAILED for Africa/Casablanca 1367114400000 0 3600000
FAILED for Africa/Casablanca 1380419999999 0 3600000
FAILED for Africa/Casablanca 1398564000000 0 3600000
FAILED for Africa/Casablanca 1411869599999 0 3600000
FAILED for Africa/Casablanca 1430013600000 0 3600000
FAILED for Africa/Casablanca 1443319199999 0 3600000
FAILED for Africa/Casablanca 1461463200000 0 3600000
FAILED for Africa/Casablanca 1474768799999 0 3600000
FAILED for Africa/Tripoli 1352505600000 3600000 0
FAILED for Africa/Tripoli 1364515199999 3600000 0
FAILED for Africa/Tripoli 1364515200000 3600000 3600000
FAILED for Africa/Tripoli 1382659199999 3600000 3600000
FAILED for Africa/Tripoli 1382659200000 3600000 0
FAILED for Africa/Tripoli 1395964799999 3600000 0
FAILED for Africa/Tripoli 1395964800000 3600000 3600000
FAILED for Africa/Tripoli 1414713599999 3600000 3600000
FAILED for Africa/Tripoli 1414713600000 3600000 0
FAILED for Africa/Tripoli 1427414399999 3600000 0
FAILED for Africa/Tripoli 1427414400000 3600000 3600000
FAILED for Africa/Tripoli 1446163199999 3600000 3600000
FAILED for Africa/Tripoli 1446163200000 3600000 0
FAILED for Africa/Tripoli 1458863999999 3600000 0
FAILED for Africa/Tripoli 1458864000000 3600000 3600000
FAILED for Africa/Tripoli 1477612799999 3600000 3600000
FAILED for Africa/Tripoli 1477612800000 3600000 0
FAILED for Libya 1352505600000 3600000 0
FAILED for Libya 1364515199999 3600000 0
FAILED for Libya 1364515200000 3600000 3600000
FAILED for Libya 1382659199999 3600000 3600000
FAILED for Libya 1382659200000 3600000 0
FAILED for Libya 1395964799999 3600000 0
FAILED for Libya 1395964800000 3600000 3600000
FAILED for Libya 1414713599999 3600000 3600000
FAILED for Libya 1414713600000 3600000 0
FAILED for Libya 1427414399999 3600000 0
FAILED for Libya 1427414400000 3600000 3600000
FAILED for Libya 1446163199999 3600000 3600000
FAILED for Libya 1446163200000 3600000 0
FAILED for Libya 1458863999999 3600000 0
FAILED for Libya 1458864000000 3600000 3600000
FAILED for Libya 1477612799999 3600000 3600000
FAILED for Libya 1477612800000 3600000 0
FAILED for Asia/Damascus 1333058400000 7200000 3600000
FAILED for Asia/Damascus 1364508000000 7200000 3600000
FAILED for Asia/Damascus 1395957600000 7200000 3600000
FAILED for Asia/Damascus 1427407200000 7200000 3600000
FAILED for Asia/Damascus 1458856800000 7200000 3600000
FAILED for Asia/Gaza 1333058400000 7200000 3600000
FAILED for Asia/Gaza 1348178399999 7200000 3600000
FAILED for Asia/Hebron 1325368800000 7200000 0
FAILED for Asia/Hebron 1333058399999 7200000 0
FAILED for Asia/Hebron 1333058400000 7200000 3600000
FAILED for Asia/Hebron 1348178399999 7200000 3600000
FAILED for Asia/Hebron 1348178400000 7200000 0
FAILED for Asia/Jerusalem 1381013999999 7200000 3600000
FAILED for Asia/Jerusalem 1412463599999 7200000 3600000
FAILED for Asia/Jerusalem 1443913199999 7200000 3600000
FAILED for Asia/Jerusalem 1458864000000 7200000 3600000
FAILED for Asia/Jerusalem 1475362800000 7200000 0
FAILED for Asia/Tel_Aviv 1381013999999 7200000 3600000
FAILED for Asia/Tel_Aviv 1412463599999 7200000 3600000
FAILED for Asia/Tel_Aviv 1443913199999 7200000 3600000
FAILED for Asia/Tel_Aviv 1458864000000 7200000 3600000
FAILED for Asia/Tel_Aviv 1475362800000 7200000 0
FAILED for Israel 1381013999999 7200000 3600000
FAILED for Israel 1412463599999 7200000 3600000
FAILED for Israel 1443913199999 7200000 3600000
FAILED for Israel 1458864000000 7200000 3600000
FAILED for Israel 1475362800000 7200000 0
FAILED for Africa/Juba 1325365200000 10800000 0
FAILED for Europe/Kaliningrad 1325365200000 10800000 0
FAILED for Europe/Minsk 1325365200000 10800000 0
FAILED for Europe/Moscow 1325361600000 14400000 0
FAILED for Europe/Samara 1325361600000 14400000 0
FAILED for Europe/Volgograd 1325361600000 14400000 0
FAILED for W-SU 1325361600000 14400000 0
FAILED for Asia/Yekaterinburg 1325354400000 21600000 0
FAILED for Antarctica/Davis 1325358000000 18000000 0
FAILED for Antarctica/Davis 1329854399999 18000000 0
FAILED for Asia/Novokuznetsk 1325350800000 25200000 0
FAILED for Asia/Novosibirsk 1325350800000 25200000 0
FAILED for Asia/Omsk 1325350800000 25200000 0
FAILED for Antarctica/Casey 1325336400000 39600000 0
FAILED for Antarctica/Casey 1329843599999 39600000 0
FAILED for Asia/Krasnoyarsk 1325347200000 28800000 0
FAILED for Asia/Irkutsk 1325343600000 32400000 0
FAILED for Asia/Yakutsk 1325340000000 36000000 0
FAILED for Asia/Sakhalin 1325336400000 39600000 0
FAILED for Asia/Vladivostok 1325336400000 39600000 0
FAILED for Asia/Anadyr 1325332800000 43200000 0
FAILED for Asia/Kamchatka 1325332800000 43200000 0
FAILED for Asia/Magadan 1325332800000 43200000 0
FAILED for Pacific/Fiji 1325329200000 43200000 3600000
FAILED for Pacific/Fiji 1327154399999 43200000 3600000
FAILED for Pacific/Fiji 1350741600000 43200000 3600000
FAILED for Pacific/Fiji 1358603999999 43200000 3600000
FAILED for Pacific/Fiji 1382191200000 43200000 3600000
FAILED for Pacific/Fiji 1390053599999 43200000 3600000
FAILED for Pacific/Fiji 1413640800000 43200000 3600000
FAILED for Pacific/Fiji 1421503199999 43200000 3600000
FAILED for Pacific/Fiji 1445090400000 43200000 3600000
FAILED for Pacific/Fiji 1453557599999 43200000 3600000
FAILED for Pacific/Fiji 1477144800000 43200000 3600000
FAILED for MIT 1325325600000 46800000 3600000
FAILED for MIT 1333202399999 46800000 3600000
FAILED for MIT 1333202400000 46800000 0
FAILED for MIT 1348927199999 46800000 0
FAILED for MIT 1348927200000 46800000 3600000
FAILED for MIT 1365256799999 46800000 3600000
FAILED for MIT 1365256800000 46800000 0
FAILED for MIT 1380376799999 46800000 0
FAILED for MIT 1380376800000 46800000 3600000
FAILED for MIT 1396706399999 46800000 3600000
FAILED for MIT 1396706400000 46800000 0
FAILED for MIT 1411826399999 46800000 0
FAILED for MIT 1411826400000 46800000 3600000
FAILED for MIT 1428155999999 46800000 3600000
FAILED for MIT 1428156000000 46800000 0
FAILED for MIT 1443275999999 46800000 0
FAILED for MIT 1443276000000 46800000 3600000
FAILED for MIT 1459605599999 46800000 3600000
FAILED for MIT 1459605600000 46800000 0
FAILED for MIT 1474725599999 46800000 0
FAILED for MIT 1474725600000 46800000 3600000
FAILED for Pacific/Apia 1325325600000 46800000 3600000
FAILED for Pacific/Apia 1333202399999 46800000 3600000
FAILED for Pacific/Apia 1333202400000 46800000 0
FAILED for Pacific/Apia 1348927199999 46800000 0
FAILED for Pacific/Apia 1348927200000 46800000 3600000
FAILED for Pacific/Apia 1365256799999 46800000 3600000
FAILED for Pacific/Apia 1365256800000 46800000 0
FAILED for Pacific/Apia 1380376799999 46800000 0
FAILED for Pacific/Apia 1380376800000 46800000 3600000
FAILED for Pacific/Apia 1396706399999 46800000 3600000
FAILED for Pacific/Apia 1396706400000 46800000 0
FAILED for Pacific/Apia 1411826399999 46800000 0
FAILED for Pacific/Apia 1411826400000 46800000 3600000
FAILED for Pacific/Apia 1428155999999 46800000 3600000
FAILED for Pacific/Apia 1428156000000 46800000 0
FAILED for Pacific/Apia 1443275999999 46800000 0
FAILED for Pacific/Apia 1443276000000 46800000 3600000
FAILED for Pacific/Apia 1459605599999 46800000 3600000
FAILED for Pacific/Apia 1459605600000 46800000 0
FAILED for Pacific/Apia 1474725599999 46800000 0
FAILED for Pacific/Apia 1474725600000 46800000 3600000
FAILED for Pacific/Fakaofo 1325329200000 46800000 0
##
Bug is in fact present, so this patch is needed
##
(Perhaps) modifying ild_ub1$ for rehotloading of sun/util/calendar/ZoneInfoFile
obj# = 45849
nbytes %d = 64512, 1st read = 64512, 2nd read = -1
96 397776 385632 12144 joxbtflag>>8 = 3
===
PL/SQL procedure successfully completed.
########################################################
Bug is in fact present, so this patch is needed
Proceed by restarting the database and running script
fixTZb
########################################################
PL/SQL procedure successfully completed.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup migrate
ORACLE instance started.
Total System Global Area 4.0899E+10 bytes
Fixed Size 2168160 bytes
Variable Size 4294970016 bytes
Database Buffers 3.6507E+10 bytes
Redo Buffers 95125504 bytes
Database mounted.
Database opened.
SQL> @?/javavm/admin/fixTZb.sql
Session altered.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Call completed.
Testing various timezone DST attributes
########################################################
Bug is no longer present. Patch succeeded.
########################################################
PL/SQL procedure successfully completed.
drop java source fixTZ
drop package fixTZ
PL/SQL procedure successfully completed.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 4.0899E+10 bytes
Fixed Size 2168160 bytes
Variable Size 4294970016 bytes
Database Buffers 3.6507E+10 bytes
Redo Buffers 95125504 bytes
Database mounted.
Database opened.
SQL>
Всё, DST установлен.
Более коротко делаем так:
Проверить Timezone Version в БД:
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;
SQL> SELECT version FROM v$timezone_file; VERSION ---------- 19
Для установки DST обновления Oracle JVM (Patch 15897884) необходимо:
1) Выполнить скрипт:
SQL> @?/javavm/admin/fixTZa.sql
2) Выполнить:
SQL> shutdown immediate
SQL> startup migrate
3) Выполнить скрипт:
SQL> @?/javavm/admin/fixTZb.sql
4) Выполнить:
SQL> shutdown immediate
SQL> startup
