Tag Archives: network

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

Oracle NETCA – Network Configuration assistant.

Leave a comment

Oracle provides several Configuration Assistant tools to facility administrative duties required by an Oracle DBA or Developer. The Oracle’s NETCA (Network Configuration Assistant) is the GUI tool used to configure the network components of your Oracle RDBMS. In this article we will review the startup of the Network Configuration Assistant within a RHEL environment.

1. Logon to your Oracle Database server as the Oracle software owner.
2. Change directories to your $ORACLE_HOME/bin.

mylinux:> cd $ORACLE_HOME/bin
mylinux:>

3. Export your display to your local machine if you are not logon to the server itself.

mylinux:> export DISPLAY=192.168.0.110:0.0
mylinux:>

4. You will require a terminal emulation program executing on your local machine to produce the display from your database server. Any terminal display software will do, however I would suggest VNC which can be downloaded for free at the URL: www.realvnc.com

5. Execute the dbca shell script to startup the database configuration assistant.


mylinux:> ./netca

6. The Network Configuration Assistant is self explanatory from here on. It allows the ability to configure, add, delete and modify you various Oracle networking components.

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, 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