Oracle 备份与恢复学习笔记（2）

原创

客居天涯 2012-02-29 09:57:31 博主文章分类：数据库技术 ©著作权

文章标签 oracle 职场休闲 Oracle study 文章分类 Oracle 数据库

©著作权归作者所有：来自51CTO博客作者客居天涯的原创作品，请联系作者获取转载授权，否则将追究法律责任

第二章：备份恢复原理

1、Oracle server ，Instance、oracle database、user process、server process、session、sga 、pga 的定义

2、share pool、data buffer、log buffer

large pool 的功能：在做备份和恢复时需要large pool的支持

3、redo 日志文件的管理（redo log group），归档（备份历史日志）和非归档模式（不备份历史日志）

4、checkpoint 概念：recover的起点
1） full checkpoint ：所有的脏块都写完，再将scn 写入到控制文件和datafile、redo file-----------正常关闭实例或手工生成检查点（alter system checkpoint)
09:55:26 SQL> select file#,checkpoint_change# from v$datafile_header;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            1086165
         2            1086165
         3            1086165
         4            1086165
         5            1086165
         6            1086172
         7            1086165
         8            1086165
         9            1086165
        10            1086165
        11            1086165

11 rows selected.

09:55:28 SQL> alter system checkpoint;

System altered.

09:55:41 SQL> select file#,checkpoint_change# from v$datafile_header;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            1086203
         2            1086203
         3            1086203
         4            1086203
         5            1086203
         6            1086203
         7            1086203
         8            1086203
         9            1086203
        10            1086203
        11            1086203

     2） incremental checkpoint（增量检查点）：每过3s ，检查checkpoint 队列，查看脏块的写入情况，并记录之前最后一个脏块的scn 写入到controlfile


     3） partial checkpoint：当对tablespace 做以下操作时：如offline、readonly 、backup 时，在tablespace 对应的数据文件上建立检查点（写入scn）
     09:54:38 SQL> alter tablespace test offline;

Tablespace altered.

09:54:46 SQL> select file#,checkpoint_change# from v$datafile_header;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            1082763
         2            1082763
         3            1082763
         4            1082763
         5            1082763
         6                  0
         7            1082763
         8            1082763
         9            1082763
        10            1082763
        11            1082763

11 rows selected.

09:54:54 SQL> alter system checkpoint;

System altered.

09:55:13 SQL> select file#,checkpoint_change# from v$datafile_header;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            1086165
         2            1086165
         3            1086165
         4            1086165
         5            1086165
         6                  0
         7            1086165
         8            1086165
         9            1086165
        10            1086165
        11            1086165

11 rows selected.

09:55:15 SQL> alter tablespace test online;

Tablespace altered.

09:55:26 SQL> select file#,checkpoint_change# from v$datafile_header;

11 rows selected.

09:55:28 SQL> alter system checkpoint;

System altered.

09:55:41 SQL> select file#,checkpoint_change# from v$datafile_header;

11 rows selected.

-------------设置表空间为read only 模式，会在数据文件上写入检查点信息
09:56:20 SQL> alter tablespace test read only;

Tablespace altered.

09:56:23 SQL> select file#,checkpoint_change# from v$datafile_header;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            1086203
         2            1086203
         3            1086203
         4            1086203
         5            1086203
         6            1086219
         7            1086203
         8            1086203
         9            1086203
        10            1086203
        11            1086203

11 rows selected.

09:56:27 SQL> alter system checkpoint;

System altered.

09:56:37 SQL> select file#,checkpoint_change# from v$datafile_header;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            1086231
         2            1086231
         3            1086231
         4            1086231
         5            1086231
         6            1086219
         7            1086231
         8            1086231
         9            1086231
        10            1086231
        11            1086231

11 rows selected.

09:57:02 SQL> alter tablespace test read write;

Tablespace altered.

09:57:04 SQL> select file#,checkpoint_change# from v$datafile_header;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            1086231
         2            1086231
         3            1086231
         4            1086231
         5            1086231
         6            1086252
         7            1086231
         8            1086231
         9            1086231
        10            1086231
        11            1086231

11 rows selected.

09:57:06 SQL> alter system checkpoint;

System altered.

09:57:13 SQL> select file#,checkpoint_change# from v$datafile_header;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            1086260
         2            1086260
         3            1086260
         4            1086260
         5            1086260
         6            1086260
         7            1086260
         8            1086260
         9            1086260
        10            1086260
        11            1086260

11 rows selected.

09:57:14 SQL>

5、dbwr、lgwr、ckpt、smon、pmon、arch 的功能
1）ckpt：检查点事件发生时，会启动ckpt ，然后ckpt通知dbwn 写脏块，在写脏块之前，通知lgwr 写redo entries；
并将未提交的事务回滚，完成后会在controlfile、data file 以及redo file 写入scn。

-----------database synchronization 数据库同步
SCN ：system change number

--------system scn（记录在controlfile）

10:02:11 SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
1086260

-------------datafile scn （记录在controlfile）

10:02:59 SQL> select file#,checkpoint_change# from v$datafile;

