Category Archives: 11g

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

Oracle – Killing a session which is causing performance issues

Leave a comment

The Oracle RDBMS provides the DBA with the ability to kill a user’s session. On occasion you will find a particular account which is causing performance issues or performing an action which may be harmful to the database. In this situation, it may be best to kill the session. This article covers the use of “alter system” command to kill a users session.

1. Logon to you Oracle database server.

2. Logon to SQLPLUS with sysdba privileges.


mylinux:> sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Sat Jan 02 08:15:39 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. In this example, we know the session causing the issue is owned by the user LJCATT. To kill the session we must identify the SID and SERIAL# of the session owned by LJCATT. Execute the following SQL to get the SID and SERIAL# from his session.


SQL> select SID, SERIAL#, username from v$session where username=’LJCATT’;

SID SERIAL# USERNAME
———- ———- ——————————
3979 108 LJCATT

SQL>

4. To kill the session use the following command: alter system kill session ‘SID,SERIAL#’ immediate;


SQL> alter system kill session ‘3979, 108′ immediate;

System altered.

SQL>

5. Re-execute the SQL in step 3 to verify that the session has been killed.

SQL> select SID, SERIAL#, username from v$session where username=’LJCATT’;

no rows selected

SQL>

NOTE: If the user has performed a large amount of updates, insert, or deletes; the kill operation may take awhile to complete, due to rollback operations.

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

11g, Oracle, Oracle 10g

Oracle – Currently executing long SQL operations.

Leave a comment

The Oracle RDBMS provides multiple views detailing the current activities within the database, which can be used to perform analysis on performance. This article covers the user of the views: V$SQL, V$SESSION_LONGOPS, and V$SESSION to determine the longest currently occurring SQL statements within our database and who is executing them. This procedure will work on any OS.

1. Logon to you Oracle database server.

2. Logon to SQLPLUS with sysdba privileges.


mylinux:> sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Sat Dec 26 08: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. Execute the following SQL statement.


select b.username, b.SID, b.SERIAL#, c.totalwork||’ ‘||c.units,
c.time_remaining, a.sql_text from v$sql a,
v$session b,
v$session_longops c
where a.address=b.sql_address
and b.status=’ACTIVE’
and b.SID||b.SERIAL#=c.SID||c.SERIAL#
and c.time_remaining>0 and b.username<>‘SYS’

4. The returned values are as follows:


USERNAME – User executing this statement.
SID – System ID of session executing.
SERIAL# – SERIAL# of session executing.
TOTALWORK+UNITS – Total Work and units of measure of work.
TIME_REMAINING – Estimate in seconds of time remaining.
SQL_TEXT – SQL being executed.

5. This information can be used to determine current performance and possible performance problems.

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

11g, audit, Oracle

Viewing all auditing occurring within an Oracle RDBMS:

Leave a comment

Oracle provides the ability to audit your database activities on a multitude of level which providing the administrator the ability to find suspicious activity. This article covers the display of all auditing which is currently defined in an Oracle RDBMS. This procedure will work regardless of OS type.

NOTE: The initialization parameter AUDIT_TRAIL controls auditing at the entire 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 AUDIT_TRAIL must be set to DB or OS and the procedure does not shutdown auditing at the database level.

1. Auditing definitions can be seen in three views: DBA_OBJ_AUDIT_OPTS, DBA_PRIV_AUDIT_OPTS, and DBA_STMT_AUDIT_OPTS

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

3. Logon to SQLPLUS with sysdba privileges.

mylinux:>sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Sat Dec 17 07:11:59 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>

4. Execute the following PL/SQL block to display all auditing which is currently defined in your ORACLE RDBMS.

————————————————————–
Beginning of PL/SQL Block
———————————————————————–

set serveroutput on

declare

v_ct number;

begin

–this block removes all auditing from an oracle RDBMS system.

for v_stmt in(select ‘audit all by ‘ || user_name as stmt from
sys.dba_priv_audit_opts)
loop
dbms_output.put_line(v_stmt.stmt);
end loop;

for v_stmt in(select ‘audit ‘ ||audit_option||’ by ‘ ||user_name as stmt from
sys.dba_stmt_audit_opts)
loop
dbms_output.put_line(v_stmt.stmt);
end loop;

for v_stmt in(select ‘audit all on ‘ ||owner||’.’||object_name as stmt from
sys.dba_obj_audit_opts)
loop
dbms_output.put_line(v_stmt.stmt);
end loop;

end;
/
———————————————————————–
end of PL/SQL Block
———————————————————————–

———————————————————————–
Beginning of PL/SQL Block output
———————————————————————–

SQL> set serveroutput on
SQL>
SQL> declare
2
3 v_ct number;
4
5 begin
6
7 –this block removes all auditing from an oracle RDBMS system.
8
9 for v_stmt in(select ‘audit all by ‘ || user_name as stmt from
sys.dba_priv_audit_opts)
10 loop
11 dbms_output.put_line(v_stmt.stmt);
12 end loop;
13
14 for v_stmt in(select ‘audit ‘ ||audit_option||’ by ‘ ||user_name as stmt
from sys.dba_stmt_audit_opts)
15 loop
16 dbms_output.put_line(v_stmt.stmt);
17 end loop;
18
19 for v_stmt in(select ‘audit all on ‘ ||owner||’.’||object_name as stmt from
sys.dba_obj_audit_opts)
20 loop
21 dbms_output.put_line(v_stmt.stmt);
22 end loop;
23
24 end;
25 /
audit all by LJCATT
audit all by LJCATT
audit CREATE SESSION by LJCATT
audit CREATE TABLE by LJCATT
audit all on LJCATT.TEST

PL/SQL procedure successfully completed.

SQL>
———————————————————————–
Ending of PL/SQL Block output
———————————————————————–

This completes viewing of all auditing occurring within an Oracle RDBMS.

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

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

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