Making UserManaged Backups.docx

Making UserManaged Backups.docx

《Making UserManaged Backups.docx》由会员分享，可在线阅读，更多相关《Making UserManaged Backups.docx（34页珍藏版）》请在冰豆网上搜索。

MakingUserManagedBackups

MakingUser-ManagedBackups

IfyoudonotuseRecoveryManager（RMAN）,thenyoucanmakebackupsofyourdatabasefileswithuser-managedmethods.

Thischaptercontainsthefollowingsections:

∙QueryingV$ViewstoObtainBackupInformation

∙MakingUser-ManagedBackupsoftheWholeDatabase

∙MakingUser-ManagedBackupsofOfflineTablespacesandDatafiles

∙MakingUser-ManagedBackupsofOnlineTablespacesandDatafiles

∙MakingUser-ManagedBackupsoftheControlFile

∙MakingUser-ManagedBackupsofArchivedRedoLogs

∙MakingUser-ManagedBackupsinSUSPENDMode

∙MakingUser-ManagedBackupstoRawDevices

∙VerifyingUser-ManagedBackups

∙MakingLogicalBackupswithOracleExportUtilities

∙MakingUser-ManagedBackupsofMiscellaneousOracleFiles

∙KeepingRecordsofCurrentandBackupDatabaseFiles

QueryingV$ViewstoObtainBackupInformation

Beforemakingabackup,youmustidentifyallthefilesinyourdatabaseanddecidewhattobackup.SeveralV$viewscanprovidethenecessaryinformation.

ListingDatabaseFilesBeforeaBackup

UseV$DATAFILE,V$LOGFILEandV$CONTROLFILEtoidentifythedatafiles,logfilesandcontrolfilesforyourdatabase.ThissameprocedureworkswhetheryounamedthesefilesmanuallyorallowedOracleManagedFilestonamethem.

Tolistdatafiles,onlineredologs,andcontrolfiles:

1.StartSQL*PlusandqueryV$DATAFILEtoobtainalistofdatafiles.Forexample,enter:

2.SQL>SELECTNAMEFROMV$DATAFILE;

3.

YoucanalsojointheV$TABLESPACEandV$DATAFILEviewstoobtainalistingofdatafilesalongwiththeirassociatedtablespaces:

SELECTt.NAME"Tablespace",f.NAME"Datafile"

FROMV$TABLESPACEt,V$DATAFILEf

WHEREt.TS#=f.TS#

ORDERBYt.NAME;

4.ObtainthefilenamesofonlineredologfilesbyqueryingtheV$LOGFILEview.Forexample,issuethefollowingquery:

5.SQL>SELECTMEMBERFROMV$LOGFILE;

6.

7.ObtainthefilenamesofthecurrentcontrolfilesbyqueryingtheV$CONTROLFILEview.Forexample,issuethefollowingquery:

8.SQL>SELECTNAMEFROMV$CONTROLFILE;

9.

Notethatyouonlyneedtobackuponecopyofamultiplexedcontrolfile.

10.IfyouplantotakeacontrolfilebackupwiththeALTERDATABASEBACKUPCONTROLFILETO'filename'statement,thensavealistofalldatafilesandonlineredologfileswiththecontrolfilebackup.Becausethecurrentdatabasestructuremaynotmatchthedatabasestructureatthetimeagivencontrolfilebackupwascreated,savingalistoffilesrecordedinthebackupcontrolfilecanaidtherecoveryprocedure.

DeterminingDatafileStatusforOnlineTablespaceBackups

Tocheckwhetheradatafileispartofacurrentonlinetablespacebackup,querytheV$BACKUPview.

Thisviewisusefulonlyforuser-managedonlinetablespacebackups,becauseneitherRMANbackupsnorofflinetablespacebackupsrequirethedatafilesofatablespacetobeinbackupmode.

TheV$BACKUPviewismostusefulwhenthedatabaseisopen.Itisalsousefulimmediatelyafteraninstancefailurebecauseitshowsthebackupstatusofthefilesatthetimeofthefailure.Usethisinformationtodeterminewhetheryouhaveleftanytablespacesinbackupmode.

