2012-04-24

DBMS_REDEFINITION sucks if you have CLOBs

DBMS_REDEFINITION is a well-known, built-in package of Oracle, which can be used to reorganize tables. It works well most of the times, but recently I have found myself working out a custom solution to reorg a large table for a customer.

For the impatient: if your table you are going to reorg contains CLOB data, you might have to consider using an alternative method such as CTAS. (Problem appeared on 11.2.0.2, I did not test it on other Oracle versions).

In this case our customer had a trace table which occupied approximately 27GB (20GB of that belonged to LOB segments!). In order to sort the problem out, the proposed solution was that we would re-create it as a partitioned table and Oracle's secure file feature with compression would be used to help cut down on required space.

For some reason the execution of DBMS_REDEFINITION.SYNC_INTERIM_TABLE(...) has never finished and the whole process got stuck in this step. As the name of the procedure suggests this ought to synchronize inserted/updated/deleted records from the original table to the new one. Internally DBMS_REDEFINITION uses a materialized view and a materialized view log on the source table to do this job.

Most likely the CLOB column which the source table had, was responsible for the misbehaviour, I experienced.

You can reproduce this issue easily by taking the following steps.
  1. Create a large enough source table and an empty destination table with the same definition.

  2. -- Create source table
    create table old_table
    as select ao.owner, ao.object_name from all_objects ao where 1 = 2;

    -- Add columns (if you change the DATA column to VARCHAR2,
    -- or you simply just remove it, everything will be working
    -- fine.
    alter table old_table add data clob not null;
    alter table old_table add id number not null;

    create table new_table
    (
      owner          varchar2(30 byte) not null,
      object_name    varchar2(30 byte) not null,
      data           clob,
      id             number
    );
    -- Populate original table (it inserts ~1M records on my database)
    create sequence seq minvalue 1 start with 1 increment by 1 cache 10;
    insert into old_table
      select ao.owner, ao.object_name, ao.owner || '.' || ao.object_name as data, seq.nextval as data from all_objects ao, dba_users du
      where du.username like '%SYS%';
    commit;

    -- We have to add a primary key, because redefinition
    -- will not start without a primary key.
    alter table old_table add constraint pk_old_table primary key (id);

  3. Perform on-line redefinition

  4. declare
      procedure cleanup is
      begin
        dbms_application_info.set_action(action_name => '');
        dbms_monitor.session_trace_disable();
      end;
    begin
      dbms_monitor.session_trace_enable(waits => true, binds => true);
      dbms_application_info.set_action(action_name => 'start_redef_table');
      dbms_redefinition.start_redef_table( uname => 'lcsontos',
                                           orig_table => 'old_table',
                                           int_table => 'new_table');
      dbms_application_info.set_action(action_name => 'sync_interim_table');
      dbms_redefinition.sync_interim_table( uname => 'lcsontos',
                                            orig_table => 'old_table',
                                            int_table => 'new_table');
      dbms_application_info.set_action(action_name => 'finish_redef_table');
      dbms_redefinition.finish_redef_table( uname => 'lcsontos',
                                            orig_table => 'old_table',
                                            int_table => 'new_table');
      cleanup();
    exception
      when others then
        cleanup();
        dbms_redefinition.abort_redef_table( uname => 'lcsontos',
                                             orig_table => 'old_table',
                                             int_table => 'new_table');
        dbms_output.put_line(dbms_utility.format_error_backtrace());
    end;

  5. While redefinition is in progress insert some records to the source table

  6. insert into old_table
      select ao.owner, ao.object_name, ao.owner || '.' || ao.object_name, seq.nextval as data from all_objects ao
      where rownum <= 1000;
    commit;

  7. Your process will be bogged down on the sync phrase.

  8. Take a look at the active sessions, this query will be running (and running ...), until you kill it. If you try to execute it from another session it gives a result within a few seconds. So I suppose there is nothing wrong with the query itself.

    SELECT CURRENT$."OWNER",
           CURRENT$."OBJECT_NAME",
           CURRENT$."SUBOBJECT_NAME",
           CURRENT$."OBJECT_ID",
           CURRENT$."DATA_OBJECT_ID",
           CURRENT$."OBJECT_TYPE",
           CURRENT$."CREATED",
           CURRENT$."LAST_DDL_TIME",
           CURRENT$."TIMESTAMP",
           CURRENT$."STATUS",
           CURRENT$."TEMPORARY",
           CURRENT$."GENERATED",
           CURRENT$."SECONDARY",
           CURRENT$."NAMESPACE",
           CURRENT$."EDITION_NAME",
           CURRENT$."DATA",
           CURRENT$."ID",
           LOG$.CHANGE_VECTOR$$
      FROM (SELECT "OLD_TABLE"."OWNER"          "OWNER",
                   "OLD_TABLE"."OBJECT_NAME"    "OBJECT_NAME",
                   "OLD_TABLE"."SUBOBJECT_NAME" "SUBOBJECT_NAME",
                   "OLD_TABLE"."OBJECT_ID"      "OBJECT_ID",
                   "OLD_TABLE"."DATA_OBJECT_ID" "DATA_OBJECT_ID",
                   "OLD_TABLE"."OBJECT_TYPE"    "OBJECT_TYPE",
                   "OLD_TABLE"."CREATED"        "CREATED",
                   "OLD_TABLE"."LAST_DDL_TIME"  "LAST_DDL_TIME",
                   "OLD_TABLE"."TIMESTAMP"      "TIMESTAMP",
                   "OLD_TABLE"."STATUS"         "STATUS",
                   "OLD_TABLE"."TEMPORARY"      "TEMPORARY",
                   "OLD_TABLE"."GENERATED"      "GENERATED",
                   "OLD_TABLE"."SECONDARY"      "SECONDARY",
                   "OLD_TABLE"."NAMESPACE"      "NAMESPACE",
                   "OLD_TABLE"."EDITION_NAME"   "EDITION_NAME",
                   "OLD_TABLE"."DATA"           "DATA",
                   "OLD_TABLE"."ID"             "ID"
              FROM "LCSONTOS"."OLD_TABLE" "OLD_TABLE") CURRENT$,
           (SELECT MLOG$."ID",
                   SYS.MVAggRawBitOr(MLOG$.CHANGE_VECTOR$$) CHANGE_VECTOR$$
              FROM "LCSONTOS"."MLOG$_OLD_TABLE" MLOG$
             WHERE "SNAPTIME$$" > :1
               AND ("DMLTYPE$$" != 'D')
             GROUP BY MLOG$."ID") LOG$
     WHERE CURRENT$."ID" = LOG$."ID"

    I have also traced it, but looking at the results did not give me any clue.

    Elapsed times include waiting on following events:
      Event waited on                             Times   Max. Wait  Total Waited
      ----------------------------------------   Waited  ----------  ------------
      db file sequential read                         2        0.03          0.04

    Yes, it is waiting for sequential read exactly the same way as it did in my case when I was on-site.
Obviously we hit a bug, but unfortunately I did not find anything similar which comes close to this issue on Metalink. I was lucky, because the table I had to reorg, is being only inserted, so I created a log table with an insert trigger in order to catch new records while CTAS was running. After that my script added those newly inserted records and created necessary dependant objects (indices, keys, etc.) and collected statistics.

2010-12-16

How to fix NAT service of VMWare Server 2.0.x on Windows 7

For some weird reason NAT service of VMWare on Windows 7 does not allow VMs to access the Internet (or other computers beyond the host). It is also strange that you can ping any host and do DNS lookups, but establishing a connection is not possible.

I have got a VM running CentOS 5 and its IP configuration has been statically set, so I conclude, the whole thing should have been able to work smoothly.

[root@dev01 ~]# ifconfig eth0
eth0 Link encap:Ethernet HWaddr 00:0C:29:AD:F2:ED
inet addr:10.100.8.10 Bcast:10.100.8.255 Mask:255.255.255.0
inet6 addr: fe80::20c:29ff:fead:f2ed/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:1561 errors:0 dropped:0 overruns:0 frame:0
TX packets:1128 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:162826 (159.0 KiB) TX bytes:207166 (202.3 KiB)
Interrupt:59 Base address:0x2024

[root@dev01 ~]# netstat -nr
Kernel IP routing table
Destination Gateway Genmask Flags MSS Window irtt Iface
10.100.8.0 0.0.0.0 255.255.255.0 U 0 0 0 eth0
10.100.1.0 0.0.0.0 255.255.255.0 U 0 0 0 eth1
169.254.0.0 0.0.0.0 255.255.0.0 U 0 0 0 eth1
0.0.0.0 10.100.8.254 0.0.0.0 UG 0 0 0 eth0


And here comes the problem. The host machine is attached to a local network, which has an HTTP proxy server (192.168.1.1). What really strange to me is that I can ping the proxy, but opening a connection to it fails.

[root@dev01 ~]# ping -c 3 192.168.1.1
PING 192.168.1.1 (192.168.1.1) 56(84) bytes of data.
64 bytes from 192.168.1.1: icmp_seq=1 ttl=128 time=3.36 ms
64 bytes from 192.168.1.1: icmp_seq=2 ttl=128 time=1.55 ms
64 bytes from 192.168.1.1: icmp_seq=3 ttl=128 time=7.54 ms

[root@dev01 ~]# nc -vw 3 192.168.1.1 3128
nc: connect to 192.168.1.1 port 3128 (tcp) timed out: Operation now in progress


After googling an hour to make it work I finally found out that it is a known issue that NAT is broken if VMWare is running on Windows 7: http://communities.vmware.com/thread/206553

I followed the suggestion which is mentioned somewhere at the end of that thread, but it did not really help.

After that I had a random thought, what if I had VMnet8 bridged through my wireless network adapter, and let Windows 7 to do the NAT stuff? I have never ever seen the properties of VMnet* adapters on a Windows XP or an Ubuntu host, because they used to always work out of the box.

(1) Disable auto bridging using VMWare's Virtual Network Editor.


(2) Enable bridging through my WIFI card.



(3) After clicking OK an another window appeared and W7 told me that it had just set a new IP address to that interface through which other computers (VMs) would be connected to the Internet.


Bad news for me, because VMnet8 has been already assigned to a subnet, and all the VMs of mine are using it, so this means reconfiguring it again.


I do not clearly understand why Windows wants to use a fixed IP subnet for this purpose and why I am not allowed to pick one by myself. Never mind, it can be changed at the expense of a little registry hack.

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\SharedAccess\Parameters]
"ScopeAddress"="10.100.8.254"
"ScopeAddressBackup"="10.100.8.254"


