Category Archives: itanium

11g, hp-ux, itanium, linux, network, Oracle, Oracle 10g, oracle 9i, unix

Advantages and Disadvantages of single user owning multiple databases with separate ORACLE_HOME:

Leave a comment

Oracle provides for the installation of multiple ORACLE_HOMEs on a single server for support of separate databases. This is normally seen in production systems to allow for isolation of databases residing on the same server. In this article will discuss the advantages and disadvantages of such a configuration, in order to determine which would best suit your needs.

Advantages:

1. Oracle database software is physically separated and corruption will not affect all databases.
2. Ability to configure and manage network configuration to one database without affecting the others.
3. Ability to patch single database without affecting other databases.
4. Configuration changes to one database will not impact other databases.

Disadvantages:

1. Increase maintenance and software configuration work required.
2. Increase disk storage requirements.
3. Increase management of network configurations.
4. Increase use of system resources.

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

11g, hp-ux, itanium, linux, network, Oracle, Oracle 10g, oracle 9i, unix

Advantages and Disadvantages of single user owning multiple databases with a single ORACLE_HOME:

Leave a comment

Oracle provides for the creation of multiple Oracle database configurations which all use a single Oracle installation. This is normally seen in test and development system to allow for a single server to house multiple databases with reduced space usage. In this article will discuss the advantages and disadvantages of such a configuration, in order to determine which would best suit your needs.

Advantages:

1. Uses less disk space.
2. Environmental parameter setup centrally located.
3. Reduced time in patching all databases.
4. Reduced network setup configuration.

Disadvantages:

1. Corruption of binaries affects all databases.
2. Unable to patch a single database instance without affecting all instances.
3. Unable to shutdown network configuration to single database.
4. Oracle software owner is a single point of failure.

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

11g, hp-ux, itanium, linux, network, Oracle, Oracle 10g, oracle 9i, unix

Oracle – Profile for single User owning multiple databases with Single

Leave a comment

In test and development database environments, it is desirable to use one Oracle installation for multiple databases. The use of a single Oracle installation for multiple databases reduces the amount of space being used and creates a single place for update of binary files. This is easily accommodated in a LINUX or UNIX environment through the proper setting of your .profile or .bash_profile file. In this article we will review the configuration of your profile file to use a single Oracle home with multiple databases.

1. To accomplish the setup of our profile for multiple databases on a single ORACLE_HOME, we will have to create a .profile with three sections: 1. Request for information; 2. Local variables definitions; and 3. global variables definitions. The request for information section will appear to the user upon logon or re-initialization of the .profile and determines which database the user wishes to use. The local variable section defines the variables need to connect to a particular database. The global variable section defines the variables which will be the same regardless of our connection to a certain database.

2. The request for information section defines the variable “database” which is used to set the ORACLE_SID variable, as seen below.

## Beginning of request for information
database=””
while [ -z “$database” ]
do
echo “\n\nEnter database & Oracle version to use:”
echo “1 – ORCL 10.2.0.4 ”
echo “2 – ORCL2 10.2.0.4”
echo “3 – ORCL3 10.2.0.4”
print -n “> ”
read database
if [[ “$database” != “1” &&
“$database” != “2” &&
“$database” != “3” ]]
then
echo “$database” is not valid >&3
database=””
fi
done
## Ending of request for information

3. The local variable section defines all variables for a specific database, as seen below.

##Beginning of local variable definitions
if [[ $database = “1” ]]
then
export ORACLE_SID=orcl
elif [[ $database = “2” ]]
then
export ORACLE_SID=orcl2
elif [[ $database = “3” ]]
then
export ORACLE_SID=orcl3
else
echo “Invalid options ”
fi
## Ending of local variable definitions

4. The global variable section defines all variables for any database using this specific ORACLE_HOME, as seen below.

## Beginning of global variable definitions

export NLS_LANG=AMERICAN_AMERICA.US7ASCII
export ORACLE_BASE=/opt/app/oracle/product
export ORACLE_HOME=/opt/app/oracle/product/10.2
export PATH=.:/opt/perl/bin:$PATH:$ORACLE_HOME/bin
export SHLIB_PATH=$ORACLE_HOME/lib:/usr/lib:/opt/java1.3
export TNS_AMDIN=$ORACLE_HOME/network/admin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/obackup/bin
export ORACLE_TERM=vt220

