1. 首页首页
  2. 编程笔记编程笔记

oracle并行parallel 查询

编程笔记 • • 阅读 21

首先说下测试环境：单实例DG，数据库版本为11.2.0.1.0，db_name='TEST'，DB_UNIQUE_NAME=TEST(主备库都是TEST) 主库和备库可以正常同步，做ddl、ddm操作都可以同步，但是我查看归档日志的时候备库比主库多一个归档日志，

首先说下测试环境：单实例DG，数据库版本为11.2.0.1.0，db_name='TEST'，DB_UNIQUE_NAME=TEST(主备库都是TEST)

主库和备库可以正常同步，做ddl、ddm操作都可以同步，但是我查看归档日志的时候备库比主库多一个归档日志，然后我把主库关掉，那个备库多出来的归档日志就没了。

后来我做主备切换，发现不能切换，下面是我的配置方法，以及日志

主库归档日志：

thread-1876352-1-1.html

备库归档日志：(红色表示多余的归档日志，把主库挂掉，就没了)

dd06a1abc3fb84da465ff4eff32374f6.gif

QQ图片20140706161120.jpg (69.48 KB, 下载次数: 13)

2014-7-6 16:11 上传

主备库切换：

主库查询切换状态(不具备切换状态)

dd06a1abc3fb84da465ff4eff32374f6.gif

QQ图片20140706161502.jpg (7.8 KB, 下载次数: 10)

2014-7-6 16:26 上传

遇到这种情况，应该怎么做，我在网上查了一下，网上一般说的都是备库有归档日志没有应用，或者主库有归档没有传过去，但是和我遇到的问题还是有点区别，我这边是可以正常传归档，但是备库比主库多了一组。

参数文件

备库：

*.audit_file_dest='/home/oracle/app/admin/TEST/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/home/oracle/oradata/TEST/control01.ctl','/home/oracle/app/flash_recovery_area/TEST/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='TEST'

*.db_recovery_file_dest='/home/oracle/app/flash_recovery_area'

*.db_recovery_file_dest_size=5218762752

*.diagnostic_dest='/home/oracle/app'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCL2XDB)'

*.log_archive_dest_1='location=/home/oracle/oradata/TEST/archivelog'

*.memory_target=14735638528

*.open_cursors=300

*.processes=500

*.remote_login_passwordfile='EXCLUSIVE'

*.sessions=555

*.undo_tablespace='UNDOTBS1'

*.DB_UNIQUE_NAME=TEST

*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl2,orcl)'

*.log_archive_dest_1='location=/home/oracle/oradata/TEST/archivelog  valid_for=(all_logfiles,all_roles) db_unique_name=TEST'

*.log_archive_dest_2='service=orcl2 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=TEST'

*.log_archive_dest_state_1=enable

*.log_archive_dest_state_2=enable

*.log_archive_max_processes=10

*.fal_client='orcl'

*.fal_server='orcl2'

*.STANDBY_FILE_MANAGEMENT=AUTO

主库参数文件：

*.audit_file_dest='/home/oracle/app/admin/TEST/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/home/oracle/oradata/TEST/control01.ctl','/home/oracle/app/flash_recovery_area/TEST/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='TEST'

*.db_recovery_file_dest='/home/oracle/app/flash_recovery_area'

*.db_recovery_file_dest_size=5218762752

*.db_unique_name='TEST'

*.diagnostic_dest='/home/oracle/app'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCL2XDB)'

*.fal_client='ORCL2'

*.fal_server='ORCL'

*.log_archive_config='dg_config=(orcl2,orcl)'

*.log_archive_dest_1='location=/home/oracle/oradata/TEST/archivelog  valid_for=(all_logfiles,all_roles) db_unique_name=TEST'

*.log_archive_dest_2='service=orcl lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=TEST'

*.log_archive_dest_state_1='ENABLE'

*.log_archive_dest_state_2='ENABLE'

*.log_archive_max_processes=10

*.memory_target=14735638528

*.open_cursors=300

*.processes=500

*.remote_login_passwordfile='EXCLUSIVE'

*.sessions=555

*.standby_file_management='AUTO'

*.undo_tablespace='UNDOTBS1'

日志

主库日志：

ARC9 started with pid=29, OS id=41078

ARC2: Archival started

ARC3: Archival started

