Applying “version 4″ Time Zone Files on an Oracle Database

Applying “version 4″ Time Zone Files on an Oracle Database

Applying “version 4″ Time Zone Files on an Oracle Database if your timezone file version is less than 4
Yesterday I was upgrading the database form 10.2.0.3 to 10.2.0.5 on RHEL 5,
In readme of the 10.2.0.5 patch i came across the timezoe files,
We need to upgarde the timezone files of the base database to 4, is minimum requirement.
I found my timezone files 3,

you can use following query to find the timezone of the database:

SQL> select * from v$timezone_file;
FILENAME        VERSION
------------ ----------
timezlrg.dat          3

 

If  your database versions are 9.2.0.8 & 10.2.0.4,your  timezone file version will be 4 by default,

upgrading to 11.1.0.6.0  or 11.2.0.1.0 we need timezone version 4,

If your database is 9.2.0.7, we need to upgarde it to 9.2.0.8

 or database versions are 10.2.0.1, 10.2.0.2, 10.2.0.3, then we need to upgarde the database to 10.2.0.4 then to upgrade the 11.2.0.1

Another & Best way is to apply the  “version 4″ Time Zone Files on an Oracle Database  

you can file the patches for your version from the metalink ID ID 413671.1

Go to end of the article,  Identify your version from the table, apply the patch,
your timezonefile version will be updated to 4, & you can proceed for the upgradation to 11g R1, 11gR2
Please refer the following link for applying OPATCH
http://deveshdba.wordpress.com/2011/09/14/applying-opatch/
Check your opatch utility working fine,

[oracle@primary OPatch]$ cd $ORACLE_HOME/OPatch
[oracle@primary OPatch]$ ./opatch version
Invoking OPatch 10.2.0.3.0
OPatch Version: 10.2.0.3.0
OPatch succeeded.
[oracle@primary OPatch]$ ./opatch lsinventory
Invoking OPatch 10.2.0.3.0
Oracle interim Patch Installer version 10.2.0.3.0
Copyright (c) 2005, Oracle Corporation.  All rights reserved..

Oracle Home       : /oracle/10201
Central Inventory : /oracle/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 10.2.0.3.0
OUI version       : 10.2.0.3.0
OUI location      : /oracle/10201/oui
Log file location : /oracle/10201/cfgtoollogs/opatch/opatch2011-07-07_12-08-57PM.log
Lsinventory Output file location : /oracle/10201/cfgtoollogs/opatch/lsinv/lsinventory2011-07-07_12-08-57PM.txt
--------------------------------------------------------------------------------
Installed Top-level Products (2):
Oracle Database 10g                                                  10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 2                            10.2.0.3.0
There are 2 products installed in this Oracle Home.

Interim patches (2) :
Patch  5556081      : applied on Thu Jul 07 11:20:32 GMT+05:30 2011
   Created on 9 Nov 2006, 22:20:50 hrs PST8PDT
   Bugs fixed:
     5556081
Patch  5557962      : applied on Thu Jul 07 11:20:24 GMT+05:30 2011
   Created on 9 Nov 2006, 23:23:06 hrs PST8PDT
   Bugs fixed:
     4269423, 5557962, 5528974

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

donwload the patch, unzip it, Follow the instructions in readme of the patch, & apply.

 
[oracle@primary OPatch]$ ./opatch apply
Invoking OPatch 10.2.0.3.0
Oracle interim Patch Installer version 10.2.0.3.0
Copyright (c) 2005, Oracle Corporation.  All rights reserved..

Oracle Home       : /oracle/10201
Central Inventory : /oracle/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 10.2.0.3.0
OUI version       : 10.2.0.3.0
OUI location      : /oracle/10201/oui
Log file location : /oracle/10201/cfgtoollogs/opatch/opatch2011-07-07_12-13-56PM.log
ApplySession applying interim patch '5632264' to OH '/oracle/10201'
OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/oracle/10201')
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.3.0...
Copying file to "/oracle/10201/oracore/zoneinfo/timezone.dat"
Copying file to "/oracle/10201/oracore/zoneinfo/timezlrg.dat"
Copying file to "/oracle/10201/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.

restart the database & check the timezone file version,

 

