DBMS SCHEDULER MAINTENANCE

Create a job

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
     job_name           => 'TEST_JOB',
     job_type           => 'EXECUTABLE',
     job_action         => '/u01/app/abc.sql',
     repeat_interval    => 'FREQ=MINUTELY',
     enabled            => TRUE
  );
END;
/


Unlike DBMS_JOB you do not need to commit the job creation for it to be taken into account.
As a corollary, if you want to cancel it, you have to remove or disable it.

           ******   Remove a job

EXEC DBMS_SCHEDULER.DROP_JOB('TEST_JOB');

           ******   Run a job now

To force immediate job execution:
EXEC dbms_scheduler.run_job('myjob');

           ******   Change job attributes

Examples:
EXEC DBMS_SCHEDULER.SET_ATTRIBUTE('WEEKNIGHT_WINDOW', 'duration', '+000 06:00:00');
BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE
     ('WEEKNIGHT_WINDOW', 'repeat_interval',
      'freq=daily;byday=MON, TUE, WED, THU, FRI;byhour=0;byminute=0;bysecond=0');
END;

           ******   Enable / Disable a job

BEGIN
  DBMS_SCHEDULER.ENABLE('myjob');
END;
BEGIN
  DBMS_SCHEDULER.DISABLE('myjob');
END;

           ******    Monitoring jobs

SELECT * FROM dba_scheduler_jobs WHERE job_name = 'TEST_JOB';
SELECT * FROM dba_scheduler_job_log WHERE job_name = 'TEST_JOB';

           ******    or checking from JOB owner schema

 SELECT * FROM user_scheduler_jobs WHERE job_name = 'TEST_JOB';
 SELECT * FROM user_scheduler_job_log WHERE job_name = 'TEST_JOB';

ORA-01113: file 13 needs media recovery

oracle@Linux01:[+DATA1] $ sqlplus /"As sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 21 10:09:56 2012

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL > ALTER database  RENAME FILE '/u01/app/oracle/datafiles/SOME_TABLESPC_USER_03.dbf' TO '/u01/app/oracle/datafiles/qpdev/SOME_USER_04.dbf';
            

SQL> ALTER DATABASE

DATAFILE '+DATA1/USER_DATA_04.dbf'

ONLINE  2 ;

*

ERROR at line 3:

ORA-01916: keyword ONLINE, OFFLINE, RESIZE, AUTOEXTEND or END/DROP expected


SQL> ALTER DATABASE DATAFILE '+DATA1/USER_DATA_04.dbf' ONLINE;

ALTER DATABASE DATAFILE '+DATA1/USER_DATA_04.dbf' ONLINE

*

ERROR at line 1:

ORA-01113: file 13 needs media recovery

ORA-01110: data file 13: '+DATA1/USER_DATA_04.dbf'

SQL> recover datafile '+DATA1/USER_DATA_04.dbf';

Media recovery complete.

SQL> ALTER DATABASE DATAFILE '+DATA1/USER_DATA_04.dbf' ONLINE;

Database altered.


SQL>

FIND ARCHIVE GAP BETWEEN PRIMARY AND STANDBY

Finding Archive gap between PRIMARY and STANDBY

  
  A Physical Standby database syncs with Primary by continuous apply of archive logs from a Primary Database. When the logs are missing on standby is less than 10-15 numbers, We can simple ship the logs which are missing in the standby database from primary database by using SCP/FTP and then register the log files in standby to resolve the gap.

Use below query on standby Db to see if there is any gap. This will be help full if u have logs shipped and not applied. But in situations when the listener is down or network disturbances. etc the logs wont be shipped so this query might not work.

Run on Primary DB :

Primary: SQL > select thread#, max(sequence#) "Last Primary Seq Generated"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#

group by thread# order by 1;

The primary thread max seq should match standby.

Run on Standby DB:

******************************************************************************
----- More Detailed info with max received seqno# & max applied seqno# included -----
******************************************************************************

