Category Archives: linux

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

Execution of Oracle SQL commands from within UNIX or LINUX shell scripts:

Leave a comment

In the management of Oracle RDBMS, we frequently have the need to develop shell scripts to manage our databases. This article demonstrates the use of BASH shell script to connect to an Oracle database and execute PL/SQL and SQL statements from within the Oracle database. The following procedure will work on UNIX and LINUX machines.

1. Logon to your Oracle database server as the Oracle software owner.

2. Use vi to create a new file with the name sql_shell_test.sh

mylinux:> vi sql_shell_test.sh
“sql_shell_test.sh” [New file]

~
~

3. Press the ‘a’ key once (for append operations) and past the following text into your file.

sqlplus ‘/ as sysdba’ <

4. Press the keys : –> w –> q and hit the return key to save and exit vi.

5. Execute the chmod command to change you file status to read_write_execute for owner and group

mylinux:> chmod 770 sql_shell_test.sh
mylinux:>

6. As the Oracle software owner, execute the shell script sql_shell_test.sh with the following command.

mylinux:>./sql_shell_test.sh

7. Perform an ls command and you can see that the script has generated the output file test_shell_script_output.lst.

mylinux:>ls -lrt
-rwxrwxrwx 1 oracle dba 148 May 13 03:30 sql_shell_test.sql
-rw-r–r– 1 oracle dba 1391 May 13 03:32 test_shell_script_output.lst
mylinux:>

8. Perform a cat command to see the output of file test_shell_script_output.lst.

mylinux:>cat test_shell_script_output.lst
SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
————— —————-
HOST_NAME
—————————————————————-
VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
—————– ——— ———— — ———- ——- —————
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
———- — —————– —————— ——— —
1 orcl
frankie
10.2.0.4.0 13-MAY-10 OPEN NO 1 STOPPED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO

SQL> select name, open_mode from v$database;

NAME OPEN_MODE
——— ———-
ORCL READ WRITE

SQL> spool off
mylinux:>

This concludes the execution of Oracle SQL commands from within UNIX or LINUX shell scripts.

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

11g, hp-ux, itanium, linux, Oracle

Oracle variable ORACLE_HOME undefined:

Leave a comment

This article covers the errors you will receive from a Linux or UNIX OS if your ORACLE_HOME variable is not defined correctly and how to resolve it.

1. Initialize sqlplus without defining an ORACLE_HOME variable at the OS layer.

mylinux:> sqlplus ‘/ as sysdba’
Error 6 initializing SQL*Plus
Message file sp1.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory

2. Set your ORACLE_HOME variable at the OS layer.

mylinux:> export ORACLE_HOME=/opt/app/oracle/10.2.0
mylinux:>

3. Oracle SQLPLUS starts up normally once it know where to find the Oracle Home software directory defined by the OS parameter ORACLE_HOME.

mylinux:> sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Wed May 5 14:52:47 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

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

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

Oracle – SQL*PLUS does not show the SQL being executed from a script file.

Leave a comment

Oracle SQL*PLUS utility provides several options to customize the display of information from your database session. In this article we will review how to display the DML and DDL statements which are stored in an OS layer file upon execution. By default SQL*PLUS will not display the DML or DDL statement from a script file.

1. Logon to your Oracle database server as the Oracle software owner.

2. Create a file called test.sql with the following SQL.

select * from v$instance;

3. Logon to SQLPLUS with SYSDBA privileges.

mylinux:> sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Fri Apr 23 22:00:09 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

4. Execute the SQL file test.sql with the SQL*PLUS command: @./test.sql

SQL> @./test.sql

INSTANCE_NUMBER INSTANCE_NAME
————— —————-
HOST_NAME
—————————————————————-
VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
—————– ——— ———— — ———- ——- —————
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
———- — —————– —————— ——— —
1 orcl
mylinux
10.2.0.4.0 23-APR-10 OPEN NO 1 STOPPED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO

SQL>

NOTE: The SQL contained within the file test.sql is not displayed, thus the end user executing such a file may not be aware of the output they are looking at.

