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数据库视频
MineServer：洛杉矶CN2 GIA VPS/512MB内存/20GB NVME/800GB流量/200Mbps/KVM,58元/季
mineserver怎么样?mineserver是一家国人商家，主要提供香港CN2 KVM VPS、香港CMI KVM VPS、日本CN2 KVM VPS、洛杉矶cn2 gia端口转发等服务，之前介绍过几次，最近比较活跃。这家新推出了洛杉矶CN2 GIA VPS，512MB内存/20GB NVME/800GB流量/200Mbps/KVM,58元/季，并且进行了带宽升级，同时IP更改为美国IP。点击...
天上云：香港大带宽物理机服务器572元;20Mbps带宽!三网CN2线路
天上云服务器怎么样?天上云是国人商家，成都天上云网络科技有限公司，专注于香港、美国海外云服务器的产品，有多年的运维维护经验。世界这么大 靠谱最重，我们7*24H为您提供服务，贴心售后服务，安心、省事儿、稳定、靠谱。目前，天上云香港大带宽物理机服务器572元;20Mbps带宽!三网CN2线路，香港沙田数据中心!点击进入：天上云官方网站地址香港沙田数据中心!线路说明 ：去程中国电信CN2 +中国联通+...
ShockHosting（$4.99/月），东京机房 可享受五折优惠，下单赠送10美金
ShockHosting商家在前面文章中有介绍过几次。ShockHosting商家成立于2013年的美国主机商，目前主要提供虚拟主机、VPS主机、独立服务器和域名注册等综合IDC业务，现有美国洛杉矶、新泽西、芝加哥、达拉斯、荷兰阿姆斯特丹、英国和澳大利亚悉尼七大数据中心。这次有新增日本东京机房。而且同时有推出5折优惠促销，而且即刻使用支付宝下单的话还可获赠10美金的账户信用额度，折扣相比之前的常规...
oracle数据库视频为你推荐
-
今日油条油条每周最多能吃多少www.4411b.com难道那www真的4411B坏了，还是4411b梗换com鑫域明了抓站工具抓鸡要什么工具?彪言彪语（ ）言（ ）语ww.43994399在线单机小游戏国风商讯国风轮胎待遇怎么样雀嘴鳝长嘴鳄（雀鳝）如何分雌雄达林赞雅达信雅是什么意思www.mm.com找几个有美女图片的网址新广告法被投诉违反了新广告法该怎么办