V$BACKUPisnotusefulifthecontrolfilecurrentlyinuseisarestoredbackuporanewcontrolfilecreatedafterthemediafailureoccurred.Arestoredorre-createdcontrolfiledoesnotcontaintheinformationthedatabaseneedstopopulateV$BACKUPaccurately.Also,ifyouhaverestoredabackupofafile,thisfile'sSTATUSinV$BACKUPreflectsthebackupstatusoftheolderversionofthefile,notthemostcurrentversion.Thus,thisviewcancontainmisleadingdataaboutrestoredfiles.

Forexample,thefollowingquerydisplayswhichdatafilesarecurrentlyincludedinatablespacethathasbeenplacedinbackupmode:

SELECTt.nameAS"TB_NAME",d.file#as"DF#",d.nameAS"DF_NAME",b.status

FROMV$DATAFILEd,V$TABLESPACEt,V$BACKUPb

WHEREd.TS#=t.TS#

ANDb.FILE#=d.FILE#

ANDb.STATUS='ACTIVE'

/

ThefollowingsampleoutputshowsthatthetoolsanduserstablespacescurrentlyhaveACTIVEstatus:

TB_NAMEDF#DF_NAMESTATUS

----------------------------------------------------------------------

TOOLS7/oracle/oradata/trgt/tools01.dbfACTIVE

USERS8/oracle/oradata/trgt/users01.dbfACTIVE

IntheSTATUScolumn,NOTACTIVEindicatesthatthefileisnotcurrentlyinbackupmode（thatis,youhavenotexecutedtheALTERTABLESPACE...BEGINBACKUPorALTERDATABASEBEGINBACKUPstatement）,whereasACTIVEindicatesthatthefileiscurrentlyinbackupmode.

MakingUser-ManagedBackupsoftheWholeDatabase

YoucanmakeawholedatabasebackupofallfilesinadatabaseafterthedatabasehasbeenshutdownwiththeNORMAL,IMMEDIATE,orTRANSACTIONALoptions.AwholedatabasebackuptakenwhilethedatabaseisopenorafteraninstancefailureorSHUTDOWNABORTisinconsistent.Insuchcases,thefilesareinconsistentwithrespecttothecheckpointSCN.

YoucanmakeawholedatabasebackupifadatabaseisoperatingineitherARCHIVELOGorNOARCHIVELOGmode.IfyourunthedatabaseinNOARCHIVELOGmode,however,thebackupmustbeconsistent;thatis,youmustshutdownthedatabasecleanlybeforethebackup.

ThesetofbackupfilesthatresultsfromaconsistentwholedatabasebackupisconsistentbecauseallfilesarecheckpointedtothesameSCN.Youcanrestoretheconsistentdatabasebackupwithoutfurtherrecovery.Afterrestoringthebackupfiles,youcanperformadditionalrecoverystepstorecoverthedatabasetoamorecurrenttimeifthedatabaseisoperatedinARCHIVELOGmode.Also,youcantakeinconsistentwholedatabasebackupsifyourdatabaseisinARCHIVELOGmode.

Controlfilesplayacrucialroleindatabaserestoreandrecovery.FordatabasesrunninginARCHIVELOGmode,OracleCorporationrecommendsthatyoubackupcontrolfileswiththeALTERDATABASEBACKUPCONTROLFILETO'filename'statement.

SeeAlso:

"MakingUser-ManagedBackupsoftheControlFile"formoreinformationaboutbackingupcontrolfiles

MakingConsistentWholeDatabaseBackups

Thissectiondescribeshowtobackupthedatabasewithanoperatingsystemutility.

Tomakeaconsistentwholedatabasebackup:

1.Ifthedatabaseisopen,useSQL*PlustoshutdownthedatabasewiththeNORMAL,IMMEDIATE,orTRANSACTIONALoptions.

