Pages

Sunday, December 13, 2015

ORACLE FAL_CLIENT and FAL_SERVER explained


FAL_CLIENT and FAL_SERVER are initialization parameters used to configure log gap detection and resolution at the standby database side of a physical database configuration. This functionality is provided by log apply services and is used by the physical standby database to manage the detection and resolution of archived redo logs.

FAL_CLIENT and FAL_SERVER only need to be defined in the initialization parameter file for the standby database(s). It is possible; however, to define these two parameters in the initialization parameter for the primary database server to ease the amount of work that would need to be performed if the primary database were required to transition its role.


Sample setup:

In Primary site:

FAL_SERVER=STANDBY
FAL_CLIENT=PRIMARY

In Standby site:

FAL_SERVER=PRIMARY
FAL_CLIENT=STANDBY

UNIX Permissions - table

Unix/Linux Permissions:

Here is the list of values and what they stand for in unix folder/directory permissions.Basically it is a simple you can count the value by using octa decimal binary numbers 421 :


See below image 



Value 0 directory listing is 000 = 0+0+0 
Value 1 directory listing is 001 = 0+0+1
Value 2 directory listing is 020 = 0+0+0  

This is where they gain their permission from :

so simple chmod 1 will give execute only permision on file/folder
so simple chmod 2 will give write only permision on file/folder
so simple chmod 3 will write & execute permision on file/folder

chmod 775 test.txt >> this mean 7 for 

UNIX Permissions

ValuePermissionDirectory Listing
0No read, no write, no execute---
1No read, no write, execute--x
2No read, write, no execute-w-
3No read, write, execute-wx
4Read, no write, no executer--
5Read, no write, executer-x
6Read, write, no executerw-
7Read, write, executerwx

Friday, December 11, 2015

DATABASE NAME MAX SIZE in ORACLE



