Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog

January 10, 2007

Great news from Oracle Türkiye today :)

Filed under: Oracle Other — H.Tonguç Yılmaz @ 5:19 pm

I recieved an email with subject “Oracle University Türkiye Ocak-Nisan 2007 Eğitim Programı” and in the mail it was said that Mr.Jonathan Lewis will be in İstanbul for a seminar;

“..Oracle database konusunda Oracle Gurusu olan Jonathan Lewis “Cost Based Optimization, Indexing Strategies and Explain Plan” adlı eğitim semineriyle 27-28 Şubat tarihlerinde İstanbul’da olacak. Eğitim semineri içeriğine bu linki tıklayarak ulaşabilirsiniz.

Eğitim Adı: Cost Based Optimization, Indexing Strategies and Explain Plan
Eğitim Tarihi: 27-28 Şubat 2007
..
Daha fazla bilgi için, lütfen 0212 329 68 48 numaralı telefondan veya educations_tr@oracle.com e-posta adresinden bizlere ulaşınız..”

I learned these details immediately;
– ~40 attandance is planned for this event,
– the place will be announced later, most probably a hotel’s meeting lounge,
– the price will be around 750 Euro per person

As a group we were still working on his new book so this was a really good news for me, lots of questions to ask :) I also post a comment to Mr.Lewis’s blog and tried to show how excited we are :)

So who is Mr.Jonathan Lewis and why is he so important to us?

These lines are from the Oracle ACE list at OTN;
“Jonathan Lewis is a freelance consultant who specializes in design, troubleshooting, and advanced training in best use of the Oracle database engine. He is a frequent speaker on the international circuit, with presentations lasting from 45 minutes to three days. His first book, Practical Oracle8i, is still worth reading, despite being two generations behind the latest version, and his most recent book, Cost Based Oracle Fundamentals (Apress), is currently receiving enthusiastic reviews. Jonathan is currently a director of the UK Oracle User Group.”

More than these Mr.Lewis is one of three people who I admire most, follow and except as my masters through the last eight years of my Oracle database administration and development career. Other two are Mr.Thomas Kyte and Mr.Steven Feuerstein. I trust these experts because they talk with numbers, with proofs. I try to read every single line they write, more than that I try to understand what they are trying to explain, I work on their books and blog posts. This pain is worthy, I experienced it with the development of my career and I advise all of the starters to read and follow them.

Mr.Jonathan Lewis was choosen as “Oracle Author of the year 2006” with his new book and Mr.Steven Feuerstein was choosen as “Oracle Pl/Sql Developer of the year 2006” by the readers of Oracle Magazine.

They are the owners of all these life saver mottas and of course more;
Universal mantra on data intensive processing
Beware of “Question Authorities”
The moral of the story

I hope to meet Mr.Lewis during this seminar, but since he is no different than Eric Clapton or
Clint Eastwood for me I am really not sure if I will be able to speek some words in front of him :) But I am sure this time not reading but llistenening to him will be something very special in my life!

Mr.Lewis’s official site
Mr.Lewis’s blog
Practical Oracle 8i: Building Efficient Databases
Cost-Based Oracle Fundamentals

Advertisements

Oracle Best Practices Part 1

Filed under: Oracle Best Practices — H.Tonguç Yılmaz @ 2:58 pm

1- Put your Database into ARCHIVELOG Mode

Although backup and recovery operations can vary from one business to another, the basic principles follow these four simple steps:

1. Run the database in ARCHIVELOG mode and archive redo logs to multiple locations,
2. Multiplex the online redo logs,
3. Maintain multiple concurrent backups of the control file,
4. Take frequent backups of physical datafiles and store them in a safe place, making multiple copies if possible
5. Periodically doing a complete restore to test your procedures.

As long as you have backups of the database and archive redo logs in a safe storage, the original database can be in safe.

Oracle can be run in either of two modes:
* ARCHIVELOG – Oracle archives the filled online redo log files before reusing them in the cycle.
* NOARCHIVELOG – Oracle does not archive the filled online redo log files before reusing them in the cycle.

Running the database in ARCHIVELOG mode has the following benefits:
* The database can be completely recovered from both instance and media failure.
* The user can perform online backups, i.e., back up tablespaces while the database is open and available for use.
* Archived redo logs can be transmitted and applied to the standby database
* Oracle supports multiplexed archive logs to avoid any possible single point of failure on the archive logs.
* The user has more recovery options, such as the ability to perform tablespace-point-in-time recovery (TSPITR)

