[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的备份.
