扫二维码与项目经理沟通
我们在微信上24小时期待你的声音
解答本文疑问/技术咨询/运营咨询/技术建议/互联网交流
如何进行 11.2.0.4 DG for linux 部署，相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。
让客户满意是我们工作的目标，不断超越客户的期望值来自于我们对这个行业的热爱。我们立志把好的技术通过有效、简单的方式提供给客户，将通过不懈努力成为客户在信息化领域值得信任、有价值的长期合作伙伴，公司提供的服务项目有：申请域名、网络空间、营销软件、网站建设、殷都网站维护、网站推广。
1、oracle数据库 DB的工作示意图
2、oracle dg的三大工作模式及对数据库的需求依赖
3、本次实验的环境
4、oracle DG部署前的工作
在centdgpri机器上部署Oracle 11.2.0.4数据库软件并部署实例，在centdgstd机器上只安装Oracle数据库软件，要求centdgpri和centdgstd机器上的oracle环境即数据库相关的软件部署路径最好保持一致。
5、oracle DG的部署
主库调整，开启归档
archive log list;
shutdown immediate;
startup mount;
alter system set log_archive_dest_1='location=/opt/oracle/arch';
alter database archivelog;
archive log list;
alter database open;
alter system archive log current
主库调整，开启闪回
select force_logging, FLASHBACK_ON from v$database;
alter database force logging;
alter system set DB_RECOVERY_FILE_DEST_SIZE=10g;
alter system set db_recovery_file_dest='/opt/oracle/flash_recovery_area';
alter database FLASHBACK ON;
select force_logging, FLASHBACK_ON from v$database;
主库调整，添加standby logfile日志
set linesize 1000
col member for a50
select * from v$logfile order by 1;
select GROUP# ,BYTES/1024/1024 size_M,STATUS,ARCHIVED from v$log;
alter database add standby logfile group 6 ('/opt/oracle/oradata/redo06.log') size 50m;
alter database add standby logfile group 7 ('/opt/oracle/oradata/redo07.log') size 50m;
alter database add standby logfile group 8 ('/opt/oracle/oradata/redo08.log') size 50m;
alter database add standby logfile group 9 ('/opt/oracle/oradata/redo09.log') size 50m;
alter database add standby logfile group 10 ('/opt/oracle/oradata/redo10.log') size 50m;
主库调整，修改数据库启动pfile文件
orcl.__db_cache_size=327155712 orcl.__java_pool_size=4194304 orcl.__large_pool_size=8388608 orcl.__oracle_base='/opt/oracle'#ORACLE_BASE set from environment orcl.__pga_aggregate_target=314572800 orcl.__sga_target=469762048 orcl.__shared_io_pool_size=0 orcl.__shared_pool_size=117440512 orcl.__streams_pool_size=0 *.audit_file_dest='/opt/oracle/admin/orcl/adump' *.audit_trail='db' *.compatible='11.2.0.4.0' *.control_files='/opt/oracle/oradata/orcl/control01.ctl','/opt/oracle/oradata/orcl/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='orcl' *.diagnostic_dest='/opt/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)' *.log_archive_dest_1='location=/opt/arch' *.memory_target=783286272 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.undo_tablespace='UNDOTBS1'
log_archive_config='DG_CONFIG=(orcl,orcls)' log_archive_dest_1='LOCATION=/opt/oracle/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl' LOG_ARCHIVE_DEST_2='SERVICE=dbstandby LGWR ASYNC VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcls' LOG_ARCHIVE_DEST_STATE_1=ENABLE LOG_ARCHIVE_DEST_STATE_2=ENABLE FAL_SERVER=dbstandby FAL_CLIENT=dbprimary STANDBY_FILE_MANAGEMENT=AUTO *.log_file_name_convert='/opt/oracle/oradata','/opt/oracle/oradata' *.db_file_name_convert='/opt/oracle/oradata','/opt/oracle/oradata' |
主库调整，重新生成spfile
startup pfile='$ORACLE_HOME/dbs/initorcl.ora';
create spfile from memory;
shutdown immediate;
startup;
show parameter spfile;
主库调整，修改监听配置文件listener.ora
LISTENER = (DESCRIPTION_LIST= (DESCRIPTION = (ADDRESS=(PROTOCOL=tcp)(HOST=centdgpri)(PORT=1521)) (ADDRESS=(PROTOCOL=ipc)(KEY= EXTPROC1521))) ) SID_LIST_LISTENER = (SID_LIST= (SID_DESC= (GLOBAL_DBNAME=orcl) (SID_NAME=orcl) (ORACLE_HOME=/opt/oracle/product/11.2.0.4/db) ) (SID_DESC = (GLOBAL_DBNAME = orcl_DGMGRL) (ORACLE_HOME = /opt/oracle/product/11.2.0.4/db) (SID_NAME= orcl) ) ) ADR_BASE_LISTENER = /opt/oracle |
主库调整，修改TNS服务配置文件tnsnames.ora
DBPRIMARY= (DESCRIPTION= (ADDRESS_LIST= (ADDRESS=(PROTOCOL =TCP)(HOST=centdgpri)(PORT=1521)) ) (CONNECT_DATA=(SID=orcl)(SERVER=DEDICATED)) ) ORCL= (DESCRIPTION= (ADDRESS_LIST= (ADDRESS=(PROTOCOL =TCP)(HOST=centdgpri)(PORT=1521)) ) (CONNECT_DATA=(SID=orcl)(SERVER=DEDICATED)) ) DBSTANDBY= (DESCRIPTION= (ADDRESS_LIST= (ADDRESS=(PROTOCOL =TCP)(HOST=centdgstd)(PORT=1521)) ) (CONNECT_DATA=(SID=orcl)(SERVER=DEDICATED)) ) |
备库调整，编辑数据库启动pfile
*.audit_file_dest='/opt/oracle/diag/rdbms/orcl/orcl/adump' *.compatible='11.2.0.4.0' *.control_files='/opt/oracle/oradata/control01.ctl','/opt/oracle/oradata/control02.ctl' *.core_dump_dest='/opt/oracle/diag/rdbms/orcl/orcl/cdump' *.db_block_size=8192 *.db_create_file_dest='/opt/oracle/oradata' *.db_file_multiblock_read_count=16 *.db_name='orcl' *.db_recovery_file_dest='/opt/oracle/flash_recovery_area' *.db_recovery_file_dest_size=10G *.diagnostic_dest='/opt/oracle/diag/rdbms/orcl/orcl/trace' *.dispatchers='(PROTOCOL=TCP) (SERVICE=orcl)' *.job_queue_processes=10 *.log_archive_dest_1='LOCATION=/opt/oracle/arch' *.log_buffer=7356416 # log buffer update *.open_cursors=300 *.optimizer_dynamic_sampling=2 *.optimizer_mode='ALL_ROWS' *.pga_aggregate_target=186M *.plsql_warnings='DISABLE:ALL' # PL/SQL warnings at init.ora *.processes=150 *.query_rewrite_enabled='TRUE' *.remote_login_passwordfile='EXCLUSIVE' *.result_cache_max_size=2880K *.sga_target=560M *.skip_unusable_indexes=TRUE *.undo_management='AUTO' *.undo_tablespace='UNDOTBS1'
log_archive_config='DG_CONFIG=(orcls,orcl)' log_archive_dest_1='LOCATION=/opt/oracle/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcls' LOG_ARCHIVE_DEST_2='SERVICE=dbprimary LGWR ASYNC VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl' LOG_ARCHIVE_DEST_STATE_1=ENABLE LOG_ARCHIVE_DEST_STATE_2=ENABLE FAL_SERVER=dbprimary FAL_CLIENT=dbstandby STANDBY_FILE_MANAGEMENT=AUTO *.log_file_name_convert='/opt/oracle/oradata','/opt/oracle/oradata' *.db_file_name_convert='/opt/oracle/oradata','/opt/oracle/oradata' |
备库调整，编辑监听配置文件listener.ora
LISTENER = (DESCRIPTION_LIST= (DESCRIPTION = (ADDRESS=(PROTOCOL=tcp)(HOST=centdgstd)(PORT=1521)) (ADDRESS=(PROTOCOL=ipc)(KEY= EXTPROC1521))) ) SID_LIST_LISTENER = (SID_LIST= (SID_DESC= (GLOBAL_DBNAME=orcl) (SID_NAME=orcl) (ORACLE_HOME=/opt/oracle/product/11.2.0.4/db) ) (SID_DESC = (GLOBAL_DBNAME = orcls_DGMGRL) (ORACLE_HOME = /opt/oracle/product/11.2.0.4/db) (SID_NAME= orcl) ) ) ADR_BASE_LISTENER = /opt/oracle |
备库调整，修改备库TNS服务配置文件tnsnames.ora
DBPRIMARY= (DESCRIPTION= (ADDRESS_LIST= (ADDRESS=(PROTOCOL =TCP)(HOST=centdgpri)(PORT=1521)) ) (CONNECT_DATA=(SID=orcl)(SERVER=DEDICATED)) ) ORCLS= (DESCRIPTION= (ADDRESS_LIST= (ADDRESS=(PROTOCOL =TCP)(HOST=centdgstd)(PORT=1521)) ) (CONNECT_DATA=(SID=orcl)(SERVER=DEDICATED)) ) DBSTANDBY= (DESCRIPTION= (ADDRESS_LIST= (ADDRESS=(PROTOCOL =TCP)(HOST=centdgstd)(PORT=1521)) ) (CONNECT_DATA=(SID=orcl)(SERVER=DEDICATED)) ) |
6、主库克隆岛备库
主库创建sys密码文件并传送到备库dbs目录
orapwd file=$ORACLE_HOME/dbs/PWDorcl.ora password=WaterH2o entries=40 force=y
备库创建和主库一致的审计目录
主库：
cd $ORACLE_BASE
tar -cvf diag.tar diag/
scp diag.tar centdgstd:/opt/oracle
备库：
mv diag $ORACLE_BASE/
cd $ORACLE_BASE
tar -xvf diag.tar
mkdir -p $ORACLE_BASE/flash_recovery_area
mkdir
-p $ORACLE_BASE/oradata
克隆主库前的TNS服务测试，一定要在主备库均进行测试
tnsping dbprimary
tnsping dbstandby
sqlplus sys/WaterH2o@dbprimary as sysdba
sqlplus sys/WaterH2o@dbstndby as sysdba
主库克隆到备机
rman target sys/WaterH2o@dbprimary
auxiliary sys/WaterH2o@dbstandby
duplicate
target database for standby from active database nofilenamecheck;
7、开启备库的日志同步进程
alter database recover managed standby database
disconnect from session;
8、查看主备库的角色
select db_unique_name,database_role,switchover_status,open_mode from v$database;
9、验证物理DG的数据同步
select switchover_status from v$database; --查看有没有gap的归档日志
主库：
select STATUS, GAP_STATUS from V$ARCHIVE_DEST_STATUS where DEST_ID = 2;
主库：
SQL>select STATUS, GAP_STATUS from V$ARCHIVE_DEST_STATUS where DEST_ID = 2;
STATUS GAP_STATUS
--------- ------------------------
VALID RESOLVABLE GAP
备库：
SQL> select STATUS, GAP_STATUS from V$ARCHIVE_DEST_STATUS where DEST_ID = 2;
STATUS GAP_STATUS
--------- ------------------------
VALID NO
GAP
查看主备库角色及状态
select open_mode,database_role,db_unique_name from v$database;-
主库：
SQL> select open_mode,database_role,db_unique_name from v$database;
OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME
-------------------- ---------------- ------------------------------
READ WRITE PRIMARY orcl
SQL>
备库：
SQL>select open_mode,database_role,db_unique_name from v$database;
OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME
-------------------- ---------------- ------------------------------
READ ONLY WITH APPLY PHYSICAL STANDBY orcl
SQL>
查看主备库的序列号
select max(sequence#) from v$archived_log;
archive log list;
主库：
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
25
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/arch
Oldest online log sequence 24
Next log sequence to archive 26
Current log sequence 26
SQL>
备库：
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
25
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/arch
Oldest online log sequence 17
Next log sequence to archive 0
Current log sequence 25
SQL>
10、DG Broker手工管理配置
DG部署前的主库确认开启闪回
select
flashback_on from v$database;
show parameter dg_broker_start
alter system set dg_broker_start=true;
show parameter
dg_broker_start
主库登录dgmrl客户端
dgmgrl
sys/WaterH2o@dbprimary
创建dgb控制文件（注意一定要进行tnsping TNS服务名测试）
create
configuration my_dgb as primary database is orcl connect identifier is dgb_p;
DGMGRL> create configuration my_dgb as primary database is orcl connect identifier is dbprimary; Configuration "my_dgb" created with primary database "orcl" |
添加备库
DGMGRL> add database orcls as connect identifier is dbstandby maintained as physical; Database "orcls" added |
启用配置文件
DGMGRL> enable configuration; Enabled. DGMGRL> |
验证配置启动状态
DGMGRL> show configuration Configuration - my_dgb Protection Mode: MaxPerformance Databases: orcl - Primary database orcls - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS DGMGRL> |
开启主备库StandbyFileManagement并同步到DGB
SQL>alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=both;
DGMGRL> edit database orcl set property StandbyFileManagement='AUTO';
DGMGRL> edit database orcls set property StandbyFileManagement='AUTO';
DGMGRL> EDIT DATABASE orcl SET PROPERTY LogXptMode='async';
DGMGRL> EDIT DATABASE orcls SET PROPERTY LogXptMode='async';
取消物理DG的两个参数
alter system set fal_server='' scope=both sid='*';
alter system set fal_client='' scope=both sid='*';
11、物理DG 通过Broker手工切换主备库的角色
主备库切换前的角色检查
主库：
SQL> select database_role,switchover_status,open_mode from v$database; DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE ---------------- -------------------- -------------------- PRIMARY TO STANDBY READ WRITE |
备库：
SQL> select database_role,switchover_status,open_mode from v$database; DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE ---------------- -------------------- -------------------- PHYSICAL STANDBY NOT ALLOWED MOUNTED |
DGMGRIL控制台切换主备库
DGMGRL> show configuration Configuration - my_dgb Protection Mode: MaxPerformance Databases: orcl - Primary database orcls - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS DGMGRL> switchover to orcls Performing switchover NOW, please wait... Operation requires a connection to instance "orcl" on database "orcls" Connecting to instance "orcl"... Connected. New primary database "orcls" is opening... Operation requires startup of instance "orcl" on database "orcl" Starting instance "orcl"... ORACLE instance started. Database mounted. Database opened. Switchover succeeded, new primary is "orcls" DGMGRL> DGMGRL> show configuration Configuration - my_dgb Protection Mode: MaxPerformance Databases: orcls - Primary database orcl - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS DGMGRL> |
DGMGRIL控制台手工切换主备库的结果检查
新主库： SQL> select db_unique_name,database_role,switchover_status,open_mode from v$database; DB_UNIQUE_NAME DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE ------------------------------ ---------------- -------------------- -------------------- orcls PRIMARY TO STANDBY READ WRITE 新备库： SQL> select db_unique_name,database_role,switchover_status,open_mode from v$database; DB_UNIQUE_NAME DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE ------------------------------ ---------------- -------------------- -------------------- orcl PHYSICAL STANDBY NOT ALLOWED MOUNTED |
12、启动DG FFS即启动Failvoer Fast Start
启用FFS必须满足的条件
主备库日志同步模式是自动同步
LogXptMode='async'
EDIT DATABASE orcl SET PROPERTY LogXptMode='async';
EDIT DATABASE orcls SET PROPERTY LogXptMode='async';
主备库均开启闪回，目的是启动快速自动恢复数据库
select name,db_unique_name,flashback_on from v$database;
处理standby的备库打开闪回
alter database open read only;
alter database flashback on;
启用主备库的FFS
edit database orcl set property FastStartFailoverTarget=orcls;
edit database orcls set property FastStartFailoverTarget=orcl;
enable fast_start failover
启用结果
13、DG Broker FFS功能测试
主库shutdown abort故障模拟
登录主库发起shutdown abort
备库告警日志提示备库成功接管主库
observer提示角色自动切换
确认角色自动切换结果
主库恢复后启动
observer日志提示主备角色分配
原主库恢复后的主备库角色查看
原主库端查看
原备库端查看
看完上述内容，你们掌握如何进行 11.2.0.4 DG for linux 部署的方法了吗？如果还想学到更多技能或想了解更多相关内容，欢迎关注创新互联行业资讯频道，感谢各位的阅读！
我们在微信上24小时期待你的声音
解答本文疑问/技术咨询/运营咨询/技术建议/互联网交流
微信二维码
移动版官网