DataGuard服务
日志传输
控制redo数据传输到一个或多个归档位置
日志应用
应用redo数据到standby 数据库，以保持对primary数据库事务一致
角色转换
primary standby
switchover
failover --有可能丢失数据 ------------------------DataGuard 搭建--------------------
primary standy
hostname： node1.oracle.com node1.oracle.com
instancename souid staid
db_unique_name souid staid
db_name souid souid file_nmae /oracle/oradata/souid /oracle/oradata/staid
archive /home/oracle/arch /home/oracle/arch
1. 准备工作
[[email protected] ~]# vi /etc/sysctl.conf
kernel.shmmax = 18294967295 //调大
[[email protected] ~]# sysctl -p
2. 更改参数（主库）
SQL> alter system set db_unique_name=souid scope=spfile;
SQL> alter database force logging;
SQL> alter database flashback on; 前提开归档
SQL> alter system set log_archive_config='DG_CONFIG=(souid,staid)';
SQL> alter system set log_archive_dest_1='location=/home/oracle/arch valid_for=(all_logfiles,all_roles) db_unique_name=souid';
SQL> alter system set log_archive_dest_2='service=staid async valid_for=(online_logfiles,primary_role) db_unique_name=staid';
SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE
SQL> alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE;
SQL> alter system set LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' scope=spfile;
SQL> alter system set LOG_ARCHIVE_MAX_PROCESSES=30;
SQL> alter system set fal_server=staid scope=spfile; //库出现故障后，staid切换成主库
SQL> alter system set fal_client=souid scope=spfile; //库出现故障后，souid切换成备库
SQL> alter system set db_file_name_convert='/oracle/oradata/staid','/oracle/oradata/souid' scope=spfile;
SQL> alter system set log_file_name_convert='/oracle/oradata/staid','/oracle/oradata/souid' scope=spfile;
SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO;
SQL> alter system set local_listener=''; //不使用特殊监听
SQL> alter database add standby logfile;
SQL> alter database add standby logfile;
SQL> alter database add standby logfile;
SQL> alter database add standby logfile;
SQL> alter system set dg_broker_start=true
2. 更改参数（备库）
SQL> create pfile='/home/oracle/initstaid.ora' from spfile;
更改后的内容如下
staid.__db_cache_size=12582912
staid.__java_pool_size=4194304
staid.__large_pool_size=4194304
staid.__oracle_base='/oracle'#ORACLE_BASE set from environment
staid.__pga_aggregate_target=104857600
staid.__sga_target=318767104
staid.__shared_io_pool_size=0
staid.__shared_pool_size=79691776
staid.__streams_pool_size=209715200
*.aq_tm_processes=2
*.audit_file_dest='/oracle/admin/staid/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/oracle/oradata/staid/control01.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='/oracle/oradata/souid','/oracle/oradata/staid'
*.db_name='souid'
*.db_recovery_file_dest='/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4322230272
*.db_unique_name='STAID'
*.dg_broker_start=TRUE
*.diagnostic_dest='/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=staidXDB)'
*.fal_client='STAID'
*.fal_server='SOUID'
*.global_names=TRUE
*.job_queue_processes=10
*.local_listener=''
*.log_archive_config='DG_CONFIG=(staid,souid)'
*.log_archive_dest_1='location=/home/oracle/arch1 valid_for=(all_logfiles,all_roles) db_unique_name=staid'
*.log_archive_dest_2='service=souid async valid_for=(online_logfiles,primary_role) db_unique_name=souid'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=30
*.log_file_name_convert='/oracle/oradata/souid','/oracle/oradata/staid'
*.open_cursors=300
*.parallel_max_servers=20
*.pga_aggregate_target=104857600
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_max_size=629145600
*.sga_target=0
*.shared_pool_reserved_size=0
*.standby_file_management='AUTO'
*.streams_pool_size=209715200
*.undo_retention=3600
*.undo_tablespace='UNDOTBS1'
3. 配置监听和tnsnames.ora
静态监听
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=souid)
(SID_NAME=souid)
(ORACLE_HOME=/oracle/database)
)
(SID_DESC=
(GLOBAL_DBNAME=staid)
(SID_NAME=staid)
(ORACLE_HOME=/oracle/database)
)
) LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1.oracle.com)(PORT = 1521))
) ADR_BASE_LISTENER = /oracle
独占方式
[[email protected] admin]$ vi tnsnames.ora
SOUID =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1.oracle.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = souid)
)
) STAID =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1.oracle.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = staid)
)
)
4. 密码文件
[[email protected] dbs]$ orapwd file=orapwstaid password=oracle ignorecase=y
5. 启动备库到mount状态
SQL> startup nomount pfile='/home/oracle/initstaid.ora';
SQL> create spfile from pfile='/home/oracle/initstaid.ora';
SQL> shutdown immediate;
SQL> startup nomount
6. 克隆
[[email protected] admin]$ rman target sys/[email protected] auxiliary sys/[email protected]
RMAN> duplicate target database for standby nofilenamecheck from active database;
SQL> select status from v$instance;------------------备库到mount状态
SQL> alter database open;
SQL> select open_mode from v$database;------------------只读模式
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE disconnect；
RMAN> configure ARCHIVELOG DELETION POLICY to applied on all standby; 日志同步
----------------定义日志传输模式----------------
SQL> alter system set log_archive_dest_1='location=/home/oracle/arch valid_for=(all_logfiles,all_roles) db_unique_name=souid';
sync: 同步传输 。把主库的操作写入主库的在线日志之前就传送到standby log file。 如果备库使用real-time apply的话，在写入standby log redo后就直接引用备库。
如果备库没有启用read-time apply，则备库的arcn进程会等待主库切换日志时把standby redo 也做一次归档（把归档应用到备库上） async : 异步传输。 是主库在写入主库的在线日志，由LNSn进程发送给备库。备库的RFS进程负责接收数据并写入到standby logfile，后续动作同上。
affrim ： 指定所有redo log文件和 standby redo log 必须同步
noaffrim ： 指定所有redo log文件和 standby redo log 不必须同步 default
1. 添加监听 (SID_DESC=
(GLOBAL_DBNAME=souid_DGMGRL) ------------------给DGMGRL 添加特定静态监听，服务名格式固定
(SID_NAME=souid)
(ORACLE_HOME=/oracle/database)
)
(SID_DESC=
(GLOBAL_DBNAME=staid_DGMGRL) ------------------给DGMGRL 添加特定静态监听，服务名格式固定
(SID_NAME=staid)
(ORACLE_HOME=/oracle/database)
)
[[email protected] admin]$ vi tnsnames.ora
根据监听文件添加相应的内容show parameter dg_broker_start查看
确保两个参数都是开启
dg_broker_start boolean TRUE 使用DG BROKER 来配置dataguard
[[email protected] admin]$ dgmgrl sys/[email protected]
DGMGRL> create configuration dgc1 as primary database is souid connect identifier is souid
DGMGRL> add database staid as connect identifier is staid;
DGMGRL> enable configuration
DGMGRL> show configuration;
Configuration - dgc1 Protection Mode: MaxPerformance //最大性能
Databases:
souid - Primary database
staid - Physical standby database Fast-Start Failover: DISABLED Configuration Status:
SUCCESS
DGMGRL> show configuration verbose;
DGMGRL> show database verbose souid;
DGMGRL> show database verbose staid;
---------------------------将最大性能改为最大保护----------------
最大保护模式
sync lgwr affrim
async arcn noaffrim
[[email protected] admin]$ dgmgrl sys/[email protected]
DGMGRL> edit database souid SET PROPERTY LogXptMode = 'SYNC'
DGMGRL> edit CONFIGURATION SET PROTECTION MODE AS MaxProtection;
主库和备库都要修改
DGMGRL> edit database staid SET PROPERTY LogXptMode = 'SYNC'
DGMGRL> edit CONFIGURATION SET PROTECTION MODE AS MaxProtection; ------------------日常维护-------------
启动顺序 关闭顺序
1. 监听 1.监听
2. 备库 2.主库
3. 主库 3.备库
SQL> startup
SQL> alter database recover managed standby database disconnect from session;
SQL> alter database open read only;
SQL> alter database recover managed standby database using current logfile disconnect;
SQL> select open_mode from v$database; OPEN_MODE
--------------------
READ ONLY WITH APPLY 关闭
主库shutdown immediate SQL> alter database recover managed standby database cancel;
备库停止日志传送
SQL> recover managed standby database cancel;
SQL> alter database open read only;
备份 回到日志传送模式
SQL> recover managed standby database disconnect from session;
备库RFS（remote file service）接收日志清空和MRP应用日志同步 主备库情况
查看备库中已经归档的日志
SQL> select SEQUENCE#,status from v$archived_log;
查看备库，应用redo数据过程
SQL> select message from v$dataguard_status;
查看备库中语句应用的redo
SQL> select * from v$log_history ;
---------------------------swichover-----------------
SQL> select database_role,switchover_status from v$database; 查看主备库
开始将主库切换成备库
SQL> alter database commit to switchover to physical standby with session shutdown;
关闭主库
再将主库打开到mount状态
SQL> alter database recover managed standby database disconnect from session;
SQL> alter database open read only;
SQL> alter database recover managed standby database using current logfile disconnect; 修改为备库
备库切换成主库
SQL> alter database commit to switchover to primary with session shutdown ;(如果有会话连接)
关闭重启即可
如果用dg broker切换
DGMGRL> switchover to staid;
快照库
DGMGRL> convert database souid to snapshot standby; //如果是最大保护模式，不允许备库转成快照库
DGMGRL> edit CONFIGURATION SET PROTECTION MODE AS MaxPerformance; //改成最大性能模式，即可操作
做完操作之后，再将 快照库还原成 备库
DGMGRL> convert database souid to PHYSICAL STANDBY;
将快照库还原成物理备库，那之前的测试都将被删除
-------------------failover------------------
备库
SQL> alter database recover managed standby database cancel; //停止redo传输应用
重启
SQL> alter database recover managed standby database finish; //完结
SQL> alter database commit to switchover to primary with session shutdown ; //切成正常库
关闭重启即可
变为可以读写的库
---------------------FSSO-----------------
故障快速转移
1. 打开flashback on （备库）
SQL> alter database recover managed standby database cancel;
SQL> alter database flashback on
SQL> alter database recover managed standby database disconnect;
2. 开启功能
DGMGRL> enable fast_start failover;
重启configuration
DGMGRL> DISABLE FAST_START FAILOVER
DGMGRL> disable configuration;
DGMGRL> enable configuration;
DGMGRL> enable Fast-Start Failover;
DGMGRL> enable FAST_START FAILOVER ;
开启 FSSO
DGMGRL> enable FAST_START FAILOVER ;
开启并查看终端
DGMGRL> start observer
主库 （异常关机）
SQL> shutdown abort;
查看
DGMGRL> start observer
查看备库状态
SQL> select open_mode,database_role from v$database ;
原主库修复
原主库修复后，将其启动到mount状态
再次查看（这个启动库自动关联到我们的DG环境当中，但是作为备库存在）
DGMGRL> start observer
手工切换
DGMGRL> switchover to souid;
![【MySQL数据库】数据库索引事务1.索引2.事务[图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)