Oracle Tutorials

In this Blog you can learn database, networking and cloud skills that will help you to understand Oracle Database and Non Oracle Database with extra key skill

Showing posts with label Oracle Database 11g. Show all posts

Practice SQL with Sample Tables

August 09, 2019 No comments :

Image result for SQL table
CREATE TABLE  "AGENTS"
   (
    "AGENT_CODE" CHAR(6) NOT NULL PRIMARY KEY,
        "AGENT_NAME" CHAR(40),
        "WORKING_AREA" CHAR(35),
        "COMMISSION" NUMBER(10,2),
        "PHONE_NO" CHAR(15),
        "COUNTRY" VARCHAR2(25)
         );

INSERT INTO AGENTS VALUES ('A007', 'Ramasundar', 'Bangalore', '0.15', '077-25814763', '');
INSERT INTO AGENTS VALUES ('A003', 'Alex ', 'London', '0.13', '075-12458969', '');
INSERT INTO AGENTS VALUES ('A008', 'Alford', 'New York', '0.12', '044-25874365', '');
INSERT INTO AGENTS VALUES ('A011', 'Ravi Kumar', 'Bangalore', '0.15', '077-45625874', '');
INSERT INTO AGENTS VALUES ('A010', 'Santakumar', 'Chennai', '0.14', '007-22388644', '');
INSERT INTO AGENTS VALUES ('A012', 'Lucida', 'San Jose', '0.12', '044-52981425', '');
INSERT INTO AGENTS VALUES ('A005', 'Anderson', 'Brisban', '0.13', '045-21447739', '');
INSERT INTO AGENTS VALUES ('A001', 'Subbarao', 'Bangalore', '0.14', '077-12346674', '');
INSERT INTO AGENTS VALUES ('A002', 'Mukesh', 'Mumbai', '0.11', '029-12358964', '');
INSERT INTO AGENTS VALUES ('A006', 'McDen', 'London', '0.15', '078-22255588', '');
INSERT INTO AGENTS VALUES ('A004', 'Ivan', 'Torento', '0.15', '008-22544166', '');
INSERT INTO AGENTS VALUES ('A009', 'Benjamin', 'Hampshair', '0.11', '008-22536178', '');


CREATE TABLE  "CUSTOMER"
   (    "CUST_CODE" VARCHAR2(6) NOT NULL PRIMARY KEY,
        "CUST_NAME" VARCHAR2(40) NOT NULL,
        "CUST_CITY" CHAR(35),
        "WORKING_AREA" VARCHAR2(35) NOT NULL,
        "CUST_COUNTRY" VARCHAR2(20) NOT NULL,
        "GRADE" NUMBER,
        "OPENING_AMT" NUMBER(12,2) NOT NULL,
        "RECEIVE_AMT" NUMBER(12,2) NOT NULL,
        "PAYMENT_AMT" NUMBER(12,2) NOT NULL,
        "OUTSTANDING_AMT" NUMBER(12,2) NOT NULL,
        "PHONE_NO" VARCHAR2(17) NOT NULL,
        "AGENT_CODE" CHAR(6) NOT NULL REFERENCES AGENTS
);

