Category Archives: Oracle 10g

linux, Oracle, Oracle 10g, RAC, unix

Manually initializing Oracle OCFS2 stack

Leave a comment

Oracle provides the file system OCFS2 to support Oracle RAC file storage. This file system provides for a locking mechanism which allows files to be accessed by multiple Oracle instances while avoiding corruption. The OSFS2 file system binaries must be started before any OCFS2 formatted mount points can be accessed. This article shows the error generated when the OCFS2 stack has not been started and how to resolve the problem.

General OS error:


[root@mylinux /]# mount /dev/mapper/MPATH10 /u02
mount.ocfs2: Unable to access cluster service while trying initialize cluster

Resolution:

1. Logon to your server as root.
2. Execute the OS layer command /etc/init.d/o2cb enable

[root@mylinux /]# /etc/init.d/o2cb enable
Loading filesystem “configfs”: OK
Mounting configfs filesystem at /sys/kernel/config: OK
Loading filesystem “ocfs2_dlmfs”: OK
Mounting ocfs2_dlmfs filesystem at /dlm: OK
[root@mylinux /]#

3. Execute the OS layer command /etc/init.d/o2cb start

[root@mylinux /]# /etc/init.d/o2cb start
Starting O2CB cluster ocfs2: OK
[root@mylinux /]#

4. Attempt to mount your ocfs2 storage device.

[root@mylinux /]# mount /dev/mapper/MPATH10 /u02
[root@mylinux /]#

5. This completes restarting OCFS2 binaries.

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

11g, listener, network, OPatch, Oracle, Oracle 10g, oracle 9i

Enable ADMIN Tracing on Oracle Listener:

Leave a comment

Enable ADMIN Tracing on Oracle Listener:

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 3-Jul-2010 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 Jul 3 15:25 listener_orcl.trc
-rw-r—– 1 oracle dba 43347 Jul 3 15:40 user_access_trace.trc
-rw-r—– 1 oracle dba 34150 Jul 3 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 Sat Jul 3 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 Jul 3 15:25 listener_orcl.trc
-rw-r—– 1 oracle dba 43347 Jul 3 15:40 user_access_trace.trc
-rw-r—– 1 oracle dba 119638 Jul 3 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

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

Oracle UTL_RECOMP package and the RECOMP_SERIAL subprogram

Leave a comment

During the normal administration and operation of an Oracle database system, programming units within the database can become invalid for various reasons. To resolve this issue, the Oracle RDBMS provides the package UTL_RECOMP to recompile objects which are currently in an invalid status. The UTL_RECOMP package provides the administrator with 3 subprogram units: PARALLEL_SLAVE, RECOMP_PARALLEL, and RECOMP_SERIAL. This article covers the user of package and subprogram unit UTL_RECOMP.RECOMP_SERIAL and was written against an Oracle 10.2.0.4 RDBMS on RHEL.

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

2. Connect to SQLPLUS as the sysdba user.

mylinux:> sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Jul 3 10:24:46 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. The RECOMP_SERIAL subprogram unit recompiles all objects or objects within a schema in an ordered fashion. This means that any objects which are depended on by other objects are compiled first.

4. To compile all objects within the current database execute the command: execute utl_recomp.recomp_serial();

SQL> execute utl_recomp.recomp_serial();

PL/SQL procedure successfully completed.

SQL>

5. To compile only the objects in a single schema, specify the schema name: execute utl_recomp.recomp_serial(‘SCOTT’);

SQL> execute utl_recomp.recomp_serial('SCOTT');

PL/SQL procedure successfully completed.

SQL>

This completes the recompilation of objects using the package UTL_RECOMP with subprogram unit RECOMP_SERIAL.

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

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

Oracle UTL_RECOMP package and the RECOMP_PARALLEL subprogram

Leave a comment

