基于同一主机配置 Oracle 11g Data Guard

--演示环境
[oracle@linux3 ~]$ cat /etc/issue
Enterprise Linux Enterprise Linux Server release 5.5 (Carthage)
Kernel \r on an \m

[oracle@linux3 ~]$ sqlplus -v

SQL*Plus: Release 11.2.0.1.0 Production

--主库：sybo3
--备库: sybo5
--配置物理standby，使用最佳性能模式

a、主库前期准备
--查看主库的归档模式，如果为非归档模式应先切换到归档模式
--关于如何切换到归档模式可以参考：Oracle 归档日志 http://blog.csdn.net/robinson_0612/article/details/5784713
sys@SYBO3> select name,log_mode from v$database;

NAME      LOG_MODE
--------- ------------
SYBO3     ARCHIVELOG

--为备库准备pfile文件 
sys@SYBO3> create pfile=/u01/oracle/db_1/dbs/initsybo5.ora from spfile;

--使主库处于强制日志模式
sys@SYBO3> alter database force logging;

sys@SYBO3> select database_role from v$database;

DATABASE_ROLE
----------------
PRIMARY

--为主库添加standby redo log，简要描述一下standby redo log的作用
--实际上就是与主库接收到的重做日志相对应，也就是说备库调用RFS进程将从主库接收到的重做日志按顺序写入到standby logfile
--在主库创建standby logfile是便于发生角色转换后备用
--sandby redo log创建原则：
--a)、确保standby redo log的大小与主库online redo log的大小一致  
--b)、如主库为单实例数据库：standby redo log组数=主库日志组总数+1
--c)、如果主库是RAC数据库：standby redo log组数=(每线程的日志组数+1)*最大线程数
--d)、不建议复用standby redo log，避免增加额外的I/O以及延缓重做传输

sys@SYBO3> alter database add standby logfile ('/u01/database/sybo3/redo/standby_redo01.log') size 50m;

sys@SYBO3> alter database add standby logfile ('/u01/database/sybo3/redo/standby_redo02.log') size 50m;

sys@SYBO3> alter database add standby logfile ('/u01/database/sybo3/redo/standby_redo03.log') size 50m;

sys@SYBO3> alter database add standby logfile ('/u01/database/sybo3/redo/standby_redo04.log') size 50m;


b、修改主库参数文件
--使用下面的命令修改主库参数(此时主库应当使用spfile启动参数)
[oracle@linux3 ~]$ more ch_sp_sybo3.sql 
--Add below item when DB acts as primary role
alter system set db_unique_name='sybo3' scope=spfile;
alter system set log_archive_config='DG_CONFIG=(sybo3,sybo5)';
alter system set log_archive_dest_1='LOCATION=/u01/database/sybo3/arch db_unique_name=sybo3 valid_for=(ALL_LOGFILES,ALL_ROLES)';
alter system set log_archive_dest_2='SERVICE=sybo5 ASYNC db_unique_name=sybo5 valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE)';
alter system set log_archive_dest_state_1=enable;
alter system set log_archive_dest_state_2=enable;
alter system set log_archive_max_processes=4;
alter system set remote_login_passwordfile='EXCLUSIVE' scope=spfile;

--Add below item when DB turn to standby role
alter system set db_file_name_convert='sybo5','sybo3' scope=spfile;
alter system set log_file_name_convert='sybo5','sybo3' scope=spfile;
alter system set standby_file_management='AUTO';
alter system set fal_server='sybo5';
alter system set fal_client='sybo3';

sys@SYBO3> @ch_sp_sybo3
sys@SYBO3> shutdown immediate;


c、配置主备库监听
--为主库和备库配置监听，整个DG的redo传输服务，都依赖于Oracle Net，因此需要为主备库配置监听
--配置方法多种多样，可用netmgr，netca，以及直接编辑listener.ora 与tnsnames.ora文件
--下面是配置之后的listener.ora 与tnsnames.ora文件内容
[oracle@linux3 ~]$ more /u01/oracle/db_1/network/admin/listener.ora 
# listener.ora Network Configuration File: /u01/oracle/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER_SYBO5 =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = sybo5.orasrv.com)
      (ORACLE_HOME = /u01/oracle/db_1)
      (SID_NAME = sybo5)
    )
  )

SID_LIST_LISTENER_SYBO3 =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = sybo3.orasrv.com)
      (ORACLE_HOME = /u01/oracle/db_1)
      (SID_NAME = sybo3)
    )
  )

LISTENER_SYBO5 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = linux3.orasrv.com)(PORT = 1532))
  )

ADR_BASE_LISTENER_SYBO5 = /u01/oracle

LISTENER_SYBO3 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = linux3.orasrv.com)(PORT = 1531))
  )

ADR_BASE_LISTENER_SYBO3 = /u01/oracle  

[oracle@linux3 ~]$ more /u01/oracle/db_1/network/admin/tnsnames.ora   
# tnsnames.ora Network Configuration File: /u01/oracle/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

SYBO5 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.7.25)(PORT = 1532))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = SYBO5.ORASRV.COM)
    )
  )

SYBO3 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.7.25)(PORT = 1531))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = SYBO3.ORASRV.COM)
    )
  )

--启动监听器并测试
[oracle@linux3 ~]$ lsnrctl start LISTENER_SYBO3
[oracle@linux3 ~]$ lsnrctl start LISTENER_SYBO5
[oracle@linux3 ~]$ tnsping sybo3
[oracle@linux3 ~]$ tnsping sybo5