Running the database in NOARCHIVELOG mode has the following consequences:
* The user can only back up the database while it is completely closed after a clean shutdown.
* Typically, the only media recovery option is to restore the whole database, which causes the loss of all transactions issued since the last backup.

Most of the High Availability features of Oracle require you to enable ARCHIVELOG mode for your database. When you enable this mode redo logs will be archived instead of overwritten and they are utilized by RMAN, Data Guard, LogMiner, Flashback and many others. I personally set my laptop XE database also ARCHIVELOG mode on, since there is no capacity to perform any type of point in time recovery operations or online backups on NOARCHIVELOG mode, no need to be a mission critical system..

Oracle databases are created initially in NOARCHIVELOG mode by default but enabling ARCHIVELOG mode is simple;


conn / as sysdba
-- Lets start by checking the current archive mode
archive log list

Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     24
Current log sequence           25

-- close the database
shutdown immediate;
 -- now startup the database at mount mode, switch to archivelog and open it
startup mount exclusive;
alter database archivelog;
alter database open;

alter system switch logfile;
archive log list

Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     25
Next log sequence to archive   25
Current log sequence           26

Also Oracle provides data dictionary views for the archived redo logs like:
v$parameter – Shows the location of the flash recovery area or archive destination where archived redo logs are created.
v$archived_log – Information about archived redo logs.
v$log_history – Contains information on previous redo logs

2- Comment on Tables and Columns similar to your PL/SQL or SQL Source Codes

COMMENT statement adds a comment about a table, view, materialized view, or column into the Oracle data dictionary. To drop a comment from the database, set it to the empty string ‘ ‘.

This feature is what I call documentation in the database, very helpfull always, even you can not remember why you created a column after some years of development;


-- DROP TABLE tbl PURGE;
CREATE TABLE tbl (ncol number, vcol varchar2(16), dcol date);

-- Comment a table : COMMENT ON TABLE <table_name> IS '<comment>';
COMMENT ON TABLE tbl IS 'Speed xxx Responsible yyy Description zzz ...';
SELECT table_name, comments FROM user_tab_comments WHERE comments LIKE 'Speed xxx%';

TABLE_NAME                     COMMENTS

TBL                            Speed xxx Responsible yyy Description zzz ...

-- Comment a column :      COMMENT ON COLUMN <table_name.column_name> IS '<comment>';
COMMENT ON COLUMN tbl.ncol IS 'n Digit Customer Code, Genereted From m Sequence ...';
SELECT table_name, column_name, comments
FROM user_col_comments
WHERE comments LIKE '%Customer%';

TABLE_NAME                     COLUMN_NAME                    COMMENTS

TBL                            NCOL                           n Digit Customer Code, Genereted From m Sequence ...

3- Use DBMS_STATS for statistic collection

Collection of data that describes the database and the objects in the database are very important for the performance of your queries.

Important information collected by dbms_stats package and used by query optimizer to estimate are:
– Selectivity of predicates
– Cost of each execution plan
– Access method and join method
– CPU and I/O costs

Types of Optimizer Statistics are;
Object statistics
– Table statistics
– Column statistics
– Index statistics
System statistics
– I/O performance and utilization
– CPU performance and utilization

So how statistics can be gathered;
– Automatic statistics gathering; introduced with 10g, GATHER_STATS_JOB
– Manual statistics gathering; DBMS_STATS package or old ANALYZE statement
– Dynamic sampling; introduced with 10g, used to automatically collect statistics when:
* The cost of collecting the statistics is minimal compared to the execution time
* The query is executed many times

DBMS_STATS package is available with Oracle8i and later, better than ANALYZE because;
– it can speed the Stats Collection Process ;
– Tables can be analyzed in parallel,
– You can select STALE(10% changed) tables for ANALYZING, not all of them everytime
– it can copy stats stored in one database to another,
– you can lock the statistics,
– you can manipulate the statistics,
– you can revert back to previous stats,
– you can migrate to system statistics which enable the CBO to use CPU and I/O characteristics

How to verify Statistics collected and valid;


-- Table based
SELECT last_analyzed analyzed, sample_size, monitoring
FROM dba_tables WHERE table_name ='EMPLOYEES';

-- Column based
SELECT column_name, num_distinct,histogram,  num_buckets, density, last_analyzed
FROM dba_tab_col_statistics WHERE table_name  ='EMPLOYEES'
ORDER BY column_name;

