DATAGUARD STANDBY Refresh Süresi

          Dataguard kurulan sistemde primary database'de çok fazla archive dosyası üretilmiyor ise bunun standby database'e gönderilmeside o kadar seyrek olacaktir. Standby database'de controlfile time kontrolu ile yapılacak olan güncellik kontrolleri surekli gecikme var diye uyarı verecektir.
          Bunu aşmak için
      Primary database ile standby database' arasında sql ile kontrol yapılabilir.
      Primary database de cron'a alter system switch logfile ile archive dosyası üretilmesi sağlanabilir
      En  kolayı  primary database'de  ile 1800 saniyede bir logların switch edilmesi sağlanır.

alter system set archive_lag_target=1800 scope=BOTH;

DATAGUARD ORA-08181: specified number is not a valid system change number

Standby database eğer geriden geliyor ise aşağıdaki hata alınabilir.

Hatanın giderilmesi için dataguard beslemesinin restart edilmesi gerekmektedir.


HATA

SQL> BEGIN
  2    DBMAIN.DB_CHECK_STANDBY;
  3    COMMIT;
  4  END;
  5  /
BEGIN
*
ERROR at line 1:
ORA-20081: DBMAIN.DB_CHECK_STANDBY  de hata olustu -20061 ORA-20061:
dbmain.DB_CHECK_STANDBY send_message_sms_email  hata olustu !! -600 ORA-00600:
internal error code, arguments: [psdnopGetACL-4], [8181], [ORA-08181: specified
number is not a valid system change number
ORA-06512: at "SYS.DBMS_NETWORK_ACL_ADMIN", line 651
], [], [], [], [], [], [], [], [], []
ORA-06512: at "DBMAIN.DB_CHECK_STANDBY", line 350
ORA-06512: at line 2

Alertlog'da

Errors in file /oracle/diag/rdbms/drated/DRATED/trace/DRATED_ora_26427.trc  (incident=69786):
ORA-00600: internal error code, arguments: [psdnopGetACL-4], [8181], [ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.DBMS_NETWORK_ACL_ADMIN", line 651
], [], [], [], [], [], [], [], [], []
Incident details in: /oracle/diag/rdbms/drated/DRATED/incident/incdir_69786/DRATED_ora_26427_i69786.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Wed Sep 03 15:30:03 2014
Dumping diagnostic data in directory=[cdmp_20140903153003], requested by (instance=1, osid=26427), summary=[incident=69786].
Wed Sep 03 15:30:05 2014
Sweep [inc][69786]: completed
Sweep [inc2][69786]: completed,


 oracle@pratestb:/oracle/diag/rdbms/drated/DRATED/trace>more /oracle/diag/rdbms/drated/DRATED/trace/DRATED_ora_24197.trc
Trace file /oracle/diag/rdbms/drated/DRATED/trace/DRATED_ora_24197.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = /oracle/product/11.2.0.3/db
System name:    HP-UX
Node name:      pratestb
Release:        B.11.31
Version:        U
Machine:        ia64
Instance name: DRATED
Redo thread mounted by this instance: 1
Oracle process number: 57
Unix process pid: 24197, image: oracle@pratestb


*** 2014-09-03 15:49:37.035
*** SESSION ID:(293.63) 2014-09-03 15:49:37.035
*** CLIENT ID:() 2014-09-03 15:49:37.035
*** SERVICE NAME:() 2014-09-03 15:49:37.035
*** MODULE NAME:(Toad.exe) 2014-09-03 15:49:37.035
*** ACTION NAME:() 2014-09-03 15:49:37.035

in internal exception handler, error2: 8181
resignalling error
in outer exception handler, error: 8181
resignalling error
DDE: Problem Key 'ORA 600 [psdnopGetACL-4]' was flood controlled (0x2) (incident: 70057)
ORA-00600: internal error code, arguments: [psdnopGetACL-4], [8181], [ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.DBMS_NETWORK_ACL_ADMIN", line 651
], [], [], [], [], [], [], [], [], []

*** 2014-09-03 15:50:06.714
in internal exception handler, error2: 8181
resignalling error
in outer exception handler, error: 8181
resignalling error
DDE: Problem Key 'ORA 600 [psdnopGetACL-4]' was flood controlled (0x2) (incident: 70058)
ORA-00600: internal error code, arguments: [psdnopGetACL-4], [8181], [ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.DBMS_NETWORK_ACL_ADMIN", line 651
], [], [], [], [], [], [], [], [], []
 oracle@pratestb:/oracle/diag/rdbms/drated/DRATED/trace>



Çözüm

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

SQL> alter session set nls_date_format='dd.mm.yyyy hh24:mi:ss';

Session altered.

SQL> select controlfile_time from v$database;

CONTROLFILE_TIME
-------------------
03.09.2014 13:43:02


Sonra archive dosyalarıda gelsin diye primary database'de

alter system switch logfile yapin :)

Dataguard'dan mail Atma

Mail atma işleminde hata alınırsa

begin
SYS.UTL_MAIL.SEND (
      'ali.ates@xxxx.com.tr',
      'ali.ates@xxxx.com.tr',
      nvl(null,''),
      nvl(null,''),
      'TEST',
      'test_mesaj',
     -- 'text/html; charset=us-ascii',
   --  'text/plain; charset=us-ascii',
      'html',
      1
   );
end;



Error at line 2
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access
ORA-06512: at line 14
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.UTL_MAIL", line 654
ORA-06512: at "SYS.UTL_MAIL", line 671
ORA-06512: at line 2

Script Terminated on line 19.

Çözüm


alter system set smtp_out_server='10.112.2.26:25' scope=both;

begin
SYS.UTL_MAIL.SEND (
      'ali.ates@XXXX.com.tr',
      'ali.ates@XXXX.com.tr',
      nvl(null,''),
      nvl(null,''),
      'TEST DRATED',
      'test_mesaj',
     -- 'text/html; charset=us-ascii',
   --  'text/plain; charset=us-ascii',
      'html',
      1
   );
end;