ASM with RAW Disks will not start after reboot

ASM (Automatic Storage Management) instances for the storage of database files have become a very popular option in the database world since the release of ORACLE 10g. However, there exist several administration concerns over ASM, which the DBA must take into consideration. This article covers the problem of ASM instance not starting after a reboot of a Linux server and applies to most Unix systems while using RAW Disks.

Error: ORA-15042: ASM disk is missing.

Reason: This error is caused by the RAW device being removed from the OS, corrupted, or/and most likely, not owned by the ORACLE user.

1. Verify that the ASM disk exists.

[root@mylinux1 /]# cd /dev/raw
[root@mylinux1 /dev/raw]# ls
/dev/raw/raw1
/dev/raw/raw2
/dev/raw/raw3
[root@mylinux1 u06]#

2. Check the ownership of the disks.

[root@mylinux1 /]# cd /dev/raw
[root@mylinux1 /dev/raw]# ls –la
-rw-r–r– 1 root root 2 Sep 2 08:33 raw1
-rw-r–r– 1 root root 2 Sep 2 08:33 raw2
-rw-r–r– 1 root root 2 Sep 2 08:34 raw3
[root@mylinux1 /dev/raw]#

NOTE: These raw devices are now owned by ROOT after the reboot, thus ORACLE does not have access.

3. On most UNIX systems you will see that the ownership of RAW devices is reset to ROOT after reboot. To correct this behavior, enter the following code into your init process before the Oracle database startup scripts are executed.

chown oracle:dba /dev/raw/raw1
chown oracle:dba /dev/raw/raw2
chown oracle:dba /dev/raw/raw3

Larry J Catt, OCP
oracle@allcompute.com
www.allcompute.com

CRS setup for ORACLE 10g ASM instance

In order for ASM and RAC to work properly the CRS (Cluster Ready Service) must first be configured. This procedure outlines the configuration of CRS in a Linux environment.

1. Logon to Linux server as the root user.

2. Verify that the correct support packages are installed.

[root@mylinux_1 RPMS]# ls compat-libstdc++*
compat-libstdc++-296-2.96-132.7.2.i386.rpm

3. NOTE: depending on your OS you may require a different version or bit setting of this package. I am running x86_64 system so I will install the newest compat-listdc++ package for this system.

[root@mylinux_1 RPMS]# rpm -i compat-libstdc++-33-3.2.3-47.3.x86_64.rpm
[root@mylinux_1 RPMS]#

4. As the ROOT user navigate to the directory $ORACLE_HOME/bin. Execute the command ./localconfig add. The localconfig command cannot be run as the ORACLE user.

[root@mylinux_1 bin]# ./localconfig add
Successfully accumulated necessary OCR keys.
Creating OCR keys for user ‘root’, privgrp ‘root’..
Operation successful.
Configuration for local CSS has been initialized

Adding to inittab
Startup will be queued to init within 30 seconds.
Checking the status of new Oracle init process…
Expecting the CRS daemons to be up within 600 seconds.
CSS is active on these nodes.
mylinux_1
CSS is active on all nodes.
Oracle CSS service is installed and running under init(1M)
[root@mylinux_1 bin]#

5. If CRS fails to install you must back out the files created with the command ./localconfig delete. Most likely the failure has occurred due to missing support packages. Verify that all packages suggested by Oracle Corporation for your OS and product version exist. You can find this listing at www.oracle.com/technology/index.html.

Larry Catt, OCP
oracle@allcompute.com
www.allcompute.com

UNIX CRON – Shell script errors with [10]: sqlplus: not found

In normal administration of an Oracle RDBMS on UNIX or LINUX operating systems, the DBA will be required to develop shell and SQL scripts to maintain his/her RDBMS. In fact, to be considered a mid to senior DBA you will need to be well versed in scripting at the OS layer. In this article we will review a common error which occurs in a shell script called by CRON due to a lack of properly set variables.

1. In a normal scenario, the DBA has developed a shell script which calls and executes a SQL script to perform a certain maintenance task. In testing, the DBA executes the script as the oracle software owner and the script performs perfectly. However, when placed in the OS CRON for execution, the following error occurs.

[10]: sqlplus: not found

2. The error occurs because the environmental variables are not being read. Thus, the OS does not know where to find the executable: sqlplus.

3. The solution is to update your shell script and hard code the directory structure for the command sqlplus. An example would be if sqlplus is located in the following path /u01/opt/app/oracle/bin then perform the following:

Replace:

sqlplus

With:

/u01/opt/app/oracle/bin/sqlplus

This completes resolving the error message [10]: sqlplus: not found in a shell script called through CRON.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Display the time at your SQL*PLUS Prompt

Oracle SQL*PLUS provides the ability to change the default prompt in your session. This allows you to easily determine information about your current session without having to query the database. In this article we will review displaying the current system time at your SQL*PLUS prompt.

1. Connect to your database through SQL*PLUS.

SQL>connect ljcatt/xxxx@mydb
Connected.
SQL>

2. Execute the following SQL*PLUS command: set time on.

SQL>set time on
19:53:56 SQL>
19:53:58 SQL>

3. The time will now be displayed in your SQL*PLUS prompt.
4. To turn off the time execute the following SQL*PLUS command: set time off

19:53:58 SQL>set time off
SQL>
SQL>

5. This completes displaying the time at your Oracle SQL*PLUS prompt.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Safely removing an Oracle user account

In an oracle database every user has the ability (may not have the privilege) to own objects, this collection of objects is referred to as a schema. A typical user of a database application will not have any objects within their schema. To safely remove an Oracle user you must ensure that they do not own any objects which may be referenced by other users. This article outlines the step to ensure that the user does not own any objects.

1. Logon to SQL*PLUS with DBA privileges.

2. Select the number of objects owned by the user account you wish to delete with the following statement. In this example we are trying to delete user JDOE.

SQL> select count(*) from dba_objects where owner='JDOE';

COUNT(*)
----------
0

SQL>

3. If the above SQL statement returns a number greater than 0, further investigation is required. If the above SQL statement returns 0, you can safely remove the user account with the below statement.

SQL> drop user jdoe;

User dropped.

SQL>

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Dropping a user and the cascade option

In an oracle database every user has the ability (may not have the privilege) to own objects, this collection of objects is referred to as a schema. A typical user of a database application will not have any objects within their schema. To safely remove an Oracle user you must ensure that they do not own any objects which may be referenced by other users. The cascade option of the drop command, allows the administrator to by-pass the deletion of objects owned by the user. NOTE: It is very important to verify that you do not need any objects owned by a user before dropping the account with the cascade option. This article outlines the step to list all objects owned by a user before dropping the account with the cascade option..

1. Logon to SQL*PLUS with DBA privileges.

2. Select the object name and type owned by the user account you wish to delete with the following statement. In this example we are trying to delete user JDOE.
SQL> column object_name format a30
SQL> select object_name, object_type from dba_objects where owner='JDOE';

OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------
TEMP TABLE

SQL>

3. If the above SQL statement returns a number greater than 0, you should validate that the objects are not used by any other user. If the above SQL statement returns 0, you can remove the user account without the cascade option as shown below.

SQL> drop user jdoe;

User dropped.

SQL>

4. Once you are sure that the objects are not being used by any other user, use the following SQL to remove the account and all associated objects.

SQL> drop user jdoe cascade;

User dropped.

SQL>

NOTE: The associated table TEMP was removed before the actual account was removed.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle tips and tricks.