本站文章除注明转载外，均为本站原创： 转载自love wife love life —roger 的oracle技术博客 本文链接地址: goldengate 学习系列6–抽取physical standby 之前从未测试过ogg抽取physical standby或adg的情况，今天测试了一下，由于最近一个项目是9208的升级
本站文章除注明转载外，均为本站原创： 转载自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 sysdbasql*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 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:00 *** 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:00 *** 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:00 *** total inserts 9.00 total updates 0.00 total deletes 0.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-1119goldengate 学习系列1–10gasm to 11gr2 asm 单向复制(ddl支持)goldengate 学习系列2–相关配置说明