Home
 | 
Archives
 | 
Archives
Recent Article
All Archives
Topics
Comments

/




All topic articles are listed in this page.

Select the articles under each topic and enjoy reading.

General
 
Oracle Database is widely regarded as the industry standard RDBMS (Relational Database Management System) and is available on many platforms including Windows, Unix. This concise article briefs the Instance and Database and its various components.
Read more ...
 
Creating 11g database in 5 easy steps with bare minimum parameters and simple directory structure. In my opinion, it should take no more than 15 min.
Read more ...
 
This article explains how to manage the Talespaces and Datafiles.
Read more ...
 
Have you ever wondered how Oracle handles Undo and Redo information in the event of failures. We know that Undo data is stored in undo tablespaces or undo segments. But the point here is, the undo information is also protected by Redo. In other words, Oracle treats undo data just like a normal table segment data or index segment data. Which means, changes to undo information also causes redo generation.
Read more ...
 
Listener log file audits trail information which enables us to analyze network statistics, client connection requests, service registratione events and other useful information during troubleshooting of network related problems. The file is usually small and easy to diagnose. But in cases of very active databases (e.g. very active OLTP databases), it can grow very fast to several GBs.
Read more ...
 
DBNEWID is a database utility that can change the internal database identifier (DBID) and the database name (DBNAME) for an operational database. Changing the DBID of a database is a serious procedure.
Read more ...
 
Every Oracle Database has a control file, which is a small binary file that records the physical structure of the database.
Read more ...
 
Since Oracle defers writing to the datafile there is chance of power failure or system crash before the row is written to the disk. That’s why Oracle writes the statement in redo logfile so that in case of power failure or system crash oracle can re-execute the statements next time when you open the database.
Read more ...
 
Every Oracle Database must have a method of maintaining 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.
Read more ...
 
If your database is having a smaller redo logfile, then your instance may experience frequent log switches - conversely when having larger redo logfile, you run the risk of losing the data in the event an instance crash. Ideally, there shouldn't be more than five switches in an hour. But it all depends.
Read more ...
 
This document will guide you to establish a database link from Oracle Database server to SQL Server so as to fetch data from SQL Server into Oracle Database server.
Read more ...
 
In your environment if you are using Oracle Enterprise Manager (OEM), then running the tuning Advisor is pretty straightforward. If you do not have the OEM installed in your setup, nothing to worry. We could still run the SQL Tuning Advisor in few steps from the SQL Plus itself.
Read more ...
 
The most common cause of deadlocks is the normal row-level locking, which is relatively easy to find. But that's not the only reason. ITL shortage, Bitmap index locking, lack of FK index, Direct Path load, PK overlaps are some of the potential causes.
Read more ...
 
»  Why ASM?
Compared to standard volume managers and filesystems (either clustered or single system), ASM has a number of advantages. I can hardly think of any disadvatanges, maybe a couple.
Read more ...
 
DDL logging is enabled by setting init parameter enable_ddl_logging, which is same in previous Oracle releases.
Read more ...
 
By default SCAN would listen on to 1521. We can change it using SRVCTL command. It is straightforward and simple step.
Read more ...
 
In order for ASM to use disks, it needs to be able to identify the devices consistently and to have the correct ownership and permissions. In Linux we can use ASMLib, but is seen as an additional layer of complexity. Another alternative is Linux's native device manager 'udev' to perform these tasks. I will demonstrate it here in this article.
Read more ...
 
Installation
 
This articles describes the step-by-step installation procedure of Oracle 10g Database Server Release 2 (10.2.0.4) on RHEL 5.4 (X86_64) operating system.
Read more ...
 
This articles describes the step-by-step installation procedure of Oracle 11g Database Server Release 2 (10.2.0.4) on RHEL 5.4 (X86_64) operating system.
Read more ...
 
This articles describes the step-by-step installation procedure of Oracle 11g Database Server Release 2 (10.2.0.4) on CentOS 6.0 x64 operating system.
Read more ...
 
This article runs through the step by step installation of Oracle Grid Infrastructure and Cluster Database 11g Release 2 (2 nodes) on RedHat 5.4 using VirtualBox.
Read more ...
 
This article runs through the step by step installation of Oracle Grid Infrastructure and Cluster Database 12c Release 1 (2 nodes) on Oracle Linux 7.3 using VirtualBox.
Read more ...
 
