因修改SYS密碼引起的gap問題修複

早上對資料庫進行例行巡檢時發現有一台資料庫，讀庫無法正常接收日志。檢視備庫日志發現最後一次同步時間是2017年7月8号（依賴EM監控，但是沒有收到報警，這個需要後期排查原因）。

之前對主庫巡檢時通過user$視圖檢視到sys密碼有效期低于14天後，将sys密碼通過密文修改的和之前一樣：

檢視密碼有效期語句：

因為改成與之前相同的密碼，是以沒有對備庫進行同步密碼檔案，看來是這個步驟導緻主備庫password files不同。

手動在主庫切換一次日志後，通過主庫日志發現報錯密碼檔案過期：

SQL> alter system switch logfile;

Error  received logging on to the standby
------------------------------------------------------------
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE, 
and that the SYS password is same in the password files.
      returning error ORA-
------------------------------------------------------------

将主庫的password files同步到備庫：

[oracle@gzkDB26 ~]$ scp $ORACLE_HOME/dbs/orapwsid @standby:$ORACLE_HOME/dbs

檢視備庫日志後發現出現gap：

[[email protected] ~]$ tail -f /oracle/app/oracle/diag/rdbms/maindb/center/trace/alert_center.log

Mon Jul  :: 
Primary database is in MAXIMUM PERFORMANCE mode
RFS[]: Assigned to RFS process 
RFS[]: Selected log  for thread  sequence  dbid  branch 
Mon Jul  :: 
RFS[]: Assigned to RFS process 
RFS[]: Opened log for thread  sequence  dbid  branch 
Mon Jul  :: 
Fetching gap sequence in thread , gap sequence -
Archived Log entry  added for thread  sequence  rlc  ID  dest :
Fetching gap sequence in thread , gap sequence -
Mon Jul  :: 
FAL[client]: Failed to request gap sequence
 GAP - thread  sequence -
 DBID  branch 
FAL[client]: All defined FAL servers have been attempted.
------------------------------------------------------------
Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization
parameter is defined to a value that's sufficiently large
enough to maintain adequate log switch information to resolve
archivelog gaps.
------------------------------------------------------------

在備庫檢視丢失的日志：

SQL> select * from v$archive_gap;

   THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------

在主庫上檢視日志檔案，發現實體檔案已經被删除（因空間有限，做了腳本隻保留一天歸檔日志）：

SQL> select name from v$archived_log where thread#=1 and dest_id=1 and sequence# between 1574855 and 1574954;

NAME
--------------------------------------------------------------------------------
....
....
....
100 rows selected.

因為主庫每天都在做增量備份，是以查找丢失的日志序列号對應的增量備份檔案：

[[email protected] dbs]$ rman target /

Recovery Manager: Release  - Production on Mon Jul  :: 

Copyright (c) , , Oracle and/or its affiliates.  All rights reserved.

connected to target database: CENTER (DBID=)

RMAN> list backup of archivelog from sequence  until sequence ;


List of Backup Sets
===================


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
   M    DISK        ::     -JUL-      
        BP Key:    Status: AVAILABLE  Compressed: YES  Tag: TAG20170709T010329
        Piece Name: /oracle/app/oracle/fast_recovery_area/STBYDB26/backupset/_07_09/o1_mf_annnn_TAG20170709T010329_dp26mfks_.bkp

  List of Archived Logs in backup set 
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
   M    DISK        ::     -JUL-      
        BP Key:    Status: AVAILABLE  Compressed: YES  Tag: TAG20170709T010329
        Piece Name: /oracle/app/oracle/fast_recovery_area/STBYDB26/backupset/_07_09/o1_mf_annnn_TAG20170709T010329_dp26qwks_.bkp

  List of Archived Logs in backup set 
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
   M    DISK        ::     -JUL-      
        BP Key:    Status: AVAILABLE  Compressed: YES  Tag: TAG20170709T010329
        Piece Name: /oracle/app/oracle/fast_recovery_area/STBYDB26/backupset/_07_09/o1_mf_annnn_TAG20170709T010329_dp26tvdj_.bkp

  List of Archived Logs in backup set 
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-
        -JUL-  -JUL-

傳輸備份檔案到備庫：

[oracle@gzkDB26 ]$ scp o1_mf_annnn_TAG20170709T010329_dp26* .:/oracle/backup
oracle@172..'s password: 
o1_mf_annnn_TAG20170709T010329_dp266yj9_.bkp                                                                                                                 100%  723MB  14.5MB/s   00:50    
o1_mf_annnn_TAG20170709T010329_dp2679d4_.bkp                                                                                                                 100%  833MB  26.9MB/s   00:31    
o1_mf_annnn_TAG20170709T010329_dp26fnso_.bkp                                                                                                                 100%  690MB  15.0MB/s   00:46    
o1_mf_annnn_TAG20170709T010329_dp26mfks_.bkp                                                                                                                 100%  575MB  14.8MB/s   00:39    
o1_mf_annnn_TAG20170709T010329_dp26qwks_.bkp                                                                                                                 100%  664MB  11.9MB/s   00:56    
o1_mf_annnn_TAG20170709T010329_dp26tvdj_.bkp                                                                                                                 100%  648MB  14.4MB/s   00:45    
o1_mf_annnn_TAG20170709T010329_dp26ynxv_.bkp                                                                                                                 100%  761MB  13.6MB/s   00:56

