热备模式相关问题2.txt

　　--//上午测试热备模式相关问题,就是如果打开热备模式,如果中间的归档丢失,oracle在alter database end backup ;时并没有应用日志.

　　--//虽然热备份模式文件头scn被"冻结",一定在某个地方记录的检查点的scn,这样在执行alter database end backup ;时,写入新的scn

　　--//这样在恢复时才有可能跳过一些丢失的归档.

　　--//从某种意义讲,oracle这样设计有一定道理,假设某种情况打开热备模式,由于热备模式中断或者没有完成,忘记结束,如果在某次异常关闭时

　　--//需要恢复,并需要从"冻结"的scn号开始恢复.

　　--//测试看看这些相关信息保存在那里.

　　1.环境:

　　SCOTT@book> @ ver1

　　PORT_STRING VERSION BANNER

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

　　x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

　　2.测试1:

　　SYS@book> alter tablespace tea begin backup ;

　　Tablespace altered.

　　SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header where file# in (1,6);

　　FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME

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

　　1 13277609065 2018-04-13 09:38:45 7 925702 ONLINE 937 YES /mnt/ramdisk/book/system01.dbf SYSTEM

　　6 13277634716 2018-04-13 15:02:36 13276257767 925702 ONLINE 309 YES /mnt/ramdisk/book/tea01.dbf TEA

　　SYS@book> alter tablespace tea end backup ;

　　Tablespace altered.

　　SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header where file# in (1,6);

　　FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME

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

　　1 13277609065 2018-04-13 09:38:45 7 925702 ONLINE 937 YES /mnt/ramdisk/book/system01.dbf SYSTEM

　　6 13277634716 2018-04-13 15:02:36 13276257767 925702 ONLINE 310 YES /mnt/ramdisk/book/tea01.dbf TEA

　　--//数据文件6的scn=13277634716,没有变化,CHECKPOINT_COUNT增加.

　　3.测试2:

　　SYS@book> alter tablespace tea begin backup ;

　　Tablespace altered.

　　SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header where file# in (1,6);

　　FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME

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

　　1 13277609065 2018-04-13 09:38:45 7 925702 ONLINE 937 YES /mnt/ramdisk/book/system01.dbf SYSTEM

　　6 13277634864 2018-04-13 15:04:47 13276257767 925702 ONLINE 311 YES /mnt/ramdisk/book/tea01.dbf TEA

　　SYS@book> alter system checkpoint ;

　　System altered.

　　SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header where file# in (1,6);

　　FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME

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

　　1 13277634878 2018-04-13 15:04:56 7 925702 ONLINE 938 YES /mnt/ramdisk/book/system01.dbf SYSTEM

　　6 13277634864 2018-04-13 15:04:47 13276257767 925702 ONLINE 312 YES /mnt/ramdisk/book/tea01.dbf TEA

　　--//虽然数据文件6呃文件头scn被冻结13277634864,但是CHECKPOINT_COUNT依旧还是增加,也就是还是会改动文件头信息.

　　select 13277634878,trunc(13277634878/power(2,32)) scn_wrap,mod(13277634878,power(2,32)) scn_base from dual

　　13277634878 SCN_WRAP SCN_BASE SCN_WRAP16 SCN_BASE16

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

　　13277634878 3 392732990 3 1768a13e

　　SYS@book> @ &r/10to16 392732990

　　10 to 16 HEX REVERSE16

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

　　000000001768a13e 0x3ea16817-00000000

　　--//通过bbed观察,可以发出检查点信息是记录在文件头中的.

　　BBED> p dba 6,1 kcvfh.kcvfhbcp.kcvcpscn

　　struct kcvcpscn, 8 bytes @152

　　ub4 kscnbas @152 0x1768a13e

　　ub2 kscnwrp @156 0x0003

　　SYS@book> alter system checkpoint ;

　　System altered.

　　SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header where file# in (1,6);

　　FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME

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

　　1 13277635895 2018-04-13 15:13:16 7 925702 ONLINE 939 YES /mnt/ramdisk/book/system01.dbf SYSTEM

