Oracle ORA600 News Aggregator

Oracle OpenWorld 2010: Sessions By OakTable Members

kurtvm — Thu, 02 Sep 2010 21:45:12 +0200

As Oracle OpenWorld is just around the corner and you are probably getting your session schedule together, I thought I’d pass on this (nearly complete) list of sessions of which one or more of the presenters is an OakTable member. There is no doubt in my mind that these sessions will contain some of the best technical content presented at OpenWorld so be sure and pre-register for these sessions today. Hope to see you there!

RAC Investigation on Low-Memory Linux

kurtvm — Thu, 02 Sep 2010 21:05:44 +0200

Back in the Oracle 9i days, I was one of those people who got on eBay to buy firewire PCI cards and disks that could do non-exclusive login. Remember that? The first time a little test cluster could be cheap enough for the home enthusiast? I still have the parts in my closet.

Of course, we all know what happened after that – virtualization. It didn’t take long before my home-built test clusters were running on VMware. (Personally, I think that virtualization really started because of those NES and SNES emulators. Most great achievements start with a geek who wants to play more video games.) There are lots of people now who run RAC on virtual environments and it’s easy to find tutorials on the web for many different OS and VM combinations.

Low-Memory Linux

Something I haven’t seen many other people do is RAC with a very small memory configuration. Like 760M of memory per server. (!) Of course you’d only do this for a hobby setup – never on a system where you want any kind of support. But I’m kinda cheap… and running RAC on these small VMs means that I don’t have to go buy an expensive new home computer. My current gateway laptop with Vista Home does the job quite nicely!

10.2 and 11.1 RAC will install and run on servers with 760M of memory. But things were a little unstable at first. Now I’m the curious type… I like to fiddle with things… so I investigated a little bit.

Basic Unix Investigation

There are two basic investigation scenarios:

what happened in the past	My main tool is sar (System Activity Reporter). Or Java-based ksar on my desktop – it gets data via ssh and graphs it.
what is happening now	My starting point is vmstat and top. To dig a little deeper, I might then use other tools like ps, free, iostat or netstat.

In this particular case, I noticed pretty quickly from the top utility that one process was consuming over 30% of the system’s memory! (Note: in top, you can press the ‘<’ and ‘>’ keys to move the sort column left and right. The initial sort column is %CPU. I moved it one column to the right, sorting by %MEM.)

top - 18:41:13 up 5:28, 3 users, load average: 0.04, 0.35, 0.60 Tasks: 180 total, 2 running, 178 sleeping, 0 stopped, 0 zombie Cpu(s): 0.7%us, 3.7%sy, 0.0%ni, 89.4%id, 6.3%wa, 0.0%hi, 0.0%si, 0.0%st Mem: 767020k total, 754296k used, 12724k free, 7084k buffers Swap: 1540088k total, 654696k used, 885392k free, 361800k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 17435 oracle RT 0 230m 229m 31m S 0.3 30.6 0:26.21 /u01/crs/oracle/product/11.1.0/crs/bin/ocssd.bin 7765 oracle 15 0 464m 111m 102m S 0.0 14.9 0:04.52 ora_smon_RAC1 7743 oracle -2 0 445m 90m 83m S 0.0 12.1 0:12.01 ora_lms0_RAC1 7783 oracle 15 0 440m 70m 66m S 0.0 9.4 0:05.35 ora_mmon_RAC1 20321 oracle 15 0 438m 48m 45m S 0.0 6.5 0:00.88 oracleRAC1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) 8676 oracle 16 0 437m 46m 45m S 0.0 6.2 0:02.69 oracleRAC1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) 8801 oracle 15 0 440m 44m 41m S 0.0 6.0 0:04.57 ora_cjq0_RAC1

This process (ocssd) is Oracle’s Cluster Synchronization Services Daemon. It’s the process that sends and receives heartbeats from other nodes. Any delays sending or receiving those heartbeats can cause node evictions (a.k.a. server reboots) – so it’s a pretty important process! That’s why it runs with realtime (RT) scheduling priority, as you can see in the above output from top.

I was surprised that CSS uses so much physical memory – usually Linux is very good at memory management. In top, the VIRT column shows how much total memory each process is using, while the RES column shows how much actual physical memory Linux has allocated to it. It’s clear that Linux is pretty actively managing the physical memory for other processes.

A quick glance at vmstat shows that although we are actively swapping, it seems under control. This is about what I’d expect when we’re idle and all of the processes except CSS are sharing only 500M of memory:

collabn1:/home/oracle[RAC1]$ vmstat 5 procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------ r b swpd free buff cache si so bi bo in cs us sy id wa st 0 0 604144 14024 34948 300632 0 0 32 86 1044 1797 1 8 84 7 0 2 1 603912 11224 34968 303968 87 0 763 81 1083 1902 3 11 56 30 0 0 0 604840 12660 34912 303904 25 0 253 38 1050 1975 2 11 69 18 0 0 0 604840 12728 34924 303900 0 0 68 106 1043 1975 1 9 82 8 0 0 0 604784 14536 34936 304140 19 0 89 71 1043 2042 1 5 81 13 0 1 0 604752 14168 34944 304496 6 0 119 21 1040 2016 2 8 80 11 0 0 1 604736 18732 35020 306212 0 0 384 73 1050 2489 1 10 70 19 0 1 1 604736 11144 35232 311252 104 0 1209 128 1074 2024 2 12 38 47 0 3 1 607900 8056 30788 307352 77 1500 504 1661 1055 2642 9 16 56 19 0 0 0 607900 9836 30800 307360 0 0 71 70 1031 1798 1 6 85 8 0 1 0 607884 10536 30812 307400 8 0 44 93 1031 1832 1 4 87 8 0

The SO column tells us when memory is written to disk (and removed from physical). The SI column tells us when memory is read from disk (and put back in physical). On a side note, remember that on a healthy Unix system the free memory is always small. Sometimes this is confusing at first.

Linux Process Memory Investigation

Nonetheless, I’m not happy that CSS is using 30% of my physical memory in this highly-constrained hobby environment. Why is Linux allowing this? The first clue comes simply from the output of the familiar unix ps utility:

collabn1:/home/oracle[RAC1]$ ps v -C ocssd.bin PID TTY STAT TIME MAJFL TRS DRS RSS %MEM COMMAND 17435 ? SLl 0:08 7 588 235331 234840 30.6 /u01/crs/oracle/product/11.1.0/crs/bin/ocssd.bin

On Linux, the “v” flag tells ps to give information relevant to virtual memory. TRS and DRS tell me how much physical (resident) memory is used for machine executable code (text) and data, respectively. But more importantly – the STAT column gives some informative BSD-style flags about the process. That capital-L indicates that CSS has some pages that are locked into physical memory. Bingo.

If I have root access, then I can get a very detailed report on process memory usage with the pmap command. The output was a little long, so I’ve abbreviated it here:

