Tuesday, January 31, 2012

Not letting an ORACLE job start past a defined time – schedule_limit (Implementation & Bugs)


In heavily loaded system you must have seen that the jobs are not always started at their scheduled time.


REQ_START_DATE.DBA_SCHEDULER_JOB_RIN_DETAILS -- SHOWS WHEN THE JOB WAS SCHEDULED TO RUN
ACTUAL_START_DATE.DBA_SCHEDULER_JOB_RIN_DETAILS -- SHOWD WHEN THE JOB REALLY STARTED

By default the job can start any time after its scheduled time, as soon as there are resources available to run it.
Now what if I don’t want a job to start if could not start with in a defined time (specified interval) after the scheduled time.

What is schedule_limit?

With the scheduler, you can answer the question of how long past its time you're willing to let a job start. You do this by setting the job's schedule_limit attribute value (It can be a value of 1 minute to 99 days.). The following specifies that my TEST.NEW_JOB job must begin within 10 minutes of its scheduled start time:

sys.dbms_scheduler.set_attribute(
name => TEST.NEW_JOB ',
attribute => 'schedule_limit',
value => 10);

If ever this job is delayed for more than 10 minutes past its start time, that run of the job will be skipped. The default schedule_limit attribute is null, which imposes no limit. That is it will start ASAP.
An entry in the job log will be made to reflect the skipped run.

select operation,additional_info from dba_scheduler_job_log


OPERATION     ADDITIONAL_INFO
TIMED OUT       REASON="Recurring job rescheduled after schedule limit exceeded"

********************************************************************************

Please be aware of Bug 6071651, Delays executing a job if execution time exceeds SCHEDULE_LIMIT [ID 6071651.8]

Excerpts of the Metalink[ID 6071651.8] note on Bug 6071651:-

Description:

If a job has an execution time that is greater than both its repeat interval and
its schedule limit then there may be excessive delays in scheduling it.

Affects:

Product (Component)
Oracle Server (Rdbms)
Range of versions believed to be affected
Versions < 11.2
Versions confirmed as being affected
  • 11.1.0.6
Platforms affected
Generic (all / most platforms affected)

Fixed:

This issue is fixed in
  • 10.2.0.5 (Server Patch Set)
  • 11.1.0.7 (Server Patch Set)
  • 11.2 (Future Release)

 

 

















Additionally the schedule_limit set to one minute will not prevent the jobs due to run from starting when database starts up many minutes past the one minute due (schedule_limit)

********************************************************************************


Let’s forget the bugs and try to implement the SCHEDULE_LIMIT


exec dbms_lock.sleep (5);

We will use the sleep procedure to create the program, and pass the parameter (seconds to sleep) as an argument, to make it dynamic.
Thus making it a lot easy to play around.


BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM(
program_name => 'TEST_PGM_V2',
program_action => 'dbms_lock.sleep',    
program_type => 'STORED_PROCEDURE',
number_of_arguments => 1,
enabled => FALSE);
END;
/

BEGIN
 DBMS_SCHEDULER.define_program_argument (
    program_name      => 'TEST_PGM_V2',
    argument_name     => 'SEC',
    argument_position =>  1,
    argument_type     => 'NUMBER',
    default_value     => '180'); -- in seconds



DBMS_SCHEDULER.enable (name => 'TEST_PGM_V2');
END;
/



BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE(
schedule_name => 'TEST_SCH_V2',
start_date =>  Trunc(sysdate),
end_date => NULL,
repeat_interval => 'FREQ=DAILY;BYHOUR=1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,0; BYMINUTE=5,10,15,20,25,30,35,40,45,50,55,0;',
comments => 'Every hour');
END;
/

BEGIN
dbms_scheduler.create_job(
job_name => 'TEST__JOB_V2',
job_class => 'default_job_class',
program_name => 'TEST_PGM_V2',
schedule_name => 'TEST_SCH_V2',
enabled => FALSE);
END;
/

--- in case if we have to alter the attribute of the scheduler -- to test with different intervals

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE (
   name           =>   'TEST_SCH_V2',
   attribute      =>   'repeat_interval',
   value          =>   'FREQ=DAILY;BYHOUR=1,2,3,4,5,6,7,8,9,10,11,12; BYMINUTE=5,10,15,20,25,30,35,40,45,50,55,0;');
END;
/

----

-- to diable enable job , if required ,to play around.

