ORA-00904: "SYS"."DBMS_EXPORT_EXTENSION"."FUNC_INDEX_DEFAULT": invalid identifier

[Problem]

Export: Release 9.2.0.8.0 - Production on Wed Jul 25 10:47:29 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses UTF8 character set (possible charset conversion)

About to export specified tables via Direct Path ...
Current user changed to DBAPL
. . exporting table RPT_LOCAL__01 1012208 rows exported
EXP-00056: ORACLE error 904 encountered
ORA-00904: "SYS"."DBMS_EXPORT_EXTENSION"."FUNC_INDEX_DEFAULT": invalid identifier
Export terminated successfully with warnings.

[Root Cause]

It caused by different DB version in the server , as below two version is installed:

DB1 V9.2.0.8 , while DB2 is 9.2.0.5

In operator id 's env , the ORACLE_HOME is point to 9.2.0.8, caused using higher version exp to exp data from lower version db.

[solution]
in the script , explicitly specify the path of exp , i.e:

/software/oradb1/product/9.2.0/bin/exp $RPT_CONNECT@$RPT_DB file=$expfile log=$LOGFILE direct=y recordlength=65535 buffer=10240000 compress=N tables=${tab_name} statistics=none

Today 21-Jan-2009, hit another similar case. get new findings from metalink

Doc ID:

358508.1

.

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 10.2.0.1
This problem can occur on any platform.

Symptoms

Export fails with:

EXP-00056: ORACLE error 904 encountered
ORA-00904: "SYS"."DBMS_EXPORT_EXTENSION"."FUNC_INDEX_DEFAULT": invalid identifier

Changes

A patchset was recently installed against the database.

Cause

After the patchset installation, the data dictionary components have not been upgraded to the patchset release (e.g. 10.1.0.4)

A patchset (e.g. 10.1.0.4) was applied to an existing ORACLE_HOME install but one or more of the patchset steps were not performed per the patchset readme to bring the database components up to the same version as the patched software as shown in DBA_REGISTRY.

If the database components versions do not match the software and the running instance, then the exp utility fails as it finds an earlier version of the exp packages in the database.

Solution

Follow the Post-Installation Tasks section of the Oracle Database Server Patch Set Notes to run the catpatch.sql script with the database started in migrate mode and then retry the export.

Connect to the database as SYSDBA and run:

SQL> STARTUP MIGRATE
SQL> SPOOL patch.log
SQL> @?/rdbms/admin/catpatch.sql
SQL> SPOOL OFF
SQL> SHUTDOWN
SQL> STARTUP

Flashback drop a table

Today I have change to use flashback drop table. A developer came to me and tell me what happend in one 10g databse.

With the help of "flashback table abc to before drop", I recovered it within minutes.
Compare to doing the same thing in 9i,
1. use lominer to find the time when the table was dropped.
2. Restored the database in anothe place
3. Perform incomplete revoery to the timestamp using backup controlfile
4. Export the table and import to production.

Guess how many time is needed.

More to add
flashback doesn't use undo tablespace.
You can recover a dropped table using Oracle’s recycle bin functionality: a dropped table is
stored in a special structure in the tablespace and is available for retrieval as long as the space
occupied by the table in the tablespace is not needed for new objects. Even if the table is no
longer in the tablespace’s recycle bin, depending on the criticality of the dropped table, you can
use either tablespace point in time recovery (TSPITR) or Flashback Database Recovery to
recover the table, taking into consideration the potential data loss for other objects stored in the
same tablespace for TSPITR or in the database if you use Flashback Database Recovery.

ora-2393 cpu_call exceed profile limit

Wed Jul 25 10:24:30 2007
Errors in file
ORA-00604: error
occurred at recursive SQL level 1
ORA-02393: exceeded call limit on CPU
usage
ORA-00604: error occurred at recursive SQL level 1
ORA-02393:
exceeded call limit on CPU usage
ORA-00604: error occurred at recursive SQL
level 1
ORA-02393: exceeded call limit on CPU usage
ORA-02393: exceeded
call limit on CPU usage

