Avoiding ASM Pitfalls | SUSE LINUX Enterprise Server 9 Administrators Handbook

This section discusses the common issues, challenges, and tips to overcome problems associated with setting up and using ASM for database storage.

Unable to Connect to ASM Instance

If you get errors like ORA-17203 or ORA-15055 when trying to connect from a database instance to an ASM instance, follow these steps to check and fix the error conditions.

Verify that the ASM instance is up and running:

 setenv ORACLE_SID +ASM

or use

 export ORACLE_SID=+ASM sqlplus "/as sysdba" SQL> startup;

When the ASM instance starts up, export your session to point to the ORACLE_HOME of the database you had problems with. Follow the same steps as in the ASM instance:

 setenv ORACLE_SID = Q0OR10G

or use

 export ORACLE_SID= Q0OR10G sqlplus "/as sysdba" SQL> startup;

Now that the ASM instance is up and running, you should be able to make your database connect to the ASM instance.

Non-ASM and ASM Database Migrations

This section provides tips to migrate a non-ASM database to an ASM database or vice versa. These steps are also mentioned in ASM MetaLink Notes.

Non-ASM to ASM Migration

You, the DBA, may encounter situations where storage requirements get unmanageable and will benefit from using ASM files. To address this, set up the ASM database instance as mentioned in the section titled "Configuration Options" in this chapter. This example assumes that you have four sets of redo log members:

1.	Edit the `initSID.ora` of the current non-ASM database to point to the new `control_file` location: control_file="+dskgrp10g01"
2.	Start the database to nomount state: SQL> Startup nomount;
3.	Copy the control file from the old location to the new location, using RMAN: RMAN> RESTORE CONTROLFILE FROM 'opt/app/oracle/product/10.0.1/Q0OR10G/control01.ctl';
4.	Mount the database: SQL> ALTER DATABASE MOUNT;
5.	Using `RMAN`, copy the data file from the non-ASM location to the ASM location: RMAN>BACKUP AS COPY DATABASE FORMAT '+dskgrp10g01';
6.	Rename the data file using `RMAN`: RMAN> SWITCH DATABASE TO COPY;
7.	Open the database and reset the logs: SQL> ALTER DATABASE OPEN RESETLOGS;
8.	Repeat the following commands for all the four log members: SQL> ALTER DATABASE DROP LOGFILE '<old_log_name1>'; SQL> ALTER DATABASE ADD LOGFILE '+dskgrp10g01'; SQL> ALTER DATABASE SWITCH LOGFILE; SQL> ALTER DATABASE DROP LOGFILE '< old_log_name2>'; SQL> ALTER DATABASE ADD LOGFILE '+dskgrp10g01'; SQL> ALTER DATABASE SWITCH LOGFILE; SQL> ALTER DATABASE DROP LOGFILE '< old_log_name3>'; SQL> ALTER DATABASE ADD LOGFILE '+dskgrp10g01'; SQL> ALTER DATABASE SWITCH LOGFILE; SQL> ALTER DATABASE DROP LOGFILE '< old_log_name4>'; SQL> ALTER DATABASE ADD LOGFILE '+dskgrp10g01'; SQL> ALTER DATABASE SWITCH LOGFILE;
9.	If you want to multiplex your control files to more than one location over ASM disk groups, use the following command: [View full width] SQL> ALTER SYSTEM SET control_files='+dskgrp10g01/control1.ctl', '+dskgrp10g02/control2 .ctl' SCOPE=SPFILE;

Oracle has issued a whitepaper titled Oracle Database 10g Migration to Automatic Storage Management to give you an overview of different cold/hot migration strategies.

ASM to Non-ASM Migration

You may want to revert the database in the preceding section from using ASM files to using non-ASM files for certain reasons. Follow these steps if you have a change of mind and need to go back to using file-based databases:

