冷备-物理备份-在线热备-控制文件

BooBooWei - 2019.12.01

上一节课学习了物理备份-在线热备-数据文件;
今天学习的是在数据库正常提供服务的情况线下,备份控制文件。

物理备份-在线热备-控制文件

核心命令SQL

  1. 在线热备控制文件

    alter database backup controlfile to '<backup control file path>';
  2. 切换日志

    alter system switch logfile;
  3. 恢复控制文件

    recover database using backup controlfile;
  4. 重置redolog

    alter database open resetlogs;

实践01-在线热备控制文件

控制文件备份的一般步骤

  1. 在线热备控制文件alter database backup controlfile to '<backup control file path>';
  2. 切换日志alter system switch logfile;
  3. 查看备份的控制文件

操作记录

SQL> alter database backup controlfile to '/home/oracle/hotbk/control01.ctl';

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> !ls -lh /home/oracle/hotbk/control01.ctl
-rw-r----- 1 oracle oinstall 9.4M 12月 1 19:42 /home/oracle/hotbk/control01.ctl

SQL> select count(*) from scott.t01;

COUNT(*)
----------
56

实践02-恢复数据库到控制文件时间点

控制文件恢复的一般步骤

  1. 查看控制文件参数获取控制文件路径show parameter control;
  2. 将备份的控制文件复制到控制文件路径!cp <backup control file path> <control file path>
  3. 打开数据库到mount模式alter database mount;
  4. 执行恢复控制文件的SQL命令recover database using backup controlfile;
  5. 计算还需应用的redolog
  6. 打开数据库到open模式alter database open resetlog

操作记录

模拟控制文件损坏
SQL> insert into scott.t01 select * from scott.t01;

56 rows created.

SQL> select count(*) from scott.t01;

COUNT(*)
----------
112

SQL> select * from v$controlfile;

STATUS
-------
NAME
--------------------------------------------------------------------------------
IS_ BLOCK_SIZE FILE_SIZE_BLKS
--- ---------- --------------

/alidata/oracle/oradata/testdb/control01.ctl
NO 16384 594


/alidata/oracle/flash_recovery_area/testdb/control02.ctl
NO 16384 594

STATUS
-------
NAME
--------------------------------------------------------------------------------
IS_ BLOCK_SIZE FILE_SIZE_BLKS
--- ---------- --------------


SQL> !rm -rf /alidata/oracle/oradata/testdb/control01.ctl

SQL> !rm -rf /alidata/oracle/flash_recovery_area/testdb/control02.ctl

SQL> startup force;
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size 2215064 bytes
Variable Size 1392509800 bytes
Database Buffers 738197504 bytes
Redo Buffers 4964352 bytes
ORA-00205: error in identifying control file, check alert log for more info
恢复控制文件
SQL> show parameter control_file;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /alidata/oracle/oradata/testdb
/control01.ctl, /alidata/oracl
e/flash_recovery_area/testdb/c
ontrol02.ctl
SQL> !cp -v /home/oracle/hotbk/control01.ctl /alidata/oracle/oradata/testdb/control01.ctl
"/home/oracle/hotbk/control01.ctl" -> "/alidata/oracle/oradata/testdb/control01.ctl"

SQL> !cp -v /home/oracle/hotbk/control01.ctl /alidata/oracle/flash_recovery_area/testdb/control02.ctl
"/home/oracle/hotbk/control01.ctl" -> "/alidata/oracle/flash_recovery_area/testdb/control02.ctl"

SQL> alter database mount;

Database altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> recover database using backup controlfile;
ORA-00279: change 3460311 generated at 12/01/2019 21:45:17 needed for thread 1
ORA-00289: suggestion : /home/oracle/orc_booboo_dest1/1_120_1019996090.dbf
ORA-00280: change 3460311 for thread 1 is in sequence #120


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 3460407 generated at 12/01/2019 21:48:24 needed for thread 1
ORA-00289: suggestion : /home/oracle/orc_booboo_dest1/1_121_1019996090.dbf
ORA-00280: change 3460407 for thread 1 is in sequence #121
ORA-00278: log file '/home/oracle/orc_booboo_dest1/1_120_1019996090.dbf' no
longer needed for this recovery


ORA-00279: change 3460410 generated at 12/01/2019 21:48:25 needed for thread 1
ORA-00289: suggestion : /home/oracle/orc_booboo_dest1/1_122_1019996090.dbf
ORA-00280: change 3460410 for thread 1 is in sequence #122
ORA-00278: log file '/home/oracle/orc_booboo_dest1/1_121_1019996090.dbf' no
longer needed for this recovery


