NFS locking issue while data pump export - Linux-x86_64 Error: 37: No locks available

Yesterday, as usual the cron job triggered a datapump export job against a database on a Linux Server.
Immediately post running the export job it got failed. When i look into the dump logfile i found below sort of errors.

Export: Release 11.2.0.3.0 - Production on Sat Mar 8 05:53:37 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
;;;
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning and Automatic Storage Management options
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file "/oraexp/NTLSNDB/Data_Pump_Export_NTLSNDB_FULL_030814_0553_01.dmp"
ORA-27086: unable to lock file - already in use
Linux-x86_64 Error: 37: No locks available
Additional information: 10

I verified at database level whether the dump directory, its path and the proper read & write privileges are granted on the directory. Yes everything was fine at database end.

I believed this could be an issue of nfs mount option at OS level. We are using an NFS shared mount point for all of the servers it needs to get mounted with proper options on each server to get it used by the database. I could see this mount point is mounted properly with recommended options by Oracle.

Then i checked the logs at OS level, then i found the issue is related to nfslock services. The nfslock service is not running on this database.  this service helps the client to lock a file in the related NFS mount point on the server to create a file and make write operations.

>cat messages | grep lockd
Mar  8 04:03:31 demoserver kernel: lockd: cannot monitor 10.207.80.179
Mar  8 04:03:31 demoserver kernel: lockd: failed to monitor 10.207.80.179
Mar  8 04:20:27 demoserver kernel: lockd: cannot monitor 10.207.80.179
Mar  8 04:20:27 demoserver kernel: lockd: failed to monitor 10.207.80.179

Further i came to know that t the server got rebooted couple of days ago for a reason, after reboot the nfslock services did not startup automatically. So manually we started the services. Note that  If the nfslock services need to get auto start after a reboot then we need to use chkconfig nfslock on. Later the same has been taken care. hence onwards whenever the server gets rebooted the nfslock services will automatically startup.

cat messages | grep rpc
Mar  8 07:01:43 demoserver rpc.statd[12667]: Version 1.0.9 Starting
Mar  8 07:01:49 demoserver rpc.statd[12667]: Caught signal 15, un-registering and exiting.
Mar  8 07:01:49 demoserver rpc.statd[12745]: Version 1.0.9 Starting

You can manage the nfslock services by below commands.

service nfslock status
service nfslock start
service nfslock stop

After making sure that the services got started and the client could able to lock the file on the NFS file system on the server. we re-triggered the export job. It executed successfully.

Killing process in Unix

 To check running process in Unix,

Command- ps –ef

Here we can use “grep” option to find out any particular process,

Example-

To find out running processes for apache,

root@sunpstsrv01# ps -ef | grep http

webservd   587   584   0   Sep 01 ?           0:00 /opt/csw/apache2/sbin/httpd -k start

    root   584     1   0   Sep 01 ?           0:47 /opt/csw/apache2/sbin/httpd -k start

  nobody  1498  1494   0   Sep 01 ?           0:00 /usr/local/apache2/bin/httpd -k start

webservd   586   584   0   Sep 01 ?           0:00 /opt/csw/apache2/sbin/httpd -k start

webservd   588   584   0   Sep 01 ?           0:00 /opt/csw/apache2/sbin/httpd -k start

nobody  8860  1494   0   Sep 02 ?           0:00 /usr/local/apache2/bin/httpd -k start

  nobody  1499  1494   0   Sep 01 ?           0:00 /usr/local/apache2/bin/httpd -k start

  nobody  8861  1494   0   Sep 02 ?           0:00 /usr/local/apache2/bin/httpd -k start

  nobody  1500  1494   0   Sep 01 ?           0:00 /usr/local/apache2/bin/httpd -k start

  nobody  1501  1494   0   Sep 01 ?           0:00 /usr/local/apache2/bin/httpd -k start

  nobody  1502  1494   0   Sep 01 ?           0:00 /usr/local/apache2/bin/httpd -k start

  nobody  2832  1494   0   Sep 01 ?           0:00 /usr/local/apache2/bin/httpd -k start

webservd  6031   584   0   Sep 01 ?           0:00 /opt/csw/apache2/sbin/httpd -k start

