Oracle Applications and Fusion DBA concepts: Refresh Table space -

step 1 : Source

=========

check for the tablespaces owned by the owner from dba_segments.

select distinct TABLESPACE_NAME from dba_segments where OWNER='SLTC_QUOTE';

TABLESPACE_NAME

------------------------------

SLTC_QUOTEDAT02

SLTC_QUOTEIDX03

SLTC_QUOTEDAT01

SLTC_QUOTEIDX01

SLTC_QUOTEIDX02

SLTC_QUOTEDAT03

6 rows selected.

SQL> execute dbms_tts.transport_set_check ('SLTC_QUOTEDAT01,SLTC_QUOTEIDX01',true);

execute dbms_tts.transport_set_check ('SLTC_QUOTEDAT02,SLTC_QUOTEIDX02',true);

execute dbms_tts.transport_set_check ('SLTC_QUOTEDAT03,SLTC_QUOTEIDX03',true);

PL/SQL procedure successfully completed.

SQL> select * from transport_set_violations;

no rows selected

step 2

=======

(Alter the TS from normal to read only)

SQL> select 'alter tablespace '||tablespace_name||' read only;'

from dba_tablespaces

where tablespace_name like '%QUOTE%';

'ALTERTABLESPACE'||TABLESPACE_NAME||'READONLY;'

----------------------------------------------------------

alter tablespace SLTC_QUOTEDAT01 read only;

alter tablespace SLTC_QUOTEIDX01 read only;

alter tablespace SLTC_QUOTEDAT02 read only;

alter tablespace SLTC_QUOTEIDX02 read only;

alter tablespace SLTC_QUOTEDAT03 read only;

alter tablespace SLTC_QUOTEIDX03 read only;

SQL> alter tablespace SLTC_QUOTEDAT01 read only;

alter tablespace SLTC_QUOTEIDX01 read only;

Tablespace altered.

SQL>

Tablespace altered. (for all the above mentioned tablepaces.)

step 3

=======

export using the following syntax

exp file=exp_quote.dmp log=exp_quote.log TRANSPORT_TABLESPACE=y TABLESPACES=SLTC_QUOTEDAT01,SLTC_QUOTEIDX01,SLTC_QUOTEDAT02,SLTC_QUOTEIDX02,SLTC_QUOTEDAT03,SLTC_QUOTEIDX03

username: sys as sysdba

Passwd:

step 4

=======

In target

to be on the safe side take the list of datafiles.

Make the tablespace offline

SQL> select name from v$database;

NAME

---------

STGCPQ7

SQL> select 'alter tablespace '||tablespace_name||' offline;'

from dba_tablespaces where tablespace_name like 'SLTC_QUOTE%'; 2

'ALTERTABLESPACE'||TABLESPAC

--------------------------------------------------------------------------------

alter tablespace SLTC_QUOTEDAT01 offline;

alter tablespace SLTC_QUOTEIDX01 offline;

alter tablespace SLTC_QUOTEDAT02 offline;

alter tablespace SLTC_QUOTEIDX02 offline;

alter tablespace SLTC_QUOTEDAT03 offline;

alter tablespace SLTC_QUOTEIDX03 offline;

6 rows selected.

SQL> select distinct status from dba_tablespaces where tablespace_name like 'SLTC_QUOTE%';

STATUS

---------

OFFLINE

SQL> select 'drop tablespace '||tablespace_name||' including contents and datafiles;'

from dba_tablespaces where tablespace_name like 'SLTC_QUOTE%'; 2

'DROPTABLESPACE'||TABLESPACE

--------------------------------------------------------------------------------

drop tablespace SLTC_QUOTEDAT01 including contents and datafiles;

drop tablespace SLTC_QUOTEIDX01 including contents and datafiles;

drop tablespace SLTC_QUOTEDAT02 including contents and datafiles;

drop tablespace SLTC_QUOTEIDX02 including contents and datafiles;

drop tablespace SLTC_QUOTEDAT03 including contents and datafiles;

