Oracle incremental update using image copy

Explains the RMAN Image copy feature and incrementally updated backups New in Oracle 10G.

• Rman is configured with Recovery Catalog.
• Recovery Catalog Database Name-Orcl
• Database Name test is taken for demo
• Database Test is Running in no archive log mode
• Configured Backup location is 'D:\ORACLE\RMAN\ORA10G'
• During the first and second run of the backup scripts I made some changes in the Database. Also during the second and the third run I made some changes in the Database.

Expanded Image Copying Features: A standard RMAN backup set contains one or more backup pieces, and each of these pieces consists of the data blocks for a particular datafile stored in a special compressed format. When a datafile needs to be restored, therefore, the entire datafile essentially needs to be recreated from the blocks present in the backup piece.

An image copy of a datafile, on the other hand, is much faster to restore because the physical structure of the datafile already exists. Oracle 10g now permit image copies to be created at the database, tablespace, or datafile level through the new RMAN directive BACKUP AS COPY. For example, here is a command script to create image copies for all datafiles in the entire database:
Backup Procedure
Step 1 : Create a image copy of full database
RUN {
ALLOCATE CHANNEL dbkp1 DEVICE TYPE DISK FORMAT 'D:\oracle\rman\ora10G\U%';
BACKUP AS COPY DATABASE; }

Step 2 : Backup incrementally and restore to the image copy
RUN {
# Roll forward any available changes to image copy files
# From the previous set of incremental Level 1 backups
RECOVER COPY OF DATABASE WITH TAG 'cool';
# Create incremental level 1 backup of all datafiles in the database
# For roll-forward application against image copies
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'cool' DATABASE; }

Restore/recovery procedure
In case of a disaster you can now tell rman to just change the locations of the datafiles in the controlfile to the image copies by issuing the following:
RMAN> alter database mount;
RMAN> switch database to copy;

If you want to restore the database
RMAN > restore database;
RMAN > recover database;

 

DBMS SCHEDULER

DBMS SCHEDULER is a more sophisticated job scheduler introduced in Oracle 10g. The older job scheduler, DBMS_JOB, is still available, is easier to use in simple cases
Make sure the OracleJobScheduler Service is started.
Create a job
BEGIN
   DBMS_SCHEDULER.CREATE_JOB (
   job_name => 'myjob',
   job_type => 'EXECUTABLE',
   job_action => 'd:\oracle\script\vng.bat',
   repeat_interval => 'FREQ=MINUTELY',
   enabled => TRUE );
END;
Remove a job
EXEC DBMS_SCHEDULER.DROP_JOB('myjob');
Change job attributes
Examples:
EXEC DBMS_SCHEDULER.SET_ATTRIBUTE('WEEKNIGHT_WINDOW', 'duration', '+000 06:00:00');
BEGIN
   DBMS_SCHEDULER.SET_ATTRIBUTE ('WEEKNIGHT_WINDOW',    'repeat_interval', 'freq=daily;byday=MON, TUE, WED, THU, FRI;byhour=0;byminute=0;bysecond=0');END;/
Enable
/
Disable a job
BEGIN
   DBMS_SCHEDULER.ENABLE('myjob');
END;
BEGIN
   DBMS_SCHEDULER.DISABLE('myjob');
END;
Monitoring jobs
SELECT * FROM dba_scheduler_jobs WHERE job_name = 'myjob';
SELECT * FROM dba_scheduler_job_log WHERE job_name = 'myjob';
Use user_scheduler_jobs and user_scheduler_job_log to only see jobs that belong to your user (current schema).

Oracle image copy

With Oracle 10g R2 we can recover datafile copies like we recover the real datafiles.This gives us the oportunity to recover the entire database without having to restore it from backup first.Which of course saves very valuable time in case of a disaster.

Backup Procedure
Step 1 : Create a image copy of full database
RUN {
ALLOCATE CHANNEL dbkp1 DEVICE TYPE DISK FORMAT 'D:\oracle\rman\ora10G\U%';
BACKUP AS COPY DATABASE; }

Step 2 : Backup incrementally and restore to the image copy
RUN {
# Roll forward any available changes to image copy files
# From the previous set of incremental Level 1 backups
RECOVER COPY OF DATABASE WITH TAG 'cool';
# Create incremental level 1 backup of all datafiles in the database
# For roll-forward application against image copies
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'cool' DATABASE; }

Restore/recovery procedure
In case of a disaster you can now tell rman to just change the locations of the datafiles in the controlfile to the image copies by issuing the following:
RMAN> alter database mount;
RMAN>switch database to copy;

If you want to restore the database
RMAN > restore database;
RMAN > recover database;

Investigating high redo archive generation

If you have ASH, you can use it to find the sessions and queries that waited the most for “log file sync” event. I found that this has some correlation with the worse redo generators.view sourceprint?

1. find the sessions and queries causing most redo and when it happened

select SESSION_ID,user_id,sql_id,round(sample_time,'hh'),count(*) from V$ACTIVE_SESSION_HISTORY where event like 'log file sync' AND SESSION_ID=506 group by SESSION_ID,user_id,sql_id,round(sample_time,'hh') order by count(*) desc

 

1. you can look the the SQL itself by: 

select * from DBA_HIST_SQLTEXT where sql_id='dwbbdanhf7p4a' Another ways from Oracle metalink is by checking block_changes SELECT s.sid, s.serial#, s.username, s.program, i.block_changes FROM v$session s, v$sess_io i WHERE s.sid = i.sid ORDER BY 5 , 1, 2, 3, 4;

you can also check using this script if you are using 10g and above

SELECT dhso.object_name, sum(db_block_changes_delta) FROM dba_hist_seg_stat dhss, dba_hist_seg_stat_obj dhso, dba_hist_snapshot dhs WHERE dhs.snap_id = dhss.snap_id AND dhs.instance_number = dhss.instance_number AND dhss.obj# = dhso.obj# AND dhss.dataobj# = dhso.dataobj# AND begin_interval_time BETWEEN to_date(’2012_01_19 18',’YYYY_MM_DD HH24') AND to_date(’2012_01_19 19',’YYYY_MM_DD HH24') GROUP BY dhso.object_name order by sum(db_block_changes_delta) desc SELECT distinct dbms_lob.substr(sql_text,4000,1) FROM dba_hist_sqlstat dhss, dba_hist_snapshot dhs, dba_hist_sqltext dhst WHERE upper(dhst.sql_text) LIKE ‘%INT_UPLOAD_STATUS%’ AND dhss.snap_id=dhs.snap_id AND dhss.instance_Number=dhs.instance_number AND dhss.sql_id = dhst.sql_id and rownum<2;