【oracle数据库安装篇三】Linux6.8单机环境oracle11g容灾ADG搭建

lu9up的数据库笔记

已于 2024-05-13 01:31:35 修改

阅读量638

点赞数 25

分类专栏： oracle小白入门文章标签： oracle 容灾 oracle adg

于 2024-05-04 00:46:47 首次发布

本文链接：https://blog.csdn.net/qq_34595089/article/details/138429060

版权

oracle小白入门专栏收录该内容

8 篇文章 0 订阅

订阅专栏

说明

DataGuard 是在主节点与备用节点间通过日志同步来保证数据的同步，可以实现数据库快速切换与灾难性恢复。用户能够在对主数据库影响很小的情况下，实现主备数据库的同步。

关联文章

【oracle数据库安装篇一】Linux5.6基于LVM安装oracle11gR2单机
 【oracle数据库安装篇二】Linux6.8基于ASM安装oracle11gR2单机
 【oracle数据库安装篇三】Linux6.8单机环境oracle11g容灾ADG搭建
 【oracle数据库安装篇四】Linux6.8安装oracle11gR2之RAC双节点集群（待续）

1 环境规划

配置项	主库	备库
操作系统版本	rhel6.8 x86_64	rhel6.8 x86_64
服务器主机名	oracle11g	oracle11gadg
IP地址	192.168.56.3	192.168.56.5
数据库版本	11.2.0.4.0	11.2.0.4.0
db_name	orcl	orcl
db_unique_name	orcl	orcladg
instance_name	orcl	orcladg
存储方式	ASM	file system
本地磁盘及Oracle软件目录	/u01/app/oracle	/u01/app/oracle

2 前期准备

主节点搭建好主库，备用节点只需要安装数据库软件。为了方便后续配置，需要提前规划好和清楚主库和备库的db_name、db_unique_name、instance_name、数据库文件路径。

例如，确定文件路径：

我的主库存储方式是ASM，需要清楚主库文件路径：

SQL> select name from v$datafile where rownum = 1;
NAME
--------------------------------------------------------------------------------
+DATA/orcl/datafile/system.256.1166397573

SQL> select member from v$logfile where rownum = 1;
MEMBER
--------------------------------------------------------------------------------
+DATA/orcl/onlinelog/group_1.261.1166397623

所以可以确定主库的文件路径为+DATA/orcl，这需要记录下来，后面配置参数需要用到。

备库的存储方式我这里没有像主库一样用ASM，用的是file system。所以需要自己规划好文件路径创建在哪里，一般创建在数据库软件目录下：/u01/app/oracle/oradata/orcladg/$ORACLE_SID。如果也是使用ASM存储方式，则需要提前创建好asm实例。

3 主库配置

3.1 激活归档和强制记录日志

检查归档是否激活：

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/arch
Oldest online log sequence     44
Next log sequence to archive   46
Current log sequence           46

如果Automatic archival为Disabled，则需要将数据库启动到mount状态，然后开启归档：

SQL>shutdown immediate
SQL>startup mount
SQL>alter database archivelog;
SQL>alter database open;

查看是否打开强制记录日志：

SQL> select force_logging from v$database;

FOR
---
NO

开启强制记录日志：

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

3.2 添加Standby日志组

查看主库online日志数量和大小：

