首页 » ORACLE 9i-23c » 配置11Gr2 physical dataguard,11203 RAC 到single instance

配置11Gr2 physical dataguard,11203 RAC 到single instance

11G DATA Guard在新特性physical standby可以在apply log时,open read only,并且可以real-time apply,这样standby db 也就告别了用户老觉的机器闲置,丢掉了在发生故障时才见其价值的歧视。

standby db在高可用中不但可以用来做灾备,还可以平时用来测试、做报表统计,及logical standby的Rolling upgrade

下面配置一种DATA Guard,primary db是2nodes 11203 RAC,physical standby是11203 的single instance,Primary db有用到ASM存储文件,Single instance只用local filesystem

primary database server:

[grid@znode1 ~]$ olsnodes
znode1
znode2

sys@RAC>select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

sys@RAC>show parameter cluster
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     TRUE
cluster_database_instances           integer     2
cluster_interconnects                string

sys@RAC>select group#,thread#,sequence#,bytes,blocksize,members from v$log;

GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS
---------- ---------- ---------- ---------- ---------- ----------
1          1         93   52428800        512          2
2          1         92   52428800        512          2
3          2         33   52428800        512          2
4          2         32   52428800        512          2

sys@RAC>select member,group# from v$logfile order by 2;
MEMBER                                                           GROUP#
------------------------------------------------------------ ----------
+FLRV/rac/onlinelog/group_1.260.790169423                             1
+DBDG/rac/onlinelog/group_1.262.790169417                             1
+FLRV/rac/onlinelog/group_2.259.790169425                             2
+DBDG/rac/onlinelog/group_2.261.790169423                             2
+DBDG/rac/onlinelog/group_3.258.790169683                             3
+FLRV/rac/onlinelog/group_3.258.790169685                             3
+DBDG/rac/onlinelog/group_4.257.790169685                             4
+FLRV/rac/onlinelog/group_4.257.790169689                             4

8 rows selected.

sys@RAC>select name from v$datafile;
NAME
----------------------------------------------------
+DBDG/rac/datafile/system.260.790169267
+DBDG/rac/datafile/sysaux.267.790169269
+DBDG/rac/datafile/undotbs1.263.790169271
+DBDG/rac/datafile/users.266.790169271
+DBDG/rac/datafile/undotbs2.259.790169575

sys@RAC>show parameter name
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string
db_name                              string      rac
db_unique_name                       string      rac
global_names                         boolean     FALSE
instance_name                        string      rac1
service_names                        string      rac.anbob.com

NOTE:
每个THREAD 两组日志,每组日志两个成员分别在+DBDG,+FLRV

standby database server:

sys@ORA11GR2>select * from v$version WHERE ROWNUM=1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production


sys@ORA11GR2>show parameter cluster
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     FALSE
cluster_database_instances           integer     1
cluster_interconnects                string

NOTE:
Standby db版本与primary db相同,但是一个单实例



确保实例使用spfile启动,在primary db为了直观增加一个service name,不是必须的,修改service_names 参数,默认是db_unique_name + [.db_domain],这是一个动态参数不需要重启实例,可以通过

system set也可以通过srvctl add service增加,pmon process会自动注册到listener上。
sys@RAC>show parameter service

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      rac.anbob.com

sys@RAC>alter system set service_names='rac.anbob.com','prmrdb';

sys@RAC>show parameter service

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      rac.anbob.com, prmrdb

下面正式开始DATA GUARD配置


1.physical standby安装oracle software . 另外创建db所需要的目录机构如adump,oradata,这里我们用OMF结构也就是ORACLE管理方式

[oracle@dbserver1 ~]$ . 11g.env
[oracle@dbserver1 ~]$ env |grep ORACLE
ORACLE_SID=stdby
ORACLE_BASE=/u02/app/oracle
ORACLE_TERM=xterm
ORACLE_HOME=/u02/app/oracle/product/11.2.0/db_2

[oracle@dbserver1 admin]$ mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/{a,dp}dump
[oracle@dbserver1 admin]$ mkdir -p $ORACLE_BASE/fast_recovery_area/$ORACLE_SID/onlinelog
[oracle@dbserver1 admin]$ mkdir -p $ORACLE_BASE/oradata/$ORACLE_SID/datafile
[oracle@dbserver1 admin]$ mkdir -p $ORACLE_BASE/oradata/$ORACLE_SID/tempfile
[oracle@dbserver1 admin]$ mkdir -p $ORACLE_BASE/oradata/$ORACLE_SID/controlfile


2. 在standby db配置tnsnames.ora记录,这里我用的是SCAN 地址,也就是可以连接任何节点

primarytns =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan.anbob.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prmrdb.anbob.com)
)
)



3. 增加一个静态监听,在 physical standby db

cd $ORACLE_HOME/network/admin
vi listener.ora

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = stdby)
(SID_NAME = stdby)
(ORACLE_HOME = /u02/app/oracle/product/11.2.0/db_2)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbserver1)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)



