SQL & PL/SQL :: ORA-01555 Snapshot Too Old?

Apr 29, 2010

I am getting the ORA-01555 Snapshot Too Old error while excecuting my procedure (which is running in OLTP system). My procedure contains one table which master table of my DB which holds 1.2 GB of data. The proc is using the MERGE statement which is inserting or updating the data in the master table, while the condition of MERGE is using the subquery in which 2 DBlink table is used (in which one table is updating quite frequently). The proc. is executing in the interval of 6 hrs i.e 4 times a day and its is scheduled in job. find the proc. as an attachment.

View 3 Replies


ADVERTISEMENT

ORA-01555 / Snapshot Too Old Error

Jan 12, 2012

I have a problem regarding with Oracle Flashback Query.if I'm doing flashback a database two hours before,I'm receiving ORA-01555: snapshot too old error.My question is:How to able to extend the based on the hour or day?

View 1 Replies View Related

Server Utilities :: ORA-01555 - Snapshot Too Old?

Nov 3, 2010

We are taking full logical backup ( export) in freeze hours( no user login). But prompting error "ORA-01555: snapshot too old: rollback segment number 7 with name "_SYSSMU7$" too small".

View 6 Replies View Related

Expdp Slow With Error ORA-01555 - Snapshot Too Old On 11g?

May 15, 2013

data pump export is very slow. For 50GB export has taken more than 24Hrs with one below error:

Database Version:11.2.0.2.0
OS: Windows server 2008 r2
Increased 10GB RAM and CPU 6 to 8 then also same issue

Error:
ORA-31693: Table data object "BNCSDB"."MS_DATA_PTORE" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01555: snapshot too old: rollback segment number 20 with name "_SYSSMU20_4037596720$" too small

Export log:
Export: Release 11.2.0.2.0 - Production on Tue May 14 20:03:25 2013

Copyright � 1982, 2009, Oracle and/or its affiliates. All rights reserved.
;;;
Connected to: Oracle Database 11g Release 11.2.0.2.0 - 64bit Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********@orcl dumpfile=BCSDB04_19.dmp logfile=BCSDB04_19.log

[code]...

View 12 Replies View Related

Server Administration :: How To Check Error ORA-01555 / Snapshot Too Old

Dec 23, 2011

i am getting this error during expdp ORA-01555 -snapshot too old

1. i increased undo_retention i.e 50000

2. i execute expdp during late night when almost zero user is online so no commit from any other session.

3. we have a table having BLOB data type field in a table which is 500 GB in size.complete DB size is 700 GB

where and why this error exists.if corruption of BLOB so how to check that etc. the same error is occurring in three of our data centres during expdp.

View 4 Replies View Related

Server Administration :: Query Fails With Error ORA-01555 / Snapshot Too Old

Jun 23, 2011

We are running a query in one of our databases using the database the link. The query fails in the middle with "ORA-01555: snapshot too old". Not sure, about the database which this error message points to? Will it be the database we have logged in or it is the database where db link points ?

View 7 Replies View Related

12008 - ORA-12008 - Error In Materialized View Refresh Path ORA-01555 - Snapshot Too Old?

Jul 21, 2009

I am having problems with my procedure which is refreshing materialized views. This is the error i am getting : -12008: ORA-12008: error in materialized view refresh path
ORA-01555: snapshot too old: rollback segment number 14...

I have two database with the same procedure and the same objects. They run at the same time, however, there are times when i get the error on the other database while the other database runs the procedure without any error.

When i checked the net, they say that this error is caused by data that is old, so the solution is to make the source a predecessor of my procedure/job. But what i am thinking is that how was it possible for the other database to run it completely even if it is not yet linked as the successor of the source?

View 8 Replies View Related

How To Tackle ORA-01555 Error

Feb 5, 2007

how to tackle the error ORA-01555: Snapshot too old: rollback segment number XX with name NNN too small.I am using Oracle 8i .ways to tackle it without extending the rollback segment

View 2 Replies View Related

Server Administration :: Rollback Segment Too Small ORA-01555

Jan 19, 2012

One of the users received the error
ora-01555: rollback segment too small

I have read about the error and saw that it is caused by transactions made upon same data, filling to maximum one of the UNDOTBS rollback segments.It happens only once in a while, each time on a different Rollback Segment.

I've read that i should do a few things to enlarge those segments, such as bring a 1. segment offline, 2. drop it and then 3. create it with a bigger size & possibly a bigger extent setting

i've also read that those actions aren't relevant if you have the parameter UNDO_MANAGEMENT set on AUTO, which is actually the case.is that why when i execute ALTER ROLLBACK SEGMENT RB_SEG_NAME_11$ OFFLINE;

1. How do i solve my issue with the Rollback segment being too small to contain the snapshot with the requested SCN?

2. what does the parameter UNDO_MANAGEMENT mean? should i change it, in order to adjust my Rollback Segments attributes, to prevent my error of re-occurring?

View 10 Replies View Related

Performance Tuning :: ORA-01555 Required Old Image Is Not In Undo

Sep 22, 2011

I am getting the below error in alert log file,when my application calling a procedure.

ORA-01555 caused by SQL statement below (Query Duration=1576 sec, SCN: 0x09a2.5dda3165):
Fri Sep 16 16:33:40 2011
UPDATE SSPT_NETWORK_DETAILS SET INCLUDE_OFFERS = 'Yes' WHERE SESS_ID = SESS_ID

There is no ROLLBACK statement in my procedure. As per my understanding, the ORA-1555 error will occur,

1. The required old image is not in the undo,when we rollback the trasaction.
2. the select query may face this error because of delayed block cleanout concept.

But I don't know why this update statement causing this 1555 error?

View 6 Replies View Related

Server Administration :: ORA-01555 / Unable To Find Sql_id

Jan 25, 2012

I am getting below ORA-01555 error in alert log everyday.

ORA-01555 caused by SQL statement below (SCN: 0x09ad.86a4562a):
Sat Jan 21 08:39:45 2012
SELECT (NVL(MAX(BLOCK_ID + BLOCKS ),0) * :b1 ) / 1024
FROM DBA_EXTENTS WHERE TABLESPACE_NAME = :b2 AND FILE_ID = :b3

I cannot able to find the sql_id here. So how can I find from which process or session this query is firing? before increasing the undo size, I need to analyze as why it is occuring?

undo_management string MANUAL
undo_retention integer 900
undo_suppress_errors boolean FALSE
undo_tablespace string

View 9 Replies View Related

Performance Tuning :: ORA-01555 - Increase Undo Size Or Retention?

Apr 22, 2013

I have got the following error yesterday

ORA-01555 caused by SQL statement below (SQL ID: fdxcyoin67ty8t, Query Duration=380128 sec, SCN: 0x0229.ff00afd0):

following are the existing settings

SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 96000
undo_tablespace string undo

[code]....

following are the details from v$undostat

select begin_time, end_time, undotsn, undoblks, maxquerylen, maxqueryid, activeblks, unexpiredblks, expiredblks, tuned_undoretention from v$undostat
where trunc(begin_time)=trunc(sysdate)-1 order by begin_time;
BEGIN_TIME END_TIME UNDOTSN UNDOBLKS MAXQUERYLEN MAXQUERYID ACTIVEBLKS UNEXPIREDBLKS EXPIREDBLKS TUNED_UNDORETENTION
-------------- -------------- ---------- ---------- ----------- ------------- ---------- ------------- ----------- -------------------
21-04-13 00:08 21-04-13 00:18 1 12733 378446 duqnawh32hp4u 91152 7068448 225440 345600
21-04-13 00:18 21-04-13 00:28 1 8951 379047 duqnawh32hp4u 99344 7072800 225440 345600
21-04-13 00:28 21-04-13 00:38 1 14073 379650 duqnawh32hp4u 90128 7075872 234656 345600

[code]....

Following are the details in AWR report (00:00 til 01:00 of 21-Apr-2013) .... not thet the error was produced at 00:42

Undo Segment Summary DB/Inst: DBCPY/dbcpy01 Snaps: 18853-18854
-> Min/Max TR (mins) - Min and Max Tuned Retention (minutes)
-> STO - Snapshot Too Old count, OOS - Out of Space count
-> Undo segment block stats:
-> uS - unexpired Stolen, your - unexpired Released, uU - unexpired reUsed

