Oracle 11g 安装部署新特性

Oracle11g单实例for单实例DG搭建

2020年3月3日2021年5月12日 handsup

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

备库：
ip：10.1.200.67     系统：linux6.5   数据库：oracle11g单实例    sys密码：oracle

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

1、搭建dataguard

判断主库DG是否已经安装：
  select * from v$option where parameter = 'Oracle Data Guard';
  如果是true表示已经安装可以配置，否则需要安装相应组件。
如果不知道主库sys密码，修改主库sys用户密码：
alter user sys identified by oracle;
1.1确认主库是否开启archivelog与force log 
select log_mode,force_logging from v$database; --检查
alter database force logging; --开启

archive log list;
alter database archivelog;

2、配置备库监听

备库:必须是静态监听（GLOBAL_DBNAME服务名，可自定义，tnsnames.ora文件中的service_name与之一致；SID_NAME与数据库实例名一致）
su - oracle
cd $ORACLE_HOME/network/admin
vi listener.ora
如下语句：
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.200.67)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcldg)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/db_1)
      (SID_NAME = orcldg)
    )
  )
启动监听
lsnrctl start
然后再查看监听状态，”UNKNOWN”说明是静态监听。
lsnrctl status

主备库都配置$ORACLE_HOME/network/admin/tnsnames.ora
orcl =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.200.66)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

orcldg =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.200.67)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcldg)
    )
  )

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;

根据以上查询出的路径及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;

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

主库：

alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orcldg)' scope=both;  --DG_CONFIG填db_unique_name
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/product/11.2.0.4/db_1/dbs/arch  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl' scope=both;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=orcldg LGWR ASYNC  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcldg' scope=both;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;
ALTER SYSTEM SET FAL_SERVER = orcldg scope=both;  --服务名
alter system set STANDBY_FILE_MANAGEMENT=MANUAL scope=both;

5、备库的相应配置

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

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

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

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

备库：编辑/tmp/orcl.ora，修改为以下内容：
*.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
*.audit_file_dest='/orcldg/adump'
*.control_files='/orcldg/controlfile/control01.ctl','/orcldg/controlfile/control02.ctl','/orcldg/controlfile/control03.ctl'#Restore Controlfile
*.db_file_name_convert='/u01/app/oracle/oradata/orcl/','/orcldg/datafile/'
*.db_name='orcl'
*.db_unique_name='orcldg'
*.diagnostic_dest='/u01/app/oracle'
*.fal_client='orcldg'
*.fal_server='orcl'
*.log_archive_config='DG_CONFIG=(orcldg,orcl)'
*.log_archive_dest_1='LOCATION=/orcldg/archivelog/  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcldg'
*.log_archive_dest_2='SERVICE=orcl LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'
*.log_file_name_convert='/u01/app/oracle/oradata/orcl/','/orcldg/logfile/'
*.compatible='11.2.0.4.0'
*.standby_file_management='AUTO'

修改/tmp/orcl.ora文件名为/tmp/orcldg.ora
用oracle用户在备库创建上面涉及到的路径（相关的路径配置，可根据实际情况修改）
mkdir -p /orcldg/adump
mkdir -p /orcldg/controlfile
mkdir -p /orcldg/datafile
mkdir -p /orcldg/archivelog
mkdir -p /orcldg/logfile

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

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

7、复制数据

备库：新建脚本vi /home/oracle/duplicate.sh ，加入以下内容：
rman target sys/oracle@orcl auxiliary sys/oracle@orcldg 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等待完成。

以下为同步归档，预计时间比较久
SQL> alter database recover managed standby database disconnect from session;

8、查看归档是否同步完成

主备库对比是否一样：
主库：
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;

9、使用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

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

create table test2016 as select * from dual;

select * from test2016;