Spool out all parameters from a datafile to a file

psql -h postgres01.oric.no -U mysuperuser postgres -c "COPY (SELECT name || '='  || setting FROM pg_settings ORDER BY name) TO STDOUT WITH CSV HEADER"   > pg_config.csv

PostgreSQL Memory Parameters and how they relate

Here are some of the important memory parameters in a postgreSQL server, and how they relate to one another.

shared_buffers

The amount of memory the database server uses for shared memory buffers.

The postgres documentation suggest starting with allocating 25% of the available memory to the shared database memory pool:

If you have a dedicated database server with 1GB or more of RAM, a reasonable starting value for shared_buffers is 25% of the memory in your system... because PostgreSQL also relies on the operating system cache, it is unlikely that an allocation of more than 40% of RAM to shared_buffers will work better than a smaller amount.

It also points out the necessity of considering database checkpointing:

Larger settings for shared_buffers usually require a corresponding increase in max_wal_size, in order to spread out the process of writing large quantities of new or changed data over a longer period of time.

max_wal_size

Controls the maximum size the Write-Ahead Logging (WAL) files can grow before triggering a checkpoint. Checkponts are relative expensive operations, so we do not want them to occur too often. On the other hands, too infrequent checkpointing may increase recovery times. max_wal_size can be set to balance performance and recovery time by influencing how often checkpoints occur.

work_mem

Sets the base maximum amount of memory to be used by a query operation (such as a sort or hash table) before writing to temporary disk files.

The documentation points out that a complex query might perform several sort and hash operations at the same time, with each operation generally being allowed to use as much memory as this value specifies before it starts to write data into temporary files and that serveral running sessions could be executing such operations at the same time. So even if the 6M specified as its value does not seem like much, it could mean significant memory usage on a busy system.

It is similar to pga_aggregate_target in an oracle database: the amount of memory set for all private global areas on the server. After the introduction of this parameter in Oracle 9i, the private global area parameters used back then, for example sort_area_size and hash_area_size, was not necessary any longer.

maintenance_work_mem

Specifies the maximum amount of memory to be used by maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY.

It can be set higher than work_mem: Since only one of these operations can be executed at a time by a database session, and an installation normally doesn't have many of them running concurrently, it's safe to set this value significantly larger than work_mem. Larger settings might improve performance for vacuuming and for restoring database dumps.

autovacuum_work_mem

Specifies the maximum amount of memory to be used by each autovacuum worker process. If this value is specified without units, it is taken as kilobytes. It defaults to -1, indicating that the value of maintenance_work_mem should be used instead. The setting has no effect on the behavior of VACUUM when run in other contexts.

Here is a table with my settings for my 16G Memory server

Parameter	Value
shared_buffers	4GB
max_wal_size	8GB
work_mem	6MB
maintenance_work_mem	479MB

Source: PostgreSQL documentation

Simple PL/SQL script to alter parameter in database

At my current workplace, we use emcli for mass-updates of database parameters in groups of databases.

Below is a simple PL/SQL script that can be used to change parameter if needed. If the parameter is already set, nothing happens.

In my case, it was the parameter "recyclebin" that was needed to be switched from OFF to ON for some databases, but it could be any parameter.

Surely there are many ways to solve such a problem, this was how I solved it with very little effort :-)

set serveroutput on
set feedback off
set echo off
set verify off

DECLARE

 v_db_name v$database.name%%TYPE;
 v_rb v$system_parameter.name%%TYPE;


BEGIN
 execute immediate 'alter session set nls_language=''american''';
 select name
 into v_db_name
 from v$database;

 select value
 into v_rb
 from v$system_parameter
 where name = 'recyclebin';
 
--dbms_output.put_line('v_db_name: ' || v_db_name);
--dbms_output.put_line('v_rb:      ' || v_rb);

