正式生产库，配置双节点的RAC + 单实例的 DATAGUARD

最新推荐文章于 2022-03-22 12:08:41 发布

原创最新推荐文章于 2022-03-22 12:08:41 发布 · 8.7k 阅读

本内容遵循CC 4.0 BY-SA版权协议

正式生产库，配置DATAGUARD

RAC+单实例DATAGUARD 配置

RAC 两节点：
192.1.0.101   rac1
192.1.0.102   rac2

存储：ASM
DB_UNIQUE_NAME= racdb
实例：racdb1; racdb2;

DG:
192.1.0.103   dg1
存储：ASM

数据库名： racdb
DB_UNIQUE_NAME=stbdb;

一、系统准备

   1.1 DG 磁盘分区：

   本地磁盘，磁盘分区ASM DISK 对照表

   磁盘分区   磁盘大小   RAID   ASM NAME   ASM GROUPDISKNAME
   -----------------------------------------------------------------
   /dev/sdb
   /dev/sdb1   300       GB   RAID5   RECOVERY1   +RECOVERY
   /dev/sdb2   300       GB   RAID5   RECOVERY2   +RECOVERY
   /dev/sdb3   4023.1 GB   RAID5   RECOVERY3   +RECOVERY

   /dev/sdc1   899.5   MB   RAID0   DATA1       +DATA
   /dev/sdd1   899.5   GB   RAID0   DATA2       +DATA

   /dev/sde1   899.5   GB   RAID0   DATA3       +DATA
   /dev/sdf1   899.5   GB   RAID0   DATA4       +DATA
   /dev/sdg1   899.5   GB   RAID0   DATA5       +DATA

   /dev/sdb 为多个小盘合并成为一个RAID5的磁盘，因考虑到多个小盘（8*146G）多分区不好管理，
   同时这几个多个小盘已使用了2年左右，合并一起做RAID5也可以做个容错处理。



   1.2   做一个SSH对待访问

   #在dg1上执行建立私匙，以方便拷贝文件

   # su - oracle
   $ mkdir ~/.ssh
   $ ssh-keygen -t rsa
   $ ssh-keygen -t dsa

   #将dg1的加入
   $ ssh dg1 cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
   $ ssh dg1 cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys
   $ scp ~/.ssh/authorized_keys dg1:~/.ssh/authorized_keys

   （遇到一个问题：
       [oracle@rac1 .ssh]$ ssh dg1 date
       Warning: the RSA host key for 'dg1' differs from the key for the IP address '192.1.0.103'
       Offending key for IP in /home/oracle/.ssh/known_hosts:15
       Matching host key in /home/oracle/.ssh/known_hosts:17
       Are you sure you want to continue connecting (yes/no)? yes
       2013年 10月 12日星期六 11:39:08 CST
       解决方法：
       查看known_hosts 内容，发现'192.1.0.103' 以前指定了其它的服务器，直接删除对应的其它服务器行。
       问题解决。
   ）
   还有一个有趣的问题，在RAC1中配置了ssh 对等访问，系统会自动更新到其它的RAC节点中，比如我这里有RAC2；

   在单机上先安装一个GRID（ASM),再安装ORACLE.
   1.3 安装GRID
       【】为独立服务器配置ORACLE GRID INFRASTRUCTURE

       安装此项选择，还要选择一个ASM磁盘，是开始时没想到的。后来另外分出两个分区做ASM特征盘，
       有点象RAC中的RAC表决盘(或OCR磁盘）。

安装完成GRID后，查看状态如下：

[grid@dg1 ~]$ crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora.CRS.dg     ora....up.type ONLINE    ONLINE    dg1
ora.DATA.dg    ora....up.type ONLINE    ONLINE    dg1
ora....ER.lsnr ora....er.type ONLINE    ONLINE    dg1
ora....VERY.dg ora....up.type ONLINE    ONLINE    dg1
ora.asm        ora.asm.type   ONLINE    ONLINE    dg1
ora.cssd       ora.cssd.type ONLINE    ONLINE    dg1
ora.diskmon    ora....on.type OFFLINE   OFFLINE
ora.evmd       ora.evm.type   ONLINE    ONLINE    dg1
ora.ons        ora.ons.type   OFFLINE   OFFLINE
[grid@dg1 ~]$

(红色部分为添加的ASM 磁盘组)

   1.4 安装ORACLE

   1.3，1.4 两个安装参考：
   http://blog.csdn.net/miyatang/article/details/6659839



先前测试的配置日志（http://blog.csdn.net/miyatang/article/details/9979579）

二、数据库配置


   2.1 监听/ASM 配置
   2.1.1 listener.ora

   [oracle@localhost admin]$ cat listener.ora
   # listener.ora Network Configuration File: /opt/app/oracle/product/11.2.0/network/admin/listener.ora
   # Generated by Oracle configuration tools.

   LISTENER =
      (DESCRIPTION_LIST =
       (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = 192.1.0.103)(PORT = 1521))
       )
      )

   2.1.2 tnsnames.ora
   [oracle@localhost admin]$ cat tnsnames.ora
   # tnsnames.ora Network Configuration File: /opt/app/oracle/product/11.2.0/network/admin/tnsnames.ora
   # Generated by Oracle configuration tools.

   RACDB1 =
      (DESCRIPTION =
       (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = 192.1.0.101)(PORT = 1521))
          (ADDRESS = (PROTOCOL = TCP)(HOST = 192.1.0.111)(PORT = 1521))
       )
       (CONNECT_DATA =
          (SERVICE_NAME = racdb)
          (INSTANCE_NAME = racdb1)
       )
      )

   RACDB =
      (DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))
       (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = racdb)
       )
      )

   RACDB2 =
      (DESCRIPTION =
       (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = 192.1.0.102)(PORT = 1521))
          (ADDRESS = (PROTOCOL = TCP)(HOST = 192.1.0.112)(PORT = 1521))
       )
       (CONNECT_DATA =
          (SERVICE_NAME = racdb)
          (INSTANCE_NAME = racdb2)
       )
      )

   stbdb =
      (DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.1.0.103)(PORT = 1521))
       (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = stbdb)
       )
      )

   把stbdb 部分插入到两个节点的 tnsnames.ora 文件中。

   2.1.3 grid 用户下，使用asmca 建立 +DATA,+RECOVERY 两个ASM 磁盘组。
       测试查看ASM 磁盘组：
       [grid@localhost grid]$ asmcmd
       Connected to an idle instance.
       ASMCMD> ls
       ASMCMD-8102: no connection to ASM; command requires ASM to run

       修改grid 用户下的.bash_profile

           ORACLE_SID=+ASM; export ORACLE_SID       (原为：ORACLE_SID=+ASM1; 此内容为RAC中拷贝而来有两个实例ASM1,ASM2.)
           PATH=$ORACLE_HOME/bin:$PATH; export PATH


   修改后问题解决：

       [grid@localhost grid]$ asmcmd
       ASMCMD> ls
       CRS/
       DATA/
       RECOVERY/
       ASMCMD> exit

   2.2.配置参数：

       2.2.1、打开Forced Logging模式

       SQL> alter database force logging;
       Database altered.

       2.2.2
       SQL> alter system set log_archive_config='DG_CONFIG=(stbdb,racdb)';
       SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30;
       SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;
       SQL> ALTER SYSTEM SET FAL_SERVER=stbdb;
       SQL> alter system set FAL_CLIENT=racdb;
       SQL> alter system set standby_file_management=auto;

       ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=stbdb LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stbdb' scope=both;
       ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;
       --先设置成不活动的，后面再修改成： ENABLE;
       --ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2 = ENABLE scope=both;

       #添加STANDBY LOGFILE:


       select group#,THREAD#,l.MEMBERS,l.STATUS from v$log l

           GROUP#   THREAD#   MEMBERS   STATUS
       1   1   1   2   INACTIVE
       2   2   1   2   CURRENT
       3   3   2   2   CURRENT
       4   4   2   2   ACTIVE
       5   5   1   2   ACTIVE
       6   6   2   2   ACTIVE

       alter database add STANDBY logfile thread 1 group 7 ;
       alter database add STANDBY logfile thread 1 group 8 ;
       alter database add STANDBY logfile thread 1 group 9 ;

       alter database add STANDBY logfile thread 2 group 10 ;
       alter database add STANDBY logfile thread 2 group 11 ;
       alter database add STANDBY logfile thread 2 group 12 ;

   #2.2 生成pfile，并修改。

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

       1.修改
           *.audit_file_dest='/opt/app/oracle/admin/racdb/adump'
          修改后：
           *.audit_file_dest='/opt/app/oracle/admin/stbdb/adump'

           建立目录：
           mkdir -p /opt/app/oracle/admin/stbdb/adump
           chown oracle:oinstall /opt/app/oracle/admin/stbdb/adump

       2.添加以下内容:

       *.db_file_name_convert='+DATA/racdb/datafile','+DATA/stbdb/datafile','+DATA/racdb/tempfile','+DATA/stbdb/tempfile'
       *.LOG_FILE_NAME_CONVERT='+DATA/racdb/onlinelog','+DATA/stbdb/onlinelog','+RECOVERY/racdb/onlinelog' ,'+RECOVERY/stbdb/onlinelog'

       修改前的 initstbdb.ora

           racdb1.__db_cache_size=13623099392
           racdb2.__db_cache_size=13623099392
           racdb2.__java_pool_size=67108864
           racdb1.__java_pool_size=67108864
           racdb2.__large_pool_size=67108864
           racdb1.__large_pool_size=67108864
           racdb1.__oracle_base='/opt/app/oracle'#ORACLE_BASE set from environment
           racdb2.__oracle_base='/opt/app/oracle'#ORACLE_BASE set from environment
           racdb2.__pga_aggregate_target=9462349824
           racdb1.__pga_aggregate_target=9462349824
           racdb2.__sga_target=17582522368
           racdb1.__sga_target=17582522368
           racdb2.__shared_io_pool_size=0
           racdb1.__shared_io_pool_size=0
           racdb1.__shared_pool_size=3556769792
           racdb2.__shared_pool_size=3690987520
           racdb2.__streams_pool_size=0
           racdb1.__streams_pool_size=67108864
           *.audit_file_dest='/opt/app/oracle/admin/racdb/adump'
           *.audit_trail='NONE'
           *.cluster_database=true
           *.compatible='11.2.0.0.0'
           *.control_files='+DATA/racdb/controlfile/current.267.812303153','+RECOVERY/racdb/controlfile/current.3283.812303153'
           *.db_block_size=8192
           *.db_create_file_dest='+DATA'
           *.db_domain=''
           *.db_flashback_retention_target=1440
           *.db_name='racdb'
           *.db_recovery_file_dest='+RECOVERY'
           *.db_recovery_file_dest_size=3221225472000
           *.diagnostic_dest='/opt/app/oracle'
           *.dispatchers='(PROTOCOL=TCP) (SERVICE=racdbXDB)'
           *.fal_client='RACDB'
           *.fal_server='DGDB'
           racdb2.instance_number=2
           racdb1.instance_number=1
           *.log_archive_config='DG_CONFIG=(dgdb,racdb)'
           *.log_archive_dest_2='SERVICE=dgdb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dgdb'
           *.log_archive_dest_state_2='DEFER'
           *.log_archive_format='%t_%s_%r.dbf'
           *.log_archive_max_processes=30
           racdb1.memory_max_target=29360128000
           *.memory_target=27031240704
           *.nls_language='SIMPLIFIED CHINESE'
           *.nls_territory='CHINA'
           *.open_cursors=5000
           *.processes=8000
           *.remote_listener='rac-scan:1521'
           *.remote_login_passwordfile='EXCLUSIVE'
           *.resource_manager_plan=''
           *.session_cached_cursors=100
           *.sessions=5000
           *.standby_file_management='MANUAL'
           racdb2.thread=2
           racdb1.thread=1
           racdb2.undo_tablespace='UNDOTBS2'
           racdb1.undo_tablespace='UNDO_TBS01'
           [oracle@localhost dbs]$

       修改后的initstbdb.ora

           [oracle@localhost dbs]$ cat initstbdb.ora
           stbdb.__db_cache_size=13623099392
           stbdb.__java_pool_size=67108864
           stbdb.__large_pool_size=67108864
           stbdb.__oracle_base='/opt/app/oracle'#ORACLE_BASE set from environment
           stbdb.__pga_aggregate_target=9462349824
           stbdb.__sga_target=17582522368
           stbdb.__shared_io_pool_size=0
           stbdb.__shared_pool_size=3556769792
           stbdb.__streams_pool_size=67108864
           *.audit_file_dest='/opt/app/oracle/admin/stbdb/adump'
           *.audit_trail='NONE'
           *.compatible='11.2.0.0.0'
           *.control_files='+DATA/racdb/controlfile/current.267.812303153','+RECOVERY/racdb/controlfile/current.3283.812303153'
           *.db_block_size=8192
           *.db_create_file_dest='+DATA'
           *.db_domain=''
           *.db_flashback_retention_target=1440
           *.db_name='racdb'
           db_unique_name='stbdb'
           *.db_recovery_file_dest='+RECOVERY'
           *.db_recovery_file_dest_size=900000000000
           *.diagnostic_dest='/opt/app/oracle'
           *.dispatchers='(PROTOCOL=TCP) (SERVICE=stbdb)'
           *.fal_client='STBDB'
           *.fal_server='RACDB1,RACDB2'
           stbdb.instance_number=1
           *.db_file_name_convert='+DATA/racdb/datafile','+DATA/stbdb/datafile','+DATA/racdb/tempfile','+DATA/stbdb/tempfile'
           *.LOG_FILE_NAME_CONVERT='+DATA/racdb/onlinelog','+DATA/stbdb/onlinelog','+RECOVERY/racdb/onlinelog' ,'+RECOVERY/stbdb/onlinelog'
           *.log_archive_config='DG_CONFIG=(dgdb,racdb)'
           *.log_archive_dest_2='SERVICE=dgdb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dgdb'
           *.log_archive_dest_state_2='DEFER'
           *.log_archive_format='%t_%s_%r.dbf'
           *.log_archive_max_processes=30
           stbdb.memory_max_target=29360128000
           *.memory_target=27031240704
           *.nls_language='SIMPLIFIED CHINESE'
           *.nls_territory='CHINA'
           *.open_cursors=5000
           *.processes=8000
           *.remote_listener='stbdb:1521'
           *.remote_login_passwordfile='EXCLUSIVE'
           *.resource_manager_plan=''
           *.session_cached_cursors=100
           *.sessions=5000
           *.standby_file_management='AUTO'
           thread=1
           undo_tablespace='UNDOTBS1'

   #2.3创建控制文件：
       切换两次日志及做两次归档
       SQL> alter system switch logfile;

       System altered.

       SQL> alter system switch logfile;

       System altered.

       SQL> alter system archive log current;

       System altered.

       SQL> alter system archive log current;

       System altered.

       SQL> alter database create standby controlfile as '/tmp/control01.ctl';

       Database altered.

   #2.4创建密码文件
       [oracle@ract1 ~]$ orapwd file=/tmp/orapwstbdb.ora password=sa force=y ignorecase=y

   #2.5把文件拷贝到 dg1 服务器：

       scp /tmp/orapwstbdb.ora dg1:/opt/app/oracle/product/11.2.0/dbs/orapwstbdb.ora
       scp /tmp/inittdb.ora dg1:/tmp/inittdb.ora

   #2.6 在STANDBY 端
       SQL> create spfile from pfile='/tmp/inittdb.ora';
       File created.
       SQL> startup nomount;

