sysaux在没有备份的情况下，数据块损坏，数据库启动不了处理

最新推荐文章于 2025-05-13 09:13:19 发布

转载最新推荐文章于 2025-05-13 09:13:19 发布 · 2.8k 阅读

oracle 专栏收录该内容

116 篇文章

订阅专栏

本文介绍了一种在Oracle数据库中通过修改控制文件来排除SYSAUX表空间的方法，并详细展示了创建新控制文件及启动数据库的具体步骤。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

具体的思路：

(一) 考虑在控制文件中把sysaux表空间给剔除掉，启动数据库

1 创建控制文件

alter database backup controlfile to trace as '/oracle/DEV/control01.bak';

2 查看/oracle/DEV/control01.bak内容

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "DEV" NORESETLOGS ARCHIVELOG
    MAXLOGFILES 255
    MAXLOGMEMBERS 3
    MAXDATAFILES 254
    MAXINSTANCES 50
    MAXLOGHISTORY 4041
LOGFILE
GROUP 1 (
    '/oracle/DEV/origlogA/log_g11m1.dbf',
    '/oracle/DEV/mirrlogA/log_g11m2.dbf'
) SIZE 50M BLOCKSIZE 512,
GROUP 2 (
    '/oracle/DEV/origlogB/log_g12m1.dbf',
    '/oracle/DEV/mirrlogB/log_g12m2.dbf'
) SIZE 50M BLOCKSIZE 512,
GROUP 3 (
    '/oracle/DEV/origlogA/log_g13m1.dbf',
    '/oracle/DEV/mirrlogA/log_g13m2.dbf'
) SIZE 50M BLOCKSIZE 512,
GROUP 4 (
    '/oracle/DEV/origlogB/log_g14m1.dbf',
    '/oracle/DEV/mirrlogB/log_g14m2.dbf'
) SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/oracle/DEV/sapdata1/system_1/system.data1',
'/oracle/DEV/sapdata1/sysaux_1/sysaux.data1',
'/oracle/DEV/sapdata1/undo_1/undo.data1',
'/oracle/DEV/sapdata2/sr3_1/sr3.data1',
'/oracle/DEV/sapdata2/sr3_2/sr3.data2',
'/oracle/DEV/sapdata2/sr3_3/sr3.data3',
'/oracle/DEV/sapdata2/sr3_4/sr3.data4',
'/oracle/DEV/sapdata2/sr3_5/sr3.data5',
'/oracle/DEV/sapdata2/sr3_6/sr3.data6',
'/oracle/DEV/sapdata2/sr3_7/sr3.data7',
'/oracle/DEV/sapdata2/sr3_8/sr3.data8',
'/oracle/DEV/sapdata2/sr3_9/sr3.data9',
'/oracle/DEV/sapdata2/sr3_10/sr3.data10',
'/oracle/DEV/sapdata2/sr3_11/sr3.data11',
'/oracle/DEV/sapdata2/sr3_12/sr3.data12',
'/oracle/DEV/sapdata2/sr3_13/sr3.data13',
'/oracle/DEV/sapdata2/sr3_14/sr3.data14',
'/oracle/DEV/sapdata2/sr3_15/sr3.data15',
'/oracle/DEV/sapdata2/sr3_16/sr3.data16',
'/oracle/DEV/sapdata3/sr3702_1/sr3702.data1',
'/oracle/DEV/sapdata3/sr3702_2/sr3702.data2',
'/oracle/DEV/sapdata3/sr3702_3/sr3702.data3',
'/oracle/DEV/sapdata3/sr3702_4/sr3702.data4',
'/oracle/DEV/sapdata3/sr3702_5/sr3702.data5',
'/oracle/DEV/sapdata3/sr3702_6/sr3702.data6',
'/oracle/DEV/sapdata3/sr3702_7/sr3702.data7',
'/oracle/DEV/sapdata3/sr3702_8/sr3702.data8',
'/oracle/DEV/sapdata3/sr3702_9/sr3702.data9',
'/oracle/DEV/sapdata3/sr3702_10/sr3702.data10',
'/oracle/DEV/sapdata3/sr3702_11/sr3702.data11',
'/oracle/DEV/sapdata3/sr3702_12/sr3702.data12',
'/oracle/DEV/sapdata3/sr3702_13/sr3702.data13',
'/oracle/DEV/sapdata3/sr3702_14/sr3702.data14',
'/oracle/DEV/sapdata4/sr3usr_1/sr3usr.data1'
CHARACTER SET UTF8
;

3 启动数据库nomount状态下，排除数据文件'/oracle/DEV/sapdata1/sysaux_1/sysaux.data1',
创建控制文件

SQL> CREATE CONTROLFILE REUSE DATABASE "DEV" NORESETLOGS ARCHIVELOG
2      MAXLOGFILES 255
    MAXLOGMEMBERS 3
    MAXDATAFILES 254
    MAXINSTANCES 50
    MAXLOGHISTORY 4041