備庫基于增量備份進行恢複：

[[email protected] ~]$ rman target /

Recovery Manager: Release  - Production on Mon Jul  :: 

Copyright (c) , , Oracle and/or its affiliates.  All rights reserved.

connected to target database: CENTER (DBID=)

RMAN> catalog start with '/oracle/backup';

using target database control file instead of recovery catalog
searching for all files that match the pattern /oracle/backup

List of Files Unknown to the Database
=====================================
File Name: /oracle/backup/o1_mf_annnn_TAG20170709T010329_dp26ynxv_.bkp
File Name: /oracle/backup/o1_mf_annnn_TAG20170709T010329_dp26qwks_.bkp
File Name: /oracle/backup/o1_mf_annnn_TAG20170709T010329_dp2679d4_.bkp
File Name: /oracle/backup/o1_mf_annnn_TAG20170709T010329_dp26tvdj_.bkp
File Name: /oracle/backup/o1_mf_annnn_TAG20170709T010329_dp26mfks_.bkp
File Name: /oracle/backup/o1_mf_annnn_TAG20170709T010329_dp266yj9_.bkp
File Name: /oracle/backup/o1_mf_annnn_TAG20170709T010329_dp26fnso_.bkp

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /oracle/backup/o1_mf_annnn_TAG20170709T010329_dp26ynxv_.bkp
File Name: /oracle/backup/o1_mf_annnn_TAG20170709T010329_dp26qwks_.bkp
File Name: /oracle/backup/o1_mf_annnn_TAG20170709T010329_dp2679d4_.bkp
File Name: /oracle/backup/o1_mf_annnn_TAG20170709T010329_dp26tvdj_.bkp
File Name: /oracle/backup/o1_mf_annnn_TAG20170709T010329_dp26mfks_.bkp
File Name: /oracle/backup/o1_mf_annnn_TAG20170709T010329_dp266yj9_.bkp
File Name: /oracle/backup/o1_mf_annnn_TAG20170709T010329_dp26fnso_.bkp

RMAN> restore archivelog from sequence  until sequence ;

Starting restore at -JUL-
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID= device type=DISK

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: reading from backup piece /oracle/backup/o1_mf_annnn_TAG20170709T010329_dp26mfks_.bkp
channel ORA_DISK_1: piece handle=/oracle/backup/o1_mf_annnn_TAG20170709T010329_dp26mfks_.bkp tag=TAG20170709T010329
channel ORA_DISK_1: restored backup piece 
channel ORA_DISK_1: restore complete, elapsed time: ::
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: reading from backup piece /oracle/backup/o1_mf_annnn_TAG20170709T010329_dp26qwks_.bkp
channel ORA_DISK_1: piece handle=/oracle/backup/o1_mf_annnn_TAG20170709T010329_dp26qwks_.bkp tag=TAG20170709T010329
channel ORA_DISK_1: restored backup piece 
channel ORA_DISK_1: restore complete, elapsed time: ::
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: restoring archived log
archived log thread= sequence=
channel ORA_DISK_1: reading from backup piece /oracle/backup/o1_mf_annnn_TAG20170709T010329_dp26tvdj_.bkp
channel ORA_DISK_1: piece handle=/oracle/backup/o1_mf_annnn_TAG20170709T010329_dp26tvdj_.bkp tag=TAG20170709T010329
channel ORA_DISK_1: restored backup piece 
channel ORA_DISK_1: restore complete, elapsed time: ::
Finished restore at -JUL-

此時檢視備庫日志已經開始注冊丢失的日志檔案：

Media Recovery Log /oracle/app/oracle/fast_recovery_area/MAINDB/archivelog/_07_10/o1_mf_1_1574947_dp5whllr_.arc
Mon Jul  :: 
Media Recovery Log /oracle/app/oracle/fast_recovery_area/MAINDB/archivelog/_07_10/o1_mf_1_1574948_dp5wh40h_.arc
Mon Jul  :: 
RFS[]: Selected log  for thread  sequence  dbid  branch 
Mon Jul  :: 
Archived Log entry  added for thread  sequence  ID  dest :
RFS[]: Selected log  for thread  sequence  dbid  branch 
Mon Jul  :: 
Archived Log entry  added for thread  sequence  ID  dest :
Mon Jul  :: 
Media Recovery Log /oracle/app/oracle/fast_recovery_area/MAINDB/archivelog/_07_10/o1_mf_1_1574949_dp5whl86_.arc
RFS[]: Selected log  for thread  sequence  dbid  branch 
Mon Jul  :: 
Archived Log entry  added for thread  sequence  ID  dest :

重新檢視gap，如果還有繼續按上面的步驟修複，直到dg broker提示叢集正常：

DGMGRL> show configuration

Configuration - sgsdgmb

  Protection Mode: MaxAvailability
  Databases:
    maindb - Primary database
    stbydb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

因修改SYS密碼引起的gap問題修複

因修改SYS密碼引起的gap問題修複

繼續閱讀

Testlink安裝部署之XAMPP

set define off關閉替代變量功能

報錯：'mysql' 不是内部或外部指令，也不是可運作的程式或批處理檔案。

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

登入plsql 報錯 the account is locked --使用者被鎖

SequoiaDB巨杉資料庫C++驅動概述