DB_ULTRA_SAFE a new GEM for High Availability

DB_ULTRA_SAFE is a new parameter introduced with Oracle 11gR1, and a fantastic new GEM for High Availability, that using Data Guard to configure on both the primary and standby will trigger the most comprehensive data corruption prevention and detection (and repair on 11gR2, see **) tool in the market.

** Starting in Oracle Database 11g Release 2 (11.2), the primary database automatically attempts to repair the corrupted block in real time by fetching a good version of the same block from a physical standby database.

Speaking simple, what this new functionality will do is use your Standby Database as a backup to correct automatically any data corruption on your primary database and vice-versa (again on 11.2).

The DB_ULTRA_SAFE initialization parameter also controls other data protection behavior in Oracle Database, such as requiring ASM to perform sequential mirror write I/Os.

You basically need to understand that when setting DB_ULTRA_SAFE it will control the behaviour of DB_BLOCK_CHECKING, DB_BLOCK_CHECKSUM, or DB_LOST_WRITE_PROTECT parameters for you, which mean:

When you set DB_ULTRA_SAFE to	Then the following parameters…
DATA_AND_INDEX (recommended by Oracle)	DB_BLOCK_CHECKING is set to FULL. DB_LOST_WRITE_PROTECT is set to TYPICAL. DB_BLOCK_CHECKSUM is set to FULL.
DATA_ONLY	DB_BLOCK_CHECKING is set to MEDIUM. DB_LOST_WRITE_PROTECT is set to TYPICAL. DB_BLOCK_CHECKSUM is set to FULL.

Lets’ Check all the parameters affected by DB_ULTRA_SAFE:

·                   DB_BLOCK_CHECKING (Introduced with Oracle 8.1.6) prevents memory and data corruptions, but it incurs some performance overhead on every block change.

·                   DB_BLOCK_CHECKSUM (Introduced with Oracle 8.1.6) detects redo and data block corruptions and detect corruptions on the primary database and protect the standby database. This parameter requires minimal CPU resources.

·                   DB_LOST_WRITE_PROTECT (also introduced with 11gR1) enable or disable a physical standby database to detect lost write corruptions on both the primary and physical standby database.

Important: if you explicitly set the DB_BLOCK_CHECKING, DB_BLOCK_CHECKSUM, or DB_LOST_WRITE_PROTECT parameters in the initialization parameter file, then the DB_ULTRA_SAFE parameter has no effect and no changes are made to the parameter values. Thus, if you specify the DB_ULTRA_SAFE parameter, do not explicitly set these underlying parameters.

To activate it, all you need to do is follow the following steps:

On the Primary Database:

1.      Set the DB_ULTRA_SAFE=DATA_AND_INDEX initialization parameter using:

2.      SQL> alter system set db_ultra_safe=dta_and_index scope=spfile;

3.      SQL> shutdown immediate (Shutdown your Database)

4.      SQL> startup (This will start your primary Database using your new parameter set in the SPFILE previously)

On the Physical Standby Database:

1.      Set the DB_ULTRA_SAFE=DATA_AND_INDEX initialization parameter using:

2.      SQL> alter system set db_ultra_safe=dta_and_index scope=spfile;

3.      SQL> startup nomount

4.      SQL> alter database mount standby database;

5.      SQL> alter database recover managed standby database disconnect from session;

If you are using your Standby Database on Read Only mode you also need to run the follow commands on your Physical Standby DB:

1.      SQL> alter database recover managed standby database cancel;

2.      SQL> alter database open read only;

If you decide to change later the Read Only Standby to Standby again, you just will need to run the following command:

1.      SQL> alter database recover managed standby database disconnect from session;

Hoping this information could help you in the future,

by   Francisco Munoz Alvarez

Connect Time Failover & Transparent Application Failover for Data Guard

Connect Time Failover & Transparent Application Failover for Data Guard

 

I was giving a 10g Data Guard course this week in Düsseldorf, demonstrating amongst others the possibility to configure Transparent Application Failover (TAF) for Data Guard. I always try to keep things as simple as seriously possible, in order to achieve an easy and good understanding of what I like to explain. Later on, things are getting complex by themselves soon enough

In my simple scenario, I have one Primary Database (prima) and one Physical Standby Database (physt). On my downloads page, you may find an installation guide for that scenario for 10g and for 11g. After a switchover or after a failover, the primary is going to be physt. The challenge is now to get the connect from the client side to the right (primary) database. That is called Connect Time Failover and is achieved as follows:

First, we make sure that the client uses a tnsnames.ora with a connect descriptor that uses a SERVICE_NAME instead of a SID

MYAPP =

 (DESCRIPTION =

 (ADDRESS_LIST =

 (ADDRESS = (PROTOCOL = TCP)(HOST = HostA)(PORT = 1521))

 (ADDRESS = (PROTOCOL = TCP)(HOST = HostB)(PORT = 1521))

 )

 (CONNECT_DATA =

 (SERVICE_NAME = myapp)

 )

 )

HostA is the host on which prima runs, HostB has physt running.

