GoldenGate学习系列6–抽取PhysicalStandby

本站文章除注明转载外，均为本站原创： 转载自love wife & love life —Roger 的Oracle技术博客

本文链接地址: GoldenGate 学习系列6–抽取Physical Standby

之前从未测试过ogg抽取Physical standby或ADG的情况，今天测试了一下，由于最近一个项目是9208的升级，需要利用OGG。因此下午利用一个vm搭建了DG顺便测试了OGG，如下是简单的步骤，供参考！

1. 主库

[ora9@killdb killdb]$ sqlplus "/as sysdba"SQL*Plus: Release 9.2.0.8.0 - Production on Mon Dec 8 22:44:32 2014Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.Connected to an idle instance.SQL> startup mountORACLE instance started.Total System Global Area 252776884 bytesFixed Size 450996 bytesVariable Size 201326592 bytesDatabase Buffers 50331648 bytesRedo Buffers 667648 bytesDatabase mounted.SQL> alter database archivelog;Database altered.SQL> alter database force logging;Database altered.

SQL> alter database add supplemental log data;Database altered.SQL> alter system set log_archive_dest_1='location=/home/ora9/arch_p';System altered.SQL> alter database open;Database altered.SQL> alter system set log_archive_start=true scope=spfile;System altered.SQL> alter system set log_archive_dest_2='SERVICE=std9 MANDATORY REOPEN=60';System altered.SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> startupORACLE instance started.Total System Global Area 252776884 bytesFixed Size 450996 bytesVariable Size 201326592 bytesDatabase Buffers 50331648 bytesRedo Buffers 667648 bytesDatabase mounted.Database opened.

2. 备份主库以及控制文件

[ora9@killdb killdb]$ rman target /Recovery Manager: Release 9.2.0.8.0 - ProductionCopyright (c) 1995, 2002, Oracle Corporation. All rights reserved.connected to target database: KILLDB (DBID=801221485)RMAN> backup database format='/home/ora9/killdb_full.bak';Starting backup at 08-DEC-14using target database controlfile instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: sid=17 devtype=DISKchannel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetincluding current SPFILE in backupsetincluding current controlfile in backupsetinput datafile fno=00001 name=/home/ora9/oradata/killdb/system01.dbfinput datafile fno=00011 name=/home/ora9/oradata/killdb/jwdb01.dbfinput datafile fno=00002 name=/home/ora9/oradata/killdb/undotbs01.dbfinput datafile fno=00004 name=/home/ora9/oradata/killdb/example01.dbfinput datafile fno=00009 name=/home/ora9/oradata/killdb/xdb01.dbfinput datafile fno=00005 name=/home/ora9/oradata/killdb/indx01.dbfinput datafile fno=00006 name=/home/ora9/oradata/killdb/odm01.dbfinput datafile fno=00008 name=/home/ora9/oradata/killdb/users01.dbfinput datafile fno=00003 name=/home/ora9/oradata/killdb/drsys01.dbfinput datafile fno=00010 name=/home/ora9/oradata/killdb/cwmlite01.dbfinput datafile fno=00007 name=/home/ora9/oradata/killdb/tools01.dbfchannel ORA_DISK_1: starting piece 1 at 08-DEC-14channel ORA_DISK_1: finished piece 1 at 08-DEC-14piece handle=/home/ora9/killdb_full.bak comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:01:06Finished backup at 08-DEC-14RMAN> backup current controlfile ;Starting backup at 08-DEC-14using target database controlfile instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: sid=12 devtype=DISKchannel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetincluding current controlfile in backupsetchannel ORA_DISK_1: starting piece 1 at 08-DEC-14channel ORA_DISK_1: finished piece 1 at 08-DEC-14piece handle=/home/ora9/product/9.2/dbs/03ppmf95_1_1 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01Finished backup at 08-DEC-14

3. 准备备库pfile并启动备库实例

