ORACLE Cookies

Light HTML Oracle Support Site

2011-01-26T01:32:00.000-08:00

The standard Flash GUI of the actual Oracle Support site is driving me crazy. For a guy like me, used to work especially with console applications, addicted to VIM and browsing using Vimperator, having this Flash site in front of me really hurts. It hurts when I have to move my hand to click something with the mouse, it hurts when I cannot go back to the previous page, it hurts... it hurts... it hurts! Oracle Support is not a game, it doesn't need animation, video or other such things Flash is good at.

Okey, enough with all these complaints! If you don't like Flash, or your browser simply doesn't support it you may switch to the plain html interface: https://supporthtml.oracle.com. Isn't it cool? Just have a look:

Now I can use my beloved Vimperator and, of course, Flashblock.

However, I have to point out the limitations.The HTML option does not include the following functionality, which is only available in the Flash version of My Oracle Support:

* Systems
* Projects
* Healthchecks
* Patch Advice & Recommendations
* Inventory Reporting
* OnDemand Portal, Service Request and RFC Functionality
* CRM OnDemand Service Requests & Knowledge

As far as I'm concerned, I didn't use those features anyway therefore I'm good!

crs_stat pretty print on RAC 11.2

2011-01-24T02:27:00.000-08:00

I really don't like the way crs_stat -t displays RAC status information on my 11.2.0.2 database. For example, one of my instances are down. Can you figure out this looking at the output below?

[grid@owl ~]$ crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora.DATA.dg    ora....up.type ONLINE    ONLINE    hen         
ora....ER.lsnr ora....er.type ONLINE    ONLINE    hen         
ora....N1.lsnr ora....er.type ONLINE    ONLINE    hen         
ora....N2.lsnr ora....er.type ONLINE    ONLINE    hen         
ora....N3.lsnr ora....er.type ONLINE    ONLINE    owl         
ora.asm        ora.asm.type   ONLINE    ONLINE    hen         
ora.cvu        ora.cvu.type   ONLINE    ONLINE    hen         
ora.gns        ora.gns.type   ONLINE    ONLINE    hen         
ora.gns.vip    ora....ip.type ONLINE    ONLINE    hen         
ora.gsd        ora.gsd.type   OFFLINE   OFFLINE               
ora....SM2.asm application    ONLINE    ONLINE    hen         
ora....EN.lsnr application    ONLINE    ONLINE    hen         
ora.hen.gsd    application    OFFLINE   OFFLINE               
ora.hen.ons    application    ONLINE    ONLINE    hen         
ora.hen.vip    ora....t1.type ONLINE    ONLINE    hen         
ora....network ora....rk.type ONLINE    ONLINE    hen         
ora.oc4j       ora.oc4j.type  ONLINE    ONLINE    hen         
ora.ons        ora.ons.type   ONLINE    ONLINE    hen         
ora....SM1.asm application    ONLINE    ONLINE    owl         
ora....WL.lsnr application    ONLINE    ONLINE    owl         
ora.owl.gsd    application    OFFLINE   OFFLINE               
ora.owl.ons    application    ONLINE    ONLINE    owl         
ora.owl.vip    ora....t1.type ONLINE    ONLINE    owl         
ora.poc.db     ora....se.type ONLINE    ONLINE    hen         
ora....uci.svc ora....ce.type ONLINE    ONLINE    hen         
ora....ry.acfs ora....fs.type ONLINE    ONLINE    hen         
ora.scan1.vip  ora....ip.type ONLINE    ONLINE    hen         
ora.scan2.vip  ora....ip.type ONLINE    ONLINE    hen         
ora.scan3.vip  ora....ip.type ONLINE    ONLINE    owl

The resource "ora.poc.db" is ONLINE therefore no red flags you might say. Well, bad luck: the database is up & running but with only one instance. The other one is dead. The database is policy managed but I want to be aware if all instances from the pool are running. How can we figure this out? Not a very big deal, just issue: "crsctl status resource". You'll get something like this:

NAME=ora.poc.db
TYPE=ora.database.type
TARGET=OFFLINE, ONLINE
STATE=OFFLINE, ONLINE on hen

Looking at TARGET and STATE is clear that something is OFFLINE and the red flag pops up right away. Of course, "crsctl status resource" command has the same problem as crs_stat when it comes to pretty print the result. It also has a tabular format (see the -t switch) but it'a a little bit verbose as it also displays the local resources status. But hey, do you remember Note 259301.1? It was about an awk script used for parsing the crs_stat output and displaying it in a nicer way. Okey, let's take that script and change it to take the output of the "crsctl status resource" command. I'm not an awk expert, but the following script is working pretty well:

#!/usr/bin/ksh
#
# Sample 10g CRS resource status query script
#
# Description:
#    - Returns formatted version of crs_stat -t, in tabular
#      format, with the complete rsc names and filtering keywords
#   - The argument, $RSC_KEY, is optional and if passed to the script, will
#     limit the output to HA resources whose names match $RSC_KEY.
# Requirements:
#   - $ORA_CRS_HOME should be set in your environment 

RSC_KEY=$1
QSTAT=-u
AWK=/bin/awk    # if not available use /usr/bin/awk

# Table header:echo ""
$AWK \
  'BEGIN {printf "%-45s %-10s %-18s\n", "HA Resource", "Target", "State";
          printf "%-45s %-10s %-18s\n", "-----------", "------", "-----";}'

# Table body:
$ORACLE_HOME/bin/crsctl status resource | $AWK \
 '
function ltrim(s) { sub(/^[ \t]+/, "", s); return s }
function rtrim(s) { sub(/[ \t]+$/, "", s); return s }
function trim(s)  { return rtrim(ltrim(s)); }

  BEGIN { FS="="; state = 0; }
  $1~/NAME/ && $2~/'$RSC_KEY'/ {appname = $2; state=1};
  state == 0 {next;}
  $1~/TARGET/ && state == 1 {apptarget = $2; split(apptarget, atarget, ","); state=2;}
  $1~/STATE/ && state == 2 {appstate = $2; split(appstate, astate, ","); state=3;}
  state == 3 { split(appname, a, ","); 
               for (i = 1; i <= length(atarget); i++) { 
                 printf "%-45s %-10s %-18s\n", appname, trim(atarget[i]), trim(astate[i]) 
               }; 
               state=0;}'

And the output is:

[grid@owl ~]$ cs
HA Resource                                   Target     State             
-----------                                   ------     -----             
ora.DATA.dg                                   ONLINE     ONLINE on hen     
ora.DATA.dg                                   ONLINE     ONLINE on owl     
ora.LISTENER.lsnr                             ONLINE     ONLINE on hen     
ora.LISTENER.lsnr                             ONLINE     ONLINE on owl     
ora.LISTENER_SCAN1.lsnr                       ONLINE     ONLINE on hen     
ora.LISTENER_SCAN2.lsnr                       ONLINE     ONLINE on hen     
ora.LISTENER_SCAN3.lsnr                       ONLINE     ONLINE on owl     
ora.asm                                       ONLINE     ONLINE on hen     
ora.asm                                       ONLINE     ONLINE on owl     
ora.cvu                                       ONLINE     ONLINE on hen     
ora.gns                                       ONLINE     ONLINE on hen     
ora.gns.vip                                   ONLINE     ONLINE on hen     
ora.gsd                                       OFFLINE    OFFLINE           
ora.gsd                                       OFFLINE    OFFLINE           
ora.hen.vip                                   ONLINE     ONLINE on hen     
ora.net1.network                              ONLINE     ONLINE on hen     
ora.net1.network                              ONLINE     ONLINE on owl     
ora.oc4j                                      ONLINE     ONLINE on hen     
ora.ons                                       ONLINE     ONLINE on hen     
ora.ons                                       ONLINE     ONLINE on owl     
ora.owl.vip                                   ONLINE     ONLINE on owl     
ora.poc.db                                    OFFLINE    OFFLINE           
ora.poc.db                                    ONLINE     ONLINE on hen     
ora.poc.muci.svc                              ONLINE     ONLINE on hen     
ora.poc.muci.svc                              ONLINE     OFFLINE           
ora.registry.acfs                             ONLINE     ONLINE on hen     
ora.registry.acfs                             ONLINE     ONLINE on owl     
ora.scan1.vip                                 ONLINE     ONLINE on hen     
ora.scan2.vip                                 ONLINE     ONLINE on hen     
ora.scan3.vip                                 ONLINE     ONLINE on owl

Looking at the above output I can clearly see the partial OFFLINE status of my database. From my point of view, is much better.

Kill a Session From Any Node

2010-12-14T06:31:00.000-08:00

I really like this new 11g feature which allows the DBA to kill a session despite his session is on a different instance than the instance where the session to be killed resides. The ALTER SYSTEM KILL SESSION statement has been improved and allows specifying the instance number where the session you want to kill is located:

ALTER SYSTEM KILL SESSION 'sid, serial#, @inst_no';

Great!

Extending my RAC with a new node

2010-12-11T01:47:00.000-08:00

I have a 11.2.0.2 database comprised of one node. I especially created it with one node just to have the chance to add another node later. Why? Because I wanted to play with this new GPnP feature. So, despite my RAC was comprised of one node, it was actually a fully functional environment, with GNS, IPMI, CTSAS and a policy managed database. Okey, the process should be straightforward: run some CVU checks to see if the node to be added is ready and then run addNode.sh script from the GI home of the existing RAC node. In my case, the existing node was named "owl" and the node to be added was "hen".

First of all, I ran:

[grid@owl bin]$ cluvfy stage -pre nodeadd -n hen

Performing pre-checks for node addition

Checking node reachability...
Node reachability check passed from node "owl"


Checking user equivalence...
User equivalence check passed for user "grid"

Checking node connectivity...

Checking hosts config file...

Verification of the hosts config file successful

Check: Node connectivity for interface "eth0"
Node connectivity passed for interface "eth0"

Node connectivity check passed


Checking CRS integrity...

CRS integrity check passed

Checking shared resources...

Checking CRS home location...
The location "/u01/app/11.2.0.2/grid" is not shared but is present/creatable on all nodes
Shared resources check for node addition passed


Checking node connectivity...

Checking hosts config file...

Verification of the hosts config file successful

Check: Node connectivity for interface "eth0"
Node connectivity passed for interface "eth0"

Check: Node connectivity for interface "eth1"
Node connectivity passed for interface "eth1"

Node connectivity check passed

Total memory check passed
Available memory check passed
Swap space check passed
Free disk space check passed for "owl:/tmp"
Free disk space check passed for "hen:/tmp"
Check for multiple users with UID value 1100 passed
User existence check passed for "grid"
Run level check passed
Hard limits check passed for "maximum open file descriptors"
Soft limits check passed for "maximum open file descriptors"
Hard limits check passed for "maximum user processes"
Soft limits check passed for "maximum user processes"
System architecture check passed
Kernel version check passed
Kernel parameter check passed for "semmsl"
Kernel parameter check passed for "semmns"
Kernel parameter check passed for "semopm"
Kernel parameter check passed for "semmni"
Kernel parameter check passed for "shmmax"
Kernel parameter check passed for "shmmni"
Kernel parameter check passed for "shmall"
Kernel parameter check passed for "file-max"
Kernel parameter check passed for "ip_local_port_range"
Kernel parameter check passed for "rmem_default"
Kernel parameter check passed for "rmem_max"
Kernel parameter check passed for "wmem_default"
Kernel parameter check passed for "wmem_max"
Kernel parameter check passed for "aio-max-nr"
Package existence check passed for "make-3.81( x86_64)"
Package existence check passed for "binutils-2.17.50.0.6( x86_64)"
Package existence check passed for "gcc-4.1.2 (x86_64)( x86_64)"
Package existence check passed for "libaio-0.3.106 (x86_64)( x86_64)"
Package existence check passed for "glibc-2.5-24 (x86_64)( x86_64)"
Package existence check passed for "compat-libstdc++-33-3.2.3 (x86_64)( x86_64)"
Package existence check passed for "elfutils-libelf-0.125 (x86_64)( x86_64)"
Package existence check passed for "elfutils-libelf-devel-0.125( x86_64)"
Package existence check passed for "glibc-common-2.5( x86_64)"
Package existence check passed for "glibc-devel-2.5 (x86_64)( x86_64)"
Package existence check passed for "glibc-headers-2.5( x86_64)"
Package existence check passed for "gcc-c++-4.1.2 (x86_64)( x86_64)"
Package existence check passed for "libaio-devel-0.3.106 (x86_64)( x86_64)"
Package existence check passed for "libgcc-4.1.2 (x86_64)( x86_64)"
Package existence check passed for "libstdc++-4.1.2 (x86_64)( x86_64)"
Package existence check passed for "libstdc++-devel-4.1.2 (x86_64)( x86_64)"
Package existence check passed for "sysstat-7.0.2( x86_64)"
Package existence check passed for "ksh-20060214( x86_64)"
Check for multiple users with UID value 0 passed
Current group ID check passed

Checking OCR integrity...

OCR integrity check passed

Checking Oracle Cluster Voting Disk configuration...

Oracle Cluster Voting Disk configuration check passed
Time zone consistency check passed

Starting Clock synchronization checks using Network Time Protocol(NTP)...

NTP Configuration file check started...
No NTP Daemons or Services were found to be running

Clock synchronization check using Network Time Protocol(NTP) passed


User "grid" is not part of "root" group. Check passed
Checking consistency of file "/etc/resolv.conf" across nodes

File "/etc/resolv.conf" does not have both domain and search entries defined
domain entry in file "/etc/resolv.conf" is consistent across nodes
search entry in file "/etc/resolv.conf" is consistent across nodes
All nodes have one search entry defined in file "/etc/resolv.conf"
The DNS response time for an unreachable node is within acceptable limit on all nodes

File "/etc/resolv.conf" is consistent across nodes


Checking GNS integrity...
The GNS subdomain name "vmrac.fits.ro" is a valid domain name
GNS VIP "poc-gns-vip.vmrac.fits.ro" resolves to a valid IP address
PRVF-5229 : GNS VIP is active before Clusterware installation

PRVF-5232 : The GNS subdomain qualified host name "hen.vmrac.fits.ro" was resolved into an IP address

GNS integrity check failed
Pre-check for node addition was unsuccessful on all the nodes.

PRVF-5229 is really a strange error: of course the GNS VIP is active because I already have my RAC installed. It really makes sense when installing a new RAC and the GNS vip sould be unallocated but otherwise I don't get it. So, I decided to go on even the CVU was complaining.

The next step would be to run addNode.sh script from [GI_HOME]/oui/bin location. I ran the script and I found that it does nothing if the CVU checks are not passed. You can figure out this if you run the script with debugging:

[grid@owl bin]$ sh -x ./addNode.sh -silent "CLUSTER_NEW_NODES={hen}"
+ OHOME=/u01/app/11.2.0.2/grid
+ INVPTRLOC=/u01/app/11.2.0.2/grid/oraInst.loc
+ ADDNODE='/u01/app/11.2.0.2/grid/oui/bin/runInstaller -addNode -invPtrLoc /u01/app/11.2.0.2/grid/oraInst.loc ORACLE_HOME=/u01/app/11.2.0.2/grid -silent CLUSTER_NEW_NODES={hen}'
+ '[' '' = Y -o '!' -f /u01/app/11.2.0.2/grid/cv/cvutl/check_nodeadd.pl ']'
+ CHECK_NODEADD='/u01/app/11.2.0.2/grid/perl/bin/perl /u01/app/11.2.0.2/grid/cv/cvutl/check_nodeadd.pl -pre -silent CLUSTER_NEW_NODES={hen}'
+ /u01/app/11.2.0.2/grid/perl/bin/perl /u01/app/11.2.0.2/grid/cv/cvutl/check_nodeadd.pl -pre -silent 'CLUSTER_NEW_NODES={hen}'
+ '[' 1 -eq 0 ']'

As you can see, the check_nodeadd.pl script ends with a non-zero exit code which means error (this perl script is really running the cluvfy utility so, it fails because of the GNS check). The only workaround I found was to ignore this checking using: export IGNORE_PREADDNODE_CHECKS=Y
After that I was able to successfully run addNode.sh script:

[grid@owl bin]$ ./addNode.sh -silent "CLUSTER_NEW_NODES={hen}"
Starting Oracle Universal Installer...

... output truncated ...

Saving inventory on nodes (Friday, December 10, 2010 8:49:27 PM EET)
.                                                               100% Done.
Save inventory complete
WARNING:A new inventory has been created on one or more nodes in this session. However, it has not yet been registered as the central inventory of this system.
To register the new inventory please run the script at '/u01/app/oraInventory/orainstRoot.sh' with root privileges on nodes 'hen'.
If you do not register the inventory, you may not be able to update or patch the products you installed.
The following configuration scripts need to be executed as the "root" user in each cluster node.
/u01/app/oraInventory/orainstRoot.sh #On nodes hen
/u01/app/11.2.0.2/grid/root.sh #On nodes hen
To execute the configuration scripts:
    1. Open a terminal window
    2. Log in as "root"
    3. Run the scripts in each cluster node

The Cluster Node Addition of /u01/app/11.2.0.2/grid was successful.
Please check '/tmp/silentInstall.log' for more details.

Okey, GREAT! Let's run those scripts on the new node:

[root@hen app]# /u01/app/oraInventory/orainstRoot.sh
Creating the Oracle inventory pointer file (/etc/oraInst.loc)
Changing permissions of /u01/app/oraInventory.
Adding read,write permissions for group.
Removing read,write,execute permissions for world.

Changing groupname of /u01/app/oraInventory to oinstall.
The execution of the script is complete.

[root@hen app]# /u01/app/11.2.0.2/grid/root.sh
Running Oracle 11g root script...

The following environment variables are set as:
    ORACLE_OWNER= grid
    ORACLE_HOME=  /u01/app/11.2.0.2/grid

Enter the full pathname of the local bin directory: [/usr/local/bin]: 
The contents of "dbhome" have not changed. No need to overwrite.
The contents of "oraenv" have not changed. No need to overwrite.
The contents of "coraenv" have not changed. No need to overwrite.


Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: /u01/app/11.2.0.2/grid/crs/install/crsconfig_params
Creating trace directory
PROTL-16: Internal Error
Failed to create or upgrade OLR

 Failed to create or upgrade OLR at /u01/app/11.2.0.2/grid/crs/install/crsconfig_lib.pm line 6740.
/u01/app/11.2.0.2/grid/perl/bin/perl -I/u01/app/11.2.0.2/grid/perl/lib -I/u01/app/11.2.0.2/grid/crs/install /u01/app/11.2.0.2/grid/crs/install/rootcrs.pl execution failed

