Get Instant Database Support Oracle 19c Installation on Linux Oracle 19c adds more features to Oracle database and this article demonstrates Oracle 19c installation on Linux 7. We will be using Oracle Linux 7 and Oracle Database 19.3 softwares for this activity. We will also be looking at database creation via DBCA. FEATURED ARTICLES Most Popular Articles New to DBA Genesis Articles? Start with our most-viewed guides and resources. I am a title 01 I am a title 02 I am a title 03 View all Performance Tuning Articles Tune your Oracle database for high performance and low latency I am a title 01 I am a title 02 I am a title 03 View all Oracle Data Guard Articles Keep you primary in sync with standby always I am a title 01 I am a title 02 I am a title 03 View all Oracle RAC Articles Oracle RAC enables high availability and scalability with seamless failover across multiple servers. I am a title 01 I am a title 02 I am a title 03 View all Oracle Golden Gate Articles Oracle GoldenGate ensures real-time data integration and replication for heterogeneous systems I am a title 01 I am a title 02 I am a title 03 View all Stay Ahead in the World of Database and Cloud Script to create JUSTLEE schema in Oracle Here is the script to create JUSTLEE schema in Oracle Connect Linux Instance with SSH Keys This article provides a step-by-step guide on establishing a secure connection to a Linux server using SSH keys. It covers the process for both Windows using PuTTY, and MacOS using the Terminal. SQL Project - Library Management The goal of the project is to create a database for a library management system that allows users to efficiently manage customer information, book inventory & book borrowings SQL Project - Supermarket Billing The goal of the project is to create a database for a supermarket to efficiently manage inventory, sales, and customer information. Linux Project - Monitor Server Disk Space In this Linux project we will write a shell script that will monitor the disk space and put the info in a file every 20 mints. Setting up... Install Oracle 19c with ASM In this article we would be looking at installing Oracle 19c with ASM on Linux. This will allow us to create 19c databases on ASM disks.... Oracle 21c Installation on Linux Oracle 21c is an innovation release that provides early insights to latest developments and features. Move Spfile to ASM With ASM configured for RAC or NON-RAC systems, it is a good idea to move the spfile to ASM. The PFILE under $ORACLE_HOME/dbs location... Oracle Database 19c RPM-based Installation On Oracle Linux 7 Oracle Database 19c RPM-based installation on Oracle Linux 7 allows customers to quickly and easily install and configure the Oracle... MySql 8.0 on OEL 7.9 This article demonstrates MySQL 8.0.31 installation on OEL 7.9 version. Now, we will use WGET utility to download the RPM. wget... PostgreSQL15 on CentOS 7 PostgreSQL is a powerful, open-source object-relational database system with over 30 years of active development that has earned it a... MySQL 8.0 on CentOS 7 This article demonstrates MySQL installation on Cent OS Linux 7 version. Installation of MySQL Install wget to download MySQL repository...

Oracle ASM Queries ASM Metadata allows you to manage ASM disks and Diskgroups smoothly. The fastest way to administer ASM is via sqlplus / as sysasm . Here are some of my favorite ASM queries that will help you quickly understand its configuration Query to Find ASM Diskgroup Usage Query to Find ASM Disks Usage Query to Find Databases Connected to ASM Add Disk to ASM Diskgroup ASM Disks Header and Mount Status Query to Find ASM Diskgroup Usage To check ASM diskgroup size along with free space and used percentage SELECT NAME, STATE, TYPE, ROUND(TOTAL_MB / 1024, 2) "SIZE_GB", ROUND(FREE_MB / 1024, 2) "AVAILABLE_GB", ROUND((total_mb - free_mb) / total_mb * 100, 2) AS "USED%" FROM v$asm_diskgroup; Query to Find ASM Disks Usage To check ASM disks size along with free space and used percentage SELECT dg.name AS "Disk Group", d.name AS "Disk Name", ROUND(d.total_mb / 1024, 2) AS "SIZE_GB", ROUND(d.free_mb / 1024, 2) AS "AVAILABLE_GB", ROUND((d.total_mb - d.free_mb) / d.total_mb * 100, 2) AS "USED%" FROM v$asm_disk d JOIN v$asm_diskgroup dg ON (d.group_number = dg.group_number) ORDER BY dg.name, d.name; Query to Find Databases Using ASM To check which database instances are connected to ASM instance SELECT instance_name, db_name, status, software_version FROM v$asm_client; Add Disk to ASM Diskgroup As root user, create the new ASM disk on the specific partition oracleasm createdisk DATA02 /dev/sdc1 Check the newly added disk and attach it the diskgroup select name, path, mount_status, header_status from v$asm_disk; alter diskgroup DATA add disk ‘/dev/oracleasm/disks/DATA02’ NAME DATA02 rebalance power 100; Check rebalance status - If no output, then rebalance is completed select * from v$asm_operation; Check the newly added disk in ASM Diskgroup ASM Disks Header and Mount Status There are two important columns under V$ASM_DISK which are MOUNT_STATUS and HEADER_STATUS . These two columns are required when you add / remove ASM disks. ASM Disk MOUNT_STATUS MISSING – Disk is known to be part of the ASM disk group, but no disk in the storage CLOSED – Disk is present in the storage system but is not being accessed by ASM OPENED – Disk is present in the storage system and is being accessed by ASM CACHED – Disk is present in the storage system, and is part of a disk group being accessed by the ASM instance. This is the normal state for disks in an ASM. ASM Disk HEADER_STATUS UNKNOWN – ASM disk header has not been read CANDIDATE – Can be used INCOMPATIBLE – Version number in the disk header is not compatible with the ASM version PROVISIONED – Disk is not part of a disk group and may be added to a disk group MEMBER – Already member of a diskgroup FORMER – Once used, can be re-used CONFLICT – ASM disk was not mounted due to a conflict Related Posts Heading 2 Add paragraph text. Click “Edit Text” to customize this theme across your site. You can update and reuse text themes.

