Oracle, Oracle 10g

DBCA will not start with error NJNI10

Leave a comment

1. If you see the error below when trying to execute DBCA try the next three steps to resolve.

[oracle@mylinux1 bin]$ ./dbca

UnsaUntitled 1tisfiedLinkError exception loading native library: njni10
Exception in thread “main” java.lang.UnsatisfiedLinkError: get
at oracle.net.common.NetGetEnv.get(Native Method)
at oracle.net.config.Config.getNetDir(Unknown Source)
at oracle.net.config.Config.initConfig(Unknown Source)
at oracle.net.config.Config.(Unknown Source)
at oracle.sysman.assistants.util.NetworkUtils.(NetworkUtils.java:225)
at oracle.sysman.assistants.util.step.StepContext.(StepContext.java:264)
at oracle.sysman.assistants.dbca.backend.Host.(Host.java:686)
at oracle.sysman.assistants.dbca.ui.UIHost.(UIHost.java:205)
at oracle.sysman.assistants.dbca.ui.InteractiveHost.(InteractiveHost.java:54)
at oracle.sysman.assistants.dbca.Dbca.getHost(Dbca.java:160)
at oracle.sysman.assistants.dbca.Dbca.execute(Dbca.java:94)
at oracle.sysman.assistants.dbca.Dbca.main(Dbca.java:180)

[oracle@mylinux1 bin]$

2. Create the following soft links in $ORACLE_HOME/bin directory and retry ./dbca command.

[oracle@mylinux1 bin]$ ln -s $ORACLE_HOME/lib/libnjni10.dylib $ORACLE_HOME/lib/libnjni10
[oracle@mylinux1 bin]$ ln -s $ORACLE_HOME/lib/libclntsh.dylib $ORACLE_HOME/lib/libclntsh.dylib.10.1

3. If the previous step did not resolve the issue ensure that the variable LD_LIBRARY_PATH contains both $ORACLE_HOME/lib and $ORACLE_HOME/lib32. Re-execute your .bash file and retry ./dbca command.

[oracle@mylinux1 ~]$ vi .bash_profile
[oracle@mylinux1 ~]$ . ./.bash_profile

4. If the previous step did not resolve the issue, install or re-install the following 32 bit packages: glibc-devel-2.3.4-2.36.i386.rpm and glibc-2.3.4-2.36.i686.rpm. Then cd to $ORACLE_HOME/bin and execute ./relink all. Finally, retry the ./dbca command.

[root@mylinux1 RPMS]# rpm -Uvh –force glibc-devel-2.3.4-2.36.i386.rpm
Preparing… ########################################### [100%]
1:glibc-devel ########################################### [100%]
[root@mylinux1 RPMS]# rpm -Uvh –force glibc-2.3.4-2.36.i686.rpm
Preparing… ########################################### [100%]
1:glibc ########################################### [100%]
[root@mylinux1 RPMS]#
Larry J Catt, OCP
oracle@allcompute.com
www.allcompute.com

ASM, Oracle, Oracle 10g

ASM with RAW Disks will not start after reboot

Leave a comment

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

ASM, Oracle, Oracle 10g

CRS setup for ORACLE 10g ASM instance

Leave a comment

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

11g, hp-ux, itanium, linux, Oracle, Oracle 10g, oracle 9i, unix

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

Leave a comment

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

11g, Oracle 10g, oracle 9i, SQL, sqlplus

Display the time at your SQL*PLUS Prompt

Leave a comment

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

Oracle, SQL, sqlplus

Safely removing an Oracle user account

Leave a comment

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

Oracle, script, SQL, sqlplus

Dropping a user and the cascade option

Leave a comment

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.