How to see the oldest flashback available?

Using the following query one can see the flashback data available.

SELECT to_char(sysdate,'YYYY-MM-DD HH24:MI') current_time, to_char(f.oldest_flashback_time, 'YYYY-MM-DD HH24:MI') OLDEST_FLASHBACK_TIME,
(sysdate – f.oldest_flashback_time)*24*60 HIST_MIN FROM v$database d, V$FLASHBACK_DATABASE_LOG f;

CURRENT_TIME OLDEST_FLASHBACK HIST_MIN
—————- —————- ———-
2012-04-25 07:34 2012-04-25 05:48 106.066667

How to stop a job scheduled in DBMS_SCHEDULER?

Using sys.dbms_scheduler.STOP_JOB one can stop scheduled job.

SQL> exec sys.dbms_scheduler.STOP_JOB(job_name=>'SYS.ORA$AT_OS_OPT_SY_12856', force=>true);

Output:
PL/SQL procedure successfully completed.

If it can’t find the job then you may see the following error, in the example below I hadn’t specified the user to it was looking for the job as the current user.
ORA-27475: “DBAUSER.ORA$AT_OS_OPT_SY_12856” must be a job
ORA-06512: at “SYS.DBMS_ISCHED”, line 199
ORA-06512: at “SYS.DBMS_SCHEDULER”, line 557
ORA-06512: at line 1

To see jobs running https://oraclespin.wordpress.com/2010/05/08/dba_scheduler_running_jobs/

dba_jobs and interval

Few examples of some scenarios of setting intervals for dba_jobs. Note: Updating the interval will not update the NEXT_DATE run of the job until the job runs.

Setting a job to run every monday at 10:00 am
SQL> exec dbms_job.interval(5, 'TRUNC(NEXT_DAY(SYSDATE, ''MONDAY''))+(10/24)');
SQL> commit;

— assume job was run and this SQL was run after it was run
SQL> SELECT job, interval, next_date FROM dba_jobs where job = 5;
JOB NEXT_DATE
———- ——————–
5 19-MAR-2012 10:00:00

Setting a job to run every hour on the 1/2 hour
SQL> exec dbms_job.interval(5, 'TRUNC(SYSDATE, ''HH'')+(1.5/24)');
SQL> commit;

Setting a job to run every hour after the last time the job was started
SQL> exec dbms_job.interval(5, 'SYSDATE+(1/24)');
SQL commit;
— assume job was run and this SQL was run after it was run, the time NEXT_DATE would be a hour after the job was last started.
SQL> SELECT job, interval, next_date FROM dba_jobs where job = 5;
JOB NEXT_DATE
———- ——————–
5 15-MAR-2012 22:45:00

Using sys.dbms_iob one can manage jobs in dba_jobs.

DBMS_JOB allows one to create/manage jobs under user who has logged but using sys.dbms_ijob one can manage jobs all jobs scheduled in DBA_JOBS.

Here are some of the functions available in sys.dbms_ijob.
To execute/run job: You don’t have to be an owner of the job
SQL> exec sys.dbms_ijob.run(5);

If one tried executing the job not owned by the user using DBMS_JOB, one can’t run it and you will get the following error “ORA-23421: job number 21 is not a job in the job queue”

To enable/disable job:
-- to disable a job. Note one should set this when the job is not running by checking DBA_JOBS_RUNNING, because if the job is running you will have to wait till it finishes before disabling it and it has to be followed by a commit.
SQL> exec sys.dbms_ijob.broken(5, true);
SQL> commit;

-- Note the next_date value it initialized when the job is broken.
SQL> select broken, next_date from dba_jobs where job = 5;

