windows 2003 32bit oracle dataguard 物理standby

Oracle10g dataguard for win2003
Windows版本
Microsoft(R) Windows(R) Server 2003, Enterprise Edition
5.2.3790 Service Pack 1 内部版本 3790
primary ip:192.168.1.11
primary sid:test
standby ip:192.168.1.12
standby sid:test1
db_name:test

一、在primary和standby上安装oracle10r2软件， 2台机器的安装目录相同。
二、在primary上用DBCA建立database,建立database的时候要设置成归档模式。
三、为standby创建控制文件
从cmd登录到database,打开database,创建standby控制文件。
C:\>set oracle_sid=test
C:\>sqlplus /nolog
SQL>conn / as sysdba
SQL>startup;
SQL>alter database create standby controlfile as ‘c:\oracle\ TEST1_CONTOL.CTL’;

四、修改primary的init文件,由于默认spfile启动，先创建pfile:
Sql>create pfile from pfile
然后修改C:\oracle\product\10.2.0\db_1\database\INITtest.ora，加入如下行：
*.db_unique_name=test
*.service_names=test
*.log_archive_config='DG_CONFIG=(test,test1)'
*.log_archive_dest_1='LOCATION=C:\oracle\product\10.2.0\flash_recovery_area

VALID_FOR=(ALL_LOGFILES,ALL_ROLES)  db_unique_name=test'
*.log_archive_dest_2='SERVICE=test1 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=test1'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.FAL_CLIENT='test1'
*.FAL_SERVER='test'
*.standby_file_management='auto'
*.standby_archive_dest='C:\oracle\product\10.2.0\flash_recovery_area'
让数据库用INITtest.ora参数启动。
五、把primary上的database移植到standby中
把primary上的test的库关闭。
把primary上的test的相关datafile拷贝到standby机器上。文件路径要一致。
1. 数据文件: C:\oracle\product\10.2.0\ oradata
2. 参数文件：C:\oracle\product\10.2.0\db_1\database\INITtest.ora
3. 跟踪文件：C:\oracle\product\10.2.0\admin
4. 在pramiry中生成的standby的控制文件：c:\oracle\TEST1_CONTOL.CTL
5. 闪回文件：C:\oracle\product\10.2.0\ flash_recovery_area
在cmd中建立standby的database服务
C:\〉oradim -NEW -SID test1 –INTPWD kyskys -STARTMODE manual
创建standby的密码文件：
Orapwd file=PWDtest1 password=kyskys(和pramiry的密码一样)
六、修改standby的init文件，注意如下参数：
*.control_files='C:\oracle\TEST1_CONTOL.CTL'
*.db_unique_name='test1'
*.FAL_CLIENT='test1'
*.FAL_SERVER='test'
*.service_names='test1'
*.standby_file_management='auto'
*.standby_archive_dest='C:\oracle\product\10.2.0\flash_recovery_area'
*.log_archive_config='DG_CONFIG=(test,test1)'
*.log_archive_dest_1='LOCATION=C:\oracle\product\10.2.0\flash_recovery_area VALID_FOR=(ALL_LOGFILES,ALL_ROLES)  db_unique_name=test1'
*.log_archive_dest_2='SERVICE=test VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=test'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'

七、修改pramiry监听和TNS：
C:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\ listener.ora
# listener.ora Network Configuration File:
C:\oracle\product\10.2.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.11)(PORT = 1521))
)

(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PL***tProc)
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = test)
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
(SID_NAME = test)
)
)
C:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\
tnsnames.ora
# tnsnames.ora Network Configuration File:
C:\oracle\product\10.2.0\db_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
TEST1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.12)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = test1)
)
)

TEST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.11)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = test)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PL***tProc)
(PRESENTATION = RO)
)
)
七、修改standby监听和TNS：
# listener.ora Network Configuration File:
C:\oracle\product\10.2.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.12)(PORT = 1521))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = test)
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
(SID_NAME = test)
)
)
# tnsnames.ora Network Configuration File:
C:\oracle\product\10.2.0\db_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
TEST1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.12)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = test1)
)
)
TEST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.11)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = test)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PL***tProc)
(PRESENTATION = RO)
)
)
注意修改sqlnet.ora:
# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES = (NTS)
SQLNET.ALLOWED_LOGON_VERSION = 10

八、测试primary和standby是否能互访:
Tnsping test
Tnsping test1

九、启动standby:
sqlplus "/ as sysdba"
SQL>startup mount
SQL>alter database recover managed standby database disconnect from session;十、在pramiry查看归档状态
SQL> select dest_name,status,error from v$archive_dest;

 

 

////////////
alter database create standby  controlfile as 'c:\*.ctl' ;
recover database until console using backup controlfile;