如何从dmp文件和日志文件导入Oracle数据库?

发布于 2024-11-16 17:24:52 字数 579 浏览 3 评论 0原文

我将如何从转储文件创建数据库?我的系统上没有具有相同结构的现有数据库,因此它必须包含作业、事件、表等。

我将转储和日志文件放在 E: 驱动器中

我尝试了导入实用程序

E:/>impdp system/tiger@oratest FILE=WB_PROD_FULL_20MAY11.dmp

但出现错误

invalid argument value
bad dump file specification
unable to open dump file "E:\app\admin\oratest\dpdump\WB_PROD_F
ULL_20MAY11.dmp" for read
unable to open file
unable to open file
(OS 2) The system cannot find the file specified.

,当我在 Windows 资源管理器中看到 DMP 文件(取自 Linux 服务器)显示为崩溃转储文件时,

我不明白我该如何解决这个问题。请帮我解决这个问题。

我是 Oracle 的新手...

How would I go about creating a database from a dump file? I do not have an existing database with the same structure on my system so it has to be complete with jobs, events, tables, and so on.

I placed the dump and log file in E: drive

I have tried the import utility

E:/>impdp system/tiger@oratest FILE=WB_PROD_FULL_20MAY11.dmp

But I'm getting error as

invalid argument value
bad dump file specification
unable to open dump file "E:\app\admin\oratest\dpdump\WB_PROD_F
ULL_20MAY11.dmp" for read
unable to open file
unable to open file
(OS 2) The system cannot find the file specified.

And when I see in Windows Explorer DMP file(taken from Linux server) is showing as Crash dump file

I don't understand how I can resolve this issue. Please help me to solve this issue.

I'm a complete newbie on Oracle...

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

决绝 2024-11-23 17:24:52

数据库是如何导出的?

  • 如果使用 exp 导出并导出完整架构,则

    1. 创建用户:

      创建用户<用户名>;由<密码>识别默认表空间<表空间名称>  上的配额不受限制;
      
    2. 授予权利:

      授予连接、创建会话、imp_full_database 到<用户名>;
      
    3. 使用 imp 开始导入:

      imp <用户名>/<密码>@<主机名>; file=<文件名>.dmp log=<文件名>.log full=y;
      
  • 如果使用 expdp 导出,则使用 impdp 开始导入>:

    impdp <用户名>/<密码>目录=<目录名称>; dumpfile=<文件名>.dmp logfile=<文件名>.log full=y;
    

看错误日志,好像你没有指定目录,因此 Oracle 尝试在默认目录(即 E:\app\Vensi\admin\oratest\dpdump\)中查找 dmp 文件。

将导出文件移动到上述路径,或者创建一个目录对象以指向 dmp 文件所在的路径,并将对象名称传递给上面的 impdp 命令。

How was the database exported?

  • If it was exported using exp and a full schema was exported, then

    1. Create the user:

      create user <username> identified by <password> default tablespace <tablespacename> quota unlimited on <tablespacename>;
      
    2. Grant the rights:

      grant connect, create session, imp_full_database to <username>;
      
    3. Start the import with imp:

      imp <username>/<password>@<hostname> file=<filename>.dmp log=<filename>.log full=y;
      
  • If it was exported using expdp, then start the import with impdp:

    impdp <username>/<password> directory=<directoryname> dumpfile=<filename>.dmp logfile=<filename>.log full=y;
    

Looking at the error log, it seems you have not specified the directory, so Oracle tries to find the dmp file in the default directory (i.e., E:\app\Vensi\admin\oratest\dpdump\).

Either move the export file to the above path or create a directory object to pointing to the path where the dmp file is present and pass the object name to the impdp command above.

久夏青 2024-11-23 17:24:52

所有这些和平的代码放入 *.bat 文件并立即运行:

我在 oracle 中创建用户的代码。 crate_drop_user.sql 文件

drop user "USER" cascade;
DROP TABLESPACE "USER";

CREATE TABLESPACE USER DATAFILE 'D:\ORA_DATA\ORA10\USER.ORA' SIZE 10M REUSE 
    AUTOEXTEND 
    ON NEXT  5M  EXTENT MANAGEMENT LOCAL 
    SEGMENT SPACE MANAGEMENT  AUTO
/ 

CREATE  TEMPORARY TABLESPACE "USER_TEMP" TEMPFILE 
    'D:\ORA_DATA\ORA10\USER_TEMP.ORA' SIZE 10M REUSE AUTOEXTEND
    ON NEXT  5M  EXTENT MANAGEMENT LOCAL 
    UNIFORM SIZE 1M    
/

CREATE USER "USER"  PROFILE "DEFAULT" 
    IDENTIFIED BY "user_password" DEFAULT TABLESPACE "USER" 
    TEMPORARY TABLESPACE "USER_TEMP" 