Second, we take care that the service myapp is offered only at the right database – only on the primary.Notice that the PMON background processes of both databases must be able to communicate with the (local) listeners in order to register the service myapp. If you don’t use the listener port 1521, they can’t. You have to point to that listener port then with the initialization parameter LOCAL_LISTENER.

We create and start now the service myapp manually on the primary:

begin

 dbms_service.create_service('myapp','myapp');

end;

/

begin

 DBMS_SERVICE.START_SERVICE('myapp');

end;

/

Then we create a trigger, that ensures that this service is only offered, if the database is in the primary role:

create trigger myapptrigg after startup on database

declare

 v_role varchar(30);

begin

 select database_role into v_role from v$database;

 if v_role = 'PRIMARY' then

 DBMS_SERVICE.START_SERVICE('myapp');

 else

 DBMS_SERVICE.STOP_SERVICE('myapp');

 end if;

end;

/

The event after startup is fired, if an instance changes from status MOUNT to OPEN. If you use a logical standby, it is not fired, because the logical standby remains in status OPEN. You may use the event after db_role_change in this case. The creation of the trigger and of the service is accompanied with redo protocol (the Data Dictionary has changed) and therefore also present at physt without any additional work to do there for the DBA. With the present setup, we have already achieved Connect Time Failover: Clients can use the same connect descriptor (myapp) to get to the right (primary) database now, regardless of switchover or failover.

But sessions that are connected to prima are disconnected if a switchover or failover to physt takes place. They have got to connect again then. We can change that, so that a Runtime Failover is possible, under ideal circumstances, that failover is even completely transparent to the client and proceeds without error messages. To achieve that, you don’t have to touch the tnsnames.ora on the client side. Instead, you do the following on the primary database:

begin

 dbms_service.modify_service

 ('myapp',

 FAILOVER_METHOD => 'BASIC',

 FAILOVER_TYPE => 'SELECT',

 FAILOVER_RETRIES => 200,

 FAILOVER_DELAY => 1);

end;

/

Connections to the service myapp are now automatically failed over together with the service to the new primary. Should they have done nothing during the time of the failover/switchover, or even if they had run a select statement, they will not receive any error but only notice a short interruption (about 20 seconds, in a typical case). Only if sessions have open transactions during the failover/switchover, they will receive error messages (“transaction must roll back”) after they try commit then.

I use to demonstrate that with a select on a table with 100000 rows that starts on the primary. Then I kill the SMON of that primary and the select stops at row 30000 something, waits a couple of seconds (maximal 200, with the above settings) and then continues on the new primary after the failover, fetching exactly the 100000 rows! That is always quite impressive and shows how robust Oracle Databases – especially combined with Data Guard – are

 

by Uwe Hesse,

How to change database character set

This article gives a overview of methods to change the database character set .


Change Oracle Database Character Set : NLS_CHARACTERSET

The syntax of the ALTER DATABASE CHARACTER SET statement is as follows:

ALTER DATABASE db_name CHARACTER SET new_character_set;

db_name is optional. The character set name should be specified without quotes. For example:

ALTER DATABASE CHARACTER SET AL32UTF8;

To change the database character set, perform the following steps:

Shut down the database, using either a SHUTDOWN IMMEDIATE or a SHUTDOWN NORMAL statement.
Do a full backup of the database because the ALTER DATABASE CHARACTER SET statement cannot be rolled back.
Complete the following statements:
4. STARTUP MOUNT;

5. ALTER SYSTEM ENABLE RESTRICTED SESSION;

6. ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;

7. ALTER SYSTEM SET AQ_TM_PROCESSES=0;

8. ALTER DATABASE OPEN;

9. ALTER DATABASE CHARACTER SET new_character_set;

10. SHUTDOWN IMMEDIATE; — or SHUTDOWN NORMAL;

11. STARTUP;

SQL > select * from nls_database_parameters ORDER BY PARAMETER;

Transportable Tablespaces

Transportable tablespaces were introduced in Oracle 8i to allow whole tablespaces to be copied between databases in the time it takes to copy the datafiles. In Oracle 8i one of the restrictions was that the block size of both databases must be the same. In Oracle 9i the introduction of multiple block sizes has removed this restriction. In this article I will run through a simple example of transporting a tablespace between two databases.


Oracle Data Pump in Oracle Database 10g (expdp and impdp)
Data Pump Enhancements in Oracle Database 11g Release 1
SQL Developer 3.1 Data Pump Wizards (expdp, impdp)


For this example I'm going to create a new tablespace, user and table to work with in the source database.

CONN / AS SYSDBA

CREATE TABLESPACE test_data
  DATAFILE '/u01/app/oracle/oradata/DB11G/test_data01.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 1M;

CREATE USER test_user IDENTIFIED BY test_user
  DEFAULT TABLESPACE test_data
  TEMPORARY TABLESPACE temp
  QUOTA UNLIMITED ON test_data;

GRANT CREATE SESSION, CREATE TABLE TO test_user;

CONN test_user/test_user

CREATE TABLE test_tab (
  id          NUMBER,
  description VARCHAR2(50),
  CONSTRAINT test_tab_pk PRIMARY KEY (id)
);