begin
DBMS_SCHEDULER.disable('TEST__JOB_V2');
end;
/
/*---if we try to stop an "running" job,we will get the below error ---*/
ORA-27478: job "USER.TEST__JOB_V2" is running
ORA-06512: at "SYS.DBMS_ISCHED", line 2805
ORA-06512: at "SYS.DBMS_SCHEDULER", line 1753
ORA-06512: at line 2


select * from dba_scheduler_running_jobs; -- to view the RUNNING JOBS
select * from v$scheduler_running_jobs; -- to view the RUNNING JOBS

--we have to stop the job and then disable-
begin
DBMS_SCHEDULER.STOP_JOB('TEST__JOB_V2');
end;
/


begin
DBMS_SCHEDULER.enable('TEST__JOB_V2');
end;
/


---- TO SET THE SCHEDULE_LIMIT
begin
dbms_scheduler.set_attribute(
name =>'TEST__JOB_V2',
attribute => 'schedule_limit',
value => '10');
end;
/
/*---the above format will result in ORA-01867 ---*/
ORA-01867: the interval is invalid
ORA-06512: at "SYS.DBMS_SCHEDULER", line 1870
ORA-06512: at line 2

-- use any of the below two methods to set the schedule_limit

begin
dbms_scheduler.set_attribute(
name =>'TEST__JOB_V2',
attribute => 'schedule_limit',
value => '+000 00:10:00');
end;
/

begin
dbms_scheduler.set_attribute(
name =>'TEST__JOB_V2',
attribute => 'schedule_limit',
value => NUMTODSINTERVAL(5,'MINUTE'));
end;
/

--Query to confirm
select schedule_name,schedule_limit from dba_scheduler_jobs;

--Query to monitor
select a.owner,a.job_name,a.status,a.req_start_date,a.actual_start_date,a.run_duration,a.log_id,
a.log_date,b.log_date,b.operation,b.status,b.additional_info
from dba_scheduler_job_run_details a, dba_scheduler_job_log b
where  b.job_name='NEW_JOB'
and a.log_id(+) =b.log_id
order by b.log_id desc


Wednesday, January 18, 2012

Oracle Scripts & Tricks

--Script to monitor the Historic growth of the tablespaces --

SELECT TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY') days
, ts.tsname
, max(round((tsu.tablespace_size* dt.block_size )/(1024*1024),2) ) cur_size_MB
, max(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) usedsize_MB
FROM DBA_HIST_TBSPC_SPACE_USAGE tsu
, DBA_HIST_TABLESPACE_STAT ts
, DBA_HIST_SNAPSHOT sp
, DBA_TABLESPACES dt
WHERE tsu.tablespace_id= ts.ts#
AND tsu.snap_id = sp.snap_id
AND ts.tsname = dt.tablespace_name
AND ts.tsname  NOT IN ('SYSTEM','SYSAUX')
GROUP BY TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY'), ts.tsname
ORDER BY ts.tsname, days;


 --Script to find the LR(Logical reads) & DR(Disk Reads) of an SQL stmt --
select
sql_id "Sqlid",
--sql_fulltext,
--last_active_time "LastActive",
--first_load_time "FirstLoaded",
--parsing_schema_name "ParsingUser",
executions "Executions",
--round(fetches/executions,2) "FetchesPerExec",
--round(fetches,2) "TotalFetches",
--round(rows_processed /executions,2) "RowsPerExec",
--rows_processed "TotalRowsProcessed",
round(buffer_gets/executions,2) "LRsPerExec",
--round(buffer_gets,2) "TotalLRs",
round(disk_reads/executions,2) "DRsPerExec",
--round(disk_reads,2) "TotalDRs", 
round((elapsed_time/1000000)/executions,3) "ElapsedPerExec",
round((elapsed_time/1000000),2) "TotalElapsedc",
cpu_time
--sql_fulltext "Sqltext"
from v$sql
where
parsing_schema_name like 'CMPS%'
and sql_fulltext NOT LIKE '%v$sql%'
and sql_fulltext NOT LIKE '%QRTZ%'
and sql_fulltext NOT LIKE '%session%'
and lower(sql_fulltext)  LIKE '%<enter the sql text here>%'
and executions > 0
--and TO_date(last_active_time, 'DD-MON-YYYY') = TO_date(sysdate, 'DD-MON-YYYY')
and last_active_time > to_date('24-11-2011 08:30:00', 'DD-MM-YYYY HH24:MI:SS')
--and (sql_id in ( 'xxxxxxxxx,'xxxxxxxxxx ) )
--and upper(sql_text) like '%con.cust_stat_cd = %'
order by last_active_time desc
--order by sql_id

