Oracle 11g Linux单机STANDBY配置

blue_prince

环境：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]
[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
..
[/php]

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

blue_prince

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)'

blue_prince

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

[php]
[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
..
[/php]
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就配置完成了。

blue_prince

最早按10G的方法配置，无法配置成功。后面按文档里面说的步骤进行配置，并且参数什么的都遵循文档里面新的方法，才配置成功。相比之前的STANDBY，11G就是废除了几个参数， 增加了VALID_FOR=(STANDBY_LOGFILE,STANDBY_ROLE)这么几个东西。接下有空再测测边恢复边打开的新特性。

NinGoo

VALID_FOR=(STANDBY_LOGFILE,STANDBY_ROLE)

这个在10g里就有了的啊

blue_prince

最初由 NinGoo 发布
[B]VALID_FOR=(STANDBY_LOGFILE,STANDBY_ROLE)

这个在10g里就有了的啊 [/B]

哦，10G里面我从来没配置过这玩意儿

vongates

VALID_FOR=(STANDBY_LOGFILE,STANDBY_ROLE)
这个是从10g起就一直成在的.

vongates

最初由 blue_prince 发布
[B]

哦，10G里面我从来没配置过这玩意儿 [/B]

那这次为什么要用这个呀.

等其它OS的出来,公司连linux的环境都没有.

blue_prince

最初由 vongates 发布
[B]

那这次为什么要用这个呀.

等其它OS的出来,公司连linux的环境都没有. [/B]

前面一直没配置成功，只好照着文档一个不漏地走下来。

vongates

最初由 blue_prince 发布
[B]

前面一直没配置成功，只好照着文档一个不漏地走下来。 [/B]

通常 log_archive_dest_1 归档到本地都使用 default的值
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
或者
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)

可以通过下面的SQL来查看设定的是不是有问题.正确的话,valid_now列会为YES


SELECT DEST_ID,VALID_TYPE,VALID_ROLE,VALID_NOW FROM V$ARCHIVE_DEST;