oracle数据库备份与还原完整版

--切换数据库角色
su - orcale
--创建表空间
create tablespace TESTSIT 
datafile '/data/oracle/oradata/TESTSIT.DBF' 
size 128m 
autoextend on 
next 
50m maxsize unlimited;

create tablespace NNC_DATA01 
datafile '/data/oracle/oradata/NNC_DATA01.DBF' 
size 128m 
autoextend on 
next 
50m maxsize unlimited;

--创建用户
create user test identified by test
default tablespace TESTSIT 
temporary tablespace TEMP
profile DEFAULT;

--赋权
GRANT "DBA" TO "test";
grant connect to test;
grant resource to test;
grant create database link to test;
grant create job to test;
grant create procedure to test;
grant create public database link to test;
grant create synonym to test;
grant create table to test;
grant create view to test;
grant debug connect session to test;
grant select any dictionary to test;
grant unlimited tablespace to test;


--创建备份文件夹
drop directory kmdata_exp ;
create or replace directory kmdata_exp as '/data/oracle/backup';

grant read,write on directory kmdata_exp to public ;
mkdir /data/oracle/backup;
chown -R oracle:oinstall /data/oracle/backup;
--查看源数据库文件编码
select * from v$nls_parameters where parameter='NLS_CHARACTERSET';

--如果编码不一致，可修改待客户端编码（先备份）
SQL> conn /as sysdba
SQL> shutdown immediate; 
SQL> startup mount 
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION; 
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0; 
SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0; 
SQL> alter database open; 
SQL> ALTER DATABASE character set INTERNAL_USE AL32UTF8;

--修改完毕后，再重新启动
SQL> select * from v$nls_parameters;  
SQL> shutdown immediate; 
SQL> startup 
SQL> select * from v$nls_parameters; 

--导入备份数据库
--数据和索引分开导入
impdp test/test directory=kmdata_exp table_exists_action=truncate dumpfile=back-0114.dmp logfile=expdp.log ignore=y exclude=index exclude=constraint version=19.0.0.0.0
impdp test/test directory=kmdata_exp table_exists_action=truncate dumpfile=back-0114.dmp logfile=expdp.log ignore=y include=index include=constraint version=19.0.0.0.0