Category Archives: Oracle 10g

OPatch, Oracle, Oracle 10g, oracle 9i

OPatch using 64 bit linux CPU on a 32 bit linux Oracle home: Code 73

Leave a comment

Oracle’s OPatch utility is used for applying interim CPU patches to maintain the security of your RDBMS. When installing any oracle patch set it is important to know the bit count of your current Oracle binaries. In this article we will cover the error which would be received when you attempt to install a 64 bit patch on a 32 bit Oracle home

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 napply -skip_subset -skip_duplicate

4. You are under the assumption that you have a 64 bit Oracle home which is platform ID 226. However, you’re true Oracle home is 32 bit or platform ID 46. The following error message is the result.

OPatch2010-03-02_02-29-19AM.log:Platform IDs supported by patch are: 226 Patch (
8836667 ) is not applicable on current platform.
OPatch2010-03-02_02-29-19AM.log:Platform IDs supported by patch are: 226 Patch (
8836684 ) is not applicable on current platform.
ISM :> vi OPatch2010-03-02_02-29-19AM.log
Platform ID needed is : 46
Platform IDs supported by patch are: 226 Patch ( 9173253 ) is not applicable on
current platform.
Platform ID needed is : 46
Platform IDs supported by patch are: 226
SEVERE:OUI-67073:UtilSession failed: Prerequisite check
“CheckPatchApplicableOnCurrentPlatform” failed.
INFO:Finishing UtilSession at Sun Feb 28 02:33:02 EDT 2010
INFO:Stack Description: java.lang.RuntimeException: Prerequisite check
“CheckPatchApplicableOnCurrentPlatform” failed.
INFO:StackTrace: oracle.OPatch.OPatchSessionHelper.runApplyPrereqs(OPatchSession
Helper.java:4173)
INFO:StackTrace: oracle.OPatch.OPatchutil.NApply.process(NApply.java:2057)
INFO:StackTrace: oracle.OPatch.OPatchutil.OUSession.napply(OUSession.java:868)
INFO:StackTrace: sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
INFO:StackTrace:
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
INFO:StackTrace: sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMetho
dAccessorImpl.java:25)
INFO:StackTrace: java.lang.reflect.Method.invoke(Method.java:324)
INFO:StackTrace: oracle.OPatch.UtilSession.process(UtilSession.java:313)
INFO:StackTrace: oracle.OPatch.OPatchSession.main(OPatchSession.java:1993)
INFO:StackTrace: oracle.OPatch.OPatch.main(OPatch.java:630)

5. The solution is to logon to www.metalink.oracle.com and download the correct bit version for your platform.

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

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

OPatch platform id 46-linux not 59-hpux: Code 73

Leave a comment

Oracle’s OPatch utility is used for applying interim CPU patches to maintain the security of your RDBMS. Oracle Corporation uses a numbering system for its patch sets which do not change across differing OS layers. Though the patch number would be the same for differing operating systems, the actual binary files are specific. In this article we will cover the error which would be received when you attempt to apply a CPU for HPUX on a LINUX OS.

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 napply -skip_subset -skip_duplicate

4. You receive the following error.

Platform ID needed is : 46
Platform IDs supported by patch are: 59 Patch ( 9173253 ) is not applicable on
current platform.
Platform ID needed is : 46
Platform IDs supported by patch are: 59
UtilSession failed: Prerequisite check “CheckPatchApplicableOnCurrentPlatform”
failed.

OPatch failed with error code 73

5. The error produced specifies the current platform ID is 46 – LINUX, however you are attempting to apply a CPU for platform ID of 59 –HPUX.

6. The resolution is to download the correct patch from www.metalink.oracle.com for your specific OS.

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

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

OPatch utility error due to incorrect option: Code 14

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 if you pass an unknown variable to the OPatch utility. This error message will appear regardless of OS you are trying to apply.

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 napply -skip_subset -skip_duplicate

4. Execute OPatch napply with the wrong tag –skip_subnet instead of –skip_subset.

mylinux:> OPatch napply -skip_subnet -skip_duplicate
Invoking OPatch 11.2.0.1.2

Oracle Interim Patch Installer version 11.2.0.1.2
Copyright (c) 2010, Oracle Corporation. All rights reserved.

Syntax Error… Unrecognized Command or Option: failed to parse arguments
“unknown option ‘-skip_subnet'”
Please use the option ‘OPatch -help’ to get correct syntax

OPatch failed with error code 14
mylinux:>

