MUST_BE_SAME_TIMEZONE_FILE_VERSION

When you upgrade database to 11g r2

when running catupgrd.sql

SQL> SELECT TO_NUMBER('MUST_BE_SAME_TIMEZONE_FILE_VERSION')
  2     FROM sys.props$
  3     WHERE
  4       (
  5        ((SELECT TO_NUMBER(value$) from sys.props$
  6           WHERE name = 'DST_PRIMARY_TT_VERSION') !=
  7         (SELECT tz_version from registry$database))
  8        AND
  9        (((SELECT substr(version,1,4) FROM registry$ where cid = 'CATPROC') =
10           '9.2.') OR
11         ((SELECT substr(version,1,4) FROM registry$ where cid = 'CATPROC') =
12           '10.1') OR
13         ((SELECT substr(version,1,4) FROM registry$ where cid = 'CATPROC') =
14           '10.2') OR
15         ((SELECT substr(version,1,4) FROM registry$ where cid = 'CATPROC') =
16           '11.1'))
17       );
SELECT TO_NUMBER('MUST_BE_SAME_TIMEZONE_FILE_VERSION')
                 *
ERROR at line 1:
ORA-01722: invalid number


control following steps


SQL> SELECT * FROM v$timezone_file;

FILENAME                VERSION
-------------------- ----------
timezlrg_14.dat              14


SQL> select TZ_VERSION from registry$database;

TZ_VERSION
----------
        4


If version of timezone_file is different than TZ_VERSION
run following update.

SQL> update registry$database set TZ_VERSION = (select version FROM v$timezone_file);
SQL> commit;


try to run catupgrd.sql

ORA-14265: data type or length of a table subpartitioning column may not be changed

When you want to modify partition table' s key columns.

sample

1 - create partitioned table with subpartition.


SQL> CREATE TABLE vty_log
    (tarih        DATE,
    program       VARCHAR2(6),
    pcismi        VARCHAR2(12),
    mesaj         VARCHAR2(2000),
    boyut         NUMBER(10,0))
  PARTITION BY RANGE (TARIH)
  SUBPARTITION BY HASH (program)
  (
  PARTITION vty_log_201201 VALUES LESS THAN (TO_DATE(' 2012-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  LOGGING
  (
  SUBPARTITION vty_log_201201_P1,
  SUBPARTITION vty_log_201201_P2,
  SUBPARTITION vty_log_201201_P3,
  SUBPARTITION vty_log_201201_P4
  )
  );

2- try to modify subpartition column

SQL> alter table vty_log modify program varchar2(10);

*

ERROR at line 1:

ORA-14265: data type or length of a table subpartitioning column may not be changed 

you have to drop/create table to achive this problem.

You can create non-partition table for each partition which has same name with partition

and exchange partition 

or

If have enoguh downtime you can export/import with orjinal table. 

or

you can create dummy table desired structure and you can insert/select/rename.

Oracle 11g Cross platform Active Standby

You can use different supported platform for standby database with using 11g active standby features.

please click here

Oracle JDBC Connection Test

Save source  file: Conn.java

import java.sql.*;
class Conn {
  public static void main (String[] args) throws Exception
  {
   Class.forName ("oracle.jdbc.OracleDriver");

   Connection conn = DriverManager.getConnection
     ("jdbc:oracle:thin:@localhost:1521:ORCL","scott","tiger");
   try {
     Statement stmt = conn.createStatement();
     try {
       ResultSet rset = stmt.executeQuery("select BANNER from SYS.V_$VERSION");
       try {
         while (rset.next())
           System.out.println (rset.getString(1));   // Print col 1
       } 
       finally {
          try { rset.close(); } catch (Exception ignore) {}
       }
     } 
     finally {
       try { stmt.close(); } catch (Exception ignore) {}
     }
   } 
   finally {
     try { conn.close(); } catch (Exception ignore) {}
   }
  }
}

ps: Change host, port, SID, user and password to your value in connection url

("jdbc:oracle:thin:@localhost:1521:ORCL","scott","tiger");

Compile java

javac -cp .:$ORACLE_HOME/jdbc/lib/ojdbc5.jar:$ORACLE_HOME/jlib/orai18n.jar Conn.java

Run program

java -cp .:$ORACLE_HOME/jdbc/lib/ojdbc5.jar:$ORACLE_HOME/jlib/orai18n.jar Conn



PS : for compile and run in windows os, change columns(:) to semicolons(;)  like
.;$ORACLE_HOME/jdbc/lib/ojdbc5.jar;$ORACLE_HOME/jlib/orai18n.jar

also change jar names in their directories the correct ones.

simple java tricks

http://www.oracle.com/technetwork/java/compile-136656.html

Oracle SNAPSHOT STANDBY Database

For test purpose or others you can open read/write mode your standby database.



--to make read/write

alter system set db_recovery_file_dest_size=100G;
alter system set db_flashback_retention_target=7200; ---5 days
alter system set db_recovery_file_dest='/u01/flashback';

shutdown immediate

startup mount

ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

shutdown
startup mount
alter database open;

select open_mode,database_role from v$database;



--to return physical standby

shutdown immediate
startup mount
ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

shutdown
startup mount
select open_mode,database_role from v$database;

--start recovery.
alter database recover managed standby database disconnect from session;



http://docs.oracle.com/cd/B28359_01/server.111/b28294/manage_ps.htm#SBYDB00708

http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams056.htm#REFRN10233