- 浏览: 187614 次
- 性别:
- 来自: 杭州
文章分类
最新评论
oracle 10g dataguard搭建步骤
- 博客分类:
- oracle高级
一、主库操作
1、修改主库属性:
alter system force logging;
##查看状态
select FORCE_LOGGING from v$database;
2、修改数据库为归档模式:
archive log list;
shutdown immediate;
startup mount;
alter database archivelog;(alter database noarchivelog;关掉归档模式)
archive log list;
3、添加standby logfile(也可以不加)
为主数据库添加"备用联机日志文件",这里要保证备日志文件与主库联机日志文件相同大小。
添加备用日志文件是规则:
备用日志最少应该比redo log 多一个。推荐的备重做日志数依赖于主数据库上的线程数。
(每线程日志文件最大数目 + 1 ) * 线程数
alter database add standby logfile
group 4('/oracle2/app/oracle/oradata/std_redo04a.log','/oracle2/app/oracle/oradata/std_redo04b.log')size 50m,
group 5('/oracle2/app/oracle/oradata/std_redo05a.log','/oracle2/app/oracle/oradata/std_redo05b.log')size 50m,
group 6('/oracle2/app/oracle/oradata/std_redo06a.log','/oracle2/app/oracle/oradata/std_redo06b.log')size 50m,
group 7('/oracle2/app/oracle/oradata/std_redo07a.log','/oracle2/app/oracle/oradata/std_redo08b.dbf')size 50m;
否则备库在应用时报如下信息:
RFS[1]: No standby redo logfiles created
RFS[1]: Archived Log: '/oracle2/arch/1_30_633287861.dbf'
在主库添加完standby logfile后,当主库切换后备库后会自动使用备库的redo logfile,具体应用信息如下:
RFS[1]: Successfully opened standby log 4:'/oracle2/app/oracle/oradata/10g/redo04.log'
RFS[1]: Successfully opened standby log 4: '/oracle2/app/oracle/oradata/10g/redo04.log'
4、修改主库参数文件:
10g.__db_cache_size=1207959552
10g.__java_pool_size=16777216
10g.__large_pool_size=16777216
10g.__shared_pool_size=352321536
10g.__streams_pool_size=0
*.audit_file_dest='/oracle2/app/oracle/admin/10g/adump'
*.background_dump_dest='/oracle2/app/oracle/admin/10g/bdump'
*.compatible='10.2.0.3.0'
*.control_files='/oracle2/app/oracle/oradata/10g/control01.ctl','/oracle2/app/oracle/oradata/10g/control02.ctl','/oracle2/app/oracle/oradata/10g/control03.ctl'
*.core_dump_dest='/oracle2/app/oracle/admin/10g/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='10g'
*.DB_UNIQUE_NAME='10gpri' ###必须 定义每个数据库的唯一标识
*.log_archive_config='DG_CONFIG=(10gpri,10gstandby)' ###必须
*.log_archive_dest_1='location=/oracle2/arch/VALID_FOR=(ALL_LOGFILES,ALL_ROLES)' DB_UNIQUE_NAME='10gpri' ###必须 本地的归档路径
*.LOG_ARCHIVE_DEST_2='SERVICE=10gstandby arch ASYNCVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=10gstandby' ###必须(远程服务器端的归档日志)
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.FAL_SERVER=10gpri ###定义FAL服务器的Oracle Net服务的名称*.FAL_CLIENT=10gstandby ### 定义备数据库的Oracle Net服务名 (这两个参数在主库可有可无,但备库必须有)
*.db_recovery_file_dest='/oracle2/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=10gXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=1707081728
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1610612736
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/oracle2/app/oracle/admin/10g/udump'
*.STANDBY_FILE_MANAGEMENT=AUTO ###设置为AUTO,使得当数据文件添加到主数据库或者从主数据库删除的时候,对应的修改能够在备用数据库中自动执行.
5、用pfile启动,再重新创建spfile.
shutdown immediate;
startup pfile='./pfile.pra';
create spfile from pfile='./pfile.ora';
shutdown immediate;
startup;
6、在主库创建密码文件、以及控制文件。
orapwd file=orapw10gstandby.ora password=change_on_install entries=10
alter database create standby database controlfile '/tmp/standby.ctl';
7、TNS信息如下:
主库
10g=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =192.169.1.204)(PORT = 1921))
)
(CONNECT_DATA =
(SID = 10g)
)
)
10gpri=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =192.169.1.224)(PORT = 1921))
)
(CONNECT_DATA =
(SID = 10g)
)
)
10gstandby =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =192.169.1.204)(PORT = 1921))
)
(CONNECT_DATA =
(SID = 10g)
)
)
8、对主库进行全库备份
run {
allocate channel t1 type disk;
allocate channel t2 type disk;
backup database format '/tmp/full_%s';
release channel t1;
release channel t2;
}
二、备库操作如下:
1、依照主库的数据文件位置,在备库上创建相应的目录结构(最好与主库一致);
mkdir -p /oracle/app/oracle/oradata/....
2、通过FTP把在主库创建的密码文件、standby controlfile、full backup database文件到备库主机上。
3、备份的参数文件内容:
10g.__db_cache_size=1207959552
10g.__java_pool_size=16777216
10g.__large_pool_size=16777216
10g.__shared_pool_size=352321536
10g.__streams_pool_size=0
*.audit_file_dest='/oracle2/app/oracle/admin/10g/adump'
*.background_dump_dest='/oracle2/app/oracle/admin/10g/bdump'
*.compatible='10.2.0.3.0'
*.control_files='/oracle2/app/oracle/oradata/10g/control01.ctl','/oracle2/app/oracle/oradata/10g/control02.ctl','/oracle2/app/oracle/oradata/10g/control03.ctl'
*.core_dump_dest='/oracle2/app/oracle/admin/10g/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='10g'
*.DB_UNIQUE_NAME='10gstandby' ##
*.log_archive_config='DG_CONFIG=(10gpri,10gstandby)' ##
*.log_archive_dest_1='location=/oracle2/arch/VALID_FOR=(ALL_LOGFILES,ALL_ROLES)'DB_UNIQUE_NAME='10gstandby' ##
*.LOG_ARCHIVE_DEST_2='SERVICE=10gpri -arch ASYNCVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=10gpri' ##
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.FAL_SERVER=10gstandby ##
*.FAL_CLIENT=10gpri
##*.db_recovery_file_dest='/oracle2/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=10gXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=1707081728
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1610612736
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/oracle2/app/oracle/admin/10g/udump'
*.STANDBY_FILE_MANAGEMENT=AUTO ##
4、分别对备库进行全库恢复,并启动到standby database mount状态下。
用PFILE文件起动到nomount状态下,恢复控制文件;起动到mount状态下,然后再恢复全库。
5、TNS信息如下:
10g=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =192.169.1.204)(PORT = 1921))
)
(CONNECT_DATA =
(SID = 10g)
)
)
10gpri=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =192.169.1.224)(PORT = 1921))
)
(CONNECT_DATA =
(SID = 10g)
)
)
10gstandby =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =192.169.1.204)(PORT = 1921))
)
(CONNECT_DATA =
(SID = 10g)
)
)
用TNSPING 对方是否通。
6、修改备库处于应用归档状态
alter database recover managed standby database disconnect fromsession;
如果主库从不过来归档,可以通过在主库侧手工修改参数如下:
ALTER SYSTEM SET log_archive_dest_state_2='DEFER' SCOPE=MEMORY;
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=MEMORY;
7、测试
通过在主库执行alter system switch logfile;切换日志可以观察到备库会自动应用通过主库传过来的日志。
三、切换测试
1、在主库端
select switchover_stats from v$database;
如果是to standby 表可以正常切换.
直接执行ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
否则执行: ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBYWITH SESSION SHUTDOWN;
shutdown immediate;
startup nomount;
alter database mount standby database;
2、在备库
在备库
SELECT SWITCHOVER_STATUS FROM V$DATABASE;
如果是to_primary 表可以正常切换.
执行:ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
否则执行: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITHSESSION SHUTDOWN;
shutdown immediate;
startup;
然后观察主备库日志,如果正常的话会看到备库会自动应用日志.
转载:http://bbs.chinaunix.net/thread-998901-1-1.html
另荐:小步老师oracle dataguard快速搭建视频:http://www.boobooke.com/v/bbk3476/
发表评论
-
oracle数组游标批量插入
2013-12-07 16:40 3351declare type ary is table ... -
使用PL/SQL DEVELOPER 经常会碰到“DYNAMIC PERFORMANCE TABLES NOT ACCESSIBLE”错误
2012-10-16 08:45 898在使用PL/SQL Developer工具登陆一个新 ... -
buffer busy waits 等待事件(热点块竞争)
2012-08-31 16:31 1699*************************** ... -
ora-00054:resource busy and acquire with nowait specified解决方法
2012-06-16 09:35 908当某个数据库用户在数据库中插入、更新、删除一个表的数据, ... -
Oracle 9i 使用Statpack snap 步骤和说明
2012-06-14 08:40 17111、telnet到远程的数据库服务器 CMD>t ... -
oracle Statspack 报告解析之 Shared Pool Statistics(共享池统计信息)
2012-06-21 12:57 1186Shared Pool Statistics ... -
ORACLE里锁模式v$locked_object , locked_mode
2012-06-16 09:37 1412ORACLE里锁有以下几种模式: 0:none ... -
Oracle全面优化:如何对Oracle进行系统的全面优化?
2012-06-13 08:36 1155数据库系统和操作系统一样,在计算机上安装成功后,还需要进 ... -
oracle 参数 FAST_START_MTTR_TARGET
2012-06-14 08:41 1245参数FAST_START_MTTR_TARGET ... -
ORA-12519: TNS:no appropriate service handler found 解决
2012-06-15 08:35 811ORA-12519: TNS:no appropriat ... -
oracle Statspack 报告解析之 Top 5 Timed Events(等待事件)
2012-06-20 08:29 1965常见等待事件说明: ... -
index cluster/hash cluster/sort hash cluster
2012-06-15 08:36 1232簇是一个或多个表的组合,这些表的数据存储在相同的数据块 ... -
Oracle flashback几种方式
2012-06-20 08:26 661flashback(闪回)是Oracle10 ... -
ORACLE Audit 审计
2012-06-21 12:58 13101、什么是审计 审计(A ... -
Oracle建立用户 ,限制用户访问资源
2012-06-14 08:38 1061一、目的: Oracle系 ... -
oracle block 格式
2012-06-19 08:35 1056create table t(n number); ... -
oracle Statspack 报告解析之 Load Profile
2012-06-21 12:57 2057该部分提供每秒和每个事物的统计信息,是监控系统吞吐量和负载变化 ... -
数据库突然变慢,怎么办?
2012-06-16 09:38 2840第一章 检查系统的状态 1.1 使用sar来检查操作系 ... -
oracle library cache 中的hash算法原理
2012-06-18 08:24 1084详细文章:http://bkeep.blog.163.com/ ... -
Oracle数据库提高命中率及相关优化
2012-06-18 08:25 877关于Oracle中各个命中率的计算以及相关的调优 ...
相关推荐
ORACLE 11G 搭建DATAGUARD步骤,搭建rac环境之后,还需搭建dg,让你的数据库做到更安全。
ORACLE 11G 搭建DATAGUARD步骤
ORACLE 11G 搭建DATAGUARD步骤操作总结,一步一步教会你怎么搭建。适合初学者。
ORACLE11G搭建DATAGUARD步骤.pdf
2020年生产环境windows2012r2+oracle 11.2.0.4+psu+dataguard搭建步骤
(完整word版)ORACLE11G搭建DATAGUARD步骤.doc
这是我在以前在工作中总结的Oracle 10g Dataguard在Windows下搭建的详细步骤的总结,希望对大家有帮助哦。
redhat 7.6 oracle 11.0.2.4 DataGuard 搭建详细步骤
Oracle_dataguard的3种创建方法(绝对完整)
。。。
...
13 ADG Oracle 10g DataGuard实施文档 14 ADG ORACLE 11G DATAGUARD 搭建(RMAN duplicate方式-相同目录结构) 15 ADG oracle 11g rac+单机dataguard实施文档(详细) 16 ADG Oracle 11g:ORACLE ACTIVE DATA ...
详细描述oracle DG的搭建步骤,以及切换
oracle DG搭建步骤
Oracle 10g DATAGUARD安装配置 维护 1.1 LINUX 操作系统的安装和配置.................................................................. 1.2 DataGuard 安装环境..................................................
oracle 19c容器多租户 rac to rac的adg测试部署方案,完全实测步骤。 使用了DG_BROCKER管理,方便switchover。
在CentOS7.6上安装Oracle12数据库,对数据库进行安装操作,配置过程非常详细,根据步骤能够搭建好; 后续继续搭建Dataguard