Thursday, February 17, 2011

Restoring/Clonning a Database into a new Host using RMAN



To restore a database into a new host using RMAN there are two methods

1. RESTORE
2. DUPLICATE

--A few points of comparison

The main problem of DUPLICATE command is the RMAN backups from the source db have to be copied to the same file location (path) in the destination host.

Duplicate command changes the DBBID, so that database also can be registered in the same RMAN Catalog as of source database. And also we can change the Database Name.
Same can be achieved using RESTORE option also by using NID after restoring the full db.

DUPLICATE command is executed in the nomount stage, it restores the controlfile also, so we can use the “DB_FILE_NAME_CONVERT” & “LOG_FILE_NAME_CONVERT” parameters to change the file names while restoring. i.e. the controlfile is restored and it is aware of the file name change done in the pfile.
But , when using RESTORE command to restore a database into new host even if we use the “DB_FILE_NAME_CONVERT” & “LOG_FILE_NAME_CONVERT” parameters to change the file names it will not take effect as we do the restore step by step ,first we nomount ,the restore the controlfile using the backup from the source db , so here the controlfiles knows the datafiles and logfiles as it were on the source db, so to change the location we have to either use alter database rename file command in sqlplus or use set newname for datafile 1 to ..‘ command to rename the files.

============================
**********RESTORE************
============================

Note: Take special care not to overwrite the target databases information recorded in the recovery catalog while cloning, discussed later in this doc
1. Copy parameter file to new host (make the location changes of parameters if required).
2. Restore the backup from tape to the new host.
3. Use catalog command to update the RMAN repository with the new file names (location different)
  Use change...uncatalog to remove other old backups from the repository
