When updated table, found error:
ORA-08102: index key not found, obj# 116528, file 96, block 8795 (2)
This problem I had resolved by rebuild indexes (ORA-08102 on index objects). But today I can not use this solution:
What is about this error?
Error: ORA-08102
Oracle10g Message
Text: index key not found, obj# %s, file %s, block %s (%s)
Oracle 9.2 or Earlier Error Message
Text: index key not found, obj# %s, dba %s (%s)
ORA-08102 is a mismatch between the key(s) stored in the index and the values stored in the table.
What typically happens is the index is built and at some future time, some type of corruption occurs, either in the table or index, to cause the mismatch.
When know about is, what is the way to resolve it? How?
-> Check object, that have the problem, if it's index... try to rebuild:
SQL>select object_name, object_type
from dba_objects
where object_id = [obj# in ORA-08102]
Example:
SQL> select object_name, object_type
from dba_objects
where object_id = 116528;
OBJECT_NAME OBJECT_TYPE
--------------- -------------------
XXX_IDX07 INDEX PARTITION
And then rebuild it.
SQL> alter index [index_name] rebuild [online];
OR "partition index"
SQL> select partition_name from dba_segments where TABLESPACE_NAME = (select tablespace_name from dba_data_files where file_id=[file_id from ORA-08102] ) and SEGMENT_NAME=[object_name from dba_objects];
SQL> select partition_name from dba_segments where TABLESPACE_NAME = (select tablespace_name from dba_data_files where file_id=96) and SEGMENT_NAME='XXX_IDX07';
PARTITION_NAME
------------------------------
XXX_PART1
SQL> alter index [index_name] rebuild partition [partition_name] [online];
if ORA-08102 on table objects -> resolve by "analyze table ... validate structure.. or use dbv to check corrupt on table" ) don't forget check error on OS , trace file and alert log file.
-> (ORA-08102 on index objects), if rebuilt indexes, But still error ORA-08102: Drop that index and recreate it.
vendredi 11 juin 2010
jeudi 3 juin 2010
Freeing up db_recovery_file_dest reclaimable space.
I temporarily reduced db_recovery_file_dest_size to force the database to free reclaimable space from recovery_file_dest.
This reduced disk usage of ASM_ORADATA217 by over 320GB.
SYS>show parameter recovery
NAME TYPE VALUE
----------------------------------- ------------------------- ---------------------
db_recovery_file_dest string +ASM_ORADATA217
db_recovery_file_dest_size big integer 375G
SYS>select * from V$FLASH_RECOVERY_AREA_USAGE;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG 0 0 0
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 88.7 83.73 1991
SYS>alter system set db_recovery_file_dest_size=20G;
System altered.
SYS>alter system set db_recovery_file_dest_size=375G;
System altered.
SYS>alter system switch logfile;
System altered.
SYS>select * from V$FLASH_RECOVERY_AREA_USAGE;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG 0 0 0
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 5.32 .35 135
This reduced disk usage of ASM_ORADATA217 by over 320GB.
SYS>show parameter recovery
NAME TYPE VALUE
----------------------------------- ------------------------- ---------------------
db_recovery_file_dest string +ASM_ORADATA217
db_recovery_file_dest_size big integer 375G
SYS>select * from V$FLASH_RECOVERY_AREA_USAGE;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG 0 0 0
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 88.7 83.73 1991
SYS>alter system set db_recovery_file_dest_size=20G;
System altered.
SYS>alter system set db_recovery_file_dest_size=375G;
System altered.
SYS>alter system switch logfile;
System altered.
SYS>select * from V$FLASH_RECOVERY_AREA_USAGE;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG 0 0 0
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 5.32 .35 135
mardi 1 juin 2010
Cannot export empty tables in 11.2
In 11g there is no segment allocated to the table until you insert, so the EXP utility fails to export empty tables.
[oracle@dev-oranode-221 ~]$ sqlplus a/a
SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 30 14:38:06 2009
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, Oracle Database Vault
and Real Application Testing options
SQL> create table imp_test(id integer primary key);
Table created.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining, Oracle Database Vault
and Real Application Testing options
[oracle@dev-oranode-221 ~]$ exp a/a
Export: Release 11.2.0.1.0 - Production on Mon Nov 30 14:37:17 2009
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining, Oracle Database Vault
and Real Application Testing options
Enter array fetch buffer size: 4096 >
Export file: expdat.dmp >
(1)E(ntire database), (2)U(sers), or (3)T(ables): (2)U > 3
Export table data (yes/no): yes >
Compress extents (yes/no): yes >
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8MSWIN1252 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
Table(T) or Partition(T:P) to be exported: (RETURN to quit) > imp_test
EXP-00011: A.IMP_TEST does not exist
Table(T) or Partition(T:P) to be exported: (RETURN to quit) >
Export terminated successfully with warnings.
[oracle@dev-oranode-221 ~]$ sqlplus a/a
SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 30 14:38:06 2009
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, Oracle Database Vault
and Real Application Testing options
SQL> insert into imp_test values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining, Oracle Database Vault
and Real Application Testing options
[oracle@dev-oranode-221 ~]$ exp a/a
Export: Release 11.2.0.1.0 - Production on Mon Nov 30 14:38:32 2009
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining, Oracle Database Vault
and Real Application Testing options
Enter array fetch buffer size: 4096 >
Export file: expdat.dmp >
(1)E(ntire database), (2)U(sers), or (3)T(ables): (2)U > 3
Export table data (yes/no): yes >
Compress extents (yes/no): yes >
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8MSWIN1252 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
Table(T) or Partition(T:P) to be exported: (RETURN to quit) > imp_test
. . exporting table IMP_TEST 1 rows exported
Table(T) or Partition(T:P) to be exported: (RETURN to quit) >
in oracle11G, Oracle don't allocate the space segment for newly-created tables. At the same time, Exp just is used to export the data objects allocated to the segments. That is why your failed to exp the data. You can exppor the empty segment object by changing the parameter "deferred_segment_creation" from true to false
[oracle@dev-oranode-221 ~]$ sqlplus a/a
SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 30 14:38:06 2009
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, Oracle Database Vault
and Real Application Testing options
SQL> create table imp_test(id integer primary key);
Table created.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining, Oracle Database Vault
and Real Application Testing options
[oracle@dev-oranode-221 ~]$ exp a/a
Export: Release 11.2.0.1.0 - Production on Mon Nov 30 14:37:17 2009
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining, Oracle Database Vault
and Real Application Testing options
Enter array fetch buffer size: 4096 >
Export file: expdat.dmp >
(1)E(ntire database), (2)U(sers), or (3)T(ables): (2)U > 3
Export table data (yes/no): yes >
Compress extents (yes/no): yes >
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8MSWIN1252 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
Table(T) or Partition(T:P) to be exported: (RETURN to quit) > imp_test
EXP-00011: A.IMP_TEST does not exist
Table(T) or Partition(T:P) to be exported: (RETURN to quit) >
Export terminated successfully with warnings.
[oracle@dev-oranode-221 ~]$ sqlplus a/a
SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 30 14:38:06 2009
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, Oracle Database Vault
and Real Application Testing options
SQL> insert into imp_test values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining, Oracle Database Vault
and Real Application Testing options
[oracle@dev-oranode-221 ~]$ exp a/a
Export: Release 11.2.0.1.0 - Production on Mon Nov 30 14:38:32 2009
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining, Oracle Database Vault
and Real Application Testing options
Enter array fetch buffer size: 4096 >
Export file: expdat.dmp >
(1)E(ntire database), (2)U(sers), or (3)T(ables): (2)U > 3
Export table data (yes/no): yes >
Compress extents (yes/no): yes >
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8MSWIN1252 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
Table(T) or Partition(T:P) to be exported: (RETURN to quit) > imp_test
. . exporting table IMP_TEST 1 rows exported
Table(T) or Partition(T:P) to be exported: (RETURN to quit) >
in oracle11G, Oracle don't allocate the space segment for newly-created tables. At the same time, Exp just is used to export the data objects allocated to the segments. That is why your failed to exp the data. You can exppor the empty segment object by changing the parameter "deferred_segment_creation" from true to false
Inscription à :
Commentaires (Atom)