-- Index based
SELECT index_name , num_rows , last_analyzed , distinct_keys , leaf_blocks ,
avg_leaf_blocks_per_key, join_index
FROM dba_indexes WHERE table_name = 'EMPLOYEES' ORDER BY index_name;

How to copy statistics with DBMS_STATS

You can copy statistics from a production to a test database to facilitate tuning. For example, to copy a schema’s statistics:

1. Use the DBMS_STATS.CREATE_STAT_TABLE procedure in the production database to create a user-defined statistics table.
2. Use the DBMS_STATS.EXPORT_SCHEMA_STATS procedure in the production database to copy statistics from the data dictionary to the user-defined statistics table from step 1.
3. Use the Export and Import utilities to transfer the statistics to a corresponding user-defined statistics table in the test database.
4. Use the DBMS_STATS.IMPORT_SCHEMA_STATS procedure to import the statistics into the data dictionary in the test database.

The DBMS_STATS package can also be used to back up statistics prior to analyzing objects. The backup can used to:
– Restore old statistics and
– Study changes in data characteristics over time


-- Step 1. Create the table to hold the statistics:
DBMS_STATS.CREATE_STAT_TABLE
(‘HR’ /* schema name */
,’STATS’ /* statistics table name */
,’SAMPLE’ /* tablespace */
);

-- Step 2. Copy the statistics into the table:
DBMS_STATS.EXPORT_TABLE_STATS
(’HR’ /* schema name */
,’EMP’ /* table name */
, NULL /* no partitions */
,’STATS’ /* statistics table name */
,’CRS990601’ /* id for statistics */
, TRUE /* index statistics */
);

-- Step 3. Export the STATS table, and then import it into the second database.

-- Step 4. Copy the statistics into the data dictionary:
DBMS_STATS.IMPORT_TABLE_STATS
(’HR’ /* schema name */
,’EMP’ /* table name */
, NULL /* no partitions */
,’STATS’ /* statistics table name */
,’CRS990601’ /* id for statistics */
, TRUE /* index statistics */
);

How to lock statistics with DBMS_STATS

With Oracle Database 10g, you can also lock statistics on a specified table with the new LOCK_TABLE_STATS procedure of the DBMS_STATS package to prevents automatic statistics gathering on that table.

If a table’s data is so volatile that the statistics become stale between statistics gathering intervals, you may choose to lock it when the table is empty. The optimizer then performs dynamic sampling on the table whenever required to get current statistics. You can also lock statistics on a volatile table at a point when it is fully populated so that the table statistics are more representative of the table population.

You can lock statistics at the schema level using the LOCK_SCHEMA_STATS procedure.
You can query the STATTYPE_LOCKED column in the {USER | ALL | DBA}_TAB_STATISTICS view to determine if the statistics on the table are locked.

You can use the UNLOCK_TABLE_STATS procedure to unlock the statistics on a specified table. When you lock the statistics on a table, all of the dependent statistics are considered locked. This includes table statistics, column statistics, histograms, and dependent index statistics. Dynamic sampling involves an overhead and is repeated for the same objects unless statistics are gathered.

How to migrate to system statistics with DBMS_STATS


-- Step 1. Check for system statistics if they are gathered before:

SELECT pname, pval1 FROM sys.aux_stats$ WHERE sname = 'SYSSTATS_MAIN';

PNAME                               PVAL1
—————————— ———-
CPUSPEED
CPUSPEEDNW                        495,977
IOSEEKTIM                              10
IOTFRSPEED                           4096
MAXTHR
MBRC
MREADTIM
SLAVETHR
SREADTIM

9 rows selected.

-- Step 2. Start gathering system statistics for a period:

exec dbms_stats.gather_system_stats(’start’);

-- During this period load on the database will determine the accuracy of the statistics representation.
-- Sample load script - http://tonguc.oracleturk.org/index.php/2006/12/13/migrating-to-system-statistics/

-- Step 3. Stop gathering system statistics:

exec dbms_stats.gather_system_stats(’stop’);

-- Step 4. Check for new system statistics gathered:

SELECT pname, pval1 FROM sys.aux_stats$ WHERE sname = 'SYSSTATS_MAIN';

PNAME                               PVAL1
—————————— ———-
CPUSPEED                              496
CPUSPEEDNW                        495,977
IOSEEKTIM                              10
IOTFRSPEED                           4096
MAXTHR                            7029760
MBRC                                   88
MREADTIM                           26,065
SLAVETHR
SREADTIM                              4,8

9 rows selected.

4- Using Recovery Manager(RMAN) for Backup and Recovery needs

