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

2019-03-05 本文已影响50人 wolfyn

以下配置除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的日志归档方式

八、启停相关操作

猜你喜欢

热点阅读

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

DG原理

一、配置流程图

二、现场环境情况

三、 配置新主机环境

四、DG配置

五、RMAN恢复备库

六、测试

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

八、启停相关操作

猜你喜欢

热点阅读

三、配置新主机环境