LOGFILE
GROUP 1 (
    '/oracle/DEV/origlogA/log_g11m1.dbf',
    '/oracle/DEV/mirrlogA/log_g11m2.dbf'
) SIZE 50M BLOCKSIZE 512,
GROUP 2 (
    '/oracle/DEV/origlogB/log_g12m1.dbf',
    '/oracle/DEV/mirrlogB/log_g12m2.dbf'
) SIZE 50M BLOCKSIZE 512,
GROUP 3 (
    '/oracle/DEV/origlogA/log_g13m1.dbf',
    '/oracle/DEV/mirrlogA/log_g13m2.dbf'
) SIZE 50M BLOCKSIZE 512,
GROUP 4 (
    '/oracle/DEV/origlogB/log_g14m1.dbf',
    '/oracle/DEV/mirrlogB/log_g14m2.dbf'
3    4    5    6    7    8    9   10    ) SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/oracle/DEV/sapdata1/system_1/system.data1',
'/oracle/DEV/sapdata1/undo_1/undo.data1',
'/oracle/DEV/sapdata2/sr3_1/sr3.data1',
'/oracle/DEV/sapdata2/sr3_2/sr3.data2',
'/oracle/DEV/sapdata2/sr3_3/sr3.data3',
'/oracle/DEV/sapdata2/sr3_4/sr3.data4',
'/oracle/DEV/sapdata2/sr3_5/sr3.data5',
'/oracle/DEV/sapdata2/sr3_6/sr3.data6',
'/oracle/DEV/sapdata2/sr3_7/sr3.data7',
'/oracle/DEV/sapdata2/sr3_8/sr3.data8',
'/oracle/DEV/sapdata2/sr3_9/sr3.data9',
'/oracle/DEV/sapdata2/sr3_10/sr3.data10',
'/oracle/DEV/sapdata2/sr3_11/sr3.data11',
'/oracle/DEV/sapdata2/sr3_12/sr3.data12',
'/oracle/DEV/sapdata2/sr3_13/sr3.data13',
'/oracle/DEV/sapdata2/sr3_14/sr3.data14',
'/oracle/DEV/sapdata2/sr3_15/sr3.data15',
'/oracle/DEV/sapdata2/sr3_16/sr3.data16',
'/oracle/DEV/sapdata3/sr3702_1/sr3702.data1',
'/oracle/DEV/sapdata3/sr3702_2/sr3702.data2',
'/oracle/DEV/sapdata3/sr3702_3/sr3702.data3',
11   12   13   14   15    '/oracle/DEV/sapdata3/sr3702_4/sr3702.data4',
'/oracle/DEV/sapdata3/sr3702_5/sr3702.data5',
'/oracle/DEV/sapdata3/sr3702_6/sr3702.data6',
'/oracle/DEV/sapdata3/sr3702_7/sr3702.data7',
'/oracle/DEV/sapdata3/sr3702_8/sr3702.data8',
'/oracle/DEV/sapdata3/sr3702_9/sr3702.data9',
'/oracle/DEV/sapdata3/sr3702_10/sr3702.data10',
'/oracle/DEV/sapdata3/sr3702_11/sr3702.data11',
'/oracle/DEV/sapdata3/sr3702_12/sr3702.data12',
'/oracle/DEV/sapdata3/sr3702_13/sr3702.data13',
'/oracle/DEV/sapdata3/sr3702_14/sr3702.data14',
'/oracle/DEV/sapdata4/sr3usr_1/sr3usr.data1'
CHARACTER SET UTF8
;
16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31   32   33   34   35   36   37   38   39   40   41   42   43   44   45   46   47   48   49   50   51   52   53   54   55   56   57   58   59   60

Control file created.

4 打开数据库

Control file created.

SQL> SQL> SQL> SQL> SQL> SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01100: database already mounted

SQL> alter database open;
Database altered.

5 检测数据的完整性

SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
PSAPUNDO
PSAPSR3
PSAPSR3702
PSAPSR3USR
SYSAUX
PSAPTEMP

7 rows selected

注：SYSAUX表空间是存在的，使用select访问各表，均可以访问，故数据是完成的

7 设置sysaux表空间为offline

SQL> alter tablespace sysaux offline;
alter tablespace sysaux offline
*
ERROR at line 1:
ORA-01191: file 2 is already offline - cannot do a normal offline
ORA-01111: name for data file 2 is unknown - rename to correct file
ORA-01110: data file 2: '/oracle/DEV/112_64/dbs/MISSING00002'

注：这样设置后，其实数据文件已经offline

(二) 在其他服务器上创建数据库

略

(三) 把数据导入新建数据库中

sapdb:oradev 80> exp sapsr3/Geely123 file=sap.dmp owner=sapsr3 rows=n buffer=6500000

Export: Release 11.2.0.3.0 - Production on Thu Oct 10 15:24:12 2013

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in UTF8 character set and UTF8 NCHAR character set
Note: table data (rows) will not be exported
. exporting pre-schema procedural objects and actions

注：由此可知此方式是可以的