Tag: database

I was recently playing with the new AI Vector Search capabilities of Database 23ai, when I did something a little daft.

Without really thinking about what I was doing, I increased the memory allocated to vector_memory_size. “Ok” you say, “Whats wrong with that?” At face value nothing, I changed a parameter. But as I didn’t think about the any of the other memory parameters, like sga_max_size and sga_max_target, I set myself up for an inevitable problem if I restarted the database, or the VMs I was using were restarted.

And restart the database (and the VMs) I did – or tried.

And restart the database did not.

Without worrying about how I messed up, lets cover how I got out of this pickle.

First, as the title indicates, I’m working on an Exadata that has been deployed with the awesome (if I do say so myself) Exascale technology. You can check out more about Exascale on the Exadata PM blog. For now, know that at its core is a new decoupled storage management system that manages storage resources globally, as in for many database or VM clusters, rather than cluster-by-cluster. It has similarities to ASM, but introduces a ton of new capabilities including resource pooling, and very cool database instant snapshot and cloning features. More on those another time. Back to my “broken” database.

As my database won’t start – not even in mount mode – I can’t easily update the vector_memory_size parameter. So I need to do the following:

1. Extract the spfile from Exascale as a text file
2. Remove or change the vector_memory_size parameter
3. Create an spfile from the edited text file
4. Start the database

Exascale aside, I sure most if not all DBA’s have had to do this at some point in their careers.

---

---

Step 1 – get the spfile!

SQL*Plus and sqlcl make this easy. Simply fire up your chosen utility and

SQL> create pfile='/tmp/pfile.ora' from spfile;

That gets us our parameters out of Exascale into an easily editable text file.

---

Step 2 – edit the pfile!

Again, this is pretty simple. Start your text editor of choice, e.g. vi, emacs, nano, pico, etc, and make the required changes to the file you created in the last step.

In my case, rather than remove or downsizing the memory allocation for vector_memory_size, I’m going to up-size my SGA.

For the sake of comparison, this was the SGA size before I shotdown the database and realized what I’d done.

SQL> show sga

Total System Global Area 6439969072 bytes
Fixed Size		    5439792 bytes
Variable Size		 2030043136 bytes
Database Buffers	 2248146944 bytes
Redo Buffers		    8855552 bytes
In-Memory Area		 2147483648 bytes
Vector Memory Area	 2147483648 bytes

---

Step 3 – (re)create the spfile from the edited pfile!

Now we have a pfile, we need to re-create the spfile in Exascale so both instances in my cluster can access it.

First, we’ll create an spfile from the pfile in SQL*Plus or sqlcl

SQL> create spfile from pfile='/tmp/pfile.ora'

This will create an spfile in the $ORACLE_HOME/dbs directory of the database VM you are currently on. In my case, its called $ORACLE_HOME/dbs/spfilec8db12.ora

$ ls -al $ORACLE_HOME/dbs/spfilec8db12.ora
-rw-r----- 1 oracle dba 8704 May 27 22:12 /u01/app/oracle/product/23.7.0.25/dbhome_1/dbs/spfilec8db12.ora

Next, we need to backup the existing spfile. I may be a Product Manager these days, but good old DBA instincts are hard to suppress.

First, lets get the location of our databases spfile using srvctl.

$ srvctl config database -db C8DB1|grep -i spfile
Spfile: @exapm13050608vault/EXAPM13050608-82B1CA80638E4F86BF2E4B3EABC7B6E7/C8DB1/PARAMETERFILE/spfile.OMF.628AA9E0

Exascale includes a new utility called XSH – the Exascale Shell.

XSH includes a cp command that allows us to copy files in and out of Exascale storage. ASM has a similar capability.

Using XSH, we’ll copy our file current spfile to a safe location.

$ xsh cp @exapm13050608vault/EXAPM13050608-82B1CA80638E4F86BF2E4B3EABC7B6E7/C8DB1/PARAMETERFILE/spfile.OMF.628AA9E0 /tmp

$ ls -al spfile.OMF.628AA9E0
-rw-rw---- 1 oracle oinstall 8704 May 27 22:15 spfile.OMF.628AA9E0

Now we can copy our new spfile back into Exascale using XSH. Note in the command below, tell XSH that its copying in an ‘init‘ file with the --ftype attribute.

$ xsh cp --ftype init $ORACLE_HOME/dbs/spfilec8db12.ora @exapm13050608vault/EXAPM13050608-82B1CA80638E4F86BF2E4B3EABC7B6E7/C8DB1/PARAMETERFILE/spfile.OMF.628AA9E0

$ xsh ls -l @exapm13050608vault/EXAPM13050608-82B1CA80638E4F86BF2E4B3EABC7B6E7/C8DB1/PARAMETERFILE/spfile.OMF.628AA9E0
            8704             Mar 10 15:24:05 2025 @exapm13050608vault/EXAPM13050608-82B1CA80638E4F86BF2E4B3EABC7B6E7/C8DB1/PARAMETERFILE/spfile.OMF.628AA9E0

---

Step 4 – restart the database!

Finally, use srvctl to restart the database

$ srvctl start database -db C8DB1

Success! The database is back up and running.

Comparing the SGA now with the earlier one, you can see I added 4 GB to the sga_max_size and sga_target being the total to 10 GB.

SQL> show sga

Total System Global Area   10736573488 bytes
Fixed Size                     7076912 bytes
Variable Size               2516582400 bytes
Database Buffers            3909091328 bytes
Redo Buffers                   8855552 bytes
In-Memory Area              2147483648 bytes
Vector Memory Area          2147483648 bytes

---

And that’s it. You can copy spfiles in and out of Exascale. In reality, you can copy many more file types in and out – check out the XSH – the Exascale Shell command reference for all the details.