#2.7复制数据库

（执行命令要在PRIMARY 中的某一个节点中进行，不然会出错）

rman target / auxiliary sys/oracle@stbdb nocatalog
       [oracle@dg1 dbs]$ rman target sys/oracle@racdb1 auxiliary sys/oracle@stbdb nocatalog

       rman>duplicate target database for standby from active database nofilenamecheck;

三、DATAGUARD 开启

   SQL> alter database recover managed standby database disconnect from session;

   Database altered.

   # 开启STANDBY 数据库

   ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;

   #启用STANDBY数据库，进入RECOVER模式：

   ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

   #启动到只读模式：
   alter database open read only;

   #转换到REAL-TIME模式下：
   --alter database recover managed standby database cancel;
   --ALTER DATABASE OPEN;
   ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT from session;

四、测试验证

   #PRIMARY 端：

SQL>
SQL> select thread#,sequence#,status from v$log;

   THREAD# SEQUENCE# STATUS
---------- ---------- ----------------
   1   57930 CURRENT
   1   57929 INACTIVE
   2   43251 INACTIVE
   2   43252 ACTIVE
   1   57928 INACTIVE
   2   43253 CURRENT

6 rows selected.
   SQL>
   #STANDBY 端

   SQL> select archived_thread#,archived_seq#,APPLIED_THREAD#,APPLIED_SEQ# from v$archive_dest_status;
ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ#
---------------- ------------- --------------- ------------
           0         0             0          0
           0         0             0          0
           0         0             0          0
           0         0             0          0
           0         0             0          0
           0         0             0          0
           0         0             0          0
           0         0             0          0
           0         0             0          0
           2   43252             2          43252