To find out parent & child processes in unix.

Command- ptree- To print process tree

Example-

root@sunpstsrv01# ptree 8860

1494  /usr/local/apache2/bin/httpd -k start

                 8860  /usr/local/apache2/bin/httpd -k start

Here in above example we took any process id “8860” and used ptree command, we can see pid “1494” is a parent process for child process “8860”

Using parent PID we can get all running child processes id’s.

Example-

root@sunpstsrv01# ptree 1494

1494  /usr/local/apache2/bin/httpd -k start              

                1498  /usr/local/apache2/bin/httpd -k start

                1499  /usr/local/apache2/bin/httpd -k start

                1500  /usr/local/apache2/bin/httpd -k start

                1501  /usr/local/apache2/bin/httpd -k start

                 1502  /usr/local/apache2/bin/httpd -k start

                2832  /usr/local/apache2/bin/httpd -k start

                8860  /usr/local/apache2/bin/httpd -k start

                8861  /usr/local/apache2/bin/httpd -k start

                8862  /usr/local/apache2/bin/httpd -k start

Here we can see all child PID’s associated with Parent process ID “1494”

To kill Parent & child process,

Command- kill -9 ‘PID’

 

Example-

To kill apache process,

root@sunpstsrv01# kill -9 1494

 

Here we are killing parent process running for apache.

Most of the time if we killed parent process then child process associated with that gets killed.

We can confirm that by using “ps –ef “ command.

  

Zombie process in Unix

 It is a process that has completed execution but still has an entry in the process table, allowing the process that started it to read its exit status.

When a process ends, all of the memory and resources associated with it are de-allocated so they can be used by other processes. However, the process entry in the process table remains. The parent is sent a SIGCHLD signal indicating that a child has died; the handler for this signal will typically execute the wait system call, which reads the exit status and removes the zombie.

Zombies can be identified in the output from the UNIX ps command by the presence of a “Z” in the STAT column.

 

Example-

ps -el | grep 'Z'

With a normal ps -el command you see an output with in the second colum the state of the process. Here are some states:

S : sleeping

R : running

D : waiting (over het algemeen voor IO)

T : gestopt (suspended) of getrasseerd

Z : zombie (defunct)

The output under this text is an example. We can see that dovecot-auth is the zombie.

 

[root@s324 /]# ps -el | grep 'Z'

F S   UID   PID  PPID  C PRI  NI ADDR    SZ WCHAN  TTY          TIME CMD

1 Z     0  1213   589  0  75   0    -     0 funct> ?        00:00:00 dovecot-auth

Here 2^nd column “Z” indicates zombie process.

 

Most of the time zombie process can be killed by “kill -9 ‘Zombie PID’” but still if that zombie process is not being killed then we might need to restart that application related to process.

Foglight for ORACLE : An Advanced Performance Monitoring and Management for Oracle Databases

Database health is critical to ensuring business continuity. To maintain peak database performance, database administrators (DBAs) need unattended, 24x7 data collection and automated alerting. Unfortunately, native tools do not offer these capabilities. And even without the capabilities, these tools still impose excessive overhead onto systems that might already be experiencing performance problems.

         

However, with Quest’s Foglight® for Oracle, DBAs can easily detect, diagnose, resolve and report on performance issues wherever, whenever and however they occur.

 Foglight’s intuitive interface allows DBAs to assess enterprise-level performance at a glance. Through the interface, DBAs are instantly alerted to performance bottlenecks and can play back historical data to diagnose issues. To identify the root cause of performance problems and speed resolution, the solution offers built-in expert knowledge and workflows. And Foglight provides all of these capabilities with a minimal footprint, imposing no agent overhead on the monitored hosts.The solution can function as either a standalone application or as a component of a larger enterprise monitoring solution
 
Key Features

