Batch file running on command prompt but not in Microsoft window scheduler

Batch file running on command prompt but not in Microsoft window scheduler

Microsoft Scheduler not run the Scheduled task .bat file but working fine manually run.

I am created a job for rman backup scheduled and created bat file to execute it in Microsoft windows Scheduler. On running the .bat file manually it’s running successfully.
But when I scheduled it with windows scheduler its not running actually finished in 1 seconds as shown in windows scheduler history.

Followings all the steps I performed to solve my problem. I would guess that maybe some of these face same problem.

Solution:

1) Open the Task Scheduler of Microsoft Windows.
2) On General Tab, make sure the task is set to “configure for Windows Vista or Windows 2008” on the first page of the task properties.
3) On Action Tab, Open the Task Properties by click on “edit” Button at bottom, need to set the “start in” field with the folder location where the batch file located.
In the “Edit Action” Window there is a field for “start in (optional)” that you set to the path to the batch file.
Example:
D:\scripts\rman.bat is the my file for rman backup , then set “Start in (optional)” with D:\scripts value.
4) Make sure that the task is running as an account that has explicit “Full access” permissions to all these things: The .bat file itself, the folder containing the .bat file, and the target files/folders that are affected by the .bat script. Inherited permissions didn’t seem to work for me.
5) Make sure that the account running the task is a member of the local “administrators” group for this machine.
5) Make sure that the task is set to “run whether logged on or not”
6) The Task should run successfully with expected output when you right-click on the task and select “run”
If it does that then it will run successfully when you are logged off.

Advertisements

Enabling Automatic Degree of Parallelism (DOP) in Oracle

Enabling Automatic Degree of Parallelism (DOP) in Oracle

Init.ora file parameter PARALLEL_DEGREE_POLICY to AUTO allow Oracle to find out SQL Statement run in parallel or Single thread.
This parameter helps in Enterprise Edition of Oracle.
With this parameter value set to Auto, you instructs the Oracle to choose automatically about parallelism used for SQL Statement.


-- At system level
alter system set parallel_degree_policy=auto scope=both;
-- At session level
alter session set parallel_degree_policy=auto;

Parallel_degree_auto has three values:
MANUAL: it is default which is disable the parallelism.
LIMITED: Oracle to use automatic DOP only on those objects with parallelism explicitly set.
AUTO: Oracle having all rights to set automatic DOP.

Steps follow for choosing the Statement used parallelism or Skip:
1. Parsing
2. Checked another parameter PARALLEL_MIN_TIME_THRESHOLD
3. If value is less then threshold value, then statements runs without parallelism.
4. If value is greater then threshold value, then statements runs with parallelism.

Other Parameters configured for Parallelism in Oracle Database:
Parellel_degree_limit: DOP is determined by no of CPU on the system or I/O requirement of a given query.
Note: One prerequisite of using automatic DOP is to run the DBMS_RESOURCE_MANAGER.CALIBRATE_IO procedure. This procedure run only once, and gathers information about the hardware on your system.
Parallel_max_servers: Set the maximum number of parallel processes (from 0 to 3600) for a database instance.
Parallel_min_servers: Set the minimum number of parallel processes for a database instance. Setting to a non-zero value keeps that minimum number of parallel processes for Statements.
Parallel_servers_target: let the oracle decide how many parallel processes can run at one time before query statements requiring parallel execution begin to be queued for execution.

Handle the broken DBMS JOB in Oracle

Handle the broken DBMS JOB in Oracle

Oracle will not attempt to execute the job until the broken status has been removed or the the job has been forced to execute
Job is broken by oracle automatically if it continue failed 16 attemps, then oracle marked the job as broken. Then Oracle never attempt to execute until its status not changed.

1. Check the broken job with column BROKEN present in dba_jobs view:

Select job,what,BROKEN from dba_jobs;

2. Remove the broken status of dbms job into running mode:

exec dbms_job.broken(1,FALSE);

3. You can also force the job to execute by run procedure:
To force a job marked as broken, using the DBMS_JOB.BROKEN statement, the following command can be used:

EXEC DBMS_JOB.RUN(52);

4. Verify with dba_jobs views:

Select job,what,BROKEN from dba_jobs;

5. To make the job broken, so that it is never run by Oracle Scheduler as:

exec dbms_job.broken(118,TRUE);

If you have multiple jobs running and you are continue facing the broken job issue you can scheduled the following procedure in scheduler for make broken job into running status:


CREATE OR REPLACE PROCEDURE job_fixer
AS
/*calls DBMS_JOB.BROKEN to try and set any broken jobs to unbroken*/
CURSOR broken_jobs_cur
IS
SELECT job
FROM user_jobs
WHERE broken = 'Y';
BEGIN
FOR job_rec IN broken_jobs_cur
LOOP
DBMS_JOB.BROKEN(job_rec.job,FALSE);
END LOOP;
END job_fixer;

Implementing Parallel DML Operations in Database

Implementing Parallel DML Operations in Database

Parallel DML Operation is disable by default. For enable fire the following command:

ALTER SESSION ENABLE PARALLEL DML;

to force parallel behaviour, regardless of the parallel degree you have placed on an object:

ALTER SESSION FORCE PARALLEL DML;

For session to run parallel DML operations:
You can run parallel session by specifying in hints or table have degree value more than 1. You can use parallel operation on all DML Statements INSERT, UPDATE & DELETE

1. Enable the session for Parallel DML operations:

ALTER SESSION ENABLE PARALLEL DML;

2. Run the parallel operation by specifying HInts in the statements:

INSERT /*+ PARALLEL(DEPT,4) */ INTO DEPT
SELECT /*+ PARALLEL(DEPT_COPY,4) */ * FROM DEPT_COPY;

UPDATE /*+ PARALLEL(EMP,4) */ EMP SET SAL = SAL*1.01 WHERE DEPTNO=10;

DELETE /*+ PARALLEL(EMP,4) */ FROM EMP WHERE DEPTNO=10;

If you want to delete or update large amount of data in bigger table in Enterprise edition then you can speed up with Parallel hints for the operations.

Note: Insert parallel is not work on single insert statement
Table having the trigger is not worked with parallel hints

Enabling Parallelism for a SQL Query

Enabling Parallelism for a SQL Query

You found a query running on large table, you want to enable parallelism to check its speed after normal execution.

For using the parallelism on SQL Query, you need to explicitly defines hints on the SQL Query for forcefully used the parallel operations.

Two way to be used parallel operation on SQL Query as
1. Parallel Hint
2. Parallel_index Hint

Parallel hint works on tables as follows:
In following queries, you are specify the parallel 4 means 4 process is used to fetch data from the employees table.

SELECT /*+ parallel(employees,4) */ emp_id, name FROM hr.employees;

In following queries, you specify nothing, Oracle detect the parallel operation based on database initialization parameters:

SELECT/*+ parallel(emp) */ emp_id, name FROM hr.employees emp;

Parallel_index hints
Parallel_index hint used for parallel access to indexes.

SELECT /*+ parallel_index(emp, emp_i4 ,4) */ emp_id, name
FROM employees WHERE deptno = 10;

Two arguments used in parallel_index hint: Table name and Index name. If you do not specify parallel number then oracle choose automatic on db settings.

NO_PARALLEL & NO_PARALLEL_INDEX Hints
Both used if you want to test the query without using parallel execution. Sometime Query is executed better in case of non parallel then parallel and your parallel level is set on table degree or instance level (PARALLEL_DEGREE_POLICY=AUTO) and you want to over come this situation for specific query. Then use No_PARALLEL and NO_PARALLEL_INDEX.

Note: As of Oracle 11g Release 2, the NOPARALLEL and NOPARALLEL_INDEX hints have been deprecated. Instead, use NO_PARALLEL and NO_PARALLEL_INDEX.

Check the parallelism of object or index with following query:

select degree from dba_tables where table_name = 'EMPLOYEES';

select index_name,degree from dba_indexes where table_name = 'EMPLOYEES';

