Category Archives: Database Administrator

Become a Database Administrator with DevOpsRoles.com. Access expert guides and tutorials to master database management, optimization, and security for DevOps.

Database Administrator

Mastering Date Manipulation in Oracle Database: Oracle change sysdate Tips and Tricks

Leave a comment

In this tutorial

How to change sysdate for Oracle Database server. Oracle gets the system day from OS server data. You can change Oracle SYSDATE for testing.

In Oracle Database, the SYSDATE function returns the current date and time of the database server. However, you cannot directly change the value of SYSDATE as it is a read-only function that always provides the current timestamp. It reflects the system time when the query or statement is executed.

If you want to manipulate or change dates in Oracle, you can use various date manipulation functions and arithmetic operations. For example, you can use the ADD_MONTHS, TO_DATE, TRUNC, DATEADD, and other functions to perform date calculations and transformations.

Oracle change sysdate Tips and Tricks

Oracle change SYSDATE.

SQL> ALTER SYSTEM SET fixed_date = '2018-01-28-13:00:00' SCOPE = MEMORY;
  • Compatibility and Availability: The use of fixed_date is available only in Oracle Database releases before Oracle Database 21c. Starting from Oracle Database 21c, the fixed_date parameter has been desupported and cannot be used to alter the system date and time.
  • Scope: The SCOPE parameter determines whether the change takes effect immediately (MEMORY scope) or at the next database startup (SPFILE scope).
  • Format: The fixed_date parameter expects the date and time in the YYYY-MM-DD-HH24:MI:SS format.
  • Privileges: Modifying system parameters typically requires administrative privileges, such as the ALTER SYSTEM privilege.

Oracle Reset SYSDATE

SQL> ALTER SYSTEM SET fixed_date=NONE;

Beware! Changing Oracle sysdate for testing. After you can reset sysdate for the Oracle database server.

Checking the change SYSDATE Oracle.

SQL> select sysdate from dual;

Conclusion

You have used Oracle change sysdate for testing. My topic is another  “Install Oracle Database 12c on Centos 7”. I hope will this your helpful.

Database Administrator

Export and Import in Oracle

Leave a comment

Introduction

In this tutorial, you will learn how to Export and Import in Oracle 11g. This process is essential for database administrators to backup, transfer, and restore database schemas efficiently.

  1. Create DBA Directory
  2. Export all SCHEMA using expdp
  3. Import all SCHEMA using impd

Export and Import in Oracle

Syntax for IMPDP command

IMPDP TABLE_EXISTS_ACTION PARAMETER EXPLAINED

TABLE_EXISTS_ACTION

  • SKIP: The default value for this parameter is SKIP.
  • APPEND: the dump will be appended to the table and the existing data will remain unchanged.
  • TRUNCATE: This option truncates the existing rows in the table and inserts the rows from the dump
  • REPLACE: This option drops the current table and creates the table as it is in the dump file. Both SKIP and REPLACE options are not valid if you set the CONTENT=DATA_ONLY for the impdp.

Create a DBA Directory in Oracle

Login into SQL Plus with user sys.

[oracle@DBOracle ~]$ sqlplus / as sysdba

Creating folder DBA MY_BACKUP_DIR in /tmp folder

SQL> create or replace directory MY_BACKUP_DIR AS '/tmp/oraclebackup';

DBA Directory is created by the user system. Only users grant DBA to use this folder. you can assign grant Other_User.

SQL> grant read,write on directory MY_BACKUP_DIR to Other_User;

Export all SCHEMA using expdp

Using expdp command to export all schema into a dump file.

[oracle@DBOracle ~]$ expdp \"sys/123456789@DEVOPSROLES AS SYSDBA\" DIRECTORY=MY_BACKUP_DIR DUMPFILE=exp_ora.dmp SCHEMAS=huupv LOGFILE=exp_ora.log

Import all SCHEMA using impd

The first, create a user huupv

SQL> create user huupv identified by 123456789;

Import from sysadmin

[oracle@DBOracle ~]$ impdp \"sys/123456789@DEVOPSROLES AS SYSDBA\" schemas=huupv directory=MY_BACKUP_DIR dumpfile=exp_ora.dmp logfile=imp_ora.log

Note:

  • File dump: exp_ora.dmp
  • Service Name: DEVOPSROLES
  • To schema: huupv

Conclusion

Through this article, you have learned how to use the Export and Import in Oracle 11g to manage database schemas. These tools are crucial for database backup, restoration, and migration tasks. I hope will this your helpful. Thank you for reading the DevopsRoles page!

Database Administrator

Oracle create schema: A Step-by-Step Guide

1 Comment

Introduction

A schema is defined as a user that owns data such as tables, indexes, and so forth. In this tutorial, How to use Oracle create schema.

