迁移用户 BBSX,EECODB ‘AASX’,’EECODB’ #数据对象查看与比较
0.查询用户信息
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; set line 200 select username,created,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users WHERE username IN ('AASX','EECODB') order by created;
查询结果：
USERNAME CREATED DEFAULT_TABLESPACE TEMPORARY_TABLESPACE ------------ ------------------- -------------------- --------------------- AASX 2015-04-01 19:36:36 AASXTAB AASXTEMP EECODB 2016-01-06 21:52:08 EECODBDATA EECODBTEMP
1.查询对象 dba_objects
select count(*) from dba_objects where owner in ('AASX','EECODB'); select count(object_type),object_type from dba_objects where owner in ('AASX','EECODB') group by object_type; select owner,object_type,count(*) from dba_objects where owner in ('AASX','EECODB') group by owner,object_type order by 1,2;
2.查看组件
col comp_name for a60 select comp_name,version, status from dba_registry;
3.查看表空间
–目标库和源库都执行，进行比较
SELECT a.TABLESPACE_NAME "TableSpace Name", round(a.BYTES/1024/1024) "Total MB", round((a.BYTES-nvl(b.BYTES, 0)) /1024/1024) "Used MB ", nvl(round(b.BYTES/1024/1024), 0) "Free MB ", round((1-((a.BYTES-nvl(b.BYTES,0))/a.BYTES))*100,2) "Pct Free", a.AUTO FROM (SELECT TABLESPACE_NAME, sum(BYTES) BYTES, max(AUTOEXTENSIBLE) AUTO FROM sys.dba_data_files GROUP BY TABLESPACE_NAME) a, (SELECT TABLESPACE_NAME, sum(BYTES) BYTES FROM sys.dba_free_space GROUP BY TABLESPACE_NAME) b WHERE a.TABLESPACE_NAME = b.TABLESPACE_NAME (+) ORDER BY ((a.BYTES-b.BYTES)/a.BYTES) DESC;
结果：
SQL> SELECT a.TABLESPACE_NAME "TableSpace Name", round(a.BYTES/1024/1024) "Total MB", round((a.BYTES-nvl(b.BYTES, 0)) /1024/1024) "Used MB ", nvl(round(b.BYTES/1024/1024), 0) "Free MB ", round((1-((a.BYTES-nvl(b.BYTES,0))/a.BYTES))*100,2) "Pct Free", a.AUTO FROM (SELECT TABLESPACE_NAME, sum(BYTES) BYTES, max(AUTOEXTENSIBLE) AUTO FROM sys.dba_data_files GROUP BY TABLESPACE_NAME) a, (SELECT TABLESPACE_NAME, sum(BYTES) BYTES FROM sys.dba_free_space GROUP BY TABLESPACE_NAME) b WHERE a.TABLESPACE_NAME = b.TABLESPACE_NAME (+) ORDER BY ((a.BYTES-b.BYTES)/a.BYTES) DESC; TableSpace Name Total MB Used MB Free MB Pct Free AUT ------------------------------ ---------- ---------- ---------- ---------- --- UNDOTBS1 8096 7828 268 3.31 YES SYSAUX 12192 9486 2706 22.2 YES USERS 8524 4378 4146 48.63 YES SYSTEM 4096 696 3401 83.02 YES UNDOTBS2 32768 76 32692 99.77 YES EECODBDATA 2000 3 1997 99.87 YES AASXIND 10240 1 10239 99.99 NO AASXTAB 20480 1 20479 100 NO
–源库执行：查询是否有使用users等系统默认的表空间，如果有的话，导入的时候需要使用remap_tablespace
SQL> select distinct(tablespace_name) from dba_segments where owner ='AASX' or owner='EECODB'; TABLESPACE_NAME ------------------------------ EECODBDATA EECODBINDX
4.查看directory
col directory_name for a25 col DIRECTORY_PATH for a60 col owner for a15 select owner,directory_name,directory_path from dba_directories; SELECT DBMS_LOB.substr(DBMS_METADATA.get_ddl('DIRECTORY',DIR.directory_name),4000,1) FROM DBA_DIRECTORIES DIR WHERE directory_name NOT in('DATA_PUMP_DIR','ORACLE_OCM_CONFIG_DIR');
5.查看dblink
col db_link for a30 col username for a20 col host for a30 select * from dba_db_links where owner='PUBLIC'; col owner format a15; col db_link format a20; col host format a20; set heading on select * from dba_db_links;
无db_link （如存在db_link，确保准备好创建脚本
select DBMS_LOB.substr(DBMS_METADATA.GET_DDL('DB_LINK',dl.db_link,dl.owner),4000,1) FROM DBA_DB_LINKS dl;） select DBMS_LOB.substr(DBMS_METADATA.get_ddl('DB_LINK',DL.DB_LINK,'PUBLIC'),4000,1) from dba_db_links DL where owner='PUBLIC';
6.检测同义词
SELECT * FROM dba_synonyms WHERE table_owner in ('EECODB','AASX');
准备生成脚本
SELECT 'CREATE PUBLIC SYNONYM '|| synonym_name|| ' FOR '|| table_owner|| '.'|| table_name|| ';' ROM dba_synonyms WHERE table_owner in ('EECODB','AASX');
7.查看job
col schema_user for a12 col interval for a30 col what for a60 select job,what,schema_user,interval from dba_jobs order by what; select owner,job_name from dba_scheduler_jobs;
8.查看角色 RESOURCE 权限
select Privilege from ROLE_SYS_PRIVS where ROLE=upper('RESOURCE');
9.新环境创建表空间
–创建固定大小的表空间
create tablespace AASXTAB datafile '+WYDB_DATA/' size 1024M autoextend on; create tablespace AASXIND datafile '+WYDB_DATA/' size 1024M autoextend on;
10.新环境创建DIRECTORY
CREATE OR REPLACE DIRECTORY impdir AS '/u01/app/oracle';
11.查看源库tnsname.ora、listener.ora、sqlnet.ora
cd $ORACLE_HOME/network/admin cat listener.ora cat tnsnames.ora cat sqlnet.ora
12.删除schemas
–如果已经存在测试的同名schema，需要 drop 掉： –删除schema命令：
drop user AASX cascade; drop user EECODB cascade;
13.起停生产库
lsnrctl stop kill -9 `ps -ef | grep LOCAL=NO | awk '{ print $2}'`
14.锁定用户脚本
--ALTER USER AASX ACCOUNT LOCK ; --ALTER USER EECODB ACCOUNT LOCK ;
–集群
srvctl stop listener srvctl stop scan_listener srvctl stop database -d eaaxdb srvctl start database -d eaaxdb
15.导出\导入数据
查看磁盘空间，dg -g ， 磁盘空间是否足够存放dmp文件
源dir：directory=expdpdir /u01/app/oracle/dump 目标dir directory=impdir /u01/app/oracle
源主机
expdp zabbix/zabbix directory=expdpdir dumpfile=eaax_$(date +%F).dmp schemas=AASX,EECODB logfile=eaax_$(date +%F)_expdp.log cd /u01/app/oracle/dump csum eaax_$(date +%F)*.dmp
1.ftp传输
ftp -n<<_EOF open 10.0.8.8 user oracle password binary prompt off cd /u01/app/oracle mput eaax_$(date +%F).dmp close bye _EOF
2.比较md5
目标主机：
cd /u01/app/oracle
计算比较md5值
csum eaax_$(date +%F)*.dmp
3.导入数据
impdp zabbix/zabbix directory=impdir dumpfile=eaax_$(date +%F).dmp schemas=AASX,EECODB logfile=eaax_$(date +%F)_impdp.log
1、数据库表空间使用率、归档、force logging检查
df -g archive log list; select log_mode,force_logging from v$database;
2、crontab相关定时检查
ps -ef |grep nmon ls -lrt /home/oracle/backup/log
3、检查redo、cotrol file
select group#,member from v$logfile; select name from v$controlfile
16.数据对象个数对比：编译失效对象
exec utl_recomp.recomp_parallel(8);
17.统计信息收集
vi gtd.sh sqlplus / as sysdba<<_EOF exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS(); exec dbms_stats.gather_schema_stats(ownname=>'SYS',cascade=>TRUE,degree=>4,estimate_percent=>10); exec dbms_stats.gather_schema_stats(ownname=>'SYSTEM',cascade=>TRUE,degree=>4,estimate_percent=>10); exec dbms_stats.gather_schema_stats(ownname=>'AASX',cascade=>TRUE,degree=>4,estimate_percent=>10); exec dbms_stats.gather_schema_stats(ownname=>'EECODB',cascade=>TRUE,degree=>4,estimate_percent=>10); _EOF nohup sh gtd.sh &