oracle 10g dg搭建,搭建 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搭建完成