Monday, July 19, 2021

Generate Table, Index or tablespace DDLs in oracle

When migrating database from one server to another you need Tablespace creation DDL. Also it is very useful when copying specific table or index or exp/imp from one database to databases.

Here are few ways to generate the tablespace script from source database.

SQL>SET ECHO OFF;
SQL>SET HEADING OFF;
SQL>SET LINESIZE 1000;
SQL> SET LONG 60000;
SQL> SET FEEDBACK OFF;
SQL>SPOOL TBS_DDL.SQL
SQL>SELECT DBMS_METADATA.GET_DDL('TABLESPACE',DBA_TABLESPACES.TABLESPACE_NAME) FROM DBA_TABLESPACES;
SQL>SPOOL OFF

You can see below command if you want the TABLESPACE creation script specific to one Table space.

SQL> SELECT DBMS_METADATA.GET_DDL('TABLESPACE','&TABLESPACE_NAME') FROM dual;

You can use below command to generate specific TABLE, INDEX Script from a user

Syntax:-
select dbms_metadata.get_ddl('TABLE','<TABEL NAME>','<SCHEMA>') from dual;
select dbms_metadata.get_ddl('INDEX','<INDEX NAME>','<SCHEMA>') from dual;


Ex:-
select dbms_metadata.get_ddl('TABLE','EMP_SAL','EMP') from dual;
select dbms_metadata.get_ddl('INDEX','EMPNO_IDX','EMP') from dual;


You can use below command to all TABLE, INDEX Script from a user. First, connect to user

SQL>SET ECHO OFF;
SQL>SET HEADING OFF;
SQL>SET LINESIZE 1000;
SQL> SET LONG 60000;
SQL> SET FEEDBACK OFF;
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE',U.TABLE_NAME)
FROM USER_TABLES U;
SQL> SELECT DBMS_METADATA.GET_DDL('INDEX',U.INDEX_NAME)
FROM USER_INDEXES U;  

Thanks & Regards
http://oracleracexpert.com






Wednesday, July 14, 2021

DBCA fails with ORA-29516: Aurora assertion failure

When creating 11g database on a new system we encounter below error…

BEGIN
*
ERROR at line 1:
ORA-29516: Aurora assertion failure: Assertion failure at joez.c:3422
Bulk load of method java/lang/Object.<init> failed; insufficient shm-object space
ORA-06512: at line 3

You should look for dbca error logs for detailed error message that you can find under /<ORACLE_BASE>/cfgtoollogs/dbca/<SID>/

We found below error from postDBCreation.log

IF CatbundleCreateDir(:catbundleLogDir) = 0 THEN
*
ERROR at line 71:
ORA-06550: line 71, column 14:
PLS-00201: identifier 'CATBUNDLECREATEDIR' must be declared
ORA-06550: line 71, column 11:
PL/SQL: Statement ignored


This error could be one of the below reasons

1. The file systems or mount point options are in correct
2. Incorrect ulimit sessions for oracle user
3. You system has JAVA_JIT_ENABLED=TRUE

In my case I have below mount options in /etc/fstab
/dev/shm tmpfs defaults,nodev,nosuid,noexec 0 0

After removing “nodev,nosuid,noexec” from /etc/fstab resolved the issue.
/dev/shm tmpfs defaults 0 0

Thanks & Regards
http://oracleracexpert.com  





Wednesday, May 26, 2021

ORA-01555: snapshot too old: rollback segment number x with name "_xxxxx" too small

When we come across “snapshot too old” error we need to look into all possibilities

1. Determine if UNDO_MANAGEMENT is MANUAL or AUTO – Make sure you are using Auto, this will take care of auto management and will help to tune

2. In the ora-01555, if you see segment number with name that means it is caused by UNDO segment, if not LOG segment due to read consistency

3. Find out which client sessions or programs causing the issue and QUERY DURATION.

The possible solution will be set UNDO_MANAGEMENT to AUTO then make sure we have set correct value for UNDO_RETENTION.

Run below SQL query to identify weather undo tablespace was too small to maintain UNDO_RETENTION

