May 01, 2014

memory_target sga_target and db_cache_size

On my two databases, the buffer cache were way too small. One buffer was 128 mb, which was the default value set in db_cache_size; one is 64 mb while the db_cache_size value is 0.

On both servers, the memory_target parameters were set (5g and 3g respectively) while sga_targets are set to zero. It seemed ASSM mis-configured the buffer cache.

After I set the sga_target to a reasonable value, ASSM began to adjust the cache buffer and set them to a much higher value. The first one was set to 700 mb and the second 608 mb.

Both databases are on solaris 10.

Maybe we should either set the sga_target, or set a high value in db_cache_size.

February 24, 2014

System frozen

This morning users couldn’t connect to one test database server. From listener log, the following errors:

TNS-12540: TNS:internal limit restriction exceeded.

Logged in on global zone (not db zone)
prstat -Z, pay attention to the history load “1, 5, 15”. Noticed the load in “15 minute” period is 63. The “psrinfo” show 32 core, 32 * 2 = 64 threads. It meant all CPU were busy.

Top command also worked fine. But each oracle process has 15G in “SIZE” if misleading. It should mean all oracle process have 15G in total, not each has 15G.

“vmstat -S 5” command,
check si and so column for swap in/out
swap in/out is different to page in/out. See Here

December 13, 2013

How to retrieve and insert nvarchar data in SQLPLUS

The database settings is:


OS platform: Solaris 10×86

[oracle@beta:~]$ locale   

The table cn has column “name” of type nvarchar2:

1. To display the value correctly

AL32UTF8 is different to database character set WE8ISO8859P15. Client character set need to be a same or super set of column value encoding set.

SQL > select * from; ----- -------------------
7 气候
2 北京
5 气候
6 おはよう
1 좋은 아침


To find out what value of your NLS_LANG environment variable, query:
select userenv(‘language’) from dual

2. To insert the a value into nvarchar

set another variable:


SQL > insert into values(7, N’上海’);

1 row created.

SQL > commit;

SQL > select * from; ----- -------------------
7 气候
7 上海
2 北京
5 气候
6 おはよう
1 좋은 아침


3. To edit the spooled out file
set LC_CTYPE=en_GB.UTF-8

Then vi command can display the file content correctly.

December 12, 2013


What’s SCAN IP used for?

Receive connection from client and pass on to local listeners

  • Each SCAN VIP has SCAN Listener associated with each other and both works as a pair. One SCAN Listener is created corresponding to one SCAN VIP.
  • Whenever a new connection request is made the SCAN listener listening on a SCAN IP address and the SCAN port is contracted on a client’s behalf. Because all services on the cluster (HS: even the nodes that have no scan listener running on) are registered with the SCAN listener, the SCAN listener replies with the address of the local listener as Node VIP address on the least-loaded node (Each scan listener keeps updated cluster load statistics) and connection is routed to that node.

What’s VIP used for ?

The local listener run on the VIP. If local node failed, the VIP is moved to surviving node, avoiding the long delay of TCP time out.


srvctl status vip -n rad1dev
srvctl config scan
srvctl config scan_listener

Very good article:

  • Virtual IP must be associated with the same interface name on every node that is a part of the cluster
  • The Virtual IP address should not be in use at the time of the installation because this is a Virtual IP address that Oracle manages internally to the RAC processes.
  • This virtual IP address does not require a separate NIC. The VIPs should be registered in the DNS.
  • Virtual IP (VIP) is for fast connection establishment in failover dictation

April 12, 2013

Chrome Java plugin

On my ubuntu box, there are two versions of JDK installed: OpenJDK and Sun JDK.

The latest version OpenJDK I can get now is openjdk-7-jdk.

root> java -version
java version "1.7.0_15" 
OpenJDK Runtime Environment (IcedTea7 2.3.7) (7u15-2.3.7-0ubuntu1~12.04.1)
OpenJDK 64-Bit Server VM (build 23.7-b01, mixed mode)

With OpenJDK, you can install package “icedtea-7-plugin” to install the java plugin for Chrome. You can go “” and click “Do I have Java” to verify the Java version. The verification page display a blank page even after I accepted the security warning. However, I can succesfully verify in
Somehow, the IcedTea crashed often.

I downloaded the binary version of Sun JDK and installed the software by unzip the binaries:

root>  java -version
java version "1.7.0_17" 
Java(TM) SE Runtime Environment (build 1.7.0_17-b02)
Java HotSpot(TM) 64-Bit Server VM (build 23.7-b01, mixed mode)

To make Chrome use the jre plugins:

cd ~/.mozilla/plugins
 ln -s /apps/jdk1.7.0_17/jre/lib/amd64/

Go “” and click “Do I have Java” to verify the java successfully.

