ORA-01555 caused by SQL statement below (SQL ID: 96g93hntrzj

All posts relating to Oracle database administration.

Moderator: Tim...

ORA-01555 caused by SQL statement below (SQL ID: 96g93hntrzj

Postby royalyogi » Tue Apr 23, 2013 4:10 pm

Please help me here to resolve the issue
Getting above ORA-01555 error while running batch job on standby active dataguard version 11.2.0.2 and only standby database will get very slow once batch job will get started, same time primary database will work fine.
Once restarted the standby database, Job will work fine but second day again we will face same issue(ora-01555).
Note: We are getting same error ORA-01555 even after adding space on tbs UNDOTBS1,TEMP and increasing the undo_retention parameter. There no locking,blocking session are exist and only we are getting above. Didn't get any clue from .trc or drc files also.
Important Points:
1.)UNDO size is set to auto extend
2.)Same query will run successfully once restarted the standby database and 2nd day again same issue will reoccur so daily database will get slow once batch job started then we will restart the database and batch will run successfully
3.)Database: Active standby database
Version: 11.2.0.2
OS: Windows 2008 R2 server
4.)Batch job contain only select statements.
5.)Found that some of the rollback segments are offline. is it fine or all segments should be online?

SQL> select owner, segment_name, tablespace_name, status from dba_rollback_segs order by 3;

OWNER SEGMENT_NAME TABLESPACE_NAME STATUS
* ------------------------------ ------------------------------ ----------------

SYS SYSTEM SYSTEM ONLINE
PUBLIC _SYSSMU28_3280112850$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU27_3488832319$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU26_1877694247$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU25_1029469395$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU24_3706513972$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU23_862303936$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU22_1603039711$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU21_1691925291$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU20_593055436$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU19_886535550$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU18_3158585725$ UNDOTBS1 ONLINE
PUBLIC _SYSSMU17_3073215262$ UNDOTBS1 ONLINE
PUBLIC _SYSSMU16_1138559631$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU15_4141089397$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU14_3679068455$ UNDOTBS1 ONLINE
PUBLIC _SYSSMU13_3902595986$ UNDOTBS1 ONLINE
PUBLIC _SYSSMU12_2632985044$ UNDOTBS1 ONLINE
PUBLIC _SYSSMU11_2153627416$ UNDOTBS1 ONLINE
PUBLIC _SYSSMU10_1599800294$ UNDOTBS1 ONLINE
PUBLIC _SYSSMU9_4125436319$ UNDOTBS1 ONLINE
PUBLIC _SYSSMU8_3640122478$ UNDOTBS1 ONLINE
royalyogi
Senior Member
 
Posts: 167
Joined: Tue Apr 23, 2013 3:59 pm

Re: ORA-01555 caused by SQL statement below (SQL ID: 96g93hn

Postby Tim... » Tue Apr 23, 2013 6:15 pm

Hi.

The undo on your standby will be overwritten at the same time as it is overwritten on your primary. Remember, undo is protected by redo, just like regular data. You must size the undo of both the primary and standby sufficiently to allow queries on the standby to complete before the undo on the primary is overwritten, thereby causing the undo on the standby to be overwritten.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17952
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: ORA-01555 caused by SQL statement below (SQL ID: 96g93hn

Postby royalyogi » Thu Apr 25, 2013 7:06 pm

Great. Now above issue got resolved. Thanks a lot
royalyogi
Senior Member
 
Posts: 167
Joined: Tue Apr 23, 2013 3:59 pm

Re: ORA-01555 caused by SQL statement below (SQL ID: 96g93hn

Postby Tim... » Fri Apr 26, 2013 7:45 am

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17952
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle Database Administration

Who is online

Users browsing this forum: No registered users and 1 guest