Oracle数据库备份

Oracle 数据库备份

1.用户及表空间相关指令

1.表空间

--给用户创建表空间
create tablespace TestUser /*--默认表空间*/　　　　　　　　　　
datafile 'D:\Environments\O19C\oradata\O19C\TestUser.dbf'   
size 1024m					
autoextend on                 
next 100m ;

--删除表空间
drop tablespace TestUser including contents and datafiles;

2.用户

--创建用户 grant connect, resource , dba to xxx
create user TestUser identified by 1;
--一般不给用户dba权限
grant connect, resource   to TestUser;
alter user TestUser DEFAULT TABLESPACE TestUser;
alter user TestUser TEMPORARY TABLESPACE TEMP;

--修改用户名和密码
alter user 用户名 identified by "新密码"；

--删除用户
drop user TestUser cascade;

2.impdp/expdp导入导出

1.本地路径创建

create or replace directory ORACLE_BAK as 'D:\oracle_back';

2.导出数据库

--version不指定使用系统版本
expdp sys/1@127.0.0.1:1521/orcl  schemas=TestUser DIRECTORY=ORACLE_BAK DUMPFILE=TestUser.dmp LOGFILE=TestUser.LOG  
version=11.2.0.1;

远程导出数据库

--建立数据库Link
create public database link LN[LinkName] connect to RM[Remote_dbName] identified by 1[Remote_dbPwd] 
using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)
(HOST = [Remote_dbHost])(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME
= orcl)))';
--通过当前用户远程导出Remote用户
expdp sys/1@127.0.0.1:1521/orcl schemas=RM directory=ORACLE_BAK dumpfile=RM.dmp  network_link=LN logfile=RM.log;

3.导入数据库

--create or replace directory ORACLE_BACK as 'E:\oracle_back';
select * from dba_directories;

impdp sys/1@127.0.0.1:1521/orcl directory=ORACLE_BAK dumpfile=RM.dmp  remap_schema=RM:TestUser remap_tablespace=User:TestUser    logfile = TestUser.log
full=y;

3.批量杀死用户连接,删除用户

--通过查询直接列出该用户的所有连接,执行后杀死
select 'alter system kill session '''||sid ||','||serial#||''';',username,status from v$session where username='UserTest';

--通过表空间,批量删除用户
select 'drop user '||USERNAME||' cascade;',username from dba_users where default_tablespace  like '%TEST%' order  BY USERNAME DESC;