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;
@?/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;