CASE
   WHEN v_rb = 'ON' THEN
    dbms_output.put_line('Database ' || v_db_name || ': recyclebin already enabled.');
   ELSE
    dbms_output.put_line('Database ' || v_db_name || ': recyclebin is currently disabled. Turning it on now...');
    execute immediate 'alter system set recyclebin=ON scope=spfile';
     dbms_output.put_line('Database ' || v_db_name || ' now has recyclebin=ON in spfile. Database must be bounced in in order to enable the setting');
   END CASE;
END;
/
exit

When we execute the script through emcli, the syntax comes to:

emcli execute_sql -sql="FILE" -iemcli execute_sql -sql="FILE" -input_file="FILE:/scripts/chk_and_alter_param.sql" -targets="TEST_DBS:composite"

How to show current utilization of sessions, processes and other important settings for a database

Example:

set lines 300
col RESOURCE_NAME format a30
select RESOURCE_NAME,CURRENT_UTILIZATION,MAX_UTILIZATION,INITIAL_ALLOCATION,LIMIT_VALUE
from v$resource_limit
where resource_name in ('processes','sessions');

RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION     LIMIT_VALUE
------------------------------ ------------------- --------------- ---------------------- ---------------
processes                                     1498            1500       1500             1500
sessions                                      1511            1517       2272             2272

Documented for Oracle 19c here

How to change an Oracle instance parameter based on database version and container type

Here is a simple pl/sql script that will set parameters based on version and container type.

set serveroutput on
DECLARE

v_version     sys.v_$instance.version%type;
v_param_value sys.v_$system_parameter.value%type;
v_pdb boolean;
v_con_id number;
v_con_type varchar(7);

BEGIN

-- find current setting of optimizer_dynamic_sampling
select value
into   v_param_value
from   v$parameter
where  name = 'optimizer_dynamic_sampling';

-- find current(major) version
select version
into   v_version
from   sys.v_$instance;

-- find container type
select 
    case when (sys_context('USERENV','CON_ID') = 0 ) then 'NON-CDB' 
       when (sys_context('USERENV','CON_ID') = 1 ) then 'CDB'
       when (sys_context('USERENV','CON_ID') > 1 ) then 'PDB'
    end
into v_con_type
from DUAL;

--dbms_output.put_line('v_con_type: ' || v_con_type);
--dbms_output.put_line('version: ' || v_version);
--dbms_output.put_line('optimizer_dynamic_sampling: ' || v_param_value);

v_pdb := FALSE;

IF v_con_type = 'PDB' THEN
  v_pdb := TRUE;
ELSE
  v_pdb := FALSE;
END IF;


CASE WHEN v_version IN ('18.0.0.0.0','19.0.0.0.0') AND v_param_value = '0' THEN
    dbms_output.put_line('version is >= 18 and optimizer_dynamic_sampling = 0');
    
    IF v_pdb = FALSE THEN
      dbms_output.put_line('execute immediate alter system set optimizer_dynamic_sampling=4 scope=both;');
      execute immediate 'alter system set optimizer_dynamic_sampling=4 scope=both';
    ELSIF v_pdb = TRUE THEN
      dbms_output.put_line('execute immediate alter system set optimizer_dynamic_sampling=4 container=current scope=both;');
      execute immediate 'alter system set optimizer_dynamic_sampling=4 container=current scope=both';
    END IF;
    
WHEN v_version IN ('18.0.0.0.0','19.0.0.0.0') AND v_param_value <> '0' THEN   
     dbms_output.put_line('version is >= 18 and optimizer_dynamic_sampling is already set.');
ELSE
  dbms_output.put_line('version is < 18');
END CASE;

END;
/

List parameters in a container database

To see the list of parameters in a container database, use can use the following query.

select name,value,display_value, default_value,isdefault,
    case 
     when issys_modifiable = 'FALSE'     then  'Change must go to spfile. Restart of cdb required'
     when issys_modifiable = 'IMMEDIATE' then  'Current sessions will pick up new value.'
     when issys_modifiable = 'DEFERRED'   then 'Future sessions will pick up the new value'
     else issys_modifiable
    end "issys_modifiable",
     case 
     when ispdb_modifiable = 'TRUE' then 'Parameter can be set in PDB'
     when ispdb_modifiable = 'FALSE' then 'Parameter cannot be set in PDB'
    end "ispdb_modifiable" 
    from v$system_parameter; 

