Wednesday, September 18, 2013

My Oracle jobs are not running


Suddenly, in one day all my jobs are not running without showing any error.even though I have no trust of Oracle Scheduler but I wouldn't like to jump unless I solve the issue.

At the same time I was having an issue with EM so I needed to recreate it again.

SQL> show parameter job

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     0

What Make it 0?? I think from EM recreation

just by altering this value everything returned back as normal.

SQL> alter system set job_queue_processes=1000;

System altered.

SQL> show parameter job

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     1000

Reference:
Posted by at No comments:
Labels:

Tuesday, August 6, 2013

How to add ASM disk to a diskgroup

Login as grid user with sysasm privilege to be able to use administration statements.

$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.2.0 Production on Tue Aug 6 03:54:23 2013

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL> col path format a20
SQL>col name format a20
SQL>col failgroup format a15
SQL>set lines 150
SQL>select disk_number,failgroup,create_date,path,name from v$asm_disk where name like 'DATA%';

DISK_NUMBER FAILGROUP       CREATE_DA PATH                 NAME
----------- --------------- --------- -------------------- --------------------
          0 DATA_0000       07-SEP-10 /dev/rhdisk3         DATA_0000
          1 DATA_0001       07-SEP-10 /dev/rhdisk4         DATA_0001
          2 DATA_0002       07-SEP-10 /dev/rhdisk5         DATA_0002
          3 DATA_0003       07-SEP-10 /dev/rhdisk6         DATA_0003
   
4 rows selected.

SQL> alter diskgroup DATA add disk '/dev/rhdisk22' ;

Diskgroup altered.


As you already know '/dev/rhdisk22' must be share the same name on all nodes on your RAC environment.

SQL> col path format a20
SQL>col name format a20
SQL>col failgroup format a15
SQL>set lines 150
SQL>select disk_number,failgroup,create_date,path,name from v$asm_disk  where name like 'DATA%';

DISK_NUMBER FAILGROUP       CREATE_DA PATH                 NAME
----------- --------------- --------- -------------------- --------------------
          0 DATA_0000       07-SEP-10 /dev/rhdisk3         DATA_0000
          1 DATA_0001       07-SEP-10 /dev/rhdisk4         DATA_0001
          2 DATA_0002       07-SEP-10 /dev/rhdisk5         DATA_0002
          3 DATA_0003       07-SEP-10 /dev/rhdisk6         DATA_0003
          4 DATA_0004       06-AUG-13 /dev/rhdisk22        DATA_0004

once disk added oracle will do rebalance operation with other members, we can check that by:

SQL> col OPERATION format a20
SQL>col STATE format a20

SQL>select OPERATION,state from v$asm_operation;

OPERATION            STATE
-------------------- --------------------
REBAL                RUN


check again after a while

SQL>select OPERATION,state from v$asm_operation;

no rows selected
Posted by at No comments:
Labels:

Tuesday, July 2, 2013

Move Database Audit Trail to a Different Tablespace

1.      Create new tablespace to hold the audit trail.

SQL>create tablespace audit_data datafile 'E:\HISPL\oradata\HISPL\DATAFILE\audit_date.DBF' size 200M;




2.      Truncate sys.aud$ to accelerate movement to the new tablespace (Optional).
Ensure that the size of aud$ can fit the created tablespace otherwise try to delete some of it or just truncate it and that would accelerate the movement.




SQL> truncate table sys.aud$;





3.      Initializes the audit management infrastructure for all audit trails with one week.

SQL> BEGIN
IF NOT DBMS_AUDIT_MGMT.IS_CLEANUP_INITIALIZED
(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD)
THEN
dbms_output.put_line('Calling DBMS_AUDIT_MGMT.INIT_CLEANUP');
DBMS_AUDIT_MGMT.INIT_CLEANUP(
audit_trail_type => dbms_audit_mgmt.AUDIT_TRAIL_AUD_STD,
default_cleanup_interval => 24*7);
else
dbms_output.put_line('Cleanup for STD was already initialized');
end if;
end;
/

4.     Move the standard audit to the new tablespace.

Begin
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
audit_trail_type => dbms_audit_mgmt.AUDIT_TRAIL_AUD_STD,
audit_trail_location_value => 'AUDIT_DATA') ;
end;
/


5.     Specify a timestamp of 7 days ago for the standard audit trail.

Begin
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
last_archive_time => sysdate - 7);
end;
/

6.     Purge all records older than 7 days.

BEGIN
DBMS_AUDIT_MGMT.DROP_PURGE_JOB
(AUDIT_TRAIL_PURGE_NAME => 'Standard_Audit_Trail_PJ');
-- exception
-- when others then
-- null;
end;
/

