expdp升级案例 -CSDN博客

准备事项

1 目标库新建生产空实例
2 目标库安装OLAP
@?/olap/admin/olap.sql SYSAUX TEMP;
@?/rdbms/admin/utlrp.sql
3 目标库安装XDB
create tablespace XDB datafile '+DATA_TEST_DG' size 500m;

@?/rdbms/admin/catqm.sql xdb XDB TEMP YES

解决OLAP安装后的失效对象

4 目标库初始化实例
执行init_instance.sql
5 目标库目标库创建profile文件
1.目标库创建MONITORING_PROFILE
create profile MONITORING_PROFILE limit
failed_login_attempts unlimited;

2.目标库创建PA_PW_PROFILE
CREATE OR REPLACE FUNCTION SYS.pa_pw_verify_function
(username varchar2,
password varchar2,
old_password varchar2)
RETURN boolean IS
n boolean;
m integer;
differ integer;
isdigit boolean;
ischar boolean;
ispunct boolean;
digitarray varchar2(20);
chararray varchar2(52);

BEGIN
digitarray:= '0123456789';
chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';

-- Check if the password is same as the username
IF upper(password) = upper(username) THEN

 raise_application_error(-20001, 'Password same as user');

END IF;

-- Check for the minimum length of the password
IF length(password) < 8 THEN

  raise_application_error(-20002, 'Password length less than 8');

END IF;

-- Check if the password is too simple. A dictionary of words may be
-- maintained and a check may be made so as not to allow the words
-- that are too simple for the password.
IF NLS_LOWER(password) IN ('welcome', 'database', 'account', 'user', 'password', 'oracle', 'computer', 'abcd') THEN

  raise_application_error(-20002, 'Password too simple');

END IF;

-- Check if the password contains at least one letter, one digit and one
-- punctuation mark.
-- 1. Check for the digit
isdigit:=FALSE;
m := length(password);
FOR i IN 1..10 LOOP

  FOR j IN 1..m LOOP
     IF substr(password,j,1) = substr(digitarray,i,1) THEN
        isdigit:=TRUE;
         GOTO findchar;
     END IF;
  END LOOP;

END LOOP;
IF isdigit = FALSE THEN

  raise_application_error(-20003, 'Password should contain at least one digit and one character');

END IF;
-- 2. Check for the character
<>
ischar:=FALSE;
FOR i IN 1..length(chararray) LOOP

  FOR j IN 1..m LOOP
     IF substr(password,j,1) = substr(chararray,i,1) THEN
        ischar:=TRUE;
         GOTO endsearch;
     END IF;
  END LOOP;

END LOOP;
IF ischar = FALSE THEN

  raise_application_error(-20003, 'Password should contain at least one digit and one character');

END IF;

<>
-- Check if the password differs from the previous password by at least
-- 5 letters
IF old_password = '' THEN

  raise_application_error(-20004, 'Old password is null');

END IF;
-- Everything is fine; return TRUE ;
differ := length(old_password) - length(password);

IF abs(differ) < 5 THEN

  IF length(password) < length(old_password) THEN
     m := length(password);
  ELSE
     m := length(old_password);
  END IF;
  differ := abs(differ);
  FOR i IN 1..m LOOP
      IF substr(password,i,1) != substr(old_password,i,1) THEN
         differ := differ + 1;
      END IF;
  END LOOP;
  IF differ < 5 THEN
      raise_application_error(-20004, 'Password should differ by at least 5 characters');
  END IF;

END IF;
-- Everything is fine; return TRUE ;
RETURN(TRUE);
END;
/