5. Above the OPatch utility errors out with code 14, “UNKOWN OPTION”. Reviewing the tags used, you can see that skip_subset is misspelled. NOTE: This error will occur if a misspelling is present in the tag or you give the utility a tag which does not exist.

6. Re-execute the OPatch utility with correct tags and the utility will complete successfully.

mylinux:> OPatch napply -skip_subset -skip_duplicate
Invoking OPatch 11.2.0.1.2

Oracle Interim Patch Installer version 11.2.0.1.2
Copyright (c) 2010, Oracle Corporation. All rights reserved.

UTIL session

Patch : 9119226

Do you want to proceed? [y|n]

Response = y
OPatch Session completed without warnings.

OPatch completed without warnings.
mylinux:>

This completes the correction of OPatch utility error code 14.

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

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

Removal of Oracle auditing on specific privilege:

Leave a comment

Oracle provides the ability to audit your database activities on a multitude of level which provides the administrator the ability to find suspicious activity. In most cases the DBA knows which privilege and user in the database they suspect is causing a problem, thus they only wish to monitor that privilege. However, in a heavily used system, the auditing process can produce a large amount of data and should be discontinued once it is obsolete. This article covers the removal of audit definitions for a specific privilege on an Oracle RDBMS. This procedure will work on any OS.

NOTE: The initialization parameter AUDIT_TRAIL controls auditing at the entire database level and can be set to three definitions: 1. DB – audit trail in the database; 2. OS – audit trail on the OS; and 3. none – no auditing. In this procedure AUDIT_TRAIL must be set to DB or OS and the procedure does not shutdown auditing at the database level.

1. Auditing definition for user accounts are stored in views:
DBA_OBJ_AUDIT_OPTS, DBA_PRIV_AUDIT_OPTS, and DBA_STMT_AUDIT_OPTS.

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

3. Logon to SQLPLUS with sysdba privileges.

mylinux:>sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Sat Mar 6 10:22:512010

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. In this procedure we will audit the create table privilege by the user LJCATT, execute the following command to begin the auditing process for our specified object: audit create table by {schema.object_name};

SQL> audit create table by ljcatt;

Audit succeeded.

SQL>

5. Perform a select from the views DBA_PRIV_AUDIT_OPTS to see the audit policies defined by the command in step 4 and as you can see the privilege ‘create table ‘ by LJCATT is setup for auditing.

SQL> select user_name, privilege from DBA_PRIV_AUDIT_OPTS;

USER_NAME PRIVILEGE
—————————— —————————————-
LJCATT CREATE TABLE

SQL>

6. Once you have completed your analysis, you want to remove this audit policy, execute the following PL/SQL block.

NOTE: This script will remove all privilege auditing. To only remove auditing on LJCATT privileges replace the line

for v_stmt in(select ‘noaudit ‘||privilege||’ by ‘||user_name as stmt from
sys.dba_obj_audit_opts)

with

for v_stmt in(select ‘noaudit ‘||privilege||’ by ‘||user_name as stmt from sys.dba_obj_audit_opts where USER_NAME=’LJCATT’)

———————————————————————–
Beginning removal of Oracle auditing definitions for a specific object PL/SQL
Block
———————————————————————–

set serveroutput on

declare

v_ct number;

begin

for v_stmt in(select ‘noaudit ‘||privilege||’ by ‘|| user_name as stmt from
sys.dba_priv_audit_opts)
loop
execute immediate(v_stmt.stmt);
end loop;

end;
/

———————————————————————–
End removal of Oracle auditing definitions for a specific object PL/SQL Block
———————————————————————–
———————————————————————–
OUPUT
———————————————————————–
SQL>
SQL> declare
2
3 v_ct number;
4
5 begin
6
7 for v_stmt in(select ‘noaudit ‘||privilege||’ by ‘|| user_name as stmt from
sys.dba_priv_audit_opts)
8 loop
9 execute immediate(v_stmt.stmt);
10 end loop;
11
12
13
14 end;
15 /

PL/SQL procedure successfully completed.

SQL>
———————————————————————–
End of OUPUT
———————————————————————–

7. Once completed, re-execute the following SQL to verify that the auditing definition have been removed from the system: select * from DBA_PRIV_AUDIT_OPTS;

SQL> select * from DBA_PRIV_AUDIT_OPTS;

no rows selected

SQL>

That completes removal of all Oracle auditing for a privilege in the RDBMS.

Larry J. Catt
oracle@allcompute.com
www.allcompute.com

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

Determining the total size of an Oracle Database.

Leave a comment