INSERT /*+ APPEND */ INTO test_tab (id, description)
SELECT level,
       'Description for ' || level
FROM   dual
CONNECT BY level <= 10000;

COMMIT;
Source Database

For a tablespace to be transportable it must be totally self contained. This can be checked using the DBMS_TTS.TRANSPORT_SET_CHECK procedure. The TS_LIST parameter accepts a comma separated list of tablespace names and the INCL_CONSTRAINTS parameter indicates if constraints should be included in the check.

CONN / AS SYSDBA
EXEC DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'TEST_DATA', incl_constraints => TRUE);

PL/SQL procedure successfully completed.

SQL>
The TRANSPORT_SET_VIOLATIONS view is used to check for any violations.

SELECT * FROM transport_set_violations;

no rows selected

SQL>
Assuming no violations are produced we are ready to proceed by switching the tablespace to read only mode.

SQL> ALTER TABLESPACE test_data READ ONLY;

Tablespace altered.

SQL>
Next we export the tablespace metadata using the export (expdp or exp) utility. If you are using 10g or above you should use the expdp utility. This requires a directory object pointing to a physical directory with the necessary permissions on the database server.

CONN / AS SYSDBA
CREATE OR REPLACE DIRECTORY temp_dir AS '/tmp/';
GRANT READ, WRITE ON DIRECTORY temp_dir TO system;
We can now export the tablespace metadata.

$ expdp userid=system/password directory=temp_dir transport_tablespaces=test_data dumpfile=test_data.dmp logfile=test_data_exp.log
If you are using a version prior to 10g, you do not need the directory object and your command would look something like this.

$ exp userid='system/password as sysdba' transport_tablespace=y tablespaces=test_data file=test_data.dmp log=test_data_exp.log
Copy the datafile to the appropriate location on the destination database server. Also copy the dump file to a suitable place on the destination database server. You may use binary FTP or SCP to perform this copy.

The source tablespace can now be switched back to read/write mode.

ALTER TABLESPACE test_data READ WRITE;

Tablespace altered.

SQL>
Destination Database

Create any users in the destination database that owned objects within the tablespace being transported, assuming they do not already exist.

CONN / AS SYSDBA

CREATE USER test_user IDENTIFIED BY test_user;
GRANT CREATE SESSION, CREATE TABLE TO test_user;
Now we import the metadata into the destination database. If you are using 10g or above you should use the impdp utility. This requires a directory object pointing to a physical directory with the necessary permissions on the database server.

CONN / AS SYSDBA
CREATE OR REPLACE DIRECTORY temp_dir AS '/tmp/';
GRANT READ, WRITE ON DIRECTORY temp_dir TO system;
We can now import the tablespace metadata.

$ impdp userid=system/password directory=temp_dir dumpfile=test_data.dmp logfile=test_data_imp.log transport_datafiles='/u01/app/oracle/oradata/DB11GB/test_data01.dbf'
If you are using a version prior to 10g, you do not need the directory object and your command would look something like this.

$ imp userid='system/password as sysdba' transport_tablespace=y datafiles='/u01/app/oracle/oradata/DB11GB/test_data01.dbf' tablespaces=test_data file=test_data.dmp log=test_data_imp.log
Switch the new tablespace into read write mode.

SQL> ALTER TABLESPACE test_data READ WRITE;

Tablespace altered.

SQL>
The tablespace is now available in the destination database.

SELECT tablespace_name, plugged_in, status
FROM   dba_tablespaces
WHERE  tablespace_name = 'TEST_DATA';

TABLESPACE_NAME                PLU STATUS
------------------------------ --- ---------
TEST_DATA                      YES ONLINE

1 row selected.

SQL>


By Tim.....

Performance Monitoring: Active Session History at work

New Feature Active Session History (ASH)  in oracle 10g. That was one major improvement – together with the Automatic Workload Repository (AWR) and the Automatic Database Diagnostic Monitor (ADDM) – of the 10g version. Way better than STATSPACK was before!

Imagine you are a DBA on a production system and get an emergency call like “The Database is dead slow!”. You are supposed to spot the cause as soon as possible. ASH kicks in here: We sample the Wait-Events of active sessions every second into the ASH-Buffer. with little effort from the command line like this :

-----------------------------------------
--
-- Top 10 CPU consumers in last 5 minutes
--
-----------------------------------------
select session_id, session_serial#, count(*)
from v$active_session_history
where session_state= 'ON CPU' and
sample_time > sysdate - interval '5' minute
group by session_id, session_serial#
order by count(*) desc
--------------------------------------------
--
-- Top 10 waiting sessions in last 5 minutes
--
--------------------------------------------
select * from
(
select session_id, session_serial#,count(*)
from v$active_session_history
where session_state='WAITING'  and  sample_time >  sysdate - interval '5' minute
group by session_id, session_serial#
order by count(*) desc
)
where rownum <= 10;
--------------------
--
-- Who is that SID?
--
--------------------
select  serial#,username, osuser, machine, program, resource_consumer_group, client_info
from v$session where sid=SID;
-------------------------
--
-- What did that SID do?
--
-------------------------
select distinct sql_id, session_serial# from v$active_session_history
where sample_time >  sysdate - interval '5' minute
and session_id=SID;
----------------------------------------------
--
-- Retrieve the SQL from the Library Cache:
--
----------------------------------------------
select sql_text from v$sql where sql_id='SQL_ID';


