Home » RDBMS Server » Backup & Recovery » indexes of sysman (Oracle 10g)
indexes of sysman [message #445674] Wed, 03 March 2010 05:48 Go to next message
gir_sat
Messages: 64
Registered: October 2008
Location: Mumbai
Member

Hi all,

I am facing problem of logical block curruption.
SQL> select * from v$database_block_corruption;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
3 61454 1 1417157166 LOGICAL
3 31256 1 1417011117 LOGICAL

OWNER CONSTRAINT_NAME C TABLE_NAME
------------------------------ ------------------------------ - ------------------------------
SYSMAN MGMT_METRICS_RAW_PK P MGMT_METRICS_RAW

Can i recreate the SYSMAN schema index by dropping this one?

Thanks in advancd

Re: indexes of sysman [message #445675 is a reply to message #445674] Wed, 03 March 2010 05:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to drop or disable the associated constraint.

Regards
Michel
Re: indexes of sysman [message #445679 is a reply to message #445674] Wed, 03 March 2010 06:06 Go to previous messageGo to next message
gir_sat
Messages: 64
Registered: October 2008
Location: Mumbai
Member

Hi,

Thanks for the guidance.
But is it ok, will it not cause any problem to my database?
As it is on production, whats why worring so much.
Becz of this, our backup (expdp/rman) was also failed.

Thanks again
Re: indexes of sysman [message #445690 is a reply to message #445679] Wed, 03 March 2010 06:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ask for a maintenance window (to minimize the workload), stop db console and grid agent (if any), stop jobs (set job_queue_processes to 0), then you should not have any problem.

Regards
Michel
Re: indexes of sysman [message #446332 is a reply to message #445690] Sun, 07 March 2010 22:33 Go to previous messageGo to next message
gir_sat
Messages: 64
Registered: October 2008
Location: Mumbai
Member

Hi,

I have tried to remove the block curroption from the sysman.

I have recreated the sysman.

But still the issue is there.



Can i take the exp/imp for the sysman schema ?

Thanks
Re: indexes of sysman [message #446340 is a reply to message #446332] Sun, 07 March 2010 22:54 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>But still the issue is there.


Which object(s) contain corrupt blocks?
Re: indexes of sysman [message #446342 is a reply to message #445674] Sun, 07 March 2010 23:02 Go to previous messageGo to next message
gir_sat
Messages: 64
Registered: October 2008
Location: Mumbai
Member

hi,

Thanks again for prompt and quick reply

Prevously our expdp was not happeining, it was giving as block curruption occured on the sysman user and gives the filename and the block no.

But now the expdp is completing , but gives one error saying the same fact.

So how should now i appracoh for the issue.

I have also tried metalink ID : 978718.1
But not worrking this also

Thanks
Re: indexes of sysman [message #446346 is a reply to message #446342] Sun, 07 March 2010 23:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Copy and paste what you have.

Regards
Michel
Re: indexes of sysman [message #446348 is a reply to message #445674] Sun, 07 March 2010 23:25 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
SELECT segment_name
FROM dba_extents
WHERE file_id =3
and 31256 between BLOCK_ID and block_id + BLOCKS
/
SELECT segment_name
FROM dba_extents
WHERE file_id =3
and 61454 between BLOCK_ID and block_id + BLOCKS
/
Re: indexes of sysman [message #446362 is a reply to message #445674] Sun, 07 March 2010 23:51 Go to previous messageGo to next message
gir_sat
Messages: 64
Registered: October 2008
Location: Mumbai
Member

Hi,

Here is the asked output:

SEGMENT_NAME
--------------------------------------------------------------------------------
PK_CRED_TYPE_COL_VALS

SQL> SELECT segment_name
2 FROM dba_extents
3 WHERE file_id =3
4 and 61454 between BLOCK_ID and block_id + BLOCKS
5 /

SEGMENT_NAME
--------------------------------------------------------------------------------
MGMT_METRICS_RAW_PK

SQL>


Thanks
Re: indexes of sysman [message #446364 is a reply to message #446362] Sun, 07 March 2010 23:55 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Obtain DDL for PK_CRED_TYPE_COL_VALS & MGMT_METRICS_RAW_PK, then DROP them & make them again
Re: indexes of sysman [message #446367 is a reply to message #445674] Mon, 08 March 2010 00:12 Go to previous messageGo to next message
gir_sat
Messages: 64
Registered: October 2008
Location: Mumbai
Member

Hi, we have tried that one, but not able to drop it.

We also followed the note id : 978718.1.

Is any other solution for it,

Thanks
Re: indexes of sysman [message #585301 is a reply to message #446367] Thu, 23 May 2013 10:43 Go to previous messageGo to next message
dmrojasb
Messages: 3
Registered: May 2013
Location: Ecuador
Junior Member
Hi gir sat
I have the same problem. How do you resolved this? Please help me.
Re: indexes of sysman [message #585305 is a reply to message #585301] Thu, 23 May 2013 11:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you try the note?
Are you in the same version than OP?

Regards
Michel
Re: indexes of sysman [message #585307 is a reply to message #585305] Thu, 23 May 2013 11:42 Go to previous messageGo to next message
dmrojasb
Messages: 3
Registered: May 2013
Location: Ecuador
Junior Member
Hi, Michel.

I have 11.1.0.7, and I have the problem in MGMT_METRICS_RAW_PK. Because MGMT_METRICS_RAW ia an IOT a cannot drop the index. I tried "alter table MGMT_METRICS_RAW move online tablespace sysaux overflow tablespace tbs_gema_idx2;" but I have this error:
ORA-01578: ORACLE data block corrupted (file # 2, block # 69425)
ORA-01110: data file 2: '/home/oracle/app/oracle/oradata/dbgema/sysaux01.dbf'

Any idea?
Re: indexes of sysman [message #585313 is a reply to message #585307] Thu, 23 May 2013 12:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at Note "Bug 12989816" and read the notes pointed by this one.

Regards
Michel
Re: indexes of sysman [message #585325 is a reply to message #585313] Thu, 23 May 2013 12:48 Go to previous messageGo to next message
dmrojasb
Messages: 3
Registered: May 2013
Location: Ecuador
Junior Member
Hi Michel,

I solved this issue following this steps:

1. Open the DB in Restrict Mode.
2. Run following SQL on database as SYSMAN user to recreate MGMT_METRICS_RAW table.
-1. select object_name, object_type from user_objects where status='INVALID';
-2. DROP TABLE MGMT_METRICS_RAW
-3. CREATE TABLE MGMT_METRICS_RAW
(target_guid RAW(16) NOT NULL,
metric_guid RAW(16) NOT NULL,
key_value VARCHAR2(256) DEFAULT ' ',
collection_timestamp DATE NOT NULL,
value NUMBER DEFAULT NULL,
string_value VARCHAR2(4000) DEFAULT NULL,CONSTRAINT mgmt_metrics_raw_pk
PRIMARY KEY (target_guid, metric_guid, key_value, collection_timestamp))
ORGANIZATION INDEX PCTFREE 0 COMPRESS 3 INITRANS 4
OVERFLOW INCLUDING value
MONITORING;

3. Run an analyze on this table and its index.
SQL> analyze table MGMT_METRICS_RAW validate structure cascade;

4. Shutdown and open the database.

But i have another problem when i do a select on some tables using index or when i try to rebuild some indexes. The error is:
ERROR at line 1:
ORA-00600: internal error code, arguments: [25027], [49], [12595041], [], [],
[], [], [], [], [], [], []
Re: indexes of sysman [message #585329 is a reply to message #585325] Thu, 23 May 2013 12:54 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-00600/ORA-07445/ORA-03113 = Oracle bug => search on Metalink/MOS and/or call Oracle support
Have a look at alert.log and trace files.
You can also read this article: Troubleshooting Internal Errors.

Regards
Michel
Previous Topic: ORA-01139: RESETLOGS option only valid after an incomplete database recovery
Next Topic: ORA-01115: IO error reading block from file 11 (block # 118736)
Goto Forum:
  


Current Time: Thu Mar 28 23:42:09 CDT 2024