RandyDBA | Database - Tips & Techniques

APPLIES TO: Oracle Database - Enterprise Edition - Version 9.0.1.0 and later Information in this document applies to any platform. ***Checked for relevance on 12-Nov-2013*** GOAL The goal of this note is to illustrate how to read/interpret the PGA Memory Advisory section in AWR and Statspack reports, and tune the PGA_AGGREGATE_TARGET instance parameter based on the findings. SOLUTION The preferred and easiest way of monitoring and setting the PGA_AGGREGATE_TARGET instance parameter is by examining the 'PGA Memory Advisory' section in an AWR or Statspack report. PGA Memory Advisory for DB: AAA Instance: aaa End Snap: 20555 Estd Extra Estd P Estd PGA   PGA Target    Size       W/A MB   W/A MB Read/      Cache  Overalloc      Est (MB)     Factr     Processed Written to Disk     Hit %  ...

Many Similarities, But Different? RDBMSs: There are allot of different relational database management systems (RDBMS) out there. You may have heard about Microsoft Access, Sybase, and MySQL but the two most famous and widely used are MS SQL Server and Oracle. Although there are many common things between the two RDBMSs there are also a number of key differences.  In this blog, I will take a look at several in peculiar, in the areas of their command language, how they manage transaction control and their management of database objects. LANGUAGE: Perhaps the foremost obvious distinction between the 2 RDBMS is the language they use. Though each systems use a version of Structured Query Language, or SQL, MS SQL Server uses Transact SQL, or T-SQL, which is an extension of SQL originally developed by Sybase and used by Microsoft. Oracle, meanwhile, uses PL/SQL, or Procedural Language/SQL. Both are different "flavors" or dialects of SQL and both languag...

I recently read an article that have an SQL prompt displyaing "user@hostname". As DBAs we frequently use SQL*Plus to perform daily administrative tasks. Often, you'll work on servers that contain multiple databases. Obviously, each database contains multiple user accounts. When connected to a database, you can run the following commands to verify information such as your username, database connection, and hostname:   SQL> show user;  SQL> select name from v$databae;   A more efficient way to determine your username and SID is to set you SQL prompt to display that information; for example,   SQL> SET SQLPROMPT '&_USER.@&_CONNECT_IDENTIFIER.> '   An even more efficient way to configure your SQL prompt is to have it automatically run the SET SQLPROMPT command when you log in to SQL*Plus.   Follow these steps to fully automate this:  1. Create a file named login.sql, and place in it the SET SQLPRO...

Many times Oracle DBA's are often faced with situation where shutting down the instance with shutdown immediate seems to take infinite time and gives an impression that the session in hung. In most of the cases this is not a hang. Hang can be defined as a scenario when few sessions/processes are waiting on some action and in turn blocking other session/processes to complete their tasks. Now the original sessions could be waiting because of resource issues or Oracle Bugs. Shutdown immediate can take long time to complete (appear to be hung) because of three reasons: Uncommitted transactions are being rolled back. SMON is cleaning temp segments or performing delayed block clean-outs. Processes still continue to be connected to the database and do not terminate. 1. Uncommitted transactions are being rolled back: This is the case when the message 'Waiting for smon  to disable tx recovery' is posted in the alert log after we issue shutdown immediate. There ...

Installing Oracle Database 12c After the Installation of Oracle Linux follow the following steps:- STEP ONE : Install the following packages ·         Login as root ·         Create a new file on the terminal using the command: #  vi rpm_packages.txt ·         Copy the packages below, paste and save the file. binutils-2.23.52.0.1-12.el7.x86_64 compat-libcap1-1.10-3.el7.x86_64 compat-libstdc++-33-3.2.3-71.el7.i686 compat-libstdc++-33-3.2.3-71.el7.x86_64 gcc-4.8.2-3.el7.x86_64 gcc-c++-4.8.2-3.el7.x86_64 glibc-2.17-36.el7.i686 glibc-2.17-36.el7.x86_64 glibc-devel-2.17-36.el7.i686 glibc-devel-2.17-36.el7.x86_64 ksh libaio-0.3.109-9.el7.i686 libaio-0.3.109-9.el7.x86_64 libaio-devel-0.3.109-9.el7.i686 libaio-devel-0.3.109-9.el7.x86_64 libgcc-4.8.2-3.el7.i686 libgcc-4.8.2-3.el7.x86_64 libstdc++-4.8.2-3.el7.i686 libstdc++-4.8.2-3....

Obtaining and Preparing Installation Media Download Oracle Linux installation media from the Oracle Software Delivery Cloud at http://edelivery.oracle.com/linux. This ISO image contains everything needed to boot a system and start an installation. However, to complete the installation, you must specify the location. However, to complete the installation, you must specify the location of the packages, for example on a local disk or an NFS share. The Red Hat Compatible Kernel (RHCK) is used during the installation. Both the Unbreakable Enterprise Kernel Release 3 (UEK R3) and the RHCK aer installed. After installation, the system boots the UEK R3 kernel by default. Installing Oracle Linux Manually To install Oracle Linux, you need to boot the system from the boot image . Use the graphical installation program to install Oracle Linux provided that the system has sufficient memory and the video card is supported. Otherwise, text-based installation. Use the up an...