BASIC SQL REFERENCE FOR ORACLE DBA

OVERVIEW OF SQL

Please create the scott schema from the following GITHUB repository

https://github.com/oracle/dotnet-db-samples/blob/master/schemas/scott.sql

EXECUTING SELECT STATEMENT

SQL> SELECT * FROM DEPT;

DEPTNO DNAME LOC
———- ————– ————-
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

HANDLING NULL VALUES

SQL> SELECT EMPNO,ENAME,NVL(MGR,0) FROM EMP;

EMPNO ENAME NVL(MGR,0)
———- ———- ———-
7369 SMITH 7902
7499 ALLEN 7698
4567 WARD 1234
7566 JONES 7839
7654 MARTIN 7698
7698 BLAKE 7839
7782 CLARK 7839
7788 SCOTT 7566
7839 KING 0
7844 TURNER 7698
7876 ADAMS 7788

THE DISTINCT KEYWORD

SQL> SELECT DISTINCT MGR FROM EMP;

MGR
———-
1234
7566
7698
7782
7788
7839
7902
8 rows selected.

CHANGING OUTPUT HEADINGS WITH ALIASES

SQL> SELECT EMPNO EMPLOYEEID,ENAME,NVL(MGR,0) AS MGR FROM EMP;

EMPLOYEEID ENAME MGR
———- ———- ———-
7369 SMITH 7902
7499 ALLEN 7698
4567 WARD 1234
7566 JONES 7839
7654 MARTIN 7698
7698 BLAKE 7839
7782 CLARK 7839
7788 SCOTT 7566
7839 KING 0
7844 TURNER 7698
7876 ADAMS 7788

7900 JAMES 7698
7902 FORD 7566
7934 MILLER 7782

PUTTING COLUMN TOGETHER WITH CONCATENATION

SQL>SELECT ENAME||’,WHO IS THE’||CONCAT(JOB,’FOR THE COMPANY’) “NAME AND ROLE”
FROM EMP

NAME AND ROLE
—————————————
SMITH,WHO IS THECLERKFOR THE COMPANY
ALLEN,WHO IS THESALESMANFOR THE COMPANY
WARD,WHO IS THESALESMANFOR THE COMPANY
JONES,WHO IS THEMANAGERFOR THE COMPANY
MARTIN,WHO IS THESALESMANFOR THE COMPAN
BLAKE,WHO IS THEMANAGERFOR THE COMPANY
CLARK,WHO IS THEMANAGERFOR THE COMPANY
SCOTT,WHO IS THEANALYSTFOR THE COMPANY
KING,WHO IS THEPRESIDENTFOR THE COMPANY
TURNER,WHO IS THESALESMANFOR THE COMPAN
ADAMS,WHO IS THECLERKFOR THE COMPANY

NAME AND ROLE
—————————————
JAMES,WHO IS THECLERKFOR THE COMPANY
FORD,WHO IS THEANALYSTFOR THE COMPANY
MILLER,WHO IS THECLERKFOR THE COMPANY

OTHER SQL*PLUS COMMAND TO KNOW

SQL> desc emp;

Name Null? Type
—————————————– ——– —————————-

EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

SQL>list
SQL>DEL 3 (DELETE THE LINE NUMBER 3 FROM BUFFER)
SQL>CLEAR BUFFER;
SQL>RUN SELECT * FROM EMP;
SQL>SAVE DEB.SQL
SQL>SPO ON
SQL>SELECT * FROM EMP;
SQL>SPO OFF
SQL>ED ON.LST
SQL>C/SALARY/SAL;( CHANGING MISTAKEN WORD)
SQL>@EMP;( EXECUTING SQL)
SQL>STA EMP;( EXECUTING SQL)

LIMITING ,STORING AND MANIPULATING RETURN DATA

SORTING RETURN DATA WITH THE ORDER BY CLAUSE

SQL>SELECT EMPNO,ENAME,SAL FROM EMP ORDER BY ENAME ASC;     (DEFAULT IS ASC)

EMPNO ENAME SAL
———- ———- ———-
7876 ADAMS 1100
7499 ALLEN 1600
7698 BLAKE 2850
7782 CLARK 2450
7902 FORD 3000
7900 JAMES 950
7566 JONES 2975
7839 KING 5000
7654 MARTIN 1250
7934 MILLER 1300
7788 SCOTT 3000

EMPNO ENAME SAL
———- ———- ———-
7369 SMITH 800
7844 TURNER 1500
4567 WARD

14 ROWS SELECTED.

SQL> SELECT ENAME FROM EMP ORDER BY ENAME DESC;

ENAME
———-
WARD
TURNER
SMITH
SCOTT
MILLER
MARTIN
KING
JONES
JAMES
FORD
CLARK

ENAME
———-
BLAKE
ALLEN
ADAMS

14 ROWS SELECTED.

SQL> SELECT ENAME,JOB FROM EMP ORDER BY 2 DESC;(2 DENOTES THE SECOND COLUMN)

ENAME JOB
———- ———
ALLEN SALESMAN
WARD SALESMAN
TURNER SALESMAN
MARTIN SALESMAN
KING PRESIDENT
BLAKE MANAGER
JONES MANAGER
CLARK MANAGER
MILLER CLERK
SMITH CLERK
ADAMS CLERK

ENAME JOB
———- ———
JAMES CLERK
FORD ANALYST
SCOTT ANALYST

14 rows selected.

USE OF WHERE CLAUSE TO LIMIT DATA

SQL> SELECT EMPNO AS EID FROM EMP WHERE SAL IN(800,3000);

EID
———
7369
7788
7902

SQL> SELECT ENAME FROM EMP WHERE SAL BETWEEN 1500 AND 3000;

ENAME
———-
ALLEN
JONES
BLAKE
CLARK
SCOTT
TURNER
FORD

SQL> SELECT * FROM EMP WHERE JOB LIKE ‘_LE%’

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
——— ———- ——— ——— ——— ——— ——— ———
7369 SMITH CLERK 7902 17-DEC-80 800 20
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7934 MILLER CLERK 7782 23-JAN-82 1300 10

SQL>SELECT ENAME,COMM FROM EMP ORDER BY COMM DESC NULLS LAST;

ENAME COMM
———- ———-
MARTIN 1400
WARD 500
ALLEN 300
TURNER 0
SCOTT
KING
ADAMS
JAMES
FORD
MILLER
BLAKE

ENAME COMM
———- ———-
JONES
SMITH
CLARK

USING SINGLE ROW FUNCTIONS

STRING FUNCTION

SQL>SELECT CHR(65) FROM DUAL;