ARC4: Archival started

ARC5: Archival started

ARC6: Archival started

ARC7: Archival started

ARC8: Archival started

ARC1: Becoming the 'no FAL' ARCH

ARC1: Becoming the 'no SRL' ARCH

ARC2: Becoming the heartbeat ARCH

Sun Jul 06 15:44:05 2014

NSA2 started with pid=30, OS id=41080

Archived Log entry 63 added for thread 1 sequence 164 ID 0x80256749 dest 1:

Error 1034 received logging on to the standby

Errors in file /home/oracle/app/diag/rdbms/test/orcl2/trace/orcl2_arc2_41064.trc:

ORA-01034: ORACLE not available

PING[ARC2]: Heartbeat failed to connect to standby 'orcl'. Error is 1034.

replication_dependency_tracking turned off (no async multimaster replication found)

Error 1034 received logging on to the standby

Errors in file /home/oracle/app/diag/rdbms/test/orcl2/trace/orcl2_arc3_41066.trc:

ORA-01034: ORACLE not available

FAL[server, ARC3]: Error 1034 creating remote archivelog file 'orcl'

FAL[server, ARC3]: FAL archive failed, see trace file.

Errors in file /home/oracle/app/diag/rdbms/test/orcl2/trace/orcl2_arc3_41066.trc:

ORA-16055: FAL request rejected

ARCH: FAL archive failed. Archiver continuing

ORACLE Instance orcl2 - Archival Error. Archiver continuing.

Starting background process QMNC

Sun Jul 06 15:44:05 2014

QMNC started with pid=31, OS id=41084

Completed: ALTER DATABASE OPEN

ARC9: Archival started

ARC0: STARTING ARCH PROCESSES COMPLETE

ARC0: STARTING ARCH PROCESSES

Sun Jul 06 15:44:06 2014

ARCa started with pid=33, OS id=41088

Sun Jul 06 15:44:06 2014

db_recovery_file_dest_size of 4977 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.

ARCa: Archival started

ARC0: STARTING ARCH PROCESSES COMPLETE

Thread 1 advanced to log sequence 166 (LGWR switch)

Shutting down archive processes  Current log# 1 seq# 166 mem# 0: /home/oracle/oradata/TEST/redo01.log

ARCH shutting down

ARCa: Archival stopped

Archived Log entry 64 added for thread 1 sequence 165 ID 0x80256749 dest 1:

Starting background process CJQ0

Sun Jul 06 15:44:08 2014

CJQ0 started with pid=32, OS id=41100

Setting Resource Manager plan SCHEDULER[0x3009]

266faa3e2451dd0bb0047cc3ca69ae30.gifEFAULT_MAINTENANCE_PLAN via scheduler window

Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter

Sun Jul 06 15:44:11 2014

Starting background process VKRM

Sun Jul 06 15:44:11 2014

VKRM started with pid=33, OS id=41103

Sun Jul 06 15:44:20 2014

ALTER DATABASE REGISTER LOGFILE '/home/oracle/oradata/TEST/archivelog/1_164_851783113.dbf'

There are 1 logfiles specified.

ALTER DATABASE REGISTER [LOGICAL] LOGFILE

ORA-16225 signalled during: ALTER DATABASE REGISTER LOGFILE '/home/oracle/oradata/TEST/archivelog/1_164_851783113.dbf'...

Sun Jul 06 15:45:54 2014

Using STANDBY_ARCHIVE_DEST parameter default value as /home/oracle/oradata/TEST/archivelog

ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=MEMORY SID='*';

Sun Jul 06 15:45:54 2014

ARC2: Archival destination is a Primary RAC instance: 'orcl'

Sun Jul 06 15:45:54 2014

ARC5: Archival destination is a Primary RAC instance: 'orcl'

Sun Jul 06 15:45:54 2014

ARC4: Archival destination is a Primary RAC instance: 'orcl'

Sun Jul 06 15:45:56 2014

******************************************************************

LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2

******************************************************************

Sun Jul 06 15:45:56 2014

Thread 1 advanced to log sequence 167 (LGWR switch)

Current log# 2 seq# 167 mem# 0: /home/oracle/oradata/TEST/redo02.log

Sun Jul 06 15:45:56 2014

Archived Log entry 65 added for thread 1 sequence 166 ID 0x80256749 dest 1:

LNS: Archival destination is a Primary RAC instance: 'orcl'

Sun Jul 06 15:49:05 2014

Starting background process SMCO

Sun Jul 06 15:49:05 2014

SMCO started with pid=36, OS id=41135

备库日志：

un Jul 06 15:46:01 2014

ARC9 started with pid=29, OS id=3573

ARC2: Archival started

ARC3: Archival started

ARC4: Archival started

ARC5: Archival started

ARC6: Archival started

ARC7: Archival started

ARC8: Archival started

ARC1: Becoming the 'no FAL' ARCH

ARC2: Becoming the heartbeat ARCH

Sun Jul 06 15:46:02 2014

Using STANDBY_ARCHIVE_DEST parameter default value as /home/oracle/oradata/TEST/archivelog

RFS[1]: Assigned to RFS process 3579

RFS[1]: Identified database type as 'physical standby': Client is ARCH pid 41064

RFS[1]: Identified database type as 'standby cross instance archival': Client is ARCH pid 41064

Sun Jul 06 15:46:02 2014

RFS[2]: Assigned to RFS process 3581

RFS[2]: Identified database type as 'physical standby': Client is ARCH pid 41070

Sun Jul 06 15:46:02 2014

RFS[3]: Assigned to RFS process 3583

RFS[3]: Identified database type as 'physical standby': Client is ARCH pid 41068

RFS[2]: Identified database type as 'standby cross instance archival': Client is ARCH pid 41070

RFS[3]: Identified database type as 'standby cross instance archival': Client is ARCH pid 41068

RFS[2]: Opened log for thread 1 sequence 165 dbid -2145037751 branch 851783113

RFS[3]: Opened log for thread 1 sequence 164 dbid -2145037751 branch 851783113

Archived Log entry 17 added for thread 1 sequence 165 rlc 851783113 ID 0x80256749 dest 2:

Archived Log entry 18 added for thread 1 sequence 164 rlc 851783113 ID 0x80256749 dest 2:

ARC9: Archival started

ARC0: STARTING ARCH PROCESSES COMPLETE

Sun Jul 06 15:46:04 2014

RFS[4]: Assigned to RFS process 3585

RFS[4]: Identified database type as 'physical standby': Client is LGWR ASYNC pid 41080

Primary database is in MAXIMUM PERFORMANCE mode

RFS[4]: Identified database type as 'standby cross instance archival': Client is LGWR ASYNC pid 41080

RFS[4]: Opened log for thread 1 sequence 166 dbid -2145037751 branch 851783113

Archived Log entry 19 added for thread 1 sequence 166 rlc 851783113 ID 0x80256749 dest 2:

RFS[4]: Opened log for thread 1 sequence 167 dbid -2145037751 branch 851783113

Sun Jul 06 15:46:12 2014

alter database recover managed standby database disconnect from session

Attempt to start background Managed Standby Recovery process (orcl)

Sun Jul 06 15:46:12 2014

MRP0 started with pid=34, OS id=3587

MRP0: Background Managed Standby Recovery process started (orcl)

started logmerger process

Sun Jul 06 15:46:17 2014

Managed Standby Recovery not using Real Time Apply

Parallel Media Recovery started with 4 slaves

Waiting for all non-current ORLs to be archived...

All non-current ORLs have been archived.

Media Recovery Log /home/oracle/oradata/TEST/archivelog/1_164_851783113.dbf

Media Recovery Log /home/oracle/oradata/TEST/archivelog/1_165_851783113.dbf

Media Recovery Log /home/oracle/oradata/TEST/archivelog/1_166_851783113.dbf

Media Recovery Waiting for thread 1 sequence 167

Completed: alter database recover managed standby database disconnect from session

Sun Jul 06 16:00:59 2014

db_recovery_file_dest_size of 4977 MB is 3.01% 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.

2014-7-6 16:10 上传

a9e439422dfa5ee454465ca071732e60.gif

9da8c69fd21b85dfd654e308afd6bd05.gif

dd06a1abc3fb84da465ff4eff32374f6.gif

知秋君
知秋君

知秋君

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请关注公众号“全栈程序员社区”联系小编举报，一经查实，本站将立刻删除。
上一篇 2024-07-31 14:12
下一篇 2024-07-31 13:48

相关推荐