PostgreSQL installation on Windows

PostgreSQL is a powerful, open source object-relational database system with over 30 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance.
PostgreSQL is an advanced, enterprise-class, and open-source relational database system. PostgreSQL supports both SQL (relational) and JSON (non-relational) querying. PostgreSQL is a highly stable database backed by more than 20 years of development by the open-source community. PostgreSQL is used as a primary database for many web applications as well as mobile and analytics applications.

I will be showing installation of PostgreSQL on windows.
Following the steps will help you in the installation which is a very straight forward and easy.

ASM installation on Windows

This article also assumes you are using Oracle version 11g Release 2 -specifically, 11.2.0.4. That is the latest version of Oracle available for general free download at the time of writing, and if it’s not the version you are using, I suggest that you upgrade immediately. I should add that I am using and describing the 32-bit version of Oracle 11g, and have no idea whether the 64-bit version works similarly or not at all!

In this article, as well as simulating a 6GB ASM array (which will require 6GB of free disk space), I’m going to show you how to convert a pre-existing database that is stored on a traditional file system into one that uses ASM exclusively. That is an important capability of Oracle’s RMAN tool, and the instructions therefore apply equally to Linux and Unix users as to Windows ones. I will also show you how to get Enterprise Manager to administer and monitor your ASM array -and that again is something that applies equally to all ASM users, not just Windows ones.

Undo Corruption in Oracle

What is Undo ?

Undo records are used to:

Oracle Database creates and manages information that is used to roll back, or undo, changes to the database. Such information consists of records of the actions of transactions, primarily before they are committed. These records are collectively referred to as undo.

• Roll back transactions when a ROLLBACK statement is issued
• Recover the database
• Provide read consistency
• Analyze data as of an earlier point in time by using Oracle Flashback Query
• Recover from logical corruptions using Oracle Flashback features

When a ROLLBACK statement is issued, undo records are used to undo changes that were made to the database by the uncommitted transaction. During database recovery, undo records are used to undo any uncommitted changes applied from the redo log to the datafiles. Undo records provide read consistency by maintaining the before image of the data for users who are accessing the data at the same time that another user is changing it.

Let’s see how to handle the Undo Corruption Scenario.

Redo log corruption

What is a Redo log corruption?

In the Oracle RDBMS environment, redo logs comprise files in a proprietary format which log a history of all changes made to the database. Each redo log file consists of redo records. A redo record, also called a redo entry, holds a group of change vectors, each of which describes or represents a change made to a single block in the database.

For example, if a user UPDATEs a salary-value in a table containing employee-related data, the DBMS generates a redo record containing change-vectors that describe changes to the data segment block for the table. And if the user then COMMITs the update, Oracle generates another redo record and assigns the change a “system change number” (SCN).

 So whenever Oracle throws the following error, it means that Redo is corrupted.

Demystifying Oracle Data Block – 1

Oracle consists of various logical and physical structures, namely :

1. Oracle Datablock
2. Extents
3. Segments
4. Tablespace
5. Datafiles

At the finest level of granularity, Oracle stores data in data blocks (also called logical blocks, Oracle blocks, or pages). One data block corresponds to a specific number of bytes of physical database space on disk. You set the data block size for every Oracle database when you create the database. This data block size should be a multiple
of the operating system’s block size within the maximum limit. Oracle data blocks are the smallest units of storage that Oracle can use or allocate.

Oracle manages the storage space in the datafiles of a database in units called data blocks. A data block is the smallest unit of data used by a database. In contrast, at the physical, operating system level, all data is stored in bytes. Each operating system has a block size. Oracle requests data in multiples of Oracle data blocks, not operating system blocks.

Changing the Database name in Oracle

The following steps describe how to change the database name without changing the DBID.

Oracle Database Re-Organisation.

The idea is that we find out all tablespaces in which schema’s data is present, then we drop schema plus its related tablespace and recreate them, in the end, take an import which reduces fragmentation in the database, thereby improving performance.
After the Re-org Activity, the database performance increases and size reduces.
Below are the commands for Re-org Activity :
Schema considered for performing the Re-org is NEFT.
Defer the Dataguard

Taking an Export Backup

 C:\> expdp dumpfile=dumpfile_name.dmp logfile=logfile_name.log schemas=NEFT


 SQL> spool 'C:\ReOrg.txt'
 SQL> set pagesize 200

Oracle Patches

1.  PATCH :

Patches are periodic software updates released by Oracle. They contain bugs fixes only, and they must be applied on the software product to maintain a bug-free and improved version of the software.

2.   PATCHSET :

Patch Sets are new releases of the software product released by Oracle. They contain a collection of patches, important enhancements, and bug fixes. They are much larger in size and are released for all customers. They must be applied on the base or existing release of the software product to maintain an up-to-date version of the software.

Things to do during a Database Health Check

HEALTH CHECK :

The Monitoring of predefined events that generates a message or warning when a certain threshold has been exceeded. The database health check is required for the following reason:

1. Smooth running of production
2. Database performance
3. In Support of the SLA (service level agreement)

Generating Statspack, AWR and ADDM reports

STATSPACK

The Statspack package is a set of SQL, PL/SQL, and SQL*Plus scripts that allow the collection, automation, storage, and viewing of performance data. Statspack stores the performance statistics permanently in Oracle tables, which can later be used for reporting and analysis. The data collected can be analyzed using Statspack reports, which includes an instance health and load summary page, high resource SQL statements, and the traditional wait events and initialization parameters