Ups! I did not see that coming! First of all, OLR?! Yea, it's like an OCR but local. The only note I found about this error was 1123453.1 and it advises to double check if all install prerequisites are passed using cluvfy. In my case, the only problem I had was with the GNS check. Does GNS have anything to do with my error? As it turned out, no, it doesn't! The big mistake I made (and the cluvfy didn't notice that) was that the SSH setup between nodes was wrong. Connecting from owl to hen was okey, but not vice-versa. After I fixed the SSH configuration the root.sh script was executed without any problems. Great!

The next step was to clone the database oracle home. That was really easy: just run the addNode.sh in the same way I did for GI. So far so good... at this point I was expecting that little magic to happen. Look what the documentation says:

If you store your policy-managed database on Oracle Automatic Storage Management (Oracle ASM), Oracle Managed Files (OMF) is enabled, and if there is space in a server pool for node2, then crsd adds the Oracle RAC instance to node2 and no further action is necessary. If OMF is not enabled, then you must manually add undo and redo logs.

Hey, that's my case! Unfortunately, the new instance didn't show up. Furthermore, the pool configuration was asking for a new node:

[oracle@hen oracle]$ srvctl config srvpool -g poc
Server pool name: poc
Importance: 10, Min: 2, Max: -1
Candidate server names:

Look, I have increased the importance level and I set the "Min" property to 2. Damn it! I don't know why the new server was not automatically picked up, but maybe is also my leak of experience concerning this new server pools concept. In the end I launched "dbca" from the new added node hoping that some new magic options were added. But, no... even the "Instance Management" option was disabled. But, if you are choosing "Configure database" and next, next, next until the SYSDBA credentials are requested then dbca will try to connect to the local instance and it will actually create this new instance. I'm sure this is not the way it was supposed to work but, at least, I could see some results. However, there was another interesting thing. Looking into the alert of the new created instance I found:

Could not open audit file: /u01/app/oracle/admin/poc/adump/poc_2_ora_18197_1.aud
Retry Iteration No: 1   OS Error: 2
Retry Iteration No: 2   OS Error: 2
Retry Iteration No: 3   OS Error: 2
Retry Iteration No: 4   OS Error: 2
Retry Iteration No: 5   OS Error: 2
OS Audit file could not be created; failing after 5 retries

I didn't create the /u01/app/oracle/admin/poc/adump folder on my new node and that was causing the error. So, this is another thing I should remember... as part of the addNode.sh cloning process the "adump" location is not automatically created.
And, that's all! Now, my fancy RAC has a new baby node.

Upgrade GI to 11.2.0.2: Simply Surprising...

2010-12-08T04:03:00.000-08:00

I never thought I'd write a post about such a trivial task... Well, if you are going to upgrade from 11.2.0.1 to 11.2.0.2 be prepared for surprises.

The first surprise is given by the download page from the oracle support site (formally know as Oracle Metalink). The 11.2.0.2 patch set has 4.8G! WTF?! Furthermore, it is split in 7 pieces... Despite of this huge size, the good thing is that, unlike the previous releases, this patch-set may be used as a self-contained Oracle installer, which means you don't have to install a base 11.2.0.1 release and, after that, to apply the 11.2.0.2 patch-set on top of it, but you may simply install the 11.2.0.2 release directly. There's one more catch: if you want to upgrade just the Grid Infrastructure you don't need all 7 pieces from the patch-set. On the download page is not very clear mentioned but if you have the curiosity to open the README (and you should!) then you'll find the following:

Great! So, for the beginning we'd need the 3rd piece in order to upgrade our Grid Infrastructure.

The second surprise is the fact that the GI cannot be in-place upgraded. In previous releases we used to patch providing an existing home location. Starting with 11.2.0.2 in-place upgrades for GI are not supported. According to the "Upgrade" guide:

As of Oracle Database 11g release 2 (11.2), the Oracle Clusterware software must be upgraded to a new home location in the Oracle grid infrastructure home. Additionally, Oracle ASM and Oracle Clusterware (and Oracle Restart for single-instance databases) must run in the same Oracle grid infrastructure home. When upgrading Oracle Clusterware to release 11.2, OUI automatically calls Oracle ASM Cluster Assistant (ASMCA) to perform the upgrade into the grid infrastructure home.

Okey, good to know! Let's start the upgrade process of GI. The wizard provided by the OUI is quite intuitive therefore I will not bother you with screenshots and other obvious things. However, the next surprise comes when you are running the rootupgrade.sh script. The error is:

Failed to add (property/value):('OLD_OCR_ID/'-1') for checkpoint:ROOTCRS_OLDHOMEINFO.Error code is 256
 The fixes for bug 9413827 are not present in the 11.2.0.1 crs home
 Apply the patches for these bugs in the 11.2.0.1 crs home and then run 
 rootupgrade.sh /oragi/perl/bin/perl -I/oragi/perl/lib -I/oragi/crs/install /oragi/crs/install/rootcrs.pl execution failed

WTF? You cannot patch if you don't have another stupid patch already there. Okey, as an Oracle DBA you have to be a patient guy... take a deep breath and start looking for bug 9413827. First of all there is the 10036834.8 note, which basically says that you might still get this error even if you apply the patch for the 9413827 bug. As an workaround they suggest to also apply the patch for 9655006 bug. That's madness! In the end it turns out that 9655006 patch is actually the July 10.2.0.1.2 PSU. Okey, just download the appropriate version for your platform. Now, another surprise... you need an updated version of OPatch utility. Damn it! Back to metalink, search for patch 6880880 and download the 11.2.0.0.0 version for your platform (Take care not to download the wrong version. By the way, did you noticed that you may download a wget script which can be used to download the patch without using a browser? Yea, finally something good on that shitty flash GUI.) According to the README they suggest to unzip the upgraded OPatch utility directly into your CRS home, using something like:

unzip [p6880880...zip] -d [your GI home]

... which I did!
Now, you have to unzip the PSU patch into an empty folder, let's say /u01/stage, and run the following command as root:

/OPatch/opatch auto /u01/stage/ -och [your GI home]

In my case, the output was:

Executing /usr/bin/perl /u01/app/11.2.0.1/grid/OPatch/crs/patch112.pl -patchdir /u01 -patchn stage -och /u01/app/11.2.0.1/grid/ -paramfile /u01/app/11.2.0.1/grid/crs/install/crsconfig_params
2010-12-08 12:32:19: Parsing the host name
2010-12-08 12:32:19: Checking for super user privileges
2010-12-08 12:32:19: User has super user privileges
Using configuration parameter file: /u01/app/11.2.0.1/grid/crs/install/crsconfig_params
The opatch Component check failed. This patch is not applicable for /u01/app/11.2.0.1/grid/
The opatch Component check failed. This patch is not applicable for /u01/app/11.2.0.1/grid/
Patch Component/Conflict  check failed for /u01/app/11.2.0.1/grid/

Upssy! Another surprise! This patch is not applicable for bla bla bla? Are you serious? Let's check the logs. They should be in your $CRS_HOME/cfgtoollogs. Search for a log file named as opatchauto[timestamp].log. The important part for the log:

2010-12-08 12:32:19: The component check failed with following error
2010-12-08 12:32:19: bash: /u01/app/11.2.0.1/grid/OPatch/opatch: Permission denied

Ha? I'm root! Aaaa... okey! Apparently it tries to run the OPatch tool under the grid user. Okey, let's fix the permissions.

chown root:oinstall /u01/app/11.2.0.1/grid/OPatch -R
chmod g+r /u01/app/11.2.0.1/grid/OPatch/opatch

Now, try again! Yeap... now it's working.
After applying the patch we are ready for our rootupgrade.sh. It's interesting that the output still contains the Failed to add (property/value):('OLD_OCR_ID/'-1') message but the upgrade continues without any other complaints. Okey, let's perform a quick check:

srvctl config asm
ASM home: /u01/app/11.2.0.2/grid
ASM listener: LISTENER

srvctl config listener -a
Name: LISTENER
Network: 1, Owner: grid
Home: 
  /u01/app/11.2.0.2/grid on node(s) owl
End points: TCP:1521

Great, the ASM and listeners are relocated to the new GI home. The next logical thing to do is to uninstall the old GI home, right? It's as simple as:

/deinstall/deinstall

Oookey, meet SURPRISE Number 6:

ERROR: You must delete or downgrade the Oracle RAC databases and de-install the Oracle RAC homes before attempting to remove the Oracle Clusterware homes.

Isn't it great? On metalink I found Bug 10332736 and, on the WORKAROUND section, it says something about writing a note with a manual uninstall procedure. However, at the time of writing this, the note wasn't published yet. Yea... all I can say is that I'm tired of these stupid issues. What happend with the Oracle testing department? They encourage to patch frequently but, as far as I'm concerned, I always have this creepy feeling before doing it.

My first 11gR2 RAC on VirtualBox - Learned Lessons

2010-11-28T15:05:00.000-08:00

Oracle 11gR2 comes with many RAC goodies and, of course, I want to see them in action. But, the first step is to actually have a 11gR2 available. So, I decided to install one on my poor Dell PowerEdge 1850 server. As it turned out, even the installation process is changed with the introduction of the SCAN, GNS, server pools etc. However it makes this task more challenging, doesn't it?

Because this RAC was not intended to be used for any productive purposes my first choice was for a virtualized environment. I tried Oracle VM in the first place and I was quite disappointed about the results:

1. my virtual machines were reboting unexpectedly, even they were idle. I didn't managed to find the cause of this.

2. during heavy loads on my virtual machines I was constantly getting:

INFO: task kjournal:337 blocked for more than 120 seconds.
"echo 0 > /proc/sys/kernel/hung_task_timeout_sec" disables this message.

I guess Oracle already fixed that but I don't have any ULN subscriptions, so no updates.

My next option was VirtualBox which is a nice choice and it is also provided by Oracle. VirtualBox supports now shared disks which makes it a very appealing solution for RAC testing. In addition, there's also a well written guide about how to install a RAC on VirtualBox here.

To summarize, below are the main lessons I learned out of this RAC installation process:

1. High CPU load for my virtual hosts: after I created the hosts which were supposed to act as RAC nodes, I noticed that the CPUs on the host server was on 100% even the guests were idle. My host server has 8G RAM and 2 physical CPU on 3.4GHz, so this high CPU consumption didn't feel right at all. The solution was to boot my virtual hosts with the divider=10 option. Even with this tweak the whole installation process was slow, so be prepared to wait...

2. pay attention to the groups membership for the oracle/grid users: I made a stupid mistake and I forgot to add the oracle user to asmdba group. The prerequisites checks didn't complained and I successfully installed the Grid Infrastructure and the Oracle database software. However, when I reached the database installation phase using dbca I noticed that no asm diskgroups were available even they were accessible on my "grid" user. So, in order to save precious time for debugging such tricky issues double check these groups membership requirements.

3. the time synchronization issue: because I wanted to use new stuff for my RAC I decided to get rid of the ntpd synchronization and to use the Oracle CTSSD implementation. However, be careful here. Oracle is peaky when it comes to detecting if other synchronization software is installed. Even your ntpd daemon is stopped you also have to remove/rename the /etc/ntpd.conf file. Otherwise, the time synchronization check will fail. And another thing: if you configure your NIC interfaces via DHCP you may end up having this /etc/ntpd.conf after every node reboot. In order to prevent this you may use static address initialization or you may add PEERNTPD=no to your ifcfg-ethX scripts.

4. GNS preparations: this GNS (Grid Naming Service) component is new in 11gR2 and is not a very tasty concept for those DBAs (like me) who do not have a lot of knowledge in network administration field. So, if you are going to use GNS, be sure you have an experienced system administrator around, to provide you support for configuring it. However, you still need to know what to ask him to do. Basically, you have to agree on a new DNS zone. If your company domain is gigel.ro you may choose for your RAC rac.gigel.ro. Then, you need to ask him to delegate the requests form *.rac.gigel.ro to an unallocated IP address from the same IP class as your future RAC public interface. This IP is the VIP for your GNS and it will be available only when your RAC installation is successfully finished. Then, your system administrator will ask you under which name to "glue" the new rac.gigel.ro zone. He actually want to know under which DNS name to register this GNS vip address. The glue is really a well-known concept in the DNS terminology. As far as I noticed Oracle uses <cluster_name>-gns-vip.<gns_zone>. So, for our hypothetical example, assuming the rac name is "muci", the gns glue would be: muci-gns-vip.rac.gigel.ro.

5. ORA-15081: I think this has to do with the membership mistake. DBCA was reporting ORA-15081, complaining that it cannot create stuff into ASM diskgroups. The metalink note 1084186.1 provides the solution.

Okey, that would be all. Happy (but slow) RAC on VirtualBox.

SHARED remote_login_password_file

2010-10-31T13:14:00.000-07:00

When talking about the shared option of the remote_login_password_file parameter, the official 11.2 documentation states:

One or more databases can use the password file. The password file can contain SYS as well as non-SYS users.

Whiles that's true, it is important to mention that, as soon as you set this parameter on SHARED, you are not allowed to add more SYSDBA users nor to change their passwords. A shared password file may contain non-SYS users, only if they were previously granted SYSDBA privilege, at the time the password file was in exclusive mode.

Statistics on Client Result Cache

2010-09-22T06:54:00.000-07:00

I've just noticed that the result cache client statistics are not very accurate on my 11.2.0.1 Oracle server. I have the following java code:


package test;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import oracle.jdbc.pool.OracleDataSource;

public class ClientResultCache {

 public static void main(String[] args) throws SQLException, IOException, InterruptedException {
  OracleDataSource ods = new OracleDataSource();
  ods.setDriverType("oci");
  ods.setTNSEntryName("owldb");
  ods.setUser("talek");
  ods.setPassword("muci");
  Connection conn = ods.getConnection();
  String  query  = "select /*+ result_cache */ * from xxx";
  ((oracle.jdbc.OracleConnection)conn).setImplicitCachingEnabled(true);
     ((oracle.jdbc.OracleConnection)conn).setStatementCacheSize(10);
  PreparedStatement  pstmt;
  ResultSet rs;
  for (int j = 0 ; j < 1000 ; j++ ) {
   System.out.println(j);
   pstmt  = conn.prepareStatement (query);
      rs  = pstmt.executeQuery();
      while (rs.next( ) ) {
      }
      rs.close();
      pstmt.close();
      Thread.sleep(100);
  } 
  System.in.read();
 }

}

While the above code is running I'm monitoring the CLIENT_RESULT_CACHE_STATS$. And this is what I've got:

   
STAT_ID NAME                                VALUE
---------- ------------------------------ ----------
         1 Block Size                            256
         2 Block Count Max                       128
         3 Block Count Current                   128
         4 Hash Bucket Count                    1024
         5 Create Count Success                    1
         6 Create Count Failure                    0
         7 Find Count                            812
         8 Invalidation Count                      0
         9 Delete Count Invalid                    0
        10 Delete Count Valid                      0

The "Find Count" should be 999, right? My test program is still running (see the System.in.read at the end) therefore I expect my client result cache to be still there. My first guess was a delay in computing the statistics but even after 15 minutes of waiting I didn't get the right figures. Hmm... am I miss something?

Oracle IDE for Geeks

2010-07-15T06:56:00.000-07:00

Let's be honest guys... how many times you find yourself googeling for “the best oracle IDE”? If you are like me then the answer is “too many times”... Why this? Well, partly I guess because we are not satisfied with what the market offers us in this area.
If we're going to take a look at what we have now, the most well known Oracle IDEs are:

Toad from Quest Software
PLSQL Developer offered by Allround Automations
SQL Developer from Oracle

If you ask me, my favorite choice would be PLSQL Developer. It has a lot of cool features on a fair price. Toad is also nice but it's expensive. As far as SQL Developer is concerned I simply can't get used with it... In other words, I don't like it: it's slow (maybe Java has something to do with this) and it uses Java Swing for its GUI, bypassing my OS look & feel. However, there are some pros which might count: it's free and it's cross-platform. In fact, if you are on a Unix based OS you don't have many choices but SQL Developer. Of course, there is/was Tora... but I would rather mention it on past tense.
So, what I don't like about these tools? Let's see:

They are heavy... some of them take a lot of time just to startup.
Most of them are not cross platform.
They are closed software. You don't have access to the code.
Limited editing features. I know they offer templates, auto-complete and stuff, but they look so small in comparison with what VIM provides.
They are not suitable for server environments. I mean... what if you have to connect to the database on a remote Unix server, connected via ssh within a "friendly" console? I guess sqlplus is all you have there and it's not a very pleasant experience.
A lot of the so useful sqlplus commands doesn't work in these environments. PLSQL Developer does a good job emulating many of these commands but I still miss AUTOTRACE, sub-totals and all the other cool features sqlplus provides.

So, taking into consideration the above limitations I decided to create (why not?) my own Oracle IDE. It may sound stupid or too ambitious, but is not (well, maybe ambitious is). The new Oracle IDE I'm working on is called VoraX and is hosted on GoogleCode. VoraX stands for Vim ORAcle eXtenstion and yes... you have right: it's a VIM plugin. If you are a big fan of the VIM editor then you have to give VoraX a try. Of course there is also dbext plugin which provides support for Oracle databases but the main problem with it is that it doesn't maintain a persistent connection to the database. In dbext, when you are going to execute something, sqlplus is launched, a new connection is done, the statement is handed over, the results are fetched into VIM and then, sqlplus is closed. This cycle is restarted on every statement execution which is quite expensive, slow and does not preserve your transactional context. Anyway, dbext is a tool designed for many databases, not just Oracle and, personally, I don't like generic database tools.

SqlPlus Injection

2010-05-26T03:29:00.000-07:00

Despite that at the very first sight it might look stupid you may be hacked by a colleague in a very rude way. Suppose one developer asks you to create a new user for an upcoming system. Because he's a nice guy, he also hands you a simple script which creates this user along with all the required grants. Of course, even you like your colleague and appreciate his effort, you carefully inspect that script before running it. Let's see a preview of this script in a plain vim window:

Oookey! The script has nice comments, nothing unusual... You run it in your sqlplus SYS session and... BANG! your SYSTEM user is compromised and you'll even don't know that. If you still have the WTF face, then look again.
The catch is in the last comment. We used to think that in sqlplus a multiline comment start with an /* (and because sqlplus is quite picky it has to be further followed by a space or CR) and then, everything till the closing */ is taken as a comment. This assumption is wrong because, in sqlplus, a # at the very beginning of a line means "execute the command on that line". In fact, it doesn't have to be # but this is the symbol configured by default for sqlprefix setting. Just check it out:

SQL> show sqlprefix
sqlprefix "#" (hex 23)

However, we are simply fooled by our editor which, with its nice code highlighting feature, just marked our comments accordingly. Of course, it doesn't know anything about the sqlplus "sqlprefix" setting. So, before running any third-party scripts you should carefully look at them, even at comments.

Autobackup CF with Flash Recovery Area

2010-05-02T23:22:00.000-07:00

In our office we have a 10g RAC database. It has a flash recovery area enabled, which points to an ASM disk. Nothing special I would say... However, from time to time, our nightly backup script simply fails complaining that it can't find some obsolete backups which should be deleted:


RMAN-06207: WARNING: 4 objects could not be deleted for DISK channel(s) due
RMAN-06208:          to mismatched status.  Use CROSSCHECK command to fix status
RMAN-06210: List of Mismatched objects
RMAN-06211: ==========================
RMAN-06212:   Object Type   Filename/Handle
RMAN-06213: --------------- ---------------------------------------------------
RMAN-06214: Backup Piece    /u01/app/oracle/product/10.2.0/db_1/dbs/c-24173594-20100427-00
RMAN-06214: Backup Piece    /u01/app/oracle/product/10.2.0/db_1/dbs/c-24173594-20100427-01
RMAN-06214: Backup Piece    /u01/app/oracle/product/10.2.0/db_1/dbs/c-24173594-20100428-00
RMAN-06214: Backup Piece    /u01/app/oracle/product/10.2.0/db_1/dbs/c-24173594-20100428-01

That's weird! All those backup pieces are controlfile autobackups. RMAN looks for them into a local filesystem and, being a RAC database, those files are accessible, obvious, just from one node. But how? They were supposed to be placed into our shared storage, in FRA, to be more precise. Well, let's look once again to our settings:


SQL> show parameter recov

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest       string  +DG1
db_recovery_file_dest_size      big integer 150000M
recovery_parallelism       integer  0

Okey, it's clear we have a FRA! What about RMAN settings?


RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO COMPRESSED BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/10.2.0/db_1/dbs/snapcf_fd1.f'; # default

It looks good... the autobackup format for controlfile is '%F' which is the default one, right? The documentation proves that:

The default location for the autobackup on disk is the flash recovery area (if configured) or a platform-specific location (if not configured). RMAN automatically backs up the current control file using the default format of %F.

Okey, we have a flash recovery area and a %F default autobackup format... WTF? Well, the answer is given by the 338483.1 metalink note. Apparently, there is a big difference between having the autobackup format set on its default value and having it reset to its default... Interesting, ha? It is... So, if you set (explicitly) the autobackup format to %F, the autobackup file will go to a OS specific location, which on Linux is $?/dbs. But if you have the autobackup format on its default (explicitly reset it, or never set it at all) and you have a FRA configured then that autobackup file will actually go to FRA.
So, in my case the solution was simple (please notice the "# default" marker):


RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR; 

old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F';
RMAN configuration parameters are successfully reset to default value

RMAN> show controlfile autobackup format;

RMAN configuration parameters are:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

Ooookey, really really unintuitive... I think the Oracle documentation should be more precise regarding this.

When having a rman retention policy based on REDUNDANCY is a bad idea...

2010-03-19T06:53:00.000-07:00

Suppose you have a RMAN retention policy of "REDUNDANCY 2". This means that as long as you have at least two backups of the same datafile, controlfile/spfile or archivelog the other older backups become obsolete and RMAN is allowed to safely remove them.

Now, let's also suppose that every night you backup your database using the following script:

CONFIGURE CONTROLFILE AUTOBACKUP ON;
rman {
   backup database plus archivelog;
   delete noprompt obsolete redundancy 2;
}

The backup task is quite simple: first of all it ensures that we have the controlfile autobackup feature on, then it backups the database and archives and, at the end, it deletes all obsolete backups using the REDUNDANCY 2 retention policy.
Using the above approach you might think that you can restore your database as it was two days ago, right? For example, if you have a backup taken on Monday and another one taken on Tuesday you may restore your database as it was within the (Monday_last_backup - Today) time interval. Well, that's wrong!

Consider the following scenario:
1. On Monday night you backup the database using the above script;
2. On Tuesday, during the day, you drop a tablespace. Because this is a structural database change a controlfile autobackup will be triggered. Ieeei, you have a new controlfile backup.
3. On Tuesday night you backup again the database... nothing unusual, right?

Well, the tricky part is regarding the DELETE OBSOLETE command. When the backup script will run this command, RMAN finds out three controlfile backups: one is originating from the Monday backup, one is from the structural change and the third is from our just finished Tuesday backup database command. Now according to the retention policy of "REDUNDANCY 2", RMAN will assume that it is safe to delete the backup of the controlfile taken on Monday night backup because it's out of our retention policy and because this backup is the oldest one. Uuups... this means that we gonna have a big problem restoring the database as it was before our structural change because we don't have a controlfile backup from that time.

So, if you intend to incomplete recover your database to a previous time in the past it's really a good idea to switch to a retention policy based on a "RECOVERY WINDOW" instead. In our case a RECOVERY WINDOW OF 2 DAYS would be more appropriate.

PLSQL "All or Nothing" Pitfall

2010-02-28T11:54:00.000-08:00

Transactions are such a common thing when working with databases. They act on an "all or nothing" basis, that is, they succeed or fail but they always should let the database into a consistent state. Of course, in Oracle databases the rules are the same, but the interesting part I want to refer to is in connection with PL/SQL modules (procedures, functions or packages).

A PL/SQL module is some kind of "all or nothing" component. If the procedure fails it rollbacks the uncommited work it has done. Suppose we have the following procedure:

CREATE OR REPLACE PROCEDURE test AS 
BEGIN
  insert into yyy values (1);
  raise_application_error(-20000, 'I am a cute error!');
END test;

Let's see what happens:

SQL> truncate table yyy;

Table truncated.

SQL> exec test;
BEGIN test; END;

*
ERROR at line 1:
ORA-20000: I am a cute error!
ORA-06512: at "TALEK.TEST", line 4
ORA-06512: at line 1


SQL> select * from yyy;

no rows selected

Nice... we didn't explicitly rollback, but Oracle was smart enough to do the cleanup job for us. This makes sense and proves that PLSQL modules are, in a way, "all or nothing" components.

Now, let's say we have an oracle job which calls our "test" procedure and if an error occurs it has to log it into another table. A possible implementation of the job PLSQL caller block may be:

begin
  test;
exception
  when others then
    insert into log values (dbms_utility.format_error_stack);
    commit;
    raise;
end;
/

The above code may seem harmless: the test procedure is called and if it raises an error the exception part of the PL/SQL caller block is executed which further inserts the error into our log table. Of course, we commit the log entry we just inserted and we re-raise the originating error. We know that if test procedure fails then it rollbacks its uncommited work as we seen above. After all, it's an "all or nothing" piece, right? Well, here's the pitfall: if you catch the exception then the procedure which raised the error will not clean up anything as long as you are within the EXCEPTION section. Even the whole anonymous block will fail because of re-raising the original error, the COMMIT statement from the EXCEPTION section will actually commit the incomplete work done by our "TEST" procedure. So, in most cases you should look twice to such EXCEPTION WHEN THEN ... COMMIT definitions... otherwise you may end up with nasty bugs. In the above example, in order to avoid this problem, a ROLLBACK should be performed before logging the error. Of course, there are smarter logging solutions which use autonomous transactions but, anyway, the goal was just to reveal the pitfall.

INS-32018 Warning for Standalone Server

2010-02-24T02:12:00.000-08:00

When it comes to installing Oracle you should always follow the procedures written into the installation guides. As you already know, Oracle 11.2 packages ASM within a new separate component called Oracle Grid Infrastructure. So, if you want to install the database files into ASM then you must install Grid Infrastructure. As a good practice, Oracle recommends to install it under a different user, typically named "grid".
As far as the OFA directories structure is concerned the installation guide recommends:

to create an "/u01/app/grid" directory to be used as an ORACLE_BASE for this "grid" user;

to create an "/u01/app/11.2.0/grid" directory to be used as an ORACLE_HOME for this "grid" user.

If you're like me, the above configuration looks a little bit weird because I used to think that the ORACLE_HOME should be somewhere under the ORACLE_BASE directory. Nevertheless, the documentation clearly states the following:

Caution:

For grid infrastructure for a cluster installations, the Grid home must not be placed under one of the Oracle base directories, or under Oracle home directories of Oracle Database installation owners, or in the home directory of an installation owner. During installation, ownership of the path to the Grid home is changed to root. This change causes permission errors for other installations.

However, the above applies just to cluster installations. If you just want ASM installed for a single instance database then it's fine (and recommended) to place the ORACLE_HOME under the ORACLE_BASE. If not doing so, you'll get the following warning:

So, to sum up the above ideas, remember that if you are going to install a RAC then you need to create the grid ORACLE_HOME out of the ORACLE_BASE of any oracle software owner. If you choose to install the Oracle Grid Infrastructure for a standalone server then the ORACLE_HOME of the grid user should be under its ORACLE_BASE.

ALL_TABLES versus ALL_ALL_TABLES

2010-02-17T00:47:00.000-08:00

If you ever wondered what's the difference between ALL_TABLES and ALL_ALL_TABLES then here's the answer: both views provide all tables to which the current user has access to but, in addition to the tables returned by ALL_TABLES, the ALL_ALL_TABLES will also return all object tables (system generated or not) accessible by the current user.

Pay attention that this may be an interview question (e.g. how can you get all tables you have access to?) and you may leave a good impression if you respond with another question: "Do you also want object tables to be included?". :)

A DDL statement may fire a DML trigger

2009-12-16T12:05:00.000-08:00

Maybe you know this, maybe you don't. Because it's not quite obvious it deserves a little attention. We all know about DML triggers. Remember? Yea, yea... the before/after insert/update/delete each row triggers. We use to think that the INSERT, UPDATE or DELETE statements fire the corresponding triggers (of course, if any are defined). That's true with one (as far as I know) important note: a DDL statement which adds a new column with a default value will also fire the UPDATE trigger.

For example, let's create a dummy table:

SQL> create table muc  (col1 integer primary key, modify_date timestamp);

Table created.

Then, the corresponding trigger:

SQL> create or replace trigger trg_muc_mod_dt before update on muc for each row
2  begin
3  :new.modify_date := systimestamp;
4  end;
5  /

Add some records:

SQL> insert into muc values (1, systimestamp);

1 row created.

SQL> insert into muc values (2, systimestamp);

1 row created.

SQL> commit;

We end up having:

SQL> select * from muc;

    COL1 MODIFY_DATE
---------- ------------------------------
       1 16-DEC-09 09.54.03.804223 PM
       2 16-DEC-09 09.54.41.815575 PM

Now, the moment of truth:

SQL> alter table muc add (active integer default '0');

Table altered.

SQL> select * from muc;

    COL1 MODIFY_DATE                        ACTIVE
---------- ------------------------------ ----------
       1 16-DEC-09 09.55.53.836113 PM            0
       2 16-DEC-09 09.55.53.840896 PM            0

Take a look at the MODIFY_DATE and see the new timestamp. The update trigger was invoked in response to our DDL statement. This is important to know. Think to a deposit table which has a column named LAST_UPATED and a trigger which updates it whenever something within a deposit changes. Now, suppose the business logic dictates that a new column must be added with a default value. You run the DDL statement to add that column and... suddenly, all information regarding when a particular deposit was last upated is lost. Ups. So, I should write down one hundred times: "Think twice before adding new columns with default values on a table with UPDATE triggers".

Strange RMAN snapshot controlfile issue

2009-11-29T11:19:00.000-08:00

A strange thing happen today. I executed a delete obsolete command on my RMAN prompt and it reported the snapshot controlfile as obsolete. I don't know under which circumstances this problem occurs and I couldn't find any relevant information on forums or metalink (oh! sorry "my oracle support") about this.

Below is the output of the DELETE OBSOLETE command:

RMAN> delete obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
using channel ORA_DISK_1
using channel ORA_DISK_2
Deleting the following obsolete backups and copies:
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Control File Copy     36     29-11-2009 12:35:33 /u01/app/oracle/product/11.2.0/
                                                 dbhome_1/dbs/snapcf_tetris.f

Do you really want to delete the above objects (enter YES or NO)? y
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of delete command on ORA_DISK_2 channel at 11/29/2009 21:11:16
ORA-19606: Cannot copy or restore to snapshot control file

Indeed, this is the default configured snapshot controlfile:

RMAN> show snapshot controlfile name;               

RMAN configuration parameters for database with db_unique_name TETRIS are:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/
dbhome_1/dbs/snapcf_tetris.f';

It seems I'm in a kind of deadlock here. The snapshot controlfile is reported as obsolete but it can't be deleted as it is used by RMAN. The only solution I found was to change the RMAN configuration to use another snapshot controlfile, to remove then the reported obsolete one and to switch back to the default. However, the question remains: why the snapshot controlfile is reported as obsolete?

PS: This happend on a 11gR2 database installed under a Linux x86 platform.

Update: Apparently this is encountered after executing a DUPLICATE database from ACTIVE DATABASE. Furthermore, the snapshot controlfile is reported as a "datafile copy" when a CROSSCHECK is suggested. See below:

RMAN> delete obsolete;                                                                                                                                                                                           

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
using channel ORA_DISK_1
using channel ORA_DISK_2
Deleting the following obsolete backups and copies:
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Control File Copy     40     30-11-2009 18:41:15 /u01/app/oracle/product/11.2.0/dbhome_1
                                                 /dbs/snapcf_tetris.f

Do you really want to delete the above objects (enter YES or NO)? y                                                                                                                                              

