oracle技术之oracle基于backup control 的完全恢复
误删除表空间（有备份），利用备份的控制文件恢复一、模拟环境
07:59:14 SQL> select count(*) from scott.dept2;
COUNT(*)
----------
12
07:59:50 SQL> drop tablespace lxtbs1 including contents and datafiles;
Tablespace dropped.
07:59:56 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
08:00:58 SQL> !
Fri Mar 23 18:50:06 2012
drop tablespace cuug including contents and datafiles
Fri Mar 23 18:50:08 2012
Deleted file /u01/app/oracle/oradata/anny/cuug01.dbf
Completed: drop tablespace cuug including contents and datafiles
二、转储所有数据文件
$ cp /orabak/orcl/cold_bak/*.dbf /disk1/oradata/orcl
08:03:26 SQL> recover database until time '2012-02-12 07:59:53' using backup controlfile;
ORA-01034: ORACLE not available
08:04:12 SQL> startup mount
ORACLE instance started.
Total System Global Area167772160 bytes
Fixed>
Variable> Database Buffers 88080384 bytes
Redo Buffers 2973696 bytes
Database mounted.
三、recoverdatabase
08:04:36 SQL> recover database until time '2012-02-12 07:59:53' using backup controlfile;
ORA-00279: change 831098 generated at 02/12/2012 06:32:28 needed for thread 1
ORA-00289: suggestion : /arch/orcl/arch_1_6_775023202.log
ORA-00280: change 831098 for thread 1 is in sequence #6
08:04:45 Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 832010 generated at 02/12/2012 07:55:37 needed for thread 1
ORA-00289: suggestion : /arch/orcl/arch_1_1_775036537.log
ORA-00280: change 832010 for thread 1 is in sequence #1
ORA-00279: change 832995 generated at 02/12/2012 07:58:39 needed for thread 1
ORA-00289: suggestion : /arch/orcl/arch_1_2_775036537.log
ORA-00280: change 832995 for thread 1 is in sequence #2
ORA-00278: log file '/arch/orcl/arch_1_1_775036537.log' no longer needed for this recovery
ORA-00279: change 832997 generated at 02/12/2012 07:58:40 needed for thread 1
ORA-00289: suggestion : /arch/orcl/arch_1_3_775036537.log
ORA-00280: change 832997 for thread 1 is in sequence #3
ORA-00278: log file '/arch/orcl/arch_1_2_775036537.log' no longer needed for this recovery
ORA-00279: change 833000 generated at 02/12/2012 07:58:43 needed for thread 1
ORA-00289: suggestion : /arch/orcl/arch_1_4_775036537.log
ORA-00280: change 833000 for thread 1 is in sequence #4
ORA-00278: log file '/arch/orcl/arch_1_3_775036537.log' no longer needed for this recovery
ORA-00279: change 833017 generated at 02/12/2012 07:59:13 needed for thread 1
ORA-00289: suggestion : /arch/orcl/arch_1_5_775036537.log
ORA-00280: change 833017 for thread 1 is in sequence #5
ORA-00278: log file '/arch/orcl/arch_1_4_775036537.log' no longer needed for this recovery
ORA-00279: change 833019 generated at 02/12/2012 07:59:14 needed for thread 1
ORA-00289: suggestion : /arch/orcl/arch_1_6_775036537.log
ORA-00280: change 833019 for thread 1 is in sequence #6
ORA-00278: log file '/arch/orcl/arch_1_5_775036537.log' no longer needed for this recovery
ORA-00308: cannot open archived log '/arch/orcl/arch_1_6_775036537.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
四、利用当前日志组恢复
08:04:52 SQL> select name from v$archived_log;
NAME
------------------------------------------------------------------------------------------------------------------------
/arch/orcl/arch_1_9_771838300.log
/arch/orcl/arch_1_10_771838300.log
/arch/orcl/arch_1_11_771838300.log
/arch/orcl/arch_1_12_771838300.log
/arch/orcl/arch_1_13_771838300.log
/arch/orcl/arch_1_14_771838300.log
/arch/orcl/arch_1_15_771838300.log
/arch/orcl/arch_1_16_771838300.log
/arch/orcl/arch_1_17_771838300.log
/arch/orcl/arch_1_18_771838300.log
/arch/orcl/arch_1_19_771838300.log
/arch/orcl/arch_1_20_771838300.log
/arch/orcl/arch_1_21_771838300.log
/arch/orcl/arch_1_4_775023202.log
/arch/orcl/arch_1_5_775023202.log
/arch/orcl/arch_1_1_775036537.log
/arch/orcl/arch_1_2_775036537.log
NAME
------------------------------------------------------------------------------------------------------------------------
/arch/orcl/arch_1_3_775036537.log
/arch/orcl/arch_1_4_775036537.log
/arch/orcl/arch_1_5_775036537.log
20 rows selected.
08:05:06 SQL> select member from v$logfile;
MEMBER
------------------------------------------------------------------------------------------------------------------------
/disk2/oradata/orcl/redo03.log
/disk2/oradata/orcl/redo02.log
/disk2/oradata/orcl/redo01.log
08:05:25 SQL> select * from v$log;
GROUP# THREAD#SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 5 52428800 1 YES INACTIVE 833017 2012-02-12 07:59:13
3 1 6 52428800 1 NOCURRENT 833019 2012-02-12 07:59:14
2 1 4 52428800 1 YES INACTIVE 833000 2012-02-12 07:58:43
08:05:59 SQL> recover database until time '2012-02-12 07:59:53' using backup controlfile;
ORA-00279: change 833019 generated at 02/12/2012 07:59:14 needed for thread 1
ORA-00289: suggestion : /arch/orcl/arch_1_6_775036537.log
ORA-00280: change 833019 for thread 1 is in sequence #6
08:06:07 Specify log: {=suggested | filename | AUTO | CANCEL}
/disk2/oradata/orcl/redo03.log
Log applied.
Media recovery complete.
08:06:13 SQL>>
Database> 08:06:40 SQL> select name from v$datafile;
NAME
------------------------------------------------------------------------------------------------------------------------
/disk1/oradata/orcl/system01.dbf
/disk1/oradata/orcl/undotbs01.dbf
/disk1/oradata/orcl/sysaux01.dbf
/disk1/oradata/orcl/users01.dbf
/disk1/oradata/orcl/example01.dbf
/u01/app/oracle/product/10.2.0/db_1/dbs/MISSING00006
6 rows selected.
08:06:48 SQL> !
$ ls /u01/app/oracle/product/10.2.0/db_1/dbs/
hc_orcl.datinitdw.orainit.orainitorcl.oralkORCLorapworclspfileorcl.ora
$ ls -a /u01/app/oracle/product/10.2.0/db_1/dbs/
...hc_orcl.datinitdw.orainit.orainitorcl.oralkORCLorapworclspfileorcl.ora
08:08:29 SQL> col file_name for a50
08:08:35 SQL> select file_id,file_name,tablespace_name from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME
---------- -------------------------------------------------- ------------------------------
4 /disk1/oradata/orcl/users01.dbf USERS
3 /disk1/oradata/orcl/sysaux01.dbf SYSAUX
2 /disk1/oradata/orcl/undotbs01.dbf UNDOTBS1
1 /disk1/oradata/orcl/system01.dbf SYSTEM
6 /u01/app/oracle/product/10.2.0/db_1/dbs/MISSING000 LXTBS1
06
5 /disk1/oradata/orcl/example01.dbf EXAMPLE
6 rows selected.
五、利用备份的datafile 再做完全恢复
08:08:59 SQL>> alter tablespace lxtbs1 offline
*
ERROR at line 1:
ORA-01191: file 6 is already offline - cannot do a normal offline
ORA-01111: name for data file 6 is unknown - rename to correct file
ORA-01110: data file 6: '/u01/app/oracle/product/10.2.0/db_1/dbs/MISSING00006'
08:09:58 SQL>>
Database> 08:10:05 SQL> !
$ cp /orabak/orcl/cold_bak/lxtbs01.dbf /disk1/oradata/orcl/
08:11:32 SQL>>
Tablespace>
08:11:44 SQL>> alter tablespace lxtbs1 online
*
ERROR at line 1:
ORA-01190: control file or data file 6 is from before the last RESETLOGS
ORA-01110: data file 6: '/disk1/oradata/orcl/lxtbs01.dbf'
08:11:58 SQL> recover datafile 6;
ORA-00279: change 831098 generated at 02/12/2012 06:32:28 needed for thread 1
ORA-00289: suggestion : /arch/orcl/arch_1_6_775023202.log
ORA-00280: change 831098 for thread 1 is in sequence #6
08:12:19 Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 832010 generated at 02/12/2012 07:55:37 needed for thread 1
ORA-00289: suggestion : /arch/orcl/arch_1_1_775036537.log
ORA-00280: change 832010 for thread 1 is in sequence #1
ORA-00279: change 832995 generated at 02/12/2012 07:58:39 needed for thread 1
ORA-00289: suggestion : /arch/orcl/arch_1_2_775036537.log
ORA-00280: change 832995 for thread 1 is in sequence #2
ORA-00278: log file '/arch/orcl/arch_1_1_775036537.log' no longer needed for this recovery
ORA-00279: change 832997 generated at 02/12/2012 07:58:40 needed for thread 1
ORA-00289: suggestion : /arch/orcl/arch_1_3_775036537.log
ORA-00280: change 832997 for thread 1 is in sequence #3
ORA-00278: log file '/arch/orcl/arch_1_2_775036537.log' no longer needed for this recovery
ORA-00279: change 833000 generated at 02/12/2012 07:58:43 needed for thread 1
ORA-00289: suggestion : /arch/orcl/arch_1_4_775036537.log
ORA-00280: change 833000 for thread 1 is in sequence #4
ORA-00278: log file '/arch/orcl/arch_1_3_775036537.log' no longer needed for this recovery
ORA-00279: change 833017 generated at 02/12/2012 07:59:13 needed for thread 1
ORA-00289: suggestion : /arch/orcl/arch_1_5_775036537.log
ORA-00280: change 833017 for thread 1 is in sequence #5
ORA-00278: log file '/arch/orcl/arch_1_4_775036537.log' no longer needed for this recovery
ORA-00279: change 833019 generated at 02/12/2012 07:59:14 needed for thread 1
ORA-00289: suggestion : /arch/orcl/arch_1_6_775036537.log
ORA-00280: change 833019 for thread 1 is in sequence #6
ORA-00278: log file '/arch/orcl/arch_1_5_775036537.log' no longer needed for this recovery
Log applied.
Media recovery complete.
08:12:35 SQL>>
Database> 08:12:42 SQL> select * from scott.dept2;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
12 rows selected.
oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html
页:
[1]