RAC+DG架构部署（使用备份集）

最新推荐文章于 2024-03-15 20:17:56 发布

BaldwinSu

最新推荐文章于 2024-03-15 20:17:56 发布

阅读量1.9k

点赞数 2

分类专栏： Data Guard 文章标签：数据库 RAC ADG

本文链接：https://blog.csdn.net/sudahai102448567/article/details/105366115

版权

Data Guard 专栏收录该内容

12 篇文章 3 订阅

订阅专栏

本文章为网络资源学习，自己做笔记使用，如有侵权请联系删除！
概述
RAC安装可以参考我的其他文档
一、DataGuard部署部分
我们已经搭建了RAC环境了，此时我们需要另外一台服务器做standby database，在以下步骤之前，请确保该服务器已经安装好Oracle库软件并且配置好了静态监听。

主库配置及相关操作

1、确认主库处于归档模式

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +DATA
Oldest online log sequence     135
Next log sequence to archive   136
Current log sequence           136

2、如果不是归档模式，必须先调整为归档模式
主库设为FORCE LOGGING模式

SQL> alter database force logging;
SQL> select name,log_mode,force_logging from v$database;
 
NAME      LOG_MODE     FOR
--------- ------------ ---
RAC       ARCHIVELOG   YES

3、查询主库日志文件

SQL> select thread#,group#,members,bytes/1024/1024 from v$log;
 
   THREAD#     GROUP#    MEMBERS BYTES/1024/1024
---------- ---------- ---------- ---------------
         1          1          1              50
         1          2          1              50
         2          3          1              50
         2          4          1              50

4、为主库添加6组standby log文件，以便它们自动被传送到备库。（一般比主库多一组）

SQL> alter database add standby logfile thread 1 group 11 size 50m;
SQL> alter database add standby logfile thread 1 group 12 size 50m;
SQL> alter database add standby logfile thread 1 group 13 size 50m;
SQL> alter database add standby logfile thread 2 group 14 size 50m;
SQL> alter database add standby logfile thread 2 group 15 size 50m;
SQL> alter database add standby logfile thread 2 group 16 size 50m;

5、查询添加的日志文件

  SQL> select group#,thread#,bytes/1024/1024,status from v$standby_log;
 
    GROUP#    THREAD# BYTES/1024/1024 STATUS
---------- ---------- --------------- ----------
        11          1              50 UNASSIGNED
        12          1              50 UNASSIGNED
        13          1              50 UNASSIGNED
        14          2              50 UNASSIGNED
        15          2              50 UNASSIGNED
        16          2              50 UNASSIGNED
 
6 rows selected.

6、修改主库初始化参数文件

SQL> alter system set log_archive_config='DG_CONFIG=(rac,rac_dg)' scope=both sid='*';  ##这里DG_CONFIG填的都是数据库的db_unique_name
SQL> alter system set log_archive_dest_2='SERVICE=rac_dg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=rac_dg' scope=both sid='*';
SQL> alter system set log_archive_format='%t_%s_%r.arc' scope=spfile sid='*';
SQL> alter system set log_archive_max_processes=8 scope=both sid='*';
SQL> alter system set db_file_name_convert='/oradata/rac/','+DATA/rac/datafile' scope=spfile sid='*';
SQL> alter system set log_file_name_convert='/oradata/rac/','+DATA/rac/onlinelog' scope=spfile sid='*';
SQL> alter system set standby_file_management=AUTO scope=both sid='*';
SQL> alter system set fal_server='rac_dg' scope=both sid='*';

修改之后要重启数据库（也可以不重启，主要是db_file_name_convert、log_file_name_convert、log_archive_format都是静态参数）
grid用户执行

srvctl stop database -d 数据库名
srvctl start database -d 数据库名

7、查看配置是否生效，通过下列语句查询

