[20180413]熱備模式相關問題.txt
[20180413]熱備模式相關問題.txt
--//昨天遇到開啟熱備模式的相關問題,一個不是很重要的資料庫,估計有人開啟了熱備模式,異常關機,開啟報錯,
--//自己在測試環境重複測試:
1.環境:
SCOTT@book> @ &r/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
SCOTT@book> alter database begin backup ;
Database altered.
SYS@book> shutdown immediate
ORA-01149: cannot shutdown - file 1 has online backup set
ORA-01110: data file 1: '/mnt/ramdisk/book/system01.dbf'
--//處於熱備模式,無法正常關閉資料庫.排除system表空間看看,是否可以關閉資料庫.
SYS@book> set numw 12
SYS@book> select * from v$backup ;
FILE# STATUS CHANGE# TIME
------------ ------------------ ------------ -------------------
1 ACTIVE 13277525910 2018-04-13 09:03:44
2 ACTIVE 13277525910 2018-04-13 09:03:44
3 ACTIVE 13277525910 2018-04-13 09:03:44
4 ACTIVE 13277525910 2018-04-13 09:03:44
5 ACTIVE 13277525910 2018-04-13 09:03:44
6 ACTIVE 13277525910 2018-04-13 09:03:44
6 rows selected.
SYS@book> alter tablespace system end backup ;
Tablespace altered.
SYS@book> select * from v$backup ;
FILE# STATUS CHANGE# TIME
------------ ------------------ ------------ -------------------
1 NOT ACTIVE 13277525910 2018-04-13 09:03:44
2 ACTIVE 13277525910 2018-04-13 09:03:44
3 ACTIVE 13277525910 2018-04-13 09:03:44
4 ACTIVE 13277525910 2018-04-13 09:03:44
5 ACTIVE 13277525910 2018-04-13 09:03:44
6 ACTIVE 13277525910 2018-04-13 09:03:44
6 rows selected.
SYS@book> shutdown immediate
ORA-01149: cannot shutdown - file 2 has online backup set
ORA-01110: data file 2: '/mnt/ramdisk/book/sysaux01.dbf'
--//也就是處於熱備份,如果有表空間處於熱備模式,無法正常關閉資料庫.我結束sysaux,UNDOTBS1也一樣遇到上述問題.
2.模擬異常關閉資料庫:
SYS@book> shutdown abort ;
ORACLE instance shut down.
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 4 needs to be either taken out of backup mode or media recovered
ORA-01110: data file 4: '/mnt/ramdisk/book/users01.dbf'
SYS@book> select * from v$backup ;
FILE# STATUS CHANGE# TIME
------------ ------------------ ------------ -------------------
1 NOT ACTIVE 13277525910 2018-04-13 09:03:44
2 NOT ACTIVE 13277525910 2018-04-13 09:03:44
3 NOT ACTIVE 13277525910 2018-04-13 09:03:44
4 ACTIVE 13277525910 2018-04-13 09:03:44
5 ACTIVE 13277525910 2018-04-13 09:03:44
6 ACTIVE 13277525910 2018-04-13 09:03:44
6 rows selected.
3.解決方法:
--//方法1:
SYS@book> recover datafile 4;
Media recovery complete.
SYS@book> alter database open ;
alter database open
*
ERROR at line 1:
ORA-10873: file 5 needs to be either taken out of backup mode or media recovered
ORA-01110: data file 5: '/mnt/ramdisk/book/example01.dbf'
--//簡單recover database可以修復.
--//方法2:
SYS@book> alter database end backup ;
Database altered.
SYS@book> alter database open ;
Database altered.
--//不知道如果歸檔不存在情況如何,驗證看看.
4.驗證歸檔不存在的情況:
SYS@book> alter tablespace users begin backup ;
Tablespace altered.
SYS@book> select * from v$backup where file#=4;
FILE# STATUS CHANGE# TIME
----- ------------------ ------------ -------------------
4 ACTIVE 13277546699 2018-04-13 09:16:22
SYS@book> alter system archive log current ;
System altered.
SYS@book> alter system archive log current ;
System altered.
SYS@book> alter system archive log current ;
System altered.
SYS@book> alter system archive log current ;
System altered.
SYS@book> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/archivelog/book/
Oldest online log sequence 747
Next log sequence to archive 749
Current log sequence 749
SYS@book> column name format a52
SYS@book> select NAME,SEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE#,COMPLETION_TIME from v$archived_log where dest_id=1 and SEQUENCE#>=743;
NAME SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# COMPLETION_TIME
---------------------------------------------------- ------------ ------------- ------------ -------------------
/u01/app/oracle/archivelog/book/1_743_896605872.dbf 743 13277475418 13277507502 2018-04-13 04:48:00
/u01/app/oracle/archivelog/book/1_744_896605872.dbf 744 13277507502 13277546258 2018-04-13 09:14:09
/u01/app/oracle/archivelog/book/1_745_896605872.dbf 745 13277546258 13277546740 2018-04-13 09:16:54
/u01/app/oracle/archivelog/book/1_746_896605872.dbf 746 13277546740 13277546745 2018-04-13 09:16:55
/u01/app/oracle/archivelog/book/1_747_896605872.dbf 747 13277546745 13277546752 2018-04-13 09:16:59
/u01/app/oracle/archivelog/book/1_748_896605872.dbf 748 13277546752 13277546757 2018-04-13 09:17:01
6 rows selected.
SYS@book> alter system checkpoint ;
System altered.
SYS@book> SELECT file#, CHECKPOINT_CHANGE#,CHECKPOINT_TIME,UNRECOVERABLE_CHANGE#,UNRECOVERABLE_TIME,LAST_CHANGE#,LAST_TIME, OFFLINE_CHANGE#, ONLINE_CHANGE#,FIRST_NONLOGGED_SCN,FIRST_NONLOGGED_TIME,status,name FROM v$datafile;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME LAST_CHANGE# LAST_TIME OFFLINE_CHANGE# ONLINE_CHANGE# FIRST_NONLOGGED_SCN FIRST_NONLOGGED_TIM STATUS NAME
----- ------------------ ------------------- --------------------- ------------------- ------------ ------------------- --------------- -------------- ------------------- ------------------- ------- --------------------------------------------------
1 13277547058 2018-04-13 09:21:36 0 925701 925702 0 SYSTEM /mnt/ramdisk/book/system01.dbf
2 13277547058 2018-04-13 09:21:36 0 925701 925702 0 ONLINE /mnt/ramdisk/book/sysaux01.dbf
3 13277547058 2018-04-13 09:21:36 0 925701 925702 0 ONLINE /mnt/ramdisk/book/undotbs01.dbf
4 13277546699 2018-04-13 09:16:22 0 925701 925702 0 ONLINE /mnt/ramdisk/book/users01.dbf
5 13277547058 2018-04-13 09:21:36 13274819965 2017-01-16 22:00:05 952916 952921 0 ONLINE /mnt/ramdisk/book/example01.dbf
6 13277547058 2018-04-13 09:21:36 0 0 0 0 ONLINE /mnt/ramdisk/book/tea01.dbf
6 rows selected.
SYS@book> 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 13277547058 2018-04-13 09:21:36 7 925702 ONLINE 922 YES /mnt/ramdisk/book/system01.dbf SYSTEM
2 13277547058 2018-04-13 09:21:36 1834 925702 ONLINE 911 YES /mnt/ramdisk/book/sysaux01.dbf SYSAUX
3 13277547058 2018-04-13 09:21:36 923328 925702 ONLINE 832 YES /mnt/ramdisk/book/undotbs01.dbf UNDOTBS1
4 13277546699 2018-04-13 09:16:22 16143 925702 ONLINE 918 YES /mnt/ramdisk/book/users01.dbf USERS
5 13277547058 2018-04-13 09:21:36 952916 925702 ONLINE 827 YES /mnt/ramdisk/book/example01.dbf EXAMPLE
6 13277547058 2018-04-13 09:21:36 13276257767 925702 ONLINE 295 YES /mnt/ramdisk/book/tea01.dbf TEA
6 rows selected.
--//可以發現資料檔案4的檔案頭scn被凍結,scn=13277546699,控制檔案記錄的也是13277546699.
--//移出其中的一個歸檔看看.seq=748.
$ mv /u01/app/oracle/archivelog/book/1_748_896605872.dbf /u01/app/oracle/archivelog/book/1_748_896605872.dbf_xxx
SYS@book> shutdown abort ;
ORACLE instance shut down.
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 4 needs to be either taken out of backup mode or media recovered
ORA-01110: data file 4: '/mnt/ramdisk/book/users01.dbf'
SYS@book> recover datafile 4;
ORA-00279: change 13277567601 generated at 04/13/2018 09:28:36 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_750_896605872.dbf
ORA-00280: change 13277567601 for thread 1 is in sequence #750
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
--//注:recover datafile 4;是補做的,也就是選擇recover datafile如果歸檔不在無法繼續.
SYS@book> alter database end backup ;
Database altered.
--//OK.沒有問題.使用recover datafile有問題.
--//總之,不再建議在採用熱備模式管理oracle的備份.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2152883/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20180413]熱備模式相關問題2.txt模式
- Docker 相關問題Docker
- Java相關問題整理Java
- PHP相關問題集合PHP
- [20190515]熱備份模式與rman衝突.txt模式
- [20190929]bash使用bc計算的相關問題.txt
- mysql相關問題總結MySql
- 個人專案相關問題
- Sql Mode及相關問題SQL
- Oracle MTS的相關問題Oracle
- 瀏覽器相關問題瀏覽器
- [20191220]關於共享記憶體段相關問題.txt記憶體
- 關於盒模型相關的問題模型
- 關於 go-micro 相關問題Go
- [20190108]rlwrap sqlplus tee相關問題.txtSQL
- [20171113]修改表結構刪除列相關問題.txt
- [20200809]12c熱備份模式.txt模式
- Mysql跨庫主從熱備失效問題MySql
- 中介軟體相關問題整理
- java語言相關的問題Java
- Spring相關問題記錄Spring
- 面試遇到的redis相關問題面試Redis
- 資料庫事物相關問題資料庫
- SpringBoot-相關問題Spring Boot
- Oracle kill session相關問題（上）OracleSession
- Oracle kill session相關問題（下）OracleSession
- 多執行緒相關問題執行緒
- GoldenGate實施相關問題Go
- 【Java面試題】如何回答GC相關問題Java面試題GC
- [20191223]關於共享記憶體段相關問題3.txt記憶體
- [20230306]學習UNIFIED audit--dg相關問題.txtNifi
- 關於橋模式的問題模式
- 關於匯入模式問題模式
- Activity相關備忘
- HandlerThread解析以及相關問題分析thread
- TCP/IP 協議相關的問題TCP協議
- 前端面試之js相關問題前端面試JS
- Laravel佇列相關問題記錄Laravel佇列