11g data guard(PHYSICAL STANDBY)配置

联系:手机(+86 13429648788) QQ(107644445)QQ咨询惜分飞

标题:11g data guard(PHYSICAL STANDBY)配置

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

一、 Preparing the Primary Database for Standby Database Creation
1、安装redhat 5.5系统和11g r2数据库,相关参数如下

Database DB_UNIQUE_NAME TNS Ip
Primary first first 192.168.1.2
Physical standby sencode sencode 192.168.1.3

2、Enable Forced Logging
2.1)查看数据库是否开启force log
select force_logging from v$database;
2.2)如果得到结果是YES,跳过2.3)
2.3)ALTER DATABASE FORCE LOGGING;

3、Enable Archiving
3.1)查看数据库是否启动归档模式
archive log list;
3.2)如果得到类此下面结果跳过步骤3.3)
Database log mode Archive Mode
Automatic archival Enabled
3.3)开启归档模式
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

4、创建Primary Database和Standby Database的tns,确保两者能够相互tnsping通
Note:tns配置成dedicated server模式

二、Step-by-Step Instructions for Creating a Physical Standby Database
1、Create a Backup Copy of the Primary Database Datafiles
方法一、冷备份
关闭Primary Database数据库(immediate)拷贝datafile、redo、undo、temp到Standby Database位置
Note:如果确保自己有能力在没有redo、undo、temp文件的情况下恢复数据库,可以只拷贝datafile(建议全拷)
方法二、rman备份
Rman 备份,然后在Standby端恢复

2、Create a Control File for the Standby Database
ALTER DATABASE CREATE STANDBY CONTROLFILE AS ‘/tmp/control01.ctl’;
Scp /tmp/control01.ctl oracle@192.168.1.3:/u01/oradata/first/

3、Create a Parameter File for the Standby Database
3.1)Create a parameter file
CREATE PFILE=’/tmp/pfile’ FROM SPFILE;

3.2)Modify the Parameter File
3.2.1)Primary Database
first.__db_cache_size=71303168
first.__java_pool_size=4194304
first.__large_pool_size=4194304
first.__oracle_base=’/u01′#ORACLE_BASE set from environment
first.__pga_aggregate_target=121634816
first.__sga_target=192937984
first.__shared_io_pool_size=0
first.__shared_pool_size=100663296
first.__streams_pool_size=4194304
*.audit_file_dest=’/u01/admin/first/adump’
*.audit_trail=’db’
*.compatible=’11.2.0.0.0′
*.control_files=’/u01/oradata/first/control01.ctl’,
‘/u01/flash_recovery_area/first/control02.ctl’
*.db_block_size=8192
*.db_domain=’dg’
*.db_name=’first’
*.db_unique_name=’first’

*.db_recovery_file_dest=’/u01/flash_recovery_area’
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest=’/u01′
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=firstXDB)’
*.log_archive_config=’dg_config=(first,second)’
*.log_archive_dest_1=’LOCATION=/u01/archive valid_for=(all_logfiles,all_roles)
*.log_archive_dest_2=’service=second lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=second’
*.log_archive_format=’%t_%s_%r.arc’
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
fal_server=second

*.memory_target=314572800
*.nls_language=’SIMPLIFIED CHINESE’
*.nls_territory=’CHINA’
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.undo_tablespace=’UNDOTBS1′
standby_file_management=auto

3.2.2)Standby Database
second.__db_cache_size=71303168
second.__java_pool_size=4194304
second.__large_pool_size=4194304
second.__oracle_base=’/u01′#ORACLE_BASE set from environment
second.__pga_aggregate_target=121634816
second.__sga_target=192937984
second.__shared_io_pool_size=0
second.__shared_pool_size=100663296
second.__streams_pool_size=4194304
*.audit_file_dest=’/u01/admin/second/adump’
*.audit_trail=’db’
*.compatible=’11.2.0.0.0′
*.control_files=’/u01/oradata/first/control01.ctl’
*.db_block_size=8192
*.db_domain=’dg’
*.db_name=’first’
*.db_unique_name=’second’

*.db_recovery_file_dest=’/u01/flash_recovery_area’
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest=’/u01′
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=firstXDB)’
*.log_archive_config=’dg_config=(first,second)’
*.log_archive_dest_1=’LOCATION=/u01/archive valid_for=(all_logfiles,all_roles) db_unique_name=second’
*.log_archive_dest_2=’service=first lgwr async valid_for=(online_logfiles,primary_role)
*.log_archive_format=’%t_%s_%r.arc’
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
fal_server=first

*.memory_target=314572800
*.nls_language=’SIMPLIFIED CHINESE’
*.nls_territory=’CHINA’
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.undo_tablespace=’UNDOTBS1′
standby_file_management=auto

scp /tmp/pfile oracle@192.168.1.3:/tmp/
拷贝主库密码文件到备库,确保sys密码一致
scp /u01/oracle/dbs/orapwfirst oracle@192.168.1.3:/u01/oracle/dbs/orapwsecond

4、Start the Physical Standby Database
4.1)Startup pfile=’/tmp/pfile’ mount(可以直接open,oracle会自动置于read only模式)
4.2)Add STANDBY LOGFILE
ALTER DATABASE ADD STANDBY LOGFILE (‘/u01/oradata/first/slog1.rdo’) SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE (‘/u01/oradata/first/slog1.rdo’) SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE (‘/u01/oradata/first/slog1.rdo’) SIZE 50M;
4.3)Start Redo Apply
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

5、Start Primary Database
Startup pfile=’/tmp/pfile’;

6、Verify the Physical Standby Database Is Performing Properly
6.1)standby database,
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
6.2)Primary Database
ALTER SYSTEM SWITCH LOGFILE;
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
6.3)standby database
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
观察SEQUENCE#在Primary Database和standby database是否一致

7、Create Spfile
create spfile from pfile=’/tmp/pfile’;(Primary Database和standby database)

此条目发表在 Data Guard 分类目录。将固定链接加入收藏夹。

发表评论

电子邮件地址不会被公开。 必填项已用 * 标注

您可以使用这些 HTML 标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>