[Oracle datagard]從庫恢復之 ORA-16032: parameter ORA-07286:問題

機房斷電，所以oracle的datagard的從庫需要重新啟動：

1 登入sqlplus啟動，出現報錯資訊：

SQL> STARTUP MOUNT;

ORA-16032: parameter LOG_ARCHIVE_DEST_3 destination string cannot be translated
ORA-07286: sksagdi: cannot obtain device information.
Linux-x86_64 Error: 2: No such file or directory

2 去檢視找下 LOG_ARCHIVE_DEST_3 的具體路徑

[root@localhost ~]# su - oracle 
[oracle@localhost ~]$ echo $ORACLE_HOME
/oracle/app/oracle/product/11.2.0/dbhome_1
[oracle@localhost ~]$ cd /oracle/app/oracle/product/11.2.0/dbhome_1
[oracle@localhost dbhome_1]$ 
[oracle@localhost dbhome_1]$ cd dbs
[oracle@localhost dbs]$ ll
總用量 13160
-rw-rw----. 1 oracle dba          1544 5月   4 14:49 hc_powerdes.dat
-rw-r--r--. 1 oracle dba          2851 5月  15 2009 init.ora
-rw-r-----. 1 oracle dba            24 8月  27 2013 lkPDDGUNQ
-rw-r-----. 1 oracle oinstall     1536 8月  27 2013 orapwpowerdes
-rw-r-----. 1 oracle dba      13451264 4月  27 05:00 snapcf_powerdes.f

-rw-r-----. 1 oracle dba          5632 8月  30 2013 spfilepowerdes.ora

3 # 找到了路徑

[oracle@localhost dbs]$ strings spfile*.ora|grep dest_3
*.log_archive_dest_3='LOCATION=/data/oracle/oradgdata/standby_archive VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=pddgunq'

4  check路徑，排查問題
[root@localhost data]# ls -l /data/oracle/oradgdata/standby_archive
ls: 無法訪問/data/oracle/oradgdata/standby_archive: 沒有那個檔案或目錄

[oracle@localhost dbs]$ mkdir -p /data/oracle/oradgdata/standby_archive
mkdir: 無法建立目錄"/data/oracle": 只讀檔案系統

[oracle@localhost dbhome_1]$ df -h
檔案系統       容量  已用  可用 已用%% 掛載點
/dev/mapper/VolGroup-lv_root
                       50G   28G   19G  60% /
tmpfs                 8.9G  3.0G  5.9G  34% /dev/shm
/dev/sda2             485M   76M  384M  17% /boot
/dev/sda1             200M  260K  200M   1% /boot/efi
/dev/mapper/VolGroup-lv_home
                       76G   37G   36G  51% /home
/dev/sdb1            1008G  221G  737G  24% /data

5 解決問題

/data已經存在了，只是無法使用，google，看到2種解決方案
mkdir: 無法建立目錄‘XX’: 只讀檔案系統
touch: 無法觸碰 “XX”: 只讀檔案系統,
建立檔案和目錄都提示錯誤，
       解決方法如下:
                      第一，重起伺服器
                         我的這樣就好了。
                   第二，
               如果第一不好使的話，
                          就得進行修復硬碟了，
                    開機進入修復模式，
                    看你是在哪個目錄下能建立檔案，然後用 fsck.ext3 -y ..掛載分割槽...進行修復就ok了。
參考：
reboot試試看

6 再次登入sqlplus start從庫

SQL>  STARTUP MOUNT;
ORACLE instance started.


Total System Global Area 5344731136 bytes
Fixed Size		    2213136 bytes
Variable Size		 3489663728 bytes
Database Buffers	 1811939328 bytes
Redo Buffers		   40914944 bytes
Database mounted.


SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;


Database altered.


SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-10456: cannot open standby database; media recovery session may be in
progress

# 有報錯資訊，看到這裡，需要暫時中止media recovery了。

SQL> recover managed standby database cancel;
Media recovery complete.

7 試圖再自動 recover下，不過還是fail，只要再次中止

