oracle 11g rac data guard,Oracle 11g RAC搭建单机DataGuard

最新推荐文章于 2023-09-01 16:55:58 发布

聿子先生

最新推荐文章于 2023-09-01 16:55:58 发布

阅读量404

点赞数

文章标签： oracle 11g rac data guard

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=ORCLDGLGWR 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

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

1110850CURRENT13-OCT-16

1210650INACTIVE13-OCT-16

1310750INACTIVE13-OCT-16

2410550INACTIVE13-OCT-16

2510650INACTIVE13-OCT-16

2610750CURRENT13-OCT-16

6rows selected.

查看日志文件信息

SQL> set linesize 200

SQL>col member format a50

SQL>select * from v$logfile;

SQL>setlinesize200

SQL>col member format a50

SQL>select*from v$logfile;

GROUP# STATUS TYPE MEMBER IS_

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

1ONLINE+DATA/orcl/onlinelog/group_1.272.924777023NO

1ONLINE+DATA/orcl/onlinelog/group_1.271.924777027NO

1ONLINE+FRA/orcl/onlinelog/group_1.260.924777027NO

2ONLINE+DATA/orcl/onlinelog/group_2.270.924777065NO

2ONLINE+DATA/orcl/onlinelog/group_2.269.924777067NO

2ONLINE+FRA/orcl/onlinelog/group_2.259.924777067NO

3ONLINE+DATA/orcl/onlinelog/group_3.280.924777461NO

3ONLINE+DATA/orcl/onlinelog/group_3.281.924777463NO

3ONLINE+FRA/orcl/onlinelog/group_3.393.924777463NO

4ONLINE+DATA/orcl/onlinelog/group_4.262.924777517NO

4ONLINE+DATA/orcl/onlinelog/group_4.263.924777517NO

GROUP# STATUS TYPE MEMBER IS_

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

4ONLINE+FRA/orcl/onlinelog/group_4.394.924777519NO

5ONLINE+DATA/orcl/onlinelog/group_5.264.924777567NO

5ONLINE+DATA/orcl/onlinelog/group_5.265.924777567NO

5ONLINE+FRA/orcl/onlinelog/group_5.395.924777571NO

6ONLINE+DATA/orcl/onlinelog/group_6.286.924777631NO

6ONLINE+DATA/orcl/onlinelog/group_6.287.924777633NO

6ONLINE+FRA/orcl/onlinelog/group_6.396.924777633NO

18rows selected.

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

2. 规划standby文件

standby redolog文件的要求是

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

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

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

3. 执行添加

也就是说:

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

ALTER DATABASE ADD STANDBY LOGFILE THREAD1

GROUP7('+DATA/orcl/SRL/stdbyredo01.log')SIZE50M,

GROUP8('+DATA/orcl/SRL/stdbyredo02.log')SIZE50M,

GROUP9('+DATA/orcl/SRL/stdbyredo03.log')SIZE50M,

GROUP10('+DATA/orcl/SRL/stdbyredo04.log')SIZE50M;

ALTER DATABASE ADD STANDBY LOGFILE THREAD2

GROUP11('+DATA/orcl/SRL/stdbyredo05.log')SIZE50M,

GROUP12('+DATA/orcl/SRL/stdbyredo06.log')SIZE50M,

GROUP13('+DATA/orcl/SRL/stdbyredo07.log')SIZE50M,

GROUP14('+DATA/orcl/SRL/stdbyredo08.log')SIZE50M;

2. 备端创建standby redolog

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

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

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

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

ALTER DATABASE ADD standby LOGFILE THREAD1

GROUP7('/oradata/orcldg/srl/srl07.log')SIZE50M,

GROUP8('/oradata/orcldg/srl/srl08.log')SIZE50M,

GROUP9('/oradata/orcldg/srl/srl09.log')SIZE50M,

GROUP10('/oradata/orcldg/srl/srl10.log')SIZE50M;

ALTER DATABASE ADD standby LOGFILE THREAD2

GROUP11('/oradata/orcldg/srl/srl11.log')SIZE50M,

GROUP12('/oradata/orcldg/srl/srl12.log')SIZE50M,

GROUP13('/oradata/orcldg/srl/srl13.log')SIZE50M,

GROUP14('/oradata/orcldg/srl/srl14.log')SIZE50M;

好了,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 CLOSING110716710

ARCH CONNECTED00000

ARCH CLOSING2106140

ARCH CLOSING210516440

RFS IDLE00000

RFS IDLE11085971610

RFS IDLE00000

RFS IDLE21072326410

PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS DELAY_MINS

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

RFS IDLE00000

MRP0 APPLYING_LOG1108597151024000

13rows 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

在主库上

聿子先生

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
oracle 11g rac data guard,Oracle 11g RAC搭建单机DataGuard

1. 将rac主库修改为forcelogging模式SQL> select name , open_mode, log_mode,force_logging from gv$database;NAME OPEN_MODE LOG_MODE FOR--------- -------------------- ------------ ---ORCL ...
复制链接

扫一扫