SQL> 
set lines 200
col groupno for a10
col member for a50
col THREAD for a10
SELECT to_char(f.group#) groupno , to_char(l.thread#) thread , f.member member , f.type redo_file_type , l.status group_status , f.status member_status , l.bytes/1024/1024 bytes , l.archived archived FROM v$logfile f , v$log l WHERE f.group# = l.group# ORDER BY f.group#, f.member;

GROUPNO    THREAD     MEMBER                                             REDO_FI GROUP_STATUS     MEMBER_      BYTES ARC
---------- ---------- -------------------------------------------------- ------- ---------------- ------- ---------- ---
1          1          +DATA/orcl/onlinelog/group_1.261.1166397623        ONLINE  CURRENT                          50 NO
2          1          +DATA/orcl/onlinelog/group_2.262.1166397623        ONLINE  INACTIVE                         50 YES
3          1          +DATA/orcl/onlinelog/group_3.263.1166397623        ONLINE  INACTIVE                         50 YES

添加Standby日志组，大小与onlinelog一样，数量多一两组：

alter database add standby logfile group 4 '+DATA' size 50m;
alter database add standby logfile group 5 '+DATA' size 50m;
alter database add standby logfile group 6 '+DATA' size 50m;
alter database add standby logfile group 7 '+DATA' size 50m;
alter database add standby logfile group 8 '+DATA' size 50m;

查看

SQL> select group#,THREAD#,archived,status from v$standby_log;

    GROUP#    THREAD# ARC STATUS
---------- ---------- --- ----------
         4          0 YES UNASSIGNED
         5          0 YES UNASSIGNED
         6          0 YES UNASSIGNED
         7          0 YES UNASSIGNED
         8          0 YES UNASSIGNED

3.3 修改DG参数

alter system set log_archive_config='DG_CONFIG=(orcl,orcladg)' scope=both ;
alter system set log_archive_dest_1='LOCATION=/u01/app/oracle/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl' scope=both;
alter system set log_archive_dest_2='SERVICE=orcladg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcladg' scope=both;
alter system set log_archive_dest_state_1=ENABLE scope=both;
alter system set log_archive_dest_state_2=ENABLE scope=both;
alter system set fal_server=orcladg scope=both;
alter system set fal_client=orcl scope=both;
alter system set standby_file_management=auto;
alter system set db_file_name_convert='/u01/app/oracle/oradata/orcladg','+DATA/orcl' scope=spfile;
alter system set log_file_name_convert='/u01/app/oracle/oradata/orcladg','+DATA/orcl' scope=spfile;

参数说明：

log_archive_config：指定Data Guard配置中的两个数据库的唯一名称：orcl（主数据库）和orcladg（备用数据库）。
log_archive_dest_1：定义了第一个归档日志路径，即归档日志将被写入到/u01/app/oracle/arch目录下。这个归档位置对所有日志文件和所有角色（主和备用）都有效，且这些归档日志属于orcl数据库。
log_archive_dest_2：第二个归档日志目标，即归档日志将通过LGWR（日志写入器）进程异步发送到名为orcladg的备用数据库服务上。这个归档位置仅对在线日志文件和主角色有效，且这些归档日志属于orcladg数据库。
log_archive_dest_state_1：启用log_archive_dest_1。
log_archive_dest_state_2：启用log_archive_dest_2。
fal_server：指定了FAL（Fetch Archive Log）服务器。
fal_client：指定了FAL（Fetch Archive Log）客户端。
standby_file_management：设置备用数据库的文件管理为自动模式，备用数据库将自动创建、删除或重命名所需的文件。
db_file_name_convert：当数据文件从主数据库复制到备用数据库时转换文件名。
log_file_name_convert：当日志文件从主数据库复制到备用数据库时转换文件名。

创建静态参数文件：

SQL> create pfile='/home/oracle/pfile/initorcl.ora' from spfile;

File created.

3.4 添加网络服务名

[oracle@oracle11g admin]$ cat $ORACLE_HOME/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle11g)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

ORCLADG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle11gadg)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    (UR = A)
    )
  )

测试联通性：

[oracle@oracle11g ~]$ tnsping orcladg

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 21-APR-2024 21:47:08

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 = oracle11gadg)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) (UR = A)))
OK (10 msec)

3.5 传输参数文件和密码文件到备用节点

[oracle@oracle11g ~]$ scp $ORACLE_HOME/network/admin/tnsnames.ora oracle11gadg:$ORACLE_HOME/network/admin/tnsnames.ora
oracle@oracle11gadg's password:
tnsnames.ora                                                                                                           100%  514     0.5KB/s   00:00

