现在环境为生产的前一天数据同步环境,开发人员误删数据,想恢复几张表,于是用用户导入相关表
impdp slisdata/**** directory=TEST_DIR dumpfile=slisdataDP_20090406.dmp tables=LCCUSTOMSOSDETAIL
Import: Release 10.2.0.1.0 - 64bit Production on 星期二, 07 4月, 2009 16:03:45
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORA-39002: invalid operation
ORA-31694: master table "SLISDATA"."SYS_IMPORT_TABLE_01" failed to load/unload
ORA-31640: unable to open dump file "/data/dmpfile/slisdataDP_20090406.dmp" for read
ORA-19505: failed to identify file "/data/dmpfile/slisdataDP_20090406.dmp"
ORA-27046: file size is not a multiple of logical block size
Additional information: 1
然后不动
说数据文件无效肯定是不可能的,因为有crontab每晚进行数据同步。
在网上查了很多,有的说空间满了,有的说权限不够,一一尝试都已经排除,甚至都给了用户slisdata dba权限
对于test_dir是绝对有读写权限的。
[oracle@stage dmpfile]$ impdp system/**** directory=TEST_DIR dumpfile=slisdataDP_20090406.dmp tables=SLISDATA.LCCUSTOMSOSCARD
Import: Release 10.2.0.1.0 - 64bit Production on 星期二, 07 4月, 2009 16:42:29
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01": system/******** directory=TEST_DIR dumpfile=slisdataDP_20090406.dmp tables=SLISDATA.LCCUSTOMSOSCARD
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SLISDATA"."LCCUSTOMSOSCARD" 2.869 MB 21365 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
ORA-31684: Object type INDEX:"SLISDATA"."PK_LCCUSTOMSOSCARD" already exists
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ORA-31684: Object type CONSTRAINT:"SLISDATA"."PK_LCCUSTOMSOSCARD" already exists
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
ORA-39111: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"SLISDATA"."PK_LCCUSTOMSOSCARD" already exists
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_TABLE_01" completed with 3 error(s) at 16:43:07
最后用这种方法导入,然后又看了先system和slisdata的权限对比,没有发现原因在哪里
SELECT * FROM DBA_SYS_PRIVS t where t.grantee='SYSTEM' or
t.grantee='SLISDATA' ORDER BY 1
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
SLISDATA CREATE PUBLIC SYNONYM NO
SLISDATA CREATE SEQUENCE NO
SLISDATA CREATE SESSION NO
SLISDATA CREATE TABLE NO
SLISDATA CREATE VIEW NO
SLISDATA UNLIMITED TABLESPACE NO
SYSTEM CREATE MATERIALIZED VIEW NO
SYSTEM CREATE TABLE NO
SYSTEM GLOBAL QUERY REWRITE NO
SYSTEM SELECT ANY TABLE NO
SYSTEM UNLIMITED TABLESPACE YES
impdp slisdata/**** directory=TEST_DIR dumpfile=slisdataDP_20090406.dmp tables=LCCUSTOMSOSDETAIL
Import: Release 10.2.0.1.0 - 64bit Production on 星期二, 07 4月, 2009 16:03:45
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORA-39002: invalid operation
ORA-31694: master table "SLISDATA"."SYS_IMPORT_TABLE_01" failed to load/unload
ORA-31640: unable to open dump file "/data/dmpfile/slisdataDP_20090406.dmp" for read
ORA-19505: failed to identify file "/data/dmpfile/slisdataDP_20090406.dmp"
ORA-27046: file size is not a multiple of logical block size
Additional information: 1
然后不动
说数据文件无效肯定是不可能的,因为有crontab每晚进行数据同步。
在网上查了很多,有的说空间满了,有的说权限不够,一一尝试都已经排除,甚至都给了用户slisdata dba权限
对于test_dir是绝对有读写权限的。
[oracle@stage dmpfile]$ impdp system/**** directory=TEST_DIR dumpfile=slisdataDP_20090406.dmp tables=SLISDATA.LCCUSTOMSOSCARD
Import: Release 10.2.0.1.0 - 64bit Production on 星期二, 07 4月, 2009 16:42:29
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01": system/******** directory=TEST_DIR dumpfile=slisdataDP_20090406.dmp tables=SLISDATA.LCCUSTOMSOSCARD
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SLISDATA"."LCCUSTOMSOSCARD" 2.869 MB 21365 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
ORA-31684: Object type INDEX:"SLISDATA"."PK_LCCUSTOMSOSCARD" already exists
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ORA-31684: Object type CONSTRAINT:"SLISDATA"."PK_LCCUSTOMSOSCARD" already exists
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
ORA-39111: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"SLISDATA"."PK_LCCUSTOMSOSCARD" already exists
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_TABLE_01" completed with 3 error(s) at 16:43:07
最后用这种方法导入,然后又看了先system和slisdata的权限对比,没有发现原因在哪里
SELECT * FROM DBA_SYS_PRIVS t where t.grantee='SYSTEM' or
t.grantee='SLISDATA' ORDER BY 1
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
SLISDATA CREATE PUBLIC SYNONYM NO
SLISDATA CREATE SEQUENCE NO
SLISDATA CREATE SESSION NO
SLISDATA CREATE TABLE NO
SLISDATA CREATE VIEW NO
SLISDATA UNLIMITED TABLESPACE NO
SYSTEM CREATE MATERIALIZED VIEW NO
SYSTEM CREATE TABLE NO
SYSTEM GLOBAL QUERY REWRITE NO
SYSTEM SELECT ANY TABLE NO
SYSTEM UNLIMITED TABLESPACE YES
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10805681/viewspace-588717/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/10805681/viewspace-588717/