How to set and check the max size of PLUGGABLE DATABASE STORAGE

Hello Friend's,

In This Post we will discuss about How to set and check the max size of PLUGGABLE DATABASE STORAGE

So let's get started

When creating a Pluggable Database (PDB) in a Container Database (CDB), you can set its maximum size limit using the STORAGE clause in the CREATE PLUGGABLE DATABASE command. If you need to adjust this limit later, you can use the ALTER PLUGGABLE DATABASE query. To alter a PDB's storage limit, you must connect to the PDB and issue the ALTER DATABASE command with the appropriate parameters. This is the only ALTER PLUGGABLE DATABASE operation that requires dedicated execution from within the PDB. The other ALTER PLUGGABLE DATABASE operations can be performed remotely without connecting to the PDB. This capability ensures backward compatibility for applications that have been migrated to a CDB environment.

Default of MAX_PDB_STORAGE is no limit.

Run following sql in current PDB to limit the size of all datafiles of that PDB:


ALTER PLUGGABLE DATABASE STORAGE (MAXSIZE 25G);

SQL> set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a30
 select INST_ID,INSTANCE_NAME, name DB_NAME,HOST_NAME,DATABASE_ROLE,OPEN_MODE,version DB_VERSION,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,gv$instance;SQL> SQL> SQL> 

   INST_ID INSTANCE_NAME    DB_NAME   HOST_NAME 		     DATABASE_ROLE    OPEN_MODE  DB_VERSION	   LOGINS     DB UP TIME
---------- ---------------- --------- ------------------------------ ---------------- ---------- ----------------- ---------- -----------------------------
	 1 CDBFIN	    CDBFIN    dm01db01.database.com	     PRIMARY	      READ WRITE 19.0.0.0.0	   ALLOWED    06-APR-2024 21:35:08

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 FINDB			  MOUNTED
SQL> alter pluggable database FINDB open read write;

Pluggable database altered.

SQL> alter session set container=FINDB;

Session altered.

SQL> ALTER PLUGGABLE DATABASE STORAGE (MAXSIZE 25G);

Pluggable database altered.


To verify the setting, run below in current PDB:


SQL> select (PROPERTY_VALUE/1024/1024/1024) GB FROM database_properties WHERE property_name = 'MAX_PDB_STORAGE';

	GB
----------
	25

To check every PDB's setting, run below in CDB:


SELECT property_name,
       property_value,
       description,
       con_id
FROM   cdb_properties
WHERE  property_name = 'MAX_PDB_STORAGE'; 

Storage limits for Pluggable Databases (PDBs), such as MAX_PDB_STORAGE and MAX_SHARED_TEMP_SIZE, are also maintained within the central properties (CDB_PROPERTIES) of the containing Consolidated Database (CDB).


SQL> col name for a34
SQL> col MAX_SIZE for a34
SQL> SELECT name,
       total_size,
       Nvl(property_value, 'UNLIMITED') AS "MAX_SIZE"
FROM   v$containers c,
       cdb_properties p
WHERE  p.con_id(+) = c.con_id
AND    p.property_name(+) = 'MAX_PDB_STORAGE';  2    3    4    5    6    7  

NAME				   TOTAL_SIZE MAX_SIZE
---------------------------------- ---------- ----------------------------------
FINDB				   1380974592 26843545600
CDB$ROOT				    0 UNLIMITED
PDB$SEED			   1354760192 UNLIMITED

The MAX_SIZE in V$PDBS shows the initial MAXSIZE set by "ALTER PLUGGABLE DATABASE STORAGE." However, it excludes the discount rate (_pdb_max_size_discount, typically 10%). This discount is used internally to calculate the actual maximum size. To get the true MAXSIZE with the discount applied, use the ACTUAL_MAX_SIZE column from X$CON.


SELECT actual_max_size
FROM   x$con; 

ACTUAL_MAX_SIZE
---------------
	      0
	      0
     2.9528E+10


SELECT total_size,
       max_size,
       ( max_size + ( max_size * 0.10 ) ) AS REAL_MAX_SIZE
FROM   v$pdbs; 

TOTAL_SIZE   MAX_SIZE REAL_MAX_SIZE
---------- ---------- -------------
1354760192	    0		  0
1380974592 2.6844E+10	 2.9528E+10


Adding space to a pluggable database (PDB) beyond its maximum size (ACTUAL_MAX_SIZE) will result in the ORA-65114 error.

This error generally comes when total size of PDB after resize of the datafile is more than the size you have allocated to a PDB while creating.

e.g :

SQL> ALTER DATABASE DATAFILE '+DATA' RESIZE 4g;
ALTER DATABASE DATAFILE '+DATA' RESIZE 4g
*
ERROR at line 1:

ORA-65114: space usage in container is too high

If you face this error you can revert the same using below steps .

You can modify the allocation storage for a Pluggable Database (PDB) and subsequently attempt to alter the size of its data file by logging on to the PDB.


SQL> alter session set container=FINDB;

Session altered.

Configure the storage allocation as either a specific value or set it to an unbounded limit.


SQL> ALTER PLUGGABLE DATABASE STORAGE UNLIMITED;

Pluggable database altered.
Previous
Next Post »