[code]....

Undo Advisor information taken 'now' is as following

SQL> select dbms_undo_adv.longest_query(sysdate-2,sysdate) from dual;
DBMS_UNDO_ADV.LONGEST_QUERY(SYSDATE-2,SYSDATE)
----------------------------------------------
379650
SQL> select dbms_undo_adv.required_retention from dual;

[code]....

In above situation what should be my first choice (assuming increasing space is not an issue) - increase undo tablespace or increase undo retention?

If latter is the choice then what should be the value? Because as I understand present 96000 value is taken as lower limit and because of auto tuning the actual value (TUNED_UNDORETENTION) being used was 345600 In that case shall I set it to something > max(maxquerylen) i.e 379,650 + X?Or I shall increase the undo tablespace size?

From Undo Advisor output it looks to me that even if I increase the undo retention to 379650 current undo size will be able to support it (may be at the expense of DMLs)Is that right?

View 13 Replies View Related

Performance Tuning :: ORA-01555 - No Unexpired Or Expired Blocks Were Over Written?

May 10, 2011

I have encountered ORA-01555 and trying to find what caused the issue.

#UNDO parameters
undo_management AUTO
undo_retention 0
undo_tablespace UNDOTBS1
set pagesize 25
set linesize 120
select inst_id,tuned_undoretention,to_char(begin_time,'MM/DD/YYYY HH24:MI') begin_time,

[code]...

at the end you can see there is one occurance of ORA-555, but no Unexpired or expired blocks were over written .why Oracle didnt try to use them ?

View 10 Replies View Related

SQL & PL/SQL :: Dropping A Snapshot

Apr 13, 2008

I am unable to drop a snapshot , i tried even from sys, it is giving the following error,

drop snapshot adm.dup_resource_status
9:46:29 ORA-08103: object no longer exists

but, when i try to create a new snapshot with the same name

CREATE MATERIALIZED VIEW ADM.DUP_RESOURCE_STATUS
..............
I get
ORA-12006: a materialized view with the same user.name already exists.

View 31 Replies View Related

Controlfile Snapshot In RMAN

Aug 29, 2012

I wish to know about Controlfile Snapshot in RMAN. Is this equalent to controlfile backup?

View 4 Replies View Related

Replication :: Index On A Snapshot In 8i

Feb 2, 2008

Can i create an index on a snapshot... to increase the performance of searching.... in Oracle 8i.... provide with syntax...

Or else tell me how to increase the speed of a query on Snapshot.

View 1 Replies View Related

Getting Error (snapshot Too Old) - Restructure Code?

Oct 8, 2013

we are getting "snapshot too old" error when we executing a procedure. The error details are as below:

"ORA-01555: snapshot too old: rollback segment number 208 with .."

The code framework is below. basically we are having 3 cursor to select data and the base tables seems to update frequently. hence we understood that this error might come. if any other better way to restructure the below code framework.

Create or replace procedure proc1 as
CURSOR C1 is
select a from tab1
CURSOR C2 (p1 VARCHAR2)
is
select * from tab2;

[code]...

Since DBA not willing to change UNDO_RETENTION and other UNDO* parameters, we need to work around with restructure the code.

View 2 Replies View Related

Generate AWR Report Between Two Timings (Not Snapshot ID)

Aug 12, 2013

I have a requirement to create a generic script to generate awr report on the basis of two timings . We are planning to do load test on multiple server. The user will key in the start time and the end time of the load test and the script should round off the user entered time to the nearest snapshot id and generate a awr report. I have tried using the dba_hist_snapshot table fields begin_interval_time and end_interval_time column. However it failed.

The servers are running on 11g as well as 10g and few are in RAC and few single instances.

/************************************
* INPUT PARAMETERS
************************************/
UNDEFINE FROM_TIME
UNDEFINE TO_TIME

[Code]....

View 4 Replies View Related

Performance Tuning :: No Snapshot Available For AWR Report

Feb 1, 2011

