Growth trend check: How much archives created per day/week or in an hour

-

COUNT OF ARCHIVE FILES AND SIZE OF THE REDO GENERATED BY DAY

SELECT A.*, 
       Round (A.count# * B.avg# / 1024 / 1024) Daily_Avg_Mb 
FROM   (SELECT To_char (first_time, 'YYYY-MM-DD') DAY, 
               Count (1)                          Count#, 
               Min (recid)                        Min#, 
               Max (recid)                        Max# 
        FROM   v$log_history 
        GROUP  BY To_char (first_time, 'YYYY-MM-DD') 
        ORDER  BY 1 DESC) A, 
       (SELECT Avg (bytes) AVG#, 
               Count (1)   Count#, 
               Max (bytes) Max_Bytes, 
               Min (bytes) Min_Bytes 
        FROM   v$log) B; 

COUNT OF ARCHIVE FILES AND SIZE OF THE REDO GENERATED EVERY HOUR

SELECT To_char (first_time, 'YYYY-MON-DD')                            day, 
       To_char (SUM (Decode (To_char (first_time, 'HH24'), '00', 1, 
                                                           0)), '99') "00", 
       To_char (SUM (Decode (To_char (first_time, 'HH24'), '01', 1, 
                                                           0)), '99') "01", 
       To_char (SUM (Decode (To_char (first_time, 'HH24'), '02', 1, 
                                                           0)), '99') "02", 
       To_char (SUM (Decode (To_char (first_time, 'HH24'), '03', 1, 
                                                           0)), '99') "03", 
       To_char (SUM (Decode (To_char (first_time, 'HH24'), '04', 1, 
                                                           0)), '99') "04", 
       To_char (SUM (Decode (To_char (first_time, 'HH24'), '05', 1, 
                                                           0)), '99') "05", 
       To_char (SUM (Decode (To_char (first_time, 'HH24'), '06', 1, 
                                                           0)), '99') "06", 
       To_char (SUM (Decode (To_char (first_time, 'HH24'), '07', 1, 
                                                           0)), '99') "07", 
       To_char (SUM (Decode (To_char (first_time, 'HH24'), '08', 1, 
                                                           0)), '99') "0", 
       To_char (SUM (Decode (To_char (first_time, 'HH24'), '09', 1, 
                                                           0)), '99') "09", 
       To_char (SUM (Decode (To_char (first_time, 'HH24'), '10', 1, 
                                                           0)), '99') "10", 
       To_char (SUM (Decode (To_char (first_time, 'HH24'), '11', 1, 
                                                           0)), '99') "11", 
       To_char (SUM (Decode (To_char (first_time, 'HH24'), '12', 1, 
                                                           0)), '99') "12", 
       To_char (SUM (Decode (To_char (first_time, 'HH24'), '13', 1, 
                                                           0)), '99') "13", 
       To_char (SUM (Decode (To_char (first_time, 'HH24'), '14', 1, 
                                                           0)), '99') "14", 
       To_char (SUM (Decode (To_char (first_time, 'HH24'), '15', 1, 
                                                           0)), '99') "15", 
       To_char (SUM (Decode (To_char (first_time, 'HH24'), '16', 1, 
                                                           0)), '99') "16", 
       To_char (SUM (Decode (To_char (first_time, 'HH24'), '17', 1, 
                                                           0)), '99') "17", 
       To_char (SUM (Decode (To_char (first_time, 'HH24'), '18', 1, 
                                                           0)), '99') "18", 
       To_char (SUM (Decode (To_char (first_time, 'HH24'), '19', 1, 
                                                           0)), '99') "19", 
       To_char (SUM (Decode (To_char (first_time, 'HH24'), '20', 1, 
                                                           0)), '99') "20", 
       To_char (SUM (Decode (To_char (first_time, 'HH24'), '21', 1, 
                                                           0)), '99') "21", 
       To_char (SUM (Decode (To_char (first_time, 'HH24'), '22', 1, 
                                                           0)), '99') "22", 
       To_char (SUM (Decode (To_char (first_time, 'HH24'), '23', 1, 
                                                           0)), '99') "23" 
FROM   v$log_history 
GROUP  BY To_char (first_time, 'YYYY-MON-DD') 
ORDER  BY day DESC; 

Comments

Post a Comment

Popular posts from this blog

Installation of Oracle10g on LINUX

-
Table of Contents 1.  Pre-installation Tasks for the ORACLE 10g Database. 2. Installing the ORACLE 10g Database. 3. Post-installation tasks for the ORACLE 10g Database. 4. Testing the installation.  Pre-installation Tasks for the ORACLE 10g Database. Before starting the oracle 10g installation ,several parameters must be checked. To perform these checks login as the root user or switch user to root using the command su – root. Checking Memory and Swap space. For successful installation of Oracle 10g, the system is required to have atleast 512MB of RAM and 1 GB of Swap space.You can verify this using :-- grep MemTotal /proc/meminfo  grep SwapTotal /proc/meminfo Checking /tmp space. The Oracle Universal Installer requires 400 MB of free space in the /tmp directory. The command to check this is as follows:-- df /tmp Checking The Existence of following Softwares/RPM Packages.The following packages or later versions must be installed :--- · make-3.79.1· gcc-3.2.3-...
Read more

RMAN compression

-
RMAN Compression: There are 3 three types of compression by default: 1.       Null compression ·         Usages only till Oracle version 10.1 by default ·         RMAN skips the block that have never been used when backing up datafiles into backup sets. ·         E.g. let’s tablespace having once datafile of size 100MB and out of 100 MB only 50 is used. Then RMAN will backup only 50 MB. 2.       Unused block compression ·         Oracle version 10.2 forward, uses ‘Null’ and ‘unused block’ compression. ·         More compact backups of datafiles by skipping datafile blocks that are not currently used to store data. ·         In previous release, RMAN only supported NULL compression, which skipped space in ...
Read more

Database upgrade -11.2.0.1 to 11.2.0.4

-
Database upgrade -11.2.0.1 to 11.2.0.4 Complete Checklist for out-of-Place Manual Upgrade from Previous 11.2.0.N Version to the Latest 11.2.0.N Patchset (Doc ID 1276368.1) STEP 1) Download the 11.2.0.4 or RDBMS software. STEP 2) Install the latest 11.2.0.4 RDBMS Software into a new ORACLE_HOME. STEP 3 ) After installing the latest 11.2.0.4 RDBMS software, run Pre-upgrade tool (utlu112i.sql) on database (11.2.0.1). You need to review pre-upgrade output and fix if any issues before upgrading to the latest release of 11.2.0.4. STEP 4) Run  dbupgdiag.sql  script from the below My Oracle Support article to verify that all the components in dba_registry are valid and no invalid data dictionary objects exist in dba_objects. Note 556610.1  Script to Collect DB Upgrade/Migrate Diagnostic Information ( dbupgdiag.sql ) $ sqlplus / as sysdba sql> alter session set nls_language=’American’; sql> @ dbupgdiag.sql sql> exit If the  db...
Read more