当存在read only或者offline表空间时，重建控制文件时要注意！

最新推荐文章于 2024-10-11 17:04:33 发布

clt3617

最新推荐文章于 2024-10-11 17:04:33 发布

阅读量228

点赞数

文章标签：数据库

当存在read only或者offline表空间时，重建控制文件分两种情况：

1、重建controlfile的script中包含read only或者offline所对应的datafile；这种情况比较简单，datafile不需要rename，如果是offline的表空间，那么重建controlfile之后只需要online tbs即可；如果是read only tbs那么重建controlfile之后需要对其对应的daafile进行恢复。

2、重建controlfile的script中不包含read only或者offline所对应的datafile；重建controlfile之后需要rename datafile到正确的位置，之后如果是offline的表空间，那么重建controlfile之后只需要online tbs即可；如果是read only tbs那么重建controlfile之后需要对其对应的daafile进行恢复。

[@more@]

测试1：

SQL> alter database backup controlfile to trace;

数据库已更改。

SQL> alter tablespace users read only;

表空间已更改。

SQL> connect /as sysdba
已连接。
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。

Total System Global Area 167772160 bytes
Fixed Size 1247900 bytes
Variable Size 58721636 bytes
Database Buffers 104857600 bytes
Redo Buffers 2945024 bytes
ORA-00205: ?????????, ??????, ???????

SQL> select status from v$instance;

STATUS
------------------------
STARTED

SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS NOARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 226
7 LOGFILE
8 GROUP 1 (
9 'E:ORACLEPRODUCT10.2.0ORADATAORCLONLINELOGO1_MF_1_46MGCZHN_.LOG'
,
10 'E:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREAORCLONLINELOGO1_MF_1_46
MGD3VT_.LOG'
11 ) SIZE 50M,
12 GROUP 2 (
13 'E:ORACLEPRODUCT10.2.0ORADATAORCLONLINELOGO1_MF_2_46MGD7OZ_.LOG'
,
14 'E:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREAORCLONLINELOGO1_MF_2_46
MGDDKL_.LOG'
15 ) SIZE 50M,
16 GROUP 3 (
17 'E:ORACLEPRODUCT10.2.0ORADATAORCLONLINELOGO1_MF_3_46MGDJ3F_.LOG'
,
18 'E:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREAORCLONLINELOGO1_MF_3_46
MGDNX9_.LOG'
19 ) SIZE 50M
20 -- STANDBY LOGFILE
21 DATAFILE
22 'E:ORACLEPRODUCT10.2.0ORADATAORCLDATAFILEO1_MF_SYSTEM_46MGDYM1_.DB
F',
23 'E:ORACLEPRODUCT10.2.0ORADATAORCLDATAFILEO1_MF_UNDOTBS1_46MGFWLK_.
DBF',
24 'E:ORACLEPRODUCT10.2.0ORADATAORCLDATAFILEO1_MF_SYSAUX_46MGG4B8_.DB
F',
25 'E:ORACLEPRODUCT10.2.0ORADATAORCLDATAFILEO1_MF_USERS_46MGGRPG_.DBF
'
26 CHARACTER SET ZHS16GBK
27 ;
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS NOARCHIVELOG
*
第 1 行出现错误:
ORA-01503: CREATE CONTROLFILE ??
ORA-01276: ??????
E:ORACLEPRODUCT10.2.0ORADATAORCLCONTROLFILEO1_MF_46MGCW6C_.CTL?????????
Oracle Managed Files ????

SQL> alter session set nls_language=american;

Session altered.

SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS NOARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 226
7 LOGFILE
8 GROUP 1 (
9 'E:ORACLEPRODUCT10.2.0ORADATAORCLONLINELOGO1_MF_1_46MGCZHN_.LOG'
,
10 'E:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREAORCLONLINELOGO1_MF_1_46
MGD3VT_.LOG'
11 ) SIZE 50M,
12 GROUP 2 (
13 'E:ORACLEPRODUCT10.2.0ORADATAORCLONLINELOGO1_MF_2_46MGD7OZ_.LOG'
,
14 'E:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREAORCLONLINELOGO1_MF_2_46
MGDDKL_.LOG'
15 ) SIZE 50M,
16 GROUP 3 (
17 'E:ORACLEPRODUCT10.2.0ORADATAORCLONLINELOGO1_MF_3_46MGDJ3F_.LOG'
,
18 'E:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREAORCLONLINELOGO1_MF_3_46
MGDNX9_.LOG'
19 ) SIZE 50M
20 -- STANDBY LOGFILE
21 DATAFILE
22 'E:ORACLEPRODUCT10.2.0ORADATAORCLDATAFILEO1_MF_SYSTEM_46MGDYM1_.DB
F',
23 'E:ORACLEPRODUCT10.2.0ORADATAORCLDATAFILEO1_MF_UNDOTBS1_46MGFWLK_.
DBF',
24 'E:ORACLEPRODUCT10.2.0ORADATAORCLDATAFILEO1_MF_SYSAUX_46MGG4B8_.DB
F',
25 'E:ORACLEPRODUCT10.2.0ORADATAORCLDATAFILEO1_MF_USERS_46MGGRPG_.DBF
'
26 CHARACTER SET ZHS16GBK
27 ;
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS NOARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01276: Cannot add file
E:ORACLEPRODUCT10.2.0ORADATAORCLCONTROLFILEO1_MF_46MGCW6C_.CTL. File
has an Oracle Managed Files file name.

SQL> show parameter control_files

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string E:ORACLEPRODUCT10.2.0ORADA
TAORCLCONTROLFILEO1_MF_46MG
CW6C_.CTL, E:ORACLEPRODUCT1
0.2.0FLASH_RECOVERY_AREAORCL
CONTROLFILEO1_MF_46MGCX9R_.C
TL
SQL> alter system reset control_files scope=spfile sid='*';

System altered.

SQL> shutdown immediate
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1247900 bytes
Variable Size 58721636 bytes
Database Buffers 104857600 bytes
Redo Buffers 2945024 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS NOARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 226
7 LOGFILE
8 GROUP 1 (
9 'E:ORACLEPRODUCT10.2.0ORADATAORCLONLINELOGO1_MF_1_46MGCZHN_.LOG'
,
10 'E:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREAORCLONLINELOGO1_MF_1_46
MGD3VT_.LOG'
11 ) SIZE 50M,
12 GROUP 2 (
13 'E:ORACLEPRODUCT10.2.0ORADATAORCLONLINELOGO1_MF_2_46MGD7OZ_.LOG'
,
14 'E:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREAORCLONLINELOGO1_MF_2_46
MGDDKL_.LOG'
15 ) SIZE 50M,
16 GROUP 3 (
17 'E:ORACLEPRODUCT10.2.0ORADATAORCLONLINELOGO1_MF_3_46MGDJ3F_.LOG'
,
18 'E:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREAORCLONLINELOGO1_MF_3_46
MGDNX9_.LOG'
19 ) SIZE 50M
20 -- STANDBY LOGFILE
21 DATAFILE
22 'E:ORACLEPRODUCT10.2.0ORADATAORCLDATAFILEO1_MF_SYSTEM_46MGDYM1_.DB
F',
23 'E:ORACLEPRODUCT10.2.0ORADATAORCLDATAFILEO1_MF_UNDOTBS1_46MGFWLK_.
DBF',
24 'E:ORACLEPRODUCT10.2.0ORADATAORCLDATAFILEO1_MF_SYSAUX_46MGG4B8_.DB
F',
25 'E:ORACLEPRODUCT10.2.0ORADATAORCLDATAFILEO1_MF_USERS_46MGGRPG_.DBF
'
26 CHARACTER SET ZHS16GBK
27 ;

控制文件已创建。

SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-01113: ?? 4 ??????
ORA-01110: ???? 4:
'E:ORACLEPRODUCT10.2.0ORADATAORCLDATAFILEO1_MF_USERS_46MGGRPG_.DBF'

SQL> select status from v$instance;

STATUS
------------
MOUNTED
SQL> alter session set nls_language=american;

