升级check SQL01

简介: conn / as sysdbaSET termout on feedback on verify OFF echo on heading OFF trims ON escape ON pages 0 linesize 511 long 3000--在11g 操作系统创建迁移升级使用的目录hos...

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;

目录
相关文章
|
5月前
|
SQL Oracle 关系型数据库
SQL CHECK 约束
【7月更文挑战第19天】SQL CHECK 约束。
48 5
|
7月前
|
SQL 数据采集 关系型数据库
如何解决MySQL报错 You have an error in your SQL syntax; check the manual that corresponds to your MySQL?
如何解决MySQL报错 You have an error in your SQL syntax; check the manual that corresponds to your MySQL?
12059 2
|
4月前
|
SQL 存储 Oracle
MySQL 项目中 SQL 脚本更新、升级方式,防止多次重复执行
MySQL 项目中 SQL 脚本更新、升级方式,防止多次重复执行
72 0
|
4月前
|
SQL 运维 Oracle
SQL Server 项目中 SQL 脚本更新、升级方式,防止多次重复执行
SQL Server 项目中 SQL 脚本更新、升级方式,防止多次重复执行
58 0
|
6月前
|
SQL Oracle 关系型数据库
SQL CHECK 约束
SQL CHECK 约束
40 3
|
5月前
|
SQL 关系型数据库 MySQL
【Python】已解决:ERROR 1064 (42000): You have an error in your SQL syntax. check the manual that correspo
【Python】已解决:ERROR 1064 (42000): You have an error in your SQL syntax. check the manual that correspo
2698 0
|
5月前
|
SQL 关系型数据库 MySQL
You have an error in your SQL syntax;check the manual that corresponds to you Mysql server version
You have an error in your SQL syntax;check the manual that corresponds to you Mysql server version
|
6月前
|
SQL 存储 关系型数据库
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server
|
6月前
|
SQL 关系型数据库 MySQL
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version
|
SQL 关系型数据库 MySQL
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version
错误提示: Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; che
132 0