BEGIN
DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
AUDIT_TRAIL_PURGE_INTERVAL => 24,
AUDIT_TRAIL_PURGE_NAME => 'Standard_Audit_Trail_PJ',
USE_LAST_ARCH_TIMESTAMP => TRUE );
END;
/

7.     Call DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP regularly to advance prompt the last archive TS, Audit Vault will do this for you automatically.

Create or replace procedure set_archive_retention
(retention in number default 7) as
begin
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
last_archive_time => sysdate - retention);
end;
/

8.     Schedule automatic advancement of the archive timestamp (Optionally).


Begin
  DBMS_SCHEDULER.disable('advance_archive_timestamp');
  DBMS_SCHEDULER.drop_job('advance_archive_timestamp');
-- exception
-- when others then
-- null;
end;
/

BEGIN
   DBMS_SCHEDULER.create_job (
   job_name => 'advance_archive_timestamp',
   job_type => 'STORED_PROCEDURE',
   job_action => 'SET_ARCHIVE_RETENTION',
   number_of_arguments => 1,
   start_date => SYSDATE,
   repeat_interval => 'freq=daily' ,
   enabled => false,
   auto_drop => FALSE);
   dbms_scheduler.set_job_argument_value
    (job_name =>'advance_archive_timestamp',
     argument_position =>1,
     -- one week, you can customize this line:
     argument_value => 7);
   DBMS_SCHEDULER.ENABLE('advance_archive_timestamp');
End;
/

BEGIN
    DBMS_SCHEDULER.run_job (job_name => 'advance_archive_timestamp',
    use_current_session => FALSE);
END;
/

Posted by at No comments:
Labels:

Tuesday, June 18, 2013

How to resize/recreate online redo log files


Oracle suggest to configure the size of redo log files to switch once an hour during presented load and they should no switch more frequently than every 20 minutes during peak time therefore, we need to resize our log files to  hold this redo information.
We cannot just resize our redo log files, we have to drop and recreate them so we require at least two groups of redo log files.
Let’s check how many log file do we per hour for past two days:

select to_char(first_time,'mm/dd/yy') logdate,
sum(decode(to_char(first_time,'hh24'),'00',1,0)) "MidN",
sum(decode(to_char(first_time,'hh24'),'01',1,0)) "1AM",
sum(decode(to_char(first_time,'hh24'),'02',1,0)) "2AM",
sum(decode(to_char(first_time,'hh24'),'03',1,0)) "3AM",
sum(decode(to_char(first_time,'hh24'),'04',1,0)) "4AM",
sum(decode(to_char(first_time,'hh24'),'05',1,0)) "5AM",
sum(decode(to_char(first_time,'hh24'),'06',1,0)) "6AM",
sum(decode(to_char(first_time,'hh24'),'07',1,0)) "7AM",
sum(decode(to_char(first_time,'hh24'),'08',1,0)) "8AM",
sum(decode(to_char(first_time,'hh24'),'09',1,0)) "9AM",
sum(decode(to_char(first_time,'hh24'),'10',1,0)) "10AM",
sum(decode(to_char(first_time,'hh24'),'11',1,0)) "11AM",
sum(decode(to_char(first_time,'hh24'),'12',1,0)) "Noon",
sum(decode(to_char(first_time,'hh24'),'13',1,0)) "1PM",
sum(decode(to_char(first_time,'hh24'),'14',1,0)) "2PM",
sum(decode(to_char(first_time,'hh24'),'15',1,0)) "3PM",
sum(decode(to_char(first_time,'hh24'),'16',1,0)) "4PM",
sum(decode(to_char(first_time,'hh24'),'17',1,0)) "5PM",
sum(decode(to_char(first_time,'hh24'),'18',1,0)) "6PM",
sum(decode(to_char(first_time,'hh24'),'19',1,0)) "7PM",
sum(decode(to_char(first_time,'hh24'),'20',1,0)) "8PM",
sum(decode(to_char(first_time,'hh24'),'21',1,0)) "9PM",
sum(decode(to_char(first_time,'hh24'),'22',1,0)) "10PM",
sum(decode(to_char(first_time,'hh24'),'23',1,0)) "11PM"
from v$log_history
where trunc(first_time)>sysdate-2
group by to_char(first_time,'mm/dd/yy')
order by 1

LOGDATE        MidN        1AM        2AM        3AM        4AM        5AM        6AM        7AM     8AM           9AM       10AM       11AM       Noon        1PM        2PM        3PM        4PM        5PM        6PM        7PM        8PM        9PM       10PM       11PM
-------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
06/16/13          8          7          7         10          8          7          7          7      10             7         13          8          7          8          7          7          8          7          7          7          7          7          7          8
06/17/13          7          7          7          8          8          7          7         10       7             7          9          7          8          7         10          0          0          0          0          0          0          0          0          0


Check the current size of redo log file:
SQL> SELECT a.group#, a.member, b.bytes/1024/1024 MByte FROM v$logfile a, v$log b WHERE a.group# = b.group#;