CREATE PROFILE PA_PW_PROFILE LIMIT
SESSIONS_PER_USER DEFAULT
CPU_PER_SESSION DEFAULT
CPU_PER_CALL DEFAULT
CONNECT_TIME DEFAULT
IDLE_TIME 10
LOGICAL_READS_PER_SESSION DEFAULT
LOGICAL_READS_PER_CALL DEFAULT
COMPOSITE_LIMIT DEFAULT
PRIVATE_SGA DEFAULT
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LIFE_TIME 60
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX 10
PASSWORD_LOCK_TIME 999
PASSWORD_GRACE_TIME 7
PASSWORD_VERIFY_FUNCTION PA_PW_VERIFY_FUNCTION;
6 目标库目标库新建表空间
create tablespace ISTAGE datafile '+DATA_TEST_DG' size 500m autoextend on;
create tablespace STAGE1 datafile '+DATA_TEST_DG' size 500m autoextend on;
create tablespace TBS_IBISTARGET datafile '+DATA_TEST_DG' size 500m autoextend on;
create tablespace TBS_STAGE2 datafile '+DATA_TEST_DG' size 500m autoextend on;
create tablespace TBS_STAGE3 datafile '+DATA_TEST_DG' size 500m autoextend on;
create tablespace STAGE2 datafile '+DATA_TEST_DG' size 500m autoextend on;
create tablespace WORKAREA datafile '+DATA_TEST_DG' size 500m autoextend on;"
7 源库/目标库
在源库和目标库创建升级用户
CREATE USER migups IDENTIFIED BY XXXXXX
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON users;

GRANT dba TO migups;
GRANT select ON sys.user$ TO migups;
GRANT SELECT ANY DICTIONARY TO migups;
GRANT RESTRICTED SESSION TO migups;
GRANT EXECUTE ON SYS.DBMS_LOCK TO migups;
8 源库
在源生产库创建要迁移的用户清单配置表(dbmgr.s_user_cfg)
create table dbmgr.s_user_list as select username,1 as GGFLAG

FROM dba_users

where username not in (
'SYS','SYSTEM','MIGUPS','ANONYMOUS','OUTLN','EXFSYS','DBSNMP','WMSYS','APPQOSSYS','OLAPSYS','DIP','ORACLE_OCM','XS$NULL','XDB','SYSMAN'
)
9 目标库
在目标库创建迁移用的临时dblink
1:在目标库创建迁移用的临时dblink ---直接使用连接串
create public database link dblink_old connect to migups identified by XXXXX
using '(description=(address=(protocol=tcp)(host=XXXXXXX)(port=1521))(connect_data=(sid=test)))';
2:验证dblink是否通
select * from dual@dblink_old; --有结果返回表示通的否则不通需处理"
10 目标库目标库创建角色
在pc server目标库生成角色创建脚本：
select 'create role '||role||';' from dba_roles@dblink_old where role not in (select role from dba_roles);
在pc server目标库执行上述脚本进行角色创建。
11 目标库搭建目标新库同城容灾

正式迁移
序列环境操作步骤具体操作内容备注
1 源库源库停应用、监控
2 源库源库设置表空间只读
alter tablespace users read only;
alter tablespace test read only;
.
.
.
.
复核检查：待传输的表空间状态都为read only。
select tablespace_name, status from dba_tablespaces a
where (a.tablespace_name not in ('TEMP', 'SYSAUX', 'SYSTEM') and
a.contents not in ('TEMPORARY', 'UNDO'));"
3 源库源库按用户做导出
userid='/ as sysdba'
directory=expdp_0214
filesize=4000m
dumpfile=expdp%U.dmp
content=all
LOGFILE=expdp_fgbi.log
SCHEMAS=
(
XXXXXXX
XXXXX
XXX
)
parallel=4
4 源库/目标库检查导出日志是否有报错，无问题后传输导出的dump文件到目标主机
5 目标库在目标主机做导入
userid='/ as sysdba'
directory=impdp_0214
dumpfile=expdp%U.dmp
content=all
LOGFILE=impdp_fgbi.log
parallel=4

6 目标库补建公共同义词 "在目标库执行如下语句，执行生成出的结果脚本：
select 'create public synonym '||synonym_name||' for '||table_owner||'.'||table_name||';' from dba_synonyms
where table_owner in (
XXXXX
)
and owner ='PUBLIC'"
7 目标库补充sys对象授权 "在目标库执行如下语句，执行生成出的结果脚本：
select 'grant ' || privilege || ' on ' || owner || '.' || table_name ||

     ' to ' || grantee || ';'