Error:  ORA 2393
Text:   exceeded call limit on CPU usage
-------------------------------------------------------------------------------
Cause:  An attempt was made to exceed the maximum CPU time for a call, a parse,
       execute, or fetch, allowed by the CPU_PER_CALL clause of the user
      profile.
Action: If this happens often, ask the database administrator to increase the
      CPU_PER_CALL limit of the user profile.

CentOS 4.4 upgrade to CentOS 5

During start transaction, hit the error message : installation failed due to the follwing reason (no reason it is shown, instead it is blank).

What I did are:
1. Remove unnecessary repository from /etc/yum.repo.d .
2. Free up disk space (/) to 11Gb. (Think it was due to this reason,as failed twice when free space was <6Gb)
3. Tried to upgrade in text mode.

Don't know which of above is the exact cause/solution, anyway it suceed after near four hours upgrade.


After that, need the folloings to make live udpate available.

cd /etc
cp yum.conf.rpmnew yum.conf
cd /etc/yum.repos.d
cp CentOS-Base.repo.rpmnew CentOS-Base.repo

Cheers.

References:
http://www.linuxfans.org/bbs/redirect.php?tid=178447&goto=lastpost&highlight=

Purging the Audit Trail

Database audit records for statement, privilege, and object auditing are stored in the table
SYS.AUD$. Depending on how extensive your auditing and retention policies are, you will need
to periodically delete old audit records from this table. The database does not provide an inter-
face to assist in deleting rows from the audit table, so you will need to do so yourself. To purge
audit records older than 90 days, execute the following as user SYS:
DELETE FROM sys.aud$ WHERE timestamp# < SYSDATE -90;

Default database tablespace

If the default tablespace is not specified during the database creation, it defaults to SYSTEM. But how do you know which tablespace is default for the existing database? Issue the following query:

SELECT PROPERTY_VALUE
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = 'DEFAULT_PERMANENT_TABLESPACE';

The DATABASE_PROPERTIES view shows some very important information, in addition to the default tablespace—such as the default temporary tablespace, global database name, time zone, and much more.


To change the database default permenantly/temporary tablespace, use the ALTER DATABASE statement,
like this:
ALTER DATABASE DEFAULT TABLESPACE users;ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Eclipse Intro error

Problem : Error message Windos "Intro error " prompted during initialistion OF "Eclipse IDE for C / C++ Developers".

Could not find a valid configuration for intro Part: intro element" in
extension: org.eclipse.ui.intro in Bundle: org.eclipse.ui.intro.universal

Check Error Log view for details

CAUSE:
No JAVA_HOME is exported.

Solution:

1. download the J2SE from java.com, choose the tar.gz file instead of the auto extracted file.
2. Export the JAVA_HOME

rlwrap works on centos5

Yesterday I managed to compile wlrap on CentOS 5. The root cause is missing development packages, like libglib, gtk+-devel ... , after the fresh installation of CentOS 5.

To rectify:
1. note down the missage file name, NOT package name.(as we are not ware of the package name)
2. For lookup purpose, go to rpm.pbone.ne and key in the mssing file, click seach and from the listed packages you will guess which to install.
3. Try to install the package from thy linux distributor, for centos, use "yum install

By using this method, I succeed to install my favor tools: mplayer, freemind, stardict ...

Cheers

use except to find dupliated records

SQL> create table stu (f1 number);
insert table st
Table created.

SQL> insert into stu values (100);

1 row created.

SQL> insert into stu values (101);

1 row created.

SQL> insert into stu values (101);

1 row created.

SQL> insert into stu values (102);

1 row created.

SQL> insert into stu values (103);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from stu;

F1
----------
100
101
101
102
103


SQL> alter table stu add constraint f1_uk unique(f1) using index tablespace users;
alter table stu add constraint f1_uk unique(f1) using index tablespace users
*
ERROR at line 1:
ORA-02299: cannot validate (SYS.F1_UK) - duplicate keys found


SQL> desc exceptions
ERROR:
ORA-04043: object exceptions does not exist


SQL> @?/rdbms/admin/utlexcpt

Table created.

SQL> desc exceptions
Name Null? Type
----------------------------------------- -------- ----------------------------
ROW_ID ROWID
OWNER VARCHAR2(30)
TABLE_NAME VARCHAR2(30)
CONSTRAINT VARCHAR2(30)


SQL> alter table stu add constraint f1_uk unique(f1) using index tablespace users exceptions into exceptions;
alter table stu add constraint f1_uk unique(f1) using index tablespace users exceptions into exceptions
*
ERROR at line 1:
ORA-02299: cannot validate (SYS.F1_UK) - duplicate keys found


SQL> desc exceptions
Name Null? Type
----------------------------------------- -------- ----------------------------
ROW_ID ROWID
OWNER VARCHAR2(30)
TABLE_NAME VARCHAR2(30)
CONSTRAINT VARCHAR2(30)

SQL> select * from exceptions
2 /

ROW_ID OWNER TABLE_NAME
------------------ ------------------------------ ------------------------------
CONSTRAINT
------------------------------
AAAMfdAABAAANsSAAC SYS STU
F1_UK

AAAMfdAABAAANsSAAB SYS STU
F1_UK


SQL> select * from user_constraints where table_name='STU';

no rows selected

SQL> SELECT * FROM STU where rowid in ('AAAMfdAABAAANsSAAC','AAAMfdAABAAANsSAAB');

F1
----------
101
101

SQL> DELETE FROM STU where rowid in ('AAAMfdAABAAANsSAAB');
1 row deleted.

SQL> COMMIT;

Commit complete.

SQL> alter table stu add constraint f1_uk unique(f1) using index tablespace users exceptions into exceptions;

Table altered.


SQL> SET PAGES 1000
SQL> select * from user_constraints where table_name='STU';

OWNER CONSTRAINT_NAME C
------------------------------ ------------------------------ -
TABLE_NAME
------------------------------
SEARCH_CONDITION
--------------------------------------------------------------------------------
R_OWNER R_CONSTRAINT_NAME DELETE_RU STATUS
------------------------------ ------------------------------ --------- --------
DEFERRABLE DEFERRED VALIDATED GENERATED BAD RELY LAST_CHAN
-------------- --------- ------------- -------------- --- ---- ---------
INDEX_OWNER INDEX_NAME INVALID
------------------------------ ------------------------------ -------
VIEW_RELATED
--------------
SYS F1_UK U
STU

ENABLED
NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 14-JUL-07
SYS F1_UK

sqlplus command history wrapper -- gqlplus

Before I upgrade my Linux to CentOS 5(equal to RHEL 5), I am happy with the sql command wrapper --rlwrap, except for having problem with automatically invoke "alias sqlplus='rlwrap sqlplus' " in .bash_profile. Each time I have to manually type it again after launch gnome-terminal.

Now my favorite rlwrap can't be succefully comipled under CentOS 5, becaus can't manage to install the prerequisite library -readline. No choice , have to find alternative solution.

Luckly, I fing gsqlplus. Download it from sourceforge.net, without compilation. I run the pre-build binary suprisely.

Move it to $ORACLE_HOME/bin without hesitate.

The minor issue is looks slow while invoking it, takes tens of seconds on my p3-600/384Mb memory.

Never mind. It is my mostest stable machine.


BTW, I also find another open source yasql, however facing the same problem with compilation. Think I need to improve my Linux knowlege rapidly

MetaLink search engine plugin

There's a new MetaLink search engine plugin available for Microsoft Internet Explorer 7.0 and Mozilla Firefox 2.0.

Want to have something like this?

1. go to http://mycroft.mozdev.org/ and teyp "metalink" to search the plugin.
2. Install it.
3. That is it. Try!