Oracle ASM Administration Managing data under ASM disks and diskgroups is very important task for a DBA. In this article, we will be looking at general ASM administration tasks you must know. Create Directory Under ASM Diskgroup Find ASM physical disk mapping Create Diskgroup ASM Create Tablespace in Oracle ASM Drop Diskgroup Create Directory Under ASM Diskgroup Creating directories under ASM filesystem is same as running Linux commands. Start asmcmd asmcmd You can use many OS level commands like ls – to list contents pwd – check present working directory mkdir – create new directory rm – remove directory or file cp – copy file or directory ASMCMD > ls CRSVOL1 / DATAVOL1 / FRAVOL1 / RMANVOL / Let us create directory under RMANVOL diskgroup ASMCMD > cd RMANVOL / ASMCMD > mkdir RMAN_BACKUP ASMCMD > mkdir RMAN_SCRIPT ASMCMD > mkdir RMAN_LOG You can create multiple directories in one single command too ASMCMD > mkdir RMAN_BACKUP RMAN_SCRIPT RMAN_LOG Find ASM physical disk mapping When you work in a RAC environment with a list of ASM disks, sometimes you might want to figure out which ASM disk is mapped to which physical device. Below is a simple script that will help you find out ASM disks that are mapped to Linux partitions #!/bin/bash echo "------------------" echo "ASM Disk Mappings" echo "------------------" for f in $(oracleasm listdisks); do dp=$(lsblk | grep $(oracleasm querydisk -d $f | awk -F'[][]' '{gsub(/,/ , ":"); print $2}') | awk '{print $1, "size", $4}' | tr -d '`' | sed 's/[-|]//g') echo -e "$f\tmapped to /dev/$dp" done And below is the sample output ------------------ ASM Disk Mappings ------------------ CRS01 mapped to /dev/sdb1 size 5G DATA01 mapped to /dev/sdb2 size 50G FRA01 mapped to /dev/sdb3 size 80G Create Diskgroup ASM There are two ways to create new diskgroups in ASM: ASMCA (GUI) and SQLPLUS (SQL Syntax) . As root user, mark the disk headers which you will be attaching to the diskgroup oracleasm createdisk RMAN01 /dev/sdc1 As grid user, connect to sqlplus and check if above created disk is available sqlplus / as sysasm SELECT NAME, PATH, HEADER_STATUS FROM V$ASM_DISK; The HEADER_STATUS must show as PROVISIONED. Create diskgroup with EXTERNAL REDUNDANCY CREATE DISKGROUP RMAN EXTERNAL REDUNDANCY DISK '/dev/oracleasm/disks/rman01'; Create Normal Redundancy Diskgroup Redundancy is defined by failgroups inside a diskgroup. You need to add at lease two failgroups when creating normal redundancy disk group. All the disks that you would like to attach shall belong to respective failgroups. Everytime ASM is writing to one failgroup, it create a copy of the same data into another failgroup. If any failgroup is not available, ASM retrieves the data from the available failgroup. As root user, mark the disk headers which you want to attach oracleasm createdisk OLTP01 / dev / sdd1 oracleasm createdisk OLTP02 / dev / sdd2 As grid user, connect to sqlplus and check if above created disk is available sqlplus / as sysasm SELECT NAME, PATH, HEADER_STATUS FROM V$ASM_DISK; The HEADER_STATUS must show as PROVISIONED. Create diskgroup with NORMAL REDUNDANCY CREATE DISKGROUP OLTP NORMAL REDUNDANCY FAILGROUP OLTP_FG1 DISK '/dev/oracleasm/disks/oltp01' FAILGROUP OLTP_FG2 DISK '/dev/oracleasm/disks/oltp02'; Create High Redundancy Diskgroup For high redundancy, you need at least 3 failgroups. As root user, mark the disk headers which you want to attach oracleasm createdisk BACKUP01 / dev / sde1 oracleasm createdisk BACKUP02 / dev / sde2 oracleasm createdisk BACKUP03 /dev/sde3 Create diskgroup with EXTERNAL REDUNDANCY CREATE DISKGROUP BACKUP EXTERNAL REDUNDANCY FAILGROUP BACKUP_FG1 DISK '/dev/oracleasm/disks/backup01' FAILGROUP BACKUP_FG2 DISK '/dev/oracleasm/disks/backup02' FAILGROUP BACKUP_FG3 DISK '/dev/oracleasm/disks/backup03'; Change Diskgroup Redundancy You cannot directly change the redundancy of diskgroup. Instead create a new diskgroup with desired redundancy and move datafiles. You can simply use below command to move datafile from 12c version and above alter database move datafile '+DATA/ORCL/DATAFILE/user01.dbf' to '+NEWDG'; Create Tablespace in Oracle ASM ASM uses OMF (Oracle Managed Files) feature of Oracle database to auto create datafiles in respective ASM diskgroups. The DB_CREATE_FILE_DEST defines the default location where Oracle creates datafiles Show parameter db_create_file_dest; Make sure db_create_file_dest is set to +DATA DB_RECOVERY_FILE_DEST defines the default location for FRA show parameter db_reocovery_file_dest show parameter db_recovery_file_dest_size Make sure db_reocovery_file_dest is set to +FRA Create new tablespace with 10 gb size SQL > CREATE TABLESPACE test_tbs DATAFILE SIZE 10G ; You can manually give datafile name too SQL > CREATE TABLESPACE test_tbs datafile '+ASM/DATAFILES/ORCL/test_tbs.dbf' size 10G ; Drop Diskgroup To drop a diskgroup including all the files on the ASM disks DROP DISKGROUP RMAN INCLUDING CONTENTS; Related Posts Heading 2 Add paragraph text. Click “Edit Text” to customize this theme across your site. You can update and reuse text themes.

