Archived log generation volume and count report

Source :

V$ARCHIVED_LOG displays information about the archived logs generated by your database and haven’t yet aged out from your control file.
We are going to use this view to generate a report, displaying some useful information about it.

BLOCKS column stores archived log’s size in blocks, BLOCK_SIZE stores block size in bytes, so if we multiply them (BLOCKS*BLOCK_SIZE) we get its size in bytes.
ARCHIVED indicates whether the log is archived and DELETED whether RMAN has deleted it.

The following query displays per day the volume in MBytes of archived logs generated, deleted and of those that haven’t yet been deleted by RMAN.

Continue reading



Taking statistics and have them updated is a very important task on most Oracle databases, the CBO (Cost Based Optimizer) evaluates the cost of several Execution Paths, this cost is mainly based on physical and logical Disk IO, CPU use and network IO, if our stats are not being taken or aren’t updated there is a possibility for the CBO to take a wrong decision.

Make sure you have the system parameter STATISTICS_LEVEL set to TYPICAL or ALL (default=typical), this parameter indicates the level of the statistics to be taking for both, database and OS.
Statistics can be taken with only one command for the whole database, and we can use several options to improve the quality and the time it takes to collect them.

Let’s start with the basic recommendations:
1st.- Use the option GATHER_AUTO
2nd.- If possible take them in parallel
3rd.- Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE in the ESTIMATE_PERCENT parameter

Continue reading

Bigfile Tablespaces in Oracle

Bigfile tablespaces are tablespaces with a single large datafile (up to 4G blocks). In contrast normal or smallfile tablespaces can have several datafiles, but each is limited in size. The system default is to create a smallfile tablespace. The SYSTEM and SYSAUX tablespace types are always created using the system default type.
Bigfile tablespaces are supported only for locally managed tablespaces with automatic segment space management, with three exceptions: locally managed undo tablespaces, temporary tablespaces, and the SYSTEM tablespace
Advantages of using Bigfile Tablespaces:
By allowing tablespaces to have a single large datafile the total capacity of the database is increased. An Oracle database can have a maximum of 64,000 datafiles which limits its total capacity.It allows you to create a bigfile tablespace of up to eight exabytes (eight million terabytes) in size, and significantly increase the storage capacity of an Oracle database. Continue reading

How To Change Timezone on a CentOS 6 and 7 Easily

You can easily change timezone in CentOS Linux using the following methods.
CentOS timezone config files and directories

/usr/share/zoneinfo/ – The system timezone directory contains the files as per timezone name. For example, the file /usr/share/zoneinfo/America/New_York represents time zone for New York.
/etc/localtime – It is a symlink to the file localtime or to the correct timezone file in the system located in /usr/share/zoneinfo/ directory.
How do I see the current time zone?

Type the date command or the ls command:
$ date
$ ls -l /etc/localtime


To find list of all available time zones, run:
# timedatectl list-timezones
##*** Grep possible Asian timezones ***##
# timedatectl list-timezones | grep Asia

Sample outputs:

…. Continue reading