本站文章除注明转载外，均为本站原创： 转载自love wife & love life —Roger 的Oracle技术博客
之前从未测试过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 2014 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to an idle instance. SQL> startup mount 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 Database 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 immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup 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 Database mounted. Database opened.
2. 备份主库以及控制文件
[ora9@killdb killdb]$ rman target / Recovery Manager: Release 9.2.0.8.0 - Production Copyright (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-14 using target database controlfile instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=17 devtype=DISK channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset including current SPFILE in backupset including current controlfile in backupset input datafile fno=00001 name=/home/ora9/oradata/killdb/system01.dbf input datafile fno=00011 name=/home/ora9/oradata/killdb/jwdb01.dbf input datafile fno=00002 name=/home/ora9/oradata/killdb/undotbs01.dbf input datafile fno=00004 name=/home/ora9/oradata/killdb/example01.dbf input datafile fno=00009 name=/home/ora9/oradata/killdb/xdb01.dbf input datafile fno=00005 name=/home/ora9/oradata/killdb/indx01.dbf input datafile fno=00006 name=/home/ora9/oradata/killdb/odm01.dbf input datafile fno=00008 name=/home/ora9/oradata/killdb/users01.dbf input datafile fno=00003 name=/home/ora9/oradata/killdb/drsys01.dbf input datafile fno=00010 name=/home/ora9/oradata/killdb/cwmlite01.dbf input datafile fno=00007 name=/home/ora9/oradata/killdb/tools01.dbf channel ORA_DISK_1: starting piece 1 at 08-DEC-14 channel ORA_DISK_1: finished piece 1 at 08-DEC-14 piece handle=/home/ora9/killdb_full.bak comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:01:06 Finished backup at 08-DEC-14 RMAN> backup current controlfile ; Starting backup at 08-DEC-14 using target database controlfile instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=12 devtype=DISK channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset including current controlfile in backupset channel ORA_DISK_1: starting piece 1 at 08-DEC-14 channel ORA_DISK_1: finished piece 1 at 08-DEC-14 piece handle=/home/ora9/product/9.2/dbs/03ppmf95_1_1 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 08-DEC-14
3. 准备备库pfile并启动备库实例
--create pfile SQL> 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 - Production
Copyright (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 catalog
allocated channel: d1
channel d1: sid=14 devtype=DISK
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
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 08-DEC-14
channel d1: starting datafile backupset restore
channel d1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /home/ora9/oradata/std9/system01.dbf
restoring datafile 00002 to /home/ora9/oradata/std9/undotbs01.dbf
restoring datafile 00003 to /home/ora9/oradata/std9/drsys01.dbf
restoring datafile 00004 to /home/ora9/oradata/std9/example01.dbf
restoring datafile 00005 to /home/ora9/oradata/std9/indx01.dbf
restoring datafile 00006 to /home/ora9/oradata/std9/odm01.dbf
restoring datafile 00007 to /home/ora9/oradata/std9/tools01.dbf
restoring datafile 00008 to /home/ora9/oradata/std9/users01.dbf
restoring datafile 00009 to /home/ora9/oradata/std9/xdb01.dbf
restoring datafile 00010 to /home/ora9/oradata/std9/cwmlite01.dbf
restoring datafile 00011 to /home/ora9/oradata/std9/jwdb01.dbf
channel d1: restored backup piece 1
piece handle=/home/ora9/killdb_full.bak tag=TAG20141208T230354 params=NULL
channel d1: restore complete
Finished restore at 08-DEC-14
datafile 1 switched to datafile copy
input datafilecopy recid=45 stamp=865812239 filename=/home/ora9/oradata/std9/system01.dbf
datafile 2 switched to datafile copy
input datafilecopy recid=46 stamp=865812239 filename=/home/ora9/oradata/std9/undotbs01.dbf
datafile 3 switched to datafile copy
input datafilecopy recid=47 stamp=865812239 filename=/home/ora9/oradata/std9/drsys01.dbf
datafile 4 switched to datafile copy
input datafilecopy recid=48 stamp=865812239 filename=/home/ora9/oradata/std9/example01.dbf
datafile 5 switched to datafile copy
input datafilecopy recid=49 stamp=865812239 filename=/home/ora9/oradata/std9/indx01.dbf
datafile 6 switched to datafile copy
input datafilecopy recid=50 stamp=865812239 filename=/home/ora9/oradata/std9/odm01.dbf
datafile 7 switched to datafile copy
input datafilecopy recid=51 stamp=865812239 filename=/home/ora9/oradata/std9/tools01.dbf
datafile 8 switched to datafile copy
input datafilecopy recid=52 stamp=865812239 filename=/home/ora9/oradata/std9/users01.dbf
datafile 9 switched to datafile copy
input datafilecopy recid=53 stamp=865812239 filename=/home/ora9/oradata/std9/xdb01.dbf
datafile 10 switched to datafile copy
input datafilecopy recid=54 stamp=865812239 filename=/home/ora9/oradata/std9/cwmlite01.dbf
datafile 11 switched to datafile copy
input datafilecopy recid=55 stamp=865812239 filename=/home/ora9/oradata/std9/jwdb01.dbf
released channel: d1
RMAN> exit
5. 停止备库
SQL> shutdown abort; ORACLE instance shut down. SQL> startup nomount 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
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.ctl cp /tmp/std9.ctl /home/ora9/oradata/std9/control02.ctl cp /tmp/std9.ctl /home/ora9/oradata/std9/control03.ctl
说明，由于我是在同一个VM进行操作，因此这里我直接cp覆盖即可.
8. 启动备库
SQL> startup nomount 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 SQL> alter database mount standby database; Database altered.
9. 修改tsnames.ora,listener.ora
--tnsnames.ora KILLDB = (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.ora SID_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.ctl SQL> 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.dbf 11 rows selected. SQL> select name from v$tempfile; no rows selected SQL> 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 subdirs Creating subdirectories under current directory /home/ora9/ggs Parameter files /home/ora9/ggs/dirprm: created Report files /home/ora9/ggs/dirrpt: created Checkpoint files /home/ora9/ggs/dirchk: created Process status files /home/ora9/ggs/dirpcs: created SQL script files /home/ora9/ggs/dirsql: created Database definitions files /home/ora9/ggs/dirdef: created Extract data files /home/ora9/ggs/dirdat: created Temporary files /home/ora9/ggs/dirtmp: created Veridata files /home/ora9/ggs/dirver: created Veridata Lock files /home/ora9/ggs/dirver/lock: created Veridata Out-Of-Sync files /home/ora9/ggs/dirver/oos: created Veridata Out-Of-Sync XML files /home/ora9/ggs/dirver/oosxml: created Veridata Parameter files /home/ora9/ggs/dirver/params: created Veridata Report files /home/ora9/ggs/dirver/report: created Veridata Status files /home/ora9/ggs/dirver/status: created Veridata Trace files /home/ora9/ggs/dirver/trace: created Stdout files /home/ora9/ggs/dirout: created GGSCI (killdb.com) 3> edit param mgr port 7810 dynamicportlist 7840-7849 purgeoldextracts ./dirdat/*, usecheckpoints, minkeepdays 3
2. 配置源端抽取进程
GGSCI (killdb.com) 48> view param ext_std extract 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 now EXTRACT added. GGSCI (killdb.com) 52> ADD EXTTRAIL /home/ora9/ggs/dirdat/ra, EXTRACT EXT_STD EXTTRAIL added.
3. 配置源端pump进程
GGSCI (killdb.com) 16> edit param dp1 EXTRACT dp1 RMTHOST 192.168.109.12, MGRPORT 7809 TCPBUFSIZE 5000000 PASSTHRU RMTTRAIL ./dirdat/r1 NUMFILES 3000 TABLE roger.*; ~ ~ ~ "dirprm/dp1.prm" [New] 7L, 129C written GGSCI (killdb.com) 17> add extract dp1, exttrailsource ./dirdat/ra EXTRACT added. GGSCI (killdb.com) 18> add rmttrail ./dirdat/r1, extract dp1, megabytes 10 RMTTRAIL added.
4. 启动源端进程
5. 配置目标端replicat进程
GGSCI (killdb.com) 13> view param rep6 replicat rep6 userid ggs@Roger,password AADAAAAAAAAAAADAKHEJYIFGVAKDPFZBGDFJNEQBBJRISJAAOCHHZEWCEFTCRIRCJDSHUHAJZBFDZEWC,encryptkey kasaur_key reperror default, discard discardfile ./dirrpt/rep6.dsc, append, megabytes 50 assumetargetdefs allownoopupdates numfiles 3000 map roger.t_buffer, target roger.t_buffer; GGSCI (killdb.com) 5> add replicat rep6, exttrail ./dirdat/r1 REPLICAT added. GGSCI (killdb.com) 15> start rep6 Sending 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 - Production PL/SQL Release 9.2.0.8.0 - Production CORE 9.2.0.8.0 Production TNS for Linux: Version 9.2.0.8.0 - Production NLSRTL Version 9.2.0.8.0 - Production SQL> select database_role,open_mode from v$database; DATABASE_ROLE OPEN_MODE ---------------- ---------- PRIMARY READ WRITE SQL> conn roger/roger Connected. SQL> insert into t_buffer select * from dba_objects where rownum < 10; 9 rows created. SQL> 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 - Production PL/SQL Release 9.2.0.8.0 - Production CORE 9.2.0.8.0 Production TNS for Linux: Version 9.2.0.8.0 - Production NLSRTL Version 9.2.0.8.0 - Production SQL> select database_role,open_mode from v$database; DATABASE_ROLE OPEN_MODE ---------------- ---------- PHYSICAL STANDBY MOUNTED GGSCI (killdb.com) 71> stats ext_std Sending 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.00 End of Statistics.
—OGG目标端数据库
GGSCI (killdb.com) 20> stats rep6 Sending 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.00 End of Statistics. www.killdb.com>select * from v$version where rownum < 3; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Prod PL/SQL Release 10.2.0.5.0 - Production www.killdb.com>conn roger/roger Connected. www.killdb.com>select count(1) from t_buffer; COUNT(1) ---------- 9 www.killdb.com>
Related posts:
