Tuesday, December 9, 2014

Fetching Multiple Rows with EXECUTE IMMEDIATE

execute immediate is very handy for executing dynamic SQL statements inside PLSQL block. It can accept IN and OUT parameters and can also return output values to PLSQL variables by into clause. But returning values can be tricky because of the multiple returning rows. For single row returns into clause works fine but for multiple rows, hitting the following exception is quite easy.

declare  
  
  v_cmd varchar2(100);
  v_table_owner varchar2(30) := 'SCH_OLTP';  
  v_table_name varchar2(30) := 'T_VISIT_STATS_DETAILED';
  
  v_column_name varchar2(30);
  
begin 

  v_cmd := 'select column_name from dba_tab_columns where owner=:v_table_owner and table_name=:v_table_name';
   
  execute immediate v_cmd into v_column_name
    using v_table_owner, v_table_name;
  
end;
/*
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 13
*/

So it is important to check the returning row count before using execute immediate is important. Or you can use the bulk collect clause to fetch multiple returning rows into an array.

declare 
  type typ_varcharlist is table of varchar2(30);
  
  col_list typ_varcharlist;  
  
  v_cmd varchar2(100);
  v_table_owner varchar2(30) := 'SCH_OLTP';  
  v_table_name varchar2(30) := 'T_VISIT_STATS_DETAILED';
  
begin 
  v_cmd := 'select column_name from dba_tab_columns where owner=:v_table_owner and table_name=:v_table_name';
  
  
  execute immediate v_cmd bulk collect into col_list 
    using v_table_owner, v_table_name;
  
  for n_ctl in 1 .. col_list.count() loop
    dbms_output.put_line (col_list(n_ctl));
  end loop;
  
end;
/*
DUMMY
SUCCESS
VISITED_USER_ID
FROM_IP
VISIT_DATE
PAGE_ID
*/

Reference:
Oracle PLSQL Users Guide
Thesis and Papers > Oracle 11g Execute Immediate


Monday, July 7, 2014

Change Data Capture using Streams

I found this dusty script from my old shelves which enables change data capture feature enabled in oracle database 11.2.0.4 enterprise edition running RHEL 6.2 64bit.

I remember this script is for keeping a product table changes in a CDC table for a java process to read the CDC$xxx table. This consumer process reads the changed records is developed by our talented java development group and is beyond the scope of this post as can be understood.

Here, i will share how i enabled my oracle database change data capture feature.

There is some preparation steps in order to start the change data capture. Most of them are like to streams preparations scripts which makes sense hence streams is the underlying option if this feature.



--prepare the publisher user

--publisher user needs the following privileges and quotas

grant execute_catalog_role to dba_planet;
grant select_catalog_role to dba_planet;
grant execute on dbms_cdc_publish to dba_planet;
grant create sequence to dba_planet;
grant dba to dba_planet;
exec dbms_streams_auth.grant_admin_privilege('dba_planet');

alter user dba_planet quota unlimited on system;
alter user dba_planet quota unlimited on sysaux;


--prepare the database

--supplemental logging should be enabled in database level

alter database force logging;
alter database add supplemental log data;


--check database level supplemental logging level of the database

select SUPPLEMENTAL_LOG_DATA_MIN,
       SUPPLEMENTAL_LOG_DATA_PK,
       SUPPLEMENTAL_LOG_DATA_UI from v$database;


--prepare the instance

--depending on the configuration there will some process overhead when enabling the CDC
--so instance parameters should be reviewed

select name, value, isspecified
  from v$spparameter
  where name in
    ('compatible',
     'java_pool_size',
     'job_queue_processes',
     'parallel_max_servers',
     'processes',
     'sessions',
     'streams_pool_size',
     'undo_retention');


--minimum value of the undo_retention parameter should be 7200sec

alter system set undo_retention=7200 scope=both sid='*';



--prepare source tables

--create necessary log groups
--every column to be change logged should be supplemental logging enabled
--I preferred to create individual log groups for every column I will track

alter table sch_dmall.product add supplemental log data (all) columns;
alter table sch_dmall.product add supplemental log data (primary key) columns;
alter table sch_dmall.product add supplemental log data (foreign key) columns;
alter table sch_dmall.product add supplemental log data (unique) columns;

