In this guide, I will show you how to back up and restore MySQL or MariaDB databases using the mysqldump command line from the terminal.

MySQL Backup Strategies

• Physical Backups: Directly copying the database files. Fast recovery but requires downtime.
• Logical Backups: Extracting data as SQL statements, allowing for portability but slower to restore.
• Incremental Backups: Only saving changes since the last backup, reducing time and storage needs.
• Automation: Using tools like cron for regular and consistent backups without manual intervention.
• Verification: Regularly checking backups for integrity to ensure they are usable.
• Recovery Testing: Regularly testing the recovery process to ensure minimal downtime during actual data loss.

Here’s an illustrated image representing MySQL backup strategies.

Backup a Single MySQL Database

To back up a single MySQL database, utilize the command line. Here’s how you can perform the backup using the root user and save it to a specific folder:

mysqldump -u root -p LINUXOPERATINGSYSTEM > /tmp/LINUXOPERATINGSYSTEM-$(date +%Y%m%d).sql

Replace DATABASE_NAME with your actual database name and /path/to/backup/ with the actual path where you want to save the backup.

Restore a Single MySQL Database

To restore a single MySQL database to a new host, follow these steps. Here’s an example:

Example my Database

• New Database: LINUXOPERATINGSYSTEM
• File dump: /tmp/LINUXOPERATINGSYSTEM-$(date +%Y%m%d).sql

mysql -u root -p -e "create database LINUXOPERATINGSYSTEM";
mysql -u root -p LINUXOPERATINGSYSTEM < /tmp/LINUXOPERATINGSYSTEM-$(date +%Y%m%d).sql

mysqldump syntax

mysqldump <OPTIONS> > dumpfile.sql

Where OPTIONS include backing up restoring, and dumpfile.sql is the name of the file to store the database backup.

Conclusion

Following these steps, you can effectively back up and restore MySQL databases using the mysqldump command line. This knowledge is crucial for database management and ensuring that your data is securely backed up. Thank you for following this tutorial from DevopsRoles page!

Introduction

MariaDB is free and Open-source. It is the famous fork of the MySQL database. In this tutorial, How to Reset MariaDB root password. Forgetting the root password of your MariaDB database can be a frustrating experience, especially when you need to make critical updates or changes.

However, resetting the MariaDB root password on CentOS is a manageable task if you follow the right steps. This guide will take you through a straightforward, step-by-step process to reset your MariaDB root password, ensuring you regain access to your database quickly and securely. Let’s get started and resolve this issue efficiently.

Check the version of the MariaDB server.

mysql --version

How to reset MariaDB root password

Step by step to reset your MySQL/MariaDB root password.

Stop MySQL/MairaDB service

For MySQL:

sudo systemctl stop mysql

For MariaDB:

sudo systemctl stop mariadb

Start the database server without loading the grant tables

sudo mysqld_safe --skip-grant-tables &

Log in to the MySQL shell

mysql -u root

Set a new root password

For MySQL 5.7.6 and later or MariaDB 10.1.20 and later

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MY_NEW_PASSWORD';
mysql> FLUSH PRIVILEGES;

If ALTER USER statement doesn’t work for you, Try the command below

mysql> USE mysql;
mysql> UPDATE user SET password=PASSWORD('MY_NEW_PASSWORD') WHERE User='root' AND Host = 'localhost';
mysql> FLUSH PRIVILEGES;

For MySQL 5.7.5 and earlier or MariaDB 10.1.20 and earlier:

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MY_NEW_PASSWORD');
mysql> FLUSH PRIVILEGES;

Stop and Start MySQL/MariaDB

For MySQL:

sudo systemctl stop mysql
sudo systemctl start mysql

For MariaDB:

sudo systemctl stop mariadb
sudo systemctl start mariadb

Verify the password

mysql -u root -p

Conclusion

You have Reset MariaDB root password on Centos. By following the steps outlined in this guide, you should now have successfully reset your password and regained control over your MariaDB database. Remember, maintaining secure and up-to-date records of your credentials is essential to avoid similar issues in the future. If you encounter any problems or need further assistance, don’t hesitate to reach out for support. I hope will this your helpful. Thank you for reading the DevopsRoles page!

Introduction

