Oracle 10g DATAGUARD 主从数据同步


Oracle 10g DATAGUARD安装 配置 维护 1.1 LINUX 操作系统的安装和配置.................................................................. 1.2 DataGuard 安装环境 ............................................................................... 1.3 DataGuard物理Standby之创建..................................................................... 1.4 MAXIMIZE PERFORMANCE和MAXIMIZE PROTECTION模式转换 ..................... 1.5 主库和备库的switchover配置...................................................................... 1.6 DATAGUARD 维护.......................................................................................... 1.1 LINUX 操作系统的安装和配置 Red Hat Enterprise Linux 5 (RHEL5) 所需内核: 2.6.9-5.EL 或更高版本 验证内核版本: # uname -r 2.6.18-194.el5 #1 SMP Mon Mar 29 22:10:29 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux 其他所需程序包的版本(或更高版本): make-3.81-3.el5 gcc-4.1.2-48.el5 compat-db-4.1.25-9 验证已安装的程序包: # rpm -q make gcc compat-db make-3.81-3.el5 gcc-4.1.2-48.el5 compat-db-4.1.25-9 Red Hat Enterprise Linux 5 (RHEL5) 所需内核: 2.4.21-4.EL 或更高版本 验证内核版本: # uname -r 2.6.18-194.el5 #1 SMP Mon Mar 29 22:10:29 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux 其他所需程序包的版本(或更高版本): make-3.79 binutils-2.11.90.0.8-12 make-3.81-3.el5 gcc-4.1.2-48.el5 compat-db-4.0.14.5 compat-gcc-7.3-2.96.122 compat-gcc-c++-7.3-2.96.122 compat-libstdc++-7.3-2.96.122 compat-libstdc++-devel-7.3-2.96.122 openmotif21-2.1.30-8 setarch-1.3-1 验证已安装的程序包: # rpm -q make binutils gcc compat-db compat-gcc compat-gcc-c++ compat-libstdc++ compat-libstdc++-devel openmotif setarch make-3.81-3.el5 binutils-2.17.50.0.6-14.el5 gcc-4.1.2-48.el5 compat-gcc-7.3-2.96.122 compat-gcc-c++-7.3-2.96.122 compat-libstdc++-7.3-2.96.122 compat-libstdc++-devel-7.3-2.96.122 openmotif-2.2.2-16 setarch-1.3-1 所需内核: 2.4.9-e.25 或更高版本 验证内核版本: # uname -r 2.4.9-e.27smp 其他所需程序包的版本(或更高版本): gcc-2.96.108.1 make-3.79 binutils-2.11.90.0.8-12 openmotif-2.1.30-11 glibc-2.2.4-31 验证已安装的程序包: # rpm -q gcc make binutils openmotif glibc gcc-2.96.2-38 make-3.79.1-8 binutils-2.11.90.0.8-12 openmotif-2.1.30-11 glibc-2.2.4-32.8 所需程序包集: 基本运行时系统 YaST 图形基本系统 Linux 工具 KDE 桌面环境 C/C++ 编译器和工具(默认情况下未选择) 其他所需程序包的版本(或更高版本): make-3.79 binutils-2.11.90.0.8-12 gcc-4.2.2-38 openmotif-2.2.2-124 1.2 DataGuard 安装环境 OS:RHL5 u5(64 位) ORACLE 10G 10.2.0.4 IP:192.168.1.127(primary) 192.168.1.128(standby) ORACLE_SID:lottery ORACLE_HOME:/opt/oracle/product/10.2.0 1.3 DataGuard 物理 Standby 之创建 1.设置主库为 force logging SQL> alter database force logging; 2.设置主库为归档模式: SQL> archive log list SQL> shutdown immediate SQL> startup mount SQL> alter database archivelog; SQL> archive log list 3.检查主机是否有口令文件,如没有需建立 orapwd file='/opt/oracle/product/10.2.0/dbs/orawdlottery.ora' password=sys entries=5 4.为主数据库添加"备用联机日志文件" SQL> alter database add standby logfile group 4 ('/opt/oracle/oradata/lottery/redo04.log') size 50m; SQL> alter database add standby logfile group 5 ('/opt/oracle/oradata/lottery/redo05.log') size 50m; SQL> alter database add standby logfile group 6 ('/opt/oracle/oradata/lottery/redo06.log') size 50m; SQL> alter database add standby logfile group 7 ('/opt/oracle/oradata/lottery/redo07.log') size 50m; 5.修改主库参数文件 SQL> create pfile='/opt/oracle/admin/lottery/pfile/init.ora' from spfile; lottery.__db_cache_size=100663296 lottery.__java_pool_size=4194304 lottery.__large_pool_size=4194304 lottery.__shared_pool_size=54525952 lottery.__streams_pool_size=0 *.audit_file_dest='/opt/oracle/admin/lottery/adump' *.background_dump_dest='/opt/oracle/admin/lottery/bdump' *.compatible='10.2.0.1.0' *.control_files='/opt/oracle/oradata/lottery/control01.ctl','/opt/oracle/oradata/lottery/control02.ctl','/ opt/oracle/oradata/lottery/control03.ctl' *.core_dump_dest='/opt/oracle/admin/lottery/cdump' *.db_block_size=8192 *.db_domain='' *.db_file_multiblock_read_count=16 *.db_name='lottery' *.db_recovery_file_dest='/opt/oracle/flash_recovery_area' *.db_recovery_file_dest_size=2147483648 *.dispatchers='(PROTOCOL=TCP) (SERVICE=lotteryXDB)' *.job_queue_processes=10 *.log_archive_format='%T%S%r.ARC' *.log_archive_max_processes=3 *.open_cursors=300 *.pga_aggregate_target=16777216 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=167772160 *.undo_management='AUTO' *.undo_tablespace='UNDOTBS1' *.user_dump_dest='/opt/oracle/admin/lottery/udump' #add below parameter for standy database *.DB_UNIQUE_NAME='primary' *.log_archive_config='DG_CONFIG=(primary,standby)' *.log_archive_dest_1='location=/opt/oracle/oradata/lottery/archive/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary' *.log_archive_dest_2='SERVICE=standby arch ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby' *.STANDBY_FILE_MANAGEMENT=AUTO *.LOG_ARCHIVE_DEST_STATE_1=ENABLE *.LOG_ARCHIVE_DEST_STATE_2=ENABLE *.FAL_SERVER='standby' *.FAL_CLIENT='primary' 6.主库用 PFILE 建立 SPFILE [oracle@host127 pfile]$ sqlplus '/ as sysdba' SQL> create spfile from pfile='/opt/oracle/admin/lottery/pfile/init.ora'; 7.建立备用库的控制文件 SQL> alter database create standby controlfile as '/tmp/standby_ctl01.ctl'; 8.配置主数据库 listener 及 tnsnames [oracle@host127 admin]$ cat listener.ora SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = lottery) (ORACLE_HOME = /opt/oracle/product/10.2.0) (SID_NAME = lottery) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = host127)(PORT = 1521)) ) ) #加 1522 端口供以后做 switchover SID_LIST_LISTENER1 = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = lottery) (ORACLE_HOME = /opt/oracle/product/10.2.0) (SID_NAME = lottery) ) ) LISTENER1 = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = host127)(PORT = 1522)) ) ) [oracle@host127 admin]$ cat tnsnames.ora #1521 和 1522 端口都能连上主机和备机,这样在做 switchover 时就不需要改这里的设置了 primary = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.128)(PORT = 1522)) (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.127)(PORT = 1522)) ) (CONNECT_DATA = (SID = lottery) ) ) standby = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.127)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.128)(PORT = 1521)) ) (CONNECT_DATA = (SID = lottery) ) ) 9.复制文件到备机 [oracle@host128 ~]cd /opt/oracle/oradata/lottery [oracle@host128 lottery]$rcp host127:/opt/oracle/oradata/lottery/*.dbf . [oracle@host128 lottery]$rcp host127:/tmp/standby_ctl01.ctl . [oracle@host128 lottery]$mv standby_ctl01.ctl control01.ctl [oracle@host128 lottery]$cp control01.ctl control02.ctl [oracle@host128 lottery]$cp control01.ctl control03.ctl 10.复制并修改备机的参数文件 [oracle@host128 ~]cd /opt/oracle/admin/lottery/pfile [oracle@host128 pfile]$rcp host127:/opt/oracle/admin/lottery/pfile/init.ora . 修改为如下: lottery.__db_cache_size=100663296 lottery.__java_pool_size=4194304 lottery.__large_pool_size=4194304 lottery.__shared_pool_size=54525952 lottery.__streams_pool_size=0 *.audit_file_dest='/opt/oracle/admin/lottery/adump' *.background_dump_dest='/opt/oracle/admin/lottery/bdump' *.compatible='10.2.0.1.0' *.control_files='/opt/oracle/oradata/lottery/control01.ctl','/opt/oracle/oradata/lottery/control02.ctl','/ opt/oracle/oradata/lottery/control03.ctl' *.core_dump_dest='/opt/oracle/admin/lottery/cdump' *.db_block_size=8192 *.db_domain='' *.db_file_multiblock_read_count=16 *.db_name='lottery' *.db_recovery_file_dest='/opt/oracle/flash_recovery_area' *.db_recovery_file_dest_size=2147483648 *.dispatchers='(PROTOCOL=TCP) (SERVICE=lotteryXDB)' *.job_queue_processes=10 *.log_archive_format='%T%S%r.ARC' *.log_archive_max_processes=3 *.open_cursors=300 *.pga_aggregate_target=16777216 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=167772160 *.undo_management='AUTO' *.undo_tablespace='UNDOTBS1' *.user_dump_dest='/opt/oracle/admin/lottery/udump' *.DB_UNIQUE_NAME='standby' *.log_archive_config='DG_CONFIG=(primary,standby)' *.log_archive_dest_1='location=/opt/oracle/oradata/lottery/archive/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby' *.log_archive_dest_2='SERVICE=primary arch ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary' *.STANDBY_FILE_MANAGEMENT=AUTO *.LOG_ARCHIVE_DEST_STATE_1=ENABLE *.LOG_ARCHIVE_DEST_STATE_2=ENABLE *.FAL_SERVER='primary' *.FAL_CLIENT='standby' 11.生成备用库密码文件 orapwd file='/opt/oracle/product/10.2.0/dbs/orawdlottery.ora' password=sys entries=5 12.修改备机的 listener 及 tnsnames [oracle@host128 admin]$ cat listener.ora SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = lottery) (ORACLE_HOME = /opt/oracle/product/10.2.0) (SID_NAME = lottery) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = host128)(PORT = 1521)) ) ) #加一个 1522 的端口供以后做 switchover SID_LIST_LISTENER1 = (SID_LIST = ) (SID_DESC = (GLOBAL_DBNAME = lottery) (ORACLE_HOME = /opt/oracle/product/10.2.0) (SID_NAME = lottery) ) ) LISTENER1 = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = host128)(PORT = 1522)) ) ) [oracle@host128 admin]$ cat tnsnames.ora primary = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.128)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.127)(PORT = 1521)) ) (CONNECT_DATA = (SID = lottery) ) ) standby = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.127)(PORT = 1522)) (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.128)(PORT = 1522)) ) (CONNECT_DATA = (SID = lottery) ) ) 13.测试主备之间网络连通 [oracle@host127 admin]$ lsnrctl start [oracle@host127 admin]$ tnsping standby [oracle@host128 admin]$ lsnrctl start [oracle@host128 admin]$ tnsping primary 14.打开备库 SQL> startup mount pfile='/opt/oracle/admin/lottery/pfile/init.ora'; SQL> create spfile from pfile='/opt/oracle/admin/lottery/pfile/init.ora'; SQL> alter database recover managed standby database disconnect from session; 15.打开主库 SQL> startup 16.测试是否 OK 主库: SQL> alter system switch logfile; 从库: SQL> select FIRST_TIME,NEXT_TIME, APPLIED,SEQUENCE# from v$archived_log order by SEQUENCE#; FIRST_TIME NEXT_TIME APP SEQUENCE# ------------------- ------------------- --- ---------- 2010-04-1611:39:33 2010-04-1614:16:39 YES 5 2010-04-1614:16:39 2010-04-1614:21:06 YES 6 1.4 MAXIMIZE PERFORM 和 MAXIMIZE PROTECTION 模 式转换 1.在备机上: SQL> shutdown immediate; SQL> startup mount; SQL> alter database add standby logfile group 4 2 ('/opt/oracle/oradata/lottery/redo04.log') size 50m; SQL> alter database add standby logfile group 5 2 ('/opt/oracle/oradata/lottery/redo05.log') size 50m; SQL> alter database add standby logfile group 6 2 ('/opt/oracle/oradata/lottery/redo06.log') size 50m; SQL> alter database add standby logfile group 7 2 ('/opt/oracle/oradata/lottery/redo07.log') size 50m; 2.在主机上 SQL> shutdown immediate; SQL> startup mount; SQL> alter system set log_archive_dest_2='SERVICE=standby LGWR SYNC AFFIRM 2 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby'; SQL>alter database set standby database to maximize protection; SQL>alter database open; SQL> select protection_mode from v$database; PROTECTION_MODE -------------------- MAXIMUM PROTECTION 3.在备机上 SQL> recover managed standby database disconnect from session; 4.测试一下: 在主机上执行 SQL> alte system switch logfile; 在备机上查看 v$standby_log 视图 SQL> select GROUP#,THREAD#,SEQUENCE#,USED,ARCHIVED,STATUS from v$standby_log; GROUP# THREAD# SEQUENCE# USED ARC STATUS ---------- ---------- ---------- ---------- --- ---------- 4 1 36 59392 YES ACTIVE 5 1 0 512 NO UNASSIGNED 6 0 0 512 YES UNASSIGNED 7 0 0 512 YES UNASSIGNED MAXIMIZE PROTECTION 和 MAXIMIZE AVAILABILITY 模式下,备机不能先关闭,会出 现如下错误 SQL> shutdown immediate; ORA-01154: database busy. Open, close, mount, and dismount not allowed now 1.5 主库和备库的 switchover 配置 注意:Swithover 时只能先从 Primary 切到 Standby,再从 Standby 切到 Primary. 1.准备原主库是否有 standby redo log,上面 1.4 已建好了。 2.准备主库和备库的参数文件,最好就是将两个数据库的参数文件互换,在两台机器上同时 保留主库和备库的参数文件。 [oracle@host128 /]$ cd /opt/oracle/admin/lottery/pfile [oracle@host128 pfile]rcp host127:/opt/oracle/admin/lottery/pfile/init.ora initprimary.ora [oracle@host128 pfile]rcp init.ora host127:/opt/oracle/admin/lottery/pfile/initstandby.ora 3.从 primary 切换到 standby connect / as sysdba SQL> alter database commit to switchover to physical standby with session shutdown; SQL> shutdown SQL> startup mount pfile=/opt/oracle/admin/lottery/pfile/initstandby.ora; SQL> recover managed standby database disconnect; 4.启动新备库端的 Listener (port=1522) [oracle@host127 admin]$ lsnrctl stop [oracle@host127 admin]$ lsnrctl start LISTENER1 5.从 standby 切换到 primary connect / as sysdba SQL> alter database commit to switchover to primary; SQL> shutdown SQL> startup pfile=/opt/oracle/admin/lottery/pfile/initprimary.ora 6.启动新主库端的 Listener (port=1522) [oracle@host128 admin]$ lsnrctl stop [oracle@host128 admin]$ lsnrctl start LISTENER1 1.6 DataGuard 维护 1、在生产库停止 Data Guard 操作: SQL> show parameter log_archive_dest SQL> alter system set log_archive_dest_state_2=defer; 2、在生产库开启 Data Guard 操作: SQL> alter system set log_archive_dest_state_2=enable; 3、在备份库查看生产库传过来的归档应用情况 SQL>SELECT FILE_NAME, SEQUENCE# AS SEQ#, FIRST_CHANGE# AS FCHANGE#,NEXT_CHANGE# AS NCHANGE#, TIMESTAMP,DICT_BEGIN AS BEG, DICT_END AS END,THREAD# AS THR# , APPLIED FROM DBA_LOGSTDBY_LOG ORDER BY THREAD#,SEQUENCE#; 4、在备份库查看应用事件 SQL>SELECT EVENT_TIME,EVENT,XIDUSN, XIDSLT, XIDSQN FROM DBA_LOGSTDBY_EVENTS order by 1 desc; 可跳过某些引起阻塞的 DDL 或 DML 应用,然后手工执行这些应用: SQL>alter database stop logical standby apply; SQL>exec dbms_logstdby.skip_transaction(14,21,517969); SQL>ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE; 5、在备份库跳过特定的 DML 或 DDL 操作 SQL>alter database stop logical standby apply; SQL>execute dbms_logstdby.skip (stmt => 'DML', schema_name => 'BANPING',object_name => 'TABLENAME', proc_name => null); SQL>alter database start logical standby apply; 由于 oracle 的 bug,10203 版本开始应用后会报以下错误: ORA-00604: error occurred at recursive SQL level 1 ORA-01425: escape character must be character string of length 1 此时执行这个 SQL 语句可解决: SQL> update system.logstdby$skip set esc = '\' where esc is NULL; SQL> commit; 6、重新初始化表 SQL>alter database stop logical standby apply; SQL>execute dbms_logstdby.unskip('DML','BANPING','TABLENAME'); SQL>exec dbms_logstdby.instantiate_table('BANPING','TABLENAME','dblink_name'); SQL>alter database start logical standby apply; 注意这里建立的 DBLINK dblink_name 必须是 public 的,否则会报以下错误: ORA-39001: invalid argument value ORA-06512: at "SYS.DBMS_LOGSTDBY", line 577 ORA-06512: at line 1 7、在备库查看日志应用状态和进度: SQL>select * from V$LOGSTDBY_STATE SQL>select * from V$LOGSTDBY_PROGRESS
还剩12页未读

继续阅读

下载pdf到电脑,查找使用更方便

pdf的实际排版效果,会与网站的显示效果略有不同!!

需要 8 金币 [ 分享pdf获得金币 ] 0 人已下载

下载pdf

pdf贡献者

mjhx

贡献于2012-12-20

下载需要 8 金币 [金币充值 ]
亲,您也可以通过 分享原创pdf 来获得金币奖励!
下载pdf