Tag Archives: Oracle

hp-ux, itanium, linux, unix

Shell script to perform string replacement in multiple files for UNIX and LINUX:

Leave a comment

As a DBA, regardless of RDBMS type, you will come across the need to replace text strings in dozens if not hundreds of files to facilitate the completion of your job. In this article we will cover the use of bash and perl scripts to perform text replacement of multiple files within a UNIX or LINUX environment.

1. Logon to your UNIX or LINUX server as the owner of the files you want to update or a user which has permission to update these files.

2. In this procedure we will create a file named files.txt containing a listing of all files we wish to update.

mylinux:> more files.txt
./test1.txt
./test2.txt
./test3.txt
./test4.txt

3. Next create a file called update.sh with the following text.

dt=`date “+%m%d%Y”` # Gets current date.
cat ./files.txt|while read line # Reads in all files from files.txt one line at a time.
do # Opens a loop
cp $line $line$dt # copies original file to backup with file_name+date.
ls $line |xargs perl -pi -e ‘s/{old_string}/{new_string}/g’ # if found replace old_string with new_stirng
done # ends loop

4. Change permissions on the update.sh to 770, so it will execute.

mylinx:>:>chmod 770 update.sh
mylinx:>:>

5. View the contents of one of the files in you files.txt file.

mylinx:>:>cat test*
one
one
one
one
mylinx:>:>

6. In this example, all of the files contain the text “one” which we will replace with the string “two”. Thus your update.sh file will look like the example below.

dt=`date “+%m%d%Y”`
cat ./files.txt|while read line
do
cp $line $line$dt
ls $line |xargs perl -pi -e ‘s/one/two/g’
done

7. Execute the update.sh file with the command: ./update.sh.

mylinx:>:>./update.sh
mylinx:>:>

8. Now cat all files named test*

mylinx:>:>cat test*
two
two
two
two
mylinx:>

As you can see all strings of “one” have been replaced with the string “two”. This completes replacement of strings in UNIX and LINUX.

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

hp-ux, linux, unix

String replacement at UNIX or LINUX command prompt

Leave a comment

As an Oracle DBA you will come across many situations where you need to replace string text within OS layer files. This procedure covers the use of Perl command to perform such a task. This procedure will work in both UNIX and LINUX.

1. Logon to you Oracle server as the owner of the files you wish to perform a string replacement on.

2. We will use the command (“perl -pi -e ‘s/{old_string}/{new_string}/g’ {file_name}”) to perform our string replacement.

3. In this example we will replace all of the references to the string “MARS” with the string “EARTH” in any file that ends in test*sh, the command will appear as:

perl -pi -e ‘s/MARS/EARTH/g’ test*sh

4. List all of the contents of files of the format test*sh with the cat command.

mylinux:>cat test*sh
MARS
MARS
MARS
MARS
mylinux:>

5. Execute your perl command in the same directory where your the files are located.

mylinux:>perl -pi -e ‘s/MARS/EARTH/g’ test*sh mylinux:>

6. List all of the contents of the files with format of test*sh with the cat command.

mylinux:>cat test*sh
EARTH
EARTH
EARTH
EARTH
mylinux:>

As you can see above the test string “MARS” has been replaced with “EARTH”. This completes replacement of text string with Perl.

Larry 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