prepare for this project,checked the environment
1. Check object counts of SYSADM.
SQL> select owner, object_type, count (*) from dba_objects where status <>'VALID' group by owner, object_type;
OWNER OBJECT_TYPE COUNT(*)
------------------------------ ------------------ ----------
SYS VIEW 11
SYS PACKAGE BODY 5
IMON VIEW 10
IMON PACKAGE BODY 1
PATROL VIEW 4
PATROL PACKAGE BODY 2
SPOTLT VIEW 17
SPOTLT PACKAGE BODY 1
SYSADM VIEW 101
SQLREAD PROCEDURE 12
PSOFTDBA FUNCTION 1
OWNER OBJECT_TYPE COUNT(*)
------------------------------ ------------------ ----------
PSOFTDBA PACKAGE BODY 7
OPS$ORACLE PROCEDURE 2
2.Checked the schema SYSADM owner objects
select owner, object_type, count(*) from dba_objects where wner='SYSADM' group by owner, object_type order by owner, object_type
/ 2
OWNER OBJECT_TYPE COUNT(*)
------------------------------ ------------------ ----------
SYSADM DATABASE LINK 6
SYSADM FUNCTION 5
SYSADM INDEX 20325
SYSADM MATERIALIZED VIEW 3
SYSADM PROCEDURE 11
SYSADM SYNONYM 3
SYSADM TABLE 18214
SYSADM TRIGGER 18
SYSADM VIEW 8813
9 rows selected.
3. Identify the Materialized Views in the target database (t00hr03s) and extract their definitions(DDL) using DBArtisian.
4. Identify original db links owned by sysadm & extract their definitions (DDL) using DBMS_METADATA package (can't use DBArtisian to extract ddl of database
links).
select owner, object_type,object_name from dba_objects where wner='SYSADM' and object_type='DATABASE LINK' order by object_name
/
OWNER OBJECT_TYPE OBJECT_NAME
------------------------------ ------------------ ------------------------------
SYSADM DATABASE LINK GL_INTERFACE.WORLD
SYSADM DATABASE LINK T00CM85S.TMMNA.COM
SYSADM DATABASE LINK T00CM86S.TMMNA.COM
SYSADM DATABASE LINK T00DE61S.TMMNA.COM
SYSADM DATABASE LINK T00FI04S.TMMNA.COM
SYSADM DATABASE LINK T01HR50S.TMMNA.COM
6 rows selected.
CREATE DATABASE LINK t00fi04s.tmmna.com CONNECT TO fin_link
IDENTIFIED BY fin_link123 USING 'T00FI04S';
connect SYS/
CREATE PUBLIC DATABASE LINK compass.tmmna.com CONNECT TO
compassread IDENTIFIED BY readonly USING 'COMPASS';
CREATE PUBLIC DATABASE LINK sectest.tmmna.com CONNECT TO
compassread IDENTIFIED BY readonly USING
'SECTEST.TMMNA.COM';
CREATE PUBLIC DATABASE LINK security.tmmna.com CONNECT TO
compassread IDENTIFIED BY readonly USING
'SECURITY.TMMNA.COM';
CREATE PUBLIC DATABASE LINK t00cm86s.tmmna.com CONNECT TO
sysadm IDENTIFIED BY br0wn1ng USING 't00cm86s.tmmna.com';
connect SYSADM/
CREATE DATABASE LINK gl_interface CONNECT TO gl_interface
IDENTIFIED BY gl_passwd USING 't0finprd';
CREATE DATABASE LINK t01hr50s.tmmna.com CONNECT TO sysadm
IDENTIFIED BY guitar50 USING 't01hr50s';
CREATE DATABASE LINK t00cm85s.tmmna.com CONNECT TO sysadm
IDENTIFIED BY ginger USING 'T00cm85s';
CREATE DATABASE LINK t00cm86s.tmmna.com CONNECT TO sysadm
IDENTIFIED BY br0wn1ng USING 't00cm86s.tmmna.com';
CREATE DATABASE LINK t00de61s.tmmna.com CONNECT TO sysadm
IDENTIFIED BY br0wn1ng USING 't00de61s.tmmna.com';
CREATE DATABASE LINK t00fi04s.tmmna.com CONNECT TO sysadm
IDENTIFIED BY k0ng019 USING 'T00FI04S.TMMNA.COM';
CREATE MATERIALIZED VIEW SYSADM.PS_CMS_DPTDPTS
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE(INITIAL 128K)
TABLESPACE TLLARGE
LOGGING
BUILD IMMEDIATE
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 128K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0) TABLESPACE PSINDEX
REFRESH FAST
START WITH TO_DATE('03-FEB-2009 06:17 AM','DD-MON-YYYY HH12:MI PM')
NEXT sysdate + 1
ENABLE QUERY REWRITE
WITH ROWID
AS select setid,deptid,effdt,eff_status,CMS_DEPT_TYPE
TM_WF_DEPT_TYPE ,CMS_LABOR_TYPE TM_WF_LABOR_CLASS,CMS_SHIFT
TM_WF_SHIFT
from sysadm.ps_cms_dptdpts@t00fi04s.tmmna.com
/
GRANT SELECT ON SYSADM.PS_CMS_DPTDPTS TO PSOFTREAD
/
CREATE MATERIALIZED VIEW SYSADM.PS_CMS_DPTSTRU
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE(INITIAL 128K)
TABLESPACE TLLARGE
LOGGING
BUILD IMMEDIATE
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 200K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0) TABLESPACE PSINDEX
REFRESH FAST
START WITH TO_DATE('03-FEB-2009 06:17 AM','DD-MON-YYYY HH12:MI PM')
NEXT sysdate + 1
WITH ROWID
AS select setid,tree_name,LEVELNUM ,TREE_NODE , DEPTID,
EFFDT ,EFF_STATUS
from ps_cms_dptstru@t00fi04s.tmmna.com
/
GRANT SELECT ON SYSADM.PS_CMS_DPTSTRU TO PSOFTREAD
/
CREATE MATERIALIZED VIEW SYSADM.PS_TM_PEFF_CONTRIB
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE(INITIAL 128K)
TABLESPACE TLLARGE
LOGGING
BUILD IMMEDIATE
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 128K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0) TABLESPACE TLLARGE
REFRESH FAST
START WITH TO_DATE('03-FEB-2009 06:17 AM','DD-MON-YYYY HH12:MI PM')
NEXT sysdate + 1
WITH ROWID
AS select BUSINESS_UNIT ,REP_COST_CENTER_CD TM_WF_REP_COST_CTR,
TM_CONTRIB_TYPE TM_WF_CONTRIB_TYPE,
TM_EFF_START_DATE START_DATE , TM_EFF_STOP_DATE
END_DATE, TM_SHOP_CODE TM_WF_SHOP_CODE,TM_CONTRIBUTION
TM_WF_CONTRIBUTION
from sysadm.ps_tm_peff_contrib@t00de61s.tmmna.com
/
GRANT SELECT ON SYSADM.PS_TM_PEFF_CONTRIB TO PSOFTREAD
/
begin to refresh
5. Drop SYSADM objects,generated the script. for drop table and objects .
set heading off
set feedback off
set echo off
set pagesize 0
set linesize 132
set space 0
set newpage 0
set verify off
spool drop_sysadm_obj_new.sql
select 'DROP '||OBJECT_TYPE||' '||OWNER||'.'||OBJECT_NAME||';'from dba_objects
where owner in ('SYSADM')
and object_type in ('VIEW','SEQUENCE','PACKAGE','PROCEDURE','FUNCTION', 'MATERIALIZED VIEW')
order by owner,object_type,object_name
/
spool off
spool drop_sysadm_tab_new.sql
select 'drop table ' ||owner||'.'||table_name||';' from dba_tables where wner='SYSADM' and tablespace_name not in ('TMARCHIVE_TBL','TMARCHIVE_IDX')
spool off
6. Compare tablespace used space from production with tablespace free space in target database. Maike sure free space in target database MUST be greater
than used space from production. The information of tablesapce used space in production will be provided by the client in the Excel spreadsheet.
col ratio for a10
select f.aname ,t.total-f.free used,f.free,t.total,round((t.total-f.free)/t.total,4)*100||'%' ratio from (select a.TABLESPACE_NAME aname,sum
(a.BYTES)/1024/1024 free from dba_free_space a group by a.tablespace_name) f
,(select b.tablespace_name bname,sum(b.BYTES)/1024/1024 total from dba_data_files b group by b.tablespace_name) t where f.aname=t.bname order by TOTAL desc ;
ANAME USED FREE TOTAL RATIO
------------------------------ ---------- ---------- ---------- ----------
PSINDEX 3.5625 62000.4375 62004 .01%
TMARCHIVE_TBL 42015.625 2080.375 44096 95.28%
TMARCHIVE_IDX 36901 867 37768 97.7%
PSINDEX2 .875 28931.125 28932 0%
PS_TL_PAYABLE_TIME .75 24815.25 24816 0%
PYLARGE .8125 20739.1875 20740 0%
PS_TL_PAYABLE_TIME_IDX .625 20407.375 20408 0%
TLLARGE .4375 18547.5625 18548 0%
PS_TL_RPTD_ELPTIME .5 16667.5 16668 0%
UNDOTBS 1431.375 13592.625 15024 9.53%
GPAPP .5625 13847.4375 13848 0%
ANAME USED FREE TOTAL RATIO
------------------------------ ---------- ---------- ---------- ----------
PSIMAGE .4375 11047.5625 11048 0%
PS_TL_RPTD_ELPTIME_IDX .3125 8191.6875 8192 0%
HRAPP .3125 6345.6875 6346 0%
TLWORK 1.5 4094.5 4096 .04%
TM_HSDATA1 .25 3811.75 3812 .01%
TLAPP .3125 3575.6875 3576 .01%
PTTBL .3125 3273.6875 3274 .01%
HRLARGE .125 2559.875 2560 0%
PS_TM_PAY_TIME_HST .0625 2047.9375 2048 0%
PS_TM_RPTD_ELP_HST .0625 2047.9375 2048 0%
SYSTEM 787.8125 1260.1875 2048 38.47%
ANAME USED FREE TOTAL RATIO
------------------------------ ---------- ---------- ---------- ----------
STATSPACK 953.875 796.125 1750 54.51%
PERFMON 1005.5 594.5 1600 62.84%
TM_SQLREAD_TBL 729.3125 570.6875 1300 56.1%
PTAMSG .1875 1199.8125 1200 .02%
PS_TM_RPTD_ELP_HST_IDX .0625 1023.9375 1024 .01%
TM_HSINDEX1 .0625 1023.9375 1024 .01%
TOOLS 110 490 600 18.33%
PS_TM_PAY_TIME_HST_IDX .0625 511.9375 512 .01%
TM_HSIMAGE1 .0625 511.9375 512 .01%
BNAPP .0625 499.9375 500 .01%
TMSUGG .0625 499.9375 500 .01%
ANAME USED FREE TOTAL RATIO
------------------------------ ---------- ---------- ---------- ----------
PAAPP .0625 299.9375 300 .02%
PYAPP .125 249.875 250 .05%
PTPRC .125 227.875 228 .05%
HPAPP .125 199.875 200 .06%
PY0LRG .0625 199.9375 200 .03%
PTRPTS .0625 169.9375 170 .04%
FGAPP .0625 149.9375 150 .04%
PTTLRG .0625 149.9375 150 .04%
GIAPP .0625 99.9375 100 .06%
HTAPP .3125 99.6875 100 .31%
DIAPP .0625 74.9375 75 .08%
ANAME USED FREE TOTAL RATIO
------------------------------ ---------- ---------- ---------- ----------
PTAPP .125 57.875 58 .22%
BDAPP .0625 49.9375 50 .13%
FSAPP .0625 49.9375 50 .13%
STLARGE .0625 49.9375 50 .13%
STAPP .0625 49.9375 50 .13%
PTCMSTAR .0625 49.9375 50 .13%
PILARGE .0625 49.9375 50 .13%
PCLARGE .0625 49.9375 50 .13%
HRWORK .0625 49.9375 50 .13%
HRAPPS .0625 49.9375 50 .13%
EOCMAPP .0625 38.9375 39 .16%
ANAME USED FREE TOTAL RATIO
------------------------------ ---------- ---------- ---------- ----------
HRAPP6 .0625 38.9375 39 .16%
PTAPPE .0625 34.9375 35 .18%
HRAPP2 .0625 29.9375 30 .21%
PCAPP .0625 24.9375 25 .25%
CUAUDIT .0625 23.9375 24 .26%
CULARGE .0625 23.9375 24 .26%
EOAPP .0625 19.9375 20 .31%
PYWORK .0625 19.9375 20 .31%
STWORK .0625 19.9375 20 .31%
COAPP .0625 14.9375 15 .42%
ERAPP .0625 14.9375 15 .42%
ANAME USED FREE TOTAL RATIO
------------------------------ ---------- ---------- ---------- ----------
AMAPP .0625 9.9375 10 .63%
PVAPP .0625 9.9375 10 .63%
PTPRJWK .0625 9.9375 10 .63%
PTAUDIT .0625 9.9375 10 .63%
POAPP .0625 9.9375 10 .63%
PIWORK .0625 9.9375 10 .63%
PIAPP .0625 9.9375 10 .63%
PALARGE .0625 9.9375 10 .63%
INAPP .0625 9.9375 10 .63%
HRAPP5 .0625 9.9375 10 .63%
HRAPP4 .0625 9.9375 10 .63%
ANAME USED FREE TOTAL RATIO
------------------------------ ---------- ---------- ---------- ----------
HRAPP3 .0625 9.9375 10 .63%
HRAPP1 .0625 9.9375 10 .63%
WAAPP .0625 9.9375 10 .63%
TLALL .0625 9.9375 10 .63%
EOLARGE .0625 9.9375 10 .63%
BNLARGE .0625 9.9375 10 .63%
CULARG1 .0625 9.9375 10 .63%
EODITBL .0625 9.9375 10 .63%
CULARG2 .0625 9.9375 10 .63%
CULARG3 .0625 9.9375 10 .63%
EOEWAPP .0625 7.9375 8 .78%
ANAME USED FREE TOTAL RATIO
------------------------------ ---------- ---------- ---------- ----------
PSIMGR .0625 7.9375 8 .78%
EPAPP .0625 6.9375 7 .89%
EOECAPP .0625 5.9375 6 1.04%
PSWORK .0625 5.9375 6 1.04%
PTWORK .0625 5.9375 6 1.04%
PTLOCK .0625 5.9375 6 1.04%
EOEIAPP .0625 4.9375 5 1.25%
EOTPAPP .0625 4.9375 5 1.25%
PTTREE .0625 4.9375 5 1.25%
EOECLRG .0625 3.9375 4 1.56%
EOTPLRG .0625 2.9375 3 2.08%
ANAME USED FREE TOTAL RATIO
------------------------------ ---------- ---------- ---------- ----------
GPDEAPP .0625 2.9375 3 2.08%
EPLARGE .0625 2.9375 3 2.08%
101 rows selected.
7.add datafile for has not enough tablespace_sapce.
8. Modify the import script. with the new export dumpfile location and name (provided by the client).
9. Kick off the import by running the following script. in background.
cd /backup/psbackup01/t01hr50v/exp/refresh
cat imp_t00hr03s.sh
#!/bin/ksh
# import into t00hr03s
PATH=$PATH:/etc:/usr/bin:/usr/local/bin:/usr/ucb:/usr/etc:/common/bin:/var/opt/b
in:/usr/sbin:/usr/contib/bin:/opt/bin:/usr/ccs/bin:/usr/bin/X11:/usr/openwin/bin
:/apps/app/oracle/product/9.2.0.5/bin
ORACLE_SID=t00hr03s;export ORACLE_SID
ORAENV_ASK="NO";export ORAENV_ASK
. oraenv
mknod /backup/psbackup01/t00hr03s/exp/refresh/imp_t00hr03s.dmp p
if [ $? -ne 0 ]
then
errmsg="$0:ERROR===> "\
" $ORACLE_SID mknod /backup/psbackup01/t00hr03s/exp/refresh/imp_t00
hr03s.dmp"
echo $errmsg
exit 4
else
echo "mknod successful"
fi
#
uncompress < /backup/psbackup01/t00hr03s/exp/refresh/exp_full_t01hr50p_09020802
30.dmp.Z \
> /backup/psbackup01/t00hr03s/exp/refresh/imp_t00hr03s.dmp &
if [ $? -ne 0 ]
then
#because of mknod/compress commands, you must write
# to the .dmp file before exiting; otherwise the process
# will hang
echo > /backup/psbackup01/t00hr03s/exp/refresh/exp_dummy_t00hr03s.dmp
echo "truncated the dmp file" $?
rm /backup/psbackup01/t00hr03s/exp/refresh/imp_t00hr03s.dmp
echo "removed the dmp file " $?
errmsg="$0:ERROR===> "\
"$ORACLE_SID compress /backup/psbackup01/t00hr03s/exp/refresh/imp_t00hr
03s.dmp"
echo $errmsg
exit 4
else
echo "compress of pipe successful"
fi
#
echo 'executing the imp oracle utility'
#
sleep 15
imp parfile=/backup/psbackup01/t00hr03s/exp/refresh/imp_t00hr03s.par
rm /backup/psbackup01/t00hr03s/exp/refresh/imp_t00hr03s.dmp
cat imp_t00hr03s.par
userid=system/harp002
buffer=419430400
file=/backup/psbackup01/t00hr03s/exp/refresh/imp_t00hr03s.dmp
ignore=y
destroy=n
log=imp_t00hr03s_HRPP.log
commit=n
statistics=none
fromuser=sysadm
touser=sysadm
tables=(PS_UPG_CONV_FG2,
PS_UPG_ER_STATUS,
PS_UPG_83_JP_AET)
nohup imp_t00hr03s.sh &
10. Monitor the import process by checking import log, alert log and temp tablespace usage .
There is a problem ,client ask us exclude the archive tables in ablespaces(TMARCHIVE_TBL & TMARCHIVE_IDX)
But when import will rewrite this tablespace ,so We must alter tablesapce (TMARCHIVE_TBL & TMARCHIVE_IDX) read only
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SYSADM.PS_TM_DED_BLN_HST) violated
Column : INIT2005
Column : 54
Column : 9786
Column : CY
Column : 1998
Column : 1
Column : 1
Column : 999
Column : 00
Column :
Column : CCCAFT
Column : A
Column : TIN
Column : 30.18
Column : 30.18
Column : 30.18
sql>alter tablespace TMARCHIVE_TBL read only ;
sql>alter tablespace TMARCHIVE_IDX read only;
11 .when import completed checked found has 221 table is not been import .
Checked the log found :
IMP-00003: ORACLE error 1658 encountered
ORA-01658: unable to create INITIAL extent for segment in tablespace HRAPP
This tablespace has 6.48G freespace and this tablespace has used 6.46G , provided by the client in the Excel spreadsheet
but this Excel is 1 weeks ago client provided for us ,I assume there are some change for this tablespace.
and now the space user more than 6.49G.
Checked the log and found the lost table ,increase tablespace and import lack of table again .
alter tablespace HRAPP add datafile '/data/psdata13/oradata/t00hr03s/hrapp06.dbf'size 1200m autoextend off;
When this import completed ,all object has been import .
select owner, object_type, count(*) from dba_objects where wner='SYSADM' group by owner, object_type order by owner, object_type;
OWNER OBJECT_TYPE COUNT(*)
------------------------------ ------------------ ----------
SYSADM DATABASE LINK 11
SYSADM FUNCTION 5
SYSADM INDEX 20327
SYSADM PROCEDURE 11
SYSADM SYNONYM 3
SYSADM TABLE 18213
SYSADM TRIGGER 18
SYSADM VIEW 8817
8 rows selected.
sql>alter tablespace TMARCHIVE_TBL read write ;
sql>alter tablespace TMARCHIVE_IDX read write;
12Recompile invalid objects.
SQL> select owner, object_type, count (*) from dba_objects where status <>'VALID' group by owner, object_type;
OWNER OBJECT_TYPE COUNT(*)
------------------------------ ------------------ ----------
SYS VIEW 11
SYS PACKAGE BODY 2
SYSADM VIEW 86
SYSADM FUNCTION 1
SYSADM PROCEDURE 1
PSOFTDBA FUNCTION 1
OPS$ORACLE PROCEDURE 2
sql>@/apps/app/oracle/product/9.2.0.5/rdbms/admin/utlrp.sql
SQL> select owner, object_type, count (*) from dba_objects where status <>'VALID' group by owner, object_type;
OWNER OBJECT_TYPE COUNT(*)
------------------------------ ------------------ ----------
SYSADM VIEW 85
PSOFTDBA FUNCTION 1
OPS$ORACLE PROCEDURE 2
13. Run statistics with 5% sample and cascade=true option (it will take around 1 hour and 45 minutes).
exec dbms_stats.gather_schema_stats(OWNNAME =>'SYSADM', estimate_percent =>5, CASCADE =>true);
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/92530/viewspace-548863/ ，如需转载，请注明出处，否则将追究法律责任。