By  Uwe Hesse ....

adrci: A survival guide for the DBA

Starting with 11gR1, we have a new way to deal with Oracle Errors & Tracefiles: There is now a special command line utility dedicated for that purpose called adrci (Automatic Diagnostic Repository Command Interpreter). This posting is intended to show you the (in my view) essential commands, a DBA ought to know in order to use it. We will look at
 1.Viewing the alert.log
 2.The relation between incident & problem
 3.Creation of Packages & ZIP files to send to Oracle Support
 4.Managing, especially purging tracefiles

I will at first create a problem. Don’t do that with your Production Database! Especially: Never do DML on dictionary tables!
 [oracle@uhesse ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Wed Jun 1 10:25:06 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0    Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
SQL> show parameter diagnostic
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest              string     /u01/app/oracle
SQL> grant dba to adam identified by adam;
Grant succeeded.
SQL> connect adam/adam
Connected.
SQL> create table t (n number);
Table created.
SQL> select object_id from user_objects;
 OBJECT_ID
----------
     75719
SQL> connect / as sysdba
Connected.
SQL> update tab$ set cols=2 where obj#=75719;
1 row updated.
SQL> commit;
Commit complete.
SQL> alter system flush shared_pool;
System altered.
SQL> connect adam/adam
Connected.
SQL> select * from t;
select * from t
              *
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 2236
Session ID: 29 Serial number: 9
I flushed the Shared Pool to get the Data Dictionary Cache empty. Else the select may not crash the session as it did. Imagine the user calls me now on the phone. Our first idea as an experienced DBA: We look at the alert.log! Right so. Please notice that we now have two different kinds of the alert.log.

One is present in the conventional text format, per OFA in $ORACLE_BASE/diag/rdbms/name of the db/name of the instance/trace This location is determined by the new initialization parameter DIAGNOSTIC_DEST, while BACKGROUND_DUMP_DEST is deprecated in 11g.

1. Viewing the alert.log

The other one is in XML format placed in $ORACLE_BASE/diag/rdbms/name of the db/name of the instance/alert This version of the alert.log is accessed by adrci:
 [oracle@uhesse ~]$ adrci
ADRCI: Release 11.2.0.2.0 - Production on Wed Jun 1 10:20:08 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
ADR base = "/u01/app/oracle"
adrci> show home
ADR Homes:
diag/tnslsnr/uhesse/listener
diag/rdbms/orcl/orcl
Please notice that we have different ADR Homes. In my case only two, because I am not using Grid Infrastructure on this Demo System, else there would be another one. I specify my Database Home first. Then I look at the alert.log. Good news if you are on Windows: Finally, you can tail -f your alert.log also
adrci> set home diag/rdbms/orcl/orcl
adrci> show alert -tail -f
2011-06-01 10:16:35.337000 +02:00
db_recovery_file_dest_size of 4032 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Starting background process CJQ0
CJQ0 started with pid=21, OS id=2204
2011-06-01 10:18:42.668000 +02:00
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x0] [PC:0x90D891A, qcstda()+702] [flags: 0x0, count: 1]
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2236.trc  (incident=6153):
ORA-07445: exception encountered: core dump [qcstda()+702] [SIGSEGV] [ADDR:0x0] [PC:0x90D891A] [Address not mapped to object] []
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_6153/orcl_ora_2236_i6153.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2011-06-01 10:18:47.518000 +02:00
Dumping diagnostic data in directory=[cdmp_20110601101847], requested by (instance=1, osid=2236), summary=[incident=6153].
2011-06-01 10:18:48.727000 +02:00
Sweep [inc][6153]: completed
Sweep [inc2][6153]: completed
2. The relation between Incident & Problem

You see the incident was recorded in the alert.log. And it tells you “Use ADRCI or Support Workbench to package the incident.” We will soon see how to do that. First I’d like to explain the relation between incident and problem: An incident is the concrete occurrence of a problem. In other words: The same problem may have multiple incidents. To show that, I will open another terminal and do again a select against the table t, while still tailing the alert log from the first session.

Second terminal:
 [oracle@uhesse ~]$ sqlplus adam/adam
SQL*Plus: Release 11.2.0.2.0 Production on Wed Jun 1 10:21:52 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from t where n=42;
select * from t where n=42
                         *
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 2299
Session ID: 36 Serial number: 11
First terminal:
 2011-06-01 10:21:31.367000 +02:00