INSERT INTO CUSTOMER VALUES ('C00013', 'Holmes', 'London', 'London', 'UK', '2', '6000.00', '5000.00', '7000.00', '4000.00', 'BBBBBBB', 'A003');
INSERT INTO CUSTOMER VALUES ('C00001', 'Micheal', 'New York', 'New York', 'USA', '2', '3000.00', '5000.00', '2000.00', '6000.00', 'CCCCCCC', 'A008');
INSERT INTO CUSTOMER VALUES ('C00020', 'Albert', 'New York', 'New York', 'USA', '3', '5000.00', '7000.00', '6000.00', '6000.00', 'BBBBSBB', 'A008');
INSERT INTO CUSTOMER VALUES ('C00025', 'Ravindran', 'Bangalore', 'Bangalore', 'India', '2', '5000.00', '7000.00', '4000.00', '8000.00', 'AVAVAVA', 'A011');
INSERT INTO CUSTOMER VALUES ('C00024', 'Cook', 'London', 'London', 'UK', '2', '4000.00', '9000.00', '7000.00', '6000.00', 'FSDDSDF', 'A006');
INSERT INTO CUSTOMER VALUES ('C00015', 'Stuart', 'London', 'London', 'UK', '1', '6000.00', '8000.00', '3000.00', '11000.00', 'GFSGERS', 'A003');
INSERT INTO CUSTOMER VALUES ('C00002', 'Bolt', 'New York', 'New York', 'USA', '3', '5000.00', '7000.00', '9000.00', '3000.00', 'DDNRDRH', 'A008');
INSERT INTO CUSTOMER VALUES ('C00018', 'Fleming', 'Brisban', 'Brisban', 'Australia', '2', '7000.00', '7000.00', '9000.00', '5000.00', 'NHBGVFC', 'A005');
INSERT INTO CUSTOMER VALUES ('C00021', 'Jacks', 'Brisban', 'Brisban', 'Australia', '1', '7000.00', '7000.00', '7000.00', '7000.00', 'WERTGDF', 'A005');
INSERT INTO CUSTOMER VALUES ('C00019', 'Yearannaidu', 'Chennai', 'Chennai', 'India', '1', '8000.00', '7000.00', '7000.00', '8000.00', 'ZZZZBFV', 'A010');
INSERT INTO CUSTOMER VALUES ('C00005', 'Sasikant', 'Mumbai', 'Mumbai', 'India', '1', '7000.00', '11000.00', '7000.00', '11000.00', '147-25896312', 'A002');
INSERT INTO CUSTOMER VALUES ('C00007', 'Ramanathan', 'Chennai', 'Chennai', 'India', '1', '7000.00', '11000.00', '9000.00', '9000.00', 'GHRDWSD', 'A010');
INSERT INTO CUSTOMER VALUES ('C00022', 'Avinash', 'Mumbai', 'Mumbai', 'India', '2', '7000.00', '11000.00', '9000.00', '9000.00', '113-12345678','A002');
INSERT INTO CUSTOMER VALUES ('C00004', 'Winston', 'Brisban', 'Brisban', 'Australia', '1', '5000.00', '8000.00', '7000.00', '6000.00', 'AAAAAAA', 'A005');
INSERT INTO CUSTOMER VALUES ('C00023', 'Karl', 'London', 'London', 'UK', '0', '4000.00', '6000.00', '7000.00', '3000.00', 'AAAABAA', 'A006');

INSERT INTO CUSTOMER VALUES ('C00006', 'Shilton', 'Torento', 'Torento', 'Canada', '1', '10000.00', '7000.00', '6000.00', '11000.00', 'DDDDDDD', 'A004');
INSERT INTO CUSTOMER VALUES ('C00010', 'Charles', 'Hampshair', 'Hampshair', 'UK', '3', '6000.00', '4000.00', '5000.00', '5000.00', 'MMMMMMM', 'A009');
INSERT INTO CUSTOMER VALUES ('C00017', 'Srinivas', 'Bangalore', 'Bangalore', 'India', '2', '8000.00', '4000.00', '3000.00', '9000.00', 'AAAAAAB', 'A007');
INSERT INTO CUSTOMER VALUES ('C00012', 'Steven', 'San Jose', 'San Jose', 'USA', '1', '5000.00', '7000.00', '9000.00', '3000.00', 'KRFYGJK', 'A012');
INSERT INTO CUSTOMER VALUES ('C00008', 'Karolina', 'Torento', 'Torento', 'Canada', '1', '7000.00', '7000.00', '9000.00', '5000.00', 'HJKORED', 'A004');
INSERT INTO CUSTOMER VALUES ('C00003', 'Martin', 'Torento', 'Torento', 'Canada', '2', '8000.00', '7000.00', '7000.00', '8000.00', 'MJYURFD', 'A004');
INSERT INTO CUSTOMER VALUES ('C00009', 'Ramesh', 'Mumbai', 'Mumbai', 'India', '3', '8000.00', '7000.00', '3000.00', '12000.00', 'Phone No', 'A002');
INSERT INTO CUSTOMER VALUES ('C00014', 'Rangarappa', 'Bangalore', 'Bangalore', 'India', '2', '8000.00', '11000.00', '7000.00', '12000.00', 'AAAATGF', 'A001');
INSERT INTO CUSTOMER VALUES ('C00016', 'Venkatpati', 'Bangalore', 'Bangalore', 'India', '2', '8000.00', '11000.00', '7000.00', '12000.00', 'JRTVFDD', 'A007');
INSERT INTO CUSTOMER VALUES ('C00011', 'Sundariya', 'Chennai', 'Chennai', 'India', '3', '7000.00', '11000.00', '7000.00', '11000.00', 'PPHGRTS', 'A010');

