11gRAC搭建单机Dataguard

来源：互联网发布：巨星知我心 by凌豹姿编辑：程序博客网时间：2024/05/22 14:15

本实验是oracle 11.2.0.4 RAC 对单机本地文件路径的DataGuard

推荐设置/etc/hosts文件的域名解析,方便后面的配置,因为IP非常容易乱套了

我的是直接将所有的节点(包括备机)的etc都设置成完全的,一样的,

192.168.115.100 rac1
192.168.115.101 rac2
192.168.115.110 rac1-vip
192.168.115.111 rac2-vip
192.168.145.16 rac1-priv
192.168.145.17 rac2-priv
192.168.115.120 scan
192.168.115.37 racstb

首先是rac主库准备工作:

1. 将rac主库修改为forcelogging模式

SQL> select name , open_mode, log_mode,force_logging from gv$database;

NAME OPEN_MODE LOG_MODE FOR

--------- -------------------- ------------ ---

ORCL READ WRITE ARCHIVELOG NO

SQL> alter database force logging;

Database altered.

SQL> select name , open_mode, log_mode,force_logging from gv$database;

NAME OPEN_MODE LOG_MODE FOR

--------- -------------------- ------------ ---

ORCL READ WRITE ARCHIVELOG YES

2. 设置参数

1. 设置所有的启用的数据库名

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCL,ORCLDG)' SID='*';

2. 设置归档日志的路径(本地和网络)

ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=+FRA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME='ORCL' SID='*';

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=ORCLDG LGWR ASYNC REOPEN NET_TIMEOUT=300 VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE) DB_UNIQUE_NAME='ORCLDG' SID='*';

alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;

alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE;

3. 设置备库文件自动管理,可以在主库新建数据文件的时候自动新建备库

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SID='*';

4. 设置FAL_SERVER为备库名

ALTER SYSTEM SET FAL_SERVER='ORCLDG' SID='*';

5. 设置文件路径的转换,当RMAN还原的时候,主备库路径不一致的时候能够进行绝对路径的转换(实质是绝对路径的字符集简单替换)

alter system set DB_FILE_NAME_CONVERT='/oradata/orcldg/datafile/','+DATA/orcl/datafile/' scope=spfile;

alter system set LOG_FILE_NAME_CONVERT='/oradata/orcldg/orl/','+DATA/orcl/onlineredo/' scope=spfile;

需要注意:这里的LOG_FILE_NAME_CONVERT,实际上就是在RMAN还原的时候,将原控制文件标注的online redo路径的指定字符转换到后面的字符

注意不是standby redolog的路径,standby redolog的路径和这个没关系

3. 配置监听和解析

1. 配置备库上的监听:

vim $ORACLE_HOME/network/admin/listener.ora

原来的保留,只是新加上一个静态监听

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcldg)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = orcldg)
    )
  )

2. 配置所有节点上的解析

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
ORCL1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
ORCL2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
ORCLDG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = racstb)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcldg)
    )
  )

注意是所有节点

3. 检查效果

备库启动监听

lsnrctl start

所有节点使用tnsping 相互ping SID,检查通不通

tnsping ORCL

tnsping ORCL1

tnsping ORCL2

tnsping ORCLDG

4. 创建密码文件到备库

密码文件一般都在$ORACLE_HOME/dbs/下

名称为orapw实例名

注意每个实例的实例名不一样,所以复制到对应的实例下时一定需要注意命名问题

强烈建议:

将一个实例下的口令文件复制到所有节点去,不然可能会出现:

复制rac2的口令文件到备库,结果连不上rac1

复制rac1的口令文件到备库,结果连不上rac2

这个地方很坑的

[oracle@rac2 dbs]$ scp orapworcl1 racstb:/$ORACLE_HOME/dbs/orapworcldg

5. 主库生成pfile,修改后传到备库应用

SQL> create pfile='/home/oracle/pfile.ora' from spfile;

在此附上原pfile和修改后的pfile

scp pfil.ora racstb:/home/oracle/

我使用的是指定sga_target,自动分配,你们可以自己分配

下面的黄色背景我都删除了,蓝色背景需要你们自己修改