Note: If automatic DOP is enabled (PARALLEL_DEGREE_POLICY=AUTO), then the parallelism that you set on objects is ignored.

Change the degree at object level ( table or index):

ALTER TABLE EMPLOYEES PARALLEL(DEGREE 4);

ALTER INDEX EMP_IDX PARALLEL(DEGREE 4);

ALTER TABLE EMPLOYEES PARALLEL(DEGREE 1);

ALTER TABLE EMPLOYEES NOPARALLEL;

Script for change parallel degree for all objects in schema

-- For indexes
select 'alter index '||owner||'."'||index_name||'" parallel (degree 4);' from dba_indexes where owner='IC';

-- For Tables
select 'alter table '||owner||'."'||table_name||'" parallel (degree 4);' from dba_tables where owner='IC';

Enable parallel at session level forcefully:


alter session force parallel query parallel N;
--example: Run SQL Query with parallel with 4 process
alter session force parallel query parallel 4;

Comparing Two Tables to Finding Missing Rows & Matching Rows

Comparing Two Tables to Finding Missing Rows & Matching Rows

Find rows in one table that are missing from the other.

You can used MINUS operator to compare the two sets of data or Select queries and show data missing from one table.

Note: select queries must be by identical in terms of columns number and datatypes

Example : you are working on school and fetch the data that this students pay all the dues or not both having seperate tables.
Master table Students_master having information of all students.
Pay table Students_dues having information which students pay dues.

Then you can compare this for find the pending dues list for the students.

select id from students_master;
id
--
1
2
3
4
5

Select id from students_dues;

Id
--
2
4
5

select Id from students_master;
minus
select id from students_dues;

ID
--
1
3

Finding the matching rows between the tables

INTERSECT operator is used to show the matching data between the two tables.

Note: select queries must be by identical in terms of columns number and datatypes

Examples: You want to see the student which pay the dues;
Master student table: All students information like name and address present in master table (students_master)
Dues student table: All paid students list is present in this table ( Students_dues)

select id from students_master
intersect
select id from students_dues

If you want to find additional information such as name

select first_name,address from students_master where id in
(select id from students_master
intersect
select id from students_dues
);

Joins in SQL

JOINS

A JOIN can be recognized in SQL as select statement if it has more than one table and we want to display the combined data from both tables. This join condition is based on primary keys and foreign keys. There must be n-1 join conditions for n joins to tables. If join condition is omitted then the result is Cartesian product.

SYNTAX
SQL>SELECT "list of columns" FROM table1, table2 WHERE "condition";

TYPES OF JOINS
EQUI JOIN: It returns all rows from tables where there is a match. Tables are joined on columns that have the same datatype & size in table. It is also known as equality join or simple join or inner join.

SYNTAX:
SELECT field1,field2 FROM table1,table2 WHERE table1.field=table2.field;

NATURAL JOIN: The SQL NATURAL JOIN is a type of EQUI JOIN and is structured in such a way that, columns with the same name of associated tables will appear once only.

CARTESION JOIN: When the join condition is omitted the result is Cartesian join of two or more tables in which all the combinations of rows will be displayed. All the rows are joined to all rows of the second table.

SYNTAX
SQL>SELECT field1, field2 FROM table1, table2

OUTER JOIN: While using equi join we see that if there exists certain rows in one table which don’t have corresponding values in the second table thn those rows will not be selected. We can forcefully select those rows by outer join. The rows for those columns will have NULL values.

SYNTAX
SELECT table1.col, table2.col FROM table1, table2 WHERE table1.col (+) = table2.col;

EXAMPLE
SQL>SELECT empno, ename, emp.deptno, dname FROM emp, dept WHERE emp.deptno (+) = dept.deptno;

LEFT OUTER JOIN: In case of Left outer join,all the fields will appear on left side and only those field will appear on right where there is match.

SQL> select emp.name, dep.fname from emp left join dept on emp.id=dept.id;

RIGHT OUTER JOIN: In case of Right outer join,all the fields will appear on right side and only those field will appear on left where there is match.

SQL> select emp.name, dept.fname from emp right join dept on emp .id=dept.id;