I have a procedure for changing memory parameters for an Oracle RDBMS database instance.
The reason for this complex procedure is that I have a standard approach to updating parameters that is resilient and repeatable. Although the judicious use of the ALTER SYSTEM SET command can achieve the same results, I still have painful memories of being caught out by bugs and misunderstandings.
The outline of the procedure is:
- CREATE PFILE=’/tmp/initorcl_old.ora’ FROM SPFILE;
- cp /tmp/initorcl_old.ora /tmp/initorcl_new.ora
- vi /tmp/initorcl_new.ora
- startup nomount pfile=/tmp/initorcl_new.ora
- CREATE SPFILE=’?/dbs/spfileorcl.ora’ FROM PFILE=’/tmp/initorcl_new.ora’;
I create a text version of the system parameter file; edit the copy with the new memory parameters; start up the instance in NOMOUNT in order to verify the parameters; and overwrite the system parameter file with the text file.
NOTE: There are missing steps in the above procedure, and the names have to be changed to match the operating system, and environment.
SEE: Starting Up a Database for more details.