RMAN is a utility that backs up, restores, and recovers Oracle databases. You can use it with or without the central information repository called a recovery catalog. If you do not use a recovery catalog, RMAN uses the database’s control file to store information necessary for backup and recovery operations. You can use RMAN in conjunction with a media manager to back up files to tertiary storage.

How to start RMAN without a Catalog


C:\>rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Sal Oca 9 15:37:35 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: XE (DBID=2488207843)

RMAN>

How to configure Backup Parameters


CONFIGURE BACKUP OPTIMIZATION ON;                   -- do not back up unchanged data files
CONFIGURE MAXSETSIZE TO 2 G;                        -- make filesize <= 2GB
CONFIGURE ENCRYPTION FOR DATABASE ON;               -- encrypt backups
CONFIGURE ENCRYPTION ALGORITHM 'AES256';            -- use 256 bit encryption
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;        # default
/* Configure the number of server processes (channels) that write backups to DISK. */
CONFIGURE DEVICE TYPE DISK PARALLELISM 2;
/* Set the retention policy to a recovery window of 30 days. This ensures that RMAN retains all backups needed to recover the database to any point in time in the last 30 days. You can use the DELETE OBSOLETE command to delete backups that are no longer required by the retention policy. To exclude a backup from consideration by the policy, you can use KEEP option with the BACKUP command. */
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 30 DAYS;
/* Enable automatic controlfile backup after each database or archivelog backup */
CONFIGURE CONTROLFILE AUTOBACKUP ON;

RMAN> show all;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 30 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO 2 G;
CONFIGURE ENCRYPTION FOR DATABASE ON;
CONFIGURE ENCRYPTION ALGORITHM 'AES256';
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\ORACLEXE\APP\ORACLE\PRODUCT\10.2.0\SERVER\DATABASE\SNCFXE.ORA'; # default

Run Full Backup


RMAN> report need backup;

RMAN retention policy will be applied to the command
RMAN retention policy is set to recovery window of 30 days
Report of files that must be backed up to satisfy 30 days recovery window
File Days  Name
---- ----- -----------------------------------------------------
1    336   D:\ORACLEXE\ORADATA\XE\SYSTEM.DBF
2    336   D:\ORACLEXE\ORADATA\XE\UNDO.DBF
3    336   D:\ORACLEXE\ORADATA\XE\SYSAUX.DBF
4    336   D:\ORACLEXE\ORADATA\XE\USERS.DBF

# mkdir c:\backup_xe1
# mkdir h:\backup_xe2

-- RMAN-06770: backup encryption requires Enterprise Edition
CONFIGURE ENCRYPTION FOR DATABASE OFF ;
-- RMAN-06908: WARNING: operation will not run in parallel on the allocated channels
-- RMAN-06909: WARNING: parallelism require Enterprise Edition
RUN
{       ALLOCATE CHANNEL d1 DEVICE TYPE DISK FORMAT 'c:\backup_xe1\%U';
ALLOCATE CHANNEL d2 DEVICE TYPE DISK FORMAT 'h:\backup_xe2\%U';
# AS COPY is default when backing up to disk
BACKUP DATABASE PLUS ARCHIVELOG; }

RMAN> report need backup;

RMAN retention policy will be applied to the command
RMAN retention policy is set to recovery window of 30 days
Report of files that must be backed up to satisfy 30 days recovery window
File Days  Name
---- ----- -----------------------------------------------------

RMAN> list backup summary;

List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
1       B  A  A DISK        09/01/2007      1       1       NO         TAG20070109T154753
2       B  A  A DISK        09/01/2007      1       1       NO         TAG20070109T154753
3       B  F  A DISK        09/01/2007      1       1       NO         TAG20070109T154809
4       B  A  A DISK        09/01/2007      1       1       NO         TAG20070109T155150
5       B  F  A DISK        09/01/2007      1       1       NO         TAG20070109T155157

RMAN> validate backupset 1 ;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=23 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=36 devtype=DISK
channel ORA_DISK_1: starting validation of archive log backupset
channel ORA_DISK_1: reading from backup piece C:\BACKUP_XE12I7351A_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\BACKUP_XE12I7351A_1_1 tag=TAG20070109T154753
channel ORA_DISK_1: validation complete, elapsed time: 00:00:05

RMAN> BACKUP VALIDATE DATABASE;