After restarting VMWare NAT service it works! :)

2010-06-09

Sun Netra T1 diary - Post install configuration (2010-06-05)

After the installation of the Solaris 10 on my Netra T1 box had been taken place, I've started to configure the system. As I've chosen Reduced Networking Core System Support there will be a lot of works to do.

First of all the dud, noisy, old fans needed to be replaced. Unfortunately I haven't found any supplier in Hungary who whould have traveled in selling fans of the same type (1606KL-04W-B59) that my Netra T1 had. I have had to select a different model (KDE1204PFV1) with similar airflow characteristics ...



... in spite of the fact that it won't fit into the oldest one's place since it's smaller in one dimension.



By applying plastic spacers I was able to put in the new fans.



Fix networking

After logging in as root I immediately realized that the network didn't work at all, apparently it had been a bad idea to choose QFE0 as the primary network interface. I suppose there must be a different SRV4 package supporting these kind of network interfaces which needed to be installed as well. Nevertheless Netra T1 also has two HME (Happy Meal Ethernet) ports thus I've abandoned to make any of the QFE port to work.

# rm /etc/hostname.qfe0
# echo "sunrise" > /etc/hostname.hme0


Mount CDROM

I really hate this on Solaris to figure out what is the exact device node of the CDROM and mount it manually. I feel that it's a real pain in contrast to do it under Linux.