• Global View – Determine your most critical instance alarms and immediately take action to resolve performance issues, both for Oracle and across platforms.
• Built-in Intelligence – Understand the health and activity of your Oracle enterprise with a variety of integrated management functionality and performance views; this includes pop-up advice with correlated workflows.
• Adaptive Baselines – Track performance using automatic detection and calculation of normal ranges for all metrics, and receive alerts about deviations from normal activity.
• RAC Monitoring – Receive global alerts for issues detected at the cluster and the instance levels, plus notification on cluster overhead and interconnect problems; also, track cluster balance based on a variety of workload measures.
• Dataguard and ASM Monitoring – Monitor failover replication and manage storage efficiently
• Historical Data – Navigate through performance diagnostics and alarm data to resolve recent performance issues from any drill-down screen.
• Wait-State Analysis – Diagnose wait statistics to easily determine where waits are occurring and what is causing the event.
• Comprehensive Alarm Workflow – Manage and annotate alarms, set up blackouts and search for past solutions.
• Drag-and-Drop Reporting – Create custom views and reports quickly and easily.
• Wizard-driven Installation – Leverage a powerful wizard to easily install and configure Foglight as a standalone solution or to add Oracle monitoring to existing infrastructure.
• Simple Integration – Integrate Foglight seamlessly with other end-to-end enterprise monitors.
• Automatic Instance Discovery – Deploy Foglight capabilities rapidly and painlessly with automatic instance discovery or specific hosts (you can also manually add instances).

Know more about  Foglight® for Oracle

Converting ASM database to non-ASM environment

Following procedure need to be followed for migrating of a ASM database to non-ASM.

Create directories (locations) where you want to put your datafiles,controlfile,and redologfiles.
Now shutdown the ASM database and change the parameters control_files,db_create_file_dest,db_create_online_log_dest_1{2/3/4…}

C:\>set oracle_sid=CATDB
C:\>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Feb 18 13:09:07 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

Go to init file or spfile , then modify the parameters mentioned above.
C:\oracle\product\10.2.0\db_1>
C:\oracle\product\10.2.0\db_1\database>notepad initcatdb.ora
Eg: control_files='C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\CONTROL01.CTL','C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\CONTROL01.CTL’,’C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\CONTROL01.CTL'

Accordingly change the other paths ASM DISK GROUP to normal file system.
C:\oracle\product\10.2.0\db_1\database>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Feb 18 13:24:33 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.

SQL> STARTUP NOMOUNT PFILE='C:\oracle\product\10.2.0\db_1\database\initCATDB.ora
ORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 192940932 bytes
Database Buffers 411041792 bytes
Redo Buffers 7135232 bytes

Open a new session and connect to RMAN and then copy the controlfile from older location to non-ASM location using the following command:
C:\Documents and Settings\Quest>set oracle_sid=catdb
C:\Documents and Settings\Quest>rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Thu Feb 18 13:27:40 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: catdb (not mounted)

RMAN> restore controlfile from '+data\control01.ctl';
Starting restore at 18-FEB-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: copied control file copy
output filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\CONTROL01.CTL
output filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\CONTROL02.CTL
output filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\CONTROL03.CTL
Finished restore at 18-FEB-10

Now mount the database from existing RMAN session
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1

Now take a backup copy into a file system location, this copied file system Database backup will be used as the actual non-ASM database files .
RMAN> backup as copy database format 'c:\oracle\product\10.2.0\oradata\catdb\%U.dbf';
Starting backup at 18-FEB-10
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 copy
input datafile fno=00001 name=+DATA/catdb/datafile/system.259.711293871
output filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\DATA_D-CATDB_I-2256105880_TS-SYSTEM_FNO-1_08L6C22K.DBF tag=TAG20100218
T233755 recid=11 stamp=711329913
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=+DATA/catdb/datafile/sysaux.260.711293917
output filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\DATA_D-CATDB_I-2256105880_TS-SYSAUX_FNO-3_09L6C242.DBF tag=TAG20100218
T233755 recid=12 stamp=711329942
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=+DATA/catdb/datafile/undotbs1.261.711293941
output filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\DATA_D-CATDB_I-2256105880_TS-UNDOTBS1_FNO-2_0AL6C24R.DBF tag=TAG201002
18T233755 recid=13 stamp=711329949
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=+DATA/catdb/datafile/users.262.711293945
output filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\DATA_D-CATDB_I-2256105880_TS-USERS_FNO-4_0BL6C24U.DBF tag=TAG20100218T
233755 recid=14 stamp=711329952
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:04
channel ORA_DISK_1: starting datafile copy
copying current control file
output filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\CF_D-CATDB_ID-2256105880_0CL6C252.DBF tag=TAG20100218T233755 recid=15
stamp=711329955
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 18-FEB-10