alter table sch_dmall.product add supplemental log group lg_product1 (id, deleted, seller_id,lastmodifieddate,category_id,dis_price,price,title,subtitle,productstatus,salestatus, urlwords) always;
alter table sch_dmall.product add supplemental log group lg_product2 (id) always;
alter table sch_dmall.product add supplemental log group lg_product3 (deleted) always;
alter table sch_dmall.product add supplemental log group lg_product4 (seller_id) always;
alter table sch_dmall.product add supplemental log group lg_product5 (lastmodifieddate) always;
alter table sch_dmall.product add supplemental log group lg_product6 (category_id) always;
alter table sch_dmall.product add supplemental log group lg_product7 (dis_price) always;
alter table sch_dmall.product add supplemental log group lg_product8 (price) always;
alter table sch_dmall.product add supplemental log group lg_product9 (title) always;
alter table sch_dmall.product add supplemental log group lg_product10 (subtitle) always;
alter table sch_dmall.product add supplemental log group lg_product11 (productstatus) always;
alter table sch_dmall.product add supplemental log group lg_product12 (salestatus) always;
alter table sch_dmall.product add supplemental log group lg_product13 (urlwords) always;



it begins with the famous streams starting point which is prepare_table_instantiation procedure to log the current SCN of the table to be change captured. This procedure will let CDC processes to know from which starting SCN point to start the Change Data Capture.

begin
--dbms_capture_adm.build;
dbms_capture_adm.prepare_table_instantiation(table_name => 'sch_dmall.product');
end;
/




Now we should create the change set to associate with the CDC table. One change set can include one or more CDC tables to process.

--if exists, first drop the change set
exec dbms_cdc_publish.drop_change_set('CS_PRODUCT');

--create the change set
begin
  dbms_cdc_publish.create_change_set(
    change_set_name => 'CS_PRODUCT',
    description => 'Change set for test',
    change_source_name => 'HOTLOG_SOURCE',
    stop_on_ddl => 'n',
    begin_date => sysdate,
    end_date => null);
end;
/



After successfully create the change set, it is time to create the change table. This important because columns to be change captured and the source table will be defined here by the column_type_list and source_table parameters.

--if exists, first drop the change set
exec dbms_cdc_publish.drop_change_table('SCH_DMALL','PRODUCT_CDC','Y');

--create the change table
begin
  dbms_cdc_publish.create_change_table(
  owner             => 'sch_dmall',
  change_table_name => 'product_cdc',
  change_set_name   => 'CS_PRODUCT',
  source_schema     => 'SCH_DMALL',
  source_table      => 'PRODUCT',
  column_type_list => 'ID NUMBER, DELETED NUMBER, SELLER_ID NUMBER, LASTMODIFIEDDATE DATE, CATEGORY_ID NUMBER, ' || 
                      'DIS_PRICE NUMBER, PRICE NUMBER, TITLE VARCHAR2(255), SUBTITLE VARCHAR2(255), ' || 
                      'PRODUCTSTATUS VARCHAR2(255), SALESTATUS VARCHAR2(255), URLWORDS VARCHAR2(255) ',
  capture_values  =>'both',
  rs_id           => 'y',
  row_id          => 'y',
  user_id         => 'y',
  timestamp       => 'y',
  object_id       => 'n',
  source_colmap   => 'n',
  target_colmap   => 'y',
  options_string  => 'TABLESPACE TS_DMALL_DATA');
end;
/

After all to start the CDC processes.

begin
  dbms_cdc_publish.alter_change_set(
    change_set_name => 'CS_PRODUCT',
    enable_capture => 'y');
end;
/

When examining the product_cdc table where the changes are collected, there will be some columns ending with '$' sign will exist. These columns are for holding the streams CDC processes internal information (called control columns) which are very helpful for identifying the row information. Especially the 'operation$' and 'commit_timestamp$' columns;

operation$ : D (delete), UN (updated after image), UO (updated before image),  Inserted
commit_timestamp$ : commit timestamp

Before handing the data over the development teams, creating a view can be a good idea. In this case I have created the following view for the development team to consume. Cheers, Ergem.


create or replace view sch_dmall.v_product_cdc as
select
  case
    when operation$='I' then 'I'
    when operation$='UN' and deleted=0 then 'U'
    when operation$='UN' and deleted=1 then 'D'
    when operation$='D' then 'D'
    else operation$
  end as operation,
  id as product_id,
  deleted,
  seller_id,
  category_id,
  dis_price,
  price,
  title,
  subtitle,
  productstatus,
  salestatus,
  cscn$ id,
  commit_timestamp$ create_date
from sch_dmall.product_cdc
where operation$ in ('D','I','UN');



References;
http://docs.oracle.com/cd/E11882_01/server.112/e25554/cdc.htm#DWHSG016

Thursday, January 9, 2014