Dive into the world of Oracle with this practical guide on creating schemas. This tutorial walks you through the essential steps to establish a schema in Oracle Database, starting with creating a tablespace, defining a new user, and culminating in setting up a test table. Ideal for database administrators and developers who want to streamline their database structure and enhance data management.

Oracle create schema

Access the Oracle database with permission privileges for creating a tablespace, creating a user, and creating a table on the Oracle Database.

[oracle@DBOracle ~]$ sqlplus / as sysdba

Step 1: Create tablespace on Oracle Database

Before creating a schema you need to create a file ( one or more files) for the schema to place its data into. This file schema writes data is called a tablespace. One tablespace has one or more datafile.

SQL> create tablespace datafile_test datafile '/U02/datafile_test_01.dbf' size 1000M extent management local autoallocate segment space management auto;

Step 2: Oracle Create a new user

The explain use command creates a user in Oracle

User: HuuPV
Password: pwd4HuuPV
Default Tablespace: datafile_test
Temporary Tablespace: TEMP

SQL> CREATE USER HuuPV IDENTIFIED BY pwd4HuuPV DEFAULT TABLESPACE datafile_test TEMPORARY TABLESPACE TEMP;

Oracle Privileges Granted to the APPDEV Role

SQL> GRANT CONNECT TO HuuPV;
SQL> GRANT UNLIMITED TABLESPACE TO HuuPV;
SQL> GRANT CREATE TABLE TO HuuPV;
SQL> GRANT CREATE PROCEDURE TO HuuPV;
SQL> GRANT CREATE VIEW TO HuuPV;
SQL> GRANT CREATE TRIGGER TO HuuPV;
SQL> GRANT CREATE SEQUENCE TO HuuPV;
SQL> GRANT CREATE SYNONYM TO HuuPV;

Step 3: Oracle creates a table for the test

SQL> create table test (NAME varchar2(32), AGE number);
SQL> insert into test (NAME, AGE) values ('HuuPV', 29);
SQL> insert into test (NAME, AGE) values ('Huu', 30);
SQL> commit;

Examine the content of the table test created above

SQL> select * from test;

The result created a table for the test

Oracle create schema another method uses the script oracle_create_user.sql  file

The content oracle_create_user.sql file

CREATE USER HuuPV IDENTIFIED BY pwd4HuuPV DEFAULT TABLESPACE datafile_test TEMPORARY TABLESPACE TEMP;
GRANT CONNECT TO HuuPV;
GRANT UNLIMITED TABLESPACE TO HuuPV;
GRANT CREATE TABLE TO HuuPV;
GRANT CREATE PROCEDURE TO HuuPV;
GRANT CREATE VIEW TO HuuPV;
GRANT CREATE TRIGGER TO HuuPV;
GRANT CREATE SEQUENCE TO HuuPV;
GRANT CREATE SYNONYM TO HuuPV;

Running script oracle_create_user.sql file

SQL>@/home/oracle/scripts/oracle_create_user.sql

Conclusion

By following the steps outlined in this guide, you’ve learned how to create an Oracle schema effectively. This foundation is crucial for managing and organizing data in Oracle databases efficiently, providing you with the skills to maintain a robust database environment.

My topic is “Install Oracle Database 12c on Centos 7“. I hope you find this helpful.

Database Administrator

Install Oracle Database 12c on Centos 7

11 Comments

Oracle Database is a relational database management system (RDBMS) from the Oracle Corporation. I will guide Install Oracle Database 12c on centos 7 for Dev Environment step by step.

Step 1: Intro

My system is as below:

[root@DBOracle ~]# hostnamectl
Static hostname: DBOracle.DevopsRoles.local
Icon name: computer-vm
Chassis: vm
Machine ID: 248dbd1f6d5b4bbb85e743d11d4e994e
Boot ID: 7eded015f6a242e9a9e8c982d754cc26
Virtualization: kvm
Operating System: CentOS Linux 7 (Core)
CPE OS Name: cpe:/o:centos:centos:7
Kernel: Linux 3.10.0-862.6.3.el7.x86_64
Architecture: x86-64

Prerequisites

  • The firewall allows port Oracle Database
  • SELinux allows Oracle Database

In this guide, I will stop the firewall and SELinux disable mode 🙂 for a test install Oracle Database.

Step 2: Prepare to install Oracle database 12c step-by-step

Install required packages

[root@DBOracle ~]# yum install -y binutils.x86_64 compat-libcap1.x86_64 gcc.x86_64 gcc-c++.x86_64 glibc.i686 glibc.x86_64 \
glibc-devel.x86_64 ksh compat-libstdc++-33 libaio.i686 libaio.x86_64 libaio-devel.i686 libaio-devel.x86_64 \
libgcc.x86_64 libstdc++.i686 libstdc++.x86_64 libstdc++-devel.i686 libstdc++-devel.x86_64 libXi.i686 libXi.x86_64 \
libXtst.x86_64 make.x86_64 sysstat.x86_64 zip unzip smartmontools net-tools