--create pfileSQL> create pfile='/tmp/pfile.ora' from spfile;File created.--修改pfile*.aq_tm_processes=1*.background_dump_dest='/home/ora9/admin/std9/bdump'*.compatible='9.2.0.0.0'*.control_files='/home/ora9/oradata/std9/control01.ctl','/home/ora9/oradata/std9/control02.ctl','/home/ora9/oradata/std9/control03.ctl'*.core_dump_dest='/home/ora9/admin/std9/cdump'*.db_block_size=4096*.db_cache_size=50000000*.db_domain=''*.db_file_multiblock_read_count=8*.db_name='killdb'*.dispatchers='(PROTOCOL=TCP) (SERVICE=killdbXDB)'*.fast_start_mttr_target=300*.hash_join_enabled=FALSE*.instance_name='std9'*.java_pool_size=83886080*.job_queue_processes=10*.large_pool_size=16777216*.log_archive_dest_1='location=/home/ora9/arch_s'*.log_archive_start=TRUE*.open_cursors=300*.pga_aggregate_target=16777216*.processes=150*.query_rewrite_enabled='FALSE'*.remote_login_passwordfile='EXCLUSIVE'*.sga_max_size=100000000*.shared_pool_size=83886080*.sort_area_size=524288*.star_transformation_enabled='FALSE'*.timed_statistics=TRUE*.undo_management='AUTO'*.undo_retention=900*.undo_tablespace='UNDOTBS1'*.user_dump_dest='/home/ora9/admin/std9/udump'

说明：将其中的killdb全部修改为std9

另外创建相关的目录.步骤略.

SQL> startup nomount pfile='/tmp/pfile.ora'; ORACLE instance started.Total System Global Area 252776884 bytes Fixed Size 450996 bytes Variable Size 201326592 bytes Database Buffers 50331648 bytes Redo Buffers 667648 bytes

4. 备库restore数据文件

[ora9@killdb admin]$ rman target /Recovery Manager: Release 9.2.0.8.0 - ProductionCopyright (c) 1995, 2002, Oracle Corporation. All rights reserved.connected to target database: KILLDB (DBID=801221485)RMAN> run {2> allocate channel d1 type disk;3> set newname for datafile '/home/ora9/oradata/killdb/system01.dbf' to '/home/ora9/oradata/std9/system01.dbf' ;4> set newname for datafile '/home/ora9/oradata/killdb/undotbs01.dbf' to '/home/ora9/oradata/std9/undotbs01.dbf';5> set newname for datafile '/home/ora9/oradata/killdb/drsys01.dbf' to '/home/ora9/oradata/std9/drsys01.dbf' ;6> set newname for datafile '/home/ora9/oradata/killdb/example01.dbf' to '/home/ora9/oradata/std9/example01.dbf';7> set newname for datafile '/home/ora9/oradata/killdb/indx01.dbf' to '/home/ora9/oradata/std9/indx01.dbf' ;8> set newname for datafile '/home/ora9/oradata/killdb/odm01.dbf' to '/home/ora9/oradata/std9/odm01.dbf' ;9> set newname for datafile '/home/ora9/oradata/killdb/tools01.dbf' to '/home/ora9/oradata/std9/tools01.dbf' ;10> set newname for datafile '/home/ora9/oradata/killdb/users01.dbf' to '/home/ora9/oradata/std9/users01.dbf' ;11> set newname for datafile '/home/ora9/oradata/killdb/xdb01.dbf' to '/home/ora9/oradata/std9/xdb01.dbf' ;12> set newname for datafile '/home/ora9/oradata/killdb/cwmlite01.dbf' to '/home/ora9/oradata/std9/cwmlite01.dbf';13> set newname for datafile '/home/ora9/oradata/killdb/jwdb01.dbf' to '/home/ora9/oradata/std9/jwdb01.dbf' ;14> restore database force;15> switch datafile all;16> release channel d1;17> }using target database controlfile instead of recovery catalogallocated channel: d1channel d1: sid=14 devtype=DISKexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEStarting restore at 08-DEC-14channel d1: starting datafile backupset restorechannel d1: specifying datafile(s) to restore from backup setrestoring datafile 00001 to /home/ora9/oradata/std9/system01.dbfrestoring datafile 00002 to /home/ora9/oradata/std9/undotbs01.dbfrestoring datafile 00003 to /home/ora9/oradata/std9/drsys01.dbfrestoring datafile 00004 to /home/ora9/oradata/std9/example01.dbfrestoring datafile 00005 to /home/ora9/oradata/std9/indx01.dbfrestoring datafile 00006 to /home/ora9/oradata/std9/odm01.dbfrestoring datafile 00007 to /home/ora9/oradata/std9/tools01.dbfrestoring datafile 00008 to /home/ora9/oradata/std9/users01.dbfrestoring datafile 00009 to /home/ora9/oradata/std9/xdb01.dbfrestoring datafile 00010 to /home/ora9/oradata/std9/cwmlite01.dbfrestoring datafile 00011 to /home/ora9/oradata/std9/jwdb01.dbfchannel d1: restored backup piece 1piece handle=/home/ora9/killdb_full.bak tag=TAG20141208T230354 params=NULLchannel d1: restore completeFinished restore at 08-DEC-14datafile 1 switched to datafile copyinput datafilecopy recid=45 stamp=865812239 filename=/home/ora9/oradata/std9/system01.dbfdatafile 2 switched to datafile copyinput datafilecopy recid=46 stamp=865812239 filename=/home/ora9/oradata/std9/undotbs01.dbfdatafile 3 switched to datafile copyinput datafilecopy recid=47 stamp=865812239 filename=/home/ora9/oradata/std9/drsys01.dbfdatafile 4 switched to datafile copyinput datafilecopy recid=48 stamp=865812239 filename=/home/ora9/oradata/std9/example01.dbfdatafile 5 switched to datafile copyinput datafilecopy recid=49 stamp=865812239 filename=/home/ora9/oradata/std9/indx01.dbfdatafile 6 switched to datafile copyinput datafilecopy recid=50 stamp=865812239 filename=/home/ora9/oradata/std9/odm01.dbfdatafile 7 switched to datafile copyinput datafilecopy recid=51 stamp=865812239 filename=/home/ora9/oradata/std9/tools01.dbfdatafile 8 switched to datafile copyinput datafilecopy recid=52 stamp=865812239 filename=/home/ora9/oradata/std9/users01.dbfdatafile 9 switched to datafile copyinput datafilecopy recid=53 stamp=865812239 filename=/home/ora9/oradata/std9/xdb01.dbfdatafile 10 switched to datafile copyinput datafilecopy recid=54 stamp=865812239 filename=/home/ora9/oradata/std9/cwmlite01.dbfdatafile 11 switched to datafile copyinput datafilecopy recid=55 stamp=865812239 filename=/home/ora9/oradata/std9/jwdb01.dbfreleased channel: d1RMAN> exit