SQL> set linesize 500 pages 0
SQL> col value for a90
SQL> col name for a50
SQL> select name, value from v$parameter where name in ('db_name','db_unique_name','log_archive_config', 'log_archive_dest_1','log_archive_dest_2','log_archive_dest_state_1','log_archive_dest_state_2','remote_login_passwordfile','log_archive_format','log_archive_max_processes','fal_server','db_file_name_convert', 'log_file_name_convert', 'standby_file_management');
db_file_name_convert                               /oradata/rac, +DATA/rac/datafile
log_file_name_convert                              /oradata/rac, +DATA/rac/onlinelog
log_archive_dest_1                                 LOCATION=+DATA
log_archive_dest_2                                 SERVICE=rac_dg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=rac_dg
log_archive_dest_state_1                           enable
log_archive_dest_state_2                           ENABLE
fal_server                                         rac_dg
log_archive_config                                 DG_CONFIG=(rac,rac_dg)
log_archive_format                                 %t_%s_%r.arc
log_archive_max_processes                          8
standby_file_management                            AUTO
remote_login_passwordfile                          EXCLUSIVE
db_name                                            rac
db_unique_name                                     rac
 
14 rows selected.

8、配置主库本地NET服务名（两个节点，包括standby database节点）

[oracle@rac1 ~]$ vi /u01/oracle/app/11.2.0/network/admin/tnsnames.ora 
 RAC =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = rac)
    )
  )
 
 RAC_DG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.10)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = rac_dg)
    )
  )

9、在主库中创建Standby Control File，并上传到standby节点

SQL> alter database create standby controlfile as '/tmp/control_dg.ctl';
[oracle@rac1 ~]$ scp /tmp/control_dg.ctl 192.168.56.10:/oradata/rac/control01.ctl

10、主库做全库备份

[root@rac01 /u01/app/11.2.0/grid/bin]# mkdir -p /u01/oracle/backup
[root@rac01 /u01/app/11.2.0/grid/bin]# chown -R oracle:oinstall /u01/oracle/backup/
RMAN> backup database format '/u01/oracle/backup/rac_%T_%s';
 
Starting backup at 25-MAY-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 instance=rac1 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/rac/datafile/system.256.939291779
input datafile file number=00002 name=+DATA/rac/datafile/sysaux.257.939291779
input datafile file number=00003 name=+DATA/rac/datafile/undotbs1.258.939291779
input datafile file number=00005 name=+DATA/rac/datafile/undotbs2.264.939292069
input datafile file number=00004 name=+DATA/rac/datafile/users.259.939291779
input datafile file number=00006 name=+DATA/rac/datafile/test.411.944053153
input datafile file number=00007 name=+DATA/rac/datafile/test.412.944053495
channel ORA_DISK_1: starting piece 1 at 25-MAY-17
channel ORA_DISK_1: finished piece 1 at 25-MAY-17
piece handle=/u01/oracle/backup/rac_20170525_10 tag=TAG20170525T211359 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:08
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 25-MAY-17
channel ORA_DISK_1: finished piece 1 at 25-MAY-17
piece handle=/u01/oracle/backup/rac_20170525_11 tag=TAG20170525T211359 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 25-MAY-17

11、scp备份集到standby节点
备库创建目录

[oracle@oracle01 ~]$ mkdir /oradata/backup

