How often are the archived logs generated?

SQL> set line 200 pages 80
SQL> alter session set nls_date_format='dd-mon-yy hh24:mi:ss';

Session altered.

Hourly report:


  select trunc(COMPLETION_TIME,'HH') Hour,thread# , count(*) Archives from v$archived_log
  where COMPLETION_TIME > sysdate-1
  group by trunc(COMPLETION_TIME,'HH'),thread#  order by 1;




HOUR                  THREAD#   ARCHIVES
------------------ ---------- ----------
07-mar-17 15:00:00          1          3
07-mar-17 16:00:00          1          6
07-mar-17 17:00:00          1          6
07-mar-17 18:00:00          1          9
07-mar-17 19:00:00          1         19
07-mar-17 20:00:00          1          5
07-mar-17 21:00:00          1         20
07-mar-17 22:00:00          1          8
07-mar-17 23:00:00          1          4
08-mar-17 00:00:00          1          4
08-mar-17 01:00:00          1         19
08-mar-17 02:00:00          1         15
08-mar-17 03:00:00          1         25
08-mar-17 04:00:00          1         10
08-mar-17 05:00:00          1          5
08-mar-17 06:00:00          1         12
08-mar-17 07:00:00          1          4
08-mar-17 08:00:00          1         16
08-mar-17 09:00:00          1          6
08-mar-17 10:00:00          1         20
08-mar-17 11:00:00          1          6
08-mar-17 12:00:00          1          6
08-mar-17 13:00:00          1         19
08-mar-17 14:00:00          1          7
08-mar-17 15:00:00          1          3

25 rows selected.

Daily report:

 select trunc(COMPLETION_TIME,'DD') Day,thread# , count(*) Archives from v$archived_log
  where COMPLETION_TIME > sysdate-10
  group by trunc(COMPLETION_TIME,'DD'),thread#  order by 1
/

 Another question would be, how many archived logs per day are generated, on average?

On average we generate 77 logs/day.

SQL> l

  select avg(count(*)) Archives from v$archived_log

  where COMPLETION_TIME > sysdate-100

  group by trunc(COMPLETION_TIME,'DD')

SQL> /

  ARCHIVES

----------

77.0566038



Update:
Querying v$log_history produces a nice table:

http://ajwatblog.blogspot.com/2011/04/redo-logs-switching-frequency.html


In case the archivelog mode is OFF:

select count(*),trunc(FIRST_TIME,'hh')
from v$log_history
group by trunc(FIRST_TIME,'hh')
order by 2

/

How to allocate an extent for a partitioned table?

Example:

SQL> alter table MY_PART_TABLE  modify partition PR198 ALLOCATE EXTENT;

Table altered.

The need for the above might arise after creating a partition table with deferred segment allocation.

How to allocate an extent for a partitioned table?

Example:

SQL> alter table MY_PART_TABLE  modify partition PR198 ALLOCATE EXTENT;

Table altered.

The need for the above might arise after creating a partition table with deferred segment allocation.

Manual recovery until specific time (not with RMAN)

RECOVER DATABASE UNTIL TIME '1992-12-31:12:47:30' USING BACKUP CONTROLFILE;

How to startup the oracle database automatically on the Linux box?

The script being called when the Linux box is starting up is:

/etc/rc.d/init.d/oracle

It has inside calls to "dbstart" and "dbshut"; both of these scripts exist in $ORACLE_HOME/bin.

How to format my SQL query? (oracle)

The output from querying v$sql.sql_text is not formatted.
A nice site that will turn this output into a a formatted SQL is:

      http://www.dpriver.com/pp/sqlformat.htm

Oracle 11g: how to validate that a specific archived redo log is corrupted or not? Hint: Log Miner to the rescue

Very simple, according to the MOS doc ID 1268590.1.

Example:

 SQL> execute DBMS_LOGMNR.ADD_LOGFILE('/u01/app/oracle/users/Florin/MYDB_1799337628_202043.arc');

PL/SQL procedure successfully completed.

SQL> execute DBMS_LOGMNR.START_LOGMNR(options => -
                                dbms_logmnr.dict_from_online_catalog);
>
PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> select count(1) from v$logmnr_contents;

  COUNT(1)
----------
   2874703

So there is no issue with the archived log :-)