升级check SQL01-白红宇

conn / as sysdba

SET termout on feedback on verify OFF echo on heading OFF trims ON escape ON pages 0 linesize 511 long 3000

--在11g 操作系统创建迁移升级使用的目录

host mkdir -p $HOME/$ORACLE_SID/migup/perf

spool $HOME/$ORACLE_SID/migup/perf/perf_test.log

--创建要迁移的用户清单配置表

CREATE TABLE DBA_OWNER.t_user_list(

username VARCHAR2(30) primary key,

flag varchar2(1),

src_tbs VARCHAR2(30),

dst_tbs VARCHAR2(30)

);

create public synonym t_user_list for DBA_OWNER.t_user_list;

--在11g性能测试库中创建使用的dblink 特别注意：升级不同的库要修改using使用的连接串同时演练的源库里的DBA_OWNER的密码要改为pass1234

create public database link dblink_migup connect to DBA_OWNER identified by pass1234 using '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xxxxxxxxx)(PORT=1534))(connect_data=(sid=testdb)))';

--初始化用户清单配置表

INSERT INTO DBA_OWNER.t_user_list

(username, flag, src_tbs,dst_tbs)

SELECT username, 'Y', default_tablespace, default_tablespace

FROM dba_users@dblink_migup

WHERE username not in (select username from t_user_list)

and username not in     ('SYS', 'SYSTEM', 'PUBLIC', 'OUTLN', 'WMSYS', 'ORDSYS', 'MDSYS',      'CTXSYS', 'XDB', 'WKSYS', 'ANONYMOUS', 'ORDPLUGINS', 'EXFSYS',      'SCOTT', 'JONES', 'CLARK', 'BLAKE', 'ADAMS', 'TRACESVR') and username not in     ('FGLPA', 'PERFSTAT', 'SPOTLIGHT', 'I3_ORCL', 'MIGUPS',      'DBSNMP', 'FOGLIGHT', 'DBSTATS', 'DBAQUERY', 'GGMGR', 'GGDEPLOY',      'RSMSMONOPR', 'DEPLOYOP', 'EXPFULL', 'FACTUSERMGR', 'OVSEE',      'DBCONN', 'DBMON', 'TOAD');

spool off;

--在11g目标库中创建迁移表空间，打开undo表空间和temp表空间的自动扩展

SET termout OFF feedback OFF verify OFF echo OFF heading OFF trims ON escape ON pages 0 linesize 511 long 3000

spool $HOME/$ORACLE_SID/migup/perf/01_11g_deal_tablespaces.sql

--data tablespace

