Импортировать файл dmp в новую версию oracle xe
Я пытаюсь импортировать файл *.dmp из базы данных клиента в новую установку Oracle XE 11.2.0.
Я новичок в Oracle DB, я всегда использовал MySQL, поэтому я искал форум и использовал команду из cmd
C:\oraclexe\app\oracle\product\11.2.0\server\bin>imp 'system/root AS SYSDBA' file=c:\export.dmp full=yes log=implog.txt
без удачи Implog.txt выглядит следующим образом:
Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
Export file created by EXPORT:V09.02.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
export client uses UTF8 character set (possible charset conversion)
. importing SYSTEM's objects into SYSTEM
IMP-00015: following statement failed because the object already exists:
"CREATE UNDO TABLESPACE "UNDOTBS1" BLOCKSIZE 8192 DATAFILE '/home/app/oracl"
"e/oradata/ANA/undotbs01.dbf' SIZE 2000M AUTOEXTEND ON NEXT 5242880 M"
"AXSIZE 32767M EXTENT MANAGEMENT LOCAL "
IMP-00015: following statement failed because the object already exists:
"CREATE TEMPORARY TABLESPACE "TEMP" BLOCKSIZE 8192 TEMPFILE '/home2/oradata"
"/ANA/temp01.dbf' SIZE 2916M AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M"
" EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576"
IMP-00017: following statement failed with ORACLE error 1119:
"CREATE TABLESPACE "INDX" BLOCKSIZE 8192 DATAFILE '/home/app/oracle/oradata"
"/ANA/indx01.dbf' SIZE 1048576 AUTOEXTEND ON NEXT 1310720 MAXSIZE 327"
"67M EXTENT MANAGEMENT LOCAL AUTOALLOCATE ONLINE PERMANENT NOLOGGING SEGM"
"ENT SPACE MANAGEMENT AUTO"
IMP-00003: ORACLE error 1119 encountered
ORA-01119: error in creating database file '/home/app/oracle/oradata/ANA/indx01.dbf'
ORA-27040: file create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 3) Impossibile trovare il percorso specificato.
IMP-00017: following statement failed with ORACLE error 1119:
"CREATE TABLESPACE "TOOLS" BLOCKSIZE 8192 DATAFILE '/home/app/oracle/oradat"
"a/ANA/tools01.dbf' SIZE 209715200 AUTOEXTEND ON NEXT 327680 MAXSIZE "
"32767M EXTENT MANAGEMENT LOCAL AUTOALLOCATE ONLINE PERMANENT NOLOGGING S"
"EGMENT SPACE MANAGEMENT AUTO"
IMP-00003: ORACLE error 1119 encountered
ORA-01119: error in creating database file '/home/app/oracle/oradata/ANA/tools01.dbf'
ORA-27040: file create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 3) Impossibile trovare il percorso specificato.
IMP-00015: following statement failed because the object already exists:
"CREATE TABLESPACE "USERS" BLOCKSIZE 8192 DATAFILE '/home/app/oracle/oradat"
"a/ANA/users01.dbf' SIZE 1048576 AUTOEXTEND ON NEXT 1310720 MAXSIZE 3"
"2767M EXTENT MANAGEMENT LOCAL AUTOALLOCATE ONLINE PERMANENT NOLOGGING SE"
"GMENT SPACE MANAGEMENT AUTO"
IMP-00017: following statement failed with ORACLE error 1119:
"CREATE TABLESPACE "XDB" BLOCKSIZE 8192 DATAFILE '/home/app/oracle/oradata/"
"ANA/xdb01.dbf' SIZE 209715200 AUTOEXTEND ON NEXT 655360 MAXSIZE 3276"
"7M EXTENT MANAGEMENT LOCAL AUTOALLOCATE ONLINE PERMANENT NOLOGGING SEGME"
"NT SPACE MANAGEMENT AUTO"
IMP-00003: ORACLE error 1119 encountered
ORA-01119: error in creating database file '/home/app/oracle/oradata/ANA/xdb01.dbf'
ORA-27040: file create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 3) Impossibile trovare il percorso specificato.
IMP-00017: following statement failed with ORACLE error 1119:
"CREATE TABLESPACE "A00" BLOCKSIZE 8192 DATAFILE '/home2/oradata/A/a00.dbf'"
" SIZE 1048576000 EXTENT MANAGEMENT LOCAL AUTOALLOCATE ONLINE PERMAN"
"ENT NOLOGGING"
IMP-00003: ORACLE error 1119 encountered
ORA-01119: error in creating database file '/home2/oradata/A/a00.dbf'
ORA-27040: file create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 3) Impossibile trovare il percorso specificato.
IMP-00017: following statement failed with ORACLE error 1119:
"CREATE TABLESPACE "D00" BLOCKSIZE 8192 DATAFILE '/home2/oradata/A/d00.dbf'"
" SIZE 5000M EXTENT MANAGEMENT LOCAL AUTOALLOCATE ONLINE PERMANENT "
"NOLOGGING"
IMP-00003: ORACLE error 1119 encountered
ORA-01119: error in creating database file '/home2/oradata/A/d00.dbf'
ORA-27040: file create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 3) Impossibile trovare il percorso specificato.
IMP-00015: following statement failed because the object already exists:
"CREATE USER "OUTLN" IDENTIFIED BY VALUES '4A3BA55E08595C81' TEMPORARY TABLE"
"SPACE "TEMP" PASSWORD EXPIRE ACCOUNT LOCK"
IMP-00015: following statement failed because the object already exists:
"CREATE USER "ANONYMOUS" IDENTIFIED BY VALUES 'anonymous' DEFAULT TABLESPACE"
" "XDB" TEMPORARY TABLESPACE "TEMP" PASSWORD EXPIRE ACCOUNT LOCK"
IMP-00017: following statement failed with ORACLE error 959:
"CREATE USER "A00" IDENTIFIED BY VALUES 'DB7EF8D464FCEC4D' DEFAULT TABLESPAC"
"E "A00" TEMPORARY TABLESPACE "TEMP""
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'A00' does not exist
IMP-00017: following statement failed with ORACLE error 959:
"CREATE USER "D00" IDENTIFIED BY VALUES '546A075B8012922B' DEFAULT TABLESPAC"
"E "D00" TEMPORARY TABLESPACE "TEMP""
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'D00' does not exist
IMP-00015: following statement failed because the object already exists:
"CREATE ROLE "SELECT_CATALOG_ROLE""
IMP-00015: following statement failed because the object already exists:
"REVOKE "SELECT_CATALOG_ROLE" FROM SYSTEM"
IMP-00015: following statement failed because the object already exists:
"CREATE ROLE "EXECUTE_CATALOG_ROLE""
IMP-00015: following statement failed because the object already exists:
"REVOKE "EXECUTE_CATALOG_ROLE" FROM SYSTEM"
IMP-00015: following statement failed because the object already exists:
"CREATE ROLE "DELETE_CATALOG_ROLE""
IMP-00015: following statement failed because the object already exists:
"REVOKE "DELETE_CATALOG_ROLE" FROM SYSTEM"
IMP-00015: following statement failed because the object already exists:
"CREATE ROLE "RECOVERY_CATALOG_OWNER""
IMP-00015: following statement failed because the object already exists:
"REVOKE "RECOVERY_CATALOG_OWNER" FROM SYSTEM"
IMP-00015: following statement failed because the object already exists:
"CREATE ROLE "GATHER_SYSTEM_STATISTICS""
IMP-00015: following statement failed because the object already exists:
"REVOKE "GATHER_SYSTEM_STATISTICS" FROM SYSTEM"
IMP-00015: following statement failed because the object already exists:
"CREATE ROLE "LOGSTDBY_ADMINISTRATOR""
IMP-00015: following statement failed because the object already exists:
"REVOKE "LOGSTDBY_ADMINISTRATOR" FROM SYSTEM"
IMP-00015: following statement failed because the object already exists:
"CREATE ROLE "AQ_ADMINISTRATOR_ROLE""
IMP-00015: following statement failed because the object already exists:
"CREATE ROLE "AQ_USER_ROLE""
IMP-00015: following statement failed because the object already exists:
"REVOKE "AQ_USER_ROLE" FROM SYSTEM"
IMP-00017: following statement failed with ORACLE error 439:
"CREATE ROLE "GLOBAL_AQ_USER_ROLE" IDENTIFIED GLOBALLY "
IMP-00003: ORACLE error 439 encountered
ORA-00439: feature not enabled: Enterprise User Security
IMP-00015: following statement failed because the object already exists:
"CREATE ROLE "OEM_MONITOR""
IMP-00015: following statement failed because the object already exists:
"REVOKE "OEM_MONITOR" FROM SYSTEM"
IMP-00015: following statement failed because the object already exists:
"CREATE ROLE "HS_ADMIN_ROLE""
IMP-00015: following statement failed because the object already exists:
"REVOKE "HS_ADMIN_ROLE" FROM SYSTEM"
IMP-00015: following statement failed because the object already exists:
"CREATE ROLE "XDBADMIN""
IMP-00015: following statement failed because the object already exists:
"REVOKE "XDBADMIN" FROM SYSTEM"
IMP-00015: following statement failed because the object already exists:
"CREATE ROLE "AUTHENTICATEDUSER""
IMP-00015: following statement failed because the object already exists:
"REVOKE "AUTHENTICATEDUSER" FROM SYSTEM"
IMP-00017: following statement failed with ORACLE error 1917:
"GRANT UNLIMITED TABLESPACE TO "A00""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'A00' does not exist
IMP-00017: following statement failed with ORACLE error 1917:
"GRANT CREATE PROCEDURE TO "D00""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'D00' does not exist
IMP-00017: following statement failed with ORACLE error 1917:
"GRANT EXECUTE ANY PROCEDURE TO "D00""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'D00' does not exist
IMP-00017: following statement failed with ORACLE error 1917:
"GRANT CREATE TRIGGER TO "D00""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'D00' does not exist
IMP-00017: following statement failed with ORACLE error 1917:
"GRANT CREATE SNAPSHOT TO "D00""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'D00' does not exist
IMP-00017: following statement failed with ORACLE error 1917:
"GRANT CREATE TYPE TO "D00""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'D00' does not exist
IMP-00017: following statement failed with ORACLE error 1917:
"GRANT "CONNECT" TO "A00""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'A00' does not exist
IMP-00017: following statement failed with ORACLE error 1917:
"GRANT "DBA" TO "A00""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'A00' does not exist
IMP-00017: following statement failed with ORACLE error 1917:
"GRANT "CONNECT" TO "D00""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'D00' does not exist
IMP-00017: following statement failed with ORACLE error 1918:
"ALTER USER "D00" DEFAULT ROLE ALL"
IMP-00003: ORACLE error 1918 encountered
ORA-01918: user 'D00' does not exist
IMP-00017: following statement failed with ORACLE error 1918:
"ALTER USER "A00" DEFAULT ROLE ALL"
IMP-00003: ORACLE error 1918 encountered
ORA-01918: user 'A00' does not exist
IMP-00017: following statement failed with ORACLE error 1918:
"ALTER USER "A00" QUOTA UNLIMITED ON "TEMP" QUOTA UNLIMITED ON "A00""
IMP-00003: ORACLE error 1918 encountered
ORA-01918: user 'A00' does not exist
IMP-00017: following statement failed with ORACLE error 1918:
"ALTER USER "D00" QUOTA UNLIMITED ON "TEMP" QUOTA UNLIMITED ON "D00""
IMP-00003: ORACLE error 1918 encountered
ORA-01918: user 'D00' does not exist
IMP-00015: following statement failed because the object already exists:
"CREATE SEQUENCE "MVIEW$_ADVSEQ_GENERIC" MINVALUE 1 MAXVALUE 4294967295 INCR"
"EMENT BY 1 START WITH 1 CACHE 50 NOORDER NOCYCLE"
IMP-00015: following statement failed because the object already exists:
"CREATE SEQUENCE "MVIEW$_ADVSEQ_ID" MINVALUE 1 MAXVALUE 4294967295 INCREMENT"
" BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE"
IMP-00015: following statement failed because the object already exists:
"CREATE SEQUENCE "LOGMNR_SEQ$" MINVALUE 1 MAXVALUE 9999999999999999999999999"
"99 INCREMENT BY 1 START WITH 1 CACHE 20 ORDER NOCYCLE"
IMP-00015: following statement failed because the object already exists:
"CREATE SEQUENCE "LOGMNR_UIDS$" MINVALUE 1 MAXVALUE 999999999999999999999999"
"999 INCREMENT BY 1 START WITH 100 CACHE 20 ORDER NOCYCLE"
IMP-00015: following statement failed because the object already exists:
"CREATE SEQUENCE "REPCAT$_FLAVORS_S" MINVALUE -2147483647 MAXVALUE 214748364"
"7 INCREMENT BY 1 START WITH 1 NOCACHE NOORDER NOCYCLE"
IMP-00015: following statement failed because the object already exists:
"CREATE SEQUENCE "REPCAT$_FLAVOR_NAME_S" MINVALUE 1 MAXVALUE 999999999999999"
"999999999999 INCREMENT BY 1 START WITH 1 NOCACHE NOORDER NOCYCLE"
IMP-00015: following statement failed because the object already exists:
"CREATE SEQUENCE "REPCAT$_REPPROP_KEY" MINVALUE 1 MAXVALUE 99999999999999999"
"9999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE"
IMP-00015: following statement failed because the object already exists:
"CREATE SEQUENCE "REPCAT_LOG_SEQUENCE" MINVALUE 1 MAXVALUE 99999999999999999"
"9999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE"
IMP-00015: following statement failed because the object already exists:
"CREATE SEQUENCE "REPCAT$_REFRESH_TEMPLATES_S" MINVALUE 1 MAXVALUE 999999999"
"999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE"
IMP-00015: following statement failed because the object already exists:
"CREATE SEQUENCE "REPCAT$_USER_AUTHORIZATIONS_S" MINVALUE 1 MAXVALUE 9999999"
"99999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE"
IMP-00015: following statement failed because the object already exists:
"CREATE SEQUENCE "REPCAT$_TEMPLATE_REFGROUPS_S" MINVALUE 1 MAXVALUE 99999999"
"9999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE"
IMP-00015: following statement failed because the object already exists:
"CREATE SEQUENCE "REPCAT$_TEMPLATE_OBJECTS_S" MINVALUE 1 MAXVALUE 9999999999"
"99999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE"
IMP-00015: following statement failed because the object already exists:
"CREATE SEQUENCE "REPCAT$_TEMPLATE_PARMS_S" MINVALUE 1 MAXVALUE 999999999999"
"999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE"
IMP-00015: following statement failed because the object already exists:
"CREATE SEQUENCE "REPCAT$_USER_PARM_VALUES_S" MINVALUE 1 MAXVALUE 9999999999"
"99999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE"
IMP-00015: following statement failed because the object already exists:
"CREATE SEQUENCE "REPCAT$_TEMPLATE_SITES_S" MINVALUE 1 MAXVALUE 999999999999"
"999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE"
IMP-00015: following statement failed because the object already exists:
"CREATE SEQUENCE "REPCAT$_TEMP_OUTPUT_S" MINVALUE 1 MAXVALUE 999999999999999"
"999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE"
IMP-00015: following statement failed because the object already exists:
"CREATE SEQUENCE "REPCAT$_RUNTIME_PARMS_S" MINVALUE 1 MAXVALUE 9999999999999"
"99999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE"
IMP-00015: following statement failed because the object already exists:
"CREATE SEQUENCE "TEMPLATE$_TARGETS_S" MINVALUE 1 MAXVALUE 99999999999999999"
"9999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE"
IMP-00015: following statement failed because the object already exists:
"CREATE SEQUENCE "REPCAT$_EXCEPTIONS_S" MINVALUE 1 MAXVALUE 9999999999999999"
"99999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE"
. importing WMSYS's objects into WMSYS
. importing A00's objects into A00
"ALTER SESSION SET CURRENT_SCHEMA= "A00""
IMP-00003: ORACLE error 1435 encountered
ORA-01435: user does not exist
IMP-00000: Import terminated unsuccessfully
Теперь я читаю:
-сценарий удаляет все привилегии для пользователя SYSTEM, но даже при сбое импорта, после выполнения импорта я больше не могу войти в систему как SYSTEM
-Я пытаюсь импортировать *.dmp на 32-битную виртуальную машину Windows XP, и я думаю, что экспорт был создан на машине с Windows Vista (не знаю x86 или x64), но я не понимаю, что где в журнале написано "ошибка при создании файла базы данных" /home/app/oracle/oradata/ANA/indx01.dbf '". Здесь кажется путь Unix...
- почти везде Oracle говорит, что не может создавать пользователей / таблицы / индексы, потому что они уже существуют. Это новая установка, или, по крайней мере, это было, когда я впервые попробовал импортировать. После первого сбоя я попытался войти в систему как SYSTEM, не смог, поэтому я удалил Oracle XE и переустановил с нуля, так что я думаю, что это новая установка. Я делаю что-то неправильно? Есть ли способ воссоздать привилегии SYSTEM без удаления и переустановки всего?
Заранее спасибо за вашу помощь
РЕДАКТИРОВАТЬ: только что получил журнал экспорта, я опустил имена таблиц:
Connected to: Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
JServer Release 9.2.0.8.0 - Production
Export done in UTF8 character set and AL16UTF16 NCHAR character set
About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting context namespaces
. exporting foreign function library names
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions
. exporting system procedural objects and actions
. exporting pre-schema procedural objects and actions
. exporting cluster definitions
. about to export SYSTEM's tables via Conventional Path ...
. about to export OUTLN's tables via Conventional Path ...
. about to export DBSNMP's tables via Conventional Path ...
. about to export WMSYS's tables via Conventional Path ...
. about to export ANONYMOUS's tables via Conventional Path ...
. about to export A00's tables via Conventional Path ...
. about to export D00's tables via Conventional Path ...
. exporting synonyms
. exporting views
. exporting referential integrity constraints
. exporting stored procedures
. exporting operators
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting triggers
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting user history table
. exporting default and system auditing options
. exporting statistics
Export terminated successfully without warnings.
4 ответа
Решение
Чтобы определить, какие табличные пространства вам нужно создать, вам нужно выяснить, какие таблицы и индексы нужны, а затем посмотреть, из каких табличных пространств они построены. Первое, что вам нужно выяснить, - это какие схемы (пользователи) вам нужно воссоздать. Из журнала экспорта, который выглядит как A00, D00 или оба.
Вы можете получить DDL для таблиц и индексов, которые будут включать табличные пространства, в которые они встроены - запустив imp с INDEXFILE параметр, что-то вроде:
imp 'system/root AS SYSDBA' file=c:\export.dmp full=n rows=n fromuser=A00 touser=A00 indexfile=c:\indexfile.sql
Это даст вам текстовый файл с DDL для таблиц и индексов, но с командами создания таблиц, закомментированными с rem, Вы можете просмотреть этот файл в блокноте или другом текстовом редакторе и найти все TABLESPACE Рекомендации. Вам просто нужны все отличные значения. В вашем случае может случиться так, что все A00объекты находятся в A00 табличное пространство, но вам нужно проверить.
Получив эту информацию, вы можете вручную создать табличные пространства, которые вы определили в вашей новой базе данных с такими же именами. Вам также нужно будет создать пользователя, в которого вы импортируете, и убедиться, что он может создавать объекты; в вашем оригинальном журнале импорта у вас есть quota unlimited набор для A00 на табличном пространстве A00например, но также посмотрите на grant Команды, которые он пытался.
Как только они собраны, вы можете снова запустить импорт как:
imp 'system/root AS SYSDBA' file=c:\export.dmp full=n rows=y fromuser=A00 touser=A00 log=import.log
Таблицы, индексы и т. Д. Должны быть построены нормально, и данные будут импортированы в них.
Вам следует ознакомиться с командой и параметрами импорта, чтобы понять, что будут делать эти примеры, и решить, нужны ли какие-либо другие для изменения поведения импорта в зависимости от конечной цели.
Попробуйте следующие шаги:
- Создать табличное пространство
TOOLSвручную - использование
FROM_USER/TO_USERпараметры вimpимпортировать только данные схемы этого пользователя.
Импорт SYSTEM и другие табличные пространства от Oracle 9 до Oracle 11 сломают множество системных таблиц / объектов, которые живут в этом табличном пространстве.
Может быть, этот экспорт с машины Linux? Поскольку путь к табличному пространству: /home/app/oracle/oradata/* на компьютере с Windows кажется неправильным. Вы можете сначала создать табличное пространство и затем импортировать dmp. Просто мысль.
Открыть командную строку (здесь GLOBAL - имя пользователя и пароль) импортировать файлы 11g только в базу данных 11g, а файлы 10g - только в 10g.
> IMP GLOBAL/GLOBAL
password:global
it will give some error ORA-0107
again it will ask for logon username:GLOBAL
Password:global
Import Data Only (yes/no):no
Import file:EXPDAT.DMP > C:\Downloads\global_tables.dmp (location of dump file)
Enter insert buffer size (minimum is 8192) 30720>
30720
List contents of import file only (yes/no): no >
no
Ignore create error due to object existence (yes/no): no >
no
Import grants (yes/no): yes >
yes
Import table data (yes/no): yes >
yes
Import entire export file (yes/no): no >
yes