SQL> recover automatic standby database ;
ORA-00279: change 10386441904 generated at 04/18/2014 17:56:46 needed for
thread 1
ORA-00289: suggestion :
/data/oracle/oradgdata/standby_archive/1_7368_821708334.dbf
ORA-00280: change 10386441904 for thread 1 is in sequence #7368
ORA-00278: log file
'/data/oracle/oradgdata/standby_archive/1_7368_821708334.dbf' no longer needed
for this recovery
ORA-00308: cannot open archived log
'/data/oracle/oradgdata/standby_archive/1_7368_821708334.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log
'/data/oracle/oradgdata/standby_archive/1_7368_821708334.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

SQL> RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Media recovery complete.

8，檢視下當前datagard的狀態

SQL> SELECT MESSAGE FROM V$DATAGUARD_STATUS;


MESSAGE
--------------------------------------------------------------------------------
ARC0: Archival started
ARC1: Archival started
ARC2: Archival started
ARC3: Archival started
ARC4: Archival started
ARC5: Archival started
ARC6: Archival started
ARC7: Archival started
ARC8: Archival started
ARC9: Archival started
ARCa: Archival started


MESSAGE
--------------------------------------------------------------------------------
ARCb: Archival started
ARCc: Archival started
ARCd: Archival started
ARCe: Archival started
ARCf: Archival started
ARCg: Archival started
ARCh: Archival started
ARCi: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC2: Becoming the heartbeat ARCH
ARCj: Archival started


MESSAGE
--------------------------------------------------------------------------------
Media Recovery Start: Managed Standby Recovery
Managed Standby Recovery not using Real Time Apply
Media Recovery Waiting for thread 1 sequence 7368
Fetching gap sequence in thread 1, gap sequence 7368-7392
FAL[client]: Failed to request gap sequence
 GAP - thread 1 sequence 7368-7392
 DBID 3391761643 branch 821708334
FAL[client]: All defined FAL servers have been attempted.
Attempt to start background Managed Standby Recovery process
MRP0: Background Managed Standby Recovery process started
Managed Standby Recovery not using Real Time Apply


MESSAGE
--------------------------------------------------------------------------------
Media Recovery Waiting for thread 1 sequence 7368
Fetching gap sequence in thread 1, gap sequence 7368-7392
FAL[client]: Failed to request gap sequence
 GAP - thread 1 sequence 7368-7392
 DBID 3391761643 branch 821708334
FAL[client]: All defined FAL servers have been attempted.
MRP0: Background Media Recovery cancelled with status 16037
MRP0: Background Media Recovery process shutdown
Managed Standby Recovery Canceled
Managed Standby Recovery not using Real Time Apply
Attempt to start background Managed Standby Recovery process


MESSAGE
--------------------------------------------------------------------------------
MRP0: Background Managed Standby Recovery process started
Managed Standby Recovery not using Real Time Apply
Media Recovery Waiting for thread 1 sequence 7368
Fetching gap sequence in thread 1, gap sequence 7368-7392
FAL[client]: Failed to request gap sequence
 GAP - thread 1 sequence 7368-7392
 DBID 3391761643 branch 821708334
FAL[client]: All defined FAL servers have been attempted.


52 rows selected.

9 再試試直接修改狀態到open吧
# 開啟standary從庫

SQL> alter database open;

Database altered.

SQL>  select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY
# 將備庫置於自動恢復狀態
SQL> recover managed standby database disconnect from session;
Media recovery complete.

10 # check，檢查主從的歸檔日誌情況
# 主庫狀態:

SQL> archive log list;                                                                                                                                                                         
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       /oracle/app/oracle/flash_recovery_area/archivelog
Oldest online log sequence     7777
Next log sequence to archive   7779
Current log sequence	       7779
SQL>   

#從庫狀態：

SQL>  archive log list;                                                                                                                                                                        
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       /data/oracle/oradgdata/standby_archive
Oldest online log sequence     7778
Next log sequence to archive   0
Current log sequence	       7779
SQL> 

最後問貓貓，怎麼樣才能徹底check下datagard主從資料一致，結果被貓貓美女一頓狂批，多看點datagard官方文件，別老問她，得自己去專研去總結。

哈哈，看來是得抽功夫去惡補下oracle datagard了。