[root@collabn1 ~]# pmap -x 17435 17435: /u01/crs/oracle/product/11.1.0/crs/bin/ocssd.bin Address Kbytes RSS Anon Locked Mode Mapping 00110000 656 - - - r-x-- libhasgen11.so 001b4000 8 - - - rwx-- libhasgen11.so ... 37 more library blocks 02a0d000 100 - - - rwx-- [ anon ] 02a26000 4 - - - --x-- [ anon ] 02a27000 10240 - - - rwx-- [ anon ] 03427000 4 - - - --x-- [ anon ] 03428000 10240 - - - rwx-- [ anon ] ... 10 more anonymous blocks, half are 10240K 08048000 592 - - - r-x-- ocssd.bin 080dc000 4 - - - rwx-- ocssd.bin ... 30 more anonymous blocks, half are 10240K bfe40000 148 - - - rwx-- [ stack ] bfe65000 8 - - - rw--- [ anon ] -------- ------- ------- ------- ------- total kB 235920 - - -

Interestingly, the linux pmap utility does not indicate any locked memory! I don’t know whether that output column is non-functional or if it refers only to some particular kind of locking. But at any rate, I know something is locked. I couldn’t think of anything better, so the next place I looked was in the Linux /proc pseudo-filesystem.

collabn1:/home/oracle[RAC1]$ grep Vm /proc/17435/status VmPeak: 235924 kB VmSize: 235920 kB VmLck: 235920 kB VmHWM: 234844 kB VmRSS: 234840 kB VmData: 202272 kB VmStk: 156 kB VmExe: 592 kB VmLib: 31960 kB VmPTE: 268 kB

Now we’re talking. The process has a total of 235920 kB of memory – and it’s ALL locked. On a normal RAC system you’d want this. Generally, important realtime processes should be locked so that they are never delayed by paging or swapping. (Remember how that could cause node reboots?)

But I personally doubt that all of the memory really NEEDS to be locked, and I think that Linux will actually do a decent job of not swapping the most important parts. And my highly constrained hobby environment will probably run much smoother if Linux gets more flexibility in how it manages it’s measly 760M of memory.

Unlocking Linux Process Memory

But is it actually possible to unlock the process memory? As far as I know, Oracle provides no option to disable CSS memory locking. (For good reason.) There is the system call munlockall() – which unlocks all of a particular processes’ memory. But the CSS process itself would have to call this function. And of course it will not. Or will it?

If you’ve got root, then there’s a hacker-back-door way of doing this. Remember, you’d be crazy to try this anywhere besides a dark closet at home. And if you type too slow then CSS could reboot your machine.

But watch this…

[root@collabn1 ~]# grep Vm /proc/17435/status VmPeak: 235924 kB VmSize: 235920 kB VmLck: 235920 kB VmHWM: 234844 kB VmRSS: 234840 kB VmData: 202272 kB VmStk: 156 kB VmExe: 592 kB VmLib: 31960 kB VmPTE: 268 kB [root@collabn1 ~]# gdb -p 17435 <<EOF > call munlockall() > quit > EOF GNU gdb Fedora (6.8-27.el5) Copyright (C) 2008 Free Software Foundation, Inc. License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html> This is free software: you are free to change and redistribute it. There is NO WARRANTY, to the extent permitted by law. Type "show copying" and "show warranty" for details. This GDB was configured as "i386-redhat-linux-gnu". Attaching to process 17435 Reading symbols from /u01/crs/oracle/product/11.1.0/crs/bin/ocssd.bin...done. ... 17 more Reading/Loading symbols. [Thread debugging using libthread_db enabled] [New Thread 0xb7f629f0 (LWP 17435)] ... 19 more New Threads. Loaded symbols for /lib/libpthread.so.0 Reading symbols from /lib/libnsl.so.1...done. ... 13 more Loaded/Reading symbols. 0x008e7402 in __kernel_vsyscall () (gdb) $1 = 0 (gdb) The program is running. Quit anyway (and detach it)? (y or n) [answered Y; input not from terminal] Detaching from program: /u01/crs/oracle/product/11.1.0/crs/bin/ocssd.bin, process 17435 [root@collabn1 ~]# grep Vm /proc/17435/status VmPeak: 235924 kB VmSize: 235920 kB VmLck: 0 kB VmHWM: 234844 kB VmRSS: 234840 kB VmData: 202272 kB VmStk: 156 kB VmExe: 592 kB VmLib: 31960 kB VmPTE: 268 kB

Ha. This is something you won’t find on metalink. After generating some activity on the system, the top utility shows me that Linux has significantly reduced the physical memory used by CSS.

These days I’ve actually scripted this for my home and classroom VM environments. I haven’t done a careful comparison or analysis, but it really has seemed to me that my low-memory Linux systems run noticeably smoother.

APEX Meetup @ OOW 2010

kurtvm — Thu, 02 Sep 2010 19:53:27 +0200

It's an annual tradition, so also this year we'll organize an APEX Meetup at the 4th Street Bar & Deli on the Tuesday (7.30 PM) during Oracle Open World.

Here you find some pictures of last year. As you can see, it's always fun with nice people, drinks and food.

Every year we get more people during the meetup, so we started to get sponsors to help to pay for the bills ;-)

Thanks so much to our sponsors of this year:
- Oracle Technology Network (OTN)
- Oracle Development Tools User Group (ODTUG)
- APEX Development Team

If you or your company wants to sponsor aswell, feel free to send me a mail or add a comment to this post. The current total to spend is $500 :-)

See you soon!

EBS, Collaborate, Security, BPEL, OWB, Blog of Note, Hyperion, EPM, Burnout, WiFi

kurtvm — Thu, 02 Sep 2010 19:34:00 +0200

EBS
This week at the E-Business Suite Technology blog:
Oracle Internet Directory and Single Sign-On Certified with EBS 12.1.2 on IBM Linux on System z
New Forms and Reports 10.1.2.3 Bundle Patch Certified with EBS 12
Securing E-Business Suite Web Services with Integrated SOA Gateway
A Primer on Hardware Sizing for Oracle E-Business Suite
Collaborate 11
The call for papers has come out for the Collaborate 11 conference.
Esoteric Security

Great, now even if you invest a fortune in extremely esoteric quantum crypto equipment to guard your data transmissions....you're still vulnerable. So far the system used by the ancients--training a trustworthy courier to keep secrets and sending him to the place the data is needed--seems to still be the best technique available: Hardware hackers defeat quantum crypto • The Register

BPEL

Over at the Online Apps DBA blog there is a handy posting on Troubleshooting BPEL worklist integration with Oracle Single Sign-on.

OWB

The Oracle Warehouse Builder (OWB) Weblog has a recommendation for those upgrading to OWB 11.2.0.1: Recommended OWB Patch before Repository Upgrade or Migration: 9802120

Blog of Note: Rittman Mead Consulting

You'll find a lot of postings here linked from the Ritmann Mead Consulting blog. It's one of the best out there, with consistently high quality technical content. A couple of samples over the last week or two:

Oracle BI EE 11g – Authentication & Authorization – Weblogic Security
and
Oracle BI EE 11g – New BI Server Functions
Hyperion
Speaking of great blogs, In 2 Hyperion has a posting on Long Live The Essbase Add-In!
EPM
New EPM Documentation Portal Available on Oracle.com
The EPM Documentation Portal provides a single entry point to locate documentation, training and other useful information that assists with the implementation process and enhances a customer’s experience with our products.

The tool is available on oracle.com from this link: http://www.oracle.com/us/solutions/ent-performance-bi/index.html.

· On the right hand side of the page, click on Technical Information