RMAN-06207: WARNING: 1 objects could not be deleted for DISK channel(s) due
RMAN-06208:          to mismatched status.  Use CROSSCHECK command to fix status
RMAN-06210: List of Mismatched objects
RMAN-06211: ==========================
RMAN-06212:   Object Type   Filename/Handle
RMAN-06213: --------------- ---------------------------------------------------
RMAN-06214: Datafile Copy   /u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_tetris.f

Obviously, that can't be a datafile copy. So, let's try a crosscheck as suggested:

RMAN> crosscheck datafilecopy '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_tetris.f';                                                                                                                     

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=148 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=140 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of crosscheck command at 11/30/2009 19:09:43
RMAN-20230: datafile copy not found in the repository
RMAN-06015: error while looking up datafile copy name: /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/snapcf_tetris.f

Okey, this was expected as I don't have any datafilecopy with that name despite of what RMAN says. So, let's try a crosscheck for the controlfile copy:

RMAN> crosscheck controlfilecopy '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_tetris.f';                                                                                                                  

released channel: ORA_DISK_1
released channel: ORA_DISK_2
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=148 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=140 device type=DISK
validation failed for control file copy
control file copy file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_tetris.f 
RECID=40 STAMP=704313675
Crosschecked 1 objects

As it can be seen the validation fails, although the file exists on that location:

$ ls -al /u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_tetris.f
-rw-r----- 1 oracle oinstall 10436608 Nov 30 18:57 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_tetris.f

I don't know if this is documented somewhere but it looks to me like a bug. No idea why the snapshot control file is messed up after a DUPLICATE TARGET DATABASE ... FROM ACTIVE DATABASE.

TSPITR to recover a dropped tablespace

2009-11-27T13:08:00.000-08:00

A nice feature of Oracle 11gR2 is the ability to recover a dropped tablespace using TSPITR. Of course, in order to succeed this, you need valid backups. Let's test this! First of all, just to be on the safe side, take a fresh backup of the database:

BACKUP DATABASE PLUS ARCHIVELOG;

Then supposing you have a "MUCI" tablespace, simply drop it:

drop tablespace MUCI including contents;

Let's try to recover "MUCI" tablespace. You'll need the nearest timestamp or SCN before the tablespace was dropped.

If you are tempted to use fully automatic TSPITR then be prepared for troubles. This is what happen to me when I tried it:

RMAN> recover tablespace muci until scn 2240386 auxiliary destination '/u01/app/backup';

...

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/27/2009 21:57:13
RMAN-06965: Datapump job has stopped
RMAN-06961:    IMPDP> Job "SYS"."TSPITR_IMP_hilc" stopped due to fatal error at 21:57:09
RMAN-06961:    IMPDP> ORA-39123: Data Pump transportable tablespace job aborted
ORA-01565: error in identifying file '/u01/app/oracle/oradata/TETRIS/datafile/o1_mf_muci_5k0bwdmb_.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

I google it and found this post which recommends to drop the tablespace without "AND DATAFILES" but, as far as I'm concerned, it didn't work.
Nevertheless, setting a new name for the datafile which belongs to the dropped datafile did the job.

RMAN> run {
2> set newname for datafile 6 to new;
3> recover tablespace muci until scn 2240386 auxiliary destination '/u01/app/backup';
4> }

A direct consequence of this in 11gR2 is that you can apply multiple TSPITR for the same tablespace without using a recovery catalog. If you chosen a wrong SCN and you already brought the recovered tablespace ONLINE then you can simply drop it and try again with another SCN.

Awesome!

Annoying Tablespaces Quotas

2009-11-27T11:02:00.000-08:00

There's one thing about tablespace quotas which I really don't like. If I allocate quota on a tablespace to a user and then I drop that tablespace the quota is not automatically revoked. It still can be seen in DBA_TS_QUOTAS view but with the DROPPED column set as YES. However, if i create afterwards a tablespace with the same name as the one previously dropped the old quota is auto-magically reactivated on this new tablespace which might not be my intention. Let's see it in action:

1. first of all, let's create a dummy tablespace:

SQL> create tablespace test_tbs datafile size 20M;                                                                               

Tablespace created.

2. let's also create a user and grant quota on the TEST_TBS tablespace:

SQL> create user gogu identified by xxx quota unlimited on users;                                                                

User created.

SQL> alter user gogu quota unlimited on test_tbs;                                                                                

User altered.

3. take a look at quotas:

SQL> select * from dba_ts_quotas where username='GOGU';                                                                          

TABLESPACE_NAME USERNAME             BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO
--------------- --------------- ---------- ---------- ---------- ---------- ---
USERS           GOGU                     0         -1          0         -1 NO
TEST_TBS        GOGU                     0         -1          0         -1 NO

4. now drop the TEST_TBS tablespace and look again at quotas:

SQL> drop tablespace test_tbs including contents and datafiles;                                                                  

Tablespace dropped.

SQL> select * from dba_ts_quotas where username='GOGU';                                                                          

TABLESPACE_NAME USERNAME             BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO
--------------- --------------- ---------- ---------- ---------- ---------- ---
USERS           GOGU                     0         -1          0         -1 NO
TEST_TBS        GOGU                     0         -1          0         -1 YES

Just notice that the DROPPED column is now set to YES for the TEST_TBS tablespace. This I don't like and if I want to revoke the quota oracle complains that it doesn't know anything about the TEST_TBS tablespace.

SQL> alter user gogu quota 0 on test_tbs;                                                                                        
alter user gogu quota 0 on test_tbs
*
ERROR at line 1:
ORA-00959: tablespace 'TEST_TBS' does not exist

Obvious, but then why preserving that quota in DBA_TS_QUOTAS anyway?

5. Let's recreate the TEST_TBS tablespace and then look at quotas:

SQL> create tablespace test_tbs datafile size 20M;                                                                               

Tablespace created.

SQL> select * from dba_ts_quotas where username='GOGU';                                                                          

TABLESPACE_NAME USERNAME             BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO
--------------- --------------- ---------- ---------- ---------- ---------- ---
USERS           GOGU                     0         -1          0         -1 NO
TEST_TBS        GOGU                     0         -1          0         -1 NO

See how the "DROPPED" column is now back on "NO". But wait... this TEST_TBS tablespace is a new tablespace which just happen to be named like an old dropped tbs. Bleah... ugly!

So, this boils down to the conclusion that when you are about to drop a tablespace is a good thing to check the quotas allocated to users and to revoke them before dropping the tablespace. Otherwise they will remain in DBA_TS_QUOTAS and they'll be reactivated when a tablespace with the same name is created. Furthermore, I don't know how you can get rid of them if the tablespace no longer exists. Of course, you can create a dummy tablespace with the same name, revoke quotas and after that to drop the dummy tablespace. But this is an awful workaround.

Update: Yet, I see an advantage of the above behaviour. In 11gR2 you can recover a dropped tablespace with TSPITR. After the TSPITR successfully completes and the dropped tablespace is recovered, the old quotas are also reactivated which is a good thing for the users who had objects in that tablespace.

Do archivelogs become obsolete if they contain blocks from an BEGIN BACKUP operation?

2009-11-18T00:54:00.000-08:00

Of course, not every possible case is described within the docs therefore some of them have to be simply tried. So, today I was wondering what would happen if I leave a tablespace in BEGIN BACKUP mode and I will continue to backup the database using:

RUN {
  BACKUP DATABASE PLUS ARCHIVELOG; 
  DELETE NOPROMPT OBSOLETE.
}

As you already know, if a tablespace is put in BEGIN BACKUP mode then all subsequent changes will force the dirty blocks to be written into the redologs which will be eventually archived. My main concern here was regarding the DELETE OBSOLETE command. Is RMAN smart enough to know that those archives are not going to become obsolete as long as the BEGIN BACKUP status is in place? After some tests I can conclude: RMAN knows this and will NOT consider those archives as obsolete. This was kind of obvious but, you know... it's always good to try and to see by your own eyes.

High Availability Guaranteed Restore Points

2009-11-15T03:43:00.000-08:00

I like the flashback database feature introduced by Oracle 10g and especially the guaranteed restore points. We intended to use it on our 10g production database to create guaranteed restore points before major upgrades but without enabling flashback logging because we wanted to affect at least as possible the whole database performance. What really bothered me at the time was the fact that I couldn't create my first guaranteed restore point if the database was open which, in my opinion, affects the high availability goal. The solution to this was to already have at least one guaranteed restore point before creating the next ones with the database open, but this always felt like an ugly workaround.
So, the question is: what do you choose: the overhead of always having the flashback logging enabled or the downtime produced by the creation of a guaranteed restore point? Hmmm...
As many Oracle new features they seem to be a little bit unpolished when just launched but they tend to become better an better. The same here. I've just had a nice surprise to see that in 11gR2 (I don't know if it's also in R1) you can create guaranteed restore points with the database open, without flashback logging enabled and without any previous guaranteed restore points:


SQL> select name from v$restore_point;                                                        

no rows selected

SQL> select flashback_on from v$database;                                                     

FLASHBACK_ON
------------------
NO

SQL> select status from v$instance;                                                           

STATUS
------------
OPEN

SQL> create restore point before_upgrade guarantee flashback database;                        

Restore point created.

SQL> select name from v$restore_point;                                                        

NAME
---------------------------------------------------
BEFORE_UPGRADE

Nice and good to know.

RMAN Retention Policy with Corrupted Backups

2009-11-01T11:07:00.000-08:00

I always assumed that RMAN is smart enough to take care of my database obsolete backups. I give it the retention policy and it's done: whenever I invoke the DELETE OBSOLETE command rman will identify those backups out of the scope of my retention policy and will safely delete them. Nevertheless, there is at least one big exception: when the taken backup is corrupted.

The following is quite self explanatory. Lets assume we have a retention policy of redundancy 1 and we take a new backup of the database.

RMAN> backup database;

Starting backup at 01-11-2009 11:20:53
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set

...
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:26
Finished backup at 01-11-2009 11:22:20

Now, we have two backups and, according to the configured retention policy, the previous one becomes obsolete. However, let's suppose that the backup we just taken is corrupted. We can simulate this using dd (we're zeroing 1MB somewhere in between):

dd if=/dev/zero of=o1_mf_nnndf_TAG20091101T232053_5gvyxpwt_.bkp bs=1M seek=10 count=1

Okey! As a good practice it's nice to validate the backup using the "RESTORE VALIDATE BACKUP" so let's do it:

RMAN> restore validate database;

Starting restore at 01-11-2009 11:30:10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=42 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=37 device type=DISK

channel ORA_DISK_1: starting validation of datafile backup set
channel ORA_DISK_2: starting validation of datafile backup set

...

ORA-19599: block number 1280 is corrupt in backup piece 
/opt/oracle/app/oracle/flash_recovery_area
/VENUSDB/backupset/2009_11_01/o1_mf_nnndf_TAG20091101T232053_5gvyxpwt_.bkp

channel ORA_DISK_2: piece handle=/opt/oracle/app/oracle/flash_recovery_area
/VENUSDB/backupset/2009_11_01/o1_mf_nnndf_TAG20091101T232053_5gvyxp3o_.bkp 
tag=TAG20091101T232053
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: validation complete, elapsed time: 00:00:35
failover to previous backup

...
Finished restore at 01-11-2009 11:31:13

As you can see the BACKUP VALIDATE worked as expected. It identified the corrupted backupset and failed over to the previous valid one. However, what if at the end of the backup script there's a "delete noprompt obsolete" command?

RMAN> delete noprompt obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
using channel ORA_DISK_1
using channel ORA_DISK_2
Deleting the following obsolete backups and copies:
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Archive Log          2      01-11-2009 10:40:27 /opt/oracle/app/oracle/flash_recovery_area
/VENUSDB/archivelog/2009_11_01/o1_mf_1_6_5gvwkv55_.arc
Backup Set           10     01-11-2009 11:19:57
  Backup Piece       10     01-11-2009 11:19:57 /opt/oracle/app/oracle/flash_recovery_area
/VENUSDB/backupset/2009_11_01/o1_mf_nnndf_TAG20091101T231814_5gvyrqdc_.bkp
Backup Set           9      01-11-2009 11:19:53
  Backup Piece       9      01-11-2009 11:19:53 /opt/oracle/app/oracle/flash_recovery_area
/VENUSDB/backupset/2009_11_01/o1_mf_nnndf_TAG20091101T231814_5gvyrqtm_.bkp
Backup Set           11     01-11-2009 11:20:04
  Backup Piece       11     01-11-2009 11:20:04 /opt/oracle/app/oracle/flash_recovery_area
/VENUSDB/autobackup/2009_11_01/o1_mf_s_701824802_5gvyw3h1_.bkp
deleted archived log
archived log file name=/opt/oracle/app/oracle/flash_recovery_area/VENUSDB/archivelog/
2009_11_01/o1_mf_1_6_5gvwkv55_.arc RECID=2 STAMP=701822427
deleted backup piece
backup piece handle=/opt/oracle/app/oracle/flash_recovery_area/VENUSDB/backupset/
2009_11_01/o1_mf_nnndf_TAG20091101T231814_5gvyrqdc_.bkp RECID=10 STAMP=701824695
deleted backup piece
backup piece handle=/opt/oracle/app/oracle/flash_recovery_area/VENUSDB/backupset/
2009_11_01/o1_mf_nnndf_TAG20091101T231814_5gvyrqtm_.bkp RECID=9 STAMP=701824695
deleted backup piece
backup piece handle=/opt/oracle/app/oracle/flash_recovery_area/VENUSDB/autobackup/
2009_11_01/o1_mf_s_701824802_5gvyw3h1_.bkp RECID=11 STAMP=701824803
Deleted 4 objects

Uuups! It just deleted our valid backupset. The proof:

RMAN> restore validate database;

Starting restore at 01-11-2009 11:35:03
using channel ORA_DISK_1
using channel ORA_DISK_2

channel ORA_DISK_1: starting validation of datafile backup set
channel ORA_DISK_2: starting validation of datafile backup set

...

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 11/01/2009 23:35:40
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 5 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore

I don't know if the above behavior is clearly mentioned in the Oracle backup and recovery documentation but this should be taken into account when defining the backup and recovery strategy. Of course a RETENTION POLICY of 1 is not a setting to be used in productive systems but, anyway, I expect troubles even if the retention policy is set to a higher redundancy. In my option, it would be great if RMAN could label somehow the corrupted backups at the time the restore validate is invoked and then to take into account this when the retention policy is applied.

Meanwhile, in order to avoid the above scenario within your backup scripts, it's advisable to group the RESTORE VALIDATE and DELETE NOPROMPT OBSOLETE within a RUN { ... } command. If the first command fails then the DELETE command will never be executed.

CREATE VIEW with FORCE does not work

2009-04-28T22:51:00.000-07:00

Yesterday I loaded an oracle dump in our 10.2.0.4 database... and guess what? Not all the views were created. I took a look into the impdp log and I saw some errors complaining that: ORA-00980: synonym translation is no longer valid. So what? The CREATE VIEW statements were issued with the FORCE clause therefore it should have been created, right?

Well, after some diggings on metalink I found this. It basically says that there is a(nother) bug and according to their description: create force view using a synonym for a table fails to create the view if the synonym is invalid. The 10.2.0.3 and 10.2.0.4 databases are confirmed to be affected and this bug is supposed to be fixed in 10.2.0.5 and 11.2.

In my case, the solution was to fix the synonyms problem and after that to reimport just the views using the INCLUDE parameter of the impdp utility.

WTF is that? (ep. 2)

2009-02-12T01:05:00.000-08:00

Today, the next episode of the Oracle WTF stories. One of my colleague brought to my attention the fact that the DECODE function doesn't work as expected when used with dates. He had a very simple test case:


create table muci (my_date date);

insert into muci
  select decode(to_date('30/12/2099', 'dd/mm/yyyy'),
                sysdate,
                null,
                to_date('30/12/2099', 'dd/mm/yyyy')) from dual;

He asked me: what we'll have in "MUCI" table after running the statements above? I didn't think too much. I realized that SYSDATE is not likely to be 30/12/2099, even the possibility of having a wrong setting in the OS clock couldn't be excluded, but anyway, I simply said that the final result should be 30/12/2099.

Let's take a look:


SQL> select to_char(my_date, 'dd/mm/yyyy') from muci;
 
TO_CHAR(MY_DATE,'DD/MM/YYYY')
-----------------------------
30/12/1999

Well, this was unexpected.. WTF? What's wrong with the YEAR? Even with a wrong OS clock setting this shouldn't happen. The reason must be somewhere else. Because I remembered that the result of DECODE depends on the type of the arguments, I said: let's look into docs! Yeap, the answer was there: "if the first result is null, then Oracle converts the return value to the datatype VARCHAR2". How this applies to our test case? It's simple: in fact, the whole result of the DECODE is a VARCHAR2 and not a DATE as one might think. The VARCHAR2 representation of a plain date value depends on the NLS_DATE_FORMAT, which on our server was:


SQL> select value from nls_session_parameters 
where parameter='NLS_DATE_FORMAT';
 
VALUE
----------------------------------------
DD-MON-RR

So, when the INSERT was done, the inserted value was '30/12/99' which was further automatically casted to a DATE according to the NLS_DATE_FORMAT setting and we ended up with a “wrong” year in the final result. Lovely!

Remotely Connect to a RESTRICT Opened Database

2008-10-07T03:09:00.000-07:00

Lately, I have this sub-conscience mantra which basically says: don’t believe everything Oracle Official Docs say but try and prove those facts! For example, one thing to try is starting an instance in restricted mode and prove after that what Oracle says in the Administration Guide 11g/Starting Up a Database chapter:

when the instance is in restricted mode, a database administrator cannot access the instance remotely through an Oracle Net listener, but can only access the instance locally from the machine that the instance is running on.

Lets try! On the server:

SQL> startup restrict
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size                  2149000 bytes
Variable Size             595592568 bytes
Database Buffers          230686720 bytes
Redo Buffers                6676480 bytes
Database mounted.
Database opened.

On the client, using an admin user:

Enter user-name: admin@tbag
Enter password:
ERROR:
ORA-12526: TNS:listener: all appropriate instances are in restricted mode

What they forget to say here is the fact that this behavior is obtained just with dynamic listener registration. If I’m going to explicitly specify the SID_LIST within my listener.ora file then I can connect remotely without problems.

Oracle Linux Date

2008-06-12T02:17:00.000-07:00

If you’ll ever need to get the current Linux time from Oracle then you might be interested in the following solution. First of all, the Linux epoch time is expressed as the number of seconds since 1970-01-01 00:00:00 UTC and can be obtain by using the date +'%s' command. For example:

oracle@oxg:~$ date +'%s'
1213261534

From Oracle you can use the following custom function:

create or replace function current_linux_date return integer is
  l_crr_date timestamp(9) := SYS_EXTRACT_UTC(systimestamp);
  l_ref_date timestamp(9) := to_date('01011970', 'ddmmyyyy');
  l_seconds integer;
begin
  l_seconds := extract(day from (l_crr_date - l_ref_date)) * 24 * 3600 +
               extract(hour from (l_crr_date - l_ref_date)) * 3600 +
               extract(minute from (l_crr_date - l_ref_date)) * 60 +
               extract(second from (l_crr_date - l_ref_date));
  return(l_seconds);
end current_linux_date;
/

Now, you should get the same result from Oracle:

SQL> select current_linux_date from dual;
 
CURRENT_LINUX_DATE
------------------
        1213261993

oracle@oxg:~$ date +'%s'
1213261993

Have fun!

Profiling the new SIMPLE_INTEGER type

2008-05-12T03:44:00.000-07:00

Oracle 11g comes with a new PLSQL type called SIMPLE_INTEGER. The official documentation says that this type yield significant performance compared to PLS_INTEGER type. Because I want to see this with my own eyes I’ve decided to test it using another new 11g component called hierarchical profiler which I also want to see how it’s working.

First of all, let’s setup the environment:

1. on the database server create a new directory to be used for creating profiler trace files:

oracle@obi:oracle$ mkdir profiler
oracle@obi:oracle$ chmod o-rx profiler/

2. create the DIRECTORY object in the database too, and grant read/write privileges to the testing user (in our case TALEK user):

SQL> create directory profiler_dir as '/opt/oracle/profiler';

Directory created.

SQL> grant read, write on directory profiler_dir to talek;

Grant succeeded.

3. grant execute privilege for DBMS_HPROF package to the TALEK user:

SQL> grant execute on dbms_hprof to talek;

Grant succeeded.

4. connect using TALEK user and create the following package (the only difference between the first and second approach is the type of the l_count variable):

create or replace package trash is

  procedure approach_1;
  
  procedure approach_2;

end trash;
/

create or replace package body trash is

  procedure approach_1 as
    l_count pls_integer := 0;
  begin
    for i in 1..10000 loop
      l_count := l_count + 1;
    end loop;
    dbms_output.put_line(l_count);
  end;
  
  procedure approach_2 as
    l_count simple_integer := 0;
  begin
    for i in 1..10000 loop
      l_count := l_count + 1;
    end loop;
    dbms_output.put_line(l_count);
  end;

end trash;
/

5. Profile the approaches:

SQL> exec dbms_hprof.start_profiling(location => 'PROFILER_DIR', filename => 'test.trc');
 
PL/SQL procedure successfully completed
 
SQL> exec trash.approach_1;
 
PL/SQL procedure successfully completed
 
SQL> exec trash.approach_2;
 
PL/SQL procedure successfully completed
 
SQL> exec dbms_hprof.stop_profiling;
 
PL/SQL procedure successfully completed

6. Analyze the generated trace file. For this we’ll use the "plshprof" command line utility.

oracle@obi:profiler$ plshprof -output report test.trc
PLSHPROF: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
[8 symbols processed]
[Report written to 'report.html']

Aaaaand, the WINNER is:

TALEK.TRASH.APPROACH_1 -> 5713 (microseconds)
TALEK.TRASH.APPROACH_2 -> 100706 (microseconds)

Well… this is unexpected. According to Oracle docs, the SIMPLE_INTEGER should be faster. Ok, back to official doc: "The new PL/SQL SIMPLE_INTEGER data type is a binary integer for use with native compilation which is neither null checked nor overflow checked". Ahaaa… native compilation! Let’s check this:

SQL> show parameter plsql_code_type

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
plsql_code_type                      string      INTERPRETED

So, we have a first clue and a first conclusion. If the database doesn’t use NATIVE compilation the SIMPLE_INTEGER type is actually much slower.

Let’s switch to native compilation. This can be easily done because the "plsql_code_type" parameter is dynamic:

SQL> alter system set plsql_code_type=native scope=both;

System altered.

It is important to compile once again the package because otherwise the old PLSQL byte code will be used (you can use "alter package trash compile plsql_code_type=native;"), then repeat the profiler tests.

The new results are:

TALEK.TRASH.APPROACH_2 -> 3927 (microseconds)
TALEK.TRASH.APPROACH_1 -> 12556 (microseconds)

Now, the second approach with SIMPLE_INTEGER is much faster and, interestingly, the PLS_INTEGER approach is slightly slower on native compilation compared with the same approach on the initial PLSQL interpreted environment.

Okey, one more thing. I really enjoy using the new 11g hierarchical profiler. From my point of view is a big step forward compared with the old DBMS_PROFILER, and the provided HTML reports produced by "plshprof" are quite lovely.

WTF is that? (ep. 1)

2008-05-11T11:56:00.000-07:00

I've just decided to put here some posts about the (...well, you know) Oracle WTF stories, those moments (... hours, and sometimes days) when being at my desk, I'm just staring at that stupid SQLPLUS> prompt couldn't figure out what the hell is happening.

Today, episode 1:

The scenario is very simple. I have two nice users: TALEK and SIM. TALEK has a table and gives UPDATE rights to SIM.

SQL> connect talek
Enter password: 
Connected.

SQL> create table muci (col1 varchar2(10));

Table created.

SQL> insert into muci values ('abc')
  2  /

1 row created.

SQL> commit;

Commit complete.

SQL> grant update on muci to sim;

Grant succeeded.

SQL> connect sim
Enter password: 
Connected.

SQL> update talek.muci set col1='xyz' where col1='abc';
update talek.muci set col1='xyz' where col1='abc'
             *
ERROR at line 1:
ORA-01031: insufficient privileges

