CountDownLatch

    public static long time(Executor executor, int concurrency,  final Runnable action) throws InterruptedException {

        final CountDownLatch ready = new CountDownLatch(concurrency);
        final CountDownLatch start = new CountDownLatch(1);
        final CountDownLatch done = new CountDownLatch(concurrency);

        for (int i = 0; i < concurrency; i++) {

            final int j = i;
            executor.execute(new Runnable() {
                public void run() {
                    System.out.println(" ready " + j + " : countDown " + System.nanoTime());
                    ready.countDown(); // Tell timer we're ready
                    try {
                  //      Thread.sleep(1000);
                        System.out.println(" start " + j + " : await " + System.nanoTime());
                        start.await(); // Wait till peers are ready
                        System.out.println(" action " + j + " : run " +  System.nanoTime());
                        action.run();
                    } catch (InterruptedException e) {
                        Thread.currentThread().interrupt();
                    } finally {
                        System.out.println(" Down " + j + " : countDown " +  System.nanoTime());
                        done.countDown();  // Tell timer we're done
                    }
                }
            });
        }

        System.out.println("Ready " +  System.nanoTime());
        ready.await();     // Wait for all workers to be ready
        long startNanos = System.nanoTime();

        System.out.println("Start " +  System.nanoTime());
        start.countDown(); // And they're off!

        System.out.println("Done await " +  System.nanoTime());
        done.await();      // Wait for all workers to finish
        return System.nanoTime() - startNanos;
    }

    public static void main(String[] args) throws InterruptedException {
        time (Executors.newFixedThreadPool(3),3, new Runnable() {

            public void run() {
                System.out.println(" my name: " + Thread.currentThread().getName() + " " + System.nanoTime());
            }
        });
    }

Every thread calls ready.countDown() then wait at start.await().
The main thread will run start.countDown() after ready.await unblocked after all thread called ready.countDown().

If you uncomment the “Thread.sleep()”, the main thread will fire off start.countDown() without care about the status of thread.

The output of System.out.println() in multi-threads does not necessarily appear (in eclipse console) as the same order as they are executed. Append System.nanoTime() in the printed message can confirm that.

Hongfeng Sun : 08 Mar 2012 16:58 |  Tags: Java |  Comments (0) |  Close comments |  Report a problem

Instantiate table in Oracle Streams

Writing about web page http://krish-dba.blogspot.com/2009/01/re-synchronizingrefresh-table-in.html

Credit to this article

1. Stop Capture
EXEC DBMS_CAPTURE_ADM.STOP_CAPTURE(capture_name => 'CAP_HSUN');

2. Stop propagation
EXEC DBMS_PROPAGATION_ADM.STOP_PROPAGATION('PROP_HSUN',FORCE=>TRUE);

3. Stop Apply Process on Target(T)

EXEC DBMS_APPLY_ADM.STOP_APPLY(apply_name => 'AAPL_HSUN');

4. Delete the apply errors on Target

Check the errors in the error queue:

SQL> select apply_name, LOCAL_TRANSACTION_ID, ERROR_MESSAGE from dba_apply_error;

To delete specific transaction:

SQL> EXEC DBMS_APPLY_ADM.DELETE_ERROR('transaction_id');

To delete all errors: 

SQL> EXEC DBMS_APPLY_ADM.DELETE_ALL_ERRORS('AAPL_HSUN');

5. Truncate table 

ALTER TABLE  UWTABS.UW_TPRS ADD CONSTRAINT "C_TPRS_1" PRIMARY KEY ("TPRS_CODE");
Truncate table UWTABS.UW_TPRS

6. Re-instantiate the problem table on Source(S)

EXEC DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION('UWTABS.UW_TPRS');

DECLARE iscn NUMBER; 
BEGIN iscn:=DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER(); 
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@target.warwick.ac.uk(
 source_object_name => 'UWTABS.UW_TPRS', 
 source_database_name => 'source.warwick.ac.uk', 
 instantiation_scn => iscn);
END;
/