d、为备库创建目录
--为备库创建相应的文件夹
[oracle@linux3 database]$ more sybo5.sh 
#!/bin/sh

mkdir -p /u01/database
mkdir -p /u01/database/sybo5/adump
mkdir -p /u01/database/sybo5/controlf
mkdir -p /u01/database/sybo5/fra
mkdir -p /u01/database/sybo5/oradata
mkdir -p /u01/database/sybo5/redo
mkdir -p /u01/database/sybo5/dpdump
mkdir -p /u01/database/sybo5/pfile
mkdir -p /u01/database/sybo5/arch
[oracle@linux3 database]$ ./sybo5.sh 


e、配置备库密码文件及参数文件
--由于要求主库与备库sys使用相同的密码，在此处，我们直接复制了主库的密码文件到备库
[oracle@linux3 ~]$ cp $ORACLE_HOME/dbs/orapwsybo3 $ORACLE_HOME/dbs/orapwsybo5

[oracle@linux3 ~]$ sed -i 's/sybo3/sybo5/g' $ORACLE_HOME/dbs/initsybo5.ora
---注意db_name的设置还是使用原来的sybo3，即修改回db_name='sybo3'，同一DG中应设置相同的db_name

--下面是配置之后的备库参数文件，仅列出修改部分，如果原来pfile文件中有相同的条目，可以将其之前的注释或删除
[oracle@linux3 ~]$ tail -20 $ORACLE_HOME/dbs/initsybo5.ora
db_unique_name=sybo5
log_archive_config='DG_CONFIG=(sybo3,sybo5)'
log_archive_dest_1='LOCATION=/u01/database/sybo5/arch db_unique_name=sybo5 valid_for=(ALL_LOGFILES,ALL_ROLES)'
log_archive_dest_2='SERVICE=sybo3 ASYNC db_unique_name=sybo3 valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE)'  
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
db_file_name_convert='sybo3','sybo5'
log_file_name_convert='sybo3','sybo5'
standby_file_management='AUTO'
fal_server='sybo3'
fal_client='sybo5'
log_archive_max_processes=4
remote_login_passwordfile='EXCLUSIVE'


f、复制数据文件，日志文件，备份控制文件到备库
--对于从主库克隆standby有多种方法，而且Oracle 11g支持从ative database直接克隆数据库
--由于在同一主机，因此此次操作直接使用冷备方式将数据及日志文件复制到备库目录
[oracle@linux3 ~]$ cp /u01/database/sybo3/oradata/* /u01/database/sybo5/oradata/
[oracle@linux3 ~]$ cp /u01/database/sybo3/redo/* /u01/database/sybo5/redo/       

--启动主库到mount状态
sys@SYBO3> startup mount;

--为主库生成控制文件,注，对于配置standby，不能直接使用copy方式复制控制文件到备库
sys@SYBO3> alter database create standby controlfile as '/u01/database/sybo5/controlf/control01.ctl';

sys@SYBO3> ho cp /u01/database/sybo5/controlf/control01.ctl /u01/database/sybo5/controlf/control02.ctl    

--Author : Robinson Cheng
--Blog   : http://blog.csdn.net/robinson_0612

--打开主库
sys@SYBO3> alter database open;


g、启动备库到mount状态并校验结果
[oracle@linux3 ~]$ export ORACLE_SID=sybo5
[oracle@linux3 ~]$ sqlplus / as sysdba
sys@SYBO5> startup mount pfile=/u01/oracle/db_1/dbs/initsybo5.ora  

--为备库生成spfile文件
sys@SYBO5> create spfile from pfile;

sys@SYBO5> show parameter instance_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      sybo5

sys@SYBO5> select name,open_mode,database_role,protection_mode from v$database;

NAME      OPEN_MODE            DATABASE_ROLE    PROTECTION_MODE
--------- -------------------- ---------------- --------------------
SYBO3     MOUNTED              PHYSICAL STANDBY MAXIMUM PERFORMANCE

--在主库端进行日志切换
sys@SYBO3> ALTER SYSTEM SWITCH LOGFILE;

sys@SYBO3> ALTER SYSTEM SWITCH LOGFILE;

--在备库端启动redo apply
sys@SYBO5> alter database recover managed standby database disconnect from session;

--查看日志被apply的结果
sys@SYBO5> select sequence#, first_time, next_time,applied from v$archived_log order by sequence#;

SEQUENCE# FIRST_TIM NEXT_TIME APPLIED
---------- --------- --------- ---------
       113 12-AUG-13 12-AUG-13 YES
       114 12-AUG-13 12-AUG-13 YES
       115 12-AUG-13 12-AUG-13 YES
       116 12-AUG-13 12-AUG-13 YES
       117 12-AUG-13 12-AUG-13 YES
        
--查看主库端
sys@SYBO3> select * from (
  2  select sequence#, first_time, next_time,applied from v$archived_log order by sequence# desc)
  3  where rownum<5;

 SEQUENCE# FIRST_TIM NEXT_TIME APPLIED
---------- --------- --------- ---------
       117 12-AUG-13 13-AUG-13 YES
       116 12-AUG-13 12-AUG-13 YES
       115 12-AUG-13 12-AUG-13 YES
       114 12-AUG-13 12-AUG-13 YES