Of course, this is the WTF moment. Why the UPDATE failed? The first thing to do is to check the DBA_TAB_PRIVS view for a confirmation that the UPDATE privilege is still there. (I'm pretty sure that no one was so fast to revoke meanwhile the granted privilege but, just in case...)

SQL> select grantee, owner, table_name, privilege 
from dba_tab_privs where table_name='MUCI' and owner='TALEK';

GRANT OWNER TABLE_NAM PRIVILEGE
----- ----- --------- ----------
SIM   TALEK MUCI      UPDATE

And yes, the privilege is there. Hmmm... what's next? Usually the next thought is that another Oracle bug makes fun of me. But, this sounds too scary to be true. Finally, the stupid answer comes to light.

SQL> show parameter sql92_security

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sql92_security                       boolean     TRUE

The database reference documentation says the following:
"The SQL92 standards specify that security administrators should be able to require that users have SELECT privilege on a table when executing an UPDATE or DELETE statement that references table column values in a WHERE or SET clause. SQL92_SECURITY specifies whether users must have been granted the SELECT object privilege in order to execute such UPDATE or DELETE statements."

With the above sql92_security parameter set, actually the "where col1='abc'" filter from the UPDATE statement complains about "insufficient privileges" and not the UPDATE itself. Without a filter the update executes as expected:

SQL> update talek.muci set col1='xyz';

1 row updated.

Ok, another lesson has been learned!

Obsolete Policy Violation

2008-05-09T04:43:00.000-07:00

This is quite funny. Starting with 10g the LOG_ARCHIVE_START parameter is obsolete. Nevertheless, into the "Database Control" of a new installed 11g database I have a policy rule violation called "Use of Automatic Log Archival Features". The description of this policy rule says: "This policy ensures that archiving of redo logs is done automatically and prevents suspension of instance operations when redo logs fill. Only applicable if database is in archivelog mode".

Well, my database is already configured to run in archivelog mode:

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     15
Next log sequence to archive   17
Current log sequence           17

So, where is the problem? The action suggested by "Database Control" console is "Set the value of the LOG_ARCHIVE_START initialization parameter to TRUE". Hmmm, that is to set an obsolete parameter... no, thanks!

Update: Actually, I discovered that there are a lot of non-relevant/obsolete policies for 11g, like SGA_TARGET is not used, or PGA_AGGREGATE_TARGET should be used etc. It seems that many default settings from the "Database Control" do not take into consideration the actual version of the managed database therefore you have to manually suppress these policy/rule violations.

TKPROF Everywhere

2008-03-27T06:22:00.000-07:00

Lately, I was searching for a solution to let developers to “tkprof” their statements in the most non-intrusive way possible. Of course, a quite appealing solution I found was the one suggested by Tom Kyte in his “Effective Oracle by Design” book. The solution involves reading the user trace file from the server and returning the content of that trace through a pipelined table function. The result may be spooled into a file on the client side and, after that, “tkprof” may be executed for this local file. However, the problem I have is that me, personally, I use oracle instant client and I don't have tkprof on my local machine. Furthermore, I don't use sqlplus all the time, therefore I would really like to get the formatted tkprof output directly within a simple SQL-SELECT statement.

The solution is quite simple: instead of returning the raw trace file it's enough to run tkprof utility on the server with the user dump trace file as a parameter and, eventually, to return the final output.

In order to setup the needed environment we'll have to:

1.create the user which will own the “tkprof” framework:


grant create session, alter session, create procedure to tools identified by xxx;

2.grant additional privileges for this user:


grant select on sys.v_$process to tools;
grant select on sys.v_$session to tools;
exec dbms_java.grant_permission( 'TOOLS', 
  'SYS:java.lang.RuntimePermission', writeFileDescriptor', '' );
exec dbms_java.grant_permission( 'TOOLS', 
  'SYS:java.lang.RuntimePermission', 'readFileDescriptor', '' );

3.create the following java source object. This is needed in order to execute an external program in the OS environment (thanks again Tom for this, see the Java procedure for host calls on Unix environment question.):


create or replace and compile java source named util as
import java.io.*;
import java.lang.*;

public class Util extends Object
{

 public static int RunThis(String args)
 {
 Runtime rt = Runtime.getRuntime();
 int        rc = -1;

 try
 {
    Process p = rt.exec(args);

    int bufSize = 4096;
    BufferedInputStream bis =
     new BufferedInputStream(p.getInputStream(), bufSize);
    int len;
    byte buffer[] = new byte[bufSize];

    // Echo back what the program spit out
    while ((len = bis.read(buffer, 0, bufSize)) != -1)
       System.out.write(buffer, 0, len);

    rc = p.waitFor();
 }
 catch (Exception e)
 {
    e.printStackTrace();
    rc = -1;
 }
 finally
 {
    return rc;
 }
 }
}

4.Create the package which embeds the whole tracing logic:


create or replace package tkprof 
/**
  Provides the tkprof tracing feature to developers. This package is
  supposed to be used in developing environments only.
  
  Required rights (the owner of this package is supposed to be TOOLS):
  
  grant alter session to tools;
  grant select on sys.v_$process to tools;
  grant select on sys.v_$session to tools;
  
  exec dbms_java.grant_permission( 'TOOLS', 'SYS:java.lang.RuntimePermission', 'writeFileDescriptor', '' );
  exec dbms_java.grant_permission( 'TOOLS', 'SYS:java.lang.RuntimePermission', 'readFileDescriptor', '' );

*/
is

  /**
    The complete path along the name of the tkprof utility. Change this constant to fit to your
    environment. After that the following right is required:
    
    exec dbms_java.grant_permission( 'TOOLS', 'SYS:java.io.FilePermission', TKPROF.TKPROF_EXECUTABLE, 'execute' );
    
    If a RAC configuration is used than the path should be the same accross all nodes. If the ORACLE_HOME
    env variable is different between the RAC nodes then the value of the ORACLE_HOME should be get dynamically
    by using, for example, the DBMS_SYSTEM.get_env procedure or symbolinc links may be created in the OS
    environment of every RAC node.
  */
  TKPROF_EXECUTABLE constant varchar2(300) := '/opt/oracle/product/10.2.0/db_1/bin/tkprof';

  /**
    The directory where the user trace files are stored. May be found out using:

    show parameter user_dump_dest
    
    If a RAC configuration is used than the path should be the same accross all nodes. If not, then the value
    should be fetch dynamically from v$parameter view. Another approach would be to create symbolic links in the
    OS environment of every RAC node.
  */
  UDUMP_PATH constant varchar2(300) := '/opt/oracle/admin/rfd/udump';

  /**
    The name of the oracle directory object which points out to the above
    path. The owner of this package must have read privileges on this
    directory:
    
    create directory UDUMP_DIR as '/opt/oracle/admin/rfd/udump';
    grant read on directory UDUMP_DIR to tools;
  */
  UDUMP_DIR constant varchar2(30) := 'UDUMP_DIR';
  
  /**
    A simple type used to return the tkprof_output.
  */
  type tkprof_output is table of varchar2(4000);

  /**
    Enable tracing for the current session. 
  */
  procedure enable;
  
  /**
    Disable tracing for the current session.
  */
  procedure disable;
  
  /**
    Get the status of the tracing for the current session.
    
    #return 'TRUE' if the trace is enabled, 'FALSE' otherwise.
  */
  function is_enabled return varchar2;
  
  /**
    Get the tkprof content thorough a pipelined table function.

    #pi_tkprof_params additional parameters to tkprof
  */
  function output(pi_tkprof_params varchar2 := '') return tkprof_output pipelined;

end tkprof;
/

create or replace package body tkprof is

  g_unique_session_id varchar2(100);
  g_trace_file_name   varchar2(4000);
  g_is_enabled        boolean;

  function run(pi_cmd in varchar2) return number as
    language java name 'Util.RunThis(java.lang.String) return integer';

  procedure enable as
  begin
    execute immediate 'alter session set tracefile_identifier=''' ||
                      g_unique_session_id || '''';
    execute immediate 'alter session set timed_statistics=true';
    execute immediate 'alter session set max_dump_file_size=unlimited';
    execute immediate 'alter session set events ''10046 trace name context forever, level 12''';
    g_is_enabled := true;
    dbms_output.put_line('session tracing enabled under ' ||
                         g_unique_session_id || ' identifier.');
  end;
  
  function is_enabled return varchar2 as
  begin
    if g_is_enabled then
      return 'TRUE';
    else
      return 'FALSE';
    end if;
  end;

  procedure disable as
  begin
    execute immediate 'alter session set events ''10046 trace name context off''';
    g_is_enabled := false;
    dbms_output.put_line('session tracing disabled');
  end;

  function output(pi_tkprof_params varchar2 := '') return tkprof_output
    pipelined as
    l_status  pls_integer;
    l_bfile   bfile := bfilename(UDUMP_DIR, g_trace_file_name || '.prf');
    l_last    pls_integer := 1;
    l_current pls_integer;
  begin
    dbms_java.set_output(10000);
    l_status := run(TKPROF_EXECUTABLE || ' ' || UDUMP_PATH || '/' ||
                    g_trace_file_name || ' ' || UDUMP_PATH || '/' ||
                    g_trace_file_name || '.prf ' || pi_tkprof_params);
    if l_status != 0 then
      dbms_output.put_line('status: ' || l_status);
      dbms_output.put_line('ERROR: cannot produce the tkprof trace!');
      return;
    else
      dbms_lob.fileopen(l_bfile);
      loop
        l_current := dbms_lob.instr(l_bfile, '0A', l_last, 1);
        exit when(nvl(l_current, 0) = 0);
        pipe row(utl_raw.cast_to_varchar2(dbms_lob.substr(l_bfile,
                                                          l_current -
                                                          l_last + 1,
                                                          l_last)));
        l_last := l_current + 1;
      end loop;
      dbms_lob.fileclose(l_bfile);
    end if;
  exception
    when others then
      if dbms_lob.isopen(l_bfile) != 0 then
        dbms_lob.fileclose(l_bfile);
      end if;
      raise;
  end;

begin
  g_unique_session_id := sys_context('userenv', 'sessionid');
  select sys_context('userenv', 'instance_name') || '_ora_' ||
         ltrim(to_char(a.spid)) || '_' || g_unique_session_id || '.trc'
    into g_trace_file_name
    from v$process a, v$session b
   where a.addr = b.paddr
     and b.audsid = sys_context('userenv', 'sessionid');
end tkprof;
/

5.Change the TKPROF_EXECUTABLE and UDUMP_PATH constants to fit with your environment.
6.Create the UDUMP_DIR directory:


create directory UDUMP_DIR as '/opt/oracle/admin/rfd/udump';
grant read on directory UDUMP_DIR to tools;

7.Grant execute rights for tkprof executable:


exec dbms_java.grant_permission( 'TOOLS', 'SYS:java.io.FilePermission', 
TKPROF.TKPROF_EXECUTABLE, 'execute' );

8.Make the TKPROF package available to everybody:


grant execute on tools.tkprof to public;
create public synonym tkprof for tools.tkprof;

Okey, that's it! Now, let's test it:


fits_cr@RFD> exec tkprof.enable

session tracing enabled under 7154134 identifier.

PL/SQL procedure successfully completed.

fits_cr@RFD> select count(1) from rolmda;

  COUNT(1)
----------
        95

fits_cr@RFD> exec tkprof.disable;
session tracing disabled

PL/SQL procedure successfully completed.


fits_cr@RFD> select * from table(tkprof.output);

COLUMN_VALUE
--------------------------------------------------------------------------------------
TKPROF: Release 10.2.0.3.0 - Production on Thu Mar 27 15:13:06 2008
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Trace file: /opt/oracle/admin/rfd/udump/rfd3_ora_26618_7154134.trc
Sort options: default

********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************

select /*+ index(idl_sb4$ i_idl_sb41) +*/ piece#,length,piece
from
 idl_sb4$ where obj#=:1 and part=:2 and version=:3 order by piece#

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          2          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          2          0           0

... and so on ...

I'm sure this package can be improved but as a proof of concept I think that it's more than enough. Likewise, I have to mention that this was tested on a 10gR2 database therefore I don't know if you can use it without any changes on a different Oracle database version.

What's Wrong with Oracle TIMESTAMP WITH TIME ZONE Data-type?

2008-03-22T03:53:00.000-07:00

First of all, let's start by recalling what a TIMESTAMP WITH TIME ZONE data-type is. According to the Oracle official documentation "a TIMESTAMP WITH TIME ZONE is a variant of TIMESTAMP that includes a time zone region name or time zone offset in its value. The time zone offset is the difference (in hours and minutes) between local time and UTC (Coordinated Universal Time, formerly Greenwich Mean Time)".

Well, very nice... this is a data-type which might be very useful in a distributed environment across all over the world where, unlike its counterpart TIMESTAMP WITH LOCAL TIME ZONE data-type, is also important to record the time zone from which, a transaction or whatsoever information has been inputed into the system. A potential suitable case for using this timezone information might be the producing of a report with all peak hours of activity in the context of a specific timezone area.

So far so good. What I really don't like about this data-type is that I cannot create a primary/unique constraint on a such a column. The Oracle documentation clear states that "none of the columns in the unique/primary key can be of LOB, LONG, LONG RAW, VARRAY, NESTED TABLE, OBJECT, REF, TIMESTAMP WITH TIME ZONE, or user-defined type. However, the unique/primary key can contain a column of TIMESTAMP WITH LOCAL TIME ZONE".

Furthermore, any index created for such a column is actually converted into a functional index. Behind the scenes, Oracle automatically normalize all the values of a TIMESTAMP WITH TIME ZONE column using the SYS_EXTRACT_UTC() function. In a way, this makes sens. Suppose we have to order the result-set of a SQL-SELECT using a TIMESTAMP WITH TIME ZONE column. Having for example:


TIMESTAMP '2003-01-01 2:00:00 -08:00
TIMESTAMP '2003-01-01 2:00:00 -02:00

what record should be the first one? According to what point of reference? The solution is to normalize all values and only after that to compare the values. Oracle transparently does this.
Now, what if I want that the values to be unique in a TIMESTAMP WITH TIME ZONE column? Well, you can't create a unique constraint but you can create a unique index.


SQL> create table muci_test (
  2   col1 timestamp with time zone,
  3   constraint uk_muci_test_col1 unique(col1)
  4  );

create table muci_test (
 col1 timestamp with time zone,
 constraint uk_muci_test_col1 unique(col1)
)

ORA-02329: column of datatype TIME/TIMESTAMP WITH TIME ZONE 
cannot be unique or a primary key
SQL> create table muci_test (
  2   col1 timestamp with time zone
  3  );

Table created

Executed in 0,204 seconds

SQL> create unique index ix_muci_text_col1 on muci_test(col1);

Index created

Executed in 0,25 seconds

SQL> insert into muci_test values (to_timestamp('22-MAR-08 11.25.39.457137'));

1 row inserted

Executed in 0,031 seconds

SQL> insert into muci_test values (to_timestamp('22-MAR-08 11.25.39.457137'));

insert into muci_test values (to_timestamp('22-MAR-08 11.25.39.457137'))

ORA-00001: unique constraint (ADMIN.IX_MUCI_TEXT_COL1) violated

As I already said, Oracle has actually created a functional index and this can be easily checked:


SQL> select index_type from user_indexes where index_name = 'IX_MUCI_TEXT_COL1';

INDEX_TYPE
---------------------------
FUNCTION-BASED NORMAL

If you don't feel very comfortable with functional indexes or you really want a primary/unique key constraint then the application design should take into consideration this limitation. Me personally, I would choose a TIMESTAMP WITH LOCAL TIME ZONE data-type and an additional VARCHAR2 column to store the originating TIME ZONE offsets. Using this approach it is possible to enforce a multi-column primary/unique key.

ORA_ROWSCN pitfall for optimistic locking

2008-02-10T03:58:00.000-08:00

I can see lately that there's a big enthusiasm around this new 10g pseudo-column called ORA_ROWSCN, one of its great benefit being for implementing an optimistic locking mechanism. From a simplistic point of view, this mechanism consists of:

1.on the application server or on the client side we have an entity class which is mapped on a particular table. Instances of that class (objects) reflect rows on that particular table.
2.Every entity object has also a version field which maps to the ORA_ROWSCN pseudo-column.
3.At the very beginning, we are loading our entities using SELECT T.*, ORA_ROWSCN FROM TABLE T so that every entity has the corresponding values and version.
4.Now, suppose that the user changes some attributes from the entity through the application front-end. This changes have to be propagated into the database, most likely using an UPDATE statement, but taking care of the version field. That is, we need to be sure that meanwhile, the version of the entity we are about to persist didn't change within the database. So, all is narrowing down on performing something like : UPDATE TABLE set COLUMN = [value from the entity] where PK_COLUMN = [id from the entity] and ORA_ROWSCN = [version from the entity]. If the above UPDATE will return “0 rows updated” we'll know for sure that somebody else has changed the record into the database and the application will throw an “Optimistic Lock Exception”.

So, everything seems to work nicely: no more timestamp version columns, no more OLD and NEW column values comparisons, no more row hashes. In 10g, it seems that Oracle offered us a bullet proof solution. BUUUT, there's a pitfall here. Every decent application is working with transactions therefore every DML is nicely encapsulated into an Oracle transaction. What happens if on the 4th step our update succeeds? Well, in this case the version of the entity will have to be updated accordingly. But, our update is not yet committed therefore we don't have access to the new value of the ORA_ROWSCN which will be available only after COMMIT. This means that, in order to refresh our entity, we'll have to issue an SQL-SELECT after COMMIT, something like: SELECT T.*, ORA_ROWSCN FROM TABLE T WHERE PK_COLUMN=[id from the entity]. The main problem here is the fact that between our COMMIT and the SQL-SELECT there is a tiny time frame within which a lot of nasty things may happen: somebody else may delete our record or may update it once again. Furthermore, the fact that I have to issue an SQL-SELECT after the UPDATE doesn't sound to me very appealing as involves additional work for the database and might affect the whole scalability. In addition I would really like to use the RETURNING clause of the INSERT/UPDATE statements and to refresh my entity in one step.

In conclusion, if you are designing an WEB application which doesn't reuse entities across WEB pages then using ORA_ROWSCN for implementing optimistic locking is fine as long as the working flow is the following:

1.the user select the record he/she wants to change from a grid;
2.the application loads the entity and assigns the ORA_ROWSCN value as version;
3.the user performs the required changes and press the SAVE button;
4.the application performs the UPDATE into the database taking care of the ORA_ROWSCN values (WHERE ... and ORA_ROWSCN=[version from the entity]) and COMMITs the changes.
5.the application display once again the initial grid re-querying the database.

However, if you are designing an application which is required to take advantage of caching (most likely a stand-alone rich client application) then the ORA_ROWSCN is not the right solution. For example:

1.the application loads a grid and every grid record has the corresponding entity attached;
2.the user select the record he/she wants to change from the grid;
3.the application relies on the data from the corresponding entity therefore it doesn't query the database;
4.the user performs the changes and press the SAVE button;
6.the application performs the UPDATE into the database taking care of the ORA_ROWSCN values (WHERE ... and ORA_ROWSCN=[version from the entity]) and COMMIT the changes.
7.If the UPDATE returns “1 record updated” then our entity is stale because the new version from the database doesn't reflect the old one from the entity. A new SELECT, outside of the UPDATE transaction is required in order to refresh the entity which, of course, is not convenient at all.

So for this second kind of application I would use a regular version-column or hashing techniques which allows to use the RETURNING clause of the INSERT/UPDATE statements or to refresh the entity within the same modifying transaction.

DEFAULT and NOT NULL become friends in 11g

2007-12-06T04:35:00.000-08:00

If somebody would ask me to add a new column to a production table the first thing I would check is how many active transactions on that table are or, in other words, how hot it is from the concurrency point of view. This is because, if there are any active transactions on that table, my attempt to change its structure will simply fail. Of course, an online redefinition could be the right solution but is far more complicated than a simple ALTER TABLE. However, with Oracle 11g things change. My decision of using an ALTER TABLE statement instead of a DBMS_REDEFINITION operation when I am about to add new columns should be driven by the answers to the following questions:
1. Is it appropriate for the new column to be NOT NULL and to have a DEFAULT clause?
OR
2. Is it appropriate for the new column to allow NULLs without having a DEFAULT clause?
OR
3. Is it about a virtual column?
If the answer is YES you can safety go on with the ALTER TABLE ADD COLUMN statement.

Below is the proof:

1. create a dummy table:

create table muci (col1 number, col2 number);

2. from another sqlplus session run the following anonymous block which simulates a mixed workload with: INSERTs, UPDATEs and DELETEs:

-- simulate a workload with a mix of INSERTs, UPDATEs and DELTEs
-- close the sqlplus window in order to terminate
declare
  l_option pls_integer;
begin
  loop
    l_option := round(dbms_random.value(1, 3));
    if l_option = 1 then
       update muci set col2 = dbms_random.value 
          where rowid in (select rowid from muci where rownum <= 1);
    elsif l_option = 2 then
       insert into muci (col1, col2) values (dbms_random.value, dbms_random.value);
    else
       delete from muci where rowid in (select rowid from muci where rownum <= 1);
    end if;
    commit;
  end loop;
end;
/

3. now, run the following statements:

SQL> set timing on
SQL> alter table muci add (col3 number default 0 not null);

Table altered.

Elapsed: 00:00:01.32
SQL> alter table muci add (col4 number);

Table altered.

Elapsed: 00:00:03.36
SQL> alter table muci add (col5 number as (col1+col2) virtual);

Table altered.

Elapsed: 00:00:00.21
SQL> alter table muci add(col6 number default 0);
alter table muci add(col6 number default 0)
            *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired


Elapsed: 00:00:00.10

Very nice, except for the last case, when trying to add a column which allows NULLs and also have a DEFAULT clause. Why did not work? The answer relies, for sure, in the way the ALTER TABLE ADD COLUMN was implemented.

If the new column allows NULLs and has no DEFAULT clause then, anyway, the value of the new added column is NULL and being the last one it is not stored therefore no "data reorganization" is required within the table blocks. The rows from the "MUCI" table continue to have the following structure:

However, Oracle knows that there is a third column but its NULL value is not stored within the row.

When a new column is added having the NOT NULL constraint with a DEFAULT clause, the default value for this new column is stored just once as a table metadata. In a way it's the same like in the table compression feature where for repeated values of a column just one single value is stored, being further referenced when the table is queried. In this case, the rows from the MUCI table will still have the same structure:

The nice part is that Oracle knows that there is a third column which, on the row structure level, is like a "last column with NULLs". But how this could be true? The column was defined with a NOT NULL constraint. Well, if the row representation is like a "last column with NULLs" but the column was defined as NOT NULL then Oracle will simply take the default value which is stored in the metadata of the table and that value will be returned.

We can easily test that the rows layout remains the same:

1. create a dummy table:

SQL> create table my_table as select * from all_objects;

Table created.

2. check the corresponding segment size:

SQL> select bytes/1024 from user_segments where segment_name='MY_TABLE';

BYTES/1024
----------
      8192

3. add a new "last column with NULLs":

SQL> alter table my_table add (my_column1 char(2000));

Table altered.

3. check the segment size.

SQL> select bytes/1024 from user_segments where segment_name='MY_TABLE';

BYTES/1024
----------
      8192

Please notice that the size is the same.

4. add a new NOT NULL column with a default value:

SQL> alter table my_table add (my_column2 char(2000) default 'xxx' not null);

Table altered.

4. check the segment size:

SQL> select bytes/1024 from user_segments where segment_name='MY_TABLE';

BYTES/1024
----------
      8192

Please notice that the segment size is unchanged.

So far so good... but what happens when you try do add a column which allows NULLs and also have a DEFAULT clause? In this case Oracle cannot decide if the value of the new added column should be NULL or the default value. For the previous two cases, it was a black&white approach but now, allowing DEFAULTS and NULLS turns out to be a gray problem and it cannot be handled in the same way. I mean, how could ever Oracle figure out whenever it's about a DEFAULT value or a NULL one? There's no possibility therefore the old approach will be used which actually implies re-organizations within the Oracle table blocks that is allocating the extra storage within each oracle block which belongs to the target table in order to accommodate space for the new column with the specified default value.

Let's check:

SQL> alter table my_table add (my_column3 char(10) default 'xxx');

Table altered.

SQL> select bytes/1024 from user_segments where segment_name='MY_TABLE';

BYTES/1024
----------
      9216

Yes, the segment size has increased. Lovely!

Playing with Flashback Archives

2007-11-07T11:24:00.000-08:00

Today I've decided to play a little bit with a new Oracle 11g feature called "Flashback Archive". The "Flashback" term is not new at all. I heard about it, for the very first time, in Oracle 9i. At that time, the "flashback" feature it was on its very beginning and allowed just to query a table "AS OF" a previous moment in time, taking into consideration, especially, UNDO segments constraints. As far as I remember there was no possibility to guarantee that the UNDO will really deliver the requested information as there was no GUARANTEE option for the UNDO tablespaces. However, Oracle 10g continued to refine this feature and brought a plenty of improvements: guarantee option for UNDOs, flashback database and flashback table, flashback transactions and others. Now, Oracle 11g adds a new component to the "flashback suite" which is this "flashback archive".
The "flashback archive" is nothing more than a repository which, of course, requires storage provided through one or more tablespaces and it has a well defined retention policy. This archive may be used to keep track of all changes performed on several configured tables. It is important to notice that you can setup more than one flashback archives which, interestingly, may share the same underlying tablespaces.

For example:

SQL> create tablespace test_arch_tbs datafile size 100M;

Tablespace created

SQL> create flashback archive test_archive tablespace test_arch_tbs retention 1 month;

Flashback archive created.

SQL> create flashback archive test_archive2 tablespace test_arch_tbs retention 1 month;

Flashback archive created.

Please notice that both "test_archive" and "test_archive2" share the same "test_arch_tbs" tablespace. In addition, you can configure a quota for every flashback archive you create, otherwise unlimited is supposed to be the default option.
Another important thing to notice is that you can configure a default flashback archive. You can do this at the archive creation time by specifying the "DEFAULT" option or afterwards using an "ALTER FLASHBACK ARCHIVE... SET DEFAULT". For both operations you need to be logged in as SYSDBA. Of course, you cannot have more than one default flashback archive.
Now, lets create a table with the flashback archive option enabled:

SQL> create table customers (
  2    cust_id    integer,
  3    cust_name  varchar2(100),
  4    country_id varchar2(2)
  5  ) flashback archive test_archive;

Table created

If the archive name is not explicitly specified then the default archive (if previously setup) will be used. It's time now to insert some records into our customers table. The question which arises is which time-stamp is used when flashback query the table: the one which corresponds to the INSERT itself or the one corresponding to the COMMIT statement. We'll figure out shortly:

SQL> insert into customers values (1, 'synapstec', 'ro');

1 row inserted

SQL> select systimestamp from dual;

SYSTIMESTAMP
-------------------------------------------------
07-NOV-07 05.50.01.383321 PM +02:00

SQL> commit;

Commit complete

SQL> select systimestamp from dual;

SYSTIMESTAMP
-------------------------------------------------
07-NOV-07 05.50.15.130301 PM +02:00

SQL> select /* INSERT TIME */ count(1)
  2    from customers
  3    as of timestamp to_timestamp('07-NOV-07 05.50.01.383321 PM',
  4    'dd-mon-yy hh.mi.ss.ff PM');

  COUNT(1)
----------
         0

SQL> select /* COMMIT TIME */ count(1)
  2    from customers
  3    as of timestamp to_timestamp('07-NOV-07 05.50.15.130301 PM',
  4    'dd-mon-yy hh.mi.ss.ff PM');

  COUNT(1)
----------
         1

As you can see the COMMIT time is taken into consideration when the table is flashback queried, which is the expected behavior. This means that, for example, several UPDATES onto the same column will not be stored into the archive but the last committed one.
Likewise, you may encounter problems if you need to flashback query at very precise timestamps. Please have a look to the above example:

6:21:38 PM SQL> update customers set country_id = 'fr';

1 row updated

6:21:38 PM SQL> commit;

Commit complete

6:21:38 PM SQL> select systimestamp from dual;

SYSTIMESTAMP
-------------------------------------------------
07-NOV-07 06.16.16.506341 PM +02:00

6:21:38 PM SQL> update customers set country_id = 'en';

1 row updated

6:21:38 PM SQL> commit;

Commit complete

6:21:38 PM SQL> select systimestamp from dual;

SYSTIMESTAMP
-------------------------------------------------
07-NOV-07 06.16.16.669808 PM +02:00

6:23:09 PM SQL> select /* FIRST COMMIT */ country_id
             2    from customers
             3    as of timestamp to_timestamp('07-NOV-07 06.16.16.506341 PM',
             4    'dd-mon-yy hh.mi.ss.ff PM');

COUNTRY_ID
----------
ro

6:23:10 PM SQL>
6:24:18 PM SQL> select /* SECOND COMMIT */ country_id
             2    from customers
             3    as of timestamp to_timestamp('07-NOV-07 06.16.16.669808 PM',
             4    'dd-mon-yy hh.mi.ss.ff PM');

COUNTRY_ID
----------
ro

6:27:56 PM SQL> select /* AFTER 3 SECONDS */ country_id
             2    from customers
             3    as of timestamp to_timestamp('07-NOV-07 06.16.19.669808 PM',
             4    'dd-mon-yy hh.mi.ss.ff PM');

COUNTRY_ID
----------
ro

6:29:27 PM SQL> select /* AFTER SOME SECONDS */ country_id
             2    from customers
             3    as of timestamp to_timestamp('07-NOV-07 06.16.21.669808 PM',
             4    'dd-mon-yy hh.mi.ss.ff PM');

COUNTRY_ID
----------
en

As you can see the rough time of each of the above commits doesn't return the correct flashback data. There is a delay between the COMMIT and the relative time to which the flashback archive may be queried. This drawback can be alleviated by using SCNs. So, we can replace timestamps with their corresponding, more precise, SCNs:

8:58:50 PM SQL> update customers set country_id = 'fr';

1 row updated

8:58:50 PM SQL> commit;

Commit complete

8:58:50 PM SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                  611944

8:58:51 PM SQL> update customers set country_id = 'en';

1 row updated

8:58:51 PM SQL> commit;

Commit complete

8:58:51 PM SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                  611947

8:58:51 PM SQL>
9:00:13 PM SQL> select /* FIRST COMMIT */ country_id
             2    from customers
             3    as of scn 611944;

COUNTRY_ID
----------
fr

9:00:13 PM SQL> select /* SECOND COMMIT */ country_id
             2    from customers
             3    as of scn 611947;

COUNTRY_ID
----------
en

Nice!
There are however some limitations as far as this feature is concerned. The ugliest ones, at least from my point of view, are:
1. the fact that I cannot modify columns owned by a table which has the flashback archive option enabled along with
2. the fact that we cannot perform partition operations for those tables.
These, put together with a long retention period, become a little bit scary because it is quite likely that, in 5 years for example, the business logic to change leading to modifying the type of a column, for instance. These changes are difficult to predict therefore extreme care should be given for the design of those tables which require flashback archiving. Again, if you have a big transaction table which needs flashback archiving but it is also suitable for partitioning, which feature should we elect? Hard choice, right?
If you ask me, I would, at least, prefer to have the possibility to temporary store the flashback archive records for a particular table into a temporary stage, with something like: "create table temp_stage as select * from {table} ... as of ...", then to disable the flashback archive, to do the maintenance stuff and then to have the possibility to reload the archive from the temporary stage. Well, it's just a thought... of course I have no idea about internal stuff and what this might imply.

Okey, that's it for the moment. The feature is nice and fits to a lot of projects.

Preparing Parameters for 12g?

2007-11-06T09:32:00.000-08:00

Within all Oracle versions there were some features about I heard a lot something like: "well this feature is not quite ready now but it will be within the next Oracle version". This was the case for the "Oracle Resource Manager" in 9i, or for the "Oracle Managed Files" feature. Both of them were made available in 9i but many DBAs, including me, felt that they were not really ready for production. Only starting with Oracle 10g I could figure out that the strength of the OMF was especially in connection with ASM (Automatic Storage Management) and, just beginning with this version, the "Resource Manager" component had been improved quite enough to become appealing for a productive environment.

Now, I have the same feeling regarding the named and mixed notation for USER DEFINED PL/SQL functions, when they are invoked in a SQL context. For me, it was a common error in Oracle previous releases to write something like:

select dbms_random.string(opt => 'L', len => 10) from dual;

Of course, an error was raised because the SQL context doesn't know how to handle named parameters, but there is also true that when you are using an IDE like PL/SQL Developer or other powerful tools which provide code completion, it is almost impossible not to be tempted by such a feature, being sometimes hard to avoid the above mistake. However, the above code is perfectly valid in Oracle 11g. What really bothers me here is that I cannot use this approach for all functions. For example:

SQL> select regexp_count(srcstr => 'tralala', pattern => 'a') from dual;

select regexp_count(srcstr => 'tralala', pattern => 'a') from dual

ORA-00907: missing right parenthesis

Bleah, ugly! At least it is working for DBMS packages which is a great plus. But, anyway, for me it's a feature which is not "quite ready" but I hope it will be in Oracle 12g... maybe.

Describe REFCURSORs

2007-11-05T10:09:00.000-08:00

In Oracle 11g, I really like the interoperability between DBMS_SQL cursors and REFCURSORs. Thanks God, they are finally friends and can "talk" to each other.
I remember that a few months ago I needed a feature through which to be able to describe a weak ref cursor, that is to know how many columns are in the result set along with the corresponding types for every column. Of course, I knew that DBMS_SQL package provides such a feature but it didn't apply to ref cursors therefore, bad luck!
Now, with the new Oracle 11g, it is possible to convert a ref cursor into a DBMS_SQL cursor and vice-versa so, virtually, this opens new gates, right?... including my problem with describing ref cursors.
Lets take a very simple example. Suppose we have a basic procedure which receives a ref cursor as a parameter and it has to know the structure of that cursor. Well, not a big deal in 11g. Just see below how it works:

SQL> create or replace procedure desc_refcursor(pi_cursor sys_refcursor) is
  2    l_ref sys_refcursor;
  3    l_cursor integer;
  4    l_cols_count integer;
  5    l_desc_tab dbms_sql.desc_tab;
  6 
  7  begin
  8    l_ref := pi_cursor;
  9    l_cursor := dbms_sql.to_cursor_number(l_ref);
 10    dbms_sql.describe_columns(l_cursor, l_cols_count, l_desc_tab);
 11    dbms_output.put_line('number of columns = ' || l_cols_count);
 12    dbms_output.new_line;
 13    dbms_output.put_line('DESCRIBE CURSOR');
 14    dbms_output.put_line('===========================');
 15    for i in 1..l_cols_count
 16    loop
 17      dbms_output.put_line(l_desc_tab(i).col_name);
 18    end loop;
 19    dbms_sql.close_cursor(l_cursor);
 20  exception
 21    when others then
 22      if dbms_sql.is_open(l_cursor) then
 23        dbms_sql.close_cursor(l_cursor);
 24      end if;
 25      raise;
 26  end desc_refcursor;
 27  /

Procedure created

The whole trick consists in using the new DMBS_SQL.TO_CURSOR_NUMBER function (see line 9). Maybe you wonder why this function was not used against the pi_cursor parameter and why that additional assignment at the line 8. That's because the TO_CURSOR_NUMBER function expects an input/output parameter and because pi_cursor is read only the compiler will complain.

Now, lets test the above procedure:

SQL> set serveroutput on
SQL> declare
  2    l_cursor sys_refcursor;
  3  begin
  4    open l_cursor for select * from user_tables;
  5    desc_refcursor(l_cursor);
  6  end;
  7  /

number of columns = 50

DESCRIBE CURSOR
===========================
TABLE_NAME
TABLESPACE_NAME
CLUSTER_NAME
IOT_NAME
STATUS
PCT_FREE
PCT_USED
INI_TRANS
MAX_TRANS
INITIAL_EXTENT
NEXT_EXTENT
MIN_EXTENTS
MAX_EXTENTS
PCT_INCREASE
FREELISTS
FREELIST_GROUPS
LOGGING
BACKED_UP
NUM_ROWS
BLOCKS
EMPTY_BLOCKS
AVG_SPACE
CHAIN_CNT
AVG_ROW_LEN
AVG_SPACE_FREELIST_BLOCKS
NUM_FREELIST_BLOCKS
DEGREE
INSTANCES
CACHE
TABLE_LOCK
SAMPLE_SIZE
LAST_ANALYZED
PARTITIONED
IOT_TYPE
TEMPORARY
SECONDARY
NESTED
BUFFER_POOL
ROW_MOVEMENT
GLOBAL_STATS
USER_STATS
DURATION
SKIP_CORRUPT
MONITORING
CLUSTER_OWNER
DEPENDENCIES
COMPRESSION
COMPRESS_FOR
DROPPED
READ_ONLY

PL/SQL procedure successfully completed

As you can see we were able to obtain the structure of the REF CURSOR and with a minimum effort. However, there are some pseudo-limitations when using this feature and the most important one is that, as soon as you convert the REF CURSOR into the DBMS_SQL cursor representation then you cannot switch back to REF CURSOR without explicit conversion. This means that a scenario in which you'd want to describe the REF CURSOR and after that you would like to continue with your REF CURSOR using FETCH, CLOSE etc. (REF CURSOR specific), those operations will simply not work. You'll have to do the whole further cursor handling using DBMS_SQL package. However, you can re-convert to the REF CURSOR using DBMS_SQL.TO_REFCURSOR. So, the DESCRIBE_REFCURSOR procedure can be modified like this:

SQL> create or replace procedure desc_refcursor(pio_cursor in out sys_refcursor) is
  2    l_cursor integer;
  3    l_cols_count integer;
  4    l_desc_tab dbms_sql.desc_tab;
  5  begin
  6    l_cursor := dbms_sql.to_cursor_number(pio_cursor);
  7    dbms_sql.describe_columns(l_cursor, l_cols_count, l_desc_tab);
  8    dbms_output.put_line('number of columns = ' || l_cols_count);
  9    dbms_output.new_line;
 10    dbms_output.put_line('DESCRIBE CURSOR');
 11    dbms_output.put_line('===========================');
 12    for i in 1..l_cols_count
 13    loop
 14      dbms_output.put_line(l_desc_tab(i).col_name);
 15    end loop;
 16    pio_cursor := dbms_sql.to_refcursor(l_cursor);
 17  end desc_refcursor;
 18  /

Now the invoking anonymous block can continue using ref cursor operations:

SQL> set serveroutput on
SQL> declare
  2    l_cursor sys_refcursor;
  3  begin
  4    open l_cursor for select * from cat;
  5    desc_refcursor(l_cursor);
  6    close l_cursor; -- REFCURSOR operation
  7  end;
  8  /

number of columns = 2

DESCRIBE CURSOR
===========================
TABLE_NAME
TABLE_TYPE

PL/SQL procedure successfully completed

Yes, that's it! Nice feature!

DBMS_XA and Four Eyes Principle

2007-11-04T07:28:00.000-08:00

I am simply amazed by the new DBMS_XA package available in Oracle 11g. It offers the possibility to define global transactions and to manage them from within different sessions using a 2PC (Two Phase Commit) approach. One of the great benefit I see now is for those systems which require the implementation of a "four eyes principle", that is somebody puts a transaction into the system but somebody else checks it and finally approves it. Without this approval that transaction is not visible which, from the business point of view, it's not committed.

Let's walk through a very simple and basic example.
First of all we'll need an Oracle user:

grant connect, resource, force any transaction to fep identified by xxx;

Pay attention to the special right "foce any transaction". It is needed in order to be able to manage transactions created by other users.
Now, lets create a simple table which is supposed to store invoices.


SQL> connect fep/xxx@testdb
Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0
Connected as fep

SQL>
SQL> create table invoices (
  2   inv_number integer,
  3   inv_text   varchar2(100),
  4   inv_date   date,
  5   inv_value  number(20, 6)
  6  );

Table created

Now, assume that somebody books in a new invoice. This means that a new record has to be added to the "INVOICES" table but, being subject to "four eyes principle" it must not be committed till somebody else will check it. In the DBMS_XA package light this should be done like this:


SQL> DECLARE
  2    l_rc  PLS_INTEGER;
  3    l_oer PLS_INTEGER;
  4    e_xae EXCEPTION;
  5  BEGIN
  6    l_rc := DBMS_XA.XA_START(DBMS_XA_XID(123), DBMS_XA.TMNOFLAGS);
  7 
  8    IF l_rc != DBMS_XA.XA_OK THEN
  9      RAISE e_xae;
 10    END IF;
 11 
 12    insert into invoices values (1, 'buy a TV', sysdate, 1000);
 13 
 14    l_rc := DBMS_XA.XA_END(DBMS_XA_XID(123), DBMS_XA.TMSUSPEND);
 15 
 16    IF l_rc != DBMS_XA.XA_OK THEN
 17      RAISE e_xae;
 18    END IF;
 19 
 20  EXCEPTION
 21    WHEN OTHERS THEN
 22      l_rc := DBMS_XA.XA_END(DBMS_XA_XID(123), DBMS_XA.TMSUCCESS);
 23      l_rc := DBMS_XA.XA_ROLLBACK(DBMS_XA_XID(123));
 24   
 25      IF l_rc != DBMS_XA.XA_OK THEN
 26        l_oer := DBMS_XA.XA_GETLASTOER();
 27        raise_application_error(-20001,
 28                                'ORA-' || l_oer ||
 29                                ' error in rolling back a failed transaction');
 30      END IF;
 31   
 32      raise_application_error(-20002,
 33                              'error in transaction processing, transaction rolled back');
 34  END;
 35  /

PL/SQL procedure successfully completed.

SQL> disconnect
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning and Real Application Testing options

Lets have a look at the above piece of code. At the 6th line a new global transaction is started. The 123 is the global transaction identifier and should be provided by the application and it is required to be unique. If the transaction has been successfully started then returning value must be DBMS_XA.XA_OK,otherwise an error has occurred. Then, the insert into our "INVOICES" table is made and the global transaction is simply suspended. Pay attention that no COMMIT is issued. Of course, it is important to note how the status of each DBMS_XA operation is checked and how the exception handler is written. Finally, the session is ended by effectively disconnecting.

Now, suppose that the second person must take over the previous transaction, to check it and to approve it. This can be done like shown below:


SQL> connect fep/xxx@testdb
Connected.
SQL> select * from invoices;

no rows selected

SQL> commit;

Commit complete.

SQL> set serveroutput on
SQL>
SQL> DECLARE
  2    l_rc  PLS_INTEGER;
  3    l_oer PLS_INTEGER;
  4    e_xae EXCEPTION;
  5    l_inv_no  invoices.inv_number%type;
  6    l_inv_dat invoices.inv_date%type;
  7    l_inv_txt invoices.inv_text%type;
  8    l_inv_val invoices.inv_value%type;
  9  BEGIN
 10    -- resume global transaction
 11    l_rc := DBMS_XA.XA_START(DBMS_XA_XID(123), DBMS_XA.TMRESUME);
 12    IF l_rc != DBMS_XA.XA_OK THEN
 13      RAISE e_xae;
 14    END IF;
 15 
 16    select *
 17      into l_inv_no, l_inv_txt, l_inv_dat, l_inv_val
 18      from invoices i
 19     where i.inv_number = 1;
 20 
 21    dbms_output.put_line('invoice number = ' || l_inv_no);
 22    dbms_output.put_line('invoice text = ' || l_inv_txt);
 23    dbms_output.put_line('invoice date = ' || l_inv_dat);
 24    dbms_output.put_line('invoice value = ' || l_inv_val);
 25 
 26    -- end global transaction
 27    l_rc := DBMS_XA.XA_END(DBMS_XA_XID(123), DBMS_XA.TMSUCCESS);
 28    IF l_rc != DBMS_XA.XA_OK THEN
 29      RAISE e_xae;
 30    END IF;
 31 
 32    -- commit global transaction
 33    l_rc := DBMS_XA.XA_COMMIT(DBMS_XA_XID(123), TRUE);
 34    IF l_rc != DBMS_XA.XA_OK THEN
 35      RAISE e_xae;
 36    END IF;
 37 
 38  EXCEPTION
 39    WHEN OTHERS THEN
 40      l_rc := DBMS_XA.XA_END(DBMS_XA_XID(123), DBMS_XA.TMSUCCESS);
 41      l_rc := DBMS_XA.XA_ROLLBACK(DBMS_XA_XID(123));
 42   
 43      IF l_rc != DBMS_XA.XA_OK THEN
 44        l_oer := DBMS_XA.XA_GETLASTOER();
 45        raise_application_error(-20001,
 46                                'ORA-' || l_oer ||
 47                                ' error in rolling back a failed transaction');
 48      END IF;
 49   
 50      raise_application_error(-20002,
 51                              'error in transaction processing, transaction rolled back');
 52  END;
 53  /
invoice number = 1
invoice text = buy a TV
invoice date = 04-NOV-07
invoice value = 1000

PL/SQL procedure successfully completed.

SQL> disconnect
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning and Real Application Testing options

Pay attention to the COMMIT statement just above the anonymous block. That's needed in order the anonymous block to start within a new fresh transaction, otherwise an error may occur.

So, using DBMS_XA package we were able to resume an uncommitted transaction made from another session, to see the data and to commit the changes. Of course, we can connect once again to see if the data is really committed.


SQL> connect fep/xxx@testdb
Connected.
SQL> set linesize 120
SQL> column inv_text format a10
SQL> select * from invoices;

INV_NUMBER INV_TEXT   INV_DATE   INV_VALUE
---------- ---------- --------- ----------
         1 buy a TV   04-NOV-07       1000

Lovely! The data is there!

However, there are some additional areas which I still have to investigate in order to validate this model for a MAYBE "four eyes principle" system:
1) where I can see all suspended XA transactions? it seems that DBA_PENDING_TRANSACTIONS, V$GLOBAL_TRANSACTION, DBA_2PC_PENDING or DBA_2PC_NEIGHBORS doesn't show any suspended transaction.
2) are the suspended global transactions persistent across database restarts? Apparently, as far as I could test, it's really not the case.
3) what about the global transaction timeout for a session? If a global transaction should never expire than we have a problem as I don't see any special value for the timeout parameter of the DBMS_XA.XA_SETTIMOUT function (e.g. zero means forever). So the maximum timeout we can set is the upper bound of the PLS_INTEGER type which is 2147483647. That means approximately 24855 days. However, without persistence across database restarts it's, anyway, useless.
4) no idea how this component behaves in a RAC configuration. For example starting a transaction on one instance and resuming it on another one.