7.
Synchronize the problem table through datapump exp/imp

8. Start the Apply Process on Target(T)

EXEC DBMS_APPLY_ADM.START_APPLY(apply_name => 'AAPL_HSUN');

9. Start the propagation Process on the Source(S)

EXEC DBMS_PROPAGATION_ADM.START_PROPAGATION('PROP_HSUN');

10. Start the Capture Process on the Source(S)

EXEC DBMS_CAPTURE_ADM.START_CAPTURE(capture_name => 'CAP_HSUN');

Hongfeng Sun : 23 Feb 2012 10:30 |  Tags: Oracle |  Comments (0) |  Close comments |  Report a problem

job name in datapump

Pay attention to the format of job name

oracle@db$ expdp / attach=nightly_pump_210212-1059

ed to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning and Real Application Testing options
ORA-31626: job does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.KUPV$FT", line 405
ORA-31638: cannot attach to job nightly_pump_210212-1059 for user NIGHTLY_PUMP_210212
ORA-31632: master table "NIGHTLY_PUMP_210212.nightly_pump_210212-1059" not found, invalid, or inaccessible
ORA-00942: table or view does not exist

Datapump can find correct user after changing the job name.

oracle@db$ expdp / attach=nightly_pump_210212_1059
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning and Real Application Testing options
ORA-31626: job does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.KUPV$FT", line 405
ORA-31638: cannot attach to job NIGHTLY_PUMP_210212_1059 for user OPS$ORACLE
ORA-31632: master table "OPS$ORACLE.NIGHTLY_PUMP_210212_1059" not found, invalid, or inaccessible
ORA-00942: table or view does not exist

Hongfeng Sun : 21 Feb 2012 12:21 |  Tags: Oracle |  Comments (0) |  Close comments |  Report a problem

"File 1 was not restored from a sufficiently old backup" in RMAN Recover

RMAN> recover database;

starting media recovery

Oracle Error: 
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup 
ORA-01110: data file 1: '/package/oracle/oradata/perseus/system01.dbf'

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/15/2012 11:09:12
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 1 with sequence 41765 and starting SCN of 9738413586917 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 41764 and starting SCN of 9738413585738 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 41763 and starting SCN of 9738413584155 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 41762 and starting SCN of 9738413582950 found to restore
...

RMAN-06025: no backup of archived log for thread 1 with sequence 41734 and starting SCN of 9738413520883 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 41733 and starting SCN of 9738413519245 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 41732 and starting SCN of 9738413518015 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 41731 and starting SCN of 9738413516741 found to restore

RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 02/15/2012 11:28:44
ORA-01152: file 1 was not restored from a sufficiently old backup 
ORA-01110: data file 1: '/package/oracle/oradata/perseus/system01.dbf'

RMAN> list backup of archivelog all;

List of Backup Sets
===================

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
16198481 73.00K     DISK        00:00:00     11-FEB-12      
        BP Key: 16198488   Status: AVAILABLE  Compressed: YES  Tag: SAT
        Piece Name: /package/oracle/orabackup/rman/rman_PERSEUS_arc_20120211_4644_1

  List of Archived Logs in backup set 16198481
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    41584   9738413221153 11-FEB-12 9738413222321 11-FEB-12
...

  1    41724   9738413502482 12-FEB-12 9738413503782 12-FEB-12
  1    41725   9738413503782 12-FEB-12 9738413505258 12-FEB-12
  1    41726   9738413505258 12-FEB-12 9738413509317 12-FEB-12
  1    41727   9738413509317 12-FEB-12 9738413513782 12-FEB-12

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
16205673 11.50K     DISK        00:00:01     12-FEB-12      
        BP Key: 16205679   Status: AVAILABLE  Compressed: YES  Tag: SUN
        Piece Name: /package/oracle/orabackup/rman/rman_PERSEUS_arc_20120212_4653_1

  List of Archived Logs in backup set 16205673
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    41730   9738413516668 12-FEB-12 9738413516741 12-FEB-12

