oracle pdb相关故障,模拟19c数据库pdb undo异常恢复

对于19c在pdb情况下三种常见故障进行了模拟测试:

模拟19c数据库redo异常恢复

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

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

测试在有事务的情况下,删除pdb中的undo数据库异常情况测试

会话1在root pdb中删除表记录,不提交

SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED READ ONLY NO

3 PDB READ WRITE NO

SQL> delete from system.t_xifenfei;

2316160 rows deleted.

会话2在pdb中删除表记录,不提交

SQL> conn xff/oracle@127.0.0.1/pdb

Connected.

SQL> delete from xff.t_xifenfei;

72351 rows deleted.

会话3 直接abort库

SQL> shutdown abort;

ORACLE instance shut down.

删除pdb中undo文件

[oracle@localhost pdb]$ rm -rf undotbs01.dbf

[oracle@localhost pdb]$

启动数据库

SQL> startup

ORACLE instance started.

Total System Global Area 4999609088 bytes

Fixed Size 9145088 bytes

Variable Size 905969664 bytes

Database Buffers 4076863488 bytes

Redo Buffers 7630848 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 11 - see DBWR trace file

ORA-01110: data file 11: '/u01/app/oracle/oradata/ORA19C/pdb/undotbs01.dbf'

offline异常文件,再open库

SQL> alter database datafile 11 offline drop;

alter database datafile 11 offline drop

*

ERROR at line 1:

ORA-01516: nonexistent log file, data file, or temporary file "11" in the current container

SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED MOUNTED

3 PDB MOUNTED

SQL> alter session set container=pdb;

Session altered.

SQL> alter database datafile 11 offline drop;

Database altered.

SQL> conn / as sysdba

Connected.

SQL> startup

ORACLE instance started.

Total System Global Area 4999609088 bytes

Fixed Size 9145088 bytes

Variable Size 905969664 bytes

Database Buffers 4076863488 bytes

Redo Buffers 7630848 bytes

Database mounted.

Database opened.

open pdb

SQL> alter session set container=pdb;

Session altered.

SQL>

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

测试中库open比较简单,后续只要对异常undo进行处理即可

SQL> create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/ORA19C/pdb/undotbs02.dbf' size 128M autoextend on;

Tablespace created.

SQL> alter system set undo_tablespace=undotbs2;

System altered.

SQL> select tablespace_name,segment_name,status from dba_rollback_segs;

TABLESPACE_NAME SEGMENT_NAME STATUS

------------------------------ ------------------------------ --------------------------------

SYSTEM SYSTEM ONLINE

UNDOTBS1 _SYSSMU1_3588498444$ NEEDS RECOVERY

UNDOTBS1 _SYSSMU2_2971032042$ NEEDS RECOVERY

UNDOTBS1 _SYSSMU3_3657342154$ NEEDS RECOVERY

UNDOTBS1 _SYSSMU4_811969446$ NEEDS RECOVERY

UNDOTBS1 _SYSSMU5_3018429039$ NEEDS RECOVERY

UNDOTBS1 _SYSSMU6_442110264$ NEEDS RECOVERY

UNDOTBS1 _SYSSMU7_2728255665$ NEEDS RECOVERY

UNDOTBS1 _SYSSMU8_801938064$ NEEDS RECOVERY

UNDOTBS1 _SYSSMU9_647420285$ NEEDS RECOVERY

UNDOTBS1 _SYSSMU10_2262159254$ NEEDS RECOVERY

SQL> drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped.

在测试中,undo有事务的情况下,数据库可以正常open,而且运行了一段时间未crash,在这个方面确实比11g及其以前版本有很大改进.当然由于测试环境本身比较单一,可能实际生产中会此类故障处理比较复杂