· then Enterprise Performance Management Documentation Portal

which takes you to this page: http://www.oracle.com/us/solutions/ent-performance-bi/technical-information-147174.html

Burnout

Over at Lifehacker, always one of my favorite general technology and office life blogs, is an article on the addictive nature of technology and how to keep it from consuming your life:

Why Technology Is So Addictive, and How You Can Avoid Tech Burnout

WiFi

Wifi has become an increasingly important part of day to day life. Windows' wifi builtin is crude at best. Several of these programs look interesting, but I haven't tried them yet, so I can't give you a review. I plan to get the one that measures signal strength of Wi-Fi servers in your home area, though, since it would be nice to know that you are on a channel not getting interference from a neighbor:

6 useful Wi-Fi tools for Windows - Computerworld

Creating Standby with Active Database Duplication in 11gR2 or also known as Wading through the MOS

kurtvm — Thu, 02 Sep 2010 16:42:02 +0200

Several errors prevented the creation of a physical standby database (11.2.0.1) in a DataGuard configuration using the High Availability Console in Grid Control 10.2.0.5…finally resorted to manual methods to finish the task. A combination of DATAGUARD command-line, SQLPLUS and RMAN. The initial goal of this task was to see how automatic (less work for the DBA) 11gR2 is when creating physical standbys. This post will have expanded information when I start testing Logical Standbys in 11gR2.

A. Plan 1 – GC HA Console

(Supposed to be the Easy One)- Create a physical standby using the GC Availability console, twice. Failed at exactly the same spot the three times it was attempted. The setup was between identical nodes (same chipset, OS, etc.) The only option at this point is to create a Service Request. Previous recovery step is successful according to the log, this is the last step of the process. Not seeing errors in the DATAGUARD or alert logs of the primary database. Seeing TNS Fatal connection errors (TNS-12526, TNS-12564) in the alert log of the physical standby. Initial conclusion seems to be related to this document – Connections to NOMOUNT/MOUNTED or RESTRICTED Databases Fail [ID 362656.1].

B. Plan 2 – RMAN Active Duplication using My Oracle Support (MOS) examples

Create a physical standby using RMAN using Active Duplication, one of the newer features in 11g Database. The following document was as least partly correct.. RMAN ‘Duplicate Database’ Feature in 11G [ID 452868.1] The MOS document contradicts itself when mentioning the required initialization parameters for the auxiliary database:

DB_NAME

CONTROL_FILES

DB_BLOCK_SIZE

DB_FILE_NAME_CONVERT

LOG_FILE_NAME_CONVERT

DB_RECOVERY_FILE_DEST (see RMAN DUPLICATE ERROR RMAN-06136 ORA-19801 PARAMETER DB_RECOVERY_FILE_DEST NOT SET [ID 1068315.1] for the reasoning behind this parameter)

A different list is farther down in the document outlined an example initTEST.ora file which is different than the previous list. This is the start of the wading through MOS…trying to pick the most accurate article for the task I am trying to accomplish. I don’t expect all articles to be EXACTLY what I need, but I do expect them to be reproducible (aka accurate) for the versions listed.

DB_NAME=TEST

diagnostic_dest=’E:\oracle’

