DG搭建（一主一备一级联）
先说明一下dataguard一主一备一级联，意思是主库将日志传输给备库，然后备库在将日志传输给级联库，主库和级联库其实没有任何关系。另外，关于数据同步问题，后面也做了验证，主库上的操作一般情况下是可以实时同步到备库的，但是级联库必须等备库归档时，才能同步。如果主库切换日志，那么这时级联库也能及时同步。
|
节点 |
网络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 |
orclps |
orclps |
/u01/app/oracle/oradata/orcl/ |
|
dg2.orcl.com（级联库） |
192.168.11.23 |
orcl |
orclstd |
orclstd |
/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,orclps)';
sys@ORCL>alter system set log_archive_dest_2='service=orclps sync affirm net_timeout=10 valid_for=(online_logfile,primary_role) db_unique_name=orclps';
#####下面参数是当主库切换为备库时需要的，这里先不设置
alter system set fal_server=orclps;
alter system set fal_client=orcl;
alter system set standby_file_management=auto;
sys@ORCL> create pfile='/home/oracle/pfile.ora' from spfile;
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)
)
)
ORCLPS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.22)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCLPS)
)
)
2.备库orclps配置
----数据库软件安装好，数据库不用创建
2.1 环境变量
[oracle@orcl ~]$ vim .bash_profile
export ORACLE_SID=orclps
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=orclps
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=orclps)
(SID_NAME=orclps)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)))
[oracle@orcl ~]$ lsnrctl start
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)
)
)
ORCLPS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.22)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCLPS)
)
)
2.5 参数文件设置
[oracle@zyx ~]$ scp pfile.ora 192.168.11.22:/home/oracle
idle>ho vim /home/oracle/pfile.ora
----可以删除的参数，或修改为下面格式(该参数是指当前数据库为主库时，传输在线日志给orcl)
*.log_archive_dest_2='service=orcl sync affirm net_timeout=10 valid_for=(online_logfile,primary_role) db_unique_name=orcl'
----追加参数
*.db_unique_name='orclps'
*.fal_client='orclps'
*.fal_server='orcl'
*.standby_file_management=auto
----创建spfile，启动到nomount
idle>create spfile from pfile='/home/oracle/pfile.ora';
idle>startup nomount
2.6 密钥文件创建
[oracle@orcl ~]$ cd $ORACLE_HOME/dbs
[oracle@orcl dbs]$ orapwd file=orapworclps password=sys
3.duplicate到备库
3.1 登录测试
[oracle@dg2 ~]$ tnsping orclps
[oracle@dg2 ~]$ tnsping orcl
[oracle@dg2 ~]$ sqlplus sys/sys@orclps as sysdba
[oracle@dg2 dbs]$ sqlplus sys/sys@orcl as sysdba
3.2 duplicate复制数据库到orclps
[oracle@orcl ~]$ rman target sys/sys@orcl auxiliary sys/sys@orclps
Recovery Manager: Release 11.2.0.4.0 - Production on Mon Apr 25 18:30:21 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，不然会报错
3.3 备库orclps配置
----查看当前状态
idle>select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
orclps 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;
idle>alter database add standby logfile '/u01/app/oracle/oradata/orcl/srl02.log' size 50m;
idle>alter database add standby logfile '/u01/app/oracle/oradata/orcl/srl03.log' size 50m;
idle>alter database add standby logfile '/u01/app/oracle/oradata/orcl/srl04.log' size 50m;
----应用日志，开启redoapply
idle> alter database recover managed standby database using current logfile disconnect;
---------取消日志应用 recover managed standby database cancel;
# 主库添加srl日志,转为备库时需要
# alter database add standby logfile '/u01/app/oracle/oradata/orcl/srl01.log' size 50m;
# alter database add standby logfile '/u01/app/oracle/oradata/orcl/srl02.log' size 50m;
# alter database add standby logfile '/u01/app/oracle/oradata/orcl/srl03.log' size 50m;
# alter database add standby logfile '/u01/app/oracle/oradata/orcl/srl04.log' size 50m;
4.查看主/备应用日志情况
4.1主库切换日志
sys@ORCL>alter system switch logfile;
4.2备库出现新归档
----备库orclps出现新的归档
idle>select sequence#, applied from v$archived_log;
SEQUENCE# APPLIED
---------- ---------
7 YES
8 YES
9 IN-MEMORY
----备库orclps传输模式
idle> select protection_mode, protection_level from v$database;
PROTECTION_MODE PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
----主库上最大性能改为最大可用
sys@ORCL>alter database set standby database to maximize availability;
sys@ORCL>show parameter log_archive_dest_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string service=orclps sync affirm net
_timeout=10 valid_for=(online_
logfile,primary_role) db_uniqu
e_name=orclps
sys@ORCL>select protection_mode,protection_level from v$database;
PROTECTION_MODE PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY
4.3主库上查看备库应用情况（可以把name字段也加上）
sys@ORCL>select sequence#, applied from v$archived_log;
SEQUENCE# APPLIED
---------- ---------
6 NO
7 NO
8 NO
8 YES
9 YES
9 NO
10 YES
10 NO
11 YES
11 NO
12 YES
12 NO
13 YES
13 NO
14 NO
14 NO
16 rows selected.
------上面applied应用：NO的代表本地归档，是不需要应用的，YES的代表备库传输后已经应用。orclps备库是从8号归档开始应用
4.4 查看备库数据文件存放位置
idle>select name from v$controlfile;
idle>select name from v$datafile;
idle>select name from v$tempfile;
idle>select group#,member,type from v$logfile;
GROUP# MEMBER TYPE
---------- -------------------------------------------------------------------------------- ----------
3 /u01/app/oracle/fast_recovery_area/ORCLPS/onlinelog/o1_mf_3_ckvxlzto_.log ONLINE
2 /u01/app/oracle/fast_recovery_area/ORCLPS/onlinelog/o1_mf_2_ckvxlydr_.log ONLINE
1 /u01/app/oracle/fast_recovery_area/ORCLPS/onlinelog/o1_mf_1_ckvxlwvd_.log ONLINE
4 /u01/app/oracle/oradata/orcl/srl01.log STANDBY
5 /u01/app/oracle/oradata/orcl/srl02.log STANDBY
6 /u01/app/oracle/oradata/orcl/srl03.log STANDBY
7 /u01/app/oracle/oradata/orcl/srl04.log STANDBY
7 rows selected.
------后续转为主库后，redo log可以自己调整一下，上面是duplicate主库到备库时，自动生成的redo log
5.级联配置之备库设置
5.1 备库开启ADG模式
------备库不是一定要开启ADG，备库在mount下也是可以的完成级联库搭建的
idle>alter database recover managed standby database cancel;
idle>alter database open;
sys@ORCL>alter database recover managed standby database using current logfile disconnect;
sys@ORCL>select sequence#, applied from v$archived_log;
SEQUENCE# APPLIED
---------- ---------
12 YES
13 YES
14 YES
8 rows selected.
sys@ORCL>select database_role,switchover_status,db_unique_name from v$database;
DATABASE_ROLE SWITCHOVER_STATUS DB_UNIQUE_NAME
---------------- -------------------- ------------------------------
PHYSICAL STANDBY NOT ALLOWED orclps
5.2 备库参数设置
sys@ORCL>select log_mode,force_logging from v$database;
LOG_MODE FOR
------------ ---
ARCHIVELOG YES
sys@ORCL>alter system set log_archive_config='dg_config=(orcl,orclps,orclstd)';
sys@ORCL>alter system set log_archive_dest_3='service=orclstd sync affirm net_timeout=10 valid_for=(standby_logfile,standby_role) db_unique_name=orclstd';
--------- primary_role / standby_role/ all_roles online_logfile/ standby_logfile/ all_logfiles
--------- 这几个参数的含义注意理解
sys@ORCL>create pfile='/home/oracle/orclstd.ora' from spfile;
5.3 备库TNS配置追加
[oracle@orcl ~]$ vim /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
ORCLSTD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.23)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orclstd)
)
)
6.级联库orclstd配置
6.1 环境变量
[oracle@dg2 ~]$ vim .bash_profile
export ORACLE_SID=orclstd
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=orclstd
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@orcl ~]$ . .bash_profile
6.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
6.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=orclstd)
(SID_NAME=orclstd)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)))
[oracle@dg2 ~]$ lsnrctl start
6.4 配置TNS
[oracle@dg2 ~]$ vim /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
ORCLPS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.22)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCLPS)
)
)
ORCLSTD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.23)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orclstd)
)
)
6.5 参数文件设置
[oracle@orcl ~]$ scp orclstd.ora 192.168.11.23:/home/oracle/
----修改参数
SQL> !vim /home/oracle/orclstd.ora
*.db_name='orcl'
*.db_unique_name='orclstd'
*.fal_client='orclstd'
*.fal_server='orclps'
----暂时删除参数
*.log_archive_dest_3='service=orclstd sync affirm net_timeout=10 valid_for=(standby_logfile,standby_role) db_unique_name=orclstd'
----创建spfile，启动到nomount
idle>create spfile from pfile='/home/oracle/orclstd.ora';
idle>startup nomount
6.6 密钥文件创建
[oracle@dg2 ~]$cd $ORACLE_HOME/dbs
[oracle@dg2 dbs]$ orapwd file=orapworclstd password=sys
7.duplicate到级联库
7.1 登录测试
[oracle@dg2 ~]$ tnsping orclps
[oracle@dg2 ~]$ tnsping orclstd
[oracle@dg2 ~]$ sqlplus sys/sys@orclps as sysdba
[oracle@dg2 dbs]$ sqlplus sys/sys@orclstd as sysdba
7.2 duplicate复制数据库到orclstd
[oracle@orcl ~]$ rman target sys/sys@orclps auxiliary sys/sys@orclstd
Recovery Manager: Release 11.2.0.4.0 - Production on Mon Apr 25 19:37:45 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，不然会报错
7.3 级联库orclstd调整srl日志
----查看当前状态
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
orclstd MOUNTED
----如果需要手动启动备用数据库：
---- startup nomount
---- alter database mount standby database;
----查看是否有srl日志
set linesize 200
set pagesize 999
col member for a80
select group#,type,member from v$logfile;
GROUP# TYPE MEMBER
---------- ------- --------------------------------------------------------------------------------
3 ONLINE /u01/app/oracle/fast_recovery_area/ORCLSTD/onlinelog/o1_mf_3_cl7h802v_.log
2 ONLINE /u01/app/oracle/fast_recovery_area/ORCLSTD/onlinelog/o1_mf_2_cl7h7z1l_.log
1 ONLINE /u01/app/oracle/fast_recovery_area/ORCLSTD/onlinelog/o1_mf_1_cl7h7y17_.log
4 STANDBY /u01/app/oracle/fast_recovery_area/ORCLSTD/onlinelog/o1_mf_4_cl7h8102_.log
5 STANDBY /u01/app/oracle/fast_recovery_area/ORCLSTD/onlinelog/o1_mf_5_cl7h8291_.log
6 STANDBY /u01/app/oracle/fast_recovery_area/ORCLSTD/onlinelog/o1_mf_6_cl7h83dn_.log
7 STANDBY /u01/app/oracle/fast_recovery_area/ORCLSTD/onlinelog/o1_mf_7_cl7h84r2_.log
7 rows selected.
------有redo和srl日志，不用再添加，当然这些日志都可以自己再手动调整位置
------redo日志需要转为主库才能调整，现在先调整srl日志
SQL> alter database drop standby logfile group 4;
SQL> alter database drop standby logfile group 5;
SQL> alter database drop standby logfile group 6;
SQL> alter database drop standby logfile group 7;
SQL> alter database add standby logfile '/u01/app/oracle/oradata/orcl/srl01.log' size 50m;
SQL> alter database add standby logfile '/u01/app/oracle/oradata/orcl/srl02.log' size 50m;
SQL> alter database add standby logfile '/u01/app/oracle/oradata/orcl/srl03.log' size 50m;
SQL> alter database add standby logfile '/u01/app/oracle/oradata/orcl/srl04.log' size 50m;
SQL> select group#,type,member from v$logfile;
GROUP# TYPE MEMBER
---------- ------- --------------------------------------------------------------------------------
3 ONLINE /u01/app/oracle/fast_recovery_area/ORCLSTD/onlinelog/o1_mf_3_cl7hf6kq_.log
2 ONLINE /u01/app/oracle/fast_recovery_area/ORCLSTD/onlinelog/o1_mf_2_cl7hf4mo_.log
1 ONLINE /u01/app/oracle/fast_recovery_area/ORCLSTD/onlinelog/o1_mf_1_cl7hf33q_.log
4 STANDBY /u01/app/oracle/oradata/orcl/srl01.log
5 STANDBY /u01/app/oracle/oradata/orcl/srl02.log
6 STANDBY /u01/app/oracle/oradata/orcl/srl03.log
7 STANDBY /u01/app/oracle/oradata/orcl/srl04.log
7 rows selected.
7.4开启级联库redoapply
SQL> alter database recover managed standby database using current logfile disconnect;
---------取消日志应用 recover managed standby database cancel;
8.查看备库/级联库应用日志情况
8.1 级联库日志应用情况
----主库切换日志
sys@ORCL>alter system switch logfile;
----级联库出现新日志
SQL> select sequence#, applied from v$archived_log;
SEQUENCE# APPLIED
---------- ---------
15 IN-MEMORY
SQL> select protection_mode, protection_level from v$database;
PROTECTION_MODE PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY
----备库orclps日志应用情况
sys@ORCL>select sequence#, applied from v$archived_log;
SEQUENCE# APPLIED
---------- ---------
12 YES
13 YES
14 YES
15 YES
15 YES
10 rows selected.
8.2 主库上创建表，级联库查看
----先开启级联库ADG
SQL> alter database recover managed standby database cancel;
SQL> alter database open;
SQL> alter database recover managed standby database using current logfile disconnect;
----主库orcl上创建表，并插入数据
sys@ORCL>create table shall(shall int);
begin
for i in 1..100000 loop
insert into shall values(i);
end loop;
commit;
end;
/
PL/SQL procedure successfully completed.
----备库orclps能实时查询到数据
sys@ORCL>select count(*) from shall;
COUNT(*)
----------
100000
----级联库orclstd不能查询到数据
SQL> select count(*) from shall;
select count(*) from shall
*
ERROR at line 1:
ORA-00942: table or view does not exist
----主库orcl切换日志
sys@ORCL>alter system switch logfile;
----此时级联库orclstd可以查询到数据
SQL> select count(*) from shall;
COUNT(*)
----------
0
SQL> select count(*) from shall;
COUNT(*)
----------
100000
----------备库是实时数据，而级联库需要等备库归档后才能同步