During the normal administration and operation of an Oracle database system, programming units within the database can become invalid for various reasons. To resolve this issue, the Oracle RDBMS provides the package UTL_RECOMP to recompile objects which are currently in an invalid status. The UTL_RECOMP package provides the administrator with 3 subprogram units: PARALLEL_SLAVE, RECOMP_PARALLEL, and RECOMP_SERIAL. This article covers the user of package and subprogram unit UTL_RECOMP.RECOMP_PARALLEL and was written against an Oracle 10.2.0.4 RDBMS on RHEL.

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

2. Connect to SQLPLUS as the sysdba user.

mylinux:> sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Jun 30 19:58:23 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. The RECOMP_PARALLEL subprogram unit recompiles all objects or objects within a schema using a degree of parallelism specified in the command or by the init parameter JOB_QUEUE_PROCESSES. This means that if you specified a degree of parallelism of 4, than Oracle would attempt to recompile 4 objects at a single time.

4. To compile all objects in the database with a parallelism of 2 use the command: execute utl_recomp.recomp_parallel(2);

SQL> execute utl_recomp.recomp_parallel(2);

PL/SQL procedure successfully completed.

SQL>

5. To compile all object in a particular schema with a parallelism of 2 use the command: execute utl_recomp.recomp_parallel(2, ‘SCOTT’);

SQL> execute utl_recomp.recomp_parallel(2, 'SCOTT');

PL/SQL procedure successfully completed.

SQL>

This completes the recompilation of objects using the package UTL_RECOMP with subprogram unit RECOMP_PARALLEL.

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

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

Oracle – Encoding and Decoding Oracle data with UTL_ENCODE Package

Leave a comment

Oracle RDBMS provides the ability to encode and decode data through the package UTL_ENCODE. This functionality can be extremely helpful in the transmission of data over open networks where plan text transmission would not be wanted. In this article we will review the use of encoding plain text and decoding the string produced.

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 Wed Jun 30 20:00:09 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 PL/SQL block to produce the encoded text string for: Test encode procedure.

set serveroutput on

declare
v_text varchar2(100):='Test encode procedure';
v_encoded_text varchar2(200);
v_ct number;
begin

v_encoded_text:=utl_encode.text_encode(v_text,'WE8ISO8859P1',
UTL_ENCODE.BASE64);
dbms_output.put_line(v_encoded_text);

end;
/

Output from SQL*PLUS

SQL> declare
2
3 v_text varchar2(100):='Test encode procedure';
4 v_encoded_text varchar2(200);
5 v_ct number;
6
7 begin
8
9 v_encoded_text:=utl_encode.text_encode(v_text,'WE8ISO8859P1',
UTL_ENCODE.BASE64);
10
11 dbms_output.put_line(v_encoded_text);
12
13 end;
14 /
VGVzdCBlbmNvZGUgcHJvY2VkdXJl

PL/SQL procedure successfully completed.

SQL>

4. The PL/SQL block executed generated the encoded version of ‘Test encode procedure’ as VGVzdCBlbmNvZGUgcHJvY2VkdXJl. Now we will decode the string with the text decode function.

set serveroutput on

declare

v_text varchar2(100):='VGVzdCBlbmNvZGUgcHJvY2VkdXJl';
v_encoded_text varchar2(200);
v_ct number;

begin

v_encoded_text:=utl_encode.text_decode(v_text,'WE8ISO8859P1',
UTL_ENCODE.BASE64);

dbms_output.put_line(v_encoded_text);

end;
/

Output from SQL*PLUS:

SQL>
SQL> declare
2
3 v_text varchar2(100):='VGVzdCBlbmNvZGUgcHJvY2VkdXJl';
4 v_encoded_text varchar2(200);
5 v_ct number;
6
7 begin
8
9 v_encoded_text:=utl_encode.text_decode(v_text,'WE8ISO8859P1',
UTL_ENCODE.BASE64);
10
11 dbms_output.put_line(v_encoded_text);
12
13 end;
14 /
Test encode procedure

PL/SQL procedure successfully completed.

SQL>

The decode function displays the original text presented to the encode function.

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

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

Oracle NETCA – Network Configuration assistant.

