首先RAC要确实是开归档的状态
archive log list;如果是非归档状态,需要执行下面几步srvctl stop database -d +数据库实例名 关闭数据库--节点1(要做DG主库的)sql>startup mountsql> alter database archivelog;sql>alter system set log_archive_dest_1='LOCATION=/oracleapp/arch' scope=spfile sid='prod1';sql>shutdown immediatesrvctl 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 a90SQL> col name for a50SQL> 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/datafilelog_file_name_convert /u01/dg/oradata/rac/, +DATA/rac/onlineloglog_archive_dest_1 LOCATION=+DATAlog_archive_dest_2 SERVICE=rac_dg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=rac_dglog_archive_dest_state_1 enablelog_archive_dest_state_2 ENABLEfal_server rac_dglog_archive_config DG_CONFIG=(prod,prod_dg)log_archive_format %t_%s_%r.arclog_archive_max_processes 8standby_file_management AUTOremote_login_passwordfile EXCLUSIVEdb_name proddb_unique_name prod14 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.ctlscp /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_dgstandbydatabase配置部分 修改参数:
#主库上生成pfileSQL> create pfie='/u01/oracle/backup/init.ora' from spfile;cat /u01/oracle/backup/init.ora
prod1.__db_cache_size=130023424
prod2.__db_cache_size=130023424prod2.__java_pool_size=4194304prod1.__java_pool_size=4194304prod2.__large_pool_size=4194304prod1.__large_pool_size=4194304prod1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environmentprod2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environmentprod2.__pga_aggregate_target=104857600prod1.__pga_aggregate_target=104857600prod2.__sga_target=314572800prod1.__sga_target=314572800prod2.__shared_io_pool_size=0prod1.__shared_io_pool_size=0prod1.__shared_pool_size=167772160prod2.__shared_pool_size=167772160prod2.__streams_pool_size=0prod1.__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=2prod1.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=2prod1.thread=1prod2.undo_tablespace='UNDOTBS2'prod1.undo_tablespace='UNDOTBS1修改信息:
prod1.__db_cache_size=130023424prod1.__java_pool_size=4194304prod1.__large_pool_size=4194304prod1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environmentprod1.__pga_aggregate_target=104857600prod1.__sga_target=314572800prod1.__shared_io_pool_size=0prod1.__shared_pool_size=167772160prod1.__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是一个TNS name,指向primary库。--FAL_CLIENT指定一个FAL客户端的名字,以便FAL Server可以引用standby库,这也是一个TNS name,--primary库必须适当配置此TNS name指向stanby库。这个参数也是在standby库端设置。---比如,FAL_CLIENT = StandbyDB,StandbyDB是standby库的TNS 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=1prod1.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$asmcmdASMCMD> cd +ARCDG/repprod/AR*/2018_04_18ASMCMD> 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.一定要是在主库是open 的情况下开库,当然如果是在想ADG 那就不存在该问题了
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
至此,搭建完成,可以执行相关其他操作