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

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

Oracle – Adding DBA user to password file:

Leave a comment

Oracle provides for a normal DBA user account to remotely connect to a database with SYSDBA privileges through the use of a password file. This article will cover granting a normal DBA user the SYSDBA privilege which in turn adds the user to the password file for that database.
This allows the user to connect remotely to the database for the purpose of shutting down the database and starting the database.
This procedure will work on any OS.

1. From a remote machine, not your Oracle database server, attempt to connect as SYSDBA to your database with an account which has DBA privileges.

C:\> sqlplus ljcatt/password5@oracle as SYSDBA

SQL*Plus: Release 10.2.0.1.0 – Production on Sun Oct 18 13:32:27 2009

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

ERROR:
ORA-01031: insufficient privileges

Enter user-name:

Now attempt to connect without specifying SYSDBA privilege.

C:\> sqlplus ljcatt/password5@oracle

SQL*Plus: Release 10.2.0.1.0 – Production on Sun Oct 18 13:34:27 2009

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. The reason this fails with insufficient privileges at first, is because the user LJCATT is not recorded in the database password file for remote access with SYSDBA privilege. The following steps outline how to add the user LJCATT to the remote access password file.

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

4. Logon to SQLPLUS with SYSDBA privileges.

mylinux :> sqlplus ‘/ as SYSDBA’

SQL*Plus: Release 10.2.0.4.0 – Production on Sun Oct 18 13:42:31 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>

5. Verify that you are using a password file by ensuring that the init parameter REMOTE_LOGIN_PASSWORDFILE is set to exclusive or shared.

SQL> show parameter password

NAME TYPE VALUE
———————————— ———– ——————
REMOTE_LOGIN_PASSWORDFILE string EXCLUSIVE
SQL>

6. If the REMOTE_LOGIN_PASSWORDFILE init parameter is set to NONE, adjust it to EXCLUSIVE or SHARED.

7. In SQLPLUS, grant the privilege SYSDBA to LJCATT.

SQL> grant SYSDBA to ljcatt;

Grant succeeded.

SQL>

8. Now, the user LJCATT has been added to the password file. From a remote machine, not your Oracle database server, attempt to connect to your database with the account LJCATT with SYSDBA.

C:\> sqlplus ljcatt/password5@oracle as SYSDBA

SQL*Plus: Release 10.2.0.1.0 – Production on Sun Oct 18 13:59:08 2009

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>

This completes adding a user to the remote password file for access as SYSDBA from a remote connection.

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

linux, Oracle, Oracle 10g, oracle 9i

SQLPLUS does not allow connect as sysdba:

Leave a comment

In Oracle configuration files and system parameters there exist certain setting which will prevent the Oracle software owner from connecting to the database with sysdba privileges. In this article we will discuss a SQLNET.ORA setting which will result in this error. This article was written using a linux environment; however the result will be very similar on any OS.

1. Logon to your Oracle server as the Oracle software owner and attempt to logon to sqlplus with sysdba privileges.

mylinux:>sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Tue Oct 19 19:00:46 2009

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

ERROR:
ORA-01031: insufficient privileges

Enter user-name:

2. Exit out of sqlplus by hitting return twice.

3. Change directory to $ORACLE_HOME/network/admin.

mylinux:>cd $ORACLE_HOME/network/admin
mylinux:>

4. Open the file sqlnet.ora and comment out the line sqlnet.authentication.

mylinux:>vi sqlnet.ora
“sqlnet.ora” 2 lines, 59 characters
#sqlnet.authentication_services=none

5. Save your changes and exit your editor.

6. Now try to connect to sqlplus with sysdba privileges as the oracle software owner.

mylinux:>sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Tue Oct 19 19:05:01 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>

You have successfully connected with SYSDBA privilege.

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

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

Changing init parameters with an SPFILE:

Leave a comment

Since Oracle 9i, Oracle has introduced the use of SPFILE over INIT files for control of the RDBMS initialization parameters. The introduction of SPFILE was to allow for the dynamic changing of initialization parameters setting while the database is up and eliminating the need to open an OS layer file to make changes to the ORACLE initialization parameters. This article will cover: how to change initialization parameter from within an Oracle database. This procedure is the same on any OS.

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

2. Connect to SQLPLUS with SYSDBA privileges.

mylinux :> sqlplus ‘/ as SYSDBA’

