Monthly Archives: April 2010

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

Oracle – SQL*PLUS does not show the SQL being executed from a script file.

Leave a comment

Oracle SQL*PLUS utility provides several options to customize the display of information from your database session. In this article we will review how to display the DML and DDL statements which are stored in an OS layer file upon execution. By default SQL*PLUS will not display the DML or DDL statement from a script file.

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

2. Create a file called test.sql with the following SQL.

select * from v$instance;

3. Logon to SQLPLUS with SYSDBA privileges.

mylinux:> sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Fri Apr 23 22:00:09 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. Execute the SQL file test.sql with the SQL*PLUS command: @./test.sql

SQL> @./test.sql

INSTANCE_NUMBER INSTANCE_NAME
————— —————-
HOST_NAME
—————————————————————-
VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
—————– ——— ———— — ———- ——- —————
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
———- — —————– —————— ——— —
1 orcl
mylinux
10.2.0.4.0 23-APR-10 OPEN NO 1 STOPPED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO

SQL>

NOTE: The SQL contained within the file test.sql is not displayed, thus the end user executing such a file may not be aware of the output they are looking at.

5. To remedy this situation we will have to setup our SQL*PLUS environment to display the commands within our file. To do this we will have to turn two options on: ECHO and FEEDBACK.

SQL> set echo on
SQL> set feedback on
SQL>

6. Now re-execute the file test.sql with the SQL*PLUS command: @./test.sql.

SQL> @./test.sql
SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
————— —————-
HOST_NAME
—————————————————————-
VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
—————– ——— ———— — ———- ——- —————
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
———- — —————– —————— ——— —
1 orcl
mylinux
10.2.0.4.0 23-APR-10 OPEN NO 1 STOPPED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO

1 row selected.

SQL>
SQL>

As show above, SQL*PLUS is now displaying the SQL contained within our file, before the output. This will allow the end user to know exactly how the output of our script file was produced, thus turning our script into more usefully information.

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

11g, Oracle, Oracle 10g, oracle 9i, script, SQL, sqlplus

Oracle – Viewing errors generated when writing a PL/SQL program unit.

Leave a comment

When you create a PL/SQL block of code within Oracle in the form of a procedure, function, or package and a compilation error occurs; Oracle will not immediately give you the error details. This article details the procedure to view errors generated by a PL/SQL block within SQL*PLUS.

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

2. Create a file called create_file.sql with the following SQL.

create or replace procedure test_file
is

v_record varchar2(50) := ‘Testing file creation’;
v_file varchar2(30) := ‘test_file.txt’;
v_dir varchar2(512) := ‘/home/lcatt’;
v_write utl_file.file_type;
begin

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

utl_file.put_line(v_write, v_record);

utl_file.fclose(v_write);

end test_file;
/

3. Logon to SQLPLUS with SYSDBA privileges.

mylinux:> sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Thu Apr 22 19:00:09 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. Set FEEDBACK and ECHO to on and execute the file create_file.sql.

SQL> set echo on
SQL> set feedback on
SQL> @./create_file.sql
SQL> create or replace procedure test_file
2 is
3
4 v_record varchar2(50) := ‘Testing file creation’;
5 v_file varchar2(30) := ‘test_file.txt’;
6 v_dir varchar2(512) := ‘/home/lcatt’;
7
8 begin
9
10 v_write:=utl_file.fopen(v_dir, v_file, ‘w’, 2000);
11
12 utl_file.put_line(v_write, v_record);
13
14 utl_file.fclose(v_write);
15
16 end test_file;
17 /

Warning: Procedure created with compilation errors.

SQL>

5. The procedure was created with compilation errors, however it does not show you the errors. To see the errors generated in your procedure creation statement, use the SQL*PLUS command: show errors

SQL> show errors
Errors for PROCEDURE TEST_FILE:

LINE/COL ERROR
——– —————————————————————–
10/1 PL/SQL: Statement ignored
10/1 PLS-00201: identifier ‘V_WRITE’ must be declared
12/1 PL/SQL: Statement ignored
12/19 PLS-00201: identifier ‘V_WRITE’ must be declared
14/1 PL/SQL: Statement ignored
14/17 PLS-00201: identifier ‘V_WRITE’ must be declared
SQL>

6. Now you can see that the procedure creation statement is missing the definition for the variable V_WRITE. To correct this error replace the contents of your create_file.sql file with the following text, where the variable v_write is defined as utl_file.file_type.

create or replace procedure test_file
is

v_record varchar2(50) := ‘Testing file creation’;
v_file varchar2(30) := ‘test_file.txt’;
v_dir varchar2(512) := ‘/home/lcatt’;
v_write utl_file.file_type;
begin

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

utl_file.put_line(v_write, v_record);

utl_file.fclose(v_write);

end test_file;
/

7. Re-enter SQL*PLUS, set ECHO and FEEDBACK to on, and execute the file create_file.sql.

Mylinux:/home/lcatt:>sqlplus ljcatt