PGA Diagnostics

2007-08-06T03:13:00.000-07:00

Do not forget a very important Oracle memory buffer which is the PGA (Program Global Area)! Most of us are tempted to look first of all at the SGA, especially at the library cache, whenever users complain that their SQLs run poorly. Of course, the library cache is a proper place to look at for problems, as well as the disk activity, CPU load and so many, many others. However, do NOT forget the PGA, because an inappropriate configuration can lead to a lot of performance issues.

So, reading through the "Database Performance Tuning Guide" I have found the section which talk about the tuning of the PGA. I am reading it quite often but it's still difficult to remember all the related fixed views and queries which are in connection with this topic. Therefore I have decided to put all the nice queries into a script which should give me a rough image of the PGA configuration.

Below is the script which I would like to share with all whom might be interested:


-- PGA Report
-- Displays various statistics regarding the PGA usage

set linesize 140
set pagesize 9999
set heading off
set feedback off

-- general statistics
select 'SECTION 1: GENERAL STATISTICS FOR THE PGA' from dual;
select '=================================================================='
  || '=========================' from dual;

set heading on
select name,
       decode(unit, 'bytes', round(value / 1024 / 1024, 2), value) value,
       decode(unit, 'bytes', 'MB', '') unit
  from v$pgastat;

set heading off
select 'Check the following:' from dual;
select '  - "aggregate PGA auto target" should not be too small in comparison ' 
  || 'with the "aggregate PGA target parameter"' from dual;
select '  - "global memory bound" should not be lower than 1M' from dual;
select '  - "over allocation count" should be near 0 or should not increasing in time' from dual;
select '  - the bigger "cache hit percentage", the better' from dual;
select '' from dual;

set heading on
SELECT name profile, cnt, decode(total, 0, 0, round(cnt*100/total)) percentage
    FROM (SELECT name, value cnt, (sum(value) over ()) total
    FROM V$SYSSTAT 
    WHERE name like 'workarea exec%');
set heading off

-- PGA used by all DB processes
select 'SECTION 2: PGA USED BY CURRENT DB PROCESSES (IN MB), ORDER BY PGA_ALLOC_MEM' from dual;
select '=================================================================='
  || '=========================' from dual;
set heading on

break on report;
compute sum label '--> TOTAL' of pga_used_mem on report;
compute sum label '--> TOTAL' of pga_alloc_mem on report;
compute sum label '--> TOTAL' of pga_freeable_mem on report;
compute sum label '--> TOTAL' of pga_max_mem on report;

SELECT PROGRAM,
       round(PGA_USED_MEM / 1024 / 1024, 2) pga_used_mem,
       round(PGA_ALLOC_MEM / 1024 / 1024, 2) pga_alloc_mem,
       round(PGA_FREEABLE_MEM / 1024 / 1024, 2) pga_freeable_mem,
       round(PGA_MAX_MEM / 1024 / 1024, 2) pga_max_mem
  FROM V$PROCESS
 order by pga_alloc_mem desc;
  
set heading off
select 'The columns have the following meaning:' from dual;
select '  - PGA_USED_MEM = PGA memory currently used by the process' from dual;
select '  - PGA_ALLOC_MEM = PGA memory currently allocated by the process (including free ' 
  || 'PGA memory not yet released to the operating system by the server process)' from dual;
select '  - PGA_FREEABLE_MEM = Allocated PGA memory which can be freed' from dual;
select '  - PGA_MAX_MEM = Maximum PGA memory ever allocated by the process' from dual;
select '' from dual;

set feedback off;
select 'SECTION 3: USED PGA MEMORY BY CATHEGORIES (VALUES IN MB ORDER DESC BY ALLOCATED_MB).' from dual;
select '=================================================================='
  || '=========================' from dual;
set heading on
select category,
       round(sum(allocated) / 1024 / 1024, 2) allocated_mb,
       round(sum(used) / 1024 / 1024, 2) used_mb,
       round(sum(max_allocated) / 1024 / 1024, 2) max_allocated_mb
  from v$process_memory
 group by category
 order by 2 desc;

set heading off
select '' from dual;

-- workareas histogram
select 'SECTION 4: SQL WORKAREAS HISTOGRAM' from dual;
select '=================================================================='
  || '=========================' from dual;
set heading on
SELECT LOW_OPTIMAL_SIZE/1024 low_kb,
       (HIGH_OPTIMAL_SIZE+1)/1024 high_kb,
       OPTIMAL_EXECUTIONS, ONEPASS_EXECUTIONS, MULTIPASSES_EXECUTIONS
  FROM V$SQL_WORKAREA_HISTOGRAM
 WHERE TOTAL_EXECUTIONS != 0;

set heading off
select '' from dual;

-- active workareas
select 'SECTION 5: CURRENTLY ACTIVE WORKAREAS' from dual;
select '=================================================================='
  || '=========================' from dual;
set heading on
set feedback on
SELECT to_number(decode(SID, 65535, NULL, SID)) sid,
       operation_type OPERATION,
       trunc(EXPECTED_SIZE/1024) ESIZE,
       trunc(ACTUAL_MEM_USED/1024) MEM,
       trunc(MAX_MEM_USED/1024) "MAX MEM",
       NUMBER_PASSES PASS,
       trunc(TEMPSEG_SIZE/1024/1024) TSIZE_MB
  FROM V$SQL_WORKAREA_ACTIVE
 ORDER BY 1,2;
