很多时候丢失归档,但是客户不想resetlogs open库。可以使用bbed修改scn实现完全恢复。
使用bbed实现完全恢复
点击(此处)折叠或打开
- [oracle@yws ~]$ df -h
- Filesystem Size Used Avail Use% Mounted on
- /dev/sda2 18G 9.1G 7.3G 56% /
- /dev/sda1 289M 16M 258M 6% /boot
- tmpfs 506M 0 506M 0% /dev/shm
-
- [oracle@yws yws]$ sqlplus / as sysdba
-
- SQL*Plus: Release 10.2.0.5.0 - Production on Fri Dec 20 18:59:57 2013
-
- Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
-
-
- Connected to:
- Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
- With the Partitioning, OLAP, Data Mining and Real Application Testing options
-
-
- SQL> shutdown immediate; --关库
- Database closed.
- Database dismounted.
- ORACLE instance shut down.
- SQL> exit
- Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
- With the Partitioning, OLAP, Data Mining and Real Application Testing options
- [oracle@yws yws]$ mkdir /home/oracle/bak
- [oracle@yws yws]$ ls
- control01.ctl control03.ctl redo01.log redo03.log system01.dbf undotbs01.dbf
- control02.ctl example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf
- [oracle@yws yws]$ cp * /home/oracle/bak --物理全备
- [oracle@yws yws]$ sqlplus / as sysdba
-
- SQL*Plus: Release 10.2.0.5.0 - Production on Fri Dec 20 19:04:30 2013
-
- Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
-
- Connected to an idle instance.
-
- SQL> startup
- ORACLE instance started.
-
- Total System Global Area 285212672 bytes
- Fixed Size 1273276 bytes
- Variable Size 192938564 bytes
- Database Buffers 88080384 bytes
- Redo Buffers 2920448 bytes
- Database mounted.
- Database opened.
- SQL> archive log list
- Database log mode Archive Mode
- Automatic archival Enabled
- Archive destination /arc
- Oldest online log sequence 140
- Next log sequence to archive 142
- Current log sequence 142
- SQL> set linesize 1000
-
-
- SQL> select * from v$log;
-
- GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
- 1 1 142 52428800 1 NO CURRENT 2241033 20-DEC-13
- 2 1 140 52428800 1 YES INACTIVE 2220503 18-DEC-13
- 3 1 141 52428800 1 YES INACTIVE 2230571 18-DEC-13
-
- SQL> alter system switch logfile; --切日志产生归档
-
- System altered.
-
- SQL> /
-
- System altered.
-
- SQL> /
-
- System altered.
-
- SQL> /
-
- System altered.
-
- SQL> /
-
- System altered.
-
- SQL> /
-
- System altered.
-
- SQL> /
-
- System altered.
-
- SQL> /
-
- System altered.
-
- SQL> /
-
- System altered.
-
- SQL> select * from v$Log; --看序列号
-
- GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
- 1 1 151 52428800 1 NO CURRENT 2242730 20-DEC-13
- 2 1 149 52428800 1 YES INACTIVE 2242726 20-DEC-13
- 3 1 150 52428800 1 YES INACTIVE 2242728 20-DEC-13
-
-
- SQL> archive log list;
- Database log mode Archive Mode
- Automatic archival Enabled
- Archive destination /arc
- Oldest online log sequence 149
- Next log sequence to archive 151
- Current log sequence 151
- SQL> exit
- Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
- With the Partitioning, OLAP, Data Mining and Real Application Testing options
- [oracle@yws yws]$ cd /arc --查看归档
- [oracle@yws arc]$ ls
- 1_113_832788550.dbf 1_120_832788550.dbf 1_127_832788550.dbf 1_134_832788550.dbf 1_141_832788550.dbf 1_148_832788550.dbf
- 1_114_832788550.dbf 1_121_832788550.dbf 1_128_832788550.dbf 1_135_832788550.dbf 1_142_832788550.dbf 1_149_832788550.dbf
- 1_115_832788550.dbf 1_122_832788550.dbf 1_129_832788550.dbf 1_136_832788550.dbf 1_143_832788550.dbf 1_150_832788550.dbf
- 1_116_832788550.dbf 1_123_832788550.dbf 1_130_832788550.dbf 1_137_832788550.dbf 1_144_832788550.dbf
- 1_117_832788550.dbf 1_124_832788550.dbf 1_131_832788550.dbf 1_138_832788550.dbf 1_145_832788550.dbf
- 1_118_832788550.dbf 1_125_832788550.dbf 1_132_832788550.dbf 1_139_832788550.dbf 1_146_832788550.dbf
- 1_119_832788550.dbf 1_126_832788550.dbf 1_133_832788550.dbf 1_140_832788550.dbf 1_147_832788550.dbf
- [oracle@yws arc]$ mv 1_149_832788550.dbf /home/oracle/ --移除在此期间产生的归档
- [oracle@yws arc]$ mv 1_150_832788550.dbf /home/oracle/
- [oracle@yws arc]$ mv 1_143_832788550.dbf /home/oracle/
- [oracle@yws arc]$ mv 1_144_832788550.dbf /home/oracle/
- [oracle@yws arc]$ mv 1_145_832788550.dbf /home/oracle/
-
- [oracle@yws yws]$ rm -rf users01.dbf --删除数据文件
- [oracle@yws yws]$ sqlplus / as sysdba
-
- SQL*Plus: Release 10.2.0.5.0 - Production on Fri Dec 20 19:09:37 2013
-
- Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
-
-
- Connected to:
- Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
- With the Partitioning, OLAP, Data Mining and Real Application Testing options
-
- SQL> conn scott/tiger
- Connected.
- SQL> select * from emp; --文件丢失 报错
- select * from emp
- *
- ERROR at line 1:
- ORA-01116: error in opening database file 4
- ORA-01110: data file 4: \'/u01/oracle/oradata/yws/users01.dbf\'
- ORA-27041: unable to open file
- Linux Error: 2: No such file or directory
- Additional information: 3
-
-
- SQL> conn / as sysdba
- Connected.
- SQL> select status from v$instance;
-
- STATUS
- ------------
- OPEN
-
-
- SQL> show user
- USER is \"SYS\"
- SQL> select * from v$recover_file;
-
- no rows selected
-
- SQL> alter system checkpoint;
-
- System altered.
-
- SQL> select * from v$recover_file;
-
- no rows selected
-
- SQL> select name from v$datafile;
-
- NAME
- --------------------------------------------------------------------------------
- /u01/oracle/oradata/yws/system01.dbf
- /u01/oracle/oradata/yws/undotbs01.dbf
- /u01/oracle/oradata/yws/sysaux01.dbf
- /u01/oracle/oradata/yws/users01.dbf
- /u01/oracle/oradata/yws/example01.dbf
-
- SQL> select * from v$recover_file;
-
- no rows selected
-
- SQL> shutdown immedIATE; --正常关库报错 2.1应该可以 2.5可能有保护机制
- ORA-01116: error in opening database file 4
- ORA-01110: data file 4: \'/u01/oracle/oradata/yws/users01.dbf\'
- ORA-27041: unable to open file
- Linux Error: 2: No such file or directory
- Additional information: 3
- SQL> select status from v$instance;
-
- STATUS
- ------------
- OPEN
-
- SQL> shutdown abort; --强制停库
- ORACLE instance shut down.
- SQL> startup;
- ORACLE instance started.
-
- Total System Global Area 285212672 bytes
- Fixed Size 1273276 bytes
- Variable Size 192938564 bytes
- Database Buffers 88080384 bytes
- Redo Buffers 2920448 bytes
- Database mounted.
- ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
- ORA-01110: data file 4: \'/u01/oracle/oradata/yws/users01.dbf\'
-
-
- SQL> select status from v$instance;
-
- STATUS
- ------------
- MOUNTED
-
- SQL> select * from v$recover_file; --查看需要恢复的文件
-
- FILE# ONLINE ONLINE_
- ---------- ------- -------
- ERROR CHANGE#
- ----------------------------------------------------------------- ----------
- TIME
- ---------
- 4 ONLINE ONLINE
- FILE NOT FOUND 0
-
-
-
- [oracle@yws oradata]$ mv users01.dbf yws/ --把原来的备份拿回来
- [oracle@yws oradata]$ cd yws/
- [oracle@yws yws]$ ls
- control01.ctl control03.ctl redo01.log redo03.log system01.dbf undotbs01.dbf
- control02.ctl example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf
- [oracle@yws yws]$ sqlplus / as sysdba
-
- SQL*Plus: Release 10.2.0.5.0 - Production on Fri Dec 20 19:21:08 2013
-
- Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
-
-
- Connected to:
- Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
- With the Partitioning, OLAP, Data Mining and Real Application Testing options
-
- SQL> alter database open;
- alter database open
- *
- ERROR at line 1:
- ORA-01113: file 4 needs media recovery if it was restored from backup, or END
- BACKUP if it was not
- ORA-01110: data file 4: \'/u01/oracle/oradata/yws/users01.dbf\'
-
-
- SQL> recover datafile 4; --恢复
- ORA-00279: change 2242425 generated at 12/20/2013 19:00:23 needed for thread 1
- ORA-00289: suggestion : /arc/1_142_832788550.dbf
- ORA-00280: change 2242425 for thread 1 is in sequence #142
-
-
- Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
- auto --自动模式让他去选择
- ORA-00279: change 2242713 generated at 12/20/2013 19:06:21 needed for thread 1
- ORA-00289: suggestion : /arc/1_143_832788550.dbf
- ORA-00280: change 2242713 for thread 1 is in sequence #143
- ORA-00278: log file \'/arc/1_142_832788550.dbf\' no longer needed for this
- recovery
-
-
- ORA-00308: cannot open archived log \'/arc/1_143_832788550.dbf\' --需要seq为143的归档 此文件已经被我们移走
- ORA-27037: unable to obtain file status
- Linux Error: 2: No such file or directory
- Additional information: 3
使用bbed实现完全恢复
点击(此处)折叠或打开
- 点击(此处)折叠或打开
- SQL> select status from v$instance;
- STATUS
- ------------
- MOUNTED
- SQL> select name,CHECKPOINT_CHANGE# from v$datafile_header; --查看数据文件头scn
- NAME CHECKPOINT_CHANGE#
- ---------------------------------------------------------------------------------------------------- ------------------
- /u01/oracle/oradata/yws/system01.dbf 2242908
- /u01/oracle/oradata/yws/undotbs01.dbf 2242908
- /u01/oracle/oradata/yws/sysaux01.dbf 2242908
- /u01/oracle/oradata/yws/users01.dbf 2242713
- /u01/oracle/oradata/yws/example01.dbf 2242908
- SQL> select to_char('2242713','XXXXXXXXX') from dual; --4号文件scn 转成16进制值
- TO_CHAR('2
- ----------
- 223899
- bbed 修改
- BBED> set dba 1,1
- DBA 0x00400001 (4194305 1,1)
- BBED> set offset 484
- OFFSET 484
- BBED> p
- pad
- ---
- ub1 pad @484 0x5c
- BBED> dump --查看1号数据文件的scn
- File: /u01/oracle/oradata/yws/system01.dbf (1)
- Block: 1 Offsets: 484 to 995 Dba:0x00400001
- ------------------------------------------------------------------------
- 5c392200 0000a8bf 1e68c031 0100b552 97000000 95000000 1000af2e 02000000
- 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
- 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
- 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
- 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
- 0a000a00 0a000100 00000000 00000000 00000000 02004000 00000000 00000000
- 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
- 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
- 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
- 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
- 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
- 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
- 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
- 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
- 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
- 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
- <32 bytes per line>
- BBED> set mode edit;
- MODE Edit
- BBED> modify /x 5c3922 dba 4,1 offset 484 --修改4号文件的数据文件头scn一致
- Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
- File: /u01/oracle/oradata/yws/users01.dbf (4)
- Block: 1 Offsets: 484 to 995 Dba:0x01000001
- ------------------------------------------------------------------------
- 5c392200 00000000 ad66c031 01000000 8f000000 02000000 00000000 02000000
- 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
- 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
- 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
- 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
- 0a000a00 0a000100 00000000 00000000 00000000 02000001 00000000 00000000
- 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
- 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
- 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
- 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
- 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
- 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
- 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
- 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
- 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
- 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
- <32 bytes per line>
- BBED> sum apply;
- Check value for File 4, Block 1:
- current = 0xcdd9, required = 0xcdd9
- BBED> exit
- [oracle@yws yws]$ sqlplus / as sysdba
- SQL*Plus: Release 10.2.0.5.0 - Production on Fri Dec 20 19:39:04 2013
- Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
- Connected to:
- Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
- With the Partitioning, OLAP, Data Mining and Real Application Testing options
- SQL> startup force;
- ORACLE instance started.
- Total System Global Area 285212672 bytes
- Fixed Size 1273276 bytes
- Variable Size 192938564 bytes
- Database Buffers 88080384 bytes
- Redo Buffers 2920448 bytes
- Database mounted.
- ORA-01113: file 4 needs media recovery if it was restored from backup, or END
- BACKUP if it was not
- ORA-01110: data file 4: '/u01/oracle/oradata/yws/users01.dbf'
- SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered. - 成功open。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29477587/viewspace-1078561/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29477587/viewspace-1078561/