12C以上 DG 配置
前期准备 2台服务器：
配置网络 主机名 防火墙 selinux hosts文件
我的环境
oraclelinux 7.4主机名 ：pridb.gsc.com 主库 IP 172.16.0.250
主机名 ：stddb.gsc.com 备库 IP 172.16.0.251主库安装oracle软件 ，安装数据库，配置监听等等
备库安装oracle软件。
DG配置：
一、主库
1、主库设置强制日志，保证所有的操作都记录到日志文件。
[oracle@pridb ~]$ sqlplus / as sysdba SYS@prod>select force_logging from v$database;FORCE_LOGGING
---------------------------------------
NOSYS@prod> alter database force logging; SYS@prod>select force_logging from v$database;FORCE_LOGGING
---------------------------------------
YES启动归档模式SYS@prod>shutdown immediateSYS@prod>startup nomount; SYS@prod>alter database mount ;SYS@prod>alter database archivelog;SYS@prod> archive log list;
Database log mode Archive Mode --改为归档模式了
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 2
Next log sequence to archive 4
Current log sequence 4
2、检查密码文件DG中的密码要想同
[oracle@pridb ~]$ cd $ORACLE_HOME/dbs
[oracle@pridb dbs]$ ls orapwprod
orapwprod
3、准备参数文件
SYS@prod>create pfile from spfile;
注：查一下文件位置
SYS@prod>select name from v$datafile;NAME
--------------------------------------------------------------
/u01/app/oracle/oradata/prod/system01.dbf
/u01/app/oracle/oradata/prod/sysaux01.dbf
/u01/app/oracle/oradata/prod/undotbs01.dbf
/u01/app/oracle/oradata/prod/pdbseed/system01.dbf
/u01/app/oracle/oradata/prod/pdbseed/sysaux01.dbf
/u01/app/oracle/oradata/prod/users01.dbf
/u01/app/oracle/oradata/prod/pdbseed/undotbs01.dbf
/u01/app/oracle/oradata/prod/prodpdb/system01.dbf
/u01/app/oracle/oradata/prod/prodpdb/sysaux01.dbf
/u01/app/oracle/oradata/prod/prodpdb/undotbs01.dbf
/u01/app/oracle/oradata/prod/prodpdb/users01.dbf11 rows selected.SYS@prod>select member from v$logfile;MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/prod/redo03.log
/u01/app/oracle/oradata/prod/redo02.log
/u01/app/oracle/oradata/prod/redo01.log[oracle@pridb dbs]$ cd $ORACLE_HOME/dbs
[oracle@pridb dbs]$ ls
hc_prod.dat init.ora initprod.ora lkPROD orapwprod spfileprod.ora
[oracle@pridb dbs]$ vim initprod.ora prod.__data_transfer_cache_size=0
prod.__db_cache_size=222298112
prod.__inmemory_ext_roarea=0
prod.__inmemory_ext_rwarea=0
prod.__java_pool_size=4194304
prod.__large_pool_size=8388608
prod.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
prod.__pga_aggregate_target=260046848
prod.__sga_target=465567744
prod.__shared_io_pool_size=16777216
prod.__shared_pool_size=197132288
prod.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/prod/adump'
*.audit_trail='db'
*.compatible='12.2.0'
*.control_files='/u01/app/oracle/oradata/prod/control01.ctl','/u01/app/oracle/fast_recovery_area/prod/control02.ctl'
*.db_block_size=8192
*.db_domain='gsc.com'
*.db_name='prod'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area/prod'
*.db_recovery_file_dest_size=12780m
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=prodXDB)'
*.enable_pluggable_database=true
*.local_listener='LISTENER_PROD'
*.memory_target=689m
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'db_unique_name=pridb
--用于dg环境中为每一个数据库指定一个唯一的名称，区别不同的数据库
log_archive_config='dg_config=(pridb,stddb)'
--指定dg环境中，有哪些数据库
log_archive_dest_1='location=/u01/app/oracle/arch/pridb valid_for=(all_logfiles,all_roles) db_unique_name=pridb'
--设置本地的归档路径，路径存放哪些类型的日志
log_archive_dest_2='service=stddb valid_for=(online_logfiles,primary_role) db_unique_name=stddb'
--把主库的所有角色和日志都用stddb的本地服务名的方式传输到远程的 db_unique_name=stddb的数据库上
--指定参数值为ENABLE，允许redo 传输服务传输redo 数据到指定的路径
log_archive_dest_state_1=enable
--指定参数值为ENABLE，允许redo 传输服务传输redo 数据到指定的路径
log_archive_dest_state_2=enable
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
--指定归档文件格式：thread (%t), sequence number (%s), and resetlogs ID (%r).
LOG_ARCHIVE_MAX_PROCESSES=4
--指定归档进程的数量(1-30)，默认值通常是4
fal_server=stddb --指定切换对象
fal_client=pridb
db_file_name_convert='/u01/app/oracle/oradata/stddb/','/u01/app/oracle/oradata/prod/'--指定主库中数据文件的位置，并指定如果到了备库中该存放在哪里，主备数据文件存放路径的对应关系，对方在前，自己在后面log_file_name_convert='/u01/app/oracle/oradata/stddb/','/u01/app/oracle/oradata/prod/'
STANDBY_FILE_MANAGEMENT=AUTO--dg的管理方式，如果primary 数据库数据文件发生修改（如新建，重命名等）
--则按照本参数的设置在standby 中做相应修改。
--设为AUTO 表示自动管理。设为MANUAL表示需要手工管理。
创建相关目录
要用oracle用户创建
[oracle@pridb ~]$ mkdir /u01/app/oracle/arch/pridb -pv 归档目录
[oracle@pridb ~]$
然后生成新的spfile：
SYS@prod>shutdown immediate ;SYS@prod>create spfile from pfile;
4、创建standby redo logfile
主库不需要这个文件，当主库变成备库的时候才使用，个数一般是比现有日志组的数量+1
SYS@prod> alter database add standby logfile group 4 '/u01/app/oracle/oradata/prod/std_redo04.log' size 200Malter database add standby logfile group 5 '/u01/app/oracle/oradata/prod/std_redo05.log' size 200Malter database add standby logfile group 6 '/u01/app/oracle/oradata/prod/std_redo06.log' size 200Malter database add standby logfile group 7 '/u01/app/oracle/oradata/prod/std_redo07.log' size 200M
SYS@prod>select group#,status,used from v$standby_log;GROUP# STATUS USED
---------- ---------- ----------4 UNASSIGNED 05 UNASSIGNED 06 UNASSIGNED 07 UNASSIGNED 0
正常来说要rman备份主库但是也可以不备份。
我就先不备份了
二、备库
备库的SID是stddb
1、准备密码文件参数文件
从主库scp过去的
[oracle@pridb dbs]$ scp initprod.ora orapwprod 172.16.0.251:/u01/app/oracle/product/12.2.0/db_home1/dbs/
发不过去的话oracle用户设置密码
[oracle@stddb dbs]$ cp orapwprod orapwstddb
[oracle@stddb dbs]$ cp initprod.ora initstddb.ora
将prod改成stddb 除了个别的1 stddb.__data_transfer_cache_size=02 stddb.__db_cache_size=2222981123 stddb.__inmemory_ext_roarea=04 stddb.__inmemory_ext_rwarea=05 stddb.__java_pool_size=41943046 stddb.__large_pool_size=83886087 stddb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment8 stddb.__pga_aggregate_target=2600468489 stddb.__sga_target=46556774410 stddb.__shared_io_pool_size=1677721611 stddb.__shared_pool_size=19713228812 stddb.__streams_pool_size=013 *.audit_file_dest='/u01/app/oracle/admin/stddb/adump'14 *.audit_trail='db'15 *.compatible='12.2.0'16 *.control_files='/u01/app/oracle/oradata/stddb/control01.ctl','/u01/app/oracle/fast_recovery_area/stddb/control02.ctl'17 *.db_block_size=819218 *.db_domain='gsc.com'19 *.db_name='prod'20 *.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area/stddb'21 *.db_recovery_file_dest_size=12780m22 *.diagnostic_dest='/u01/app/oracle'23 *.dispatchers='(PROTOCOL=TCP) (SERVICE=stddbXDB)'24 *.enable_pluggable_database=true25 *.local_listener='LISTENER_PROD'26 *.memory_target=689m27 *.nls_language='AMERICAN'28 *.nls_territory='AMERICA'29 *.open_cursors=30030 *.processes=30031 *.remote_login_passwordfile='EXCLUSIVE'32 *.undo_tablespace='UNDOTBS1'33 34 35 db_unique_name=stddb36 log_archive_config='dg_config=(pridb,stddb)'37 log_archive_dest_1='location=/u01/app/oracle/arch/stddb valid_for=(all_logfiles,all_roles) db_unique_name=stddb'38 log_archive_dest_2='service=pridb valid_for=(online_logfiles,primary_role) db_unique_name=pridb'39 log_archive_dest_state_1=enable40 log_archive_dest_state_2=enable41 LOG_ARCHIVE_FORMAT=%t_%s_%r.arc42 LOG_ARCHIVE_MAX_PROCESSES=443 fal_server=pridb44 fal_client=stddb45 db_file_name_convert='/u01/app/oracle/oradata/prod/','/u01/app/oracle/oradata/stddb/'46 log_file_name_convert='/u01/app/oracle/oradata/prod/','/u01/app/oracle/oradata/stddb/'47 STANDBY_FILE_MANAGEMENT=AUTO主要是后边的stddb和pridb换下
[oracle@stddb dbs]$ cd $ORACLE_HOME/sqlplus/admin
[oracle@stddb admin]$ vim glogin.sql set sqlprompt "&_user@&_connect_identifier>"
set null (null)
define _editor=vi
改下显示
创建目录
用oracle用户创建
[oracle@stddb admin]$ mkdir /u01/app/oracle/arch/stddb -pv 归档目录[oracle@stddb admin]$ mkdir /u01/app/oracle/oradata/stddb/ -pv 数据文件位置[oracle@stddb admin]$ mkdir /u01/app/oracle/fast_recovery_area/stddb -pv 快速恢复区[oracle@stddb admin]$ mkdir /u01/app/oracle/admin/stddb/adump -pv 审计相关目录
进入nomount状态
[oracle@stddb admin]$ sqlplus / as sysdba SYS@stddb>startup nomount; ORACLE instance started.Total System Global Area 725614592 bytes
Fixed Size 8797008 bytes
Variable Size 562037936 bytes
Database Buffers 146800640 bytes
Redo Buffers 7979008 bytesSYS@stddb>
三、主备库网络配置
1、主库添加静态注册
[oracle@pridb ~]$ netmgr
然后就可以看到了
[oracle@pridb dbs]$ cd $ORACLE_HOME/network/admin
[oracle@pridb admin]$ ls
listener.ora samples shrept.lst tnsnames.ora
[oracle@pridb admin]$ vim listener.oraSID_LIST_LISTENER =(SID_LIST =(SID_DESC =(GLOBAL_DBNAME = pridb.gsc.com)(ORACLE_HOME = /u01/app/oracle/product/12.2.0/db_home1)(SID_NAME = prod)))LISTENER =(DESCRIPTION_LIST =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = pridb.gsc.com)(PORT = 1521)))(DESCRIPTION =(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))))ADR_BASE_LISTENER = /u01/app/oracle[oracle@pridb admin]$ lsnrctl reload重新加载和查看状态[oracle@pridb admin]$ lsnrctl statusService "a717ef30b1d6b55ce053fa0010ac530d.gsc.com" has 1 instance(s).Instance "prod", status READY, has 1 handler(s) for this service...
Service "pridb.gsc.com" has 2 instance(s).Instance "prod", status UNKNOWN, has 1 handler(s) for this service...Instance "prod", status READY, has 1 handler(s) for this service...
Service "prodXDB.gsc.com" has 1 instance(s).Instance "prod", status READY, has 1 handler(s) for this service...
Service "prodpdb.gsc.com" has 1 instance(s).Instance "prod", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@pridb admin]$ vim tnsnames.ora STDDBPDB =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.0.251)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = stddb.gsc.com)))
STDDB =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.0.251)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = stddb.gsc.com)))PRODPDB =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = pridb.gsc.com)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = prodpdb.gsc.com)))PROD =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = pridb.gsc.com)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = pridb.gsc.com) ##这个是服务名原来是prod现在是pridb))LISTENER_PROD =(ADDRESS = (PROTOCOL = TCP)(HOST = pridb.gsc.com)(PORT = 1521))[oracle@pridb admin]$ tnsping prodpdb
tnsping等等的测试
2、备库添加静态注册
[oracle@stddb admin]$ vim listener.oraSID_LIST_LISTENER =(SID_LIST =(SID_DESC =(GLOBAL_DBNAME = stddb.gsc.com)(ORACLE_HOME = /u01/app/oracle/product/12.2.0/db_home1)(SID_NAME = stddb)))LISTENER =(DESCRIPTION_LIST =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = stddb.gsc.com)(PORT = 1521))(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))))[oracle@stddb admin]$ lsnrctl reload[oracle@stddb admin]$ lsnrctl status
Service "stddb.gsc.com" has 1 instance(s).Instance "stddb", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@stddb admin]$ vim listener.ora
主库tnsname.ora拷到备库
[oracle@pridb admin]$ scp tnsnames.ora 172.16.0.251:/u01/app/oracle/product/12.2.0/db_home1/network/admin/
这时可以进行测试了 tnsping 等等的，两边都能ping通
四、恢复备库
备库rman
[oracle@stddb admin]$ rman target sys/oracle@prod auxiliary sys/oracle@stddbRecovery Manager: Release 12.2.0.1.0 - Production on Tue Jun 2 11:11:44 2020Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.connected to target database: PROD (DBID=453843032)
connected to auxiliary database: PROD (not mounted)RMAN> duplicate target database for standby from active database;这样就减少了备份的时间而且这个是封装命令基本不会出错，报错了就检查配置
五、测试
1、备库
select instance_name ,status from v$instance;INSTANCE_NAME STATUS
---------------- ------------
stddb MOUNTED备库恢复后是mount状态SYS@stddb>archive log list;没问题的话刷下日志SYS@stddb>select sequence#,applied from v$archived_log order by sequence#;SEQUENCE# APPLIED
---------- ---------7 NO8 NO9 NO10 NOSYS@stddb> alter database recover managed standby database disconnect from session;Database altered.SYS@stddb>select sequence#,applied from v$archived_log order by sequence#; SEQUENCE# APPLIED
---------- ---------7 YES8 YES9 YES10 YES2、主库：
SYS@prod>archive log list;Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/arch/pridb
Oldest online log sequence 6
Next log sequence to archive 8
Current log sequence 8切下日志：
SYS@prod>alter system switch logfile;3、打开备库
如果备库要打开，需要停止日志应用服务，打开的库是read only模式
SYS@stddb>alter database recover managed standby database cancel; Database altered.SYS@stddb> alter database open; Database altered.SYS@stddb> select name,open_mode from v$database;NAME OPEN_MODE
--------- --------------------
PROD READ ONLY
4、启用ADG
对于Oracle11g以上的版本，支持ADG（active dg）物理备库可以在open状态下，启动日志应用服务；10g不可以
SYS@stddb>select name,open_mode from v$database;NAME OPEN_MODE
--------- --------------------
PROD READ ONLYSYS@stddb>alter database recover managed standby database using current logfile disconnect;SYS@stddb>select name,open_mode from v$database;NAME OPEN_MODE
--------- --------------------
PROD READ ONLY WITH APPLY
5、角色查询：
主库角色
SYS@prod>select database_role,protection_mode,protection_level from v$database;DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
---------------- -------------------- --------------------
PRIMARY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE备库角色
SYS@stddb>select database_role,protection_mode,protection_level from v$database;DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
---------------- -------------------- --------------------
PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE6、一个小错误：我这样配置完之后发现备库没有动态注册只有静态注册：
发现是local_listener参数错误。
这个LISTENER_PROD是一个tnsname
查看tnsname.ora文件
LISTENER_PROD =(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.0.251)(PORT = 1521))
果然发现是这个HOST没改 还是主库中的pridb.gsc.com ，因为是直接复制的嘛。现在改成 备库的IP 然后重启实例 重启监听。
这样监听就正常了
然后进行数据测试：
主库备库都连接到prodpdb中 打开HR用户。
主库 [oracle@pridb ~]$ sqlplus hr/hr@172.16.0.250:1521/prodpdb.gsc.com 登录HR@172.16.0.250:1521/prodpdb.gsc.com>create table test1 (id number , name varchar2(20));
创建表
HR@172.16.0.250:1521/prodpdb.gsc.com>insert into test1 values(1,'tom');
插入数据
HR@172.16.0.250:1521/prodpdb.gsc.com>select * from test1;ID NAME
---------- --------------------1 tomHR@172.16.0.250:1521/prodpdb.gsc.com>commit ;
Commit complete.
提交
备库
不添加 不创建 直接查询[oracle@stddb ~]$ sqlplus hr/hr@172.16.0.251:1521/prodpdb.gsc.com 登录HR@172.16.0.251:1521/prodpdb.gsc.com>select * from test1; no rows selected没提交时查询，有表 没数据HR@172.16.0.251:1521/prodpdb.gsc.com>/ID NAME
---------- --------------------1 tom提交后查询能查到数据注意，这个是要在ADG模式下才可以实现的。
以上DG完成。
注意：
1、创建的目录的权限要是oracle 和 oinstall 的。
2、tnanames.ora 和listener.ora 文件的对应关系
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！