DB_FILE_name_CONVERT=(‘I:\app\apadhi\oradata\amar’,'E:\oracle\oradata\test’)

LOG_FILE_NAME_CONVERT=( ‘I:\app\apadhi\oradata\amar’,'E:\oracle\oradata\test’)

SGA_TARGET=262144000

CONTROL_FILES=’E:\oracle\oradata\TEST\control01.dbf’

COMPATIBLE= 11.1.0.0.0

Farther down in the MOS document when you get to the following line:

RMAN>DUPLICATE TARGET DATABASE FOR STANDBY –(I added the for standby part of the line that isn’t in the MOS document example during testing)

TO ‘TEST’

FROM ACTIVE DATABASE

DB_FILE_NAME_CONVERT ‘I:\app\apadhi\oradata\amar’,'E:\oracle\oradata\test’;

A reproducible error occurred when I tried to use the db_file_name_convert and log_file_name_convert initialization parameters as part of the command.

RMAN-05535: WARNING: All redo log files were not defined properly.

The above RMAN error led me to another document – duplicate for standby fails with RMAN-05535 when path the same as primary [ID 783113.1]. This document led me to make a change in the DUPLICATE command and moving the DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT to the initTEST.ora file. This error led me down the wrong path didn’t encounter it using the second improved MOS document.

There were also issues with the primary finding (ORA-12154 TNS:could not resolve service name) the physical standby because I use a static listener entry with a non-default port instead of dynamic service registration. That issue was first discovered when trying to create a configuration using the DATAGUARD command-line utility – dgmgrl. Another issue was discovered when using the dgmgrl utility was the case sensitivity of the password file created for the physical standby. That issue was encountered with the error - invalid username or password. You either have to directly copy over the password file from the primary or create the password file with ignorecase=Y. See MOS Document Changing SYS password of PRIMARY database when STANDBY in place to avoid ORA-16191 [ID 806703.1] .

The initialization parameter file and RMAN command that seemed to work for my situation (different node, different data and log file directories, different password file, static listener entry, active duplicate for standby):

db_block_size = 8192

DB_NAME=TEST

compatible = 10.2.0.4.0

CONTROL_FILES=(‘/u01/oradata/TEST/control01.ctl’,'/u01/oradata/TEST/control02.ctl’)

local_listener=’(address=(protocol=tcp)(host=nodename)(port=1523))’

DB_FILE_NAME_CONVERT=(‘/u01/oradata/TRNG’,'/u01/oradata/TEST’)

LOG_FILE_NAME_CONVERT=(‘/u01/oradata/TRNG’,'/u01/oradata/TEST’)

diagnostic_dest=’/u01/app/oracle’

RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER NOFILENAMECHECK;

But all was not right with the world, the archived redo logs from the primary were not being applied.

I knew that by looking at the following sql output on the physical standby.

SQL> select process,status,client_process,sequence#,block#,active_agents,known_agents from v$managed_standby;

I started to wade through MOS again looking for a better example to follow since I had modified the original command for a physical standby.

First off I found this one to remove all remnants of any existing Dataguard configurations, this one was accurate and well-written. How to Safely Remove a Data Guard Broker Configuration [ID 261336.1]

While searching for the best way to remove an existing Dataguard configuration, I came across this article:

Step by Step Guide on Creating Physical Standby Using RMAN DUPLICATE…FROM ACTIVE DATABASE Without Shutting down the Primary and using Primary Active Database Files [ID 1075908.1]

Easier to read and understand from beginning to end than 452868.1, but there are inconsistencies (wouldn’t really call them errors) in this article. In order to do active database duplication the COMPATIBLE initialization parameter needs to be set at a minimum of 11.1.0.0 on both the primary and standby or the following error occurs on creation of the standby with active database duplication:

ERROR when missing COMPATIBLE parameter:

connected to auxiliary database (not started)

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of Duplicate Db command at 09/01/2010 08:32:41

RMAN-03015: error occurred in stored script Memory Script

RMAN-04014: startup failed: ORA-32012: SPFILE format is inconsistent with value of COMPATIBLE parameter

Since my primary database was still at 10.2.0.4….I have to change its parameter first. The active database duplication process will bring over that same compatible parameter for the physical standby. You cannot change this parameter in memory, it has to be added to the spfile or pfile and the primary database restarted as shown in the following example:

PRIMARY> alter system set compatible=’11.1.0.0.0′ scope=spfile;

System altered.

Last step in the article is to start the recovery process….

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Ok, querying V$MANAGED_STANDBY on the standby database will indicate the progress of archived logs being applied from the primary database. There is a problem, it is waiting. This is know as a gap. The primary most current log is 338 and the physical standby is looking for 337. I also encounter errors about inconsistent properties when trying to adjust settings using the dgmgrl utility.

STANDBY> select process,status,client_process,sequence#,block#,active_agents,known_agents from v$managed_standby;

PROCESS STATUS CLIENT_P SEQUENCE# BLOCK# ACTIVE_AGENTS KNOWN_AGENTS

——— ———— ——– ———- ———- ————- ————

ARCH CONNECTED ARCH 0 0 0 0

MRP0 WAIT_FOR_LOG N/A 337 0 3 3

This is where I realize the article (1075908.1) has some inconsistencies…or let’s say it left out some important information. I was not able to validate the DATAGUARD configuration following the instructions as written -

1. Initialization parameter for Dataguard broker had to be changed – dg_broker_start=TRUE.

DGMGRL> show configuration verbose;

Configuration – PRMY,STBY

Protection Mode: MaxPerformance

Databases:

PRMY – Primary database

STBY – Physical standby database

Error: ORA-16525: the Data Guard broker is not yet available

Fast-Start Failover: DISABLED

Configuration Status:

ERROR

2. Received errors related to inconsistent properties between DataGuard and what was set with SQLPLUS.

I start referring back to the 11.2 documentation (Creating a Standby Database with Recovery Manager appendix) located at

http://download.oracle.com/docs/cd/E11882_01/server.112/e10700/rcmbackp.htm#SBYDB4988

I modify the instructions from article 1075908.1 to include the dorecover command and change some of the other parameters slightly..

duplicate target database for standby from active database

dorecover

spfile

parameter_value_convert ‘PRMY’,'STBY’

set db_unique_name=’STBY’

set db_file_name_convert=’/PRMY/’,'/STBY/’

set log_file_name_convert=’/PRMY/’,'/STBY/’

set control_files=’/u01/oradata/PRMY/control01.ctl’

set log_archive_dest_2=”service=PRMY ASYNC REGISTER VALID_FOR=(online_logfile,primary_role)” –required for archive log shipping

set fal_server=’PRMY’

set standby_file_management=’AUTO’;

I can now also verify DATAGUARD for this configuration is correct using dgmgrl>

DGMGRL> show configuration verbose;

Configuration – PRMY,STBY

Protection Mode: MaxPerformance

Databases:

PRMY – Primary database

STBY – Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

And also verify the redo logs are being applied by querying the standby and switching logs on the primary.

STBY>select process,status,client_process,sequence#,block#,active_agents,known_agents from v$managed_standby;

PROCESS   STATUS       CLIENT_P SEQUENCE#     BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
——— ———— ——– ———- ———- ————- ————
ARCH      CONNECTED    ARCH              0          0             0            0
ARCH      CLOSING      ARCH            350          1             0            0
ARCH      CONNECTED    ARCH              0          0             0            0
ARCH      CLOSING      ARCH            348          1             0            0
RFS       IDLE         N/A               0          0             0            0
RFS       IDLE         LGWR            351          7             0            0
RFS       IDLE         UNKNOWN           0          0             0            0
MRP0      APPLYING_LOG N/A             351          7             3            3

PRMY> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 347

Next log sequence to archive 351

Current log sequence 351

Adding the dorecover command applies any outstanding archived logs, starting the managed recovery process so you don’t have to do the last command in the document (1075908.1) as follows:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

11g : Active Database Duplication [ID 568034.1] – You might find this article as well…but not as good as an example as [ID 1075908.1].

Final Conclusions – Since the COMPATIBLE initialization parameter is required to be at 11.1…I will not be using this process for my initial migration/recreation of our physical standbys to 11gR2. We plan on staying at the COMPATIBLE parameter of 10.2.x for several weeks in the primary database so that we can accomplish a downgrade if need be. Staying at 10.2.x will also allow more time to upgrade the Optimizer enabling the newer features in a gradual, controlled process.

Which number takes more space in an Oracle row?

kurtvm — Thu, 02 Sep 2010 15:32:00 +0200

So, which number takes more bytes inside an Oracle row?

A: 123

B: 1000000000000000000000000000000000000

And the correct answer is … (drumroll) … B! The “big” number 1000000000000000000000000000000000000 actually takes less space than the “small” 123!

Let’s verify this:

SQL> select vsize(123) A, vsize(1000000000000000000000000000000000000) B from dual; A B ---------- ---------- 3 2

WTF? Why does such a small number 123 take more space than 1000000000000000000000000000000000000 ?

Well, the answer lies in how Oracle stores numbers. Oracle NUMBER datatype doesn’t store numbers in their platform-native integer format. Oracle uses it’s own format which stores numbers in scientific notation, in exponent-mantissa form. More details about this here.

You can use the DUMP sql function to see the actual binary value of the number data stored:

select dump(123) from dual; DUMP(123) --------------------- Typ=2 Len=3: 194,2,24 SQL> select dump(1000000000000000000000000000000000000) from dual; DUMP(10000000000000 ------------------- Typ=2 Len=2: Typ=2 Len=2: 211,2

So, although the number 1000000000000000000000000000000000000 is bigger than 123, when stored in base-10 exponent form, it really carries much less information in it than 123 (1 x 10^36 vs 123 x 10^0). Oracle doesn’t need many bits for keeping the precision of this large value as it happens to be a power of 10.

See what happens when I store a number only slightly bigger or smaller than the original large number, now the stored number requires much more storage for keeping the required precision:

SQL> select dump(1000000000000000000000000000000000000+1) from dual; DUMP(1000000000000000000000000000000000000+1) ------------------------------------------------------- Typ=2 Len=20: 211,2,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,2 SQL> select dump(1000000000000000000000000000000000000-1) from dual; DUMP(1000000000000000000000000000000000000-1) ----------------------------------------------------------------------------------------- Typ=2 Len=19: 210,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100

Oracle Security

kurtvm — Thu, 02 Sep 2010 15:30:08 +0200

A few things to report about Oracle Security after we have had a short break for familly holidays and also because of a lot of work being done over the last few months. It is nice to be busy in....[Read More]

Posted by Pete On 02/09/10 At 02:27 PM

Is it a bird? Is it a plane? No, it’s Captain Support…

kurtvm — Thu, 02 Sep 2010 13:40:08 +0200

I’m starting to think I’ve made a move into the PC support business, without wanting to or being paid for it…

Over the last couple of weeks my brothers laptop (one of my old ones) has been acting a bit strange. Nothing I could put my finder on, but something was not quite right. Yesterday my “Support Senses” started to tingle when I noticed some files and directories had become read-only for no apparent reason. A meer mortal might have considered trying to diagnose the problem, but this looked like a job for Captain Support. Quicker than a flash Captain Support did a fresh backup of all the photos and documents, then wasted that no good son of a… Dell.

Several hours and many Windows updates later there was a fresh copy of Windows Vista installed. Piece and harmony returned to the world. Captain Support even remembered to configure the VPN connection so his brother could connect to work.

Cheers

Tim…

Captain Support is the ongoing tale of a science nerd who was innocently sequencing sex-incompatibility genes in Brassica oleracea (wild cabbage) when he was spiked by the pins of a radioactive “i386 DX2/66″ chip, transforming him into a PC support superhero.

Using the connection_rate parameter to stop DoS attacks

kurtvm — Thu, 02 Sep 2010 12:06:19 +0200

I recently posted on the oracle-l mailing list about how to stop denial of serice attack. My message is below

We had an application that repeatedly connects to the database via java connection pool fail because the account had become locked. The application kept on trying, the database did not allow the connection and we ended up with thousands of ‘dead’ processes causing the unix server to hang as all memory was used up.

The obvious thing to fix in our case was some form of application logic to recognise that failed connections had been made and stop the repeated connection attempts.

However this could also be used in a denial of service attack. What steps could we take to reduce that risk. The problem as I see it is that the database has reacted correctly and there is not much more we could do at the database level. However I am always open to suggestions.

I received two responses, both of which were valuable. Freek DHooge suggested enabling dead connection detection by using the sqlnet.expire time setting and another mail from Grzegorz Goryszewski directing me to the 11g new feature listener connection rate feature. I set up a test to use both features and here are the results.

Firstly I generated 3 scripts to generate a number of connections into the database

DOA.sh to open sqlplus connections, DOAStart.sh to generate lots of calls of that script and DOAStop.sh to kill all the connections

#!/bin/sh # # Simple shell script to simpulate a DOA # while true do sqlplus -s << EOF <a href="mailto:soe/soe@DB11G">soe/soe@DB11G</a> <a href="mailto:soe/soe@DB11G">soe/soe@DB11G</a> <a href="mailto:soe/soe@DB11G">soe/soe@DB11G</a> EOF done #!/bin/sh # # DOA Controller # count=50 while [ $count -gt 0 ] do count=`expr $count - 1` /home/oracle/DOA.sh > /dev/null & done ps -fu oracle | grep DOA | grep -v grep | awk '{print$2}' > /home/oracle/DOAProcesses #!/bin/sh # # DOA Stop echo Stopping DOA Processes for i in `cat /home/oracle/DOAProcesses` do kill -9 $i done DOAProcCnt=`ps -ef | grep DOA | grep -v grep | wc -l` echo Number processes Running are $DOAProcCnt

Firstly I should mention that I tried running with Swingbench and that just sends a blast of 50 logins in at once which is not what I was trying to emulate. I also locked the soe account I was using which is what the status would be in a real; world attack after 3 failed login atempts.

Enabling sqlnet.expire_time=1 in the sqlnet.ora file did not work and we still had a lot of dead connection which killed the CPU. Note my original problem was with an application grabbing memory but I need to spend more time testing that specific issue whereas this blog is more about the usage of the connection rate parameter in listener.ora which was new to me.

sqlnet.expire_time set

I then unset that paremeter and added the the rate_limit and connection_rate parameters to my listener .This allows 2 connections per second. Note the listener needs restarting, a reload will not be sufficient

LISTENER_server = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = server) (PORT = 1525) (RATE_LIMIT=YES)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1525)) ) ) SID_LIST_LISTENER_server = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /app/oracle/product/10.2.0.4/db_1) (PROGRAM = extproc) ) ) CONNECTION_RATE_LISTENER_server=2

