Category Archives: linux

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

linux, Oracle, Oracle 10g, oracle 9i

Enable Support Tracing on Oracle Listener:

Leave a comment

Most Oracle database connects are made through the use of an Oracle process called the LISTENER which monitors a certain machine and port for connection requests to one or more database instances. There are times when a DBA or SA will want to monitor connects being made through the listener for various reasons. There also exist times when you will have to open a SAR or TAR with oracle support, if they suspect you are having issues with your listener connections, they will ask for a SUPPORT level trace of your Oracle listener. This article covers the setup of SUPPORT level tracing of you Oracle Listener and will work on UNIX, Linux, and Windows based machines.

NOTE: Even very well trained DBA’s may have a difficult time interpreting everything generated by this type of trace. When it is generated, it is normally sent to Oracle support, where someone how specializes in the Oracle LISTENER can review it.

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

2. Open up the LSNRCTL utility.

mylinux:>lsnrctl

LSNRCTL for LINUX: Version 10.2.0.4.0 – Production on 23-DEC-2009 17:17:05

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

Welcome to LSNRCTL, type “help” for information.

LSNRCTL>

3. If you are not using the default name of the Oracle listener (LISTENER) then define the Oracle listener name with the following command: set current_listener

LSNRCTL> set current_listener listener_orcl
Current Listener is listener_orcl
LSNRCTL>

4. If you have established a password for your oracle listener set it in the LSNRCTL session with the following command: set password

LSNRCTL> set password oracle
The command completed successfully
LSNRCTL>

5. Turn on support level tracing by specifying 16 or support as the level with the following command: set trc_level

LSNRCTL> set trc_level 16
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.110)(PORT=1521)))
listener_orcl parameter “trc_level” set to support
The command completed successfully
LSNRCTL>

or

LSNRCTL> set trc_level support
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.110)(PORT=1521)))
listener_orcl parameter “trc_level” set to support
The command completed successfully
LSNRCTL>

6. Define the name of the trace file that will be produced by the listener process upon establishment of user connection with the command below: set trc_file

LSNRCTL> set trc_file support_access_trace
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.110)(PORT=1521)))
listener_orcl parameter “trc_file” set to support_access_trace.trc
The command completed successfully
LSNRCTL>

NOTE: You do not have to set a trace directory by default the listener trace files are placed under: $ORACLE_HOME/network/trace

7. Save the changes made to the listener.ora file with the following command: save_config

LSNRCTL> save_config
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.110)(PORT=1521)))
Saved listener_orcl configuration parameters.
Listener Parameter File /opt/app/oracle/10.2.0/network/admin/listener.ora
Old Parameter File /opt/app/oracle/10.2.0/network/admin/listener.bak
The command completed successfully
LSNRCTL>

8. Exit out of LSNRCTL utility, change directories to
$ORACLE_HOME/network/trace, list the files present and you will see that the trace has already begun.

mylinux:> ls -lrt
total 1440
-rw-r—– 1 oracle dba 408755 Dec 23 17:25 listener_orcl.trc
-rw-r—– 1 oracle dba 43347 Dec 23 17:40 user_access_trace.trc
-rw-r—– 1 oracle dba 231352 Dec 23 19:00 admin_access_trace.trc
-rw-r—– 1 oracle dba 35087 Dec 23 19:01
support_access_trace.trc
mylinux:>

9. Logon to the RDBMS as an administrator, regular user and exit.

mylinux:> sqlplus system/devlmgr@orcl

SQL*Plus: Release 10.2.0.4.0 – Production on Wed Dec 23 19:03:18 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> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 –
64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
mylinux:> sqlplus joe/joe@orcl

SQL*Plus: Release 10.2.0.4.0 – Production on Wed Dec 23 19:03:33 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> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 –
64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
mylinux:>

10. Perform another listing of the files under directory
$ORACLE_HOME/network/admin and you will see that the file support_access_trace.trc has grown by recording the connection from the users in step 9.

mylinux:> ls -lrt
total 1568
-rw-r—– 1 oracle dba 408755 Dec 23 17:25 listener_orcl.trc
-rw-r—– 1 oracle dba 43347 Dec 23 17:40 user_access_trace.trc
-rw-r—– 1 oracle dba 231352 Dec 23 19:00 admin_access_trace.trc
-rw-r—– 1 oracle dba 103777 Dec 23 19:03
support_access_trace.trc
mylinux:>

