EBS测试环境DataGuard配置

1、环境信息

primary数据库（必须运行在归档模式）

127.71.28.71

sid

test

db_unique_name

test

log_mode

archivelog

force_logging

standby数据库

127.71.48.38

sid

ebstest_stby

db_unique_name

db_standby

设置提示，以区分操作的位置

primary数据库

set SQLPROMPT Primary>

standby数据库

set SQLPROMPT StandBy>

2、Standby端新建数据库用户

当前的环境中已经有dba用户组，因此只新建用户ebstest_standby即可

[wangshengzhuang@ebstest ~]$ sudo useradd -g dba ebstest_standby

3、拷贝ORACLE_HOME目录至备库服务器

在StandBy上创建如下oracle软件父目录

[ebstest_standby@ebstest ebstest_standby]$ mkdir -p /ebstest/ebstest_standby

压缩Primary的数据库目录（排除trace和audit目录,大约需要7分钟）

tar -zcvf  ebstest_oracle_home_20151225.tar.gz  /TEST/db/tech_st  --exclude  /TEST/db/tech_st/11.1.0/admin  --exclude /TEST/db/tech_st/11.1.0/rdbms/audit

传送上面生成的压缩文件至Standby服务器

[oratest@erptest db]$ scp ebstest_oracle_home_20151225.tar.gz  ebstest_standby@127.71.48.38:/ebstest/ebstest_standby

在Standby服务器解压

[ebstest_standby@ebstest ebstest_standby]$ tar -zxvf ebstest_oracle_home_20151225.tar.gz

4、standby端创建数据库相关目录

查询primary端的目录

1SQL> select name ,value from v$parameter  where name in ('audit_file_dest','background_dump_dest','control_files','core_dump_dest','user_dump_dest') ORDER BY name ASC;
2
3NAME                           VALUE
4------------------------------ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
5audit_file_dest                /TEST/db/tech_st/11.1.0/rdbms/audit
6background_dump_dest           /TEST/db/tech_st/11.1.0/admin/TEST_erptest/diag/rdbms/test/TEST/trace
7control_files                  /TEST/db/apps_st/data/cntrl01.dbf, /TEST/db/apps_st/data/cntrl02.dbf, /TEST/db/apps_st/data/cntrl03.dbf
8core_dump_dest                 /TEST/db/tech_st/11.1.0/admin/TEST_erptest/diag/rdbms/test/TEST/cdump
9user_dump_dest                 /TEST/db/tech_st/11.1.0/admin/TEST_erptest/diag/rdbms/test/TEST/trace

对应的standby端对应的目录

audit_file_dest

/ebstest/ebstest_standby/TEST/db/tech_st/11.1.0/rdbms/audit

background_dump_dest

/ebstest/ebstest_standby/TEST/db/tech_st/11.1.0/admin/TEST_erptest/diag/rdbms/test/TEST/trace

core_dump_dest

/ebstest/ebstest_standby/TEST/db/tech_st/11.1.0/admin/TEST_erptest/diag/rdbms/test/TEST/cdump

user_dump_dest

/ebstest/ebstest_standby/TEST/db/tech_st/11.1.0/admin/TEST_erptest/diag/rdbms/test/TEST/trace

control_files

/ebstest/ebstest_standby/TEST/db/apps_st/data/cntrl01.dbf, /ebstest/ebstest_standby/TEST/db/apps_st/data/cntrl02.dbf,
/ebstest/ebstest_standby /TEST/db/apps_st/data/cntrl03.dbf

创建上述目录

1[ebstest_standby@ebstest ebstest_standby]$ mkdir -p /ebstest/ebstest_standby/TEST/db/tech_st/11.1.0/rdbms/audit
2[ebstest_standby@ebstest ebstest_standby]$ mkdir -p /ebstest/ebstest_standby/TEST/db/tech_st/11.1.0/admin/TEST_erptest/diag/rdbms/test/TEST/trace
3[ebstest_standby@ebstest ebstest_standby]$ mkdir -p /ebstest/ebstest_standby/TEST/db/tech_st/11.1.0/admin/TEST_erptest/diag/rdbms/test/TEST/cdump
4[ebstest_standby@ebstest db]$ mkdir -p /ebstest/ebstest_standby/TEST/db/apps_st/data/

5、修改standby环境变量

.bash_profile中添加：

. /ebstest/ebstest_standby/TEST/db/tech_st/11.1.0/TEST_erptest.env

修改/ebstest/ebstest_standby/TEST/db/tech_st/11.1.0/TEST_erptest.env

将所有的/TEST/db/tech_st/11.1.0/ 替换为/ebstest/ebstest_standby/TEST/db/tech_st/11.1.0/ 一共21处
设置ORACLE_SID为EBSTEST_STBY

确认结果

