How to check the Oracle database character set

Introduction

Character sets are a crucial aspect of Oracle databases, influencing data storage, retrieval, and integrity. Properly setting and verifying your Oracle database’s character set ensures compatibility across different systems and prevents data corruption. This guide will walk you through various methods how to Oracle database character set, including using the NLS_CHARACTERSET and NLS_NCHAR_CHARACTERSET parameters.

Basic Methods to Check Oracle Database Character Set

Using SQL*Plus

One of the simplest ways to check your Oracle database character set is through SQL*Plus. Follow these steps:

Log in to SQL*Plus:

sqlplus username/password@database

Execute the following query:

SELECT parameter, value 
FROM nls_database_parameters 
WHERE parameter = 'NLS_CHARACTERSET';

Using Data Dictionary Views

Oracle provides several data dictionary views that store character set information. The NLS_DATABASE_PARAMETERS view is one such view.

  1. Log in to SQL*Plus or any other SQL interface.
  2. Run the following query:
    • SELECT * FROM v$nls_parameters WHERE parameter = 'NLS_CHARACTERSET';

Intermediate Methods to Check Oracle Database Character Set

Using DBMS_SESSION Package

The DBMS_SESSION package allows you to access various session-level settings, including character set information.

Run the following PL/SQL block:

DECLARE
l_nls_characterset VARCHAR2(30);
BEGIN
DBMS_SESSION.GET_NLS('NLS_CHARACTERSET', l_nls_characterset);
DBMS_OUTPUT.PUT_LINE('Database Character Set: ' || l_nls_characterset);
END;
/

Checking Character Set in a Multitenant Environment

In a multi-tenant environment, it’s essential to check the character set of each pluggable database (PDB).

  1. Connect to the container database (CDB).
    • sqlplus username/password@CDB
  2. Query the character set for each PDB:
    • SELECT name, value FROM v$pdbs, v$nls_parameters WHERE parameter = 'NLS_CHARACTERSET' AND con_id = pdb.con_id;

Advanced Methods to Check Oracle Database Character Set

Using Oracle Enterprise Manager

Oracle Enterprise Manager provides a graphical interface to check and manage database settings, including character sets.

  1. Log in to Oracle Enterprise Manager.
  2. Navigate to the database you want to check.
  3. Go to the “Administration” tab and select “NLS Database Parameters.”
  4. Check the “NLS_CHARACTERSET” parameter.

Using SQL Developer

Oracle SQL Developer is a powerful tool that allows you to manage database settings graphically.

  1. Open Oracle SQL Developer and connect to your database.
  2. Navigate to “View” > “DBA.”
  3. Expand the “Database” node and select “NLS.”
  4. Find the “NLS_CHARACTERSET” parameter.

Checking Both NLS_CHARACTERSET and NLS_NCHAR_CHARACTERSET

To get a complete picture of your Oracle database character settings, you should check both the NLS_CHARACTERSET and NLS_NCHAR_CHARACTERSET parameters. Here’s how you can do it:

Log in to SQL*Plus:

sqlplus username/password@database

Execute the following query to check the NLS_CHARACTERSET:

SELECT PROPERTY_NAME, PROPERTY_VALUE
FROM database_properties
WHERE PROPERTY_NAME = 'NLS_CHARACTERSET';

Screen output:

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ --------------
NLS_CHARACTERSET               AL32UTF8

Execute the following query to check the NLS_NCHAR_CHARACTERSET:

SELECT PROPERTY_NAME, PROPERTY_VALUE 
FROM database_properties 
WHERE PROPERTY_NAME = 'NLS_NCHAR_CHARACTERSET'; 

Screen output:

PROPERTY_NAME PROPERTY_VALUE


NLS_NCHAR_CHARACTERSET AL16UTF16

Frequently Asked Questions (FAQs)

What is a character set in Oracle?

A character set in Oracle defines the set of characters that can be used in the database, determining how data is stored and retrieved. It includes encoding rules for characters.

Why is it important to check the Oracle database character set?

Checking the Oracle database character set ensures data integrity and compatibility, especially when dealing with multiple languages and diverse systems. It prevents data corruption and ensures seamless data exchange.

How can I change the character set of my Oracle database?

Changing the character set of an Oracle database is a complex process that involves exporting the database, creating a new database with the desired character set, and importing the data. It is recommended to consult Oracle documentation and perform thorough testing.