RMAN> recover database until sequence 41730;

Starting recover at 15-FEB-12
using channel ORA_DISK_1
using channel ORA_DISK_2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/15/2012 11:38:42
RMAN-06556: datafile 1 must be restored from backup older than SCN 9738413516668

Need to tell RMAN when to stop.

RMAN>  recover database until sequence 41731;

Starting recover at 15-FEB-12
using channel ORA_DISK_1
using channel ORA_DISK_2

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=41730
channel ORA_DISK_1: reading from backup piece /package/oracle/orabackup/rman/rman_PERSEUS_arc_20120212_4653_1
channel ORA_DISK_1: piece handle=/package/oracle/orabackup/rman/rman_PERSEUS_arc_20120212_4653_1 tag=SUN
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/oradb/archive/perseus/archive1_41730_729171422.dbf thread=1 sequence=41730
media recovery complete, elapsed time: 00:00:01
Finished recover at 15-FEB-12

RMAN> alter database open resetlogs;

database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

How does it happen? What’s cause of the “datafile 1 must be restored from backup” ?

I found an excellent explanation here. According to this article, RMAN wont backup the archivelogs generated after the start of the run of rman backup script.
We switch log every 10 minutes, so it very likely that new archivelog is generated during this period.

Hongfeng Sun : 16 Feb 2012 10:05 |  Comments (0) |  Close comments |  Report a problem

Cancel the flashback of database

If you want to completely undo the effect of the FLASHBACK DATABASE command, then you can perform complete recovery of the database by using the RECOVER DATABASE command without an UNTIL clause or SET UNTIL command: RECOVER DATABASE;

Hongfeng Sun : 09 Feb 2012 16:12 |  Comments (0) |  Close comments |  Report a problem

Switchover to logical standby

This morning we did a switchover between our primary database and one of the two logical standby databases.

The switchover went smoonthly. When the data began to be replicated from primary, however, the standby database suddenly did successive log switch at very high frequency. This is a bit puzzling since the primary database was not too busy.

Loads of waiting events from “SYS” user:

LogMiner builder: DDL                    oracle@si-sbr-db (MS01)  
LogMiner reader: buffer                  oracle@si-sbr-db (MS00) 
Streams apply: waiting for dependency    oracle@si-sbr-db (AS05)                     
Streams apply: waiting for dependency    oracle@si-sbr-db (AS04)   

The frequent updating SQL was:

update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6,cache=:7,highwater=:8,audit$=:9,flags=:10 
where obj#=:1

We started to replicate a big log table a couple of days ago. That table was previous skipped in log apply. The old logical standby coped the change very well. Did this change cause problem on the new standby?
I increased the cache size of hibernate sequence to 10,000 from default value 20.

alter sequence hitlog.hibernate_sequence cache 10000

The log switch immediately stopped. I was surprised how effective the SQL was.

ORA-16282
At developer’s request, I did switchover again. Went smoothly. Then I was asked to switchover again ( for some reason of course), I encountered an error on the current standby database:

SYS@standby SQL>ALTER DATABASE PREPARE TO SWITCHOVER TO PRIMARY; 
ALTER DATABASE PREPARE TO SWITCHOVER TO PRIMARY
*
ERROR at line 1:
ORA-16282: operation not permitted during rolling upgrade

After I faffed around on google, then I recalled I forgot to startup the log apply on the current standby.
Well, the “PREPARE TO SWITCHOVER TO PRIMARY” succeeded after I started the log apply.

Hongfeng Sun : 06 Feb 2012 12:36 |  Tags: Oracle-Availability |  Comments (0) |  Close comments |  Report a problem

Oracle TDE

First we check there is no wallet in the database.

oracle@warwick$ mkdir /app/oracle/admin/hsun/tde_wallet

oracle@warwick$ sqlplus / as sysdba
SYS@hsun SQL>select * from v$encryption_wallet;

WRL_TYPE
--------------------
WRL_PARAMETER
----------------------------------
STATUS
------------------
file
/app/oracle/admin/hsun/tde_wallet/
CLOSED

