生产系统oracle备份及恢复

1、备份数据库

 

# su -oracle

$ expdpsystem/oracle schemas=jingya directory=EXPDP_DUMP dumpfile=jingya20130426.dmplogfile=expdp20130426.log parallel=4 Cluster=n exclude=table:\" like \'VT%\'\",STATISTICS

 

2、建表空间

 

$sqlplus / as sysdba

SQL>

 

CREATEsmallfile TABLESPACE EAS_D_JINGYASP_STANDARD DATAFILE

 SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZEUNLIMITED,

 SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZEUNLIMITED,

 SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZEUNLIMITED,

 SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZEUNLIMITED,

 SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZEUNLIMITED,

 SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZEUNLIMITED,

 SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZEUNLIMITED,

 SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZEUNLIMITED,

 SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZEUNLIMITED,

 SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZEUNLIMITED,

 SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZEUNLIMITED,

 SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZEUNLIMITED,

 SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZEUNLIMITED,

 SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZEUNLIMITED,

 SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZEUNLIMITED,

 SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZEUNLIMITED,

 SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,

 SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZEUNLIMITED,

 SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZEUNLIMITED,

 SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZEUNLIMITED,

 SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZEUNLIMITED,

 SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZEUNLIMITED,

 SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZEUNLIMITED,

 SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZEUNLIMITED,

 SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZEUNLIMITED

LOGGING

ONLINE

PERMANENT

EXTENTMANAGEMENT LOCAL AUTOALLOCATE

BLOCKSIZE8K

SEGMENTSPACE MANAGEMENT AUTO

FLASHBACKON;

 

CREATEsmallfile TABLESPACE EAS_D_JINGYASP_TEMP2 DATAFILE

 SIZE 5G AUTOEXTEND ON NEXT 64M MAXSIZEUNLIMITED,

 SIZE 5G AUTOEXTEND ON NEXT 64M MAXSIZEUNLIMITED,

 SIZE 5G AUTOEXTEND ON NEXT 64M MAXSIZEUNLIMITED

NOLOGGING

ONLINE

PERMANENT

EXTENTMANAGEMENT LOCAL AUTOALLOCATE

BLOCKSIZE8K

SEGMENTSPACE MANAGEMENT AUTO

FLASHBACKON;

 

CREATEsmallfile TEMPORARY TABLESPACE EAS_T_JINGYASP_STANDARD TEMPFILE

 SIZE 2G AUTOEXTEND ON NEXT 5M MAXSIZEUNLIMITED,

 SIZE 2G AUTOEXTEND ON NEXT 5M MAXSIZEUNLIMITED,

 SIZE 2G AUTOEXTEND ON NEXT 5M MAXSIZEUNLIMITED

TABLESPACEGROUP ''

EXTENTMANAGEMENT LOCAL UNIFORM SIZE 5M;

 

 

3、创建用户

CREATEUSER jingyaSP

  IDENTIFIED BY VALUES 'DCF8D58B8B578628'

  DEFAULT TABLESPACE EAS_D_jingyaSP_STANDARD

  TEMPORARY TABLESPACE EAS_T_jingyaSP_STANDARD

  PROFILE DEFAULT

  ACCOUNT UNLOCK;

  GRANT CREATE VIEW TO jingyaSP;

  GRANT DELETE ANY TABLE TO jingyaSP;

  GRANT ALTER ANY TABLE TO jingyaSP;

  GRANT UNLIMITED TABLESPACE TO jingyaSP;

  GRANT ALTER ANY PROCEDURE TO jingyaSP;

  GRANT DROP ANY TABLE TO jingyaSP;

  GRANT INSERT ANY TABLE TO jingyaSP;

  GRANT SELECT ANY DICTIONARY TO jingyaSP;

  GRANT CREATE ANY VIEW TO jingyaSP;

  GRANT CREATE TRIGGER TO jingyaSP;

  GRANT DROP ANY PROCEDURE TO jingyaSP;

  GRANT UPDATE ANY TABLE TO jingyaSP;

  GRANT CREATE SESSION TO jingyaSP;

  GRANT CREATE SEQUENCE TO jingyaSP;

  GRANT CREATE ANY INDEX TO jingyaSP;

  GRANT CREATE PROCEDURE TO jingyaSP;

  GRANT CREATE TABLE TO jingyaSP;

  GRANT CREATE ANY PROCEDURE TO jingyaSP;

  GRANT DROP ANY VIEW TO jingyaSP;

  GRANT DROP ANY INDEX TO jingyaSP;

  GRANT SELECT ANY TABLE TO jingyaSP;

  GRANT CREATE ANY TABLE TO jingyaSP;

  GRANT READ, WRITE ON DIRECTORY SYS.DMPDIR TOjingyaSP;

 