SQL*Plus: Release 10.2.0.4.0 – Production on Sun Sep 27 09:49:04 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. We use the alter system command to change initialization parameters from within the Oracle database while an SPFILE is in use. The following is an example of the alter system command.

alter system set {initialization_parameter} = {new_value} scope = {scope}

4. The key here is the scope clause which can have three possible values:

MEMORY – No changes are made to the SPFILE at all and the change is only mode in the current instance. To use this option, the initialization parameter must be dynamic and modifiable.

BOTH – Changes are made both in the current instance and in the SPFILE for future restart of the database. To use this option, the initialization parameter must be dynamic and modifiable.

SPFILE – Changes are not made in the current instance and the SPFILE is updated with new initialization parameter setting; however it will only take affect on restart of the database.

5. Examples of changing initialization parameter with alter system command:

SQL> alter system set user_dump_dest = ‘/opt/oracle/udump’ scope=both;

System altered.

SQL> alter system set user_dump_dest = ‘/opt/oracle/udump ‘ scope=memory;

System altered.

SQL> alter system set audit_file_dest=’/opt/oracle/udump ‘ scope=spfile;

System altered.

This completes adjusting initialization parameters in an SPFILE.

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

Oracle, Oracle 10g, oracle 9i

Determining if an initializing parameter is dynamic or not:

Leave a comment

Since Oracle 9i, Oracle has introduced the use of SPFILE over INIT files for control of the RDBMS initialization parameters. The introduction of SPFILE was to allow for the dynamic changing of initialization parameters setting while the database is up and eliminating the need to open an OS layer file to make changes to the ORACLE initialization parameters. This article will cover: how to determine if an initialization parameter is dynamic. This procedure is the same on any OS.

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

2. Connect to SQLPLUS with SYSDBA privileges.

mylinux :> sqlplus ‘/ as SYSDBA’

SQL*Plus: Release 10.2.0.4.0 – Production on Fri Sep 18 20:49:04 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. Execute the following SQL statement: select NAME, ISSYS_MODIFIABLE from V$PARAMETER;

SQL> set pagesize 500
SQL> column name format a30
SQL> select NAME, ISSYS_MODIFIABLE from V$PARAMETER;
NAME ISSYS_MODIFIABLE
——————— ——————-
..
..
..
user_dump_dest IMMEDIATE
max_dump_file_size IMMEDIATE
audit_file_dest DEFERRED
LINUX_sched_noage FALSE
object_cache_optimal_size DEFERRED
object_cache_max_size_percent DEFERRED
session_max_open_files FALSE
..
..
..
SQL>

4. Note: In the example above we have the NAME of the initialization parameter and three values for ISSYS_MODIFIABLE column, we can use this to determine if the initialization parameter is modifiable or not.

IMMEDIATE – The parameter is dynamic and can take affect immediately no matter if you are using a SPFILE or INIT file. When using an SPFILE you can use the clause SCOPE in the following ways: BOTH = change will take affect immediately and on restart of database; MEMORY = change will only take affect in current instance; and SPFILE = change will only take affect on restart of database. When using an INIT file you can use the clause SCOPE in the following ways: MEMEORY = change will only take affect in current instance.

DEFERRED – The parameter is not dynamic and will not take affect immediately. When using an SPFILE you can use the clause SCOPE in the following ways: SPFILE = change will only take affect on restart of database. When using an INIT file you have to manually update INIT file and restart database.

FALSE – The parameter is not dynamic and cannot be changed immediately. When using an SPFILE you can use the clause SCOPE in the following ways: SPFILE = change will only take affect on restart of database. When using an INIT file you have to manually update INIT file and restart database.

This completes determining if an initialization parameter is dynamic.

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

Oracle 10g, RAC, SQL

Taking an Oracle database out of a multi-Node RAC

Leave a comment

I have found during some administrative tasks it is easier to have your system in single NODE configuration. An example of this, would be physical changes the database structure that due not include binary upgrades such as a CPU patch. It is easier to startup, shutdown, and restrict access thorough single stand alone system, rather then a RAC environment with several active nodes. I realize that most of you are quite verse in the user of the SRVCTL utility to administrate the instances and database. However, I would like to show the use of starting up the database in single node from within the database and using SRVCTL just to verify the status.

1. Logon to one node as sysdba.

mylinux1>$ sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Thu Sep 3 18:47:59 2009

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

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

SQL>