5. 停止备库

SQL> shutdown abort;ORACLE instance shut down.SQL> startup nomountORACLE instance started.Total System Global Area 252776884 bytesFixed Size 450996 bytesVariable Size 201326592 bytesDatabase Buffers 50331648 bytesRedo Buffers 667648 bytes

6. 主库创建standby controlfile

SQL> alter database create standby controlfile as '/tmp/std9.ctl';Database altered.

7. 替换备库的控制文件

cp /tmp/std9.ctl /home/ora9/oradata/std9/control01.ctlcp /tmp/std9.ctl /home/ora9/oradata/std9/control02.ctlcp /tmp/std9.ctl /home/ora9/oradata/std9/control03.ctl

说明，由于我是在同一个VM进行操作，因此这里我直接cp覆盖即可.

8. 启动备库

SQL> startup nomountORACLE instance started.Total System Global Area 252776884 bytesFixed Size 450996 bytesVariable Size 201326592 bytesDatabase Buffers 50331648 bytesRedo Buffers 667648 bytesSQL> alter database mount standby database;Database altered.

9. 修改tsnames.ora,listener.ora

--tnsnames.oraKILLDB = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = killdb) ) )std9 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = std9) ) )---listener.oraSID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /home/ora9/product/9.2/) (PROGRAM = extproc) ) (SID_DESC = (GLOBAL_DBNAME = jw) (ORACLE_HOME = /home/ora9/product/9.2) (SID_NAME = jw) ) (SID_DESC = (GLOBAL_DBNAME = killdb) (ORACLE_HOME = /home/ora9/product/9.2) (SID_NAME = killdb) ) (SID_DESC = (GLOBAL_DBNAME = std9) (ORACLE_HOME = /home/ora9/product/9.2) (SID_NAME = std9) ) )

注意，这里最好是使用静态注册.
10. rename备库数据文件

由于替换了备库的控制文件，因此我们需要rename 数据文件，否则启动dg的recover会报错。