4、导入数据库

SQL>exit

$ impdpsystem/oracle REMAP_SCHEMA=jingya:jingyaSPREMAP_TABLESPACE=EAS_D_JINGYA_STANDARD:EAS_D_jingyaSP_STANDARD,EAS_D_JINGYA_TEMP2:EAS_D_jingyaSP_TEMP2,EAS_T_JINGYA_STANDARD:EAS_T_jingyaSP_STANDARDdirectory=EXPDP_DUMP dumpfile=jingya20130426.dmp logfile=impdp20130426.logparallel=4 exclude=STATISTICS

 

 

导入过程中报错，报错信息是：

Additionalinformation: 3

ORA-31693:Table data object "JINGYASP"."T_GL_VOUCHERASSISTRECORD"failed to load/unload and is being skipped due to error:

ORA-31640:unable to open dump file "/oraback/jingya20130426.dmp" for read

ORA-19505:failed to identify file "/oraback/jingya20130426.dmp"

ORA-27037:unable to obtain file status

IBM AIXRISC System/6000 Error: 2: No such file or directory

 

从网上查询信息，原因可能是数据文件及目录没有权限。但通过查看文件权限是没有问题的。那就剩一种可能性应该是impdp导入语句中缺少  Cluster=n 这个参数

 

需要将impdp这个命令停掉,此时按组合快捷键 CTRL+C,继续执行以下命令

 

Import> 

Import>exit

$

 

但通过more jingya20130426.dmp 仍有日志在写说明这个进程没有停掉，也就说CTRL+C 只是退出当前交互模式，并没有真正的kill 掉impdp这个进程。通过上网查询资料，可以在交互模式下执行STOP_JOB 命令来终止进程，但目前的情况是我已经exit退出交互模式，那如何在回到交互模式？

 

通过查询资料：EXPDP和IMPDP的本质在于，通过API调用，把传统的EXP/IMP类交付式模式的操作，转变成数据库内部的job任务，从而实现了任务的可终止与重启动。因此，只要找到数据库中

impdp正在运行这个job就可以实现，到底现在的job如何去找呢？

 

用plsql链接数据库，执行以下语句：

 

SQL>select* from DBA_DATAPUMP_JOBS --查看job

 

得到JOB_NAME=SYS_IMPORT_FULL_01

 

（在plsq下查看对应session:

 

SQL>selectsid, serial#

 2  fromv$session s, dba_datapump_sessions d

 3  wheres.saddr = d.saddr;）

 

再执行以下语句

 

impdpsystem/oracle（参数与执行中的对应） attach=SYS_IMPORT_FULL_01

重新回到交互模式

Import> 

执行命令

Import>  STOP_JOB

Are yousure you wish to stop this job ([yes]/no):Y

 

 

到目前为止停止impdp进程结束！

 

5、删除之前所建立的表空间和用户

$sqlplus / as sysdba

SQL>

DROPTABLESPACE EAS_D_JINGYASP_STANDARD INCLUDING CONTENTS AND DATAFILES CASCADECONSTRAINTS;

DROPTABLESPACE EAS_D_JINGYASP_TEMP2 INCLUDING CONTENTS AND DATAFILES CASCADECONSTRAINTS;

DROPTABLESPACE EAS_T_JINGYASP_STANDARD INCLUDING CONTENTS AND DATAFILES CASCADECONSTRAINTS;

DROPUSER JINGYASP CASCADE;

 

特别关注：上面的语句如果只执行，DROPUSER JINGYASP CASCADE;可以把用户删除，然后表空间中的数据删除，表空间还在。表面上看这种删除方式即简单，后期的工作量又小（无需重建表空间，只需创建用户即可），但这是最慢最慢最慢的一种方式，一定要记住，严格按照上面的语句顺序进行执行是最快最快的方式。

 

6、重复上面3、4两个步骤

7、重新导入数据库

SQL>exit

$ impdpsystem/oracle REMAP_SCHEMA=jingya:jingyaSPREMAP_TABLESPACE=EAS_D_JINGYA_STANDARD:EAS_D_jingyaSP_STANDARD,EAS_D_JINGYA_TEMP2:EAS_D_jingyaSP_TEMP2,EAS_T_JINGYA_STANDARD:EAS_T_jingyaSP_STANDARDdirectory=EXPDP_DUMP dumpfile=jingya20130426.dmp logfile=impdp20130427.logparallel=4 Cluster=n