set heading off
set feedback off
select 'The meaning of the above columns is:' from dual;
select '  - SID = the active session identifier' from dual;
select '  - OPERATION = the type of the operation' from dual;
select '  - ESIZE = the expected size for the sql workarea' from dual;
select '  - MEM = Amount of PGA memory (in KB) currently allocated on behalf of this work area.' from dual;
select '  - MAX MEM = Maximum memory amount (in KB) used by this work area' from dual;
select '  - PASS = Number of passes corresponding to this work area (0 if running in OPTIMAL mode)' from dual;
select '  - TSIZE_MB = Size (in megabytes) of the temporary segment used on behalf of this work area. '
  || 'This column is NULL if this work area has not (yet) spilled to disk.' from dual;
select '' from dual;

-- top 10 sql with gurmand sql areas
select 'SECTION 6: OP 10 WORK AREAS REQUIRING MOST CACHE MEMORY' from dual;
select '=================================================================='
  || '=========================' from dual;
set heading on
SELECT *
  FROM (SELECT distinct s.sql_id,
                        substr(s.SQL_TEXT, 1, 50) sql_text,
                        operation_type,
                        estimated_optimal_size,
                        max_tempseg_size
          FROM V$SQL_WORKAREA a, V$SQLSTATS s
         WHERE a.SQL_ID = s.SQL_ID
         ORDER BY estimated_optimal_size)
 WHERE ROWNUM <= 10;

set heading off
select 'SECTION 7: SQLs WITH WORK AREAS THAT HAVE BEEN EXECUTED IN ONE OR EVEN MULTIPLE PASSES' from dual;
select '=================================================================='
  || '=========================' from dual;
set heading on
set feedback on
SELECT sql_text, sum(ONEPASS_EXECUTIONS) onepass_cnt,
       sum(MULTIPASSES_EXECUTIONS) mpass_cnt 
FROM V$SQL s, V$SQL_WORKAREA wa 
WHERE s.address = wa.address 
GROUP BY sql_text 
HAVING sum(ONEPASS_EXECUTIONS+MULTIPASSES_EXECUTIONS)>0;
set feedback off
set heading off

select 'SECTION 8: PGA TARGET ADVCE' from dual;
select '=================================================================='
  || '=========================' from dual;
show parameter pga_aggregate_target;
set heading on
SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,
       ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,
       ESTD_OVERALLOC_COUNT
  FROM V$PGA_TARGET_ADVICE;


set heading off
select '' from dual;
pause press any key to exit...
exit

Have fun!

Automatic Shared Memory Management

2007-08-02T02:49:00.000-07:00

Well, it's been a while since the last post... However, I'm back and I am going to dig a little bit on the “Automatic Shared Memory Management” 10g feature.

As you already (probably) know, every Oracle instance has a big shared buffer called SGA (System Global Area). This big memory buffer is further divided in several slices, each of them being used for special kind of memory allocations. Till Oracle 9i, the only possibility to adjust their sizes were to manually set a couple of initialization parameters like: DB_CACHE_SIZE, SHARED_POOL_SIZE, JAVA_POOL_SIZE etc. However, in 10g some buffers from the SGA were endowed with a nice capability to resize themselves according to the current workload and other parameters.

The figure below depicts a regular section through the SGA.

As you can see the maximum size upon to which the whole SGA may extend is given by the SGA_MAX_SIZE parameter. However, the SGA_TARGET parameter is the one which actually set the amount of memory to be used by all SGA buffers.
The correlation between the values of the SGA_MAX_SIZE and the SGA_TARGET parameters is important mostly when you query the V$SGA_DYNAMIC_FREE_MEMORY.
Lets have a look onto the following output:


SQL> show parameter sga_target;

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
sga_target                           big integer                      1504M

SQL> select current_size/1024/1024 size_M from v$sga_dynamic_free_memory;

    SIZE_M
----------
       544

SQL> show parameter sga_max_size

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
sga_max_size                         big integer                      2G

At the very first sight you may think that from the SGA_TARGET which is 1504M, about 544M are free which, of course, is not true because the Oracle server will always try to expand SGA buffers so that the whole SGA_TARGET amount to be allocated. The free memory reported by the V$SGA_DYNAMIC_FREE_MEMORY view is the difference between the SGA_MAX_SIZE and the SGA_TARGET, which should be read as: “I still have 544M free from the whole SGA_MAX_SIZE which I can use for further expansions of the SGA_TARGET”.

A nice view which I also like to query when I want to find out a brief summary of the SGA composition is the V$SGAINFO.


SQL> select * from v$sgainfo;

NAME                                     BYTES RES
----------------------------------- ---------- ---
Fixed SGA Size                         2074152 No
Redo Buffers                          14700544 No
Buffer Cache Size                    671088640 Yes
Shared Pool Size                     838860800 Yes
Large Pool Size                       16777216 Yes
Java Pool Size                        16777216 Yes
Streams Pool Size                     16777216 Yes
Granule Size                          16777216 No
Maximum SGA Size                    2147483648 No
Startup overhead in Shared Pool      100663296 No
Free SGA Memory Available            570425344

11 rows selected.

Please notice that I can also look at the size of the so-called “granules”, which are the allocation units for the SGA memory. The size of one granule varies according to the whole size of the SGA, operation system and platform. You can also find the size of the granule by querying the V$SGA_DYNAMIC_COMPONENTS.


SQL> select granule_size from v$sga_dynamic_components where rownum <= 1;

GRANULE_SIZE
------------
    16777216

Another important thing to remember is that if you set values for the parameters which controls the size of the dynamic buffers into the SGA then, those values will be taken as minimum thresholds which means that Oracle server will try to adjust the size of the buffers when necessary but will never decrease them under the manually set value.
Of course, there is also the possibility to disable the “Automatic Shared Memory Management” feature by simply setting the SGA_TARGET value to zero. Likewise, this feature will also be disabled if the STATISTICS_LEVEL parameter is NONE, but this is not recommended at all as it disables other Oracle features too (ADDM, for example).

Well, that's it... not very new information but just a short review.

/etc/oratab or /etc/rc.d/rc3.d/S99gcstartup?

2007-06-21T06:46:00.000-07:00

A few days ago I had updated the Oracle Grid Control and its database located on a RedHat AS4 server. Everything worked fine till the server was rebooted and I had the “nice” surprise to see that the repository database was restarted but using the old ORACLE_HOME. At this point it is important to review the steps I had followed as part of the upgrading task:

1. stop agent


10ag
emctl stop agent

2. stop opmn stack


10gr
$ORACLE_HOME/opmn/bin/opmnctl stopall

3. stop listener


10db
lsnrctl stop

4. stop database


sqlplus / as sysdba
shutdown immediate

5. backup agent, database, oms homes


cp -r /opt/oracle/product/10.2.0/agent /opt/oracle/backup/
cp -r /opt/oracle/product/10.2.0/db10g /opt/oracle/backup/
cp -r /opt/oracle/product/10.2.0/oms10g /opt/oracle/backup/

6. apply patch for 4329444


oracle@aut-vie-racman:kits$ unzip p4329444_10104_LINUX.zip
Archive: p4329444_10104_LINUX.zip
creating: 4329444/
creating: 4329444/files/
creating: 4329444/files/lib/
creating: 4329444/files/lib/libserver10.a/
inflating: 4329444/files/lib/libserver10.a/qerix.o
creating: 4329444/etc/
creating: 4329444/etc/config/
inflating: 4329444/etc/config/inventory
inflating: 4329444/etc/config/actions
creating: 4329444/etc/xml/
inflating: 4329444/etc/xml/GenericActions.xml
inflating: 4329444/etc/xml/ShiphomeDirectoryStructure.xml
inflating: 4329444/README.txt
oracle@aut-vie-racman:kits$ cd 4329444/
oracle@aut-vie-racman:4329444$ /opt/oracle/product/10.2.0/db10g/OPatch/opatch apply
Invoking OPatch 10.2.0.1.0
Oracle interim Patch Installer version 10.2.0.1.0
Copyright (c) 2005, Oracle Corporation. All rights reserved..
Oracle Home : /opt/oracle/product/10.2.0/db10g
Central Inventory : /opt/oracle/oraInventory
from : /opt/oracle/product/10.2.0/db10g/oraInst.loc
OPatch version : 10.2.0.1.0
OUI version : 10.2.0.1.0
OUI location : /opt/oracle/product/10.2.0/db10g/oui
Log file location : /opt/oracle/product/10.2.0/db10g/cfgtoollogs/opatch/opatch-
2007_Jun_19_16-16-19-CEST_Tue.log
ApplySession applying interim patch '4329444' to OH
'/opt/oracle/product/10.2.0/db10g'
Invoking fuser to check for active processes.
Invoking fuser on "/opt/oracle/product/10.2.0/db10g/bin/oracle"
OPatch detected non-cluster Oracle Home from the inventory and will patch the local
system only.
Please shutdown Oracle instances running out of this ORACLE_HOME on the local
system.
(Oracle Home = '/opt/oracle/product/10.2.0/db10g')
Is the local system ready for patching?
Do you want to proceed? [y|n]
y
User Responded with: Y
Backing up files and inventory (not for auto-rollback) for the Oracle Home
Backing up files affected by the patch '4329444' for restore. This might take a
while...
Backing up files affected by the patch '4329444' for rollback. This might take a
while...
Patching component oracle.rdbms, 10.1.0.4.0...
Updating archive file "/opt/oracle/product/10.2.0/db10g/lib/libserver10.a" with
"lib/libserver10.a/qerix.o"
Running make for target ioracle
ApplySession adding interim patch '4329444' to inventory
The local system has been patched and can be restarted.
OPatch succeeded.

7. Start db listener


oracle@aut-vie-racman:4329444$ 10db
oracle@aut-vie-racman:4329444$ lsnrctl start
LSNRCTL for Linux: Version 10.1.0.4.0 - Production on 19-JUN-2007 16:18:42
Copyright (c) 1991, 2004, Oracle. All rights reserved.
Starting /opt/oracle/product/10.2.0/db10g/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 10.1.0.4.0 - Production
System parameter file is /opt/oracle/product/10.2.0/db10g/network/admin/listener.ora
Log messages written to /opt/oracle/product/10.2.0/db10g/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=aut-vieracman.
bankgutmann.co.at)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.1.0.4.0 - Production
Start Date 19-JUN-2007 16:18:43
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File
/opt/oracle/product/10.2.0/db10g/network/admin/listener.ora
Listener Log File /opt/oracle/product/10.2.0/db10g/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=aut-vieracman.
bankgutmann.co.at)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

8. start emrep database


oracle@aut-vie-racman:4329444$ sqlplus / as sysdba
SQL*Plus: Release 10.1.0.4.0 - Production on Tue Jun 19 16:20:27 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 536870912 bytes
Fixed Size 780056 bytes
Variable Size 275781864 bytes
Database Buffers 260046848 bytes
Redo Buffers 262144 bytes
Database mounted.
Database opened.

9. Patch OMS.
10. Patch agent
11. Start agent


10ag
emctl start agent

12. Start oms


10gr
$ORACLE_HOME/opmn/bin/opmnctl startall

The Grid Control was patched and it worked just fine. The only problem was that the database
remained at 10.1.0.4.0 version. So, I decided to upgrade the repository database too, following the steps below:

1. stop agent
2. stop oms
3. stop database
4. stop listener
5. install 10.2.0.1 Oracle software into a new home
6. upgrade to 10.2.0.3 the Oracle software
7. upgrade the database using “dbua”
8. start oms
9. start agent

Everything was normal until today when we had to shutdown the server for maintenance reasons. Unfortunately, on reboot the repository database continued to be started under the previous 10.1.0.4 oracle home and, of course, because of this the OEM couldn't be used. I had a look into /etc/oratab file and the entries were fine. The database was supposed to be started from the new upgraded ORACLE_HOME!!! Furthermore, changing the /etc/oratab file seemed to have no effect. In the end, I found the reason. Under the /etc/rc.d/rc3.d/ directory there is a script called S99gcstartup. This script calls the startup scripts for repository database, OMS and for the agent. It's enough to copy the startup script for the database under the new ORACLE_HOME directory and to change the ORACLE_HOME variable which is declared in that script. After these changes the repository database was/is started up with the correct ORACLE_HOME on every reboot.

Losing One Voting Disk

2007-06-08T07:14:00.000-07:00

Voting disks are used in a RAC configuration for maintaining nodes membership. They are critical pieces in a cluster configuration. Starting with ORACLE 10gR2, it is possible to mirror the OCR and the voting disks. Using the default mirroring template, the minimum number of voting disks necessary for a normal functioning is two.

Scenario Setup

In this scenario it is simulated the crash of one voting disk by using the following steps:

identify votings:

crsctl query css votedisk

0. 0 /dev/raw/raw1

1. 0 /dev/raw/raw2

2. 0 /dev/raw/raw3

corrupt one of the voting disks (as root):

dd if=/dev/zero /dev/raw/raw3 bs=1M

Recoverability Steps

check the “$CRS_HOME/log/[hostname]/alert[hostname].log” file. The following message should be written there which allows us to determine which voting disk became corrupted:

[cssd(9120)]CRS-1604:CSSD voting file is offline: /opt/oracle/product/10.2.0/crs_1/Voting1. Details in /opt/oracle/product/10.2.0/crs_1/log/aut-arz-ractest1/cssd/ocssd.log.
According to the above listing the Voting1 is the corrupted disk. Shutdown the CRS stack:

srvctl stop database -d fitstest -o immediate

srvctl stop asm -n aut-vie-ractest1

srvctl stop asm -n aut-arz-ractest1

srvctl stop nodeapps -n aut-vie-ractest1

srvctl stop nodeapps -n aut-arz-ractest1

crs_stat -t

On every node as root:

crsctl stop crs
Pick a good voting from the remaining ones and copy it over the corrupted one:

dd if=/dev/raw/raw4 of=/dev/raw/raw3 bs=1M
Start CRS (on every node as root):
Check log file “$CRS_HOME/log/[hostname]/alert[hostname].log”. It should look like shown below:

[cssd(14463)]CRS-1601:CSSD Reconfiguration complete. Active nodes are aut-vie-ractest1 aut-arz-ractest1 .

2007-05-31 15:19:53.954

[crsd(14268)]CRS-1012:The OCR service started on node aut-vie-ractest1.

2007-05-31 15:19:53.987

[evmd(14228)]CRS-1401:EVMD started on node aut-vie-ractest1.

2007-05-31 15:19:55.861 [crsd(14268)]CRS-1201:CRSD started on node aut-vie-ractest1.
After a couple of minutes check the status of the whole CRS stack:

[oracle@aut-vie-ractest1 ~]$ crs_stat -t

Name Type Target State Host

------------------------------------------------------------

ora....SM2.asm application ONLINE ONLINE aut-...est1

ora....T1.lsnr application ONLINE ONLINE aut-...est1

ora....st1.gsd application ONLINE ONLINE aut-...est1

ora....st1.ons application ONLINE ONLINE aut-...est1

ora....st1.vip application ONLINE ONLINE aut-...est1

ora....SM1.asm application ONLINE ONLINE aut-...est1

ora....T1.lsnr application ONLINE ONLINE aut-...est1

ora....st1.gsd application ONLINE ONLINE aut-...est1

ora....st1.ons application ONLINE ONLINE aut-...est1

ora....st1.vip application ONLINE ONLINE aut-...est1

ora....test.db application ONLINE ONLINE aut-...est1

ora....t1.inst application ONLINE ONLINE aut-...est1

ora....t2.inst application ONLINE ONLINE aut-...est1

Note: There's also possible to recover a lost voting disk from an old voting backup and to perform the “dd” command without shutting down the CRS stack.

select * from XML

2007-02-05T00:51:00.000-08:00

