dbra.log oracle,Oracle begin backup研究

一念前尘

于 2021-04-09 10:23:41 发布

阅读量117

点赞数

文章标签： dbra.log oracle

由于昨天客户数据库数据文件头scn远远低于当前scn，我猜测是表空间做了begin backup。为了验证这一想法，特做了如下实验：

数据库版本为：

引用

SQL> select * from v$version;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi

PL/SQL Release 10.2.0.1.0 - Production

CORE 10.2.0.1.0 Production

TNS for Linux: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

将数据库置于begin backup模式

引用

SQL> alter database begin backup;

Database altered.

SQL> select * from v$backup;

FILE# STATUS CHANGE# TIME

---------- ------------------ ---------- ---------

1 ACTIVE 9.7448E+12 20-MAR-10

2 ACTIVE 9.7448E+12 20-MAR-10

3 ACTIVE 9.7448E+12 20-MAR-10

4 ACTIVE 9.7448E+12 20-MAR-10

5 ACTIVE 9.7448E+12 20-MAR-10

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /opt/ora10g/archlog

Oldest online log sequence 1071

Next log sequence to archive 1073

Current log sequence 1073

切换几个归档，模拟业务正在进行

引用

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

将数据库shutdown abort

引用

SQL> shutdown abort

ORACLE instance shut down.

恢复之前数据文件头处在冻结状态

引用

SQL> select * from v$backup;

FILE# STATUS CHANGE# TIME

---------- ------------------ ---------- ---------

1 ACTIVE 9.7448E+12 20-MAR-10

2 ACTIVE 9.7448E+12 20-MAR-10

3 ACTIVE 9.7448E+12 20-MAR-10

4 ACTIVE 9.7448E+12 20-MAR-10

5 ACTIVE 9.7448E+12 20-MAR-10

根据客户现场用"recover database using backup controlfile;"将数据库进行恢复

引用

SQL>

recover database using backup controlfile;

ORA-00279: change 9744845765380 generated at 03/20/2010 14:42:22 needed for

thread 1

ORA-00289: suggestion : /opt/ora10g/archlog/1_1073_665850840.dbf

ORA-00280: change 9744845765380 for thread 1 is in sequence #1073

Specify log: {=suggested | filename | AUTO | CANCEL}

auto

。。。

SQL> recover database using backup controlfile;

ORA-00279: change 9744845765485 generated at 03/20/2010 14:44:08 needed for

thread 1

ORA-00289: suggestion : /opt/ora10g/archlog/1_1077_665850840.dbf

ORA-00280: change 9744845765485 for thread 1 is in sequence #1077

Specify log: {=suggested | filename | AUTO | CANCEL}

/opt/ora10g/oradata/dbra10g/redo02.log

Log applied.

Media recovery complete.

可以看到v$backup已经置为 inactive

引用

SQL> select * from v$backup;

FILE# STATUS CHANGE# TIME

---------- ------------------ ---------- ---------

1 NOT ACTIVE 9.7448E+12 20-MAR-10

2 NOT ACTIVE 9.7448E+12 20-MAR-10

3 NOT ACTIVE 9.7448E+12 20-MAR-10

4 NOT ACTIVE 9.7448E+12 20-MAR-10

5 NOT ACTIVE 9.7448E+12 20-MAR-10

最后将数据打开

引用

SQL> alter database open RESETLOGS;

Database altered.

从以上实验可以看出，数据文件头scn远小于当前scn很大可能是ca软件将表空间begin backup所致。

+++++++++++++++++++++++++华丽的分割线+++++++++++++++++++++++++++++

其实恢复远比以上过程简单:

当数据处在mount状态时，查看数据文件头scn

引用

SQL> select * from v$backup;

FILE# STATUS CHANGE# TIME

---------- ------------------ --------------------- ---------

1 ACTIVE 9744845806881 20-MAR-10

2 ACTIVE 9744845806881 20-MAR-10

3 ACTIVE 9744845806881 20-MAR-10

4 ACTIVE 9744845806881 20-MAR-10

5 ACTIVE 9744845806881 20-MAR-10

SQL> col checkpoint_change# for 9999999999999999999

SQL> select checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#

--------------------

9744845806881

SQL> select checkpoint_change# from v$datafile;

CHECKPOINT_CHANGE#

--------------------

9744845806881

查看数据库scn

引用

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#

--------------------

9744845806954

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

在mount状态进行end backup，可以看到数据文件头进行了更新同步

引用

SQL> alter database end backup;

Database altered.

SQL> select checkpoint_change# from v$datafile;

CHECKPOINT_CHANGE#

--------------------

9744845806954

SQL> select checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#

--------------------

9744845806954

随后只要将数据库打开即可

引用

SQL> alter database open;

Database altered.

其中alert日志显示

引用

Sat Mar 20 15:03:28 2010

alter database end backup

Sat Mar 20 15:03:28 2010

Completed: alter database end backup

Sat Mar 20 15:03:57 2010

alter database open

Sat Mar 20 15:03:57 2010

Beginning crash recovery of 1 threads

parallel recovery started with 3 processes

Sat Mar 20 15:03:57 2010

Started redo scan

Sat Mar 20 15:03:57 2010

Completed redo scan

245 redo blocks read, 41 data blocks need recovery

Sat Mar 20 15:03:57 2010

Started redo application at

Thread 1: logseq 15, block 977

Sat Mar 20 15:03:57 2010

Recovery of Online Redo Log: Thread 1 Group 3 Seq 15 Reading mem 0

Mem# 0 errs 0: /opt/ora10g/oradata/dbra10g/redo03.log

Sat Mar 20 15:03:57 2010

Completed redo application

Sat Mar 20 15:03:57 2010

Completed crash recovery at

Thread 1: logseq 15, block 1222, scn 9744845826986

41 data blocks read, 41 data blocks written, 245 redo blocks read

Sat Mar 20 15:03:57 2010

LGWR: STARTING ARCH PROCESSES

ARC0 started with pid=19, OS id=8939

Sat Mar 20 15:03:57 2010

ARC0: Archival started

ARC1: Archival started

LGWR: STARTING ARCH PROCESSES COMPLETE

ARC1 started with pid=20, OS id=8941

Sat Mar 20 15:03:57 2010

Thread 1 advanced to log sequence 16

Thread 1 opened at log sequence 16

Current log# 2 seq# 16 mem# 0: /opt/ora10g/oradata/dbra10g/redo02.log

Successful open of redo thread 1

Sat Mar 20 15:03:57 2010

MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set

Sat Mar 20 15:03:57 2010

ARC1: STARTING ARCH PROCESSES

Sat Mar 20 15:03:57 2010

SMON: enabling cache recovery Sat Mar 20 15:03:57 2010 ARC0: Becoming the 'no FAL' ARCH ARC0: Becoming the 'no SRL' ARCH Sat Mar 20 15:03:57 2010 ARC2: Archival started ARC1: STARTING ARCH PROCESSES COMPLETE ARC1: Becoming the heartbeat ARCH ARC2 started with pid=21, OS id=8951 Sat Mar 20 15:03:57 2010 Successfully onlined Undo Tablespace 1. Sat Mar 20 15:03:57 2010 SMON: enabling tx recovery Sat Mar 20 15:03:57 2010 Database Characterset is ZHS16GBK replication_dependency_tracking turned off (no async multimaster replication found) Starting background process QMNC QMNC started with pid=22, OS id=8960 Sat Mar 20 15:03:58 2010 Completed: alter database open