We now create the wallet using “orapki”. Then create a local auto-login wallet. Note the wallet has no master key in it. The status of wallet is “OPEN_NO_MASTER_KEY”

oracle@warwick$ orapki wallet create -wallet /app/oracle/admin/hsun/tde_wallet

oracle@warwick$ ls -l /app/oracle/admin/hsun/tde_wallet/
total 1
-rw-------   1 oracle   oinstall    3512 Jan 27 12:47 ewallet.p12

oracle@warwick$ orapki wallet create -wallet /app/oracle/admin/hsun/tde_wallet  -auto_login_local
oracle@warwick$ ls -l /app/oracle/admin/hsun/tde_wallet
total 17
-rw-------   1 oracle   oinstall    3589 Jan 27 12:50 cwallet.sso
-rw-------   1 oracle   oinstall    3512 Jan 27 12:47 ewallet.p12

oracle@warwick$ sqlplus / as sysdba

SYS@hsun SQL>CREATE TABLESPACE securespace
DATAFILE '/app/oracle/oradata/hsun/secure01.dbf'
SIZE 150M
ENCRYPTION
DEFAULT STORAGE(ENCRYPT);

ERROR at line 1:
ORA-28374: typed master key not found in wallet

SYS@hsun SQL>select * from v$encryption_wallet;

WRL_TYPE
--------------------
WRL_PARAMETER
----------------------------------
STATUS
------------------
file
/app/oracle/admin/hsun/tde_wallet/
OPEN_NO_MASTER_KEY

SYS@hsun SQL>alter system switch logfile;
SYS@hsun SQL>shutdown immediate;
SYS@hsun SQL>startup

SYS@hsun SQL>select * from v$encryption_wallet;

WRL_TYPE
--------------------
WRL_PARAMETER
----------------------------------
STATUS
------------------
file
/app/oracle/admin/hsun/tde_wallet/
CLOSED

SYS@hsun SQL>alter system set encryption wallet open identified by "password";

SYS@hsun SQL>select * from v$encryption_wallet;

WRL_TYPE
--------------------
WRL_PARAMETER
----------------------------------
STATUS
------------------
file
/app/oracle/admin/hsun/tde_wallet/
OPEN_NO_MASTER_KEY

SYS@hsun SQL>alter system set encryption wallet  close identified by "password";

System altered.

SYS@hsun SQL>select * from v$encryption_wallet;

WRL_TYPE
--------------------
WRL_PARAMETER
----------------------------------
STATUS
------------------
file
/app/oracle/admin/hsun/tde_wallet/
CLOSED

We remove this wallet and create a new wallet using “alter system” . The wallet will be created and open.

oracle@warwick$ rm  /app/oracle/admin/hsun/tde_wallet/ewallet.p12 

oracle@warwick$ sqlplus / as sysdba

SYS@hsun SQL>alter system set encryption wallet open identified by "password";
alter system set encryption wallet open identified by "password" 
*
ERROR at line 1:
ORA-28367: wallet does not exist

SYS@hsun SQL>alter system set encryption key identified by "password";

System altered.

SYS@hsun SQL>select * from v$encryption_wallet;

WRL_TYPE
--------------------
WRL_PARAMETER
----------------------------------
STATUS
------------------
file
/app/oracle/admin/hsun/tde_wallet/
OPEN

SYS@hsun SQL>alter system set encryption wallet  close identified by "password";

System altered.

SYS@hsun SQL>select * from v$encryption_wallet;

WRL_TYPE
--------------------
WRL_PARAMETER
----------------------------------
STATUS
------------------
file
/app/oracle/admin/hsun/tde_wallet/
CLOSED

Create a new local auto-login wallet. It open automatically when db open. You cannot close it, even the “close” wallet command run successfully without warnings.

oracle@warwick$ orapki wallet create -wallet /app/oracle/admin/hsun/tde_wallet  -auto_login_local

oracle@warwick$ sqlplus / as sysdba

SYS@hsun SQL>select * from v$encryption_wallet;