当然可能还是由于环境不一致导致一些错误,需要你们自己抓取alert log进行分析

SQL> show parameter background_dump_dest; #查看alert日志所在目录

orcl2.__db_cache_size=268435456
orcl1.__db_cache_size=268435456
orcl2.__java_pool_size=4194304
orcl1.__java_pool_size=4194304
orcl2.__large_pool_size=8388608
orcl1.__large_pool_size=8388608
orcl1.__oracle_base='/u02/app/oracle'#ORACLE_BASE set from environment
orcl2.__oracle_base='/u02/app/oracle'#ORACLE_BASE set from environment
orcl2.__pga_aggregate_target=159383552
orcl1.__pga_aggregate_target=159383552
orcl2.__sga_target=473956352
orcl1.__sga_target=473956352
orcl2.__shared_io_pool_size=0
orcl1.__shared_io_pool_size=0
orcl2.__shared_pool_size=184549376
orcl1.__shared_pool_size=184549376
orcl2.__streams_pool_size=0
orcl1.__streams_pool_size=0
*.audit_file_dest='/u02/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.cluster_database=TRUE
*.compatible='11.2.0.4.0'
*.control_files='+DATA/orcl/controlfile/current.261.922977393','+DATA/orcl/controlfile/current.260.922977393','+FRA/orcl/controlfile/current.256.922977393','+DATA/orcl/controlfile/current.279.924349985'#Restore Controlfile
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+DATA'
*.db_create_online_log_dest_2='+DATA'
*.db_create_online_log_dest_3='+FRA'
*.db_domain=''
*.db_file_name_convert='/oradata/orcldg/datafile/','+DATA/orcl/datafile/'
*.db_name='orcl'
*.diagnostic_dest='/u02/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.fal_server='ORCLDG'
orcl2.instance_number=2
orcl1.instance_number=1
orcl1.local_listener='(DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.115.110)(PORT=1521)))'
*.log_archive_config='DG_CONFIG=(ORCL,ORCLDG)'
*.log_archive_dest_1='LOCATION=+FRA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORCL'
*.log_archive_dest_2='SERVICE=ORCLDG LGWR ASYNC REOPEN NET_TIMEOUT=300 VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLDG'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='ora%t_%s_%r.log'
*.log_file_name_convert='/oradata/orcldg/orl/','+DATA/orcl/SRL/'
*.open_cursors=300
*.pga_aggregate_target=157286400
*.processes=150
*.remote_listener='scan:1521'
*.remote_login_passwordfile='exclusive'
*.sga_target=471859200
*.standby_file_management='AUTO'
orcl2.thread=2
orcl1.thread=1
orcl1.undo_tablespace='UNDOTBS1'
orcl2.undo_tablespace='UNDOTBS2'

修改后:

*.audit_file_dest='/u01/app/oracle/admin/orcldg/adump'

*.audit_trail='db'

*.cluster_database=TRUE

*.compatible='11.2.0.4.0'

*.control_files='/oradata/orcldg/controlfile/ctrlfile01.ctl','/fra/orcldg/controlfile/ctrlfile02.ctl'

*.db_block_size=8192

*.db_create_file_dest='/oradata/orcldg/datafile/'

*.db_domain=''

*.db_file_name_convert='+DATA/orcl/datafile/','/oradata/orcldg/datafile/'

*.db_name='orcl'

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orcldgXDB)'

*.fal_server='orcl1','orcl2'

*.log_archive_config='DG_CONFIG=(ORCL,ORCLDG)'

*.log_archive_dest_1='LOCATION=+FRA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORCLDG'

*.log_archive_dest_2='SERVICE=ORCL LGWR ASYNC REOPEN NET_TIMEOUT=300 VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL'

*.log_archive_dest_state_1='ENABLE'

*.log_archive_dest_state_2='ENABLE'

*.log_archive_format='ora%t_%s_%r.log'

*.log_file_name_convert='+DATA/orcl/onlinelog/','/oradata/orcldg/orl/',

*.open_cursors=300

*.pga_aggregate_target=157286400

*.processes=150

*.remote_login_passwordfile='exclusive'

*.sga_target=471859200

*.standby_file_management='AUTO'

