Packaging Oracle Incidents

Under version 11, oracle introduced an interesting mechanism called "adrci" aka

"AUTOIMATIC DIAGNOSTIC REPOSITORY"

This tool, among other tasks does the following:

 - Automatically purges trace files, alert log files etc
 - Manages "incidents"
 - Packages incidents.

This blog will talk about how to manage the logs and manage incidents. Although many features discussed in this blog can be completed via OEM, I will be discussing how to do this from the command interface.

To Log On, run:

unix> adrci

To show your current environment, run:

adrci> show homes

To set a HOME environment, run:

adrci> set home

To show current incidents, run:

adrci> show incidents

ADR Home = /opt/oracle/admin/diag/rdbms/xxx/XXX:

*************************************************************************
INCIDENT_ID PROBLEM_KEY CREATE_TIME
-------------------- ----------------------------------------------------------- ------------------
12426 ORA 3137 [12333] 2011-11-02 16:47:50.277000 +13:00
1 rows fetched


To purge a particular incident, run:
 
adrci> purge -i n nn nnn
 
To purge a type of data, run:
 
adrci> purge -age nn -type xxx
 
where age is defined in minutes and type can be "alert, incident, trace, dump etc"

11.2 Update Window Details

One of the nice features introduced in recent oracle releases was automated maintenance jobs. This is a nice feature as oracle tries to automate STATS generation and automating execution of advisors.

Sometimes, however the default schedule for these maintenance jobs is not good enough, so to change these windows.

In a nutshell, you need to disable the window, set attributes, duration time, start time etc.

A sample would be:

BEGIN
DBMS_SCHEDULER.DISABLE(
name=>'"SYS"."SATURDAY_WINDOW"',
force=>TRUE);
END;

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'"SYS"."SATURDAY_WINDOW"',
attribute=>'DURATION',
value=>numtodsinterval(1080, 'minute'));
END;

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'"SYS"."SATURDAY_WINDOW"',
attribute=>'REPEAT_INTERVAL',
value=>'FREQ=WEEKLY;BYDAY=SAT;BYHOUR=3;BYMINUTE=0;BYSECOND=0');
END;

BEGIN
DBMS_SCHEDULER.ENABLE(
name=>'"SYS"."SATURDAY_WINDOW"');
END;

Remove unwanted files from FLASH_RECOVERY_AREA

Move backups from the flash recovery area to tertiary storage such as tape.

RMAN> BACKUP RECOVERY AREA;

Note : Flashback logs cannot be backed up outside the recovery area and so are not backed up by BACKUP RECOVERY AREA.

NOTE: Removing these files with an operating system utility will physically remove these files, but the database will still think the files exist and the problem of archival error may still occur. Use the following commands to synchronize the files with the database and use rman to remove these files.

RMAN>CROSSCHECK BACKUP;
RMAN>CROSSCHECK ARCHIVELOG ALL;

RMAN>Delete expired backup;
RMAN>Delete expired archivelog all;
RMAN>Delete force obsolete;

4. Make sure that your guaranteed restore points are necessary. If not, delete them

SQL>Drop restore point restore_point_name

5. If flashback logs are enabled then make sure you have enough space for all the flashback logs. If its not required then you can turn off flashback.

SQL> Alter database FLASHBACK OFF;

6. Review your backup retention policy and if required change the RMAN RETENTION POLICY

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

Exceptions :

- If RMAN is not part of backup strategy and archivelogs are going to FRA then manual intervention required for deletion of archivelogs. Periodically purse old archivelogs

for example

RMAN> Delete archivelog all completed before 'SYSDATE-7';

- By default RMAN backup goes to FRA. While taking RMAN backup if backup location explictly specified to flash recovery area location then those backup pieces are not considered as part of FRA for auto managment.

- For Archivelogs backup to FRA use USE_DB_RECOVERY_FILE_DEST rather than giving explict path of FRA

SQL> alter system set log_archive_dest_10='LOCATION=USE_DB_RECOVERY_FILE_DEST' scope=both;

