ITPub博客

首页 > 数据库 > Oracle > ​[20200809]12c热备份模式.txt

​[20200809]12c热备份模式.txt

原创 Oracle 作者:lfree 时间:2020-08-09 22:11:12 0 删除 编辑

[20200809]12c热备份模式.txt

--//昨天看链接:https://www.anbob.com/archives/5982.html,很奇怪作者为什么要bbed修改文件头,
--//除非恢复那段日志损坏或者相应归档丢失.
--//我记忆里以前做过类似测试,实际上恢复从检查点开始恢复.

--//实际上打开热备份并不是"冻结"文件头,仅仅冻结scn.实际上你如果发alter system checkpoint;还是会更新文件头的.
--//并且oracle实际上恢复从这个scn(alter system checkpoint命令的)开始恢复.
--//可以参考链接:http://blog.itpub.net/267265/viewspace-2152909/=>[20180413]热备模式相关问题2.txt
--//http://blog.itpub.net/267265/viewspace-2152883/ => [20180413]热备模式相关问题.txt
--//这样异常关闭仅仅需要从最后发出alter system checkpoint后的scn开始恢复.

--//12c呢? 也许增加PDB模式情况变得不同,测试看看.

1.环境:
SYS@test> @ ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0

SYS@test> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 TEST01P                        READ WRITE NO

2.测试:
SYS@test> alter database begin backup ;
Database altered.

--//测试在热备份模式下,可以正常关闭PDB库吗?
SYS@test> # alter pluggable database test01p close ;
 alter pluggable database test01p close
*
ERROR at line 1:
ORA-01149: cannot shutdown - file 36 has online backup set
ORA-01110: data file 36: 'D:\APP\ORACLE\ORADATA\TEST\TEST01P\LFREE05X.DBF'
--//不行.注意看提示ORA-01149: cannot shutdown - file 36 has online backup set.

SYS@test> show pdbs
    CON_ID CON_NAME  OPEN MODE  RESTRICTED
---------- --------- ---------- ----------
         2 PDB$SEED  READ ONLY  NO
         3 TEST01P   READ WRITE NO

SYS@test> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE#  , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name  FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME     CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME                                               TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------ ---------------- --- -------------------------------------------------- --------------------
    1           12000787 2020-08-09 17:04:30                9                 1 ONLINE             3131 YES D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF            SYSTEM
    2            1102125 2018-10-06 23:24:44              227                 1 ONLINE               37 NO  D:\APP\ORACLE\ORADATA\TEST\PDBSEED\SYSTEM01.DBF    SYSTEM
    3           12000787 2020-08-09 17:04:30             4748                 1 ONLINE             3128 YES D:\APP\ORACLE\ORADATA\TEST\SYSAUX01.DBF            SYSAUX
    4            1102125 2018-10-06 23:24:44             4762                 1 ONLINE               37 NO  D:\APP\ORACLE\ORADATA\TEST\PDBSEED\SYSAUX01.DBF    SYSAUX
    5           12000787 2020-08-09 17:04:30             6090                 1 ONLINE             3128 YES D:\APP\ORACLE\ORADATA\TEST\UNDOTBS01.DBF           UNDOTBS1
    6            1102125 2018-10-06 23:24:44             6212                 1 ONLINE               37 NO  D:\APP\ORACLE\ORADATA\TEST\PDBSEED\UNDOTBS01.DBF   UNDOTBS1
    7           12000787 2020-08-09 17:04:30            28597                 1 ONLINE             3169 YES D:\APP\ORACLE\ORADATA\TEST\USERS01.DBF             USERS
    8           12000787 2020-08-09 17:04:30          1103550                 1 ONLINE             3207 YES D:\APP\ORACLE\ORADATA\TEST\TEST01P\SYSTEM01.DBF    SYSTEM
    9           12000787 2020-08-09 17:04:30          1103553                 1 ONLINE             3205 YES D:\APP\ORACLE\ORADATA\TEST\TEST01P\SYSAUX01.DBF    SYSAUX
   10           12000787 2020-08-09 17:04:30          1103555                 1 ONLINE             3205 YES D:\APP\ORACLE\ORADATA\TEST\TEST01P\UNDOTBS01.DBF   UNDOTBS1
   11           12000787 2020-08-09 17:04:30          1105613                 1 ONLINE             3208 YES D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF     USERS
   36           12000787 2020-08-09 17:04:30          3718607                 1 ONLINE             3066 YES D:\APP\ORACLE\ORADATA\TEST\TEST01P\LFREE05X.DBF    LFREE