SQL*Plus: Release 10.2.0.4.0 – Production on Thu Apr 22 19:43:17 2010

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> set feedback on
SQL> set echo on
SQL>@./create_file.sql
SQL> create or replace procedure test_file
2 is
3
4 v_record varchar2(50) := ‘Testing file creation’;
5 v_file varchar2(30) := ‘test_file.txt’;
6 v_dir varchar2(512) := ‘/home/lcatt’;
7 v_write utl_file.file_type;
8 begin
9
10 v_write:=utl_file.fopen(v_dir, v_file, ‘w’, 2000);
11
12 utl_file.put_line(v_write, v_record);
13
14 utl_file.fclose(v_write);
15
16 end test_file;
17 /

Procedure created.

SQL>

The procedure now compiles without error, through the use of the SQL*PLUS command ‘show errors’ which allowed us to see the detailed error message provided by the Oracle RDBMS.

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 separate ORACLE_HOME:

Leave a comment

In Oracle production database environments, it is desirable to use one Oracle installation for each database which resides on a server. The use of a separate Oracle installation for each database reduces the possibility of corruption of one ORACLE_HOME affecting all of the databases on a single server and allows for maintenance to be performed on one RDBMS without affecting the operations of the others. This is easily accommodated in a LINUX or UNIX environments through the proper setting of your .profile or .bash_profile file. In this article we will review the configuration of your profile to use multiple ORACLE_HOMEs on a single server.

1. To accomplish the setup of our profile for multiple database with separate ORACLE_HOMEs, we will have to create a .profile file 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 to determine 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, 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 section defines all variables for a specific database, as seen below:

##Beginning of local variable definitions
if [[ $database = “1” ]]
then
export NLS_LANG=AMERICAN_AMERICA.US7ASCII
export ORACLE_BASE=/opt/app/oracle/product
export ORACLE_HOME=/opt/app/oracle/product/10.2/orcl
export ORACLE_SID=orcl
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/orcl
cd
elif [[ $database = “2” ]]
then
export NLS_LANG=AMERICAN_AMERICA.US7ASCII
export ORACLE_BASE=/opt/app/oracle/product
export ORACLE_HOME=/opt/app/oracle/product/10.2/orcl2
export ORACLE_SID=orcl2
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/orcl2
cd
elif [[ $database = “3” ]]
then
export NLS_LANG=AMERICAN_AMERICA.US7ASCII
export ORACLE_BASE=/opt/app/oracle/product
export ORACLE_HOME=/opt/app/oracle/product/10.2/orcl3
export ORACLE_SID=orcl3
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/orcl
cd

else
echo “Invalid options ”
fi

## Ending of local variable definitions

4. The global section defines all variables for any database regardless of the specific ORACLE_HOME, as seen below:

## Beginning of global variable definitions

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'”

export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/obackup/bin
export ORACLE_TERM=vt220

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, we will place the following text in the file /home/lcatt/.profile for 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 NLS_LANG=AMERICAN_AMERICA.US7ASCII
export ORACLE_BASE=/opt/app/oracle/product
export ORACLE_HOME=/opt/app/oracle/product/10.2/orcl
export ORACLE_SID=orcl
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/orcl
cd
elif [[ $database = “2” ]]
then
export NLS_LANG=AMERICAN_AMERICA.US7ASCII
export ORACLE_BASE=/opt/app/oracle/product
export ORACLE_HOME=/opt/app/oracle/product/10.2/orcl2
export ORACLE_SID=orcl2
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/orcl2
cd
elif [[ $database = “3” ]]
then
export NLS_LANG=AMERICAN_AMERICA.US7ASCII
export ORACLE_BASE=/opt/app/oracle/product
export ORACLE_HOME=/opt/app/oracle/product/10.2/orcl3
export ORACLE_SID=orcl3
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/orcl
cd

else
echo “Invalid options ”
fi

## Ending of local variable definitions

## Beginning of global variable definitions

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'”

export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/obackup/bin
export ORACLE_TERM=vt220

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. As seen below the request for information section is displayed and we are presented with the choice of 1, 2, or 3. Select 1 to setup 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’
orcl $

This completes setup of .profile for multiple databases using the separate ORACLE_HOMEs.

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

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

Multiple TNSNAMES alias for single database:

Leave a comment

Oracle provides for the ability to have multiple tnsnames aliases for connection to the same database instance in a standard TNSNAMES.ORA file. This is extremely useful when you are changing alias to a database without affecting connections to previously configured database alias listings. In this article we will discuss how to configure a TNSNAMES.ORA file to connect to an Oracle database instance through a new and old alias.

1. In this example we will look at the TNSNAMES listing for the alias ORCL, which we wish to change to an alias of MYDB without affecting any previously configured application or script connections to the old alias ORCL.

2. We open our currently configured TNSNAMES.ORA file and find the alias ORCL.

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = mobile_1-PC)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL)
)
)

3. Test this connection to ORCL through your tnsping utility and as shown below, the connection currently works.

C:\Users\mobile_1>tnsping ORCL

TNS Ping Utility for 64-bit Windows: Version 10.2.0.4.0 – Production on 26-MAY-2
010 18:19:53

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

