Monthly Archives: March 2010

OPatch, Oracle, Oracle 10g, oracle 9i

OPatch picks up incorrect Home from OraInst.loc: Code 73

Leave a comment

Oracle’s OPatch utility is used for applying interim CPU patches to maintain the security of your RDBMS. OPatch uses the OraInst.loc file located in /etc or /var/opt/oracle depending on your OS to determine what needs to be applied during patch installation. In this article we will cover the error which would be received if your OraInst.loc file points at the incorrect home or contains invalid information.

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 or similar error during installation.

Oracle Interim Patch Installer version 10.2.0.4.9
Copyright (c) 2009, Oracle Corporation. All rights reserved.

Oracle Home : /app/oracle/product/10.2.0
Central Inventory : /app/agent10g
from : /var/opt/oracle/oraInst.loc
OPatch version : 10.2.0.4.9
OUI version : 10.2.0.4.0
OUI location : /app/oracle/product/10.2.0/oui
Log file location : /app/oracle/product/10.2.0/cfgtoollogs/OPatch/OPatch2010
-02-20_14-48-27PM.log

Patch history file:
/app/oracle/product/10.2.0/cfgtoollogs/OPatch/OPatch_history.txt

List of Homes on this system:

Home name= agent10g, Location= “/app/agent10g”
Inventory load failed… OPatch cannot load inventory for the given Oracle Home.
Possible causes are:
Oracle Home dir. path does not exist in Central Inventory
Oracle Home is a symbolic link
Oracle Home inventory is corrupted
LsInventorySession failed: OracleHomeInventory gets null oracleHomeInfo

OPatch failed with error code 73

5. As seen by the error message, the OPatch utility is picking up the incorrect inventory location from the file OraInst.loc. Open up the OraInst.loc file, correct the location of your inventory directory, and re-execute OPatch.

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

OPatch, Oracle, Oracle 10g, oracle 9i

OPatch apply lib .so file not writable: 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 if the file permissions in your Oracle Home are not correctly set and a work-around to continue your patch installation.

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 or similar error during installation.

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

Patch 8568398:
Copy Action: Desctination File “/app/oracle/product/10.2.0/lib/libjox10.so”
is not writeable.
‘oracle.rdbms, 10.2.0.4.0’: Cannot copy file from ‘libjox10.so’ to
‘/app/oracle/product/10.2.0/lib/libjox10.so’

UtilSession failed: Prerequisite check “CheckApplicable” failed.

OPatch failed with error code 73
$

5. The OPatch utility was unable to update one of the lib files for installation, due to a file permission error. To resolve this issue, change directories to your ORACLE_HOME/lib directory.

$ cd /app/oracle/product/10.2.0/lib/

6. Check the existence of the lib file in question with the command ls.

$ ls libjox10.so
-r-xr-xr-x 1 fpdfqt dba 20041728 Jan 07 2009 libjox10.so

7. Make a backup copy of this lib file with the command cp.

$ cp libjox10.so libjox10.so_bak

8. Change permission on the lib file in question.

$ chmod 777 libjox10.so
$

8. Attempt re-installation of your Oracle patch with the OPatch utility.

OPatch napply -skip_subset -skip_duplicate

Return Code = 0

The local system has been patched and can be restarted.

UtilSession: N-Apply done.

OPatch succeeded.
$

As seen above the patch set is successfully installed.

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

hp-ux, itanium, Oracle, Oracle 10g

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

Leave a comment

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

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

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

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

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

1. Started the listener with command lsnrctl start.

$ lsnrctl start

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

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

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

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

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

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

$ lsnrctl status

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

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

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

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

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

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

5. Start the listener with the command lsnrctl start.

$ lsnrctl start

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

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

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

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

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

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

$ lsnrctl status

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

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

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

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

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

And the problem with listener is resolved.

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

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

Oracle OUI installer fails with operating system version verification:

Leave a comment

In some OS version the Oracle Universal Installer will fail with verification of OS. You can by pass this failure with the -ignoresysprereqs tag while execution runInstaller. The following procedure demonstrates the use of this tag.

1. Move to the location of you Oracle binary installer files in this case we have stored our Oracle binaries under /opt/oracle/software/linux/10.2.0.1/database

cd /opt/oracle/software/hp/10.2.0.1/database

2. List the files located under this directory.

myhpux:> ls
doc response stage
install runInstaller welcome.html
myhpux:>

3. If you are performing a remote installation, insure to set your display variable to the appropriate IP address of you client machine. Example: If my client machines IP is 192.168.0.110

myhpux:> export DISPLAY=192.168.0.110:0.0

4. Execute the runInstaller shell script to begin your installation.

myhpux:> ./runInstaller
Starting Oracle Universal Installer…

Checking installer requirements…

Checking operating system version: must be B.11.23. Actual B.11.31
Failed < <<< Exiting Oracle Universal Installer, log for this session can be found at /tmp/OraInstall2010-03-21_08-25-19AM/installActions2010-03-21_08-25-19AM.log mylinux:>

5. NOTE: The installer has failed with with an OS verification error, even though the OS is a higher level then required.

6. Re-execute the runInstaller shell script with the tag -ignoresysprereqs and the Oracle Universal Installer will start as normal.

myhpux:> ./runInstaller -ignoresysprereqs
Starting Oracle Universal Installer…

Checking installer requirements…

Checking operating system version: must be B.11.23. Actual B.11.31
Failed < <<< >>> Ignoring required pre-requisite failures. Continuing…

Preparing to launch Oracle Universal Installer from
/tmp/OraInstall2010-03-21_08-54-42AM. Please wait …myhpux:> Oracle Universal
Installer, Version 10.2.0.1.0 Production
Copyright (C) 1999, 2005, Oracle. All rights reserved.

The OUI (Oracle Universal Installer) ignores the OS version and starts up normally to complete your Oracle installation.

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

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