Tag Archives: connection

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

Auditing the connections for a particular user:

Leave a comment

Oracle provides the ability to audit your database activities on a multitude of level, providing the administrator the ability to find suspicious activity. In this article we will show how to audit a particular user’s connectivity in an Oracle database. This procedure will work on any OS.

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 Mon Feb 12 04:31:43 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>

3. Check the current setting of the initialization parameter AUDIT_TRAIL with the command: show parameter {initialization_parameter}
The initialization parameter AUDIT_TRAIL controls auditing at the database level and can be set to three definitions: 1. DB – audit trail in the database; 2. OS – audit trail on the OS; and 3. none – no auditing. In this procedure we want the definition to be set to DB.

SQL> show parameter AUDIT_TRAIL

NAME TYPE VALUE
———————————— ———– ———————-
Audit_trail string DB
SQL>

4. If the parameter is not set to DB and you are using a spfile, execute the command: alter system set audit_trail=DB scope=spfile; and bounce the database. If you are using an init file, shutdown the database add the string AUDIT_TRIAL=DB and start the database.
NOTE: If you do not know if you are using an SPFILE or INIT file, search ORACLE SPFILE vs INIT File

SQL> alter system set audit_trail=DB scope=spfile;

System altered.

SQL>

5. Restart your database if you had to change the audit_trail parameter.

6. Execute the following command to begin auditing of connection by a particular user: audit session by {user_name}

SQL> audit session by ljcatt;

Audit succeeded.

SQL>

7. Perform the following select from the view DBA_PRIV_AUDIT_OPTS to see that auditing is enabled: select user_name, privilege from dba_priv_audit_opts;

SQL> select user_name, privilege from dba_priv_audit_opts;

USER_NAME PRIVILEGE
—————————— —————————————-
LJCATT CREATE SESSION

SQL>

8. Connect as the user you are auditing.

SQL> connect ljcatt
Enter password:
Connected.
SQL>

9. Reconnect as sysdba.

SQL> connect / as sysdba
Connected.
SQL>

10 Perform a select against the view dba_audit_trail to determine if the connection was recorded.

SQL> select username, returncode, action_name from dba_audit_trail;

USERNAME RETURNCODE ACTION_NAME
—————————— ———- —————————-
LJCATT 0 SESSION REC
LJCATT 0 LOGOFF
SQL>

As seen in the output above, the session reconnection and then log off where both successfully record with a return code of zero. If either action failed, the return code would show the error number produced. This completes auditing of a particular user account in Oracle RDBMS.

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

linux, Oracle, Oracle 10g, unix

Enable User 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 USER 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.

myhpux:>lsnrctl

LSNRCTL for HPUX: Version 10.2.0.4.0 – Production on 21-OCT-2009 18: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 user level tracing by specifying 4 or user as the level with the following command: set trc_level

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

or

LSNRCTL> set trc_level user
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.110)(PORT=1521)))
listener_orcl parameter “trc_level” set to user
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 user_access_trace
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.110)(PORT=1521)))
listener_orcl parameter “trc_file” set to user_access_trace.trc
The command completed successfully
LSNRCTL>

NOTE: You do not have to set a trace directory by default the trace files for the listener 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, and list the files present and you will see that the trace has already begun.

LSNRCTL> exit
mylinux:> cd $ORACLE_HOME/network/trace
mylinux:> ls -lrt
-rw-r—– 1 oracle dba 3010 Oct 21 18:26 user_access_trace.trc

9. Logon to the RDBMS as a normal user and exit.

mylinux:> sqlplus ljcatt/ljcatt@orcl

SQL*Plus: Release 10.2.0.4.0 – Production on Wed Oct 21 18:31:26 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> 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 user_access_trace.trc has grown by recording the connection
from user in step 9.

mylinux:> ls -lrt
-rw-r—– 1 oracle dba 6810 Oct 21 18:31 user_access_trace.trc

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.

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