怎样配置ORACLE 的DATA GUARD呀? 紧急求救,大量送分! 我还没有听说过DATA GUARD。帮你UP。 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 先实现两台PC机之间的以后也要考虑SUn工作站之间的哪位大侠实验过吗?我看了ORACLE的帮助DataGuard是能实现的,但配置的时候就是通过不了 注一下:一下说明是fenng本人写的,可不是我写的,呵呵! 说明;Oracle9i数据库的Data Guard 特性确保对数据进行完整的保护,是Oracle 9i的一个关键特性之一.Data Guard可以创建物理的Standby数据库,也可以创建逻辑的Standby数据库,还可以混合使用,灵活性比较强.这个文档是Fenng练习时候记下来的东西,谈不上是什么心得.*这不过是一份安装过程中的笔记而已* 如果对这个有更高的期望,或者想得到关于Oracle数据库的data guard 和Standby的更多信息,请参考官方的文档. 系统环境:Windows 2000 专业版 SP3 512M 内存 现有的数据库实例(Primary)名字:Demo 预创建的Standby 数据库实例名字:Pstandby 数据库版本信息: SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production PL/SQL Release 9.2.0.1.0 - Production CORE 9.2.0.1.0 Production TNS for 32-bit Windows: Version 9.2.0.1.0 - Production NLSRTL Version 9.2.0.1.0 - Production 准备工作 首先确认Primary数据库是否在归档模式下 SQL> show user USER is "SYS" SQL> SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination d:\oracle\oradata\demo\archive Oldest online log sequence 42 Next log sequence to archive 44 Current log sequence 44 如果不在归档模式下,调整数据库。 首先提交命令修改SPfile: SQL>alter system set LOG_ARCHIVE_START=TRUE scope=spfile; 然后关闭数据库实例 SQL>SHUTDOWN 备份数据库 SQL>STARTUP MOUNT SQL>ALTER DATABASE ARCHIVELOG; SQL>ALTER DATABASE OPEN; SQL>SHUTDOWN IMMEDIATE 备份 1. Primary Database 需要做的准备工作: 1.1 激活 Forced Logging SQL> ALTER DATABASE FORCE LOGGING; 1.2 设置本地归档目标 SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=D:\oracle\oradata\DEMO\Archive' SCOPE=BOTH; 此操作直接生效 2.创建物理的Standby数据库 2.1 标记出Primary数据库的数据文件 SQL> select name from v$datafile; NAME ----------------------------------------------------- D:\ORACLE\ORADATA\DEMO\SYSTEM01.DBF D:\ORACLE\ORADATA\DEMO\UNDOTBS01.DBF D:\ORACLE\ORADATA\DEMO\CWMLITE01.DBF D:\ORACLE\ORADATA\DEMO\DRSYS01.DBF D:\ORACLE\ORADATA\DEMO\EXAMPLE01.DBF D:\ORACLE\ORADATA\DEMO\INDX01.DBF D:\ORACLE\ORADATA\DEMO\ODM01.DBF D:\ORACLE\ORADATA\DEMO\TOOLS01.DBF D:\ORACLE\ORADATA\DEMO\USERS01.DBF D:\ORACLE\ORADATA\DEMO\XDB01.DBF D:\ORACLE\ORADATA\DEMO\OEM_REPOSITORY.DBF 11 rows selected. SQL> 2.2 关闭Instance 拷贝数据文件到既定目的地 SQL> SHUTDOWN IMMEDIATE; 2.3 为Standby 数据库创建控制文件 SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'C:\DataGuard\Pstandby\DEMO\DEMOSTANDBY.CTL'; 要注意这个控制文件的名字不要和Primary的控制文件名字重复 2.4 为Standby数据库准备初始化参数文件名字: SQL> CREATE PFILE='C:\DataGuard\Pstandby\DEMO\initpstandby' FROM SPFILE; 2.5 设定初始化Physical Standby Database参数 *.aq_tm_processes=1 *.background_dump_dest='D:\oracle\admin\DEMO\bdump' *.compatible='9.2.0.0.0' *.control_files='C:\DataGuard\Pstandby\DEMO\DEMOSTANDBY.CTL' *.core_dump_dest='D:\oracle\admin\DEMO\cdump' *.db_block_size=8192 *.db_cache_size=19922944 *.db_domain='' *.db_file_multiblock_read_count=32 *.db_name='DEMO' *.dispatchers='(PROTOCOL=TCP) (SERVICE=DEMOXDB)' *.fast_start_mttr_target=300 *.hash_area_size=1048576 *.hash_join_enabled=TRUE *.instance_name='pstandby' *.java_pool_size=20971520 *.job_queue_processes=10 *.large_pool_size=7340032 *.log_archive_dest_1='location=d:\oracle\oradata\demo\archive' *.log_archive_start=TRUE *.open_cursors=300 *.optimizer_mode='FIRST_ROWS' *.pga_aggregate_target=17825792 *.processes=150 *.query_rewrite_enabled='TRUE' *.remote_login_passwordfile='EXCLUSIVE' *.shared_pool_size=33554432 *.sort_area_size=1048576 *.star_transformation_enabled='TRUE' *.timed_statistics=TRUE *.undo_management='AUTO' *.undo_retention=10800 *.undo_tablespace='UNDOTBS1' *.user_dump_dest='D:\oracle\admin\DEMO\udump' lock_name_space=pstandby standby_file_management=AUTO remote_archive_enable=TRUE standby_archive_dest='C:\DataGuard\Pstandby\DEMO\Archive' db_file_name_convert=('D:\oracle\oradata\DEMO', 'C:\DataGuard\Pstandby\DEMO\') log_file_name_convert=('D:\oracle\oradata\DEMO', 'C:\DataGuard\Pstandby\DEMO\') log_archive_dest_1=('LOCATION=C:\DataGuard\Pstandby\DEMO\Archive') 整个操作的过程中,容易出现错误的地方几乎都集中在此处。必须认真仔细的对待这个文件。标记为黑色的地方是需要进行修改的。 2.6 创建一个Windows服务 WINNT> oradim -NEW -SID Pstandby -STARTMODE manual 2.7 Create a Server Parameter File for the Standby Database 可参考执行如下操作: C:\>set oracle_sid=pstandby C:\>sqlplus /nolog SQL> connect / as sysdba SQL> CREATE SPFILE FROM PFILE='C:\DataGuard\Pstandby\DEMO\initPstandby.ora'; 2.8 启动物理Standby数据库 C:\>set oracle_sid=pstandby C:\>sqlplus /nolog SQL> connect / as sysdba SQL> STARTUP NOMOUNT; SQL> ALTER DATABASE MOUNT STANDBY DATABASE; 2.9 在Standby数据库上,初始化Log Apply 服务: SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; 2.10 激活到物理Standby数据库的归档 SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=Pstandby' SCOPE=BOTH; SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH; 2.11 启动远程归档 SQL> ALTER SYSTEM ARCHIVE LOG CURRENT; 3.安装完的的验证: 3.1在database, 查询V$ARCHIVED_LOG (其实也可以直接到相关目录下查看Log是否创建): SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME 2 FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#; SEQUENCE# FIRST_TIME NEXT_TIME ---------- ---------- ---------- 38 23-7? -02 23-7? -02 39 23-7? -02 23-7? -02 40 23-7? -02 23-7? -02 41 23-7? -02 23-7? -02 42 23-7? -02 23-7? -02 3.2 在Primary数据库上,归档当前的Log SQL> ALTER SYSTEM ARCHIVE LOG CURRENT; 3.3 验证是否收到: SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME 2> FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#; SEQUENCE# FIRST_TIME NEXT_TIME ---------- ---------- ---------- 38 23-7? -02 23-7? -02 39 23-7? -02 23-7? -02 40 23-7? -02 23-7? -02 41 23-7? -02 23-7? -02 42 23-7? -02 23-7? -02 43 23-7? -02 23-7? -02 3.4 验证是否新的归档Redo日志已经被应用: SQL> select sequence#,applied from v$archived_log 2 order by sequence#; SEQUENCE# APP ---------- --- 38 YES 39 YES 40 YES 41 YES 42 YES 43 YES OK.表明我们还是成功的。暂时告一段落。 参考文档 Oracle Data Guard Concepts and Administration Release 2 (9.2) Part Number A96653-02 附加内容: primary数据库的Pfile内容: *.aq_tm_processes=1 *.background_dump_dest='D:\oracle\admin\DEMO\bdump' *.compatible='9.2.0.0.0' *.control_files='D:\oracle\oradata\DEMO\CONTROL01.CTL', 'D:\oracle\oradata\DEMO\CONTROL02.CTL','D:\oracle\oradata\DEMO\CONTROL03.CTL' *.core_dump_dest='D:\oracle\admin\DEMO\cdump' *.db_block_size=8192 *.db_cache_size=19922944 *.db_domain='' *.db_file_multiblock_read_count=32 *.db_name='DEMO' *.dispatchers='(PROTOCOL=TCP) (SERVICE=DEMOXDB)' *.fast_start_mttr_target=300 *.hash_area_size=1048576 *.hash_join_enabled=TRUE *.instance_name='DEMO' *.java_pool_size=20971520 *.job_queue_processes=10 *.large_pool_size=7340032 *.log_archive_dest_1='location=d:\oracle\oradata\demo\archive' *.log_archive_dest_2='SERVICE=PSTANDBY' *.log_archive_dest_state_2='ENABLE' *.log_archive_start=TRUE *.open_cursors=300 *.optimizer_mode='FIRST_ROWS' *.pga_aggregate_target=17825792 *.processes=150 *.query_rewrite_enabled='TRUE' *.remote_login_passwordfile='EXCLUSIVE' *.shared_pool_size=33554432 *.sort_area_size=1048576 *.star_transformation_enabled='TRUE' *.timed_statistics=TRUE *.undo_management='AUTO' *.undo_retention=10800 *.undo_tablespace='UNDOTBS1' *.user_dump_dest='D:\oracle\admin\DEMO\udump' 创建过程中的可能的错误:待续 新手的一个问题 应用服务器用32位或64位Oracle客户端连接64位Oracle数据库,性能方面是否有区别? oracle数据脚本采用bat文件导入,导入的汉字变成了乱码,求解决,急————在线等—— 高手请进,一个关于oracle数据库的执行效率问题 如何调用表的数据组成xml文档 紧急求助,存储过程中文问题,在线等待 面试题 -- 取分组后的最新两条记录. 有请大家一齐讨论一下.[转] oracle中有去掉空格的函数吗?谢谢! 何谓oracle9IAS? 请问存储过程中如何执行字符串?等。。。 oracle数据库连接问题 什么是NTL?
以后也要考虑SUn工作站之间的
哪位大侠实验过吗?我看了ORACLE的帮助DataGuard是能实现的,但配置的时候就是通过不了
说明;Oracle9i数据库的Data Guard 特性确保对数据进行完整的保护,是Oracle 9i的一个关键特性之一.Data Guard可以创建物理的Standby数据库,也可以创建逻辑的Standby数据库,还可以混合使用,灵活性比较强.这个文档是Fenng练习时候记下来的东西,谈不上是什么心得.*这不过是一份安装过程中的笔记而已* 如果对这个有更高的期望,或者想得到关于Oracle数据库的data guard 和Standby的更多信息,请参考官方的文档. 系统环境:Windows 2000 专业版 SP3 512M 内存 现有的数据库实例(Primary)名字:Demo
预创建的Standby 数据库实例名字:Pstandby 数据库版本信息: SQL> select * from v$version; BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production 准备工作 首先确认Primary数据库是否在归档模式下
SQL> show user
USER is "SYS"
SQL>
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination d:\oracle\oradata\demo\archive
Oldest online log sequence 42
Next log sequence to archive 44
Current log sequence 44
如果不在归档模式下,调整数据库。
首先提交命令修改SPfile: SQL>alter system set LOG_ARCHIVE_START=TRUE scope=spfile; 然后关闭数据库实例
SQL>SHUTDOWN 备份数据库 SQL>STARTUP MOUNT SQL>ALTER DATABASE ARCHIVELOG;
SQL>ALTER DATABASE OPEN; SQL>SHUTDOWN IMMEDIATE
备份 1. Primary Database 需要做的准备工作: 1.1 激活 Forced Logging SQL> ALTER DATABASE FORCE LOGGING; 1.2 设置本地归档目标 SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=D:\oracle\oradata\DEMO\Archive' SCOPE=BOTH;
此操作直接生效
2.创建物理的Standby数据库 2.1 标记出Primary数据库的数据文件 SQL> select name from v$datafile; NAME
----------------------------------------------------- D:\ORACLE\ORADATA\DEMO\SYSTEM01.DBF
D:\ORACLE\ORADATA\DEMO\UNDOTBS01.DBF
D:\ORACLE\ORADATA\DEMO\CWMLITE01.DBF
D:\ORACLE\ORADATA\DEMO\DRSYS01.DBF
D:\ORACLE\ORADATA\DEMO\EXAMPLE01.DBF
D:\ORACLE\ORADATA\DEMO\INDX01.DBF
D:\ORACLE\ORADATA\DEMO\ODM01.DBF
D:\ORACLE\ORADATA\DEMO\TOOLS01.DBF
D:\ORACLE\ORADATA\DEMO\USERS01.DBF
D:\ORACLE\ORADATA\DEMO\XDB01.DBF
D:\ORACLE\ORADATA\DEMO\OEM_REPOSITORY.DBF 11 rows selected. SQL>
2.2 关闭Instance 拷贝数据文件到既定目的地 SQL> SHUTDOWN IMMEDIATE; 2.3 为Standby 数据库创建控制文件 SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE
AS 'C:\DataGuard\Pstandby\DEMO\DEMOSTANDBY.CTL'; 要注意这个控制文件的名字不要和Primary的控制文件名字重复 2.4 为Standby数据库准备初始化参数文件名字: SQL> CREATE PFILE='C:\DataGuard\Pstandby\DEMO\initpstandby' FROM SPFILE; 2.5 设定初始化Physical Standby Database参数 *.aq_tm_processes=1
*.background_dump_dest='D:\oracle\admin\DEMO\bdump'
*.compatible='9.2.0.0.0'
*.control_files='C:\DataGuard\Pstandby\DEMO\DEMOSTANDBY.CTL'
*.core_dump_dest='D:\oracle\admin\DEMO\cdump'
*.db_block_size=8192
*.db_cache_size=19922944
*.db_domain=''
*.db_file_multiblock_read_count=32
*.db_name='DEMO'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DEMOXDB)'
*.fast_start_mttr_target=300
*.hash_area_size=1048576
*.hash_join_enabled=TRUE
*.instance_name='pstandby'
*.java_pool_size=20971520
*.job_queue_processes=10
*.large_pool_size=7340032
*.log_archive_dest_1='location=d:\oracle\oradata\demo\archive'
*.log_archive_start=TRUE
*.open_cursors=300
*.optimizer_mode='FIRST_ROWS'
*.pga_aggregate_target=17825792
*.processes=150
*.query_rewrite_enabled='TRUE'
*.remote_login_passwordfile='EXCLUSIVE'
*.shared_pool_size=33554432
*.sort_area_size=1048576
*.star_transformation_enabled='TRUE'
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='D:\oracle\admin\DEMO\udump' lock_name_space=pstandby
standby_file_management=AUTO
remote_archive_enable=TRUE
standby_archive_dest='C:\DataGuard\Pstandby\DEMO\Archive'
db_file_name_convert=('D:\oracle\oradata\DEMO', 'C:\DataGuard\Pstandby\DEMO\')
log_file_name_convert=('D:\oracle\oradata\DEMO', 'C:\DataGuard\Pstandby\DEMO\')
log_archive_dest_1=('LOCATION=C:\DataGuard\Pstandby\DEMO\Archive')
整个操作的过程中,容易出现错误的地方几乎都集中在此处。必须认真仔细的对待这个文件。标记为黑色的地方是需要进行修改的。
2.6 创建一个Windows服务 WINNT> oradim -NEW -SID Pstandby -STARTMODE manual 2.7 Create a Server Parameter File for the Standby Database 可参考执行如下操作:
C:\>set oracle_sid=pstandby
C:\>sqlplus /nolog
SQL> connect / as sysdba
SQL> CREATE SPFILE FROM PFILE='C:\DataGuard\Pstandby\DEMO\initPstandby.ora'; 2.8 启动物理Standby数据库 C:\>set oracle_sid=pstandby
C:\>sqlplus /nolog
SQL> connect / as sysdba
SQL> STARTUP NOMOUNT;
SQL> ALTER DATABASE MOUNT STANDBY DATABASE; 2.9 在Standby数据库上,初始化Log Apply 服务: SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
2.10 激活到物理Standby数据库的归档 SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=Pstandby' SCOPE=BOTH;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH; 2.11 启动远程归档 SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
3.安装完的的验证: 3.1在database, 查询V$ARCHIVED_LOG
(其实也可以直接到相关目录下查看Log是否创建): SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME
2 FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#; SEQUENCE# FIRST_TIME NEXT_TIME
---------- ---------- ----------
38 23-7? -02 23-7? -02
39 23-7? -02 23-7? -02
40 23-7? -02 23-7? -02
41 23-7? -02 23-7? -02
42 23-7? -02 23-7? -02 3.2 在Primary数据库上,归档当前的Log SQL> ALTER SYSTEM ARCHIVE LOG CURRENT; 3.3 验证是否收到: SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME
2> FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#; SEQUENCE# FIRST_TIME NEXT_TIME
---------- ---------- ----------
38 23-7? -02 23-7? -02
39 23-7? -02 23-7? -02
40 23-7? -02 23-7? -02
41 23-7? -02 23-7? -02
42 23-7? -02 23-7? -02
43 23-7? -02 23-7? -02
3.4 验证是否新的归档Redo日志已经被应用: SQL> select sequence#,applied from v$archived_log
2 order by sequence#; SEQUENCE# APP
---------- ---
38 YES
39 YES
40 YES
41 YES
42 YES
43 YES OK.表明我们还是成功的。暂时告一段落。
参考文档 Oracle Data Guard Concepts and Administration
Release 2 (9.2)
Part Number A96653-02 附加内容: primary数据库的Pfile内容: *.aq_tm_processes=1
*.background_dump_dest='D:\oracle\admin\DEMO\bdump'
*.compatible='9.2.0.0.0'
*.control_files='D:\oracle\oradata\DEMO\CONTROL01.CTL',
'D:\oracle\oradata\DEMO\CONTROL02.CTL','D:\oracle\oradata\DEMO\CONTROL03.CTL'
*.core_dump_dest='D:\oracle\admin\DEMO\cdump'
*.db_block_size=8192
*.db_cache_size=19922944
*.db_domain=''
*.db_file_multiblock_read_count=32
*.db_name='DEMO'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DEMOXDB)'
*.fast_start_mttr_target=300
*.hash_area_size=1048576
*.hash_join_enabled=TRUE
*.instance_name='DEMO'
*.java_pool_size=20971520
*.job_queue_processes=10
*.large_pool_size=7340032
*.log_archive_dest_1='location=d:\oracle\oradata\demo\archive'
*.log_archive_dest_2='SERVICE=PSTANDBY'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_start=TRUE
*.open_cursors=300
*.optimizer_mode='FIRST_ROWS'
*.pga_aggregate_target=17825792
*.processes=150
*.query_rewrite_enabled='TRUE'
*.remote_login_passwordfile='EXCLUSIVE'
*.shared_pool_size=33554432
*.sort_area_size=1048576
*.star_transformation_enabled='TRUE'
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='D:\oracle\admin\DEMO\udump' 创建过程中的可能的错误:待续