生产端为一套RAC,容灾端为单库,数据量几个TB,异地容灾,数据通过异地硬盘传输,网络速度太慢
0、备份原来的/goldengate目录,包括备份参数文件
tar -cvf ogg.tar /goldengate
cp /goldengate/dirprm /tmp/dirprm
cp GLOBALS /tmp/GLOBALS
$ more GLOBALS
GGSCHEMA goldengate
0.1、 卸载原来的DDL配置
如果之前曾经安装过ddl,需要重新进行安装,则需要先将ddl的一些组件卸载掉,然后再重新安装:
依次在OGG的根目录执行
1)切换到Goldengate安装目录下,并且按照顺序以SYS用户依次执行以下脚本:
2)SQLPLUS SYS/ORACLE@ORCL @./ddl_disable.sql
3)SQL>/goldengate/ddl_remove.sql
4)SQL>/goldengate/marker_remove.sql
注:在执行上述脚本的时候,会提示输入安装时所指定的schema名称。
0.2、 删除原来的OGG软件
rmdir /goldengate
mkdir /goldengate
1、安装OGG软件
ftp 192.168.2.3
bin
prompt
cd
put ggs11.2.1.0.4_HPUX_ia64_ora10g_64bit.tar
通过SRT登录到192.168.2.3
su - oracle
cd /goldengate
tar -xvf ggs11.2.1.0.4_HPUX_ia64_ora10g_64bit.tar
./ggsci
create subdirs
2、数据库建表空间和用户
create tablespace goldengate datafile '/xx/goldengate01.dbf' size 10240M ; -----不需要做
create tablespace goldengate datafile '/dev/vgdlwwqxp2/rvgdlwwqxp_20g_469' size 10240M ; -----不需要做
create user goldengate identified by password_10 default tablespace goldengate quota unlimited on goldengate; -----不需要做
grant CONNECT, RESOURCE ,DBA to goldengate;-----不需要
3、数据库开归档模式和强制日志模式
Select log_mode from v$database;
开启归档模式:
shutdown immediate; -----不需要
startup mount; -----不需要
alter database archivelog;-----不需要
alter database open; -----不需要
检查强制日志情况,使用以下SQL语句:
SELECT force_logging FROM v$database;
开启强制日志模式:
alter database force logging;
4、数据库开附加日志
检查附加日志情况,使用以下sql语句检查数据库附加日志的打开状况:
Select
SUPPLEMENTAL_LOG_DATA_MIN
,SUPPLEMENTAL_LOG_DATA_PK
,SUPPLEMENTAL_LOG_DATA_UI
,SUPPLEMENTAL_LOG_DATA_FK
,SUPPLEMENTAL_LOG_DATA_ALL from v$database;
打开数据库附加日志,打开附加日志并切换日志(保证Online redo log和Archive log一致)
alter database add supplemental log data ;
alter database add supplemental log data (primary key, unique,foreign key) columns;
alter system switch logfile;
回退操作:如果出现问题,可以通过以下语句进行回退:
alter database drop supplemental log data ;
alter database drop supplemental log data (primary key, unique,foreign key) columns;
确认附加日志情况,使用以下sql语句检查数据库附加日志的打开状况:
Select
SUPPLEMENTAL_LOG_DATA_MIN
,SUPPLEMENTAL_LOG_DATA_PK
,SUPPLEMENTAL_LOG_DATA_UI
,SUPPLEMENTAL_LOG_DATA_FK
,SUPPLEMENTAL_LOG_DATA_ALL from v$database;
注:确保最小附加日志,pk,uk,fk附加日志打开。而all columns的附加日志关闭;
如果all columns的附加日志打开的话,则需要使用以下语句予以关闭:
alter database drop supplemental log data (ALL) columns;
5、关闭数据库的recyclebin (仅实施DDL时进行配置)
SQL> show parameter recyclebin
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string OFF
SQL>
如不是off,需要关闭recyclebin:
SQL>alter system set recyclebin=off scope=both;
如果需要回退,打开recyclebin的话,可以使用以下语句:
SQL>alter system set recyclebin=off scope=both;
6、开表级补全日志(仅实施DML时进行配置,开启DDL复制不需要做)
./ggsci
DBLOGIN USERID goldengate, PASSWORD password_10
ADD TRANDATA sgwwqxp.*
INFO TRANDATA SGwwqxp.A_RCVBL_ENTRY
ADD TRANDATA SGwwqxp.A_RCVBL_ENTRY
delete trandata SGwwqxp.A_RCVBL_ENTRY
ADD TRANDATA SGwwqxp.A_RCVBL_ENTRY
7、编辑GLOBALS参数文件
这里直接建立在goldengate用户下:
Ggsci>EDIT PARAMS ./GLOBALS --在该文件中添加以下内容
GGSCHEMA goldengate --指定的进行DDL复制的数据库用户
8、配置管理进程
./ggsci
ENCRYPT PASSWORD password_10 blowfish encryptkey default ----获取OGG密文密码
AACAAAAAAAAAAANAPATACEHBIGQGCFZCOBTATGXCDGTIUISJ
./ggsci
EDIT PARAMS MGR
PORT 7839
DYNAMICPORTLIST 7840-7914
USERID goldengate, PASSWORD AACAAAAAAAAAAANAPATACEHBIGQGCFZCOBTATGXCDGTIUISJ, ENCRYPTKEY default ---密码需要重新生成
AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 7
PURGEDDLHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10
PURGEMARKERHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45
start mgr
info all
9、配置抽取进程
./ggsci
DBLOGIN USERID goldengate, PASSWORD password_10
EXTRACT extoa
setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
USERID goldengate, PASSWORD AACAAAAAAAAAAANAPATACEHBIGQGCFZCOBTATGXCDGTIUISJ, ENCRYPTKEY default
GETTRUNCATES
DDL &
INCLUDE MAPPED OBJTYPE 'table' &
INCLUDE MAPPED OBJTYPE 'index' &
EXCLUDE OPTYPE COMMENT
DDLOPTIONS NOCROSSRENAME REPORT
DDLOPTIONS ADDTRANDATA REPORT
REPORTCOUNT EVERY 1 MINUTES, RATE
DISCARDFILE ./dirrpt/extoa.dsc,APPEND,MEGABYTES 1024
THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 90000 IOLATENCY 180000
DBOPTIONS ALLOWUNUSEDCOLUMN
WARNLONGTRANS 2h,CHECKINTERVAL 3m
EXTTRAIL ./dirdat/oa
TRANLOGOPTIONS EXCLUDEUSER goldengate
FETCHOPTIONS USESNAPSHOT
TRANLOGOPTIONS CONVERTUCS2CLOBS
TRANLOGOPTIONS altarchivelogdest primary instance dcwwqxp1 /backup1 altarchivelogdest instance dcwwqxp2 /backup2
table CIM.*;
table EPBI.*;
table EPBI_DXP.*;
table PSDSS_BUFF.*;
table PSDSS_DCHK.*;
table PSDSS_JCJK.*;
table PSDSS_MID.*;
table PSDSS_MID_1.*;
table wwqxpYQ.*;
table MW_ANHUI.*;
table MW_IQ.*;
table MW_ZTK.*;
table MW_APP.*;
table MW_RTM.*;
table MW_SYS.*;
table STAT.*;
添加相应的进程和队列文件
add extract extoa,tranlog, threads 2,begin now
add exttrail ./dirdat/oa,extract extoa,megabytes 100
start extsa
info all
info *
10、配置传输进程
./ggsci
DBLOGIN USERID goldengate, PASSWORD password_10
extract dpeoa
passthru
rmthost 192.168.2.18, mgrport 7839,compress
rmttrail ./dirdat/oa
table CIM.*;
table EPBI.*;
table EPBI_DXP.*;
table PSDSS_BUFF.*;
table PSDSS_DCHK.*;
table PSDSS_JCJK.*;
table PSDSS_MID.*;
table PSDSS_MID_1.*;
table wwqxpYQ.*;
table MW_ANHUI.*;
table MW_IQ.*;
table MW_ZTK.*;
table MW_APP.*;
table MW_RTM.*;
table MW_SYS.*;
table STAT.*;
添加相应的进程和队列文件
add ext dpeoa,exttrailsource ./dirdat/oa
add rmttrail ./dirdat/oa,ext dpeoa,megabytes 100
start dpeoa
info all
info *
传输进程需要等到容灾端管理进程配置好才可以启动
11、配置DDL复制
OGG的DDL对象安装时不能有运行的sessoin存在,请DBA执行停机清理或者杀死所有数据库中的Session。
建议有条件先执行停止业务,并关闭Oracle的Listener。
注:时间大约需要30分钟左右,在这段时间需要完成下面所有操作步骤。此后的所有步骤,都可以在业务正常运行时进行操作。
./ggsci
stop mgr
stop extsa
stop dpesa
---在安装DDL脚本时,应该停止所有OGG进程---
sqlplus "/ as sysdba"
SQL> @marker_setup.sql
Enter GoldenGate schema name:goldengate
SQL> @ddl_setup.sql
Enter GoldenGate schema name:goldengate
SQL> @role_setup.sql
Grant this role to each user assigned to the Extract, Replicat, GGSCI, and Manager processes, by using the following SQL command:
GRANT GGS_GGSUSER_ROLE TO <loggedUser>
where <loggedUser> is the user assigned to the GoldenGate processes.
注意这里的提示:需要手工将这个GGS_GGSUSER_ROLE指定给extract所使用的数据库用户(即参数文件里面通过userid指定的用户),可以到sqlplus下执行类似的sql:
GRANT GGS_GGSUSER_ROLE TO goldengate;
注:这里的goldengate是extract使用的用户。如果你有多个extract,使用不同的数据库用户,则需要重述以上过程全部赋予GGS_GGSUSER_ROLE权限。
运行以下脚本,使触发器生效
SQL> @ddl_enable.sql
启动OGG进程
start mgr
start *
9、RMAN备份数据库
备份数据文件
more rman.sh
rman target / cmdfile=/oggtmp/rmanbak/backdatafile.sql
more backdatafile.sql
run
{
allocate channel d1 type disk maxpiecesize 10G;
allocate channel d2 type disk maxpiecesize 10G;
allocate channel d3 type disk maxpiecesize 10G;
allocate channel d4 type disk maxpiecesize 10G;
allocate channel d5 type disk maxpiecesize 10G;
allocate channel d6 type disk maxpiecesize 10G;
allocate channel d7 type disk maxpiecesize 10G;
allocate channel d8 type disk maxpiecesize 10G;
backup format '/oggtmp/rmanbak/wwqxp_full_%s_%p.dbf' database;
sql 'alter system switch logfile';
sql 'alter system switch logfile';
sql 'alter system switch logfile';
sql 'alter system switch logfile';
sql 'alter system switch logfile';
sql 'alter system switch logfile';
sql 'alter system switch logfile';
release channel d1;
release channel d2;
release channel d3;
release channel d4;
release channel d5;
release channel d6;
release channel d7;
release channel d8;
}
备份归档文件
SQL>set line 200
SQL>select * from Gv$log;
SQL> select inst_id,group#,thread#,sequence#,bytes,members,archived,status,to_char(FIRST_CHANGE#),FIRST_TIMe from Gv$log;
INST_ID GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS TO_CHAR(FIRST_CHANGE#) FIRST_TIM
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ---------------------------------------- ---------
2 1 1 7417 524288000 2 YES INACTIVE 12620301743415 03-JAN-14
2 2 1 7418 524288000 2 NO CURRENT 12620301747657 03-JAN-14
2 3 1 7415 524288000 2 YES INACTIVE 12620301742955 03-JAN-14
2 4 1 7416 524288000 2 YES INACTIVE 12620301743085 03-JAN-14
2 5 2 9185 524288000 2 NO CURRENT 12620301748197 03-JAN-14
2 6 2 9182 524288000 2 YES INACTIVE 12620301746544 03-JAN-14
2 7 2 9183 524288000 2 YES INACTIVE 12620301746591 03-JAN-14
2 8 2 9184 524288000 2 YES INACTIVE 12620301747299 03-JAN-14
1 1 1 7417 524288000 2 YES INACTIVE 12620301743415 03-JAN-14
1 2 1 7418 524288000 2 NO CURRENT 12620301747657 03-JAN-14
1 3 1 7415 524288000 2 YES INACTIVE 12620301742955 03-JAN-14
1 4 1 7416 524288000 2 YES INACTIVE 12620301743085 03-JAN-14
1 5 2 9185 524288000 2 NO CURRENT 12620301748197 03-JAN-14
1 6 2 9182 524288000 2 YES INACTIVE 12620301746544 03-JAN-14
1 7 2 9183 524288000 2 YES INACTIVE 12620301746591 03-JAN-14
1 8 2 9184 524288000 2 YES INACTIVE 12620301747299 03-JAN-14
SQL> set line 200
SQL> select inst_id,group#,thread#,sequence#,bytes,members,archived,status,to_char(FIRST_CHANGE#),FIRST_TIMe from Gv$log;
INST_ID GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS TO_CHAR(FIRST_CHANGE#) FIRST_TIM
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ---------------------------------------- ---------
2 1 1 7537 524288000 2 NO CURRENT 12620341389385 04-JAN-14
2 2 1 7534 524288000 2 YES ACTIVE 12620341384329 04-JAN-14
2 3 1 7535 524288000 2 YES ACTIVE 12620341386860 04-JAN-14
2 4 1 7536 524288000 2 YES ACTIVE 12620341387356 04-JAN-14
2 5 2 9253 524288000 2 YES ACTIVE 12620341370135 04-JAN-14
2 6 2 9254 524288000 2 YES ACTIVE 12620341377321 04-JAN-14
2 7 2 9255 524288000 2 YES ACTIVE 12620341386918 04-JAN-14
2 8 2 9256 524288000 2 NO CURRENT 12620341389031 04-JAN-14
1 1 1 7537 524288000 2 NO CURRENT 12620341389385 04-JAN-14
1 2 1 7534 524288000 2 YES ACTIVE 12620341384329 04-JAN-14
1 3 1 7535 524288000 2 YES ACTIVE 12620341386860 04-JAN-14
INST_ID GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS TO_CHAR(FIRST_CHANGE#) FIRST_TIM
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ---------------------------------------- ---------
1 4 1 7536 524288000 2 YES ACTIVE 12620341387356 04-JAN-14
1 5 2 9253 524288000 2 YES ACTIVE 12620341370135 04-JAN-14
1 6 2 9254 524288000 2 YES ACTIVE 12620341377321 04-JAN-14
1 7 2 9255 524288000 2 YES ACTIVE 12620341386918 04-JAN-14
1 8 2 9256 524288000 2 NO CURRENT 12620341389031 04-JAN-14
16 rows selected.
SQL>
INST_ID GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS TO_CHAR(FIRST_CHANGE#) FIRST_TIM
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ---------------------------------------- ---------
2 1 1 7537 524288000 2 NO CURRENT 12620341389385 04-JAN-14
2 2 1 7534 524288000 2 YES ACTIVE 12620341384329 04-JAN-14
2 3 1 7535 524288000 2 YES ACTIVE 12620341386860 04-JAN-14
2 4 1 7536 524288000 2 YES ACTIVE 12620341387356 04-JAN-14
2 5 2 9253 524288000 2 YES ACTIVE 12620341370135 04-JAN-14
2 6 2 9254 524288000 2 YES ACTIVE 12620341377321 04-JAN-14
2 7 2 9255 524288000 2 YES ACTIVE 12620341386918 04-JAN-14
2 8 2 9256 524288000 2 NO CURRENT 12620341389031 04-JAN-14
1 1 1 7537 524288000 2 NO CURRENT 12620341389385 04-JAN-14
1 2 1 7534 524288000 2 YES ACTIVE 12620341384329 04-JAN-14
1 3 1 7535 524288000 2 YES ACTIVE 12620341386860 04-JAN-14
INST_ID GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS TO_CHAR(FIRST_CHANGE#) FIRST_TIM
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ---------------------------------------- ---------
1 4 1 7536 524288000 2 YES ACTIVE 12620341387356 04-JAN-14
1 5 2 9253 524288000 2 YES ACTIVE 12620341370135 04-JAN-14
1 6 2 9254 524288000 2 YES ACTIVE 12620341377321 04-JAN-14
1 7 2 9255 524288000 2 YES ACTIVE 12620341386918 04-JAN-14
1 8 2 9256 524288000 2 NO CURRENT 12620341389031 04-JAN-14
16 rows selected.
SQL>
SQL> set line 200
SQL> select inst_id,group#,thread#,sequence#,bytes,members,archived,status,to_char(FIRST_CHANGE#),FIRST_TIMe from Gv$log;
INST_ID GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS TO_CHAR(FIRST_CHANGE#) FIRST_TIM
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ---------------------------------------- ---------
1 1 1 7541 524288000 2 YES INACTIVE 12620341953012 04-JAN-14
1 2 1 7542 524288000 2 YES INACTIVE 12620342069182 04-JAN-14
1 3 1 7543 524288000 2 YES ACTIVE 12620342187234 04-JAN-14
1 4 1 7544 524288000 2 NO CURRENT 12620342309187 04-JAN-14
1 5 2 9257 524288000 2 YES INACTIVE 12620341618880 04-JAN-14
1 6 2 9258 524288000 2 YES INACTIVE 12620341734410 04-JAN-14
1 7 2 9259 524288000 2 YES INACTIVE 12620341995892 04-JAN-14
1 8 2 9260 524288000 2 NO CURRENT 12620342199395 04-JAN-14
2 1 1 7541 524288000 2 YES INACTIVE 12620341953012 04-JAN-14
2 2 1 7542 524288000 2 YES INACTIVE 12620342069182 04-JAN-14
2 3 1 7543 524288000 2 YES ACTIVE 12620342187234 04-JAN-14
INST_ID GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS TO_CHAR(FIRST_CHANGE#) FIRST_TIM
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ---------------------------------------- ---------
2 4 1 7544 524288000 2 NO CURRENT 12620342309187 04-JAN-14
2 5 2 9257 524288000 2 YES INACTIVE 12620341618880 04-JAN-14
2 6 2 9258 524288000 2 YES INACTIVE 12620341734410 04-JAN-14
2 7 2 9259 524288000 2 YES INACTIVE 12620341995892 04-JAN-14
2 8 2 9260 524288000 2 NO CURRENT 12620342199395 04-JAN-14
16 rows selected.
SQL>
12620341618880
获取不活动的已归档日志的最后一个SCN号,如果有多个已归档的INACTIVE的组,取最大的FIRST_CHANGE#,这里取218412
more rmanlog.sh
rman target / cmdfile=/oggtmp/archbak/backarchlog.sql
more backarchlog.sql
run
{
allocate channel d1 type disk maxpiecesize 10G;
allocate channel d2 type disk maxpiecesize 10G;
sql 'alter system switch logfile';
sql 'alter system switch logfile';
sql 'alter system switch logfile';
sql 'alter system switch logfile';
sql 'alter system switch logfile';
sql 'alter system switch logfile';
sql 'alter system switch logfile';
sql 'alter system archive log current';
backup format '/oggtmp/archbak/wwqxp_archive_%s_%p.dbf' archivelog from time "sysdate-2";
release channel d1;
release channel d2;
}
备份控制文件
backup current controlfile format '/oggtmp/archbak/wwqxp_controlfil_%s_%p.dbf';
备份参数文件
create pfile='/oggtemp/rmanbak/initwwqxp.0816' from spfile;
备份数据文件名称
sqlplus /nolog
spool /oggtmp/0814/file0816.txt
select file_id,file_name from dba_data_files order by file_id asc;
spool /oggtmp/0814/tempfile0816.txt
select file_id,tablespace_name,file_name from dba_temp_files;
spool /oggtmp/0814/redofile0816.txt
select group#,status,member from gv$logfile;
10、ftp传输rman备份集到移动硬盘
ftp 192.168.2.3
bin
prmopt
get *.dbf
11、ftp传输rman备份集到灾备服务器
ftp 192.168.2.18
bin
prmopt
put *.dbf
12、恢复数据库
1)恢复参数文件
startup nomount pfile='/oracle/app/product/10.2.0/dbs/initpmdb.ora';
2)恢复控制文件
restore controlfile from '/oggtmp/archbak/wwqxp_controlfil_%s_%p.dbf';
alter database mount;
3)注册RMAN备份集
catalog backuppiece '/rmanbak/wwqxp_archivelog_24230_1.dbf';
catalog backuppiece '/rmanbak/wwqxp_archivelog_24231_1.dbf';
catalog backuppiece '/rmanbak/wwqxp_archivelog_24232_1.dbf';
catalog backuppiece '/rmanbak/wwqxp_archivelog_24233_1.dbf';
catalog backuppiece '/rmanbak/wwqxp_archivelog_24240_1.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24223_1.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24223_10.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24223_11.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24223_12.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24223_13.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24223_14.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24223_15.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24223_16.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24223_17.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24223_18.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24223_19.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24223_2.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24223_3.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24223_4.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24223_5.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24223_6.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24223_7.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24223_8.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24223_9.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24224_1.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24224_10.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24224_11.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24224_12.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24224_13.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24224_14.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24224_15.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24224_16.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24224_17.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24224_18.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24224_19.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24224_2.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24224_3.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24224_4.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24224_5.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24224_6.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24224_7.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24224_8.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24224_9.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24225_1.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24225_10.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24225_11.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24225_12.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24225_13.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24225_14.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24225_15.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24225_16.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24225_17.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24225_18.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24225_2.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24225_3.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24225_4.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24225_5.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24225_6.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24225_7.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24225_8.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24225_9.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24226_1.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24227_1.dbf';
4)、恢复数据文件,dba_data_files输出的数据文件
run{
allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;
allocate channel d5 type disk;
allocate channel d6 type disk;
restore database;
switch datafile all;
release channel d1;
release channel d2;
release channel d3;
release channel d4;
release channel d5;
release channel d6;
}
more recover.sh
rman target / cmdfile=/wwqxpdata_20131010/scripts/recoverfile.sql
run{
allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;
allocate channel d5 type disk;
allocate channel d6 type disk;
set until scn=12432504957403;
recover database;
release channel d1;
release channel d2;
release channel d3;
release channel d4;
release channel d5;
release channel d6;
}
6)更改redo日志文件路径
7)更改临时表空间文件路径
8)打开数据库
alter database open resetlogs;
9)容灾端数据库禁用JOB、触发器、外键
在dba_jobs中,有些job/schedule,会在复制对象中产生DML的操作,必须禁用掉,否则会造成DML语句在目标端的重复执行,
注:在目标端恢复的时候,可以在pfile文件中将job_queue_processes设置为0,然后将带有dml的job停掉以后,再修改job_queue_processes参数至正常值,如job_queue_processes=10;
declare
v_sql varchar2(2000);
CURSOR c_trigger IS SELECT 'alter trigger '||owner||'.'||trigger_name||' disable' from
dba_triggers where owner in (
'SGPM','SGPM_OUT','AMBER','WR_AMBER','OWF_MGR','CZK','KBSD', 'OCS'); -----用户名需要改
BEGIN
OPEN c_trigger;
LOOP
FETCH c_trigger INTO v_sql;
EXIT WHEN c_trigger%NOTFOUND;
execute immediate v_sql;
end loop;
close c_trigger;
end;
/
declare
v_sql varchar2(2000);
CURSOR c_trigger IS SELECT 'alter table '||owner||'.'||table_name||' disable constraint '||constraint_name
from dba_constraints where constraint_type='R' and
owner in
'SGPM','SGPM_OUT','AMBER','WR_AMBER','OWF_MGR','CZK','KBSD', 'OCS'); -----用户名需要改
BEGIN
OPEN c_trigger;
LOOP
FETCH c_trigger INTO v_sql;
EXIT WHEN c_trigger%NOTFOUND;
execute immediate v_sql;
end loop;
close c_trigger;
end;
-----------trigger-----------------------------
步骤九:禁用trigger
declare
v_sql varchar2(2000);
CURSOR c_trigger IS SELECT 'alter trigger '||owner||'.'||trigger_name||' disable' from dba_triggers
where owner in ('AMBER','CZK','KBSD','OCS','OWF_MGR','SGPM','SGPM_OUT','WF_AMBER'); -----用户名需要改
BEGIN
OPEN c_trigger;
LOOP
FETCH c_trigger INTO v_sql;
EXIT WHEN c_trigger%NOTFOUND;
execute immediate v_sql;
end loop;
close c_trigger;
end;
/
------------------job-----------------------
步骤十:禁用job
在配置pfile文件时,将JOB_QUEUE_PROCESSES参数改为0.或执行以下包。
alter system set JOB_QUEUE_PROCESSES=0;
begin
exec dbms_job.broken(21,true);
end;
/
---------------scheduler------------------
步骤十一:禁用scheduler
declare
v_sql varchar2(2000);
CURSOR c_scheduler IS SELECT 'exec dbms_scheduler.drop_job(' || '''' || job_name || '''' || ');' from dba_scheduler_jobs
where owner in ('AMBER','CZK','KBSD','OCS','OWF_MGR','SGPM','SGPM_OUT','WF_AMBER'); -----用户名需要改
BEGIN
OPEN c_scheduler;
LOOP
FETCH c_scheduler INTO v_sql;
EXIT WHEN c_scheduler%NOTFOUND;
--execute immediate v_sql;
dbms_output.put_line(v_sql);
end loop;
close c_scheduler;
end;
/
exec dbms_scheduler.drop_job('ZW_DXJK_JL_1');
-----------------外键---------------------
步骤十二:禁用外键
declare
v_sql varchar2(2000);
CURSOR c_cons IS SELECT 'alter table '||owner||'.'||table_name||' disable constraint '||constraint_name from
dba_constraints where constraint_type='R' and owner in ('AMBER','CZK','KBSD','OCS','OWF_MGR','SGPM','SGPM_OUT','WF_AMBER'); -----用户名需要改
BEGIN
OPEN c_cons;
LOOP
FETCH c_cons INTO v_sql;
EXIT WHEN c_cons%NOTFOUND;
execute immediate v_sql;
end loop;
close c_cons;
end;
/
12、配置容灾端数据库
---这步在RMAN恢复生产库时,已经恢复生产库的表空间和用户,不需要再做----
create tablespace goldengate datafile '/xx/goldengate01.dbf' size 10240M ;
create user goldengate identified by password_10 default tablespace goldengate quota unlimited on goldengate;
grant CONNECT, RESOURCE ,DBA to goldengate;
13、安装OGG软件、配置管理进程、复制进程
1)安装OGG软件
su - oracle
cd /goldengate
tar -xvf ggs_AIX_ia64_ora10g_64bit.tar
./ggsci
create subdirs
2)配置管理进程
./ggsci
EDIT PARAMS MGR
PORT 7839
DYNAMICPORTLIST 7840-7914
AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 30
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45
start mgr
info all
3)配置复制进程
dblogin userid goldengate,password password_10
add replicat repya checkpointtable goldengate.checktablea,exttrail ./dirdat/ya
add checkpointtable goldengate.checktablea
edit params repya
REPLICAT repya
setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
USERID goldengate, PASSWORD AACAAAAAAAAAAANAPATACEHBIGQGCFZCOBTATGXCDGTIUISJ, ENCRYPTKEY default
REPORT AT 01:59
REPORTCOUNT EVERY 30 MINUTES, RATE
REPERROR DEFAULT, discard
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/repya.dsc, APPEND, MEGABYTES 1024
DISCARDROLLOVER AT 02:30
GETTRUNCATES
ALLOWNOOPUPDATES
DBOPTIONS DEFERREFCONST
map SGwwqxp.* , target SGwwqxp.*;
start replicat repya,aftercsn ******
0、备份原来的/goldengate目录,包括备份参数文件
tar -cvf ogg.tar /goldengate
cp /goldengate/dirprm /tmp/dirprm
cp GLOBALS /tmp/GLOBALS
$ more GLOBALS
GGSCHEMA goldengate
0.1、 卸载原来的DDL配置
如果之前曾经安装过ddl,需要重新进行安装,则需要先将ddl的一些组件卸载掉,然后再重新安装:
依次在OGG的根目录执行
1)切换到Goldengate安装目录下,并且按照顺序以SYS用户依次执行以下脚本:
2)SQLPLUS SYS/ORACLE@ORCL @./ddl_disable.sql
3)SQL>/goldengate/ddl_remove.sql
4)SQL>/goldengate/marker_remove.sql
注:在执行上述脚本的时候,会提示输入安装时所指定的schema名称。
0.2、 删除原来的OGG软件
rmdir /goldengate
mkdir /goldengate
1、安装OGG软件
ftp 192.168.2.3
bin
prompt
cd
put ggs11.2.1.0.4_HPUX_ia64_ora10g_64bit.tar
通过SRT登录到192.168.2.3
su - oracle
cd /goldengate
tar -xvf ggs11.2.1.0.4_HPUX_ia64_ora10g_64bit.tar
./ggsci
create subdirs
2、数据库建表空间和用户
create tablespace goldengate datafile '/xx/goldengate01.dbf' size 10240M ; -----不需要做
create tablespace goldengate datafile '/dev/vgdlwwqxp2/rvgdlwwqxp_20g_469' size 10240M ; -----不需要做
create user goldengate identified by password_10 default tablespace goldengate quota unlimited on goldengate; -----不需要做
grant CONNECT, RESOURCE ,DBA to goldengate;-----不需要
3、数据库开归档模式和强制日志模式
Select log_mode from v$database;
开启归档模式:
shutdown immediate; -----不需要
startup mount; -----不需要
alter database archivelog;-----不需要
alter database open; -----不需要
检查强制日志情况,使用以下SQL语句:
SELECT force_logging FROM v$database;
开启强制日志模式:
alter database force logging;
4、数据库开附加日志
检查附加日志情况,使用以下sql语句检查数据库附加日志的打开状况:
Select
SUPPLEMENTAL_LOG_DATA_MIN
,SUPPLEMENTAL_LOG_DATA_PK
,SUPPLEMENTAL_LOG_DATA_UI
,SUPPLEMENTAL_LOG_DATA_FK
,SUPPLEMENTAL_LOG_DATA_ALL from v$database;
打开数据库附加日志,打开附加日志并切换日志(保证Online redo log和Archive log一致)
alter database add supplemental log data ;
alter database add supplemental log data (primary key, unique,foreign key) columns;
alter system switch logfile;
回退操作:如果出现问题,可以通过以下语句进行回退:
alter database drop supplemental log data ;
alter database drop supplemental log data (primary key, unique,foreign key) columns;
确认附加日志情况,使用以下sql语句检查数据库附加日志的打开状况:
Select
SUPPLEMENTAL_LOG_DATA_MIN
,SUPPLEMENTAL_LOG_DATA_PK
,SUPPLEMENTAL_LOG_DATA_UI
,SUPPLEMENTAL_LOG_DATA_FK
,SUPPLEMENTAL_LOG_DATA_ALL from v$database;
注:确保最小附加日志,pk,uk,fk附加日志打开。而all columns的附加日志关闭;
如果all columns的附加日志打开的话,则需要使用以下语句予以关闭:
alter database drop supplemental log data (ALL) columns;
5、关闭数据库的recyclebin (仅实施DDL时进行配置)
SQL> show parameter recyclebin
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string OFF
SQL>
如不是off,需要关闭recyclebin:
SQL>alter system set recyclebin=off scope=both;
如果需要回退,打开recyclebin的话,可以使用以下语句:
SQL>alter system set recyclebin=off scope=both;
6、开表级补全日志(仅实施DML时进行配置,开启DDL复制不需要做)
./ggsci
DBLOGIN USERID goldengate, PASSWORD password_10
ADD TRANDATA sgwwqxp.*
INFO TRANDATA SGwwqxp.A_RCVBL_ENTRY
ADD TRANDATA SGwwqxp.A_RCVBL_ENTRY
delete trandata SGwwqxp.A_RCVBL_ENTRY
ADD TRANDATA SGwwqxp.A_RCVBL_ENTRY
7、编辑GLOBALS参数文件
这里直接建立在goldengate用户下:
Ggsci>EDIT PARAMS ./GLOBALS --在该文件中添加以下内容
GGSCHEMA goldengate --指定的进行DDL复制的数据库用户
8、配置管理进程
./ggsci
ENCRYPT PASSWORD password_10 blowfish encryptkey default ----获取OGG密文密码
AACAAAAAAAAAAANAPATACEHBIGQGCFZCOBTATGXCDGTIUISJ
./ggsci
EDIT PARAMS MGR
PORT 7839
DYNAMICPORTLIST 7840-7914
USERID goldengate, PASSWORD AACAAAAAAAAAAANAPATACEHBIGQGCFZCOBTATGXCDGTIUISJ, ENCRYPTKEY default ---密码需要重新生成
AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 7
PURGEDDLHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10
PURGEMARKERHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45
start mgr
info all
9、配置抽取进程
./ggsci
DBLOGIN USERID goldengate, PASSWORD password_10
EXTRACT extoa
setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
USERID goldengate, PASSWORD AACAAAAAAAAAAANAPATACEHBIGQGCFZCOBTATGXCDGTIUISJ, ENCRYPTKEY default
GETTRUNCATES
DDL &
INCLUDE MAPPED OBJTYPE 'table' &
INCLUDE MAPPED OBJTYPE 'index' &
EXCLUDE OPTYPE COMMENT
DDLOPTIONS NOCROSSRENAME REPORT
DDLOPTIONS ADDTRANDATA REPORT
REPORTCOUNT EVERY 1 MINUTES, RATE
DISCARDFILE ./dirrpt/extoa.dsc,APPEND,MEGABYTES 1024
THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 90000 IOLATENCY 180000
DBOPTIONS ALLOWUNUSEDCOLUMN
WARNLONGTRANS 2h,CHECKINTERVAL 3m
EXTTRAIL ./dirdat/oa
TRANLOGOPTIONS EXCLUDEUSER goldengate
FETCHOPTIONS USESNAPSHOT
TRANLOGOPTIONS CONVERTUCS2CLOBS
TRANLOGOPTIONS altarchivelogdest primary instance dcwwqxp1 /backup1 altarchivelogdest instance dcwwqxp2 /backup2
table CIM.*;
table EPBI.*;
table EPBI_DXP.*;
table PSDSS_BUFF.*;
table PSDSS_DCHK.*;
table PSDSS_JCJK.*;
table PSDSS_MID.*;
table PSDSS_MID_1.*;
table wwqxpYQ.*;
table MW_ANHUI.*;
table MW_IQ.*;
table MW_ZTK.*;
table MW_APP.*;
table MW_RTM.*;
table MW_SYS.*;
table STAT.*;
添加相应的进程和队列文件
add extract extoa,tranlog, threads 2,begin now
add exttrail ./dirdat/oa,extract extoa,megabytes 100
start extsa
info all
info *
10、配置传输进程
./ggsci
DBLOGIN USERID goldengate, PASSWORD password_10
extract dpeoa
passthru
rmthost 192.168.2.18, mgrport 7839,compress
rmttrail ./dirdat/oa
table CIM.*;
table EPBI.*;
table EPBI_DXP.*;
table PSDSS_BUFF.*;
table PSDSS_DCHK.*;
table PSDSS_JCJK.*;
table PSDSS_MID.*;
table PSDSS_MID_1.*;
table wwqxpYQ.*;
table MW_ANHUI.*;
table MW_IQ.*;
table MW_ZTK.*;
table MW_APP.*;
table MW_RTM.*;
table MW_SYS.*;
table STAT.*;
添加相应的进程和队列文件
add ext dpeoa,exttrailsource ./dirdat/oa
add rmttrail ./dirdat/oa,ext dpeoa,megabytes 100
start dpeoa
info all
info *
传输进程需要等到容灾端管理进程配置好才可以启动
11、配置DDL复制
OGG的DDL对象安装时不能有运行的sessoin存在,请DBA执行停机清理或者杀死所有数据库中的Session。
建议有条件先执行停止业务,并关闭Oracle的Listener。
注:时间大约需要30分钟左右,在这段时间需要完成下面所有操作步骤。此后的所有步骤,都可以在业务正常运行时进行操作。
./ggsci
stop mgr
stop extsa
stop dpesa
---在安装DDL脚本时,应该停止所有OGG进程---
sqlplus "/ as sysdba"
SQL> @marker_setup.sql
Enter GoldenGate schema name:goldengate
SQL> @ddl_setup.sql
Enter GoldenGate schema name:goldengate
SQL> @role_setup.sql
Grant this role to each user assigned to the Extract, Replicat, GGSCI, and Manager processes, by using the following SQL command:
GRANT GGS_GGSUSER_ROLE TO <loggedUser>
where <loggedUser> is the user assigned to the GoldenGate processes.
注意这里的提示:需要手工将这个GGS_GGSUSER_ROLE指定给extract所使用的数据库用户(即参数文件里面通过userid指定的用户),可以到sqlplus下执行类似的sql:
GRANT GGS_GGSUSER_ROLE TO goldengate;
注:这里的goldengate是extract使用的用户。如果你有多个extract,使用不同的数据库用户,则需要重述以上过程全部赋予GGS_GGSUSER_ROLE权限。
运行以下脚本,使触发器生效
SQL> @ddl_enable.sql
启动OGG进程
start mgr
start *
9、RMAN备份数据库
备份数据文件
more rman.sh
rman target / cmdfile=/oggtmp/rmanbak/backdatafile.sql
more backdatafile.sql
run
{
allocate channel d1 type disk maxpiecesize 10G;
allocate channel d2 type disk maxpiecesize 10G;
allocate channel d3 type disk maxpiecesize 10G;
allocate channel d4 type disk maxpiecesize 10G;
allocate channel d5 type disk maxpiecesize 10G;
allocate channel d6 type disk maxpiecesize 10G;
allocate channel d7 type disk maxpiecesize 10G;
allocate channel d8 type disk maxpiecesize 10G;
backup format '/oggtmp/rmanbak/wwqxp_full_%s_%p.dbf' database;
sql 'alter system switch logfile';
sql 'alter system switch logfile';
sql 'alter system switch logfile';
sql 'alter system switch logfile';
sql 'alter system switch logfile';
sql 'alter system switch logfile';
sql 'alter system switch logfile';
release channel d1;
release channel d2;
release channel d3;
release channel d4;
release channel d5;
release channel d6;
release channel d7;
release channel d8;
}
备份归档文件
SQL>set line 200
SQL>select * from Gv$log;
SQL> select inst_id,group#,thread#,sequence#,bytes,members,archived,status,to_char(FIRST_CHANGE#),FIRST_TIMe from Gv$log;
INST_ID GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS TO_CHAR(FIRST_CHANGE#) FIRST_TIM
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ---------------------------------------- ---------
2 1 1 7417 524288000 2 YES INACTIVE 12620301743415 03-JAN-14
2 2 1 7418 524288000 2 NO CURRENT 12620301747657 03-JAN-14
2 3 1 7415 524288000 2 YES INACTIVE 12620301742955 03-JAN-14
2 4 1 7416 524288000 2 YES INACTIVE 12620301743085 03-JAN-14
2 5 2 9185 524288000 2 NO CURRENT 12620301748197 03-JAN-14
2 6 2 9182 524288000 2 YES INACTIVE 12620301746544 03-JAN-14
2 7 2 9183 524288000 2 YES INACTIVE 12620301746591 03-JAN-14
2 8 2 9184 524288000 2 YES INACTIVE 12620301747299 03-JAN-14
1 1 1 7417 524288000 2 YES INACTIVE 12620301743415 03-JAN-14
1 2 1 7418 524288000 2 NO CURRENT 12620301747657 03-JAN-14
1 3 1 7415 524288000 2 YES INACTIVE 12620301742955 03-JAN-14
1 4 1 7416 524288000 2 YES INACTIVE 12620301743085 03-JAN-14
1 5 2 9185 524288000 2 NO CURRENT 12620301748197 03-JAN-14
1 6 2 9182 524288000 2 YES INACTIVE 12620301746544 03-JAN-14
1 7 2 9183 524288000 2 YES INACTIVE 12620301746591 03-JAN-14
1 8 2 9184 524288000 2 YES INACTIVE 12620301747299 03-JAN-14
SQL> set line 200
SQL> select inst_id,group#,thread#,sequence#,bytes,members,archived,status,to_char(FIRST_CHANGE#),FIRST_TIMe from Gv$log;
INST_ID GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS TO_CHAR(FIRST_CHANGE#) FIRST_TIM
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ---------------------------------------- ---------
2 1 1 7537 524288000 2 NO CURRENT 12620341389385 04-JAN-14
2 2 1 7534 524288000 2 YES ACTIVE 12620341384329 04-JAN-14
2 3 1 7535 524288000 2 YES ACTIVE 12620341386860 04-JAN-14
2 4 1 7536 524288000 2 YES ACTIVE 12620341387356 04-JAN-14
2 5 2 9253 524288000 2 YES ACTIVE 12620341370135 04-JAN-14
2 6 2 9254 524288000 2 YES ACTIVE 12620341377321 04-JAN-14
2 7 2 9255 524288000 2 YES ACTIVE 12620341386918 04-JAN-14
2 8 2 9256 524288000 2 NO CURRENT 12620341389031 04-JAN-14
1 1 1 7537 524288000 2 NO CURRENT 12620341389385 04-JAN-14
1 2 1 7534 524288000 2 YES ACTIVE 12620341384329 04-JAN-14
1 3 1 7535 524288000 2 YES ACTIVE 12620341386860 04-JAN-14
INST_ID GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS TO_CHAR(FIRST_CHANGE#) FIRST_TIM
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ---------------------------------------- ---------
1 4 1 7536 524288000 2 YES ACTIVE 12620341387356 04-JAN-14
1 5 2 9253 524288000 2 YES ACTIVE 12620341370135 04-JAN-14
1 6 2 9254 524288000 2 YES ACTIVE 12620341377321 04-JAN-14
1 7 2 9255 524288000 2 YES ACTIVE 12620341386918 04-JAN-14
1 8 2 9256 524288000 2 NO CURRENT 12620341389031 04-JAN-14
16 rows selected.
SQL>
INST_ID GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS TO_CHAR(FIRST_CHANGE#) FIRST_TIM
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ---------------------------------------- ---------
2 1 1 7537 524288000 2 NO CURRENT 12620341389385 04-JAN-14
2 2 1 7534 524288000 2 YES ACTIVE 12620341384329 04-JAN-14
2 3 1 7535 524288000 2 YES ACTIVE 12620341386860 04-JAN-14
2 4 1 7536 524288000 2 YES ACTIVE 12620341387356 04-JAN-14
2 5 2 9253 524288000 2 YES ACTIVE 12620341370135 04-JAN-14
2 6 2 9254 524288000 2 YES ACTIVE 12620341377321 04-JAN-14
2 7 2 9255 524288000 2 YES ACTIVE 12620341386918 04-JAN-14
2 8 2 9256 524288000 2 NO CURRENT 12620341389031 04-JAN-14
1 1 1 7537 524288000 2 NO CURRENT 12620341389385 04-JAN-14
1 2 1 7534 524288000 2 YES ACTIVE 12620341384329 04-JAN-14
1 3 1 7535 524288000 2 YES ACTIVE 12620341386860 04-JAN-14
INST_ID GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS TO_CHAR(FIRST_CHANGE#) FIRST_TIM
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ---------------------------------------- ---------
1 4 1 7536 524288000 2 YES ACTIVE 12620341387356 04-JAN-14
1 5 2 9253 524288000 2 YES ACTIVE 12620341370135 04-JAN-14
1 6 2 9254 524288000 2 YES ACTIVE 12620341377321 04-JAN-14
1 7 2 9255 524288000 2 YES ACTIVE 12620341386918 04-JAN-14
1 8 2 9256 524288000 2 NO CURRENT 12620341389031 04-JAN-14
16 rows selected.
SQL>
SQL> set line 200
SQL> select inst_id,group#,thread#,sequence#,bytes,members,archived,status,to_char(FIRST_CHANGE#),FIRST_TIMe from Gv$log;
INST_ID GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS TO_CHAR(FIRST_CHANGE#) FIRST_TIM
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ---------------------------------------- ---------
1 1 1 7541 524288000 2 YES INACTIVE 12620341953012 04-JAN-14
1 2 1 7542 524288000 2 YES INACTIVE 12620342069182 04-JAN-14
1 3 1 7543 524288000 2 YES ACTIVE 12620342187234 04-JAN-14
1 4 1 7544 524288000 2 NO CURRENT 12620342309187 04-JAN-14
1 5 2 9257 524288000 2 YES INACTIVE 12620341618880 04-JAN-14
1 6 2 9258 524288000 2 YES INACTIVE 12620341734410 04-JAN-14
1 7 2 9259 524288000 2 YES INACTIVE 12620341995892 04-JAN-14
1 8 2 9260 524288000 2 NO CURRENT 12620342199395 04-JAN-14
2 1 1 7541 524288000 2 YES INACTIVE 12620341953012 04-JAN-14
2 2 1 7542 524288000 2 YES INACTIVE 12620342069182 04-JAN-14
2 3 1 7543 524288000 2 YES ACTIVE 12620342187234 04-JAN-14
INST_ID GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS TO_CHAR(FIRST_CHANGE#) FIRST_TIM
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ---------------------------------------- ---------
2 4 1 7544 524288000 2 NO CURRENT 12620342309187 04-JAN-14
2 5 2 9257 524288000 2 YES INACTIVE 12620341618880 04-JAN-14
2 6 2 9258 524288000 2 YES INACTIVE 12620341734410 04-JAN-14
2 7 2 9259 524288000 2 YES INACTIVE 12620341995892 04-JAN-14
2 8 2 9260 524288000 2 NO CURRENT 12620342199395 04-JAN-14
16 rows selected.
SQL>
12620341618880
获取不活动的已归档日志的最后一个SCN号,如果有多个已归档的INACTIVE的组,取最大的FIRST_CHANGE#,这里取218412
more rmanlog.sh
rman target / cmdfile=/oggtmp/archbak/backarchlog.sql
more backarchlog.sql
run
{
allocate channel d1 type disk maxpiecesize 10G;
allocate channel d2 type disk maxpiecesize 10G;
sql 'alter system switch logfile';
sql 'alter system switch logfile';
sql 'alter system switch logfile';
sql 'alter system switch logfile';
sql 'alter system switch logfile';
sql 'alter system switch logfile';
sql 'alter system switch logfile';
sql 'alter system archive log current';
backup format '/oggtmp/archbak/wwqxp_archive_%s_%p.dbf' archivelog from time "sysdate-2";
release channel d1;
release channel d2;
}
备份控制文件
backup current controlfile format '/oggtmp/archbak/wwqxp_controlfil_%s_%p.dbf';
备份参数文件
create pfile='/oggtemp/rmanbak/initwwqxp.0816' from spfile;
备份数据文件名称
sqlplus /nolog
spool /oggtmp/0814/file0816.txt
select file_id,file_name from dba_data_files order by file_id asc;
spool /oggtmp/0814/tempfile0816.txt
select file_id,tablespace_name,file_name from dba_temp_files;
spool /oggtmp/0814/redofile0816.txt
select group#,status,member from gv$logfile;
10、ftp传输rman备份集到移动硬盘
ftp 192.168.2.3
bin
prmopt
get *.dbf
11、ftp传输rman备份集到灾备服务器
ftp 192.168.2.18
bin
prmopt
put *.dbf
12、恢复数据库
1)恢复参数文件
startup nomount pfile='/oracle/app/product/10.2.0/dbs/initpmdb.ora';
2)恢复控制文件
restore controlfile from '/oggtmp/archbak/wwqxp_controlfil_%s_%p.dbf';
alter database mount;
3)注册RMAN备份集
catalog backuppiece '/rmanbak/wwqxp_archivelog_24230_1.dbf';
catalog backuppiece '/rmanbak/wwqxp_archivelog_24231_1.dbf';
catalog backuppiece '/rmanbak/wwqxp_archivelog_24232_1.dbf';
catalog backuppiece '/rmanbak/wwqxp_archivelog_24233_1.dbf';
catalog backuppiece '/rmanbak/wwqxp_archivelog_24240_1.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24223_1.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24223_10.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24223_11.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24223_12.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24223_13.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24223_14.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24223_15.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24223_16.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24223_17.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24223_18.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24223_19.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24223_2.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24223_3.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24223_4.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24223_5.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24223_6.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24223_7.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24223_8.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24223_9.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24224_1.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24224_10.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24224_11.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24224_12.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24224_13.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24224_14.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24224_15.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24224_16.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24224_17.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24224_18.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24224_19.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24224_2.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24224_3.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24224_4.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24224_5.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24224_6.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24224_7.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24224_8.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24224_9.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24225_1.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24225_10.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24225_11.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24225_12.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24225_13.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24225_14.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24225_15.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24225_16.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24225_17.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24225_18.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24225_2.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24225_3.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24225_4.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24225_5.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24225_6.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24225_7.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24225_8.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24225_9.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24226_1.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24227_1.dbf';
4)、恢复数据文件,dba_data_files输出的数据文件
run{
allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;
allocate channel d5 type disk;
allocate channel d6 type disk;
restore database;
switch datafile all;
release channel d1;
release channel d2;
release channel d3;
release channel d4;
release channel d5;
release channel d6;
}
more recover.sh
rman target / cmdfile=/wwqxpdata_20131010/scripts/recoverfile.sql
run{
allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;
allocate channel d5 type disk;
allocate channel d6 type disk;
set until scn=12432504957403;
recover database;
release channel d1;
release channel d2;
release channel d3;
release channel d4;
release channel d5;
release channel d6;
}
6)更改redo日志文件路径
7)更改临时表空间文件路径
8)打开数据库
alter database open resetlogs;
9)容灾端数据库禁用JOB、触发器、外键
在dba_jobs中,有些job/schedule,会在复制对象中产生DML的操作,必须禁用掉,否则会造成DML语句在目标端的重复执行,
注:在目标端恢复的时候,可以在pfile文件中将job_queue_processes设置为0,然后将带有dml的job停掉以后,再修改job_queue_processes参数至正常值,如job_queue_processes=10;
declare
v_sql varchar2(2000);
CURSOR c_trigger IS SELECT 'alter trigger '||owner||'.'||trigger_name||' disable' from
dba_triggers where owner in (
'SGPM','SGPM_OUT','AMBER','WR_AMBER','OWF_MGR','CZK','KBSD', 'OCS'); -----用户名需要改
BEGIN
OPEN c_trigger;
LOOP
FETCH c_trigger INTO v_sql;
EXIT WHEN c_trigger%NOTFOUND;
execute immediate v_sql;
end loop;
close c_trigger;
end;
/
declare
v_sql varchar2(2000);
CURSOR c_trigger IS SELECT 'alter table '||owner||'.'||table_name||' disable constraint '||constraint_name
from dba_constraints where constraint_type='R' and
owner in
'SGPM','SGPM_OUT','AMBER','WR_AMBER','OWF_MGR','CZK','KBSD', 'OCS'); -----用户名需要改
BEGIN
OPEN c_trigger;
LOOP
FETCH c_trigger INTO v_sql;
EXIT WHEN c_trigger%NOTFOUND;
execute immediate v_sql;
end loop;
close c_trigger;
end;
-----------trigger-----------------------------
步骤九:禁用trigger
declare
v_sql varchar2(2000);
CURSOR c_trigger IS SELECT 'alter trigger '||owner||'.'||trigger_name||' disable' from dba_triggers
where owner in ('AMBER','CZK','KBSD','OCS','OWF_MGR','SGPM','SGPM_OUT','WF_AMBER'); -----用户名需要改
BEGIN
OPEN c_trigger;
LOOP
FETCH c_trigger INTO v_sql;
EXIT WHEN c_trigger%NOTFOUND;
execute immediate v_sql;
end loop;
close c_trigger;
end;
/
------------------job-----------------------
步骤十:禁用job
在配置pfile文件时,将JOB_QUEUE_PROCESSES参数改为0.或执行以下包。
alter system set JOB_QUEUE_PROCESSES=0;
begin
exec dbms_job.broken(21,true);
end;
/
---------------scheduler------------------
步骤十一:禁用scheduler
declare
v_sql varchar2(2000);
CURSOR c_scheduler IS SELECT 'exec dbms_scheduler.drop_job(' || '''' || job_name || '''' || ');' from dba_scheduler_jobs
where owner in ('AMBER','CZK','KBSD','OCS','OWF_MGR','SGPM','SGPM_OUT','WF_AMBER'); -----用户名需要改
BEGIN
OPEN c_scheduler;
LOOP
FETCH c_scheduler INTO v_sql;
EXIT WHEN c_scheduler%NOTFOUND;
--execute immediate v_sql;
dbms_output.put_line(v_sql);
end loop;
close c_scheduler;
end;
/
exec dbms_scheduler.drop_job('ZW_DXJK_JL_1');
-----------------外键---------------------
步骤十二:禁用外键
declare
v_sql varchar2(2000);
CURSOR c_cons IS SELECT 'alter table '||owner||'.'||table_name||' disable constraint '||constraint_name from
dba_constraints where constraint_type='R' and owner in ('AMBER','CZK','KBSD','OCS','OWF_MGR','SGPM','SGPM_OUT','WF_AMBER'); -----用户名需要改
BEGIN
OPEN c_cons;
LOOP
FETCH c_cons INTO v_sql;
EXIT WHEN c_cons%NOTFOUND;
execute immediate v_sql;
end loop;
close c_cons;
end;
/
12、配置容灾端数据库
---这步在RMAN恢复生产库时,已经恢复生产库的表空间和用户,不需要再做----
create tablespace goldengate datafile '/xx/goldengate01.dbf' size 10240M ;
create user goldengate identified by password_10 default tablespace goldengate quota unlimited on goldengate;
grant CONNECT, RESOURCE ,DBA to goldengate;
13、安装OGG软件、配置管理进程、复制进程
1)安装OGG软件
su - oracle
cd /goldengate
tar -xvf ggs_AIX_ia64_ora10g_64bit.tar
./ggsci
create subdirs
2)配置管理进程
./ggsci
EDIT PARAMS MGR
PORT 7839
DYNAMICPORTLIST 7840-7914
AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 30
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45
start mgr
info all
3)配置复制进程
dblogin userid goldengate,password password_10
add replicat repya checkpointtable goldengate.checktablea,exttrail ./dirdat/ya
add checkpointtable goldengate.checktablea
edit params repya
REPLICAT repya
setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
USERID goldengate, PASSWORD AACAAAAAAAAAAANAPATACEHBIGQGCFZCOBTATGXCDGTIUISJ, ENCRYPTKEY default
REPORT AT 01:59
REPORTCOUNT EVERY 30 MINUTES, RATE
REPERROR DEFAULT, discard
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/repya.dsc, APPEND, MEGABYTES 1024
DISCARDROLLOVER AT 02:30
GETTRUNCATES
ALLOWNOOPUPDATES
DBOPTIONS DEFERREFCONST
map SGwwqxp.* , target SGwwqxp.*;
start replicat repya,aftercsn ******
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/27571661/viewspace-1771207/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/27571661/viewspace-1771207/