Monday, 27 February 2012

Oracle Secure Backup Express for free anyone ?

Well, after Oracle providing Oracle Database Express Edition for free and now if you need a Tape backup solution, we can use Oracle Secure Backup Express for "FREE". "Free" with below notes :


"...Protecting data in a single-server Oracle environment has just gotten easier with the
release of Oracle Secure Backup Express (XE). Oracle Secure Backup XE provides
tape data protection for one database server directly attached to one tape drive." 
Read more

Anyway it is good for a start and once the needs grow, you can just upgraded to Full version of Oracle Secure Backup.
Happy backup :)

Thursday, 10 November 2011

Avoid ORA-31603 on Sqldeveloper on DDL tabs

I recently have an issue of giving privileges to a Database support user that user for application team to do production support. I have grant Select any dictionary and Select Catalog role to the user, but still they still not able to view the SQL tabs(DDL scripts) on Sqldeveloper from other users and giving ORA-31603.
I tested in UAT database all are ok without any issue to view the DDL tab. After comparing what is the different between UAT and PROD sqldeveloper, found out that when I grant "Select Catalog Role" it is not the default Role of that user. After adding "Select Catalog Role" as a default role to that support user, it works perfectly now.
Not sure if this only happen to my environment. We use Sqldeveloper-Version 2.1.1.64.

Thursday, 6 October 2011

Oracle Software Investment Guide

For those needs more detail information on how oracle licensing on their products. Please refer to this links from oracle  : http://www.oracle.com/us/corporate/pricing/sig-070616.pdf. Many people ask about Standby/ Dataguard licensing if let say we have Oracle DB EE license on primary site. You may refer to that links to give some guide to it.

Tuesday, 20 September 2011

Oracle Database Support annoucement on the Itanium Processor

To anyone haven't heard about this news from Oracle. Oracle has an update about what will be supported and not supported on Itanium processor. For my case, we actually just upgraded RAC running on HP UX Itanium from 10.2.0.4 to 11.2.0.1. It is quiet a "big" news for us since most of the Databases are running on HP UX Itanium. See below notes :

"Oracle Database 11g Release 2 is currently shipping on HP-UX Itanium, latest available patchset level is 11.2.0.2. We will continue to ship further 11g release 2 patchsets, PSUs and CPUs on HP-UX Itanium, just as we do on other platforms. The next release of Oracle Database, currently called 12g, will not be shipped on HP-UX Itanium. Customers can either stay on an Oracle Database 11g Release, as per Oracle's Lifetime Support Policy, or move to another platform when they want to upgrade to the next major Oracle release.

Oracle Database 11g Release 2 (including all the 11.2 patchsets)will continue to be supported on HP-UX 11.31. Oracle will certify Database 11.2 on future versions of the HP-UX operating system that are generally available within the Extended Support period for 11g Release 2 as documented in the Oracle Lifetime Support Policy.  Oracle does not require explicit certification on future versions of the Itanium processor provided HP supports the versions of the HP-UX operating system that Oracle Database 11g Release 2 is certified on.
..."

For more details please check this article on Oracle support site : 1307745.1

Friday, 26 August 2011

How to recover Oracle TDE wallet Password which almost "Lost"

I recently have an issue with Oracle TDE wallet password. As part of our quarterly Security patch, we are planning to patch one of the database which using this Wallet for the encryption. Means I will need to shutdown and startup the database. The problem is when we restart the database then we need to open the wallet so the tables that encrypted can be visible for the trusted connection. Just guess what ? nobody sure about what is the wallet password in production.( The previous DBA has left the company, also with the password - "Very good").

Back to the issue, now I have to make sure I can open the wallet with the correct password before I can shutdown this database. This issue as good as "This Database should be alive all the time", once it is down without the wallet password it will become useless. I actually raise SR about this issue on how to test the wallet password without affecting our production database operation. As in the documentation written, there is no way to recover the password if the password is lost. But they never mention on how to test it if somehow nobody remember the password.Anyhow Oracle support still telling me there is no way to test the password.

Well I almost give up. But I just do my own testing, I just copy the wallet file from production to my testing environment. Let say my wallet in production are located here(sqlnet.ora) :
ENCRYPTION_WALLET_LOCATION=
     (SOURCE=(METHOD=FILE)(METHOD_DATA=
             (DIRECTORY=/u01/app/oracle/admin/DBPROD/wallet)))

I just copy "ewallet.p12" to my testing environment directory :
ENCRYPTION_WALLET_LOCATION=
    (SOURCE=(METHOD=FILE)(METHOD_DATA=
            (DIRECTORY=D:\Oracle\product\10.2.0\admin\DB10G\wallet)))

The first thing is we need to test  to open that wallet with any password that we think is correct. Once we can open the wallet with the correct password, we can test if the wallet is valid to open the data in production. I import a table from production that having encryption on one of the columns. Well this tricks works for me and I managed to patch my database in production and open the wallet safely.
Of course if the wallet password can't be find, my suggestion is make sure that database keep alive :)
Let's keep the wallet password safely now.

Thursday, 14 July 2011

Some Note on Firewall with Oracle RAC

If anyone implementing Firewall filtering for SQL*Net Proxy, please make sure that packet redirection is using fix port not random, because this will make the Listener redirection failed by assigning random port number.
For details please see this note on Oracle Support (361284.1).
On one of our system, this issue suddenly come up when some application that calling tnsnames to use the loadbalancing intermittently failed with ORA-12541. Initially I thought it is a connection issue, but further I do a trace from client side and found below :