Step by step installation guide of Oracle Restart 12c (Oracle Grid Infrastructure for Standalone server).
Read more ...
 
Step by step installation guide of Oracle Restart 12c (Oracle Grid Infrastructure for Standalone server) on Windows Server 2012 R2.
Read more ...
 
This article describes the installation of Oracle Enterprise Manager Cloud Control 12c Release 5 on Oracle Linux 6.6 (x86_64).
Read more ...
 
This article describes the installation of Oracle Enterprise Manager Cloud Control 13c Release 2 on Oracle Linux 7.3 (x86_64).
Read more ...
 
This article describes the installation of Oracle Database 12c Release 2 (12.2) 64-bit on Oracle Linux 7.2.
Read more ...
 
I will try to convert single instance stand alone database to RAC (3 nodes). My 3 nodes cluster is already installed and configured. I am demonstrating this in Grid Infrastructure 12c (12.1.0.2) version. There are several ways we can achieve this. But here I will try the traditional RMAN restore method.
Read more ...
 
In this article, I will attempt to convert single instance stand alone database to RAC (3 nodes) using DBCA. My 3 nodes cluster is already installed and configured. I am demonstrating this in Grid Infrastructure 12c (12.1.0.2) version. The single instance database datafiles are currently on the normal filesystem and durin the RAC migration will be converted to ASM.
Read more ...
 
This article describes the installation of Oracle Grid Infrastructure and Cluster Database 12c Release 2 (2 nodes) on Oracle Linux 7.3 using VirtualBox.
Read more ...
 
Maintenance
 
A physical standby database is an identical, block-for-block copy of the primary database and is kept in sync with the primary using media recovery. As redo gets generated on the primary database, it gets transferred to the standby database where an RFS process receives the primary redo and applies the change vectors directly to the standby database. A physical standby database is an excellent choice for disaster recovery.
Read more ...
 
Unlike an ordinary view which is only a stored select statement that runs whenever we use the view, a Materialized View (also known as snapshots) stores the resultset of the select statement in a typical table object in the local or remote database. Which means, materialized view will always hold the data in a table segment as would any other segment.
Read more ...
 
Let's assume due to some unkown reasons, the Grid Infrastructure installation got stuck in the middle, especially when running the root.sh script. Failures can be due to tons of reasons. Once our investigation to conclude the failure is confirmed, we decide to repeat the installation after properly cleaning up everything. In this article, let us try to clean up everything as if a brand new environment for a fresh installation is made available again.
Read more ...
 
How to move datafile from one diskgroup to another in few simple steps.
Read more ...
 
When a disk failure occurs for an ASM disk, behavior of ASM would be different, based on what kind of redundancy for the diskgroup is in use. If diskgroup has EXTERNAL REDUDANCY, diskgroup would keep working if you have redundancy at external RAID level. If there is no RAID at external level, the diskgroup would immediately get dismounted and disk would need a repair/replaced and then diskgroup might need to be dropped and re-created, and data on this diskgroup would require recovery.
Read more ...
 
I recently did an upgrade of an Oracle Grid Infrastructure 12c Release 1 (12.1.0.2.0) hosted on a RAC Virtual Box environment on my Mac to the latest release 12c Release 2 12.2.0.1.0 version. Oracle Grid Infrastructure 12.2 software is now available as a single image file for direct download and quicker installation.
Read more ...
 
By default AWR snapshot interval is set to 60 minutes and retention of snapshots is set to 8 days. For better and precise investigation of problems, it is recommended to use an interval of 15 minutes and retention of 31 days.
Read more ...
 
This is very important to keep the time of cluster nodes synchronized across the cluster. Time difference among nodes can cause issues, and sometime it can cause the node(s) restart. If you plan to use ctss (Cluster Time Synchronized Service), you would need to disable OS level NTP on all RAC nodes and then restart all nodes, or alternatively restart cluster services to have ctss start working for RAC nodes time synchronization.
Read more ...
 
Recently I encountered a space issue with the Oracle Home which was located under the root partition. Since this was a virtual box, I could easily add another disk and mount it. Then I had relocate the Oracle Home to the new added disk.
Read more ...
 
In this article, I will show you the way and methodology for installing DB PSU and OJVM PSU to a Single Instance 12c (12.1.0.2) Oracle Database.
Read more ...
 
