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


ADVERTISEMENT

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

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 View Related

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

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

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

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

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

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

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

Server Utilities :: Finding Versions Of Exp And Imp Utilities Of Database Server?

Feb 23, 2012

how to find the versions of exp and imp utilities of database server from windows command prompt?

Note: Currently i have 10.2.0.10 oracle software installed on my local machine.

View 4 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 Utilities :: Do EXPDP Utilities Does Backup At Block Level As What RMAN Is Doing

May 29, 2013

I have one doubt on Expdp & RMAN. Do EXPDP utilities does backup at block level as what RMAN is doing? Which one is faster, expdp or RMAN?

View 16 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

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

Server Utilities :: Intermediate COMMITS When Using Sqlldr Or Imp Utilities

Oct 29, 2013

I want to load lakhs of records into a table. My problem is when after loading the ¼ of records my process is abend due to the size of my rollback segment area. I don't have an option to increase it. So, Is there any way to go for intermediate commits when I am using the imp or sqlldr utilities to load the entire data without abend?

View 2 Replies View Related

Server Utilities :: Netca And Netmgr Utilities?

Feb 20, 2012

I am familiar with tool Netca. However there is one more utility exist for the same functionatlty which is netmgrI checked with many DBAs for the exact difference, however I did not get the best answer from them. I also have checked in google but not exactly got the difference. list the exact difference between those 2 tools (netca, netmgr)

View 1 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

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







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