Transfer AWR data

We can transfer AWR information to another database:

1. The script @$ORACLE_HOME/rdbms/admin/awrextr.sql lets you extract AWR information.
   
2. Copy the dump file to the destination server where you want to import the AWR data.
3. Import the AWR data executing the @$ORACLE_HOME/rdbms/admin/awrload.sql in the destination database.

ASMLib with Unbreakable Linux Network

A very interesting document about ASMLib with Unbreakable Linux Network with a description about installing and upgrading.


http://www.oracle.com/technology/tech/linux/asmlib/uln.html

Oracle Performance Firefighting Intensive Seminar by Craig Shallahamer

I have attended a 2-day seminar about "Reactive Performance Management Intensive" by the Oracle guru Craig Shallahamer of OraPub. Craig presented his methodology about how to analyze performance problems. He focused on Response time analysis to diagnose and find solutions. He let me know the details about latching, buffer cache, shared pool and log buffer management.

He gave examples of Active Session History in Response Time analysis methodology.

I liked how he teached the different concepts and the clarity of his explanations.

Thanks to Craig for this fantastic seminar.

Saving and restoring Optimizer Statistics

When you are in the process of optimizing a query, it's very convenient to backup the optimizer statistics previous to gather specific statistics like histograms or other gathering methods.

You can do it following these steps:

1.- Create the statistics table where you hold the statistics of the table that you would like to backup

exec DBMS_STATS.CREATE_STAT_TABLE (ownname =>'BDD', stattab =>'STATS_TABLE', tblspace => 'USERS');

in this case BDD is the schema where the STATS_TABLE table will be created.

2.- Backup the statistics of the application table

exec dbms_stats.export_table_stats (ownname =>'TST10', tabname => 'T_PART',stattab =>'STATS_TABLE', statid => 'BKP20080702', cascade => TRUE, statown=>'BDD');

TST10 is the application schema and T_PART is the table whose statistics will be saved. With parameter statid we can associate an identifier to the backed up statistics.

3.- Importing the statistics

If we want to restore the statistics we can execute:

exec dbms_stats.import_table_stats (ownname =>'TST10', tabname => 'T_PART',stattab =>'STATS_TABLE', statid => 'BKP20080702',cascade => TRUE, statown=>'BDD');

Following these simple steps we can save and restore the optimizer statistics of a table.

Migrate users with DBMS_METADATA

Do you need to migrate users with all system and object grants ?. The DBMS_METADATA package helps us on Oracle 9i or higher. This script collects all information that you need:

-- DIR_OUTPUT: directory where create the output script file cr_users.sql.

create procedure EXTRACT_DDL_FOR_USERS as

no_grant exception;

pragma exception_init( no_grant, -31608 );
-- to skip the ORA-31608 when no data for the object requested.

cursor get_username is
select username
from dba_users;

file_handle utl_file.file_type;
stmt clob;

begin
file_handle:=utl_file.fopen('DIR_OUTPUT','cr_users.sql','w',32767);

for l_user in get_username loop

-- USERS
stmt := DBMS_METADATA.GET_DDL('USER', l_user.username);
utl_file.put(file_handle, stmt);

-- SYSTEM GRANTS
begin
stmt := DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', l_user.username);
exception
when no_grant then stmt := '-- no system grants';
end;
utl_file.put(file_handle,stmt);

-- OBJECT GRANTS
begin
stmt := DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', l_user.username);
exception
when no_grant then stmt := '-- no object grants';
end;
utl_file.put(file_handle,stmt);

-- ROLE GRANTS
begin
stmt := DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', l_user.username);
exception
when no_grant then stmt := '-- no role grants';
end;
utl_file.put(file_handle, stmt);

-- TABLESPACE QUOTAS
begin
stmt:=DBMS_METADATA.GET_GRANTED_DDL('TABLESPACE_QUOTA',l_user.username);
exception
when no_grant then stmt := '-- no tablespace quota';
end;
utl_file.put(file_handle, stmt);

-- DEFAULT ROLES
begin
stmt := DBMS_METADATA.GET_GRANTED_DDL('DEFAULT_ROLE', l_user.username);
exception
when no_grant then stmt := '-- no default role';
end;
utl_file.put(file_handle, stmt);

end loop;
utl_file.fclose(file_handle);
end;
/



Then you can execute it with:

set long 100000
begin
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);
extract_ddl_for_users;
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',FALSE);
end;
/


I use the SET_TRANSFORM_PARAM to add the terminator character statement (;).

Statistics on Dictionary tables

Gathering statistics on dictionary tables (owned by 'sys') is a recommended 10g best practice. The automatic statistics gathering job performs this task, but if we use a customized statistics gathering job and switch off the default automatic job then the recommended strategy is don't switch off the automatic job and configure the value of AUTOSTATS_TARGET to ORACLE instead of AUTO using the procedure DBMS_STATS.SET_PARAM:

BEGIN
DBMS_STATS.SET_PARAM('AUTOSTATS_TARGET','ORACLE');
END;
/

with this configuration we can use our customized job and let the automatic job that runs during the maintenance window gather statistics on dictionary tables.

RMAN. To be or not to be about Oracle Backup

I have found a lot of sites still using the user-managed backup method. We're in the RMAN century and I believe it's necessary migrate to RMAN for Oracle Backup&Recovery.

RMAN has a lot of useful functions that free the DBA of awful rutinary tasks. RMAN takes care of our backups and with a simple command you can backup your database without operating system scripts. RMAN provides block level corruption detection during backup and restore, optimize performance and space consumption with backup set compression. With 10g we have the new feature Flash Recovery Area, disk destination space for RMAN backup's that Oracle manage and it's not necessary to be worried about managing free space of our destination backup area. We have to dimension the Flash Recovery Area with the space needed for our backup policy retention.

There is a lot of information about RMAN but all that you must know is in "Backup and Recovery Advanced User’s Guide" that you can download of OTN