利用rman创建standby(一)

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

1.试验环境
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

2.确认主库处于归档模式
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination d:/oracle/ora92/RDBMS
Oldest online log sequence 79
Next log sequence to archive 81
Current log sequence 81

3.创建备库instance

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

4.准备好备库的参数文件
本次试验在同一个系统上建备库，要注意设置好DB_FILE_NAME_CONVERT和LOG_FILE_NAME_CONVERT参数。另外

db_name设置为主库名，lock_name_space设置为备库名。主要的几个参数如下：
*.compatible='9.2.0.0.0'
*.control_files='d:/oracle/oradata/test/controlstandby.ctl'
*.background_dump_dest='d:/oracle/admin/test/bdump'
*.core_dump_dest='d:/oracle/admin/test/cdump'
*.user_dump_dest='d:/oracle/admin/test/udump'
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.db_name='ning'
*.lock_name_space='test'
*.db_file_name_convert='d:/oracle/oradata/ning','d:/oracle/oradata/test'
*.log_file_name_convert='d:/oracle/oradata/ning','d:/oracle/oradata/test'
*.fal_server=ning
*.fal_client=test
*.standby_archive_dest='location=d:/oracle/arch/test'
*.standby_file_management=auto

5.生成password file
c:/>orapwd file=d:/oracle/ora92/DATABASE/PWDtest.ORA password=pass

6.配置网络

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

Listener.ora

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

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = d:oracleora92)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = ning)
(ORACLE_HOME = d:oracleora92)
(SID_NAME = ning)
)
(SID_DESC =
(GLOBAL_DBNAME = test)
(ORACLE_HOME = d:oracleora92)
(SID_NAME = test)
)
)

tnsnames.ora

ning =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ning)
)
)

test =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
)
(CONNECT_DATA =
(SID = test)
)
)

7.使用rman备份主库
c:/>rman target /

Recovery Manager: Release 9.2.0.1.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

connected to target database: NING (DBID=1192138470)

RMAN> backup full format 'd:/backup.ora' database;

Starting backup at 21-NOV-06
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=15 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current SPFILE in backupset
including current controlfile in backupset
input datafile fno=00001 name=D:/ORACLE/ORADATA/NING/SYSTEM01.DBF
input datafile fno=00002 name=D:/ORACLE/ORADATA/NING/UNDOTBS01.DBF
input datafile fno=00004 name=D:/ORACLE/ORADATA/NING/EXAMPLE01.DBF
input datafile fno=00005 name=D:/ORACLE/ORADATA/NING/INDX01.DBF
input datafile fno=00007 name=D:/ORACLE/ORADATA/NING/USERS01.DBF
input datafile fno=00003 name=D:/ORACLE/ORADATA/NING/CWMLITE01.DBF
input datafile fno=00006 name=D:/ORACLE/ORADATA/NING/TOOLS01.DBF
input datafile fno=00008 name=D:/ORACLE/ORADATA/NING/TEST01.DBF
channel ORA_DISK_1: starting piece 1 at 21-NOV-06
channel ORA_DISK_1: finished piece 1 at 21-NOV-06
piece handle=D:/BACKUP.ORA comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:26
Finished backup at 21-NOV-06

8.生成备库控制文件
SQL> alter database create standby controlfile as 'd:/oracle/oradata/test/controlstandby.ctl';

9.启动备库到nomount状态
SQL> conn as sysdba
Connected to an idle instance.

SQL> startup nomount pfile='d:/oracle/ora92/database/inittest.ora'
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

10.将备库至于mount状态
SQL> alter database mount standby database;

Database altered.

如果参数文件的db_name和lock_name_space设置不正确，可能报以下错误：
ORA-01103: database name 'NING' in controlfile is not 'TEST'

如果没有password file，则报错：
ORA-01990: error opening password file 'd:/oracle/ora92/DATABASE/PWDtest.ORA'

11.利用rman恢复出备库
D:/Documents and Settings/haiyuan.ning>rman target

Recovery Manager: Release 9.2.0.1.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

connected to target database: NING (DBID=1192138470)

RMAN> restore database;

Starting restore at 21-NOV-06

using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=14 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 D:/ORACLE/ORADATA/TEST/SYSTEM01.DBF
restoring datafile 00002 to D:/ORACLE/ORADATA/TEST/UNDOTBS01.DBF
restoring datafile 00003 to D:/ORACLE/ORADATA/TEST/CWMLITE01.DBF
restoring datafile 00004 to D:/ORACLE/ORADATA/TEST/EXAMPLE01.DBF
restoring datafile 00005 to D:/ORACLE/ORADATA/TEST/INDX01.DBF
restoring datafile 00006 to D:/ORACLE/ORADATA/TEST/TOOLS01.DBF
restoring datafile 00007 to D:/ORACLE/ORADATA/TEST/USERS01.DBF
restoring datafile 00008 to D:/ORACLE/ORADATA/TEST/TEST01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=D:/BACKUP.ORA tag=TAG20061121T134050 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 21-NOV-06

12.修改主库的归档参数
SQL> alter system set log_archive_dest_1='location=d:/oracle/arch/ning';

System altered.

SQL> alter system set log_archive_dest_2='service=test';

System altered.

13.将备库置于自动恢复状态
SQL> conn as sysdba
Connected.

SQL> recover managed standby database disconnect from session;
Media recovery complete.
SQL> alter database recover managed standby database disconnect from session;
alter database recover managed standby database disconnect from session
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active

SQL> alter database recover managed standby database finish;

Database altered.

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

SQL> select * from v$archive_gap;

no rows selected

SQL> select sequence# from v$log_history;

SEQUENCE#
----------
1
2
...
79
80

15.在备库上执行检查
SQL> conn as sysdba
Connected.
SQL> select * from v$archive_gap;

no rows selected

SQL> select sequence# from v$log_history;

SEQUENCE#
----------
1
2
...
79
80

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

SEQUENCE# APP
---------- ---
79 YES
80 YES

SQL> select process,status from v$managed_standby;

PROCESS STATUS
------- ------------
ARCH CONNECTED
ARCH CONNECTED
MRP0 WAIT_FOR_LOG
RFS RECEIVING