SQL> alter system set standby_file_management=manual;System altered.SQL> alter database rename file '/home/ora9/oradata/killdb/system01.dbf' to '/home/ora9/oradata/std9/system01.dbf' ;Database altered.SQL> alter database rename file '/home/ora9/oradata/killdb/undotbs01.dbf' to '/home/ora9/oradata/std9/undotbs01.dbf';Database altered.SQL> alter database rename file '/home/ora9/oradata/killdb/drsys01.dbf' to '/home/ora9/oradata/std9/drsys01.dbf' ;alter database rename file '/home/ora9/oradata/killdb/example01.dbf' to '/home/ora9/oradata/std9/example01.dbf';Database altered.SQL>Database altered.SQL> alter database rename file '/home/ora9/oradata/killdb/indx01.dbf' to '/home/ora9/oradata/std9/indx01.dbf' ;Database altered.SQL> alter database rename file '/home/ora9/oradata/killdb/odm01.dbf' to '/home/ora9/oradata/std9/odm01.dbf' ;Database altered.SQL> alter database rename file '/home/ora9/oradata/killdb/tools01.dbf' to '/home/ora9/oradata/std9/tools01.dbf' ;Database altered.SQL> alter database rename file '/home/ora9/oradata/killdb/users01.dbf' to '/home/ora9/oradata/std9/users01.dbf' ;Database altered.SQL> alter database rename file '/home/ora9/oradata/killdb/xdb01.dbf' to '/home/ora9/oradata/std9/xdb01.dbf' ;Database altered.SQL> alter database rename file '/home/ora9/oradata/killdb/cwmlite01.dbf' to '/home/ora9/oradata/std9/cwmlite01.dbf';Database altered.SQL> alter database rename file '/home/ora9/oradata/killdb/jwdb01.dbf' to '/home/ora9/oradata/std9/jwdb01.dbf' ;Database altered.SQL> select name from v$controlfile;NAME--------------------------------------------------------------------------------/home/ora9/oradata/std9/control01.ctl/home/ora9/oradata/std9/control02.ctl/home/ora9/oradata/std9/control03.ctlSQL> select name from v$datafile;NAME--------------------------------------------------------------------------------/home/ora9/oradata/std9/system01.dbf/home/ora9/oradata/std9/undotbs01.dbf/home/ora9/oradata/std9/drsys01.dbf/home/ora9/oradata/std9/example01.dbf/home/ora9/oradata/std9/indx01.dbf/home/ora9/oradata/std9/odm01.dbf/home/ora9/oradata/std9/tools01.dbf/home/ora9/oradata/std9/users01.dbf/home/ora9/oradata/std9/xdb01.dbf/home/ora9/oradata/std9/cwmlite01.dbf/home/ora9/oradata/std9/jwdb01.dbf11 rows selected.SQL> select name from v$tempfile;no rows selectedSQL> alter system set standby_file_management=auto;System altered.

12. 修改gap获取参数(备库操作)

SQL> alter system set FAL_SERVER='killdb';System altered.SQL> alter system set FAL_CLIENT='std9';System altered.

13. 启动备库的同步

SQL> alter database recover managed standby database disconnect from session;Database altered.

上述为9208 版本的physical standby手工搭建过程，如下是诊断standby 进程ogg同步的配置。
1. 配置源端mgr

