demoo(Oracle DG 最佳实践)

场景: 主库:Oracle 11g rac,rhel 6.10; 备库:Oracle 11g 单实例,rhel 6.10; 一、备库端操作 1、网络服务名和监听(netmgr) ---a、网络名more $ORACLE_HOME/network/admin/tnsname.or...

场景:

主库:Oracle 11g rac,rhel 6.10;

备库:Oracle 11g 单实例,rhel 6.10;

Oracle DG 最佳实践

一、备库端操作

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,大家关注下吧。

  • 发表于 2022-11-02 15:25:04
  • 阅读 ( 124 )
  • 分类:科技

0 条评论

请先 登录 后评论
一无所有
一无所有

313 篇文章

你可能感兴趣的文章

相关问题