Oracle 11g 安装部署新特性

oracle11gRACfor单实例DG搭建

2020年2月22日2020年12月11日 handsup

主库：
节点1：
ip：192.168.174.144     系统：linux6.8   数据库：oracle11gRAC    sys密码：123456  
db_name=racdb,即instance_name、db_unique_name 、service_name都为racdb

节点2：
ip：192.168.174.145     系统：linux6.8   数据库：oracle11gRAC    sys密码：123456  
db_name=racdb,即instance_name、db_unique_name 、service_name都为racdb

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

搭建前准备：
需要在158两台服务器先行安装好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
   startup  mount
alter database force logging;//修改数据库为强制记日志，这是必须的操作，主库的每一步操作都得记录到日志中去。??
archive log list;//--修改数据库为归档模式，因为dg是通过传送归档日志到备库然后应用来保证主备库一致的。??
select log_mode,force_logging from v$database; --检查
alter database force logging; --开启

archive log list;
alter database archivelog;

2、配置主、备库监听，tns连接串

配置主库监听，2个节点都需：（主库的监听可不动，生产环境尽量不动）

cd $ORACLE_HOME/network/admin
 vi listener.ora

加入以下内容
节点1：
SID_LIST_LISTENER=
  (SID_LIST=
       (SID_DESC=
         (GLOBAL_DBNAME= racdb)
         (ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/db_1/)
         (SID_NAME = racdb1) //实例名
        )
)
srvctl stop listener -n rac1(主机名)
srvctl start listener -n rac1
lsnrctl status

节点2：
SID_LIST_LISTENER=
  (SID_LIST=
      (SID_DESC=
         (GLOBAL_DBNAME= racdb)
         (ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/db_1/)
         (SID_NAME = racdb2)
        )
       )
      )
srvctl stop listener -n rac2
srvctl start listener -n rac2
lsnrctl status

备库:必须是静态监听
su - oracle
cd $ORACLE_HOME/network/admin
vi listener.ora
加上如下语句：
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/db_1)
      (SID_NAME = orcl)
    )
  )

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

ADR_BASE_LISTENER = /u01/app/oracle

重启备库监听
lsnrctl reload 
然后再查看监听状态
lsnrctl status

主备库都配置tnsnames.ora(均为oracle用户，主库2个节点都要)
su - oracle
cd $ORACLE_HOME/network/admin
vi tnsnames.ora
racdb =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.174.144)(PORT = 1521))
   (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.174.145)(PORT = 1521))
   (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME =racdb)
    )
  )
orcl =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.174.158)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
)
）

3、配置主库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;  //注意日志文件的所有路径，所有日志都要拷过来
根据以上查询结果创建standby redo log，创建原则thread要一样多，同个thread中要比redo多一个group：
ALTER DATABASE ADD STANDBY LOGFILE thread 1 '+DATA2/racdb/onlinelog/stbredo11.log' SIZE 50m;
ALTER DATABASE ADD STANDBY LOGFILE thread 1 '+DATA2/racdb/onlinelog/stbredo12.log' SIZE 50m;
ALTER DATABASE ADD STANDBY LOGFILE thread 1 '+DATA2/racdb/onlinelog/stbredo13.log' SIZE 50m;
ALTER DATABASE ADD STANDBY LOGFILE thread 2 '+DATA2/racdb/onlinelog/stbredo14.log' SIZE 50m;
ALTER DATABASE ADD STANDBY LOGFILE thread 2 '+DATA2/racdb/onlinelog/stbredo15.log' SIZE 50m;
ALTER DATABASE ADD STANDBY LOGFILE thread 2 '+DATA2/racdb/onlinelog/stbredo16.log' SIZE 50m;

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

4、配置主库初始化参数文件

主库：

alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(racdb, orcl)' scope=both;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=+DATA2/racdb/  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=racdb' scope=both;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=orcl LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl' scope=both;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;
ALTER SYSTEM SET FAL_SERVER = orcl scope=both;
alter system set STANDBY_FILE_MANAGEMENT='AUTO'scope=both;

5、备库的相应配置

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

Pfile生成：
create pfile='/tmp/racdb.ora' from spfile;
将pfile文件拷贝到备库：/tmp
将密码文件拷贝到备库：$ORACLE_HOME/dbs ///12C的密码文件要去asm管理中去找。
在备库oracle用户执行
scp @192.168.174.144:/tmp/racdb.ora  /tmp
scp @192.168.174.144:$ORACLE_HOME/dbs/orapwracdb1 $ORACLE_HOME/dbs

密码文件拷贝到备库后，需要修改文件名。格式为orapw+ORACLE_SID
主备库都测试联通性：
sqlplus sys/123456@racdb as sysdba
sqlplus sys/123456@orcl as sysdba

6.pfile的参数配置