Create user and group for Oracle Database

[root@DBOracle ~]# groupadd oinstall
[root@DBOracle ~]# groupadd dba
[root@DBOracle ~]# useradd -g oinstall -G dba oracle
[root@DBOracle ~]# passwd oracle

Add the parameters to the /etc/sysctl.conf as below

fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 1987162112
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048586

Check and apply the new value-add parameters atop

sysctl -p
sysctl -a

the limit for Oracle users in /etc/security/limits.conf file

oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
#Oracle recommended value for stack is set to 10240 for user oracle
oracle soft stack 10240

Oracle installation requires GUI access. I will install an X Window System on the Server Oracle Database

[root@DBOracle ~]# yum groupinstall -y "X Window System"

The use command below, to access to install Oracle databases in Step 3

ssh -X oracle@192.168.1.114

Download Oracle Databases

If you do not have an Oracle account yet, then register one to download it!

[root@DBOracle ~]# cd ~oracle/
[root@DBOracle ~]# unzip linuxx64_12201_database.zip -d /stage
[root@DBOracle ~]# chown -R oracle:oinstall /stage/database

Create folder U01 for Oracle installation files and folder U02 for the Oracle database files.

[root@DBOracle ~]# mkdir -p /U01 /U02

Change the owner and group of the new folder and set permissions to ‘755’

[root@DBOracle ~]# chown -R oracle:oinstall /U01 /U02
[root@DBOracle ~]# chmod -R 775 /U01 /U02
[root@DBOracle ~]# chmod g+s /U01 /U02

Yeah! let’s go install Oracle Database 12c. Are you ready

Step 3: Install Oracle Database 12c

From the client open a new terminal and connect to the server Oracle database.

ssh -X oracle@192.168.1.114

Install Oracle Database 12c as below:

[root@DBOracle ~]# cd /stage/database
[root@DBOracle ~]# ./runInstaller

The display terminal is as below:

Starting Oracle Universal Installer...

Checking Temp space: must be greater than 500 MB. Actual 26587 MB Passed
Checking swap space: must be greater than 150 MB. Actual 9535 MB Passed
Checking monitor: must be configured to display at least 256 colors. Actual 16777216 Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2018-07-05_10-20-03PM. Please wait ...

The installation GUI Oracle Database 12c as the picture below:

Select Installation Option

The choice “Create and configure a database” –> Next

Configure Security Updates

For Dev Environment

  • Email: None
  • Uncheck: I wish to receive security updates via My Oracle Support

For Production Environment

  • Email: Input your email
  • Check: I wish to receive security updates via My Oracle Support
  • My Oracle Support Password: Input your password

Select System Class

For Dev Environment

  • The choice ” Desktop class” –> Next

For production Environment

  • The choice “Server class” –> Next

Typical Install Configuration

  • Oracle base: /U01/home/oracle/app/oracle
  • Software location: /U01/home/oracle/app/oracle/product/12.2.0/dbhome_1
  • Database file location: /U02
  • Global database name: DevopsRoles
  • Password: DevopsRoles123
  • Uncheck: Create a Container database

Create Inventory

Summary

Install Product

During installation display pop-up as below

Open a new terminal and Login the root account into the server Oracle Database run two commands in the pop-up as below:

# bash /U01/home/oracle/app/oraInventory/orainstRoot.sh
# bash /U01/home/oracle/app/oracle/product/12.2.0/dbhome_1/root.sh

The picture runs commands as below:

Finish

Install Oracle Database 12c on Centos 7

Step 4: Testing login Oracle databases 12c

The Oracle installation is finished.

ssh root@192.168.1.114
su - oracle

To execute the command below to set the Oracle environment

export ORACLE_SID=DevopsRoles
export ORACLE_HOME=/U01/home/oracle/app/oracle/product/12.2.0/dbhome_1
export PATH=$PATH:$ORACLE_HOME/bin

Another method set the Oracle environment without losing it after the server Oracle reboot

[root@DBOracle ~]# su - oracle
[oracle@DBOracle ~]$ vi ~/.bash_profile

Add lines set Oracle end of the line in the bash_profile file

#Oracle environment
export ORACLE_SID=DevopsRoles
export ORACLE_HOME=/U01/home/oracle/app/oracle/product/12.2.0/dbhome_1
export PATH=$PATH:$ORACLE_HOME/bin

To apply the changeset Oracle environment

[oracle@DBOracle ~]$ source ~/.bash_profile

Access the Oracles database with permission privileges

sqlplus / as sysdba

Oracle uses default users. To change the default username ‘sys’

alter user sys identified by yourpassword;

Now, access the Oracle database web-based. Open your web browser https://192.168.1.114:5500/em/

Login Oracle database

User: system
Password: you have set up in step 3

Conclusion

Through the article, you have Install Oracle Database 12c on Centos 7. I hope will this your helpful. Thank you for reading the DevopsRoles page!