Starting background process SMCO
SMCO started with pid=19, OS id=2268
2011-06-01 10:22:08.781000 +02:00
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x0] [PC:0x90D891A, qcstda()+702] [flags: 0x0, count: 1]
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2299.trc  (incident=6201):
ORA-07445: exception encountered: core dump [qcstda()+702] [SIGSEGV] [ADDR:0x0] [PC:0x90D891A] [Address not mapped to object] []
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_6201/orcl_ora_2299_i6201.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2011-06-01 10:22:11.135000 +02:00
Dumping diagnostic data in directory=[cdmp_20110601102211], requested by (instance=1, osid=2299), summary=[incident=6201].
2011-06-01 10:22:13.370000 +02:00
Sweep [inc][6201]: completed
Sweep [inc2][6201]: completed
I have seen the second incident recorded. I exit out of the tail -f with CTRL+C and continue:
 adrci> show problem
ADR Home = /u01/app/oracle/diag/rdbms/orcl/orcl:
*************************************************************************
PROBLEM_ID           PROBLEM_KEY                                                 LAST_INCIDENT        LASTINC_TIME                            
-------------------- ----------------------------------------------------------- -------------------- ----------------------------------------
1                    ORA 7445 [qcstda()+702]                                     6201                 2011-06-01 10:22:08.924000 +02:00      
1 rows fetched
So I have one problem with the ID 1 and the last incident occurred at 10:22. Are there more?
 adrci> show incident
ADR Home = /u01/app/oracle/diag/rdbms/orcl/orcl:
*************************************************************************
INCIDENT_ID          PROBLEM_KEY                                                 CREATE_TIME                             
-------------------- ----------------------------------------------------------- ----------------------------------------
6153                 ORA 7445 [qcstda()+702]                                     2011-06-01 10:18:42.995000 +02:00      
6201                 ORA 7445 [qcstda()+702]                                     2011-06-01 10:22:08.924000 +02:00      
2 rows fetched
I want to see some more detail about the incidents:
 adrci> show incident -mode detail -p "incident_id=6201"
ADR Home = /u01/app/oracle/diag/rdbms/orcl/orcl:
*************************************************************************
**********************************************************
INCIDENT INFO RECORD 1
**********************************************************
   INCIDENT_ID                   6201
   STATUS                        ready
   CREATE_TIME                   2011-06-01 10:22:08.924000 +02:00
   PROBLEM_ID                    1
   CLOSE_TIME                  
   FLOOD_CONTROLLED              none
   ERROR_FACILITY                ORA
   ERROR_NUMBER                  7445
   ERROR_ARG1                    qcstda()+702
   ERROR_ARG2                    SIGSEGV
   ERROR_ARG3                    ADDR:0x0
   ERROR_ARG4                    PC:0x90D891A
   ERROR_ARG5                    Address not mapped to object
   ERROR_ARG6                  
   ERROR_ARG7                  
   ERROR_ARG8                  
   ERROR_ARG9                  
   ERROR_ARG10                 
   ERROR_ARG11                 
   ERROR_ARG12                 
   SIGNALLING_COMPONENT          SQL_Parser
   SIGNALLING_SUBCOMPONENT     
   SUSPECT_COMPONENT           
   SUSPECT_SUBCOMPONENT        
   ECID                        
   IMPACTS                       0
   PROBLEM_KEY                   ORA 7445 [qcstda()+702]
   FIRST_INCIDENT                6153
   FIRSTINC_TIME                 2011-06-01 10:18:42.995000 +02:00
   LAST_INCIDENT                 6201
   LASTINC_TIME                  2011-06-01 10:22:08.924000 +02:00
   IMPACT1                       0
   IMPACT2                       0
   IMPACT3                       0
   IMPACT4                       0
   KEY_NAME                      ProcId
   KEY_VALUE                     25.3
   KEY_NAME                      Client ProcId
   KEY_VALUE                     oracle@uhesse (TNS V1-V3).2299_140262306875136
   KEY_NAME                      PQ
   KEY_VALUE                     (0, 1306916528)
   KEY_NAME                      SID
   KEY_VALUE                     36.11
   OWNER_ID                      1
   INCIDENT_FILE                 /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2299.trc
   OWNER_ID                      1
   INCIDENT_FILE                 /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_6201/orcl_ora_2299_i6201.trc
1 rows fetched
I want to look at the incident tracefile mentioned above:
 adrci> show trace /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_6201/orcl_ora_2299_i6201.trc
/u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_6201/orcl_ora_2299_i6201.trc
 ----------------------------------------------------------
 LEVEL PAYLOAD
 ----- ------------------------------------------------------------------------------------------------------------------------------------------------
 Dump file /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_6201/orcl_ora_2299_i6201.trc
 Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
 With the Partitioning, OLAP, Data Mining and Real Application Testing options
 ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1
 System name:      Linux
 Node name:        uhesse
 Release:  2.6.32-100.28.5.el6.x86_64
 Version:  #1 SMP Wed Feb 2 18:40:23 EST 2011
 Machine:  x86_64
 Instance name: orcl
 Redo thread mounted by this instance: 1
 Oracle process number: 25
 Unix process pid: 2299, image: oracle@uhesse (TNS V1-V3)