GROUP# MEMBER                         MBYTE
------ ------------------------------ -----
     4 D:\APP\FINPROD\REDO04.LOG         50
     3 D:\APP\FINPROD\REDO03.LOG         50
     2 D:\APP\FINPROD\REDO02.LOG         50
     1 D:\APP\FINPROD\REDO01.LOG         50

To determine the size of log files, simply take the median of switches per hour which is 10 multiply by 50MB to result 500MB.

Now let’s start our steps:


1.      Check the status of redo log files:

SQL> select group#, status from v$log;

GROUP# STATUS
------ ----------------
     1 INACTIVE
     2 INACTIVE
     3 INACTIVE
     4 CURRENT


Note:
In case you find any active status you would drop just issue:
SQL> alter system checkpoint global;

system altered.

2.      Drop all inactive log groups


SQL> alter database drop logfile group 4;

Database altered.


3.      Re-create dropped redo log group
SQL> alter database add logfile group 4 ('D:\APP\FINPROD\REDO04.LOG') size 500m reuse;

Database altered.

SQL> select group#,sequence#,bytes,archived,status from v$log;

GROUP#  SEQUENCE#      BYTES ARC STATUS
------ ---------- ---------- --- ----------------
     1      96646   52428800 NO  CURRENT
     2      96644   52428800 YES INACTIVE
     3      96645   52428800 YES INACTIVE
     4          0  524288000 YES UNUSED

4.      Switch redo log group to make the newly added group used (Current)


SQL> alter system switch logfile;

System altered.

SQL> select group#,sequence#,bytes,archived,status from v$log;

GROUP#  SEQUENCE#      BYTES ARC STATUS
------ ---------- ---------- --- ----------------
     1      96646   52428800 YES ACTIVE
     2      96644   52428800 YES INACTIVE
     3      96645   52428800 YES INACTIVE
     4      96647  524288000 NO  CURRENT


5.      Repeat the same steps to rebuild all redo log groups

SQL> alter database drop logfile group 2;

Database altered.

SQL> select group#,sequence#,bytes,archived,status from v$log;

GROUP#  SEQUENCE#      BYTES ARC STATUS
------ ---------- ---------- --- ----------------
     1      96646   52428800 YES ACTIVE
     3      96645   52428800 YES INACTIVE
     4      96647  524288000 NO  CURRENT

SQL> alter database add logfile group 2 ('D:\APP\FINPROD\REDO02.LOG') size 500m reuse;

Database altered.

SQL> select group#,sequence#,bytes,archived,status from v$log;

GROUP#  SEQUENCE#      BYTES ARC STATUS
------ ---------- ---------- --- ----------------
     1      96646   52428800 YES ACTIVE
     2          0  524288000 YES UNUSED
     3      96645   52428800 YES INACTIVE
     4      96647  524288000 NO  CURRENT

SQL> alter system switch logfile;

System altered.

SQL> select group#,sequence#,bytes,archived,status from v$log;

GROUP#  SEQUENCE#      BYTES ARC STATUS
------ ---------- ---------- --- ----------------
     1      96646   52428800 YES ACTIVE
     2      96648  524288000 NO  CURRENT
     3      96645   52428800 YES INACTIVE
     4      96647  524288000 YES ACTIVE

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database add logfile group 3 ('D:\APP\FINPROD\REDO03.LOG') size 500m reuse;

Database altered.

SQL> select group#,sequence#,bytes,archived,status from v$log;

GROUP#  SEQUENCE#      BYTES ARC STATUS
------ ---------- ---------- --- ----------------
     1      96646   52428800 YES ACTIVE
     2      96648  524288000 NO  CURRENT
     3          0  524288000 YES UNUSED
     4      96647  524288000 YES ACTIVE

SQL> alter system switch logfile;

System altered.

SQL> select group#,sequence#,bytes,archived,status from v$log;

GROUP#  SEQUENCE#      BYTES ARC STATUS
------ ---------- ---------- --- ----------------
     1      96646   52428800 YES ACTIVE
     2      96648  524288000 YES ACTIVE
     3      96649  524288000 NO  CURRENT
     4      96647  524288000 YES ACTIVE

SQL> alter system checkpoint global ;

System altered.

SQL> select group#,sequence#,bytes,archived,status from v$log;

GROUP#  SEQUENCE#      BYTES ARC STATUS
------ ---------- ---------- --- ----------------
     1      96646   52428800 YES INACTIVE
     2      96648  524288000 YES INACTIVE
     3      96649  524288000 NO  CURRENT
     4      96647  524288000 YES INACTIVE

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database add logfile group 1 ('D:\APP\FINPROD\REDO01.LOG') size 500m reuse;

Database altered.

