升级用户创建

  1. 云栖社区>
  2. 博客>
  3. 正文

升级用户创建

jan1990 2019-04-09 12:39:24 浏览232
展开阅读全文

--1. 创建源库迁移临时用户
CREATE USER migups IDENTIFIED BY &pw
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON users;

GRANT dba TO migups;
GRANT select ON sys.user$ TO migups;
GRANT RESTRICTED SESSION TO migups;
GRANT EXP_FULL_DATABASE TO migups;
GRANT EXECUTE ON SYS.DBMS_LOCK TO migups ;
GRANT SELECT ON SYS.LINK$ TO migups ;

--2. 创建源库迁移用户清单记录表
CREATE TABLE migups.s_user_list(
username VARCHAR2(30) primary key,
flag varchar2(1),
ggflag int,
src_tbs VARCHAR2(30),
dst_tbs VARCHAR2(30)
);
comment on column migups.s_user_list.FLAG
is 'y或Y表示要迁移,n或N表示不迁移';
comment on column migups.s_user_list.GGFLAG
is '1表示要配置在gg回滚链路里 0或空表示不配置在gg回滚链路里';

--在源库中migups用户下创建要迁移的表空间清单配置表
CREATE TABLE migups.s_tbs_list(
tbsname VARCHAR2(30)
);

--3. 创建源库Job备份清单表
create table migups.S_JOB_LIST
(
JOB NUMBER not null,
ISMIG VARCHAR2(1) not null,
SRC VARCHAR2(100) not null,
LOG_USER VARCHAR2(30),
PRIV_USER VARCHAR2(30),
SCHEMA_USER VARCHAR2(30),
LAST_DATE DATE,
LAST_SEC VARCHAR2(16),
THIS_DATE DATE,
THIS_SEC VARCHAR2(16),
NEXT_DATE DATE,
NEXT_SEC VARCHAR2(16),
TOTAL_TIME NUMBER,
BROKEN VARCHAR2(1),
INTERVAL VARCHAR2(200),
FAILURES NUMBER,
WHAT VARCHAR2(4000) not null,
NLS_ENV VARCHAR2(4000) ,
INSTANCE NUMBER,
resume_nextdate varchar2(20)
);
comment on column migups.S_JOB_LIST.ISMIG
is '是否迁移 0:是目标库初始化时创建的不能迁移或删除 1:是 2:否 3:其它(未定)';
comment on column migups.S_JOB_LIST.SRC
is 'job来源, 如:SRC_10G:来自10g源库 TGT_11G:来自11g目标库即11g目标库初始化时创建的';
comment on column migups.S_JOB_LIST.resume_nextdate
is '恢复Job时候,设置下次运行的时间' ;

---4. 在源库部署数据校验对比的table及procedure
create table dba_owner.TAB_HASH
(
OWNER VARCHAR2(30),
TABLE_NAME VARCHAR2(50),
SQL_VALUE VARCHAR2(4000),
HASH_VALUE NUMBER(20)
) ;

CREATE OR REPLACE PROCEDURE dba_owner.GET_TAB_HASH8(V_OWNER VARCHAR2,
V_TABLE_NAME VARCHAR2)
AS
v_sql_temp varchar2(4000);
v_hash number(20);
BEGIN
FOR r in (SELECT COLUMN_NAME FROM DBA_TAB_COLUMNS WHERE OWNER = V_OWNER AND TABLE_NAME = V_TABLE_NAME

           AND DATA_TYPE NOT IN ('CLOB', 'BLOB')order by 1 asc) loop
v_sql_temp:= v_sql_temp || '||' || R.COLUMN_NAME;

end loop;
v_sql_temp:= 'select/+ parallel(t 8) full(t)/ sum(dbms_utility.get_hash_value(' || substr(v_sql_temp,3) || ', 0, 999999999)) from '
|| V_OWNER || '.' || V_TABLE_NAME || ' t';

execute immediate v_sql_temp into v_hash;

