IMPDP Error - ORA-04031: unable to allocate shared memory

Problem

Following Error Occurred during the datapump import

Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE
ORA-39097: Data Pump job encountered unexpected error -4031
ORA-39065: unexpected master process exception in MAIN
ORA-04031: unable to allocate 40 bytes of shared memory ("shared pool","select t.ts#,t.file#,t.block...","SQLA^b52a5f10","idndef : qcuAllocIdn")
ORA-39014: One or more workers have prematurely exited.
ORA-39029: worker 1 with process name "DW00" prematurely terminated
ORA-31671: Worker process DW00 had an unhandled exception.
ORA-04031: unable to allocate 272 bytes of shared memory ("shared pool","SELECT /*+all_rows*/ SYS_XML...","SQLA^896032bf","kccdef: qkxrMemAlloc")
ORA-06512: at "SYS.KUPW$WORKER", line 2144
ORA-06512: at line 2
ORA-04031: unable to allocate 40 bytes of shared memory ("shared pool","select t.ts#,t.file#,t.block...","SQLA^b52a5f10","idndef : qcuAllocIdn")

Solution

If the large pool is not large enough, then you get an error ORA-4031 in the large pool.  The issue is generated because the dm00 process is running out space in the large pool

1. Increase the Large pool in parameter file

SQL> alter system set large_pool_size=64m scope=spfile;

System altered.

2. Restart database to effect the parameter changes

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  838860800 bytes
Fixed Size                  2929936 bytes
Variable Size             432016112 bytes
Database Buffers          369098752 bytes
Redo Buffers               34816000 bytes
Database mounted.
Database opened.

Reference

DataPump Import (IMPDP) Fails With Errors ORA-39097 ORA-39065 ORA-56937 (Doc ID 1471910.1)

Change DB name using nid utility

1.  Mount the Database

SQL> startup mount;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 6442448976 bytes
Fixed Size                  8910928 bytes
Variable Size            2030043136 bytes
Database Buffers         4378853376 bytes
Redo Buffers               24641536 bytes
Database mounted.

2.  Execute nid utility to with new DB name

[oracle@ofprim ~]$  nid target= / dbname=test

DBNEWID: Release 19.0.0.0.0 - Production on Tue Aug 2 00:17:22 2022

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to database PRDDB (DBID=3869830090)

Connected to server version 19.12.0

Control Files in database:
    +RECO/TEST_SYD1MQ/CONTROLFILE/current.309.1095468811

Change database ID and database name PRDDB to TEST? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 3869830090 to 3502387348
Changing database name from PRDDB to TEST
    Control File +RECO/TEST_SYD1MQ/CONTROLFILE/current.309.1095468811 - modified
    Datafile +DATA/TEST_SYD1MQ/DATAFILE/system.586.111161509 - dbid changed, wrote new name
    Datafile +DATA/TEST_SYD1MQ/DATAFILE/data1.498.111160622 - dbid changed, wrote new name
    Datafile +DATA/TEST_SYD1MQ/DATAFILE/indx_large.500.111160622 - dbid changed, wrote new name
    Datafile +DATA/TEST_SYD1MQ/DATAFILE/tools.351.111161899 - dbid changed, wrote new name
    Datafile +DATA/TEST_SYD1MQ/DATAFILE/undotbs.458.111161936 - dbid changed, wrote new name
    Datafile +DATA/TEST_SYD1MQ/DATAFILE/sysaux.448.111161920 - dbid changed, wrote new name
    Datafile +DATA/TEST_SYD1MQ/TEMPFILE/temp.490.111162268 - dbid changed, wrote new name
    Control File +RECO/TEST_SYD1MQ/CONTROLFILE/current.309.1095468811 - dbid changed, wrote new name
    Instance shut down

Database name changed to TEST.
Modify parameter file and generate a new password file before restarting.
Database ID for database TEST changed to 3502387348.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.

3.  Change db_name from parameter file

[oracle@ofprim ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Aug 2 00:17:58 2022
Version 19.12.0.0.0

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

Connected to an idle instance.

SQL> startup nomount;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 6442448976 bytes
Fixed Size                  8910928 bytes
Variable Size            2030043136 bytes
Database Buffers         4378853376 bytes
Redo Buffers               24641536 bytes
SQL> alter system set db_name=testt scope=spfile;

System altered.

SQL> shutdown immediate;
ORA-01507: database not mounted;
ORACLE instance shut down.

4.  Open DB with resetlogs option

SQL> startup mount;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 6442448976 bytes
Fixed Size                  8910928 bytes
Variable Size            2030043136 bytes
Database Buffers         4378853376 bytes
Redo Buffers               24641536 bytes
Database mounted.
SQL> alter database open resetlogs;

Database altered.