CREATE TABLE  "ORDERS"
   (
        "ORD_NUM" NUMBER(6,0) NOT NULL PRIMARY KEY,
        "ORD_AMOUNT" NUMBER(12,2) NOT NULL,
        "ADVANCE_AMOUNT" NUMBER(12,2) NOT NULL,
        "ORD_DATE" DATE NOT NULL,
        "CUST_CODE" VARCHAR2(6) NOT NULL REFERENCES CUSTOMER,
        "AGENT_CODE" CHAR(6) NOT NULL REFERENCES AGENTS,
        "ORD_DESCRIPTION" VARCHAR2(60) NOT NULL
   );



INSERT INTO ORDERS VALUES('200100', '1000.00', '600.00', '08-JAN-08', 'C00013', 'A003', 'SOD');
INSERT INTO ORDERS VALUES('200110', '3000.00', '500.00', '04-FEB-08', 'C00019', 'A010', 'SOD');
INSERT INTO ORDERS VALUES('200107', '4500.00', '900.00', '08-MAR-08', 'C00007', 'A010', 'SOD');
INSERT INTO ORDERS VALUES('200112', '2000.00', '400.00', '05-APR-08', 'C00016', 'A007', 'SOD');
INSERT INTO ORDERS VALUES('200113', '4000.00', '600.00', '06-MAY-08', 'C00022', 'A002', 'SOD');
INSERT INTO ORDERS VALUES('200102', '2000.00', '300.00', '05-JUN-08', 'C00012', 'A012', 'SOD');
INSERT INTO ORDERS VALUES('200114', '3500.00', '2000.00', '08-JUL-08', 'C00002', 'A008', 'SOD');
INSERT INTO ORDERS VALUES('200122', '2500.00', '400.00', '09-AUG-08', 'C00003', 'A004', 'SOD');
INSERT INTO ORDERS VALUES('200118', '500.00', '100.00', '07-SEP-08', 'C00023', 'A006', 'SOD');
INSERT INTO ORDERS VALUES('200119', '4000.00', '700.00', '09-OCT-08', 'C00007', 'A010', 'SOD');
INSERT INTO ORDERS VALUES('200121', '1500.00', '600.00', '09-NOV-08', 'C00008', 'A004', 'SOD');
INSERT INTO ORDERS VALUES('200130', '2500.00', '400.00', '07-DEC-08', 'C00025', 'A011', 'SOD');
INSERT INTO ORDERS VALUES('200134', '4200.00', '1800.00', '09-JAN-08', 'C00004', 'A005', 'SOD');
INSERT INTO ORDERS VALUES('200108', '4000.00', '600.00', '02-FEB-08', 'C00008', 'A004', 'SOD');
INSERT INTO ORDERS VALUES('200103', '1500.00', '700.00', '05-MAR-08', 'C00021', 'A005', 'SOD');
INSERT INTO ORDERS VALUES('200105', '2500.00', '500.00', '07-APR-08', 'C00025', 'A011', 'SOD');
INSERT INTO ORDERS VALUES('200109', '3500.00', '800.00', '07-MAY-08', 'C00011', 'A010', 'SOD');
INSERT INTO ORDERS VALUES('200101', '3000.00', '1000.00', '07-JUN-08', 'C00001', 'A008', 'SOD');
INSERT INTO ORDERS VALUES('200111', '1000.00', '300.00', '07-JUL-08', 'C00020', 'A008', 'SOD');
INSERT INTO ORDERS VALUES('200104', '1500.00', '500.00', '03-AUG-08', 'C00006', 'A004', 'SOD');
INSERT INTO ORDERS VALUES('200106', '2500.00', '700.00', '04-SEP-08', 'C00005', 'A002', 'SOD');
INSERT INTO ORDERS VALUES('200125', '2000.00', '600.00', '10-OCT-08', 'C00018', 'A005', 'SOD');
INSERT INTO ORDERS VALUES('200117', '800.00', '200.00', '10-NOV-08', 'C00014', 'A001', 'SOD');
INSERT INTO ORDERS VALUES('200123', '500.00', '100.00', '09-DEC-08', 'C00022', 'A002', 'SOD');
INSERT INTO ORDERS VALUES('200120', '500.00', '100.00', '07-JAN-08', 'C00009', 'A002', 'SOD');
INSERT INTO ORDERS VALUES('200116', '500.00', '100.00', '07-FEB-08', 'C00010', 'A009', 'SOD');
INSERT INTO ORDERS VALUES('200124', '500.00', '100.00', '06-MAR-08', 'C00017', 'A007', 'SOD');
INSERT INTO ORDERS VALUES('200126', '500.00', '100.00', '06-APR-08', 'C00022', 'A002', 'SOD');
INSERT INTO ORDERS VALUES('200129', '2500.00', '500.00', '07-MAY-08', 'C00024', 'A006', 'SOD');
INSERT INTO ORDERS VALUES('200127', '2500.00', '400.00', '07-JUN-08', 'C00015', 'A003', 'SOD');
INSERT INTO ORDERS VALUES('200128', '3500.00', '1500.00', '07-JUL-08', 'C00009', 'A002', 'SOD');
INSERT INTO ORDERS VALUES('200135', '2000.00', '800.00', '09-AUG-08', 'C00007', 'A010', 'SOD');
INSERT INTO ORDERS VALUES('200131', '900.00', '150.00', '08-SEP-08', 'C00012', 'A012', 'SOD');
INSERT INTO ORDERS VALUES('200133', '1200.00', '400.00', '06-OCT-08', 'C00009', 'A002', 'SOD');