insert into tab_hash(owner, table_name, sql_value, hash_value) values (V_OWNER, V_TABLE_NAME, v_sql_temp || ';', v_hash);
commit;
EXCEPTION
WHEN OTHERS THEN

NULL;

END;
/
----------------------------------------------------------2
--升级不同的库时,此脚本有个地方需要本地化,搜索“需要修改的地方”
conn / as sysdba

accept pswd prompt 'Input the migupd password: ' hide
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/pre
spool $HOME/$ORACLE_SID/migup/pre/pre_deal_oneday.log

--复核新库(目标库)是否使用的是ASM盘上的参数文件,正确结果为no rows selected
select name,value from v$parameter v where name='spfile' and v.VALUE not like '+%spfile%';

--按要求设置11G相关参数
alter system set job_queue_processes=0 scope=both;
alter system set aq_tm_processes =0 scope=both;
alter system set audit_trail='NONE' scope=spfile;
alter system set recyclebin='OFF' scope=spfile;
alter system set deferred_segment_creation=FALSE scope=both;
alter system set optimizer_use_sql_plan_baselines=TRUE scope=both ;
alter system set "_serial_direct_read"=never scope=both ;
alter system set "_use_adaptive_log_file_sync"=false scope=both ;

--按要求设置12c相关参数 ---需要修改的地方:升级11G的请注释此处的参数-----
alter system set audit_sys_operations=false scope=spfile;
alter system set temp_undo_enabled=true scope=spfile;
alter system set parallel_min_servers =0 scope=both;
alter system reset sec_case_sensitive_logon scope=spfile;
alter system set "_optimizer_dsdir_usage_control"=0 scope=spfile;
alter system set "_sql_plan_directive_mgmt_control="=0 scope=spfile ;
alter system set "_use_single_log_writer"=true scope=spfile ;

--复核目标库要求设置的参数是否正确设置,正确结果为no rows selected
select name, value
from v$parameter v
where (v.NAME = 'job_queue_processes' and v.VALUE <> '0')

or (v.NAME = 'aq_tm_processes' and v.VALUE <> '0')
or (v.NAME = 'audit_trail' and upper(v.VALUE) <> 'NONE')
or (v.NAME = 'recyclebin' and upper(v.VALUE) <> 'OFF')
or (v.NAME = 'deferred_segment_creation' and upper(v.VALUE) <> 'FALSE')
or (v.NAME = 'optimizer_use_sql_plan_baselines' and upper(v.VALUE) <> 'TRUE')
or (v.NAME = 'audit_sys_operations' and upper(v.VALUE) <> 'FALSE')
or (v.NAME = 'temp_undo_enabled' and upper(v.VALUE) <> 'TRUE')    
or (v.NAME = 'parallel_min_servers' and upper(v.VALUE) <> '0')    
or (v.NAME = 'sec_case_sensitive_logon' and upper(v.VALUE) <> 'TRUE')
or (v.NAME = 'fixed_date' and v.VALUE is not null);

--在新库(目标库)中migupd用户下创建要迁移的用户清单配置表
CREATE TABLE migupd.t_user_list(
username VARCHAR2(30) primary key,
flag varchar2(1),
ggflag int,
src_tbs VARCHAR2(30),
dst_tbs VARCHAR2(30)
);
comment on column MIGUPD.T_USER_LIST.FLAG
is 'y或Y表示要迁移,n或N表示不迁移,S或者s代表目标库已经初始化创建的不需要迁移的用户';
comment on column MIGUPD.T_USER_LIST.GGFLAG
is '1表示要配置在gg回滚链路里 0或空表示不配置在gg回滚链路里';

--在新库(目标库)中migupd用户下创建要迁移的表空间清单配置表
CREATE TABLE migupd.t_tbs_list(
tbsname VARCHAR2(30)
);

--需要修改的地方------个性化定制----
--初始化新库(目标库)中migupd用户下的待迁移表空间清单配置表 特别注意:升级不同的库要修改以下初始化语句
insert into migupd.t_tbs_list
select distinct tablespace_name from dba_data_files@dblink_migup b
where b.tablespace_name not in
(select tablespace_name from dba_tablespaces@dblink_migup a

where (a.tablespace_name in ('TEMP','SYSAUX','SYSTEM','USERS','其他不迁移表空间' ) or a.contents in ('TEMPORARY','UNDO'))

) ;

