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;
搭建ORACLE 10G DATAGUARD示例
最新推荐文章于 2021-04-09 07:32:18 发布