本实验是Oracle 11.2.0.4 RAC 对单机本地文件路径的DataGuard
推荐设置/etc/hosts文件的域名解析,方便后面的配置,因为IP非常容易乱套了
我的是直接将所有的节点(包括备机)的etc都设置成完全的,一样的,
首先是rac主库准备工作:
1. 将rac主库修改为forcelogging模式
2. 设置参数
3. 配置监听和解析
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcldg)
(ORACLE_HOME =/u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = orcldg)
)
)
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)
)
)
4. 创建密码文件到备库
5. 主库生成pfile,修改后传到备库应用
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'
6. 创建上面提到的目录,包括:
7. 备份
8. 恢复
9. 创建standby redolog
1. 主端创建standby redolog --为了主备切换
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;
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.
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
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;
10. 跑归档使主备之间同步
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.
11. 备端开启和关闭Active Data Guard
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
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
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的路径和这个没关系
1. 配置备库上的监听:
vim $ORACLE_HOME/network/admin/listener.ora
原来的保留,只是新加上一个静态监听
2. 配置所有节点上的解析
注意是所有节点
3. 检查效果
备库启动监听
lsnrctl start
所有节点使用tnsping 相互ping SID,检查通不通
tnsping ORCL
tnsping ORCL1
tnsping ORCL2
tnsping ORCLDG
密码文件一般都在$ORACLE_HOME/dbs/下
名称为orapw实例名
注意每个实例的实例名不一样,所以复制到对应的实例下时一定需要注意命名问题
强烈建议:
将一个实例下的口令文件复制到所有节点去,不然可能会出现:
复制rac2的口令文件到备库,结果连不上rac1
复制rac1的口令文件到备库,结果连不上rac2
这个地方很坑的
[oracle@rac2 dbs]$ scp orapworcl1 racstb:/$ORACLE_HOME/dbs/orapworcldg
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日志所在目录
修改后:
*.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解析
control_files目录
db_create_file_dest
db_file_name_convert中所包含的本地的目录,也就是本地数据文件目录
log_file_name_convert中所包含的本地的目录,也就是本地online redo目录
推荐创建个standby redolog目录,创建standby logfile的时候放在此处,更便于管理
注意所有目录都需要chown oracle:oinstall
注意: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;
}
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;
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> set linesize 200
SQL> col member format a50
SQL> select * from v$logfile;
得知:主库共两个线程,每个线程有三个日志组,每个日志组的日志大小有50M
2. 规划standby文件
standby redolog文件的要求是
1. 不能小于online redo日志文件大小
2. 每个线程都要创建相对应的日志组
3. 每个线程必须要比对面的多一个文件组
3. 执行添加
也就是说:
我需要创建2个线程,每个线程4个日志组,每个日志组50M (还可以更大,但是不能小)
主备的online redo信息肯定是一样的
还是根据上面的分析,和上面分析的是一样的
你们需要根据自己的规划进行决定,
有良好的规划是一个dba的基本能力
好了,standby redolog配置完成了
备端恢复数据文件完成后，开启介质恢复进程，将主库的归档日志恢复到备库。
备端启动恢复进程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> 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;
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;
该功能的测试可以在一个主端插入数据提交后,在备库能够立刻或者很快看到数据(业务空闲的情况)
主库上
备库上
在主库上
alter system switch logfile;
可以看到备库上的MAX(SEQUENCE#)是随主库不断变化的
一般说相差1-2等是正常的,但是差很多的话就需要检查网络等情况了
以下查询仅限11G Active Data Guard
SQL> set line 300
SQL> select name,value from v$dataguard_stats;
：