备份及恢复EAS测试服务器数据库

以下操作在测试服务器进行：标识黄色为替换点

1、备份正式数据库：

# su - oracle

$ expdp system/passwd schemas=jingyadirectory=EXPDP_DUMP dumpfile=jingya20130918.dmp logfile=expdp20130918.log parallel=4Cluster=n exclude=table:\" like \'VT%\'\",STATISTICS

注：备份文件名及日志文件名需要变动

 

2、创建文件夹：

mkdir /oracle/app/11g/oradata/jytest/jingya7

3、更改权限：

cd /oracle/app/11g/oradata/jytest

chown oracle:oinstall/oracle/app/11g/oradata/jytest/jingya7

4、进入sysdba

#su – oracle

$sqlplus / as sysdba

5、创建表空间

CREATE smallfile TABLESPACE EAS_D_jingya7_STANDARDDATAFILE

 '/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD01.ora'SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,

 '/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD02.ora'SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,

 '/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD03.ora'SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,

 '/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD04.ora'SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,

 '/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD05.ora'SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,

 '/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD06.ora'SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,

 '/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD07.ora'SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,

 '/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD08.ora'SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,

 '/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD09.ora'SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,

 '/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD10.ora'SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,

 '/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD11.ora'SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,

 '/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD12.ora'SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,

 '/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD13.ora'SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,

 '/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD14.ora'SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,

 '/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD15.ora'SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,

 '/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD16.ora'SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,

 '/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD17.ora'SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,

 '/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD18.ora'SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,

 '/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD19.ora'SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,

 '/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD20.ora'SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED,

 '/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_STANDARD21.ora'SIZE 20G AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED

LOGGING

ONLINE

PERMANENT

EXTENT MANAGEMENT LOCAL AUTOALLOCATE

BLOCKSIZE 8K

SEGMENT SPACE MANAGEMENT AUTO

FLASHBACK OFF;

6、创建临时表空间之一

CREATE smallfile TABLESPACE EAS_D_jingya7_TEMP2DATAFILE

 '/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_TEMP201.dbf' SIZE5G AUTOEXTEND ON NEXT 64M MAXSIZE UNLIMITED,

 '/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_TEMP202.dbf' SIZE5G AUTOEXTEND ON NEXT 64M MAXSIZE UNLIMITED,

 '/oracle/app/11g/oradata/jytest/jingya7/EAS_D_jingya7_TEMP203.dbf' SIZE5G AUTOEXTEND ON NEXT 64M MAXSIZE UNLIMITED

NOLOGGING

ONLINE

PERMANENT

EXTENT MANAGEMENT LOCAL AUTOALLOCATE

BLOCKSIZE 8K

SEGMENT SPACE MANAGEMENT AUTO

FLASHBACK OFF;

7、创建临时表空间之二

CREATE smallfile TEMPORARY TABLESPACEEAS_T_jingya7_STANDARD TEMPFILE

 '/oracle/app/11g/oradata/jytest/jingya7/EAS_T_jingya7_STANDARD01.dbf'SIZE 2G AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED,

 '/oracle/app/11g/oradata/jytest/jingya7/EAS_T_jingya7_STANDARD02.dbf'SIZE 2G AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED,

 '/oracle/app/11g/oradata/jytest/jingya7/EAS_T_jingya7_STANDARD03.dbf'SIZE 2G AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED

TABLESPACE GROUP ''

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 5M;

8、创建用户

CREATE USER jingya7

 IDENTIFIED BY VALUES 'passwd'

 DEFAULT TABLESPACE EAS_D_jingya7_STANDARD

 TEMPORARY TABLESPACE EAS_T_jingya7_STANDARD

 PROFILE DEFAULT

 ACCOUNT UNLOCK;

  --22 System Privileges for jingya7

 GRANT CREATE VIEW TO jingya7;

 GRANT DELETE ANY TABLE TO jingya7;

 GRANT ALTER ANY TABLE TO jingya7;

 GRANT UNLIMITED TABLESPACE TO jingya7;

 GRANT ALTER ANY PROCEDURE TO jingya7;

 GRANT DROP ANY TABLE TO jingya7;

 GRANT INSERT ANY TABLE TO jingya7;

 GRANT SELECT ANY DICTIONARY TO jingya7;

 GRANT CREATE ANY VIEW TO jingya7;

 GRANT CREATE TRIGGER TO jingya7;

 GRANT DROP ANY PROCEDURE TO jingya7;

 GRANT UPDATE ANY TABLE TO jingya7;

 GRANT CREATE SESSION TO jingya7;

 GRANT CREATE SEQUENCE TO jingya7;

 GRANT CREATE ANY INDEX TO jingya7;

 GRANT CREATE PROCEDURE TO jingya7;

 GRANT CREATE TABLE TO jingya7;

 GRANT CREATE ANY PROCEDURE TO jingya7;

 GRANT DROP ANY VIEW TO jingya7;

 GRANT DROP ANY INDEX TO jingya7;

 GRANT SELECT ANY TABLE TO jingya7;

 GRANT CREATE ANY TABLE TO jingya7;

  --1 Object Privilege for jingya7

   GRANT READ, WRITE ON DIRECTORY SYS.DMPDIR TO jingya7;

9、导入备份数据：

$impdp system/oracle REMAP_SCHEMA=jingya:jingya7REMAP_TABLESPACE=EAS_D_JINGYA_STANDARD:EAS_D_jingya7_STANDARD,EAS_D_JINGYA_TEMP2:EAS_D_jingya7_TEMP2,EAS_T_JINGYA_STANDARD:EAS_T_jingya7_STANDARDdirectory=EXPDP_DUMP dumpfile=jingya20131213.dmp logfile=impdp20131218.log parallel=4

 

10.删除数据库的表空间和用户

DROPTABLESPACE EAS_D_JINGYA2_STANDARDINCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

DROPTABLESPACE EAS_D_JINGYA2_TEMP2INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

DROPTABLESPACE EAS_T_JINGYA2_STANDARDINCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

DROPUSER JINGYA2CASCADE;