READoracle数据库视频
oracle数据库视频 时间:2021-04-04 阅读:()
OracleDatabaseUtilities:FullTransportableExport/ImportORACLEWHITEPAPER|FEBRUARY2018DisclaimerThefollowingisintendedtooutlineourgeneralproductdirection.
Itisintendedforinformationpurposesonly,andmaynotbeincorporatedintoanycontract.
Itisnotacommitmenttodeliveranymaterial,code,orfunctionality,andshouldnotberelieduponinmakingpurchasingdecisions.
Thedevelopment,release,andtimingofanyfeaturesorfunctionalitydescribedforOracle'sproductsremainsatthesolediscretionofOracle.
ORACLEDATABASEUTILITIES:FULLTRANSPORTABLEEXPORT/IMPORTDisclaimer1Introduction1BenefitsofUsingFullTransportableExport/Import2FullTransportableExport/ImportSupportforPluggableDatabases3InternalsofFullTransportableExport/Import3Example:UsingFullTransportabletoMoveaNon-CDBintoaCDB5Conclusion8Appendix:LimitationsonFullTransportableExport/Import8ORACLEDATABASEUTILITIES:FULLTRANSPORTABLEEXPORT/IMPORTIntroductionOracleDatabaseincludessignificantfeaturessuchastheOracleMultitenantOption.
Thismultitenantarchitectureincludestheabilitytocreatepluggabledatabases(PDBs),awaytoconsolidatemultipledatabasesintoasinglemultitenantcontainerdatabase(CDB).
Customerscansaveondatabasemanagementcosts,achievemoreefficienthardwareutilization,andrealizeeconomiesofscalebymigratingtoaconsolidateddatabaseenvironmentusingpluggabledatabases.
Traditionalmethodsusedfordatabasemigrationworkseamlesslywithpluggabledatabases.
TheoriginalOracleExport/Import,OracleDataPumpexport/import,andtransportabletablespacescanallbeusedtomigratefromearlierversionsofOracleDatabasetoapluggableOracleDatabase.
Inaddition,thefeature,fulltransportableexport/import,isavailabletomakemigrationtoOracleDatabase12candhigherfaster,easier,andmoreefficientthaneverbefore.
Thiswhitepaperdescribesthefulltransportableexport/importfeatureinOracleDatabase.
Aftergivinganoverviewofthebenefitsofusingfulltransportableexport/import,itexplainshowthefeatureworksandprovidesadetailedexampleoffulltransportableexport/importtoshowthesyntaxandprocessflowofusingthisfeature.
Notethat,whilethiswhitepaperfocusesontheuseoffulltransportableexport/importinapluggabledatabaseenvironment,thisfeaturecanbeusedgenerallyformigrationstoaOracleDatabase12cnon-CDBdatabaseaswell.
1|ORACLEDATABASEUTILITIES:FULLTRANSPORTABLEEXPORT/IMPORTBenefitsofUsingFullTransportableExport/ImportFulltransportableexport/importcombinestheeaseofusefamiliartousersoforiginalExport/ImportandDataPumpexport/import,withthespeedofmigrationpossiblewithtransportabletablespaces.
Arecapofthesethreemigrationtechniques,andacomparisonandcontrastwithfulltransportableexport/import,helpsshowwhyfulltransportableexport/importisfaster,easier,andmoreefficientthanpreviouslyavailablemigrationmethods.
FulltransportableexportisavailablestartingwithOracleDatabase11gRelease2(11.
2.
0.
3).
FulltransportableimportisavailablestartingwithOracleDatabase12c.
OriginalExport/Importisgenerallytheslowestmethodforperformingdatabasemigrations.
Whileithasbeenavailableformanyyears,andhasbeenamainstayutilityformanyDBAs,originalExport/Importwasnotdesignedwithlarge(100+GB)databasesinmind.
ItsperformancedoesnotscaletothedatavolumeofmodernITsystems.
Further,originalExport/ImporthasnotbeenenhancedtosupportnewdatabasefunctionalityaddedinOracleDatabase10gandlaterreleases.
Therefore,whileoriginalExport/Importisaknownandreliablemigrationtechnique,itshouldbeusedonlyforOracle9iandearlierdatabases.
OracleDataPumpwasintroducedinOracleDatabase10g,andisdesignedtohandlelargevolumesofdata.
Employingtechniquessuchasparallelworkerprocesses,choosingthebestaccessmethodforthedatabeingmoved,andofferingflexibleandpowerfulexclude/includecapabilities,OracleDataPumphaseffectivelyreplacedoriginalExport/ImportasthemostcommonwaytomovedatabetweenOracledatabases.
ThecommandlineforOracleDataPump,whilenotidenticaltothatoforiginalExport/Import,offersafamiliarfeeltoDBAsandisgenerallyconsideredveryeasytouse.
Evenwithallthesebenefits,however,therecanbecaseswhereDataPumpisovertakenintermsofperformancebytransportabletablespaces.
Whenthedatabeingmovedisverylarge(morethanafewhundredgigabytes),orwhentherearelargevolumesofindexestobemoved,DataPumpisfast…butothertechniquesmaybeevenfaster.
Transportabletablespacesareusuallythefastestfastwaytomoveuserandapplicationdatabetweendatabases,becausetablespacedatafilesaremovedenmassefromthesourcedatabasetothetarget.
Movinganentiredatafileisgenerallymuchfasterthanexportingandimportingindividualrowsorevenblocksofdata.
However,traditionaltransportabletablespacescanrequireafairlycomplicatedsetofstepstomoveuserandapplicationmetadataneededtoeffectivelyusethesetablespacedatafilesinthedestinationdatabase.
Amigrationusingtransportabletablespacescanthereforebecharacterizedasbeingveryfastbutmorecomplex.
AsofOracleDatabase12c,thebestfeaturesofspeedandusabilityarecombinedinfulltransportableexport/import.
Thecommandlineforfulltransportableexport/importisthatofOracleDataPump.
Fulltransportableexport/importcantakeadvantageofDataPumpoptionssuchastheabilitytomovemetadataoveradatabaselink,andisabletoaccomplishafulldatabasemigrationwithasingleimportcommand.
Atthesametime,fulltransportableexport/importusesthetransportabletablespacesmechanismtomoveuserandapplicationdata.
Thisresultsinamigrationthatisveryfast,evenforverylargevolumesofdata.
Mostimportant,fulltransportableexport/importmovesallofthesystem,user,andapplicationmetadataneededforadatabasemigration,withoutthecomplexsetofstepsrequiredforatraditionaltransportabletablespacesoperation.
Thus,fulltransportableexport/importcombinestheeaseofuseofOracleDataPumpwiththeperformanceoftransportabletablespaces,resultinginafeaturethatmakesdatabasemigrationfasterandeasier.
2|ORACLEDATABASEUTILITIES:FULLTRANSPORTABLEEXPORT/IMPORTFullTransportableExport/ImportSupportforPluggableDatabasesFulltransportableexport/importwasdesignedwithpluggabledatabasesasamigrationdestination.
Youcanusefulltransportableexport/importtomigratefromanon-CDBdatabaseintoaPDB,fromonePDBtoanotherPDB,orfromaPDBtoanon-CDB.
Pluggabledatabasesactexactlylikenon-CDBswhenimportingandexportingbothdataandmetadata.
Thestepsformigratingfromanon-CDBintoapluggabledatabaseareasfollows:CreateanewPDBinthedestinationCDBusingthecreatepluggabledatabasecommandSettheuserandapplicationtablespacesinthesourcedatabasetobeREADONLYCopythetablespacedatafilestothedestinationUsinganaccountthathastheDATAPUMP_IMP_FULL_DATABASEprivilege,eitherExportfromthesourcedatabaseusingexpdpwiththeFULL=YTRANSPORTABLE=ALWAYSoptions,andimportintothetargetdatabaseusingimpdp,orImportoveradatabaselinkfromthesourcetothetargetusingimpdpPerformpost-migrationvalidationortestingaccordingyournormalpracticeInternalsofFullTransportableExport/ImportWhilenotintendedtobeanindepthtechnologicaltreatise,thissectiondescribesthemechanismsbehindfulltransportableexport/import.
Theintentofthissectionistogiveyouabetterunderstandingofwhatfulltransportableexport/importdoes,andhowitachievestheoptimalcombinationofusabilityandperformance.
FullTransportableExport/ImportConceptsTounderstandtheinternalsoffulltransportableexport/import,youneedtoknowthedifferencebetweenmovingdatainaconventionalmannerversusatransportableapproach,andthedistinctionbetweenadministrativeandusertablespaces.
Whenusingconventionalmethodstomovedata,OracleDataPumpuseseitherexternaltablesordirectpathunloadtoextractdata.
Whilethechoicebetweenthesetwoaccessmethodsisbasedonthestructureandtypesofthedatabeingunloaded,bothmethodsefficientlyextractlogicalsubsetsofdatafromanOracledatabase.
Incontrast,atransportablemoveofdataandindexesinvolvesthephysicalmovementofoneormoretablespacedatafiles.
Thedatasegmentsinsidethetablespacedatafilesarenotreadindividually.
Instead,theexportoperationextractsthemetadatathatdescribestheobjectscontainingstoragewithineachdatafile,andeachfileismovedasasingleentity.
Movinglargevolumesofdatausingtransportabletablespacescanbefasterthanconventionaldatamovementbecausethereisnoneedtointerpretandextractindividualrowsofdataorindexentries.
Itispossibletomoveindividualtablesorpartitionsinatransportablemanner,buttheentiretablespacedatafileismovedinthesecasesaswell.
Understandingthedifferencebetweenconventionalandtransportabledatamovementishelpfulwhenconsideringthedistinctionbetweenadministrativeandusertablespaces.
Forthepurposesofafulltransportableexport,administrativetablespacesarethetablespacesprovidedbyOracle,suchasSYSTEM,SYSAUX,TEMP,andUNDO.
Thesetablespacescontaintheprocedures,packages,andseeddataforthecoreOracledatabasefunctionalityandOracle-provideddatabasecomponentssuchasOracleSpatial,OracleText,OLAP,JAVAVM,andXMLDatabase.
Incontrast,usertablespacesarethosetablespacesdefinedbydatabaseusersorapplications.
Thesemaystoreuserdata,applicationdata,andanyotherinformationdefinedbyusersofthedatabase.
3|ORACLEDATABASEUTILITIES:FULLTRANSPORTABLEEXPORT/IMPORTThefirststepofafulltransportableexportistocreatethedestinationdatabase.
Thisnewlycreateddatabaseincludesasetofadministrativetablespacesappropriatetothetargetenvironment,completewithOracle-suppliedcomponentsandpackages.
StartingwithOracleDatabase12c,Oracle-suppliedobjectsareneitherexportednorimportedbyOracleDataPump.
Thus,whenmigratingdatafromtheadministrativetablespacesofthesourcedatabase,fulltransportableexportusesconventionaldatamovementtoextractonlythedataandmetadataforuser-definedobjectsthatwillbeneedtobeaddedtothedestinationdatabase.
Theusertablespaces,ontheotherhand,aremovedtothedestinationdatabaseasfulltablespacedatafiles,undertheassumptionthatafullexportmigratesalluserandapplicationdataandmetadatafromthesourcetothedestinationsystem.
Usertablespacesarethusmovedtransportably,resultinginmaximumperformancewhenmigratinguserdata.
Oneconsiderationspecifictofulltransportableexport/importariseswhenthereexistsadatabaseobject(e.
g.
,apartitionedtable)thatisstoredacrossbothuserandadministrativetablespaces.
Storinganobjectinthiswayisgenerallynotgoodpractice,butitispossible.
Ifthereisanobjectwithstorageinbothadministrativeandusertablespaces,thenyoucaneitherredefinethatobjectbeforetransportingyourdata,oruseconventionalDataPumpexport/import.
Theexamplelaterinthiswhitepapershowshowtodetectthisconditionpriortostartingafulltransportableexport.
FullTransportableExportThekeytotheimprovedusabilityoffulltransportableexport/importisthattheexportisabletoextractthemetadataforalluserandadministrativeobjects.
AcalloutmechanismandAPIisprovidedforinternalusebyOraclecomponents,allowingforacompleteextractofthemetadataneededtocreateafullcopyofthedatabaseuponimport.
Alluserandapplicationobjectsthatresideinadministrativetablespacesareunloadedconventionally,includingboththeirmetadata(e.
g.
atabledefinition)andtheirdata(e.
g.
therowsinthattable).
Incontrast,objectsstoredinusertablespaceshaveonlytheirmetadataunloadedbyDataPump;thedataforthoseobjectsismovedtransportablyinthetablespacedatafiles.
InvokingFullTransportableExportOracleDataPumpexport(expdp)isthecommandlineinterfaceforfulltransportableexport.
Youcaninitiateafulltransportableexportbyspecifyingtwoparametersintheparameterfileoronthecommandline:TRANSPORTABLE=ALWAYSandFULL=Y.
TheseparametervaluestellDataPumptousefulltransportableratherthanconventionalexportmethods.
Inaddition,thereisaspecialconsiderationiftheCOMPATIBLEdatabaseinitializationparameterofthesourcedatabaseisnotsettoavalueofatleast12.
0.
Thiswouldbetrue,forexample,ifyouperformafulltransportableexportfromOracleDatabase11gRelease2(11.
2.
0.
3).
Inthiscase,youmustalsospecifytheDataPumpparameterVERSION=12todenotethefactthattheresultoftheexportwillbeimportedintoanOracleDatabase12cRelease1(12.
1)orlaterdatabase.
FulltransportableImportLikeaconventionalDataPumpimport,fulltransportableimportcanbeusedtoimportadumpfileortoimportdirectlyfromasourcedatabaseintoadestinationdatabaseoveradatabaselink.
Theabilitytoimportwithoutusingadumpfilemakesfulltransportableimportanindispensabletoolfordatabasemigrations.
4|ORACLEDATABASEUTILITIES:FULLTRANSPORTABLEEXPORT/IMPORTInvokingFullTransportableImportIfyouareimportingadumpfile,OracleDataPumpisabletodeterminewhetheradumpfilewasproducedbyaconventionalorfulltransportableexport.
Afile-basedfulltransportableimportthusrequiresonlythatyouspecifythedumpfilenameandthelistofusertablespacedatafilestobetransportedusingtheTRANSPORT_DATAFILES=parameter.
Thisparametercanuseacomma-separatedlistoffiles,orcanbespecifiedmultipletimesformultipledatafiles.
Oraclerecommendstheuseofaparameterfiletoavoidissuessuchasproperuseofquotationmarksaroundfilenames.
Ifyouaremigratingdirectlyfromthesourcedatabasetothetargetdatabasewithoutusingadumpfile,thenyoumustspecifyseveralparameterstostarttheoperationinfulltransportablemode.
Thisisbecauseanetworkmodeimportactuallyperformstheexportfromthesourcedatabaseandimporttothetargetdatabaseasasingleoperation.
Anetwork-basedfulltransportableimportrequiresthefollowingparameters:FULL=YTRANSPORTABLE=ALWAYSasspecifiedforafulltransportableexportTRANSPORT_DATAFILES=asusedinafile-basedfulltransportableimportVERSION=12iftheCOMPATIBLEsettingforthesourcedatabaseislowerthan12.
0.
NotethatthesourcedatabasemustbeOracleDatabase11gRelease2(11.
2.
0.
3)orhigherforafulltransportableimportNETWORK_LINK=tospecifythedatabaselinkoverwhichthedataandmetadataaretransferredfromthesourcedatabaseNotethatusertablespacescanbeinaread/writestateonthedestinationsystemduringafulltransportableimport.
Itisimportantthatnootheruserapplicationsexecuteduringtheimportjob.
Atthesuccessfulcompletionoftheimportjob,allusertablespaceswillbesettoaread/writestate.
EndianConversionIfboththesourceandtargetplatformshavethesameendianformat,thendatafilescanbetransportedasiftheywereonthesameplatform.
Ifthesourceplatformandthetargetplatformareofdifferentendianness,thenthedatafilesbeingtransportedmustbeconvertedtothetargetplatformformatusingeithertheRMANCONVERTcommandortheGET_FILEorPUT_FILEproceduresintheDBMS_FILE_TRANSFERpackage.
Notethatencryptedtablespacescannotbetransportedtoaplatformwithdifferentendianness.
Example:UsingFullTransportabletoMoveaNon-CDBintoaCDBInthisexample,weusefulltransportabletomigrateanOracleDatabase11gRelease2(11.
2.
0.
3)databasefromanOracleSolarisx86servertoanOracleDatabase12cPDBinsideofaCDBrunningonOracleLinux.
Thisexampleusesadumpfile,requiringafulltransportableexportfromthesourcedatabasefollowedbyafulltransportableimportatthedestinationdatabase.
Thesourcedatabasehastwousertablespaces:hr_1andhr_2.
TABLE1.
SOURCEDATABASETABLESPACESTablespaceNameEncryptedDatafileNameHR_1Yes/u01/app/oracle/oradata/hr_db/hr_101.
dbfHR_2No/u01/app/oracle/oradata/hr_db/hr_201.
dbf5|ORACLEDATABASEUTILITIES:FULLTRANSPORTABLEEXPORT/IMPORTForthisexampleweassumethattheOracleSIDofthetargetdatabaseisHR_PDB,andthatthedatafilesforthetargetPDBarestoredinthedirectory/u01/app/oracle/oradata/hr_pdb/onthedestinationserver.
Notethatoneofthetablespacesinthisexampleisencrypted.
ThecommandsusedtosetupandadministerTransparentDataEncryption(TDE)havechangedbetweenOracleDatabase11gRelease2andOracleDatabase12c.
PleaseseeOracleDatabaseAdvancedSecurityGuideformoreinformationaboutimplementingTDEforyourdatabase.
STEP1:ChecktheendiannessofbothplatformsTochecktheendiannessofaplatform,runthefollowingqueryoneachplatform.
SQL>SELECTd.
PLATFORM_NAME,ENDIAN_FORMATFROMV$TRANSPORTABLE_PLATFORMtp,V$DATABASEdWHEREtp.
PLATFORM_ID=d.
PLATFORM_ID;Inthiscase,bothOracleSolarisx86andOracleEnterpriseLinuxhavelittleendianformat,sonoendianconversionisnecessary.
STEP2:Verifythatthesetoftablespacestobetransportedisself-containedBeforetransportingasetoftablespaces,youmustverifythattherearenologicalorphysicaldependenciesbetweenobjectsstoredinthetablespacesbeingtransportedandthosethatarenotbeingtransported.
Forexample,youneedtodetectsituationsinwhichanobjectisstoredinbothuserandadministrativetablespaces.
Thisisreferredtoasacontainmentcheck.
Todeterminewhetherourtablespaceshr_1andhr_2areself-contained,includingverificationthatreferentialintegrityconstraintswillbevalidafterthetransport,executethefollowingcommandonthesourcedatabase.
SQL>EXECUTEDBMS_TTS.
TRANSPORT_SET_CHECK('hr_1,hr_2',TRUE);Notethatyoumustincludeallusertablespacesinthedatabasewhenperformingthischeckforafulltransportableexport/import.
AfterinvokingthisPL/SQLprocedure,youcanseeallviolationsbyselectingfromtheTRANSPORT_SET_VIOLATIONSview.
SQL>SELECT*FROMTRANSPORT_SET_VIOLATIONS;Ifthesetoftablespacesisself-contained,thisviewwillbeempty.
Ifanyviolationsarelisted,thenyoumustaddresstheseissuesbeforeproceedingwiththefulltransportableoperation.
STEP3:CreateadirectoryobjectinthesourcedatabaseBeforeyoustartthefulltransportableexport,createadirectoryobjectthatpointstothelocationtowhichthedumpfilewillbewritten.
ReadandwriteaccesstothisdirectoryisautomaticallygrantedtotheDBAroleandtousersSYSandSYSTEM.
SQL>CREATEDIRECTORYdp_dirAS'/u01/app/datafiles';Thisdirectoryobjectcanbedroppedfromthedatabaseafterthefulltransportableexporthasfinished.
6|ORACLEDATABASEUTILITIES:FULLTRANSPORTABLEEXPORT/IMPORTSTEP4:Placethehr_1andhr_2tablespacesinread-onlymodeThetablespacestobetransportedmustbeinread-onlymodeforthedurationoftheexport.
Inthiscaseweneedtoissuetwocommandsonthesourcedatabase.
SQL>ALTERTABLESPACEhr_1READONLY;SQL>ALTERTABLESPACEhr_2READONLY;Thetablespacescanbereturnedtoread-writestatusoncethefulltransportableexporthasfinishedandthetablespacedatafileshavebeencopiedtothedestinationsystem.
.
STEP5:InvokefulltransportableexportonthesourcedatabaseTheuseofMETRICS=YandEXCLUDEofstatisticsarenotrequired,butaregenerallygoodpracticefordatapumpexports.
InvoketheDataPumpexportutilityasauserwiththeDATAPUMP_EXP_FULL_DATABASErole.
$expdpsystem/managerfull=ytransportable=alwaysversion=12\directory=dp_dirdumpfile=full_tts.
dmp\metrics=yexclude=statistics\encryption_password=secret123word456\logfile=full_tts_export.
logNotethattheVERSION=12parameterisrequiredbecausethesourcedatabaseisOracleDatabase11gRelease2(11.
2.
0.
3).
Thisistheonlytimethataversionnumbergreaterthanthecurrentversionisallowedbytheexpdpcommand.
IfthesourcedatabaseisOracleDatabase12c,withCOMPATIBLE=12.
0orhigher,thentheVERSIONparameterisnotrequired.
Aftertheexportcommandcompletes,theexportlogfileshowsalistofallofthetablespacedatafilesthatneedtobemovedtothetarget.
STEP6:TransportthetablespacedatafilesandtheexportdumpfilefromsourcetotargetBecausewearemigratingthisdatabasetoanewserver,wemustcopythetablespacedatafilesandtheexportdumpfiletoalocationthatisaccessiblefromthedestinationdatabase.
Forexample,onthedestinationsystemyoucouldissuethefollowingcommands.
$cd/u01/app/oracle/oradata/hr_pdb/$cp/net//u01/app/oracle/oradata/hr_db/hr_101.
dbf.
$cp/net//u01/app/oracle/oradata/hr_db/hr_201.
dbf.
$cp/net//u01/app/datafiles/full_tts.
dmp.
STEP7:CreateadirectoryobjectonthedestinationdatabaseBecausewecopiedthedatapumpdumpfiletotheoradatadirectoryforHR_PDB,wewillcreateadirectoryobjecttopointtothatsamedirectoryforthisimport.
ThisdirectoryobjectmustbecreatedbyauserconnectedtothePDBcontainer.
SQL>CREATEDIRECTORYdp_dirAS'/u01/app/oracle/oradata/hr_pdb';7|ORACLEDATABASEUTILITIES:FULLTRANSPORTABLEEXPORT/IMPORTSQL>GRANTread,writeondirectorydp_dirtosystem;Thisdirectoryobjectcanbedroppedfromthedatabaseafterthefulltransportableimporthasfinished.
STEP8:InvokefulltransportableimportonthedestinationdatabaseInvoketheDataPumpimportutilityasauserwithDATAPUMP_IMP_FULL_DATABASErole.
YoumustexplicitlyspecifytheservicenameofthePDBintheconnectstringfortheimpdpcommand.
$impdpsystem/manager@hr_pdbdirectory=dp_dir\dumpfile=full_tts.
dmplogfile=full_tts_imp.
log\metrics=y\encryption_password=secret123word456\transport_datafiles='/u01/app/oracle/oradata/hr_pdb/hr_101.
dbf',\'/u01/app/oracle/oradata/hr_pdb/hr_201.
dbf'Notethat,whilethisexampleshowsseveralparametersspecifiedonthecommandline,inmostcasesuseofadatapumpparameterfileisrecommendedtoavoidproblemswithwhitespaceandquotationmarksonthecommandline.
Afterthisstatementexecutessuccessfully,theusertablespacesareautomaticallyplacedinread/writemodeonthedestinationdatabase.
Checktheimportlogfiletoensurethatnounexpectederroroccurred,andperformyournormalpost-migrationvalidationandtesting.
STEP9:(Optional)Restoreusertablespacestoread-writemodeonthesourcedatabaseAfterthefulltransportableexporthasfinished,youcanreturntheuser-definedtablespacestoread-writemodeatthesourcedatabaseifdesired.
SQL>ALTERTABLESPACEhr_101READWRITE;SQL>ALTERTABLESPACEhr_201READWRITE;Onthedestinationdatabase,alltablespacesaresettoread-writemodeautomaticallyuponcompletionofthefulltransportableimport.
ConclusionFulltransportableexport/importisafeaturebeginningwithOracleDatabase12cthatgreatlysimplifiestheprocessofdatabasemigration.
CombiningtheeaseofuseofOracleDataPumpwiththeperformanceoftransportabletablespaces,fulltransportableexport/importgivesyoutheabilitytoupgradeormigratetoOracleDatabase12corlaterinasingleoperationifyoursourcedatabaseisatleastOracleDatabase11gRelease2(11.
2.
0.
3).
Fulltransportableexport/importisavaluabletoolformigratingtopluggabledatabases,allowingyoutotakeadvantageofthecostsavingsandeconomiesofscaleinherentinmovingtoamultitenantarchitecture.
Appendix:LimitationsonFullTransportableExport/ImportWhilefulltransportableexport/importgreatlyenhancestheusabilityoftraditionaltransportabletablespaces,therearelimitationsoftransportingdataaboutwhichyoushouldbeaware.
8|ORACLEDATABASEUTILITIES:FULLTRANSPORTABLEEXPORT/IMPORTLiketraditionaltransportabletablespaces,fulltransportableexport/importrequiresthattheusertablespacestobetransportedbeinread-onlymodeforthedurationoftheexport.
Ifthisisundesirable(whichmaybeparticularlytruewhenyouaretestingthismethodbeforeusingitinproduction),thenyoucanusetheRMANcapabilitytotransporttablespacesfrombackups.
SeeOracleDatabaseBackupandRecoveryUser'sGuideformoreinformationontransportingtablespacesfrombackups.
Allobjectswithstoragethatareselectedforexportmusthavealloftheirstoragesegmentsentirelywithinadministrative,non-transportabletablespacesorentirelywithinuser-definedtransportabletablespaces.
Storageforasingleobjectcannotstraddlethetwokindsoftablespaces.
Otherwise,transportablemodeisinhibitedandcausestheexporttofail.
Inthiscase,theexportneedstobedoneconventionallyortheobject'sstorageredefinedtomeetthiscriteria.
Ifthesourceandtargetplatformsareofdifferentendianness,thenyoumustconvertthedatabeingtransportedsothatitisintheformatofthetargetplatform.
YoucanusetheRMANCONVERTDATAFILEcommandtoconvertthedata.
AlternativelydatafilescanbeconvertedtothetargetplatformbytransferringthemwiththeGET_FILEorPUT_FILEproceduresfromtheDBMS_FILE_TRANSFERpackageYoucannottransportanencryptedtablespacetoaplatformwithdifferentendianness.
Totransportanencryptedtablespacetoaplatformwiththesameendianness,usetheexpdpENCRYPTION_PASSWORDparameterduringexport.
Duringimport,specifythesamevaluefortheimpdpENCRYPTION_PASSWORDparameter.
TheXDBrepositoryisnotsupportedbyfulltransportableexport/import.
However,user-definedXMLschemasaresupportedbyallformsofexport/import:DataPumpexport/import,transportabletablespaces,andfulltransportableexport/import.
TheAutomaticWorkloadRepository(AWR)isnotsupportedbyfulltransportableexport/import.
Usetheawrextr.
sqlandawrload.
sqlscriptstomoveAWRdatabetweenOracledatabases.
Thesourceandtargetdatabasesmustusecompatibledatabasecharactersets.
SeeOracleDatabaseAdministrator'sGuideformoreinformationaboutthisandotherGeneralLimitationsonTransportingData.
9|ORACLEDATABASEUTILITIES:FULLTRANSPORTABLEEXPORT/IMPORTOracleCorporation,WorldHeadquartersWorldwideInquiries500OracleParkwayPhone:+1.
650.
506.
7000RedwoodShores,CA94065,USAFax:+1.
650.
506.
7200means,electronicormechanical,foranypurpose,withoutourpriorwrittenpermission.
OracleDatabaseUtilities:FullTransportableExport/ImportCONNECTWITHUSblogs.
oracle.
com/oraclefacebook.
com/oracletwitter.
com/oracleoracle.
comCopyright2018,Oracleand/oritsaffiliates.
Allrightsreserved.
Thisdocumentisprovidedforinformationpurposesonly,andthecontentshereofaresubjecttochangewithoutnotice.
Thisdocumentisnotwarrantedtobeerror-free,norsubjecttoanyotherwarrantiesorconditions,whetherexpressedorallyorimpliedinlaw,includingimpliedwarrantiesandconditionsofmerchantabilityorfitnessforaparticularpurpose.
Wespecificallydisclaimanyliabilitywithrespecttothisdocument,andnocontractualobligationsareformedeitherdirectlyorindirectlybythisdocument.
ThisdocumentmaynotbereproducedortransmittedinanyformorbyanyOracleandJavaareregisteredtrademarksofOracleand/oritsaffiliates.
Othernamesmaybetrademarksoftheirrespectiveowners.
IntelandIntelXeonaretrademarksorregisteredtrademarksofIntelCorporation.
AllSPARCtrademarksareusedunderlicenseandaretrademarksorregisteredtrademarksofSPARCInternational,Inc.
AMD,Opteron,theAMDlogo,andtheAMDOpteronlogoaretrademarksorregisteredtrademarksofAdvancedMicroDevices.
UNIXisaregisteredtrademarkofTheOpenGroup.
0116February2018Authors:RoyF.
Swonger,GeorgeClaborn,JimStenois,,WilliamBeauregard
Itisintendedforinformationpurposesonly,andmaynotbeincorporatedintoanycontract.
Itisnotacommitmenttodeliveranymaterial,code,orfunctionality,andshouldnotberelieduponinmakingpurchasingdecisions.
Thedevelopment,release,andtimingofanyfeaturesorfunctionalitydescribedforOracle'sproductsremainsatthesolediscretionofOracle.
ORACLEDATABASEUTILITIES:FULLTRANSPORTABLEEXPORT/IMPORTDisclaimer1Introduction1BenefitsofUsingFullTransportableExport/Import2FullTransportableExport/ImportSupportforPluggableDatabases3InternalsofFullTransportableExport/Import3Example:UsingFullTransportabletoMoveaNon-CDBintoaCDB5Conclusion8Appendix:LimitationsonFullTransportableExport/Import8ORACLEDATABASEUTILITIES:FULLTRANSPORTABLEEXPORT/IMPORTIntroductionOracleDatabaseincludessignificantfeaturessuchastheOracleMultitenantOption.
Thismultitenantarchitectureincludestheabilitytocreatepluggabledatabases(PDBs),awaytoconsolidatemultipledatabasesintoasinglemultitenantcontainerdatabase(CDB).
Customerscansaveondatabasemanagementcosts,achievemoreefficienthardwareutilization,andrealizeeconomiesofscalebymigratingtoaconsolidateddatabaseenvironmentusingpluggabledatabases.
Traditionalmethodsusedfordatabasemigrationworkseamlesslywithpluggabledatabases.
TheoriginalOracleExport/Import,OracleDataPumpexport/import,andtransportabletablespacescanallbeusedtomigratefromearlierversionsofOracleDatabasetoapluggableOracleDatabase.
Inaddition,thefeature,fulltransportableexport/import,isavailabletomakemigrationtoOracleDatabase12candhigherfaster,easier,andmoreefficientthaneverbefore.
Thiswhitepaperdescribesthefulltransportableexport/importfeatureinOracleDatabase.
Aftergivinganoverviewofthebenefitsofusingfulltransportableexport/import,itexplainshowthefeatureworksandprovidesadetailedexampleoffulltransportableexport/importtoshowthesyntaxandprocessflowofusingthisfeature.
Notethat,whilethiswhitepaperfocusesontheuseoffulltransportableexport/importinapluggabledatabaseenvironment,thisfeaturecanbeusedgenerallyformigrationstoaOracleDatabase12cnon-CDBdatabaseaswell.
1|ORACLEDATABASEUTILITIES:FULLTRANSPORTABLEEXPORT/IMPORTBenefitsofUsingFullTransportableExport/ImportFulltransportableexport/importcombinestheeaseofusefamiliartousersoforiginalExport/ImportandDataPumpexport/import,withthespeedofmigrationpossiblewithtransportabletablespaces.
Arecapofthesethreemigrationtechniques,andacomparisonandcontrastwithfulltransportableexport/import,helpsshowwhyfulltransportableexport/importisfaster,easier,andmoreefficientthanpreviouslyavailablemigrationmethods.
FulltransportableexportisavailablestartingwithOracleDatabase11gRelease2(11.
2.
0.
3).
FulltransportableimportisavailablestartingwithOracleDatabase12c.
OriginalExport/Importisgenerallytheslowestmethodforperformingdatabasemigrations.
Whileithasbeenavailableformanyyears,andhasbeenamainstayutilityformanyDBAs,originalExport/Importwasnotdesignedwithlarge(100+GB)databasesinmind.
ItsperformancedoesnotscaletothedatavolumeofmodernITsystems.
Further,originalExport/ImporthasnotbeenenhancedtosupportnewdatabasefunctionalityaddedinOracleDatabase10gandlaterreleases.
Therefore,whileoriginalExport/Importisaknownandreliablemigrationtechnique,itshouldbeusedonlyforOracle9iandearlierdatabases.
OracleDataPumpwasintroducedinOracleDatabase10g,andisdesignedtohandlelargevolumesofdata.
Employingtechniquessuchasparallelworkerprocesses,choosingthebestaccessmethodforthedatabeingmoved,andofferingflexibleandpowerfulexclude/includecapabilities,OracleDataPumphaseffectivelyreplacedoriginalExport/ImportasthemostcommonwaytomovedatabetweenOracledatabases.
ThecommandlineforOracleDataPump,whilenotidenticaltothatoforiginalExport/Import,offersafamiliarfeeltoDBAsandisgenerallyconsideredveryeasytouse.
Evenwithallthesebenefits,however,therecanbecaseswhereDataPumpisovertakenintermsofperformancebytransportabletablespaces.
Whenthedatabeingmovedisverylarge(morethanafewhundredgigabytes),orwhentherearelargevolumesofindexestobemoved,DataPumpisfast…butothertechniquesmaybeevenfaster.
Transportabletablespacesareusuallythefastestfastwaytomoveuserandapplicationdatabetweendatabases,becausetablespacedatafilesaremovedenmassefromthesourcedatabasetothetarget.
Movinganentiredatafileisgenerallymuchfasterthanexportingandimportingindividualrowsorevenblocksofdata.
However,traditionaltransportabletablespacescanrequireafairlycomplicatedsetofstepstomoveuserandapplicationmetadataneededtoeffectivelyusethesetablespacedatafilesinthedestinationdatabase.
Amigrationusingtransportabletablespacescanthereforebecharacterizedasbeingveryfastbutmorecomplex.
AsofOracleDatabase12c,thebestfeaturesofspeedandusabilityarecombinedinfulltransportableexport/import.
Thecommandlineforfulltransportableexport/importisthatofOracleDataPump.
Fulltransportableexport/importcantakeadvantageofDataPumpoptionssuchastheabilitytomovemetadataoveradatabaselink,andisabletoaccomplishafulldatabasemigrationwithasingleimportcommand.
Atthesametime,fulltransportableexport/importusesthetransportabletablespacesmechanismtomoveuserandapplicationdata.
Thisresultsinamigrationthatisveryfast,evenforverylargevolumesofdata.
Mostimportant,fulltransportableexport/importmovesallofthesystem,user,andapplicationmetadataneededforadatabasemigration,withoutthecomplexsetofstepsrequiredforatraditionaltransportabletablespacesoperation.
Thus,fulltransportableexport/importcombinestheeaseofuseofOracleDataPumpwiththeperformanceoftransportabletablespaces,resultinginafeaturethatmakesdatabasemigrationfasterandeasier.
2|ORACLEDATABASEUTILITIES:FULLTRANSPORTABLEEXPORT/IMPORTFullTransportableExport/ImportSupportforPluggableDatabasesFulltransportableexport/importwasdesignedwithpluggabledatabasesasamigrationdestination.
Youcanusefulltransportableexport/importtomigratefromanon-CDBdatabaseintoaPDB,fromonePDBtoanotherPDB,orfromaPDBtoanon-CDB.
Pluggabledatabasesactexactlylikenon-CDBswhenimportingandexportingbothdataandmetadata.
Thestepsformigratingfromanon-CDBintoapluggabledatabaseareasfollows:CreateanewPDBinthedestinationCDBusingthecreatepluggabledatabasecommandSettheuserandapplicationtablespacesinthesourcedatabasetobeREADONLYCopythetablespacedatafilestothedestinationUsinganaccountthathastheDATAPUMP_IMP_FULL_DATABASEprivilege,eitherExportfromthesourcedatabaseusingexpdpwiththeFULL=YTRANSPORTABLE=ALWAYSoptions,andimportintothetargetdatabaseusingimpdp,orImportoveradatabaselinkfromthesourcetothetargetusingimpdpPerformpost-migrationvalidationortestingaccordingyournormalpracticeInternalsofFullTransportableExport/ImportWhilenotintendedtobeanindepthtechnologicaltreatise,thissectiondescribesthemechanismsbehindfulltransportableexport/import.
Theintentofthissectionistogiveyouabetterunderstandingofwhatfulltransportableexport/importdoes,andhowitachievestheoptimalcombinationofusabilityandperformance.
FullTransportableExport/ImportConceptsTounderstandtheinternalsoffulltransportableexport/import,youneedtoknowthedifferencebetweenmovingdatainaconventionalmannerversusatransportableapproach,andthedistinctionbetweenadministrativeandusertablespaces.
Whenusingconventionalmethodstomovedata,OracleDataPumpuseseitherexternaltablesordirectpathunloadtoextractdata.
Whilethechoicebetweenthesetwoaccessmethodsisbasedonthestructureandtypesofthedatabeingunloaded,bothmethodsefficientlyextractlogicalsubsetsofdatafromanOracledatabase.
Incontrast,atransportablemoveofdataandindexesinvolvesthephysicalmovementofoneormoretablespacedatafiles.
Thedatasegmentsinsidethetablespacedatafilesarenotreadindividually.
Instead,theexportoperationextractsthemetadatathatdescribestheobjectscontainingstoragewithineachdatafile,andeachfileismovedasasingleentity.
Movinglargevolumesofdatausingtransportabletablespacescanbefasterthanconventionaldatamovementbecausethereisnoneedtointerpretandextractindividualrowsofdataorindexentries.
Itispossibletomoveindividualtablesorpartitionsinatransportablemanner,buttheentiretablespacedatafileismovedinthesecasesaswell.
Understandingthedifferencebetweenconventionalandtransportabledatamovementishelpfulwhenconsideringthedistinctionbetweenadministrativeandusertablespaces.
Forthepurposesofafulltransportableexport,administrativetablespacesarethetablespacesprovidedbyOracle,suchasSYSTEM,SYSAUX,TEMP,andUNDO.
Thesetablespacescontaintheprocedures,packages,andseeddataforthecoreOracledatabasefunctionalityandOracle-provideddatabasecomponentssuchasOracleSpatial,OracleText,OLAP,JAVAVM,andXMLDatabase.
Incontrast,usertablespacesarethosetablespacesdefinedbydatabaseusersorapplications.
Thesemaystoreuserdata,applicationdata,andanyotherinformationdefinedbyusersofthedatabase.
3|ORACLEDATABASEUTILITIES:FULLTRANSPORTABLEEXPORT/IMPORTThefirststepofafulltransportableexportistocreatethedestinationdatabase.
Thisnewlycreateddatabaseincludesasetofadministrativetablespacesappropriatetothetargetenvironment,completewithOracle-suppliedcomponentsandpackages.
StartingwithOracleDatabase12c,Oracle-suppliedobjectsareneitherexportednorimportedbyOracleDataPump.
Thus,whenmigratingdatafromtheadministrativetablespacesofthesourcedatabase,fulltransportableexportusesconventionaldatamovementtoextractonlythedataandmetadataforuser-definedobjectsthatwillbeneedtobeaddedtothedestinationdatabase.
Theusertablespaces,ontheotherhand,aremovedtothedestinationdatabaseasfulltablespacedatafiles,undertheassumptionthatafullexportmigratesalluserandapplicationdataandmetadatafromthesourcetothedestinationsystem.
Usertablespacesarethusmovedtransportably,resultinginmaximumperformancewhenmigratinguserdata.
Oneconsiderationspecifictofulltransportableexport/importariseswhenthereexistsadatabaseobject(e.
g.
,apartitionedtable)thatisstoredacrossbothuserandadministrativetablespaces.
Storinganobjectinthiswayisgenerallynotgoodpractice,butitispossible.
Ifthereisanobjectwithstorageinbothadministrativeandusertablespaces,thenyoucaneitherredefinethatobjectbeforetransportingyourdata,oruseconventionalDataPumpexport/import.
Theexamplelaterinthiswhitepapershowshowtodetectthisconditionpriortostartingafulltransportableexport.
FullTransportableExportThekeytotheimprovedusabilityoffulltransportableexport/importisthattheexportisabletoextractthemetadataforalluserandadministrativeobjects.
AcalloutmechanismandAPIisprovidedforinternalusebyOraclecomponents,allowingforacompleteextractofthemetadataneededtocreateafullcopyofthedatabaseuponimport.
Alluserandapplicationobjectsthatresideinadministrativetablespacesareunloadedconventionally,includingboththeirmetadata(e.
g.
atabledefinition)andtheirdata(e.
g.
therowsinthattable).
Incontrast,objectsstoredinusertablespaceshaveonlytheirmetadataunloadedbyDataPump;thedataforthoseobjectsismovedtransportablyinthetablespacedatafiles.
InvokingFullTransportableExportOracleDataPumpexport(expdp)isthecommandlineinterfaceforfulltransportableexport.
Youcaninitiateafulltransportableexportbyspecifyingtwoparametersintheparameterfileoronthecommandline:TRANSPORTABLE=ALWAYSandFULL=Y.
TheseparametervaluestellDataPumptousefulltransportableratherthanconventionalexportmethods.
Inaddition,thereisaspecialconsiderationiftheCOMPATIBLEdatabaseinitializationparameterofthesourcedatabaseisnotsettoavalueofatleast12.
0.
Thiswouldbetrue,forexample,ifyouperformafulltransportableexportfromOracleDatabase11gRelease2(11.
2.
0.
3).
Inthiscase,youmustalsospecifytheDataPumpparameterVERSION=12todenotethefactthattheresultoftheexportwillbeimportedintoanOracleDatabase12cRelease1(12.
1)orlaterdatabase.
FulltransportableImportLikeaconventionalDataPumpimport,fulltransportableimportcanbeusedtoimportadumpfileortoimportdirectlyfromasourcedatabaseintoadestinationdatabaseoveradatabaselink.
Theabilitytoimportwithoutusingadumpfilemakesfulltransportableimportanindispensabletoolfordatabasemigrations.
4|ORACLEDATABASEUTILITIES:FULLTRANSPORTABLEEXPORT/IMPORTInvokingFullTransportableImportIfyouareimportingadumpfile,OracleDataPumpisabletodeterminewhetheradumpfilewasproducedbyaconventionalorfulltransportableexport.
Afile-basedfulltransportableimportthusrequiresonlythatyouspecifythedumpfilenameandthelistofusertablespacedatafilestobetransportedusingtheTRANSPORT_DATAFILES=parameter.
Thisparametercanuseacomma-separatedlistoffiles,orcanbespecifiedmultipletimesformultipledatafiles.
Oraclerecommendstheuseofaparameterfiletoavoidissuessuchasproperuseofquotationmarksaroundfilenames.
Ifyouaremigratingdirectlyfromthesourcedatabasetothetargetdatabasewithoutusingadumpfile,thenyoumustspecifyseveralparameterstostarttheoperationinfulltransportablemode.
Thisisbecauseanetworkmodeimportactuallyperformstheexportfromthesourcedatabaseandimporttothetargetdatabaseasasingleoperation.
Anetwork-basedfulltransportableimportrequiresthefollowingparameters:FULL=YTRANSPORTABLE=ALWAYSasspecifiedforafulltransportableexportTRANSPORT_DATAFILES=asusedinafile-basedfulltransportableimportVERSION=12iftheCOMPATIBLEsettingforthesourcedatabaseislowerthan12.
0.
NotethatthesourcedatabasemustbeOracleDatabase11gRelease2(11.
2.
0.
3)orhigherforafulltransportableimportNETWORK_LINK=tospecifythedatabaselinkoverwhichthedataandmetadataaretransferredfromthesourcedatabaseNotethatusertablespacescanbeinaread/writestateonthedestinationsystemduringafulltransportableimport.
Itisimportantthatnootheruserapplicationsexecuteduringtheimportjob.
Atthesuccessfulcompletionoftheimportjob,allusertablespaceswillbesettoaread/writestate.
EndianConversionIfboththesourceandtargetplatformshavethesameendianformat,thendatafilescanbetransportedasiftheywereonthesameplatform.
Ifthesourceplatformandthetargetplatformareofdifferentendianness,thenthedatafilesbeingtransportedmustbeconvertedtothetargetplatformformatusingeithertheRMANCONVERTcommandortheGET_FILEorPUT_FILEproceduresintheDBMS_FILE_TRANSFERpackage.
Notethatencryptedtablespacescannotbetransportedtoaplatformwithdifferentendianness.
Example:UsingFullTransportabletoMoveaNon-CDBintoaCDBInthisexample,weusefulltransportabletomigrateanOracleDatabase11gRelease2(11.
2.
0.
3)databasefromanOracleSolarisx86servertoanOracleDatabase12cPDBinsideofaCDBrunningonOracleLinux.
Thisexampleusesadumpfile,requiringafulltransportableexportfromthesourcedatabasefollowedbyafulltransportableimportatthedestinationdatabase.
Thesourcedatabasehastwousertablespaces:hr_1andhr_2.
TABLE1.
SOURCEDATABASETABLESPACESTablespaceNameEncryptedDatafileNameHR_1Yes/u01/app/oracle/oradata/hr_db/hr_101.
dbfHR_2No/u01/app/oracle/oradata/hr_db/hr_201.
dbf5|ORACLEDATABASEUTILITIES:FULLTRANSPORTABLEEXPORT/IMPORTForthisexampleweassumethattheOracleSIDofthetargetdatabaseisHR_PDB,andthatthedatafilesforthetargetPDBarestoredinthedirectory/u01/app/oracle/oradata/hr_pdb/onthedestinationserver.
Notethatoneofthetablespacesinthisexampleisencrypted.
ThecommandsusedtosetupandadministerTransparentDataEncryption(TDE)havechangedbetweenOracleDatabase11gRelease2andOracleDatabase12c.
PleaseseeOracleDatabaseAdvancedSecurityGuideformoreinformationaboutimplementingTDEforyourdatabase.
STEP1:ChecktheendiannessofbothplatformsTochecktheendiannessofaplatform,runthefollowingqueryoneachplatform.
SQL>SELECTd.
PLATFORM_NAME,ENDIAN_FORMATFROMV$TRANSPORTABLE_PLATFORMtp,V$DATABASEdWHEREtp.
PLATFORM_ID=d.
PLATFORM_ID;Inthiscase,bothOracleSolarisx86andOracleEnterpriseLinuxhavelittleendianformat,sonoendianconversionisnecessary.
STEP2:Verifythatthesetoftablespacestobetransportedisself-containedBeforetransportingasetoftablespaces,youmustverifythattherearenologicalorphysicaldependenciesbetweenobjectsstoredinthetablespacesbeingtransportedandthosethatarenotbeingtransported.
Forexample,youneedtodetectsituationsinwhichanobjectisstoredinbothuserandadministrativetablespaces.
Thisisreferredtoasacontainmentcheck.
Todeterminewhetherourtablespaceshr_1andhr_2areself-contained,includingverificationthatreferentialintegrityconstraintswillbevalidafterthetransport,executethefollowingcommandonthesourcedatabase.
SQL>EXECUTEDBMS_TTS.
TRANSPORT_SET_CHECK('hr_1,hr_2',TRUE);Notethatyoumustincludeallusertablespacesinthedatabasewhenperformingthischeckforafulltransportableexport/import.
AfterinvokingthisPL/SQLprocedure,youcanseeallviolationsbyselectingfromtheTRANSPORT_SET_VIOLATIONSview.
SQL>SELECT*FROMTRANSPORT_SET_VIOLATIONS;Ifthesetoftablespacesisself-contained,thisviewwillbeempty.
Ifanyviolationsarelisted,thenyoumustaddresstheseissuesbeforeproceedingwiththefulltransportableoperation.
STEP3:CreateadirectoryobjectinthesourcedatabaseBeforeyoustartthefulltransportableexport,createadirectoryobjectthatpointstothelocationtowhichthedumpfilewillbewritten.
ReadandwriteaccesstothisdirectoryisautomaticallygrantedtotheDBAroleandtousersSYSandSYSTEM.
SQL>CREATEDIRECTORYdp_dirAS'/u01/app/datafiles';Thisdirectoryobjectcanbedroppedfromthedatabaseafterthefulltransportableexporthasfinished.
6|ORACLEDATABASEUTILITIES:FULLTRANSPORTABLEEXPORT/IMPORTSTEP4:Placethehr_1andhr_2tablespacesinread-onlymodeThetablespacestobetransportedmustbeinread-onlymodeforthedurationoftheexport.
Inthiscaseweneedtoissuetwocommandsonthesourcedatabase.
SQL>ALTERTABLESPACEhr_1READONLY;SQL>ALTERTABLESPACEhr_2READONLY;Thetablespacescanbereturnedtoread-writestatusoncethefulltransportableexporthasfinishedandthetablespacedatafileshavebeencopiedtothedestinationsystem.
.
STEP5:InvokefulltransportableexportonthesourcedatabaseTheuseofMETRICS=YandEXCLUDEofstatisticsarenotrequired,butaregenerallygoodpracticefordatapumpexports.
InvoketheDataPumpexportutilityasauserwiththeDATAPUMP_EXP_FULL_DATABASErole.
$expdpsystem/managerfull=ytransportable=alwaysversion=12\directory=dp_dirdumpfile=full_tts.
dmp\metrics=yexclude=statistics\encryption_password=secret123word456\logfile=full_tts_export.
logNotethattheVERSION=12parameterisrequiredbecausethesourcedatabaseisOracleDatabase11gRelease2(11.
2.
0.
3).
Thisistheonlytimethataversionnumbergreaterthanthecurrentversionisallowedbytheexpdpcommand.
IfthesourcedatabaseisOracleDatabase12c,withCOMPATIBLE=12.
0orhigher,thentheVERSIONparameterisnotrequired.
Aftertheexportcommandcompletes,theexportlogfileshowsalistofallofthetablespacedatafilesthatneedtobemovedtothetarget.
STEP6:TransportthetablespacedatafilesandtheexportdumpfilefromsourcetotargetBecausewearemigratingthisdatabasetoanewserver,wemustcopythetablespacedatafilesandtheexportdumpfiletoalocationthatisaccessiblefromthedestinationdatabase.
Forexample,onthedestinationsystemyoucouldissuethefollowingcommands.
$cd/u01/app/oracle/oradata/hr_pdb/$cp/net//u01/app/oracle/oradata/hr_db/hr_101.
dbf.
$cp/net//u01/app/oracle/oradata/hr_db/hr_201.
dbf.
$cp/net//u01/app/datafiles/full_tts.
dmp.
STEP7:CreateadirectoryobjectonthedestinationdatabaseBecausewecopiedthedatapumpdumpfiletotheoradatadirectoryforHR_PDB,wewillcreateadirectoryobjecttopointtothatsamedirectoryforthisimport.
ThisdirectoryobjectmustbecreatedbyauserconnectedtothePDBcontainer.
SQL>CREATEDIRECTORYdp_dirAS'/u01/app/oracle/oradata/hr_pdb';7|ORACLEDATABASEUTILITIES:FULLTRANSPORTABLEEXPORT/IMPORTSQL>GRANTread,writeondirectorydp_dirtosystem;Thisdirectoryobjectcanbedroppedfromthedatabaseafterthefulltransportableimporthasfinished.
STEP8:InvokefulltransportableimportonthedestinationdatabaseInvoketheDataPumpimportutilityasauserwithDATAPUMP_IMP_FULL_DATABASErole.
YoumustexplicitlyspecifytheservicenameofthePDBintheconnectstringfortheimpdpcommand.
$impdpsystem/manager@hr_pdbdirectory=dp_dir\dumpfile=full_tts.
dmplogfile=full_tts_imp.
log\metrics=y\encryption_password=secret123word456\transport_datafiles='/u01/app/oracle/oradata/hr_pdb/hr_101.
dbf',\'/u01/app/oracle/oradata/hr_pdb/hr_201.
dbf'Notethat,whilethisexampleshowsseveralparametersspecifiedonthecommandline,inmostcasesuseofadatapumpparameterfileisrecommendedtoavoidproblemswithwhitespaceandquotationmarksonthecommandline.
Afterthisstatementexecutessuccessfully,theusertablespacesareautomaticallyplacedinread/writemodeonthedestinationdatabase.
Checktheimportlogfiletoensurethatnounexpectederroroccurred,andperformyournormalpost-migrationvalidationandtesting.
STEP9:(Optional)Restoreusertablespacestoread-writemodeonthesourcedatabaseAfterthefulltransportableexporthasfinished,youcanreturntheuser-definedtablespacestoread-writemodeatthesourcedatabaseifdesired.
SQL>ALTERTABLESPACEhr_101READWRITE;SQL>ALTERTABLESPACEhr_201READWRITE;Onthedestinationdatabase,alltablespacesaresettoread-writemodeautomaticallyuponcompletionofthefulltransportableimport.
ConclusionFulltransportableexport/importisafeaturebeginningwithOracleDatabase12cthatgreatlysimplifiestheprocessofdatabasemigration.
CombiningtheeaseofuseofOracleDataPumpwiththeperformanceoftransportabletablespaces,fulltransportableexport/importgivesyoutheabilitytoupgradeormigratetoOracleDatabase12corlaterinasingleoperationifyoursourcedatabaseisatleastOracleDatabase11gRelease2(11.
2.
0.
3).
Fulltransportableexport/importisavaluabletoolformigratingtopluggabledatabases,allowingyoutotakeadvantageofthecostsavingsandeconomiesofscaleinherentinmovingtoamultitenantarchitecture.
Appendix:LimitationsonFullTransportableExport/ImportWhilefulltransportableexport/importgreatlyenhancestheusabilityoftraditionaltransportabletablespaces,therearelimitationsoftransportingdataaboutwhichyoushouldbeaware.
8|ORACLEDATABASEUTILITIES:FULLTRANSPORTABLEEXPORT/IMPORTLiketraditionaltransportabletablespaces,fulltransportableexport/importrequiresthattheusertablespacestobetransportedbeinread-onlymodeforthedurationoftheexport.
Ifthisisundesirable(whichmaybeparticularlytruewhenyouaretestingthismethodbeforeusingitinproduction),thenyoucanusetheRMANcapabilitytotransporttablespacesfrombackups.
SeeOracleDatabaseBackupandRecoveryUser'sGuideformoreinformationontransportingtablespacesfrombackups.
Allobjectswithstoragethatareselectedforexportmusthavealloftheirstoragesegmentsentirelywithinadministrative,non-transportabletablespacesorentirelywithinuser-definedtransportabletablespaces.
Storageforasingleobjectcannotstraddlethetwokindsoftablespaces.
Otherwise,transportablemodeisinhibitedandcausestheexporttofail.
Inthiscase,theexportneedstobedoneconventionallyortheobject'sstorageredefinedtomeetthiscriteria.
Ifthesourceandtargetplatformsareofdifferentendianness,thenyoumustconvertthedatabeingtransportedsothatitisintheformatofthetargetplatform.
YoucanusetheRMANCONVERTDATAFILEcommandtoconvertthedata.
AlternativelydatafilescanbeconvertedtothetargetplatformbytransferringthemwiththeGET_FILEorPUT_FILEproceduresfromtheDBMS_FILE_TRANSFERpackageYoucannottransportanencryptedtablespacetoaplatformwithdifferentendianness.
Totransportanencryptedtablespacetoaplatformwiththesameendianness,usetheexpdpENCRYPTION_PASSWORDparameterduringexport.
Duringimport,specifythesamevaluefortheimpdpENCRYPTION_PASSWORDparameter.
TheXDBrepositoryisnotsupportedbyfulltransportableexport/import.
However,user-definedXMLschemasaresupportedbyallformsofexport/import:DataPumpexport/import,transportabletablespaces,andfulltransportableexport/import.
TheAutomaticWorkloadRepository(AWR)isnotsupportedbyfulltransportableexport/import.
Usetheawrextr.
sqlandawrload.
sqlscriptstomoveAWRdatabetweenOracledatabases.
Thesourceandtargetdatabasesmustusecompatibledatabasecharactersets.
SeeOracleDatabaseAdministrator'sGuideformoreinformationaboutthisandotherGeneralLimitationsonTransportingData.
9|ORACLEDATABASEUTILITIES:FULLTRANSPORTABLEEXPORT/IMPORTOracleCorporation,WorldHeadquartersWorldwideInquiries500OracleParkwayPhone:+1.
650.
506.
7000RedwoodShores,CA94065,USAFax:+1.
650.
506.
7200means,electronicormechanical,foranypurpose,withoutourpriorwrittenpermission.
OracleDatabaseUtilities:FullTransportableExport/ImportCONNECTWITHUSblogs.
oracle.
com/oraclefacebook.
com/oracletwitter.
com/oracleoracle.
comCopyright2018,Oracleand/oritsaffiliates.
Allrightsreserved.
Thisdocumentisprovidedforinformationpurposesonly,andthecontentshereofaresubjecttochangewithoutnotice.
Thisdocumentisnotwarrantedtobeerror-free,norsubjecttoanyotherwarrantiesorconditions,whetherexpressedorallyorimpliedinlaw,includingimpliedwarrantiesandconditionsofmerchantabilityorfitnessforaparticularpurpose.
Wespecificallydisclaimanyliabilitywithrespecttothisdocument,andnocontractualobligationsareformedeitherdirectlyorindirectlybythisdocument.
ThisdocumentmaynotbereproducedortransmittedinanyformorbyanyOracleandJavaareregisteredtrademarksofOracleand/oritsaffiliates.
Othernamesmaybetrademarksoftheirrespectiveowners.
IntelandIntelXeonaretrademarksorregisteredtrademarksofIntelCorporation.
AllSPARCtrademarksareusedunderlicenseandaretrademarksorregisteredtrademarksofSPARCInternational,Inc.
AMD,Opteron,theAMDlogo,andtheAMDOpteronlogoaretrademarksorregisteredtrademarksofAdvancedMicroDevices.
UNIXisaregisteredtrademarkofTheOpenGroup.
0116February2018Authors:RoyF.
Swonger,GeorgeClaborn,JimStenois,,WilliamBeauregard
- READoracle数据库视频相关文档
- 数据oracle数据库视频
- singleoracle数据库视频
- copiesoracle数据库视频
- Elementsoracle数据库视频
- upgradingoracle数据库视频
- criticaloracle数据库视频
Sharktech10Gbps带宽，不限制流量，自带5个IPv4，100G防御
Sharktech荷兰10G带宽的独立服务器月付319美元起，10Gbps共享带宽，不限制流量，自带5个IPv4，免费60Gbps的 DDoS防御，可加到100G防御。CPU内存HDD价格购买地址E3-1270v216G2T$319/月链接E3-1270v516G2T$329/月链接2*E5-2670v232G2T$389/月链接2*E5-2678v364G2T$409/月链接这里我们需要注意，默...
BlueHost 周年庆典 - 美国/香港虚拟主机 美国SSD VPS低至月32元
我们对于BlueHost主机商还是比较熟悉的，早年我们还是全民使用虚拟主机的时候，大部分的外贸主机都会用到BlueHost无限虚拟主机方案，那时候他们商家只有一款虚拟主机方案。目前，商家国际款和国内款是有差异营销的，BlueHost国内有提供香港、美国、印度和欧洲机房。包括有提供虚拟主机、VPS和独立服务器。现在，BlueHost 商家周年活动，全场五折优惠。我们看看这次的活动有哪些值得选择的。 ...
提速啦(24元/月）河南BGP云服务器活动 买一年送一年4核 4G 5M
提速啦的来历提速啦是 网站 本着“良心 便宜 稳定”的初衷 为小白用户避免被坑 由赣州王成璟网络科技有限公司旗下赣州提速啦网络科技有限公司运营 投资1000万人民币 在美国Cera 香港CTG 香港Cera 国内 杭州 宿迁 浙江 赣州 南昌 大连 辽宁 扬州 等地区建立数据中心 正规持有IDC ISP CDN 云牌照 公司。公司购买产品支持3天内退款 超过3天步退款政策。提速啦的市场定位提速啦主...
oracle数据库视频为你推荐
-
网罗设计网络设计是什么专业www.jjwxc.net有那个网站可以看书？杰景新特杰德特这个英雄怎么样月神谭适合12岁男孩的网名,要非主流的，帮吗找找，谢啦月神谭有没有什么好看的小说?拒绝言情小说!曹谷兰曹谷兰事件 有吧友知道吗长尾关键词挖掘工具外贸长尾关键词挖掘工具哪个好用5xoy.com求个如月群真汉化版下载地址lcoc.top日本Ni-TOP是什么意思？33tutu.comDnf绝望100鬼泣怎么过