标签归档:ORA-01157 ORA-01110

模拟19c数据库root pdb undo异常恢复

对于19c在pdb情况下三种常见故障进行了模拟测试:
模拟19c数据库redo异常恢复
模拟19c数据库pdb undo异常恢复
模拟19c数据库root pdb undo异常恢复
模拟oracle 19c cdb模式下root pdb中undo丢失故障恢复
会话1,pdb中插入大量数据,未提交

SQL> alter session set container=pdb; 

Session altered.

SQL> alter database open;

Database altered.

SQL> create user xff identified by oracle default tablespace users;
grant dba to xff;
conn xff/oracle@127.0.0.1/pdb
create table t_xifenfei as select * from dba_objects;
insert into t_xifenfei select * from t_xifenfei;
insert into t_xifenfei select * from t_xifenfei;
insert into t_xifenfei select * from t_xifenfei;
insert into t_xifenfei select * from t_xifenfei;
insert into t_xifenfei select * from t_xifenfei;

User created.

SQL> 
Grant succeeded.

SQL> Connected.
SQL> 

Table created.

SQL> 
72351 rows created.

SQL> 
144702 rows created.

SQL> 
289404 rows created.

SQL> 
578808 rows created.

SQL> 

1157616 rows created.

SQL> SQL> SQL> 

会话2中root pdb模拟事务

[oracle@localhost ~]$ ss

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Nov 16 16:56:01 2020
Version 19.5.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0

SQL> 
SQL> 
SQL> conn system/oracle
Connected.
SQL> create table t_xifenfei tablespace users as select * from dba_objects;
insert into t_xifenfei select * from t_xifenfei;
insert into t_xifenfei select * from t_xifenfei;
insert into t_xifenfei select * from t_xifenfei;
insert into t_xifenfei select * from t_xifenfei;
insert into t_xifenfei select * from t_xifenfei;

Table created.

SQL> 
72380 rows created.

SQL> 
144760 rows created.

SQL> 
289520 rows created.

SQL> 
579040 rows created.

SQL> 

1158080 rows created.

SQL> SQL> 

会话3 abort库并删除root pdb中undo文件

[oracle@localhost ~]$ ss

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Nov 16 16:56:55 2020
Version 19.5.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0

SQL> shutdown abort;
ORACLE instance shut down.
SQL> 

[oracle@localhost oradata]$ cd ORA19C
[oracle@localhost ORA19C]$ ls
control01.ctl  control02.ctl  pdb  pdbseed  redo01.log  redo02.log  redo03.log  sysaux01.dbf
system01.dbf  temp01.dbf  undotbs01.dbf  users01.dbf
[oracle@localhost ORA19C]$ rm -rf undotbs01.dbf 

启动数据库报ORA-01157 ORA-01110错误

SQL> alter database datafile 4 offline drop;

Database altered.

SQL> alter database open;

Database altered.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB                            MOUNTED
SQL> alter session set container=pdb;

Session altered.

SQL> alter database open;

Database altered.

SQL> conn / as sysdba
Connected.
SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB                            READ WRITE NO
 
SQL> SQL> select tablespace_name,segment_name,status from dba_rollback_segs;

TABLESPACE_NAME                SEGMENT_NAME                   STATUS
------------------------------ ------------------------------ --------------------------------
SYSTEM                         SYSTEM                         ONLINE
UNDOTBS1                       _SYSSMU1_1261223759$           NEEDS RECOVERY
UNDOTBS1                       _SYSSMU2_27624015$             NEEDS RECOVERY
UNDOTBS1                       _SYSSMU3_2421748942$           NEEDS RECOVERY
UNDOTBS1                       _SYSSMU4_625702278$            NEEDS RECOVERY
UNDOTBS1                       _SYSSMU5_2101348960$           NEEDS RECOVERY
UNDOTBS1                       _SYSSMU6_813816332$            NEEDS RECOVERY
UNDOTBS1                       _SYSSMU7_2329891355$           NEEDS RECOVERY
UNDOTBS1                       _SYSSMU8_399776867$            NEEDS RECOVERY
UNDOTBS1                       _SYSSMU9_1692468413$           NEEDS RECOVERY
UNDOTBS1                       _SYSSMU10_930580995$           NEEDS RECOVERY