Upgrade Oracle Golden Gate 12cR1 to 12cR2 In this article, we will look at Oracle Golden Gate upgrade from 12.1 to 12.2 on a Linux server. Make sure you Allow golden gate processes to finish processing all current DML and DDL data in GG trail files. Golden Gate Upgrade Prerequisite Perform Golden Gate upgrade If upgrading DB along with GG, upgrade DB first Golden Gate Upgrade Prerequisite Stop user activity on source objects which are involved in replication Stop Extract GGSCI> lag ext1 GGSCI> stop ext1 Make sure replicat applies all transactions on target GGSCI> lag rep1 Stop pump and Replicat GGSCI> stop dp1 GGSCI> stop rep1 Stop Manager on both source and target GGSCI> stop mgr Take backup of GG_HOME on both source and target If you want to upgrade source and target DBs, this is the time. Once DBs are upgraded still, make sure there is no activity on source objects involved in the replication Perform Golden Gate upgrade Download the latest 12.2.0.2 Golden Gate and copy the zip file to both source and target DBs Unzip and start the runInstaller Give same old GG_HOME location while installation – Do not worry, this option will auto-upgrade old GG Do not start manager Perform installation on both source and target DBs in the same old GG_HOME location Run @ulg script for supplemental log upgrade from GG_HOME cd $GG_HOME Sqlplus / as sysdba SQL> @ulg.sql  press enter when prompted Rollover Extract, Pump, and Replicat to next trail sequence number On source DB: ============= alter extract ext1 etrollover alter extract dp1 etrollover info ext1 detail ALTER EXTRACT dp1, EXTSEQNO , EXTRBA 0 info dp1 detail On target DB: ============= alter replicat rep1, EXTSEQNO , EXTRBA 0 Start Manager, Extract, Pump, and Replicat This is an oracle known error. You get prvtlmpg.plb script with GG binaries. This resides under $GG_HOME. Run this script on both source and target database cd $GG_HOME Sqlplus / as sysdba SQL> @prvtlmpg.plb --> press enter when prompted Allow activity on source objects involved in the replication. Related Posts Heading 2 Add paragraph text. Click “Edit Text” to customize this theme across your site. You can update and reuse text themes.

Projects SQL Project - Supermarket Billing The goal of the project is to create a database for a supermarket to efficiently manage inventory, sales, and customer information. SQL Project - Library Management The goal of the project is to create a database for a library management system that allows users to efficiently manage customer information, book inventory & book borrowings Linux Project - Monitor Server Disk Space In this Linux project we will write a shell script that will monitor the disk space and put the info in a file every 20 mints. Setting up...

