准备工作

1	硬件环境准备		提前2个月	已完成
2	生产数据库安装（19C）			已完成
3	数据库初始化，参数调整	参照oracle 19C参数设置来修正新库的参数		已完成
4	监听配置及测试，要求高可用			已完成
5	数据库备份、监控配置	nbu备份配置  model监控配置、zcloud监控、model主机alert日志监控		
6	数据库表空间初始化	
复制

"select distinct ‘create tablespace ’ || ff.TABLESPACE_NAME ||
’ datafile size 100m autoextend on next 100m;’
from dba_data_files ff
where tablespace_name not in
(‘GGMGR’, ‘UNDOTBS1’, ‘SYSTEM’, ‘UNDOTBS’, ‘UNDOTBS2’, ‘SYSAUX’,‘USERS’);

select ‘alter tablespace ‘||ff.TABLESPACE_NAME||’ add datafile size 100m autoextend on next 100m;’ from dba_data_files ff ;

–temp表空间大小
select ‘alter tablespace ‘||ee.tablespace_name||’ add tempfile size 100M autoextend on next 100M;’ from dba_temp_files ee;
" 已完成

7	表空间创建/temp/undo空间创建	按照标准生产库初始化		已完成

8	role创建
复制

“select ‘create role ‘|| role ||’;’ from dba_roles
where role like ‘R_%’ and role not in (‘RESOURCE’,‘RECOVERY_CATALOG_OWNER’);” 已完成

9	profile创建	参考建库初始化脚本		已完成
10	dblink创建	将dblink迁移到新库		已完成
11	outline、profile、spm迁移	将之前固定的执行计划迁移到新库		已完成
12	执行计划监控迁移	AG平台执行计划迁移，部署在dbamon用户下面		已完成
13	cronjob迁移			已完成
15	新库 job清理			已完成
16	新老数据库参数核对		DBA	已完成
17	新库alert日志监控增加	model上增加新库的alert日志监控		
18	orabm测试完成		DBA	
19	SPA对比测试完成	cv7w3jxpnbwy9-----执行计划和传参有关，不做执行计划固化	DBA	""
	19c库日志清理策略调整	
复制

“CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON all standby;
adg使用delete_archivelog_scn.sh清理日志”

20	搭建OGG回退链路		DBA	"
复制

"

上面准备工作完成后才可以开始正式迁移

正式升级

21	原库cron注释	
复制

“注释掉原库所有cron
crontab -l> .cron_file
touch cron_null
crontab cron_null” 1

22	停原库、新库job	
复制

“alter system set job_queue_processes=0 scope=both;” 2
"SYS@lsif11g1> show parameter aq
NAME TYPE VALUE

aq_tm_processes integer 1
SYS@lsif11g1> show parameter job
NAME TYPE VALUE

job_queue_processes integer 1000
"
23 停外围应用
24 停止周末的备份作业 1
记录job状态 select * from dba_jobs

25	原库用户锁定，重启数据库(生产、温备、灾备)
复制

"alter user FOGMON_NEW account lock;
alter user DBMONOPR account lock;
alter user BDGK account lock;
alter user SRRSCOREOPR account lock;
" 1
“select username,account_status from dba_users
where account_status not like ‘%LOCKED%’;
select count(0),username from gvsession group by username;" 11g库打回滚点（灾备、温备、生产 "alter database recover managed standby database cancel; create restore point upgrade_20230622 guarantee flashback database; alter database recover managed standby database disconnect from session using current logfile; select * from vrestore_point;”

26	原库数据导出	
复制

“userid=’/ as sysdba’
directory=DUMPDIR
dumpfile=expdp_lsif11g20230622_%U.dmp
logfile=expdp_lsif11g20230622.log
compression=all
filesize=20g
parallel=32
CLUSTER=N
schemas=(
SODSETL,
BDGK,
SQLAUDIT
)
exclude=statistics” 120 20:38

27	禁用goldengate的trigger	
复制

“cd $OOG_HOME
sqlplus / as sysdba
@ddl_disable.sql” 1

28	用户数据导入	
复制

“userid=’/ as sysdba’
directory=DUMPDIR
dumpfile=expdp_lsif11g20230622_%U.dmp
logfile=impdp_lsif11g20230622.log
parallel=64
CLUSTER=N
schemas=(
ORASTRESS,
BDGK
)
exclude=statistics,job” 130
导入job
"需使用应用用户执行，否则job的log_user会变成sys
userid=‘bdgk/xxxxooo’
directory=dumpdir
dumpfile=lsif20230618_bdgk_jobs_%U.dmp
logfile=lsif20230618_bdgk_jobs.log
cluster=n
schemas=BDGK
include=job

