ADG搭建系列之 11G RAC to Single DATABASE

615 阅读3分钟

「这是我参与11月更文挑战的第24天,活动详情查看:2021最后一次更文挑战

一、环境准备 

 主机名ipDB Versiondb_namedb_unique_name
主库节点一lucifer0110.211.55.10011.2.0.4orclorcl
主库节点二lucifer0210.211.55.10111.2.0.4orclorcl
备库luciferdg10.211.55.11011.2.0.4orclorcldg

Notes:

1、db_unique_name主备库不能相同。

2、db_name主备库需保持一致。

3、主备库DB版本需保持一致。

二、搭建过程

1、Oracle软件安装

主库一键安装:

./AllRacOracleSilent.sh -i 10.211.55.101 -n lucifer02 -o orcl -d 11g -rac Y -n1 lucifer01 -n2 lucifer02 -pb1 10.211.55.100 -pb2 10.211.55.101 -vi1 10.211.55.102 -vi2 10.211.55.103 -pi1 10.10.1.1 -pi2 10.10.1.2 -si 10.211.55.105 -sn lucifer-scan -cn lucifer-cluster -dd /dev/asm_data -od /dev/asm_ocr -puf eth0 -prf eth1

./AllRacOracleSilent.sh -i 10.211.55.100 -n lucifer01 -o orcl -d 11g -rac Y -n1 lucifer01 -n2 lucifer02 -pb1 10.211.55.100 -pb2 10.211.55.101 -vi1 10.211.55.102 -vi2 10.211.55.103 -pi1 10.10.1.1 -pi2 10.10.1.2 -si 10.211.55.105 -sn lucifer-scan -cn lucifer-cluster -dd /dev/asm_data -od /dev/asm_ocr -puf eth0 -prf eth1

备库一键安装:(备库仅安装ORACLE软件,不建库)

./AllRacOracleSilent.sh -i 10.211.55.110 -n luciferdg -o orcl -d 11g -w Y

一键安装脚本可参考ORACLE一键安装单机11G/12C/18C/19C并建库脚本

2、环境配置

a.配置hosts文件

主库:

##节点一
#dg
10.211.55.110 luciferdg

##节点二
#dg
10.211.55.110 luciferdg

备库:

##dg
10.211.55.100 lucifer01
10.211.55.101 lucifer02
10.211.55.105 lucifer-scan

b.配置静态监听和TNS

主库+备库:

##listener.ora
##节点一
su - oracle -c "cat <<EOF >> /u01/app/oracle/product/11.2.0/db/network/admin/listener.ora
##FOR DG BEGIN
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db)
      (SID_NAME = orcl01)
    )
  )
##FOR DG END
EOF"

##节点二
su - oracle -c "cat <<EOF >> /u01/app/oracle/product/11.2.0/db/network/admin/listener.ora
##FOR DG BEGIN
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db)
      (SID_NAME = orcl02)
    )
  )
##FOR DG END
EOF"

##DG
su - oracle -c "cat <<EOF >> /u01/app/oracle/product/11.2.0/db/network/admin/listener.ora
##FOR DG BEGIN
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db)
      (SID_NAME = orcl)
    )
  )
##FOR DG END
EOF"
 
##重启监听
##rac
su - grid -c "lsnrctl stop"
su - grid -c "lsnrctl start"

##dg
su - oracle -c "lsnrctl stop"
su - oracle -c "lsnrctl start"
 
##tnsnames.ora
su - oracle -c "cat <<EOF >> /u01/app/oracle/product/11.2.0/db/network/admin/tnsnames.ora
##FOR DG BEGIN
ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = lucifer-scan)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

ORCL1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = lucifer01)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

ORCL2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = lucifer02)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )
ORCLDG =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = luciferdg)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )
##FOR DG BEGIN
EOF"

c.主库配置参数

--开启归档模式
srvctl stop database -d orcl -o immediate
srvctl start instance -d orcl -i orcl1 -o mount
alter database archivelog;
alter database open;
srvctl start instance -d orcl -i oorcl2
 
--开启强制日志模式
alter database force logging;

d.复制参数文件和密码文件至备库