Sample Table File_2 

Patching in Oracle Databases using Opatch Utility

April 02, 2019 No comments :
Image result for oracle opatch
  • OPatch is an Oracle-supplied utility that assists you with the process of applying interim patches to Oracle's software and rolling back interim patches from Oracle's software. 
  • Patches are a small collection of files copied over to an existing installation and associated with particular versions of Oracle products.
  • Interim patches are bug fixes available to customers in response to specific bugs and They require a particular base release or patchset to be installed before you can apply them, generally address specific bugs for a particular customer.
  • OPatch also supports silent operation. This mode allows you to run the software without any user interaction.
  • OPatch has various levels of logging and tracing mechanisms. It also has a debug option that helps to easily diagnose software problems.



 OPatch supports the following tasks
1- Applying an interim patch.
2- Rolling back the application of an interim patch.
3- Detecting a conflict when applying an interim patch after previous interim patches have been applied. It also suggests the best options to resolve a conflict.
4- Reporting on installed products and interim patches.

OPatch uses the following environment variables
ORACLE_HOME — Oracle home location.
export ORACLE_HOME=/u04/oracle/product/database/12102
PATH — Path information.
export PATH=$ORACLE_HOME/OPatch:$PATH

 Required:
Oracle Home must to set to valid location
Java commands for Windows, and ar, cp, fuser, and make commands for UNIX must be made available

 Prerequisite Checks for OPatch
