Oracle 11g RAC搭建单机DataGuard

共计 13620 个字符，预计需要花费 35 分钟才能阅读完成。

本实验是 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
----------------------------------------------------------------
1110850 CURRENT 13-OCT-16
1210650 INACTIVE 13-OCT-16
1310750 INACTIVE 13-OCT-16
2410550 INACTIVE 13-OCT-16
2510650 INACTIVE 13-OCT-16
2610750 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 110716710
ARCH CONNECTED 00000
ARCH CLOSING 2106140
ARCH CLOSING 210516440
RFS IDLE 00000
RFS IDLE 00000
RFS IDLE 11085971610
RFS IDLE 00000
RFS IDLE 00000
RFS IDLE 00000
RFS IDLE 21072326410
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS DELAY_MINS
-----------------------------------------------------------------------
RFS IDLE 00000
MRP0 APPLYING_LOG 1108597151024000
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#
------------------------
1071
1062

在主库上

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 +0000:00:00
apply lag +0000:00:00
apply finish time +0000:00:00.000
estimated startup time 42