【OracleDatabase】GoldenGate（rac-single）
源端数据库配置
[oracle@king01 ~]$ sqlplus / as sysdba
SQL> create tablespace goldengate datafile '+DATAFILE' size 1024M;
Tablespace created.
SQL> create user ggs identified by ggs default tablespace goldengate;
User created.
SQL> grant dba to ggs;
Grant succeeded.
SQL> select log_mode from v$database;
LOG_MODE
------------------------------------
ARCHIVELOG
SQL> alter database force logging;
Database altered.
SQL> select force_logging from v$database;
FORCE_LOGGING
------------------------------
YES
SQL> alter database add supplemental log data;
Database altered.
SQL> col supplemental_log_data_min for a30
SQL> select supplemental_log_data_min from v$database;
SUPPLEMENTAL_LOG_DATA_MIN
------------------------------
YES
SQL> alter system set enable_goldengate_replication=true;
System altered.
SQL> alter system archive log current;
System altered.
源端安装OGG
[oracle@king01 ~]$ vi .bash_profile
export OGG_HOME=/home/ogg
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export PATH=$OGG_HOME:$PATH
[oracle@king01 ~]$ source .bash_profile
[oracle@king01 ~]$ mkdir -p /home/ogg
[oracle@king01 ~]$ unzip fbo_ggs_Linux_x64_shiphome
[oracle@king01 ~]$ cd fbo_ggs_Linux_x64_shiphome/Disk1
[oracle@king01 Disk1]$ ./runInstaller
[oracle@king01 ~]$ cd /home/ogg
[oracle@king01 ogg]$ ./ggsci
创建目录
GGSCI (king01) 1> create subdirs
Creating subdirectories under current directory /home/ogg
Parameter files /home/ogg/dirprm: created
Report files /home/ogg/dirrpt: created
Checkpoint files /home/ogg/dirchk: created
Process status files /home/ogg/dirpcs: created
SQL script files /home/ogg/dirsql: created
Database definitions files /home/ogg/dirdef: created
Extract data files /home/ogg/dirdat: created
Temporary files /home/ogg/dirtmp: created
Credential store files /home/ogg/dircrd: created
Masterkey wallet files /home/ogg/dirwlt: created
Dump files /home/ogg/dirdmp: created
源端MANAGER进程组
GGSCI (king01) 2> edit params mgr
PORT 7839
DYNAMICPORTLIST 7840-7939
AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3
PURGEOLDEXTRACTS ./dirdat/*,USECHECKPOINTS, MINKEEPDAYS 7
LAGREPORTHOURS 1
LAGINFOMINUTES 3
LAGCRITICALMINUTES 10
GGSCI (king01) 3> start mgr
Manager started.
GGSCI (king01) 4> info mgr
Manager is running (IP port king01.7839, Process ID 3243).
源端EXTRACT进程组
GGSCI (king01) 5> dblogin userid ggs,password ggs
Successfully logged into database.
GGSCI (king01 as ggs@king) 6> add trandata soe.*
GGSCI (king01 as ggs@king) 7> add extract ext_soe, tranlog, begin now, threads 2
EXTRACT added.
GGSCI (king01 as ggs@king) 8> add exttrail ./dirdat/st,extract ext_soe,megabytes 100
EXTTRAIL added.
GGSCI (king01 as ggs@king) 9> edit params ext_soe
EXTRACT ext_soe
SETENV (NLS_LANG = "AMERICAN_AMERICA.UTF8")
USERID ggs, PASSWORD ggs
REPORTCOUNT EVERY 30 MINUTES, RATE
REPORTROLLOVER AT 02:00
TRANLOGOPTIONS DBLOGREADER
TRANLOGOPTIONS EXCLUDEUSER ggt
DBOPTIONS ALLOWUNUSEDCOLUMN
EXTTRAIL ./dirdat/st
DISCARDFILE ./dirrpt/ext_soe.dsc,APPEND,MEGABYTES 5
TABLEEXCLUDE SOE.ORDERENTRY_METADATA
TABLE SOE.*;
GGSCI (king01 as ggs@king) 10> start ext_soe
Sending START request to MANAGER ...
EXTRACT EXT_SOE starting
GGSCI (king01 as ggs@king) 11> info ext_soe
EXTRACT EXT_SOE Last Started 2018-11-08 17:05 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:03 ago)
Process ID 7172
Log Read Checkpoint Oracle Redo Logs
2018-11-09 08:53:58 Thread 1, Seqno 74, RBA 27958272
SCN 0.1517400 (1517400)
Log Read Checkpoint Oracle Redo Logs
2018-11-09 08:54:01 Thread 2, Seqno 58, RBA 19046912
SCN 0.1517403 (1517403)
源端PUMP进程组
GGSCI (king01 as ggs@king) 12> add extract pmp_soe, exttrailsource ./dirdat/st
EXTRACT added.
GGSCI (king01 as ggs@king) 13> add rmttrail ./dirdat/rt,EXTRACT pmp_soe,megabytes 100
RMTTRAIL added.
GGSCI (king01 as ggs@king) 14> edit params pmp_soe
EXTRACT pmp_soe
PASSTHRU
RMTHOST 192.168.1.203, MGRPORT 7839, COMPRESS
RMTTRAIL ./dirdat/rt
TABLE SOE.*;
GGSCI (king01 as ggs@king) 15> start pmp_soe
Sending START request to MANAGER ...
EXTRACT PMP_SOE starting
GGSCI (king01 as ggs@king) 16> info pmp_soe
EXTRACT PMP_SOE Last Started 2018-11-08 17:01 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:07 ago)
Process ID 6690
Log Read Checkpoint File /home/ogg/dirdat/st000000000
First Record RBA 1382
GGSCI (king01 as ggs@king) 17> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT_SOE 00:00:00 00:00:01
EXTRACT RUNNING PMP_SOE 00:00:00 00:00:04
源端备份数据库
[oracle@king01 ~]$ mkdir backup
[oracle@king01 ~]$ rman target /
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/home/oracle/backup/%F';
RMAN> RUN {
BACKUP INCREMENTAL LEVEL=0 TAG 'FULL_BACKUP' DATABASE
FORMAT '/home/oracle/backup//soe_full_incr_%s_%p_%T'
PLUS ARCHIVELOG
FORMAT '/home/oracle/backup/soe_arch_%s_%p_%T' delete all input;
DELETE NOPROMPT OBSOLETE;
CROSSCHECK BACKUP;
DELETE NOPROMPT EXPIRED BACKUP;
}
[oracle@king01 ~]$ sqlplus / as sysdba
SQL> select to_char(current_scn) from v$database;
TO_CHAR(CURRENT_SCN)
----------------------------------------
1419545
SQL> alter system archive log current ;
System altered.
SQL> alter system archive log current ;
System altered.
SQL> alter system archive log current ;
System altered.
[oracle@king01 ~]$ rman target /
RMAN> backup archivelog all FORMAT '/home/oracle/backup/soe_arch_%s_%p_%T';
[oracle@king01 ~]$ cd /home/oracle/backup
[oracle@king01 backup]$ scp * 192.168.1.203:/home/oracle/backup
创新互联公司主要从事成都网站设计、成都网站建设、外贸网站建设、网页设计、企业做网站、公司建网站等业务。立足成都服务威信,十年网站建设经验,价格优惠、服务专业,欢迎来电咨询建站服务:18980820575
目标端恢复数据库
[oracle@king03 ~]$ mkdir -p /home/oracle/admin/kingdb/adump
[oracle@king03 ~]$ mkdir -p /home/oracle/oradata/kingdb/
[oracle@king03 ~]$ mkdir -p /home/oracle/fast_recovery_area/kingdb
[oracle@king03 ~]$ cd $ORACLE_HOME/dbs
[oracle@king03 dbs]$ vi initkingdb.ora
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/home/oracle/oradata/kingdb/control01.ctl','/home/oracle/fast_recovery_area/kingdb/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='kingdb'
*.db_recovery_file_dest='/home/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=21474836480
*.diagnostic_dest='/home/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=kingdbXDB)'
*.enable_goldengate_replication=TRUE
*.job_queue_processes=0
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=536870912
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=2147483648
*.undo_tablespace='UNDOTBS1'
[oracle@king03 dbs]$ sqlplus / as sysdba
SQL> create spfile from pfile;
File created.
[oracle@king03 dbs]$ orapwd file=/home/oracle/product/11.2.0/db_1/dbs/orapwkingdb password=oracle entries=5 force=y
[oracle@king03 ~]$ rman target /
RMAN> startup nomount
Total System Global Area 1068937216 bytes
Fixed Size 2260088 bytes
Variable Size 322962312 bytes
Database Buffers 687865856 bytes
Redo Buffers 55848960 bytes
RMAN> set DBID=4126740520
executing command: SET DBID
RMAN> run {
set CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/home/oracle/backup/%F';
restore controlfile from autobackup;
}
RMAN> alter database mount;
RMAN> run {
set until scn 1419545;
SET NEWNAME FOR DATAFILE 1 to '/home/oracle/oradata/kingdb/system.dbf';
SET NEWNAME FOR DATAFILE 2 to '/home/oracle/oradata/kingdb/sysaux.dbf';
SET NEWNAME FOR DATAFILE 3 to '/home/oracle/oradata/kingdb/undotbs1.dbf';
SET NEWNAME FOR DATAFILE 4 to '/home/oracle/oradata/kingdb/users.dbf';
SET NEWNAME FOR DATAFILE 5 to '/home/oracle/oradata/kingdb/undotbs2.dbf';
SET NEWNAME FOR DATAFILE 6 to '/home/oracle/oradata/kingdb/soe.dbf';
SET NEWNAME FOR DATAFILE 7 to '/home/oracle/oradata/kingdb/goldengate.dbf';
SET NEWNAME FOR DATAFILE 8 to '/home/oracle/oradata/kingdb/tpcc.dbf';
SET NEWNAME FOR DATAFILE 9 to '/home/oracle/oradata/kingdb/tpch.dbf';
SET NEWNAME FOR TEMPFILE 1 to '/home/oracle/oradata/kingdb/temp01.dbf';
RESTORE DATABASE;
SWITCH DATAFILE ALL;
SWITCH TEMPFILE ALL;
recover database;
}
[oracle@king03 ~]$ sqlplus / as sysdba
SQL> alter database rename file '+DATAFILE/kingdb/onlinelog/group_1.261.991491245' to '/home/oracle/oradata/kingdb/redo1_a.log';
SQL> alter database rename file '+FRA/kingdb/onlinelog/group_1.257.991491249' to '/home/oracle/oradata/kingdb/redo1_b.log';
SQL> alter database rename file '+DATAFILE/kingdb/onlinelog/group_2.262.991491251' to '/home/oracle/oradata/kingdb/redo2_a.log';
SQL> alter database rename file '+FRA/kingdb/onlinelog/group_2.258.991491255' to '/home/oracle/oradata/kingdb/redo2_b.log';
SQL> alter database rename file '+DATAFILE/kingdb/onlinelog/group_3.265.991491457' to '/home/oracle/oradata/kingdb/redo3_a.log';
SQL> alter database rename file '+FRA/kingdb/onlinelog/group_3.259.991491461' to '/home/oracle/oradata/kingdb/redo3_b.log';
SQL> alter database rename file '+DATAFILE/kingdb/onlinelog/group_4.266.991491465' to '/home/oracle/oradata/kingdb/redo4_a.log';
SQL> alter database rename file '+FRA/kingdb/onlinelog/group_4.260.991491469' to '/home/oracle/oradata/kingdb/redo4_b.log';
SQL> alter database open resetlogs;
SQL> SQL> select thread#,status,enabled from v$thread;
THREAD# STATUS ENABLED
---------- ------------------ ------------------------
1 OPEN PUBLIC
2 CLOSED PUBLIC
SQL> alter database disable thread 2;
Database altered.
SQL> select thread#,status,enabled from v$thread;
THREAD# STATUS ENABLED
---------- ------------------ ------------------------
1 OPEN PUBLIC
2 CLOSED DISABLED
目标端数据库设置
[oracle@king03 ~]$ sqlplus / as sysdba
SQL> select 'alter table '||owner||'.'||table_name||' disable constraint '||constraint_name||';'
from dba_constraints
where constraint_type in ('R') and
owner in('SOE')
order by status,owner;
'ALTERTABLE'||OWNER||'.'||TABLE_NAME||'DISABLECONSTRAINT'||CONSTRAINT_NAME||';'
--------------------------------------------------------------------------------
alter table SOE.ADDRESSES disable constraint ADD_CUST_FK;
alter table SOE.ORDERS disable constraint ORDERS_CUSTOMER_ID_FK;
alter table SOE.ORDER_ITEMS disable constraint ORDER_ITEMS_PRODUCT_ID_FK;
alter table SOE.INVENTORIES disable constraint INVENTORIES_PRODUCT_ID_FK;
alter table SOE.ORDER_ITEMS disable constraint ORDER_ITEMS_ORDER_ID_FK;
alter table SOE.INVENTORIES disable constraint INVENTORIES_WAREHOUSES_FK;
6 rows selected.
SQL> select 'alter trigger '||owner||'.'||trigger_name||' disable;'
from dba_triggers
where owner in('SOE')
order by status,owner;
no rows selected
SQL> drop user ggs cascade;
User dropped.
SQL> create user ggt identified by ggt default tablespace goldengate;
User created.
SQL> grant dba to ggt;
Grant succeeded.
目标端安装OGG
[oracle@king03 ~]$ vi .bash_profile
export OGG_HOME=/home/ogg
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export PATH=$OGG_HOME:$PATH
[oracle@king03 ~]$ source .bash_profile
[oracle@king03 ~]$ mkdir -p /home/ogg
[oracle@king03 ~]$ unzip fbo_ggs_Linux_x64_shiphome
[oracle@king03 ~]$ cd fbo_ggs_Linux_x64_shiphome/Disk1
[oracle@king03 Disk1]$ ./runInstaller
[oracle@king03 Disk1]$ cd /home/ogg
[oracle@king03 ogg]$ ./ggsci
创建目录
GGSCI (king03) 1> create subdirs
Creating subdirectories under current directory /home/ogg
Parameter files /home/ogg/dirprm: created
Report files /home/ogg/dirrpt: created
Checkpoint files /home/ogg/dirchk: created
Process status files /home/ogg/dirpcs: created
SQL script files /home/ogg/dirsql: created
Database definitions files /home/ogg/dirdef: created
Extract data files /home/ogg/dirdat: created
Temporary files /home/ogg/dirtmp: created
Credential store files /home/ogg/dircrd: created
Masterkey wallet files /home/ogg/dirwlt: created
Dump files /home/ogg/dirdmp: created
目标端MANAGER进程组
GGSCI (king03) 2> edit params mgr
PORT 7839
DYNAMICPORTLIST 7840-7939
AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3
PURGEOLDEXTRACTS ./dirdat/*,USECHECKPOINTS, MINKEEPDAYS 7
LAGREPORTHOURS 1
LAGINFOMINUTES 3
LAGCRITICALMINUTES 10
GGSCI (king03) 3> start mgr
Manager started.
GGSCI (king03) 4> info mgr
Manager is running (IP port king03.7839, Process ID 13650).
目标端REPLICAT进程组
GGSCI (king03) 5> edit params ./GLOBALS
checkpointtable ggt.chkpt
GGSCI (king03) 6> dblogin userid ggt,password ggt
Successfully logged into database.
GGSCI (king03 as ggt@king) 7> add checkpointtable
No checkpoint table specified. Using GLOBALS specification (ggt.chkpt)...
Successfully created checkpoint table ggt.chkpt.
GGSCI (king03 as ggt@king) 8> add replicat rep_soe,exttrail ./dirdat/rt
REPLICAT added.
GGSCI (king03 as ggt@king) 9> edit param rep_soe
REPLICAT rep_soe
SETENV (NLS_LANG = "AMERICAN_AMERICA.UTF8")
USERID ggt, PASSWORD ggt
REPORTCOUNT EVERY 30 MINUTES, RATE
REPORTROLLOVER AT 02:00
DBOPTIONS SUPPRESSTRIGGERS
REPERROR DEFAULT, ABEND
DISCARDFILE ./dirrpt/rep_soe.dsc,APPEND,MEGABYTES 5
ASSUMETARGETDEFS
ALLOWNOOPUPDATES
MAP SOE.*, TARGET SOE.*;
GGSCI (king03 as ggt@king) 10> start replicat rep_soe, aftercsn 1419545
Sending START request to MANAGER ...
REPLICAT REP_SOE starting
GGSCI (king03 as ggt@king) 11> info rep_soe
REPLICAT REP_SOE Last Started 2018-11-08 17:10 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:03 ago)
Process ID 28121
Log Read Checkpoint File /home/ogg/dirdat/rt000000000
First Record RBA 0
GGSCI (king03 as ggt@king) 12> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP_SOE 00:00:00 00:00:01
当前名称：【OracleDatabase】GoldenGate（rac-single）
新闻来源：http://scyanting.com/article/pjjpjj.html