A few days ago, one of our programmers asked me how he can call a procedure from the database passing as a parameter an array of records. Oracle provides this feature on the server side by using, for example, collections of objects, but building such collections on the client side and passing them as parameters is not a trivial task at all (I don’t even know if this is possible with complex types, especially if we are taking into consideration that we use IBATIS to handle database connections, statements and so on, which of course makes this attempt more difficult). However, the first thing which came into my mind was the usage of XML. In the light of this approach, the client side has to build up a XML and pass it to the invoked procedure as a replacement of an array of records. This is not difficult as far as the client csharp code is concerned and, of course, is not a big deal to write a PL/SQL procedure which expects a CLOB parameter, which actually represents the XML content. However, being a SQL guy I must admit that I don’t feel very comfortable to manually parse the XML and to do a lot of DBMS_XML stuff in order to get the data from that XML. Instead, I would rather like to see the XML content as a regular table. Below is how you can do the trick:


  SELECT id, value
   FROM XMLTABLE('/ROWSET/ROW'
      PASSING xmltype('
      <ROWSET>
        <ROW>
          <ID>1</ID>
          <VALUE>aaa</VALUE>
        </ROW>
        <ROW>
          <ID>2</ID>
          <VALUE>bbb</VALUE>
        </ROW>
      </ROWSET>')
   COLUMNS 
           id INTEGER PATH 'ID',
           value VARCHAR2(30) PATH 'VALUE') src;

If you want to embed the above piece of code within a procedure with the XML taken as parameter then it’s just of matter of replacing the actual plain XML content with the corresponding CLOB parameter.


  SELECT id, value
   FROM XMLTABLE('/ROWSET/ROW'
      PASSING xmltype(p_clob_xml)
   COLUMNS 
           id INTEGER PATH 'ID',
           value VARCHAR2(30) PATH 'VALUE') src;

I have tested this on an Oracle 10.2.0.2 server. It might work on previous releases but I cannot offer any guarantees. Happy XMLing! :P

Get the “tnsnames.ora” from OPENLDAP

2007-01-30T02:18:00.000-08:00

One thing which really bothers me is when I have to go to every client machine and I must edit the local “tnsnames.ora” file. If you have to deal with just a few clients is not a big issue but as soon as the number of clients exceed five or ten, this approach becomes ugly. The most convenient way would be to have these connection descriptors into a central place and every client to query that repository in order to get the requested descriptor. We have at least two possibilities here: to use “oranames” which is obsolete or to use a LDAP server as the recommended way. Oracle provides its own LDAP server known as OID (Oracle Internet Directory) but it is part of the Advanced Security option and you have to pay for it. In this case, when the whole goal is to use a LDAP server just as a replacement for the local “tnsnames.ora”, it’s not affordable to buy such a wide option for such a tiny functionality. We’ll use instead a free LDAP server which runs seemly on every UNIX platform and it is called OPENLDAP (there are OPENLDAP distributions for Windows as well).

So, the first thing is to install the required packages. You’ll need: openldap, opeldap-servers and openldap-clients. On my Fedora server I can query the RPM repository as shown below:


root@ssafe:~# rpm -qa | grep openldap
openldap-2.3.19-4
openldap-devel-2.3.19-4
openldap-clients-2.3.19-4

As you can see the openldap-servers package is missing therefore we have to install it. We’ll use YUM to do this:


root@ssafe:~# yum install openldap-servers
Loading "installonlyn" plugin
Setting up Install Process
Setting up repositories
core                                                                 [1/3]
extras                                                               [2/3]
updates                                                              [3/3]
Reading repository metadata in from local files
Parsing package install arguments
Resolving Dependencies
--> Populating transaction set with selected packages. Please wait.
---> Package openldap-servers.i386 0:2.3.19-4 set to be updated
--> Running transaction check

Dependencies Resolved

=============================================================================
Package                 Arch       Version          Repository        Size
=============================================================================
Installing:
openldap-servers        i386       2.3.19-4         core              2.2 M

Transaction Summary
=============================================================================
Install      1 Package(s)
Update       0 Package(s)
Remove       0 Package(s)
Total download size: 2.2 M
Is this ok [y/N]: y
Downloading Packages:
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
Installing: openldap-servers             ######################### [1/1]

Installed: openldap-servers.i386 0:2.3.19-4
Complete!

Now, we have a nice LDAP server installed on our server. The next step is to configure it. Oracle will need some special LDAP schemas which must be loaded into our server. These schema files are: oidbase.schema, oidrdbms.schema and oidnet.schema. Save them into a new created directory called, let’s say, “oracle-ldap”. Below is the content of the needed files:
1. oidbase.schema:

attributetype ( 2.16.840.1.113894.7.1.1 NAME 'orclVersion' EQUALITY caseIgnoreMatch SYNTAX '1.3.6.1.4.1.1466.115.121.1.15' SINGLE-VALUE )

attributetype ( 2.16.840.1.113894.7.1.2 NAME 'orclOracleHome' SYNTAX '1.3.6.1.4.1.1466.115.121.1.15' SINGLE-VALUE )

attributetype ( 2.16.840.1.113894.7.1.3 NAME 'orclSystemName' EQUALITY caseIgnoreMatch SYNTAX '1.3.6.1.4.1.1466.115.121.1.15' SINGLE-VALUE )

attributetype ( 2.16.840.1.113894.7.1.4 NAME 'orclServiceType' EQUALITY caseIgnoreMatch SYNTAX '1.3.6.1.4.1.1466.115.121.1.15' SINGLE-VALUE )

attributetype ( 2.16.840.1.113894.7.1.5 NAME 'orclSid' EQUALITY caseIgnoreMatch SYNTAX '1.3.6.1.4.1.1466.115.121.1.15' SINGLE-VALUE )

attributetype ( 2.16.840.1.113894.7.1.6 NAME 'orclProductVersion' EQUALITY caseIgnoreMatch SYNTAX '1.3.6.1.4.1.1466.115.121.1.15' )

objectClass ( 2.16.840.1.113894.7.2.2 NAME 'orclContainer' SUP 'top' STRUCTURAL MUST ( cn ) )

objectClass ( 2.16.840.1.113894.7.2.3 NAME 'orclContext' SUP 'top' STRUCTURAL MUST ( cn ) )

objectClass ( 2.16.840.1.113894.7.2.6 NAME 'orclSchemaVersion' SUP 'top' STRUCTURAL MUST ( cn $ orclProductVersion ) )

attributetype ( 2.16.840.1.113894.3.1.12 NAME 'orclNetDescName' EQUALITY distinguishedNameMatch SYNTAX '1.3.6.1.4.1.1466.115.121.1.12' SINGLE-VALUE )

attributetype ( 2.16.840.1.113894.3.1.13 NAME 'orclNetDescString' EQUALITY caseIgnoreMatch SYNTAX '1.3.6.1.4.1.1466.115.121.1.15' SINGLE-VALUE )

objectClass ( 2.16.840.1.113894.7.2.1001 NAME 'orclService' SUP 'top' STRUCTURAL MUST ( cn ) MAY ( orclServiceType $ orclOracleHome $ orclSystemName $ orclSid $ orclNetDescName $ orclNetDescString $ orclVersion $ Description ) )

2. oidrdbms.schema file:

attributetype ( 2.16.840.1.113894.2.1.1 NAME 'orclDBtrustedUser' EQUALITY distinguishedNameMatch SYNTAX '1.3.6.1.4.1.1466.115.121.1.12' )

attributetype ( 2.16.840.1.113894.2.1.2 NAME 'orclDBServerMember' EQUALITY distinguishedNameMatch SYNTAX '1.3.6.1.4.1.1466.115.121.1.12' )

attributetype ( 2.16.840.1.113894.2.1.3 NAME 'orclDBEntUser' EQUALITY distinguishedNameMatch SYNTAX '1.3.6.1.4.1.1466.115.121.1.12' )

attributetype ( 2.16.840.1.113894.2.1.4 NAME 'orclDBEntRoleAssigned' EQUALITY distinguishedNameMatch SYNTAX '1.3.6.1.4.1.1466.115.121.1.12' )

attributetype ( 2.16.840.1.113894.2.1.5 NAME 'orclDBServerRole' EQUALITY caseIgnoreMatch SYNTAX '1.3.6.1.4.1.1466.115.121.1.15' )

attributetype ( 2.16.840.1.113894.2.1.6 NAME 'orclDBTrustedDomain' EQUALITY caseIgnoreMatch SYNTAX '1.3.6.1.4.1.1466.115.121.1.15' SINGLE-VALUE )

attributetype ( 2.16.840.1.113894.2.1.7 NAME 'orclDBRoleOccupant' EQUALITY distinguishedNameMatch SYNTAX '1.3.6.1.4.1.1466.115.121.1.12' )

attributetype ( 2.16.840.1.113894.2.1.8 NAME 'orclDBDistinguishedName' EQUALITY distinguishedNameMatch SYNTAX '1.3.6.1.4.1.1466.115.121.1.12' SINGLE-VALUE )

attributetype ( 2.16.840.1.113894.2.1.9 NAME 'orclDBNativeUser' EQUALITY caseIgnoreMatch SYNTAX '1.3.6.1.4.1.1466.115.121.1.15' SINGLE-VALUE )

attributetype ( 2.16.840.1.113894.2.1.10 NAME 'orclDBGlobalName' EQUALITY caseIgnoreMatch SYNTAX '1.3.6.1.4.1.1466.115.121.1.15' SINGLE-VALUE )

objectClass ( 2.16.840.1.113894.2.2.1 NAME 'orclDBServer' SUP 'orclService' STRUCTURAL MAY ( userCertificate $ orclDBtrustedUser $ orclDBGlobalName ) )

objectClass ( 2.16.840.1.113894.2.2.2 NAME 'orclDBEnterpriseDomain' SUP top STRUCTURAL MUST cn MAY ( orclDBServerMember $ orclDBEntUser $ orclDBTrustedDomain ) )

objectClass ( 2.16.840.1.113894.2.2.3 NAME 'orclDBEnterpriseRole' SUP top STRUCTURAL MUST cn MAY ( orclDBServerRole $ orclDBEntRoleAssigned $ description $ seeAlso $ o $ ou $ orclDBRoleOccupant ) )

objectClass ( 2.16.840.1.113894.2.2.4 NAME 'orclDBEntryLevelMapping' SUP top STRUCTURAL MUST cn MAY ( orclDBDistinguishedName $ orclDBNativeUser ) )

objectClass ( 2.16.840.1.113894.2.2.5 NAME 'orclDBSubtreeLevelMapping' SUP top STRUCTURAL MUST cn MAY ( orclDBDistinguishedName $ orclDBNativeUser ) )

3. oidnet.schema file:

attributetype ( 2.16.840.1.113894.3.1.1 NAME 'orclNetSourceRoute' SYNTAX '1.3.6.1.4.1.1466.115.121.1.5' SINGLE-VALUE )

attributetype ( 2.16.840.1.113894.3.1.2 NAME 'orclNetLoadBalance' SYNTAX '1.3.6.1.4.1.1466.115.121.1.5' SINGLE-VALUE )

attributetype ( 2.16.840.1.113894.3.1.3 NAME 'orclNetFailover' SYNTAX '1.3.6.1.4.1.1466.115.121.1.5' SINGLE-VALUE )

attributetype ( 2.16.840.1.113894.3.1.4 NAME 'orclNetSdu' EQUALITY caseIgnoreMatch SYNTAX '1.3.6.1.4.1.1466.115.121.1.15' SINGLE-VALUE )

attributetype ( 2.16.840.1.113894.3.1.5 NAME 'orclNetServer' EQUALITY caseIgnoreMatch SYNTAX '1.3.6.1.4.1.1466.115.121.1.15' SINGLE-VALUE )

attributetype ( 2.16.840.1.113894.3.1.6 NAME 'orclNetServiceName' EQUALITY caseIgnoreMatch SYNTAX '1.3.6.1.4.1.1466.115.121.1.15' SINGLE-VALUE )

attributetype ( 2.16.840.1.113894.3.1.7 NAME 'orclNetInstanceName' EQUALITY caseIgnoreMatch SYNTAX '1.3.6.1.4.1.1466.115.121.1.15' SINGLE-VALUE )

attributetype ( 2.16.840.1.113894.3.1.8 NAME 'orclNetHandlerName' EQUALITY caseIgnoreMatch SYNTAX '1.3.6.1.4.1.1466.115.121.1.15' SINGLE-VALUE )

attributetype ( 2.16.840.1.113894.3.1.9 NAME 'orclNetParamList' EQUALITY caseIgnoreMatch SYNTAX '1.3.6.1.4.1.1466.115.121.1.15' )

attributetype ( 2.16.840.1.113894.3.1.10 NAME 'orclNetAuthenticationType' EQUALITY caseIgnoreMatch SYNTAX '1.3.6.1.4.1.1466.115.121.1.15' SINGLE-VALUE )

attributetype ( 2.16.840.1.113894.3.1.11 NAME 'orclNetAuthParams' EQUALITY caseIgnoreMatch SYNTAX '1.3.6.1.4.1.1466.115.121.1.15' SINGLE-VALUE )

attributetype ( 2.16.840.1.113894.3.1.14 NAME 'orclNetAddressString' EQUALITY caseIgnoreMatch SYNTAX '1.3.6.1.4.1.1466.115.121.1.15' SINGLE-VALUE )

attributetype ( 2.16.840.1.113894.3.1.15 NAME 'orclNetProtocol' EQUALITY caseIgnoreMatch SYNTAX '1.3.6.1.4.1.1466.115.121.1.15' SINGLE-VALUE )

attributetype ( 2.16.840.1.113894.3.1.16 NAME 'orclNetShared' EQUALITY caseIgnoreMatch SYNTAX '1.3.6.1.4.1.1466.115.121.1.15' SINGLE-VALUE )

attributetype ( 2.16.840.1.113894.3.1.17 NAME 'orclNetAddrList' EQUALITY caseIgnoreMatch SYNTAX '1.3.6.1.4.1.1466.115.121.1.15' )

attributetype ( 2.16.840.1.113894.3.1.18 NAME 'orclNetProtocolStack' EQUALITY caseIgnoreMatch SYNTAX '1.3.6.1.4.1.1466.115.121.1.15' SINGLE-VALUE )

attributetype ( 2.16.840.1.113894.3.1.19 NAME 'orclNetDescList' EQUALITY caseIgnoreMatch SYNTAX '1.3.6.1.4.1.1466.115.121.1.15' )

attributetype ( 2.16.840.1.113894.3.1.20 NAME 'orclNetConnParamList' EQUALITY caseIgnoreMatch SYNTAX '1.3.6.1.4.1.1466.115.121.1.15' )

attributetype ( 2.16.840.1.113894.3.1.21 NAME 'orclNetAuthenticationService' EQUALITY caseIgnoreMatch SYNTAX '1.3.6.1.4.1.1466.115.121.1.15' SINGLE-VALUE )

objectClass ( 2.16.840.1.113894.3.2.5 NAME 'orclNetService' SUP 'top' STRUCTURAL MUST ( cn ) MAY ( orclNetDescName $ orclNetDescString $ orclVersion $ Description ) )

objectClass ( 2.16.840.1.113894.3.2.4 NAME 'orclNetDescriptionList' SUP 'top' STRUCTURAL MUST ( cn ) MAY ( orclNetDescList $ orclNetSourceRoute $ orclNetLoadBalance $ orclNetFailover $ orclNetShared $ orclVersion $ Description ) )

objectClass ( 2.16.840.1.113894.3.2.3 NAME 'orclNetDescription' SUP 'top' STRUCTURAL MUST ( cn ) MAY ( orclNetAddrList $ orclNetProtocolStack $ orclNetSdu $ orclSid $ orclNetServer $ orclNetServiceName $ orclNetInstanceName $ orclNetHandlerName $ orclOracleHome $ orclNetAuthenticationType $ orclNetAuthenticationService $ orclNetAuthParams $ orclNetParamList $ orclNetConnParamList $ orclNetSourceRoute $ orclNetLoadBalance $ orclNetFailover $ orclNetShared $ orclVersion $ Description ) )

objectClass ( 2.16.840.1.113894.3.2.2 NAME 'orclNetAddressList' SUP 'top' STRUCTURAL MUST ( cn ) MAY ( orclNetAddrList $ orclNetSourceRoute $ orclNetLoadBalance $ orclNetFailover $ orclNetShared $ orclVersion $ Description ) )

objectClass ( 2.16.840.1.113894.3.2.1 NAME 'orclNetAddress' SUP 'top' STRUCTURAL MUST ( cn ) MAY ( orclNetAddressString $ orclNetProtocol $ orclNetShared $ orclVersion $ Description ) )

Then, edit the slapd.conf file. On my installation this file can be found under /etc/openldap directory. Before changing something it is wise to make a copy of the original slapd.conf file.

cp /etc/openldap/slapd.conf /etc/openldap/slapd.conf.bak

Now, open the /etc/openldap/slapd.conf file and change it as following (see the bolded lines):


#
# See slapd.conf(5) for details on configuration options.
# This file should NOT be world readable.
#
include     /etc/openldap/schema/core.schema
include     /etc/openldap/schema/cosine.schema
include     /etc/openldap/schema/inetorgperson.schema
include     /etc/openldap/schema/nis.schema

include /oracle-ldap/oidbase.schema
include /oracle-ldap/oidrdbms.schema
include /oracle-ldap/oidnet.schema


# Allow LDAPv2 client connections.  This is NOT the default.
allow bind_v2

# Do not enable referrals until AFTER you have a working directory
# service AND an understanding of referrals.
#referral   ldap://root.openldap.org

pidfile     /var/run/openldap/slapd.pid
argsfile    /var/run/openldap/slapd.args

# Load dynamic backend modules:
# modulepath    /usr/lib/openldap
# moduleload    back_bdb.la
# moduleload    back_ldap.la
# moduleload    back_ldbm.la
# moduleload    back_passwd.la
# moduleload    back_shell.la

# The next three lines allow use of TLS for encrypting connections using a
# dummy test certificate which you can generate by changing to
# /etc/pki/tls/certs, running "make slapd.pem", and fixing permissions on
# slapd.pem so that the ldap user or group can read it.  Your client software
# may balk at self-signed certificates, however.
# TLSCACertificateFile /etc/pki/tls/certs/ca-bundle.crt
# TLSCertificateFile /etc/pki/tls/certs/slapd.pem
# TLSCertificateKeyFile /etc/pki/tls/certs/slapd.pem

# Sample security restrictions
#   Require integrity protection (prevent hijacking)
#   Require 112-bit (3DES or better) encryption for updates
#   Require 63-bit encryption for simple bind
# security ssf=1 update_ssf=112 simple_bind=64

# Sample access control policy:
#   Root DSE: allow anyone to read it
#   Subschema (sub)entry DSE: allow anyone to read it
#   Other DSEs:
#       Allow self write access
#       Allow authenticated users read access
#       Allow anonymous users to authenticate
#   Directives needed to implement policy:
# access to dn.base="" by * read
# access to dn.base="cn=Subschema" by * read

access to *


#   by self write
#   by users read

by anonymous auth


#
# if no access controls are present, the default policy
# allows anyone and everyone to read anything but restricts
# updates to rootdn.  (e.g., "access to * by * read")
#
# rootdn can always read and write EVERYTHING!

#######################################################################
# ldbm and/or bdb database definitions
#######################################################################

database    bdb

suffix "dc=itfits,dc=biz"
rootdn "cn=Manager,dc=itfits,dc=biz"


# Cleartext passwords, especially for the rootdn, should
# be avoided.  See slappasswd(8) and slapd.conf(5) for details.
# Use of strong authentication encouraged.

rootpw secret


# rootpw        {crypt}ijFYNcSNctBYg

# The database directory MUST exist prior to running slapd AND
# should only be accessible by the slapd and slap tools.
# Mode 700 recommended.
directory   /var/lib/ldap

# Indices to maintain for this database
index objectClass                       eq,pres
index ou,cn,mail,surname,givenname      eq,pres,sub
index uidNumber,gidNumber,loginShell    eq,pres
index uid,memberUid                     eq,pres,sub
index nisMapName,nisMapEntry            eq,pres,sub

# Replicas of this database
#replogfile /var/lib/ldap/openldap-master-replog
#replica host=ldap-1.example.com:389 starttls=critical
#     bindmethod=sasl saslmech=GSSAPI
#     authcId=host/ldap-master.example.com@EXAMPLE.COM

The “suffix” and “rootdn” should be changed according to your specific domain. It’s time to start the “slapd” daemon by simply typing slapd . Now our LDAP server is ready for incoming requests but we’ll have to create the initial structure of the LDAP tree. In order to do this we’ll need some so called “ldif” files which will be used to specify LDAP entries along with their attributes.

Let’s create the following ldif files:
1. base.ldif, with the following content:
dn: dc=itfits,dc=biz
objectclass: dcObject
objectclass: organization
o: ITFITS Network
dc: itfits
2. manager.ldif with the following content:
dn: cn=Manager,dc=itfits,dc=biz
objectClass: organizationalRole
cn: Manager
3. OracleContext.ldif with the following content:
dn: cn=OracleContext,dc=itfits,dc=biz
objectclass: orclContext
cn: OracleContext
4. pdmdb.ldif with the following content:
dn: cn=pdmdb,cn=OracleContext,dc=itfits,dc=biz
objectclass: top
objectclass: orclNetService
cn: pdmdb
orclNetDescString: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ops)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=PDMDB)))

After we have created the above files we must use them to alter the LDAP tree structure:


root@ssafe:oracle-ldap# ldapadd -x -D "cn=Manager,dc=itfits,dc=biz" -w secret -f base.ldif
adding new entry "dc=itfits,dc=biz"

root@ssafe:oracle-ldap# ldapadd -x -D "cn=Manager,dc=itfits,dc=biz" -w secret -f manager.ldif
adding new entry "cn=Manager,dc=itfits,dc=biz"

root@ssafe:oracle-ldap# ldapadd -x -D "cn=Manager,dc=itfits,dc=biz" -w secret -f OracleContext.ldif
adding new entry "cn=OracleContext,dc=itfits,dc=biz"

root@ssafe:oracle-ldap# ldapadd -x -D "cn=Manager,dc=itfits,dc=biz" -w secret -f pdmdb.ldif
adding new entry "cn=pdmdb,cn=OracleContext,dc=itfits,dc=biz"

Oookey, the LDAP server is ready to serve our oracle clients. However, our oracle clients might not be prepared for talking with a LDAP server therefore we should spend some time to configure them. It is always recommended to use the provided graphical tools therefore we’ll use “netca”. Choose “Naming Method configuration” and add “Directory naming” from the available methods as a “Selected Naming” method. The “Directory naming” should be the only selected method or at least should be the first one.
Perfect! Now, we must create an “$ORACLE_HOME/network/admin/ldap.ora” file, with the following content:


DIRECTORY_SERVERS= (ssafe:389:636)
DEFAULT_ADMIN_CONTEXT = "dc=itfits,dc=biz"
DIRECTORY_SERVER_TYPE = OID

“ssafe” is the DNS name of the machine where the LDAP server was installed.

Now, let’s test the configuration:
C:\Documents and Settings\alec>tnsping pdmdb

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 30-JAN-2007 12:07:27

Copyright (c) 1997, 2005, Oracle. All rights reserved.

Used parameter files:
C:\oracle\product\10.2.0\client_1\network\admin\sqlnet.ora

Used LDAP adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ops)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=PDMDB)))
OK (0 msec)

Lovely! From now on our oracle clients will get the network descriptors from the LDAP repository. No more “tnsnames.ora” is required.

Recreating the Database Control Repository

2007-01-22T00:02:00.000-08:00

I don’t know why, but after I upgraded my database to 10.2.0.2, the DB Console started to behave quite unusual. For example, I was not able to submit backup jobs because it was complaining that the host privileges were not set up. Trying to manually set those privileges within the “Preferences/Host Preferred Credentials” was unsuccessful because the displayed page was simply empty. So, I decided to recreate the repository and this fixed my problem. Below are the steps I followed:

drop the repository:


 emca -deconfig dbcontrol db -repos drop

recreate the repository using dbca.

Writing PLSQL Developer Plugins in CSHARP

2007-01-12T01:21:00.000-08:00

Few months ago I decided to write a plugin for the well known PLSQL Developer environment, using csharp. Form the very beginning, I must admit that I haven’t a large experience with this programming language as “deep inside” I’m an Oracle guy, but sometimes is nice to play around with it, especially if you have former knowledge with Java. At that time my plans were to develop a Microsoft Source Safe plugin using InteropSSafeTypeLib. The main problem was the fact that my plugin was not seen by the PLSQL Developer IDE, despite that from the outside it looked like a regular DLL (it turned out that I was wrong). So, below is the class from which I started my research:


using System;
using System.Collections.Generic;
using System.Text;

namespace test_plugin
{
    public class VSSPlugin
    {
        public static string IdentifyPlugIn(int ID)
        {
            string result = "Hello from c#";
            return result;
        }
    }

}

This compiles nicely into a self-packaged DLL but, as I previously said, it is not suitable to be used as a PL/SQL Developer plugin. The problem actually resides in the way the DLL is built-up, its structure being designed to handle managed code which cannot be directly handled by an external application written in C or Delphi. The workaround is to decompile the DLL into its pure code called MSIL to make some changes and to recompile again providing the modified MSIL code for our DLL.

So, to decompile you have to use the following command:


ildasm.exe /OUT:test_plugin.il test_plugin.dll

This will create in the current directory two files: “test_plugin.il” and “test_plugin.res”. The file which is interesting for us is “test_plugin.il” and it contains the MSIL code. You’ll obtain something like this:


//  Microsoft (R) .NET Framework IL Disassembler.  Version 2.0.50727.42
//  Copyright (c) Microsoft Corporation.  All rights reserved.



// Metadata version: v2.0.50727
.assembly extern mscorlib
{
  .publickeytoken = (B7 7A 5C 56 19 34 E0 89 ) 
  .ver 2:0:0:0
}
.assembly test_plugin
{
...
...bla bla bla...
...
}
.module test_plugin.dll
// MVID: {CA976282-5C4E-46F5-A770-39DF57596ECE}
.imagebase 0x00400000
.file alignment 0x00001000
.stackreserve 0x00100000
.subsystem 0x0003       // WINDOWS_CUI
.corflags 0x00000001    //  ILONLY
// Image base: 0x00EB0000


// =============== CLASS MEMBERS DECLARATION ===================

.class public auto ansi beforefieldinit test_plugin.VSSPlugin
       extends [mscorlib]System.Object
{
  .method public hidebysig static string 
          IdentifyPlugIn(int32 ID) cil managed
  {
    // Code size       8 (0x8)
    .maxstack  1
    .locals init ([0] string result)
    IL_0000:  ldstr      "Hello from c#"
    IL_0005:  stloc.0
    IL_0006:  ldloc.0
    IL_0007:  ret
  } // end of method VSSPlugin::IdentifyPlugIn

  .method public hidebysig specialname rtspecialname 
          instance void  .ctor() cil managed
  {
    // Code size       7 (0x7)
    .maxstack  8
    IL_0000:  ldarg.0
    IL_0001:  call       instance void [mscorlib]System.Object::.ctor()
    IL_0006:  ret
  } // end of method VSSPlugin::.ctor

} // end of class test_plugin.VSSPlugin


// =============================================================

// *********** DISASSEMBLY COMPLETE ***********************
// WARNING: Created Win32 resource file test_plugin.res

Now it’s time to make our changes. Bellow is the IL file, modified to support the export of our “IdentifyPlugIn” function. The changed and the added lines are bolded.


//  Microsoft (R) .NET Framework IL Disassembler.  Version 2.0.50727.42
//  Copyright (c) Microsoft Corporation.  All rights reserved.



// Metadata version: v2.0.50727
.assembly extern mscorlib
{
  .publickeytoken = (B7 7A 5C 56 19 34 E0 89 )
  .ver 2:0:0:0
}
.assembly test_plugin
{
...
...bla bla bla...
...
}
.module test_plugin.dll
// MVID: {CA976282-5C4E-46F5-A770-39DF57596ECE}
.imagebase 0x00400000
.file alignment 0x00001000
.stackreserve 0x00100000
.subsystem 0x0003       // WINDOWS_CUI

.corflags 0x00000002
.vtfixup [1] int32 fromunmanaged at VT_01
.data VT_01 = int32(0)


// Image base: 0x00EB0000


// =============== CLASS MEMBERS DECLARATION ===================

.class public auto ansi beforefieldinit test_plugin.VSSPlugin
       extends [mscorlib]System.Object
{
  .method public hidebysig static string 
          IdentifyPlugIn(int32 ID) cil managed
  {

.vtentry 1 : 1
.export [1] as IdentifyPlugIn


    // Code size       8 (0x8)
    .maxstack  1
    .locals init ([0] string result)
    IL_0000:  ldstr      "Hello from c#"
    IL_0005:  stloc.0
    IL_0006:  ldloc.0
    IL_0007:  ret
  } // end of method VSSPlugin::IdentifyPlugIn

  .method public hidebysig specialname rtspecialname 
          instance void  .ctor() cil managed
  {
    // Code size       7 (0x7)
    .maxstack  8
    IL_0000:  ldarg.0
    IL_0001:  call       instance void [mscorlib]System.Object::.ctor()
    IL_0006:  ret
  } // end of method VSSPlugin::.ctor

} // end of class test_plugin.VSSPlugin


// =============================================================

// *********** DISASSEMBLY COMPLETE ***********************
// WARNING: Created Win32 resource file test_plugin.res

It’s time to recompile the IL code into the target DLL file. You can use the following command:


ilasm /OUT:test_plugin.dll test_plugin.il /DLL

Copy the generated test_plugin.dll file under the PLSQL Developer “PlugIns” directory and start the IDE environment. Take a look into Tools/Configure Plugins! Bingo!!! You’ll see there “Hello from c#” which is the name of our first plugin written in csharp.

This approach should be taken as proof of concept that, basically, you can write a PLSQL Developer plugin using csharp but I cannot tell you for sure what problems you might expect or if you will be allowed to use forms or other advanced csharp programming techniques.

The Infamous Update on a Primary Key

2007-01-11T09:13:00.000-08:00

Today I was put in front of a new challenging situation. We have a reporting application which is fed from another system using materialized views. In short, on the remote source system, for several “interesting” tables materialized view logs were defined with the “WITH PRIMARY KEY” option. These logs are used to support a fast refresh mechanism for the corresponding materialized views created on the reporting system. Each materialized view was created on a pre-built table. Of course, there’s a refresh job which takes place on regular intervals in order to keep in sync both systems. As soon as a new record arrives or an existing one is updated this is marked accordingly into a flag column within the pre-built table through a trigger. Likewise, the operation type (INSERT or UPDATE) is written as well into the pre-built table. Now, the big problem is the fact that some modules from the source system are updating (yes, very ugly!!!) values from columns which are part of the primary key. From the materialized view log perspective this is converted into a delete and an insert therefore as soon as the DBMS_MVIEW.REFRESH procedure is called on the reporting system the BEFORE/AFTER DELETE and BEFORE/AFTER INSERT triggers are executed instead of a BEFORE/AFTER UPDATE trigger. Now, one of the MV interface requirement was to convert this sequence of one DELETE and one INSERT so that on the reporting system that initial UPDATE on the primary key to look like a regular update. Even at the very beginning this might appear as a trivial task in fact is quite cumbersome. The solution we found so far relies on using ROWIDs for our pre-built tables.

Okey, because the so called “too much sound but little sense” is not going to be the subject of this post, let’s delve into a concrete test scenario.

First of all, create the source table:


create table muci (col1 integer, col2 integer, col3 integer);
alter table muci add primary key (col1, col2);

insert into muci values (1, 1, 1);
insert into muci values (2, 2, 2);
insert into muci values (3, 3, 3);

commit;
create materialized view log on muci with primary key;

Now, let’s create the pre-built table and the materialized view.


create table muci_mv as 
select t.rowid rid, t.*, '1' processed, 'INSERT' operation from muci t;

create materialized view muci_mv 
on prebuilt table 
refresh fast on demand as 
select t.rowid rid, t.* from muci t;

It is important to notice that we’ve created the pre-built table with additional columns: the “processed” and the “operation” fields. These fields are going to be filled in using a trigger on the MUCI_MV table. Also, pay attention to the “rid” field which is supposed to store ROWID addresses for all records refreshed from the MUCI table (not ROWIDs of MUCI_MV records!). I’ll show you right in a minute what the reason of this column is.

Let’s go on creating a very simple package:


create or replace package MUCI_TOOLKIT 
is

  g_old_rid rowid;

end MUCI_TOOLKIT;

This package has a global variable which is intended to store the old value of the “rid” column of a record which is about to be deleted.

Our test scenario is almost finished! The final step is to create a trigger onto the MUCI_MV table which actually takes care of updating the additional flags:


create or replace trigger trg_biudr_muci_mv
  before insert or update or delete on muci_mv  
  for each row
declare
begin
  if inserting then
    dbms_output.put_line('inserting...');
    :new.processed := '0';
    if muci_toolkit.g_old_rid is not null and :new.rid = muci_toolkit.g_old_rid then
      :new.operation := 'UPDATE';
      muci_toolkit.g_old_rid := null; -- cleanup
    else
      :new.operation := 'INSERT';
    end if;
  end if;
  if updating then
    dbms_output.put_line('updating...');
    :new.processed := '0';
    :new.operation := 'UPDATE';
  end if;
  if deleting then
    dbms_output.put_line('deleting...');
    muci_toolkit.g_old_rid := :old.rid;
  end if;
end trg_biudr_muci_mv;

Now, it’s time to effectively test!


external_interface@pdmdb> set serveroutput on
external_interface@pdmdb> select * from muci;

 COL1  COL2  COL3
----- ----- -----
    1     1     1
    2     2     2
    3     3     3

external_interface@pdmdb> select * from muci_mv;

RID                 COL1  COL2  COL3 PROCESSED OPERATION
------------------ ----- ----- ----- --------- ---------
AACdH+AAFAADheGAAA     1     1     1 1         INSERT
AACdH+AAFAADheGAAB     2     2     2 1         INSERT
AACdH+AAFAADheGAAC     3     3     3 1         INSERT

external_interface@pdmdb> update muci set col1='123' where col1=1 and col2=1;

1 row updated

external_interface@pdmdb> commit;

Commit complete

external_interface@pdmdb> exec dbms_mview.refresh('MUCI_MV', 'f');

deleting...
inserting...

PL/SQL procedure successfully completed

external_interface@pdmdb> select * from muci_mv;

RID                 COL1  COL2  COL3 PROCESSED OPERATION
------------------ ----- ----- ----- --------- ---------
AACdH+AAFAADheGAAB     2     2     2 1         INSERT
AACdH+AAFAADheGAAC     3     3     3 1         INSERT
AACdH+AAFAADheGAAA   123     1     1 0         UPDATE