4. Determine the SCN for Incomplete Recovery (UNTIL TIME ,UNTIL SCN or UNTIL TIME can be used)
 Perform incomplete recovery up to the lowest SCN of the most recently archived log in each thread and then open with the RESETLOGS option
 SELECT MIN(maxnc) FROM
 (SELECT MAX(a.NEXT_CHANGE#) maxnc
  FROM V$ARCHIVED_LOG a, V$THREAD t
 WHERE a.THREAD# = t.THREAD#
 AND a.ARCHIVED='YES'
 AND t.ENABLED='DISABLED'
GROUP BY a.THREAD#);

5. From the new host connect to the target using RMAN
set oracle_sid=<sid_on_host>  -- have to be set if using RESTORE …..FROM AUTOBACKUP
rman target / nocatalog ---connect to rman without connecting to recovery catalog
(Note : even if using recovery catalog don connect to it while restoring db into new host using RECOVER)
6. set dbid=<DBID>
   startup nomount  -- rman will fail as the parameter file cannot be found
(You can find the DBID in -
v$database in target db ,RC_DATABASE in catalog
the rman output (cmd line and v$RMAN_STATUS)
the filename of the controlfile autobackup)

----startup fails-----
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file
'\<location>\initSID.ora'
Trying to start the Oracle instance without parameter files ...
Oracle instance started


7.
RUN
{
  ALLOCATE CHANNEL c1 DEVICE TYPE sbt PARMS='...';
  RESTORE SPFILE TO PFILE '<Location on the new host\initSID.ora>' FROM AUTOBACKUP;
  SHUTDOWN ABORT;
}

Or RESTORE SPFILE TO PFILE FROM ‘<file path + name>’
---then change the location specific parameters
Those ending with _DEST & _PATH to reflect new directory structure

8. Restart the instance using the pfile
STARTUP FORCE NOMOUNT PFILE='<Location on the new host\initSID.ora>'

9.
b'coz you have edited the location of the control file(CONTROL_FILES) in the pfile it will
get restored into the new location
RUN
{
  ALLOCATE CHANNEL c1 DEVICE TYPE sbt PARMS='...';
  RESTORE CONTROLFILE FROM AUTOBACKUP;
  ALTER DATABASE MOUNT;
}
--restore controlfile from autobackup will work only of the backup is in the location specified in the rman configuration (RMAN CONFIGURE)
Otherwise we can use
RESTORE CONTROLFILE  FROM 'N:\DUP\rman\C-190696285-20110219-01'

Starting restore at 19-FEB-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
channel ORA_DISK_1: restoring control file
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 02/19/2011 17:35:49
ORA-19502: write error on file "N:\DUP\CTL\CONTROL02.CTL", blockno 256
(blocksize=16384)
ORA-27072: File I/O error
OSD-04008: WriteFile() failure, unable to write to file
O/S-Error: (OS 2) The system cannot find the file specified.
ORA-19600: input file is control file  (N:\DUP\CTL\CONTROL01.CTL)
ORA-19601: output file is control file  (N:\DUP\CTL\CONTROL02.CTL)
RMAN> restore controlfile from 'N:\DUP\rman\C-190696285-20110219-01';
Starting restore at 19-FEB-11
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output filename=N:\DUP\CTL\CONTROL01.CTL
output filename=N:\DUP\CTL\CONTROL02.CTL
output filename=N:\DUP\CTL\CONTROL03.CTL
Finished restore at 19-FEB-11

10. Connect to sqlplus
SQL> COLUMN NAME FORMAT a60
SQL> SPOOL LOG 'db_filenames.out'
SQL> SELECT FILE# AS "File/Grp#", NAME FROM V$DATAFILE
     UNION
     SELECT GROUP#,MEMBER FROM V$LOGFILE;
SQL> SPOOL OFF
SQL EXIT


11.
RMAN> SQL 'ALTER DATABASE MOUNT';

12. After copying the RMAN backup into the new host into new location we will have to register the backup file name  and remove the other backup file information
--archives need not be copied to the same location instead can be copied to the location mentioned in the parameter file of the new instance to which we are about to clone.

RMAN> CATALOG START WITH 'N:\DUP\rman\' NOPROMPT;

searching for all files that match the pattern N:\DUP\rman\
List of Files Unknown to the Database
=====================================
File Name: N:\DUP\RMAN\1AM52L9Q_1_1
File Name: N:\DUP\RMAN\1BM52LAT_1_1
File Name: N:\DUP\RMAN\C-190696285-20110219-00
File Name: N:\DUP\RMAN\C-190696285-20110219-01
File Name: N:\DUP\RMAN\LOG_T743527910_S45_P1
File Name: N:\DUP\RMAN\LOG_T743527915_S46_P1
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: N:\DUP\RMAN\1AM52L9Q_1_1
File Name: N:\DUP\RMAN\1BM52LAT_1_1
File Name: N:\DUP\RMAN\C-190696285-20110219-00
File Name: N:\DUP\RMAN\C-190696285-20110219-01
File Name: N:\DUP\RMAN\LOG_T743527910_S45_P1
File Name: N:\DUP\RMAN\LOG_T743527915_S46_P1

As the other backup files will not be in the target host we can crosscheck and delete the expired backups.
  
RMAN > CROSSCHECK BACKUP
RMAN > CROSSCHECK ARCHIVELOG ALL
RMAN > DELETE EXPIRED BACKUP
RMAN > DELETE EXPIRED ARCHIVELOG ALL


RUN
{
ALLOCATE CHANNEL C DEVICE TYPE DISK;
SET NEWNAME FOR DATAFILE 1 TO 'N:\DUP\DFS\SYSTEM01.DBF';
SET NEWNAME FOR DATAFILE 2 TO 'N:\DUP\DFS\UNDOTBS01.DBF';
SET NEWNAME FOR DATAFILE 3 TO 'N:\DUP\DFS\SYSAUX01.DBF';
SET NEWNAME FOR DATAFILE 4 TO 'N:\DUP\DFS\USERS01.DBF';
SET NEWNAME FOR DATAFILE 5 TO 'N:\DUP\DFS\EXAMPLE01.DBF';
SET NEWNAME FOR DATAFILE 6 TO 'N:\DUP\DFS\NEW001.DBF';
SET NEWNAME FOR DATAFILE 7 TO 'N:\DUP\DFS\TBS01.DBF';
SQL "ALTER DATABASE RENAME FILE ''F:\ORACLE\PRODUCT\10.2.0\ORADATA\NEWDB\MYNEWDB\REDO01.LOG'' TO ''N:\DUP\DFS\REDO01.LOG''";
SQL "ALTER DATABASE RENAME FILE ''F:\ORACLE\PRODUCT\10.2.0\ORADATA\NEWDB\MYNEWDB\REDO02.LOG'' TO ''N:\DUP\DFS\REDO02.LOG''";
SQL "ALTER DATABASE RENAME FILE ''F:\ORACLE\PRODUCT\10.2.0\ORADATA\NEWDB\MYNEWDB\REDO03.LOG'' TO ''N:\DUP\DFS\REDO03.LOG''";
#SET UNTIL SCN;
SET UNTIL SEQUENCE 14;
#UNTIL SEQUENCE 14 MEANS IT WILL RESTORE TILL 13
#SET UNTIL TIME "to_date((to_char(SYSDATE,'dd.mm.yyyy')||' 08:00:00'),'dd.mm.yyyy hh24:mi:ss')";
RESTORE DATABASE;
SWITCH DATAFILE ALL;
RECOVER DATABASE;
}
 EXIT

12. ALTER DATABASE OPEN RESTLOGS.
--- if the restored to a heigher version than the original db
we will have to
ALTER DATABASE OPEN UPGRADE

---AFTER TESTING TO REMOVE THE TEST DATABASE
RMAN > STARTUP FORCE NOMOUNT PFILE='<LOCATION>'
RMAN > DROP DATABASE



============================
**********
DUPLICATE**********
============================


Duplicate a Database Using RMAN
A nice feature of RMAN is the ability to duplicate, or clone, a database from a previous backup.
It is possible to create a duplicate database on a remote server with the same file structure, a remote server will a different file structure or the local server with a different file structure. In this article I'll demonstrate the last method, how to duplicate a database on the local server with a different file structure. This can prove useful when you want to recover selected objects from a backup, rather than roll back a whole database or tablespace.
--but if cloning to a different HOST the backup have to copied to the new host into the same file path
--archives need not be copied to the same location instead can be copied to the location mentioned in the pfile of the new instance to which we are about to clone.

1. First we must create a password file for the duplicate instance:
    orapwd file=/u01/app/oracle/product/9.2.0.1.0/dbs/orapwDUP password=password entries=10
2.
Next add the appropriate entries into the tnsnames.ora and listener.ora files in the $ORACLE_HOME/network/admin directory. Remember to load the new configuration into the listener:
    # Added to the listener.ora SID_LIST
    (SID_DESC =
      (ORACLE_HOME = /u01/app/oracle/product/9.2.0.1.0)
      (SID_NAME = DUP)
    )
    # Added to the tnsnames.ora
    DUP =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = server1.tshcomputing.com)(PORT = 1521))
        )
        (CONNECT_DATA =
          (ORACLE_SID = DUP)
        )
      )
    # Reload listener
    lsnrctl reload
