What is oracle database ? Part VI

DB Files:


  1.  Parameter 
  2.  Password
  3.  tnsnames
  4.  Listener
  5.  Sqlnet
  6.  Control file
  7.  Data files
  8.  Temp file
  9.  Online Redo Log File
10. Archive Log

          1.  Parameter

Parameter is a mandatory file required to start database instance in nomount stage by reading instance characteristics
Parameter files hold DB parameter & its respective values
Below Query can be used to find parameter file being used during db startup

SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type"  FROM v$parameter WHERE name = 'spfile';

Parameter files located in default location $ORACLE_HOME/dbs (UNIX) & %ORACLE_HOME%database (Windows)


PFILE:

PFILE is a text file & editable using editors like  vi in Unix & notepad  in windows
PFILE is the static parameter file i.e. to reflect modified parameter values requires restart of the instance
Although PFILE is supported in all DB Versions it was the only supported parameters file in oracle 8i & lower

SPFILE

SPFILE is a binary file can be updated using “ALTER SYSTEM SET <parameter>=<value>”
If SPFILE is modified using editor then it returns below error while starting up DB

ORA-01078:falilure in processing system parameters
ORA-01565: error in identifying file 'C:\oracle\SPFILEMRTEDEV.ora'
ORA-27046:file size is not a multiple of logical block size

SPFILE can be backed up using RMAN

CONFIGURE CONTROLFILE AUTOBACKUP ON;
BACKUP CURRENT CONTROLFILE FORMAT ‘<location>/ctl_%U’;

Dynamic parameters can be updated in SPFILE without need of instance re-start

Identifying Parameter’s characteristics:

ISDEFAULT –
TRUE if value default and not changed since instance creation.
Also note event if value is set to default value again post changes it needs db restart followed by “alter system reset <parameter>” to reflect value as TRUE in view
FALSE if value is modified or it could be default value changes at least once since instance creation without reset/restart

ISSES_MODIFIABLE –
TRUE if parameter can be modified by “alter session set <parameter>=<value>;” else FALSE

ISSYS_MODIFIABLE –
Has below values based on ability to modify parameters using “ALTER SYSTEM SET <parameter>=<value>;”
IMMEDIATE — can be altered regardless of what type of parameter file was used to start the instance and changes take effect immediately
DEFERRED — can be altered regardless of what type of parameter file was used to start the instance and change will take effect in subsequent sessions
FALSE — can only be changed when a SPFILE is used, you need to bounce the instance.

ISINSTANCE_MODIFIABLE –


It is related to RAC.
If this value is set to ‘TRUE’ for a parameter, it means that there can be different value of the same parameter in different instance in RAC.
If the value is set to ‘FALSE’, it means that particular parameter should always have the same value in all RAC instances.
If issys_modifiable value is ‘FALSE’ for the specific parameter then ISINSTANCE_MODIFIABLE is always ‘FALSE’ for the same parameter.


ISMODIFIED –

MODIFIED - Parameter has been modified with ALTER SESSION
SYSTEM_MOD - Parameter has been modified with ALTER SYSTEM (which causes all the currently logged in sessions' values to be modified)
FALSE - Parameter has not been modified after instance startup

ISADJUSTED –

Indicates whether Oracle adjusted the input value to a more suitable value (for example, the parameter value should be prime, but the user input a non-prime number, so Oracle adjusted the value to the next prime number)

ISDEPRECATED –

Indicates whether the parameter has been deprecated (TRUE) or not (FALSE)

Parameter Modification Options:

Parameters can be modified using “ALTER SYSTEM SET <parameter>=<value> SCOPE=<SPFILE/MEMORY/BOTH>”
Where,
SCOPE = SPFILE  (For both static and dynamic parameters, changes are recorded in the spfile, takes effect on restart)
SCOPE = MEMORY (For dynamic parameters, changes are applied in memory only. No static parameter change is allowed.)
SCOPE = BOTH (For dynamic parameters, the change is applied in both the server parameter file and memory. No static parameter change is allowed.)
For dynamic parameters, we can also specify the DEFERRED keyword. When specified, the change is effective only for future sessions
ALTER SYSTEM SET <parameter> = <value> [DEFERRED]

No comments:

Post a Comment