Oracle ADDM Report It analysis AWR report to identify potential performance bottle neck. For each identified issue, it locates the root cause and provides recommendations. It is created and stored every time AWR report is run provided STATISTICS_LEVEL parameter is set to TYPICAL or ALL. To create ADDM Report @$ORACLE_HOME/rdbms/admin/addmrpt.sql Sample output of ADDM report: FINDING: 59% impact (944 seconds) The buffer cache was undersized causing significant additional read I/O. RECOMMENDATION 1: DB Configuration, 59% benefit (944 seconds) ACTION: Increase SGA target size by increasing the value of parameter “sga_target” by 28 M. SYMPTOMS THAT LED TO THE FINDING: Wait class User I/O was consuming significant database time. (83% impact [1336 seconds]) Related Posts Heading 2 Add paragraph text. Click “Edit Text” to customize this theme across your site. You can update and reuse text themes.

Schedule RMAN Backup on Windows Scheduling RMAN backups on a windows machine is little different than Linux machine. Windows uses bash shell and windows scheduler to automate RMAN backups. Create RMAN Backup Script File Create Bash Shell Add Schedule to Windows Scheduler Create RMAN Backup Script File We will create a backup.cmd file with our RMAN run block for database backup run{ crosscheck backup; backup database plus archivelog; }; Create Bash Shell Create a proddb_rman_backup.bat (.bat is a windows bash shell) file which will call above file set oracle_sid=proddb rman target sys/sys@ecccatalog rman_rc/rman_rc@rcat cmdfile='F:\backup.cmd' log='F:\rman_backup_full\backup.log' Add Schedule to Windows Scheduler Schedule the above .bat script in windows scheduler Launch the Task Scheduler from start menu On the right-hand panel, click on Create Task Give a name to your task Click on Triggers tab >> New and define the schedule Make sure you set proper frequency like daily or weekly and set proper start date and time. Click on Actions tab >> New and provide the location of .bat file you created Click on OK and task will be created. You can even run the task for testing by right clicking on the task name and select Run Related Posts Heading 2 Add paragraph text. Click “Edit Text” to customize this theme across your site. You can update and reuse text themes.

Install Neo4j on Docker On Windows platform: Install Docker by going to the website https://docs.docker.com/desktop/windows/install/ and downloading the docker file. Note: A 64-bit processor and 4GB system RAM are the hardware prerequisites required to successfully run Docker on Windows 10. Double-click on the Docker Desktop Installer.exe to run the installer. Once you start the installation process, always enable Hyper-V Windows Feature on the Configuration page and follow the installation process to allow the installer and wait till the process is done. After completion of the installation process, click Close and restart your PC. Sign In the Docker https://hub.docker.com/ search for the NEO4j Open Command Prompt as Administrative Rights run the below command to pull the official image of Neo4j docker pull neo4j After completion To verify open the Docker and click the Images menu. To Run open the command prompt and run the following command. docker run -it --rm --publish=7474:7474 --publish=7687:7687 -e NEO4J_dbms_connector_https_advertised__address=":7473" -e NEO4J_dbms_connector_http_advertised__address=":7474" -e NEO4J_dbms_connector_bolt_advertised__address=":7687" --env=NEO4J_AUTH=none neo4j open the link http://localhost:7474/ in the browser and set name and password. Now you are connected to database: open terminal and test cypher command neo4j@neo4j> match (n) return count(n); On Linux platform: Installing Docker on Oracle Linux 8/7 Before we begin, run the system update command to rebuild the repo cache and update installed packages. sudo yum update Now Install some of the default packages sudo yum install -y yum-utils Add Docker repository to Oracle Linux sudo yum-config-manager --add-repo https://download.docker.com/linux/centos/docker-ce.repo Add this below entry to the /etc/yum.repos.d/docker-ce.repo file [centos-extras] name=Centos extras - $basearch baseurl=http://mirror.centos.org/centos/7/extras/x86_64 enabled=1 gpgcheck=1 gpgkey=http://centos.org/keys/RPM-GPG-KEY-CentOS-7 Now run the below command to install the extra plugin required by the docker yum -y install slirp4netns fuse-overlayfs container-selinux Lastly, to install Docker Engine, command line and contained (a standalone high-level container runtime) run, finally, this command. sudo yum install docker-ce docker-ce-cli containerd.io Enable and start the Docker service sudo systemctl start docker sudo systemctl enable docker Add User to Docker group By default, docker needs the root access to run commands, Therefore, we will add our current or the user that you want to use to access docker to the docker group. sudo groupadd docker sudo usermod -aG docker your_user Now, we have finally Installed Docker in Oracle Linux. Our Next step is to PULL the Neo4j Image. docker pull neo4j After, Image is downloaded in the docker, we are ready to run it. To verify it, we can use web browser to and open url localhost:7474 Cypher Shell If you want to run cypher shell, Run the below command docker exec -it testneo4j bash cypher-shell -u neo4j -p test Thank you. Related Posts Heading 2 Add paragraph text. Click “Edit Text” to customize this theme across your site. You can update and reuse text themes.

