查阅了网上的大部分文章,原因有很多:归档使用率高导致的(​​ORA-03113​​​),冷备后出现日志文件比控制文件新导致的(​​ORA-03113​​​),没有一致性关库导致的(​​ORA-03113​​​)。

奇怪的是,在我的测试库,启动PDB1时,报错ORA-03113并没有在日志中看到。而ORA-03113的报错,在借鉴网上给出的原因后,我删除了归档日志后,此错误不再出现。因为我的测试库db_recovery_file_dest_size=0。之后,报错ORA-04031。而ORA-04031的错误,就是由于SGA和PGA的不足导致,变更(修改SGA和PGA)后,启动PDB1恢复正常。

以下是我的处理过程截图,大家可以做为参考。

Oracle-ORA-03113与ORA-04031导致的PDB启动失败_控制文件

Oracle-ORA-03113与ORA-04031导致的PDB启动失败_ORA-04031_02

Oracle-ORA-03113与ORA-04031导致的PDB启动失败_ORA-04031_03

Oracle-ORA-03113与ORA-04031导致的PDB启动失败_ORA-03113_04

Oracle-ORA-03113与ORA-04031导致的PDB启动失败_控制文件_05

Oracle-ORA-03113与ORA-04031导致的PDB启动失败_归档日志_06


Oracle-ORA-03113与ORA-04031导致的PDB启动失败_ORA-03113_07

Oracle-ORA-03113与ORA-04031导致的PDB启动失败_闪回_08

Oracle-ORA-03113与ORA-04031导致的PDB启动失败_归档日志_06

Oracle-ORA-03113与ORA-04031导致的PDB启动失败_归档日志_10

Oracle-ORA-03113与ORA-04031导致的PDB启动失败_ORA-03113_11

Oracle-ORA-03113与ORA-04031导致的PDB启动失败_控制文件_12

Oracle-ORA-03113与ORA-04031导致的PDB启动失败_ORA-03113_13

Oracle-ORA-03113与ORA-04031导致的PDB启动失败_闪回_14

Oracle-ORA-03113与ORA-04031导致的PDB启动失败_控制文件_15

处理过程命令参考:

#############################################
######启动PDB失败 报错 ORA-03113
#############################################
$ sqlplus / as sysdba
SYS@b19c01> show pdbs
SYS@b19c01> alter pluggable database pdb1 open;
alter pluggable database pdb1 open;
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 42107
Session ID: 90 Serial number: 9521
SYS@b19c01> exit

$ srvctl stop database -d b19c0

#############################################
######启动到 mount 状态 删除归档
#############################################
$ sqlplus / as sysdba
SYS@b19c01> startup mount
SYS@b19c01> select * from v%flash_recovery_area_usage;

no rows selected

SYS@b19c01> show parameter db_recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string
db_recovery_file_dest_size big integer 0
SYS@b19c01>
SYS@b19c01> exit

$ rman target /
##这里删除的是今天以前的归档日志记录
RMAN> crosscheck archivelog all;
RMAN> delete archivelog all completed before 'sysdate - 0';
RMAN> exit

$ /u01/app/19.3.0/grid/bin/crsctl stop cluster -all

#############################################
######正常启动 打开PDB时 报错 ORA-04031
#############################################
$ /u01/app/19.3.0/grid/bin/crsctl start cluster -all
$ sqlplus / as sysdba
SYS@b19c01> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
4 PDB2 MOUNTED

SYS@b19c01> alter pluggable database pdb1 open;
alter pluggable database pdb1 open
*
ERROR at line 1:
ORA-04031: unable to allocate 64840 bytes of shared memory ("shared pool","unknown object","PDB Dynamic He","pdb event stats segment")

SYS@b19c01> exit
$ /u01/app/19.3.0/grid/bin/crsctl stop cluster -all
#############################################
######启动到 mount 状态 变更SGA PGA设置
#############################################
$ sqlplus / as sysdba
SYS@b19c01> startup mount
ORACLE instance started.

Total System Global Area 2483027624 bytes
Fixed Size 9137832 bytes
Variable Size 486539264 bytes
Database Buffers 1979711488 bytes
Redo Buffers 7639040 bytes
Database mounted.
SYS@b19c01>
SYS@b19c01> show parameter sga
SYS@b19c01> show parameter pga
SYS@b19c01> show parameter memory
SYS@b19c01> show parameter pfile
SYS@b19c01> create pfile from spfile;

System altered.

SYS@b19c01> create pfile='/u01/app/oracle/admin/b19c0/pfile/pfileb19c01.ora' from spfile;

System altered.

SYS@b19c01> alter system set sga_max_size=2600m sid='*' scope=spfile;

System altered.

SYS@b19c01> alter system set sga_target=2600m sid='*' scope=spfile;

System altered.

SYS@b19c01> alter system set pga_aggregate_target=1024m sid='*' scope=spfile;

System altered.

SYS@b19c01> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SYS@b19c01> exit
#############################################
######再次重启 恢复正常 PDB正常打开
#############################################
$sqlplus / as sysdba

SYS@b19c01> startup
ORACLE instance started.

Total System Global Area 2734682488 bytes
Fixed Size 9138552 bytes
Variable Size 486539264 bytes
Database Buffers 2231369728 bytes
Redo Buffers 7634944 bytes
Database mounted.
Database opened.
SYS@b19c01> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
4 PDB2 MOUNTED

SYS@b19c01> alter pluggable database pdb1 open;

Pluggable database altered.

SYS@b19c01>