Can I check the character set using PL/SQL Developer?

Yes, you can use PL/SQL Developer to check the character set. Connect to your database, navigate to “View” > “NLS Parameters,” and find the “NLS_CHARACTERSET” parameter.

What are the common character sets used in Oracle databases?

Common character sets include AL32UTF8, UTF8, WE8ISO8859P1, and WE8MSWIN1252. The choice depends on the languages and data types you need to support.

Conclusion

Through this article, you have learned how to check the Oracle database character set using various methods, including NLS_CHARACTERSET and NLS_NCHAR_CHARACTERSET. Properly identifying and managing your Oracle database character set is essential for maintaining data integrity and compatibility.

By following these steps, you can confidently manage your database settings, ensuring smooth operations and data handling across different systems and languages. I hope this guide is helpful. Thank you for reading the DevopsRoles page!

How to Oracle query Database name: A Step-by-Step Guide

Introduction

In this tutorial, How do I use the Oracle query Database name? Using “v$parameter” for the database name. This tutorial guides you on how to query the “v$system_parameter” to determine the database name in Oracle. By connecting as SYSDBA and executing a specific SQL query, users can retrieve the database name from the system parameters, a crucial skill for database administrators managing multiple Oracle environments.

To query the database name in Oracle, start by connecting to the database using the SYSDBA role. This role grants you the necessary privileges to perform administrative tasks, including querying system parameters to find out detailed configuration information such as the database name. This process involves executing specific SQL commands that access Oracle’s system views, which contain data about the database instance.

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

Oracle query Database name

SQL> SELECT name, value from v$parameter WHERE name = 'db_name' order by name;

The screen output terminal

NAME                         VALUE
--------------------------- -----------
db_name                  DRDB1

Conclusion

Successfully querying the “v$system_parameter” for the database name provides valuable insights into your Oracle database configuration. This method, though straightforward, requires careful execution of the SQL command provided to ensure accurate data retrieval and system management I hope will this your helpful. Thank you for reading the DevopsRoles page!

Docker My Note: A Complete Reference Guide

Introduction

Docker is a popular tool in the DevOps field that simplifies the deployment and management of applications.

In this article, we will explore important notes about Docker, including basic concepts, common commands, and useful tips. Whether you are a beginner or have some experience, these notes will help you optimize your work with Docker. To learn more, visit Docker My Note.

Docker My Note: Your Go-To Guide for DevOps Success

Cannot connect to the Docker daemon. Is the docker daemon running on this host?

Solve the problem: Add this user to the docker group.

Detach and Attach

  • Detach : Ctrl-p + Ctrl-q
  • Attach: docker attach [container]

Why can not detach?

Solve the problem: docker run with “-i”, “-t” options. http://stackoverflow.com/questions/20145717/how-to-detach-from-a-docker-container

Docker runs without -it, but attach, so can not type CTRL-C

Close your terminal! Anything OK.

To safely detach…

Docker runs without “-i”, “-t” options. But you want to detach, if you hit Ctrl-C then the docker process be killed.
To avoid it, attach with

--sig-proxy=false

You want to log in to a container…

docker exec -it [container-name] /bin/bash

start on os-startup

docker command

 --restart=always

docker-compose add restart: always in docker-compose.yaml file

search a docker image in hub.docker.com

docker search nginx

Download a docker image from hub.docker.com

docker image pull <image_name>:<image_version/tag>

List out docker images from your local system

docker image ls

Expose your application to host server

docker run -d -p <host_port>:<container_port> --name <container_Name> <image_name>:<Image_version/tag>

List out running containers

docker ps

List out all docker containers (running, stpooed, terminated, etc…)

docker ps -a

Stop/Start a container

docker stop <container_id>

docker start <container_id>

Remove a container

docker rm <container_id>

Conclusion

Docker has become an indispensable tool in the modern DevOps toolkit. Through this article, we hope you have grasped the basic knowledge and useful commands to effectively apply Docker in your work. If you want to learn more and stay updated with the latest information about Docker, don’t forget to visit Docker My Note. Let Docker help you simplify and optimize your application deployment process.

Install Docker compose on Vagrant

In this tutorial, How to install docker compose on Vagrant? Docker Compose is a tool for defining and running multi-container Docker applications. Docker the essential for DevOps Roles.

