–生产库对象统计：

spool $ORACLE_HOME/CTAIS_BAK/db_obj_status_bk

prompt “统计用户对象数量”;
SELECT COUNT(*),
OWNER,
OBJECT_TYPE
FROM DBA_OBJECTS
WHERE OWNER NOT IN (‘SYS’,'SYSTEM’,'OUTLN’,'DBSNMP’,'WMSYS’)
GROUP BY OWNER,OBJECT_TYPE
ORDER BY 2,3;
prompt “统计失效用户对象情况”;
SELECT count(*),owner
FROM dba_objects
WHERE status=’INVALID’
group by owner;
prompt “统计失效用户对象情况明细”;
SELECT distinct object_name
FROM dba_objects
WHERE status= ‘INVALID’;
prompt “统计失效外键”;
Select owner,
constraint_type,
constraint_name,
table_name,
r_owner,
r_constraint_name,
status
From dba_constraints
Where owner not in (‘SYS’,'SYSTEM’,'OUTLN’,'DBSNMP’,'WMSYS’)
And status <> ‘ENABLED’
ORDER BY OWNER;
prompt “统计失效索引”;
SELECT A.INDEX_OWNER,
A.INDEX_NAME,
A.TABLE_NAME,
A.COLUMN_NAME,
B.INDEX_TYPE,
B.TABLESPACE_NAME
FROM DBA_IND_COLUMNS A, DBA_INDEXES B
WHERE A.INDEX_NAME = B.INDEX_NAME
AND B.STATUS <> ‘VALID’
AND B.TABLESPACE_NAME IS NOT NULL;
prompt “统计失效触发器”;
select trigger_name, table_name, status
From dba_triggers
Where status <> ‘ENABLED’;
prompt “统计表空间使用明细”;
select a.tablespace_name ,
totalg,
round(freeg,2),
ROUND(totalg-freeg,2) USEDM,
round(100*(totalg-freeg)/totalg,2) used_pct
from
(select sum(bytes/1024/1024/1024) totalg,
tablespace_name
from dba_data_files
group by tablespace_name) a,
(select sum(bytes/1024/1024/1024) freeg,
tablespace_name
from dba_free_space
group by tablespace_name) b
where a.tablespace_name=b.tablespace_name;
prompt “统计数据库空间使用”;
select sum(totalg),sum(usedg)
from
(select a.tablespace_name ,
totalg,
round(freeg,2),
ROUND(totalg-freeg,2) USEDG,
round(100*(totalg-freeg)/totalg,2) used_pct
from
(select sum(bytes/1024/1024/1024) totalg,tablespace_name
from dba_data_files group by tablespace_name) a,
(select sum(bytes/1024/1024/1024) freeg,tablespace_name
from dba_free_space group by tablespace_name) b
where a.tablespace_name=b.tablespace_name);
prompt “检查JOB”;
select job,
log_user,
schema_user,
what,
broken,
failures
from dba_jobs;
spool off


–生成用户权限脚本
drop table app_maint.role_system_object_privs;

create table app_maint.role_system_object_privs
(username varchar(30),
privs_flag varchar(30),
ddl_sql clob);
insert into app_maint.role_system_object_privs
select r.grantee,
‘ROLE_GRANT’,
dbms_metadata.get_granted_ddl(‘ROLE_GRANT’,R.GRANTEE)
from app_maint.DBA_ROLE_PRIVS_BAK R group by r.grantee;
COMMIT;

insert into app_maint.role_system_object_privs
select o.grantee,
‘OBJECT_GRANT’,
dbms_metadata.get_granted_ddl(‘OBJECT_GRANT’,O.GRANTEE)
from DBA_TAB_PRIVS O group by O.grantee;
COMMIT;

insert into app_maint.role_system_object_privs
SELECT S.GRANTEE,
‘SYSTEM_GRANT’,
DBMS_METADATA.GET_GRANTED_DDL(‘SYSTEM_GRANT’,S.GRANTEE)
FROM DBA_SYS_PRIVS S
WHERE S.ADMIN_OPTION != ‘YES’
GROUP BY S.GRANTEE;
COMMIT;

