该博客详细记录了如何使用RMAN（恢复管理器）初始化并复制一个Oracle数据库到新的目标环境。过程包括配置数据库参数、设置初始化参数文件、建立连接、启动复制、设置新名称、恢复数据、打开数据库、处理临时表空间、安装GoldenGate软件、配置环境变量、创建用户和权限、执行DDL和DML同步测试。整个过程展示了数据库迁移的完整步骤。
一.用RMAN初始化数据库：配置好目标数据库的参数文件：
OGG1.__db_cache_size=318767104
OGG1.__java_pool_size=4194304
OGG1.__large_pool_size=16777216
OGG1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
OGG1.__pga_aggregate_target=293601280
OGG1.__sga_target=545259520
OGG1.__shared_io_pool_size=0
OGG1.__shared_pool_size=192937984
OGG1.__streams_pool_size=0
*._optimizer_ignore_hints=FALSE
*.audit_file_dest='/u01/app/oracle/admin/OGG1/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='+DATA1/ogg1/controlfile/control01.dbf','+DATA1/ogg1/controlfile/control02.dbf'#Restore Controlfile
*.db_block_size=8192
*.DB_FILE_NAME_CONVERT='+DATA/phub/datafile/','+DATA1/ogg1/datafile'
*.LOG_FILE_NAME_CONVERT='+DATA/phub/onlinelog/','+DATA1/ogg1/onlinelog/'
*.db_create_file_dest='+DATA1'
*.db_domain=''
*.db_flashback_retention_target=120
*.db_name='OGG1'
*.db_recovery_file_dest='+DATA1'
*.db_recovery_file_dest_size=10737418240
*.DB_UNIQUE_NAME='OGG1'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=OGG1XDB)'
*.memory_target=838860800
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
[oracle@cwogg admin]$ rman target sys/123123@PHUB auxiliary sys/123123@OGG1 nocatalog
Recovery Manager: Release 11.2.0.4.0 - Production on Mon Oct 26 16:53:15 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: PHUB (DBID=536511065)
using target database control file instead of recovery catalog
connected to auxiliary database: OGG1 (not mounted)
RMAN> duplicate target database to OGG1 from active database nofilenamecheck;
Starting Duplicate Db at 26-OCT-15
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=131 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=192 device type=DISK
contents of Memory Script:
{
sql clone "create spfile from memory";
}
executing Memory Script
sql statement: create spfile from memory
contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 511708240 bytes
Database Buffers 314572800 bytes
Redo Buffers 6565888 bytes
contents of Memory Script:
{
sql clone "alter system set db_name =
''PHUB'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''OGG1'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
backup as copy current controlfile auxiliary format '+DATA1/ogg1/controlfile/control01.dbf';
restore clone controlfile to '+DATA1/ogg1/controlfile/control02.dbf' from
'+DATA1/ogg1/controlfile/control01.dbf';
alter clone database mount;
}
executing Memory Script
sql statement: alter system set db_name = ''PHUB'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''OGG1'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 511708240 bytes
Database Buffers 314572800 bytes
Redo Buffers 6565888 bytes
Starting backup at 26-OCT-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=398 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=25 device type=DISK
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_PHUB.f tag=TAG20151026T165351 RECID=13 STAMP=894128033
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 26-OCT-15
Starting restore at 26-OCT-15
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=69 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=131 device type=DISK
channel ORA_AUX_DISK_2: skipped, AUTOBACKUP already found
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 26-OCT-15
database mounted
RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.
contents of Memory Script:
{
set newname for datafile 1 to
"+data1";
set newname for datafile 2 to
"+data1";
set newname for datafile 3 to
"+data1";
set newname for datafile 4 to
"+data1";
set newname for datafile 5 to
"+data1";
set newname for datafile 6 to
"+DATA1/ogg1/datafilellc01.dbf";
set newname for datafile 7 to
"+DATA1/ogg1/datafileidx01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"+data1" datafile
2 auxiliary format
"+data1" datafile
3 auxiliary format
"+data1" datafile
4 auxiliary format
"+data1" datafile
5 auxiliary format
"+data1" datafile
6 auxiliary format
"+DATA1/ogg1/datafilellc01.dbf" datafile
7 auxiliary format
"+DATA1/ogg1/datafileidx01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 26-OCT-15
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=+DATA/phub/datafile/llc01.dbf
channel ORA_DISK_2: starting datafile copy
input datafile file number=00002 name=+DATA/phub/datafile/sysaux.272.891340857
output file name=+DATA1/ogg1/datafilellc01.dbf tag=TAG20151026T165410
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=+DATA/phub/datafile/idx01.dbf
output file name=+DATA1/ogg1/datafile/sysaux.261.894128051 tag=TAG20151026T165410
channel ORA_DISK_2: datafile copy complete, elapsed time: 00:04:21
channel ORA_DISK_2: starting datafile copy
input datafile file number=00001 name=+DATA/phub/datafile/system.271.891340857
output file name=+DATA1/ogg1/datafileidx01.dbf tag=TAG20151026T165410
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:03:30
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA/phub/datafile/users.269.891340843
output file name=+DATA1/ogg1/datafile/system.259.894128311 tag=TAG20151026T165410
channel ORA_DISK_2: datafile copy complete, elapsed time: 00:02:40
channel ORA_DISK_2: starting datafile copy
input datafile file number=00005 name=+DATA/phub/datafile/example.287.891340843
output file name=+DATA1/ogg1/datafile/users.258.894128337 tag=TAG20151026T165410
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:23
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATA/phub/datafile/undotbs1.260.891340857
output file name=+DATA1/ogg1/datafile/undotbs1.264.894128479 tag=TAG20151026T165410
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
output file name=+DATA1/ogg1/datafile/example.263.894128473 tag=TAG20151026T165410
channel ORA_DISK_2: datafile copy complete, elapsed time: 00:01:02
Finished backup at 26-OCT-15
sql statement: alter system archive log current
contents of Memory Script:
{
backup as copy reuse
archivelog like "+DATA/phub/archivelog/2015_10_26/thread_1_seq_398.284.894128537" auxiliary format
"+DATA1" ;
catalog clone start with "+DATA1";
switch clone datafile all;
}
executing Memory Script
Starting backup at 26-OCT-15
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=398 RECID=784 STAMP=894128536
output file name=+DATA1/ogg1/archivelog/2015_10_26/thread_1_seq_398.265.894128539 RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 26-OCT-15
searching for all files that match the pattern +DATA1
List of Files Unknown to the Database
=====================================
File Name: +data1/OGG1/datafilellc01.dbf
File Name: +data1/OGG1/datafileidx01.dbf
File Name: +data1/OGG1/ARCHIVELOG/2015_10_26/thread_1_seq_398.265.894128539
File Name: +data1/OGG1/datafile/SYSAUX.261.894128051
File Name: +data1/OGG1/datafile/SYSTEM.259.894128311
File Name: +data1/OGG1/datafile/USERS.258.894128337
File Name: +data1/OGG1/datafile/EXAMPLE.263.894128473
File Name: +data1/OGG1/datafile/UNDOTBS1.264.894128479
File Name: +data1/ASM/ASMPARAMETERFILE/REGISTRY.253.894123127
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: +data1/OGG1/datafilellc01.dbf
File Name: +data1/OGG1/datafileidx01.dbf
File Name: +data1/OGG1/ARCHIVELOG/2015_10_26/thread_1_seq_398.265.894128539
File Name: +data1/OGG1/datafile/SYSAUX.261.894128051
File Name: +data1/OGG1/datafile/SYSTEM.259.894128311
File Name: +data1/OGG1/datafile/USERS.258.894128337
File Name: +data1/OGG1/datafile/EXAMPLE.263.894128473
File Name: +data1/OGG1/datafile/UNDOTBS1.264.894128479
List of Files Which Where Not Cataloged
=======================================
File Name: +data1/ASM/ASMPARAMETERFILE/REGISTRY.253.894123127
RMAN-07518: Reason: Foreign database file DBID: 0 Database Name:
datafile 1 switched to datafile copy
input datafile copy RECID=20 STAMP=894128541 file name=+DATA1/ogg1/datafile/system.259.894128311
datafile 2 switched to datafile copy
input datafile copy RECID=21 STAMP=894128542 file name=+DATA1/ogg1/datafile/sysaux.261.894128051
datafile 3 switched to datafile copy
input datafile copy RECID=22 STAMP=894128542 file name=+DATA1/ogg1/datafile/undotbs1.264.894128479
datafile 4 switched to datafile copy
input datafile copy RECID=23 STAMP=894128542 file name=+DATA1/ogg1/datafile/users.258.894128337
datafile 5 switched to datafile copy
input datafile copy RECID=24 STAMP=894128542 file name=+DATA1/ogg1/datafile/example.263.894128473
datafile 6 switched to datafile copy
input datafile copy RECID=25 STAMP=894128542 file name=+DATA1/ogg1/datafilellc01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=26 STAMP=894128543 file name=+DATA1/ogg1/datafileidx01.dbf
contents of Memory Script:
{
set until scn 6109815;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 26-OCT-15
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
starting media recovery
archived log for thread 1 with sequence 398 is already on disk as file +DATA1/ogg1/archivelog/2015_10_26/thread_1_seq_398.265.894128539
archived log file name=+DATA1/ogg1/archivelog/2015_10_26/thread_1_seq_398.265.894128539 thread=1 sequence=398
media recovery complete, elapsed time: 00:00:02
Finished recover at 26-OCT-15
Oracle instance started
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 511708240 bytes
Database Buffers 314572800 bytes
Redo Buffers 6565888 bytes
contents of Memory Script:
{
sql clone "alter system set db_name =
''OGG1'' comment=
''Reset to original value by RMAN'' scope=spfile";
sql clone "alter system reset db_unique_name scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set db_name = ''OGG1'' comment= ''Reset to original value by RMAN'' scope=spfile
sql statement: alter system reset db_unique_name scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 511708240 bytes
Database Buffers 314572800 bytes
Redo Buffers 6565888 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "OGG1" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '+data1', '+data1' ) SIZE 50 M REUSE,
GROUP 2 ( '+data1', '+data1' ) SIZE 50 M REUSE,
GROUP 3 ( '+data1', '+data1' ) SIZE 50 M REUSE
DATAFILE
'+DATA1/ogg1/datafile/system.259.894128311'
CHARACTER SET AL32UTF8
contents of Memory Script:
{
set newname for tempfile 1 to
"+data";
set newname for tempfile 2 to
"+DATA/phub/tempfile/tmp01.dbf";
switch clone tempfile all;
catalog clone datafilecopy "+DATA1/ogg1/datafile/sysaux.261.894128051",
"+DATA1/ogg1/datafile/undotbs1.264.894128479",
"+DATA1/ogg1/datafile/users.258.894128337",
"+DATA1/ogg1/datafile/example.263.894128473",
"+DATA1/ogg1/datafilellc01.dbf",
"+DATA1/ogg1/datafileidx01.dbf";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to +data in control file
renamed tempfile 2 to +DATA/phub/tempfile/tmp01.dbf in control file
cataloged datafile copy
datafile copy file name=+DATA1/ogg1/datafile/sysaux.261.894128051 RECID=1 STAMP=894128569
cataloged datafile copy
datafile copy file name=+DATA1/ogg1/datafile/undotbs1.264.894128479 RECID=2 STAMP=894128569
cataloged datafile copy
datafile copy file name=+DATA1/ogg1/datafile/users.258.894128337 RECID=3 STAMP=894128569
cataloged datafile copy
datafile copy file name=+DATA1/ogg1/datafile/example.263.894128473 RECID=4 STAMP=894128569
cataloged datafile copy
datafile copy file name=+DATA1/ogg1/datafilellc01.dbf RECID=5 STAMP=894128569
cataloged datafile copy
datafile copy file name=+DATA1/ogg1/datafileidx01.dbf RECID=6 STAMP=894128569
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=894128569 file name=+DATA1/ogg1/datafile/sysaux.261.894128051
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=894128569 file name=+DATA1/ogg1/datafile/undotbs1.264.894128479
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=894128569 file name=+DATA1/ogg1/datafile/users.258.894128337
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=894128569 file name=+DATA1/ogg1/datafile/example.263.894128473
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=894128569 file name=+DATA1/ogg1/datafilellc01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=6 STAMP=894128569 file name=+DATA1/ogg1/datafileidx01.dbf
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 26-OCT-15
一定记得处理一下临时表空间：
SQL> alter database tempfile '+DATA/phub/tempfile/tmp01.dbf' drop;
Database altered.
SQL> alter tablespace temp add tempfile '+DATA1/ogg1/tempfile/tmp01.dbf' size 1024M;
Tablespace altered.
注册数据到crs：
[oracle@ogg1 ~]$ srvctl status listener -l listener
Listener LISTENER is enabled
Listener LISTENER is running on node(s): ogg1
[oracle@ogg1 ~]$ srvctl add database -d OGG1 -o /u01/app/oracle/product/11.2.0/db_1/
[oracle@ogg1 ~]$ srvctl status database -d OGG1
Database is not running.
[oracle@ogg1 ~]$ srvctl start database -d OGG1
[oracle@ogg1 ~]$ srvctl status database -d OGG1
Database is running.
[oracle@ogg1 ~]$ su - grid
Password:
[grid@ogg1 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA1.dg
ONLINE ONLINE ogg1
ora.LISTENER.lsnr
ONLINE ONLINE ogg1
ora.asm
ONLINE ONLINE ogg1 Started
ora.ons
OFFLINE OFFLINE ogg1
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
1 ONLINE ONLINE ogg1
ora.diskmon
1 OFFLINE OFFLINE
ora.evmd
1 ONLINE ONLINE ogg1
ora.ogg1.db
1 ONLINE ONLINE ogg1 Open
以下操作在源库和目标库都要执行:
安装GoldenGate软件
[oracle@cwogg ~]$ mkdir -p /u01/ogg/
[oracle@cwogg u01]$ cd ogg/
[oracle@cwogg ogg]$ ls
ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip
[oracle@cwogg ogg]$ unzip ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip
[oracle@cwogg ogg]$ unzip ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip
Archive: ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip
inflating: fbo_ggs_Linux_x64_ora11g_64bit.tar
inflating: OGG_WinUnix_Rel_Notes_11.2.1.0.1.pdf
inflating: Oracle GoldenGate 11.2.1.0.1 README.txt
inflating: Oracle GoldenGate 11.2.1.0.1 README.doc
[oracle@cwogg ogg]$ tar -xvf fbo_ggs_Linux_x64_ora11g_64bit.tar
配置环境变量：
export PATH=/u01/ogg:$PATH
export LD_LIBRARY_PATH=/u01/ogg:$LD_LIBRARY_PATH
export GGATE=/u01/ogg
创建ogg目录：
GGSCI (cwogg) 1> create subdirs
Creating subdirectories under current directory /u01/ogg
Parameter files /u01/ogg/dirprm: already exists
Report files /u01/ogg/dirrpt: created
Checkpoint files /u01/ogg/dirchk: created
Process status files /u01/ogg/dirpcs: created
SQL script files /u01/ogg/dirsql: created
Database definitions files /u01/ogg/dirdef: created
Extract data files /u01/ogg/dirdat: created
Temporary files /u01/ogg/dirtmp: created
Stdout files /u01/ogg/dirout: created
检查数据归档模式，附加日志，强制日志，
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 662703184 bytes
Database Buffers 163577856 bytes
Redo Buffers 6565888 bytes
Database mounted.
SQL> alter database force logging;
Database altered.
SQL> alter database add supplemental log data;
Database altered.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> select log_mode,supplemental_log_data_min,force_logging from v$database;
LOG_MODE SUPPLEME FOR
------------ -------- ---
ARCHIVELOG YES YES
创建存放DDL 信息的user并赋权
SQL> create user ogg identified by ogg default tablespace users temporary tablespace temp;
User created.
SQL> grant connect,resource to ogg;
Grant succeeded.
SQL> grant execute on utl_file to ogg;
Grant succeeded.
SQL> grant create table,create sequence to ogg;
Grant succeeded.
SQL> @marker_setup.sql
Marker setup script
You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter Oracle GoldenGate schema name:ogg
Marker setup table script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to OGG
MARKER TABLE
-------------------------------
OK
MARKER SEQUENCE
-------------------------------
OK
Script complete.
SQL> @ddl_setup.sql
Oracle GoldenGate DDL Replication setup script
Verifying that current user has privileges to install DDL Replication...
You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter Oracle GoldenGate schema name:ogg
Working, please wait ...
Spooling to file ddl_setup_spool.txt
Checking for sessions that are holding locks on Oracle Golden Gate metadata tables ...
Check complete.
Using OGG as a Oracle GoldenGate schema name.
Working, please wait ...
DDL replication setup script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to OGG
CLEAR_TRACE STATUS:
Line/pos Error
---------------------------------------- -----------------------------------------------------------------
No errors No errors
CREATE_TRACE STATUS:
Line/pos Error
---------------------------------------- -----------------------------------------------------------------
No errors No errors
TRACE_PUT_LINE STATUS:
Line/pos Error
---------------------------------------- -----------------------------------------------------------------
No errors No errors
INITIAL_SETUP STATUS:
Line/pos Error
---------------------------------------- -----------------------------------------------------------------
No errors No errors
DDLVERSIONSPECIFIC PACKAGE STATUS:
Line/pos Error
---------------------------------------- -----------------------------------------------------------------
No errors No errors
DDLREPLICATION PACKAGE STATUS:
Line/pos Error
---------------------------------------- -----------------------------------------------------------------
No errors No errors
DDLREPLICATION PACKAGE BODY STATUS:
Line/pos Error
---------------------------------------- -----------------------------------------------------------------
No errors No errors
DDL IGNORE TABLE
-----------------------------------
OK
DDL IGNORE LOG TABLE
-----------------------------------
OK
DDLAUXPACKAGE STATUS:
Line/pos Error
---------------------------------------- -----------------------------------------------------------------
No errors No errors
DDLAUX PACKAGE BODY STATUS:
Line/pos Error
---------------------------------------- -----------------------------------------------------------------
No errors No errors
SYS.DDLCTXINFOPACKAGE STATUS:
Line/pos Error
---------------------------------------- -----------------------------------------------------------------
No errors No errors
SYS.DDLCTXINFOPACKAGE BODY STATUS:
Line/pos Error
---------------------------------------- -----------------------------------------------------------------
No errors No errors
DDL HISTORY TABLE
-----------------------------------
OK
DDL HISTORY TABLE(1)
-----------------------------------
OK
DDL DUMP TABLES
-----------------------------------
OK
DDL DUMP COLUMNS
-----------------------------------
OK
DDL DUMP LOG GROUPS
-----------------------------------
OK
DDL DUMP PARTITIONS
-----------------------------------
OK
DDL DUMP PRIMARY KEYS
-----------------------------------
OK
DDL SEQUENCE
-----------------------------------
OK
GGS_TEMP_COLS
-----------------------------------
OK
GGS_TEMP_UK
-----------------------------------
OK
DDL TRIGGER CODE STATUS:
Line/pos Error
---------------------------------------- -----------------------------------------------------------------
No errors No errors
DDL TRIGGER INSTALL STATUS
-----------------------------------
OK
DDL TRIGGER RUNNING STATUS
------------------------------------------------------------------------------------------------------------------------
ENABLED
STAYMETADATA IN TRIGGER
------------------------------------------------------------------------------------------------------------------------
OFF
DDL TRIGGER SQL TRACING
------------------------------------------------------------------------------------------------------------------------
0
DDL TRIGGER TRACE LEVEL
------------------------------------------------------------------------------------------------------------------------
0
LOCATION OF DDL TRACE FILE
------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/phub/PHUB/trace/ggs_ddl_trace.log
Analyzing installation status...
STATUS OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
SUCCESSFUL installation of DDL Replication software components
Script complete.
SQL> @role_setup.sql
GGS Role setup script
This script will drop and recreate the role GGS_GGSUSER_ROLE
To use a different role name, quit this script and then edit the params.sql script to change the gg_role parameter to the preferred name. (Do not run the script.)
You will be prompted for the name of a schema for the GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter GoldenGate schema name:ogg
Wrote file role_setup_set.txt
PL/SQL procedure successfully completed.
Role setup script complete
Grant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command:
GRANT GGS_GGSUSER_ROLE TO
where is the user assigned to the GoldenGate processes.
SQL> grant GGS_GGSUSER_ROLE to ogg;
Grant succeeded.
SQL>@ddl_enable.sql
Trigger altered.
测试goldengate
3.1在Source 和Target 上配置Manager
GGSCI (cwogg) 6> view params mgr
port 7500
dynamicportlist 7500-7505
autorestart extract *,waitminutes 2 ,retries 5
GGSCI (cwogg) 3> start mgr
Manager started.
GGSCI (cwogg) 4> info mgr
Manager is running (IP port cwogg.7500).
3.2 配置SourceDB 的复制队列(ASM管理)
GGSCI (cwogg) 2> view params ext1
extract ext1
setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
userid ogg,password ogg
rmthost 172.16.30.226, mgrport 7500
rmttrail /u01/ogg/dirdat/ht
(TRANLOGOPTIONS ASMUSER SYS@ASM,ASMPASSWORD 123123
TRANLOGOPTIONS ALTARCHIVELOGDEST primary instance PHUB +DATA/phub/archivelog/)和下面参数等价，在11.2.0.4版本中
TRANLOGOPTIONS DBLOGREADER
ddl include mapped objname scott.*;
table scott.*;
增加抽取：
GGSCI (orcl1) 20>add extract ext1,tranlog, begin now
GGSCI (orcl1) 20>add exttrail /u01/ogg/dirdat/lt, extract ext1
3.4 配置TargetDB 同步队列
3.4.1 在Target 端添加checkpoint表：
GGSCI (ogg1) 1> edit params ./GLOBALS
CHECKPOINTTABLE ogg.CKPT_TABLE
GGSCI (ogg1) 2> dblogin userid ogg,password ogg
Successfully logged into database.
--说明，这个用户是在Source 库启用DDL 创建的，我在Target 库也创建了这个用户。
GGSCI (ogg1) 3> add checkpointtable ogg.CKPT_TABLE
Successfully created checkpoint table ogg.CKPT_TABLE.
3.4.2 创建同步队列
GGSCI (ogg1) 8> view params rep1
replicat rep1
setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
ASSUMETARGETDEFS
userid ogg,password ogg
discardfile /u01/ogg/dirdat/rep1_discard.txt,append, megabytes 10
DDL
map scott.*, target scott.*;
GGSCI (ogg1) 31> add replicat rep1,exttrail /u01/ogg/dirdat/ht, checkpointtable ogg.CKPT_TABLE
REPLICAT added.
验证dml同步：(源库)
SQL> select count(*) from t_emp;
COUNT(*)
----------
28
SQL> insert into t_emp select * from t_emp;
28 rows created.
SQL> commit;
Commit complete.
目标库已同步：
SQL> select count(*) from t_emp;
COUNT(*)
----------
56
DDL同步：
SQL> drop table t_emp;(源库)
Table dropped.
SQL> desc t_emp;
ERROR:
ORA-04043: object t_emp does not exist
目标库：
SQL> desc t_emp;
ERROR:
ORA-04043: object t_emp does not exist
日志信息：
2015-10-27 15:27:24 INFO OGG-01407 Setting current schema for DDL operation to [SYS].
扫一扫
814
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