*.service_name='orcldg'

*.db_unique_name='orcldg'

注意db_name不要改,改了之后,加载配置文件后使用RMAN恢复,会报db_name不一致的错误

2. 使用pfile启动备库

$ export ORACLE_SID=orcldg

$ sqlplus / as sysdba

SQL> create spfile from pfile='/home/oracle/pfile.ora';

SQL> startup nomount;

哪里参数错了就改哪里

然后测试一下主备库的连通性,

主库:

sqlplus /nolog

conn sys/oracle@orcldg as sysdba

备库:

sqlplus /nolog

conn sys/oracle@orcl as sysdba

conn sys/oracle@orcl1 as sysdba

conn sys/oracle@orcl2 as sysdba

都能成功就对了,假如不能成功,回去检查监听和tns解析

6. 创建上面提到的目录,包括:

control_files目录

db_create_file_dest

db_file_name_convert中所包含的本地的目录,也就是本地数据文件目录

log_file_name_convert中所包含的本地的目录,也就是本地online redo目录

推荐创建个standby redolog目录,创建standby logfile的时候放在此处,更便于管理

注意所有目录都需要chown oracle:oinstall

7. 备份

注意:11g可以使用RMAN duplicate进行复制,但是本人多次都失败了...

此处使用笨方法,RMAN备份还原

1. 在主库上备份数据文件和控制文件

run {

allocate channel c1 type disk;

allocate channel c2 type disk;

allocate channel c3 type disk;

backup incremental level 0 format '/home/oracle/rmanbackup/orcl_full_%U' database;

backup format '/home/oracle/rmanbackup/orcl_full_stanctf_%U' current controlfile for standby;

release channel c1;

release channel c2;

release channel c3;

}

8. 恢复

1. 主库将备份传输到备库上

scp orcl_full_* orcldg:/home/oracle/rmanbackup/

2. RMAN恢复

RMAN target/

RMAN> restore standby controlfile from '/home/oracle/backup/orcl_full_stanctf_XXXXXX';

RMAN> alter database mount;

RMAN> catalog start with '/home/oracle/backup/orcl_full_';

RMAN> run {

allocate channel d1 type disk;

allocate channel d2 type disk;

allocate channel d3 type disk;

restore database;

release channel d1;

release channel d2;

release channel d3;

}

解释:

1. 恢复控制文件到spfile指定的位置;

2. alter database mount;

3. 创建RMAN恢复目录册 catalog,这样RMAN知道了所有备份的位置,下面才能直接restore database;

9. 创建standby redolog

1. 主端创建standby redolog --为了主备切换

standby redolog文件只是在备库上有用,是用来同步主库的online redo的,主库的时候是不起作用的

但是为了主备切换的流畅,我们一样需要在主库上进行设置

1. 查看日志分组信息和大小信息

SQL> col status format a10;

SQL> select thread#, GROUP#,SEQUENCE#,BYTES/1024/1024,STATUS,FIRST_TIME from v$log;

SQL> col status format a10;
SQL> select thread#, GROUP#,SEQUENCE#,BYTES/1024/1024,STATUS,FIRST_TIME from v$log;
   THREAD#     GROUP#  SEQUENCE# BYTES/1024/1024 STATUS     FIRST_TIM
---------- ---------- ---------- --------------- ---------- ---------
         1          1        108              50 CURRENT    13-OCT-16
         1          2        106              50 INACTIVE   13-OCT-16
         1          3        107              50 INACTIVE   13-OCT-16
         2          4        105              50 INACTIVE   13-OCT-16
         2          5        106              50 INACTIVE   13-OCT-16
         2          6        107              50 CURRENT    13-OCT-16
6 rows selected.

查看日志文件信息

SQL> set linesize 200

SQL> col member format a50

SQL> select * from v$logfile;

SQL>  set linesize 200
SQL> col member format a50
SQL> select * from v$logfile;
    GROUP# STATUS     TYPE    MEMBER                                             IS_