1- Check ORACLE_HOME and Environment Variable
2- Check for JRE
3- Check for Oracle Universal Installer and OPatch Version Compatibility
4- Check for OPatch Lsinventory : OPatch lsinventory -detail

 Note: It is highly recommended before applying the patch , database must be down and back up the ORACLE_HOME before any patch operation

[oracle@dgprim2 ~]$ opatch lsinventory
Oracle Interim Patch Installer version 12.2.0.1.13
Copyright (c) 2019, Oracle Corporation.  All rights reserved.
Oracle Home       : /u04/oracle/product/database/12102
Central Inventory : /u03/app/oracle/oraInventory
   from           : /u04/oracle/product/database/12102/oraInst.loc
OPatch version    : 12.2.0.1.13
OUI version       : 12.1.0.2.0
Log file location : /u04/oracle/product/database/12102/cfgtoollogs/opatch/opatch2019-04-02_11-47-29AM_1.log

 Lsinventory Output file location : /u04/oracle/product/database/12102/cfgtoollogs/opatch/lsinv/lsinventory2019-04-02_11-47-29AM.txt

 --------------------------------------------------------------------------------
Local Machine Information::
Hostname: dgprim2.transappdata.com
ARU platform id: 226
ARU platform description:: Linux x86-64

 Installed Top-level Products (1):

 Oracle Database 12c                                                  12.1.0.2.0
There are 1 products installed in this Oracle Home.

 There are no Interim patches installed in this Oracle Home.
--------------------------------------------------------------------------------
OPatch succeeded.

 Location of Opatch Utility: $ORACLE_HOME/OPatch

 syntax: opatch [-help] [command] [-option]

 help — Displays the help message for the command.
$ opatch -help

 Apply Command for OUI-based Oracle Homes
opatch napply Command for OUI-based Oracle Homes
opatch napply <patch_location>
opatch napply <patch_location> -id 1,2,3
opatch napply <patch_location> -skip_subset -skip_duplicate
opatch napply <patch_location> -id 1,2,3 -skip_subset -skip_duplicate

u

Clone an Oracle Database using controlfile

March 29, 2019 No comments :

Clone of Oracle Database with new database name using control file

Source Database Name: DG731124 
Source Database Version: 11.2.0.4 Standard Edition
Source Datafile location: /u03/app/oracle/oradata/DG731124/
Source Logfile location: /u03/app/oracle/oradata/DG731124/
Target location of datafile and logfile: /u04/testdir/
Target Database name: TCLONE

SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u03/app/oracle/oradata/DG731124/system01.dbf
/u03/app/oracle/oradata/DG731124/sysaux01.dbf
/u03/app/oracle/oradata/DG731124/undotbs01.dbf
/u03/app/oracle/oradata/DG731124/users01.dbf
/u03/app/oracle/oradata/DG731124/test011.dbf

SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u03/app/oracle/oradata/DG731124/redo03.log
/u03/app/oracle/oradata/DG731124/redo02.log
/u03/app/oracle/oradata/DG731124/redo01.log
SQL> create pfile='/tmp/initTCLONE.ora' from spfile;

Add tablespace, user and table to test the cloning of the database.
Connect to source database
Create tablespace test01 
SQL> create tablespace test01 datafile '/u03/app/oracle/oradata/DG731124/test011.dbf' size 15M;
Add user 'u11' to the database
SQL> create user u11 identified by ******;
SQL> grant connect ,resource , create session to u11;
SQL> alter user u11 quota unlimited on test01;
SQL> conn u11; 
Now user can create database object.
SQL> select * from tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE
EMP                            TABLE
SQL> alter system switch logfile;
Create control file 
SQL> alter database backup controlfile to trace;
SQL> shut immediate;
Go to the trace file location and rename recently create trace file as control.sql