GGSCI (killdb.com) 4> create subdirsCreating subdirectories under current directory /home/ora9/ggsParameter files/home/ora9/ggs/dirprm: createdReport files /home/ora9/ggs/dirrpt: createdCheckpoint files /home/ora9/ggs/dirchk: createdProcess status files /home/ora9/ggs/dirpcs: createdSQL script files /home/ora9/ggs/dirsql: createdDatabase definitions files /home/ora9/ggs/dirdef: createdExtract data files /home/ora9/ggs/dirdat: createdTemporary files/home/ora9/ggs/dirtmp: createdVeridata files /home/ora9/ggs/dirver: createdVeridata Lock files /home/ora9/ggs/dirver/lock: createdVeridata Out-Of-Sync files /home/ora9/ggs/dirver/oos: createdVeridata Out-Of-Sync XML files /home/ora9/ggs/dirver/oosxml: createdVeridata Parameter files /home/ora9/ggs/dirver/params: createdVeridata Report files /home/ora9/ggs/dirver/report: createdVeridata Status files /home/ora9/ggs/dirver/status: createdVeridata Trace files /home/ora9/ggs/dirver/trace: createdStdout files /home/ora9/ggs/dirout: createdGGSCI (killdb.com) 3> edit param mgrport 7810dynamicportlist 7840-7849purgeoldextracts ./dirdat/*, usecheckpoints, minkeepdays 3

2. 配置源端抽取进程

GGSCI (killdb.com) 48> view param ext_stdextract ext_std userid ggs@killdb,password ggs tranlogoptions archivedlogonly tranlogoptions altarchivelogdest /home/ora9/arch_s exttrail /home/ora9/ggs/dirdat/ra discardfile ./dirrpt/exta.dsc,append, megabytes 500 table roger.t_buffer;GGSCI (killdb.com) 51> add ext ext_std,tranlog,begin nowEXTRACT added.GGSCI (killdb.com) 52> ADD EXTTRAIL /home/ora9/ggs/dirdat/ra, EXTRACT EXT_STDEXTTRAIL added.

3. 配置源端pump进程

GGSCI (killdb.com) 16> edit param dp1EXTRACT dp1RMTHOST 192.168.109.12, MGRPORT 7809 TCPBUFSIZE 5000000PASSTHRURMTTRAIL ./dirdat/r1NUMFILES 3000TABLE roger.*;~~~"dirprm/dp1.prm" [New] 7L, 129C writtenGGSCI (killdb.com) 17> add extract dp1, exttrailsource ./dirdat/raEXTRACT added.GGSCI (killdb.com) 18> add rmttrail ./dirdat/r1, extract dp1, megabytes 10RMTTRAIL added.

4. 启动源端进程

5. 配置目标端replicat进程

GGSCI (killdb.com) 13> view param rep6replicat rep6userid ggs@Roger,password AADAAAAAAAAAAADAKHEJYIFGVAKDPFZBGDFJNEQBBJRISJAAOCHHZEWCEFTCRIRCJDSHUHAJZBFDZEWC,encryptkey kasaur_keyreperror default, discarddiscardfile ./dirrpt/rep6.dsc, append, megabytes 50assumetargetdefsallownoopupdatesnumfiles 3000map roger.t_buffer, target roger.t_buffer;GGSCI (killdb.com) 5> add replicat rep6, exttrail ./dirdat/r1REPLICAT added.GGSCI (killdb.com) 15> start rep6Sending START request to MANAGER ...REPLICAT REP6 starting

测试Physical Standby的数据能否同步至目标端的10205 数据库.

—物理主库进行DML操作

SQL> select * from V$version;BANNER----------------------------------------------------------------Oracle9i Enterprise Edition Release 9.2.0.8.0 - ProductionPL/SQL Release 9.2.0.8.0 - ProductionCORE 9.2.0.8.0 ProductionTNS for Linux: Version 9.2.0.8.0 - ProductionNLSRTL Version 9.2.0.8.0 - ProductionSQL> select database_role,open_mode from v$database;DATABASE_ROLE OPEN_MODE---------------- ----------PRIMARY READ WRITESQL> conn roger/rogerConnected.SQL> insert into t_buffer select * from dba_objects where rownum commit;Commit complete.SQL> alter system switch logfile;System altered.

—物理备库

SQL> select * from V$version;BANNER----------------------------------------------------------------Oracle9i Enterprise Edition Release 9.2.0.8.0 - ProductionPL/SQL Release 9.2.0.8.0 - ProductionCORE 9.2.0.8.0 ProductionTNS for Linux: Version 9.2.0.8.0 - ProductionNLSRTL Version 9.2.0.8.0 - ProductionSQL> select database_role,open_mode from v$database;DATABASE_ROLE OPEN_MODE---------------- ----------PHYSICAL STANDBY MOUNTEDGGSCI (killdb.com) 71> stats ext_stdSending STATS request to EXTRACT EXT_STD ...Start of Statistics at 2014-12-09 01:16:25.Output to /home/ora9/ggs/dirdat/ra:Extracting from ROGER.T_BUFFER to ROGER.T_BUFFER:*** Total statistics since 2014-12-09 01:16:00 *** Total inserts9.00 Total updates0.00 Total deletes0.00 Total discards 0.00 Total operations 9.00*** Daily statistics since 2014-12-09 01:16:00 *** Total inserts9.00 Total updates0.00 Total deletes0.00 Total discards 0.00 Total operations 9.00*** Hourly statistics since 2014-12-09 01:16:00 *** Total inserts9.00 Total updates0.00 Total deletes0.00 Total discards 0.00 Total operations 9.00*** Latest statistics since 2014-12-09 01:16:00 *** Total inserts9.00 Total updates0.00 Total deletes0.00 Total discards 0.00 Total operations 9.00End of Statistics.

—OGG目标端数据库

GGSCI (killdb.com) 20> stats rep6Sending STATS request to REPLICAT REP6 ...Start of Statistics at 2014-12-09 01:16:34.Replicating from ROGER.T_BUFFER to ROGER.T_BUFFER:*** Total statistics since 2014-12-09 01:16:06 *** Total inserts 9.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 9.00*** Daily statistics since 2014-12-09 01:16:06 *** Total inserts 9.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 9.00*** Hourly statistics since 2014-12-09 01:16:06 *** Total inserts 9.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 9.00*** Latest statistics since 2014-12-09 01:16:06 *** Total inserts 9.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 9.00End of Statistics.www.killdb.com>select * from v$version where rownum conn roger/rogerConnected.www.killdb.com>select count(1) from t_buffer;COUNT(1)---------- 9www.killdb.com>

Related posts:

列删除的恢复测试 – 不要模仿
手工构造逻辑坏块一例
logical standby ORA-1119
goldengate 学习系列1–10gasm to 11gR2 asm 单向复制(DDL支持)
goldengate 学习系列2–相关配置说明