–备份索引
DROP TABLE app_maint.INDEX_SCRIPTS;
DROP TABLE app_MAINT.USER_INDEXES_BAK;
CREATE TABLE app_MAINT.USER_INDEXES_BAK
AS SELECT * FROM USER_INDEXES;

–抽取主键

CREATE TABLE app_maint.INDEX_SCRIPTS
(OWNER VARCHAR2(30),
TABLE_NAME VARCHAR2(30),
INDEX_NAME VARCHAR2(30),
CONSTRAINT_NAME VARCHAR2(30),
OBJECT_TYPE CHAR(1),
DDL_SQL CLOB);

INSERT INTO app_maint.INDEX_SCRIPTS
select OWNER,
TABLE_NAME,
INDEX_NAME,
CONSTRAINT_NAME,
‘P’,
replace (dbms_metadata.get_ddl(‘CONSTRAINT’,CONSTRAINT_NAME,OWNER),’”‘,”)
FROM DBA_CONSTRAINTS
WHERE CONSTRAINT_TYPE=’P’
AND OWNER NOT IN (‘SYS’,'SYSTEM’,'OUTLN’,'DBSNMP’,'WMSYS’);

COMMIT;

–抽取索引
INSERT INTO app_maint.INDEX_SCRIPTS
select OWNER,TABLE_NAME,INDEX_NAME,NULL,
‘I’,
replace (dbms_metadata.get_ddl(‘INDEX’,INDEX_NAME,OWNER),’”‘,”)
FROM DBA_INDEXES A
WHERE NOT EXISTS(SELECT 1
FROM app_maint.INDEX_SCRIPTS
WHERE TABLE_NAME=A.TABLE_NAME
AND INDEX_NAME=A.INDEX_NAME)
AND INDEX_TYPE<>’LOB’
AND OWNER NOT IN (‘SYS’,'SYSTEM’,'OUTLN’,'DBSNMP’,'WMSYS’);

COMMIT;

–抽取物化视图日志脚本
DROP TABLE app_maint.dba_mview_logs_bak;
create table app_maint.dba_mview_logs_bak
as select * from dba_mview_logs;

DROP TABLE app_maint.user_mview_logs_sql;
create table app_maint.user_mview_logs_sql
(LOG_OWNER VARCHAR2(30),
LOG_TABLE VARCHAR2(20),
MASTER VARCHAR2(30),
DDL_SQL CLOB
);

INSERT INTO app_maint.user_mview_logs_sql
select LOG_OWNER,LOG_TABLE,MASTER,
REPLACE (dbms_metadata.get_ddl(‘MATERIALIZED_VIEW_LOG’,LOG_TABLE,LOG_OWNER),’”‘,”)
FROM DBA_MVIEW_LOGS
where LOG_OWNER NOT IN (‘SYS’,'SYSTEM’,'OUTLN’,'DBSNMP’,'WMSYS’);

commit;

–抽取物化视图脚本
drop table app_maint.DBA_MVIEWS_BAK;
create table app_maint.DBA_MVIEWS_BAK
as select owner,mview_name from DBA_MVIEWS;

–抽取物化视图定义
set long 9999
drop table app_maint.mview_scripts;

create table app_maint.mview_scripts
(owner varchar2(30),
mview_name varchar2(30),
ddl_sql clob);

insert into app_maint.mview_scripts
select owner,
mview_name,
dbms_metadata.get_ddl(‘MATERIALIZED_VIEW’,MVIEW_NAME,OWNER)
FROM DBA_MVIEWS
where OWNER NOT IN (‘SYS’,'SYSTEM’,'OUTLN’,'DBSNMP’,'WMSYS’);

commit;

–统计迁移前用户业务表数据

set timing on

prompt “正在准备统计表…”;

select to_char(sysdate,’yyyy-mm-dd hh24:mi:ss’) as time from dual;

drop table app_maint.all_compare_qyq_qyh_data;
create table app_maint.all_compare_qyq_qyh_data(
owner varchar2(30),
table_name varchar2(50),
qyq_rows number,
qyh_rows number,
constraint pk_all_compare_qyq_qyh_data primary key (owner,table_name)
);


