数据库数据文件有备份的恢复 ORA-01157 【续+1】

浏览数：17 / 时间：2015年06月12日

目的：

测试将数据库关闭状态下，将数据文件删除（把数据文件做备份），启动数据库报错，如何恢复数据文件？？

启动数据库如何恢复没有换路径的数据文件和更换路径的数据文件

继续试验

http://blog.csdn.net/wanghui5767260/article/details/20293807

准备环境：

创建两个表空间 tyger1 和 tyger2

创建两个用户 tu1 默认表空间 tyger1

tu2 默认表空间 tyger2

在每个用户下创建相应的测试表 tu1 用户 tu1_t1

tu2 用户 tu2_t2

[oracle@tyger ORCL]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Mar 3 10:28:36 2014

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SYS@ORCL>create tablespace tyger1 datafile ‘/u01/app/oracle/oradata/ORCL/tyger1_01.dbf‘ size 5m;

Tablespace created.

SYS@ORCL>create tablespace tyger2 datafile ‘/u01/app/oracle/oradata/ORCL/tyger2_01.dbf‘ size 5m;

Tablespace created.

SYS@ORCL>create user tu1 identified by tu1 account unlock default tablespace tyger1;

User created.

SYS@ORCL>create user tu2 identified by tu2 account unlock default tablespace tyger2;

User created.

SYS@ORCL>grant connect,resource,select any table to tu1;

Grant succeeded.

SYS@ORCL>grant connect,resource,select any table to tu2;

Grant succeeded.

SYS@ORCL>conn tu1/tu1
Connected.
TU1@ORCL>create table tu1_t1 as select * from scott.emp;

Table created.

TU1@ORCL>select count(*) from tu1_t1;

COUNT(*)
----------
14

TU1@ORCL>conn tu2/tu2
Connected.
TU2@ORCL>create table tu2_t2 as select * from scott.emp;

Table created.

TU2@ORCL>select count(*) from tu2_t2;

COUNT(*)
----------
14

TU2@ORCL>conn / as sysdba
Connected.
SYS@ORCL>col file_name for a50
SYS@ORCL>col tablespace_name for a20
SYS@ORCL>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
SYSTEM /u01/app/oracle/oradata/ORCL/system01.dbf
EXAMPLE /u01/app/oracle/oradata/ORCL/example01.dbf
TYGER1 /u01/app/oracle/oradata/ORCL/tyger1_01.dbf
TYGER2 /u01/app/oracle/oradata/ORCL/tyger2_01.dbf

7 rows selected.

SYS@ORCL>select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS
-------------------- ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
TYGER1 ONLINE
TYGER2 ONLINE

8 rows selected.

SYS@ORCL>!
[oracle@tyger ORCL]$ ls
control01.ctl redo03.log standbylog6b.log tyger1_01.dbf
control02.ctl standbylog4a.log standbylog7a.log tyger2_01.dbf
control03.ctl standbylog4b.log standbylog7b.log undotbs01.dbf
example01.dbf standbylog5a.log sysaux01.dbf users01.dbf
redo01.log standbylog5b.log system01.dbf
redo02.log standbylog6a.log temp01.dbf
[oracle@tyger ORCL]$ mv tyger1_01.dbf tyger1_01.dbf.bak
[oracle@tyger ORCL]$ mv tyger2_01.dbf tyger2_01.dbf.bak
[oracle@tyger ORCL]$ ls
recontrol01.ctl redo03.log standbylog6b.log tyger1_01.dbf.bak
control02.ctl standbylog4a.log standbylog7a.log tyger2_01.dbf.bak
control03.ctl standbylog4b.log standbylog7b.log undotbs01.dbf
example01.dbf standbylog5a.log sysaux01.dbf users01.dbf
redo01.log standbylog5b.log system01.dbf
redo02.log standbylog6a.log temp01.dbf
[oracle@tyger ORCL]$ mv tyger1_01.dbf.bak tyger1_01.dbf
[oracle@tyger ORCL]$ mv tyger2_01.dbf.bak tyger2_01.dbf
[oracle@tyger ORCL]$ exit
exit

SYS@ORCL>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ORCL>!
[oracle@tyger ORCL]$ mv tyger1_01.dbf tyger1_01.dbf.bak
[oracle@tyger ORCL]$ mv tyger2_01.dbf tyger2_01.dbf.bak
[oracle@tyger ORCL]$ exit
exit

SYS@ORCL>startup
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 150996560 bytes
Database Buffers 130023424 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: ‘/u01/app/oracle/oradata/ORCL/tyger1_01.dbf‘

SYS@ORCL>alter database datafile 6 offline;

Database altered.

SYS@ORCL>alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: ‘/u01/app/oracle/oradata/ORCL/tyger2_01.dbf‘

SYS@ORCL>alter database datafile 7 offline;

Database altered.

SYS@ORCL>alter database open;

Database altered.

SYS@ORCL>select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS
-------------------- ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
TYGER1 ONLINE
TYGER2 ONLINE

8 rows selected.

SYS@ORCL>!
[oracle@tyger ORCL]$ mv tyger1_01.dbf.bak tyger1_01.dbf
[oracle@tyger ORCL]$ mv tyger2_01.dbf.bak ../tyger2_01.dbf
[oracle@tyger ORCL]$ cd ..
[oracle@tyger oradata]$ ls
ORCL tyger2_01.dbf
[oracle@tyger oradata]$ pwd
/u01/app/oracle/oradata
[oracle@tyger oradata]$ exit
exit

SYS@ORCL>alter tablespace tyger1 online;

Tablespace altered.

SYS@ORCL>alter tablespace tyger2 online;
alter tablespace tyger2 online
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: ‘/u01/app/oracle/oradata/ORCL/tyger2_01.dbf‘

SYS@ORCL>alter database rename file ‘/u01/app/oracle/oradata/ORCL/tyger2_01.dbf‘ to ‘/u01/app/oracle/oradata/tyger2_01.dbf‘;

Database altered.

SYS@ORCL>alter tablespace tyger2 online;

Tablespace altered.

SYS@ORCL>conn tu1/tu1
Connected.
TU1@ORCL>select count(*) from tu1_t1;

COUNT(*)
----------
14

TU1@ORCL>conn tu2/tu2
Connected.
TU2@ORCL>select count(*) from tu2_t2;

COUNT(*)
----------
14