環境:AIX5.3

Oracle 10.2.0.2

[@more@]

1. 對資料庫做FULL BACKUP

2. 停掉資料庫和TNS
[TEST:oradev] /oradev1/home> sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.2.0 - Production on Tue Mar 13 11:48:51 2007

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

[TEST:oradev] /oradev1> lsnrctl stop

LSNRCTL for IBM/AIX RISC System/6000: Version 10.2.0.2.0 - Production on 13-MAR-
2007 10:44:07

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=TESTSERVER)(PORT=1521)))
The command completed successfully

3.Backup Oracle App Directory
[TEST:oradev] /oradev1> df -k
Filesystem 1024-blocks Free %Used Iused %Iused Mounted on
/dev/hd4 262144 245896 7% 2547 5% /
/dev/hd2 2097152 303244 86% 32732 32% /usr
/dev/hd9var 262144 177268 33% 707 2% /var
/dev/hd3 2097152 1468296 30% 2003 1% /tmp
/dev/fwdump 262144 261776 1% 4 1% /var/adm/ras/platform
/dev/hd1 3145728 3117160 1% 112 1% /home
/proc - - - - - /proc
/dev/hd10opt 262144 161240 39% 3655 10% /opt
/dev/fslv00 15728640 9918616 37% 25351 2% /oracle
/dev/fslv01 15728640 5204560 67% 8291 1% /sw
/dev/fslv02 12582912 7852620 38% 19148 2% /oradev1
/dev/fslv03 104857600 34658172 67% 26 1% /devTESTdata
/dev/fslv05 41943040 41217652 2% 61 1% /devTESTarch1
/dev/fslv08 52428800 48455768 8% 8 1% /testdata
/dev/fslv04 41943040 41023744 3% 182 1% /qaspmarch
/dev/fslv06 5242880 5241752 1% 4 1% /backup_arch
/dev/fslv07 15728640 11849016 25% 17855 1% /oratest
[TEST:oradev] /oradev1> tar cvf app.tar app
a app
a app/oracle
a app/oracle/product
a app/oracle/product/10.2.0
a app/oracle/product/10.2.0/inventory
a app/oracle/product/10.2.0/inventory/Queries21
......................................

4.開始大JVM 補丁

- 備份原來的相關檔案
[TEST:oradev] /oradev1/home> vi bak_timefile
mv $ORACLE_HOME/javavm/lib/zi/Africa/Timbuktu $ORACLE_HOME/javavm/lib/zi/Africa/Timbuktu.bak
mv $ORACLE_HOME/javavm/lib/zi/America/Buenos_Aires $ORACLE_HOME/javavm/lib/zi/America/Buenos_Aires.bak
mv $ORACLE_HOME/javavm/lib/zi/America/Catamarca $ORACLE_HOME/javavm/lib/zi/America/Catamarca.bak
mv $ORACLE_HOME/javavm/lib/zi/America/Coral_Harbour $ORACLE_HOME/javavm/lib/zi/America/Coral_Harbour.bak
mv $ORACLE_HOME/javavm/lib/zi/America/Cordoba $ORACLE_HOME/javavm/lib/zi/America/Cordoba.bak
mv $ORACLE_HOME/javavm/lib/zi/America/Indianapolis $ORACLE_HOME/javavm/lib/zi/America/Indianapolis.bak
mv $ORACLE_HOME/javavm/lib/zi/America/Jujuy $ORACLE_HOME/javavm/lib/zi/America/Jujuy.bak
mv $ORACLE_HOME/javavm/lib/zi/America/Louisville $ORACLE_HOME/javavm/lib/zi/America/Louisville.bak
mv $ORACLE_HOME/javavm/lib/zi/America/Mendoza $ORACLE_HOME/javavm/lib/zi/America/Mendoza.bak
mv $ORACLE_HOME/javavm/lib/zi/Europe/Belfast $ORACLE_HOME/javavm/lib/zi/Europe/Belfast.bak
mv $ORACLE_HOME/javavm/lib/zi/Pacific/Yap $ORACLE_HOME/javavm/lib/zi/Pacific/Yap.bak
~
:q!