WRL_TYPE
--------------------
WRL_PARAMETER
----------------------------------
STATUS
------------------
file
/app/oracle/admin/hsun/tde_wallet/
OPEN

SYS@hsun SQL>shutdown immediate;
SYS@hsun SQL>startup
SYS@hsun SQL>select * from v$encryption_wallet;

WRL_TYPE
--------------------
WRL_PARAMETER
----------------------------------
STATUS
------------------
file
/app/oracle/admin/hsun/tde_wallet/
OPEN

SYS@hsun SQL>shutdown immediate;
SYS@hsun SQL>startup
SYS@hsun SQL>select * from v$encryption_wallet;

WRL_TYPE
--------------------
WRL_PARAMETER
----------------------------------
STATUS
------------------
file
/app/oracle/admin/hsun/tde_wallet/
OPEN

SYS@hsun SQL>alter system set encryption wallet close;

System altered.

SYS@hsun SQL>select * from v$encryption_wallet;

WRL_TYPE
--------------------
WRL_PARAMETER
----------------------------------
STATUS
------------------
file
/app/oracle/admin/hsun/tde_wallet/
OPEN

We can delete this local wallet and revert to use first wallet.

oracle@warwick$ rm /app/oracle/admin/hsun/tde_wallet/cwallet.sso 

oracle@warwick$ sqlplus / as sysdba

SYS@hsun SQL>startup
SYS@hsun SQL>select * from v$encryption_wallet;

WRL_TYPE
--------------------
WRL_PARAMETER
----------------------------------
STATUS
------------------
file
/app/oracle/admin/hsun/tde_wallet/
CLOSED

SYS@hsun SQL>alter system set encryption wallet open identified by "password";

System altered.

SYS@hsun SQL>select * from v$encryption_wallet;

WRL_TYPE
--------------------
WRL_PARAMETER
----------------------------------
STATUS
------------------
file
/app/oracle/admin/hsun/tde_wallet/
OPEN

SYS@hsun SQL>CREATE TABLESPACE securespace
DATAFILE '/app/oracle/oradata/hsun/secure01.dbf'
SIZE 150M
ENCRYPTION
DEFAULT STORAGE(ENCRYPT);

Tablespace created.

SYS@hsun SQL>
SYS@hsun SQL>create table  person (id number, name varchar2(20))  tablespace securespace;

Table created.

SYS@hsun SQL>insert into person values (1, 'hongfeng');

1 row created.

SYS@hsun SQL>commit;

Commit complete.

SYS@hsun SQL>shutdown immediate;
SYS@hsun SQL>startup

SYS@hsun SQL>select * from person;
select * from person
              *
ERROR at line 1:
ORA-28365: wallet is not open

SYS@hsun SQL>alter system set encryption wallet open identified by "password";

System altered.

SYS@hsun SQL>select * from person;

        ID NAME
---------- --------------------
         1 hongfeng

SYS@hsun SQL>exit

We can re-create a local wallet. We don’t need to use the first wallet now. Local wallet will take over from it.

oracle@warwick$ orapki wallet create -wallet /app/oracle/admin/hsun/tde_wallet -auto_login_local

oracle@warwick$ sqlplus / as sysdba

SYS@hsun SQL>select * from v$encryption_wallet;

WRL_TYPE
--------------------
WRL_PARAMETER
----------------------------------
STATUS
------------------
file
/app/oracle/admin/hsun/tde_wallet/
OPEN

SYS@hsun SQL>shutdown immediate;
SYS@hsun SQL>startup
SYS@hsun SQL>select * from v$encryption_wallet;

WRL_TYPE
--------------------
WRL_PARAMETER
----------------------------------
STATUS
------------------
file
/app/oracle/admin/hsun/tde_wallet/
OPEN

SYS@hsun SQL>select * from person;

        ID NAME
---------- --------------------
         1 hongfeng

We can change the password of first wallet. The “orapki” only require you to input password once. So be careful you know what you input. Or put the password in the command line.

