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

Value	Permission	Directory Listing
0	No read, no write, no execute	---
1	No read, no write, execute	--x
2	No read, write, no execute	-w-
3	No read, write, execute	-wx
4	Read, no write, no execute	r--
5	Read, no write, execute	r-x
6	Read, write, no execute	rw-
7	Read, write, execute	rwx

Configure second physical standby database - Oracle

I know there are various cases where in we have to setup a second physical standby database in our environments to have a redundant fail over strategy. I don't think there are enough documents out there that outline the process of adding the second standby database. I though I will share these steps today.

Parameters that should be considered while setting up the 2 node (single instance) standby Database.
In my case I will explain the parameters and their usage in setup. I am not going to explain the entire setup here. I you don't know how to setup physical standby database, please refer to this Physical_standby_setup.

At this point I am considering you know how to setup physical standby database and you are looking to add additional node to your setup.

Environments:

ORCL is PRIMARY INSTANCE and is on host        LINUX01   >> Primary Instance
ORCLSTB1 is STANDBY INSTANCE and is on host LINUX02  >> Standby already exists
ORCLSTB2 is STANDBY INSTANCE and is on host LINUX03  >>>  new instance to be added

PARAMETERS to be considered :

ARCHIVE_LOG_DEST_N   >>> these needs to be set to ship your logs to the new node,

This is a simple setup that can be used to makes sure all the three nodes will ship logs in case of switch over. In the below image I have configured such a way that each instance will send log files to other when they act as PRIMARY INSTANCE.

Note : I am using round robin process to ship logs between DB servers.

Example of Archive_Log_Dest setup

Adding Archive_log Dests:

ON DB SERVER Linux01 (ORCLPRIM):

alter system set log_archive_config='dg_config=(ORCL,ORCLSTB1,ORCLSTB2)';

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=ORCLSTB1 NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLSTB1';

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='SERVICE=ORCLSTB2 NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLSTB2';

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ENABLE;

ON DB SERVER Linux02 (ORCLSTB1):

alter system set log_archive_config='dg_config=(ORCL,ORCLSTB1,ORCLSTB2)';

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=ORCLPRIM NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL';

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='SERVICE=ORCLSTB2 NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLSTB2';

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ENABLE;

ON DB SERVER Linux03 (ORCLSTB2):

alter system set log_archive_config='dg_config=(ORCL,ORCLSTB1,ORCLSTB2)';

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=ORCLPRIM NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL';

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='SERVICE=ORCLSTB1 NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLSTB1';

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ENABLE;

OTHER PARAMETER FILE SETTINGS: 

PFILE DB_* PARAMETERS on DB SERVER1 (Linux01)(ORCL): 

log_archive_config='dg_config=(ORCL,ORCLSTB1,ORCLSTB2)';  >> This is to tell oracle that what all instances are part of this config

*.db_file_name_convert='/ORCLSTB1/','/ORCL/','/ORCLSTB2/','/ORCL/'        -- > add both servers so oracle knows where to create file. 

*.db_name='ORCL'  ### same across all Databases 

*.db_unique_name='ORCL'  ### Unique across each Database 

FAL_SERVER=ORCLSTB1, ORCLSTB2-- > add both servers so oracle knows where to get the archive files from incase of switchover. 

FAL_CLIENT=ORCLThis is always the Current DB server typically standby DB. This is ignored when the DB is in Primary mode 

PFILE DB_* PARAMETERS on DB SERVER2 (Linux02)(ORCLSTB1): 

log_archive_config='dg_config=(ORCL,ORCLSTB1,ORCLSTB2)';
*.db_file_name_convert='/ORCL/','/ORCLSTB1/','/ORCLSTB2/','/ORCLSTB1/' 

*.db_name='ORCL'### same across all Databases 

*.db_unique_name='ORCLSTB1'                    ### Unique across each Database 

*.FAL_CLIENT=STANDBY_SERVER 

*.FAL_SERVER=PRIMARY_SERVERS 

PFILE DB_* PARAMETERS on DB SERVER3 (Linux03)(ORCLSTB2): 

log_archive_config='dg_config=(ORCL,ORCLSTB1,ORCLSTB2)';
*.db_file_name_convert='/ORCLSTB1/','/ORCLSTB2/','/ORCL/','/ORCLSTB2/' 

*.db_name='ORCL'### same across all Databases 

*.db_unique_name='ORCLSTB2'                 ### Unique across each Database 

*.FAL_CLIENT=STANDBY_SERVER 

*.FAL_SERVER=PRIMARY_SERVERS 

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.

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
.
.

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"'

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.

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;

/

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;
 

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]#

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

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