dg broker 创建dg &switchover

最新推荐文章于 2024-04-16 18:54:26 发布

a545578125

最新推荐文章于 2024-04-16 18:54:26 发布

阅读量848

点赞数

分类专栏： oracle db

oracle db 专栏收录该内容

78 篇文章 2 订阅

订阅专栏

instance to instance + dg broker
创建备库前：
---------主库
修改tnsnames.ora 分别添加instance entry
tnsping net_service_name 测试是否连通
修改主库 rha实例参数文件
alter system set log_archive_dest_1='location=/u01/oracle/onlinelog' scope=spfile;
调整为归档模式
生成备份文件、standby控制文件、spfile   把这些文件含归档文件一起传输到备库相同路径
---------备库
创建密码文件与主库保持一致 orapwd file=* password=
--忘了建密码到后面主库连接备库时会报
--RMAN-04006: error from auxiliary database: ORA-01031: insufficient privileges
修改spfile文件
添加db_file_name_convert   log_file_name_convert db_unique_name
保持db_name于主库一致
修改相关参数为本地路径
用spfile启动备库为nomount
创建listener.ora 或Netca 创建监听后修改如下
SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (SID_NAME = rhb)
      (ORACLE_HOME = /u01/oracle/app/product/10.2.0/db_1)
      (GLOBAL_DBNAME = rhb)
    )
)

LISTENER =
(DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = rhb)(PORT = 1521))
    )
)

主库执行复制
rman target/ auxiliary sys/111111@rhb_inst
。。。
Finished Duplicate Db
备库查看状态
SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

SQL> select status from v$instance;

STATUS
------------
MOUNTED
查看日志及size
SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/oracle/app/oradata/rhb/redo03.log
/u01/oracle/app/oradata/rhb/redo02.log
/u01/oracle/app/oradata/rhb/redo01.log

SQL> select bytes/1024/1024 from v$log;

BYTES/1024/1024
---------------
50
添加standby 日志用于lgwr传输日志时应用
alter database add standby logfile group 4 '/u01/oracle/app/oradata/rhb/redo4.rdo' size 50m;
alter database add standby logfile group 5 '/u01/oracle/app/oradata/rhb/redo5.rdo' size 50m;
alter database add standby logfile group 6 '/u01/oracle/app/oradata/rhb/redo6.rdo' size 50m;
alter database add standby logfile group 7 '/u01/oracle/app/oradata/rhb/redo7.rdo' size 50m;

使用DG配置模式

修改主库备库参数
dg_broker_config_file1选默认
dg_broker_config_file2选默认
dg_broker_start 改为TRUE alter system set dg_broker_start=true scope=spfile;
--alter system set local_listener=orcl scope=spfile;
LOCAL_LISTENER = network_name
----------------------------------------------------------------------------------
After the initial connection, the DMON process constructs connect descriptors for
communication with other DMON processes on other databases, using the address
value from the LOCAL_LISTENER initialization parameter from those databases.
LOCAL_LISTENER specifies a network name that resolves to an address or address list
of Oracle Net local listeners (that is, listeners that are running on the same machine
as this instance). The address or address list is specified in the TNSNAMES.ORA file
or other address repository as configured for your system.
-----------------------------------------------------------------------------------
分别重启。

主节点上执行
dgmgrl 创建configuration 添加standby节点再enable configuration
观察到dg_broker_config_filen执行的路径下创建了drnSID.dat格式的文件。
分别观察参数上的变化
主库：
log_archive_dest_2 被自动设置为执行备库的Lgwr async 异步模式即最大性能模式
log_archive_config dg_config=(rhb)--这里只需定义远程归档实例的db_unique_name
验证：
SQL> select PROTECTION_MODE from v$database;

PROTECTION_MODE
--------------------
MAXIMUM PERFORMANCE
备库则设置了fal_client fal_server
查看日志来确认
主库日志：
ALTER SYSTEM SET log_archive_config='dg_config=(rhb)' SCOPE=BOTH;
ALTER SYSTEM SET log_archive_dest_2='service="(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=rhb)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=rhb_XPT)(INSTANCE_NAME=rhb)(SERVER=dedicated)))"',' LGWR ASYNC NOAFFIRM delay=0 OPTIONAL max_failure=0 max_connections=1 reopen=300 db_unique_name="rhb" register net_timeout=180 valid_for=(online_logfile,primary_role)' SCOPE=BOTH;