Example output shows that the parameters can be set at different levels in your multitenant database structure

NAME	VALUE	DISPLAY_VALUE	DEFAULT_VALUE	ISDEFAULT	issys_modifiable	ispdb_modifiable
sga_max_size	8287944704	7904M	0	TRUE	Change must go to spfile. Restart of cdb required	Parameter cannot be set in PDB
shared_pool_size	671088640	640M	134217728	FALSE	Current sessions will pick up new value.	Parameter can be set in PDB
sga_target	8287944704	7904M	0	FALSE	Current sessions will pick up new value.	Parameter can be set in PDB
sort_area_size	65536	65536	65536	TRUE	Future sessions will pick up the new value	Parameter can be set in PDB

* Some parameters may only be written to the spfile and the CDB instance must be restarted.
* Other parameters may be set in the CDB and will be picked up in the individual PDBs, either affecting current sessions or future sessions only
* Some parameters may be set in each PDB, regardless of the CDB parameter value for the same parameter is set to


"Listing the Modifiable Initialization Parameters in PDBs Oracle 19c" is documented here

The v$system_parameter view in Oracle 19c is documented here

How to check when a parameter was changed

Thanks to Sandeep Singh for providing this very useful information.

The following query will reveal any changes to a particular parameter:

select instance_number instance, snap_id, time, parameter_name, old_value, new_value 
from (
       select a.snap_id,to_char(end_interval_time,'DD-MON-YY HH24:MI') TIME, a.instance_number, parameter_name, value new_value,
       lag(parameter_name,1) over (partition by parameter_name, a.instance_number order by a.snap_id) old_pname,
       lag(value,1) over (partition by parameter_name, a.instance_number order by a.snap_id) old_value ,
       decode(substr(parameter_name,1,2),'__',2,1) calc_flag
from dba_hist_parameter a, dba_Hist_snapshot b , v$instance v
where a.snap_id=b.snap_id
and   a.instance_number=b.instance_number
and   parameter_name like nvl('&parameter_name',parameter_name)
and   a.instance_number like nvl('&instance_number',v.instance_number)
)
where
new_value != old_value
order by 1,2;

In my case the output was

INSTANCE	SNAP_ID	TIME	PARAMETER_NAME	OLD_VALUE	NEW_VALUE
1	52050	28-FEB-22 14:00	optimizer_adaptive_statistics	TRUE	FALSE

How to set a dynamic parameter in a postgreSQL database cluster

As with oracle, some parameters may be set dynamically in a postgreSQL database cluster. A postgreSQL database cluster uses a parameter file called postgres.conf. This file holds the cluster wide parameters. If you set a dynamic parameter using the ALTER SYSTEM SET command, the parameter will be written to yet another file called postgres.auto.conf, which values will always override the ones parameters in the postgres.conf Before the change, postgres.auto.conf look like this:

log_line_prefix = '[%m] – %p %q- %u@%h:%d – %a'
wal_level = 'replica'
hot_standby = 'on'

I then make a change to the system configuration:

alter system set hot_standby_feedback=on;
ALTER SYSTEM

After this change, the file postgres.auto.conf has another entry:

log_line_prefix = '[%m] – %p %q- %u@%h:%d – %a'
wal_level = 'replica'
hot_standby = 'on'
hot_standby_feedback = 'on'

I will then have to reload the database using the function pg_reload_conf() for the new parameter to take effect:

postgres=#  select pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

The current logfile for the postgreSQL database cluster records this fact:

[2022-01-03 14:45:23.127 CET] – 1683 LOG:  received SIGHUP, reloading configuration files
[2022-01-03 14:45:23.129 CET] – 1683 LOG:  parameter "hot_standby_feedback" changed to "on"

For details, check the documentation

How to rename a database to use lower case instead of upper case characters

At my customer's site, we have standardized the Oracle SIDs (System ID) to use lower case character.

