未加星标

11gRAC搭建单机Dataguard

字体大小 | |
[数据库(综合) 所属分类 数据库(综合) | 发布者 店小二03 | 时间 2016 | 作者 红领巾 ] 0人收藏点击收藏

本实验是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 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 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 这个地方很坑的

[[email protected] dbs]$ scp orapworcl1 racstb:/$ORACLE_HOME/dbs/orapworcldg

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

1. 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 [email protected] as sysdba

备库: sqlplus /nolog conn [email protected] as sysdba conn [email protected] as sysdba

conn [email protected] 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#

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

107 1

106 2

备库上

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

本文数据库(综合)相关术语:系统安全软件

主题: SQL数据TIUTCUAUTAURIMRYLG
分页:12
转载请注明
本文标题:11gRAC搭建单机Dataguard
本站链接:http://www.codesec.net/view/483019.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(综合) | 评论(0) | 阅读(38)