connection_rate parameter enabled

Bingo – the server usage from glance was vastly improved. No died sqlplus processes and CPU not impacted. However whilst tailing the listener log file I could see no evidence that connections were being rejected.

This is a piece of work in progress and I thought I would post my initial findings and follow up later on with more findings.

Pushing the Technical Frontier

kurtvm — Thu, 02 Sep 2010 10:37:46 +0200

I have a dilemma. In the BI system that I’m working on I have the perfect application for the use of Expression Filters. It is almost as if someone specifically devised this problem in order to tempt me into using them, and I really am very tempted indeed. Let me explain. In the application which [...]

I Shall Be At Sangam, Will You….

kurtvm — Thu, 02 Sep 2010 05:57:19 +0200

This is just a quick post to say that I shall be leaving today with Amardeep Sidhu , Ankit Goel and Neeraj Bhatia to attend AIOUG’s annual oracle conference Sangam which is happening at Hyderabad this year. If you are also attending, just come and say Hi! I hope it would be good two days of Oracle and just Oracle . I am not presenting this time so I would have plenty of time to attend my favorite sessions and meet my friend, Francisco Munoz Alvarez and one of the best known Oracle guru’s in this world, Jonathan Lewis.

Its time for me to get going and do the last minute checks for ticket printouts and everything else. Next stop would be Hyderabad!

Purging trace and dump files with 11g ADRCI

kurtvm — Thu, 02 Sep 2010 05:04:43 +0200

In previous versions of Oracle prior to 11g, we had to use our own housekeeping scripts to purge the udump, cdump and bdump directories.

In Oracle 11g, we now have the ADR (Automatic Diagnostic Repository) which is defined by the diagnostic_dest parameter.

So how are unwanted trace and core dump files cleaned out in 11g automatically?

This is done by the MMON background process.

There are two time attributes which are used to manage the retention of information in ADR. Both attributes correspond to a number of hours after which the MMON background process purges the expired ADR data.

LONGP_POLICY (long term) defaults to 365 days and relates to things like Incidents and Health Monitor warnings.

SHORTP_POLICY (short term) defaults to 30 days and relates to things like trace and core dump files

The ADRCI command show control will show us what the current purge settings are as shown below.

adrci> show control ADR Home = /u01/app/oracle/diag/rdbms/ttrlwiki/ttrlwiki: ************************************************************************* ADRID SHORTP_POLICY LONGP_POLICY LAST_MOD_TIME LAST_AUTOPRG_TIME LAST_MANUPRG_TIME ADRDIR_VERSION ADRSCHM_VERSION ADRSCHMV_SUMMARY ADRALERT_VERSION CREATE_TIME -------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ---------------------------------------- 1095473802 720 8760 2010-07-07 08:46:56.405618 +08:00 2010-08-22 22:14:11.443356 +08:00 1 2 76 1 2010-07-07 08:46:56.405618 +08:00

In this case it is set to the defaults of 720 hours (30 days) for the Short Term and 8760 hours (One year) for the long term category.

We can change this by using the ADRCI command ‘set control’

In this example we are changing the retention to 15 days for the Short Term policy attribute (note it is defined in Hours)

adrci> set control (SHORTP_POLICY =360) adrci> show control ADR Home = /u01/app/oracle/diag/rdbms/ttrlwiki/ttrlwiki: ************************************************************************* ADRID SHORTP_POLICY LONGP_POLICY LAST_MOD_TIME LAST_AUTOPRG_TIME LAST_MANUPRG_TIME ADRDIR_VERSION ADRSCHM_VERSION ADRSCHMV_SUMMARY ADRALERT_VERSION CREATE_TIME -------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ---------------------------------------- 1095473802 360 8760 2010-08-27 09:36:09.385370 +08:00 2010-08-22 22:14:11.443356 +08:00 1 2 76 1 2010-07-07 08:46:56.405618 +08:00

