11g库的sysaux表空间因缺少历史归档,导致无法正常online使数据库的很多功能受限,通过BBED实现恢复过程.
还原故障
查看状态
SQL> col name for a50
SQL> select file#,name,status from v$datafile;
FILE# NAME STATUS
---------- ------------------------------------------------------------ -------------
1 /u01/app/oracle/oradata/liyuper/system01.dbf SYSTEM
2 /u01/app/oracle/oradata/liyuper/sysaux01.dbf ONLINE
3 /u01/app/oracle/oradata/liyuper/undotbs01.dbf ONLINE
4 /u01/app/oracle/oradata/liyuper/users01.dbf ONLINE
5 /u01/app/oracle/oradata/liyuper/example01.dbf ONLINE
6 /u01/lizi01.dbf ONLINE
7 /u01/lizi02.dbf ONLINE
8 /u01/lizi03.dbf ONLINE
9 /u01/app/oracle/oradata/liyuper/ogg01.dbf ONLINE
9 rows selected.
SQL>
offline数据文件
SQL> alter database datafile 2 offline;
Database altered.
SQL>
查看归档路径
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 68
Next log sequence to archive 70
Current log sequence 70
SQL> show parameter RECOVERY
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/fast_recovery_
area
db_recovery_file_dest_size big integer 4182M
recovery_parallelism integer 0
SQL>
切换归档
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL>
删除归档
[oracle@rac1 ~]$ cd /u01/app/oracle/fast_recovery_area/LIYUPER/archivelog/
[oracle@rac1 archivelog]$ ls -l
total 12
drwxr-x--- 2 oracle oinstall 4096 Mar 11 22:58 2019_03_11
drwxr-x--- 2 oracle oinstall 4096 Mar 12 07:26 2019_03_12
drwxr-x--- 2 oracle oinstall 4096 Mar 16 11:35 2019_03_16
[oracle@rac1 archivelog]$ rm * -rf
[oracle@rac1 archivelog]$ ls -l
total 0
[oracle@rac1 archivelog]$
故障现象
SQL> col name for a50 SQL> select file#,name,status from v$datafile;
FILE# NAME STATUS ---------- -------------------------------------------------- ------- 1 /u01/app/oracle/oradata/liyuper/system01.dbf SYSTEM 2 /u01/app/oracle/oradata/liyuper/sysaux01.dbf RECOVER 3 /u01/app/oracle/oradata/liyuper/undotbs01.dbf ONLINE 4 /u01/app/oracle/oradata/liyuper/users01.dbf ONLINE 5 /u01/app/oracle/oradata/liyuper/example01.dbf ONLINE 6 /u01/lizi01.dbf ONLINE 7 /u01/lizi02.dbf ONLINE 8 /u01/lizi03.dbf ONLINE 9 /u01/app/oracle/oradata/liyuper/ogg01.dbf ONLINE
9 rows selected.
SQL>
online数据文件
SQL> alter database datafile 2 online; alter database datafile 2 online * ERROR at line 1: ORA-01113: file 2 needs media recovery ORA-01110: data file 2: '/u01/app/oracle/oradata/liyuper/sysaux01.dbf'
SQL>
recover数据文件
SQL> recover datafile 2; ORA-00279: change 1308625 generated at 03/16/2019 11:32:15 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/LIYUPER/archivelog/2019_03_16/o1_mf_1_69_g8rvmhw2_.arc ORA-00280: change 1308625 for thread 1 is in sequence #69
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log '/u01/app/oracle/fast_recovery_area/LIYUPER/archivelog/2019_03_16/o1_mf_1_69_g8rvmhw2_.arc' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3
SQL>
由于归档被删除,导致数据文件无法recover
修改数据文件头
现在datafile 2不能恢复,需要修改的就是该datafile header 相关的scn信息,由于只是归档丢失,只需要推进scn即可。
datafile 2 scn
BBED> info File# Name Size(blks) ----- ---- ---------- 1 /u01/app/oracle/oradata/liyuper/system01.dbf 96000 2 /u01/app/oracle/oradata/liyuper/sysaux01.dbf 69120 3 /u01/app/oracle/oradata/liyuper/undotbs01.dbf 11520 4 /u01/app/oracle/oradata/liyuper/users01.dbf 640 5 /u01/app/oracle/oradata/liyuper/example01.dbf 40080 6 /u01/lizi01.dbf 1280 7 /u01/lizi02.dbf 1280 8 /u01/lizi03.dbf 1280 9 /u01/app/oracle/oradata/liyuper/ogg01.dbf 6400
BBED> set file 2 block 1 FILE# 2 BLOCK# 1
BBED> set count 16 COUNT 16
BBED> dump /v offset 484 File: /u01/app/oracle/oradata/liyuper/sysaux01.dbf (2) Block: 1 Offsets: 484 to 499 Dba:0x00800001 ------------------------------------------------------- d1f71300 00000000 bf77c93b 01000000 l .........w.;....
<16 bytes per line>
BBED>
查看数据库scn
SQL> col name for a50 SQL> select file#,name,status,checkpoint_change# from v$datafile;
FILE# NAME STATUS CHECKPOINT_CHANGE# ---------- ------------------------------------------------------------------ ------------------ ------------------ 1 /u01/app/oracle/oradata/liyuper/system01.dbf SYSTEM 1315215 2 /u01/app/oracle/oradata/liyuper/sysaux01.dbf RECOVER 1308625 3 /u01/app/oracle/oradata/liyuper/undotbs01.dbf ONLINE 1315215 4 /u01/app/oracle/oradata/liyuper/users01.dbf ONLINE 1315215 5 /u01/app/oracle/oradata/liyuper/example01.dbf ONLINE 1315215 6 /u01/lizi01.dbf ONLINE 1315215 7 /u01/lizi02.dbf ONLINE 1315215 8 /u01/lizi03.dbf ONLINE 1315215 9 /u01/app/oracle/oradata/liyuper/ogg01.dbf ONLINE 1315215
9 rows selected.
SQL>
转换16进制
SQL> select to_char('&number','xxxxxxxx') from dual; Enter value for number: 1308625 old 1: select to_char('&number','xxxxxxxx') from dual new 1: select to_char('1308625','xxxxxxxx') from dual
TO_CHAR(' --------- 13f7d1
SQL> select to_char('&number','xxxxxxxx') from dual; Enter value for number: 1315215 old 1: select to_char('&number','xxxxxxxx') from dual new 1: select to_char('1315215','xxxxxxxx') from dual
TO_CHAR(' --------- 14118f
SQL>
bbed修改scn
BBED> modify /x 8f1114 offset 484 File: /u01/app/oracle/oradata/liyuper/sysaux01.dbf (2) Block: 1 Offsets: 484 to 499 Dba:0x00800001 ------------------------------------------------------------------------ 8f111400 00000000 bf77c93b 01000000
<32 bytes per line>
BBED> sum apply Check value for File 2, Block 1: current = 0xb120, required = 0xb120
BBED> verify DBVERIFY - Verification starting FILE = /u01/app/oracle/oradata/liyuper/sysaux01.dbf BLOCK = 1
DBVERIFY - Verification complete
Total Blocks Examined : 1 Total Blocks Processed (Data) : 0 Total Blocks Failing (Data) : 0 Total Blocks Processed (Index): 0 Total Blocks Failing (Index): 0 Total Blocks Empty : 0 Total Blocks Marked Corrupt : 0 Total Blocks Influx : 0 Message 531 not found; product=RDBMS; facility=BBED
BBED>
恢复数据文件
recover数据文件
SQL> recover datafile 2 Media recovery complete. SQL>
online数据文件
SQL> alter database datafile 2 online;
Database altered.
SQL>
切归档换
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL>
查看状态
SQL> select file#,name,status,checkpoint_change# from v$datafile;
FILE# NAME STATUS CHECKPOINT_CHANGE# ---------- -------------------------------------------------- ------- ------------------ 1 /u01/app/oracle/oradata/liyuper/system01.dbf SYSTEM 1316727 2 /u01/app/oracle/oradata/liyuper/sysaux01.dbf ONLINE 1316727 3 /u01/app/oracle/oradata/liyuper/undotbs01.dbf ONLINE 1316727 4 /u01/app/oracle/oradata/liyuper/users01.dbf ONLINE 1316727 5 /u01/app/oracle/oradata/liyuper/example01.dbf ONLINE 1316727 6 /u01/lizi01.dbf ONLINE 1316727 7 /u01/lizi02.dbf ONLINE 1316727 8 /u01/lizi03.dbf ONLINE 1316727 9 /u01/app/oracle/oradata/liyuper/ogg01.dbf ONLINE 1316727
9 rows selected.
SQL>
故障恢复!
备注:ub4 kscnbas @484 — —真正2号文件的检查点SCN 8f1114 (倒:14118f)