---------- ---------- ------- -------------------------------------------------- ---
         1            ONLINE  +DATA/orcl/onlinelog/group_1.272.924777023         NO
         1            ONLINE  +DATA/orcl/onlinelog/group_1.271.924777027         NO
         1            ONLINE  +FRA/orcl/onlinelog/group_1.260.924777027          NO
         2            ONLINE  +DATA/orcl/onlinelog/group_2.270.924777065         NO
         2            ONLINE  +DATA/orcl/onlinelog/group_2.269.924777067         NO
         2            ONLINE  +FRA/orcl/onlinelog/group_2.259.924777067          NO
         3            ONLINE  +DATA/orcl/onlinelog/group_3.280.924777461         NO
         3            ONLINE  +DATA/orcl/onlinelog/group_3.281.924777463         NO
         3            ONLINE  +FRA/orcl/onlinelog/group_3.393.924777463          NO
         4            ONLINE  +DATA/orcl/onlinelog/group_4.262.924777517         NO
         4            ONLINE  +DATA/orcl/onlinelog/group_4.263.924777517         NO
    GROUP# STATUS     TYPE    MEMBER                                             IS_
---------- ---------- ------- -------------------------------------------------- ---
         4            ONLINE  +FRA/orcl/onlinelog/group_4.394.924777519          NO
         5            ONLINE  +DATA/orcl/onlinelog/group_5.264.924777567         NO
         5            ONLINE  +DATA/orcl/onlinelog/group_5.265.924777567         NO
         5            ONLINE  +FRA/orcl/onlinelog/group_5.395.924777571          NO
         6            ONLINE  +DATA/orcl/onlinelog/group_6.286.924777631         NO
         6            ONLINE  +DATA/orcl/onlinelog/group_6.287.924777633         NO
         6            ONLINE  +FRA/orcl/onlinelog/group_6.396.924777633          NO
18 rows selected.

得知:主库共两个线程,每个线程有三个日志组,每个日志组的日志大小有50M

2. 规划standby文件

standby redolog文件的要求是

1. 不能小于online redo日志文件大小

2. 每个线程都要创建相对应的日志组

3. 每个线程必须要比对面的多一个文件组

3. 执行添加

也就是说:

