12C以上 DG 配置

٩(•̮̮̃•̃)۶

于 2020-06-16 17:14:21 发布

阅读量301

点赞数

分类专栏： oracle

本文链接：https://blog.csdn.net/n_u_l_l_/article/details/106504459

版权

oracle 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

前期准备 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
---------------------------------------
NO

SYS@prod> alter database force logging;	

SYS@prod>select force_logging from v$database;

FORCE_LOGGING
---------------------------------------
YES

启动归档模式

SYS@prod>shutdown immediate

SYS@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.dbf

11 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 200M

alter database add standby logfile group 5 '/u01/app/oracle/oradata/prod/std_redo05.log' size 200M

alter database add standby logfile group 6 '/u01/app/oracle/oradata/prod/std_redo06.log' size 200M

alter 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	       0
	 5 UNASSIGNED	       0
	 6 UNASSIGNED	       0
	 7 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=0
  2 stddb.__db_cache_size=222298112
  3 stddb.__inmemory_ext_roarea=0
  4 stddb.__inmemory_ext_rwarea=0
  5 stddb.__java_pool_size=4194304
  6 stddb.__large_pool_size=8388608
  7 stddb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
  8 stddb.__pga_aggregate_target=260046848
  9 stddb.__sga_target=465567744
 10 stddb.__shared_io_pool_size=16777216
 11 stddb.__shared_pool_size=197132288
 12 stddb.__streams_pool_size=0
 13 *.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=8192
 18 *.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=12780m
 22 *.diagnostic_dest='/u01/app/oracle'
 23 *.dispatchers='(PROTOCOL=TCP) (SERVICE=stddbXDB)'
 24 *.enable_pluggable_database=true
 25 *.local_listener='LISTENER_PROD'
 26 *.memory_target=689m
 27 *.nls_language='AMERICAN'
 28 *.nls_territory='AMERICA'
 29 *.open_cursors=300
 30 *.processes=300
 31 *.remote_login_passwordfile='EXCLUSIVE'
 32 *.undo_tablespace='UNDOTBS1'
 33 
 34 
 35 db_unique_name=stddb
 36 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=enable
 40 log_archive_dest_state_2=enable
 41 LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
 42 LOG_ARCHIVE_MAX_PROCESSES=4
 43 fal_server=pridb
 44 fal_client=stddb
 45 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 bytes

SYS@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.ora


SID_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 status

Service "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.ora

SID_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@stddb

Recovery Manager: Release 12.2.0.1.0 - Production on Tue Jun 2 11:11:44 2020

Copyright (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 NO
	 8 NO
	 9 NO
	10 NO

SYS@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 YES
	 8 YES
	 9 YES
	10 YES

2、主库：

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 ONLY

SYS@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 PERFORMANCE

6、一个小错误：我这样配置完之后发现备库没有动态注册只有静态注册：
在这里插入图片描述

发现是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 tom

HR@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模式下才可以实现的。