Send Email From Oracle Linux Server Getting an email alert from your Linux server is very important when it comes to notifying administrators. Every time a script is executed, its a great ideas to configure you Linux server to send emails. Let's look at the different ways in which you can configure email alerts using SMTP via Gmail. Gmail Pre-Requisites for SMTP Configure Mail on Linux Note: as we don't have mail server configured on Linux server, we will use gmail to send emails to recipient Gmail Pre-Requisites for SMTP We are basically using gmail account (email sender) which will send emails to recipients. For gmail to send emails, we need to make some changes. Login to your gmail https://myaccount.google.com/u/6/security and and enable 2-factor authentication, if not already done Once done, we will not generate app password so that we do not have to put our gmail password on Linux server mail configuration files Select app as Mail Select device as Other (Custom name), type postfix and click on Generate You alternate password is ready, you will be using this password going forward to setup email sending from the Linux server Our gmail account is ready to be used as email sender! Configure Mail on Linux There two methods you can use to configure email on Linux server Method 1: Send Email Using Postfix (Quick!) Method 2: Send Email Using Mailx Use any one method to configure mail on Linux, not both Method 1: Send Email Using Postfix Install postfix and other dependent packages yum install -y postfix mailx cyrus-sasl cyrus-sasl-plain Start postfix systemctl enable postfix systemctl start postfix Replace sender email and app password before executing below echo 'smtp.gmail.com : ' > /etc/postfix/sasl_passwd Set the permissions and load the sasl_passwd file chmod 600 /etc/postfix/sasl_passwd postmap hash:/etc/postfix/sasl_passwd Open postfix configuration file and paste below at the end of the file on Oracle Linux If you using gmail to send emails, just copy paste below vi /etc/postfix/main.cf #SMTP Gmail Relay for Oracle Linux smtp_sasl_auth_enable = yes smtp_sasl_password_maps = hash:/etc/postfix/sasl_passwd smtp_sasl_security_options = noanonymous smtp_tls_security_level = secure smtp_tls_mandatory_protocols = TLSv1 smtp_tls_mandatory_ciphers = high smtp_tls_secure_cert_match = nexthop smtp_tls_CAfile = /etc/pki/tls/certs/ca-bundle.crt relayhost = smtp.gmail.com:587 Only for CentOS , comment smtp_tls_security_level parameter, else email won't work #smtp_tls_security_level = secure Restart postfix systemctl restart postfix Send test email echo "Hello" | mail -s "test" Verify via below command if email is sent or stuck due to some error mailq Method 2: Send Email using Mailx This method of Linux mail setup does not work with CentOS Install Mailx package yum install -y mailx Edit /etc/mail.rc file and put below SMTP configuration at the end vi /etc/mail.rc #smtp settings set smtp=smtp://smtp.gmail.com:587 set smtp-auth=login set smtp-auth-user= set smtp-auth-password= set smtp-use-starttls set nss-config-dir=/etc/mail_certs set ssl-verify=ignore We need to setup SSL certificate so that gmail knows that emails are being sent from a trusted source. Create a certificate directory then create new certificate and key databases mkdir /etc/mail_certs certutil -N -d /etc/mail_certs --> give password Copy the cert chain for smtp.google.com:465 over to my_certs file ( CTRL + C to end ) openssl s_client -showcerts -connect smtp.gmail.com:465 > /etc/mail_certs/my_certs Open my_certs file and you will see three certs starting with --BEGIN CERTIFICATE-- and --END CERTIFICATE-- cat /etc/mail_certs/my_certs Copy the google cert (usually the first one) into a new file vi /etc/mail_certs/google --> save & close Copy the geotrust cert (usually the second one) into a new file vi /etc/mail_certs/geotrust --> save & close Copy the equifax cert (usually the third one) into a new file vi /etc/mail_certs/equifax --> save & close Start importing the google cert certutil -A -n "Google Internet Authority" -t "TC,," -d /etc/mail_certs -i /etc/mail_certs/google Start importing the geotrust cert certutil -A -n "GeoTrust Global CA" -t "TC,," -d /etc/mail_certs -i /etc/mail_certs/geotrust Start importing the equifax cert certutil -A -n "Equifax Secure Certificate Authority" -t "TCP,," -d /etc/mail_certs -i /etc/mail_certs/equifax Verify if certs are imported properly certutil -L -d /etc/mail_certs The next step is crucial as this will allow other users on Linux server to send email Give permissions on mail_certs so that other users on Linux server can send emails chmod -R 755 /etc/mail_certs/* Send test email echo "Your message" | mail -s "Subject" Enjoy! Related Posts Heading 2 Add paragraph text. Click “Edit Text” to customize this theme across your site. You can update and reuse text themes.

