SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 17 15:49:59 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
select d.platform_name,endian_format
from v$transportable_platform tp, v$database d
3 where tp.platform_name=d.platform_name;
PLATFORM_NAME ENDIAN_FORMAT
----------------------------------------------------------------------------------------------------- --------------
Linux IA (32-bit) Little
-bash-3.00$ ss
SQL*Plus: Release 10.2.0.5.0 - Production on Sat Oct 17 15:39:39 2015
SQL> col platform_name for a20
SQL> col endian_format for a20
SQL> select d.platform_name,endian_format
from v$transportable_platform tp, v$database d
where tp.platform_name=d.platform_name;
PLATFORM_NAME ENDIAN_FORMAT
-------------------- --------------------
Solaris Operating Sy Little
stem (x86-64)
-bash-3.00$ impdp system/oracle directory=dump2 dumpfile=samples_data.dmp full=y remap_tablespace=example:data02
source db
SYS@orcl>create tablespace udata01
2 datafile '/export/home/oracle/oradata/orcl/udata01.dbf' size 4m;
Tablespace created.
SYS@orcl>create tablespace udata02
2 datafile '/export/home/oracle/oradata/orcl/udata02.dbf' size 4m;
Tablespace created.
SYS@orcl>create table hr.emp_99 tablespace udata01 as select * from hr.employees;
Table created.
SYS@orcl>create table hr.dept_99 tablespace udata02
2 as select * from hr.departments; 2
2 as select * from hr.departments
*
ERROR at line 2:
ORA-00922: missing or invalid option
SYS@orcl>ed
Wrote file afiedt.buf
"afiedt.buf" 3 행, 82 문자
create table hr.dept_99 tablespace udata02
2 as select * from hr.departments
/
1 create table hr.dept_99 tablespace udata02
2* 2 as select * from hr.departments
SYS@orcl>create table hr.dept_99 tablespace udata02
as select * from hr.departments; 2
Table created.
SYS@orcl>alter table hr.dept_99 add primary key(department_id);
Table altered.
SYS@orcl>alter table hr.emp_99 add constraint fk_dept99 foreign key (department_id)
references hr.dept_99(department_id) 2 ;
Table altered.
SYS@orcl>select * from transport_set_violations;
select * from transport_set_violations
*
ERROR at line 1:
ORA-00942: table or view does not exist
SYS@orcl>begin
dbms_tts.transport_set_check(
ts_list=>'udata01, udata02',
incl_constraints=>TRUE);
end;
/ 2 3 4 5 6
PL/SQL procedure successfully completed.
SYS@orcl>select * from transport_set_violations
2 ;
VIOLATIONS
------------------------------------------------------------------------------------------------------------------------------------------------------
Index HR.SYS_C005927 in tablespace USERS enforces primary constriants of table HR.DEPT_99 in tablespace UDATA02
SYS@orcl>alter index hr.SYS_C0011696 rebuild tablespace udata02;
alter index hr.SYS_C0011696 rebuild tablespace udata02
*
ERROR at line 1:
ORA-01418: specified index does not exist
SYS@orcl>alter index HR.SYS_C005927 rebuild tablespace udata02;
Index altered.
SYS@orcl>alter tablespace udata01 read only;
Tablespace altered.
SYS@orcl>alter tablespace udata02 read only;
Tablespace altered.
SYS@orcl>create directory ext_dir as '/export/home/oracle/backup';
Directory created.
SYS@orcl>grant read,write on directory ext_dir to system;
-bash-3.00$ expdp system/oracle directory=ext_dir TRANSPORT_FULL_CHECK=y transport_tablespaces=udata01, udata02 dumpfile=tts1.dmp
Export: Release 10.2.0.5.0 - 64bit Production on Saturday, 17 October, 2015 17:04:21
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** directory=ext_dir TRANSPORT_FULL_CHECK=y transport_tablespaces=udata01, udata02 dumpfile=tts1.dmp
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
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/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
/export/home/oracle/backup/tts1.dmp
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 17:04:34
[oracle@lnx04 files]$ scp -r oracle@192.168.137.104:/export/home/oracle/oradata/orcl/udata0* /home/oracle/files
Password:
udata01.dbf 100% 4104KB 4.0MB/s 00:00
udata02.dbf 100% 4104KB 4.0MB/s 00:00
RMAN> convert tablespace 'UDATA01' to platform 'Linux IA (32-bit)'
2> format '/export/home/oracle/backup/%U';
Starting backup at 17-OCT-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00007 name=/export/home/oracle/oradata/orcl/udata01.dbf
converted datafile=/export/home/oracle/backup/data_D-ORCL_I-1418735677_TS-UDATA01_FNO-7_01qjutuk
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished backup at 17-OCT-15
RMAN> convert tablespace 'UDATA02' to platform 'Linux IA (32-bit)'
2> format '/export/home/oracle/backup/%U';
Starting backup at 17-OCT-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00008 name=/export/home/oracle/oradata/orcl/udata02.dbf
converted datafile=/export/home/oracle/backup/data_D-ORCL_I-1418735677_TS-UDATA02_FNO-8_02qjutvg
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished backup at 17-OCT-15
[oracle@lnx04 files]$ scp -r oracle@192.168.137.104:/export/home/oracle/backup/* /home/oracle/files
Password:
data_D-ORCL_I-1418735677_TS-UDATA01_FNO-7_01qjutuk 100% 4104KB 4.0MB/s 00:00
data_D-ORCL_I-1418735677_TS-UDATA02_FNO-8_02qjutvg 100% 4104KB 4.0MB/s 00:00
export.log 100% 1190 1.2KB/s 00:00
import.log 100% 8466 8.3KB/s 00:00
samples_data.dmp 100% 46MB 22.9MB/s 00:02
select 100% 0 0.0KB/s 00:00
tts1.dmp 100% 92KB 92.0KB/s 00:00
SYS@orcl>create directory dump1 as '/home/oracle/files';
Directory created.
SYS@orcl>grant read,write on directory dump1 to system;
Grant succeeded.
RMAN> convert datafile '/home/oracle/files/data_D-ORCL_I-1418735677_TS-UDATA01_FNO-7_01qjutuk'
2> format "/u01/app/oracle/oradata/orcl/sol_data01.dbf";
Starting conversion at target at 2015-10-17:17:24:45
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=145 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input file name=/home/oracle/files/data_D-ORCL_I-1418735677_TS-UDATA01_FNO-7_01qjutuk
converted datafile=/u01/app/oracle/oradata/orcl/sol_data01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished conversion at target at 2015-10-17:17:24:47
Starting Control File and SPFILE Autobackup at 2015-10-17:17:24:47
piece handle=/BACKUP/rman_con/c-1420439357-20151017-01 comment=NONE
Finished Control File and SPFILE Autobackup at 2015-10-17:17:24:48
RMAN> convert datafile '/home/oracle/files/data_D-ORCL_I-1418735677_TS-UDATA02_FNO-8_02qjutvg'
2> format "/u01/app/oracle/oradata/orcl/sol_data02.dbf";
Starting conversion at target at 2015-10-17:17:25:30
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting datafile conversion
input file name=/home/oracle/files/data_D-ORCL_I-1418735677_TS-UDATA02_FNO-8_02qjutvg
converted datafile=/u01/app/oracle/oradata/orcl/sol_data02.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished conversion at target at 2015-10-17:17:25:31
Starting Control File and SPFILE Autobackup at 2015-10-17:17:25:31
piece handle=/BACKUP/rman_con/c-1420439357-20151017-02 comment=NONE
Finished Control File and SPFILE Autobackup at 2015-10-17:17:25:32
unix meta data expdp
-bash-3.00$ expdp system/oracle dumpfile=tts1_meta.dmp directory=dump2 full=y include=user,role,role_grant,profile content=metadata_only
Export: Release 10.2.0.5.0 - 64bit Production on Saturday, 17 October, 2015 17:29:06
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/******** dumpfile=tts1_meta.dmp directory=dump2 full=y include=user,role,role_grant,profile content=metadata_only
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/ROLE
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
/export/home/oracle/backup/tts1_meta.dmp
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at 17:29:08
[oracle@lnx04 files]$ impdp system/oracle directory=dump1 dumpfile=tts1_meta.dmp full=y
Import: Release 11.2.0.1.0 - Production on Sat Oct 17 17:31:08 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 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=dump1 dumpfile=tts1_meta.dmp full=y
Processing object type DATABASE_EXPORT/PROFILE
ORA-31684: Object type PROFILE:"MONITORING_PROFILE" already exists
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
ORA-31684: Object type USER:"OUTLN" already exists
ORA-31684: Object type USER:"ANONYMOUS" already exists
ORA-31684: Object type USER:"OLAPSYS" already exists
ORA-31684: Object type USER:"MDDATA" already exists
ORA-31684: Object type USER:"SYSMAN" already exists
ORA-31684: Object type USER:"MGMT_VIEW" already exists
ORA-31684: Object type USER:"SCOTT" already exists
ORA-31684: Object type USER:"HR" already exists
ORA-31684: Object type USER:"SH" already exists
ORA-31684: Object type USER:"BI" already exists
Processing object type DATABASE_EXPORT/ROLE
ORA-31684: Object type ROLE:"SELECT_CATALOG_ROLE" already exists
ORA-31684: Object type ROLE:"EXECUTE_CATALOG_ROLE" already exists
ORA-31684: Object type ROLE:"DELETE_CATALOG_ROLE" already exists
ORA-31684: Object type ROLE:"RECOVERY_CATALOG_OWNER" already exists
ORA-31684: Object type ROLE:"GATHER_SYSTEM_STATISTICS" already exists
ORA-31684: Object type ROLE:"LOGSTDBY_ADMINISTRATOR" already exists
ORA-31684: Object type ROLE:"AQ_ADMINISTRATOR_ROLE" already exists
ORA-31684: Object type ROLE:"AQ_USER_ROLE" already exists
ORA-31684: Object type ROLE:"GLOBAL_AQ_USER_ROLE" already exists
ORA-31684: Object type ROLE:"SCHEDULER_ADMIN" already exists
ORA-31684: Object type ROLE:"HS_ADMIN_ROLE" already exists
ORA-31684: Object type ROLE:"OEM_ADVISOR" already exists
ORA-31684: Object type ROLE:"OEM_MONITOR" already exists
ORA-31684: Object type ROLE:"WM_ADMIN_ROLE" already exists
ORA-31684: Object type ROLE:"JAVAUSERPRIV" already exists
ORA-31684: Object type ROLE:"JAVAIDPRIV" already exists
ORA-31684: Object type ROLE:"JAVASYSPRIV" already exists
ORA-31684: Object type ROLE:"JAVADEBUGPRIV" already exists
ORA-31684: Object type ROLE:"EJBCLIENT" already exists
ORA-31684: Object type ROLE:"JAVA_ADMIN" already exists
ORA-31684: Object type ROLE:"JAVA_DEPLOY" already exists
ORA-31684: Object type ROLE:"CTXAPP" already exists
ORA-31684: Object type ROLE:"XDBADMIN" already exists
ORA-31684: Object type ROLE:"AUTHENTICATEDUSER" already exists
ORA-31684: Object type ROLE:"OLAP_DBA" already exists
ORA-31684: Object type ROLE:"OLAP_USER" already exists
ORA-31684: Object type ROLE:"MGMT_USER" already exists
ORA-31684: Object type ROLE:"CWM_USER" already exists
ORA-31684: Object type ROLE:"OLAPI_TRACE_USER" already exists
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 40 error(s) at 17:31:10
[oracle@lnx04 files]$ impdp system/oracle directory=dump1 dumpfile=tts1.dmp transport_datafiles='/u01/app/oracle/oradata/orcl/sol_data01.dbf'
Import: Release 11.2.0.1.0 - Production on Sat Oct 17 17:32:18 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 - Production
With the Partitioning, 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/******** directory=dump1 dumpfile=tts1.dmp transport_datafiles=/u01/app/oracle/oradata/orcl/sol_data01.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123: Data Pump transportable tablespace job aborted
ORA-29345: cannot plug a tablespace into a database using an incompatible character set
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" stopped due to fatal error at 17:32:19
SYS@orcl>SELECT PARAMETER, VALUE FROM V$NLS_PARAMETERS;
PARAMETER VALUE
----------------------------------------------------------------
NLS_CHARACTERSET AL32UTF8
SYS@orcl>SELECT PARAMETER, VALUE FROM V$NLS_PARAMETERS;
PARAMETER
--------------------------------------------------------------------------------------------------------------------------------
NLS_CHARACTERSET
WE8ISO8859P1
Tartget db char set AL32UTF8 is not a superset of WE8ISO8859P1.
Failed to plug in a tablespace due to incompatible
database character set"AL32UTF8" and
transportable set database character set "WE8ISO8859P1"
'스터디북' 카테고리의 다른 글
| [10/18] 정리 (0) | 2015.10.18 |
|---|---|
| [10/17] 정리 (0) | 2015.10.17 |
| [10/16] 실습 = 4 TTS (0) | 2015.10.16 |
| [10/16] 정리 (0) | 2015.10.16 |
| [10/16] 실습 - 3 resumable space allocation (0) | 2015.10.16 |