[20200809]12c熱備份模式.txt
[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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20190515]熱備份模式與rman衝突.txt模式
- 熱備份原理
- Oracle 熱備份Oracle
- [20180413]熱備模式相關問題.txt模式
- [20171031]rman備份壓縮模式.txt模式
- mysql的冷備份與熱備份MySql
- oracle的熱備份和冷備份Oracle
- [20150913]檔案檢查點_熱備份.txt
- oracle 12c rman備份pdbOracle
- 揭祕ORACLE備份之－－熱備份（也叫聯機備份）Oracle
- Oracle冷備份和熱備份的處理Oracle
- Oracle 熱備份和冷備份的區別Oracle
- [20180413]熱備模式相關問題2.txt模式
- Oracle OCP(62)：熱備份Oracle
- oracle雙機熱備份Oracle
- 生成熱備份指令碼指令碼
- Oracle熱備份原理分析Oracle
- MySQL的冷備份和熱備份概念理解（轉）MySql
- Oracle學習系列—資料庫備份—熱備份Oracle資料庫
- Oracle 12c 備份與恢復Oracle
- Oracle資料庫冷備份與熱備份操作梳理Oracle資料庫
- 熱備份與RMAN備份中split block的處理BloC
- 歸納熱備份機制
- oracle雙機熱備份方法Oracle
- 處於熱備份模式下shutdown abort之後db的恢復模式
- [20150308]熱備份和資料庫檢查點.txt資料庫
- 資料庫備份與異機恢復——熱備份方式資料庫
- oracle聯機熱備份的原理，及rman增量備份原理Oracle
- Oracle聯機熱備份的原理及rman增量備份原理Oracle
- Oracle物理熱備份指令碼(ZT)Oracle指令碼
- 歸檔模式下聯機熱備份某個表空間步驟模式
- oracle聯機熱備份的原理及rman增量備份原理(zt)Oracle
- 【物理熱備】（下）備份恢復系統表空間 手工備份恢復
- RAC模式下的備份策略以及RMAN備份指令碼模式指令碼
- Oracle備份恢復之熱備份恢復及異機恢復Oracle
- Backup And Recovery User's Guide-RMAN備份概念-聯機備份和備份模式GUIIDE模式
- oracle聯機熱備份的原理(轉)Oracle
- Oracle使用者管理熱備份原理Oracle