SQL> startup
ORACLE instance started.
Total System Global Area  943718400 bytes
Fixed Size                  1265172 bytes
Variable Size             591397356 bytes
Database Buffers          348127232 bytes
Redo Buffers                2928640 bytes
Database mounted.
Database opened.
SQL> select * from v$timezone_file;
FILENAME        VERSION
------------ ----------
timezlrg.dat          4

IMPDP TABLE_EXISTS_ACTION = APPEND, REPLACE, [SKIP] and TRUNCATE

 

IMPDP  TABLE_EXISTS_ACTION PARAMETER EXPLAINED

 Data Pump IMPDP TABLE_EXISTS_ACTION = APPEND, REPLACE, [SKIP] and TRUNCATE
In conventional import utility (IMP) we have ignore =y option which will ignore the error when the object is already exist with the same name.

When it comes to the data pump there is one enhanced option of ignore=y which is called TABLE_EXISTS_ACTION. The values for this parameter give 4 different options to handle the existing table and data.

$ impdp help=y

    TABLE_EXISTS_ACTION
    Action to take if imported object already exists.
    Valid keywords are: APPEND, REPLACE, [SKIP] and TRUNCATE.

    SKIP: Default value for this parameter is SKIP. This parameter is exactly same as the IGNORE=Y option in conventional import utility.

    APPEND: This option appends the data from the data dump. The extra rows in the dump will be appended to the table and the existing data remains unchanged.

    TRUNCATE: This option truncate the exiting rows in the table and insert the rows from the dump

    REPLACE: This option drop the current table and create the table as it is in the dump file. Both SKIP and REPLACE options are not valid if you set the  CONTENT=DATA_ONLY for the impdp.

   
Method to Import only rows does not exist in the target table
See some examples here.

In this example lets use abc table in my schema (ATOORPU)

SQL> select * from abc;

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
eds                                    44 19-DEC-12
rew                                    33 19-DEC-12
ARV                                    70 20-DEC-12
ARVIND                                 69 20-DEC-12
ATOORPU                                64 19-DEC-12
BI                                     63 19-DEC-12

I took the data pump dump export EXPDP of employee table.

oracle@orcl: $ expdp atoorpu directory=DPUMP dumpfile=abc.dmp logfile=abc.log tables=abc