2.UseanoperatingsystemutilitytomakebackupsofalldatafilesaswellasallcontrolfilesspecifiedbytheCONTROL_FILESparameteroftheinitializationparameterfile.Also,backuptheinitializationparameterfileandotherOracleproductinitializationfiles.Tofindthesefiles,doasearchfor*.orastartinginyourOraclehomedirectoryandrecursivelysearchallofitssubdirectories.

Forexample,youcanbackupthedatafiles,controlfilesandarchivedlogsto/disk2/backupasfollows:

%cp$ORACLE_HOME/oradata/trgt/*.dbf/disk2/backup

%cp$ORACLE_HOME/oradata/trgt/arch/*/disk2/backup/arch

3.Restartthedatabase.Forexample,enter:

4.SQL>STARTUP

SeeAlso:

OracleDatabaseAdministrator'sGuideformoreinformationonstartingupandshuttingdownadatabase

MakingUser-ManagedBackupsofOfflineTablespacesandDatafiles

Youcanbackupallorsomeofthedatafilesofanindividualtablespacewhilethetablespaceisoffline.Allothertablespacesofthedatabasecanremainopenandavailableforsystemwideuse.YoumusthavetheDBAprivilegeorhavetheMANAGETABLESPACEsystemprivilegetotaketablespacesofflineandonline.

Notethefollowingguidelineswhenbackingupofflinetablespaces:

∙YoucannotofflinetheSYSTEMtablespaceoratablespacewithactiverollbacksegments.Thefollowingprocedurecannotbeusedforsuchtablespaces.

∙AssumethatatableisintablespacePrimaryanditsindexisintablespaceIndex.TakingtablespaceIndexofflinewhileleavingtablespacePrimaryonlinecancauseerrorswhenDMLisissuedagainsttheindexedtableslocatedinPrimary.TheproblemonlymanifestswhentheaccessmethodchosenbytheoptimizerneedstoaccesstheindexesintheIndextablespace.

Tobackupofflinetablespaces:

1.Beforebeginningabackupofatablespace,identifythetablespace'sdatafilesbyqueryingtheDBA_DATA_FILESview.Forexample,assumethatyouwanttobackuptheuserstablespace.EnterthefollowinginSQL*Plus:

2.SELECTTABLESPACE_NAME,FILE_NAME

3.FROMSYS.DBA_DATA_FILES

4.WHERETABLESPACE_NAME='USERS';

5.

6.TABLESPACE_NAMEFILE_NAME

7.---------------------------------------------------------------

8.USERS/oracle/oradata/trgt/users01.dbf

9.

Inthisexample,/oracle/oradata/trgt/users01.dbfisafullyspecifiedfilenamecorrespondingtothedatafileintheuserstablespace.

10.Takethetablespaceofflineusingnormalpriorityifpossiblebecauseitguaranteesthatyoucansubsequentlybringthetablespaceonlinewithouthavingtorecoverit.Forexample:

11.SQL>ALTERTABLESPACEusersOFFLINENORMAL;

12.

13.Backuptheofflinedatafiles.Forexample:

14.%cp/oracle/oradata/trgt/users01.dbf/d2/users01_`date"+%m_%d_%y"`.dbf

15.

16.Bringthetablespaceonline.Forexample:

17.ALTERTABLESPACEusersONLINE;

Note:

Ifyoutookthetablespaceofflineusingtemporaryorimmediatepriority,thenyoucannotbringthetablespaceonlineunlessyouperformtablespacerecovery.

18.Archivetheunarchivedredologssothattheredorequiredtorecoverthetablespacebackupisarchived.Forexample,enter:

19.ALTERSYSTEMARCHIVELOGCURRENT;

MakingUser-ManagedBackupsofOnlineTablespacesandDatafiles

Youcanbackupalloronlyspecificdatafilesofanonlinetablespacewhilethedatabaseisopen.Theprocedurediffersdependingonwhethertheonlinetablespaceisread/writeorread-only.

Note:

Youshouldnotbackuptemporarytablespaces.