转 Alert.log shows No Standby Redo Logfiles Of Size 153600 Blocks Available
http://blog.itpub.net/23135684/viewspace-703620/
| Alert.log shows No Standby Redo Logfiles Of Size 153600 Blocks Available [ID 405836.1] | |||||
| 修改时间 05-APR-2011 类型 PROBLEM 状态 PUBLISHED | |||||
In this Document
Symptoms
Cause
Solution
Applies to:
Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 10.2.0.5 - Release: 10.1 to 10.2
Information in this document applies to any platform.
***Checked for relevance on 05-APR-2011***
Symptoms
After real time apply works within 24hrs
the following error occurs.
Rfs[2]: no standby redo logfiles of size 153600 blocks available
Checking the standby logs on standby database, all SRLs are ACTIVE dated from weeks ago- normally we see 1 used for 1 thread and the others will be UNASSIGNED
GROUP# DBID THREAD# SEQUENCE# BYTES USED ARC STATUS FIRST_CHANGE# FIRST_TIME LAST_CHANGE# LAST_TIME
---------- ---------------------------------------- ---------- ---------- ---------- ---------- --- ---------- ------------- -------------------- ------------ --------------------
7 2924799764 1 233641 157286400 148177920 NO ACTIVE 8.2227E+12 01-Dec-2006 21:36:20 8.2227E+12 01-Dec-2006 21:54:04
8 2924799764 1 233637 157286400 148179968 NO ACTIVE 8.2227E+12 01-Dec-2006 20:20:21 8.2227E+12 01-Dec-2006 20:39:22
9 2924799764 1 233635 157286400 148292608 NO ACTIVE 8.2227E+12 01-Dec-2006 19:43:15 8.2227E+12 01-Dec-2006 20:00:54
10 2924799764 1 233627 157286400 12578816 NO ACTIVE 8.2227E+12 01-Dec-2006 18:00:25 8.2227E+12 01-Dec-2006 18:01:31
11 2924799764 1 233633 157286400 148156416 NO ACTIVE 8.2227E+12 01-Dec-2006 19:06:06 8.2227E+12 01-Dec-2006 19:22:41
12 2924799764 1 233639 157286400 148199424 NO ACTIVE 8.2227E+12 01-Dec-2006 20:57:35 8.2227E+12 01-Dec-2006 21:16:32
13 2924799764 1 233629 157286400 148305920 NO ACTIVE 8.2227E+12 01-Dec-2006 18:01:41 8.2227E+12 01-Dec-2006 18:20:14
14 2924799764 1 233631 157286400 148451328 NO ACTIVE 8.2227E+12 01-Dec-2006 18:32:17 8.2227E+12 01-Dec-2006 18:48:59
15 2924799764 1 234647 157286400 3922944 YES ACTIVE 8.2228E+12 20-Dec-2006 16:37:31 8.2228E+12 20-Dec-2006 16:37:57
Cause
In Dec1 SRLs created on LG were not archived/stuck and hence remained ACTIVE and could not longer be assigned. At the that time we see that Primary was archiving every minute and only 1 ARCH available to archive to standby. From standby log_archive_max_processes was set to 2, hence only 1 ARCH archiving Locally and most likely unable to cope with the amount of archiving required.
Solution
1. On Standby/Primary set log_archive_max_processes=10
OR
2. Another way around this if the logs have been applied as they have in this case, the old standby logs
can be dropped and recreated to clear the problem.
then
alter database add standby logfile group x '<logfile_name>';
<logfile_name><logfile_name>
#########################################################
报错 ：
Sun Oct 21 04:39:06 CST 2018
FAL[client]: Failed to request gap sequence
GAP - thread 2 sequence 101986-101989
解决方法1:
ALTER DATABASE REGISTER LOGFILE '/tmp/dba/1/o1_mf_2_101986_fwps5fbo_.arc';
ALTER DATABASE REGISTER LOGFILE '/tmp/dba/1/o1_mf_2_101988_fwps7ntp_.arc';
ALTER DATABASE REGISTER LOGFILE '/tmp/dba/1/o1_mf_2_101987_fwps7o6j_.arc';
ALTER DATABASE REGISTER LOGFILE '/tmp/dba/1/o1_mf_2_101989_fwpsg14g_.arc';
ALTER DATABASE REGISTER LOGFILE '/tmp/dba/1/o1_mf_2_102004_fwq5wq6m_.arc';
ALTER DATABASE REGISTER LOGFILE '/tmp/dba/1/o1_mf_2_102005_fwq5wx6s_.arc';
解决方法2：
Cause 3 : ARC process on the primary that is responsible for the heartbeat sticks on the network due to bug 6113783
You could see different symptoms. For example, you get fal archive error as the primary arch process sticks and it doesn't ship the missing archive log files to the standby. The worst case you see is all arch processes stick and no one does local archiving and all online redo log files are full and that causes the primary database hangs. Please see the following errors related to this cause.
Mon Oct 22 13:30:44 CST 2018 <- RFS fetch gap begin failed
FAL[client]: Failed to request gap sequence
GAP - thread 1 sequence 100641-100641
DBID 2583306402 branch 777418146
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 is sufficiently large
enough to maintain adequate log switch information to resolve
archivelog gaps.
-------------------------------------------------------------
Mon Oct 22 13:32:44 CST 2018 <- find the reason is network disconnet
RFS[342]: Possible network disconnect with primary database
Mon Oct 22 13:32:46 CST 2018
RFS[341]: Possible network disconnect with primary database
Mon Oct 22 13:52:16 CST 2018 <- after 30 minutes ,RFS retry .
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[350]: Assigned to RFS process 23920798
FAL[client]: Failed to request gap sequence
GAP - thread 1 sequence 2262-2266
DBID 2591313681 branch 686760021
FAL[client]: All defined FAL servers have been attempted.
SQL> alter session set nls_date_format='YYYY-MON-DD HH24:MI:SS';
SQL> select message, timestamp
from v$dataguard_status
where severity in ('Error','Fatal')
order by timestamp;
MESSAGE
-------------------------------------------------------------------------------
TIMESTAMP
--------------------------
Error 12154 received logging on to the standby
OCT-12-2010 15:55:45
PING[ARC1]: Heartbeat failed to connect to standby 'STDBY'. Error is 12154.
OCT-12-2010 15:55:45
Solution 3: The bug 6113783 was fixed in 11.2.0.2. You could workaround the issue by killing the arch processes on the primary database.
in primary :
This won't harm your primary database at all as arch processes will be respawned automatically immediately by Oracle.
% ps -ef |grep -i arc
% kill -9 <ospid of arc process> <ospid of arc process> <ospid of arc process>...
% ps -ef |grep -i arc
(new arch process will respawned quickly,monitor)
NOTE: If you are on Windows Platform you can use either 'orakill'-Command or any OS-Tool to kill Windows Processes/Threads
sqlplus / as sysdba
alter system switch logfile;
in standby db:
just cancle mrp and restart standby instance.
#issue 4. 搭建rac dg ,第一次同步出现这个报错PING[ARC1]: Heartbeat failed to connect to standby 'STDBY'. Error is 12154.
1按照方法3：重启了主库arc和 容灾库，依然失效。
2主库的密码文件也已经同步了容灾库。
3.
在主库通过修改一下参数进行尝试：
alter system set log_archive_dest_state_2=defer;
alter system set log_archive_dest_state_2=enable;
4.检查主库arc3。发现报错ora-12154
使用sqlplus user/pass@standby 报错 ora-12154.
5.还有一种可能： tnanames.ora 没有被archive 进程识别，让archive进程意识到tnsnames.ora的变动。（怕之前修改过，archive进程没有意识到。），还是没用。依旧报错。
规避方法：
直接将
有一个库自从上线之后，主库的alertlog中一直有如下报错：
1. 检查远端的standby库已经启动，且已经到了mount以上的状态（即在read only的模式下Real Time Apply）。
2. 检查主库到远端的tnsping，正常。
3. 检查主库sqlplus user/pwd@tns 也是正常连接。
4. defer log_archive_dest_state_2后再enable，让archive进程意识到tnsnames.ora的变动。（怕之前修改过，archive进程没有意识到。），还是没用。依旧报错。
5. 再检查了一下，发现TNS_ADMIN的环境变量没有设置。所以用于心跳检测的archive进程，会找不到tns的配置文件。
临时解决办法：
|
1
2
3
4
5
6
|
不使用tnsnames.ora的配置文件，直接在log_archive_dest_2中设置：
alter system set log_archive_dest_2='SERVICE="(description=(address_list=(address=(protocol=TCP)(host=rhost)(port=1534)))(connect_data=(sid=mydb)))" LGWR ASYNC NOAFFIRM reopen=60 valid_for=(online_logfile,primary_role) db_unique_name=rmydb';
alter system set log_archive_dest_state_2=defer;
alter system set log_archive_dest_state_2=enable;
|
修改环境变量，添加TNS_ADMIN，待下次重启数据库生效。
参考：
Error 12154 received logging on to the standby whenever primary instance startup with srvctl (Doc ID 1943178.1)
RFS Is not coming up on standby Due to ORA-12154 on transport (primary side) (Doc ID 2196182.1)
Troubleshooting R11; Heartbeat failed to connect to standby (Doc ID 1432367.1)
如果以上方法都无法实现：尝试这个解决方法，
tnsnames.ora 指向的scan-ip，但是scan-ip 上没有服务。
alter system set remote-listener='***scan-ip***'
注册服务，即可
