Oracle BBED 跳過歸檔實現完全恢復
很多時候丟失歸檔，但是客戶不想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/28211342/viewspace-2130944/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 使用BBED跳過歸檔檔案
- 跳過歸檔日誌的非常規恢復（一）
- [裝載]oracle 無歸檔的不完全恢復Oracle
- 【BBED】丟失歸檔檔案情況下的恢復
- Data Guard跳歸檔恢復的案例
- 備庫跳歸檔恢復的有趣案例
- oracle實驗記錄 （恢復-完全恢復）Oracle
- 基於歸檔的熱備份完全恢復
- bbed 與檔案頭恢復
- ORACLE非歸檔下的恢復Oracle
- oracle實驗記錄 （恢復-不完全恢復）Oracle
- 歸檔模式下四種完全恢復的場景模式
- rman恢復時跳過資料檔案，進行恢復
- BBED 修改oracle 資料檔案的 SCN 號來做資料庫不完全恢復。Oracle資料庫
- 歸檔模式下，線上刪除資料檔案的完全恢復模式
- 使用bbed完成資料庫的不完全恢復資料庫
- Oracle 不完全恢復Oracle
- Oracle手工完全恢復案例Oracle
- oracle dg 歸檔日誌恢復情況Oracle
- oracle RMAN 非歸檔資料庫恢復Oracle資料庫
- 測試在丟失歸檔日誌的情況下，跳過部分歸檔日誌進行資料恢復資料恢復
- Oracle RMAN 不完全恢復（只有資料檔案備份，丟失歸檔日誌備份）Oracle
- oracle基於歸檔的增量異地恢復Oracle
- Oracle RAC Standby 恢復歸檔numuber錯亂Oracle
- 恢復之非歸檔模式下的恢復模式
- Oracle使用備份檔案集恢復歸檔日誌Oracle
- 【備份與恢復】控制檔案的恢復（不完全恢復）
- 【Mysql】完全恢復與不完全恢復MySql
- 6_Oracle truncate異常恢復之bbed修復Oracle
- Oracle delete誤運算元據恢復(BBED)Oracledelete
- rman datafile恢復(歸檔模式)模式
- 【BBED】 SYSTEM檔案頭損壞的恢復(4)
- 歸檔模式下資料庫全恢復的過程模式資料庫
- 28_bbed實戰(1)_delete操作恢復delete
- DG歸檔日誌缺失恢復
- 無備份恢復(歸檔模式)模式
- Oracle手工不完全恢復（一）：使用當前控制檔案Oracle
- Oracle手工不完全恢復（二）：使用備份控制檔案Oracle