Oracle 11g 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配置：

PHP code:



[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：

PHP code:



[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至恢复管理模式：
[php]

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.
..
[php]
9、主库配置到STANDBY的归档：

Sys@ORA11G> alter system set log_archive_dest_state_2=enable;

System altered.

Sys@ORA11G> alter system set log_archive_dest_2='service=standby ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby';

System altered.

VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)这个在我配置的过程中是必须要的，我自作主张把里面的PRIMARY_ROLE改为STANDBY _ROLE，归档无法从主库传至STANDBY了。

10、可以看到STANDBY端已经收到主库传递过来的归档日志了：

[oracle@test51 archive]$ ls -l
total 7236
-rw-r-----  1 oracle oracle 7392768 Aug 16 16:46 1_63_630254857.dbf

Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[2]: Assigned to RFS process 5315
RFS[2]: Identified database type as 'physical standby'
Primary database is in MAXIMUM PERFORMANCE mode
Primary database is in MAXIMUM PERFORMANCE mode
RFS[2]: Successfully opened standby log 4: '/home/oracle/opt/oradata/standby/redo04.log'
Thu Aug 16 16:46:21 2007
Media Recovery Log /home/oracle/opt/oradata/standby/archive/1_63_630254857.dbf
Media Recovery Waiting for thread 1 sequence 64 (in transit)

这样STANDBY就配置完成了。