Step by step Install Docker compose on Vagrant

  • Launch CentOS 7 with Vagrant
  • Install Docker on CentOS 7
  • Install Docker compose on CentOS 7
  • Sharing directory with host OS and guest OS ( CentOS 7)

Precondition

Vagrant must be installed. You can refer to Install Vagrant as below:

Directory Structure

The directory Structure looks something like as below

$/home/huupv/DockerHost
- share # Directory
- install.sh
- Vagrantfile

The content Vagrantfile file as below

# -*- mode: ruby -*-
# vi: set ft=ruby :

Vagrant.configure("2") do |config|
    config.vm.box = "centos/7"
    config.vm.network :private_network, type: "dhcp", ip: "192.168.3.6"
    config.vm.synced_folder "./share", "/home/vagrant/share", type: "rsync"
    config.vm.network :forwarded_port, host: 80, guest: 80
    config.vm.provision :shell, path: "./install.sh"
end

Install docker on Centos 7 ( guest )

Check the current release and if necessary, update it in the command below:

The content “install.sh” file as below

# SELinux Permissive
sudo setenforce 0
# set timezone JST
sudo timedatectl set-timezone Asia/Ho_Chi_Minh
# EPEL
sudo yum install -y epel-release
sudo yum install -y vim git
# install docker
sudo yum remove docker \
docker-client \
docker-client-latest \
docker-common \
docker-latest \
docker-latest-logrotate \
docker-logrotate \
docker-selinux \
docker-engine-selinux \
docker-engine
sudo yum install -y yum-utils \
device-mapper-persistent-data \
lvm2
sudo yum-config-manager \
--add-repo \
https://download.docker.com/linux/centos/docker-ce.repo
sudo yum install -y docker-ce
# install docker-compose
sudo mkdir -p /opt/bin/
sudo curl -L https://github.com/docker/compose/releases/download/1.21.2/docker-compose-$(uname -s)-$(uname -m) > ./docker-compose
sudo mv docker-compose /usr/local/bin/docker-compose
sudo chmod +x /usr/local/bin/docker-compose
# vagrant user add docker group
sudo gpasswd -a vagrant docker
# docker running
sudo systemctl enable docker
sudo systemctl start docker

Execute vagrant command

$ cd /home/huupv/DockerHost
$ vagrant up && vagrant ssh

Conclusion

Through the article, You can “Install docker compose on Vagrant as above. I hope will this your helpful.

Install Docker on Vagrant

In this tutorial, How to install docker on Vagrant? Docker the essential for DevOps Roles.

Step by step Install Docker on Vagrant

  • Launch CentOS 7 with Vagrant
  • Install Docker on CentOS 7
  • Sharing directory with host OS and guest OS ( CentOS 7)

Precondition

Vagrant must be installed. You can refer to Install Vagrant as below:

Directory Structure

The directory structure looks something like as below

$/home/huupv/DockerHost
- share # Directory
- install.sh
- Vagrantfile

The content Vagrantfile file as below

# -*- mode: ruby -*-
# vi: set ft=ruby :

Vagrant.configure("2") do |config|
    config.vm.box = "centos/7"
    config.vm.network :private_network, type: "dhcp", ip: "192.168.3.6"
    config.vm.synced_folder "./share", "/home/vagrant/share", type: "rsync"
    config.vm.network :forwarded_port, host: 80, guest: 80
    config.vm.provision :shell, path: "./install.sh"
end

Install docker on Centos 7 ( guest )

The content “install.sh” file as below

# SELinux Permissive
sudo setenforce 0
# set timezone JST
sudo timedatectl set-timezone Asia/Ho_Chi_Minh
# EPEL
sudo yum install -y epel-release
sudo yum install -y vim git
# install docker
sudo yum remove docker \
docker-client \
docker-client-latest \
docker-common \
docker-latest \
docker-latest-logrotate \
docker-logrotate \
docker-selinux \
docker-engine-selinux \
docker-engine
sudo yum install -y yum-utils \
device-mapper-persistent-data \
lvm2
sudo yum-config-manager \
--add-repo \
https://download.docker.com/linux/centos/docker-ce.repo
sudo yum install -y docker-ce
# vagrant user add docker group
sudo gpasswd -a vagrant docker
# docker running
sudo systemctl enable docker
sudo systemctl start docker

