broker 学习

最新推荐文章于 2023-06-19 09:10:31 发布

congfen5646

最新推荐文章于 2023-06-19 09:10:31 发布

阅读量419

点赞数

文章标签：数据库网络

配置先决条件
[1]server parameter file 启动数据库
[2]DG_BROKER_START设置true
[]3如果配置环境有rac 则必须配置DG_BROKER_CONFIG_FILEn参数指向共享文件其可以存储在CFS 、裸设备、asm上
[4]网络联通
[5]必须配置local_listener
由各实例参数local_listener解析出来的监听地址必须能被所有成员访问 local_listener=network_name
[6]修改监听文件的GLOBAL_DBNAME修改成db_unique_name_DGMGRL.domain的组合这样就可以在broker操作过程中重启实例
--如果忽略这点可能导致enable configuration时走不下去一直卡着
[7]如果环境中有rac 则instance需在OCR中注册
srvctl modify -d orcl -o $ORACLE_HOME -s mount
[8]主库需在归档模式下运行

创建修改DG_BROKER_CONFIG_FILEn
第一次启动broker时会自动根据指定参数创建文件
CFS: DG_BROKER_CONFIG_FILE1=dr1db_unique_name.dat
raw: DG_BROKER_CONFIG_FILE2=dr2SID.dat
alter system set dg_broker_start=true
--------------------------
ALTER DATABASE OPEN
Wed Aug 25 16:44:06 2010
Starting Data Guard Broker (DMON)
--每个文件一般需占有>=1m空间可以存储10个数据库 45个实例
如果DMON正在运行则
1、使用CLI停止broker configuration
2、alter system set dg_broker_start=false
3、alter system set dg_broker_config_file1=filespec1
4、如果是CFS则重命名文件
5、alter system set dg_broker_start=true
6、使用CLI开启broker configuration
dg_broker_start 如果使用DG_BROKER 则会自动设置true 用CLI则手动修改