# mkdir -p /mnt/cdrom
# mount -F hsfs -o ro /dev/dsk/c1t2d0s2 /mnt/cdrom


Install & configure SSH

We don't have SSH installed at this time and I'm using a VT100 terminal attached using a serial cable to the box. Of course this is not very convenient.

# pkgadd -d /mnt/cdrom/Solaris_10/Product SUNWgssc SUNWgss SUNWsshcu SUNWsshdr SUNWsshdu
# vi /etc/ssh/sshd_config

...
PermitRootLogin yes
...


After this step I tried to log into the box as root, but it didn't work. Googling a little pointed out and SSH keys are missing and they can be created using the statement below.

# /lib/svc/method/sshd -c
# svcadm restart ssh


Create a non-priv user

# mkdir -p /home
# useradd -m -d /home/sysadm sysdadm


Install NFS

I've decided to share the whole set of SRV4 packages on a different box running Debian Lenny, this way installing packages from CDROM can be eliminated.

# pkgadd -d /mnt/cdrom/Solaris_10/Product SUNWgssk SUNWrsgk SUNWnfscr SUNWnfsckr SUNWnfscu
# mount dezso:/mnt/data/SOFTWARE/SUN/OS/SOLARIS/10/SPARC/PKG /var/spool/pkg
# vi /etc/vfstab

