搭建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 ModeAutomatic archival             EnabledArchive destination            /oracle/archivelogOldest online log sequence     25Next log sequence to archive   27Current log sequence           27把主库改成强制归档模式：SQL> alter database force logging;Database altered.在主库添加STANDBY LOGSQL> 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主库初始参数设置产生主库的PFILESQL>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=75497472DG2.__java_pool_size=4194304DG2.__large_pool_size=4194304DG2.__shared_pool_size=79691776DG2.__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 modeRFS[3]: Successfully opened standby log 4: '/oracle/oradata/DG2/redo04c.log'Tue Feb 23 15:09:30 2010Primary database is in MAXIMUM PERFORMANCE modeRFS[3]: Successfully opened standby log 4: '/oracle/oradata/DG2/redo04c.log'Tue Feb 23 15:09:42 2010Primary database is in MAXIMUM PERFORMANCE modeRFS[3]: Successfully opened standby log 4: '/oracle/oradata/DG2/redo04c.log'Primary database is in MAXIMUM PERFORMANCE modeRFS[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=xhlSQL> 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=BOTHSQL> 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;