Oracle 11g 安装部署新特性

oracle11g单实例一主二备DG搭建

2020年1月25日2020年12月11日 handsup

主库：
ip：192.168.174.154     系统：linux6.8   数据库：oracle11g单实例    sys密码：123456  
db_name=orcl,即instance_name、db_unique_name 、service_name都为orcl

备库1：
ip：192.168.174.155     系统：linux6.8   数据库：oracle11g单实例    sys密码：123456 

备库2：
ip：192.168.174.156     系统：linux6.8   数据库：oracle11g单实例    sys密码：123456 

搭建前准备：
需要在155、156两台服务器先行安装好oracle11g数据库，且都未创建实例，备库的db_name一定与主库一致，db_unique_name，3个库需要一定不一样，其余随意

1、搭建dataguard

判断主库DG是否已经安装：
  select * from v$option where parameter = 'Oracle Data Guard';
  如果是true表示已经安装可以配置，否则需要安装相应组件。
如果不知道主库sys密码，修改主库sys用户密码：
alter user sys identified by 123456;

确认主库是否开启archivelog与force log 
select log_mode,force_logging from 
v$database; --检查
alter database force logging; --开启

archive log list;
alter database archivelog;

select log_mode,force_logging from v$database; --再次检查

2、配置主、备库监听、tnsmaes（主库监听尽量不动，备库必须是静态监听）

备库:必须是静态监听（GLOBAL_DBNAME服务名，可自定义，tnsnames.ora文件中的service_name与之一致；SID_NAME与数据库实例名一致）
su - oracle
cd $ORACLE_HOME/network/admin
vi listener.ora
如下语句：

备库1：

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.174.155)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcldg1)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/db_1)
      (SID_NAME = orcldg1)
    )
  )

备库2：

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.174.156)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcldg2)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/db_1)
      (SID_NAME = orcldg2)
    )
  )

启动监听
lsnrctl start
然后再查看监听状态，”UNKNOWN”说明是静态监听。
lsnrctl status

主备库都配置$ORACLE_HOME/network/admin/tnsnames.ora（以下每个tns的第一行为tnsnames描述符）-->基本建议tnsnames描述符设置为和db_unique_name一致。

orcl =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.174.154)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

orcldg1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.174.155)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcldg1)
    )
  )

orcldg2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.174.156)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcldg2)
    )
  )

3.--使用下面的命令修改主库参数(此时主库应当使用 spfile 启动参数),主库能不动尽量不动

注意以下几点：

LOG_ARCHIVE_CONFIG='DG_CONFIG(db_unique_name, db_unique_name, ... )' 主库与备库端采用相同设置。

LOG_ARCHIVE_DEST_n ='SERVICE=..... SERIVCE: 用于指定备用数据库的 TNSNAMES 描述符

db_file_name_convert、 log_file_name_convert 参数值为路径，可以直接写,db_unique_name。如果使用 ASM，可以设置为*.db_file_name_convert =('+DATA','+RECOVERY')

fal_server、 fal_client 参数值为 TNSNAMES 描述符

以下为本次实验主库参数设置：
alter system set db_unique_name='orcl' scope=spfile sid='*';
alter system set instance_name='orcl' scope=spfile sid='*';
alter system set service_names='orcl' scope=spfile sid='*';

alter system set log_archive_config='DG_CONFIG=(orcl,orcldg1,orcldg2)' scope=both sid='*';    //--DG_CONFIG填db_unique_name
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/oradata/orcl/arch  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl' scope=both sid='*';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=orcldg1 LGWR ASYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcldg1' scope=both sid='*';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='SERVICE=orcldg2 LGWR ASYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcldg2' scope=both sid='*';//一主2备时用
alter system set log_archive_dest_state_1=enable scope=both sid='*';
alter system set log_archive_dest_state_2=enable scope=both sid='*';
alter system set log_archive_dest_state_3=enable scope=both sid='*';
alter system set log_archive_max_processes=4 scope=both sid='*';//可不写
alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=both sid='*';
alter system set  local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.174.154)(PORT=1521))))' scope=both sid='*';
alter system set fal_server='orcldg1,orcldg2' scope=both sid='*';//客端
alter system set fal_client='orcl' scope=both sid='*';//主端

alter system set remote_login_passwordfile='EXCLUSIVE' scope=spfile sid='*';

//这两个参数主库可以不写，具体查看主备切换文档
alter system set db_file_name_convert='/u01/app/oracle/oradata/orcl/','/orcldg1/datafile/' scope=spfile sid='*';  前主后备,主要作用传输数据文件并改名
alter system set log_file_name_convert='/u01/app/oracle/oradata/orcl/','/orcldg1/logfile/ scope=spfile sid='*';  前主后备，主要作用传输日志文件并改名

4.配置主库standby redo log，并验证结果

查看是否已经存在standby redo log
select group#, thread#, sequence#, archived, status from v$standby_log;

存在就不用按照以下内容创建。

根据以下sql查询的结果，创建standby redo log。
select GROUP#,THREAD#,BYTES from v$log ;
select MEMBER from v$logfile;

根据以上查询出的路径及group个数，创建standby redo log，创建原则thread要一样多，同个thread中要比redo多一个group（standby redo log大小必须与主库重做日志大小一致）
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/orcl/stbredo01.log' SIZE 50m;
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/orcl/stbredo02.log' SIZE 50m;
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/orcl/stbredo03.log' SIZE 50m;
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/orcl/stbredo04.log' SIZE 50m;

检查创建standby redo log结果
select group#, thread#, sequence#, archived, status from v$standby_log;

5.备库的相应配置

将主库的密码文件、pfile拷贝到备库。

Pfile生成：
create pfile='/tmp/orcl.ora' from spfile;

将pfile文件拷贝到备库：/tmp
将密码文件拷贝到备库：$ORACLE_HOME/dbs
在备库oracle用户执行
scp oracle@192.168.174.154:/tmp/orcl.ora  /tmp
scp oracle@192.168.174.154:$ORACLE_HOME/dbs/orapworcl $ORACLE_HOME/dbs

密码文件拷贝到备库后，需要修改文件名。格式为orapw+ORACLE_SID

mv orapworcl orapworcldg1
mv orapworcl orapworcldg2

主备库都测试联通性：
sqlplus sys/123456@orcl as sysdba
sqlplus sys/123456@orcldg1 as sysdba
sqlplus sys/123456@orcldg2 as sysdba

6.备库：编辑/tmp/orcl.ora，修改为以下内容：

主库：
orcl.__db_cache_size=486539264
orcl.__java_pool_size=16777216
orcl.__large_pool_size=33554432
orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=486539264
orcl.__sga_target=721420288
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=167772160
orcl.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_flashback_retention_target=2880
*.db_name='orcl'
*.db_recovery_file_dest_size=3221225472
*.db_recovery_file_dest='/u01/database'
*.db_unique_name='orcl'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.fal_client='orcl'
*.fal_server='orcldg1,orcldg2'
*.instance_name='orcl'
*.local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.174.154)(PORT=1521))))'
*.log_archive_config='DG_CONFIG=(orcl,orcldg1,orcldg2)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/orcl/arch  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl'
*.log_archive_dest_2='SERVICE=orcldg1 LGWR ASYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcldg1'
*.log_archive_dest_3='SERVICE=orcldg2 LGWR ASYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcldg2'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_dest_state_3='ENABLE'
*.log_archive_max_processes=4
*.memory_target=1202716672
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.service_names='orcl'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

改本：

备库1：

*.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment  
*.audit_file_dest='/orcldg1/adump/'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/orcldg1/controlfile/control01.ctl','/orcldg1/controlfile/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_flashback_retention_target=2880
*.db_name='orcl'
*.db_recovery_file_dest_size=3221225472
*.db_recovery_file_dest='/orcldg1/flashback/'
*.db_unique_name='orcldg1'
*.diagnostic_dest='/u01/app/oracle'
*.fal_client='orcldg1
*.fal_server='orcl,orcldg2'
*.instance_name='orcldg1'
*.local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.174.155)(PORT=1521))))'
*.db_file_name_convert='/u01/app/oracle/oradata/orcl/','/orcldg1/datafile/' 
*.log_file_name_convert='/u01/app/oracle/oradata/orcl/','/orcldg1/logfile/'    
*.log_archive_config='DG_CONFIG=(orcl,orcldg1,orcldg2)'
*.log_archive_dest_1='LOCATION=/orcldg1/archivelog/  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcldg1'
*.log_archive_dest_2='SERVICE=orcl LGWR ASYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'
*.log_archive_dest_3='SERVICE=orcldg2 LGWR ASYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcldg2'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_dest_state_3='ENABLE'
*.log_archive_max_processes=4
*.memory_target=1202716672
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.service_names='orcldg1'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

mkdir -p /orcldg1/adump                                                                                                                         
mkdir -p /orcldg1/controlfile                                                                                                                   
mkdir -p /orcldg1/datafile                                                                                                                      
mkdir -p /orcldg1/archivelog                                                                                                                    
mkdir -p /orcldg1/logfile
mkdir -p /orcldg1/flashback

备库2：

