水木刀-梁 发表于 2014-4-2 10:23:21

data guard PING[ARC2]: Heartbeat failed to connect . Error i...

主库/etc/hosts# cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1       odd   localhost.localdomain localhost
::1             localhost6.localdomain6 localhost6
192.168.146.202 even
192.168.146.201 odd
192.168.146.203 standby

备库/etc/hosts：
# cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
#127.0.0.1      standby         localhost.localdomain localhost
#::1            localhost6.localdomain6 localhost6
192.168.146.202 even
192.168.146.201 odd
192.168.146.203 standby



主库 listener.ora
$ cat listener.ora
LISTENER=
(DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=odd)(PORT=1521))
      (ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))
SID_LIST_LISTENER=
(SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME=PROD)
      (ORACLE_HOME=/opt/app/oracle/product/10.2.0/db_1)
      (SID_NAME=PROD))
    (SID_DESC=
      (GLOBAL_DBNAME=EMREP)
      (ORACLE_HOME=/opt/app/oracle/product/10.2.0/db_1)
      (SID_NAME=EMREP))
    (SID_DESC=
      (SID_NAME=plsextproc)
      (ORACLE_HOME=/opt/app/oracle/product/10.2.0/db_1)
      (PROGRAM=extproc)))

LSNR2=
(DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=odd)(PORT=1526))
      (ADDRESS=(PROTOCOL=ipc)(KEY=extproc1))))

主库 上的tnsname.ora
$ cat tnsnames.ora
PROD=
(DESCRIPTION=
   (ADDRESS_LIST=
   (ADDRESS=(PROTOCOL=tcp)(HOST=odd)(PORT=1521))
    )
(CONNECT_DATA=
   (SERVICE_NAME=PROD)
   (SERVER=dedicated)
)
)

prod_s=
(DESCRIPTION=
   (ADDRESS_LIST=
   (ADDRESS=(PROTOCOL=tcp)(HOST=odd)(PORT=1526))
    )
(CONNECT_DATA=
   (SERVICE_NAME=PROD)
   (SERVER=share)
)
)


racdb=
(DESCRIPTION=
   (ADDRESS_LIST=
   (ADDRESS=(PROTOCOL=tcp)(HOST=odd)(PORT=1521))
    )
(CONNECT_DATA=
   (SERVICE_NAME=RACDB)
   (SERVER=dedicated)
)
)



emrep=
(DESCRIPTION=
   (ADDRESS_LIST=
   (ADDRESS=(PROTOCOL=tcp)(HOST=even)(PORT=1521))
    )
(CONNECT_DATA=
   (SERVICE_NAME=EMREP)
   (SERVER=dedicated)
)
)

sbdb=
(DESCRIPTION=
   (ADDRESS_LIST=
   (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.146.203)(PORT=1521))
    )
(CONNECT_DATA=
   (SERVICE_NAME=SBDB)
   (SERVER=dedicated)
)
)


备库的listener.ora
$ cat listener.ora
LISTENER=
(DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=standby)(PORT=1521))
      (ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))
SID_LIST_LISTENER=
(SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME=PROD)
      (ORACLE_HOME=/opt/app/oracle/product/10.2.0/db_1)
      (SID_NAME=PROD))
    (SID_DESC=
      (GLOBAL_DBNAME=SBDB)
      (ORACLE_HOME=/opt/app/oracle/product/10.2.0/db_1)
      (SID_NAME=SBDB))
    (SID_DESC=
      (SID_NAME=plsextproc)
      (ORACLE_HOME=/opt/app/oracle/product/10.2.0/db_1)
      (PROGRAM=extproc)))

LSNR2=
(DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=standby)(PORT=1526))
      (ADDRESS=(PROTOCOL=ipc)(KEY=extproc1))))


备库上的tnsname.ora
$ cat tnsnames.ora
PROD=
(DESCRIPTION=
   (ADDRESS_LIST=
   (ADDRESS=(PROTOCOL=tcp)(HOST=odd)(PORT=1521))
    )
(CONNECT_DATA=
   (SERVICE_NAME=PROD)
   (SERVER=dedicated)
)
)

prod_s=
(DESCRIPTION=
   (ADDRESS_LIST=
   (ADDRESS=(PROTOCOL=tcp)(HOST=odd)(PORT=1526))
    )
(CONNECT_DATA=
   (SERVICE_NAME=PROD)
   (SERVER=share)
)
)


racdb=
(DESCRIPTION=
   (ADDRESS_LIST=
   (ADDRESS=(PROTOCOL=tcp)(HOST=odd)(PORT=1521))
    )
(CONNECT_DATA=
   (SERVICE_NAME=RACDB)
   (SERVER=dedicated)
)
)



emrep=
(DESCRIPTION=
   (ADDRESS_LIST=
   (ADDRESS=(PROTOCOL=tcp)(HOST=even)(PORT=1521))
    )
(CONNECT_DATA=
   (SERVICE_NAME=EMREP)
   (SERVER=dedicated)
)
)

sbdb=
(DESCRIPTION=
   (ADDRESS_LIST=
   (ADDRESS=(PROTOCOL=tcp)(HOST=standby)(PORT=1521))
    )
(CONNECT_DATA=
   (SERVICE_NAME=SBDB)
   (SERVER=dedicated)
)
)