drop tablespace SLTC_QUOTEIDX03 including contents and datafiles;

SQL> select 'drop tablespace '||tablespace_name||' including contents and datafiles;'

from dba_tablespaces where tablespace_name like 'SLTC_QUOTE%'; 2

no rows selected

step 5 (source)

==================

SQL> select count(*) from dba_data_files where tablespace_name like '%SLTC_QUOTE%';

COUNT(*)

----------

scp the datafiles

FILE_NAME BYTES/1024/1024

------------------------------------------------------- ---------------

/brkcpq/u015/oradata/brkcpq/sltc_quotedat0101.dbf 2000

/brkcpq/u017/oradata/brkcpq/sltc_quotedat0121.dbf 2000

/brkcpq/u015/oradata/brkcpq/sltc_quotedat0102.dbf 2000

/brkcpq/u013/oradata/brkcpq/sltc_quotedat0129.dbf 1000

/brkcpq/u018/oradata/brkcpq/sltc_quotedat0126.dbf 2000

/brkcpq/u015/oradata/brkcpq/sltc_quotedat0103.dbf 2000

/brkcpq/u016/oradata/brkcpq/sltc_quotedat0114.dbf 2000

/brkcpq/u016/oradata/brkcpq/sltc_quotedat0116.dbf 2000.0625

/brkcpq/u017/oradata/brkcpq/sltc_quotedat0115.dbf 2000

/brkcpq/u016/oradata/brkcpq/sltc_quotedat0117.dbf 2000

/brkcpq/u015/oradata/brkcpq/sltc_quotedat0104.dbf 2000

/brkcpq/u012/oradata/brkcpq/sltc_quotedat0118.dbf 2000

/brkcpq/u015/oradata/brkcpq/sltc_quotedat0119.dbf 2000

/brkcpq/u013/oradata/brkcpq/sltc_quotedat0127.dbf 2000

/brkcpq/u010/oradata/brkcpq/sltc_quotedat0128.dbf 1000

/brkcpq/u012/oradata/brkcpq/sltc_quotedat0120.dbf 2000

/brkcpq/u014/oradata/brkcpq/sltc_quotedat0130.dbf 500

/brkcpq/u010/oradata/brkcpq/sltc_quotedat0131.dbf 200

/brkcpq/u017/oradata/brkcpq/sltc_quotedat0122.dbf 2000

/brkcpq/u017/oradata/brkcpq/sltc_quotedat0123.dbf 2000.0625

/brkcpq/u017/oradata/brkcpq/sltc_quotedat0124.dbf 2000

/brkcpq/u015/oradata/brkcpq/sltc_quotedat0105.dbf 2000

/brkcpq/u015/oradata/brkcpq/sltc_quotedat0106.dbf 2000

/brkcpq/u014/oradata/brkcpq/sltc_quotedat0107.dbf 2000

/brkcpq/u010/oradata/brkcpq/sltc_quotedat0132.dbf 2000

/brkcpq/u011/oradata/brkcpq/sltc_quotedat0108.dbf 2000.0625

/brkcpq/u011/oradata/brkcpq/sltc_quotedat0109.dbf 2000.0625

/brkcpq/u013/oradata/brkcpq/sltc_quotedat0110.dbf 2000.0625

/brkcpq/u016/oradata/brkcpq/sltc_quotedat0111.dbf 2000

/brkcpq/u010/oradata/brkcpq/sltc_quotedat0133.dbf 2000

/brkcpq/u016/oradata/brkcpq/sltc_quotedat0112.dbf 2000.0625

/brkcpq/u016/oradata/brkcpq/sltc_quotedat0113.dbf 2000

/brkcpq/u013/oradata/brkcpq/sltc_quotedat0125.dbf 2000

/brkcpq/u012/oradata/brkcpq/sltc_quoteidx0101.dbf 500

cp1

scp /brkcpq/u015/oradata/brkcpq/sltc_quotedat0101.dbf swbdb2s:/stgcpq3/u010/oradata/stgcpq3/.

