11g OCM备考5——DG搭建---参考

1. 创建两台虚拟机
关闭防火墙

192.168.1.170 pri

192.168.1.180 sta

pri上建一数据库woods

2. 网络配置
建议用netmgr配置

pri端

SID_LIST_LISTENER =   (SID_LIST =     (SID_DESC =       (GLOBAL_DBNAME = woods.oracle.com)       (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)       (SID_NAME = woods)     )     (SID_DESC =       (GLOBAL_DBNAME = woods_DGMGRL.oracle.com)       (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)       (SID_NAME = woods)     )   )   LISTENER =   (DESCRIPTION =     (ADDRESS = (PROTOCOL = TCP)(HOST = pri)(PORT = 1521))   )   ADR_BASE_LISTENER = /u01/app/oracle

 

WOODS =   (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = TCP)(HOST = pri)(PORT = 1521))     )     (CONNECT_DATA =       (SERVICE_NAME = woods.oracle.com)     )   )   STANDBY =   (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = TCP)(HOST = sta)(PORT = 1521))     )     (CONNECT_DATA =       (SERVICE_NAME = standby.oracle.com)     )   )

 
sta端

SID_LIST_LISTENER =   (SID_LIST =     (SID_DESC =       (GLOBAL_DBNAME = standby.oracle.com)       (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)       (SID_NAME = standby)     )     (SID_DESC =       (GLOBAL_DBNAME = standby_DGMGRL.oracle.com)       (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)       (SID_NAME = standby)     )   )   LISTENER =   (DESCRIPTION =     (ADDRESS = (PROTOCOL = TCP)(HOST = sta)(PORT = 1521))   )   ADR_BASE_LISTENER = /u01/app/oracle

 

tnsnames.ora两端一模一样

3. 修改pri端的参数和添加standby log

alter database force logging;

alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(woods,standby)';

ALTER SYSTEM SET log_archive_dest_1='LOCATION=/u01/app/oracle/fast_recovery_area VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=woods'

alter system set LOG_ARCHIVE_DEST_2='SERVICE=standby async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby';

alter system set DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/standby/','/u01/app/oracle/oradata/woods/' scope=spfile;

alter system set LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/standby/','/u01/app/oracle/oradata/woods/' scope=spfile;

alter system set STANDBY_FILE_MANAGEMENT=AUTO;

切成归档模式：

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

SQL> ALTER DATABASE ARCHIVELOG;

SQL> ALTER DATABASE OPEN;

添加standby log,要比redo log多一组：

alter database add standby logfile '/u01/app/oracle/oradata/woods/standby01.log' size 50m;

alter database add standby logfile '/u01/app/oracle/oradata/woods/standby02.log' size 50m;

alter database add standby logfile '/u01/app/oracle/oradata/woods/standby03.log' size 50m;

alter database add standby logfile '/u01/app/oracle/oradata/woods/standby04.log' size 50m;

 
4. 传输密码文件和初始化参数文件到sta端

create pfile from spfile;

scp orapwwoods oracle@sta:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwstandby

scp initwoods.ora oracle@sta:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initstandby.ora

5. sta端修改参数，并创建相应路径

修改initstandby.ora，相应路径要创建 *.audit_trail='db' *.compatible='11.2.0.0.0' *.control_files='/u01/app/oracle/oradata/standby/control01.ctl' *.db_block_size=8192 *.db_domain='oracle.com' *.db_file_name_convert='/u01/app/oracle/oradata/woods/','/u01/app/oracle/oradata/standby/' *.db_name='woods' *.db_unique_name='standby' *.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area' *.db_recovery_file_dest_size=4322230272 *.diagnostic_dest='/u01/app/oracle' *.log_archive_config='DG_CONFIG=(woods,standby)' .log_archive_dest_1='LOCATION=/u01/app/oracle/fast_recovery_area VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby' *.log_archive_dest_2='SERVICE=woods async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=woods' *.log_file_name_convert='/u01/app/oracle/oradata/woods/','/u01/app/oracle/oradata/standby/' *.memory_target=930086912 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.standby_file_management='AUTO' *.undo_tablespace='UNDOTBS1'