select  MAX_RECEIVED_SEQNO,MAX_APPLIED_SEQNO,(MAX_RECEIVED_SEQNO - MAX_APPLIED_SEQNO) as "Difference" from (SELECT 
   ARCHIVED_THREAD#, MAX(ARCHIVED_SEQ#) as MAX_RECEIVED_SEQNO, 
   APPLIED_THREAD#,  MAX(APPLIED_SEQ#) as  MAX_APPLIED_SEQNO
FROM 
  V$ARCHIVE_DEST_STATUS) ARCH ;

Sample output :


SQL> select  MAX_RECEIVED,MAX_APPLIED,(MAX_RECEIVED - MAX_APPLIED) as "Difference" from (SELECT
   ARCHIVED_THREAD#, MAX(ARCHIVED_SEQ#) as MAX_RECEIVED,
   APPLIED_THREAD#,  MAX(APPLIED_SEQ#) as  MAX_APPLIED
FROM
  V$ARCHIVE_DEST_STATUS) ARCH ;  

MAX_RECEIVED          MAX_APPLIED         Difference
------------                                -----------                ----------
       26424                                   26419                       5


*********************************************************************************
----- Simple query  to just display difference-----
*********************************************************************************

select  MAX_RECEIVED,MAX_APPLIED,(MAX_RECEIVED - MAX_APPLIED) as "Difference" from (SELECT 
   ARCHIVED_THREAD#, MAX(ARCHIVED_SEQ#) as MAX_RECEIVED, 
   APPLIED_THREAD#,  MAX(APPLIED_SEQ#) as  MAX_APPLIED
FROM 
  V$ARCHIVE_DEST_STATUS) ARCH ;

Sample output :


SQL> select (MAX_RECEIVED - MAX_APPLIED) as "Difference" from (SELECT
   ARCHIVED_THREAD#, MAX(ARCHIVED_SEQ#) as MAX_RECEIVED,
   APPLIED_THREAD#,  MAX(APPLIED_SEQ#) as  MAX_APPLIED
FROM
  V$ARCHIVE_DEST_STATUS) ARCH ; 

Difference
----------
         3


  How to Sync small gap betwen Primary and Standby :

Lets say there is a Gap between the DB's, try to sync them manually if gap is small. Follow below process :

COPY MISSING ARCHIVELOG FILE'S FROM PRIMARY TO STANDBY :

$ scp log_file_1234.arc oracle@standby:/log_location/log_file_1234.arc

NOW REGISTER LOGFILE IN STANDBY DB:

SQL> alter database register logfile ‘/log_location/log_file_n.arc'; 
logfile registered

Repeat the same process for all the log files which are missing at standby.In case the gap is huge You can use Rman backup to cover the gap.

 Similar Posts :

• How to add second standby database 
• How to create Standby database using RMAN
• Open Standby Database in Read-Only 

Flashback Database setup in Oracle

Enable Flash back on :

Starting from Oracle 11g R2 we don't have to bounce the Database for these effects to get affected. If you are using pre 11G R2, you have to re bounce your system for these settings to get affected.

[oracle@Linux1 ~]$ sqlplus /"As sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Mon May 4 10:25:05 2015

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production


Lets check if Flash back is enabled or not :

SQL> select flashback_on, status from v$database, v$instance;

FLASHBACK_ON       STATUS
------------------ ------------
NO                 OPEN

Since it is not enabled. Lets turn flash back ON:

SQL> alter database flashback on;

Database altered.


SQL> select flashback_on, status from v$database, v$instance;

FLASHBACK_ON       STATUS
------------------ ------------
YES                OPEN

Confirm the size of DB_RECOVERY_FILE_DEST_SIZE (Better keep it at least 30GB+ until it is Dev or Test environment)

SQL> show parameter DB_RECOVERY_FILE_DEST_SIZE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size           big integer 20000M

I want to set it to 30GB:

SQL> alter system set db_recovery_file_dest_size=30G  ;

System altered.

SQL> show parameter DB_RECOVERY_FILE_DEST_SIZE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size           big integer 30G

