Learning PostgreSQL - Architecture

I have started learning a new database, i.e. PostgreSQL, which is a powerful and open source relational database system. 

This is something new that I am trying, so I will start this series where I will try to publish a post every week with what I learned. Basically, these posts are nothing but notes that I am taking during the learning journey of PostgreSQL database.

This can also help those who are also in the process of learning this database system, and to those who already learned PostgreSQL, it can be a refresher.

Without wasting more time in introduction, let's start with the Architecture of the PostgreSQL. For Oracle DBAs (someone like me 😉), I will try to compare it with Oracle terminology so that it may help or speedup the learning.

Recover the Data without Backup !

So I had one interview, wherein got below question which left me confused. As per interviewer there is a solution but at that time I was not aware of the solution.

The question goes like...

There is a database which is having size in TBs and accidentally one of the developer deleted all rows of a business critical table and committed the changes. The table is not huge, it was not having many rows at that time. There is no flashback enabled. Now you've to recover deleted rows. So what would you do to recover the data in minimal time?

Recovering from removed/deleted datafile

In this post will discuss on how to recover from the scenario where someone somehow deleted any datafile. But before proceeding make sure you have a backup of the database, and the archive logs from the backup till date. So to recover from that scenario we must create that scenario.

For creating scenarios follow this:

• Make sure you’ve a Permanent Tablespace which is not that important (In Test Env). If you don’t want to use any existing tablespace then create a new tablespace to take it to this scenario.

SYS@DBACODE > CREATE TABLESPACE USERS DATAFILE '/oracle_11204/oracle/oradata/dbacode/datafile/users01.dbf' SIZE 60M;

Tablespace created.

Create a Linux / Unix Mount Point

In this post we will see how to create a Mount Point in Linux. For this post I'll be using Oracle Linux 6.4 on Virtual Box. This tutorial can be used for any UNIX, but before doing to actually on Live Machine, first use it in Test Machine and if everything is fine do it on Live Machine.

If you are using Linux on VMWare, the step to add storage will be somewhat different but rest of it will be same.

Follow below steps in order to create a mount point on Linux –

• Add new SATA disk(s) in Virtual Machine.
  Select the Machine where the Linux is installed (In this case dbrhel)
  Machine Setting -> Storage -> Controller:SATA -> Add Disk
  Provide the required details.
  

Scenario - Archive Destination Full

This post will discuss what action you can take if your Archive Destination is full. There are many ways to solve this scenario but I found this solution very easy to understand and it is working (in my environment.)

So lets get started. I will now tell you how to simulate so that your archive destination will be full. This is very simple technique, there is nothing complicated in this simulation just follow the steps as described.

rlwrap for Command Line History and Editing in SQL*Plus and RMAN

The rlwrap (readline wrapper) utility provides a command history and editing of keyboard input for any other command. This is comes in handy when we are using sqlplus or rman on linux, which don't come with command line history feature.

This post will explain how to install and configure rlwrap for sqlplus and rman.

Start Oracle Database without SPFILE / PFILE.

This post will explain how can we can start an Oracle Database Instance in nomount mode, for retrival of spfile from the backup sets.

This is called as dummy instance where you don't have to define any SGA components, Oracle will take care of all the SGA related components.

So lets start, I have attached a demo just go through it.

Difference between Recovery Window & Backup Window

Recovery Window :
A recovery window is a period of time that begins with the current time and extends backward in time to the point of recoverability. The point of recoverability is the earliest time for a hypothetical point-in-time recovery, that is, the earliest point to which you can recover following a media failure.
To configure a retention policy based on recovery window, use

 CONFIGURE RETENTION POLICY TO RECOVERY WINDOW no DAYS;  

Silent Installation of Oracle Database Software 11gR2

Before proceeding I am assuming that you've completed Pre-Installation tasks for Oracle Database Software Installation. If you have not completed then do it before proceeding, click here to goto that page.

Silent Installation is an alternative for GUI installation. It's very useful method when you want to prepare standard installation using shell scripts. A response file can be use to provide all the required information for the installion, so no user interaction is required. 

There are 3 response files which can used, for now we will use db_install.rsp to install database software only. So now download oracle database software from Oracle Official site and unzip. Now locate the unzipped files and locate response folder so that we can get db_install.rsp, copy this file to a temporary location and edit it.