Oracle备份、还原数据库
程序员文章站
2022-04-21 18:14:22
备份数据库 创建备份目录(用sys账号),若已创建备份目录,此步可忽略 create directory db_bak as 'D:\ ECIMS_DB' --查看创建的目录 select * from dba_directories --删除已创建的目录 drop directory DB_BAK ......
备份数据库
创建备份目录(用sys账号),若已创建备份目录,此步可忽略
create directory db_bak as 'd:\ ecims_db'
--查看创建的目录 select * from dba_directories --删除已创建的目录 drop directory db_bak 格式: drop directory 目录名
备份(导出)数据库(cmd状态下)
expdp xxx/xxx@xxx schemas=xxx dumpfile=xxx_20181130.dump logfile=xxx_20181130.log directory=db_bak 语法: expdp 用户名/密码@实例名 schemas=用户名 dumpfile=导出dump文件名.dump logfile=导出日志文件名.log directory=db_bak
导入数据库
步骤一、导入前,先删除账号(plsql状态下)
drop user xxx cascade; 格式: drop user 用户名 cascade;
注:若删除不掉,需先删除所有会话!!!
select username, sid, serial# from v$session where username='xxx' --找到用户session 格式: select username, sid, serial# from v$session where username='用户名' --找到用户session 注:若有多条会员,需批量删除 alter system kill session '249,57377' --杀掉用户session 'sid,serial#' alter system kill session '250,57376' --杀掉用户session 'sid,serial#' alter system kill session '251,57375' --杀掉用户session 'sid,serial#' 格式: alter system kill session 'sid,serial'
步骤二、创建账号,赋予权限(plsql状态下)
create user xxx identified by xxx default tablespace users temporary tablespace temp profile default; -- grant/revoke role privileges grant connect to xxx; grant dba to xxx; grant resource to xxx; -- grant/revoke system privileges grant alter any sequence to xxx; grant alter any table to xxx; grant alter any trigger to xxx; grant change notification to xxx; grant create any procedure to xxx; grant create any sequence to xxx; grant create any table to xxx; grant create any type to xxx; grant create any view to xxx; grant unlimited tablespace to xxx; -------------------------------------------------------------- 格式: create user 用户名 identified by 密码 default tablespace users temporary tablespace temp profile default; -- grant/revoke role privileges grant connect to 用户名; grant dba to 用户名; grant resource to 用户名; -- grant/revoke system privileges grant alter any sequence to 用户名; grant alter any table to 用户名; grant alter any trigger to 用户名; grant change notification to 用户名; grant create any procedure to 用户名; grant create any sequence to 用户名; grant create any table to 用户名; grant create any type to 用户名; grant create any view to 用户名; grant unlimited tablespace to 用户名;
步骤三、导入数据(cmd状态下)
impdp xxx/xxx directory=db_bak dumpfile=xxx.dump logfile=xxx.log remap_schema=xxx:xxx remap_tablespace=xxx:xxx 格式 impdp 用户名/密码 directory=db_bak dumpfile=备份文件名.dump logfile=备份日志文件名.log remap_schema=导出用户名:导入用户名 remap_tablespace=导出表空间:导入表空间