4. 在RAC的每个节点增加TNSNAMES.ORA记录,在primary db

[oracle@znode1 ~]$ cd $ORACLE_HOME/network/admin
[oracle@znode1 admin]$ vi tnsnames.ora
stdbytns =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.168.173)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = stdby)
)
)


5. 启动force logging 在 primary DB
sys@RAC>select force_logging from v$database;
FOR
---
NO
sys@RAC>alter database force logging;

sys@RAC>select force_logging from v$database;
FOR
---
YES




6,对于网络认证我这里没有用SSL,而是PASSWORD文件,复制password文件从primary db到standby host's ORACLE_HOME/dbs,并重命名为standby instance

[oracle@znode1 dbs]$ scp orapwrac1 192.168.168.173:/u02/app/oracle/product/11.2.0/db_2/dbs
oracle@192.168.168.173's password:
orapwrac1         100% 1536     1.5KB/s   00:00

login standby host
[oracle@dbserver1 dbs]$ mv orapwrac1 orapwstdby


7.修改 data guard 参数 to primary. 包括DG配置和 redo传输参数如SYNC,ASYNC, AFFIRM, NOAFFIRM, archive log , datafile/logfile 文件名转换

sys@RAC>alter system set log_archive_config='dg_config=(rac,stdby)' scope=both ;
System altered.

tip:
DG_CONFIG list that includes the DB_UNIQUE_NAME of the primary database and each standby database

sys@RAC>alter system set log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=rac' scope=both;
System altered.

sys@RAC>alter system set log_archive_dest_2='service=STDBYTNS LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role)

db_unique_name=stdby' scope=both;
System altered.

sys@RAC>alter system set log_archive_dest_state_1='enable' scope=both;
sys@RAC>alter system set log_archive_dest_state_2='enable' scope=both;
sys@RAC>alter system set fal_server='STDBYTNS' scope=both;
sys@RAC>alter system set log_archive_max_processes=10 scope=both;
sys@RAC>alter system set db_file_name_convert='+DBDG/rac','/u02/app/oracle/oradata/stdby' scope=spfile;
sys@RAC>alter system set log_file_name_convert='+FLRV/rac','/u02/app/oracle/fast_recovery_area/stdby','+DBDG/rac','/u02/app/oracle/fast_recovery_area/stdby' scope=spfile;
sys@RAC>alter system set standby_file_management='AUTO' scope=both;


8,启用primary库rac 到archivelog mode
[oracle@znode1 oracle]$ srvctl stop database  -d rac
[oracle@znode1 oracle]$ srvctl start database  -d rac -o mount
idle>archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     72
Current log sequence           73

idle>alter database archivelog;
Database altered.

idle>archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     72
Next log sequence to archive   73
Current log sequence           73


9,在standby database 创建初始化参数,只写db_name

[oracle@dbserver1 dbs]$ vi initstdby.ora

*.db_name='stdby'

