Oracle Jobs

If you want to schedule a job in the Oracle Database to execute a certain task regularly, basically you can use one of the following methods:

1. Oracle Scheduler (DBMS_SCHEDULER):

DBMS_SCHEDULER is a powerful feature that you can use to schedule jobs on the database, which can be very useful for certain DBA tasks like indexes rebuilding, backups checking or data consistency.

To view the information about currently running jobs, you can execute this query:

SELECT * FROM ALL_SCHEDULER_RUNNING_JOBS;

With this query you can check all the scheduled jobs into the database:

SELECT JOB_NAME, STATE FROM DBA_SCHEDULER_JOBS;

It is a very strong feature and very recommended currently. If you want to find more information about the scheduler, check the Oracle documentation in this link:

https://docs.oracle.com/database/121/ARPLS/d_sched.htm

2. Oracle Jobs (DBMS_JOB):

DBMS_JOB is a deprecated feature but you can still use it even in Oracle 12c, even if it started in Oracle 7!

The DBMS JOB feature is a lot less intuitive to use, although some DBAs state that it is more flexible. In any case, either if you want to take advantage of its flexibility or if you need to understand how it works because your database already uses this feature, it is another option.
You can find the Oracle documentation here:

https://docs.oracle.com/database/121/ARPLS/d_job.htm#ARPLS66577

And a very good and practical post explaining how to use DBMS_JOB:

http://www.orafaq.com/node/871
3. Crontab (Unix/Linux) / Windows Task Scheduler

The third option is to externalize the task execution to the Operating System, so that the tasks are executed through a .sh in Unix, or a .bat in Windows, executing later a .sql to the database.

This method is easier to manage if you are already using crontab or Task Scheduler for other tasks not related with the database, but the disadvantage is that it is node dependent so it can be a problem in the case of Oracle RAC.
Do you know any other method to schedule jobs in Oracle? What is your prefered one?

Incremental restore from primary database to standby database

If we have Dataguard, either with physical or logical standby, our databases will always be synchronized and we will be able to do switchover whenever we want.

Or not… maybe when we want to do a switchover, we notice that the standby database hasn’t been synchronized in months, and we need to restore the synchronization as soon as possible.

If the gap resolution is not an option, we will have to use RMAN in order to perform this operation. The steps will be the following ones:

1. On standby site, identify the minimum SCN used by all the datafiles:

SQL> select min(CHECKPOINT_CHANGE#) from v$datafile_header;

MIN(CHECKPOINT_CHANGE#)
———————–
262044906

2. On primary site, take an incremental backup of the database using a number a bit smaller than the smallest SCN obtained.

backup as compressed backupset incremental from scn 262044600 database format ‘/opt/oracle/install/restore_syncr/full_backup_mario_%U’;

3. Take also a backup of the controlfile:

backup as copy current controlfile format ‘/opt/oracle/install/restore_syncr/backup_control_file_mario’;

4. Copy the files to standby site (scp, ftp, diskettes)

5. Execute the following commands on RMAN in order to apply the incremental backup:

shutdown immediate;
startup force nomount;
restore standby controlfile from ‘/opt/oracle/install/restore_syncr/backup_control_file_mario’ ;
alter database mount;
catalog start with ‘+DATA/mario/datafile’ ;
catalog start with ‘/opt/oracle/install/restore_syncr/’ ;
switch database to copy ;

run {
allocate channel a0 device type disk ;
allocate channel a1 device type disk ;
allocate channel a2 device type disk ;
allocate channel a3 device type disk ;
allocate channel a4 device type disk ;
allocate channel a5 device type disk ;
allocate channel a6 device type disk ;
allocate channel a7 device type disk ;
allocate channel a8 device type disk ;
allocate channel a9 device type disk ;

recover database ;
}

6. Start the MRP process:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT ;

7. Enjoy your synchronized database and make sure that you don’t have to restore it again

DBA Tasks

We can separate the dba tasks between daily, weekly and monthly procedures:

Daily
– Verify instance status
– Check alerts
– Check configured metrics
– Check RMAN backups
– Check storage
– Check CPU contention
– Check waiting times
– Check memory usage
– Check network load
– Check iostat

Weekly

– Invalid objects
– Tunning: indexes and execution plans
– Top SQL
– Environments consistence
– Review of ressource policy
– Trends and peaks
– Cleaning of alert logs
– Review of RMAN

Monthly

– Recovery tests
– Analyze the data increment trend
– Tunning
– Review I/O
– Fragmentation
– Row chaining
– High Availability Analysis
– Scalability
– Schedule monthly downtime