Oracle 11g 单实例到单实例OGG同步实施文档(2)

当所有在Extract 启动之前的开始的交易都完成后,我们就可以使用RMAN 备份生产
端的数据库了。备份数据库的过程中一定要密切监控Extract 进程的状态,保证其一
直正常运行:

5.4.1.源端使用RMAN备份全库 注意:备份过程保证抽取进程状态一直正常。 --备份归档和控制文件如果有三个日志文件组,可以按照以下方式进行切换,如果更多,则对应增加切换的次数即可) --备份数据库 run { allocate channel ch00 type disk maxpiecesize 10g; allocate channel ch01 type disk maxpiecesize 10g; sql 'alter system switch logfile'; sql 'alter system switch logfile'; sql 'alter system switch logfile'; sql 'alter system switch logfile'; crosscheck backupset; delete noprompt expired backupset; backup database format '/u01/backup/bk_%s_%p_%t'; sql 'alter system archive log current'; BACKUP ARCHIVELOG ALL FORMAT '/u01/backup/ARCH_%U'; BACKUP CURRENT CONTROLFILE FORMAT '/u01/backup/bk_controlfile'; release channel ch00; release channel ch01; } --将备份文件拷贝到目标主机上。 cd /u01/backup/ [root@ backup]# scp * 192.168.1.86:/u01/backup/ --在目标端给备份文件授权 [root@ backup]# chown -R oracle:oinstall /u01/backup/ 5.5.恢复目标端数据库 5.5.1.讲数据库启动到nomount SQL> startup nomount ORACLE instance started. Total System Global Area 1060585472 bytes Fixed Size 2260000 bytes Variable Size 905970656 bytes Database Buffers 146800640 bytes Redo Buffers 5554176 bytes 5.5.2.恢复控制文件 RMAN> restore controlfile from '/u01/backup/bk_controlfile'; Starting restore at 07-JUN-18 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=18 device type=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 output file name=/u01/app/oracle/oradata/cndba/control01.ctl output file name=/u01/app/oracle/oradata/cndba/control02.ctl Finished restore at 07-JUN-18 5.5.3.将数据库启动到mount SQL> alter database mount; Database altered. 5.5.4.注册备份集 RMAN> catalog start with '/u01/backup/'; using target database control file instead of recovery catalog searching for all files that match the pattern /u01/backup/ List of Files Unknown to the Database ===================================== File Name: /u01/backup/bk_controlfile Do you really want to catalog the above files (enter YES or NO)? yes cataloging files... cataloging done List of Cataloged Files ======================= File Name: /u01/backup/bk_controlfile 5.5.5.恢复数据库 RMAN> RESTORE DATABASE; Starting restore at 07-JUN-18 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=20 device type=DISK channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/cndba/system01.dbf channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/cndba/users01.dbf channel ORA_DISK_1: reading from backup piece /u01/backup/bk_31_1_978141141 channel ORA_DISK_1: piece handle=/u01/backup/bk_31_1_978141141 tag=TAG20180607T015220 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:55 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/cndba/sysaux01.dbf channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/cndba/undotbs01.dbf channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/cndba/ogg01.dbf channel ORA_DISK_1: reading from backup piece /u01/backup/bk_32_1_978141141 channel ORA_DISK_1: piece handle=/u01/backup/bk_32_1_978141141 tag=TAG20180607T015220 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:45 Finished restore at 07-JUN-18 RMAN> RECOVER DATABASE; executing command: SET until clause Starting recover at 07-JUN-18 using channel ORA_DISK_1 starting media recovery media recovery complete, elapsed time: 00:00:00 Finished recover at 07-JUN-18 5.5.6.使用以下SQL语句查找目标端数据库的SCN号:(得到该SCN号之后,启动复制进程时,使用该SCN号) SQL> SELECT CHECKPOINT_CHANGE#,CHECKPOINT_TIME FROM V$DATAFILE_HEADER; CHECKPOINT_CHANGE# CHECKPOINT_T ------------------ ------------ 1066024 07-JUN-18 1066024 07-JUN-18 1066024 07-JUN-18 1066024 07-JUN-18 1066024 07-JUN-18 a) 如果目标端数据库在“5.4.1”操作之后,又额外追加了源数据库rman备份后的归档日志,导致目标端数据库的SCN号大于“5.4.1”中SCN号。所以必须以目标端数据库当前的SCN为主,从而避免数据重复。 b) 如果目标端数据库在“5.4.1”操作之后,没有额外追加源数据库rman备份后的归档日志,则“5.5.6”中得到的SCN号应该与“5.4.1”中的SCN号相等; --以resetlogs方式打开数据库 SQL> alter database open resetlogs; Database altered. 5.5.7.添加GLOBALS参数文件,创新检查点表 GGSCI (cndba) 7> edit params ./GLOBALS GGSCI (cndba) 8> view params ./GLOBALS --添加以下内容: GGSCHEMA ogg checkpointtable ogg.checkpoint GGSCI (cndba) 9> dblogin userid ogg,password ogg Successfully logged into database. GGSCI (cndba) 10> add checkpointtable ogg.checkpoint Successfully created checkpoint table ogg.checkpoint. 5.5.8.配置replicat复制进程 GGSCI (cndba) 11> add replicat rep1, exttrail ./dirdat/et, checkpointtable ogg.checkpoint REPLICAT added. GGSCI (cndba) 12> edit params rep1 GGSCI (cndba) 13> view params rep1 REPLICAT rep1 setenv (ORACLE_SID=cndba) SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/db_1") USERID ogg,PASSWORD ogg ASSUMETARGETDEFS HANDLECOLLISIONS REPERROR (DEFAULT, DISCARD) DDLERROR DEFAULT DISCARD DDLOPTIONS REPORT DISCARDFILE ./dirrpt/repr1.dsc,append,megabytes 100 MAP test.*, TARGET test.*; 5.5.9.用SCN 启动Replicat [oracle@ ogg]$ cd $OGG_HOME [oracle@ ogg]$ ./ggsci Oracle GoldenGate Command Interpreter for Oracle Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14 Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved. GGSCI (cndba) 15> start rep1, aftercsn 1066024 Sending START request to MANAGER ... REPLICAT REP1 starting GGSCI (cndba) 33> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING REP1 00:00:00 00:00:07 6.检查同步是否正常 6.1.DML测试 --检查目标端数据是否正常 GGSCI (cndba) 4> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING REP1 00:00:00 00:00:06 SQL> select * from test; ID NAME ---------- -------- 1 zhangsan 2 lisi --源端表中添加数据 SQL> insert into test values(3,'wanger'); 1 row created. SQL> commit; Commit complete. SQL> select * from test; ID NAME ---------- -------- 1 zhangsan 2 lisi 3 wanger --目标端查看 SQL> select * from test; ID NAME ---------- -------- 1 zhangsan 2 lisi 3 wanger 可以看到可以同步过来的。 7.开启DDL 7.1.添加参数 GGSCI (cndba) 8> edit params ./GLOBALS GGSCI (cndba) 9> view params ./GLOBALS GGSCHEMA ogg 7.2.在源端执行与DDL同步相关的SQL脚本 切记@marker_setup.sql 一定要在cd $OGG_HOME目录下执行否则会卡主。执行脚本时要输入ogg管理用户,本实例是ogg。 cd /u01/app/oracle/ogg sqlplus / as sysdba grant execute on utl_file to ogg; @marker_setup.sql @ddl_setup.sql @role_setup.sql grant GGS_GGSUSER_ROLE to ogg; @ddl_enable.sql @ddl_pin ogg @marker_status 7.3.源端extract 配置 GGSCI (cndba) 12> edit params ext1 GGSCI (cndba) 16> view params ext1 EXTRACT ext1 SETENV (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK") --此处数据库字符集设为一致 --SETENV (ORACLE_SID = "cndba") SETENV (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1) USERID ogg, PASSWORD ogg THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 20000 EXTTRAIL ./dirdat/et DYNAMICRESOLUTION DDL INCLUDE ALL TABLE test.*; 重启extract进程 GGSCI (cndba) 13> stop ext1 Sending STOP request to EXTRACT EXT1 ... Request processed. GGSCI (cndba) 14> start ext1 Sending START request to MANAGER ... EXTRACT EXT1 starting 7.4.目标端replicat 配置 GGSCI (cndba) 8> edit params rep1 GGSCI (cndba) 9> view params rep1 REPLICAT rep1 setenv (ORACLE_SID=cndba) SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/db_1") USERID ogg,PASSWORD ogg ASSUMETARGETDEFS HANDLECOLLISIONS REPERROR (DEFAULT, DISCARD) DDLERROR DEFAULT DISCARD DDLOPTIONS REPORT DISCARDFILE ./dirrpt/repr1.dsc,append,megabytes 100 MAP test.*, TARGET test.*; ddl include all ddlerror default ignore retryop maxretries 3 retrydelay 5 重启replicat进程 GGSCI (cndba) 10> stop rep1 Sending STOP request to REPLICAT REP1 ... Request processed. GGSCI (cndba) 11> start rep1 Sending START request to MANAGER ... R SQL> insert into test1 values(1,'zhangsan'); 1 row created. SQL> commit; EPLICAT REP1 starting 7.5.DDL测试 --源端: SQL> create table test1 (id number(10) primary key ,name varchar(8)); Table created. Commit complete. --目标端: SQL> desc test1 Name Null? Type ----------------------------------------- -------- ---------------------------- ID NOT NULL NUMBER(10) NAME VARCHAR2(8) SQL> select * from test1; ID NAME ---------- -------- 1 zhangsan

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/8279f4ea07b18d72a3a4d2d3051f62bf.html