5. To remedy this situation we will have to setup our SQL*PLUS environment to display the commands within our file. To do this we will have to turn two options on: ECHO and FEEDBACK.

SQL> set echo on
SQL> set feedback on
SQL>

6. Now re-execute the file test.sql with the SQL*PLUS command: @./test.sql.

SQL> @./test.sql
SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
————— —————-
HOST_NAME
—————————————————————-
VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
—————– ——— ———— — ———- ——- —————
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
———- — —————– —————— ——— —
1 orcl
mylinux
10.2.0.4.0 23-APR-10 OPEN NO 1 STOPPED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO

1 row selected.

SQL>
SQL>

As show above, SQL*PLUS is now displaying the SQL contained within our file, before the output. This will allow the end user to know exactly how the output of our script file was produced, thus turning our script into more usefully information.

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

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

Oracle – Profile for single User owning multiple databases with separate ORACLE_HOME:

Leave a comment

In Oracle production database environments, it is desirable to use one Oracle installation for each database which resides on a server. The use of a separate Oracle installation for each database reduces the possibility of corruption of one ORACLE_HOME affecting all of the databases on a single server and allows for maintenance to be performed on one RDBMS without affecting the operations of the others. This is easily accommodated in a LINUX or UNIX environments through the proper setting of your .profile or .bash_profile file. In this article we will review the configuration of your profile to use multiple ORACLE_HOMEs on a single server.

1. To accomplish the setup of our profile for multiple database with separate ORACLE_HOMEs, we will have to create a .profile file with three sections: 1. request for information; 2. local variables definitions; and 3. global variables definitions. The request for information section will appear to the user upon logon or re-initialization of the .profile to determine which database the user wishes to use. The local variable section defines the variables need to connect to a particular database. The global variable section defines the variables which will be the same regardless of our connection to a certain database.

2. The request for information section defines the variable “database” which is used to set the ORACLE_SID, as seen below:

## Beginning of request for information
database=””
while [ -z “$database” ]
do
echo “\n\nEnter database & Oracle version to use:”
echo “1 – ORCL 10.2.0.4 ”
echo “2 – ORCL2 10.2.0.4”
echo “3 – ORCL3 10.2.0.4”
print -n “> ”
read database
if [[ “$database” != “1” &&
“$database” != “2” &&
“$database” != “3” ]]
then
echo “$database” is not valid >&3
database=””
fi
done
## Ending of request for information

3. The local section defines all variables for a specific database, as seen below:

##Beginning of local variable definitions
if [[ $database = “1” ]]
then
export NLS_LANG=AMERICAN_AMERICA.US7ASCII
export ORACLE_BASE=/opt/app/oracle/product
export ORACLE_HOME=/opt/app/oracle/product/10.2/orcl
export ORACLE_SID=orcl
export PATH=.:/opt/perl/bin:$PATH:$ORACLE_HOME/bin
export SHLIB_PATH=$ORACLE_HOME/lib:/usr/lib:/opt/java1.3
export TNS_AMDIN=$ORACLE_HOME/network/admin/orcl
cd
elif [[ $database = “2” ]]
then
export NLS_LANG=AMERICAN_AMERICA.US7ASCII
export ORACLE_BASE=/opt/app/oracle/product
export ORACLE_HOME=/opt/app/oracle/product/10.2/orcl2
export ORACLE_SID=orcl2
export PATH=.:/opt/perl/bin:$PATH:$ORACLE_HOME/bin
export SHLIB_PATH=$ORACLE_HOME/lib:/usr/lib:/opt/java1.3
export TNS_AMDIN=$ORACLE_HOME/network/admin/orcl2
cd
elif [[ $database = “3” ]]
then
export NLS_LANG=AMERICAN_AMERICA.US7ASCII
export ORACLE_BASE=/opt/app/oracle/product
export ORACLE_HOME=/opt/app/oracle/product/10.2/orcl3
export ORACLE_SID=orcl3
export PATH=.:/opt/perl/bin:$PATH:$ORACLE_HOME/bin
export SHLIB_PATH=$ORACLE_HOME/lib:/usr/lib:/opt/java1.3
export TNS_AMDIN=$ORACLE_HOME/network/admin/orcl
cd

