请教个数据库的备份与恢复问题

levinpluto

我通过exp user/passwd file=xxx.dmp 导出一个数据库的dmp包作为备份，恢复之前先运行如下的脚本：

begin
for i in (select table_name from user_tables)
loop
execute immediate('drop table '||i.table_name||' cascade constraints');
end loop;
end;
/
commit;

begin
for i in (select object_type,object_name from user_objects where object_type like 'SEQUENCE')
loop
execute immediate('drop '||i.object_type||' '||i.object_name);
end loop;
end;
/
commit;

begin
for i in (select object_type,object_name from user_objects where object_type like 'PROCEDURE')
loop
execute immediate('drop '||i.object_type||' '||i.object_name);
end loop;
end;
/
commit;

begin
for i in (select object_type,object_name from user_objects where object_type like 'VIEW')
loop
execute immediate('drop '||i.object_type||' '||i.object_name);
end loop;
end;
/
commit;

begin
for i in (select object_type,object_name from user_objects where object_type like 'INDEX')
loop
execute immediate('drop '||i.object_type||' '||i.object_name);
end loop;
end;
/
commit;

再imp之前备份的dmp包时会报错：
IMP-00015: following statement failed because the object already exists:
"CREATE function...

IMP-00015: following statement failed because the object already exists:
"CREATE PACKAGE...

IMP-00017: following statement failed with ORACLE error 1:
"BEGIN   DBMS_JOB.ISUBMIT(JOB=>934,WHAT=>'SP_BC_SubscriberUsage;',NEXT_DATE="
">TO_DATE('2012-08-02:04:00:00','YYYY-MM-DD:HH24:MI:SS'),INTERVAL=>'SYSDATE "
"+ 1',NO_PARSE=>TRUE); END;"
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SYS.I_JOB_JOB) violated
ORA-06512: at "SYS.DBMS_JOB", line 100
ORA-06512: at line 1
IMP-00017: following statement failed with ORACLE error 1:
"BEGIN   DBMS_JOB.ISUBMIT(JOB=>8,WHAT=>'PROC_COLDBINFO;',NEXT_DATE=>TO_DATE("
"'2012-08-31:06:13:36','YYYY-MM-DD:HH24:MI:SS'),INTERVAL=>'SYSDATE + 30',NO_"
"ARSE=>TRUE); END;"
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SYS.I_JOB_JOB) violated
ORA-06512: at "SYS.DBMS_JOB", line 100
ORA-06512: at line 1
IMP-00017: following statement failed with ORACLE error 1:
"BEGIN   DBMS_JOB.ISUBMIT(JOB=>10,WHAT=>'PROC_COLUSERINFOROC_COLTSINFORO"
"C_COLDBINFO;',NEXT_DATE=>TO_DATE('2012-08-02:06:13:36','YYYY-MM-DD:HH24:MI:"
"SS'),INTERVAL=>'SYSDATE + 1',NO_PARSE=>TRUE); END;"
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SYS.I_JOB_JOB) violated
ORA-06512: at "SYS.DBMS_JOB", line 100
ORA-06512: at line 1
IMP-00017: following statement failed with ORACLE error 1:
"BEGIN   DBMS_JOB.ISUBMIT(JOB=>11,WHAT=>'proc_exec_clearcookie;',NEXT_DATE=>"
"TO_DATE('2012-08-02:06:13:36','YYYY-MM-DD:HH24:MI:SS'),INTERVAL=>'SYSDATE +"
" 1',NO_PARSE=>TRUE); END;"
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SYS.I_JOB_JOB) violated
ORA-06512: at "SYS.DBMS_JOB", line 100
ORA-06512: at line 1
IMP-00017: following statement failed with ORACLE error 1:
"BEGIN   DBMS_JOB.ISUBMIT(JOB=>9,WHAT=>'delete from operatelog;',NEXT_DATE=>"
"TO_DATE('2012-08-31:06:13:36','YYYY-MM-DD:HH24:MI:SS'),INTERVAL=>'SYSDATE +"
" 30',NO_PARSE=>TRUE); END;"
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SYS.I_JOB_JOB) violated
ORA-06512: at "SYS.DBMS_JOB", line 100
ORA-06512: at line 1
About to enable constraints...
Import terminated successfully with warnings.

似乎是job package function没删干净是吗？

另外我想问这种备份恢复的方法有问题吗？

我还知道另一种恢复的方法：先drop掉整个用户，运行重建用户脚本，导入备份的dmp包，这两个方法那个更好？

andrew_young

我备份一般优先使用rman，不行的话用expdp/impdp

flutter

drop掉用户及其对象，重建用户，再导入