SQL Project - Supermarket Billing The goal of the project is to create a database for a supermarket to efficiently manage inventory, sales, and customer information. 1. Products Table creation and insert sample data: Table Creation: CREATE TABLE mkt_Products ( ProductID INT PRIMARY KEY, ProductName VARCHAR(50), Category VARCHAR(50), Price DECIMAL(10, 2), StockQuantity INT ); Insert Data: INSERT ALL INTO mkt_Products VALUES (1, 'Laptop', 'Electronics', 800.00, 50) INTO mkt_Products VALUES (2, 'Smartphone', 'Electronics', 500.00, 100) INTO mkt_Products VALUES (3, 'Desk Chair', 'Furniture', 120.00, 25) INTO mkt_Products VALUES (4, 'Coffee Maker', 'Appliances', 40.00, 30) INTO mkt_Products VALUES (5, 'Running Shoes', 'Apparel', 80.00, 50) INTO mkt_Products VALUES (6, 'Bookshelf', 'Furniture', 150.00, 20) INTO mkt_Products VALUES (7, 'Backpack', 'Accessories', 30.00, 40) INTO mkt_Products VALUES (8, 'Microwave', 'Appliances', 70.00, 15) INTO mkt_Products VALUES (9, 'Office Desk', 'Furniture', 200.00, 10) INTO mkt_Products VALUES (10, 'T-shirt', 'Apparel', 15.00, 75) SELECT * FROM dual; 2. Clients Table creation and insert sample data: Table Creation: CREATE TABLE mkt_Clients ( ClientID INT PRIMARY KEY, FirstName VARCHAR(50), LastName VARCHAR(50), Email VARCHAR(100), Phone VARCHAR(15) ); Insert Data: INSERT ALL INTO mkt_Clients VALUES (1, 'John', 'Doe', 'john.doe@example.com', '555-1234') INTO mkt_Clients VALUES (2, 'Jane', 'Smith', 'jane.smith@example.com', '555-5678') INTO mkt_Clients VALUES (3, 'Robert', 'Johnson', 'robert.j@example.com', '555-9876') INTO mkt_Clients VALUES (4, 'Emily', 'Davis', 'emily.davis@example.com', '555-4321') INTO mkt_Clients VALUES (5, 'Michael', 'Wilson', 'michael.w@example.com', '555-8765') INTO mkt_Clients VALUES (6, 'Lisa', 'Miller', 'lisa.miller@example.com', '555-2345') INTO mkt_Clients VALUES (7, 'David', 'Brown', 'david.brown@example.com', '555-6789') INTO mkt_Clients VALUES (8, 'Sarah', 'Turner', 'sarah.turner@example.com', '555-3456') INTO mkt_Clients VALUES (9, 'Kevin', 'Harris', 'kevin.harris@example.com', '555-7890') INTO mkt_Clients VALUES (10, 'Emma', 'Taylor', 'emma.t@example.com', '555-4567') SELECT * FROM dual; 3. Sales Table creation and insert sample data: Table Creation: CREATE TABLE mkt_Sales ( SaleID INT PRIMARY KEY, ProductID INT, ClientID INT, SaleDate DATE, Quantity INT, TotalAmount DECIMAL(10, 2), FOREIGN KEY (ProductID) REFERENCES mkt_Products(ProductID), FOREIGN KEY (ClientID) REFERENCES mkt_Clients(ClientID) ); Insert Data: INSERT ALL INTO mkt_Sales VALUES (1, 1, 1, TO_DATE('2024-01-01', 'YYYY-MM-DD'), 2, 1600.00) INTO mkt_Sales VALUES (2, 3, 2, TO_DATE('2024-01-03', 'YYYY-MM-DD'), 1, 120.00) INTO mkt_Sales VALUES (3, 7, 4, TO_DATE('2024-01-05', 'YYYY-MM-DD'), 3, 90.00) INTO mkt_Sales VALUES (4, 5, 5, TO_DATE('2024-01-08', 'YYYY-MM-DD'), 5, 400.00) INTO mkt_Sales VALUES (5, 9, 3, TO_DATE('2024-01-10', 'YYYY-MM-DD'), 1, 200.00) INTO mkt_Sales VALUES (6, 2, 6, TO_DATE('2024-01-12', 'YYYY-MM-DD'), 2, 1000.00) INTO mkt_Sales VALUES (7, 4, 7, TO_DATE('2024-01-15', 'YYYY-MM-DD'), 4, 160.00) INTO mkt_Sales VALUES (8, 8, 9, TO_DATE('2024-01-18', 'YYYY-MM-DD'), 1, 70.00) INTO mkt_Sales VALUES (9, 6, 8, TO_DATE('2024-01-20', 'YYYY-MM-DD'), 3, 450.00) INTO mkt_Sales VALUES (10, 10, 10, TO_DATE('2024-01-22', 'YYYY-MM-DD'), 2, 30.00) SELECT * FROM dual; 4. Suppliers Table creation and insert sample data: Table Creation: CREATE TABLE mkt_Suppliers ( SupplierID INT PRIMARY KEY, SupplierName VARCHAR(50), ContactPerson VARCHAR(50), Email VARCHAR(100), Phone VARCHAR(15), FOREIGN KEY (SupplierID) REFERENCES mkt_Sales(SaleID) ); Insert Data: INSERT ALL INTO mkt_Suppliers VALUES (1, 'Tech Distributors', 'Mark Johnson', 'info@techdist.com', '555-1111') INTO mkt_Suppliers VALUES (2, 'Furniture Mart', 'Sarah Smith', 'sales@furnituremart.com', '555-2222') INTO mkt_Suppliers VALUES (3, 'Appliance World', 'David Brown', 'support@applianceworld.com', '555-3333') INTO mkt_Suppliers VALUES (4, 'Shoe Wholesalers', 'Emily Davis', 'sales@shoewholesale.com', '555-4444') INTO mkt_Suppliers VALUES (5, 'Book Suppliers', 'Michael Wilson', 'books@suppliers.com', '555-5555') INTO mkt_Suppliers VALUES (6, 'Office Solutions', 'Lisa Miller', 'info@officesolutions.com', '555-6666') INTO mkt_Suppliers VALUES (7, 'Accessory Hub', 'Robert Johnson', 'contact@accessoryhub.com', '555-7777') INTO mkt_Suppliers VALUES (8, 'Kitchen Essentials', 'Jane Smith', 'info@kitchenessentials.com', '555-8888') INTO mkt_Suppliers VALUES (9, 'Apparel World', 'Kevin Harris', 'info@apparelworld.com', '555-9999') INTO mkt_Suppliers VALUES (10, 'Sports Gear Inc.', 'Emma Taylor', 'sales@sportsgear.com', '555-0000') SELECT * FROM dual; 5. Employee Details creation and insert sample data: Table Creation: CREATE TABLE mkt_EmployeeDetails ( EmployeeID INT PRIMARY KEY, FirstName VARCHAR(50), LastName VARCHAR(50), Email VARCHAR(100), Phone VARCHAR(15) ); Insert Data: INSERT ALL INTO mkt_EmployeeDetails VALUES (1, 'Adam', 'Johnson', 'adam.j@example.com', '555-1234') INTO mkt_EmployeeDetails VALUES (2, 'Olivia', 'Brown', 'olivia.b@example.com', '555-5678') INTO mkt_EmployeeDetails VALUES (3, 'Liam', 'Miller', 'liam.m@example.com', '555-9876') INTO mkt_EmployeeDetails VALUES (4, 'Ava', 'Davis', 'ava.d@example.com', '555-4321') INTO mkt_EmployeeDetails VALUES (5, 'Noah', 'Wilson', 'noah.w@example.com', '555-8765') INTO mkt_EmployeeDetails VALUES (6, 'Sophia', 'Smith', 'sophia.s@example.com', '555-2345') INTO mkt_EmployeeDetails VALUES (7, 'Jackson', 'Turner', 'jackson.t@example.com', '555-6789') INTO mkt_EmployeeDetails VALUES (8, 'Emma', 'Harris', 'emma.h@example.com', '555-3456') INTO mkt_EmployeeDetails VALUES (9, 'Aiden', 'Taylor', 'aiden.t@example.com', '555-7890') INTO mkt_EmployeeDetails VALUES (10, 'Grace', 'Jones', 'grace.j@example.com', '555-1111') SELECT * FROM dual; 6. Employees Table creation and insert sample data: Create mkt_Employees table with Employee_Position: CREATE TABLE mkt_Employees ( SaleID INT PRIMARY KEY, ProductID INT, ClientID INT, EmployeeID INT, SaleDate DATE, Quantity INT, Employee_Position VARCHAR(50), -- Add this column FOREIGN KEY (EmployeeID) REFERENCES mkt_EmployeeDetails(EmployeeID) ); Insert data into mkt_Employees from mkt_EmployeeDetails, mkt_Products, mkt_Clients, and mkt_Sales: INSERT INTO mkt_Employees (SaleID, ProductID, ClientID, EmployeeID, SaleDate, Quantity) SELECT s.SaleID, p.ProductID, c.ClientID, ed.EmployeeID, s.SaleDate, s.Quantity FROM mkt_Sales s JOIN mkt_Products p ON s.ProductID = p.ProductID JOIN mkt_Clients c ON s.ClientID = c.ClientID JOIN mkt_EmployeeDetails ed ON s.ClientID = ed.EmployeeID; Update the job positions in mkt_Employees: UPDATE mkt_Employees SET Employee_Position = ( CASE WHEN EmployeeID = 1 THEN 'Sales Associate' WHEN EmployeeID = 2 THEN 'Manager' WHEN EmployeeID = 3 THEN 'Customer Support' WHEN EmployeeID = 4 THEN 'Warehouse Clerk' WHEN EmployeeID = 5 THEN 'IT Specialist' WHEN EmployeeID = 6 THEN 'Marketing' WHEN EmployeeID = 7 THEN 'HR Manager' WHEN EmployeeID = 8 THEN 'Finance' WHEN EmployeeID = 9 THEN 'Logistics' WHEN EmployeeID = 10 THEN 'Research Analyst' ELSE NULL END ); Write Queries: To retrieve product details, customer information, and sales records. To generate sales details based on the product. To find remaining stock. Related Posts Heading 2 Add paragraph text. Click “Edit Text” to customize this theme across your site. You can update and reuse text themes.

