oracle数据库停止热备,oracle 热备及恢复

1、可脱机文件丢失

SQL>select * from scott.dept;

select * from scott.dept;

*

ERROR at line 1:

ORA-01116:error in opening database file

4

ORA-01110: data file 4: ‘/u01/app/oracle/oradata/orcl/users01.dbf’     -----(users表空间丢失)

ORA-27041: unable to open file

首先查看数据文件的状态

SQL>select file#,status,enabled from v$datafile;

FILE# STATUS  ENABLED

---------- ------- ----------

1 SYSTEM  READ WRITE

2 ONLINE  READ WRITE

3 ONLINE  READ WRITE

4

ONLINE  READ WRITE

5 ONLINE  READ WRITE

SQL>

将文件号为4的脱机在查看状态

SQL>alter database datafile 4 offline;

SQL>select file#,status,enabled from v$datafile;

FILE# STATUS  ENABLED

---------- ------- ----------

1 SYSTEM   READ WRITE

2 ONLINE   READ WRITE

3 ONLINE   READ WRITE

4RECOVERREAD WRITE

5 ONLINE   READ WRITE

RECOVER :说明此文件时不正常的需要恢复

SQL>

进行数据文件还原，将备份的数据文件拷贝到数据文件原路径

SQL>ho cp /u02/horcl/users01.dbf

/u01/app/oracle/oradata/orcl/users01.dbf

将数据文件联机查看是否需要media

recover

SQL>alter database datafile 4 online;

ORA-01113：file 4 needs

media recover

ORA-01110: data file 4: ‘/u01/app/oracle/oradata/orcl/users01.dbf’

恢复数据文件4

SQL>recover datafile 4;

最后将数据文件联机

SQL>alter database datafile 4 online;

确认是否恢复成功

SQL>select * from scott.dept;

DEPTNO DNAME          LOC

---------- -------------- -------------

10 ACCOUNTING     NEW YORK

20 RESEARCH       DALLAS

30 SALES          CHICAGO

40 OPERATIONS     BOSTON

SQL>

2、不能脱机的数据文件丢失

删除系统表空间数据文件

SQL>ho rm /u01/app/oracle/oradata/orcl/system01.dbf

SQL>select tablespace_name,file_name from dba_data_files;

ERROR at line 1:

ORA-00604: error

occurred at recursive SQL level 1

ORA-01116: error in

opening database file 1

ORA-01110: data file 1:

‘/u01/app/oracle/oradata/orcl/system01.dbf’

ORA-27041: unable to

open file

系统表空间是不能脱机的

SQL>alter database datafile 1 offline;

alter database datafile 1 offline

*

ERROR at line 1:

ORA-01541: system tablespace cannot be

brought offline; shut down if necessary

关闭数据库一致性关闭数据库是无效的

可以

SQL>shutdown

abort

SQL>startup  mount

或者

SQL>startup force mount

将备份的数据文件拷贝到原数据文件路径

SQL>ho cp /u02/horcl/system01.dbf

/u01/app/oracle/oradata/orcl/system01.dbf

恢复数据文件1

SQL>recover datafile 1;

启动数据库到open阶段

SQL>alter database open;

验证

SQL>select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME

------------------------------

FILE_NAME

--------------------------------------------------------------------------------

USERS

/u01/app/oracle/oradata/orcl/users01.dbf

SYSAUX

/u01/app/oracle/oradata/orcl/sysaux01.dbf

UNDOTBS1

/u01/app/oracle/oradata/orcl/undotbs01.dbf

TABLESPACE_NAME

------------------------------

FILE_NAME

--------------------------------------------------------------------------------

SYSTEM

/u01/app/oracle/oradata/orcl/system01.dbf

EXAMPLE

/u01/app/oracle/oradata/orcl/example01.dbf