正常关闭，数据文件被覆盖，用数据文件新建前的控制文件热备份恢复新建数据文件...-优快云博客

数据库正常关闭，数据文件被覆盖，采用数据文件新建前的控制文件热备份恢复新建数据文件

[oracle@RHEL5 shujukuai]$ mkdir /u01/backup_dir
[oracle@RHEL5 shujukuai]$ 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_dir/'||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;
/
exit

[oracle@RHEL5 shujukuai]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jun 1 00:29:39 2010

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> create tablespace test datafile '/u01/app/oracle/oradata/shujukuai/test.dbf' size 10m;

Tablespace created.

SQL> select file#,status$,CRSCNBAS from file$;

FILE#    STATUS$   CRSCNBAS
---------- ---------- ----------
         1          2          5
         2          2     600647
         3          2       6678
         4          2      10685
         5          2     631813
         6          2     673840
         7          2     676237

6 rows selected.

SQL> set linesize 170

SQL> create table b tablespace test as select * from dict;

Table created.

SQL> alter system switch logfile;

System altered.

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
676790

SQL> alter system switch logfile;

System altered.

SQL> drop tablespace test including contents and datafile;
drop tablespace test including contents and datafile
*
ERROR at line 1:
ORA-00905: missing keyword

SQL> drop tablespace test including contents and datafiles;

Tablespace dropped.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> @/u01/02.log

SQL> startup
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size                  2083368 bytes
Variable Size             117442008 bytes
Database Buffers          159383552 bytes
Redo Buffers                6303744 bytes
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/app/oracle/oradata/shujukuai/system01.dbf'

SQL> col dname for a60
SQL> select file#,status,substr(name,1,60) dname from v$datafile;

     FILE# STATUS DNAME
---------- ------- ------------------------------------------------------------
         1 SYSTEM /u01/app/oracle/oradata/shujukuai/system01.dbf
         2 ONLINE /u01/app/oracle/oradata/shujukuai/undotbs01.dbf
         3 ONLINE /u01/app/oracle/oradata/shujukuai/sysaux01.dbf
         4 ONLINE /u01/app/oracle/oradata/shujukuai/users01.dbf
         5 ONLINE /u01/app/oracle/oradata/shujukuai/tbs01.dbf
         6 ONLINE /u01/app/oracle/oradata/shujukuai/test2.dbf
         7 RECOVER /u01/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00007

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/test.dbf';

Database altered.

SQL> select file#,status,substr(name,1,70) dname from v$datafile;

SQL> recover database using backup controlfile until change 676790;
ORA-00279: change 676237 generated at 06/01/2010 02:42:10 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/SHUJUKUAI/archivelog/2010_06_01/o1_mf_1_17_%
u_.arc
ORA-00280: change 676237 for thread 1 is in sequence #17

Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/flash_recovery_area/SHUJUKUAI/archivelog/2010_06_01/o1_mf_1_17_%
u_.arc
ORA-00308: cannot open archived log
'/u01/app/oracle/flash_recovery_area/SHUJUKUAI/archivelog/2010_06_01/o1_mf_1_17_
%'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

Specify log: {=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log 'u_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '/u01/app/oracle/oradata/shujukuai/system01.dbf'

SQL> alter database open resetlogs;

Database altered.

SQL> select file#,status$ from file$;

     FILE#    STATUS$
---------- ----------
         1          2
         2          2
         3          2
         4          2
         5          2
         6          2
         7          2                     这个数据文件变为在线了

7 rows selected.

SQL> select file#,status,substr(name,1,70) dname from v$datafile;

7 rows selected.

SQL> select count(*) from b;

COUNT(*)
----------
1875

数据也找回来了，这里应用了归档日志进行的恢复。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21158541/viewspace-666675/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21158541/viewspace-666675/