Oracle 10G R2的Data Guard物理standby搭建（原创）

搭建环境：
OS :RHEL 5.4              Oracle版本：10g R2             ORACLE_HOME=/u01/app/oracle
虚拟机1    hostname :dg1            IP:192.168.0.103
虚拟机2    hostname :dg2            IP:192.168.0.104

参数规划
数据库       db_name      sid      instance_name      service_names       db_unique_name     fal_server      fal_client  
主 库              dg1            dg1           dg1                      dg1                           dg1                        dg2                 dg1
备库              dg1            dg1           dg1                      dg2                           dg2                        dg1                  dg2

注意：搭建10g DG时数据库版本必须一致，包括小版本，且操作系统平台之间必须满足 一定要求具体可以参见

http://czmmiao.iteye.com/blog/1447643
主库上的操作
将主库至于foce logging状态
[oracle@dg1 ~]$ sqlplus / as sysdba;
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jan 25 19:56:11 2011
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.
Total System Global Area  373293056 bytes
Fixed Size                  1219496 bytes
Variable Size             138413144 bytes
Database Buffers          230686720 bytes
Redo Buffers                2973696 bytes
Database mounted.
SQL> alter database force logging;   
Database altered.        

关闭force logging模式的语法如下
alter database no  force logging ;
执行force logging的原因：有一些DDL语句可以通过指定NOLOGGING子句的方式避免写REDO（目的是提高速度，某些时候确实有效）。指定数据库为Force Logging模式后，数据库将会记录除临时表空间或临时回滚段外所有的操作，而忽略类似NOLOGGING之类的指定参数。如果在执行Force Logging时有NOLOGGING之类的语句在执行，那么Force Logging会等待，直到这类语句全部执行。
Force Logging是作为固定参数保存在控制文件中，因此其不受重启之类操作的影响（只执行一次即可），如果想取消，可以通过ALTER DATABASE NO FORCE LOGGING语句关闭强制记录。
注意：force logging并不比一般的logging 记录的日志多,数据库在force logging状态下,nologging选项将无效,因为nologging将破坏dataguard的可恢复性.force logging

强制数据库在任何状态下必须记录日志而已。
并处于归档模式
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oradata/archive
Oldest online log sequence     40
Next log sequence to archive   42
Current log sequence           42
查看需要拷贝的数据文件
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/czmmiao/system01.dbf
/u01/app/oracle/oradata/czmmiao/undotbs01.dbf
/u01/app/oracle/oradata/czmmiao/sysaux01.dbf
/u01/app/oracle/oradata/czmmiao/users01.dbf
/u01/app/oracle/oradata/czmmiao/example.dbf
/u01/app/oracle/oradata/czmmiao/nk_tbs.dbf
6 rows selected.
查看需要拷贝的临时文件
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/czmmiao/temp01.dbf
/u01/app/oracle/oradata/czmmiao/temp.dbf
2 rows selected.

利用主库的控制文件创建备库的控制文件
SQL> alter database create standby controlfile as '/u01/app/oracle/dbs/standby.ctl';
Database altered.
利用主库的spfile创建pfile，主要是为了便于参数的修改
SQL> create pfile from spfile;
File created.
关闭主库
SQL> shutdown immediate;
Database dismounted.
ORACLE instance shut down.
主库参数文件配置
在主库的pfile增加如下几行，其中红色的需要特别注意
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(dg1,dg2)'
*.log_archive_dest_1='LOCATION=/u01/app/oradata/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dg1 '    #这个参数的意义为： 在数据库dg1上log_archive_dest_1对主备库上的联机日志都有效，这里的 db_unique_name可以省略

*.log_archive_dest_2='SERVICE=dg2 LGWR ASYNC=40960 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dg2 '      
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=2
*.log_archive_start=true
*.service_names='dg1 '                
*.db_name='dg1 '
*.db_unique_name='dg1 '
*.fal_client='dg1 '
*.fal_server='dg2 '
注意：这里的fal_client、fal_server参数是针对主库转换为备库之后而言的。
将所需文件拷贝到dg2上
[oracle@dg1 dbs]$ more /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1               dg1 localhost.localdomain localhost
192.168.0.104   dg2
192.168.0.103   dg1
拷贝数据文件和日志文件
[oracle@dg1 oradata]$ pwd
/u01/app/oradata
[oracle@dg1 oradata]$ ls
archive  dg1
这里采用关闭主库进行数据文件拷贝的方式，当然也可以采用rman备份的方式进行拷贝，采用rman的方式可以不用关闭主库。
[oracle@dg1 oradata]$ scp -r dg1/ dg2:/u01/app/oradata/
拷贝admin下的文件，主要是为了拷贝这个目录的目录结构
[oracle@dg1 app]$ pwd
/u01/app
[oracle@dg1 app]$ ls
admin  flash_recovery_area  oracle  oradata  oraInventory
[oracle@dg1 app]$ scp -r admin/ dg2:/u01/app/
在主库上创建口令文件
[oracle@dg1 app]$ orapwd file=/u01/app/oracle/dbs/orapwdg1 password=oracle entries=5
拷贝口令文件，参数文件到备库上
[oracle@dg1 oracle]$ scp orapwdg1 initdg1 dg2:/u01/app/oracle/dbs

