OraFAQ Forum: Backup & Recovery » indexes of sysman

Home » RDBMS Server » Backup & Recovery » indexes of sysman (Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

indexes of sysman [message #445674]

Wed, 03 March 2010 05:48

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

Report message to a moderator

Re: indexes of sysman [message #445675 is a reply to message #445674]

Wed, 03 March 2010 05:50

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You have to drop or disable the associated constraint.

Regards
Michel

Report message to a moderator

Re: indexes of sysman [message #445679 is a reply to message #445674]

Wed, 03 March 2010 06:06

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

Report message to a moderator

Re: indexes of sysman [message #445690 is a reply to message #445679]

Wed, 03 March 2010 06:36

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: indexes of sysman [message #446332 is a reply to message #445690]

Sun, 07 March 2010 22:33

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

Report message to a moderator

Re: indexes of sysman [message #446340 is a reply to message #446332]

Sun, 07 March 2010 22:54

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>But still the issue is there.

Which object(s) contain corrupt blocks?

Report message to a moderator

Re: indexes of sysman [message #446342 is a reply to message #445674]

Sun, 07 March 2010 23:02

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

Report message to a moderator

Re: indexes of sysman [message #446346 is a reply to message #446342]

Sun, 07 March 2010 23:22

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Copy and paste what you have.

Regards
Michel

Report message to a moderator

Re: indexes of sysman [message #446348 is a reply to message #445674]

Sun, 07 March 2010 23:25

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
/

Report message to a moderator

Re: indexes of sysman [message #446362 is a reply to message #445674]

Sun, 07 March 2010 23:51

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

Report message to a moderator

Re: indexes of sysman [message #446364 is a reply to message #446362]

Sun, 07 March 2010 23:55

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

Report message to a moderator

Re: indexes of sysman [message #446367 is a reply to message #445674]

Mon, 08 March 2010 00:12

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

Report message to a moderator

Re: indexes of sysman [message #585301 is a reply to message #446367]

Thu, 23 May 2013 10:43

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.

Report message to a moderator

Re: indexes of sysman [message #585305 is a reply to message #585301]

Thu, 23 May 2013 11:34

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Did you try the note?
Are you in the same version than OP?

Regards
Michel

Report message to a moderator

Re: indexes of sysman [message #585307 is a reply to message #585305]

Thu, 23 May 2013 11:42

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?

Report message to a moderator

Re: indexes of sysman [message #585313 is a reply to message #585307]

Thu, 23 May 2013 12:23

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Have a look at Note "Bug 12989816" and read the notes pointed by this one.

Regards
Michel

Report message to a moderator

Re: indexes of sysman [message #585325 is a reply to message #585313]

Thu, 23 May 2013 12:48

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], [], [],
[], [], [], [], [], [], []

Report message to a moderator

Re: indexes of sysman [message #585329 is a reply to message #585325]

Thu, 23 May 2013 12:54

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Nov 26 22:00:09 CST 2024