​[20200809]12c熱備份模式.txt

lfree發表於2020-08-09

[20200809]12c熱備份模式.txt

--//昨天看連結:很奇怪作者為什麼要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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章