*.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment  
*.audit_file_dest='/orcldg2/adump/'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/orcldg2/controlfile/control01.ctl','/orcldg2/controlfile/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_flashback_retention_target=2880
*.db_name='orcl'
*.db_recovery_file_dest_size=3221225472
*.db_recovery_file_dest='/orcldg2/flashback/'
*.db_unique_name='orcldg2'
*.diagnostic_dest='/u01/app/oracle'
*.fal_client='orcldg2
*.fal_server='orcl,orcldg1'
*.instance_name='orcldg2'
*.local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.174.156)(PORT=1521))))'
*.db_file_name_convert='/u01/app/oracle/oradata/orcl/','/orcldg2/datafile/' 
*.log_file_name_convert='/u01/app/oracle/oradata/orcl/','/orcldg2/logfile/'    
*.log_archive_config='DG_CONFIG=(orcl,orcldg1,orcldg2)'
*.log_archive_dest_1='LOCATION=/orcldg2/archivelog/  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcldg2'
*.log_archive_dest_2='SERVICE=orcl LGWR ASYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'
*.log_archive_dest_3='SERVICE=orcldg1 LGWR ASYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcldg1'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_dest_state_3='ENABLE'
*.log_archive_max_processes=4
*.memory_target=1202716672
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.service_names='orcldg2'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

 mkdir -p /orcldg2/adump                                                                                                                         
mkdir -p /orcldg2/controlfile                                                                                                                   
mkdir -p /orcldg2/datafile                                                                                                                      
mkdir -p /orcldg2/archivelog                                                                                                                    
mkdir -p /orcldg2/logfile
mkdir -p /orcldg2/flashback

7.备库：以pfile创建spfile并启动数据库到nomount：

SQL> create spfile from pfile='/tmp/orcldg1.ora';
SQL> startup nomount

SQL> create spfile from pfile='/tmp/orcldg2.ora';
SQL> startup nomount

8.复制数据

备库1：新建脚本vi /home/oracle/duplicate.sh ，加入以下内容：
rman target sys/123456@orcl auxiliary sys/123456@orcldg1 nocatalog > duplicate.log <<EOF
duplicate target database for standby from active database nofilenamecheck;
EOF

备库2：新建脚本vi /home/oracle/duplicate.sh ，加入以下内容：
rman target sys/123456@orcl auxiliary sys/123456@orcldg2 nocatalog > duplicate.log <<EOF
duplicate target database for standby from active database nofilenamecheck;
EOF

后台执行脚本

cd /home/oracle
chmod a+x duplicate.sh
nohup ./duplicate.sh &
观察日志duplicate.log等待完成。

9.使用11G Data Guard新特性，Archive Data Guard

在备库运行：

以下为同步归档，预计时间比较久
alter database recover managed standby database disconnect from session;
recover managed standby database cancel; 
alter database open read only;
alter database recover managed standby database using current logfile disconnect from session;

10.查看归档是否同步完成

主备库对比是否一样：
主库：
SQL> select * from ( select sequence#,applied from v$archived_log where dest_id=2 order by first_time  desc) where rownum<=10;

 SEQUENCE# APPLIED
---------- ---------
        22 YES
        23 YES
        24 IN-MEMORY

备库：
SQL> select * from ( select sequence#,applied from v$archived_log where dest_id=1 order by first_time desc) where rownum<=10;

在主库运行，确认是否有断层

SQL> select STATUS, GAP_STATUS from V$ARCHIVE_DEST_STATUS where DEST_ID = 2;

STATUS    GAP_STATUS
--------- ------------------------
VALID     NO GAP

SQL> select STATUS, GAP_STATUS from V$ARCHIVE_DEST_STATUS where DEST_ID = 3;

STATUS    GAP_STATUS
--------- ------------------------
VALID     NO GAP

备库查看MRP进程：
select inst_id,process,status,thread#,sequence#,block# from gv$managed_standby where PROCESS like 'MRP%';

主库：
--检查主库已经产生的日志sequence# 号
select thread#,max(sequence#) "Last Primary Seq Generated" from v$archived_log val,v$database vdb where val.resetlogs_change#=vdb.resetlogs_change# group by thread# order by 1;

备库：
-- 检查备库已经接收到的 sequence# 号
select thread#,max(sequence#) "Last Standby Seq Received" from v$archived_log val,v$database vdb where val.resetlogs_change#=vdb.resetlogs_change# group by thread# order by 1;
-- 检查备库已经应用到的 sequence# 号
select thread#,max(sequence#) "Last Standby Seq Applied" from v$archived_log val,v$database vdb where val.resetlogs_change#=vdb.resetlogs_change# and val.applied in ('YES','IN-MEMORY') group by thread# order by 1;

1.11主库创建临时表确认是否正常同步

主库：
create table test2020 as select * from dual;

备库：
select * from test2020;