During an upgrade, the SID was accidently set to upper case. This new uppercase SID was picked up by Oracle Cloud Control and all of a sudden we had a non-standardized database within our ranks. Not a disaster, but irritating, nevertheless.

Given that your environment variable in you operating system is also set to lower case, you can simply restart the database with a new value for the parameter db_name in the parameter file to rectify the situation. There is no need to use the nid ("new id") tool or to recreate the controlfile. A simple restart with the right value for the parameter db_name will do.

It's easy to verify this conclution if you have access to another database which adheres to the standard. Check the value of the columnn name in v$database:

select name from v$database;

NAME
---------
PRODDB01

SQL> show parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      PRODDB01

As expected, the query from v$database it is displayed in upper case as Oracle alwaysd does by default, but we also see that the value of the parameter db_name is actually in lower case.

In other words, there is no connection between the value of ORACLE_SID and the name of the database used in the controlfile. Oracle will always refer to the database in upper case. How we present and use this value in the operating system, is up to us.

These are the simple steps to get the value of the db_name parameter aligned with the value of your operating system variable ORACLE_SID:

Check your environment variable:

env | grep ORA
ORACLE_SID=testdb01 

It is indeed shown in lower case

Create a new pfile:

sqlplus / as sysdba
create pfile='inittestdb01.ora' from spfile;

Edit the pfile so that the db_name parameter has its value in lower case:

Old value:
*.db_name='TESTDB01'

New value:

*.db_name='testdb01'

Restart the database:

sqlplus / as sysdba
shutdown immediate
startup

If desirable, create a new spfile and restart the database one final time to instruct oracle to use it instead of the pfile:

create spfile from pfile='inittestdb01.ora';
shutdown immediate
startup

How to change the control_files parameter used in the spfile

Mind your syntax when you are changing certain multi-value parameters directly in the spfile. Oracle will tell you that the syntax is accepted, but it will give you an error later. The following example is from an attempt to change the control_files parameter.

Won't work:

SQL> alter system set control_files='/oradata/proddb01/control01.ctl, /fra/proddb01/control02.ctl' scope=spfile;

System altered.

During mount stage, this will give you the following message in the database's alert log:

ORA-205 signalled during: ALTER DATABASE   MOUNT...
ORA-00202: control file: /oradata/proddb01/control01.ctl, /fra/proddb01/control02.ctl
ORA-27037: cannot obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7

Works:

SQL> alter system set control_files='/oradata/proddb01/control01.ctl','/fra/proddb01/control02.ctl' scope=spfile;

System altered.

You should enclose both values within ' ' apostroph characters.

Parameters that can be removed after upgrade to 12.2

From Information For Parameters _upgrade_optim and _upgrade_capture_noops (Doc ID 2182783.1):


Below parameters gets added after upgrade to 12.2:

_upgrade_optim = FALSE
_upgrade_capture_noops = FALSE

These parameters are part of upgrade optimizations. They do not have any effect on upgrade currently.

Since these parameters does not have any impact, it can be removed safely after upgrade to 12.2.


Can be removed, like this:

echo "alter system reset \"_upgrade_optim\" scope=spfile SID='*';" | sqlplus / as sysdba
echo "alter system reset \"_upgrade_capture_noops\" scope=spfile SID='*';" | sqlplus / as sysdba

How to remove unwanted parameters from init.ora using a shell script

1. Create a "removal list" containing your unwanted parameters. For example, put the following into a text file called removal_list.txt, and save it in $ORACLE_HOME/dbs:

_b_tree_bitmap_plans
_fast_full_scan_enabled
_grant_secure_role
_like_with_bind_as_equality
_projection_pushdown

2. create a Shell script to loop through the lines in remval_list.txt, called remove_params.sh:

for a in $(cat removal_list.txt ); do
 echo now testing $a
 sleep 1
 curr_param=`grep -w $a init${ORACLE_SID}.ora | awk -F '[=]' '{ print $1 }'`
 if [ $curr_param ]; then
  echo removing $curr_param
  sed -i "/${curr_param}/d" init${ORACLE_SID}.ora
  sleep 1
 else
  echo $a not found in parameter file. Skipping...
  sleep 1
 fi;
