数据迁移配置OGG

原创 15666777360 2021-08-19

1714

数据迁移配置OGG

纲要
1.源库首先打开ogg的抽取
2.再源库抽取和投递正常进行之后
3.源库执行备份，一个全备+归档备份即可
4.然后到目标库恢复
5.目标库执行完restore和recover之后，open之前，去获取当前的scn：
select FILE# ,CHECKPOINT_CHANGE# from v$datafile_header;
6. open库
7.目标执行start replicat aftercsn时指定的就是第5步查到的scn值

备注： SCN 是数据恢复的灵魂，recover后记录当前 scn，
1、源库执行0级备份
备份脚本，修改原有的备份脚本，
调整BAK_LEVEN=0 ，0级备份
调整，format地址为本地路径 /data/backup/rmanbackup/

run{
BAK_LEVEL=0
$RMAN nocatalog TARGET sys/11111 msglog $RMAN_LOG_FILE append <<EOF
run
{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate channel c4 device type disk;
backup as compressed backupset incremental level= $BAK_LEVEL database format=’/data/backup/rmanbackup/rman_lev"$BAK_LEVEL"%d%U_date +%Y%m%d%H%M’ tag=‘SPIPRDSTD_lev"$BAK_LEVEL"’;
backup as compressed backupset archivelog from time ‘sysdate-3’ format=’/data/backup/rmanbackup/arch_%d_%U_date +%Y%m%d%H%M’ tag=‘ARCHIVE’ delete all input;backup current controlfile format=’/data/backup/rmanbackup/ctl_file_%d_%U_date +%Y%m%d%H%M’ tag=‘CTLFILE’;
backup spfile format=’/data/backup/rmanbackup/spfile_%d_%U_date +%Y%m%d%H%M’ tag=‘SPFILE’ ;
delete noprompt archivelog all completed before ‘sysdate-3’;
crosscheck archivelog all;
delete noprompt expired archivelog all;
crosscheck backup;
report obsolete;
delete noprompt obsolete;
release channel c2;
release channel c1;
release channel c3;
release channel c4;
}

2.将备份传输至目标服务器
scp /data/backup/rmanback/ oracle@10.201.10.81:/data/backup/

3.在目标服务器还原
拷贝服务器的spfile文件到81 数据库的$ORACLE_HOME/dbs下修改文件 initspiprdogg.ora
去掉standby 的相关配置
*.__data_transfer_cache_size=0
*.__db_cache_size=161061273600
*.__java_pool_size=3758096384
*.__large_pool_size=11811160064
*.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment
*.__pga_aggregate_target=81067507712
*.__sga_target=242665652224
*.__shared_io_pool_size=536870912
*.__shared_pool_size=64424509440
*.__streams_pool_size=0
*._always_semi_join=‘OFF’
*._b_tree_bitmap_plans=FALSE
*._like_with_bind_as_equality=TRUE
*._optimizer_max_permutations=100
*.optimizer_sortmerge_join_enabled=FALSE
*.partition_view_enabled=FALSE
*.audit_file_dest=’/u01/app/oracle/admin/SPIPRDSTD/adump’
*.audit_trail=‘NONE’
*.cluster_database=false
*.compatible=‘12.1.0.2.0’
*.control_files=’/data/SPIPRDSTD/CONTROLFILE/current01.ctl’,’/data/fast_recovery_area/SPIPRDSTD/CONTROLFILE/current02.ctl’#R
estore Controlfile
*.db_block_size=8192
*.db_create_file_dest=’/data’
*.db_domain=’’
*.db_keep_cache_size=536870912
*.db_name=‘SPIPRD’
*.db_recovery_file_dest=’/data/fast_recovery_area’
*.db_recovery_file_dest_size=68719476736
*.db_unique_name=‘SPIPRDSTD’
*.diagnostic_dest=’/u01/app/oracle’
*.dispatchers=’(PROTOCOL=TCP)(SERVICE=SPIPRDSTDXDB)’
*.filesystemio_options=‘SETALL’
*.log_archive_format=’%t%s%r.dbf’
*.open_cursors=5000
*.optimizer_capture_sql_plan_baselines=FALSE
*.optimizer_index_cost_adj=30
*.pga_aggregate_target=77060m
*.processes=5000
*.recyclebin=‘OFF’
*.remote_login_passwordfile=‘exclusive’
*.result_cache_mode=‘MANUAL’
*.session_cached_cursors=300
*.sga_target=231180m
*.shared_pool_size=64424509440
SPIPRDSTD.undo_tablespace=‘undotbs1’
*.undo_tablespace=‘undo_t1’

将数据库启动至nomount

export ORACLE_SID=spiprdogg
SQL> sqlplus / as sysdba
SQL> startup nomount pfile=‘initspiprdogg.ora’;

还原控制文件
run{
restore controlfile from ‘/data/backup/rmanbackup/ctl_file_SPIPRD_ojv7krt8_1_1_202008112201’;
}

注册备份
catalog start with ‘/data/backup/rmanbackup/’;

还原数据库
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
allocate channel c5 type disk;
restore database;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
}

数据库恢复
查看当前目标数据库SCN，记录recover后的SCN

