Analyticsandthecloud

TYPES OF PARAMETER

DYNAMIC PARAMETER

   Changes takes effect immediately.

   No need to take the database offline

STATIC PARAMETER

    Changes only takes effect after restart of the database

How to see the parameters

SQL> desc v$parameter;

How to know which parameter is dynamic or static

SQL> SELECT NAME, ISSYS_MODIFIABLE FROM v$parameter WHERE NAME=’processes’;

NAME

——————————————————————————–

ISSYS_MOD

———

processes

FALSE

SQL> col name for a30;

SQL> /

NAME                           ISSYS_MOD

—————————— ———

processes                      FALSE

FALSE means changes will not take place immediately

SCOPE = SPFILE — Changes happens to the spfile but not the current spfile until the database is restarted

SCOPE = MEMORY — Changes happens on the current memory

SCOPE = BOTH  — Used only for dynamic parameters.

–SPFILE

SQL> show parameter spfile;

NAME                                 TYPE        VALUE

———————————— ———– ——————————

spfile                               string

The above result shows that my database started with a pfile

–To make the database to start implicitly with spfile, create spfile from pfile,

   shutdown the database gracefully and startup the database again.

SQL> show parameter spfile;

NAME                                 TYPE        VALUE

———————————— ———– ——————————

spfile                               string      /u01/app/oracle/product/19.0.1

                                                 /dbhome_1/dbs/spfilePBSDB.ora

Now you can see the location of your spfile

Open the spfile to see its content:

oracle@node1.cyarnitech.org(PBSDB): vi /u01/app/oracle/product/19.0.1/dbhome_1/dbs/spfilePBSDB.ora.

It is a binary file and changes can’t be made directly on it else it will be corrupted .

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     40

log_archive_max_processes       integer     4

processes                                        integer     300

type

CHANGIMG PARAMETER VALUE

Example: Change the value of processes

Let’s assume that where’re experiencing high volume of transactions and want to accommodate the new volume.

SQL> ALTER SYSTEM SET processes=400 SCOPE=SPFILE;

SQL> Shu immediate

SQL> col name for a30;

SQL> select name,ISSYS_MODIFIABLE from v$parameter where name=’open_cursors’;

NAME                           ISSYS_MOD

—————————— ———

open_cursors                   IMMEDIATE

 IMMEDIATE means parameter is dynamic. Applied changes will take immediate effect on memory and in spfile.

SQL> ALTER SYSTEM SET open_cursors=500 scope=both;

SQL> ALTER SYSTEM SET open_cursors=500 scope=memory;

System altered.

Changes will immediately take place in the Memory and go back to previous value after restart.

SQL> show parameter open_cursors;

NAME                                 TYPE        VALUE

———————————— ———– ——————————

open_cursors                         integer     500

CONTROL FILE (Control file is a static parameter)

–Has location of data files

–Has location of Online Redo Log files

–RMAN backup information is stored in control file

–Keeps information about DB Name

For the above reasons, it is best practice to multiplexed CONTROL FILE

–Changes are not applied directly to control file. Changes made to the data file are also stored in the control file.

ADD A CONTROL FILE

— Database must be taken offline/ shutdown before adding a control file

SQL> show parameter control_files;

NAME                                 TYPE        VALUE

———————————— ———– ——————————

control_files                        string      /u01/app/oracle/oradata/PBSDB/

                                                 control01.ctl, /u01/app/oracle

                                                 /oradata/PBSDB/control02.ctl

SQL> select name,ISSYS_MODIFIABLE from v$parameter where name=’control_files’;

NAME                           ISSYS_MOD

—————————— ———

control_files                  FALSE (Static parameter)

ALTER SYSTEM SET CONTROL_FILES=’/u01/app/oracle/oradata/PBSDB/ control01.ctl’,’/u01/app/oracle/oradata/PBSDB/control02.ctl’,’/u01/app/oracle /oradata/PBSDB/control03.ctl’ scope=spfile;

This will not change autamatically.

Restart the database

At the OS level, copy your control file as follows:

Cp u01/app/oracle/oradata/PBSDB/ control01.ctl  /u01/app/oracle /oradata/PBSDB/control03.ctl

This will copy the content of the current control file into the empty control file.

Connect to the instance and startup the database

Verify that control file exists

SQL> select name from v$controlfile;

Or

SQL> show parameter control_files;

SQL> select file_name from dba_data_files;

FILE_NAME

——————————————————————————–

/u01/app/oracle/oradata/PBSDB/users01.dbf

/u01/app/oracle/oradata/PBSDB/undotbs01.dbf

/u01/app/oracle/oradata/PBSDB/system01.dbf

/u01/app/oracle/oradata/PBSDB/sysaux01.dbf