匯入前準備

建立匯入使用者

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