Used parameter files:
C:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = mobile_1
-PC)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL)
))
OK (20 msec)

C:\Users\mobile_1>

4. Now edit and save the ORCL alias listing by replace “ORCL =” with “ORCL, MYDB =”, as shown below:

ORCL, MYDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = mobile_1-PC)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL)
)
)

5. Now test the connection to both aliases MYDB and ORCL, as show below:

C:\Users\mobile_1>tnsping mydb

TNS Ping Utility for 64-bit Windows: Version 10.2.0.4.0 – Production on 26-MAY-2
010 18:19:45

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

Used parameter files:
C:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = mobile_1
-PC)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL)
))
OK (10 msec)

C:\Users\mobile_1>tnsping ORCL

TNS Ping Utility for 64-bit Windows: Version 10.2.0.4.0 – Production on 26-MAY-2
010 18:19:53

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

Used parameter files:
C:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = mobile_1
-PC)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL)
))
OK (20 msec)

C:\Users\mobile_1>

This completes configuration of TNSNAMES.ORA file for database resolution with multiple aliases.

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 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, Oracle, Oracle 10g, oracle 9i

Changing the Date format of an Oracle database:

Leave a comment

The date format of the oracle database normally defaults to DD-MON-YYYY in American Language standards. However, there are times when you want to see the hours, minutes and even seconds in which a transaction occurred. There are to ways of performing this task in the Oracle database: convert the date field to a char with the to_char function, specifying the desired format or changing the default NLS_DATE_FORMAT of the system or session. In this procedure we will demonstrate changing the NLS_DATE_FORMAT of the session.

1. Logon to you oracle database as an administrator.

mylinux:> sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Wed Apr 14 10:18:48 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>

2. Perform a select on the V$INSTANCE view for startup_time to see the current default date format of your NLS_DATE_FORMAT parameter.

SQL> select startup_time from v$instance;

STARTUP_T
———
14-APR-10

SQL>

3. NOTE: The current format only shows the day-month-year of the instance startup.

4. Perform a select on the view NLS_SESSION_PARAMETERS and you can see that the format of NLS_DATE_FORMAT is DD-MON-RR.

SQL> select * from nls_session_parameters;

PARAMETER VALUE
—————————— —————————————-
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE

17 rows selected.

SQL>

5. Perform an alter session statement to change the default format to include hours, minutes, and seconds.

alter session set nls_date_format=’DD-MON-YYYY HH24:MI:SS’;

6. Again perform a select on the view NLS_SESSION_PARAMETERS.

SQL> select * from nls_session_parameters;

PARAMETER VALUE
—————————— —————————————-
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-YYYY HH24:MI:SS
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE

17 rows selected.

SQL>

7. Now select the startup_time from the view V$INSTANCE and you will see the hours, minutes, and seconds of the instance startup. All date variables will be displayed in this format for the rest of the current session.

SQL> select startup_time from v$instance;

STARTUP_TIME
——————–
14-APR-2010 07:27:18

SQL>

NOTE: Changing this parameter at the system level will change the display of dates for all sessions in the database.

This completes changing the default date format in an Oracle database.

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

OPatch, Oracle, Oracle 10g, oracle 9i

OPatch Oracle database still running: Code 73

Leave a comment

Oracle’s OPatch utility is used for applying interim CPU patches to maintain the security of your RDBMS. In this article we will cover the error which would be received when you attempt to apply an Oracle patch to a currently executing Oracle database.

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

2. Change directories to the location of your CPU patch and unzip the file.

unzip p9119226_10204_.zip

3. Change directories into your unzipped patch directory which is named with the CPU number you are applying.

cd 9119226
OPatch apply

conflicts against Oracle Home…
OPatch continues with these patches: 7155248 7155249 7155250 7155251
7155252 7155253 7155254 7197583 7375611 7375613 7375617 7609057 7609058
8309592 8309632 8309642 8568395 8568397 8568398 8568402 8568404 8568405
8836667 8836671 8836675 8836677 8836678 8836681 8836683 8836684 8836686
9119226 9173244 9173248 9173253

Do you want to proceed? [y|n]
y
User Responded with: Y

Running prerequisite checks…
Prerequisite check “CheckActiveFilesAndExecutables” failed.
The details are:

Following executables are active :
/u01/prism/oracle/product/10.2.0/db_1/bin/oracle
UtilSession failed: Prerequisite check “CheckActiveFilesAndExecutables” failed.

OPatch failed with error code 73
mylinux :>

4. As seen above the OPatch utility errored out with code 73 and references the Prerequisite check “CheckActiveFilesAndExecutables”. This error normally occurs when the Oracle database is still up and running, so we check it below with the command ps:

mylinux :> ps -ef|grep smon
cimsora 26981 1 0 02:12 ? 00:00:00 ora_smon_prism
cimsora 4326 11581 0 02:34 pts/1 00:00:00 grep smon

5. As seen above the database is in fact up, so logon as sysdba and shut it down.

mylinux :> sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Sun Feb 2802:38:36 2010

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

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

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit

6. Once the database is shutdown we can re-execute our OPatch install.

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