B TO_CHAR(NEXT_DATE,’D
– ——————–
Y 01-JAN-4000 00:00:00

-- to enable a job, note it has to be followed by a commit and after the job is enabled the NEXT_DATE is updated based on the value of interval.
SQL> exec sys.dbms_ijob.broken(5, false);
SQL> commit;

SQL> select broken, next_date from dba_jobs where job in ( 5 );

B TO_CHAR(NEXT_DATE,’D
– ——————–
N 11-MAR-2012 19:31:45

To change the next date the job runs, again note if the job is running when one modifies this, it will get reset to the next interval instead of the value specified.
SQL> exec sys.dbms_ijob.next_date(5, sysdate + 1);
SQL commit;

SQL> select next_date from dba_jobs where job in ( 5 );

B TO_CHAR(NEXT_DATE,’D
– ——————–
N 12-MAR-2012 19:41:27

To change the interval
Example below changes the interval to run every Monday at midnight
SQL> exec sys.dbms_ijob.interval(5, 'TRUNC(NEXT_DAY(sysdate, ''MONDAY''))');
PL/SQL procedure successfully completed.
SQL> commit;

How to tell/find if the instance is Primary/Standby?

By querying v$database one can tell if the host is primary or standby

For primary
testdb> select database_role from v$database;

DATABASE_ROLE
—————-
PRIMARY

For Standby – Note you may need to connect to as sys as sysdba if the instance is in mount state
testd> select database_role from v$database;

DATABASE_ROLE
—————-
PHYSICAL STANDBY

OR
On Primary database the value of controlfile_type in V$database is “CURRENT” and standby is “STANDBY”

Value on Primary:
SQL> SELECT controlfile_type FROM V$database;

CONTROL
——–
CURRENT

Value on Standby:
SQL> SELECT controlfile_type FROM V$database;

CONTROL
——–
STANDBY

Dataguard ORA-16817

If you have an issue where the server where standby resides had to be restarted and when you try to enable Fast Start Failover, it wouldn’t enable due to ORA-16608 and when checking the primary database in dataguard it would return ORA-16817 “unsynchronized fast-start failover configuration”

Example:
$ dgmgrl /
DGMGRL> enable configuration
Enabled.
DGMGRL> show configuration verbose
Configuration
Name: FSF
Enabled: YES
Protection Mode: MaxAvailability
Databases:
testdb_a – Primary database
testdb_b – Physical standby database

Fast-Start Failover: DISABLED
Current status for “FSF”:
Warning: ORA-16608: one or more databases have warnings

So upon checking both the databases, the primary would report the following warning ORA-16817 and Standby was fine.
DGMGRL> SHOW DATABASE VERBOSE testdb_a
..
Current status for “testdb_a”:
Warning: ORA-16817: unsynchronized fast-start failover configuration.

Also then checking the primary it returned the following:
On Primary:
SQL> select PROTECTION_LEVEL, FS_FAILOVER_STATUS from v$database;
PROTECTION_LEVEL FS_FAILOVER_STATUS
——————– ———————-
RESYNCHRONIZATION UNSYNCHRONIZED

And checking V$archived_log

select * from (
select sequence#,dest_id,first_time,archived,applied,completion_time, name
from v$archived_log
where applied = ‘YES’
order by first_time desc
);

SEQUENCE# DEST_ID FIRST_TIM ARC APPLIED COMPLETIO NAME
———- ———- ——— — ——— ——— ———————-

15321 2 03-MAR-12 YES NO 03-MAR-12 testdb_b.amazon
15321 1 03-MAR-12 YES NO 03-MAR-12 /arch/testdb/redolog-15321-1.arc
15320 1 03-MAR-12 YES NO 03-MAR-12 /arch/testdb/redolog-15320-1.arc
15319 2 03-MAR-12 YES NO 03-MAR-12 testdb_b.amazon
15319 1 03-MAR-12 YES NO 03-MAR-12 /arch/testdb/redolog-15319-1.arc
15318 2 03-MAR-12 YES YES 03-MAR-12 avm1na_b.amazon
15318 1 03-MAR-12 YES NO 03-MAR-12 /arch/testdb/redolog-15318-1.arc
..

If you see above there is a gap for redo log sequence# 15320 and due to which primary is in the Resynchronization state and when one does a logfile switch on primary, it transmits the redo log to standby and standby applies the archive log (by monitoring the alert log of standby).

So fix this issue, resetting the value of log_archive_max_processes would fix the synchronize issue i.e. update FS_FAILOVER_STATUS to SYNCHRONIZED and update the protection_level in v$database.

SQL> show parameters log_archive_max_processes;
log_archive_max_processes integer 4

SQL> alter system set log_archive_max_processes=1;
System altered.

SQL> alter system set log_archive_max_processes=4;
System altered.

How to suspend all jobs from executing in dba_jobs?

By setting the value of 0 to the parameter “job_queue_processes” one can suspend all jobs from executing in DBA_JOBS. The value of this parameter can be changed without instance restart.

-- see current value of the parameter
SQL> show parameter job_queue_processes;

NAME TYPE VALUE
———————————— ———– ———–
job_queue_processes integer 1000

-- Set the value of the parameter in memory, which will suspend jobs from starting
SQL> alter system set job_queue_processes=0 scope=memory;

System altered.

How to see the jobs currently being executed?

Using dba_jobs_running it will show the all jobs executing.

SQL> select djr.sid, djr.job, djr.failures, djr.this_date, djr.this_sec, dj.what from dba_jobs_running djr, dba_jobs dj where djr.job = dj.job;

SID JOB FAILURES THIS_DATE THIS_SEC
———- ———- ———- ——————– ——–
WHAT
—————————————————————————————————-

68 21 0 19-feb-2012 21:09:34 21:09:34
dbms_lock.sleep(100);

How to submit a new job using dbms_job?

Using dbms_job one can schedule a job, in the following example schedules a job to run every 5 minutes starting from current time. The next interval can be fixed time or it can be based on the time when the last run finished, in the example below the interval is time when the last run/time when the job finishes so the next run would occur 5 minutes after the last run. The owner, priv user and log user of the job created would be the user who executed the dbms_job.submit.
SQL> set serveroutput on
SQL> declare
job_id number;
begin
dbms_job.submit(job_id, 'dbms_lock.sleep(100);', sysdate, 'sysdate+5/(24*60)');
dbms_output.put_line('Job: ' || job);
end;
/
Job: 21
PL/SQL procedure successfully completed.

--Make sure you run commit to schedule the job.
SQL> commit;

-- To see the time when the job last run and next time it will run
SQL> select job, last_date, last_sec, next_date, next_Sec from dba_jobs where job in (21);

JOB LAST_DATE LAST_SEC NEXT_DATE NEXT_SEC
———- ——— ——– ——— ——–
21 19-FEB-12 19:57:43 19-FEB-12 20:02:43

How to generate execution plan created from AWR snapshot?

Using $ORACLE_HOME/rdbms/admin/awrsqrpt.sql one can generate execution plan from AWR by passing start snap id, end snap id and SQL ID.

It will ask for most of the same inputs like awrrpt.sql, type of report (html/text), start snap id, end snap id, SQL id and report name and create a output in the file specified.

Example:
SQL> @?/rdbms/admin/awrsqrpt.sql