userid=‘bdgkhist/xxxxooo’
directory=dumpdir
dumpfile=lsif20230618_bdgkhist_jobs_%U.dmp
logfile=lsif20230618_bdgkhist_jobs.log
cluster=n
schemas=BDGKHIST
include=job

job有中文星期，需设置为简体中文环境再执行导入
export NLS_LANG=‘SIMPLIFIED CHINESE_CHINA.ZHS16GBK’
userid=‘bdgk/xxxxooo’
directory=dumpdir
dumpfile=lsif20230618_bdgk_jobs_%U.dmp
logfile=lsif20230618_bdgk_jobs.log
cluster=n
schemas=BDGK
include=job

userid=‘bdgkhist/xxxxooo’
directory=dumpdir
dumpfile=lsif20230618_bdgkhist_jobs_%U.dmp
logfile=lsif20230618_bdgkhist_jobs.log
cluster=n
schemas=BDGKHIST
include=job"
“select ‘Exec dbms_ijob.remove(’||job||’);’ from dba_jobs where SCHEMA_USER in (‘BDGK’,‘BDGKHIST’);
sys执行，需要commit;”
按11g jobs启动时间重置19c job启动时间
"select ‘exec dbms_ijob.next_date(’||job||’,’’’||next_date||’’’); ’ from dba_jobs;
需执行commite;
"

29	其他用户sys权限单独导入	
复制

“set pagesize 0
spool lsif11g_grant_sys.sql
select 'grant ’ || a.privilege || ’ to ’ || a.grantee || ‘;’
from dba_sys_privs a
where a.grantee in (‘KDRF’,‘DEVSUP01’,‘MOMQRY’,‘CPASQX’);
spool off” 1 lsif11g_grant_sys.sql

30	其他用户对象权限授权导入	"set pagesize 0
复制

spool lsif11g_grant_tab.sql
select 'grant ’ || a.privilege || ’ on ’ || a.owner || ‘.""’ || a.table_name ||
'"" to ’ || a.grantee || ‘;’
from dba_tab_privs a
where a.grantee in
(‘KDRF’,‘DEVSUP01’,‘MOMQRY’,‘CPASQX’);
spool off
" 5 lsif11g_grant_tab.sql

31	其他用户角色权限授权导入	
复制

“set pagesize 0
spool lsif11g_grant_role.sql
select ‘grant ‘||a.GRANTED_ROLE||’ to ‘||a.grantee||’;’ from DBA_ROLE_PRIVS a
where a.grantee in (‘KDRF’,‘DEVSUP01’,‘MOMQRY’,‘CPASQX’);
spool off” 5 lsif11g_grant_role.sql
comments导入
"select ‘comment on table ‘||ee.owner||’.’||ee.table_name||’ is ‘’’||comments||’’’;’
from dba_tab_comments ee
where ee.owner in
(‘KDRF’,‘DEVSUP01’,‘MOMQRY’,‘CPASQX’)
and comments is not null;

select ‘comment on column ‘||ee.owner||’.’||ee.table_name||’.’||ee.column_name||’ is ‘’’||comments||’’’;’
from dba_col_comments ee
where owner in
(‘KDRF’,‘DEVSUP01’,‘MOMQRY’,‘CPASQX’)
and comments is not null
order by owner, table_name;" 1

32	公共同义词创建	
复制

“select 'create public synonym ’ || a.synonym_name || ’ for ’ ||
a.table_owner || ‘.’ || a.table_name || ‘;’
from dba_synonyms a
where a.owner IN (‘PUBLIC’)
and a.table_owner in (‘KDRF’,‘DEVSUP01’,‘MOMQRY’,‘CPASQX’)” 5

33	编译失效对象	sqlplus>@?/rdbms/admin/utlrp.sql	10	
34	失效对象确认	select * from dba_objects where status <> 'VALID';	1	

35	检查新库是否存在统计信息锁定情况	
复制

“select * from dba_tab_statistics where stattype_locked is not null and owner in
(
select username from dba_users
where default_tablespace not in (‘SYSTEM’,‘SYSAUX’)
and account_status=‘OPEN’
and username not in (‘FACTUSERMGR’,‘FOGLIGHT’,‘DBQUA’,‘OVSEE’,‘DBMGR’,‘FGLPA’,‘DBQINTF’)
) —应该没记录返回;
如果有锁定需要进行解锁:举例
begin
DBMS_STATS.UNLOCK_schema_STATS(‘TRADE’);
end;
/” 1

36	统计信息收集	
复制