11 This information can be used to show where connections in you database are coming from and other statistical information about the network connections being established. However, it is designed to be used by Oracle Support.

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

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

Shell script to remove trace or log files from ORACLE environment.

Leave a comment

The Oracle RDBMS produces a significant number of trace and log files, which record current status and other information related to the condition of the database system. Part of the administration of an ORACLE RDBMS is to remove these files from the OS layer once they become obsolete. This article covers the creation of shell script to remove such files from the Oracle database server in a UNIX or LINUX environment.

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

2. Logon to SQLPLUS with SYSDBA privileges.


mylinux:> sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Tue Dec 15 18:08:39 2009

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>

3. Use the SQLPLUS command show to see the location of your
background_dump_dest directory.


SQL> show parameter background_dump_dest

NAME TYPE VALUE
———————————— ———– ——————–
background_dump_dest string /orcl/admin/orcl/bdump
SQL>

4. Exit out of SQLPLUS.


SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 –
64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
mylinux:>

5. Change directories to the location of you back


mylinux:> cd /orcl/admin/orcl/bdump
mylinux:>

6. Execute the following statement to see how many trace files exist greater than 3 days old.


find . -name “*trc” -mtime +3 -exec ls -lrt {} \;

mylinux:> find . -name “*trc” -mtime +3 -exec ls -lrt {} \;
…
…
…
-rw-r—– 1 oracle dba 943 Jul 12 13:00
./orcl_m001_11864.trc
-rw-r—– 1 oracle dba 811 Jul 12 23:00
./orcl_m001_17140.trc
-rw-r—– 1 oracle dba 897 Jul 13 13:00
./orcl_m001_7152.trc
-rw-r—– 1 oracle dba 789 Jul 13 23:00
./orcl_m001_29058.trc
mylinux:>

7. Execute the following command to remove all file which are older than 3 days: find . -name “*trc” -mtime +3 -exec rm {} \;

mylinux:> find . -name “*trc” -mtime +3 -exec rm {} \;
mylinux:>

8. This shell command can be placed in an executable shell script and executed through CRON to automatically delete files older then three days. Note: The example below will remove trace files from your current directory ending in trc. You will have to replace the find “.” with find “directory_structure” to remove files in a specific directory.

Example: You wish to remove all files older then 3 days in directory
/opt/oracle/db_1/bdump the command would be.


find /opt/oracle/db_1/bdump -name “*trc” -mtime +3 -exec rm {} \;

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

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

Enable ADMIN Tracing on Oracle Listener:

Leave a comment

Most Oracle database connects are made through the use of an Oracle process called the LISTENER which monitors a certain machine and port for connection requests to one or more database instances. There are times when a DBA or SA will want to monitor connects being made through the listener for various reasons. This article covers the setup of ADMINISTRATIVE level tracing of you Oracle Listener and will work on UNIX, Linux, and Windows based machines.

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

2. Open up the LSNRCTL utility.

mylinux:>lsnrctl

LSNRCTL for LINUX: Version 10.2.0.4.0 – Production on 13-DEC-2009 15:17:05

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

Welcome to LSNRCTL, type “help” for information.

LSNRCTL>

3. If you are not using the default name of the Oracle listener (LISTENER) then define the Oracle listener name with the following command: set current_listener

LSNRCTL> set current_listener listener_orcl
Current Listener is listener_orcl
LSNRCTL>

4. If you have established a password for your oracle listener set it in the LSNRCTL session with the following command: set password

LSNRCTL> set password oracle
The command completed successfully
LSNRCTL>

5. Turn on admin level tracing by specifying 10 or admin as the level with the following command: set trc_level

LSNRCTL> set trc_level 10
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.110)(PORT=1521)))
listener_orcl parameter “trc_level” set to admin
The command completed successfully

or

LSNRCTL> set trc_level admin
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.110)(PORT=1521)))
listener_orcl parameter “trc_level” set to admin
The command completed successfully
LSNRCTL>

6. Define the name of the trace file that will be produced by the listener process upon establishment of user connection with the command below: set trc_file

LSNRCTL> set trc_file admin_access_trace
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.110)(PORT=1521)))
listener_orcl parameter “trc_file” set to admin_access_trace.trc
The command completed successfully
LSNRCTL>

NOTE: You do not have to set a trace directory by default the trace files are placed under: $ORACLE_HOME/network/trace

7. Save the changes made to the listener.ora file with the following command: save_config

