本文详细介绍了一个基于归档日志的RAC到单节点standby的dataguard配置流程，包括参数设置、文件转换及数据库复制等步骤，并展示了如何观察standby节点如何应用来自两个RAC节点的日志。
（以下为作业内容）
1.配置一个基于归档日志的RAC到单节点standby的data guard，贴出主要的配置操作。
2.分别从RAC的两个节点切换日志，让归档传到standby，在standby上观察它是如何应用来自两个节点的归档日志的，贴出观察到的结果。
=========================================================================
1. RAC+单实例DATAGUARD 配置情况:
RAC 两节点：
192.168.10.81 ract1
192.168.10.82 ract2
存储：ASM
DB_UNIQUE_NAME= tdb
实例：tdb1; tdb2;
DG:
192.168.10.85 racdg3
存储 ：本地文件目录存储
数据库名： tdb
DB_UNIQUE_NAME=tdg;
一、RAC状态 （已配置完成）
1.ract1 /etc/hosts
--------------------------
#public ip
192.168.10.81 ract1
192.168.10.82 ract2
#priv ip
192.168.0.81 ract1-private
192.168.0.82 ract2-private
#vip ip
192.168.10.83 ract1-vip
192.168.10.84 ract2-vip
#scan ip
192.168.10.80 ract-scan
192.168.10.85 racdg3
2.RAC状态：
[root@ract1 ~]# /opt/app/grid/bin/crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.CRS.dg ora....up.type ONLINE ONLINE ract1
ora.DATA.dg ora....up.type ONLINE ONLINE ract1
ora....ER.lsnr ora....er.type ONLINE ONLINE ract1
ora....N1.lsnr ora....er.type ONLINE ONLINE ract1
ora....VERY.dg ora....up.type ONLINE ONLINE ract1
ora.asm ora.asm.type ONLINE ONLINE ract1
ora.eons ora.eons.type ONLINE ONLINE ract1
ora.gsd ora.gsd.type OFFLINE OFFLINE
ora....network ora....rk.type ONLINE ONLINE ract1
ora.oc4j ora.oc4j.type OFFLINE OFFLINE
ora.ons ora.ons.type ONLINE ONLINE ract1
ora....SM1.asm application ONLINE ONLINE ract1
ora....T1.lsnr application ONLINE ONLINE ract1
ora.ract1.gsd application OFFLINE OFFLINE
ora.ract1.ons application ONLINE ONLINE ract1
ora.ract1.vip ora....t1.type ONLINE ONLINE ract1
ora....SM2.asm application ONLINE ONLINE ract2
ora....T2.lsnr application ONLINE ONLINE ract2
ora.ract2.gsd application OFFLINE OFFLINE
ora.ract2.ons application ONLINE ONLINE ract2
ora.ract2.vip ora....t1.type ONLINE ONLINE ract2
ora....ry.acfs ora....fs.type ONLINE ONLINE ract1
ora.scan1.vip ora....ip.type ONLINE ONLINE ract1
ora.tdb.db ora....se.type ONLINE ONLINE ract1
---------------------------------------------------------
2.#STANDBY listener.ora
# listener.ora Network Configuration File: /opt/app/oracle/product/11.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = tdg)
(ORACLE_HOME = /opt/app/oracle/product/11.2.0)
(SID_NAME = tdg)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.85)(PORT = 1521))
)
)
#STANDBY 端的 SID_NAME，SID_NAME 设置和PRIMARY 端的 一致。
-----------------------------------------------------------------------
3. tnsnames.ora
[oracle@ractdg3 dbs]$ cat ../network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /opt/app/oracle/product/11.2.0/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
tdb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.81)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.82)(PORT = 1521))
(LOAD_BALANCE = yes)
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = tdb)
(FAILOVER_MODE =
(TYPE = session)
(METHOD = basic)
(RETRIES = 180)
(DELAY = 5)
)
)
)
tdb1 =
(DESCRIPTION=
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.81)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = tdb)
)
)
tdb2 =
(DESCRIPTION=
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.82)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = tdb)
)
)
tdg =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST= 192.168.10.85)(PORT=1521))
(CONNECT_DATA =
(SERVICE_NAME = tdg)
)
)
#STANDBY 端的 SERVICE_NAME 和PRIMARY 设置成一致。
---------------------------------------------------------------------
二、配置DATAGUARD相关参数：
2.1.配置参数：
SQL> alter system set log_archive_config='DG_CONFIG=(tdg,tdb)';
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30;
System altered.
SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;
System altered.
SQL> ALTER SYSTEM SET FAL_SERVER=tdg;
System altered.
SQL> alter system set FAL_CLIENT=tdb;
System altered.
SQL>
SQL> alter system set standby_file_management=auto;
System altered.
SQL>
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=tdg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=tdg';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
#添加STANDBY LOGFILE:
select group#,THREAD#,l.MEMBERS,l.STATUS from v$log l
alter database add STANDBY logfile thread 1 group 5 ;
alter database add STANDBY logfile thread 1 group 6 ;
alter database add STANDBY logfile thread 1 group 7 ;
alter database add STANDBY logfile thread 2 group 8 ;
alter database add STANDBY logfile thread 2 group 9 ;
alter database add STANDBY logfile thread 2 group 10 ;
#2.2 生成pfile，并修改。
SQL> create pfile='/tmp/inittdb.ora' from spfile;
File created.
并修改PFILE,添加以下内容:
*.db_file_name_convert='/opt/app/oracle/oradata/tdg','+DATA/tdb/datafile','/opt/app/oracle/oradata/tdg/tempfile','+DATA/tdb/tempfile'
*.LOG_FILE_NAME_CONVERT='/opt/app/oracle/flash_recovery_area/tdg/onlinelog','+DATA/tdb/onlinelog','/opt/app/oracle/flash_recovery_area/tdg/onlinelog' ,'+RECOVERY/tdb/archivelog'
（注：这里有个错误后面有介绍：'+RECOVERY/tdb/archivelog'）
#2.3创建控制文件 ：
SQL> alter database create standby controlfile as '/tmp/control01.ctl';
Database altered.
#2.4创建密码文件
[oracle@ract1 ~]$ orapwd file=/tmp/orapwtdg.ora password=sa force=y ignorecase=y
#2.5把文件拷贝到ractdg3 服务器：
scp /tmp/orapwtdg.ora ractdg3:/opt/app/oracle/product/11.2.0/dbs/orapwtdg.ora
scp /tmp/inittdb.ora ractdg3:/tmp/inittdb.ora
scp /tmp/control01.ctl ractdg3:/opt/app/oracle/oradata/tdg/control01.ctl
scp /tmp/control01.ctl ractdg3:/opt/app/oracle/flash_recovery_area/tdg/control02.ctl
(拷贝文件 前建立相关目录：/opt/app/oracle/oradata/tdg，/opt/app/oracle/flash_recovery_area/tdg)
#2.6添加以下内容
*.db_file_name_convert='+DATA/tdb/datafile','/opt/app/oracle/oradata/tdg','+DATA/tdb/tempfile','/opt/app/oracle/oradata/tdg/tempfile'
*.LOG_FILE_NAME_CONVERT='+DATA/tdb/onlinelog','/opt/app/oracle/flash_recovery_area/tdg/onlinelog','+RECOVERY/tdb/archivelog','/opt/app/oracle/flash_recovery_area/tdg/onlinelog'
(这里有个错误，因为'+RECOVERY/tdb/archivelog' 这个，搞得RMAN复制数据时，出错了，弄了一天。
正确为： '+RECOVERY/tdb/onlinelog' 这里同步的是在线重做日志，而不是归档日志。)
修改前PRIMARY 端的pfile
[oracle@ract1 dbs]$ cat /tmp/inittdb.ora
tdb1.__db_cache_size=1476395008
tdb2.__db_cache_size=1476395008
tdb1.__java_pool_size=16777216
tdb2.__java_pool_size=16777216
tdb1.__large_pool_size=16777216
tdb2.__large_pool_size=16777216
tdb1.__oracle_base='/opt/app/oracle'#ORACLE_BASE set from environment
tdb2.__oracle_base='/opt/app/oracle'#ORACLE_BASE set from environment
tdb1.__pga_aggregate_target=1375731712
tdb2.__pga_aggregate_target=1375731712
tdb1.__sga_target=2030043136
tdb2.__sga_target=2030043136
tdb1.__shared_io_pool_size=0
tdb2.__shared_io_pool_size=0
tdb1.__shared_pool_size=503316480
tdb2.__shared_pool_size=503316480
tdb1.__streams_pool_size=0
tdb2.__streams_pool_size=0
*.audit_file_dest='/opt/app/oracle/admin/tdb/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.0.0'
*.control_files='+DATA/tdb/controlfile/current.256.823282229','+RECOVERY/tdb/controlfile/current.256.823282237'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_file_name_convert='tdg','tdb'
*.db_name='tdb'
*.db_recovery_file_dest='+RECOVERY'
*.db_recovery_file_dest_size=10485760000
*.diagnostic_dest='/opt/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=tdbXDB)'
*.fal_client='tdb'
*.fal_server='tdg'
tdb1.instance_number=1
tdb2.instance_number=2
*.db_file_name_convert='/opt/app/oracle/oradata/tdg','+DATA/tdb/datafile','/opt/app/oracle/oradata/tdg/tempfile','+DATA/tdb/tempfile'
*.LOG_FILE_NAME_CONVERT='/opt/app/oracle/flash_recovery_area/tdg/onlinelog','+DATA/tdb/onlinelog','/opt/app/oracle/flash_recovery_area/tdg/onlinelog' ,'+RECOVERY/tdb/onlinelog'
*.log_archive_config='DG_CONFIG=(tdg,tdb)'
*.log_archive_dest_2='SERVICE=tdg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=tdg'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=30
*.log_file_name_convert='tdg','tdb'
*.memory_target=3401580544
*.open_cursors=300
*.processes=150
*.remote_listener='ract-scan:1521'
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
tdb2.thread=2
tdb1.thread=1
tdb1.undo_tablespace='UNDOTBS1'
tdb2.undo_tablespace='UNDOTBS2'
修改后STANDBY 端的pfile参数如下：
[oracle@ractdg3 dbs]$ cat /tmp/inittdb.ora
tdg.__db_cache_size=1476395008
tdg.__java_pool_size=16777216
tdg.__large_pool_size=16777216
tdg.__oracle_base='/opt/app/oracle'#ORACLE_BASE set from environment
tdg.__pga_aggregate_target=1375731712
tdg.__sga_target=2030043136
tdg.__shared_io_pool_size=0
tdg.__shared_pool_size=503316480
tdg.__streams_pool_size=0
*.audit_file_dest='/opt/app/oracle/admin/tdg/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/opt/app/oracle/oradata/tdg/control01.ctl','/opt/app/oracle/flash_recovery_area/tdg/control02.ctl'
*.db_block_size=8192
*.db_create_file_dest='/opt/app/oracle/oradata'
*.db_domain=''
*.db_file_name_convert='tdb','tdg'
*.db_name='tdb'
*.db_recovery_file_dest='/opt/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=10485760000
*.diagnostic_dest='/opt/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=tdbXDB)'
*.fal_client='tdg'
*.fal_server='tdb1','tdb2'
*.db_file_name_convert='+DATA/tdb/datafile','/opt/app/oracle/oradata/tdg','+DATA/tdb/tempfile','/opt/app/oracle/oradata/tdg/tempfile'
*.LOG_FILE_NAME_CONVERT='+DATA/tdb/onlinelog','/opt/app/oracle/flash_recovery_area/tdg/onlinelog','+RECOVERY/tdb/onlinelog','/opt/app/oracle/flash_recovery_area/tdg/onlinelog'
*.log_archive_config='DG_CONFIG=(tdg,tdb)'
*.log_archive_dest_2='SERVICE=tdg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=tdg'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=30
*.log_file_name_convert='tdg','tdb'
*.memory_target=3401580544
*.open_cursors=300
*.processes=150
*.remote_listener='ract-scan:1521'
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
thread=1
undo_tablespace='UNDOTBS1'
#2.7 在STANDBY 端
SQL> create spfile from pfile='/tmp/inittdb.ora';
File created.
SQL> startup nomount;
#2.8复制数据库
[oracle@ract1 dbs]$ rman target sys/sa@tdb auxiliary sys/sa@tdg nocatalog
rman>duplicate target database for standby from active database nofilenamecheck;
#2.9 启动备库
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> create spfile from pfile='/opt/app/oracle/product/11.2.0/dbs/inittdg.ora';
File created.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 2042241024 bytes
Fixed Size 1337548 bytes
Variable Size 553649972 bytes
Database Buffers 1476395008 bytes
Redo Buffers 10858496 bytes
SQL> alter database mount standby database;
Database altered.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
# 开启STANDBY 数据库
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;
#启用STANDBY数据库，进入RECOVER模式：
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
#启动到只读模式：
alter database open read only;
#转换到REAL-TIME模式下：
--alter database recover managed standby database cancel;
--ALTER DATABASE OPEN;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT from session;
#2.10检查状态：
#PRIMARY 端：
SQL> select sequence#,status from v$log;
SEQUENCE# STATUS
---------- ------------------------------------------------
127 INACTIVE
128 CURRENT
29 INACTIVE
30 CURRENT
SQL>
#STANDBY 端
SQL> select archived_thread#,archived_seq#,APPLIED_THREAD#,APPLIED_SEQ# from v$archive_dest_status;
ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ#
---------------- ------------- --------------- ------------
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
1 127 1 127
#RAC PRIMARY 端： 另一个节点插入数据并切换日志：
SQL> insert into test values(5);
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> select sequence#,status from v$log;
SEQUENCE# STATUS
---------- ------------------------------------------------
127 INACTIVE
128 CURRENT
31 CURRENT
30 ACTIVE
SQL> /
#STANDBY 端
SQL> select archived_thread#,archived_seq#,APPLIED_THREAD#,APPLIED_SEQ# from v$archive_dest_status;
ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ#
---------------- ------------- --------------- ------------
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
2 30 2 30
扫一扫
2661
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
