在单机上创建物理Oracle数据库9i 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:\oracle\arcOldest 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:\oracle\arc' SCOPE=BOTH;

        此操作直接生效

        2.创建物理的Standby数据库

        2.1 标记出Primary数据库的数据文件

SQL> SELECT NAME FROM v$datafile;         NAME         -----------------------------------------------------         D:\ORACLE\ORADATA\ORCL1\SYSTEM01.DBFD:\ORACLE\ORADATA\ORCL1\UNDOTBS01.DBFD:\ORACLE\ORADATA\ORCL1\DRSYS01.DBFD:\ORACLE\ORADATA\ORCL1\INDX01.DBFD:\ORACLE\ORADATA\ORCL1\TOOLS01.DBFD:\ORACLE\ORADATA\ORCL1\USERS01.DBFD:\ORACLE\ORADATA\ORCL1\XDB01.DBF

        2.2 关闭Instance 拷贝数据文件到既定目的地

SQL> SHUTDOWN IMMEDIATE;

        2.3 为Standby 数据库创建控制文件

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE         AS 'E:\oracle\oradata\orcl2\STANDBY.CTL';

        要注意这个控制文件的名字不要和Primary的控制文件名字重复

        2.4 为Standby数据库准备初始化参数文件名字

SQL> CREATE PFILE='E:\oracle\admin\orcl2\pfile\initorcl2.ora' FROM SPFILE;

        2.5 设定初始化Physical Standby Database参数

*.aq_tm_processes=1         *.background_dump_dest='E:\oracle\admin\orcl2\bdump'         *.compatible='9.2.0.0.0'         *.control_files='E:\oracle\oradata\orcl2\STANDBY.CTL'         *.core_dump_dest='E:\oracle\admin\orcl2\cdump'         *.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:\oracle\arc'         *.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:\oracle\admin\orcl2\udump'         lock_name_space=orcl2         standby_file_management=AUTO         remote_archive_enable=TRUE         standby_archive_dest='E:\oracle\standbyarc'         db_file_name_convert=('D:\oracle\oradata\orcl1', 'E:\oracle\oradata\orcl2')         log_file_name_convert=('D:\oracle\oradata\orcl1', 'E:\oracle\oradata\orcl2')         log_archive_dest_1=('LOCATION=E:\oracle\standbyarc')

        整个操作的过程中，容易出现错误的地方几乎都集中在此处。必须认真仔细的对待这个文件。标记为黑色的地方是需要进行修改的。

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:\oracle\admin\orcl2\pfile\initorcl2.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:\oracle\standbyarc' 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:\oracle\admin\orcl1\bdump'         *.compatible='9.2.0.0.0'         *.control_files='D:\oracle\oradata\orcl1\control01.ctl','D:\oracle\oradata\orcl1\control02.ctl','D:\oracle\oradata\orcl1\control03.ctl'         *.core_dump_dest='D:\oracle\admin\orcl1\cdump'         *.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:\oracle\arc'         *.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:\oracle\admin\orcl1\udump'         *.standby_archive_dest='E:\oracle\standbyarc'         *.remote_archive_enable=true