【干货】生产环境Oracle DG配置

作者: wolfyn | 来源:发表于2019-03-05 12:35 被阅读50次

以下配置除SID和IP地址不是真实生产环境以外，其余均为生产环境配置。

DG原理

老生常谈，DG的原理很简单，就是传输日志然后应用日志。
它的原理图如下：

DG原理图

原理图说明：

日志传输服务将主库产生的日志数据传到从库。
应用服务（Apply Service）验证日志数据，并且更新从库的数据文件。
主数据库的写进程更新数据文件，并不依赖于DataGuard架构。
当网络或者从库故障恢复时，DG自动重传已经被主库归档的日志数据。

一、配置流程图

[主库配置] >[备库配置] >[RMAN恢复] >[测试验证] >[更改归档] >[启停操作]

二、现场环境情况

名称	数据库名	数据库版本	系统版本	虚拟IP	实例名	安装目录
主库	db1	10.2.0.4	rhel72	172.31.31.1	abcd	/u01/app/oracle/product/11.2.0/db_1
灾备库	dbstd	10.2.0.4	rhel72	172.30.110.110	abcd	/u01/app/oracle/product/11.2.0/db_1

三、配置新主机环境

安装数据库(略）
修改参数(新主机)
/etc/sysctl.conf

kernel.shmall = 67108864
kernel.shmmax=53687091199
kernel.shmmni = 4096
kernel.sem = 3010 385280 3010 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default=262144   
net.core.wmem_default=262144     
net.core.wmem_max=1048576
fs.file-max = 6815744
net.core.rmem_max = 4194304
fs.aio-max-nr = 1048576

/etc/security/limits.conf

oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536

/etc/pam.d/login

session required pam_limits.so

创建用户、组和目录(新主机)
创建用户和组

/usr/sbin/groupadd oinstall 
/usr/sbin/groupadd dba 
/usr/sbin/useradd -g oinstall -G dba oracle
passwd oracle

创建目录

mkdir -p /u01/app/oracle/product/11.2.0/db_1
chown -R oracle:oinstall /u01/app/oracle/product/11.2.0/db_1

修改OS版本(新主机)

vi /etc/redhat-release
Red Hat Enterprise Linux AS release 4 (Nahant Update 8)

修改用户配置环境(新主机)

umask 022
TMP=/tmp
TMPDIR=/tmp
PATH=/bin:/usr/bin:/usr/local/bin:/usr/X11R6/bin
LD_LIBRARY_PATH=/usr/lib:/usr/X11R6/lib
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
ORACLE_SID=
LD_LIBRARY_PATH=$ORACLE_HOME/jdk/jre/lib/i386:$ORACLE_HOME/jdk/jre/lib/i386/server:$ORACLE_HOME/rdbms/lib:$ORACLE_HOME/lib:$LD_LIBRARY_PATH
PATH=$ORACLE_HOME/bin:$PATH
NLS_LANG=American_America.ZHS16GBK
export ORACLE_BASE ORACLE_HOME ORACLE_SID LD_LIBRARY_PATH PATH NLS_LANG TMP TMPDIR

备库安装oracle软件(略)
检查依赖包(linux)，缺少的需要安装上。

rpm -q binutils compat-libstdc++ elfutils-libelf elfutils-libelf-devel glibc glibc-common glibc-devel gcc gcc-c++ libaio libaio-devel libgcc libstdc++ libstdc++-devel make sysstat unixODBC unixODBC-devel

yum --enablerepo=rhel-media install binutils compat-libstdc++ elfutils-libelf elfutils-libelf-devel glibc glibc-common glibc-devel gcc gcc-c++ libaio libaio-devel libgcc libstdc++ libstdc++-devel make sysstat unixODBC unixODBC-devel

备库升级到10204
- 建一个测试库
  dbca
- 开始升级

startup upgrade
SPOOL patch.log
@?/rdbms/admin/catupgrd.sql
@?/rdbms/admin/utlrp.sql
SPOOL OFF

四、DG配置

主库ABCD设置为force logging 模式

alter database force logging;

主库abcd设为归档模式(如果已有，可省略)

archive log list;
shutdown immediate
startup mount
alter database archivelog;
archive log list; 
alter database open;

主库ABCD创建参数文件

create pfile from spfile;

主库ABCD上建立的备库控制文件standby.ctl

alter database create standby controlfile as ‘/tmp/rman/standby.ctl’

主库abcd进行RMAN备份

export ORACLE_SID=abcd
rman target /
CONFIGURE CONTROLFILE AUTOBACKUP ON  -------show all查看，如果不为ON，则照此修改
backup as compressed backupset device type disk format '/databackp/rman/%U' current controlfile for Standby; 
#backup device type disk format '/databackp/rman/%U' database plus archivelog; 
backup as compressed backupset device type disk '/databackp/rman/%U' database plus archivelog;
backup device type disk format '/databackp/rman/%U' current controlfile for Standby;

拷贝数据文件

将RMAN备份文件全部上传至备用服务器/tmp/rman目录
将主库pfile和密码文件上传至备库相应位置
将主库控制文件上传至备库相应位置

主库SPFILE修改
- 主库在不停机时执行

alter system set log_archive_config='dg_config=(abcd,abcdstd)' scope=spfile;
alter system set log_archive_dest_1='location=/oradata/arch_data VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=abcd' scope=spfile;
alter system set log_archive_dest_2='service=abcdstd LGWR SYNC NET_TIMEOUT=20 REOPEN=20 AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=abcdstd' scope=spfile;
alter system set log_archive_max_processes=5 scope=spfile;
alter system set log_archive_dest_state_1=ENABLE scope=spfile;
alter system set log_archive_dest_state_2=ENABLE scope=spfile;
alter system set remote_login_passwordfile=EXCLUSIVE scope=spfile;
alter system set fal_server='abcdstd' scope=spfile;
alter system set fal_client='abcd' scope=spfile;
alter system set db_file_name_convert ='abcdstd','abcd' scope=spfile;
alter system set log_file_name_convert='abcdstd','abcd' scope=spfile;
alter system set standby_file_management='AUTO' scope=spfile;
alter system set standby_archive_dest='location=/oradata/arch_data/abcdstdby' scope=spfile;

修改后的主pfile:

*.db_unique_name='abcd'
*.log_archive_config='dg_config=(abcd,abcdstd)'
*.log_archive_dest_1='location=/oradata/arch_data VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=abcd'
*.log_archive_dest_2='service=abcdstd LGWR SYNC NET_TIMEOUT=20 REOPEN=20 AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=abcdstd'
*.log_archive_max_processes=5 
*.remote_login_passwordfile=EXCLUSIVE
*.fal_client='abcd'
*.fal_server='abcdstd'
*.standby_file_management='AUTO'
*.log_archive_dest_state_1=ENABLE
*.log_archive_dest_state_2=ENABLE
*.db_file_name_convert ='abcdstd','abcd'
*.log_file_name_convert='abcdstd','abcd'
*.standby_archive_dest='location=/oradata/arch_data/abcdstdby'

主库创建pfile

create pfile from spfile;

配置主库监听

LISTENER=
        (DESCRIPTION_LIST=
                (DESCRIPTION=
                (ADDRESS_LIST=
                        (ADDRESS=(PROTOCOL=TCP)(HOST=172.31.31.1)(PORT=1521)
                        )
                )
                (ADDRESS_LIST=
                        (ADDRESS=(PROTOCOL=IPC)(KEY=EXPROC))
                        )
                )
        )
SID_LIST_LISTENER=
        (SID_LIST=
                (SID_DESC=
                        (SID_NAME=PLSExtProc)
                        (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
                        (PROGRAM=extproc)
                )
                (SID_DESC=
                        (GLOBAL_DBNAME=abcd)
                        (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
                        (SID_NAME=abcd)
                )
        )

配置主库TNS

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )
abcd =
        (DESCRIPTION =
                (ADDRESS = (PROTOCOL = TCP)(HOST = 172.31.31.1)(PORT = 1521))
                        (CONNECT_DATA =
                                (SERVER = DEDICATED)
                                (SERVICE_NAME = abcd)
                        )
        )
abcdSTD =
       (DESCRIPTION =
             (ADDRESS = (PROTOCOL = TCP)(HOST = 172.30.110.110)(PORT = 1521))
                       (CONNECT_DATA =
                       (SERVER = DEDICATED)
                       (SERVICE_NAME = abcd)
                       )
       )

重启主库监听

lsnrctl reload

修改后的备pfile

*.db_unique_name='abcdstd'
*.log_archive_config='dg_config=(abcdstd,abcd)'
*.log_archive_dest_1='location=/oradata/arch_data VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=abcdstd'
*.log_archive_dest_2='service=abcd LGWR SYNC NET_TIMEOUT=20 REOPEN=20 AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=abcd'
*.log_archive_max_processes=5 
*.remote_login_passwordfile=EXCLUSIVE
*.fal_client='abcdstd'
*.fal_server='abcd'
*.standby_file_management='AUTO'
*.log_archive_dest_state_1=ENABLE
*.log_archive_dest_state_2=ENABLE
*.db_file_name_convert ='abcd','abcdstd'
*.log_file_name_convert='abcd','abcdstd'
*.standby_archive_dest='location=/oradata/arch_data/abcdstdby'

配置备库监听

LISTENER=
        (DESCRIPTION_LIST=
                (DESCRIPTION=
                (ADDRESS_LIST=
                        (ADDRESS=(PROTOCOL=TCP)(HOST=172.30.110.110)(PORT=1521)
                        )
                )
                (ADDRESS_LIST=
                        (ADDRESS=(PROTOCOL=IPC)(KEY=EXPROC))
                        )
                )
        )
SID_LIST_LISTENER=
        (SID_LIST=
                (SID_DESC=
                        (SID_NAME=PLSExtProc)
                        (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
                        (PROGRAM=extproc)
                )
                (SID_DESC=
                        (GLOBAL_DBNAME=abcd)
                        (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
                        (SID_NAME=abcd)
                )
        )

配置备库TNS

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )
abcd =
        (DESCRIPTION =
                (ADDRESS = (PROTOCOL = TCP)(HOST = 172.31.31.1)(PORT = 1521))
                        (CONNECT_DATA =
                                (SERVER = DEDICATED)
                                (SERVICE_NAME = abcd)
                        )
        )
abcdSTD =
       (DESCRIPTION =
             (ADDRESS = (PROTOCOL = TCP)(HOST = 172.30.110.110)(PORT = 1521))
                       (CONNECT_DATA =
                       (SERVER = DEDICATED)
                       (SERVICE_NAME = abcd)
                       )
       )

主备测试监听

tnsping abcd
tnsping abcdstd

五、RMAN恢复备库

export ORACLE_SID=abcd
sqlplus "/as sysdb"
startup nomount

另开一个窗口duplicate数据库

rman target sys/oracle@abcd auxiliary /
RUN
{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
allocate auxiliary channel t1 type disk;
allocate auxiliary channel t2 type disk;
allocate auxiliary channel t3 type disk;
allocate auxiliary channel t4 type disk;
DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel t1;
release channel t2;
release channel t3;
release channel t4;
}

在duplicate数据库完成之后，转到startup nomount的SQL窗口

alter database recover managed Standby database disconnect from session no timeout;
#alter tablespace temp add tempfile '/oradata/db_sys_data/dgtemp' size 30M;
shutdown immediate;

备库启动建立standby redolog

startup nomount;

检查是否有standby redolog的记录，有则删除

检查记录

select 'alter database drop standby logfile group '||group#||';' from v$standby_log;

检查出有记录后，执行删除操作

alter database drop standby logfile group 4;
alter database drop standby logfile group 5;
alter database drop standby logfile group 6;
alter database drop standby logfile group 7;

备库建立standby logfile

删除以前的redo

#alter database drop logfile group 4;
#alter database drop logfile group 5;
#alter database drop logfile group 6;
#alter database drop logfile group 7;

添加新REDO

ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/oradata/db_sys_data/abcdstd/redo04.log') size 512M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/oradata/db_sys_data/abcdstd/redo05.log') size 512M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/oradata/db_sys_data/abcdstd/redo06.log') size 512M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/oradata/db_sys_data/abcdstd/redo07.log') size 512M;

查看备库的standby logfile：

select group#,thread#,sequence#,status from v$standby_log;

主备库中查看存档参数是否正常

#主库abcd：
col dest_name for a30
col error for a20
select dest_name,status,error,target,process from v$archive_dest where rownum<=2;

#备库:
col dest_name for a30
col error for a20
select dest_name,status,error,target,process from v$archive_dest where rownum<=2;

查看主备库保护模式

#查看主库abcd保护模式：
select protection_mode,database_role,protection_level,open_mode from v$database; 

#查看备库保护模式：
select protection_mode,database_role,protection_level,open_mode from v$database;

开启物理备库的实时redo应用

startup nomount;
alter database mount standby database ;
alter database recover managed standby database using current logfile disconnect from session;

验证备库应用了日志

#主库abcd：
alter system switch logfile;

#备库：最后一行APPLIED为YES则表示已经实时应用    
select sequence#,applied from v$archived_log;

六、测试

在主库上新建一张表

create table test_table as select * from dba_users;

在备库上查看此表是否已经存在

sqlplus / as sysdba
alter database recover managed standby database cancel;
alter database open read only;

查询刚才主库上创建的表,如果表存在，那就说明DG已经正常工作。

select * from test_table;

4.备库恢复到数据接收模式

shutdwon immediate;
startup mount;
alter database recover managed standby database using current logfile disconnect from session;

备库检查同步状态，主要查看最后一行APPLIED为YES则表示应经应用

select sequence#,applied from v$archived_log;

七、更改主库RMAN的日志归档方式

When backups of archived redo log files are taken on the primary database:

#备库
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
#主库
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;

八、启停相关操作

DG启动顺序
- 先standby后primary

#standby操作
startup nomount;
alter database mount standby database ;
alter database recover managed standby database using current logfile disconnect from session;
lsnrctl start

#primary操作
startup
lsnrctl start

【干货】生产环境Oracle DG配置

DG原理

一、配置流程图

二、现场环境情况

三、配置新主机环境

四、DG配置

五、RMAN恢复备库

六、测试

七、更改主库RMAN的日志归档方式

八、启停相关操作

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

【Camel】...

IT@程序员猿媛

【学】@Jav...

【干货】生产环境Oracle DG配置

DG原理

一、配置流程图

二、现场环境情况

三、 配置新主机环境

四、DG配置

五、RMAN恢复备库

六、测试

七、更改主库RMAN的日志归档方式

八、启停相关操作

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

【Camel】...

IT@程序员猿媛

【学】@Jav...

三、配置新主机环境