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)
Valid objects found invalid in ODS (OLTP & ODS are in SYNC) [message #626063] Mon, 20 October 2014 05:23 Go to next message
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
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 Go to previous messageGo to next message
John Watson
Messages: 8922
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?
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 Go to previous messageGo to next message
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.
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 Go to previous messageGo to next message
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.
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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
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

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 Go to previous message
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
Previous Topic: Primary database experiencing lag when disconnect from standby
Next Topic: Data Guard Broker warning
Goto Forum:
  


Current Time: Fri Mar 29 10:23:42 CDT 2024