- Bug 4911954

Details:
V$RECOVERY_FILE_DEST SPACE_USED and NUMBER_OF_FILES values may be wrong.
eg: Number of files in V$FLASH_RECOVERY_AREA_USAGE and V$RECOVERY_FILE_DEST are different.
This problem can lead to recovery area files being deleted even though there is no space pressure.
Fixed in : 10.2.0.3 , 11.1.0.6


- Bug 5106952

Details:
Flashback logs are not reclaimed by flash recovery area when using guaranteed
restore point (after dropping guaranteed restore point) or when changing
db_flashback_retention_target to a lower value.


11.2 recovery - free up spaceon DB_RECOVERY_FILE_DEST*

Make more disk space available and increase DB_RECOVERY_FILE_DEST_SIZE

SQL> alter system set db_recovery_file_dest_size=xG SCOPE=BOTH; -- (larger amount)

2. Move backups from the flash recovery area to tertiary storage such as tape.

RMAN>BACKUP RECOVERY AREA;

Note : Flashback logs cannot be backed up outside the recovery area and so are not backed up by BACKUP RECOVERY AREA.

3. Run DELETE for any files that have been removed with an operating system utility. If you use host operating system commands to delete files, then the database will not be aware of the resulting free space.

RMAN>CROSSCHECK BACKUP;
RMAN>CROSSCHECK ARCHIVELOG ALL;

RMAN>Delete expired backup;
RMAN>Delete expired archivelog all;
RMAN>Delete force obsolete;

4. Make sure that your guaranteed restore points are necessary. If not, delete them

SQL>Drop restore point ;

5. If flashback logs are enable then make sure you have enough space for all the flashback logs. If its not required then you can turn off flashback.

SQL>Alter database FLASHBACK OFF;

6. Review your backup retention policy and if required change the RMAN RETENTION POLICY

RMAN>CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

Exceptions :

- If RMAN is not part of backup strategy and archivelogs are going to FRA then manual intervention required for deletion of archivelogs. Periodically purse old archivelogs

for example

RMAN>Delete archivelog all completed before 'SYSDATE-7';

- By default RMAN backup goes to FRA. While taking RMAN backup if backup location explictly specified to flash recovery area location then those backup pieces are not considered as part of FRA for auto managment.

- For Archivelogs backup to FRA use USE_DB_RECOVERY_FILE_DEST rather than giving explict path of FRA

SQL> alter system set log_archive_dest_10='LOCATION=USE_DB_RECOVERY_FILE_DEST' scope=both;

- Bug 4911954

Details:
V$RECOVERY_FILE_DEST SPACE_USED and NUMBER_OF_FILES values may be wrong.
eg: Number of files in V$FLASH_RECOVERY_AREA_USAGE and V$RECOVERY_FILE_DEST are different.
This problem can lead to recovery area files being deleted even though there is no space pressure.
Fixed in : 10.2.0.3 , 11.1.0.6


- Bug 5106952

Details:
Flashback logs are not reclaimed by flash recovery area when using guaranteed
restore point (after dropping guaranteed restore point) or when changing
db_flashback_retention_target to a lower value.


sql escape character setup

in sqlplus setting the "&" as a literal, you need to do the following.

set escape \

select * from bla where blabla = 'xxx \&';

note: you should really set the escape sqlplus parameter in a glogin.sql or login.sql

transportable tablespaces

References: metalink note:77523.1

Refer to the relevant database documentation, correct version for restrictions when using this feature.

note:

a Ensure the following scripts have been run in the database. These would normally be run at database creation time.

$ORACLE_HOME/rdbms/admin/catplug.sql
$ORACLE_HOME/rdbms/admin/dbmsplts.sql
$ORACLE_HOME/rdbms/admin/prvtplts.plb

sql> desc dbms_plugts

b If running this procedure by a user other than sys then ensure the user has dba privileges and PUBLIC SYNONYMS for the following following have been created.