from dba_tab_privs@dblink_old
where grantee in (select username from dbmgr.s_user_list@dblink_old

   )
 and grantee not in ('FGLPA', 'FOGLIGHT')  and (owner='SYS' or grantor='SYS');"

8 目标库 "对比失效对象，参数信息(minus)
directory，组件，比对temp，user，table，role，授权，object，index等。"

----check object -----------------

select owner ,object_name, object_type,status from dba_objects@dblink_migup
where owner in ( select username from dbmgr.s_user_list@dblink_migup) and owner not in (XXXXXX)
minus
select owner ,object_name, object_type,status from dba_objects
where owner in ( select username from dbmgr.s_user_list@dblink_migup) and owner not in (XXXXXX);

----check system priv-------------
select grantee,privilege from dba_sys_privs@dblink_migup
where grantee in ( select username from dbmgr.s_user_list@dblink_migup) and grantee not in (XXXXX)
minus
select grantee,privilege from dba_sys_privs
where grantee in ( select username from dbmgr.s_user_list@dblink_migup) and grantee not in (XXXXX);

----check role priv-------------
select grantee,granted_role,admin_option from dba_role_privs@dblink_migup
where grantee in ( select username from dbmgr.s_user_list@dblink_migup) and grantee not in (XXXXX)
minus
select grantee,granted_role,admin_option from dba_role_privs
where grantee in ( select username from dbmgr.s_user_list@dblink_migup) and grantee not in (XXXXX);

---check role----
select * from dba_roles@dblink_migup
minus
select * from dba_roles;

----check role object priv-------------
select grantee,owner,table_name,grantor,privilege,grantable from dba_tab_privs@dblink_migup
where grantee in (select rolename from dbmgr.s_role_list@dblink_migup)
minus
select grantee,owner,table_name,grantor,privilege,grantable from dba_tab_privs
where grantee in ( select rolename from dbmgr.s_role_list@dblink_migup) ;

----check user object priv-------------
select grantee,owner,table_name,grantor,privilege,grantable from dba_tab_privs@dblink_migup
where grantee in ( select username from dbmgr.s_user_list@dblink_migup) and grantee not in (XXXXX)
minus
select grantee,owner,table_name,grantor,privilege,grantable from dba_tab_privs
where grantee in ( select username from dbmgr.s_user_list@dblink_migup) and grantee not in (XXXXX);

--check audit-------------
select user_name,audit_option,success,failure
from dba_stmt_audit_opts@dblink_migup
where user_name in ( select username from dbmgr.s_user_list@dblink_migup) and user_name not in (XXXX)
minus
select user_name,audit_option,success,failure
from dba_stmt_audit_opts
where user_name in ( select username from dbmgr.s_user_list@dblink_migup) and user_name not in (XXXXX);

--check synonym no rows selected
select owner,synonym_name,table_owner,table_name
from dba_synonyms@dblink_migup
where table_owner in ( select username from dbmgr.s_user_list@dblink_migup) and table_owner not in (XXXXX)
minus
select owner,synonym_name,table_owner,table_name
from dba_synonyms
where table_owner in ( select username from dbmgr.s_user_list@dblink_migup) and table_owner not in (XXXX);

--check db links no rows selected
select owner,db_link,username,host from dba_db_links@dblink_migup
where owner in ( select username from dbmgr.s_user_list@dblink_migup) and owner not in (XXXXX)
minus
select owner,db_link,username,host from dba_db_links
where owner in ( select username from dbmgr.s_user_list@dblink_migup) and owner not in (XXXXX);

--check db context no rows selected
select namespace,schema,package,type
from dba_context@dblink_migup
where schema in ( select username from dbmgr.s_user_list@dblink_migup) and schema not in (XXXXX)
minus
select namespace,schema,package,type
from dba_context
where schema in ( select username from dbmgr.s_user_list@dblink_migup) and schema not in (XXXX);

--check object type num no rows selected
select owner ,object_type ,count(*) from dba_objects@dblink_migup
where owner in ( select username from dbmgr.s_user_list@dblink_migup) and owner not in (XXXXX) group by owner ,object_type
minus
select owner ,object_type ,count(*) from dba_objects
where owner in ( select username from dbmgr.s_user_list@dblink_migup) and owner not in (XXXXX) group by owner ,object_type;