[oracle@oracle11g ~]$ scp $ORACLE_HOME/dbs/orapworcl oracle11gadg:$ORACLE_HOME/dbs/orapworcladg
oracle@oracle11gadg's password:
orapworcl                                                                                                              100% 2048     2.0KB/s   00:00

注意：这个密码文件的格式是orapw$ORACLE_SID，如果备库的实例名和主库不一致的话，密码文件的后缀名也要相应修改。如果密码文件名不对的话，远程登录备库的时候就会报错。我备库的实例名是orcladg，所以密码文件为orapworcladg。

4 备库配置

4.1 环境变量

主要是确认实例名。

export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2/db_1
export ORACLE_SID=orcladg
export LANG=en_US
export PATH=$ORACLE_HOME/bin:$PATH
export TNS_ADMIN=$ORACLE_HOME/network/admin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/local/lib
export TEMP=/tmp
export TMPDIR=/tmp
umask 022
export PATH

4.2 创建数据库文件目录

[oracle@oracle10g oracle]$ mkdir -p /u01/app/oracle/oradata/orcladg/orcladg

[oracle@oracle10g oracle]$ mkdir -p /u01/app/oracle/fast_recovery_area/orcladg

如果你的备库也创建了ASM实例，那么就切换到grid用户，asmcmd命令进去创建目录：

[grid@oracle11g ~]$ asmcmd
ASMCMD> mkdir /data/orcladg
ASMCMD> ls data
ASM/
ORCLADG/

4.3 配置静态监听

由于备库未建库，当备库没有实例无法启动，最多启动到nomount状态，这时动态监听注册的服务状态为BLOCKED，是无法连接的。

为了让主库能远程登录备库，所以需要创建备库的静态监听。

静态注册就是 listener 启动时读取 listener.ora 文件中实例和服务的配置，将
实例和服务注册到监听进程，实例状态为UNKNOWN值时表明此服务是静态注册的，这时实例可能并未启动。

把下面这段监听配置添加到$ORACLE_HOME/network/admin/listener.ora文件中：

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = /u01/app/oracle/product/11.2/db_1)
      (SID_NAME = orcladg)
    )
  )

注意：SID_NAME为备库实例名。GLOBAL_DBNAME为服务名，取什么都可以，需要和tnsnames.ora文件的server_name保持一致。

4.4 配置tnsnames.ora

检查一下从主库传过来的tnsnames.ora配置是否正确：

[oracle@oracle11gadg admin]$ cat tnsnames.ora
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle11g)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

ORCLADG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle11gadg)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    (UR = A)
    )
  )

注意：SERVICE_NAME需要和listener.ora文件的静态监听配置的GLOBAL_DBNAME保持一致。

4.5 修改参数

修改从主库传过来的静态参数文件：

[oracle@oracle11gadg dbs]$ vim $ORACLE_HOME/dbs/initorcladg.ora

orcl.__db_cache_size=352321536
orcl.__java_pool_size=4194304
orcl.__large_pool_size=8388608
orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=335544320
orcl.__sga_target=503316480
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=130023424
orcl.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/orcladg/controlfile/controlfile1.ctl','/u01/app/oracle/oradata/orcladg/controlfile/controlfile2.ctl','/u01/app/oracle/fast_recovery_area/orcladg/controlfile/controlfile3.ctl'#Oracle managed file
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='+DATA/orcl','/u01/app/oracle/oradata/orcladg'
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area/orcladg'
*.db_recovery_file_dest_size=4294967296
*.db_unique_name='ORCLADG'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.fal_client='ORCLADG'
*.fal_server='ORCL'
*.log_archive_config='DG_CONFIG=(orcladg,orcl)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/orcladg/arch'
*.log_archive_dest_2='SERVICE=orcladg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcladg'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_file_name_convert='+DATA/orcl','/u01/app/oracle/oradata/orcladg'
*.memory_target=838860800
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

需要关注或修改的几个参数为：