We switch the actual ASM database to non-ASM using the below simple but powerful command.
RMAN> switch database to copy;
datafile 1 switched to datafile copy "C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\DATA_D-CATDB_I-2256105880_TS-SYSTEM_FNO-1_08L6C2
2K.DBF"
datafile 2 switched to datafile copy "C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\DATA_D-CATDB_I-2256105880_TS-UNDOTBS1_FNO-2_0AL6
C24R.DBF"
datafile 3 switched to datafile copy "C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\DATA_D-CATDB_I-2256105880_TS-SYSAUX_FNO-3_09L6C2
42.DBF"
datafile 4 switched to datafile copy "C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\DATA_D-CATDB_I-2256105880_TS-USERS_FNO-4_0BL6C24
U.DBF"

Switch the tempfiles from non-ASM to ASM ,
RMAN> run{
2> set newname for tempfile 1 to 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB/tempfile';
3> switch tempfile all;
4> }
executing command: SET NEWNAME
renamed tempfile 1 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB/tempfile in control file


Now our database is switched from ASM to non- ASM
Now you can open the database
RMAN> ALTER DATABASE OPEN (RESETLOGS);
database opened

Now, and create new in the non-ASM and drop the existing ASM ONLINE LOG FILES:
SQL> select group#,member from v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------
3 +DATA/catdb/onlinelog/group_3.265.711296273
2 +DATA/catdb/onlinelog/group_2.266.711296283
1 +DATA/catdb/onlinelog/group_1.265.711296273

SQL> ALTER DATABASE ADD LOGFILE GROUP 4 ('C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\REDO04.LOG');
Database altered.
SQL> ALTER DATABASE ADD LOGFILE GROUP 5 ('C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\REDO05.LOG');
Database altered.
SQL> ALTER DATABASE ADD LOGFILE GROUP 6 ('C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\REDO06.LOG');
Database altered.

SQL> select group#,member from v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------
6 C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\REDO06.LOG
5 C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\REDO05.LOG
4 C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\REDO04.LOG
1 +DATA/catdb/onlinelog/group_1.265.711296273
2 +DATA/catdb/onlinelog/group_2.266.711296283
3 +DATA/catdb/onlinelog/group_3.267.711296293
6 rows selected.

Make sure that the ASM online redo logfiles are not in current/active state.
SQL> ALTER DATABASE DROP LOGFILE GROUP 1;
Database altered.
SQL> ALTER DATABASE DROP LOGFILE GROUP 2;
Database altered.
SQL> ALTER DATABASE DROP LOGFILE GROUP 3;

SQL> select group#,member from v$logfile;
GROUP# MEMBER
---------- ---------------------------------------------
6 C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\REDO06.LOG
5 C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\REDO05.LOG
4 C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\REDO04.LOG




********************************************************************************

Migration of Database from non-ASM to ASM (Part - 02)

Following procedure need to be followed for migrating a non-ASM database to ASM.

Now shutdown the non-ASM database and change the parameters control_files,db_create_file_dest,db_create_online_log_dest_1{2/3/4…}
C:\>set oracle_sid=CATDB
C:\>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Feb 18 13:09:07 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Go to init file or spfile , then modify the parameters mentioned above.
C:\oracle\product\10.2.0\db_1\database>notepad initcatdb.ora
Eg: control_files='+DATA\control01.ctl','+DATA\control02.ctl','+DATA\control03.ctl'
Accordingly change the other paths to ASM DISK GROUP.
C:\oracle\product\10.2.0\db_1\database>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Feb 18 13:24:33 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> STARTUP NOMOUNT PFILE='C:\oracle\product\10.2.0\db_1\database\initCATDB.oraORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 192940932 bytes
Database Buffers 411041792 bytes
Redo Buffers 7135232 bytes