We can also manually purge information from the ADR using the ‘purge’ command from ADRCI (note this is defined in minutes and not hours!).

In this example we are purging all trace files older than 6 days. We see that the LAST_MANUPRG_TIME column is now populated.

adrci> purge -age 8640 -type TRACE adrci> show control ADR Home = /u01/app/oracle/diag/rdbms/ttrlwiki/ttrlwiki: ************************************************************************* ADRID SHORTP_POLICY LONGP_POLICY LAST_MOD_TIME LAST_AUTOPRG_TIME LAST_MANUPRG_TIME ADRDIR_VERSION ADRSCHM_VERSION ADRSCHMV_SUMMARY ADRALERT_VERSION CREATE_TIME -------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ---------------------------------------- 1095473802 360 8760 2010-08-27 09:36:09.385370 +08:00 2010-08-22 22:14:11.443356 +08:00 2010-08-27 09:50:07.399853 +08:00 1 2 76 1 2010-07-07 08:46:56.405618 +08:00

Mixed case passwords for Oracle

kurtvm — Thu, 02 Sep 2010 02:03:08 +0200

So, we all know that Oracle used to be non-case sensitive when it came to user names and passwords. We also know that since 11g this is not the case and Oracle, by default, is case sensitive. The one thing I wanted to point out is that even if you are using sec_case_sensitive_logon=false and ignore [...]

Public appearances 2010

kurtvm — Wed, 01 Sep 2010 22:49:12 +0200

Here’s the list of events where I’ll speak this year:

Michigan OakTable Symposium 2010
Ann Arbor, MI
16-17 September 2010

Considering the concentration of OakTable members there, this will be an awesome event!

I will be delivering my “Back to Basics: Choosing The Entry Point to Performance Troubleshooting Wisely” and “Understanding LGWR, log file sync waits and commit performance” sessions there.

Promo video:
http://www.oaktable.net/media/michigan-oaktable-symposium-2010-promo

Agenda & Registration:
http://michigan.oaktable.net/

Oracle Open Closed World
San Francisco, CA
19-22. September

Note that I won’t be speaking at the official Oracle Open World conference, but I will be speaking at a secret underground event there, about some really fun stuff, like deep internals, hacking, kernel tracing and of course advanced troubleshooting ;-) And rest of the time I’ll be in some bar.

NYOUG Fall 2010 Training Session
Manhattan, NYC, NY
16 November 2010

This is a full day seminar organized by NYOUG. I will be delivering my “Scripts and Tools for Oracle Troubleshooting and Advanced Performance Analysis” session there. It’s an updated version of the material I delivered at the Hotsos Symposium Training Day this year.

Agenda & Registration:
http://www.nyoug.org/upcoming_events.htm#NYOUG_Training_Days

UKOUG Tech & EBS Conference (to be confirmed)
Birmingham, UK
29 November – 1 December 2010

I submitted four papers to UKOUG Tech&EBS conference, so if all goes well, I’ll be there in end of Nov/beginning of Dec too.

http://techandebs.ukoug.org/

Oracle Versions

kurtvm — Wed, 01 Sep 2010 21:51:25 +0200

Having discovered that it’s now easy to create polls, I find that it’s a little addictive.

There have been requests for help going all the way back to 7.3 fairly recently on OTN, so I thought I’d set up a poll to see which versions people had in production. If I’ve got it right you’ll be able to mark multiple choices from the list.

View This Poll
Market Research

Update from Mumbai 2nd Sept 2010: It’s fascinating that two percent of the current vote (9 / 527) goes to 8.0 or earlier.

Graphical Work Center Utilization – Creating the Demo Data and Active Server Page

kurtvm — Wed, 01 Sep 2010 21:10:44 +0200

September 1, 2010 Today’s blog article provides a graphical view of production work areas on a factory floor, providing feedback to indicate when the production area is in use. The blog article includes a classic ASP web page which uses VBScript to write the web page on the fly to the browser on the client [...]

Contributions by Angela

kurtvm — Wed, 01 Sep 2010 20:33:00 +0200
Contributions by Angela Golla, Infogram Contributor

PeopleSoft Enterprise PeopleTools 8.50 Has Arrived!

Oracle's PeopleSoft Enterprise PeopleTools 8.50 is now generally available. This new version of PeopleTools is the foundation to the PeopleSoft Enterprise 9.1 applications; however, it is also available to customers running any of the recent PeopleSoft Enterprise applications (8.8, 8.9 and 9.0). Start planning your upgrade now! More information is available at: http://www.oracle.com/us/products/applications/peoplesoft-enterprise/tools-tech/index.html

A listing of Oracle Education classes can be found HERE.

Exadata Book

kurtvm — Wed, 01 Sep 2010 14:38:51 +0200

Here’s some white Exaddata text

Well, I guess it’s official. A couple of weeks ago I committed to write an Exadata book for Apress, along with my intrepid co-author Randy Johnson. For those of you who don’t know Randy, he’s a very experienced Oracle Guy with a wealth of knowledge, particularly around RAC. I think the two of us make a pretty good team - making up for each others weaknesses (oh wait, I should say we have “Complimentary Skill Sets” - yeah that sounds better).

Anyway, it turns out that writing a book is a lot of work! The way Tom Kyte turns them out I thought it must be pretty easy, but I’ve always been a little overconfident. So I’m starting to realize that I may not have time to do as many blog posts as I might like. But I must say that I am really excited about the subject matter! So I think it will be worth the effort. By the way, that’s not the official cover art (or even the official title as far as I know). I just hacked that together with a Adobe Illustrator. ;-)

As I have said many times, I think the Exadata storage software will usher in a whole new era in relational databases. Not just for Oracle, because you know the other guys will be trying to follow in their footsteps. But I think Oracle is miles ahead at this point, and they own their own hardware platform. Oracle claims that it has been their most successful product launch ever and I believe it. They are starting to pop up like weeds. It will be very interesting to see what the future holds for this platform. To be honest, I think we’re just seeing the tip of the iceberg at this point. Anyway, I appreciate the guys at Apress having the faith in us to take on this project. I hope we don’t disappoint them (I don’t think we will).

Convert Ext3 to Btrfs

kurtvm — Wed, 01 Sep 2010 12:05:00 +0200
After I make kernel 2.6.35.4 on Oracle Enterprise Linux 5.4 support Btrfs.
# cd /usr/src
# tar jxvf linux-2.6.35.4.tar.bz2
# cd linux-2.6.35.4
# cp /boot/config-2.6.18-164.el5 .config
# make && make modules_install headers_install install
# mkinitrd /boot/initrd-2.6.35.4 2.6.35.4
# reboot
then installed btrfs-progs-0.19 program.
# cat /etc/enterprise-release
Enterprise Linux Enterprise Linux Server release 5.4 (Carthage)

# rpm -q enterprise-release
enterprise-release-5-0.0.20

# uname -a
Linux oel 2.6.35.4 #1 SMP Wed Sep 1 20:37:04 ICT 2010 x86_64 x86_64 x86_64 GNU/Linux
and then tested to convert ext3 to btrfs (read):
# mount | grep /dev/sda6
/dev/sda6 on /data type ext3 (rw)