col CHECKPOINT_CHANGE# for 9999999999999999999999999
select FILE# ,CHECKPOINT_CHANGE# from v$datafile_header;
recover
15083980930130

 1	       15084056820732
 2	       15084056820733
 3	       15084056820733
 4	       15084056820732
 5	       15084056820731
 6	       15084056820730
 7	       15084056820731
 8	       15084056820732
 9	       15084056820731
10	       15084056820731
11	       15084056820733

 FILE#	   CHECKPOINT_CHANGE#
复制

12	       15084056820732
13	       15084056820733
复制

执行增量备份 level =1

run
{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate channel c4 device type disk;
backup as compressed backupset incremental level= $BAK_LEVEL database format=’/data/backup/rmanbackup/rman_lev"$BAK_LEVEL"%
d%U_date +%Y%m%d%H%M ’ tag=‘SPIPRDSTD_lev"$BAK_LEVEL"’;
backup as compressed backupset archivelog from time ‘sysdate-3’ format=’/data/backup/rmanbackup/arch_%d_%U_date +%Y%m%d%H% M’ tag=‘ARCHIVE’ delete all input;backup current controlfile format=’/data/backup/rmanbackup/ctl_file_%d_%U_date +%Y%m%d%H %M’ tag=‘CTLFILE’;
backup spfile format=’/data/backup/rmanbackup/spfile_%d_%U_date +%Y%m%d%H%M’ tag=‘SPFILE’ ;
crosscheck archivelog all;
crosscheck backup;
report obsolete;
15119651409376

然后清理日志组
多次执行ALTER DATABASE ACTIVATE STANDBY DATABASE;都会报错，逐个清理以下日志组

ALTER DATABASE ACTIVATE STANDBY DATABASE;

alter database drop logfile group 22;
alter database clear logfile group 22;
alter database clear logfile group 15;
alter database clear logfile group 18;
alter database clear logfile group 19;
alter database clear logfile group 20;
…
根据alter 日志提示删除不存在的日志组

–添加日志组，已启用文件自动管理
ALTER DATABASE ADD LOGFILE GROUP 1 SIZE 512M;
ALTER DATABASE ADD LOGFILE GROUP 2 SIZE 512M;
ALTER DATABASE ADD LOGFILE GROUP 7 SIZE 512M;

ogg库中还原增量

run{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
recover database;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}

启动数据库报错
根据trace日志信息备份缺少的归档，在RAC 节点获取缺失thread 的归档

backup archivelog from sequence 9096 thread 1 format ‘/home/oracle/arc_%d_%U.arc’;
backup archivelog from sequence 7961 thread 2 format ‘/home/oracle/arcth2_%d_%U.arc’;

恢复增量归档日志

run{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
recover database；
release channel c1;
release channel c2;
release channel c3;
release channel c4;

打开数据库，创建spfile文件

SQL> alter database open
SQL> create spfile from pfile；

配置数据库OGG相关参数
启用参数
alter system set enable_goldengate_replication=true scope=spfile;
禁用触发器
alter trigger SIEBEL.IDM_ROW_ID disable;
alter trigger SIEBEL.PRODUCT_DELETE disable;
alter trigger SIEBEL.TIG_OLD_SR_PART disable;
alter trigger SIEBEL.ON_LOGIN_TRIGGER disable;

OGG 配置

添加检查表
DBLOGIN USERID ogg PASSWORD ogg
add checkpointtable ogg.checkpoint

重新配置repe进程

删除repe进程

delete replicat REPEA
delete replicat REPEA_2
delete replicat REPEA_3
delete replicat REPEA_4
delete replicat REPEA_5
delete replicat REPEA_6
delete replicat REPEA_7
delete replicat REPEA_8
delete replicat REPEA_9
添加repe进程
add replicat REPEA,exttrail ./dirdat/ea, checkpointtable ogg.checkpoint
add replicat REPEA_2,exttrail ./dirdat/ea, checkpointtable ogg.checkpoint
add replicat REPEA_3,exttrail ./dirdat/ea, checkpointtable ogg.checkpoint
add replicat REPEA_4,exttrail ./dirdat/ea, checkpointtable ogg.checkpoint
add replicat REPEA_5,exttrail ./dirdat/ea, checkpointtable ogg.checkpoint
add replicat REPEA_6,exttrail ./dirdat/ea, checkpointtable ogg.checkpoint
add replicat REPEA_7,exttrail ./dirdat/ea, checkpointtable ogg.checkpoint
add replicat REPEA_8,exttrail ./dirdat/ea, checkpointtable ogg.checkpoint
add replicat REPEA_9,exttrail ./dirdat/ea, checkpointtable ogg.checkpoint

启动repe进程

15083552903068
start replicat repea,aftercsn 15083980930130
start replicat repea_2,aftercsn 15083980930130
start replicat repea_3,aftercsn 15083980930130
start replicat repea_4,aftercsn 15083980930130
start replicat repea_5,aftercsn 15083980930130
start replicat repea_6,aftercsn 15083980930130
start replicat repea_7,aftercsn 15083980930130
start replicat repea_8,aftercsn 15083980930130
start replicat repea_9,aftercsn 15083980930130
start replicat repea_9,aftercsn 15083980930130

–查找日志序列
set line 200 pages 200
col name for a100
SELECT sequence#,name,thread#,resetlogs_id,archived,deleted FROM v$archived_log WHERE sequence# >= 76437;

rman list 日志序列

list backup of archivelog sequence between 76437 and 76501;

oracle