一、创建broker配置
使用GUI 可以添加已存在的standby database到配置中，或创建standby并添加到配置中
standby 可以是logical or physical
使用CLI是主库和备库必须同时存在备库通过使用主库的备份来构造
1.1 使用CLI 创建configuration
DGMGRL>
连接主库
创建包含主库profile的配置文件这里命名为MYBROKER
--broker使用object profile管理监控Instance的状态
--通过profile中的状态值和属性 broker可以识别standby database 是logical 还是 physical
connect sys/**@orcl1 or /connect sys/**
用户必须有sysdba权限但不需指定as sysdba DGMGRL默认指定这个选项
DGMGRL> create configuration MYBROKER as
> primary database is 'orcl'--必须是db_unique_name
> connect identifier is orcl;--network_service_name
Configuration "mybroker" created with primary database "orcl"
观察是否自动创建DG_BROKER_CONFIG_FILEn文件
ASMCMD [+dga/dgm] > ls
dr1orcl.dat
dr2orcl.dat
---------standby database 没有创建到文件
查看配置
DGMGRL> show configuration

Configuration
Name:                mybroker
Enabled:             NO
Protection Mode:     MaxPerformance
Fast-Start Failover: DISABLED
Databases:
    orcl - Primary database

Current status for "mybroker":
DISABLED
添加STANDBY DATABASE
使用ADD database 创建standby configuration profile
DGMGRL> add database alone as--db_unique_name
> connect identifier is alone_inst--network_service_name
> maintained as physical;--standby 类型
Database "alone" added
查看配置
DGMGRL> show configuration

Configuration
Name:                mybroker
Enabled:             NO
Protection Mode:     MaxPerformance
Fast-Start Failover: DISABLED
Databases:
    orcl - Primary database
    alone - Physical standby database        --NEW

Current status for "mybroker":
DISABLED
1.2 设置数据库属性
查看在configuration中存在的实例属性
DGMGRL> show database verbose orcl

Database
Name:            orcl
Role:            PRIMARY
Enabled:         NO
Intended State: OFFLINE
Instance(s):
    orcl1

Properties:
    InitialConnectIdentifier        = 'orcl'
    LogXptMode                      = 'ARCH'
    Dependency                      = ''
    DelayMins                       = '0'
    Binding                         = 'OPTIONAL'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'--300秒同步一次
    NetTimeout                      = '180'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'MANUAL'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '2'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = ''
    LogFileNameConvert              = ''
    FastStartFailoverTarget         = ''
    StatusReport                    = '(monitor)'
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = 'rac1'
    SidName                         = 'orcl1'
    LocalListenerAddress            = '(ADDRESS=(PROTOCOL=tcp)(HOST=rac1)(PORT=1521))'
    StandbyArchiveLocation          = '/log1'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    LatestLog                       = '(monitor)'
    TopWaitEvents                   = '(monitor)'

Current status for "orcl":
DISABLED
修改语法
edit database db_unique_name set property key = value;
ex:
DGMGRL> edit database alone set property 'LogArchiveFormat'='%t_%s_%r_%d.dbf';
Property "LogArchiveFormat" updated
修改后会同步目标库的spfile文件
查看日志：
Mon Aug 30 16:43:33 2010
ALTER SYSTEM SET log_archive_format='%t_%s_%r_%d.dbf' SCOPE=SPFILE SID='alone';

二、使配置可用
使所有配置可用
enable configuration
DGMGRL> enable configuration
Enabled.
观察standby database是否自动创建DG_BROKER_CONFIG_FILEn文件
--验证了broker在头次使用时会自动创建文件
DGMGRL> show configuration

Configuration
Name:                nbk
Enabled:             YES
Protection Mode:     MaxPerformance
Fast-Start Failover: DISABLED
Databases:
    orcl - Primary database
    alone - Physical standby database

Current status for "nbk":
SUCCESS
--注：如果DG_BROKER_CONFIG_FILEn路径有错导致没有生成指定文件则导致configuration配置无法
保存（只能保存在内存中）重启实例后：
DGMGRL> show configuration
Error: ORA-16532: Data Guard broker configuration does not exist
Configuration details cannot be determined by DGMGRL

使standby配置可用--enable database只能用于standby
DGMGRL> enable database orcl;
Warning: ORA-16614: object has an ancestor that is disabled
--ancestor 祖宗前辈。。

DGMGRL> enable database alone;
Warning: ORA-16614: object has an ancestor that is disabled
一般只有standby节点diable 而没有跟其他节点一起enable时使用

20100826
rac1节点执行dgmgrl
enable configuration后观察到alertSID.log
orcl1:
NSV1 started with pid=32, OS id=25848
RSM0 started with pid=40, OS id=26054
-------NSV RSM
[1]修改参数全局和实例级别参数
ALTER SYSTEM SET log_archive_dest_3='service="(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.3)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=alone_XPT)(INSTANCE_NAME=alone)(SERVER=dedicated)))"',' LGWR ASYNC NOAFFIRM delay=0 OPTIONAL max_failure=0 max_connections=1 reopen=300 db_unique_name="alone" register net_timeout=180 valid_for=(online_logfile,primary_role)' SCOPE=BOTH;
ALTER SYSTEM SET db_file_name_convert='/u01/oracle/oradata/alone','+dga/orcl/datafile','/u01/oracle/oradata/alone','+dga/orcl/tempfile' SCOPE=SPFILE;
ALTER SYSTEM SET log_file_name_convert='/u01/oracle/oradata/alone','+dga/orcl/onlinelog/' SCOPE=SPFILE;
等等。。。
[2]LNS2 started with pid=31, OS id=26435
归档
orcl2:
INSV started with pid=20, OS id=14278
RSM0 started with pid=31, OS id=18640
[1]修改参数实例级别参数
ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='orcl2';
[2]LNS2 started with pid=40, OS id=18930
归档

单实例节点：
NSV0 started with pid=23, OS id=27543
RSM0 started with pid=24, OS id=27545
[1]修改参数实例级别参数
ALTER SYSTEM SET log_archive_dest_1='location="/u01/oracle/admin/onlinelog"','valid_for=(ALL_LOGFILES,ALL_ROLES)' SCOPE=BOTH SID='alone';
[2]恢复应用日志文件
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE
Attempt to start background Managed Standby Recovery process (alone)
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE
Mon Aug 30 12:09:23 2010
Attempt to start background Managed Standby Recovery process (alone)RFS[7]: Possible network disconnect with primary database
Closing latent archivelog for thread 1 sequence 190
EOF located at block 571 low SCN 0:2833556 next SCN 0:2835309
Latent archivelog '/u01/oracle/admin/onlinelog/1_190_727198250.dbf'
If you wish to failover to this standby database, you should use the
following command to manually register the archivelog for recovery:
ALTER DATABASE REGISTER LOGFILE '/u01/oracle/admin/onlinelog/1_190_727198250.dbf';
-----standby 手动注册丢失日志的方法
RFS[9]: Assigned to RFS process 27549
RFS[9]: Identified database type as 'physical standby'
MRP0 started with pid=21, OS id=27547
MRP0: Background Managed Standby Recovery process started (alone)
Managed Standby Recovery starting Real Time Apply
Media Recovery Log /u01/oracle/admin/onlinelog/1_189_727198250.dbf

******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_3
******************************************************************
Thu Aug 26 12:31:18 2010
ALTER SYSTEM SET log_archive_dest_3='service="(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=alone)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=alone_XPT)(INSTANCE_NAME=alone)(SERVER=dedicated)))"',' LGWR ASYNC NOAFFIRM delay=0 OPTIONAL max_failure=0 max_connections=1 reopen=300 db_unique_name="alone" register net_timeout=180 valid_for=(online_logfile,primary_role)' SCOPE=MEMORY;
Thu Aug 26 12:31:18 2010
ALTER SYSTEM SET log_archive_dest_state_3='ENABLE' SCOPE=MEMORY;
Thu Aug 26 12:31:18 2010
Thread 2 cannot allocate new log, sequence 89
Checkpoint not complete
Current log# 4 seq# 88 mem# 0: +DGA/orcl/onlinelog/group_4.267.727198595
Current log# 4 seq# 88 mem# 1: +DGB/orcl/onlinelog/group_4.260.727198595
Thread 2 advanced to log sequence 89
Current log# 3 seq# 89 mem# 0: +DGA/orcl/onlinelog/group_3.266.727198589
Current log# 3 seq# 89 mem# 1: +DGB/orcl/onlinelog/group_3.259.727198591
注：节点间不断的在刷日志

查看节点状态
DGMGRL> show database orcl statusreport
STATUS REPORT
       INSTANCE_NAME   SEVERITY ERROR_TEXT
               orcl2      ERROR ORA-16797: database is not using a server parameter file
               orcl1    WARNING ORA-16714: the value of property StandbyFileManagement is inconsistent with the database setting
               orcl1    WARNING ORA-16714: the value of property ArchiveLagTarget is inconsistent with the database setting
               orcl1    WARNING ORA-16714: the value of property LogArchiveMaxProcesses is inconsistent with the database setting
               orcl1    WARNING ORA-16714: the value of property LogArchiveMinSucceedDest is inconsistent with the database setting
DGMGRL> show database alone statusreport
Error: ORA-16532: Data Guard broker configuration does not exist
节点alone上raw设备没有设置好属主

-----------------------------------------
startup
startup pfile='/u01/oracle/app/product/10.2.0/db_1/dbs/initSID.ora'
pfile>>
SPFILE='+DGA/orcl/spfileorcl.ora'
---------------------------------------

DGMGRL> show configuration
Error: ORA-16525: the Data Guard broker is not yet available

注：切记各节点必须以server spfile启动数据库
--遗留问题:
rac2上查看configuration
DGMGRL> show database alone statusreport
Warning: ORA-16509: the request timed out
-- show database alone 正常
三、设置保护模式
MaxPerformance--&gtmaxprotection
[1]配置standby log files
[2]修改备库日志传输模式为同步--多个standby节点则至少设置一个节点为sync
edit database alone set property logxptmode=sync
[3]修改配置（configuration）文件保护模式
DGMGRL> edit configuration set protection mode as maxprotection;
Operation requires shutdown of instance "orcl1" on database "orcl"
Shutting down instance "orcl1"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "orcl1" on database "orcl"
Starting instance "orcl1"...
ORACLE instance started.
Database mounted.
ORA-16072: a minimum of one standby database destination is required
ORA-06512: at "SYS.X$DBMS_DRS", line 347
ORA-06512: at line 8

Unable to connect to database
ORA-12537: TNS:connection closed

Failed.
-----orcl2 先关闭关了就没起来
日志报：
LGWR: Primary database is in MAXIMUM PROTECTION mode
Destination LOG_ARCHIVE_DEST_2 is SYNCHRONIZED
LGWR: Destination LOG_ARCHIVE_DEST_1 is not serviced by LGWR
LNSb started with pid=34, OS id=19110
Errors in file /u01/oracle/app/admin/orcl/bdump/orcl2_lgwr_18468.trc:
ORA-16086: standby database does not contain available standby log files
Destination LOG_ARCHIVE_DEST_2 is UNSYNCHRONIZED
LGWR: Error 16086 disconnecting from destination LOG_ARCHIVE_DEST_2 standby host
'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.3)(PORT=1521)))
(CONNECT_DATA=(SERVICE_NAME=alone_XPT)(INSTANCE_NAME=alone)(SERVER=dedicated)))'
ORA-16072: a minimum of one standby database destination is required
-----orcl1 正常
Error: ORA-16525: the Data Guard broker is not yet available

Configuration details cannot be determined by DGMGRL

ORA-16086:由于备库的standby log和主库的log size不一致导致备库standby log 无效
In fact, the size has to be the same, but the standby should always have at least
one more standby redo log group than the primary does
alter database drop standby logfile group # -- including content;
alter database add standby logfile --重建
查看状态
SQL> select group#,status from v$standby_log;

    GROUP# STATUS
---------- ----------
         5 ACTIVE
         6 UNASSIGNED
         7 UNASSIGNED
         8 ACTIVE
         9 UNASSIGNED
        10 UNASSIGNED

6 rows selected.
The only thing about this view to note is that “UNASSIGNED” means the same
thing as “INACTIVE” does in the v$log view of normal redo logs.
standby log在max protection模式下是必须的否则宕机调整后即可解决
观察max protection模式日志应用的变化
rac1：
LGWR: Standby redo logfile selected to archive thread 1 sequence 234
LGWR: Standby redo logfile selected for thread 1 sequence 234 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 234
Current log# 1 seq# 234 mem# 0: +DGA/orcl/onlinelog/group_1.261.727198251
Current log# 1 seq# 234 mem# 1: +DGB/orcl/onlinelog/group_1.257.727198253

单实例：
Recovery of Online Redo Log: Thread 1 Group 5 Seq 234 Reading mem 0
Mem# 0 errs 0: /u01/oracle/oradata/alone/redo5.rdo

dg broker 的异常同样搞定
DGMGRL> show configuration

Configuration
Name:                nbk
Enabled:             YES
Protection Mode:     MaxProtection--这个变了。。。
Fast-Start Failover: DISABLED
Databases:
    orcl - Primary database
    alone - Physical standby database

Current status for "nbk":
SUCCESS

对象
primary database physical database logical database
对象状态
primary online
open| read and write |log transport service active
如果是rac 应用到所有实例
to online
broker开启log transport service需设置LOG_ARCHIVE_DEST_n参数
必要时在configuration中设置保护模式

primary log-transport-off
open|read and write|log transport inactive
rac 同上
to log-transport-off
broker关闭log transport service需reset LOG_ARCHIVE_DEST_n参数

primary offline
[1]shutdown database
[2]the broker is no longer managing configuration

physical online
mounted|log apply service is active
rac 有且仅有一个实例是 apply instance 如果指定实例异常 broker会
自动其他可用实例作为应用实例

physical log-apply-off
mounted|log apply service is inactive
physical read-only
open|read|log apply service is inactive
rac 同上
to log-apply-off
database read only 则close database--read only 转换为其他状态时建议关闭所有事务否则broker会自动关闭所有事务
database online 则stops log apply services

physical or logical offline
[1]broker automatically shuts down the database turns off log transport service to this database
[2]leave the database as disabled in configuration
logical online
open |read|log apply service is active
log apply service 同physical online
logical log-apply-off
open |read|log apply service is inactive
对象状态的相互转换
ex:
edit database alone SET STATE='LOG-TRANSPORT-OFF';

properties
属性分监控和配置属性
monitorable and configurable
monitorable 在对象enable时 read only
configurable 任意时候可查询、更新
--When a new database is added into the configuration, the broker connects
--to the database and imports initial values for the database properties from the
--current database settings.
--修改静态参数重启Instance生效
查看属性
show database verbose alone

InconsistentLogXptProps
查看the broker configuration file和对象log tranasport service参数间的差异discrepancy
Log Transport Services属性
--AlternateLocation
--AsyncBlocks
--Binding
--Dependency
--LogShipping
--LogXptMode
--MaxFailure
--NetTimeout
--ReopenSecs
--StandbyArchiveLocation

20100901 rac起不来
rac1：日志：
ORA-16143: RFS connections not allowed during or after terminal recovery
LGWR: Error 16143 verifying archivelog destination LOG_ARCHIVE_DEST_2
Wed Sep 1 15:59:31 2010
Destination LOG_ARCHIVE_DEST_2 is UNSYNCHRONIZED
LGWR: Error 16143 disconnecting from destination LOG_ARCHIVE_DEST_2 standby host '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.3)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=alone_XPT)(INSTANCE_NAME=alone)(SERVER=dedicated)))'
LGWR: Continuing...
LGWR: Minimum of 1 applicable standby database required
--------------------------------------------------------
Oracle Error : ORA-16143: RFS connections not allowed during or after terminal recovery

Cause: An attempt was made, by an RFS process, to access a standby online log file during or after a terminal recovery.

Action: The primary must not attempt to archive to the standby after a terminal recovery.
尝试primary 切回最大性能拷贝归档日志到standby 做managed recover
-------------------------------------------------------------------------------------
rac2：日志
LGWR: Standby database destination mismatch with other open instances
Errors in file /u01/oracle/app/admin/orcl/bdump/orcl2_lgwr_16333.trc:
ORA-16075: standby database destination mismatch

standby 节点
--ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE;--实时应用
through all switchover??
--注：作用？？

dg 后台进程
RFS：remote file server　
On the standby system, the remote file server (RFS) receives redo data over the network from the
LGWR process and writes the redo data to the standby redo log files.

LNSn:
On the primary database, the LGWR process submits the redo data to one or more network server
(LNSn) processes, which then initiate the network I/O in parallel to multiple remote destinations.

MRP: managed recovery process　
The managed recovery process (MRP) applies archived redo log files to the physical standby database,
and automatically determines the optimal number of parallel recovery processes at the time it starts．
The number of parallel recovery slaves spawned is based on the number of CPUs available on the
standby server.

LSP:logical standby process　
The logical standby process (LSP) uses parallel execution (Pnnn) processes to apply archived redo
log files to the logical standby database, using SQL interfaces.

常用命令
change mode
alter database set standby database to maximize protection

Start Standby Database

startup nomount
alter database mount standby database;
alter database recover managed standby database disconnect;

Disable/Enable archive log destinations

alter system set log_archive_dest_state_2 = 'defer';
alter system set log_archive_dest_state_2 = 'enable';
To remove a delay from a standby

alter database recover managed standby database cancel;

alter database recover managed standby database nodelay disconnect;

Stop and Start of Logical standby apply

alter database stop logical standby apply;
alter database start logical standby apply;

Physical Standby switchover:
I. Before Switchover:

1. As I always recommend, test the Switchover first on your testing systems before working on Production.

2. Verify the primary database instance is open and the standby database instance is mounted.

3. Verify there are no active users connected to the databases.

4. Make sure the last redo data transmitted from the Primary database was applied on the standby database. Issue the following commands
on Primary database and Standby database to find out:
SQL>select sequence#, applied from v$archvied_log;
Perform. SWITCH LOGFILE if necessary.
1. Initiate the switchover on the primary database PRIM:
SQL>connect /@PRIM as sysdba
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

2. After step 1 finishes, Switch the original physical standby db STAN to primary role;
Open another prompt and connect to SQLPLUS:
SQL>connect /@STAN as sysdba
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

3. Immediately after issuing command in step 2, shut down and restart the former primary instance PRIM:
SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP MOUNT;

4. After step 3 completes:
- If you are using Oracle Database 10g release 1, you will have to Shut down and restart the new primary database STAN.
SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP;

- If you are using Oracle Database 10g release 2, you can open the new Primary database STAN:
SQL>ALTER DATABASE OPEN;

STAN is now transitioned to the primary database role.

5. On the new primary database STAN, perform. a SWITCH LOGFILE to start sending redo data to the standby database PRIM.
SQL>ALTER SYSTEM SWITCH LOGFILE;

SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1';

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21993926/viewspace-676251/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21993926/viewspace-676251/