Leave a comment

Oracle provides several Configuration Assistant tools to facility administrative duties required by an Oracle DBA or Developer. The Oracle’s NETCA (Network Configuration Assistant) is the GUI tool used to configure the network components of your Oracle RDBMS. In this article we will review the startup of the Network Configuration Assistant within a RHEL environment.

1. Logon to your Oracle Database server as the Oracle software owner.
2. Change directories to your $ORACLE_HOME/bin.

mylinux:> cd $ORACLE_HOME/bin
mylinux:>

3. Export your display to your local machine if you are not logon to the server itself.

mylinux:> export DISPLAY=192.168.0.110:0.0
mylinux:>

4. You will require a terminal emulation program executing on your local machine to produce the display from your database server. Any terminal display software will do, however I would suggest VNC which can be downloaded for free at the URL: www.realvnc.com

5. Execute the dbca shell script to startup the database configuration assistant.


mylinux:> ./netca

6. The Network Configuration Assistant is self explanatory from here on. It allows the ability to configure, add, delete and modify you various Oracle networking components.

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

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

Oracle DBCA – Database Configuration assistant.

Leave a comment

Oracle provides several Configuration Assistant tools to facility administrative duties required by an Oracle DBA or Developer. The Oracle’s DBCA (Database Configuration Assistant) is the GUI tool used to create starter databases within the Oracle RDBMS. The DBCA utility was introduced in Oracle 8i and provides the user a GUI to eliminate the need for manual configuration of database creation scripts. In this article we will review the startup of the Database Configuration Assistant within a RHEL environment.

1. Logon to your Oracle Database server as the Oracle software owner.
2. Change directories to your $ORACLE_HOME/bin.

mylinux:> cd $ORACLE_HOME/bin
mylinux:>

3. Export your display to your local machine if you are not logon to the server itself.

mylinux:> export DISPLAY=192.168.0.110:0.0
mylinux:>

4. You will require a terminal emulation program executing on your local machine to produce the display from your database server. Any terminal display software will do, however I would suggest VNC which can be downloaded for free at the URL: www.realvnc.com

5. Execute the dbca shell script to startup the database configuration assistant.

mylinux:> ./dbca

6. The Welcome screen will display and the wizard from here on is pretty self explanatory. The DBCA utility provides you the ability to Create Databases, Configure Database Options, Delete Databases, Manage Database Templates, and Configure ASM instances for storage.

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

11g, Oracle, Oracle 10g

Oracle – UTL_MATCH.EDIT_DISTANCE_SIMILARITY string comparison

Leave a comment

Oracle provides the procedure UTL_MATCH to compare the difference between to two sets of strings. In this article we will examine the function EDIT_DISTANCE_SIMILARITY which returns the percentage of matching strings: 0 meaning no similarity and 100 meaning complete similarity.

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 Tues May 25 19:32:21 ge2010

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. Comparing the same strings: ‘The First Dog’ and ‘The First Dog’

select utl_match.edit_distance_SIMILARITY(‘The First Dog’,’The First Dog’) from dual;

SQL*PLUS Output:

SQL> select utl_match.edit_distance_SIMILARITY(‘The First Dog’,’The First Dog’) from dual;

UTL_MATCH.EDIT_DISTANCE_SIMILARITY(‘THEFIRSTDOG’,’THEFIRSTDOG’)
—————————————————————
100

SQL>

The stings are a 100% match.

4. Comparing strings with no simularity: ‘The First Dog’,’1234567890123′

select utl_match.edit_distance_SIMILARITY(‘The First Dog’,’1234567890123′) from dual;

SQL*PLUS Output:

SQL> select utl_match.edit_distance_SIMILARITY(‘The First Dog’,’1234567890123′) from dual;

UTL_MATCH.EDIT_DISTANCE_SIMILARITY(‘THEFIRSTDOG’,’1234567890123′)
—————————————————————–
0

SQL>

The strings are a 0% match.

5. Comparing strings of varying case: ‘The First Dog’,’tHE fIRST dOG’

