一步一脚印

博客导航

博客日历

«	十月 2008					»
一	二	三	四	五	六	日
		1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30	31

10GR2下创建物理standby STEP BY STEP

发表人:space6212 | 发表时间: 2007年六月24日, 16:45

这篇文章主要介绍如何在10g下创建物理standby。
服务器信息：
主库：
IP:10.2.98.10
SID:Primary
备库：
IP:10.2.98.11
SID:Primary

OS平台都是基于linux，数据库版本是10.2.0.3

具体步骤如下：

1、在主数据库执行force logging
SQL> alter database force logging;

Database altered.

2、在主库创建密码文件（如果已经存在则略过此步骤）
3、在主库创建standby redo log
在主库上建立standby log，大小与主库联机日志大小一样，组数至少大1（这个是可选，是为了角色切换方便）
SQL> alter database add standby logfile group 4 '/u01/oracle/oradata/primary/standbyredo04.log' size 50m;

Database altered.

SQL> alter database add standby logfile group 5 '/u01/oracle/oradata/primary/standbyredo5.log' size 50m;

Database altered.

SQL> alter database add standby logfile group 6 '/u01/oracle/oradata/primary/standbyredo6.log' size 50m;

Database altered.

SQL> alter database add standby logfile group 7 '/u01/oracle/oradata/primary/standbyredo7.log' size 50m;

Database altered.

4、设置主库初始化参数
SQL> create pfile from spfile;

File created.
然后编辑生成的pfile，主要修改的地方如下：
DB_UNIQUE_NAME=primary
LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary,standby)'
LOG_ARCHIVE_DEST_1='LOCATION=/u01/archivelog/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary'
LOG_ARCHIVE_DEST_2='SERVICE=standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=4
COMPATIBLE = 10.2.0.3
#以下参数是为了角色切换设置
FAL_CLIENT = primary
FAL_SERVER = standby
STANDBY_FILE_MANAGEMENT =AUTO
log_file_name_convert='/u01/oracle/oradata/primary/','/u01/oracle/oradata/primary/'

5、设置归档模式
SQL> startup mount pfile=?/dbs/initprimary.ora

Total System Global Area 167772160 bytes
Fixed Size 1260696 bytes
Variable Size 125829992 bytes
Database Buffers 37748736 bytes
Redo Buffers 2932736 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> create spfile from pfile;

File created.

6、在主库用RMAN做一个全备
[oracle@primary ~]$ rman target /

Recovery Manager: Release 10.2.0.3.0 - Production on Sun Jun 17 02:50:40 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: PRIMARY (DBID=1462491904)

RMAN> backup database format='/u01/backup/%U_%s.bak';

Starting backup at 17-JUN-07
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=118 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/oracle/oradata/primary/system01.dbf
input datafile fno=00002 name=/u01/oracle/oradata/primary/undotbs01.dbf
input datafile fno=00003 name=/u01/oracle/oradata/primary/sysaux01.dbf
input datafile fno=00004 name=/u01/oracle/oradata/primary/users01.dbf
channel ORA_DISK_1: starting piece 1 at 17-JUN-07
channel ORA_DISK_1: finished piece 1 at 17-JUN-07
piece handle=/u01/backup/01ikfgkh_1_1_1.bak tag=TAG20070617T025056 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 17-JUN-07
channel ORA_DISK_1: finished piece 1 at 17-JUN-07
piece handle=/u01/backup/02ikfglk_1_1_2.bak tag=TAG20070617T025056 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
Finished backup at 17-JUN-07

RMAN> sql "Alter System Archive Log Current";

sql statement: Alter System Archive Log Current

RMAN> Backup filesperset 10 ArchiveLog all format='/u01/backup/%U_%s.bak';

Starting backup at 17-JUN-07
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=22 recid=1 stamp=625459450
input archive log thread=1 sequence=23 recid=2 stamp=625459916
input archive log thread=1 sequence=24 recid=3 stamp=625459929
channel ORA_DISK_1: starting piece 1 at 17-JUN-07
channel ORA_DISK_1: finished piece 1 at 17-JUN-07
piece handle=/u01/backup/03ikfgmr_1_1_3.bak tag=TAG20070617T025210 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 17-JUN-07

