steps to Migrate ASM diskgroups from one SAN to another SAN

1) Backup all your databases and valid the backup .

2) Add the new path (new disks from the new storage) to your asm_disktring to be recognized by ASM:

Example:

SQL> alter system set asm_disktring = ‘/dev/dellpowerc*’ , ‘/dev/dellpowerh*’;

Where: ‘/dev/dellpowerc*’ are the current disks.
Where: ‘/dev/dellpowerh*’ are the new disks.

3) Confirm that the new disks are being detected by ASM:

SQL> select path from v$asm_disk;

4) Add the new disks to your desired diskgroup:

SQL> alter diskgroup <diskgroup name> add disk
‘<new disk 1>’,
‘<new disk 2>’,
‘<new disk 3>’,
‘<new disk 4>’,
.
.
.
‘<new disk N>’;

5) Then wait until the rebalance operation completes:

SQL> select * from v$asm_operation;

6) Finally, remove the old disks:

SQL> alter diskgroup <diskgroup name> drop disk
<disk name A>,
<disk name B>,
<disk name D>,
<disk name E>,
.
.
.
<disk name X>;

7) Then wait until the rebalance operation completes:

SQL> select * from v$asm_operation;

8) Done, your ASM diskgroups and database have been migrated to the new storage.

Note: Alternatively, we can execute add disk & drop disk statements in one operation, in that way only one rebalance operation will be started as follow:

SQL> alter diskgroup <diskgroup name>
add disk ‘<new device physical name 1>’, .., ‘<new device physical name N>’
drop disk <old disk logical name 1>, <old disk logical name 2>, ..,<old disk logical name N>
rebalance <#>;

This is more efficient than separated commands (add disk & drop disk statements).

Oracle Clusterware

Oracle Clusterware is the cross platform cluster software required to run the Real Application Clusters (RAC) option for Oracle Database.

It provides the basic clustering services at the operating system level that enable Oracle software to run in clustering mode. In earlier versions of  Oracle (version 9i and earlier), RAC required a vendor supplied clusterware like Sun Cluster or Veritas Cluster Server and with a seperate software for  Linux and Windows.

Oracle clusterware naming Evolution:

In 9i     – Third Party softwares  (Sun Cluster or Veritas Cluster Server)
In 10gR1-Cluster  Ready services (Oracle corporation)
In 10gR2- Oracle Clusterware     (Oracle corporation)

Oracle Clusterware stores configuration information in the Oracle Cluster Repository (OCR),
which must be located on shared storage available to all nodes. The Membership of the cluster is determined using a  file known as the voting disk, which must also be available to all nodes. Voting Disk  acts a tiebreaker during communication failures and  Consistent heartbeat information from all the nodes is sent to voting disk when the cluster is running .

Oracle Clusterware comprises a set of daemons(Background process).

1.Cluster Ready Services Daemon(crsd)

This component manages high availability operations within the cluster. Objects managed by CRS are known as resources and can include databases, instances, services, listeners, virtual IP addresses, and application processes. By default, CRS manages four application process resources: Oracle Net listeners, virtual IP addresses, the Global Services Daemon (GSD), and the Oracle Notification Service (ONS).

Configuration information about each resource is stored in the Oracle Cluster Repository
(OCR). When the status of a resource changes, CRS generates an event.

CRS monitors resources, such as instances and listeners. In the event of the failure of a resource, CRS will attempt to automatically restart the component.

2.Oracle Cluster Synchronization Service Daemon (OCSSD)

This component manages the cluster configuration by controlling which nodes are members of the cluster. When a node joins or leaves the cluster, CSS notifies the other nodes of the change in configuration.
If this process fails, then the cluster will be restarted.

3.Event Volume Manager Daemon (evmd)

The EVM component publishes events created by Oracle Clusterware. It will restart Automatically on failures and death of the evmd process does not halt the instance

4.Process Monitor Daemon (oprocd)

Oprocd provides the I/O Fencing solution for the Oracle Clusterware. It is the process monitor for the oracle clusterware and it uses the hang check timer or watchdog timer (depending on the implementation) for the cluster integrity.

Dbms_Stats with method_opt

Hi,

Platform: windows server 2003
Database : 10.2.0.4

Recently we have faced an problem with Gathering statistics which leads to
an oracle internal error (Ora-00600).

There is a  production database which has doing some  large transactions.

we usally gather the statistics for this database bettweeen the interval of two weeks .

we will gather the ststistics using the folowing method

exec dbms_stats.gather_schema_stats(ownname=>’user01′,estimate_percent=>dbms_stats.auto_sample_size,cascade=>true);

Last night we have gathered statistics for the a schema .
Suddenly  Only the next day morning Our users reported that some Oracle errors is
flashed  on one of our applications when accessing some particular form .

Then we tried to find the table on which that particular form is been accessing,
and we manually quried a simple statement on tht table:

SQL> select * from Tabular_data;
select * from Tabular_data
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [19004], [], [], [], [], [], [], []

After some search about the argument [19004] we finally understood that it is as a Bug .

This bug is due to the miscalculation of the Oracle’S Automatic Historgram collection on the table
columns .

since we dint mention any method_opt parameter on the above statement , it leads to its default value

