oracle物理备份和逻辑备份理解
逻辑备份 ：就是逻辑导出数据（expdp/impdp)只是对数据进行转存，所以恢复是只能恢复到备份时保存的数据，
逻辑备份适合备份那些变化很少的表。
缺点：恢复时间长优点：逻辑备份和平台无关，所以是数据迁移的重要手段
物理备份：又分为两种
冷备份：需要一致性停库，然后拷贝数据文件，日志文件，控制文件停库前，
需要通过v$datafile,v$logfile,v$controlfile 确定文件路径
非open的数据库
优点：执行简单，可靠性依存cp
缺点：停止数据库，整体备份
热备份：前提条件：数据库必须运行中处于活动中并运行归档模式
分为用户管理备份和oracle管理备份
用户管理备份：是指用户通过将表空间置于热备份模式，然后通过操作系统工具进行拷贝，拷贝结束后表空间热备份模式。
10g之前 只能对表空间逐备份alter tablespace 表空间名 begin backup；
alter tablespace 表空间名 end backup ；
10g中可以对数据库置于热备状态 alter database begin backup
alter database end backup
1.表空间单独存盘
2.冻结文件头 其他块继续使用此时拷贝只有文件头是好块
3.改变了日志的行为
1. 验证表空间单独存盘
[email protected]>col name for a50
[email protected]>select name,checkpoint_change# from v$datafile;
NAME CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/u01/app/oracle/oradata/ORCL/system01.dbf 626426
/u01/app/oracle/oradata/ORCL/undotbs01.dbf 626426
/u01/app/oracle/oradata/ORCL/sysaux01.dbf 626426
/u01/app/oracle/oradata/ORCL/users01.dbf 626426
/u01/app/oracle/oradata/ORCL/example01.dbf 626426
/u01/app/oracle/oradata/ORCL/block.dbf 626426
6 rows selected.
2. 查看当前系统SCN号
[email protected]>select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
626513
3. 修改users表空间为热备状态
[email protected]>alter tablespace users begin backup;
Tablespace altered.
4. 查看当前表空间的SCN号，此时users表空间头部的SCN号已经被冻结
[email protected]>select name,checkpoint_change# from v$datafile;
NAME CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/u01/app/oracle/oradata/ORCL/system01.dbf 626426
/u01/app/oracle/oradata/ORCL/undotbs01.dbf 626426
/u01/app/oracle/oradata/ORCL/sysaux01.dbf 626426
/u01/app/oracle/oradata/ORCL/users01.dbf 626522
/u01/app/oracle/oradata/ORCL/example01.dbf 626426
/u01/app/oracle/oradata/ORCL/block.dbf 626426
6 rows selected.
5. 产生系统检查点 验证 users 表空间文件头SCN被冻结
[email protected]>alter system checkpoint;
System altered.
[email protected]>select name,checkpoint_change# from v$datafile;
NAME CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/u01/app/oracle/oradata/ORCL/system01.dbf 626543
/u01/app/oracle/oradata/ORCL/undotbs01.dbf 626543
/u01/app/oracle/oradata/ORCL/sysaux01.dbf 626543
/u01/app/oracle/oradata/ORCL/users01.dbf 626522
/u01/app/oracle/oradata/ORCL/example01.dbf 626543
/u01/app/oracle/oradata/ORCL/block.dbf 626543
6 rows selected.
6. 查看当前备份的文件状态，发现file# 4 文件 也就是users 表空间对应的文件活动状态，说明需要进行介质恢复
[email protected]>select * from v$backup;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
1 NOT ACTIVE 0
2 NOT ACTIVE 0
3 NOT ACTIVE 0
4 ACTIVE 626522 06-MAR-14
5 NOT ACTIVE 0
6 NOT ACTIVE 0
6 rows selected.
[email protected]>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'
[email protected]>startup force
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 79693392 bytes
Database Buffers 201326592 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: '/u01/app/oracle/oradata/ORCL/users01.dbf'
7. 结束users 表空间的热备状态
[email protected]>alter tablespace users end backup;
Tablespace altered.
8.查看当前备份的文件状态，此时file# 4 也就是users 表空间对应的文件已经 不在活动
[email protected]>select * from v$backup;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
1 NOT ACTIVE 0
2 NOT ACTIVE 0
3 NOT ACTIVE 0
4 NOT ACTIVE 626522 06-MAR-14
5 NOT ACTIVE 0
6 NOT ACTIVE 0
6 rows selected.
[email protected]>select name,checkpoint_change# from v$datafile;
NAME CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/u01/app/oracle/oradata/ORCL/system01.dbf 626543
/u01/app/oracle/oradata/ORCL/undotbs01.dbf 626543
/u01/app/oracle/oradata/ORCL/sysaux01.dbf 626543
/u01/app/oracle/oradata/ORCL/users01.dbf 626543
/u01/app/oracle/oradata/ORCL/example01.dbf 626543
/u01/app/oracle/oradata/ORCL/block.dbf 626543
6 rows selected.
9.打开数据库
[email protected]>alter database open;
Database altered.
[email protected]>select * from v$backup;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
1 NOT ACTIVE 0
2 NOT ACTIVE 0
3 NOT ACTIVE 0
4 NOT ACTIVE 626522 06-MAR-14
5 NOT ACTIVE 0
6 NOT ACTIVE 0
6 rows selected.