当所有在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_GG
SUSER_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