/    

alter user USER quota unlimited on "USER";

GRANT CREATE PROCEDURE TO "USER";
GRANT CREATE PUBLIC SYNONYM TO "USER";
GRANT CREATE SEQUENCE TO "USER";
GRANT CREATE SNAPSHOT TO "USER";
GRANT CREATE SYNONYM TO "USER";
GRANT CREATE TABLE TO "USER";
GRANT CREATE TRIGGER TO "USER";
GRANT CREATE VIEW TO "USER";
GRANT "CONNECT" TO "USER";
GRANT SELECT ANY DICTIONARY to "USER";
GRANT CREATE TYPE TO "USER";

创建文件 import.bat 并将以下行放入其中:

SQLPLUS SYSTEM/systempassword@ORA_alias @"crate_drop_user.SQL"
IMP SYSTEM/systempassword@ORA_alias FILE=user.DMP FROMUSER=user TOUSER=user GRANTS=Y log =user.log

如果要从一个用户导入到另一个用户,请小心。例如,如果您有名为 user1 的用户并且您将导入到 user2 ,您可能会丢失所有 grant ,因此您必须重新创建它。

祝你好运,伊万

All this peace of code put into *.bat file and run all at once:

My code for creating user in oracle. crate_drop_user.sql file

drop user "USER" cascade;
DROP TABLESPACE "USER";

CREATE TABLESPACE USER DATAFILE 'D:\ORA_DATA\ORA10\USER.ORA' SIZE 10M REUSE 
    AUTOEXTEND 
    ON NEXT  5M  EXTENT MANAGEMENT LOCAL 
    SEGMENT SPACE MANAGEMENT  AUTO
/ 

CREATE  TEMPORARY TABLESPACE "USER_TEMP" TEMPFILE 
    'D:\ORA_DATA\ORA10\USER_TEMP.ORA' SIZE 10M REUSE AUTOEXTEND
    ON NEXT  5M  EXTENT MANAGEMENT LOCAL 
    UNIFORM SIZE 1M    
/

CREATE USER "USER"  PROFILE "DEFAULT" 
    IDENTIFIED BY "user_password" DEFAULT TABLESPACE "USER" 
    TEMPORARY TABLESPACE "USER_TEMP" 
/    

alter user USER quota unlimited on "USER";

GRANT CREATE PROCEDURE TO "USER";
GRANT CREATE PUBLIC SYNONYM TO "USER";
GRANT CREATE SEQUENCE TO "USER";
GRANT CREATE SNAPSHOT TO "USER";
GRANT CREATE SYNONYM TO "USER";
GRANT CREATE TABLE TO "USER";
GRANT CREATE TRIGGER TO "USER";
GRANT CREATE VIEW TO "USER";
GRANT "CONNECT" TO "USER";
GRANT SELECT ANY DICTIONARY to "USER";
GRANT CREATE TYPE TO "USER";

create file import.bat and put this lines in it:

SQLPLUS SYSTEM/systempassword@ORA_alias @"crate_drop_user.SQL"
IMP SYSTEM/systempassword@ORA_alias FILE=user.DMP FROMUSER=user TOUSER=user GRANTS=Y log =user.log

Be carefull if you will import from one user to another. For example if you have user named user1 and you will import to user2 you may lost all grants , so you have to recreate it.

Good luck, Ivan

俯瞰星空 2024-11-23 17:24:52

如果您使用 @sathyajith-bhat 响应中的 impdp 命令示例:

impdp <username>/<password> directory=<directoryname> dumpfile=<filename>.dmp logfile=<filename>.log full=y;

您将需要使用强制参数 directory 并将其创建并授予为:

CREATE OR REPLACE DIRECTORY DMP_DIR AS 'c:\Users\USER\Downloads';
GRANT READ, WRITE ON DIRECTORY DMP_DIR TO {USER};

或使用定义之一:

select * from DBA_DIRECTORIES;

My ORACLE Express 11g R2 默认名为DATA_PUMP_DIR(位于 {inst_dir}\app\oracle/admin/xe/dpdump/),您仍然需要授予它你的用户。

If you are using impdp command example from @sathyajith-bhat response:

impdp <username>/<password> directory=<directoryname> dumpfile=<filename>.dmp logfile=<filename>.log full=y;

you will need to use mandatory parameter directory and create and grant it as:

CREATE OR REPLACE DIRECTORY DMP_DIR AS 'c:\Users\USER\Downloads';
GRANT READ, WRITE ON DIRECTORY DMP_DIR TO {USER};

or use one of defined:

select * from DBA_DIRECTORIES;

My ORACLE Express 11g R2 has default named DATA_PUMP_DIR (located at {inst_dir}\app\oracle/admin/xe/dpdump/) you sill need to grant it for your user.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文