被删除的数据文件号多次被重用
[oracle@RHEL5 backup_dir]$ vi /u01/backup_dir/hot_backup.sql
set heading off
set feedback on
set linesize 121
set termout on
set serveroutput on
begin
for x in ( select tablespace_name from dba_tablespaces where contents <> 'TEMPORARY' )
loop
dbms_output.put_line( 'alter tablespace ' || x.tablespace_name || ' begin backup');
execute immediate 'alter tablespace ' || x.tablespace_name || ' begin backup';
end loop;
end;
/
set heading off
set feedback off
set linesize 121
set termout off
--生成备份的sql
spool /u01/backup_dir/01.log
select '--'||ts.name tsname,'host cp '||df.name||' /u01/backup_dir/' ||substr(df.name,instr(df.name,'/',-1) + 1) from v$datafile df,v$tablespace ts where ts.ts# not in (select distinct ts# from v$tempfile) and df.ts# = ts.ts# order by 1;
select 'host cp '||name||' /u01/backup_dir/' from v$controlfile;
spool off
--生成恢复的sql
spool /u01/02.log
select '--'||ts.name tsname,'host cp /u01/backup_script/'||substr(df.name,instr(df.name,'/',-1) + 1)||' '||df.name from v$datafile df,v$tablespace ts where ts.ts# not in (select distinct ts# from v$tempfile) and df.ts# = ts.ts# order by 1;
select 'host cp /u01/backup_dir/'||substr(name,instr(name,'/',-1) + 1)||' '||name from v$controlfile;
spool off
--执行自动热备份
@/u01/backup_dir/01.log
set termout on
begin
for x in ( select tablespace_name from dba_tablespaces where contents <> 'TEMPORARY' )
loop
dbms_output.put_line( 'alter tablespace ' || x.tablespace_name || ' end backup');
execute immediate 'alter tablespace ' || x.tablespace_name || ' end backup';
end loop;
end;
/
先做一个热备份和一个控制文件的备份撒
[oracle@RHEL5 backup_dir]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jun 1 17:28:29 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @/u01/backup_dir/hot_backup.sql
alter tablespace SYSTEM begin backup
alter tablespace UNDOTBS1 begin backup
alter tablespace SYSAUX begin backup
alter tablespace USERS begin backup
alter tablespace TBS01 begin backup
alter tablespace TEST2 begin backup
PL/SQL procedure successfully completed.
alter tablespace SYSTEM end backup
alter tablespace UNDOTBS1 end backup
alter tablespace SYSAUX end backup
alter tablespace USERS end backup
alter tablespace TBS01 end backup
alter tablespace TEST2 end backup
SQL> alter database backup controlfile to '/u01/backup_dir/control.ctl';
SQL> host ls /u01/backup_dir
01.log control02.ctl control.ctl sysaux01.dbf tbs01.dbf undotbs01.dbf
control01.ctl control03.ctl hot_backup.sql system01.dbf test2.dbf users01.dbf
SQL> create tablespace tbs1 datafile '/u01/app/oracle/oradata/shujukuai/tbs1.dbf ' size 10m;
SQL> select file#,status$,crscnwrp,crscnbas from file$;
1 2 0 5
2 2 0 600647
3 2 0 6678
4 2 0 10685
5 2 0 631813
6 2 0 673840
7 2 0 712861 //看这个数据文件的起始SCN
SQL> col name for a40
SQL> select file#,tablespace_name,name from v$datafile_header;
FILE# TABLESPACE_NAME NAME
---------- ------------------------------ --------------------------------------------------
1 SYSTEM /u01/app/oracle/oradata/shujukuai/system01.dbf
2 UNDOTBS1 /u01/app/oracle/oradata/shujukuai/undotbs01.dbf
3 SYSAUX /u01/app/oracle/oradata/shujukuai/sysaux01.dbf
4 USERS /u01/app/oracle/oradata/shujukuai/users01.dbf
5 TBS01 /u01/app/oracle/oradata/shujukuai/tbs01.dbf
6 TEST2 /u01/app/oracle/oradata/shujukuai/test2.dbf
7 TBS1 /u01/app/oracle/oradata/shujukuai/tbs1.dbf 这里是为了证明刚刚创建的数据文件就是7号
7 rows selected.
SQL> alter system switch logfile;
System altered.
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
711306
1 row selected.
SQL> alter system switch logfile;
System altered.
SQL> drop tablespace tbs1 including contents and datafiles;
Tablespace dropped.
SQL> select file#,status$,crscnwrp,crscnbas from file$;
FILE# STATUS$ CRSCNWRP CRSCNBAS
---------- ---------- ---------- ----------
1 2 0 5
2 2 0 600647
3 2 0 6678
4 2 0 10685
5 2 0 631813
6 2 0 673840
7 1 0 712861 //数据文件状态变成1了，因为被删除球了撒
7 rows selected.
再创建一个表空间TBS2
SQL> create tablespace tbs2 datafile '/u01/app/oracle/oradata/shujukuai/tbs2.dbf' size 10m;
Tablespace created.
SQL> select file#,status$,crscnwrp,crscnbas from file$;
FILE# STATUS$ CRSCNWRP CRSCNBAS
---------- ---------- ---------- ----------
1 2 0 5
2 2 0 600647
3 2 0 6678
4 2 0 10685
5 2 0 631813
6 2 0 673840
7 2 0 713207 看，数据文件号7又变重用了，状态为2
7 rows selected.
第二次删除7号数据文件对应的表空间TBS2
SQL> drop tablespace tbs2 including contents and datafiles;
Tablespace dropped.
SQL> select file#,status$,crscnwrp,crscnbas from file$;
FILE# STATUS$ CRSCNWRP CRSCNBAS
---------- ---------- ---------- ----------
1 2 0 5
2 2 0 600647
3 2 0 6678
4 2 0 10685
5 2 0 631813
6 2 0 673840
7 1 0 713207 这里又变成1球了哈
7 rows selected.
第三次创建表空间
SQL> create tablespace tbs3 datafile '/u01/app/oracle/oradata/shujukuai/tbs3.dbf' size 10m;
Tablespace created.
SQL> select file#,status$,crscnwrp,crscnbas from file$;
FILE# STATUS$ CRSCNWRP CRSCNBAS
---------- ---------- ---------- ----------
1 2 0 5
2 2 0 600647
3 2 0 6678
4 2 0 10685
5 2 0 631813
6 2 0 673840
7 2 0 711800 又变成2球了
7 rows selected.
SQL> create table tb1(col1 number) tablespace tbs3;
Table created.
SQL> insert into tb1 values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
712949
1 row selected.
SQL> alter system switch logfile;
System altered.
SQL> insert into tb1 values(2);
1 row created.
SQL> commit;
Commit complete.
SQL> drop tablespace tbs3 including contents and datafiles;
Tablespace dropped.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 2083368 bytes
Variable Size 125830616 bytes
Database Buffers 150994944 bytes
Redo Buffers 6303744 bytes
SQL> host cp /u01/backup_dir/control.ctl /u01/app/oracle/oradata/shujukuai/control01.ctl
SQL> host cp /u01/backup_dir/control.ctl /u01/app/oracle/oradata/shujukuai/control02.ctl
SQL> host cp /u01/backup_dir/control.ctl /u01/app/oracle/oradata/shujukuai/control03.ctl
SQL> host cp /u01/backup_dir/sysaux01.dbf /u01/app/oracle/oradata/shujukuai/sysaux01.dbf
SQL> host cp /u01/backup_dir/tbs01.dbf /u01/app/oracle/oradata/shujukuai/tbs01.dbf
SQL> host cp /u01/backup_dir/undotbs01.dbf /u01/app/oracle/oradata/shujukuai/undotbs01.dbf
SQL> host cp /u01/backup_dir/system01.dbf /u01/app/oracle/oradata/shujukuai/system01.dbf
SQL> host cp /u01/backup_dir/test2.dbf /u01/app/oracle/oradata/shujukuai/test2.dbf
SQL> host cp /u01/backup_dir/user01.dbf /u01/app/oracle/oradata/shujukuai/user01.dbf
cp: cannot stat `/u01/backup_dir/user01.dbf': No such file or directory
SQL> host cp /u01/backup_dir/users01.dbf /u01/app/oracle/oradata/shujukuai/users01.dbf
SQL> alter database mount;
Database altered.
SQL> col dname for a50
SQL> set linesize 180
SQL> select substr(name,1,50) dname,checkpoint_change#,last_change#,offline_change#,creation_change# from v$datafile;
DNAME CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# CREATION_CHANGE#
-------------------------------------------------- ------------------ ------------ --------------- ----------------
/u01/app/oracle/oradata/shujukuai/system01.dbf 712460 711304 5
/u01/app/oracle/oradata/shujukuai/undotbs01.dbf 712466 711304 600647
/u01/app/oracle/oradata/shujukuai/sysaux01.dbf 712471 711304 6678
/u01/app/oracle/oradata/shujukuai/users01.dbf 712477 711304 10685
/u01/app/oracle/oradata/shujukuai/tbs01.dbf 712483 711304 631813
/u01/app/oracle/oradata/shujukuai/test2.dbf 712489 711304 673840 注意看这里的SCN都没有小于第一次创建数据文件时的SCN 711306
6 rows selected.
SQL> select substr(name,1,50) dname,recover,fuzzy,checkpoint_change#,checkpoint_count from v$datafile_header;
DNAME REC FUZ CHECKPOINT_CHANGE# CHECKPOINT_COUNT
-------------------------------------------------- --- --- ------------------ ----------------
/u01/app/oracle/oradata/shujukuai/system01.dbf YES 712460 109
/u01/app/oracle/oradata/shujukuai/undotbs01.dbf YES 712466 69
/u01/app/oracle/oradata/shujukuai/sysaux01.dbf YES 712471 109
/u01/app/oracle/oradata/shujukuai/users01.dbf YES 712477 108
/u01/app/oracle/oradata/shujukuai/tbs01.dbf YES 712483 52
/u01/app/oracle/oradata/shujukuai/test2.dbf YES 712489 31 晕了，都没有比SCN710797小了呢
6 rows selected.
SQL> recover database using backup controlfile until change 712949;
ORA-00279: change 712460 generated at 06/01/2010 18:58:34 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/SHUJUKUAI/archivelog/2010_06_01/o1_mf_1_1_%u_.arc
ORA-00280: change 712460 for thread 1 is in sequence #1
Specify log: {=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 7: '/u01/app/oracle/oradata/shujukuai/tbs1.dbf '
ORA-01112: media recovery not started
SQL> select substr(name,1,50) dname,checkpoint_change#,last_change#,offline_change#,creation_change# from v$datafile;
DNAME CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# CREATION_CHANGE#
-------------------------------------------------- ------------------ ------------ --------------- ----------------
/u01/app/oracle/oradata/shujukuai/system01.dbf 712863 711304 5
/u01/app/oracle/oradata/shujukuai/undotbs01.dbf 712863 711304 600647
/u01/app/oracle/oradata/shujukuai/sysaux01.dbf 712863 711304 6678
/u01/app/oracle/oradata/shujukuai/users01.dbf 712863 711304 10685
/u01/app/oracle/oradata/shujukuai/tbs01.dbf 712863 711304 631813
/u01/app/oracle/oradata/shujukuai/test2.dbf 712863 711304 673840
/u01/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00007 712861 0 712861 该出现的出现了
7 rows selected.
SQL> alter database create datafile '/u01/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00007' as '/u01/app/oracle/oradata/shujukuai/tbs1.dbf';
Database altered.
SQL> select substr(name,1,60) dname,checkpoint_change#,last_change#,offline_change#,creation_change# from v$datafile;
DNAME CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# CREATION_CHANGE#
-------------------------------------------------- ------------------ ------------ --------------- ----------------
/u01/app/oracle/oradata/shujukuai/system01.dbf 712863 711304 5
/u01/app/oracle/oradata/shujukuai/undotbs01.dbf 712863 711304 600647
/u01/app/oracle/oradata/shujukuai/sysaux01.dbf 712863 711304 6678
/u01/app/oracle/oradata/shujukuai/users01.dbf 712863 711304 10685
/u01/app/oracle/oradata/shujukuai/tbs01.dbf 712863 711304 631813
/u01/app/oracle/oradata/shujukuai/test2.dbf 712863 711304 673840
/u01/app/oracle/oradata/shujukuai/tbs1.dbf 712861 0 712861
7 rows selected.
SQL> recover database using backup controlfile until change 712949;
ORA-00279: change 712861 generated at 06/01/2010 19:01:08 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/SHUJUKUAI/archivelog/2010_06_01/o1_mf_1_1_%u_.arc
ORA-00280: change 712861 for thread 1 is in sequence #1
Specify log: {=suggested | filename | AUTO | CANCEL}
AUTO
Log applied.
Media recovery complete.
SQL>alter database open resetlogs;
Database altered.
SQL> select * from tb1;
select * from tb1
*
ERROR at line 1:
ORA-00942: table or view does not exist
晕，没有达到我想要的效果，应该是继续恢复数据文件TBS2.DBF，然后再继续恢复TBS3.DBF，恢复完成打开查询TB1成功才对的啊。呜呜
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21158541/viewspace-666857/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/21158541/viewspace-666857/
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
