导入前准备
建立导入用户
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’;