恢复只读表空间
One of the tablespaces is read-only in your database. The loss of all control files forced you to re-create
the control file.
Which operation do you need to perform. after re-creating the control file and opening the database?
A. drop and re-create the read-only tablespaces
B. rename the read-only data files to their correct file names
C. change the tablespace status from read/write to read-only
D. re-create the read-only tablespace because it is automatically removed
Answer: B
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
CWMLITE ONLINE
DRSYS ONLINE
EXAMPLE READ ONLY
INDX ONLINE
ODM ONLINE
TOOLS ONLINE
USERS ONLINE
XDB ONLINE
RMIS ONLINE
RMAN_TS ONLINE
已选择13行。
SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount;
ORACLE 例程已经启动。
Total System Global Area 1578183724 bytes
Fixed Size 457772 bytes
Variable Size 1560281088 bytes
Database Buffers 16777216 bytes
Redo Buffers 667648 bytes
ORA-00205: ?????????????????????
SQL> select status from v$instance;
STATUS
------------------------
STARTED
SQL> @E:\dbdata\oradata\RMIS\controlfile.txt
控制文件已创建
SQL> alter database mount;
alter database mount
*
ERROR 位于第 1 行:
ORA-01100: ??????
SQL> shutdown immediate;
ORA-01109: ??????
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup nomount;
ORACLE 例程已经启动。
Total System Global Area 1578183724 bytes
Fixed Size 457772 bytes
Variable Size 1560281088 bytes
Database Buffers 16777216 bytes
Redo Buffers 667648 bytes
SQL> select status from v$instance;
STATUS
------------------------
STARTED
SQL> alter database mount;
数据库已更改。
SQL> select status from v$instance;
STATUS
------------------------
MOUNTED
SQL> alter database open;
数据库已更改。
SQL> select count(*) from hr.employees;
select count(*) from hr.employees
*
ERROR 位于第 1 行:
ORA-00376: ???????? 5
ORA-01111: ???? 5 ???? - ?????????
ORA-01110: ???? 5: 'D:\ORACLE\ORA92\DATABASE\MISSING00005'
SQL> select a.file_name,a.status from dba_data_files a;
FILE_NAME STATUS
-------------------------------------------------- ---------
E:\DBDATA\ORADATA\RMIS\SYSTEM01.DBF AVAILABLE
E:\DBDATA\ORADATA\RMIS\UNDOTBS01.DBF AVAILABLE
E:\DBDATA\ORADATA\RMIS\CWMLITE01.DBF AVAILABLE
E:\DBDATA\ORADATA\RMIS\DRSYS01.DBF AVAILABLE
D:\ORACLE\ORA92\DATABASE\MISSING00005 AVAILABLE
E:\DBDATA\ORADATA\RMIS\INDX01.DBF AVAILABLE
E:\DBDATA\ORADATA\RMIS\ODM01.DBF AVAILABLE
E:\DBDATA\ORADATA\RMIS\TOOLS01.DBF AVAILABLE
E:\DBDATA\ORADATA\RMIS\USERS01.DBF AVAILABLE
E:\DBDATA\ORADATA\RMIS\XDB01.DBF AVAILABLE
E:\DBDATA\ORADATA\RMIS\RMIS.ORA AVAILABLE
E:\DBDATA\ORADATA\RMIS\RMAN_TS.DBF AVAILABLE
已选择12行。
SQL> select a.tablespace_name,a.status from dba_tablespaces a;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
CWMLITE ONLINE
DRSYS ONLINE
EXAMPLE READ ONLY
INDX ONLINE
ODM ONLINE
TOOLS ONLINE
USERS ONLINE
XDB ONLINE
RMIS ONLINE
RMAN_TS ONLINE
已选择13行。
SQL> alter database rename file 'D:\ORACLE\ORA92\DATABASE\MISSING00005' to 'E:\DBDATA\ORADATA\RMIS\EXAMPLE01.DBF';
数据库已更改。
SQL> select a.file_name,a.status from dba_data_files a;
FILE_NAME STATUS
-------------------------------------------------- ---------
E:\DBDATA\ORADATA\RMIS\SYSTEM01.DBF AVAILABLE
E:\DBDATA\ORADATA\RMIS\UNDOTBS01.DBF AVAILABLE
E:\DBDATA\ORADATA\RMIS\CWMLITE01.DBF AVAILABLE
E:\DBDATA\ORADATA\RMIS\DRSYS01.DBF AVAILABLE
E:\DBDATA\ORADATA\RMIS\EXAMPLE01.DBF AVAILABLE
E:\DBDATA\ORADATA\RMIS\INDX01.DBF AVAILABLE
E:\DBDATA\ORADATA\RMIS\ODM01.DBF AVAILABLE
E:\DBDATA\ORADATA\RMIS\TOOLS01.DBF AVAILABLE
E:\DBDATA\ORADATA\RMIS\USERS01.DBF AVAILABLE
E:\DBDATA\ORADATA\RMIS\XDB01.DBF AVAILABLE
E:\DBDATA\ORADATA\RMIS\RMIS.ORA AVAILABLE
E:\DBDATA\ORADATA\RMIS\RMAN_TS.DBF AVAILABLE
已选择12行。
SQL> select a.tablespace_name,a.status from dba_tablespaces a;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
CWMLITE ONLINE
DRSYS ONLINE
EXAMPLE READ ONLY
INDX ONLINE
ODM ONLINE
TOOLS ONLINE
USERS ONLINE
XDB ONLINE
RMIS ONLINE
RMAN_TS ONLINE
已选择13行。
SQL> select count(*) from hr.employees;
select count(*) from hr.employees
*
ERROR 位于第 1 行:
ORA-00376: ???????? 5
ORA-01110: ???? 5: 'E:\DBDATA\ORADATA\RMIS\EXAMPLE01.DBF'
SQL> alter tablespace example online;
表空间已更改。
SQL> select count(*) from hr.employees;
COUNT(*)
----------
107
SQL> spool off
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/8102208/viewspace-584411/ ，如需转载，请注明出处，否则将追究法律责任。