Execute vagrant command

$ cd /home/huupv/DockerHost
$ vagrant up && vagrant ssh
Video How to Install Docker using Vagrant

Conclusion

Thought the article, You can “install docker on Vagrant as above . I hope will this your helpful. Thank you for reading the DevopsRoles page!

Modifying individual text fields in Bash Script

In this tutorial, How do I modifying individual text fields in bash script? Bash Script the essential for DevOps Roles. I run the script on a Linux server.

  • Input: string is “HHHHHHHH
  • Output: AHHHHHHH, BHHHHHHH, CHHHHHHH, DHHHHHHH, XHHHHHHH, ZHHHHHHH

Modifying individual text fields in Bash Script

#!/bin/bash
STRING="HHHHHHHH"
COUNT=1
echo "Input: $STRING"
while [ "$COUNT" -le "${#STRING}" ]; do
   for i in A B C D X Y Z
   do
       printf '%s%s%s\n' "${STRING:0:COUNT-1}" "$i" "${STRING:COUNT}"
   done
   COUNT=$(( COUNT + 1 ))
   break
done

The screen output terminal as below

[vagrant@app1 ~]$ ./bashscript.sh
Input: HHHHHHHH
AHHHHHHH
BHHHHHHH
CHHHHHHH
DHHHHHHH
XHHHHHHH
YHHHHHHH
ZHHHHHHH

For example, You delete break line in the loop while 🙂 

The result, delete break line in the loop while as below

Conclusion

Through the article, You can “Modifying individual text fields in Bash Script as above. I hope will this your helpful.

Set up Jenkins with docker

In this tutorial, Quickstart with Jenkins in docker. Jenkins is an automation CI/CD tool. with Jenkins, you can create jobs to build, test and deploy your application. Docker the essential for DevOps Roles.

Set up Jenkins with docker

Docker compose for Jenkins

You can create “jenkins-docker-compose.yml” file for Jenkins

version: '2'

services:
  jenkins:
    image: 'jenkins:2.60.3'
    container_name: jenkins
    user: root
    restart: always
    ports:
      - '8080:8080'
      - '5000:5000'
    volumes:
      - /home/huupv/data/jenkins:/var/jenkins_home
      - /var/run/docker.sock:/var/run/docker.sock

Note:

  • docker.sock with Host in the volume. But be careful, you can delete the container from Docker.
  • “/var/jenkins_home” is a place to read docker images. so do not change. If Docker has died, then your data remains and restore if you start it again.

Starting Jenkins with docker

Execute this command as below

docker-compose -f jenkins-docker-compose.yml up

From local, you can access  Jenkins at browser at http://0.0.0.0:8080

Conclusion

Through the article, You can “Set up Jenkins with docker“ as above. I hope will this your helpful.

Docker compose WordPress

Introduction

In this tutorial, you’ll learn how to set up WordPress using Docker Compose, which utilizes two separate containers to streamline the process. Docker is crucial for DevOps roles, enabling efficient deployment and management of applications. This guide simplifies the integration of WordPress into your Docker workflow, ensuring a smooth setup.

Docker image

  • WordPress
  • MySQL

You can find the installation guide for Docker Compose provided here

Docker compose WordPress

Docker Compose with WordPress refers to using Docker Compose as a tool to facilitate the setup and management of WordPress in a containerized environment. This approach leverages Docker to simplify the deployment and configuration processes by defining and running multi-container Docker applications. It’s a powerful method for developers looking to streamline their WordPress development and deployment workflows within Docker.

The configuration file and start docker-compose.

version '2'
services: 
 web:
  image: wordpress
  ports:
    - "8888:80"
  environment:
    WORDPRESS_DB_PASSWORD: password_here
    WORDPRESS_DB_HOST: db
 db:
  image: mysql
  environment:
    MYSQL_ROOT_PASSWORD: password_here

Starting docker in the background with the -d option.

$ docker-compose up -d

After, you can check http://localhost:8888 in the browser.

The end service is down to the command below

$ docker-compose down

Conclusion

I hope this article on using “Docker Compose with WordPress” has been informative and useful for your projects. This setup is designed to streamline your WordPress deployment, making it more efficient within a Dockerized environment. If you have any questions or need further assistance, feel free to reach out or consult additional resources. Thank you for reading, and good luck with your DevOps endeavors!