CHR(
—-
A

SQL>SELECT CONCAT(‘DEBASIS’,’MAITY’) FROM DUAL;

CONCAT(‘DEBA
————
DEBASISMAITY

SQL> SELECT INITCAP(‘deb’) FROM DUAL;

INI

Deb

SQL> SELECT LENGTH(‘DEBASIS MAITY’) FROM DUAL;

LENGTH(‘DEBASISMAITY’)
———————-
13

SQL> SELECT LOWER(‘DEB’),UPPER(‘deb’) FROM DUAL;

LOW UPP
— —
deb DEB

SQL> SELECT LPAD(ENAME,10,’-‘),RPAD(ENAME,10,’.’) FROM EMP WHERE ROWNUM<5;

LPAD(ENAME RPAD(ENAME
———- ———-
—–SMITH SMITH…..
—–ALLEN ALLEN…..
——WARD WARD……
—–JONES JONES…..

SQL> SELECT LTRIM(‘DEBASIS’,’DEB’) FROM DUAL;

LTRI
—-
ASIS

SQL> SELECT REPLACE(‘DEBASIS’,’DEB’,’RUBU’) FROM DUAL;

REPLACE(
——–
RUBUASIS

SQL> SELECT SUBSTR(‘DEBASIS’,1,3) FROM DUAL;

SUB

DEB

SQL> SELECT SUBSTR(‘DEBASIS’,-3,2) FROM DUAL;

SU

SI

The Oracle TRANSLATE() function returns a string with all occurrences of each character in a string replaced by its corresponding character in another string.

The TRANSLATE() function allows you to make several single-character, one-to-one translations or substitutions in one operation.

SQL> SELECT
TRANSLATE( ‘[127.8, 75.6]’, ‘[,]’, ‘( )’ ) Point,
TRANSLATE( ‘(127.8 75.6)’, ‘( )’, ‘[,]’ ) Coordinates
FROM
dual;

POINT COORDINATES
——————————————————————————————————————–
(127.8 75.6) [127.8,75.6]

SQL> select * from emp where soundex(job)=soundex(‘cleark’);

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
——— ———- ——— ——— ——— ——— ——— ———
7369 SMITH CLERK 7902 17-DEC-80 800 20
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7934 MILLER CLERK 7782 23-JAN-82 1300 10

NOTE:TRIM=LTRIM(RTRIM(COLUMN NAME))

SQL>SELECT trim(‘S’ from ‘DEBASIS’ ) FROM DUAL;

DEBASI

NUMBER FUNCTION

SQL> SELECT ABS(-23),ABS(23) FROM DUAL;

ABS(-23) ABS(23)
——— ———
23 23
SQL> SELECT CEIL(-23.45) FROM DUAL;

CEIL(-23.45)
————
-23

SQL> SELECT FLOOR(-23.45),FLOOR(23.56),FLOOR(90.98),FLOOR(23.6) FROM DUAL;

FLOOR(-23.45) FLOOR(23.56) FLOOR(90.98) FLOOR(23.6)
————- ———— ———— ———–
-24 23 90 23

SQL> SELECT MOD(12,5) FROM DUAL;(12%5=2)

MOD(12,5)
———
2
SQL> SELECT POWER(5,2) FROM DUAL;

POWER(5,2)
———-
25
SQL> SELECT SQRT(25) FROM DUAL;

SQRT(25)
———
5
SQL> SELECT TRUNC(23.345,1) FROM DUAL;

TRUNC(23.345,1)
—————
23.3

DATE FUNCTION

SQL> SELECT SYSDATE,ADD_MONTHS(SYSDATE,2) FROM DUAL;

SYSDATE ADD_MONTH
——— ———
17-OCT-03 17-DEC-03

SQL> SELECT LAST_DAY(SYSDATE) FROM DUAL;

LAST_DAY(
———
31-OCT-03

SQL> SELECT MONTHS_BETWEEN(’17-OCT-05′,’31-OCT-2005′) FROM DUAL;

MONTHS_BETWEEN(’17-OCT-05′,’31-OCT-2005′)
—————————————–
-.4516129

SQL> SELECT SYSDATE,NEXT_DAY(SYSDATE,’MONDAY’) FROM DUAL;

SYSDATE NEXT_DAY(
——— ———
17-OCT-03 20-OCT-03

SQL>SELECT TO_CHAR(SYSDATE,’DD-MONTH-YEAR:HH-MI’) FROM DUAL

TO_CHAR(SYSDATE,’DD-MONTH-YEAR:HH-MI’)
————————————————————-
17-OCTOBER -TWO THOUSAND THREE:10-50

SQL> select sysdate,round(sysdate) from dual;

SYSDATE ROUND(SYS
——— ———
17-JAN-09 18-JAN-09

SQL> select sysdate,trunc(sysdate) from dual;

SYSDATE TRUNC(SYS
——— ———
17-JAN-09 17-JAN-09

SQL> select sysdate,round(sysdate,’MONTH’) from dual;

SYSDATE ROUND(SYS
——— ———
17-JAN-09 01-FEB-09

SQL> select sysdate,trunc(sysdate,’MONTH’) from dual;

SYSDATE TRUNC(SYS
——— ———
17-JAN-09 01-JAN-09

SQL> select sysdate,round(sysdate,’YEAR’) from dual;

SYSDATE ROUND(SYS
——— ———
17-JAN-09 01-JAN-09

SQL> select sysdate,trunc(sysdate,’YEAR’) from dual;

SYSDATE TRUNC(SYS
——— ———
17-JAN-09 01-JAN-09

SQL> select sysdate,round(sysdate,’DAY’) from dual;

SYSDATE ROUND(SYS
——— ———
17-JAN-09 18-JAN-09

SQL> select sysdate,trunc(sysdate,’DAY’) from dual;

SYSDATE TRUNC(SYS
——— ———
17-JAN-09 11-JAN-09

OTHER FUNCTIONS

SQL> SELECT DECODE(JOB,’CLERK’,’OFFICER’,JOB) FROM EMP WHERE ROWNUM<5;

DECODE(JO
———
OFFICER
SALESMAN
SALESMAN
MANAGER

SQL> SELECT USER FROM DUAL;

USER
——————————
SCOTT

SQL> SELECT USERENV(‘ISDBA’) FROM DUAL;

USEREN
——
FALSE

SQL> SELECT USERENV(‘INSTANCE’) FROM DUAL;

USERENV(‘INSTANCE’)
——————-
1
SQL>SHOW USER
SQL>PASSWORD USERNAME;

SQL> SELECT SAL,SAL + NVL(COMM,0) TOTAL_COMP FROM EMP;

SAL TOTAL_COMP
———- ———-
800 800
1600 1900
1250 1750
2975 2975
1250 2650
2850 2850
2450 2450
3000 3000
5000 5000
1500 1500
1100 1100

950 950
3000 3000
1300 1300

SQL> SELECT SIGN(-20),SIGN(20),SIGN(0) FROM DUAL;

SIGN(-20) SIGN(20) SIGN(0)
———- ———- ———-
-1 1 0

SQL> SELECT DECODE(SIGN(SAL-3000),-1,’LOWER PAID’,1,’HIGHER PAID’,’SAME’),SAL FROM EMP;

DECODE(SIGN(SAL-3000),-1,’LOWERP SAL
——————————– ———-
LOWER PAID 800
LOWER PAID 1600
LOWER PAID 1250
LOWER PAID 2975
LOWER PAID 1250
LOWER PAID 2850
LOWER PAID 2450
SAME 3000
HIGHER PAID 5000
LOWER PAID 1500
LOWER PAID 1100

LOWER PAID 950
SAME 3000
LOWER PAID 1300

14 rows selected.

SQL> select case
when sal>1000 and sal<2000 then
‘A’
when sal>2000 and sal<5000 then
‘B’
else
‘C’
end as “EXMP”,sal from emp order by exmp ;

A 1500
A 1250
A 1300
A 1100
A 1250
A 1600
B 3000
B 2850
B 2975
B 2450
B 3000

C 950
C 800
C 5000

The Oracle/PLSQL NULLIF function compares expr1 and expr2. If expr1 and expr2 are equal, the NULLIF function returns NULL. Otherwise, it returns expr1

SQL> select nullif(‘DEBASIS’,’DEBASIS’) from dual;

NULLIF(
——-

SQL> select nullif(‘DEBASIS’,’DEBASISM’) from dual;

NULLIF(
——-
DEBASIS

SQL> select to_char(1234,’$0,000.00MI’) from dual;

TO_CHAR(12
———-
$1,234.00

The Oracle/PLSQL COALESCE function returns the first non-null expression in the list. If all expressions evaluate to null, then the COALESCE function will return null.

SQL> select comm,sal from emp;

COMM SAL
———- ———-
800
300 1600
500 1250
2975
1400 1250
2850
2450
3000
5000
0 1500
1100

950
3000
1300

SQL> select greatest(1,2,3) from dual;

GREATEST(1,2,3)
—————
3

SQL> select least(1,2,3) from dual;

LEAST(1,2,3)
————
1

GROUP BY FUNCTION

SQL> SELECT COUNT(*) FROM EMP GROUP BY JOB;

COUNT(*)
———
2
4
3
1
4

SQL> SELECT SUM(SAL),JOB,MIN(SAL),MAX(SAL) FROM EMP GROUP BY JOB;

SUM(SAL) JOB MIN(SAL) MAX(SAL)
——— ——— ——— ———
6000 ANALYST 3000 3000
4150 CLERK 800 1300
8275 MANAGER 2450 2975
5000 PRESIDENT 5000 5000
5600 SALESMAN 1250 1600

SQL>SELECT SUM(SAL),JOB,MIN(SAL),MAX(SAL) FROM EMP GROUP BY JOB HAVING SUM(SAL)>5000

SUM(SAL) JOB MIN(SAL) MAX(SAL)
——— ——— ——— ———
6000 ANALYST 3000 3000
8275 MANAGER 2450 2975
5600 SALESMAN 1250 1600

SQL> SELECT DECODE(SUM(SAL),6000,’DEB’,SUM(SAL)) FROM EMP GROUP BY JOB

DECODE(SUM(SAL),6000,’DEB’,SUM(SAL))
—————————————-
DEB
4150
8275
5000
5600

JOINING

SQL> select empno,ename,emp.deptno,dname from emp,dept where emp.deptno=dept.deptno and emp.deptno=20;

EMPNO ENAME DEPTNO DNAME
——— ———- ——— ————–
7369 SMITH 20 RESEARCH
7566 JONES 20 RESEARCH
7788 SCOTT 20 RESEARCH
7876 ADAMS 20 RESEARCH
7902 FORD 20 RESEARCH

sql>select * from scott.emp natural join scott.dept where deptno>20;

sql>select * from scott.emp join scott.dept using(deptno);

SQL> SELECT EMPNO,ENAME,D.DEPTNO,DNAME FROM EMP,DEPT D WHERE EMP.DEPTNO=D.DEPTNO (+) AND ROWNUM<3;

EMPNO ENAME DEPTNO DNAME
——— ———- ——— ————–
7934 MILLER 10 ACCOUNTING
7839 KING 10 ACCOUNTING

SQL> SELECT * FROM EMP
UNION
SELECT * FROM EMP_BAK;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
——— ———- ——— ——— ——— ——— ——— ———
123 DEBASIS
3434 DEBRAJ
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

SQL>SELECT * FROM EMP
INTERSECT
SELECT * FROM EMP_BAK;

SUB QUERIES

SINGLE ROW SUBQUERIES

SQL> select ename,deptno,sal from emp where deptno=(select deptno from dept where loc=’NEW YORK’);

ENAME DEPTNO SAL
———- ———- ———-
CLARK 10 2450
KING 10 5000
MILLER 10 1300

SQL> select ename,job,sal from emp where deptno in (select deptno from dept where dname in(‘ACCOUNTI
NG’,’SALES’));

ENAME JOB SAL
———- ——— ———-
ALLEN SALESMAN 1600
WARD SALESMAN 1250
MARTIN SALESMAN 1250
BLAKE MANAGER 2850
CLARK MANAGER 2450
KING PRESIDENT 5000
TURNER SALESMAN 1500
JAMES CLERK 950
MILLER CLERK 1300

9 rows selected.

SQL> select e.ename,e.job,e.sal from emp e where exists(select d.deptno from dept d where d.loc=’NEW
YORK’ and d.deptno=e.deptno);

ENAME JOB SAL
———- ——— ———-
CLARK MANAGER 2450
KING PRESIDENT 5000
MILLER CLERK 1300

SQL> select empno,ename,sal from emp where sal=(select min(sal) from emp);

EMPNO ENAME SAL
———- ———- ———-
7369 SMITH 800

MULTI-ROWS SUBQUERIES

SQL> select ename,job,sal from emp where deptno in (select deptno from dept where dname in (‘ACCOUNT
ING’,’SALES’));

ENAME JOB SAL
———- ——— ———-
ALLEN SALESMAN 1600
WARD SALESMAN 1250
MARTIN SALESMAN 1250
BLAKE MANAGER 2850
CLARK MANAGER 2450
KING PRESIDENT 5000
TURNER SALESMAN 1500
JAMES CLERK 950
MILLER CLERK 1300

9 rows selected.

HAVING CLAUSE AND SUBQURIES

SQL> select deptno,job,avg(sal) from emp group by deptno,job having avg(sal)>(select sal from emp wh
ere ename=’MARTIN’);

DEPTNO JOB AVG(SAL)
———- ——— ———-
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 ANALYST 3000
20 MANAGER 2975
30 MANAGER 2850
30 SALESMAN 1400

7 rows selected.

MULTIPLE COLUMN SUBQUERIES

SQL> select deptno,ename,job,sal from emp where (deptno,sal) in (select deptno,max(sal) from emp group by deptno);

DEPTNO ENAME JOB SAL
———- ———- ——— ———-
10 KING PRESIDENT 5000
20 SCOTT ANALYST 3000
20 FORD ANALYST 3000
30 BLAKE MANAGER 2850

INLINE VIEWS IN SUBQUERIES

SQL> select e.ename,subq.loc from emp e,(select deptno,loc from dept where loc in(‘NEW YORK’,’DALLAS
‘)) subq where e.deptno=subq.deptno;

ENAME LOC
———- ————-
SMITH DALLAS
JONES DALLAS
CLARK NEW YORK
SCOTT DALLAS
KING NEW YORK
ADAMS DALLAS
FORD DALLAS
MILLER NEW YORK

8 rows selected.

TOP N-QUERIES

SQL> select ename,job,sal,rownum from (select ename,job,sal from emp order by sal) where rownum<=3;

ENAME JOB SAL ROWNUM
———- ——— ———- ———-
SMITH CLERK 800 1
JAMES CLERK 950 2
ADAMS CLERK 1100 3

SELECTING A PARTICULAR ROW WITH ROWNUM

SQL> (select empno,rownum from emp where rownum<=4) minus(select empno,rownum from emp where rownum<=1);

EMPNO ROWNUM
———- ———-
7499 2
7521 3
7566 4

ENTERING VALUES

SQL> select ename,job from emp where empno=&empno;
Enter value for empno: 111

old 1: select ename,job from emp where empno=&empno
new 1: select ename,job from emp where empno=111

no rows selected

FORMATING AS REPORT

SQL> SET HEADSEP !
SQL> TTITLE “EMPLOYEE LIST”
SQL> BTITLE “MADE BY DEBASIS”
SQL> SET PAGESIZE 50
SQL> SET LINESIZE 100
SQL> SET FEEDBACK OFF
SQL> SET UNDERLINE *
SQL> BREAK ON JOB
SQL> COL SAL FORMAT 9,999
SQL> COL ENAME FORMAT A8
SQL> SELECT * FROM EMP ORDER BY JOB ASC;

Thu Apr 22 page 1
EMPLOYEE LIST

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
********** ******** ********* ********** ********* ****** ********** **********
7788 SCOTT ANALYST 7566 19-APR-87 3,000 20
7902 FORD 7566 03-DEC-81 3,000 20
7369 SMITH CLERK 7902 17-DEC-80 800 20
7876 ADAMS 7788 23-MAY-87 1,100 20
7934 MILLER 7782 23-JAN-82 1,300 10
7900 JAMES 7698 03-DEC-81 950 30
7566 JONES MANAGER 7839 02-APR-81 2,975 20
7782 CLARK 7839 09-JUN-81 2,450 10
7698 BLAKE 7839 01-MAY-81 2,850 30
7839 KING PRESIDENT 17-NOV-81 5,000 10
7499 ALLEN SALESMAN 7698 20-FEB-81 1,600 300 30
7654 MARTIN 7698 28-SEP-81 1,250 1400 30
7844 TURNER 7698 08-SEP-81 1,500 0 30
7521 WARD 7698 22-FEB-81 1,250 500 30
1111 DEBASIS

MADE BY DEBASIS
SAVING CUSTOMIZATIONS

SQL> STORE SET DEB.OUT
Created file DEB.OUT
SQL> GET DEB.OUT

CREATING ORACLE DATABASE OBJECTS

CREATING TEMPORARY TABLES

SQL>CREATE GLOBAL TEMPORARY TABLE TEMP_EMP (EMPNO NUMBER,ENAME VARCHAR2(10))

CREATING ONE TABLE WITH DATA FROM ANOTHER

SQL> create table emp_copy(empno,sal) as select empno,sal from emp;

ADDING AND MODIFYING AND DROPPING COLUMNS

SQL> alter table employee add (hire_date date);
SQL> alter table products modify(lastname varchar2(30));
SQL> alter table employee set unused column salary;
SQL> alter table employee drop unused columns;
SQL> alter table employee drop column salary;

DROPPING,RENAMING AND TRUNCATING TABLES

SQL> drop table emp;
SQL>drop table emp cascade constraints;
SQL> truncate table emp;

RENAMING TABLE

SQL> rename emp to emp_bak;
SQL>alter table emp rename to emp_bak;

COMMENTING OBJECTS

SQL> comment on table emp is
‘This is a table containing employees’;

CREATING TABLE

SQL>CREATE TABLE EMP
(EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);

SQL>CREATE TABLE EMP (
EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2) CHECK (SAL <=10000),
COMM NUMBER(7,2),
DEPTNO NUMBER(2),
CONSTRAINT PK_EMP
PRIMARY KEY ( EMPNO )
USING INDEX PCTFREE 10
STORAGE(INITIAL 12288 NEXT 12288 PCTINCREASE 50 )
TABLESPACE SYSTEM)
TABLESPACE SYSTEM PCTUSED 40 PCTFREE 10
STORAGE(INITIAL 12288 NEXT 12288 PCTINCREASE 50 )
PARALLEL (DEGREE 1 INSTANCES 1) NOCACHE;

SQL>CREATE TABLE EMP (
ENAME VARCHAR2(90);
EMPNO NUMBER(20);
CONSTRAINT PK_NAMES PRIMARY KEY (ENAME,EMPNO));

ADDING INTEGRITY CONSTRAINTS TO EXISTING TABLES

SQL> alter table emp add constraints pk_emp_01 primary key(empno);
SQL> alter table emp add constraints fk_emp_01 foreign key (deptno) references dept(deptno);
SQL> alter table emp add constraints uk_emp_o1 unique(empno);
SQL> alter table emp modify (ename not null);

DISABLING CONSTRAINTS

SQL> alter table emp disable primary key ;
SQL> alter table emp disable constraint pk_emp_01;
SQL> alter table emp disable primary key cascade;

ENABLING A DISABLED CONSTRAINTS

SQL> alter table department enable primary key;
SQL> alter table emp enable uk_emp_01;

WHEN EXISTING DATA IN A COLUMN VIOLATES A DISABLED CONSTRAINT

SQL> @@$ORACLE_HOME/rdbms/admin/utlexcpt;

Let us assume that there is a table where primary key constraint is enabled and we have inserted two rows with different values.

SQL>create table example_1(col1 number);
SQL>insert into example_1 values(10);
SQL> insert into example_1 values(1);
SQL>alter table example_1 add(constraint pk_01 primary key(col1));

Now we have disabled the constraint

SQL>alter table example_1 disable constraint pk_01;
SQL>insert into example_1 values(1);

Now the rows that causes unique constraint violation will be inserted into exception table

SQL> alter table exp_1 enable constraint pk_01 exceptions into exceptions;
SQL>select e.row_id,a.col1 from exceptions e,example_1 a where e.row_id=a.rowid;

USING DEFFERED CONSTRAINT

DROP TABLE emp;
DROP TABLE dept;

CREATE TABLE dept (
deptno NUMBER(2) NOT NULL
, dname CHAR(14)
, loc CHAR(13)
, CONSTRAINT dept_pk PRIMARY KEY (deptno)
);

INSERT INTO dept VALUES (10,’FINANCE’,’PITTSBURGH’);
INSERT INTO dept VALUES (20,’SALES’,’NEW YORK’);
INSERT INTO dept VALUES (30,’OPERATIONS’,’BOSTON’);

COMMIT;

CREATE TABLE emp (
empno NUMBER(4) NOT NULL
, ename CHAR(10)
, job CHAR(9)
, deptno NUMBER(2) NOT NULL
, CONSTRAINT emp_fk1 FOREIGN KEY (deptno)
REFERENCES dept (deptno)
DEFERRABLE
INITIALLY IMMEDIATE
, CONSTRAINT emp_pk PRIMARY KEY (empno)
);

INSERT INTO emp VALUES (1001, ‘JEFF’, ‘PRESIDENT’, 10);
INSERT INTO emp VALUES (1002, ‘MELODY’, ‘MANAGER’, 30);
INSERT INTO emp VALUES (1003, ‘MARK’, ‘MANAGER’, 10);
INSERT INTO emp VALUES (1004, ‘MARTIN’, ‘MANAGER’, 20);

COMMIT;

REM — ——————————-
REM — Try to delete from parent table
REM — ——————————-

DELETE FROM dept WHERE deptno = 10;

ERROR at line 1:
ORA-02292: integrity constraint (OPS$ORACLE.EMP_FK1) violated – child record found

REM — ——————————————————–
REM — Set this transaction to defer all deferrable constraints
REM — ——————————————————–

SET CONSTRAINTS ALL DEFERRED;

DELETE FROM dept WHERE deptno = 10;

1 row deleted.

COMMIT;

ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02292: integrity constraint (OPS$ORACLE.EMP_FK1) violated – child record found

REMOVING CONSTRAINTS

SQL> alter table employee drop unique (govt_id);
SQL> alter table employee drop primary key cascade;
SQL> alter table employee drop constraint ck_emp_01;

MANIPULATING ORACLE DATA

ADDNING NEW ROWS TO TABLE

SQL>INSERT INTO EMPLOYEE(EMPID,LASTNAME,FIRSTNAME,SALARY,DEPT,HIRE_DATE) VALUES (‘39334’,’SMITH’,’GINA’,75000,NULL,’15-MAR-97’);

(YOU HAVE TO DECLARE THE COLUMN NAME EXPLICITLY OTHERWISE YOU HAVE TO PROVIDE ALL THE VALUES)

SQL>INSERT INTO EMPLOYEE(‘0223’,’WALIA’,’RAJENDRA’,60000,’01-JAN-2006’,NULL);
(YOU HAVE TO INSERT NULL VALUE IF ANY COLUMN IS NOT REQUIRED DATA)

INSERTING FROM OTHER TABLE

SQL>INSER INTO EMP (SELECT * FROM EMP_BAK);

SPECIFYING EXPLICIT DEFAULT VALUES

SQL>INSERT INTO SAMPLE (COL1) VALUES (1);(DEFAULT VALUE WILL BE INSERTED AUTOMATICALLY);

SQL>INSERT INTO SAMPLE (COL1,COL2) VALUES (2,DEFAULT);

UPDATING AND DELETING

SQL>UPDATE EMP SET FIRST_NAME=’DEB’,LAST_NAME=’MAITY’ WHERE EMPID=1;

SQL>UPDATE EMP SET LAST_NAME=(SELECT ENAME FROM EMPLOYEE WHERE EMPID=2) WHERE EMPID=1;

SQL>DELETE FROM EMP(ALL DATA WILL BE DELETED!!!)

SQL>DELETE FROM EMP WHERE ROWNUM<2;

THE IMPORTANCE OF TRANSACTION CONTROL

SQL>commit;

SQL>ROLLBACK;

SAVEPOINT

SQL> UPDATE EMP SET SAL=5000 WHERE ENAME=’FORD’;

1 row updated.

SQL> SAVEPOINT DEB;

Savepoint created.

SQL> UPDATE EMP SET SAL=6000 WHERE ENAME=’FORD’;

1 row updated.

SQL> ROLLBACK TO SAVEPOINT DEB;

Rollback complete.

SQL> COMMIT;

Commit complete.

SQL> SELECT SAL FROM EMP WHERE ENAME=’FORD’;

SAL
———-
5000

CREATING UNDERLYING TABLE DATA THROUGH SIMPLE VIEWS

SQL>CREATE VIEW EMP_VIEW AS (SELECT * FROM EMP WHERE JOB=’CLERK’);

SQL>create view my_view as (select * from emp) order by empno;

SQL>create view my_view as (select distinct(job) as my_jobs from emp);

SQL>CREATE OR REPLACE VIEW EMP_VIEW AS ( SELECT EMPNO,ENAME,DECODE(ENAME,USER,SAL,’KING’,SAL,0) AS SAL,DECODE(ENAME,USER,COMM,’KING’,SAL,0) AS COMM,DEPTNO FROM EMP;

TO RESTRICT VIEW FROM MODIFICATION

SQL>create or replace view emp_view as (select empno,ename,job,deptno from emp where deptno=10) with check option constraint emp_view_constraint;

SQL>select constraint_name,constraint_type from user_constraints;

SQL>create or replace view emp_view as (select * from emp) with read only;

COMPLEX VIEW

SQL>create or replace view emp_dept_view as (select empno,ename,job,loc from emp e,dept d where e.deptno=d.deptno);

SQL>select column_name,updatable from user_updatable_columns where table_name=’EMP_DEPT_VIEW’;

MODIFYING AND REMOVING VIEWS

SQL>alter view emp_dept_view compile;

SQL>select object_name,status from user_objects where object_name=’PROFITS_VIEW’;

SQL>drop view profits_view;

SEQUENCES

SQL>create sequence count_20 start with 20 increment by 1 maxvalue 20 minvalue 0 cycle order cache 2;

SQL>select count_20.nextval from dual;

SQL>selct count_20.currval from dual;

SQL>Insert into expense(exense_no,empid) values(count_20.nextval,2345);

SQL>update product set product_num=count_20.currval where serial_num=123;

Modifying Sequences

SQL>alter sequence count_20 increment by –4;
SQL>Alter sequence count_20 nocycle;

INDEXES

SQL>create unique index emp_empno_01 on emp(deptno);
SQL>create index emp_sal_01 on (sal);
SQL>create unique index employee_ename_index_01 on emp(empno,ename);
SQL>create index emp_ename_reverse_indx on emp(ename) reverse;
SQL>create bitmap index emp_deptno_indx_01 on emp(deptno);
SQL>create index ixd_emp_01 on emp(sal*1.08);
SQL>alter session set query_rewritable_enabled=true;
SQL>drop index employee_list_indx;

View
1.user_indexes;
2.user_ind_columns

SYNONYMS

SQL>create synonym emp for scott.emp;
SQL>drop synonym all_my_emps;
SQL>drop public synonym emp;

DELETING DUPLICATE ROW

delete from dup_data where rowid not in (select min(rowid) from dup_data group by col1,col2);

USE OF BIND VARIABLE

conn / as sysdba

GRANT select ON gv_$statname TO uwclass;

conn uwclass/uwclass

CREATE TABLE run_stats (
runid VARCHAR2(15),
name VARCHAR2(80),
value INT);

CREATE OR REPLACE VIEW stats AS
SELECT ‘STAT…’ || a.name NAME, b.value
FROM gv$statname a, gv$mystat b
WHERE a.statistic# = b.statistic#
UNION ALL
SELECT ‘LATCH.’ || NAME, gets
FROM gv$latch;

CREATE TABLE t (
x INT);

set timing on

ALTER SYSTEM FLUSH SHARED_POOL;

INSERT INTO run_stats
SELECT ‘before’, stats.*
FROM stats;

— not using bind variables
DECLARE
x NUMBER(10);
BEGIN
FOR i IN 1 .. 5000
LOOP
EXECUTE IMMEDIATE ‘SELECT ‘ || i || ‘ INTO :b1 FROM dual’
INTO x;
END LOOP;
END;
/

INSERT INTO run_stats
SELECT ‘after 1’, stats.*
FROM stats;

— using bind variables
DECLARE
x NUMBER(10);
BEGIN
FOR i IN 1 .. 5000
LOOP
EXECUTE IMMEDIATE ‘SELECT :b1 FROM dual’
INTO x
USING i;
END LOOP;
END;
/

INSERT INTO run_stats
SELECT ‘after 2’, stats.*
FROM stats;

col name format a35

SELECT a.name, b.value-a.value RUN1,
c.value-b.value RUN2,
((c.value-b.value)-(b.value-a.value)) DIFF
FROM run_stats a, run_stats b, run_stats c
WHERE a.name = b.name
AND b.name = c.name
AND a.runid = ‘before’
AND b.runid = ‘after 1’
AND c.runid = ‘after 2’
AND (c.value-a.value) > 0
AND (c.value-b.value) <> (b.value-a.value)
ORDER BY ABS((c.value-b.value)-(b.value-a.value));

SELECT sql_fulltext
FROM gv$sql s, gv$sqltext_with_newlines n
WHERE s.hash_value = n.hash_value
AND n.sql_text LIKE ‘%dual%’
ORDER BY last_active_time;

HANDLING LARGE OBJECTS

1.Create table as following structure

CREATE TABLE BOOK_SAMPLES
(
BOOK_SAMPLE_ID NUMBER(10),
ISBN CHAR(10 BYTE),
DESCRIPTION CLOB,
BOOK_COVER BLOB,
CHAPTER BFILE
)
TABLESPACE USERS
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
LOB (BOOK_COVER) STORE AS
( TABLESPACE USERS
ENABLE STORAGE IN ROW
CHUNK 8192
PCTVERSION 0
NOCACHE
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
)
LOB (DESCRIPTION) STORE AS
( TABLESPACE USERS
ENABLE STORAGE IN ROW
CHUNK 8192
PCTVERSION 0
NOCACHE
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
)
NOCACHE
NOPARALLEL
MONITORING;

ALTER TABLE BOOK_SAMPLES ADD (
PRIMARY KEY
(BOOK_SAMPLE_ID)
USING INDEX
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
));

2.Create external LOB storage for storing BFILE

create directory book_sample_loc as ‘c:/oracle’

3.Inserting data into table

INSERT INTO DEB.BOOK_SAMPLES (
BOOK_SAMPLE_ID, ISBN, DESCRIPTION,
BOOK_COVER, CHAPTER)
VALUES (1 ,’07123455′ ,’This is test book’,empty_blob(),bfilename(‘book_sample_loc’,’test.jpeg’));

Error undefined symbol: JNU_ThrowByName during oracle 12c R2 installation

We faced this error during Oracle12cR2 installation.

During our installation of oracle 12.2 in new home on existing Oracle 11g installation,we faced the error

 

INFO: Executing [/opt/oracle/product/12.2.0.1/db1/bin/diagsetup, clustercheck=false, basedir=/opt/oracle/product, oraclehome=/opt/oracle/product/12.2.0.1/db1]

Solution

We found there was old 11g Database installed and all environment variables were pointing to old installation.After resetting variables to new installed ORACLE_HOME and other variable,it got resolved.

 

Add additional swap space in RHEL 7

This document describes process to add additional swap space in RHEL 7.

1.First partition your newly added disk

[root@LinuxAcademy dev]# gdisk /dev/xvdf
GPT fdisk (gdisk) version 0.8.10

Partition table scan:
MBR: not present
BSD: not present
APM: not present
GPT: not present

Creating new GPT entries.

Command (? for help): n
Partition number (1-128, default 1):
First sector (34-41943006, default = 2048) or {+-}size{KMGTP}:
Last sector (2048-41943006, default = 41943006) or {+-}size{KMGTP}:
Current type is ‘Linux filesystem’
Hex code or GUID (L to show codes, Enter = 8300): 8e00
Changed type of partition to ‘Linux LVM’

Command (? for help): w

Final checks complete. About to write GPT data. THIS WILL OVERWRITE EXISTING
PARTITIONS!!

Do you want to proceed? (Y/N): y
OK; writing new GUID partition table (GPT) to /dev/xvdf.
The operation has completed successfully.

2.Please create physical volume group and logical volume group.

[root@LinuxAcademy dev]# pvdisplay
[root@LinuxAcademy dev]# vgcreate battlestar /dev/xvdf1
Physical volume “/dev/xvdf1” successfully created
Volume group “battlestar” successfully created
[root@LinuxAcademy dev]# lvcreate -n swap -L 2G battlestar
Logical volume “swap” created.

4.Please create swap file system now.

[root@LinuxAcademy dev]# mkswap /dev/battlestar/swap
mkswap: /dev/battlestar/swap: warning: don’t erase bootbits sectors
on whole disk. Use -f to force.
Setting up swapspace version 1, size = 2097148 KiB
no label, UUID=ffb912cb-a280-41ca-9558-d2cc70447469

swapon /dev/battlestar/swap

5.Please check free memory now

free -m

6.Please add below entry in fstab for auto mount after restart.

vi /etc/fstab
/dev/battlestar/swap swap swap 0 0

 

7.Check swap space now.

swapon -a
swapon -s

Extend LVM after adding new disk to the server Linux

This document describes the procedure to extend your existing mount point after adding the disk in RHEL 7 using LVM.

1.Please create partition on newly added disk

[root@linuxacademy1 ~]# gdisk /dev/xvdf
GPT fdisk (gdisk) version 0.8.6

Partition table scan:
MBR: not present
BSD: not present
APM: not present
GPT: not present

Creating new GPT entries.

Command (? for help): n
Partition number (1-128, default 1):
First sector (34-41943006, default = 2048) or {+-}size{KMGTP}:
Last sector (2048-41943006, default = 41943006) or {+-}size{KMGTP}:
Current type is ‘Linux filesystem’
Hex code or GUID (L to show codes, Enter = 8300): 008a
Exact type match not found for type code 008A; assigning type code for
‘Linux filesystem’
Changed type of partition to ‘Linux filesystem’

Command (? for help): w

Final checks complete. About to write GPT data. THIS WILL OVERWRITE EXISTING
PARTITIONS!!

Do you want to proceed? (Y/N): y
OK; writing new GUID partition table (GPT) to /dev/xvdf.
The operation has completed successfully.
[root@linuxacademy1 ~]# gdisk /dev/xvdg
GPT fdisk (gdisk) version 0.8.6

Partition table scan:
MBR: not present
BSD: not present
APM: not present
GPT: not present

Creating new GPT entries.

Command (? for help): n
Partition number (1-128, default 1): 1
First sector (34-41943006, default = 2048) or {+-}size{KMGTP}:
Last sector (2048-41943006, default = 41943006) or {+-}size{KMGTP}:
Current type is ‘Linux filesystem’
Hex code or GUID (L to show codes, Enter = 8300): 008a
Exact type match not found for type code 008A; assigning type code for
‘Linux filesystem’
Changed type of partition to ‘Linux filesystem’

Command (? for help): w

Final checks complete. About to write GPT data. THIS WILL OVERWRITE EXISTING
PARTITIONS!!

Do you want to proceed? (Y/N): y
OK; writing new GUID partition table (GPT) to /dev/xvdg.
The operation has completed successfully.

2.Now please create physical volume group

[root@linuxacademy1 ~]# pvcreate /dev/xvdf1 /dev/xvdg1
Physical volume “/dev/xvdf1” successfully created.
Physical volume “/dev/xvdg1” successfully created.
[root@linuxacademy1 ~]# pvdisplay

“/dev/xvdf1” is a new physical volume of “<20.00 GiB”
— NEW Physical volume —
PV Name /dev/xvdf1
VG Name
PV Size <20.00 GiB
Allocatable NO
PE Size 0
Total PE 0
Free PE 0
Allocated PE 0
PV UUID a52OwU-svuG-mR1O-wJ2E-pZ2i-HCFa-287P7I

“/dev/xvdg1” is a new physical volume of “<20.00 GiB”
— NEW Physical volume —
PV Name /dev/xvdg1
VG Name
PV Size <20.00 GiB
Allocatable NO
PE Size 0
Total PE 0
Free PE 0
Allocated PE 0
PV UUID cVCWqw-BSdi-FC00-e5JW-2dq9-QQH2-13q7eZ

[root@linuxacademy1 ~]# vgcreate battlestar /dev/xvdf1 /dev/xvdg1
Volume group “battlestar” successfully created
[root@linuxacademy1 ~]# vgdisplay
— Volume group —
VG Name battlestar
System ID
Format lvm2
Metadata Areas 2
Metadata Sequence No 1
VG Access read/write
VG Status resizable
MAX LV 0
Cur LV 0
Open LV 0
Max PV 0
Cur PV 2
Act PV 2
VG Size 39.99 GiB
PE Size 4.00 MiB
Total PE 10238
Alloc PE / Size 0 / 0
Free PE / Size 10238 / 39.99 GiB
VG UUID xtAtA6-4WZY-KZK8-wskY-QNcJ-YYKt-3XlSh1

3.Please create logical volume now

[root@linuxacademy1 ~]# lvcreate -n galactica -L 20G battlestar
Logical volume “galactica” created.
[root@linuxacademy1 ~]# mkfs -t xfs /dev/battlestar/galactica
meta-data=/dev/battlestar/galactica isize=512 agcount=4, agsize=1310720 blks
= sectsz=512 attr=2, projid32bit=1
= crc=1 finobt=0, sparse=0
data = bsize=4096 blocks=5242880, imaxpct=25
= sunit=0 swidth=0 blks
naming =version 2 bsize=4096 ascii-ci=0 ftype=1
log =internal log bsize=4096 blocks=2560, version=2
= sectsz=512 sunit=0 blks, lazy-count=1
realtime =none extsz=4096 blocks=0, rtextents=0

4.Mount now.

[root@linuxacademy1 ~]# mkdir /mnt/mydir
[root@linuxacademy1 ~]# mount /dev/battlestar/galactica /mnt/mydir

5.Please add the extra disk to expand mount point

You can use following command to scan newly added disk online(No need to restart server)

echo “- – -” > /sys/class/scsi_host/host0/scan
 echo “- – -” > /sys/class/scsi_host/host1/scan

[root@linuxacademy1 ~]# gdisk /dev/xvdj
GPT fdisk (gdisk) version 0.8.6

Partition table scan:
MBR: not present
BSD: not present
APM: not present
GPT: not present

Creating new GPT entries.

Command (? for help): n
Partition number (1-128, default 1):
First sector (34-41943006, default = 2048) or {+-}size{KMGTP}:
Last sector (2048-41943006, default = 41943006) or {+-}size{KMGTP}:
Current type is ‘Linux filesystem’
Hex code or GUID (L to show codes, Enter = 8300): 008a
Exact type match not found for type code 008A; assigning type code for
‘Linux filesystem’
Changed type of partition to ‘Linux filesystem’

Command (? for help): w

Final checks complete. About to write GPT data. THIS WILL OVERWRITE EXISTING
PARTITIONS!!

Do you want to proceed? (Y/N): y
OK; writing new GUID partition table (GPT) to /dev/xvdj.
The operation has completed successfully.

[root@linuxacademy1 ~]# partprobe

6.Extending volume group now

[root@linuxacademy1 ~]# pvcreate /dev/xvdj1
Physical volume “/dev/xvdj1” successfully created.
[root@linuxacademy1 ~]# vgextend battlestar /dev/xvdj1
Volume group “battlestar” successfully extended
[root@linuxacademy1 ~]# vgdisplay
— Volume group —
VG Name battlestar
System ID
Format lvm2
Metadata Areas 3
Metadata Sequence No 3
VG Access read/write
VG Status resizable
MAX LV 0
Cur LV 1
Open LV 1
Max PV 0
Cur PV 3
Act PV 3
VG Size <59.99 GiB
PE Size 4.00 MiB
Total PE 15357
Alloc PE / Size 5120 / 20.00 GiB
Free PE / Size 10237 / <39.99 GiB
VG UUID xtAtA6-4WZY-KZK8-wskY-QNcJ-YYKt-3XlSh1

[root@linuxacademy1 ~]# lvextend -L 59G /dev/battlestar/galactica
Size of logical volume battlestar/galactica changed from 20.00 GiB (5120 extents) to 59.00 GiB (15104 extents).
Logical volume battlestar/galactica successfully resized.
[root@linuxacademy1 ~]# lvdisplay
— Logical volume —
LV Path /dev/battlestar/galactica
LV Name galactica
VG Name battlestar
LV UUID 0C8PRN-HsxV-mnne-9Awb-Net4-zNAN-kpJrM9
LV Write Access read/write
LV Creation host, time linuxacademy1, 2017-10-16 05:06:56 -0400
LV Status available
# open 1
LV Size 59.00 GiB
Current LE 15104
Segments 3
Allocation inherit
Read ahead sectors auto
– currently set to 8192
Block device 253:0

7.Please check disk space now and this has not reflected new size

[root@linuxacademy1 ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/xvda2 10G 1.9G 8.2G 19% /
devtmpfs 477M 0 477M 0% /dev
tmpfs 496M 0 496M 0% /dev/shm
tmpfs 496M 13M 483M 3% /run
tmpfs 496M 0 496M 0% /sys/fs/cgroup
tmpfs 100M 0 100M 0% /run/user/1001
tmpfs 100M 0 100M 0% /run/user/0
/dev/mapper/battlestar-galactica 20G 33M 20G 1% /mnt/mydir

 

8.Please use following command to resize mount point

For XFS file system

[root@linuxacademy1 ~]# xfs_growfs /mnt/mydir
meta-data=/dev/mapper/battlestar-galactica isize=512 agcount=4, agsize=1310720 blks
= sectsz=512 attr=2, projid32bit=1
= crc=1 finobt=0 spinodes=0
data = bsize=4096 blocks=5242880, imaxpct=25
= sunit=0 swidth=0 blks
naming =version 2 bsize=4096 ascii-ci=0 ftype=1
log =internal bsize=4096 blocks=2560, version=2
= sectsz=512 sunit=0 blks, lazy-count=1
realtime =none extsz=4096 blocks=0, rtextents=0
data blocks changed from 5242880 to 15466496

For Ext4 file system

[root@linuxacademy1 ~]# resize2fs /dev/mapper/battlestar-galactica
resize2fs 1.42.9 (28-Dec-2013)
Filesystem at /dev/mapper/oemcc-oemcc is mounted on /oemcc; on-line resizing required
old_desc_blocks = 4, new_desc_blocks = 7
The filesystem on /dev/mapper/oemcc-oemcc is now 14680064 blocks long.

[root@linuxacademy1 ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/xvda2 10G 1.9G 8.2G 19% /
devtmpfs 477M 0 477M 0% /dev
tmpfs 496M 0 496M 0% /dev/shm
tmpfs 496M 13M 483M 3% /run
tmpfs 496M 0 496M 0% /sys/fs/cgroup
tmpfs 100M 0 100M 0% /run/user/1001
tmpfs 100M 0 100M 0% /run/user/0
/dev/mapper/battlestar-galactica 59G 33M 59G 1% /mnt/mydir

ORA-12545: Connect failed because target host or object does not exist using SERVICE_NAME in tnsnames.ora

Today I observed that we were getting following error when we were going to connect using SERVICE_NAME from (DR site) to new exadata server.

The connection was working fine with SID in tnsnames.ora to individual instances.

[oracle@XXX admin]$ sqlplus dba/XXX@PROD_CDV

SQL*Plus: Release 12.1.0.2.0 Production on Mon Nov 27 09:08:38 2017

Copyright (c) 1982, 2014, Oracle. All rights reserved.

ERROR:
ORA-12545: Connect failed because target host or object does not exist

Enter user-name:

I was using following tns:-

PROD_CDV =
(description=
(load_balance=on)
(address=(protocol=tcp)(host= x.x.x.x )(port=1521))
(address=(protocol=tcp)(host= x.x.x.y )(port=1521))
(connect_data=
(service_name= PROD.world)
)
)

The ping,tnsping were absolutely fine.

I enable trace in sqlnet.ora from client site

vi $ORACLE_HOME/network/admin

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
TRACE_LEVEL_CLIENT=USER
TRACE_FILENO_CLIENT=6
TRACE_FILELEN_CLIENT=51200
TRACE_UNIQUE_CLIENT=ON
TRACE_TIMESTAMP_CLIENT=ON
TRACE_DIRECTORY_CLIENT=/home/oracle/client_trace
LOG_DIRECTORY_CLIENT=/home/oracle/client_trace
DIAG_ADR_ENABLED=OFF
ADR_BASE = /u01/app/oracle

and found below issue in trace file generated under /home/oracle/client_trace:-

(3267593728) [000001 27-NOV-2017 09:08:48:587] nsc2addr: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=prod01vm03-vip)(PORT=1521)))
(3267593728) [000001 27-NOV-2017 09:08:48:587] snlinGetAddrInfo: getaddrinfo() failed with error -2
(3267593728) [000001 27-NOV-2017 09:08:48:587] nttbnd2addr: looking up IP addr for host: prod01vm03-vip
(3267593728) [000001 27-NOV-2017 09:08:58:608] snlinGetAddrInfo: getaddrinfo() failed with error -3
(3267593728) [000001 27-NOV-2017 09:08:58:608] nttbnd2addr: *** hostname lookup failure! ***
(3267593728) [000001 27-NOV-2017 09:08:58:608] nserror: nsres: id=0, op=77, ns=12545, ns2=12560; nt[0]=515, nt[1]=110, nt[2]=0; ora[0]=0, ora[1]=0, ora[2]=0

So it seems IP is getting implicitly converted to hostname .But this IP is not in DNS in DR site.

I added below entry of exadata VIP and physical IP in /etc/hosts in all 4 DR nodes

10.1.14.145 prod01vm03.tdeprdcl.world.com prod01vm03
10.1.14.147 prod02vm03.tdeprdcl.world.com prod02vm03
10.1.14.146 prod01vm03-vip.tdeprdcl.world.com prod01vm03-vip
10.1.14.148 prod02vm03-vip.tdeprdcl.world.com prod02vm03-vip

After that connection worked fine.

 

Reference:-

  • 454927.1 Using and Disabling the Automatic Diagnostic Repository (ADR) with Oracle Net for 11g
  • 219968.1 SQL*Net & Oracle Net Services – Tracing and Logging at a Glance
  • 834822.1 Oracle Net Diagnostics
  • 1076022.1 Examples of Troubleshooting Slow Oracle Net Connections

Restore Archivelog from particular sequence to another location using tape backup

Error in DR alert log:-

FAL[client]: Failed to request gap sequence
GAP – thread 2 sequence 9432-9473
DBID 2085418592 branch 924361120
FAL[client]: All defined FAL servers have been attempted.
————————————————————
Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization
parameter is defined to a value that’s sufficiently large
enough to maintain adequate log switch information to resolve
archivelog gaps.
————————————————————

Now in Primary,do the following steps to restore missing archive log

export ORACLE_SID=INSTANCE_NAME

rman target / catalog rcat/***@RCAT

run {
 allocate channel 'dev_00' type 'sbt_tape' parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=OCRMPD1,OB2BARLIST=CDV-PRD-DB-OCRMPD1_MENSUAL)';
 allocate channel 'dev_01' type 'sbt_tape' parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=OCRMPD1,OB2BARLIST=CDV-PRD-DB-OCRMPD1_MENSUAL)';
 allocate channel 'dev_02' type 'sbt_tape' parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=OCRMPD1,OB2BARLIST=CDV-PRD-DB-OCRMPD1_MENSUAL)';
 set archivelog destination to '/NFSPREPROD/NEW';
 RESTORE ARCHIVELOG FROM sequence 20453 thread 1;

RESTORE ARCHIVELOG FROM sequence 9432 thread 2;
 }

PSU patch Oracle 11gR2 oracle single instance HA

1.OPatch installation

Copy the opatch binary using root user to $ORACLE_HOME and $GRID_HOME.

Then please keep backup of old OPatch directory.

Unzip the binary in the $ORACLE_HOME and $GRID_HOME.

unzip -o -d $ORACLE_HOME /oracle/soft/p6880880_112000_Linux-x86-64.zip

unzip -o -d $GRID_HOME /oracle/soft/p6880880_112000_Linux-x86-64.zip

Change owner of the folder OPatch.

chown -R oracle:oinstall $ORACLE_HOME/OPatch

chown -R oracle:oinstall $GRID_HOME/OPatch

2.Stop EM DB console

 

[oracle@jminvgpdb01 ~]$ export ORACLE_UNQNAME=xcomprod
[oracle@jminvgpdb01 ~]$ emctl stop dbconsole

3.Create response file for ORACLE

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
$ORACLE_HOME/OPatch/ocm/bin/emocmrsp -no_banner -output /tmp/ocmdb.rsp

4.Create response file for GRID

export ORACLE_HOME=/u01/app/grid/11.2.0
$ORACLE_HOME/OPatch/ocm/bin/emocmrsp -no_banner -output /tmp/ocmgrid.rsp

5.Please proceed with GRID patching using root user

export PATH=/u01/app/grid/11.2.0/OPatch:$PATH
[root@jmbscspdb01 ~]# /u01/app/grid/11.2.0/OPatch/opatch auto /u01/app/oracle/soft/patch/22191577 -oh /u01/app/grid/11.2.0 -ocmrf /tmp/ocmgrid.rsp
Executing /u01/app/11.2.0/grid/perl/bin/perl /u01/app/11.2.0/grid/OPatch/crs/patch11203.pl -patchdir /u01/app/oracle/soft -patchn grid_patch -oh /u01/app/11.2.0/grid -ocmrf /tmp/ocmgrid.rsp -paramfile /u01/app/11.2.0/grid/crs/install/crsconfig_params

This is the main log file: /u01/app/11.2.0/grid/cfgtoollogs/opatchauto2015-05-09_22-34-25.log

This file will show your detected configuration and all the steps that opatchauto attempted to do on your system:
/u01/app/11.2.0/grid/cfgtoollogs/opatchauto2015-05-09_22-34-25.report.log

2015-05-09 22:34:25: Starting Clusterware Patch Setup
Using configuration parameter file: /u01/app/11.2.0/grid/crs/install/crsconfig_params

Stopping CRS…
Stopped CRS successfully

patch /u01/app/oracle/soft/grid_patch/17592127 apply successful for home /u01/app/11.2.0/grid
patch /u01/app/oracle/soft/grid_patch/20299017 apply successful for home /u01/app/11.2.0/grid

Starting CRS…
CRS-4123: Oracle High Availability Services has been started.

opatch auto succeeded.

6.Please proceed with ORACLE patching using root user

export PATH=/u01/app/oracle/product/11.2.0/db_1/OPatch:$PATH
[root@jmbscspdb01 ~]# /u01/app/oracle/product/11.2.0/db_1/OPatch/opatch auto /u01/app/oracle/soft/patch/22191577 -oh /u01/app/oracle/product/11.2.0/db_1 -ocmrf /tmp/ocmdb.rsp
Executing /u01/app/11.2.0/grid/perl/bin/perl /u01/app/oracle/product/11.2.0/db_1/OPatch/crs/patch11203.pl -patchdir /u01/app/oracle/soft -patchn grid_patch -oh /u01/app/oracle/product/11.2.0/db_1 -ocmrf /tmp/ocmdb.rsp -paramfile /u01/app/11.2.0/grid/crs/install/crsconfig_params

This is the main log file: /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatchauto2015-05-09_22-50-23.log

This file will show your detected configuration and all the steps that opatchauto attempted to do on your system:
/u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatchauto2015-05-09_22-50-23.report.log

2015-05-09 22:50:23: Starting Clusterware Patch Setup
Using configuration parameter file: /u01/app/11.2.0/grid/crs/install/crsconfig_params

Stopping RAC /u01/app/oracle/product/11.2.0/db_1 …
Stopped RAC /u01/app/oracle/product/11.2.0/db_1 successfully

patch /u01/app/oracle/soft/grid_patch/17592127/custom/server/17592127 apply successful for home /u01/app/oracle/product/11.2.0/db_1
patch /u01/app/oracle/soft/grid_patch/20299017 apply successful for home /u01/app/oracle/product/11.2.0/db_1

Starting RAC /u01/app/oracle/product/11.2.0/db_1 …
Started RAC /u01/app/oracle/product/11.2.0/db_1 successfully

opatch auto succeeded.

7.Please run catbundle PSU

cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> STARTUP
SQL> @catbundle.sql psu apply
SQL> QUIT

Grid and Oracle PSU patching using OPatch 11gR2 RAC

1.Please download latest version of OPatch and install under $ORACLE_HOME and $GRID_HOE

Copy the opatch binary using root user to $ORACLE_HOME and $GRID_HOME.

Then please keep backup of old OPatch directory.

Unzip the binary in the $ORACLE_HOME and $GRID_HOME.

unzip p6880880_112000_Linux-x86-64.zip -d /u01/app/11.2.0/grid

unzip p6880880_112000_Linux-x86-64.zip -d /u01/app/oracle/product/11.2.0/db_1

Change owner of the folder OPatch.

chown -R oracle:oinstall $ORACLE_HOME/OPatch

/u01/app/11.2.0/grid/OPatch/opatch version

chown -R oracle:oinstall $GRID_HOME/OPatch

/u01/app/oracle/product/11.2.0/db_1/OPatch/opatch version

2.Please create OCM response file

 

From ORACLE user , create ocm response files

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
$ORACLE_HOME/OPatch/ocm/bin/emocmrsp -no_banner -output /tmp/ocmdb.rsp

From GRID user , create ocm response files

export ORACLE_HOME=/u01/app/11.2.0/grid
$ORACLE_HOME/OPatch/ocm/bin/emocmrsp -no_banner -output /tmp/ocmgrid.rsp

3.Unzip GRID patch

unzip p20485830_112030_Linux-x86-64.zip -d /u01/app/oracle/softr/grid_patch

4.Inventory details from GRID user

[grid@jmeocpdb01 ~]$ /u01/app/11.2.0/grid/OPatch/opatch lsinventory -detail -oh /u01/app/11.2.0/grid
Oracle Interim Patch Installer version 11.2.0.3.10
Copyright (c) 2015, Oracle Corporation. All rights reserved.

Oracle Home : /u01/app/11.2.0/grid
Central Inventory : /u01/app/oraInventory
from : /u01/app/11.2.0/grid/oraInst.loc
OPatch version : 11.2.0.3.10
OUI version : 11.2.0.3.0
Log file location : /u01/app/11.2.0/grid/cfgtoollogs/opatch/opatch2015-05-02_19-03-33PM_1.log

Lsinventory Output file location : /u01/app/11.2.0/grid/cfgtoollogs/opatch/lsinv/lsinventory2015-05-02_19-03-33PM.txt

——————————————————————————–
Local Machine Information::
Hostname: jmeocpdb01
ARU platform id: 226
ARU platform description:: Linux x86-64

Installed Top-level Products (1):

Oracle Grid Infrastructure 11.2.0.3.0
There are 1 products installed in this Oracle Home.

Installed Products (88):

Agent Required Support Files 10.2.0.4.3
Assistant Common Files 11.2.0.3.0
Automatic Storage Management Assistant 11.2.0.3.0
Bali Share 1.1.18.0.0
Buildtools Common Files 11.2.0.3.0
Character Set Migration Utility 11.2.0.3.0
Cluster Ready Services Files 11.2.0.3.0
Cluster Verification Utility Common Files 11.2.0.3.0
Cluster Verification Utility Files 11.2.0.3.0
Database SQL Scripts 11.2.0.3.0
Deinstallation Tool 11.2.0.3.0
Enterprise Manager Common Core Files 10.2.0.4.4
Enterprise Manager Common Files 10.2.0.4.3
Enterprise Manager plugin Common Files 11.2.0.3.0
Expat libraries 2.0.1.0.1
HAS Common Files 11.2.0.3.0
HAS Files for DB 11.2.0.3.0
Installation Common Files 11.2.0.3.0
Installation Plugin Files 11.2.0.3.0
Installer SDK Component 11.2.0.3.0
LDAP Required Support Files 11.2.0.3.0
OLAP SQL Scripts 11.2.0.3.0
Oracle Clusterware RDBMS Files 11.2.0.3.0
Oracle Configuration Manager Deconfiguration 10.3.1.0.0
Oracle Containers for Java 11.2.0.3.0
Oracle Core Required Support Files 11.2.0.3.0
Oracle Database 11g 11.2.0.3.0
Oracle Database 11g Multimedia Files 11.2.0.3.0
Oracle Database Deconfiguration 11.2.0.3.0
Oracle Database User Interface 2.2.13.0.0
Oracle Database Utilities 11.2.0.3.0
Oracle DBCA Deconfiguration 11.2.0.3.0
Oracle Extended Windowing Toolkit 3.4.47.0.0
Oracle Globalization Support 11.2.0.3.0
Oracle Globalization Support 11.2.0.3.0
Oracle Grid Infrastructure 11.2.0.3.0
Oracle Help For Java 4.2.9.0.0
Oracle Ice Browser 5.2.3.6.0
Oracle Internet Directory Client 11.2.0.3.0
Oracle Java Client 11.2.0.3.0
Oracle JDBC/OCI Instant Client 11.2.0.3.0
Oracle JDBC/THIN Interfaces 11.2.0.3.0
Oracle JFC Extended Windowing Toolkit 4.2.36.0.0
Oracle JVM 11.2.0.3.0
Oracle LDAP administration 11.2.0.3.0
Oracle Locale Builder 11.2.0.3.0
Oracle Multimedia 11.2.0.3.0
Oracle Multimedia Client Option 11.2.0.3.0
Oracle Multimedia Java Advanced Imaging 11.2.0.3.0
Oracle Multimedia Locator 11.2.0.3.0
Oracle Multimedia Locator RDBMS Files 11.2.0.3.0
Oracle Net 11.2.0.3.0
Oracle Net Listener 11.2.0.3.0
Oracle Net Required Support Files 11.2.0.3.0
Oracle Netca Client 11.2.0.3.0
Oracle Notification Service 11.2.0.3.0
Oracle Notification Service (eONS) 11.2.0.3.0
Oracle One-Off Patch Installer 11.2.0.1.7
Oracle Quality of Service Management (Client) 11.2.0.3.0
Oracle Quality of Service Management (Server) 11.2.0.3.0
Oracle RAC Deconfiguration 11.2.0.3.0
Oracle RAC Required Support Files-HAS 11.2.0.3.0
Oracle Recovery Manager 11.2.0.3.0
Oracle Security Developer Tools 11.2.0.3.0
Oracle Text Required Support Files 11.2.0.3.0
Oracle Universal Installer 11.2.0.3.0
Oracle USM Deconfiguration 11.2.0.3.0
Oracle Wallet Manager 11.2.0.3.0
Parser Generator Required Support Files 11.2.0.3.0
Perl Interpreter 5.10.0.0.2
Perl Modules 5.10.0.0.1
PL/SQL 11.2.0.3.0
PL/SQL Embedded Gateway 11.2.0.3.0
Platform Required Support Files 11.2.0.3.0
Precompiler Required Support Files 11.2.0.3.0
RDBMS Required Support Files 11.2.0.3.0
RDBMS Required Support Files for Instant Client 11.2.0.3.0
RDBMS Required Support Files Runtime 11.2.0.3.0
Required Support Files 11.2.0.3.0
Secure Socket Layer 11.2.0.3.0
SQL*Plus 11.2.0.3.0
SQL*Plus Files for Instant Client 11.2.0.3.0
SQL*Plus Required Support Files 11.2.0.3.0
SSL Required Support Files for InstantClient 11.2.0.3.0
Sun JDK 1.5.0.30.03
Universal Storage Manager Files 11.2.0.3.0
XDK Required Support Files 11.2.0.3.0
XML Parser for Java 11.2.0.3.0
There are 88 products installed in this Oracle Home.

There are no Interim patches installed in this Oracle Home.

Rac system comprising of multiple nodes
Local node = jmeocpdb01
Remote node = jmeocpdb02

——————————————————————————–

OPatch succeeded.

5.Inventory details from ORACLE user

[grid@jmeocpdb01 ~]$ /u01/app/oracle/product/11.2.0/db_1/OPatch/opatch lsinventory -detail -oh /u01/app/oracle/product/11.2.0/db_1
OPatch could not create/open history file for writing.

Oracle Interim Patch Installer version 11.2.0.3.10
Copyright (c) 2015, Oracle Corporation. All rights reserved.

Oracle Home : /u01/app/oracle/product/11.2.0/db_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.2.0/db_1/oraInst.loc
OPatch version : 11.2.0.3.10
OUI version : 11.2.0.3.0
Log file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2015-05-02_19-06-03PM_1.log

Lsinventory Output file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/lsinv/lsinventory2015-05-02_19-06-03PM.txt

——————————————————————————–
Local Machine Information::
Hostname: jmeocpdb01
ARU platform id: 226
ARU platform description:: Linux x86-64

Installed Top-level Products (1):

Oracle Database 11g 11.2.0.3.0
There are 1 products installed in this Oracle Home.

Installed Products (136):

Agent Required Support Files 10.2.0.4.3
Assistant Common Files 11.2.0.3.0
Bali Share 1.1.18.0.0
Buildtools Common Files 11.2.0.3.0
Character Set Migration Utility 11.2.0.3.0
Cluster Verification Utility Common Files 11.2.0.3.0
Database Configuration and Upgrade Assistants 11.2.0.3.0
Database SQL Scripts 11.2.0.3.0
Database Workspace Manager 11.2.0.3.0
Deinstallation Tool 11.2.0.3.0
Enterprise Edition Options 11.2.0.3.0
Enterprise Manager Agent 10.2.0.4.3
Enterprise Manager Agent Core Files 10.2.0.4.4
Enterprise Manager Common Core Files 10.2.0.4.4
Enterprise Manager Common Files 10.2.0.4.3
Enterprise Manager Database Plugin — Agent Support 11.2.0.3.0
Enterprise Manager Database Plugin — Repository Support 11.2.0.3.0
Enterprise Manager Grid Control Core Files 10.2.0.4.4
Enterprise Manager plugin Common Files 11.2.0.3.0
Enterprise Manager Repository Core Files 10.2.0.4.4
Exadata Storage Server 11.2.0.1.0
Expat libraries 2.0.1.0.1
Generic Connectivity Common Files 11.2.0.3.0
HAS Common Files 11.2.0.3.0
HAS Files for DB 11.2.0.3.0
Installation Common Files 11.2.0.3.0
Installation Plugin Files 11.2.0.3.0
Installer SDK Component 11.2.0.3.0
JAccelerator (COMPANION) 11.2.0.3.0
LDAP Required Support Files 11.2.0.3.0
OLAP SQL Scripts 11.2.0.3.0
Oracle 11g Warehouse Builder Required Files 11.2.0.3.0
Oracle Advanced Security 11.2.0.3.0
Oracle Application Express 11.2.0.3.0
Oracle Call Interface (OCI) 11.2.0.3.0
Oracle Clusterware RDBMS Files 11.2.0.3.0
Oracle Code Editor 1.2.1.0.0I
Oracle Configuration Manager 10.3.5.0.1
Oracle Configuration Manager Client 10.3.2.1.0
Oracle Configuration Manager Deconfiguration 10.3.1.0.0
Oracle Containers for Java 11.2.0.3.0
Oracle Core Required Support Files 11.2.0.3.0
Oracle Data Mining RDBMS Files 11.2.0.3.0
Oracle Database 11g 11.2.0.3.0
Oracle Database 11g 11.2.0.3.0
Oracle Database 11g Multimedia Files 11.2.0.3.0
Oracle Database Deconfiguration 11.2.0.3.0
Oracle Database Gateway for ODBC 11.2.0.3.0
Oracle Database User Interface 2.2.13.0.0
Oracle Database Utilities 11.2.0.3.0
Oracle Database Vault J2EE Application 11.2.0.3.0
Oracle Database Vault option 11.2.0.3.0
Oracle DBCA Deconfiguration 11.2.0.3.0
Oracle Display Fonts 9.0.2.0.0
Oracle Enterprise Manager Console DB 11.2.0.3.0
Oracle Extended Windowing Toolkit 3.4.47.0.0
Oracle Globalization Support 11.2.0.3.0
Oracle Globalization Support 11.2.0.3.0
Oracle Help For Java 4.2.9.0.0
Oracle Help for the Web 2.0.14.0.0
Oracle Ice Browser 5.2.3.6.0
Oracle Internet Directory Client 11.2.0.3.0
Oracle Java Client 11.2.0.3.0
Oracle JDBC Server Support Package 11.2.0.3.0
Oracle JDBC/OCI Instant Client 11.2.0.3.0
Oracle JDBC/THIN Interfaces 11.2.0.3.0
Oracle JFC Extended Windowing Toolkit 4.2.36.0.0
Oracle JVM 11.2.0.3.0
Oracle Label Security 11.2.0.3.0
Oracle LDAP administration 11.2.0.3.0
Oracle Locale Builder 11.2.0.3.0
Oracle Message Gateway Common Files 11.2.0.3.0
Oracle Multimedia 11.2.0.3.0
Oracle Multimedia Annotator 11.2.0.3.0
Oracle Multimedia Client Option 11.2.0.3.0
Oracle Multimedia Java Advanced Imaging 11.2.0.3.0
Oracle Multimedia Locator 11.2.0.3.0
Oracle Multimedia Locator RDBMS Files 11.2.0.3.0
Oracle Net 11.2.0.3.0
Oracle Net Listener 11.2.0.3.0
Oracle Net Required Support Files 11.2.0.3.0
Oracle Net Services 11.2.0.3.0
Oracle Netca Client 11.2.0.3.0
Oracle Notification Service 11.2.0.3.0
Oracle Notification Service (eONS) 11.2.0.3.0
Oracle ODBC Driver 11.2.0.3.0
Oracle ODBC Driverfor Instant Client 11.2.0.3.0
Oracle OLAP 11.2.0.3.0
Oracle OLAP API 11.2.0.3.0
Oracle OLAP RDBMS Files 11.2.0.3.0
Oracle One-Off Patch Installer 11.2.0.1.7
Oracle Partitioning 11.2.0.3.0
Oracle Programmer 11.2.0.3.0
Oracle Quality of Service Management (Client) 11.2.0.3.0
Oracle RAC Deconfiguration 11.2.0.3.0
Oracle RAC Required Support Files-HAS 11.2.0.3.0
Oracle Real Application Testing 11.2.0.3.0
Oracle Recovery Manager 11.2.0.3.0
Oracle Security Developer Tools 11.2.0.3.0
Oracle Spatial 11.2.0.3.0
Oracle SQL Developer 11.2.0.3.0
Oracle Starter Database 11.2.0.3.0
Oracle Text 11.2.0.3.0
Oracle Text Required Support Files 11.2.0.3.0
Oracle UIX 2.2.24.6.0
Oracle Universal Connection Pool 11.2.0.3.0
Oracle Universal Installer 11.2.0.3.0
Oracle USM Deconfiguration 11.2.0.3.0
Oracle Wallet Manager 11.2.0.3.0
Oracle XML Development Kit 11.2.0.3.0
Oracle XML Query 11.2.0.3.0
Parser Generator Required Support Files 11.2.0.3.0
Perl Interpreter 5.10.0.0.2
Perl Modules 5.10.0.0.1
PL/SQL 11.2.0.3.0
PL/SQL Embedded Gateway 11.2.0.3.0
Platform Required Support Files 11.2.0.3.0
Precompiler Common Files 11.2.0.3.0
Precompiler Required Support Files 11.2.0.3.0
Provisioning Advisor Framework 10.2.0.4.3
RDBMS Required Support Files 11.2.0.3.0
RDBMS Required Support Files for Instant Client 11.2.0.3.0
RDBMS Required Support Files Runtime 11.2.0.3.0
regexp 2.1.9.0.0
Required Support Files 11.2.0.3.0
Sample Schema Data 11.2.0.3.0
Secure Socket Layer 11.2.0.3.0
SQL*Plus 11.2.0.3.0
SQL*Plus Files for Instant Client 11.2.0.3.0
SQL*Plus Required Support Files 11.2.0.3.0
SQLJ Runtime 11.2.0.3.0
SSL Required Support Files for InstantClient 11.2.0.3.0
Sun JDK 1.5.0.30.03
XDK Required Support Files 11.2.0.3.0
XML Parser for Java 11.2.0.3.0
XML Parser for Oracle JVM 11.2.0.3.0
There are 136 products installed in this Oracle Home.

There are no Interim patches installed in this Oracle Home.

Rac system comprising of multiple nodes
Local node = jmeocpdb01
Remote node = jmeocpdb02

——————————————————————————–

OPatch succeeded.

Now proceed for Patching after checking if you have sufficient space in $ORACLE HOME and $GRID_HOME

6.GRID patching from root user

/u01/app/11.2.0/grid/OPatch/opatch auto /u01/app/oracle/soft/grid_patch -oh /u01/app/11.2.0/grid -ocmrf /tmp/ocmgrid.rsp

7.ORACLE patching from root user

/u01/app/oracle/product/11.2.0/db_1/OPatch/opatch auto /u01/app/oracle/soft/grid_patch -oh /u01/app/oracle/product/11.2.0/db_1 -ocmrf /tmp/ocmdb.rsp

Please proceed step 1 to step 7 in another node of RAC

8.Now please proceed for catbundle.

cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> STARTUP
SQL> @catbundle.sql psu apply
SQL> QUIT

Automated sql code deployment using Github and Jenkins DevOps

Install GitHub

Download Git

https://git-scm.com/download/win

Installation Steps

After finish,Please create repository where code will be saved

 

There is no code now

I am creating sample code in c:\mygitrep directory

 

 

We need to again login to git from program and rescan.

Now stage unchanged

Now sign off

Please commit now

Now we will get this screen.

Please create a github repository to store code in central repository online.I login with my user.You can also create your user in github.

Now take the url and put into arbitrary url

 

Now you can push the code to git hub

 

Install Jenkins

https://jenkins.io/download/thank-you-downloading-windows-installer-stable/

You can get admin password from below location

Default plugin as well as github integration plugin needs to be installed.

Jenkins is ready with default url localhost:8080

 

Please provide git local installation file details in below

 

Please now go to create new job and provide git location

Please enable Poll scm which will poll and check if code is modified

 

This Build section is very important.You need to put DB tns names and power shell command to run sqlplus

Please create following file under your local repository->.git->hooks.

The content of the file will be as following

Now we need to test if change in code configured in git repository will trigger the Jenkins job

Great.The change in sql code trigger the Jenkins job automatically kick off.

 

You may view job result fail or successfully

Please check that you have two version of same sql in github repository

Create new mount point using LVM in RHEL 7

First install gdisk utility (you may use old fdisk utility if LUN/drive size is less than 2 TB)

-bash-4.1$ yum install gdisk
Loaded plugins: fastestmirror, security
You need to be root to perform this command.
-bash-4.1$ su
Password:
[root@LinuxAcademy linuxacademy]# yum install gdisk
Loaded plugins: fastestmirror, security
Setting up Install Process
Determining fastest mirrors
* base: mirror.cs.pitt.edu
* extras: mirrors.advancedhosters.com
* updates: mirror.atlanticmetro.net
base | 3.7 kB 00:00
base/primary_db | 3.7 MB 00:00
extras | 3.3 kB 00:00
extras/primary_db | 21 kB 00:00
updates | 3.4 kB 00:00
updates/primary_db | 4.6 MB 00:00
Resolving Dependencies
–> Running transaction check
—> Package gdisk.i686 0:0.8.10-1.el6 will be installed
–> Finished Dependency Resolution

Dependencies Resolved

================================================================================
Package Arch Version Repository Size
================================================================================
Installing:
gdisk i686 0.8.10-1.el6 base 172 k

Transaction Summary
================================================================================
Install 1 Package(s)

Total download size: 172 k
Installed size: 645 k
Is this ok [y/N]: y
Downloading Packages:
gdisk-0.8.10-1.el6.i686.rpm | 172 kB 00:00
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Installing : gdisk-0.8.10-1.el6.i686 1/1
Verifying : gdisk-0.8.10-1.el6.i686 1/1

Installed:
gdisk.i686 0:0.8.10-1.el6

Complete!

Please partition the new disk using gdisk

[root@LinuxAcademy linuxacademy]# gdisk /dev/xvdf

GPT fdisk (gdisk) version 0.8.10

Partition table scan:
MBR: not present
BSD: not present
APM: not present
GPT: not present

Creating new GPT entries.

Command (? for help): n
Partition number (1-128, default 1):
First sector (34-41943006, default = 2048) or {+-}size{KMGTP}:
Last sector (2048-41943006, default = 41943006) or {+-}size{KMGTP}:
Current type is ‘Linux filesystem’
Hex code or GUID (L to show codes, Enter = 8300): L
0700 Microsoft basic data 0c01 Microsoft reserved 2700 Windows RE
3000 ONIE boot 3001 ONIE config 4100 PowerPC PReP boot
4200 Windows LDM data 4201 Windows LDM metadata 7501 IBM GPFS
7f00 ChromeOS kernel 7f01 ChromeOS root 7f02 ChromeOS reserved
8200 Linux swap 8300 Linux filesystem 8301 Linux reserved
8302 Linux /home 8400 Intel Rapid Start 8e00 Linux LVM
a500 FreeBSD disklabel a501 FreeBSD boot a502 FreeBSD swap
a503 FreeBSD UFS a504 FreeBSD ZFS a505 FreeBSD Vinum/RAID
a580 Midnight BSD data a581 Midnight BSD boot a582 Midnight BSD swap
a583 Midnight BSD UFS a584 Midnight BSD ZFS a585 Midnight BSD Vinum
a800 Apple UFS a901 NetBSD swap a902 NetBSD FFS
a903 NetBSD LFS a904 NetBSD concatenated a905 NetBSD encrypted
a906 NetBSD RAID ab00 Apple boot af00 Apple HFS/HFS+
af01 Apple RAID af02 Apple RAID offline af03 Apple label
af04 AppleTV recovery af05 Apple Core Storage be00 Solaris boot
bf00 Solaris root bf01 Solaris /usr & Mac Z bf02 Solaris swap
bf03 Solaris backup bf04 Solaris /var bf05 Solaris /home
bf06 Solaris alternate se bf07 Solaris Reserved 1 bf08 Solaris Reserved 2
bf09 Solaris Reserved 3 bf0a Solaris Reserved 4 bf0b Solaris Reserved 5
c001 HP-UX data c002 HP-UX service ea00 Freedesktop $BOOT
eb00 Haiku BFS ed00 Sony system partitio ed01 Lenovo system partit
Press the <Enter> key to see more codes: 8e00
ef00 EFI System ef01 MBR partition scheme ef02 BIOS boot partition
fb00 VMWare VMFS fb01 VMWare reserved fc00 VMWare kcore crash p
fd00 Linux RAID
Hex code or GUID (L to show codes, Enter = 8300): 8e00
Changed type of partition to ‘Linux LVM’

Command (? for help): w

Final checks complete. About to write GPT data. THIS WILL OVERWRITE EXISTING
PARTITIONS!!

Do you want to proceed? (Y/N): y
OK; writing new GUID partition table (GPT) to /dev/xvdf.
The operation has completed successfully.

Please repeat the steps for /dev/xvdj

Please create physical volume group and logical volume group

[root@LinuxAcademy linuxacademy]# pvcreate /dev/xvdf1 /dev/xvdj1
Physical volume “/dev/xvdf1” successfully created
Physical volume “/dev/xvdj1” successfully created
[root@LinuxAcademy linuxacademy]# pvdisplay
“/dev/xvdf1” is a new physical volume of “20.00 GiB”
— NEW Physical volume —
PV Name /dev/xvdf1
VG Name
PV Size 20.00 GiB
Allocatable NO
PE Size 0
Total PE 0
Free PE 0
Allocated PE 0
PV UUID 5E12Tu-E1tn-8U7d-OK2g-pw3N-B8tC-9K1eeU

“/dev/xvdj1” is a new physical volume of “20.00 GiB”
— NEW Physical volume —
PV Name /dev/xvdj1
VG Name
PV Size 20.00 GiB
Allocatable NO
PE Size 0
Total PE 0
Free PE 0
Allocated PE 0
PV UUID xIc4BG-SXXf-RTYs-4xX8-Z4Ye-wT7n-z7vDJh

[root@LinuxAcademy linuxacademy]# vgcreate log_vg /dev/xvdf1 /dev/xvdj1
Volume group “log_vg” successfully created
[root@LinuxAcademy linuxacademy]# vgdisplay
— Volume group —
VG Name log_vg
System ID
Format lvm2
Metadata Areas 2
Metadata Sequence No 1
VG Access read/write
VG Status resizable
MAX LV 0
Cur LV 0
Open LV 0
Max PV 0
Cur PV 2
Act PV 2
VG Size 39.99 GiB
PE Size 4.00 MiB
Total PE 10238
Alloc PE / Size 0 / 0
Free PE / Size 10238 / 39.99 GiB
VG UUID ojwPwH-OhHT-vKCi-EIqc-K5g4-1Aom-7272Qn

[root@LinuxAcademy linuxacademy]# lvcreate -n log_lv -L 10GB log_vg
Logical volume “log_lv” created.
[root@LinuxAcademy linuxacademy]# lvdisplay
— Logical volume —
LV Path /dev/log_vg/log_lv
LV Name log_lv
VG Name log_vg
LV UUID ES1qhb-fIUg-Jvho-lviK-temg-ppD0-b9AEp4
LV Write Access read/write
LV Creation host, time LinuxAcademy, 2017-10-16 04:13:39 -0400
LV Status available
# open 0
LV Size 10.00 GiB
Current LE 2560
Segments 1
Allocation inherit
Read ahead sectors auto
– currently set to 256
Block device 253:0

Create file system

[root@LinuxAcademy linuxacademy]# mkfs -t ext4 /dev/log
mke2fs 1.41.12 (17-May-2010)
/dev/log is not a block special device.
Proceed anyway? (y,n) n
[root@LinuxAcademy linuxacademy]# mkfs -t ext4 /dev/log_vg/log_lv
mke2fs 1.41.12 (17-May-2010)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
Stride=0 blocks, Stripe width=0 blocks
655360 inodes, 2621440 blocks
131072 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=2684354560
80 block groups
32768 blocks per group, 32768 fragments per group
8192 inodes per group
Superblock backups stored on blocks:
32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632

Writing inode tables: done
Creating journal (32768 blocks): done
Writing superblocks and filesystem accounting information: done

This filesystem will be automatically checked every 34 mounts or
180 days, whichever comes first. Use tune2fs -c or -i to override.

Mount the filesystem

[root@LinuxAcademy linuxacademy]# mkdir /mnt/log_files

[root@LinuxAcademy linuxacademy]# mount /dev/log_vg/log_lv /mnt/log_files
[root@LinuxAcademy linuxacademy]# vgdisplay
— Volume group —
VG Name log_vg
System ID
Format lvm2
Metadata Areas 2
Metadata Sequence No 2
VG Access read/write
VG Status resizable
MAX LV 0
Cur LV 1
Open LV 1
Max PV 0
Cur PV 2
Act PV 2
VG Size 39.99 GiB
PE Size 4.00 MiB
Total PE 10238
Alloc PE / Size 2560 / 10.00 GiB
Free PE / Size 7678 / 29.99 GiB
VG UUID ojwPwH-OhHT-vKCi-EIqc-K5g4-1Aom-7272Qn

Please make following entry in /etc/fstab to auto mount

/dev/log_vg/log_lv /mnt/log_files ext4 defaults 0 0

How to remove Volume group

[root@LinuxAcademy linuxacademy]# vgremove log_vg
Do you really want to remove volume group “log_vg” containing 1 logical volumes? [y/n]: y
Logical volume log_vg/log_lv contains a filesystem in use.
[root@LinuxAcademy linuxacademy]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/xvde1 5.8G 1.5G 4.1G 26% /
tmpfs 299M 0 299M 0% /dev/shm
/dev/mapper/log_vg-log_lv
9.9G 151M 9.2G 2% /mnt/log_files
[root@LinuxAcademy linuxacademy]# umount /mnt/log_files
[root@LinuxAcademy linuxacademy]# vgremove log_vg
Do you really want to remove volume group “log_vg” containing 1 logical volumes? [y/n]: y
Do you really want to remove active logical volume log_lv? [y/n]: y
Logical volume “log_lv” successfully removed
Volume group “log_vg” successfully removed
[root@LinuxAcademy linuxacademy]# lvdisplay
[root@LinuxAcademy linuxacademy]# vgdisplay

[root@LinuxAcademy linuxacademy]# pvremove /dev/xvdf1 /dev/xvdj1
Labels on physical volume “/dev/xvdf1” successfully wiped
Labels on physical volume “/dev/xvdj1” successfully wiped