主库上的参数
$ cat /home/oracle/pfileprod.ora
PROD.__db_cache_size=440401920
PROD.__java_pool_size=4194304
PROD.__large_pool_size=4194304
PROD.__shared_pool_size=176160768
PROD.__streams_pool_size=0
*.CONTROL_FILES='/opt/app/oracle/oradata/PROD/disk1/control01.ctl','/opt/app/oracle/oradata/PROD/disk2/control02.ctl','/opt/app/oracle/oradata/PROD/disk3/control03.ctl'
*.DB_BLOCK_SIZE=8192
*.db_file_name_convert='/opt/app/oracle/oradata/SBDB/disk1','/opt/app/oracle/oradata/PROD/disk1'
*.DB_NAME='PROD'
*.db_unique_name='prod'
*.fal_client='PROD'
*.fal_server='SBDB'
*.JOB_QUEUE_PROCESSES=15
*.local_listener='(ADDRESS=(PROTOCOL=tcp)(HOST=odd)(PORT=1526))'
*.log_archive_config='dg_config=(prod,sbdb)'
*.log_archive_dest_1='location=/opt/app/oracle/oradata/PROD/disk1/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=prod'
*.log_archive_dest_2='service=SBDB lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=sbdb'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_max_processes=10
*.log_file_name_convert='/opt/app/oracle/oradata/SBDB/disk1','/opt/app/oracle/oradata/PROD/disk1'
*.PGA_AGGREGATE_TARGET=300M
*.PROCESSES=200
*.SGA_MAX_SIZE=600M
*.SGA_TARGET=600M
*.standby_file_management='AUTO'
*.UNDO_MANAGEMENT='auto'


备库上的参数
$ cat /home/oracle/pfilesbdb.ora
SBDB.__db_cache_size=440401920
sbdb.__db_cache_size=452984832
SBDB.__java_pool_size=4194304
sbdb.__java_pool_size=4194304
SBDB.__large_pool_size=4194304
sbdb.__large_pool_size=4194304
SBDB.__shared_pool_size=176160768
sbdb.__shared_pool_size=163577856
SBDB.__streams_pool_size=0
sbdb.__streams_pool_size=0
*.background_dump_dest='/opt/app/oracle/admin/SBDB/bdump'
*.CONTROL_FILES='/opt/app/oracle/oradata/SBDB/disk1/control01.ctl'
*.core_dump_dest='/opt/app/oracle/admin/SBDB/cdump'
*.DB_BLOCK_SIZE=8192
*.db_file_name_convert='/opt/app/oracle/oradata/PROD/disk1','/opt/app/oracle/oradata/SBDB/disk1'
*.DB_NAME='PROD'
*.db_unique_name='SBDB'
*.fal_client='SBDB'
*.fal_server='prod'
*.JOB_QUEUE_PROCESSES=15
*.local_listener='(ADDRESS=(PROTOCOL=tcp)(HOST=standby)(PORT=1526))'
*.log_archive_config='dg_config=(prod,SBDB)'
*.log_archive_dest_1='location=/opt/app/oracle/oradata/SBDB/disk1/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=SBDB'
*.log_archive_dest_2='service=prod lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=prod'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.log_archive_max_processes=10
*.log_file_name_convert='/opt/app/oracle/oradata/PROD/disk1','/opt/app/oracle/oradata/SBDB/disk1'
*.PGA_AGGREGATE_TARGET=300M
*.PROCESSES=200
*.SGA_MAX_SIZE=600M
*.SGA_TARGET=600M
*.standby_file_management='auto'
*.UNDO_MANAGEMENT='auto'
*.user_dump_dest='/opt/app/oracle/admin/SBDB/udump'


问题：在备库在日志应用状态时：
alter database recover managed standby database disconnect from session;

主库是日志无法传到备库。
主库上的后台日志一直报
Errors in file /opt/app/oracle/admin/PROD/bdump/prod_arc2_16283.trc:
ORA-01034: ORACLE not available
PING: Heartbeat failed to connect to standby 'SBDB'. Error is 1034.

之前检查了两台的hosts文件，tnsping，及相互登录。
SQL> show parameter remote

NAME                                 TYPE      VALUE
------------------------------------ ----------- ------------------------------
remote_archive_enable                string      true
remote_dependencies_mode             string      TIMESTAMP
remote_listener                      string
remote_login_passwordfile            string      EXCLUSIVE
remote_os_authent                  boolean   FALSE
remote_os_roles                      boolean   FALSE

请大家帮忙看下这个错误是怎么回事呢？

ziyoo0830 发表于 2014-4-2 10:31:28

确认备库启动？

ziyoo0830 发表于 2014-4-2 10:41:52

sqlplus sys/password@prod as sysdba
sqlplus sys/password@SBDB as sysdba

都可以登录吗？

ziyoo0830 发表于 2014-4-2 10:42:41

还有监听，怎么有的是1526 有的是1521 ？ 实际用的是哪个？

水木刀-梁 发表于 2014-4-2 10:51:27

ziyoo0830 发表于 2014-4-2 10:41
sqlplus sys/password@prod as sysdba
sqlplus sys/password@SBDB as sysdba



这个试过了是可以登陆的。

水木刀-梁 发表于 2014-4-2 10:52:03

ziyoo0830 发表于 2014-4-2 10:42
还有监听，怎么有的是1526 有的是1521 ？ 实际用的是哪个？

动态跟静态监听都配置的

travel.liu 发表于 2014-4-2 11:34:25

select mode from v$instance;

xifenfei 发表于 2014-4-2 16:17:50

在主库上查询
select DEST_ID,dest_name,ERROR,status from v$archive_dest_status;
页: [1]
查看完整版本: data guard PING[ARC2]: Heartbeat failed to connect . Error i...