DBMS_PLUGTS
DBMS_TTS


1. Identify tablespaces that need to be migrated. note: Dependent data objects need to be incapsuated in the process. i.e.: Data + Index objects need to be included.

2. Put relevent tablespaces into readonly mode.

sql> alter tablespace ... readonly;

3. Export metadata from tablesspaces
unix> exp userid/password file=... tablespaces=... transport_tablespaces=y

note: multiple tablespaces need to be delimited by a comma.

4. Copy datafiles that make up the tablespaces identified above into the target environment.

5. Copy the export dat file created in step 3.

6. Import metadata into target database.

unix> imp ... tablespaces=... datafiles=... transport_tablespaces=y
note: tablespaces and datafiles need to be seperated by comma's.

7. Verify tablespaces are updated correctly by querying relevent views, dba_tablespaces and v$datafile.

8. Put tablespaces into read write mode in source and target databases.
sql> alter tablespace ... read write;

instr, substr

A couple of usefull oracle functions for manipulating strings.

instr(string1, string2, start position, nth appearance) returns the position where the string exists.

where
string1 is the original string to search from
string2 is the string to search in string 1
start posititon is optional and is the start position to start searching from
nth appearance is optional and is the number of occurances of string2 in string1.

instr('Tech on the net', 'e', 1, 3) would return 14.

substr(string1, start position, length) returns a string.

where
string1 is the string to check.
start position is the position in the string to start the substring from
length is optional and the length of the position.

substr('Tech on the net',1,5) would return 'Tech '

Often using a combination of the above functions is useful.

substr('Tech on the net',1, instr('Tech on the net', 'e', 1, 3)) would return ''Tech on the ne'

bug - 2942857 - OERI:12327 from complex view merging

Bug 2942857 - OERI:12327 from complex view merging
DOC ID 2942857.8

Fixed: 9.2.05, 10.1.0.2

Complex view merging may fail with ORA-600[12327] if an COUNT(*) is used and the query has a subquery predicate against the COUNT(*) .

Workaround: Set "_complex_view_merging"=false

Affects EDR, EDMT, DWH.

SGA

SGA is made up of the following -

Fixed - Small and always constant depending on platform, os and database version
Variable (large pool, shared pool, java pool)
Database Buffer
Redo Log Buffer

10G
Oracle introduced memory management. Basically you set the following parameters and oracle manages the memory for you. The theory is the database will adjust the memory components as on a as required basis.

sga_taget <> 0
statistics_level = TYPICAL

TNS Basics

If you are getting TNS-03505 when using tnsping or sqlplus then check the following -

sqlnet.ora - NAMES.DEFAULT_DOMAIN = world

then ensure your entry in the tnsnames.ora file has either a .world fixed to the ens of the database alias or there is an extention.

i.e. -

RISST01P.WORLD = ...

DB Links

Database Links cabn be created as PUBLIC or PRIVATE.

create public database link "dblink name" connect to "target db user" identified by "target db user password" using 'sid';

create database link "dblink name" connect to "target db user" identified by "target db user password" using 'sid';

DB Links use Oracle Networking from the installed oracle home.

Public Database Links will be used before private DB Links.

usage:

select * from dual@dblink

Password File

Create Password File.
orapwd file=password_file_name password=the_secret_password
orapwd file=password_file_name password=the_secret_password entries=n

Add users to password files:
grant sysdba to "user"
grant sysoper to "user"

View users who have SYSDBA or SYSOPER priv:
select * from v$pwfile_users;

Connect:
sqlplus ""user" as sysdba"

note:
in 9.2, connection info shows connection as sys.
in 8.1.x, connection info shows connection as self.

remove db parameter from spfile

Required when obsolete db parameter needs to be removed or reset from the spfile.

alter system reset "parameter name" scope=spfile sid='*'

tracing

More information on tracing.

Use dbms_system to trace sessions ...

