在单机上创建物理Oracle数据库9i standby
|
说明：Oracle9i 数据库的 Data Guard 特性确保对数据进行完整的保护，是 Oracle 9i 的一个关键特性之一。 Data Guard 可以创建物理的 Standby 数据库，也可以创建逻辑的Standby数据库,还可以混合使用，灵活性比较强．如果对Standby机制的了解有更高的期望，或者想得到关于Oracle数据库的data guard 和Standby的更多信息，请参考官方的文档。 现有的数据库实例 (Primary)名字：orcl1 预创建的Standby数据库实例名字：orcl2 数据库版本信息： 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 ModeAutomatic archival EnabledArchive destination D:oraclearcOldest online log sequence 28Next log sequence to archive 30Current log sequence 30如果不在归档模式下，调整数据库。首先提交命令修改SPfile： SQL>ALTER SYSTEM SET log_archive_start=TRUE SCOPE=SPFILE; 然后关闭数据库实例 SQL>SHUTDOWN immediate; 备份数据库 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:oraclearc" SCOPE=BOTH; 此操作直接生效 2.创建物理的Standby数据库 2.1 标记出Primary数据库的数据文件 SQL> SELECT NAME FROM v$datafile; NAME ----------------------------------------------------- D:ORACLEORADATAORCL1SYSTEM01.DBFD:ORACLEORADATAORCL1UNDOTBS01.DBFD:ORACLEORADATAORCL1DRSYS01.DBFD:ORACLEORADATAORCL1INDX01.DBFD:ORACLEORADATAORCL1TOOLS01.DBFD:ORACLEORADATAORCL1USERS01.DBFD:ORACLEORADATAORCL1XDB01.DBF 2.2 关闭Instance 拷贝数据文件到既定目的地 SQL> SHUTDOWN IMMEDIATE; 2.3 为Standby 数据库创建控制文件 SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS "E:oracleoradataorcl2STANDBY.CTL"; 要注意这个控制文件的名字不要和Primary的控制文件名字重复 2.4 为Standby数据库准备初始化参数文件名字 SQL> CREATE PFILE="E:oracleadminorcl2pfileinitorcl2.ora" FROM SPFILE; 2.5 设定初始化Physical Standby Database参数 *.aq_tm_processes=1 *.background_dump_dest="E:oracleadminorcl2dump" *.compatible="9.2.0.0.0" *.control_files="E:oracleoradataorcl2STANDBY.CTL" *.core_dump_dest="E:oracleadminorcl2cdump" *.db_block_size=8192 *.db_cache_size=19922944 *.db_domain="" *.db_file_multiblock_read_count=32 *.db_name="orcl1" *.dispatchers="(PROTOCOL=TCP) (SERVICE=DEMOXDB)" *.fast_start_mttr_target=300 *.hash_area_size=1048576 *.hash_join_enabled=TRUE *.instance_name="orcl2" *.java_pool_size=20971520 *.job_queue_processes=10 *.large_pool_size=7340032 *.log_archive_dest_1="location=E:oraclearc" *.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="E:oracleadminorcl2udump" lock_name_space=orcl2 standby_file_management=AUTO remote_archive_enable=TRUE standby_archive_dest="E:oraclestandbyarc" db_file_name_convert=("D:oracleoradataorcl1", "E:oracleoradataorcl2") log_file_name_convert=("D:oracleoradataorcl1", "E:oracleoradataorcl2") log_archive_dest_1=("LOCATION=E:oraclestandbyarc") 整个操作的过程中，容易出现错误的地方几乎都集中在此处。必须认真仔细的对待这个文件。标记为黑色的地方是需要进行修改的。 2.6 创建一个Windows服务 WINNT> oradim -NEW -SID orcl2 -STARTMODE manual 2.7 Create a Server Parameter File for the Standby Database 可参考执行如下操作： C:>SET oracle_sid=orcl2 C:>SQLPLUS /nolog SQL> CONNECT / as sysdba SQL> CREATE SPFILE FROM PFILE="E:oracleadminorcl2pfileinitorcl2.ora"; 2.8 启动物理Standby数据库 C:>set oracle_sid=orcl2 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=orcl2" SCOPE=BOTH; SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH; 2.11 启动远程归档 SQL> ALTER SYSTEM ARCHIVE LOG CURRENT; -在primary database上执行. 3.安装完的的验证 3.1 在Primary database上 alter system set standby_archive_dest="E:oraclestandbyarc" scope=both; 3.2 在database,查询V$ARCHIVED_LOG (其实也可以直接到相关目录下查看Log是否创建): SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#; SEQUENCE# FIRST_TIM NEXT_TIME---------- --------- --------- 30 09-JAN-07 17-JAN-07 31 17-JAN-07 17-JAN-073.3 在Primary数据库上，归档当前的Log SQL> ALTER SYSTEM ARCHIVE LOG CURRENT; 3.4 验证是否收到 SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#; SEQUENCE# FIRST_TIM NEXT_TIME---------- --------- --------- 30 09-JAN-07 17-JAN-07 31 17-JAN-07 17-JAN-07 32 17-JAN-07 17-JAN-07 3.5 验证是否新的归档Redo日志已经被应用 SQL> SELECT sequence#, applied FROM v$archived_log ORDER BY sequence#; SEQUENCE# APP---------- --- 30 YES 31 YES 32 YESOK.表明我们还是成功的. 附加内容: primary数据库的Pfile内容 *.aq_tm_processes=1 *.background_dump_dest="D:oracleadminorcl1dump" *.compatible="9.2.0.0.0" *.control_files="D:oracleoradataorcl1control01.ctl","D:oracleoradataorcl1control02.ctl","D:oracleoradataorcl1control03.ctl" *.core_dump_dest="D:oracleadminorcl1cdump" *.db_block_size=8192 *.db_cache_size=25165824 *.db_domain="" *.db_file_multiblock_read_count=16 *.db_name="orcl1" *.dispatchers="(PROTOCOL=TCP)" *.fast_start_mttr_target=300 *.hash_join_enabled=TRUE *.instance_name="orcl1" *.java_pool_size=33554432 *.job_queue_processes=10 *.large_pool_size=8388608 *.log_archive_dest_1="LOCATION=D:oraclearc" *.log_archive_start=TRUE *.open_cursors=300 *.pga_aggregate_target=25165824 *.processes=150 *.query_rewrite_enabled="FALSE" *.remote_login_passwordfile="EXCLUSIVE" *.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="D:oracleadminorcl1udump" *.standby_archive_dest="E:oraclestandbyarc" *.remote_archive_enable=true |
