OraProfessionals free white papers and Technology news

-- Fundamental Oracle flaw revealed --

2012-01-18T13:05:00.000-08:00

Very interesting InfoWorld article worth to share, please be sure you apply the latest January 2012 CPU patch. You can access the article by clicking here.

- Oracle 11g Automatic Memory Management (AMM) --

2011-03-25T08:03:00.000-07:00

Let's start with a brief introduction to basic concepts.

PROGRAM GLOBAL AREA (PGA): is a region that contains data and control information for a server process, this is non shared memory created by the database when a server process is started and access is exclusive to the particular server process.SH
SHARED_GLOBAL_AREA (SGA) : Is a group of memory structures that contain data and control information for the Oracle instance, it is shared by all background processes.

Oracle has made great progress to simplify memory management through the last versions of the RDBMS. In 9i they introduced PGA management, in 10g they automated the SGA management and in 11g you can manage both (PGA and SGA) with a single configuration using "Automatic Memory Management (AMM)". AMM allows you to allocate a chunk of memory which Oracle uses to manage both SGA and PGA, AMM uses 2 parameters:

MEMORY_TARGET: Amount of memory available to Oracle to use dynamically to control SGA and PGA, this is the amount of memory Oracle will allocate when you start the database to manage PGA and SGA.

MEMORY_MAX_TARGET: Maximum amount of memory that Oracle can use, when no specified the max is equal to MEMORY_TARGET.

When AMM is in use, SGA_TARGET (Introduced in 10g) and PGA_AGGREGATE_TARGET (Introduced in 9i) act as the minimum size setting for their respective areas. It is recommend to set this to 0 when using AMM so Oracle can take full control of it.

-- How to map disk devices to physical devices in Solaris --

2011-03-03T06:32:00.001-08:00

You can map between the two device naming conventions using a script like the one below.

Instance Names: Instance names refer to the nth device in the system (for example, sd20).

Instance names are occasionally reported in driver error messages. You can determine the binding of an instance name to a physical name by looking at dmesg(1M) output, as in the following example.

sd9 at esp2: target 1 lun 1 sd9 is /sbus@1,f8000000/esp@0,800000/sd@1,0

Once the instance name has been assigned to a device, it remains bound to that device.

Instance numbers are encoded in a device's minor number. To keep instance numbers consistent across reboots, the system records them in the /etc/path_to_inst file.

!/usr/bin/env perl

use strict;