11 rows selected.

----------datafile stop scn
（记录在controlfile）

10:03:02 SQL> select file#,checkpoint_change#,last_change# from v$datafile;

     FILE# CHECKPOINT_CHANGE# LAST_CHANGE#
---------- ------------------ ------------
         1            1086260
         2            1086260
         3            1086260
         4            1086260
         5            1086260
         6            1086260
         7            1086260
         8            1086260
         9            1086260
        10            1086260
        11            1086260

11 rows selected.

-------------正常关库
10:10:31 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
10:10:57 SQL> startup mount
ORACLE instance started.

Total System Global Area 314572800 bytes
Fixed Size                  1219184 bytes
Variable Size              96470416 bytes
Database Buffers          213909504 bytes
Redo Buffers                2973696 bytes
Database mounted.
10:11:06 SQL> select file#,checkpoint_change# ,last_change# from v$datafile;

     FILE# CHECKPOINT_CHANGE# LAST_CHANGE#
---------- ------------------ ------------
         1            1087187      1087187
         2            1087187      1087187
         3            1087187      1087187
         4            1087187      1087187
         5            1087187      1087187
         6            1087187      1087187
         7            1087187      1087187
         8            1087187      1087187
         9            1087187      1087187
        10            1087187      1087187
        11            1087187      1087187

11 rows selected.

----------非正常关库
10:12:00 SQL> select file#,checkpoint_change# ,last_change# from v$datafile;

     FILE# CHECKPOINT_CHANGE# LAST_CHANGE#
---------- ------------------ ------------
         1            1087188
         2            1087188
         3            1087188
         4            1087188
         5            1087188
         6            1087188
         7            1087188
         8            1087188
         9            1087188
        10            1087188
        11            1087188

11 rows selected.

10:12:05 SQL> show parameter alert

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_checkpoints_to_alert             boolean     FALSE
10:12:16 SQL> alter system set log_checkpoints_to_alert=true;

System altered.

10:12:26 SQL> shutdown abort
ORACLE instance shut down.
10:12:31 SQL> startup mount
ORACLE instance started.

Total System Global Area 314572800 bytes
Fixed Size                  1219184 bytes
Variable Size              96470416 bytes
Database Buffers          213909504 bytes
Redo Buffers                2973696 bytes
Database mounted.
10:12:41 SQL> select file#,checkpoint_change# ,last_change# from v$datafile;

11 rows selected.

10:12:49 SQL> alter database open;

Database altered.

10:13:01 SQL> select file#,checkpoint_change# ,last_change# from v$datafile;

     FILE# CHECKPOINT_CHANGE# LAST_CHANGE#
---------- ------------------ ------------
         1            1108038
         2            1108038
         3            1108038
         4            1108038
         5            1108038
         6            1108038
         7            1108038
         8            1108038
         9            1108038
        10            1108038
        11            1108038

11 rows selected.

10:13:35 SQL>

---------last_change# 在database open 状态下是一个null 或无穷大的值
当database 正常关闭时，last_change#会和start scn 保持一致；如果非正常关闭，仍然是一个空值或无穷大的值，这时候在启动instance，smon 需要做instance recover。

----------datafile scn（记录在datafile 头部的scn ，也叫start scn）
10:03:53 SQL> select file#,checkpoint_change# from v$datafile_header;

11 rows selected.

---------database的一致性，指的是在open database 时，记录在controlfile的system scn 和 datafile scn 以及在数据文件头部的start scn 应该保持一致，在一致的情况下
database可以正常打开，如不不一致，需要做media recover。

6、Instance Recover 的参数：
   1）fast_start_mttr_target ：设置生成检查点的间隔时间，实现instance 的快速recover。

   2）recovery_parallelism：recover的并行度
   10:17:38 SQL> show parameter recover

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/flash_recovery
                                                 _area
db_recovery_file_dest_size           big integer 2G
recovery_parallelism                 integer     0
10:18:59 SQL> alter system set recovery_parallelism=2 scope=spfile;

System altered.

10:19:04 SQL> startup force;
ORACLE instance started.

Total System Global Area 314572800 bytes
Fixed Size                  1219184 bytes
Variable Size              96470416 bytes
Database Buffers          213909504 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
10:19:19 SQL> show parameter recover

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/flash_recovery
                                                 _area
db_recovery_file_dest_size           big integer 2G
recovery_parallelism                 integer     2
----------提高并行度，加快roll forward的速度

3） fast_start_parallel_rollback ：roll back的并行度
10:19:23 SQL> show parameter fast

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fast_start_io_target                 integer     0
fast_start_mttr_target               integer     0
fast_start_parallel_rollback         string      LOW
---------low 启动的slave process 数是cpu 个数的 2倍

10:21:11 SQL> alter system set fast_start_parallel_rollback=high;

System altered.

10:22:08 SQL> show parameter fast

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fast_start_io_target                 integer     0
fast_start_mttr_target               integer     0
fast_start_parallel_rollback         string      HIGH
10:22:09 SQL>
----------high 启动的slave process 是cpu 个数的 4倍，加快roll back 的速度