How to find history of SGA/PAGA usage

For resource planning it is vital to know how the memory usage is changing during night/day  time. 

Most often the database load varies over time.  If you know the hourly usage of the SGA / PGA , you can better plan your resources or tune your instance.

You need to have diagnostic pack for your database to get this information.

Here is a query getting hourly  sga/pga memory usages of an Oracle instance. 

select sn.INSTANCE_NUMBER, sga.allo sga, pga.allo pga,(sga.allo+pga.allo) tot,

trunc(SN.END_INTERVAL_TIME,'mi') time
  from
(select snap_id,INSTANCE_NUMBER,round(sum(bytes)/1024/1024/1024,3) allo 
   from DBA_HIST_SGASTAT 
  group by snap_id,INSTANCE_NUMBER) sga
,(select snap_id,INSTANCE_NUMBER,round(sum(value)/1024/1024/1024,3) allo 
    from DBA_HIST_PGASTAT where name = 'total PGA allocated' 
   group by snap_id,INSTANCE_NUMBER) pga
, dba_hist_snapshot sn 
where sn.snap_id=sga.snap_id
  and sn.INSTANCE_NUMBER=sga.INSTANCE_NUMBER
  and sn.snap_id=pga.snap_id
  and sn.INSTANCE_NUMBER=pga.INSTANCE_NUMBER
order by sn.snap_id desc, sn.INSTANCE_NUMBER
;

Sample Output

----------------------

INSTANCE_NUMBER        SGA        PGA        TOT TIME
--------------- ---------- ---------- ---------- ---------
              1      69.75       .278     70.028 17-DEC-14
              1     69.751       .277     70.028 17-DEC-14
              1     69.753       .276     70.029 17-DEC-14
              1     69.764       .257     70.021 17-DEC-14
              1     69.798       .304     70.102 17-DEC-14
              1     69.798       .301     70.099 17-DEC-14
              1     69.798       .303     70.101 17-DEC-14
              1     69.798       .304     70.102 17-DEC-14
              1     69.798       .307     70.105 17-DEC-14
              1     69.798       .307     70.105 17-DEC-14
              1     69.798       .307     70.105 17-DEC-14
              1     69.799       .307     70.106 17-DEC-14
              1       69.8       .309     70.109 17-DEC-14
              1       69.8       .308     70.108 17-DEC-14
              1     69.805       .304     70.109 16-DEC-14
              1     69.806       .304      70.11 16-DEC-14
              1     69.791       .301     70.092 16-DEC-14
              1     69.793       .292     70.085 16-DEC-14
              1     69.793        .29     70.083 16-DEC-14
 

Measuring average tablespace growth per day based on previous 7 day statistics.

Run this query and see..This query will consider the sysdate -7 days and makes the avg growth per day calculations for the tablespaces.


SELECT b.tsname tablespace_name
, MAX(b.used_size_mb) cur_used_size_mb
, round(AVG(inc_used_size_mb),2)avg_increas_mb
FROM (
  SELECT a.days, a.tsname, used_size_mb
  , used_size_mb - LAG (used_size_mb,1)  OVER ( PARTITION BY a.tsname ORDER BY a.tsname,a.days) inc_used_size_mb
  FROM (
      SELECT TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY') days
       ,ts.tsname
       ,MAX(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) used_size_mb
      FROM DBA_HIST_TBSPC_SPACE_USAGE tsu, DBA_HIST_TABLESPACE_STAT ts
       ,DBA_HIST_SNAPSHOT sp, DBA_TABLESPACES dt
      WHERE tsu.tablespace_id= ts.ts# AND tsu.snap_id = sp.snap_id
       AND ts.tsname = dt.tablespace_name  AND sp.begin_interval_time > sysdate-7
      GROUP BY TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY'), ts.tsname
      ORDER BY ts.tsname, days
  ) A
) b GROUP BY b.tsname ORDER BY b.tsname
/

Sample output:


TABLESPACE_NAME                CUR_USED_SIZE_MB AVG_INCREAS_MB
------------------------------ ---------------- --------------
GWMONITOR                                 19.44              0
MEDIADMIN                                 74.56              0
MYVRNUSER                                  4.06              0
NPIDATA                                  5954.5              0
NSIDATA                                35847.67          27.43
NSIDATA01                             422923.03         266.93
NSIDATA03                                   .06              0
QUARTZ                                    42.75              0
SMRDATA                                10418.44              0
SYSAUX                                 14261.13           8.49
SYSTEM                                 10651.25          18.29
UNDOTBS1                                1109.88         -67.29
UNDOTBS2                                1383.88         -35.14
USERS                                     67.63              0
VPX                                     8399.31           1.65

