I need to copy more than 1000 database users(without objects) from orcle 9i to oracle 11g. They don't allow to use any graphical tools.which is the best way to complete this task? does conventional export /import works for only users only ?
View 2 RepliesI have a RedHat Server with Oracle Ent. 8iI did a full export of all my current data bases ( DCTEST, DCPROG, DCUSAC, DCCAND)I now have the DC*.dmp files. How can I know import these files into Oracle Database 11R2?
Do you have a Step-by-Step how-to ?IF I don't have the scripts to re-create the databases how to go forward ?
i want to perform full export + import of an oracle 11g database as fast as possible. i was thinking to perform the exp+imp on the same command.in exp i can perform something like this :
mknod /oracle/migration/exp_pipe p
exp '/ AS SYSDBA' file= /oracle/migration/exp_pipe full=y | imp system/***@oracle_db file= /oracle/migration/exp_pipe full=y
i know that i can do both action in impdp when using a dblink, but the problem is that some objects in the database cannot be copied via a dblink. the question is if there's a corresponding datapump command to the old exp+imp command i presented.
I want to Copy a data from One oracle database to another.
I have checked Import/Export Utility but the problem is import utility doesn't support conflicts resolution techniques between rows.
For Example if there's a table in the source database have the same row key in the destination database. if i use 'Ignore' parameter with value = y, the destination table will have a duplicate rows.
I want to ask if there's another way to import data from oracle database to another with some mechanism of detecting the conflicts and resolve them?
1) Is there a way to skip database jobs while exporting (EXPDP) ?
2) Is there a way to skip database jobs while importing (IMPDP) ?
I'm studying abt SQL*Loader. All I've learn it needs to have:
1. One text input file
2.Control file
3.Bad file...
But I'm confused where to put the input file...where to put the control file in which format and in control file what should I write...
My oracle version is:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
i have a .dmp file and i want to use the data in this file for my further practices. so, i need to dump the data in the .dmp file to the any schema exists in data base.
View 1 Replies View RelatedI am migrating data from a Solid Database to Oracle, I am using Flat Files to do that.
1.- I download the data to flat files from Solid
2.- I move the files to Oracle server
3.- I upload the data to Oracle
Now, I have done the 90% of the data base, but I have found some tables that has description columns and in this description the users writes enters, so when I try to upload the data to Oracle SQL loader cannot recognize this characters.
Example:
'25','0.','5.','0.','0.','0.','0.','0.','0.','0.','0.','0.','0.','',''
'26','0.','2.','0.','0.','0.','0.','3.','0.','0.','0.','0.','0.','',''
'27','0.','1.','0.','0.','0.','0.','0.','0.','0.','0.','0.','0.','',''
'28','0.','1.','0.','0.','0.','0.','0.','0.','0.','0.','0.','0.','',''
'29','0.','38.','0.','0.','0.','0.','0.','0.','0.','0.','0.','0.','',''
'30','0.','13.','0.','0.','0.','0.','0.','6.','0.','6.','0.','0.','|SE RECHAZA B20CS50SNW ^M
^M
SE RECHAZAN CINCO PZAS ^M
DOS MOD. HSC15I41EH,DOS MOD. HSK15I41EH |Agregó: 06/06/2009 12:22:50
|','DEV. A PROV.'
'31','0.','50.','0.','0.','0.','0.','0.','0.','0.','0.','0.','0.','',''
'32','0.','9.','0.','0.','0.','0.','0.','0.','0.','0.','0.','0.','',''
'33','0.','2.','0.','0.','0.','0.','0.','0.','0.','0.','0.','0.','',''
How can I solve this ?
I would like to export an entire DB metadata . I want to exclude data.is it possible.We have 100+users.We get request to restore package from their schema very often.So I am thinking of creating job to emport an entire DB metadata .
View 7 Replies View RelatedI am trying to export selective data from one of my prod database tables. But not succeeding. I was keep on trying for the past 2 hours.
OS : SOLARIS SPARC
ORACLE - 10G
Query --> WHERE E3RECV_DT LIKE '201305%' (I need to export this query data)
Below Script i am using
===============
exp E3USER@SGEBAPU2 statistics=none consistent=n buffer=100000000 file=exp_pipe_file TABLES=IFDATA query="WHERE E3RECV_DT LIKE '201305\%'" log=PGTB_IFDATA_conditional.log
I had just successfully finished a full importing from Oracle 9i DB to Oracle 11gR2 DB. My export was a full db export.
Prior to this importing, my 11g was a newly created DB with the default SYS, System etc.. schema. Their passwords is different from those in 9i.
However, i realised that after importing... their passwords in 11g was replaced by those passwords in 9i, including SYS and SYSTEM user...
I have new machine with oracle 11g and i have exported dump from oracle 10g . Now i need to import that dump on oracle 11g.
View 3 Replies View RelatedI 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 ?
version 10203 on windows (old db on solaris 8170)
i Have to refresh database data, No of users/schemas are 400+. fastest way to do that would be to do full exp/imp.but 1st drop current users cascade. (any command to drop all users in with one command?)then validate all all tables/schemas are same & up-to-date. i am thinking to check full exp logs on old & new db.(what that can take forever manually going through thousands of tables etc)
I need to migrate a 10g database into an 11gR2 on same Red Hat Linux platform (although different servers with different versions of Linux). The difference between the two databases however is the SID, the new one has a different SID which means that the datafiles will be named differently (as our datafile names include the SID) otherwise everything else is the same..
I propose to take the following steps:
- install 11gR2 on the new server
- create the 11gR2 database with new SID using DBCA
- full export 10g database
- full import dump file into 11gR2 database.
I however do not have experience of how this will work with respect to the full import where the datafiles are named differently. For example TABLESPACE TEST in the source database has datafile TEST_SOURCE.dbf but the same TABLESPACE TEST in the target database will have datafile TEST_TARGET.dbf.
Will all the data in the source database be correctly imported into the new database?
I need to refresh a PROD database into TEST database. The PROD and TEST runs on 10g. I need a full refresh. Is there any pre req's which i should keep in mind ?.
View 1 Replies View RelatedCan I use impdp to restore to a different database name? If yes what is the syntax? This is 10.2 on linux.
View 2 Replies View RelatedWhy do export-import require temporary tablespace? Since export-import do behave like DMLs, when does temporary tablespace be needed by datapump utility?
View 2 Replies View Relatedhave few exp dumps from earlier version of oracle 8i & 9i, now i wanted to import in oracle 11g. how to do or some URLs/Docs to refer ?I have read MOS Doc 132904.1 do from where i can get earlier exp/imp client and how to use on oracle 11g DB server ?
View 2 Replies View RelatedMy database size is around 2900 GB in AIX 6.1, database version is 10.2.0.3. Everyday I need to take expdp dump backup of a single table which is only 57 MB in size. It takes around 55 minutes to complete the dump backup.
I have noticed that when backup starts , in first phase it does table scan ( we have 330000 tables) , next purely backup begin. My query is ,
1. how to make first my dump backup?
2. is there any way to skip table scan ?
the database (11gR2) is located on Linux server. A business application is installed on a Windows server with an Oracle client 11g.The application is able to start a datapump export, but as matter of fact the dumpfiles are always written to the Linux-server. The directoryobject is defined as DATA_PUMP_DIR (which is the default directory).
Now we are supposed to change the datapump export in a matterthat the dumpfiles get written to the Windows server. Creating a new directory (e.g. c:datapump) and starting than the datapump from theclient always raises the errors
ORA-39002: ...ORA-39070: ...ORA-29283: ...ORA-06512: in "SYS.UTL_FILE", Zeile 536ORA-29283: ...
Is it possible at all to start a datapump export from a Windows client and writing the dumpfiles to the Windows server itself? Or do the dumpfiles always written to the database-server?
Upgrading one of the 9i database to 11g that supports a 3rd party software - ***Vendor provided an over-simplified documentations*** and recommends moving from 9i to 10g before going to 11g. A few changes from 9i to 10g.
1) db_block_size
2) character sets
etc.
Anyway, created the database DBUPGTEST on 10.2.0.1 (ultimately moving to 11gR2, so no point patching to 10.2.0.5, is there?) with all the parameter changes. At this point, these are the 2 db in play:
Current production db: Oracle 9i - PROD dbname => 2048K db block size
Current migrating db to: Oracle10g - DBUPGTEST dbname => 8192k db block size
Steps
According to vendor notes / documentation,
1) create db
2) exp full from 9i
3) imp full to 10g
Problems
1) import ended with completed unsuccessful.
2) user accounts are imported (because their default tablespace is USERS - which had already been created during DB creation); but, user accounts (schema accounts) with a different default tablespace are not imported.
Looking at the imp.log - seems like it's complaining about the db_block_size during tablespace creation - which explains why the schema accounts are not imported; because the tablespace was not created.
My questions
1) How do I import to 10g? Can I create all the tablespace in 10g first? Then import? Will it crap out because it already exists? Or will it import the objects in the schema?
2) How do I refresh data from PROD? Remember this is 9i and most of the expdp functionalities are not available. And I cannot re-exp and re-imp because there are steps (sql to run) after moving to 10g to fix some software upgrade table mappings. If I re-exp from 9i and re-imp to 10g, won't I have to re-run all those steps before the apps will run?
In our application, we are allowing user to upload data using excel sheet in UI. We are using PHP script in UI and using SQL Loader to load data from excel sheet to temp_table.
The temp_table has a primary key.
Here my question is , Is there any way to put some batch id for every upload in that table in automatic way ? so that we can easily extract the data by using batch id . we are using Oracle 11g.
how to Migrate Postgres DB Schema to Oracle 11 g??? Any tool for migration of postgres Schema to Oracle 11g .
View 2 Replies View RelatedI am getting the following error while trying to import dmp file in oracle xe:
D:>imp system/manager file=pune_ucf.dmp tables=(ARR_TOT, DE
P_TOT) grants=no indexes=no rows=yes ignore=yes log=loc.log buffer=100000000;
LRM-00104: '100000000;' is not a legal integer for 'buffer'
IMP-00022: failed to process parameters, type 'IMP HELP=Y' for help
IMP-00000: Import terminated unsuccessfully.
I want to read the csv file and load into oracle table.But I am getting file with filename_<today date> for every day. Is it possible to use single External table to read file in dynamic.
or what is the best way to do this? My oracle version 10g in windows OS.
I have a flat file (student.dat delimiter %~| ) using control file (student.ctl) through sql loader. Here are the details.
student.dat
student_id, student_firstname, gender, student_lastName, student_newId
101%~|abc%~|F %~|xyz%~|110%~|
Corresponding table
Student (
Student_ID,
Student_FN,
Gender,
Student_LN
)
How do i map student_newId field to student_id field in STUDENT DB table so that new id should be inserted in student_id column. How do i specify the mapping in control file. I dont want to create a new column in student table. In control file i will specify the below, Is this a best approach?. Do we have any othe way?
STUDENT_ID *(:STUDENT_NEWID)*,
STUDENT_FN,
GENDER,
STUDENT_LNAME,
STUDENT_NEWID BOUNDFILLER
MY DB Version: 10.2.o
OS: Windows Server 2003
I am trying to import on table which i have the export dump file which i take using expdp previously when i load that table on the same host
by using below command:
expdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log
after that i zip that dump and move it to external usb and now i need that table i copy that table and unzip that that dump
Command i am using to do the import is :
impdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=impdpEMP_DEPT.log
But the query of import is still runing even not showing any amount of rows to be imported.
i already make the tablespace in which the table was previosuly before dropping but when i check the sapce of tablespace that is also not consuming one error i got preiviously while performing this task is:
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "CDR"."SYS_IMPORT_TABLE_03" successfully loaded/unloaded
Starting "CDR"."SYS_IMPORT_TABLE_03": cdr/********@tsiindia directory=TEST_DIR dumpfile=CAT_IN_DATA_042012.DMP tables=CAT_IN_DATA_042012 logfile=impdpCAT_IN_DATA_042012.log
[code]....
i check streams_pool_size it will show zero and then i make it to 48M and after that
SQL> show parameter streams_pool_size;
NAME TYPE VALUE
-----------
streams_pool_size big integer 48M
But still it takes time
My database version
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - ProductionOS version:
Windows 7 64bit I have schema(scott) export with schema level option and imported with different name as (scott1).At regular period of time i need to import the scott to scott1 without affecting existing records.such as
*1. Need to append new created records.*
*2. Need to append updated records.*
for the above requirement I did in the following way
expdp xxxx/******** schemas=SCOTT directory=dumpdir dumpfile=SCOTT_28-SEP-2012.dmp logfile=exp_SCOTT_28-SEP-2012.log imported in the following way impdp xxxx/******** AS SYSDBA REMAP_SCHEMA=SCOTT:SCOTT1 directory=DUMPDIR dumpfile=SCOTT_28-SEP-2012.dmp logfile=imp_SCOTT2_28-09-2012.log TRANSFORM=SEGMENT_ATTRIBUTES:n TABLE_EXISTS_ACTION=APPEND.
The problem is i couldn'table to append the records to existing tables the log error show such ways.
ORA-31684: Object type USER:"SCOTT1" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
[code].....
I am trying to run impdp over network to import tables only but i am getting an error saying not able to create metadata and the import fails
Here are the steps below,
1. Source database created a user and granted select on certain tables to the user.
2. Created a user in the Target database.
3. Created a public link as sys user in the target database.
4. granted imp and exp full database to both users and all the other privs.
5. Started the impdp from the target server.
The import fails with
$impdp abc/xyz directory=DATA_PUMP_DIR network_link=TESTAR logfile=net_import_proddev.log TABLES=impdb.abc parallel=12 REMAP_SCHEMA=IMPDB:ABC
Import: Release 11.2.0.3.0 - Production on Tue Apr 23 13:10:51 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "IMPDB"."SYS_IMPORT_TABLE_01": abc/******** directory=DATA_PUMP_DIR network_link=TESTAR logfile=net_import_proddev.log TABLES=impdb.abc parallel=12 REMAP_SCHEMA=IMPDB:ABC
[code]....