oracle data guard搭建

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. 准备工作
[root@node1 ~]# vi /etc/sysctl.conf
kernel.shmmax = 18294967295 //调大
[root@node1 ~]# 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
独占方式
[oracle@node1 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. 密码文件
[oracle@node1 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. 克隆
[oracle@node1 admin]$ rman target sys/oracle@souid auxiliary sys/oracle@staid
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)
        )
[oracle@node1 admin]$ vi tnsnames.ora
根据监听文件添加相应的内容show parameter dg_broker_start查看
确保两个参数都是开启
dg_broker_start                      boolean     TRUE

使用DG BROKER 来配置dataguard
[oracle@node1 admin]$ dgmgrl sys/oracle@souid
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
[oracle@node1 admin]$ dgmgrl sys/oracle@souid
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;