　　6 13277634864 2018-04-13 15:04:47 13276257767 925702 ONLINE 313 YES /mnt/ramdisk/book/tea01.dbf TEA

　　SYS@book> @ &r/10to16 13277635895

　　10 to 16 HEX REVERSE16

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

　　000000031768a537 0x37a56817-03000000

　　BBED> p dba 6,1 kcvfh.kcvfhbcp.kcvcpscn

　　struct kcvcpscn, 8 bytes @152

　　ub4 kscnbas @152 0x1768a537

　　ub2 kscnwrp @156 0x0003

　　--//可以发现数据文件6kcvfh.kcvfhbcp.kcvcpscn位置也会更新.这样在结束热备模式时,自动更新文件头.

　　SYS@book> alter tablespace tea end backup ;

　　Tablespace altered.

　　SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header where file# in (1,6);

　　FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME

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

　　1 13277635895 2018-04-13 15:13:16 7 925702 ONLINE 939 YES /mnt/ramdisk/book/system01.dbf SYSTEM

　　6 13277635895 2018-04-13 15:13:16 13276257767 925702 ONLINE 314 YES /mnt/ramdisk/book/tea01.dbf TEA

　　--//这也就很好解析为什么结束热备模式,从那里更新检查点.另外kcvfh.kcvfhbcp.kcvcpscn,里面的hb可以猜测表示hot backup的意思.

　　4.测试3:

　　--//做一个文件头转储看看:

　　SYS@book> alter tablespace tea begin backup ;

　　Tablespace altered.

　　SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header where file# in (1,6);

　　FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME

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

　　1 13277635895 2018-04-13 15:13:16 7 925702 ONLINE 939 YES /mnt/ramdisk/book/system01.dbf SYSTEM

　　6 13277637553 2018-04-13 15:39:15 13276257767 925702 ONLINE 317 YES /mnt/ramdisk/book/tea01.dbf TEA

　　SYS@book> alter system checkpoint ;

　　System altered.

　　SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header where file# in (1,6);

　　FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME

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

　　1 13277637599 2018-04-13 15:39:56 7 925702 ONLINE 940 YES /mnt/ramdisk/book/system01.dbf SYSTEM

　　6 13277637553 2018-04-13 15:39:15 13276257767 925702 ONLINE 318 YES /mnt/ramdisk/book/tea01.dbf TEA

　　select 13277637599,trunc(13277637599/power(2,32)) scn_wrap,mod(13277637599,power(2,32)) scn_base from dual

　　13277637599 SCN_WRAP SCN_BASE SCN_WRAP16 SCN_BASE16

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

　　13277637599 3 392735711 3 1768abdf

　　select 13277637553,trunc(13277637553/power(2,32)) scn_wrap,mod(13277637553,power(2,32)) scn_base from dual

　　13277637553 SCN_WRAP SCN_BASE SCN_WRAP16 SCN_BASE16

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

　　13277637553 3 392735665 3 1768abb1

　　SYS@book> alter session set events 'immediate trace name FILE_HDRS level 12';

　　Session altered.

　　--//检查转储:

　　DATA FILE #6:

　　name #10: /mnt/ramdisk/book/tea01.dbf

　　creation size=5120 block size=8192 status=0xe head=10 tail=10 dup=1

　　tablespace 7, index=7 krfil=6 prev_file=0

　　unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00

　　Checkpoint cnt:318 scn: 0x0003.1768abb1 04/13/2018 15:39:15

　　~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

　　// --> 注这里的信息来之控制文件:http://blog.itpub.net/267265/viewspace-2136766/

　　Stop scn: 0xffff.ffffffff 04/13/2018 09:38:22

　　Creation Checkpointed at scn: 0x0003.17539de7 02/13/2017 15:09:58

　　thread:1 rba:(0x1d6.48.10)

　　enabled threads: 01000000 00000000 00000000 00000000 00000000 00000000

　　00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

　　00000000 00000000 00000000 00000000 00000000 00000000