In this tutorial, I used the Default Service for connecting to the PDB. Made in the tnsnames.ora file that defines database addresses that allow us to establish a connection with the database. The status of the PDB needs to be changed from mounted to read-write mode.

The concept Databases in Oracle 19

• The Container Database (CDB).
• Pluggable Database (PDB).

Connect to Pluggable Databases in Oracle 19

• Where cpdb is the global database.
• pdb1 and pdb2 are the two pluggable databases created.

Made in the tnsnames.ora file as below:

$ sudo su - oracle
$ cat $ORACLE_HOME/network/admin/tnsnames.ora

#The content tnsnames.ora as below:

CPDB01 =
  (DESCRIPTION =
    (ENABLE = BROKEN)
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.x.x)(PORT = 1655))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = DEVOPSROLES)
      (SERVER = DEDICATED)
    )
  )

PDB01 =
  (DESCRIPTION =
    (ENABLE = BROKEN)
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.x.x)(PORT = 1655))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = PDB01_DEVOPSROLES)
      (SERVER = DEDICATED)
    )
  )

PDB02 =
  (DESCRIPTION =
    (ENABLE = BROKEN)
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.x.x)(PORT = 1655))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = PDB02_DEVOPSROLES)
      (SERVER = DEDICATED)
    )
  )

Changing the status of the pluggable database

When the pdbs are created, by default, the open_mode is ‘Mounted’ only. Query status opens mode pdbs as command below:

$ sudo su - oracle
$ export ORACLE_SID=CPDB01
$ sqlplus "/as sysdba"
SQL> select name, open_mode from v$pdbs;

If pdbs open mode is “Mounted” only. You run the command that opens all the pluggable databases in a read-write mode.

SQL> Alter pluggable database all open;

After this connection can be established with the database using sqlplus command as below:

$ sqlplus "sys/oracle@PDB01"

Conclusion

Thought the article, “How to connect to Pluggable Databases in Oracle 19″ as above. Connecting to pluggable databases in Oracle 19c is a fundamental skill for anyone working with Oracle’s advanced database management system. By following the steps outlined in this guide, you can easily manage and connect to your PDBs, taking full advantage of Oracle’s multi-tenant architecture.

This capability not only simplifies database administration but also enhances scalability and resource management. Stay proactive in exploring and mastering Oracle’s features to keep your database environment efficient and cutting-edge. I hope will this your helpful. Thank you for reading the DevopsRoles page!

In this tutorial, How to install Oracle client 18.3 on Centos 7.

Bach server (sqlplus,sqlldr, etc) <–> Oracle server

Your environment

On server

• OS: Centos 7 (Batch server)
• Oracle client 18.3

On My PC

• OS: Windows
• Putty
• Xming server

Install Oracle client 18.3 on centos 7

On server

Install the packages

$ sudo yum install zip libaio wget unzip
$ sudo yum install  xorg-x11-server-Xorg xorg-x11-xauth xorg-x11-apps -y

Download Oracle client 18.3 Here

To change to folder Oracle client

$ cd /opt/
$ unzip LINUX.X64_180000_client.zip

Create Account for Oracle

$ sudo groupadd -g 501 oinstall
$ sudo groupadd -g 502 dba
$ sudo useradd -u 501 -g 501 -G 502 -s /bin/bash -d /home/oracle oracle -m

Enable X11Forwarding in sshd_config file. The content as below

X11Forwarding yes
X11DisplayOffset 10
X11UseLocalhost yes

On PC

Install Xming server link download here. After you have to install Xming server then start it.

Configure Putty enable X11Forwarding as the picture below

Install Oracle client 18.3

[oracle@batch-server ~]$ cd /opt/client/
[oracle@batch-server ~]$ xclock

The result as the picture below

Run Oracle client 🙂

[oracle@batch-server ~]$  ./runInstaller

Confirm after install Oracle client 18.3

How to create variable $ORACLE_HOME for oracle user. For example as below

[oracle@batch-server ~]$ cat .bash_profile

 #The content as below
 PATH=$PATH:$HOME/.local/bin:$HOME/bin
 export ORACLE_HOME=/opt/oraInventory/product/18.0.0/client_1
 export PATH=$PATH:$ORACLE_HOME/bin

 [oracle@batch-server ~]$ source .bash_profile