*** 2011-06-01 10:22:08.929
 *** SESSION ID:(36.11) 2011-06-01 10:22:08.929
 *** CLIENT ID:() 2011-06-01 10:22:08.929
 *** SERVICE NAME:(SYS$USERS) 2011-06-01 10:22:08.929
 *** MODULE NAME:(SQL*Plus) 2011-06-01 10:22:08.929
 *** ACTION NAME:() 2011-06-01 10:22:08.929
Dump continued from file: /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2299.trc
 1>     ***** Error Stack *****
 ORA-07445: exception encountered: core dump [qcstda()+702] [SIGSEGV] [ADDR:0x0] [PC:0x90D891A] [Address not mapped to object] []
 1<     ***** Error Stack *****
 1>     ***** Dump for incident 6201 (ORA 7445 [qcstda()+702]) *****
 2>      ***** Beginning of Customized Incident Dump(s) *****
 2>      ***** Beginning of Customized Incident Dump(s) *****
 Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x0] [PC:0x90D891A, qcstda()+702] [flags: 0x0, count: 1]
 Registers:
 %rax: 0x0000000000000000 %rbx: 0x00007f915c77f0e0 %rcx: 0x0000000000000007
 %rdx: 0x0000000000000000 %rdi: 0x00007f915c77be98 %rsi: 0x0000000000000000
 %rsp: 0x00007fffc65178e0 %rbp: 0x00007fffc6517960  %r8: 0x0000000000000028
 %r9: 0x0000000000002000 %r10: 0x00000000093849c0 %r11: 0x0000000000000168
 %r12: 0x00007f915c77ade8 %r13: 0x000000008edbb178 %r14: 0x00007f915c777da0
 %r15: 0x00007f915c77ae28 %rip: 0x00000000090d891a %efl: 0x0000000000010246
 qcstda()+686 (0x90d890a) mov -0x40(%rbp),%rdi
 qcstda()+690 (0x90d890e) mov %rdx,0x18(%rbx)
 qcstda()+694 (0x90d8912) mov 0x60(%r15),%rsi
 qcstda()+698 (0x90d8916) mov %ecx,0x8(%r15)
 > qcstda()+702 (0x90d891a) mov %ecx,(%rsi)
 qcstda()+704 (0x90d891c) mov 0x78(%rdi),%rdx
 qcstda()+708 (0x90d8920) test %rdx,%rdx
 qcstda()+711 (0x90d8923) jnz 0x90d8d03
 qcstda()+717 (0x90d8929) mov -0x70(%rbp),%rdi
*** 2011-06-01 10:22:08.963
 dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x3, level=3, mask=0x0)
 3>       ***** Current SQL Statement for this session (sql_id=8r222qucmawdt) *****
          select * from t where n=42
 3<       ***** current_sql_statement ***** 3 
3. Creation of Packages & ZIP files to send to Oracle Support

I may not be able to solve the problem myself. Oracle Support will help me with that one. I gather all the required information with a method called “Incident Packaging Service” (IPS):
 adrci> ips create package problem 1 correlate all
 Created package 2 based on problem id 1, correlation level all
This did not yet create a ZIP file and is therefore referred to as “Logical Package”. The ZIP file is generated from the Logical Package that was created:
 adrci> ips generate package 2 in "/home/oracle" 
Generated package 2 in file /home/oracle/ORA7445qc_20110601112533_COM_1.zip, mode complete
4. Managing, especially purging of tracefiles