Lets verify the location of recovery area. This is where the flashback logs are stored:

SQL> show parameter DB_RECOVERY_FILE_DEST

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/flash_recovery
                                                 _area
db_recovery_file_dest_size           big integer 30G

You can reset the location by simple command :

 alter system set db_recovery_file_dest= '/u01/app/oracle/flash_recovery/oraflash';


 Lets confirm the retention period :

SQL> show parameter db_flashback_retention_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     1440 

You can Change the retention period :

sql > alter system set db_flashback_retention_target=2880;  -- 2days

Note : This retention period is in minutes so 1440/60 = 24 hours. 




If you have a standby Database configured:
Activating flashback logging on the standby database Flashback operations, such as activating or accessing restore points, can only be undertaken in the MOUNT stage of the database. An active recovery will also first have to be canceled.


Manual method First cancel the recovery on the standby database:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Then activate Flashback:

 ALTER DATABASE FLASHBACK ON;

Then restart the recovery: 

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION

Deactivating To deactivate flashback mode, use the command:

 ALTER DATABASE FLASHBACK OFF; 

ORA-01113: file string needs media recovery

Question:  I get this error when starting my Oracle database.  What is the ORA-01113 error?

SQL> ALTER TABLESPACE sample
 ONLINE  2  ;
ALTER TABLESPACE sample
*
ERROR at line 1:
ORA-01113: file 27 needs media recovery
ORA-01110: data file 27: '/u02/oracle/oradata/ORCL/sample.dbf'


SQL> recover datafile '/u02/oracle/oradata/ORCL/sample.dbf';
Media recovery complete.
SQL> ALTER TABLESPACE sample ONLINE;
ALTER TABLESPACE sample ONLINE
*
ERROR at line 1:
ORA-01113: file 29 needs media recovery
ORA-01110: data file 29: '/u02/oracle/oradata/ORCL/SAMPLE10.dbf'


SQL> recover datafile '/u02/oracle/oradata/ORCL/SAMPLE10.dbf';
Media recovery complete.
SQL> ALTER TABLESPACE sample ONLINE;
ALTER TABLESPACE sample ONLINE
*
ERROR at line 1:
ORA-01113: file 30 needs media recovery
ORA-01110: data file 30: '/u01/app/QPDEV/SAMPLE11.dbf'


SQL> recover datafile '/u01/app/QPDEV/SAMPLE11.dbf';
Media recovery complete.
SQL> ALTER TABLESPACE sample ONLINE;

Tablespace altered.

The ORA-01113 error can be thrown when you begin to start Oracle with the "startup" command. or sometimes if u take Tablespace offline and try to bring it back online.

 The ORA-01113 has many causes: 

ORACLE_HOME_LISTNER is not SET, unable to auto-stop Oracle Net Listener

ORACLE_HOME_LISTNER is not SET, unable to auto-stop Oracle Net Listener

When you start or stop your oracle service using dbstart/dbshut scripts on Unix/Linux system and you may get the error ORACLE_HOME_LISTNER is not SET, unable to auto-stop Oracle Net Listener error.

[oracle@linux1bin]$ echo $ORACLE_SID

qptest

[oracle@linux1 bin]$ . dbshut

ORACLE_HOME_LISTNER is not SET, unable to auto-stop Oracle Net Listener

Usage: -bash ORACLE_HOME

Then you need to edit the “dbstart” & “dbshut” file, the should be located at $ORACLE_HOME\bin

Go through the file and find line

ORACLE_HOME_LISTNER=$1

and change to

ORACLE_HOME_LISTNER=$ORACLE_HOME

find the LAST_DDL_TIME change time of an Oracle object

SQL to find the LAST_DDL_TIME change time of an Oracle object in the database.

-- Get the name, type, date of change of the DDL of a user object.

select OBJECT_NAME, OBJECT_TYPE, LAST_DDL_TIME from dba_objects where
owner not in ('SYS','SYSTEM');