此贴接上贴OGG搭建帖子:
[原创]同平台同数据库REDHAT+ORACLE11203+OGG的搭建过程[上][原创]
4.8.源端数据库备份并传到目标端:
[oracle@oggs ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Sat Nov 8 12:21:50 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: EDISON (DBID=1741909597)
RMAN> backup database format '/s01/oracle/oradata/rmanbak/full_%D_%d_%U.bak';
Starting backup at 08-NOV-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=46 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/s01/oracle/oradata/edison/system01.dbf
input datafile file number=00003 name=/s01/oracle/oradata/edison/undotbs01.dbf
input datafile file number=00002 name=/s01/oracle/oradata/edison/sysaux01.dbf
input datafile file number=00004 name=/s01/oracle/oradata/edison/users01.dbf
input datafile file number=00005 name=/s01/oracle/oradata/edison/GoldenGate.dbf
channel ORA_DISK_1: starting piece 1 at 08-NOV-14
channel ORA_DISK_1: finished piece 1 at 08-NOV-14
piece handle=/s01/oracle/oradata/rmanbak/full_08_EDISON_01pn3he6_1_1.bak tag=TAG20141108T122334 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:03:46
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 08-NOV-14
channel ORA_DISK_1: finished piece 1 at 08-NOV-14
piece handle=/s01/oracle/oradata/rmanbak/full_08_EDISON_02pn3hl8_1_1.bak tag=TAG20141108T122334 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 08-NOV-14
RMAN>
[oracle@oggs rmanbak]$ scp *.bak oggd:/s01/oracle/oradata/rmanbak
oracle@oggd's password:
full_08_EDISON_01pn3he6_1_1.bak 100% 1046MB 11.8MB/s 01:29
full_08_EDISON_02pn3hl8_1_1.bak 100% 9600KB 9.4MB/s 00:01
[oracle@oggs rmanbak]$
将密码文件传输到目标端:
[oracle@oggs rmanbak]$ cd $ORACLE_HOME/dbs
[oracle@oggs dbs]$ ls -ltr
total 9556
-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r----- 1 oracle oinstall 1536 Nov 8 05:40 orapwedison
-rw-r----- 1 oracle oinstall 24 Nov 8 05:40 lkEDISON
-rw-r----- 1 oracle oinstall 2560 Nov 8 11:57 spfileedison.ora
-rw-rw---- 1 oracle oinstall 1544 Nov 8 11:57 hc_edison.dat
-rw-r----- 1 oracle oinstall 9748480 Nov 8 12:27 snapcf_edison.f
[oracle@oggs dbs]$ scp orapwedison oggd:$ORACLE_HOME/dbs
oracle@oggd's password:
orapwedison 100% 1536 1.5KB/s 00:01
[oracle@oggs dbs]$
4.9.源端获取scn,并切归档:
SQL> spool current_scn.txt
SQL> select to_char(current_scn) from v$database;
TO_CHAR(CURRENT_SCN)
----------------------------------------
847495
SQL> alter system archive log current;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL>
4.10.目标端启动归档:
[oracle@oggd goldengate]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Aug 23 2012 20:20:21
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (oggd) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
GGSCI (oggd) 2> view params mgr
port 7839
DYNAMICPORTLIST 7840-7850
AUTOSTART EXTRACT *
AUTORESTART EXTRACT *
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 7
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45
GGSCI (oggd) 3>
4.11.目标端恢复数据库:
[oracle@oggd ~]$ rman target / nocatalog
Recovery Manager: Release 11.2.0.3.0 - Production on Sat Nov 8 12:43:45 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> startup nomount;
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/s01/oracle/proudct/11.2.0.1/db_home1/dbs/initedison.ora'
starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started
Total System Global Area 158662656 bytes
Fixed Size 2226456 bytes
Variable Size 92276456 bytes
Database Buffers 58720256 bytes
Redo Buffers 5439488 bytes
RMAN> restore spfile from '/s01/oracle/oradata/rmanbak/full_08_EDISON_02pn3hl8_1_1.bak';
Starting restore at 08-NOV-14
using channel ORA_DISK_1
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /s01/oracle/oradata/rmanbak/full_08_EDISON_02pn3hl8_1_1.bak
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 08-NOV-14
RMAN>
创建相关目录:
[oracle@oggd admin]$ mkdir -p /s01/oracle/admin/edison/adump
[oracle@oggd admin]$ mkdir -p /s01/oracle/admin/edison/bdump
[oracle@oggd admin]$ mkdir -p /s01/oracle/admin/edison/cdump
[oracle@oggd admin]$ mkdir -p /s01/oracle/admin/edison/pfile
[oracle@oggd admin]$ mkdir -p /s01/oracle/admin/edison/udump
[oracle@oggd admin]$ rman target / nocatalog
Recovery Manager: Release 11.2.0.3.0 - Production on Sat Nov 8 12:50:25 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> startup nomount;
Oracle instance started
Total System Global Area 835104768 bytes
Fixed Size 2232960 bytes
Variable Size 494931328 bytes
Database Buffers 335544320 bytes
Redo Buffers 2396160 bytes
RMAN> restore controlfile from '/s01/oracle/oradata/rmanbak/full_08_EDISON_02pn3hl8_1_1.bak';
Starting restore at 08-NOV-14
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/s01/oracle/oradata/edison/control01.ctl
output file name=/s01/oracle/oradata/edison/control02.ctl
Finished restore at 08-NOV-14
RMAN> restore database;
Starting restore at 08-NOV-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 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 /s01/oracle/oradata/edison/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /s01/oracle/oradata/edison/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /s01/oracle/oradata/edison/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /s01/oracle/oradata/edison/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /s01/oracle/oradata/edison/GoldenGate.dbf
channel ORA_DISK_1: reading from backup piece /s01/oracle/oradata/rmanbak/full_08_EDISON_01pn3he6_1_1.bak
channel ORA_DISK_1: piece handle=/s01/oracle/oradata/rmanbak/full_08_EDISON_01pn3he6_1_1.bak tag=TAG20141108T122334
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:04:08
Finished restore at 08-NOV-14
RMAN>
源端主机备份归档:
RMAN> backup archivelog all format '/s01/oracle/oradata/rmanbak/arch_%D_%d_%U.bak';
Starting backup at 08-NOV-14
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=68 RECID=1 STAMP=863074635
input archived log thread=1 sequence=69 RECID=2 STAMP=863074636
input archived log thread=1 sequence=70 RECID=3 STAMP=863074640
input archived log thread=1 sequence=71 RECID=4 STAMP=863075980
input archived log thread=1 sequence=72 RECID=5 STAMP=863083557
input archived log thread=1 sequence=73 RECID=6 STAMP=863095131
input archived log thread=1 sequence=74 RECID=7 STAMP=863095132
input archived log thread=1 sequence=75 RECID=8 STAMP=863095135
input archived log thread=1 sequence=76 RECID=9 STAMP=863095138
input archived log thread=1 sequence=77 RECID=10 STAMP=863096765
channel ORA_DISK_1: starting piece 1 at 08-NOV-14
channel ORA_DISK_1: finished piece 1 at 08-NOV-14
piece handle=/s01/oracle/oradata/rmanbak/arch_08_EDISON_03pn3jtt_1_1.bak tag=TAG20141108T130605 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 08-NOV-14
RMAN>
[oracle@oggs rmanbak]$ ls -ltr
total 1185048
-rw-r----- 1 oracle oinstall 1097007104 Nov 8 12:27 full_08_EDISON_01pn3he6_1_1.bak
-rw-r----- 1 oracle oinstall 9830400 Nov 8 12:27 full_08_EDISON_02pn3hl8_1_1.bak
-rw-r----- 1 oracle oinstall 105447424 Nov 8 13:06 arch_08_EDISON_03pn3jtt_1_1.bak
[oracle@oggs rmanbak]$ scp arch* oggd:/s01/oracle/oradata/rmanbak
oracle@oggd's password:
arch_08_EDISON_03pn3jtt_1_1.bak 100% 101MB 25.1MB/s 00:04
[oracle@oggs rmanbak]$
目标端恢复归档:
RMAN> catalog backuppiece '/s01/oracle/oradata/rmanbak/arch_08_EDISON_03pn3jtt_1_1.bak';
cataloged backup piece
backup piece handle=/s01/oracle/oradata/rmanbak/arch_08_EDISON_03pn3jtt_1_1.bak RECID=2 STAMP=863097368
RMAN> run{
2> set until scn 847495;
3> recover database;
4> }
executing command: SET until clause
Starting recover at 08-NOV-14
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=73
channel ORA_DISK_1: reading from backup piece /s01/oracle/oradata/rmanbak/arch_08_EDISON_03pn3jtt_1_1.bak
channel ORA_DISK_1: piece handle=/s01/oracle/oradata/rmanbak/arch_08_EDISON_03pn3jtt_1_1.bak tag=TAG20141108T130605
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
archived log file name=/s01/oracle/oradata/arch/1_73_863070045.dbf thread=1 sequence=73
media recovery complete, elapsed time: 00:00:01
Finished recover at 08-NOV-14
RMAN>
确认已经恢复到指定的scn后resetlogs方式打开库:
SQL> alter database open resetlogs;
Database altered.
SQL>
4.12.查看源端是否有job需要禁用:
SQL> select job,log_user,what from dba_jobs;
JOB LOG_USER
---------- ------------------------------
WHAT
--------------------------------------------------------------------------------
4001 SYS
wwv_flow_cache.purge_sessions(p_purge_sess_older_then_hrs => 24);
4002 SYS
wwv_flow_mail.push_queue(wwv_flow_platform.get_preference('SMTP_HOST_ADDRESS'),w
wv_flow_platform.get_preference('SMTP_HOST_PORT'));
SQL>
SQL> select owner,job_name from DBA_SCHEDULER_JOBs;
OWNER JOB_NAME
------------------------------ ------------------------------
SYS XMLDB_NFS_CLEANUP_JOB
SYS SM$CLEAN_AUTO_SPLIT_MERGE
SYS RSE$CLEAN_RECOVERABLE_SCRIPT
SYS FGR$AUTOPURGE_JOB
SYS BSLN_MAINTAIN_STATS_JOB
SYS DRA_REEVALUATE_OPEN_FAILURES
SYS HM_CREATE_OFFLINE_DICTIONARY
SYS ORA$AUTOTASK_CLEAN
SYS FILE_WATCHER
SYS PURGE_LOG
ORACLE_OCM MGMT_STATS_CONFIG_JOB
ORACLE_OCM MGMT_CONFIG_JOB
EXFSYS RLM$SCHDNEGACTION
EXFSYS RLM$EVTCLEANUP
14 rows selected.
SQL>
4.13.目标端禁用triger和约束:
SQL> set heading off
SQL> set pages 200
SQL> spool disable_con.sql
SQL> select 'alter table "' owner '"."' table_name '" disable constraint "' constraint_name '";' from dba_constraints where constraint_type in('R') and owner in ('OGGTEST') order by status,owner;
no rows selected
SQL>
SQL> spool off
如果有就执行生成的脚本。
4.14.目标端创建gg用户并授权:
如果目标数据库是用RMAN恢复建立的,需要先drop原来的goldengate用户。
SQL> drop user goldengate cascade;
User dropped.
SQL> CREATE USER GOLDENGATE IDENTIFIED BY GOLDENGATE DEFAULT TABLESPACE GOLDENGATE TEMPORARY TABLESPACE TEMP;
User created.
SQL>
执行以下脚本:
GRANT CONNECT TO GOLDENGATE;
GRANT ALTER ANY TABLE TO GOLDENGATE;
GRANT ALTER SESSION TO GOLDENGATE;
GRANT CREATE SESSION TO GOLDENGATE;
GRANT FLASHBACK ANY TABLE TO GOLDENGATE;
GRANT SELECT ANY DICTIONARY TO GOLDENGATE;
GRANT SELECT ANY TABLE TO GOLDENGATE;
GRANT RESOURCE TO GOLDENGATE;
GRANT INSERT ANY TABLE TO GOLDENGATE;
GRANT UPDATE ANY TABLE TO GOLDENGATE;
GRANT DELETE ANY TABLE TO GOLDENGATE;
GRANT CREATE ANY INDEX TO GOLDENGATE;
GRANT SELECT ANY TRANSACTION TO GOLDENGATE;
grant execute any type to goldengate;
4.15.目标端创建replicate进程:
GGSCI (oggd) 4> dblogin userid GOLDENGATE, password GOLDENGATE
Successfully logged into database.
GGSCI (oggd) 5> add checkpointtable goldengate.rep_app_ckpt
Successfully created checkpoint table goldengate.rep_app_ckpt.
GGSCI (oggd) 6>
强列建议为每个Replicat进程创建checkpointtable。
GGSCI (oggd) 6> add replicat rep_app,exttrail ./dirdat/t1,checkpointtable goldengate.rep_app_ckpt
REPLICAT added.
GGSCI (oggd) 7>
注:
Exttrail参数要和源端DataPump进程的rmttrail参数指向一致;
Checkpointtable参数引用的表必须已用add checkpointtable命令创建;
编辑rep_app参数:
GGSCI (oggd) 8> edit params rep_app
GGSCI (oggd) 9> view params rep_app
REPLICAT rep_app
SETENV (NLS_LANG = "American_America.WE8MSWIN1252")
USERID GOLDENGATE,PASSWORD GOLDENGATE
REPORTCOUNT EVERY 30 MINUTES, RATE
REPERROR DEFAULT, ABEND
numfiles 5000
--HANDLECOLLISIONS
assumetargetdefs
DISCARDFILE ./dirrpt/rep_app.dsc, APPEND, MEGABYTES 1000
ALLOWNOOPUPDATES
map OGGTEST.*;
GGSCI (oggd) 10>
注:
NLS_LANG变量要和数据库语言环境一致;
确保需要复制的表都出现在map参数中;
4.16.目标端启动replicat进程:
启动Replicat进程需要确认源数据库到目标数据库的数据初始化已完成;
第一次启动Replicat进程需要指定aftercsn scn_number参数;
Scn_number为数据库RMAN Recover目标数据库until指定的SCN值,或exp/expdp指定的flashback_scn值;
启动目标端Replicat进程:
GGSCI (oggd) 16> start replicat rep_app, aftercsn 847495
Sending START request to MANAGER ...
REPLICAT REP_APP starting
GGSCI (oggd) 21> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP_APP 00:00:00 00:00:02
GGSCI (oggd) 22> info rep_app
REPLICAT REP_APP Last Started 2014-11-08 14:37 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:14 ago)
Log Read Checkpoint File ./dirdat/t1000000
First Record RBA 23646890
GGSCI (oggd) 23>
至此,GoldenGate初始化完成。
严重提示:以后启动Replicat进程不能指定csn参数!
附录:
其实在11g中启动rep_app进程的过程中报了如下错误:
2014-11-08 14:28:41 ERROR OGG-00204 Oracle GoldenGate Delivery for Oracle, rep_app.prm: Missing TARGET specification.
2014-11-08 14:28:41 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, rep_app.prm: PROCESS ABENDING.
查找网上的资料,是因为rep_app进程的map参数没有配置target选项:
把上面的参数map OGGTEST.*;改成map OGGTEST.*,target OGGTEST.*;就没问题了,修改后的参数如下:
GGSCI (oggd) 23> view params rep_app
REPLICAT rep_app
SETENV (NLS_LANG =AMERICAN_AMERICA.WE8MSWIN1252)
USERID GOLDENGATE,PASSWORD GOLDENGATE
REPORTCOUNT EVERY 30 MINUTES, RATE
REPERROR DEFAULT, ABEND
numfiles 5000
--HANDLECOLLISIONS
assumetargetdefs
DISCARDFILE ./dirrpt/rep_app.dsc, APPEND, MEGABYTES 1000
ALLOWNOOPUPDATES
map OGGTEST.*, target OGGTEST.*;
GGSCI (oggd) 24>
但是10g中可以不用配置target选项。