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

DataGuard Broker Configuration in Oracle 19C

Step 1. Enable the DGMGRL Listener Both sides:

[oracle@server1 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = pearl)
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
      (SID_NAME = pearl)
    )
 (SID_DESC =
      (GLOBAL_DBNAME = pearl_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
      (SID_NAME = pearl)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.31.201)(PORT = 1521))
  )

ADR_BASE_LISTENER = /u01/app/oracle

Step 2. Check current state on DG Broker in Both Side:

show parameter DG_BROKER_START

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start                      boolean     FALSE

Step 3. Verify the GAP Both Sides:

SQL>  select status,instance_name,database_role,protection_mode from v$database,v$instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    PROTECTION_MODE
------------ ---------------- ---------------- --------------------
OPEN         pearl            PRIMARY          MAXIMUM PERFORMANCE

SQL>  select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
            10

Step 4. Run Both side and verify

alter system set dg_broker_start=true;

show parameter dg_broker_config;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      /u01/app/oracle/product/19.0.0
                                                 /dbhome_1/dbs/dr1pearl.dat
dg_broker_config_file2               string      /u01/app/oracle/product/19.0.0
                                                 /dbhome_1/dbs/dr2pearl.dat

SQL> alter system set LOG_ARCHIVE_DEST_2=''; (run Both database)

Step 5. Run DGMGRL in Primary Server:

[oracle@server1 dbs]$ dgmgrl
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Mon Feb 17 23:38:01 2025
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL>
DGMGRL>
DGMGRL> connect  sys/admin123@pearl
Connected to "pearl"
Connected as SYSDBA.

 Step 6. Create the configuration and add primary database:

DGMGRL> create configuration 'test1' as primary database is 'pearl' connect identifier is pearl;
Configuration "test1" created with primary database "pearl"
DGMGRL>
DGMGRL> show configuration;

Configuration - test1

  Protection Mode: MaxPerformance
  Members:
  pearl - Primary database

Fast-Start Failover:  Disabled

Configuration Status:
DISABLED

Step 7. Add the standby database in Dataguard broker:

DGMGRL>  add database 'muthu' as connect identifier is muthu maintained as physical;
Database "muthu" added

Step 8. Enable the Dataguard broker configuration:

DGMGRL> enable configuration;
Enabled.

DGMGRL> show configuration;

Configuration - test1

  Protection Mode: MaxPerformance
  Members:
  pearl - Primary database
    muthu - Physical standby database
      Warning: ORA-16809: multiple warnings detected for the member

Fast-Start Failover:  Disabled

Configuration Status:
WARNING   (status updated 5 seconds ago)

DGMGRL> show database muthu;

Database - muthu

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      (unknown)
  Apply Lag:          0 seconds (computed 40 seconds ago)
  Average Apply Rate: 10.00 KByte/s
  Real Time Query:    ON
  Instance(s):
    muthu

  Database Warning(s):
    ORA-16856: transport lag could not be determined
    ORA-16857: member disconnected from redo source for longer than specified threshold

Database Status:
WARNING

Step 9. set the lag threshold

DGMGRL>  edit database muthu set property TransportLagThreshold =0;
Property "transportlagthreshold" updated
DGMGRL>
DGMGRL> edit database muthu set property ApplyLagThreshold  =0;
DGMGRL> Property "applylagthreshold" updated

DGMGRL>
DGMGRL> show configuration;

Configuration - test1

  Protection Mode: MaxPerformance
  Members:
  pearl - Primary database
    muthu - Physical standby database
      Warning: ORA-16809: multiple warnings detected for the member

Fast-Start Failover:  Disabled

Configuration Status:
WARNING   (status updated 46 seconds ago)

DGMGRL> show database pearl;

Database - pearl

  Role:               PRIMARY
  Intended State:     TRANSPORT-ON
  Instance(s):
    pearl

Database Status:
SUCCESS

DGMGRL> show database muthu;

Database - muthu

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 50 seconds ago)
  Apply Lag:          0 seconds (computed 50 seconds ago)
  Average Apply Rate: 10.00 KByte/s
  Real Time Query:    ON
  Instance(s):
    muthu

  Database Warning(s):
    ORA-16857: member disconnected from redo source for longer than specified threshold

Database Status:
WARNING

Step 10. any threshold issue restart the standby check connections for primary and standby

DGMGRL> show database muthu;

Database - muthu

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      (unknown)
  Apply Lag:          (unknown)
  Average Apply Rate: (unknown)
  Real Time Query:    ON
  Instance(s):
    muthu

Database Status:
SUCCESS

DGMGRL> show configuration;

Configuration - test1

  Protection Mode: MaxPerformance
  Members:
  pearl - Primary database
    muthu - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 52 seconds ago

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