登陆到备机，重命名口令文件
[oracle@dg2 oracle]$ mv orapwdg1 orapwdg2
下面为采用rman备份创建备库的简要步骤如下，供读者参考
主库操作
RMAN>backup database format '/home/oracle/full_%U.dbf';

$scp 备份文件 dg2:/home/oracle/
创建standby controlfile，一定要在备份后创建
SQL>alter database create standby controlfile as '/home/oracle/st.ctl';
$scp st.ctl dg2:/home/oracle/
备库操作
RMAN>restore database
RMAN>recover database
即可
配置主库的listener.ora和tnsname.ora
listener.ora
[oracle@dg1 admin]$ pwd
/u01/app/oracle/network/admin
[oracle@dg1 admin]$ cat listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = dg1 )
      (ORACLE_HOME = /u01/app/oracle )
#      (PROGRAM = extproc)
      (GLOBAL_DBNAME=dg1 )
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = dg1 )(PORT = 1521))
    )
  )
tnsname.ora
DG1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.103 )(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dg1 )
      (INSTANCE_NAME = dg1 )
    )
  )

DG2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.104 )(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = dg1 )
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )
重启监听程序
[oracle@dg1 admin]$ lsnrctl stop
[oracle@dg1 admin]$ lsnrctl start
利用pfile创建spfile
[oracle@dg1 ~]$ sqlplus / as sysdba;
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jan 25 19:56:11 2011
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to an idle instance.
SQL>  create spfile from pfile;
File created.
至此在主库上的操作告一段落
备库操作
备库参数文件
*.control_files='/u01/app/oradata/dg1/standby.ctl'          ＃＃确保控制文件置于该目录下
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(dg1,dg2)'

*.log_archive_dest_1='LOCATION=/u01/app/oradata/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dg2 '     ＃确保 /u01/app/oradata/archive该路径存在
*.log_archive_dest_2='SERVICE=dg2 LGWR ASYNC  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dg1 '    #同上
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=2
*.log_archive_start=true
*.service_names='dg2 '
*.db_name='dg1 '
*.db_unique_name='dg2 '
*.fal_client='dg2 '
*.fal_server='dg1 '
配置备库上的listener.ora和tnsname.ora
lstener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = dg2 )
      (GLOBAL_DBNAME= dg2 )
      (ORACLE_HOME = /u01/app/oracle )
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.104 )(PORT = 1521))
    )
  )
tnsname.ora和主库上的一模一样，建议直接从主库上拷贝避免出错
重启监听程序
[oracle@dg1 admin]$ lsnrctl stop
[oracle@dg1 admin]$ lsnrctl start
至此备库上的操作完成
测试 DG
依次打开主库和备库
 主库:
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE OPEN;
备库:
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT
FROM SESSION;
在主库上
SQL>archive log list;
SQL>alter system archive log current;
SQL>archive log list;
主库进行日志切换:
SQL>Alter system switch logfile;
然后分别查看主库和备库的 目录下是否产生了同样的归档日志文件。
执行如下SQL
select max(sequence#) from v$archived_log;
select max(sequence#) from v$log_history;
select group#,sequence#,archived,status from v$log;
select name,sequence#,applied from v$archived_log;
select sequence#,applied from v$archived_log;
亦可在主库进行一些DML操作然后在备库上进行验证，具体操作如下
主库
SQL> select * from dg;
        ID NAME
---------- ---------------------------------------------------------------------
         1 czm
SQL> insert into dg values(2,'czm');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from dg;
        ID NAME
---------- ---------------------------------------------------------------------
         1 czm
         2 czm
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
备库上以read only方式打开
SQL> alter database recover managed standby database cancel;
SQL> alter database read only;
SQL> select * from dg;
        ID NAME
---------- ---------------------------------------------------------------------
         1 czm
         2 czm
至此DG顺利搭建成功  

 

参考至：百度文库《ORACLE_10G_DATAGUARD_配置物理STANDBY手记2》
              http://docs.oracle.com/cd/B19306_01/server.102/b14239/log_arch_dest_param.htm

 

              http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10823/log_arch_dest_param.htm#98301
              http://blog.csdn.net/tianlesoftware/article/details/5989638
              http://www.itpub.net/thread-1054909-1-1.html
              http://www.linuxidc.com/Linux/2011-09/42721.htm
本文原创，转载请注明出处、作者
如有错误，欢迎指正
邮箱：[e-mail]