搭建ORACLE 10G DATAGUARD示例

2010年因为需要评估DATAGUARD 和 GOLDENGATE某些功能，搭建了环境做了一些功能比较测试
以下就是搭建环境的操作步骤


ORACLE VERSION  10.2.0.3

示例环境：

DATABASE  HOSTNAME     IP        DB_UNIQUE_NAME   ORACLE NET SERVICE NAME
主         DG1     192.168.128.2    DG1                 DG1
备         DG2     192.168.128.3    DG2                 DG2

主库配置
将主库设为归档模式：
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /oracle/archivelog
Oldest online log sequence     25
Next log sequence to archive   27
Current log sequence           27

把主库改成强制归档模式：
SQL> alter database force logging;

Database altered.

在主库添加STANDBY LOG
SQL> alter database add standby logfile group 4
  2  ('/oracle/oradata/DG1/redo04c.log') size 30m;

Database altered.

SQL> alter database add standby logfile group 5
  2   ('/oracle/oradata/DG1/redo05c.log') size 30m;

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


主库初始参数设置
产生主库的PFILE
SQL>CREATE PFILE FROM SPFILE;
然后设置以下参数：
*.db_name='DG1'
*.db_unique_name='DG1'
*.log_archive_config='DG_CONFIG=(DG1,DG2)'
*.log_archive_dest_1='LOCATION=/oracle/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DG1'
*.log_archive_dest_2='SERVICE=DG2 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DG2'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.remote_login_passwordfile='EXCLUSIVE'
*.FAL_SERVER=DG2
*.FAL_CLIENT=DG1
*.DB_FILE_NAME_CONVERT='/oracle/oradata/DG2','/oracle/oradata/DG1'
*.LOG_FILE_NAME_CONVERT='/oracle/oradata/DG2','/oracle/oradata/DG1'
*.STANDBY_FILE_MANAGEMENT=AUTO
*.log_archive_max_processes=5

备份主库
SQL> SHUTDOWN IMMEDIATE;
使用OS 命令将所有数据文件 LOG 文件备份并COPY到备库

产生备库控制文件
SQL>STARTUP MOUNT;
SQL>ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/TMP/DG2.CTL';
SQL>ALTER DATABASE OPEN;



在备机建立相应文件夹

备库设置：
建立相关文件夹
[oracle@DG2 ~]$ mkdir -p /oracle/oradata
[oracle@DG2 ~]$ mkdir -p /oracle/archivelog
[oracle@DG2 ~]$ mkdir -p /oracle/app/admin/DG2/adump
[oracle@DG2 ~]$ mkdir -p /oracle/app/admin/DG2/bdump
[oracle@DG2 ~]$ mkdir -p /oracle/app/admin/DG2/cdump
[oracle@DG2 ~]$ mkdir -p /oracle/app/admin/DG2/udump

备库初始化参数设置：
将主库的PFILE COPY到备库$ORACLE_HOME/dbs下
将从主库产生的STANDBY 控制文件COPY到相应目录
更改如下：
DG2.__db_cache_size=75497472
DG2.__java_pool_size=4194304
DG2.__large_pool_size=4194304
DG2.__shared_pool_size=79691776
DG2.__streams_pool_size=0
*.audit_file_dest='/oracle/app/admin/DG2/adump'
*.background_dump_dest='/oracle/app/admin/DG2/bdump'
*.core_dump_dest='/oracle/app/admin/DG2/cdump'
*.user_dump_dest='/oracle/app/admin/DG2/udump'
*.control_files='/oracle/oradata/DG2/control01.ctl','/oracle/oradata/DG2/control02.ctl','/oracle/oradata/DG2/control03.ctl'
*.db_name='DG1'
*.db_unique_name='DG2'
*.log_archive_config='DG_CONFIG=(DG1,DG2)'
*.log_archive_dest_1='LOCATION=/oracle/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DG2'
*.log_archive_dest_2='SERVICE=DG1 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DG1'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.remote_login_passwordfile='EXCLUSIVE'
*.FAL_SERVER=DG1
*.FAL_CLIENT=DG2
*.DB_FILE_NAME_CONVERT='/oracle/oradata/DG1','/oracle/oradata/DG2'
*.LOG_FILE_NAME_CONVERT='/oracle/oradata/DG1','/oracle/oradata/DG2'
*.STANDBY_FILE_MANAGEMENT=AUTO
*.log_archive_max_processes=5

在备库创建密码文件
orapwd file=orapwDG2 entries=5 password=xhl

创建备库SPFILE,在备库连接到IDLE INSTANCE后
SQL>CREATE SPFILE FROM PFILE;


主 备 库都要设置监听与服务
如下：
备库
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = DG2)
      (ORACLE_HOME = /oracle/app/product/10.2.0/db_1)
      (SID_NAME = DG2)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = DG2)(PORT = 1521))
  )

DG2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.128.3)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = DG2)
    )
  )

DG1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.128.2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = DG1)
    )
  )

主库：
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = DG1)
      (ORACLE_HOME = /oracle/app/product/10.2.0/db_1)
      (SID_NAME = DG1)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = DG1)(PORT = 1521))
  )

DG2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.128.3)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = DG2)
    )
  )

DG1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.128.2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = DG1)
    )
  )

重启监听

 
启动备库
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;


验证是否成功
在主库
SQL> ALTER SYSTEM SWITCH LOGFILE;
在备库查询：
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME  FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

查看ALTER LOG 
Primary database is in MAXIMUM PERFORMANCE mode
RFS[3]: Successfully opened standby log 4: '/oracle/oradata/DG2/redo04c.log'
Tue Feb 23 15:09:30 2010
Primary database is in MAXIMUM PERFORMANCE mode
RFS[3]: Successfully opened standby log 4: '/oracle/oradata/DG2/redo04c.log'
Tue Feb 23 15:09:42 2010
Primary database is in MAXIMUM PERFORMANCE mode
RFS[3]: Successfully opened standby log 4: '/oracle/oradata/DG2/redo04c.log'
Primary database is in MAXIMUM PERFORMANCE mode
RFS[3]: Successfully opened standby log 4: '/oracle/oradata/DG2/redo04c.log'

至此PHYSICAL DATAGUARD 设置完毕

下面来设置LOGICAL DATAGUARD

备库
SELECT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_NOT_UNIQUE
 WHERE (OWNER, TABLE_NAME) NOT IN
 (SELECT DISTINCT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED)
 AND BAD_COLLUMN = 'Y';

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;


主库
SQL> EXECUTE DBMS_LOGSTDBY.BUILD;

备库
SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY DG2;

SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY DG2;
ALTER DATABASE RECOVER TO LOGICAL STANDBY DG2
*
ERROR at line 1:
ORA-16254: change db_name to DG2 in the client-side parameter file (pfile)


[oracle@DG2 dbs]$ rm orapwDG2
[oracle@DG2 dbs]$ orapwd file=orapwDG2 entries=5 password=xhl


SQL> SHUTDOWN 
SQL> STARTUP MOUNT;

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/oracle/archivelogDG2/ VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DG2' SCOPE=BOTH;

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='LOCATION=/oracle/archivelog/ VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=DG2' SCOPE=BOTH

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ENABLE SCOPE=BOTH;


SQL> ALTER DATABASE OPEN RESETLOGS;

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY IMMEDIATE;