Resizing Virtual Disks of VirtualBox

I have a virtual box version 4.3.4 64bit installed in Windows7 64bit. As a virtual host there is 64bit Redhat6.2 Enterprise Server and an Oracle Database 11.2.0.3 installed on it. The case is, I need more disk space in my virtual host for the Oracle Database datafiles. I have already assigned three virtual disks and I dont want to add more disks but to extend the existing ones. After extending the virtual disks of the Virtualbox then I need to extend the physical volumes and the logical volumes and then the filesystems.

Hence there is not any user interface functionality, starting from Virtualbox 4.0 there is a way to extend the .vdi virtual disk sizes. "VboxManage" supports to extend and change the .vdi virtual disk attributes. "VboxManage" executable can be found in the following paths of the related operating systems.


--Using Windows as a Host
# "C:\Program Files\Oracle\VirtualBox\VboxManage.exe" modifyhd virtual_hard_disk_name.vdi --resize size_in_mb

--Using Mac as a Host
# /Applications/VirtualBox.app/Contents/MacOS/VBoxManage modifyhd virtual_hard_disk_name.vdi --resize size_in_mb

--Using Linux as a Host
# VBoxManage modifyhd virtual_hard_disk_name.vdi --resize size_in_mb

# "C:\Program Files\Oracle\VirtualBox\VboxManage.exe" list hdds
UUID:           f7a2fda3-c730-465a-b022-8ee89e48a093
Parent UUID:    base
State:          locked write
Type:           normal (base)
Location:       E:\VirtualBox VMs\rhel6\rhel6_root.vdi
Storage format: VDI
Capacity:       10240 MBytes

UUID:           2be1f81e-b86b-41fe-9268-8622b5f4e4ec
Parent UUID:    base
State:          locked write
Type:           normal (base)
Location:       E:\VirtualBox VMs\rhel6\rhel6_oracle01.vdi
Storage format: VDI
Capacity:       20480 MBytes

UUID:           e62699cc-19dc-4e34-bf09-8e1a9ac6b415
Parent UUID:    base
State:          locked write
Type:           normal (base)
Location:       E:\VirtualBox VMs\rhel6\rhel6_oracle02.vdi
Storage format: VDI
Capacity:       20480 MBytes

UUID:           2d2d2490-d8a3-4184-bf54-02d874264f1b
Parent UUID:    base
State:          locked write
Type:           normal (base)
Location:       E:\VirtualBox VMs\rhel6\rhel6_oracle03.vdi
Storage format: VDI
Capacity:       20480 MBytes

UUID:           3d4385bf-ac3b-4ae7-a3f3-13c14fac4ff5
Parent UUID:    base
State:          locked write
Type:           normal (base)
Location:       E:\VirtualBox VMs\rhel6\rhel6_oracle04.vdi
Storage format: VDI
Capacity:       30720 MBytes

Virtual drives of the Virtualbox can be extended by using the following command (Please refer to your installation directory of the virtualbox to locate and execute the VboxManage.exe). VboxManage accepts either the full path of the virtual disk or the UUID of the disk. I prefered the UUID to extend.


# "C:\Program Files\Oracle\VirtualBox\VboxManage.exe" modifyhd 2d2d2490-d8a3-4184-bf54-02d874264f1b --resize 30170
0%...10%...20%...30%...40%...50%...60%...70%...80%...90%...100%

As you can examine from the following output that the disk UUID 2d2d2490-d8a3-4184-bf54-02d874264f1b is extended from 20480MB to 30720MB successfully.


# "C:\Program Files\Oracle\VirtualBox\VboxManage.exe" list hdds
UUID:           f7a2fda3-c730-465a-b022-8ee89e48a093
Parent UUID:    base
State:          created
Type:           normal (base)
Location:       E:\VirtualBox VMs\rhel6\rhel6_root.vdi
Storage format: VDI
Capacity:       10240 MBytes

UUID:           2be1f81e-b86b-41fe-9268-8622b5f4e4ec
Parent UUID:    base
State:          created
Type:           normal (base)
Location:       E:\VirtualBox VMs\rhel6\rhel6_oracle01.vdi
Storage format: VDI
Capacity:       20480 MBytes

UUID:           e62699cc-19dc-4e34-bf09-8e1a9ac6b415
Parent UUID:    base
State:          created
Type:           normal (base)
Location:       E:\VirtualBox VMs\rhel6\rhel6_oracle02.vdi
Storage format: VDI
Capacity:       20480 MBytes

