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
Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog 
Cool article! On 10G I have found it best to stop the default daily stats gathering program and run my own version. The default one seems to be too ‘auto’!