Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

A question about SET COLSEP '|'

dariyooshMay 28 2010 — edited May 28 2010
Hello everybody,


I have a question about directing query output into TXT file. At the beginning of my sql script file I write 
SET TRIMSPOOL ON;
SET COLSEP '|';
This allows me to have the query result as a pipe delimited file. However, there are always several space characters between pipe and the columns, that is, for example instead of having 
a1|b1|c1|d1
a2|b2|c2|d2
a3|b3|c2|d3
I obtain the following: 
a1   |   b1   |   c1  |  d1
a2   |   b2   |   c2  |  d2
a3   |   b3   |   c2  |  d3
As long as you open this file with Excel, this is not really annoying, but the problem is that my TXT file is read directly by a KornShell script and these undesired space characters cause problem. Is there any way to get rid of them?


Thanks in advance,

Kind Regards,
Dariyoosh
This post has been answered by Frank Kulash on May 28 2010
Jump to Answer

Comments

Hi,

the UEK is the Oracle Unbreakable Linux Kernel, the other the 100% Redhat compatible kernel.
Both kernels are o.k. to run databases, however it is said that the performance with the UEK Kernel is better. (However I have not tested it, and don't know about the difference in VM environments). No matter what, both are supported.

There is just one small thing to know: At the moment ACFS won't work with the UEK Kernel. But that is the only exception.
If you just running databases with or without ASM I would choose the UEK kernel.

BTW. There is no XEN type of kernel anymore, since the UEK kernel decides at boot time, if it runs paravirtualized or not (like the RHEL 6 kernel).

Regards
Sebastian
user67111
Thanks for the information. It seems like I should choose the UEK kernel. I don't use ACFS, but I do use ASM.
And, I think that the UEK kernel will support HugePages too.
Hi,

yes and no... UEK will support HugePages, but unfortunately OVM does not yet fully support HugePages in paravirtualized environments:

Setup HugePages in an Guest Does Not Work with Oracle VM 2.1 or 2.1.1 (Doc ID 728063.1)

Regards
Sebastian
micheloe
Interesting replies here. OracleVM 2.2 (actually 2.1.2 for that matter) has support for HugePages in HVM and PVHVM guests (unfortunately still not in PVM guests). Also take in mind whether or not the use of superpages for guest memory is advisable to use for your guests. And yes, the UEK kernel has support for HugePages. Unfortunately for me, the UEK kernel (which is a pv_ops kernel) doesn't boot for PVM guests with VCPU's > 1:

BUG: unable to handle kernel NULL pointer dereference at (null)
IP: [<ffffffff81439d18>] cache_add_dev+0x16/0x2be
...
Call Trace:
[<ffffffff81439ff3>] cache_sysfs_init+0x33/0x5e
[<ffffffff81439fc0>] ? cache_sysfs_init+0x0/0x5e
[<ffffffff8100a06a>] do_one_initcall+0x5f/0x15a
[<ffffffff81b5994a>] kernel_init+0x15b/0x1b5
[<ffffffff81012dea>] child_rip+0xa/0x20
[<ffffffff81011fd1>] ? int_ret_from_sys_call+0x7/0x1b
[<ffffffff8101275d>] ? retint_restore_args+0x5/0x6
[<ffffffff81012de0>] ? child_rip+0x0/0x20
Code: 80 a9 b3 81 48 c7 04 03 00 00 00 00 5b 41 5c 41 5d 41 5e c9 c3 55 48 89 e5 41 57 41 56 41 55 41 54 53 48 83 ec 18 e8 68 7d bd ff <8b> 07 49 89 ff 41 be fe ff ff ff 89 45 c4 66 8b 05 73 9e cd 00
RIP [<ffffffff81439d18>] cache_add_dev+0x16/0x2be
RSP <ffff8801f7121e40>
CR2: 0000000000000000
---[ end trace e93713a9d40cd06c ]---
Kernel panic - not syncing: Fatal exception
Pid: 1, comm: swapper Tainted: G D 2.6.32-100.36.1.el5uek #1
Call Trace:
[<ffffffff81430227>] ? init_memory_mapping+0x51e/0x560
[<ffffffff81057ada>] panic+0xa5/0x162
[<ffffffff814400e5>] ? profile_cpu_callback+0x27/0x261
[<ffffffff81430227>] ? init_memory_mapping+0x51e/0x560
[<ffffffff812b1500>] ? get_random_bytes+0x20/0x22
[<ffffffff81057774>] ? init_oops_id+0x26/0x36
[<ffffffff810577a7>] ? print_oops_end_marker+0x23/0x25
[<ffffffff81430227>] ? init_memory_mapping+0x51e/0x560
[<ffffffff81444d16>] oops_end+0xb7/0xc7
[<ffffffff8103770a>] no_context+0x1f1/0x200
[<ffffffff81430227>] ? init_memory_mapping+0x51e/0x560
[<ffffffff8100e531>] ? xen_force_evtchn_callback+0xd/0xf
[<ffffffff8103795d>] __bad_area_nosemaphore+0x183/0x1a6
[<ffffffff81037a0a>] bad_area_nosemaphore+0x13/0x15
[<ffffffff814461a3>] do_page_fault+0x15d/0x299
[<ffffffff812dfc77>] ? get_device+0x19/0x1f
[<ffffffff81444225>] page_fault+0x25/0x30
[<ffffffff81439d18>] ? cache_add_dev+0x16/0x2be
[<ffffffff81439ff3>] cache_sysfs_init+0x33/0x5e
[<ffffffff81439fc0>] ? cache_sysfs_init+0x0/0x5e
[<ffffffff8100a06a>] do_one_initcall+0x5f/0x15a
[<ffffffff81b5994a>] kernel_init+0x15b/0x1b5
[<ffffffff81012dea>] child_rip+0xa/0x20
[<ffffffff81011fd1>] ? int_ret_from_sys_call+0x7/0x1b
[<ffffffff8101275d>] ? retint_restore_args+0x5/0x6
[<ffffffff81012de0>] ? child_rip+0x0/0x20

and running a DB VM with only one VCPU is not really an option huh? Sigh...
We've been trying to track down that bug, but haven't had much luck so far. Mainly because we can't reproduce it ourselves. Do you have a testcase that reliably reproduces this panic? If so, please share it with us, so we can get this fixed. Best to use your Oracle Support channel for that.
micheloe
For me, I can always reproduce this kernel panic on all Oracle VM 2.2 latest servers when booting a paravirtualized guest with more than one VCPU with the UEK kernel. Yesterday I finally found a testcase that always reproduces it at will:

For a paravirtualized guest (OEL 5 update 7), edit vm.cfg to have vcpus=2 (or any number above 1). Booting the VM with the UEK kernel this way will always produce that kernel panic here. As soon as I change back vcus to 1 in the vm.cfg, the virtualized guest boots fine with the UEK kernel. I have the exact same result on different types of (IBM) hardware:

* IBM System x3550 type 7978KPG with one 2.33GHz Xeon Quad core (E5410) and 18GB of memory
* IBM System x3630 M3 type 737764G with two 2,67GHz Xeon Hexa core (X5650) and 96GB of memory

I'd be glad to share this with OSS, however AFAIK we have no basic/premier support for OEL - only network/update support. I'd be happy to work with OSS and tackle this one though. I'll check with the masters here to see whether or not we have OEL basic/premier support in some way.

The same panic shows up on the xen-users list in 2009 in a thread but doesn't give any pointers whatsoever (http://lists.xensource.com/archives/html/xen-users/2009-12/msg00186.html). It's also completely different hardware for that matter.
Herbert van den Bergh-Oracle
Could you post your vm.cfg? Have you tried different numbers of disks/nics for the guest?
Also, when the guest is booted with one vcpu, get the output of: 
cd /sys/devices/system/cpu/cpu0/cache/
grep -r . .
Edited by: Herbert van den Bergh on Aug 11, 2011 8:53 AM
micheloe
Hi Herbert,

First of all thanks for your efforts. Below an example of a vm.cfg with the problem exposed:

-----
name = 'basisvm'
bootloader = '/usr/bin/pygrub'
disk = [
'phy:/dev/vg1/basisvm-root,xvda1,w',
'phy:/dev/vg1/basisvm-var,xvda2,w',
'phy:/dev/vg1/basisvm-swap,xvda3,w'
]
disk_other_config = [['xvda1', 'ionice', 'sched=best-effort,prio=5'], ['xvda2', 'ionice', 'sched=best-effort,prio=5'], ['xvda3', 'ionice', 'sched=best-effort,prio=7']]
vif = [
'mac=00:16:3e:a0:00:00, bridge=xenbr0',
'mac=00:16:3e:b0:00:00, bridge=xenbr1',
]
vfb = ['type=vnc,vncunused=1,vnclisten=0.0.0.0,vncpasswd=XXXXXXXXXXXXXXXXX']
cpus="18-23"
vcpus=2
vcpu_avail=2
memory = 512
keymap = 'en-us'
on_crash = 'restart'
on_reboot = 'restart'
-----

As you can see it is using LVM-backed VBD's at the moment, I can try file backed VBD's if you wish. Below the output of the grep command from the VM after booting with one VCPU:

-----
[root@basisvm ~]# cd /sys/devices/system/cpu/cpu0/cache/
[root@basisvm cache]# grep -r . .
./index3/shared_cpu_map:00000000,00000000,00000000,00000000,00000000,00000000,00000000,00000001
./index3/size:12288K
./index3/number_of_sets:12288
./index3/ways_of_associativity:16
./index3/physical_line_partition:1
./index3/coherency_line_size:64
./index3/level:3
./index3/type:Unified
./index2/shared_cpu_map:00000000,00000000,00000000,00000000,00000000,00000000,00000000,00000001
./index2/size:256K
./index2/number_of_sets:512
./index2/ways_of_associativity:8
./index2/physical_line_partition:1
./index2/coherency_line_size:64
./index2/level:2
./index2/type:Unified
./index1/shared_cpu_map:00000000,00000000,00000000,00000000,00000000,00000000,00000000,00000001
./index1/size:32K
./index1/number_of_sets:128
./index1/ways_of_associativity:4
./index1/physical_line_partition:1
./index1/coherency_line_size:64
./index1/level:1
./index1/type:Instruction
./index0/shared_cpu_map:00000000,00000000,00000000,00000000,00000000,00000000,00000000,00000001
./index0/size:32K
./index0/number_of_sets:64
./index0/ways_of_associativity:8
./index0/physical_line_partition:1
./index0/coherency_line_size:64
./index0/level:1
./index0/type:Data
-----

I have also tried booting the VM with only one VBD (in single user mode because it lacks a /var partition) and also with only one network device, but this makes no difference: the kernel panics with the same call trace. I've also checked if we do have basic/premier support for OEL ourselves or one of our customers but unfortunately the answer is no at this time.
micheloe
Reading my post again and comparing it to the output of the grep command on the OracleVM itself, the shared_cpu_map looks off or wrong. This it what it looks like on dom0:

-----
./index3/shared_cpu_map:0000000f
./index3/size:12288K
./index3/number_of_sets:12288
./index3/ways_of_associativity:16
./index3/physical_line_partition:1
./index3/coherency_line_size:64
./index3/level:3
./index3/type:Unified
./index2/shared_cpu_map:00000003
./index2/size:256K
./index2/number_of_sets:512
./index2/ways_of_associativity:8
./index2/physical_line_partition:1
./index2/coherency_line_size:64
./index2/level:2
./index2/type:Unified
./index1/shared_cpu_map:00000003
./index1/size:32K
./index1/number_of_sets:128
./index1/ways_of_associativity:4
./index1/physical_line_partition:1
./index1/coherency_line_size:64
./index1/level:1
./index1/type:Instruction
./index0/shared_cpu_map:00000003
./index0/size:32K
./index0/number_of_sets:64
./index0/ways_of_associativity:8
./index0/physical_line_partition:1
./index0/coherency_line_size:64
./index0/level:1
./index0/type:Data
-----
I think the mask looks different because UEK prints it differently from the OVM server kernel. This doesn't really tell me what is happening, the output doesn't look all that unusual to me. What happens if you remove the vm.cfg option cpus="18-23" or vcpu_avail?
micheloe
Hi Herbert,

Of course, there's about 4 years difference between those kernels, no wonder it looks different. Removing the cpus="18-23" option did not change behaviour, but the good news is that removing the "vcpu_avail" option now correctly boots the UEK kernel with more than one VCPU. :-) So somehow the culprit is the vcpu_avail option, even though it was set at the same amount of the vcpus option.

The reason this parameter was there is because in the past we used it on VM's to be able to dynamically scale up the amount of VCPUs. We have now moved away from it, but the parameter is still there. Funny thing is, this morning I already found this out when running the YUM updates for the new environment, where I already disabled this parameter (vcpus_avail). I accidently booted the UEK kernel (I forgot to change grub.conf) and was surprised to see the UEK kernel running with 4 VCPUs.

This is good news, however IMHO the kernel should be able to work like this, shouldn't it? FWIW, I'd be glad to help investigate further. Thanks for the effort!
I'm glad you found a solution. We'll get this fixed.
1 - 12
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jun 25 2010
Added on May 28 2010
3 comments
8,290 views