Oracle11g Linux单机STANDBY配置

环境：RHEL 4 U5+Oracle 11.1.0.6

主库SID：ora11g 备用库SID:standby

主库数据文件存放目录：/home/oracle/opt/oradata/ora11g/

备用库数据文件存放目录：/home/oracle/opt/oradata/standby/

1、 验证主库是否归档，如果是未归档模式的话必须更改为归档模式：

Sys@ORA11G> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /home/oracle/opt/oradata/ora11g/archive

Oldest online log sequence 61

Next log sequence to archive 63

Current log sequence 63

2、 将主库置于FORCE LOGGING模式：

Sys@ORA11G> alter database force logging;

3、 对主库做一个全库的RMAN备份，用于STANDBY配置：

[oracle@test51 bin]$ ./rman target /

Recovery Manager: Release 11.1.0.6.0 - Production on Thu Aug 16 15:51:22 2007

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

connected to target database: ORA11G (DBID=4026454982)

RMAN> backup database format '/home/oracle/liuyun/%U.bak';

Starting backup at 16-AUG-07

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=121 device type=DISK

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00004 name=/home/oracle/opt/oradata/ora11g/users01.dbf

input datafile file number=00002 name=/home/oracle/opt/oradata/ora11g/sysaux01.dbf

input datafile file number=00001 name=/home/oracle/opt/oradata/ora11g/system01.dbf

input datafile file number=00005 name=/home/oracle/opt/oradata/ora11g/example01.dbf

input datafile file number=00003 name=/home/oracle/opt/oradata/ora11g/undotbs01.dbf

channel ORA_DISK_1: starting piece 1 at 16-AUG-07

channel ORA_DISK_1: finished piece 1 at 16-AUG-07

piece handle=/home/oracle/liuyun/03iphp8l_1_1.bak tag=TAG20070816T155148 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:01:45

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

including current SPFILE in backup set

channel ORA_DISK_1: starting piece 1 at 16-AUG-07

channel ORA_DISK_1: finished piece 1 at 16-AUG-07

piece handle=/home/oracle/liuyun/04iphpc9_1_1.bak tag=TAG20070816T155148 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 16-AUG-07

4、 准备STANDBY数据文件路径和其他路径：

[oracle@test51 oradata]$ pwd

/home/oracle/opt/oradata

[oracle@test51 oradata]$ mkdir standby

[oracle@test51 oradata]$ ls

ora11g orcl standby

[oracle@test51 oradata]$ cd $ORACLE_BASE/admin

[oracle@test51 admin]$ mkdir standby

[oracle@test51 admin]$ ls

ora11g orcl standby

[oracle@test51 standby]$mkdir adump

[oracle@test51 standby]$mkdir dpdump

[oracle@test51 standby]$mkdir pfile

[oracle@test51 standby]$ ls

adump dpdump pfile

5、更改tnsnames配置，添加主库和备用库的连接字：

primary =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = test51)(PORT = 1522))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ora11g)

)

)

standby =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = test51)(PORT = 1522))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = standby)

)

)

6、生成STANDBY控制文件：

Sys@ORA11G> alter database create standby controlfile as '/home/oracle/opt/oradata/standby/control01.ctl';

Database altered.

[oracle@test51 standby]$ cp control01.ctl control02.ctl

[oracle@test51 standby]$ cp control01.ctl control03.ctl

[oracle@test51 standby]$ ls

archive control01.ctl control02.ctl control03.ctl

6、生成standby 初始化参数文件：

Sys@ORA11G> create pfile='$ORACLE_BASE/admin/standby/pfile/init.ora' from spfile;

File created.

添 加以下几个参数，需要注意的是从11G开始原来备用库归档参数standby_archive_dest这个参数已经废除了，STANDBY的归档路径改 为常规的归档路径log_archive_dest_n。还有原来单机配置STANDBY需要的参数lock_name_space也废除了：

*.log_archive_config='DG_CONFIG=(ora11g,standby)'

*.fal_client='standby'

*.fal_server='primary'

*.db_file_name_convert='ora11g','standby'

*.log_file_name_convert='ora11g','standby'

*.standby_file_management='auto'

*.log_archive_dest_1='location=/home/oracle/opt/oradata/standby/archive VALID_FOR=(STANDBY_LOGFILE,STANDBY_ROLE)'

更改后的参数文件如下，注意db_unique_name要和主库区分，否则无法MOUNT STANDBY：

standby.__db_cache_size=234881024

standby.__java_pool_size=12582912

