Oracle SQL Optimization Tricks: Table Elimination and OR-Expansion Explained

-
Oracle optimizer's job is to find the most efficient way to execute SQL statements and provide the requested data to the client. We all know it is a complex code and gets improved as part of every product release. Today Oracle Optimizer is very evolved and has very rich useful features in-built in it (adaptive plans, dynamic statistics etc) Below is a basic optimization technique ,which may be there from 8i/9i, may be not relevant today , but still posting as sometimes below things may come up in some execution plan which you are checking. Table discard/elimination ========================= The query involves 2 tables - students and teachers , with student table having a FK relationship with the teachers table. CREATE TABLE STUDENTS ( STUDENT_ID NUMBER(10) NOT NULL, NAME VARCHAR2(50 BYTE) NOT NULL, TEACHER_ID NUMBER(10) ) ALTER TABLE STUDENTS ADD ( FOREIGN KEY (TEACHER_ID) REFERENCES TEACHERS (TEACHER_ID) ENABLE VALI...
Post a Comment

Installation and Configuration of ZDM(Zero Down-Time Migration).

-

In this blog we will only concentrate on installation and configuration of ZDM.

Pre-requisites:

1. Zero Downtime Migration service host should be a dedicated system, No GI/Oracle Home installed on the server.

2. Zero Downtime Migration software requires a standalone Linux host running on any one of the following platforms:

     Oracle Linux 7, Oracle Linux 8, or Red Hat Enterprise Linux 8.

Configuration of ZDM Server:

a. For this POC we will install ZDM Service on a EC2 instance in AWS. For creation      of instance we used terraform.

 OS used for this demo was "Red Hat Enterprise Linux release 9.6 (Plow)" (As this   was under free tier).

 Instance type was t2.micro (As this was under free tier). 

 Storage requirement :

   Minimum 500MB of swap space is required.

   Minimum 100GB of additional space is required for downloading ZDM installer/binaries and logs.  

b. After instance creation, below rpm packages need to be installed.

yum -y install gcc

yum -y install kernel-devel

yum -y install kernel-headers

yum -y install dkms

yum -y install make

yum -y install bzip2

yum -y install perl

yum -y install glibc-devel

yum -y install expect

yum -y install zip

yum -y install unzip

yum -y install libaio

yum -y install libnsl

yum -y install kernel-uek

c. create a group named "zdm" along user "zdmuser".

groupadd zdm

useradd –g zdm zdmuser

d. Create below directories and make zdmuser as the owner.

mkdir -p /u01/app/oraInventory/

mkdir -p /u01/app/oracle/zdmhome

chown -R zdmuser:zdm /u01

Installation of ZDM Software:

Login to EC2 instance via zdmuser and perform below steps:

a. Add below entries in your .bash_profile.

   export INVENTORY_LOCATION=/u01/app/oraInventory/

   export ORACLE_BASE=/u01/app/oracle/

   export ZDM_BASE=/u01/app/oracle/

   export ZDM_HOME=/u01/app/oracle/zdmhome/

   export ZDM_INSTALL_LOC=/u01/software/zdm21.5/

b. Download the latest ZDM software and upload it to ZDM server. 

    Once Downloaded, unzip the V1045330-01.zip file.









c. Once downloaded start the installation from zdmuser using below command:

    cd /u01/software/zdm21.5/

   ./zdminstall.sh setup oraclehome=/u01/app/oracle/zdmhome oraclebase=/u01/app/oracle ziploc=/u01/software/zdm21.5/zdm_home.zip

d. After successful installation you get below message -








e. Start the zdmservice using below command -

  /u01/app/oracle/zdmhome/bin/zdmservice start 








We can check the status of zdm service using 

/u01/app/oracle/zdmhome/bin/zdmservice status








Network Connectivity :

ZDM Service host must connect to both source and target database host via SSH. Password less ssh connectivity between both source and target database must be setup.

Now, we have a working ZDM Service host. We are ready to start the Database migration process.





Comments

Post a Comment

Popular posts from this blog

Row Locking and ITL in Oracle.

-
Image
Locking is very important aspect in a database. It is must to regulate access to (shared) resource so that one process/user doesn't change it while other is modifying the same. If we question how database may be locking rows/data, we usually may think there may be a process/manager which must be doing the task internally. The manager process maintains the information which process id wants/has to lock particular rows etc. Seems perfect for simple logical explanation, However if we consider this for OLTP systems, we can easily understand that such a single point of control may cause bottleneck. All processes competing for locks(acquire or release) on resources via a single manager will ensure that the application/workload cant scale at all. One of the major reason for Oracle being the top database is the locking mechanism which it implements. There is no such concept as lock manger etc. Oracle places the lock related information in the actual data block itself. Ex:  when a row in a ...
Read more

Oracle Zero Downtime Migration(Oracle ZDM) - Overview

-
Image
Oracle ZDM is a software solution which allows you to directly and seamlessly migrate your on-premises Oracle Databases to and between any Oracle-owned infrastructure, including Exadata Database Machine On-Premises, Exadata Cloud at Customer (ExaC@C), and Oracle Cloud Infrastructure. Oracle Zero Downtime Migration (ZDM) is designed using Oracle's best practices for high availability. It uses tools like Golden-Gate and Data Guard to keep your database available during migration, and relies on technologies such as RMAN, Data Pump, and database links to move data efficiently and safely — all with minimal downtime. Oracle ZDM offers four different migration methods: 1. Logical Online Migration using Golden-Gate.     In  logical online migration approach ZDM leverages Oracle Data Pump for the initial export and Oracle Golden-Gate for continuous data replication. Oracle Golden-Gate plays a critical role by capturing and applying real-time changes from the source to the tar...
Read more