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数据库视频
随风云25元/月 ，德阳高防云服务器 2核2G 10M 75元/月 内蒙古三线BGP服务器 2核2G 5M
公司介绍成都随风云科技有限公司成立于2021年，是国内领先的互联网业务平台服务提供商。公司专注为用户提供低价高性能云计算产品，致力于云计算应用的易用性开发，并引导云计算在国内普及。目前公司研发以及运营云服务基础设施服务平台（IaaS），面向全球客户提供基于云计算的IT解决方案与客户服务，拥有丰富的国内BGP、双线高防、香港等优质的IDC资源。公司一直秉承”以人为本、客户为尊、永续创新&...
buyvm美国大硬盘VPS，1Gbps带宽不限流量
buyvm正式对外开卖第四个数据中心“迈阿密”的块存储服务，和前面拉斯维加斯、纽约、卢森堡一样，依旧是每256G硬盘仅需1.25美元/月，最大支持10T硬盘。配合buyvm自己的VPS，1Gbps带宽、不限流量，在vps上挂载块存储之后就可以用来做数据备份、文件下载、刷BT等一系列工作。官方网站：https://buyvm.net支持信用卡、PayPal、支付宝付款，支付宝付款用的是加元汇率，貌似...
digital-vm：VPS低至$4/月，服务器$80/月，10Gbps超大带宽，不限流量，机房可选：日本新加坡美国英国西班牙荷兰挪威丹麦
digital-vm，这家注册在罗马尼亚的公司在国内应该有不少人比较熟悉了，主要提供VPS业务，最高10Gbps带宽，还不限制流量，而且还有日本、新加坡、美国洛杉矶、英国、西班牙、荷兰、挪威、丹麦这些可选数据中心。2020年，digital-vm新增了“独立服务器”业务，暂时只限“日本”、“新加坡”机房，最高也是支持10Gbps带宽... 官方网站：https://digital-vm.co...
oracle数据库视频为你推荐
-
蓝瘦香菇被抢注蓝瘦香菇下一句怎么接permissiondeniedpermission denied是什么意思啊？netlife熊猫烧香图片xyq.163.cbg.com梦幻西游藏宝阁rawtools佳能单反照相机的RAW、5.0M 是什么意思?巫正刚想在淘宝开一个类似于耐克、阿迪之类的店、需要多少钱、如何能够代理www.765.com下载小说地址www.sesehu.comwww.121gao.com 是谁的网站啊百度指数词什么是百度指数lcoc.topeagle solder stop mask top是什么层