OGG同步数据方法
来源:互联网 发布:sybase数据库win7 64 编辑:程序博客网 时间:2024/06/01 08:02
http://blog.itpub.net/15480802/viewspace-761788 ----rman/expdp
http://blog.itpub.net/26442936/sid-172198-list-1/ ----rman/expdp
http://blog.itpub.net/429786/viewspace-1062223/ ----OGG 自身初始化
http://blog.itpub.net/26442936/sid-172198-list-1/ ----rman/expdp
http://blog.itpub.net/429786/viewspace-1062223/ ----OGG 自身初始化
http://www.askmaclean.com/archives/ogg-goldengate-initial-load-method.html
Oracle goldengate instantiation 方法:
一、 coning with oracle rman
二、 oracle EXPDP/IMPDP or EXP/IMP Utilities
一、coning with oracle rman
On the Source (RMAN refers to as target): Add and Start Rxtract:Enable Minimal Supplemental Logging in OracleSQLPLUS > alter database add supplemental log dataEnable Supplemental Logging at Table LevelGGSCI> dblogin userid xxxxx password xxxxxxGGSCI> add trandata <schema>.<tablename>Add Extract, Add Exttrail, Add Pump, Add RmttrailStart Extract, Start Pump Recover database on target database:Create init.ora file for the destination .Run the following SQL (if you currently use an spfile; if you already use a pfile then you can use the pfile and skip this step)SQLPLUS> create pfile=‘<path>/init<sid>.ora’ from spfile ;Copy init<sid>.ora file to target (to $ORACLE_HOME/dbs directory on destination)sftp> put init<sid>.oraBackup source databaseOS> RMAN target /RMAN> backup database plus archivelog ;Copy backup files to destination located in the same location as they were written on the source (default is $ORACLE_HOME/dbs but it may have been changed using the CONFIGURE command in RMAN).sftp> <backup files>Once backup completes, capture SCN to clear datafile fuzzinessInstantiation from an Oracle Source Database: Oracle GoldenGateRMAN> restore database preview summary;Note the SCN returned, it will appear at the end of the command like:Media recovery start SCN is 34822578Recovery must be done beyond SCN 34822578 to clear datafile fuzzinessFinished restore at 10-OCT-10On the Destination (RMAN refers to as auxiliary) :Add a tnsnames.ora entry (in $ORACLE_HOME/network/admin) for the source database.Create the password file (in the $ORACLE_HOME/dbs directory):$ORACLE_HOME/dbs> orapwd file=orapw<SID> password=<password>Start up auxiliary instance (make sure the $ORACLE_SID is set correctly and the init<SID>.ora is in $ORACLE_HOME/dbs)SQLPLUS> startup nomountCreate duplicate database. This step will open the databaseOS> RMAN target sys/<sys password for source db>@<source database from tnsnames.ora >RMAN> connect auxiliary /RMAN> duplicate target database to <databasename> nofilenamecheck until scn <value returned from RMAN restore database preview summary command> ;Note: you can also run the duplicate database command by running RMAN on the source. In that case you use connect target / on the source, and connect auxiliary sys@<target db>.In either case you must copy the backup files to the destination server. Also for this scenario you must include a tnsnames entry for the target database which would be added to the tnsnames.ora file on the host with the auxiliary. In addition, on the source server, you must have listener configured to accept requests for the destination database in a nomount state. This is done by explicitly including the service/SID in the listener.ora configuration file.Once complete review the Oracle alert log to get last SCN that the database was recovered to. Look in the oracle alert log for the following entry:RESETLOGS after incomplete recovery UNTIL CHANGE 34822578After 34822578 in this sample is the SCN where we want Replicat to start processingAdd Temp file (before Oracle 10.2; starting with Oracle 10.2 Oracle will automatically add temp files and you can skip this step)SQLPLUS> alter tablespace temp add tempfile ‘<oradata dir>/<temp.dbf>‘ size <size>Create spfile (if you want to use an spfile; if not you can skip this step and the next step below):SQLPLUS> create spfile from pfile=‘$ORACLE_HOME/dbs/init<sid>.ora’ ;Stop and Restart Database to pick up spfile:SQLPLUS> shutdown immediateSQLPLUS> startup Add and Start Replicat:GGSCI> add replicat <rename>, exttrail ./dirdat/<xx>GGSCI> start replicat <rename>, aftercsn <value returned from alert log for SCN database was recovered to>二、oracle EXPDP/IMPDP or EXP/IMP Utilities
Example 1: full database export using expdp Add and Start Rxtract:Enable Minimal Supplemental Logging in Oracle on sourcealter database add supplemental log data ;Enable Supplemental Logging at Table Level on sourceGGSCI> dblogin userid xxxxx password xxxxxxGGSCI> add trandata <schema>.<tablename>Add Extract, Add Exttrail, Add Pump, Add Rmttrail on sourceStart Extract, Start Pump on source Create a database directory:SQLPLUS> create directory dumpdir as ‘<some directory>‘ ;Get the current SCN on the source database:SQLPLUS> select current_scn from v$database ;CURRENT_SCN-----------------------28318029Run the Data Pump Export using the flashback SCN you obtained in the previous step. The following example shows running the expdp utility at a Degree Of Parallelism (DOP) of 4. If you have sufficient system resources (CPU, memory and IO) then running at a higher DOP will decrease the amount of time it takes to take the export (up to 4x for a DOP of 4). Note that expdp uses Oracle Database parallel execution settings (e.g. parallel_max_servers) which have to be set appropriately in order to take advantage of parallelism. Other processes running in parallel may be competing for those resources. See the Oracle Documentation for more details.>expdp directory=dumpdir full=y parallel=4 dumpfile=ora102_%u.dmp flashback_scn=28318029>Username: systemNOTE: any DB user with DBA privileges will do>Password:Note: The export log needs to be checked for errors.Start an import using impdp to the target database when the export step is complete. Add and Start Replicat after import is complete:GGSCI> add replicat <repname>, exttrail ./dirdat/<xx>GGSCI> start replicat <repname>, aftercsn <value returned from step 6> Example 2: schema export using exp Enable Minimal Supplemental Logging in Oracle on sourceSQLPLUS > alter database add supplemental log data;Enable Supplemental Logging at Table Level on sourceGGSCI> dblogin userid xxxxx password xxxxxxGGSCI> add trandata <schema>.<tablename>Add Extract, Add ExtTrail FileTrail File, Add Pump, Add RmtTrail FileTrail File on sourceStart Extract, Start Pump on sourceGet the current SCN on the source database:SQLPLUS> select current_scn from v$database ;CURRENT_SCN-----------------------3410138433Get a flashback snapshot from the SCN that you obtained in the previous step. You can do this with exp or expdp utility. The following example shows the use of export utility (exp):>exp <username>/<password> wner=<schema_name> grants=n statistics=none triggers=n compress=n FLASHBACK_SCN=3410138433Note: Undo_retention has to be set high enough, and the export log needs to be checked for errors. You can speed up exp by running multiple session in parallel but you have to manually configure subsets of the data you want to export (e.g. different tables for different export files). Make sure to use the same FLASHBACK_SCN for all export sessions to ensure taking a read consistent backup. Start an import to the target database when export is complete. Add and Start Replicat:GGSCI> add replicat <rename>, exttrail ./dirdat/<xx>GGSCI> start replicat <rename>, aftercsn <SCN value returned from query on source>
0 0
- OGG同步数据方法
- 不同表结构OGG数据同步
- 使用ogg同步数据给kafka
- oracle ogg同步到kafka不抓数据,重启ogg报错
- ogg数据同步停止OGG-00446 Could not find archived log for sequence
- 【GoldenGate】使用OGG,两个Oracle库之间单向同步数据
- ogg同步mysql(mariadb5.5)数据到oracle
- ogg同步的是语句还是数据变更
- 基于OGG Datahub插件将Oracle数据同步上云
- Windows平台OGG单向数据同步笔记(一)
- Windows平台OGG单向数据同步笔记(二)
- ogg根据scn同步数据,源库零停机时间
- OGG同步序列配置
- OGG DDL 双向同步
- ogg同步表
- DG备库-OGG 同步
- ogg单向DML同步配置
- ogg 同步SQLServer到Oracle
- HTTP错误
- Java Web开发相关连接
- 选择排序
- 剑指offer--面试题19:二叉树的镜像--Java实现
- C++第12章项目,ATM机模拟队列。
- OGG同步数据方法
- 关于selector失效的问题
- C++下高效率int转成string
- block IO层框架分析2
- C#不同类间通过事件传递参数
- 感知机学习算法的原始形式
- Opencv中常用库文件名称
- CocoaUI 是一个强大的 iOS UI 框架
- Fragment和Activity之间的数据传递