oracle@warwick$ orapki wallet change_pwd -wallet /app/oracle/admin/hsun/tde_wallet 
Enter wallet password:       llll

New password:
Enter wallet password:          88

oracle@warwick$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Fri Jan 27 13:41:30 2012

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning and Real Application Testing options

SYS@hsun SQL>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@hsun SQL>startup

SYS@hsun SQL>select * from person;

        ID NAME
---------- --------------------
         1 hongfeng

We can move the wallet to different location.

  
oracle@warwick$ mv /app/oracle/admin/hsun/tde_wallet/
cwallet.sso  ewallet.p12  
oracle@warwick$ mv /app/oracle/admin/hsun/tde_wallet/* /app/oracle/admin/hsun/tde_backup
oracle@warwick$ ls -l /app/oracle/admin/hsun/tde_backup
total 8
-rw-------   1 oracle   oinstall    1387 Jan 27 13:41 cwallet.sso
-rw-------   1 oracle   oinstall    1310 Jan 27 13:41 ewallet.p12
oracle@warwick$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Fri Jan 27 14:30:47 2012

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning and Real Application Testing options

SYS@hsun SQL>shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SYS@hsun SQL>startup
ORACLE instance started.

SYS@hsun SQL>select * from v$encryption_wallet;

WRL_TYPE
--------------------
WRL_PARAMETER
-------------------------------------------------
STATUS
------------------
file
/app/oracle/admin/hsun/tde_wallet/
CLOSED

SYS@hsun SQL>select * from hsun.person;
select * from hsun.person
                   *
ERROR at line 1:
ORA-28365: wallet is not open

SYS@hsun SQL>exit

oracle@warwick$ cp  /app/oracle/admin/hsun/tde_backup/
cwallet.sso  ewallet.p12  
oracle@warwick$ cp  /app/oracle/admin/hsun/tde_backup/cwallet.sso  /app/oracle/admin/hsun/tde_wallet/

oracle@warwick$ sqlplus / as sysdba

SYS@hsun SQL>alter system set encryption wallet open;
                                      *
ERROR at line 1:
ORA-28356: invalid open wallet syntax

SYS@hsun SQL>shutdown immediate;
SYS@hsun SQL>startup

SYS@hsun SQL>select * from hsun.person;

        ID NAME
---------- ----------------------------------------
         1 peter

Oracle securefile use TDE master key as well.

HSUN@hsun SQL>CREATE TABLE securefile_tab (
  id         NUMBER,
  clob_data  CLOB
)   2    3    4  
  5  tablespace users
  6  LOB(clob_data) STORE AS SECUREFILE securefile_lob(
 encrypt
 compress
);  7    8    9  

Table created.

HSUN@hsun SQL>select segment_name , tablespace_name from user_segments;   

SEGMENT_NAME                                                                      TABLESPACE_NAME
--------------------------------------------------------------------------------- ------------------------------
PERSON                                                                            SECURESPACE

HSUN@hsun SQL>INSERT INTO securefile_tab VALUES (1, 'ONE');
INSERT INTO securefile_tab VALUES (2, 'TWO');
COMMIT;

1 row created.

HSUN@hsun SQL>
1 row created.

HSUN@hsun SQL>
Commit complete.

HSUN@hsun SQL>select segment_name , tablespace_name from user_segments;   

SEGMENT_NAME                                                                      TABLESPACE_NAME
--------------------------------------------------------------------------------- ------------------------------
PERSON                                                                            SECURESPACE
SECUREFILE_TAB                                                                    USERS
SYS_IL0000074563C00002$$                                                          USERS
SECUREFILE_LOB                                                                    USERS

Auto login Wallet
Oracle warned that:
If an auto login wallet is being used, you must not use the ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY “password” command.

In my test, no harm if you run that command.

SYS@hsun SQL>ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "password";

ERROR at line 1:
ORA-28354: wallet already open

BUG 11727498 Recreating Wallet
If dropping the wallet after creating TDE encrypted data (using column or tablespace encryption), the wallet can’t be recreated.

This issue has been investigated in:
BUG 11727498 – SECOND WALLET CREATED AFTER DELETE ORIGINAL DOES NOT HAVE TS MASTER KEY
The bug is fixed starting with release 11.2.0.3.
Whenever recreating the wallet, make sure to do a full log switch rotation and a checkpoint after the wallet has been closed.

Hongfeng Sun : 27 Jan 2012 15:25 |  Comments (0) |  Close comments |  Report a problem

Use Timestamp type instead of Date type

Today I noticed another problem caused by Date type.

select page3_.*
from sb2.NEWS_ITEM this_ inner join sb2.CONTENT_FETCHER cf1_ on this_.news_id=cf1_.ID
 inner join sb2.CONTENT content2_ on cf1_.content_id=content2_.id 
  inner join sb2.PAGE page3_ on content2_.page_id=page3_.id
where ( this_.discriminator = 'calendar_item') and page3_.LAST_UPDATED_DATE>:1 
order by page3_.LAST_UPDATED_DATE asc, this_.publication_date asc

The SQL Tunning Advisor suggested that “page3_.LAST_UPDATED_DATE>:1 ” has an implicit data type conversion prevent the optimizer from selecting indices.

The LAST_UPDATED_DATE column is of “DATE” type.

From V$SQL_MONITOR, the bind variable is:

<binds>
<bind name=":1" pos="1" dty="180" dtystr="TIMESTAMP" maxlen="11" len="7" format="hexdump">78640101010101</bind>
</binds>

After inspecting the source code, it is appear bind as java.util.Date.

I think it should be bound as java.sql.Date, not java.sql.Timestamp or java.util.Date. See http://databaseperformance.blogspot.com/2009/01/java-data-types-and-oracle-index-usage.html

Oracle saw the value it was getting was of type TIMESTAMP, which has sub-second values in it. And rather than lose precision by truncating its value, it was instead converting the DATE value in each data row to a TIMESTAMP value before comparing.

Better to use Timestamp type instead of Date type. The index should work properly whether you bind java.sql.Date or java.sql.Timestamp.

Data Type Conversion
Oracle will convert the value to most precise type:

Column type DATE, predicate value is TIMESTAMP => Right size is more precise => Convert every row to timestamp
Column type Timestap, predicate value is DATE => Left size is more precise => convert predicate to Timestap
Column type varchar, predicate value is number => Right size is more precise => convert every row to varchar
Column type is number, predicate value is varchar => Left size is more precise => convert predicate value to number

Hongfeng Sun : 25 Jan 2012 16:12 |  Tags: Oracle |  Comments (0) |  Close comments |  Report a problem

Warning messages after failover

I failovered to standby database. The following message kept on loged in alert log.

RFS[303]: Assigned to RFS process 18298
RFS[303]: Database mount ID mismatch [0x5f0edd7b:0x5788f47c] (1594809723:1468593276)
RFS[303]: Destination database ID mismatch [0x5bb6deb1:0x5775211a] (1538711217:1467293978)
RFS[303]: Not using real application clusters

I unset fal_client, fal_server, and log_archive_config the message wont go away.
Suddenly realized that the old primary is still sending archive log to this new standby.

SQL>alter system reset log_archive_dest_2;  (this is a 10g db)
ERROR at line 1:ORA-00905: missing keyword

SQL>alter system set log_archive_dest_state_2=defer;
System altered.

That stopped the message.

Hongfeng Sun : 18 Jan 2012 15:54 |  Tags: Oracle-Availability |  Comments (0) |  Close comments |  Report a problem

Log apply enhancement in 11g R2

Oracle 11g introduced a new framework for Extended Datatype Support (EDS) that can be used with certain datatypes that lack native redo-based support.

It support tables with SDO_GEOMETRY and Binary XML columns.

Not of too much use to use but worth to know.
See note ID 949516.1

Hongfeng Sun : 17 Jan 2012 10:56 |  Comments (0) |  Close comments |  Report a problem