scp /brkcpq/u017/oradata/brkcpq/sltc_quotedat0121.dbf swbdb2s:/stgcpq3/u010/oradata/stgcpq3/.

scp /brkcpq/u015/oradata/brkcpq/sltc_quotedat0102.dbf swbdb2s:/stgcpq3/u010/oradata/stgcpq3/.

scp /brkcpq/u013/oradata/brkcpq/sltc_quotedat0129.dbf swbdb2s:/stgcpq3/u010/oradata/stgcpq3/.

cp2

scp /brkcpq/u018/oradata/brkcpq/sltc_quotedat0126.dbf swbdb2s:/stgcpq3/u011/oradata/stgcpq3/.

scp /brkcpq/u015/oradata/brkcpq/sltc_quotedat0103.dbf swbdb2s:/stgcpq3/u011/oradata/stgcpq3/.

scp /brkcpq/u016/oradata/brkcpq/sltc_quotedat0114.dbf swbdb2s:/stgcpq3/u011/oradata/stgcpq3/.

cp3

scp /brkcpq/u016/oradata/brkcpq/sltc_quotedat0116.dbf swbdb2s:/stgcpq3/u012/oradata/stgcpq3/.

scp /brkcpq/u017/oradata/brkcpq/sltc_quotedat0115.dbf swbdb2s:/stgcpq3/u012/oradata/stgcpq3/.

scp /brkcpq/u016/oradata/brkcpq/sltc_quotedat0117.dbf swbdb2s:/stgcpq3/u012/oradata/stgcpq3/.

scp /brkcpq/u015/oradata/brkcpq/sltc_quotedat0104.dbf swbdb2s:/stgcpq3/u012/oradata/stgcpq3/.

cp4

scp /brkcpq/u012/oradata/brkcpq/sltc_quotedat0118.dbf swbdb2s:/stgcpq3/u013/oradata/stgcpq3/.

scp /brkcpq/u015/oradata/brkcpq/sltc_quotedat0119.dbf swbdb2s:/stgcpq3/u013/oradata/stgcpq3/.

scp /brkcpq/u013/oradata/brkcpq/sltc_quotedat0127.dbf swbdb2s:/stgcpq3/u013/oradata/stgcpq3/.

scp /brkcpq/u010/oradata/brkcpq/sltc_quotedat0128.dbf swbdb2s:/stgcpq3/u013/oradata/stgcpq3/.

cp5

scp /brkcpq/u012/oradata/brkcpq/sltc_quotedat0120.dbf swbdb2s:/stgcpq3/u014/oradata/stgcpq3/.

scp /brkcpq/u014/oradata/brkcpq/sltc_quotedat0130.dbf swbdb2s:/stgcpq3/u014/oradata/stgcpq3/.

scp /brkcpq/u010/oradata/brkcpq/sltc_quotedat0131.dbf swbdb2s:/stgcpq3/u014/oradata/stgcpq3/.

scp /brkcpq/u017/oradata/brkcpq/sltc_quotedat0122.dbf swbdb2s:/stgcpq3/u014/oradata/stgcpq3/.

scp /brkcpq/u017/oradata/brkcpq/sltc_quotedat0123.dbf swbdb2s:/stgcpq3/u014/oradata/stgcpq3/.

cp6

scp /brkcpq/u017/oradata/brkcpq/sltc_quotedat0124.dbf swbdb2s:/stgcpq3/u015/oradata/stgcpq3/.

scp /brkcpq/u015/oradata/brkcpq/sltc_quotedat0105.dbf swbdb2s:/stgcpq3/u015/oradata/stgcpq3/.

scp /brkcpq/u015/oradata/brkcpq/sltc_quotedat0106.dbf swbdb2s:/stgcpq3/u015/oradata/stgcpq3/.

scp /brkcpq/u014/oradata/brkcpq/sltc_quotedat0107.dbf swbdb2s:/stgcpq3/u015/oradata/stgcpq3/.

scp /brkcpq/u010/oradata/brkcpq/sltc_quotedat0132.dbf swbdb2s:/stgcpq3/u015/oradata/stgcpq3/.

