The Data guard impacts by a trigger
Recently，I used the oracle trigger on my testing environment, I met an issue.
Tesing Enviroment:
OS：
[oracle@racnodea ~]$ uname -a
Linux racnodea.firecat.com 2.6.32-300.3.1.el6uek.x86_64 #1 SMP Fri Dec 9 18:57:35 EST 2011 x86_64 x86_64 x86_64 GNU/Linux
Database：
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
Primary and Standby Database are all on the same VM.
Primary DB Name: PROD
Standby DB Name:DG2
Problem:
When the standby server is under open status, the primary server cannot archive the log to the standby. The check steps as below
Reproduce the fault:
Create the trigger on primary:
SQL> create table Utb_test(program varchar(100));
Table created.
SQL> create or replace trigger logon_trig
2 after logon
3 on database
4 begin
5 insert into Utb_test values(SYS_CONTEXT('USERENV','MODULE'));
6 end;
7 /
Trigger created.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
Execute the below SQL statement on both servers to check.
SQL> select * from Utb_test;
PROGRAM
--------------------------------------------------------------------------------
oracle@racnodea.firecat.com (TNS V1-V3)
oracle@racnodea.firecat.com (TNS V1-V3)
oracle@racnodea.firecat.com (TNS V1-V3)
Restart the primary server
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 313159680 bytes
Fixed Size 2212936 bytes
Variable Size 188746680 bytes
Database Buffers 117440512 bytes
Redo Buffers 4759552 bytes
Database mounted.
Database opened.
Create a table& insert the sample data
SQL> create table Utb_jason_one (x char(5));
Table created.
SQL> insert into Utb_jason_one values('1');
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
The primary server
SQL> SELECT SEQUENCE# FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE#
----------
170
171
171
172
172
173
174
175
176
177
178
SQL> SELECT MESSAGE FROM V$DATAGUARD_STATUS;
MESSAGE
--------------------------------------------------------------------------------
ARC0: Archival started
ARC1: Archival started
ARC2: Archival started
ARC2: Becoming the 'no FAL' ARCH
ARC2: Becoming the 'no SRL' ARCH
ARC1: Becoming the heartbeat ARCH
ARC2: Beginning to archive thread 1 sequence 175 (508986-509192)
ARC2: Completed archiving thread 1 sequence 175 (508986-509192)
Error 604 received logging on to the standby
PING[ARC1]: Heartbeat failed to connect to standby 'DG2'. Error is 604.
ARC3: Archival started
MESSAGE
--------------------------------------------------------------------------------
Error 604 received logging on to the standby
FAL[server, ARC1]: Error 604 creating remote archivelog file 'DG2'
ARC4: Archival started
ARC2: Beginning to archive thread 1 sequence 176 (509192-509280)
ARC2: Completed archiving thread 1 sequence 176 (509192-509280)
Error 604 received logging on to the standby
FAL[server, ARC3]: Error 604 creating remote archivelog file 'DG2'
ARCH shutting down
ARC4: Archival stopped
Error 604 received logging on to the standby
PING[ARC1]: Heartbeat failed to connect to standby 'DG2'. Error is 604.
MESSAGE
--------------------------------------------------------------------------------
Error 604 received logging on to the standby
PING[ARC1]: Heartbeat failed to connect to standby 'DG2'. Error is 604.
Error 604 received logging on to the standby
PING[ARC1]: Heartbeat failed to connect to standby 'DG2'. Error is 604.
ARC0: Beginning to archive thread 1 sequence 177 (509280-509495)
ARC0: Completed archiving thread 1 sequence 177 (509280-509495)
Error 604 received logging on to the standby
FAL[server, ARC3]: Error 604 creating remote archivelog file 'DG2'
ARC0: Beginning to archive thread 1 sequence 178 (509495-509501)
ARC0: Completed archiving thread 1 sequence 178 (509495-509501)
Error 604 received logging on to the standby
MESSAGE
--------------------------------------------------------------------------------
FAL[server, ARC3]: Error 604 creating remote archivelog file 'DG2'
Error 604 received logging on to the standby
PING[ARC1]: Heartbeat failed to connect to standby 'DG2'. Error is 604.
Error 604 received logging on to the standby
PING[ARC1]: Heartbeat failed to connect to standby 'DG2'. Error is 604.
Error 604 received logging on to the standby
PING[ARC1]: Heartbeat failed to connect to standby 'DG2'. Error is 604.
Error 604 received logging on to the standby
PING[ARC1]: Heartbeat failed to connect to standby 'DG2'. Error is 604.
Error 604 received logging on to the standby
PING[ARC1]: Heartbeat failed to connect to standby 'DG2'. Error is 604.
MESSAGE
--------------------------------------------------------------------------------
Error 604 received logging on to the standby
PING[ARC1]: Heartbeat failed to connect to standby 'DG2'. Error is 604.
46 rows selected.
The standby server
SQL> SELECT SEQUENCE# FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE#
----------
168
169
170
171
172
SQL> SELECT MESSAGE FROM V$DATAGUARD_STATUS;
MESSAGE
--------------------------------------------------------------------------------
ARC0: Archival started
ARC1: Archival started
ARC2: Archival started
ARC2: Becoming the 'no FAL' ARCH
ARC1: Becoming the heartbeat ARCH
ARC3: Archival started
RFS[1]: Assigned to RFS process 2195
RFS[1]: Identified database type as 'physical standby': Client is ARCH pid 1895
Attempt to start background Managed Standby Recovery process
MRP0: Background Managed Standby Recovery process started
Managed Standby Recovery starting Real Time Apply
MESSAGE
--------------------------------------------------------------------------------
Media Recovery Log /s01/app/oracle/flash_recovery_area/DG2/archivelog/2012_10_20
/o1_mf_1_168_885g2gpm_.arc
Media Recovery Log /s01/app/oracle/flash_recovery_area/DG2/archivelog/2012_10_20
/o1_mf_1_169_885g2foc_.arc
Media Recovery Log /s01/app/oracle/flash_recovery_area/DG2/archivelog/2012_10_20
/o1_mf_1_170_885g2fpx_.arc
Media Recovery Waiting for thread 1 sequence 171
RFS[2]: Assigned to RFS process 2207
MESSAGE
--------------------------------------------------------------------------------
RFS[2]: Identified database type as 'physical standby': Client is ARCH pid 1895
RFS[3]: Assigned to RFS process 2210
RFS[3]: Identified database type as 'physical standby': Client is ARCH pid 1895
RFS[4]: Assigned to RFS process 2213
RFS[4]: Identified database type as 'physical standby': Client is LGWR ASYNC pid
1899
Primary database is in MAXIMUM PERFORMANCE mode
ARC3: Beginning to archive thread 1 sequence 171 (506309-508708)
RFS[5]: Assigned to RFS process 2215
RFS[5]: Identified database type as 'physical standby': Client is LGWR ASYNC pid
MESSAGE
--------------------------------------------------------------------------------
1899
ARC3: Completed archiving thread 1 sequence 171 (0-0)
Primary database is in MAXIMUM PERFORMANCE mode
Media Recovery Log /s01/app/oracle/flash_recovery_area/DG2/archivelog/2012_10_20
/o1_mf_1_171_885g93hw_.arc
ARC0: Beginning to archive thread 1 sequence 172 (508708-508713)
ARC0: Completed archiving thread 1 sequence 172 (0-0)
Media Recovery Log /s01/app/oracle/flash_recovery_area/DG2/archivelog/2012_10_20
/o1_mf_1_172_885g9505_.arc
MESSAGE
--------------------------------------------------------------------------------
Media Recovery Waiting for thread 1 sequence 173
32 rows selected.
Check the alert log
[oracle@racnodea ~]$ adrci
ADRCI: Release 11.2.0.1.0 - Production on Sat Oct 20 23:39:09 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
ADR base = "/s01/app/oracle"
adrci> show alert
Choose the alert log from the following homes to view:
1: diag/tnslsnr/racnodea/listener3
2: diag/tnslsnr/racnodea/listener2
3: diag/tnslsnr/racnodea/listener.ora
4: diag/tnslsnr/racnodea/listener
5: diag/tnslsnr/racnodea/dg2
6: diag/rdbms/prod/PROD
7: diag/rdbms/dg2/DG2
Q: to quit
Please select option:
Key in the number 6, I got the errors
Thread 1 advanced to log sequence 179 (LGWR switch)
Current log# 2 seq# 179 mem# 0: /s01/app/oradata/redolog2
Archived Log entry 222 added for thread 1 sequence 178 ID 0xc577aca dest 1:
Error 604 received logging on to the standby
Errors in file /s01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_arc3_2340.trc:
ORA-00604: error occurred at recursive SQL level
FAL[server, ARC3]: Error 604 creating remote archivelog file 'DG2'
FAL[server, ARC3]: FAL archive failed, see trace file.
Errors in file /s01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_arc3_2340.trc:
ORA-16055: FAL request rejected
ARCH: FAL archive failed. Archiver continuing
ORACLE Instance PROD - Archival Error. Archiver continuing.
2012-10-20 22:56:54.528000 +08:00
Error 604 received logging on to the standby
Errors in file /s01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_arc1_2336.trc:
ORA-00604: error occurred at recursive SQL level
PING[ARC1]: Heartbeat failed to connect to standby 'DG2'. Error is 604.
2012-10-20 22:57:54.873000 +08:00
After Shutdown & remount standby, switch the logfile on primary.
Execute the SQL statement on both server
SQL>SELECT SEQUENCE# FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
On Primary
SEQUENCE#
----------
173
173
174
174
175
175
176
176
177
177
178
178
179
179
180
180
SQL>SELECT MESSAGE FROM V$DATAGUARD_STATUS;
MESSAGE
--------------------------------------------------------------------------------
ARC2: Completed archiving thread 1 sequence 179 (509501-510251)
LNS: Standby redo logfile selected for thread 1 sequence 179 for destination LOG
_ARCHIVE_DEST_2
LNS: Beginning to archive log 2 thread 1 sequence 179
LNS: Completed archiving log 2 thread 1 sequence 179
LNS: Standby redo logfile selected for thread 1 sequence 180 for destination LOG
_ARCHIVE_DEST_2
LNS: Beginning to archive log 3 thread 1 sequence 180
LNS: Completed archiving log 3 thread 1 sequence 180
ARC3: Beginning to archive thread 1 sequence 180 (510251-510255)
ARC3: Completed archiving thread 1 sequence 180 (510251-510255)
LNS: Standby redo logfile selected for thread 1 sequence 181 for destination LOG
_ARCHIVE_DEST_2
LNS: Beginning to archive log 1 thread 1 sequence 181
On Standby
SEQUENCE#
----------
168
169
170
171
172
173
174
175
176
177
178
179
180
[oracle@racnodea ~]$ adrci
ADRCI: Release 11.2.0.1.0 - Production on Sat Oct 20 23:39:09 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
ADR base = "/s01/app/oracle"
adrci> show alert
Choose the alert log from the following homes to view:
1: diag/tnslsnr/racnodea/listener3
2: diag/tnslsnr/racnodea/listener2
3: diag/tnslsnr/racnodea/listener.ora
4: diag/tnslsnr/racnodea/listener
5: diag/tnslsnr/racnodea/dg2
6: diag/rdbms/prod/PROD
7: diag/rdbms/dg2/DG2
Q: to quit
Please select option: 6
Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=MEMORY SID='*';
2012-10-20 23:07:50.046000 +08:00
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
Thread 1 advanced to log sequence 180 (LGWR switch)
Current log# 3 seq# 180 mem# 0: /s01/app/oradata/redolog3
Archived Log entry 229 added for thread 1 sequence 179 ID 0xc577aca dest 1:
LNS: Standby redo logfile selected for thread 1 sequence 179 for destination LOG_ARCHIVE_DEST_2
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
LNS: Standby redo logfile selected for thread 1 sequence 180 for destination LOG_ARCHIVE_DEST_2
2012-10-20 23:07:51.120000 +08:00
Thread 1 advanced to log sequence 181 (LGWR switch)
Current log# 1 seq# 181 mem# 0: /s01/app/oradata/redolog1
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
Archived Log entry 232 added for thread 1 sequence 180 ID 0xc577aca dest 1:
LNS: Standby redo logfile selected for thread 1 sequence 181 for destination LOG_ARCHIVE_DEST_2
If we create a trigger of logon database on primary, we will involve in such situation:
The primary server may not transfer log files to standby unless the standby is under mount status.