Ultimate MySQL Cheat Sheet: Essential Commands You Need to Know

Introduction

Welcome to the MySQL Cheat Sheet, a valuable resource for anyone looking to enhance their command line skills with MySQL. This comprehensive guide covers a range of commands, from basic operations like database creation and deletion to more complex tasks such as backing up and restoring databases.

In this tutorial, I will write a MySQL Cheat sheet by command line. How to Reset Root Password in MySQL? How to backup and restore databases, and so forth in MySQL databases.

  • MySQL Database Commands Cheat Sheet:
    • A comprehensive list of commands for database creation, modification, and management.
  • MySQL Command Line Client Cheat Sheet:
    • Essential commands for initiating and interacting with MySQL through the command line.
  • Create Table MySQL Cheat Sheet:
    • Syntax and options for creating tables, defining columns, setting data types, and applying constraints.
  • MySQL Connect Cheat Sheet:
    • Commands for connecting to a MySQL database using various authentication methods.

MySQL cheat sheet

At the command line, log in to MySQL as the root user:

mysql -u root -p

How to create and delete MySQL DATABASE

CREATE DATABASE DBName
CREATE DATABASE DBName CHARACTER SET utf8
DROP DATABASE DBName

Backup DATABASE to SQL File in MySQL

mysqldump -u Username -p DBName > sampledatabasename_backup.sql

Restore from backup SQL File in MySQL

mysql - u Username -p DBName < sampledatabasename_backup.sql

MySQL select query

SELECT * FROM table
SELECT * FROM table1, table2, ...
SELECT field1, field2, ... FROM table1, table2, ...
SELECT ... FROM ... WHERE condition
SELECT ... FROM ... WHERE condition GROUPBY field
SELECT ... FROM ... WHERE condition GROUPBY field HAVING condition2
SELECT ... FROM ... WHERE condition ORDER BY field1, field2
SELECT ... FROM ... WHERE condition ORDER BY field1, field2 DESC
SELECT ... FROM ... WHERE condition LIMIT 10
SELECT DISTINCT field1 FROM ...
SELECT DISTINCT field1, field2 FROM ...

Users and Privileges in MySQL

GRANT ALL PRIVILEGES ON sampledatabase.* TO 'user'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, INSERT, DELETE ON sampledatabase.* TO 'user'@'localhost' IDENTIFIED BY 'password';
REVOKE ALL PRIVILEGES ON sampledatabase.* FROM 'user'@'hostname'; -- one permission only
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'hostname'; -- To revoke all privileges, which drops all global, database, table, column, and routine privileges for the named user or users

SET PASSWORD = PASSWORD('new_pass')
SET PASSWORD FOR 'user'@'hostname' = PASSWORD('new_pass')
SET PASSWORD = OLD_PASSWORD('new_pass')

DROP USER 'user'@'hostname' -- DROP to delete a user

Reset Root Password in MySQL

$ /etc/init.d/mysql stop
$ mysqld_safe --skip-grant-tables

Open another terminal

$ mysql 
mysql> UPDATE mysql.user SET password=PASSWORD('new_pass') WHERE user='root';

Switch back to the mysqld_safe terminal and kill the process using Control

$ /etc/init.d/mysql start

Conclusion

This MySQL Cheat Sheet is designed to serve as a quick reference for frequently used MySQL commands. Whether you’re a beginner or an experienced developer, these commands are essential tools to efficiently manage your databases. Keep this guide handy to streamline your database operations. I hope will this your helpful. Thank you for reading the DevopsRoles page!

How to find latest Oracle database patchset

In this tutorial, How to find the latest Oracle database patchset: Patch Set Release (PSR). Sometimes you check the latest patch name.

find latest Oracle database patchset

Which oracle patch has installed your system?

The easiest check Patch Set Release (PSR)as command below

$ sudo su - oracle
$ opatch lsinventory | grep "^Oracle Client"

More verbose list on the installed Oracle patches:

$ sudo su - oracle
$ opatch lsinventory -details

Conclusion

Thought the article, You can “How to find latest Oracle database patchset” as above. I hope will this your helpful. Thank you for reading the DevopsRoles page!

Devops Tutorial

Exit mobile version