*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.control_files='/u01/app/oracle/oradata/orcladg/controlfile/controlfile1.ctl','/u01/app/oracle/oradata/orcladg/controlfile/controlfile2.ctl','/u01/app/oracle/fast_recovery_area/orcladg/controlfile/controlfile3.ctl'#Oracle managed file
*.db_file_name_convert='+DATA/orcl','/u01/app/oracle/oradata/orcladg'
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area/orcladg'
*.db_unique_name='ORCLADG'
*.fal_client='ORCLADG'
*.fal_server='ORCL'
*.log_archive_config='DG_CONFIG=(orcladg,orcl)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/orcladg/arch'
*.log_archive_dest_2='SERVICE=orcl LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_file_name_convert='+DATA/orcl','/u01/app/oracle/oradata/orcladg'

注意：db_file_name_convert和log_file_name_convert是和主库相反的。

创建adump目录：

mkdir -p /u01/app/oracle/admin/orcl/adump

4.6 开启备库到nomount状态，并启动网络监听

启动的时候会自动通过参数文件initorcladg.ora启动，此时show parameter spfile可以看到为空。

startup npomount；

验证参数，主要看db_name、db_unique_name和instance_name。

SQL> show parameter name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name               string
db_file_name_convert                 string      +DATA/orcl, /u01/app/oracle/or
                                                 adata
db_name                              string      orcl
db_unique_name                       string      ORCLADG
global_names                         boolean     FALSE
instance_name                        string      orcladg
lock_name_space                      string
log_file_name_convert                string      +DATA/orcl, /u01/app/oracle/or
                                                 adata
processor_group_name                 string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      ORCLADG

启动监听：

lsnrctl stop
lsnrctl start

主备库验证连通性，主备库都执行：

sqlplus sys/oracle@orcl as sysdba
sqlplus sys/oracle@orcladg as sysdba

sys的密码根据实际替换。

5 创建备库

5.1 rman在线远程duplicate主库到备库

[oracle@oracle11g ~]$ rman target sys/oracle@orcl auxiliary sys/oracle@orcladg

RMAN> 
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate auxiliary channel c3 type disk;
allocate auxiliary channel c4 type disk;
duplicate target database for standby from active database;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}

需要同时观察主库和备库的alert日志，看看有没有错误信息。如果有报错，脚本执行到中途会停下来，根据alert日志定位和解决相应问题后，重新执行一遍上述rman脚本即可，直到所有没有输出任何错误。

脚本最后出现Finished Duplicate为成功。

5.2 打开备库

备库创建成功后，此时已经从nomount变为mount状态，打开备库数据库实例：

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> alter database open;

Database altered.

5.3 备库开启日志应用

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

Database altered.

SQL> col name for a50
select name,SEQUENCE#,APPLIED from v$archived_log order by sequence#;

NAME                                                SEQUENCE# APPLIED
-------------------------------------------------- ---------- ---------
/u01/app/oracle/oradata/arch1_39_1166397622.dbf            39 YES
/u01/app/oracle/oradata/arch1_40_1166397622.dbf            40 YES
/u01/app/oracle/oradata/arch1_41_1166397622.dbf            41 YES
/u01/app/oracle/oradata/arch1_42_1166397622.dbf            42 YES
/u01/app/oracle/oradata/arch1_43_1166397622.dbf            43 IN-MEMORY

6 安装后检查

一、查看备库同步情况

SQL> set lines 200
col name for a13
col value for a20
col unit for a30
col TIME_COMPUTED for a30
select name,value,unit,time_computed from v$dataguard_stats where name in ('transport lag','apply lag');

NAME          VALUE                UNIT                           TIME_COMPUTED
------------- -------------------- ------------------------------ ------------------------------
transport lag +00 00:00:00         day(2) to second(0) interval   04/29/2024 15:22:45
apply lag     +00 00:00:00         day(2) to second(0) interval   04/29/2024 15:22:45

二、查看主备库数据库角色

主库：

SQL> select name,database_role from v$database;
NAME      DATABASE_ROLE
--------- ----------------
ORCL      PRIMARY

备库：

SQL> select name,database_role from v$database;
NAME          DATABASE_ROLE
------------- ----------------
ORCL          PHYSICAL STANDBY