我需要创建2个线程,每个线程4个日志组,每个日志组50M (还可以更大,但是不能小)

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1
GROUP 7 ('+DATA/orcl/SRL/stdbyredo01.log') SIZE 50M,
GROUP 8 ('+DATA/orcl/SRL/stdbyredo02.log') SIZE 50M,
GROUP 9 ('+DATA/orcl/SRL/stdbyredo03.log') SIZE 50M,
GROUP 10 ('+DATA/orcl/SRL/stdbyredo04.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2
GROUP 11 ('+DATA/orcl/SRL/stdbyredo05.log') SIZE 50M,
GROUP 12 ('+DATA/orcl/SRL/stdbyredo06.log') SIZE 50M,
GROUP 13 ('+DATA/orcl/SRL/stdbyredo07.log') SIZE 50M,
GROUP 14 ('+DATA/orcl/SRL/stdbyredo08.log') SIZE 50M;

2. 备端创建standby redolog

主备的online redo信息肯定是一样的

还是根据上面的分析,和上面分析的是一样的

你们需要根据自己的规划进行决定,

有良好的规划是一个dba的基本能力

ALTER DATABASE ADD standby LOGFILE THREAD 1
GROUP 7 ('/oradata/orcldg/srl/srl07.log') SIZE 50M,
GROUP 8 ('/oradata/orcldg/srl/srl08.log') SIZE 50M,
GROUP 9 ('/oradata/orcldg/srl/srl09.log') SIZE 50M,
GROUP 10 ('/oradata/orcldg/srl/srl10.log') SIZE 50M;
ALTER DATABASE ADD standby LOGFILE THREAD 2
GROUP 11 ('/oradata/orcldg/srl/srl11.log') SIZE 50M,
GROUP 12 ('/oradata/orcldg/srl/srl12.log') SIZE 50M,
GROUP 13 ('/oradata/orcldg/srl/srl13.log') SIZE 50M,
GROUP 14 ('/oradata/orcldg/srl/srl14.log') SIZE 50M;

好了,standby redolog配置完成了

10. 跑归档使主备之间同步

备端恢复数据文件完成后，开启介质恢复进程，将主库的归档日志恢复到备库。

备端启动恢复进程mrp0

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

备端查询是否有mrp0进程

SELECT PROCESS, STATUS,THREAD#,SEQUENCE#,BLOCK#,BLOCKS, DELAY_MINS FROM V$MANAGED_STANDBY;

SQL> SELECT  PROCESS, STATUS,THREAD#,SEQUENCE#,BLOCK#,BLOCKS, DELAY_MINS FROM V$MANAGED_STANDBY;
PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS DELAY_MINS
--------- ------------ ---------- ---------- ---------- ---------- ----------
ARCH      CLOSING               1        107          1        671          0
ARCH      CONNECTED             0          0          0          0          0
ARCH      CLOSING               2        106          1          4          0
ARCH      CLOSING               2        105          1        644          0
RFS       IDLE                  0          0          0          0          0
RFS       IDLE                  0          0          0          0          0
RFS       IDLE                  1        108      59716          1          0
RFS       IDLE                  0          0          0          0          0
RFS       IDLE                  0          0          0          0          0
RFS       IDLE                  0          0          0          0          0
RFS       IDLE                  2        107      23264          1          0
PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS DELAY_MINS
--------- ------------ ---------- ---------- ---------- ---------- ----------
RFS       IDLE                  0          0          0          0          0
MRP0      APPLYING_LOG          1        108      59715     102400          0
13 rows selected.

切换主库归档，观察备库归档日志同步是否正常。

SQL> alter system archive log current;

查看备机恢复进程状态的方法：

SELECT PROCESS, STATUS,THREAD#,SEQUENCE#,BLOCK#,BLOCKS, DELAY_MINS FROM V$MANAGED_STANDBY;

关闭介质恢复的方法：

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

11. 备端开启和关闭Active Data Guard

Data Guard可以以只读的方式打开数据库，但此时Media Recovery利用日志进行数据同步的过程就停止了，如果物理备用数据库处于恢复的过程中数据库就不能打开查询，也就是说日志应用和只读打开两个状态是互斥的。

Oracle 11g 中推出的Active Data Guard功能解决了这个矛盾，在利用日志恢复数据的同时可以用只读的方式打开数据库，用户可以在备用数据库上进行查询、报表等操作，这类似逻辑Data Guard备用数据库的功能（查询功能方面），但是，数据同步的效率更高、对硬件的资源要求更低。这样可以更大程度地发挥物理备用数据库的硬件资源的效能。

如果开启了第10步的介质恢复进程，需要先关闭介质恢复进程

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

启动

SQL> ALTER DATABASE OPEN READ ONLY;

SQL> RECOVER MANAGED STANDBY DATABASE DISCONNECT USING CURRENT LOGFILE;

该功能的测试可以在一个主端插入数据提交后,在备库能够立刻或者很快看到数据(业务空闲的情况)

12. 查询同步情况

主库上

SQL> select max(sequence#),thread# from v$archived_log where RESETLOGS_CHANGE# = (SELECT RESETLOGS_CHANGE# FROM V$DATABASE_INCARNATION WHERE STATUS = 'CURRENT') GROUP BY THREAD#;
MAX(SEQUENCE#)  THREAD#
-------------- ----------
   1071
   1062

备库上

SQL> select max(sequence#),thread# from v$archived_log where RESETLOGS_CHANGE# = (SELECT RESETLOGS_CHANGE# FROM V$DATABASE_INCARNATION WHERE STATUS = 'CURRENT') GROUP BY THREAD#;
MAX(SEQUENCE#)    THREAD#
-------------- ----------
           107          1
           106          2

在主库上

alter system switch logfile;

可以看到备库上的MAX(SEQUENCE#)是随主库不断变化的

一般说相差1-2等是正常的,但是差很多的话就需要检查网络等情况了

以下查询仅限11G Active Data Guard

SQL> set line 300

SQL> select name,value from v$dataguard_stats;

SQL> set line 300
SQL> select name,value  from v$dataguard_stats;
NAME                             VALUE
-------------------------------- ----------------------------------------------------------------
transport lag                    +00 00:00:00
apply lag                        +00 00:00:00
apply finish time                +00 00:00:00.000
estimated startup time           42

0 0