else
echo “Invalid options ”
fi

## Ending of local variable definitions

4. The global section defines all variables for any database regardless of the specific ORACLE_HOME, as seen below:

## Beginning of global variable definitions

echo “Your ORACLE_BASE is set to ‘$ORACLE_BASE'”
echo “Your ORACLE_SID is set to ‘$ORACLE_SID'”
echo “Your ORACLE_HOME is set to ‘$ORACLE_HOME'”

export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/obackup/bin
export ORACLE_TERM=vt220

umask 022
export PS1=$ORACLE_SID” $ ”
stty erase ^?
export LINES=24
export COLUMNS=80
export PATH=$PATH:/opt/java1.5:/opt/java1.5/bin

## Ending of global variable definitions

5. Place all profile sections into one file named .profile from UNIX or .bash_profile for linux under your /home/{username} directory. In this example, we will place the following text in the file /home/lcatt/.profile for OS user lcatt.

## Beginning of request for information
database=””
while [ -z “$database” ]
do
echo “\n\nEnter database & Oracle version to use:”
echo “1 – ORCL 10.2.0.4 ”
echo “2 – ORCL2 10.2.0.4”
echo “3 – ORCL3 10.2.0.4”
print -n “> ”
read database
if [[ “$database” != “1” &&
“$database” != “2” &&
“$database” != “3” ]]
then
echo “$database” is not valid >&3
database=””
fi
done
## Ending of request for information

##Beginning of local variable definitions

if [[ $database = “1” ]]
then
export NLS_LANG=AMERICAN_AMERICA.US7ASCII
export ORACLE_BASE=/opt/app/oracle/product
export ORACLE_HOME=/opt/app/oracle/product/10.2/orcl
export ORACLE_SID=orcl
export PATH=.:/opt/perl/bin:$PATH:$ORACLE_HOME/bin
export SHLIB_PATH=$ORACLE_HOME/lib:/usr/lib:/opt/java1.3
export TNS_AMDIN=$ORACLE_HOME/network/admin/orcl
cd
elif [[ $database = “2” ]]
then
export NLS_LANG=AMERICAN_AMERICA.US7ASCII
export ORACLE_BASE=/opt/app/oracle/product
export ORACLE_HOME=/opt/app/oracle/product/10.2/orcl2
export ORACLE_SID=orcl2
export PATH=.:/opt/perl/bin:$PATH:$ORACLE_HOME/bin
export SHLIB_PATH=$ORACLE_HOME/lib:/usr/lib:/opt/java1.3
export TNS_AMDIN=$ORACLE_HOME/network/admin/orcl2
cd
elif [[ $database = “3” ]]
then
export NLS_LANG=AMERICAN_AMERICA.US7ASCII
export ORACLE_BASE=/opt/app/oracle/product
export ORACLE_HOME=/opt/app/oracle/product/10.2/orcl3
export ORACLE_SID=orcl3
export PATH=.:/opt/perl/bin:$PATH:$ORACLE_HOME/bin
export SHLIB_PATH=$ORACLE_HOME/lib:/usr/lib:/opt/java1.3
export TNS_AMDIN=$ORACLE_HOME/network/admin/orcl
cd

else
echo “Invalid options ”
fi

## Ending of local variable definitions

## Beginning of global variable definitions

echo “Your ORACLE_BASE is set to ‘$ORACLE_BASE'”
echo “Your ORACLE_SID is set to ‘$ORACLE_SID'”
echo “Your ORACLE_HOME is set to ‘$ORACLE_HOME'”

export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/obackup/bin
export ORACLE_TERM=vt220

umask 022
export PS1=$ORACLE_SID” $ ”
stty erase ^?
export LINES=24
export COLUMNS=80
export PATH=$PATH:/opt/java1.5:/opt/java1.5/bin

## Ending of global variable definitions

6. Change directory to your home directory with command cd, list current user with command id, and Re-initialize your .profile file as shown below:

$ orcl $ cd
orcl $ id uid=103(lcatt) gid=20(users) groups=200(dba)
orcl $ . ./.profile