Session altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4:
'E:ORACLEPRODUCT10.2.0ORADATAORCLDATAFILEO1_MF_USERS_46MGGRPG_.DBF'

SQL> recover datafile 4;
Media recovery complete.
SQL> alter database open;

Database altered.

SQL>

测试2：

SQL> connect sys/system@test as sysdba
已连接。
SQL> alter tablespace users read only;

表空间已更改。
SQL> alter database backup controlfile to trace;

数据库已更改。
--====================================
CREATE CONTROLFILE REUSE DATABASE "TEST" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 'E:ORACLEPRODUCT10.2.0ORADATATESTREDO01.LOG' SIZE 10M,
GROUP 2 'E:ORACLEPRODUCT10.2.0ORADATATESTREDO02.LOG' SIZE 10M,
GROUP 3 'E:ORACLEPRODUCT10.2.0ORADATATESTREDO03.LOG' SIZE 10M
-- STANDBY LOGFILE
DATAFILE
'E:ORACLEPRODUCT10.2.0ORADATATESTSYSTEM01.DBF',
'E:ORACLEPRODUCT10.2.0ORADATATESTUNDOTBS01.DBF',
'E:ORACLEPRODUCT10.2.0ORADATATESTSYSAUX01.DBF',
'E:ORACLEPRODUCT10.2.0ORADATATESTUNTOTBS02.DBF'
CHARACTER SET ZHS16GBK
;
--====================================
SQL> alter tablespace sysaux offline;

表空间已更改。

SQL> alter database backup controlfile to trace;

数据库已更改。
--===================================
CREATE CONTROLFILE REUSE DATABASE "TEST" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 'E:ORACLEPRODUCT10.2.0ORADATATESTREDO01.LOG' SIZE 10M,
GROUP 2 'E:ORACLEPRODUCT10.2.0ORADATATESTREDO02.LOG' SIZE 10M,
GROUP 3 'E:ORACLEPRODUCT10.2.0ORADATATESTREDO03.LOG' SIZE 10M
-- STANDBY LOGFILE
DATAFILE
'E:ORACLEPRODUCT10.2.0ORADATATESTSYSTEM01.DBF',
'E:ORACLEPRODUCT10.2.0ORADATATESTUNDOTBS01.DBF',
'E:ORACLEPRODUCT10.2.0ORADATATESTUNTOTBS02.DBF'
CHARACTER SET ZHS16GBK
;
--===================================
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup nomount
ORACLE 例程已经启动。

Total System Global Area 167772160 bytes
Fixed Size 1247900 bytes
Variable Size 67110244 bytes
Database Buffers 96468992 bytes
Redo Buffers 2945024 bytes
SQL> show parameter control_files

NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
control_files string
E:ORACLEPRODUCT10.2.0FLASH
_RECOVERY_AREATESTCONTROLFIL
EO1_MF_46HB5K8S_.CTL
--由于使用了OMF管理的controlfile，因此在重建controlfile之前必须
执行下面命令把参数control_files清空，否则重建controlfile失败
SQL> alter system reset control_files scope=spfile sid='*';

系统已更改。

SQL> shutdown immediate
ORA-01507: ??????

ORACLE 例程已经关闭。
SQL> startup nomount
ORACLE 例程已经启动。

Total System Global Area 167772160 bytes
Fixed Size 1247900 bytes
Variable Size 67110244 bytes
Database Buffers 96468992 bytes
Redo Buffers 2945024 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "TEST" NORESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 'E:ORACLEPRODUCT10.2.0ORADATATESTREDO01.LOG' SIZE 10M,
9 GROUP 2 'E:ORACLEPRODUCT10.2.0ORADATATESTREDO02.LOG' SIZE 10M,
10 GROUP 3 'E:ORACLEPRODUCT10.2.0ORADATATESTREDO03.LOG' SIZE 10M
11 -- STANDBY LOGFILE
12 DATAFILE
13 'E:ORACLEPRODUCT10.2.0ORADATATESTSYSTEM01.DBF',
14 'E:ORACLEPRODUCT10.2.0ORADATATESTUNDOTBS01.DBF',
15 'E:ORACLEPRODUCT10.2.0ORADATATESTUNTOTBS02.DBF'
16 CHARACTER SET ZHS16GBK
17 ;