select utl_match.edit_distance_SIMILARITY(‘The First Dog’,’tHE fIRST dOG’) from dual;

SQL*PLUS Output:

SQL> select utl_match.edit_distance_SIMILARITY(‘The First Dog’,’tHE fIRST dOG’) from dual;

UTL_MATCH.EDIT_DISTANCE_SIMILARITY(‘The First Dog ‘, ‘tHE fIRST dOG’)
—————————————————————-
16

The strings have a 16% match, due to function being case sensitive.

6. Comparing strings with an off-set: ‘The First Dog’ and ‘-The First Dog’

select utl_match.edit_distance_SIMILARITY(‘The First Dog’,’-The First Dog’) from dual;

SQL*PLUS Output:

SQL> select utl_match.edit_distance_SIMILARITY(‘The First Dog’,’-The First Dog’)
from dual;

UTL_MATCH.EDIT_DISTANCE_SIMILARITY(‘THEFIRSTDOG’,’-THEFIRSTDOG’)
—————————————————————-
93

SQL>

The strings have a 93% match due to offsetting having no affect on comparison operation.

This completes the use of EDIT_DISTANCE function for the Oracle RDBMS procedure UTL_MATCH.

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

Oracle, Oracle 10g

Oracle – UTL_MATCH.EDIT_DISTANCE string comparison

Leave a comment

Oracle – UTL_MATCH.EDIT_DISTANCE

Oracle provides the procedure UTL_MATCH to compare the difference between to two sets of strings. In this article we will examine the function EDIT_DISTANCE which returns the number of changes required in a string comparison to make the strings identical.

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 Mon May 24 21:41:18 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. Comparing the same strings: ‘The First Dog’ and ‘The First Dog’

select utl_match.edit_distance(‘The First Dog’,’The First Dog’) from dual;

SQL*PLUS Output:

SQL> select utl_match.edit_distance(‘The First Dog’,’The First Dog’) from dual;

UTL_MATCH.EDIT_DISTANCE(‘THEFIRSTDOG’,’THEFIRSTDOG’)
—————————————————-
0

SQL>

The comparison returns a zero meaning no changes required to make the two strings match.

4. Comparing strings with no simularity: ‘The First Dog’ and ‘1234567890123’

select utl_match.edit_distance(‘The First Dog’,’1234567890123′) from dual;

SQL*PLUS Output:

SQL> select utl_match.edit_distance(‘The First Dog’,’1234567890123′) from dual;

UTL_MATCH.EDIT_DISTANCE(‘THEFIRSTDOG’,’1234567890123′)
——————————————————
13

SQL>

It would take 13 changes to make the strings match.

5. Comparing strings of variaring case: ‘The First Dog’ and ‘tHE fIRST dOG’

select utl_match.edit_distance(‘The First Dog’,’tHE fIRST dOG’) from dual;

SQL*PLUS Output:

SQL> select utl_match.edit_distance(‘The First Dog’,’tHE fIRST dOG’) from dual;

UTL_MATCH.EDIT_DISTANCE(‘THEFIRSTDOG’,’THEFIRSTDOG’)
—————————————————-
11

SQL>

The case of the charactors is critical, thus 11 changes are required to make the strings match.

6. Comparing strings with an off-set: ‘The First Dog’ and ‘-The First Dog’

select utl_match.edit_distance(‘The First Dog’,’Off Set Text The First Dog’) from dual;

SQL*PLUS Output:

SQL> select utl_match.edit_distance(‘The First Dog’,’Off Set Text The First Dog’) from dual;

UTL_MATCH.EDIT_DISTANCE(‘THEFIRSTDOG’,’OFFSETTEXTTHEFIRSTDOG’)
————————————————————–
13

SQL>

Off setting the text will still result in matches, thus in the example above, it would only take 13 changes to make the strings match as oppose to 26 changes.

This completes the use of EDIT_DISTANCE function for the Oracle RDBMS procedure UTL_MATCH.

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