Oracle Performance and Backup Blog

DataGuard Broker configuration backup

2012-01-19T18:17:00.006Z

Some time ago I have seen question on oracle-l list about DataGuard Broker configuration backup. There was no clear answer and there were some suggestions to use "dgmgrl" and perl script to display and parse output. This topic was very interesting for me and I decided to dig deeper and find out how Oracle is keeping these information.

First step was to figure out how DataGuard Broker is gathering that data. You can enable debug option for dgmgrl tool and check how it works

piorovm$ dgmgrl -xml -debug
DGMGRL for Linux: Version 11.2.0.2.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect /
[W000 01/19 11:44:50.82] Connecting to database using .
[W000 01/19 11:44:50.84] Checking broker version [BEGIN :version := dbms_drs.dg_broker_info('VERSION'); END;].
[W000 01/19 11:44:50.84] Broker version is '11.2.0.2.0'
Connected.
DGMGRL>

As you can see there is a dbms_drs package which is used for that.I have checked what I could find in package definition file ( dbmsdrs.sql ) and it was it. There are two examples for blocking and non-blocking queries. Last thing to figure out was to find a XML based command I can use with dbms_drs package to get configuration.

DGMGRL> show configuration verbose;
<do_monitor version="11.2"><verify level="minor" object_id="4096"> </verify>
</do_monitor>

And then step after step I crated script showed below - displayconfig.sql

In 11.2 Oracle add new internal table X$DRC and it can be used to quick check of DataGuard Configuration. Object_id column can be decoded using following rules:
- bit 12 - object_id = 4096 - configuration
- bit 16 - object_id = 65536 - database (probably first database in site)
- bit 24 - object_id = 16777216 - first site
- bit 25 - object_id = 33554432 - second site

First database in first site will have following object_id = 16777216 + 65536 = 16842752.

SQL> set pagesize 1000
SQL> column value format a100
SQL> set linesize 200
SQL> select  attribute, object_id, utl_raw.cast_to_varchar2(hextoraw(value_raw)) value FROM x$drc;

ATTRIBUTE                       OBJECT_ID VALUE
------------------------------ ---------- ----------------------
DRC                                  4096 fsf
intended_state                       4096 ONLINE
protection_mode                      4096 MaxAvailability
enabled                              4096 YES
fast_start_failover                  4096 ENABLED
observer_info                        4096 client.local
fsfo_target                          4096 testdg_b
SITE                             16777216 testdg_a
DATABASE                         16842752 testdg_a
intended_state                   16842752 READ-WRITE-XPTON
connect_string                   16842752 testdg_a.local
RAC                              16842752 NO
enabled                          16842752 YES
role                             16842752 PRIMARY
INSTANCE                         16842753 testdg
SITE                             33554432 testdg_b
DATABASE                         33619968 testdg_b
intended_state                   33619968 PHYSICAL-APPLY-ON
connect_string                   33619968 testdg_b.local
RAC                              33619968 NO
enabled                          33619968 YES
role                             33619968 PHYSICAL
INSTANCE                         33619969 testdg

Disclaimer:
I have tested that script with 11.1 and 11.2 on Linux with simple primary / standby configuration and with RAC primary. Please check this script first on your test environment before you will run it on production configuration.

regards,
Marcin

displayconfig.sql:

set serveroutput on
set linesize 200
declare
rid integer;
indoc varchar2(4000);
outdoc varchar2(4000);
p integer;
z XMLType;
y clob;
v_xml xmltype;
tout varchar2(4000);
begin
    indoc:='<DO_CONFIGURE_DRC><ENUM_DRC/></DO_CONFIGURE_DRC>';
    y := null;
    rid :=dbms_drs.do_control(indoc);

    outdoc :=NULL;
    p:=1;
    while (outdoc is null)
    loop
       outdoc:=dbms_drs.get_response(rid,p);
       y := y || to_clob(outdoc);
    end loop;

    begin
    while (outdoc is not NULL)
    loop
       p:=p+1;
   
       outdoc:=dbms_drs.get_response(rid,p);
       y := y || to_clob(outdoc);
       
    end loop;
    exception
    when no_data_found then
        NULL;
    end;
    z := XMLType(y);

dbms_drs.delete_request(rid);    
    