# ls /data
linux-2.6.35.4.tar.bz2

# umount /data

# fsck.ext3 /dev/sda6
e2fsck 1.39 (29-May-2006)
/data: ...

# btrfs-convert /dev/sda6
creating btrfs metadata.
creating ext2fs image file.
cleaning up system chunk.
conversion complete.

# mount -t btrfs /dev/sda6 /data

# mount | grep /dev/sda6
/dev/sda6 on /data type btrfs (rw)

# ls /data
ext2_saved linux-2.6.35.4.tar.bz2
we will see ext3/4 snapshot (ext2_saved). we can mount loopback for image in snapshot:
# mount -t ext3 -o loop,ro /data/ext2_saved/image /mnt

# ls /mnt
linux-2.6.35.4.tar.bz2
check some command-line:
# btrfs-show
Label: /data uuid: 7721003c-adcb-4706-8238-68946a5e2547
Total devices 1 FS bytes used 8.73GB
devid 1 size 128.79GB used 128.79GB path /dev/sda6

Btrfs Btrfs v0.19
However, if we need to roll back the conversion(we should backup). we can:
# umount /mnt
# umount /data
# btrfs-convert -r /dev/sda6
Written By: Surachart Opun http://surachartopun.com

DOAG Conference in Nürnberg

kurtvm — Wed, 01 Sep 2010 11:45:01 +0200
This is a short note to point out that I just added to the Public Appearances page the next conference organized by the Deutsche ORACLE-Anwendergruppe (DOAG) in Nürnberg. It will take place on November 16-18. My talk, entitled “Transaktions-Management Internas”, will be the German version of then one I will give at the Michigan OakTable [...]

OBIEE 11g Install - Thoughts

kurtvm — Wed, 01 Sep 2010 08:24:00 +0200
This post is almost a direct copy of my reply to the OBIEE EMG mailing list started by Mark Rittman about Initial Opinions on OBIEE 11g?.

I can't say I've had any success installing the software. So far I've tried on XP Pro (VM on laptop) and Server 2008 (AWS instance). I've chosen the Enterprise install each time.

The laptop I can understand, sort of, as it is severely underpowered. 3 GB RAM total for the machine. I understand the integration with WebLogic/Fusion, but it makes it that much more difficult for those starting out to get it up and running on their personal machines. I can still install the database on my laptop; in fact I have 3 or 4 versions (in VMs) on my machine right now. That's nice.

A couple of days ago, I fired up a cloud Windows Server 2008 server with 18 GB RAM. Installed the database. Created the BI metadata (rcu). Finally began the OBIEE install (Enterprise). It failed finally on one of the configuration steps (12 of 13), I believe it was one of the opmnctl processes.

That said, I haven't read the install documents from front to back yet. I have, however, installed a lot of Oracle software over the years, including EM, OAS (10g), etc. I can usually get the software up and running. This is a different beast though and is not for the weak.

I'll eventually read the docs, sooner rather than later.

On the bright side, installing the latest 11g forces people to learn a bit more about the interactions of the database, EM, WebLogic, etc. That's a good thing.

Some Blog Errors Are Just Too Serious To Ignore. A Comparison of Intel Xeon 5400 (Harpertown) to Intel Xeon 5500 (Nehalem EP).

kurtvm — Tue, 31 Aug 2010 20:51:10 +0200
I’d like to direct readers to an important blog update/correction. In my post entitled An Intel Xeon 5400 System That Outperforms An Intel 5500 (Nehalem EP) System? Believe It…Or Know It I blogged about an erroneous conclusion I had drawn about a test performed on these two processor models. I think the update does the blog [...]

What you Don't Know About Me

kurtvm — Tue, 31 Aug 2010 20:28:00 +0200
Some think I am just a vest, others think I am the most important part of the Oracle ACE Program but few know I am a very secret undercover agent.

If you promise not to tell anyone I will let you into the undercover world that is Miracle Security.

This very secret, undercover outfit is run by one of my keepers but I can't say which. His identity needs to be protected to ensure the safety of his clients. But a man cannot work alone and sometimes a vest can go where a man cannot, on these occasions, like when champagne needs to be smuggled into an AA meeting, they send me, in disguise of course.

Would you like to work for them? Well they don't advertise, they approach you, you are hand selected, tested and put through many, many tests. You think the abuse I suffer in this blog is just fun, well it isn't - each is a carefully designed exercise to see how far I can be pushed. I have earned my insignia, I am the best.

Shhhhhh remember don't tell anyone

Scripting, Doom

kurtvm — Tue, 31 Aug 2010 17:32:00 +0200

Scripting

This posting Effect Of Multiple SHMMAX Settings at the AskDba.org Weblog is useful in itself, but points out something I learned back in my days as a DBA that is of wider utility: Computers are dumb. Yes, I know they seem smart enough, but if you tell them in a batch file: Pour coffee. Put cup in coffee machine filling bracket, that's exactly what they'll do: Pour the coffee all over the floor and then drop the cup in place. The duplicate settings problem can be especially vexing. It reminds me of a time a friend of mine played a CD. Only something completely different played than he had expected. He took out the CD, looked at it, and only then realized that there had been a CD already in the drive and he put the new one in on top of it. Silly error? Perhaps, but things happen. Scripts are prone to this kind of error, and all the more prone if you don't document changes. Let's say Oracle Support solved a problem you were having in 10.2.0.3 by giving you an underscore parameter. You put it in your initialization parameters and all is good....at least until you move to 10.2.0.5 and find whackiness breaking out all over the DB. If you documented things you will find the date and reason that unserscore parameter was set and comment it out. If not, you may have to do a lot of exploring before you remember that change and track down why it is there and why it has kept you up nights all week.
Doomed Again
Every few years (well every few months, really) another death knell sounds for the relational model. Trouble is, it's based on solid math and just doesn't die off. Other bright attempts, however, like the hierarchical database and its stepchild the object oriented application-implemented approach, seem to haunt us regularly, but they never quite measure up to reality. The relational model, of course, is not implemented as theoretically envisioned, in any commercial RDBMS. But the fundamentals are there. This article at ReadWrite Enterprise has some interesting thoughts (in spite of calling tables 'entities' when a relational table is a relation, thus the name: relational): Is the Relational Database Doomed?

Statistically summarize Oracle Performance data

kurtvm — Tue, 31 Aug 2010 17:13:14 +0200

Here’s the draft of this post http://karlarao.tiddlyspot.com/#dbms_stat_funcs , expounded version coming up!

My First Wedding

kurtvm — Tue, 31 Aug 2010 16:25:00 +0200
One of the reasons the trip to Melbourne was so short was because I had been invited to my first Wedding.

I don't often go to church, so I sat very quietly during the service, avoiding the temptation to answer the Vicar's question about why they shouldn't be married. But at the reception I let it all hang out.
Julie's first priority for photos was for a picture with me. Here she is rushing to wear me at the reception. You will have seen this on the previous guest posting from Paul Jackson who labeled me the 'Vest Man' ; I quite like that.

Julie is actually a customer of Debra's but they have been great friends for a long time and she just love me. Please don't get this Julie mixed up with the other Julie. They are both friends of Debra and I think it is a reflection on Debra's lack of originality that she can't find friends with different names.

