搭建 Oracle10g DataGuard
primary数据库
os:winxp
ip:10.0.10.112
db:ora10.2.0.4
dbname:lixiang
stanby数据库
os:winxp
ip:10.0.10.114
db:ora10.2.0.4
dbname:lixiangdg
物理standby 创建步骤:
一、primary数据库相关操作(10.0.10.112):
1、确认主库处于归档模式
SQL> archive log list
数据库日志模式 存档模式
自动存档 启用
存档终点 G:oraclearchivelog
最早的联机日志序列 15
下一个存档日志序列 17
当前日志序列 17
2、设置primary数据库为FORCELOGGING模式
SQL> alter database force logging;
数据库已更改。
3、创建standby数据库控制文件
SQL>alter database create standby controlfile as 'c:/lixiangdg.ctl';
数据库已更改。
4、创建pfile修改DG用参数
SQL>create pfile='c:/pfile.ora' from spfile;
文件已创建
5、修改c:/pfile.ora 添加DG用参数
#--------配置primary
DB_UNIQUE_NAME=lixiang
LOG_ARCHIVE_CONFIG='DG_CONFIG=(lixiang,lixiangdg)'
LOG_ARCHIVE_DEST_1='LOCATION=G:oraclearchivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=lixiang'
LOG_ARCHIVE_DEST_2='SERVICE=lixiangdg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=lixiangdg'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
#--------配置standby 角色的参数用于角色转换
FAL_SERVER=lixiangdg
FAL_CLIENT=lixiang
DB_FILE_NAME_CONVERT='oradatalixiangdg','oradatalixiang'
LOG_FILE_NAME_CONVERT='oradatalixiangdg','oradatalixiang'
STANDBY_FILE_MANAGEMENT=AUTO
6、通过pfile 重建spfile
SQL> shutdown immediate
...
SQL> create spfile from pfile='c:/pfile.ora';
文件已创建。
7、复制数据文件、日志文件、pfile、PWDlixiang.ora、standby controlfile(lixiangdg.ctl) 至 stanby数据库
8、配置tnsname.ora
LIXIANG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.10.112)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = lixiang)
)
)
LIXIANGDG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.10.114)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = lixiangdg)
)
)
9、tnsping 测试tnsname
C:Documents and SettingsAdministrator>tnsping lixiang
...
已使用 TNSNAMES 适配器来解析别名
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.10.112)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =
lixiang)))
OK (30 毫秒)
C:Documents and SettingsAdministrator>tnsping lixiangdg
...
已使用 TNSNAMES 适配器来解析别名
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.10.114)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =
lixiangdg)))
OK (30 毫秒)
二、standby数据库相关操作(10.0.10.114):
1、创建新的OracleService
C:Documents and SettingsAdministrator>oradim -new -sid lixiangdg
实例已创建
2、创建目录
E:G:oracleproduct10.2.0admin>mkdir lixiangdg
E:G:oracleproduct10.2.0adminlixiangdg>mkdir adump
E:G:oracleproduct10.2.0adminlixiangdg>mkdir bdump
E:G:oracleproduct10.2.0adminlixiangdg>mkdir cdump
E:G:oracleproduct10.2.0adminlixiangdg>mkdir udump
E:G:oracleproduct10.2.0adminlixiangdg>mkdir pfile
3、将数据文件、日志文件、PWDlixiang.ora,基于lixiangdg.ctl复制三份lixiangdg01.ctl,lixiangdg02.ctl,lixiangdg03.ctl放到相应位置,数据文件等位置保持与主库一致。
4、修改从主库传送的pfile.ora
#--------配置standy
db_unique_name=lixiangdg
LOG_ARCHIVE_CONFIG='DG_CONFIG=(lixiang,lixiangdg)'
DB_FILE_NAME_CONVERT='oradatalixiang','oradatalixiangdg'
LOG_FILE_NAME_CONVERT='oradatalixiang','oradatalixiangdg'
LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc
LOG_ARCHIVE_DEST_1='LOCATION=E:ora10goradatalixiangdg
VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=lixiangdg'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
#---下列参数用于角色切换
LOG_ARCHIVE_DEST_2='SERVICE=lixiang LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=lixiang'
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
FAL_SERVER=lixiang
FAL_CLIENT=lixiangdg
STANDBY_FILE_MANAGEMENT=AUTO
同时修改*_dest的路径
5、通过pfile 重建spfile
SQL> create spfile from pfile='c:/pfile.ora';
文件已创建。
6、配置tnsname.ora
LIXIANG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.10.112)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = lixiang)
)
)
LIXIANGDG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.10.114)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = lixiangdg)
)
)
7、tnsping 测试tnsname
C:Documents and SettingsAdministrator>tnsping lixiang
...
已使用 TNSNAMES 适配器来解析别名
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.10.112)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =
lixiang)))
OK (30 毫秒)
C:Documents and SettingsAdministrator>tnsping lixiangdg
...
已使用 TNSNAMES 适配器来解析别名
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.10.114)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =
lixiangdg)))
OK (30 毫秒)
8、启动standby数据库到mount
SQL> startup mount;
ORACLE 例程已经启动。
Total System Global Area 167772160 bytesFixed Size 1289484 bytes
Variable Size 62915316 bytes
Database Buffers 96468992 bytes
Redo Buffers 7098368 bytes
数据库装载完毕。
9、启动redo 应用
SQL> alter database recover managed standby database disconnect from session;
数据库已更改。
10、查看同步情况:
首先连接到primary 数据库
SQL> show parameter instance_name;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string lixiang
SQL> alter system switch logfile;
系统已更改。
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
51
SQL> select d.NAME,d.DATABASE_ROLE,d.PROTECTION_MODE,d.PROTECTION_LEVEL from v$database d;
NAME DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
--------- ---------------- -------------------- --------------------
LIXIANG PRIMARY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
连接到standby 数据库
SQL> show parameter instance_name;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string lixiangdg
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
51
至此物理standby DG搭建完成
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7417660/viewspace-1034179/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7417660/viewspace-1034179/