1[ebstest_standby@ebstest ~]$ source .bash_profile
2[ebstest_standby@ebstest ~]$ echo $ORACLE_SID
3EBSTEST_STBY

6、密码文件

直接从Primary数据库复制密钥文件过来

1[oratest@erptest dbs]$ pwd
2/TEST/db/tech_st/11.1.0/dbs
3[oratest@erptest dbs]$ scp orapwTEST  ebstest_standby@127.71.48.38:/ebstest/ebstest_standby/TEST/db/tech_st/11.1.0/dbs

改名

[ebstest_standby@ebstest dbs]$ mv orapwTEST  orapwEBSTEST_STBY

7、修改Primary端spfile文件参数

查询Primary库的db_unique_name

1SQL> show parameter db_unique
2
3NAME                                 TYPE        VALUE
4------------------------------------ ----------- ------------------------------
5db_unique_name                       string      TEST

修改Primay端spfile参数值（因为不要求switchover 很多参数值未设置）

1-------为了不重启,沿用上面的db_unique_name   
2
3----alter system set DB_UNIQUE_NAME=TEST scope=spfile;
4alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(TEST,db_standby)'
5alter system set LOG_ARCHIVE_DEST_2='SERVICE=tns_standby LGWR ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=db_standby'
6alter system set LOG_ARCHIVE_DEST_STATE_2=DEFER

8、生成StandBy端spfile文件

首先在primary端生成pfile文件

1SQL>  create pfile ='/TEST/initEBSTEST_STBY.ora'  from spfile;
2
3File created.

拷贝到备库

1[oratest@erptest TEST]$ scp initEBSTEST_STBY.ora  ebstest_standby@127.71.48.38:/ebstest/ebstest_standby/TEST/db/tech_st/11.1.0/dbs ebstest_standby@127.71.48.38's password:
2initEBSTEST_STANDBY.ora                                               100% 2770     2.7KB/s   00:00

修改如下

内存参数中的TEST 改为 EBSTEST_STBY
修改pfile中各种文件的路径
修改下面dataguard涉及的参数：

*.db_unique_name='db_standby' *.LOG_ARCHIVE_CONFIG='DG_CONFIG=(TEST,db_standby)' *.log_archive_dest_2='SERVICE=tns_primary LGWR ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TEST' *.log_archive_dest_state_2='ENABLE'

*.DB_FILE_NAME_CONVERT= '/TEST/db/apps_st/data/','/ebstest/ebstest_standby/TEST/db/apps_st/data/' *.LOG_FILE_NAME_CONVERT='/TEST/db/apps_st/data/','/ebstest/ebstest_standby/TEST/db/apps_st/data/'

*.FAL_SERVER=tns_primary *.FAL_CLIENT=tns_standby *.STANDBY_FILE_MANAGEMENT=AUTO

通过复制的pfile创建Standby数据库的spfile

1StandBy> create spfile from pfile;
2
3File created.

9、Standby端配置监听

查看listener.ora位置（.env中配置的）

1[ebstest_standby@ebstest ebstest_standby]$ echo $TNS_ADMIN
2/ebstest/ebstest_standby/TEST/db/tech_st/11.1.0/network/admin/TEST_erptest

standby端配置静态监听（服务名GLOBAL_DBNAME = StandBy，后面配置tns会用到）

1TESTSTBY =
2  (DESCRIPTION_LIST =
3    (DESCRIPTION =
4      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.71.48.38)(PORT = 1529))
5    )
6  )
7
8
9SID_LIST_LISTENER =
10  (SID_LIST =
11    (SID_DESC =
12      (GLOBAL_DBNAME = StandBy)
13      (ORACLE_HOME =/ebstest/ebstest_standby/TEST/db/tech_st/11.1.0)
14      (SID_NAME = EBSTEST_STBY)
15    )
16  )

启动监听

[ebstest_standby@ebstest TEST_erptest]$ lsnrctl start

查看监听该状态

[ebstest_standby@ebstest ebstest_standby]$ lsnrctl status

10、配置网络服务名tns，并测试互通性

primary端和standby端配置tns

1tns_primary =
2  (DESCRIPTION =
3    (ADDRESS = (PROTOCOL = TCP)(HOST = 127.71.28.71)(PORT = 1529))
4    (CONNECT_DATA =
5      (SERVER = DEDICATED)
6      (SERVICE_NAME = TEST)
7    )
8  )
9
10
11tns_standby =
12  (DESCRIPTION =
13    (ADDRESS = (PROTOCOL = TCP)(HOST = 127.71.48.38)(PORT = 1521))
14    (CONNECT_DATA =
15      (SERVER = DEDICATED)
16      (SERVICE_NAME =StandBy )
17    )
18  )

测试

1tnsping tns_primary
2tnsping tns_standby

11、duplicate standby

备库Standby启动到nomount