[TEST:oradev] /oradev1/home> chmod 755 *timefile
- 一個檔案沒找到
[TEST:oradev] /oradev1/home> ./bak_timefile
mv: cannot rename /oradev1/app/oracle/product/10.2.0/javavm/lib/zi/America/Coral
_Harbour to /oradev1/app/oracle/product/10.2.0/javavm/lib/zi/America/Coral_Harbo
ur.bak:
No such file or directory
[

ind:oradev] /oradev1/app/oracle/product/10.2.0/javavm/lib/zi/America> Harbour<
Coral_Harbour not found
- 打OPATCH
[
[TEST:oradev] /oradev1/home/timezone_patch> cd 5865568

[TEST:oradev] /oradev1/home/timezone_patch/5865568> opatch apply
Invoking OPatch 10.2.0.2.0

Oracle Home : /oradev1/app/oracle/product/10.2.0
Central Inventory : /oradev1/app/oracle/oraInventory
from : /oradev1/app/oracle/product/10.2.0/oraInst.loc
OPatch version : 10.2.0.2.0
OUI version : 10.2.0.2.0
OUI location : /oradev1/app/oracle/product/10.2.0/oui
Log file location : /oradev1/app/oracle/product/10.2.0/cfgtoollogs/opatch/opatch
-00_Mar_13_10-54-03-GMT+08_Tue.log

ApplySession applying interim patch '5865568' to OH '/oradev1/app/oracle/product
/10.2.0'

OPatch detected non-cluster Oracle Home from the inventory and will patch the lo
cal system only.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local sy
stem.
(Oracle Home = '/oradev1/app/oracle/product/10.2.0')

Is the local system ready for patching?

Do you want to proceed? [y|n]
y
User Responded with: Y
Backing up files and inventory (not for auto-rollback) for the Oracle Home
Backing up files affected by the patch '5865568' for restore. This might take a
while...
Backing up files affected by the patch '5865568' for rollback. This might take a
while...

Patching component oracle.javavm.server, 10.2.0.2.0...
Copying file to "/oradev1/app/oracle/product/10.2.0/javavm/lib/zi/Africa/Cairo"
Copying file to "/oradev1/app/oracle/product/10.2.0/javavm/lib/zi/Africa/Tunis"
Copying file to "/oradev1/app/oracle/product/10.2.0/javavm/lib/zi/America/Indian
....................................
apu"
ApplySession adding interim patch '5865568' to inventory

Verifying the update...
Inventory check OK: Patch ID 5865568 is registered in Oracle Home inventory with
proper meta-data.
Files check OK: Files from Patch ID 5865568 are present in Oracle Home.

The local system has been patched and can be restarted.

OPatch succeeded.
[TEST:oradev] /oradev1/home/timezone_patch/5865568> sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.2.0 - Production on Tue Mar 13 10:54:38 2007

Connected to an idle instance.

SQL> STARTUP
ORACLE instance started.

Total System Global Area 3758096384 bytes
Fixed Size 2075728 bytes
Variable Size 721421232 bytes
Database Buffers 3019898880 bytes
Redo Buffers 14700544 bytes
Database mounted.
Database opened.
SQL> @?/javavm/admin/fixTZa

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 Australia/Perth 2:0:10 12/3/2006
FAILED for Australia/Perth 1:0:10 3/25/2007
FAILED for America/Vancouver 10:0:0 3/11/2007
FAILED for America/Edmonton 10:0:0 3/11/2007
FAILED for America/St_Johns 10:0:0 3/11/2007
FAILED for America/New_York 10:0:0 3/11/2007
FAILED for America/Los_Angeles 10:0:0 3/11/2007
##
Bug is in fact present, so this patch is needed
##
(Perhaps) modifying ild_ub1$ for rehotloading of sun/util/calendar/ZoneInfoFile
obj# = 30858
nbytes %d = 64512, 1st read = 64512, 2nd read = -1
87 344070 333936 10134 joxbtflag>>8 = 3
===
(Perhaps) modifying ild_ub1$ for rehotloading of /c8a1ddf0_PolicyTable
obj# = 17345
nbytes %d = 20069, 1st read = 20069, 2nd read = -1
177 18340 4408 13932 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
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP MIGRATE
ORACLE instance started.

Total System Global Area 3758096384 bytes
Fixed Size 2075728 bytes
Variable Size 721421232 bytes
Database Buffers 3019898880 bytes
Redo Buffers 14700544 bytes
Database mounted.
Database opened.
SQL> @?/javavm/admin/fixTZb

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
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !
$ opatch lsinventory
Invoking OPatch 10.2.0.2.0

Oracle Home : /oradev1/app/oracle/product/10.2.0
Central Inventory : /oradev1/app/oracle/oraInventory
from : /oradev1/app/oracle/product/10.2.0/oraInst.loc
OPatch version : 10.2.0.2.0
OUI version : 10.2.0.2.0
OUI location : /oradev1/app/oracle/product/10.2.0/oui
Log file location : /oradev1/app/oracle/product/10.2.0/cfgtoollogs/opatch/opatch
-00_Mar_13_10-58-15-GMT+08_Tue.log

Lsinventory Output file location : /oradev1/app/oracle/product/10.2.0/cfgtoollog
s/opatch/lsinv/lsinventory-00_Mar_13_10-58-15-GMT+08_Tue.txt

--------------------------------------------------------------------------------
Installed Top-level Products (2):

Oracle Database 10g 10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 1 10.2.0.2.0
There are 2 products installed in this Oracle Home.

Interim patches (1) :

Patch 5865568 : applied on Tue Mar 13 10:54:15 GMT+08:00 2007
Created on 15 Feb 2007, 19:51:37 hrs PST8PDT
Bugs fixed:
5865568

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

OPatch succeeded.
$ exit

SQL> ! ps -ef | grep ora_
oracle 262282 1 0 15:40:22 - 0:09 ora_pmon_qaspm2
oracle 278696 1 0 15:40:34 - 0:00 ora_qmnc_qaspm2
oracle 340174 1 0 15:40:25 - 0:08 ora_lck0_qaspm2
oracle 368782 1 0 15:40:30 - 0:00 ora_arc0_qaspm2
oracle 372912 1 0 15:40:23 - 0:00 ora_d000_qaspm2
oracle 376946 1 0 15:40:22 - 1:19 ora_lms1_qaspm2
oracle 401444 1 0 15:40:23 - 0:36 ora_mmnl_qaspm2
oracle 405738 1 0 15:40:23 - 0:14 ora_mmon_qaspm2
oracle 434200 1 0 15:40:23 - 0:17 ora_cjq0_qaspm2
oracle 438276 1 0 15:40:22 - 0:01 ora_psp0_qaspm2
oracle 450782 1 0 15:40:22 - 0:00 ora_reco_qaspm2
oracle 458806 1 0 15:40:22 - 0:03 ora_smon_qaspm2
oracle 491656 1 0 15:40:22 - 0:15 ora_ckpt_qaspm2
oracle 520346 1 0 15:40:22 - 0:02 ora_lgwr_qaspm2
oracle 524502 1 0 15:40:45 - 0:00 ora_q001_qaspm2
oracle 553000 1 0 15:40:22 - 0:26 ora_diag_qaspm2
oracle 561312 1 0 09:51:34 - 0:00 ora_pz99_qaspm2
oracle 569574 1 0 15:40:23 - 0:00 ora_s000_qaspm2
oracle 590042 1 0 15:40:22 - 1:25 ora_lmon_qaspm2
oracle 618740 1 0 15:40:30 - 0:00 ora_arc1_qaspm2
oradev 647210 1032276 0 10:59:22 pts/2 0:00 grep ora_
oracle 765978 1 0 15:40:22 - 0:03 ora_mman_qaspm2
oracle 774244 1 0 15:40:22 - 1:20 ora_lms0_qaspm2
oracle 794872 1 0 15:40:22 - 0:44 ora_lmd0_qaspm2
oracle 876608 1 0 15:40:22 - 0:02 ora_dbw0_qaspm2
oracle 880880 1 0 15:40:45 - 0:01 ora_q000_qaspm2

SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64
bit Production
With the Partitioning, OLAP and Data Mining options
[TEST:oradev] /oradev1/home/timezone_patch/5865568> cd ..
[TEST:oradev] /oradev1/home/timezone_patch> ls
5632264 p5632264_10202_AIX64-5L.zip
5746835 p5746835_10202_GENERIC.zip
5865568 p5865568_10202_GENERIC.zip
[TEST:oradev] /oradev1/home/timezone_patch> cd 5746835
[TEST:oradev] /oradev1/home/timezone_patch/5746835> ls
README.txt etc files
[TEST:oradev] /oradev1/home/timezone_patch/5746835> opatch apply
Invoking OPatch 10.2.0.2.0

Oracle Home : /oradev1/app/oracle/product/10.2.0
Central Inventory : /oradev1/app/oracle/oraInventory
from : /oradev1/app/oracle/product/10.2.0/oraInst.loc
OPatch version : 10.2.0.2.0
OUI version : 10.2.0.2.0
OUI location : /oradev1/app/oracle/product/10.2.0/oui
Log file location : /oradev1/app/oracle/product/10.2.0/cfgtoollogs/opatch/opatch
-00_Mar_13_11-19-18-GMT+08_Tue.log

ApplySession applying interim patch '5746835' to OH '/oradev1/app/oracle/product
/10.2.0'

OPatch detected non-cluster Oracle Home from the inventory and will patch the lo
cal system only.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local sy
stem.
(Oracle Home = '/oradev1/app/oracle/product/10.2.0')

Is the local system ready for patching?

Do you want to proceed? [y|n]
y
User Responded with: Y
Backing up files and inventory (not for auto-rollback) for the Oracle Home
Backing up files affected by the patch '5746835' for restore. This might take a
while...
Backing up files affected by the patch '5746835' for rollback. This might take a
while...

Patching component oracle.oracore.rsf, 10.2.0.2.0...
Copying file to "/oradev1/app/oracle/product/10.2.0/rdbms/admin/utltzuv2.sql"
Copying file to "/oradev1/app/oracle/product/10.2.0/oracore/zoneinfo/timezdif.cs
v"
ApplySession adding interim patch '5746835' to inventory

Verifying the update...
Inventory check OK: Patch ID 5746835 is registered in Oracle Home inventory with
proper meta-data.
Files check OK: Files from Patch ID 5746835 are present in Oracle Home.

The local system has been patched and can be restarted.

OPatch succeeded.
[TEST:oradev] /oradev1/home/timezone_patch/5746835> cd $ORACLE_HOME

[TEST:oradev] /oradev1/app/oracle/product/10.2.0> cd rdbms
[TEST:oradev] /oradev1/app/oracle/product/10.2.0/rdbms> ls
admin doc label.info log public
audit install lib mesg xml
demo jlib lib32 notes
[TEST:oradev] /oradev1/app/oracle/product/10.2.0/rdbms> cd admin

[TEST:oradev] /oradev1/app/oracle/product/10.2.0/rdbms/admin> tzuv2.sql.bak <
2c2
< Rem $Header: utltzuv2.sql 27-sep-2006.10:26:10 huagli Exp $
---
> Rem $Header: utltzuv2.sql 18-may-2005.11:20:49 srsubram Exp $
6c6
< Rem Copyright (c) 2003, 2006, Oracle. All rights reserved.
---
> Rem Copyright (c) 2003, 2005, Oracle. All rights reserved.
9c9
< Rem utltzuv2.sql - time zone file upgrade to a new version script
---
> Rem utltzuv2.sql - time zone file upgrade to version 2 script
12,13c12,13
< Rem The contents of the files timezone.dat and timezlrg.dat
< Rem are usually updated to a new version to reflect the transition rule
---
> Rem In 10g, the contents of the file timezone.dat and timezlrg.dat
> Rem are updated to the version 2 to reflect the transition rule
31c31
< Rem time zone file to the latest version. This is a pre-update script.
---
> Rem time zone file to the new version.
33,42c33
< Rem This script first determines the time zone version currently in use
< Rem before the upgrade. It then queries an external table to get all the

< Rem affected timezone regions between the current version (version before

< Rem the update) and the latest one. This external table points to the fil
e
< Rem timezdif.csv, which contains all the affected time zone names in each

< Rem version. Please make sure that you have the latest version of the
< Rem timezdif.csv (the one corresponding to the latest timezone data file)
,
< Rem before you run the script.
< Rem
< Rem Then, this script scans the database to find out all columns
---
> Rem This script scans the database to find out all columns
46c37
< Rem contain the values for the affected time zone names.
---
> Rem contain the values for these specific time zone names.
86,88c77,78
< Rem * This script needs to be run before upgrading to a new version time
< Rem zone file. Also, before running this script, please make sure that
< Rem you get the latest version of timezdif.csv file.
---
> Rem * This script needs to be run before upgrading to the version 2 time
> Rem zone file.
91,110d80
< Rem * This script is created only for Oracle 10.1 or higer. A separate
< Rem script is provided for Oracle 9i.
< Rem * Two files, tzuv2ext_*.log and tzuv2ext_*.bad will be created in
< Rem the directory of $ORACLE_HOME/oracore/zoneinfo when using the
< Rem external table for timezdif.csv file to get the affected time zones
.
< Rem After running the script, please refer to these two files to see if

< Rem there are any rows in timezdif.csv, which are not loaded. If so,
< Rem it might affect the correct selection of affected tables in the dat
abase.
< Rem You can always delete tzuv2ext_*.log and tzuv2ext_*.bad.
< Rem * A Java stored procedure is created to get the system file separator
,
< Rem e.g., Windows uses '' and Unix uses '/'. Before you run this scrip
t,
< Rem make sure that the Java source/class object does not conflict with
< Rem any existing Java source/class object in the database. If it does,
please
< Rem change the Java source/class object name GetFileSeparator to some o
ther
< Rem name in the script. Also make sure that the Java stored procedure d
oes not
< Rem conflict with any existing function in the database. If it does, pl
ease
< Rem change the function name GET_FILE_SEPARATOR to some other name in t
he script.
< Rem If renaming is needed, please make sure that you change all the occ
urences
< Rem to make them consistent.
< Rem
113,114d82
< Rem huagli 09/27/06 - Backport huagli_bug-5126270 from main
< Rem time zone update
124d91
<
140,150d106
< Rem========================================================================
< Rem Check any existing table with this name sys.sys_tzuv2_temptab1
< Rem========================================================================
< DROP TABLE sys.sys_tzuv2_temptab1
< /
< CREATE TABLE sys.sys_tzuv2_temptab1
< (
< time_zone_name VARCHAR2(60)
< )
< /
<
153,166c109,117
< dbv VARCHAR2(10);
< dbtzv VARCHAR2(5);
< numrows NUMBER;
< TYPE cursor_t IS REF CURSOR;
< cursor_tstz cursor_t;
< tstz_owner VARCHAR2(30);
< tstz_tname VARCHAR2(30);
< tstz_qcname VARCHAR2(4000);
< tz_version NUMBER;
< oracle_home VARCHAR(4000);
< tz_count INTEGER;
< plsql_block VARCHAR2(200);
< file_separator VARCHAR2(3);
<
---
> dbv VARCHAR2(10);
> numrows NUMBER;
> TYPE cursor_t IS REF CURSOR;
> cursor_tstz cursor_t;
> tstz_owner VARCHAR2(30);
> tstz_tname VARCHAR2(30);
> tstz_qcname VARCHAR2(4000);
> tz_version NUMBER;
>
169,172d119
< --========================================================================
< -- Make sure that only version 10 or higher uses this script
< --========================================================================
<
174c121,122
<
---
>
>
178,179c126,127
< 'Release 8.1.7.');
< DBMS_OUTPUT.PUT_LINE('No need to validate TIMEZONE data.');
---
> 'Release 8.1.7');
> DBMS_OUTPUT.PUT_LINE('No need to validate TIMEZONE data');
182,183c130,131
<
< IF dbv in ('9.0.1.','9.2.0.')
---
>
> IF dbv = '10.1.0'
185,207d132
< DBMS_OUTPUT.PUT_LINE('Please contact Oracle support to get the script ' ||
< 'for Release 9.0.1 or 9.2.0.');
< RETURN;
< END IF;
<
< --========================================================================
< -- Get $ORACLE_HOME
< --========================================================================
<
< plsql_block := 'BEGIN SYS.DBMS_SYSTEM.GET_ENV(:1, :2); END;';
< EXECUTE IMMEDIATE plsql_block USING 'ORACLE_HOME', OUT oracle_home;
<
< --========================================================================
< -- Use an external table created on timezdif.csv file to get the
< -- affected time zones. In this way, every time when time zone information
< -- changes, we only need to provide user with the updated timezdif.csv file
< -- without changing utltzuv2.sql.
< --
< -- 1. Setup the directory for timezdif.csv and log files(log, bad log)
< -- 2. Check any existing external table with this name
< -- sys.sys_tzuv2_affected_regions
< -- 3. Setup the parameters of the external table
< --========================================================================
209,224c134
< --========================================================================
< -- Create a Java stored procedure to get the file separator
< --========================================================================
<
< EXECUTE IMMEDIATE 'CREATE OR REPLACE AND COMPILE JAVA SOURCE
< NAMED "GetFileSeparator" AS
< public class GetFileSeparator {
< public static String get() {
< return System.getProperty("file.separator");
< }
< }';
<
< EXECUTE IMMEDIATE 'CREATE OR REPLACE FUNCTION GET_FILE_SEPARATOR
< RETURN VARCHAR2
< AS LANGUAGE JAVA
< NAME ''GetFileSeparator.get() return java.lang.String'';'
;
---
> EXECUTE IMMEDIATE 'SELECT version FROM v$timezone_file' INTO tz_version;
225a136,142
> IF tz_version = 2
> THEN
> DBMS_OUTPUT.PUT_LINE('TIMEZONE data is consistent with version 2 ' ||
> 'transition rules');
> DBMS_OUTPUT.PUT_LINE('No need to validate TIMEZONE data');
> RETURN;
> END IF;
227,243d143
< plsql_block := 'BEGIN :1 := GET_FILE_SEPARATOR(); END;';
< EXECUTE IMMEDIATE plsql_block USING OUT file_separator;
<
< EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY timezdif_dir AS ''' ||
< oracle_home || file_separator || 'oracore' ||
< file_separator || 'zoneinfo''';
<
<
< EXECUTE IMMEDIATE
< 'SELECT count(*)
< FROM all_tables
< WHERE owner = ''SYS'' and table_name = ''SYS_TZUV2_AFFECTED_REGIONS'''
< INTO tz_count;
<
< IF tz_count <> 0
< THEN
< EXECUTE IMMEDIATE 'DROP TABLE sys.sys_tzuv2_affected_regions';
246,281c146
<
< EXECUTE IMMEDIATE 'CREATE TABLE sys.sys_tzuv2_affected_regions
< (
< version NUMBER,
< time_zone_name VARCHAR2(40),
< from_year NUMBER,
< to_year NUMBER
< )
< ORGANIZATION EXTERNAL
< (
< TYPE ORACLE_LOADER
< DEFAULT DIRECTORY timezdif_dir
< ACCESS PARAMETERS
< (
< records delimited by newline
< badfile timezdif_dir:''tzuvext%a_%p.bad''
< logfile timezdif_dir:''tzuvext%a_%p.log''
< fields terminated by '',''
< lrtrim
< missing field values are null
< (
< version, time_zone_name, from_year, to_year
< )
< )
< LOCATION (''timezdif.csv'')
< )
< REJECT LIMIT UNLIMITED';
<
< --======================================================================
< -- Check if the TIMEZONE data is consistent with the latest version.
< --======================================================================
<
< EXECUTE IMMEDIATE 'SELECT version FROM v$timezone_file' INTO tz_version;
< EXECUTE IMMEDIATE 'SELECT MAX(version) FROM sys_tzuv2_affected_regions' INTO
dbtzv;
<
< IF tz_version = dbtzv
---
> IF dbv in ('9.0.1.','9.2.0.','10.1.0')
283,287d147
< DBMS_OUTPUT.PUT_LINE('TIMEZONE data is consistent with the latest version
' ||
< dbtzv || ' transition rules');
< DBMS_OUTPUT.PUT_LINE('No need to validate TIMEZONE data');
< RETURN;
< END IF;
289,298c149,160
< --======================================================================
< -- Get tables with columns defined as type TIMESTAMP WITH TIME ZONE.
< --======================================================================
<
< OPEN cursor_tstz FOR
< 'SELECT atc.owner, atc.table_name, atc.qualified_col_name ' ||
< 'FROM "ALL_TAB_COLS" atc, "ALL_TABLES" at ' ||
< 'WHERE data_type LIKE ''TIMESTAMP%WITH TIME ZONE''' ||
< ' AND atc.owner = at.owner AND atc.table_name = at.table_name ' ||
< 'ORDER BY atc.owner, atc.table_name, atc.column_name';
---
> IF dbv in ('9.0.1.','9.2.0.')
> THEN
> OPEN cursor_tstz FOR
> 'SELECT owner, table_name, column_name ' ||
> 'FROM ALL_TAB_COLS ' ||
> 'WHERE data_type LIKE ''TIMESTAMP%WITH TIME ZONE''';
> ELSE
> OPEN cursor_tstz FOR
> 'SELECT owner, table_name, qualified_col_name ' ||
> 'FROM ALL_TAB_COLS ' ||
> 'WHERE data_type LIKE ''TIMESTAMP%WITH TIME ZONE''';
> END IF;
300,310c162,189
< --======================================================================
< -- Query the external table to get all the affected time zones based
< -- on the current database time zone version, and then put them into
< -- a temporary table, sys_tzuv2_temptab1.
< --======================================================================
<
< EXECUTE IMMEDIATE
< 'INSERT INTO sys.sys_tzuv2_temptab1
< SELECT DISTINCT time_zone_name
< FROM sys.sys_tzuv2_affected_regions t
< WHERE t.version > ' || tz_version;
---
> LOOP
> BEGIN
> FETCH cursor_tstz INTO tstz_owner, tstz_tname, tstz_qcname;
> EXIT WHEN cursor_tstz%NOTFOUND;
> EXECUTE IMMEDIATE
> 'SELECT COUNT(1) FROM ' ||
> tstz_owner || '."' || tstz_tname || '" t_alias' ||
> ' WHERE TO_CHAR(t_alias.' || tstz_qcname || ', ''TZR'')
> IN (''AMERICA/ST_JOHNS'', ''AMERICA/WINNIPEG'',
> ''AMERICA/MEXICO_CITY'', ''AMERICA/MAZATLAN'',
> ''AMERICA/TIJUANA'', ''AMERICA/HAVANA'',
> ''AMERICA/SAO_PAULO'', ''AMERICA/SANTIAGO'',
> ''AMERICA/GOOSE_BAY'', ''AMERICA/IQUALUIT'',
> ''AMERICA/RAMKIN_INLET'', ''AMERICA/CAMBRIDGE_BAY'',
> ''AMERICA/CANCUN'', ''AMERICA/CHIHUAHUA'',
> ''AMERICA/BUENOS_AIRES'', ''AMERICA/FORTALEZA'',
> ''AMERICA/ARAGUAINA'', ''AMERICA/MACEIO'',
> ''AMERICA/CUIABA'', ''AMERICA/BOA_VISTA'',
> ''ASIA/TEHRAN'', ''ASIA/JERUSALEM'',
> ''ASIA/BAGHDAD'', ''ASIA/AMMAN'',
> ''ASIA/ALMATY'', ''ASIA/AQTOBE'',
> ''ASIA/AQTAU'', ''ASIA/KARACHI'',
> ''ASIA/ANADYR'', ''ATLANTIC/STANLEY'',
> ''AUSTRALIA/LORD_HOWE'', ''PACIFIC/FIJI'',
> ''PACIFIC/GUAM'', ''PACIFIC/SAIPAN'',
> ''PACIFIC/EASTER'', ''PACIFIC/TONGATAPU'',
> ''EUROPE/TALLINN'', ''EUROPE/RIGA'',
> ''EUROPE/VILNUS'')' INTO numrows;
312,334c191,195
< EXECUTE IMMEDIATE 'ANALYZE TABLE sys.sys_tzuv2_temptab1 ' ||
< 'COMPUTE STATISTICS';
<
< --======================================================================
< -- Check regular table columns.
< --======================================================================
< LOOP
< BEGIN
< FETCH cursor_tstz INTO tstz_owner, tstz_tname, tstz_qcname;
< EXIT WHEN cursor_tstz%NOTFOUND;
<
< EXECUTE IMMEDIATE
< 'SELECT COUNT(1) FROM ' ||
< tstz_owner || '."' || tstz_tname || '" t_alias, ' ||
< ' sys.sys_tzuv2_temptab1 r ' ||
< ' WHERE UPPER(r.time_zone_name) = ' ||
< ' UPPER(TO_CHAR(t_alias.' || tstz_qcname || ', ''TZR'')) ' INTO
numrows;
<
< IF numrows > 0 THEN
< EXECUTE IMMEDIATE ' INSERT INTO sys.sys_tzuv2_temptab VALUES (''' |
|
< tstz_owner || ''',''' || tstz_tname || ''',''' ||
< tstz_qcname || ''',' || numrows || ', ''NO'')';
< END IF;
---
> IF numrows > 0 THEN
> EXECUTE IMMEDIATE ' INSERT INTO sys.sys_tzuv2_temptab VALUES (''' ||
> tstz_owner || ''',''' || tstz_tname || ''',''' ||
> tstz_qcname || ''',' || numrows || ', ''NO'')';
> END IF;
338,343c199,204
< DBMS_OUTPUT.PUT_LINE('OWNER : ' || tstz_owner);
< DBMS_OUTPUT.PUT_LINE('TABLE : ' || tstz_tname);
< DBMS_OUTPUT.PUT_LINE('COLUMN : ' || tstz_qcname);
< DBMS_OUTPUT.PUT_LINE(SQLERRM);
< END;
< END LOOP;
---
> dbms_output.put_line('OWNER : ' || tstz_owner);
> dbms_output.put_line('TABLE : ' || tstz_tname);
> dbms_output.put_line('COLUMN : ' || tstz_qcname);
> dbms_output.put_line(sqlerrm);
> END;
> END LOOP;
345,354c206,207
< --======================================================================
< -- Check nested table columns.
< --======================================================================
<
< EXECUTE IMMEDIATE
< 'INSERT INTO sys.sys_tzuv2_temptab
< SELECT owner, table_name, qualified_col_name, NULL, ''YES''
< FROM ALL_NESTED_TABLE_COLS
< WHERE data_type like ''TIMESTAMP%WITH TIME ZONE''';
<
---
> IF dbv in ('10.1.0')
> THEN
356,358c209,215
< DBMS_OUTPUT.PUT_LINE('Query sys.sys_tzuv2_temptab table to see ' ||
< 'if any TIMEZONE data is affected by version ' || dbtzv
||
< ' transition rules');
---
> BEGIN
> EXECUTE IMMEDIATE
> 'INSERT INTO sys.sys_tzuv2_temptab
> SELECT owner, table_name, qualified_col_name, NULL, ''YES''
> FROM ALL_NESTED_TABLE_COLS
> WHERE data_type like ''TIMESTAMP%WITH TIME ZONE''';
> END;
360,371c217
< EXCEPTION
< WHEN OTHERS THEN
< IF INSTR(SQLERRM, 'KUP-04063') != 0
< THEN
< DBMS_OUTPUT.PUT_LINE('Directory for file timezdif.csv is not correctly s
pecified!');
< DBMS_OUTPUT.PUT_LINE(sqlerrm);
< ELSIF INSTR(SQLERRM, 'KUP-04040') != 0
< THEN
< DBMS_OUTPUT.PUT_LINE('File timezdif.csv in TIMEZDIF_DIR not found!');
< ELSE
< DBMS_OUTPUT.PUT_LINE(SQLERRM);
< END IF;
---
> END IF;
373,407c219,221
< END;
< /
<
< COMMIT
< /
<
< DECLARE
< tz_count NUMBER;
<
< BEGIN
<
< --========================================================================
< -- After obtaining the file separator, drop the Java stroed procedure
< --========================================================================
<
< EXECUTE IMMEDIATE
< 'SELECT count(*)
< FROM user_objects
< WHERE object_name = ''GetFileSeparator'' and UPPER(object_type) like ''JA
VA%'''
< INTO tz_count;
<
< IF tz_count > 0
< THEN
< EXECUTE IMMEDIATE 'DROP JAVA SOURCE "GetFileSeparator"';
< END IF;
<
< EXECUTE IMMEDIATE
< 'SELECT count(*)
< FROM user_objects
< WHERE object_name = ''GET_FILE_SEPARATOR'' and UPPER(object_type) = ''FUN
CTION'''
< INTO tz_count;
<
< IF tz_count > 0
< THEN
< EXECUTE IMMEDIATE 'DROP FUNCTION GET_FILE_SEPARATOR';
---
> DBMS_OUTPUT.PUT_LINE('Query sys.sys_tzuv2_temptab Table to see ' ||
> 'if any TIMEZONE data is affected by version 2 ' ||
> 'transition rules');
[TEST:oradev] /oradev1/app/oracle/product/10.2.0/rdbms/admin> cd
[TEST:oradev] /oradev1/home>
[TEST:oradev] /oradev1/home>
[TEST:oradev] /oradev1/home> sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.2.0 - Production on Tue Mar 13 11:20:20 2007

Connected to an idle instance.

SQL> STARTUP MIGRATE
ORACLE instance started.

Total System Global Area 3758096384 bytes
Fixed Size 2075728 bytes
Variable Size 721421232 bytes
Database Buffers 3019898880 bytes
Redo Buffers 14700544 bytes
Database mounted.
Database opened.
SQL> @?/rdbms/admin/utltzuv2.sql

Table dropped.

Table created.

Table dropped.

Table created.

Query sys.sys_tzuv2_temptab table to see if any TIMEZONE data is affected by
version 4 transition rules

PL/SQL procedure successfully completed.

Commit complete.

PL/SQL procedure successfully completed.

Commit complete.

SQL> startup force
ORACLE instance started.

Total System Global Area 3758096384 bytes
Fixed Size 2075728 bytes
Variable Size 721421232 bytes
Database Buffers 3019898880 bytes
Redo Buffers 14700544 bytes
Database mounted.
Database opened.
SQL> STARTUP MIGRATE
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP MIGRATE
ORACLE instance started.

Total System Global Area 3758096384 bytes
Fixed Size 2075728 bytes
Variable Size 721421232 bytes
Database Buffers 3019898880 bytes
Redo Buffers 14700544 bytes
Database mounted.
Database opened.
SQL> @?/rdbms/admin/utltzuv2.sql

Table dropped.

Table created.

Table dropped.

Table created.

Query sys.sys_tzuv2_temptab table to see if any TIMEZONE data is affected by
version 4 transition rules

PL/SQL procedure successfully completed.

Commit complete.

PL/SQL procedure successfully completed.

Commit complete.

SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64
bit Production
With the Partitioning, OLAP and Data Mining options
[TEST:oradev] /oradev1/home> ls
bak_timefile rda_4.6-061207.tar rman scripts
rda restore_timefile rman.tar timezone_patch
[TEST:oradev] /oradev1/home> cd timezone_patch
[TEST:oradev] /oradev1/home/timezone_patch> ls
5632264 p5632264_10202_AIX64-5L.zip
5746835 p5746835_10202_GENERIC.zip
5865568 p5865568_10202_GENERIC.zip
[TEST:oradev] /oradev1/home/timezone_patch> cd 5632264
[TEST:oradev] /oradev1/home/timezone_patch/5632264> ls
README.txt etc files
[TEST:oradev] /oradev1/home/timezone_patch/5632264> opatch apply
Invoking OPatch 10.2.0.2.0

Oracle Home : /oradev1/app/oracle/product/10.2.0
Central Inventory : /oradev1/app/oracle/oraInventory
from : /oradev1/app/oracle/product/10.2.0/oraInst.loc
OPatch version : 10.2.0.2.0
OUI version : 10.2.0.2.0
OUI location : /oradev1/app/oracle/product/10.2.0/oui
Log file location : /oradev1/app/oracle/product/10.2.0/cfgtoollogs/opatch/opatch
-00_Mar_13_11-29-10-GMT+08_Tue.log

ApplySession applying interim patch '5632264' to OH '/oradev1/app/oracle/product
/10.2.0'

OPatch detected non-cluster Oracle Home from the inventory and will patch the lo
cal system only.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local sy
stem.
(Oracle Home = '/oradev1/app/oracle/product/10.2.0')

Is the local system ready for patching?

Do you want to proceed? [y|n]
y
User Responded with: Y
Backing up files and inventory (not for auto-rollback) for the Oracle Home
Backing up files affected by the patch '5632264' for restore. This might take a
while...
Backing up files affected by the patch '5632264' for rollback. This might take a
while...

Patching component oracle.oracore.rsf, 10.2.0.2.0...
Copying file to "/oradev1/app/oracle/product/10.2.0/oracore/zoneinfo/timezone.da
t"
Copying file to "/oradev1/app/oracle/product/10.2.0/oracore/zoneinfo/timezlrg.da
t"
Copying file to "/oradev1/app/oracle/product/10.2.0/oracore/zoneinfo/readme.txt"
ApplySession adding interim patch '5632264' to inventory

Verifying the update...
Inventory check OK: Patch ID 5632264 is registered in Oracle Home inventory with
proper meta-data.
Files check OK: Files from Patch ID 5632264 are present in Oracle Home.

The local system has been patched and can be restarted.

OPatch succeeded.
[TEST:oradev] /oradev1/home/timezone_patch/5632264> opatch lsinventory
Invoking OPatch 10.2.0.2.0

Oracle Home : /oradev1/app/oracle/product/10.2.0
Central Inventory : /oradev1/app/oracle/oraInventory
from : /oradev1/app/oracle/product/10.2.0/oraInst.loc
OPatch version : 10.2.0.2.0
OUI version : 10.2.0.2.0
OUI location : /oradev1/app/oracle/product/10.2.0/oui
Log file location : /oradev1/app/oracle/product/10.2.0/cfgtoollogs/opatch/opatch
-00_Mar_13_11-29-38-GMT+08_Tue.log

Lsinventory Output file location : /oradev1/app/oracle/product/10.2.0/cfgtoollog
s/opatch/lsinv/lsinventory-00_Mar_13_11-29-38-GMT+08_Tue.txt

--------------------------------------------------------------------------------
Installed Top-level Products (2):

Oracle Database 10g 10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 1 10.2.0.2.0
There are 2 products installed in this Oracle Home.

Interim patches (3) :

Patch 5632264 : applied on Tue Mar 13 11:29:21 GMT+08:00 2007
Created on 29 Jan 2007, 03:49:25 hrs PST8PDT
Bugs fixed:
5632264

Patch 5746835 : applied on Tue Mar 13 11:19:26 GMT+08:00 2007
Created on 10 Jan 2007, 22:20:19 hrs PST8PDT
Bugs fixed:
5126270, 5726033, 5746835

Patch 5865568 : applied on Tue Mar 13 10:54:15 GMT+08:00 2007
Created on 15 Feb 2007, 19:51:37 hrs PST8PDT
Bugs fixed:
5865568

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

OPatch succeeded.
[TEST:oradev] /oradev1/home/timezone_patch/5632264> sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.2.0 - Production on Tue Mar 13 11:30:04 2007

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> startup force
ORACLE instance started.

Total System Global Area 3758096384 bytes
Fixed Size 2075728 bytes
Variable Size 721421232 bytes
Database Buffers 3019898880 bytes
Redo Buffers 14700544 bytes
Database mounted.
Database opened.
SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64
bit Production
With the Partitioning, OLAP and Data Mining options
[TEST:oradev] /oradev1/home/timezone_patch/5632264> lsnrctl start

LSNRCTL for IBM/AIX RISC System/6000: Version 10.2.0.2.0 - Production on 13-MAR-
2007 11:31:58

Starting /oradev1/app/oracle/product/10.2.0/bin/tnslsnr: please wait...

TNSLSNR for IBM/AIX RISC System/6000: Version 10.2.0.2.0 - Production
System parameter file is /oradev1/app/oracle/product/10.2.0/network/admin/listen
er.ora
Log messages written to /oradev1/app/oracle/product/10.2.0/network/log/listener.
log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=TESTSERVER)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=TESTSERVER)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for IBM/AIX RISC System/6000: Version 10.2.0.2
.0 - Production
Start Date 13-MAR-2007 11:31:59
Uptime 0 days 0 hr. 0 min. 1 sec
Trace Level off
Security ON: Local OS Authentication
SNMP ON
Listener Parameter File /oradev1/app/oracle/product/10.2.0/network/admin/liste
ner.ora
Listener Log File /oradev1/app/oracle/product/10.2.0/network/log/listene
r.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=TESTSERVER)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[TEST:oradev] /oradev1/home/timezone_patch/5632264> cd $ORACLE_HOME
[TEST:oradev] /oradev1/app/oracle/product/10.2.0> ls
OPatch endorsed mgw relnotes
TESTSERVER_TEST has network root.sh
assistants hs nls root.sh.old
bali install oc4j root.sh.old.1
bin install.platform olap slax
cdata inventory opmn sqlj
cfgtoollogs javavm opsm sqlnet.log
clone jdbc oraInst.loc sqlplus
config jdk oracore srvm
crs jlib ord sysman
css jre oui tg4sybs
ctx ldap owm uix
dbs lib perl wwg
demo lib32 plsql xdk
diagnostics log precomp
emdw md racg
encryption mesg rdbms
[TEST:oradev] /oradev1/app/oracle/product/10.2.0> cd rdbms
[TEST:oradev] /oradev1/app/oracle/product/10.2.0/rdbms> ls
admin doc label.info log public
audit install lib mesg xml
demo jlib lib32 notes
[TEST:oradev] /oradev1/app/oracle/product/10.2.0/rdbms> cd admin

[TEST:oradev] /oradev1/app/oracle/product/10.2.0/rdbms/admin> timezdif.csv <
timezdif.csv not found
[TEST:oradev] /oradev1/app/oracle/product/10.2.0/rdbms/admin> cd ..
[TEST:oradev] /oradev1/app/oracle/product/10.2.0/rdbms> cd ..
[TEST:oradev] /oradev1/app/oracle/product/10.2.0> ls
OPatch endorsed mgw relnotes
TESTSERVER_TEST has network root.sh
assistants hs nls root.sh.old
bali install oc4j root.sh.old.1
bin install.platform olap slax
cdata inventory opmn sqlj
cfgtoollogs javavm opsm sqlnet.log
clone jdbc oraInst.loc sqlplus
config jdk oracore srvm
crs jlib ord sysman
css jre oui tg4sybs
ctx ldap owm uix
dbs lib perl wwg
demo lib32 plsql xdk
diagnostics log precomp
emdw md racg
encryption mesg rdbms
[TEST:oradev] /oradev1/app/oracle/product/10.2.0> cd ..
[TEST:oradev] /oradev1/app/oracle/product> ls
10.2.0
[TEST:oradev] /oradev1/app/oracle/product> cd ..
[TEST:oradev] /oradev1/app/oracle> find ./ -name "timezdif.csv"
./product/10.2.0/oracore/zoneinfo/timezdif.csv
./product/10.2.0/.patch_storage/5746835_Jan_10_2007_22_20_19/original_patch/file
s/oracore/zoneinfo/timezdif.csv
[TEST:oradev] /oradev1/app/oracle> 0/oracore/zoneinfo/timezdif.csv <
-rw-rw-rw- 1 oradev dba 6475 Jan 11 14:20 ./product/10.2.0/oracor
e/zoneinfo/timezdif.csv
[TEST:oradev] /oradev1/app/oracle> cd ORACLE_HOME/oracore/zoneinfo
ksh: ORACLE_HOME/oracore/zoneinfo: not found
[TEST:oradev] /oradev1/app/oracle> cd $ORACLE_HOME/oracore/zoneinfo
[TEST:oradev] /oradev1/app/oracle/product/10.2.0/oracore/zoneinfo> ls
readme.txt timezlrg.dat tzuvext_323650.bad tzuvext_921718.bad
timezdif.csv timezone.dat tzuvext_323650.log tzuvext_921718.log
[TEST:oradev] /oradev1/app/oracle/product/10.2.0/oracore/zoneinfo> ls -rtl
total 1176
-rw-rw-rw- 1 oradev dba 6475 Jan 11 14:20 timezdif.csv
-rw-r--r-- 1 oradev dba 160733 Jan 29 19:49 timezone.dat
-rw-r--r-- 1 oradev dba 408315 Jan 29 19:49 timezlrg.dat
-r--r--r-- 1 oradev dba 1406 Jan 29 19:49 readme.txt
-rw-r--r-- 1 oradev dba 2280 Mar 13 11:21 tzuvext_323650.log
-rw-r--r-- 1 oradev dba 150 Mar 13 11:21 tzuvext_323650.bad
-rw-r--r-- 1 oradev dba 2280 Mar 13 11:28 tzuvext_921718.log
-rw-r--r-- 1 oradev dba 150 Mar 13 11:28 tzuvext_921718.bad
[TEST:oradev] /oradev1/app/oracle/product/10.2.0/oracore/zoneinfo> 718.log <

LOG file opened at 03/13/07 11:28:02

Field Definitions for table SYS_TZUV2_AFFECTED_REGIONS
Record format DELIMITED BY NEWLINE
Data in file has same endianness as the platform
Rows with all null fields are accepted

VERSION CHAR (255)
Terminated by ","
Trim whitespace from left and right
TIME_ZONE_NAME CHAR (255)
Terminated by ","
Trim whitespace from left and right
FROM_YEAR CHAR (255)
Terminated by ","
Trim whitespace from left and right
TO_YEAR CHAR (255)
Terminated by ","
Trim whitespace from left and right
error processing column VERSION in row 1 for datafile /oradev1/app/oracle/produc
t/10.2.0/oracore/zoneinfo/timezdif.csv
ORA-01722: invalid number
error processing column VERSION in row 2 for datafile /oradev1/app/oracle/produc
t/10.2.0/oracore/zoneinfo/timezdif.csv
ORA-01722: invalid number
error processing column VERSION in row 3 for datafile /oradev1/app/oracle/produc
t/10.2.0/oracore/zoneinfo/timezdif.csv
ORA-01722: invalid number

LOG file opened at 03/13/07 11:28:02

Field Definitions for table SYS_TZUV2_AFFECTED_REGIONS
Record format DELIMITED BY NEWLINE
Data in file has same endianness as the platform
Rows with all null fields are accepted

VERSION CHAR (255)
Terminated by ","
Trim whitespace from left and right
TIME_ZONE_NAME CHAR (255)
Terminated by ","
Trim whitespace from left and right
FROM_YEAR CHAR (255)
Terminated by ","
Trim whitespace from left and right
TO_YEAR CHAR (255)
Terminated by ","
Trim whitespace from left and right
error processing column VERSION in row 1 for datafile /oradev1/app/oracle/produc
t/10.2.0/oracore/zoneinfo/timezdif.csv
ORA-01722: invalid number
error processing column VERSION in row 2 for datafile /oradev1/app/oracle/produc
t/10.2.0/oracore/zoneinfo/timezdif.csv
ORA-01722: invalid number
error processing column VERSION in row 3 for datafile /oradev1/app/oracle/produc
t/10.2.0/oracore/zoneinfo/timezdif.csv
ORA-01722: invalid number
error processing column VERSION in row 3 for datafile /oradev1/app/oracle/produc
t/10.2.0/oracore/zoneinfo/timezdif.csv
ORA-01722: invalid number

error processing column VERSION in row 2 for datafile /oradev1/app/oracle/produc
error processing column VERSION in row 1 for datafile /oradev1/app/oracle/produc
error processing column VERSION in row 3 for datafile /oradev1/app/oracle/produc
error processing column VERSION in row 2 for datafile /oradev1/app/oracle/produc
error processing column VERSION in row 1 for datafile /oradev1/app/oracle/produc
:q!

以上錯誤資訊可以忽略。。
[TEST:oradev] /oradev1/app/oracle/product/10.2.0/oracore/zoneinfo> ls
readme.txt timezlrg.dat tzuvext_323650.bad tzuvext_921718.bad
timezdif.csv timezone.dat tzuvext_323650.log tzuvext_921718.log
[TEST:oradev] /oradev1/app/oracle/product/10.2.0/oracore/zoneinfo> ls -rtl
total 1176
-rw-rw-rw- 1 oradev dba 6475 Jan 11 14:20 timezdif.csv
-rw-r--r-- 1 oradev dba 160733 Jan 29 19:49 timezone.dat
-rw-r--r-- 1 oradev dba 408315 Jan 29 19:49 timezlrg.dat
-r--r--r-- 1 oradev dba 1406 Jan 29 19:49 readme.txt
-rw-r--r-- 1 oradev dba 2280 Mar 13 11:21 tzuvext_323650.log
-rw-r--r-- 1 oradev dba 150 Mar 13 11:21 tzuvext_323650.bad
-rw-r--r-- 1 oradev dba 2280 Mar 13 11:28 tzuvext_921718.log
-rw-r--r-- 1 oradev dba 150 Mar 13 11:28 tzuvext_921718.bad
[TEST:oradev] /oradev1/app/oracle/product/10.2.0/oracore/zoneinfo> 18.bad <
# File version 1.0
# Fields: VERSION#, TIMEZONE_NAME, FROM_YEAR, TO_YEAR
# Note: A NULL/blank for TO_YEAR means max year representable/allowed by db.
~
~
:q!
[TEST:oradev] /oradev1/app/oracle/product/10.2.0/oracore/zoneinfo> ls -rtl
total 1176
-rw-rw-rw- 1 oradev dba 6475 Jan 11 14:20 timezdif.csv
-rw-r--r-- 1 oradev dba 160733 Jan 29 19:49 timezone.dat
-rw-r--r-- 1 oradev dba 408315 Jan 29 19:49 timezlrg.dat
-r--r--r-- 1 oradev dba 1406 Jan 29 19:49 readme.txt
-rw-r--r-- 1 oradev dba 2280 Mar 13 11:21 tzuvext_323650.log
-rw-r--r-- 1 oradev dba 150 Mar 13 11:21 tzuvext_323650.bad
-rw-r--r-- 1 oradev dba 2280 Mar 13 11:28 tzuvext_921718.log
-rw-r--r-- 1 oradev dba 150 Mar 13 11:28 tzuvext_921718.bad

Oracle 10gR2 DST Patch 安裝全程LOG

相關文章