Oracle dataGuard专题：利用rman创建standby-白红宇

本专题相关链接

Oracle dataGuard专题：利用冷备创建standby：

Oracle dataGuard专题：Rman通过duplicate创建standby:

使用rman来创建standby，过程相当简单，主库无需停机。本次试验演示了利用Rman热备，然后restore来创建备库的过程。

1. 演示环境

[oracle@gaojf orcl]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Dec 6 08:54:45 2006

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> select * from v$version;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE 10.2.0.1.0 Production

TNS for Linux: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

SQL>

2. 确认主库处于归档模式

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /free/oracle/orabak

Oldest online log sequence 275

Next log sequence to archive 277

Current log sequence 277

SQL>

3：创建备库instance

windows平台利用oradim工具创建一个新的instance，unix/linux平台设置新的ORACLE_SID即可，我的是linux平台，设置ORACLE_SID即可。

4：准备好备库的参数文件

本次试验在两个系统上建主备库，另外如果主备库都在一起的话，db_name设置为主库名，lock_name_space设置为备库名，同时要注意设置好DB_FILE_NAME_CONVERT和LOG_FILE_NAME_CONVERT参数。

备库主要的几个参数如下：

*.log_archive_config='DG_CONFIG=(PRIMARY,STANDBY)'

*.LOG_ARCHIVE_DEST_1='LOCATION=/free/oracle/orabak VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STANDBY'

*.LOG_ARCHIVE_DEST_2='SERVICE=primary VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRIMARY LGWR SYNC AFFIRM'

*.db_unique_name='STANDBY'

*.FAL_CLIENT='STANDBY'

*.FAL_SERVER='PRIMARY'

*.log_archive_dest_state_1='enable'

*.log_archive_dest_state_2='enable'

*.STANDBY_ARCHIVE_DEST='/free/oracle/orabak'

*.STANDBY_FILE_MANAGEMENT='AUTO'

主库几个主要参数如下：

*.log_archive_config='DG_CONFIG=(PRIMAYR,STANDBY)'

*.DB_UNIQUE_NAME='PRIMARY'

*.LOG_ARCHIVE_DEST_1= 'LOCATION=/free/oracle/orabak'

#*.LOG_ARCHIVE_DEST_2='SERVICE=STANDBYVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STANDBY LGWR SYNC AFFIRM' ＃LGWR方式

*.LOG_ARCHIVE_DEST_2='SERVICE=STANDBY DB_UNIQUE_NAME=STANDBY ARCH SYNC REOPEN=10' ＃arch方式

*.LOG_ARCHIVE_DEST_STATE_1=ENABLE

*.LOG_ARCHIVE_DEST_STATE_2=ENABLE

*.LOG_ARCHIVE_FORMAT='arc%s%t%r.arc'

*.FAL_SERVER='STANDBY'

*.FAL_CLIENT='PRIMARY'

*.STANDBY_FILE_MANAGEMENT='AUTO'

*.standby_archive_dest='/free/oracle/orabak'

5.在备库生成password file

[oracle@gaojfdbs]$Orapwd file=/free/oracle/product/10.2.0/db_1/orapworcl.ora password=orcl entries=5

或者可以直接复制主库密码文件到备库相应的位置也可。

6.配置网络

配置主备库的listener.ora,tnsnames.ora。修改完lisner.ora后注意重启监听。

主库Listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = /free/oracle/product/10.2.0/db_1)

(PROGRAM = extproc)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.60.144)(PORT = 1521))

)

备库Listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = /free/oracle/product/10.2.0/db_1)

(PROGRAM = extproc)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.60.253)(PORT = 1521))

)

tnsnames.ora（主备库配置一样）

PRIMARY =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.60.144)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = primary)

)

STANDBY =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.60.253)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = standby)

)

7.使用rman备份主库

c:/>rman target /

Recovery Manager: Release 9.2.0.1.0 - Production

connected to target database: NING (DBID=1192138470)

RMAN> backup database

2> format '/free/oracle/full_%d_%T_%s'；

Starting backup at 06-DEC-06

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00001 name=/free/oracle/oradata/orcl/system01.dbf

input datafile fno=00003 name=/free/oracle/oradata/orcl/sysaux01.dbf

input datafile fno=00006 name=/free/oracle/oradata/orcl/gaojf1db.dbf

input datafile fno=00007 name=/free/oracle/oradata/orcl/gaojf2db.dbf

input datafile fno=00008 name=/free/oracle/oradata/orcl/gaojf3db.dbf

input datafile fno=00010 name=/free/oracle/oradata/orcl/cicrodb1.dbf

input datafile fno=00011 name=/free/oracle/oradata/orcl/cicrodb11.dbf

input datafile fno=00009 name=/free/oracle/oradata/orcl/gaojf4db.dbf

input datafile fno=00002 name=/free/oracle/oradata/orcl/undotbs01.dbf