[oracle@rac1 backup]$ scp /u01/oracle/backup/* 192.168.56.10:/oradata/backup/
oracle@192.168.56.10's password: 
rac_20170525_10                                                100% 1261MB   7.5MB/s   02:48 
rac_20170525_11                                                100%   18MB  18.0MB/s   00:01

12、为standby创建密码文件

[oracle@rac1 ~]$ scp /u01/oracle/app/11.2.0/dbs/orapwrac1 192.168.56.10:/u01/oracle/app/11.2.0/dbs/orapwrac
oracle@192.168.56.10's password: 
orapwrac1                                                      100% 1536     1.5KB/s   00:00

standby database配置部分

1、修改初始化文件如下（千万别忘了db_unique_name和log_file_name_convert、db_file_name_convert）

[oracle@racdg dbs]$ cat /u01/oracle/app/11.2.0/dbs/initrac.ora 
*.audit_file_dest='/u01/oracle/app/admin/rac/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/oradata/rac/control01.ctl'
*.db_block_size=8192
*.db_create_file_dest='/oradata/rac'
*.db_domain=''
*.db_name='rac'
*.db_unique_name='rac_dg'
*.diagnostic_dest='/u01/oracle/app'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=racXDB)'
*.fal_server='rac'
*.log_archive_config='DG_CONFIG=(rac,rac_dg)'
*.log_archive_dest_1='LOCATION=/oradata/arch'
#*.log_archive_dest_2='SERVICE=rac LGWR ASYNC VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=rac OPTIONAL PROPEN=15 MAX_FAILURE=10 NET_TIMEOUT=30'
*.log_archive_dest_2='SERVICE=rac LGWR ASYNC VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=rac'
*.log_archive_dest_state_2='enable'
*.log_file_name_convert='+DATA/rac/onlinelog','/oradata/rac'
*.db_file_name_convert='+DATA/rac/datafile','/oradata/rac'
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=8
*.open_cursors=300
*.pga_aggregate_target=536870912
*.processes=150
*.remote_login_passwordfile='exclusive'
*.standby_file_management='AUTO'
*.sga_target=2147483648
*.undo_tablespace='UNDOTBS1'

2、创建相关目录路径

[oracle@racdg dbs]$ mkdir -p /u01/oracle/app/admin/rac/adump
[oracle@racdg dbs]$ mkdir -p /oradata/rac
[oracle@racdg dbs]$ mkdir -p /oradata/arch

3、创建监听器

[oracle@racdg dbs]$ vi /u01/oracle/app/11.2.0/network/admin/listener.ora 
LISTENER=
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.13.30)(PORT=1521))
      (ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))
SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME=prod.neves.com)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME=prod))
    (SID_DESC=
      (SID_NAME=plsextproc)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
      (PROGRAM=extproc)))

4、配置备库本地NET服务名（与主库一致）

[oracle@racdg dbs]$ cat /u01/oracle/app/11.2.0/network/admin/tnsnames.ora 
 RAC =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.100)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = rac)
    )
  )
 
 rac_dg =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.10)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = rac_dg)
    )
  )

5、备库tnsping测试（确保监听打开）

[oracle@rac1 tmp]$ tnsping rac_dg
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 26-MAY-2017 00:37:30
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 = 192.168.56.10)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = rac_dg)))
OK (0 msec)

[oracle@rac1 tmp]$sqlplus sys/oracle@rac_dg as sysdba

6、主库tnsping测试（确保监听打开）

[oracle@racdg dbs]$ tnsping rac
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 25-MAY-2017 22:32:43
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 = 192.168.56.100)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = rac)))
OK (0 msec)

tnsping验证之后不一定链接上，主备库要通过sqlplus测试

[oracle@racdg dbs]$sqlplus sys/oracle@rac as sysdba

5、备库启动到mount状态

SQL> startup mount;
ORACLE instance started.
 
Total System Global Area 2137886720 bytes
Fixed Size                  2254952 bytes
Variable Size             553650072 bytes
Database Buffers         1577058304 bytes
Redo Buffers                4923392 bytes
Database mounted.

6、使用rman恢复备库

[oracle@racdg ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Thu May 25 22:36:41 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: RAC (DBID=2527273880, not open)
RMAN> catalog start with '/oradata/backup';
using target database control file instead of recovery catalog
searching for all files that match the pattern /oradata/backup
 
List of Files Unknown to the Database
=====================================
File Name: /oradata/backup/rac_20170525_11
File Name: /oradata/backup/rac_20170525_10
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /oradata/backup/rac_20170525_11
File Name: /oradata/backup/rac_20170525_10

RMAN> run {
2> allocate channel ch00 type disk;
3> allocate channel ch01 type disk;
4> allocate channel ch02 type disk;
5> allocate channel ch03 type disk;
6> set newname for datafile 1 to '/oradata/rac/system.dbf';
7> set newname for datafile 2 to '/oradata/rac/sysaux.dbf';
8> set newname for datafile 3 to '/oradata/rac/undotbs1.dbf';
9> set newname for datafile 4 to '/oradata/rac/users.dbf';
10> set newname for datafile 5 to '/oradata/rac/undotbs2.dbf';
11> set newname for datafile 6 to '/oradata/rac/test.dbf';
12> set newname for datafile 7 to '/oradata/rac/test01.dbf';
13> restore database;
14> switch datafile all;
15> release channel ch00;
16> release channel ch01;
17> release channel ch02;
18> release channel ch03;
19> }
 
allocated channel: ch00
channel ch00: SID=148 device type=DISK
 
allocated channel: ch01
channel ch01: SID=24 device type=DISK
 
allocated channel: ch02
channel ch02: SID=149 device type=DISK
 
allocated channel: ch03
channel ch03: SID=25 device type=DISK
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 25-MAY-17
channel ch00: starting datafile backup set restore
channel ch00: specifying datafile(s) to restore from backup set
channel ch00: restoring datafile 00001 to /oradata/rac/system.dbf
channel ch00: restoring datafile 00002 to /oradata/rac/sysaux.dbf
channel ch00: restoring datafile 00003 to /oradata/rac/undotbs1.dbf
channel ch00: restoring datafile 00004 to /oradata/rac/users.dbf
channel ch00: restoring datafile 00005 to /oradata/rac/undotbs2.dbf
channel ch00: restoring datafile 00006 to /oradata/rac/test.dbf
channel ch00: restoring datafile 00007 to /oradata/rac/test01.dbf
channel ch00: reading from backup piece /oradata/backup/rac_20170525_10
channel ch00: piece handle=/oradata/backup/rac_20170525_10 tag=TAG20170525T211359
channel ch00: restored backup piece 1
channel ch00: restore complete, elapsed time: 00:00:45
Finished restore at 25-MAY-17
 
datafile 1 switched to datafile copy
input datafile copy RECID=10 STAMP=944955143 file name=/oradata/rac/system.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=11 STAMP=944955143 file name=/oradata/rac/sysaux.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=12 STAMP=944955143 file name=/oradata/rac/undotbs1.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=13 STAMP=944955143 file name=/oradata/rac/users.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=14 STAMP=944955143 file name=/oradata/rac/undotbs2.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=15 STAMP=944955143 file name=/oradata/rac/test.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=16 STAMP=944955143 file name=/oradata/rac/test01.dbf
released channel: ch00
released channel: ch01
released channel: ch02
released channel: ch03

7、standby开启日志应用

SQL> alter database recover managed standby database using current logfile disconnect from session;

8、备库查询进程状态（注意RFS进程和MRP0进程）

SQL> select process, client_process, sequence#,thread#,status from v$managed_standby;
 
PROCESS   CLIENT_P  SEQUENCE#    THREAD# STATUS
--------- -------- ---------- ---------- ------------
ARCH      ARCH              0          0 CONNECTED
ARCH      ARCH              0          0 CONNECTED
ARCH      ARCH              0          0 CONNECTED
ARCH      ARCH              0          0 CONNECTED
ARCH      ARCH              0          0 CONNECTED
ARCH      ARCH            140          1 CLOSING
ARCH      ARCH             48          2 CLOSING
ARCH      ARCH              0          0 CONNECTED
RFS       ARCH              0          0 IDLE
RFS       ARCH              0          0 IDLE
RFS       LGWR             49          2 IDLE
RFS       UNKNOWN           0          0 IDLE
RFS       UNKNOWN           0          0 IDLE
RFS       UNKNOWN           0          0 IDLE
RFS       UNKNOWN           0          0 IDLE
RFS       LGWR            141          1 IDLE
RFS       UNKNOWN           0          0 IDLE
RFS       UNKNOWN           0          0 IDLE
RFS       UNKNOWN           0          0 IDLE
RFS       UNKNOWN           0          0 IDLE
RFS       UNKNOWN           0          0 IDLE
MRP0      N/A             141          1 APPLYING_LOG
 
22 rows selected.

9、主库查询进程状态（注意LNS进程）

SQL> select process, client_process, sequence#,thread#,status from v$managed_standby;
 
PROCESS   CLIENT_P  SEQUENCE#    THREAD# STATUS
--------- -------- ---------- ---------- ------------
ARCH      ARCH            139          1 CLOSING
ARCH      ARCH              0          0 CONNECTED
ARCH      ARCH            139          1 CLOSING
ARCH      ARCH              0          0 CONNECTED
ARCH      ARCH            140          1 CLOSING
ARCH      ARCH            137          1 CLOSING
ARCH      ARCH            136          1 CLOSING
ARCH      ARCH            137          1 CLOSING
LNS       LNS             141          1 WRITING
 
9 rows selected.

10、对比主备库日志序列号

SQL> select sequence#, first_time, next_time, applied from v$archived_log order by sequence#;
 
 SEQUENCE# FIRST_TIM NEXT_TIME APPLIED
---------- --------- --------- ---------
        44 25-MAY-17 25-MAY-17 YES
        45 25-MAY-17 25-MAY-17 YES
        46 25-MAY-17 25-MAY-17 YES
        47 25-MAY-17 25-MAY-17 YES
        48 25-MAY-17 25-MAY-17 YES
        49 25-MAY-17 25-MAY-17 IN-MEMORY
       136 25-MAY-17 25-MAY-17 YES
       137 25-MAY-17 25-MAY-17 YES
       138 25-MAY-17 25-MAY-17 YES
       139 25-MAY-17 25-MAY-17 YES
       140 25-MAY-17 25-MAY-17 YES
 
11 rows selected.

11、主库手动切换日志

SQL> alter system switch logfile;

12、备库查询日志序列号

SQL> select sequence#, first_time, next_time, applied from v$archived_log order by sequence#;
 
 SEQUENCE# FIRST_TIM NEXT_TIME APPLIED
---------- --------- --------- ---------
        44 25-MAY-17 25-MAY-17 YES
        45 25-MAY-17 25-MAY-17 YES
        46 25-MAY-17 25-MAY-17 YES
        47 25-MAY-17 25-MAY-17 YES
        48 25-MAY-17 25-MAY-17 YES
        49 25-MAY-17 25-MAY-17 YES
       136 25-MAY-17 25-MAY-17 YES
       137 25-MAY-17 25-MAY-17 YES
       138 25-MAY-17 25-MAY-17 YES
       139 25-MAY-17 25-MAY-17 YES
       140 25-MAY-17 25-MAY-17 YES
       141 25-MAY-17 25-MAY-17 IN-MEMORY
 
12 rows selected.

由此得知，standby已经实时应用日志了。

12、Data Guard切换到打开模式，首先需要停止日志应用

SQL> alter database recover managed standby database cancel;
 
Database altered.
 
SQL> alter database open read only;
 
Database altered.
 
SQL> alter database recover managed standby database using current logfile disconnect from session;
 
Database altered.

13、测试操作

在主库新建一张表aa，从备库能实时查询

SQL> create table aa as select * from scott.emp;
 
Table created.

--主备库查询数据一致：
SQL> select count(*) from aa;
 
  COUNT(*)
----------
        14

需要注意的是，数据库只读打开后，只可以进行查询并恢复PRIMARY数据库的日志，但是不能进行修改数据。

至此，rac+dg的搭建部署已经完成。

BaldwinSu

关注

2
点赞
踩
12

收藏

觉得还不错? 一键收藏
2
评论
RAC+DG架构部署（使用备份集）

本文章为网络资源学习，自己做笔记使用，如有侵权请联系删除！概述RAC全称是Real Application Clusters，中文译为“实时应用集群”，是ORACLE甲骨文公司提供的在低成本服务器上构建高可用性数据库系统的解决方案，部署自由，无需购买额外部件，就可以实现多节点的负载均衡和故障转移功能，满足7*24业务不间断的需求，而且，结合“Oracle数据卫士”（OracleData Gua...
复制链接

扫一扫