When using any RDBMS there exist a distinct difference between the actual size of the data and the physical size of the database. The physical size on disk is defined as the space required for the database to operate (also known as the “foot print”). This article will discuss the components which make up the physical size of the database and how to determine that size within Oracle. NOTE: This does not include determining of OS layer database objects (Control Files, Binaries, Log Files, and Trace files ).

Components which make-up the physical size of an Oracle database:
1. Data Files
2. Temporary Files
3. Redo Logs
4. Archive log Files

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 Thu Mar 04 21:19:51 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. Execute the following SQL to get the size of all Data files: select
sum(bytes)/1024/1024 from dba_data_files;

SQL> select sum(bytes)/1024/1024 from dba_data_files;

SUM(BYTES)/1024/1024
——————–
473824.297

SQL>

4. Execute the following SQL to get the size of all Temporary files: select sum(bytes)/1024/1024 from dba_temp_files;

SQL> select sum(bytes)/1024/1024 from dba_temp_files;

SUM(BYTES)/1024/1024
——————–
46082

SQL>

5. Execute the following SQL to get the size of all redo logs: select
sum(bytes)/1024/1024 from v$log;

SQL> select sum(bytes)/1024/1024 from v$log;

SUM(BYTES)/1024/1024
——————–
1200

SQL>

6. Execute the following SQL to get the size of all archive redo logs: select sum(block_size*blocks)/1024/1024 from v$archived_log;

SQL> select sum(block_size*blocks)/1024/1024 from v$archived_log;

SUM(BLOCK_SIZE*BLOCKS)/1024/1024
——————————–
136885.281

SQL>

7. This completes the physical size of an Oracle database.

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

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

Determining actual size of data stored in an Oracle RDBMS.

Leave a comment

When using any RDBMS there exist a distinct difference between the actual size of the data and the physical size of the database. In this article we will demonstrate how to get the actual size of the data stored in an Oracle RDBMS.

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 Mar 01 17:17: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>

3. Select the sum of all bytes allocated in the view DBA_SEGMENTS, returns the actual space being consumed on disk.

SQL> select sum(bytes)/1024/1024 from dba_segments;

SUM(BYTES)/1024/1024
——————–
316865.805

4. This completes the determining the actual size of data in an Oracle database.

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

audit, Oracle, Oracle 10g, oracle 9i

Removal of Oracle auditing on specific object:

Leave a comment

Oracle provides the ability to audit your database activities on a multitude of level which provides the administrator the ability to find suspicious activity. In most cases the DBA knows which object in the database they suspect is causing a problem, thus they only wish to monitor that object. However, in a heavily used system, the auditing process can produce a large amount of data and should be discontinued once it is obsolete. This article covers the removal of audit definitions for a specific object on an Oracle RDBMS. This procedure will work on any OS.

NOTE: The initialization parameter AUDIT_TRAIL controls auditing at the entire database level and can be set to three definitions: 1. DB – audit trail in the database; 2. OS – audit trail on the OS; and 3. none – no auditing. In this procedure AUDIT_TRAIL must be set to DB or OS and the procedure does not shutdown auditing at the database level.

1. Auditing definition for user accounts are stored in views:
DBA_OBJ_AUDIT_OPTS, DBA_PRIV_AUDIT_OPTS, and DBA_STMT_AUDIT_OPTS.

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

3. Logon to SQLPLUS with sysdba privileges.

mylinux:>sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Sat Feb 27 06:38:462010

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. In this procedure we will audit the table account LJCATT.TEST_AUDIT, execute the following command to begin the auditing process for our specified object: audit insert, update, delete on
{schema.object_name} by access;

SQL> audit insert, update, delete on ljcatt.test_audit by access;

Audit succeeded.

SQL>

5. Perform a select from the views DBA_OBJ_AUDIT_OPTS to see the audit policies defined by the command in step 4 and as you can see the object is setup for auditing.

SQL> select * from DBA_OBJ_AUDIT_OPTS;

OWNER OBJECT_NAME OBJECT_TYPE
—————————— —————————— —————–
ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE CRE REA WRI FBK
— — — — — — — — — — — — — — — — —
LJCATT TEST_AUDIT TABLE
-/- -/- -/- A/A -/- -/- A/A -/- -/- -/- A/A -/- -/- -/- -/- -/- -/-

SQL>

6. Once you have completed your analysis, you want to remove this audit policy, execute the following PL/SQL block.

NOTE: This script will remove all object auditing. To only remove auditing on LJCATT.TEST_AUDIT table replace the line

for v_stmt in(select ‘noaudit all on ‘ ||owner||’.’||object_name as stmt from
sys.dba_obj_audit_opts)

