Wednesday, May 27, 2015

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';

Wednesday, May 20, 2015

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>

Thursday, May 14, 2015

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.

Monday, May 4, 2015

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;