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!

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.

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
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

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

ARCH CONNECTED ARCH 0 0 0 0

ARCH CONNECTED ARCH 0 0 0 0

ARCH CONNECTED ARCH 0 0 0 0

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.

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

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

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.

Tweet

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.

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 [...]

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!

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

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 [...]

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/

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.

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 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.

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).

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

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 [...]