Now to the management of tracefiles. You may notice that 11g creates lots of tracefiles that need to be purged from time to time. In fact, this is done automatically, but you may want to change the default purge policy:
 adrci> show tracefile -rt
 01-JUN-11 10:31:48  diag/rdbms/orcl/orcl/trace/orcl_mmon_2106.trc
 01-JUN-11 09:43:43  diag/rdbms/orcl/orcl/trace/orcl_ckpt_2100.trc
 01-JUN-11 09:22:13  diag/rdbms/orcl/orcl/trace/alert_orcl.log
 01-JUN-11 09:22:11  diag/rdbms/orcl/orcl/trace/orcl_diag_2088.trc
 01-JUN-11 09:22:10  diag/rdbms/orcl/orcl/trace/orcl_ora_2299.trc
 01-JUN-11 09:22:10  diag/rdbms/orcl/orcl/incident/incdir_6201/orcl_ora_2299_i6201.trc
 01-JUN-11 09:18:47  diag/rdbms/orcl/orcl/trace/orcl_ora_2236.trc
 01-JUN-11 09:18:47  diag/rdbms/orcl/orcl/incident/incdir_6153/orcl_ora_2236_i6153.trc
 01-JUN-11 09:17:19  diag/rdbms/orcl/orcl/trace/orcl_dbrm_2090.trc
 01-JUN-11 09:16:44  diag/rdbms/orcl/orcl/trace/orcl_j002_2210.trc
 01-JUN-11 09:16:30  diag/rdbms/orcl/orcl/trace/orcl_ora_2187.trc
 01-JUN-11 09:16:19  diag/rdbms/orcl/orcl/trace/orcl_mman_2094.trc
 01-JUN-11 09:16:16  diag/rdbms/orcl/orcl/trace/orcl_vktm_2082.trc
 01-JUN-11 09:16:14  diag/rdbms/orcl/orcl/trace/orcl_ora_2016.trc
 30-MAY-11 14:07:02  diag/rdbms/orcl/orcl/trace/orcl_mmon_2093.trc
 30-MAY-11 11:15:30  diag/rdbms/orcl/orcl/trace/orcl_ora_3414.trc
 30-MAY-11 11:00:01  diag/rdbms/orcl/orcl/trace/orcl_j000_2245.trc
 30-MAY-11 10:56:58  diag/rdbms/orcl/orcl/trace/orcl_dbrm_2077.trc
 30-MAY-11 10:56:20  diag/rdbms/orcl/orcl/trace/orcl_j002_2201.trc
 30-MAY-11 10:56:06  diag/rdbms/orcl/orcl/trace/orcl_ora_2178.trc
 30-MAY-11 10:55:58  diag/rdbms/orcl/orcl/trace/orcl_mman_2081.trc
 30-MAY-11 10:55:55  diag/rdbms/orcl/orcl/trace/orcl_vktm_2069.trc
 30-MAY-11 10:55:53  diag/rdbms/orcl/orcl/trace/orcl_ora_2006.trc
 27-MAY-11 10:53:25  diag/rdbms/orcl/orcl/trace/orcl_mmon_8589.trc
 27-MAY-11 10:17:05  diag/rdbms/orcl/orcl/trace/orcl_ora_11390.trc
 27-MAY-11 09:26:41  diag/rdbms/orcl/orcl/trace/orcl_ora_10739.trc
 27-MAY-11 09:23:53  diag/rdbms/orcl/orcl/trace/orcl_dbrm_8573.trc
 27-MAY-11 09:22:58  diag/rdbms/orcl/orcl/trace/orcl_ora_8687.trc
 27-MAY-11 09:22:54  diag/rdbms/orcl/orcl/trace/orcl_mman_8577.trc
 27-MAY-11 09:22:50  diag/rdbms/orcl/orcl/trace/orcl_vktm_8565.trc
 27-MAY-11 09:22:48  diag/rdbms/orcl/orcl/trace/orcl_ora_8516.trc
 27-MAY-11 09:22:44  diag/rdbms/orcl/orcl/trace/orcl_ora_8515.trc
 27-MAY-11 09:22:44  diag/rdbms/orcl/orcl/trace/orcl_vktm_8347.trc
 27-MAY-11 09:21:24  diag/rdbms/orcl/orcl/trace/orcl_dbrm_8355.trc
 27-MAY-11 09:20:29  diag/rdbms/orcl/orcl/trace/orcl_ora_8470.trc
 27-MAY-11 09:20:28  diag/rdbms/orcl/orcl/trace/orcl_mmon_8371.trc
 27-MAY-11 09:20:28  diag/rdbms/orcl/orcl/trace/orcl_ora_8381.trc
 27-MAY-11 09:20:26  diag/rdbms/orcl/orcl/trace/orcl_mman_8359.trc
 27-MAY-11 09:20:20  diag/rdbms/orcl/orcl/trace/orcl_ora_8299.trc
 27-MAY-11 09:20:15  diag/rdbms/orcl/orcl/trace/orcl_ora_8297.trc
 27-MAY-11 09:20:15  diag/rdbms/orcl/orcl/trace/orcl_vktm_8096.trc
 27-MAY-11 09:20:07  diag/rdbms/orcl/orcl/trace/orcl_ora_8296.trc
 27-MAY-11 09:19:42  diag/rdbms/orcl/orcl/trace/orcl_ora_8285.trc
 27-MAY-11 09:19:33  diag/rdbms/orcl/orcl/trace/orcl_dm00_8271.trc
 27-MAY-11 09:19:33  diag/rdbms/orcl/orcl/trace/orcl_dw00_8273.trc
 27-MAY-11 09:19:11  diag/rdbms/orcl/orcl/trace/orcl_dbrm_8104.trc
 27-MAY-11 09:18:53  diag/rdbms/orcl/orcl/trace/orcl_ora_8267.trc
 27-MAY-11 09:18:33  diag/rdbms/orcl/orcl/trace/orcl_j001_8237.trc
 27-MAY-11 09:18:26  diag/rdbms/orcl/orcl/trace/orcl_mmon_8219.trc
 27-MAY-11 09:18:23  diag/rdbms/orcl/orcl/trace/orcl_ora_8231.trc
 27-MAY-11 09:18:22  diag/rdbms/orcl/orcl/trace/orcl_cjq0_8229.trc
 27-MAY-11 09:18:16  diag/rdbms/orcl/orcl/trace/orcl_ora_8131.trc
 27-MAY-11 09:18:14  diag/rdbms/orcl/orcl/trace/orcl_m000_8223.trc
 27-MAY-11 09:18:13  diag/rdbms/orcl/orcl/trace/orcl_mman_8108.trc
 27-MAY-11 09:18:05  diag/rdbms/orcl/orcl/trace/orcl_ora_8048.trc
 27-MAY-11 09:17:59  diag/rdbms/orcl/orcl/trace/orcl_vktm_7920.trc
 27-MAY-11 09:17:59  diag/rdbms/orcl/orcl/trace/orcl_ora_8046.trc
 27-MAY-11 09:17:00  diag/rdbms/orcl/orcl/trace/orcl_mman_7932.trc
 27-MAY-11 09:16:56  diag/rdbms/orcl/orcl/trace/orcl_ora_7954.trc
 27-MAY-11 09:16:51  diag/rdbms/orcl/orcl/trace/orcl_ora_7871.trc
