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

NLS_CHARACTERSET: WE8ISO8859P15
NLS_NCHAR_CHARACTERSET: AL16UTF16

OS platform: Solaris 10×86

[oracle@beta:~]$ locale   
LANG=en_GB.UTF-8
LC_CTYPE=en_GB.ISO8859-15
LC_NUMERIC=en_GB.ISO8859-15
LC_TIME=en_GB.ISO8859-15
LC_COLLATE=en_GB.ISO8859-15
LC_MONETARY=en_GB.ISO8859-15
LC_MESSAGES=C

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

1. To display the value correctly

NLS_LANG=ENGLISH_UNITED KINGDOM.AL32UTF8
Note:
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 sysas.cn; ----- -------------------
7 气候
2 北京
5 气候
6 おはよう
1 좋은 아침

ID NAME

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:

export ORA_NCHAR_LITERAL_REPLACE=true

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

1 row created.

SQL > commit;

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

ID NAME

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

VIP in RAC

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.

Command:

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

Very good article:

http://www.dbas-oracle.com/2013/08/SCAN-NAME-SCAN-VIP-SCAN-Listener-Node-VIP-Local-Listener-Remote-Listener-RAC.html

http://satya-racdba.blogspot.co.uk/2012/07/virtual-ip-vip-address-rac-oracle.html

  • 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 “http://www.java.com/” 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 http://javatester.org/enabled.html
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/libnpjp2.so

Go “http://www.java.com/” 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 http://www.java.com/en/download/testjava.jsp 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:

STARTUP MOUNT
BACKUP CURRENT CONTROLFILE FOR STANDBY;
SQL> ALTER DATABASE OPEN;
SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';  # so backup is consistent and recoverable
BACKUP ARCHIVELOG ALL NOT BACKED UP 1 TIMES;
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:

COPY CURRENT CONTROLFILE FOR STANDBY TO '/tmp/sby_control01.ctl';

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

impdp

Moved the floating ip to to another machine,


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 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 11.2.0.3.0 – 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

Then

re-run it again.
Everything works


March 08, 2012

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.


February 23, 2012

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');

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

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


Search this blog

Search Warwick Blogs

 

Tags

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