Export: Release 11.2.0.1.0 - Production on Fri Oct 24 09:25:02 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "ATOORPU"."SYS_EXPORT_TABLE_01":  atoorpu/******** directory=DPUMP dumpfile=abc.dmp logfile=abc.log tables=abc
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "ATOORPU"."ABC"                             5.921 KB       6 rows
Master table "ATOORPU"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ATOORPU.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/dpump/abc.dmp
Job "ATOORPU"."SYS_EXPORT_TABLE_01" successfully completed at 09:25:36


oracle@qpdbdev201:[/u01/app/oracle/dpump] $ sqlplus atoorpu

SQL*Plus: Release 11.2.0.1.0 Production on Fri Oct 24 09:25:57 2014

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


   ------ TABLE_EXISTS_ACTION=SKIP  ------

In this example I want to use table_exists_action=skip, where I want to skip the table data in my import, if a similar table exists.

oracle@orcl: $ impdp atoorpu directory=DPUMP dumpfile=abc.dmp logfile=abc_imp_skip.log tables=abc table_exists_action=skip

Import: Release 11.2.0.1.0 - Production on Fri Oct 24 09:32:32 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "ATOORPU"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "ATOORPU"."SYS_IMPORT_TABLE_01":  atoorpu/******** directory=DPUMP dumpfile=abc.dmp logfile=abc_imp_skip.log tables=abc table_exists_action=skip
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39151: Table "ATOORPU"."ABC" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "ATOORPU"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 09:32:42


    ------     TABLE_EXISTS_ACTION=APPEND    ------

I have deleted and inserted 4 new rows into employee table. So as of now the rows the dump and table are different and I am going to import the dump with APPEND option.

SQL> delete from employee;

4 rows deleted.

SQL> insert into abc (select * from abc_bak);

4 rows created.

SQL> commit;
SQL> select * from abc;

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
ARVd                                   70 20-DEC-12
ARVIND2                                69 20-DEC-12
ATOORPUe                               64 19-DEC-12
BIf                                    63 19-DEC-12

$  impdp atoorpu directory=DPUMP dumpfile=abc.dmp logfile=abc_imp_append.log tables=abc table_exists_action=append

Import: Release 11.2.0.1.0 - Production on Fri Oct 24 09:37:34 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "ATOORPU"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "ATOORPU"."SYS_IMPORT_TABLE_01":  atoorpu/******** directory=DPUMP dumpfile=abc.dmp logfile=abc_imp_append.log tables=abc table_exists_action=append
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39152: Table "ATOORPU"."ABC" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "ATOORPU"."ABC"                             5.921 KB       6 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "ATOORPU"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 09:37:42

Now 4 more rows appended to the table. Lets verify that

SQL> select * from abc;

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
ARVd                                   70 20-DEC-12
ARVIND2                                69 20-DEC-12
ATOORPUe                               64 19-DEC-12
BIf                                    63 19-DEC-12
eds                                    44 19-DEC-12
rew                                    33 19-DEC-12
ARV                                    70 20-DEC-12
ARVIND                                 69 20-DEC-12
ATOORPU                                64 19-DEC-12
BI                                     63 19-DEC-12

8 rows selected.

        ------ TABLE_EXISTS_ACTION=TRUNCATE     ------ 

    Now let’s try with table_exists_action=truncate option. In truncate option it will truncate the content of the existing table and insert the rows from the dump. Currently my abc table has 8 rows which we inserted last insert.

$   impdp atoorpu directory=DPUMP dumpfile=abc.dmp logfile=abc_imp_trunc.log tables=abc table_exists_action=truncate

Import: Release 11.2.0.1.0 - Production on Fri Oct 24 09:39:39 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "ATOORPU"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "ATOORPU"."SYS_IMPORT_TABLE_01":  atoorpu/******** directory=DPUMP dumpfile=abc.dmp logfile=abc_imp_trunc.log tables=abc table_exists_action=truncate
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39153: Table "ATOORPU"."ABC" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "ATOORPU"."ABC"                             5.921 KB       6 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "ATOORPU"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 09:39:46


SQL >  select * from abc


USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
eds                                    44 19-DEC-12
rew                                    33 19-DEC-12
ARV                                    70 20-DEC-12
ARVIND                                 69 20-DEC-12
ATOORPU                                64 19-DEC-12
BI                                     63 19-DEC-12


        ------ TABLE_EXISTS_ACTION=REPLACE    ------


This option drop the current table in the database and the import recreate the new table as in the dumpfile.

$ impdp atoorpu directory=DPUMP dumpfile=abc.dmp logfile=abc_imp_replace.log tables=abc table_exists_action=replace

Import: Release 11.2.0.1.0 - Production on Fri Oct 24 09:41:59 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "ATOORPU"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "ATOORPU"."SYS_IMPORT_TABLE_01":  atoorpu/******** directory=DPUMP dumpfile=abc.dmp logfile=abc_imp_replace.log tables=abc table_exists_action=replace
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "ATOORPU"."ABC"                             5.921 KB       6 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "ATOORPU"."SYS_IMPORT_TABLE_01" successfully completed at 09:42:07


Now if you check the last_ddl_time for the table it would be the same as the import time.


SQL> select OBJECT_NAME, to_char(LAST_DDL_TIME,'dd-mm-yyyy hh:mi:ss') created from dba_objects where OBJECT_NAME='ABC' and owner='ATOORPU';


OBJECT_NAME                    CREATED          
--------------------------             -------------------
ABC                                        24-10-2014 09:42:06

Svn client error svn: E160006: Invalid revision number '-1'

Topic : svn: E160006: Invalid revision number '-1' 

I have recently come across this error when I installed the svn client on my machine. I have the latest version of tortoise svn client on my local machine. After install I used to get this weird error when ever I used to do check out.

 


Unexpected HTTP status 500 'Server Error' on
 '/scm/svn/database/releases/!svn/vcc/default'
Additional errors:
svn: E160006: Invalid revision number '-1'

 I goggled a lot but nothing seems to work. Finally I figured out. The client and server versions are not same then I downloaded the same version of the tortoise client as of server. Then it seemed to work fine.


Conclusion :

Try to download the tortoise svn of same version as of server and it seems to work fine without any problem.