cp7

scp /brkcpq/u011/oradata/brkcpq/sltc_quotedat0108.dbf swbdb2s:/trnswb/u016/oradata/stgcpq3/.

scp /brkcpq/u011/oradata/brkcpq/sltc_quotedat0109.dbf swbdb2s:/trnswb/u016/oradata/stgcpq3/.

scp /brkcpq/u013/oradata/brkcpq/sltc_quotedat0110.dbf swbdb2s:/trnswb/u016/oradata/stgcpq3/.

scp /brkcpq/u016/oradata/brkcpq/sltc_quotedat0111.dbf swbdb2s:/trnswb/u016/oradata/stgcpq3/.

scp /brkcpq/u010/oradata/brkcpq/sltc_quotedat0133.dbf swbdb2s:/trnswb/u016/oradata/stgcpq3/.

scp /brkcpq/u016/oradata/brkcpq/sltc_quotedat0112.dbf swbdb2s:/trnswb/u016/oradata/stgcpq3/.

scp /brkcpq/u016/oradata/brkcpq/sltc_quotedat0113.dbf swbdb2s:/trnswb/u016/oradata/stgcpq3/.

scp /brkcpq/u013/oradata/brkcpq/sltc_quotedat0125.dbf swbdb2s:/trnswb/u016/oradata/stgcpq3/.

scp /brkcpq/u012/oradata/brkcpq/sltc_quoteidx0101.dbf swbdb2s:/trnswb/u016/oradata/stgcpq3/.

step 6

=======

import using the following syntax

imp_quote.par

userid='sys/ima91ne as sysdba'

file=exp_quote.dmp

log=imp_quote.log

TRANSPORT_TABLESPACE=y

datafiles= /stgcpq3/u010/oradata/stgcpq3/sltc_quotedat0101.dbf,

/stgcpq3/u010/oradata/stgcpq3/sltc_quotedat0121.dbf,

/stgcpq3/u010/oradata/stgcpq3/sltc_quotedat0102.dbf,

/stgcpq3/u010/oradata/stgcpq3/sltc_quotedat0129.dbf,

/stgcpq3/u011/oradata/stgcpq3/sltc_quotedat0126.dbf,

/stgcpq3/u011/oradata/stgcpq3/sltc_quotedat0103.dbf,

/stgcpq3/u011/oradata/stgcpq3/sltc_quotedat0114.dbf,

/stgcpq3/u012/oradata/stgcpq3/sltc_quotedat0116.dbf,

/stgcpq3/u012/oradata/stgcpq3/sltc_quotedat0115.dbf,

/stgcpq3/u012/oradata/stgcpq3/sltc_quotedat0117.dbf,

/stgcpq3/u012/oradata/stgcpq3/sltc_quotedat0104.dbf,

/stgcpq3/u013/oradata/stgcpq3/sltc_quotedat0118.dbf,

/stgcpq3/u013/oradata/stgcpq3/sltc_quotedat0119.dbf,

/stgcpq3/u013/oradata/stgcpq3/sltc_quotedat0127.dbf,

/stgcpq3/u013/oradata/stgcpq3/sltc_quotedat0128.dbf,

/stgcpq3/u014/oradata/stgcpq3/sltc_quotedat0120.dbf,

/stgcpq3/u014/oradata/stgcpq3/sltc_quotedat0130.dbf,

/stgcpq3/u014/oradata/stgcpq3/sltc_quotedat0131.dbf,

/stgcpq3/u014/oradata/stgcpq3/sltc_quotedat0122.dbf,

/stgcpq3/u014/oradata/stgcpq3/sltc_quotedat0123.dbf,

/stgcpq3/u015/oradata/stgcpq3/sltc_quotedat0124.dbf,

/stgcpq3/u015/oradata/stgcpq3/sltc_quotedat0105.dbf,

/stgcpq3/u015/oradata/stgcpq3/sltc_quotedat0106.dbf,

/stgcpq3/u015/oradata/stgcpq3/sltc_quotedat0107.dbf,