my @path_to_inst = qx#cat /etc/path_to_inst#;
map {s/"//g} @path_to_inst;
my ($device, $path, @instances);

for my $line (qx#ls -l /dev/dsk/*s2#) {
    ($device, $path) = (split(/\s+/, $line))[-3, -1];
    $path =~ s#.*/devices(.*):c#$1#;

    @instances =
        map {join("", (split /\s+/)[-1, -2])}
            grep {/$path/} @path_to_inst;
*emphasized text*
    for my $instance (@instances) {
        print "$device $instance\n";
    }
}

-- Oracle TAF server side not properly configured using srvctl due to bug 6886239 --

2011-02-15T07:53:00.000-08:00

Oracle alerted us recently of a non public bug #6886239 "DBMS_SERVICE parameters are not added using srvctl add service. It affects 10g and 11gR1 databases, this is fixed in release 11.2 onwards. In Oracle RAC after setting up TAF although it does not fails and everything looks good, it does not configure correctly.

When you check the service configuration you find no values for failover method, type and retries, those values are needed for TAF to happen.

Creating a service

srvctl add service -d rac -s server_taf -r "rac1,rac2" -P BASIC

Start the service

srvctl start service -d rac -s server_taf

Checking that the services are running.

srvctl config service -d rac

ractest PREF: rac1 rac2 AVAIL:
server_taf PREF: rac1 rac2 AVAIL:

Getting the service ID value

sqlplus /nolog
Connect / as sysdba
SQL> select name,service_id from dba_services where name = 'server_taf';
NAME SERVICE_ID
---------------------------------------------------------------- ----------
server_taf 6

Checking the setup

SQL>col name format a15
col failover_method format a11 heading 'METHOD'
col failover_type format a10 heading 'TYPE'
col failover_retries format 9999999 heading 'RETRIES'
col goal format a10
col clb_goal format a8
col AQ_HA_NOTIFICATIONS format a5 heading 'AQNOT'
SQL>select name, failover_method, failover_type, failover_retries,goal, clb_goal,aq_ha_notifications
from dba_services where service_id = 6
NAME METHOD TYPE RETRIES GOAL CLB_GOAL AQNOT
--------------- ----------- ---------- -------- ---------- -------- -----
server_taf LONG NO

Adding the server side failover parameter to the service

Server side TAF method is BASIC. BASIC is the only value currently supported. This means that a new connection is established at failure time. It is not possible to pre-establish a backup connection. (which is to say, PRECONNECT is not supported)

SQL> execute dbms_service.modify_service (service_name => 'server_taf' -
, aq_ha_notifications => true -
, failover_method => dbms_service.failover_method_basic -
, failover_type => dbms_service.failover_type_select -
, failover_retries => 180 -
, failover_delay => 5 -
, clb_goal => dbms_service.clb_goal_long);
PL/SQL procedure successfully completed.

Check that the service can now see the values for methods, types and retries

SQL>select name, failover_method, failover_type, failover_retries,goal, clb_goal,aq_ha_notifications
from dba_services where service_id = 6
NAME METHOD TYPE RETRIES GOAL CLB_GOAL AQNOT
--------------- ----------- ---------- -------- ---------- -------- -----
server_taf BASIC SELECT 180 NONE LONG YES

Check that the listener has the service registered

lsnrctl services

Create a net service name. Here we have client load balancing between the two nodes.

SERVERTAF =
(DESCRIPTION =
(LOAD_BALANCE = yes)
(ADDRESS = (PROTOCOL = TCP)(HOST = dell01)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = dell02)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = server_taf.za.oracle.com)

You are all set now :-

-- Questions about the Oracle GG Roadmap --

2010-10-19T12:49:00.000-07:00

By Oracle Corp.

1.- Will Oracle fix the recyclebin off issue when capturing DDL? If so, what OGG release and what RDBMS versions are expected to support a functioning recyclebin in the source?

We now support the recyclebin on/true for Oracle 11g along with our DDL replication support, Oracle 10g still requires the recyclebin to be off/false.

2.- Does OGG support active-active in a 3+ node (multi master) configuration? Documentation shows 3 nodes in an active-active configuration, is 3+ supported? If so, any restrictions for DML or DDL for a 3+ active-active configuration?

Our active-active capabilities apply to any number of nodes in the configuration, in other words, we are not limited to only two. Obviously you need to make special considerations for other factors such as the replication of tables that use sequences, replication of truncate or other DDL operations, for example. For best practices, I recommend for multi-node active-active environments, that user privileges/grants are set so that DDL operations can only originate from one node and not the others. Configure OGG to replicat those DDL operations to all downstream nodes in one direction only. Of course that means that other considerations need to plan in event that the node that allows DDL operations goes down or has a planned downtime for maintenance activities.

3.- When will OGG support source tables with Advanced Compression? 10.4.x docs says compressed tables are not supported for extract.

It is on the road-map to support advanced compression. In fact, a release defining feature is to integrate our transaction log reading functionality with the XStreamOut API. This integration will give us support for the variety of database compression features. This is scheduled to be part of the next major release.

4.- Will the SUPPRESSTRIGGERS param available for RDBMS 10.2.0.5 and 11.2.+ for OGG 11.1.x be supported in other RDBMS versions, mainly 10.2.0.4 and all 11G R1 RDBMS versions.

SUPPRESSTRIGGERS parameter support is the ultimate goal, but I am not clear which future release this is targeted to be included. Each database has different functionality so it might be a phased approach depending if it is possible and the development effort required to support it.

-- CRS will not start on one node (Fixed on 11.2.0.2 and Workaround below) --

2010-10-19T08:39:00.001-07:00

: Hdr: 9469133 11.2.0.1 PCW 11.2.0.1 CSS PRODID-5 PORTID-197
Abstract: CORE DUMP OF OCSSD.BIN WHEN VOTING DISK IS NOT ACTIVATED.

Some explanation from development:
==================================
In 11.2, voting files are discovered, not hard-wired, so we look through a
list of files that are specified in the 'discovery string', e.g.
/dev/vdisk/*, and use all files that appear to be legitimate voting files,
i.e. they have a TOC (Table Of Contents), volume info block, etc. Since the
VG with the voting files is not online, the discovery does not see them at
all, so does not consider them as voting files and fails as a result of an
inability to find enough voting files.

=====================================

The workaround is to wait the disks are completely ONLINE after machine boot before cluster is started
(note 459169.1 CRS Does Not Startup Automatically After Node Reboot,
Manual Start is OK - but this node is up to 11.1 version)

or apply patch, backport is available for Solaris x86-64(64 bit) - please confirm the Operating System

please upload the patch inventory - I may need to raise backport for you

We're getting these errors on the other node when rebooting:

I did see in the boot messages on both hosts these items:
Johnston, Nathaniel [10:39 AM]:
Sep 24 16:05:15 mhddb-nb-2p.philadelphia.pa.bo.comcast.net root: Oracle HA daemon is enabled for autostart.
Johnston, Nathaniel [10:39 AM]:
Sep 24 16:05:18 mhddb-nb-2p.philadelphia.pa.bo.comcast.net unix: vn_rdwr failed with error 0x15
Sep 24 16:05:18 mhddb-nb-2p.philadelphia.pa.bo.comcast.net unix: kobj_load_module: read header failed
Johnston, Nathaniel [10:39 AM]:
Sep 24 16:05:19 mhddb-nb-2p.philadelphia.pa.bo.comcast.net root: exec /u01/app/grid/perl/bin/perl -I/u01/app/grid/perl/lib /u01/app/grid/bin/crswrapexece.pl /u01/app/grid/crs/install/s_crsconfig_mhddb-nb-2p_env.txt /u01/app/grid/bin/ohasd.bin "reboot"
Sep 24 16:05:19 mhddb-nb-2p.philadelphia.pa.bo.comcast.net root: exec /u01/app/grid/perl/bin/perl -I/u01/app/grid/perl/lib /u01/app/grid/bin/crswrap
Johnston, Nathaniel [10:40 AM]:
Sep 24 16:05:36 mhddb-nb-2p.philadelphia.pa.bo.comcast.net mDNSResponder (Engineering Build) (Nov 2 2009 05:02:07) [5272]: starting
Sep 24 16:05:37 mhddb-nb-2p.philadelphia.pa.bo.comcast.net mDNSResponder: Oracle mDNSResponder starting

======================================
WORKAROUND
======================================

11gR2 CRS doesn't startup after node reboot [ID 1050164.1]

		Modified 31-JAN-2010 Type PROBLEM Status PUBLISHED

In this Document
  Symptoms
  Changes
  Cause
  Solution

Applies to:

Oracle Server - Enterprise Edition - Version: 11.2.0.1.0 to 11.2.0.1.0 - Release: 11.2 to 11.2
Generic Linux

Symptoms

Installation of the 11gR2 Grid Infrastructure on a Linux cluster completed successfully
OCR & Voting files located in ASM diskgroup
using ASMLIB driver
ASM disks are located on multipath devices (/dev/mapper/)
following a node reboot CRS does not startup
CSS daemon log shows the following message:
2010-01-13 09:04:15.075: [ CSSD][1150449984]clssnmvDDiscThread: using discovery string for initial discovery 2010-01-13 09:04:15.075: [ SKGFD][1150449984]Discovery with str:: 2010-01-13 09:04:15.075: [ SKGFD][1150449984]UFS discovery with :: 2010-01-13 09:04:15.075: [ SKGFD][1150449984]OSS discovery with :: 2010-01-13 09:04:15.076: [ SKGFD][1150449984]Discovery with asmlib :ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so: str :: 2010-01-13 09:04:15.076: [ SKGFD][1150449984]Fetching asmlib disk :ORCL:DATA1: 2010-01-13 09:04:15.076: [ SKGFD][1150449984]Fetching asmlib disk :ORCL:DATA2: 2010-01-13 09:04:15.076: [ SKGFD][1150449984]Fetching asmlib disk :ORCL:DATA3: 2010-01-13 09:04:15.076: [ SKGFD][1150449984]Fetching asmlib disk :ORCL:DATA4: 2010-01-13 09:04:15.077: [ SKGFD][1150449984]ERROR: -15(asmlib ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so op asm_open error Operation not permitted) 2010-01-13 09:04:15.077: [ SKGFD][1150449984]ERROR: -15(asmlib ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so op asm_open error Operation not permitted) 2010-01-13 09:04:15.077: [ SKGFD][1150449984]ERROR: -15(asmlib ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so op asm_open error Operation not permitted) 2010-01-13 09:04:15.077: [ SKGFD][1150449984]ERROR: -15(asmlib ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so op asm_open error Operation not permitted) 2010-01-13 09:04:15.077: [ CSSD][1150449984]clssnmvDiskVerify: Successful discovery of 0 disks 2010-01-13 09:04:15.077: [ CSSD][1150449984]clssnmCompleteInitVFDiscovery: Completing initial voting file discovery 2010-01-13 09:04:15.077: [ CSSD][1150449984]clssnmvFindInitialConfigs: No voting files found 2010-01-13 09:04:15.077: [ CSSD][1150449984]################################### 2010-01-13 09:04:15.077: [ CSSD][1150449984]clssscExit: CSSD signal 11 in thread clssnmvDDiscThread 2010-01-13 09:04:15.077: [ CSSD][1150449984]################################### 2010-01-13 09:04:15.077: [ CSSD][1139960128]clssgmClientShutdown: total iocapables 0 2010-01-13 09:04:15.077: [ CSSD][1139960128]clssgmClientShutdown: graceful shutdown completed. 2010-01-13 09:04:15.077: [ CSSD][1150449984]
running the cluster verification utility returns the following messages:
/cluvfy stage -post crsinst -n racnode1 Performing post-checks for cluster services setup Checking node reachability... Node reachability check passed from node "racnode1" Checking user equivalence... User equivalence check passed for user "grid" Checking time zone consistency... Time zone consistency check passed. ERROR: Cluster manager integrity check failed PRVF-5434 : Cannot identify the current CRS software version UDev attributes check for OCR locations started... UDev attributes check passed for OCR locations UDev attributes check for Voting Disk locations started... ERROR: PRVF-5197 : Failed to retrieve voting disk locations UDev attributes check failed for Voting Disk locations Default user file creation mask check passed Checking cluster integrity... Cluster integrity check failed This check did not run on the following node(s): racnode1 Checking OCR integrity... Checking the absence of a non-clustered configuration... All nodes free of non-clustered, local-only configurations ERROR: PRVF-5300 : Failed to retrieve active version for CRS on this node OCR integrity check failed Checking CRS integrity... ERROR: PRVF-5300 : Failed to retrieve active version for CRS on this node CRS integrity check failed OCR detected on ASM. Running ACFS Integrity checks... Starting check to see if ASM is running on all cluster nodes... PRVF-5137 : Failure while checking ASM status on node "racnode1" Starting Disk Groups check to see if at least one Disk Group configured... PRVF-5112 : An Exception occurred while checking for Disk Groups PRVF-5114 : Disk Group check failed. No Disk Groups configured Task ACFS Integrity check failed Checking Oracle Cluster Voting Disk configuration... ERROR: PRVF-5434 : Cannot identify the current CRS software version PRVF-5431 : Oracle Cluster Voting Disk configuration check failed User "grid" is not part of "root" group. Check passed Post-check for cluster services setup was unsuccessful on all the nodes.

Changes

Node was rebooted after install.

Cause

The CSS daemon crashes because it cannot locate any Voting files in any of the discovered ASM disks, which is indicated by the following message in the CSS daemon log (/log//cssd/ocssd.log):

2010-01-13 09:04:15.077: [ CSSD][1150449984]clssnmvFindInitialConfigs: No voting files found

This error is preceded by the following ASMLIB error:

2010-01-13   09:04:15.077: [ SKGFD][1150449984]ERROR: -15(asmlib   ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so op asm_open error   Operation not permitted)

suggesting that ASMLIB has problem accessing the ASM disk.

Solution

1. either edit the file /etc/sysconfig/oracleasm-_dev_oracleasm and change the lines:

ORACLEASM_SCANORDER=""
ORACLEASM_SCANEXCLUDE=""

ORACLEASM_SCANORDER="dm"
ORACLEASM_SCANEXCLUDE="sd"

or alternatively run the following command (as user root)

/usr/sbin/oracleasm configure -i -e -u user -g group -o "dm" -x "sd"

2. stop & restart ASMLIB as user root using:

/usr/sbin/oracleasm exit
/usr/sbin/oracleasm init

3. restart CRS or reboot node

The above steps need to be executed on all nodes

-- ASM unresponsive on 1 cluster node of 4 (Fixed on 11.2.0.2) --

2010-10-19T08:37:00.001-07:00

By Rafael Orta

=======================================================
Bug 9276348 BugDB see Bug 9276348.-P Base Bug 9254569
Related Bugs : BugMatrix 9276348 DDR: BugDesc 9276348
Customer: INTERNAL - RACSYSTST Created: 11-JAN-10
Component: PCW Comp Ver: 11.2.0.2 Rel St: D Updated: 03-FEB-10
Sub Comp: RDBMS Ver: 11.2.0.2 By: HFLAKE
Status: 96,Closed, Duplicate Bug
Severity: 2,Severe Loss of Service Fixed In Ver:
O/S: 226 Linux x86-64
PL Group: Gen/Port: G Error #: - Pub: N
Hdr: 9276348 11.2.0.2 PCW 11.2.0.2 PRODID-5 PORTID-226 9254569
Abstract: LX64: CRS STACK FAILS TO COME UP ON FIRST NODE, CSS STARTS OK
=======================================================

3) Bug 9276348 is closed as duplicated of Bug 9254569 :
=======================================================
Bug 9254569 BugDB see Bug 9254569.-P No Base Bug
Related Bugs : BugMatrix 9254569 DDR: BugDesc 9254569
Customer: INTERNAL-RACSYSTST Created: 04-JAN-10
Component: PCW Comp Ver: 11.2 Rel St: D Updated: 13-SEP-10
Sub Comp: CRS RDBMS Ver: 11.2 By: ARU
Status: 80,Development to Q/A
Severity: 2,Severe Loss of Service Fixed In Ver: 11.2.
0.2
O/S: 233 Microsoft Windows Server 2003 (64-bit
PL Group: Gen/Port: G Error #: - Pub: N

Hdr: 9254569 11.2 PCW 11.2 CRS PRODID-5 PORTID-233
Abstract: MISSING DEPENDENCY IN OHASD: GIPC HA REQUIRES GIPCD
=======================================================

4) Bug 9254569 is fixed on release 11.2.0.2.

ACTION PLAN
===========

5) In order to fix the bug 9254569 please apply the patchset 11.2.0.2 on the Grid Infrastructure Oracle Home. Patchset 11.2.0.2 also contains another very important fixes for the Grid Infrastructure CRS.

6) For additional information please check the next note:

=)> Important Changes to Oracle Database Patch Sets Starting With 11.2.0.2 (Doc ID 1189783.1)

-- Backup Issue encountered after DB failover --

2010-10-19T08:32:00.001-07:00

By Raja Ankathi

We encountered the backup issue on db. As per the metalink note 566635.1, there is an bug in 10.2. and it is fixed in 11.2

==================

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.2.0 This problem can occur on any platform.

Symptoms

Resync of the catalog for the Primary database fails with:

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

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

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

RMAN-03009: failure of resync command on default channel at 05/06/2008

10:29:09

ORA-00001: unique constraint (RMAN.TF_P) violated Cause You are hitting bug 6653570

The root cause scenario in the bug is:

1) a primary and standby is created

2) on primary customer added a tempfile. This doesn't get created on standby.

It gets resynced to recovery catalog

3) Then when switchover happens, they add the tempfile on new primary and

resync it to recovery catalog. This resync marks the tempfile on primary (at

step 2) as dropped.

4) Then when the switchover happens again, the temp file that was dropped is

again resynced which is causing the problem.

Solution

Bug 6653570 is fixed in Oracle Database version 11.2

Please check metalink if the patch is available for your platform and version. If not and a one off is required, please contact Oracle support

The workaround for this issue is to recreate the temporary tablespace on the primary database.

-- 11.2.0.2 upgrade error --

2010-10-19T08:31:00.001-07:00

By Gary Oprendek

When upgrading grid 11.2.0.1.0 to 11.2.0.2.0, you must make sure that patch 9413827 is installed on the 11.2.0.1.0 grid and database binaries PRIOR to starting the upgrade. The upgrade will continue without it until you get to the end when you should run root.sh and it will bomb out stating that that patch is not installed. In order to install it you will have to shut down the grid and asm home and apply the patch. This will of course mean that you have to kill the upgrade process. This causes a problem because after you apply the patch and restart the upgrade process, the installer states that there is no grid or asm installation on the machine. This is because it has wiped out a parameter in the oracle inventory for the grid installation. It removes the “ CRS=”true” “ parameter from the end of the line listing the grid home in the ~oraInventory/ContentsXML/inventory.xml file. The entry should look similar to this:

CRS="true">

If you run into this problem then manually edit the file and add the parameter back in and the installer will find the cluster and continue normally.

- Changing Private IP Addresses 11G R2 OCR / VD on ASM --

2010-10-13T12:58:00.000-07:00

By Bill Toranto

I recently encountered this issue and thought everyone should be aware of it.

If any re IP-ing is to take place in your 11G R2 RAC clusters (mainly the Private IP), the Grid / CRS OCR needs to be updated prior to anything being done on the host. The ASM instance needs to be available in order for the OCR to be accessible.

IF the host is re IP-ed first, the Grid will need to be re installed using the new private IP address, since ASM will not mount & the OCR inaccessible.

Oracle does expect to have a fix for this very soon since they are getting a large number of reported issues related to this, and ASM OCR’s a relatively new implementation.

This may seem fundamental, but putting the OCR on ASM is a fairly new practice for most of us and previously could be modified after the host ip’s were modified since we used RAW devices.

-- Oracle Debuts Unbreakable Enterprise Kernel for Oracle Linux --

2010-10-11T09:09:00.000-07:00

I don’t know what is in Larry’s mind, but seen this been announced all around makes me think Oracle is reaffirming their commitment to this Linux based platform, does it makes sense for them to keep the expensive Solaris platform alive or will there be a merge path down the road?, this is just something to think about to position ourselves properly in the future.

"Oracle Debuts Unbreakable Enterprise Kernel for Oracle Linux


	Oracle has announced the availability of the Unbreakable Enterprise Kernel for Oracle Linux, a fast, modern, reliable kernel that is optimized for Oracle software and hardware. "Today's hardware innovations are fast and frequent, making it very important that the Linux distributions evolve quickly to leverage the latest hardware," said Wim Coekaerts, senior vice president of Linux and Virtualization Engineering at Oracle. The new Unbreakable Enterprise Kernel is the result of the combined efforts of Oracle's Linux, database, middleware, and hardware engineering teams, and is: Fast—More than 75 percent performance gain demonstrated in OLTP performance tests over a Red Hat Compatible Kernel; 200 percent speedup of Infiniband messaging; 137 percent faster solid state disk access Modern—Provides optimizations for large NUMA servers; improved power management and energy efficiency; fine-grained CPU and memory resource control Reliable—Supports the Data Integrity Extensions and T10 Protection Information Model, to stop corrupt data from being written to storage; hardware fault management improves application uptime; low overhead performance counters for tracing Optimized for Oracle—Built and tested to run Oracle hardware, databases, and middleware with the best Linux performance and reliability available "With the combination of Oracle Linux and the Unbreakable Enterprise Kernel, customers gain fast access to optimizations, new enhancements, and bug fixes," says Coekaerts. "This new offering is a result of Oracle Linux kernel development efforts, on top of the current mainline kernel, and further distinguishes Oracle Linux as the best choice for enterprise customers.""

-- Real Application Testing for Earlier Releases --

2010-10-06T09:58:00.000-07:00

Metalink Note: 560977.1: Real Application Testing for Earlier Releases

What is being announced?

Oracle Database 11g introduced Database Replay and SQL Performance Analyzer as part of the Real Application Testing option to enable businesses identify issues with system changes before production deployment. This note provides information on the Real Application Testing functionality available
for pre-11g database releases. The main goal of making Real Application Testing functionality available in earlier releases is to enable customers to upgrade to higher database releases.

SQL Performance Analyzer allows users to identify SQL performance regressions caused by system changes such as adding an index, gathering optimizer statistics, implementing partitioning, RDBMS upgrades, etc. SQLPerformance Analyzer assesses the impact of change on SQL response times by executing each SQL serially with production context (bind variables, optimizer environment, etc.) before and after a change and then provides a report highlighting any performance divergences.

Database Replay allows users to perform comprehensive testing of database and infrastructure changes using real application workloads. It allows capture of production workload including concurrency, think time, and transactionsdependencies, and allows users to replay the workload on a test system with the exact same production characteristics so that all problems can be identified and remediate in test before deploying the change to production.

Please check the “Licensing Information” manual on OTN for details regarding licensing of Real Application Testing Option.

What do you need to do?

What you need to do to use Real Application Testing functionality in earlier releases?

Real Application Testing functionality for pre-11g database releases is installed using the “opatch” utility and following instructions for the generic and platform specific “README” for the patches. If you want to only use Database Replay or SQL Performance Analyzer, then only the patch for that particular functionality
needs to be applied. If you want to use both Database Replay and SQL Performance Analyzer, the patches for both the functionalities need to be applied.

Database Replay

To help customers upgrade to Oracle Database 11g, the “capture” functionality of Database Replay has been made available in previous releases as shown in thetable below. The replay of the captured workload can only be done on Oracle Database 11g and higher.

Please refer to Oracle documentation on OTN for information on the capture part of Database Replay available for the earlier releases.

Enabling Workload Capture

This step is required for 10.2.0.4.0 only. By default, the workload capture is disabled on the pre-11g database releases. Database workload capture functionality is enabled on the system by specifying
the PRE_11G_ENABLE_CAPTURE initialization parameter. To enable workload capture, run the wrrenbl.sql script at the SQL prompt as sys or system: This step is NOT required for version lower then 10.2.0.4.0.

@$ORACLE_HOME/rdbms/admin/wrrenbl.sql

The wrrenbl.sql script calls the ALTER SYSTEM SQL statement to set the PRE_11G_ENABLE_CAPTURE initialization parameter to TRUE. Please check the OTN documentation mentioned above for more details.

SQL Performance Analyzer

SQL Performance Analyzer functionality has been enhanced and made available in earlier releases to help customers upgrade their databases from versions Oracle 9.x, 10.1.x, and 10.2.x to higher release. Applying the one-off patches as shown below actually installs the SQL Performance Analyzer functionality for that
release. After the functionality is installed, customers can then leverage Oracle Database 11g SQL Performance Analyzer functionality to upgrade from any database releases 9.x, 10.1.x, 10.2.x to 10.2 or higher release. The one-off patches that need to be applied depend on the version of the source and the
destination databases as given in the table below.

Further information on this topic is available in the technical white paper “Oracle Real Application Testing: Testing the SQL Performance Impact of Oracle 9i/10g Release 1 to Oracle Database 10g Release 2 upgrade with SQL Performance Analyzer”. It is recommended to review this document before using SQL Performance Analyzer functionality for earlier releases.

Patch 6865809 should be applied on the Oracle Database Release 11.1.0.6 test database that is used to orchestrate SPA on Oracle Database 10.2. The patch enables Oracle 11g test database to consume SQL trace files from previous database releases and generate a SQL Tuning Set (STS). The STS can then be
used as input for the SQL Performance Analyzer task.

Patches applied on Oracle Database 10.2 source or destination databases enable “Remote SQL Test Execution” functionality on that release for more accurate analysis of performance data.

Table 3: SQL Performance Analyzer Availability Information. For Windows
platform, see additional notes below:

Note for Windows Platforms:

* For Windows 32-bit Platform patch number on top of 11.1.0.6.0 database is 7044721.
* For Windows 64-bit AMD64 patch number on top of 11.1.0.6.0 database is 7044728.

Note for 11.1.0.7.0 Database

If you're using 11.1.0.7.0 database for SPA testing, you do not need to apply any of the above-mentioned patches on the 11g SPA system that is used for analysis. All the fixes are already in there. On 10.X database side, you do need the patch as mentioned above.

Who to contact for more information?

Please create a Service Request with Oracle Support

References

Note 466181.1 - 10g Upgrade Companion
Note 562899.1 - TESTING SQL PERFORMANCE IMPACT OF AN ORACLE 9i
TO ORACLE DATABASE 10g RELEASE 2 UPGRADE WITH SQL
PERFORMANCE ANALYZER

Note 601807.1 - Oracle 11g Upgrade Companion

Keywords

SQL~PERFORMANCE~ANALYZER ; UPGRADE ; SQL~TUNING ; UPGRADE~
TO~10.2.0.3.0 ; MIGRATE~DATABASE ; PATCH~UPGRADE~TO10.sp;SQL~T
UNING~ADVISOR ; TUNING~PACK ; UPGRADE~FROM~9.2.0 ; UPGRADE~T
O~9.2.0 ;

-- Failed login attempts updates the account status column as LOCKED in 11.2.0.1 --

2010-10-06T09:11:00.000-07:00

There is a known bug under 11.2.0.1 that displays the incorrect account status when a user has accidently locked an account via too many invalid passwords attempts. In a nutshell, too many password attempts should result in a “LOCKED(TIMED)” status, but instead result in a “LOCKED” status. The “LOCKED” status is what an account is set to when a DBA intentionally locks it (i.e. when somebody leaves the company or team).

There is a work around which would require updating all profiles to ensure password_lock_time is set to a number, rather than unlimited. Alternatively we can apply the 11.2.0.2 patchset.

-- Warning while upgrading to 11g RAC --

2010-07-21T12:00:00.000-07:00

Recently while upgrading to 11gR2 one of our RAC databases it failed while running ASMCA which is now part of grid infrastructure, we found that we were hitting the Bug: 9818657 (11.2 UPGRADE FROM 10.2 FAILED WHEN RUNNING ASMCA), the issue was that we had set in the bash_profile the ORA_CRS_HOME environment variable pointing to the old 10g environment, we removed the parameter from the bash_profile and that solved the issue.

-- How to manage AUDIT trails automatically in 11g before it grows out-of-control overtime --

2010-06-23T07:28:00.000-07:00

By Ken Patel

11.2 has AUDIT trail turned on for lot of SYSTEM privileges. But there is no automatic purge job defined out-of-box, not only that SYS.AUD$ is located in SYSTEM tablespace which can cause space management problem unnecessarily.

11.2 has new package DBMS_AUDIT_MGMT to manage this. one of good thing of this package, is that it allows DBA to setup automatic purge job (not to say, Oracle should have done this out-of-box with 7 days or some good interval just like AWR snapshots). this package also helps moving SYS.AUD$ to SYSAUX or any other tablespace DBA wants.

I think, DBA should manage this as soon as 11.2 Database is setup. Purging takes time if done after few weeks. I would do atleast this much after deploying 11.2 database for controlling audit trails ( Documentation has more details on various other package calls as well as different argument and their significance).

-- this block initializes the clean up as well as moves SYS.AUD$ to SYSAUX tablespace

BEGIN

DBMS_AUDIT_MGMT.INIT_CLEANUP(AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,

DEFAULT_CLEANUP_INTERVAL => 12 );

END;

-- manual clean up

BEGIN

DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(

AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,

USE_LAST_ARCH_TIMESTAMP => TRUE );

END;

-- creating automatic purge job

BEGIN

DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (

AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,

AUDIT_TRAIL_PURGE_INTERVAL => 12,

AUDIT_TRAIL_PURGE_NAME => 'Sys_Audit_Purge_JOB',

USE_LAST_ARCH_TIMESTAMP => TRUE );

END;

Ken is a Senior Database Engineer at Comcast NETO DBE&O Solutions Engineering Team.

-- Sometimes you have no option but to use Multithreaded Servers --

2010-05-26T07:01:00.000-07:00

A customer of mine was faced with the situation of having poor memory management even that ASMM was configured, I noticed poor buffer cache utilization 46%, low hit ratio 84% and the sqls were constantly reloading, when faced with the question of why we do not pin objects in the buffers the answer was that their Windows System is 32 bits and was maxed out due to the number of connections to the 3GB limit.

Although we all know that dedicated servers are faster that shared servers, we also know that the most expensive operation in a transaction is I/O, in the case of my customer I know his service time is about 20ms per I/O which is not the best (They use EMC Clarion). So in this case we need to sacrifice a bit of CPU to reduce I/O and do more cache utilization.

The most important factor when configuring MTS is to determinate the number of servers and dispatchers that you will need and this is a good rule of thumb.

For dispatchers of 1 per every 250 connections works well for typical systems, Being too aggressive in your estimates is not beneficial; configuring too many dispatchers can degrade performance.

For shared servers typical systems seem to stabilize at a ratio of 1 shared server for every 10 connections. On NT, exercise care when setting MTS_MAX_SERVERS to too a high value because as mentioned, each server is a thread in a common process. The optimal values for these settings can change based on your configuration; these are just estimates of what seems to work for typical configurations.

Example OLTP Application: If you expect to require 2,000 concurrent connections, begin with 200 shared servers or 1 shared server for every 10 connections. Set MTS_MAX_SERVERS to 400.

Because MTS uses your large_pool for storing MTS related UGA information , we recommend to increase and explicitly set the minimum large_pool_size. If you do not set a value for LARGE_POOL_SIZE, Oracle uses the shared pool for MTS user session memory.

Some important views that will help you to tune your servers / dispatchers ratio:

- V$DISPATCHER This view gives information about dispatcher processes.

- V_$SHARED_SERVER This view gives information about shared server processes.

- V$CIRCUITS This view gives information about the virtual circuits. Virtual circuits are state objects that act as repositories of all the user related state information that needs to be accessed during a database session. There is one virtual circuit per client connection.

- V$QUEUE This view gives information about messages in the common message queue and the dispatcher message queues.

- V$DISPATCHER_RATE This view gives information and statistics about the rate at which each dispatcher is receiving and handling messages, events, and so on.

The following initialization parameters control shared server operation:

SHARED_SERVERS: Specifies the initial number of shared servers to start and the minimum number of shared servers to keep. This is the only required parameter for using shared servers.
MAX_SHARED_SERVERS: Specifies the maximum number of shared servers that can run simultaneously.
SHARED_SERVER_SESSIONS: Specifies the total number of shared server user sessions that can run simultaneously. Setting this parameter enables you to reserve user sessions for dedicated servers.
DISPATCHERS: Configures dispatcher processes in the shared server architecture.
MAX_DISPATCHERS: Specifies the maximum number of dispatcher processes that can run simultaneously. This parameter can be ignored for now. It will only be useful in a future release when the number of dispatchers is auto-tuned according to the number of concurrent connections.
CIRCUITS: Specifies the total number of virtual circuits that are available for inbound and outbound network sessions.

=======================================================

One of my readers posted the followintg comment and I decided to share it, thanks Mitra for the tip

" I used to administer quite a few 32 bit oracle boxes on windows and faced the same problem. Its annoying but the solution I found is to resuce the stack size of the "oracle.exe" and the "listener.exe" with the orastack utility. I believe that the default is 1 MB and for oltp applications 128KB works great. Please try this next time and I bet it will get some extra millage for you"

Yes, reducing the Oracle stack size is definitively an option, reducing it too much will produce the error “ORA-03113: end-of-file on communication channel” errors. Oracle supplies the ORASTACK utility to allow customers to modify the default stack size of a thread / session when created in the Oracle executable.

When ORASTACK is run against specific executables it alters the part of the binary header that defines the default stack size used by the create thread API.

Below are examples of setting the stack to 500K for the main executables :

orastack oracle.exe 500000
orastack tnslsnr.exe 500000

orastack svrmgrl.exe 500000

orastack sqlplus.exe 500000

-- Oracle Opeworld Keynotes --

2009-10-14T16:49:00.000-07:00

Hello Everybody

I have been this week at the Oracle Openworld, I will be posting some of the technical information, 11.2 highlights and pictures I got soon. Today is the day where we expect the annual speech from Larry Ellison with great products and news of whatever is to come.

To check what is going on here on daily basis click Here

The Keynote was started by S. Gopalakrishnan from Infosys, he was talking about the game changing trends of IT leads to innovation.

Simplicity on Organizational Complexity.
Value Webs (Creating better customer insight, Innovation through Co-creation,
Smarter Organization (Better learning through collaboration and personalization)

Were some of his most important highlights.

Larry starting talking about how the Linux business is important for Oracle, they have now more than 4,000 customers running OEL or Oracle VM.

He spend some time talking about the "Database Machine" (Exadata 2), It is a Sun Sparc server
- World fastest machine for OLTP, it contains 5TB, and yes that is TB of flash memory data. More than 400TB for DRAM, yes it is impressive, never the less very costly too.

As per Ellison he said that it is 4 times fastest than the faster IBM server for the same cost, been more green and taking a lot less space and energy consumption. Exadata offers columnar D/W compression which in query mode archives 10x compression, in Archive mode it achieves 15x compression. You can add capacity as needed to the database machine, they offer a massive parallel grid and there is not single point of failure. The database machine comes pre-configured, it is ready to use on day one, it eliminates months of configuration troubleshooting and tuning, as per Ellison you do not have to do any changes to it.

The database machine comes on different sizes and prices stating with the simple configuration at a value of $110,000 which is not fault tolerant.

As per Ellison the database machine offers the fastest OLTP and DW performance, offers best cost performance, is fault tolerant and scalable on demand.

The Governor of California Arnold did a surprised entrance in the middle of Larry's speech and spoke about the importance of technology and how technology has helped him on his career and the importance for the future of California and humanity in general. He mentioned that California is 40% more energy efficient than any other state in the US.

Larry next topic was about proactive product problem detection, so Oracle can notify you of potential problems in your installation and patches that they recommend for you to apply.

There was as well a presentations of Oracle Enterprise Manager 11g and some of the new features. To close the keynotes Larry spoke about Oracle Fusion coexisting strategy, he plead to the current customers to move to this new platform so they can make use of the new technology, however he was clear saying that the customers has the choice to not move and they will continue to get supported.

Oracle 11g upgrading tips

2009-09-02T08:16:00.000-07:00

The following is a compilation of interesting tips I found when upgrading to 11g.

Things you may not know:

- SQL Developer is installed by default when you use one of the templates from DBCA.
- Oracle XML DB will be installed as well when the DB gets created.
- Oracle Data mining creation schema is now part of catproc.sql
- Your can upgrade directly to 11g using DBUA from 9.2.0.4 or higher, 10.1.0.2 or higher or
10.2.0.1 or higher.
- If your optimizer statistics are stale, then DBUA will recommend for you to exit and collect
statistics before you continue.
- If you did not applied the time zone patches to your 9i or 10g database then DBUA will fail to
upgrade the database until you apply the required patches.
- A new parameter called "diagnostic_dest" determinate where your log files will be created, this
parameter replaces background_dump_dest, user_dump_dest and core_dump_dest.
- DBUA will give you the option to move files from file system to file system, from file system to
ASM and from ASM to file system.
- DBUA will also give you the option to recompile invalid objects after the upgrade.
- If you need to restore and you let DBUA do the backup for you, your restore files are located
on $ORACLE_BASE/admin//backup.

Personal and optional Advice

- Although it is possible to install and upgrade your database to 11g at the same time, please abstain from it as you may face problems during the install.

- Be sure you have a good install before trying to perform the upgrade, I advice as well to create a small new database, just to be sure the software is working properly and you have all the parameters in place.

- If you have never tested your backups before, now would be a good idea to do it. Also if you are like me that do not trust the tape library, then try the restore in a different server and document your restore process. If you need to restore you will have a lot of eyes in your shoulders, and if you start checking the manual for restore you are not going to transmit a lot of confidence to them.

- DBUA will present you to do the backup of your database for you, however I recommend you do your own.

RMAN> shutdown immediate
RMAN> startup restrict
RMAN> backup database plus archivelog format '' tag pre-upgrade;
RMAN> shutdown immediate
RMAN> startup

If you need to restore

startup nomount
run {
restore controlfile from '';
alter database mount;
restore database from tag pre-upgrade
recover database noredo;
alter database open resetlogs;
}

After the restore is complete, you need to point your environment variables to the old Oracle sofware location.

- Do another backup after you successfully upgrade.

- Check your DB alert.log after the upgrade for any errors.

- Check the DBA_REGISTRY view to make sure all the components reflect the new version.

- The issues I frequently find while upgrading are tablespace space and memory, be sure you have
enough of both. After you upgrade you can bet your footprint will take more memory and many times
more disk.

- before you upgrade do dump the control file to trace:

SQL> alter database backup controlfile to trace;

These are a must

- Backup your binaries and database before trying to upgrade.
- Check the release notes, most of the time they tell you what errors can you expect.
- Read the upgrade manual.
- DBUA logs are by default located at $ORACLE_HOME/cfgtoolslogs/dbua/db_name, tail the
log files during the upgrade and check them again after the upgrade.

UpgradeResults.htm = Summary of what DBUA will upgrade
Post_Upgrade.log = Log file for post upgrade work.
Oracle_Server.log = Detail of the migration execution
Trace.log = Detail tracing information of the upgrade process.

- Check that your redo log file size are bigger than 4MB. The upgrade will fail if they are not.

- Run the pre-upgrade information tool $ORACLE_HOME/rdbms/admin/utlu111i.sql this will
will tell you if you need to change anything to go to 11g.

- After you are done with the upgrade and post upgrade activities check for invalid objects and compile, you can use the view dba_invalid_objects for this.

- Collect statistics for your tables.

New 11g Parameters

- asm_preferred_read_failure_groups
- client_result_cache_lg
- client_result_cache_size
- commit_logging
- commit_wait
- control_management_pack_access
- db_lost_write_protect
- db_secure_file
- db_ultra_safe
- ddl_lock_timeout
- diagnostic_dest
- global_txn_process
- java_jit_enabled
- ldap_directory_sysauth
- memory_max_target
- memory_target
- optimizer_capture_sql_plan_baselines
- optimizer_use_invisible_indexes
- optimizer_use_pending_statistics
- optimizer_use_sql_plan_baselines
- parallel_io_cap_enabled
- plscope_setting
- redo_transport_user
- resource_manager_cpu_allocation
- result_cache_max_result
- result_cache_max_size
- result_cache_mode
- result_cache_remote_expiration
- sec_case-sensitive_logon
- sec_max_failed_login_sttempts
- sec_protocol_error_further_action
- sec_protocol_error_trace_action
- sec_return_server_release_banner
- xml_db_events

Deprecated 11g parameters

- background_dump_dest
- core_dump_dest
- user_dump_dest
- commit_write
- commit_wait
- instance_groups
- log_archive_local_first
- plsql_debug
- plsql_v2_compatibility
- remote_os_authent
- standby_archive_dest
- transaction_lag

Obsolete parameters in 11g

- ddl_wait_for_locks
- logmnr_max_persistent_sessions
- plsql_compiler_flags

Oracle11g Best Practices with Thomas Kyte

2009-08-13T20:16:00.001-07:00

Today I had the opportunity of assisting to this presentation at Bridgewater NJ with Thomas Kyte, as usual Tom's presentation was excellent, he always transmit that sense that he knows what we go through day to day and you actually feel identified with him , he has that something that you only get with many years of been in the field and experienced that, that something that you don't get just reading books, keeps you attention and make you have a good time. I wanted to share with you some of the points he covered and I consider very important. As a matter of fact I will look to implement some of this technologies / features in the next few months and offer them as part of the portfolio of services we have at OraProfessionals.

- Tom suggested the use of Physical DG in 11g for Testing, in 11gR2 you can use your physical DG for doing destructive test, after you are done you Flashback the DB to the point before the test and continue business as usual. I think this is great as I have plenty of DG databases, however I struggle with the developers to get time on the test database.

- Tom suggested as well the use of logical standby as a reporting solution, and I ask myself why no more people is using this technology. Nowadays probably the most common solution for a reporting server is Oracle Streams, however we know how fragile and heavy to maintain it can be. We rarely have any problems with Oracle DG, however I can't say the same for streams.

- 11gR2 will allow you to re-route in an automatic way your read only queries to a standby database. This will make the developers happy as they won't need to change their application code. Apparently this is configurable so if your standby is behind more than so many minutes, Oracle will resolve the query on the primary database.

- 11gR2 has a feature when using Real Application Testing (RAT) that allows you to use the sql performance analyzer (dbms_sqlpa) to compare the performance of your regressed SQLs with the new ones (assuming you are testing some database changes). RAT is one of the technologies that we plan to explore in the next few months. I want to clarify that RAT is not the product that will allow you to test some application functionality, this product will just tell you how your database will behave with similar workload. RAT requires for your objects in the source and the target to be identical.

- 11gR2 also has a Data Recovery advisor, you don't longer have to think what are the different ways that you can recover the database and try to determinate which one is the most efficient. The advisor will layout your options and give you an educated guesstimate of how long it will take for each option. RMAN will even create the script for you to restore depending of the option you selected; To me this is a priceless feature, I lost track of the times my customers requires to know how long will it take to restore their databases, and the answer is not always easy to provide due to the variables involved.

- Use of flashback database for application upgrades: You upgrade your application, if something goes wrong you flashback to the point before the upgrade. I actually like very much this idea, however be careful if you do not have a fast I/O subsystem, as enabling flashback database can deteriorate your performance up to 30%. If your application is sensitive to performance then this may not be a good idea for you.

- Flashback Data Archive: Allows you to have an easily accessible archive of historical data, that you can access using standard SQLs. (looking forward to hear more about it).

- 11g Automatic provisioning of partitions: Very good feature, you actually provision the new partitions once new rows are available to insert on them, so there is no need for the DBA to pre-allocate the partitions. I wish this was available in 1ogR2, as I could immediately use it.

During the meeting Oracle Support spoke about the upgrade paths to 10gR2, something to keep in mind If you are upgrading from a version such as 10.2.0.1, you can upgrade to 11g directly, however if you need to downgrade you need to go to 10.2.0.3 and from there to your original release, ex: 10.2.0.1

11g bring several nice new features, however I have been told that Oracle 11g brings as well stability, Oracle solved several bugs, and enhanced features that were difficult to work with.

Enjoy the rest of the summer :-)

-

The Oracle Optimizer & Philosophycal discussions

2009-03-28T07:50:00.000-07:00

In Oracle 10.2.x I have found recently very often that some of our systems are slowing down in performance, when looking at the specific SQLs that are causing the problem to our surprise we see poor plan execution, when looking at the tables involved we found that the table has not been analyzed for a while and that puzzle me.

Starting 10g Oracle has a default job process called GATHER_STATS_JOB that using DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC suppose to intelligent decide whether or not collect stats for a table depending of the changes on that table.

I find that job is not working as it suppose to work, we have been replacing that job for explicit analyze using the following command.

EXEC DBMS_STATS.gather_schema_stats (ownname => '', cascade =>true, estimate_percent => dbms_stats.auto_sample_size);

When you specify the cascade parameters equal true explicitly it will include indexes

Oracle Corporation recommend setting the ESTIMATE_PERCENT parameter of the gathering procedures to DBMS_STATS.AUTO_SAMPLE_SIZE. This will maximize performance gains while achieving good statistical accuracy.

I know I may be opening Pandora's Box with the following topic, but I want to set my stand on this clear.

While doing the changes above for the analyze on multiple systems (about 15 production databases), one database presented performance degradation in 2 tables. This particular application was written by a famous software house of billing systems called "A M * * C S" you fill in the stars. When looking to what is causing the problem we found that the particular query has a column in the predicate that was not indexed, and we found that they added a tremendous amount of hidden parameters that modify the optimizer behavior without authorization. When questioned about why they introduced those parameters they said that although they don't know what they do, it has worked for some other customers (It is up to your to decide if that is a valid argument, but it is not for me) . The vendor told the client that the reason for the performance degradation is that the tables were analyzed. They said verbally that we should never analyze this two tables because they do not change much. When asked about number of daily changes we found that in 8 days 11% of the table will change and in a month 41.25% of the table will change (too much for a no changing table in my opinion).

This is my stand, "tables should always be analyzed". If you don't want a particular table to be analyzed for x or y reason you must lock the stats on those tables and document it. The Oracle optimizer picks the right execution plan 90% to 95% of the times (this is only true if you have a decent developer and you understand how the Oracle architecture works). For the other 5% to 10% of the code you need to use hint, SQL Profiles and other artifacts that Oracle provides so you can get a good execution plan.

"Changing the optimizer behavior so 5% to 10% of your code runs properly is just not a good idea nor makes any sense."

I will advice to change the optimizer behavior just when you exhausted any other option, you are dealing with an small set of tables that you know works well with the change, and you know you will not add more code or tables to that database.

As usual I respect the opinion of those people that think that "if it is no broken don't fix it", the fact is that if you are a DBA you better you better get your hands dirty and understand well how the application uses your database, what are your heaviest tables, what are your problematic queries, etc, etc. The vendor will leave after they finish the application code, data will change and you will have a ticking boom in your hands.

Rovio

2009-03-22T15:12:00.000-07:00

Rovio is an amazing robot created by WowWee,
The beauty about Rovio is that it allows you keep in contact
with your family while you are away. Rovio has a video camera
on the top that has 3 levels and can be managed remotely through the internet
using a browser; but it does not stop there it allows you to talk
to your family using full duplex communication.

The UI that Rovio uses is very intuitive, the best of all is loaded into the device itself, you do not have to deal with complex setup of web servers or anything like that. The UI allows you to move Rovio in any possible direction, switch the camera to 3 different levels, setup the sound, video quality and brightness.

The quality of the picture is amazing, the sound is not bad but it only works with Internet Explorer, there is not plug in for any other browser. It allows you to grant guest accounts to your family, in that way they will not be able to mess with the setup, but will be able to use it. Rovio is powered by rechargeable batteries that gets charged through a base that comes with the device. The auto docking features of the device is just amazing, it uses a beacon of light reflected on the roof of the room to guide itself into the docking station with the push of a button.

Rovio is propelled by 3 sets of wheels each wheel has several mini wheels, that gives Rovio and amazing moving flexibility. I am not clear about the price, I have seen it going from $130 at BJS to over $400 on the internet while I think at $130 is a fair price I would not pay for it $400, but I can't understand why such a difference in the price.

Pros & Cons

Pros:

Amazing moving flexibility.
Good quality picture.
Very intuitive user interface.
Very easy to setup.

Cons:

Once in a while it loses the network signal and needs manual reboot.
The battery could last longer.
It should come with additional beacons so you can preset way points to different parts of the house.
You should be able to move the video camera up and down on demand rather than having preset position.

Rating: 4 out of 5

HP2133 Mini-Note PC The Little wonder for DBAs.

2009-02-12T02:21:00.000-08:00

If you don't know it by know is because you haven't read my articles, but I am an enthusiastic in technology and an early adopter of it. I just love technology and I can't live without it.

For sometime I have been looking for an inexpensive laptop, small size but powerful enough that I could have my Oracle database and programs on it, and carry it every where I go without the need for a heavy laptop bag. Also the most important feature I was looking for was no Windows on it, yes I am more than disappointed with Windows Vista although I own several computers that have it installed.

I am glad to announce that I believe I got to the end of the journey in my finding, I recently got an HP2133 Mini-Note PC equipped with SUSE Linux Enterprise Desktop 10 SP1 and I just love it.

This Laptop has a price tag of $349 (at least by the time I bought it) from CompUSA.com comes with 1GB RAM and a powerful 120GB hard drive, and for $20 you can get a 2GB memory SDIMM from Newegg.com (BTW there is a handy video in Youtube that explains how to add the memory), HP sales it for $399, but I am sure you can get it for around $311 from other Internet sites like Provantange.

http://h10010.www1.hp.com/wwpc/us/en/sm/WF05a/321957-321957-64295-321838-306995-3687084.html?jumpid=oc_R1002_USENC-001_HP%202133%20
Base%20Model%20Mini-Note%20PC&lang=en&cc=us

It comes with the following

Specifications

	Lifestyle:	Business
		Netbooks


	Condition:	New


	Operating Systems:	SUSE Enterprise Desktop 10 SP1.


	Startup Operating System:	SUSE Enterprise Desktop 10 SP1.


	Platform:	Notebook PC


	Expansion Ports:	1 - Express Card Slot/54

	Display Type:	WXGA


	Viewable Screen Size:	8.9"


	Maximum Resolution:	1280 x 768

	Processor Brand:	VIA


	Processor Class:	C7-M


	Processor Speed:	1.60GHz


	Processor Cache:	128KB L2

	Memory Type:	DDR2


	Memory Size:	2GB


	Memory Speed:	DDR2 667


	Memory Slots (Total):	1


	Memory Slots (Available):	0


	Maximum Memory Supported:	2GB

	Graphics Description:	Integrated Graphics


	GPU/VPU:	VIA® Chrome9™


	Video Memory:	Up to 256MB Shared


	Video Interface:	VGA

	Capacity:	120GB

	Optical Drive Type:	N/A

	Supplemental Drive Type:	Media Reader


	Capacity:	1


	Media Types:	SecureDigital

	Audio Description:	Integrated Audio


	Audio Chipset:	ADI High Definition Codec

	PS/2 Mouse Connectors:	N/A


	PS/2 Keyboard Connectors:	N/A


	Serial Communication Ports:	N/A


	Parallel Ports:	N/A


	USB Ports:	2


	FireWire Ports:	N/A


	Fast Infrared Ports (FIR):	N/A


	LAN Ports:	1


	Modem Ports:	N/A


	Audio Out Jacks:	1


	Line In Jacks:	N/A


	Microphone Jacks:	1


	VGA Ports:	1


	S-Video Connectors:	N/A


	DVI Video:	N/A


	Port Replicator/Connector:	N/A

	Communications Description:	Integrated LAN
		Integrated Wireless LAN
		Integrated Bluetooth


	Interface Type:	RJ-45 Ethernet Connector
		Bluetooth™
		Broadcom 802.11a/b/g WLAN


	Data Transfer Rate:	10/100/1000Mbps Network
		54 Mbps
		Bluetooth 2.0


	Protocols:	802.11b
		802.11a
		802.11g

	Width:	10.04", 255 mm


	Height:	27 mm, 1.05"


	Depth:	6.5", 165 mm


	Weight:	2.6 lbs
		1.19 kg

	Mouse Type:	Touch Pad


	Scrolling Capability:	Scroll Zone

	Keyboard Type:	Spill Resistant

	Battery Type:	6-Cell Lithium-ion


	Battery Life:	Up to 4.5 hours

I was able to install Oracle 10gR2 on it with the help of my friend Nick Donatone from Oracle, the biggest challenge was to get installed the pre-required packages for Oracle like gcc and gcc++, etc. This laptop comes with no CD ROM nor DVD ROM, but I believe you can buy one optional or you can just get an external USB CD-ROM drive (No that you need one)

The packages you need to install Oracle comes with the software which you can download from Novell (NOTE: Just install the packages you need and no more than that) , Novell support tells me that this is an special edition of SUSE for HP, therefore not all open source software will work on it.

For the price tag that this computer has, all the features on it, and the flexibility it represents I will say this is a must buy.

So far Pros and Cons:

- Support from HP for matters with the OS could be a lot better.
- Support from Novel has been great so far (thanks Jack Hodge).
- The speakers are great
- The keyword is good enough and you can always get an USB keyword, mouse and connect it to
and external display and you won't have any problems with it.
- The Webcam does not work in SUSE Linux but, I heard there is a way to make it work with a
open source driver.
- The battery life is about 2 hours, I am thinking in getting the extended battery (gives you 4 hours)
- It is very light.
- It warms up a bit at the bottom while you use it for extended periods.
- It comes pack with plenty of neat Open source software such as Open office.
- The wireless is very reliable.
- I wish the processor were faster, but this is just good enough.
- I love the touch pad technology HP implemented here, it has an scroll zone that simulates that
third mouse button.

In conclusion I give to it a 3.5 rating out of 5.

This is what it needs to be a 5 out of 5.

- HP Support improvements.
- Webcam working out of the box with SUSE.
- Faster CPU.

The cost of selecting the wrong technology

2009-02-09T20:32:00.000-08:00

When I took my class about Oracle Streams, something called my attention, what do I use Oracle streams for, and how does it compares with Oracle Advance replication. The answer to that question is not trivial but it is important. Despite what Oracle can tell you Oracle Streams is a lot more complex and requires a lot more maintenance than Oracle Advance replication, but both of them have it's niche.

- Oracle Streams allows you to do transformations of the data as well as to partially replicate a table or a group of tables. (Mviews allows partial replication ans transformation as well)
- Oracle Replication is more suitable when you want to replicate something from site A to site B.
- Oracle Streams relies heavily on the use of keys.
- Oracle Streams is a newer technology more prompt to bugs, Oracle Advance replication has years in the market and is a proven technology.
- A single Oracle streams transaction generates multiple LCRs and the complete transaction is propagated, with advance replication only the values that change gets propagated.
- Oracle Streams can lag behind if you do not perform frequently commits in your source database.
- Oracle Streams allows you to do down streams capture which impose almost no performance impact to the source, while advance replication has an small performance penalty.

As you can see above it is important to evaluate your application when selecting any of this technologies. selecting the wrong technology just could create a maintenance nightmare that anyway is not going to satisfy your business requirements.

Oracle RAC, what Oracle does not tell you.

2009-02-09T19:50:00.000-08:00

Recently in one of my customers, executive management wants to deploy Oracle RAC everywhere, The enterprise architecture group armed with a bunch of marketing material and sales speeches are afraid to confront those decisions, furthermore they have decided to embrace them.

Although I love RAC technology there are several factors you need to take into consideration before jumping into the wagon.

- RAC is very , very complicated, you need high skilled dedicate personnel to maintain this.
- Not every single application is RAC aware.
- RAC does not improve your performance, RAC offers you high availability and scalability.

My advice is to not to use RAC at least your really needed, the degree of complexity and the
increase of maintenance is such that your ROI have to really call for it. A good way to determinate this is to ask yourself, how much money is going to lose the company because the database went down for 15 minutes.

If your application is not designed for RAC, meaning that is has transaction isolation and benefits from parallelism then you can experience significant performance degradation due to block contention (block busy waits). In one of the test we performed we found that an application that is not RAC aware got 45% performance degradation when deployed on RAC using load balance across 2 nodes.

There is this big misconception that if you need more power you add nodes to a RAC cluster, if your application can't escalate vertically, it is not going to escalate horizontally either, furthermore if the application is not RAC aware your performance will be degraded exponentially with each node you add to the cluster.

Despite everything I mention above RAC is an excellent architecture for high availability and scalability as long as your application allows for it.

Oracle Streams – The ABC’s of a Heartbeat table

2008-08-26T20:57:00.000-07:00

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------

NOTE: The following posting was in collaboration with Frank Ortega, Thanks Frank for reviewing my work.

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------

What is a heartbeat table and what is it used for?

This table is especially useful for databases that experience periods of low replication activity. The use of a heartbeat table ensures the replication environment is working even if there are just a few replicated changes. It also is valuable as a quick check for the DBA to ensure that the Streams environment is functioning properly (by simply querying a date column in the heartbeat table on the destination).

By default, an Oracle Streams capture process requests a Streams checkpoint after every 10MB of generated redo. The Oracle Streams metadata is only maintained during that checkpoint if the transactions have data relative to Streams capture. Implementing a heartbeat table ensures that there are open active ‘Streams transactions’ occurring regularly in the source database, insuring Streams metadata is updated during each checkpoint.

Ultimately, the end result of this is that CAPTURED_SCN in the Streams metadata (DBA_CAPTURE), will be maintained and stay current with redo that is being generated even during typical periods of low replication activity. This will ensure that the Capture process will not fail (missing archive logs for an old SCN required for capture) during Oracle Startup following low activity periods.

What is involved in doing this?

A.- You will need to create a table in your source database that has as one of the columns a date/timestamp column. Enable supplemental logging for the table and instantiate the table at the source. Export the table from the source and Import at the destinations with the instantiation option.

B.- Add a rule to capture changes for the heartbeat table at the source. Propagate the changes to the destination.

C.- Make sure that the target destination will apply changes to this
table as well by adding a new apply rule.

D.- Set up an automated job to update this table at the source site
periodically.

Let's do it

Assumptions: A Streams environment already exists. Capture Process (CAPTURE_STREAM) on source and Apply Process (APPLY_STREAM ) on destinations exist. Queues (STREAMS_QUEUE) already exist on both source and destinations. Table level replication is occurring. STREAMADM is streams administrator account on source and destination. Capture and Apply processes are stopped prior to adding rules and restarted when you are done.

(A)

On the source:

SQL> create table heartbeat (a number primary key, b date);

SQL> alter table heartbeat enable supplemental log data (all) columns;

Using userid

SQL> exec dbms_capture_adm.prepare_table_instantiation

(table_name => '.HEARTBEAT');

exp USERID=/ FILE=strmuser.dmp TABLE=.HEARTBEAT OBJECT_CONSISTENT=y ROWS=y

On the destinations:

imp USERID=/ FILE=strmuser.dmp TABLE=.HEARTBEAT COMMIT=y LOG=import.log STREAMS_INSTANTIATION=y GRANTS=n

(B)

On the source:

BEGIN

DBMS_STREAMS_ADM.ADD_TABLE_RULES(

table_name => '.heartbeat',

streams_type => 'CAPTURE',

streams_name => 'CAPTURE_STREAM',

queue_name => ‘STREAMADM.STREAMS_QUEUE',

include_dml => true,

include_ddl => false,

source_database => 'SOURCEDB');

END;

BEGIN

DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(

table_name => '.heartbeat',

streams_name => ‘CAPTURE_STREAM,

source_queue_name => 'STREAMADM.STREAMS_QUEUE',

destination_queue_name => ' STREAMADM.STREAMS_QUEUE@DESTDB’,

include_dml => true,

include_ddl => false,

source_database => 'SOURCEDB');

END;

(C)

On the destinations:

BEGIN

DBMS_STREAMS_ADM.ADD_TABLE_RULES(

table_name => '.heartbeat',

streams_type => 'APPLY',

streams_name => 'APPLY_STREAM',

queue_name => 'STREAMADM.STREAMS_QUEUE',

include_dml => false,

include_ddl => true,

source_database => 'SOURCEDB’);

END;

(D)

On the source:

SQL> create sequence temp_seq start with 1;

SQL> variable jobno number;
begin
dbms_job.submit(:jobno, 'insert into heartbeat

values (temp_seq.nextval, sysdate);',
sysdate, 'sysdate+60/(60*60*24)');
commit;
end;
/

The new Oracle scheduler and how it works

2008-05-05T13:13:00.000-07:00

Traditionally you used the dbms_job.submit to create a job (before Oracle 9i) , and you summited as parameters , the job number, what you want to be run, the start date and the interval, simple but if you ask me not much flexibility.

Typical example: exec dbms_job.submit(Jobno, 'begin (procedure_name); end; ' , SYSDATE, 'SYSDATE + 36/86400'); (Every 36 seconds)

In 10g and above Oracle provides greater flexibility and in my opinion clarity, it used to be a pain trying to come up with the right interval formulas for odds intervals. (for example 3/1440 would be execute every 3 minutes, but at simple view that is not that clear).

Now in 10g Oracle splits the process in 3 to provide flexibility, first you create a schedule, next you create a program and then you create a job and assign that schedule and program for the job. This allows you to reuse the schedule or the programs with another jobs.

NOTE: The examples below are for illustration purpose only and in most of the cases you need to complete and adapt the syntax to what you want to do.

Example of creating a schedule:

Begin
sys.dbms_scheduler.create_schedule(
repeat_interval => 'FREQ=WEEKLY;BDAY=WED;BYHOUR=8;BYMINUTE=0;BYSECOND=0',
start_date => to_timestamp_tz('2008-05-06 US/Eastern','YYYY-MM-DD TZR'),
comments => 'Wednesday AM Schedule',
schedule_name => '"RORTA"."WED_AM"');
end;
/

The schedule above is created on the rorta schema with the name WED_AM to be executed every Wednesday at 8 AM.

Creating a program:

You need first to create the program with the dbms_scheduler.create_program

begin
  dbms_scheduler.create_program (
      program_name => 'RORTA.WEEKLY_CHECK'
     ,program_type => 'STORED PROCEDURE'
     ,program_action => 'package.procedure'
     ,number_of_arguments => 1
     ,enabled => FALSE
     ,comments => 'comments');

You can pass arguments to your programs, see below

dbms_scheduler.define_program_argument (
      program_name => 'RORTA.WEEKLY_CHECK'
     ,argument_position => 1
     ,argument_name => 'kol1'
     ,argument_type => 'VARCHAR2'
     ,default_value => 'default'
   );

Now you enable the program

exec dbms_scheduler.enable(name => 'RORTA.WEEKLY_CHECK');

Creating the job:

A job is a combination of the schedule and a program. look at the example below:

begin
sys.dbms_scheduler.create_job(
job_name => 'RORTA.WEEKLY_CHECK',
program_name => 'RORTA.WEEKLY_CHECK',
schedule_name => 'RORTA.WED_AM'
comments => "Check the database Health",
auto_drop => FALSE,
enabled => TRUE);
end;
/

In addition your job can execute an external executable as follow:

begin
dbms_scheduler.create_job ( job_name => 'RUN_SHELL',
schedule_name => 'SHELL_SCHEDULE',
job_type => 'EXECUTABLE',
job_action => '/home/oracle/script.sh',
enabled => true,
comments => 'Shell-script' );
end;
/

Use the following views and commands to monitor your jobs

- dba_scheduler_job_run_details
- dba_scheduler_running_jobs
- dba_scheduler_job_log
- show all schedules
- dba_scheduler_schedules
- dba_scheduler_jobs
- dba_scheduler_programs
- dba_scheduile_programs_args

How to send html emails from Linux / Unix using mutt

2008-04-30T07:40:00.000-07:00

Hello Everybody

We recently created a new database health check for one of our customer as part of the DMDRS (Database Monitor Diagnostic & Resolution Services) service, it is a Sqlplus HTML report that is executed automatically through a cron job and then mailed automatically. The challenge was to display the email on the client as an html page. As usual I did my research on the internet and I found tons answers, most of them involved some amount of work and new software install. In general IT people don't link HTML emails because it posses a security risk and consumes several times the size of a regular email. In my case it was a marketing decision, the president of this company wants to see this email and I wanted to impress him, so HTML does the trick. Yes I could send the HTML report as an attachment but I want to make it easier for my customer (one click instead of 2 clicks).

I was getting crazy trying different options there is plenty of information on the internet however none of the answers worked for me. I did send an email out to my network asking them if they have done this before and to my surprise I got no answer. However one of my friends Michael Monostra after playing with it got it right, so kudos to Mike for his help and support.

Before getting to it, I consider important explaining a concept. What is MIME about?, we all hear it before we have seen the word in some of our emails but how many of you really know what it is?.

MIME or Multipurpose Internet Mail Extension in a nutshell is a specification for the format of a non-text email. The main characteristic is the presence of the MIME header, it tells your MIME compatible browser or email client that the message is not text, so it interprets your message in a different way.

So here is the way to make it work, we are going to use "mutt" command lie to send the email, I found that mutt is available in most of the Unix and Linux versions and installed by default.

1.- In your $HOME/.muttrc (initialization file for mutt) add the following lines (this file is likely to not exist, just create it).

set hostname = Your hostname
set realname = "The senders name"

2.- You need to check that you have a mime type like "text/html" associated with the extensions .html and .htm in either of this files /etc/mime.types and $HOME/.mime.types

3.- This is the actual command

mutt -e 'my_hdr Content-Type:text/html' -s "subject for the email" < filename.html

NOTE: During my tests I found that some providers won't allow you to send emails from dynamic IP address by any other method other than using their SMTP server, so I was not able to send the email from my home using Comcast ISP and a non business account. Some email servers will not deliver this emails and bounce it back because they do reverse lookup base on the hostname of the sender and if that is not setup properly it will be block. During my test I found that Yahoo and Gmail will not care about it and will receive the emails.

I hope this is helpful to you.

Rafael Orta

To my incredible smart nephew Alex Sugar

2008-03-21T22:29:00.000-07:00

Alex, It never stops amazing me how smart you are, you have a brilliant career in front of you, go for it champ. Here are the answer to some of your questions in case you forget. You can always write me to Rafael.Orta@oraprofessionals.com with any other question you may have.

To my readers: Alex is a fifth grader, eleven years old kid on his mid way to be a Visual Basic programmer; hopefully I can motivate him to get into the Java / Oracle duo. Alex has in mind to be a game programmer, no doubt he will reach his goal!

Integer: The integers are the set of numbers consisting of the natural numbers and their negatives. They are numbers that can be written without a fractional or decimal component, and fall within the set {... −2, −1, 0, 1, 2, ...}. For example, 65, 7, and −756 are integers; 1.6 and 1½ are not integers. In other terms, integers are the numbers you can count with items such as apples or your fingers, and their negatives, including 0.

Short Integer: a short integer is a data type that can represent a positive or negative whole number whose range is less than or equal to that of a standard integer on the same machine. Although there is no global standard, it is common for a short integer to either be exactly half the size, or the same size as a standard integer (in the same context). In this latter case, use of the word 'short' is technically redundant, but may be used to indicate that it is not a long integer.

A variable defined as a short integer in one programming language may be different in size to a similarly defined variable in another. In some languages this size is fixed across platforms, while in others it is machine dependent. In some languages this data type does not exist at all.

Long Integer: is a data type that can represent a positive or negative whole number whose range is greater than or equal to that of a standard integer on the same machine.

In practice it is usual for a long integer to require double the storage capacity of a standard integer, although this is not always the case.

A variable defined as a long integer in one programming language may be different in size to a similarly defined variable in another. In some languages this size is fixed across platforms, in others it is machine dependent. In some languages this data type does not exist at all.

Real: This is a data type used by computers programs to represent an approximation to a real number, because real numbers are not countable computers cannot represent them exactly using a finite amount of information. Most often the computer uses a reasonable approximation.

Blobs: A binary large object, also known as a blob, is a collection of binary data stored as a single entity in a database management system. Blobs are typically images, audio or other multimedia objects, though sometimes binary executable code is stored as a blob. Database support for blobs is not universal.

The data type and definition was introduced to describe data not originally defined in traditional computer database systems but became possible when disk space became cheap.

Fixed point Arithmetic: It is as well a real (numbers with decimals) data type for a number that has a fixed numbers of digits after the decimal point (and sometimes also before). They are useful for representing fractional values usually on base 2 (binary) or base 10. See my example below for more details.

Floating point: To make it easy for you let's say that floating point is the way the computer represent numbers that contain decimals. In other words floating point describes a numerical representation system in which a string of digits represents a real number.

The advantage of floating-point representation over fixed point (or integer) representation is that it can support a much wider range of values. For example, a fixed-point representation that has eight decimal digits, with the decimal point assumed to be positioned after the sixth digit, can represent the numbers 123456.78, 8765.43, 123.00, and so on, whereas a floating-point representation with eight decimal digits could also represent 1.2345678, 1234567.8, 0.000012345678, 12345678000000000, and so on.

Single Precision Floating Point: Single-precision values can contain decimal points and have a range of +/- 8.43*10^-37 to 3.40*10^38.

While Single-precision numbers can represent both enormous and microscopic values, they are limited to six digits of precision. In other words, Single-precision does a good job with figures like $451.21 and $6,411.92, but $671,421.22 cannot be represented exactly because it contains too many digits. Neither can 234.56789 or 0.00123456789. A Single-precision representation will come as close as it can in six digits: $671,421, or 234.568, or 0.00123457. Depending on your application, this rounding off can be a trivial or crippling deficiency.

Double Precision Floating Point: Double-precision floating-point numbers are to Single precision numbers what Long integers are to Integers They take twice as much space in memory (8 bytes versus 4 bytes), but have a greater range (+/- 4.19*10^-307 to 1.79*10^308) and a greater accuracy (15 to 16 digits of precision versus the 6 digits of Single-precision). A Double-precision, 5,000-element array requires 40,000 bytes. An Integer array with the same number of elements occupies only 10,000 bytes.

Short floating-point: is of the representation of smallest fixed precision provided by an implementation.

Long floating-point number: is of the representation of the largest fixed precision provided by an implementation.

Intermediate: between short and long formats are two others, arbitrarily called single and double.

The precise definition of these categories is implementation-dependent. However, the rough intent is that short floating-point numbers be precise to at least four decimal places (but also have a space-efficient representation); single floating-point numbers, to at least seven decimal places; and double floating-point numbers, to at least fourteen decimal places.

Source: different websites over the internet but mostly Wikipedia.

EZ Guide to Oracle Data Pump

2008-03-06T13:43:00.000-08:00

Oracle Data Pump (DP) was introduced in 10g to replace the legendary export / import utility (Still available). The Data Pump although takes more time to start is more flexible and manageable and provides greater performance.

Data Pump consist of the following components:

Data Pump API: DBMS_DATAPUMP (creates and monitors DP Jobs).
Metadata API : DBMS_METADATA (Provides objects definitions to DP).
Client Tool: expdp and impdp (Makes calls to DBMS_DATAPUMP).
Data movement API: Uses direct path API (DPAPI) to move data when possible.

The first you need to do is to setup a location (Oracle Directory) where the DP files will be stored.

SQL> create directory dumplocation as '/oradata/dumpfiles';
SQL> grant read, write on directory dumplocation to scott;

In addition a default directory can be created for data pump operations in the database as follows (once created privileged users with EXP_FULL_DATABASE or IMP_FULL_DATABASE privilege do not need to specify a directory):

SQL> create directory DATA_PUMP_DIR as '/oradata/dumpfiles';

Note: The name of the directory must be DATA_PUMP_DIR.

There are 5 different types of DP import and exports:

Database (The default performed by specifying the FULL=Y parameter)
Tablespaces (Performed by specifying the TABLESPACES parameter)
Schema (Performed by specifying the SCHEMAS parameter)
Table (Performed by specifying the TABLES parameter)
Transportable Tablespace (Performed by specifying the TRANSPORT_TABLESPACES parameter)

EXPDP:

$ expdp directory=dumplocation
..
..
Username: scott/tiger
..
..
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 14:49
$

Since we did not specify any other parameters, the expdp used default values for the file names (expdat.dmp, export.log), did schema-level export (login schema), calculated job estimation using blocks method, used default job name (SYS_EXPORT_SCHEMA_01), and exported both data and metadata.

List of the expdp parameters

Keyword Description (Default)
------------------------------------------------------------------------------
ATTACH Attach to existing job, e.g. ATTACH [=job name].
COMPRESSION Reduce size of dumpfile contents where valid
keyword values are: (METADATA_ONLY) and NONE.
CONTENT Specifies data to unload where the valid keywords are:
(ALL), DATA_ONLY, and METADATA_ONLY.
DIRECTORY Directory object to be used for dumpfiles and logfiles.
DUMPFILE List of destination dump files (expdat.dmp),
e.g. DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.
ENCRYPTION_PASSWORD Password key for creating encrypted column data.
ESTIMATE Calculate job estimates where the valid keywords are:
(BLOCKS) and STATISTICS.
ESTIMATE_ONLY Calculate job estimates without performing the export.
EXCLUDE Exclude specific object types, e.g. EXCLUDE=TABLE:EMP.
FILESIZE Specify the size of each dumpfile in units of bytes.
FLASHBACK_SCN SCN used to set session snapshot back to.
FLASHBACK_TIME Time used to get the SCN closest to the specified time.
FULL Export entire database (N).
HELP Display Help messages (N).
INCLUDE Include specific object types, e.g. INCLUDE=TABLE_DATA.
JOB_NAME Name of export job to create.
LOGFILE Log file name (export.log).
NETWORK_LINK Name of remote database link to the source system.
NOLOGFILE Do not write logfile (N).
PARALLEL Change the number of active workers for current job.
PARFILE Specify parameter file.
QUERY Predicate clause used to export a subset of a table.
SAMPLE Percentage of data to be exported;
SCHEMAS List of schemas to export (login schema).
STATUS Frequency (secs) job status is to be monitored where
the default (0) will show new status when available.
TABLES Identifies a list of tables to export - one schema only.
TABLESPACES Identifies a list of tablespaces to export.
TRANSPORT_FULL_CHECK Verify storage segments of all tables (N).
TRANSPORT_TABLESPACES List of tablespaces from which metadata will be unloaded.
VERSION Version of objects to export where valid keywords are:
(COMPATIBLE), LATEST, or any valid database version.

The following commands are valid while in interactive mode.
Note: abbreviations are allowed

Command Description
------------------------------------------------------------------------------
ADD_FILE Add dumpfile to dumpfile set.
CONTINUE_CLIENT Return to logging mode. Job will be re-started if idle.
EXIT_CLIENT Quit client session and leave job running.
FILESIZE Default filesize (bytes) for subsequent ADD_FILE commands.
HELP Summarize interactive commands.
KILL_JOB Detach and delete job.
PARALLEL Change the number of active workers for current job.
PARALLEL=.
START_JOB Start/resume current job.
STATUS Frequency (secs) job status is to be monitored where
the default (0) will show new status when available.
STATUS[=interval]
STOP_JOB Orderly shutdown of job execution and exits the client.
STOP_JOB=IMMEDIATE performs an immediate shutdown of the
Data Pump job.

IMPDP

$ impdp dumpfile=expdat.dmp
..
username: scott/tiger
..
Job "SCOTT"."SYS_IMPORT_FULL_01" successfully completed at 07:09

List of impdp Parameters

Keyword Description (Default)
------------------------------------------------------------------------------
ATTACH Attach to existing job, e.g. ATTACH [=job name].
CONTENT Specifies data to load where the valid keywords are:
(ALL), DATA_ONLY, and METADATA_ONLY.
DIRECTORY Directory object to be used for dump, log, and sql files.
DUMPFILE List of dumpfiles to import from (expdat.dmp),
e.g. DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.
ENCRYPTION_PASSWORD Password key for accessing encrypted column data.
This parameter is not valid for network import jobs.
ESTIMATE Calculate job estimates where the valid keywords are:
(BLOCKS) and STATISTICS.
EXCLUDE Exclude specific object types, e.g. EXCLUDE=TABLE:EMP.
FLASHBACK_SCN SCN used to set session snapshot back to.
FLASHBACK_TIME Time used to get the SCN closest to the specified time.
FULL Import everything from source (Y).
HELP Display help messages (N).
INCLUDE Include specific object types, e.g. INCLUDE=TABLE_DATA.
JOB_NAME Name of import job to create.
LOGFILE Log file name (import.log).
NETWORK_LINK Name of remote database link to the source system.
NOLOGFILE Do not write logfile.
PARALLEL Change the number of active workers for current job.
PARFILE Specify parameter file.
QUERY Predicate clause used to import a subset of a table.
REMAP_DATAFILE Redefine datafile references in all DDL statements.
REMAP_SCHEMA Objects from one schema are loaded into another schema.
REMAP_TABLESPACE Tablespace object are remapped to another tablespace.
REUSE_DATAFILES Tablespace will be initialized if it already exists (N).
SCHEMAS List of schemas to import.
SKIP_UNUSABLE_INDEXES Skip indexes that were set to the Index Unusable state.
SQLFILE Write all the SQL DDL to a specified file.
STATUS Frequency (secs) job status is to be monitored where
the default (0) will show new status when available.
STREAMS_CONFIGURATION Enable the loading of Streams metadata
TABLE_EXISTS_ACTION Action to take if imported object already exists.
Valid keywords: (SKIP), APPEND, REPLACE and TRUNCATE.
TABLES Identifies a list of tables to import.
TABLESPACES Identifies a list of tablespaces to import.
TRANSFORM Metadata transform to apply to applicable objects.
Valid transform keywords: SEGMENT_ATTRIBUTES, STORAGE
OID, and PCTSPACE.
TRANSPORT_DATAFILES List of datafiles to be imported by transportable mode.
TRANSPORT_FULL_CHECK Verify storage segments of all tables (N).
TRANSPORT_TABLESPACES List of tablespaces from which metadata will be loaded.
Only valid in NETWORK_LINK mode import operations.
VERSION Version of objects to export where valid keywords are:
(COMPATIBLE), LATEST, or any valid database version.
Only valid for NETWORK_LINK and SQLFILE.

Table Exports/Imports

The TABLES parameter is used to specify the tables that are to be exported. The following is an example of the table export and import syntax:

expdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log

impdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=impdpEMP_DEPT.log

The TABLE_EXISTS_ACTION=APPEND parameter allows data to be imported into existing tables.

Schema Exports/Imports

The OWNER parameter of exp has been replaced by the SCHEMAS parameter which is used to specify the schemas to be exported. The following is an example of the schema export and import syntax:

expdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log

impdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=impdpSCOTT.log

Database Exports/Imports

The FULL parameter indicates that a complete database export is required. The following is an example of the full database export and import syntax:

expdp system/password@db10g full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=expdpDB10G.log

impdp system/password@db10g full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=impdpDB10G.log

I was debating if adding this or not to this document because this suppose to be an EZ guide, but since these are my 2 favorite Data Pump features I decided to add it.

Few years ago you needed to wait for a long import / export to finish before going home and logout of you account or turn off your desktop. Or simple you got an alectric failure while the import was taking place and now you have to clean up and start all over (I have been there and done that, it is very frustrating).

Attach and Detach from a session

The first you need to know is that the core of the DP import or export operation is taking place using a database job, so it does not really depend of your client connection, cool isn't it?. In
other words your job will continue to run even if the client disconnects.

DP works in 2 modes, logging similar to the old import / export and interactive mode. To enter into the interactive mode just control-C while the import or export is taking place.

In the example below I pressed Control-C as soon as the export started.

oracle@mtl01dbatlora03:/opt/oracle/product/10.2.0.1/rdbms/log> expdp system/password

Export: Release 10.2.0.3.0 - Production on Friday, 07 March, 2008 11:31:24

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Release 10.2.0.3.0 - Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Export> status

Job: SYS_EXPORT_SCHEMA_01
Operation: EXPORT
Mode: SCHEMA
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /opt/oracle/product/10.2.0.1/rdbms/log/expdat.dmp
bytes written: 4,096

Worker 1 Status:
State: EXECUTING
Object Schema: SYSTEM
Object Name: TEMPLATE$_TARGETS_S
Object Type: SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Completed Objects: 18
Total Objects: 18
Worker Parallelism: 1

Export> exit_client

Then to re-attach all I have to do is

expdp system/password attach=SYS_EXPORT_SCHEMA_01

export>

and I will be on my way, if I decide to continue in logging mode all I next to do is

export> continue_client

The other cool feature of Data Pump is that in most of the cases you can stop the job do anything you forgot in the database (like creating a specific tablespace) and then re-start it. ``

Below is a table of some of the commands you can execute while in interactive mode.

PHOUG Presentation by Tim Gorman

2008-01-30T20:29:00.000-08:00

I had the opportunity tonight of assisted to the PHOUG meeting which included a presentation by Tim Gorman about "Scaling to Infinity: Partitioning Data Warehousing in Oracle".

I have to be honest this is the first time I hear about Tim, but he really surprised me, what a great presentation and presenter. I can see Tim have a lot of Data Warehousing experience and enjoy sharing his knowledge. I will try to summarize below the points that Tim covered.

Tim sees 3 Mayor errors on DW design.

A) Ignore the Basic Requirements for DW and design what is familiar
B) Fail to portray data changes over time.
C) Fail to Utilize partitioning from the beginning.

- Reporting and analysis applications are responsible for presenting data in the format that works best for end users and their query / analysis tools (very ofter what end users want is a simple spreadsheet, hundreds of columns wide.

- Reporting and analysis applications do not enforce business rules.

- Do not build a data model to enforce referential integrity and / or business rules. Normalization is intended for use in process oriented operational systems and not in a DW.

- Provide very simple data representation, with one degree of normalization for flexibility.

Time-variant data, who cares?

There are two major types of queries from business intelligence appliances to the data warehouse databases. Point in time (What is the present situation) and trend analysis (How things looks now versus 3 months ago). Dimension tables are usually designed to be point int time while fact tables are more trend analysis.

Every Data Warehouse has at least one slowly-changing dimension usually involving people.

The Virtuous Cycle

Using EXCHANGE PARTITION for loads enables:
- Elimination of ETL load windows and 24x7 availability for queries.
- Direct path loads
- Bitmap indexes and bitmap-join indexes.

Exchange partition is the basic technique of a bulk-loading new data into a temporary "Load table" which is then indexed , analyzed and then "published" all at once to the end user using the EXCHANGE PARTITION operation.

Final Recommendations

1.- Use dimensional data models for the "presentation" to end users.
2.- Base the database design on time-variant data structures.
3.- Use partitioning

Notes from Oracle RAC Customer Summit

2008-01-18T12:00:00.000-08:00

Hello Everybody

I had the opportunity this week of assisting to a Real Application Cluster Customer Summit in the new Oracle office of Bridgewater NJ. In the meeting was one of the product managers for Real application Cluster, members of he RACPACK and representation from Oracle Consulting.

I was a very interesting meeting where Oracle was basically asking their non RAC customers what can we do for you to start using it, and to the current customers what can we do for you be more comfortable using it.

They did emphasis on virtualization and the hardware becoming cheaper, the different ways that you can virtualize (server split (VMWARE) or server aggregations (GRID)). They went through the advantages and disadvantages of each one, in summary server split does not offer business continuity, scalability across servers, performance (high overhead) and reduction in the management burden and with the prices of the hardware and they becoming more powerful it almost a no brainier to use grid.

However Server split virtualization has its niche, It is good for systems with smaller workloads, test, development environments and non critical applications where you can justify the price tag involved with using RAC. while GRID works best for larger workloads and business critical applications.

Grid aims to provide dynamic resource provisioning and automation, For Oracle grid is evolving, the first version of grid (GRID 1.0) is the typical 2 nodes cluster database, once their customers start feeling comfortable with the and it's potential they wan to leverage the full potential of grid and evolve into what they call GRID 2.0. Oracle is looking into incorporate to it policy based service level automation.

In the meeting Oracle also spoke about ASM and Clusterware giving us some tips:

- They say that SAN based redundancy is commonly used but when that is not available ASM is a good option.
- They clarified that contrary to what people think ASM stripping in conjunction with SAN stripping does not hurt performance.
- They said there is a benefit in using ASM redundancy when using extended clusters (Clusters separated geographically).
- When using ASM 2 different LUNS should not map back to the same spindle.
- LUN provisioning should be done in a complimentary manner to ASM.
- Redundancy for private network is highly recommended, most customers use a pair of dedicated switches for it.
- Jumbo frames can be used to reduce the private network traffic and CPU overhead (1500 bps to 9k).
- They recommend to use workload management by service and to partition similar workloads by service.

Some Oracle 10g Tuning tips

2008-01-15T13:13:00.000-08:00

Some Oracle 10g Tuning tips

AWR

In Oracle10g the Automatic Workload repository is the primary component of Common Manageability Infrastructure (CMI). It is the successor for the Statspack. While the Statspack is still available with 10g, just a few improvements are planned to it; therefore AWR is the preferred mechanism for diagnosing database problems.

AWR provides data used by other CMI components, such as system generated alerts and the advisors. The AWR consist of 2 main components:

- In-memory statistics

- Repository snapshot.

In-memory statistics are gathered once a second on active sessions. They are not written to the database and are aged out of memory as new statistics are generated. In memory statistics are accessed through the view V$ACTIVE_SESSION_HISTORY which queries the ASH (Active Session History) circular buffer area of the SGA (System Global Area)

AWR relies on MMON which wakes up every hour and statistics collection into the repository snapshot (you can modify the collection interval), AWR snapshot provides a persistent view of database statistics.

A script is provided ($ORACLE_HOME/rdbms/admin/awrrpt.sql) to generate a report using repository snapshot. There is also an awrrpti.sql report as well, which has essentially the same output but allows you to define and report on a specific instance.

ADDM

The Automatic Database Diagnostic Monitor (ADDM) uses the snapshot information of AWR to automatically identify performance problems and make recommendations to correct them. ADDM is the predecessor of the Oracle expert tool. ADDM functionality is embedded into the kernel making performance impact near negligible. ADDM analysis is performed every time an AWR snapshot is taken. in addition ADDM can automatically fix certain problems.

To allow use of ADDM, a PL/SQL interface called DBMS_ADVISOR has been provided. This interface may be called directly through the supplied script $ORACLE_HOME/rdbms/admin/addmrpt.sql. Besides this PL/SQL package a number of views allow you to retrieve the results of any actions performed with the DBMS_ADVISOR API.

When the parameter STATISTICS_LEVEL is set to TYPICAL or ALL, the database will automatically schedule the AWR to be populated every 60 min. To create snapshots out of this interval you need to use the dbms_workload_repository.create_snapshot () package.

To be useful in diagnosing a particular problem, the snapshots needs to be created before and after the situation you wish to examine.

The following views are available for querying ADDM information.

View	Description
DBA_ADVISOR_ACTIONS	Actions associated with all recommendations
DBA_ADVISOR_COMMANDS	Commands used by advisor in DB for specifying recommended actions
DBA_ADVISOR_DEF_PARAMETERS	Parameters and default values for all tasks in the database.
DBA_ADVISOR_DEFINITIONS	Properties of all the advisors in the database
DBA_ADVISOR_DIRECTIVES	Not documented
DBA_ADVISOR_FINDINGS	Findings discovered by all advisors
DBA_ADVISOR_JOURNAL	Journal entries for all tasks
DBA_ADVISOR_LOG	Current state of all tasks along with execution specific data such as progress monitoring and completion status
DBA_ADVISOR_OBJECT_TYPES	Object types used by all advisors
DBA_ADVISOR_OBJECTS	Objects currently referenced by all advisors
DBA_ADVISOR_PARAMETERS	Parameters and their current value for all tasks
DBA_ADVISOR_PARAMETERS_PROJ	Not documented
DBA_ADVISOR_RATIONALE	Rationales for all recommendations
DBA_ADVISOR_RECOMMENDATION	Result of complemented diagnostics tasks with action recommendation for the problems identified in each run
DBA_ADVISOR_SQLA_REC_SUM	Recommendation rollup information for all workload objects after analysis
DBA_ADVISOR_SQLA_WK_MAP	Workload references for all tasks
DBA_ADVISOR_SQLA_WK_STMTS	Workload objects after analysis
DBA_ADVISOR_SQLW_COLVOL	Not documented
DBA_ADVISOR_SQLW_JOURNAL	Journal entries for all workload
DBA_ADVISOR_SQLW_PARAMETERS	Workload parameters and their current values
DBA_ADVISOR_SQLW_STMTS	Rows that correspond to all statements in the workload
DBA_ADVISOR_SQLW_SUM	Aggregate picture of all SQL workload
DBA_ADVISOR_SQLW_TABLES	Cross-references between workload statements and tables references on it.
DBA_ADVISOR_SQLW_TABVOL	Not documented
DBA_ADVISOR_SQLW_TEMPLATES	Aggregate picture of all SQL workload template objects
DBA_ADVISOR_TASKS	Information about all tasks in the DB
DBA_ADVISOR_TEMPLATES	Information about all templates in the DB
DBA_ADVISOR_USAGE	Information for each type of advisor

SQL Tuning Advisor

This is feature automates the entire SQL tuning process. It analyzes the SQL statements and executes a complete analysis of the statements.

The advisor can be administered with the procedures in the DBMS_SQLTUNE package. To use the API the user must be granted DBA role, SELECT_CATALOG_ROLE role and the ADVISOR privilege.

Running SQL Tuning advisor using DBMS_SQLTUNE package is a two-step process. First you create a tuning task and then you execute the task. The CREATE_TUNING_TASK function returns the task name that you have provided or generates a unique task name. You can use the task name to specify this task when using other API’s.

Check for chaining rows

To see if you have chaining problems, run utlchain.sql script that Oracle provides and is in /$ORACLE_HOME/rdbms/admin subdirectory. You should check for chaining on a weekly basis and fix any problems immediately.

Analyze table customers;

List chained rows;

Then run the following query accessing the CHAINED_ROWS table to check the customer table chaining:

Select HEAD_ROWID from CHAINED_ROWS where TABLE_NAME = ‘CUSTOMER’;

If no rows are returned, then you don’t have a chaining problem. If there is a chaining problem then the query will return the head_rowid for all chained rows. You can also use the “count(*)” against the CHAINED_ROWS table to find the number of chained rows. In V$SYSSTAT, the “table fetch continue row” is an indicator of chained rows as well.

To avoid row chaining, set PCTFREE correctly, the default is set to 10.

Increasing the log file size and LOG_CHECKPOINT_INTERVAL for speed

If you want to speed up large numbers of INSERTs, UPDATEs, and DELETEs, increase the sizes of your log files and make sure they are on the fastest disk. Previously, you could also increase the LOG_CHECKPOINT_INTERVAL if it was set such that it would checkpoint prior to log switch, but this parameter is being deprecated and currently is set to zero (which indicates switching based on redo log being full).

Determining if redo log file size is a problem

There could be 2 problems with the size of the redo log files:

A) Batch jobs that do not have enough total redo space to complete or are so fast that the online redo logs wrap (cycle through all the logs and start writing to the first one again) before they are archived to the offline redo logs.

B) Very long running jobs that are spending a large amount of time switching online redo logs. Long running jobs are often much faster when the entire job fits into a single online redo log. For the online transaction processing (OLTP) type of environment, smaller online redo log are usually better. Optimal online redo log switch should happen every half hour (no counting the long running batch jobs that shorten this time)

Use the query below to determinate if there is a problem, it shows the time between log switches.

SQL> l

select b.recid,

to_char(b.first_time,'DD-MON-YY HH:MI:SS') start_time, a.recid,

to_char(a.first_time,'DD-MON-YY HH:MI:SS') end_time,

round(((a.first_time - b.first_time)*25)*60,2) minutes

from v$log_history a, v$log_history b

where a.recid = b.recid+1

order by a.first_time a

Determining the size of your log files and checkpoint interval

SQL> l

1 select a.member, b.*

2 from v$logfile a, v$log b

3* where a.group# = b.group#

Discussion of the Oracle Global Executive Survey

2008-01-11T11:37:00.000-08:00

A couple of weeks ago I got a survey from Oracle where you rated several divisions of the company separated (Support, Consulting, Education, Sales, etc). I filled it and this morning I found an invitation in my Inbox from Keith Block (Oracle Executive Vice President) thanking me for filling the survey and inviting me for a conference call at 11am today where he was going to to talk about the result of the survey and what Oracle plans to do.

They received 7000 surveys, Keith invited several of the managers from the different organizations for them to explain what are they doing with the results of it on each area. Below are outlines the main points per organization.

Support

- They improve and use more the collaboration tool and offer a more personalized support experience.

- They are encouraging customers to use their configuration manager feature.

- They are building support capabilities inside the product in order to have proactive diagnostic.

Development

Here they focused only in application development.

- Lifetime support policy for the applications.
- They are working so you can monitor all your different application from Grid Control.
- They are working on having a common reporting tool for all your applications.
- They are focusing in tasks that aim to reduce the cost of ownership of their customers.
- They are architecting the integration between the different applications, the idea is for the
delivery of this to be a prepackaged integration product.
- They spoke about the Oracle Fusion application and how they expect it to be a plug-in to any existing application you may have.

Relationships

Apparently several customers have complained because they have to deal with many different Oracle reps for the different technologies, Keith acknowledged the issue and explained that there are several sales models, but they opted for having several reps with deep knowledge about the technology they represent. Keith explained that Oracle have a huge portfolio of products and it is impossible for a single person to get to know well all of them. Keith vision is that the sales reps would become trusted advisers inside the customer organization.

Oracle 10g Installation and upgrade tips

2008-01-06T14:32:00.000-08:00

Installing & Upgrading Oracle10g.

- The most common Oracle10g installation can be performed with just one CD.

- On Linux and Unix you may use the runInstaller -ignoreSysPrereqs flag to continue with the Oracle install even if the flavor of Linux is not certified by Oracle.

- Starting 10g the Oracle client can only be installed from a separate CD.

- Personal Edition is only available for Windows and does not include RAC.

- If you choose a started database based in one if the schemes available (Transaction processing, General Purpose or Data Warehouse) DBCA will run in no interactive mode, if you use advance it will run in interactive mode.

- Database Control (EM) is installed by default if you install a preconfigured database, for custom you have the option not to install it.

- OUI allows you to choose 3 types of storage File system, ASM and Raw devices.

- If you choose ASM and an ASM instance is not installed already Oracle will create one for you.

- In 10g you may enable automatic database backup during install using the backup and recovery options screen.

- The default disk quota for Flash back is 2GB.

- While installing you have the option to provide separate password for each administrative user or provide one password for all.

- The EM Webstage and Apache, which were installed with Oralce9i, are not longer installed with the Oracle10g.

- In 10g the disk requirements for install are now less.

- The installation is simplified, you can install the software and create the database just with the default settings.

- Oracle10g requires a minimum of 512MB minimum for an instance with database control and 256MB without database control and about 2.5GB HD.

- The Oracle10g Companion CD includes:
- Database examples.
- JPublisher.
- Legato.
- Natively compiled Java libraries.
- Oracle text-supplied knowledge bases.
- Oracle HTTP Server (Different Oracle Home).
- Oracle Apex (Different Oracle Home).

Configuring Oracle 10g.

- 10g includes a new SYSAUX tablespace to store all auxiliary metadata.

- DBCA can setup the flash recovery area, clone a database, setup Database control and grid control, register automatically LDAP, implement backup and recovery, create management repository and services.

- Cloning a database using DBCA templates saves time in database creation, because copying an already created seed database's files to the correct location takes less time than creating them.

- DBCA Templates are stored in $ORACLE_HOME/assistants/dbca/templates directory, they can be easily shared and can be copied from one machine to another.

- There are 2 types of templates seed and nonseed, seed templates have the extension .dbc and include the data files of an existing database. Nonseed templates has the extension .dbt and does not include data files.

- When creating a database template for cloning DBCA will shutdown the database and start it in mount state.

- In 10g the instance parameters are categorized into basic and advanced, in most of the cases modification of the basic parameters is enough to setup and tune the database.

- The compatible parameter is irreversible, once you set it you can no change it's value to one that is less than the previous value. To lower the value you will need to do a point in time recovery of the databases.

- Have you ever wonder how Oracle knows if you are using or not some of the enterprise features of the license?. What about Looking here

SQL> l 1 select name, detected_usages DU, last_usage_date 2 from dba_feature_usage_statistics 3* where currently_used = 'TRUE'

- If you want to know the high watermark usage of various attributes such as number of tablespaces, concurrent connections, number of user indexes, etc. Then this is the query you need.

SQL> l 1* select NAME, VERSION, HIGHWATER, LAST_VALUE, DESCRIPTION from dba_high_water_mark_statistics SQL>

If you don't see the information populated in the table all you have to do is execute the following package.

exec dbms_stats.gather_database_stats

- Oracle 10g provides a utility script $ORACLE_HOME/rdbms/admin/ultu10i.sql to perform pre-upgrade validation on the database to be upgraded. DBUA will also automatically run this tool. This scripts needs to be run as SYSDBA before you plan to perform a manual upgrade.

- Oracle uses the DBMS_REGISTRY package to determinate the objects to be upgrades. In 10g the cmpdbmig.sql script determinate which components are in the database by performing specifics callouts to the components REGISTRY. Versions of Oracle prior to 9.2 does not have components REGISTRY in this case the upgrades automatically creates and populates the component REGISTRY. You can query the components using the view DBA_REGISTRY.

- If you get any errors during the upgrade "Manually only" the utl101s.sql scripts provides the name of specific scripts to run to fix the failed component.

- Run the script $ORACLE_HOME/rdbms/admin/utlrp.sql at to end to recompile all objects.

When the use of Oracle range Partitioning is effective

2007-12-30T20:30:00.000-08:00

Have you ever wondered what is the point (number of rows) where the use of Oracle range partitioning start been effective? , we all know that there is some internal overhead in using partitioning but at what moment that overhead pays off.

For this test I created 2 tables as follow:

Non partitioned Table

create table np (id number primary key, name varchar2(30), last_name varchar2(30),
age number, dept_no number);

Partitioned Table

create table pt (id number primary key, name varchar2(30), last_name varchar2(30),
age number, dept_no number)
partition by range (dept_no)
(partition dept1 values less than (2),
partition dept2 values less than (3),
partition dept3 values less than (4),
partition dept4 values less than (5),
partition dept5 values less than (6),
partition dept6 values less than (7),
partition dept7 values less than (8),
partition dept8 values less than (9),
partition dept9 values less than (10),
partition dept10 values less than (maxvalue));

In addition I created 2 indexes one for each table on the column dept_no. For the partition table it was a local index

I used the following sql to populate the tables with different amount of data.

set serveroutput on;
declare
dept_alea number(2);
name_alea varchar2(30);
last_alea varchar2(30);
begin
for i in 1..&limit
loop
select dbms_random.value(1,10) into dept_alea from dual;
select dbms_random.string('U',30) into name_alea from dual;
select dbms_random.string('U',30) into last_alea from dual;
insert into &tabla
values (i, name_alea, last_alea, 37, dept_alea);
dbms_output.put_line (i);
commit;
end loop;
end;
/

In order to be partial and try to obtain valid information I did flushed the buffer cache and shared pool after each query. These are the queries I used:

@flush.sql
select * from np where dept_no=5;

@flush.sql
select * from pt where dept_no=5;

The following are the results I obtained

Number of Rows	Non Partition Time	Partition Time

100	Elapsed: 00:00:00.11	Elapsed: 00:00:00.51
1000	Elapsed: 00:00:00.14	Elapsed: 00:00:00.29
10000	Elapsed: 00:00:00.67	Elapsed: 00:00:00.94
11500	Elapsed: 00:00:00.87	Elapsed: 00:00:01.08
12000	Elapsed: 00:00:00.84	Elapsed: 00:00:01.05
12500	Elapsed: 00:00:01.25	Elapsed: 00:00:01.30
15000	Elapsed: 00:00:01.27	Elapsed: 00:00:01.42
17500	Elapsed: 00:00:01.63	Elapsed: 00:00:01.59
18000	Elapsed: 00:00:01.64	Elapsed: 00:00:01.40
20000	Elapsed: 00:00:04.24	Elapsed: 00:00:01.83

Conclusion: Now you know and you don't have to test, if you will have more than 17,500 rows
in a table, and you can range partition and will query or access the table by the partition key, then range partitioning is a good idea. If you are wondering this test was done in 11.0.6 EE on a Intel dual core 1.3Ghz HP computer on Oracle Unbreakable Linux.

4th. and final Day at the Oracle Open World.

2007-11-19T18:40:00.000-08:00

This was the last day and you kind of wish for it, the information you get here is very interesting but after receiving such amount of information day after day at the end of the week you are totally burn out. One thing that I noticed and I recommend is to bring a digital camera, sit close to the screen and take pictures of the slides. It is impossible to take notes and pay attention to what the presenter is saying, most of them talk too fast and the slides are shown just for few seconds, you don’t even have time to read them much less take notes out of them. It is like a race they want to cover as much as they can in 90 min.

My first session was “Fault diagnostic best practices, what every DBA must know about Oracle11g by Prabhaker Gongloor”. Oracle is changing the architecture where your log and trace files are stored, there is an new incident, trace and metadata directory. Oracle introduced a new parameter diagnostic_dest that when set it places your log file traces as well as other debugging files on that directory. If you don't set this parameter it would be set to your ORACLE_BASE, if ORACLE_BASE is not set then it would be set to your ORACLE_HOME. The new view v$diag_info contain all the locations for the different directories.

There is a new enterprise manager "support workbench" to support all this new architecture. This includes Incident Packaging Services (IPS). IPS uses the new directory structure to automate packaging of diagnostic data. It solves the problem of what to send to support. The new EM also offers 11g Health monitoring which helps you find problems before they impact the service.

Background_dump_dest is deprecated as well as user_dump_dest; however core_dump_dest still exist. Oracle new alert.log is in XML format however Oracle is still providing the alert.log in text format in case you have a program that uses the text format.

Also in 11g the RDBMS automatically purges your trace files, reducing the possibility for traces to fill up your disk. The Automatic diagnostic repository stores diagnostic data in a hierarchy directory.

My second session was “What is new in Oracle Transparent Data Encryption by Daniel Wong”. Oracle did a strong effort to improve this tool but the main characteristic is the Tablespace encryption, now you don't have to guess what data to encrypt as you can encrypt a complete tablespace. There is not issues in having encrypted partition tables or an encrypted parent table with references on a unencrypted child table, etc.

My third session was “Best Practices for Upgrading to Oracle Database 11g by Ravi Pattabhi” Ravi basically went through the different ways to execute the upgrade.

- Manual using Database upgrade assistant.

- Automatic using the Database upgrade assistant.

- Data pump export and import.

Oracle introduced since 10g (I believe it was 10g) a set of scripts that you run before the upgrade (pre-upgrade info tool and pre-upgrade analysis tool) and it tells you all the possible problems you may face if you upgrade. There is also a post-upgrade script that you can run to verify that everything was correct. If you are using RAC or ASM, you need to address the clusterware and ASM upgrade before you upgrade the database.

There is a new dba_registry view in 11g that helps you with the upgrade process , only real errors are spooled during the upgrade. There is even in Metalink a white paper that helps you to minimize downtime during the upgrade.

At then end of the presentation Ravi went through the print screen slides of the upgrade process using DBUA (DB Upgrade Assistant). If you have upgraded from 10gR1 to 10gR2 it is basically the same screens you are familiar with. Ravi suggested to test your upgrade (Any intelligent DBA does this anyway) use RMAN to clone you production database and test the upgrade in the clone database. Database control and grid control offers a variety of tools that help you measure the effect of upgrading to 11g for your application. If you are going to do the manual upgrade automate as much as you can so you minimize human errors.

My last session was “Oracle Database 10g tuning arsenal by John Kanagaraj" John basically went through the efficient use of AWR (Advance workdload repository) and ADDM (Advance Diagnostic Manager) which are features we currently use therefore I will not elaborate on it.

In conclusion it is my personal opinion that in 11g Oracle took an step back to improve and fix all the nice features they launched in 10g. There were comments from presenters saying that they believe 11g is the most stable beta Oracle has ever produced and I admire and congratulate Oracle for just taking the time to mature their product.

I hope you enjoyed this overview at the Oracle Openworld 2007

Rafael Orta

3rd. day at the Oracle Open World

2007-11-15T22:47:00.000-08:00

Last night was the customer appreciation event with Billy Joel, Lenny Kravitz and Stevie (Fleetwood mac). I got to my hotel pretty late and was exhausted, so I am trying to catch up today.

My first session of the day was "Oracle 11g simplifying management of Oracle database files by Aiman Al-Khammash". This session was mostly about ASM and how you can leverage it to help you administer your database files. ASM (Automatic Storage Manager) provides better performance than OCFS (Oracle Cluster File system).

11g allows rolling upgrades patches for Clusterware and ASM, in 11g you can have variable size extends and the extend size grows automatically with the file. 11g also increases maximum ASM size which is platform dependent. In 11g the allocation unit (AU) is not longer 1MB. as in 10g, you can now set it up to 1, 2, 4,8,16,32,64 MB. Same as in 10g the striping size is one allocation unit (AU) an file stripe is 128KB.

There are new ASM disk group attributes such as

AU_SIZE , DISK_REPAIR_TIME (Length of time before removing a disk once offline), COMPATIBLE_ASM and COMPATIBLE_RDBMS.

There is also a new view: V$ASM_ATTRIBUTE

There are new command line parameters (asmcmd):

lsdsk = List asm disks.
cp = Copy files between asm and non asm storage
remap = Re-maps unreadable blocks in normal high redundancy
md_backup / md_restore = Backup and restore ASM disk group environment.

In addition you can use $asmcmd help to find commands

My second session was "Backup and recovery best practices for very large databases by Tim Chien". Tim explained that every day disk is becoming a more popular backup media since you get performance at a low cost. Tim spoke about VTL (Virtual Tape Libraries) and said they provide you the best of both worlds (tape / disk). He advised to allocate disk backup for the most critical databases, and he also suggested to use locally attached tape drives. Tim suggest the to use the following tools:

- RMAN (Incremental backups)
- Data Pump
- Oracle Secure Backup
- Flash back technology (This is redo base).
- Data Guard (Use it as a disk backup or take the backup out of it)

Use third party storage space solutions.

- Snapshots
- Split mirror backup
- CDP appliances (provides continue snapshot copy-on-write or
allocate-on-write)

Plan your data layer and exploit partitioning and read only tablespaces.

Develop your backup procedure using the following options

Option 1: Use level 0 (FULL) and fast incremental backups.

Option 2: Use level 0, fast incremental backups and incremental updates backup.

Option 3: Data Guard + Full incremental backups.

Option 4: Maintain ETL loads , restore full backups and run needed ETL.

Consider the use of nologging (used widely for Data warehouses to speedup performance.

Divide full backup workloads across multiple days.

New in 11g he said you can use multi section backups (intra file parallel backup)

And lastly develop recovery strategies and use the data recovery advisory.

Then I assisted to "Larry Ellison key notes" Larry was introduced by Billy Joel and then he announced once again Oracle Virtualization and the software was open for download after the announcement. Larry basically spoke about what they heard the customers wants and what Oracle is doing to cover that demand. He also made reference to Unbreakable and the fact that despite it has been only one year since they launched it and they have not sales force for it several big companies have moved to it.

My fourth meeting of the day was "Oracle Active data guard and how to utilize your standby databases for production workload by Grant McAlister" . Grant started by explaining the traditional physical standby architecture

He also spoke about "Active data guard" which is a new Oracle option for which you have to pay a license fee; basically it allows you to continue ship and apply of redo on the standby while the database is in read only mode, with this you get better utilization and performance.

Grant said that you can scale out with active data guard as you can have your reports been feed out of the read only standby.

Active Data Guard allows real time query and RMAN block change tracking on a standby database. When the DB is open read only the consistency is guarantee since it is maintained through query scn.

Whole the database is open read only you can select and alter session. He also spoke about the best practices such as what are the apps that are candidate for active data guard. Grant explained how he was able to scale out just separating the queries from the writes.

He mentioned 3 methods to redirect writes:

1. - DML application level change.
2. - Use of a Database Link
3. - DML via database link.

He advises to use synonyms to hide the database links, keep in mind that from a read only database you can't invoke a remote store procedure.

My last session was "Oracle Data Guard tips and tricks by Larry Carpenter".
Larry started encouraging people to use ASM, he said it provides ease of management and advises the following:

- Use ASM
- Use Flashback recovery area
- Use RMAN to create your standby database.

Convert your standby first to ASM in that way moving to ASM does not mean downtime. He explained how having a flash recovery are simplifies management. The flash recovery area is required for flash back database. Larry explained how to turn on the flash recovery option. He explained that running jobs in the logical standby will not work until the standby gets to be the primary. In 11g you can use the DBMS_SCHEDULER on the active , the standby or both.

Well that is all for today folks.

2nd Day at the Oracle Openworld

2007-11-13T19:17:00.000-08:00

I am still trying to adjust to the jet lag, my internal clock is still in NJ or Bahamas where I was on vacation before getting here, never mind.

My first conference today was "Oracle Database 11g Business Intelligence and Data Warehousing by Ray Roccaforte". Ray spoke about the exorbitant grow of data volume, he mentioned Yahoo has a DW database of 250TB. he said that due to the increase in the amount of data you need more intelligent tools to interpret that data and that is where the business intelligence tools come to help. The oracle BI solution differentiates from other solutions in that it performs analytics inside the database therefore is faster.

Oracle provides:

Scalable Data management

ASM - Partitioning - Parallel Operations - Aggregation management - RAC

Analytic Platform

Multi dimensions Calculations - Time Series - Forecasting - Statistics - Data mining

ETL & Data Quality

Data Integration - Data Modeling - Meta data Management - Data Profiling - SOA

Oracle is launching a new initiative partnered with some of the main hardware providers like Dell, IBM and Sun and they are shipping along with the hardware the DW solution perfectly tunned and ready to use out of the box.

In 11g there are performance enhancements for Oracle partitioning such as interval partitioning that I mentioned yesterday, there are new types of composite partitioning (range - range , list - range , list - hash) , you can also partition on virtual columns (on expression values) , you also have reference partitioning (equi-partition a FK table with a PK table). The advantage of partition interval is that it creates the partition on the fly when the first row is inserted, for example:

create table sh.sales partition by range(time_id) interval (numtomyinterval (1,'month'),

Also in 11g there is a new feature called "SQL Query result cache" , I think I mentioned this in my previous blog from yesterday but basically you cache the results of a query and it can be used by subsequent queries that can make use of it. For example you may have an expensive group by part of a query, that group by can be used by subsequent queries that may need of it.

Also in 11g a single cube provides equivalence to thousand of materialized views, The 11g SQL query optimizer treats OLAP cubes as materialized views. what is not clear to me is if I need to buy OLAP or they are allowing me to create cubes as part of EE base.

My second conference was "Oracle 11g Real application testing: Avoid performance regressions with SQL performance analyzer by Benoit Dageville" Benoit mentioned that the new real application testing (RAT) has 2 components and is a package of Grid control that you can access for an extra fee.

RAT - SQL Performance Analyzer
- Database replay.

You use the SQL performance analyzer to do unit testing it, helps you to predict problems before it happen.

You use the database replay feature for comprehensive testing and replay real workload.

The SQL performance analyzer offers automatic data capture with almost no overhead, it offers you production sql context and complete sql workload as well as automatic analysis in minutes. You can later use the automatic regression tuning to improve your sql.

The cycle of RAT is as follow:

You capture your sql in production -> transfer the sql to test -> execute the sql pre-change -> set changes -> execute replay -> compare performance -> do the production changes.

Creating sql plan base lines helps to revert to previously known stable plans. SQL Tunning adv visor helps to explore better execution plans. You can leverage SQL performance analyzer 11g (SPA) to determinate how changes will affect you when you move to 11g, even if your database is a 10g database and you don't have a 11g database yet (optimizer upgrade simulator).

My third session was "Oracle 11g Data pump: Data compression, encryption and more by Carol Palmer" Carol started by giving an overview of data pump, and then focused in the following new features on 11g.

- Data compression
- Dump file encryption
- Partition base transportable tablespace

she mentioned that the original import (imp) will be around still in future versions but the export as we know it is not longer on 11g. Oracle is pushing everybody to move to data pump. Data pump import (impdp) is 15-40 times faster than the regular import and expdp (Data pump export) is twice faster than the regular export.

The new data compression feature compress data and meta data or both depending of what you want, it is an inline operation meaning that while you import of export the data gets compressed and decompressed contrary to the traditional two step extraction and compression. The speed of compression is as fast as traditional utilities like gzip and the compression ration is similar as well. Compression adds a 10% overhead versus non compressed import and export.

Data pump encryption offers password base encryption, you can use the Oracle wallet or you can use both. it is also an inline operation and works in conjunction with Oracle transparent data encryption, the default encryption algorithm is AES125.

Carol briefly spoke about partition base transportable tablespace, no much details on it. She also spoke about data transform (remap_data) , you basically create a function to obscure sensitive data and the translation is done once you import in your test system, in that way you can protect columns such as social security numbers, salaries etc from the developers or anyone with access to development. You can remap several columns for several tables at a time, the disadvantage I see is that you need to create a function for every column you want to remap.

Carol also spoke about "secure files" they basically are the new lobs but with better performance , you can compress them, you can encrypt them and they are supported by data pump.

My last session and my favorite for the day was "The top 10 new features of Oracle 11g by Thomas Kyte" . Tom gave a preamble saying that it was very easy to see the data out of database files and that you could find in sourceforge.com plenty of data unloaders that can read Oracle database files and that is why you need to encrypt the data.

1. - Encrypted tablespace: Column encryption as been there since 10g R2, however you could not use range scan and you had primary / foreign key issues when one or the other was not encrypted. Also sometimes you do not know what data to encrypt. Tablespace encryption solves all those limitations.

2. - Oracle 11g does more cache: You have client side cache, server result cache (JIT - MV) and PL/SQL functions result cache. There is a result_cache hint that you can use to tell Oracle to cache or not the results. You can also ask for the result of a function to be cache using the "create or replace function cache" statement.

3. - Standby just got better: Logical standby has been always limited in type support but always open for business, while the physical standby is easier but considered not helpful for day to day operation. Now the logical standby has XML type support and the physical standby can be used for destructive testing, in other words you can open it do the operations you need then bring it back to the original state using flash back and place it again in standby mode. Another improvement is that even that you are querying the database the redo is continuously been applied. This is great because you can offload your reports to the standby database without impacting the synchronization with your primary.

4. - Real application testing: I mentioned this before, Tom did not mentioned much about this other than it has 2 components: capture workload and replay DB workload.

5. - Data pump compression and encryption: Tom mentioned the new partition options (none - departition - merge) and you can also reuse dump files (reuse_dumpfiles).

6. - Virtual columns: They are column expressions involving other columns in the table or constants.

7. - Partitioning just got better: Now Oracle covers all the set of composite partitions and you can have virtual column partition, you can partition by reference and you can use interval partitioning which was mentioned before.

8. - Long waited pivot columns: now Oracle implemented a new clause called pivot to display pivot columns.

9. - Flash back data archive: For years customers have wanted to go back in time more than just few hours or a day, well because of the architecture of flash back that was not possible (you can't keep years of undo inline) , now with the new flash back data archive you can do total data recall or table by table.

10. - Finer grained dependency tracking: In 11g you have less objects invalidations even if you specifications to a procedure or add / modify or drop a column, or if you modify a synonym pointer.

11. - OLTP table compression: Oracle advance compression now has a 3x ratio.

Personal Comment: it seems that in 11g Oracle rather than getting crazy adding features that people may not use, they took an step back and matured and improved what they already have which I think is great.

More tomorrow...

Day 1 Oracle Open World

2007-11-12T21:56:00.000-08:00

Hello Everybody

this is my report of the first day at the Oracle Open World, my trip here was not easy, I spent Sunday since 2PM flying here and arrived Monday at 9:50 AM, during the trip my luggage was lost, but I am not going to bore you with the details.

I did not arrived on time for the opening keynote of Charles Philip but friends told me that it was good. There was a Keynote on Sunday which I also missed, but this time was Larry Ellison the speaker, he spoke about Oracle and its beginnings and today everybody has made comments about how good it was.

My first meeting was "The fastest and most cost effective backup for Oracle Database" by Sean McKeown. Sean is the manager for the system administrators in the Oracle Global IT organization, he was explaining how Oracle saved 1.5 million dollars using Oracle Secure Backup (OSB), Oracle Secure Backup is a media library manager from Oracle, In the future you won't need for third party media library managers to backup Oracle databases to tape. The product only works for Oracle databases and with certain hardware, they couldn't say if in the future it will work for non Oracle databases, however my guess is that it wont.

OSB supports virtual library tape, if you are not familiar with the term it is just making the backup to a disk that looks like a tape to your server, later on the information is backup to tape from that special disk and the space taken by the backup is released.

One curious thing is that is the System Administrators and not the DBAs the ones that take care of the backup and recovery of information at Oracle, the DBAs just need to be sure that RMAN is setup properly. Oracle is using OSB for their email, they serve more than 70,000 employees and the amount of information they store is pretty big. He said that Oracle secure backup was 20 to 50% faster than the Veritas solution and a lot cheaper too, it is just 3K per tape drive. OSB does not backup the undo tablespace.

My second meeting was "Oracle 11g next generation of high availability" by Sushil Kumer. In this session he talked about flash back query, flash back version which I was not familiar with (you basically can tell the database to show you the transactions that happened between 2 date time you choose), he spoke about flash back database, class="blsp-spelling-error" id="SPELLING_ERROR_12">ASM (Automatic Storage Manager), flash back table (none of this are new) , what is new is flash back transaction, he said that now Oracle is intelligent enough to know all the dependencies of a transaction and flash it back eliminating the need for you to guess what a particular process did and what tables touched. Flashback is free with

Flashback has become very popular as a test tool since you can bring back the whole database to the original state after you test any process, and it is a lot simpler and faster than restoring and recovering your database.

In a regular DR scenario the correction time = error time + f(db size), with
flash back the correction time = error time

He said a lot of Oracle customers has been asking to flash back longer periods but until now the product was not designed to support that, in 11g Oracle introduced flash back data archive which allows you to go back years using the same flash back architecture.

My third meeting was "The best Oracle 11g new features" by Rich Niemic. This was probably the best meeting I had so far, however he did not go technical and spend 15 min. making jokes, no that the jokes were not good, but when you have the opportunity to get to Rich in front of you, you want to get the most out his time.

He said that Oracle in 11g did not added a lot of new features but they focused in improving the current features and it was his opinion that 11g was the beta version of Oracle with the best quality ever. He said Oracle did improvements automating the backup to disk, now it does data corruption validation so you know that the backup is corrupt before you need to restore it. He said more people now is using backup to cheap ATA disks.

Rich spoke about Data recovery adviser, now you don't have to think about what is the best way to recover your database in the less time, Oracle will recommend it to you.

In Data guard now can synchronize disk mirroring, and in 11g you can have a snapshot standby and been recovered at the same time, let me explain that. In the past you could use your read only standby for reporting (read only) but while the database was open it was not applying the transaction log from the primary, you had to close the database and place it in recovery mode again so it apply the changes. In 11g you can have the database open read only and the transaction log is been applied at the same time.

11g has a feature called real patch application where making use of grid control and if Oracle detects an error it can apply a patch to fix the error online. Also Oracle is doing their best to deliver all their future patches making use of rolling patches in RAC, same with the Database upgrade.

In 11g you can do online table and index redefinition online, yes I know you will say you can do it in 10g too, the difference is that is 11g there is no locking.

He said that in 11g you only have to set 1 parameter and that parameter is memory_target and that includes your PGA and your SGA.

In 11g there is a new parameter called result_cache_mode, using this parameter you can ask Oracle not to cache the results, in addition you can flush the db cache. The cache in 11g is more intelligent therefore improving performance.

In 11g there are invisible indexes, which are useful if you want to know how an application with lots of indexes will behave if you drop some of them, but without the need to drop them. If you make the index invisible Oracle will ignore it, and if you really need the index all you have to do is make it visible again.

He said people usually rebuild indexes online while what they should be doing is coalescing the indexes.

In 11g Oracle introduced a simple integer data type saving space and speeding up transactions. 11g also introduced a lot of improvements in ADDM, there is also a Global ADDM which access the entire cluster performance information.

11g introduced the SQL Plan management (SPM) which basically is a tool that records what is going on and can play it back.

Oracle is deprecating Outlines and replacing it with SQL Plan base line.

There are also improvements in grid control 11g for performance. You can capture baselines, then use the sql access advisor and sql repair advisor.

The SQL Performance analyzer captures performance and allows you to run the same in other environment, at the end it provides you a graphic comparison between environments.

Real Application testing captures your database workload and allows you to replay it in another system, which this features you an test different version of software and architectures.

Partitioning just got better with the interval clause, now if you forget to specify the maximum value for the partition or a partition gets out of range you will not get an error are long as the interval parameter is specified. In other words the interval is telling Oracle what interval to use to create new partitions when data is out of range and now you can compress partitioned tables.

Oracle is introducing Oracle Secure Files which is faster than using lobs. Oracle also did improvements on the optimizer statistics , now you have an auto sampling feature. You can also gather stats and not publish the stats until you are not ready.

There is also a new feature where Oracle understand the correlation between different column tables and behave different depending of the utilization. You can have multi column, multi table stats. They also included several security enhancements which he did not detailed.

My last meeting for the day was the keynotes with Andy Meldensohn, he is the Senior product manager for the database, he went over some of the new features for the database again, and some of their customers implementing and taking advantage of those features spoke as well. The CIO of eHarmony said that they tried using SQL Server 2005 clustered environment but did not provided them the scalability they needed so they switched to Oracle RAC.

More information tomorrow.

Oracle 10g Background processes

2007-10-25T07:41:00.001-07:00

There are a large number of new background processes in Oracle 10g, however there are just 5 that are vital for the Database to work.

5 Old and Vital background processes

- SMON (System Monitor): Performs instance recovery, cleans up temporary segments
no longer in use and recovers dead transactions, coalesce free extents in the database.

- DBWR (Database Writer): This process in on charge of writing to the DB files.

- CKPT (Checkpoint): This process is on charge of executing checkpoint

- PMON (Process Monitor): Recovers when a user process fails, clean up cache and free
resources, it also monitors dispatchers and server processes.

- LGWR (Log Writer): This process is on charge of write to the redo log files

New Background processes

ASM:

- ARBn : (ASM) Rebalance working process , it rebalances extents.

- ORBn: Rebalance ASM data extent movements. There can be many of this at the same time.

- RBAL : This is the ASM Rebalance Master disk manager, open all disks under each disk group and make them
available to the various clients. It also creates the rebalance plan to move extents.

- OSMB: (ASM) Helps to manage the drive storage.

- ASMB: (ASM) Responsible to communicate the database Instance to the ASM Instance, provides the heartbeat to the ASM instance.

Server Side Optional

- RECO: Helps to Recover distributed Transactions.

- ORA_CJQ0_: Job queue coordinator , checks the log and spawn J### processed

- QMNC: AQ Coordinator, used to manage Oracle Streams Advance Queuing.

- ARCH, ARCn: Copies redo logs to archive logs destination.

- CTWR: Block Change Tracking Writer.

- ORA_D###_: Oracle Dispatchers processed

- ORA_J###_: Job queue process

- ORA_MMAN_: Memory manager, used for the manage of the automatic shared memory.

- ORA_MMNL_: Helps collecting metrics and information about sessions.

- ORA_MMON_: On charge of collecting stats.

- ORA###_: Dedicated server (user process).

- ORA_RVWR_: Recovery Writer (Flashback Database).

- ORA_S###_: Oracle Shared Server Process (Multi Threaded Server)

- ORA_P###_: Parallel Query Slaves Process

- ORA_LNS#_: Network Server.

Other Processes

- ORA_AQ_: Sends notifications when messages arrive into the queues (Advance Queue).

- ORA_Q###_: Queue Process.

- ORA_DMON_: Data Guard Broker Process.

- ORA_SNP###_: Snapshot process (Data Guard).

- ORA_TRWR_: Advance Queuing Time Manager.

- ORA_WMON_: Wakeup monitor process.

Undocumented Server Side Extra

- ORA_FMON_: Manage mapping information when using FILE_MAPPING

Standby Mode

- ORA_LSP0_: Logical Standby

- ORA_LSP1_: Dictionary build process for Logical Standby

- ORA_LSP2_: Set Guard Standby information for logical Standby.

- ORA_DMON_: Data Guard Broker Monitor Process.

- ORA_RSM#_: Data Guard Broker Resource Guard process.

- ORA_INSV_: Data Guard Broker Instance slave process

- ORA_NSV#_: Data Guard Broker NetSlave Process.

- ORA_MRP0_: Managed Recovery process for Data Guard.

- ORA_RFS_: Remote File Server process (Data Guard) receives archived redo from primary.

Real Application Cluster

- The following processes are unique to a RAC environment. You will not see them otherwise.

* Lock monitor (LMON) process: The LMON process monitors all instances in a cluster to detect the failure of an instance. It then facilitates the recovery of the global locks held by the failed instance. It is also responsible for reconfiguring locks and other resources when instances leave or are added to the cluster (as they fail and come back online, or as new instances are added to the cluster in real time).

* Lock manager daemon (LMD) process: The LMD process handles lock manager service requests for the global cache service (keeping the block buffers consistent between instances). It works primarily as a broker sending requests for resources to a queue that is handled by the LMSn processes. The LMD handles global deadlock detection/resolution and monitors for lock timeouts in the global environment.

* Lock manager server (LMSn) process: As noted earlier, in a RAC environment, each instance of Oracle is running on a different machine in a cluster, and they all access, in a read-write fashion, the same exact set of database files. To achieve this, the SGA block buffer caches must be kept consistent with respect to each other. This is one of the main goals of the LMSn process In earlier releases of Oracle Parallel Server (OPS) this was accomplished via a ping. That is, if a node in the cluster needed a read-consistent view of a block that was locked in exclusive mode by another node, the exchange of data was done via a disk flush (the block was pinged). This was a very expensive operation just to read data. Now, with the LMSn, this exchange is done via very fast cache-to-cache exchange over the clusters¿ high-speed connection. You may have up to ten LMSn processes per instance.

* Lock (LCK0) process: This process is very similar in functionality to the LMD process described earlier, but it handles requests for all global resources other than database block buffers.

- CM: Cluster Manager , it maintains the status of the nodes and instances across the cluster.

- GCS: Global Cache Service

- GES: Global Enqueue Service daemon

- GMS: Group Membership Service

- NM: Node Monitor

-NPIC: Network Inter-process communication

- ORA_PSP0_: Process spawner, allows Oracle PL/SQL embedded in HTML (PLUG IN)

- CSS (Cluster Synchronization Services): Cluster management and node monitoring, monitors
ASM

- CRS (Cluster Register Service):

- ORA_EMNO_: Event Monitor Process

- ORA_DIAG_: Diagnosability daemon (DIAG) process: The DIAG process is used exclusively in a RAC environment. It is responsible for monitoring the overall ¿health¿ of the instance, and it captures information needed in the processing of instance failures.

SQL Server useful hints for Oracle and Sybase DBAs

2007-10-16T12:09:00.000-07:00

1. Some System Catalog tables to get familiar with:

Sysdatabases - Contains one row for each database on Microsoft® SQL Server™. When SQL Server is initially installed, sysdatabases contains entries for the master, model, msdb, mssqlweb, and tempdb databases. This table is stored only in the master database.

Sysdevices - Contains one row for each disk backup file, tape backup file, and database file. This table is stored only in the master database.

Sysfiles - Contains one row for each file in a database. This system table is a virtual table; it cannot b updated or modified directly.

Sysindexes - Contains one row for each index and table in the database. This table is stored in each database.

Syslogins - Contains one row for each login account.

Sysobjects - Contains one row for each object (constraint, default, log, rule, stored procedure, and so on) created within a database. In tempdb only, this table includes a row for each temporary object.

Sysprocesses - The sysprocesses table holds information about processes running on Microsoft® SQL Server™. These processes can be client processes or system processes. sysprocesses is stored only in the master database.

2. Filegroups:

Database files can be grouped together in filegroups for allocation and administration purposes. Some systems can improve their performance by controlling the placement of data and indexes onto specific disk drives. Filegroups can aid this process. The system administrator can create filegroups for each disk drive, then assign specific tables, indexes, or the text, ntext, or image data from a table, to specific filegroups.

These are what Sybase calls database segments. In MS SQL Server you can backup, restore and run DBCC’s on individual filegroups of a database.

3. Indexes

Clustered indexes have one row in sysindexes with indid = 1. The pages in the data
chain and the rows in them are ordered on the value of the clustered index key.
No more than one clustered index per table.

When the clustered index is rebuild, all non clustered index get automatically rebuild.

4. xp_cmdshell command:

Xp_cmdshell - Executes a given command string as an operating-system command i.e. 1>xp_cmdshell dir

*** This is considered a security risk. When allowed. ***

5. Statistics

statistics - Statistical information can be used by the query processor to determine the optimal strategy for evaluating a query. When you create an index, SQL Server automatically stores statistical information regarding the distribution of values in the indexed column(s). The query optimizer in SQL Server uses these statistics to estimate the cost of using the index for a query. Additionally, when the AUTO_CREATE_STATISTICS database option is set to ON (default), SQL Server automatically creates statistics for columns without indexes that are used in a predicate.

sp_autostats - Displays or changes the automatic UPDATE STATISTICS setting for a specific index and statistics, or for all indexes and statistics for a given table or indexed view in the current database.

The following commands can help you determine if a index needs to be rebuild.

DBCC show statistics - Displays the current distribution statistics for the specified target on the specified table. The results returned indicate the selectivity of an index (the lower the density returned, the higher the selectivity) and provide the basis for determining whether or not an index is useful to the query optimizer. The results returned are based on distribution steps of the index.

sp_spaceused – If the unused column goes negitave, it is a candidate to check the index.

** When you do a sp_helpindex and see an indexed name _WA_sys_xxx, where xxx is a column name, These are statistics that are generated by SQL server. You can look at these as possible candidates to put and index on.

6. Terms

MSDB database - This is essentially where SQL server stores backup and restore
history. Must have it, one of the four necessary DB’s along with model, tempdb
and master.

SQL Server Agent – Essentially is the process that allows you to schedule periodic activities and notifications on the SQL Servers jobs scheduler.

1433 – MS SQL Server default listening port. On Sybase it was 2025. We usually change this to another port for security purposes.

Tran log - Unlike Sybase, on MS SQL Server 2000 you must have a separate file for the transaction log. It must be separate from the data files

Detach and attach a database- In SQL Server 2000, the data and transaction log files of a database can be detached and then reattached to another server, or even to the same server. Detaching a database removes the database from SQL Server but leaves the database intact within the data and transaction log files that compose the database. These data and transaction log files can then be used to attach the database to any instance of SQL Server, including the server from which the database was detached. This makes the database available in exactly the same state it was in when it was detached.

Log Shipping - In SQL Serve 2000 Enterprise Edition, you can use log shipping to feed transaction logs from one database to another on a constant basis. Continually backing up the transaction logs from a source database and then copying and restoring the logs to a destination database keeps the destination database synchronized with the source database. You must have Enterprise Edition, otherwise, you can do this yourself manually or with scheduled jobs.

Microsoft Clustering - A cluster is a group of independent computers that work together to run a common set of applications and provide the image of a single system to the client and application. The computers are physically connected by cables and programmatically connected by cluster software. These connections allow computers to use failover and load balancing, which is not possible with a stand-alone computer.

7. Database Placement

A general rule of thumb is to place the data, logs and backups on separate devices or disk drives. In case a drive should go bad.

Oracle Hints:

1. Analyze command. Analyze command serves several purposes in Oracle.

The two features that are most important to remember are:

A. Analyze allows you to update statistics on tables. (i.e. like update statistics

B. Analyze allows you to validate objects. (i.e. like dbcc checktable)
2. If you in log mode, Oracle writes the archive logs to disk when they become full.
If the disk where the archive logs are written is full, Oracle will stop working until
Space is made to right the archive logs.

Tips for Selling over the Internet and Marketing Strategies

2007-09-30T20:59:00.000-07:00

The process I am about to describe is a compilation of several seminars, Webminars, online training and personal experience.

Choosing the perfect strategy to sell is a moving target, what is true today may not apply tomorrow therefore you should modify your strategies all the time.

Choose the right product: This is probably the most important step, choose a product that you would like to know more about or a product that you believe on and have some knowledge about (reviews, explanations, white papers, advices). It is important that besides selling a product you demonstrate you are an authority on the product you provide, people will go to your side looking for advice and you can promote your product.

If you are unsure about where to find a product, the URLs below will point you to several wholesalers you can use.

Alibaba http://www.alibaba.com/

Thomasnet http://www.thomasnet.com/

Gowholesale http://www.gowholesale.com/

Wholesalecetral http://www.wholesalecentral.com/

Idropshipper http://www.idropshipper.com/

Doba http://www.doba.com/

Ezdropshipper http://www.ezdropshipper.com/

What if I can’t find a wholesaler for my product? This is far from been an issue but a business opportunity, for sure you will find

people which are not wholesalers and sale the product on retail. You can become a wholesaler, all you have to do is to search for the people selling that product on the internet using a search engine, skip the first 2 or 3 pages of the search (The people on the top of the search engine is selling and there is a good chance they do not need to do business with you). Call several of the providers you found on the third page and beyond and ask them if they would do drop shipment for you and ask them for a discount selling their product.

There are 2 types of drop shippers.

A) Blind Drop Shippers: Will not send the orders with their logo, the advantage here is that is likely the clients will return to your website to purchase more. The disadvantage here is that you will have to deal with the returns.

B) Standard Drop Shippers: The will send an invoice with their logo and price to your customers, the advantage is that the customer can directly deal with the wholesaler for returns.

What if you can’t find your product on the internet? If you can’t buy the product on the internet but you know the manufacturer, contact them and tell them you want to sale their product on the internet and ask them if they would ship directly for you.

You can also choose to sale products you use, products of your interest or related to your profession, products that a local business sales an have not representation on the internet, Information about something you know about.

I recommend for not mixing products for example selling lamps and detergents, websites like this would rank bad and would not attract people. Try to concentrate in a product or line of products that are related, try to not be generic. For example it is very unlikely you will rank well if you choose to sell MP3 Players (too generic), focus in selling a line of MP3 players for example Ipod Players.

What makes a good product? A good product is the one that there is a market for it, it can be profitable and there is supply for it (Manufactures or Retail Supplier).

How do I find a good product? There are several ways to do this but the list “What is hot on eBay is a pretty good indicator. http://pages.ebay.com/sellercentral/whatshot.html.

In addition you can use Google or Yahoo reverse search tool to see what people is looking for, or use a paid service such as Hammertap https://secure.hammertap.com/deepanalysis/

Build the first version of your website: Nobody expects this to be perfect; you will tune up your website with time but you need some presence as soon as possible, it takes some time (weeks) for your site to be recognized and indexed by the search engines. I do recommend for you to read my other paper about how to rank higher on the search engines.

You want your web page to be a resource about the product you are selling.

Generate Initial traffic:

This can be archived in 2 ways:

A) Relevance Listing: Choose the right keyword phrases for your website, 2 to 5 words specific to your product. Here you can use the reverse search tools offered by Google and Yahoo, while doing this you need to understand your market, know what keywords your competitors are using (use them)

You need to optimize your website to rank high following these steps:

A1) Optimize each page of your website for a different keyword phrase, use the right keywords on the right places.

A2) Places 10 to 12 words in the meta title and here place the keywords phrase you are optimizing the page for only once.

A3) Place 25 words or less in the Meta Description and include there the keyword you are optimizing for 2 to 3 times.

A4) Through the text of the page bold the keywords you are optimizing for and place it about 4% of times of the total text of the page. In other words if the page have 100 words write your keyword 4 times in bold on the page, (If bold will make your site look ugly don’t bold it).

A5) Place the keyword in the alternate text of every image in the page.

A6) Link exchange is a good strategy to rank higher, keep in mind that your goal is to gain incoming links rather that outgoing links.

A7) Publish your link into vertical portals (product specific search engines)
http://www.searchengineguide.com/

A8) Publish your website in this directory http://www.dmoz.com

B) Pay per click: The 2 most common are Overture (Yahoo search marketing) and Google adwords, use middle to low keywords that appear in the reverse search tool, also bid on misspelling keywords (you can use http://www.fatfingers.com) While you get familiar with this keep your bid amount to 10 cents and set a daily budget of $1.

Test your website: Your goal has to be to improve your website, you need to improve the conversion rate (sales / visitor), you need to convert 2% of your visitors into sales. You need to keep your visitors for at least 15 seconds in your page, if you can’t make this happen you need to improve your site with interesting content.

- Test your products prices (high vs. low) what is the floor and ceiling price? Low prices will generate volume.
- Increase your average order size by offering adds on.
- Do cross promotions, people buy related products at the same time for similar reasons.
- Offer a quantity discount.
- Offer free shipping.
- If bundling offer slight discounts.
- Ask friends and family for feedback.
- Test one thing at a time.
- Maintain a close look to your web site statistics.
- Test each change with at least 100 visitors.
- Improve the exit page (the last page your visitors saw in your website before going to other website).
- Send emails with propaganda to the customers in your database (not spam).

Market more aggressively:

- Sell your product in addition to your website on eBay.
- Find and research the product you want to sell.
- Try to be in the middle zone for the shipping cost.
- Create an effective title with rich keywords that catches attention.
- Create credibility on the description, if you item has scratches you want pictures of the scratch and say how the scratch was produced, that will create trustiness.
- Fill description with keywords.
- Be honest, reputation is something difficult to make easy to lose.
- List the features and benefits of your product.
- Check what others are charging for the same product you are selling.
- Tie into emotions put a personal note with your shipment or through something your customer is not expecting, example a postcard with discounts on future purchases, a business card, a thank you note or a brochure.
- Personalize the description use words like you or your.
- Use lot of pictures, a picture is worth 1000 words.
- When using eBay try to have your bid ending at Universal time, Saturday and Sunday are the best.
- Do post sale correspondence, contact your buyer, treat them as customers and notify them of the shipment.

Oracle Partitioning the old fashion way

2007-08-31T07:56:00.000-07:00

Long long time ago in a in a galaxy far far away, no just kidding about the galaxy part but it was really around Oracle version 8 / 8i when Oracle introduced Oracle partitioning. When there was not partitioning Database Administrators and developers had to come up with different techniques to emulate partitioning (manual partitioning). It recently called my attention that a software vendor didn't have a clue of what I was talking about when I mentioned "manual partition". In their development data model they have 1 single table that keeps historic information and they wanted their customer to pay for Oracle Enterprise Edition + Oracle Partitioning for an application that really requires Oracle Standard Edition (up to 4 CPUs). This translates into several thousand or dollars more in software and maintenance fees. That motivated me to post this blog when I will provide a general idea about the things we use to do, then you take it from here and change it to fit your needs.

In this example we are going to simulate range partitioning by date; instead of a partition table we are going to use 2 tables, table name "primero" (first in Spanish) which will hold the most recent 3 months of data, and table "segundo" (second in Spanish) will hold the rest of the data.

First Step - Table Creation

create table rorta.primero (cust_id number primary key, cust_name varchar2(30),
cust_lname varchar2(30), fecha date)
create table rorta.segundo (cust_id number primary key, cust_name varchar2(30),
cust_lname varchar2(30), fecha date)

Second Step - View Creation (This will allow you to query the 2 tables as a single table)

create view rorta.customer as select * from primero
union all select * from segundo;

Third Step - Create the procedure to insert into the table.

create procedure rorta.insertar as (c_id primero.CUST_ID%TYPE, c_name primero.CUST_NAME%TYPE,
c_lname primero.CUST_LNAME%TYPE, c_fecha primero.FECHA%TYPE)
begin
if c_fecha <= sysdate - 90 then insert into primero values (c_id, c_name, c_lname, c_fecha); else insert into segundo values (c_id, c_name, c_lname, c_fecha); end if; end; Four Step - Insert information in the table.

Fifth Step - Insert into the table

exec rorta.insertar(1,'Rafael','Orta','28-APR-1970')

The way that it works is that instead of executing an insert statement you will pass to the store procedure the data you want to insert as I did above. If you want to select, delete or update you could create similar store procedures to do that operation or you will need to go directly to the table where the data is hosted primero or segundo. If you need to query all the data use the view we created.

I hope this helps, contact me if you need further details at Consulting@Oraprofessionals.com