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

BooBooWei - 2019.12.01

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

• 冷备-物理备份-在线热备-控制文件
  • 物理备份-在线热备-控制文件
    • 核心命令SQL
    • 实践01-在线热备控制文件
      • 控制文件备份的一般步骤
      • 操作记录
    • 实践02-恢复数据库到控制文件时间点
      • 控制文件恢复的一般步骤
      • 操作记录
        • 模拟控制文件损坏
        • 恢复控制文件
        • ORA-00308: cannot open archived log
    • 实践03-恢复数据库到数据文件时间点
      • 恢复数据库到数据文件时间点的一般步骤
      • 操作记录
  • 拓展知识
    • Oracle数据字典之x$kcccp
  • 大宝笔记

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

核心命令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;