Turn on level 12 tracing
exec sys.dbms_system.set_ev(sid, serial#, 10046, 12,'');

Turn off tracing
exec sys.dbms_system.set_ev(sid, serial#, 10046, 0, null);

To analyze with tkprof
tkprof

sys=yes sort='(prsela, exeela, fchela)'

where sys=yes, means data dictionary queries are included.

External Tables

Some useful information on directories and external tables

External tables rely on database directories, so firstly create a database directory if required.

create or replace directory ... as '...'
note: don't use parameters such as $HOME (as oracle can't work out what these are).

grant privileges to the directory created above:

grant read on directory ... to ...;

Next, create a table (see below for an example) and then start querying the external table.

CREATE TABLE ... ( ... some columns ...) ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER DEFAULT DIRECTORY ...
ACCESS PARAMETERS
(
records delimited by newline
fields REJECT ROWS WITH ALL NULL FIELDS
)
LOCATION
(
'add_partition.cfg'
)
) REJECT LIMIT unlimited

convert scn numbers

The scn number is very important. Whenever a change to the database is made (including the data dictionary) then this scn number is incremented. Oracle uses the scn number for backup / recovery purposes, flashback, recycle bin etc etc etc.

There are a few functions that can be used to convert scn numbers to dates and vice a versa, which may make life easier when working out what date these scn numbers refer to. These functions must look through control file as they will fail if the scn number is too old.

select dbms_flashback.get_system_change_number from dual;
select current_scn from v$database;

select scn_to_timestamp(to_char(timestamp_to_scn(sysdate),'99999999999')) from dual;
select to_char(timestamp_to_scn(sysdate),'99999999999') from dual

note:
These queries will convert the scn number to a timestamp and vice a versa.

recycle bin

The concept of the recycle bin is essentially the same as the recycle bin on the mac or windows.

if you drop a table, the table is not renamed and not dropped. You can query the recyclebin view to look at what objects are currently in the recycle bin. You can actually select from the table that was dropped, although it is quite an ungamely name. i.e.: Something like BIN$HGnc55/7rRPgQPeM/qQoRw==$0

You can restore the table by issuing:
flashback table "bla" to before drop;

scattered read and sequential read

Scattered Read - normally attributable to full table scans. These wait events occur through full table scans (FTS) as FTS normally scan multiple blocks at the same time, re: multi_block_read_count.

Sequential Read - read 1 block at a time are normally attributable to index scans. As index scans read 1 block at a time, multi block read doesn't apply with indexes and performance with sequetial reads could go down.

If any of these events are high then ensure stats are accurate, explain plans look OK and there isn't too much disk contention going on.

note: The amount of blocks that an index has to scan through before completing could have a big impact on performance. This also has implications for the cluster factor.

pga sql scripts

-- show sessions that are using more than 100M of PGA
select sid, name, round(value/(1024*1024))
from v$statname n, v$sesstat s
where
n.STATISTIC# = s.STATISTIC# and
name like 'session%memory%' and
round(value/(1024*1024)) > 100
order by 3, 1 asc;

-- Script to show total amount of PGA memory that is being used
select sum(value)/1024/1024 Mb
from v$sesstat s, v$statname n
where n.STATISTIC# = s.STATISTIC# and name = 'session pga memory';

interpreting trace files

Use some of the following notes to diagnoise trace files:

Note:39817.1 - Interpreting Raw SQL_TRACE and DBMS_SUPPORT.START_TRACE output

This note will give more information about the different lines that occur through the trace file and what some of the letters with numbers really mean.

Note:224270.1 - Trace Analyzer TRCANLZR - Interpreting Raw SQL Traces with Binds and/or Waits generated by EVENT 10046

This note will give information on the trcanlzr utility written by oracle. This utility installs scripts into the database and allows you to generate reports on the trace file. The installation documentation includes some useful bits on what the results mean.

Note:62294.1 - The DBMS_SUPPORT Package
This note gives further information about the dbms_support package, including how to install it and how to use it. According to the documentation, it provides more details than the standard scripts.