控制文件已创建。

SQL> alter database open;

数据库已更改。

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX OFFLINE
TEMP ONLINE
USERS READ ONLY
UNDOTBS2 ONLINE
TEMP2 ONLINE

已选择7行。

SQL> alter tablespace sysaux online;
alter tablespace sysaux online
*
第 1 行出现错误:
ORA-01157: ????????/???????????? 3 - ?????? DBWR ????????
ORA-01111: ???????? 3 ???????? - ??????????????????
ORA-01110: ???????? 3: 'E:ORACLEPRODUCT10.2.0DB_1DATABASEMISSING00003'

SQL> alter tablespace users read write;
alter tablespace users read write
*
第 1 行出现错误:
ORA-01135: DML/query ?????????? 4 ????????????
ORA-01111: ???????? 4 ???????? - ??????????????????
ORA-01110: ???????? 4: 'E:ORACLEPRODUCT10.2.0DB_1DATABASEMISSING00004'

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> col file_name format a60
SQL> col tablespace_name format a12
SQL> select file_name , tablespace_name from dba_data_files;

FILE_NAME TABLESPACE_N
------------------------------------------------------------ ------------
E:ORACLEPRODUCT10.2.0ORADATATESTUNTOTBS02.DBF UNDOTBS2
E:ORACLEPRODUCT10.2.0ORADATATESTUNDOTBS01.DBF UNDOTBS1
E:ORACLEPRODUCT10.2.0ORADATATESTSYSTEM01.DBF SYSTEM
E:ORACLEPRODUCT10.2.0DB_1DATABASEMISSING00003 SYSAUX
E:ORACLEPRODUCT10.2.0DB_1DATABASEMISSING00004 USERS

SQL> alter tablespace sysaux rename datafile 'E:ORACLEPRODUCT10.2.0DB_1DATA
BASEMISSING00003' to 'E:oracleproduct10.2.0oradatatestsysaux01.dbf';

表空间已更改。

SQL> alter tablespace users rename datafile 'E:ORACLEPRODUCT10.2.0DB_1DATAB
ASEMISSING00004' to 'E:oracleproduct10.2.0oradatatestusers01.dbf';

表空间已更改。

SQL> select file_name , tablespace_name from dba_data_files;

FILE_NAME TABLESPACE_N
------------------------------------------------------------ ------------
E:ORACLEPRODUCT10.2.0ORADATATESTUNTOTBS02.DBF UNDOTBS2
E:ORACLEPRODUCT10.2.0ORADATATESTUNDOTBS01.DBF UNDOTBS1
E:ORACLEPRODUCT10.2.0ORADATATESTSYSTEM01.DBF SYSTEM
E:ORACLEPRODUCT10.2.0ORADATATESTSYSAUX01.DBF SYSAUX
E:ORACLEPRODUCT10.2.0ORADATATESTUSERS01.DBF USERS

SQL> alter tablespace sysaux online;

表空间已更改。

SQL> alter tablespace users read write;
alter tablespace users read write
*
第 1 行出现错误:
ORA-01135: DML/query ?????????? 4 ????????????
ORA-01110: ???????? 4: 'E:ORACLEPRODUCT10.2.0ORADATATESTUSERS01.DBF'

SQL> alter session set nls_language=american;

Session altered.

SQL> alter tablespace users read write;
alter tablespace users read write
*
ERROR at line 1:
ORA-01135: file 4 accessed for DML/query is offline
ORA-01110: data file 4: 'E:ORACLEPRODUCT10.2.0ORADATATESTUSERS01.DBF'

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_N STATUS
------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS READ ONLY
UNDOTBS2 ONLINE
TEMP2 ONLINE

7 rows selected.

SQL> alter tablespace users online;

Tablespace altered.

SQL> alter tablespace users read write;

Tablespace altered.

SQL>

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

转载于:http://blog.itpub.net/19602/viewspace-1006658/