Oracle Database Parameters Change

Hi,

In this article, I will continue to explain the parameter files and their changing.

spfile

Because the Spfile file does not contain text content, you cannot open the file and change it directly, as in pfile. You can change any parameter from SQL * Plus as follows if database using spfile.

 

[oracle@MehmetSalih ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Mar 19 15:57:55 2019

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show parameter log_archive_max_processes;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_max_processes            integer     4
SQL>


  SQL>
SQL> alter system set log_archive_max_processes=20 scope=BOTH;

System altered.

SQL> show parameter log_archive_max_processes;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_max_processes            integer     20
SQL>
SQL>

Since the above parameter is a dynamic parameter, we can directly change the parameters using scope=BOTH option and see the effect immediately. But some static parameters are not changed like above with both option.

You can change this parameters with scope=spfile option and see its effect after restart.

When we type the Scope parameter BOTH, we immediately change it, the dynamic parameters change as above. If you change the static parameters in this way, it gives an error and we can change these parameters by using Scope =spfile.  You can see the effect of these parameters when restarting the system.

 

[oracle@MehmetSalih ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Mar 19 16:05:39 2019

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show parameter processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     1
asm_io_processes                     integer     20
db_writer_processes                  integer     1
gcs_server_processes                 integer     0
global_txn_processes                 integer     1
job_queue_processes                  integer     4000
log_archive_max_processes            integer     20
processes                            integer     300
SQL>
SQL>
SQL>
SQL> alter system set processes=1000 scope=BOTH;
alter system set processes=1000 scope=BOTH
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified


SQL>
SQL> alter system set processes=1000 scope=spfile;

System altered.

SQL>
SQL> show parameter processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     1
asm_io_processes                     integer     20
db_writer_processes                  integer     1
gcs_server_processes                 integer     0
global_txn_processes                 integer     1
job_queue_processes                  integer     4000
log_archive_max_processes            integer     20
processes                            integer     300
SQL>


  restart database and see the effect.

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

Total System Global Area  534462464 bytes
Fixed Size                  2230072 bytes
Variable Size             339740872 bytes
Database Buffers          184549376 bytes
Redo Buffers                7942144 bytes
Database mounted.
Database opened.
SQL> show parameter processes;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     1
db_writer_processes                  integer     1
gcs_server_processes                 integer     0
global_txn_processes                 integer     1
job_queue_processes                  integer     1000
log_archive_max_processes            integer     20
processes                            integer     1000
SQL>

 

In addition, if you are doing a critical change in the spfile, you can guarantee yourself to create pfile from spfile. You have taken backup of spfile with this method.
[oracle@MehmetSalih ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Mar 19 16:05:39 2019

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>  create pfile='/home/oracle/initDEVECI.ora' from spfile;

File created.

SQL> exit

[oracle@MehmetSalih ~]$ ls -l initDEV*
-rw-r--r-- 1 oracle oinstall 1450 May 6 11:46 initDEVECI.ora

 

 

 

 

 

Mehmet Salih Deveci

I am Founder of IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress mehmetsalih.deveci@outlook.com.-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  mehmetsalih.deveci@outlook.com a mail atabilirsiniz.

2 thoughts on “Oracle Database Parameters Change

  • March 24, 2019 at 7:19 pm
    Permalink

    Thanks for your blog post. The things i would like to add is that computer memory is required to be purchased if the computer cannot cope with whatever you do by using it. One can set up two random access memory boards having 1GB each, in particular, but not certainly one of 1GB and one having 2GB. One should check the manufacturer’s documentation for the PC to make sure what type of memory it can take.

    Reply
  • June 12, 2019 at 11:09 am
    Permalink

    The articles published from you is greate. Thank you.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *