Europe(1)

欧洲的第一站是法兰克福,去了欧洲央行的总部,一些wall street occupy的抗议者,比我想象的平和很多。这里的天气不错,让我有些嫉妒和羡慕,其次是不多的人口数量,还是比较有秩序,当然了,这是因为人少的缘故。

 

 

 

ORA-20000 index is in unusable

one of the clients send me the error msg below to identify the problem

————————————————————————————————————————

ORA-20000: index “IRSW”.”PK_T06_BD_BDUW_RELA” or partition of such index is in unusable state
*** 2012-05-29 22:00:04.458
GATHER_STATS_JOB: GATHER_TABLE_STATS(‘”IRSW”‘,'”T06_BD_BDUW_RELA”‘,'””‘, …)
ORA-20000: index “IRSW”.”PK_T06_BD_BDUW_RELA” or partition of such index is in unusable state
*** 2012-05-29 22:00:05.183
GATHER_STATS_JOB: GATHER_TABLE_STATS(‘”CLIRS”‘,'”T06_BD_BDUW_RELA”‘,'””‘, …)
ORA-20000: index “CLIRS”.”PK_T06_BD_BDUW_RELA” or partition of such index is in unusable state

—————————————————————————————————————————-

It’s quit clear from the log info, just rebuild the index when the system is in low overload which depends on the size of table.

alter index index_name rebuild online or offline. maybe the table is much bigger, you can choose a time drop and recreate the index.

cause:

some operation must be done on the table which contain the index. some as direct load…or move

more details:the dev of clients use the direct load to insert some data to table and disable the index which is the root of this issue.

 

 

error infor when exp between different version

we got a error message when export user data from 10.2.0.3 database using 10.2.0.4 oracle client..

exporting table TKEYTAB
EXP-00008: ORACLE error 904 encountered
ORA-00904: “IFREEPOOL”: invalid identifier

some notes may help as remind.

 

Applies to:
Oracle Server – Enterprise Edition – Version: 10.2.0.3 to 10.2.0.4
This problem can occur on any platform.

Symptoms
Export reports ORA-00904 “IFREEPOOL”: invalid identifier while using a version 10.2.0.4.0 export client to export from a 10.2.0.3.0 database.

Export Log File ————— . . Export der Tabelle CALCULATEDFIELDS EXP-00008: ORACLE-Fehler 904 aufgetreten ORA-00904: “IFREEPOOL”: invalid identifier Cause
This is an expected behaviour.

The version of the Export utility must be equal to the earliest version of the source or target database. Here, the source database is based on release 10.2.0.3.0 and hence the export client has to be a version 10.2.0.3.0.

An export client of a later release cannot be used for the following reasons:

•The export utility is continuously accessing dictionary objects such as SYS.EXU9LOB.
•Starting with release 10.2.0.4.0 we introduced additional columns such as ‘IFREEPOOL’. This column is not available in 10.2.0.3.0 and hence the client cannot handle this and ORA-00904/EXP-00008 appears.

Client 10.2.0.4.0 is not supported to export from 10.2.0.3.0 database.
Solution
FIX

The version of the Export utility must be equal to the earliest version of the source or target database.

WORKAROUND

None. – This is an expected behaviour.

“opiino: Attach failed!”-error information from trace file

a email was arrived from a friend to query about the abnormal information in trace file which contains something like “opiino: Attach failed!….”. while left no “ORA-” error msg in alert file.

after checking the support.oracle.com, I had something to share,check below:

It’s quite useful.

 


Jun 10, 2011PROBLEMPUBLISHED2

Click to add to Favorites

Rate this document

Email link to this document Open document in new window Printable Page There are no commentsComments (0)

To BottomTo Bottom

In this Document
  Symptoms
Changes
Cause
Solution
References


 

 

Applies to:

Oracle Server – Enterprise Edition – Version: 10.2.0.1 to 10.2.0.4 – Release: 10.2 to 10.2
Oracle Net Services – Version: 10.2.0.1.0 to 10.2.0.4.0   [Release: 10.2 to 10.2]
Information in this document applies to any platform.
Checked for relevance on 09-JUNE-2011

Symptoms

When a client process is abnormally disconnected from the Database (server) process, specifically at a point where the Database needs to negotiate and communicate certain OCI / TWO-TASK data to the client such as with character sets, OCI, etc., then the Database may throw one of the following errors in a UDUMP trace:

opiino: Attach failed! error=-1 ifvp=0

or

opiino: Attach failed! error=-1 ifvp=(nil)

Changes

Perhaps an abnormally high increase in connection activity from App Servers or a network condition or change causing instability.

Cause

There are various reasons why this error might be reported by the Database and some are offered below, but it should be noted that it is “post-connection” generated meaning that the Client connection has been handed off successfully by the Listener process (shown as “Established” in the listener log and services summary) and is generated by the Database itself.

1. The error is being caused by an abnormal disconnection at the OPI / OCI (TWO-TASK) level of the Client process by the Database.
This will occur if the client is terminated (abnormally) before the Database can complete the TWO-TASK communication functions. Please see the reference note on TWO-TASK analysis and resolution.

2. Another possible cause and one that can contribute to a high increase of connections or a cause of disconnections, is a default (or lower) setting of the INBOUND_CONNECT_TIMEOUT value introduced with 10g and which was found to have a default value that was too low for many environments. Another error that might be reported in the Database alert log for the same type of abnormal disconnection and especially with 11g, is ORA-609 “opiodr aborting process unknown ospid”. These two are very similar in that they are generated from the “opi” Database layer and due to an abnormal client termination (with the “ORA-609” being more common in 11g and the “Attach failed” being more common in 10g).

3. Another potential (but rare) cause is if the Listener being used for Client access is of a LOWER version than the Database being accessed. For example, using a 9.2.x listener to access a 10.x Database which is not certified or supported and will cause abnormal conditions for connections.

Solution

1. Due to the fact that this is a generic error within the Database OPI code, the error itself is not useful in determining any actual cause or in assisting to provide a solution.
Analysis needs to be done on what was happening between the clients and the Database server at the time these were thrown and which caused the failure.
Some examples of questions to ask and data to collect:
– was the network between the affected clients and the Database in a stable condition?
– was there a sudden and abnormal increase in connection activity that may have flooded the Database with connections?
– was the Client abnormally terminated on its side (such as a machine failure)?

2. To ensure that this is not related to a possible INBOUND_CONNECT_TIMEOUT issue (IBCT), make certain to set both the IBCT in the listener.ora file and sqlnet.ora file on the Database Server side (these are INBOUND CONNECT settings so only affect incoming client connections requests) to a higher value, such as 120.
Please note, that this increase in IBCT will not have a negative effect on how long a client connection will take to be established. This setting is a security feature and is a threshold used to determine how long to wait before an unresponsive connection is terminated (used specifically to prevent Denial of Service attacks on the Database). See the notes referenced for complete details.

3. If this occurs ALL the time and there is an indication that a lower version listener is used for a higher version Database, then the same (or higher) version listener should be started and used for the Database.
See reference notes following on this.

References

NOTE:1012295.102 – HOW TO SUPPORT TWO-TASK COMMON ERRORS
NOTE:149628.1 – Creating Multiple Listeners on UNIX – Including Migration or Upgrades
NOTE:151812.1 – Setting up Multiple Listeners on Windows – Including Migration or Upgrades
NOTE:345197.1 – Connections that Used to Work in Oracle 10.1 Now Intermittently Fail with ORA-3113,ORA-3106 or ORA-3136 from 10.2 Onwards
NOTE:609.1 – ORA-609 TNS-12537 and TNS-12547 in 11g Alert.log
NOTE:1121357.1 – Troubleshooting ORA-609 : opiodr aborting process unknown ospid

change the path of control file

1.create a text based file for spfile

SQL> create spfile from pfile=’f:initAAA.ora’;

file created.

2.edit the flat file just created in the path of control file

3.start the database using pfile just created and recreate a new spfile
SQL> startup
Total System Global Area 612368384 bytes
Fixed Size 1304728 bytes
Variable Size 167774056 bytes
Database Buffers 436207616 bytes
Redo Buffers 7081984 bytes

SQL> show parameter spfile;

NAME TYPE
———————————— ————-
VALUE
——————————
spfile string
C:ORACLEPRODUCT10.2.0DB_1
DATABASESPFILEAAA.ORA

 

change the path of redo log file

changing the path of REDO log file is the same as datafile and temporary file

1.keep the instance shutdown
SQL> SHUTDOWN IMMEDIATE
2.host copy the datafile to new location

SQL> HOST COPY F:oradataaaaREDO01.DBF F:oradata2aaaREDO01.DBF
The system cannot find the file specified.

SQL> HOST COPY F:oradataaaaREDO01.LOG F:oradata2aaaREDO01.LOG
1 file(s) copied.

SQL> HOST COPY F:oradataaaaREDO02.LOG F:oradata2aaaREDO02.LOG
1 file(s) copied.

SQL> HOST COPY F:oradataaaaREDO03.LOG F:oradata2aaaREDO03.LOG
1 file(s) copied.

3.start the database to mount stage

SQL> STARTUP MOUNT;
Total System Global Area 612368384 bytes
Fixed Size 1304728 bytes
Variable Size 167774056 bytes
Database Buffers 436207616 bytes
Redo Buffers 7081984 bytes

3.change the path of redo log file
SQL> alter database rename file ‘F:ORADATAAAAREDO01.LOG’ to ‘F:ORADATA2AAAREDO01.LOG’;

database altered

SQL> alter database rename file ‘F:ORADATAAAAREDO02.LOG’ to ‘F:ORADATA2AAAREDO02.LOG’;

database altered

SQL> alter database rename file ‘F:ORADATAAAAREDO03.LOG’ to ‘F:ORADATA2AAAREDO03.LOG’;

database altered

SQL> ALTER DATABASE OPEN;

database altered

SQL>
SQL> select status from v$instance;

STATUS
————————
OPEN

ok,everything was done!!

change the path of datafile and temporary file

1.oracle was on the open status,and check the datafile information
SQL> col file_name for a50
SQL> select file_id,file_name from dba_data_files;

FILE_ID FILE_NAME
———- —————————————–
4 F:ORADATAAAAUSERS01.DBF
3 F:ORADATAAAASYSAUX01.DBF
2 F:ORADATAAAAUNDOTBS01.DBF
1 F:ORADATAAAASYSTEM01.DBF
SQL> shutdown immediate
database closed

2.copy the datafile to new path
SQL> host copy F:ORADATAAAAUSERS01.DBF F:ORADATA2AAAUSERS01.DBF
1 file(s) copied.

SQL> host copy F:ORADATAAAASYSAUX01.DBF F:ORADATA2AAASYSAUX01.DBF
1 file(s) copied.

SQL> host copy F:ORADATAAAAUNDOTBS01.DBF F:ORADATA2AAAUNDOTBS01.DBF
1 file(s) copied.

SQL> host copy F:ORADATAAAASYSTEM01.DBF F:ORADATA2AAASYSTEM01.DBF
1 file(s) copied.

3.start database mount and change the path of datafile

SQL> startup mount
Total System Global Area 612368384 bytes
Fixed Size 1304728 bytes
Variable Size 167774056 bytes
Database Buffers 436207616 bytes
Redo Buffers 7081984 bytes

SQL> alter database rename file ‘F:ORADATAAAAUSERS01.DBF’ to ‘F:ORADATA2AAAUSERS01.DBF’;

database altered

SQL> alter database rename file ‘F:ORADATAAAASYSAUX01.DBF’ to ‘F:ORADATA2AAASYSAUX01.DBF’;

database altered

SQL> alter database rename file ‘F:ORADATAAAAUNDOTBS01.DBF’ to ‘F:ORADATA2AAAUNDOTBS01.DBF’;

database altered

SQL> alter database rename file ‘F:ORADATAAAASYSTEM01.DBF’ to ‘F:ORADATA2AAASYSTEM01.DBF’;

database altered

SQL> alter database open;

SQL> select file_id,file_name from dba_data_files;

FILE_ID FILE_NAME
———- ————————————————–
4 F:ORADATA2AAAUSERS01.DBF
3 F:ORADATA2AAASYSAUX01.DBF
2 F:ORADATA2AAAUNDOTBS01.DBF
1 F:ORADATA2AAASYSTEM01.DBF

now,the datafile was relocated to the new path….

ps: if you want to change the path of temporary file, it’s the same process as datafile