As you can see it works as expected… smooth! The whole trick consists in checking if the DELETE and INSERT sequence refers to the same source ROWID. If this is the case then we know that an update on the primary key was involved.

However, there are some open points regarding this approach:
1. additional storage is required for storing the added RID column;
2. the DBMS_MVIEW.REFRESH mechanism must follow the sequence order of DML commands performed on the source system which, as far as the oracle documentation states, is not always guaranteed in all Oracle versions.
3. in some MetaLink notes (see Note:67424.1) it is stated that triggers are not supported on read only materialized views.. Yet, you can find a HOWTO on the same MetaLink (176213.1) which advise you to create a trigger on a prebuilt table in order to fill in an additional column (as shown above) with the current SYSDATE value.
4. because we rely on ROWID values of course we’ll end up in big problems if on the source site some source tables are moved to another tablespace or they are re-imported.

To DESC or @DESC...

2007-01-09T14:08:00.000-08:00

There is one SQLPLUS command which seems to be out of my control as far as its displaying layout is concerned. As you already guess from the post title it's about the well known DESC command. Seems that it has a “self-tuning” layout which takes into consideration the current LINESIZE settings.

For example, using a high value for the LINESIZE setting we obtain:


scott@DDB> set linesize 300
scott@DDB> desc emp
 Name                                                                          
         Null?     Type
 ------------------------------------------------------------------------------
-------------------------------------------------------------------------------
---------------- -------- -----------------------------------------------------
---------------------------------------------------------------
 EMPNO                                                                         
         NOT NULL NUMBER(4)
 ENAME                                                                         
  VARCHAR2(10)
 JOB                                                                           
  VARCHAR2(9)
 MGR                                                                           
  NUMBER(4)
 HIREDATE                                                                      
  DATE
 SAL                                                                           
  NUMBER(7,2)
 COMM                                                                          
  NUMBER(7,2)
 DEPTNO                                                                        
  NUMBER(2)

But using a lower value for the LINESIZE setting the output looks fine:


scott@DDB> set linesize 80
scott@DDB> desc emp
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

From my point of view this behavior is quite ugly especially when I am working within a console with horizontal scrolling capabilities. Using a high value for LINESIZE allows me to execute various 'SELECT * FROM' without worrying about output wrapping but when DESC command comes in place I will always end up scrolling to the right side till I reach the displaying of the columns type, which is really, really ugly! So, in the end I've decided to create a script which provides the expected nice output. The main idea is to decrease the LINESIZE setting, to execute DESC and then to restore the old LINESIZE setting. Not a big deal! Below is my script:


store set crr_settings.cfg replace
set linesize 80
desc &1
@crr_settings.cfg

Now, all I have to do is to take care to prefix with a @ the DESC command:


scott@DDB> set linesize 300
scott@DDB> @desc emp
Wrote file crr_settings.cfg
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

Have fun! :)

Do Direct Load Inserts Generate Redo?

2007-01-07T07:31:00.000-08:00

As to so many Oracle related questions the answer is: IT DEPENDS...

If you are in the middle of such a tricky situation where, in the development environment your bulk direct load process performs well but into production the same direct load process behaves poorly because it generates a lot of redo, then you should start by checking the following:

is your development database running in NOARCHIVELOG mode? In NOARCHIVELOG mode the direct load operations produce redo information at minimum which is not necessary the same into a database which runs in ARCHIVELOG mode. It's not unlikely to have the development environment in NOARCHIVELOG and the production in ARCHIVELOG mode therefore this is the first thing to check.
is the destination table from the production site configured with NOLOGGING attribute? Without this setting the direct load inserts against the table will continue to generate redo information as normal. So, check the LOGGING column from (USER/ALL/DBA)_TABLES view for the corresponding destination table.
has the production database the FORCE LOGGING option enabled? This may be required if there are some standby databases in place therefore all direct load operations will produce redo information no matter what. To check this you can query the FORCE_LOGGING column from the V$DATABASE view.

Impersonating Of Any Oracle User?

2007-01-04T14:52:00.000-08:00

Yes, it is possible! It's like sudo command in UNIX or “Run As” in Windows (but without prompting for password). Starting with 8i version, Oracle has a special undocumented package called DBMS_SYS_SQL which provides the functionality needed for impersonating users. It is internally used in databases replication or within the XDB platform. Of course, execute right against this package should be granted with extreme care, as it can be easily exploited by hackers. Furthermore, as an additional precaution it is advisable to check from time to time to whom execute privilege for this package was granted.

So, lets try it!


SQL> connect sys as sysdba
Enter password:
Connected.

SQL> grant execute on dbms_sys_sql to alek;

Grant succeeded.

SQL> connect alek
Enter password:
Connected.
SQL> declare
  2  l_uid number;
  3  l_sqltext varchar2(100) := 'grant dba to super_user identified by xxx';
  4  l_myint integer;
  5
  6  begin
  7  select user_id into l_uid from all_users where username like 'SYS';
  8
  9  l_myint:=sys.dbms_sys_sql.open_cursor();
 10  sys.dbms_sys_sql.parse_as_user(l_myint, l_sqltext, dbms_sql.native, l_uid);

 11
 12  sys.dbms_sys_sql.close_cursor(l_myint);
 13  end ;
 14  /

PL/SQL procedure successfully completed.

SQL> connect super_user/xxx
Connected.

Brrr!!! Nice but scary!

Spelling Numbers

2006-12-23T02:16:00.000-08:00

Wouldn't be nice to be able to display numbers in their spelling textual format? There are a lot of applications which require such a feature: billing, bookkeeping systems etc. Well, if we remember right there is a special suffix for specifying the mask to convert a date using the TO_CHAR function. That special suffix is SP. However, being designed to work with date types it's not very obvious how it can be used for plain numbers. Well, even it's not very often used we can convert a number into a date representation using the Julian format and then spell it using the SP prefix. For example:


SQL> select to_char(to_date(34, 'j'), 'jspth') spell from dual;

SPELL
-------------
thirty-fourth

SQL> select to_char(to_date(1023, 'j'), 'jspth') spell from dual;

SPELL
-------------------------
one thousand twenty-third

Lovely! Nevertheless, there are some limitations here, and some of them are quite ugly therefore they should be carefully analyzed before deciding to use this approach into a production system. The following are some of the problems you might encounter:

it's not suitable for multi-language applications. The SP prefix always speaks in English, despite of the current NLS settings.
It cannot be used to display large numbers. In fact, the maximum number which can be spelled using this approach is exactly 5373484. It's quite small for a bookkeeping system, right?
How about negative numbers or zero? Forget it! It will not work!

SQLPLUS and Custom Languages

2006-12-23T01:30:00.000-08:00

Even I'm from Romania, I don't like how the translation of various messages sounds into my own language. However, sometimes it's useful to enable such a feature if, for example, the error messages which come from the Oracle server must be displayed into the client application but using the native language. So, at the very beginning you might think it's enough just to issue an “ALTER SESSION SET NLS_LANGUAGE=ROMANIAN;” but most of the time you'll find that nothing changes. This is because Oracle needs some additional translation files which, of course, must be installed. To install them you'll need the well known Oracle Universal Installer and you have to provide -addLangs as a parameter. You'll be led into a special wizard page like the one shown below:

After choosing and installing the preferred language we can go on and test if now SQLPLUS speaks our lingo.


SQL> ALTER SESSION SET NLS_LANGUAGE=romanian;

Sesiune modificata.

SQL> select * from no_table;
select * from no_table
           *
EROARE la linia 1:
ORA-00942: tabelul sau vizualizarea nu exist?

SQL> select eroare bre;
select eroare bre
             *
EROARE la linia 1:
ORA-00923: cuvantul cheie FROM nu este g?sit in locul a?teptat

So far so good! Nevertheless, as I previously said the translated messages are quite funny. By the way, I already found three translation mistakes into the above output but I will let Romanian readers to find them. Aaaa, I almost forgot to say: those strange question marks are in place there because my database character set is not appropriate for displaying romanian characters therefore please don't count this as a translation error. :)

CANCEL if you want to work!

2006-12-22T03:10:00.000-08:00

Ohoo, this is quite nice! Usually, you wouldn't expect to make something to work unless you specify CANCEL, right? One interesting case is when you want to recreate the control file with the RESETLOGS option. The scenario is simple, you have an old backup which contains all your datafiles and archivelogs and, in addition, you was smart enough to backup the control file to trace. Now supposing you lost all current datafiles and the current redolog files. You have to restore and recover from your backup. The first thing is to restore your datafiles to the known location, to start the instance in the nomount state and to issue the CREATE CONTROLFILE command from the trace file. After the command is successfully executed your instance will be brought into the mount state using the fresh created control file. Now, the next step is to use the RECOVER DATABASE command. Bellow is a sample output:


SQL> startup nomount
ORACLE instance started.

Total System Global Area  264241152 bytes
Fixed Size                  1218868 bytes
Variable Size              75499212 bytes
Database Buffers          184549376 bytes
Redo Buffers                2973696 bytes
CREATE CONTROLFILE REUSE DATABASE "DDB" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '/opt/oracle/oradata/DDB/onlinelog/o1_mf_1_2pdco23c_.log',
    '/opt/oracle/flash_recovery_area/DDB/onlinelog/o1_mf_1_2pdco49x_.log'
  ) SIZE 50M,
  GROUP 2 (
    '/opt/oracle/oradata/DDB/onlinelog/o1_mf_2_2pdco6j0_.log',
    '/opt/oracle/flash_recovery_area/DDB/onlinelog/o1_mf_2_2pdco8ns_.log'
  ) SIZE 50M,
  GROUP 3 (
    '/opt/oracle/oradata/DDB/onlinelog/o1_mf_3_2pdcoby5_.log',
    '/opt/oracle/flash_recovery_area/DDB/onlinelog/o1_mf_3_2pdcof0o_.log'
  ) SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  '/opt/oracle/oradata/DDB/datafile/o1_mf_system_2rlg719d_.dbf',
  '/opt/oracle/oradata/DDB/datafile/o1_mf_undotbs1_2rlg71l3_.dbf',
  '/opt/oracle/oradata/DDB/datafile/o1_mf_sysaux_2rlg714v_.dbf',
  '/opt/oracle/oradata/DDB/datafile/o1_mf_users_2rlg71kk_.dbf'
CHARACTER SET WE8ISO8859P1
 ;

SQL> select open_mode from v$database;

OPEN_MODE
----------
MOUNTED

SQL> recover database using backup controlfile;
ORA-00279: change 793731 generated at 12/22/2006 12:00:32 needed for thread 1
ORA-00289: suggestion :
/opt/oracle/flash_recovery_area/DDB/archivelog/1_1_609804799.dbf
ORA-00280: change 793731 for thread 1 is in sequence #1


Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 794266 generated at 12/22/2006 12:09:00 needed for thread 1
ORA-00289: suggestion :
/opt/oracle/flash_recovery_area/DDB/archivelog/1_2_609804799.dbf
ORA-00280: change 794266 for thread 1 is in sequence #2
ORA-00278: log file
'/opt/oracle/flash_recovery_area/DDB/archivelog/1_1_609804799.dbf' no longer
needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 794268 generated at 12/22/2006 12:09:03 needed for thread 1
ORA-00289: suggestion :
/opt/oracle/flash_recovery_area/DDB/archivelog/1_3_609804799.dbf
ORA-00280: change 794268 for thread 1 is in sequence #3
ORA-00278: log file
'/opt/oracle/flash_recovery_area/DDB/archivelog/1_2_609804799.dbf' no longer
needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 794271 generated at 12/22/2006 12:09:07 needed for thread 1
ORA-00289: suggestion :
/opt/oracle/flash_recovery_area/DDB/archivelog/1_4_609804799.dbf
ORA-00280: change 794271 for thread 1 is in sequence #4
ORA-00278: log file
'/opt/oracle/flash_recovery_area/DDB/archivelog/1_3_609804799.dbf' no longer
needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log
'/opt/oracle/flash_recovery_area/DDB/archivelog/1_4_609804799.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3


SQL> ALTER DATABASE OPEN RESETLOGS;
ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1:
'/opt/oracle/oradata/DDB/datafile/o1_mf_system_2rlg719d_.dbf'

Uppsy! File 1 needs media recovery? Why?
Because, when I have been asked for the last log archive I didn't specify CANCEL. From the Oracle server point of view, the recover process was unsuccessful. So, the correct approach is:


SQL> recover database until cancel using backup controlfile;
ORA-00279: change 794271 generated at 12/22/2006 12:09:07 needed for thread 1
ORA-00289: suggestion :
/opt/oracle/flash_recovery_area/DDB/archivelog/1_4_609804799.dbf
ORA-00280: change 794271 for thread 1 is in sequence #4


Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

So, explicit CANCEL does matter in this case!

Should I Delete All Archives In One Shoot?

2006-12-21T08:15:00.000-08:00

I was just wondering what is the best way to delete the backed up archives using RMAN. I have to choose between DELETE INPUT and DELETE ALL INPUT options.
In my environment, all archives are written on two different locations. If I am going to use the DELETE ALL INPUT option then after the archives from one of the two locations are backed up, all the corresponding archive files are deleted from all available locations. If I am going to use DELETE INPUT option then only the backed up archives are deleted, just from one of the two locations.
Now, I must admit that using the BACKUP ARCHIVELOG ALL command with DELETE ALL INPUT sounds to me a little bit scary as after the successful completion of it I will end up with just one backup set with all backed up archives but no archives redundancy. This backup set becomes the single point of failure if a database recovery must take place and those archives are required.
So, the other approach is to use the DELETE INPUT option only. The below figure shows what's happening in this case:

At T0 moment, there are archives not backed up on both locations. The BACKUP command creates the “Bacup Set1” and deletes the corresponding archives from the LOCATION 1. So, now I have archives on LOCATION 2 and into the backup set as well (redundancy still 2). Suppose that after new archives are generated a new backup is taken. The following figure depicts what will happen in this case:

As it can be noticed, the next BACKUP command creates the “Backup Set2” which contains all archives from the LOCATION2 not deleted by the previous BACKUP command and, in addition, the new generated archive logs from the LOCATION1. These backed up archives are deleted but the redundancy level for them is still two. Much safer, right?

Don't Forget to Restore Your Read-only Tablespaces

2006-12-20T05:42:00.000-08:00

I know, this is a basic one! But, me personally, I don't restore/recover databases everyday therefore is quite easy to forget some basics. So, supposing that you have lost all datafiles and some of them were part of some read-only tablespaces then it is important to remember that, by default, RMAN will not restore any datafiles from those read-only tablespaces. Hopefully, RMAN will display a warning, something like this: “datafile X not processed because file is read-only”, but the restore operation will go on without problems. So, in order to restore all datafiles including the ones from the read-only tablespaces then the correct command is: “RESTORE DATABASE CHECK READONLY;”.

Does any Datablock Change Generates Flashback Logs?

2006-12-20T02:00:00.000-08:00

In the “Database Backup and Recovery Basics” book from the 10g Release 2 (10.2) documentation, in the sub-chapter “5.3.7 Performance Tuning for Flashback Database” there is a remark which says:

“Queries do not change data and thus do not contribute to logging activity for Flashback Database.”

Hmm... indeed they do not change actual business data but they can change database blocks in the so called “block cleanout” process and, in such cases, they actually generate redolog information.

My first assumption was that “Flashback database” engine detects on the very low level any change in data blocks, either as a consequence of a DML or as a consequence of an internal data block maintenance process such as the “block cleanout” operation.

So, I've decided to test if the “block cleanout” process does or does not generate flashback logs. The scenario is simple: my test database has a datablock size of 8K so I will create a dummy table so that each of its rows to fit into one oracle datablock. Then I will insert enough records into this table so that, in the end, my transaction to simply give up cleaning out all modified blocks. In the end I will force a full table scan against the sample table in order to perform the cleanout process. During these operations I will closely look to some of my session statistics.

Here we go:

1.first of all, some checking and some rights are needed:


SQL> connect / as sysdba
Connected.
SQL> show parameter db_block_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192
SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL> grant select on v_$mystat to alek;

Grant succeeded.

SQL> grant select on v_$statname to alek;

Grant succeeded.

2.now, lets create our test environment:


10:57:46 SQL> connect alek
Enter password:
Connected.
10:57:55 SQL> create table muci (col1 char(2000), col2 char(2000), col3 char(2000));

Table created.

10:58:12 SQL> select name, value from v$mystat natural join v$statname
where regexp_like(name, '(.*flash.*)|(redo size)');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
physical reads for flashback new                                          6
redo size                                                              9564
flashback log writes                                                      0

10:58:22 SQL> insert into muci select 'a', 'b', 'c' from all_objects;

40710 rows created.

11:01:21 SQL> commit;

Commit complete.

11:01:37 SQL> select name, value from v$mystat natural join v$statname
where regexp_like(name, '(.*flash.*)|(redo size)');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
physical reads for flashback new                                      41526
redo size                                                         263629980
flashback log writes                                                      0

Once again after a minute:


11:01:45 SQL> select name, value from v$mystat natural join v$statname
where regexp_like(name, '(.*flash.*)|(redo size)');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
physical reads for flashback new                                      41526
redo size                                                         263629980
flashback log writes                                                      0

There's no change! Go on with the clean out process:


11:05:29 SQL> select count(1) from muci;

 COUNT(1)
----------
    40710

11:06:11 SQL> select name, value from v$mystat natural join v$statname
where regexp_like(name, '(.*flash.*)|(redo size)');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
physical reads for flashback new                                      41526
redo size                                                         266561132
flashback log writes                                                      0

Trying once again after some minutes within the same session reveals no changes, therefore we can conclude that flashback logs were not generated even asynchronously after some time.


11:06:21 SQL> select name, value from v$mystat natural join v$statname
where regexp_like(name, '(.*flash.*)|(redo size)');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
physical reads for flashback new                                      41526
redo size                                                         266561132
flashback log writes                                                      0

Thats interesting! As it can be seen, the cleanup process has, indeed, generated redolog information which means that it changed some header information from the cleaned out datablocks but no flashback logs were produced.

So, as far as these tests are concerned the above statement from the Oracle official documentation seems to be correct!

Playing Around with “Flashback Database”

2006-12-19T11:10:00.000-08:00

“Flashback Database” is a new feature available in 10g databases and it can be used to rewind the content of the database to a previous moment in time. The key elements of this feature are:

flashback logs
restore points

By default the database runs with the “flashback database” feature disabled. I can always find out if this feature is activated or not by querying the V$DATABASE view.


SQL> SELECT FLASHBACK_ON FROM V$DATABASE;

FLASHBACK_ON
------------------
NO

There are some important requirements which must be fulfilled in order to start using this feature:

a “Flash Recovery Area” must be setup;
the database must run in ARCHIVELOG mode;

To enable flashback database the following steps should be followed:

1.clean shutdown of the database:


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

2.startup the database in mount state:


SQL> startup mount
ORACLE instance started.

Total System Global Area  264241152 bytes
Fixed Size      1218868 bytes
Variable Size     79693516 bytes
Database Buffers   180355072 bytes
Redo Buffers      2973696 bytes
Database mounted.

3. enable the flashback database feature:

SQL> alter database flashback on;

Database altered.

4.open the database:

SQL> alter database open;

Database altered.

Now, that the “Flashback Database” is enabled I can monitor and estimate the size of flashback logs by using the V$FLASHBACK_DATABASE_LOG and V$FLASH_RECOVERY_AREA_USAGE views. I can perform some massive updates/inserts and to query those two views afterwards to see how the effective and estimate flashback log sizes are affected.


SQL> select * from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- --------- ---------------- -------------- ------------------------
            624609 19-DEC-06             1440        8192000                283582464

SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE    PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE                 .33                         0               1
ONLINELOG                  7.32                         0               3
ARCHIVELOG                  .61                         0               1
BACKUPPIECE               77.72                     25.84               8
IMAGECOPY                     0                         0               0
FLASHBACKLOG                .38                         0               1

6 rows selected.

The above listing reveals that currently I have a flashback log file of ~8M and the estimated flashback log size is ~270M.
OK, lets see this feature in action. I am going to create a restore point, to perform some inserts and to flashback the database to the restore point.

SQL> create restore point before_update;

Restore point created.

SQL> create table alek.fback_test as select * from all_objects;

Table created.

SQL> select count(1) from alek.fback_test;

COUNT(1)
----------
   49337

Now, I want to rewind the database back to the restore point. This will imply a shutdown, a startup in mount state, the corresponding FLASHBACK command and the opening of the database with the RESETLOGS clause.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  264241152 bytes
Fixed Size                  1218868 bytes
Variable Size              88082124 bytes
Database Buffers          171966464 bytes
Redo Buffers                2973696 bytes
Database mounted.
SQL> flashback database to restore point before_update;

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL> select count(1) from alek.fback_test;
select count(1) from alek.fback_test
                        *
ERROR at line 1:
ORA-00942: table or view does not exist

Everything went just perfect! The database was restored as it was when the restore point was defined. However, if my business requirements decide the need to restore the database to the defined restore point only and not between now and the time the restore point was defined then a better approach is to use guaranteed restore points with flashback database disabled. In this case the amount of generated flashback logs is smaller and the performance of an application which often modifies data is improved.
So, if I am going to use the above approach the first thing to be done is to disable the flashback database feature. This can be done with the database open.

SQL> alter database flashback off;

Database altered.

However, switching back is not possible when the database is in the OPEN state:


SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38759: Database must be mounted by only one instance and not open.

Another important thing to notice is that I cannot create the first guaranteed restore point with the database open. If trying to do so I'll get an error message as shown below:

SQL> create restore point before_delivery guarantee flashback database;
create restore point before_delivery guarantee flashback database
*
ERROR at line 1:
ORA-38784: Cannot create restore point 'BEFORE_DELIVERY'.
ORA-38787: Creating the first guaranteed restore point requires mount mode 
when flashback database is off.

The error message is quite self explanatory therefore the next step would be to cleanly shutdown the database and to start it in the mount state.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  264241152 bytes
Fixed Size                  1218868 bytes
Variable Size              88082124 bytes
Database Buffers          171966464 bytes
Redo Buffers                2973696 bytes
Database mounted.
SQL> create restore point before_delivery guarantee flashback database;

Restore point created.

SQL> alter database open;

Database altered.

Now, lets simulate the same scenario as we did with the flashback database feature enabled.

SQL> create table alek.fback_test as select * from all_objects;

Table created.

SQL> select count(1) from alek.fback_test;

COUNT(1)
----------
   49337

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  264241152 bytes
Fixed Size                  1218868 bytes
Variable Size              88082124 bytes
Database Buffers          171966464 bytes
Redo Buffers                2973696 bytes
Database mounted.
SQL> flashback database to restore point before_delivery;

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL> select count(1) from alek.fback_test;
select count(1) from alek.fback_test
                        *
ERROR at line 1:
ORA-00942: table or view does not exist

Lovely, it works as expected! Now, I'm wondering what happen with my guaranteed restore point.

SQL> select name, time, guarantee_flashback_database from v$restore_point;

NAME                           TIME                                     GUA
------------------------------ ---------------------------------------- ---
BEFORE_DELIVERY                19-DEC-06 05.01.55.000000000 PM          YES
BEFORE_UPDATE                  19-DEC-06 03.54.19.000000000 PM          NO

It is still there. The guaranteed restore points are never automatically age out therefore the only option I have in order to delete it is to use the DROP RESTORE POINT command.

SQL> drop restore point before_delivery;

Restore point dropped.

SQL> select name, time, guarantee_flashback_database from v$restore_point;

NAME                           TIME                                     GUA
------------------------------ ---------------------------------------- ---
BEFORE_UPDATE                  19-DEC-06 03.54.19.000000000 PM          NO

Now, the ugly part of this is that I've end up with the previous situation when no guaranteed restore points were defined and the first one must be always created with the database started in the mount state.

SQL> create restore point before_delivery guarantee flashback database;
create restore point before_delivery guarantee flashback database
*
ERROR at line 1:
ORA-38784: Cannot create restore point 'BEFORE_DELIVERY'.
ORA-38787: Creating the first guaranteed restore point requires mount mode 
when flashback database is off.

The workaround would be to create a second guaranteed restore point and to delete the previous one so that at least one guaranteed restore point to be defined. However, the drawback of this is that I have to maintain this guaranteed restore point if my goal is to maximize the availability of the database.

After the evaluation of this feature I can say what I like and what I don't like about it:

I like:

the straightforward way of configuring and using it;
the possibility to estimate the size of flashback logs through the V$FLASHBACK_DATABASE_LOG view;
much easier to use than the classical database incomplete recovery;

I don't like:

the potential performance issues on a productive system which performs a lot of changes in the underlying data.
the fact that i cannot use it to rewind the content of just one or several tablespaces. If the database hosts more than one application then all of them will be affected therefore the remaining option will still remain the TPITR. The “Flashback Database” feature is closer to the “Database incomplete recovery” than the TPITR feature.
From my point of view this feature suits well into test and/or developing environments were the likelihood of making mistakes is greater in comparison with the production environment which is supposed to be used in a restrictive and controlled way. The main benefit of using it in production systems could be seen from the application deployment strategy perspective. Before running an update script as part of the application delivery process the DBA can define a guaranteed restore point and if something nasty happens within that script then he/she can easily rewind the database as it was at the time the restore point was defined. However, the fact that the database should be shutdown doesn't sound very well to me as precious cached data from the SGA will be lost.