input datafile fno=00005 name=/free/oracle/oradata/orcl/gaojfdb.dbf

input datafile fno=00004 name=/free/oracle/oradata/orcl/users01.dbf

channel ORA_DISK_1: starting piece 1 at 06-DEC-06

channel ORA_DISK_1: finished piece 1 at 06-DEC-06

piece handle=/free/oracle/full_ORCL_20061206_37 tag=TAG20061206T101027 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:03:15

Finished backup at 06-DEC-06

8.生成备库控制文件

SQL>

alter database create standby controlfile as '/free/oracle/oradata/orcl/standby.ctl';

9：复制 rman全备份到备库

利用操作系统命令scp，或者ftp bin方式复制rman全备份到备库。

由于我rman主库备份时设置备份文件位置为/free/oracle，因此必须把此备份文件也放到备库同样的位置。

同时scp 控制文件standby.ctl到备库相应的位置。

10.启动备库到nomount状态

SQL> conn /as sysdba

Connected to an idle instance.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 135338868 bytes

Fixed Size 453492 bytes

Variable Size 109051904 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

11.将备库至于mount状态

SQL> alter database mount standby database;

Database altered.

如果没有password file，则报错：

ORA-01990: error opening password file '/free/oracle/product/10.2.0/db_1/orapworcl.ora '

12.利用rman恢复出备库

:~> rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on 星期四 12月 7 06:39:56 2006

connected to target database: ORCL (DBID=1132959697, not open)

RMAN>

RMAN> restore database;

Starting backup at 06-DEC-06

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

restoringdatafile fno=00001 name=/free/oracle/oradata/orcl/system01.dbf

restoringdatafile fno=00003 name=/free/oracle/oradata/orcl/sysaux01.dbf

restoring datafile fno=00006 name=/free/oracle/oradata/orcl/gaojf1db.dbf

restoring datafile fno=00007 name=/free/oracle/oradata/orcl/gaojf2db.dbf

restoring datafile fno=00008 name=/free/oracle/oradata/orcl/gaojf3db.dbf

restoring datafile fno=00010 name=/free/oracle/oradata/orcl/cicrodb1.dbf

restoringdatafile fno=00011 name=/free/oracle/oradata/orcl/cicrodb11.dbf

restoring datafile fno=00009 name=/free/oracle/oradata/orcl/gaojf4db.dbf

restoringdatafilefno=00002name=/free/oracle/oradata/orcl/undotbs01.dbf

restoring datafile fno=00005 name=/free/oracle/oradata/orcl/gaojfdb.dbf

restoring datafile fno=00004 name=/free/oracle/oradata/orcl/users01.dbf

channel ORA_DISK_1: starting piece 1 at 06-DEC-06

channel ORA_DISK_1: finished piece 1 at 06-DEC-06

piecehandle=/free/oracle/full_ORCL_20061206_37tag=TAG20061206T101027 comment=NONE

channel ORA_DISK_1: restore complete, elapsed time: 00:03:15

Finished restore at 06-DEC-06

13.将备库置于自动恢复状态

SQL> alter database recover managed standby database disconnect from session;

Database altered.

14.在主库上执行检查

SQL> conn /as sysdba

Connected.

SQL> select dest_name,status,error from v$archive_dest;

DEST_NAME STATUS ERROR

------------------------------ -------- ----------

LOG_ARCHIVE_DEST_1 VALID

LOG_ARCHIVE_DEST_2 VALID

LOG_ARCHIVE_DEST_3 INACTIVE

LOG_ARCHIVE_DEST_4 INACTIVE

LOG_ARCHIVE_DEST_5 INACTIVE

LOG_ARCHIVE_DEST_6 INACTIVE

LOG_ARCHIVE_DEST_7 INACTIVE

LOG_ARCHIVE_DEST_8 INACTIVE

LOG_ARCHIVE_DEST_9 INACTIVE

LOG_ARCHIVE_DEST_10 INACTIVE

10 rows selected.

SQL> select * from v$archive_gap;

no rows selected

SQL> select sequence# from v$log_history;

SEQUENCE#

----------

...

303

15.在备库上执行检查

SQL> conn / as sysdba

Connected.

SQL> select * from v$archive_gap;

no rows selected

SQL> select sequence# from v$log_history;

SEQUENCE#

----------

...

303

SQL> select sequence#,applied from v$archived_log;

SEQUENCE# APPLIE

---------- ------

295 YES

296 YES

297 YES

298 YES

299 YES

300 YES

301 YES

302 YES

303 YES

SQL> select process,status from v$managed_standby;

PROCESS STATUS

------------------ ------------------------

ARCH CLOSING

RFS IDLE

MRP0 WAIT_FOR_LOG

本文转自南非蚂蚁51CTO博客，原文链接： http://blog.51cto.com/ixdba/642906，如需转载请自行联系原作者