Trigger引起的active dataguard 報error ORA-16191

最近在用duplicate target做備庫時,在應用日誌時報如下錯誤,

FAL[server, ARC3]: FALarchive failed, see trace file.

ARCH: FAL archive failed.Archiver continuing

ORACLE Instance kgdb -Archival Error. Archiver continuing.

Thu Jul 16 11:12:26 2018

Thread 1 advanced to logsequence 5754 (LGWR switch)

  Current log# 2 seq# 5754 mem# 0:/u01/11g/oradata/erpdb/redo02.log

Thu Jul 16 11:12:26 2018

Archived Log entry 9957 addedfor thread 1 sequence 5753 ID 0xefbf45c1 dest 1:

Thu Jul 16 11:12:27 2018

Error 1017 received loggingon to the standby

------------------------------------------------------------

Check that the primary andstandby are using a password file

and remote_login_passwordfileis set to SHARED or EXCLUSIVE,

and that the SYS password issame in the password files.

      returning error ORA-16191

1)根據報錯檢視remote_login_passwordfile引數，此引數使用的是預設值，不會引起此問題的產生。

歸檔程式報如下：

Waiting for NSA2 [pid 2333]to initialize itself

krsu_start_ns: time appearsto have jumped forwards

Initial buffer sizes: read1024K, overflow 832K, change 805K

 

*** 2018-07-11 09:25:35.313

Log read is SYNCHRONOUSthough disk_asynch_io is enabled!

Log read is SYNCHRONOUSthough disk_asynch_io is enabled!

 

*** 2018-07-14 06:28:58.861

Log read is SYNCHRONOUSthough disk_asynch_io is enabled!

 無異常報錯。

2)重新重建備庫的密碼檔案：

orapwd file=orapwerpdbpassword=sys ignorecase=y force=y

重新duplicate時同樣產生如上錯誤。

3)重新檢視主庫的警告資訊

 只發現如下一個錯誤

errorsin file /u01/11g/diag/rdbms/erpdb/erpdb/trace/kgdb_ora_32350.trc:

ORA-00604:error occurred at recursive SQL level 1

ORA-01422:exact fetch returns more than requested number of rows。

 

直覺和trigger有關，沒有辦法檢視所有資料庫的trigger並與他們dba確認，發現有一個trigger嫌疑比較大：

TRIGGER SYS.trg_audit_log

AFTER LOGON ON DATABASE

declare

 v_program_name varchar2(200);

 v_username varchar2(100);

 v_ip_address varchar2(18);

 begin

selectusername,program,SYS_CONTEXT('USERENV','IP_ADDRESS')

intov_username,v_program_name,v_ip_address

from v$session where AUDSID =SYS_CONTEXT('USERENV', 'SESSIONID');

);

if  v_program_name = 'plsqldev.exe'  and upper(v_username) in ('')

     then

       RAISE_APPLICATION_ERROR(-20003,'You arenot allowed to connect to the dat

abase');

 

 end if;

根據錯誤資訊，根據經驗把triggerdisable

alter  tiggers sys.TRG_A_LOG DISABLE ;

重新執行duplicate，應用日誌成功。

 查相關文件，唯一文件如下：

ORA-16191 ORA-01017 and ORA-16000 shipping the logs to Standby database (文件 ID 2129339.1)

 文件解決方案

 

1.Disable the trigger.

2.Set the below parameter in the Standby database and restart:

   _system_trig_enabled=false

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/10201716/viewspace-2709151/，如需轉載，請註明出處，否則將追究法律責任。