Transport Tablespace Testing from Msocial (Sparc) to STS VMWare (x86_64)
Submitted by trantuananh24hg on Sun, 2016-10-16 23:24
articles:
1. Platform
- Msocial: Sparc Solaris 10
- STS_VMWare: x86_64 Solaris platform
2. Utility: RMAN
3. Task list details
3.1. On Source (MSocial)
3.1.1. Create test user schema & generate data in
sys@MSOCIAL> create user msocialtest identified by msocial
2 default tablespace users
3 temporary tablespace temp
4 quota unlimited on users
5 /
User created.
sys@MSOCIAL> grant connect, resource, create session, dba to msocialtest;
Grant succeeded.
sys@MSOCIAL> grant read, write on directory data_pump_dir to msocialtest;
Grant succeeded.
sys@MSOCIAL> grant select any dictionary to msocialtest;
Grant succeeded.
sys@MSOCIAL> grant execute on sys.dbms_tts to msocialtest;
Grant succeeded.
sys@MSOCIAL> conn msocialtest/msocial
Connected.
msocialtest@MSOCIAL> CREATE TABLE test_tab (
2 id NUMBER,
3 description VARCHAR2(50),
CONSTRAINT test_tab_pk PRIMARY KEY (id)
4 5 );
Table created.
msocialtest@MSOCIAL> INSERT /*+ APPEND */ INTO test_tab (id, description)
2 SELECT level,
3 'Description for ' || level
4 FROM dual
5 CONNECT BY level <= 10000;
10000 rows created.
msocialtest@MSOCIAL> commit;
Commit complete.
msocialtest@MSOCIAL> create table bigtab as select * from all_objects where 1=2;
Table created.
msocialtest@MSOCIAL> insert into bigtab select * from all_objects where rownum<=20000;
20000 rows created.
msocialtest@MSOCIAL> commit;
Commit complete.
msocialtest@MSOCIAL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIGTAB TABLE
TEST_TAB TABLE
msocialtest@MSOCIAL> select count(*) from bigtab;
COUNT(*)
----------
20000
msocialtest@MSOCIAL> select count(*) from test_tab;
COUNT(*)
----------
10000
msocialtest@MSOCIAL> create index indx_objid on bigtab(object_id);
Index created.
msocialtest@MSOCIAL> create index indx_objname on bigtab(object_name);
Index created.
msocialtest@MSOCIAL> alter system switch logfile;
System altered.
msocialtest@MSOCIAL>
3.1.2. Verify none XML data in user schema
sys@MSOCIAL> select distinct p.tablespace_name 2 from dba_tablespaces p, dba_xml_tables x, dba_users u, all_all_tables t 3 where t.table_name=x.table_name 4 and t.tablespace_name=p.tablespace_name 5 and x.owner=u.username 6 / TABLESPACE_NAME ------------------------------ SYSAUX sys@MSOCIAL> select username, default_tablespace 2 from dba_users 3 where default_tablespace='SYSAUX'; USERNAME DEFAULT_TABLESPACE ------------------------------ ------------------------------ SYSMAN SYSAUX DBSNMP SYSAUX FLOWS_FILES SYSAUX MDSYS SYSAUX ORDDATA SYSAUX ANONYMOUS SYSAUX EXFSYS SYSAUX WMSYS SYSAUX XDB SYSAUX ORDSYS SYSAUX CTXSYS SYSAUX APPQOSSYS SYSAUX APEX_030200 SYSAUX OWBSYS_AUDIT SYSAUX ORDPLUGINS SYSAUX OLAPSYS SYSAUX SI_INFORMTN_SCHEMA SYSAUX OWBSYS SYSAUX 18 rows selected. sys@MSOCIAL>
Note: Only tablespace SYSAUX contains XML data, so, we need only MSOCIALTEST user schema but not all of above user schema in SYSAUX default tablespace
3.1.3. Check if some limitation do not allow to export
sys@MSOCIAL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('USERS',TRUE);
PL/SQL procedure successfully completed.
sys@MSOCIAL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
no rows selected
sys@MSOCIAL>
3.1.4. Put the tablespace in READ ONLY mode
sys@MSOCIAL> alter tablespace users read only; Tablespace altered. sys@MSOCIAL>
3.1.5. Export metadata
$ expdp system/msocial dumpfile=users.dmp directory=data_pump_dir transport_tablespaces=USERS logfile=export_users_tts.log Export: Release 11.2.0.1.0 - Production on Mon Apr 13 09:18:30 2015 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options sys@MSOCIAL> alter tablespace users read only; Tablespace altered. sys@MSOCIAL> Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** dumpfile=users.dmp directory=data_pump_dir transport_tablespaces=USERS logfile=export_users_tts.log Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK Processing object type TRANSPORTABLE_EXPORT/TABLE Processing object type TRANSPORTABLE_EXPORT/INDEX Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is: /msoapp/app/oracle/product/11.2.0/msocial/rdbms/log/users.dmp ****************************************************************************** Datafiles required for transportable tablespace USERS: /mSocial-DB-DATA1/oradata/msocial/users01.dbf Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 09:19:37
3.1.6. Datafile export by RMAN
RMAN> convert tablespace USERS to platform 'Solaris Operating System (x86-64)' format '/mSocial-DB-DATA3/dpdump/%U'; Starting conversion at source at 13-APR-2015 10:10:14 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=229 instance=msocial1 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=245 instance=msocial1 device type=DISK allocated channel: ORA_DISK_3 channel ORA_DISK_3: SID=249 instance=msocial1 device type=DISK channel ORA_DISK_1: starting datafile conversion input datafile file number=00004 name=/mSocial-DB-DATA1/oradata/msocial/users01.dbf converted datafile=/mSocial-DB-DATA3/dpdump/data_D-MSOCIAL_I-1761030358_TS-USERS_FNO-4_a3q495k9 channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01 Finished conversion at source at 13-APR-2015 10:10:18 Starting Control File and SPFILE Autobackup at 13-APR-2015 10:10:18 piece handle=/mSocial-DB-FLASH/flash_recovery_area/MSOCIAL/autobackup/2015_04_13/o1_mf_s_876910219_blq5jjxv_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 13-APR-2015 10:10:34 RMAN>
3.1.7. Put the tablespace USERS in mode READ WRITE again
3.2. On Target (STS_VMWare)
3.2.1. Verify the platform
sys@STS> SELECT tp.platform_id,substr(d.PLATFORM_NAME,1,30) PLATFORM_NAME, ENDIAN_FORMAT
2 FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
3 WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ------------------------------------ --------------
20 Linux x86 64-bit Little
sys@STS>
3.2.2. Create user
sys@STS> create user msocialtest identified by msocial 2 default tablespace users 3 temporary tablespace temp 4 quota unlimited on users; User created. sys@STS> grant connect, resource, create session, dba to msocialtest; Grant succeeded. sys@STS> grant select any dictionary to msocialtest; Grant succeeded. sys@STS> grant execute on sys.dbms_tts to msocialtest; Grant succeeded. sys@STS> grant execute on sys.dbms_metadata to msocialtest; Grant succeeded.
3.2.3. Mark tablespace USERS read only
sys@STS> alter tablespace users read only; Tablespace altered.
3.2.4. Copy metadata file and import metadata
sys@STS> ! ls -ll /stsapp/app/oracle/admin/sts/dpdump/ total 13234 -rw-r--r-- 1 oracle oinstall 6561792 Apr 13 2015 data_D-MSOCIAL_I-1761030358_TS-USERS_FNO-4_a3q495k9 -rw-r----- 1 oracle asmadmin 116 Mar 31 13:32 dp.log -rw-r--r-- 1 oracle oinstall 192512 Apr 13 2015 users.dmp $ rman target / Recovery Manager: Release 11.2.0.1.0 - Production on Mon Apr 13 10:29:32 2015 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database: STS (DBID=1636328243) $ impdp transport_datafiles='/stsapp/app/oracle/admin/sts/dpdump/data_D-MSOCIAL_I-1761030358_TS-USERS_FNO-4_a3q495k9' directory=data_pump_dir dumpfile=users.dmp logfile=impd_tts_users.log Import: Release 11.2.0.1.0 - Production on Mon Apr 13 10:39:06 2015 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Username: system Password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** transport_datafiles=/stsapp/app/oracle/admin/sts/dpdump/data_D-MSOCIAL_I-1761030358_TS-USERS_FNO-4_a3q495k9 directory=data_pump_dir dumpfile=users.dmp logfile=impd_tts_users.log Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK ORA-39123: Data Pump transportable tablespace job aborted ORA-29349: tablespace 'USERS' already exists Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" stopped due to fatal error at 10:39:23
Note: Because of tablespace USERS already exists in STS database, so, we rename the tablespace USERS to USERS_STS
$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Mon Apr 13 10:45:11 2015 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options sys@STS> alter tablespace users rename to users_sts; Tablespace altered.
and re-import again
$ impdp transport_datafiles='/stsapp/app/oracle/admin/sts/dpdump/data_D-MSOCIAL_I-1761030358_TS-USERS_FNO-4_a3q495k9' directory=data_pump_dir dumpfile=users.dmp logfile=impd_tts_users.log Import: Release 11.2.0.1.0 - Production on Mon Apr 13 10:47:15 2015 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Username: system Password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** transport_datafiles=/stsapp/app/oracle/admin/sts/dpdump/data_D-MSOCIAL_I-1761030358_TS-USERS_FNO-4_a3q495k9 directory=data_pump_dir dumpfile=users.dmp logfile=impd_tts_users.log Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK Processing object type TRANSPORTABLE_EXPORT/TABLE ORA-39151: Table "SCOTT"."DEPT" exists. All dependent metadata and data will be skipped due to table_exists_action of skip ORA-39151: Table "SCOTT"."EMP" exists. All dependent metadata and data will be skipped due to table_exists_action of skip ORA-39151: Table "SCOTT"."BONUS" exists. All dependent metadata and data will be skipped due to table_exists_action of skip ORA-39151: Table "SCOTT"."SALGRADE" exists. All dependent metadata and data will be skipped due to table_exists_action of skip Processing object type TRANSPORTABLE_EXPORT/INDEX Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" completed with 4 error(s) at 10:47:22
Note: Some errors occurred due to SCOTT user schema exists, by-pass this error normally
3.2.5. Backup datafile and change the file-system into +ASM
RMAN> backup as copy datafile '/stsapp/app/oracle/admin/sts/dpdump/data_D-MSOCIAL_I-1761030358_TS-USERS_FNO-4_a3q495k9' format '+DATAVOL1'; Starting backup at 13-APR-2015 10:52:48 using channel ORA_DISK_1 using channel ORA_DISK_2 using channel ORA_DISK_3 channel ORA_DISK_1: starting datafile copy input datafile file number=00008 name=/stsapp/app/oracle/admin/sts/dpdump/data_D-MSOCIAL_I-1761030358_TS-USERS_FNO-4_a3q495k9 output file name=+DATAVOL1/sts/datafile/users.256.876912769 tag=TAG20150413T105248 RECID=1 STAMP=876912768 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 13-APR-2015 10:52:49 Starting Control File and SPFILE Autobackup at 13-APR-2015 10:52:49 piece handle=/stsapp/app/oracle/product/11.2.0/sts/dbs/c-1636328243-20150413-01 comment=NONE Finished Control File and SPFILE Autobackup at 13-APR-2015 10:52:52 RMAN>
3.2.6. Verify the new tablespace & datafile in STS
sys@STS> @?/filespace
Tablespace Name Filename FILE_ID File Size Used (in bytes) Pct. Used
------------------ -------------------------------------------------- ---------- --------------- --------------- ---------
INDX +DATA/sts/datafile/indx01.dbf 5 1,073,741,824 7,340,032 0
STS +DATA/sts/datafile/sts01.dbf 7 1,073,741,824 1,048,576 0
SYSAUX +DATA/sts/datafile/sysaux.267.875798947 2 1,073,741,824 516,685,824 48
SYSTEM +DATA/sts/datafile/system.266.875798947 1 1,073,741,824 708,247,552 65
TEMP +DATA/sts/tempfile/temp.274.875799045 1 30,408,704 29,360,128 96
UNDOTBS1 +DATA/sts/datafile/undotbs1.268.875798947 3 1,073,741,824 20,643,840 1
USERS /stsapp/app/oracle/admin/sts/dpdump/data_D-MSOCIAL 8 6,553,600 6,422,528 98 <<----
_I-1761030358_TS-USERS_FNO-4_a3q495k9
USERS_STS +DATA/sts/datafile/users.269.875798947 4 1,073,741,824 10,813,440 1
USERS_STS +DATA/sts/datafile/users01.dbf 6 1,073,741,824 1,048,576 0
--------------- --------------- ---------
avg 34
sum 7,553,155,072 1,301,610,496
9 rows selected.
sys@STS>
3.2.7. Offline the new tablespace & datafile
sys@STS> alter database datafile '/stsapp/app/oracle/admin/sts/dpdump/data_D-MSOCIAL_I-1761030358_TS-USERS_FNO-4_a3q495k9' offline; Database altered. sys@STS>
3.2.8. Switch the datafile to copy & recover datafile
$ rman target / Recovery Manager: Release 11.2.0.1.0 - Production on Mon Apr 13 10:59:14 2015 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database: STS (DBID=1636328243) RMAN> switch datafile '/stsapp/app/oracle/admin/sts/dpdump/data_D-MSOCIAL_I-1761030358_TS-USERS_FNO-4_a3q495k9' to copy; using target database control file instead of recovery catalog datafile 8 switched to datafile copy "+DATAVOL1/sts/datafile/users.256.876912769" RMAN> recover datafile '+DATAVOL1/sts/datafile/users.256.876912769'; Starting recover at 13-APR-2015 11:01:14 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=439 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=222 device type=DISK allocated channel: ORA_DISK_3 channel ORA_DISK_3: SID=13 device type=DISK starting media recovery media recovery complete, elapsed time: 00:00:00 Finished recover at 13-APR-2015 11:01:15 RMAN>
3.2.9. Put the datafile online
sys@STS> @?/filespace
Tablespace Name Filename FILE_ID File Size Used (in bytes) Pct. Used
------------------ -------------------------------------------------- ---------- --------------- --------------- ---------
INDX +DATA/sts/datafile/indx01.dbf 5 1,073,741,824 7,340,032 0
STS +DATA/sts/datafile/sts01.dbf 7 1,073,741,824 1,048,576 0
SYSAUX +DATA/sts/datafile/sysaux.267.875798947 2 1,073,741,824 516,947,968 48
SYSTEM +DATA/sts/datafile/system.266.875798947 1 1,073,741,824 708,247,552 65
TEMP +DATA/sts/tempfile/temp.274.875799045 1 30,408,704 29,360,128 96
UNDOTBS1 +DATA/sts/datafile/undotbs1.268.875798947 3 1,073,741,824 8,585,216 0
USERS +DATAVOL1/sts/datafile/users.256.876912769 8
USERS_STS +DATA/sts/datafile/users.269.875798947 4 1,073,741,824 10,813,440 1
USERS_STS +DATA/sts/datafile/users01.dbf 6 1,073,741,824 1,048,576 0
--------------- --------------- ---------
avg 26
sum 7,546,601,472 1,283,391,488
9 rows selected.
sys@STS> alter datbase datafile '+DATAVOL1/sts/datafile/users.256.876912769' online;
alter datbase datafile '+DATAVOL1/sts/datafile/users.256.876912769' online
*
ERROR at line 1:
ORA-00940: invalid ALTER command
Note: Due to offline normal datafile USERS, when recover and bring up we have got the error. Solution
sys@STS> desc v$recover_file
Name Null? Type
-------------------------------------------------------------------------------- -------- ------------------------------------------------------
FILE# NUMBER
ONLINE VARCHAR2(7)
ONLINE_STATUS VARCHAR2(7)
ERROR VARCHAR2(18)
CHANGE# NUMBER
TIME DATE
sys@STS> select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ---------
8 OFFLINE OFFLINE OFFLINE NORMAL 0
sys@STS> alter tablespace users offline;
Tablespace altered.
sys@STS> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Mon Apr 13 11:04:50 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: STS (DBID=1636328243)
RMAN> recover datafile '+DATAVOL1/sts/datafile/users.256.876912769';
Starting recover at 13-APR-2015 11:05:09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=439 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=646 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=13 device type=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 13-APR-2015 11:05:10
RMAN> sql 'alter tablespace users online';
sql statement: alter tablespace users online
RMAN> exit
Recovery Manager complete.
$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon Apr 13 11:05:25 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
sys@STS> select * from v$recover_file;
no rows selected
Appendix: filespace SQL script
-- +----------------------------------------------------------------------------+ |
-- | DATABASE : Oracle |
-- | Author : Tuan Anh Tran - |
-- | FILE : dba_file_space_usage.sql |
-- | CLASS : Database Administration |
-- | PURPOSE : Reports on all data file usage. This script was designed to |
-- | work with Oracle8i or higher. It will include true TEMPORARY |
-- | tablespaces. (i.e. use of "tempfiles") |
-- +----------------------------------------------------------------------------+
SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY OFF
COLUMN tablespace FORMAT a18 HEADING 'Tablespace Name'
COLUMN filename FORMAT a50 HEADING 'Filename'
COLUMN filesize FORMAT 9999,999,999,999 HEADING 'File Size'
COLUMN used FORMAT 9999,999,999,999 HEADING 'Used (in bytes)'
COLUMN pct_used FORMAT 999 HEADING 'Pct. Used'
BREAK ON report
COMPUTE SUM OF filesize ON report
COMPUTE SUM OF used ON report
COMPUTE AVG OF pct_used ON report
spool filespace.lst replace
SELECT /*+ ordered */
d.tablespace_name tablespace
, d.file_name filename
, d.file_id file_id
, d.bytes filesize
, NVL((d.bytes - s.bytes), d.bytes) used
, TRUNC(((NVL((d.bytes - s.bytes) , d.bytes)) / d.bytes) * 100) pct_used
FROM
sys.dba_data_files d
, v$datafile v
, ( select file_id, SUM(bytes) bytes
from sys.dba_free_space
GROUP BY file_id) s
WHERE
(s.file_id (+)= d.file_id)
AND (d.file_name = v.name)
UNION
SELECT
d.tablespace_name tablespace
, d.file_name filename
, d.file_id file_id
, d.bytes filesize
, NVL(t.bytes_cached, 0) used
, TRUNC((t.bytes_cached / d.bytes) * 100) pct_used
FROM
sys.dba_temp_files d
, v$temp_extent_pool t
, v$tempfile v
WHERE
(t.file_id (+)= d.file_id)
AND (d.file_id = v.file#)
ORDER BY 1
/
spool off
Hope this help.
TaT
»
- trantuananh24hg's blog
- Log in to post comments