Oracle 9i升級19C 邏輯遷移詳細方法（二）
# 目標端同步sequence
-- 中間伺服器(XX)執行，按schema逐個生成
cd /home/oracle/oracle_work
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
sqlplus XXX/XXXi@XX:1521/XX
檢查dblink，確認9i的dblink指向DG備端：
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
--select sysdate from dual@to9ibccdb;
select sysdate from dual@to9ibccdb;
select sysdate from dual@to19cdcdb;
--生成刪除sequence指令碼
vi /u01/dumdata/index/dropseq.sql
select 'drop sequence ' || SEQUENCE_OWNER || '.' || SEQUENCE_NAME || ';'
from (select b.SEQUENCE_OWNER SEQUENCE_OWNER,
b.sequence_name sequence_name,
b.INCREMENT_BY INCREMENT_BY,
b.MIN_VALUE MIN_VALUE,
b.MAX_VALUE MAX_VALUE,
b.last_number last_number,
b.CYCLE_FLAG CYCLE_FLAG,
b.CACHE_SIZE CACHE_SIZE,
(b.last_number - a.last_number) gap_than_zero
from dba_sequences@to19cdcdb a, dba_sequences@to9idb b
where b.sequence_owner in ('XXX')
and a.sequence_owner = b.sequence_owner(+)
and a.sequence_name = b.sequence_name(+)
order by 5, 1, 2 desc)
where gap_than_zero <> 0
order by SEQUENCE_OWNER;
-- 生成建立sequence指令碼
vi /u01/dumdata/index/createseq.sql
select 'create sequence ' || SEQUENCE_OWNER || '.' || SEQUENCE_NAME ||
' increment by ' || INCREMENT_BY || ' minvalue ' || MIN_VALUE ||
' maxvalue ' || MAX_VALUE || ' start with ' || (LAST_NUMBER) || ' ' ||
decode(CYCLE_FLAG, 'Y', 'CYCLE', 'NOCYCLE ') || decode(cache_size,0,' nocache ',' cache '||cache_size) || ';'
from (select b.SEQUENCE_OWNER SEQUENCE_OWNER,
b.sequence_name sequence_name,
b.INCREMENT_BY INCREMENT_BY,
b.MIN_VALUE MIN_VALUE,
b.MAX_VALUE MAX_VALUE,
b.last_number last_number,
b.CYCLE_FLAG CYCLE_FLAG,
b.CACHE_SIZE CACHE_SIZE,
(b.last_number - a.last_number) gap_than_zero
from dba_sequences@to19cdcdb a, dba_sequences@to9idb b
where b.sequence_owner in ('XXX')
and a.sequence_owner = b.sequence_owner(+)
and a.sequence_name = b.sequence_name(+)
order by 5, 1, 2 desc)
where gap_than_zero <> 0
order by SEQUENCE_OWNER;
--生成授權sequence指令碼
vi /u01/dumdata/index/grantseq.sql
select 'grant '||s.privilege||' on '||s.owner||'.'||s.table_name||' to '||s.grantee||';'
from
(select owner, grantee, privilege, table_name
from dba_tab_privs@to9idb
where grantee not in ('SYS','OWBSYS_AUDIT','FFPDARCHIVE','FFPDARCHIVE1','SYSTEM','OUTLN','DIP','TSMSYS','DBSNMP','ORACLE_OCM','WMSYS','EXFSYS','DMSYS','CTXSYS','XDB','ANONYMOUS','ORDSYS','SI_INFORMTN_SCHEMA','ORDPLUGINS','MDSYS','OLAPSYS','MDDATA','SCOTT','SYSMAN','MGMT_VIEW','GOLDENGATE','PATROL','SPA','MGMT_USER','APEX_030200','APPQOSSYS','ORDDATA','OWBSYS','FLOWS_FILES','QCOAGT') and owner not in ('GOLDENGATE')
and table_name in (select sequence_name from dba_sequences
where SEQUENCE_OWNER in ('XXX'))) s
full join
(select owner, grantee, privilege, table_name
from dba_tab_privs@to19cdcdb
where grantee not in ('SYS','OWBSYS_AUDIT','FFPDARCHIVE','FFPDARCHIVE1','SYSTEM','OUTLN','DIP','TSMSYS','DBSNMP','ORACLE_OCM','WMSYS','EXFSYS','DMSYS','CTXSYS','XDB','ANONYMOUS','ORDSYS','SI_INFORMTN_SCHEMA','ORDPLUGINS','MDSYS','OLAPSYS','MDDATA','SCOTT','SYSMAN','MGMT_VIEW','GOLDENGATE','PATROL','SPA','MGMT_USER','APEX_030200','APPQOSSYS','ORDDATA','OWBSYS','FLOWS_FILES','QCOAGT') and owner not in ('GOLDENGATE')
and table_name in (select sequence_name from dba_sequences
where SEQUENCE_OWNER in ('XXX'))) d
on s.grantee=d.grantee and s.privilege=d.privilege
where s.privilege is not null and d.privilege is null
order by s.owner;
--執行刪除/建立/授權sequence指令碼
su - oracle
sqlplus '/as sysdba'
@/u01/dumdata/index/dropseq.sql
@/u01/dumdata/index/createseq.sql
@/u01/dumdata/index/grantseq.sql
-- 在OEM 251 環境增加匯入3個sequence
select 'create sequence ' || SEQUENCE_OWNER || '.' || SEQUENCE_NAME ||
' increment by ' || INCREMENT_BY || ' minvalue ' || MIN_VALUE ||
' maxvalue ' || MAX_VALUE || ' start with ' || (LAST_NUMBER) || ' ' ||
decode(CYCLE_FLAG, 'Y', 'CYCLE', 'NOCYCLE ') ||
decode(cache_size, 0, ' nocache ', ' cache ' || cache_size) || ';'
from dba_sequences@to19cdcdb a
where a.sequence_owner in ('XXX')
and a.sequence_name = 'EQPSEQUENCE';
sqlplus sys/CszDb_2212*@SZFW251 as sysdba
create sequence XXX.EQPSEQUENCE increment by 1 minvalue 1 maxvalue 999999999999999999999999999 start with 228186089 NOCYCLE cache 20;
-- 以下2個固定建立
create sequence XXX.CORESEQUENCE minvalue 1 maxvalue 9999999999999999999999999999 start with 1000000 increment by 1 cache 100;
create sequence XXX.PARTITIONSEQUENCE minvalue 1 maxvalue 9999999999999999999999999999 start with 1 increment by 1 cache 100;
-- 建立job，參考建立job文件
--編譯無效物件並驗證
@?/rdbms/admin/utlprp 32;
--對比無效物件
-- 中間伺服器(XXX)執行 <<<<<<<<很多失效物件
select owner, object_name, object_type from dba_objects@to19cdcdb
where status='INVALID'
and owner in ('XXX')
minus
select owner, object_name, object_type from dba_objects@to9idb
where status='INVALID'
and owner in ('XXX')
order by owner, object_type;
## 檢查索引owner和表的owner是否一致 <<<發現生產庫有不一致情況
select * from dba_indexes
where owner in ('XXX')
and owner <> table_owner
#### 表記錄對比
sqlplus XXX/XXXi@XXX:1521/XXX
create table comp_tab_rows(
owner varchar2(30),
tab_name varchar2(30),
rows_9i number,
rows_19c number);
create index idx_tab_rows on comp_tab_rows(owner, tab_name);
truncate table comp_tab_rows;
insert into comp_tab_rows(owner, tab_name)
select owner, table_name
from (
select owner, table_name, num_rows
from dba_tables@to9idb
where owner in ('XXX')
order by owner, num_rows
);
commit;
/*
建立2個查詢儲存過程，已經建好
create or replace procedure gen_9i_tab_rows(schema_name in varchar2) as
ls_sql varchar2(500);
li_count pls_integer;
ls_begin_date varchar2(30);
ls_end_date varchar2(30);
begin
select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') into ls_begin_date from dual;
dbms_output.put_line('Begin time: ' || ls_begin_date);
for cc in (select * from comp_tab_rows where owner = schema_name ) loop
ls_sql := 'select count(1) from ' || cc.owner || '."' || cc.tab_name || '"@to9ibccdb';
begin
execute immediate ls_sql into li_count;
exception
when others then
if sqlcode = -942 then
dbms_output.put_line(cc.owner || '.' || cc.tab_name || ' is not exists!');
update comp_tab_rows set rows_9i = -1 where owner = cc.owner and tab_name = cc.tab_name;
else
dbms_output.put_line(SQLERRM);
end if;
end;
--dbms_output.put_line(cc.owner || '.' || cc.tab_name || ': ' || li_count);
update comp_tab_rows set rows_9i = li_count where owner = cc.owner and tab_name = cc.tab_name;
commit;
end loop;
commit;
select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') into ls_end_date from dual;
dbms_output.put_line('End time: ' || ls_end_date);
exception
when others then
dbms_output.put_line(SQLERRM);
end;
/
create or replace procedure gen_19c_tab_rows(schema_name in varchar2) as
ls_sql varchar2(500);
li_count pls_integer;
ls_begin_date varchar2(30);
ls_end_date varchar2(30);
begin
select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') into ls_begin_date from dual;
dbms_output.put_line('Begin time: ' || ls_begin_date);
for cc in (select * from comp_tab_rows where owner = schema_name ) loop
ls_sql := 'select count(1) from ' || cc.owner || '."' || cc.tab_name || '"@to19cdcdb';
begin
execute immediate ls_sql into li_count;
exception
when others then
if sqlcode = -942 then
dbms_output.put_line(cc.owner || '.' || cc.tab_name || ' is not exists!');
update comp_tab_rows set rows_19c = -1 where owner = cc.owner and tab_name = cc.tab_name;
else
dbms_output.put_line(SQLERRM);
end if;
end;
--dbms_output.put_line(cc.owner || '.' || cc.tab_name || ': ' || li_count);
update comp_tab_rows set rows_19c = li_count where owner = cc.owner and tab_name = cc.tab_name;
commit;
end loop;
commit;
select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') into ls_end_date from dual;
dbms_output.put_line('End time: ' || ls_end_date);
exception
when others then
dbms_output.put_line(SQLERRM);
end;
/
create or replace procedure gen_oem_tab_rows as
ls_sql varchar2(500);
li_count pls_integer;
ls_begin_date varchar2(30);
ls_end_date varchar2(30);
begin
select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') into ls_begin_date from dual;
dbms_output.put_line('Begin time: ' || ls_begin_date);
for cc in (select owner, table_name from dba_tables@tofw251 t where t.owner = 'XXX') loop
ls_sql := 'select count(1) from ' || cc.owner || '."' || cc.table_name || '"@tofw251';
begin
execute immediate ls_sql into li_count;
exception
when others then
if sqlcode = -942 then
dbms_output.put_line(cc.owner || '.' || cc.table_name || ' is not exists!');
update comp_tab_rows set rows_19c = -1 where owner = cc.owner and tab_name = cc.table_name;
else
dbms_output.put_line(cc.owner || '.' || cc.table_name);
dbms_output.put_line(SQLERRM);
end if;
end;
--dbms_output.put_line(cc.owner || '.' || cc.table_name || ': ' || li_count);
update comp_tab_rows set rows_251 = li_count where owner = cc.owner and tab_name = cc.table_name;
commit;
end loop;
commit;
select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') into ls_end_date from dual;
dbms_output.put_line('End time: ' || ls_end_date);
exception
when others then
dbms_output.put_line(SQLERRM);
end;
/
*/
執行儲存過程查詢19c庫上表的行數：
set serveroutput on
exec gen_19c_tab_rows('XX'); -- 10s
select * from comp_tab_rows where owner in ('XXX') and rows_19c is null;
執行儲存過程查詢9i庫上表的行數：
set serveroutput on
exec gen_9i_tab_rows('XXX'); -- 13s
-- oem
exec gen_oem_tab_rows();
cat tbcnt3.log | awk '{print "update comp_tab_rows set rows_log = "$2" where tab_name = \047"$1"\047;"}' > tbcnt.sql
對比行數：
select * from comp_tab_rows where rows_9i <> rows_19c or rows_9i is null or rows_19c is null;
# 檢查索引並行度
set line 120
set pagesize 5000
select 'alter index ' || owner || '.' || index_name || ' noparallel;'
from dba_indexes where DEGREE > 1;
# 收集統計資訊 <<<< 整個收集完成大概15分鐘
set timing on
EXEC dbms_stats.gather_dictionary_stats; -- Elapsed: 00:01:10.96
EXEC dbms_stats.gather_fixed_objects_stats; -- Elapsed: 00:04:42.88
--EXEC dbms_stats.gather_database_stats;
--EXEC dbms_stats.gather_database_stats(estimate_percent => 15, cascade => TRUE);
--EXEC dbms_stats.gather_database_stats(estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,degree => 32);
EXEC dbms_stats.gather_schema_stats(ownname=>'XXX', degree=>32, cascade=>true, estimate_percent => dbms_stats.auto_sample_size, method_opt=> 'for all columns size auto');
Elapsed: 00:06:40.33
# 啟用觸發器
-- 目標伺服器(XX)執行
@/home/oracle/oracle_work/enabled_triggers.sql
set line 120
set pagesize 5000
col owner for a20
col trigger_name for a30
select 'alter trigger ' || owner || '.' || trigger_name || ' enable;'
from dba_triggers
where owner in ('XXX')
and status = 'DISABLED'
order by owner;
其他使用者：
select 'alter trigger ' || owner || '.' || trigger_name || ' enable;'
from dba_triggers
where owner in ('XXX')
and status = 'DISABLED'
order by owner;
# crontab等啟用
-- 目標伺服器(XXX)執行
-- enable root crontab
#資料庫引數放開
-- 目標伺服器(XXX)執行
su - oracle
sqlplus '/as sysdba'
alter system set job_queue_processes=100;
# 開啟歸檔
show parameter dest_1
shutdown IMMEDIATE
startup MOUNT
alter database ARCHIVELOG
alter database open
# 建立閃回點
create restore point rp_20230906 guarantee flashback database;
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31547506/viewspace-2987247/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle 9i升級19C 邏輯遷移詳細方法（一）Oracle
- Oracle 9i升級19C 遷移關於失效索引的梳理方法Oracle索引
- Oracle 9i 11g歷史庫升級遷移資料至19c CDBOracle
- 遷移式升級的測試（二）
- oracle 19c pdb遷移Oracle
- 【邏輯DG滾動升級三】ORACLE11204 邏輯DG滾動升級至12C---正式升級Oracle
- 資料庫邏輯遷移方案資料庫
- 【邏輯DG滾動升級二】ORACLE11204 邏輯DG滾動升級至12C---DG端前期準備Oracle
- Oracle資料庫升級或資料遷移的方法探討Oracle資料庫
- Oracle從Windows 11.2.0.1升級並遷移到Linux 19cOracleWindowsLinux
- Centos8中遷移邏輯卷CentOS
- iOS CoreData (二) 版本升級和資料庫遷移iOS資料庫
- 【XTTS】Oracle XTTS V4--Oracle11.2.0.4+ 遷移升級TTSOracle
- ORACLE資料庫升級詳細步驟Oracle資料庫
- Oracle邏輯讀詳解Oracle
- 【DataGuarad】邏輯遷移與standby備庫
- oracle 表遷移方法 （二） 約束不失效Oracle
- 遷移式升級的測試
- Oracle：Oracle RAC 11.2.0.4 升級為 19cOracle
- 【Oracle升級】Oracle指令碼升級11g to 19c non-CDBOracle指令碼
- oracle 9i statspack詳細講解Oracle
- Oracle 18C升級到19COracle
- 【邏輯DG滾動升級一】ORACLE11204 邏輯DG滾動升級至12C---生產端前期準備Oracle
- gitlab安裝/遷移/升級流程Gitlab
- 遷移式升級的測試（三）
- 遷移式升級的一點思考
- 資料庫的升級及遷移資料庫
- Oracle 11g升級PSU詳細步驟Oracle
- Docker安裝Oracle 19c 詳細教程DockerOracle
- Oracle 19c adg全庫遷移資料Oracle
- 【效能】Oracle表並行對邏輯遷移後查詢效能的影響Oracle並行
- oracle 9i升級到oracle9208Oracle
- oracle 表遷移方法 （一）Oracle
- Oracle 12c 使用（Full Transportable Export/Import）進行升級/遷移OracleExportImport
- Grafana的版本升級和資料遷移Grafana
- 按使用者進行資料庫邏輯遷移資料庫
- 【資料庫升級】Oracle指令碼升級12c CDB to 19c CDB資料庫Oracle指令碼
- Oracle 9i RAC向單例項遷移手記Oracle單例