RAC环境上搭建DG

首先RAC要确实是开归档的状态
archive log list;
如果是非归档状态，需要执行下面几步
srvctl stop database -d +数据库实例名 关闭数据库
--节点1(要做DG主库的)
sql>startup mount
sql> alter database archivelog;
sql>alter system set log_archive_dest_1='LOCATION=/oracleapp/arch' scope=spfile sid='prod1';
sql>shutdown immediate

srvctl start database -d +数据库实例名

RAC环境上搭建DG
查看当前数据库的日志文件组
select group#,thread#,bytes/1024/1024,status from v$standby_log;
为主库添加几组组standby log文件，以便它们自动被传送到备库。（一般比主库多一组）
alter database add standby logfile thread 1 group 11 size 50m;
alter database add standby logfile thread 1 group 12 size 50m;
alter database add standby logfile thread 1 group 13 size 50m;
alter database add standby logfile thread 2 group 14 size 50m;
alter database add standby logfile thread 2 group 15 size 50m;
alter database add standby logfile thread 2 group 16 size 50m;

查询添加的日志文件
select group#,thread#,bytes/1024/1024,status from v$standby_log;

修改主库初始化参数文件
SQL> alter system set log_archive_config='DG_CONFIG=(prod,prod_dg)' scope=both sid='*';
SQL> alter system set log_archive_dest_2='SERVICE=o4db_dg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prod_dg' scope=both sid='*';
SQL> alter system set log_archive_format='%t_%s_%r.arc' scope=spfile sid='*';
SQL> alter system set log_archive_max_processes=8 scope=both sid='*';
SQL> alter system set db_file_name_convert='/oradata/rac/','+DATA/rac/datafile' scope=spfile sid='*';
SQL> alter system set log_file_name_convert='/oradata/rac/','+DATA/rac/onlinelog' scope=spfile sid='*';
SQL> alter system set standby_file_management=AUTO scope=both sid='*';
SQL> alter system set fal_server='prod_dg' scope=both sid='*';

查看配置是否生效，通过下列语句查询

SQL> set linesize 500 pages 0
SQL> col value for a90
SQL> col name for a50
SQL> 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 /u01/dg/oradata/rac/, +DATA/rac/datafile
log_file_name_convert /u01/dg/oradata/rac/, +DATA/rac/onlinelog
log_archive_dest_1 LOCATION=+DATA
log_archive_dest_2 SERVICE=rac_dg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=rac_dg
log_archive_dest_state_1 enable
log_archive_dest_state_2 ENABLE
fal_server rac_dg
log_archive_config DG_CONFIG=(prod,prod_dg)
log_archive_format %t_%s_%r.arc
log_archive_max_processes 8
standby_file_management AUTO
remote_login_passwordfile EXCLUSIVE
db_name prod
db_unique_name prod

14 rows selected.

配置主库本地NET服务名（两个节点，包括standby database节点）

备库创建监听器
vi /u01/oracle/app/11.2.0/network/admin/listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.101)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)

ADR_BASE_LISTENER = /u01/app/oracle
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC= (GLOBAL_DBNAME=prod_dg)
(SID_NAME=prod_dg)
)
)

配置备库本地NET服务名（与主库一致）
PROD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.88)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prod)
)
)

PROD_DG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.101)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prod_dg)
)

主库tnsping测试（确保监听打开）

tnsping prod
tnsping prod_dg

备库tnsping测试（确保监听打开）

tnsping prod
tnsping prod_dg

在主库中创建StandbyControl File，并上传到standby节点
SQL> alter database create standby controlfile as '/tmp/control_dg.ctl';

主库做全库备份，备份数据库。这里采用热备的方式,网上有采用冷备的。要的是全备的方式备份
rman target /
run {
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
backup database format '/u01/oracle/backup/dg_%T_%s';
backup archivelog all format '/home/oracle/rman/ARC_%U.bak';
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}