本次测试比较幸运,虽然undo段状态为NEEDS RECOVERY,但是数据库直接open成功.实际生产情况,可能比这个要复杂很多

发表在 Oracle | 标签为 , | 评论关闭

linux资源限制导致数据库异常

一起由于liunx系统资源限制导致数据库无法启动案例分享
数据库启动报ORA-01157错

SQL> startup
ORACLE instance started.

Total System Global Area 3340451840 bytes
Fixed Size		    2217952 bytes
Variable Size		 1862273056 bytes
Database Buffers	 1459617792 bytes
Redo Buffers		   16343040 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/home/oracle/oradata/XIFENFEI.dbf'

该错误一般是由于文件丢失或者路径错误导致

alert日志显示

Sun Apr 07 20:57:03 2019
ALTER DATABASE OPEN
Sun Apr 07 20:57:03 2019
Errors in file /dbdata/oracle/diag/rdbms/orcl/orcl/trace/orcl_dbw0_2681.trc:
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/home/oracle/oradata/XIFENFEI.dbf'
ORA-27092: size of file exceeds file size limit of the process
Additional information: 262144
Additional information: 262145
Errors in file /dbdata/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2802.trc:
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/home/oracle/oradata/XIFENFEI.dbf'
ORA-1157 signalled during: ALTER DATABASE OPEN...
Sun Apr 07 20:57:04 2019
Errors in file /dbdata/oracle/diag/rdbms/orcl/orcl/trace/orcl_m000_2804.trc  (incident=38578):
ORA-00600: internal error code, arguments: [kcidr_io_check_common_6], [10], 
     [/home/oracle/oradata/XIFENFEI.dbf], [8192], [2], [5], [], [], [], [], [], []
ORA-27092: size of file exceeds file size limit of the process

这里看到提示ORA-27092: size of file exceeds file size limit of the process
查看系统limit配置

[oracle@XFF ~]$ ulimit -a
core file size          (blocks, -c) unlimited
data seg size           (kbytes, -d) 640000
scheduling priority             (-e) 0
file size               (blocks, -f) 2097152
pending signals                 (-i) 128489
max locked memory       (kbytes, -l) unlimited
max memory size         (kbytes, -m) unlimited
open files                      (-n) 131072
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 10240
cpu time               (seconds, -t) unlimited
max user processes              (-u) 131072
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

一般操作系统block size为1k,这里限制文件大小为2097152=(2G)
查看文件

[oracle@XFF ~]$ ls -l /home/oracle/oradata/XIFENFEI.dbf
-rw-r-----. 1 oracle oinstall 2147491840 Apr  7 19:04 /home/oracle/oradata/XIFENFEI.dbf

文件大小为2097160>2097152,导致异常

设置系统对文件大小限制2097152kb

[root@XFF ~]# ulimit -f 102400000
[root@XFF ~]# su - oracle
[oracle@XFF ~]$ ulimit -a
core file size          (blocks, -c) unlimited
data seg size           (kbytes, -d) 640000
scheduling priority             (-e) 0
file size               (blocks, -f) 102400000
pending signals                 (-i) 128489
max locked memory       (kbytes, -l) unlimited
max memory size         (kbytes, -m) unlimited
open files                      (-n) 131072
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 10240
cpu time               (seconds, -t) unlimited
max user processes              (-u) 131072
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

重启数据库,open成功

SQL> shutdown immediate;      
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 3340451840 bytes
Fixed Size		    2217952 bytes
Variable Size		 1862273056 bytes
Database Buffers	 1459617792 bytes
Redo Buffers		   16343040 bytes
Database mounted.
Database opened.
发表在 Oracle备份恢复 | 标签为 , , | 评论关闭