7. As seen below the request for information section is displayed and we are presented with the choice of 1, 2, or 3. Select 1 to setup variables for the database ORCL.

Enter database & Oracle version to use:
1 – ORCL 10.2.0.4
2 – ORCL2 10.2.0.4
3 – ORCL3 10.2.0.4
> 1
Your ORACLE_BASE is set to ‘/opt/app/oracle/product’
Your ORACLE_SID is set to ‘orcl’
Your ORACLE_HOME is set to ‘/opt/app/oracle/product/10.2/orcl’
orcl $

This completes setup of .profile for multiple databases using the separate ORACLE_HOMEs.

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

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

Multiple TNSNAMES alias for single database:

Leave a comment

Oracle provides for the ability to have multiple tnsnames aliases for connection to the same database instance in a standard TNSNAMES.ORA file. This is extremely useful when you are changing alias to a database without affecting connections to previously configured database alias listings. In this article we will discuss how to configure a TNSNAMES.ORA file to connect to an Oracle database instance through a new and old alias.

1. In this example we will look at the TNSNAMES listing for the alias ORCL, which we wish to change to an alias of MYDB without affecting any previously configured application or script connections to the old alias ORCL.

2. We open our currently configured TNSNAMES.ORA file and find the alias ORCL.

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = mobile_1-PC)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL)
)
)

3. Test this connection to ORCL through your tnsping utility and as shown below, the connection currently works.

C:\Users\mobile_1>tnsping ORCL

TNS Ping Utility for 64-bit Windows: Version 10.2.0.4.0 – Production on 26-MAY-2
010 18:19:53

Copyright (c) 1997, 2007, Oracle. All rights reserved.

Used parameter files:
C:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = mobile_1
-PC)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL)
))
OK (20 msec)

C:\Users\mobile_1>

4. Now edit and save the ORCL alias listing by replace “ORCL =” with “ORCL, MYDB =”, as shown below:

ORCL, MYDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = mobile_1-PC)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL)
)
)

5. Now test the connection to both aliases MYDB and ORCL, as show below:

C:\Users\mobile_1>tnsping mydb

TNS Ping Utility for 64-bit Windows: Version 10.2.0.4.0 – Production on 26-MAY-2
010 18:19:45

Copyright (c) 1997, 2007, Oracle. All rights reserved.

Used parameter files:
C:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = mobile_1
-PC)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL)
))
OK (10 msec)

C:\Users\mobile_1>tnsping ORCL

TNS Ping Utility for 64-bit Windows: Version 10.2.0.4.0 – Production on 26-MAY-2
010 18:19:53

Copyright (c) 1997, 2007, Oracle. All rights reserved.

Used parameter files:
C:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = mobile_1
-PC)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL)
))
OK (20 msec)

C:\Users\mobile_1>

This completes configuration of TNSNAMES.ORA file for database resolution with multiple aliases.

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

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

Advantages and Disadvantages of single user owning multiple databases with separate ORACLE_HOME:

Leave a comment

Oracle provides for the installation of multiple ORACLE_HOMEs on a single server for support of separate databases. This is normally seen in production systems to allow for isolation of databases residing on the same server. In this article will discuss the advantages and disadvantages of such a configuration, in order to determine which would best suit your needs.

Advantages:

1. Oracle database software is physically separated and corruption will not affect all databases.
2. Ability to configure and manage network configuration to one database without affecting the others.
3. Ability to patch single database without affecting other databases.
4. Configuration changes to one database will not impact other databases.

Disadvantages:

1. Increase maintenance and software configuration work required.
2. Increase disk storage requirements.
3. Increase management of network configurations.
4. Increase use of system resources.

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

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

Advantages and Disadvantages of single user owning multiple databases with a single ORACLE_HOME:

Leave a comment

Oracle provides for the creation of multiple Oracle database configurations which all use a single Oracle installation. This is normally seen in test and development system to allow for a single server to house multiple databases with reduced space usage. In this article will discuss the advantages and disadvantages of such a configuration, in order to determine which would best suit your needs.

Advantages:

1. Uses less disk space.
2. Environmental parameter setup centrally located.
3. Reduced time in patching all databases.
4. Reduced network setup configuration.

Disadvantages:

1. Corruption of binaries affects all databases.
2. Unable to patch a single database instance without affecting all instances.
3. Unable to shutdown network configuration to single database.
4. Oracle software owner is a single point of failure.

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

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

Oracle – Profile for single User owning multiple databases with Single

Leave a comment

In test and development database environments, it is desirable to use one Oracle installation for multiple databases. The use of a single Oracle installation for multiple databases reduces the amount of space being used and creates a single place for update of binary files. This is easily accommodated in a LINUX or UNIX environment through the proper setting of your .profile or .bash_profile file. In this article we will review the configuration of your profile file to use a single Oracle home with multiple databases.

1. To accomplish the setup of our profile for multiple databases on a single ORACLE_HOME, we will have to create a .profile with three sections: 1. Request for information; 2. Local variables definitions; and 3. global variables definitions. The request for information section will appear to the user upon logon or re-initialization of the .profile and determines which database the user wishes to use. The local variable section defines the variables need to connect to a particular database. The global variable section defines the variables which will be the same regardless of our connection to a certain database.

2. The request for information section defines the variable “database” which is used to set the ORACLE_SID variable, as seen below.

## Beginning of request for information
database=””
while [ -z “$database” ]
do
echo “\n\nEnter database & Oracle version to use:”
echo “1 – ORCL 10.2.0.4 ”
echo “2 – ORCL2 10.2.0.4”
echo “3 – ORCL3 10.2.0.4”
print -n “> ”
read database
if [[ “$database” != “1” &&
“$database” != “2” &&
“$database” != “3” ]]
then
echo “$database” is not valid >&3
database=””
fi
done
## Ending of request for information

3. The local variable section defines all variables for a specific database, as seen below.

##Beginning of local variable definitions
if [[ $database = “1” ]]
then
export ORACLE_SID=orcl
elif [[ $database = “2” ]]
then
export ORACLE_SID=orcl2
elif [[ $database = “3” ]]
then
export ORACLE_SID=orcl3
else
echo “Invalid options ”
fi
## Ending of local variable definitions

4. The global variable section defines all variables for any database using this specific ORACLE_HOME, as seen below.

## Beginning of global variable definitions

export NLS_LANG=AMERICAN_AMERICA.US7ASCII
export ORACLE_BASE=/opt/app/oracle/product
export ORACLE_HOME=/opt/app/oracle/product/10.2
export PATH=.:/opt/perl/bin:$PATH:$ORACLE_HOME/bin
export SHLIB_PATH=$ORACLE_HOME/lib:/usr/lib:/opt/java1.3
export TNS_AMDIN=$ORACLE_HOME/network/admin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/obackup/bin
export ORACLE_TERM=vt220

cd

echo “Your ORACLE_BASE is set to ‘$ORACLE_BASE'”
echo “Your ORACLE_SID is set to ‘$ORACLE_SID'”
echo “Your ORACLE_HOME is set to ‘$ORACLE_HOME'”

umask 022
export PS1=$ORACLE_SID” $ ”
stty erase ^?
export LINES=24
export COLUMNS=80
export PATH=$PATH:/opt/java1.5:/opt/java1.5/bin

## Ending of global variable definitions

5. Place all profile sections into one file named .profile from UNIX or .bash_profile for linux under your /home/{username} directory. In this example, I will place the following text in the file /home/lcatt/.profile for the OS user lcatt.

## Beginning of request for information
database=””
while [ -z “$database” ]
do
echo “\n\nEnter database & Oracle version to use:”
echo “1 – ORCL 10.2.0.4 ”
echo “2 – ORCL2 10.2.0.4”
echo “3 – ORCL3 10.2.0.4”
print -n “> ”
read database
if [[ “$database” != “1” &&
“$database” != “2” &&
“$database” != “3” ]]
then
echo “$database” is not valid >&3
database=””
fi
done
## Ending of request for information