SQL> select group#,sequence#,bytes,archived,status from v$log;

GROUP#  SEQUENCE#      BYTES ARC STATUS
------ ---------- ---------- --- ----------------
     1          0  524288000 YES UNUSED
     2      96648  524288000 YES INACTIVE
     3      96649  524288000 NO  CURRENT
     4      96647  524288000 YES INACTIVE

SQL> alter system switch logfile;

System altered.

SQL> select group#,sequence#,bytes,archived,status from v$log;

GROUP#  SEQUENCE#      BYTES ARC STATUS
------ ---------- ---------- --- ----------------
     1      96650  524288000 NO  CURRENT
     2      96648  524288000 YES INACTIVE
     3      96649  524288000 YES ACTIVE
     4      96647  524288000 YES INACTIVE

Posted by at No comments:
Labels:

Wednesday, June 5, 2013

Restoring Previous Versions of Statistics

In some cases newly collected statistics leads to sub-optimal execution plan and you need to revert older statistics back when you had good performance.

to determine the history of statistics operations perform at a database

SQL> select start_time from DBA_OPTSTAT_OPERATIONS where operation='gather_database_stats(auto)' order by start_time desc;

START_TIME
---------------------------------------------------------------------------
03-JUN-13 12.00.02.564157 AM +03:00
31-MAY-13 12.05.51.805614 PM +03:00
31-MAY-13 08.00.03.118222 AM +03:00
27-MAY-13 12.00.09.220355 AM +03:00
24-MAY-13 12.07.34.262882 PM +03:00
24-MAY-13 08.00.08.300615 AM +03:00
20-MAY-13 12.00.03.268371 AM +03:00
17-MAY-13 12.05.10.604709 PM +03:00
17-MAY-13 08.00.03.196964 AM +03:00
13-MAY-13 12.00.08.494310 AM +03:00
10-MAY-13 12.01.03.120240 PM +03:00
10-MAY-13 08.00.03.093785 AM +03:00
06-MAY-13 12.00.02.676424 AM +03:00

13 rows selected.

Particularly you can check history statistics of a table by:

SQL>select TABLE_NAME, STATS_UPDATE_TIME from dba_tab_stats_history where table_name='ORDERS' and owner='XXXXX' order by 2 desc;
03/06/2013 12:48:33.316820 AM +03:00
03/06/2013 12:35:31.853101 AM +03:00
31/05/2013 4:09:15.617337 AM +03:00
27/05/2013 12:24:27.985570 AM +03:00
24/05/2013 4:19:12.392677 AM +03:00
20/05/2013 12:31:51.253413 AM +03:00
17/05/2013 4:15:53.924409 AM +03:00
17/05/2013 4:12:11.516809 AM +03:00
13/05/2013 12:22:08.997571 AM +03:00
10/05/2013 4:14:30.823023 AM +03:00
06/05/2013 12:20:52.902622 AM +03:00


Retrieve the actual number of records on the table which have the bad execution plan:
SQL>
----------
SQL> select count(*) from orders;
  COUNT(*)
     -------------
      16323926
let's check the last number of rows on the current statistic
SQL> select num_rows from dba_tables  where table_name='ORDERS' and owner='XXXX';
  NUM_ROWS
----------
  16295541

Now restore the statistics of the table gatherd on 31st-May 
SQL> execute dbms_stats.restore_table_stats('XXXX','ORDERS','31-May-2013 4:09:15.617337 AM +03:00');
PL/SQL procedure successfully completed.

again check the number of rows on 31st-May statistics.

SQL> select num_rows from dba_tables  where table_name='ORDERS' and owner='XXXX';
  NUM_ROWS
----------
  16045590

Finally, If you observe vacillation on the execution plan you can lock this table :

SQL> exec DBMS_STATS.LOCK_TABLE_STATS('XXXX','ORDERS');

Posted by at No comments:
Labels: ,

Monday, June 3, 2013

How to drop a lost TEMPFILE

If you lost your tempfile due to drive fails or un-proper cloning:

SQL> select file#,status,name from v$tempfile;

     FILE# STATUS NAME    
--------------------------------------------------------
     1 ONLINE  E:\TEMP02.DBF
     2 ONLINE  E:\TEMP00

but when it comes to the dba_temp_files

SQL> select file_name,status from dba_temp_files;
select file_name,status from dba_temp_files
                             *
ERROR at line 1:
ORA-01187: cannot read from file  because it failed verification tests
ORA-01110: data file 201: 'E:\TEMP02.DBF'

Simply drope TEMP02.DBF as follow:

SQL> alter database tempfile  'E:\TEMP02.DBF' drop;

Database altered.

SQL> select file_name,status from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
STATUS
-------
E:\TEMP00
ONLINE


Problem Solved !!!
Posted by at No comments:
Labels:
Subscribe to: Posts (Atom)