ORA-00279: change 3460413 generated at 12/01/2019 21:48:27 needed for thread 1
ORA-00289: suggestion : /home/oracle/orc_booboo_dest1/1_123_1019996090.dbf
ORA-00280: change 3460413 for thread 1 is in sequence #123
ORA-00278: log file '/home/oracle/orc_booboo_dest1/1_122_1019996090.dbf' no
longer needed for this recovery


ORA-00279: change 3460416 generated at 12/01/2019 21:48:28 needed for thread 1
ORA-00289: suggestion : /home/oracle/orc_booboo_dest1/1_124_1019996090.dbf
ORA-00280: change 3460416 for thread 1 is in sequence #124
ORA-00278: log file '/home/oracle/orc_booboo_dest1/1_123_1019996090.dbf' no
longer needed for this recovery


ORA-00279: change 3460419 generated at 12/01/2019 21:48:30 needed for thread 1
ORA-00289: suggestion : /home/oracle/orc_booboo_dest1/1_125_1019996090.dbf
ORA-00280: change 3460419 for thread 1 is in sequence #125
ORA-00278: log file '/home/oracle/orc_booboo_dest1/1_124_1019996090.dbf' no
longer needed for this recovery

ORA-00308: cannot open archived log
'/home/oracle/orc_booboo_dest1/1_125_1019996090.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00308: cannot open archived log

最后一个归档日志为sequence #124;Oracle需要sequence #125如何获取呢?

思路:

通过select * from v$log;查看备份的那一时刻,redolog日志组的状态

  • 确定当前组为GROUP#3
  • SEQUENCE#120

125 - 120 = 5

5/3=1..2

说明:备份控制文件后,redolog日志组轮询情况如下:

GROUP SEQUENCE
GROUP#3 120
GROUP#1 121
GROUP#2 122
GROUP#3 123
GROUP#1 124
GROUP#2 125

由此推算出sequence #125就是日志组GROUP#2

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 118 52428800 512 1 YES INACTIVE 3460106 01-DEC-19 3460109 01-DEC-19
3 1 120 52428800 512 1 NO CURRENT 3460112 01-DEC-19 2.8147E+14
2 1 119 52428800 512 1 YES INACTIVE 3460109 01-DEC-19 3460112 01-DEC-19

SQL> column member format a50
SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
3 ONLINE /alidata/oracle/oradata/testdb/redo03.log NO
2 ONLINE /alidata/oracle/oradata/testdb/redo02.log NO
1 ONLINE /alidata/oracle/oradata/testdb/redo01.log NO

SQL> select file#,checkpoint_change# from v$datafile_header;

FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 3460419
2 3460419
3 3460419
4 3460419
5 3460419

SQL> select file#,checkpoint_change# from v$datafile;

FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 3460274
2 3460274
3 3460274
4 3460274
5 3460274
  • 数据文件显示的检查点为 3460419
  • 控制文件显示的检查点为 3460274
  • 可以看到 数据文件 > 控制文件

因为控制文件是历史某一时刻的,而数据文件是当前的,所以检查点不一致。

SQL> recover database  using backup controlfile;
ORA-00279: change 3460419 generated at 12/01/2019 21:48:30 needed for thread 1
ORA-00289: suggestion : /home/oracle/orc_booboo_dest1/1_125_1019996090.dbf
ORA-00280: change 3460419 for thread 1 is in sequence #125


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/alidata/oracle/oradata/testdb/redo02.log
Log applied.
Media recovery complete.

SQL> select file#,checkpoint_change# from v$datafile_header;

FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 3460455
2 3460455
3 3460455
4 3460455
5 3460455

SQL> select file#,checkpoint_change# from v$datafile;

FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 3460455
2 3460455
3 3460455
4 3460455
5 3460455
  • 数据文件显示的检查点为 3460455

  • 控制文件显示的检查点为 3460455

  • 可以看到 数据文件 = 控制文件

SQL> alter database open resetlogs;

Database altered.

SQL> select count(*) from scott.t01;

COUNT(*)
----------
56

数据恢复到了控制文件备份的时间点。

实践03-恢复数据库到数据文件时间点

恢复数据库到数据文件时间点的一般步骤

  1. 查看当前控制文件、数据文件、日志文件位置

    select name from v$controlfile;
    select name from v$datafile;
    select member from v$logfile;
  2. 停库,清除当前控制文件,启动到nomount

  3. 创建控制文件

    create controlfile reuse database BOOBOO resetlogs archivelog
    datafile
    '/u01/app/oracle/oradata/BOOBOO/system01.dbf',
    '/u01/app/oracle/oradata/BOOBOO/sysaux01.dbf',
    '/u01/app/oracle/oradata/BOOBOO/undotbs01.dbf',
    '/u01/app/oracle/oradata/BOOBOO/users01.dbf'
    logfile
    group 1 ('/u01/app/oracle/oradata/BOOBOO/redo01a.log','/u01/app/oracle/oradata/BOOBOO/redo01b.log') size 50m,
    group 2 ('/u01/app/oracle/oradata/BOOBOO/redo02a.log','/u01/app/oracle/oradata/BOOBOO/redo02b.log') size 50m,
    group 3 ('/u01/app/oracle/oradata/BOOBOO/redo03a.log','/u01/app/oracle/oradata/BOOBOO/redo03b.log') size 50m;

  4. 恢复数据库到灾难产生时间点recover database

  5. 使用resetlogs方式打开数据库alter database open resetlogs

