Creating a 10gr2 Data Guard Physical Standby database with Real-Time apply [ID 343424.1]
|
|
|
Modified 14-OCT-2009Type BULLETINStatus PUBLISHED
|
|
In this Document
Purpose
Scope and Application
Creating a 10gr2 Data Guard Physical Standby database with Real-Time apply
Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.5
Information in this document applies to any platform.
This document has been created with step-by-step instructions on how to create a Data Guard Physical Standby configuration manually without the aid of the broker. All steps are derived from the 10GR2 documentation.
It is assumed that the reader is aware of the features of RDBMS such as SPFILE usage, storage such as ASM, OCFS. Please avail these features where appropriate and test them before implementing in production.
This document should aid testers, dba's and support analyst's with step-by-step instructions and additional information has been provided as notes to elaborate or clarify a point. All steps shown here were tested on a linux platform, however the steps are generic enough for all flavours of unix.
Creating a 10gr2 Data Guard Physical Standby database with Real-Time apply
Primary host : rhclu1
Standby host : rhclu2
ORACLE_SID=v102p
Kernel :Linux x86 2.4.21-27.EL
Service names : Primary rhclu1p / Standby rhclu2p
Primary Initialization parameters
db_name='v102p'
db_unique_name=rhclu1p
##COMMON TO BOTH PRIMARY AND STANDBY ROLES
LOG_ARCHIVE_CONFIG='DG_CONFIG=(rhclu1p,rhclu2p)'
LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/admin/v102p/arch
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=rhclu1p'
LOG_ARCHIVE_DEST_2='SERVICE=rhclu2p
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=rhclu2p'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_MAX_PROCESSES=2
log_archive_format='%t_%s_%r.dbf'
#SPECIFIC TO STANDBY ROLE
STANDBY_FILE_MANAGEMENT=AUTO
STANDBY_ARCHIVE_DEST='/u01/app/oracle/admin/v102p/arch'
FAL_SERVER=rhclu2p
FAL_CLIENT=rhclu1p
Standby Initialization parameters
db_name='v102p'
db_unique_name=rhclu2p
#COMMON TO BOTH PRIMARY AND STANDBY ROLES
LOG_ARCHIVE_CONFIG='DG_CONFIG=(rhclu1p,rhclu2p)'
LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/admin/v102p/arch
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=rhclu2p'
LOG_ARCHIVE_DEST_2='SERVICE=rhclu1p LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=rhclu1p'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_MAX_PROCESSES=2
log_archive_format='%t_%s_%r.dbf'
#SPECIFIC TO STANDBY ROLE
STANDBY_FILE_MANAGEMENT=AUTO
STANDBY_ARCHIVE_DEST='/u01/app/oracle/admin/v102p/arch'
FAL_SERVER=rhclu1p
FAL_CLIENT=rhclu2p
Note 1:
- Create just one initialization parameter file that contains parameters used in both Roles (primary / standby)
Parameters specific to that role will be used eg: FAL_ parameter will be used only when the database is in standby role
For Log_archive_dest_n the VALID_FOR attribute will differentiate the roles, if not specified the default is(ALL_LOGFILES,ALL_ROLES). This VALID_FOR attribute allows us to use the same initialization parameter file for both the primary and standby roles.
Note 2:
- Since the file structure is the same on both the nodes I have not specifed the file name convert strings.
If the file structure is different then we will need to specify the additional two parameters :
DB_FILE_NAME_CONVERT - Converts the path names of the primary database data files to the standby data file pathnames
LOG_FILE_NAME_CONVERT - Converts the path names of the primary database log files to the path names on the standby database
Example :
string1 is the pattern of the primary database filename /u01/prim/
string2 is the pattern of the standby database filename /u01/stdby/
string3 is the pattern of the primary database filename /u02/prim/
string4 is the pattern of the standby database filename /u02/stdby/
DB_FILE_NAME_CONVERT = 'string1' , 'string2' , 'string3' , 'string4' , ...
= ‘/u01/prim/’,’/u01/stdby, ‘/u02/prim/’,’/u02/stdby’
LOG_FILE_NAME_CONVERT = 'string1' , 'string2' , 'string3' , 'string4' , ...
= ‘/u01/prim/’,’/u01/stdby, ‘/u02/prim/’,’/u02/stdby’
Enable Archiving :
Ensure that the primary is in archive log mode
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
Note 3:
- LOG_ARCHIVE_START = TRUE has been deprecated since 10gr1
Net Services :
Set up the net services as entries in the tnsnames.ora such a way that you can sqlplus to the remote database using this alias.
Eg: from the primary
sqlplus sys/<password>@rhclu2p as sysdba
SQL>SELECT DB_UNIQUE_NAME FROM V$DATABASE;
DB_UNIQUE_NAME
--------------------------
rhclu2p
Do the same from the standby to the primary.
TNSNAMES.ORA sample
- The ip addresses are dummy in this context use the appropriate ip / hostname.
RHCLU1P =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.103)(PORT = 1521)))
(CONNECT_DATA =
(SERVER = DEDICATED) (SERVICE_NAME = v10g))
)
RHCLU2P =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.104)(PORT = 1521)))
(CONNECT_DATA =
(SERVER = DEDICATED) (SERVICE_NAME = v10g))
)
Standby Redo Log (SRL) creation :
SQL>SELECT * FROM V$LOGFILE;
GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE
------ ------- ------- ----------------------------------------------------------------------------
1 ONLINE /u01/app/oracle/admin/v102p/redo01.log NO
2 ONLINE /u01/app/oracle/admin/v102p/redo02.log NO
3 ONLINE /u01/app/oracle/admin/v102p/redo03.log NO
SQL>SELECT BYTES FROM V$LOG;
BYTES
======
10485760
10485760
10485760
Create the SRL's :
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 4
('/u01/app/oracle/admin/v102p/srl01.log') SIZE 10M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 5
('/u01/app/oracle/admin/v102p/srl02.log') SIZE 10M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 6
('/u01/app/oracle/admin/v102p/srl03.log') SIZE 10M;
Backup the Primary
Take a cold backup of the primary. ( You can also take a hot backup or oracle recommends the usage of RMAN.)
SQL>SHUTDOWN IMMEDIATE
- Backup the datafiles, online redologs and the standby logs if created and ftp / restore it on the standby site;
Note 4:
- Standby redo logs can be created even after the standby has been created. In this case we created the SRL's on the primary before the creation of the standby database. Also, we have used the default ARCH to ship the logs across in the log_archive_dest_2 parameter. In 10g the archiver (ARCn) process or the log writer (LGWR) process on the primary database can transmit redo data directly to remote standby redo logs.
-MAXLOGFILEdefaults to 16,To create online redologs + standby redologs more than this ensure that you recreate the control file to modify maxlogfile to accomodate this number.
Create the standby controlfile :
Primary :
SQL>STARTUP MOUNT;
SQL>ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/stdby.ctl';
SQL>ALTER DATABASE OPEN;
- ftp / scp the stdby.ctl file to the standby site. we can multiplex this by making copies of this stdby.ctl on different mountpoints on the standby and editing the control_files parameter in the init.ora to reflect this.
- Copy the orapw<sid>.ora file from the OH/dbs directory of the primary to the same location on the standby.
Starting the standby :
SQL>STARTUP MOUNT
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
Verify the Standby :
- Identify the existing files on the standby
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME
FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
.......................
.......................
115 29-NOV-05 29-NOV-05
116 29-NOV-05 29-NOV-05
- Switch a log on the primary
SQL>ALTER SYSTEM SWITCH LOGFILE;
- Run the above sql again on the standby to see the new log that has arrived from the primary.
.......................
.......................
115 29-NOV-05 29-NOV-05
116 29-NOV-05 29-NOV-05
117 29-NOV-05 29-NOV-05
Verify that these logs were applied :
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG
ORDER BY SEQUENCE#;
...........
115 YES
116 YES
117 YES
- Standby Set up is complete.
Note 5: ARCH processing :
- In our setup so far we have used the arch process for log_archive_dest_2 ( if nothing is specified then default is arch ) and SRL's are created on the standby;
- Archiving happens when there is a log switch on the primary.
- On the primary database, after the ARC0 process successfully archives the local online redo log to the local destination (LOG_ARCHIVE_DEST_1), the ARC1 process transmits redo from the local archived redo log files (instead of the online redo log files) to the remote standby destination (LOG_ARCHIVE_DEST_2).
- On the remote destination, the remote file server process (RFS) will, in turn, write the redo data to an archived redo log file from a standby redo log file. Log apply services use Redo Apply ( MRP ) to apply the redo to the standby database.
Real-time apply :
When real-time apply is enabled, the log apply services can apply redo data as it is received, without waiting for the current standby redo log file to be archived.
In this example we are going to use LGWRon the primary for redo transport just to prove that a committed record on the primary without switching a log will show up on the standby. However real-time apply will work with both LGWR and ARCH using SRL's.
- Set up log_archive_dest_2 on the primary with LGWR ASYNC or SYNC
log_archive_dest_2='SERVICE=rhclu2p LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=rhclu2p'
- shutdown and startup the primary or if done dynamically switch a log file
- You will see the following message in the alert log
****************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
*****************************************************************
- On the standby cancel out of the current managed recovery
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
- Place it back in recovery with Real time apply
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT
LOGFILE DISCONNECT;
Test Real time apply :
Primary : create a table 'test' and insert a record.
SQL>INSERT INTO test VALUES ( 101, 'testing');
SQL>COMMIT;
Do not switch a log
On the standby :
SQL>SELECT PROCESS, STATUS,SEQUENCE#,BLOCK#,BLOCKS, DELAY_MINS FROM
V$MANAGED_STANDBY;
Notice the block# for the RFS and MRP0 increasing
Cancel out of real time apply and open it in read only mode
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL>ALTER DATABASE OPEN READ ONLY;
SQL>SELECT * FROM.test;
You will see the committed record.
Place the standby back in managed recover mode
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT
LOGFILE DISCONNECT;
( This will take the standby directly from read only mode and place it in managed recovery mode )
Note 6:
- There is no requirement that the standby recovery once started in real-time apply should only be run in this mode. We can go between the two modes as desired, but only one recovery can occur at a given time. please refer the Online documentation for the details of the Syntax.
---------------------------------------------------------------------------------------------------
Blog: http://blog.csdn.net/tianlesoftware
网上资源: http://tianlesoftware.download.csdn.net
相关视频:http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx
DBA1 群:62697716(满); DBA2 群:62697977(满) DBA3 群:62697850(满)
DBA 超级群:63306533(满); DBA4 群: 83829929 DBA5群: 142216823
聊天 群:40132017
--加群需要在备注说明Oracle表空间和数据文件的关系,否则拒绝申请
分享到:
相关推荐
2、各台linux机器上传hbase安装包:hbase-0.98.12.1-hadoop2-bin.tar.gz 3、解压jar包:tar -zxvf hbase-0.98.12.1-hadoop2-bin.tar.gz 4、把hadoop的hdfs-site.xml和core-site.xml 放到hbase/conf下: cd root/...
在STM32上实现standby模式,经过调试后可用
Data Guard Configurations,Primary Database,Standby Databases,Data Guard Services.,Redo Transport Services.,Apply Services, Role Transitions.,Data Guard Broker.,Using Oracle Enterprise Manager ...
目录10内文件为书中第10章出现的部分脚本,分为两部分:physical目录对应创建物理Standby时,Primary和Standby库中相关配置,包括客户端初始化参数文件,监听文件以及tns配置文件;而logical目录中对应的是创建逻辑...
how to open a standby database while you lost your primary database
要求:HSRP1 为 active,HSRP2 为 standby 虚拟 IP 为 192.168.38.254,并且开启抢占机制。 任务 2:边界网络地址规划 步骤 1:border s0/1/0 ----ISP 1 s0/1/0 使用 IP 地址 200.10.1.0/30。 任务 3:外部网络环境...
Chapter 1: Data Converter History ........................................................................................3 Section 1-1: Early History ....................................................
Oracle8i------------------_Standby_Database.ppt
μA Standby Current When Shutdown .Low Dropout : 220mV @ 300mA .Wide Operating Voltage Ranges : 2.5V to 5.5V .TTL-Logic-Controlled Shutdown Input .Low Temperature Coefficient .Current Limiting ...
Stm8s105k6低功耗模式唤醒,模拟红外遥控38Khz编码,已通过测试OK,已用于项目上,源码直接可用。
F5部署红宝书Part+I+-+基础安装部署L:· 安装前准备 · 设备初始化 · ⺴⽹网络层配置 ...· Active/Standby配置 · Active/Active/Standby配置 · 安全配置基线 · VIPRION配置注意事项
2015 Oracle 技术嘉年华(OTN)分会场2Joel - Using Data Guard & Standby DBs to support DB Rolling Upgrades
Data Guard Concepts and Administration, Primary Database , Standby Databases , Far Sync Instances , Zero Data Loss Recovery Appliance, Redo Transport Services , Apply Services , Role Transitions , ...
如何在Oracle 10g On Windows下创建standby database.doc
Generate an analog signal with a mC 080405di.pdf AGC amplifier features 60-dB dynamic range Precision active load operates as low as 2V Squeeze extra outputs from a pin-limited microcontroller ...
全志 A64 全套软硬件开发资料技术文档资料参考设计原理图PCB等资料合集,可以做为你的学习设计参考: a64 3G Dongle Support List.pdf A64 brief v1.0 20150323.pdf A64 Camera模块开发说明文档.pdf A64 Camera自适应...
oracle standby data guard
Oracle Standby Database技术的原理及实现.pdf
[ 3.968638] [mmc]: sdc2 set ios: clk 25000000Hz bm PP pm ON vdd 3.3V width 1 timing LEGACY(SDR12) dt B [ 3.968734] [mmc]: mclk 0xf1c20090 0xc100000b [ 3.989421] Bluetooth: BNEP filters: protocol ...
自己在centos linux 上做的 oracle physical standby配置 ,已测试通过