Skip to main content

Welcome to DBA Master – Database Tips, Tricks, and Tutorials

Welcome to DBA Master ! This blog is dedicated to all things related to database administration , SQL optimization , and performance tuning . Whether you're a beginner or a seasoned DBA, you'll find practical guides, troubleshooting tips, and real-world tutorials to help you work smarter with data. What to Expect: SQL performance tuning tips Indexing strategies Backup and recovery best practices High availability and replication techniques Database creation, configuration, and setup Monitoring queries and scripts for proactive performance management Migration guides across different database platforms Security essentials and best practices Recommended tools for DBAs Real-world error fixes and how to solve them Stay tuned — exciting content is coming soon. Feel free to bookmark and share: www.dbamaster.com ! Thanks for visiting!
Post a Comment

Enterprisedb (EDB-XDB) Replication Tool Installation & Configuration

xDB Replication Server can be used to implement replication systems based on either of two different 

replication models –single-primary(primary-to-secondary) replication or multi-primaryreplication.

xDB Replication Server supports for single-primary replication:

  • From Oracle to PostgreSQL
  • From Oracle to Advanced Server
  • From SQL Server to PostgreSQL
  • From SQL Server to Advanced Server
  • From Advanced Server to Oracle
  • From PostgreSQL to SQL Server
  • From Advanced Server to SQL Server
  • Between PostgreSQL and Advanced Server

xDB Replication Server supports for multi-primaryreplication:

  • Between PostgreSQL database servers
  • Between PostgreSQL database servers and Advanced Servers in PostgreSQL compatible   mode
  • Between Advanced Servers in PostgreSQL compatible mode
  • Between Advanced Servers in Oracle compatible mode

The following are the database product versions that may be used with xDB Replication Server.

  • PostgreSQL versions 9.2, 9.3, 9.4, 9.5, 9.6, 10, 11, and 12
  • Advanced Server versions 9.2, 9.3, 9.4, 9.5, 9.6, 10, 11, and 12
  • Oracle 10g Release 2 version 10.2.0.1.0 has been explicitly certified. Newer minor versions in the 10.2 line are supported as well.
  • Oracle 11g Release 2 version 11.2.0.2.0 has been explicitly certified. Newer minor versions in the 11.2 line are supported as well.
  • Oracle 12c version 12.1.0.2.0 has been explicitly certified. Newer minor versions in the 12.1 line are supported as well.
  • SQL Server 2008 version 10.50.1617.0 has been explicitly certified. Newer minor versions in the 10.50 line are supported as well.
  • SQL Server 2012 version 11.0.6020.0 has been explicitly certified. Newer minor versions in the 11.0 line are supported as well.
  • SQL Server 2014 version 12.0.5000.0 has been explicitly certified. Newer minor versions in the 12.0 line are supported as well.

Replication Concepts and Definitions:

xDB Replication Server is a software product that enables the implementation of a replication system. A replication system is software and hardware whose purpose is to make a copy of data from one location to another and to ensure the copied data is the same as the original over time.

xDB Replication Server applies the replication system concept to tables of Oracle, SQL Server, PostgreSQL, and Advanced Server database management systems.

Comparison of Single-Primary and Multi-Primary Replication:

Single-Primary Replication (SPR). 

                 Changes (inserts, updates, and deletions) to table rows are allowed to occur in a designated primary database. These changes are replicated to tables in one or more secondary databases. 

                 The replicated tables in the secondary databases are not permitted to accept any changes except from its designated primary database. (This is also known as primary-to-secondary replication.)

Multi-Primary Replication (MPR). 

                  Two or more databases are designated in which tables with the same table definitions and initial row sets are created. Changes (inserts, updates, and deletions) to table rows are allowed to occur in any database. Changes to table rows in any given database are replicated to their counterpart tables in every other database.

For a single-primary replication system, a variety of configurations are supported including:

  • Replication between PostgreSQL and Advanced Server databases (between products in either direction)
  • Replication from Oracle to PostgreSQL
  • Replication in either direction between Oracle and Advanced Server
  • Replication in either direction between SQL Server and PostgreSQL
  • Replication in either direction between SQL Server and Advanced Server

For multi-primary replication, the participating database servers in a given multi-primary replication system must be of the same type:

  • PostgreSQL database servers
  • PostgreSQL database servers and Advanced Servers operating in PostgreSQL compatible mode
  • Advanced Servers operating in PostgreSQL compatible mode
  • Advanced Servers operating in Oracle compatible mode

Snapshot and Synchronization Overview:

xDB Replication Server performs two different types of replications. These two main types are called snapshot replication and synchronization replication.

snapshot replication, all existing rows in the target tables are deleted using the database system’s TRUNCATE command. 

The tables are then completely reloaded from the source tables of the publication.

synchronization replication, only the changes (inserts, updates, and deletions) to the rows in 

the source tables since the last replication are applied to the target tables.

How to Download XDB Replication Tool:

1.goto https://www.enterprisedb.com/ and click Downloads 

2.click Replication server next click os you are get xdbreplicationserver-6.2.10-1-linux-x64.run

Installation:

[root@server1 tools]# ./xdbreplicationserver-6.2.10-1-linux-x64.run //open gui mode

  • Choose language clock ok button.
  • Accept the agreement and click next button.
  • Choose installation directory and click next button.
  • Select the all components(replication console,publication server,subscription server) and click next button.
  • Type admin user username,password and click next button.
  • Type publication server port number(Example: port 9051) and click next button.
  • Type subscription server port number(Example: port 9052) and click next button.
  • Type os username and click next button after installation complete click finish button.

XDB Replication Tool bin path:

/opt/PostgreSQL/EnterpriseDB-xDBReplicationServer/bin/

XDB Replication Tool ojdbc file path:

/opt/PostgreSQL/EnterpriseDB-xDBReplicationServer/lib/jdbc/

Run XDB Replication Tool:

1.Click Applications Menu for linux os

2.Choose Postgres Plus Add-ons and click xDB Replication Console

Open GUI Mode:

1.Right click Replication server and click Register Publication Server

2.enter publication server Host,port,username,password and click Register button

Example:

3.Right Click SMR and click add Database

4.Select Publication database type and type server information.

Example:

5.Right click pubserver db and click create Publication

Example:

Type publication name and select Publication table(must Primary key Tables)

 Example:

6.Right Click Replication Servers and click Register Subscription Server

7.enter Subscription server Host,port,username,password and click Register button

Example:

8.Right click Subscription server and click add database

Example:

9.Right click subscription db and click Create Subscription

Type subscription name , publication server information and click create button

Example:

10:Right Click subscription name(sub) and click snapshot

Example:

11.click Snapshot button

Example:

Configure Schedule Replication:

Right click subscription name (sub) and click Configure Schedule option

choose type of task (example: Synchronize) and choose perform this task(example: Continously)

and click next button and type seconds 

Example:

Select repeat interval (in seconds) for this task 10 

click next button and click finish button


Labels:

Comments

Post a Comment

Popular posts from this blog

Oracle Database 19C Performance Tunning - PART 1

Advantages: 1. Improved Query Performance •    Optimized SQL execution plans lead to faster query response times. •    Reduces unnecessary full table scans and improves indexing strategies. •    Parallel execution tuning speeds up large data processing tasks. 2. Better Resource Utilization •    Efficient use of CPU, memory, disk I/O, and network resources. •    Reduces contention on Redo Logs, Undo Tablespaces, and Buffer Cache. •    Helps in load balancing across multiple instances in RAC (Real Application Clusters). 3. Increased System Scalability •    Ensures that the database can handle a growing number of users and transactions. •    Proper tuning allows scaling without degrading performance. •    Optimized parallel processing ensures better performance on multi-core servers. 4. Lower Infrastructure Costs •    Reduces the need for add...
Post a Comment
Read more

Oracle RMAN Backup And Restore

RMAN: (Oracle 8) RMAN (Recovery Manager) is a utility provided by Oracle Database to perform backup, restore, and recovery operations. It is a command line tool. Features of RMAN in Oracle 19c Comprehensive Backup Capabilities: Full and incremental backups. Block-level backups for efficient data storage. Archived redo log backups. Fast Recovery Area (FRA) integration for centralized backup storage. Efficient Recovery Options: Point-in-time recovery (PITR). Complete and incomplete recovery. Flashback database capabilities for quick undo of changes. Multitenant Database Support: RMAN fully supports container databases (CDBs) and pluggable databases (PDBs). Provides flexibility to back up and recover individual PDBs or entire CDBs. Automatic Space Management: Manages disk space in the FRA. Automatically deletes obsolete backups and archived logs. Data Deduplication and Compression: Backup optimization through block-level deduplication. Built-in compression algorithms to reduce storage req...
Post a Comment
Read more

Oracle 19c Database Software Installation in OEL8

 Pre-requisites for OS level:            Set the static IP Address     Disable the Firewall (systemctl stop firewalld & systemctl disable firewalld)     set SELINUX=permissive on /etc/selinux/config  ##Need to restart the server use init 6 Oracle Installation Pre-requisites Methods     Automatic Setup     Manual Setup      Automatic requisites Setup: (avoid step 1 to step 5): dnf install -y oracle-database-preinstall-19c Install the dependencies: curl -o oracle-database-preinstall-19c-1.0-2.el8.x86_64.rpm https://yum.oracle.com/repo/OracleLinux/OL8/appstream/x86_64/getPackage/oracle-database-preinstall-19c-1.0-2.el8.x86_64.rpm dnf -y localinstall oracle-database-preinstall-19c-1.0-2.el8.x86_64.rpm Manual Setup: step 1: Add the karenl parameters and values vi /etc/sysctl.conf     fs.file-max = 6815744 kernel.sem = 250 32000 100 128 kernel....
Post a Comment
Read more