【武汉引航】Oracle物理备份与逻辑备份
Oracle备份分为 两种
逻辑备份：就是逻辑导出数据（expdp/impdp)只是对数据进行转存，所以恢复是只能恢复到备份时保存的数据，
逻辑备份适合备份那些变化很少的表。
物理备份
：是指通过备份物理文件拷贝的方式对数据库进行备份.
物理备份：又分为两种
冷备份：需要一致性停库，然后拷贝数据文件，日志文件，控制文件停库前，
需要通过v$datafile,v$logfile,v$controlfile 确定文件路径
非open的数据库
优点：执行简单，可靠性依存cp
缺点：停止数据库，整体备份
热备份：前提条件：数据库必须运行中处于活动中并运行归档模式
分为用户管理备份和oracle管理备份
用户管理备份：是指用户通过将表空间置于热备份模式，然后通过操作系统工具进行拷贝，拷贝结束后表空间热备份模式。
1.表空间单独存盘
2.冻结文件头 其他块继续使用此时拷贝只有文件头是好块
3.改变了日志的行为
1. 验证表空间单独存盘
SYS@ORCL>col name for a50
SYS@ORCL>select name,checkpoint_change# from
v$datafile;
NAME
--------------------------------------------------
------------------
/u01/app/oracle/oradata/ORCL/system01.dbf
/u01/app/oracle/oradata/ORCL/undotbs01.dbf
/u01/app/oracle/oradata/ORCL/sysaux01.dbf
/u01/app/oracle/oradata/ORCL/users01.dbf
/u01/app/oracle/oradata/ORCL/example01.dbf
/u01/app/oracle/oradata/ORCL/block.dbf
6 rows selected.
2. 查看当前系统SCN号
SYS@ORCL>select dbms_flashback.get_system_change_number from
dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
3. 修改users表空间为热备状态
SYS@ORCL>alter tablespace users begin backup;
Tablespace altered.
4. 查看当前表空间的SCN号，此时users表空间头部的SCN号已经被冻结
SYS@ORCL>select name,checkpoint_change# from
v$datafile;
NAME
--------------------------------------------------
------------------
/u01/app/oracle/oradata/ORCL/system01.dbf
/u01/app/oracle/oradata/ORCL/undotbs01.dbf
/u01/app/oracle/oradata/ORCL/sysaux01.dbf
/u01/app/oracle/oradata/ORCL/users01.dbf
/u01/app/oracle/oradata/ORCL/example01.dbf
/u01/app/oracle/oradata/ORCL/block.dbf
6 rows selected.
5. 产生系统检查点 验证 users 表空间文件头SCN被冻结
SYS@ORCL>alter system checkpoint;
System altered.
SYS@ORCL>select name,checkpoint_change# from
v$datafile;
NAME
--------------------------------------------------
------------------
/u01/app/oracle/oradata/ORCL/system01.dbf
/u01/app/oracle/oradata/ORCL/undotbs01.dbf
/u01/app/oracle/oradata/ORCL/sysaux01.dbf
/u01/app/oracle/oradata/ORCL/users01.dbf
/u01/app/oracle/oradata/ORCL/example01.dbf
/u01/app/oracle/oradata/ORCL/block.dbf
6 rows selected.
6. 查看当前备份的文件状态，发现file# 4 文件 也就是users
表空间对应的文件活动状态，说明需要进行介质恢复
SYS@ORCL>select * from
v$backup;
---------- ------------------ ---------- ---------
6 rows selected.
SYS@ORCL>shutdown immediate //此时用Immediate方式关库报错
ORA-01149: cannot shutdown - file 4 has online backup set
ORA-01110: data file 4:
'/u01/app/oracle/oradata/ORCL/users01.dbf'
SYS@ORCL>startup
force
ORACLE instance started.
Total System Global
Area
Fixed Size
Variable Size
Database Buffers
Redo Buffers
Database mounted.
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4:
'/u01/app/oracle/oradata/ORCL/users01.dbf'
7. 结束users 表空间的热备状态
SYS@ORCL>alter tablespace users end backup;
Tablespace altered.
8.查看当前备份的文件状态，此时file# 4 也就是users 表空间对应的文件已经 不在活动
SYS@ORCL>select * from v$backup;
---------- ------------------ ---------- ---------
6 rows selected.
SYS@ORCL>select name,checkpoint_change# from
v$datafile;
NAME
--------------------------------------------------
------------------
/u01/app/oracle/oradata/ORCL/system01.dbf
/u01/app/oracle/oradata/ORCL/undotbs01.dbf
/u01/app/oracle/oradata/ORCL/sysaux01.dbf
/u01/app/oracle/oradata/ORCL/users01.dbf
/u01/app/oracle/oradata/ORCL/example01.dbf
/u01/app/oracle/oradata/ORCL/block.dbf
6 rows selected.
9.打开数据库
SYS@ORCL>alter database open;
Database altered.
SYS@ORCL>select * from v$backup;
---------- ------------------ ---------- ---------
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/23328192/viewspace-1114718/ ，如需转载，请注明出处，否则将追究法律责任。