1SQL> startup nomount
2ORACLE instance started.
3
4Total System Global Area 3290345472 bytes
5Fixed Size            2217832 bytes
6Variable Size         1795164312 bytes
7Database Buffers     1476395008 bytes
8Redo Buffers           16568320 bytes

rman连接到两个数据库

[oratest@erptest ~]$ rman target sys/yourpassword@tns_primary auxiliary sys/yourpassword@tns_standby

开始复制（确保备库有足够的空间，否则会报错）

RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER NOFILENAMECHECK;

复制完以后大概1T左右，大约需要3个多小时

1[ebstest_standby@ebstest ebstest_standby]$ du -hs TEST/
2988G    TEST/

12、添加Standby REDO log

查看主库的redo log的大小

1SQL> select * from v$logfile;
2
3GROUP# STATUS     TYPE       MEMBER                                             IS_
4------ ---------- ---------- -------------------------------------------------- ---
5     3            ONLINE     /TEST/db/apps_st/data/log03b.dbf                   NO
6     3            ONLINE     /TEST/db/apps_st/data/log03a.dbf                   NO
7     2            ONLINE     /TEST/db/apps_st/data/log02b.dbf                   NO
8     2            ONLINE     /TEST/db/apps_st/data/log02a.dbf                   NO
9     1            ONLINE     /TEST/db/apps_st/data/log01a.dbf                   NO
10     1            ONLINE     /TEST/db/apps_st/data/log01b.dbf                   NO
11
126 rows selected.
13
14SQL> select * from v$log;
15
16GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS     FIRST_CHANGE# FIRST_TIME
17------ ---------- ---------- ---------- ---------- --- ---------- ------------- ------------------
18     1          1       1288 1048576000          2 NO  CURRENT       5.9797E+12 04-JAN-16
19     2          1       1286 1048576000          2 YES INACTIVE      5.9797E+12 04-JAN-16
20     3          1       1287 2147483648          2 YES INACTIVE      5.9797E+12 04-JAN-16

当前有三组、每组1个member、大小为1000M, 我们增加四组，每组2个member，大小为1000M

ALTER DATABASE ADD STANDBY LOGFILE GROUP

1('/ebstest/ebstest_standby/TEST/db/apps_st/data/stbyredolog4a.dbf','/ebstest/ebstest_standby/TEST/db/apps_st/data/stbyredolog4b.dbf') SIZE 1000 M; 
2
3ALTER DATABASE ADD STANDBY   LOGFILE GROUP 5('/ebstest/ebstest_standby/TEST/db/apps_st/data/stbyredolog5a.dbf','/ebstest/ebstest_standby/TEST/db/apps_st/data/stbyredolog5b.dbf') SIZE  1000 M;
4
5
6ALTER DATABASE ADD STANDBY   LOGFILE GROUP 6('/ebstest/ebstest_standby/TEST/db/apps_st/data/stbyredolog6a.dbf','/ebstest/ebstest_standby/TEST/db/apps_st/data/stbyredolog6b.dbf') SIZE  1000 M;
7
8
9ALTER DATABASE ADD STANDBY   LOGFILE GROUP 7('/ebstest/ebstest_standby/TEST/db/apps_st/data/stbyredolog7a.dbf','/ebstest/ebstest_standby/TEST/db/apps_st/data/stbyredolog7b.dbf') SIZE  1000 M;

13、启用primary库的日志传送

1Primary>show parameter LOG_ARCHIVE_DEST_STATE_2
2
3NAME                     TYPE            VALUE
4------------------------------------ ---------------------- ------------------------------
5log_archive_dest_state_2         string            DEFER
6log_archive_dest_state_20         string            enable
7log_archive_dest_state_21         string            enable
8log_archive_dest_state_22         string            enable
9log_archive_dest_state_23         string            enable
10log_archive_dest_state_24         string            enable
11log_archive_dest_state_25         string            enable
12log_archive_dest_state_26         string            enable
13log_archive_dest_state_27         string            enable
14log_archive_dest_state_28         string            enable
15log_archive_dest_state_29         string            enable
16Primary>alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE;
17
18System altered.

14、验证&测试

主库插入一条数据

1SQL> INSERT INTO "SCOTT"."DEPT" ("DEPTNO", "DNAME", "LOC") VALUES ('13', 'OPERATIONS', 'OPERATIONS');
2
31 row created.
4
5SQL> commit;
6
7Commit complete.

备库启动实时应用

1SQL> alter database recover managed standby database using current logfile disconnect from session;
2
3Database altered.

查询备库

1SQL> select * from scott.dept;
2
3    DEPTNO DNAME          LOC
4---------- -------------- -------------
5         10 ACCOUNTING     NEW YORK
6        20 RESEARCH       DALLAS
7        30 SALES          CHICAGO
8        40 OPERATIONS     BOSTON
9        13 OPERATIONS OPERATIONS
10        15 OPERATIONS     OPERATIONS