6. RMAN复制数据库

standby端： create spfile from pfile; startup nomount; woods端： rman target sys/oracle@woods auxiliary sys/oracl@standby duplicate for standby from active database; (duplicate target database for standby from active database;)   duplicate完成后，standby库就是mount状态了 SQL> select OPEN_MODE from v$database; OPEN_MODE -------------------- MOUNTED alter database recover managed standby database disconnect from session; （取消日志应用，用命令：alter database recover managed standby database cancel;）   到这里就算基本配置完成了，下一章介绍broker和switchover等。

1. 打开备库，能够提供实时查询

sta端 

alter database open; SQL> select open_mode from v$database; OPEN_MODE -------------------- READ ONLY WITH APPLY     DGMGRL> show database verbose standby;   Database - standby     Role:            PHYSICAL STANDBY   Intended State:  APPLY-ON   Transport Lag:   0 seconds   Apply Lag:       0 seconds   Real Time Query: ON   Instance(s):     standby

 

2. 启用块修改跟踪

alter database enable block change tracking using file '/home/oracle/blk_chg.tra';   SQL> select status from v$block_change_tracking; STATUS ---------- ENABLED   SQL> select filename from v$block_change_tracking; FILENAME -------------------------------------------------------------------------------- /home/oracle/blk_chg.tra

 

3. convert 使用

将standby转换成快照数据库，可以用来测试用 DGMGRL> CONVERT DATABASE standby to SNAPSHOT STANDBY; GMGRL> show configuration;   Configuration - c1     Protection Mode: MaxPerformance   Databases:     woods   - Primary database     standby - Snapshot standby database   Fast-Start Failover: DISABLED   Configuration Status: SUCCESS   CONVERT DATABASE standby to PHYSICAL STANDBY;

 

4. 主备库切换

switchover

DGMGRL> SWITCHOVER TO standby;   DGMGRL> show configuration;   Configuration - c1     Protection Mode: MaxPerformance   Databases:     standby - Primary database     woods   - Physical standby database   Fast-Start Failover: DISABLED   Configuration Status: SUCCESS   SWITCHOVER TO woods;

 

 

5. 设置归档日志保留策略

rman target / CONFIGURE ARCHIVELOG DELETION POLICY TO shipped to standby; 会有这个报错：RMAN-08591: WARNING: invalid archived log deletion policy alter system set "_log_deletion_policy"=ALL scope=spfile ; 重启一下主库，再重新配置rman参数，就不会报错了

 

6. 配置Fast-start Failover

 

（1）主备库都启动快速闪回 alter database flashback on; （备库在应用日志，启动不了，可以先取消日志应用，启动闪回后再开启日志应用： alter database recover managed standby database cancel; alter database flashback on; alter database recover managed standby database using current logfile disconnect from session;）   （2）EDIT DATABASE woods SET PROPERTY FastStartFailoverTarget='standby'; （3）sta端      [oracle@sta ~]$ dgmgrl sys/oracle      DGMGRL> start observer; （4）DGMGRL> EDIT DATABASE woods SET PROPERTY FastStartFailoverTarget='standby'; DGMGRL> EDIT DATABASE woods SET PROPERTY LogXptMode='SYNC'; DGMGRL> EDIT DATABASE standby SET PROPERTY LogXptMode='SYNC'; DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MaxAvailability; （5）启动 DGMGRL> ENABLE FAST_START FAILOVER; Enabled. DGMGRL> show configuration;   Configuration - c1     Protection Mode: MaxAvailability   Databases:     woods   - Primary database     standby - (*) Physical standby database   Fast-Start Failover: ENABLED   Configuration Status: SUCCESS