done
exit 0

It is important to use the -w flag with grep, as many Oracle initialization parameters can be named similarly and the script will then save multiple values in the curr_param variable

3. Make Your script executable:

chmod 755 remove_params.sh

4. execute the script:

./remove_params.sh

Expected output:

now testing _b_tree_bitmap_plans
removing _b_tree_bitmap_plans
now testing _fast_full_scan_enabled
removing _fast_full_scan_enabled
now testing _grant_secure_role
_grant_secure_role not found in parameter file. Skipping...
now testing _like_with_bind_as_equality
removing _like_with_bind_as_equality
now testing _projection_pushdown
_projection_pushdown not found in parameter file. Skipping...

Some important tuning parameters in Oracle 12c

Here are some parameters that you will have to deal with when tuning an Oracle 12c database. Execute the following SQL to view their current setting:

col name format a40
col value format a20
col description format a80
set lines 200

SELECT NAME,VALUE,DESCRIPTION 
FROM   V$SYSTEM_PARAMETER
WHERE NAME IN ( 'optimizer_adaptive_features',
                'optimizer_adaptive_reporting_only',
                'optimizer_features_enable',
                'optimizer_use_sql_plan_baselines',
                'optimizer_capture_sql_plan_baselines',
                'optimizer_dynamic_sampling',
                'optimizer_adaptive_plans',
                'optimizer_adaptive_statistics');

OPTIMIZER_ADAPTIVE_FEATURES enables or disables all of the adaptive optimizer features, including

* adaptive plan (adaptive join methods and bitmap pruning)
* automatic re-optimization
* SQL plan directives
* adaptive distribution Methods

OPTIMIZER_DYNAMIC_SAMPLING controls both when the database gathers dynamic statistics, and the size of the sample that the optimizer uses to gather the statistics.

Dynamic statistics were called dynamic sampling in versions prior to 12c.

Range of values: 0 to 11. Default depends on the value of the parameter optimizer_features_enable:

• If OPTIMIZER_FEATURES_ENABLE is set to 10.0.0 or higher, then 2 
• If OPTIMIZER_FEATURES_ENABLE is set to 9.2.0, then 1 
• If OPTIMIZER_FEATURES_ENABLE is set to 9.0.1 or lower, then 0

At level 2, dynamic statistics are used "if at least one table in the statement has no statistics".

At level 4, Oracle is less restrictive and will use dynamic statistics when "at least one table in the statement has no statistics, the statement has one or more expressions used in the WHERE clause predicates... or the statement uses complex predicates".

When this parameter is set to 11, the optimizer will use dynamic statistics to verify cardinality estimates for all SQL operators, and it will determine an internal time limit to spend verifying the estimates.

Notice that Oracle says the following about level 11:

There are cases where the optimizer will automatically decide to use 11, for example:

•The query will run in parallel.
•The query was executed before and its history is available (from the cursor cache, Automatic Workload Repository, or the SQL Management Base).


From personal experience, this parameter is important! If it's turned off, I would recommend setting it to the default value. I have seen many cases where queries will actually perform better only by setting this parameter from 0 (off) to the default value.

Read more about the different Levels:
Oracle 12cR1
Oracle 12cR2

The two parameters appearing last in the list, optimizer_adaptive_plans and optimizer_adaptive_statistics, were added in 12.2, and replace the parameter optimizer_adaptive_features.

How to create a logon trigger

In this particular example, I create a logon trigger that sets one of many available NLS session parameters, as well as sets an undocumentet parameter that I was recommended to set by Oracle Support services:

CREATE OR REPLACE TRIGGER logon_optimizer 
after logon on database
begin 
   if user in ('SCOTT','JACK','BOB')
   then 
      execute immediate 'alter session set NLS_LANGUAGE="GERMAN"';
      execute immediate 'alter session set "_optimizer_unnest_disjunctive_subq"= FALSE';
   end if; 
end;
/

Test to verify that it Works:

