升級check SQL01

weixin_33843409發表於2019-04-09
SQL

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;

相關文章