Starting backup at 09/01/2007
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00004 name=D:\ORACLEXE\ORADATA\XE\USERS.DBF
channel ORA_DISK_2: starting full datafile backupset
channel ORA_DISK_2: specifying datafile(s) in backupset
input datafile fno=00003 name=D:\ORACLEXE\ORADATA\XE\SYSAUX.DBF
input datafile fno=00002 name=D:\ORACLEXE\ORADATA\XE\UNDO.DBF
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:24
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=D:\ORACLEXE\ORADATA\XE\SYSTEM.DBF
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:41
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:36
Finished backup at 09/01/2007

Creating and Updating Duplicate Databases with RMAN


# Duplicate database to TARGET’s current state.
DUPLICATE TARGET DATABASE TO CLONE;

# Duplicate database to TARGET’s state 4 days ago.
DUPLICATE TARGET DATABASE TO CLONE UNTIL TIME ‘SYSDATE-4';

Block Recovery Solution Example with RMAN


SQL> select * from emp;
select * from emp
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 24165)
ORA-01110: data file 4: ‘D:\ORACLE\ORADATA\USERS01.DBF’

SQL> select header_file,header_block from dba_segments where segment_name=’EMP’;

HEADER_FILE HEADER_BLOCK
———– ————
4 24163

SQL> select * from v$backup_corruption;

RECID STAMP SET_STAMP SET_COUNT PIECE# FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# MAR CO
———- ———- ———- ———- ———- ———- ———- ———- ————————–
1 550688483 550688389 46 1 4 24165 1 0 YES CORRUPT

The above output confirms that block 24165 in file 4 is indeed corrupt. We can recover the same using the following command.


RMAN> run {blockrecover datafile 4 block 24165;}

Starting blockrecover at 25-OCT-06
using channel ORA_DISK_1

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00004
channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=D:\ORACLE\FLASH_RECOVERY_AREA\BACKUPSET\2005_02_19\O1_MF_NNNDF_TAG20050219T164615_11FNO9BQ_.BKP tag=TAG20050219T164615
channel ORA_DISK_1: block restore complete

starting media recovery
media recovery complete

Finished blockrecover at 25-OCT-06

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
7369 SMITH CLERK 7902 17-DEC-80 800 20
…
14 rows selected.

Continue reading with Part 2

Testing Information : the scripts mentioned are tested on Oracle Database 10g Express Edition Release 10.2.0.1.0

References Used :
Oracle® Database Administrator’s Guide 10g Release 2 (10.2) Chapter 7 Managing Archived Redo Logs Choosing Between NOARCHIVELOG and ARCHIVELOG Mode
Archive Log Mode Blog post by Mr.Thomas Kyte
BASICS OF ORACLE BACKUP AND RECOVERY
Understanding System Statistics by Jonathan Lewis
“CPU costing, mreadtim
http://www.psoug.org/reference/dbms_stats.html
Oracle® Database Performance Tuning Guide 10g Release 2 (10.2) Chapter 14 Managing Optimizer Statistics Gathering Statistics with DBMS_STATS Procedures
http://www.psoug.org/reference/rman_demos.html
http://www.psoug.org/reference/rman.html
Oracle® Database Backup and Recovery Advanced User’s Guide 10g Release 2 (10.2) Creating and Updating Duplicate Databases with RMAN
Backup Validation with RMAN
When to Use Block Media Recovery

Getting Rows N through M of a Result Set From Database

Filed under: Oracle Performance — H.Tonguç Yılmaz @ 6:24 am

You would like to fetch data and sort it based on some field. As this query results into approx 100 records, you would like to cut the result set into 4, each of 25 records and you would like to give sequence number to each record.

SELECT *
FROM (SELECT A.*, ROWNUM rnum
FROM (your query including the order by) A
WHERE ROWNUM <= MAX_ROWS )
WHERE rnum >= MIN_ROWS;

A simple test done on SQL*Plus is as follows;

Code listing 50 : Getting Rows N through M of a Result Set From Database Example

Here is also a brief explaination for SQL*Plus autotrace statistics used above;

db block gets : Number of logical I/Os for current gets(from undo segments)
consistent gets : Reads of buffer cache blocks
physical reads : Number of blocks read from disk
redo size : Amount of redo generated (for DML statements)
sorts (memory) : Number of sorts performed in memory
sorts (disk) : Number of sorts performed using temporary disk storage

Testing Information : the scripts mentioned are tested on Oracle Database 10g Express Edition Release 10.2.0.1.0

Refences Used :
“getting rows N through M of a result set” thread on Asktom

Create a free website or blog at WordPress.com.