select inst_id, to_char(begin_time,'MM/DD/YYYY HH24:MI') begin_time,
UNXPSTEALCNT "# Unexpired|Stolen", EXPSTEALCNT "# Expired|Reused",
SSOLDERRCNT "ORA-1555|Error", NOSPACEERRCNT "Out-Of-space|Error",
MAXQUERYLEN "Max Query|Length" from gv$undostat
where begin_time between
to_date(‘Start time of the query','MM/DD/YYYY HH24:MI:SS')
and
to_date('End time of the query','MM/DD/YYYY HH24:MI:SS')
order by inst_id, begin_time;


Find out the current retention period by querying the tuned_undoretention column of v$undostat. The database tunes the undo retention period to be longer than the long running query. The v$undostat view contains one row for each 10-minute stats collection interval over the last 4 days. The Data beyond 4 days can query the dba_hist_undostat view.

The below query will display the tuned_undoretention value in seconds:

select to_char(begin_time, 'DD-MON-RR HH24:MI') begin_time,
to_char(end_time, 'DD-MON-RR HH24:MI') end_time,
tuned_undoretention from v$undostat
order by end_time;


Refer Oracle notes
Note 563470.1 Lob retention not changing when undo_retention is changed
Note 800386.1 ORA-1555 - UNDO_RETENTION is silently ignored if the LOB
Note 422826.1 How To Identify LOB Segment Use PCTVERSION Or RETENTION
Bug:3200789 Abstract: VISIBILITY OF LOB SEGMENT USAGE FOR UNDO


Thanks & Regards,
http://oracleracexpert.com, Oracle ACE

Monday, May 24, 2021

Oracle software cloning VERIFICATION_FAILED with ACFSUtil:PRCT-1011 : Failed to run "acfsutil". Detailed error

I recently come across below error message during the Oracle Binaries cloning for ORacle 19c  using “runInstaller”

I fond below messages during the troubleshooting 

INFO: [May 8, 2021 12:02:43 AM] oracle.install.library.crs.CRSInfo.isCRSConfigured() returns value false
INFO: [May 8, 2021 12:02:43 AM] oracle.install.library.crs.CRSInfo.isHAConfigured() returns value false
FINEST: [May 8, 2021 12:02:43 AM] oracle.install.library.asm.ACFSUtil:PRCT-1011 : Failed to run "acfsutil". Detailed error:
/bin/sh: /sbin//acfsutil: No such file or directory
INFO: [May 8, 2021 12:02:43 AM] Retrieving ASM Cluster File System information...
INFO: [May 8, 2021 12:02:43 AM] oracle.install.library.crs.CRSInfo.isCRSConfigured() returns value false
INFO: [May 8, 2021 12:02:43 AM] oracle.install.library.crs.CRSInfo.isHAConfigured() returns value false
INFO: [May 8, 2021 12:02:43 AM] Getting the last existing parent of: /oracle/product/19.0.0.0/dbhome_1
INFO: [May 8, 2021 12:02:43 AM] Path: /oracle/product/19.0.0.0/dbhome_1
INFO: [May 8, 2021 12:02:43 AM] Last existing parent: /oracle/product/19.0.0.0/dbhome_1
INFO: [May 8, 2021 12:02:43 AM] Executing [df, -P, /oracle/product/19.0.0.0/dbhome_1]
INFO: [May 8, 2021 12:02:43 AM] Starting Output Reader Threads for process df
INFO: [May 8, 2021 12:02:43 AM] Filesystem 1024-blocks Used Available Capacity Mounted on
INFO: [May 8, 2021 12:02:43 AM] The process df exited with code 0
INFO: [May 8, 2021 12:02:43 AM] Waiting for output processor threads to exit.
INFO: [May 8, 2021 12:02:43 AM] Output processor threads exited.
INFO: [May 8, 2021 12:02:43 AM] PATH has :==>/oracle/product/19.0.0.0/dbhome_1/lib:/oracle/product/19.0.0.0/dbhome_1/oui/lib/linux64:/oracle/product/19.0.0.0/dbhome_1/bin:/oracle/product/19.0.0.0/dbhome_1/ctx/lib:/usr/java/packages/lib/amd64:/usr/lib64:/lib64:/lib:/usr/lib
INFO: [May 8, 2021 12:02:43 AM] PATH has :==>/oracle/product/19.0.0.0/dbhome_1/lib:/oracle/product/19.0.0.0/dbhome_1/oui/lib/linux64:/oracle/product/19.0.0.0/dbhome_1/bin:/oracle/product/19.0.0.0/dbhome_1/ctx/lib:/usr/java/packages/lib/amd64:/usr/lib64:/lib64:/lib:/usr/lib
INFO: [May 8, 2021 12:02:43 AM] oracle.install.library.crs.CRSInfo.isCRSConfigured() returns value false
INFO: [May 8, 2021 12:02:43 AM] oracle.install.library.crs.CRSInfo.isHAConfigured() returns value false
FINEST: [May 8, 2021 12:02:43 AM] oracle.install.library.asm.ACFSUtil:PRCT-1011 : Failed to run "acfsutil". Detailed error:
/bin/sh: /sbin//acfsutil: No such file or directory


Verify any pre-requisites verifications failed.
$ cat installActions2021-05-08_00-02-39AM.log |grep VERIFICATION_FAILED

TaskKernelParam:OS Kernel Parameter: shmmax[CHECK_KERNEL_PARAMETER_shmmax]:TASK_SUMMARY:FAILED:IGNORABLE:VERIFICATION_FAILED:Total time taken [139 Milliseconds]
TaskKernelParam:OS Kernel Parameter: shmall[CHECK_KERNEL_PARAMETER_shmall]:TASK_SUMMARY:FAILED:IGNORABLE:VERIFICATION_FAILED:Total time taken [155 Milliseconds]
TaskKernelParam:OS Kernel Parameter: file-max[CHECK_KERNEL_PARAMETER_file-max]:TASK_SUMMARY:FAILED:IGNORABLE:VERIFICATION_FAILED:Total time taken [107 Milliseconds]
TaskKernelParam:OS Kernel Parameter: rmem_default[CHECK_KERNEL_PARAMETER_rmem_default]:TASK_SUMMARY:FAILED:IGNORABLE:VERIFICATION_FAILED:Total time taken [105 Milliseconds]
TaskKernelParam:OS Kernel Parameter: rmem_max[CHECK_KERNEL_PARAMETER_rmem_max]:TASK_SUMMARY:FAILED:IGNORABLE:VERIFICATION_FAILED:Total time taken [103 Milliseconds]
TaskKernelParam:OS Kernel Parameter: wmem_default[CHECK_KERNEL_PARAMETER_wmem_default]:TASK_SUMMARY:FAILED:IGNORABLE:VERIFICATION_FAILED:Total time taken [103 Milliseconds]
TaskKernelParam:OS Kernel Parameter: wmem_max[CHECK_KERNEL_PARAMETER_wmem_max]:TASK_SUMMARY:FAILED:IGNORABLE:VERIFICATION_FAILED:Total time taken [106 Milliseconds]
TaskKernelParam:OS Kernel Parameter: aio-max-nr[CHECK_KERNEL_PARAMETER_aio-max-nr]:TASK_SUMMARY:FAILED:IGNORABLE:VERIFICATION_FAILED:Total time taken [104 Milliseconds]
TaskPackage:Package: gcc-c++-4.8.2[CHECK_PACKAGE_EXISTENCE_gcc-c++]:TASK_SUMMARY:FAILED:IGNORABLE:VERIFICATION_FAILED:Total time taken [80 Milliseconds]

1. Make sure you verify that ORACLE_HOME has owned by Oracle and Group and it has proper permissions…
chown -R oracle:dba /oracle/product/19.0.0.0/dbhome_1
chmod 775 /oracle/product/19.0.0.0/dbhome_1

2. Also verify the logs under oraInventory for any VERIFICATION_FAILED error messages. Make sure you fix all Oracle pre-requisites before you run runInstaller again. This should resolve many of the issues during the cloning.

Thanks & Regards
http://oracleracexpert.com, Oracle ACE



Tuesday, May 18, 2021

Oracle RMAN backup using DataDomain-Boost

Data Domain boost plug-in enables database to communicate with Data domain systems in an optimized way and use of DD Boost for RMAN backup improves performance while reducing the data transfer.

You need to install RMAN pug-in on each DB server and these binaries communicate with Data domain systems. When taking backup using RMAN, the configuration channels should use media manager SBT_LIBRARY parameter to provide the path of the media management library of data domain.

RMAN issues an ORA-27211 error and exits if the media management library not able to locate.

Admin needs to enable DD Boost on a Data Domain System before we proceeding.

Steps:-  

1. Download the required pulg-in and install
For Ex:- RMAN_1.1.1.0-378849_RMAN_linux_64.tar

For Windows Download the RMAN plugin installer “libDDobkSetup.exe”

2. Installation: Set the Oracle Home then run the install.sh script
$ ./install.sh
Installing the Data Domain plugin for RMAN ...
Copying libraries to /oracle/home/11204/lib
cp libddobk.so /oracle/home/11204/lib/libddobk.so
cp libDDBoost.so /oracle/home/11204/lib/libDDBoost.so
Successfully installed the Data Domain plugin for RMAN

/orasnb/oracle_ddbda.cfg will have all configuration details

3. Verify: Verify the DDBoost install using below command , make the changes as needed

RUN {
ALLOCATE CHANNEL C1 TYPE SBT_TAPE PARMS 'SBT_LIBRARY=/oracle/home/11204/lib/libddobk.so';
send 'set username <username> password <password> servername <data domain/backup host server>';
RELEASE CHANNEL C1;
}

4. Run backup using DD Boost Media management library

You can run manual backup

run {
allocate channel dd1 type 'sbt_tape' parms='BLKSIZE=1048576,SBT_LIBRARY=/oracle/home/11204/lib/libddobk.so,ENV=(STORAGE_UNIT=<storage_unit>,BACKUP_HOST=<backup host>)';
backup filesperset 1 database format '%u_%p';
release channel dd1;
}

Configure BRTools using DDBoost: - Add/Change the below parameter in .sap file of database and comment out other unwanted parameters. Note that DDBoost will call RMAN internally to backup and restore.

backup_type = online
backup_dev_type = rman_disk
rman_channels = 10 <or any number of channels desired>
rman_filesperset = 1
rman_parms = "BLKSIZE=1048576,SBT_LIBRARY=<path-to-libddobk.so>,ENV=(STORAGE_UNIT=<LSU-name>,BACKUP_HOST=<DDR-FQDN>,ORACLE_HOME=<path-to-ORACLE_HOME>)"
rman_compress = no
backup_mode = all


RMAN Changes: Make below rman configuration changes
CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'SBT_TAPE' TO '%d_%F';

Perform Backup: 
Backup command:
brbackup -m all -p /oracle/home/11204 /dbs/initORCL.sap -u / -t online -d rman_disk
"-t online" option is the same as specifying "backup_type = online" in the configuration file
"-d rman_disk" option is to force "backup_dev_type = rman_disk", this is the observed behavior
Complete list of Command Options for BRBACKUP : http://help.sap.com/saphelp_nw70/helpdata/en/c7/c90e6b7cfd44309bd90b2b7a8a89fd/content.htm
 
Archive Log backup:
brarchive -s -c -p /oracle/home/11204/dbs/initORCL.sap -u /

Restore command:
brrestore -m all -p initKBW.disk.sap -b belcjoiv.anr -c force
We never tested restore using above command but we tested thoroughly with BRTools and RMAN

Steps to Enable DDBoost on a Data domain system: Unix Admin will execute these steps. The below are high-level steps and few other steps involved to configure as per the best practice.

1. Login as Administrator
 
2. Verify file sytem is enable dand running
# filesys status
Enable the file system, if diabled.
#filesys enable
 
3. Apply license key if not installed
# license add <license key>
 
4. Establish username and password for Data domain system
#user add <username> password <password>
# ddboost set user-name <username>
 
5. Enable DDBoost
# ddboost enable
 
6. Create the storage unit on the data domain system
# ddboost storage-unit create <storage_unit_name>

Thanks & Regards

Friday, May 14, 2021

Configure Oracle RMAN Backups using Media Manager

In order to backup and restore from sequential media such as tape you must integrate a media manager with your Oracle database. Note that media manager is not an Oracle product and must be obtained and licensed fom a third-party vendor.

1. Prerequisites for a Media Manager 

Before using any media manager with your Oracle database you need to install the required plug in and make sure that RMAN can communicate with it. For Instructions, refer vendor documentation. The third-party media management module contains the media management library that the Oracle database loads and uses when accessing the media manager..

2. Locating the Media Management Library

User should use SBT_LIBRARY parameter when allocating or configuring channels for RMAN to use to communicate with a media manager, SBT_LIBRARY parameter to provide the path to the media management library and this library will be loaded during the communication with media manager

On UNIX, the default library location is $ORACLE_HOME/lib/libobk.so, with the extension name varying according to platform: .so, .sl, .a, and so forth.

On Windows, the default library location is %ORACLE_HOME%\bin\orasbt.dll.

If the database is unable to locate a media library specified by the SBT_LIBRARY parameter RMAN issues an ORA-27211 error and exits. Note that there is no default media management library file as part of standard database installation.

When channel allocation fails, the database writes a trace file to the USER_DUMP_DEST directory.

For ex:- SBT Initialize failed for /oracle/home/lib/libobk.so
 
3. Media Manager Library Integration

You need to define the media manager library , Media manager server, storage system, configure user…etc to configure. The PARMS parameter will be used to ALLOCATE or CONFIGURE CHANNEL and FORMAT and BACKUP commands will be used to backup. To limit backup piece sizes, use the parameter MAXPIECESIZE,

Example 1:-
RUN {
ALLOCATE CHANNEL C1 TYPE SBT_TAPE PARMS='SBT_LIBRARY=<media manager > lib/libddobk.so'; ENV=(STORAGE_UNIT=<storage_unit>,BACKUP_HOST=<backup host>)'
}

Example 2: -
RUN {
ALLOCATE CHANNEL C1 TYPE SBT_TAPE PARMS='SBT_LIBRARY=<media manager > lib/libddobk.so'; ENV=(NSR_SERVER=<Media Manager server>,NSR_CLIENT=<oracle Client> ,NSR_GROUP=<Tapes>)’
}

Note that the parameters will vary based upon Media Manager vendor

If the media manager could not be located user will receive below error message
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of allocate command on c1 channel at 12/10/2020 10:11:20
ORA-19554: error allocating device, device type: SBT_TAPE, device name:
ORA-27211: Failed to load Media Management Library
Additional information: 25

Once you configure the channel, you can run below commands to backup the database files.
To check SBT configuration
RMAN> SHOW CHANNEL FOR DEVICE TYPE SBT_TAPE;
To backup the control file to SBT_TAPE
RMAN> BACKUP DEVICE TYPE SBT_TAPE CURRENT CONTROLFILE;
Backup archived redo log backup to tape
RMAN> CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE

You can configure the default device to SBT_TAPE and FORMAT so that RMAN sends all backups to the media manager in specified format

RMAN> CONFIGURE DEFAULT DEVICE TYPE TO SBT_TAPE;
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT = '?/bkup_%U';

Once you configuring the default device all the backups should go to media manager:

RMAN> BACKUP CURRENT CONTROLFILE;
RMAN> BACKUP DATABASE;

Please see the below link to configure Oracle Backups using DDBoost Media Manager
Oracle RMAN backup using DataDomain-Boost

Thanks & Regards

Monday, May 10, 2021

Managing Immutable Tableas in ORacle 19c and Oracle 21c

Oracle 19c (19.11) introduced Immutable tables, these tables provides protection against unauthorized data modification that means these are read-only tables.

In order to use this feature user must set set the COMPATIBLE parameter set to 19.11.0
SQL> alter system set compatible='19.11.0' scope=spfile;

The following are NOT supported with immutable tables:
· Creating immutable tables in the CDB root or application root
· Online redefinition using the DBMS_REDEFINITION package
· Truncating the immutable table
· Flashback table
· Sharded tables
· Adding columns, renaming columns
· Dropping columns, and dropping partitions
· Updating rows, merging rows
· Logical Standby and Oracle GoldenGate
· Direct-path loading and inserting data using parallel DML
· Defining BEFORE ROW triggers that fire for update operations
· Creating Automatic Data Optimization (ADO) policies
· Creating Oracle Label Security (OLS) policies
· Transient Logical Standby and rolling upgrades
· Converting a regular table to an immutable table or vice versa

Creating Immutable tables

SQL> CREATE IMMUTABLE TABLE IMMU_TAB1 (
COLA NUMBER,
COLB VARCHAR2(15),
COLC DATE
) NO DROP UNTIL 3 DAYS IDLE
NO DELETE UNTIL 30 DAYS AFTER INSERT;

The user can able to drop table only after 3 days of inactivity and cannot deleted rows until 30 days after it has been inserted.

DML on Immutable Tables: Except insert, DML are not allowed
  • INSERT
SQL> INSERT INTO IMMU_TAB1 values ( 1,’TEST’,sysdate);
1 row inserted.
SQL> commit;
Commit complete.
  • DELETE
SQL> DELETE FROM IMMU_TABL1 WHERE COLA = 1;
Error report -
SQL Error: ORA-05715: operation not allowed on the blockchain or immutable table
  • UPDATE
SQL> update IMMU_TAB1 set COLB = ‘TEST2’ where COLA= 1;
Error report -
SQL Error: ORA-05715: operation not allowed on the blockchain or immutable table

DDL on Immutable Tables
  • TRUNCATE TABLE – This operation is now allowed as it deletes the rows
sql> TRUNCATE TABLE IMMU_TAB1;
Error report -
ORA-05715: operation not allowed on the blockchain or immutable table
  • ALTER RETENTION- User will get below error when try to reduce the retention period.
SQL> ALTER TABLE IMMU_TAB1 NO DROP UNTIL 2 DAYS IDLE;
Error report -
ORA-05732: retention value cannot be lowered

SQL> ALTER TABLE IMMU_TAB1 NO DELETE UNTIL 20 DAYS AFTER INSERT;
Error report -
ORA-05732: retention value cannot be lowered

SQL> ALTER TABLE IMMU_TAB1 NO DELETE;
Error report -
ORA-00600: internal error code, arguments: [atbbctable_1], [0], [], [], [], [], [], [], [], [], [], []

When trying to modify the rows to NO DELETE, we got ORA-600 error and it may be related to bug and contact Oracle for support.
  • MODIFY COLUMN - User can run below command to extend column length
SQL> ALTER TABLE IMMU_TAB1 MODIFY (COLB VARCHAR2(30));
Table IMMU_TAB1 altered.

  • DROP COLUMN - User will get below error when dropping column as this operation is not allowed
SQL> ALTER TABLE IMMU_TAB1 DROP COLUMN COLB;
Error report -
ORA-05715: operation not allowed on the blockchain or immutable table
  • ADD COLUMN - User will get below error when adding a new as this operation is not allowed
SQL> ALTER TABLE IMMU_TAB1 ADD (COLD VARCHAR2(10));
Error report -
ORA-05715: operation not allowed on the blockchain or immutable table

User can use DBMS_IMMUTABLE_TABLE.DELETE_EXPIRED_ROWS procedure to delete all rows that are beyond the specified retention period or obsolete

Example1: The below example deletes the rows that were created 31 days before the current system date.

DECLARE
NROWS NUMBER;
BEGIN
DBMS_IMMUTABLE_TABLE.DELETE_EXPIRED_ROWS('TESTUSR','IMMU_TAB1', SYSDATE-31, NROWS);
DBMS_OUTPUT.PUT_LINE('NO_OF_ROWS_DELETED=' || NROWS);
END;
/

Example2: The below procedure delete the rows that are beyond the retention period or obsolete
 
DECLARE
       NROWS NUMBER;
BEGIN
       DBMS_IMMUTABLE_TABLE.DELETE_EXPIRED_ROWS(
       SCHEMA_NAME   =>'TESTUSR',
       TABLE_NAME    => 'IMMU_TAB1',
       BEFORE_TIMESTAMP  => NULL,);
       DBMS_OUTPUT.PUT_LINE('NUMBER_OF_ROWS_DELETED=' || NROWS);
END;
/
  • DROP IMMUTABLE Table - The immutable table must be in the owner schema or must have the DROP ANY TABLE system privilege.
SQL> DROP TABLE IMMU_TAB1;

Oracle Database does not prevent flashback and point-in-time recovery operations to undo changes on immutable tables as these may be required to undo logical and physical corruptions. These recovery operations on a database undo the changes made to all tables, including immutable tables.

Note that retention policies in immutable tables relies on the system time.

Thanks & Regards,
http://oracleracexpert.com, Oracle ACE

Tuesday, April 27, 2021

Automatic Indexing in Oracle 19c using DBMS_AUTO_INDEX

Oracle 19c introduced new features automatic indexing, this feature creates and drops without dba intervention and takes care of index management

The DBMS_AUTO_INDEX package used to manage the automatic indexing feature. This feature evaluates the need for new indexes, creates when needed and drops then when they are no longer needed. It supports both single and multi-column index

Enable/Disable AUTO Index
To enable automatic indexing run below command
SQL> dbms_auto_index.configure ('AUTO_INDEX_MODE', 'IMPLEMENT');

To turn off or disable automatic indexing run below command
SQL> exec dbms_auto_index.configure('AUTO_INDEX_MODE','OFF');

To turn on automatic indexing, but new indexes remain invisible run below command
SQL> exec dbms_auto_index.configure('AUTO_INDEX_MODE','REPORT ONLY');

Note: The invisible Index feature introduced in Oracle 11g, these indexes are maintained like other indexes but ignored by the optimizer unless the he OPTIMIZER_USE_INVISIBLE_INDEXES parameter is set to TRUE

For ex:- SQL> create index INDX_01 on EMP (emp_name) invisible;

Auto Index Tablespace Management

The automatic indexes by default created in the default permanent tablespace, but you can you can specify a tablespace using the AUTO_INDEX_DEFAULT_TABLESPACE property

SQL> exec dbms_auto_index.configure('AUTO_INDEX_DEFAULT_TABLESPACE','TBS_AUTO_INDX');

Run below command with NULL to return using the default permanent tablespace.
SQL> exec dbms_auto_index.configure('AUTO_INDEX_DEFAULT_TABLESPACE',NULL);

User can specify threshold in percentage using below command. In this case only 60% of the tablespace is used for Auto Index
SQL> exec dbms_auto_index.configure ('AUTO_INDEX_SPACE_BUDGET', '60');

The autocreated indexes will be named with prefix SYS_AI. To find out these indexes there is a called “AUTO” added in DBA_INDEXES where you will see the value as “YES”

Automatic Index Management at Schema Level

You can control the SCHEMAS that’s should participate in Automatic Indexing feature by using the AUTO_INDEX_SCHEMA property.

SQL> exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA', ‘EMP’, allow => TRUE);

If ALLOW parameter is set to TRUE, then specified schema will be added to inclusion list and you can clear the inclusion list using below command. 

SQL>  exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA', NULL, allow => TRUE);

User can manually add the SCHEMAS to the exclusion list by setting ALLOW parameter to FALSE. SQL> exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA', 'TEST', allow => FALSE);

User can clear the exclusion list by running below command 
SQL> exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA', NULL, allow => FALSE);

Users can also use AUTO_INDEX_EXCLUDE_SCHEMA property to exclude specific schema.
SQL> exec dbms_auto_index.configure ('AUTO_INDEX_EXCLUDE_SCHEMA', ‘EMP’);

The manually created unused indexes never deleted by the automatic indexing process and they can be deleted using AUTO_INDEX_RETENTION_FOR_MANUAL. But user needs to mention after how many days the unused manual indexes can be dropped.

SQL> exec dbms_auto_index.configure ('AUTO_INDEX_RETENTION_FOR_AUTO', '50')

Views: Oracle offers several Views associated with automatic Indexing features
 
DBA_AUTO_INDEX_CONFIG - configuration settings related to automatic indexes
DBA_AUTO_INDEX_EXECUTIONS - The history of Automatic Indexing task executions
DBA_AUTO_INDEX_STATISTICS - Statistics related to automatic indexes
DBA_AUTO_INDEX_VERIFICATIONS- stats about PLAN_HASH_VALUE, AUTO_INDEX_BUFFER_GETS
DBA_AUTO_INDEX_IND_ACTIONS - Actions performed on automatic indexes
DBA_AUTO_INDEX_SQL_ACTIONS - Actions performed on SQL statements to verify automatic indexes

Thanks & Regards
http://oracleracexpert.com, Oracle ACE

Thursday, April 22, 2021

Create Recovery Catalog for RMAN

Recovery catalog schema used by RMAN to store one or more Oracle databases metadata. This info is already stored in control file of each Target Database and using Recovery catalog provides redundancy. In case, if target control file and all backups are lost then you can still get the info from Recovery catalog.

RMAN Recovery catalog also helps as centralized metadata location for all your databases and it helps easily report from once location. In addition, you can store metadata history longer than Control file.

You must register database in order to store metadata in Recovery catalog. it is highly recommended to use enable ARCHIVELOG mode for Recovery Catalog database

· Recovery Catalog metadata has following database file info
· Data file and archived redo log backup sets and pieces
· Data file and Archive log copies
· Database structure such as tablespaces and data files
· Stored scripts
· RMAN configuration settings

Creating Recovery Catalog

1. Connect to Recovery catalog DB as admin and create the Schema.

SQL> CREATE USER RCAT IDENTIFIED BY password
DEFAULT TABLESPACE RCATTBS
TEMPORARY TABLESPACE temp;

2. The RECVEROY_CATALOG_OWNER role is required to maintain and query, so grant the role to the Schema

SQL> GRANT RECOVERY_CATALOG_OWNER TO RCAT;

3. Connect to the database as the recovery catalog owner and run CREATE CATALOG command to create the catalog.

RMAN> CREATE CATALOG

You can also specify tableaspace name for the catalog.
RMAN> CREATE CATALOG TABLESPACE RCATTBS;

4. Connect to recovery Catalog and Register Target Database using below nelow command

$ rman TARGET / CATALOG RCAT@RCATDB;
RMAN> REGISTER DATABASE;

Examples for Data file copy, Archive log backup and backup pieces
RMAN>CATALOG DATAFILECOPY '/bkp/dbfile/testuser01.dbf';
RMAN>CATALOG ARCHIVELOG '/arc/arc_DB_1_559.dbf', '/arc/arc_DB_1_560.dbf',
RMAN>CATALOG BACKUPPIECE '/disk1/backups/backup_820.bkp';

Catalog multiple backup files in a directory by using one single command
RMAN> CATALOG START WITH '/bkp/dbfile/';

Refer below link for Virtual Private Catalogs
https://docs.oracle.com/en/database/oracle/oracle-database/19/bradv/managing-recovery-catalog.html#GUID-DA362C55-5B94-4AB8-A8A1-163BF08FE594

Thanks & Regards,
http://oracleracpert.com, Oracle ACE










Tuesday, April 20, 2021

Immutable tables in Oracle 19c or 21c

Oracle 19c (19.11) introduced Immutable tables, these tables provides protection against unauthorized data modification that means these are read-only tables. User can able add or insert the rows to the table but cannot able to modify. In order to manage the data the user must specify retention period for both immutable table and for the rows with in the table.

The immutable tables will have hidden columns and these are same as Block chain tables introduced in Oracle 21c. Based upon the retention period rows become obsolete in the tableau then only user can be able to delete the rows.

In order to use this feature user must set set the COMPATIBLE parameter set to 19.11.0
SQL> alter system set compatible='19.11.0' scope=spfile;

There are several data types are not supported with immutable tables ROWID, UROWID, LONG, object type, REF, varray, nested table, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, BFILE, and XMLType and XMLType tables are not supported.

Immutable tables contain system-generated hidden columns and they are the same as blockchain tables. When a row is inserted, a non-NULL value is set for the ORABCTAB_CREATION_TIME$ and ORABCTAB_USER_NUMBER$ columns,  value of remaining system-generated hidden columns is set to NULL.

When creating IMMUTABLE TABLE statement user should use below NO DROP, NO DELETE to specify retention period. The LOCKED keyword specifies table or rows retention setting cannot be modified.

1. NO DROP clause in CREATE IMMUTABLE TABLE statement for retention period
  • NO DROP : If you use this clause without any retention then then you cannot able to drop and user needs to be careful when using this clause
  • NO DROP UNTIL number DAYS IDLE : By using retention user can able to drop the but User cannot able to drop the table if newest row is less than n days old
To set the table retention period to 0 days, the initialization parameter BLOCKCHAIN_TABLE_MAX_NO_DROP must be set to 0. Pls note that the minimum value is 16

2. NO DELETE clause to specify the retention period for the rows
  • NO DELETE [LOCKED] – If you use without any retention then Rows cannot be deleted from the immutable table

  • NO DELETE UNTIL n DAYS AFTER INSERT [LOCKED] – if you use this clause the row cannot be deleted until n days after it was added.
User can use ALTER TABLE statement with the NO DELETE UNTIL clause to modify this setting and increase the retention period but cannot reduce the retention period. The minimum value for n is 16 days. If you use LOCKED then you cannot subsequently modify the row retention.

SQL> CREATE IMMUTABLE TABLE IMMU_TAB1 (
COLA NUMBER,
COLB VARCHAR2(15),
COLC DATE
) NO DROP UNTIL 3 DAYS IDLE
NO DELETE UNTIL 30 DAYS AFTER INSERT;

The user can able to drop table only after 3 days of inactivity and cannot deleted rows until 30 days after it has been inserted.

Use the DBMS_IMMUTABLE_TABLE.DELETE_EXPIRED_ROWS procedure to delete rows beyond the specified retention period or obsolete . Either SYS user or the owner of the schema can delete immutable table rows.

In order to DROP immutable table, it must be the owner of the schema or must have the DROP ANY TABLE system privilege.

SELECT row_retention "Row Retention Period", row_retention_locked "Row Retention Lock", table_inactivity_retention "Table Retention Period" FROM dba_immutable_tables
WHERE table_name = 'IMMU_TAB1';

Row Retention Period Row Retention Locked Table Retention Period
-------------------- -------------------- ----------------------
30 NO 3

User can query {CDB|DBA|ALL|USER}_IMMUTABLE_TABLES views information about immutable tables.

Regards,
http://oracleracexpert.com, Oracle ACE

Monday, April 19, 2021

Renaming or moving Oracle Control files and redo log files

When renaming Oracle control files and redo log files user needs to be very careful and must take latest database backup to restore in case of any user mistakes.

Control File: The current location of the control files can be queried from the V$CONTROLFILE view

SQL> select NAME from v$controlfile;
NAME
--------------------------------------------------
/oracle/TDB/controlfile/TDB01.ctl
/oracle/TDB/controlfile/TDB02.ctl
/oracle/TDB/controlfile/TDB03.ctl

To rename the control file location we must alter control_files parameter in the spfile/pfile. Follow below steps
  •  Alter the control_files parameter
ALTER SYSTEM SET control_files='/dbf1/TDB/controlfile/TDB01.ctl', '/dbf2/TDB/controlfile/TDB02.ctl','/dbf3/TDB/controlfile/TDB03.ctl' SCOPE=SPFILE;
  • Shutdown the database and copy or move the files to new location
SQL> SHUTDOWN IMMEDIATE
SQL> ! mv /oracle/TDB/controlfile/TDB01.ctl /dbf1/TDB/controlfile/TDB01.ctl
SQL> ! mv /oracle/TDB/controlfile/TDB02.ctl /dbf2/TDB/controlfile/TDB02.ctl
SQL> ! mv /oracle/TDB/controlfile/TDB03.ctl /dbf3/TDB/controlfile/TDB03.ctl
  • Startup the database and verify new location

SQL> Startup
SQL> select NAME from v$controlfile;
NAME
--------------------------------------------------
/dbf1/TDB/controlfile/TDB01.ctl
/dbf2/TDB/controlfile/TDB02.ctl'
/dbf3/TDB/controlfile/TDB03.ctl'

Redo log: The current redo log files location can be queried from the V$logfile view

SQL> SELECT member FROM v$logfile;

MEMBER
---------------------------------------------------------------------
/oracle/TDB/TDB11.rdo
/oracle/TDB/TDB12.rdo
/oracle/TDB/TDB21.rdo
/oracle/TDB/TDB22.rdo

Follow the below steps to move or rename a Redo log file
  • Shutdown the Db and rename the file at operating system
SQL> Shutdown Immediate
SQL> ! /oracle/TDB/TDB11.rdo /dbf1/TDB/redo/TDB11.rdo
SQL> ! /oracle/TDB/TDB12.rdo /dbf1/TDB/redo/TDB12.rdo
SQL> ! /oracle/TDB/TDB21.rdo /dbf1/TDB/redo/TDB21.rdo
SQL> ! /oracle/TDB/TDB22.rdo /dbf1/TDB/redo/TDB22.rdo
  • Start the database in mount mode and ALTER DATABASE RENAME FILE
SQL> Startup mount
SQL> ALTER DATABASE RENAME FILE '/oracle/TDB/TDB11.rdo’ to '/dbf1/TDB/redo/TDB11.rdo’;
SQL> ALTER DATABASE RENAME FILE '/oracle/TDB/TDB12.rdo’ to ‘/dbf1/TDB/redo/TDB12.rdo’;
SQL> ALTER DATABASE RENAME FILE '/oracle/TDB/TDB21.rdo’ to ‘/dbf1/TDB/redo/TDB21.rdo’;
SQL> ALTER DATABASE RENAME FILE '/oracle/TDB/TDB22.rdo’ to ‘/dbf1/TDB/redo/TDB22.rdo’;
  • Open the database and verify
SQL> Alter database open;
SQL> SELECT member FROM v$logfile;

MEMBER
---------------------------------------------------------------------
/oracle/TDB/TDB11.rdo
/oracle/TDB/TDB12.rdo
/oracle/TDB/TDB21.rdo
/oracle/TDB/TDB22.rdo

We can also DROP and RECREATE the redo in different location. But make sure the group STATUS should be “INACTIVE” in order to drop.
  • You can check the redo status from the V$log view
SQL> select group#, status from v$log;
GROUP# STATUS
---------- ----------------
1 CURRENT
2 INACTIVE
  • Drop and recreate the redo log group with new location
SQL> ALTER DATABASE DROP LOGFILE GROUP 2;
SQL> ALTER DATABASE ADD LOGFILE GROUP 2 (‘/dbf1/TDB/redo/TDB21.rdo’, ‘/dbf1/TDB/redo/TDB22.rdo’) SIZE 100M;
  • Switch the log file to change the current redo and recreate the other redo groups
SQL> Alter system switch log file;
SQL> ALTER DATABASE DROP LOGFILE GROUP 1;
SQL> ALTER DATABASE ADD LOGFILE GROUP 1 (‘/dbf1/TDB/redo/TDB11.rdo’, ‘/dbf1/TDB/redo/TDB12.rdo’) SIZE 100M;

Thanks & Regards.
http://oracleracexpert.com, Oracle ACE

Wednesday, April 14, 2021

ORA-20011: Approximate NDV failed: ORA-08103: object no longer exists

The ORA-20011, can be found in alert.log and this error occurs when DBMS_STATS:GATHER_STATS_JOB gathering stats

ORA-20011: Approximate NDV failed: ORA-08103: object no longer exists

You identify the issue set the following trace events
SQL> alter session set events '10384 trace name context forever , level 16384';

After research found that object truncate and starts are gathering at same time. In case of partition set granularity to ‘PARTITION’

DB level
SQL> exec dbms_stats.set_global_prefs(pname=>'GRANULARITY',pvalue=>'PARTITION')

Table level
SQL> exec dbms_stats.set_table_prefs(ownname=>'Owner_Name',tabname=>'<Table_Name>',pname=>'GRANULARITY',pvalue=>'PARTITION')

Run the gather stats after DDL or DML have been completed.

Also note that “ORA-08103 object no longer exists” error occurs when another user has deleted object since the operation began. In case of incomplete recovery of the object, the work around would be to remove references to the object or delete the object.

When using Global temporary table with ON COMMIT DELETE ROWS options, may encounter ORA-08103: object no longer exists when commit statement that followed right after the delete statement.

In this case recreation of global temporary table with ON COMMIT PRESERVE ROWS clause will helps to safely fetch data

There are several bugs related to ORA-8103 error, check oracle support for more details.

Bug 13618170
Bug 5523799
Bug 5637976

Thanks & Regards,
http://oracleracexpert.com, Oracle ACE

Thursday, March 25, 2021

ORA-39346: data loss in character set conversion for object TABLE_STATISTICS

When doing export/import using data pump encountered below error. This happens when invalid or corrupt characters are stored in the database.

When using export/import instead of data pump, no errors reported.

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-39346: data loss in character set conversion for object TABLE_STATISTICS:"ORCL"."TABLE1"
ORA-39346: data loss in character set conversion for object TABLE_STATISTICS:"ORCL"."TABLEMAT"
Job "SYS"."SYS_IMPORT_SCHEMA_01" completed with 2 error(s) at Sun Apr 11 12:05:25 2021 elapsed 0 00:20:13

After research found that this is a known issue due to Bug 13696958 .

The fix for Bug 13696958 

added warning messages in 12.1 datapump import for characters that couldn't be converted. Previously, a fix for Bug 16173664 was made available to report the affected object in the import log.

Workaround/fix:-

Needs to apply interim patch 21342624 
and execute post-install step:

$ datapatch

If the patch 21342624 installed then impdp log will report the objects that are experiencing data loss during characterset conversion. If the fix then character data is checked even when the client and database character sets are the same, to prevent input of invalid character data.

That being said the fix for Bug 21342624 does not stop the error messages or repair any bad data on the source database

In case of any further issues , patch questions or concerns, please contact Oracle Support.

Refer Oracle support notes: -

Unpublished Bug 21342624 - DATA LOSS DUE TO CHARACTER SET NOT SHOWING CORRECT SET OF OBJECTS DURING IMPORT.

Note 1317012.1 - How To Use MOS Patch Planner To Check And Request The Conflict Patches?

Thanks & Regards,
http://oracleracexpert.com, Oracle ACE

Wednesday, March 10, 2021

How to reset SYS, SYSTEM or User password or grant SYSDBA, SYSOPER privilege

This article helps to reset SYS, SYSTEM or any other user password and also grant admin privileages. There are different methods to rest the password in Oracle.

As SYSDBA or DBA privilege - As long as you have SYSDBA or DBA privilege you should be able to reset any user password.

$ sqlplus / as sysdba
SQL> alter user <username> identified by <new password>;
For ex:- alter user sys identified by xxxaaxxx;
For ex: - alter user EMP identified by xxssxxxx;

Orapwd Utility – This is very useful utility to rest the password. This utility helps in granting SYSOPER, SYSDBA privileges. Creating orapwd enables users to connect with admin privileges. From Oracle 11g the passwords are case sensitive.

orapwd file=orapwd<SID> password=password entries=max_users

In order Oracle to check for password file the REMOTE_LOGIN_PASSWORDFILE must be set to either SHARED or EXCLUSIVE

SHARED - More than one database can use a password file. But only SYS user recognized by the password file
EXCLUSIVE - The password file can be used by only one database and the password file can contain names other than SYS.

SQL> alter system set remote_login_passwordfile =’EXCLUSIVE’;
SQL> show parameter password
NAME TYPE VALUE
----------------------------- ----------- ----------
remote_login_passwordfile string EXCLUSIVE

You can also grant SYSDBA, SYSOPER privileges to user by logging as SYSDBA. Once the privilege is granted, the users will be added to password file

SQL> grant sysdba to AdminUser;
SQL> grant sysoper to DBAUser;

To see who are all the users has sysdba, sysoper privilege run below command. You can also see the users listed in password file

SQL> select * from v$pwfile_users;

Oracle has listed several articles how to recover LOST password and resolve issues connecting as YSSDBA.
 
How to Set up the Oracle Password File MOSC Note: 1029539.6
Checklist for Resolving Connect AS SYSDBA Issues MOSC Note: 69642.1
How To Recover From Lost SYS Password MOSC Note: 805084.1

Thanks & Regards,
http://oracleracexpert.com





Wednesday, February 10, 2021

Patching Oracle Database Platform using Opatch or MOPatch

When applying the Oracle patches, make sure you follow the processes pre-checks, patch execution and post checks.

In SAP environment, MOPatch is very helpful and it has bundle patchs (SBP). Using this tool we can install multiple patches at single run, it will automate the oracle patching processes. However, note that MOPatch internally calls OPatch to install patches.

Pre-checks
  • Make sure you have enough space in $ORACLE_HOME file system
  • Take a Tar or backup of $ORACLE_HOME
tar cvf - $ORACLE_HOME/12201 | gzip > 12201.101020.tar.gz
  • Install latest Opatch and MOpatch (in case of SAP)
$ mv $ORACLE_HOME/OPatch $ORACLE_HOME/OPatch-pre-SBP_12201201020_101020
$ unzip -qd $ORACLE_HOME OPATCH12201P_2008-70001555.ZIP -x SIGNATURE.SMF
$ unzip -qd $ORACLE_HOME/sapbundle SAP12201P_2011-70001555.ZIP 'SBP_12201201020_101020/MOPatch/*'
$ test -d $ORACLE_HOME/MOPatch && mv $ORACLE_HOME/MOPatch $ORACLE_HOME/MOPatch-pre-SBP_12201201020_101020
$ mv $ORACLE_HOME/sapbundle/SBP_12201201020_101020/MOPatch $ORACLE_HOME/MOPatch
  • check the version of MOPatch & Opatch version
$ORACLE_HOME/MOPatch/mopatch.sh –h
$ORACLE_HOME/OPatch/opatch version

Execution
  • Stop all database applications
  • Backup the database and related files
  • Shut down the databases, listeners, and other processes running from the Oracle Home, use the fuser OS command to check for stale SQL*Plus sessions: $SBPFUSER $ORACLE_HOME/bin/oracle
  • Install the patch –
For SAP systems using MOPatch
env ORACLE_HOME=$ORACLE_HOME $ORACLE_HOME/MOPatch/mopatch.sh -v -s SAP12201P_2011-70001555.ZIP

You should see a message
MOPatch completed successfully
Overall Status : COMPLETE

For Other Oracle Systems using Opatch
$ cd /opt/patches/<patch number>
$ opatch apply

You should see a message
Opatch completed successfully
Overall Status : COMPLETE

Post-Checks
  • Start the database and verify the database processes and logs. The logs should not have any error messages
  • Run the post patch update steps for SAP system 
$ORACLE_HOME/sapbundle/catsbp
  • Apply _FIX_CONTROL, EVENT parameters if required.
Where _FIX_CONTROL, initialization parameter will give control over specific features and bug fixes. However, it should be set under the direction of Oracle Corporation

Ex:- ALTER SYSTEM SET "_FIX_CONTROL"='4545454:ON','4547778:OFF',……. COMMENT='SAP_12201201020_101020' SCOPE=SPFILE;

Where EVENT, initialization parameter will be used to generate information in form of a trace file in the context of the event. The trace file is located in a directory specified by the parameter USER_DUMP_DEST

ALTER SYSTEM SET EVENT= '10027','10028', '10142' COMMENT='SAP_12201171017_101020' SCOPE=SPFILE;
  • Restart the database and Take a backup
  • Start the application and check the application
SAP Oracle Patches Reference
  • Known issues related to the installation of Database Release Update 12.2.0.1.201020 and the other patches contained in this SBP are described in SAP Note 2507228 Database: Patches for 12.2.0.1.
  • Known issues related to the Oracle installation utilities, including MOPatch, OPatch, and the Oracle Universal Installer, are described in SAP Note 1915299 Troubleshooting Software Installation (12.1/12.2).
Oracle Patches reference

Please refer Oracle documentation
https://docs.oracle.com/cd/E24628_01/doc.121/e39376/opatch_overview.htm
Oracle Help Center
https://docs.oracle.com/cd/E25290_01/doc.60/e25224/patchesupdates.htm

Thursday, January 14, 2021

ORA-04031: unable to allocate nn bytes of shared memory

You will receive ORA-04031 error when allocated memory is small too small and does not have enough system global area (SGA).

Recently I got below error...

ORA-04031: unable to allocate 63176 bytes of shared memory ("shared pool","DBMS_STATS_INTERNAL","PLMCD^60dfd26c","BAMIMA: Bam Buffer") < ORA-04031: unable to allocate 63176 bytes of shared memory ("shared pool","DBMS_STATS_INTERNAL","PLMCD^60dfd26c","BAMIMA: Bam Buffer") < ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_STATS_INTERNAL"
< ORA-06512: at line 1
< ORA-04031: unable to allocate 63176 bytes of shared memory ("shared pool","DBMS_STATS_INTERNAL","PLMCD^60dfd26c","BAMIMA: Bam Buffer")

Please find the cause and action

ORA-04031: unable to allocate nn bytes of shared memory
Cause: More shared memory is needed than was allocated in the shared pool.
Action: If the shared pool is out of memory, either use the DBMS_SHARED_POOL package to pin large packages, reduce your use of shared memory, or increase the amount of available shared memory by increasing the value of the initialization parameters SHARED_POOL_RESERVED_SIZE and SHARED_POOL_SIZE. If the large pool is out of memory, increase the initialization parameter LARGE_POOL_SIZE. If the error is issued from an Oracle Streams or XStream process, increase the initialization parameter STREAMS_POOL_SIZE or increase the capture or apply parameter MAX_SGA_SIZE.


I will explain some of the possible root causes and how to fix it.

1. Insufficient RAM – This is most common issue when the database usage increased over the period. Increase the RAM and also adjust related SGA_MAX_TARGET or MEMORY_MAX_SIZE in case of AMM.

2. Shared pool fragmentation – This can be addressed by increasing the shared_pool_size in spfile or init.ora parameter. If you cannot bounce the database for changes to effect then I flush shared pool using “alter system flush shared pool;” command for temporary fix

3. Pinned packages – You will see this error if you have pinned lots of packages with dbms_shared_pool.keep and DB does not have enough resources

If your application is not using bind variables then it will cause SGA to become fragmented. In this case, if you turn on cursor_sharing it will help. Also make sure you use bind varibales. 


Also, run below queries to get SGA/PGA memory advice.
select * from v$sga_target_advice; select * from v$pga_target_advice;

Refer:
MOSC notes 146599.1 and 396940.1 for more details for resolving the ORA-04031 error:

Regards,
http://oracleracexpert.com