Oracle无法正常OPEN（四）

南風_入弦

于 2025-05-03 10:02:23 发布

阅读量272

点赞数 4

分类专栏： Oracle 文章标签： oracle 数据库

本文链接：https://blog.csdn.net/qq_29431123/article/details/147676080

版权

Oracle 专栏收录该内容

34 篇文章

订阅专栏

模拟部分数据文件和所有redo日志文件丢失

创建测试数据

create tablespace test datafile '/data/oracle/prod/test01.dbf' size 10M;
create user test identified by oracle default tablespace test;
grant connect,resource to test;
grant unlimited tablespace to test;
conn test/oracle;
create table t1(id int,name varchar(10));
insert into t1 values(1,'aaa');
commit;

模拟数据破坏

mv test01.dbf test01.dbf_bak
mv redo0*.log ../

尝试重启

SQL> startup
ORACLE instance started.

Total System Global Area 3221225472 bytes
Fixed Size                  8625856 bytes
Variable Size             788529472 bytes
Database Buffers         2415919104 bytes
Redo Buffers                8151040 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/data/oracle/prod/test01.dbf'

控制文件恢复

alter database backup controlfile  to trace as  '/home/oracle/cur.trc';

恢复控制文件

SQL> @cur.sql
CREATE CONTROLFILE REUSE DATABASE "PROD" NORESETLOGS FORCE LOGGING ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01565: error in identifying file '/data/oracle/prod/redo01.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
#尝试使用noresetlogs 只能使用resetlogs 恢复控制文件
[oracle@test ~]$  vi cur.sql
CREATE CONTROLFILE REUSE DATABASE "PROD" RESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/data/oracle/prod/redo01.log'  SIZE 200M BLOCKSIZE 512,
  GROUP 2 '/data/oracle/prod/redo02.log'  SIZE 200M BLOCKSIZE 512,
  GROUP 3 '/data/oracle/prod/redo03.log'  SIZE 200M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/data/oracle/prod/system01.dbf',
  '/data/oracle/prod/sysaux01.dbf',
  '/data/oracle/prod/undotbs01.dbf',
  '/data/oracle/prod/users01.dbf'
CHARACTER SET AL32UTF8
SQL>  @cur.sql

Control file created.

恢复数据库

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE;
ORA-00279: change 2965079 generated at 05/02/2025 13:32:02 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/1_2_1200058318.dbf
ORA-00280: change 2965079 for thread 1 is in sequence #2

修改隐含参数

alter system set "_allow_resetlogs_corruption"=true scope=spfile;
shutdown immediate;
startup mount;
alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-19502: write error on file "/data/oracle/prod/redo01.log", block number
329729 (block size=512)
ORA-27072: File I/O error
Additional information: 4
Additional information: 329729
Additional information: 3584

再次恢复数据库


SQL> recover database using backup controlfile until cancel;
ORA-00279: change 2965079 generated at 05/02/2025 13:32:02 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/1_2_1200058318.dbf
ORA-00280: change 2965079 for thread 1 is in sequence #2


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/data/oracle/prod/system01.dbf'


ORA-01112: media recovery not started

清理redo日志的未归档记录

alter database clear unarchived logfile group 1;   
alter database clear unarchived logfile group 2;
alter database clear unarchived logfile group 3;

尝试启动数据库

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [kcbzib_kcrsds_1], [], [], [], [],
[], [], [], [], [], [], []
Process ID: 22694
Session ID: 9 Serial number: 11734

#重新启动
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 3221225472 bytes
Fixed Size                  8625856 bytes
Variable Size             788529472 bytes
Database Buffers         2415919104 bytes
Redo Buffers                8151040 bytes
Database mounted.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery


SQL>  alter database open;
 alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/data/oracle/prod/system01.dbf'

#查看数据文件SCN号和控制文件SCN号
SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
           2965083

SQL>  select name,CHECKPOINT_CHANGE# from v$datafile_header;

NAME
--------------------------------------------------------------------------------
CHECKPOINT_CHANGE#
------------------
/data/oracle/prod/system01.dbf
           2965083

/data/oracle/prod/sysaux01.dbf
           2965083

/data/oracle/prod/undotbs01.dbf
           2965083


NAME
--------------------------------------------------------------------------------
CHECKPOINT_CHANGE#
------------------
/data/oracle/prod/users01.dbf
           2965083


#数据文件SCN号明明一致，但是open就是显示需要恢复数据文件，但是执行recover database 还是报错
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-16433: The database or pluggable database must be opened in read/write
mode.

以noresetlogs方式再次重建一次控制文件

[oracle@test ~]$ vi cur.sql 
CREATE CONTROLFILE REUSE DATABASE "PROD" NORESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/data/oracle/prod/redo01.log'  SIZE 200M BLOCKSIZE 512,
  GROUP 2 '/data/oracle/prod/redo02.log'  SIZE 200M BLOCKSIZE 512,
  GROUP 3 '/data/oracle/prod/redo03.log'  SIZE 200M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/data/oracle/prod/system01.dbf',
  '/data/oracle/prod/sysaux01.dbf',
  '/data/oracle/prod/undotbs01.dbf',
  '/data/oracle/prod/users01.dbf'
CHARACTER SET AL32UTF8
;
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 3221225472 bytes
Fixed Size                  8625856 bytes
Variable Size             788529472 bytes
Database Buffers         2415919104 bytes
Redo Buffers                8151040 bytes
SQL> @cur.sql

Control file created.

SQL> RECOVER DATABASE;
Media recovery complete.
SQL> alter database open;

Database altered.

查看数据是否丢失

用户和数据完全丢失
SQL> select username from dba_users where username='TEST';

no rows selected

SQL> select owner,table_name from dba_tables where table_name='T1' and owner='TEST';

no rows selected

遗留问题

此时表空间test 在dba_tablespaces视图中还在
SQL>  select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
TEST
尝试删除页删除不了
SQL> drop tablespace test including contents and datafiles;
drop tablespace test including contents and datafiles
*
ERROR at line 1:
ORA-01561: failed to remove all objects in the tablespace specified

创建test表空间会报错
SQL> create tablespace test datafile '/data/oracle/prod/test01.dbf' size 10M;
create tablespace test datafile '/data/oracle/prod/test01.dbf' size 10M
*
ERROR at line 1:
ORA-01543: tablespace 'TEST' already exists

创建同名表也失败
SQL> conn test/oracle;
Connected.
SQL> create table t1(id int,name varchar(10));
create table t1(id int,name varchar(10))
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kdsgrp1], [], [], [], [], [], [], [], [], [], [], []
生产环境还是马上做迁移。测试环境也无所谓了。
如果有办法删除这个表空间，欢迎评论或留言