LSNRCTL> save_config
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.110)(PORT=1521)))
Saved listener_orcl configuration parameters.
Listener Parameter File /opt/app/oracle/10.2.0/network/admin/listener.ora
Old Parameter File /opt/app/oracle/10.2.0/network/admin/listener.bak
The command completed successfully
LSNRCTL>

8. Exit out of LSNRCTL utility, change directories to ORACLE_HOME/network/trace, list the files present and you can see that the trace has already begun.

LSNRCTL> exit
mylinux:> ls -lrt
total 1008
-rw-r—– 1 oracle dba 408755 Dec 13 15:25 listener_orcl.trc
-rw-r—– 1 oracle dba 43347 Dec 13 15:40 user_access_trace.trc
-rw-r—– 1 oracle dba 34150 Dec 13 15:42 admin_access_trace.trc

9. Logon to the RDBMS as sysdba and exit.

mylinux:> sqlplus ‘system/devlmgr as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Sun Dec 13 15:44:02 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> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 –
64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
mylinux:>

10. Perform another listing of the files under the directory $ORACLE_HOME/network/admin and you will see that the file admin_access_trace.trc has grown by recording the connection from user in step 9.

mylinux:> ls -lrt
total 1136
-rw-r—– 1 oracle dba 408755 Dec 13 15:25 listener_orcl.trc
-rw-r—– 1 oracle dba 43347 Dec 13 15:40 user_access_trace.trc
-rw-r—– 1 oracle dba 119638 Dec 13 15:47 admin_access_trace.trc
mylinux:>

11 This information can be used to show where connections in you database are coming from and other statistical information about the network connection being established with administrative options.

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

11g, linux, network, Oracle, Oracle 10g, oracle 9i

Defining ORACLE network configuration home directory:

Leave a comment

Oracle provides the environmental variable TNS_ADMIN to direct the oracle binaries to the location of your network configuration files (TNSNAMES.ORA, SQLNET.ORA, and LISTENER.ORA). In a UNIX or LINUX environment this is normally placed in the Oracle software owner’s
.profile or .bash_profile file under the home directory. Windows definitions are covered in a separate article. This environmental variable is very helpful in redirecting multiple oracle installations to a single listener.ora; configuring multiple databases using the same home directory to separate network configuration files or for changing the network configuration location from the default of $ORACLE_HOME/network/admin to another location. In this article we will define the TNS_ADMIN.

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

2. Open your .profile or .bash_profile file in you home directory and place the following line at the end.

export TNS_ADMIN={directory_structure_for_network_files} v

3. Save your .profile or .bash_profile and exit.

4. Re-initialize the .profile or .bash_profile with the following command: . ./.bash

/home/oracle@orcl $ . ./.profile

5. Echo the parameter TNS_ADMIN to make sure that the variable was read.

/home/oracle@orcl $ echo $TNS_ADMIN
/opt/oralce/db_1/network/admin/orcl
/home/oracle@orcl $

6. Startup the listener and see that the lsnrctl utility picks up the configuration files in the new location referenced by TNS_ADMIN variable and not the default $ORACLE_HOME/network/admin

/home/oracle@orcl $ lsnrctl

LSNRCTL for LINUX: Version 9.2.0.8.0 – Production on 09-DEC-2009 17:26:23

Copyright (c) 1991, 2006, Oracle Corporation. All rights reserved.

Welcome to LSNRCTL, type “help” for information.

LSNRCTL> start
Starting /opt/oralce/db_1/bin/tnslsnr: please wait…

TNSLSNR for LINUX: Version 9.2.0.8.0 – Production
System parameter file is
/opt/oralce/db_1/network/admin/orcl/listener.ora
Log messages written to /opt/oralce/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=chase)(PORT=1526)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LUFS)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=ORCL)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=orclm)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=chase)(PORT=1526)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for LINUX: Version 9.2.0.8.0 – Production
Start Date 09-DEC-2009 17:26:27
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security OFF
SNMP OFF
Listener Parameter File
/opt/oralce/db_1/network/admin/orcl/listener.ora
Listener Log File /opt/oralce/db_1/network/log/listener.log
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=chase)(PORT=1526)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LUFS)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=ORCL)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=orclm)))
Services Summary…
Service “orcl” has 1 instance(s).
Instance “orcl”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully
LSNRCTL>

7. That completes redirect to new network configuration file location in Oracle for UNIX and LINUX servers.

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