This is for 11gR2:
The length of DB_NAME is limited to 8 characters;
DB_UNIQUE_NAME is limited to 30 characters. DB_UNIQUE_NAME  can contain alphanumeric, underscore (_), dollar ($), and pound (#) characters, but must begin with an alphabetic character.
INSTANCE_NAME defaults to ORACLE_SID. Oracle document does not specify the limit for INSTANCE_NAME length. However if you describe V$INSTANCE view you can see that column INSTANCE_NAME  has VARCHAR2(16), indicating the  limit of 16 characters.
Because INSTANCE_NAME defaults to ORACLE_SID you need to be careful when deciding SID before creating a database. Oracle does not clearly define the limit for SID (system identifier). Following are what I found from two 11gR2 documents about ORACLE_SID.

Thursday, November 19, 2015

Exporting partitions from a Partitioned tables

Exporting few partitions from a Partitioned table:

There might scenerios where you dont want to export the entire partitioned table for backup purposes. In this situations you can use simple expdp to do that.. Here is an example of

 Here is a sample expdp cmd to export part of entire partitioned tables.

I have a table named AUDIT_LOGONS in my database, which has partitions as below image ,




I want to export a part of it. partitions AUDIT_LOGONS1,AUDIT_LOGONS2 only


oracle@Linux1 $ expdp username/password directory=DPUMP dumpfile=AUDIT_PART1.dmp logfile=AUDIT_PART1.log TABLES=arvind.AUDIT_LOGONS:AUDIT_LOGONS4,arvind.AUDIT_LOGONS:AUDIT_LOGONS3


Export: Release 11.2.0.4.0 - Production on Mon Oct 12 16:29:59 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "arvind"."SYS_EXPORT_TABLE_01":  arvind/******** directory=DPUMP dumpfile=AUDIT_PART1.dmp logfile=AUDIT_PART1.log TABLES=arvind.AUDIT_LOGONS:AUDIT_LOGONS4,arvind.AUDIT_LOGONS:AUDIT_LOGONS3
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 120 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
:
:
:
:
Dump file set for arvind.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/dpump/AUDIT_PART1.dmp
Job "arvind"."SYS_EXPORT_TABLE_01" successfully completed at Mon Oct 12 16:32:02 2015 elapsed 0 00:01:56

ARCHIVE LOG Switch - shell script (for Oracle)


I think many of database admins will agree that it is always good idea to switch log file atleast every 20 mins or so. Especially when you have standby server setup. Doing this will ensure that redo logs are cleared up every 20 mins or so and they are shipped and applied onto standby database.

Script Benefits:

This is a simple shell script that uses plsql for checking the mount state of Database (oracle) and switch the archive log file when the open_mode of database is Read Write.  This script can be setup on both Primary and standby DB servers. This will avoid the enabling and disabling process on both servers after switchover/failover. You can setup this on crontab job on both Primary and standby DB servers, This will only switch log file if the DB is on Read Write mode

----------    Script Starts Here    -----------

#!/usr/bin/ksh

## ---- Created by Arvind Toorpu
## ----   Create Date 10/15/2015
## ----   This script can be scheduled on both PRIMARY AND STANDBY as it checks the role and switches logs
## ---- only if it is in PRIMARY ROLE
## ---- This script needs to be executed as DBA role user or user that has access to switch log file
## ---- Always good idea to switch logfile atleast every 20mins
## ----  Configure this using crontab 


mydate=`date "+%d%b%Y-%H:%M"`;
export ORACLE_HOME="/u01/app/oracle/product/11.2/db_1"
export ORACLE_SID="ORCL"
PATH=$PATH:$ORACLE_HOME/bin;export PATH;

sqlplus -s "/as sysdba" > /u01/app/oracle/admin/bin/logs/arch_switch/ARCH_SWITCH_$mydate.log <<EOF

SET SERVEROUTPUT ON;
SET FEED OFF;
declare
DB_UQ_NAME  varchar2(20);
CUR_STATUS  varchar2(20);
DB_ROLE     varchar2(20);
strQuery    varchar2(100);
CURR_ARCH number;
BEGIN
select DB_UNIQUE_NAME,open_mode,DATABASE_ROLE into DB_UQ_NAME,CUR_STATUS,DB_ROLE from v\$database;
select max(SEQUENCE#) into CURR_ARCH from  V\$ARCHIVED_LOG;
if CUR_STATUS='READ WRITE' and DB_ROLE='PRIMARY'
then
strQuery :=' alter system switch logfile';
DBMS_OUTPUT.PUT_LINE('DB_UNIQUE_NAME '||DB_UQ_NAME||' is currently in : '||CUR_STATUS ||' mode with Current ARch :'||CURR_ARCH);
EXECUTE immediate strQuery;
DBMS_OUTPUT.PUT_LINE('Switch logfile completed SUCESSFULLY !! ');
else
DBMS_OUTPUT.PUT_LINE('ERROR - DB_UNIQUE_NAME '||DB_UQ_NAME||' is currently in : '||CUR_STATUS||'.CANT SWITCH LOGFILE !!');
end if;
END;
/

EOF





Split Fullname into firstname and last name thru sql - Oracle


Today I was working around some queries and I had a requirement where in the data in table was being stored a FULLNAME. Now I have Split Fullname into First Name and Last Name. I have all the fullnames in the column seperated by a empty space ' '.
We can get the desired output by using the combination of SUBSTR and INSTR.


Lets create a sample table :

create table TESTTAB (ID number, FULLNAME varchar2(100));

Insert some data into it:

Insert into TESTTAB (ID,FULLNAME) values (1,'Jaff Schdt');
Insert into TESTTAB (ID,FULLNAME) values (2,'Bradee Will');
Insert into TESTTAB (ID,FULLNAME) values (3,'Kuck Dahl');
Insert into TESTTAB (ID,FULLNAME) values (5,'Melyssa man');
Insert into TESTTAB (ID,FULLNAME) values (6,'Melyssa man');
Insert into TESTTAB (ID,FULLNAME) values (7,'Shart Elarpre');
Insert into TESTTAB (ID,FULLNAME) values (8,'Rock Dihl');
Insert into TESTTAB (ID,FULLNAME) values (9,'Hia Dodd');
Insert into TESTTAB (ID,FULLNAME) values (10,'Pegy Sith');

select * from TESTTAB;

ID FULLNAME
-- -------------
1 Jaff Schdt
2 Bradee Will
3 Rock Dihl
5 Melyssa man
6 Melyssa man
7 Shart Elarpre
8 Rick Dihl
.
.


sample query :

SELECT
  ID,
  FULLNAME,
  SUBSTR(FULLNAME,0,(INSTR(FULLNAME,' ')       -1))  AS FIRST_NAME,
  SUBSTR(FULLNAME,(INSTR(FULLNAME,' ')         +1))  AS LAST_NAME,
  (SUBSTR(FULLNAME,0,(INSTR(FULLNAME,' ')      -1))
  ||SUBSTR(FULLNAME,(INSTR(FULLNAME,' ')))) as CONC_RESLT
FROM
  TESTTAB;

Sample Code Ouput :

ID FULLNAME FNAME LNAME CONCAT_RESLT
--- ------------ ------ ------ --------------

1 Jaff Schdt Jaff Schdt Jaff Schdt
2 Bradee Will Bradee Will Bradee Will
3 Kuck Dahl Kuck Dahl Kuck Dahl
5 Melyssa man Melyssa man Melyssa man
6 Melyssa man Melyssa man Melyssa man
7 Shart Elarpre Shart Elarpre Shart Elarpre
8 Rock Dihl Rock Dihl Rock Dihl
.
.

Tuesday, October 27, 2015

Export table data with filtered clause - Query based export (oracle expdp)



This parameter is used in conjunction with TABLE parameter of exp (export) utility of Oracle. This parameter will enable selection of rows from the list of tables mentioned in TABLE parameter. The value to this parameter is a WHERE clause for a SELECT statement which you would normally issue.

For Example: 

If you want to query all records of employees for a particular department you will use:

SELECT *
FROM HR.EMPLOYEES
WHERE dept = 10;


EXPDP UTILITY CMD :

$ expdp HR/XXXXX@ORCL directory=DPUMP dumpfile=TEST_EMP.dmp logfile=TEST_EMP.log include="HR.EMPLOYEES:\" in (where dept =10)\""



NORMAL EXPORT UTILITY CMD :

$ exp HR/XXXXX@ORCL file=/u01/app/oracle/dpump/TEST_EMP.dmp log=/u01/app/oracle/dpump/TEST_EMP.log tables=HR.EMPLOYEES query='"where dept =10"'



Sunday, October 18, 2015

Retrieve data from column as a row - Using LISTAGG function - Oracle

Retrieve data from column as a row.


Lets use employee table from Scott as a an example :

select * from scott.employee;

EMPLOYEE_ID FIRST_NAME    LAST_NAME      DEPT_NO     SALARY
----------- -------------------- -------------------- ---------- ----------
          1 Dan                  Morgan                       10     100000
          2 Helen                Lofstrom                     20     100000
          3 Akiko                Toyota                       20      50000
          4 Jackie               Stough                       20      40000
          5 Richard              Foote                        20      70000
          6 Joe                  Johnson                      20      30000
          7 Clark                Urling                       20      90000
          9 Richard              Foote                        20      70001
          8 Clark                Urling                       20      90001


Now I want to get the list of all users in a particular department. I want the output to be printed something like this :

I can achieve this by using the simple LISTAGG function provided by Oracle to get this results.In this below example I got ll the users ID,FNAME,LNAME in every dept.

DEPT_NO   EMP_DETAILS
 
        10   1:Dan-Morgan                                                                        
        20   2:Helen-Lofstrom,3:Akiko-Toyota,4:Jackie-Stough,5:Richard-Foote,6:Joe-                                                      Johnson,7:Clark-Urling,8:Clark-Urling,9:Richard-Foote
       


Code :

select DEPT_NO,LISTAGG(EMPLOYEE_ID ||':' ||FIRST_NAME||'-'||last_name,',')  WITHIN GROUP (order by EMPLOYEE_ID,first_name) "EMP DETAILS" from scott.employee group by dept_no;

usage - we can call listagg function and need to pass the columns that you want to retrieve data from and transform them into rows.

Listagg (column1||'-'||column2) make sure you pass a common column like the deptno thru which you want to group the data.

Monday, October 5, 2015

ORA-14074: partition bound must collate higher than that of the last partition



SQL>
create table TEST_PARTITION (c1 number) partition by range (c1)
    ( partition p100 values less than (100),
      partition p200 values less than (200),
      partition p300 values less than (300),
   partition pmax values less than (maxvalue));


Table created.

SQL> select high_value from dba_tab_partitions where table_name = 'TEST';

HIGH_VALUE
--------------------------------------------------------------------------------
100
200
300
MAXVALUE

SQL> alter table test add partition p40 values less than (400);
alter table test add partition p400 values less than (400)
                               *
ERROR at line 1:
ORA-14074: partition bound must collate higher than that of the last partition

SQL> alter table test split partition pmax at (400) into (partition p400, partition pmax);

Table altered.

SQL> select high_value from dba_tab_partitions where table_name = 'TEST_PARTITION';

HIGH_VALUE
--------------------------------------------------------------------------------
100
200
300
MAXVALUE
400

plsql - CASE STATEMENT

Like the IF statement, the CASE statement selects one sequence of statements to execute. However, to select the sequence, the CASE statement uses a selector rather than multiple Boolean expressions. A selector is an expression, whose value is used to select one of several alternatives.

Syntax:


CASE selector
    WHEN 'value1' THEN S1;
    WHEN 'value2' THEN S2;
    WHEN 'value3' THEN S3;
    ...
    ELSE Sn;  -- default case
END CASE;


Sample code:


DECLARE
  grade varchar2(1) :='&grade';
BEGIN
  CASE grade
  WHEN 'A' THEN
    dbms_output.put_line ('your grade is A as your score is above 70');
  WHEN 'B' THEN
    dbms_output.put_line ('your grade is B as your score is above 60 and below 70');
      WHEN 'C' THEN
    dbms_output.put_line ('your grade is C as your score is above 50 and below 60');
      WHEN 'D' THEN
    dbms_output.put_line ('your grade is D as your score is above 40 and below 50');
  END CASE;
END;

plsql - IF-THEN, IF-THEN-ELSE,IF-THEN-ELSIF STATEMENT

IF - THEN

it is the simplest form of IF control statement, frequently used in decision making and changing the control flow of the program execution.

The IF statement associates a condition with a sequence of statements enclosed by the keywords THEN and END IF. If the condition is TRUE, the statements get executed, and if the condition is FALSE or NULL, then the IF statement does nothing.

Syntax:
Syntax for IF-THEN statement is:

IF condition true THEN
{do this};

END IF;

sample code


declare
i number :=&i;
j number := &j;
k number :=&k;
begin

if i <=15 then
dbms_output.put_line ('value of i is ' ||i);
end if;
if j >= 20 and j <= 50 then
dbms_output.put_line ('value of i is ' ||j);
end if;
if k >= 50 and j <= 70 then
dbms_output.put_line ('value of i is ' ||j);
end if;
end;



IF-THEN-ELSE



A sequence of IF-THEN statements can be followed by an optional sequence of ELSE statements, which execute when the condition is FALSE.


Syntax for the IF-THEN-ELSE statement is:

IF condition THEN
   COND1;
ELSE
   COND2;
END IF;
Where, COND1 and COND2 are different sequence of statements. In the IF-THEN-ELSE statements, when the test condition is TRUE, the statement COND1 is executed and COND2 is skipped; when the test condition is FALSE, then COND1 is bypassed and statement COND2 is executed. For example:

IF color = red THEN
  dbms_output.put_line('You have chosen a red car')
ELSE
  dbms_output.put_line('Please choose a color for your car');
END IF;


Sample code

declare
i number :=&i;
j number := &j;
k number :=&k;
begin

if i <=15 then
dbms_output.put_line ('input value of i is with in limit of less than 15 :' ||i);
else
dbms_output.put_line ('input value of i is NOT with in limit of less than 15 :' ||i);
end if;
if j >= 20 and j <= 50 then
dbms_output.put_line ('input value of j is with in limit of (20 and 50) : ' ||j);
else
dbms_output.put_line ('input value of j is NOT with in limit of (20 and 50) :' ||j);
end if;
if k >= 50 and k <= 70 then
dbms_output.put_line ('input value of k is with in limit of (50 and 70) : ' ||k);
else
dbms_output.put_line ('input value of k is NOT with in limit of (50 and 70) : ' ||k);
end if;
end;



IF-THEN-ELSIF


The IF-THEN-ELSIF statement allows you to choose between several alternatives. An IF-THEN statement can be followed by an optional ELSIF...ELSE statement. The ELSIF clause lets you add additional conditions.

When using IF-THEN-ELSIF statements there are few points to keep in mind.

sample code 

declare
i number :=&i;

begin

if i <=20 then
dbms_output.put_line ('value of i is less than 21');
elsif i between 21 and  50 then
dbms_output.put_line ('value of i is bt 20 - 50');
elsif i between 51 and  70 then
dbms_output.put_line ('value of i is bt 50 - 70');
else
dbms_output.put_line ('value of i is greater than 71');
end if;
end;

Learn Plsql - Loops


Basic loop 

Basic loop structure encloses sequence of statements in between the LOOP and END LOOP statements. With each iteration, the sequence of statements is executed and then control resumes at the top of the loop.

Syntax:
The syntax of a basic loop in PL/SQL programming language is:

LOOP
  Sequence of statements;
END LOOP;


Sample code:

DECLARE
  i NUMBER :=1;
  BEGIN
  LOOP
    dbms_output.put_line('i value is'||i);
    i   := i+1;
    IF i > 10 THEN
      EXIT;
    END IF;
  END LOOP;
  --end loop;
  dbms_output.put_line(' this is end of loop');
END;


Sample output :

i value is1
i value is2
i value is3
i value is4
i value is5
i value is6
i value is7
i value is8
i value is9
i value is10
 this is end of loop




WHILE LOOP


A WHILE LOOP statement in PL/SQL programming language repeatedly executes a target statement as long as a given condition is true.

Syntax:


WHILE condition LOOP
   sequence_of_statements
END LOOP;

Sample code


DECLARE
  i NUMBER :=1;
BEGIN
  while i < 10 LOOP
    dbms_output.put_line('i value is'||i);
    i   := i+1;
  END LOOP;
  --end loop;
  dbms_output.put_line(' this is end of loop');
END;


Output :

i value is1
i value is2
i value is3
i value is4
i value is5
i value is6
i value is7
i value is8
i value is9
i value is10
 this is end of loop



 FOR LOOP

A FOR LOOP is a repetition control structure that allows you to efficiently write a loop that needs to execute a specific number of times.

Syntax:
FOR counter IN initial_value .. final_value LOOP
   sequence_of_statements;
END LOOP;



DECLARE
   i number(2);
BEGIN
   FOR i in 10 .. 15 LOOP
       dbms_output.put_line('value of i: ' || i);
  END LOOP;
END;
/

Thursday, October 1, 2015

Sample plsql code- for practice

This is sample  code 1:

declare
  test_message varchar2(30):='Hello World';
  message varchar2(20);
 begin
 message:='This is Arvind';
 DBMS_OUTPUT.PUT_LINE(test_message || ' ' || message);
 END;
 /

This is sample  code 2:


 declare
  num1 number:='10';
  num2 number:='3';
  num number;
  num3 number;
 begin
  num:= num1+ num2;
  DBMS_OUTPUT.PUT_LINE('This is num1: '||num1  || ', This is num2: ' || num2);
  DBMS_OUTPUT.PUT_LINE('This is the ouput num1+num2: ' || num);
 num3:=num+37;
 DBMS_OUTPUT.PUT_LINE('This is the ouput num3: ' || num3);
 END;
 /


Sample code 3:

declare
A1 number := &A1;
B1 number := &B1;
C1 number;
ERR exception;
begin

C1 :=A1 + B1;
if C1 <= 5 then
raise ERR;
else
DBMS_OUTPUT.PUT_LINE (' Value of C1 is '||C1||'' );
end if;
EXCEPTION
  WHEN ERR THEN
 DBMS_OUTPUT.PUT_LINE (' Value of C1 is less than 5' );
 end;
 

Wednesday, September 30, 2015

Adding disks to ASM library

[root@rac1 Desktop]# cd /dev

[root@rac1 dev]# ls sd*
sda  sda1  sda2  sdb  sdb1  sdc  sdc1  sdd  sdd1  sde  sde1  sdf  sdg

[root@rac1 dev]# /usr/sbin/oracleasm listdisks
DISK1
DISK2
DISK3
DISK4

[root@rac1 dev]# fdisk /dev/sdf

Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel with disk identifier 0xd8725891.
Changes will remain in memory only, until you decide to write them.
After that, of course, the previous content won't be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

WARNING: DOS-compatible mode is deprecated. It's strongly recommended to
         switch off the mode (command 'c') and change display units to
         sectors (command 'u').

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-652, default 1):
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-652, default 652):
Using default value 652

Command (m for help): p

Disk /dev/sdf: 5368 MB, 5368709120 bytes
255 heads, 63 sectors/track, 652 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0xd8725891

   Device Boot      Start         End      Blocks   Id  System
/dev/sdf1               1         652     5237158+  83  Linux

Command (m for help): w        
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.

[root@rac1 dev]# ls sd*
sda  sda1  sda2  sdb  sdb1  sdc  sdc1  sdd  sdd1  sde  sde1  sdf  sdf1  sdg

[root@rac1 dev]# fdisk /dev/sdg
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel with disk identifier 0x3ca5b760.
Changes will remain in memory only, until you decide to write them.
After that, of course, the previous content won't be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

WARNING: DOS-compatible mode is deprecated. It's strongly recommended to
         switch off the mode (command 'c') and change display units to
         sectors (command 'u').

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-652, default 1):
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-652, default 652):
Using default value 652

Command (m for help): p

Disk /dev/sdg: 5368 MB, 5368709120 bytes
255 heads, 63 sectors/track, 652 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x3ca5b760

   Device Boot      Start         End      Blocks   Id  System
/dev/sdg1               1         652     5237158+  83  Linux

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.
[root@rac1 dev]# ls sd*
sda  sda1  sda2  sdb  sdb1  sdc  sdc1  sdd  sdd1  sde  sde1  sdf  sdf1  sdg  sdg1

[root@rac1 dev]# /usr/sbin/oracleasm createdisk DISK5 /dev/sdf1
Writing disk header: done
Instantiating disk: done

[root@rac1 dev]# /usr/sbin/oracleasm createdisk DISK6 /dev/sdg1
Writing disk header: done
Instantiating disk: done

[root@rac1 dev]# /usr/sbin/oracleasm scandisks
Reloading disk partitions: done
Cleaning any stale ASM disks...
Scanning system for ASM disks...

[root@rac1 dev]# /usr/sbin/oracleasm listdisks
DISK1
DISK2
DISK3
DISK4
DISK5
DISK6

[root@rac1 dev]#

Thursday, September 24, 2015

ORA-39021: Database compatibility version 11.2.0.4.4.4.4 is not supported.



EXPDP ERROR :


oracle@Linux1:[/u01/app/oracle/dpump] $ expdp atoorpu directory=DPUMP dumpfile=SCOTT_24Sep2015.dmp logfile=SCOTT_24Sep2015.log schemas=SCOTT

Export: Release 11.2.0.4.0 - Production on Thu Sep 24 11:04:40 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39021: Database compatibility version 11.2.0.4.4.4.4 is not supported.


Solution :

oracle@Linux1:[/u01/app/oracle/dpump] $ expdp atoorpu directory=DPUMP dumpfile=SCOTT_24Sep2015.dmp logfile=SCOTT_24Sep2015.log schemas=SCOTT version=11.2.0

Wednesday, September 16, 2015

Oracle Which Patch has been applied?

In the events when you want to know what all patches have been applied to the oracle database, you can just goto oracle_home/opatch and type lsinventory. but you can also do the same by using the below query in the database. 

Which Patch has been applied?

SET linesize 200 pagesize 200
col action_time FOR a28
col version FOR a10
col comments FOR a35
col action FOR a25
col namespace FOR a12

SELECT * FROM registry$history;


ACTION_TIME                  ACTION                    NAMESPACE    VERSION            ID COMMENTS                         BUNDLE_SERIES
---------------------------- ------------------------- ------------ ---------- ---------- ----------------------------------- ------------------------------
24-AUG-13 12.03.45.119862 PM APPLY                     SERVER       11.2.0.4            0 Patchset 11.2.0.2.0              PSU
18-DEC-13 03.15.56.778103 PM APPLY                     SERVER       11.2.0.4            0 Patchset 11.2.0.2.0              PSU
09-NOV-14 05.22.06.808354 PM APPLY                     SERVER       11.2.0.4            4 PSU 11.2.0.4.4                   PSU
08-MAR-15 06.13.17.927522 PM APPLY                     SERVER       11.2.0.4            5 PSU 11.2.0.4.5                   PSU


Which Patch has been installed?

The easies way to list the installed patches in the current ORACLE_HOME is to use the patch utility.
List of installed patches:
$ORACLE_HOME/OPatch/opatch lsinventory

Grep on the patch description:
$ORACLE_HOME/OPatch/opatch lsinventory|grep "Patch description"
Patch description:  "Database Patch Set Update : 11.2.0.3.7 (16619892)"

A more detailed list on the installed patches:
$ORACLE_HOME/OPatch/opatch lsinventory -details

[PRCF-2010 : All connections to the remote nodes got refused. Cannot proceed with the file transfer. There has to be at least one remote node to send the installer files!


I was trying to setup Oracle RAC on my local machine and grid installation worked fine untill 65-70% and I got this weird error. After researching many blogs and sites. I was able to overcome that error and succesfully install the Grid Infrastructure.

[PRCF-2010 : All connections to the remote nodes got refused. Cannot proceed with the file transfer. There has to be at least one remote node to send the installer files!

I have some issues with the instalation of the 11G R2 Grid...

Passed all the prereq checks, everything working but when reaching the - Performing Remote Operations stage it freezes.

Checked the errors and when reaching that stage an error is dumped in the oraInstall.err file:

Exception in thread "Install API Thread" java.lang.NullPointerException
at oracle.cluster.deployment.ractrans.ClientHandlerSupervisor.threadCleanup(ClientHandlerSupervisor.java:926)
at oracle.cluster.deployment.ractrans.RACTransfer.cleanup(RACTransfer.java:1749)
at oracle.cluster.deployment.ractrans.RACTransfer.transferDirStructureToNodes(RACTransfer.java:746)
at oracle.cluster.deployment.ractrans.RACTransfer.transferDirToNodes(RACTransfer.java:252)
at oracle.ops.mgmt.cluster.ClusterCmd.transferDirToNodes(ClusterCmd.java:3103)
at oracle.ops.mgmt.cluster.ClusterCmd.transferDirToNodes(ClusterCmd.java:3022)
at oracle.sysman.oii.oiip.oiipg.OiipgClusterOps.transferDirToNodes(OiipgClusterOps.java:947)
at oracle.sysman.oii.oiif.oiifw.OiifwClusterCopyWCCE.doOperation(OiifwClusterCopyWCCE.java:544)
at oracle.sysman.oii.oiif.oiifb.OiifbCondIterator.iterate(OiifbCondIterator.java:171)
at oracle.sysman.oii.oiif.oiifw.OiifwActionsPhaseWCDE.doOperation(OiifwActionsPhaseWCDE.java:633)
at oracle.sysman.oii.oiif.oiifb.OiifbLinearIterator.iterate(OiifbLinearIterator.java:147)
at oracle.sysman.oii.oiic.OiicInstallAPISession$OiicAPISelCompsInstall.doOperation(OiicInstallAPISession.java:1058)
at oracle.sysman.oii.oiif.oiifb.OiifbCondIterator.iterate(OiifbCondIterator.java:171)
at oracle.sysman.oii.oiic.OiicInstallAPISession.doInstallAction(OiicInstallAPISession.java:642)
at oracle.sysman.oii.oiic.OiicInstallAPISession.access$000(OiicInstallAPISession.java:88)
at oracle.sysman.oii.oiic.OiicInstallAPISession$OiicActionsThread.run(OiicInstallAPISession.java:934)


In the installActions.log file the last lines are:

INFO: ORACLE_HOME is not settable, hence not setting the value
INFO: {Parameter:TOPLEVEL_COMPONENT in {Aggregate:OuiConfigVariables:1.0.0.0.0:common}}: Parameter data type is not compatible with the provided String Array.
INFO: passing params to cf done
INFO: done saving info by cf
INFO: Updating files in Oracle home '/u01/app/11.2.0/grid' to remote nodes 'oradb2'.
INFO: InstallProgressMonitor: Starting phase 16
INFO: List of files to be excluded from:install/excludeFileList.txt
INFO: Updating files in Oracle home '/u01/app/11.2.0/grid' to remote nodes 'oradb2'.
INFO: Updating files in Oracle home '/u01/app/11.2.0/grid' to remote nodes 'oradb2'.
INFO: Running command '/tmp/OraInstall2009-12-14_01-12-56AM/mvstubs.sh' on the nodes 'oradb2'.
INFO: Invoking OUI on cluster nodes oradb2
INFO: /tmp/OraInstall2009-12-14_01-12-56AM/mvstubs.sh
INFO: Copying Oracle home '/u01/app/11.2.0/grid' to remote nodes 'oradb2'.
INFO: Copying Oracle home '/u01/app/11.2.0/grid' to remote nodes 'oradb2'.


Solution that worked for me (I think it will work for you people to..) :

This happens basically for various reasons:

1. If both the servers are unable to contact each other due to firewall issues. In order to over this error u have to disable for firewall between both servers. You can do this going to system >> administration >>firewall >> disable.

2.The workaround was to stop the firewall on both nodes using "service iptables stop" during installation - this seems to resolve the issue. 

3. make sure SELINUX is disabled. 

4.Make sure your ssh connectivity is working fine between servers


I found this page very interesting about Multicast :

http://blog.trivadis.com/b/robertbialek/archive/2011/10/07/grid-infrastructure-11-2-0-3-and-multicasting.aspx

Adding disk group to ASM instance using ASMCA


Here is the step by step process on how to add diskgroup to existing ASM instance via ASMCA.

on cmd prompt set the ASM insatance and then invoke asmca. by using below cmd.

$ asmca















Column level triggers - Oracle

In this case I have  requirement where I need to update account_status column in same table with user status. When ever user deleted flag is 0

Lets create a table:

Create table users users (username varchar2(20)),fullname varchar2(30),account_status varchar2(10) default OPEN,deleted number(1), LOCK_DATE date);

Now insert some values:

insert into users values('ARVIND111','ARVIND KUMAR','',,''SYSDATE);
insert into users values('RAGHU111','RAGHU RAM','','',SYSDATE);
insert into users values('RAJ111','RAJ KUMAR','',''SYSDATE);
insert into users values('HARI111','HARI KRISHNA','',''SYSDATE);


Lets say you want to update a column with the account status 'OPEN' or 'LOCKED'. when ever we have a update in another column.

Sample :

In the below case, when ever we update deleted 0, we will update another column saying the account_status is open. If deleted =1, then the account_status is locked.

Lets create trigger now :

create or replace TRIGGER USER_LOCKDATE
BEFORE UPDATE OF DELETED ON users
FOR EACH ROW
BEGIN
  IF (:NEW.DELETED=0)
  then
  :NEW.ACCOUNT_STATUS := 'OPEN';
  ELSE
  :NEW.ACCOUNT_STATUS := 'LOCKED';
  :new.LOCK_DATE := SYSDATE;
  end if;
  END;

Note: 
Enable trigger by using. ( Alter trigger USER_LOCKDATE enable; )
Disable trigger by using. ( Alter trigger USER_LOCKDATE disable; )

Now with above trigger, when you update deleted column, it will update the account_status and lock_date.

COUNT ROWS FROM ALL TABLES AND PRINT THEM - plsql - oracle


Count rows in all tables or similar tables in database:


I have come across many situations as a DBA when I have to count the total number of rows in a tables in a table across the database. I have written this simple code to help us with that.

If you are a DBA you might also say why not just query the num_rows from all_tables or user_tables. But that is not possible as it might not give you the accurate count, as we all know that you cant get the right numbers with all_tables column, at least not until you have gather the stats for all these tables before you run the query.




-----   COUNT ROWS FROM ALL TABLES AND PRINT THEM :


DECLARE
  result sys_refcursor;
  strTableOwner VARCHAR2(100);
  strTableName1 VARCHAR2(100);
  strQuery      VARCHAR2(4000);
  rec           NUMBER;
BEGIN
  OPEN result FOR SELECT owner,table_name FROM all_tables WHERE table_name IN (
  'ABC') order by owner;    -->> you can alter this query as per your requirement
  LOOP
    FETCH
      result
    INTO
      strTableOwner,
      strTableName1;
    EXIT
  WHEN result%notfound;
    --- List schema and table being counted
    DBMS_OUTPUT.PUT_LINE('count for '||strTableOwner||'.'||strTableName1||' ');
    strQuery := 'select count(*) from '||strTableOwner||'.'||strTableName1||' ';
    EXECUTE immediate strQuery INTO rec;
    DBMS_OUTPUT.PUT_LINE(' >> '||rec||'');
  END LOOP;
  CLOSE result;
END;




Sample Output :

count for ATOORPU.ABC IS
 >> 5
count for SCOTT.ABC IS
 >> 1
count for SYS.ABC IS
 >> 8


Note : 

1) You can edit this plsql as per your requirement, it can count all tables in a schema if change all_tables to user_tables.

2) You can also get the count of different tables by changing the In clause in the cursor.




How To Change the Listener Log Filename Without Stopping the Listener

At the LSNRCTL prompt:

$ lsnrctl

LSNRCTL> set current_listener LISTENER 


Current Listener is LISTENER
LSNRCTL> set log_file /u01/app/oracle/diag/tnslsnr/linux01/listener/trace/listener1.log
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=linux01)(PORT=1521)))
LISTENER parameter "log_file" set to /u01/app/oracle/diag/tnslsnr/linux01/listener/trace/listener1.log
The command completed successfully


LSNRCTL> save_config


Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=linux01)(PORT=1521)))
Saved LISTENER configuration parameters.
Listener Parameter File   /u01/app/oracle/product/11.2/db_1/network/admin/listener.ora
Old Parameter File   /u01/app/oracle/product/11.2/db_1/network/admin/listener.bak
The command completed successfully


LSNRCTL> exit



Note : If you get this error "TNS-01251: Cannot set trace/log directory under ADR" refer to below link

click on this >> TNS-01251


TNS-01251: Cannot set trace/log directory under ADR



[oracle@linux01 trace]$ lsnrctl

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 16-SEP-2015 11:24:18

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> set current_listener LISTENER
Current Listener is LISTENER

LSNRCTL> set log_file listener1
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=linux01)(PORT=1521)))
TNS-01251: Cannot set trace/log directory under ADR 




Solution :


DIAG_ADR_ENABLED_LISTENER=OFF     -- >> add this line to you listener.ora


reload the listener.

$ lsnrctl reload 

Now lets try again to reset:

LSNRCTL> set current_listener LISTENER
Current Listener is LISTENER

LSNRCTL> set log_file /u01/app/oracle/diag/tnslsnr/linux01/listener/trace/listener1.log
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=linux01)(PORT=1521)))
LISTENER parameter "log_file" set to /u01/app/oracle/diag/tnslsnr/linux01/listener/trace/listener1.log
The command completed successfully

LSNRCTL> save_config
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=linux01)(PORT=1521)))
Saved LISTENER configuration parameters.
Listener Parameter File   /u01/app/oracle/product/11.2/db_1/network/admin/listener.ora
Old Parameter File   /u01/app/oracle/product/11.2/db_1/network/admin/listener.bak
The command completed successfully

Tuesday, September 15, 2015

information provided for listener is currently in use by another software - Virtualbox


I know this is a common problem that I have come across many time, while installing oracle software on Virtual machine  >>  Linux (Guest OS). I thought of sharing this info as this might help others to ..

Problem / Error :

I was trying to install oracle software on virtual box. I keep getting this error information provided for listener is currently in use by another software.

(or) 

Port you have provided xxxx (1523) is being used by another program.







Solution : 

Step 1 : Make sure you login as Root user.
Step 2 : make sure your valid ip address  in   >>    /etc/hosts
Step 3 : Make sure no other program is using listener port   >> netstat -ntap | grep 1521


In my case initially it had wrong ip address in the /etc/hosts  file.







PLSQL code to audit all (similar) tables in schema - oracle



I was playing around with some plsql code today.Just posting this sample plsql code as I thought this can help people.

This is a sample code that will search for all tables with name  ABC,ABC1,ACB2 in all schema's in database and execute a NOAUDIT against these tables. This sql can be modified to accommodate any changes where you want to run a query against all tables & schema's.


sample code:

declare
    result sys_refcursor;
    V_OWNER Varchar2(100):='SCHEMA_NAME'; --- update this with schema you want to audit.
    strTableOwner Varchar2(100);
strTableName Varchar2(100);
    strQuery varchar2(300);
begin

open result for
    select owner,table_name from user_tables where
    table_name in ('ABC','ABC1',ABC2')  and owner = 'V_OWNER' order by table_name;

loop
    fetch result into strTableOwner,strTableName;
    exit when result%notfound;
     
    DBMS_OUTPUT.PUT('NOAUDIT DELETE,UPDATE on '||strTableOwner||'.'||strTableName1||';');
   
strQuery := 'NOAUDIT DELETE,UPDATE on '||strTableOwner||'.'||strTableName1||' ';
    execute immediate strQuery;

 DBMS_OUTPUT.PUT_LINE(' ');
      DBMS_OUTPUT.PUT_LINE('SUCCESFULL');
end loop;

close result;

end;
/


Note : you can edit this part to what ever you want. >>>> NOAUDIT DELETE,UPDATE on

Sunday, September 6, 2015

BACKUP ORACLE HOME AND INVENTORY

BACKUP ORACLE HOME AND INVENTORY

Oracle Home and Inventory Backup
-----------------------------------------
tar -cvf $ORACLE_HOME $ORACLE_HOME/oraInventory | gzip > Backup_Software_Version.tar.gz

Note: 
tar -cvf <destination_location> <source_location>

ORACLE_HOME & ORACLE_CRS_HOME backup’s (for all nodes in RAC)

$ tar -zcvf db_1_bak.tar.gz db_1

$ cd /u01/app/oracle/product/11.2

$ ls -al
total 353532
drwxr-xr-x  3 oracle oinstall      4096 Sep  6 17:32 .
drwxrwxr-x  3 oracle oinstall      4096 Sep 12  2013 ..
drwxr-xr-x 80 oracle oinstall      4096 Jun 11 02:03 db_1
-rw-r--r--  1 oracle oinstall 361627648 Sep  6 17:34 db_1_bak.tar.gz


Controlfile Backup
---------------------
alter database backup controlfile to trace; 

show parameter user_dump_dest
(go to udump dest and make the note of controlfile trace)

Monday, August 17, 2015

Create Materialized Views

Create table table for views:

CREATE TABLE ABC 
( FNAME VARCHAR2(20 BYTE) 
, LNAME VARCHAR2(20 BYTE) 
, ID NUMBER NOT NULL 
, UPD_TSTAMP TIMESTAMP(6) DEFAULT systimestamp )
TABLESPACE USERS;
CREATE UNIQUE INDEX ABC_PK ON ABC (ID ASC) 
LOGGING 
TABLESPACE USERS ;

ALTER TABLE ABC
ADD CONSTRAINT ABC_PK PRIMARY KEY 
( ID ) USING INDEX ABC_PK
ENABLE;

Insert some data into it table:

SET DEFINE OFF;
Insert into ABC (FNAME,LNAME,ID,UPD_TSTAMP) values ('ravi','reddy',2,to_timestamp('09-JAN-15 11.44.39.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'));
Insert into ABC (FNAME,LNAME,ID,UPD_TSTAMP) values ('arvind','reddy',3,to_timestamp('16-JAN-15 12.06.01.293176000 PM','DD-MON-RR HH.MI.SSXFF AM'));
Insert into ABC (FNAME,LNAME,ID,UPD_TSTAMP) values ('raghu','kumar',4,to_timestamp('20-JAN-15 04.33.35.616985000 PM','DD-MON-RR HH.MI.SSXFF AM'));
Insert into ABC (FNAME,LNAME,ID,UPD_TSTAMP) values ('ranii','boy',5,to_timestamp('20-JAN-15 04.47.08.157119000 PM','DD-MON-RR HH.MI.SSXFF AM'));

Now Lets create Materialized view:

create materialized view mv as select * from abc ;

select * from ABC ;

FNAME                LNAME                        ID UPD_TSTAMP                    
-------------------- -------------------- ---------- -------------------------------
ravi                 reddy                         2 09-JAN-15 11.44.39.000000000 AM 
arvind               reddy                         3 16-JAN-15 12.06.01.293176000 PM 
raghu                kumar                         4 20-JAN-15 04.33.35.616985000 PM 
ranii                boy                           5 20-JAN-15 04.47.08.157119000 PM 

update t set lname = upper(val) where id=3;
update t set lname = upper(val) where id=4;


Note how, after the update, the view data matches the table data but the materialized view data does not. Data in materialized views must be refreshed to keep it synchronized with its base table. 

Refreshing can either be done manually, as below, or automatically by Oracle in some cases.

execute dbms_mview.refresh( 'MV' );


Cleanup :

drop materialized view mv ;