/stgcpq3/u015/oradata/stgcpq3/sltc_quotedat0132.dbf,

/trnswb/u016/oradata/stgcpq3/sltc_quotedat0108.dbf,

/trnswb/u016/oradata/stgcpq3/sltc_quotedat0109.dbf,

/trnswb/u016/oradata/stgcpq3/sltc_quotedat0110.dbf,

/trnswb/u016/oradata/stgcpq3/sltc_quotedat0111.dbf,

/trnswb/u016/oradata/stgcpq3/sltc_quotedat0133.dbf,

/trnswb/u016/oradata/stgcpq3/sltc_quotedat0112.dbf,

/trnswb/u016/oradata/stgcpq3/sltc_quotedat0113.dbf,

/trnswb/u016/oradata/stgcpq3/sltc_quotedat0125.dbf,

/trnswb/u016/oradata/stgcpq3/sltc_quoteidx0101.dbf

imp parfile=imp_quote.par

after imp we need to do a no rows exp/imp to get procedures,packages etc

(Source)

OWNER OBJECT_TYPE STATUS COUNT(*)

------------------------------ ------------------ ------- ----------

SLTC_QUOTE LOB VALID 1

SLTC_QUOTE VIEW VALID 4

SLTC_QUOTE INDEX VALID 63

SLTC_QUOTE TABLE VALID 42

SLTC_QUOTE PACKAGE VALID 1

SLTC_QUOTE SEQUENCE VALID 1

SLTC_QUOTE JAVA CLASS VALID 1

SLTC_QUOTE JAVA SOURCE VALID 1

SLTC_QUOTE PACKAGE BODY VALID 1

(Target)

SQL> select owner ,object_type,status, count(*) from dba_objects where owner='SLTC_QUOTE'group by owner, object_type, status;

OWNER OBJECT_TYPE STATUS COUNT(*)

------------------------------ ------------------ ------- ----------

SLTC_QUOTE LOB VALID 1

SLTC_QUOTE VIEW INVALID 4

SLTC_QUOTE INDEX VALID 63

SLTC_QUOTE TABLE VALID 42

SLTC_QUOTE SEQUENCE VALID 1

(source)

exp file=exp_quote_norows.dmp log=exp_quote_norows.log owner=sltc_quote rows=n

(target)

imp file=exp_quote_norows.dmp log=imp_quote_norows.log fromuser=sltc_quote touser=sltc_quote

SQL> select owner ,object_type,status, count(*) from dba_objects where owner='SLTC_QUOTE'group by owner, object_type, status;

OWNER OBJECT_TYPE STATUS COUNT(*)

------------------------------ ------------------ ------- ----------

SLTC_QUOTE LOB VALID 1

SLTC_QUOTE VIEW VALID 4

SLTC_QUOTE INDEX VALID 63

SLTC_QUOTE TABLE VALID 42

SLTC_QUOTE PACKAGE VALID 1

SLTC_QUOTE SEQUENCE VALID 1

SLTC_QUOTE JAVA CLASS VALID 1

SLTC_QUOTE JAVA SOURCE VALID 1

SLTC_QUOTE PACKAGE BODY VALID 1

compile invalids if any.

if we want the import to be finished soon we need to give statistics=none

make the tablespace read write in target as they will be read only

check for the tbs status in both source and target

step 7

======

make all the tablesapces in source and target read write.

select 'alter tablespace '||tablespace_name||' read write;' from dba_tablespaces where tablespace_name like 'SLTC_QUOTE%';

SQL> alter tablespace SLTC_QUOTEDAT01 read write;

alter tablespace SLTC_QUOTEIDX01 read write;

Tablespace altered.

SQL>

Tablespace altered.

Oracle Applications and Fusion DBA concepts

Search This Blog

Wednesday, August 11, 2021

Refresh Table space -

No comments:

Post a Comment

Transportable tablespace refresh

Pages

My Blog List

Translate

Total Pageviews

Oracle Blogs | Technology Blogs | Economy Blogs