Oralcle数据库备份恢复运维实践

1、Oralcle数据库备份恢复运维实践第一章备份恢复介绍第二章rman备份恢复数据泵的应用第三章目 录第一章：什么是备份 备份的相关术语 备份的概念备份的重要性第二章备份的手段RMAN介绍RMAN的使用RMAN的应用场景第三章 数据泵介绍 数据泵的使用数据泵相关参数介绍目标通过这门课，掌握如下知识:掌握基本的备份恢复概念熟悉简单的RMAN备份命令熟悉数据泵的应用第一章 ： 备份恢复介绍归档模式 和非归档01一致备份 或者非一 致备份03冷备份热 备份04物理备份 和逻辑备 份05完整或者 部分数据 库备份02重要的备份术语Cold Backup(冷备份)主要指在关闭数据库的状态下 进行的数据库完

2、全备份，备份内容包括所有数据 文件、控制文件、联机日志文件、ini文件。Hot Backup(热备份) 指在数据库处于运行状态下， 对数据文件和控制文件进行备份，要使用热备份 必须将数据库运行在(Archive Log)归档方式下。Export(逻辑备份) 是利用SQL语言从数据库中抽取 数据并存于二进制文件的过程。归档和非归档ObjectivesAfter completing this you should be able to do the following:Describe the basics of database backup, restore and recovery.Lis

3、t the types of failure that may occur in an Oracle Database.Identify the importance of checkpoints, redo log files, and archived log files.Configure ARCHIVELOG mode.Backup and Recovery IssuesThe administrators duty is to:Protect the database from failure wherever possible.Increase the Mean-Time-Betw

4、een-Failures (MTBF). Decrease the Mean-Time-To-Recover (MTTR).Minimize the loss of data.Categories of FailuresFailures can generally be divided into the following categories:User ErrorsMedia Failurethe otherUser ErrorsTypical CausesPossible SolutionsDelete * from table where ;Flashback query:select

5、* from emp as of timestamp to_timestamp(2008- 09-02 08:00:00,YYYY-MM-DDHH:MI:SS) where last_name=TROY;Drop table flashback table cube_scope to before drop rename to cube_scope_oldMedia FailureTypical CausesPossible SolutionsFailure of disk drive1.Restore the affected file from backup.2.If necessary,

6、 inform the database of a new file location.Failure of disk controller3.If necessary, recover the file by applying redo information.Deletion or corruption of database filethe other reasonTypical CausesPossible SolutionsPower outageRestart the instance using the “startup” command. Recovery from insta

7、nce failure is automatic including rolling forward changes in the redo logs and then rolling back any uncommitted transactions.Investigate causes of failure using the alert log, trace files, and Enterprise Manager.(remote disaster recoveryHardware failureFailure of one of the background processes;OR

8、A-600Adventure disasters;Instance RecoveryInstance or crash recovery:Is caused by attempts to open a database whose files were not synchronized on shutdownIs automaticUses information stored in redo log groups to synchronize filesInvolves two distinct operationsRolling forward: Data files are restor

9、ed to their state before the instance failed.Rolling back: Changes made but not committed are returned to their original state.Data files out- of-syncRoll forward (redo)Committed and non-committed data in filesRoll back (undo)Committed data in filesDatabaseSGAInstanceBa1c4k6g.r5ound ProcessesData Fi

10、leSCN: 99Data FileSCN: 129Data FileSCN: 140Control FileSCN: 143Control FileSCN: 143Redo Log GroupSCN 74-101Redo Log GroupSCN 102-143Phases of Instance RecoveryTuning Instance RecoveryDuring instance recovery the transactions between the checkpoint position and end of redo log must be applied to the

11、data files.Tune instance recovery by controlling thedifferencebetweenthecheckpointpositionandend of redolog.End of redo logTransactionsCheckpoint positionInstance recoveryConfiguring for RecoverabilityTo configure your database for maximum recoverability:Schedule regular backups Multiplex control fi

12、les Multiplex redo log groupsRetain archived copies of redo logsControl FilesProtect against database failure by multiplexing control files.At least two copies (Oracle suggests three)Each copy on a separate diskAt least one copy on a separate disk controllerControl FilesRedo Log FilesMultiplexing re

13、do log groups to protect against media failure and loss of data.At least two members (files) per group Each member on a separate disk driveEach member on a separate disk controller Redo logs heavily influence performanceGroup 1Group 2Group 3Member 1Member 2Member 1Member 2Member 1Member 2Disk 1Disk

14、2Archived Log FilesTo preserve redo information, create archived copies of redo log files.Specify archived log file naming convention. Specify one or more locations to archive logs to. Switch the database to ARCHIVELOG mode.Online Redo Log FilesArchived log filesARCHIVELOG Mode调整归档模式 shutdown immedi

15、ate; startup mount;alter database archivelog; alter database open; archive log list;修改归档路径alter system set log_archive_dest_1=location=/app/oracle/data;%t_%s_%r.dbf%t: 重做线程编号. %s: 日志序列号: %r归档名称 log_archive_formatRESETLOGS的ID值SummaryIn this lesson you should have learned how to:Describe the basics of

16、 database backup, restore and recovery List the types of failure that may occur in an Oracle Database Identify the importance of checkpoints, redo log files, andarchived log filesConfigure ARCHIVELOG modeDescribe ways to tune instance recovery第二章 RMAN备份ObjectivesAfter completing this lesson you shou

17、ld be able to do the following:Create consistent database backupsBack up your database without shutting it downAutomate database backupsRMAN界面备份全库 /表空间/数据文件/控制文件/spfile backup database;backup tablespace ; backup datafile #; backup controfile; backup spfile ;rman常用的语法介绍：log /home/oracle/backfull-date

18、 +%Y%m%d-%H%M.logoraclebys001 $ cat fullback.sh #!/bin/shsource /home/oracle/.bash_profile/u01/app/oracle/product/11.2.0/dbhome_1/bin/rman select * from dba_datapump_jobsexpdp / as sysdba attach=查询到的JOB_NAME impdp / as sysdba attach=查询到的JOB_NAME 交互模式下常见命令：stop_job start_job status kill停止，停止后可以再启动。 启动查看状态kill，kill后无法再启动。如何查看导出导入的进度 v$session_lomgopstotalwork：显示作业的总估算量（MB）为单位 sofar：显示作业中迄今为止移动的字节 opname:作业的名字TIME_REMAINING：还需要的时间(预估）# 全库逻辑导出 删除两天