--check table no rows selected
select owner,table_name,partitioned,iot_type,temporary
from dba_tables@dblink_migup
where owner in ( select username from dbmgr.s_user_list@dblink_migup) and owner not in (XXXXX)
minus
select owner,table_name,partitioned,iot_type,temporary
from dba_tables
where owner in ( select username from dbmgr.s_user_list@dblink_migup) and owner not in (XXXX);

--check constraint no rows selected
select owner, constraint_name, constraint_type, table_name, r_owner, status
from dba_constraints@dblink_migup
where owner in (select username from dbmgr.s_user_list@dblink_migup)
and owner not in (XXXXX)
and constraint_name not like 'SYS%' and constraint_name not like 'BIN$%'
minus
select owner, constraint_name, constraint_type, table_name, r_owner, status
from dba_constraints
where owner in (select username from dbmgr.s_user_list@dblink_migup)
and owner not in (XXXXX)
and constraint_name not like 'SYS%' and constraint_name not like 'BIN$%';

------check index---------------------
select index_owner,index_name,table_owner,table_name,column_name,column_position
from dba_ind_columns@dblink_migup
where table_owner in (select username from dbmgr.s_user_list@dblink_migup)
and table_owner not in (XXXXX)
minus
select index_owner,index_name,table_owner,table_name,column_name,column_position
from dba_ind_columns
where table_owner in (select username from dbmgr.s_user_list@dblink_migup)
and table_owner not in (XXXXX);

--check tab columns no rows selected
select owner,table_name,column_name,data_type,data_length,nullable,column_id from dba_tab_columns@dblink_migup
where owner in (select username from dbmgr.s_user_list@dblink_migup)
and owner not in ('FGLPA','FOGLIGHT','I3_ORCL','SPOTLIGHT') and table_name not like 'BIN$%'
minus
select owner,table_name,column_name,data_type,data_length,nullable,column_id from dba_tab_columns
where owner in (select username from dbmgr.s_user_list@dblink_migup)
and owner not in (XXXXX) ;

--check comments no rows selected
select owner,table_name,column_name,comments from dba_col_comments@dblink_migup
where owner in (select username from dbmgr.s_user_list@dblink_migup)
and owner not in (XXXXX) and table_name not like 'BIN$%'
minus
select owner,table_name,column_name,comments from dba_col_comments
where owner in (select username from dbmgr.s_user_list@dblink_migup)
and owner not in (XXXXX) ;

------check profile
select * from dba_profiles@dblink_migup
minus
select * from dba_profiles ;

------check mview
select owner,mview_name,container_name,query_len,rewrite_enabled,refresh_mode,refresh_method from dba_mviews@dblink_migup
where owner in (select username from dbmgr.s_user_list@dblink_migup)
and owner not in (XXXXXX)
minus
select owner,mview_name,container_name,query_len,rewrite_enabled,refresh_mode,refresh_method from dba_mviews
where owner in (select username from dbmgr.s_user_list@dblink_migup)
and owner not in (XXXXXX) ;

select owner,mview_name,container_name,query_len,rewrite_enabled,refresh_mode,refresh_method from dba_mviews@dblink_migup
minus
select owner,mview_name,container_name,query_len,rewrite_enabled,refresh_mode,refresh_method from dba_mviews ;
-----check user
select username,password,account_status,default_tablespace,temporary_tablespace,profile from dba_users@dblink_migup
where username not in ('SYS','SYSTEM','GGMGR','OUTLN','LBACSYS','CTXSYS','ANONYMOUS','EXFSYS','DMSYS','DBSNMP','WMSYS','XDB','DIP',
'ORACLE_OCM','TSMSYS')
minus
select username,password,account_status,default_tablespace,temporary_tablespace,profile from dba_users;
--------check trigger--
select owner,trigger_name,trigger_type,table_owner,table_name,column_name,status
from dba_triggers@dblink_migup
where owner in (select username from dbmgr.s_user_list@dblink_migup)
and owner not in (XXXXX)
minus
select owner,trigger_name,trigger_type,table_owner,table_name,column_name,status from dba_triggers
where owner in (select username from dbmgr.s_user_list@dblink_migup)
and owner not in (XXXXX) ;

--check table owner trigger no rows selected
select owner,trigger_name,trigger_type,table_owner,table_name,column_name,status
from dba_triggers@dblink_migup
where table_owner in (select username from dbmgr.s_user_list@dblink_migup)
and table_owner not in (XXXXX)
minus
select owner,trigger_name,trigger_type,table_owner,table_name,column_name,status from dba_triggers
where table_owner in (select username from dbmgr.s_user_list@dblink_migup)
and table_owner not in (XXXXX) ;

select owner,trigger_name,trigger_type,table_owner,table_name,column_name
from dba_triggers@dblink_migup
minus
select owner,trigger_name,trigger_type,table_owner,table_name,column_name
from dba_triggers;
----------check sequence---------------------
select ds.sequence_owner,

   ds.sequence_name,
   ds.min_value,
   ds.max_value,
   ds.increment_by,
   ds.cycle_flag,
   ds.order_flag,
   ds.cache_size,
   ds.last_number

from dba_sequences@dblink_migup ds
where sequence_owner in (select username from dbmgr.s_user_list@dblink_migup)
and sequence_owner not in (XXXXX)
minus
select ds.sequence_owner,

   ds.sequence_name,
   ds.min_value,
   ds.max_value,
   ds.increment_by,
   ds.cycle_flag,
   ds.order_flag,
   ds.cache_size,
   ds.last_number

from dba_sequences ds
where sequence_owner in (select username from dbmgr.s_user_list@dblink_migup)
and sequence_owner not in (XXXXXX);

9 目标库 "收集数据字典统计信息
调整收集策略" "--收集dictionary
exec dbms_stats.set_global_prefs(pname=>'PUBLISH', pvalue=>'TRUE');
EXEC dbms_stats.gather_dictionary_stats;

-- 修改11g 统计信息的默认策略
exec dbms_stats.set_global_prefs(pname=>'AUTOSTATS_TARGET',pvalue=>'ORACLE');
select dbms_stats.get_param('AUTOSTATS_TARGET') from dual;
-- 关闭resource_manager
alter system set resource_manager_plan = '' scope=both;
--- 将scheduler 的resource plan 指定成一个空的plan
exec dbms_scheduler.set_attribute('SYS.WEEKNIGHT_WINDOW','RESOURCE_PLAN','');
exec dbms_scheduler.set_attribute('SYS.WEEKEND_WINDOW','RESOURCE_PLAN','');
exec dbms_scheduler.set_attribute('SYS.MONDAY_WINDOW','RESOURCE_PLAN','');
exec dbms_scheduler.set_attribute('SYS.MONDAY_WINDOW','RESOURCE_PLAN','');
exec dbms_scheduler.set_attribute('SYS.TUESDAY_WINDOW','RESOURCE_PLAN','');
exec dbms_scheduler.set_attribute('SYS.WEDNESDAY_WINDOW','RESOURCE_PLAN','');
exec dbms_scheduler.set_attribute('SYS.THURSDAY_WINDOW','RESOURCE_PLAN','');
exec dbms_scheduler.set_attribute('SYS.FRIDAY_WINDOW','RESOURCE_PLAN','');
exec dbms_scheduler.set_attribute('SYS.SATURDAY_WINDOW','RESOURCE_PLAN','');
exec dbms_scheduler.set_attribute('SYS.SUNDAY_WINDOW','RESOURCE_PLAN','');

--关闭捕获sql plan到baseline
alter system set optimizer_capture_sql_plan_baselines=FALSE scope=BOTH;

--关闭直方图收集策略
exec dbms_stats.set_global_prefs(pname=>'METHOD_OPT', pvalue=>'FOR ALL COLUMNS SIZE repeat');
select dbms_stats.get_param('METHOD_OPT') from dual;"
10 目标库部署SPM包
11 目标库打开force logging "检查数据库是归档模式：
archive log list

打开force logging：
alter database force logging;
12 目标库复核：确认数据库的supplemental log已经打开
sqlplus '/as sysdba'
SQL>
select supplemental_log_data_min,supplemental_log_data_pk,supplemental_log_data_ui from v$database; --三个YES
SQL> select FORCE_LOGGING from v$database; ---YES" "打开supplemental_log:
alter database add supplemental log data (primary key ,unique index) columns;"
13 目标库复核：确认数据库的相关参数 "show parameter aq_tm_processes ; --为0
show parameter audit_trail ; --为DB
show parameter recyclebin ; --为off
show parameter remote_login_passwordfile ;--为EXCLUSIVE"
1 目标库 DNS切换，清理缓存 Windows组将DNS切换到新VIP上，并清理缓存（一定要清理客户端的缓存）
2 目标库设置job正确的next_date
恢复JOB参数值" "准备工作时就要跟开发运营确认维护完成后各job的下一次执行的next date
alter system set job_queue_processes=20 scope=both;
show parameter job_queue_processes"
4 目标库验证数据库连通性 "在前台连接数据库
select open_mode from v$database; -------read write
select archiver from v$instance; -------started"
5 目标库启动中间件和相关应用
6 目标库通知运营验证应用通知运营验证业务系统
7 目标库注册catalog "Rman注册：
步骤如下（主库）：
shell>rman target / catalog rman11g/rman11g@cat11g ---(对应版本的catalog数据库)
RMAN> register database;
RMAN> report schema;
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 90 DAYS;
RMAN>CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO 'SBT_TAPE';
RMAN>show all;
8 目标库为新数据库做一次RMAN的全备 "通知备份组为新数据库做一次RMAN full备份，并将RMAN备份恢复到恢复主机上，用来给dba创建灾备库。
并开始配置此后的定期的数据库rman全备份以及archive log定时备份。"
9 目标库对11G新生产库使用RMAN进行逻辑坏块检查 "安排对pc server生产进行一次全库坏块检查
1) $ rman target / nocatalog
2) RMAN> run {
allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;
backup validate check logical database; }
3) select * from V$DATABASE_BLOCK_CORRUPTION ;
4) If V$DATABASE_BLOCK_CORRUPTION contains rows please run this query to find the objects that contains the corrupted blocks:
SELECT e.owner,

   e.segment_type,
   e.segment_name,
   e.partition_name,
   c.file#,
   greatest(e.block_id, c.block#) corr_start_block#,
   least(e.block_id + e.blocks - 1, c.block# + c.blocks - 1) corr_end_block#,
   least(e.block_id + e.blocks - 1, c.block# + c.blocks - 1) -
   greatest(e.block_id, c.block#) + 1 blocks_corrupted,
   null description

FROM dba_extents e, v$database_block_corruption c
WHERE e.file_id = c.file#
AND e.block_id <= c.block# + c.blocks - 1
AND e.block_id + e.blocks - 1 >= c.block#
UNION
SELECT s.owner,

   s.segment_type,
   s.segment_name,
   s.partition_name,
   c.file#,
   header_block corr_start_block#,
   header_block corr_end_block#,
   1 blocks_corrupted,
   'Segment Header' description

FROM dba_segments s, v$database_block_corruption c
WHERE s.header_file = c.file#
AND s.header_block between c.block# and c.block# + c.blocks - 1
UNION
SELECT null owner,

   null segment_type,
   null segment_name,
   null partition_name,
   c.file#,
   greatest(f.block_id, c.block#) corr_start_block#,
   least(f.block_id + f.blocks - 1, c.block# + c.blocks - 1) corr_end_block#,
   least(f.block_id + f.blocks - 1, c.block# + c.blocks - 1) -
   greatest(f.block_id, c.block#) + 1 blocks_corrupted,
   'Free Block' description

FROM dba_free_space f, v$database_block_corruption c
WHERE f.file_id = c.file#
AND f.block_id <= c.block# + c.blocks - 1
AND f.block_id + f.blocks - 1 >= c.block#
order by file#, corr_start_block#;
SELECT tablespace_name, segment_type, owner, segment_name
FROM dba_extents
WHERE file_id = &fileid
and &blockid between block_id AND block_id + blocks - 1;