　　Offline scn: 0x0000.00000000 prev_range: 0

　　Online Checkpointed at scn: 0x0000.00000000

　　thread:0 rba:(0x0.0.0)

　　enabled threads: 00000000 00000000 00000000 00000000 00000000 00000000

　　00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

　　00000000 00000000 00000000 00000000 00000000 00000000

　　Hot Backup end marker scn: 0x0000.00000000

　　aux_file is NOT DEFINED

　　Plugged readony: NO

　　Plugin scnscn: 0x0000.00000000

　　Plugin resetlogs scn/timescn: 0x0000.00000000 01/01/1988 00:00:00

　　Foreign creation scn/timescn: 0x0000.00000000 01/01/1988 00:00:00

　　Foreign checkpoint scn/timescn: 0x0000.00000000 01/01/1988 00:00:00

　　Online move state: 0

　　V10 STYLE FILE HEADER:

　　Compatibility Vsn = 186647552=0xb200400

　　Db ID=1337401710=0x4fb7216e, Db Name='BOOK'

　　Activation ID=0=0x0

　　Control Seq=39840=0x9ba0, File size=5120=0x1400

　　File Number=6, Blksiz=8192, File Type=3 DATA

　　Tablespace #7 - TEA rel_fn:6

　　Creation at scn: 0x0003.17539de7 02/13/2017 15:09:58

　　Backup taken at scn: 0x0003.1768abb1 04/13/2018 15:39:15 thread:1

　　~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

　　--//发出热备份模式的scn信息.

　　reset logs count:0x35711eb0 scn: 0x0000.000e2006

　　prev reset logs count:0x3121c97a scn: 0x0000.00000001

　　recovered at 04/13/2018 09:38:35

　　status:0x1 root dba:0x00000000 chkpt cnt: 318 ctl cnt:317

　　begin-hot-backup file size: 5120

　　Checkpointed at scn: 0x0003.1768abb1 04/13/2018 15:39:15

　　thread:1 rba:(0x2f7.a85b.10)

　　enabled threads: 01000000 00000000 00000000 00000000 00000000 00000000

　　00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

　　00000000 00000000 00000000 00000000 00000000 00000000

　　Backup Checkpointed at scn: 0x0003.1768abdf 04/13/2018 15:39:56

　　~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

　　--//备份过程中发出的检查点信息.

　　thread:1 rba:(0x2f7.a886.10)

　　enabled threads: 01000000 00000000 00000000 00000000 00000000 00000000

　　00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

　　00000000 00000000 00000000 00000000 00000000 00000000

　　External cache id: 0x0 0x0 0x0 0x0

　　Absolute fuzzy scn: 0x0000.00000000

　　Recovery fuzzy scn: 0x0000.00000000 01/01/1988 00:00:00

　　Terminal Recovery Stamp 01/01/1988 00:00:00

　　Platform Information: Creation Platform ID: 13

　　Current Platform ID: 13 Last Platform ID: 13

　　DUMP OF TEMP FILES: 1 files in database

　　5.继续测试,使用bbed修改看看:

　　--//使用bbed修改看看,减少kcvfh.kcvfhbcp.kcvcpscn-11看看.

　　SYS@book> shutdown abort ;

　　ORACLE instance shut down.

　　--//使用bbed修改:

　　BBED> p /d dba 6,1 kcvfh.kcvfhbcp.kcvcpscn

　　struct kcvcpscn, 8 bytes @152

　　ub4 kscnbas @152 392735711

　　ub2 kscnwrp @156 3

　　BBED> assign dba 6,1 kcvfh.kcvfhbcp.kcvcpscn.kscnbas=392735700

　　Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y

　　ub4 kscnbas @152 0x1768abd4

　　BBED> sum apply dba 6,1

　　Check value for File 6, Block 1:

　　current = 0xcf4c, required = 0xcf4c

　　BBED> p dba 6,1 kcvfh.kcvfhbcp.kcvcpscn

　　struct kcvcpscn, 8 bytes @152

　　ub4 kscnbas @152 0x1768abd4

