主库：
RAC的host信息：
192.168.0.21 rac1
192.168.0.22 rac2
192.168.178.21 rac1priv
192.168.178.22 rac2priv
192.168.0.23 rac1vip
192.168.0.24 rac2vip
192.168.0.25 racscanip
节点1：
ip：192.168.0.21 系统：linux6.8 数据库：oracle11gRAC sys密码：123456
db_name=racdb,即instance_name、db_unique_name 、service_name都为racdb
节点2：
ip：192.168.0.22 系统：linux6.8 数据库：oracle11gRAC sys密码：123456
db_name=racdb,即instance_name、db_unique_name 、service_name都为racdb
备库：
RAC:
RAC的host信息：
192.168.0.61 rac1
192.168.0.62 rac2
192.168.178.61 rac1priv
192.168.178.62 rac2priv
192.168.0.63 rac1vip
192.168.0.64 rac2vip
192.168.0.65 racscanip
节点1：
ip：192.168.0.61 系统：linux6.8 数据库：oracle11gRAC sys密码：oracle123
节点2：
ip：192.168.0.62 系统：linux6.8 数据库：oracle11gRAC sys密码：oracle123
搭建前准备：
需要在61、62两台服务器先行安装好oracle11g数据库，且都未创建实例，备库的db_name一定与主库一致，db_unique_name，3个库需要一定不一样，其余随意
1、搭建dataguard
判断主库DG是否已经安装：
select * from v$option where parameter = 'Oracle Data Guard';
如果是true表示已经安装可以配置，否则需要安装相应组件。
如果不知道主库sys密码，则修改主库sys用户密码：
alter user sys identified by 123456;
确认主库是否开启archivelog与force log
startup mount
alter database force logging;//修改数据库为强制记日志，这是必须的操作，主库的每一步操作都得记录到日志中去。??
archive log list;//--修改数据库为归档模式，因为dg是通过传送归档日志到备库然后应用来保证主备库一致的。??
select log_mode,force_logging from v$database; --检查
alter database force logging; --开启
archive log list;
alter database archivelog;
2.配置主库standby redo log，并验证结果
查看是否已经存在standby redo log
select group#, thread#, sequence#, archived, status from v$standby_log;
存在就不用按照以下内容创建。
根据以下sql查询的结果，创建standby redo log。
select GROUP#,THREAD#,BYTES from v$log ;
select MEMBER from v$logfile; //注意日志文件的所有路径，所有日志都要拷过来
ALTER DATABASE ADD STANDBY LOGFILE thread 1 '+DATA/racdb/onlinelog/stbredo11.log' SIZE 50m;
ALTER DATABASE ADD STANDBY LOGFILE thread 1 '+DATA/racdb/onlinelog/stbredo12.log' SIZE 50m;
ALTER DATABASE ADD STANDBY LOGFILE thread 1 '+DATA/racdb/onlinelog/stbredo13.log' SIZE 50m;
ALTER DATABASE ADD STANDBY LOGFILE thread 2 '+DATA/racdb/onlinelog/stbredo14.log' SIZE 50m;
ALTER DATABASE ADD STANDBY LOGFILE thread 2 '+DATA/racdb/onlinelog/stbredo15.log' SIZE 50m;
ALTER DATABASE ADD STANDBY LOGFILE thread 2 '+DATA/racdb/onlinelog/stbredo16.log' SIZE 50m;
alter database add standby logfile thread 1 group 7 ('DATA') size 50M;
alter database drop standby logfile group 5;
检查创建standby redo log结果
select group#, thread#, sequence#, archived, status from v$standby_log;
3.修改主库初始化参数
alter system set log_archive_config='DG_CONFIG=(racdb,racdbdg)' scope=both sid='*';
alter system set log_archive_dest_1= 'location=+DATA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=racdb' scope=both sid='racdb1';
alter system set log_archive_dest_1= 'location=+DATA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=racdb' scope=both sid='racdb2';
alter system set log_archive_dest_2='SERVICE=racdbdg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=racdbdg' scope=both sid='*';
alter system set log_archive_dest_state_1=enable scope=both sid='*';
alter system set log_archive_dest_state_2=enable scope=both sid='*';
alter system set standby_file_management=auto scope=both sid='*';
alter system set fal_server= racdbdg scope=both sid='*';
alter system set fal_client='racdb1,racdb2' scope=both sid='*';
alter system set db_file_name_convert= '+DATA','+DATA' scope=spfile sid='*';
alter system set log_file_name_convert= '+DATA','+DATA' scope=spfile sid='*';
4重启数据库数据库，使设置生效
srvctl stop database -d racdb
srvctl start database -d racdb
set linesize 500 pages 0
col value for a90
col name for a50
select name, value
from v$parameter
where name in ('db_name','db_unique_name',
'log_archive_config',
'log_archive_dest_1','log_archive_dest_2',
'log_archive_dest_state_1',
'log_archive_dest_state_2',
'remote_login_passwordfile',
'log_archive_format',
'log_archive_max_processes',
'fal_server','db_file_name_convert',
'log_file_name_convert',
'standby_file_management')
/
db_file_name_convert +DATA, +DATA
log_file_name_convert +DATA, +DATA
log_archive_dest_1 location=+DATA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=racdb
log_archive_dest_2 SERVICE=racdbdg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=racdbdg
log_archive_dest_state_1 ENABLE
log_archive_dest_state_2 ENABLE
fal_server RACDBDG
log_archive_config DG_CONFIG=(racdb,racdbdg)
log_archive_format %t_%s_%r.dbf
log_archive_max_processes 4
standby_file_management AUTO
remote_login_passwordfile EXCLUSIVE
db_name racdb
db_unique_name racdb
5.主库备份(可做可不做）
全备
backup database format '/home/oracle/backup/%d_%U.full';
sql 'alter system archive log current';
backup archivelog all format '/home/oracle/backup/%d_%U.archivelog';
backup current controlfile format '/home/oracle/backup/%d_%U.ctl';
}
生成standby控制文件
backup device type disk format '/home/oracle/backup/standby_%U.ctl' current controlfile for standby;
6.复制密码文件
scp orapwracdb1 192.168.0.61:/u01/app/oracle/product/11.2.0.4/db_1/dbs
scp orapwracdb1 192.168.0.62:/u01/app/oracle/product/11.2.0.4/db_1/dbs
mv orapwracdb1 orapwracdbdg1
mv orapwracdb1 orapwracdbdg2
7.修改备库监听（主库监听可不修改，生产环境最好不动）、修改tnsnames.ora（两个节点上做）
racdbdg1:
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME= racdbdg)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/db_1/)
(SID_NAME = racdbdg1)
)
)
racdbdg2:
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME= racdbdg)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/db_1/)
(SID_NAME = racdbdg2)
)
)
8.修改tnsnames.ora(主备库所有节点都做）
tnsnames.ora:
RACDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.25)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.23)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.24)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racdb)
)
)
RACDBDG =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.65)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.63)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.64)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racdbdg)
)
)
9.主备库都测试联通性：
sqlplus sys/oracle123@racdb as sysdba
sqlplus sys/oracle123@racdbdg as sysdba
10.修改备库初始化参数文件
主库创建
create pfile='/tmp/racdb.ora' from spfile;
vi /tmp/racdbdg.ora
*.audit_file_dest='/u01/app/oracle/admin/racdbdg/adump'
*.audit_trail='none'
*.cluster_database=true
*.compatible='11.2.0.4.0'
*.control_files='+DATA/racdbdg/controlfile/control01.ctl'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_file_name_convert='+DATA','+DATA'
*.db_name='racdb'
*.service_names='racdb'
*.db_unique_name='racdbdg'
*.diagnostic_dest='/u01/app/oracle'
*.fal_client='racdbdg1,racdbdg2'
*.fal_server='RACDB'
racdbdg1.instance_number=1
racdbdg2.instance_number=2
*.log_archive_config='DG_CONFIG=(racdb,racdbdg)'
*.log_archive_dest_1='location=+DATA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=racdbdg'
*.log_archive_dest_2='SERVICE=racdb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=racdb'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_file_name_convert='+DATA/racdb','+DATA/racdbdg'
*.memory_target=1202716672
*.open_cursors=300
*.processes=150
*.remote_listener='racdgscanip:1521'
*.remote_login_passwordfile='exclusive'
*.sessions=1655
*.standby_file_management='AUTO'
racdbdg2.thread=2
racdbdg1.thread=1
racdbdg1.undo_tablespace='UNDOTBS1'
racdbdg2.undo_tablespace='UNDOTBS2'
创建相关的路径：
On Node 1
mkdir -p /u01/app/oracle/admin/racdbdg/adump
chmod 775 /u01/app/oracle/admin/racdbdg/adump
chown -R oracle:oinstall /u01/app/oracle/admin/racdbdg/adump
On Node 2
# mkdir -p /u01/app/oracle/admin/racdbdg/adump
# chmod 775 /u01/app/oracle/admin/racdbdg/adump
#chown -R oracle.oinstall /u01/app/oracle/admin/racdbdg/adump
创建ASM路径
# su - grid
asmcmd
ASMCMD> cd DATA
ASMCMD> ls
ASMCMD> mkdir racdbdg
ASMCMD> cd racdbdg
ASMCMD> ls
ASMCMD> mkdir controlfile
ASMCMD> ls
controlfile/
11.以pfile启动数据库到nomount：
startup nomount pfile='/tmp/racdbdg.ora';
12.duplicate 复制数据
oracleold@whepi1 ~]$ vi /home/oracle/duplicate.sh
rman target sys/oracle123@racdb auxiliary sys/oracle123@racdbdg nocatalog > duplicate.log <<EOF
duplicate target database for standby from active database nofilenamecheck;
EOF
[oracleold@whepi1 ~]$ cd ~
[oracleold@whepi1 ~]$ chmod a+x duplicate.sh
[oracleold@whepi1 ~]$ nohup ./duplicate.sh &
观察duplicate.log日志查看复制数据是否成功
在RMAN DUPLICATE操作进行过程中，standby database自动切换到mount模式
select instance_name,status from v$instance;
同步归档
以下为同步归档，预计时间比较久，可以先做后面的步骤
SQL> alter database recover managed standby database disconnect from session;
使用11G Data Guard新特性，Archive Data Guard
在备库运行：
recover managed standby database cancel;
alter database open read only;
alter database recover managed standby database using current logfile disconnect from session;
检验同步是否正常
在主库运行，确认是否有断层
SQL> select STATUS, GAP_STATUS from V$ARCHIVE_DEST_STATUS where DEST_ID = 2;
STATUS GAP_STATUS
--------- ------------------------
VALID NO GAP
备库查询MRP进程状态：
select inst_id,process,status,thread#,sequence#,block# from gv$managed_standby where PROCESS like 'MRP%';
主库创建临时表确认是否正常同步
create table test2020 as select * from dual;
select * from test2020;
切换前检验是否同步正常
alter system switch logfile;
主库：
--检查主库已经产生的日志sequence# 号
select thread#,max(sequence#) "Last Primary Seq Generated" from v$archived_log val,v$database vdb where val.resetlogs_change#=vdb.resetlogs_change# group by thread# order by 1;
备库：
-- 检查备库已经接收到的 sequence# 号
select thread#,max(sequence#) "Last Standby Seq Received" from v$archived_log val,v$database vdb where val.resetlogs_change#=vdb.resetlogs_change# group by thread# order by 1;
-- 检查备库已经应用到的 sequence# 号
select thread#,max(sequence#) "Last Standby Seq Applied" from v$archived_log val,v$database vdb where val.resetlogs_change#=vdb.resetlogs_change# and val.applied in ('YES','IN-MEMORY') group by thread# order by 1;
主库：
select a.dest_id,a.thread#,a.sequence#,b.archived,b.applied from
(select dest_id,thread#,sequence#,archived,applied from (
select dest_id,thread#,sequence#,archived,applied,row_number() over ( partition by dest_id order by completion_time DESC ) rn
from v$archived_log
where dest_id in
(select regexp_substr(name,'[0-9]+')
from v$parameter a
where a.NAME like 'log_archive_dest_%'
and upper(value) like '%LOCATION%')
order by completion_time desc
) where rn<=10) a,
(
select dest_id,thread#,sequence#,archived,applied
from v$archived_log
where dest_id in
(select regexp_substr(name,'[0-9]+')
from v$parameter a
where a.NAME like 'log_archive_dest_%'
and upper(value) like '%LGWR%')
) b
where a.thread#=b.thread#(+) and a.sequence#=b.sequence#(+);
缺少的归档注册在备库：
ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1';
13.备库启用RAC Database
创建spfile：
create spfile='+DATA' from pfile='/tmp/racdbdg.ora';
spfile='+data/racdbdg/PARAMETERFILE/spfile.265.1056023039'
mv /tmp/racdbdg.ora /u01/app/oracle/product/11.2.0.4/db_1/dbs/
修改节点1pfile：
vi initracdbdg1.ora
spfile='+data/racdbdg/PARAMETERFILE/spfile.265.1056023039'
节点1重启：
recover managed standby database cancel;
shutdown immediate
startup
select open_mode from gv$database;
OPEN_MODE
------------------------------------------------------------
READ ONLY
修改节点2pfile：
vi initracdbdg2.ora
spfile='+data/racdbdg/PARAMETERFILE/spfile.265.1056023039'
将Standby Database信息注册到grid中
oracle用户： /home/oracle/目录下执行
srvctl add database -d racdbdg -n racdbdg -o /u01/app/oracle/product/11.2.0.4/db_1 -p +data/racdbdg/PARAMETERFILE/spfile.265.1056023039 -r physical_standby -a DATA
srvctl config database -d racdbdg
srvctl add instance -d racdbdg -i racdbdg1 -n racdg1
srvctl add instance -d racdbdg -i racdbdg2 -n racdg2
重启数据库
shutdown immediate
srvctl start db -d racdbdg
crsctl stat res -t
srvctl config db -d racdbdg
SQL> select name,database_role,open_mode from gv$database;
NAME DATABASE_ROLE OPEN_MODE
--------- ---------------- --------------------
WMHWZ PHYSICAL STANDBY READ ONLY
WMHWZ PHYSICAL STANDBY READ ONLY
再次开启同步归档操作，单个节点操作即可
alter database recover managed standby database using current logfile disconnect from session;
节点2创建本地spfie：
create spfile from pfile;
14.数据同步验证
create table test2020 as select * from dual;
select * from test2020;
drop table test2020 purge;
select * from test2020;
15.设置备库定时清理归档脚本
vi /home/oracle/shback/delete_archivelog.sh
#!/bin/sh
source ~/.bash_profile
export ORACLE_SID=racdb1
rman target / <<EOF
crosscheck archivelog all;
delete NOPROMPT expired archivelog all;
delete noprompt archivelog all completed before 'sysdate -2';
delete noprompt backup completed before 'sysdate -2';
exit;
EOF
chmod a+x /home/oracle/shback/delete_archivelog.sh
crontab -e
37 16 * * * sh /home/oracle/shback/delete_archivelog.sh >> /home/oracle/shback/delete_archivelog.sh.out 2>&1