拷贝到备库节点上的相关位置
scp /tmp/control_dg.ctl 192.168.3.101:/oradata/rac/control01.ctl
scp /home/oracle/rman/* 192.168.3.101:/u01/oracle/backup/

scp备份集到standby节点
scp /u01/oracle/backup/* 192.168.3.101:/u01/app/oracle/oradata/prod_dg/

为standby创建密码文件
scp /u01/oracle/app/11.2.0/dbs/orapwprod1 192.168.3.101:/u01/oracle/app/11.2.0/dbs/orapwprod_dg

standbydatabase配置部分 修改参数：
#主库上生成pfile
SQL> create pfie='/u01/oracle/backup/init.ora' from spfile;

cat /u01/oracle/backup/init.ora

prod1.__db_cache_size=130023424
prod2.__db_cache_size=130023424
prod2.__java_pool_size=4194304
prod1.__java_pool_size=4194304
prod2.__large_pool_size=4194304
prod1.__large_pool_size=4194304
prod1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
prod2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
prod2.__pga_aggregate_target=104857600
prod1.__pga_aggregate_target=104857600
prod2.__sga_target=314572800
prod1.__sga_target=314572800
prod2.__shared_io_pool_size=0
prod1.__shared_io_pool_size=0
prod1.__shared_pool_size=167772160
prod2.__shared_pool_size=167772160
prod2.__streams_pool_size=0
prod1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/prod/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.0.0'
*.control_files='+DATA/prod/controlfile/current.260.1003090585','+FRA/prod/controlfile/current.256.1003090585'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_file_name_convert='/u01/dg/oradata/rac/','+DATA/rac/datafile'
*.db_name='prod'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=4558159872
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=prodXDB)'
*.fal_server='prod_dg'
prod2.instance_number=2
prod1.instance_number=1
*.log_archive_config='DG_CONFIG=(prod,prod_dg)'
prod1.log_archive_dest_1='LOCATION=/u01/arch'
prod2.log_archive_dest_1='LOCATION=/u01/arch'
*.log_archive_dest_2='SERVICE=o4db_dg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prod_dg'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=8
*.log_file_name_convert='/u01/dg/oradata/rac/','+DATA/rac/onlinelog'
*.open_cursors=300
*.pga_aggregate_target=104857600
*.processes=150
*.remote_listener='rac-scan:1521'
*.remote_login_passwordfile='exclusive'
*.sga_target=314572800
*.standby_file_management='AUTO'
prod2.thread=2
prod1.thread=1
prod2.undo_tablespace='UNDOTBS2'
prod1.undo_tablespace='UNDOTBS1

修改信息：
prod1.__db_cache_size=130023424
prod1.__java_pool_size=4194304
prod1.__large_pool_size=4194304
prod1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
prod1.__pga_aggregate_target=104857600
prod1.__sga_target=314572800
prod1.__shared_io_pool_size=0
prod1.__shared_pool_size=167772160
prod1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/prod/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/prod_dg/control01.ctl' -- 设置备库放置控制文件的位置
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata/prod_dg' --指定Oracle数据库服务器创建数据文件的缺省路径，
--除了数据文件之外，据说还可以创建其他如日志文件、
--控制文件等文件的缺省路径，Datafiles，Tempfiles，
--- Redo log files，Block change tracking files，Control files

*.db_domain='' --Oracle的GLOBAL_NAME由两个部分组成：DB_NAME和DB_DOMAIN
--如果在建立数据库的时候不指定DB_DOMAIN的值，则GLOBAL_NAME和DB_NAME的值一样。
*.log_file_name_convert='+DATA/rac/datafile','/u01/dg/oradata/rac/' -- 与主库相反
*.db_file_name_convert='+DATA/rac/datafile','/u01/dg/oradata/rac/' -- 与主库相反
*.db_name='prod'
*.db_recovery_file_dest='/u01/flash' --闪回归档位置
*.db_recovery_file_dest_size=4558159872
*.diagnostic_dest='/u01/app/oracle' --日志位置
*.dispatchers='(PROTOCOL=TCP) (SERVICE=prodXDB)'
*.fal_server='prod_dg' --备库实例名 在一定的条件下，或者因为网络失败，
--或者因为资源紧张，会在primary和standby之间产生裂隙，
--也就是有些归档日志没有及时的传输并应用到standby库。
--因为MRP(managed recovery process)/LSP(logical standby process)没有
--与primary直接通讯的能力来获取丢失的归档日志。
--因此这些gaps通过FAL客户和服务器来解决，由初始化参数定义FAL_CLIENT和FAL_SERVER。
--这个参数适用于standby站点。比如,FAL_SERVER = PrimaryDB,此处PrimaryDB是主库db_unique_name,指向primary库。
--FAL_CLIENT指定一个FAL客户端的名字，以便FAL Server可以引用standby库，这里是备库 自己的db_unique_name，
---FAL_CLIENT和FAL_SERVER应该成对设置或改变

prod1.instance_number=1
*.log_archive_config='DG_CONFIG=(prod,prod_dg)'
prod1.log_archive_dest_1='LOCATION=/u01/arch'
*.log_archive_dest_2='SERVICE=o4db_dg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prod_dg'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=8
*.open_cursors=300
*.pga_aggregate_target=104857600
*.processes=150
*.remote_login_passwordfile='exclusive'
*.sga_target=314572800
*.standby_file_management='AUTO'
prod1.thread=1
prod1.undo_tablespace='UNDOTBS1'

 

创建相关目录路径（一定要创建全，不然启动报错）
mkdir -p /u01/arch
mkdir -p /u01/app/oracle
mkdir -p /u01/app/oracle/oradata/prod_dg
。。。。。。。。

启动到mount状态

SQL> startup mount;

#备库恢复控制文件
SQL> startup nomount;
rman target /
RMAN> restore standby controlfile from '/u01/app/oracle/oradata/prod_dg/control01.ctl';
RMAN> alter database mount;

#注册备份集
RMAN> CATALOG START WITH '/u01/oracle/backup/';
RMAN> CROSSCHECK BACKUP;

rman target /
run
{
set newname for datafile 1 to '/u01/app/oracle/oradata/prod_dg/system01.dbf';
set newname for datafile 2 to '/u01/app/oracle/oradata/prod_dg/sysaux01.dbf';
set newname for datafile 3 to '/u01/app/oracle/oradata/prod_dg/undotbs01.dbf';
set newname for datafile 4 to '/u01/app/oracle/oradata/prod_dg/undotbs02.dbf';
set newname for datafile 5 to '/u01/app/oracle/oradata/prod_dg/users01.dbf';
set newname for datafile 6 to '/u01/app/oracle/oradata/prod_dg/bylgt01.dbf';
set newname for tempfile 1 to '/u01/app/oracle/oradata/prod_dg/temp01.dbf';
restore database;
switch datafile all;
switch tempfile all;
}

#把归档从主库中copy到备库后，进行手工recover
#根据备库alter.log查看缺失的日志sequence号
#从ASM上copy归档到本地再scp至备库
$su – grid
$asmcmd
ASMCMD> cd +ARCDG/repprod/AR*/2018_04_18
ASMCMD> cp thread_1_seq_1741.2019.973785643 /u01/oracle/backup/
ASMCMD> cp thread_1_seq_1942.2019.973785651 /u01/oracle/backup/

$cd /u01/oracle/backup/
$ scp thread_1_seq* 192.168.3.101:/u01/oracle/backup/

 

#备库上注册归档并recover database
SQL> alter database register logfile '/u01/oracle/backup/thread_1_seq_1741.2019.973785643';
SQL> alter database register logfile '/u01/oracle/backup/thread_1_seq_1942.2019.973785651';

SQL> recover managed standby database disconnect from session;

Media recovery complete.

SQL> recover managed standby database cancel;
Media recovery complete.

开库

alter database open;

个人建议在执行这一步一定要看一下日志，看是否报错，如果出现下面的情况就是错误的，

alter system switch logfile;  -- 看一下日志上是否同步

 

 

 

处理办法，参考网上 http://blog.itpub.net/25583515/viewspace-2156171  

主库到standby报错解决：Error 12154 received logging on to the standby ORA-12154

解决问题后，

alter database recover managed standby database cancel;
alter database open read only;
alter database recover managed standby database using current logfile disconnect from session;

测试操作
--主库执行
create table test1 as select * from scott.emp;

备库查询数据与主库一致：
SQL> select count(*) from test1;

COUNT(*)
----------
14

 

 至此，搭建完成，可以执行相关其他操作