Open a new session and connect to RMAN and then copy the controlfile from older location to ASM location using the following command:
C:\Documents and Settings\Quest>set oracle_sid=catdb
C:\Documents and Settings\Quest>rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Thu Feb 18 13:27:40 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: catdb (not mounted)
RMAN> restore controlfile from 'C:\oracle\product\10.2.0\oradata\catdb\control01.ctl';
Starting restore at 18-FEB-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 devtype=DISK
channel ORA_DISK_1: copied control file copy
output filename=+DATA/control01.ctl
output filename=+DATA/control02.ctl
output filename=+DATA/control03.ctl
Finished restore at 18-FEB-10

Now mount the database from existing RMAN session
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1

Now take a backup copy into ASM disk group, this copied ASM Database backup will be used as the actual ASM database files .
RMAN> backup as copy database format '+DATA';
Starting backup at 18-FEB-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=153 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\SYSTEM01.DBF
output filename=+DATA/catdb/datafile/system.259.711293871 tag=TAG20100218T133750 recid=1 stamp=711293907
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\SYSAUX01.DBF
output filename=+DATA/catdb/datafile/sysaux.260.711293917 tag=TAG20100218T133750 recid=2 stamp=711293936
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\UNDOTBS01.DBF
output filename=+DATA/catdb/datafile/undotbs1.261.711293941 tag=TAG20100218T133750 recid=3 stamp=711293943
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\USERS01.DBF
output filename=+DATA/catdb/datafile/users.262.711293945 tag=TAG20100218T133750 recid=4 stamp=711293946
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:04
channel ORA_DISK_1: starting datafile copy
copying current control file
output filename=+DATA/catdb/controlfile/backup.263.711293949 tag=TAG20100218T133750 recid=5 stamp=711293950
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 18-FEB-10
Now we are gonna see the powerful feature of RMAN, we switch the actual non-ASM database to ASM using the below simple but powerful command.
RMAN> switch database to copy;
datafile 1 switched to datafile copy "+DATA/catdb/datafile/system.259.711293871"
datafile 2 switched to datafile copy "+DATA/catdb/datafile/undotbs1.261.711293941"
datafile 3 switched to datafile copy "+DATA/catdb/datafile/sysaux.260.711293917"
datafile 4 switched to datafile copy "+DATA/catdb/datafile/users.262.711293945"

Switch the tempfiles from non-ASM to ASM ,

RMAN>
run{
2> set newname for tempfile 1 to '+DATA/catdb/tempfile';
3> switch tempfile all;
4> }
executing command: SET NEWNAME
renamed tempfile 1 to +DATA/catdb/tempfile in control file

Now our database is switched from non-ASM to ASM
Now you can can open the database
RMAN> ALTER DATABASE OPEN (RESETLOGS);
database opened.
Now, and create new online logfiles in the ASM and drop the existing non-ASM ONLINE LOG FILES:
SQL> select group#,member from v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------
3 C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\REDO03.LOG
2 C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\REDO02.LOG
1 C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\REDO01.LOG

SQL> ALTER DATABASE ADD LOGFILE GROUP 4 ('+data');
Database altered.
SQL> ALTER DATABASE ADD LOGFILE GROUP 5 ('+data');
Database altered.
SQL> ALTER DATABASE ADD LOGFILE GROUP 6 ('+data');
Database altered.
SQL> select group#,member from v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------
3 C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\REDO03.LOG
2 C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\REDO02.LOG
1 C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\REDO01.LOG
4 +DATA/catdb/onlinelog/group_4.265.711296273
5 +DATA/catdb/onlinelog/group_5.266.711296283
6 +DATA/catdb/onlinelog/group_6.267.711296293

6 rows selected.
Make sure that the non-ASM online redo logfiles are not in current/active state.
SQL> ALTER DATABASE DROP LOGFILE GROUP 1;
Database altered.
SQL> ALTER DATABASE DROP LOGFILE GROUP 2;
Database altered.
SQL> ALTER DATABASE DROP LOGFILE GROUP 3;
SQL> select group#,member from v$logfile;
GROUP# MEMBER
---------- ---------------------------------------------
4 +DATA/catdb/onlinelog/group_4.265.711296273
5 +DATA/catdb/onlinelog/group_5.266.711296283
6 +DATA/catdb/onlinelog/group_6.267.711296293

So, at last please comment on this post and critics are most welcome!!

Migration(Convertion) of Database from non-ASM to ASM (Part - 01)

