ORACLE DATAGUARD配置的问题!! RT,谁有这方面的详细教程,我在网上找了很多资料,总是在最后启动备用数据库时提示出错,已经搞了好几天了,急!! 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 我有自己机器上试验的9i,10g的可以看三思的一步一步dataguard; 同意楼上的,三思的教程不错,scribd.com 上有下载。 单机环境data guard配置1、主机ora9,目的ora82、ora9需要归档,并设置归档目录SQL> alter system set LOG_ARCHIVE_START=TRUE scope=spfileSQL> alter database force logging;SQL> alter system set log_archive_dest_1='LOCATION=c:\oracle\ora92\rdbms';3、然后关闭数据库,拷贝数据文件到standby指定目录 SQL> select name from v$datafile;NAME------------------------------------c:\ORACLE\ORADATA\ORA9\SYSTEM01.DBFc:\ORACLE\ORADATA\ORA9\UNDOTBS01.DBFc:\ORACLE\ORADATA\ORA9\INDX01.DBFc:\ORACLE\ORADATA\ORA9\TOOLS01.DBFc:\ORACLE\ORADATA\ORA9\USERS01.DBFc:\ORACLE\ORADATA\ORA9\RMAN01.DBFc:\ORACLE\ORADATA\ORA9\TS11.DBFc:\ORACLE\ORADATA\ORA9\TS22.DBFc:\ORACLE\ORADATA\ORA9\TS33.ORAc:\ORACLE\ORADATA\ORA9\TS44.ORAc:\ORACLE\ORADATA\ORA9\TS55.ORAc:\ORACLE\ORADATA\ORA9\TS66.ORAc:\ORACLE\ORADATA\ORA9\TS77.ORAc:\ORACLE\ORADATA\ORA9\TS88.ORA已选择14行。3、创建standby控制文件SQL> alter database create standby controlfile as 'c:\oracle\oradata\ora8\standby.ctl';数据库已更改。4、为standby数据库准备参数文件*.background_dump_dest='c:\oracle\admin\ora8\bdump'*.compatible='9.2.0.0.0'*.control_files='c:\oracle\oradata\ora8\standby.ctl'*.core_dump_dest='c:\oracle\admin\ora8\cdump'*.db_block_size=8192*.db_cache_size=25165824*.db_domain=''*.db_file_multiblock_read_count=16*.db_name='ora9'*.fast_start_mttr_target=300*.hash_join_enabled=TRUE*.instance_name='ora8'*.java_pool_size=0*.large_pool_size=8388608*.log_archive_start=TRUE*.open_cursors=300*.pga_aggregate_target=25165824*.processes=150*.query_rewrite_enabled='FALSE'*.remote_login_passwordfile='EXCLUSIVE'*.sga_max_size=104857600*.shared_pool_size=50331648*.sort_area_size=524288*.star_transformation_enabled='FALSE'*.timed_statistics=TRUE*.undo_management='AUTO'*.undo_retention=10800*.undo_tablespace='UNDOTBS1'*.user_dump_dest='c:\oracle\admin\ora8\udump'lock_name_space=ora8standby_file_management=AUTOremote_archive_enable=TRUEstandby_archive_dest='c:\oracle\ora92\rdbms\ora8'db_file_name_convert=('c:\oracle\oradata\ora9', 'c:\oracle\oradata\ora8')log_file_name_convert=('c:\oracle\oradata\ora9', 'c:\oracle\oradata\ora8')log_archive_dest_1=('LOCATION=c:\oracle\ora92\rdbms\ora8')*.FAL_CLIENT='ora8'*.FAL_SERVER='ora9'*.log_archive_dest_2='SERVICE=ora9'5、创建一个windows服务C:\Documents and Settings\Administrator>oradim -NEW -SID ora8 -STARTMODE manual6、创建spfileC:\Documents and Settings\Administrator>set ORACLE_SID=ora8C:\Documents and Settings\Administrator>sqlplus /nologSQL*Plus: Release 9.2.0.6.0 - Production on 星期五 6月 6 10:06:18 2008Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.SQL> conn /as sysdba已连接到空闲例程。SQL> create spfile from pfile;文件已创建。7、修改主库归档SQL> alter system set log_archive_dest_2='SERVICE=ora8' scope=both;8、配置tnsname,listener.oraORA8 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = sgdev161)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = ora8) ) )----listener.ora# LISTENER.ORA Network Configuration File: c:\oracle\ora92\network\admin\listener.ora# Generated by Oracle configuration tools.LISTENER =(DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = dzg)(PORT = 1521)) ) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0)) ) ))SID_LIST_LISTENER =(SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = c:\oracle\ora92) (PROGRAM = extproc) ) (SID_DESC = (GLOBAL_DBNAME = ora9) (ORACLE_HOME = c:\oracle\ora92) (SID_NAME = ora9) ) (SID_DESC = (ORACLE_HOME = c:\oracle\ora92) (SID_NAME = test) ) (SID_DESC = (ORACLE_HOME = c:\oracle\ora92) (SID_NAME = ora8) ))9、启动standby数据库C:\Documents and Settings\Administrator>orapwd file=c:\oracle\ora92\database\pwdora8.ora password=password entries=5;C:\Documents and Settings\Administrator>set ORACLE_SID=ora8C:\Documents and Settings\Administrator>sqlplus /nologSQL*Plus: Release 9.2.0.6.0 - Production on 星期五 6月 6 10:09:12 2008Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.SQL> conn /as sysdba已连接。SQL> startup nomount;ORACLE 例程已经启动。Total System Global Area 105979752 bytesFixed Size 454504 bytesVariable Size 79691776 bytesDatabase Buffers 25165824 bytesRedo Buffers 667648 bytesSQL> alter database mount standby database;数据库已更改。SQL> alter database recover managed standby database disconnect from session;数据库已更改。10、在主库归档日志SQL> alter system archive log current;系统已更改。11、从库取消恢复,打开只读,查看是否应用过来SQL> recover managed standby database cancel;完成介质恢复。SQL> alter database open read only;数据库已更改。查看主从是否同步,可以通过一下SQLSQL> select sequence#,first_time,next_time2 from v$archived_log order by sequence#;SEQUENCE# FIRST_TIME NEXT_TIME---------- ---------- ---------- 5 30-5? -08 06-6? -08 6 06-6? -08 06-6? -08 7 06-6? -08 06-6? -08 8 06-6? -08 06-6? -08 9 06-6? -08 06-6? -08 10 06-6? -08 06-6? -08 11 06-6? -08 06-6? -08 12 06-6? -08 06-6? -08 13 06-6? -08 06-6? -08 14 06-6? -08 06-6? -08 15 06-6? -08 06-6? -08已选择11行。12、重新让数据库恢复到standby模式SQL> shutdown immediate;数据库已经关闭。已经卸载数据库。ORACLE 例程已经关闭。SQL> startup nomount;ORACLE 例程已经启动。Total System Global Area 105979752 bytesFixed Size 454504 bytesVariable Size 79691776 bytesDatabase Buffers 25165824 bytesRedo Buffers 667648 bytesSQL> alter database mount standby database;数据库已更改。或者执行SQL> alter database recover managed standby database disconnect from session;数据库已更改。 我想问一下:在windowXP下配置的oracle9i的dataguard,前面都没有问题,就是到了最后检验是否成功时就发现无论主库生成了多少归档日记,备份库还是啥都没有,请问一下是啥问题啊? ﹏⊙b汗,原来是错了那么简单的地方: SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=STANDBY' SCOPE=BOTH;中的standby少打了一个b,哦,就当我来闹场的吧,飘过 求语句 怎么进入建好的用户啊 问题:数据库连接失败: ORA-12170: TNS:Connect timeout occurred ? Oracle 行变列的问题,急急!! 急啊,请大家帮忙 请教一语句 oracle如何读未提交的数据 SQL Server 2000数据到ORACLE问题 连接报错ora-01034 oracle not available 100分求教oracle9i安装在win2003上出现12541-TNS没有监听器错误。 oracle oracle的存储过程 如何把字段值赋到变量中
单机环境data guard配置
1、主机ora9,目的ora82、ora9需要归档,并设置归档目录
SQL> alter system set LOG_ARCHIVE_START=TRUE scope=spfile
SQL> alter database force logging;
SQL> alter system set log_archive_dest_1='LOCATION=c:\oracle\ora92\rdbms';
3、然后关闭数据库,拷贝数据文件到standby指定目录
SQL> select name from v$datafile;NAME
------------------------------------
c:\ORACLE\ORADATA\ORA9\SYSTEM01.DBF
c:\ORACLE\ORADATA\ORA9\UNDOTBS01.DBF
c:\ORACLE\ORADATA\ORA9\INDX01.DBF
c:\ORACLE\ORADATA\ORA9\TOOLS01.DBF
c:\ORACLE\ORADATA\ORA9\USERS01.DBF
c:\ORACLE\ORADATA\ORA9\RMAN01.DBF
c:\ORACLE\ORADATA\ORA9\TS11.DBF
c:\ORACLE\ORADATA\ORA9\TS22.DBF
c:\ORACLE\ORADATA\ORA9\TS33.ORA
c:\ORACLE\ORADATA\ORA9\TS44.ORA
c:\ORACLE\ORADATA\ORA9\TS55.ORA
c:\ORACLE\ORADATA\ORA9\TS66.ORA
c:\ORACLE\ORADATA\ORA9\TS77.ORA
c:\ORACLE\ORADATA\ORA9\TS88.ORA已选择14行。3、创建standby控制文件
SQL> alter database create standby controlfile as 'c:\oracle\oradata\ora8\standby.ctl';数据库已更改。4、为standby数据库准备参数文件
*.background_dump_dest='c:\oracle\admin\ora8\bdump'
*.compatible='9.2.0.0.0'
*.control_files='c:\oracle\oradata\ora8\standby.ctl'
*.core_dump_dest='c:\oracle\admin\ora8\cdump'
*.db_block_size=8192
*.db_cache_size=25165824
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='ora9'
*.fast_start_mttr_target=300
*.hash_join_enabled=TRUE
*.instance_name='ora8'
*.java_pool_size=0
*.large_pool_size=8388608
*.log_archive_start=TRUE
*.open_cursors=300
*.pga_aggregate_target=25165824
*.processes=150
*.query_rewrite_enabled='FALSE'
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_max_size=104857600
*.shared_pool_size=50331648
*.sort_area_size=524288
*.star_transformation_enabled='FALSE'
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='c:\oracle\admin\ora8\udump'
lock_name_space=ora8
standby_file_management=AUTO
remote_archive_enable=TRUE
standby_archive_dest='c:\oracle\ora92\rdbms\ora8'
db_file_name_convert=('c:\oracle\oradata\ora9', 'c:\oracle\oradata\ora8')
log_file_name_convert=('c:\oracle\oradata\ora9', 'c:\oracle\oradata\ora8')
log_archive_dest_1=('LOCATION=c:\oracle\ora92\rdbms\ora8')
*.FAL_CLIENT='ora8'
*.FAL_SERVER='ora9'
*.log_archive_dest_2='SERVICE=ora9'5、创建一个windows服务
C:\Documents and Settings\Administrator>oradim -NEW -SID ora8 -STARTMODE manual
6、创建spfile
C:\Documents and Settings\Administrator>set ORACLE_SID=ora8C:\Documents and Settings\Administrator>sqlplus /nologSQL*Plus: Release 9.2.0.6.0 - Production on 星期五 6月 6 10:06:18 2008Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.SQL> conn /as sysdba
已连接到空闲例程。
SQL> create spfile from pfile;文件已创建。7、修改主库归档
SQL> alter system set log_archive_dest_2='SERVICE=ora8' scope=both;8、配置tnsname,listener.ora
ORA8 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = sgdev161)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ora8)
)
)----listener.ora
# LISTENER.ORA Network Configuration File: c:\oracle\ora92\network\admin\listener.ora
# Generated by Oracle configuration tools.LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dzg)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
)SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = c:\oracle\ora92)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = ora9)
(ORACLE_HOME = c:\oracle\ora92)
(SID_NAME = ora9)
)
(SID_DESC =
(ORACLE_HOME = c:\oracle\ora92)
(SID_NAME = test)
)
(SID_DESC =
(ORACLE_HOME = c:\oracle\ora92)
(SID_NAME = ora8)
)
)
9、启动standby数据库
C:\Documents and Settings\Administrator>orapwd file=c:\oracle\ora92\database\pwdora8.ora password=password entries=5;C:\Documents and Settings\Administrator>set ORACLE_SID=ora8C:\Documents and Settings\Administrator>sqlplus /nologSQL*Plus: Release 9.2.0.6.0 - Production on 星期五 6月 6 10:09:12 2008Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.SQL> conn /as sysdba
已连接。
SQL> startup nomount;
ORACLE 例程已经启动。Total System Global Area 105979752 bytes
Fixed Size 454504 bytes
Variable Size 79691776 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
SQL> alter database mount standby database;数据库已更改。SQL> alter database recover managed standby database disconnect from session;数据库已更改。
10、在主库归档日志
SQL> alter system archive log current;系统已更改。11、从库取消恢复,打开只读,查看是否应用过来
SQL> recover managed standby database cancel;
完成介质恢复。SQL> alter database open read only;数据库已更改。查看主从是否同步,可以通过一下SQL
SQL> select sequence#,first_time,next_time
2 from v$archived_log order by sequence#;SEQUENCE# FIRST_TIME NEXT_TIME
---------- ---------- ----------
5 30-5? -08 06-6? -08
6 06-6? -08 06-6? -08
7 06-6? -08 06-6? -08
8 06-6? -08 06-6? -08
9 06-6? -08 06-6? -08
10 06-6? -08 06-6? -08
11 06-6? -08 06-6? -08
12 06-6? -08 06-6? -08
13 06-6? -08 06-6? -08
14 06-6? -08 06-6? -08
15 06-6? -08 06-6? -08已选择11行。
12、重新让数据库恢复到standby模式
SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup nomount;
ORACLE 例程已经启动。Total System Global Area 105979752 bytes
Fixed Size 454504 bytes
Variable Size 79691776 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
SQL> alter database mount standby database;数据库已更改。
或者执行
SQL> alter database recover managed standby database disconnect from session;数据库已更改。