...
dezso:/mnt/data/SOFTWARE/SUN/OS/SOLARIS/10/SPARC/PKG - /var/spool/pkg nfs - no ro
...


Fix locale

After the first boot there were a few error messages complaining that the locale hasn't been set correctly.



I'd rather remove North American locale and will use the default POSIX until it turns out that I really need a locale other that POSIX.

# pkgadd SUNWxcu4 SUNWladm
# localeadm -r nam
# vi /etc/default/init

...
LANG=C
# init q


Install bash

# pkgadd SUNWbash
# usermod -s /usr/bin/bash sysadm
# usermod -s /usr/bin/bash root


Install MAN pages

# pkgadd SUNWman
# pkgadd SUNWdoc
# catman &


2010-06-07

Sun Netra T1 diary - Solaris 10 installation (2010-06-03)

Well, I had a little time on Saturday and I started to seize and install Solaris 10 on this machine. The installation process was not smooth at all I've manage to do it successfully only for 3rd or 4th time. Last week I installed FreeBSD 8 which was pretty easy to do, but I didn't know that FreeBSD created EFI type disk label on c0t0d0. It took me some time until I've found this forum thread and managed to change the disk label back to SMI.



I'd like to use the installation program in English.



VT100 is the perfect selection, because I've used minicom to connect to this Netra T1 box.





Of course I want this box to connect to my network.



I've selected QFE0, becuase this interface was recognized for the first time.



QFE0 is going to have a static IP address.



Pick a host name.



This is going to be the static local IP address of this host.



Subnet configuration and the rest of network configuration. It is quite obvious I think.

















Time zone configuration.








Disable all services which would make this system available though the network. Although will be a real pain to set up everything manually after the installation has been completed, I like to do it this way for security reasons.













I was in two minds at this point and I really did not know whether I should choose ZFS or not. Though ZFS has been out there for at least five years at it must be an excellent choice, I've chosen UFS after all, since our client I'am currently working for does not use ZFS at all. I also would like to learn about the legacy SVM.



I only want to have those pieces of software which are absolutely neccessary.




The tricky part is coming: partitioning of disks. The second one will be set up after installation.






It is usually hard to decide how to lay out partitions on the disk. Yes, I know, by using ZFS it might have been easier.







The installation is finally running in a couple of minutes I can use my system.






This is the first boot. Basically there is no point in choosing the keyboard layout, since Sun Netra T1 has neither a VGA output nor a keyboard interface. It only has a serial console...



Apparently something must have been gone wrong around the locale.