12 rows selected.
--//CHECKPOINT_CHANGE#=12000787

SYS@test> @ logfile
GROUP# STATUS     TYPE       MEMBER                                IS_ GROUP# THREAD# SEQUENCE#       BYTES BLOCKSIZE MEMBERS ARC STATUS     FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME
------ ---------- ---------- ------------------------------------- --- ------ ------- --------- ----------- --------- ------- --- ---------- ------------- ------------------- ------------ --------------------
     1            ONLINE     D:\APP\ORACLE\ORADATA\TEST\REDO01.LOG NO       1       1       163   209715200       512       1 YES INACTIVE        11838255 2020-07-19 10:32:27     11973375 2020-08-07 21:43:45
     2            ONLINE     D:\APP\ORACLE\ORADATA\TEST\REDO02.LOG NO       2       1       164   209715200       512       1 NO  CURRENT         11973375 2020-08-07 21:43:45 1.844674E+19
     3            ONLINE     D:\APP\ORACLE\ORADATA\TEST\REDO03.LOG NO       3       1       162   209715200       512       1 YES INACTIVE        11736278 2020-06-20 09:09:34     11838255 2020-07-19 10:32:27

3.继续:
SYS@test> alter system switch logfile;
System altered.

SYS@test> alter system switch logfile;
System altered.

SYS@test> alter system switch logfile;
System altered.

SYS@test> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE#  , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name  FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME     CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME                                               TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------ ---------------- --- -------------------------------------------------- ------------------
    1           12000787 2020-08-09 17:04:30                9                 1 ONLINE             3133 YES D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF            SYSTEM
    2            1102125 2018-10-06 23:24:44              227                 1 ONLINE               37 NO  D:\APP\ORACLE\ORADATA\TEST\PDBSEED\SYSTEM01.DBF    SYSTEM
    3           12000787 2020-08-09 17:04:30             4748                 1 ONLINE             3130 YES D:\APP\ORACLE\ORADATA\TEST\SYSAUX01.DBF            SYSAUX
    4            1102125 2018-10-06 23:24:44             4762                 1 ONLINE               37 NO  D:\APP\ORACLE\ORADATA\TEST\PDBSEED\SYSAUX01.DBF    SYSAUX
    5           12000787 2020-08-09 17:04:30             6090                 1 ONLINE             3130 YES D:\APP\ORACLE\ORADATA\TEST\UNDOTBS01.DBF           UNDOTBS1
    6            1102125 2018-10-06 23:24:44             6212                 1 ONLINE               37 NO  D:\APP\ORACLE\ORADATA\TEST\PDBSEED\UNDOTBS01.DBF   UNDOTBS1
    7           12000787 2020-08-09 17:04:30            28597                 1 ONLINE             3171 YES D:\APP\ORACLE\ORADATA\TEST\USERS01.DBF             USERS
    8           12000787 2020-08-09 17:04:30          1103550                 1 ONLINE             3209 YES D:\APP\ORACLE\ORADATA\TEST\TEST01P\SYSTEM01.DBF    SYSTEM
    9           12000787 2020-08-09 17:04:30          1103553                 1 ONLINE             3207 YES D:\APP\ORACLE\ORADATA\TEST\TEST01P\SYSAUX01.DBF    SYSAUX
   10           12000787 2020-08-09 17:04:30          1103555                 1 ONLINE             3207 YES D:\APP\ORACLE\ORADATA\TEST\TEST01P\UNDOTBS01.DBF   UNDOTBS1
   11           12000787 2020-08-09 17:04:30          1105613                 1 ONLINE             3210 YES D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF     USERS
   36           12000787 2020-08-09 17:04:30          3718607                 1 ONLINE             3068 YES D:\APP\ORACLE\ORADATA\TEST\TEST01P\LFREE05X.DBF    LFREE
12 rows selected.
--//CHECKPOINT_CHANGE#=12000787,对比前面没有变化

SYS@test> alter system checkpoint ;
System altered.

SYS@test> alter system checkpoint ;
System altered.