Connect scott/tiger
set lines 200
col parameter format a30
col value format a40
select * from nls_session_parameters;


PARAMETER                      VALUE
------------------------------ -----------
NLS_LANGUAGE                   GERMAN
NLS_TERRITORY                  NORWAY

Now generate an error:

select * from xxx
              *
ERROR at line 1:
ORA-00942: Tabelle oder View nicht vorhanden

How to create a line-by-line comparison between the parameters used in two different databases

Here is a simple way to create a line-by-line comparison between two databases over a database link.

First make sure there is tns Connectivity between the database you execute the script in, and the remote database.

Then, create a datatabase link:

-- create db link from prod to test
create public database link testdb
connect to system
identified by ****
using 'testdb';

-- make sure that the link works before you proceed:
select host_name from v$instance@testdb;

The following script will spool a text file to your current directory, which can be investigated for differences between the two databases' initialization parameters:

set trimspool on
spool parameter_comparison.lst
set lines 200
col name format a40
col "test value" format a50
col "prod value" format a50
select prod.name,prod.value "prod value",test.value "test value"
from v$system_parameter prod full outer join v$system_parameter@testdb test
on prod.name = test.name;

exit

11.2.0.2 and onwards: v$parameter reflects the NLS session parameters, not the NLS instance parameters

After a migration of a database from single-byte to multi-byte character set, I wanted to compare the nls_parameters.

As a part of the migration, we had to change semantics on several of our tables from BYTE to CHAR to accommodate for expanding data.

One issue that came up during our initial discussions on how to complete the migration, was if it would be beneficial to set nls_length_semantics to CHAR in the init.ora file. But according to Oracle's documentation, the NLS_LENGTH_SEMANTICS should be kept to BYTE at database level.(Check Doc ID 144808.1 "Examples and limits of BYTE and CHAR semantics usage (NLS_LENGTH_SEMANTICS)", section G: "Summary of best practices") so that idea was rejected.

After the import was finished, I wanted to compare the parameters to quality check the new instance.

I queried the v$parameter view, as many DBAs would normally do and found that nls_length_semantics seemed to have been set to CHAR at the instance level:

select name,value from v$parameter where name = 'nls_length_semantics';

NAME	VALUE
nls_length_semantics	CHAR

After a quick search on My Oracle Support I found Doc ID 1368966.1 "NLS settings like NLS_DATE_FORMAT in spfile or init.ora ignored, incorrect or not as expected on Oracle 11.2.0.2 and higher"

Turns out that this is not an error, but the result of a corrected bug (bug no 8722860).

In previous releases, a query of v$parameter or "show parameter" in sql*plus would reflect the instance parameters.
This is incorrect, as Oracle points out:

The documentation set states that :
* the SQL*Plus command show parameter displays the values of initialization parameters in effect for the current session.
* the V$PARAMETER view displays information about the initialization parameters that are currently in effect for the session


As Oracle puts it
"From 11.2.0.2 onwards V$PARAMETER and the SQL*Plus command show parameter reflect the session NLS settings as it should."

So make sure to use v$system_parameter instead, which is reflecting the correct instance setting:

select name,value from V$SYSTEM_PARAMETER where name = 'nls_length_semantics';

NAME	VALUE
nls_length_semantics	BYTE

Sources:
Doc ID 144808.1 "Examples and limits of BYTE and CHAR semantics usage (NLS_LENGTH_SEMANTICS)"
Doc ID 1368966.1 "NLS settings like NLS_DATE_FORMAT in spfile or init.ora ignored, incorrect or not as expected on Oracle 11.2.0.2 and higher"
Doc ID 241047.1: "The Priority of NLS Parameters Explained (Where To Define NLS Parameters)

Syntax for setting hidden parameter in Oracle

Syntax example:

alter system set "_system_trig_enabled"=TRUE scope=both;

How to find hidden parameters in the database