for l in (select name , role, id, connectdb from XMLTABLE('/DRC/SITE/DATABASE' passing z COLUMNS name PATH '/DATABASE/@name', role PATH '/DATABASE/@role', id PATH '/DATABASE/@id', connectdb PATH '/DATABASE/@connect') )
 loop
    if (l.role = 'PRIMARY') then
        outdoc := 'create configuration DGCONF as primary database is ''' || l.name || ''' connect identifier is ' || l.connectdb || ';';
        dbms_output.put_line(outdoc);
    else
        outdoc := 'add database ''' || l.name || ''' as connect identifier is ' || l.connectdb || ';';
        dbms_output.put_line(outdoc);
    end if;
    
    indoc:='<DO_CONFIGURE_SITE version="11.2"><GET_RESOURCE res_id="' || l.id || '"/></DO_CONFIGURE_SITE>';
    y := null;
    rid :=dbms_drs.do_control(indoc);

    outdoc :=NULL;
    p:=1;
    while (outdoc is null)
    loop
       outdoc:=dbms_drs.get_response(rid,p);
       y := y || to_clob(outdoc);
    end loop;

    begin
    while (outdoc is not NULL)
    loop
       p:=p+1;
   
       outdoc:=dbms_drs.get_response(rid,p);
       y := y || to_clob(outdoc);
       
    end loop;
    exception
    when no_data_found then
        NULL;
    end;
    z := XMLType(y);
   
    select XMLQuery('/RESOURCE/PROPERTY_LIST/VALUE' passing z returning content) into v_xml from dual;
   
   
    for q in (select name , value , property_type as r from
           XMLTABLE('/VALUE' passing v_xml COLUMNS name PATH '/VALUE/@name', value PATH '/VALUE/@value', property_type PATH '/VALUE/@property_type' )
           where value is not null and name in
           ('AlternateLocation','ApplyInstanceTimeout','ApplyParallel','ArchiveLagTarget','Binding','BystandersFollowRoleChange','CommunicationTimeout',
           'DbFileNameConvert','DelayMins','DGConnectIdentifier','FastStartFailoverAutoReinstate','FastStartFailoverLagLimit','FastStartFailoverPmyShutdown',
           'FastStartFailoverTarget','FastStartFailoverThreshold','LogArchiveFormat','LogArchiveMaxProcesses','LogArchiveMinSucceedDest','LogArchiveTrace',
           'LogFileNameConvert','LogShipping','LogXptMode','LsbyASkipCfgPr','LsbyASkipErrorCfgPr','LsbyASkipTxnCfgPr','LsbyDSkipCfgPr','LsbyDSkipErrorCfgPr','LsbyDSkipTxnCfgPr',
           'LsbyMaxEventsRecorded','LsbyMaxSga','LsbyMaxServers','LsbyPreserveCommitOrder','LsbyRecordAppliedDdl','LsbyRecordSkipDdl','LsbyRecordSkipErrors','MaxConnections',
           'MaxFailure','NetTimeout','ObserverConnectIdentifier','PreferredApplyInstance','RedoCompression','ReopenSecs','StandbyArchiveLocation','StandbyFileManagement'
           ))
           loop
       dbms_output.put_line('edit database ' || l.name || ' set property ''' || q.name || ''' = ''' || q.value || ''';' );
    end loop;
    dbms_drs.delete_request(rid);
   
end loop;    
end;
/

Oracle Information Integration, Migration, and Consolidation - book review

2011-12-02T09:44:00.000Z

Recently I read interesting book about data migration and consolidation published by PACKT Publishing titled “Oracle Information Integration, Migration, and Consolidation”.

Whole book is split into 9 chapters covering different areas of this complicated topic. In my opinion this book is intended for architects or anyone who want to have a general overview of data migration and integration based on Oracle tools. You can find some details there as well but this book is covering too many tools to do a deep dive into each of them.

Chapter 1: Getting Started with Information Integration
This is a overview of data and process integration challenges and general approaches. Some high level use cases are presented as well.

Chapter 2: Oracle Tools and Products
This chapter is describing all major tools created or acquired by Oracle Corp. and used for consolidation, migration or data exchange. It is a general overview and can be used as easier way to go thought all application owned by Oracle.

Chapter 3: Application and Data Integration Case Study
Proof of Value (POV) for insurance company is a case study described in this chapter. This project include process and data integration using Oracle BPEL between mainframe VSAM, flat files, COBOL application, MQ Series and Oracle database. Author is presenting his findings and solutions to migrate one of business process in organization. Using step by step approach and Oracle SOA Suite project achieve all goals and bring some more benefits for organization.

Chapter 4: Oracle Database Migrations
There are many way to migrate data from one database to other and between versions of this same database. A few ways are mentioned here but author focused on migration from non-oracle database to Oracle database using SQL Developer. All important steps are presented and everyone can try to reproduce it on his own test environment.

Chapter 5: Database Migration Challenges and Solutions
Database migration is a tough projects and list of well know problems described in this chapter is a good point to start thinking about it. Most of RDBMS systems have their own ways to tread null/not-null columns, objects names have a different rules and procedural language running inside database is not a standard neither. Addressing most of these issues before starting a project is a good way to avoid unexpected results at the end.

Chapter 6: Data Consolidation and Management
Trusted data sources are one of base requirements in every enterprise application or reporting tools. Oracle solutions like Oracle Master Data Management Suit is presented here as an answer to these needs. Beside that some other solutions like data centric consolidation solution using in-memory TimesTen database or application centric data consolidation using Oracle Coherence are presented as well.

Chapter 7: Database-centric Data Integration
Database centric data integration is based on continuous data exchange process in heterogeneous database environment. Two very powerful products are presented here: Oracle GoldenGate and Oracle Data Integrator. In addition to that Oracle Gateways (Heteregeneous Services Agent) is presented as well. This chapter together with chapter 4 are most technical and detailed ones.

Chapter 8: Application and Process Integration
This chapter is presenting history of Oracle application and process integration solutions over the years. Starting with basic export / import tables for Oracle Apps and ending with Oracle Fusion Middleware and Oracle SOA Suite.

Chapter 9: Information Lifecycle Management for Transactional Applications
Last chapter is covering problem of data and application retention. How and how long data have to be kept in database, how to managed them and how to deal with application retirement process.

I can recommend that book for every one who want to have a general overview of Oracle approach to integration problems. As I said before one book can’t cover all technical problems/issues but it is good starting point to enter integration world.

regards,
Marcin

New release of S-ASH v.2.3

2011-11-27T14:25:00.000Z

New version of Oracle Simulate ASH is ready to download and test. You can find it here - sash-v2.3
New features:
- new easier installation process
- new metrics history (IO and system)
- improved support for RAC / multi database in one repository
- Oracle scheduler is used to manage jobs

This version of OraSASH has been tested with Oracle 10g and 11g R1 and R2 as a target database and Oracle 11gR2 Express Edition as repository.

regards,
Marcin

dNFS configuration and Hybrid Column Compression

2011-11-07T12:11:00.000Z

After my post about Hybrid Column Compression Kevin Closson suggested that there is a chance that Oracle doesn’t check what kind of storage is sitting behind NFS volumes. I followed after that idea and I had checked if I was able to create table with HCC on Open Filer or build-in NFS server. First attempt failed but at that point I decided to figure out how Oracle is checking storage and if there is any chance to used different one for test and education.

At first sight difference appear in two views - v$dnsf_server and v$dnfs_channels

SQL> set linesize 200
SQL> col DIRNAME format a30
SQL> col SVRNAME format a10
SQL> select * from v$dnfs_servers;

       ID SVRNAME    DIRNAME                           MNTPORT    NFSPORT      WTMAX      RTMAX
---------- ---------- ------------------------------ ---------- ---------- ---------- ----------
        1 piorovm    /nfs                                  653       2049          0          0
        2 sunzfs     /export/sunnfs                      53214       2049    1048576    1048576

In this example server piorovm is my local NFS storage and sunzfs is a simulator of SunStorage 7000. V$DNFS_SERVER view is showing differences in mount port and lack of information about wtmax and rtmax for local NFS.

SQL> col PATH format a25
SQL> col LOCAL format a25
SQL> col SVRNAME format a10
SQL> select * from v$dnfs_channels;

     PNUM SVRNAME    PATH                      LOCAL                          CH_ID     SVR_ID      SENDS      RECVS      PINGS
---------- ---------- ------------------------- ------------------------- ---------- ---------- ---------- ---------- ----------
       10 sunzfs     192.168.56.101                                               0          2          0          0          0
       10 sunzfs     192.168.56.101                                               1          2          0          0          0

There is no Information about local NFS channels at all in V$DNFS_CHANNELS. Similar situation happen for NFS shared from Open Filer server. In alert.log I could find an information about Sun NFS but there wasn't similar entries for local NFS.

Direct NFS: channel id [0] path [192.168.56.101] to filer [sunzfs] via local [] is UP
Direct NFS: channel id [1] path [192.168.56.101] to filer [sunzfs] via local [] is UP

I wanted to know why it happen so I was looking for information how to enable trace in dNFS subsystem.There are 3 events in oraus.msg related to autonfs and when I did research on Metalink I end up with following list of events enabling tracing for dNFS.

event "19392 trace name context forever, level 8"  # kgnfs
event "19394 trace name context forever, level 28" # skgnfs
event "19396 trace name context forever, level 2"  # kgodm

When I compared trace files from SUN ZFS and local NFS I realize that Oracle is not using dNFS for my local NFS even if local NFS exist in v$dnfs_servers and I did all configuration steps from Metalink note 762374.1.
In next step I captured all NFS traffic between Oracle and NFS server and started to analyze all NFS parameters and commands. After that I did internet and found following posts "Using dNFS for test purposes" and "Oracle direct nfs quick tips". I realize that there is a missing parameter in my local NFS configuration. This "insecure" parameter allow NFS connections from unprivileged ( above 1024 ) ports and has to be set to allow Oracle dNFS to connect to server. When I added “insecure” option to my local NFS configuration it appear in v$dnfs_channels and I was able to create table compressed with Hybrid Column Compression on my local NFS.

Here is my final configuration
/etc/exports

/nfs2 *(rw,anonuid=96,anongid=96,insecure,root_squash,wdelay,sync)

$ORACLE_HOME/dbs/oranfstab

server:local
path:192.168.1.41
export:/nfs2 mount:/local

/nfs2 is mounted in OS using following command

# mount -o rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,actimeo=0,nfsvers=3,timeo=600 node1:/nfs2 /local/

Tablespace and table with Hybrid Column Compression

SQL> create tablespace local_nfs datafile '/local/local_nfs01.dbf' size 10M;

Tablespace created.

Current status of dNFS views

SQL> set linesize 200
SQL> col DIRNAME format a30
SQL> col SVRNAME format a10
SQL> select * from v$dnfs_servers;

        ID SVRNAME    DIRNAME                           MNTPORT    NFSPORT      WTMAX      RTMAX
---------- ---------- ------------------------------ ---------- ---------- ---------- ----------
         1 local      /nfs2                                 792       2049     524288     524288

SQL> set linesize 200
SQL> col PATH format a25
SQL> col LOCAL format a25
SQL> col SVRNAME format a10
SQL> select * from v$dnfs_channels;

      PNUM SVRNAME    PATH                      LOCAL                          CH_ID     SVR_ID      SENDS      RECVS      PINGS
---------- ---------- ------------------------- ------------------------- ---------- ---------- ---------- ---------- ----------
        17 local      192.168.1.41                                                 0          1          0          0          0
        17 local      192.168.1.41                                                 1          1          0          0          0
        55 local      192.168.1.41                                                 0          1          0          0          0

Alert log's information about local NFS server are similar to previous Sun ZFS server.

Direct NFS: channel id [0] path [192.168.1.41] to filer [local] via local [] is UP
Direct NFS: channel id [1] path [192.168.1.41] to filer [local] via local [] is UP

And at the end I was able to create table with Hybrid Column Compression using local NFS server.

SQL> create table SCOTT.testsunnfs tablespace local_nfs  compress for archive high as select * from dba_objects where rownum < 100;

Table created.

Disclaimer
Please be aware that this article is for education only and using of Hybrid Column Compression according to Oracle license is limited to Exadata and two additional storage types : ZFS storage, and Pillar Axiom 600 - see details in Oracle license

Marcin

Quick note about Hybrid Columnar Compression running on my laptop

2011-10-12T08:27:00.000+01:00

As I mentioned in previous post Oracle allowed users to use Hybrid Columnar Compression on ZFS appliance. Two days ago there was a note in MOS about bug/enhancement but there was no patch for it. Yesterday I was able to download patch number 13041324 for Oracle 11.2.0.3 and successfully applied it against my installation on virtual machine. After that I run my Oracle's Sun Unified Storage Simulator and I created a new table space on it using NFS share together with Oracle Direct NFS client. Next I try to create compressed table using old existing table space on local ASM disk and it fail as expected. Than I give a try on new table space and this time table has been created :

SQL> create tablespace SUNNFSTS datafile '/nfs/sunnfsts.dbf' size 10M;
Tablespace created.

SQL> create table SCOTT.test1 tablespace USERS  compress for archive high as select * from dba_objects where rownum < 100;
create table SCOTT.test1 tablespace USERS  compress for archive high as select * from dba_objects where rownum < 100
                                                                                      *
ERROR at line 1:
ORA-64307: hybrid columnar compression is not supported for tablespaces on this
storage type

SQL> create table SCOTT.test tablespace SUNNFSTS  compress for archive high as select * from dba_objects where rownum < 100;
Table created.
SQL> exit

Looks like it is working and HCC can be tested without expensive hardware. I hope I will find some time to write down all my notes and create proper blog post about installation and configuration.
regards,
Marcin

Oracle 11.2.0.3 and Hybrid Columnar Compression Support

2011-10-06T18:20:00.000+01:00

Oracle announced support for Hybrid Columnar CompressionSupport for ZFS Storage Appliances and Pillar Axiom Storage Systems on Sep 30^th. According to Steve Tunstall's blog these feature should work with 11.2.0.3 and any ZFS Appliance. I have tested it on Oracle Enterprise Linux 5.5 with 11.2.0.3 and ZFS Appliance software emulator and it didn’t work. I have read Steve post again and he mentioned in comment that additional patch is required. There is was no information about it on MOS yesterday evening but today morning I found following note - "Bug 13041324 - Enhancement to allow HCC on ZFS and Pillar Storage". I still can’t find patch and I’m really tempted to try it on my virtual machines and has a possibility to test Oracle HCC feature. Hope this patch will be available soon.

Regards,

Marcin

Grid Control 11g - 2 small improvments

2011-10-04T02:51:00.000+01:00

I installed Oracle Grid Control 11g months ago and left it with typical configuration. Recently it becomes a little bit unstable and did a quick audit. Here are my first 2 finding but and will do a deeper dive into into if only I will find some more time.

Memory settings
In default configuration of Weblogic Management server running Grid Control instance maximum number of Java stack memory (parameter -Xmx) is unset and it is using default value of 512 MB. It is too small for big installation with many agents and concurrent users. Weblogic configuration allow users to set USER_MEM_ARGS variable with Java memory settings in starting script which will be used instead of MEM_ARGS variable. I has goint to change startManagedWebLogic.sh file as I want to keep default values for admin server but I found better solution on Oracle Enterprise Manager blog in "Increasing the JAVA heap size of the OMS" section. Instead of startManagedWebLogic.sh you need to edit startEMServer.sh file
and add following section just before last line

if [ "${SERVER_NAME}" != "EMGC_ADMINSERVER" ] ; then
   USER_MEM_ARGS="-Xms256m -Xmx1024m -XX:MaxPermSize=512m -XX:CompileThreshold=8000 -XX:PermSize=128m"
   export USER_MEM_ARGS
fi

Log rotation
It was very typical for old Oracle Application Server or Grid Control 10g that logs weren't rotated in default configurations. I have seen this many times when I have been asked to do some troubleshooting. After installation of Grid Control 11g together with Weblogic I was thinking how many logs has been left without rotation this time. Most of them looks OK but I found one mod_wl_ohs.log belonging to WebTier (Oracle HTTP server) which is still not rotated. This log file is configured in this configuration file mod_wl_ohs.conf and it is quite easy to add rotation to it. As all rotated Apache logs this line

WLLogFile ${ORACLE_INSTANCE}/diagnostics/logs/${COMPONENT_TYPE}/${COMPONENT_NAME}/mod_wl_ohs.log

should be started with rotatelog command:

WLLogFile "|${ORACLE_HOME}/ohs/bin/rotatelogs ${ORACLE_INSTANCE}/diagnostics/logs/${COMPONENT_TYPE}/${COMPONENT_NAME}/mod_wl_ohs.log 43200"

There is also a MOS note ID 1271676.1 about that issue.

Update:
Oracle Enterprise Manager 12c (like Cloud) has been just officially presented. There is still not documentation but you can download and install it.
regards,
Marcin

Oracle PeopleSoft Enterprise Financial Management - book review

2011-10-03T03:13:00.000+01:00

This is my second review in short time frame but totally different from first one. It is not about Oracle DB and I’m doing it from newbie position. This is my first experience with Oracle PeopleSoft from business perspective. Oracle PeopleSoft Enterprise Financial Management 9.1 Implementation is covering following topics from business perspective:
1. Financials Fundamentals
2. PeopleSoft Security
3. PeopleSoft Billing Module
4. PeopleSoft Accounts Receivable Module
5. PeopleSoft Asset Management Module
6. PeopleSoft Accounts Payable Module
7. PeopleSoft General Ledger Module
8. PeopleSoft Expenses Module
9. PeopleSoft Commitment Control

Whole book is covering one example based on Automotive company and every chapter is describing other business requirements.
All topics are explained in details and every topic has a short introduction from functional perspective. There are also implementation challenges for most common issues. Every chapter is guiding user though module configuration and typical actions. Examples have slides and description of all actions.

I can recommend this book for all for people without financial background like me as a starting point into PeopleSoft application world.

regards,
Marcin

Online index rebuild cleanup

2011-10-01T04:44:00.002+01:00

If session performing online index rebuild will be killed by mistake Oracle end up with some inconsistency inside dictionary. If you try to re-run killed command to create index once more database will complain that index already exist. Although if you try to drop index database will complain that index is in rebuild state and could not be dropped at that time.
To fix that issue you need to use package dbms_repair as showed in example:

Building test table

SQL> create table test tablespace users as select rownum id, 'xxxxxxxxxxxxxxxxxxxxxxxx' col1 from dba_source, dba_source where rownum < 10000000;
Table created.

New index build has been started with online clause and session has been killed

SQL> create index test_index on test (id) tablespace users online;
create index test_index on test (id) tablespace users online
                                                           *
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 4356
Session ID: 191 Serial number: 13

Sanity check - index exist even if build has been never completed.

SQL> select index_name from dba_indexes where table_name = 'TEST';
INDEX_NAME
------------------------------
TEST_INDEX

Now let's try to drop it

SQL> drop index test_index;
drop index test_index
           *
ERROR at line 1:
ORA-08104: this index object 66960 is being online built or rebuilt

So maybe we can create it again ?

SQL> create index test_index on test (id) tablespace users online;
create index test_index on test (id) tablespace users online
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object

Let's clean it up and allow Oracle to drop index - ONLINE_INDEX_CLEAN need a object number which can be taken from drop index error message or using the following query (at least it worked for me).

SQL> select min(object_id) from dba_objects where object_name = 'TEST_INDEX';

MIN(OBJECT_ID)
--------------
        66960

SQL> declare
  2  ret boolean;
  3
  4  begin
  5      ret:=dbms_repair.ONLINE_INDEX_CLEAN(66960);
  6  end;
  7  /

PL/SQL procedure successfully completed.

and now let's check if index is still there

SQL> select index_name from dba_indexes where table_name = 'TEST';
no rows selected

Hope it help to solve some of your problems.

regards,

Marcin

How to restore Oracle Grid Infrastructure OCR and vote disk on ASM

2011-09-18T13:42:00.001+01:00

After reading the book about Oracle RAC (see previous post) I decided to test recovery of OCR (Oracle Cluster Registry) file and vote disk placed on ASM disk groups. This is an operation I never did before. I was lucky enough and my ASM based RAC installation never had similar issue but I did it before when OCR and vote disk were placed on raw or block devices.

According to Oracle doc it should be straight forward and it really is but you need to careful otherwise you can hit strange errors, like "segmentation fault" for ocrconfig when Grid Infrastructure is down.

Here is step by step instruction:

Check if you can access OCR files

[root@node1 ~]# /u01/app/11.2.0/grid/bin/ocrcheck
PROT-602: Failed to retrieve data from the cluster registry
PROC-26: Error while accessing the physical storage

Stop CRS on all nodes

[root@node1 ~]# export CRS_HOME=/u01/app/11.2.0/grid
[root@node1 ~]# $CRS_HOME/bin/crsctl stop crs
CRS-2796: The command may not proceed when Cluster Ready Services is not running
CRS-4687: Shutdown command has completed with errors.
CRS-4000: Command Stop failed, or completed with errors.

If you got error like that try force option

[root@node1 ~]# $CRS_HOME/bin/crsctl stop crs -f
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'node1'
CRS-2673: Attempting to stop 'ora.ctssd' on 'node1'
CRS-2673: Attempting to stop 'ora.evmd' on 'node1'
CRS-2673: Attempting to stop 'ora.asm' on 'node1'
CRS-2673: Attempting to stop 'ora.mdnsd' on 'node1'
CRS-2677: Stop of 'ora.asm' on 'node1' succeeded
CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'node1'
CRS-2677: Stop of 'ora.mdnsd' on 'node1' succeeded
CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'node1' succeeded
CRS-2677: Stop of 'ora.evmd' on 'node1' succeeded
CRS-2677: Stop of 'ora.ctssd' on 'node1' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'node1'
CRS-2677: Stop of 'ora.cssd' on 'node1' succeeded
CRS-2673: Attempting to stop 'ora.diskmon' on 'node1'
CRS-2673: Attempting to stop 'ora.crf' on 'node1'
CRS-2677: Stop of 'ora.crf' on 'node1' succeeded
CRS-2673: Attempting to stop 'ora.gipcd' on 'node1'
CRS-2677: Stop of 'ora.diskmon' on 'node1' succeeded
CRS-2677: Stop of 'ora.gipcd' on 'node1' succeeded
CRS-2673: Attempting to stop 'ora.gpnpd' on 'node1'
CRS-2677: Stop of 'ora.gpnpd' on 'node1' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'node1' has completed
CRS-4133: Oracle High Availability Services has been stopped.
[root@node1 ~]#

When OCR file and vote disk are located on ASM disk group running ASM instance is required for recovery. Current version of Oracle Grid Infrastructure (GI) can be started in exclusive mode on one node to allow checks and restore.
There is a slight change between version 11.2.0.1 and 11.2.0.2 - in first release we need to start GI in exclusive mode and then stop CRS service (as showed below). In 11.2.0.2 we can start GI without CRS like mentioned in MOS note ID 1062983.1 but old steps are still working.
Steps for 11.2.0.1

[root@node1 ~]# $CRS_HOME/bin/crsctl start crs -excl
CRS-4123: Oracle High Availability Services has been started.
CRS-2672: Attempting to start 'ora.mdnsd' on 'node1'
CRS-2676: Start of 'ora.mdnsd' on 'node1' succeeded
CRS-2672: Attempting to start 'ora.gpnpd' on 'node1'
CRS-2676: Start of 'ora.gpnpd' on 'node1' succeeded
CRS-2672: Attempting to start 'ora.cssdmonitor' on 'node1'
CRS-2672: Attempting to start 'ora.gipcd' on 'node1'
CRS-2676: Start of 'ora.cssdmonitor' on 'node1' succeeded
CRS-2676: Start of 'ora.gipcd' on 'node1' succeeded
CRS-2672: Attempting to start 'ora.cssd' on 'node1'
CRS-2672: Attempting to start 'ora.diskmon' on 'node1'
CRS-2676: Start of 'ora.diskmon' on 'node1' succeeded
CRS-2676: Start of 'ora.cssd' on 'node1' succeeded
CRS-2672: Attempting to start 'ora.ctssd' on 'node1'
CRS-2672: Attempting to start 'ora.cluster_interconnect.haip' on 'node1'
CRS-2676: Start of 'ora.ctssd' on 'node1' succeeded
CRS-5017: The resource action "ora.cluster_interconnect.haip start" encountered the following error:
Start action for HAIP aborted
CRS-2674: Start of 'ora.cluster_interconnect.haip' on 'node1' failed
CRS-2679: Attempting to clean 'ora.cluster_interconnect.haip' on 'node1'
CRS-2681: Clean of 'ora.cluster_interconnect.haip' on 'node1' succeeded
CRS-2672: Attempting to start 'ora.asm' on 'node1'
CRS-2674: Start of 'ora.asm' on 'node1' failed
CRS-2673: Attempting to stop 'ora.ctssd' on 'node1'
CRS-2677: Stop of 'ora.ctssd' on 'node1' succeeded
CRS-4000: Command Start failed, or completed with errors.
[root@node1 ~]#

Errors are expected so now we need to stop CRS (if it has been started)

[root@node1 ~]# $CRS_HOME/bin/crsctl stop resource ora.crsd -init
[root@node1 ~]# $CRS_HOME/bin/crsctl stop resource ora.crsd -init
CRS-2500: Cannot stop resource 'ora.crsd' as it is not running
CRS-4000: Command Stop failed, or completed with errors.

Steps for 11.2.0.2 - this allow us to avoid starting CRS which is trying to start resources. Issues related to CRS resource will bring down HAIP and ASM - and this is root cause of errors shown in 11.2.0.1 example.

[root@node1 ~]# $CRS_HOME/bin/crsctl start crs -excl -nocrs
CRS-4123: Oracle High Availability Services has been started.
CRS-2672: Attempting to start 'ora.mdnsd' on 'node1'
CRS-2676: Start of 'ora.mdnsd' on 'node1' succeeded
CRS-2672: Attempting to start 'ora.gpnpd' on 'node1'
CRS-2676: Start of 'ora.gpnpd' on 'node1' succeeded
CRS-2672: Attempting to start 'ora.cssdmonitor' on 'node1'
CRS-2672: Attempting to start 'ora.gipcd' on 'node1'
CRS-2676: Start of 'ora.cssdmonitor' on 'node1' succeeded
CRS-2676: Start of 'ora.gipcd' on 'node1' succeeded
CRS-2672: Attempting to start 'ora.cssd' on 'node1'
CRS-2672: Attempting to start 'ora.diskmon' on 'node1'
CRS-2676: Start of 'ora.diskmon' on 'node1' succeeded
CRS-2676: Start of 'ora.cssd' on 'node1' succeeded
CRS-2672: Attempting to start 'ora.cluster_interconnect.haip' on 'node1'
CRS-2672: Attempting to start 'ora.ctssd' on 'node1'
CRS-2676: Start of 'ora.ctssd' on 'node1' succeeded
CRS-2676: Start of 'ora.cluster_interconnect.haip' on 'node1' succeeded
CRS-2672: Attempting to start 'ora.asm' on 'node1'
CRS-2676: Start of 'ora.asm' on 'node1' succeeded

No errors has been displayed and GI has been started in exclusive mode.

Now it is time to check if ASM instance is running and recreate ASM disk groups.

[oracle@node1 ~]$ ps aux | grep pmon
oracle    8009  0.0  0.9 495804 18564 ?        Ss   20:37   0:00 asm_pmon_+ASM1
oracle    8260  0.0  0.0  61184   760 pts/3    S+   20:38   0:00 grep pmon

ASM is running. Now we can connect and create a disk group for OCR and vote disk. Be sure that your new group will have proper COMPATIBLE.ASM value. You can put OCR and vote on groups with COMPATIBLE.ASM greater than 11.2.

[oracle@node1 ~]$ export ORACLE_HOME=/u01/app/11.2.0/grid
[oracle@node1 ~]$ export PATH=$PATH:$ORACLE_HOME/bin
[oracle@node1 ~]$ export ORACLE_SID=+ASM1
[oracle@node1 ~]$ sqlplus / as sysasm
SQL*Plus: Release 11.2.0.2.0 Production on Tue Aug 30 20:39:14 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options
SQL> show parameter asm_di
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
asm_diskgroups                       string
asm_diskstring                       string

SQL> alter system set asm_diskstring = '/dev/asmdisk*';
System altered.

SQL> create diskgroup DATA external redundancy disk '/dev/asmdisk11' attribute 'COMPATIBLE.ASM' = '11.2';
Diskgroup created.

In next step we need to create spfile for ASM instance as it will be used for rest of cluster to find out location of ASM disks using 'asm_diskstring'.
First text init.ora file has been created and then new binary spfile has been created on ASM disk group.

[oracle@node1 rac-cluster]$ cat /tmp/init.ora
*.asm_power_limit=1
*.diagnostic_dest='/u01/app/oracle'
*.instance_type='asm'
*.large_pool_size=12M
*.remote_login_passwordfile='EXCLUSIVE'
*.asm_diskstring = '/dev/asmdisk*'
[oracle@node1 rac-cluster]$ sqlplus / as sysasm
SQL*Plus: Release 11.2.0.2.0 Production on Tue Aug 30 21:23:36 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL> create spfile='+DATA' from pfile='/tmp/init.ora';
File created.

When disk group is ready it is time now to restore OCR from backup file.
Oracle Grid Infrastructure is doing OCR backup automatically into following location $GRID_HOME/cdata/. Let's restore and check if OCR file is recognized properly after it.

[root@node1 ~]# $CRS_HOME/bin/ocrconfig -restore /u01/app/11.2.0/grid/cdata/rac-cluster/backup_20110830_201118.ocr

[root@node1 ~]# $CRS_HOME/bin/ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          3
         Total space (kbytes)     :     262120
         Used space (kbytes)      :       3132
         Available space (kbytes) :     258988
         ID                       :  660203047
         Device/File Name         :      +DATA
                                    Device/File integrity check succeeded
                                    Device/File not configured
                                    Device/File not configured
                                    Device/File not configured
                                    Device/File not configured
         Cluster registry integrity check succeeded
         Logical corruption check succeeded

Now it is time to restore vote disk. This process will read asm_diskstring from ASM instance and will place vote files on these disks. See what happen when asm_diskstring is empty.

[root@node1 ~]# $CRS_HOME/bin/crsctl replace votedisk +DATA
CRS-4602: Failed 27 to add voting file 28652f742fc44f28bfc6d12d1412a604.
Failed to replace voting disk group with +DATA.
CRS-4000: Command Replace failed, or completed with errors.

Error message from log file.

[cssd(7894)]CRS-1638:Unable to locate voting file with ID 1b37b25b-686c4fb4-bfb82eac-357f48df that is being added to the list of configured voting files; details at (:CSSNM00022:) in /u01/app/11.2.0/grid/log/node1/cssd/ocssd.log
2011-08-30 20:44:12.256

When asm_diskstring is set up properly it is looking much better.

[root@node1 ~]# $CRS_HOME/bin/crsctl replace votedisk +DATA
Successful addition of voting disk 4ca8c2b58d394ff1bf7a9b88dd9f5fc3.
Successfully replaced voting disk group with +DATA.
CRS-4266: Voting file(s) successfully replaced
[root@node1 ~]#  $CRS_HOME/bin/crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
 1. ONLINE   4ca8c2b58d394ff1bf7a9b88dd9f5fc3 (/dev/asmdisk11) [DATA]
Located 1 voting disk(s).

In last step Grid Infrastructure need to be restarted on all nodes.

[root@node1 ~]#  $CRS_HOME/bin/crsctl stop crs -f
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'node1'
CRS-2673: Attempting to stop 'ora.mdnsd' on 'node1'
CRS-2673: Attempting to stop 'ora.ctssd' on 'node1'
CRS-2673: Attempting to stop 'ora.asm' on 'node1'
CRS-2677: Stop of 'ora.asm' on 'node1' succeeded
CRS-2677: Stop of 'ora.mdnsd' on 'node1' succeeded
CRS-2677: Stop of 'ora.ctssd' on 'node1' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'node1'
CRS-2677: Stop of 'ora.cssd' on 'node1' succeeded
CRS-2673: Attempting to stop 'ora.gipcd' on 'node1'
CRS-2673: Attempting to stop 'ora.diskmon' on 'node1'
CRS-2677: Stop of 'ora.diskmon' on 'node1' succeeded
CRS-2677: Stop of 'ora.gipcd' on 'node1' succeeded
CRS-2673: Attempting to stop 'ora.gpnpd' on 'node1'
CRS-2677: Stop of 'ora.gpnpd' on 'node1' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'node1' has completed
CRS-4133: Oracle High Availability Services has been stopped.

[root@node1 ~]#  $CRS_HOME/bin/crsctl start crs
CRS-4123: Oracle High Availability Services has been started.

If everything went well similar result is expected

[root@node1 ~]# $CRS_HOME/bin/crsctl check cluster -all
**************************************************************
node1:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************
node2:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************
node3:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************

Now Grid Infrastructure is working and if database ASM disk groups has been destroyed as well or we have everything in one ASM disk groups (like in my example) it is time to restore database from backup using Oracle Recovery Manager. But this is a different story.

Hope it help someone to restore Grid Infrastructure after disk crash.

regards,

Marcin

Book review - Oracle 11g R1/R2 Real Application Clusters Essentials

2011-08-29T17:26:00.000+01:00

Recently I have read a new book “Oracle 11g R1/R2 Real Application Clusters Essentials” by Ben Prusinski and Syed Jaffer Hussain.. First of all I would like to thanks both authors for good lecture for few days. Whole book is divided into a few parts:

introduction to Oracle Real Application Server including High Availability concepts, various storage options and overall Oracle RAC architecture - chapter 1 and 2
installation of Oracle RAC components - Clusterware, Automatic Storage Management - chapter 3
overview of Oracle ASM - chapter 4
cluster components troubleshooting - chapter 5
Oracle Database installation - chapter 6
Day by day topics including administration, backup, performance tuning and upgrade of Oracle Real Application Cluster - chapter 6, 7, 8 and 9
Real life examples including implementation of Oracle EBS using Oracle RAC - chapter 10 and 11
Oracle Maximum Availability - Chapter 12
Overview of Oracle tools for more experience users - Appendinx

In a first part authors are presenting High Availability options for Oracle and describing all main hardware components required for Oracle RAC implementation (server, network and storage options). Next chapter is covering an Oracle RAC architecture and it is covering mostly 11g Release 1. There is some information about a 11g Release 2 as well but due to number of changes between both version I would like to see two chapters instead of one - some additional information about a 11gR2 is covered at the end of appendix.

The next part is a guide how to install whole an Oracle RAC infrastructure - Clusterware and Automatic Storage Management in case of 11g Release 1 or Grid Infrastructure in case of version 11g Release 2. It is well documented and I will help new users to go though this process.

The next chapter is covering all topics related to Oracle Automatic Storage Management. Authors are presenting concepts and overview of this component as well as basic administration options. There is also separate part about new features of Oracle ASM 11g Release 2 covering ACFS file system.

Chapter 5 is about managing and troubleshooting Oracle RAC components. Some tools and common scenarios based on typical clusterware issues are presented there together with solving tips.

The next chapters (6,7,8 and 9) describe installation of database as well as new features of an Oracle 11g Release 1 and Release 2. In subsequent paragraph we can read about Oracle RAC High Availability features like - Load Balancing, Transparent Application Failover and Fast Connection Failover. After that backup and recovery strategy for database and whole clusterware comes to play. From management perspective this part is very important and should be study carefully. Next part is a overview of performance tuning tools, wait event and statistics important any performance improvement work.

Real life examples is in my opinion most interesting part of the book. All readers can go though typical scenario like adding or deleting node from cluster or converting database from single instance to RAC database. Configuration of Oracle EBS with Oracle RAC is also very well documented.

When I went though whole book I have to say that it was a good lecture and I can recommend it for everybody who want to do first dive into Oracle RAC world. Furthermore I think that more experience users will find this book interesting and worth to read too.

regards,

Marcin

Oracle VM 3.0

2011-08-24T19:48:00.000+01:00

Announced yesterday Oracle VM 3.0 can be downloaded from edelivery.oracle.com.

I have downloaded Oracle VM server - 177 M and Oracle VM Manager - 2.4 GB. As far as I remember Oracle VM Manager is based on Oracle Database and Weblogic stack and this explain a little bit size of iso image with VM manager.

Documentation can found here -Oracle VM 3.0 documentation.

ps. Going to install it now

regards,

Marcin

PL/SQL myths busted

2011-08-06T17:07:00.001+01:00

RSS feed from Oakie's blog point me to Toon's Koppelaars post and his redirection to Morten Braten blog. Morten wrote excellent post about stored PL/SQL procedures - Mythbusters: Stored Procedures Edition.

regards,

Marcin

How to find SQL_ID and PLAN_HASH_VALUE in Oracle SQL Plan Management Baselines

2011-07-28T19:42:00.000+01:00

SQL_ID and/or PLAN_HASH_VALUE are used almost globally across Oracle database to identify query. This is not a case for SPM mechanism
using different identifiers - SQL_HANDLE for query and PLAN_NAME for execution plan. SQL_HANDLE contain hexadecimal representation of EXACT_MATCHING_SIGNATURE from V$SQL but we don't have any information in DBA_SQL_PLAN_BASELINES about SQL_ID and PLAN_HASH_VALUE.
After some research I found two ways to find SQL_ID matched to SQL baseline (if there is more please let me know).

Let me present following example - first of all test environment will be prepared

SQL> select sql_handle, plan_name from dba_sql_plan_baselines;

no rows selected

SQL> select count(*) from soe.customers where customer_id = 19998;

  COUNT(*)
----------
         1

SQL> select sql_id, plan_hash_value, exact_matching_signature, sql_plan_baseline from v$sql where sql_text = 'select count(*) from soe.customers where customer_id = 19998';

SQL_ID                       PLAN_HASH_VALUE       EXACT_MATCHING_SIGNATURE SQL_PLAN_BASELINE
------------- ------------------------------ ------------------------------ ------------------------------
aa1m3a9b64srz                     1925551782           18089075686129158449

There is no plan baseline at the beginning to make it more clear and only baseline for test query will be loaded.

SQL> vari rc number
SQL> exec :rc:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'aa1m3a9b64srz',plan_hash_value=>1925551782)

PL/SQL procedure successfully completed.

SQL> print rc

                            RC
------------------------------
                             1

SQL> select sql_id, plan_hash_value, exact_matching_signature, sql_plan_baseline from v$sql where sql_text = 'select count(*) from soe.customers where customer_id = 19998';

SQL_ID                       PLAN_HASH_VALUE       EXACT_MATCHING_SIGNATURE SQL_PLAN_BASELINE
------------- ------------------------------ ------------------------------ ------------------------------
aa1m3a9b64srz                     1925551782           18089075686129158449

SQL> select count(*) from soe.customers where customer_id = 19998;

                      COUNT(*)
------------------------------
                             1

SQL> select sql_id, plan_hash_value, exact_matching_signature, sql_plan_baseline from v$sql where sql_text = 'select count(*) from soe.customers where customer_id = 19998';

SQL_ID                       PLAN_HASH_VALUE       EXACT_MATCHING_SIGNATURE SQL_PLAN_BASELINE
------------- ------------------------------ ------------------------------ ------------------------------
aa1m3a9b64srz                     1925551782           18089075686129158449

Plan baseline has been loaded but still not used by query - new cursor has to be opened.

SQL> alter system flush shared_pool;

System altered.

SQL> select count(*) from soe.customers where customer_id = 19998;

                      COUNT(*)
------------------------------
                             1

SQL> select sql_id, plan_hash_value, exact_matching_signature, sql_plan_baseline from v$sql where sql_text = 'select count(*) from soe.customers where customer_id = 19998';

SQL_ID                       PLAN_HASH_VALUE       EXACT_MATCHING_SIGNATURE SQL_PLAN_BASELINE
------------- ------------------------------ ------------------------------ ------------------------------
aa1m3a9b64srz                     1925551782           18089075686129158449 SQL_PLAN_gq2afgwdf4k9je2333459

After reload test query is using SQL plan baseline.

Now is a time to present first method of conversing SQL_HANLDE and PLAN_NAME into SQL_ID and PLAN_HASH_VALUE. This method is simple but it has one pitfall - query has to be in shared pool.

SQL> select sql_id, plan_hash_value, exact_matching_signature, sql_plan_baseline from v$sql where sql_text = 'select count(*) from soe.customers where customer_id = 19998';

SQL_ID                       PLAN_HASH_VALUE       EXACT_MATCHING_SIGNATURE SQL_PLAN_BASELINE
------------- ------------------------------ ------------------------------ ------------------------------
aa1m3a9b64srz                     1925551782           18089075686129158449 SQL_PLAN_gq2afgwdf4k9je2333459

SQL> select sql_handle from dba_sql_plan_baselines where plan_name = 'SQL_PLAN_gq2afgwdf4k9je2333459';

SQL_HANDLE
------------------------------
SQL_fb094e7f1ae24931

SQL> select sql_handle, plan_name from dba_sql_plan_baselines where sql_handle = 'SQL_fb094e7f1ae24931';

SQL_HANDLE                     PLAN_NAME
------------------------------ ------------------------------
SQL_fb094e7f1ae24931           SQL_PLAN_gq2afgwdf4k9je2333459

First we need to display SQL_PLAN_BASELINE from V$SQL which is related to PLAN_NAME column in DBA_SQL_PLAN_BASELINES. Second query shows SQL_HANDLE for that PLAN_NAME and last one will show all entries (it can be more plans for one query) for this particular SQL_HANDLE. At the end all required information are on screen. I have used this method for long time but I hit into problem several times as my query wasn't in v$sql anymore.

Second method is more complicated but it is working even when SQL is not in shared pool any more. DBMS_XPLAN has possibility to display plan for particular SQL_HANDLE and it will be our source of PLAN_HASH_VALUE

SQL> select * from table(dbms_xplan.DISPLAY_SQL_PLAN_BASELINE('SQL_fb094e7f1ae24931'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------
SQL handle: SQL_fb094e7f1ae24931
SQL text: select count(*) from soe.customers where customer_id = 19998
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_gq2afgwdf4k9je2333459         Plan id: 3795006553
Enabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD
--------------------------------------------------------------------------------

Plan hash value: 1925551782

-------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |              |     1 |     6 |     1   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE        |              |     1 |     6 |            |          |       |       |
|   2 |   PARTITION HASH SINGLE|              |     1 |     6 |     1   (0)| 00:00:01 |    16 |    16 |
|*  3 |    INDEX UNIQUE SCAN   | CUSTOMERS_PK |     1 |     6 |     1   (0)| 00:00:01 |    16 |    16 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("CUSTOMER_ID"=19998)

26 rows selected.

Unfortunately there is no still information about SQL_ID. But I realize that DBA_SQL_PLAN_BASELINES is keeping full text of SQL query so I should be able to calculate HASH_VALUE. Anyway this is what DBMS_XPLAN is using to generate PLAN_HASH_VALUE - optimizer is generating plan when DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE has been invoked. I tried to calculate SQL_ID using md5sum or perl but then I recall post from Tanel Poder blog how to calculate SQL_ID based on HASH_VALUE and other post by Slavik Markovich how to calculate SQL_ID using PL/SQL. Great Work Guys !
Rest was quite simple - I joined all parts together (PL/SQL loop calculating SQL_ID has been taken from Slavik script)

declare
v_sqlid VARCHAR2(13);
v_num number;
BEGIN
dbms_output.put_line('SQL_ID       '||' '|| 'PLAN_HASH_VALUE' || ' ' || 'SQL_HANDLE                    ' || ' ' || 'PLAN_NAME');
dbms_output.put_line('-------------'||' '|| '---------------' || ' ' || '------------------------------' || ' ' || '--------------------------------');
for a in (select sql_handle, plan_name, trim(substr(g.PLAN_TABLE_OUTPUT,instr(g.PLAN_TABLE_OUTPUT,':')+1)) plan_hash_value, sql_text
                 from (select t.*, c.sql_handle, c.plan_name, c.sql_text from dba_sql_plan_baselines c, table(dbms_xplan.DISPLAY_SQL_PLAN_BASELINE(c.sql_handle, c.plan_name)) t
                 where c.sql_handle = '&sql_handle') g
                 where PLAN_TABLE_OUTPUT like 'Plan hash value%') loop
    v_num := to_number(sys.UTL_RAW.reverse(sys.UTL_RAW.SUBSTR(sys.dbms_crypto.hash(src => UTL_I18N.string_to_raw(a.sql_text || chr(0),'AL32UTF8'), typ => 2),9,4)) || sys.UTL_RAW.reverse(sys.UTL_RAW.SUBSTR(sys.dbms_crypto.hash(src => UTL_I18N.string_to_raw(a.sql_text || chr(0),'AL32UTF8'), typ => 2),13,4)),RPAD('x', 16, 'x'));
    v_sqlid := '';
    FOR i IN 0 .. FLOOR(LN(v_num) / LN(32))
    LOOP
        v_sqlid := SUBSTR('0123456789abcdfghjkmnpqrstuvwxyz',FLOOR(MOD(v_num / POWER(32, i), 32)) + 1,1) || v_sqlid;
    END LOOP;
    dbms_output.put_line(v_sqlid ||' ' || rpad(a.plan_hash_value,15) || ' ' || rpad(a.sql_handle,30) ||  ' ' || rpad(a.plan_name,30));
end loop;
end;

How it work:

SQL> declare
  2  v_sqlid VARCHAR2(13);
  3  v_num number;
  4  BEGIN
dbms_output.put_line('SQL_ID       '||' '|| 'PLAN_HASH_VALUE' || ' ' || 'SQL_HANDLE                    ' || ' ' || 'PLAN_NAME');
  6      LOOP
dbms_output.put_line('-------------'||' '|| '---------------' || ' ' || '------------------------------' || ' ' || '--------------------------------');
for a in (select sql_handle, plan_name, trim(substr(g.PLAN_TABLE_OUTPUT,instr(g.PLAN_TABLE_OUTPUT,':')+1)) plan_hash_value, sql_text
                 from (select t.*, c.sql_handle, c.plan_name, c.sql_text from dba_sql_plan_baselines c, table(dbms_xplan.DISPLAY_SQL_PLAN_BASELINE(c.sql_handle, c.plan_name)) t
                 where c.sql_handle = '&sql_handle') g
                 where PLAN_TABLE_OUTPUT like 'Plan hash value%') loop
    v_num := to_number(sys.UTL_RAW.reverse(sys.UTL_RAW.SUBSTR(sys.dbms_crypto.hash(src => UTL_I18N.string_to_raw(a.sql_text || chr(0),'AL32UTF8'), typ => 2),9,4)) || sys.UTL_RAW.reverse(sys.UTL_RAW.SUBSTR(sys.dbms_crypto.hash(src => UTL_I18N.string_to_raw(a.sql_text || chr(0),'AL32UTF8'), typ => 2),13,4)),RPAD('x', 16, 'x'));
    v_sqlid := '';
    FOR i IN 0 .. FLOOR(LN(v_num) / LN(32))
    LOOP
        v_sqlid := SUBSTR('0123456789abcdfghjkmnpqrstuvwxyz',FLOOR(MOD(v_num / POWER(32, i), 32)) + 1,1) || v_sqlid;
    END LOOP;
    dbms_output.put_line(v_sqlid ||' ' || rpad(a.plan_hash_value,15) || ' ' || rpad(a.sql_handle,30) ||  ' ' || rpad(a.plan_name,30));
 18  end loop;
 19  end;
 20  /
Enter value for sql_handle: SQL_fb094e7f1ae24931
old   9:                  where c.sql_handle = '&sql_handle') g
new   9:                  where c.sql_handle = 'SQL_fb094e7f1ae24931') g
SQL_ID        PLAN_HASH_VALUE SQL_HANDLE                     PLAN_NAME
------------- --------------- ------------------------------ --------------------------------
aa1m3a9b64srz 1925551782      SQL_fb094e7f1ae24931           SQL_PLAN_gq2afgwdf4k9je2333459

PL/SQL procedure successfully completed.

Let me clean shared pool

SQL> alter system flush shared_pool;

System altered.

SQL> select sql_id, plan_hash_value, exact_matching_signature, sql_plan_baseline from v$sql where sql_text = 'select count(*) from soe.customers where customer_id = 19998';

no rows selected

Let's try one more time

SQL> declare
  2  v_sqlid VARCHAR2(13);
  3  v_num number;
  4  BEGIN
dbms_output.put_line('SQL_ID       '||' '|| 'PLAN_HASH_VALUE' || ' ' || 'SQL_HANDLE                    ' || ' ' || 'PLAN_NAME');
  6      LOOP
dbms_output.put_line('-------------'||' '|| '---------------' || ' ' || '------------------------------' || ' ' || '--------------------------------');
for a in (select sql_handle, plan_name, trim(substr(g.PLAN_TABLE_OUTPUT,instr(g.PLAN_TABLE_OUTPUT,':')+1)) plan_hash_value, sql_text
                 from (select t.*, c.sql_handle, c.plan_name, c.sql_text from dba_sql_plan_baselines c, table(dbms_xplan.DISPLAY_SQL_PLAN_BASELINE(c.sql_handle, c.plan_name)) t
                 where c.sql_handle = '&sql_handle') g
                 where PLAN_TABLE_OUTPUT like 'Plan hash value%') loop
    v_num := to_number(sys.UTL_RAW.reverse(sys.UTL_RAW.SUBSTR(sys.dbms_crypto.hash(src => UTL_I18N.string_to_raw(a.sql_text || chr(0),'AL32UTF8'), typ => 2),9,4)) || sys.UTL_RAW.reverse(sys.UTL_RAW.SUBSTR(sys.dbms_crypto.hash(src => UTL_I18N.string_to_raw(a.sql_text || chr(0),'AL32UTF8'), typ => 2),13,4)),RPAD('x', 16, 'x'));
    v_sqlid := '';
    FOR i IN 0 .. FLOOR(LN(v_num) / LN(32))
    LOOP
        v_sqlid := SUBSTR('0123456789abcdfghjkmnpqrstuvwxyz',FLOOR(MOD(v_num / POWER(32, i), 32)) + 1,1) || v_sqlid;
    END LOOP;
    dbms_output.put_line(v_sqlid ||' ' || rpad(a.plan_hash_value,15) || ' ' || rpad(a.sql_handle,30) ||  ' ' || rpad(a.plan_name,30));
 18  end loop;
 19  end;
 20  /
Enter value for sql_handle: SQL_fb094e7f1ae24931
old   9:                  where c.sql_handle = '&sql_handle') g
new   9:                  where c.sql_handle = 'SQL_fb094e7f1ae24931') g
SQL_ID        PLAN_HASH_VALUE SQL_HANDLE                     PLAN_NAME
------------- --------------- ------------------------------ --------------------------------
aa1m3a9b64srz 1925551782      SQL_fb094e7f1ae24931           SQL_PLAN_gq2afgwdf4k9je2333459

PL/SQL procedure successfully completed.

SQL> select sql_id, plan_hash_value, exact_matching_signature, sql_plan_baseline from v$sql where sql_text = 'select count(*) from soe.customers where customer_id = 19998';

no rows selected

SQL>

Hope it help work with SQL Plan Management feature.

regards,
Marcin

RMAN Database duplication without target connectivity can corrupt target database

2011-07-24T17:20:00.000+01:00

Latest version of Oracle 11g R2 has new Recovery Manager functionality - database duplication from backup without connectivity to target database. During tests I have found unexpected behavior of that functionality. I'm still not sure if it is bug or RMAN is doing it by design but if backup doesn't contain all files and SKIP TABLESPACE is not included, duplication process can overwrite target database files. When database is using ASM it can happen regardless of database state. Due to file locking mechanism this issue occur on file system only when target database is down.

All has been performed using Oracle version 11.2.0.2 on Red Hat 5.3 running target and auxiliary database on one server using file system. I have tested it with ASM as well but log files are gone together with my VM machine.

Target database is down, Backup has been taken without one tablespace,
no SKIP TABLESPACE clause.

Duplication script:

run {
set newname for tempefile 1 to '/opt/app/oracle/oradata/clon/temp01.dbf';
duplicate target database to clon
SPFILE
    PARAMETER_VALUE_CONVERT '/opt/app/oracle/admin/testdb/',
                            '/opt/app/oracle/admin/clon/'
    SET SGA_MAX_SIZE '500M'
    SET CONTROL_FILES '/opt/app/oracle/oradata/clon/control01.ctl','/opt/app/oracle/oradata/clon/control02.ctl'
    SET SGA_TARGET '450M'
    SET CLUSTER_DATABASE 'false'
    SET db_create_file_dest          '/opt/app/oracle/oradata/clon/'
    SET db_create_online_log_dest_1  '/opt/app/oracle/oradata/clon/'
    SET LOG_FILE_NAME_CONVERT  '/opt/app/oracle/oradata/testdb/','/opt/app/oracle/oradata/clon/'
    SET DB_FILE_NAME_CONVERT   '/opt/app/oracle/oradata/testdb/','/opt/app/oracle/oradata/clon/'
    BACKUP LOCATION '/opt/app/oracle/backup/';
}

Let's take a look on output - unimportant lines have been skipped

Recovery Manager: Release 11.2.0.2.0 - Production on Tue Jun 21 07:17:34 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to auxiliary database: CLON (not mounted)

Starting Duplicate Db at 21-JUN-11
...
Starting restore at 21-JUN-11
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=171 device type=DISK

channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/opt/app/oracle/oradata/clon/control01.ctl
output file name=/opt/app/oracle/oradata/clon/control02.ctl
Finished restore at 21-JUN-11

database mounted
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=171 device type=DISK

contents of Memory Script:
{
   set until scn  95159515;
   set newname for datafile  1 to 
 "/opt/app/oracle/oradata/clon/system-01.dbf";
   set newname for datafile  2 to 
 "/opt/app/oracle/oradata/clon/undo_t1-01.dbf";
   set newname for datafile  3 to 
 "/opt/app/oracle/oradata/clon/sysaux-01.dbf";
   set newname for datafile  4 to 
 "/opt/app/oracle/oradata/clon/administrator-01.dbf";
   set newname for datafile  5 to 
 "/opt/app/oracle/oradata/clon/administrator_idx-01.dbf";
   set newname for datafile  6 to 
 "/opt/app/oracle/oradata/clon/replication-01.dbf";
   set newname for datafile  7 to 
 "/opt/app/oracle/oradata/clon/replication_idx-01.dbf";
   set newname for datafile  8 to 
 "/opt/app/oracle/oradata/clon/soe.dbf";
   set newname for datafile  9 to 
 "/opt/app/oracle/oradata/clon/soeindex.dbf";
   set newname for datafile  10 to 
 "/opt/app/oracle/oradata/clon/users-01.dbf";
   restore
   clone database
   ;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME

So far so good - new name has been set for all files. What is next

file 9 is excluded from whole database backup
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /opt/app/oracle/oradata/clon/system-01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /opt/app/oracle/oradata/clon/undo_t1-01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /opt/app/oracle/oradata/clon/sysaux-01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /opt/app/oracle/oradata/clon/administrator-01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /opt/app/oracle/oradata/clon/administrator_idx-01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00006 to /opt/app/oracle/oradata/clon/replication-01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00007 to /opt/app/oracle/oradata/clon/replication_idx-01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00008 to /opt/app/oracle/oradata/clon/soe.dbf
channel ORA_AUX_DISK_1: restoring datafile 00010 to /opt/app/oracle/oradata/clon/users-01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /opt/app/oracle/backup/bck_13_testdb_1
channel ORA_AUX_DISK_1: piece handle=/opt/app/oracle/backup/bck_13_testdb_1 tag=TAG20110621T064346
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:06:36
failover to previous backup

Finished restore at 21-JUN-11

All files from backupset have been restored and Oracle realize that one file is missing. Keep in mind that new name has been prepared for all files.

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=10 STAMP=754384699 file name=/opt/app/oracle/oradata/clon/system-01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=11 STAMP=754384699 file name=/opt/app/oracle/oradata/clon/undo_t1-01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=12 STAMP=754384699 file name=/opt/app/oracle/oradata/clon/sysaux-01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=13 STAMP=754384699 file name=/opt/app/oracle/oradata/clon/administrator-01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=14 STAMP=754384699 file name=/opt/app/oracle/oradata/clon/administrator_idx-01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=15 STAMP=754384699 file name=/opt/app/oracle/oradata/clon/replication-01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=16 STAMP=754384699 file name=/opt/app/oracle/oradata/clon/replication_idx-01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=17 STAMP=754384699 file name=/opt/app/oracle/oradata/clon/soe.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=18 STAMP=754384700 file name=/opt/app/oracle/oradata/clon/users-01.dbf

This switch command is starting this issue - missing file hasn't been switch to new location

contents of Memory Script:
{
   set until scn  95159515;
   recover
   clone database
   delete archivelog
   ;
}
executing Memory Script
executing command: SET until clause
Starting recover at 21-JUN-11
using channel ORA_AUX_DISK_1
starting media recovery
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=1024
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=1025
channel ORA_AUX_DISK_1: reading from backup piece /opt/app/oracle/backup/bck_arch_16_testdb_1
channel ORA_AUX_DISK_1: piece handle=/opt/app/oracle/backup/bck_arch_16_testdb_1 tag=TAG20110621T070623
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
archived log file name=/opt/app/oracle/fra/CLON/archivelog/2011_06_21/o1_mf_1_1024_700kfxcx_.arc thread=1 sequence=1024
channel clone_default: deleting archived log(s)
archived log file name=/opt/app/oracle/fra/CLON/archivelog/2011_06_21/o1_mf_1_1024_700kfxcx_.arc RECID=2 STAMP=754384704
archived log file name=/opt/app/oracle/fra/CLON/archivelog/2011_06_21/o1_mf_1_1025_700kfxg6_.arc thread=1 sequence=1025
Oracle Error: 
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 9 was not restored from a sufficiently old backup 
ORA-01110: data file 9: '/opt/app/oracle/oradata/testdb/soeindex.dbf'

The worst part is here - RMAN was trying to apply logs to other database using entries from control file - the TARGET database entries. DBID at that time for target and clone database are still the same and target database was down so there are no locks. Nothing could prevent this process to overwrite target database file header.

What happen to target database ? The following error will be found in target alert.log file

Read of datafile '/opt/app/oracle/oradata/testdb/soeindex.dbf' (fno 9) header failed with ORA-01206
Rereading datafile 9 header failed with ORA-01206
Errors in file /opt/app/oracle/diag/rdbms/testdb/testdb/trace/testdb_dbw0_4984.trc:
ORA-01186: file 9 failed verification tests
ORA-01122: database file 9 failed verification check

File in target database has been corrupted during duplication process and it has to be restored from backup or dropped.

Target database is running, backup has been taken without one table space,
no SKIP TABLESPACE clause.
Duplication process is starting in similar way to previous scenario but instead of overwriting target file the following error is reporting during recovery process.

RMAN-11001: Oracle Error: 
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 9: '/opt/app/oracle/oradata/testdb/soeindex.dbf'
ORA-01157: cannot identify/lock data file 9 - see DBWR trace file
ORA-01110: data file 9: '/opt/app/oracle/oradata/testdb/soeindex.dbf'

This time duplication process failed but target database wasn't touched.

Target database is down, Backup has been taken without one tablespace,
SKIP TABLESPACE clause has been setup
This time backup still doesn't have all files but SKIP_TABLESPACE has been added to script. Recovery manager is working now without any issues

Recovery Manager: Release 11.2.0.2.0 - Production on Tue Jun 21 09:32:21 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to auxiliary database: CLON (not mounted)
RMAN> run {
2> set newname for tempfile 1 to '/opt/app/oracle/oradata/clon/temp01.dbf';
3>                                                             
4> duplicate target database to clon                           
5> SPFILE                                                      
6>     PARAMETER_VALUE_CONVERT '/opt/app/oracle/admin/testdb/',
7>                             '/opt/app/oracle/admin/clon/'   
8>     SET SGA_MAX_SIZE '500M'                                 
9>     SET CONTROL_FILES '/opt/app/oracle/oradata/clon/control01.ctl','/opt/app/oracle/oradata/clon/control02.ctl'
10>     SET SGA_TARGET '450M'        
11>     SET CLUSTER_DATABASE 'false' 
12>     SET db_create_file_dest          '/opt/app/oracle/oradata/clon/'
13>     SET db_create_online_log_dest_1  '/opt/app/oracle/oradata/clon/'
14>     SET LOG_FILE_NAME_CONVERT  '/opt/app/oracle/oradata/testdb/','/opt/app/oracle/oradata/clon/'
15>     SET DB_FILE_NAME_CONVERT   '/opt/app/oracle/oradata/testdb/','/opt/app/oracle/oradata/clon/'
16>     SKIP TABLESPACE 'SOEINDEX'                       
17>     UNDO TABLESPACE 'UNDO_T1'                                                                   
18>     BACKUP LOCATION '/opt/app/oracle/backup/';                                                  
19> }     
20> 
executing command: SET NEWNAME

Starting Duplicate Db at 21-JUN-11
...
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=171 device type=DISK
Not connected to TARGET or TARGET not open, cannot verify that subset of tablespaces is self-contained
Not connected to TARGET, cannot verify that set of tablespaces being duplicated does not have SYS objects
Datafile 9 skipped by request

contents of Memory Script:
{
   set until scn  95159515;
   set newname for datafile  1 to 
 "/opt/app/oracle/oradata/clon/system-01.dbf";
   set newname for datafile  2 to 
 "/opt/app/oracle/oradata/clon/undo_t1-01.dbf";
   set newname for datafile  3 to 
 "/opt/app/oracle/oradata/clon/sysaux-01.dbf";
   set newname for datafile  4 to 
 "/opt/app/oracle/oradata/clon/administrator-01.dbf";
   set newname for datafile  5 to 
 "/opt/app/oracle/oradata/clon/administrator_idx-01.dbf";
   set newname for datafile  6 to 
 "/opt/app/oracle/oradata/clon/replication-01.dbf";
   set newname for datafile  7 to 
 "/opt/app/oracle/oradata/clon/replication_idx-01.dbf";
   set newname for datafile  8 to 
 "/opt/app/oracle/oradata/clon/soe.dbf";
   set newname for datafile  10 to 
 "/opt/app/oracle/oradata/clon/users-01.dbf";
   restore
   clone database
   skip forever tablespace  "SOEINDEX"   ;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME

Starting restore at 21-JUN-11
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /opt/app/oracle/oradata/clon/system-01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /opt/app/oracle/oradata/clon/undo_t1-01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /opt/app/oracle/oradata/clon/sysaux-01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /opt/app/oracle/oradata/clon/administrator-01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /opt/app/oracle/oradata/clon/administrator_idx-01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00006 to /opt/app/oracle/oradata/clon/replication-01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00007 to /opt/app/oracle/oradata/clon/replication_idx-01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00008 to /opt/app/oracle/oradata/clon/soe.dbf
channel ORA_AUX_DISK_1: restoring datafile 00010 to /opt/app/oracle/oradata/clon/users-01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /opt/app/oracle/backup/bck_13_testdb_1
channel ORA_AUX_DISK_1: piece handle=/opt/app/oracle/backup/bck_13_testdb_1 tag=TAG20110621T064346
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:06:25
Finished restore at 21-JUN-11

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=10 STAMP=754393152 file name=/opt/app/oracle/oradata/clon/system-01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=11 STAMP=754393152 file name=/opt/app/oracle/oradata/clon/undo_t1-01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=12 STAMP=754393152 file name=/opt/app/oracle/oradata/clon/sysaux-01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=13 STAMP=754393152 file name=/opt/app/oracle/oradata/clon/administrator-01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=14 STAMP=754393152 file name=/opt/app/oracle/oradata/clon/administrator_idx-01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=15 STAMP=754393152 file name=/opt/app/oracle/oradata/clon/replication-01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=16 STAMP=754393152 file name=/opt/app/oracle/oradata/clon/replication_idx-01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=17 STAMP=754393152 file name=/opt/app/oracle/oradata/clon/soe.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=18 STAMP=754393152 file name=/opt/app/oracle/oradata/clon/users-01.dbf

contents of Memory Script:
{
   set until scn  95159515;
   recover
   clone database
   skip forever tablespace  "SOEINDEX"    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 21-JUN-11
using channel ORA_AUX_DISK_1

Executing: alter database datafile 9 offline drop
starting media recovery

channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=1024
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=1025
channel ORA_AUX_DISK_1: reading from backup piece /opt/app/oracle/backup/bck_arch_16_testdb_1
channel ORA_AUX_DISK_1: piece handle=/opt/app/oracle/backup/bck_arch_16_testdb_1 tag=TAG20110621T070623
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
archived log file name=/opt/app/oracle/fra/CLON/archivelog/2011_06_21/o1_mf_1_1024_700sp3lj_.arc thread=1 sequence=1024
channel clone_default: deleting archived log(s)
archived log file name=/opt/app/oracle/fra/CLON/archivelog/2011_06_21/o1_mf_1_1024_700sp3lj_.arc RECID=2 STAMP=754393158
archived log file name=/opt/app/oracle/fra/CLON/archivelog/2011_06_21/o1_mf_1_1025_700sp3p1_.arc thread=1 sequence=1025
channel clone_default: deleting archived log(s)
archived log file name=/opt/app/oracle/fra/CLON/archivelog/2011_06_21/o1_mf_1_1025_700sp3p1_.arc RECID=1 STAMP=754393155
media recovery complete, elapsed time: 00:00:04
Finished recover at 21-JUN-11
Oracle instance started

Additional step has been run before recover process - file from skipped table space has been dropped. In that case recovery process should touch it anymore from any location.

This exercise is showing that all new features should be tested in safe environment and in addition to that every operation on production database has to be well tested. I can't blame only Recovery Manager here but it would nice to have more checks in place (ex. if not all files are in backup just cancel duplication after control file has been restored). This is a perfect example of human error (at least someone created that scripts and run it) which can in complex environment.

regards,
Marcin

How to color (mark) SQL for AWR snapshots

2011-06-18T17:30:00.001+01:00

Are all SQL statements you’re looking for in your AWR repository ? If not, there is a simple way to ‘ask’ Oracle to include list of SQL_ID in every snapshot. All you need to do is color it. No, not using marker on the screen with Grid Control Performance tab but using DBMS_WORKLOAD_REPOSITORY. ADD_COLORED_SQL procedure. Since you mark sql_id as colored it will be included in every AWR snapshot even if it is not one of the top SQL's.
All colored sql_id together with creation date could be checked in the following view - DBA_HIST_COLORED_SQL. After all work there is a simple way to uncolor sql_id (make it white ? ). There is a procedure called DBMS_WORKLOAD_REPOSITORY.REMOVE_COLORED_SQL.

Simple example:

- script used to display SQL statistics from AWR repository

$ cat sql_stats.sql
col exetime format 99999999999.99
col begin_interval_time format a30
select begin_interval_time, PLAN_HASH_VALUE, ELAPSED_TIME_DELTA/EXECUTIONS_DELTA exetime, EXECUTIONS_DELTA, CPU_TIME_DELTA, ELAPSED_TIME_DELTA, DISK_READS_DELTA from dba_hist_sqlstat ss, dba_hist_snapshot s where s.snap_id = ss.snap_id and sql_id = '&SQL_ID' and s.snap_id > (select max(snap_id)-96 from dba_hist_snapshot) and EXECUTIONS_DELTA<>0 order by s.snap_id, sql_id;

Checking AWR repository for particular SQL_ID.

SQL> @sql_stats.sql
Enter value for sql_id: 6zfggtprazcvb
old   2: where s.snap_id = ss.snap_id and sql_id = '&SQL_ID' and s.snap_id > (select max(snap_id)-96 from dba_hist_snapshot) and EXECUTIONS_DELTA<>0 order by s.snap_id, sql_id
new   2: where s.snap_id = ss.snap_id and sql_id = '6zfggtprazcvb' and s.snap_id > (select max(snap_id)-96 from dba_hist_snapshot) and EXECUTIONS_DELTA<>0 order by s.snap_id, sql_id
BEGIN_INTERVAL_TIME            PLAN_HASH_VALUE         EXETIME EXECUTIONS_DELTA CPU_TIME_DELTA ELAPSED_TIME_DELTA DISK_READS_DELTA 
------------------------------ --------------- --------------- ---------------- -------------- ------------------ ---------------- 
16-JUN-11 04.00.43.639 PM            927619989     90627544.40               20       12879044         1812550888           125728 
16-JUN-11 04.15.51.312 PM            927619989    282777061.76              151      279247551         4.2699E+10            72065 
16-JUN-11 04.30.57.081 PM           1587981875      1308903.00                7         203969            9162321             1894 
16-JUN-11 09.46.00.195 PM           1587981875       889464.06               16         760882           14231425             2463 
17-JUN-11 12.45.49.596 AM           1587981875      1390759.44               16         778881           22252151             2973 
17-JUN-11 04.45.38.168 AM           1587981875       621587.94               16         682897            9945407             2320 
17-JUN-11 06.45.18.396 AM           1587981875       415950.50               16         692895            6655208             1774 
17-JUN-11 07.45.35.975 AM           1587981875       865986.07               15         642901           12989791             1228 

8 rows selected.

SQL has been executed in last hour a few times but was not included in AWR snapshot

SQL> select sample_time, session_id, sql_id, sql_plan_hash_value, sql_child_number  from v$active_session_history where sql_id = '6zfggtprazcvb' and sample_time > sysdate - 1/24 order by sample_time;

SAMPLE_TIME                   SESSION_ID SQL_ID        SQL_PLAN_HASH_VALUE SQL_CHILD_NUMBER
----------------------------- ---------- ------------- ------------------- ----------------
17-JUN-11 03.21.43.399 PM           1711 6zfggtprazcvb          1587981875               10
17-JUN-11 03.21.44.399 PM           1711 6zfggtprazcvb          1587981875               10
17-JUN-11 03.21.45.399 PM           1711 6zfggtprazcvb          1587981875               10
17-JUN-11 03.21.46.399 PM           1711 6zfggtprazcvb          1587981875               10
17-JUN-11 03.21.47.409 PM           1711 6zfggtprazcvb          1587981875               10
17-JUN-11 03.21.48.409 PM           1711 6zfggtprazcvb          1587981875               10
17-JUN-11 03.21.49.409 PM           1711 6zfggtprazcvb          1587981875               10
17-JUN-11 03.21.50.409 PM           1711 6zfggtprazcvb          1587981875               10
17-JUN-11 03.21.51.409 PM           1711 6zfggtprazcvb          1587981875               10
17-JUN-11 03.21.52.419 PM           1711 6zfggtprazcvb          1587981875               10
17-JUN-11 03.33.42.862 PM            474 6zfggtprazcvb          1587981875               10
17-JUN-11 03.33.43.862 PM            474 6zfggtprazcvb          1587981875               10
17-JUN-11 03.33.44.862 PM            474 6zfggtprazcvb          1587981875               10
17-JUN-11 03.49.37.819 PM           1724 6zfggtprazcvb          1587981875               10
17-JUN-11 03.49.38.819 PM           1724 6zfggtprazcvb          1587981875               10

SQL_ID has been colored (marked) using DBMS_WORKLOAD_REPOSITORY procedure

SQL> exec dbms_workload_repository.add_colored_sql('6zfggtprazcvb');

PL/SQL procedure successfully completed.

SQL> select * from DBA_HIST_COLORED_SQL;

      DBID SQL_ID        CREATE_TIME
---------- ------------- -------------------
1878812188 6zfggtprazcvb 2011-06-17 15:55:45

Now this SQL_ID has been included in AWR snapshot even if it has been executed once - see last row

SQL> @sql_stats.sql
Enter value for sql_id: 6zfggtprazcvb
old   2: where s.snap_id = ss.snap_id and sql_id = '&SQL_ID' and s.snap_id > (select max(snap_id)-96 from dba_hist_snapshot) and EXECUTIONS_DELTA<>0 order by s.snap_id, sql_id
new   2: where s.snap_id = ss.snap_id and sql_id = '6zfggtprazcvb' and s.snap_id > (select max(snap_id)-96 from dba_hist_snapshot) and EXECUTIONS_DELTA<>0 order by s.snap_id, sql_id

BEGIN_INTERVAL_TIME            PLAN_HASH_VALUE         EXETIME EXECUTIONS_DELTA CPU_TIME_DELTA ELAPSED_TIME_DELTA DISK_READS_DELTA 
------------------------------ --------------- --------------- ---------------- -------------- ------------------ ---------------- 
16-JUN-11 04.00.43.639 PM            927619989     90627544.40               20       12879044         1812550888           125728 
16-JUN-11 04.15.51.312 PM            927619989    282777061.76              151      279247551         4.2699E+10            72065 
16-JUN-11 04.30.57.081 PM           1587981875      1308903.00                7         203969            9162321             1894 
16-JUN-11 09.46.00.195 PM           1587981875       889464.06               16         760882           14231425             2463 
17-JUN-11 12.45.49.596 AM           1587981875      1390759.44               16         778881           22252151             2973 
17-JUN-11 04.45.38.168 AM           1587981875       621587.94               16         682897            9945407             2320 
17-JUN-11 06.45.18.396 AM           1587981875       415950.50               16         692895            6655208             1774 
17-JUN-11 07.45.35.975 AM           1587981875       865986.07               15         642901           12989791             1228 
17-JUN-11 03.45.11.552 PM           1587981875      1818946.00                1          12998            1818946              226

At the end of this example SQL_ID has been uncolored (unmarked).

SQL> exec dbms_workload_repository.remove_colored_sql('6zfggtprazcvb');

PL/SQL procedure successfully completed.

SQL> select * from DBA_HIST_COLORED_SQL;

no rows selected

SQL>

Rolling partition and global index - do we need to rebuild ?

2011-06-10T08:25:00.002+01:00

Some time ago (arrghhh - three month ago) I blogged about partition dropping when table has a global index - see. There was a example based on my tests. Now it is time to show some real figures. Both examples are sharing same scenario - about 2 years without touching indexes, 7 partitions are drop once per week. Partition drop is taking up to 6 - 7 hours. This time coalesce was not a option due to free up space requirement but it should help in future to avoid similar situations.

Example number 1

Table has around 562.000.000 rows and one partition has around 13.000.000 rows. Primary key index based on one number column growth to 140 GB and has been rebuilt to 13.9 GB. It is around 10 times less.

Example number 2 - bigger scale

Table has around 2.130.000.000 rows and one partition has around 45.000.000 rows.Primary key index based on one number column growth to 360 GB and has been rebuilt to 40 GB. It is 9 times less.

I put both examples here to show you that running coalesce for global indexes after partition drop can save you a lot of space and if done regularly prevent you from more resource consuming index rebuild.To be honest this is one of a few situations when I have seen any improvement (space wise this time) after index rebuild. But keep in mind that this is a special situation and for most of other cases you don't need to rebuild you indexes - if you think you need read Niall or Richard blog first.

regards,

Marcin

Pivot tables and Active Session History in Oracle 11g

2011-05-07T17:32:00.000+01:00

Oracle introduced a pivot tables in version 11g. If you want to use it there is still some limitation but as least it gave us a nice tool to display Active Session History information in various way. Please find one of possible example below - database CPU utilization for 5 minutes period with waits split into percent across wait classes

col "Administrative" format 999.99
col "Application" format 999.99
col "Cluster" format 999.99
col "Commit" format 999.99
col "Concurrency" format 999.99
col "Configuration" format 999.99
col "Network" format 999.99
col "Other" format 999.99
col "SYSTEM I/O" format 999.99
col "USER I/O" format 999.99
col "CPU" format 999.99
col Scheduler format 999.99
col Queueing format 999.99
col AAS format 999.99

select to_char(to_date(ts*300,'SSSSS'),'HH24:MI') sample_time, AAS,
nvl("'ON CPU'",0) "CPU",
nvl("'Scheduler'",0) Scheduler ,
nvl("'User I/O'",0) "USER I/O" ,
nvl("'System I/O'",0) "SYSTEM I/O" ,
nvl("'Concurrency'",0) Concurrency ,
nvl("'Application'",0) Application ,
nvl("'Commit'",0) Commit,
nvl("'Configuration'",0) Configuration,
nvl("'Administrative'",0) Administrative ,
nvl("'Network'",0) Network ,
nvl("'Queueing'",0) Queueing ,
nvl("'Cluster'",0) "CLUSTER",
nvl("'Other'",0) Other 
from (
 select trunc(to_char(sample_time, 'SSSSS')/300) ts,
 decode(session_state,'WAITING',wait_class,'ON CPU') wait_class, 
 count(*) cnt, 
 sum(count(*)) over (partition by trunc(to_char(sample_time, 'SSSSS')/300)) sum, 
 sum(count(*)) over (partition by trunc(to_char(sample_time, 'SSSSS')/300))/300 AAS 
 from v$active_session_history 
 group by trunc(to_char(sample_time, 'SSSSS')/300),
          decode(session_state,'WAITING',wait_class,'ON CPU') 
 order by 1
) 
pivot (
   sum(round(cnt/sum*100,2)) 
   for (wait_class) in    
   ('Administrative','Application','Cluster','Commit','Concurrency',
    'Configuration','Network','Other','Queueing','Scheduler','System I/O',
    'User I/O','ON CPU'
   )
) order by 1;

SAMPLE_TIME          AAS     CPU SCHEDULER USER I/O SYSTEM I/O CONCURRENCY APPLICATION  COMMIT CONFIGURATION ADMINISTRATIVE NETWORK QUEUEING CLUSTER   OTHER
---------------- ------- ------- --------- -------- ---------- ----------- ----------- ------- ------------- -------------- ------- -------- ------- -------
2011-05-06 14:58    3.38    6.40       .00    38.42        .49         .00         .00     .00           .00          54.68     .00      .00     .00     .00
2011-05-06 14:59    3.67    4.55       .00    44.09        .00         .00         .00     .00           .00          51.36     .00      .00     .00     .00
2011-05-06 15:00    2.98    5.59       .00    29.05        .56         .00         .00     .00           .00          64.80     .00      .00     .00     .00
2011-05-06 15:01    2.53    7.89       .00    16.45        .00         .00         .00     .00           .00          75.66     .00      .00     .00     .00
2011-05-06 15:02    2.62    7.64       .00    21.66        .00         .00         .00     .00           .00          70.70     .00      .00     .00     .00
2011-05-06 15:03    2.32    9.35       .00    11.51        .00         .00         .00     .00           .00          79.14     .00      .00     .00     .00
2011-05-06 15:04    2.37    6.34       .00    30.99        .70         .00         .00     .00           .00          61.97     .00      .00     .00     .00
2011-05-06 15:05    2.93   10.80       .00     6.25        .00         .00         .00     .00           .00          82.95     .00      .00     .00     .00
2011-05-06 15:06    3.28   12.69       .00    14.72       1.02         .00         .00     .00           .00          71.07     .00      .00     .00     .51
2011-05-06 15:07    4.57   10.58       .00     8.39        .00         .00         .00     .00           .00          81.02     .00      .00     .00     .00
2011-05-06 15:08    4.72    5.30       .00    13.78        .00         .00         .00     .00           .00          80.92     .00      .00     .00     .00
2011-05-06 15:09    4.80    6.25       .00    15.28        .00         .00         .00     .00           .00          78.47     .00      .00     .00     .00
2011-05-06 15:10    4.42    8.30       .00     7.92        .00         .00         .00     .00           .00          83.77     .00      .00     .00     .00
2011-05-06 15:11    4.65    6.45       .00    12.19        .00         .00         .00     .00           .00          81.36     .00      .00     .00     .00
2011-05-06 15:12    4.48    7.06       .00     9.67        .00         .00         .00     .00           .00          83.27     .00      .00     .00     .00
2011-05-06 15:13    4.58    9.09       .00    13.09        .00         .00         .00     .00           .00          77.82     .00      .00     .00     .00
2011-05-06 15:14    4.40   10.61       .00     7.20        .00         .00         .00     .00           .00          82.20     .00      .00     .00     .00
2011-05-06 15:15    4.00    7.92       .00      .00        .00         .00         .00     .00           .00          92.08     .00      .00     .00     .00
2011-05-06 15:16    4.33    8.46       .00     6.15        .00         .00         .00     .00           .00          85.00     .38      .00     .00     .00
2011-05-06 15:17    4.60    5.07       .00     9.42        .00         .00         .00     .00           .00          85.51     .00      .00     .00     .00
2011-05-06 15:18    4.80    4.86       .00    15.97        .00         .00         .00     .00           .00          79.17     .00      .00     .00     .00
2011-05-06 15:19    4.27    5.08       .00     5.08        .00         .00         .00     .00           .00          89.84     .00      .00     .00     .00
2011-05-06 15:20    4.00    2.92       .00      .00        .00         .00         .00     .00           .00          97.08     .00      .00     .00     .00
2011-05-06 15:21    4.37    3.05       .00     8.02        .00         .00         .00     .00           .00          88.93     .00      .00     .00     .00
2011-05-06 15:22    7.23   17.05       .00    32.03        .00         .00         .00     .00           .00          50.92     .00      .00     .00     .00
2011-05-06 15:23     .55    6.06       .00     3.03        .00         .00         .00     .00           .00          90.91     .00      .00     .00     .00

That query can be easily change by adding filtering condition for sessions or sample time. Unfortunately list of values for pivot table can't be generated dynamically without XML output so it is required to hard coded number of wait class into this code.

regards,
Marcin

MySQLPerformance Schema - is this introduction of Oracle event based monitoring in MySQL ?

2011-04-27T20:52:00.000+01:00

Today I read presentation about MySQL performance MySQL Essentials part 8 recommended by @surachart and I learned that Performance Schema has been introduced - see documentation.

After basic review it looks like introduction of well know Oracle wait interface and statistics. I'm going to download MySQL version 5.5 shortly and check it out. I have a lot of concerns what happen to MySQL after Oracle acquisition but at least this is step in good direction.

regards,

Marcin

SASH visualization using Excel

2011-04-03T23:01:00.000+01:00

OraSASH is a free implementation of Oracle Active Session History mechanism but it is providing only raw tables with data. To help other people use that functionality and inspired by Charles Hooper blog I have created a Excel based simple GUI interface for SASH. It is providing historical and real time view based on repository. It has been tested on Windows 7 64 bit with 32 bit Excel 2007. To make it running it is necessary to install and configure Oracle ODAC 11.2.0.2. Zip file can be downloaded from SourceForge repository. Please be aware that this is alpha stage of this Excel sheet and it can contain errors. Read only access is needed to OraSASH repository. Macros have to be enabled in Excel as all functionality is based on Visual Basic.

Setup sheet

Fill out repository user and tns alias name on Setup sheet and click Start button.

Historic data should be read from repository for whole available period of time and available instances list will be fill out as well. When all data will be collected active sheet will be switched to Graph sheet.
Graph sheet

There are two charts on Graph sheet – upper one is displaying Average Active Session - for AAS concept see John Beresniewicz presentation or Kyle Hailey blog.

Chart on the right side is displaying SQL activity for selected period of time. Percent of activity is calculated against number of all samples from that time. This same activity percent can be seen on OEM performance tab. SQL details like query text and plan will be displayed in Detail SQL view when chart bar for appropriate SQL ID will be clicked.

Historic view

To change period displayed on screen change start and stop date and then click Refresh Historic View button.

Real time view

To see current database activity switch radio button to Real Time refresh. Refresh rate can be change on this same sheet in Refresh Rate field. It take one full refresh cycle to pick up a refresh new value.

Detail SQLsheet

Separate sheet for every SQL query. Sheet name is SQL_ID and detail available information about query text and plan will be displayed there. In addition to that detail information about wait event and CPU utilization will be displayed as well.

If you have any comments or questions please feel free to contact me.
regards,
Marcin

Oracle XE 11g - beta release

2011-04-02T17:14:00.000+01:00

Hello,

Good news Oracle XE 11g has been released. Right now it is a beta version but we can expect production release soon. I was waiting for that release as my idea was to use it a repository database for S-ASH. I'm going to check if all new 11g functionality like PIVOT functions will be working fine.

regards,
Marcin

New version of S-ASH

2011-04-01T20:23:00.000+01:00

Hello,

I'm pleased to inform you that new version of Simulate ASH has been published.
You can download it from ASH Masters website or from Source Forge project site.

If you have any questions or ideas how to improve it please feel free to contact me

regards,
Marcin

Rolling partition and global index coalesce

2011-03-14T22:54:00.000Z

Resolving one of performance issues I figure out that primary key index is much bigger that it should be. Most of database activity is inserting data into partitioning table using some date field as a partitioning key. Rows can be inserted to every partition not only to most recent one. Daily job is dropping one oldest partition per day. Unfortunately primary key index is not partitioned and is maintained during partition drop as a global index. Correlation of these two things is a root cause of index size explosion.

Short test case below:

script test.sql has been used to check index size, number of rows in table and partition drop
in second session simple script inserting data into random partition was running continuously

Table definition:

  CREATE TABLE LOGS
   (    "LOG_ID" NUMBER NOT NULL ENABLE,
        "DELDATE" DATE NOT NULL ENABLE,
        "FILL" VARCHAR2(100),
         CONSTRAINT "LOGS_PK" PRIMARY KEY ("LOG_ID")
   )
  TABLESPACE "USERS"
  PARTITION BY RANGE ("DELDATE")
 (PARTITION "PARTITION1"  VALUES LESS THAN (TO_DATE(' 2011-02-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  PARTITION "PARTITION2"  VALUES LESS THAN (TO_DATE(' 2011-03-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  PARTITION "PARTITION3"  VALUES LESS THAN (TO_DATE(' 2011-04-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  PARTITION "PARTITION4"  VALUES LESS THAN (TO_DATE(' 2011-05-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  PARTITION "PARTITION5"  VALUES LESS THAN (TO_DATE(' 2011-06-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  PARTITION "PARTITION6"  VALUES LESS THAN (TO_DATE(' 2011-07-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  PARTITION "PARTITION7"  VALUES LESS THAN (TO_DATE(' 2011-08-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  PARTITION "PARTITION8"  VALUES LESS THAN (TO_DATE(' 2011-09-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  PARTITION "PARTITION9"  VALUES LESS THAN (TO_DATE(' 2011-10-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  PARTITION "PARTITION10"  VALUES LESS THAN (TO_DATE(' 2011-11-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  PARTITION "PARTITION11"  VALUES LESS THAN (TO_DATE(' 2011-12-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
 );

Source of test.sql script

prompt number of rows in table
 select count(*) from pioro.logs;
 
 prompt LOGS_PK index size
 select sum(bytes)/1024/1024 from dba_extents where segment_name = 'LOGS_PK';

 prompt calculating stats
 exec dbms_stats.gather_table_stats('PIORO','LOGS',cascade=>true, estimate_percent=>null);
 
 prompt rows/block without update global indexes
 select blevel, leaf_blocks, distinct_keys, NUM_ROWS, NUM_ROWS/leaf_blocks "rows/block" from dba_indexes 
where owner='PIORO' and table_name='LOGS' and leaf_blocks <> 0;

 prompt number of rows in table partition
 select count(*) from pioro.logs partition(&partname);
 set timing on 

 alter table pioro.logs drop partition &partname update global indexes;

Inserts script running in loop looks like

insert into logs select seq_log_id.nextval, to_date('2011-'|| trunc(dbms_random.value(2,9)) ||'-15','yyyy-mm-dd'),
lpad('x',70,'x') from all_source a, all_source b where rownum <= 3000000;
commit;

Here is a output from test.sql for first partition drop

SQL> @test
number of rows in table COUNT(*)                                                                                                 
----------                                                                                                          
  12000000                                                                                                          

Elapsed: 00:00:13.74
LOGS_PK index size

SUM(BYTES)/1024/1024                                                                                                
--------------------                                                                
242           

Elapsed: 00:00:00.51
calculating stats

PL/SQL procedure successfully completed.

Elapsed: 00:01:11.28
rows/block without update global indexes

    BLEVEL LEAF_BLOCKS DISTINCT_KEYS   NUM_ROWS rows/block                                                          
---------- ----------- ------------- ---------- ----------                                                          
         2       30000      12000000   12000000        400         

Elapsed: 00:00:00.03
number of rows in table partition
Enter value for partname: partition1
old   1:  select count(*) from pioro.logs partition(&partname)
new   1:  select count(*) from pioro.logs partition(partition1)

  COUNT(*)                                                                                                          
----------                                                                                                          
   3001361                                                                              
Elapsed: 00:00:00.35
Enter value for partname: partition1
old   1:  alter table pioro.logs drop partition &partname update global indexes
new   1:  alter table pioro.logs drop partition partition1 update global indexes

Table altered.

Elapsed: 00:00:55.37

As you can see before first drop average number of rows in one index leaf for primary key was 400. ( 8 kB database block ) Output from next run looks like:

SQL> @test
number of rows in table

  COUNT(*)   
----------   
  11998639   

Elapsed: 00:00:09.59
LOGS_PK index size

SUM(BYTES)/1024/1024  
--------------------  
                 296  

Elapsed: 00:00:01.12
calculating stats

PL/SQL procedure successfully completed.

Elapsed: 00:01:13.16
rows/block without update global indexes

    BLEVEL LEAF_BLOCKS DISTINCT_KEYS   NUM_ROWS rows/block    
---------- ----------- ------------- ---------- ----------    
         2       37500      11998639   11998639 319.963707    

Elapsed: 00:00:00.05
number of rows in table partition
Enter value for partname: partition2
old   1:  select count(*) from pioro.logs partition(&partname)
new   1:  select count(*) from pioro.logs partition(partition2)

  COUNT(*)                  
----------                  
   3749280                  

Elapsed: 00:00:00.43
Enter value for partname: partition2
old   1:  alter table pioro.logs drop partition &partname update global indexes
new   1:  alter table pioro.logs drop partition partition2 update global indexes

Table altered.

Elapsed: 00:01:22.85

Numbers of rows in one leaf block drop from 400 to 320 rows per block.After a few next runs it looks like:

SQL> @test
number of rows in table

  COUNT(*)                                                                                                          
----------                                                                                                          
   9748583                                                                                                          

Elapsed: 00:00:17.67
LOGS_PK index size

SUM(BYTES)/1024/1024      
--------------------      
                 414      

Elapsed: 00:00:00.85
calculating stats

PL/SQL procedure successfully completed.

Elapsed: 00:01:11.78
rows/block without update global indexes

    BLEVEL LEAF_BLOCKS DISTINCT_KEYS   NUM_ROWS rows/block        
---------- ----------- ------------- ---------- ----------        
         2       52500       9748583    9748583 185.687295        

Elapsed: 00:00:00.01

Now is really bad - only 186 rows in one leaf block and size of index segment is now 414 MB.It will have huge impact on database performance as much more index blocks have to be read during index range scans.Solution is quite simple and can be done online without huge overhead.It is a index coalesce - this command will consolidate a free space in existing index and provide more free blocks for particular index segment. See example:

SQL> alter index pioro.LOGS_PK coalesce;

Index altered.

Elapsed: 00:03:57.12
SQL> prompt number of rows in table partition
number of rows in table partition
SQL> select count(*) from pioro.logs partition(&partname);
Enter value for partname: partition4
old   1: select count(*) from pioro.logs partition(&partname)
new   1: select count(*) from pioro.logs partition(partition4)

  COUNT(*)                    
----------                    
   5248055                    

Elapsed: 00:00:10.47
SQL> alter table pioro.logs drop partition &partname update global indexes;
Enter value for partname: partition4
old   1: alter table pioro.logs drop partition &partname update global indexes
new   1: alter table pioro.logs drop partition partition4 update global indexes

Table altered.

Elapsed: 00:02:09.65
SQL> @test
number of rows in table

  COUNT(*)                          
----------                          
   7500528                          

Elapsed: 00:00:15.78
LOGS_PK index size

SUM(BYTES)/1024/1024      
--------------------      
                 414      

Elapsed: 00:00:01.47
calculating stats

PL/SQL procedure successfully completed.

Elapsed: 00:00:46.30
rows/block without update global indexes

    BLEVEL LEAF_BLOCKS DISTINCT_KEYS   NUM_ROWS rows/block      
---------- ----------- ------------- ---------- ----------      
         2       26682       7500528    7500528 281.108163      

Elapsed: 00:00:00.08

After index coalesce we can see that Oracle is able to allocate space from free block and size of index remain stable and number of rows in one leaf block is now increasing.

This post has been created based on test data and test scenario but after sucesfull implementation in life systems I will blog about real numbers from production databases.

Moving standby database or Oracle know limitation story

2011-01-12T03:01:00.000Z

It has been a long time since I last blogged. There were some changes in my professional live and now I'm again more close with 24/7 databases.

I really like Oracle approach to know bugs or limitation and dealing with that from version to version. I have hit one of those recently and I want to share it with all my readers. This know limitation/bug appear first time in 10g and still exist in 11g R1.

I had a simple task to do - move standby database managed by DataGuard to new host. According to some bandwidth limitation I couldn't copy files from old host but I need to copy it from production box. In 11g it is not a problem we can use duplication from active database so we don't need to wait until backup solution will be setup on new box. This is what I have chosen - RMAN and duplicate from active database.

Here is a command line used by me:

$ rman target sys@production auxiliary sys@standby
RMAN> duplicate target database for standby from active database dorecover NOFILENAMECHECK;

And here is my point - I have specified NOFILENAMECHECK so Oracle allow me to keep same disk layout on production and standby database and will no claim that datafiles have the same names on both boxes. So far so good - duplication process has been finished without any issues.

I have started standby database and there is where I my problem begun.

Here are an example entry from alert.log

Errors in file /logs/diag/rdbms/dbs0_a/dbs0/trace/dbs0_mrp0_23247.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/redo1/dbs0/redo11.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /logs/diag/rdbms/dbs0_a/dbs0/trace/dbs0_mrp0_23247.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/redo1/dbs0/redo11.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Clearing online redo logfile 1 /redo1/dbs0/redo11.log
Clearing online log 1 of thread 1 sequence number 2357
Errors in file /logs/diag/rdbms/dbs0_a/dbs0/trace/dbs0_mrp0_23247.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/redo1/dbs0/redo11.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /logs/diag/rdbms/dbs0_a/dbs0/trace/dbs0_mrp0_23247.trc:
ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 1 thread 1: '/redo1/dbs0/redo11.log'
Clearing online redo logfile 1 complete

Oracle is trying to open redo log file which doesn't exist on standby machine - this is a first error and of course expected one. Than Oracle is trying to clear (in that case recreate) redo log and it failed with error ORA-19527 and of course at the end it is informing DBA that clearing online redo log 1 has been completed.

Unfortunately this files has not been created and this error appear every time standby database has been restarted or recovery process has been restarted. The main issue is ORA-19527 "physical standby redo log must be renamed" even if in that case this is not a standby redo but online redo log. Simplest solution like drop and add a new groups of redo logs failed as Oracle is claiming that all those unexisting files will be used to database recovery. Hmmm - i like that. This is what Oracle support answer for that problem (783113.1) "These are not bug's, they are known limitations" and proposed solution is to set LOG_FILE_NAME_CONVERT to convert old redo log name to new one - even if both are same. As a part of explanation other note (352879.1) has following sentence "It is the equivalent of asking - Are you sure you want the logs to be called this....".

I can agree with that and this can be some protection to prevent overwriting of existing redo logs but why this same error happen if

I have specified NOFILENAME checking (ok I could do it by mistake)
There were not online redo logs files nor standby logs files on my new box so I can't imagine how Oracle could overwritten one of those files

This is KNOW limitation so why RMAN is not forcing user to specify LOG_FILE_NAME_CONVERT or not checking if this parameter is set in new instance ?

regards,

Marcin

Unsafe deinstall using Oracle Univeral Installer.

2010-11-02T20:17:00.000Z

Are Oracle Homes totally independent from OUI point of view? This was my impression until yesterday when I hit Oracle OUI de-installer bug in Oracle Real Application Cluster environment. I did 3 simple steps:

I have installed new Oracle Home into new directory on both nodes
There was timing issue between servers so I need to remove new Oracle Home using OUI de-install functionality.
I have corrected timing issue and installed new Oracle Home once again.

All old homes have been used by working Oracle ClusterWare, ASM and database and I believed that there should not be any issues. Unfortunately a few minutes after that work I have got first error message but only for new session:

ORA-27504: IPC error creating OSD context 
ORA-27300: OS system dependent operation: IPC init failed with status:65 
ORA-27301: OS failure message: Package not installed 
ORA-27302: failure occurred at: skgxpcini 
ORA-27303: additional information: libskgxpd.so called

I was surprised and I have checked $ORACLE_HOME/lib/ directory and I have found root cause - libskgxpd.so has very recent modification date and it was date of my installation. I have checked all OUI logs immediately looking for that file and in deinstallation log I found that entry:

INFO: The output of this make operation is also available at: '/opt/app/oracle/product/10.2.0/Db_new/install/make.log'
INFO:
INFO: Start output from spawned process:
INFO: ----------------------------------
INFO:
INFO: rm -f /opt/app/oracle/product/10.2.0/Db_1/lib/libskgxp10.so
INFO: cp /opt/app/oracle/product/10.2.0/Db_1/lib//libskgxpd.so /opt/app/oracle/product/10.2.0/Db_1/lib/libskgxp10.so

But in that same log I have found following message at the end:

INFO: Current Inventory:
        Oracle Home: OraCrs10g_home
                This Oracle Home has not been changed in this session.
        Oracle Home: ASM_HOME
                This Oracle Home has not been changed in this session.
        Oracle Home: RDBMS_HOME
                This Oracle Home has not been changed in this session.
INFO: This deinstallation was successful

So OUI replaced library libskgxp10.so in existing old home (RDBMS_HOME) and at the end of work stated that this home has not been change. There are bugs related to that : 7006848, 5474623.

If you ever hit that error – there is two possible solutions:

Copy backup libskgxp10.so from untouched home if you have home with this same patch level (in my case it was ASM)
Rebuild Oracle using the following steps – MOS note 814896.1-
- cd $ORACLE_HOME/rdbms/lib
  rename the original library (if exists)
  mv libskgxp10.so libskgxp10.so.old
- Relink to configure UDP for IPC
  make -f ins_rdbms.mk rac_on ipc_udp ioracle
- Check whether the library exists
  ls -l $ORACLE_HOME/lib/libskgxp10.so
- startup the instance

And good advise at the end – your testing environment should be same as production one. If you are going to do some work on RAC test it on your test RAC too.

Oracle patchest 11.2.0.2

2010-09-12T20:37:00.000+01:00

Hi,

Some people tweet about new Oracle patch set 11.2.0.2. I decided to check it too but I can't find it on MOS any more. Not only me as Tim Hall (@oraclebase) tweet about that situation too.

Anyway there is one important note on MOS - 1189783.1 - starting with patch set 11.2.0.2 new Oracle patch sets will be full installations of Oracle Software and patching out-of-place will be preferred one or even mandatory one in case of Grid Infrastructure.

I hope this note doesn't disappear from MOS like patch set and will be still valid after a few days.

Now I'm waiting for 11.2.0.2 to appear once again.

regards,

Marcin

New version of Simulating ASH

2010-09-08T21:56:00.000+01:00

Hello,

I have mentioned some time ago about using Simulating ASH to solve performance issue in Oracle 9i database.

According to some restrictions I have to customize it a little bit and than I agreed with Kyle Hailey that I will publish it as a new version of S-ASH. A new project has been created on SourceForge to keep a repository and this new code and installation instruction can be find here.

Please feel free to post any comments or remarks. I have some idea about potential enhancement of that tool but I'm open for any new too.

regards,

Marcin

Column names and typing errors

2010-08-04T21:40:00.000+01:00

Last few days I have hit myself into Oracle "feature" which was one of my favorite when I was helping developers solve strange Oracle issues. I recall one Oracle ANSI SQL syntax issue when Oracle didn't recognize that column names are duplicated in different tables and use a random one in output. This is why I always told people use column name with table name to avoid confusion. Anyway now I decided to blog about that and show why table.column_name syntax is so important.

Let's go through an example - In this syntax equal ?

select name1 from T1 where id1 in (select id1 from T2 where name2='NAME2');

select T1.name1 from T1 where T1.id1 in (select T2.id1 from T2 where name2='NAME2');

Looks OK isn't it ? But it really depend on how tables are defined.

SQL> desc T1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID1                                                NUMBER
 NAME1                                              VARCHAR2(100)

SQL> desc T2
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID2                                                NUMBER
 NAME2                                              VARCHAR2(100)

SQL> select id1, name1 from T1;

       ID1 NAME1
---------- -------------------------------------------------------
         1 NAME1

SQL> select id2, name2 from T2;

       ID2 NAME2
---------- -------------------------------------------------------
         2 NAME2

Now we can see that there is a typo in second query as there is no ID1 column in T2 table. But lets try to execute both. First we start a query with tablename.columnname syntax :

SQL> select T1.name1 from T1 where T1.id1 in (select T2.id1 from T2 where name2='NAME2');
select T1.name1 from T1 where T1.id1 in (select T2.id1 from T2 where name2='NAME2')
                                                *
ERROR at line 1:
ORA-00904: "T2"."ID1": invalid identifier

Looks OK Oracle found our error and query has not been executed. What about first example ?

SQL> select name1 from T1 where id1 in (select id1 from T2 where name2='NAME2');

NAME1
--------------------------------------------------------------------------------
NAME1

SQL>

It return one row and Oracle didn't find our typing error. Even worse IN filter is true for every T1 row but it should not - if you check rows in T1 and T2 where is no common value between ID1 and ID2.
Why ? We are going to check if ID1 from T1 is included in set of ID1 from T2. But there is no ID1 in T2 so Oracle is using ID1 from T1 as in every correlated subquery. What this subquery return ? It will return ID1 from table T1 for every row in table T2 where name is equal to 'NAME2'. This is not what was excepted.

But is this subquery can be executed as separate query ? Of course not

SQL> select id1 from T2 where name2='NAME2';
select id1 from T2 where name2='NAME2'
       *
ERROR at line 1:
ORA-00904: "ID1": invalid identifier

I'm not sure if that problem can be classified as bug but at least is good to know how Oracle is using columns names and use tablename.columnname syntax to avoid confusion and increase code quality. Especially if there are tables with long column names and there is one letter difference between them.

regards,
Marcin

Process hung on library cache lock

2010-07-29T20:55:00.000+01:00

Customer reported a system slowdown/process hung during small and well defined period of time and it was related to one specific process only. Standard Oracle 9i performance analyze tool - Statspack didn't show anything unusual in that time but shapshots were taking very 30 min and this issue took about 5 minutes.

As we could not replicate issue I thought about SASH (a free simulator of Oracle Active Session History) and I have installed it and configured. From that time I was able to gather and keep information about active sessions.

When next time I got detailed information about similar issue I was able to use collected data and found a problem. After investigation I figured out that process used for that business functionality stuck on “library cache lock” event. When I compared my findings from SASH with Statspack I realize why I didn't spotted it before - "library cache lock" was about 1.8 % of all locks between statspack snapshots. From overall database review there was no problem but from process (and business) perspective that lock hung important process for almost 5 minutes.

Below I described all major steps of my investigation and what was a root cause.
Let's start with SASH output (I just left 3 lines but there was many more similar lines):

SQL> select sample_time,program, session_id, session_state, event, seq#, sql_id, '0' || trim(to_char(p1,'XXXXXXXXXXXXXXXXX')) "p1raw", p3
  2  from v$active_session_history
  3  where 
  4  sample_time >= to_date('18-7-2010 13:50:10','dd-mm-yyyy hh24:mi:ss')
  5  and sample_time <= to_date('18-7-2010 13:54:58','dd-mm-yyyy hh24:mi:ss')
  6  and event like 'library%'
  7  order by sample_time desc
  8  /

SAMPLE_TI PROGRAM        SESSION_ID SESSION_STATE EVENT              SEQ#  SQL_ID     p1raw             P3
--------- -------------- ---------- ------------- ------------------ ----- -------    -----------       ---
18-JUL-10 importantp.exe        472 WAITING       library cache lock  214  1548901218 07000002AC789A98  210
18-JUL-10 otherprogr.exe        436 WAITING       library cache lock    8  2855865705 07000002AC789A98  210
18-JUL-10                       375 WAITING       library cache lock   33   261624711 07000002AC789A98  310

First idea was about parsing problem but when I took a look on SQL it looked OK but it was just entry point to PL/SQL procedure. Next thing was to check event parameters and there was first surprise. According to Oracle documentation and Metalink note 34579.1 that event has following parameters:

p1 is an address of required object - in my case 07000002AC789A98
p3 is a lock type and namespace type (lock mode * 100 + namespace) - in my case 310 or 210

In Oracle definitions (metalink or decode in v$librarycache) namespace number 10 is not defined. See whole list below
•    0 SQL Area
•    1 Table / Procedure / Function / Package Header
•    2 Package Body
•    3 Trigger
•    4 Index
•    5 Cluster
•    6 Object
•    7 Pipe
•    13 Java Source
•    14 Java Resource
•    32 Java Data

So what object it is ? P1 had a address and X$KGLOB has a list of all objects (I checked that table next day but there was no restart and it was a chance that there is still that same object in shared pool as session was running 24/7):

SQL> select kglnaown "Owner", kglnaobj "Object" from x$kglob where kglhdadr='07000002AC789A98'; 

Owner  Object
------ --------
SOFT   SNDQ

What type of objects is it ?

SQL> select object_type, object_name from dba_objects where owner='SOFT' and object_name = 'SNDQ';

OBJECT_TYPE OBJECT_NAME
----------- -----------
QUEUE       SNDQ

Bingo - object was a queue there was a dequeue in that PL/SQL code. My process called importantp.exe was waiting for lock on queue. Let's check lock type:

2 - Share mode
3 - Exclusive mode

For importantp.exe lock mode in p3 parameter was 210 which mean wait for shared lock. But queuing and dequeueing operations are running with shared locks so reason for waiting had to be related to none of them. I found it in last line of my listing there was another processes waiting for exclusive lock on that same queue. Using SQL_ID (261624711) I discovered that there was another PL/SQL piece of code and add_subscriber function was called inside.

That was a explanation of importantp.exe hungs - as dequeue function was called after add_subscriber function it had to wait until DDL command was finished. Add subscriber function had to wait until all previous operations in shared mode will be completed and there was typical chain of locks.

There are bugs in Metalink raised by other people describing similar situations and there is only one workaround do not try any DDL on queues during traffic peaks. This recommendation is well know and most DBAs preventing database from typical DDL operation ( like index rebuild or schema changes) during business hours. The problems starts with hidden DDL commands used in application.

OEM performance tab and Active Session History data.

2010-07-18T19:10:00.000+01:00

Graphic representation of Active Session History data in Database Console or in Grid Control is causing some misunderstanding of how to read it. I have lot of questions about Y scale on OEM performance tab and now I decide to blog about it. I want to describe now OEM is using a ASH date and what we can do with this information. Anyway using SQL instead of OEM is more useful in performance tuning and can be easily focused on existing issue.

For other point of view knowledge of ASH give you opportunity to use free implementation of it developed by Kyle Hailey. (Thanks Kyle for your excellent scripts – this is great staff to learn from). By the way recently I have deployed SASH on big production database running on Oracle 9i and it help me to identify and address performance problems. As soon as I finish all these work I will blog about it too.

CPU utilization

CPU usage on performance tab is in green and in my case it has upper limit set to 2 – as I have two core CPU.

It is quite clear how to read CPU usage and what is a maximum CPU utilization. Scope of time which sessions can spend on CPU has an upper limit which is 100 % utilization of all CPU. It mean that maximum CPU time for session in sampling time can be calculated using following formula

Number of CPU x Sampling Time

So if we want to calculate a CPU usage we need to use following formula:

time consumed per all sessions / (Number of CPU x Sampling Time) * 100 %

ex.
time consumed per all sessions = 16 s
Number of CPU = 2
Sampling time = 15 s

Util of all CPUs = 15 / (2 * 15) * 100 % = 53.3 %

How to calculate CPU utilisation using Active Session History data instead of OEM performance tab ?

Active Session History table is a results of sampling V$SESSION view every second. That mean there is no “real” waiting or executing time in V$ACTIVE_SESSION_HISTORY but just samples. We are assuming that every state in ASH view (WAITING or ON CPU) took exactly 1 s. This is important as we don’t sum waiting time from ASH view but we have to count how many 1 s events were in observed time.
Be aware that DBA_HIST_ACTIVE_SESS_HISTORY has only 1/10 data of V$ACTIVE_SESSIN_HISTORY (only sample_id modulo 10 are copied into it) and each sample is now 10s long if we want to use it for our calculations.

Following query will calculate average CPU utilization for two minutes:

SQL> select trunc(count(*)/
  2  (min(p.value)*(max(sample_id) - min (sample_id)))*100,2) "CPU_UTIL",
  3  max(sample_id) - min (sample_id) "sampling time [s]",
  4  count(*) "time consumed per sessions [s]",
  5  min(p.value) * (max(sample_id) - min (sample_id)) "maximum possible time [s]"
  6  from v$active_session_history,
  7  (select value from v$parameter  where name = 'cpu_count') p
  8  where 1=1
  9  and sample_time >= to_date('18-07-2010 12:23:00','dd-mm-yyyy hh24:mi:ss')
 10  and sample_time <= to_date('18-07-2010 12:25:00','dd-mm-yyyy hh24:mi:ss')
 11  and session_state = 'ON CPU';

  CPU_UTIL sampling time [s] time consumed per sessions [s] maximum possible time [s]
---------- ----------------- ------------------------------ -------------------------
     29.23               118                             69                       236

But this doesn’t help us in understanding OEM graph. Refresh rate is set to 15 sec so we need to display data from that period split into 15 sec slices.

SQL> select trunc(to_char(sample_time,'SSSSS')/15) "15s_sample", trunc(count(*)/
  2  (min(p.value)*15)*100,2) "CPU_UTIL",
  3  count(*) "time consumed per sessions [s]"
  4  from v$active_session_history,
  5  (select value from v$parameter  where name = 'cpu_count') p
  6  where 1=1
  7  and sample_time >= to_date('18-07-2010 12:23:00','dd-mm-yyyy hh24:mi:ss')
  8  and sample_time <= to_date('18-07-2010 12:25:00','dd-mm-yyyy hh24:mi:ss')
  9  and session_state = 'ON CPU'
 10  group by trunc(to_char(sample_time,'SSSSS')/15)
 11  order by 1;

15s_sample   CPU_UTIL time consumed per sessions [s]
---------- ---------- ------------------------------
      2972       6.66                              2
      2973       6.66                              2
      2974      53.33                             16
      2975      23.33                              7
      2976      36.66                             11
      2977      26.66                              8
      2978      33.33                             10
      2979      43.33                             13

8 rows selected.

Now if you use any graphic tool to create a chart using 15s_sample column as X axis and "CPU util" column as Y axis you should get very similar picture to OEM chart.

Wait events

There is no easily calculated upper limit for waiting sessions. Why ? Number of waits per sample is depended on session state and number of waiting sessions. So what is a maximum for that ? Should we use only active sessions or all connected sessions ? The worst case scenario is all sessions are active and all are in waiting state. Both values multiplied each other give us a potential upper limit.

Performance tab in OEM is calculating a average of waits in sample time divided by groups and all groups are displayed using stacked area graph.

Following SQL return data used to create following graphs

SQL> select trunc(to_char(sample_time,'SSSSS')/15) "15s_sample", wait_class, count(*)/15
  2  from v$active_session_history
  3  where sample_time >= to_date('18-07-2010 12:23:00','dd-mm-yyyy hh24:mi:ss')
  4  and sample_time <= to_date('18-07-2010 12:25:00','dd-mm-yyyy hh24:mi:ss')
  5  and session_state = 'WAITING'
  6  group by trunc(to_char(sample_time,'SSSSS')/15), wait_class order by 1,3
  7  ;

15s_sample WAIT_CLASS                                                       COUNT(*)/15
---------- ---------------------------------------------------------------- -----------
      2974 Other                                                                     .2
      2974 System I/O                                                        .533333333
      2974 Commit                                                                     2
      2974 User I/O                                                          2.06666667
      2974 Concurrency                                                       4.66666667
      2975 Application                                                       .066666667
      2975 System I/O                                                        .933333333
      2975 User I/O                                                                 3.8
      2975 Commit                                                            4.93333333
      2976 Concurrency                                                       .066666667
      2976 Application                                                       .066666667
      2976 Other                                                                     .4
      2976 System I/O                                                               1.2
      2976 Commit                                                                   5.4
      2976 User I/O                                                                 5.8
      2977 System I/O                                                        .866666667
      2977 User I/O                                                          2.93333333
      2977 Commit                                                                   5.6
      2978 Concurrency                                                       .066666667
      2978 System I/O                                                        .933333333
      2978 User I/O                                                          3.26666667
      2978 Commit                                                                     4
      2979 System I/O                                                        .866666667
      2979 User I/O                                                          1.46666667
      2979 Commit                                                            2.53333333

25 rows selected.

As results we have average time spend by all sessions divided per wait class for a 1 second. What it mean that in sample 2976 wait class “Commit” average time was 5.4 ? It exactly mean that in that 15 seconds sampling time all active sessions were 5.4*15 = 81 times waiting for events in commit class. Is it bad or not ? This is a different question and will not be answered in that post.

Now if we take a look on sample nr 2976

2976 Application                                                       .066666667
2976 Other                                                                     .4
2976 System I/O                                                               1.2
2976 Commit                                                                   5.4
2976 User I/O                                                                 5.8

Sum of all waits give us 12.8 and this is more less what we can see as a top value on graph. A differences between graph and our calculation are related to observation period start time and how we split results into 15 sampling time. We are using a group by function based on sample_time truncated into 4 periods of seconds - using group by to_char function (based on Kyle idea).These periods are: 0 – 15, 15- 30 , 30-45, 45-60 but OEM can display data based on different 15 sec groups ex. 10 - 25, 25 - 40 so small differences can occure.

Now the question is – is this a bad thing to have high numbers of OEM graph ? And like always when we are talking about Oracle performance an answer is depends.

What is bad for sure if our CPU usage is going over 85-90 % and stay there for a long time. In that case CPU response time will have good opportunity to increase and whole system performance can go down. What to do with waits ? In my opinion a best way is to create a baseline (even on paper) with average waiting time and react only when system is working over our baseline or what is more important when system users are claiming a performance problems.

Next time I will use drill-into functionality of OEM and will try to explain what other graphs means – unfortunately most of them are changing a scale between main performance graph and detailed ones so if you are not aware of that it can cause a lot of misunderstandings.

Regards,
Marcin

Oracle and AIX

2010-07-14T21:22:00.000+01:00

Hi,

Very useful document about AIX configuration for Oracle Database provided IBM.

regards,
Marcin

Grid Infrastructure - Reconfiguration / host name change

2010-06-20T19:22:00.000+01:00

In 11g Release 2 Oracle introduced a Grid Infrastructure (GI) which can be also called Oracle Restart if it is implemented for single instance only. If database is based on Oracle ASM there is no other option and GI has to be installed. Since version 11gR2 ASM is not longer part of RDBMS but part of Grid Infrastructure.

I would like to present how to perform reconfiguration of Grid Infrastructure on one server together with change of server name and IP. Of course reconfiguration can be done in case of other problems and I want to show that it isn't very complicated.

Part 1 - Gathering current configuration

Information about current configuration of ASM and LISTENER should be well documented so in case of reconfiguration it can be easily used.

[oracle@piorovm ~]$ srvctl config asm
ASM home: /u01/app/11.2.0/grid
ASM listener: LISTENER
Spfile: +DATA_SOURCE/ASM/ASMPARAMETERFILE/REGISTRY.253.722024685
ASM diskgroup discovery string: /dev/sd*

[oracle@piorovm ~]$ srvctl config listener
Name: LISTENER
Home: /u01/app/11.2.0/grid
End points: TCP:1521

In addition all ASM disk group names should be documented. If there is no information about group names this information could be read from ASM disks even when ASM instance is down.

In that example information from header of all disks matching string '/dev/sd*' will be displayed.

[oracle@piorovm ~]$ asmcmd
Connected to an idle instance.
ASMCMD> lsdsk -I -p /dev/sd*
Disk_Num  Disk_group   Header_Stat  Path
       0  DATA_SOURCE  MEMBER       /dev/sdb1
ASMCMD>

Part 2- Grid Control deconfiguration

Current Grid Infrastructure configuration has to be deleted. To achieve it roothas.pl script with proper attributes has to be executed. If Grid Infrastructure was working all resources will be stopped automatically before deletion. This script has to be run as root user.

[root@piorovm install]# cd /u01/app/11.2.0/grid/crs/install
[root@piorovm install]# ./roothas.pl -delete -force
2010-06-18 14:40:33: Checking for super user privileges
2010-06-18 14:40:33: User has super user privileges
2010-06-18 14:40:33: Parsing the host name
Using configuration parameter file: ./crsconfig_params
CRS-4639: Could not contact Oracle High Availability Services
CRS-4000: Command Stop failed, or completed with errors.
CRS-4639: Could not contact Oracle High Availability Services
CRS-4000: Command Delete failed, or completed with errors.
CRS-4544: Unable to connect to OHAS
CRS-4000: Command Stop failed, or completed with errors.
ACFS-9200: Supported
2560+0 records in
2560+0 records out
10485760 bytes (10 MB) copied, 0.077012 seconds, 136 MB/s
Successfully deconfigured Oracle Restart stack

Part 3- Host rename and Grid Infrastructure configuration

In that part any required changes to host can be made. If reconfiguration was a result of errors
next new configuration of Grid Infrastructure can be done now.

In this example both IP and host name have been changed.

[oracle@piorovm2 ~]$ cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1       localhost.localdomain localhost
::1             localhost6.localdomain6 localhost6
10.10.30.150    piorovm2.localdomain piorovm2
[oracle@piorovm2 ~]$ hostname
piorovm2.localdomain

As a first step of Grid Infrastructure configuration "roothas.pl" script has to be executed.
It will create all local resources and allow future configuration of whole stack. This script has to be run as root user.

[root@piorovm install]# cd /u01/app/11.2.0/grid/crs/install
[root@piorovm2 install]# ./roothas.pl -verbose
2010-06-19 13:38:35: Checking for super user privileges
2010-06-19 13:38:35: User has super user privileges
2010-06-19 13:38:35: Parsing the host name
Using configuration parameter file: ./crsconfig_params
LOCAL ADD MODE
Creating OCR keys for user 'oracle', privgrp 'oinstall'..
Operation successful.
CRS-4664: Node piorovm2 successfully pinned.
Adding daemon to inittab
CRS-4123: Oracle High Availability Services has been started.
ohasd is starting

piorovm2     2010/06/19 13:41:12     /u01/app/11.2.0/grid/cdata/piorovm2/backup_20100619_134112.olr
Successfully configured Oracle Grid Infrastructure for a Standalone Server
[root@piorovm2 install]#

Next part of GI configuration will be executed from "oracle" account who is a owner of GI home.

First of all current status of all resources will be displayed.

[oracle@piorovm2 ~]$ crsctl status resource -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        OFFLINE OFFLINE
ora.diskmon
      1        OFFLINE OFFLINE
[oracle@piorovm2 ~]$

Both resources CSSD and DISKMON are offline and are not automatically started. It has to be changed and auto start mode has to be enabled.

[oracle@piorovm2 ~]$ crsctl modify resource "ora.cssd" -attr "AUTO_START=1"
[oracle@piorovm2 ~]$ crsctl modify resource "ora.diskmon" -attr "AUTO_START=1"
[oracle@piorovm2 ~]$ crsctl start resource "ora.cssd"
CRS-2672: Attempting to start 'ora.cssd' on 'piorovm2'
CRS-2672: Attempting to start 'ora.diskmon' on 'piorovm2'
CRS-2676: Start of 'ora.diskmon' on 'piorovm2' succeeded
CRS-2676: Start of 'ora.cssd' on 'piorovm2' succeeded
[oracle@piorovm2 ~]$ crsctl status resource -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        ONLINE  ONLINE       piorovm2
ora.diskmon
      1        ONLINE  ONLINE       piorovm2
[oracle@piorovm2 ~]$

In that step both cluster resources (in single node configuration both resources are not really cluster one) are started and other GI resources like Oracle Listener and Oracle ASM instance can be added.
In this example host name has been changed and appropriate changes has to be done for Oracle Listener configuration. After changes configuration file should look like this.

[oracle@piorovm2 ~]$ cat /u01/app/11.2.0/grid/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/11.2.0/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = piorovm2.localdomain)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

Listener and Oracle ASM has to be added to Grid Infrastructure and can be managed by it

Listener name LISTENER has been taken from information gathered in first part.

[oracle@piorovm2 ~]$ srvctl add listener -l LISTENER
[oracle@piorovm2 ~]$ srvctl start listener
[oracle@piorovm2 ~]$ crsctl status resource -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
               ONLINE  ONLINE       piorovm2
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        ONLINE  ONLINE       piorovm2
ora.diskmon
      1        ONLINE  ONLINE       piorovm2

When Listener is up and running Oracle ASM instance can be added to GI.

Listener name, disk string path and path and name of ASM parameter file has been taken from part 1.

[oracle@piorovm2 ~]$ srvctl add asm -l LISTENER -d "/dev/sd*" -p "+DATA_SOURCE/ASM/ASMPARAMETERFILE/REGISTRY.253.722024685"
[oracle@piorovm2 ~]$ srvctl start asm
[oracle@piorovm2 ~]$ crsctl status resource -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA_SOURCE.dg
               ONLINE  ONLINE       piorovm2
ora.LISTENER.lsnr
               ONLINE  ONLINE       piorovm2
ora.asm
               ONLINE  ONLINE       piorovm2                 Started
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        ONLINE  ONLINE       piorovm2
ora.diskmon
      1        ONLINE  ONLINE       piorovm2
[oracle@piorovm2 ~]$

When both - Listener and Oracle ASM instance has been successfully added Grid Infrastructure configuration has been completed.

Oracle Grid Infrastructure and all management commands related in previous version for Oracle RAC only are now required for single instance environment with ASM and in my opinion this is a new direction of Oracle software. All differences between Oracle RAC and single instance will be minimized from maintenance and configuration point of view.

regards,

Marcin

Rename file and BAAG

2010-05-24T23:09:00.003+01:00

Hi,

There are two reasons why I'm posting this. First I have read a interview
with Alex Gorbachev about BAAG and second I run into BAAG related problem myself.

This was not pure BAAG problem as I used my knowledge but I forgot about one very important step - VERIFY old knowledge (based on Oracle 8i) with current release. What I did ?

A scenario was very simple - I have a "production" database (thanks God it was only my VM) running on ASM with all files in group called DATA. I cloned that database using a storage snapshot functionality and then I have renamed ASM group to different name and mounted it. So far so good. Next step was to mount a cloned database and rename a files in control file using old fashion command

ALTER DATABASE RENAME FILE '+DATA\xxxx\redo01.log' to '+DATACLONE\xxxx\redo02.log';

Command was successfully finished and control file has been updated. I have checked a alert log and what was my surprise when I saw warning that file source can't be deleted.

WARNING: Cannot delete Oracle managed file

I didn't want to delete a source file - this file is a part of source database. It was very interested and I did another test - I have stopped a source database and run rename command for other file.

And ? Yes a source file has been deleted. What if you do it on production environment without test ?

The most worry thing is that latest SQL reference there is no information about that behavior.

This clause renames only files in the control file. It does not actually rename them on your operating system. The operating system files continue to exist, but Oracle Database no longer uses them.

This is not about ASM but if there is a different behavior for ASM it should be mentioned as well.

So there are three important things to remember:
- ALTER DATABASE RENAME FILE can delete files

- always check what you are going to do and not guess a results

- check relevant of vendor docs, White papers and other well know stuff with current release of software.

regards,

Marcin

Persistent ISCSI disks name

2010-05-11T20:57:00.003+01:00

Last few days I was working on Oracle cloning possibility using storage snapshots (post will be blogged soon) and I hit a ISCSI issue at the very beginning.
I have been playing with restarting storage and Linux box and from time to time ISCSI disk has been mapped to different block devices (/dev/sdb1 instead of /dev/sdc1). I really need to keep this persistent and I found this instruction for RedHat Linux (so it should work for OEL and CentOS).

Additionally I was looking for possibility to check how IQN shared on storage is mapped to Linux block devices. There is a nice directory layout where you can check that.

[root@piorovm by-path]# pwd
/dev/disk/by-path
[root@piorovm by-path]# ls -l
total 0
lrwxrwxrwx 1 root root  9 May 11 13:01 ip-10.10.20.241:3260-iscsi-iqn.1986-03.com.sun:02:6fe1e73d-8d7a-6ee7-8446-db844ae0d0c7-lun-0 -> ../../sdc
lrwxrwxrwx 1 root root 10 May 11 13:01 ip-10.10.20.241:3260-iscsi-iqn.1986-03.com.sun:02:6fe1e73d-8d7a-6ee7-8446-db844ae0d0c7-lun-0-part1 -> ../../sdc1
lrwxrwxrwx 1 root root  9 May 11 13:01 ip-10.10.20.241:3260-iscsi-iqn.1986-03.com.sun:02:f6746dbb-c95f-e2dc-8bb6-c82d3ef5169a-lun-0 -> ../../sdb
lrwxrwxrwx 1 root root 10 May 11 13:01 ip-10.10.20.241:3260-iscsi-iqn.1986-03.com.sun:02:f6746dbb-c95f-e2dc-8bb6-c82d3ef5169a-lun-0-part1 -> ../../sdb1
lrwxrwxrwx 1 root root  9 May 11 13:00 pci-0000:00:07.1-ide-0:0 -> ../../hda
lrwxrwxrwx 1 root root 10 May 11 13:00 pci-0000:00:07.1-ide-0:0-part1 -> ../../hda1
lrwxrwxrwx 1 root root 10 May 11 13:00 pci-0000:00:07.1-ide-0:0-part2 -> ../../hda2
lrwxrwxrwx 1 root root  9 May 11 13:00 pci-0000:00:07.1-ide-1:0 -> ../../hdc
lrwxrwxrwx 1 root root  9 May 11 13:00 pci-0000:00:10.0-scsi-0:0:0:0 -> ../../sda
lrwxrwxrwx 1 root root 10 May 11 13:00 pci-0000:00:10.0-scsi-0:0:0:0-part1 -> ../../sda1
[root@piorovm by-path]#

When you know which IQN is pointed to which block devices we can use mentioned RedHat instruction with some additional remarks.

Here is output from my configuration

Check WWID for block devices

[root@piorovm ~]# scsi_id -g -s /block/sdb
3600144f04be80ec000000c2936224200
[root@piorovm ~]# scsi_id -g -s /block/sdc
3600144f04be7ffdf00000c2936224200

Go to

[root@piorovm ~]# cd /etc/udev/rules.d/

and create a file called 20-names.rules Replace my WWID with results from scsi_id command and put correct names

[root@piorovm rules.d]# cat 20-name.rules
KERNEL=="sd*", BUS=="scsi", PROGRAM="/sbin/scsi_id -g -s", RESULT=="3600144f04be7ffdf00000c2936224200", NAME="sdc"
KERNEL=="sd*", BUS=="scsi", PROGRAM="/sbin/scsi_id -g -s", RESULT=="3600144f04be80ec000000c2936224200", NAME="sdb"

Here is a change with RedHat doc – use double equal mark "==" instead of one equal mark "=" for compare (KERNEL, BUS,RESULT).

Save file and start

[root@piorovm ~]# start_udev
Starting udev:                                             [  OK  ]
[root@piorovm ~]#

regards,
Marcin

Grid Control 11.1- new directories structure

2010-04-23T13:09:00.000+01:00

New Grid Control has a different design of directory structure on disk.
During installation process you have been asked for two paths

Middleware home
Instance base

Let’s take a closer look on new structure.

Middleware Home - set to /u01/Middleware

This is directory where Weblogic server has been installed and according to Grid Control Installation documentation

Oracle Middleware Home is the parent directory that has the Oracle WebLogic Server
home, the Web tier instance files, and, optionally, one or more Oracle homes.
This is where the OMS and the Management Agent are deployed.

After Grid Control installation process you will see following structure in that directory:

[oracle@piorovm Middleware]$ du -s -h *
1.4G    agent11g      
4.0K    domain-registry.xml   
172M    jdk160_14_R27.6.5-32  
187M    jrockit_160_14_R27.6.5-32  
8.0K    logs       
121M    modules      
4.0K    ocm.rsp      
3.4G    oms11g       
895M    oracle_common     
887M    Oracle_WT      
60K     registry.dat     
4.0K    registry.xml     
444K    user_projects     
26M     utils       
518M    wlserver_10.3

As you can see the following directories are part of Grid Control:

agent11g - Agent Oracle Home
oms11g - OMS Oracle Home
oracle_common
Oracle_WT - WebTier

Rest of directories are part of Weblogic server.

Instance base - set to /u01/gc_inst

According to Oracle Installation documentation:

Oracle Management Service Instance Base Location is the directory under the parent directory of the Oracle Middleware Home, where the configuration files of OMS a restored.

Let see what is inside

[oracle@piorovm gc_inst]$ du -s -h *
1.4M    em        
895M    user_projects     
53M     WebTierIH1

Short description:

em - sysman configuration and logs files – equivalnet of old OMS ORACLE_HOME/sysman directory
user_projects – deployed OMS server application – equivalent of old OMS ORACLE_HOME/j2ee directory
WebTierIH1 – web tier - equivalent of old OMS ORACLE_HOME/Apache

Log files

As Grid Control has a new directory structure all log files has been moved to different directories too. Please find some new location of well know logs:

Agent trace and log files -
/u01/Middleware/agent11g/sysman/log/
OMS trace and log files -
/u01/gc_inst/em/EMGC_OMS1/sysman/log
Domian log -
/u01/gc_inst/user_projects/domains/GCDomain/servers/EMGC_ADMINSERVER/logs/GCDomain.log
Weblogic OMS server --
/u01/gc_inst/user_projects/domains/GCDomain/servers/EMGC_OMS1/logs
Weblogic Admin server -
/u01/gc_inst/user_projects/domains/GCDomain/servers/EMGC_ADMINSERVER/logs

Where:

/u01/gc_inst - is a Instance Base
GCDomain is a Weblogic domain name

As you can see there are two Weblogic servers – if you are familiar with Weblogic you know that there should be always at least two – one is a Admin server and next servers (can be more than one) are management servers and those servers are running applications. In our case server EMGC_OMS1 is a management server and OMS software is running on it.
In relation to old Oracle Grid Control we can say that new admin server is a equivalent of HOME OC4 container and new management server called EMGS_OMS1 is a equivalent of EM OC4J container.

Grid Control maintenance

Automatic start:

An additional file has been added to Linux starting scripts (/ect/init.d/gcstartup) and it is staring
Grid Control infrastructure. Keep in mind that unlike to Grid Control 10g this script doesn’t include database. It is DBA responsibility to start a database before this script will be started.

Manual start:
To start Grid Control we need to use emctl tool from oms11g Oracle Home.

[oracle@piorovm oms11g]$ ./bin/emctl start oms
Oracle Enterprise Manager 11g Release 1 Grid Control
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
Starting WebTier...
WebTier Successfully Started
Starting Oracle Management Server...
Oracle Management Server Successfully Started
Oracle Management Server is Up
[oracle@piorovm oms11g]$

Status:
From maintenance point of view OPMN functionality has been reduced to WebTier only and management server can be start and stop only via emctl utility.

[oracle@piorovm oms11g]$ ./bin/emctl status oms
Oracle Enterprise Manager 11g Release 1 Grid Control
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
WebTier is Up
Oracle Management Server is Up
[oracle@piorovm oms11g]$

WebTier
Let’s try to check HTTP status using a old OPMN process

[oracle@piorovm ]$ cd /u01/Middleware/oms11g
[oracle@piorovm oms11g]$ ./opmn/bin/opmnctl status
opmnctl: ORACLE_INSTANCE is not set.
opmnctl status requires that ORACLE_INSTANCE be correctly set in your environment.
Make sure you are using ORACLE_INSTANCE/bin/opmnctl, or set ORACLE_INSTANCE
in your environment.

Ok let’s set this environment variable and check again.

[oracle@piorovm oms11g]$ ./opmn/bin/opmnctl status

Processes in Instance: instance1
---------------------------------+--------------------+---------+---------
ias-component                    | process-type       |     pid | status
---------------------------------+--------------------+---------+---------
ohs1                             | OHS                |   11538 | Alive

Now I can try to restart WebTier process if needed

[oracle@piorovm oms11g]$ ./opmn/bin/opmnctl stopproc
opmnctl stopproc: stopping opmn managed processes...
[oracle@piorovm oms11g]$ ./opmn/bin/opmnctl status

Processes in Instance: instance1
---------------------------------+--------------------+---------+---------
ias-component                    | process-type       |     pid | status
---------------------------------+--------------------+---------+---------
ohs1                             | OHS                |     N/A | Down

[oracle@piorovm oms11g]$ ./opmn/bin/opmnctl startproc
opmnctl startproc: starting opmn managed processes...
[oracle@piorovm oms11g]$ ./opmn/bin/opmnctl status

Processes in Instance: instance1
---------------------------------+--------------------+---------+---------
ias-component                    | process-type       |     pid | status
---------------------------------+--------------------+---------+---------
ohs1                             | OHS                |   18227 | Alive

Agent

There is no changes to agent startup process - only Agent Oracle Home has a different location.

[oracle@piorovm agent11g]$ pwd
/u01/Middleware/agent11g
[oracle@piorovm agent11g]$ ./bin/emctl start agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
Starting agent ................................... started.
[oracle@piorovm agent11g]$

Grid Control 11.1- Installation - OEM

2010-04-17T17:03:00.005+01:00

Hello,

Welcome in part 2 of my series about Grid Control 11g.
In the first part I have described how to install Weblogic server which is a one of main components of new Grid Control.

Second required component is a database in releases described in previous post too.I have used 11.1.0.7 with default settings.
According to installation document (included in zip files from eDevlivery) and checks performed by OUI during my installation following parameters has to be set in my database:

open_cursors 300 or greater
processes 500 or greater
session_cached_cursors 200 or greater
log_buffer 10485760 or greater

Next thing is to delete existing database console repository and delete whole db console configuration. This is checked by OUI too so it is better to do it before start.

emca -deconfig dbcontrol db -repos drop

If you are installing Grid Control on RedHat 5.2 (it was my case) in addition to all packages required for 11gR1 database you will need following package:

compat-db-4.2 

I have used that rpm compat-db-4.2.52-5.1.i386.rpm This a package with libdb used by webserver.

Space requirements:

Weblogic - installed in previous part about 1 GB
Grid Control - required space is 8.4 GB

about 7 GB is installed in Middleware home
rest in OEM home

Database - about 1 GB for new tablespaces

After that we can start with installation.

OUI start - ./runInstaller
Confirm that you don't need MOS - at least during installation
Basic installation - without any additional patches
New installation - we don't have any existing Management Services yet
All prerequisites are met
Middleware home loction has to be set to Weblogic installation, OMS location is a directory for Grid Control - see disk spaces requirement above
Provide username and password for new Weblogic domain and password for Node Manager. Weblogic domain can be compared to OC4J but you can have more then one server in domain. Node Manager is a host based tool used for domain management.
Provide database details like hostname, listener port and sys password. OUI will check if database meet all required prerequisites now.

I have deleted DB Console but still got following error - after 2 compilations I just clicked continue. When you drop DB Console repository SYSMAN user should be dropped too - so I don't think it will be a problem.
Enter password for SYSMAN user and path for tablespace files
Registration password for agent and security settings
Ports summary - all ports in one page and easy to edit
Last review
Installation - cafe time ;) it took about 1 hour on my laptop
Root part of work - all in one script
And at least - OEM configuration - another long running process
This is it - yes if you are here you have Grid Control installed

This is all for that post. I have almost no time after installation to play with new Grid Control but I have a free weekend so I hope I will spend some time on investigating it and I hope to blog about it.

regards,
Marcin

Grid Control 11.1- Installation - Weblogic server

2010-04-15T22:50:00.003+01:00

Hello

New Grid Control 11g has been released a view days ago (see my previous post with links).I have spend some time downloading it and now is time to share my installing experience.

First of all you have to know that unlike in previous version of Grid Control, new one is shipped without application server and without database.Both of them has to be downloaded and installed separately.

This a list of all necessary Oracle products:

Oracle Database - 10.2.0.4, 11.1.0.7, 11.2.0.1 plus required patches - see Grid Control Basic Installation Guide
Weblogic 10.3.2 with patches - I was unable to find required patch but decided to try without it
certified operating system - Oracle Enterprise Linux 4, 5.x or RedHat Linux 4, 5.x

I'm wondering what is inside this package Grid Control 10g has about 2.5 GB including database and application server. Grid Control 11g has 4.2 GB without application server (1 GB) and database server (4 GB for Oracle 11gR1).
Let's start with prerequisites.I assume that everybody has database up and running and I will start with Weblogic server. I have some experience with Weblogic version 7 and 8 and I have been very excited to see a new version of it.

Download a Weblogic server - I have used Package installer which has everything inside one package
Login as oracle and start ./wls1032_linux32.bin process
Welcome screen appear - Click Next
Choose Middleware Home and click Next
Confirm that you don't need to configure My Oracle Support
Choose installation type
Confirm Weblogic directory
Installation summary - click Next
Take some - installation in progress
Installation has been completed - We don't need to configure any Weblogic domain now so we can skip Quickstart.

If everything was well you should have all necessary components (database and Weblogic server) to start Grid Control installation. Next post will cover check of database parameters and Grid Control installation. See you soon.

Marcin

Grid Control 11.1

2010-04-12T21:50:00.001+01:00

Hi,

Grid Control 11.1 on Linux platform is available to download from Oracle E-delivery.

Thanks to Marco Gralike and Rob Zoeteweij.

There are 3 disks - about 4.2 GB in total - so be aware before you download it. You will need a lot of space for test. Grid Control 11.1 is based on Weblogic server and it need 2 GB of RAM.

According to disk space and memory requirements it is laptop killer.

regards,

Marcin

Shutdown immediate and background sqlplus process

2010-04-02T19:09:00.004+01:00

Hello,

I have been playing with Oracle 11g and memory utilization and I finished myself with strange problem. I have started about 60 background sessions using following command:

$ sqlplus pioro/pioro @a.sql

Script a.sql is a simple one and it is allocating about 8 MB of memory for PL/SQL table but this is story for different post.
After some tests I was going to restart database. First thought was simple - shutdown immediate and that's it.
When I come back with next cup of tea database was still open. Hmmm.
Let's check alter.log

License high water mark = 59
Waiting for dispatcher 'D000' to shutdown
Waiting for shared server 'S000' to die
All dispatchers and shared servers shutdown
Tue Mar 30 09:31:00 2010
SHUTDOWN: Active processes prevent shutdown operation
Tue Mar 30 09:36:01 2010
SHUTDOWN: Active processes prevent shutdown operation

and trace file

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = /oracle/app/product/11.1.0/db_1
...
*** 2010-03-30 09:25:58.372
*** SESSION ID:(170.15) 2010-03-30 09:25:58.372
*** CLIENT ID:() 2010-03-30 09:25:58.372
*** SERVICE NAME:(SYS$USERS) 2010-03-30 09:25:58.372
*** MODULE NAME:(sqlplus@piorovm.localdomain (TNS V1-V3)) 2010-03-30 09:25:58.372
*** ACTION NAME:() 2010-03-30 09:25:58.372
...
ksukia: Starting kill, force = 0
ksukia: killed 57 out of 57 processes.

*** 2010-03-30 09:26:03.421
ksukia: Starting kill, force = 0
ksukia: Attempt 1 to re-kill process OS PID=24040.
...
ksukia: Attempt 1 to re-kill process OS PID=23958.
ksukia: Attempt 1 to re-kill process OS PID=23954.
ksukia: Attempt 1 to re-kill process OS PID=23951.
ksukia: Attempt 1 to re-kill process OS PID=23949.
...

Hmm - all this session was idle and there was no transactions at all - so why "shutdown immediate" can't shut down database. Next what has to be done is check of status of processes in Linux

$ ps -ef 
...
oracle    7426 11402  0 09:34 pts/1    00:00:00 sqlplus             @a.sql
oracle    7427  7426  0 09:34 ?        00:00:00 [oracle] <defunct>
...

I just put two lines as example - as you can see oracle process 7427 becomes zombie.
So let's kill it and solve problem

$ kill -9 7427
$ ps -ef
...
oracle    7426 11402  0 09:34 pts/1    00:00:00 sqlplus             @a.sql
oracle    7427  7426  0 09:34 ?        00:00:00 [oracle] <defunct>
...

Still working. I google a little bit and found answer (http://www.linuxsa.org.au/tips/zombies.html)
This is true - you can kill dead process. What you have to do is kill his parent process.
PPID for my process is 7426 and this is a sqlplus process starting my script.

$ kill -9 7426

and database is going down. (of course I have to kill all sqlplus processes without that one with shutdown immediate command running)

Most funny thing is that is sqlplus process is running in foreground or when is started from cron there is no issue with other sessions
and shutdown immediate is working well.

Example:

$ ps -ef
...
oracle    8831 11402  0 09:40 pts/1    00:00:00 sqlplus             @a.sql
oracle    8832  8831  1 09:40 ?        00:00:00 oraclepioro (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
...

Shutdown immediate executed on other sqlplus session and following result in observed session

SQL> select * from dual;
select * from dual
*
ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 8832
Session ID: 153 Serial number: 5

This is expected result of shutdown immediate command.

Where is a problem ?
If sqlplus is running as background process and it become idle (no active SQL) will be not finished until it will be killed or taken to foreground using fg.
Be aware if you want to shutdown database and you have some sqlplus'es in background – especially in this same Linux session.

regards,
Marcin

Oracle Application Server issue

2010-03-27T19:21:00.001Z

Strange error in Oracle Application Server 10g 10.1.3.3.

I have tried to start it using opmnctl startall and it finish with following error:

10/03/25 08:06:44 WARNING: OC4J will not send ONS ProcReadyPort messages to opmn for service: OC4JServiceInfo id: null protocol: jms hostname: nul
l port: 12602 description: null
10/03/25 08:06:44 null hostname was null
10/03/25 08:06:44 Oracle Containers for J2EE 10g (10.1.3.3.0)  initialized

First idea was that something is wrong with /etc/hosts file or IP has been changed but everything was OK. I have found solution on OTN forum and it works. You have to stop all iAS components and delete all files from following directories:

$ORACLE_HOME/j2ee/home/persistence
$ORACLE_HOME/j2ee/[other OC4J]/persistence

regards,

Marcin

Oracle ACFS on CentOS

2010-03-13T19:22:00.001Z

Hi,

This is short instruction how to install Oracle ACFS on CentOS. CentOS is free version of RedHat and it is binary compatible. It is not officially supported by Oracle but all solutions related for RedHat should work on CentOS too.

I have found Laurent blog post about it and I used it as a background of my investigation. When I have used Laurent's steps it was fine until I want to register ACFS in Oracle Cluster Registry. I couldn't do it as not all cluster types has been registered during Grid Infrastructure configuration process.

Here is a simple solution how to fix it for new installations (still working on solution for existing one).

Perform GUI part of Grid Control installation

Before executing root.sh on both nodes edit following file on both nodes : $ORACLE_HOME/lib/osds_acfslib.pm

line 299:    
else   {     
   # we don't know what this is     
   $supported = 1;     
   $vers = "EL5";   
}

Execute a root.sh on both nodes

Now ACFS will be installed and configured and all necessary tools will be copied.

RMAN and SBT_TAPE backup pieces - how to catalog it

2010-02-01T23:43:00.002Z

Hi,

There has been a discussion on oracle-l mailing list about RMAN and backup pieces on tape which are non in recovery catalog anymore. A few people including me response that those backup pieces can be restored using dbms_backup_restore PL/SQL package if we only know a backup piece name.

Update 01/02/2010: Now is more solutions - I have started my investigation a few days ago.

I investigated this problem a little deeper and I found another solution. I did it because I was not aware that according to official Oracle documentation CATALOG command is not working with ‘SBT_TAPE’ interface.

I have tested this solution on:

Oracle 11.1.0.7 Linux 32 bit
Oracle 10.2.0.1 Windows 32 bit
Oracle 10.2.0.4 Linux 32 bit

I’ve no possibility to check this on other machines now but if you can give me a shout what a results are.

Let’s story begin.

Only thing I knew was a backup piece name. This is a prerequisite but it can be found quite easily by Media Manager admin or by you itself if you know your Media Manager software. See example below for Legato Networker:

[oracle@piorovm usr]$ ./sbin/mminfo -q "client=piorovm" -r "ssid,name,totalsize,savetime,volume"
 ssid      name                                 total  date   volume
4267926146 RMAN:3bl4n6ru_1_1                  9961944 01/29/2010 piorovm.localdomain.001
4234372063 RMAN:3dl4n76v_1_1                805331112 01/29/2010 piorovm.localdomain.001
4217594914 RMAN:3el4n792_1_1               1389143616 01/29/2010 piorovm.localdomain.001
4200817814 RMAN:3fl4n7cl_1_1                477378960 01/29/2010 piorovm.localdomain.001
4251148936 RMAN:c-3537902502-20100129-03     10224108 01/29/2010 piorovm.localdomain.001
4184040645 RMAN:c-3537902502-20100129-04     10224108 01/29/2010 piorovm.localdomain.001

Or another example for Verita Netbackup (by Allen Brandon from Oracle-l list)

 /opt/oracle ->bplist -t 4 -k  -l -s 1/25/2010 -e 1/26/2010 -R /
-rw------- oracle    oracle       12582912 Jan 25 21:21 /c-18889999-20100125-09
-rw------- oracle    oracle        2359296 Jan 25 21:18 /xxprd_al_52855_1_709244222
-rw------- oracle    oracle       3383296K Jan 25 21:15 /xxprd_db_52852_1_709243545

Next thing is to add these names to control file or recovery catalog. First of all I started with very low level solution and dbms_backup_restore package. There is a function called inspectBackupPiece which required only backup piece name. Channel has to be allocated before that command. This gave me a hope that I can do it. I have created a small PL/SQL script and I have executed it.

DECLARE
v_dev varchar2(50);             
v_fullname varchar2(4000);
recid number;
stamp number;

BEGIN
v_dev:=sys.dbms_backup_restore.deviceAllocate(type=>'sbt_tape', ident=>'test');
sys.dbms_backup_restore.inspectBackupPiece('3fl4n7cl_1_1',v_fullname,recid,stamp);
sys.dbms_backup_restore.deviceDeallocate;
END;
/

And that was it – Backup piece has been added to control file.

Ok, if there is a possibility to does this using PL/SQL package there should be possibility do this using RMAN command line. I was trying with allocating ‘SBT_TAPE’ channel and catalog command but without success. Because I have very short memory about command syntax and I know that RMAN is helping with that a little bit I gave a try:

RMAN> catalog  ;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found ";": expecting one of: "archivelog, backup, backuppiece, controlfilecopy, clone, datafilecopy, device, db_recovery_file_dest, recovery, start"
RMAN-01007: at line 2 column 1 file: standard input

As you can see in error message is a short information about what is expected. Hmmm a device I think it is a good direction. Let’s try again

RMAN> catalog device type;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found ";": expecting one of: "double-quoted-string, disk, equal, identifier, single-quoted-string"
RMAN-01007: at line 1 column 20 file: standard input

Ok that’s it – RMAN is expecting a disk or any double or single quoted string.

Final step is to give RMAN a chance:

RMAN> list backupset;

RMAN>

There is no backup in control file.

RMAN> catalog device type 'sbt_tape' backuppiece '3dl4n76v_1_1';

released channel: ORA_DISK_1
released channel: ORA_SBT_TAPE_1
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=170 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: NMO v5.0.0.0
cataloged backup piece
backup piece handle=3dl4n76v_1_1 RECID=103 STAMP=709599103

and now

RMAN> list backupset;
List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
77      Full    Unknown    SBT_TAPE    00:00:00     29-JAN-10
        BP Key: 103   Status: AVAILABLE  Compressed: NO  Tag: TAG20100129T224031
        Handle: 3dl4n76v_1_1   Media:
  List of Datafiles in backup set 77
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 8788185    29-JAN-10 +DATA/pioro/datafile/system.260.695313067

Yes we have one already added via catalog command.

After that I have found two notes on MOS where this syntax is mentioned no 727655.1 and not public no550082.1

This example is very typical for a lot of new solutions – if you are not aware that something is impossible you can try and do that.

regards,
Marcin

Oracle RAC One Node – part 3

2010-01-30T18:31:00.002Z

At the beginning I have to apologize everyone who was waiting on that post and I will try to keep posting more regular.

This time before I will present connection test results I need to clarity one thing.
Instance name in One Node RAC can change on each node. This is related to way how instance is started. In case of Omotion migration there is a situation when both instances are up and running so both instances have to have different name, ex.

testone_1 is running on rac1
testone_2 is running on rac2

What happen if one of servers with crash with running instance (ex. rac1 will crashed).
ClusterWare status is showing that last running instance was testone_1 and this instance will be migrated to other node in cluster. This mean that testone_1 instance will be started on rac2 server.

If we fix rac1 server and we want to failback our database using Omotion instance will be started on rac1 server but according to restriction described in previous point instance name has to be different and in that case instance name will be testone_2. So that mean that now we have instance:

testone_1 running on rac2
testone_2 running on rac1

Of course after session migration only one instance will be running but still with that same name.
Where is a problem? You can’t hardcoded instance name and server name in any monitoring tool because instance name can be vary depend on fail over scenario. You should use raconestatus to figure out what is a current status of database.

Now is a time to make a last tests with Java and Fast Connection Failover client.
I have used following code based on Oracle Example with FCF functionality.

After compilation it is high time to start tests:

Case no 1– both servers are up, instance is running on rac1

I have started Oracle test program and run Omotion in rac1 forcing instance migration to rac2.
Below is output from test program:

Url=jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=rac1-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=rac2-vip)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=serviceone)))
Instance name: testone_1
FCF Activ(cache): 1
FCF Avail(cache): 9

Url=jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=rac1-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=rac2-vip)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=serviceone)))
java.sql.SQLException: Closed Connection
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:111)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:145)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:207)
        at oracle.jdbc.driver.OracleStatement.ensureOpen(OracleStatement.java:3512)
        at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1244)
        at FCFDemo2.main(FCFDemo2.java:62)
getErrorCode=17008
getSQLState=null
getMessage=Closed Connection
Url=jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=rac1-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=rac2-vip)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=serviceone)))

Instance name: testone_2
FCF Activ(cache): 1
FCF Avail(cache): 0

As excepted session has been failover from instance to another without any problems with one raised exception.
So let’s test it once again

Url=jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=rac1-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=rac2-vip)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=serviceone)))
Instance name: testone_2
FCF Activ(cache): 1
FCF Avail(cache): 9

Url=jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=rac1-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=rac2-vip)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=serviceone)))
java.sql.SQLException: Io exception: Software caused connection abort: recv failed
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:111)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:145)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:254)
        at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:805)
        at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1030)
        at oracle.jdbc.driver.T4CStatement.executeMaybeDescribe(T4CStatement.java:829)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1123)
        at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1263)
        at FCFDemo2.main(FCFDemo2.java:62)
getErrorCode=17002
getSQLState=null
getMessage=Io exception: Software caused connection abort: recv failed
java.sql.SQLException: Listener refused the connection with the following error:
ORA-12514, TNS:listener does not currently know of service requested in connect descriptor
The Connection descriptor used by the client was:
(DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=rac1-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=rac2-vip)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=serviceone)))

        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:111)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:260)
        at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:386)
        at oracle.jdbc.driver.PhysicalConnection.(PhysicalConnection.java:413)
        at oracle.jdbc.driver.T4CConnection.(T4CConnection.java:164)
        at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:34)
        at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:752)
        at oracle.jdbc.pool.OracleDataSource.getPhysicalConnection(OracleDataSource.java:296)
        at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:220)
        at oracle.jdbc.pool.OracleConnectionPoolDataSource.getPhysicalConnection(OracleConnectionPoolDataSource.java:156)
        at oracle.jdbc.pool.OracleConnectionPoolDataSource.getPooledConnection(OracleConnectionPoolDataSource.java:93)
        at oracle.jdbc.pool.OracleImplicitConnectionCache.makeCacheConnection(OracleImplicitConnectionCache.java:1529)
        at oracle.jdbc.pool.OracleImplicitConnectionCache.getCacheConnection(OracleImplicitConnectionCache.java:463)
        at oracle.jdbc.pool.OracleImplicitConnectionCache.getConnection(OracleImplicitConnectionCache.java:332)
        at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:403)
        at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:188)
        at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:164)
        at FCFDemo2.main(FCFDemo2.java:77)

C:\Downloads\fcf_stepbystep>rem Restore old var values

C:\Downloads\fcf_stepbystep>set ORACLE_HOME=C:\oracle\product\10.2.0\db_1

Opps what happen ? We lost a connection and program has been terminated.
Let’s take a look on raised exceptions – first one is related to session closure on active instance. In code there is catch section which catch exception and try to reopen connection. But in that case a new started instance has not been registered in listener yet and another exception came in. This one was not catch by any section and demo has been terminated.
This same behaviour appear in SQL*Plus tests – in my opinion information about instance availability is sending before service is registered in listener. Maybe this is only my VM installation issue but if my assumption about order of event is correct it can happen everyware.

Case no 2 session with transactions

Base on Oracle example code I have added transactions. Below is my version of code:

// 
// last revised 01/06/2006 - Adding connection pool exposure
//      revised 01/05/2006 - kpm use VIP IP addresses.
//      revised 12/21/2005 - PC
// ...........
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.*;
import java.sql.Statement;
import java.util.Properties;
import java.util.*;
import oracle.jdbc.pool.OracleDataSource;
import oracle.jdbc.pool.*;
import oracle.jdbc.*;
public class FCFDemo2 {
   public static void main(String[] args) throws InterruptedException {
   try {
   OracleDataSource ods = new OracleDataSource();
   OracleConnectionCacheManager occm = null;
   ods.setUser("system");
   ods.setPassword("oracle");
   String cacheName = "cache";
   String dbURL="jdbc:oracle:thin:@"     
               +"(DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=ON)" +
         "(ADDRESS=(PROTOCOL=TCP)" +
         "(HOST=rac1-vip)(PORT=1521))" +
         "(ADDRESS=(PROTOCOL=TCP)" +
         "(HOST=rac2-vip)(PORT=1521)))" +
         "(CONNECT_DATA=(SERVICE_NAME=serviceone)))";
   System.out.println("Url=" + dbURL );
   ods.setURL(dbURL);
   System.out.println("Url=" + dbURL );
   ods.setConnectionCachingEnabled(true);
   Properties prop = new Properties();
   prop.setProperty("MinLimit", "5");
   prop.setProperty("MaxLimit", "40");
   prop.setProperty("InitialLimit", "10");
   prop.put (oracle.net.ns.SQLnetDef.TCP_CONNTIMEOUT_STR,"" + (5000)); // 5000ms
   ods.setConnectionCacheProperties(prop);
   ods.setFastConnectionFailoverEnabled(true);
   ods.setONSConfiguration("nodes=rac1-vip:6200,rac2-vip:6200");
   ods.setConnectionCacheName(cacheName);   // moved from above
   occm = OracleConnectionCacheManager.getConnectionCacheManagerInstance();
   Connection conn = ods.getConnection();
   conn.setAutoCommit(false);
   System.out.println(conn);
   Statement stmt=conn.createStatement();  // fixed
   ResultSet rs = null;
   int stop = 0;
   
   rs =stmt.executeQuery("truncate table marcin");  
   int i = 0;
   int j=0;
   String name = new String();
      while (true){
      try {
                //System.out.println("Url=" + dbURL );
                rs =stmt.executeQuery("select instance_name from v$instance");
                while(rs.next()) {
 name = rs.getString(1);
                   System.out.println("Instance name: " + name);
                   System.out.println("FCF Activ(" + cacheName + "): " +
                   occm.getNumberOfActiveConnections(cacheName));
                   System.out.println("FCF Avail(" + cacheName + "): " +
                   occm.getNumberOfAvailableConnections(cacheName)+"\n");
                   PreparedStatement pstmt = conn.prepareStatement ("insert into marcin  values (?, ?)");
 pstmt.setInt (1, i);               
                   pstmt.setString (2, name);   
pstmt.execute ();
 System.out.println("inserted " + i +" \n");
 Thread.sleep(1000);
 conn.commit();    
 System.out.println("Commited " + i + " \n");
 i++;
      }
      catch (OracleSQLException sqle) {
     //sqle.printStackTrace();
              System.out.println("getErrorCode=" + sqle.getErrorCode());
              System.out.println("getSQLState=" + sqle.getSQLState());
              System.out.println("getMessage=" + sqle.getMessage());
     //Thread.sleep(1000);
     
     System.out.println("Try number " + j + "\n");
     j++;
     
     try {
     conn =ods.getConnection(); //Re-get the conn
     stmt =conn.createStatement();
     } catch (OracleSQLException sqle1) {
               System.out.println("getErrorCode - reconnect =" + sqle.getErrorCode());
               System.out.println("getSQLState - reconnect =" + sqle.getSQLState());
               System.out.println("getMessage - reconnect=" + sqle.getMessage());
      sqle1.printStackTrace();      
     }
        }
       Thread.sleep(1000);
    System.out.println("New iteration \n");    
       }
   }
   catch(Exception e)
      {
      e.printStackTrace();
      }
   }
}

Start inserting rows.

Instance name: testone_1
FCF Activ(cache): 1
FCF Avail(cache): 9

inserted 185
Commited 185

Instance name: testone_1
FCF Activ(cache): 1
FCF Avail(cache): 9

inserted 186
Commited 186

java.sql.SQLException: Closed Connection
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:111)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:145)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:207)
        at oracle.jdbc.driver.OracleStatement.ensureOpen(OracleStatement.java:3512)
        at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1244)
        at FCFDemo2.main(FCFDemo2.java:70)
getErrorCode=17008
getSQLState=null
getMessage=Closed Connection
Try number 0

Instance name: testone_2
FCF Activ(cache): 1
FCF Avail(cache): 0

inserted 187
Commited 187

Instance name: testone_2
FCF Activ(cache): 1
FCF Avail(cache): 0

inserted 188

Commited 188

Instance name: testone_2
FCF Activ(cache): 1
FCF Avail(cache): 0

As you can see transaction got a exception but because there is a loop in the code transaction has been replay and successfully inserted in database.

SQL> select id,cos from system.marcin where id > 184 order by id;

        ID   INSTANCE_NAME
---------- ------------------------------
       185 testone_1
       186 testone_1
       187 testone_2
       188 testone_2

Any RAC aware software should work with RAC OneNode without any problem like with typical Oracle RAC installation.

This is last part about Oracle RAC OneNode and connection details but I’m sure not a last about RAC itself.

How to create CSV file in SQL*Plus

2009-12-15T22:16:00.006Z

As I remember this is one of question which was asked every time on my Oracle DBA classes
"How to save a data in CSV or other text format ?"
Now a answer is simple go to Shivmohan Purohit Oracle blog and check that post SQL*Plus output.

Oracle RAC One Node – part 2

2009-12-14T10:30:00.015Z

In this post I want to share with you some scenarios about RAC One Node and client behaviour. In that part I’m using SQL*Plus from 10g so there is no FAN notification, next part will be with FAN enabled client. The main difference between using and not using FAN is that FAN client has a feedback from Oracle Cluster (formally Grid Infrastructure in 11R2 or Oracle ClusterWare in previous releases) and has information about current cluster status. Non FAN client has to wait for TCP/IP time-out for current session before switching to other node. More information about client failover and cluster configuration can be found here.

My server and client configuration has been presented in part 1 (entry from TNSNAMES.ORA) so this is only short recall:

Two nodes – RAC1 and RAC2 with SCAN configured
SQL*Plus 10g Client

I want to present 3 scenarios:

One node in cluster is running

Two nodes are running – current instance is crashing

Active query

Non active session

Fixing configuration

One node in cluster is running
Due to failure or maintenance only one node in cluster is running. RAC One node service is starting on that node and it is opened for all sessions. Irrespective of which node was current previously instance is open on node which is running now. Current status can be checked via raconestatus


[oracle@rac1 ~]$ raconestatus

RAC One Node databases on this cluster:


Database UP Fix Required        Current Server                    Candidate Server Names
======== == ============ ==============================  ========================================
testone  Y       N                                rac1                                 rac1 rac2                             


Available Free Servers:

As we can see there is no available server to move our instance on. If we start Omotion process we will hit error.


[oracle@rac1 ~]$ Omotion

RAC One Node databases on this cluster:

#      Database                    Server                      Fix Required
===     ========        ==============================          ============
[1]      testone                                  rac1                N

Enter number of the database to migrate [1]:

Specify maximum time in minutes for migration to complete (max 30) [30]: 2

Available Target Server(s) :
#            Server            Available
===     ==================      =========
ERROR: Cannot find any available servers in this cluster.

Exiting...

[oracle@rac1 ~]$

In case of shutdown node RAC1 all session will be terminated as in single node configuration. Whatever server will be started after both nodes are down will become a current one.

Two nodes are running – current instance is crashing
Both servers are up – instance is running on RAC1 server. Current cluster configuration can be checked via crsctl command


[oracle@rac1 grid]$ $GRID_HOME/bin/crsctl status resource -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
        ONLINE  ONLINE       rac1
        ONLINE  ONLINE       rac2
ora.LISTENER.lsnr
        ONLINE  ONLINE       rac1
        ONLINE  ONLINE       rac2
ora.asm
        ONLINE  ONLINE       rac1                     Started
        ONLINE  ONLINE       rac2                     Started
ora.eons
        ONLINE  ONLINE       rac1
        ONLINE  ONLINE       rac2
ora.gsd
        OFFLINE OFFLINE      rac1
        OFFLINE OFFLINE      rac2
ora.net1.network
        ONLINE  ONLINE       rac1
        ONLINE  ONLINE       rac2
ora.ons
        ONLINE  ONLINE       rac1
        ONLINE  ONLINE       rac2
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1        ONLINE  ONLINE       rac1
ora.oc4j
1        OFFLINE OFFLINE
ora.rac1.vip
1        ONLINE  ONLINE       rac1
ora.rac2.vip
1        ONLINE  ONLINE       rac2
ora.scan1.vip
1        ONLINE  ONLINE       rac1
ora.testone.db
1        ONLINE  ONLINE       rac1                     Open
ora.testone.serviceone.svc
1        ONLINE  ONLINE       rac1
[oracle@rac2 grid]$

Session is connected to RAC1 server


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> select * from test;

 ID
----------
  1
  2
  3
  4
  5
  6

6 rows selected.

SQL> insert into test values (7);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into test values (8);

1 row created.

SQL> select instance_name, host_name from v$instance;

INSTANCE_NAME              HOST_NAME
----------------           ----------------------------------------------------------------
testone_2                  rac1

SQL>

Active Query
Now first scenario when I’m repeating a select command in SQL*Plus and in that same time I’m turning off RAC1 server. In that case a active statement is cancelled and client is trying to reconnect at that time.


SQL> select to_char(sysdate,'hh24:mi') from dual;

TO_CH
-----
09:55

SQL> select to_char(sysdate,'hh24:mi') from dual;
select to_char(sysdate,'hh24:mi') from dual
*
ERROR at line 1:
ORA-03135: connection lost contact


SQL> select to_char(sysdate,'hh24:mi') from dual;
ERROR:
ORA-03114: not connected to ORACLE

As you can see command has been cancelled and session has been terminated. There is no other instance up and running yet to there is no target to failover a session. In client session network trace file you can find following order of errors:

ORA -12547 – TNS Lost Contact

ORA- 12151 – Bad packages

ORA-03115 – connection lost contact

After that client is going to reconnect session to other nodes like in normal RAC configuration. Client is calling SCAN address to get another VIP address but cluster is still in that same state – a reconfiguration has been just started – and Oracle Cluster is returning a rac1-vip once again as it is only one VIP configured for database service.
Keep in mind that current, not committed transaction in that case will be rollback.

Idle session
When a session is in an idle state a failover will take place when a first SQL query will be executed. See example


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> select to_char(sysdate,'hh24:mi') from dual;

TO_CH
-----
15:30


SQL> select instance_name, host_name from v$instance;

INSTANCE_NAME            HOST_NAME
----------------         ----------------------------------------------------------------
testone_2                rac1


SQL> select to_char(sysdate,'hh24:mi') from dual;

TO_CH
-----
15:38

SQL>
SQL> select instance_name, host_name from v$instance;

INSTANCE_NAME            HOST_NAME
----------------         ----------------------------------------------------------------
testone_1                rac2

SQL>

As you can see a connection has been migrated between servers without any error. This is because a before a last query a new instance was up and running. If we take a look into client network trace file we will see following errors

ORA- 12151
ORA- 3113

After that client is going to reconnect session to other nodes. Client is calling SCAN address to get another VIP address and cluster in returning a new VIP (rac2-vip) as a database service has been successfully migrated to other node.
Keep in mind that current, not committed transaction has to be rollback after session migration.

Fixing configuration
After unexpected crash it is possible that RAC One Node configuration need to be fixed. I have figure out two possible scenarios:

After node crash and migration of instance and service the crashed node has been rebooted and there are two running instances


[oracle@rac1 grid]$ raconestatus

RAC One Node databases on this cluster:


Database UP Fix Required        Current Server                    Candidate Server Names
======== == ============ ==============================  ========================================
testone  Y       Y                           rac1 rac2                                 rac1 rac2


Available Free Servers:

[oracle@rac1 grid]$

Fixing of configuration will close one of instance (on rebooted node) and will clean a configuration.


[oracle@rac1 rac1]$ raconefix

RAC One Node databases on this cluster:

#      Database                    Server                      Fix Required
===     ========        ==============================          ============
[1]      testone                                  rac1                Y

Enter number of the database to fix [1]:

If database is up, it will be checked and cleaned after a previous fail over.

Very similar situation like in previous point but only one instance is working and there is a “mess” in RAC service configuration. Oracle RAC One Node fix will clean up a configuration.

At the end I want to mention that I have hit some strange behavior related to migration of instance between servers and starting and stopping servers. I’m not sure if it a bug or not but after whole cluster restart instances have been migrated across.
First configuration was

RAC1 – instance name : testone_2
RAC2 – instance name : testone_1

Yes I know it was my inconsequence in naming so Oracle decided to fix it. No, not at the beginning but after a week of testing and restarts. And now it looks like:

RAC1 – instance name : testone_1
RAC2 – instance name : testone_2

Unfortunately after that I was unable to play with RAC One Node and I have to clear a whole configuration and create it from scratch.

Next part will be about FAN enabled clients and more about transactions.

Oracle RAC One Node – part 1

2009-12-07T07:25:00.007Z

This post is about a installation and configuration of Oracle RAC 11gR2 in One Node configuration on VMWare. At the beginning I want describe a RAC One Node a little bit more – this is a new possible configuration with special licence and price which is very similar to fail over cluster configuration. During a normal work only one instance and up and running – like in failover cluster, a difference is in migration process. The following steps are performed during a migration:

Second instance in started on target node
All session are migrated to target instance – TAF has to be enabled on client configuration
Source instance in shutdown in transaction mode
After timeout a source instance in shutdown in abort mode

Installation and configuration in based on Linux CentOS 5.3 and Oracle 11g 11.2.0.1

If you want to skip Grid Infrastructure configuration tips click here

Oracle Grid Infrastructure
Oracle Grid Infrastructure has to be installed and configured on all nodes belongs to Oracle RAC (this is requirement for both RAC configuration – typical RAC and One Node RAC). In previous releases Oracle Grid Infrastructure was called an Oracle ClusterWare. In 11g R2 a name has been change to Grid Infrastructure and a lot of changes have been made. The most important are:

Oracle ASM is a part of Grid Infrastructure and not a part of Oracle Database
Vote and cluster configuration can use ASM disk or cluster file system
Raw or block devices for vote and cluster configuration are not supported during a installation time
More RAM is required ?

Last change made me sad as I have only 4 GB RAM on my laptop so I can allocate about 3 GB for two VM. But why do not try. After a few tests I have found a working configuration for both nodes.

RAC1 – node number 1

1.5 GB of RAM allocated for VM
2.0 GB of swap
1 CPU
10 GB of free space for Oracle Homes (both infrastructure and database)

RAC2 – node number 2

1.0 GB of RAM allocated for VM
2.0 GB of swap
1 CPU
10 GB of free space for Oracle Homes (both infrastructure and database)

Yes there is a difference between memory sizes in nodes – start OUI on node number 1 with more allocated memory.

Common configuration:

/etc/hosts

10.10.20.129    rac1.localdomain rac1
192.168.226.10  rac1-priv.localdomain rac1-priv
10.10.20.130    rac2.localdomain rac2
192.168.226.20  rac2-priv.localdomain rac2-priv
10.10.20.200    rac-cluster
10.10.20.210    rac1-vip.localdomain rac1-vip
10.10.20.220    rac2-vip.localdomain rac2-vip

RAC1-VIP and RAC2-VIP has to be assigned to public network and not configured during an installation. There is an additional entry for rac-cluster which is SCAN interface and has to be in same network as both VIP interfaces and not configured during a configuration time.
SCAN interface is a single entry point for cluster – for more information you can see RAC documentation.

Now is time to start OUI and install a Grid Control infrastructure. Below some installation tips:

Installation option – choose Install and configure Grid Infrastructure for Cluster
Installation type – Choose Typical installation
SCAN name – type rac-cluster
Add both nodes – RAC1 and RAC2 with proper VIP – note there is no need to private name anymore
Test SSH connectivity between nodes and click Setup there are any problems
Choose 10.10.20.x subnet as public and 192.168 as private
Specify a Oracle Base and Software location (which is a Oracle Home for Grid Infrastructure) - note that Oracle Home for Grid has to be in different location that Oracle Base
Select a disk for ASM group – it will be used for vote and configuration file too – if required change a Discovery Path to correct value (ex. /dev/sdc* for block devices or ORCL:* if you are using ASMLib)
If there are any problem with kernel setting or missing packages solve it before installation start – you can ignore memory, swap and NTP – but you have to have at least memory size specified above.

After about 15 minutes there is a time for a last step – execute a root.sh to configure and start cluster infrastructure. Run root.sh on node with more memory first.

Oracle Database Oracle Home

Just perform a standard installation of Oracle 11gR2 binaries without database creation.

RAC One Node patch

This is a best time to install a patch which adds One Node support to our database Oracle Home. Why ? Because it has to be installed when DB is down, so before creation of database we don’t need any other actions. Patch number is RACONENODE_p9004119_112010_LINUX.zip and can be found on Oracle Support Pages.

Database creation

The most important thing is to create a database only on one node. On the first screen a RAC database has to be chosen and then only one node (ex. rac1) has to be selected.
The next important thing is storage for a database. In our example all database files will be placed in ASM disk group. This same used to keep Grid Infrastructure cluster configuration.
All other configuration settings don’t have any impact on RAC One Node configuration.

Service configuration

A new service has to be added to support a RAC One Node configuration. This service will be used in our client configuration and will be entry point to our database.


srvctl add service -d testone -s serviceone -r testone1

where

testone – is a database name
serviceone – is a service name
testone1 – is a instance name created in previous point

RAC One node configuration

When database and service are up there is a time to start a RAC One Node configuration.
To do that a raconeinit has to be started.


Candidate Databases on this cluster:
#      Database        RAC One Node    Fix Required
===     ========        ============    ============
[1]      testone             NO              N/A
Enter the database to initialize [1]:
Database testone is now running on server rac1
Candidate servers that may be used for this DB:   rac2

Enter the names of additional candidate servers where this DB may run (space delimited): rac2

Please wait, this may take a few minutes to finish.
Database configuration modified.

After that command a new configuration should be in place. Current status can be checked with following command:raconestatus


RAC One Node databases on this cluster:

Database UP Fix Required        Current Server                    Candidate Server Names
======== == ============ ==============================  ========================================
testone  Y       N                                rac1                                 rac1 rac2

Available Free Servers:

RAC One node operations

Main RAC One Node operation is moving an instance between nodes. That operation can be done using Omotion tool. Here is a example of Omotion execution


RAC One Node databases on this cluster:

#      Database                    Server                      Fix Required
===     ========        ==============================          ============
[1]      testone                                  rac1                N
Enter number of the database to migrate [1]:
Specify maximum time in minutes for migration to complete (max 30) [30]: 5
Available Target Server(s) :
#            Server            Available
===     ==================      =========
[1]                   rac2          Y
Enter number of the target node [1]:

Omotion Started...
Starting target instance on rac2...
Migrating sessions...
Stopping source instance on rac1...
Omotion Completed...

=== Current Status ===
Database testone is running on node rac2

In that example database instance has been moved from node rac1 to node rac2. Instance on rac1 has been closed in transaction mode. In that scenario there was no remaining session on rac1 instance. When there are outstanding sessions/transactions on source node (in that case rac1) Omotion will shutdown that instance in transaction mode and then after time out will shutdown in abort mode – see example below.


RAC One Node databases on this cluster:

#      Database                    Server                      Fix Required
===     ========        ==============================          ============
[1]      testone                                  rac1                N

Enter number of the database to migrate [1]:
Specify maximum time in minutes for migration to complete (max 30) [30]: 5
Available Target Server(s) :
#            Server            Available
===     ==================      =========
[1]                   rac2          Y

Enter number of the target node [1]:
Omotion Started...
Starting target instance on rac2...
Migrating sessions...
Stopping source instance on rac1...
Timeout exceeded, aborting instance...
Omotion Completed...

=== Current Status ===
Database testone is running on node rac2

Client configuration
How is look like from client perspective?

TAF not configured – session has to be reconnected after instance migration
TAF configured in client TNS – only current transaction has to be rollback.

TAF example:
tnsnames.ora


testone =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac-cluster)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = testone)
 (FAILOVER_MODE=
(TYPE=select)
(METHOD=basic))
)
)

Take a look on address - there is no VIP any more - now SCAN name has to be entered
in TNS alias and resolved via DNA or hosts as well as all RAC VIP (rac1-vip and rac2-vip).
SQL*Plus test


sqlplus system@testone
SQL> select instance_name, host_name from v$instance;

INSTANCE_NAME      HOST_NAME
----------------   ----------------------------------------------------------------
testone_1          rac2


SQL> select * from test;

  ID
----------
   1
   2
   3
   4
   5
   6
   7

7 rows selected.

SQL> select instance_name, host_name from v$instance;

INSTANCE_NAME      HOST_NAME
----------------   ----------------------------------------------------------------
testone_1          rac2


Omotion has been started.

SQL> select instance_name, host_name from v$instance;
select instance_name, host_name from v$instance
*
ERROR at line 1:
ORA-25402: transaction must roll back


SQL> rollback;

Rollback complete.

SQL> select instance_name, host_name from v$instance;

INSTANCE_NAME      HOST_NAME
----------------   ----------------------------------------------------------------
testone_2          rac1


SQL> select * from test;

  ID
----------
   1
   2
   3
   4
   5
   6

6 rows selected.

SQL>

After migration there was two changes - instance name and host name has been changed - it's not like in typical failover clustre
where instance is migrated from one host to other. In RAC One Node a new instance
is started on second node and during a migration time this configuration is working
as typical RAC.

This is end of part one - next part with more test and operations soon.

Oracle RAC 11R2 and VMWare

2009-12-03T21:27:00.004Z

Hi,

Last week I spend on installation and configuration Oracle RAC 11R2 on VMWare.
This nothing special but I want to do that on my laptop with Windows XP and 4 GB of RAM.
Until know it was enough RAM to install Oracle RAC in 9i, 10g and 11R1 but with 11R2 it is a more complicated.

First of all Oracle required 1.5 GB for Grid infrastructure or 2.5 GB for Grid Infrastructure
and database. If you multiple it by 2 - it is 8 GB as a minimum ;) Hmmm we have only half of it.
First attempt was to install it with my original settings - 1 GB RAM and 1 GB swap for each of VM.
It hung during root.sh script at the end of installation.

After that I decided to increase memory one of VM and add swap for both. New configuration was
on node 1 - 1.5 GB RAM and 2 GB swap,
on node 2 - 1.0 GB RAM and 2 GB swap.

That was it. Node with bigger RAM was used to start OUI and was first in cluster.
After successful installation of Grid Infrastructure I decided to decrease SGA size for ASM instances to required minimum (136 MB) and I have started a database installation and configuration. I have installed Oracle binaries and then I have started manually DBCA to create a database - of course SGA has been to required minimum (about 370 MB).
After 25 minutes I have had a working environment.

Next I have installed a patch for RAC One node - a new option for run Oracle in fail over cluster.
A new post about that should appear in a few days.

regards,
Marcin

Have to read and remember

2009-11-04T20:06:00.007Z

Hi,

Third post today (all very short) but I have to clean up my workspace and close all tab in Firefox.
As I always has problem with bookmarks there is a list of interesting documents which I have read and kept open from some time to add it to blog.

Jonathan Lewis - old but very important about indexes.
James Morle's blog - new stuff is coming
Oracle CBO development group blog - some new papers ex. Upgrade from 10g to 11g - CBO changes
Uwe Hesses blog - is count(col) better than count(*)
Miladin Modrakovic's blog - New Oradebug feature in 11R2

Update:
I have forgot about Richard Foote's blog - two entries - Open World Highlights
and another entries about index rebuilding - I'm on Richard side and I prefer to go out on Sunday afternoon ;)

VMWare and CentOS - 2 virtual CPU

2009-11-04T19:59:00.003Z

If you want to run virtual machine with 2 virtual CPU and CentOS 5 (RedHat 5) as a guest OS just read that note. Without a special settings a CentOS doesn't boot properly and will hung on UDEV service.
I have tried it and now I can simulate a 2 CPU in my VMPlayer and test some Jonathan Lewis staff about index explosion.

Naming Convention and Oracle Forms

2009-11-04T19:56:00.001Z

I have been asked to create a test / dev environment to migrate Oracle Forms application from Forms 6i (Windows based) to Forms 10g on Linux box. There was about 30 files so whole operation looked very easy – just copy files and setup a Forms service.
I was really surprised when I test a main form – it didn’t start at all – just blank screen in browser.
I have compiled it and all related PL/SQL libraries once again in Forms Builder and there were no errors. So I decided to compile that same form and libraries on Linux box. Five from six libraries have been compiled without any errors but one has had an error message. Very strange for me – that other library has been not found. I have left it for a while and I was trying to compile form itself. This same kind of error appears – some of PL/SQL functions have not been found. But why ? There were PLX and PLL files with “missing” PL/SQL procedures and functions. I have used “strace” tool and I have compiled a forms again. In “strace” output file I have found references to missing files – OK not missing but with a little bit different names – “p” instead of “P”.
And this is a clue of this writing – developers didn’t use any naming convention for file names and links for that files – Windows is not case sensitive so if you call form “Form1” or “FORM1” this is still this same file – but not on case sensitive OS like Linux or other UNIX based systems. I have seen a lot of form which called other forms using not case sensitive names – don’t do that because in case of migration you will have a lot of additional work just to change forms names inside code.
Forms builder added his part too – if you attached a library it is using a file name as a reference to library and it is very important to keep file name case sensitive.

What can happen if you have "\p" in your ORACLE_HOME

2009-10-02T16:17:00.005+01:00

Hi, Today I have hit very strange error when I was trying to install Grid Control 10.2.0.5 on Windows. This installation is split into two parts: installation of Grid Control 10.2.0.1 and then patch to 10.2.0.5. First part has been done without any problems but I have made a mistake. I have setup a beginning of all ORACLE_HOMEs to “e:\oracle\product\10.2.0\”. Grid Control creates itself 3 different ORACLE_HOME (one per DB, OMS and agent) using entered path as a prefix. A whole installation of 10.2.0.1 has been performed without any problems and I have started an upgrade to 10.2.0.5. During a configuration phase an OC4J deployment has been finished with error. I start to investigate and I was looked into OUI log file C:\Program Files\Oracle\Inventory\logs\installAction<...>.log


Can't find unicode character property definition via main->r or r.pl at unicode/Is/r.pl line
oracle.ias.sysmgmt.repository.plugin.advanced.apache.parser.ParserException: parsing e:\oracle\product\10.2.0\oms10g\Apache\Apache\conf\ssl.conf:

Similar error is described in Metalink note 563468.1 but it is related to Oracle Application Server and OPatch utility. A cause of that error is a ORACLE_HOME value. Perl is interpreting the string "\p" in the Oracle home path as a regular expression. It is a little bit funny as Oracle is recommending an ORACLE HOME looking like ‘\oracle\product\’ I didn’t find any patch related to that error so I have just installed a Grid Control using a patch without “\p” string.

Trace access errors in Oracle.

2009-09-23T13:56:00.003+01:00

There are several possible causes for object access error in application:

Application upgrade
Deploying new rules for security
Hardcoded schema names

Oracle common errors for those situations are:


ORA-00942: table or view does not exist
ORA-01031: insufficient privileges

Depends of application those errors can appear in application log files or only on user screen. Sometimes it is very difficult to find out a root cause of that kind of error – especially when it is out of the box application without proper logging and tracing possibility.
Oracle logging interface is very powerful and it allow a system administrator to setup a logging of above errors into Oracle alert.log file and into sessions trace files. To enable that functionality a trace level for error has to be increased – it can be done on system or session level. Of course for application a system level is more useful.
Following steps has to be performed on system or session level to trace a ORA-00942 error.

- for all sessions


alter system set events '942 trace name errorstack level 1';

for current session


alter session set events '942 trace name errorstack level 1';

Test user is executing following SQL statement:


SQL> select username from dba_users;
select username from dba_users
ERROR at line 1:
ORA-00942: table or view does not exist

Results in alter.log and trace files


Tue Sep 22 11:31:58 2009
Errors in file /oracle/app/diag/rdbms/pioro/pioro/trace/pioro_ora_23409.trc:
ORA-00942: table or view does not exist

Trace file details


Trace file details
*** 2009-09-22 11:31:58.607
*** SESSION ID:(152.1313) 2009-09-22 11:31:58.607
*** CLIENT ID:() 2009-09-22 11:31:58.607
*** SERVICE NAME:(SYS$USERS) 2009-09-22 11:31:58.607
*** MODULE NAME:(SQL*Plus) 2009-09-22 11:31:58.607
*** ACTION NAME:() 2009-09-22 11:31:58.607

----- Error Stack Dump -----
ORA-00942: table or view does not exist
----- Current SQL Statement for this session (sql_id=chvsmttqjzjkn) -----
select username from dba_users

Using above information from trace file DBA can figure out where a problem is and find a proper way to solve it.

regards
Marcin

SwingBench

2009-08-30T17:03:00.015+01:00

First time I have seen that tool a few years ago and I was really surprise how useful it was. Now I have come back to it and a new version (release 2.3) in my opinion is very mature and can be used for many various performance tests.
Let me present show main features:

Few different benchmark test
- OLTP Order Entry PL/SQL version
- OLTP Order Entry Java version
- OLTP Calling Circle
- Stress test
- DSS Sales History
- Custom

Possible outputs
- Transaction per minute
- Transaction Response time
- DML operation per minute
Many configuration options ex:
- Number of concurrent users
- Min and max delay time between transactions
- Logon delays
- Using of connection pooling
Distributed client coordinator

All features are described on Dominic Giles (author) homepage in documentation section. This document describe version 2.2 and a newest version is 2.3 and some additional configuration possibilities has been added from XML to GUI.

There are a lot of possible configurations and tests covered by SwingBench. Combining a customer defined transactions in tool with application knowledge SwingBench can be used as a small brother of Oracle Real Application Testing – of course it required much more work to define a workload but it is for free.

How to use it – it is very simple please take a look on my quick start guide.

Download a package
Unzip into target directory – ex. c:\SwingBench
Edit a SwingBenchenv.bat file – only path below should be changed

REM Set the following to reflect the root directory of your Java installation

set JAVAHOME=C:\Program Files\Java\jre1.6.0_07

REM Set the following to the directory where you installed swingbench

set SWINGHOME=C:\swingbench

REM Set the following to the location of your TimesTen install (optional)

set TTHOME=C:\TimesTen\tt70

REM If you don't have the ORACLE_HOME environment variable set uncomment the following and change it to reflect your environment

set ORACLE_HOME=C:\oracle\product\10.2.0\db_1

Go to C:\swingbench\winbin and start oewizard.bat - configuration wizard
Click next and choose a create schema
Enter a server IP, database name and sys password
Enter schema name and tablespace location - if you have a EE edition you can clik Use Partitioning option
Choose a size of demo schema using sliders
Confirm all settings and click Finish
Wizard will display a additional window with Wizard log
Click OK on confirmation screen
Click Cancel in main Wizard Window
You can start now a SwingBench - c:\swingbench\winbin\swingbench.bat

And change a ConnectionString to proper one for your environment
Click Start button and enjoy a performance test

This is all for a first time. Now I'm trying to find a fastest way to catch up
a workload on existing database and transform it into a SwingBench tests.

I hope it help some people to test a performance of existing and future installations.

regards,
Marcin

Slow network connection in 11g

2009-08-19T21:00:00.007+01:00

Today I found a thread on Oracle forum about a slow connection and I recall that I solved it in past.
Now I have found a few notes on Metalink (ex. 803838.1) which confirm my investigation that Oracle 11g is using DNS to resolve names instead of /etc/hosts file on Unix box. If your server is not registered in DNS or if there is no DNS server which was specified in /etc/resolv.conf you can have a connection problem.

A fastest way to solve it on Linux is to remove "nameserver" entry from /etc/resolv.conf file if you don't need a DNS on the server otherwise you have to be sure that your Oracle server is registered in DNS.

On Solaris a solution is a little bit different - change line in /etc/nsswitch.conf into

ipnodes: files [NOTFOUND=continue] dns

and do not delete line starting with hosts.

regards,
Marcin

Application Server - Forms session monitoring

2009-08-13T00:10:00.005+01:00

Hi,

Oracle Application Forms server has some build in logging tools which allow a DBA to trace a performance or debug data. In Oracle documentation you can find a example how to use that tools.
If you check a URL you can notice that all examples are related to default forms environment

http://yourserver/forms/frmservlet?serverURL=/forms/lservlet/perf

But how to use that if you have a few different environment deployed on server (ex. a few applications) and you are using URL which include a environment name

http://yourserver/forms/frmservlet?config=myconfig.env

You have to realize (I spend some time several times on it so I decide to create that post)
that "serverURL" and config are a parameters in your URL and according to HTTP standard for GET method we can combine parameters using "&" character.

http://yourserver/forms/frmservlet?config=myconfig.env

&serverURL=/forms/lservlet/perf

That's all folks and I will try to remember that using my blog memory

 :-)


regards,
Marcin

Oracle indexes

2009-08-13T00:03:00.003+01:00

Hello,

Jonathan Lewis just started a series of posts about indexes. This is one of these things which has a flag "must to read". Please find some examples:
- Index fragmentation
- Index explosion part 1
- Index explosion part 2

regards,
Marcin

ASM datafile operation in NOMOUNT

2009-08-10T21:52:00.008+01:00

Hi

Several times I have been asked to manipulate with data files placed on ASM. In 11g there is a very nice feature – Oracle added a cp command to asmcmd tool but in 10g we have to use database instance to do all work.
One of very common task is coping a data file from one ASM disk group to other one. It can be done via RMAN copy command but this command adds an entry to RMAN repository. There is another method to do that and I found it in Oracle documentation (Yes, yes, RTFM). Convert command can be used not only to copy data file from ASM to file system and vice versa but to copy between ASM groups too.

Example


RMAN> convert datafile '+DATA/PIORO/DATAFILE/ORABPEL.260.692892311' format '+DATA/PIORO/copyofORABPEL';
Starting backup at 10-AUG-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input filename=+DATA/pioro/datafile/orabpel.260.692892311
converted datafile=+DATA/pioro/ copyofORABPEL
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:25
Finished backup at 10-AUG-09

ASM to file system


RMAN> convert datafile '+DATA/PIORO/DATAFILE/ORABPEL.260.692892311' format '/home/oracle/copyofORABPEL';
Starting backup at 10-AUG-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input filename=+DATA/pioro/datafile/orabpel.260.692892311
converted datafile=/home/oracle/copyofORABPEL
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:26
Finished backup at 10-AUG-09

Keep in mind that this command doesn’t add any information to control file so you want to rename a file you have to maintain it manually.

This command is working in NOMOUNT state too but a additional parameter is required


[oracle@piorovm ~]$ rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Mon Aug 10 22:15:44 2009
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
connected to target database: pioro10 (not mounted)

RMAN> convert datafile '+DATA/PIORO/DATAFILE/ORABPEL.260.692892311' from platform 'Linux IA (32-bit)' format '/home/oracle/copyofORABPEL';
Starting backup at 10-AUG-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=DISK
channel ORA_DISK_1: starting datafile conversion
input filename=+DATA/pioro/datafile/orabpel.260.692892311
converted datafile=/home/oracle/copyofORABPEL
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:26
Finished backup at 10-AUG-09

If you need to copy archive log (ex. From flash recovery area) you can use a RMAN copy command - this is running only in MOUNT state.


RMAN> copy archivelog '+data/PIORO/ARCHIVELOG/2009_07_28/thread_1_seq_58.266.693419455' to '/home/oracle/arch_1_58';
Starting backup at 10-AUG-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive copy
input archive log thread=1 sequence=58 recid=1 stamp=694559795
output filename=/home/oracle/ arch_1_58 recid=3 stamp=694560324
channel ORA_DISK_1: archivelog copy complete, elapsed time: 00:00:07
Finished backup at 10-AUG-09

But it can be a dangerous command because additional entry to control file is created and if you delete this file from a file system without sending that information to RMAN archive log backup could failed. To avoid it always CROSSCHECK ARCHIVELOG ALL before archive log backup or use option “skip inaccessible”.
This is it for today but I have some additional ideas to check.

Marcin

andLinux and Oracle

2009-08-03T20:05:00.004+01:00

Hi,

Please find my short paper about installation of andLinux and Oracle 10g.
It can be a useful when you want to have a Linux version of Oracle on your Windows machine without any virtualization tool.
andLinux is a Ubuntu distribution and is not officially supported but it is working fine. It was some problem with path to "basename" and "awk" during a linking (error related to nnfyboot file).

regards,
Marcin

Free version of ASH

2009-07-27T20:31:00.003+01:00

Hi,

If you have a performance problem and don't have license to use Active Session History
you can use a free version of it. It is not using a direct SGA access but it is designed by Kyle Hailey
- one of OEM Performance tab designer.

When I read about direct access to X$ table using a oradebug utility on
Miladin Modrakovic's blog I started to wonder if this two technologies can be combined.
Unfortunately I have tested direct access using oradebug and CPU usage is unexpected high on my VM machine and in that case direct access can disrupt our observations.

regards,
Marcin

RMAN backup from active database

2009-07-22T20:29:00.006+01:00

Hello,
During my test with ASM I have broken my database. I couldn’t restore an ASM structure and I had to recreate database. Because it was a test environment I hadn’t backup of it but I remembered that I hopefully had a standby database. It should be easy to duplicate standby and recreate my test DB once again.
I decided to use a new feature of RMAN to restore database directly from standby. I created a script and I have started a RMAN.


run {
set newname for datafile 1 to '+DATA';
set newname for datafile 2 to '+DATA';
set newname for datafile 3 to '+DATA';
set newname for datafile 4 to '+DATA';
set newname for datafile 5 to '+DATA';
set newname for datafile 6 to '+DATA';
set newname for tempfile 1 to '+DATA';
duplicate target database to PIORO from active database;
}

rman target sys/oracle@piorost auxiliary sys/oracle@pioro cmdfile=dup_back.txt msglog=dup_back.log

What was my surprise when I found following message in log file.


Starting backup at 22-JUL-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=87 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/oracle/oradata/pioro/sysaux01.dbf
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 07/22/2009 10:01:58
ORA-19504: failed to create file "+DATA/pioro/datafile/sysaux01.211
channel ORA_DISK_1 disabled, job failed on it will be run on another channel
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 07/22/2009 10:01:58
RMAN-03015: error occurred in stored script Memory Script

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 07/22/2009 10:01:58
ORA-19504: failed to create file "+DATA/pioro/datafile/sysaux01.211

Recovery Manager complete.

It was very strange because this configuration was running without any problems before my tests.

After few tries with changing a script (adding a db_file_name_convert, changing a new name) I decided to check Metalink – unfortunately there is a note number 787753.1 that this is a bug in 11.1.0.7 and will be fixed in 11.2 ! My test case was a little bit different than Metalink example because my standby DB has been placed on file system and end results was identical. I have to backup a standby to disk than restore it on my test machine.

After that I hit into another strange issue – because I have restored a control file from standby database there were standby logs and online redo logs had a wrong path. But it should be easy, isn’t? Just add new online logs on ASM and drop old one.


SQL> select group#, member from v$logfile;

1 /oracle/oradata/pioro/redo01.log
2 /oracle/oradata/pioro/redo02.log
3 /oracle/oradata/pioro/redo03.log
4 /oracle/oradata/pioro/standby01.log
5 /oracle/oradata/pioro/standby02.log
6 /oracle/oradata/pioro/standby03.log

SQL> alter database add logfile ('+DATA') size 50M;
alter database add logfile ('+DATA') size 50M
*
ERROR at line 1:
ORA-19528: redo logs being cleared may need access to files

Hmmm another nice issue – yes I have cleared logs due to reset logs option but DB is open now and I’m adding a new redo file not dropping it.
Let’s try to drop something


SQL> alter database drop logfile group 6;
alter database drop logfile group 6
*
ERROR at line 1:
ORA-19528: redo logs being cleared may need access to files

I have restarted DB in clear mode but it didn’t help. In my opinion it could be a problem with control file. So I have decided to force DB to recreated a control file using – alter database backup control file to trace – command.
It was a solution – after that I could drop and add log files groups.


SQL> alter database add logfile ('+DATA') size 50M;
Database altered.

SQL>  alter database drop logfile group 2;
Database altered.

I’m not sure if similar problem with redo logs appear in previous version of Oracle but main old approach to don’t install a first release of Oracle DB on production systems seems to be still actual and it give me more safety.

regards,
Marcin

New performance papers from Method R

2009-07-02T22:08:00.004+01:00

Cary Millsap public ate a new paper about software performance - Fundamentals of Software Performance Quick Reference Card. Check a other brilliant paper too - Making Friends with the Oracle Database.

regards,
Marcin

ASM and BBED

2009-06-30T22:38:00.002+01:00

As I mentioned in my previous post I make some research with ASM and Oracle block edit.
Please find my short paper about that. This version doesn't include a table protected via Oracle Vault.

If you have any remarks please don't hesitate and drop me a mail.

regards,
Marcin

High CPU after DB Console start in 11g

2009-06-24T20:31:00.002+01:00

Hi,

If you have a problem with Oracle sessions taking a high CPU load in 11g after you start a DB Console check if any of these session is executing following query:

SELECT EXECUTION_ID, STATUS, STATUS_DETAIL FROM MGMT_JOB_EXEC_SUMMARY
WHERE JOB_ID = :B3 AND
TARGET_LIST_INDEX = :B2 AND EXPECTED_START_TIME = :B1

If yes, you can fix it using that steps:

stop DB Console

emctl stop dbconsole
connect as sysman using SQL*Plus execute both procedures

SQL> execute MGMT_PAF_UTL.STOP_DAEMON;
SQL> execute MGMT_PAF_UTL.START_DAEMON;
Start DB Console

emctl start dbconsole

This is reported as bug number 7182962

Marcin

Strange DataGuard behaviour

2009-06-24T20:08:00.003+01:00

I have been tested a new features of 11g Data Guard. During that I have change a database protection mode into Maximum Protection
and after that make some test with startup and shutdown of both databases.
As expected when primary database was online I have got following error on console and warning in alert.log

SQL> shutdown immediate
ORA-01154: database busy. Open, close, mount, and dismount not allowed now

Alert.log
Attempt to shut down Standby Database
Standby Database operating in NO DATA LOSS mode
Detected primary database alive, shutdown primary first, shutdown aborted

That’s expected results. But in next test case when I shutdown a primary database I still got that same error !

SQL> shutdown immediate
ORA-01154: database busy. Open, close, mount, and dismount not allowed now

I have rerun disabling command for recovery process and checked if recovery mode is disabled

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
SQL> SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS;

RECOVERY_MODE
-----------------------
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE

11 rows selected.

Hmmm .... of course I can shutdown that database using abort but I don’t want to.
Let’s check what else can prevent database to shutdown – a little check what processes are running and EUREKA – dataguard broker is still running. So:

SQL> alter system set dg_broker_start=FALSE scope=memory;
System altered.

SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>

Uffff .....
Other question is why ? If primary database was down, standby database should be stopped without any other circumstances.

regards,
Marcin

ASM and RedHat 5

2009-06-17T14:17:00.002+01:00

I’m really angry on me because I spend another 30 min fighting with problem which I have already solved several times. Oracle ASMLib is looking for a disks using /dev/dm-* devices which are not created by default in Red Hat 5. But if you manually map any device as Oracle ASM disk it will be working until next reboot.

So if after reboot in Red Hat 5 your ASM disks disappear check if you done following things:

Comment line in /etc/udev/rules.d/ 90-dm.rules

#KERNEL=="dm-[0-9]*", ACTION=="add", OPTIONS+="ignore_device"

it creates a /dev/dm-* devices after system reboot

Check in /etc/sysconfig/oracleasm if disk are not excluded

# ORACLEASM_ENABELED: 'true' means to load the driver on boot.
ORACLEASM_ENABLED=true
# ORACLEASM_UID: Default user owning the /dev/oracleasm mount point.
ORACLEASM_UID=oracle
# ORACLEASM_GID: Default group owning the /dev/oracleasm mount point.
ORACLEASM_GID=oinstall
# ORACLEASM_SCANBOOT: 'true' means scan for ASM disks on boot.
ORACLEASM_SCANBOOT=true
# ORACLEASM_SCANORDER: Matching patterns to order disk scanning
ORACLEASM_SCANORDER=""
# ORACLEASM_SCANEXCLUDE: Matching patterns to exclude disks from scan
ORACLEASM_SCANEXCLUDE=""

Check a file privileges

After that you should see your ASM disks.

Regards,
Marcin

BBED and Oracle Vault

2009-06-16T09:08:00.002+01:00

Hi,

I was always wondering how much Oracle Vault is preventing your data to avoid unauthorized accesses by DBA. To be honest I didn’t dig into Oracle code looking for security hole but after I recall about Block Editor I decided to try that tool.

And it is working – I was able to read a data from data file based on file system without any problem. OK there was some work to do like find out a block number and so on but it wasn’t really hard.

After that I tried to use BBED on ASM based file but without luck, but I’m a person who don’t give up so easy. I was thinking about some RMAN copy and it is required a additional disk space and we can deal only with copy of running DB. I have research a internet and I have found amazing stuff about ASM - Luca Canali web site - I read it and I found a solution how to read a data from running DB. It is possible to read a block from ASM into file system, edit it and copy back into running DB. Oracle Vault as expected didn’t recognize that block has been edited.

So is it a possibility to protect data ? In my opinion data encryption is a good choice until someone don’t find a way how to encrypt and decrypt it ;)

Working example of my work should be here in a few days.

regards,
Marcin

BBED - Oracle Block Editor

2009-06-08T15:02:00.003+01:00

Hi,

I just found that paper about Oracle Block Editor (BBED) in my old ebook directory.
It can be very useful but very dangerous too. Please read that document very careful
and never try it on production database.

Here is a link to that paper.

regards,
Marcin