EBS测试环境DataGuard配置
1、环境信息
primary数据库（必须运行在归档模式）
| ip | 127.71.28.71 |
| sid | test |
| db_unique_name | test |
| log_mode | archivelog |
| force_logging | no |
standby数据库
| ip | 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端的目录
SQL> 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; NAME VALUE ------------------------------ -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- audit_file_dest /TEST/db/tech_st/11.1.0/rdbms/audit background_dump_dest /TEST/db/tech_st/11.1.0/admin/TEST_erptest/diag/rdbms/test/TEST/trace control_files /TEST/db/apps_st/data/cntrl01.dbf, /TEST/db/apps_st/data/cntrl02.dbf, /TEST/db/apps_st/data/cntrl03.dbf core_dump_dest /TEST/db/tech_st/11.1.0/admin/TEST_erptest/diag/rdbms/test/TEST/cdump user_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 |
创建上述目录
[ebstest_standby@ebstest ebstest_standby]$ mkdir -p /ebstest/ebstest_standby/TEST/db/tech_st/11.1.0/rdbms/audit [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 [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 [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
确认结果
[ebstest_standby@ebstest ~]$ source .bash_profile [ebstest_standby@ebstest ~]$ echo $ORACLE_SID EBSTEST_STBY
6、密码文件
直接从Primary数据库复制密钥文件过来
[oratest@erptest dbs]$ pwd /TEST/db/tech_st/11.1.0/dbs [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
SQL> show parameter db_unique NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_unique_name string TEST
修改Primay端spfile参数值（因为不要求switchover 很多参数值未设置）
-------为了不重启,沿用上面的db_unique_name
----alter system set DB_UNIQUE_NAME=TEST scope=spfile; alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(TEST,db_standby)' alter system set LOG_ARCHIVE_DEST_2='SERVICE=tns_standby LGWR ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=db_standby' alter system set LOG_ARCHIVE_DEST_STATE_2=DEFER
8、生成StandBy端spfile文件
首先在primary端生成pfile文件
SQL> create pfile ='/TEST/initEBSTEST_STBY.ora' from spfile; File created.
拷贝到备库
[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: initEBSTEST_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
StandBy> create spfile from pfile;
File created.
9、Standby端配置监听
查看listener.ora位置（.env中配置的）
[ebstest_standby@ebstest ebstest_standby]$ echo $TNS_ADMIN /ebstest/ebstest_standby/TEST/db/tech_st/11.1.0/network/admin/TEST_erptest
standby端配置静态监听（服务名GLOBAL_DBNAME = StandBy，后面配置tns会用到）
TESTSTBY = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.71.48.38)(PORT = 1529)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = StandBy) (ORACLE_HOME =/ebstest/ebstest_standby/TEST/db/tech_st/11.1.0) (SID_NAME = EBSTEST_STBY) ) )
启动监听
[ebstest_standby@ebstest TEST_erptest]$ lsnrctl start
查看监听该状态
[ebstest_standby@ebstest ebstest_standby]$ lsnrctl status
10、配置网络服务名tns，并测试互通性
primary端和standby端配置tns
tns_primary = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.71.28.71)(PORT = 1529)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = TEST) ) ) tns_standby = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.71.48.38)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =StandBy ) ) )
测试
tnsping tns_primary
tnsping tns_standby
11、duplicate standby
备库Standby启动到nomount
SQL> startup nomount ORACLE instance started. Total System Global Area 3290345472 bytes Fixed Size 2217832 bytes Variable Size 1795164312 bytes Database Buffers 1476395008 bytes Redo 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个多小时
[ebstest_standby@ebstest ebstest_standby]$ du -hs TEST/ 988G TEST/
12、添加Standby REDO log
查看主库的redo log的大小
SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_ ------ ---------- ---------- -------------------------------------------------- --- 3 ONLINE /TEST/db/apps_st/data/log03b.dbf NO 3 ONLINE /TEST/db/apps_st/data/log03a.dbf NO 2 ONLINE /TEST/db/apps_st/data/log02b.dbf NO 2 ONLINE /TEST/db/apps_st/data/log02a.dbf NO 1 ONLINE /TEST/db/apps_st/data/log01a.dbf NO 1 ONLINE /TEST/db/apps_st/data/log01b.dbf NO 6 rows selected. SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME ------ ---------- ---------- ---------- ---------- --- ---------- ------------- ------------------ 1 1 1288 1048576000 2 NO CURRENT 5.9797E+12 04-JAN-16 2 1 1286 1048576000 2 YES INACTIVE 5.9797E+12 04-JAN-16 3 1 1287 2147483648 2 YES INACTIVE 5.9797E+12 04-JAN-16
当前有三组、每组1个member、大小为1000M, 我们增加四组，每组2个member，大小为1000M
ALTER DATABASE ADD STANDBY LOGFILE GROUP4
('/ebstest/ebstest_standby/TEST/db/apps_st/data/stbyredolog4a.dbf','/ebstest/ebstest_standby/TEST/db/apps_st/data/stbyredolog4b.dbf') SIZE 1000 M;
ALTER 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;
ALTER 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;
ALTER 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库的日志传送
Primary>show parameter LOG_ARCHIVE_DEST_STATE_2 NAME TYPE VALUE ------------------------------------ ---------------------- ------------------------------ log_archive_dest_state_2 string DEFER log_archive_dest_state_20 string enable log_archive_dest_state_21 string enable log_archive_dest_state_22 string enable log_archive_dest_state_23 string enable log_archive_dest_state_24 string enable log_archive_dest_state_25 string enable log_archive_dest_state_26 string enable log_archive_dest_state_27 string enable log_archive_dest_state_28 string enable log_archive_dest_state_29 string enable Primary>alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE; System altered.
14、验证&测试
主库插入一条数据
SQL> INSERT INTO "SCOTT"."DEPT" ("DEPTNO", "DNAME", "LOC") VALUES ('13', 'OPERATIONS', 'OPERATIONS'); 1 row created. SQL> commit; Commit complete.
备库启动实时应用
SQL> alter database recover managed standby database using current logfile disconnect from session; Database altered.
查询备库
SQL> select * from scott.dept; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 13 OPERATIONS OPERATIONS 15 OPERATIONS OPERATIONS