　　ub2 kscnwrp @156 0x0003

　　SYS@book> @ &r/16to10 31768abd4

　　16 to 10 DEC

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

　　13277637588

　　--//减少到13277637588.

　　SYS@book> startup

　　ORACLE instance started.

　　Total System Global Area 634732544 bytes

　　Fixed Size 2255792 bytes

　　Variable Size 197133392 bytes

　　Database Buffers 427819008 bytes

　　Redo Buffers 7524352 bytes

　　Database mounted.

　　ORA-10873: file 6 needs to be either taken out of backup mode or media recovered

　　ORA-01110: data file 6: '/mnt/ramdisk/book/tea01.dbf'

　　SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header where file# in (1,6);

　　FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME

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

　　1 13277637599 2018-04-13 15:39:56 7 925702 ONLINE 941 YES /mnt/ramdisk/book/system01.dbf SYSTEM

　　6 13277637553 2018-04-13 15:39:15 13276257767 925702 ONLINE 318 YES /mnt/ramdisk/book/tea01.dbf TEA

　　SYS@book> alter tablespace tea end backup ;

　　Tablespace altered.

　　SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header where file# in (1,6);

　　FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME

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

　　1 13277637599 2018-04-13 15:39:56 7 925702 ONLINE 941 YES /mnt/ramdisk/book/system01.dbf SYSTEM

　　6 13277637588 2018-04-13 15:39:56 13276257767 925702 ONLINE 319 YES /mnt/ramdisk/book/tea01.dbf TEA

　　--//与前面修改一致,也验证了自己的判断.

　　6.继续测试,是否在热备份模式可以offline表空间:

　　SYS@book> alter database open ;

　　Database altered.

　　SYS@book> alter tablespace tea begin backup ;

　　Tablespace altered.

　　SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header where file# in (1,6);

　　FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME

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

　　1 13277658196 2018-04-13 15:56:09 7 925702 ONLINE 944 YES /mnt/ramdisk/book/system01.dbf SYSTEM

　　6 13277658833 2018-04-13 15:59:49 13276257767 925702 ONLINE 329 YES /mnt/ramdisk/book/tea01.dbf TEA

　　SYS@book> alter tablespace tea offline;

　　alter tablespace tea offline

　　ERROR at line 1:

　　ORA-01150: cannot prevent writes - file 6 has online backup set

　　ORA-01110: data file 6: '/mnt/ramdisk/book/tea01.dbf'

　　$ oerr ora 01150

　　01150, 00000, "cannot prevent writes - file %s has online backup set"

　　// *Cause: An attempt to make a tablespace read only or offline normal found

　　// that an online backup is still in progress. It will be necessary

　　// to write the file header to end the backup, but that would not

　　// be allowed if this command succeeded.

　　// *Action: End the backup of the offending tablespace and retry this command.

　　SYS@book> alter tablespace tea offline immediate ;

　　Tablespace altered.

　　--//强制ok.

　　SYS@book> alter tablespace tea online;

　　alter tablespace tea online

　　ERROR at line 1:

　　ORA-01113: file 6 needs media recovery

　　ORA-01110: data file 6: '/mnt/ramdisk/book/tea01.dbf'

　　SYS@book> select * from v$backup where file# in (1,6);

　　FILE# STATUS CHANGE# TIME

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

　　1 NOT ACTIVE 13277525910 2018-04-13 09:03:44

　　6 NOT ACTIVE 13277658833 2018-04-13 15:59:49

　　--//热备模式已经关闭.

　　--//总结:

　　--//虽然热备份已经不常用,也不推荐使用.还是佩服oracle设计时的考虑周全,在发出检查点时记录最新的scn号数据文件中即使在热备份模式下.

　　--//这样在恢复时减少使用归档的数量.

　　(编辑：雷林鹏来源：网络)

posted @ 2018-08-17 09:37 雷林鹏阅读(200) 评论(0) 编辑收藏举报

刷新页面返回顶部

雷林鹏

热备模式相关问题2.txt

公告