Dear friends

I am posting the process of migration(conversion) of Oracle non-ASM database to ASM environment.The OS platform is Windows using Oracle Utility Recovery Manager (RMAN) for testing purpose. Before conversion I would like to describe few steps that deal with ASM Instance Creation, then we will go for Conversion!

ASM DISK CREATION IN WINDOWS USING ASMTOOL UTILITY…

Before converting database from non-ASM to ASM, we need to create few ASM disks to allocate them to ASM Instance and then later these ASM disks will be used for Database storage. You can create a mimicked ASM files in windows using this ASMTOOL utility.

Create a directory called ‘asmdir’ and In ‘asmdir’ directory create ASM disks using ‘ASMTOOL’. The below command creates an ASM disk of size 512MB

D:\asmdir>asmtool -create disk01 512
D:\asmdir>dir
Volume in drive D has no label.
Volume Serial Number is D80A-F55D
Directory of D:\asmdir
02/18/2010 11:00 AM

.
02/18/2010 11:00 AM

..
02/18/2010 11:02 AM 536,870,912 disk01
1 File(s) 536,870,912 bytes
2 Dir(s) 15,103,508,480 bytes free

Also we can create multiple ASM disks at once!

D:\asmdir>asmtool -create disk02 512 disk03 512 disk04 512
D:\asmdir>dir
Volume in drive D has no label.
Volume Serial Number is D80A-F55D
Directory of D:\asmdir
02/18/2010 11:07 AM

.
02/18/2010 11:07 AM

..
02/18/2010 11:02 AM 536,870,912 disk01
02/18/2010 11:06 AM 536,870,912 disk02
02/18/2010 11:07 AM 536,870,912 disk03
02/18/2010 11:09 AM 536,870,912 disk04
4 File(s) 2,147,483,648 bytes
2 Dir(s) 13,492,891,648 bytes free

ASM Instance Creation using DBCA:

We can create ASM Instance in Two ways , using Oracle tool DBCA (Database Configuration Assistant) or using manual method. For ease I have chosen DBCA.
The steps are as follow!

1.Run autoconfig: To create OCR key and CSS Service:
C:\oracle\product\10.2.0\db_1\BIN>localconfig add
Step 1: creating new OCR repository
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'quest', privgrp ''..
Operation successful.
Step 2: creating new CSS service
successfully created local CSS service
successfully added CSS to home

After OCR Repository and CSS service creation, call Oracle DBCA tool to create ASM Instance.
2. Creating ASM Instance using DBCA tool:

You can call DBCA tool from command prompt also like C:\>dbca

Click NEXT , to go further, there will be three major steps to complete the ASM instance Creation
In below shown option, choose Configure Automatic Storage Management



Here you will get a new window to create ASM Instance, where you have to set ASM related parameters and other settings .



Choose ASM Parameters option.



Select Show Advanced Parameters option here you have to check all the ASM Instance Parameters and set them accordingly similarly shown below…



Here you have to set ASM DISKGROUPS, ASM_DISKSTRING ,ASM_POWER_LIMIT,INSTANCE_TYPE, and other similar parameters as in normal Database Instance. You can check the description of each and every parameter and its usage by clicking on SHOW DESCRIPTION,




Like the parameter ASM_DISKSTRING description is something like below and you have to mention the Disks which are created using ASMTOOL include their complete path.. Observe the Description given above about ASM_DISKSTRING parameter .

The ASM Instance should have its own SYS user to manage remotely. So give a different password for ASM Instance SYS user



Click NEXT to go further.. Where DBCA comes to action for ASM instance creation and make it up , then you can use these ASM disk groups for Your Database storage.



Click OK to start the action, Below you can see the window of ASM creation.



Now, Your ASM Instance is created with the specified ASM DISKS, now assign these ASM disks to one or more ASM DISK GROUPS



Here you have to create new ASM Disk Groups , click on Create New, option to create a new ASM Disk Group and Assign the Disks which are created and mentioned in previous steps..



Here mention the name of the Disk Group (eg: DATA) , You need to set the Redundancy type , there are three types of Redundancies , HIGH,NORMAL and EXTERNAL, Though there is a lot to explain about Redundancy I would like to give you some brief information about them which is something important to know..