I have already got some tracefiles. How long are they going to stay?
 adrci> show control
ADR Home = /u01/app/oracle/diag/rdbms/orcl/orcl:
*************************************************************************
ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                            LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        ADRDIR_VERSION       ADRSCHM_VERSION      ADRSCHMV_SUMMARY     ADRALERT_VERSION     CREATE_TIME                             
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------
1335663986           720                  8760                 2011-05-27 10:16:46.997118 +02:00                                                                                          1                    2                    80                   1                    2011-05-27 10:16:46.997118 +02:00      
1 rows fetched
The ordinary tracefiles will stay for 30 days (720 hours), while files like incident files stay one year (8760 hours) by default. We can change that policy with for example:
 adrci> set control (SHORTP_POLICY = 360)
adrci> set control (LONGP_POLICY = 2190)
adrci> show control
ADR Home = /u01/app/oracle/diag/rdbms/orcl/orcl:
*************************************************************************
ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                            LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        ADRDIR_VERSION       ADRSCHM_VERSION      ADRSCHMV_SUMMARY     ADRALERT_VERSION     CREATE_TIME
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------
1335663986           360                  2190                 2011-06-01 11:42:17.208064 +02:00                                                                                          1                    2                    80                   1                    2011-05-27 10:16:46.997118 +02:00
1 rows fetched
Also, we may want to purge tracefiles manually. Following command will manually purge all tracefiles older than 2 days (2880 minutes):
 adrci> purge -age 2880 -type trace
adrci> show tracefile -rt
   01-JUN-11 10:46:54  diag/rdbms/orcl/orcl/trace/orcl_mmon_2106.trc
   01-JUN-11 09:43:43  diag/rdbms/orcl/orcl/trace/orcl_ckpt_2100.trc
   01-JUN-11 09:22:13  diag/rdbms/orcl/orcl/trace/alert_orcl.log
   01-JUN-11 09:22:11  diag/rdbms/orcl/orcl/trace/orcl_diag_2088.trc
   01-JUN-11 09:22:10  diag/rdbms/orcl/orcl/incident/incdir_6201/orcl_ora_2299_i6201.trc
   01-JUN-11 09:22:10  diag/rdbms/orcl/orcl/trace/orcl_ora_2299.trc
   01-JUN-11 09:18:47  diag/rdbms/orcl/orcl/incident/incdir_6153/orcl_ora_2236_i6153.trc
   01-JUN-11 09:18:47  diag/rdbms/orcl/orcl/trace/orcl_ora_2236.trc
   01-JUN-11 09:17:19  diag/rdbms/orcl/orcl/trace/orcl_dbrm_2090.trc
   01-JUN-11 09:16:44  diag/rdbms/orcl/orcl/trace/orcl_j002_2210.trc
   01-JUN-11 09:16:30  diag/rdbms/orcl/orcl/trace/orcl_ora_2187.trc
   01-JUN-11 09:16:19  diag/rdbms/orcl/orcl/trace/orcl_mman_2094.trc
   01-JUN-11 09:16:16  diag/rdbms/orcl/orcl/trace/orcl_vktm_2082.trc
   01-JUN-11 09:16:14  diag/rdbms/orcl/orcl/trace/orcl_ora_2016.trc
   30-MAY-11 14:07:02  diag/rdbms/orcl/orcl/trace/orcl_mmon_2093.trc
   30-MAY-11 11:15:30  diag/rdbms/orcl/orcl/trace/orcl_ora_3414.trc
   30-MAY-11 11:00:01  diag/rdbms/orcl/orcl/trace/orcl_j000_2245.trc
   30-MAY-11 10:56:58  diag/rdbms/orcl/orcl/trace/orcl_dbrm_2077.trc
   30-MAY-11 10:56:20  diag/rdbms/orcl/orcl/trace/orcl_j002_2201.trc
   30-MAY-11 10:56:06  diag/rdbms/orcl/orcl/trace/orcl_ora_2178.trc
   30-MAY-11 10:55:58  diag/rdbms/orcl/orcl/trace/orcl_mman_2081.trc
   30-MAY-11 10:55:55  diag/rdbms/orcl/orcl/trace/orcl_vktm_2069.trc
   30-MAY-11 10:55:53  diag/rdbms/orcl/orcl/trace/orcl_ora_2006.trc
Conclusion: With adrci, we have a new and efficient utility to deal with Oracle Errors – especially for collecting information to send them to Oracle Support. This functionality is called Incident Packaging Service. 11g is generating lots of tracefiles. We can control the purging policy of them with adrci. Finally, we can now tail -f our alert.log from any OS.



By  Uwe Hesse