10,standby database 启动到nomount状态
[oracle@dbserver1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Aug 8 17:59:06 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

idle>startup nomount
ORACLE instance started.
Total System Global Area  167395328 bytes
Fixed Size                  1343640 bytes
Variable Size             113250152 bytes
Database Buffers           50331648 bytes
Redo Buffers                2469888 bytes
idle>


11,用11g的新特性rman active duplicate 还原主库到备库,在primary host
[oracle@znode1 oracle]$ rman target / auxiliary sys/oracle@stdbytns
rman>

duplicate target database for standby from active database
spfile
parameter_value_convert 'rac','stdby','RAC','stdby'
set db_unique_name='stdby'
set db_file_name_convert='+DBDG/rac','/u02/app/oracle/oradata/stdby','+DBDG/rac/tempfile','/u02/app/oracle/oradata/stdby'
set log_file_name_convert='+FLRV/rac','/u02/app/oracle/fast_recovery_area/stdby','+DBDG/rac','/u02/app/oracle/oradata/stdby'
set control_files='/u02/app/oracle/oradata/stdby/controlfile/control01.ctl'
set log_archive_max_processes='5'
set LOG_ARCHIVE_FORMAT='log%t_%s_%r.arc'
set REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'
set fal_server='primarytns'
SET cluster_database='false'
set db_create_file_dest  = '/u02/app/oracle/oradata/stdby'
set db_recovery_file_dest  = '/u02/app/oracle/fast_recovery_area'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(rac,stdby)'
set log_archive_dest_2='service=PRIMARYTNS LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=rac'
set log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=stdby'
set audit_file_dest = '/u02/app/oracle/admin/stdby/adump'
set diagnostic_dest= '/u02/app/oracle'
reset REMOTE_LISTENER
reset local_listener

TIP:
保证以下参数指定目录在备库存在audit_file_dest,control_files,如果没有audit_file_dest目录,备库会启动后无法登录遇ORA-09925 错误,解决方法是如下
[root@dbserver1 ~]# ps -ef|grep ora_|grep -v grep|awk  '{print $2}'|xargs kill -9
[oracle@dbserver1 dbs]$ rm -rf /dev/shm/ora_stdby*

如果上面失败第二次duplicate会遇RMAN-05537,解决方法是shutdown abort standby db,rm spfile,startup nomount with pfile,re-run rman dumplicate on primary db


12,修改standby database spfile 记录,把primary instance记录删除,重建Standby spfile
sys@RAC>create pfile from spfile;
File created.

[oracle@dbserver1 dbs]$ vi initstdby.ora
rac1.__db_cache_size=209715200
rac2.__db_cache_size=209715200
stdby.__db_cache_size=339738624
stdby.__java_pool_size=4194304
rac1.__large_pool_size=4194304
rac2.__large_pool_size=4194304
stdby.__large_pool_size=4194304
rac1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
stdby.__oracle_base='/u02/app/oracle'#ORACLE_BASE set from environment
rac1.__pga_aggregate_target=293601280
rac2.__pga_aggregate_target=293601280
stdby.__pga_aggregate_target=339738624
...

note:
删除rac1和rac2开头的实例参数,可以按需要修改要修改的参数,因为从primary db传过来两个undo TBS(每个实例一个),需在standby db指定undo表空间*.undo_tablespace='UNDOTBS1',重建spfile

sql>create spfile from pfile;
File created.

sql>startup force

13,在standby db增加standby 日志 做real-time apply,standby log 每个thread 日志组比online log 组数多一个,standby日志大小与online redo相同



primary db:
sys@RAC>select group#,thread#,bytes,members from v$log;

GROUP#    THREAD#      BYTES    MEMBERS
---------- ---------- ---------- ----------
1          1   52428800          2
2          1   52428800          2
3          2   52428800          2
4          2   52428800          2

standby db:
alter database add standby logfile size 52428800;
增加6组(number of group per thread +1)* number of thread

14,修改standby database 到archive log mode

15,Starting Redo Apply on the physical standby database

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

USING CURRENT LOGFILE 表示接受到redo log立及应用,启用real-time apply
DISCONNECT 表示后台进程应用


有时因为primary db log_archive_dest_2 值  service=STDBYTNS LGWR ASYNC NOAFFIRM,不能保证适时传输时,需要alter system archive log current 或alter system switch logfile切日志查看

同步数据是否成功,当然也可以改log_archive_dest_2 的值。

验证日志传输与应用
SELECT RESETLOGS_ID,THREAD#,SEQUENCE#,STATUS,ARCHIVED FROM V$ARCHIVED_LOG

sys@RAC>SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID=2;

RECOVERY_MODE
-----------------------
MANAGED REAL TIME APPLY

SQL>SELECT MESSAGE FROM V$DATAGUARD_STATUS;
...
LNS: Beginning to archive log 1 thread 1 sequence 117
ARCH: Completed archiving thread 1 sequence 116 (6010717-6011086)
LNS: Completed archiving log 1 thread 1 sequence 117
ARCH: Beginning to archive thread 1 sequence 117 (6011086-6011618)
LNS: Standby redo logfile selected for thread 1 sequence 118 for destination LOG_ARCHIVE_DEST_2
LNS: Beginning to archive log 2 thread 1 sequence 118
ARCH: Completed archiving thread 1 sequence 117 (6011086-6011618)

sys@RAC>SELECT name, value, datum_time, time_computed FROM V$DATAGUARD_STATS;

NAME                             VALUE                          DATUM_TIME                     TIME_COMPUTED
-------------------------------- ------------------------------ ------------------------------ ------------------------------
transport lag                    +00 00:00:00                   08/13/2012 16:09:02            08/13/2012 16:09:03
apply lag                        +00 00:00:00                   08/13/2012 16:09:02            08/13/2012 16:09:03
apply finish time                +00 00:00:00.000                                              08/13/2012 16:09:03
estimated startup time           20                                                            08/13/2012 16:09:03

The apply lag metric is computed using data that is periodically received from the primary database. The DATUM_TIME column contains a timestamp of when this data was last

received by the standby database. The TIME_COMPUTED column contains a timestamp taken when the apply lag metric was calculated

sql>SELECT * FROM V$STANDBY_EVENT_HISTOGRAM WHERE NAME = 'apply lag'
sys@RAC>SELECT * FROM V$STANDBY_EVENT_HISTOGRAM WHERE NAME = 'apply lag';

NAME                  TIME UNIT                  COUNT LAST_TIME_UPDATED
--------------- ---------- ---------------- ---------- --------------------
apply lag                0 seconds                2402 08/13/2012 16:12:45
apply lag                1 seconds                  43 08/13/2012 16:12:40
apply lag                2 seconds                  13 08/13/2012 16:02:40
apply lag                3 seconds                  13 08/13/2012 16:01:08
apply lag                4 seconds                   4 08/13/2012 13:16:47
...

当然可以建一张表来测试同步了,在两个节点上的事务也都可以同步到standby实例上

Stopping Redo Apply
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

打赏

对不起,这篇文章暂时关闭评论。