OraFAQ Forum: Data Guard » Valid objects found invalid in ODS (OLTP & ODS are in SYNC)

Home » Server Options » Data Guard » Valid objects found invalid in ODS (OLTP & ODS are in SYNC) (11g, 11.2.0.3, AIX 7.1.0.0)

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

Valid objects found invalid in ODS (OLTP & ODS are in SYNC) [message #626063]

Mon, 20 October 2014 05:23

ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta

Senior Member

Dear All,

Here, data guard status is "STANDBY".
Objects are found valid in OLTP but are invalid in ODS. Though, both the databases(OLTP & ODS) are in SYNC.

I have advised to sub-ordinate to do below steps to make it Valid.

1) stop the log apply.
2) change the data-guard status to  none (alter database none;)
3) compile manually each objects
4) change the data-guard status to standby (alter database standby;)
5) start the log apply.

Please assist me to know why & how it has happened?

Regards,
Ashish Kumar Mahanta

Report message to a moderator

Re: Valid objects found invalid in ODS (OLTP & ODS are in SYNC) [message #626064 is a reply to message #626063]

Mon, 20 October 2014 05:44

John Watson
Messages: 8804
Registered: January 2010
Location: Global Village

Senior Member

Did your steps 1 through 5 actually fix the problem? Objects could go invalid for various reasons. Most likely unsupported objects or an unrecoverable operation on the primary.

btw, I am assuming that you are talking about a logical standby. What is this "ODS" to which you refer?

Report message to a moderator

Re: Valid objects found invalid in ODS (OLTP & ODS are in SYNC) [message #626075 is a reply to message #626064]

Mon, 20 October 2014 07:40

ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta

Senior Member

Dear John Watson,

Yes, ODS refer to logical standby.
I have checked the invalid objects at ODS and found there are data-related problem. Errors are " too many values", " not enough values".
Now, again big challenges to resolve this problem, as logical standby database is in standby status.

Report message to a moderator

Re: Valid objects found invalid in ODS (OLTP & ODS are in SYNC) [message #626076 is a reply to message #626075]

Mon, 20 October 2014 07:46

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Now, again big challenges to resolve this problem, as logical standby database is in standby status.

Start with a good & complete clone of the Primary DB.

Report message to a moderator

Re: Valid objects found invalid in ODS (OLTP & ODS are in SYNC) [message #626114 is a reply to message #626076]

Tue, 21 October 2014 00:18

ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta

Senior Member

Dear Blackswan,

We have analyzed and came to conclusion that, dml statement was compiled with error. And temp tables are considered and kept on skip list.

execute dbms_logstDBY.SKIP(STMT => 'DML',SCHEMA_NAME => 'INS', OBJECT_NAME =>'GEN_PROP_INFORMATION_TEMP');
execute dbms_logstDBY.SKIP(STMT => 'SCHEMA_DDL',SCHEMA_NAME => 'INS', OBJECT_NAME =>'GEN_PROP_INFORMATION_TEMP');

Even after skip list, procedures are getting Invalid. Errors are -

to many rows

We have un-skipped and skipped it again to check the status of objects. But it's status is still Invalid.

Regards,
Ashish

Report message to a moderator

Re: Valid objects found invalid in ODS (OLTP & ODS are in SYNC) [message #626136 is a reply to message #626114]

Tue, 21 October 2014 04:14

ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta

Senior Member

Dear All,

Below is the Error-

PL/SQL : ORA- 00913: to many values

After analyzing the problem came to know that, one table is in skip list (DDL & DML) and structure changes are there at OLTP.
We are trying to instantiate the tables. Once it has done. We will check the status (whether it is valid or invalid) and update the same.

Regards,
Ashish Kumar Mahanta

Report message to a moderator

Re: Valid objects found invalid in ODS (OLTP & ODS are in SYNC) [message #626140 is a reply to message #626136]

Tue, 21 October 2014 05:13

ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta

Senior Member

Dear All,

Since, version team has changing the structure of temp tables also in primary so we decided to made unskip of DDL stmt.
After instantiate of tables, all objects are becoming valid now.

Thanks for your support & co-operation.

Regards,
Ashish

Report message to a moderator

Previous Topic:	Primary database experiencing lag when disconnect from standby
Next Topic:	Data Guard Broker warning

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Mar 25 02:07:36 CDT 2023