Oracle11G DataGuard搭建记录
Database DB_UNIQUE_NAME Oracle Net Service Name
Primary chicago chicago(动态)
10.19.100.191
Physical standby boston boston(动态)
10.19.100.192
一、物理备库（Physical standby）
1. 确认主库处在归档模式，并开启强制日志
SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 1 Next log sequence to archive 2 Current log sequence 2
SQL> alter database force logging;
2. 为主库添加standby redo
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/oradata/chicago/slog1.rdo') SIZE 50M; ALTER DATABASE ADD STANDBY LOGFILE ('/u01/oradata/chicago/slog2.rdo') SIZE 50M; ALTER DATABASE ADD STANDBY LOGFILE ('/u01/oradata/chicago/slog3.rdo') SIZE 50M; ALTER DATABASE ADD STANDBY LOGFILE ('/u01/oradata/chicago/slog4.rdo') SIZE 50M;
3. 主库配置
无需修改的默认参数：
DB_NAME=chicago DB_UNIQUE_NAME=chicago CONTROL_FILES='/arch1/chicago/control1.ctl', '/arch2/chicago/control2.ctl' REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
需要调整的参数
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)' scope=both; alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=chicago' scope=both; alter system set LOG_ARCHIVE_DEST_2='SERVICE=boston ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=boston' scope=both; alter system set FAL_SERVER=boston scope=both; alter system set DB_FILE_NAME_CONVERT='boston','chicago' scope=spfile; alter system set LOG_FILE_NAME_CONVERT='/boston/','/chicago/' scope=spfile; alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=spfile;
重启主库以便生效
4. 以主库的配置文件为模板，修改成备库的配置文件
CREATE PFILE='/u01/initboston.ora' FROM SPFILE;
并修改,修改部分用红色标出
chicago.__db_cache_size=448790528 chicago.__java_pool_size=4194304 chicago.__large_pool_size=8388608 chicago.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment chicago.__pga_aggregate_target=209715200 chicago.__sga_target=629145600 chicago.__shared_io_pool_size=0 chicago.__shared_pool_size=159383552 chicago.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/boston/adump' *.audit_trail='db' *.compatible='11.2.0.4.0' *.control_files='/u01/oradata/boston/control01.ctl','/u01/oraflash/boston/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_file_name_convert='chicago','boston' *.db_name='chicago' *.db_unique_name=boston *.db_recovery_file_dest='/u01/oraflash' *.db_recovery_file_dest_size=4385144832 *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=chicagoXDB)' *.fal_server=chicago *.log_archive_config='DG_CONFIG=(chicago,boston)' *.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=boston' *.log_archive_dest_2='SERVICE=chicago ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=chicago' *.log_archive_format='%t_%s_%r.dbf' *.log_file_name_convert='/chicago/','/boston/' *.open_cursors=300 *.pga_aggregate_target=209715200 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=629145600 *.standby_file_management='AUTO' *.undo_tablespace='UNDOTBS1'
5. 复制参数文件密码文件到备库上,并创建必要路径
scp initboston.ora 10.19.100.192:/$ORACLE_HOME/dbs/ scp $ORACLE_HOME/dbs/orapwchicago 10.19.100.192:/$ORACLE_HOME/dbs/orapwboston mkdir -p /u01/app/oracle/admin/boston/adump mkdir -p /u01/oraflash/boston/ mkdir -p /u01/oradata/boston mkdir -p /u01/app/oracle/diag/rdbms/boston/boston/trace/
启动备库到nomount并创建配置文件：
startup nomount pfile='/u01/app/oracle/product/11.2.0.4/db_1/dbs/initboston.ora' create spfile from pfile; shutdown immediate; startup nomount
6. 备份主库并用备份恢复备库
主库
rman target / backup format '/u01/backup/controlfile_%U' current controlfile for standby; backup format '/u01/backup/db_%U' database plus archivelog; scp -r /u01/backup 10.19.100.192:/u01/
备库
rman target sys/123456@chicago auxiliary / duplicate target database for standby nofilenamecheck;
开启备库到只读
alter database open read only;
修改备库为恢复管理模式，使备库可以应用主库的redo数据，实现同步：
alter database recover managed standby database disconnect from session; 或 alter database recover managed standby database using current logfile disconnect from session;
7. 检查复制情况
在主库手工切换日志
alter system archive log current;
备库查看日志应用情况
select sequence#,first_time,next_time,applied from v$archived_log order by sequence#; SEQUENCE# FIRST_TIM NEXT_TIME APPLIED ---------- --------- --------- --------- 19 14-JAN-17 14-JAN-17 YES 20 14-JAN-17 14-JAN-17 YES 21 14-JAN-17 14-JAN-17 YES
检查备库DG进程(主要是RFS和MRPn是否存在)
select process,status from v$managed_standby; PROCESS STATUS --------- ------------ ARCH CLOSING ARCH CONNECTED ARCH CONNECTED ARCH CONNECTED RFS IDLE RFS IDLE RFS IDLE RFS IDLE MRP0 WAIT_FOR_LOG
查看日志gap
select * from v$archive_gap; no rows selected
如果存在日志断点，就要到主库中找到这些日志：
select sequence#,name from v$archived_log t1,v$archive_gap t2 where t1.thread#=t2.thread#;
找到这些日志文件之后，将这些文件拷贝到备库，并注册到备库进行应用：
alter database register logfile 'xxx';
检查是否有错误
主库上执行
select error from v$archive_dest where target='STANDBY'; no rows selected
检查standby模式
select database_role,protection_mode,protection_level from v$database; DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL ---------------- -------------------- -------------------- PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
在主库写入一条数据并切换日志
insert into t1 values (999); commit; alter system archive log current;
备库
select * from t1; COL1 ---------- 1 213 999 <----
停止复制并提升备库
alter database recover managed standby database cancel;
shutdown immediate
startup
注意 这里alter database recover managed standby database cancel;只是停止了MRP进程，如果再次打开想恢复同步
只要执行
alter database recover managed standby database disconnect from session;
或
alter database recover managed standby database using current logfile disconnect from session;
就好
查看lag
select to_char(SYSDATE,'yyyymmdd hh24:mi:ss') CTIME,NAME,VALUE,DATUM_TIME
from V$DATAGUARD_STATS
WHERE NAME LIKE '%lag';
二、逻辑备库（Logical standby）
逻辑standby是由物理standby转换来的，也是和主库差1个redo，但是是以SQL的方式进行复制
1. 配置好物理standby并检查DG工作正常
主库
SQL> select database_role,protection_mode,protection_level from v$database; DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL ---------------- -------------------- -------------------- PRIMARY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
备库
SQL> select database_role,protection_mode,protection_level from v$database; DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL ---------------- -------------------- -------------------- PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
检查Primary是否有不支持Logical Standby的对象
SQL> SELECT * FROM DBA_LOGSTDBY_UNSUPPORTED;
no rows selected
检查无索引或无主键表
SELECT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_NOT_UNIQUE WHERE (OWNER, TABLE_NAME) NOT IN (SELECT DISTINCT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED) AND BAD_COLUMN = 'Y';
如果有这些表，但你又不希望为其创建主键或唯一键则可以这么干(性能上考虑)
ALTER TABLE mytab ADD PRIMARY KEY (id, name) RELY DISABLE;
2. 停止Physical Standby上的Redo apply(即停掉MRP进程)
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
3. 修改主库以支持Logical Standby
alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=chicago'; alter system set LOG_ARCHIVE_DEST_3='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=chicago'; alter system set LOG_ARCHIVE_DEST_STATE_3=ENABLE;
4. 主库生成logminer字典
EXECUTE DBMS_LOGSTDBY.BUILD;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS;
5. 把备库从Physical Standby转换为Logical Standby
ALTER DATABASE RECOVER TO LOGICAL STANDBY boston; -- db_name必须和主库不同，此操作会自动修改spfile
如果切不过去请注意主库和备库的archive log的差距是否>1 如果>1 需要再切回去,同步到只差1为止
6. 修改备库参数
SHUTDOWN; STARTUP MOUNT; alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=boston'; alter system set LOG_ARCHIVE_DEST_3='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=boston'; alter system set LOG_ARCHIVE_DEST_STATE_1=enable; alter system set LOG_ARCHIVE_DEST_STATE_2=enable; alter system set LOG_ARCHIVE_DEST_STATE_3=enable; ALTER DATABASE OPEN RESETLOGS
7. 重新应用redo
ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE; select name,open_mode,database_role,protection_mode from v$database; SQL> select name,open_mode,database_role,protection_mode from v$database; NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE --------- -------------------- ---------------- -------------------- BOSTON READ WRITE LOGICAL STANDBY MAXIMUM PERFORMANCE
检查redo应用情况
SQL> select sequence#,first_change#,next_change#,timestamp,applied from dba_logstdby_log; SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# TIMESTAMP APPLIED ---------- ------------- ------------ --------- -------- 114 1087604 1088417 17-JAN-17 YES 115 1088417 1088431 17-JAN-17 YES 116 1088431 1088563 17-JAN-17 YES 117 1088563 1088915 17-JAN-17 YES 118 1088915 1089127 17-JAN-17 YES 119 1089127 1090316 17-JAN-17 YES 120 1090316 1091045 17-JAN-17 YES
注意，logical standby是没有MRP进程的,因为是靠sql的方式提供备份
select process,status from v$managed_standby; PROCESS STATUS --------- ------------ ARCH CLOSING ARCH CLOSING ARCH CONNECTED ARCH CLOSING RFS IDLE RFS IDLE RFS IDLE RFS IDLE
向主库t1表写入数据做测试(注意，LogicalStandby不能用sys用户的表，但是PhysicalStandby是可以的)
SQL> insert into scott.dept values(1,'A','B'); 1 row created. SQL> commit; Commit complete. SQL> alter system archive log current;
查询备库
SQL> select * from scott.dept; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 1 A B
SELECT DEST_ID,STATUS,DESTINATION,ERROR FROM V$ARCHIVE_DEST;
查看lag
select to_char(SYSDATE,'yyyymmdd hh24:mi:ss') CTIME,NAME,VALUE,DATUM_TIME from V$DATAGUARD_STATS WHERE NAME LIKE '%lag';