insert into app_maint.all_compare_qyq_qyh_data
select owner,table_name, null, null from dba_tables
where owner not in (‘SYS’,'SYSTEM’,'OUTLN’,'DBSNMP’,'WMSYS’,'APP_MAINT’);
commit;

prompt “正在统计生产机迁移前数据…”;
select to_char(sysdate,’yyyy-mm-dd hh24:mi:ss’) as time from dual;
set serveroutput on
declare
ls_sql varchar2(600);
ls_obj_owner varchar2(30);
ls_obj_name varchar2(30);
li_cid integer;
li_execute integer;
cursor cur_table is
select owner,table_name from app_maint.all_compare_qyq_qyh_data;
begin
li_cid := dbms_sql.open_cursor;
open cur_table;
loop
fetch cur_table
into ls_obj_owner,ls_obj_name;
exit when cur_table%notfound;
ls_sql := ‘update app_maint.all_compare_qyq_qyh_data
set qyq_rows = (select count(*) from ‘ || ls_obj_owner || ‘.’ || ls_obj_name ||’ )
where table_name = ”’ || ls_obj_name || ”’
and owner = ”’ || ls_obj_owner || ””;
dbms_sql.parse(li_cid, ls_sql, dbms_sql.native);
li_execute := dbms_sql.execute(li_cid);
commit;
end loop;
close cur_table;
dbms_sql.close_cursor(li_cid);
exception
when others then
raise_application_error(-20999,
‘failed:’ || to_char(sqlcode) || sqlerrm);
dbms_sql.close_cursor(li_cid);
end;
/

select to_char(sysdate,’yyyy-mm-dd hh24:mi:ss’) as time from dual;

prompt “统计生产机迁移前数据结束!”;

–数据分析

set timing on
exec dbms_stats.gather_database_stats(estimate_percent => 100,method_opt => ‘for all columns size 1′,degree => 24,cascade => true,gather_sys => false,gather_temp => false,gather_fixed => false);
set timing off

–迁移完毕后的数据比对

–统计迁移前用户业务表数据

set timing on
prompt “正在统计生产机迁移后数据…”;
select to_char(sysdate,’yyyy-mm-dd hh24:mi:ss’) as time from dual;
set serveroutput on
declare
ls_sql varchar2(600);
ls_obj_owner varchar2(30);
ls_obj_name varchar2(30);
li_cid integer;
li_execute integer;
cursor cur_table is
select owner,table_name from app_maint.all_compare_qyq_qyh_data;
begin
li_cid := dbms_sql.open_cursor;
open cur_table;
loop
fetch cur_table
into ls_obj_owner,ls_obj_name;
exit when cur_table%notfound;
ls_sql := ‘update app_maint.all_compare_qyq_qyh_data
set qyh_rows = (select count(*) from ‘ || ls_obj_owner || ‘.’ || ls_obj_name ||’ )
where table_name = ”’ || ls_obj_name || ”’
and owner = ”’ || ls_obj_owner || ””;
dbms_sql.parse(li_cid, ls_sql, dbms_sql.native);
li_execute := dbms_sql.execute(li_cid);
commit;
end loop;
close cur_table;
dbms_sql.close_cursor(li_cid);
exception
when others then
raise_application_error(-20999,
‘failed:’ || to_char(sqlcode) || sqlerrm);
dbms_sql.close_cursor(li_cid);
end;
/

select to_char(sysdate,’yyyy-mm-dd hh24:mi:ss’) as time from dual;

prompt “统计生产机迁移后数据结束!”;

prompt “数据比对！”
set serveroutput on
declare
ls_obj_owner varchar2(30);
ls_obj_name varchar2(30);
ls_row_compa integer;
cursor cur_table is
select owner,table_name,qyq_rows-qyh_rows as compare from app_maint.all_compare_qyq_qyh_data;
begin
dbms_output.enable(buffer_size => null);
open cur_table;
loop
fetch cur_table
into ls_obj_owner,ls_obj_name,ls_row_compa;
exit when cur_table%notfound;
if ls_row_compa <> 0
then
dbms_output.put_line(‘data_lost=>owner:’||ls_obj_owner||’ talbe:’||ls_obj_name||’ lost_data:’||ls_row_compa);
end if;
end loop;
close cur_table;
exception
when others then
raise_application_error(-20999,
‘failed:’ || to_char(sqlcode) || sqlerrm);
end;
/