ORACLE DataGuard Win 物理备库搭建
试验环境：
ORACLE Virtual Box
Windows 2008 enterprise r2
ORACLE 10gr2
主库：
ORACLE_SID: AX03
DB_UNIQUE_NAME: AX03_pd
IP: 12.11.0.111
HOSTNAME: SZHSVR053
备库：
ORACLE_SID: AX03
DB_UNIQUE_NAME: AX03_st
IP: 12.11.0.112
HOSTNAME: SZHSVR054
试验步骤：
构建基础环境：
2. 新建虚拟网络"HOSTONLY" (Hyper-v不用设置子网，就选择内部模式，并将IP设置在同一网段)
3. 配置网络并设置HOSTS文件；
hosts文件修改后内容：
127.0.0.1 localhost.localdomain localhost
12.11.0.111 SZHSVR053
12.11.0.112 SZHSVR054
注意：配置文件中的ORACLE_HOME变量设置正确。
5. 使用导出，导入的方法克隆一台WINDOWS 2008虚拟系统；
1. 使用netca, 创建监听器；
2. 使用DBCA, 创建数据库；
注意：
a. 创建过程中碰到的global name和SID均使用oracle_sid;
b. 创建是开启ARCHIVE; (检查：archive log list)
3. 开启强制日志模式：
set ORACLE_SID=AX03
sqlplus / as sysdba
alter database force logging;
4. 创建备库在线日志：（大小应该与主库的日志一样）
alter database add standby logfile group 4 ('C:\oracle\product\10.2.0\oradata\AX03\redo04.log') size 50M;
alter database add standby logfile group 5 ('C:\oracle\product\10.2.0\oradata\AX03\redo05.log') size 50M;
alter database add standby logfile group 6 ('C:\oracle\product\10.2.0\oradata\AX03\redo06.log') size 50M;
查看备库在线日志是否创建成功：
select group#, thread#, archived, status from v$standby_log;
5. 修改参数文件；
sqlplus / as sysdba
create pfile from spfile;
cd c:\oracle\product\10.2.0\db_1\database
修改initdgtest.ora
添加如下内容：
*.DB_UNIQUE_NAME='AX03_pd'
*.log_archive_dest_1='LOCATION=C:\oracle\product\10.2.0\flash_recovery_area\AX03\archivelog\ VALID_FOR=(ALL_LOGFILES,ALL_ROLES)'
*.log_archive_dest_2='SERVICE=AX03_st lgwr async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)'
*.log_archive_dest_state_1=ENABLE
*.log_archive_dest_state_2=ENABLE
*.standby_file_management='AUTO'
*.standby_archive_dest='C:\oracle\product\10.2.0\flash_recovery_area\AX03\archivelog'
*.FAL_SERVER='AX03_st'
*.FAL_CLIENT='AX03_pd'
*.LOG_ARCHIVE_MAX_PROCESSES=3
*.db_file_name_convert=('C:\oracle\product\10.2.0\oradata\AX03','C:\oracle\product\10.2.0\oradata\AX03')
*.log_file_name_convert=('C:\oracle\product\10.2.0\oradata\AX03','C:\oracle\product\10.2.0\oradata\AX03')
6. 使用新建的pfile启动数据库，并重建数据库
删除$ORACLE_HOME/dbs/spfiledgtest.ora
set ORACLE_SID=AX03
sqlplus / as sysdba
startup
create spfile from pfile
7. 创建备库控制文件：
alter database create standby controlfile as 'C:\standby.ctl';
8. 修改listener.ora, tnsnames.ora
listener.ora修改后的结果：
# listener.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DB_NAME = AX03)
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
(SID_NAME = AX03)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = SZHSVR053)(PORT = 1523))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
tnsnames.ora修改后的结果：
# tnsnames.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
AX03_PD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = SZHSVR053)(PORT = 1523))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = AX03)
)
)
AX03_ST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = SZHSVR054)(PORT = 1523))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = AX03)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
LISTENER_AX03 = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
9. 关闭主库
su - oracle
sqlplus / as sysdba
shutdown immediate;
备库服务器操作：
1. 新建文件夹：
su - oracle
cd c:\oracle\product\10.2.0\oradata
cd oradata
mkdir AX03
cd ../admin
mkdir AX03
mkdir adump
mkdir bdump
mkdir cdump
mkdir dpdump
mkdir pfile
mkdir udump
cd c:\oracle\product\10.2.0\flash_recovery_area
mkdir AX03
cd AX03
mkdir archivelog
mkdir onlinelog
2. 从主库服务器复制文件至备库服务器：
a. 控制文件：
c:\standby.ctl 备库服务器:c:\oracle\product\10.2.0\control01.ctl;
在备库服务器上：
copy control01.ctl control02.ctl;
copy control02.ctl control03.ctl;
b. 数据文件：
copy c:\oracle\product\10.2.0\oradata\AX03\*.dbf 备库c:\oracle\product\10.2.0\oradata\AX03\*.dbf;
c. 在线日志文件：
copy c:\oracle\product\10.2.0\oradata\AX03\*.log 备库:c:\oracle\product\10.2.0\oradata\AX03\*.log;
d. 密码文件：
cd c:\oracle\product\10.2.0\db_1\database
复制文件 PWDAX03 到备库 c:\oracle\product\10.2.0\db_1\database
e. 参数文件：
cd c:\oracle\product\10.2.0\db_1\database
复制文件 INITax03.ORA 到备库 c:\oracle\product\10.2.0\db_1\database
3. 修改参数文件：
4. 从主库复制并修改listener.ora和tnsnames.ora
listener.ora修改后：
# listener.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DB_NAME = AX03)
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
(SID_NAME = AX03)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = SZHSVR054)(PORT = 1523))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
tnsnames.ora修改后内容：
# tnsnames.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
AX03_PD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = SZHSVR053)(PORT = 1523))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = AX03)
)
)
AX03_ST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = SZHSVR054)(PORT = 1523))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = AX03)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
LISTENER_AX03 = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
手工创建windows服务：
Oradim –NEW –SID AX03 –STARDMODE manual
Dataguard启动：
1. 启动备库
su - oracle
sqlplus / as sysdba
startup nomount;
alter database mount standby database;
2. 启动备库日志实施进程：
alter database recover managed standby database disconnect from session;
3. 启动备库监听器：
lsnrctl start
4. 启动主库：
删除spfile
sqlplus / as sysdba
startup
5. 启动主库监听器：
lsnrctl start
检查：
1. 检查日志是否成功更新：
a. 查看备库日志状态：
select sequence#, first_time, next_time from v$archived_log;
b. 在主库上执行：
alter system switch logfile;
c. 检查备库上检查日志状态，是否与之前有变化：
select sequence#, first_time, next_time from v$archived_log;
d. 检查备库上日志是否实施：
select sequence#, applied from v$archived_log;
2. 数据检查：
a. 新建表空间：
在主库服务器：
sqlplus / as sysdba
create tablespace ts_test datafile ‘c:\oracle\product\10.2.0\oradata\AX03\test.dbf’ size 100M autoextend on next 10M;
alter system archive log current;
sqlplus / as sysdba
create tablespace ts_test datafile ‘c:\oracle\product\10.2.0\oradata\AX03\test.dbf’ size 100M autoextend on next 10M;
alter system archive log current;
查看结果：
select * from v$dbfile;
在备库服务器检查是否同样新建了表空间：
select * from v$dbfile;b. 新建用户，表：
sqlplus / as sysdba;
create user test identified by test default tablespace ts_test;
grant dba to u_test;
select count(*) from test.t_test;
查看结果：
select count(*) from test.t_test;
在备库服务器检查：
切换成只读模式：
recover managed standby database cancel;
alter database open read only;
alter database open read only;
检查结果：
select count(*) from test.t_test;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Comments
Post a Comment