cd

echo “Your ORACLE_BASE is set to ‘$ORACLE_BASE'”
echo “Your ORACLE_SID is set to ‘$ORACLE_SID'”
echo “Your ORACLE_HOME is set to ‘$ORACLE_HOME'”

umask 022
export PS1=$ORACLE_SID” $ ”
stty erase ^?
export LINES=24
export COLUMNS=80
export PATH=$PATH:/opt/java1.5:/opt/java1.5/bin

## Ending of global variable definitions

5. Place all profile sections into one file named .profile from UNIX or .bash_profile for linux under your /home/{username} directory. In this example, I will place the following text in the file /home/lcatt/.profile for the OS user lcatt.

## Beginning of request for information
database=””
while [ -z “$database” ]
do
echo “\n\nEnter database & Oracle version to use:”
echo “1 – ORCL 10.2.0.4 ”
echo “2 – ORCL2 10.2.0.4”
echo “3 – ORCL3 10.2.0.4”
print -n “> ”
read database
if [[ “$database” != “1” &&
“$database” != “2” &&
“$database” != “3” ]]
then
echo “$database” is not valid >&3
database=””
fi
done
## Ending of request for information

##Beginning of local variable definitions
if [[ $database = “1” ]]
then
export ORACLE_SID=orcl
elif [[ $database = “2” ]]
then
export ORACLE_SID=orcl2
elif [[ $database = “3” ]]
then
export ORACLE_SID=orcl3
else
echo “Invalid options ”
fi
## Ending of local variable definitions

## Beginning of global variable definitions

export NLS_LANG=AMERICAN_AMERICA.US7ASCII
export ORACLE_BASE=/opt/app/oracle/product
export ORACLE_HOME=/opt/app/oracle/product/10.2
export PATH=.:/opt/perl/bin:$PATH:$ORACLE_HOME/bin
export SHLIB_PATH=$ORACLE_HOME/lib:/usr/lib:/opt/java1.3
export TNS_AMDIN=$ORACLE_HOME/network/admin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/obackup/bin
export ORACLE_TERM=vt220

cd

echo “Your ORACLE_BASE is set to ‘$ORACLE_BASE'”
echo “Your ORACLE_SID is set to ‘$ORACLE_SID'”
echo “Your ORACLE_HOME is set to ‘$ORACLE_HOME'”

umask 022
export PS1=$ORACLE_SID” $ ”
stty erase ^?
export LINES=24
export COLUMNS=80
export PATH=$PATH:/opt/java1.5:/opt/java1.5/bin

## Ending of global variable definitions

6. Change directory to your home directory with command cd, list current user with command id, and Re-initialize your .profile file as shown below:

$ orcl $ cd
orcl $ id uid=103(lcatt) gid=20(users) groups=200(dba)
orcl $ . ./.profile

7. Upon re-initialization of the profile, you will be able to select the database environment you wish, as seen below. In this example we have select 1 to setup the variables for the database ORCL.

Enter database & Oracle version to use:
1 – ORCL 10.2.0.4
2 – ORCL2 10.2.0.4
3 – ORCL3 10.2.0.4
> 1
Your ORACLE_BASE is set to ‘/opt/app/oracle/product’
Your ORACLE_SID is set to ‘orcl’
Your ORACLE_HOME is set to ‘/opt/app/oracle/product/10.2’
orcl $

This completes setup of .profile for multiple databases using the same oracle home.

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

hp-ux, itanium, Oracle, Oracle 10g

Listener Configuration on HP-UX Itanium Server: HPUX Error: 239: Connection refused

Leave a comment

I was setting up a test instance for one of our development groups today and came across a rather strange error, which I have only encountered once before. Of course I forgot the solution and tried to google it, but found no successful solutions. Thus, this article: A friend of mine tried metalink and found the answer; I have been shying away from metalink since the website redesign.

I was trying to configure the listener for this new system. At first I configured listener.ora as I normally would and the listener appeared to start okay, however when I performed the lsnrctl status command I received the following error:

TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
HPUX Error: 239: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
HPUX Error: 239: Connection refused

I immediately went to the standby solution in this scenario and started up netca the network assistance tool to configure the listener and received the exact same error. Below is listed the solution in order of trial and error, I am not sure if this is limited to HP-UX itanium machines or not, but that is where I received the error.

Credit goes to: Muhil Jayaraman, One of the best Core Oracle DBA’s and EBS DBA’s I know. He is proof that after 15 years as an Oracle DBA, you can still learn new things by working with great people.

1. Started the listener with command lsnrctl start.

$ lsnrctl start

LSNRCTL for HPUX: Version 10.2.0.4.0 – Production on 23-MAR-2010 14:22:42

Copyright (c) 1991, 2007, Oracle. All rights reserved.

Starting /u01/oracle/bin/tnslsnr: please wait…

TNSLSNR for HPUX: Version 10.2.0.4.0 – Production
System parameter file is /u01/oracle/network/admin/listener.ora
Log messages written to /u01/oracle/network/admin/listener.log
Listening on:
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.110)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))

Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.110)(PORT=1521)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for HPUX: Version 10.2.0.4.0 – Production
Start Date 23-MAR-2010 14:22:42
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/oracle/network/admin/listener.ora
Listener Log File /u01/oracle/network/admin/listener.log
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.110)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
Services Summary…
Service “PLSExtProc” has 1 instance(s).
Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service…
Service “orcl” has 1 instance(s).
Instance “orcl”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully
$

2. The listener appears to start okay, however when you perform the command lsnrctl status you receive an error message.

$ lsnrctl status

LSNRCTL for HPUX: Version 10.2.0.4.0 – Production on 23-MAR-2010 14:23:26

Copyright (c) 1991, 2007, Oracle. All rights reserved.

Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.110)(PORT=1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
HPUX Error: 239: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
HPUX Error: 239: Connection refused
$

3. Checking the OS layer and you see that no listener is currently running, with the command ps –ef.

$ ps -ef |grep tns
ORCL 21362 8423 0 14:24:15 pts/4 0:00 grep tns
$

4. SOLUTION: Add parameter SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF to listener.ora file.

5. Start the listener with the command lsnrctl start.

$ lsnrctl start

LSNRCTL for HPUX: Version 10.2.0.4.0 – Production on 23-MAR-2010 14:25:03

Copyright (c) 1991, 2007, Oracle. All rights reserved.

Starting /u01/oracle/bin/tnslsnr: please wait…

TNSLSNR for HPUX: Version 10.2.0.4.0 – Production
System parameter file is /u01/oracle/network/admin/listener.ora
Log messages written to /u01/oracle/network/admin/listener.log
Listening on:
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.110)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))

Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.110)(PORT=1521)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for HPUX: Version 10.2.0.4.0 – Production
Start Date 23-MAR-2010 14:25:05
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/oracle/network/admin/listener.ora
Listener Log File /u01/oracle/network/admin/listener.log
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.110)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
Services Summary…
Service “PLSExtProc” has 1 instance(s).
Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service…
Service “orcl” has 1 instance(s).
Instance “orcl”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully

6. Check the status of listener with the command lsnrctl status.

$ lsnrctl status

LSNRCTL for HPUX: Version 10.2.0.4.0 – Production on 23-MAR-2010 14:26:22

Copyright (c) 1991, 2007, Oracle. All rights reserved.

Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.110)(PORT=1521)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for HPUX: Version 10.2.0.4.0 – Production
Start Date 23-MAR-2010 14:26:07
Uptime 0 days 0 hr. 0 min. 15 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/oracle/network/admin/listener.ora
Listener Log File /u01/oracle/network/admin/listener.log
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.110)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
Services Summary…
Service “PLSExtProc” has 1 instance(s).
Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service…
Service “orcl” has 1 instance(s).
Instance “orcl”, status UNKNOWN, has 1 handler(s) for this service…
Service “testdb” has 1 instance(s).
Instance “testdb”, status READY, has 1 handler(s) for this service…
Service “testdbXDB” has 1 instance(s).
Instance “testdb”, status READY, has 1 handler(s) for this service…
Service “testdb_XPT” has 1 instance(s).
Instance “testdb”, status READY, has 1 handler(s) for this service…
The command completed successfully

