Category Archives: 11g

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

Oracle Installation – Add swap space in UNIX or Linux environment

Leave a comment

The typical Oracle installation will require more swap space on a system, due to prerequisite checks performed by the installer, than is typical available. The following procedure shows how to create additional swap in Unix or Linux.

1. Create a file on /u01 of 12gb with the dd command. NOTE: Repeat on each RAC node.

dd if=/dev/zero of=/u01/tempswap bs=1k count=12000000

Example:

[root@mylinux1 ~]# dd if=/dev/zero of=/u01/tempswap bs=1k count=12000000
12000000+0 records in
12000000+0 records out
12384000000 bytes (12 GB) copied, 64.2111 seconds, 255 MB/s
[root@mylinux1 ~]#

2. Then format your new file with the mke2fs command. NOTE: Repeat on each RAC node.

/sbin/mke2fs /u01/tempswap

Example:

[root@mylinux1 ~]# /sbin/mke2fs /u01/tempswap
mke2fs 1.39 (29-May-2006)
/u01/tempswap is not a block special device.
Proceed anyway? (y,n) y
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
2003424 inodes, 4000000 blocks
200000 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=4097835008
123 block groups
32768 blocks per group, 32768 fragments per group
12288 inodes per group
Superblock backups stored on blocks:
32768, 98304, 123840, 229376, 294912, 819200, 884736, 1205632, 2654208

Writing inode tables: done
Writing superblocks and filesystem accounting information: done

This filesystem will be automatically checked every 29 mounts or
180 days, whichever comes first. Use tune2fs -c or -i to override.
[root@mylinux1 ~]#

3. Make the additional swap space with the mkswap command. NOTE: Repeat on each RAC node.

/sbin/mkswap /u01/tempswap

Example:

[root@mylinux1 ~]# /sbin/mkswap /u01/tempswap
Setting up swapspace version 1, size = 12383995 kB
[root@mylinux1 ~]#

4. Make the swap space available to the OS with the command swapon. NOTE: This will have to be executed after every reboot. Repeat on each RAC node.

/sbin/swapon /u01/tempswap

Example:

[root@mylinux1 ~]# /sbin/swapon /u01/tempswap
[root@mylinux1 ~]#

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

11g, installer, linux, OPatch, unix

Oracle error OUI-10058

Leave a comment

The OUI (Oracle Universal Installer) relies on an Oracle inventory to recognize the Oracle software which already exists on the system. Multiple files are read during the OUI startup, the first of which is /etc/oraInst.loc. The oraInst.loc file points to the location of the Oracle inventor directory, which could be anywhere. In order to get an inventory of the current system, OUI must have access to this file. The error OUI-10058 is an indication that OUI does not have access to this file. This article covers the typical error and resolution to this problem

Typical error:

OUI-10058 The OUI Inventory on this system does not exist or is not writable.
1. The /opt/oracle/oraInventory/orainstall…..
or
2. You are not a member of the specified installer group.


Solution: If you executed all previous root.sh script as instructed, the most like cause is permission.

1. Logon to the system as root.
2. Change directory to /etc
3. Check the file permissions with the command ll

[root@mylinux /]# ll *ora*

4. If the file permission is not set to 644, change the permissions with the command chmod

[root@mylinux /]# chmod 644 /etc/oraInst.loc.

5. This completes the resolution of OUI-10058, due to file permissions of /etc/oraInst.loc.

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

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

Oracle Error ORA-25153

Leave a comment

Oracle Error ORA-25153 is a regular occurrence during the modification of location or recreation of an Oracle database. It indicates that the administrator neglected to add datafiles to a temporary tablespace. This article provides a solution the error ORA-25153, if it occurs.

Typical Error message:

Errors in file /u01/app/oracle/product/10.2.0/db_1/admin/orcl/bdump/orcl_j001_25535.trc:
ORA-25153: Temporary Tablespace is Empty
Sun Sep 19 01:31:00 2010
Thread 1 advanced to log sequence 9 (LGWR switch)
Current log# 3 seq# 9 mem# 0: /u05/oradata/orcl/group_3.dbf

Solution:

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 Sep 19 08:08:39 2010

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

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

SQL>

3. Add a temp file to your default temporary tablespaces with the following syntax: alter tablespace add tempfile < 'directory and file name'> size ;

example:


SQL> alter tablespace temp add tempfile ‘/u05/oradata/orcl/temp01.dbf’ size 2048m;

Tablespace altered.

SQL>

4. This completes the resolving of Oracle error ORA-25153.

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

11g, installer, Oracle, windows, windows 7

Oracle installation of 10.2.0.4 for Windows 7 Error

Leave a comment

While I am not an OS expert, I can comment that Windows 7 has given us speed and reliability while eliminating the sluggish performance and over-bearing protection of Vista. In this series, I would like to discuss problems one may encounter while running Oracle products on a Windows 7 client and ways in which these problems can be over come. This article discusses the Error of incorrect operating system found while installing Oracle 10.2.0.4 on a Windows 7 machine and how to ignore this error.

1. After you have downloaded the 10204_vista_w2k8_x64_production_db software from Oracle website and attemtpted to install the software with the command setup, you will probably encounter the below error.

Starting Oracle Universal Installer…

Checking installer requirements…

Checking operating system version: must be 5.0, 5.1, 5.2 or 6.0 . Actual 6.1

Failed < <<< Exiting Oracle Universal Installer, log for this session can be found at C:\User s\Home\AppData\Local\Temp\OraInstall2010-08-03_11-57-13AM\installActions2010-08- 03_11-57-13AM.log Please press Enter to exit...

2. This type of error is typical for most software once a new operating system version is released. To bypass the error, first open a command prompt by entering: Start –> Run –> CMD and enter.

3. Navigate the to directory of your oracle installer.

C:\Users\Home>
C:\Users\Home>cd c:\software\10204_vista_w2k8_x64_production_db\database

c:\software\10204_vista_w2k8_x64_production_db\database>

4. Now execute the command: setup.exe -ignoresysprereqs

c:\software\10204_vista_w2k8_x64_production_db\database>setup.exe -ignoresysprereqs

5. If a warning screen appears, click Yes to install the software.

6. At the Welcome Screen, you can press Next to begin your installation.

That completes the installation of Oracle 10.2.0.4 RDBMS on a Window 7 machine.

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

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