##Beginning of local variable definitions
if [[ $database = “1” ]]
then
export ORACLE_SID=orcl
elif [[ $database = “2” ]]
then
export ORACLE_SID=orcl2
elif [[ $database = “3” ]]
then
export ORACLE_SID=orcl3
else
echo “Invalid options ”
fi
## Ending of local variable definitions

## Beginning of global variable definitions

export NLS_LANG=AMERICAN_AMERICA.US7ASCII
export ORACLE_BASE=/opt/app/oracle/product
export ORACLE_HOME=/opt/app/oracle/product/10.2
export PATH=.:/opt/perl/bin:$PATH:$ORACLE_HOME/bin
export SHLIB_PATH=$ORACLE_HOME/lib:/usr/lib:/opt/java1.3
export TNS_AMDIN=$ORACLE_HOME/network/admin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/obackup/bin
export ORACLE_TERM=vt220

cd

echo “Your ORACLE_BASE is set to ‘$ORACLE_BASE'”
echo “Your ORACLE_SID is set to ‘$ORACLE_SID'”
echo “Your ORACLE_HOME is set to ‘$ORACLE_HOME'”

umask 022
export PS1=$ORACLE_SID” $ ”
stty erase ^?
export LINES=24
export COLUMNS=80
export PATH=$PATH:/opt/java1.5:/opt/java1.5/bin

## Ending of global variable definitions

6. Change directory to your home directory with command cd, list current user with command id, and Re-initialize your .profile file as shown below:

$ orcl $ cd
orcl $ id uid=103(lcatt) gid=20(users) groups=200(dba)
orcl $ . ./.profile

7. Upon re-initialization of the profile, you will be able to select the database environment you wish, as seen below. In this example we have select 1 to setup the variables for the database ORCL.

Enter database & Oracle version to use:
1 – ORCL 10.2.0.4
2 – ORCL2 10.2.0.4
3 – ORCL3 10.2.0.4
> 1
Your ORACLE_BASE is set to ‘/opt/app/oracle/product’
Your ORACLE_SID is set to ‘orcl’
Your ORACLE_HOME is set to ‘/opt/app/oracle/product/10.2’
orcl $

This completes setup of .profile for multiple databases using the same oracle home.

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

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

Oracle – Determining the OS layer your database is residing on.

Leave a comment

I have experienced situations where the only connection I had to an Oracle database was SQL*PLUS with no OS layer user account access. Thus, I was not sure of the actual OS my database was installed on. Knowing the OS your database resides on is a key factor in successfully administrating of any RDBMS. This article covers the determination of the operating system your Oracle database uses, through SQL*PLUS. This procedure will work regardless of OS.

1. Logon to your database server with SYSDBA privileges from your remote client.

C:\>sqlplus ljcatt/password5@orcl as sysdba

SQL*Plus: Release 10.2.0.1.0 – Production on Sat Feb 13 10:02:36 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

2. Execute the following SQL statement: select platform_id, platform_name, edition from DBA_REGISTRY_DATABASE;

SQL> column platform_name format a15
SQL> select platform_id, platform_name, edition from DBA_REGISTRY_DATABASE;

PLATFORM_ID PLATFORM_NAME EDITION
———– ————— ——————————
3 HP-UX (64-bit)

SQL>

That completes determination of OS layer type from within the Oracle database.

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

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

ORACLE SID not defined in the OS layer:

Leave a comment

This article covers the error which will occur in a Linux or UNIX environment if the OS parameter ORACLE_SID is not properly set and how to resolve this error.

1. With the ORACLE_SID OS parameter set to null, attempt to start SQLPLUS as sysdba. You will receive the following error – ORA-12162.

mylinux:> sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Wed Jan 6 14:55:56 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

ERROR:
ORA-12162: TNS:net service name is incorrectly specified

Enter user-name:

2. In the OS layer define your database sid with the OS parameter ORACLE_SID.

mylinux:> export ORACLE_SID=orcl
mylinux:>

3. Attempt to start SQLPLUS as sysdba.

mylinux:> sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Wed Jan 6 14:57:54 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

4. Oracle SQLPLUS starts normally after the ORACLE_SID parameter is set at the OS layer correctly.

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