说明:Oracle9i 数据库的 Data Guard 特性确保对数据进行完整的保护,是 Oracle 9i 的一个关键特性之一。Data Guard 可以创建物理的 Standby 数据库,也可以创建逻辑的Standby数据库,还可以混合使用,灵活性比较强.这个文档是Fenng练习时候记下来的东西,谈不上是什么心得。这不过是一份安装过程中的笔记而已,大约写于一年以前,如果对Standby机制的了解有更高的期望,或者想得到关于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数据库是否在归档模式下
如果不在归档模式下,调整数据库。 首先提交命令修改SPfile: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
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
备份
SQL> ALTER DATABASE FORCE LOGGING;
SQL> ALTER SYSTEM SET log_archive_dest_1='LOCATION=D:\oracle\oradata\DEMO\Archive' SCOPE=BOTH;
此操作直接生效
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>
SQL> SHUTDOWN IMMEDIATE;
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'C:\DataGuard\Pstandby\DEMO\DEMOSTANDBY.CTL';
要注意这个控制文件的名字不要和Primary的控制文件名字重复
SQL> CREATE PFILE='C:\DataGuard\Pstandby\DEMO\initpstandby' FROM SPFILE;
*.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')
整个操作的过程中,容易出现错误的地方几乎都集中在此处。必须认真仔细的对待这个文件。标记为黑色的地方是需要进行修改的。
WINNT> oradim -NEW -SID Pstandby -STARTMODE manual
可参考执行如下操作:
C:\>SET oracle_sid=pstandby C:\>SQLPLUS /nolog SQL> CONNECT / as sysdba SQL> CREATE SPFILE FROM PFILE='C:\DataGuard\Pstandby\DEMO\initPstandby.ora';
C:\>set oracle_sid=pstandby C:\>sqlplus /nolog SQL> connect / as sysdba SQL> STARTUP NOMOUNT; SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=Pstandby' SCOPE=BOTH; SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH;
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
(其实也可以直接到相关目录下查看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
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
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
SQL> SELECT sequence#, applied FROM v$archived_log ORDER BY sequence#; SEQUENCE# APP ---------- --- 38 YES 39 YES 40 YES 41 YES 42 YES 43 YES
OK.表明我们还是成功的。暂时告一段落。
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'