操作记录

SQL> select count(*) from scott.emp;

COUNT(*)
----------
14
SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/product/11.2.0.4/dbs/cntrlBOOBOO.dbf

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/BOOBOO/system01.dbf
/u01/app/oracle/oradata/BOOBOO/sysaux01.dbf
/u01/app/oracle/oradata/BOOBOO/undotbs01.dbf
/u01/app/oracle/oradata/BOOBOO/users01.dbf

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/BOOBOO/redo01a.log
/u01/app/oracle/oradata/BOOBOO/redo01b.log
/u01/app/oracle/oradata/BOOBOO/redo02a.log
/u01/app/oracle/oradata/BOOBOO/redo02b.log
/u01/app/oracle/oradata/BOOBOO/redo03a.log
/u01/app/oracle/oradata/BOOBOO/redo03b.log

6 rows selected.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> !rm -rf /u01/app/oracle/product/11.2.0.4/dbs/cntrlBOOBOO.dbf

SQL> startup nomount
ORACLE instance started.

Total System Global Area 229683200 bytes
Fixed Size 2251936 bytes
Variable Size 171967328 bytes
Database Buffers 50331648 bytes
Redo Buffers 5132288 bytes

SQL> create controlfile reuse database BOOBOO resetlogs archivelog
datafile
'/u01/app/oracle/oradata/BOOBOO/system01.dbf',
'/u01/app/oracle/oradata/BOOBOO/sysaux01.dbf',
'/u01/app/oracle/oradata/BOOBOO/undotbs01.dbf',
'/u01/app/oracle/oradata/BOOBOO/users01.dbf'
logfile
group 1 ('/u01/app/oracle/oradata/BOOBOO/redo01a.log','/u01/app/oracle/oradata/BOOBOO/redo01b.log') size 50m,
group 2 ('/u01/app/oracle/oradata/BOOBOO/redo02a.log','/u01/app/oracle/oradata/BOOBOO/redo02b.log') size 50m,
group 3 ('/u01/app/oracle/oradata/BOOBOO/redo03a.log','/u01/app/oracle/oradata/BOOBOO/redo03b.log') size 50m;

Control file created.

SQL> recover database;

SQL> alter database open resetlogs;

Database altered.

SQL> select count(*) from scott.emp;

COUNT(*)
----------
14

拓展知识

Oracle数据字典之x$kcccp

x$kcccp顾名思义为kernel cache checkpoint progress.
X$KCCCP--[K]ernel [C]ache [C]ontrolfile management [c]heckpoint [p]rogress
metalink 22241.1详细记录了x$表名缩写含义。现在已被Oracle从metalink上撤销掉了。首先看一下x$kcccp的结构,注意其黑体部分。CPODR_SEQ表示当前redolog的sequence,CPODR_BNO表示当前redolog写至哪个块,CPHBT表示ckpt 的heart beat点。
SQL> desc x$kcccp;
Name Null? Type
----------------------------------------- -------- ----------------------------
ADDR RAW(8)
INDX NUMBER
INST_ID NUMBER
CPTNO NUMBER
CPSTA NUMBER
CPFLG NUMBER
CPDRT NUMBER
CPRDB NUMBER
CPLRBA_SEQ NUMBER
CPLRBA_BNO NUMBER
CPLRBA_BOF NUMBER
CPODR_SEQ NUMBER
CPODR_BNO NUMBER
CPODR_BOF NUMBER
CPODS VARCHAR2(16)
CPODT VARCHAR2(20)
CPODT_I NUMBER
CPHBT NUMBER
CPRLS VARCHAR2(16)
CPRLC NUMBER
CPMID NUMBER
CPSDR_SEQ NUMBER
CPSDR_BNO NUMBER
CPSDR_ADB NUMBER

通过以下SQL可以查询到生产库lgwr进程已经写至current redolog的哪一个块。

select le.leseq CURRENT_LOG_SEQUENCE#,cp.CPODR_BNO
from x$kcccp cp,x$kccle le
where LE.leseq=CP.cpodr_seq and le.INST_ID=1;

大宝笔记

本节课重点掌握:

  1. 在线热备控制文件的命令
  2. 恢复数据库到控制文件时间点recover database using backup controlfile;
  3. 恢复数据库到数据文件时间点recover database;