备份类型:逻辑备份 / 物理备份(冷、热)
逻辑备份
ORACLE 9i 脚本在哪里运行,文件就产生在哪里。exp对应imp,确保服务器与客户端字符集一致
exp Grants=Y Compress=Y Consistent =Y Indexes = Y Constraints =Y Rows = Y Triggers =Y file= d:\file.dmp tables=(dept,emp)
imp Analyze=Y Commit=Y Constraints=Y Grants=Y Ignore=Y Indexes=Y Rows=Y file= d:\file.dmp tables=(dept,emp)
ORACLE 10g 脚本只产生在服务器上。expdp对应impdp
1. 创建ORACLE Directory
OS:mkdir /tmp/movedata
sys用户:
SQL>grant create any directory to hr;
hr用户:
SQL>create or replace directory datamove as '/tmp/movedata';
查看:select * from dba_directories
删除:drop directory datamove
2. 导出:
全库:expdp system/DIRECTORY=datamove DUMPFILE=expfull.dmp FULL=y LOGFILE=expfull.log
SCHEMA:expdp system/DIRECTORY=datamove DUMPFILE=schema_exp.dmp SCHEMAS=hr,sh,oe
table:expdp system/DIRECTORY=datamove DUMPFILE=tables_exp.dmp TABLES=employees,jobs,departments
1. 运行过程中可以暂停:Ctrl+c
2. 查看:select * from dba_datapump_jobs
3. 导入:
全库:impdp system/DIRECTORY=datamove DUMPFILE=expfull.dmp FULL=y LOGFILE=impfull.og
SCHEMA:impdp system/DIRECTORY=datamove DUMPFILE=schema_exp.dmp SCHEMAS=hr,sh,oe
table:impdp system/DIRECTORY=datamove DUMPFILE=tables_exp.dmp TABLES=employees,jobs,departments
4. 使用导入导出工具复制schema
a. 使用导出工具导出某个用户的schema:
expdp system/DIRECTORY=datamove DUMPFILE=schema_exp.dmp SCHEMAS=hr
b. 创建新的表空间,新用户
创建新的表空间:create tablespace test datafile '/u01/app/oracle/oradata/orcl11g/test01.dbf' size 100M autoextend on next 10M;
创建用户:
create user hr1
identified by hr1
default tablespace test
temporary tablespace TEMP;
c. 导入到另外一个用户,同一个表空间中(程序会自动将数据导入新用户hr1,但是仍旧是原来的表空间example)
impdp system/directory=datamove dumpfile=schema_exp.dmp schemas=hr remap_schema=hr:hr1
d. 导入到另外一个用户,另外一个表空间中
impdp system/directory=datamove dumpfile=schema_exp.dmp schemas=hr remap_schema=hr:hr1 remap_tablespace=example:test
e. 检查:select * from user_segments;
物理备份
冷备份:脱机备份,物理拷贝
1. shutdown数据库;
2. 复制控制文件,数据文件,日志文件;
热备份条件:
1. 将日志模式改成归档
SQL>alter system set log_archive_start=true scope=spfile;
2. 停止数据库
SQL>shutdown immediate;
3. 启动数据库
SQL>startup mount;
4. 启用归档方式
SQL>alter database archivelog;
SQL>alter database open;
5. 验证是否是归档方式
SQL>archive log list;
导出导入报错:
错误信息:
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 475
ORA-29283: invalid file operation
报错原因:directory对应的路径,oracle无法找到;
解决方法:重建directory