I 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.
import and export database in oracle with proper example and procedure?
View 3 Replies View RelatedI 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?
Whats 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 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 ?
i've 0racle 9i database, i wanted to know the easiest way how to copy that database on another computer throw local network,
View 15 Replies View RelatedIs there any possible to export & import the data using unix pipe parallel with two server( at the same time)?
Eg. In Server A, i will export the data & import the data in server B at the same time. (300 GB data)
Is it also possible to use the EXPORT and IMPORT utilities from a client machine? I want to give these utilities to one of my developers without allowing him to sit in front of my Oracle server.
i am using oracle 11.2.0.1 and windows7 OS
I am using Oracle 11.1.0.7.0 version and UNIX-Hp OS.
which export/import method should i use when i am to do export/import... like table level, tablespace level ,full database level, schema level?
1.Datapump or
2.Traditional export/ import ?
I was told to move 8 tables along with constraints,indexes,grants,rows,triggers from one database to another database.I did export and import for that.The command i used was
exp p5/chevuri@db3.SBC.COM file=C:alaexp.dmp log=C:alaexp.log
tables= ('tab1','tab2','tab3','tab4','tab5','tab6','tab7') rows=y indexes=y grants=y
constraints=y triggers=y direct=y
Below is the export output log.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 -
64bit Production With the Partitioning, OLAP, Data Mining and Real
Application Testing options Export done in WE8MSWIN1252 character set
and AL16UTF16 NCHAR
character set
server uses WE8ISO8859P1 character set (possible charset conversion)
About to export specified tables via Direct Path ...
. . exporting table tab1 12 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
[code]...
Here is the import output log
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V10.02.01 via direct path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing JAM's objects into JAM
.
[code]...
Everything got imported successfully . Still i have a doubt in export and import command, whether the command that i used for export and import was correct or if there is anything need to be added in command.
I'm going to import a single database using FS to a RAC on ASM both are in same server running oracle 11.2.0.3. So I was wondering if can I use network link mode of impdp without setting up a listener using 10gb Ethernet interface ?
I don’t want to incur the overhead of the tcp network layer because both DB single and Rac are on the same server.
1)Want to perform an Export from a Production Schema and Import the results into Test Schema. BUT, do not want to export ALL objects from Production (only a subset of tables). Is this possible?doco on how to do this? (rather than a complete Export and then a complete Import).
2)I have 2 test instances of Oracle on the same development server, UNIT and SIT. Am using Oracle SQL Developer tool.
While in the UNIT instance, is there a way to select data from the SIT instance? An example of syntax to use?
3) Can tables in the UNIT instance be compared to tables in the SIT instance, through any existing Oracle utilities
In oracle 11g R2 , I face a problem when I export/import xml records in the tables . Everytime it takes huge time (like 2 or 3 days) to import or export the data . But the dump size is very small (4gb) and this dump comes from a vendor so that I dont understand that is it a data structure problem or it the normal behaviour to import xml records.
I am not used to with xml record with oracle database before. Here I am using datapump feature . I also mention that when I delete the schema (where I imported the xml data) , it also takes 2/3 days to delete.The import script will hang in the following stage :
=========================
bash-3.2$ impdp system/sys123 DIRECTORY=isb_dir DUMPFILE=jblbld_20110301_01.dmp,jblbld_20110301_02.dmp,jblbld_20110301_03.dmp logfile=JBLBLD_28Jan2012.log schemas=TBLD;date
Import: Release 11.2.0.1.0 - Production on Sun Jan 29 11:10:38 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_02" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_02": system/******** DIRECTORY=isb_dir DUMPFILE=jblbld_20110301_01.dmp,jblbld_20110301_02.dmp,jblbld_20110301_03.dmp logfile=JBLBLD_28Jan2012.log schemas=TBLD
Processing object type SCHEMA_EXPORT/USER
[code]...
The machine has 16 cpu , 32 gb RAM and during export/import most of the time maximum memory will free.
I am trying to export/import of a schema who's size is around 60 GB.
Export parfile goes like this..
file=expdmp1.dmp, expdmp2.dmp, expdmp3.dmp, expdmp4.dmp, expdmp5.dmp, expdmp6.dmp, expdmp7.dmp
filesize=10240M
log=explog.log
owner=owner1
Import parfile goes like this..
file=impdmp1.dmp, impdmp2.dmp, impdmp3.dmp, impdmp4.dmp, impdmp5.dmp, impdmp6.dmp, impdmp7.dmp
filesize=10240M
log=implog.log
fromuser=owner1
touser=owner2
ignore=y
I am going to run this on production. So want to check it..
I am new to Oracle DBA. m facing one problem. i imported and exported my data from one oracle DB to another by using exp/imp.....in both exp/imp log files, its shows the fallowing messages at the end.
"Import terminated successfully with warnings."
"Export terminated successfully with warnings."
but when i count there are some rows missing in some tables...
what could be the cause? is there any other way to cross check whether export/import was successful...
is it possible to import only one or two table from a schema export file or from a full database export file.
View 2 Replies View Relatedi'm trying to do an export/import process using command prompt and the idea is export a records based on the date condition. and the date will be the parameter. my code is like this:
exp <username>/<password>@<database> file=<table_name>.dmp tables=<source_table> query="where <date> between &start_date AND &end_date";
is it possible to do like this, that it should prompt you to enter the start and end date?
then my import script:
imp <username>/<password>@<database> dumpfile=<table_name>.dmp tables=<target_table>;
the idea is get only the records from ProdDB based on the date condition, and append it to the MISDB.
Quote:The EXP_FULL_DATABASE and IMP_FULL_DATABASE, respectively, are needed to perform a full export and import.
what privileges are required to perform only schema level export and import?
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 Related1) Is there a way to skip database jobs while exporting (EXPDP) ?
2) Is there a way to skip database jobs while importing (IMPDP) ?
I will have to proceed with Oracle 9 database refresh from production server to integration server. 5 biggest schemas must be exported and imported. They constitute 97% space used in a database. This is very big database so I would like to be sure that everything will go smoothly. That is why i want to ask you some questions.
Have you got any clues for me before I start with exp/imp? From my side i will tell you that I will have to exp/imp schema by schema because there is small space both on production and integration disk for a dump. First thing I thought are dependencies between schemas that are exported and that which are not, and also between schemas that are exported/imported one by one.
This is procedure that I plan:
For every schema that is to be refreshed
{
1. Export schema with ROWS=N CONSTRAINTS=Y
2. EXPORT schema with ROWS=y CONSTRAINTS=N
3. Import schema from step one
4. Disable all the foreign key constraints using ALTER TABLE DISABLE CONSTRAINT.
5. Import schema with rows
}
ALTER TABLE ENABLE CONSTRAINT
With above procedure i think that I will avoid problems with dependencies between schemas exported/imported one by one. But my concern is if there are any dependencies between those schemas and schemas that are not exported. Is there an way to check it before refresh ?
I do not import a full database.
View 3 Replies View Relatedi would like to import a table from another database by using database link.
impdp muba/muba tables=FUNCTION_NO directory=testdump NETWORK_LINK=DBLINK1
i created the public database link as system user
it gave error like
Import: Release 10.2.0.1.0 - Production on Wednesday, 17 March, 2010 11:07:02
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Release 10.2.0.1.0 - Production
Starting "MUBA"."SYS_IMPORT_TABLE_01": muba/******** tables=FUNCTION_NO directory=testdump NETWORK_LINK=DBLINK1
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
[code]....
after that i try to import as system user like
impdp system/passwd tables=FUNCTION_NO schemas=muba directory=testdump NETWORK_LINK=DBLINK1
but error like
Import: Release 10.2.0.1.0 - Production on Wednesday, 17 March, 2010 11:27:43
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Release 10.2.0.1.0 - Production
UDI-00010: multiple job modes requested, schema and tables.
when i use IMP utility
imp system/passwd file=dump.dmp fromuser=userA touser=userB log=dmplog.log but when i use IMPDP what is the parameter equal to fromuser & touser
I am trying to import .dmp file to my database
I am trying to run the imp command from dos prompt.
Here is the error I got .
C:Documents and SettingssairammMy Documentsartmsexp_AUDT2_04292012>imp
sairamm/mypassword@aws fromuser=AUDT2 toUSER=SAIRAMM file=exp_AUDT2_04292012.dmp
log=imp_AUDT2_04292012.log BUFFE
R=10000000 GRANTS=y
Import: Release 9.2.0.1.0 - Production on Mon Apr 30 14:23:29 2012
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining,Oracle Database Vault and Real Application Testing option
IMP-00010: not a valid export file, header failed verification
IMP-00000: Import terminated unsuccessfully
I need to import one schema into 3 different DBS. out of 3, have imported into 2 databases without any error. during 3rd import iam getting below error:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP and Data Mining options.
ORA-31626: job does not exist
[code]....
I am using same parfile. no changes on the syntax. this is a schema export.
---partfile content
> more QSHOUT_ALARMPOINT4_import.par
directory=APP_EXPORT
dumpfile=exp_PSHOUT_01182013_ALARMPOINT4_SCHEMA.dmp
logfile=exp_PSHOUT_01182013_ALARMPOINT4_SCHEMA_impQSHOUT.log
TABLE_EXISTS_ACTION=truncate
parallel=2
cluster=n
full=y
Is it possible to import a 9i dumpfile (taken by tradition export method) into 10g database using datapump import?
9i and 10g are database version
If I want to import 10g export dump file in to the 9i database, I am connecting to 10g database from 9i database using
exp user/password@10gdb ....
However is there any option like executing 9i catexp.dat on 10g database and do the export from 10g database itself to be imported into 9i?