DataGuard搭建 （一主两备）
DG搭建（一主两备）
| 节点 | 网络ip地址 | 数据库名 | unique name | 数据库实例名 | 数据文件位置 |
| zyx.test.com（主库） | 192.168.11.111 | orcl | orcl | test | /u01/app/oracle/oradata/orcl/ |
| orcl.test.com（备库） | 192.168.11.22 | orcl | orclps1 | orclps1 | /u01/app/oracle/oradata/orcl/ |
| dg2.orcl.com（备库） | 192.168.11.23 | orcl | orclps2 | orclps2 | /u01/app/oracle/oradata/orcl/ |
1.主库设置
1.1 开归档
sys@ORCL> shutdown immediate
sys@ORCL> startup mount
sys@ORCL> alter database archivelog;
sys@ORCL> alter database open;
sys@ORCL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 4
Next log sequence to archive 6
Current log sequence 6
sys@ORCL> alter database force logging;
Database altered.
1.2 参数设置
sys@ORCL>alter system set log_archive_config='dg_config=(orcl,orclps1,orclps2)';
System altered.
sys@ORCL>alter system set log_archive_dest_2='service=orclps1 async valid_for=(online_logfile,primary_role) db_unique_name=orclps1';
System altered.
sys@ORCL>alter system set log_archive_dest_3='service=orclps2 async valid_for=(online_logfile,primary_role) db_unique_name=orclps2';
System altered.
sys@ORCL>alter system set fal_server=orclps1,orclps2;
System altered.
sys@ORCL>alter system set fal_client=orcl;
System altered.
sys@ORCL> alter system set standby_file_management=auto;
System altered.
sys@ORCL> create pfile='/home/oracle/pfile.ora' from spfile;
File created.
1.3 配置TNS
[oracle@zyx ~]$ vim /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.111)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.test.com)
)
)
ORCLPS1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.22)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCLPS1)
)
)
ORCLPS2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.23)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCLPS2)
)
)
2.备库orclps1配置
----数据库软件安装好，数据库不用创建
2.1 环境变量
[oracle@orcl ~]$ vim .bash_profile
export ORACLE_SID=orclps1
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export TNS_ADMIN=$ORACLE_HOME/network/admin
export ORACLE_UNQNAME=orclps1
export ORACLE_HOSTNAME=orcl.test.com
export PATH=$PATH:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
export NLS_LANG=american_america.ZHS16GBK
export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'
export EDITOR=vi
export LANG=C
umask 022
[oracle@orcl ~]$ . .bash_profile
2.2 创建必要目录
mkdir -p $ORACLE_BASE/fast_recovery_area/orcl
mkdir -p $ORACLE_BASE/admin/orcl/adump
mkdir -p $ORACLE_BASE/admin/orcl/dpdump
mkdir -p $ORACLE_BASE/admin/orcl/pfile
mkdir -p $ORACLE_BASE/oradata/orcl
2.3 静态监听
[oracle@orcl ~]$ vim /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.22)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
SID_LIST_listener=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=orclps1)
(SID_NAME=orclps1)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)))
[oracle@orcl ~]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 25-APR-2016 18:37:16
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/orcl/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.11.22)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 25-APR-2016 18:37:17
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/orcl/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.11.22)(PORT=1521)))
Services Summary...
Service "orclps1" has 1 instance(s).
Instance "orclps1", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
2.4 配置TNS
[oracle@orcl ~]$ vim /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.111)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.test.com)
)
)
ORCLPS1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.22)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCLPS1)
)
)
ORCLPS2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.23)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCLPS2)
)
)
2.5 参数文件设置
[oracle@zyx ~]$ scp pfile.ora 192.168.11.22:/home/oracle
The authenticity of host '192.168.11.22 (192.168.11.22)' can't be established.
RSA key fingerprint is bf:62:c6:a5:9f:ca:46:cf:11:6b:d8:c5:24:dd:fe:47.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.11.22' (RSA) to the list of known hosts.
oracle@192.168.11.22's password:
pfile.ora 100% 1288 1.3KB/s 00:00
----修改参数
idle>ho vim /home/oracle/pfile.ora
*.db_name='orcl'
*.fal_client='ORCLPS1'
*.fal_server='ORCL','ORCLPS2'
*.log_archive_dest_2='service=orcl async valid_for=(online_logfile,primary_role) db_unique_name='orcl'
----追加参数
*.db_unique_name='orclps1'
----创建spfile，启动到nomount
idle>create spfile from pfile='/home/oracle/pfile.ora';
File created.
idle>startup nomount
ORACLE instance started.
Total System Global Area 580395008 bytes
Fixed Size 2255392 bytes
Variable Size 411043296 bytes
Database Buffers 159383552 bytes
Redo Buffers 7712768 bytes
idle>
2.6 密钥文件创建
[oracle@orcl ~]$ cd $ORACLE_HOME/dbs
[oracle@orcl dbs]$ orapwd file=orapworclps1 password=sys
[oracle@orcl dbs]$ ll -h
total 5K
-rw-r-----. 1 oracle oinstall 1.5K Apr 26 06:16 orapworclps1
-rw-r-----. 1 oracle oinstall 3.5K Apr 26 04:56 spfileorclps1.ora
[oracle@orcl dbs]$
3.备库orclps2配置
3.1 环境变量
[oracle@dg2 ~]$ vim .bash_profile
export ORACLE_SID=orclps2
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export TNS_ADMIN=$ORACLE_HOME/network/admin
export ORACLE_UNQNAME=orclps2
export ORACLE_HOSTNAME=dg2.orcl.com
export PATH=$PATH:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
export NLS_LANG=american_america.ZHS16GBK
export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'
export EDITOR=vi
export LANG=C
umask 022
[oracle@dg2 ~]$ . .bash_profile
3.2 创建必要目录
mkdir -p $ORACLE_BASE/fast_recovery_area/orcl
mkdir -p $ORACLE_BASE/admin/orcl/adump
mkdir -p $ORACLE_BASE/admin/orcl/dpdump
mkdir -p $ORACLE_BASE/admin/orcl/pfile
mkdir -p $ORACLE_BASE/oradata/orcl
3.3 静态监听
[oracle@dg2 ~]$ vim /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.23)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
SID_LIST_listener=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=orclps2)
(SID_NAME=orclps2)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)))
[oracle@dg2 ~]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 30-APR-2016 02:56:58
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/dg2/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.11.23)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 30-APR-2016 02:56:59
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/dg2/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.11.23)(PORT=1521)))
Services Summary...
Service "orclps2" has 1 instance(s).
Instance "orclps2", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
3.4 配置TNS
[oracle@dg2 ~]$ vim /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.111)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.test.com)
)
)
ORCLPS1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.22)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCLPS1)
)
)
ORCLPS2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.23)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCLPS2)
)
)
3.5 参数文件设置
[oracle@zyx ~]$ scp pfile.ora 192.168.11.23:/home/oracle
The authenticity of host '192.168.11.22 (192.168.11.22)' can't be established.
RSA key fingerprint is bf:62:c6:a5:9f:ca:46:cf:11:6b:d8:c5:24:dd:fe:47.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.11.22' (RSA) to the list of known hosts.
oracle@192.168.11.22's password:
pfile.ora 100% 1288 1.3KB/s 00:00
----修改参数
idle>ho vim /home/oracle/pfile.ora
*.db_domain='orcl.com'
*.db_name='orcl'
*.fal_client='ORCLPS2'
*.fal_server='ORCL','ORCLPS1'
*.log_archive_dest_3='service=orcl async valid_for=(online_logfile,primary_role) db_unique_name=orcl'
----追加参数
*.db_unique_name='orclps2'
----创建spfile，启动到nomount
idle>create spfile from pfile='/home/oracle/pfile.ora';
File created.
idle>startup nomount
ORACLE instance started.
Total System Global Area 580395008 bytes
Fixed Size 2255392 bytes
Variable Size 411043296 bytes
Database Buffers 159383552 bytes
Redo Buffers 7712768 bytes
idle>
3.6 密钥文件创建
[oracle@orcl dbs]$ cd $ORACLE_HOME/dbs
[oracle@orcl dbs]$ orapwd file=orapworclps2 password=sys
[oracle@orcl dbs]$ ll -h
total 5K
-rw-r-----. 1 oracle oinstall 1.5K Apr 26 06:16 orapworclps2
-rw-r-----. 1 oracle oinstall 3.5K Apr 26 04:56 spfileorclps1.ora
[oracle@orcl dbs]$
4.duplicate到备库
4.1 登录测试
[oracle@dg2 ~]$ tnsping orclps2
[oracle@dg2 ~]$ tnsping orclps1
[oracle@dg2 ~]$ tnsping orcl
[oracle@dg2 ~]$ sqlplus sys/sys@orclps2 as sysdba
[oracle@dg2 ~]$ sqlplus sys/sys@orclps1 as sysdba
[oracle@dg2 dbs]$ sqlplus sys/sys@orcl as sysdba
4.2 duplicate复制数据库到orclps1
[oracle@zyx ~]$ rman target sys/sys@orcl auxiliary sys/sys@orclps1
Recovery Manager: Release 11.2.0.4.0 - Production on Sat Apr 23 01:37:12 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1437652505)
connected to auxiliary database: ORCL (not mounted)
RMAN>
RMAN> duplicate target database for standby from active database dorecover nofilenamecheck;
--------如果复制数据库时，备库的路径和原库一致，就需要加nofilenamecheck，不然会报错
.............
media recovery complete, elapsed time: 00:00:00
Finished recover at 2016-04-23 01:40:19
Finished Duplicate Db at 2016-04-23 01:40:22
4.2 duplicate复制数据库到orclps2
[oracle@zyx ~]$ rman target sys/sys@orcl auxiliary sys/sys@orclps2
Recovery Manager: Release 11.2.0.4.0 - Production on Sat Apr 23 01:41:03 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1437652505)
connected to auxiliary database: ORCL (not mounted)
RMAN>
RMAN> duplicate target database for standby from active database dorecover nofilenamecheck;
..............
media recovery complete, elapsed time: 00:00:00
Finished recover at 2016-04-23 01:43:21
Finished Duplicate Db at 2016-04-23 01:43:27
4.3 备库orclps1配置
----查看当前状态
idle>select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
orclps1 MOUNTED
----如果需要手动启动备用数据库：
---- startup nomount
---- alter database mount standby database;
----创建srl日志（比主库redo多一组，大小一样）
idle>alter database add standby logfile '/u01/app/oracle/oradata/orcl/srl01.log' size 50m;
Database altered.
idle>alter database add standby logfile '/u01/app/oracle/oradata/orcl/srl02.log' size 50m;
Database altered.
idle>alter database add standby logfile '/u01/app/oracle/oradata/orcl/srl03.log' size 50m;
Database altered.
idle>alter database add standby logfile '/u01/app/oracle/oradata/orcl/srl04.log' size 50m;
Database altered.
----应用日志，开启redoapply
idle> alter database recover managed standby database using current logfile disconnect;
Database altered.
----取消日志应用 recover managed standby database cancel;
4.4 备库orclps2配置
idle>select instance_name,status from v$instance;
INSTANCE_NAME STATUS
------------------------------------------------ ------------------------------------
orclps2 MOUNTED
----创建srl日志（比主库redo多一组，大小一样）
idle>alter database add standby logfile '/u01/app/oracle/oradata/orcl/srl01.log' size 50m;
Database altered.
idle>alter database add standby logfile '/u01/app/oracle/oradata/orcl/srl02.log' size 50m;
Database altered.
idle>alter database add standby logfile '/u01/app/oracle/oradata/orcl/srl03.log' size 50m;
Database altered.
idle>alter database add standby logfile '/u01/app/oracle/oradata/orcl/srl04.log' size 50m;
Database altered.
----应用日志，开启redoapply
idle> alter database recover managed standby database using current logfile disconnect;
Database altered.
----取消日志应用 recover managed standby database cancel;
4.5 主库添加srl日志
sys@ORCL>alter database add standby logfile '/u01/app/oracle/oradata/orcl/srl01.log' size 50m;
Database altered.
sys@ORCL>alter database add standby logfile '/u01/app/oracle/oradata/orcl/srl02.log' size 50m;
Database altered.
sys@ORCL>alter database add standby logfile '/u01/app/oracle/oradata/orcl/srl03.log' size 50m;
Database altered.
sys@ORCL>alter database add standby logfile '/u01/app/oracle/oradata/orcl/srl04.log' size 50m;
Database altered.
5.查看主备应用日志情况
5.1主库切换日志
sys@ORCL>alter system switch logfile;
System altered.
5.2备库出现新归档
----备库orclps1出现新的归档
idle>select sequence#, applied from v$archived_log;
SEQUENCE# APPLIED
---------- ---------
9 YES
10 YES
11 YES
12 IN-MEMORY
----备库orclps2出现新的归档
idle>select sequence#, applied from v$archived_log;
SEQUENCE# APPLIED
---------- ---------------------------
11 YES
12 IN-MEMORY
----备库orclps1传输模式
idle> select protection_mode, protection_level from v$database;
PROTECTION_MODE PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
----备库orclps2传输模式
idle>select protection_mode, protection_level from v$database;
PROTECTION_MODE PROTECTION_LEVEL
------------------------------------------------------------ -----------------------------------------------------------
MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
5.3主库上查看备库应用情况（可以把name字段也加上）
sys@ORCL>select sequence#, applied from v$archived_log;
SEQUENCE# APPLIED
---------- ---------
6 NO
7 NO
8 NO
9 NO
10 NO
10 YES
11 NO
11 YES
12 NO
12 YES
12 YES
------上面applied应用：NO的代表本地归档，是不需要应用的，YES的代表备库传输后已经应用。orclps1备库是从11号归档开始应用，orclps2是从12号归档开始应用
正文到此结束
热门推荐
相关文章
近期评论
-
文章和留言都翻到11页了 没有OOM
-
-
朋友，翻页到11页，及以后，会出现OOM，无法访问
-
-
搞个gitee的项目
-
-
版本号是多少，你可以下载哪个代码仓库，jdk选1.8 直接跑就行
-
-
-
Loading...
![[HBLOG]公众号](https://www.liuhaihua.cn/img/qrcode_gzh.jpg)