Type “chrome://plugins” in the address bar, you can see both “IcedTea-Web Plugin” and “Java™ – Version: 1.7.0_17” are enabled. The latter takes precedence.

You can also use to test the Java plugin.

In the end, I chose to use OpenJDK + IcedTea. Don’t want to manually download and install Sun JDK every time I need to upgrade.

February 13, 2013

Error 16058 attaching RFS server to standby instance at host

I created a new physical standby database. The primary database wont be able to archive to it. The trace file show the following dreadful messge:

Logged on to standby successfully
Client logon and security negotiation successful!
Error 16058 attaching RFS server to standby instance at host 'charlie'
Error 16058 attaching to destination LOG_ARCHIVE_DEST_2 standby host 'charlie'
*** 2013-02-13 16:46:11.598 4320 krsh.c
PING[ARC2]: Heartbeat failed to connect to standby 'charlie'. Error is 16058.
*** 2013-02-13 16:46:11.598 2932 krsi.c
krsi_dst_fail: dest:2 err:16058 force:0 blast:1

After endless changing of configuration and googles, I notice a thread suggest that the standby control file is not generated properly. It might caused backing up standby controfile while primary database is open.

Oracle 10g R2 mentioned two ways to generate the standby control file.

Copied as follows:

Step 1   Connect to the primary database.

Connect to the primary database and, if desired, the recovery catalog database. For example, enter:
% rman TARGET SYS/oracle@trgt CATALOG rman/cat@catdb

Step 2   Create the standby control file.

Use either of the following commands to create the standby control file. 
The only difference between BACKUP and COPY commands is that the file format of the backup file is different.

Using the BACKUP command

Mount the primary database and create the standby control file with the BACKUP CURRENT CONTROLFILE FOR STANDBY command. 
The following example uses a configured channel to create the standby control file. Then open the database, archive all unarchived redo log files, 
and back up any log files that have not yet been backed up at least once:

SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';  # so backup is consistent and recoverable
Using the COPY command

Copy the current primary control file. Specify the FOR STANDBY option of the COPY CURRENT CONTROLFILE command to make a copy of the current 
control file that is usable as a standby control file. For example:


Oracle 11g R2 document is not very helpful. It only mentioned the BACKUP method.

I have be using the first method (BACKUP command) to generate the control file while the primary database is open. It works most of the time. Until now.

I tried the COPY method:
1) Generated standby using COPY CURRENT CONTROLFILE FOR STANDBY TO ’/tmp/sby_control01.ctl’;
2) Shutdown the standby database.
3) Copied file /tmp/sby_control01.ctl’ from primary and replace it with the control files used by standby database.
4) Startup the database .

The primary database immediately found the archive destination.

LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2

July 10, 2012


Moved the floating ip to to another machine,

Connected to: Oracle Database 11g Enterprise Edition Release - 64bit Production
With the Partitioning option
ORA-39001: invalid argument value
ORA-39200: Link name "sbuser" is invalid.
ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from SBUSER

I re-create the db link

Connected to: Oracle Database 11g Enterprise Edition Release – 64bit Production
With the Partitioning option
ORA-39006: internal error
ORA-39113: Unable to determine database version
ORA-04062: timestamp of package “SYS.DBMS_UTILITY” has been changed

ORA-39097: Data Pump job encountered unexpected error -4062


re-run it again.
Everything works

March 08, 2012


    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());
                    } catch (InterruptedException e) {
                    } 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.

February 23, 2012

Instantiate table in Oracle Streams

Writing about web page

Credit to this article

1. Stop Capture

2. Stop propagation

3. Stop Apply Process on Target(T)


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:


To delete all errors: 


5. Truncate table 

Truncate table UWTABS.UW_TPRS

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


 source_object_name => 'UWTABS.UW_TPRS', 
 source_database_name => '', 
 instantiation_scn => iscn);

Synchronize the problem table through datapump exp/imp

8. Start the Apply Process on Target(T)


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


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


February 21, 2012

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 - 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 - 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

Seemed the ”-” in the job name caused problem.

Search this blog

Search Warwick Blogs



Most recent comments

  • Oracle 11g is the clear leader in this area. Oracle has invested heavily into self–tuning capabiliti… by shaane on this entry
  • All the features are very nice.I like Repair Advisors the most.Oracle 10G introduced some of the dat… by lucy on this entry
  • "logical standby failed to re–start, I am also getting the same error. I have search on internet and… by anemia on this entry
  • Thanks for the nice information because from last three days i am stuck on this problem…. by robot vacuum on this entry
  • So what happen City speed dating when you tried this coding once in your PC.. Please tell me so i am… by John Bergeron on this entry
RSS2.0 Atom
Not signed in
Sign in

Powered by BlogBuilder