Tag Archives: find

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

Find the Oracle parameter causing ORA-32004 and removing

Leave a comment

During the normally life cycle of a database, multiple upgrades will occur, resulting in parameters becoming obsolete. This article shows how to determine the parameter which is obsolete and removing it.

Typical error generated by an obsolete parameter:

[oracle@mylinux2 bin]$ ./dbca -silent -deleteDatabase -sourceDB orcl
-sysDBAUserName larry -sysDBAPassword larry
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
[oracle@mylinux2 bin]$

1. Logon to the database as the sys user.

[oracle@mylinux2 bin]$ sqlplus ‘/ as sysdba’

SQL*Plus: Release 11.2.0.1.0 Production on Thu Feb 10 01:32:07 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

2. Execute the following SQL to find the problem

select name, value from v$parameter where ISDEPRECATED=’TRUE’ and ISDEFAULT=’FALSE’;

SQL> select name, value from v$parameter where ISDEPRECATED=’TRUE’ and ISDEFAULT=’FALSE’;

NAME
——————————————————————————–
VALUE
——————————————————————————–
remote_os_authent
FALSE
SQL>

3. Reset this parameter

SQL> alter system reset remote_os_authent;
System altered.
SQL>

4. Stop database.

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

5. Start database.

SQL> startup
ORACLE instance started.

Total System Global Area 217157632 bytes
Fixed Size 2211928 bytes
Variable Size 159387560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5226496 bytes
Database mounted.
Database opened.
SQL>

This completes removing of obsolete or deprecated Oracle parameters.

Larry Catt, OCP
oracle@allcompute.com
www.allcompute.com

11g, hp-ux, OPatch, Oracle, Oracle 10g, oracle 9i

How to debug Oracle opatch utility

Leave a comment

The following procedure shows how to debug the Oracle opatch utility when you receive an error.

1. Execution of opatch utility results in the following error.

[oracle@mylinux1 OPatch]$ opatch lsinventory
Invoking OPatch 11.1.0.6.6

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

Oracle Home : /u01/app/oracle/product/11.2.0/dbhome_1
Central Inventory : /u01/app/oraInventory
from : /etc/oraInst.loc
OPatch version : 11.1.0.6.6
OUI version : 11.2.0.1.0
OUI location : /u01/app/oracle/product/11.2.0/dbhome_1/oui
Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2011-04-02_19-17-51PM.log

Patch history file: /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch_history.txt
Inventory load failed… OPatch cannot load inventory for the given Oracle Home.
LsInventorySession failed: Unable to create patchObject

OPatch failed with error code 73
[oracle@mylinux1 OPatch]$

2. Set the environmental variable OPATCH_DEBUG equal to true.

[oracle@mylinux1 OPatch]$ export OPATCH_DEBUG=true
[oracle@mylinux1 OPatch]$

3. Re-execute your opatch utility and every action performed by opatch will be displayed in your buffer. Use the messages to determine the error.

This completes use of debug with the opatch utility.

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

Oracle

Determination of Components installed on your Oracle RDBMS.

Leave a comment

Oracle provides the view DBA_REGISTRY which records all components installed on our RDBMS. This view provides more then just the components installed, but also: version, if it is valid, date of modification, etc. This information is very helpful in the administration of any RDBMS. This article covers the determination of what components are installed within our Oracle RDBMS.

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 Fri Feb 19 19:24:41 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 comp_name, version, status, modified from DBA_REGISTRY;

SQL> column comp_name format a35
SQL> column version format a12
SQL> column status format a8
SQL> select comp_name, version, status, modified from DBA_REGISTRY;

COMP_NAME VERSION STATUS MODIFIED
———————————– ———— ——– ————
Oracle Database Catalog Views 10.2.0.4.0 VALID 08-SEP-2009 13:50:15
Oracle Database Packages and Types 10.2.0.4.0 INVALID 08-SEP-2009 13:50:15
Oracle Workspace Manager 10.2.0.4.3 VALID 08-SEP-2009 13:50:15
JServer JAVA Virtual Machine 10.2.0.4.0 VALID 08-SEP-2009 13:50:15
Oracle XDK 10.2.0.4.0 VALID 08-SEP-2009 13:50:15
Oracle interMedia 10.2.0.4.0 VALID 08-SEP-2009 13:50:15
Spatial 10.2.0.4.0 VALID 08-SEP-2009 13:50:15
Oracle Text 10.2.0.4.0 VALID 08-SEP-2009 13:50:15
Oracle Ultra Search 10.2.0.4.0 INVALID 08-SEP-2009 13:50:20
Oracle XML Database 10.2.0.4.0 VALID 08-SEP-2009 13:50:20
Oracle Application Server Metadata 10.1.2.0.2 VALID 07-JUL-2006 02:52:19
Repository Version-R

Oracle Application Server Distribut 10.1.2.0.2 VALID 06-JUL-2006 23:34:41
ed Configuration Management

Oracle Business Intelligence Discov 10.1.2.0.2 VALID 06-JUL-2006 23:34:43
erer

Oracle Workflow 10.1.2.0.2 VALID 06-JUL-2006 23:37:16
Oracle Application Server Integrati 10.1.2.0.2 VALID 06-JUL-2006 23:40:19
on B2B

Oracle Application Server Integrati 10.1.2.0.2 VALID 06-JUL-2006 23:40:25
on BAM

Oracle Application Server Certifica 10.1.2.0.2 VALID 06-JUL-2006 23:40:42
te Authority

Oracle Internet Directory 10.1.2.0.2 VALID 06-JUL-2006 23:41:16
Oracle Application Server Single Si 10.1.2.0.2 VALID 06-JUL-2006 23:43:27
gn-On

Oracle Application Server Portal 10.1.2.0.2 VALID 07-JUL-2006 00:01:41
Oracle Application Server Syndicati 10.1.2.0.2 VALID 07-JUL-2006 00:01:55
on Services

Oracle Application Server UDDI Regi 10.1.2.0.2 VALID 07-JUL-2006 00:02:06
stry

Oracle Application Server Web Clipp 10.1.2.0.2 VALID 07-JUL-2006 00:02:17
ing

Oracle Application Server Wireless 10.1.2.0.2 VALID 07-JUL-2006 00:08:24
Oracle Database Java Packages 10.2.0.4.0 VALID 08-SEP-2009 13:50:20

25 rows selected.

SQL>

This completes determination of components installed on your Oracle RDBMS.

Larry J. 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

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

Oracle – Determining the CPU patch applied to an Oracle database

Leave a comment

Oracle – Determining the CPU patch applied to an Oracle database

Oracle Corporation releases Critical Patch Updates (CPU) on a quarterly schedule (Jan, Apr, Jul, and Oct) to ensure their database software have the most resent patches. However, I have come across several situations where DBA’s had a difficult time determining the current patch level of their databases. This article covers viewing of the current patch level of an Oracle database. This procedure will work on any OS.

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 Feb 07 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. Execute the following SQL statement: select action, comments from
DBA_REGISTRY_HISTORY;

SQL> column action format a10
SQL> column comments format a25
SQL>select action, comments from DBA_REGISTRY_HISTORY;

ACTION COMMENTS
———- ————————-
CPU CPUApr2008
CPU view recompilation
UPGRADE Upgraded from 9.2.0.8.0
APPLY CPUApr2009
CPU view recompilation

SQL>

4. As seen above the latest CPU patch for this database is CPUAPR2009.

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