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

Switchover Activity and Revert Back Use DataGuard Broker in Oracle 19C

Switchover Activity:

Primary Database: pearl
Standby Database: muthu

Step 1. Check the Primary and Standby Status:

On Primary:

SQL> select thread#, max(sequence#) "Last Primary Seq# Generated" from v$archived_log v, v$database d where v.resetlogs_change# = d.resetlogs_change# group by thread# order by 1;

   THREAD# Last Primary Seq# Generated
---------- ---------------------------
         1                          27

On Standby:

SQL> select thread#, max(sequence#) "Last Primary Seq# Generated" from v$archived_log v, v$database d where v.resetlogs_change# = d.resetlogs_change# group by thread# order by 1; 

   THREAD# Last Primary Seq# Generated
---------- ---------------------------
         1                          27

Step 2: Connect to the broker

Connect to the broker using the dgmgrl utility on the primary database. For example, you can run the command "dgmgrl /" to connect to the default broker configuration.

[oracle@primary ~]$ dgmgrl sys/pwd
Welcome to DGMGRL, type "help" for information.
Connected to "pearl"
Connected as SYSDBA.

Step 3: Verify the configuration

Verify that the broker configuration is correct by running the "show configuration" command. This will display the current configuration settings for the broker.

DGMGRL> show configuration

Configuration - pearl

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

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 51 seconds ago)
In this configuration, we have Primary Database "PEARL" and the standby Database is "MUTHU".

Step 4: Prepare for the switchover

Prepare for the switchover by running the "validate database" command to ensure that both the primary and standby databases are ready for switchover.

Validate Primary Database

DGMGRL> validate database pearl;

  Database Role:    Primary database

  Ready for Switchover:  Yes

  Flashback Database Status:
    pearl:  Off

  Managed by Clusterware:
    pearl:  NO
    Validating static connect identifier for the primary database pearl...
    The static connect identifier allows for a connection to database "pearl".
Validate Standby Database

DGMGRL> validate database muthu;

  Database Role:     Physical standby database
  Primary Database:  pearl

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

  Flashback Database Status:
    pearl:  Off
    muthu :  Off

  Managed by Clusterware:
    pearl:  NO
    muthu :  NO
    Validating static connect identifier for the primary database pearl...
    The static connect identifier allows for a connection to database "pearl".

  Current Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (pearl)               (muthu)
    1         3                       2                       Insufficient SRLs

  Future Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (muthu)                (pearl)
    1         3                       0                       Insufficient SRLs
    Warning: standby redo logs not configured for thread 1 on pearl
Step 5: Initiate the switchover
Initiate the switchover by running the "switchover to " command in dgmgrl. This will start the switchover process and automatically failover the primary database to the standby database.

Step 5. Run the Switchover Command:

DGMGRL> switchover to muthu;
Performing switchover NOW, please wait...
Operation requires a connection to database "muthu"
Connecting ...
Connected to "muthu"
Connected as SYSDBA.
New primary database "muthu" is opening...
Operation requires start up of instance "pearl" on database "pearl"
Starting instance "pearl"...
Connected to an idle instance.
ORACLE instance started.
Connected to "pearl"
Database mounted.
Database opened.
Connected to "pearl"
Switchover succeeded, new primary is "muthu"

Step 6: Verify the switchover

After the switchover, verify that the new primary database is running by running the "show database" command in dgmgrl.

Current Primary muthu: connect with DGMGRL in current primary server

DGMGRL> show database pearl

Database - pearl

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

  Database Warning(s):
    ORA-16854: apply lag could not be determined
    ORA-16856: transport lag could not be determined
    ORA-16858: last communication time from redo source could not be determined

Database Status:
WARNING

DGMGRL> show database muthu;

Database - muthu

  Role:               PRIMARY
  Intended State:     TRANSPORT-ON
  Instance(s):
    muthu
      Error: ORA-16736: unable to find the destination entry of member "pearl" in V$ARCHIVE_DEST
      Warning: ORA-16728: consistency check for property RedoRoutes found ORA-16777 error
      Warning: ORA-16777: unable to find the destination entry of a member in V$ARCHIVE_DEST
      Warning: ORA-16728: consistency check for property LogXptMode found ORA-16777 error
      Warning: ORA-16777: unable to find the destination entry of a member in V$ARCHIVE_DEST
      Warning: ORA-16728: consistency check for property DelayMins found ORA-16777 error
      Warning: ORA-16777: unable to find the destination entry of a member in V$ARCHIVE_DEST
      Warning: ORA-16728: consistency check for property Binding found ORA-16777 error
      Warning: ORA-16777: unable to find the destination entry of a member in V$ARCHIVE_DEST
      Warning: ORA-16728: consistency check for property MaxFailure found ORA-16777 error
      Warning: ORA-16777: unable to find the destination entry of a member in V$ARCHIVE_DEST
      Warning: ORA-16728: consistency check for property ReopenSecs found ORA-16777 error
      Warning: ORA-16777: unable to find the destination entry of a member in V$ARCHIVE_DEST
      Warning: ORA-16728: consistency check for property RedoCompression found ORA-16777 error
      Warning: ORA-16777: unable to find the destination entry of a member in V$ARCHIVE_DEST

Database Status:
ERROR

Database Warnings

Sometimes you will face the below error, then you have to check the log_archive_dest_1 and log_archive_dest_2 parameter on the standby side and configure it properly.

Step 7: Reconfigure the environment

If necessary, reconfigure the environment to point to the new primary database.
For example, update connection strings, DNS entries, or load balancer settings to point to the new primary database.

reconfigure the dataguard parameters in new primary
like
standby logfile delete and recreate
other parameters also

Step 8: Start Recovery

After the switchover start the MRP process using the broker or manually.

Enable/Disable the MRP with Broker:

DGMGRL> EDIT DATABASE pearl SET STATE='APPLY-ON';
Succeeded.
Check MRP status using SQL command, if no row is selected that means MRP is disabled.

SQL> select inst_id, process, status from gv$managed_standby where process='MRP0';

   INST_ID PROCESS   STATUS
---------- --------- ------------
         1 MRP0      APPLYING_LOG
Disable with Broker
DGMGRL> EDIT DATABASE pearl SET STATE='APPLY-OFF';

Enable/Disable the MRP manually:

SQL> alter database recover managed standby database disconect from session;

SQL> alter database recover managed standby database cancel;

Finally we got:

Current Primary(muthu):

DGMGRL> show database muthu

Database - muthu

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

Database Status:
SUCCESS

Current standby pearl
DGMGRL> show database pearl

Database - pearl

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

Database Status:
SUCCESS

Revert Back:

On current primary(muthu):

dgmgrl sys/sys@muthu

DGMGRL> show configuration;
DGMGRL> switchover to pearl;

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