场景:
主库:Oracle 11g rac,rhel 6.10;
备库:Oracle 11g 单实例,rhel 6.10;
一、备库端操作
1、网络服务名和监听(netmgr)
---a、网络名
more $ORACLE_HOME/network/admin/tnsname.oraADGDEMO =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = ADGDEMO)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = ADGDEMO)))demo =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.90.10)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = demo)))
备注:tnsping demo
---b、监听,备注,使用netmgr,database services
more $ORACLE_HOME/network/admin/listener.oraLISTENER =(DESCRIPTION_LIST =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = adgdemo)(PORT = 1521))(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))))SID_LIST_LISTENER =(SID_LIST =(SID_DESC =(GLOBAL_DBNAME = adgdemo)(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)(SID_NAME = adgdemo)))ADR_BASE_LISTENER = /u01/app/oracle
2、初始化参数文件(nomount状态)
---a、
SQL>startup nomount;alter system set db_name='demo' scope=spfile sid='*';alter system set db_unique_name='adgdemo' scope=spfile sid='*';alter system set log_archive_config='DG_CONFIG=(demo,adgdemo)' scope=both sid='*';
---b、归档路径
mkdir /u01/app/oracle/oraarch/adgdemo
SQL>alter system set log_archive_dest_1='location=/u01/app/oracle/oraarch/adgdemo VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=adgdemo' scope=both sid='*';alter system set log_archive_dest_2='SERVICE=demo LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 net_timeout=30 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=demo' scope=both sid='*';
---c、
SQL>alter system set fal_server='demo' scope=both sid='*';alter system set fal_client='adgdemo' scope=both sid='*';alter system set standby_file_management=AUTO scope=both sid='*';alter system set control_files='/u01/app/oracle/oradata/ADGDEMO/controlfile/control01.ctl' scope=spfile sid='*';shutdown immediate;startup nomount;
---d、配置完参数需重启数据库和监听
lsnrctl stop
lsnrctl start
二、主库端配置
1、配置网络名(netca,netmgr)
more $ORACLE_HOME/network/admin/tnsname.orademo =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = demo-scan)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = demo)))demoO1 =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = demo1-vip)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = demo)))demoO2 =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = demo2-vip)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = demo)))ADGDEMO =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.93.195)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = adgdemo)))
2、主库开启归档
SQL>shutdown immediatestartup mountalter database archivelog;alter database force logging;alter database open
3、主库配置DG参数与归档(startup状态)
SQL>alter system set log_archive_config='DG_CONFIG=(demo,adgdemo)' scope=both sid='*';alter system set log_archive_dest_1='location=+FRA_DG valid_for=(all_logfiles,all_roles) db_unique_name=demo' scope=both sid='*';alter system set log_archive_dest_2='SERVICE=adgdemo LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 net_timeout=30 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=adgdemo' scope=both sid='*';alter system set fal_server='adgdemo' scope=both sid='*';alter system set fal_client='demo' scope=both sid='*';alter system set standby_file_management=AUTO scope=both sid='*';shutdown immediate;startup
4、主库添加备用日志组
SQL>alter database add standby logfile thread 1 group 5 '+DATA_DG' size 200m;alter database add standby logfile thread 1 group 6 '+DATA_DG' size 200m;alter database add standby logfile thread 1 group 7 '+DATA_DG' size 200m;alter database add standby logfile thread 2 group 8 '+DATA_DG' size 200m;alter database add standby logfile thread 2 group 9 '+DATA_DG' size 200m;alter database add standby logfile thread 2 group 10 '+DATA_DG' size 200m;
注意
standby logfile 要比redo logfile的数量多两个,用于缓冲。
5、rman备份
a、
RMAN>backup as compressed backupset tag 'demofull' format '/u01/app/oracle/backupsets/full_%d_%T_%s_%p_%c' database plus archivelog tag 'demoarch' format '/u01/app/oracle/backupsets/arch_%d_%T_%s_%p_%c';
b、
SQL>alter database create standby controlfile as '/u01/app/oracle/backupsets/control.bak';
c、密码文件:
[oracle@adgdemo]$ scp ./ORACLE_HOME/dbs/orapwadgdemo1 192.168.93.195:/$ORACLE_HOME/dbs/orapwadgdemo
三、备库rman还原
a、
nomountrestore controlfile from '/u01/app/oracle/backupsets/control.bak';alter database mount;catalog start with '/u01/app/oracle/backupsets';crosscheck backup;crosscheck copy;delete noprompt expired backup;delete noprompt expired copy;run{allocate channel c1 device type disk;allocate channel c2 device type disk;set newname for database to new;set newname for tempfile 1 to new;set newname for tempfile 2 to new;set newname for tempfile 3 to new;restore database;switch datafile all;switch tempfile all;release channel c1;release channel c2;}
四、备库添加备用日志组
SQL>alter database add standby logfile thread 1 group 5 size 200m;alter database add standby logfile thread 1 group 6 size 200m;alter database add standby logfile thread 1 group 7 size 200m;alter database add standby logfile thread 2 group 8 size 200m;alter database add standby logfile thread 2 group 9 size 200m;alter database add standby logfile thread 2 group 10 size 200m;
五、备库开始同步
a、单节点应用日志
alter database recover managed standby database disconnect from session;alter system set log_file_name_convert='demo','adgdemo' scope=spfile;alter database recover managed standby database cancel;alter database open;
b、启用日志实时应用
alter database recover managed standby database using current logfile disconnect from session;
c. 查看主备库状态
主库执行,确认最新归档是否已传至备库:
col destination for a20select destination,status,archived_thread#,archived_seq# from v$archive_dest_status where status<>'DEFERRED' and status <>'INACTIVE';DESTINATION STATUS ARCHIVED_THREAD# ARCHIVED_SEQ#-------------------- --------------------------- ---------------- -------------+FRA_DG VALID 1 24adgdemo VALID 2 24
查看备库是否在应用日志进行恢复
select process,thread#,sequence#,status,client_process from v$managed_standby;PROCESS THREAD# SEQUENCE# STATUS CLIENT_P--------- ---------- ---------- ------------ --------ARCH 0 0 CONNECTED ARCHARCH 2 8 CLOSING ARCHARCH 0 0 CONNECTED ARCHARCH 1 24 CLOSING ARCHRFS 0 0 IDLE ARCHRFS 0 0 IDLE ARCHRFS 0 0 IDLE UNKNOWNRFS 2 9 IDLE LGWRRFS 0 0 IDLE UNKNOWNRFS 1 25 IDLE LGWRMRP0 1 25 APPLYING_LOG N/A11 rows selected.
主备库检查日志序列一致性
select max(sequence#)from v$archived_log;
结束语:只要按照以上步骤操作,我感觉搭建DG应该问题不大,由于经常需要搭建此类环境,准备写成shell,大家关注下吧。