REDUNDANCY Clause:
The REDUNDANCY clause lets you specify the redundancy level of the disk group.
NORMAL REDUNDANCY requires the existence of at least two failure groups . Automatic Storage Management provides redundancy for all files in the disk group according to the attributes specified in the disk group templates.
HIGH REDUNDANCY requires the existence of at least three failure groups. Automatic Storage Management fixes mirroring at 3-way mirroring, with each extent getting two mirrored copies. HIGH REDUNDANCY disk groups can tolerate the loss of two failure groups.
EXTERNAL REDUNDANCY indicates that Automatic Storage Management does not provide any redundancy for the disk group. The disks within the disk group must provide redundancy (for example, using a storage array), or you must be willing to tolerate loss of the disk group if a disk fails (for example, in a test environment). You cannot specify the FAILGROUP clause if you specify EXTERNAL REDUNDANCY.

Now, SELECTING DISK MEMEBERS , Which may belong to the below four status, those are
Former - Disk was formerly part of disk group.
Candidate - Available to be added to a disk group.
Member - Indicate part of a disk group.
Provisioned - Similar to candidate, made available using ASMLIB.

So , choose SHOW CANDIDATES, to select the available addable ASM disks,

Wait, Wait, something went wrong , though we select Show Candidate option We are unable to see the ASM DISKS, So there is something has to be done more , that is , Actually there is a hidden Parameter called _ASM_ALLOW_ONLY_RAW_DISKS , Which value has to be set to FALSE to cope up our mimicked so called ASM DISKS. Now cancel only CREATE DISK GROUP window, we shall reopen it after the below settings.

So connect to the ASM Instance from command prompt like below
C:\Documents and Settings\Quest>SET ORACLE_SID=+ASM
C:\Documents and Settings\Quest>SQLPLUS / AS SYSDBA
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Feb 18 12:24:50 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> ALTER SYSTEM SET "_ASM_ALLOW_ONLY_RAW_DISKS"=FALSE SCOPE=SPFILE;
System altered.

Now restart the ASM Instance to above changed parameter come into action,
SQL> SHUTDOWN IMMEDIATE
ORA-15100: invalid or missing diskgroup name
ASM instance shutdown
SQL> STARTUP
ASM instance started
Total System Global Area 83886080 bytes
Fixed Size 1247420 bytes
Variable Size 57472836 bytes
ASM Cache 25165824 bytes
ORA-15110: no diskgroups mounted

Ignore the above error because we have not attached the ASM DISKS yes and not mounted.
Now choose CREATE NEW option again and You can see the CANDIDATE ASM DISKS .



Here You can choose the number of CANDIDATE disks to attach them to the Disk Group(Eg: DATA)



Here I have choosen all available CANDIDATE ASM DISKS and Redundency EXTERNAL is taken.
Now click OK



Now , DISK GROUP CREATION will be processed,



Yes ! a new DISK GROUP called DATA is created and mounted successfully, So this Disk Group can be now used for our DATABASE. Though there is a lot to explain about the ASM options, due to the fear that this might lead us to deviation from the actual concept I am forcibly reverting to our topic.

Click FINISH to wash your hands now.



Click YES to farewell the DBCA tool.

Now you are successfully created the ASM Instance using DBCA and Mounted the DISK GROUPS, You can check them below using command prompt.
C:\Documents and Settings\Quest>net start oracleasmservice+asm
The OracleASMService+ASM service is starting.....
The OracleASMService+ASM service was started successfully.
C:\Documents and Settings\Quest>set oracle_sid=+asm
C:\Documents and Settings\Quest>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Feb 18 12:51:33 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>
( Here all the DISK GROUPS are mounted so we are not getting the error that blinked last time.)

Check some important parameters here,
SQL> show parameter asm
NAME TYPE VALUE
------------------------------------ ---------- ------------------------------
_asm_allow_only_raw_disks boolean FALSE
asm_diskgroups string DATA
asm_diskstring string D:\ASMDIR\DIS*
asm_power_limit integer 1

Yes, it’s the time to move on to our actual concept, Migration of DATABASE from FILE SYSTEM to ASM environment using RMAN…. (End of part :1)