备库日志：
ALTER SYSTEM SET fal_server='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=rha)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=orcl_XPT)(SERVER=dedicated)))' SCOPE=BOTH;
Sat Sep 4 01:12:25 2010
ALTER SYSTEM SET fal_client='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=rhb)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=rhb_XPT)(INSTANCE_NAME=rhb)(SERVER=dedicated)))' SCOPE=BOTH;

查看日志同步情况
SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
6
主库和备库当前一致。

二、switchover
switchover前准备
查看各实例是否mount或open
断开所有连接
备库必须应用到所有从主库传输过来的归档日志
select sequence#, applied from v$archived_log
ex:
SQL> select sequence#,applied from v$archived_log;

SEQUENCE# APP
---------- ---
         4 YES
         3 YES
         2 YES
         5 YES
         6 NO
         7 NO

6 rows selected.

SQL> recover managed standby database disconnect from session;
Media recovery complete.
SQL> select sequence#,applied from v$archived_log;

SEQUENCE# APP
---------- ---
         4 YES
         3 YES
         2 YES
         5 YES
         6 YES
         7 YES

6 rows selected.

------------------------------------
修改主备库的静态监听配置用于broker 重启实例--SID_DGMGRL.DOMAIN
修改listener.ora 修改GLOBAL_dbname
SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (SID_NAME = orcl)
      (ORACLE_HOME = /u01/oracle/product/10.2.0/db_1)
      (GLOBAL_DBNAME = orcl_DGMGRL)
    )
)
reload监听重启
Services Summary...
Service "orcl_DGMGRL" has 1 instance(s).
Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
这步要排在enable configuration前执行
有时候enable configuration或show configuration是半天没反应
就需要分别查看各个节点的状态
可能是节点未开启监听或无法连接导致
--------------------------------------------------

修改主库的db_file_name_convert和log_file_name_convert
SQL> alter system set db_file_name_convert='/u01/oracle/app/oradata/rhb/','/u01/oracle/oradata/orcl' scope=spfile;

System altered.

SQL> alter system set log_file_name_convert='/u01/oracle/app/oradata/rhb/','/u01/oracle/oradata/orcl' scope=spfile;

System altered.

启动dgmgrl
show configuration 报错
查看 show database orcl statusreport
DGMGRL> show database orcl statusreport
STATUS REPORT
       INSTANCE_NAME   SEVERITY ERROR_TEXT
                orcl    WARNING ORA-16714: the value of property DbFileNameConvert is inconsistent with the database setting
                orcl    WARNING ORA-16714: the value of property LogFileNameConvert is inconsistent with the database setting
configuration和database 配置不一样建议通过broker来管理database
DGMGRL> edit database orcl set property DbFileNameConvert='/u01/oracle/app/oradata/rhb/,/u01/oracle/oradata/orcl';
Property "dbfilenameconvert" updated
DGMGRL> edit database orcl set property LogFileNameConvert='/u01/oracle/app/oradata/rhb/,/u01/oracle/oradata/orcl';
Property "logfilenameconvert" updated
重启数据库
再次检查configuration状态是否正常
DGMGRL> show configuration

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

Current status for "mybk":
SUCCESS

检查各节点是否正常
show database verbose SID
检查primary属性
重点关注的有
DbFileNameConvert, and LogFileNameConvert，StandbyArchiveLocation 归档路径
LogArchiveFormat 归档文件格式 LogXptMode 传输模式
备库要检查是否正常即可