2. Execute the following SQL “select inst_id, instance_name, instance_role, host_name from gv$instance;” and as you can see we are attached to a two node RAC.

SQL> select inst_id, instance_name, instance_role, host_name from gv$instance;

INST_ID INSTANCE_NAME INSTANCE_ROLE HOST_NAME
———- ————– —————– ——————
1 node1 PRIMARY_INSTANCE mylinux1
2 node2 PRIMARY_INSTANCE mylinux2

SQL>

3. Exit out of SQL*PLUS and use the SRVCTL utility to obtain the current RAC status. The utility SRVCTL verifies what we saw in the data dictionary view of GV$INSTANCE.

mylinux1>$ srvctl status database -d orcl
Instance node1 is running on node mylinux1
Instance node2 is running on node mylinux2

4. Due to the fact that we have two instance running, we will use the SRVCTL utility to shutdown the database.

mylinux1>$ srvctl stop database -d orcl

5. Again use the SRVCTL utility to view the status of your Instances and the results are displayed that both instance are shutdown.

srvctl status database -d orcl
Instance node1 is not running on node mylinux1
Instance node2 is not running on node mylinux2
mylinux1>$

6. Now you do have a choice here: 1. You could startup the database and a single instance with the SRVCTL utility or 2. You could logon to SQL*PLUS like a non-RAC system and startup the instance and database. We will logon to SQL*PLUS as sysdba and issue the startup command.

mylinux1>$ sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Thu Sep 3 19:02:13 2009

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1795162112 bytes
Fixed Size 1262332 bytes
Variable Size 587205892 bytes
Database Buffers 1191182336 bytes
Redo Buffers 15511552 bytes
Database mounted.
Database opened.
SQL>

7. Execute the following SQL “select inst_id, instance_name, instance_role, host_name from gv$instance;” to display the results of starting the database from NODE1.

SQL> select inst_id, instance_name, instance_role, host_name from gv$instance;

INST_ID INSTANCE_NAME INSTANCE_ROLE HOST_NAME
———- ————– —————– ——————
1 node1 PRIMARY_INSTANCE mylinux1

SQL>

8. You can verify these results with the SRVCTL utility.

mylinux1>$ srvctl status database -d orcl
Instance node1 is running on node mylinux1
Instance node2 is not running on node mylinux2
mylinux1>$

9. Your system is now executing in a SINGLE NODE fashion and you can began your administrative tasks.

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

Oracle, Oracle 10g, RAC

Oracle RAC and difference between V$ and GV$ views

Leave a comment

Since the introduction of Oracle RAC, we have seen the additional system dictionary views of GV$ which reflect information across the various nodes of a RAC system. Unlike the V$ views of the data dictionary, which relate to the current status of the single node you are connected to, the GV$ allows you to see status throughout the system. In this article we will show how to view sessions through the entire system instead of just a single node.

1. Connect to NODE1 of your Oracle RAC system as a DBA users, in this example we connect as the user ljcatt..

mylinux>$ sqlplus ljcatt/ljcatt

SQL*Plus: Release 10.2.0.4.0 – Production on Tue Sep 1 18:05:43 2009

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

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

SQL>

2. Connect to NODE2 of your Oracle RAC as sysdba.

mylinux>$ sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Tue Sep 1 18:07:28 2009

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

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

SQL>

3. Use the SQL statement “select distinct(username) from v$session;” to view the current connections within NODE2 of your RAC. NOTE: You will not be able to see the connection from LJCATT in this dictionary view, because it only reflects connections to NODE2 and not the entire RAC.

SQL> select distinct(username) from v$session;

USERNAME
——————————

SYS

4. Wile still connected to NODE2 as sysdba, use the SQL statement “select distinct(username) from gv$session;” to view all sessions within the RAC. NOTE: Now you can see the connection to LJCATT.

SQL> select distinct(username) from gv$session;

USERNAME
——————————

LJCATT
SYS

SQL>

5. The GV$ dictionary views have the additional INST_ID column which give the node that the action is occurring in. Use the SQL statement “select inst_id from gv$session where username=’LJCATT’” to identify the NODE which LJCATT is currently connected to.

SQL> Select inst_id from gv$session where username=’LJCATT’;

INST_ID
———-
1

SQL>

The lesson learned, when you are administrating a RAC system use the dictionary views of GV$ and not V$.

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

Oracle tips and tricks.