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 GROUP
4
('/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