DGMGRL> switchover to rhb
Performing switchover NOW, please wait...
Operation requires shutdown of instance "orcl" on database "orcl"
Shutting down instance "orcl"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance "rhb" on database "rhb"
Shutting down instance "rhb"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "orcl" on database "orcl"
Starting instance "orcl"...
ORACLE instance started.
Database mounted.
Operation requires startup of instance "rhb" on database "rhb"
Starting instance "rhb"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "rhb"
----------------------------------
如果原primary库没有配置静态监听则会报如下异常
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor
Failed.
You are no longer connected to ORACLE
Please connect again.
Unable to start instance "rhb"
You must start instance "rhb" manually
Switchover succeeded, new primary is "rhb"
------------------------------------------------------------------------
show configuration
show database verbose SID;
查看各节点状态

回到现在的备库添加standby logfile
alter database add standby logfile group 4 '/u01/oracle/oradata/orcl/redo4.rdo' size 50m;
alter database add standby logfile group 5 '/u01/oracle/oradata/orcl/redo5.rdo' size 50m;
alter database add standby logfile group 6 '/u01/oracle/oradata/orcl/redo6.rdo' size 50m;
alter database add standby logfile group 7 '/u01/oracle/oradata/orcl/redo7.rdo' size 50m;
添加时报错
ORA-01156: recovery in progress may need access to files
Cause: Either media recovery or instance recovery is in progress. It may
need the files this operation is being applied to.
*Action: Wait for recovery to complete.
以上错误表明：此时备库处于recover mode

SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter database add standby logfile group 4 '/u01/oracle/oradata/orcl/redo4.rdo' size 50m;

Database altered.

进入dgmgrl 准备修改属性
show database verbose orcl
Current status for "orcl":
Error: ORA-16766: Redo Apply unexpectedly offline
recover managed standby 后
再次执行
报了警告
Warning: ORA-16826: apply service state is inconsistent with the DelayMins property
DelayMins：指定日志应用的延时
Specifies the number of minutes log apply services will delay applying the archived
redo log files on the standby database.
You can set the delay time window using the DelayMins property which specifies,
in number of minutes, how long log apply services on the standby database needs
to wait before applying a log file received from the primary database. Note that
only log apply services is delayed. Log transport services are not delayed and thus
the primary database data is still well protected by the standby database.

If you want log apply services to ignore the delay and apply the archived redo log
files immediately when they become available, you can use the ApplyNoDelay
property. Setting ApplyNoDelay to YES overrides any delay setting in
DelayMins, and log apply services immediately start applying all available log
files. If you want to use the delay setting again, set the ApplyNoDelay property to
NO. Note that it is not enough to set DelayMins to zero to force log apply services
to start immediately applying log files. When you set DelayMins to zero, all the
log files transmitted to the standby database (after the DelayMins is set) will be
applied on the standby database in their turn without any additional delay, but for
the log files already accumulated on the standby database while the delay setting is
on, log apply services still respect the delay setting and wait until after the delay
period to apply those log files.
日志实时应用则只需设置ApplyNoDelay成yes 就可以覆盖其他的延时设置
如果只是把DelayMins改成0 对于改后传送过来的日志来说也是同样的效果但是对于已累积的日志，
则必须在原先定义的延时后才能apply.
For a physical standby database, while log apply services is in the delayed apply
mode, if you want log apply services to apply a few log files immediately and then
go back to the delayed mode again, you can use the ApplyNext property to specify
the number of log files you want to apply immediately, temporarily overriding the
delay. You cannot use the ApplyNext property on a logical standby database,
because the SQL apply mechanism of a logical standby database is different. A
logical standby database applies transaction by transaction rather than log file by
log file as in the physical standby database.
如果要及时应用某些日志，则可以使用ApplyNext指定要及时应用日志的个数应用完后即实行原有的延时设置
这个只使用物理dataguard

>edit database orcl set property LogXptMode='ASYNC';
重启数据库后即修正

DGMGRL> switchover to orcl
Performing switchover NOW, please wait...
Operation requires shutdown of instance "rhb" on database "rhb"
Shutting down instance "rhb"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance "orcl" on database "orcl"
Shutting down instance "orcl"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "rhb" on database "rhb"
Starting instance "rhb"...
ORACLE instance started.
Database mounted.
Operation requires startup of instance "orcl" on database "orcl"
Starting instance "orcl"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "orcl"
DGMGRL>