Oracle11gDG搭建
Oracle11gDG搭建
一、环境规划
二、搭建过程
1. 主库设置：
2. 备库配置
3. 配置网络
4. 恢复备库
5. 备库进行数据同步
6. 应用日志
三、环境测试
1. 查看主备角色
2. 开始测试
四、总结
1. 问题
2.流程分析
一、环境规划
二、搭建过程
1. 主库设置：
2. 备库配置
3. 配置网络
4. 恢复备库
5. 备库进行数据同步
6. 应用日志
三、环境测试
1. 查看主备角色
2. 开始测试
四、总结
1. 问题
2.流程分析
一、环境规划
系统版本：
CentOS release 6.8 (Final)
Oracle版本：
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
IP规划：
| 序号 | IP | 主机名 | SID | 说明 |
|---|---|---|---|---|
| 1 | 192.168.0.50 | 11g | proe | primary database |
| 2 | 192.168.1.50 | 11gtest | stddb | physical standby database |
其他说明：
系统正常初始化，配置主机名hosts文件。oracle软件已安装。
二、搭建过程
1. 主库设置：
1） 开启强制日志，保证数据库的所有操作都保存在日志中。
SYS@proe>alter database force logging;
Database altered.
SYS@proe>select force_logging from v$database;
FOR
---
YES2）给主库增加standby日志组，数量是比当前日志组数量多一个。正常情况下主库并不会用到这些日志组，只有在主库变成备库的时候才会被使用。
#查看数据库日志组个数和大小
SYS@proe>select group#,bytes/1024/1024 from v$log;
GROUP# BYTES/1024/1024
---------- ---------------
1 50
2 50
3 50
SYS@proe> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/proe/redo03.log
/u01/app/oracle/oradata/proe/redo02.log
/u01/app/oracle/oradata/proe/redo01.log
#添加日志组
SYS@proe>alter database add standby logfile group 4 '/u01/app/oracle/oradata/proe/standby04.log' size 50m;
Database altered.
SYS@proe>alter database add standby logfile group 5 '/u01/app/oracle/oradata/proe/standby05.log' size 50m;
Database altered.
SYS@proe>alter database add standby logfile group 6 '/u01/app/oracle/oradata/proe/standby06.log' size 50m;
Database altered.
SYS@proe>alter database add standby logfile group 7 '/u01/app/oracle/oradata/proe/standby07.log' size 50m;
Database altered.
# 再次查看
SYS@proe>select group#,status,type,member from v$logfile;
GROUP# STATUS TYPE MEMBER
---------- ------- ------- --------------------------------------------------
3 (null) ONLINE /u01/app/oracle/oradata/proe/redo03.log
2 (null) ONLINE /u01/app/oracle/oradata/proe/redo02.log
1 (null) ONLINE /u01/app/oracle/oradata/proe/redo01.log
4 (null) STANDBY /u01/app/oracle/oradata/proe/standby04.log
5 (null) STANDBY /u01/app/oracle/oradata/proe/standby05.log
6 (null) STANDBY /u01/app/oracle/oradata/proe/standby06.log
7 (null) STANDBY /u01/app/oracle/oradata/proe/standby07.log
7 rows selected.
#也可以查看standby_log数据字典（新建的都是未使用的）
SYS@proe>select group#,status,used from v$standby_log;
GROUP# STATUS USED
---------- ---------- ----------
4 UNASSIGNED 0
5 UNASSIGNED 0
6 UNASSIGNED 0
7 UNASSIGNED 0
#需要删除的话命令如下：
SYS@proe>alter database drop standby logfile group 7;3）修改相关参数，和DG相关的参数不多主要就是日志，文件的位置的转换，GAP的处理。可以生成静态参数文件pfile修改也可以在线用alter system set进行修改。这里我用的前者。
#首先根据启动中数据库的spfile生成pfile
SYS@proe>create pfile from spfile;
File created.
#对生成的pfile做好备份
[oracle@11g dbs]$ pwd
/u01/app/oracle/product/11.2.0/db_home1/dbs
[oracle@11g dbs]$ ls
hc_proe.dat initproe.ora lkPROE snapcf_proe.f spfileproe.ora.nbak
init.ora.bak initproe.ora.bak orapwproe spfileproe.ora
[oracle@11g dbs]$ cp initproe.ora initproe.ora.bk
#修改参数文件
[oracle@11g dbs]$ vim initproe.ora
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的数据库上
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
#指定参数值为ENABLE，允许redo 传输服务传输redo 数据到指定的路径
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
#指定切换对象
db_file_name_convert='/u01/app/oracle/oradata/stddb/','/u01/app/oracle/oradata/proe/'
log_file_name_convert='/u01/app/oracle/oradata/stddb/','/u01/app/oracle/oradata/proe/'
#指定主库中数据文件的位置，并指定如果到了备库中该存放在哪里，主备数据文件存放路径的对应关系，对方在前，自己在后面
STANDBY_FILE_MANAGEMENT=AUTO
#dg的管理方式，如果primary 数据库数据文件发生修改（如新建，重命名等）则按照本参数的设置在standby 中做相应修改。设为AUTO 表示自动管理。设为MANUAL表示需要手工管理。4）创建对应目录
[oracle@11g dbs]$ mkdir /u01/app/oracle/arch/pridb -pv5）密码文件
主库与备库密码文件必须一致。如果SID不同那么entries一定要相同。
[oracle@11g dbs]$ orapwd file=orapwdg password=123456 entries=5;
[oracle@11gtest dbs]$ orapwd file=orapwdg password=123456 entries=5;6）主库设置归档
SYS@proe>archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 15
Next log sequence to archive 17
Current log sequence 177）重启数据库应用新的参数文件
SYS@proe>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@proe>create spfile from pfile;
File created.
SYS@proe>startup;
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2253664 bytes
Variable Size 1476398240 bytes
Database Buffers 117440512 bytes
Redo Buffers 7319552 bytes
Database mounted.
Database opened.8）使用RMAN整库备份
RMAN> configure channel device type disk format '/home/oracle/dgback/%d_%I_%s_%p.bkp';
using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/home/oracle/dgback/%d_%I_%s_%p.bkp';
new RMAN configuration parameters are successfully stored
RMAN> configure channel device type disk format '/home/oracle/dgback/%d_%I_%s_%p.bkp';
...
Starting Control File and SPFILE Autobackup at 21-JUL-20
piece handle=/data/backup/eashrdb/eashrdb_control_c-485315595-20200721-02 comment=NONE
Finished Control File and SPFILE Autobackup at 21-JUL-202. 备库配置
1）这里我为从库新建一个SID，重新把密码文件给它
[oracle@11gtest dbs]$ export ORACLE_SID=stddb
[oracle@11gtest dbs]$ mv orapwproe orapwstddb2）设置standby database的参数文件
# 复制主库的参数文件到备库
[root@11g dbs]# scp initproe.ora 11gtest:/u01/app/oracle/product/11.2.0/db_home1/dbs/
#到备库进行修改
[oracle@11gtest dbs]$ mv initproe.ora initstddb.ora
[oracle@11gtest dbs]$ vim initstddb.ora
#参数的意思之前已经说过
[oracle@11gtest dbs]$ cat initstddb.ora
stddb.__db_cache_size=687865856
stddb.__java_pool_size=16777216
stddb.__large_pool_size=33554432
stddb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
stddb.__pga_aggregate_target=520093696
stddb.__sga_target=1090519040
stddb.__shared_io_pool_size=0
stddb.__shared_pool_size=335544320
stddb.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/stddb/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/stddb/control01.ctl','/u01/app/oracle/fast_recovery_area/stddb/control02.ctl'#Restore Controlfile
*.db_block_size=8192
*.db_domain='zzl.com'
*.db_name='stddb'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=stddbXDB)'
*.memory_target=1600126976
*.open_cursors=300
*.processes=1500
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=1655
*.undo_tablespace='UNDOTBS1'
db_unique_name=stddb
log_archive_config='dg_config=(pridb,stddb)'
log_archive_dest_1='location=/u01/app/oracle/arch/stddb valid_for=(all_logfiles,all_roles) db_unique_name=stddb'
log_archive_dest_2='service=stddb valid_for=(online_logfiles,primary_role) db_unique_name=pridb'
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=4
fal_server=pridb
db_file_name_convert='/u01/app/oracle/oradata/pridb/','/u01/app/oracle/oradata/stddb/'
log_file_name_convert='/u01/app/oracle/oradata/pridb/','/u01/app/oracle/oradata/stddb/'
STANDBY_FILE_MANAGEMENT=AUTO3）备库创建相应的路径
[oracle@11gtest dbs]$ mkdir /u01/app/oracle/admin/stddb/adump -pv
[oracle@11gtest dbs]$ mkdir /u01/app/oracle/oradata/stddb
[oracle@11gtest dbs]$ mkdir /u01/app/oracle/arch/stddb -pv4）启动备库实例到nomount
[oracle@11gtest dbs]$ echo $ORACLE_SID
stddb
[oracle@11gtest dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Jul 18 17:40:38 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SYS@stddb>create spfile from pfile;
File created.
SYS@stddb>startup nomount;
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2253664 bytes
Variable Size 1476398240 bytes
Database Buffers 117440512 bytes
Redo Buffers 7319552 bytes3. 配置网络
主备都要进行
1）配置监听
[oracle@11g admin]$ vim listener.ora
[oracle@11g admin]$ cat listener.ora
#istener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_home1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION =
(ADDRESS_LIST=
(ADDRESS = (PROTOCOL = TCP)(HOST = 11g)(PORT = 1521))
)
)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=proe)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_home1)
(SID_NAME=proe)
)
(SID_DESC=
(GLOBAL_DBNAME=stddb)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_home1)
(SID_NAME=stddb)
)
)
ADR_BASE_LISTENER = /u01/app/oracle
#备库监听修改
[oracle@11gtest admin]$ vim listener.ora
[oracle@11gtest admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_home1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION =
(ADDRESS_LIST=
(ADDRESS = (PROTOCOL = TCP)(HOST = 11gtest)(PORT = 1521))
)
)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=proe)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_home1)
(SID_NAME=proe)
)
(SID_DESC=
(GLOBAL_DBNAME=stddb)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_home1)
(SID_NAME=stddb)
)
)
ADR_BASE_LISTENER = /u01/app/oracle2）重载监听
[oracle@11g admin]$ lsnrctl reload
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 21-JUL-2020 13:36:46
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=11g)(PORT=1521)))
The command completed successfully
[oracle@11gtest admin]$ lsnrctl reload
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 18-JUL-2020 17:53:42
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=11gtest)(PORT=1521)))
The command completed successfully3）配置本地服务名
[oracle@11g admin]$ vim tnsnames.ora
[oracle@11g admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_home1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
PROE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 11g )(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = proe)
)
)
STDDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 11gtest )(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stddb)
)
)
[oracle@11gtest admin]$ vim tnsnames.ora
[oracle@11gtest admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_home1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
PROE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 11g )(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = proe)
)
)
STDDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 11gtest )(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stddb)
)
)
4）测试网络连接
[oracle@11gtest admin]$ tnsping proe
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 18-JUL-2020 18:12:19
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 11g)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = proe)))
OK (20 msec)
[oracle@11g dgback]$ tnsping stddb
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 21-JUL-2020 13:44:03
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 11gtest)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = stddb)))
OK (10 msec)
#sqlplus 测试
[oracle@11g dgback]$ sqlplus sys/123456@stddb as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 21 13:45:40 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@stddb>
[oracle@11gtest admin]$ sqlplus sys/123456@proe as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Jul 18 18:14:27 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@proe>4. 恢复备库
[oracle@11g admin]$ rman target sys/123456@proe auxiliary sys/123456@stddb
Recovery Manager: Release 11.2.0.4.0 - Production on Tue Jul 21 14:01:31 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROE (DBID=485315595)
connected to auxiliary database: STDDB (not mounted)
RMAN> duplicate target database for standby;
（*RMAN> duplicate target database for standby nofilenamecheck from active database ;此种方式不需要进行RMAN全备的操作，直接在主库上拉数据。简单但是对主库影响较大不建议*）
Starting Duplicate Db at 21-JUL-20
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=572 device type=DISK
contents of Memory Script:
...
input datafile copy RECID=11 STAMP=1046112247 file name=/u01/app/oracle/oradata/proe/tbs_tran01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=12 STAMP=1046112247 file name=/u01/app/oracle/oradata/proe/tbs1.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=13 STAMP=1046112247 file name=/u01/app/oracle/oradata/proe/trans_tbs1.dbf
Finished Duplicate Db at 21-JUL-20在恢复完成后备库自动启动到mount状态。
[oracle@11gtest dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Jul 18 18:54:41 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@stddb>select status from v$instance;
STATUS
------------
MOUNTED5. 备库进行数据同步
此时备库在mount状态但是日志服务已经启动
1）查看此时日志的应用情况
SYS@stddb>select sequence#,applied from v$archived_log order by sequence#;
SEQUENCE# APPLIED
---------- ---------
23 NO
24 NO #可以看到还没有被应用2）进行数据同步
#此时的数据库处于mount状态，所以先不打开，如果打开需要以read only方式打开。disconnect from session 也不是必须的，但是这个选项可以保证在处理完日志后会话不会夯住。否则需要打开新的会话。
SYS@stddb>alter database recover managed standby database disconnect from session;
Database altered.
3）再次查询此时日志的应用情况
SYS@stddb>select sequence#,applied from v$archived_log order by sequence#;
SEQUENCE# APPLIED
---------- ---------
23 YES
24 YES #此时已经被应用4）备库打开需要先关闭日志应用服务
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
--------- --------------------
PROE READ ONLY
6. 应用日志
对于Oracle11g的版本，支持ADG（active dg）物理备库可以在open状态下，启动日志应用服务；10g不可以
SYS@stddb>select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
PROE READ ONLY
#启动应用日志服务
SYS@stddb>alter database recover managed standby database using current logfile disconnect;
Database altered.
#查看此时状态
SYS@stddb>select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
PROE READ ONLY WITH APPLY三、环境测试
1. 查看主备角色
#备库信息
SYS@stddb>select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY NOT ALLOWED #not allowed是正常的只有在备库切换为主库时才会改变
#主库
SYS@proe>select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PRIMARY TO STANDBY
查看两边SCN是否一致
出现问题不一致，主库要高于备库
因为设置的ARC传输日志方式需要日志进行归档才能传到备库。所以主库多切换几次日志即可。
2. 开始测试
主库：
HR@proe>alter database set standby to maximize availability;
#切换保护模式为最大可用
Database altered.
HR@proe>create table dgtest1 (id int);
Table created.
HR@proe>insert into dgtest1 (select employee_id from employees);
107 rows created.
HR@proe>select count(*) from dgtest1;
COUNT(*)
----------
107
SYS@proe>alter system switch logfile;
# 切换日志
System altered.备库：
HR@stddb>select count(*) from dgtest1;
COUNT(*)
----------
107备库已经同步数据，环境部署成功。
四、总结
1. 问题
在进行备库恢复时报错
根据报错提示，查看当前备库的pfile文件发现db_name是stddb，在DG中主备库的db_name应该一致。而db_unique_name是不一致的用来标识主备库区别。将db_name修改后正常执行。
错误原因，使用vim修改参数文件，直接替换了所有的proe导致db_name改变。
2.流程分析
个人人为较为关键的部分在于参数文件问题，本实验中备库里我直接创建了一个新的ORALCE_SID。也就是备库只有一个oracle软件即可。相当于重新恢复了一个主库在备库中。所以在恢复备库中也可以使用其他恢复方法。把主备参数文件配置正确，恢复一个主库的整库数据即可。