select (case

when rn = 1 then      'create smallfile tablespace ' || tablespace_name ||      '  logging datafile ' ||      (select ''''||substr(file_name, 1, instr(file_name, '/') - 1)||''''         from dba_data_files        where rownum = 1) || ' size ' || msize ||      ' autoextend on'|| (case when rn=max_rn then ' ;' end)     else      ',' || (select ''''||substr(file_name, 1, instr(file_name, '/') - 1)||''''                from dba_data_files               where rownum = 1) || ' size ' || msize ||      ' autoextend on'||(case when rn=max_rn then ' ;' end)   end)

from (select sddf.tablespace_name,

trunc(sddf.bytes / 1024 / 1024) || 'M' msize,           substr(sddf.file_name, 1, instr(sddf.file_name, '/') - 1),           instr(sddf.file_name, '/') - 1,           row_number() over(partition by sddf.tablespace_name order by file_id) rn,           count(*)  over(partition by sddf.tablespace_name) max_rn      from dba_data_files@dblink_migup sddf     where sddf.tablespace_name not in           (select distinct ddf.tablespace_name from dba_data_files ddf) and  sddf.tablespace_name not like 'UNDO%'     order by tablespace_name, rn asc);

--temp tablespace

select (case

when rn = 1 then      'create smallfile tablespace ' || tablespace_name ||      '  logging datafile ' ||      (select ''''||substr(file_name, 1, instr(file_name, '/') - 1)||''''         from dba_data_files        where rownum = 1) || ' size ' || msize ||      ' autoextend on'|| (case when rn=max_rn then ' ;' end)     else      ',' || (select ''''||substr(file_name, 1, instr(file_name, '/') - 1)||''''                from dba_data_files               where rownum = 1) || ' size ' || msize ||      ' autoextend on'||(case when rn=max_rn then ' ;' end)   end)

from (select sddf.tablespace_name,

trunc(sddf.bytes / 1024 / 1024) || 'M' msize,           substr(sddf.file_name, 1, instr(sddf.file_name, '/') - 1),           instr(sddf.file_name, '/') - 1,           row_number() over(partition by sddf.tablespace_name order by file_id) rn,           count(*)  over(partition by sddf.tablespace_name) max_rn      from dba_temp_files@dblink_migup sddf     where sddf.tablespace_name not in           (select distinct ddf.tablespace_name from dba_temp_files ddf) and  sddf.tablespace_name not like 'UNDO%'     order by tablespace_name, rn asc);

select 'alter database tempfile '||''''||df.file_name||''''||' autoextend on;' from dba_temp_files df where df.autoextensible='NO';

select 'alter database datafile '||''''||df.file_name||''''||' autoextend on;' from dba_data_files df where df.autoextensible='NO' and df.tablespace_name like 'UNDO%';

spool off;

commit;

SET termout on feedback on verify OFF echo on heading OFF trims ON escape ON pages 0 linesize 511 long 3000

spool $HOME/$ORACLE_SID/migup/perf/perf_test.log append

@$HOME/$ORACLE_SID/migup/perf/01_11g_deal_tablespaces.sql

--复核:11g表空间是否创建完整，正确结果为no rows selected

select tablespace_name from dba_tablespaces@dblink_migup

minus

select tablespace_name from dba_tablespaces;

--复核undo和temp表空间的自动扩展是否打开，正确结果为no rows selected

select df.tablespace_name,df.autoextensible from dba_temp_files df where df.autoextensible='NO';

select df.tablespace_name,df.autoextensible from dba_data_files df where df.autoextensible='NO';

spool off;

commit;

--在11g目标库中创建迁移角色

SET termout OFF feedback OFF verify OFF echo OFF heading OFF trims ON escape ON pages 0 linesize 511 long 3000

spool $HOME/$ORACLE_SID/migup/perf/01_11g_deal_role.sql

select 'create role ' || role || ';'

from (select role

from dba_roles@dblink_migup    minus    select role from dba_roles);

spool off;

commit;

SET termout on feedback on verify OFF echo on heading OFF trims ON escape ON pages 0 linesize 511 long 3000

spool $HOME/$ORACLE_SID/migup/perf/perf_test.log append

@$HOME/$ORACLE_SID/migup/perf/01_11g_deal_role.sql

--复核11g目标库中角色是否创建完整，正确结果为no rows selected

select role

from dba_roles@dblink_migup

minus

select role from dba_roles;

spool off;

commit;

--------------------------------------------------------------111111111111111111111------------

SET termout on feedback on verify OFF echo on heading OFF trims ON escape ON pages 0 linesize 999 long 3000

--记录日志,创建迁移升级使用的目录

host mkdir -p $HOME/$ORACLE_SID/migup/uping/scrpt

host mkdir -p $HOME/$ORACLE_SID/migup/uping/log

spool $HOME/$ORACLE_SID/migup/uping/log/end_perf_env_cfg.log

--清除impdp导到sys用户下的job

DECLARE X NUMBER;

BEGIN

for cur in ( select * from dba_jobs j where j.log_user = 'SYS') loop

SYS.DBMS_JOB.remove(job => cur.job );

COMMIT;

end loop ;

commit;

END;

--复核Job个数,对于导出导入，正确结果为no rows selected

select * from dba_jobs ;

spool off;

--修改以SYS命名的index和constraint。

SET termout OFF feedback OFF verify OFF echo OFF heading OFF trims ON escape ON pages 0 linesize 1000 long 3000

spool $HOME/$ORACLE_SID/migup/uping/scrpt/alter_index_rename.sql

SELECT DISTINCT 'alter index ' || A.sos_owner || '.' || A.new_name ||' rename to ' || A.old_name || ';'

FROM (select tar.index_owner sos_owner,

tar.index_name new_name,           sos.index_name old_name,           sos.table_name,           sos.column_name,           sos.column_position      from dba_ind_columns tar, dba_ind_columns@dblink_migup sos     where sos.index_owner = tar.index_owner       and sos.table_owner = tar.table_owner       and sos.table_name = tar.table_name       and sos.column_name = tar.column_name       and sos.column_position = tar.column_position       and sos.column_length = tar.column_length       and tar.table_owner in (select username from migupd.t_user_list)       and tar.table_owner not in('APPMGR','dba_owner','DBQUA','DMLBAK')       and tar.index_NAME like 'SYS_C%'       and sos.index_NAME like 'SYS_C%'       and tar.index_name <> sos.index_name     order by 1, 2, 6, 5) A;

spool off;

commit;

SET termout on feedback on verify OFF echo on heading OFF trims ON escape ON pages 0 linesize 1000 long 3000

spool $HOME/$ORACLE_SID/migup/uping/log/end_perf_env_cfg.log append

@$HOME/$ORACLE_SID/migup/uping/scrpt/alter_index_rename.sql

spool off;

commit;

-- audit user

SET termout OFF feedback OFF verify OFF echo OFF heading OFF trims ON escape ON pages 0 linesize 999 long 3000

spool $HOME/$ORACLE_SID/migup/uping/scrpt/audit_users.sql

SELECT

'audit connect by '||user_name||';'

FROM dba_stmt_audit_opts@dblink_migup

WHERE audit_option = 'CREATE SESSION'

and user_name in ( select username from migupd.t_user_list t where flag ='Y' or flag is null )

and user_name is not null;

SELECT

'audit '||audit_option||' by '||user_name||' by session whenever successful;'

FROM dba_stmt_audit_opts@dblink_migup

WHERE audit_option <> 'CREATE SESSION'

and user_name in ( select username from migupd.t_user_list t where flag ='Y' or flag is null )

and user_name is not null;

SPOOL OFF;

commit;

--exec grant role priv script

SET termout on feedback on verify OFF echo on heading OFF trims ON escape ON pages 0 linesize 999 long 3000

spool $HOME/$ORACLE_SID/migup/uping/log/end_perf_env_cfg.log append

@$HOME/$ORACLE_SID/migup/uping/scrpt/audit_users.sql

commit;

SPOOL OFF;

--创建迁移用户的公共同义词

SET termout OFF feedback OFF verify OFF echo OFF heading OFF trims ON escape ON pages 0 linesize 999 long 3000

spool $HOME/$ORACLE_SID/migup/uping/scrpt/create_public_synonym.sql

SELECT

'create or replace public synonym '||synonym_name||' for '||table_owner||'.'||table_name||';'

FROM dba_synonyms@dblink_migup

WHERE owner = 'PUBLIC'

AND table_owner IN ( select username from migupd.t_user_list t where flag ='Y' or flag is null ) ;

spool off;

commit;

SET termout on feedback on verify OFF echo on heading OFF trims ON escape ON pages 0 linesize 999 long 3000

spool $HOME/$ORACLE_SID/migup/uping/log/end_perf_env_cfg.log append

@$HOME/$ORACLE_SID/migup/uping/scrpt/create_public_synonym.sql

spool off;

commit;

--grant role priv

-- generate grant role priv script

SET termout OFF feedback OFF verify OFF echo OFF heading OFF trims ON escape ON pages 0 linesize 999 long 3000

spool $HOME/$ORACLE_SID/migup/uping/scrpt/grant_role_priv.sql

--SELECT 'spool $HOME/$ORACLE_SID/migup/uping/log/grant_role_priv.log' FROM dual;

SELECT 'grant ' || granted_role || ' to ' || grantee || CASE admin_option

WHEN 'YES' THEN      ' with admin option;'     ELSE      ';'   END

FROM dba_role_privs@dblink_migup

WHERE grantee IN

( select username from migupd.t_user_list t where flag ='Y' or flag is null   union  select distinct granted_role from dba_role_privs@dblink_migup WHERE grantee IN (select username from migupd.t_user_list t where flag = 'Y' or flag is null)  and granted_role like 'R\\_%' escape '\\') ;

--SELECT 'spool off;' FROM dual;

SPOOL OFF;

commit;

--exec grant role priv script

SET termout on feedback on verify OFF echo on heading OFF trims ON escape ON pages 0 linesize 999 long 3000

spool $HOME/$ORACLE_SID/migup/uping/log/end_perf_env_cfg.log append

@$HOME/$ORACLE_SID/migup/uping/scrpt/grant_role_priv.sql

commit;

SPOOL OFF;

--grant sys priv

-- generate grant sys priv script

SET termout OFF feedback OFF verify OFF echo OFF heading OFF trims ON escape ON pages 0 linesize 999 long 3000

spool $HOME/$ORACLE_SID/migup/uping/scrpt/grant_sys_priv.sql

--SELECT 'spool $HOME/$ORACLE_SID/migup/uping/log/grant_sys_priv.log' FROM dual;

SELECT

'grant '||privilege||' to '||grantee

||CASE admin_option WHEN 'YES' THEN ' with admin option;' ELSE ';' END

FROM dba_sys_privs@dblink_migup

WHERE grantee IN

( select username from migupd.t_user_list t where flag ='Y' or flag is null   union  select distinct granted_role from dba_role_privs@dblink_migup WHERE grantee IN (select username from migupd.t_user_list t where flag = 'Y' or flag is null)  and granted_role like 'R\\_%' escape '\\') ;

SELECT

'grant '||privilege||' to '||grantee

||CASE admin_option WHEN 'YES' THEN ' with admin option;' ELSE ';' END

FROM dba_sys_privs@dblink_migup

WHERE grantee IN (SELECT role FROM dba_roles WHERE password_required <> 'NO' AND ROLE LIKE 'R\_%' ESCAPE '\');

--SELECT 'spool off;' FROM dual;

SPOOL OFF;

commit;

--exec grant sys priv script

SET termout on feedback on verify OFF echo on heading OFF trims ON escape ON pages 0 linesize 999 long 3000

spool $HOME/$ORACLE_SID/migup/uping/log/end_perf_env_cfg.log append

@$HOME/$ORACLE_SID/migup/uping/scrpt/grant_sys_priv.sql

commit;

SPOOL OFF;

-- if 9i upto 11g ,should deal connect role

--grant sys tab priv

-- generate grant sys tab priv script

SET termout OFF feedback OFF verify OFF echo OFF heading OFF trims ON escape ON pages 0 linesize 999 long 3000

spool $HOME/$ORACLE_SID/migup/uping/scrpt/grant_sys_tab_priv.sql

--SELECT 'spool $HOME/$ORACLE_SID/migup/uping/log/grant_sys_tab_priv.log' FROM dual;

SELECT

'grant '||privilege||' on '

||CASE WHEN substr(table_name,1,2)='V$' THEN 'V_$'||substr(table_name,3,length(table_name-2)) ELSE table_name END||' to '||grantee||CASE grantable WHEN 'YES' THEN ' with grant option;' ELSE ';' END

FROM dba_tab_privs@dblink_migup

WHERE grantee IN

( select username from migupd.t_user_list t where flag ='Y' or flag is null   union  select distinct granted_role from dba_role_privs@dblink_migup WHERE grantee IN (select username from migupd.t_user_list t where flag = 'Y' or flag is null)  and granted_role like 'R\\_%' escape '\\') AND owner = 'SYS'and table_name not in ( 'ORA_TEMP_1_DS_8865' ,'UTL_CON')    AND privilege NOT IN ('READ','WRITE');

SELECT

'grant '||privilege||' on directory '||table_name||' to '||grantee

||CASE grantable WHEN 'YES' THEN ' with grant option;' ELSE ';' END

FROM dba_tab_privs@dblink_migup

WHERE grantee IN

( select username from migupd.t_user_list t where flag ='Y' or flag is null   union  select distinct granted_role from dba_role_privs@dblink_migup WHERE grantee IN (select username from migupd.t_user_list t where flag = 'Y' or flag is null)  and granted_role like 'R\\_%' escape '\\') AND owner = 'SYS'AND privilege IN ('READ', 'WRITE')AND table_name not in (

'QUEST_SOO_ADUMP_DIR',

'GL_DIR',

'Q_ALERT_LOG_EOA_LOC',

'QUEST_SOO_BDUMP_DIR',

'QUEST_SOO_CDUMP_DIR',

'QUEST_SOO_UDUMP_DIR',

'EOA_NDB_DIR',

'EXPDP_DIR',

'EXPDP_CSB',

'DIR_GGSERR_LOG' );

--SELECT 'spool off;' FROM dual;

SPOOL OFF;

commit;

--exec grant sys tab priv script

SET termout on feedback on verify OFF echo on heading OFF trims ON escape ON pages 0 linesize 999 long 3000

spool $HOME/$ORACLE_SID/migup/uping/log/end_perf_env_cfg.log append

@$HOME/$ORACLE_SID/migup/uping/scrpt/grant_sys_tab_priv.sql

commit;

SPOOL OFF;

-- grant non-sys tab priv

-- generate grant non-sys tab priv script

SET termout OFF feedback OFF verify OFF echo OFF heading OFF trims ON escape ON pages 0 linesize 999 long 3000

spool $HOME/$ORACLE_SID/migup/uping/scrpt/grant_tab_priv.sql

--SELECT 'spool $HOME/$ORACLE_SID/migup/uping/log/grant_tab_priv.log' FROM dual;

SELECT

'grant '||privilege||' on '||owner||'.'||table_name||' to '||grantee

||CASE grantable WHEN 'YES' THEN ' with grant option;' ELSE ';' END

FROM dba_tab_privs@dblink_migup

WHERE grantee IN

( select username from migupd.t_user_list t where flag ='Y' or flag is null   union  select distinct granted_role from dba_role_privs@dblink_migup WHERE grantee IN (select username from migupd.t_user_list t where flag = 'Y' or flag is null)  and granted_role like 'R\\_%' escape '\\') and owner not in (select username from migupd.t_user_list t where flag ='N' )  AND owner <> 'SYS'AND privilege NOT IN ('READ','WRITE');

SELECT

'grant '||privilege||' on directory '||owner||'.'||table_name||' to '||grantee

||CASE grantable WHEN 'YES' THEN ' with grant option;' ELSE ';' END

FROM dba_tab_privs@dblink_migup

WHERE grantee IN

( select username from migupd.t_user_list t where flag ='Y' or flag is null   union  select distinct granted_role from dba_role_privs@dblink_migup WHERE grantee IN (select username from migupd.t_user_list t where flag = 'Y' or flag is null)  and granted_role like 'R\\_%' escape '\\') AND owner <> 'SYS'and owner not in (select username from migupd.t_user_list t where flag ='N' )      AND privilege IN ('READ', 'WRITE')AND table_name not in (

'QUEST_SOO_ADUMP_DIR',

'GL_DIR',

'Q_ALERT_LOG_EOA_LOC',

'QUEST_SOO_BDUMP_DIR',

'QUEST_SOO_CDUMP_DIR',

'QUEST_SOO_UDUMP_DIR',

'EOA_NDB_DIR',

'EXPDP_DIR',

'EXPDP_CSB',

'DIR_GGSERR_LOG' );

--SELECT 'spool off;' FROM dual;

SPOOL OFF;

commit;

--exec grant non-sys tab priv script

SET termout on feedback on verify OFF echo on heading OFF trims ON escape ON pages 0 linesize 999 long 3000

spool $HOME/$ORACLE_SID/migup/uping/log/end_perf_env_cfg.log append

@$HOME/$ORACLE_SID/migup/uping/scrpt/grant_tab_priv.sql

commit;

SPOOL OFF;

SET termout on feedback on verify OFF echo on heading OFF trims ON escape ON pages 0 linesize 999 long 3000

spool $HOME/$ORACLE_SID/migup/uping/log/end_perf_env_cfg.log append

--开始编译失效对象

SET termout OFF feedback OFF verify OFF echo OFF heading OFF trims ON escape ON pages 0 linesize 999 long 3000

--编译失效对象

@?/rdbms/admin/utlrp.sql;

SET termout on feedback on verify OFF echo on heading OFF trims ON escape ON pages 0 linesize 999 long 3000

--已完成编译失效对象

SPOOL OFF;