with

for v_stmt in(select ‘noaudit all on ‘ ||owner||’.’||object_name as stmt from
sys.dba_obj_audit_opts
where owner=’LJCATT’ and OBJECT_NAME=’TEST_AUDIT’)

———————————————————————–
Beginning removal of Oracle auditing definitions for a specific object PL/SQL Block
———————————————————————–

set serveroutput on

declare

v_ct number;

begin

for v_stmt in(select ‘noaudit all on ‘ ||owner||’.’||object_name as stmt from
sys.dba_obj_audit_opts)
loop
execute immediate(v_stmt.stmt);
end loop;

end;
/

———————————————————————–
End removal of Oracle auditing definitions for a specific object PL/SQL Block
———————————————————————–
———————————————————————–
OUPUT
———————————————————————–

SQL> declare
2
3 v_ct number;
4
5 begin
6
7 for v_stmt in(select ‘noaudit all on ‘ ||owner||’.’||object_name as stmt
from sys.dba_obj_audit
_opts)
8 loop
9 execute immediate(v_stmt.stmt);
10 end loop;
11
12
13
14 end;
15 /

PL/SQL procedure successfully completed.

SQL>
———————————————————————–
End of OUPUT
———————————————————————–

7. Once completed re-execute the following SQL to verify that the auditing definition have been removed from the system: select * from DBA_OBJ_AUDIT_OPTS;

SQL> select * from DBA_OBJ_AUDIT_OPTS;

no rows selected

SQL>

That completes removal of all Oracle auditing for a specific object in the RDBMS.

Larry J. Catt
oracle@allcompute.com
www.allcompute.com

Oracle, Oracle 10g, oracle 9i

Determine Oracle RDBMS component upgrade and modification history.

Leave a comment

The Oracle RDBMS provides a history of upgrade and modification to database components through the view DBA_REGISTRY_LOG. This information can be used by the DBA to determine times which installation, upgrades, and modifications have been made to various RDBMS components. This article covers the use of the view DBA_REGISTRY_LOG.

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 Wed Feb 24 20:51:08 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 * from DBA_REGISTRY_LOG;

SQL> column optime format a28
SQL> column namespace format a10
SQL> column comp_id format a10
SQL> column message format a10
SQL> set pagesize 100
SQL> select * from DBA_REGISTRY_LOG order by optime;
OPTIME NAMESPACE COMP_ID OPERATION MESSAGE
—————————- ———- ———- ———– ———-
18-JUL-09 03.13.00.026718 PM SERVER UPGRD_BGN
18-JUL-09 03.47.27.539499 PM SERVER CATPROC INVALID 10.2.0.4.0
18-JUL-09 03.48.35.621297 PM SERVER RDBMS INVALID 10.2.0.4.0
18-JUL-09 04.01.47.151968 PM SERVER JAVAVM VALID 10.2.0.4.0
18-JUL-09 04.04.58.654033 PM SERVER XML VALID 10.2.0.4.0
18-JUL-09 04.05.43.121515 PM SERVER CATJAVA VALID 10.2.0.4.0
18-JUL-09 04.07.27.818615 PM SERVER CONTEXT VALID 10.2.0.4.0
18-JUL-09 04.11.48.629069 PM SERVER XDB VALID 10.2.0.4.0
18-JUL-09 04.13.49.808540 PM SERVER OWM VALID 10.2.0.4.3
18-JUL-09 04.22.02.603685 PM SERVER ORDIM VALID 10.2.0.4.0
18-JUL-09 04.28.32.273345 PM SERVER SDO VALID 10.2.0.4.0
18-JUL-09 04.29.25.682363 PM SERVER WK INVALID 10.2.0.4.0
18-JUL-09 04.35.37.187381 PM SERVER UPGRD_END
18-JUL-09 05.45.01.775983 PM SERVER UTLRP_BGN
18-JUL-09 05.48.54.633250 PM SERVER UTLRP_END
18-JUL-09 07.06.22.748873 PM SERVER UTLRP_BGN
18-JUL-09 07.09.59.268882 PM SERVER UTLRP_END
19-JUL-09 01.16.55.416850 PM SERVER UTLRP_BGN
19-JUL-09 01.17.15.642776 PM SERVER UTLRP_END
13-AUG-09 12.56.24.513219 PM SERVER UTLRP_BGN
13-AUG-09 12.57.04.276009 PM SERVER UTLRP_END
08-SEP-09 01.27.01.558155 PM SERVER UTLRP_BGN
08-SEP-09 01.27.46.036417 PM SERVER UTLRP_END
08-SEP-09 01.30.25.121025 PM SERVER UTLRP_BGN
08-SEP-09 01.31.13.640322 PM SERVER UTLRP_END
08-SEP-09 01.37.39.576965 PM SERVER UTLRP_BGN
08-SEP-09 01.37.58.850834 PM SERVER UTLRP_END
08-SEP-09 01.43.30.395883 PM SERVER UTLRP_BGN
08-SEP-09 01.44.25.124050 PM SERVER UTLRP_END
08-SEP-09 01.50.01.872579 PM SERVER UTLRP_BGN
08-SEP-09 01.50.15.180842 PM SERVER UTLRP_END

