之前整理过一篇利用数据库冷备份创建physical standby的文章，数据库的冷备需要停机，在生产环境中实用性不大，本将中将介绍如何利用rman热备来创建dataguard物理备库，这样主库只需要重启一次即可，大大提高了主库的高可用性！
环境介绍：
primary:rhel5.4 32位 192.168.227.20/24
standby:rhel5.4 32位 192.168.227.30/24
oracle版本： 10.2.0.1 32位企业版
一：standby服务器
1：主库上开启Forced Logging
SQL> alter database force logging; Database altered. 2：在备库上创建密码文件，这里直接从主库复制到备库
[oracle@orcl ~]$ scp $ORACLE_HOME/dbs/orapworcl 192.168.227.30:/u01/app/oracle/product/10.2.0/db_1/dbs oracle@192.168.227.30's password: orapworcl 100% 1536 1.5KB/s 00:00 3：主库上配置Standby Redo Log
SQL> select member from v$logfile; MEMBER --------------------------------------- /u01/app/oracle/oradata/orcl/redo03.log /u01/app/oracle/oradata/orcl/redo02.log /u01/app/oracle/oradata/orcl/redo01.log SQL> !du -sh /u01/app/oracle/oradata/orcl/redo01.log 51M /u01/app/oracle/oradata/orcl/redo01.log SQL> alter database add standby logfile group 4 2 '/u01/app/oracle/oradata/orcl/standby/standby04.log' size 50M; Database altered. SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG; GROUP# THREAD# SEQUENCE# ARC STATUS ---------- ---------- ---------- --- ---------- 4 0 0 YES UNASSIGNED 5 0 0 YES UNASSIGNED 6 0 0 YES UNASSIGNED 4.修改主库的初始化参数
SQL> show parameter db_name; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string orcl SQL> show parameter db_unique_name; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_unique_name string orcl SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary,standby)'; System altered. SQL> alter system set log_archive_dest_1='LOCATION=/u01/arch/orcl valid_for=(all_logfiles,all_roles) db_unique_name=orcl' scope=spfile; System altered. SQL> alter system set log_archive_dest_2='service=standby lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=orcl' scope=spfile; System altered. SQL> alter system set log_archive_dest_state_1=enable; System altered. SQL> alter system set log_archive_dest_state_2=enable; System altered. SQL> alter system set log_archive_format='%t_%s_%r.arc' scope=spfile; System altered. SQL> show parameter remote_login; NAME TYPE VALUE ------------------------------------ ----------- --------- remote_login_passwordfile string EXCLUSIVE SQL> alter system set log_archive_max_processes=30; System altered. SQL> alter system set fal_server=primary; System altered. SQL> alter system set fal_client=standby; System altered. SQL> alter system set standby_file_management=auto; System altered. SQL> !mkdir -p /u01/arch/orcl SQL> shutdown immediate; SQL> startup 5：配置主库的tnsnames.ora文件，备库需要同样的操作
[oracle@orcl ~]$ cat $ORACLE_HOME/network/admin/tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. primary = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.227.20)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = orcl.herostart.com) ) ) standby = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.227.30)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = orcl.herostart.com) ) ) 6：在主库上准备备库需要的pfile和数据库备份文件，控制文件等
[oracle@orcl ~]$ mkdir -p /u01/backup/ SQL> create pfile='/u01/backup/initorcl.ora' from spfile; File created. [oracle@orcl ~]$ rman target / RMAN> backup tag 'dg_20110909' format '/u01/backup/dg_%U' incremental level 0 database plus archivelog; SQL> alter database create standby controlfile as '/u01/backup/standby01.ctl'; Database altered. 二：standby服务器
1：设置oracle_sid,配置tnsnames.ora
[oracle@orcl ~]$ export ORACLE_SID=orcl [oracle@orcl ~]$ cat $ORACLE_HOME/network/admin/tnsnames.ora primary = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.227.20)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = orcl.herostart.com) ) ) standby = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.227.30)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = orcl.herostart.com) ) ) 2：在备库上复制主库备份出来的备份文件，并准备相关的目录
[oracle@orcl ~]$ mkdir -p /u01/backup/ [oracle@orcl ~]$ scp -rp 192.168.227.20:/u01/backup/* /u01/backup/ [oracle@orcl ~]$ cp /u01/backup/initorcl.ora $ORACLE_HOME/dbs [oracle@orcl ~]$ grep 'fal_' $ORACLE_HOME/dbs/initorcl.ora *.fal_client='PRIMARY' *.fal_server='STANDBY' [oracle@orcl ~]$ mkdir -p /u01/arch/orcl [oracle@orcl ~]$ mkdir -p /u01/app/oracle/admin/orcl/{adump,bdump,cdump,dpdump,udump,pfile} [oracle@orcl ~]$ mkdir -p /u01/app/oracle/oradata/orcl [oracle@orcl ~]$ mkdir -p /u01/app/oracle/flash_recover_area/ORCL [oracle@orcl ~]$ cp /u01/backup/standby01.ctl /u01/app/oracle/oradata/orcl/control01.ctl [oracle@orcl ~]$ cp /u01/backup/standby01.ctl /u01/app/oracle/oradata/orcl/control02.ctl [oracle@orcl ~]$ cp /u01/backup/standby01.ctl /u01/app/oracle/oradata/orcl/control03.ctl 3：将备库启动到mount状态
[oracle@orcl ~]$ sqlplus /nolog SQL> conn /as sysdba Connected to an idle instance. SQL> startup nomount ORACLE instance started. SQL> alter database mount standby database; Database altered. 4：使用rman对备库进行还原和恢复，在recover的时候将会报错，这个时候的第48号日志应该是主库的online redolog，所以会找不到
[oracle@orcl ~]$ rman target / RMAN> restore database; RMAN> recover database; RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 09/09/2011 11:14:09 RMAN-06054: media recovery requesting unknown log: thread 1 seq 48 lowscn 603594 5: 将备库置于应用redolog模式
[oracle@orcl ~]$ sqlplus /nolog SQL> conn /as sysdba Connected. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/arch/orcl Oldest online log sequence 47 Next log sequence to archive 49 Current log sequence 49 SQL> alter database recover managed standby database disconnect from session; Database altered. SQL> select SEQUENCE#,FIRST_TIME,NEXT_TIME,APPLIED from v$archived_log; SEQUENCE# FIRST_TIME NEXT_TIME APP ---------- ------------------- ------------------- --- 47 2011-09-09:10:56:31 2011-09-09:10:57:51 YES 可以在主库上进行日志切换，加快备库应用日志的速度！
[oracle@orcl ~]$ sqlplus /nolog SQL> conn /as sysdba Connected. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/arch/orcl Oldest online log sequence 47 Next log sequence to archive 49 Current log sequence 49 SQL> alter system switch logfile; System altered. SQL> alter system switch logfile; System altered. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/arch/orcl Oldest online log sequence 48 Next log sequence to archive 50 Current log sequence 50 备库上再次查询： SQL> select SEQUENCE#,FIRST_TIME,NEXT_TIME,APPLIED from v$archived_log; SEQUENCE# FIRST_TIME NEXT_TIME APP ---------- ------------------- ------------------- --- 47 2011-09-09:10:56:31 2011-09-09:10:57:51 YES 49 2011-09-09:10:57:54 2011-09-09:11:21:25 YES 48 2011-09-09:10:57:51 2011-09-09:10:57:54 YES SQL> select name,database_role from v$database; NAME DATABASE_ROLE --------- ---------------- ORCL PHYSICAL STANDBY 三：测试
1.在主库上创建表空间，建表
SQL> select name,database_role from v$database; NAME DATABASE_ROLE --------- ---------------- ORCL PRIMARY SQL> create tablespace dg_test datafile 2 '/u01/app/oracle/oradata/orcl/dg_test01.dbf' 3 size 10M autoextend off; Tablespace created. SQL> create table dg01 as select * from dba_source; Table created SQL> alter system switch logfile; System altered. 2.备库应用日志后以只读方式打开查看数据
SQL> alter database recover managed standby database cancel; Database altered SQL> alter database open read only; Database altered. SQL> select type from dg01 where rownum <= 3; TYPE ------------ PACKAGE PACKAGE PACKAGE SQL> select count(*) from dg01; COUNT(*) ---------- 292428 3：从新将备库置于应用日志模式
[oracle@orcl ~]$ sqlplus /nolog SQL> conn /as sysdba Connected. SQL> shutdown immediate; SQL> startup nomount； SQL> alter database mount standby database; Database altered. SQL> alter database recover managed standby database disconnect from session; Database altered. 备注：本文参考oracle10g 手册，相关的参数含义可以在手册上查找！
作者“月牙天冲”