7. Check at the OS layer to see if the listener is running with the command ps –ef.

$ ps -ef|grep tns
ORCL 21440 1 0 14:26:07 ? 0:00 /u01/oracle/bin/tnslsnr LISTENER
-inherit
ORCL 21835 21821 0 14:27:28 pts/4 0:00 grep tns
$

And the problem with listener is resolved.

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

11g, hp-ux, itanium, linux, Oracle, Oracle 10g, oracle 9i, unix

Oracle – Determining the OS layer your database is residing on.

Leave a comment

I have experienced situations where the only connection I had to an Oracle database was SQL*PLUS with no OS layer user account access. Thus, I was not sure of the actual OS my database was installed on. Knowing the OS your database resides on is a key factor in successfully administrating of any RDBMS. This article covers the determination of the operating system your Oracle database uses, through SQL*PLUS. This procedure will work regardless of OS.

1. Logon to your database server with SYSDBA privileges from your remote client.

C:\>sqlplus ljcatt/password5@orcl as sysdba

SQL*Plus: Release 10.2.0.1.0 – Production on Sat Feb 13 10:02:36 2010

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. Execute the following SQL statement: select platform_id, platform_name, edition from DBA_REGISTRY_DATABASE;

SQL> column platform_name format a15
SQL> select platform_id, platform_name, edition from DBA_REGISTRY_DATABASE;

PLATFORM_ID PLATFORM_NAME EDITION
———– ————— ——————————
3 HP-UX (64-bit)

SQL>

That completes determination of OS layer type from within the Oracle database.

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

hp-ux, itanium, linux, Oracle, Oracle 10g, oracle 9i

ORACLE SID not defined in the OS layer:

Leave a comment

This article covers the error which will occur in a Linux or UNIX environment if the OS parameter ORACLE_SID is not properly set and how to resolve this error.

1. With the ORACLE_SID OS parameter set to null, attempt to start SQLPLUS as sysdba. You will receive the following error – ORA-12162.

mylinux:> sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Wed Jan 6 14:55:56 2010

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

ERROR:
ORA-12162: TNS:net service name is incorrectly specified

Enter user-name:

2. In the OS layer define your database sid with the OS parameter ORACLE_SID.

mylinux:> export ORACLE_SID=orcl
mylinux:>

3. Attempt to start SQLPLUS as sysdba.

mylinux:> sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Wed Jan 6 14:57:54 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>

4. Oracle SQLPLUS starts normally after the ORACLE_SID parameter is set at the OS layer correctly.

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

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

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, hp-ux, itanium, linux, Oracle, Oracle 10g, oracle 9i, script, security, SQL, sqlplus, unix

Oracle – Use of UTL_FILE to read external OS file into Oracle RDBMS.

Leave a comment

Oracle – Use of UTL_FILE to read external OS file into Oracle RDBMS.

Oracle provides the procedure UTL_FILE to allow for the reading of external OS layer files from within the Oracle RDBMS. This article will give an example of how to read the contents of an external OS file and display it in the Oracle database.

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

2. Create a file called read.txt with the following text.

This is line one.

3. Logon to SQL*PLUS with an account which has DBA privileges. In this example we are using the account LJCATT.

mylinux:> sqlplus ljcatt

SQL*Plus: Release 10.2.0.4.0 – Production on Thu Jan 29 23:28:23 2009

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

Enter password:

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. Create the procedure read_file as detailed below.

create or replace procedure read_file
is

v_record varchar2(50);
v_file varchar2(30) := ‘read.txt’;
v_dir varchar2(512) := ‘/home/lufsora’;
v_write utl_file.file_type;
begin

v_write:=utl_file.fopen(v_dir, v_file, ‘r’, 2000);

utl_file.get_line(v_write,v_record, v_record);

dbms_output.put_line(v_record);

utl_file.fclose(v_write);

end read_file;
/

5. Execute the procedure read_file with the command execute and the test within the OS file will be display.

SQL> execute read_file;
This is line one.

PL/SQL procedure successfully completed.

SQL>

This completes the use of UTL_FILE to read OS level files into your Oracle RDBMS.

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