HOW TO RECOVER DELETED DATAFILE IN ORACLE

Suppose you have accidentally removed a datafile from your production database? First thing, DON’T PANIC! There’s an easy way to recover deleted datafiles, for as long as your database remains up. The procedure below works on linux, however this method conceivably can work for other platforms.

This procedure will even work if your database is in NOARCHIVELOG mode.

First let’s create a tablespace.

SQL> create tablespace test datafile '/data/oracle/app/oracle/oradata/TESTDB/test1.dbf' size 10M;

Tablespace created.

SQL> conn test/test
 Connected.

SQL> create table test_table (key number(1));

Table created.

SQL> insert into test_table values (7);

1 row created.
  
SQL> commit;
 Commit complete.

SQL> select * from test_table;

KEY

----------
7

So we have a schema test with a simple table that was created in the test datafile (this was default tablespace of test user). Now we are going to remove this test datafile that is in the /data/oracle/app/oracle/oradata/TESTDB directory:

[oracle@localhost fd]$ rm –rf /data/oracle/app/oracle/oradata/TESTDB/test1.dbf

It’s really gone. Let’s test we can’t read from that table again:

[oracle@localhost fd]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Apr 23 10:50:23 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option

SQL> conn test/test

Connected.

SQL> select * from test_table;

KEY
----------
7

SQL> conn / as sysdba

Connected.

SQL> conn test/test

Connected.

SQL> select * from test_table;

select * from test_table
*
ERROR at line 1:
ORA-01116: error in opening database file 6
ORA-01110: data file 5: '/data/oracle/app/oracle/oradata/TESTDB/test1.dbf’
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

For a simple table already buffered, you can still retrieve the data from cache, but as soon as you are forced to attempt a read from disk you are going to error. Oops! That’s not good. So what are the options? flashback database, was my first thought when this happened for real, but that was not available, backups? In this situation still you can recover you datafile by below steps.

 For ARCHIVELOG DATABASE:

First find a PID of a process we know would have a File Descriptor open for the file we have just removed. Database writer is a good candidate:

[oracle@NVMBD1BZY150D00 TESTDB]$ ps -ef |grep dbw
oracle 43643 1 0 13:39 ? 00:00:00 ora_dbw0_TESTDB
We could do an lsof on this PID to confirm this is the case:

[oracle@NVMBD1BZY150D00 TESTDB]$ /usr/sbin/lsof -p 43643

 COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
oracle 43643 oracle rtd DIR 253,0 4096 2 /
oracle 43643 oracle DEL REG 0,4 31227916 /SYSV00000000
oracle 43643 oracle mem REG 253,0 5624 8323305 /lib64/libaio.so.1.0.1
oracle 43643 oracle mem CHR 1,5 4014 /dev/zero
oracle 43643 oracle mem REG 253,4 58949 14403358 /data/oracle/app/oracle/product/11.2.0/dbhome_1/lib/libnque11.so
oracle 43643 oracle mem REG 253,4 1544 14395587 /data/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/hc_TESTDB.dat
oracle 43643 oracle mem REG 253,0 65928 8323102 /lib64/libnss_files-2.12.so

oracle 43643 oracle mem REG 253,0 22536 8323474 /lib64/libdl-2.12.so

oracle 43643 oracle mem REG 253,4 153574 21258376 /data/oracle/app/oracle/product/11.2.0/dbhome_1/lib/libocrutl11.so
oracle 43643 oracle mem REG 253,4 3319072 21258375 /data/oracle/app/oracle/product/11.2.0/dbhome_1/lib/libocrb11.so
oracle 43643 oracle mem REG 253,4 1590995 21258374 /data/oracle/app/oracle/product/11.2.0/dbhome_1/lib/libocr11.so
oracle 43643 oracle mem REG 253,4 12755 14402512 /data/oracle/app/oracle/product/11.2.0/dbhome_1/lib/libskgxn2.so
oracle 43643 oracle mem REG 253,4 17319952 21258371 /data/oracle/app/oracle/product/11.2.0/dbhome_1/lib/libhasgen11.so
oracle 43643 oracle mem REG 253,4 161764 14402492 /data/oracle/app/oracle/product/11.2.0/dbhome_1/lib/libdbcfg11.so
oracle 43643 oracle mem REG 253,4 228765 21258373 /data/oracle/app/oracle/product/11.2.0/dbhome_1/lib/libclsra11.so
oracle 43643 oracle mem REG 253,4 7955322 21258383 /data/oracle/app/oracle/product/11.2.0/dbhome_1/lib/libnnz11.so
oracle 43643 oracle mem REG 253,4 1010297 14403515 /data/oracle/app/oracle/product/11.2.0/dbhome_1/lib/libskgxp11.so
oracle 43643 oracle mem REG 253,4 589359 21258391 /data/oracle/app/oracle/product/11.2.0/dbhome_1/lib/libcell11.so
oracle 43643 oracle mem REG 253,4 12259 14402220 /data/oracle/app/oracle/product/11.2.0/dbhome_1/lib/libodmd11.so
oracle 43643 oracle 0r CHR 1,3 0t0 4012 /dev/null
oracle 43643 oracle 1w CHR 1,3 0t0 4012 /dev/null
oracle 43643 oracle 2w CHR 1,3 0t0 4012 /dev/null
oracle 43643 oracle 3r CHR 1,3 0t0 4012 /dev/null
oracle 43643 oracle 4r CHR 1,5 0t0 4014 /dev/zero
oracle 43643 oracle 5r CHR 1,3 0t0 4012 /dev/null
oracle 43643 oracle 6r CHR 1,3 0t0 4012 /dev/null
oracle 43643 oracle 7u REG 253,4 1544 14395587 /data/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/hc_TESTDB.dat
oracle 43643 oracle 8r CHR 1,3 0t0 4012 /dev/null
oracle 43643 oracle 9r CHR 1,3 0t0 4012 /dev/null
oracle 43643 oracle 10r CHR 1,5 0t0 4014 /dev/zero
oracle 43643 oracle 11w REG 253,4 2396 14680217 /data/oracle/app/oracle/admin/TESTDB/bdump/alert_TESTDB.log

oracle 43643 oracle 12u REG 253,4 1544 14395587 /data/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/hc_TESTDB.dat

oracle 43643 oracle 13r REG 253,4 1101312 32800880 /data/oracle/app/oracle/product/11.2.0/dbhome_1/rdbms/mesg/oraus.msb

oracle 43643 oracle 14r DIR 0,3 0 16643132 /proc/43643/fd

oracle 43643 oracle 15r CHR 1,5 0t0 4014 /dev/zero

oracle 43643 oracle 16u REG 253,4 1544 14395587 /data/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/hc_TESTDB.dat

oracle 43643 oracle 17uR REG 253,4 24 14395352 /data/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/lkTESTDB

oracle 43643 oracle 20u IPv6 16643553 0t0 UDP *:61571

oracle 43643 oracle 21r REG 253,4 1101312 32800880 /data/oracle/app/oracle/product/11.2.0/dbhome_1/rdbms/mesg/oraus.msb

oracle 43643 oracle 256u REG 253,4 9846784 14532752 /data/oracle/app/oracle/oradata/TESTDB/control01.ctl

oracle 43643 oracle 257u REG 253,4 9846784 14532753 /data/oracle/app/oracle/oradata/TESTDB/control02.ctl

oracle 43643 oracle 258uW REG 253,4 104865792 14532757 /data/oracle/app/oracle/oradata/TESTDB/system.dbf

oracle 43643 oracle 259uW REG 253,4 104865792 14532758 /data/oracle/app/oracle/oradata/TESTDB/user04.dbf

oracle 43643 oracle 260uW REG 253,4 104865792 14532759 /data/oracle/app/oracle/oradata/TESTDB/sysaux.dbf

oracle 43643 oracle 261uW REG 253,4 524296192 14532760 /data/oracle/app/oracle/oradata/TESTDB/undo.dbf

oracle 43643 oracle 262uW REG 253,4 1048584192 14532761 /data/oracle/app/oracle/oradata/TESTDB/temp1.dbf

oracle 43643 oracle 263uW REG 253,4 1073750016 14532816 /data/oracle/app/oracle/oradata/TESTDB/test1.dbf (deleted)

Missed out lots of /dev/shm entries in the above. We can see on the last line, the /tmp/test01.dbf datafile and we see it’s marked as deleted. We can also see it has a file descriptor 263. You can also see this from the following directory:

[oracle@NVMBD1BZY150D00 TESTDB]$ ls -ltar /proc/43643/fd/

total 0

dr-xr-xr-x. 7 oracle oinstall 0 Dec 12 13:39 ..
dr-x------. 2 oracle oinstall 0 Dec 12 13:39 .
lr-x------. 1 oracle oinstall 64 Dec 12 15:20 9 -> /dev/null
lr-x------. 1 oracle oinstall 64 Dec 12 15:20 8 -> /dev/null
lrwx------. 1 oracle oinstall 64 Dec 12 15:20 7 -> /data/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/hc_TESTDB.dat
lr-x------. 1 oracle oinstall 64 Dec 12 15:20 6 -> /dev/null
lr-x------. 1 oracle oinstall 64 Dec 12 15:20 5 -> /dev/null
lr-x------. 1 oracle oinstall 64 Dec 12 15:20 4 -> /dev/zero
lr-x------. 1 oracle oinstall 64 Dec 12 15:20 3 -> /dev/null
lrwx------. 1 oracle oinstall 64 Dec 12 15:20 263 -> /data/oracle/app/oracle/oradata/TESTDB/test1.dbf (deleted)
lrwx------. 1 oracle oinstall 64 Dec 12 15:20 262 -> /data/oracle/app/oracle/oradata/TESTDB/temp1.dbf
lrwx------. 1 oracle oinstall 64 Dec 12 15:20 261 -> /data/oracle/app/oracle/oradata/TESTDB/undo.dbf
lrwx------. 1 oracle oinstall 64 Dec 12 15:20 260 -> /data/oracle/app/oracle/oradata/TESTDB/sysaux.dbf
lrwx------. 1 oracle oinstall 64 Dec 12 15:20 259 -> /data/oracle/app/oracle/oradata/TESTDB/user04.dbf
lrwx------. 1 oracle oinstall 64 Dec 12 15:20 258 -> /data/oracle/app/oracle/oradata/TESTDB/system.dbf
lrwx------. 1 oracle oinstall 64 Dec 12 15:20 257 -> /data/oracle/app/oracle/oradata/TESTDB/control02.ctl
lrwx------. 1 oracle oinstall 64 Dec 12 15:20 256 -> /data/oracle/app/oracle/oradata/TESTDB/control01.ctl
lr-x------. 1 oracle oinstall 64 Dec 12 15:20 21 -> /data/oracle/app/oracle/product/11.2.0/dbhome_1/rdbms/mesg/oraus.msb
lrwx------. 1 oracle oinstall 64 Dec 12 15:20 20 -> socket:[16643553]
l-wx------. 1 oracle oinstall 64 Dec 12 15:20 2 -> /dev/null
lrwx------. 1 oracle oinstall 64 Dec 12 15:20 17 -> /data/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/lkTESTDB
lrwx------. 1 oracle oinstall 64 Dec 12 15:20 16 -> /data/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/hc_TESTDB.dat
 lr-x------. 1 oracle oinstall 64 Dec 12 15:20 15 -> /dev/zero
 lr-x------. 1 oracle oinstall 64 Dec 12 15:20 14 -> /proc/43643/fd
 lr-x------. 1 oracle oinstall 64 Dec 12 15:20 13 -> /data/oracle/app/oracle/product/11.2.0/dbhome_1/rdbms/mesg/oraus.msb
 lrwx------. 1 oracle oinstall 64 Dec 12 15:20 12 -> /data/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/hc_TESTDB.dat
 l-wx------. 1 oracle oinstall 64 Dec 12 15:20 11 -> /data/oracle/app/oracle/admin/TESTDB/bdump/alert_TESTDB.log
lr-x------. 1 oracle oinstall 64 Dec 12 15:20 10 -> /dev/zero
l-wx------. 1 oracle oinstall 64 Dec 12 15:20 1 -> /dev/null
lr-x------. 1 oracle oinstall 64 Dec 12 15:20 0 -> /dev/null


Where we have used the PID of the dbw process.

Again we see the file is marked as deleted and again it’s FD 263. Now this is the idea Frits mentioned and we can start actually recovering the data:

[oracle@NVMBD1BZY150D00 TESTDB]$ cd /proc/43643/fd/

From the /proc/43643/fd/ directory I ran a cat on the File Descriptor number and sent it to another filename test1_tmp.dbf. Now you have recovered the data, you can switch the tablespace to use this datafile:

SQL>alter database datafile '/data/oracle/app/oracle/oradata/TESTDB/test1.dbf' offline;
Database altered.

SQL> alter database rename file '/data/oracle/app/oracle/oradata/TESTDB/test1.dbf' to '/data/oracle/app/oracle/oradata/TESTDB/test1_tmp.dbf';

Database altered.

SQL> alter database datafile '/data/oracle/app/oracle/oradata/TESTDB/test1_tmp.dbf' online;

alter database datafile '/data/oracle/app/oracle/oradata/TESTDB/test1_tmp.dbf' online
*
ERROR at line 1:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '/data/oracle/app/oracle/oradata/TESTDB/test1_tmp.dbf'

SQL> recover datafile '/data/oracle/app/oracle/oradata/TESTDB/test1_tmp.dbf'
Media recovery complete.

SQL> alter database datafile '/data/oracle/app/oracle/oradata/TESTDB/test1_tmp.dbf' online;

Database altered.

SQL> select * from test.test_table;

KEY 
----------
7

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!

How to enable Archivelog mode in Oracle database 11g

There are two types of logging modes in Oracle database

1. ARCHIVELOG :- In this type of logging whatever oracle writes in a redo log file related to transactions in database, saved to another location after a log file has been filled . This location is called Archive location. if database is in Archive log mode then in case of any disaster, we can recover our database upto the last commit and user don't have to reenter their data. Until a redo log file is not written to the Archive location it cannot be reused by oracle to write redo related data.

2. NOARCHIVELOG :- In this type of logging whatever oracle writes in a redo log file related to transactions in database must be overwritten when all the log files have been filled. In this type of logging we can recover our database upto the last consistent backup we have with us, after that users have to reenter their data

To change the Oracle database in ARCHIVELOG mode

1. Check current archive log mode

[oracle@NVMBD1BZY150D00 ~]$ sqlplus / as sysdba

 SQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 3 10:45:21 2014

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

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

sql> archive log list

Database log mode No Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 2296
Current log sequence 2299
OR

SQL> select log_mode from v$database;

LOG_MODE

------------
NOARCHIVELOG

2 Change archive log location and archive log format with new location and format.

If needed set the archive log destination where you want to save your archive logs whether to a single location or to multiple location. If this is not set then Oracle save archive log files in DB_RECOVERY_FILE_DEST location if set. If you have not set your DB_RECOVERY_FILE_DEST location then you have to set your archive location before changing your database to ARCHIVELOG mode.

SQL> alter system set log_archive_dest_1='LOCATION=/u01/archives/' scope=both;

System altered.

SQL> alter system set log_archive_format = 'archive_%t_%s_%r.arc' scope=spfile;                
System altered.

3. Check location has reflected or not.

SQL> archive log list;

Database log mode No Archive Mode
Automatic archival Enabled
Archive destination /u01/archives/
Oldest online log sequence 2296
Current log sequence 2299

4. Shut down the database

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

5. Startup in mount mode to change archive log mode

SQL> startup mount

ORACLE instance started.

 
Total System Global Area 535662592 bytes

Fixed Size 1375792 bytes

Variable Size 385876432 bytes
Database Buffers 142606336 bytes
Redo Buffers 5804032 bytes
Database mounted.

6. Enable archive log

SQL> alter database archivelog;

Database altered.

7. Open database

SQL> alter database open;

Database altered.

8. Apply log switch to check archives generating at new location.

SQL> alter system switch logfile;

System altered.

9.Check /verify archivelog mode.

SQL> SELECT LOG_MODE FROM V$DATABASE;

 
LOG_MODE
------------
ARCHIVELOG

OR

SQL> ARCHIVE LOG LIST

Database log mode Archive Mode

Automatic archival Enabled
Archive destination /u01/archives/
Oldest online log sequence 2296
Next log sequence to archive 2299
Current log sequence 2299
 
To change the Oracle database in NOARCHIVELOG mode
 
1. Shutdown your running database.

SQL> shut immediate

Database closed.
Database dismounted.
ORACLE instance shut down.
 
2. Start your database in MOUNT mode.

 SQL> startup mount

ORACLE instance started.

 
Total System Global Area 1025298432 bytes

Fixed Size 1341000 bytes
Variable Size 322963896 bytes
Database Buffers 696254464 bytes
Redo Buffers 4739072 bytes
Database mounted.

SQL> alter database noarchivelog;
 
Database altered.

 SQL> alter database open;

 
Database altered.
 
SQL> archive log list

Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/archives
Oldest online log sequence 1
Current log sequence 1

SQL> select name,log_mode from v$database;

NAME LOG_MODE

--------- ------------
ORCL NOARCHIVELOG

Database changed to NOARCHIVELOG mode

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!