ORA-27492: unable to run job scheduler

My Oracle application have stopped transferring data to another table.
After doing a bit of troubleshooting,I have narrowed the issue down to the scheduled job PERSON.FIG_TIME_DAILY
The procedure that is called by this job works fine when I run in manually
However, this scheduled job hasn't successfully run in over a day
I tried dropping the job entirely and rebuilding it using the DDL from the same job in my testing environment, but that didn't seem to work.

Unable to run job "PERSON.FIG_TIME_DAILY" scheduler unavailable

From the online research I've done, most users have resolved this problem with some variation of the command

SQL>show parameter  job_queue_processes;
the value was default set to 1000

Than i Google out and run the following

SQL>select attribute_name,  value
from dba_scheduler_global_attribute
where attribute_name = 'SCHEDULER_DISABLED';

ATTRIBUTE_NAME     VALUE
------------------ -----
SCHEDULER_DISABLED TRUE

-- Attribute vanishes when set to false (run as sysdba)

SQL>exec dbms_scheduler.set_scheduler_attribute ( 'SCHEDULER_DISABLED', 'FALSE' );

After this Scheduler started Working

Introduction and Uses

Oracle Dataguard is a disaster recovery solution from Oracle that has been utilized in the industry extensively at times of Primary site failure, failover, switchover scenarios.

Uses of Oracle Dataguard

a) Oracle Dataguard ensures high availability, data protection and disaster recovery for enterprise data

b) Dataguard provides a comprehensive set of services that create, maintain, manage and monitor one or more standby databases to enable production Oracle databases to survive disasters and data corruptions

c) With Dataguard, administrators can optionally improve production database performance by offloading resource – intensive backup and reporting operations to standby systems.

Why are OPS$ accounts a security risk in a client/server environment

If you allow people to log in with OPS$ accounts from Windows Workstation, you cannot be sure who they really are. With terminals, you can rely on operating system passwords with Windows you cannot.

If you set REMOTE_OS_AUTHENT = TRUE in your init.ora file, Oracle assumes that remote OS has authenticated the user.

If REMOTE_OS_AUTHENT is set to FALSE (recommended), remote users will be unable to connect without a password

IDENTIFIED EXTERNALLY will only be in effect from the local host.

Also, if you are using "OPS$" as your prefix, you will be able to log on locally with or without a password, regardless of whether you have identified your ID with a password or defined it to be IDENTIFIED EXTERNALLY

What is the difference between restoring and recovering?

Restoring involves copying backup files from secondary storage (backup media) to disk.

This can be done to replace damaged files or to copy/move a database to a new location.

Recovery is the process of applying redo log to the database to roll it forward.

One can roll – forward until a specific point – in – time (before the disaster occurred) or roll – forward until the last transaction recorded in the log files.

SQL> connect SYS as SYSDBA

SQL> RECOVER DATABASE UNTIL TIME '2016 – 03 – 06:16:00:00' USING BACKUP CONTROLFILE; 

What strategies are available for backing – up an Oracle database?

The following methods are valid for backing – up an Oracle database 

Export / Import – Exports are "logical" database backups in that they extract logical definitions and data from the database to a file.

Cold or Offline Backups – Shutdown the database and backup up all data, log and control files

Hot or Online Backups – If the databases are available and in ARCHIVELOG mode, set the tablespaces into backup mode and backup their files

Also remember to back up the control files and archived redo log files.

RMAN Backups – While the database is Offline or Online use "RMAN" utility to back up database

Oracle DataPump - ExpDp

It is advisable to use more than one of these methods to backup your database.

For example, if you choose to do Online database backups; also cover yourself by doing database exports.

Also test all backup and recovery scenarios carefully. It is better to be safe than sorry. 

Regardless of your strategy, also remember to backup all required software libraries, parameter files, password files etc.

If your database is in ARCHIVELOG mode, you also need to backup archived log files.

Why and when should one backup database?

Backup and recovery is one of the most important aspects of a DBA job. 

If you lose your company's data, you could very well lose your job.

Hardware and software can always be replaced, but your data may be irreplaceable!
Normally one would schedule a hierarchy of daily, weekly and monthly backups, however consult with your users before deciding on a backup schedule.

1. Backup frequency normally depends on the following factors
2. Rate of data change/ transaction rate
3. Database availability/ Can you shutdown for cold backups? 
4. Criticality of the data/ Value of the data to the company
5. Read – only table space needs backing up just once right after you make it read only
6. If you are running in archivelog mode you can backup parts of a database over an extended cycle of days
7. If archive logging is enabled one needs to backup archived log files timeously to prevent database freezes

Carefully plan backup retention periods. 

Ensure enough backup media (tapes) are available and that old backups are expired in – time to make media available for new backups.

Offsite vaulting is also highly recommended.
Frequently test your ability to recover and document all possible scenarios.

