Oracle DBA Daily Experiences

If you need to capture SQL statements to replay in another instance you can turn trace on and capture in the trace files located in the udump folder.

– turn timing on.
alter session set timed_statistics=true;

– set to unlimited otherwise you may get an incomplete trace file when dump file size limit is reached.

alter session set max_dump_file_size=unlimited;

alter session set events ’10046 trace name context forever, level 12′;

– ******* run all of your processing here *******

alter session set events ’10046 trace name context off’;

ADR is file based repository for diagnostic data like trace file,process dump,data structure dump etc.
In oracle 11g trace. alert not saved in *_DUMP_DEST directory even you set those parameters in init.ora.11g ignore *_DUMP_DEST and store data in new format , directory structure is given below.  DUMP dest parameters were deprecated in 11gR2.  you will get a warning when trying to start the database when reading the spfile.  Check the alert log for error message.

* By default location of DIAGNOSTIC_DEST is $ORACLE_HOME/log,
* if ORACLE_BASE is set in environment then DIAGNOSTIC_DEST is set to $ORACLE_BASE

$ORACLE_BASE/diag/product_type/database_name/instance_name

ADR_BASE=$ORACLE_BASE=diag/rdbms/msaprd/MSAPRD

ADR root where ADR directory structure start.  11g new initialize parameter DIAGNOSTIC_DEST decide location of ADR root.

In 11g alert file is saved in 2 location, one is in alert directory ( in XML format) and old style alert file in trace directory. Within ADR base, there can be many ADR homes, where each ADR home is the root directory for all diagnostic data for a particular instance. The location of an ADR home for a database is shown on the above graphic.

Environment variable ADR_HOME=/oracle/EPW/MSAPRD/admin/diag/rdbms/msaprd/MSAPRD

SQL> show parameter diag
NAME                                               TYPE                                              VALUE
———————————— ——————————— ——————————
diagnostic_dest                            string                                            /oracle/EPW/MSAPRD/admin

SQL>desc v$diag_info;

SQL>set linesize 121
SQL>col value format a75

SQL>select * from v$diag_info;

ADRCI:

The ADR Command Interpreter (ADRCI) is a command-line tool that you use to manage Oracle Database diagnostic data. ADRCI is a command-line tool that is part of the fault diagnosability infrastructure introduced in Oracle Database Release 11g. ADRCI enables:

* Viewing diagnostic data within the Automatic Diagnostic Repository (ADR).
* Viewing Health Monitor reports.
* Packaging of incident and problem information into a zip file for transmission to Oracle Support.

Diagnostic data includes incident and problem descriptions, trace files, dumps, health monitor reports, alert log entries, and more.

ADRCI has a rich command set, and can be used in interactive mode or within scripts. In addition, ADRCI can execute scripts of ADRCI commands in the same way that SQL*Plus executes scripts of SQL and PL/SQL commands.

ADR data is secured by operating system permissions on the ADR directories, hence there is no need to log in to ADRCI.

Check more information on ADR as it’s a pretty rich tool….  I thought it was a nice addition.

http://download.oracle.com/docs/cd/E11882_01/server.112/e10701/adrci.htm

ADR Retention can be controlled with ADRCI:

There is retention policy for ADR that allow to specify how long to keep the data ADR incidents are controlled by two different policies:

• The incident metadata retention policy ( default is 1 year )
• The incident files and dumps retention policy ( Default is one month)

We can change retention policy using “adrci” MMON  purge data automatically on expired ADR data.

adrci>show control

To change the retention police:

adrci> set control (SHORTP_POLICY = 360 )
adrci> set control (LONGP_POLICY = 4380 )

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> alter system reset open_cursors scope=spfile sid='*';

System altered.

# check spfile after running reset command grep no longer finds the parameter in the spfile
$ strings spfileTEST.ora | grep open
$ echo $?
1

Note: If the parameter is not found in the spfile oracle will return “ORA-32010: cannot find entry to delete in SPFILE”, and if the scope is set to both oracle will return “ORA-32009: cannot reset the memory value for instance * from instance TESTDB”

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 in the alert log the following message would be reported “ORA-00294: invalid archivelog format specifier..” or you will see an error message when you try to archive the current redo log by running “ALTER SYSTEM ARCHIVE LOG CURRENT;”, so if you change this parameter a quick test can be done by running the above SQL to make sure oracle is able to archive the redo log.

Format options available on 9i:

%s – log sequence number
%S – log sequence number, zero filled
%t – thread number, needed when running RAC as each node creates it’s own archivelog
%T thread number, zero filled, needed when running RAC as each node creates it’s own archivelog

Format options available on 10g

%s – log sequence number
%S – log sequence number, zero filled
%t – thread number, needed when running RAC as each node creates it’s own archivelog
%T – thread number, zero filled, needed when running RAC as each node creates it’s own archivelog
%a – activation ID
%d – database ID
%r – resetlogs ID that ensures unique names are constructed for the archived log files across multiple incarnations of the database.

In 10g, %s, %t, %r are required to be present in the parameter, if it doesn’t the database fail to start with the error ORA-19905: log_archive_format must contain %s, %t and %r.  Using this format makes it the archive log filename unique for that instance.

The following article has information how to start database in archivelog mode

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.

View the following link to find if parameter can be modified without restart or requires a restart.

http://oraclespin.wordpress.com/2008/05/14/internal-parameters/

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 be set the parameter with scope=spfile, if database was started with spfile one will be able to set the value if it’s started with pfile you will see the “ORA-32001: write to spfile requested but no SPFILE specified at startup”. Note: You can set the new value to be the same as current value of the parameter, it doesn’t have to be a different one.

SQL> ALTER SYSTEM set open_cursors=300 scope=spfile;
ALTER SYSTEM set open_cursors=300 scope=spfile;
*
ERROR at line 1:
ORA-32001: write to spfile requested but no SPFILE specified at startup

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 name, value, ismodified from v$parameter where name = 'open_cursors';

NAME                 VALUE ISMODIFIED
-------------------- ----- ----------
open_cursors         301   FALSE

SQL> alter system set open_cursors=300;

System altered.

SQL> select name, value, ismodified from v$parameter where name = 'open_cursors';

NAME                 VALUE ISMODIFIED
-------------------- ----- ----------
open_cursors         300   SYSTEM_MOD

– view oracle internal parameters, note this can be run by SYS user
– name, value, default value, session modifiable, system modifiable, description
select a.ksppinm name,
b.ksppstvl value,
b.ksppstdf deflt,
decode(bitand(a.ksppiflg/256,3),1, 'True', 'False') SESSMOD,
decode(bitand(a.ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED',3,'IMMEDIATE','FALSE') SYSMOD,
a.ksppdesc description
from sys.x$ksppi a, sys.x$ksppcv b
where a.indx = b.indx
and a.ksppinm like '\_%' escape '\'
order by name
/

– If SYSMOD = Immediate (The Parameter can be changed with ALTER SYSTEM without restart of th e instance)
– If SYSMOD = DEFERRED (The Parameter can be changed with ALTER SYSTEM regardless of the type of parameter file used to start the instance. The change takes effect in subsequent sessions)
– If SYSMOD = FALSE (The parameter cannot be changed with ALTER SYSTEM unless a server parameter file was used to start the instance. The change takes effect in subsequent instances)