9.2.0.8 critcal bug 584523

A critical bug of 10.2.0.2. I had blood experience ...

possible error message could be

ORA-01595: error freeing extent (2) of rollback segment (10))
ORA-01594: attempt to wrap into rollback segment (10) extent (2) which is being

ORA-00600: internal error code, arguments: [2032], [12598820], [2131047733], [81
92], [0], [1], [4155315611], [362479617]


ORA-00600: internal error code, arguments: [kcbzpb_1], [2206308750], [4], [1], [], [], [], []


bug 584523

10g Init parameter(sepecific)

--Common Settings for 10g Database
commit_write='batch' #(or not to set it )
recyclebin=on
disk_asynch_io=false #(For platform doesn’t support asynchronised I/O, like HP-UX)
-- Use hidden parameters as workaround for 10g bugs, which are fixed in future version.
_gby_hash_aggregation_enabled=FALSE # bug 4604970
_kghdsidx_count=1 #bug 5508574
_table_lookup_prefetch_size=0 #bug 5509707, 4148420

ORA-1031 while connect as sysdba on windows platform

Two possible causes are:

1. SQLNET.AUTHENTICATION_SERVICES = (NTS)

The value is not NTS . By default in 8.1.x and onwards, a new database uses Windows Native Authentication by having the above setting in the "sqlnet.ora" file.

2. No appropriate DBA group

Create group ORA_DBA or ORA__DBA, and add your windows ID to it.

About _log_io_size

_LOG_IO_SIZE specifies the max number of redo blocks to write out per IO request.
By default the value is 1/3 of log buffer size, number in blocks.Its range is between 1/3 (default) and 1/2 (max) of the log buffer size. Note that the log block size is not the same as DB block size, which is platform-dependent. e.g, in HP-UX, it is 1K.


To check it values , below two ways work.
1. check BLOCK_SIZE from v$archived_log.
-- This is the logical block size of the archived log, which is the same as the logical block size of the online log from which the archived log was copied. The online log logical block size is a platform-specific value that is not adjustable by the user.
-- For my x86 Linux 32bit , it is 512 bytes.

2. select distinct lebsz "LGWR block size (Bytes)" from X$KCCLE;


While, The max IO size on HPUX 11 is 256K, on HPUX 10.20 it is 64K. Async IO on HPUX 11 has a limit of 1MB.

If the Oracle redo log block size on the systems are 1K, and we are doing large batch commits, and using a large log buffer to reduce LGWR IOs, then it is possible we will be doing IO writes that are larger than the OS I/O size. Hence we are not doing efficient IO writes.

e.g. 1MB Log buffer, 1/3rd of that is 341K, which will be 341 redo blocks written in redo log. This incurs two I/O request, right?

So, if we are doing large commits and we want better efficient IOs:

If HPUX 11 LVM has a max IO size of 256K, and
my redo log block size is 1K, then should I set this parameter to 256?


As lgwr writes whatever to be written out in one i/o ie it uses one system call to write all the buffered data so lgwr may flush 1/2 of the log buffer if this _log_io_size is changed. So in theory setting it to the max i/o size of the OS LVM should help.
(Never had a chance to tweak with this parameter, so can't say for sure that it'll improve performance, but looking at the facts it should.)

About Long datatype

LONG A legacy datatype that exists for backward compatibility. It stores variable-length alphanumeric
data up to 2GB in size. There are many restrictions on the usage of the columns of type
LONG: there is a limit of one column of type LONG per table, tables containing a LONG cannot be
partitioned, LONG datatypes cannot be used in subqueries, and few functions will work with
LONG data. The CLOB datatype is the preferred datatype for character data larger than VARCHAR2.

Can't start DBConsole

C:\Documents and Settings\oracle>emctl start dbconsole
Oracle Enterprise Manager 10g Database Control Release 10.2.0.2.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http://mdw01:1159/em/console/aboutApplication
Starting Oracle Enterprise Manager 10g Database Control ...The OracleDBConsoleALPHAservice is starting.............


A service specific error occurred: 2.

More help is available by typing NET HELPMSG 3547.


below is except from metalink note Note:344150.1
Applies to:
Enterprise Manager for RDBMS - Version: 10.1.0.4
Microsoft Windows 2000
Symptoms
The dbconsole service does not start.
'emctl start dbconsole' returns the following error:
' A service specific error occurred: 2'
Cause
The Windows service has not enough privileges to be started.
Solution
Configure DbConsole service to use the "Log on As" feature. Log on as the the 'oracle' user, which is
member of local Administrators.

When Does Database Writer Write?

The DBWn background process writes to the datafiles whenever one of the following events occurs:
 A user’s Server Process has searched too long for a free buffer when reading a buffer into the Buffer Cache.
 The number of modified and committed, but unwritten, buffers in the Database Buffer
Cache is too large.
 At a database Checkpoint event. See Chapters 10 and 11 for information on checkpoints.
 The instance is shut down using any method other than a shutdown abort.
 A tablespace is placed into backup mode.
 A tablespace is taken offline to make it unavailable or changed to READ ONLY.
 A segment is dropped.

When Does Log Writer Write?

The LGWR background process writes to the current redo log group whenever one of the
following database events occurs:
 Every three seconds.
 A user commits a transaction.
 The Redo Log Buffer is one-third full.
 The Redo Log Buffer contains 1MB worth of redo information.
 Before the DBWn process whenever a database checkpoint occurs.

Optimal Flexible Architecture (OFA)

http://members.ozemail.com.au/~gpiper/oracle/ora_8.html
http://download-uk.oracle.com/docs/html/A97297_01/appg_ofa.htm
http://www.remote-dba.cc/teaser_aegis_ault_dba_ofa.htm

One way to simplify installation planning is to adopt the Optimal Flexible Architecture
(OFA) model that Oracle recommends as a best-practice methodology for managing Oracle
installations in Unix environments (and to a lesser extent, Windows environments).

The OFA model prescribes that the directory structures under the mount points use a consistent
and meaningful naming convention.
In addition to this naming convention, the OFA model also
assigns standard operating system environment variable names to some of these directory paths
as “nicknames” to aid in navigation and ensure portability of the directory structures in the
event that they need to be moved to new file systems.


The OFA model is useful for establishing a manageable directory structure for a new Oracle
server. The OFA model recommends mount point, directory, and file-naming conventions.
Once the OFA structure is established, you can use the OUI to install the Oracle 10g software
into the location you’ve selected.

Shrink table space

SQL> create table waste_table as select * from dba_objects;

Table created.

SQL> set lines 2000
SQL> select bytes, segment_name from user_segments;

BYTES SEGMENT_NAME
---------- ---------------------------------------------------------------------------------
6291456 WASTE_TABLE

SQL> delete from waste_table;

50553 rows deleted.

SQL> commit;

Commit complete.

SQL> select bytes, segment_name from user_segments;

BYTES SEGMENT_NAME
---------- ---------------------------------------------------------------------------------
6291456 WASTE_TABLE

--to avoid table lock if end users are accessing it

SQL> alter table waste_table shrink space compact;
alter table waste_table shrink space compact
*
ERROR at line 1:
ORA-10636: ROW MOVEMENT is not enabled


SQL> alter table waste_table enable row movement;

Table altered.

SQL> alter table waste_table shrink space compact;

Table altered.

SQL> select bytes, segment_name from user_segments;

BYTES SEGMENT_NAME
---------- ---------------------------------------------------------------------------------
6291456 WASTE_TABLE

--issue the command 2nd time, to lower down the High Water Mark
SQL> alter table waste_table shrink space ;

Table altered.

SQL> select bytes, segment_name from user_segments;

BYTES SEGMENT_NAME
---------- ---------------------------------------------------------------------------------
65536 WASTE_TABLE