Category: Parameters

General DBA, Initialization, Parameters

How to add a comment when changing a parameter?

In “ALTER SYSTEM” one can include a comment which gets recorded in the spfile and also in v$parameter as shown below. It can be used to record why parameter was changed. Example: SQL> alter system set open_cursors=1000 comment='07-Jan-2012 Changed AJ needed for application XYZ'; SQL> select value, update_comment from v$parameter where name = 'open_cursors'; VALUE ——————————————————————————– UPDATE_COMMENT ——————————————————————————– 400 $ strings spfileTESTDB.ORA | grep open_cursors … Continue reading How to add a comment when changing a parameter?

Alex LimaLeave a comment
General DBA, Initialization, Parameters

How to find parameters that will take into effect for new sessions?

Using the following query one can find the list of parameters that will take info effect for new sessions if the value of the parameter is changed. SQL> SELECT name FROM v$parameter WHERE issys_modifiable = ‘DEFERRED’; The parameter has be changed using the deferred option: SQL> alter system set sort_area_size=65538 deferred; System altered. Continue reading How to find parameters that will take into effect for new sessions?

Alex LimaLeave a comment
General DBA, Initialization, Parameters

How to delete/reset a parameter in spfile using ALTER SYSTEM?

Using ALTER SYSTEM RESET one can delete parameter from spfile if the parameter is in the spfile. Example: — shows the parameter is in the spfile $ pwd /u01/oracle/product/10.2.0/db_1/dbs $ strings spfileTEST.ora | grep open *.open_cursors=100 — Login to SQL*Plus, scope has to spfile and sid has to be supplied. When '*' is specified it applies to all instances if it is a cluster SQL> … Continue reading How to delete/reset a parameter in spfile using ALTER SYSTEM?

Alex LimaLeave a comment
Backup & Recovery, General DBA, Initialization, Parameters

Format for parameter LOG_ARCHIVE_FORMAT?

This parameter controls the format of the archive log file name.  This parameter can’t be changed on the fly therefore requires a restart of the instance.  This parameter can be changed with scope=spfile if spfile is used.   If the format defined in the parameter log_archive_format is invalid the database will startup but archiver will fail to archive logs which will cause database to hang and … Continue reading Format for parameter LOG_ARCHIVE_FORMAT?

Alex LimaLeave a comment
General DBA, Parameters

How to make trace files visible to all users?

To make trace files visible to all users, set the parameter _TRACE_FILES_PUBLIC=TRUE, this is undocumented parameter. This parameter requires a restart of instance to take into effect. SQL> alter system set “_TRACE_FILES_PUBLIC”=true scope=spfile; System altered. Shows the trace file is accessible by all users, you may have change permissions on the user_dump_dest folder -rw-r–r– 1 oracle oinstall 1433 Feb 21 21:07 test_ora_26729.trc View the following … Continue reading How to make trace files visible to all users?

Alex LimaLeave a comment
General DBA, Parameters, startup

How to tell if the database was started with spfile or pfile?

There are couple of ways of finding if the database was started with spfile or pfile. One way would be tell show the check the value of parameter spfile, if it returns blank then database was started by pfile. — belows show database was started by spfile SQL> show parameter spfile; NAME TYPE VALUE —— —— ———————————————— spfile string /u01/apps/oracle/10g/dbs/spfileorcltest.ora Another way to find would … Continue reading How to tell if the database was started with spfile or pfile?

Alex Lima2 Comments
Parameters

How to identify parameters that has been modified since instance started?

To identify the parameter one can query v$parameter view to find the parameters that were modified. The column ISMODIFIED is FALSE by default when the instance starts up, when the value is changed it is SYSTEM_MOD indicating the value is changed at the SYSTEM level and if it’s MODIFIED then it’s changed at session level. — Before parameter change displaying value of ISMODIFIED SQL> select … Continue reading How to identify parameters that has been modified since instance started?

Alex LimaLeave a comment