method_opt=>’FOR ALL COLUMNS SIZE AUTO’ (Oracle 10g’s Default value)

So we decided to include the method_opt parameter with the size 1 which is the default value in
Oracle 9i.

method_opt=>’FOR ALL COLUMNS SIZE 1′ (Oracle 9i’s Default value)

This method will disable the collection of Histograms.

if we cant loose histograms , Ofcourse the Another way to escape from this error is to lock the

Statistics for that particular table and to proceed with the older approach .
SQL> exec dbms_stats.lock_table_stats(‘USER01’, ‘TABULAR_DATA’);

PL/SQL procedure successfully completed.

SQL> SELECT stattype_locked FROM dba_tab_statistics

WHERE table_name=’TABULAR_DATA’;

STATTYPE_LOCKED
——————–
ALL

So you cant able to gather statistics on the locked table.

Namespaces

Can we able to create an index with the same name as the table name ?

Yes . Because the table and index resides in the different namespaces.

A namespace defines a group of object types, within which all names must be uniquely identified—by schema and name. Objects in different namespaces can share the same name.

These object types all share the same namespace:
• Tables
• Views
• Sequences
• Private synonyms
• Stand-alone procedures
• Stand-alone stored functions
• Packages
• Materialized views
• User-defined types

So  it is impossible to create a view with the same name as a table; at least, it
is impossible if they are in the same schema.

These object types each have their own namespace:
• Indexes
• Constraints
• Clusters
• Database triggers
• Private database links
• Dimensions
Thus it is possible for an index to have the same name as a table, even within the
same schema.

SQL> select namespace from v$librarycache;

NAMESPACE
———————————————
SQL AREA
TABLE/PROCEDURE
BODY
TRIGGER
INDEX
CLUSTER
OBJECT
PIPE
JAVA SOURCE
JAVA RESOURCE
JAVA DATA

11 rows selected.

Here there is an small test:

SQL> create table t1 as select *  from user_objects;

Table created.

SQL> create index t1 on t1(object_id);

Index created.

SQL>  CREATE OR REPLACE package t1 as (ENO VARCHAR2);
2  /
CREATE OR REPLACE package t1 as (ENO VARCHAR2);
*
ERROR at line 1:
ORA-00955: name is already used by an existing object

Here we are getting the error because the packages and tables resides in the same namespace.

Tracing a web application in Oracle

In a busy production environment with many active users, tracing a SQL session is time-consuming and complicated,
because processing SQL statements in any multitier system that uses a connection pool can span multiple processes,
or even different instances.

With Oracle Database 10g, Oracle rationalizes SQL tracing through a new built-in package, DBMS_MONITOR,
which encompasses the functionality of previously undocumented trace tools, such as the DBMS_SUPPORT package.
Now you can easily trace any user’s session from beginning to end—from client machine to middle tier to back end—and generate trace
files based on specific client ID, module, or action.

Now you can add your own reference to the trace file for your easy identification,

alter session set
tracefile_identifier =”webapp”;

Trace files generated by this command have the string value with “webapp”

The DBMS_MONITOR package has routines for enabling and disabling statistics aggregation as well as for tracing by session ID,
or tracing based upon a combination of service name, module name, and action name.

PL/SQL developers can embed calls into their applications by using the DBMS_APPLICATION_INFO package to set module and action names

The service name is determined by the connect string used to connect to a service. User sessions not associated with a specific service are handled by sys$users (sys$background is the default service for the background processes).
Since we have a service and a module name, we can turn on tracing for this module as follows:

SQL> exec dbms_monitor.serv_mod_act_trace_enable(service_name=>’test’, module_name=>’w3link’);

PL/SQL procedure successfully completed.

To Disable:

SQL>exec dbms_monitor.serv_mod_act_trace_disable(service_name=>’test’, module_name=>’w3link’);

PL/SQL procedure successfully completed.
Tracing For a particular session:
 
SQL> select sid, serial#, username
from v$session;

   SID     SERIAL#  USERNAME
     ——    ——-  ————
       103       4152  SYS
       107       2418  SYSMAN
       129         53  TEST
       130        561  HR
       141          4  DBSNMP
. . .
       168          1
       169          1
       170          1
15 rows selected.
SQL> exec dbms_monitor.session_trace_enable
(session_id=>129, serial_num=>53, waits=>true, binds=>TRUE);
PL/SQL procedure successfully completed.
SQL> exec dbms_monitor.session_trace_disable(129);
PL/SQL procedure successfully completed.

Importance Of SQL Design

If the sql statement  is designed poorly, nothing much can be done by optimizer or indexes

Few are the well known rules

-Enabling indexes to eliminate the need for full table scans

-Avoid Cartesian joins

–Use UNION ALL instead of UNION – if possible

–Use EXIST clause instead of IN – (Wherever appropiate)

–Use order by when you really require it – Its very costly

–When joining 2 views that themselves select from other views, check that the 2 views that you are using do not join the same tables!

–Avoid NOT in or NOT = on indexed columns. They prevent the optimizer from using indexes. Use where amount > 0 instead of where amount != 0

– Avoid writing where is not null. nulls can prevent the optimizer from using an index

– Avoid calculations on indexed columns. Write WHERE amount > 26000/3 instead of WHERE approved_amt/3 > 26000