users表空间在线损坏（不通过RMAN恢复）

---users表空间在线损坏（不通过RMAN恢复）


-----------------------------模拟users表空间在线损坏-----------------------------
现在HR下创建一个表
create table  hr.strom ( a number ) tablespace users;
alter system switch logfile;               ----此语句的作用是强制系统进行日志切换
insert into hr.strom values(1);
alter system switch logfile;
commit;                                    ---提交事务


备份datafile的users表空间，备份完将原有的users的表空间删除

SQL> ! cp -rp /u01/app/oracle/oradata/ORCL/datafile/*users* /u01/app/oracle/strom_oradata/


-----------------------------模拟users表空间在线损坏-----------------------------

SQL> ! rm -rf /u01/app/oracle/oradata/ORCL/datafile/*users*  ------删除users表空间

SQL> conn hr/hr                                                                                               -----切换到HR用户
Connected.
SQL> create table text (a number) tablespace users;                   ----HR用户下创建表
create table text (a number) tablespace users
*
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4:
'/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_7wnthzto_.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory               -----找不到users表空间
Additional information: 3

-----------------------------users表空间在线损坏---------------------------------


-----------------------------users表空间在线损坏-----------------------------

查看在"/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_7wnthzto_.dbf"下是否有这个文件

SQL> ! ls /u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_7wnthzto_.dbf
/bin/bash: line 0: cd: /u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_7wnthzto_.dbf: 
No such file or directory                   ----没有文件或目录

SQL> conn /as sysdba
Connected.
SQL> select tablespace_name,status from dba_tablespaces;    ---查看表空间状态

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

6 rows selected.

SQL> alter database datafile 4 offline;

Database altered.

SQL> ! cp -rp /u01/app/oracle/strom*/* /u01/app/oracle/oradata/ORCL/datafile/

拷贝备份目录的USERS表空间文件，到实际数据文件目录

SQL> recover tablespace users;
Media recovery complete.
SQL> alter database datafile 4 online;

Database altered.

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

Total System Global Area  281018368 bytes
Fixed Size                  2020192 bytes
Variable Size             130026656 bytes
Database Buffers          146800640 bytes
Redo Buffers                2170880 bytes
Database mounted.
Database opened.
SQL> 

-----------------------------users表空间在线损坏---------------------------------

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

转载于:http://blog.itpub.net/13453302/viewspace-735348/