cd /u03/app/oracle/diag/rdbms/dg731124/DG731124/trace
ls -lrt
cp DG731124_ora_12252.trc  control.sql 

Remove unwanted lines from the control file keep information like this:
--------------------------------------------------------------------------------------------------------
CREATE CONTROLFILE SET DATABASE "TCLONE" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u04/testdir/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u04/testdir/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u04/testdir/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u04/testdir/system01.dbf',
  '/u04/testdir/sysaux01.dbf',
  '/u04/testdir/undotbs01.dbf',
  '/u04/testdir/users01.dbf',
  '/u04/testdir/test011.dbf'
CHARACTER SET AL32UTF8
;
RECOVER DATABASE;
-- Database can now be opened normally.
--ALTER DATABASE OPEN;
--ALTER TABLESPACE TEMP ADD TEMPFILE '/u04/testdir/temp01.dbf'
--     SIZE 30408704  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
----------------------------------------------------------------------------------------------------------
1- Use set option in create control file to set new name of the database.
2- Replace the old path of datafile and logfile with new location.
3- Edit the initTCLONE.ora replace the path and create following directory as described in pfile.
4- Shut down the source database.
5- copy the datafile, logfile, control file and pfile from source to target location '/u04/testdir/ 
-------------------------------------------------------------------------------------------------------------
export the oracle_sid for new database instance
# export ORACLE_SID=TCLONE
Start database in nomount state using pfile:
SQL>  startup nomount pfile='/u04/testdir/initTCLONE.ora'; 
Execute the control file 
SQL> @/u04/testdir/control.sql
Control file created.

ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done.

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 1258136 generated at 03/29/2019 10:47:01 needed for thread 1
ORA-00289: suggestion :
/u04/testdir/fast_recovery_area/TCLONE/archivelog/2019_03_29/o1_mf_1_9_%u_.arc
ORA-00280: change 1258136 for thread 1 is in sequence #9

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u04/testdir/redo03.log
Log applied.
Media recovery complete.

Note:  Apply the logfile until the Media recovery get completed.
SQL> alter database open resetlogs;
Database altered.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u04/testdir/temp01.dbf' SIZE 30408704  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

Clone database TCLONE get created in different location.

SQL> select name from v$database;
NAME
---------
TCLONE

SQL> SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u04/testdir/system01.dbf
/u04/testdir/sysaux01.dbf
/u04/testdir/undotbs01.dbf
/u04/testdir/users01.dbf
/u04/testdir/test011.dbf

SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u04/testdir/redo03.log
/u04/testdir/redo02.log
/u04/testdir/redo01.log

SQL> conn u11;
SQL> select * from tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE
EMP                            TABLE


Introduction to Oracle Data Guard

March 09, 2019 No comments :





Oracle Database Startup phase

February 20, 2019 1 comment :
Oracle Database Startup Phase

Steps to Drop Database

February 07, 2019 No comments :
Steps to Drop Database

1- Export database SID : export ORACLE_SID=database
2- Connect as sysdba  : sqlplus / as sysdba
3- Start The database : SQL> startup
4- Shutdown the database : SQL> shutdown immediate;
5- Start in Exclusive mode : SQL> startup mount exclusive restrict ;
6- Drop the database : SQL> drop database;

Parameters used in Dataguard

February 07, 2019 No comments :
IMPORTANT PARAMETERS USED IN DG [DATAGUARD]
--------------------------------------------
.db_unique_name='physical'
.log_archive_config='DG_CONFIG=(physical,standby)'
.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES)'
.log_archive_dest_2='SERVICE=standby ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby'
.standby_file_management='AUTO'
.fal_server='standby'
Subscribe to: Posts ( Atom )

Recent Posts

Contact Form

Name

Email *

Message *