问题描述
- 求教oracle dataguard 主备库日志无法同步的问题
-
现在做data guard 测试，试了很多次，主库的日志一直 无法同步到备份库。求高手解答。
测试环境主库:：操作系统 redhat 5.8 地址 192.168.1.135 数据库版本 oracle 10.2.0
备库： 操作系统 redhat 5.8 地址 192.168.1.3 数据库版本 oracle 10.2.0主库参数文件
orcl.__db_cache_size=390070272
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__shared_pool_size=155189248
orcl.__streams_pool_size=0
*.audit_file_dest='/app/oracle/product/10.2.0/db_1/admin/orcl/adump'
*.background_dump_dest='/app/oracle/product/10.2.0/db_1/admin/orcl/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/app/oracle/product/10.2.0/db_1/oradata/orcl/control01.ctl','/app/oracle/product/10.2.0/db_1/oradata/orcl/control02.ctl','/app/oracle/product/10.2.0/db_1/oradata/orcl/control03.ctl'
*.core_dump_dest='/app/oracle/product/10.2.0/db_1/admin/orcl/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_file_name_convert='STDDB','PRIDB'
*.db_name='orcl'
*.db_unique_name='PRIDB'
*.db_recovery_file_dest='/app/oracle/product/10.2.0/db_1/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.fal_client='DB_PRIDB'
*.fal_server='DB_STDDB'
*.job_queue_processes=10
*.log_archive_config='DG_CONFIG=(PRIDB,STDDB)'
*.log_archive_dest_1='LOCATION=/app/oracle/product/10.2.0/db_1/oradata/archivelog_orcl VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRIDB'
*.log_archive_dest_2='SERVICE=DB_STDDB LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STDDB'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=5
*.log_file_name_convert='STDDB','PRIDB'
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.pga_aggregate_target=186646528
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=559939584
*.standby_archive_dest='LOCATION=/app/oracle/product/10.2.0/db_1/oradata/archivelog_orcl'
*.standby_file_management='AUTO'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/app/oracle/product/10.2.0/db_1/admin/orcl/udump'备库参数文件
orcl.__db_cache_size=390070272
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__shared_pool_size=155189248
orcl.__streams_pool_size=0
*.audit_file_dest='/app/oracle/product/10.2.0/db_1/admin/orcl/adump'
*.background_dump_dest='/app/oracle/product/10.2.0/db_1/admin/orcl/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/app/oracle/product/10.2.0/db_1/oradata/orcl/control01.ctl','/app/oracle/product/10.2.0/db_1/oradata/orcl/control02.ctl','/app/oracle/product/10.2.0/db_1/oradata/orcl/control03.ctl'
*.core_dump_dest='/app/oracle/product/10.2.0/db_1/admin/orcl/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_file_name_convert='PRIDB','STDDB'
*.db_name='orcl'
*.db_recovery_file_dest='/app/oracle/product/10.2.0/db_1/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.db_unique_name='STDDB'
*.fal_client='db_stddb'
*.fal_server='db_pridb'
*.job_queue_processes=10
*.log_archive_config='DG_CONFIG=(STDDB,PRIDB)'
*.log_archive_dest_1='LOCATION=/app/oracle/product/10.2.0/db_1/oradata/archivelog_orcl VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STDDB'
*.log_archive_dest_2='SERVICE=db_pridb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRIDB'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=5
*.log_file_name_convert='PRIDB','STDDB'
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.pga_aggregate_target=186646528
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=559939584
*.standby_archive_dest='LOCATION=/app/oracle/product/10.2.0/db_1/oradata/archivelog_orcl'
*.standby_file_management='AUTO'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/app/oracle/product/10.2.0/db_1/admin/orcl/udump'主库ntsname文件
DB_PRIDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.135)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)DB_STDDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.3)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)备库tnsnames文件
DB_PRIDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.135)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)DB_STDDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.3)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)主库alert.log文件
Error 12514 received logging on to the standby
Fri Oct 23 15:05:03 2015
Errors in file /app/oracle/product/10.2.0/db_1/admin/orcl/bdump/orcl_arc4_5182.trc:
ORA-12514: Message 12514 not found; No message file for product=RDBMS, facility=ORA
PING[ARC4]: Heartbeat failed to connect to standby 'DB_STDDB'. Error is 12514.
Fri Oct 23 15:10:03 2015
Error 12514 received logging on to the standby
Fri Oct 23 15:10:03 2015
Errors in file /app/oracle/product/10.2.0/db_1/admin/orcl/bdump/orcl_arc4_5182.trc:
ORA-12514: Message 12514 not found; No message file for product=RDBMS, facility=ORA
PING[ARC4]: Heartbeat failed to connect to standby 'DB_STDDB'. Error is 12514.
Fri Oct 23 15:15:03 2015
Error 12514 received logging on to the standby
Fri Oct 23 15:15:03 2015
Errors in file /app/oracle/product/10.2.0/db_1/admin/orcl/bdump/orcl_arc4_5182.trc:
ORA-12514: Message 12514 not found; No message file for product=RDBMS, facility=ORA
PING[ARC4]: Heartbeat failed to connect to standby 'DB_STDDB'. Error is 12514.
Fri Oct 23 15:20:03 2015
Error 12514 received logging on to the standby
Fri Oct 23 15:20:03 2015
Errors in file /app/oracle/product/10.2.0/db_1/admin/orcl/bdump/orcl_arc4_5182.trc:
ORA-12514: Message 12514 not found; No message file for product=RDBMS, facility=ORA
PING[ARC4]: Heartbeat failed to connect to standby 'DB_STDDB'. Error is 12514.
Fri Oct 23 15:25:03 2015
Error 12514 received logging on to the standby
Fri Oct 23 15:25:03 2015
Errors in file /app/oracle/product/10.2.0/db_1/admin/orcl/bdump/orcl_arc4_5182.trc:
ORA-12514: Message 12514 not found; No message file for product=RDBMS, facility=ORA
PING[ARC4]: Heartbeat failed to connect to standby 'DB_STDDB'. Error is 12514.
Fri Oct 23 15:25:40 2015
Thread 1 advanced to log sequence 6
Current log# 2 seq# 6 mem# 0: /app/oracle/product/10.2.0/db_1/oradata/orcl/redo02.log备库alert.log 文件
ARCH: STARTING ARCH PROCESSES
ARC0 started with pid=16, OS id=8947
ARC1 started with pid=17, OS id=8949
ARC2 started with pid=18, OS id=8951
ARC3 started with pid=19, OS id=8953
Fri Oct 23 14:41:41 2015
ARC0: Archival started
ARC1: Archival started
ARC2: Archival started
ARC3: Archival started
ARC4: Archival started
ARCH: STARTING ARCH PROCESSES COMPLETE
Fri Oct 23 14:41:41 2015
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
ARC1: Thread not mounted
Fri Oct 23 14:41:41 2015
ARC3: Becoming the heartbeat ARCH
ARC3: Thread not mounted
Fri Oct 23 14:41:41 2015
ARC0: Thread not mounted
ARC4 started with pid=20, OS id=8955
ARC4: Thread not mounted
Fri Oct 23 14:41:41 2015
ARC2: Thread not mounted
Fri Oct 23 14:41:41 2015
Successful mount of redo thread 1, with mount id 1421667489
Fri Oct 23 14:41:41 2015
Physical Standby Database mounted.
Completed: alter database mount standby database
Fri Oct 23 14:42:21 2015
alter database recover managed standby database disconnect from session
Fri Oct 23 14:42:21 2015
Attempt to start background Managed Standby Recovery process (orcl)
MRP0 started with pid=21, OS id=8958
Fri Oct 23 14:42:21 2015
MRP0: Background Managed Standby Recovery process started (orcl)
Managed Standby Recovery not using Real Time Apply
Media Recovery Waiting for thread 1 sequence 3
Fri Oct 23 14:42:27 2015
Completed: alter database recover managed standby database disconnect from session
Fri Oct 23 14:56:30 2015
db_recovery_file_dest_size of 2048 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.