新手搭建ORACLE Data Guard 详细流程

最新推荐文章于 2023-11-28 22:49:18 发布

cuannuo2296

最新推荐文章于 2023-11-28 22:49:18 发布

阅读量246

点赞数

文章标签：数据库

--测试环境搭建DATA GUARD phisical redhat6.5 oracle 11.2.0.4

一、现状
主库：运行正常，原来是按照单实例设计(可能要调整好多参数)
备库：只安装了软件，没有建库（监听建好了）
规划：主节点SID=redglag 归档位置=/arch_p 备节点SID=eastwind 归档位置=/arch_s

二、搭建步骤，主库的修改
2.1 主库调整为归档模式
sql> alter system set log_archive_dest_1='location=/arch_p/' scope=spfile;
System altered.

SQL> alter system set log_archive_format='arch_%d_%t_%r_%s.arc' scope=spfile;
System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

   Total System Global Area 1970864128 bytes
   Fixed Size                  2254544 bytes
   Variable Size            1107298608 bytes
   Database Buffers          855638016 bytes
   Redo Buffers                5672960 bytes
   Database mounted.

SQL> alter database archivelog;

   Database altered.

   SQL> archive log list;
   Database log mode              Archive Mode
   Automatic archival             Enabled
   Archive destination            /arch_p/
   Oldest online log sequence     63
   Next log sequence to archive   65
   Current log sequence           65
SQL> alter database open;

2.2 将primary 库设置成force logging 模式
SQL> alter database force logging;

2.3 检查密码文件是否存在，如果不存在则创建，
bash-4.1$ cd $ORACLE_HOME/dbs
bash-4.1$ ls -l orapw*
-rw-r-----. 1 oracle dba 1536 Dec 27 22:32 orapwredflag    ##主库sid=redglag
##此处存在密码文件，如果不存在，则使用如下命令创建
bash-4.1$ orapwd file=orapwredflag password=oracle entries=10