I am trying to generate AWR report for database observation. But I am not getting any snapshot listed there. below is the output of my awrrpt.sql

SQL> @?/rdbms/admin/awrrpt.sql

Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
1140984076 AFCCV 1 afccv

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?

Enter 'html' for an HTML report, or 'text' for plain text Defaults to 'html'

Enter value for report_type: html

Type Specified: html

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 1140984076 1 AFCCV afccv SERVICEDB1

Using 1140984076 for database Id
Using 1 for instance number

Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent (n) days of snapshots being listed. Pressing <return> without specifying a number lists all completed snapshots.

Enter value for num_days: 3

Listing the last 3 days of Completed Snapshots

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap:

View 8 Replies View Related

SQL & PL/SQL :: Main Difference In Snapshot And View

May 3, 2010

I want basics of snapshot...creation,deletion,check...etc and what main difference in snapshot and view..?

View 2 Replies View Related

Number Of Waits Reaches To 100 In 8-hour Snapshot

Oct 8, 2013

in the awr report I saw some segments in Segments by ITL Waits section. Number of waits reaches to 100 in 8-hour snapshot. Is it small/big number? Should I consider increase INITRANS or there is nothing to be worried because value 80-100 are not too high?

View 3 Replies View Related

Replication :: Refresh Snapshot Data Without Logging

Jan 20, 2009

Materialized views are normally used for summarized data access.

CREATE MATERIALIZED VIEW mv_snapshot_A
REFRESH FAST START WITH SYSDATE
NEXT SYSDATE + 20/1440
WITH PRIMARY KEY
AS SELECT * FROM A;

This does not seem to be the case here as the materialized view seems to be just a full select. The overhead of the snapshot logs are concerning for this core table. Can we turn off logging in 10g ? the materialized view is defined as fast refresh/ build immediate .

The main requirement here is to keep the snapshot every 15 minutes so that the users can see the updated information ( the flow of data from one location to other).

User get the location wise count of data and can go further in details like in which location wise system wise data count. As the base table is volatile the materialized view is used so that the moment the user clicks for location wise details the data is static for 15 min and user don't get confused.

View 31 Replies View Related

Replication :: Difference Between Materialized View And Snapshot

Jul 7, 2008

what is the difference between materialized view and snapshot ?

View 1 Replies View Related

Server Utilities :: Snapshot Error During Export

Jul 1, 2010

i am getting "snapshot too old " error while take in export backup of a database(with CONSISTENT=y), it actually runs for 3 hours.

it always fails for table1 with snapshot error

i pulled the awr for that 3 hours, to see any long running SQL hitting table1 . and i found 3 , Two SELECT and one INSERT.

I assume it is INSERT not letting me take a consistent export backup of Table1 .

View 9 Replies View Related

RMAN :: Restore / Recovery From Snapshot Faster?

Aug 8, 2012

Version : 11.2 on Solaris, AIX

All our production DBs are backed up by some Hitachi tool (apparently it is called ShadowImage) . Apparently they put all the tablespaces in Backup mode and 'take a snap' . Our BAU team says this is faster.

On an event of DB Crash Is it easier to restore, recover from these 'snapshots' ? Are these snapshots much faster than RMAN ?

View 3 Replies View Related

Replication :: Materialized Views Refresh In Snapshot-group?

Mar 24, 2010

How to refresh the dependent materialized views in a snapshot group. It refreshes in the order we add the materialized views to snapshot group?

Here below is the scenario.

MATVW_A
CREATE MATERIALIZED VIEW MATVW_A BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS SELECT * FROM EMP;

MATVW_B
CREATE MATERIALIZED VIEW MATVW_B BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS SELECT * FROM MATVW_A;

MATVW_C
CREATE MATERIALIZED VIEW MATVW_C BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS SELECT * FROM MATVW_B;

I created a snapshotgroup SNGRP with all the above materialized views.

DBMS_REFRESH.MAKE(name => 'SNGRP',
tab => 'MATVW_A,MATVW_B,MATVW_C',
next_date => TRUNC(sysdate) + 1,
interval => 'SYSDATE + 1');