Create new file tnsnames.ora

[oracle@batch-server ~]$ $ORACLE_HOME/network/admin/tnsnames.ora

The following is a reference example.

devopsroles_01 = 
   (DESCRIPTION =
     (ENABLE = BROKEN)
     (LOAD_BALANCE = ON)
     (FAILOVER = ON)
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.2.15)(PORT = 1534))
       (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.2.15)(PORT = 1535))
     )
     (CONNECT_DATA =
       (SERVICE_NAME = DBB_DEVOPSROLES)
       (SERVER = DEDICATED)
     )
   )

Add end line in file sqlnet.ora

[oracle@batch-server ~]$ $ORACLE_HOME/network/admin/sqlnet.ora

The content as below

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
ADR_BASE = /opt/oracle

Connection check from Batch server to Oracle database.

[oracle@batch-server ~]$ sqlplus /nolog
 SQL> conn username/password
 SQL> set head off
 SQL> select * from v$instance ;
 SQL> quit

You have installed Oracle client 18.3 on Centos 7. If during setup error you can Q&A. Thank you for reading!

Introduction

Discover how to effectively query the Oracle v$system_parameter. You’ll learn essential SQL commands to access and manage system parameters that control various Oracle database functions.

This guide is ideal for Oracle DBAs who need to monitor or adjust system settings for optimized database performance. Gain insights into best practices for querying and interpreting the values of critical system parameters in Oracle.

• Oracle v$system_parameter: Displays system-wide parameter settings for the entire database instance.
• Oracle v$parameter: Shows parameter values specific to the current session, allowing session-specific adjustments.
• Key Differences:
  • Scope: v$system_parameter is instance-wide, while v$parameter is session-specific.
  • Usage: Understanding the difference helps in effective database management, optimizing both system stability and session flexibility.

What does Oracle “v$system_parameter” mean?

Oracle’s v$system_parameter is a dynamic performance view that provides information about the instance-level parameters of an Oracle database. It allows database administrators to view the current settings of various configuration parameters, which are essential for tuning and maintaining the database.

To check the values of these parameters, you need to log in to the Oracle database as a user with SYSDBA privileges and query the v$system_parameter view.

Step by step: Oracle v$system_parameter

Step 1: Log in and connect to the database as “SYSDBA“.

[huupv@devopsroles ~]$ sudo su - oracle
[oracle@devopsroles ~]$ sqlplus "/as sysdba"

Step 2: Check the value of a specific parameter

SYS@DEVOPSROLES > SELECT name, value FROM V$SYSTEM_PARAMETER where name = 'db_block_size';

The output will look like this:

NAME                 VALUE
-------------------- -----
db_block_size        9100

Step 3: Repeat the above query for other parameters:

To check value “db_cache_size” in Oracle Database.

SYS@DEVOPSROLES > SELECT name, value FROM V$SYSTEM_PARAMETER where name = 'db_cache_size';

To check the value of the java_pool_size parameter:

SYS@DEVOPSROLES > SELECT name, value FROM V$SYSTEM_PARAMETER where name = 'java_pool_size';

To check value “nls_language” in Oracle Database.

SYS@DEVOPSROLES > SELECT name, value FROM V$SYSTEM_PARAMETER where name = 'nls_language';

Check value “processes” in the Oracle Database.

SYS@DEVOPSROLES > SELECT name, value FROM V$SYSTEM_PARAMETER where name = 'processes';

To check the value “shared_pool_size” in Oracle Database.

SYS@DEVOPSROLES > SELECT name, value FROM V$SYSTEM_PARAMETER where name = 'shared_pool_size';

By querying the Oracle v$system_parameter view, you can easily obtain the current settings of various parameters, which is crucial for database administration and tuning.

Conclusion

the article on querying “v$system_parameter” in Oracle, it is essential to understand the power and significance of system parameters in managing and optimizing Oracle database environments.

This guide offers a comprehensive look at how to access and manipulate these settings, providing database administrators with the knowledge needed to ensure efficient database operation.

For further mastery of Oracle system parameters, continue exploring and experimenting with different queries and settings. I hope will this your helpful. Thank you for reading the DevopsRoles page!