2.4 配置Standby redo log
##注意standby redo log 和online redo log 大小一致，可以到操作系统上du一下redo 文件大小
bash-4.1$ pwd
   /oracle/app/oradata/redflag
   bash-4.1$ du -sm redo0*
   51      redo01.log
   51      redo02.log
   51      redo03.log
   ##我的redo 大小是51M

    一般 standby redo 要比 online redo 多一组
    按照公式：standby redo logfile amount = (每个线程的日志组数 + 1) * 线程数    ##线程主要是RAC环境的说法，也就是有几个节点
    所以我需要创建 (3+1)*1=4 个standby redo日志组，大小都是51M
   SQL>alter database add standby logfile group 4 ('/oracle/app/oradata/redflag/redo_st_041.log') size 51M;
       alter database add standby logfile group 5 ('/oracle/app/oradata/redflag/redo_st_052.log') size 51M;
       alter database add standby logfile group 6 ('/oracle/app/oradata/redflag/redo_st_063.log') size 51M;
       alter database add standby logfile group 7 ('/oracle/app/oradata/redflag/redo_st_074.log') size 51M;

   验证standby redo log 是否添加成功
   SQL> select group#,thread#,sequence#,archived,status from v$standby_log;

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


   2.5 配置主库的初始化参数，先展示一下原来参数文件时什么样子，这样对比最终改动后究竟添加了什么

        SQL> create pfile from spfile; --创建pfile


       SQL> !cat $ORACLE_HOME/dbs/initredflag.ora
       redflag.__db_cache_size=905969664
       redflag.__java_pool_size=16777216
       redflag.__large_pool_size=33554432
       redflag.__oracle_base='/oracle/app'#ORACLE_BASE set from environment
       redflag.__pga_aggregate_target=687865856
       redflag.__sga_target=1291845632
       redflag.__shared_io_pool_size=0
       redflag.__shared_pool_size=301989888
       redflag.__streams_pool_size=16777216
       *.audit_file_dest='/oracle/app/admin/redflag/adump'
       *.audit_trail='db'
       *.compatible='11.2.0.4.0'
       *.control_files='/oracle/app/oradata/redflag/control01.ctl','/oracle/app/fast_recovery_area/redflag/control02.ctl'
       *.db_block_size=8192
       *.db_domain=''
       *.db_name='redflag'
       *.db_recovery_file_dest='/oracle/app/fast_recovery_area'
       *.db_recovery_file_dest_size=4385144832
       *.diagnostic_dest='/oracle/app'
       *.dispatchers='(PROTOCOL=TCP) (SERVICE=redflagXDB)'
       *.log_archive_dest_1='location=/arch_p/'
       *.log_archive_format='arch_%d_%t_%r_%s.arc'
       *.memory_target=1978662912
       *.open_cursors=300
       *.processes=150
       *.remote_login_passwordfile='EXCLUSIVE'
       *.resource_manager_plan='FLAG_PLAN'
       redflag.resource_manager_plan='FLAG_PLAN'
       *.undo_tablespace='UNDOTBS1'
       以上就是primary 库参数文件的样子


       调整后的样子：
       redflag.__db_cache_size=905969664
       redflag.__java_pool_size=16777216
       redflag.__large_pool_size=33554432
       redflag.__oracle_base='/oracle/app'#ORACLE_BASE set from environment
       redflag.__pga_aggregate_target=687865856
       redflag.__sga_target=1291845632
       redflag.__shared_io_pool_size=0
       redflag.__shared_pool_size=301989888
       redflag.__streams_pool_size=16777216
       *.audit_file_dest='/oracle/app/admin/redflag/adump'
       *.audit_trail='db'
       *.compatible='11.2.0.4.0'
       *.control_files='/oracle/app/oradata/redflag/control01.ctl','/oracle/app/fast_recovery_area/redflag/control02.ctl'
       *.db_block_size=8192
       *.db_domain=''
       ##-----------分界线内为添加----
       *.db_name='redflag'     ##主备都一样
       *.DB_UNIQUE_NAME='redflag'    ##主备一定要不一样
       *.LOG_ARCHIVE_CONFIG='DG_CONFIG=(redflag,eastwind)'
       *.LOG_ARCHIVE_DEST_1='LOCATION=/arch_p/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=redflag'
       *.LOG_ARCHIVE_DEST_2='service=eastwind LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=eastwind'   ##此处 LGWR ASYNC 是众多选项中的一个，以后再补充不同之处。
       *.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
       *.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
       *.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
       #--下面是standby 角色的参数用户角色转化
       *.FAL_SERVER=eastwind
       *.FAL_CLIENT=redflag
       *.standby_file_management='AUTO'
       *.LOG_FILE_NAME_CONVERT='/oracle/app/oradata/eastwind/','/oracle/app/oradata/redflag/'
       *.DB_FILE_NAME_CONVERT='/oracle/app/oradata/eastwind/','/oracle/app/oradata/redflag/'
       ##-----------分界线以上为添加---
       *.db_recovery_file_dest='/oracle/app/fast_recovery_area'
       *.db_recovery_file_dest_size=4385144832
       *.diagnostic_dest='/oracle/app'
       *.dispatchers='(PROTOCOL=TCP) (SERVICE=redflagXDB)'
       *.log_archive_dest_1='location=/arch_p/'
       *.log_archive_format='arch_%d_%t_%r_%s.arc'
       *.memory_target=1978662912
       *.open_cursors=300
       *.processes=150
       *.remote_login_passwordfile='EXCLUSIVE'
       *.resource_manager_plan='FLAG_PLAN'
       redflag.resource_manager_plan='FLAG_PLAN'
       *.undo_tablespace='UNDOTBS1'



       再把改好的pfile文件拷贝到备库$ORACLE_HOME/dbs/ 下，做相应修改，去掉不必要参数，把涉及到实例名的参数修改下，把参数中的路径修改下，顺便把目录创建上
       *.audit_file_dest='/oracle/app/admin/eastwind/adump'
       *.audit_trail='db'
       *.compatible='11.2.0.4.0'
       *.control_files='/oracle/app/oradata/eastwind/control01.ctl','/oracle/app/fast_recovery_area/eastwind/control02.ctl'
       *.db_block_size=8192
       *.db_domain=''
       *.db_name='redflag'
       *.db_recovery_file_dest='/oracle/app/fast_recovery_area'
       *.db_recovery_file_dest_size=4385144832
       *.diagnostic_dest='/oracle/app'
       *.log_archive_dest_1='location=/arch_s/'
       *.log_archive_format='arch_%d_%t_%r_%s.arc'
       *.memory_target=1978662912
       *.open_cursors=300
       *.processes=150
       *.undo_tablespace='UNDOTBS1'
       ####content below is added for DG
       *.db_name='redflag'     ##主备都一样
       *.DB_UNIQUE_NAME='eastwind'    ##主备一定要不一样
       *.LOG_ARCHIVE_CONFIG='DG_CONFIG=(redflag,eastwind)'
       *.LOG_ARCHIVE_DEST_1='LOCATION=/arch_s/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=eastwind'
       *.LOG_ARCHIVE_DEST_2='service=redflag LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=redflag'
       *.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
       *.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
       *.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
       #--下面是standby 角色的参数用户角色转化
       *.FAL_SERVER=redflag
       *.FAL_CLIENT=eastwind
       *.standby_file_management='AUTO'
       *.LOG_FILE_NAME_CONVERT='/oracle/app/oradata/redflag/','/oracle/app/oradata/eastwind/'
       *.DB_FILE_NAME_CONVERT='/oracle/app/oradata/redflag/','/oracle/app/oradata/eastwind/'


