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';