set lines 200
col description format a70
col value format a20
col name format a30
SELECT name,value,description from SYS.V$PARAMETER WHERE name  LIKE '\_%' ESCAPE '\';
SELECT (translate(value,chr(13)||chr(10),' ')) FROM sys.v$parameter2  WHERE  UPPER(name) ='EVENT' AND  isdefault='FALSE';
SELECT (translate(value,chr(13)||chr(10),' ')) from sys.v$parameter2 WHERE UPPER(name) = '_TRACE_EVENTS' AND isdefault='FALSE';

If desirable, you can generate a "reset" script, which preserves your hidden parameters, like this:

set lines 200
set pages 0
set feedback off
set verify off
set echo off
set heading off
set trimspool on
spool original_hidden_params.sql
select '-- original hidden parameter values' from dual;
select 'alter system set "' || name || '"=' || value || ' scope=spfile;' from SYS.V$PARAMETER WHERE name  LIKE '\_%' ESCAPE '\';
select 'exit' from dual;
spool off
spool reset_hidden_params.sql
select '-- reset hidden parameter' from dual;
select 'alter system reset "' || name || '" scope=spfile;' from SYS.V$PARAMETER WHERE name  LIKE '\_%' ESCAPE '\';
select 'exit' from dual;
exit

To set a hidden parameter in Your instance, see this post.

log_archive_format default value

Having trouble finding out why the value of the parameter LOG_ARCHIVE_FORMAT does not seem to apply to your database?

From Oracle 10g and onwards, the default value for LOG_ARCHIVE_FORMAT is %t_%S_%r.dbf

However, this parameter will only make a difference if you are NOT using a "Fast Recovery Area" (in Version 10 Oracle called the same thing a "Flash Recovery Area")

If you have defined a FRA by using the parameters db_recovery_file_dest and db_recovery_file_dest_size, the archivelogs will be automatically named according to OMF (Oracle Managed Files) standard and the value of LOG_ARCHIVE_FORMAT will be ignored.

An example of an OMF managed file in the FRA:

/fra/PRODDB01/archivelog/2014_06_30/o1mv_1_1_9vxzch5_.arc

From the documentation:

"The fast recovery area is an Oracle Database managed space that can be used to hold RMAN disk backups, control file autobackups and archived redo log files. The files placed in this location are maintained by Oracle Database and the generated file names are maintained in Oracle Managed Files (OMF) format."

At first glance, letting the LOG_ARCHIVE_FORMAT default to such a poor naming standard may seem like a bad idea, but I guess it illustrates the point of defining an FRA vs. using conventional archive log destinations.

How to set the db_file_name_convert and log_file_name_convert parameters

In this example, I want to redirect the files to a slightly different path during a clone from prod to test.

In prod, files are to be found under:

* /u02/oradata/proddb01/datafile

In test, I want to be placed under:

* /u02/oradata/testdb01

Furthermore, some tempfiles are placed differently than regular datafiles in prod. In test I do not need or want multiple destinations; all files should be placed under /u02/oradata/testdb01. Therefore, my db_file_name_convert parameter must have multiple pairs of source and target locations.

For the log files, they could all be placed under similar locations, so the redirection string can simply contain the only thing that will differ: the ORACLE_SID.

When using a pfile:

db_file_name_convert=('/u02/oradata/proddb01/datafile','/u02/oradata/testdb01',
                      '/u02/oradata/proddb01/tempfile',/u02/oradata/testdb01')

log_file_name_convert=('proddb01','testdb01')

When using an spfile:

alter system set db_file_name_convert='/u02/oradata/proddb01/datafile','/u02/oradata/testdb01',
                                      '/u02/oradata/proddb01/tempfile',/u02/oradata/testdb01' scope=spfile;

alter system set log_file_name_convert='proddb01','testdb01' scope=spfile;

It is also supported to use the log_file_name_convert multiple times in the parameter file, like this:

log_file_name_convert=('/u01/app/oracle/oradata/proddb01/onlinelog/','/u04/oradata/testdb01/onlinelog/')
log_file_name_convert=('/u01/app/oracle/flash_recovery_area/proddb01/onlinelog/','/u04/oradata/testdb01/onlinelog/')

Oracle Docs:
db_file_name_convert
log_file_name_convert