SYS@test> alter system checkpoint ;
System altered.
--//做一个bbed观察,注意bbed for windows版本看文件头结构存在问题,不过以前测试知道偏移在152处.

BBED> dump /v dba 1,2  offset 152 count 16
 File: D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF (1)
 Block: 2                                 Offsets:  152 to  167                            Dba:0x00400002
-----------------------------------------------------------------------------------------------------------
 0128b700 00800000 4f56773e 01000000                                     l .(?....OVw>....

 <32 bytes per line>

BBED> dump /v dba 11,2  offset 152 count 16
 File: D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF (11)
 Block: 2                                 Offsets:  152 to  167                            Dba:0x02c00002
-----------------------------------------------------------------------------------------------------------
 0128b700 00800000 4f56773e 01000000                                     l .(?....OVw>....
<32 bytes per line>

--//翻转就是00b72801 = 12003329.

4.做一个转储看看.

SYS@test> alter session set events 'immediate trace name FILE_HDRS level 12';
Session altered.

DATA FILE #1:
  name #4: D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF
creation size=89600 block size=8192 status=0xe flg=0x1 head=4 tail=4 dup=1
 pdb_id 1, tablespace 0, index=1 krfil=1 prev_file_in_ts=0 prev_file_in_pdb=0
 unrecoverable scn: 0x0000000000000000 01/01/1988 00:00:00
 Checkpoint cnt:3136 scn: 0x0000000000b71e13 08/09/2020 17:04:30
--//b71e13 = 12000787 , 检查点scn=12000787.
 Stop scn: 0xffffffffffffffff 08/07/2020 23:28:58
 Creation Checkpointed at scn:  0x0000000000000009 10/06/2018 21:16:52
 thread:1 rba:(0x1.3.10)
 enabled  threads:  01000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  0000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000
 Offline scn: 0x0000000000000000 prev_range: 0
 Online Checkpointed at scn:  0x0000000000000000
 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 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 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: 0x0000000000000000
 aux_file is NOT DEFINED
 Plugged readony: NO
 Plugin scnscn: 0x0000000000000000
 Plugin resetlogs scn/timescn: 0x0000000000000000 01/01/1988 00:00:00
 Foreign creation scn/timescn: 0x0000000000000000 01/01/1988 00:00:00
 Foreign checkpoint scn/timescn: 0x0000000000000000 01/01/1988 00:00:00
 Online move state: 0
 V10 STYLE FILE HEADER:
    Compatibility Vsn = 203423744=0xc200000
    Db ID=2286984624=0x88509db0, Db Name='TEST'
    Activation ID=0=0x0
    Control Seq=43371=0xa96b, File size=115200=0x1c200
    File Number=1, Blksiz=8192, File Type=3 DATA
Tablespace #0 - SYSTEM  rel_fn:1
Creation   at   scn: 0x0000000000000009 10/06/2018 21:16:52
Backup taken at scn: 0x0000000000b71e13 08/09/2020 17:04:30 thread:1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--//b71e13 = 12000787
 reset logs count:0x3af07930 scn: 0x0000000000000001
 prev reset logs count:0x0 scn: 0x0000000000000000
 recovered at 08/07/2020 21:43:41
 status:0x2001 root dba:0x00400208 chkpt cnt: 3136 ctl cnt:3135
begin-hot-backup file size: 115200
Checkpointed at scn:  0x0000000000b71e13 08/09/2020 17:04:30
 thread:1 rba:(0xa4.240f9.10)
 enabled  threads:  01000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000
Backup Checkpointed at scn:  0x0000000000b72801 08/09/2020 17:24:31
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
--//备份过程中发出的检查点信息,与文件头看到的offset 152信息一致.
--//b72801 = 12003329,与前面bbed观察的能够对上.
 thread:1 rba:(0xa7.fb6.10)
 enabled  threads:  01000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 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: 0x0000000000000000
Recovery fuzzy scn: 0x0000000000000000 08/24/2019 22:53:13
Terminal Recovery Stamp  01/01/1988 00:00:00
    PDB ID=1, PDB Db ID=0=0x0, PDB UID=1=0x1
Previous recovery fuzzy scn: 0x0000000000000000
Last deallocation scn: 0x0000000000000000
Plugged-in scn: 0x0000000000000000
Plugin resetlogs scn: 0x0000000000000000
Foreign creation scn: 0x0000000000000000
Foreign checkpoint scn: 0x0000000000000000
EOF section checkpoint scn: 0x0000000000000000
Undo optimization current scn: 0x0000000000000000
File key structure: ena 2 flg 0x6 mkloc 0
   key: 693d4cdb4c674ebde21c65bfcc9e210000000000000000000000000000000000
   mkeyid: 59930166df4b4fcdbfb43c446d6f8721
Last read CF transaction OCX clock 0
Platform Information:     Creation Platform ID: 12
Current Platform ID: 12    Last Platform ID: 12
PDB incarnation 0: inc_scn 0x0000000000000000 inc_time 0, br_scn 0x0000000000000000 br_time 0, er_scn 0x0000000000000000 er_time 0

5.关闭数据库看看:

SYS@test> shotdown immediate ;
SP2-0734: unknown command beginning "shotdown i..." - rest of line ignored.
SYS@test> shutdown immediate ;
ORA-01149: cannot shutdown - file 1 has online backup set
ORA-01110: data file 1: 'D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF'
--//提示也很清晰.ORA-01149: cannot shutdown - file 1 has online backup set

SYS@test> show pdbs
      CON_ID CON_NAME                       OPEN MODE  RESTRICTED
------------ ------------------------------ ---------- ----------
           2 PDB$SEED                       READ ONLY  NO
           3 TEST01P                        READ WRITE NO

SYS@test> shutdown abort;
ORACLE instance shut down.

--//改名归档目录名.
D:\app\oracle\fast_recovery_area\test\TEST\ARCHIVELOG> ls -l
total 0
drwxrwxrwx   1 user     group           0 Aug  7 21:43 2020_08_07
drwxrwxrwx   1 user     group           0 Aug  9 17:13 2020_08_09x

SYS@test> startup
ORACLE instance started.
Total System Global Area    805306368 bytes
Fixed Size                    8924064 bytes
Variable Size               297796704 bytes
Database Buffers            490733568 bytes
Redo Buffers                  7852032 bytes
Database mounted.
ORA-10873: file 1 needs to be either taken out of backup mode or media recovered
ORA-01110: data file 1: 'D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF'
--//实际上提示很清楚,需要file 1 needs to be either taken out of backup mode or media recovered.

SYS@test> alter database end backup ;
Database altered.
--//或者recover database也是ok的,测试略.

SYS@test> select * from v$backup ;
FILE# STATUS          CHANGE# TIME
----- ---------- ------------ -------------------
    1 NOT ACTIVE     12000787 2020-08-09 17:04:30
    2 NOT ACTIVE            0
    3 NOT ACTIVE     12000787 2020-08-09 17:04:30
    4 NOT ACTIVE            0
    5 NOT ACTIVE     12000787 2020-08-09 17:04:30
    6 NOT ACTIVE            0
    7 NOT ACTIVE     12000787 2020-08-09 17:04:30
    8 NOT ACTIVE     12000787 2020-08-09 17:04:30
    9 NOT ACTIVE     12000787 2020-08-09 17:04:30
   10 NOT ACTIVE     12000787 2020-08-09 17:04:30
   11 NOT ACTIVE     12000787 2020-08-09 17:04:30
   36 NOT ACTIVE     12000787 2020-08-09 17:04:30
12 rows selected.

SYS@test> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE#  , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name  FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME     CREATION_CHANGE# RESETLOGS_CHANGE# STATUS     CHECKPOINT_COUNT FUZ NAME                                               TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ---------- ---------------- --- -------------------------------------------------- ---------------
    1           12003329 2020-08-09 17:24:31                9                 1 ONLINE                 3137 YES D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF            SYSTEM
    2            1102125 2018-10-06 23:24:44              227                 1 ONLINE                   37 NO  D:\APP\ORACLE\ORADATA\TEST\PDBSEED\SYSTEM01.DBF    SYSTEM
    3           12003329 2020-08-09 17:24:31             4748                 1 ONLINE                 3134 YES D:\APP\ORACLE\ORADATA\TEST\SYSAUX01.DBF            SYSAUX
    4            1102125 2018-10-06 23:24:44             4762                 1 ONLINE                   37 NO  D:\APP\ORACLE\ORADATA\TEST\PDBSEED\SYSAUX01.DBF    SYSAUX
    5           12003329 2020-08-09 17:24:31             6090                 1 ONLINE                 3134 YES D:\APP\ORACLE\ORADATA\TEST\UNDOTBS01.DBF           UNDOTBS1
    6            1102125 2018-10-06 23:24:44             6212                 1 ONLINE                   37 NO  D:\APP\ORACLE\ORADATA\TEST\PDBSEED\UNDOTBS01.DBF   UNDOTBS1
    7           12003329 2020-08-09 17:24:31            28597                 1 ONLINE                 3175 YES D:\APP\ORACLE\ORADATA\TEST\USERS01.DBF             USERS
    8           12003329 2020-08-09 17:24:31          1103550                 1 ONLINE                 3213 YES D:\APP\ORACLE\ORADATA\TEST\TEST01P\SYSTEM01.DBF    SYSTEM
    9           12003329 2020-08-09 17:24:31          1103553                 1 ONLINE                 3211 YES D:\APP\ORACLE\ORADATA\TEST\TEST01P\SYSAUX01.DBF    SYSAUX
   10           12003329 2020-08-09 17:24:31          1103555                 1 ONLINE                 3211 YES D:\APP\ORACLE\ORADATA\TEST\TEST01P\UNDOTBS01.DBF   UNDOTBS1
   11           12003329 2020-08-09 17:24:31          1105613                 1 ONLINE                 3214 YES D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF     USERS
   36           12003329 2020-08-09 17:24:31          3718607                 1 ONLINE                 3072 YES D:\APP\ORACLE\ORADATA\TEST\TEST01P\LFREE05X.DBF    LFREE
12 rows selected.
--//CHECKPOINT_CHANGE#=12003329.12003329 = 0xb72801 ,可以发现检查点现在是最后一次alter system checkpoint ;的检查点.

SYS@test> alter database open ;
Database altered.

SYS@test> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE#  , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name  FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME     CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME                                               TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------ ---------------- --- -------------------------------------------------- ----------------
    1           12110910 2020-08-09 18:12:14                9                 1 ONLINE             3140 YES D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF            SYSTEM
    2            1102125 2018-10-06 23:24:44              227                 1 ONLINE               37 NO  D:\APP\ORACLE\ORADATA\TEST\PDBSEED\SYSTEM01.DBF    SYSTEM
    3           12110910 2020-08-09 18:12:14             4748                 1 ONLINE             3137 YES D:\APP\ORACLE\ORADATA\TEST\SYSAUX01.DBF            SYSAUX
    4            1102125 2018-10-06 23:24:44             4762                 1 ONLINE               37 NO  D:\APP\ORACLE\ORADATA\TEST\PDBSEED\SYSAUX01.DBF    SYSAUX
    5           12110910 2020-08-09 18:12:14             6090                 1 ONLINE             3137 YES D:\APP\ORACLE\ORADATA\TEST\UNDOTBS01.DBF           UNDOTBS1
    6            1102125 2018-10-06 23:24:44             6212                 1 ONLINE               37 NO  D:\APP\ORACLE\ORADATA\TEST\PDBSEED\UNDOTBS01.DBF   UNDOTBS1
    7           12110910 2020-08-09 18:12:14            28597                 1 ONLINE             3178 YES D:\APP\ORACLE\ORADATA\TEST\USERS01.DBF             USERS
    8           12111106 2020-08-09 18:12:45          1103550                 1 ONLINE             3217 NO  D:\APP\ORACLE\ORADATA\TEST\TEST01P\SYSTEM01.DBF    SYSTEM
    9           12111106 2020-08-09 18:12:45          1103553                 1 ONLINE             3215 NO  D:\APP\ORACLE\ORADATA\TEST\TEST01P\SYSAUX01.DBF    SYSAUX
   10           12111106 2020-08-09 18:12:45          1103555                 1 ONLINE             3215 NO  D:\APP\ORACLE\ORADATA\TEST\TEST01P\UNDOTBS01.DBF   UNDOTBS1
   11           12111106 2020-08-09 18:12:45          1105613                 1 ONLINE             3218 NO  D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF     USERS
   36           12111106 2020-08-09 18:12:45          3718607                 1 ONLINE             3076 NO  D:\APP\ORACLE\ORADATA\TEST\TEST01P\LFREE05X.DBF    LFREE
12 rows selected.

SYS@test> # alter pluggable database all open;
Pluggable database altered.

SYS@test> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE#  , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name  FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME     CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME                                               TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------ ---------------- --- -------------------------------------------------- --------------------
    1           12110910 2020-08-09 18:12:14                9                 1 ONLINE             3140 YES D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF            SYSTEM
    2            1102125 2018-10-06 23:24:44              227                 1 ONLINE               37 NO  D:\APP\ORACLE\ORADATA\TEST\PDBSEED\SYSTEM01.DBF    SYSTEM
    3           12110910 2020-08-09 18:12:14             4748                 1 ONLINE             3137 YES D:\APP\ORACLE\ORADATA\TEST\SYSAUX01.DBF            SYSAUX
    4            1102125 2018-10-06 23:24:44             4762                 1 ONLINE               37 NO  D:\APP\ORACLE\ORADATA\TEST\PDBSEED\SYSAUX01.DBF    SYSAUX
    5           12110910 2020-08-09 18:12:14             6090                 1 ONLINE             3137 YES D:\APP\ORACLE\ORADATA\TEST\UNDOTBS01.DBF           UNDOTBS1
    6            1102125 2018-10-06 23:24:44             6212                 1 ONLINE               37 NO  D:\APP\ORACLE\ORADATA\TEST\PDBSEED\UNDOTBS01.DBF   UNDOTBS1
    7           12110910 2020-08-09 18:12:14            28597                 1 ONLINE             3178 YES D:\APP\ORACLE\ORADATA\TEST\USERS01.DBF             USERS
    8           12112275 2020-08-09 18:15:15          1103550                 1 ONLINE             3218 YES D:\APP\ORACLE\ORADATA\TEST\TEST01P\SYSTEM01.DBF    SYSTEM
    9           12112275 2020-08-09 18:15:15          1103553                 1 ONLINE             3216 YES D:\APP\ORACLE\ORADATA\TEST\TEST01P\SYSAUX01.DBF    SYSAUX
   10           12112275 2020-08-09 18:15:15          1103555                 1 ONLINE             3216 YES D:\APP\ORACLE\ORADATA\TEST\TEST01P\UNDOTBS01.DBF   UNDOTBS1
   11           12112275 2020-08-09 18:15:15          1105613                 1 ONLINE             3219 YES D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF     USERS
   36           12112275 2020-08-09 18:15:15          3718607                 1 ONLINE             3077 YES D:\APP\ORACLE\ORADATA\TEST\TEST01P\LFREE05X.DBF    LFREE
12 rows selected.
--//并不需要归档日志恢复.

6.看看alert日志记录:

2020-08-09T18:06:32.280473+08:00
Errors in file D:\APP\ORACLE\diag\rdbms\test\test\trace\test_ora_7148.trc:
ORA-10873: file 1 needs to be either taken out of backup mode or media recovered
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ORA-01110: data file 1: 'D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF'
ORA-10873 signalled during: ALTER DATABASE OPEN...
2020-08-09T18:06:32.448483+08:00
Errors in file D:\APP\ORACLE\diag\rdbms\test\test\trace\test_m000_4076.trc:
ORA-01110: 数据文件 1: 'D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF'
2020-08-09T18:06:32.821504+08:00
Errors in file D:\APP\ORACLE\diag\rdbms\test\test\trace\test_m000_4076.trc:
ORA-01110: 数据文件 3: 'D:\APP\ORACLE\ORADATA\TEST\SYSAUX01.DBF'
2020-08-09T18:06:33.118521+08:00
Errors in file D:\APP\ORACLE\diag\rdbms\test\test\trace\test_m000_4076.trc:
ORA-01110: 数据文件 5: 'D:\APP\ORACLE\ORADATA\TEST\UNDOTBS01.DBF'
2020-08-09T18:06:33.396537+08:00
Errors in file D:\APP\ORACLE\diag\rdbms\test\test\trace\test_m000_4076.trc:
ORA-01110: 数据文件 7: 'D:\APP\ORACLE\ORADATA\TEST\USERS01.DBF'
2020-08-09T18:06:33.638551+08:00
Errors in file D:\APP\ORACLE\diag\rdbms\test\test\trace\test_m000_4076.trc:
ORA-01110: 数据文件 8: 'D:\APP\ORACLE\ORADATA\TEST\TEST01P\SYSTEM01.DBF'
2020-08-09T18:06:33.874564+08:00
Errors in file D:\APP\ORACLE\diag\rdbms\test\test\trace\test_m000_4076.trc:
ORA-01110: 数据文件 9: 'D:\APP\ORACLE\ORADATA\TEST\TEST01P\SYSAUX01.DBF'
2020-08-09T18:06:34.063575+08:00
Errors in file D:\APP\ORACLE\diag\rdbms\test\test\trace\test_m000_4076.trc:
ORA-01110: 数据文件 10: 'D:\APP\ORACLE\ORADATA\TEST\TEST01P\UNDOTBS01.DBF'
2020-08-09T18:06:34.292588+08:00
Errors in file D:\APP\ORACLE\diag\rdbms\test\test\trace\test_m000_4076.trc:
ORA-01110: 数据文件 11: 'D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF'
2020-08-09T18:06:34.620607+08:00
Errors in file D:\APP\ORACLE\diag\rdbms\test\test\trace\test_m000_4076.trc:
ORA-01110: 数据文件 36: 'D:\APP\ORACLE\ORADATA\TEST\TEST01P\LFREE05X.DBF'
2020-08-09T18:06:55.922825+08:00
ARCH: STARTING ARCH PROCESSES
Starting background process ARC0
2020-08-09T18:06:55.935826+08:00
ARC0 started with pid=33, OS id=7176
Starting background process ARC1
2020-08-09T18:06:55.949827+08:00
ARC1 started with pid=34, OS id=8120
2020-08-09T18:06:56.949884+08:00
Starting background process ARC2
2020-08-09T18:06:56.960885+08:00
ARC2 started with pid=35, OS id=7352
2020-08-09T18:06:57.959942+08:00
Starting background process ARC3
2020-08-09T18:06:57.970942+08:00
ARC3 started with pid=36, OS id=7336
2020-08-09T18:06:58.971000+08:00
ARC0: Archival started
ARC1: Archival started
ARC2: Archival started
2020-08-09T18:06:58.972000+08:00
ARC1: Becoming a 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
2020-08-09T18:06:58.983000+08:00
ARC3: Archival started
ARCH: STARTING ARCH PROCESSES COMPLETE
2020-08-09T18:06:58.994001+08:00
TT00: Gap Manager starting (PID:6364)
2020-08-09T18:07:11.518717+08:00
alter database end backup
~~~~~~~~~~~~~~~~~~~~~~~~~
Completed: alter database end backup

2020-08-09T18:12:13.376983+08:00
alter database open
2020-08-09T18:12:13.631997+08:00
Ping without log force is disabled:
  instance mounted in exclusive mode.
2020-08-09T18:12:13.754004+08:00
Beginning crash recovery of 1 threads
 parallel recovery started with 3 processes
2020-08-09T18:12:13.968016+08:00
Started redo scan
2020-08-09T18:12:14.283034+08:00
Completed redo scan
 read 0 KB redo, 0 data blocks need recovery
2020-08-09T18:12:14.339038+08:00
Started redo application at
 Thread 1: logseq 167, block 36286, offset 0, scn 0x0000000000b7459a
2020-08-09T18:12:14.399041+08:00
Recovery of Online Redo Log: Thread 1 Group 2 Seq 167 Reading mem 0
  Mem# 0: D:\APP\ORACLE\ORADATA\TEST\REDO02.LOG
2020-08-09T18:12:14.407041+08:00
Completed redo application of 0.00MB
2020-08-09T18:12:14.424042+08:00
Completed crash recovery at
 Thread 1: RBA 167.36286.0, nab 36286, scn 0x0000000000b7459b
 0 data blocks read, 0 data blocks written, 0 redo k-bytes read
Endian type of dictionary set to little
2020-08-09T18:12:14.879068+08:00

7.总结:
--//实际上使用热备模式已经很少见.出现执行热备模式后,注意检查视图v$backup.
--//异常关闭,打开时注意看提示.一般情况下需要仅仅需要在线日志完好就ok了.
--//并不需要bbed修复文件头.仅仅需要alter database end   backup ;或者recover database就ok了.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2710349/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2713
  • 访问量
    6519911