UUID:           2d2d2490-d8a3-4184-bf54-02d874264f1b
Parent UUID:    base
State:          created
Type:           normal (base)
Location:       E:\VirtualBox VMs\rhel6\rhel6_oracle03.vdi
Storage format: VDI
Capacity:       30720 MBytes

UUID:           3d4385bf-ac3b-4ae7-a3f3-13c14fac4ff5
Parent UUID:    base
State:          created
Type:           normal (base)
Location:       E:\VirtualBox VMs\rhel6\rhel6_oracle04.vdi
Storage format: VDI
Capacity:       30720 MBytes

The rest of the operations will be held in the virtual host, which is RHEL6.2 64bit with logical volume manager used.

First /dev/sd* devices should be rescanned. I used /sbin/partprobe and then a reboot because all my devices where mounted. In order not to reboot, all devices should be unmounted first.

After the reboot, checking with fdisk gives the updated disk size of 30Gb. But the problem is the partition itself. The partition is still 20G and there is a way to extend it which is simply removing and recreating it. But, the thing is, i dont want to lose the data inside this partition which is a part of my volume group. After some investigation, I learned that if I delete the partition and create it with same SystemID (8e - Linux LVM) and stating from the very same starting block my data should not be lost. This worths trying.


# fdisk /dev/sdc

WARNING: DOS-compatible mode is deprecated. It's strongly recommended to
         switch off the mode (command 'c') and change display units to
         sectors (command 'u').

Command (m for help): p

Disk /dev/sdc: 32.2 GB, 32212254720 bytes
255 heads, 63 sectors/track, 3916 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00079340

   Device Boot      Start         End      Blocks   Id  System
/dev/sdc1               1        2611    20970496   8e  Linux LVM

Command (m for help): d
Selected partition 1

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-3916, default 1):
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-3916, default 3916):
Using default value 3916

Command (m for help): t
Selected partition 1
Hex code (type L to list codes): 8e
Changed system type of partition 1 to 8e (Linux LVM)

Command (m for help): p

Disk /dev/sdc: 32.2 GB, 32212254720 bytes
255 heads, 63 sectors/track, 3916 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00079340

   Device Boot      Start         End      Blocks   Id  System
/dev/sdc1               1        3916    31454246   8e  Linux LVM

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.

WARNING: Re-reading the partition table failed with error 16: Device or resource busy.
The kernel still uses the old table. The new table will be used at
the next reboot or after you run partprobe(8) or kpartx(8)
Syncing disks.

After the reboot it seems the partition is extended successfully and my LVM works fine. Next, I should extend my physical volume pv's.


# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda1             9.9G  7.1G  2.3G  76% /
tmpfs                 2.0G  100K  2.0G   1% /dev/shm
/dev/mapper/vg_oracle-lv_oracle
                       30G   17G   12G  61% /oracle
/dev/mapper/vg_oracle-lv_oradata
                       60G   53G  3.2G  95% /oradata
shmfs                 2.0G  100K  2.0G   1% /dev/shm
shared                120G   94G   26G  79% /media/sf_shared
/dev/sr0               62M   62M     0 100% /media/VBOXADDITIONS_4.3.4_91027

# pvresize /dev/sdc1 30720
  Physical volume "/dev/sdc1" changed
  Failed to read physical volume "30720"
  1 physical volume(s) resized / 0 physical volume(s) not resized

# pvdisplay /dev/sdc1
  --- Physical volume ---
  PV Name               /dev/sdc1
  VG Name               vg_oracle
  PV Size               30.00 GiB / not usable 986.00 KiB
  Allocatable           yes
  PE Size               4.00 MiB
  Total PE              7679
  Free PE               2560
  Allocated PE          5119
  PV UUID               bkjcdV-ZOMc-0pSe-BfcW-pbny-ka4f-MiDBFO

# vgdisplay
  --- Volume group ---
  VG Name               vg_oracle
  System ID           
  Format                lvm2
  Metadata Areas        4
  Metadata Sequence No  21
  VG Access             read/write
  VG Status             resizable
  MAX LV                0
  Cur LV                2
  Open LV               2
  Max PV                0
  Cur PV                4
  Act PV                4
  VG Size               109.98 GiB
  PE Size               4.00 MiB
  Total PE              28156
  Alloc PE / Size       23015 / 89.90 GiB
  Free  PE / Size       5141 / 20.08 GiB
  VG UUID               9ERVOu-ATwS-j7H5-5vce-PLj9-3aG9-c7biBO



with a couple of reboots it seems that it is possible to extend the virtual disks of Virtualbox without dataloss... Cheers..