31 rows selected.

SQL>

3. The information above can be used to determine dates of upgrades, installations, and recompilation of various ORACLE RDBMS components.

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

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

Securing Oracle Listener with a password:

Leave a comment

Normally, connection to an Oracle database is performed through the use of an Oracle LISTENER process which monitors a specific machine and port for request to connect to a one or more database instances. The LISTENER process is control by a utility named LSNRCTL which is located under $ORACLE_HOME/bin. The listener provides the main connection access to most Oracle database systems, thus if it is tempered with, it could prevent use of your database even though the RDBMS is up and running fine. In this article we will review password protecting your oracle LISTENER from unauthorized shutdown. This article was written using LINUX but will work just as well on any OS.

1. Logon to your Oracle database server as the Oracle software owner, switch directories to your $ORACLE_HOME/network/admin and view the file listener.ora with your chose of editor.

mylinux:> cd $ORACLE_HOME/network/admin
mylinux:> cat listener.ora
# LISTENER.ORA Network Configuration File:
/opt/app/oracle/10.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.110)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
)

SID_LIST_LISTENER_ORCL =
(SID_LIST =
(SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME = /opt/app/oracle/10.2.0)
)

(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /opt/app/oracle/10.2.0)
(PROGRAM = extproc)
)
)

mylinux:>

NOTE: There exists no tag for PASSWORDS_{listener_name} = {new_password} which has not been established yet.

2. Exit the listener.ora file and startup the lsnrctl utility.

mylinux:> lsnrctl

LSNRCTL for LINUX: Version 10.2.0.4.0 – Production on 21-FEB-2010 16:30:52

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 {listener_name}

LSNRCTL> set current_listener listener_orcl
Current Listener is listener_orcl
LSNRCTL>

4. If your oracle listener is not currently running, start it up with the command: start

LSNRCTL> start
Starting /opt/app/oracle/10.2.0/bin/tnslsnr: please wait…

TNSLSNR for LINUX: Version 10.2.0.4.0 – Production
System parameter file is /opt/app/oracle/10.2.0/network/admin/listener.ora
Log messages written to
/opt/app/oracle/10.2.0/network/log/listener_orcl.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_orcl
Version TNSLSNR for LINUX: Version 10.2.0.4.0 – Production
Start Date 21-FEB-2010 16:32:55
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /opt/app/oracle/10.2.0/network/admin/listener.ora
Listener Log File
/opt/app/oracle/10.2.0/network/log/listener_orcl.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
LSNRCTL>

5. To set a password for your listener execute the following lsnrctl command: change_password

NOTE: Hit return when asked for current password if none is set.

LSNRCTL> change_password
Old password:
New password:
Reenter new password:
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.110)(PORT=1521)))
Password changed for listener_orcl
The command completed successfully
LSNRCTL>

6. Save the changes made in the LSNRCTL utility with the 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>

7. Exit out of lsnrctl utility and open up the file listener.ora with you chose of editor.

LSNRCTL> exit
mylinux:> cat listener.ora
# LISTENER.ORA Network Configuration File:
/opt/app/oracle/10.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.110)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
)

SID_LIST_LISTENER_ORCL =
(SID_LIST =
(SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME = /opt/app/oracle/10.2.0)
)

(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /opt/app/oracle/10.2.0)
(PROGRAM = extproc)
)
)

#—-ADDED BY TNSLSNR 21-FEB-2010 16:37:01—
PASSWORDS_listener_orcl = 1DF5C2FD0FE9CFA2
#——————————————–
mylinux:>

NOTE: The tag PASSWORDS_{listener_name} = {new_password} has been added to the file listener.ora and the password is encrypted so it will not look like what you typed. You can shutdown you listener with the password string you original entered or the encrypted string. However, without the password you will not be able to shutdown the LISTENER process.

This completes securing oracle listener with a password.

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