在单机上创建物理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:oracleadminorcl2bdump’

*.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:oracleadminorcl1bdump’

*.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

—-想了解更多的数据库相关异常处理怎么解决关注<计算机技术网(www.ctvol.com)!!>