备库：编辑/tmp/orcl.ora，修改为以下内容：（模板）
*.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment//oracle基目录
*.audit_file_dest='/u01/topprddg/adump'//审计目录
*.audit_trail='db'  //审计跟踪
*.compatible='12.2.0'  //版本号
*.control_files='/u01/topprddg/controlfile/control01.ctl','/u01/topprddg/controlfile/control02.ctl','/u01/topprddg/controlfile/control03.ctl'#Restore Controlfile  //控制文件目录，注意/u01的路径
*.db_file_name_convert='+DATA/topprd/datafile/','/u01/topprddg/datafile/'  //数据文件目录，前主库，后备库
*.db_block_size=8192  //块大小
*.db_name='topprd'  //主库名
*.db_unique_name='topprddg'   //备库名
*.diagnostic_dest='/u01/app/oracle' //实例、asm、监听日志路径
*.fal_client='topprddg'  //备库
*.fal_server='topprd'    //主库
*.log_archive_config='DG_CONFIG=(topprddg, topprd)'  //配DG,前主端，后对端。
*.log_archive_dest_1='LOCATION=/u01/topprddg/archivelog/  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=topprddg'  //归档文件路径
*.log_archive_dest_2='SERVICE=topprd LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=topprd'   //对端的连接串，对端的服务名
*.log_file_name_convert='+DATA/TOPPRD/ONLINELOG/','/u01/topprddg/logfile1/','+DATA/TOPPRD/DATAFILE/','/u01/topprddg/logfile2/'     //日志文件目录，要全拷过来，要注意看有几个路径。
*.log_archive_dest_state_2='ENABLE'   
*.standby_file_management='AUTO'

备库pifle参数：
*.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
*.audit_file_dest='/orcl/adump'
*.audit_trail='db'  
*.compatible='11.2.0.4.0'
*.control_files='/orcl/controlfile/control01.ctl','/orcl/controlfile/control02.ctl','/orcl/controlfile/control03.ctl'#Restore Controlfile
*.db_file_name_convert='+DATA2/racdb/datafile/','/orcl/datafile/' 
*.db_block_size=8192  
*.db_name='racdb'  
*.db_unique_name='orcl'   
*.diagnostic_dest='/u01/app/oracle'
*.fal_client='orcl'
*.fal_server='racdb'
*.log_archive_config='DG_CONFIG=(orcl, racdb)'
*.log_archive_dest_1='LOCATION=/orcl/archivelog/  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl'
*.log_archive_dest_2='SERVICE=racdb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=racdb'
*.log_file_name_convert='+DATA/racdb/onlinelog/','/orcl/logfile1/','+DATA2/racdb/onlinelog/','/orcl/logfile2/'
*.log_archive_dest_state_2='ENABLE'
*.standby_file_management='AUTO'

cp /tmp/racdb.ora文件名为 /tmp/racdb_bak.ora
修改/tmp/racdb.ora文件名为/tmp/orcl.ora
用oracle用户在备库创建上面涉及到的路径，在实际环境中一定查看好实体机的磁盘大小，选好足够大的路径磁盘。（相关的路径配置，可根据实际情况修改）
mkdir -p /orcl/adump
mkdir -p /orcl/controlfile
mkdir -p /orcl/datafile
mkdir -p /orcl/archivelog
mkdir -p /orcl/logfile1
mkdir -p /orcl/logfile2

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

create spfile from pfile='/tmp/orcl.ora';
startup nomount

8、复制数据

备库vi duplicate.sh后台运行：

vi /home/oracle/duplicate.sh
rman target sys/123456@racdb auxiliary sys/123456@orcl nocatalog> duplicate.log <<EOF
duplicate target database for standby from active database nofilenamecheck;
EOF

cd ~
chmod a+x duplicate.sh
nohup ./duplicate.sh &
sar -n DEV 1 100
iostat -xdk
观察日志duplicate.log等待完成。

以下为同步归档，预计时间比较久，可以先做后面的步骤
SQL> alter database recover managed standby database disconnect from session;

9、开启dg_broker

主库：
alter system set dg_broker_start=false scope=bothsid='*';
alter system set dg_broker_config_file1='+MESDG2/dr1EPIMES.dat' sid='*';
alter system set dg_broker_config_file2='+MESDG2/dr2EPIMES.dat' sid='*';
alter system set dg_broker_start=true scope=bothsid='*';

备库：
alter system set dg_broker_start=true scope=both;

主库，配置dgmgrl
[oracle@dbsource admin]$ dgmgrl /
DGMGRL for Linux: Version 11.2.0.2.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> CREATE CONFIGURATION dg_epimesas  PRIMARY DATABASE IS EPIMES  CONNECT IDENTIFIER IS EPIMES;
Configuration "dg_epimes" created with primary database "epimes"
DGMGRL> enable CONFIGURATION
Enabled.
DGMGRL> add database EPIMESDG  as  CONNECT IDENTIFIER IS EPIMESDG;
Database "epimesdg" added
DGMGRL> show database verbose EPIMESDG

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;

11、使用11G Data Guard新特性，Archive Data Guard

在备库运行：
SQL> recover managed standby database cancel; 
Media recovery complete.

SQL> alter database open read only;

Database altered.

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

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

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

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

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

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

备库：
select * from test;