Performance & Tuning
 
How index can be efficient even in a relatively smaller table having a handful rows that can fit in one data block as against a FULL TABLE SCAN which requires just one consistent get operation to read data from the table via a full table scan. Well, not necessarily...
Read more ...
 
We gather statistics to ensure that the optimizer chooses the optimal execution plans, but gathering statistics invalidates the parsed representation of the SQL statement and reparsing the statement post gather stats can make the optimizer choose an execution plan which is different (and less optimised) than the original plan.
Read more ...
 
Divide and conquer is what Oracle was thinking with this one. Table partitioning is about optimizing “medium selectivity queries”. The Oracle database has optimization techniques for high selectivity queries, with the use of indexes. If we need to process all data in a big table, we have to live with the fact it will take a while, but the engine will process the data as fast as possible. However, a medium selectivity query needs just a portion of the data, for instance a tenth. This is to much data for indexes, to little data for full table scans so the processing time might become rather long regarding the outcome.
Read more ...
 
Oracle continues to add new wait events as they add new functionality to the Oracle kernel. In this article, we will take a look at the Top Wait Events ranked by Total Wait Time which represent over 90% of all wait events in production databases in multiple industries.
Read more ...
 
Backup & Recovery
 
This guide will prepare you to run a simple RMAN shell script to perform a hot backup of the full database. In addition, you will learn few useful commands on how to list the RMAN binary backup files and on how to crosscheck if the backed up files are physically present on disk.
Read more ...
 
This article will demonstrate to rebuild a database using the rman backup into another server in a disaster recovery. Let us imagine that we have completely lost our server which was running the instance for some reason. And we have got a valid rman backup in a remote/safe location and a spare server which is already installed with the Oracle server software (10g).
Read more ...
 
This article demonstrates rebuilding the database using the rman backup into the same server. Let us imagine that accidentally someone removed all the datafiles in the ASM diskgroup including the controlfiles. And we have got a valid rman backup in the same server which we can use to restore. Also we have the recent archive logs (+FRA) generated after the last backup was taken.
Read more ...
 
A table called BEST_USER.CO_PROVIDER was truncated mistakenly by a user at around 10:40 on 13/12/2011. After little analyzing, we decided to perform a Tablespace Point-In-Time Recovery. The below steps were performed to restore the tablespace (TS_BEST_D & TS_BEST_X) until the timestamp 09:30 AM (until archivelog 636).
Read more ...
 
This article is the first in a series to introduce various database recovery techniques using user managed hot backups and RMAN hot backups.
Read more ...
 
This article demonstrates the step by step procedure of restoring and recovering a RAC database to a different server (single instance).
Read more ...
 
To recover from a case of a dropped tablespace, the Tablespace Point In Time Recovery (TSPITR) method cannot be used. When we drop a tablespace, the controlfile will then no longer have any records of the tablespace which has been dropped. Attempts to use the RMAN RECOVER TABLESPACE would fail.
Read more ...
 
One of the amazing features of Oracle Database 12c is that now you can do a logical restore from a physical backup. Before 12c it was not possible to restore a table from a RMAN backup. This feature is amazing speciall when you have a large database and needs to restore a small table. One of the pre-requisites is you must have a Full Backup of the database and also archivelogs after the fully backup must be available.
Read more ...
 
High Availability
 
A physical standby database is an identical, block-for-block copy of the primary database and is kept in sync with the primary using media recovery. As redo gets generated on the primary database, it gets transferred to the standby database where an RFS process receives the primary redo and applies the change vectors directly to the standby database. A physical standby database is an excellent choice for disaster recovery.
Read more ...
 
A physical standby database is an identical, block-for-block copy of the primary database and is kept in sync with the primary using media recovery. As redo gets generated on the primary database, it gets transferred to the standby database where an RFS process receives the primary redo and applies the change vectors directly to the standby database. A physical standby database is an excellent choice for disaster recovery.
Read more ...
 
A physical standby database is an identical, block-for-block copy of the primary database and is kept in sync with the primary using media recovery. As redo gets generated on the primary database, it gets transferred to the standby database where an RFS process receives the primary redo and applies the change vectors directly to the standby database. A physical standby database is an excellent choice for disaster recovery.
Read more ...
 
This is a 11g feature wherein we can duplicate the primary database as standby database without any backups actually needed to create the standby.
Read more ...
 
