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

[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

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.