This example illustrates the renaming of data files as part of a transportable tablespace export and import operation.
1. Clean up the tablespace TTS1 in target database RAC node1 ORCL1
TRANP@ORCL1> conn / as sysdba
Connected.
SYS@ORCL1> drop tablespace tts1 including contents and datafiles;
Tablespace dropped.
SYS@ORCL1> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
UNDOTBS2 ONLINE
EXAMPLE ONLINE
7 rows selected.
SYS@ORCL1> exit
2. logon as grid who is ASM owner. Rename the file name while copying (remove 0 in filename)
orarac1poc:ORCL1:/home/oracle/ops/exp> su - grid
Password:
[grid@orarac1poc ~]$ asmcmd
ASMCMD> cp /u01/oradata/DG/tts1_01.dbf +DGDATA/orcl//tts1_1.dbf
copying /u01/oradata/DG/tts1_01.dbf -> +DGDATA/orcl//tts1_1.dbf
ASMCMD> cp /u01/oradata/DG/tts1_02.dbf +DGDATA/orcl//tts1_2.dbf
copying /u01/oradata/DG/tts1_02.dbf -> +DGDATA/orcl//tts1_2.dbf
ASMCMD> ls -l +DGDATA/orcl/
Type Redund Striped Time Sys Name
Y CONTROLFILE/
Y DATAFILE/
Y ONLINELOG/
Y PARAMETERFILE/
Y TEMPFILE/
N control01.ctl => +DGDATA/ORCL/CONTROLFILE/Current.260.803128289
N control02.ctl => +DGDATA/ORCL/CONTROLFILE/Current.261.803128289
N example01.dbf => +DGDATA/ORCL/DATAFILE/UNKNOWN.265.803128321
N redo01.log => +DGDATA/ORCL/ONLINELOG/group_1.262.803128313
N redo02.log => +DGDATA/ORCL/ONLINELOG/group_2.263.803128313
N redo03.log => +DGDATA/ORCL/ONLINELOG/group_3.267.803128495
N redo04.log => +DGDATA/ORCL/ONLINELOG/group_4.268.803128495
N spfileORCL.ora => +DGDATA/ORCL/PARAMETERFILE/spfile.269.803128497
N sysaux01.dbf => +DGDATA/ORCL/DATAFILE/SYSAUX.257.803128215
N system01.dbf => +DGDATA/ORCL/DATAFILE/SYSTEM.256.803128215
N temp01.dbf => +DGDATA/ORCL/TEMPFILE/TEMP.264.803128317
N tts1_1.dbf => +DGDATA/ASM/DATAFILE/tts1_1.dbf.270.810560049
N tts1_2.dbf => +DGDATA/ASM/DATAFILE/tts1_2.dbf.271.810560059
N undotbs01.dbf => +DGDATA/ORCL/DATAFILE/UNDOTBS1.258.803128215
N undotbs02.dbf => +DGDATA/ORCL/DATAFILE/UNDOTBS2.266.803128451
N users01.dbf => +DGDATA/ORCL/DATAFILE/USERS.259.803128215
ASMCMD> exit
[grid@orarac1poc ~]$ exit
logout
3. Import the tablespace's metadata
orarac1poc:ORCL1:/home/oracle/ops/exp> cat impdp_trans_df_rename.par
DIRECTORY=tranp_dir
DUMPFILE=tts.dmp
TRANSPORT_DATAFILES='+DGDATA/orcl/tts1_1.dbf' ,'+DGDATA/orcl/tts1_2.dbf'
orarac1poc:ORCL1:/home/oracle/ops/exp> impdp system parfile=impdp_trans_df_rename.par
Import: Release 11.2.0.2.0 - Production on Wed Mar 20 11:35:59 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, 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/******** parfile=impdp_trans_df_rename.par
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 11:36:26
4.1 verification from RAC node1
orarac1poc:ORCL1:/home/oracle/ops/exp> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Wed Mar 20 11:37:40 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SYS@ORCL1> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
UNDOTBS2 ONLINE
EXAMPLE ONLINE
TTS1 READ ONLY
8 rows selected.
SYS@ORCL1> select tablespace_name, file_name from dba_data_files;
TABLESPACE_NAME
------------------------------
FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------
USERS
+DGDATA/orcl/users01.dbf
UNDOTBS1
+DGDATA/orcl/undotbs01.dbf
SYSAUX
+DGDATA/orcl/sysaux01.dbf
SYSTEM
+DGDATA/orcl/system01.dbf
EXAMPLE
+DGDATA/orcl/example01.dbf
UNDOTBS2
+DGDATA/orcl/undotbs02.dbf
TTS1
+DGDATA/orcl/tts1_2.dbf
TTS1
+DGDATA/orcl/tts1_1.dbf
8 rows selected.
SYS@ORCL1> conn tranp/oracle123
Connected.
TRANP@ORCL1> select count(*) from t01;
COUNT(*)
----------
60708
TRANP@ORCL1> conn / as sysdba
Connected.
SYS@ORCL1> alter tablespace tts1 read write;
Tablespace altered.
SYS@ORCL1> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
UNDOTBS2 ONLINE
EXAMPLE ONLINE
TTS1 ONLINE
8 rows selected.
4.2 verify from RAC node2 too
orarac2poc:ORCL2:/home/oracle> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Wed Mar 20 11:47:35 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SYS@ORCL2> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
UNDOTBS2 ONLINE
EXAMPLE ONLINE
TTS1 ONLINE
8 rows selected.
SYS@ORCL2> select tablespace_name, file_name from dba_data_files;
TABLESPACE_NAME
------------------------------
FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------
USERS
+DGDATA/orcl/users01.dbf
UNDOTBS1
+DGDATA/orcl/undotbs01.dbf
SYSAUX
+DGDATA/orcl/sysaux01.dbf
SYSTEM
+DGDATA/orcl/system01.dbf
EXAMPLE
+DGDATA/orcl/example01.dbf
UNDOTBS2
+DGDATA/orcl/undotbs02.dbf
TTS1
+DGDATA/orcl/tts1_2.dbf
TTS1
+DGDATA/orcl/tts1_1.dbf
8 rows selected.
SYS@ORCL2> select count(*) from tranp.t01;
COUNT(*)
----------
60708
Popular Posts
-
configured "CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO DISK; " when RMAN> delete noprompt archivelog u...
-
I have one PT env using storage sync for database sync. Thought need to re-register DB after DB refresh, so unregister and register again....
-
[symptoms] orarac2poc:ORCL2:/home/oracle> . ./oms_agent_env orarac2poc:ORCL2:/home/oracle> $AGENT_HOME/bin/emctl start agent Oracl...
-
SQL> select * from dba_data_files where tablespace_name='TESTBS'; FILE_NAME ...
-
As I have CPU issue with physical standby database in previous post, I have to remove it before I have the solution. In 12c Enterprise Man... -
after rounds of recovery testing, I believer there are tons of obsolete files in FRA. There are some stubborn datafile copy can't be de...
-
This installation extended to my home hour, since both Postgresql and Zabbix are new to me. Followed few articles without much difficulty.... -
Oracle collects Active Session History (ASH) statistics (mostly wait statistics for different events) for all active sessions every second f...
-
RMAN> show all; RMAN configuration parameters for database with db_unique_name ORCL are: CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # de...
-
I hit the error when using RMAN duplicate command to create a physical standby database, with purpose of using same directory structure in...