How to check database backups Checking Database backups is one of the main focus areas of a DBA. From time to time, DBA needs to check database backup status and see if it's completed, failed, running, etc. Also, DBA must be able to get the backup start time, end time, and even the backup size for reference purposes. The below query gives answers to all the backup details in oracle. Query to check database backup Query to check archivelog backup Query to check database backup set linesize 500 col BACKUP_SIZE for a20 SELECT INPUT_TYPE "BACKUP_TYPE", --NVL(INPUT_BYTES/(1024*1024),0)"INPUT_BYTES(MB)", --NVL(OUTPUT_BYTES/(1024*1024),0) "OUTPUT_BYTES(MB)", STATUS, TO_CHAR(START_TIME,'MM/DD/YYYY:hh24:mi:ss') as START_TIME, TO_CHAR(END_TIME,'MM/DD/YYYY:hh24:mi:ss') as END_TIME, TRUNC((ELAPSED_SECONDS/60),2) "ELAPSED_TIME(Min)", --ROUND(COMPRESSION_RATIO,3)"COMPRESSION_RATIO", --ROUND(INPUT_BYTES_PER_SEC/(1024*1024),2) "INPUT_BYTES_PER_SEC(MB)", --ROUND(OUTPUT_BYTES_PER_SEC/(1024*1024),2) "OUTPUT_BYTES_PER_SEC(MB)", --INPUT_BYTES_DISPLAY "INPUT_BYTES_DISPLAY", OUTPUT_BYTES_DISPLAY "BACKUP_SIZE", OUTPUT_DEVICE_TYPE "OUTPUT_DEVICE" --INPUT_BYTES_PER_SEC_DISPLAY "INPUT_BYTES_PER_SEC_DIS", --OUTPUT_BYTES_PER_SEC_DISPLAY "OUTPUT_BYTES_PER_SEC_DIS" FROM V$RMAN_BACKUP_JOB_DETAILS where start_time > SYSDATE -10 and INPUT_TYPE != 'ARCHIVELOG' ORDER BY END_TIME DESC / Query to check archivelog backup set linesize 500 col BACKUP_SIZE for a20 SELECT INPUT_TYPE "BACKUP_TYPE", --NVL(INPUT_BYTES/(1024*1024),0)"INPUT_BYTES(MB)", --NVL(OUTPUT_BYTES/(1024*1024),0) "OUTPUT_BYTES(MB)", STATUS, TO_CHAR(START_TIME,'MM/DD/YYYY:hh24:mi:ss') as START_TIME, TO_CHAR(END_TIME,'MM/DD/YYYY:hh24:mi:ss') as END_TIME, TRUNC((ELAPSED_SECONDS/60),2) "ELAPSED_TIME(Min)", --ROUND(COMPRESSION_RATIO,3)"COMPRESSION_RATIO", --ROUND(INPUT_BYTES_PER_SEC/(1024*1024),2) "INPUT_BYTES_PER_SEC(MB)", --ROUND(OUTPUT_BYTES_PER_SEC/(1024*1024),2) "OUTPUT_BYTES_PER_SEC(MB)", --INPUT_BYTES_DISPLAY "INPUT_BYTES_DISPLAY", OUTPUT_BYTES_DISPLAY "BACKUP_SIZE", OUTPUT_DEVICE_TYPE "OUTPUT_DEVICE" --INPUT_BYTES_PER_SEC_DISPLAY "INPUT_BYTES_PER_SEC_DIS", --OUTPUT_BYTES_PER_SEC_DISPLAY "OUTPUT_BYTES_PER_SEC_DIS" FROM V$RMAN_BACKUP_JOB_DETAILS where start_time > SYSDATE -10 and INPUT_TYPE = 'ARCHIVELOG' ORDER BY END_TIME DESC / Related Posts Heading 2 Add paragraph text. Click “Edit Text” to customize this theme across your site. You can update and reuse text themes.

Shell Script to Check Vowels Linux script allows the user to use the case command that accepts the input from the user and match the answer. To elaborate this we are asking the question Enter any vowel and the script will accept the answer in both upper case and lower case. vi vowel_check.sh # !bin/bash echo Enter any vowel? read response case $response in A) echo $response is correct ;; a) echo $response is correct ;; E) echo $response is correct ;; e) echo $response is correct ;; I) echo $response is correct ;; i) echo $response is correct ;; O) echo $response is correct ;; o) echo $response is correct ;; U) echo $response is correct ;; u) echo $response is correct ;; *) echo sorry, that is wrong esac Here is the sample output of the above script More ways to use case You can use the case command to accept the user input and match the case You can ask for user input and evaluate it using case command and returns the response Related Posts Heading 2 Add paragraph text. Click “Edit Text” to customize this theme across your site. You can update and reuse text themes.