--初始化新库(目标库)中migupd用户下的待迁移用户清单配置表 特别注意:升级不同的库要修改以下初始化语句
--注意:待迁移表空间上对象的属主用户都在待迁移用户清单中;待迁移用户的对象都在待迁移表空间中.否则要提前安排整改和特殊处理步骤
INSERT INTO migupd.t_user_list
(username, flag ,ggflag,src_tbs, dst_tbs)
SELECT username, 'Y', 1, default_tablespace, default_tablespace

FROM dba_users@dblink_migup

WHERE username not in (select username from migupd.t_user_list)

  and username not in    ` 
     ('SYS', 'SYSTEM', 'PUBLIC', 'OUTLN', 'WMSYS', 'ORDSYS', 'MDSYS','CTXSYS', 'XDB', 'WKSYS', 
      'ANONYMOUS', 'ORDPLUGINS', 'EXFSYS', 'LBACSYS' ,'DIP', 'LBACSYS', 'ORACLE_OCM', 'TSMSYS' , 
      'SCOTT', 'JONES', 'CLARK', 'BLAKE', 'ADAMS', 'TRACESVR','DBSNMP','SYSMAN','MGMT_VIEW','DMSYS')
 and username not in ('FGLPA','PERFSTAT','SPOTLIGHT','I3_ORCL','FOGLIGHT','DBSTATS','DBAQUERY','MIGUPS',
                      'GGMGR','GGBAK', 'GGDEPLOY','RSMSMONOPR', 'EXPFULL', 'DBCONN', 'TOAD',
                      'DBQINTF'/*,'DBMON','OVSEE','FACTUSERMGR', 'DEPLOYOP','DBMONOPR' , 'DBMONOPR02'*/);

commit ;
----以下用户不需要迁移到目标库,但为使迁移过程不报错,仍然将相关的对象迁移到新库后再清理掉
INSERT INTO migupd.t_user_list
(username, flag ,ggflag,src_tbs, dst_tbs)
SELECT username, 'N', 0, default_tablespace, default_tablespace

FROM dba_users@dblink_migup

WHERE username in ('SPOTLIGHT','GGMGR')
;
commit ;
INSERT INTO migupd.t_user_list
(username, flag ,ggflag,src_tbs, dst_tbs)
SELECT username, 'S', 0, default_tablespace, default_tablespace

FROM dba_users

WHERE username not in (select username from migupd.t_user_list)

 and username not in ('MIGUPD','GGBAK','FOGLIGHT')
 and username not in 
     ('SYS', 'SYSTEM', 'PUBLIC', 'OUTLN', 'WMSYS', 'ORDSYS', 'MDSYS','CTXSYS', 'XDB', 'WKSYS', 
      'ANONYMOUS', 'ORDPLUGINS', 'EXFSYS', 'LBACSYS' ,'DIP', 'LBACSYS', 'ORACLE_OCM', 'TSMSYS' , 
      'SCOTT', 'JONES', 'CLARK', 'BLAKE', 'ADAMS', 'TRACESVR','DBSNMP','APPQOSSYS')

;
commit ;

--标志要配置在GG回滚链路里的用户
--实名用户不在GG回滚链路中配置
update migupd.t_user_list set ggflag=0 where username in( select oa_name from dba_owner.factuser_list@dblink_migup );
update migupd.t_user_list set ggflag=0 where username in( 'FACTUSERMGR','DBMONOPR','DBMONOPR_JZZC','DBQINTF','DBACOMM','DEPLOYOP','DBQUA','DBMONOPR02','dba_owner');

--整库升级或整库迁移升级时,不改变数据库架构,所以用户的表空间不变
--UPDATE migupd.t_user_list t SET t.dst_tbs = 'USERS' WHERE src_tbs IN ('xxx','xxx');
--UPDATE migupd.t_user_list t set t.dst_tbs = 'yyy' where t.username = 'yyy' and t.src_tbs ='ddd' ;
commit;

-- XTTS检查是否有业务对象都在迁移表空间上,业务对象在迁移表空间范围外的需要提前处理,正确结果为no rows selected
select distinct a.owner, a.segment_name , a.partition_name , a.segment_type , a.tablespace_name from dba_segments@dblink_migup a
where a.tablespace_name not in (select tbsname from migupd.t_tbs_list )

and a.owner in (select username  from  migupd.t_user_list where flag ='Y');    

-- XTTS检查迁移表空间上的对象属主是否都在迁移用户清单中. 否则需要提前处理迁移到其他表空间或者加入迁移用户清单,正确结果为no rows selected
select distinct a.owner, a.segment_name , a.partition_name , a.segment_type , a.tablespace_name from dba_segments@dblink_migup a
where a.tablespace_name in (select tbsname from migupd.t_tbs_list )

and a.owner not in (select username  from  migupd.t_user_list where flag ='Y');    

--在新库(目标库)中migupd用户下创建job清单表--
create table migupd.T_JOB_LIST(
JOB NUMBER not null,
ISMIG VARCHAR2(1) not null,
SRC VARCHAR2(100) not null,
LOG_USER VARCHAR2(30),
PRIV_USER VARCHAR2(30),
SCHEMA_USER VARCHAR2(30),
LAST_DATE DATE,
LAST_SEC VARCHAR2(16),
THIS_DATE DATE,
THIS_SEC VARCHAR2(16),
NEXT_DATE DATE,
NEXT_SEC VARCHAR2(16),
TOTAL_TIME NUMBER,
BROKEN VARCHAR2(1),
INTERVAL VARCHAR2(200),
FAILURES NUMBER,
WHAT VARCHAR2(4000) not null,
NLS_ENV VARCHAR2(4000) ,
INSTANCE NUMBER,
resume_nextdate varchar2(20)
);
comment on column migupd.T_JOB_LIST.ISMIG
is '是否迁移 0:是新库初始化时创建的不能迁移或删除 1:是 2:否 3:其它(未定)';
comment on column migupd.T_JOB_LIST.SRC
is 'job来源 OLD:来自旧库 NEW:来自新库(目标库)即新库初始化时创建的';
comment on column migupd.T_JOB_LIST.resume_nextdate
is '恢复Job时候,设置下次运行的时间' ;

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/pre/01_11g_deal_tablespaces.sql
-----需要修改的地方:调整目标库已有表空间的大小, 如Users表空间、临时表空间、Undo表空间
select 'alter database tempfile '||''''||df.file_name||''''||' resize 20000M ;' from dba_temp_files df where df.tablespace_name= 'TEMP' ;
select 'alter tablespace TEMP add tempfile '''||substr(file_name, 1, instr(file_name, '/') - 1)||''' size 20000M autoextend on;'
from dba_temp_files where rownum <=2 ;

select 'alter database datafile '||''''||df.file_name||''''||' resize 20000M ;' from dba_data_files df where df.autoextensible='NO' and df.tablespace_name='UNDOTBS1' ;
select 'alter tablespace UNDOTBS1 add datafile '''||substr(file_name, 1, instr(file_name, '/') - 1)||''' size 20000M autoextend on;'
from dba_data_files where rownum <=3 ;

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/pre/pre_deal_oneday.log append
@$HOME/$ORACLE_SID/migup/pre/01_11g_deal_tablespaces.sql

--在新库(目标库)中创建迁移角色
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/pre/01_11g_deal_role.sql
select 'create role ' || role || ';'
from (select role

      from dba_roles@dblink_migup  where role not in('MGMT_USER','CTXAPP','LBAC_DBA')
    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 999 long 3000
spool $HOME/$ORACLE_SID/migup/pre/pre_deal_oneday.log append
--使用migupd临时用户来创建role,避免sys角色太多出问题
conn migupd/&pswd
@$HOME/$ORACLE_SID/migup/pre/01_11g_deal_role.sql

--切换回sys用户继续执行
conn / as sysdba

--设置migupd临时用户的default role,避免登录报错ORA-28031: maximum of 148 enabled roles exceeded----
ALTER USER MIGUPD DEFAULT ROLE DBA;

--复核migupd临时用户的default role个数为1
select count(1) default_role_cnt from dba_role_privs rl where rl.grantee = 'MIGUPD' and rl.default_role = 'YES' ;

--复核新库(目标库)中角色是否创建完整,正确结果为no rows selected
select role
from dba_roles@dblink_migup where role not in ('MGMT_USER','CTXAPP','LBAC_DBA')
minus
select role from dba_roles;
commit;

--在新库(目标库)中创建迁移profile并复核
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/pre/01_11g_deal_profile.sql
select (case

      when rn = 1 then
       ('create profile ' || profile ||' limit ' || resource_name ||' ' || limit || (case
      when rn = max_rn then
       ';'
    end)) else(resource_name || ' ' || limit || (case
     when rn = max_rn then
      ';'
   end)) end)

from (select profile,

           resource_name,
           limit,
           row_number() over(partition by profile order by profile, resource_name) rn,
           count(*) over(partition by profile) max_rn
      from dba_profiles@dblink_migup
     where profile in
           (select distinct profile
              from dba_profiles@dblink_migup
            minus
            select distinct profile from dba_profiles)
     order by profile, resource_name)

order by profile, resource_name;
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/pre/pre_deal_oneday.log append
@$HOME/$ORACLE_SID/migup/pre/01_11g_deal_profile.sql
--复核新库(目标库)中profile是否创建完整,正确结果为no rows selected
select distinct profile
from dba_profiles@dblink_migup
minus
select distinct profile from dba_profiles;
commit;

--在新库(目标库)中创建要迁移的context并复核
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/pre/01_tgt_deal_context.sql
select 'create or replace context ' || c.namespace || ' using ' ||

     c.schema || '.' || c.package || ' ' || case
       when c.type like '% LOCALLY%' then
        ';'
       else
        c.type || ';'
     end Create_context
from dba_context@dblink_migup c

where c.schema in (select username

                    from migupd.t_user_list t
                   where flag = 'Y'  or flag is null)
 and c.namespace not in (select namespace from dba_context);

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/pre/pre_deal_oneday.log append
@$HOME/$ORACLE_SID/migup/pre/01_tgt_deal_context.sql
--复核新库(目标库)中context是否创建完整,正确结果为no rows selected
select namespace
from dba_context@dblink_migup
where schema in (select username

                from migupd.t_user_list t
               where flag = 'Y' or flag is null)

minus
select namespace
from dba_context
where schema in (select username

                from migupd.t_user_list t
               where flag = 'Y' or flag is null);

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/pre/pre_deal_oneday.log append
select username from dba_users;
commit;

--查询并记录keep到内存中的对象
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/pre/pre_deal_oneday.log append
select 'alter table '||owner||'.'||table_name||' storage(buffer_pool keep);' from dba_tables@dblink_migup where buffer_pool = 'KEEP';
select 'alter index '||owner||'.'||index_name||' storage(buffer_pool keep);' from dba_indexes@dblink_migup where buffer_pool = 'KEEP';
commit;

spool off

-----------------------------------------------------3
conn / as sysdba

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/pre
spool $HOME/$ORACLE_SID/migup/pre/tgt_pre_xtts_deal_oneday_check.log

--复核目标库是否使用的是ASM盘上的参数文件,正确结果为no rows selected
select name,value from v$parameter v where name='spfile' and v.VALUE not like '+%spfile%';

--复核新库(目标库)是否为非归档模式
archive log list;

--复核新库(目标库)要求设置的参数是否正确设置,正确结果为no rows selected
select name, value
from v$parameter v
where (v.NAME = 'job_queue_processes' and v.VALUE <> '0')

or (v.NAME = 'aq_tm_processes' and v.VALUE <> '0')
or (v.NAME = 'audit_trail' and upper(v.VALUE) <> 'NONE')
or (v.NAME = 'recyclebin' and upper(v.VALUE) <> 'OFF')
or (v.NAME = 'deferred_segment_creation' and upper(v.VALUE) <> 'FALSE')
or (v.NAME = 'audit_sys_operations' and upper(v.VALUE) <> 'FALSE')
or (v.NAME = 'temp_undo_enabled' and upper(v.VALUE) <> 'TRUE')    
or (v.NAME = 'parallel_min_servers' and upper(v.VALUE) <> '0')    
or (v.NAME = 'sec_case_sensitive_logon' and upper(v.VALUE) <> 'TRUE')
or (v.NAME = 'fixed_date' and v.VALUE is not null);

--复核新库(目标库)中临时使用的迁移用户migupd是否已经创建,正确结果为MIGUPD
select username from dba_users where username in ('MIGUPD');

--复核新库(目标库)中临时使用的dblink是否已经创建,正确结果为dblink_migup
col owner for a32
col db_link for a32
col username for a32
col host for a32
col object_name for a32

select owner,db_link,username,host from dba_db_links where db_link ='DBLINK_MIGUP' and owner in ('MIGUPD', 'PUBLIC' );

--复核新库(目标库)中临时使用的表migupd.t_user_list,migupd.T_JOB_LIST是否已创建,正确结果这2张表都有
select owner,table_name,tablespace_name from dba_tables
where owner='MIGUPD' and table_name in('T_USER_LIST','T_JOB_LIST');

--复核新库(目标库)中临时表t_user_list,t_tbs_list是否已经初始化
select * from MIGUPD.T_USER_LIST order by 1;
select * from MIGUPD.T_tbs_LIST order by 1;

-- XTTS检查是否有业务对象都在迁移表空间上,业务对象在迁移表空间范围外的需要提前处理,正确结果为no rows selected
select distinct a.owner, a.segment_name , a.partition_name , a.segment_type , a.tablespace_name from dba_segments@dblink_migup a
where a.tablespace_name not in (select tbsname from migupd.t_tbs_list )

and a.owner in (select username  from  migupd.t_user_list where flag ='Y');    

-- XTTS检查迁移表空间上的对象属主是否都在迁移用户清单中. 否则需要提前处理迁移到其他表空间或者加入迁移用户清单,正确结果为no rows selected
select distinct a.owner, a.segment_name , a.partition_name , a.segment_type , a.tablespace_name from dba_segments@dblink_migup a
where a.tablespace_name in (select tbsname from migupd.t_tbs_list )

and a.owner not in (select username  from  migupd.t_user_list where flag ='Y');    

--复核新库(目标库)已经存在的临时表t_user_list清单中的用户---add by liuyong
select username , to_char( u.created,'yyyy-mm-dd hh24:mi:ss') created, u.default_tablespace from dba_users u where exists (
select 1 from migupd.t_user_list t where t.username = u.username and t.flag ='Y'
);

--复核新库(目标库)中角色是否创建完整,正确结果为no rows selected
select role
from dba_roles@dblink_migup where role not in ('MGMT_USER','CTXAPP','LBAC_DBA','XDBADMIN','WKUSER','AUTHENTICATEDUSER','GGS_GGSUSER_ROLE')
minus
select role from dba_roles;
commit;

--复核新库(目标库)中profile是否创建完整,正确结果为no rows selected
select distinct profile
from dba_profiles@dblink_migup
minus
select distinct profile from dba_profiles;
commit;

--复核新库(目标库)中context是否创建完整,正确结果为no rows selected
select namespace
from dba_context@dblink_migup
where schema in (select username

                from migupd.t_user_list t
               where flag = 'Y'
                  or flag is null)

minus
select namespace
from dba_context
where schema in (select username

                from migupd.t_user_list t
               where flag = 'Y'
                  or flag is null);

spool off;
commit;

网友评论

登录后评论
0/500
评论
jan1990
+ 关注