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 .
We are getting the below errors while migrating partitioned tables using expdp.
The source and target databases are both running on 10.2.0.5 and the main thing is source database doesn't have any active sessions. This is a clone of a Prod Database and no one is accessing it.
ORA-31693: Table data object "DPMMGR"."WHSE_CTNR_EVNT_W":"MSG_PRCS_N"."MSG_PRCS_N_DC556" 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 31 with name "_SYSSMU31$" too small
ORA-31693: Table data object "DPMMGR"."RLTM_PRDCT_LOG":"RPL_20120814" 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 14 with name "_SYSSMU14$" too small
Undo Tablespace has enough space but still the expdp is failing.
SQL>/
TABLESPACE Totalspace(MB) Used Space(MB) Freespace(MB) % Used % Free
--------------- --------------- -------------- ------------- ---------- ----------
UNDO01 145096 115338 29758 79.49 20.51
SQL> show parameter undo
[code]....
i am having a problem when trying to export my DB,i could run an import fine,i have ran the catalog.sql,catproc.sql,catexp.sql and utlrp.sql again.Is it because the client and DB are different?How can i solve this problem
exp usr/pass file=exp_full.dmp log=exp_full.log full=y consistent=y
Export: Release 8.1.7.0.0 - Production on Wed Nov 28 13:40:04 2007
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
[code]...
when i try to export schema using expdp i got error Like
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 6.25 MB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
[code]...
HOw to solve this issue?
I have an oracle instance running on solaris 5.8 oracle 10.1.0.3 and when I export the database I get a bunch of error messages which I dont know why?
. exporting private type synonyms
. exporting object type definitions
. exporting system procedural objects and actions
EXP-00008: ORACLE error 6550 encountered
ORA-06550: line 1, column 13:
[code]........
I have a ORACLE 10g(10.2.0.1.0) database that I am running on windows. I have export backups batch file running every day through windows scheduler. I would like to know If there is some way that sends me an email after the backup is finished and also send me if it does not finish with error.
I have successfully done this om my SQL Server machine but don't know for ORACLE as I have not purchased it and have license
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 Relatedi 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.
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 RelatedI am trying to export schemas from 10g to 11g. The NLS_CHARACTERSET for 10g is WE8ISO8859P1 and the NLS_CHARACTERSET for 11g is WE8MSWIN1252. Is it fine or do I need to change the character set, so that I will be able to successfully do the export/import?
View -1 Replies View RelatedI 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 RelatedI would like to export specific tables(not entire schema) including metadata. I am using a parameter file for expdp.
Tables=emp,dept
Does this also include all metadata or should i also add the below Include in the parfile ?
INCLUDE =Indexes,Sequences,Procedures,Views
I was asked to do export/import of some schemas from 10g(linux) to 11g(AIX) using original expor/import method. I did not consider the character set and started doing export and import. while exporting, I get questionable statistics error in export log file. In the import log, I see the error like CREATE DATABASE LINK "xxxxxxxxxxxxx" CONNECT TO "xxxx" IDENTIFIED BY...
What can be done with these errors?
i need to export master data in excel sheets to our database and we use toad too. How i can export the data with the use of macros in excel. how i can export data from excel to oracle.
View 6 Replies View RelatedWhats is the usage of log file in Import/export .If i use following command ,it exports successfully
exp scott/tiger file=check.dmp log=empc.log tables=emp
and if i remove .log from here it will also export successfully So why do we use .log in import/export.
how can i monitor the export and import job and how increase the export and import job performance.
can i monitor the export and import job by checking the log and dump file created by export and import and can its performance increase by configure parallism. m i right or not?
I had specified the below:
Q1: Can we combine the 2 parameters together (owner and tables)? If not, then what is the way to specify it....
Exp scott/tiger owner=scott tables=(T1)
Error msg is: conflicting modes specified.
Q2: what is the privilege need for exporting other schema's tables?
Q3: what is the use of export table with index and many, but without ROWS?
send me the command for exporting multiple tables(1000+) in Linux env. 9i db, i know we can do using spool command but dont know exactly how to put it. i know using Datapump but this is 9i.
View 7 Replies View RelatedAs a part of our back up we used to export the production data every day using Original export Utility but from 11g original export Utility is de supported and also datapump doesn't support XML Objects so is there any other way to export the full database else any option to export xml Object using datapump.
View 2 Replies View RelatedI would like to run a daily job that will export the table data from SQL server table and Import back into Oracle table. I might need to run the query to update the flag back into sql server table once job is done. How can i do this using either sql server or oracle?
We have oracle 9.2 and sql server 2005.
Normally i do from flat file or csv file which is generated by developer or user from source destination (not me) and i dump into oracle using sql*loader but this time I have to directly extract/export the data from MS Sql server and load into Oracle table, mostly it will reload so i might doing any massaging data during the load.
Is it sql sql*loader has any function that i can use the datasource to connect the MS Sql server and fetch the data and insert back data into oracle? I have access to Sql server but i don't how to use sql server to do this or using oracle as a daily job even because have to schedule the job for this as it will be a daily job.
We are doing daily cold backup. Due to lack of disk space,we couldn't Hot backup. We want our database to be up when doing backups. Since only export/import is possible in our scenario, clarify few queries:
1) Export was done during off period from the live server.
2) We have a development server, in which we have to update our database daily. Can i overwrite the Development server using IMPORT daily? Since this import might show lots of errors (Object already exist), what parameters can i use for import.
When i do a table export , it got over in 30 mins.When i do import using same dump file (that was created in 30 mins), its taking more than 30 mins .
why the import is taking more time than the export time ?
Is it possible to identify what level of export by looking at export dumpfile .. whether it is a schema export,full export,table export,..
If yes.. how ?
any other utilities that we can use to load data from our PROD server (10g) to DEV server (9i)? I've read some related topics here that it's not possible to import from a HIGHER to LOWER versions of Oracle. We've tried (many times) EXPorting selected tables from the 10g dB, then IMPort it to the 9i dB and we've haven't succeeded anyhow. PROD & DEV have a different schema/owner but the same table structures.
View 4 Replies View Relatedwe 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.
We have two databases running on 10.2.0.4 and 9.2.0.8. Both are having the same unpartitioned table of size 80G. I am exporting the table on 10g by using parallel=8 and dumpfile with %U option. That took around 4 hours to export the table.
And on 9.2.0.8, i am exporting using below parameters, taking around 5 hours.
buffer=2000000
recordlength=64000
options i can try to speed up the export in both versions.
Just want to confirm if I can run the export (command line) even if the database is up (online)? Are there any risks/impact while doing so?
View 7 Replies View RelatedI want to create two or three sachems on my production server which should be the same copy of my another second production server. And I access this second server through VPN connection on toad9.0.1. And I access my production server through VNC viewer and database through toad.
How cloud I create schema on my first prod. server from second server.
is any way to export an Oracle database organized in manner that, both tables and constraints would be exported in the correct order.An easy sample:
- An database with 2 tables, with constraints between them. Table 1 has a FK on Table 2.
Is it possible to export both structure and data regarding the constraints, resulting in an script that makes it possibly to import it in a way that would not give me problems about constraints?
I need to export a user with all the tables. But I need to export data into only few tables, need to omit the data of few tables.
Ex I dont want to export data of Audit tables with AU prefix.