From Oracle Database 11g onwards building a standby database has become a whole lot easier. With just one powerful RMAN command, the standby database can be created on another server without any backups.
Read more ...
 
We can easily convert a physical standby to a logical standby. The standby database will need to get the data dictionary from somewhere. The dictionary information should be put into the redo stream that comes from the primary. So, on the primary database, issue the following to build the LogMiner tables for dictionary.
Read more ...
 
A physical standby database is an identical, block-for-block copy of the primary database and is kept in sync with the primary using media recovery. As redo gets generated on the primary database, it gets transferred to the standby database where an RFS process receives the primary redo and applies the change vectors directly to the standby database. A physical standby database is an excellent choice for disaster recovery.
Read more ...
 
This article explains the step by step process of building the 12c R1 (12.1.0.2) 2 node(s) RAC primary to 2 node(s) RAC Physical Standby.
Read more ...
 
We know that the standby database is expected to be lagging behind the primary database (assuming this is an asynchronous non-real time apply). But what if the gap is significantly wide in terms of hours or days.
Read more ...
 
Synchronizing the standby and primary databases can be done by copying and applying the archived logs from the primary database but this process is quite time consuming as it will first apply both the COMMITED and the NON COMMITED transactions followed by rolling back uncommitted transactions. Employing incremental backups of the primary database containing changes since the standby database was last refreshed is a faster alternative.
Read more ...
 
Oracle Cluster Registry is a critical component of RAC. OCR records cluster configuration information. If it fails, the entire clustered environment for Oracle 11g RAC will be adversely affected and a possible outage may result if OCR is lost.
Read more ...
 
What if we lost the diskgroup +OCRDG where our OCR, Voting Disk, ASM spfile, mgmtDB and its spfile exist due to the underlying ASM disk failure.
Read more ...
 
In Oracle Clusterware 11g Release 2 and later an additional component related to the OCR called the Oracle Local Registry (OLR) is installed on each node in the cluster. The OLR is a local registry for node specific resources. The OLR is not shared by other nodes in the cluster. It is installed and configured when Oracle Clusterware is installed.
Read more ...
 
There may be situations when you (DBAs) will be required to shutdown and startup the physical standby database in the remote site for various reasons. Mostly for maintenance activity.
Read more ...
 
Consider a scenario your application/QA team wants to test the DR application using the most recent production data. Let us assume your standby database is already running in the DR site and is constantly in sync with the primary database.
Read more ...
 
In 12c, refreshing the physical standby has been dramatically simplified. Now you can use the RECOVER … FROM SERVICE command to synchronize the physical standby database with the primary database.
Read more ...
 
What if we have a Logical Standby Database and want to add/resize datafiles to the primary, while the Logical Standby DB uses an other directory structure than the Primary.
Read more ...
 
This article doesn't cover the installation of 12c RAC, instead it only focuses on extending my two nodes cluster to three nodes cluster. If you care about installing 12c RAC, check this blog Grid Infrastructure and Database 12c R1 Installation (2 Node RAC).
Read more ...
 
Deleting a node from a cluster does not remove a node’s configuration information from the cluster. The residual configuration information does not interfere with the operation of the cluster. Before starting, you can optionally delete the instance from the node to be c using 'dbca'.
Read more ...
 
Voting disks are important component of Oracle Clusterware. Clusterware uses voting disk to determine which nodes are members of the cluster. After ASM is introduced to store these files, these are called as VOTING FILES. Primary function of voting disks is to manage node membership and prevent SPLITBRAIN Syndrome in which 2 or more instances attempt to control the RAC database.
Read more ...
 
Switchover is a two way process in Oracle Dataguard and is a role reversal between the Primary database and one of its Standby databases. This way of role transition where the current Primary database starts behaving as a Physical Standby and on the other hand the current Physical Standby database starts behaving as a Primary database. Bystander standbys will receive redo from the new primary database.
Read more ...
 
Failover is a one way process where your primary database goes down due to some reasons and you convert your existing Physical Standby database to start behaving as Primary database.
Read more ...
 
From Oracle Database 11g onwards building a standby database has become a whole lot easier. With just one powerful RMAN command, the standby database can be created on another server without any backups. In this article, I will attempt to create RAC physical standby using RMAN's powerful Active Duplication.
Read more ...
1
2
3