Scheduling Automatic Exp / Imp Jobs With Crontab Using Utility
Feb 26, 2012
i have a production database server with 2 node rac. i now have a separate database server for my report db. Now i want to do an automatic export of the production database or a schema in the production database and then import it automatically to the report db immadiately after the export job finishes at a specific time, say 12 midnight everyday. how do i go about this using crontab or any other better solution. my database is 11gr2 and operating system is rhel 5.7.
Oracle DB Version - 11g XE I scheduled a job using dbms_scheduler which will insert a record into table T for each minute. I didnt mention COMMIT inside my procedure but records are being commited after each successful execution. How come it is possible. Here is my code.
SQL> create table t ( empno number, creation_date date); Table created SQL> create or replace procedure test_proc 2 is 3 4 begin 5 6 insert into t values (1,sysdate); 7 8 end; 9 / Procedure created
we need to create a materialized view which should do complete refresh at pre determined interval like - 6am,12pm and 4pm every day.
Is it possible to have this option in materialized view?
As of now, I could understand we can do "start with" and "next" option which will schedule refresh with same interval but not with different time interval which I mentioned above.
I need to take RMAN full backup by every Sunday night and Wednesday night . I have the netbackup script which will take the backup to media.
Question is:1. We have 15 - backup scripts for 15 DB in that server , so if i configure crontab for backup do i need to give all 15 scripts one by one or (*) will work . ie) 00 20 * * 3,7 /app/oracle/rman/scripts/hot_db_<sid>.sh
instead of this if i give : 00 20 * * 3,7 /app/oracle/rman/scripts/hot_db_*.sh then it will works fine or not ? 2.
Already my backup will create .output file in the same location , do i need to give output file location in crontab using ( > ) ? 3.
Is the above crontab timing is correct ? (3,7) for Wednesday and Sunday 8pm? so Sunday is 0(zero) or 7 ?
@ the end of the day means After date changes i need to populate the new row in Some of my tables with the previous date values with the new date.. In that time some other Transaction will Occur on those tables so we need to lock all the tables in that time..Even i tried by doing the Exclusive lock on the tables...
execute immediate 'LOCK TABLE CTR_SALES IN EXCLUSIVE MODE NOWAIT'; execute immediate 'LOCK TABLE CTR_STK_ASSIGN IN EXCLUSIVE MODE NOWAIT';
And how to run this function @ 12:00 in the midnight by scheduling.... And 1 more thing how to pick the recent (Transaction) row on that table. I tried from the rowid it's not working
I have a shell script which triggers a batch in our application (an application which is not integrated with any other scheduler and is trying to integrate with OEM for the first time).
Is it possible to schedule a job to run this shell script in OEM ?
I have oracle 11g r2, I have a table emp, where i have three columns emp_no,status and creation_date, creation date is a time stamp like '02/09/2012 17:59:47'.
I have to trigger an email if the status is not updated for 30 minutes.
DECLARE CUSROR C_GET_DATA IS /*SELECTING RECORDS*/ SELECT * FROM EMP WHERE creation_date BETWEEN CREATION_DATE AND CREATION_dATE+30 AND STATUS IS NULL;
[Code]....
in the above code "CREATION_DATE AND CREATION_dATE+30" will give 30 days, how can i select just 30 minutes and schedule it every 30 minutes, so that email is triggered for if status is not updated for more than 30 minutes.
I have requirement as follows. I need to load the data to the target table on every Saturday. My source file consists of data of several sates. For every week i have to load one particular state data to target table. If first week I loaded AP data, then second week on Saturday karnatak, etc.
Provide code also how can i schedule the data load with every Saturday with different state column values automatically.
How can I call the dos2unix utility from Oracle ? I'm building an external table in a procedure where each time the latter is called the file name(s) are different.
I am using 11.2.0.3.0 version of oracle. I have not worked on regular expressions. During working on sql injection, I got set of below patterns which is feeded to some JAVA regx classes or utilityto restrict selective Request, based on patterns.below patterns and the characters which will be restricted by this pattern matching utility.
I worked on SQL Server used a GUI,SQL Server Profiler, to debug queries. In ORACLE how to debug queries.Is there any GUI utility for debugging?.Can i see all the queries run by a specific user?.
I have executed two jobs using dbms_scheduler. The value of the job_queue_process is 10 in my database. It is RAC database with Oracle 10g.
My job gets executed. But the next run is not getting executed at the next run date. In the below example, the systimestamp is 12:27. The first job's next run date is 12:30 so that is fine. But the second job's run date is 12:21 which is 6 mintues less than current time. As a result, the second job does not get executed after this at all..
I am trying to find a way to identify what are the user_jobs currently running. I have found 1 way which requires DBA User ID to execute (e.g. DBA_JOBS and DBA_JOBS_RUNNING) which I don't want.
I believe there is NO equivalent User table for DBA_JOBS_RUNNING table.
I have done my 20 mins search in this forum and unable to find.
I have a procedure that needs to be scheduled as a job. This procedure takes an average of 20-25 minutes to complete. The constraint is that these jobs are submitted using dbms_job.submit with an interval of 5 minutes.
So, will Job2 execute only after Job1 completes [even though Job1 takes 25 minutes to complete] or is there any other mechanism for setting incompatibility for a job with itself.
am facing an issue with scheduled jobs. The below script will create a scheduled job to insert a record for every 2 mins into the table "job_table".
--Table script CREATE TABLE job_table (now DATE); --Stored procedure run by scheduled job CREATE OR REPLACE PROCEDURE test1 IS BEGIN
[code]...
The scheduled job does not require any special grant privillege as the PUBLIC has access to run it. Anyhow the same set of instruction working for my friend on his machine but not on my environment.
I'm going to backup this un-maintained Oracle 11g server in my office. I'm being told it's never been backed up and extremely critical to our success so tomorrow I'll be performing my 1st backup of the database using RMAN. My question is if I connect to the Oracle 11g database and run the:
[oracle@db1 ~]$ rman target=/ Recovery Manager: Release 11.2.0.1.0 - Production on Mon Feb 13 13:18:05 2012 connected to target database: CQDB (DBID=1854033249)
RMAN> shutdown immediate
using target database control file instead of recovery catalog database closed database dismounted Oracle instance shut down
BACKUP DATABASET FORMAT �/oracle/u01/app/oracle/backup/%d-%T-%s-%P�;
My question is the above sufficient for a safe full backup? We only have the one single target database called 'cqdb' which houses several schemas. Can you tell me if this is correct? I've shut the database down so it's in a cold state so I don't think I need to configure the FRA (Flash / Fast Recovery Area) but I honestly don't know. This is my 2nd time on a Oracle DBMS.
What about the 'archivelog' files? Do I back those up? Do I need them in case a recovery? Also what about the 'controlfile'? Do I need to back those up as well as the mentioned above?
I have to automate TDPOSYNC utility, it is a IBM tool for oracle backup.I tried except utility of UNIX in shell script, but due to some reason same utility i could not get on production server.Not i asked to use PL/SQL to automate the same.I am facing some problem
1. How to call TDPOSYNC commands from pl/sql
2. How to pass run time input parameter to the TDPOSYNC like user/password, date rage etc.
