OCM实验-备份恢复-控制文件
来源：互联网 发布：网络报案中心我要报案 编辑：程序博客网 时间：2024/05/21 17:04
--查看环境信息SQL> select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionPL/SQL Release 11.2.0.1.0 - ProductionCORE 11.2.0.1.0 ProductionTNS for Linux: Version 11.2.0.1.0 - ProductionNLSRTL Version 11.2.0.1.0 - ProductionSQL> show parameter control_files;NAME TYPE VALUE------------------------------------ ----------- ------------------------------control_files string /u01/app/oracle/oradata/vict/c ontrol01.ctl, /home/oracle/fla sh_recovery_area/vict/control0 2.ctl
--模拟场景一：非归档下，删除所有控制文件，数据库未关闭。
SQL> select log_mode from v$database;LOG_MODE------------NOARCHIVELOG--手工方式备份控制文件 重建控制时文件关键在于各个数据文件的位置SQL> alter database backup controlfile to trace;Database altered.SQL> col value for a100;SQL> select value from v$diag_info where name ='Default Trace File';VALUE--------------------------------------------------------------------------------/u01/app/oracle/diag/diag/rdbms/vict/vict/trace/vict_ora_2745.trc--查找当前控制文件文件描述信息SQL> !ps -ef|grep ckpt|grep -v greporacle 2725 1 0 23:00 ? 00:00:00 ora_ckpt_vict--查看文件句柄信息[root@vict selinux]# cd /proc/2725/fd[root@vict fd]# ls -ltotal 0lr-x------. 1 oracle oinstall 64 Jul 25 23:08 0 -> /dev/nulll-wx------. 1 oracle oinstall 64 Jul 25 23:08 1 -> /dev/nulll-wx------. 1 oracle oinstall 64 Jul 25 23:08 10 -> /u01/app/oracle/diag/diag/rdbms/vict/vict/trace/vict_ora_2697.trcl-wx------. 1 oracle oinstall 64 Jul 25 23:08 11 -> /u01/app/oracle/diag/diag/rdbms/vict/vict/trace/vict_ora_2697.trmlrwx------. 1 oracle oinstall 64 Jul 25 23:08 12 -> /u01/app/oracle/product/11.2/dbs/hc_vict.datlr-x------. 1 oracle oinstall 64 Jul 25 23:08 13 -> /dev/zerolr-x------. 1 oracle oinstall 64 Jul 25 23:08 14 -> /proc/2725/fdlr-x------. 1 oracle oinstall 64 Jul 25 23:08 15 -> /dev/zerolrwx------. 1 oracle oinstall 64 Jul 25 23:08 16 -> /u01/app/oracle/product/11.2/dbs/lkVICTlrwx------. 1 oracle oinstall 64 Jul 25 23:08 17 -> <span style="color:#ff0000;">/u01/app/oracle/oradata/vict/control01.ctl</span>lrwx------. 1 oracle oinstall 64 Jul 25 23:08 18 -> <span style="color:#ff0000;">/home/oracle/flash_recovery_area/vict/control02.ctl</span>l-wx------. 1 oracle oinstall 64 Jul 25 23:08 2 -> /dev/nulll-wx------. 1 oracle oinstall 64 Jul 25 23:08 3 -> /u01/app/oracle/product/11.2/rdbms/log/vict_ora_2697.trclr-x------. 1 oracle oinstall 64 Jul 25 23:08 4 -> /dev/nulllr-x------. 1 oracle oinstall 64 Jul 25 23:08 5 -> /dev/nulllr-x------. 1 oracle oinstall 64 Jul 25 23:08 6 -> /dev/nulllrwx------. 1 oracle oinstall 64 Jul 25 23:08 7 -> /u01/app/oracle/product/11.2/dbs/hc_vict.datlrwx------. 1 oracle oinstall 64 Jul 25 23:08 8 -> /u01/app/oracle/product/11.2/dbs/lkinstvict (deleted)lr-x------. 1 oracle oinstall 64 Jul 25 23:08 9 -> /proc/2725/fd--数据库正常开启情况下，删除控制文件。SQL> !rm -rf /u01/app/oracle/oradata/vict/control01.ctlSQL> !rm -rf /home/oracle/flash_recovery_area/vict/control02.ctl--数据库没有第一时间报错SQL> select * from dual;D-XSQL> select open_mode from v$database;OPEN_MODE--------------------READ WRITE--手工做检查点 也没有报错SQL> alter system checkpoint;System altered.--在未关闭数据库的情况下，手工将控制文件拷贝回来[oracle@vict flash_recovery_area]$ cd /proc/2725/fd[oracle@vict fd]$ ls -ltotal 0lr-x------. 1 oracle oinstall 64 Jul 25 23:08 0 -> /dev/nulll-wx------. 1 oracle oinstall 64 Jul 25 23:08 1 -> /dev/nulll-wx------. 1 oracle oinstall 64 Jul 25 23:08 10 -> /u01/app/oracle/diag/diag/rdbms/vict/vict/trace/vict_ora_2697.trcl-wx------. 1 oracle oinstall 64 Jul 25 23:08 11 -> /u01/app/oracle/diag/diag/rdbms/vict/vict/trace/vict_ora_2697.trmlrwx------. 1 oracle oinstall 64 Jul 25 23:08 12 -> /u01/app/oracle/product/11.2/dbs/hc_vict.datlr-x------. 1 oracle oinstall 64 Jul 25 23:08 13 -> /dev/zerolr-x------. 1 oracle oinstall 64 Jul 25 23:08 14 -> /proc/2725/fdlr-x------. 1 oracle oinstall 64 Jul 25 23:08 15 -> /dev/zerolrwx------. 1 oracle oinstall 64 Jul 25 23:08 16 -> /u01/app/oracle/product/11.2/dbs/lkVICTlrwx------. 1 oracle oinstall 64 Jul 25 23:08 17 -><span style="color:#ff0000;"> /u01/app/oracle/oradata/vict/control01.ctl (deleted)</span>lrwx------. 1 oracle oinstall 64 Jul 25 23:08 18 -> <span style="color:#ff0000;">/home/oracle/flash_recovery_area/vict/control02.ctl (deleted)</span>lrwx------. 1 oracle oinstall 64 Jul 25 23:15 19 -> /u01/app/oracle/oradata/vict/system01.dbfl-wx------. 1 oracle oinstall 64 Jul 25 23:08 2 -> /dev/nulllrwx------. 1 oracle oinstall 64 Jul 25 23:15 20 -> /u01/app/oracle/oradata/vict/sysaux01.dbflrwx------. 1 oracle oinstall 64 Jul 25 23:15 21 -> /u01/app/oracle/oradata/vict/undotbs01.dbflrwx------. 1 oracle oinstall 64 Jul 25 23:15 22 -> /u01/app/oracle/oradata/vict/users01.dbfl-wx------. 1 oracle oinstall 64 Jul 25 23:08 3 -> /u01/app/oracle/product/11.2/rdbms/log/vict_ora_2697.trclr-x------. 1 oracle oinstall 64 Jul 25 23:08 4 -> /dev/nulllr-x------. 1 oracle oinstall 64 Jul 25 23:08 5 -> /dev/nulllr-x------. 1 oracle oinstall 64 Jul 25 23:08 6 -> /dev/nulllrwx------. 1 oracle oinstall 64 Jul 25 23:08 7 -> /u01/app/oracle/product/11.2/dbs/hc_vict.datlrwx------. 1 oracle oinstall 64 Jul 25 23:08 8 -> /u01/app/oracle/product/11.2/dbs/lkinstvict (deleted)lr-x------. 1 oracle oinstall 64 Jul 25 23:08 9 -> /proc/2725/fd[oracle@vict fd]$ cp 17 /u01/app/oracle/oradata/vict/control01.ctl[oracle@vict fd]$ cp 18 /home/oracle/flash_recovery_area/vict/control02.ctl-rw-r-----. 1 oracle oinstall 9748480 Jul 25 23:15 /u01/app/oracle/oradata/vict/control01.ctl[oracle@vict fd]$ ls -l /home/oracle/flash_recovery_area/vict/control02.ctl-rw-r-----. 1 oracle oinstall 9748480 Jul 25 23:16 /home/oracle/flash_recovery_area/vict/control02.ctl-- 关闭数据库，重新打开，校验是否成功。SQL> shutdown immediate;Database closed.Error while trying to retrieve text for error ORA-03113separator not found in message(3144)separator not found in message(3142)SQL> shutdown abort;Error while trying to retrieve text for error ORA-24324Error while trying to retrieve text for error ORA-01041SQL> ho ps -ef|grep smonoracle 2934 2696 0 23:17 pts/2 00:00:00 /bin/bash -c ps -ef|grep smonoracle 2936 2934 0 23:17 pts/2 00:00:00 grep smonSQL> exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options[oracle@vict ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.1.0 Production on Fri Jul 25 23:17:56 2014Copyright (c) 1982, 2009, Oracle. All rights reserved.Connected to an idle instance.SQL> startup;ORACLE instance started.Total System Global Area 313860096 bytesFixed Size 1336232 bytesVariable Size 104860760 bytesDatabase Buffers 201326592 bytesRedo Buffers 6336512 bytesDatabase mounted.Database opened.SQL> select open_mode from v$database;OPEN_MODE--------------------READ WRITE--数据库正常打开----------------
--模拟场景二：非归档下，删除所有控制文件，数据库已关闭。
SQL> select log_mode from v$database;LOG_MODE------------NOARCHIVELOGSQL> show parameter control_f;NAME TYPE VALUE------------------------------------ ----------- ------------------------------control_file_record_keep_time integer 7control_files string /u01/app/oracle/oradata/vict/c ontrol01.ctl, /home/oracle/fla sh_recovery_area/vict/control0 2.ctlSQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.--删除所有控制文件，此时无任何备份。SQL> !rm -rf /u01/app/oracle/oradata/vict/control01.ctl SQL> !rm -rf /home/oracle/flash_recovery_area/vict/control02.ctlSQL> ! ls /u01/app/oracle/oradata/vict/control01.ctl ls: cannot access /u01/app/oracle/oradata/vict/control01.ctl: No such file or directorySQL> ! ls /home/oracle/flash_recovery_area/vict/control02.ctlls: cannot access /home/oracle/flash_recovery_area/vict/control02.ctl: No such file or directory--启动数据库报错。SQL> startup;ORACLE instance started.Total System Global Area 313860096 bytesFixed Size 1336232 bytesVariable Size 104860760 bytesDatabase Buffers 201326592 bytesRedo Buffers 6336512 bytesORA-00205: Message 205 not found; No message file for product=RDBMS,facility=ORA; arguments: [0] [] [0]--查看告警信息数据库mount阶段报错。715 Fri Jul 25 23:25:25 2014716 ALTER DATABASE MOUNT717 ORA-00210: Message 210 not found; No message file for product=RDBMS, facility=ORA718 ORA-00202: Message 202 not found; No message file for product=RDBMS, facility=ORA; arguments: [/home/oracle/flash_recovery_area/ vict/control02.ctl]719 ORA-27037: Message 27037 not found; No message file for product=RDBMS, facility=ORA720 Linux Error: 2: No such file or directory721 Additional information: 3722 ORA-00210: Message 210 not found; No message file for product=RDBMS, facility=ORA723 ORA-00202: Message 202 not found; No message file for product=RDBMS, facility=ORA; arguments: [/u01/app/oracle/oradata/vict/cont rol01.ctl]724 ORA-27037: Message 27037 not found; No message file for product=RDBMS, facility=ORA725 Linux Error: 2: No such file or directory726 Additional information: 3727 ORA-205 signalled during: ALTER DATABASE MOUNT...728 Fri Jul 25 23:25:26 2014729 Checker run found 2 new persistent data failures--根据 control_files 参数发现，控制文件不存在，重建控制文件。SQL> shutdown abort;ORACLE instance shut down.SQL> startup nomount;ORACLE instance started.Total System Global Area 313860096 bytesFixed Size 1336232 bytesVariable Size 104860760 bytesDatabase Buffers 201326592 bytesRedo Buffers 6336512 bytes--采用noresetlogs版本 --重建控制文件关键要设置好每个数据文件的位置SQL> CREATE CONTROLFILE REUSE DATABASE "VICT" NORESETLOGS NOARCHIVELOG 2 MAXLOGFILES 16 3 MAXLOGMEMBERS 3 4 MAXDATAFILES 100 5 MAXINSTANCES 8 6 MAXLOGHISTORY 292 7 LOGFILE 8 GROUP 1 '/u01/app/oracle/oradata/vict/redo01.log' SIZE 50M BLOCKSIZE 512, 9 GROUP 2 '/u01/app/oracle/oradata/vict/redo02.log' SIZE 50M BLOCKSIZE 512, 10 GROUP 3 '/u01/app/oracle/oradata/vict/redo03.log' SIZE 50M BLOCKSIZE 512 11 -- STANDBY LOGFILE 12 DATAFILE 13 '/u01/app/oracle/oradata/vict/system01.dbf', 14 '/u01/app/oracle/oradata/vict/sysaux01.dbf', 15 '/u01/app/oracle/oradata/vict/undotbs01.dbf', 16 '/u01/app/oracle/oradata/vict/users01.dbf' 17 CHARACTER SET AL32UTF8 18 ;Control file created.SQL> select open_mode from v$database;OPEN_MODE--------------------MOUNTEDSQL> alter database open;Database altered.SQL> select open_mode from v$database;OPEN_MODE--------------------READ WRITESQL> ! ls /u01/app/oracle/oradata/vict/control01.ctl /u01/app/oracle/oradata/vict/control01.ctlSQL> ! ls /home/oracle/flash_recovery_area/vict/control02.ctl/home/oracle/flash_recovery_area/vict/control02.ctl--数据库正常启动--检查文件信息，临时文件是需要手工添加的SQL> set linesize 1000;SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIE---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ -------- 1 1 7 52428800 512 1 NO CURRENT 817590 25-JUL-14 2.8147E+14 2 1 5 52428800 512 1 NO INACTIVE 793737 25-JUL-14 817000 25-JUL-4 3 1 6 52428800 512 1 NO INACTIVE 817000 25-JUL-14 817590 25-JUL-4SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER _---------- ------- ------- --------------------------------------------------------------------------------------------------------- 3 STALE ONLINE /u01/app/oracle/oradata/vict/redo03.log O 2 STALE ONLINE /u01/app/oracle/oradata/vict/redo02.log O 1 ONLINE /u01/app/oracle/oradata/vict/redo01.log OSQL> select * from v$dbfile; FILE# NAME---------- ------------------------------------------------------------------------------------------------------------------------- 4 /u01/app/oracle/oradata/vict/users01.dbf 3 /u01/app/oracle/oradata/vict/undotbs01.dbf 2 /u01/app/oracle/oradata/vict/sysaux01.dbf 1 /u01/app/oracle/oradata/vict/system01.dbfSQL> select * from dba_temp_files;no rows selectedSQL> select name from v$tablespace;NAME------------------------------SYSTEMSYSAUXUNDOTBS1USERSTEMPSQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/vict/temp01.dbf';Tablespace altered.
-------------------------------------------
--模拟场景三：归档模式下有备份，删除所有控制文件。
SQL> alter database archivelog;Database altered.SQL> archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination USE_DB_RECOVERY_FILE_DESTOldest online log sequence 5Next log sequence to archive 7Current log sequence 7--做好备份RMAN> backup database include current controlfile;Starting backup at 25-JUL-14using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=20 device type=DISKchannel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setinput datafile file number=00001 name=/u01/app/oracle/oradata/vict/system01.dbfinput datafile file number=00002 name=/u01/app/oracle/oradata/vict/sysaux01.dbfinput datafile file number=00003 name=/u01/app/oracle/oradata/vict/undotbs01.dbfinput datafile file number=00004 name=/u01/app/oracle/oradata/vict/users01.dbfchannel ORA_DISK_1: starting piece 1 at 25-JUL-14channel ORA_DISK_1: finished piece 1 at 25-JUL-14piece handle=/home/oracle/flash_recovery_area/VICT/backupset/2014_07_25/o1_mf_nnndf_TAG20140725T234120_9x6mdjwl_.bkp tag=TAG2014072Echannel ORA_DISK_1: backup set complete, elapsed time: 00:01:07channel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setincluding current control file in backup setincluding current SPFILE in backup setchannel ORA_DISK_1: starting piece 1 at 25-JUL-14channel ORA_DISK_1: finished piece 1 at 25-JUL-14piece handle=/home/oracle/flash_recovery_area/VICT/backupset/2014_07_25/o1_mf_ncsnf_TAG20140725T234120_9x6mgnvx_.bkp tag=TAG2014072Echannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01Finished backup at 25-JUL-14RMAN> list backup of database;List of Backup Sets===================BS Key Type LV Size Device Type Elapsed Time Completion Time------- ---- -- ---------- ----------- ------------ ---------------1 Full 1007.97M DISK 00:00:59 25-JUL-14 BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20140725T234120 Piece Name: /home/oracle/flash_recovery_area/VICT/backupset/2014_07_25/o1_mf_nnndf_TAG20140725T234120_9x6mdjwl_.bkp List of Datafiles in backup set 1 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 1 Full 818053 25-JUL-14 /u01/app/oracle/oradata/vict/system01.dbf 2 Full 818053 25-JUL-14 /u01/app/oracle/oradata/vict/sysaux01.dbf 3 Full 818053 25-JUL-14 /u01/app/oracle/oradata/vict/undotbs01.dbf 4 Full 818053 25-JUL-14 /u01/app/oracle/oradata/vict/users01.dbf--开始删除文件SQL> show parameter controlNAME TYPE VALUE------------------------------------ ----------- ------------------------------control_file_record_keep_time integer 7control_files string /u01/app/oracle/oradata/vict/c ontrol01.ctl, /home/oracle/fla sh_recovery_area/vict/control0 2.ctlcontrol_management_pack_access string DIAGNOSTIC+TUNINGSQL> shutdown immediate;ORA-01109: Message 1109 not found; No message file for product=RDBMS, facility=ORADatabase dismounted.ORACLE instance shut down.SQL> !rm -rf /u01/app/oracle/oradata/vict/control01.ctlSQL> !rm -rf /home/oracle/flash_recovery_area/vict/control02.ctl--启动报错SQL> startup;ORACLE instance started.Total System Global Area 313860096 bytesFixed Size 1336232 bytesVariable Size 104860760 bytesDatabase Buffers 201326592 bytesRedo Buffers 6336512 bytesORA-00205: Message 205 not found; No message file for product=RDBMS, facility=ORA; arguments: [0] [] [0]--尝试恢复，此时推荐重建控制文件，本次测试restore recoverRMAN> restore controlfile from 2> '/home/oracle/flash_recovery_area/VICT/backupset/2014_07_25/o1_mf_ncsnf_TAG20140725T234120_9x6mgnvx_.bkp';Starting restore at 25-JUL-14using channel ORA_DISK_1channel ORA_DISK_1: restoring control filechannel ORA_DISK_1: restore complete, elapsed time: 00:00:03output file name=/u01/app/oracle/oradata/vict/control01.ctloutput file name=/home/oracle/flash_recovery_area/vict/control02.ctlFinished restore at 25-JUL-14RMAN> mount database;using target database control file instead of recovery catalogdatabase mountedRMAN> recover database;Starting recover at 26-JUL-14Starting implicit crosscheck backup at 26-JUL-14allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=18 device type=DISKCrosschecked 1 objectsFinished implicit crosscheck backup at 26-JUL-14Starting implicit crosscheck copy at 26-JUL-14using channel ORA_DISK_1Finished implicit crosscheck copy at 26-JUL-14searching for all files in the recovery areacataloging files...cataloging doneList of Cataloged Files=======================File Name: /home/oracle/flash_recovery_area/VICT/backupset/2014_07_25/o1_mf_ncsnf_TAG20140725T234120_9x6mgnvx_.bkpusing channel ORA_DISK_1starting media recoveryarchived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/oradata/vict/redo01.logarchived log file name=/u01/app/oracle/oradata/vict/redo01.log thread=1 sequence=7media recovery complete, elapsed time: 00:00:00Finished recover at 26-JUL-14SQL> select open_mode from v$database;OPEN_MODE--------------------MOUNTED--以resetlogs打开 重置了日志sequence，之后做好备份一次。也可以以noresetlogs重建控制文件,就无需重置日志。SQL> alter database open resetlogs;Database altered.SQL> set linesize 1000;SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIE---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ -------- 1 1 1 52428800 512 1 NO CURRENT 818055 26-JUL-14 2.8147E+14 2 1 0 52428800 512 1 YES UNUSED 0 0 3 1 0 52428800 512 1 YES UNUSED 0 0
0 0
- OCM实验-备份恢复-控制文件
- <<OCM实验选讲>> 第五课 数据备份恢复实验
- 控制文件恢复与备份
- ORACLE控制文件备份恢复 noresetlogs 恢复
- rman实验之归档模式有备份，正常关机丢失控制文件的恢复
- 控制文件的备份与恢复
- Oracle控制文件备份恢复 resetlogs方式
- oracle冷备份恢复重建控制文件
- 冷备份恢复控制文件报错
- 使用备份控制文件恢复数据库
- 控制文件的备份与恢复
- 控制文件多镜像及备份恢复
- oracle-控制文件备份和恢复
- 控制文件的备份和恢复
- 备份恢复——控制文件
- rman备份丢失控制文件恢复
- oracle备份之rman_恢复控制文件
- oracle控制文件备份和日志文件备份和恢复
- Spring 入门总结
- 滋肮腋懊蹦叶嘎铱棕瞥阂魄呐段感
- 八滓临罩口拓研庞嚼旁胰张畔谇此
- 拦战柏可概悼诶梁梁禄嫉碧堆贫阅
- 幢似匀未胤烙摆械剖呜计值冶撼压
- OCM实验-备份恢复-控制文件
- 当心甜食“中毒” 几个饮食控制血糖的秘密
- 播放器（FZU 2091）
- 验证素数（素数筛选法验证素数）
- hdu 2544 最短路(图论:迪杰斯特拉||弗洛伊德算法)
- JVM的垃圾回收机制详解和调优
- SGU 138 Games of Chess 构造
- jquery 取子节点及当前节点属性值
- 继承（2）