##复制参数文件至备库(备库执行),要在oracle用户下复制
su - oracle
scp oracle@lucifer01:/tmp/initorcldg.ora /tmp

##备库创建目录
mkdir -p /u01/app/oracle/admin/orcl/adump
su - oracle -c "mkdir -p /oradata/orcl/datafile"
su - oracle -c "mkdir -p /oradata/orcl/onlinelog"
su - oracle -c "mkdir -p /oradata/orcl/tempfile"
mkdir -p /u01/app/oracle/fast_recovery_area/orcl
chown -R oracle:oinstall /u01/app/oracle/admin/orcl/adump
chown -R oracle:oinstall /u01/app/oracle/fast_recovery_area
 
##备库修改参数文件
*._optimizer_cartesian_enabled=FALSE
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='NONE'
*.compatible='11.2.0.4.0'
*.db_block_size=8192
*.control_files='/oradata/orcl/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl/control02.ctl'
*.db_create_file_dest='/oradata/orcl'
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=5501878272
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.event='10949 trace name context forever:28401 trace name context forever,level 1:10849 trace name context forever, level 1:19823 trace name context forever, level 90'
*.open_cursors=300
*.pga_aggregate_target=196083712
*.processes=150
*.result_cache_max_size=0
*.sga_target=784334848
*.db_unique_name='orcldg'
*.log_archive_config='dg_config=(ORCLDG,ORCL)'
*.log_archive_dest_1='location=/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=ORCLDG'
*.log_archive_dest_2='service=orcl async valid_for=(online_logfiles,primary_role) db_unique_name=ORCL'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=4
*.remote_login_passwordfile='exclusive'
*.fal_server='ORCL'
*.fal_client='ORCLDG'
*.db_file_name_convert='+DATA','/oradata'
*.log_file_name_convert='+DATA','/oradata'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
 
##复制密码文件至备库(备库执行),要在oracle用户下复制
su - oracle
scp oracle@lucifer01:/u01/app/oracle/product/11.2.0/db/dbs/orapworcl1 /u01/app/oracle/product/11.2.0/db/dbs/orapworcl

e.主库添加stanby log文件

set pagesize100
set line222
col member for a60
select * from v$logfile;
select * from v$log;
 
--需要注意:
--1.stanby log日志大小与redo log日志保持一致
--2.stanby log数量: 
standby logfile=(1+logfile组数)*thread=(1+3)*1=4组,需要加4组standby logfile.
--3.thread要与redo log保持一致,如果是rac,需要增加多个thread对应的standby log
 
ALTER DATABASE ADD STANDBY LOGFILE thread 1 
group 5 ('+DATA') SIZE 120M,
group 6 ('+DATA') SIZE 120M,
group 7 ('+DATA') SIZE 120M;

ALTER DATABASE ADD STANDBY LOGFILE thread 2
group 8 ('+DATA') SIZE 120M,
group 9 ('+DATA') SIZE 120M,
group 10 ('+DATA') SIZE 120M;

select * from v$standby_log;

f.备库开启到nomount状态

startup nomount pfile='/tmp/initorcldg.ora';

3、 RMAN DUPLICATE

##rman恢复备库
rman target sys/oracle@orcl1 AUXILIARY sys/oracle@orcldg
 
run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate auxiliary channel aux1 type disk;
allocate auxiliary channel aux2 type disk;
DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER NOFILENAMECHECK;
}

##主库设置DG参数
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCL,ORCLDG)';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=+DATA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORCL';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=orcldg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLDG';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=4;
ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;
ALTER SYSTEM SET FAL_SERVER=ORCLDG;
ALTER SYSTEM SET FAL_CLIENT=ORCL;
ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/oradata','+DATA' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/oradata','+DATA'  SCOPE=SPFILE;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

4、备库开启日志应用

alter database open read only;
 
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
 
select database_role,open_mode from v$database;
 
DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY READ ONLY WITH APPLY
 
SQL> SELECT protection_mode FROM v$database;
 
PROTECTION_MODE
--------------------
MAXIMUM PERFORMANCE

5、主库开启LOG_ARCHIVE_DEST_STATE_2

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;