导入前准备

建立导入用户

CREATE USER YYBS_IMP IDENTIFIED BY YYBS_IMP DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP PROFILE DEFAULT ACCOUNT UNLOCK; GRANT RESOURCE TO YYBS_IMP; GRANT CONNECT TO YYBS_IMP; GRANT IMP_FULL_DATABASE TO YYBS_IMP; ALTER USER YYBS_IMP DEFAULT ROLE ALL; GRANT UNLIMITED TABLESPACE TO YYBS_IMP;

确认数据库

tnsping stakfdb export ORACLE_SID=stakfdb sqlplus / as sysdba select name,log_mode from v$database; —确认SID select utl_inaddr.get_host_address from dual; —确认IP地址

杀进程

select sid,serial#,username,status,osuser,machine,terminal,program from v$session; alter system kill session ‘861,21309’; 强杀进程： select spid, osuser, s.program from v$session s,v$process p where s.paddr=p.addr and s.sid=144 kill -9 spid 锁用户： select ‘alter user ‘||USERNAME||’ account lock;’ from dba_users where username like ‘U%’ and created>to_date(‘20110926’,‘yyyymmdd’) order by CREATED; DEMO:alter user UCR_CEN1 ACCOUNT LOCK;

清库

select user_id,USERNAME,ACCOUNT_STATUS,CREATED from dba_users order by CREATED; select ‘drop user ‘||USERNAME||’ cascade;’ from dba_users where username like ‘U%’ and created>to_date(‘20110926’,‘yyyymmdd’) order by CREATED; demo:drop user UOP_UIF2 cascade;

建立Directory

sqlplus system/oracle@STAKFDB CREATE OR REPLACE DIRECTORY imp930sta_dir AS ‘/app/imp930/sta’; sqlplus system/oracle@CRMKFDB CREATE OR REPLACE DIRECTORY imp930crm_dir AS ‘/app/imp930/crm’; CREATE OR REPLACE DIRECTORY imp930cen_dir AS ‘/app/imp930/center’; CREATE OR REPLACE DIRECTORY imp930oth_dir AS ‘/app/imp930/other’;

导入脚本

impdp system/oracle@csngstat831 dumpfile=Usta_full.dump logfile=Usta_full.log job_name=Usta_full full=y directory=imp930sta_dir TABLE_EXISTS_ACTION=replace parallel=1 impdp system/oracle@csngstat831 dumpfile=sUCR_STA4.dump logfile=sUCR_STA4.log job_name=sUCR_STA4 schemas=UCR_STA4 directory=imp930sta_dir TABLE_EXISTS_ACTION=replace parallel=1

导入过程监控

监控主机性能

nmon vmstat iostat

查看导入进度

select count(0) from all_objects where CREATED > sysdate-1; select * from tab where tname like ‘CRM_FULL’;

查看IMPDP进度

select * from dba_datapump_jobs; impdp system/oracle@crmkfdb attach=UCR_CRM3 help status start_jo stop_job kill_job parallel=4

导入后工作

重置密码

select ‘alter user ‘||USERNAME||’ identified by test123456;’ from dba_users where username like ‘U%’ and created>to_date(‘20110926’,‘yyyymmdd’) order by CREATED; alter user uif_act1_sta1 identified by test123456;

解锁用户：

alter user UCR_CEN1 ACCOUNT UNLOCK;

安全策略修改

select * from dba_profiles WHERE profile = ‘DEFAULT’ AND resource_type = ‘PASSWORD’; alter profile DEFAULT limit password_verify_function null; alter profile DEFAULT limit FAILED_LOGIN_ATTEMPTS UNLIMITED; alter user XXXX profile DEFAULT;

其它

重新导入同义词

table_exists_action=skip content=metadata_only impdp system/oracle@csngcrm831 dumpfile=cUCR_CRM3.dump logfile=cUCR_CRM3.log job_name=cUCR_CRM3 schemas=UCR_CRM3 directory=imp930crm_dir TABLE_EXISTS_ACTION=skip content=metadata_only parallel=1

重建同义词：

select ‘create or replace synonym UCR_CRM3.’||synonym_name||’ for UCR_CEN1.’||table_name||’;‘
from dba_synonyms where table_owner=‘UCR_CEN1’ and owner=‘UCR_CRM4’;

查看更改表空间

select tablespace_name, file_id, file_name, round(bytes/(1024*1024),0) total_space from dba_data_files where tablespace_name like ‘TBS_CRM_DUSR3’ order by tablespace_name; —查看表空间

CREATE TABLESPACE TBS_ACT_DEF DATAFILE ‘/csoradata/csngcrm/TBS_ACT_DEF.dbf’ SIZE 1024M UNIFORM SIZE 128k; —建立表空间

CREATE TABLESPACE “TBS_ACT_HIACT07” DATAFILE ‘/oradata/ngcrm/TBS_ACT_HIACT07.dbf’ SIZE 10485760 AUTOEXTEND ON NEXT 10485760 MAXSIZE 32767M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO; —建立表空间2

ALTER TABLESPACE “TBS_CRM_IUSR5” ADD DATAFILE ‘/oradata/ngbil/crm/TBS_CRM_IUSR5_2.dbf’ SIZE 10485760 AUTOEXTEND ON NEXT 10485760 MAXSIZE 32767M ; —增加表空间文件

ALTER DATABASE DATAFILE ‘/csoradata/csngcrm/TBS_ACT_DEF.dbf’ AUTOEXTEND ON NEXT 100M MAXSIZE 24576M; —设定自动扩展

CREATE TEMPORARY TABLESPACE temp_data TEMPFILE ‘/oracle/oradata/db/TEMP_DATA.dbf’ SIZE 50M —建立临时表空间

ALTER DATABASE DATAFILE ‘/oradata/ngcrm/TBS_CRM_DUSR3.dbf’ RESIZE 12288M; —调表空间 ALTER DATABASE TEMPFILE ‘/oradata/ngcrm/temp1.dbf’ RESIZE 12288M; —调临时表空间

移动表空间： alter tablespace TBS_ACT_DEF offline; alter tablespace TBS_ACT_DEF rename datafile ‘/oradata/ngbil/crm/TBS_ACT_DEF_2.dbf’ to ‘/oradata/ngcrm/TBS_ACT_DEF_2.dbf’; alter tablespace TBS_ACT_DEF online; select * from dba_tablespaces where tablespace_name=‘TBS_ACT_DEF’; select * from dba_data_files where tablespace_name=‘TBS_CRM_DUSR1’;

查锁

select * from v$locked_object select * from dba_objects where object_id=286655 select * from v$session where sid=822; alter system kill session ‘822,94’;