Remember, it's the little things that will get you. Most failed recoveries are a result of organizational errors and miscommunications

Can one resize tablespaces and datafiles

One can manually increase or decrease size of a datafile from Oracle 7.2 using command.

SQL>ALTER DATABASE DATAFILE 'filename2' RESIZE 100M; 

Because one can change the sizes of data files, one can add more space to your database without adding more datafiles. This is beneficial if concerned about reaching the maximum number of datafiles allowed in your database
Manually reducing the sizes of datafiles allows one to reclaim unused space in the database. This is useful for correcting errors in estimations of space requirements. 

Also, datafiles can be allowed to automatically extend if more space is required.

Command 

SQL>CREATE TABLESPACE pcs_data_ts
DATAFILE 'c: \ora_apps\pcs\pcsdata1.dbf' SIZE 3M
AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
DEFAULT STORAGE (INITIAL 10240
NEXT 10240
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0)
ONLINE
PERMANENT; 

What is Fine Grained Auditing?

Fine Grained Auditing (DBMS_FGA) allows auditing records to be generated when certain rows are selected from a table.

A list of defined policies can be obtained from DBA_AUDIT_POLICIES

Audit records are stored in DBA_FGA_AUDIT_TRAIL

Example:

Add policy on table with auditing condition...
execute dbms_fga.add_policy('HR', 'EMP', 'policy1', 'deptno > 10');
Must ANALYZE, this feature works with CBO (Cost Based Optimizer)

SQL>analyze table EMP compute statistics;
SQL>select * from EMP where c1 = 11; -- Will trigger auditing
SQL>select * from EMP where c1 = 09; -- No auditing

Now we can see the statements that triggered the auditing condition...
SQL>select sqltext from sys.fga_log$;
SQL>delete from sys.fga_log$; 

When should one rebuild an index?

You can run 'ANALYZE INDEX VALIDATE STRUCTURE' command on the affected indexes; each invocation of this command creates a single row in the INDEX_STATS view. 

This row is overwritten by the next ANALYZE INDEX command, so copy the contents of the view into a local table after each ANALYZE 

The 'badness' of the index can then be judged by the ratio of 'DEL_LF_ROWS' to 'LF_ROWS' 

How does one create a RMAN recovery catalog?

Start by creating a database schema (usually called RMAN)

Assign an appropriate table space to it and grant it recovery_catalog_owner role.

sqlplus sys
SQL>create user RMAN identified by RMAN;
SQL> alter user RMAN default table space tools temporary tablespace temp;
SQL> alter user RMAN quota unlimited on tools;
SQL> grant connect, resource, recovery_catalog_owner to RMAN;

Next, log in to RMAN and create the catalog schema.

Prior to Oracle 8i this was done by running catRMAN.sql script.

RMAN> catalog RMAN/RMAN
RMAN>create catalog tablespace tools;
RMAN> exit; 

You can now continue by registering your databases in the catalog.

RMAN catalog RMAN/RMAN target backdba/backdba
RMAN> register database; 

I've lost some Rollback Segments, how can I get my DB back

Restart your database with following INIT.ORA parameter _Corrupted_rollback_segments = (rbs01, rbs01, rbs03, rbs04) if one of your rollback segments is corrupted.

You can then drop the corrupted rollback segments and create it from scratch.

Caution is advised when enabling this parameter, as uncommitted transactions will be marked as committed.

One can very well end up with lost or inconsistent data!!!

Please contact Oracle Support before using it.

 _Corrupted_rollback_segments = (rbs01, rbs01, rbs03, rbs04) 

I've lost my REDO LOG files, how can I get my DB back?

The following INIT.ORA parameter _allow_resetlogs_corruption = true may be required if your current redo logs are corrupted or blown away.

Caution is advised when enabling this parameter as you might end up losing your entire database

Please contact Oracle Support before using it. _allow_resetlogs_corruption = true 

My database is down and I cannot restore. What now?

Recovery without any backup is normally not supported; however, Oracle Consulting can sometimes extract data from an Offline database using a utility called DUL (Disk Unload).

This utility reads data in the data files and unloads it into SQL*Loader or export dump files.

DUL does not care about rollback segments, corrupted blocks etc. and can thus not guarantee that the data is not logically corrupt

It is intended as an absolute last resort and will most likely cost your company a lot of money!

My database was terminated while in BACKUP MODE, do I need to recover

If a database was terminated while one of its tablespaces was in BACKUP MODE

ALTER TABLESPACE <table space name> BEGIN BACKUP;

It will tell you that media recovery is required when you try to restart the database.

DBA is then required to recover the database and apply all archived logs to the database.

However, from Oracle7.2, you can simply take the individual data files out of backup mode and restart the database

ALTER DATABASE DATAFILE '/path/filename' END BACKUP; 