3.
Next create an init.ora file for the duplicate database. Since we are duplicating the database onto the same server as the original we must convert the file names so there is no conflict:

    # Minimum Requirement.
    DB_NAME=DUP
    CONTROL_FILES=(/u02/oradata/DUP/control01.ctl,
                   /u02/oradata/DUP/control02.ctl,
                   /u02/oradata/DUP/control03.ctl)
   # Convert file names to allow for different directory structure.
    DB_FILE_NAME_CONVERT=(/u02/oradata/TSH1/,/u02/oradata/DUP/)    LOG_FILE_NAME_CONVERT=(/u01/oradata/TSH1/,/u01/oradata/DUP/)
    # make sure block_size and compatible parameters
    # match if you are not using the default.
    DB_BLOCK_SIZE=8192
    COMPATIBLE=9.2.0.0.0

Instead we can also restore from the spfie backup which is more simpler, and then change the DB_NAME and file name parameters

Next we connect to the duplicate instance:
    ORACLE_SID=DUP; export ORACLE_SID
    sqlplus /nolog
    conn / as sysdba

4.
Next we create an SPFILE based on the init.ora:
    CREATE SPFILE FROM PFILE='/u01/app/oracle/admin/DUP/pfile/init.ora';
Next we start the database in NOMOUNT mode:
    STARTUP FORCE NOMOUNT;
5.
With the duplicate database started we can now connect to it from RMAN. For the duplication to work we must connect to the original database (TARGET), the recovery catalog (CATALOG) and our duplicate database (AUXILIARY):

    ORACLE_SID=DUP; export ORACLE_SID
    RMAN TARGET sys/password@connection CATALOG rman/rman@ connection AUXILIARY /

We can then duplicate the database using one of the following commands:
    # Duplicate database to TARGET's current state.
    DUPLICATE TARGET DATABASE TO DUP;
    # Duplicate database to TARGET's state 4 days ago.
    DUPLICATE TARGET DATABASE TO DUP UNTIL TIME 'SYSDATE-4';
The time it takes to complete varies depending on the size of the database and the specification of the server. Once the process is finished RMAN produces a completion message and you have your duplicate instance.

One can use any of the two methords according to the circumstances and specific requirements.