This content has been marked as final. Show 4 replies
1. You can use Import/Export or Transportable Tablespace or RMAN with CONVERT DATABASE command. Check below notes.
Note 733205.1 - Migration of an Oracle Database Across OS Platforms
Note 1401921.1 - Cross-Platform Database Migration (across same endian) using RMAN Transportable Database
Note 414878.1 - Cross-Platform Migration on Destination Host Using Rman Convert Database
2. Endian Format, Export/Import Utility Version, TTS limitations
3. Yes. Linux & Windows have same endian format. It would be certainly the faster approch but will have limitations.
Note 243304.1 10g: Transportable Tablespaces Across Different Platforms
4. Regular import/export options will be enough. No specific options/parameters are required. But remember below.
SQL> select * from V$TRANSPORTABLE_PLATFORM order by 1; PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT ----------- ---------------------------------------- -------------- 1 Solaris[tm] OE (32-bit) Big 2 Solaris[tm] OE (64-bit) Big 3 HP-UX (64-bit) Big 4 HP-UX IA (64-bit) Big 5 HP Tru64 UNIX Little 6 AIX-Based Systems (64-bit) Big 7 Microsoft Windows IA (32-bit) Little 8 Microsoft Windows IA (64-bit) Little 9 IBM zSeries Based Linux Big 10 Linux IA (32-bit) Little 11 Linux IA (64-bit) Little 12 Microsoft Windows x86 64-bit Little 13 Linux x86 64-bit Little 15 HP Open VMS Little 16 Apple Mac OS Big 17 Solaris Operating System (x86) Little 18 IBM Power Based Linux Big 19 HP IA Open VMS Little 20 Solaris Operating System (x86-64) Little 21 Apple Mac OS (x86-64) Little 20 rows selected.
When creating an export dump file for an import into a lower release database (e.g.: from Oracle11g to Oracle10g), use a version of the Export client that is equal to the version of the target database (= lowest version = Oracle10g in this case). Always use a version of the EXPORT utility that is equal to the lowest version of either the source or the target database. Always use a version of the IMPORT utility that is equal to the version of the target database.
1. If I want to tarnsfer data between diff planforms (like 11g in unix/linux and 10g in windows) what is the option for it?Data Pump can handle this. Platforms don't matter if you are not using transportable. More on that below.
2.What are the dependency we need to check in both databases for the data movment?You may get some data loss if you are using different character sets. If the source is not a subset of the target, then some data
characters may be truncated/modified.
3.Can I use transporteable tablespace here ? which will be more faster?Not if you are going down. Transportable dumpfiles need to be imported into a version equal or greater than the source. If you
have different endianness, then you need to run RMAN convert to modify the datafiles. If you have different character sets, then
there are restrictions here.
4.Can we use exp with some possible option which will do the same ?exp can do the same, but remember, exp is no longer supported, so if you have new features in your database, exp may not
export those objects. I believe you are better off using Data Pump.
CREATE TABLE "HR"."EMPLOYEES1"
( "EMPLOYEE_ID" NUMBER(6,0),
"LAST_NAME" VARCHAR2(25) CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL ENABLE,
"EMAIL" VARCHAR2(25) CONSTRAINT "EMP_EMAIL_NN" NOT NULL ENABLE,
"HIRE_DATE" DATE CONSTRAINT "EMP_HIRE_DATE_NN" NOT NULL ENABLE,
"JOB_ID" VARCHAR2(10) CONSTRAINT "EMP_JOB_NN" NOT NULL ENABLE,
CONSTRAINT "EMP_SALARY_MIN" CHECK (salary > 0) ENABLE,
CONSTRAINT "EMP_EMAIL_UK" UNIQUE ("EMAIL")
USING INDEX ENABLE,
CONSTRAINT "EMP_EMP_ID_PK" PRIMARY KEY ("EMPLOYEE_ID")
USING INDEX ENABLE,
CONSTRAINT "EMP_MANAGER_FK" FOREIGN KEY ("MANAGER_ID")
REFERENCES "HR"."EMPLOYEES" ("EMPLOYEE_ID") ENABLE,
CONSTRAINT "EMP_JOB_FK" FOREIGN KEY ("JOB_ID")
REFERENCES "HR"."JOBS" ("JOB_ID") ENABLE,
CONSTRAINT "EMP_DEPT_FK" FOREIGN KEY ("DEPARTMENT_ID")
REFERENCES "HR"."DEPARTMENTS" ("DEPARTMENT_ID") ENABLE
) as select (col1,....coln from x.table)
will it work & give me all constarint index which I have in old table.