Anyway, back to the wedding. If you follow me on facebook, (and if not, why not?), then you will know that I was at a loss as to whether I should wear a hat or not. Debra says only men where hats, but Cary Millsap thought perhaps I should. Julie wanted me to and as it was her wedding I felt I had to. However I was on a trip to the other side of the world so how was I meant to get a hat? Luckily my personal milliner Sue Shaw, remember I went to Amsterdam with her last year, she was also in Melbourne and she and her husband Ian made me a lovely hat for the big occasion.

And finally, here I am with Kara, who is Julie's little girl. I do love my younger fans, they believe in me so much more readily, just like they believe in my other red friend Mr Claus.

Would you believe it - AbbaWorld

kurtvm — Tue, 31 Aug 2010 15:09:00 +0200
What do you do when you only have the morning to sight see in Melbourne? We were looking at options when we first arrived for our very quick 3.5 day visit for Insync10. As ever we were 3 out of 4 with no Dan Norris (Is Australia not interested in Exadata?). Anyway I asked my friend Tom Kyte (doesn't everyone AskTom?), and he, knowing how much of a fan Keeper Mogens is, suggested AbbaWorld which is based in Melbourne.

I didn't mind, on the day after my christening in Denmark, there was a bit of an AbbaFest at Mogens' house and a lot of my friends now have the entire Abba back catalogue on their iTunes, (I won't embarrass them here).
The concept is quite funny, you buy a ticket which has a bar code and at each interactive station you scan this. First you choose your favourite Abba member and I have to say, Debra and Mogens suit the part very well.This is where being a celebrity causes you to come unstuck, I got in for free, as you would expect, but it meant I didn't have a ticket to scan.

As you wonder around the exhibits there are also quiz stations, Debra won these hands down, I think possibly because she read the notices more thoroughly, she certainly isn't an Abba expert. But Mogens found his area of expertise - any of you have a Wii or one of the other dance systems? You know, where the screen tells you were to put your feet and you have a mat that records how well you do? Well Mogens won this hands or should I say feet down. Turns out he had ballroom dancing lessons as a boy, (there goes his reputation).

He loved the old film best, you know how he loves video. Anyway here he is sitting down, and although it is hard to tell he is actually awake!

You also had the chance to pose for photos, and here is me in a helicopter and then just to get in on the act, Debra and Mogens tried to recreate the Abba LP Cover.

And finally you get a chance to appear in a video with Abba themselves, well I couldn't turn that down now, could I? Although as it turns out they were also very excited to appear with me.

Loving Melbourne

kurtvm — Tue, 31 Aug 2010 14:44:00 +0200

Insync10 was a chance to meet up with a lot of friends. We arrived early in the morning and I was able to make new friends with the official welcome party at the airport who turned out to be as quiet as me.

When we got to the hotel our rooms were not ready - did they not know they had a superstar coming? Anyway it turned out OK as Debra seemed to be the subject of the teasing, not me for once, so I was quite happy.

I went whale watching last year in Monterrey with fellow ACE Director Richard Foote and I hope I was lucky for him at the casino night, I worked all my magic for Collaborate in Las Vegas.

And as for new friends here is Connor McDonald who couldn't wait to meet me, Stanley the original ACE Director!

It was a very quick visit, just 3.5 days, and not a lot of time for sightseeing, but keeper Mogens is a HUGE Abba fan and Tom Kyte told us to go to the AbbaWorld experience in Melbourne so we did...........

Ensuring Table With Only One Row in Oracle 11g Using Virtual Column

kurtvm — Tue, 31 Aug 2010 14:41:42 +0200

There was a discussion on the OTN General database forum, in which the OP asked creating a table with just one row and restricting that table to just one row. Here is my attempt at it.

I created a table with two columns, and the second column is a virtual column and contains a constant. I created a unique index on this column. On every insertion, this second column always evaluates to 1, and unique index (which become the function based index on virtual column) ensures that only one row remains in the table.
oracle@test # sqlplus /nolog SQL*Plus: Release 11.1.0.7.0 - Production on Sat Aug 28 19:09:16 2010 Copyright (c) 1982, 2008, Oracle. All rights reserved. idle> conn test/test Connected. test@test> create table t1 (c1 number, c2 generated always as (1) virtual); Table created. test@test> create unique index idx1 on t1(c2); Index created. test@test> insert into t1(c1) values (1); 1 row created. test@test> commit; Commit complete. test@test> insert into t1(c1) values (1); insert into t1(c1) values (1) * ERROR at line 1: ORA-00001: unique constraint (TEST.IDX1) violated test@test> insert into t1(c1) values (2); insert into t1(c1) values (2) * ERROR at line 1: ORA-00001: unique constraint (TEST.IDX1) violated

Oracle-related events in Sydney area (September 2010)

kurtvm — Tue, 31 Aug 2010 14:41:23 +0200

Hello folks,

If it happens and you are in and around the Sydney area 1-2 weeks from now then you might be interested in taking part in the following events:

The first event is organized by Oracle Community in Sydney (sponsored by Pythian), during the next Sydney Oracle Meetup. The good news is that you can have free beer and pizza to recharge your batteries after a working day and have a good discussion with Oracle professionals in friendly environment.
- a – RAC, Grid, Cloud or on the way to Oracle Cloud
Wednesday 8 September 2010 17:30 p.m. to 19:30 p.m.

Usually, there are not many full day Oracle-related events happening in Sydney. More good news is that another one is coming soon. Attend and hear about new products and features from Oracle themselves and win a Car Navigator (as one of the lucky Pythian’s employees did at last Oracle event in Sydney recently :)
- b – Oracle’s Next Generation Data Centre Summit 2010 – Sydney
Thursday 16 September 2010 8:30 a.m. to 6:00 p.m.

See you around,
Yury – a DBA from down under

Oracle ORA600 News Aggregator

Oracle OpenWorld 2010: Sessions By OakTable Members

RAC Investigation on Low-Memory Linux

APEX Meetup @ OOW 2010

EBS, Collaborate, Security, BPEL, OWB, Blog of Note, Hyperion, EPM, Burnout, WiFi

Creating Standby with Active Database Duplication in 11gR2 or also known as Wading through the MOS

Which number takes more space in an Oracle row?

Oracle Security

Is it a bird? Is it a plane? No, it’s Captain Support…

Using the connection_rate parameter to stop DoS attacks

Pushing the Technical Frontier

I Shall Be At Sangam, Will You….

Purging trace and dump files with 11g ADRCI

Mixed case passwords for Oracle

Public appearances 2010

Oracle Versions

Graphical Work Center Utilization – Creating the Demo Data and Active Server Page

Contributions by Angela

Exadata Book

Convert Ext3 to Btrfs

DOAG Conference in Nürnberg

OBIEE 11g Install - Thoughts

Some Blog Errors Are Just Too Serious To Ignore. A Comparison of Intel Xeon 5400 (Harpertown) to Intel Xeon 5500 (Nehalem EP).

What you Don't Know About Me

Scripting, Doom

Statistically summarize Oracle Performance data

My First Wedding

Would you believe it - AbbaWorld

Loving Melbourne

Ensuring Table With Only One Row in Oracle 11g Using Virtual Column

Oracle-related events in Sydney area (September 2010)

RAC Investigation on Low-Memory Linux