[OCP study & practice]1.Backup / Recovery / Flashback

OCP 10G学习笔记加实验个人心得，本人原创，转载请注明出处。文章尽量用英语完成，一方面方便复制粘贴英文资料，另一方面练习一下English writing。

1.Prepare

1.1 Confiure archive log mode

SQL>startup mount;

SQL>alter database archivelog;

SQL>alter database open;

SQL>archive log list;

1.2 Enable flashback

SQL>startup mount;

SQL>alter database flashback on;

SQL>show parameter db_recovery;

1.3 Configure RMAN enviorment

Steps for configuring RMAN

1.3.1.Determine the repository location:control file or recovery catalog

---We use recovery catalog for example in this practice.

1.3.2.Define parameter that affect RMAN

---CONTROL_FILE_RECORD_KEEP_TIME(for using controlfile)

---DB_RECOVERY_XXX(for using Flash Recovery Area)

1.3.3.Creating recovery catalog database and rman user

SQL>create tablespace rmantbs  datafile 'F:\Oracle\product\10.2.0\oradata\oratest\rmantbs01.dbf'  size 20M;

SQL>create user rman identified by rman default tablespace rmantbs quota unlimited on rmantbs;

SQL>grant recovery_catalog_owner to rman;

SQL>select * from dba_sys_privs where grantee='RECOVERY_CATALOG_OWNER';

1.3.4.Starting RMAN and connect to the target,optionally,the recovery catalog DB

OS>rman catalog rman/rman

RMAN>create catalog tablespace rmantbs;

RMAN>connect target system/password@oratest;

RMAN>register database;

OR

OS>rman catalog rman/rman(rman db) target user/pwd@db(target DB)

1.3.5.Configure persistent setting

RMAN>show all;

...............

1.4 Multiplexing redo log file

At least 2 member per group / Each copy on a separate disk

SQL>select * from v$logfile;

SQL>select * from v$log;

SQL>alter database add logfile group 4 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORATEST\REDO04.LOG' size 50m;

SQL>alter database add logfile member  'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORATEST\REDO04a.LOG'  to group 4; 

SQL>alter system switch logfile;

1.5 Multiplexing control file

At least 2 control files(oracle recommend 3 control files)

Each copy on a separate disk

configure multiple control files with modifing parameter in spfile

SQL>show parameter control;

SQL>select * from v$controlfile;

SQL> alter system set control_files = 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORATEST\CONTROL01.CTL'  ,

2  'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORATEST\CONTROL02.CTL',

3   'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORATEST\CONTROL03.CTL'

4  scope = spfile; (ISSYS_MODIFIABLE=false,need for restart)

then copy files to the correct path and restart DB.

2.Backup

2.1User managed backup(Cold/hot backup)(052-P428)

2.1.1 Consistent backup

step.1 shutdown clearly(immediate/transactional/normal)

step.2 copy controlfile/datafile/online redo log file

2.1.2 Inconsistent backup

step.1 backup datafile

SQL>alter system switch logfile;

SQL>alter system archive log all;

SQL>alter tablespace system begin backup;

SQL>!cp datafile to destination

SQL>alter tablespace system end backup; then other tablespace

step.2 backup control file

#backup as binary file,this makes an image copy of current control  file.

SQL>alter database backup controlfile to 'F:\ORACLE\BACKUP\control.ctl' reuse;

#backup as text file(a trace file),

SQL>alter database backup controlfile to trace as 'filename';

step.3 backup archive log and spfile

 

2.2 RMAN backup

2.2.1 Consistent backup

this kind of backup must run in mount mode.

Eg.

run{

shutdown immediate;

startup mount;

allocate channel d1 type disk;

backup as backupset(compressed backupset/image copy) database;

format 'F:\ORACLE\BACKUP\Offline_Full_Whole.bus';

alter database open;

}

2.2.2 Inconsistent backup

Eg.

run{

allocate channel t1/t2 type sbt_tape...

backup as compressed backupset filesperset 4 database;
backup as compressed backupset plus archivelog all delete all input;

}

for more detail refer to Chap 5.Advance topic

3.Recovery

3.1User managed recovery(043-chap.5)

3.2Noncritical losses(043-chap.4)

3.3RMAN recovery

 

4.Flashback

4.1Flashback drop

4.2Flashback table

4.3fFlashback database

4.4Flashback query

 

5.Advance Topic

5.1Auto backup with rman script&crontab / manage rman backup(p437)

5.2Auto backup with user managed backup

 

Tips

1.redo log status / archive log details

2.why archiving log files?

3.Difference between Flashback database & Incomplete recovery

 

Terminology

1.MTTR(Mean-Time-To-Recover)

2.MTBF(Mean-Time-Between-Failures)

3.Backup Concepts

3.1Backup Modes

offline=consistant=cold backup

online=inconsistant=hot backup

3.2Backup Strategy(Whole/Partial)

3.3Backup Type(Full/Incremental)

 

参考资料：

1.Oracle Database 10g-Administration Workshop I

2.Oracle Database 10g-Administration Workshop II

3.http://hi.baidu.com/dba_jungle/blog/item/3dea1e8ae3103dbf0f24441c.html (ORACLE热备份脚本)

4.http://blog.csdn.net/tianlesoftware/article/details/5800172 (RMAN备份数据块一致性讨论)

5.OCA认证考试指南(IZ0-052)  清华大学出版社

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/11751028/viewspace-712453/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/11751028/viewspace-712453/