1.	Make sure that the database is up and running.
2.	Create the PFILE from SPFILE and edit the PFILE so that the control file name is pointing to a file system location (the reverse of the step done in the preceding example of nonASM to ASM migration).
3.	Start the database up to nomount stage: SQL> Startup nomount;
4.	Copy the control file from ASM to non-ASM using `RMAN`: RMAN> RESTORE CONTROLFILE FROM '+dskgrp10g01/control1.ctl';
5.	Mount the database: SQL> alter database mount;
6.	Copy the database from ASM to non-ASM using `RMAN`: [View full width] RMAN> BACKUP AS COPY DATABASE format ''opt/app/oracle/product/10.0.1/Q0OR10G/%U'; allocate channel c1 type disk; allocate channel c2 type disk; copy datafile ''+dskgrp10g01/q0testdb/datafile/system.471.1' to ''opt/app/oracle/product /10.0.1/Q0OR10G/system.471.1'; copy datafile ''+dskgrp10g01/q0testdb/datafile/sysaux.474.1' to ''opt/app/oracle/product /10.0.1/Q0OR10G/sysaux.474.1'; copy datafile ''+dskgrp10g01/q0testdb/datafile/undotbs1.473.1' to ''opt/app/oracle/product /10.0.1/Q0OR10G/undotbs1.473.1'; copy datafile ''+dskgrp10g01/q0testdb/datafile/undotbs2.478.1' to ''opt/app/oracle/product /10.0.1/Q0OR10G/undotbs2.478.1'; copy datafile ''+dskgrp10g01/q0testdb/datafile/users.476.1' to ''opt/app/oracle/product/10 .0.1/Q0OR10G/users.476.1'; copy datafile ''+dskgrp10g01/q0testdb/datafile/asm_ts.479.1' to ''opt/app/oracle/product /10.0.1/Q0OR10G/asm_ts.479.1'; copy datafile ''+dskgrp10g01/q0testdb/rtlsldata_1.dbf' to ''opt/app/oracle/product/10.0.1 /Q0OR10G/rtlsldata_1.dbf'; RMAN> SWITCH DATABASE TO COPY;
7.	Repeat the following commands for all four redo log members. SQL> ALTER DATABASE DROP LOGFILE '+dskgrp10g01' SQL> ALTER DATABASE ADD LOGFILE '<new_log_name1>';

Discovering ASM Instances in a RAC Environment

If an ASM instance is added to an existing RAC environment, it will not be discovered automatically by the Database Control. You will have to help RAC discover the new ASM target manually.

To do so, create an XML file with the instance details, and run emctl config agent addtarget filename to append the new target to the list of targets in targets.xml. Restart the agent to complete the steps. Refer to MetaLink Notes #266770.1 and #273561.1 for more details on UNIX and Windows platforms.

VIP Timeouts and VIP Failure on RAC

In an Oracle Database 10g RAC environment running Oracle CRS, the crsd daemon (in UNIX) or OracleCRService (on Windows) is responsible for monitoring the resources associated with the cluster. One of these monitored resources is the virtual IP address (VIP) associated with each node. CRS creates dependencies for the listener service, ASM instance, and each database instance on a given node on this VIP.

If for some reason this VIP fails and cannot be restarted, the CRS will bring down all dependent resources, including the listener, ASM instance, and database instance, gracefully using a SHUTDOWN IMMEDIATE command.

When this happens, check the trace file for the VIP found in the ORA_CRS_HOME/racg/dump directory (ora.nodename.vip.trc) . This could be due to known issues with timeouts of the VIP under a high load. This issue is logged as bug #3718601. Similarly, another bug, # 3677437, is reported on VIP. If you increase the TIMEOUT values for the VIP from 60 to 120 seconds, as given in MetaLink Note #277274.1, you could resolve the issue.

 ./crs_stat -p ora.<name1>.vip > /tmp/ora.<name1>.vip.cap

Update the .cap file to have CHECK_INTERVAL and SCRIPT_TIMEOUT values of 120 seconds (where ci = CHECK_INTERVAL and st = SCRIPT_TIMEOUT):

 ./crs_profile -update ora.<name1>.vip -dir /tmp -o ci=120,st=120

Re-register the VIP file using the -u option:

 ./crs_register ora.<name1>.vip -dir /tmp -u

Please refer to abovementioned MetaLink Notes and to Chapter 15, "Utilizing Oracle Database 10g Real Applications Clusters" for more details on CRS and details of the bugs.