把备份文件传到备库中
[oracle@primary backup]$ pwd
/u01/backup
[oracle@primary backup]$ ls
01ikfgkh_1_1_1.bak 02ikfglk_1_1_2.bak 03ikfgmr_1_1_3.bak
[oracle@primary backup]$ pwd
/u01/backup
[oracle@primary backup]$ scp * 10.2.98.11:`pwd`
oracle@10.2.98.11's password:
01ikfgkh_1_1_1.bak 100% 298MB 3.0MB/s 01:39
02ikfglk_1_1_2.bak 100% 6976KB 2.3MB/s 00:03
03ikfgmr_1_1_3.bak 100% 178KB 178.0KB/s 00:00

也可以用热备的方法备份，把数据文件和归档传到备库中

7、在主库创建备用服务器控制文件
SQL> alter database create standby controlfile as '/u01/backup/standby.ctl';

Database altered.

拷贝到备库，并复制多份
--主库
[oracle@primary backup]$ scp standby.ctl 10.2.98.11:/u01/oracle/oradata/primary
oracle@10.2.98.11's password:
standby.ctl 100% 6896KB 3.4MB/s 00:02
--备库
[oracle@standby backup]$ mkdir -p /u01/oracle/oradata/primary
[oracle@standby backup]$ cp standby.ctl /u01/oracle/oradata/primary/control01.ctl
[oracle@standby backup]$ cp standby.ctl /u01/oracle/oradata/primary/control02.ctl
[oracle@standby backup]$ cp standby.ctl /u01/oracle/oradata/primary/control03.ctl

8、设置备库参数文件

从主库传送pfile到备库中。
[oracle@primary dbs]$ scp initprimary.ora 10.2.98.11:`pwd`
oracle@10.2.98.11's password:
initprimary.ora 100% 1528 1.5KB/s 00:00
修改如下参数：
*.DB_UNIQUE_NAME='standby'
*.FAL_CLIENT='standby'
*.FAL_SERVER='primary'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary,standby)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/u01/archivelog/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby'
*.LOG_ARCHIVE_DEST_2='SERVICE=primary LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary'
*.STANDBY_FILE_MANAGEMENT =AUTO
*.control_files='/u01/oracle/oradata/primary/control01.ctl','/u01/oracle/oradata/primary/control02.ctl','/u01/oracle/oradata/primary/control03.ctl'
*.COMPATIBLE = 10.2.0.3
*.log_file_name_convert='/u01/oracle/oradata/primary/','/u01/oracle/oradata/primary/'

9、在备库创建密码文件
[oracle@standby dbs]$ orapwd file=orapwprimary password=suk entries=10

在备库上创建目录：
[oracle@standby dbs]$ mkdir -p /u01/oracle/admin/primary/{adump,bdump,cdump,udump}
10、在备库端还原数据库
[oracle@standby dbs]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.3.0 - Production on Sun Jun 24 00:19:36 2007

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup nomount pfile=?/dbs/initprimary.ora
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1260696 bytes
Variable Size 75498344 bytes
Database Buffers 88080384 bytes
Redo Buffers 2932736 bytes

SQL> alter database mount standby database;

Database altered.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning and Data Mining options
[oracle@standby dbs]$ $ORACLE_HOME/bin/rman target /

Recovery Manager: Release 10.2.0.3.0 - Production on Sun Jun 24 00:20:10 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: PRIMARY (DBID=1463363807, not open)

RMAN> restore database;

Starting restore at 24-JUN-07
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=153 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/oracle/oradata/primary/system01.dbf
restoring datafile 00002 to /u01/oracle/oradata/primary/undotbs01.dbf
restoring datafile 00003 to /u01/oracle/oradata/primary/sysaux01.dbf
restoring datafile 00004 to /u01/oracle/oradata/primary/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/backup/01il1ila_1_1_1.bak
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/backup/01il1ila_1_1_1.bak tag=TAG20070623T231553
channel ORA_DISK_1: restore complete, elapsed time: 00:00:36
Finished restore at 24-JUN-07

RMAN> restore archivelog all;

Starting restore at 24-JUN-07
using channel ORA_DISK_1

channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=10
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=11
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=12
channel ORA_DISK_1: reading from backup piece /u01/backup/03il1inl_1_1_3.bak
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/backup/03il1inl_1_1_3.bak tag=TAG20070623T231708
channel ORA_DISK_1: restore complete, elapsed time: 00:00:06
Finished restore at 24-JUN-07

11、分别在主库和备库配置监听并启动
在主库，listener.ora配置如下：
[oracle@primary admin]$ more listener.ora
# listener.ora Network Configuration File: /u01/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = primary)
(ORACLE_HOME = /u01/oracle/product/10.2.0/db_1)
(SID_NAME = primary)
)
)

LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary)(PORT = 1521))
)

在备库，listener.ora配置如下：
[oracle@primary admin]$ more listener.ora
# listener.ora Network Configuration File: /u01/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = primary)
(ORACLE_HOME = /u01/oracle/product/10.2.0/db_1)
(SID_NAME = primary)
)
)

LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521))
)

12、在主库和备库分别配置tnsnames

在主库和备库的tnsnames.ora都做以下配置：
[oracle@primary admin]$ more tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

PRIMARY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.2.98.10)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = primary)
)
)

STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.2.98.11)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = primary)
)
)

13、备库端创建spfile
在备库执行：
SQL> create spfile from pfile;

File created.

13、启动备库
startup mount
14、在备库建立standby redo log和online redo log
在备库上建立standby log，大小与主库联机日志大小一样，组数至少大1；(如果是最大性能保护模式，可以不添加standby log，但是建议添加上，避免损失更多数据)
在备库中不需要建立联机日志，它会自动创建与主库一样的日志的。

首先在主库切换一次日志，使备库中记录的原主库的standby redo log信息被清除：
主库执行：
SQL> alter system switch logfile;

稍等一会，等在主库中查询v$standby_log没有原来主库standby log的信息返回时，再在备库中添加standby redo log。

如果备库正在处于恢复状态，先停止恢复，否则添加standby log会报错：
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
--取消恢复状态
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> alter database add standby logfile group 4 '/u01/oracle/oradata/primary/standbyredo4.log' size 50m;

Database altered.

SQL> alter database add standby logfile group 5 '/u01/oracle/oradata/primary/standbyredo5.log' size 50m;

Database altered.

SQL> alter database add standby logfile group 6 '/u01/oracle/oradata/primary/standbyredo6.log' size 50m;

Database altered.

SQL> alter database add standby logfile group 7 '/u01/oracle/oradata/primary/standbyredo7.log' size 50m;

Database altered.

15、启动redo apply
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

16、检查物理standby情况
1）在备库检查当前的archivelog
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# FIRST_TIME NEXT_TIME
---------- ------------------- -------------------
22 2007-06-17 02:42:12 2007-06-17 02:44:05
23 2007-06-17 02:44:05 2007-06-17 02:51:56
24 2007-06-17 02:51:56 2007-06-17 02:52:09

2）在主库新建一个表，插入数据，然后切换日志

SQL> create table test(id int);

Table created.

SQL> insert into test values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

3）再次检查备库的archivelog
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# FIRST_TIME NEXT_TIME
---------- ------------------- -------------------
22 2007-06-17 02:42:12 2007-06-17 02:44:05
23 2007-06-17 02:44:05 2007-06-17 02:51:56
24 2007-06-17 02:51:56 2007-06-17 02:52:09
25 2007-06-17 02:52:09 2007-06-17 04:16:23
26 2007-06-17 04:16:23 2007-06-17 04:19:16
27 2007-06-17 04:19:16 2007-06-17 04:21:59
此时检查alret文件，可以看到类似于下面的信息：
Sun Jun 24 16:36:32 2007
Primary database is in MAXIMUM PERFORMANCE mode
RFS[2]: Successfully opened standby log 4: '/u01/oracle/oradata/primary/standbyredo04.log'
Sun Jun 24 16:36:33 2007
Media Recovery Log /u01/archivelog/1_15_626106231.dbf

这表示应用归档成功。
4）在备库查询数据
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> ALTER DATABASE OPEN;

Database altered.

SQL> select * from test;

ID
----------
1

可以，数据已经正常同步。

至此，最大性能保护模式下的DG配置完成。

[回复]