ITPub博客

首页 > Linux操作系统 > Linux操作系统 > refresh for database

refresh for database

原创 Linux操作系统 作者:lorikyo 时间:2019-07-12 19:51:06 0 删除 编辑

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博客 ” ,链接:http://blog.itpub.net/92530/viewspace-548863/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2004-07-18

  • 博文量
    211
  • 访问量
    155957