0

实操:12C RAC环境下的ADG同步库搭建

一森咖记 2019-07-24
565

【此为本公众号第20篇文章】

【前言】

《Oracle ADG同步技术,DBA必备的一种“后悔药”》

ADG实操:如何吃下这颗“后悔药”

本公众号上述两篇推文中讲到:“Oracle层面的高可用性架构为RAC,熟悉此架构的朋友都知道,RAC能满足高可用性、高并发访问的要求,但这只体现在计算资源的冗余,数据源还是一个,当数据源出现损坏或异常时,便不能提供数据服务。此情况的一个解决方案是:使用RAC+ADG架构,通过构建ADG standby 库来保障数据层面的冗余。”


大家都知道,因为oracle 是基于share-everything的设计原则,如上面一段文字,RAC环境虽然有多个计算节点,但数据节点还是只有一份。


为了保证数据层的冗余,增强数据服务的SLA服务等级,本文将描述如何在RAC环境下搭建一套ADG环境。

系统信息:

    主库 192.168.0.100  intance_name:ethan主库 192.168.0.101  intance_name:ethanadgOracle_version: 12.2.0.1.0

    主库ASM存储信息

      [root@ethanDB ~]# asmcmd lsdg
      State Type Rebal Sector Logical_Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
      MOUNTED EXTERN N 512 512 4096 4194304 3276800 1832824 0 1832824 0 N ethanDATA/
      MOUNTED EXTERN N 512 512 4096 4194304 512000 467448 0 467448 0 N ARCH/
      MOUNTED EXTERN N 512 512 4096 4194304 51200 7792 0 7792 0 N MGMT/
      MOUNTED NORMAL N 512 512 4096 4194304 30720 29852 10240 9806 0 Y OCR/


      一、 主库primary操作

      1.1 Enable Forced Logging

      Place the primary database in FORCE LOGGING mode.

        SQL> alter database force logging;


        1.2 为了不影响主库运行,直接修改如下参数

          SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(ethan,ethanadg)' scope=both sid='*';         
          SQL> alter system set log_archive_dest_1='LOCATION=+arch \
          VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ethan' scope=spfile;
          SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=ethanadg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ethanadg' scope=both sid='*';

          -- for standby,

          --使用命令是为了不中断主库业务使用,故没有在主库初始参数文件进行设定

            SQL>  alter system set fal_client='ethan' scope=both sid='*';    
            SQL>  alter system set FAL_SERVER='ethanadg' scope=both sid='*';  
            SQL>  alter system set standby_file_management=AUTO scope=both sid='*'
            SQL>  alter system set DB_FILE_NAME_CONVERT='/oradata/ethan/datafile','+ethanDATA/ethan/DATAFILE','/oradata/ethan/tempfile','+ethanDATA/ethan/TEMPFILE' scope=spfile sid='*';  
            SQL>  alter system set LOG_FILE_NAME_CONVERT='/oradata/ethan/onlinelog','+ethanDATA/ethan/ONLINELOG' scope=spfile sid='*';


              SQL> alter database add standby logfile thread 1 group 5 '+ethanDATA' size 200m;
              SQL> alter database add standby logfile thread 1 group 6 '+ethanDATA' size 200m;
              SQL> alter database add standby logfile thread 1 group 7 '+ethanDATA' size 200m;
              SQL> alter database add standby logfile thread 2 group 8 '+ethanDATA' size 200m;
              SQL> alter database add standby logfile thread 2 group 9 '+ethanDATA' size 200m;
              SQL> alter database add standby logfile thread 2 group 10 '+ethanDATA' size 200m;
                SQL> select member,type from v$logfile order by 2;

                 主库和备库均同时设置Tnsnames.ora

                  ethan =                                                        
                  (DESCRIPTION =
                  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.100)(PORT = 1521))
                  (CONNECT_DATA =
                  (SERVER = DEDICATED)
                  (SERVICE_NAME = ethan)
                  )
                  )
                  ethanadg =
                  (DESCRIPTION =
                  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.101)(PORT = 1521))
                  (CONNECT_DATA =
                  (SERVER = DEDICATED)
                  (SID = ethanadg)
                  )
                  )

                  主库生产初始参数文件

                    SQL> create pfile='/tmp/initethanadg.ora' from spfile;

                    主库cp一份密码文件/tmp文件系统

                      [oracle@ethan-rac1 ~]asmcmd
                      ASMCMD> cp pwdethan.259.1002301387 tmp/orapwethanadg
                      copying +ethanDATA/ethan/password/pwdethan.259.1002301387 -> /tmp/pwdethan.259.1002301387


                      scp 将密码文件和参数文件copy到备库standby 备库$ORACLE_HOME/dbs目录

                      备库上$ORACLE_HOME/dbs目录下的修改initethanadg.ora初始参数文件 

                        *.db_unique_name=ethanadg
                        *.LOG_ARCHIVE_CONFIG='DG_CONFIG=(ethanadg,ethan)'
                        *.log_archive_dest_1='LOCATION=/archive/ethan VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ethanadg'
                        *.LOG_ARCHIVE_DEST_2='SERVICE=ethan LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ethan'
                        *.fal_client='ethanadg'
                        *.FAL_SERVER='ethan'
                        *.standby_file_management=AUTO
                        *.DB_FILE_NAME_CONVERT='+ethanDATA/ethan/DATAFILE','/oradata/ethan/datafile','+ethanDATA/ethan/tempfile','/oradata/ethan/tempfile'
                        *.LOG_FILE_NAME_CONVERT='+ethanDATA/ethan/onlinelog','/oradata/ethan/onlinelog'
                        *.log_archive_format='ethan_%t_%s_%r.arc'
                        *.remote_login_passwordfile='EXCLUSIVE'
                        *.PARALLEL_EXECUTION_MESSAGE_SIZE=8192

                        #------多租户pdb cdb都要写
                        *.enable_pluggable_database=true
                        *.DB_FILE_NAME_CONVERT='+ethanDATA/ethan/DATAFILE','/oradata/ethan/datafile','+ethanDATA/ethan/tempfile','/oradata/ethan/tempfile','+ethanDATA/ethan/64A52F53A7693286E053CDA9E80AED76/DATAFILE/','+ethanDATA/ethan/pdbseed/','+ethanDATA/ethan/80A758C64E9223BAE053CABDA8C09E9B/TEMPFILE/','+ethanDATA/ethan/pdbseed/'

                        根据上述备库中的initethanadg.ora初始参数文件创建如下文件目录 

                          [oracle@ethan-standby~]$ mkdir -p oradata/ethan/datafile
                          [oracle@ethan-standby~]$ mkdir -p oradata/ethan/tempfile
                          [oracle@ethan-standby~]$ mkdir -p oradata/ethan/onlinelog
                          [oracle@ethan-standby~]$ mkdir -p oracle/oracle/app/oracle/admin/ethanadg/adump
                          [oracle@ethan-standby~]$ mkdir -p archive/ethan
                          [oracle@ethan-standby~]$ mkdir -p oradata/ethan/CONTROLFILE

                          ## $for pdb only

                            [oracle@ethan-standby~]$ mkdir -p oradata/ethan/pdbseed/  /archive/work

                            在备库上设置静态监

                              [oracle@ethan-standby~]vi oracle/oracle/app/oracle/product/12.2/db_1/network/admin/listener.ora


                              SID_LIST_LISTENER =
                              (SID_LIST =
                              (SID_DESC =
                              (ORACLE_HOME = oracle/oracle/app/oracle/product/12.2/db_1)
                              (SID_NAME = ethanadg)
                              )
                              )
                              LISTENER =
                              (DESCRIPTION_LIST =
                              (DESCRIPTION =
                              (ADDRESS = (PROTOCOL = TCP)(HOST = standby_db)(PORT = 1521))
                              )
                              )

                              启动备库监听

                                [oracle@ethan-standby~]$ lsnrctl start

                                备库启动到nomount状态

                                  SQL> startup nomount ;

                                  主库到备库的数据初始化

                                  以下rman命令,主库或者备库均可,本文在主库执行初始化动作

                                    [oracle@ethan-rac1 ~]$ rman target sys/"XXXXXXXX"@ethan auxiliary sys/"XXXXXXXX"@ethanadg
                                    Recovery Manager: Release 12.2.0.1.0 - Production on Wed Jul 24 12:15:37 2019
                                    Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
                                    connected to target database: ethan (DBID=206149876)
                                    connected to auxiliary database: ethan (not mounted)
                                    run
                                    {
                                    allocate channel c1 type disk;
                                    allocate channel c2 type disk;
                                    allocate channel c3 type disk;
                                    allocate channel c4 type disk;
                                    allocate channel c5 type disk;
                                    allocate channel c6 type disk;
                                    allocate AUXILIARY channel c7 type disk;
                                    allocate AUXILIARY channel c8 type disk;
                                    allocate AUXILIARY channel c9 type disk;
                                    allocate AUXILIARY channel c10 type disk;
                                    allocate AUXILIARY channel c11 type disk;
                                    allocate AUXILIARY channel c12 type disk;
                                    DUPLICATE TARGET DATABASE
                                    FOR STANDBY
                                    FROM ACTIVE DATABASE
                                    DORECOVER
                                    NOFILENAMECHECK;
                                    release channel c1;
                                    release channel c2;
                                    release channel c3;
                                    release channel c4;
                                    release channel c5;
                                    release channel c6;
                                    release channel c7;
                                    release channel c8;
                                    release channel c9;
                                    release channel c10;
                                    release channel c11;
                                    release channel c12;
                                    }

                                    注意:初始化完成后备库状态为open

                                    在备库上创建spfile文件

                                    单实例备库创建完毕之后并不会生成spfile,需要手动生成

                                      SQL> create spfile from pfile;

                                      创建备库spfile,并重启备库

                                        SQL> startup;


                                        至此,RAC到ADG的DG初始化同步已经完成。

                                        备库上执行如下命令,执行日志应用

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


                                            SQL> select SWITCHOVER_STATUS,DATABASE_ROLE,PROTECTION_MODE,OPEN_MODE FROM v$database;
                                            SWITCHOVER_STATUS DATABASE_ROLE PROTECTION_MODE OPEN_MODE
                                            -------------------- ---------------- -------------------- --------------------
                                            NOT ALLOWED PHYSICAL STANDBY MAXIMUM PERFORMANCE READ ONLY WITH APPLY

                                            手动再主库增加一个数据文件,备库同步增加

                                              SQL> alter tablespace users add datafile '+ethanDATA' size 1g;Tablespace altered.

                                              备库查询

                                                SQL> select file_name from dba_data_files;
                                                FILE_NAME--------------------------------------------------------------------------------
                                                +ethanDATA/ethan/DATAFILE/system.263.1002301401
                                                +ethanDATA/ethan/DATAFILE/sysaux.264.1002301403
                                                +ethanDATA/ethan/DATAFILE/undotbs1.265.1002301405
                                                +ethanDATA/ethan/DATAFILE/undotbs2.267.1002301409
                                                +ethanDATA/ethan/DATAFILE/users.268.1002301409
                                                +ethanDATA/ethan/DATAFILE/users.278.1002363941

                                                取消日志应用命令

                                                  SQL> alter database recover managed standby database cancel;

                                                  主库中检查归档日志的传送至备库的情况

                                                    SQL> SET LINE 200
                                                    SQL> COL DEST_NAME FOR A30
                                                    SQL> select INST_ID,dest_name,error,LOG_SEQUENCE from gv$ARCHIVE_DEST where dest_name ='LOG_ARCHIVE_DEST_2';


                                                    但这个时候存在两个问题:

                                                    1. 是随着主库归档日志的增多,如何进行清理?

                                                    2. 是备库上从主库传送过来的,且已经在备库应用的归档日志,如何进行删除?



                                                    针对问题1的解答:


                                                    在主库rman环境下修改如下参数,作用是主库在数据库备份时,清理那些已经在所有standby库应用了的归档日志的主库所在的归档文件。


                                                    针对问题2的解答:


                                                    通过写定时脚本的方式,清理那些已经12小时前的归档文件,防止归档空间被撑爆

                                                      [oracle@ethan-standby~]$  crontab -l
                                                      0 0,6,12,18 * * * /home/oracle/del_arch/del.sh > /home/oracle/del_arch/del_arch.log 2>&1

                                                        [oracle@ethan-standby~]$ more /home/oracle/del_arch/del.sh
                                                        #!/bin/bash
                                                        export ORACLE_BASE=/oracle/oracle/app/oracle
                                                        export ORACLE_HOME=/oracle/oracle/app/oracle/product/12.2/db_1
                                                        export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH:$HOME/bin
                                                        export LD_LIBRARY_PATH=$ORACLE_HOME/lib
                                                        export ORACLE_SID=ethanadg

                                                        rman target / <<EOF >>/home/oracle/del_arch/del_arch.log

                                                        ########## delete the archivelogs applied in standby_db about 12 hours before the current time point###########
                                                        delete noprompt archivelog all completed before 'sysdate - 12/24';
                                                        #delete noprompt archivelog all;
                                                        exit
                                                        EOF


                                                        至此,RAC环境下搭建ADG环境结束。


                                                        【结语】

                                                            1. 本文简洁描述了12c RAC环境下如何建一套ADG环境的步骤,具有实操性;

                                                            2. 本文的在主库上的配置信息均没有重启主库,为了不影响主库的业务使用;修改的参数生效可放在有业务变更的时候,进行启停库使之生效;

                                                            3. 不管是RAC和单机环境,读者均需重点注意的是一定要制定归档清理规则:1) 是主库上归档文件的清理规则;2) 是备库上的从主库传送来的归档日志的清理;否则,再大的归档空间也顶不住,届时归档空间满会导致主库业务停止数据服务,严重影响业务正常使用,会显得很不专业;感觉和进洗手间没有纸一个感觉,对,就是那个感觉


                                                        【官网参考】

                                                          https://docs.oracle.com/en/database/oracle/oracle-database/19/sbydb/creating-oracle-data-guard-physical-standby.html#GUID-AAA6D97B-A345-4825-A320-B662BB16E2ED


                                                          最后来个图搞笑的,懂了就懂了,不懂可以问我啊。



                                                          如果大家觉得此文有帮助,欢迎关注个人微信公众号;

                                                          长按识别二维码或公众号搜索“一森咖记”


                                                          「喜欢文章,快来给作者赞赏墨值吧」
                                                          文章转载自一森咖记,如果涉嫌侵权,请发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

                                                          评论

                                                          关注
                                                          最新发布
                                                          暂无内容,敬请期待...
                                                          数据库资讯
                                                          最新 热门 更多
                                                          本月热门
                                                          近期活动
                                                          全部
                                                          暂无活动,敬请期待...
                                                          相关课程
                                                          全部