The order of refreshing the materialized views would be like first materialized view MATVW_A should get refresh then after MATVW_B,MATVW_C.

As they are dependent views.Is there anyway where we can mention the order of refreshing materialized views in snapshot group.

View 2 Replies View Related

Performance Tuning :: Snapshot Skipped In Between Scheduled Time?

Sep 11, 2013

Yesterday, there were performance issue at server. So today, when i am generating report for that particular period, found snapshot id sequence is serially but with skipped hourly timed. Instead of generating report at 15:30, it generated at 16:30.

Enter value for num_days: 2

Listing the last 2 days of Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
tagidev TAGIDEV 2857 10 Sep 2013 00:30 1
2858 10 Sep 2013 01:30 1
2859 10 Sep 2013 02:30 1
2860 10 Sep 2013 03:30 1
2861 10 Sep 2013 04:30 1
2862 10 Sep 2013 05:31 1

[code]....

Below are the details at alert log -

Tue Sep 10 14:28:20 2013
Thread 1 cannot allocate new log, sequence 7029
Checkpoint not complete
Current log# 2 seq# 7028 mem# 0: E:APPORACLEORADATATAGIDEVREDO02.LOG
Thread 1 advanced to log sequence 7029 (LGWR switch)

[code]....

1) why snap didn't started at 15:30?

2) since database just started at the scheduled time of AWR snap time. But generated at 16:32 instead of 16:30, though last services "SMCO" is started at 16:42. How it snap id generated for this particular time?

3) what does "kewastUnPackStats(): bad magic 1 (0x000000001B3CE48D, 0)" mean?

View 7 Replies View Related

Server Administration :: Remove Synonym STATS$SNAPSHOT-ID?

Sep 11, 2007

I've got to collect statistics by DBMS_STAT. For the first time, the spcreate.sql is to be called, however, it returned one error following:

If this script is automatically called from spcreate (which is the supported method), all STATSPACK segments will be created in the PERFSTAT user's default tablespace.

Using perfstat tablespace to store Statspack objects

... Creating STATS$SNAPSHOT_ID Sequence Sequence created.

create public synonym STATS$SNAPSHOT_ID for STATS$SNAPSHOT_ID
*
ERROR at line 1:
ORA-00955: name is already used by an existing object Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options

And then, I checked it again with its owner:

SQL> select owner, synonym_name
2 from dba_synonyms
3 where synonym_name='STATS$SNAPSHOT_ID';

OWNER SYNONYM_NAME
------------------------------ ------------------------------
PUBLIC STATS$SNAPSHOT_ID

Then, I checked again after dropping schema PERFSTAT:

SQL> conn /as sysdba
Connected.
SQL>
SQL> select username, account_status

[code]...

What's the STATS@SNAPSHOT_ID synonym? Can I remove it from PUBLIC owner and recreate?

View 13 Replies View Related

Backup & Recovery :: Interoperability Between RMAN And VSS Snapshot Backups?

Jun 8, 2012

is it possible to have a vss snapshot backup on the top of an RMAN backup. Because the link [URL] says that we can have an incremental level 1 backup on the vss snapshot backup.

So I need to know if interoperability between RMAN and VSS exists or not.

Also is it possible to have mixed backups meaning vss snapshot backups and rman backups. Whenever we take snapshot backup, is it updated in the control file?

View 2 Replies View Related

Replication :: MV Views To Snapshot Data Pros And Cons

Nov 4, 2012

I have 3 reporting tables with 2.2 million records each being rebuilt nightly. The data is used online 24/7 by users and thus, snapshot tables are being built from the refreshed reporting tables. The current method to do this:

delete from snapshot table;
insert into snapshot table (select * from report table);
<repeat for other 2 tables>
commit;

This seems to me to be resource intense on the system even though the table is defined with nologging option.

Is it better to create a MV (select only with refresh complete on demand)? The query is very simple without joins so it at first seems like overkill. However, I am also seeing that dbms_mview.refresh allows for an atomic option. Thus, if 1 of the 3 MVs fails during refresh all 3 rollback, which is a nice feature.

Are there better ways to replicate a snapshot table that I've missed? Is a delete and insert strategy a bad idea?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved