expdp升级案例

准备事项

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');

-- 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');

-- 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');

-- 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;

IF ischar = FALSE THEN

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

-- 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');

-- 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;

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 目标库在目标主机做导入

directory=impdp_0214

LOGFILE=impdp_fgbi.log

6 目标库补建公共同义词 "在目标库执行如下语句，执行生成出的结果脚本：

select 'create public synonym '||synonym_name||' for '||table_owner||'.'||table_name||';' from dba_synonyms

where table_owner in (

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)

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

select grantee,granted_role,admin_option from dba_role_privs

---check role----

select * from dba_roles@dblink_migup

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)

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-------------

--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)

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)

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)

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)

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

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)

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$%'

from dba_constraints

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)

from dba_ind_columns

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$%'

select owner,table_name,column_name,data_type,data_length,nullable,column_id from dba_tab_columns

and owner not in (XXXXX) ;

--check comments no rows selected

select owner,table_name,column_name,comments from dba_col_comments@dblink_migup

and owner not in (XXXXX) and table_name not like 'BIN$%'

select owner,table_name,column_name,comments from dba_col_comments

------check profile

select * from dba_profiles@dblink_migup

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

and owner not in (XXXXXX)

select owner,mview_name,container_name,query_len,rewrite_enabled,refresh_mode,refresh_method from dba_mviews

and owner not in (XXXXXX) ;

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')

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

and owner not in (XXXXX)

select owner,trigger_name,trigger_type,table_owner,table_name,column_name,status from dba_triggers

and owner not in (XXXXX) ;

--check table owner trigger no rows selected

where table_owner in (select username from dbmgr.s_user_list@dblink_migup)

and table_owner not in (XXXXX)

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)

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

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.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

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;

expdp升级案例

继续阅读

set define off关闭替代变量功能

报错：'mysql' 不是内部或外部命令，也不是可运行的程序或批处理文件。

Linxu常用命令技巧汇总

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艰难安装LDAP,SSL认证

《Linux命令行与Shell脚本编程大全第2版.布卢姆》pdf

MySQL的4种隔离级别？出现问题

XX系统实施过程问题总结

无组件上传图片到数据库中，最完整解决方案

【MySQL数据库】数据库索引事务1.索引2.事务

neo4j之cypher使用文档

NOSQL安全攻击

mybatis_入门程序Mybatis入门

登录plsql 报错 the account is locked --用户被锁

sqlServer根据经纬查距离

SequoiaDB巨杉数据库C++驱动概述