搭建 Oracle10g DataGuard

搭建 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

[@more@]

物理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/