oracle物理备份和逻辑备份理解

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. 验证表空间单独存盘

SYS@ORCL>col name for a50
SYS@ORCL>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号

SYS@ORCL>select dbms_flashback.get_system_change_number from dual;


GET_SYSTEM_CHANGE_NUMBER
------------------------
626513

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 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被冻结

SYS@ORCL>alter system checkpoint;


System altered.


SYS@ORCL>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 表空间对应的文件活动状态，说明需要进行介质恢复

SYS@ORCL>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.


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 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 表空间的热备状态

SYS@ORCL>alter tablespace users end backup;


Tablespace altered.

8.查看当前备份的文件状态，此时file# 4 也就是users 表空间对应的文件已经 不在活动

SYS@ORCL>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.


SYS@ORCL>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.打开数据库

SYS@ORCL>alter database open;


Database altered.


SYS@ORCL>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.