Category Archives: Oracle

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

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

Auditing the use of an Oracle privilege:

Leave a comment

Oracle provides the ability to audit your database activities on a multitude of level which provides the administrator the ability to find suspicious activity. In this article we will show how to audit use of a system privilege by a particular user in the Oracle RDBMS. 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 Sat Nov 14 13:28: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>

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. In this procedure we will audit the creation of tables by the user LJCATT. To perform this auditing, we will use the command: audit create table by {user_name};

SQL> connect / as sysdba
Connected.
SQL> audit create table by ljcatt;

Audit succeeded.

SQL>

7. Perform a select on the view DBA_PRIV_AUDIT_OPTS, to verify that auditing is enabled.

SQL> select user_name, privilege from dba_priv_audit_opts;

USER_NAME PRIVILEGE
—————————— —————————————-

LJCATT CREATE TABLE

SQL>

8. Logon as the user you are auditing and create a table.

SQL> connect ljcatt
Enter password:
Connected.
SQL> create table test_audit(col_a varchar2(30));

Table created.

SQL>

9. Logon with sysdba privileges and verify that the create table action was recorded in you DBA_AUDIT_TRAIL view. As seen below the audit record for the create table privilege was recorded.

SQL> connect / as sysdba
Connected.
SQL>
SQL> select username, action_name from dba_audit_trail;

USERNAME ACTION_NAM
———- ———-
LJCATT CREATE TABLE

This completes auditing of a system privilege by a user account within the Oracle RDBMS.

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

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

Senior-Level DBA Interview Question:

Leave a comment

With well over a decade of Oracle experience from version 6 to 11g, I have had my share of interviewing prospective DBA employees in a range from Junior to Senior DBAs. Needless to say in this field, technical competence is the key to any new hire and early on I made the blunder of not asking the write questions during the interview. I am sure every senior person or manager has made this mistake and had to deal with either training the person from scratch or letting them go. Either of these options is not desirable in our fast past business world. So, I decided to write a couple of articles which cover what I believe a Junior, Mid, and Senior Oracle DBA should know to fill their prospective positions.

In this article we will cover what a Senior Level DBA should know to be successful. If you have any comments or questions, please email me at larry.catt@relidb.com.

Questions: NOTE: Answers here can be very broad, however they not always wrong. That is why it is important to have a proven DBA on staff or one you can call to review the answers. I have seen very inventive ways to get around the most impossible tasks and while they do not actually meet the standard way of doing the work, I would not count these people out. A person whom thinks outside of the box is very valuable in IT and believe it or not, some of the best DBA’s I have ever met thought completely outside the box, however always accomplished the task.

1. You have a brand new Solaris Machine, freshly built by the SA, with all Oracle RDBMS recommended patching and Kernel Parameters set. Please give the order of tasks to install Oracle up to its latest CPU?

2. You have just moved Oracle Binary files from an ORACLE_HOME on one server to another server. What is the oracle utility command you would use to make this new ORACLE_HOME usable?

3. Tell me why just copying the oracle datafiles from HP-UX to LINUX RHEL and starting the database would not work?

4. In what months does Oracle release CPU patches and what is the utility to apply them.

5. You need to shutdown the oracle database but it will not shutdown with SHUTDOWN IMMEDIATE. How will you shutdown the database?

6. How do you logon to Oracle server without knowing any oracle passwords?

7. How would you determine that SCATTERED READ where occuring?

8. How would you see the SQL of the currently active sessions from a command prompt?

9. When having multiple oracle homes on a single server or client what is the parameter that points all Oracle installs at one TNSNAMES.ORA file.

10. You have ASM database that used by three production systems. The server which the ASM database is shutdown. What happens to the production RDBMS?

Answers:

1. You have a brand new Solaris Machine, freshly built by the SA, with all Oracle RDBMS recommended patching and Kernel Parameters set. Please give the order of tasks to install Oracle up to its latest CPU?

– Verify that all patches are installed and kernel parameters set.(Some not all SA say the system is ready when it is not. You can save yourself a lot of problems by double checking)
– Configuration of .profile or .bash_profile for the Oracle owner.
– Base Installation of Oracle (ie what to get to 10.2.0.4 must install Oracle 10.1 first).
– Upgrade to Oracle Version Desired.
– Upgrade the database(if upgrade is necessary)
– Run UTLRP.SQL.
– Apply lastest CPU patch.
– Run UTLRP.SQL.
– Configuration of Listener.
– Configuration of client TNSNAMES.ORA file.