standby.__large_pool_size=4194304

standby.__oracle_base='/home/oracle/opt'#ORACLE_BASE set from environment

standby.__pga_aggregate_target=209715200

standby.__sga_target=629145600

standby.__shared_io_pool_size=0

standby.__shared_pool_size=369098752

standby.__streams_pool_size=0

*.audit_file_dest='/home/oracle/opt/admin/standby/adump'

*.audit_trail='db'

*.compatible='11.1.0.0.0'

*.control_files='/home/oracle/opt/oradata/standby/control01.ctl','/home/oracle/opt/oradata/standby/control02.ctl'

,'/home/oracle/opt/oradata/standby/control03.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='ora11g'

*.db_recovery_file_dest='/home/oracle/opt/flash_recovery_area'

*.db_recovery_file_dest_size=2147483648

*.db_unique_name='standby'

*.ddl_lock_timeout=10

*.diagnostic_dest='/home/oracle/opt'

*.dispatchers=''

*.job_queue_processes=0

*.local_listener='LISTENER_ora11g'

*.memory_target=838860800

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

*.log_archive_config='DG_CONFIG=(ora11g,standby)'

*.fal_client='standby'

*.fal_server='primary'

*.db_file_name_convert='ora11g','standby'

*.log_file_name_convert='ora11g','standby'

*.standby_file_management='auto'

*.log_archive_dest_1='location=/home/oracle/opt/oradata/standby/archive VALID_FOR=(STANDBY_LOGFILE,STANDBY_ROLE)'

7、启动STANDBY数据库，进行RESTORE：

[oracle@test51 standby]$ export ORACLE_SID=standby

[oracle@test51 standby]$ sql

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Aug 16 16:27:08 2007

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

Connected to an idle instance.

idle> startup nomount pfile='$ORACLE_BASE/admin/standby/pfile/init.ora'

ORACLE instance started.

Total System Global Area 836976640 bytes

Fixed Size 1303132 bytes

Variable Size 595594660 bytes

Database Buffers 234881024 bytes

Redo Buffers 5197824 bytes

idle> alter database mount standby database;

Database altered.

[oracle@test51 bin]$ ./rman target /

Recovery Manager: Release 11.1.0.6.0 - Production on Thu Aug 16 16:30:11 2007

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

connected to target database: ORA11G (DBID=4026454982, not open)

RMAN> restore database;

Starting restore at 16-AUG-07

Starting implicit crosscheck backup at 16-AUG-07

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=147 device type=DISK

Crosschecked 4 objects

Finished implicit crosscheck backup at 16-AUG-07

Starting implicit crosscheck copy at 16-AUG-07

using channel ORA_DISK_1

Crosschecked 2 objects

Finished implicit crosscheck copy at 16-AUG-07

searching for all files in the recovery area

cataloging files...

no files cataloged

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore

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

channel ORA_DISK_1: restoring datafile 00001 to /home/oracle/opt/oradata/standby/system01.dbf

channel ORA_DISK_1: restoring datafile 00002 to /home/oracle/opt/oradata/standby/sysaux01.dbf

channel ORA_DISK_1: restoring datafile 00003 to /home/oracle/opt/oradata/standby/undotbs01.dbf

channel ORA_DISK_1: restoring datafile 00004 to /home/oracle/opt/oradata/standby/users01.dbf

channel ORA_DISK_1: restoring datafile 00005 to /home/oracle/opt/oradata/standby/example01.dbf

channel ORA_DISK_1: reading from backup piece /home/oracle/liuyun/03iphp8l_1_1.bak

channel ORA_DISK_1: piece handle=/home/oracle/liuyun/03iphp8l_1_1.bak tag=TAG20070816T155148

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:02:05

Finished restore at 16-AUG-07

8、添加STANDBY LOGFILE，启动STANDBY至恢复管理模式：

idle> alter database add standby logfile '/home/oracle/opt/oradata/standby/redo04.log' size 50M;

Database altered.

Elapsed: 00:00:01.06

idle> alter database add standby logfile '/home/oracle/opt/oradata/standby/redo05.log' size 50M;

Database altered.

Elapsed: 00:00:01.18

idle> alter database add standby logfile '/home/oracle/opt/oradata/standby/redo06.log' size 50M;

Database altered.

Elapsed: 00:00:00.85

idle> recover managed standby database disconnect from session;

Media recovery complete.

9、主库配置到STANDBY的归档：

Sys@ORA11G> alter system set log_archive_dest_state_2=enable;