"exec dbms_stats.gather_schema_stats(ownname => ‘BDGK724’,estimate_percent => dbms_stats.auto_sample_size,method_opt =>‘FOR ALL COLUMNS SIZE 1’,degree => 64,no_invalidate => false, cascade => true);
exec dbms_stats.gather_schema_stats(ownname => ‘SPATEST’,estimate_percent => dbms_stats.auto_sample_size,method_opt =>‘FOR ALL COLUMNS SIZE 1’,degree => 64,no_invalidate => false, cascade => true);
exec dbms_stats.gather_schema_stats(ownname => ‘DSGCLR’,estimate_percent => dbms_stats.auto_sample_size,method_opt =>‘FOR ALL COLUMNS SIZE 1’,degree => 64,no_invalidate => false, cascade => true);
" 60

37	数据量对比，对象对比	需要核对条数和表内容，需要先增加并行参数到256，对比完成后改回2970	120	
复制

"
-----4611
select ee.owner,ee.object_name
from dba_objects@dbverify ee
where ee.owner in (‘KDRF’,‘DEVSUP01’,‘MOMQRY’,‘CPASQX’)
minus
select ee.owner,ee.object_name
from dba_objects ee
where ee.owner in (‘KDRF’,‘DEVSUP01’,‘MOMQRY’,‘CPASQX’);

select username from dba_users@dbverify
minus
select username from dba_users;
"

38	检查索引	
复制

“select owner,index_name,index_type,table_owner,table_name,status
from dba_indexes
where owner in (select username from dba_users where created > sysdate-1)
and status<>‘VALID’
order by table_name” 1
打回滚点（灾备、温备、生产）
“alter database recover managed standby database cancel;
create restore point upgrade_20230622 guarantee flashback database;
alter database recover managed standby database disconnect from session using current logfile;
select * from v$restore_point;”
“alter database recover managed standby database cancel;
flashback database to restore point upgrade_20230622;
alter database recover managed standby database disconnect from session using current logfile;”
通知应用测试验证
回滚数据库

39	添加trandata		
复制

"1、创建用户
./ggsci <<EOF
dblogin userid ggmgr, password HMLDQFZ#2021
add trandata FOGMON_NEW.*
add trandata SDMPMGMTOPR.*
EOF
" 30
表很多，测试环境用了15分钟左右

40	启动OGGtrigger	
复制

"
cd $OGG_HOME
sqlplus / as sysdba
@ddl_enable.sql
" 1

41	起源端OGG链路	
复制

“源端：
alter extract e_rol begin now
info all
start *
info all” 1

42	目标端禁用trigger，并做好记录	
复制

"set pagesize 0
spool diable_triggers.sql
select ‘alter trigger ‘||owner||’.’||trigger_name||’ disable;’ from dba_triggers ff where ff.owner not in (‘SYS’,
‘SYSTEM’,
‘WMSYS’,
‘DBSNMP’,
‘APPQOSSYS’,
‘EXFSYS’,
‘XDB’,
‘OUTLN’,
‘GGMGR’,
‘XS$NULL’,
‘ANONYMOUS’,
‘ORACLE_OCM’,
‘DIP’)
and status = ‘ENABLED’;
spool off
" 5
"alter trigger BDGK.TRG_SUBJECTS_UPD disable;
alter trigger DBMON.TR_DB_STARTUP disable;
alter trigger DBMON.TR_DB_SHUTDOWN disable;
"

43	目标端禁用外键约束，并做好记录	
复制

"set pagesize 0
spool lsif11g_disable_fk.sql
select 'alter table ’ || ee.owner || ‘.’ || table_name ||
’ disable constraint ’ || constraint_name || ‘;’
from dba_constraints ee
where constraint_type = ‘R’
and status = ‘ENABLED’
and ee.owner in (‘KDRF’,‘DEVSUP01’,‘MOMQRY’,‘CPASQX’);
spool off " 1

44	确认目标端trigger，job已禁用	
复制

"
show parameter job_queue_processe
show parameter aq_tm_processes" 1

45	起目标端OGG进程	
复制

"./ggsci
info all
start *
info all " 1

46	解锁新库lsif用户	
复制

"
alter user FOGMON_NEW account unlock;
alter user BDGK account unlock;
"

47	放开新库job，原库禁用	
复制

“alter system set job_queue_processes=1152 scope=both;
alter system set aq_tm_processes=1 scope=both;
alter system set job_queue_processes=0 scope=both;
alter system set aq_tm_processes=0 scope=both;” 1

48	功能验证			
49	"rman备份调整，调整到温备并开启策略
复制

检查温备、灾备库crontab、生产日志清理策略调整"
CONFIGURE ARCHIVELOG DELETION POLICY TO none;

50	数据库备份、监控配置	nbu备份配置  model监控配置、zcloud监控、model主机alert日志监控
51	开放用户使用
52	11g dump文件上带做永久保留
53	迁移crontab
54	运行期间监控
复制