2. You have just moved Oracle Binary files from an ORACLE_HOME on one server to another server. What is the oracle utility command you would use to make this new ORACLE_HOME usable?

Solution:
Relink all

3. Tell me why just copying the oracle datafiles from HP-UX to LINUX RHEL and starting the database would not work?

Solution:
Differing Indian codes.

4. In what months does Oracle release CPU patches and what is the utility to apply them.

Solution:
JAN, APR, JUL, OCT

5. You need to shutdown the oracle database but it will not shutdown with SHUTDOWN IMMEDIATE. How will you shutdown the database?

Solution:
Kill the smon process.

6. How do you logon to Oracle server without knowing any oracle passwords?

Solution:
Sudo to the Oracle software owners account and logon with the command sqlplus ‘/ as sysdba’

7. How would you determine that SCATTERED READ where?
Solution:

Look in the views v$session_wait or v$session_wait_history.

Could give:

Statspack or EM report.

8. How would you see the SQL of the currently active sessions from a command prompt?

Solution:
Compare tables V$SQL view by sql_address with V$SESSION view of currently active sessions.

9. When having multiple oracle homes on a single server or client what is the parameter that points all Oracle installs at one TNSNAMES.ORA file.

Solution:
TNS_ADMIN

10. You have ASM database that used by three production systems. The server which the ASM database is shutdown. What happens to the production RDBMS?

Solution:
Other database would shutdown abort.

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

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

Oracle Error – ORA-01996: GRANT failed: password file ” is full

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. However, the Oracle password file is created with a set size which is not designed to expand to allow for additional information to be stored. Thus, the utility ORAPWD has a clause named ENTRIES which defines the maximum number of DBA user accounts with SYSDBA privileges you would every need. Once the entries clause is exceeded, you will receive the error ORA-01996. The only way to resolve this error is to recreate your password file with the utility ORAPWD. This article covers the recreation of a new password file. 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 Sun Oct 25 11:21:31 2009

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

SQL>

3. You attempt to grant SYSDBA to a user and receive the following eerror; your password file is full.

SQL> grant SYSDBA to SYSTEM
*
ERROR at line 1:
ORA-01996: GRANT failed: password file ” is full

4. Perform the following to resolve this issue. Navigate to the following directory $ORACLE_HOME/dbs and list the file orapw* to check the current name of your password file.

mylinux :> cd $ORACLE_HOME/dbs
mylinux :> ls -lrt
total 272
-rw-r–r– 1 oracle dba 3979 Dec 13 07:57 initorcl.ora
-rw-r—– 1 oracle dba 5120 Mar 19 18:12 spfileorcl.ora
mylinux :>

3. In this example our SID is ORCL and we have located a file of the orapworcl. Rename the file with the following command:

mv orapworcl orapworcl_bak

4. Now we are ready to re-create our password file with the ORAPWD utility. Ensure that the entries clause is larger then you ever anticipate the number of DBAs to be.

mylinux :>orapwd file=./orapworacle password=password1 entries=100
mylinux :>

6. Execute the ls command to see the new password file.

mylinux :> ls orapw*
orapworcl
mylinux :>

7. Enter SQLPLUS with SYSDBA privileges.

mylinux :> sqlplus ‘/ as SYSDBA’

SQL*Plus: Release 10.2.0.4.0 – Production on Oct 25 11:39:25 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>

8. Ensure that the init parameter REMOTE_LOGIN_PASSWORDFILE is set to EXCLUSIVE through the use of the show command.

SQL> show parameter REMOTE_LOGIN_PASSWORDFILE

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

9. If the parameter was something other then EXCLUSIVE, use the alter system command to change it to exclusive and restart the database.

10. Now you have to re-grant the SYSDBA privilege to all users which previously had the privilege and the new DBA user which caused the original error message. Use the command:

grant SYSDBA to {username};

Or see the article ‘Oracle determining users that are in the password file before recreating’

This completes recreation of Oracle password file due to error ORA-01996

Larry J. Catt, OCP 9i, 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

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