....
[000001 05-APR-2011 14:55:15:106] nspsend: 01 09 00 00 06 00 00 00 |........|
[000001 05-APR-2011 14:55:15:106] nspsend: 00 00 28 44 45 53 43 52 |..(DESCR|
[000001 05-APR-2011 14:55:15:106] nspsend: 49 50 54 49 4F 4E 3D 28 |IPTION=(|
[000001 05-APR-2011 14:55:15:106] nspsend: 41 44 44 52 45 53 53 3D |ADDRESS=|
[000001 05-APR-2011 14:55:15:106] nspsend: 28 50 52 4F 54 4F 43 4F |(PROTOCO|
[000001 05-APR-2011 14:55:15:106] nspsend: 4C 3D 54 43 50 29 28 48 |L=TCP)(H|
[000001 05-APR-2011 14:55:15:106] nspsend: 4F 53 54 3D 6D 6E 65 74 |OST=mnet|
[000001 05-APR-2011 14:55:15:106] nspsend: 64 62 32 2D 76 69 70 29 |db2-vip)|
[000001 05-APR-2011 14:55:15:106] nspsend: 28 50 4F 52 54 3D 31 35 |(PORT=15|
[000001 05-APR-2011 14:55:15:106] nspsend: 32 31 29 29 28 4C 4F 41 |21))(LOA|
[000001 05-APR-2011 14:55:15:106] nspsend: 44 5F 42 41 4C 41 4E 43 |D_BALANC|
[000001 05-APR-2011 14:55:15:106] nspsend: 45 3D 79 65 73 29 28 43 |E=yes)(C|
[000001 05-APR-2011 14:55:15:106] nspsend: 4F 4E 4E 45 43 54 5F 44 |ONNECT_D|
[000001 05-APR-2011 14:55:15:106] nspsend: 41 54 41 3D 28 53 45 52 |ATA=(SER|
[000001 05-APR-2011 14:55:15:106] nspsend: 56 45 52 3D 44 45 44 49 |VER=DEDI|
[000001 05-APR-2011 14:55:15:106] nspsend: 43 41 54 45 44 29 28 53 |CATED)(S|
[000001 05-APR-2011 14:55:15:106] nspsend: 45 52 56 49 43 45 5F 4E |ERVICE_N|
[000001 05-APR-2011 14:55:15:106] nspsend: 41 4D 45 3D 43 48 4E 4E |AME=CHNN|
[000001 05-APR-2011 14:55:15:106] nspsend: 45 4C 29 28 46 41 49 4C |EL)(FAIL|
[000001 05-APR-2011 14:55:15:106] nspsend: 4F 56 45 52 5F 4D 4F 44 |OVER_MOD|
[000001 05-APR-2011 14:55:15:106] nspsend: 45 3D 28 54 59 50 45 3D |E=(TYPE=| ----> Here the client has sent connect packet to the listener on mnetdb2-vip
[000001 05-APR-2011 14:55:15:106] nspsend: 53 45 4C 45 43 54 29 28 |SELECT)(|
[000001 05-APR-2011 14:55:15:106] nspsend: 4D 45 54 48 4F 44 3D 42 |METHOD=B|
[000001 05-APR-2011 14:55:15:106] nspsend: 41 53 49 43 29 28 52 45 |ASIC)(RE|
[000001 05-APR-2011 14:55:15:106] nspsend: 54 52 49 45 53 3D 31 38 |TRIES=18|
[000001 05-APR-2011 14:55:15:106] nspsend: 30 29 28 44 45 4C 41 59 |0)(DELAY|
[000001 05-APR-2011 14:55:15:106] nspsend: 3D 35 29 29 28 43 49 44 |=5))(CID|
[000001 05-APR-2011 14:55:15:106] nspsend: 3D 28 50 52 4F 47 52 41 |=(PROGRA|
[000001 05-APR-2011 14:55:15:106] nspsend: 4D 3D 73 71 6C 70 6C 75 |M=sqlplu|
[000001 05-APR-2011 14:55:15:106] nspsend: 73 29 28 48 4F 53 54 3D |s)(HOST=|
[000001 05-APR-2011 14:55:15:106] nspsend: 6D 6E 65 74 61 70 70 33 |mnetapp3|
[000001 05-APR-2011 14:55:15:106] nspsend: 29 28 55 53 45 52 3D 6F |)(USER=o|
[000001 05-APR-2011 14:55:15:106] nspsend: 72 61 63 6C 65 29 29 29 |racle)))|

.....
[000001 05-APR-2011 14:55:15:110] nscall: connecting...
[000001 05-APR-2011 14:55:15:110] nsc2addr: entry
[000001 05-APR-2011 14:55:15:110] nsc2addr: (ADDRESS=(PROTOCOL=TCP)(HOST=mnetdb1-vip)(PORT=33949))
[000001 05-APR-2011 14:55:15:110] nttbnd2addr: entry
[000001 05-APR-2011 14:55:15:110] snlinGetAddrInfo: entry
[000001 05-APR-2011 14:55:15:110] snlinGetAddrInfo: Invalid IP address string mnetdb1-vip ----> Here the transport was unable to open to the PORT 33949 on mnetdb1-vip
[000001 05-APR-2011 14:55:15:110] snlinFreeAddrInfo: entry

At that point we notice it assign to port number 33949 which wrong port number. We only use port 1521.
The challenge now is to ask the network engineer to change the firewall rule. In our case the firewall use by many other subsytem. Well this issue never been an issue for all other application that using Weblogic.