One can select from V$BACKUP to see which data files are in backup mode. This normally saves a significant amount of database down time.

From Oracle9i onwards, the following command can be used to take all of the data files out of hot backup mode

ALTER DATABASE END BACKUP; 

The above commands need to be issued when the database is mounted

What are the common Import / Export problems

ORA – 00001: Unique constraint (...) violated
You are importing duplicate rows.

Use IGNORE=NO to skip tables that already exist

(Imp will give an error if the object is re – created)

ORA – 01555: Snapshot too old 
Ask your users to STOP working while you are exporting or use parameter CONSISTENT=NO

ORA – 01562: Failed to extend rollback segment
Create bigger rollback segments or set parameter 
COMMIT = Y while importing

IMP – 00015: Statement failed ... object already exists...

Use IGNORE = Y import parameter to ignore these errors, but be careful as you might end up with duplicate rows

Can one export to multiple files? Can one beat UNIX 2 Gig limit

From Oracle8i, export utility supports multiple output files. This feature enables large exports to be divided into files whose sizes will not exceed any operating system limits (FILESIZE= parameter). When importing from multi – file export you must provide the same filenames in the same sequence in the FILE = parameter

Look at this example

exp SCOTT/TIGER FILE=D:\F1.dmp,E:\F2.dmp FILESIZE=10m LOG=scott.log

Use the following technique if you use an Oracle version prior to 8i

Create a compressed export on the fly. Depending on the type of data, you probably can export up to 10 gigabytes to a single file. This example uses gzip. It offers the best compression I know of, but you can also substitute it with zip, compress or whatever.

# creates a named pipe
mknod exp.pipe p

# read the pipe – output to zip file in the background
gzip < exp.pipe > scott.exp.gz &

# feed the pipe
exp userid=scott/tiger file=exp.pipe... 

Can one import/export between different versions of Oracle

Different versions of import utility are upward compatible. This means that one can take an export file created from an old export version and import it using a later version of the import utility. 

This is quite an effective way of upgrading a database from one release of Oracle to the next. 

Oracle also ships some previous catexpX.sql scripts that can be executed as user SYS enabling older imp/exp versions to work (for backwards compatibility)

For example, one can run $ORACLE_HOME/rdbms/admin/catexp7.sql on an Oracle 8 database to allow Oracle 7.3 exp/imp utilities to run against an Oracle 8 database

Does one need to drop / truncate objects before importing

Before one import rows into already populated tables, one needs to truncate or drop these tables to get rid of the old data. If not, the new data will be appended to the existing tables.

One must always DROP existing sequences before re – importing. 

If the sequences are not dropped, they will generate numbers inconsistent with the rest of the database.

Note: It is also advisable to drop indexes before importing to speed up the import process. Indexes can easily be recreated after the data was successfully imported. 

Where should be the tuning effort directed

Consider the following areas for tuning.

The order in which steps are listed needs to be maintained to prevent tuning side effects.

For example, it is not good increasing the buffer cache if you can reduce I/O by rewriting a SQL statement. Database Design (if it's not too late)
Poor system performance usually results from a poor database design. 
One should generally normalize to the 3^rd Normalization Format. 
Selective denormalization can provide valuable performance improvements. 
When designing, always keep the "data access path" in mind. 
Also look at proper data partitioning, data replication and aggregation tables for decision support systems etc. 

Application Tuning
Experience showed that approximately 80% of all Oracle system performance problems are resolved by coding optimal SQL. Also consider proper scheduling of batch tasks after peak working hours. 
Memory Tuning

Properly size your database buffers (shared pool, buffer cache, log buffer etc.) by looking at your buffer hit ratios. Pin large objects into memory to prevent frequent reloads. 
Disk I/O Tuning
Database files needs to be properly sized and placed to provide maximum disk subsystem throughput. Also look for frequent disk sorts, full table scans, missing indexes, row chaining, data fragmentation etc
Eliminate Database Contention
Study database locks, latches and wait events carefully and eliminate where possible.

Tune the Operating System

Monitor and tune operating system CPU, I/O and memory utilization. For more information, read the related Oracle FAQ dealing with your specific operating system. 

What database aspects should be monitored

One should implement a monitoring system to constantly monitor the following aspects of a database. 

Writing custom scripts, implementing Oracle's Enterprise Manager or buying a third party monitoring product that can achieve this. If an alarm is triggered, system should automatically notify the DBA (e – mail, pager etc.) to take appropriate action. 

Infrastructure availability

1. Is the database up and responding to request
2. Are the listeners up and responding to request
3. Are Oracle Names and LDAP Servers up and responding to request
4. Are the Web Listeners up and responding to requests 

Things that can cause service outrages

1. Is the archive log destination filling up? 
2. Objects getting close to their max extents
3. User and process limits reached