三、数据同步
    参数文件的配置和监听配置到以上就行了。剩下的就是怎样把primary库的数据搬迁到备库，现在我的备库还是只有软件的状态。

    数据的迁移分为2中方式：
    1)主库停库,拷贝数据文件，创建的standby控制文件、参数文件(我们已经在备库上改好了，就不用了)、密码文件
      此法要注意先创建standby controlfile
      主库执行：SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/boston.ctl'; 然后将创建的standby controlfile 拷贝到备库上
    2)使用rman duplicate 这种方式的好处是主库不用停库。以后生产环境standby坏了重新搭建肯定不能停主库，所以以下就使用rman duplicate
   3.1 使用rman 的duplicate 需要配置静态监听和tnsnames.ora 使得rman通过服务名能够同时连接主库和备库
    主库修改如下：
     vi tnsnames.ora
      添加：
     redflag =
       (DESCRIPTION =
         (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.200.187)(PORT = 1521))
         (CONNECT_DATA =
           (SERVER = DEDICATED)
           (SERVICE_NAME = redflag)
         )
       )

     eastwind =
       (DESCRIPTION =
         (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.200.188)(PORT = 1521))
         (CONNECT_DATA =
           (SERVER = DEDICATED)
           (SERVICE_NAME = eastwind)
         )
       )

   主库listener.ora
   SID_LIST_LISTENER =
     (SID_LIST =
       (SID_DESC =
         (GLOBAL_DBNAME = redflag)
         (ORACLE_HOME = /oracle/app/product/11.2.0/Db_1)
         (SID_NAME = redflag)
       )
     )

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

   ADR_BASE_LISTENER = /oracle/app

   备库修改如下：
   备库 listener.ora
   SID_LIST_LISTENER =
     (SID_LIST =
       (SID_DESC =
         (GLOBAL_DBNAME = eastwind)
         (ORACLE_HOME = /oracle/app/product/11.2.0/db_home)
         (SID_NAME = eastwind)
       )
     )

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


   ADR_BASE_LISTENER = /oracle/app

    备库的tnsnames.ora 文件内容改成和主库一样就行了。

   3.2 备份主库，duplicate
   主库：
    rman target /
    run{
    allocate channel c1 type disk;
    allocate channel c2 type disk;
    backup database include current controlfile for standby plus archivelog tag=dg;
    sql'alter system switch logfile';
    release channel c1;
    release channel c2;
    }
   把主库的备份传到备库相同目录

   以下开始用RMAN恢复备库，都在备库上操作
   首先把主库的密码文件拷贝到备库，如果主备实例名不一样，则备库上的密码文件拷贝完改个名。eg：从orapwredflag 改成 orapweastwind。拷贝是为了保证主备sys密码一致
   备库上操作：
   rman target sys/oracle@redflag auxiliary sys/oracle@eastwind ##如果报 auxiliary 辅助实例密码不对, 则用 rman target sys/oracle@redflag auxiliary /

   RMAN> duplicate target database for standby nofilenamecheck dorecover    ##dorecover 不是必选项，但还是加上吧
   完成后备库处于mount状态
   SQL> alter database recover managed standby database disconnect from session;   --备库开始应用redo
   SQL> alter database recover managed standby database cancel;      --备库停止应用redo
   SQL> select process,sequence#,status from v$managed_standby ;     --查看备库应用状态


搭建过程中的常见问题。
1)主库归档传不到备库
   aler日志中报错如下：
   Error 1034 received logging on to the standby
   PING[ARC2]: Heartbeat failed to connect to standby 'eastwind'. Error is 1034.
   原因：主备通讯有问题,可能是备库监听问题，也可能是备库密码文件问题
   解决：检查从主库上能否登上备库，用pl/sql 等否登陆备库。既检查监听、tnsnames.ora 文件配置是否正确。密码文件是否正确(密码文件名要包含实例名,主备sys密码要一致)
         任何sys密码更改或者有授权个其他用户DBA角色，要重新同步密码文件

主库上执行sql： SELECT DEST_ID "ID", STATUS "DB_status", DESTINATION "Archive_dest", ERROR "Error" FROM V$ARCHIVE_DEST WHERE DEST_ID <=5; ##帮助定位不传归档问题


   参考：
   [三思笔记] 一步一步学Data Guard
   http://blog.csdn.net/tianlesoftware/article/details/5547565
   http://docs.oracle.com/cd/B19306_01/server.102/b14239/create_ps.htm#i68626
   http://docs.oracle.com/cd/B19306_01/server.102/b14239/toc.htm