ORACLE - ORATOP

oratop - Utility for Near Real-time Monitoring of Databases, RAC and Single Instance (Doc ID 1500864.1)

oratop: Release 14.1.2

Usage: 

oratop [ [Options] [Logon] ]

Logon:

{username[/password][@connect_identifier] | / }

[AS {SYSDBA|SYSOPER}]

connect_identifier:

    o Net Service Name, (TNS) or

    o Easy Connect (host[:port]/[service_name])

Options:

    -d : real-time (RT) wait events, section 3 (default is Cumulative)

    -k : FILE#:BLOCK#, section 4 lt is (EVENT/LATCH)

    -m : MODULE/ACTION, section 4 (default is USERNAME/PROGRAM)

    -s : SQL mode, section 4 (default is process mode)

    -c : database service mode (default is connect string) 

    -f : detailed format, 132 columns (default: standard, 80 columns)

    -b : batch mode (default is text-based user interface)

    -n : maximum number of iterations (requires number)

    -i : interval delay, requires value in seconds (default: 5s)

    -v : oratop release version number

    -h : this help

[root@cacti tmp]# ./oratop.RDBMS_11.2_LINUX_X64 siltech/siltech@xe -f -s

Oracle 11g - Primary XE     09:10:05 up: 4.9d,   1 ins,    1 sn,   1 us, 561M mt,    4% fra,   0 er,                      17.1% db

ID %CPU LOAD %DCU   AAS  ASC  ASI  ASW  ASP  AST  UST MBPS IOPS IORL LOGR PHYR PHYW  %FR   PGA TEMP UTPS UCPS SSRT DCTR DWTR  %DBT

 1   23    0   19   0.7    2    0    0    0    2    2    0   40  42u  15k    0    0   96   63M  13M    0  141 244u  116    0   100

EVENT (C)                                                         TOTAL WAITS   TIME(s)  AVG_MS  PCT                    WAIT_CLASS

DB CPU                                                                             4580           59                              

resmgr:cpu quantum                                                     144969      2211    15.3   28                     Scheduler

log file parallel write                                                249928       414     1.7    5                    System I/O

log file sync                                                          186382       408     2.2    5                        Commit

control file parallel write                                            151533       211     1.4    3                    System I/O

ID  USERNAME  MODULE   ACTION  SQL_ID          SQL_TEXT               X  ELAP  CPUT  IOWT  WAIT  EXEC  ROWS  BUFG  DISK  BH%  LOAD

 1  LAURA     perl@ca          74vma1wu3pf7w   select * from all_tab  S   26m   25m    3u     0  4.3k   119   862     0  100     1

[root@cacti ~]# su - oracle

-bash-4.1$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Dec 16 09:23:58 2014

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

Connected to:

Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> select sql_text from v$sql where sql_id='74vma1wu3pf7w' union select username from v$session where sql_id='74vma1wu3pf7w';

SQL_TEXT

------------------------------------------------------------------------------------------------------------------------

LAURA

select * from all_tables

SQL> /

[oracle@orasrvteste ]$ ./oratop.RDBMS_11.2_LINUX_X64 -h
./oratop.RDBMS_11.2_LINUX_X64: error while loading shared libraries: libclntsh.so.11.1: cannot open shared object file: No such file or directory

[oracle@orasrvteste lib]$ cd $ORACLE_HOME/lib

[oracle@orasrvteste lib]$export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib;

[oracle@orasrvteste lib]$ ll libclntsh.so*
lrwxrwxrwx 1 oracle oinstall       17 May 14  2010 libclntsh.so -> libclntsh.so.10.1
-rwxrwx--- 1 oracle oinstall 20651386 May 14  2010 libclntsh.so.10.1
[oracle@orasrvteste lib]$ cp libclntsh.so.10.1 libclntsh.so.11.1
[oracle@orasrvteste lib]$ oratop.RDBMS_11.2_LINUX_X64 -h
oratop: Release 14.1.2
Usage:
         oratop [ [Options] [Logon] ]

         Logon:
                {username[/password][@connect_identifier] | / }
                [AS {SYSDBA|SYSOPER}]

                connect_identifier:
                     o Net Service Name, (TNS) or
                     o Easy Connect (host[:port]/[service_name])
         Options:
             -d : real-time (RT) wait events, section 3 (default is Cumulative)
             -k : FILE#:BLOCK#, section 4 lt is (EVENT/LATCH)
             -m : MODULE/ACTION, section 4 (default is USERNAME/PROGRAM)
             -s : SQL mode, section 4 (default is process mode)
             -c : database service mode (default is connect string)
             -f : detailed format, 132 columns (default: standard, 80 columns)
             -b : batch mode (default is text-based user interface)
             -n : maximum number of iterations (requires number)
             -i : interval delay, requires value in seconds (default: 5s)
             -v : oratop release version number
             -h : this help

[oracle@orasrvteste lib]$

Read More

PERL - LINUX CONNECT SQLServer

[root@cacti SQLServer]# ./sql_connect.pl 

Successful Connection.

Current Connection Properties
---------------------------------------------------------------------------------------
    SQL Server Instance : SRV-DB
    SQL Server Version  : Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) 
    Current Database    : master
    Current User        : sa
    Client Machine      : blogger.blogger.com.br

Available Databases
-----------------------------------------------------------------
    DATABASE                                          35178.13 MB
    DTBTESTE                                          35178.13 MB
    DBTTESTE2                                         32835.31 MB
    MSSQL SYSTEM RESOURCE                                61.06 MB
    msdb                                                 21.25 MB
    ReportServer                                         17.81 MB
    tempdb                                                8.50 MB
    master                                                8.13 MB
    ReportServerTempDB                                    3.06 MB
    model                                                 3.00 MB

Disconnecting from SQL Server.

[root@cacti SQLServer]# cat sql_connect.pl 
#!/usr/bin/perl -w
use warnings;
use strict;

use DBI;

my $user   = "sa";
my $passwd = "passwd";
my $server = "122.128.0.0";

my $dbh = DBI->connect("DBI:Sybase:server=$server", $user, $passwd, {PrintError => 0});
my $sth;

my $sqlStatement;

unless ($dbh) {
    die "ERROR: Failed to connect to server ($server).\nERROR MESSAGE: $DBI::errstr";
} else {
print "\n";
print "Successful Connection.";
print "\n\n";
}

print "Current Connection Properties\n";
print "---------------------------------------------------------------------------------------\n";
$sqlStatement = "select \@\@servername, \@\@version, db_name(), system_user, host_name()";
unless ($sth = $dbh->prepare($sqlStatement)) {
$dbh->disconnect;
die "ERROR: Failed to prepare SQL statement.\nSQL: $sqlStatement\nERROR MESSAGE: $DBI::errstr";
}
unless ($sth->execute) {
$dbh->disconnect;
die "ERROR: Failed to execute query.\nSQL: $sqlStatement\nERROR MESSAGE: $DBI::errstr";
}
while ((  my $serverName, my $sqlServerVersion, my $currentDatabase, my $currentUser, my $clientMachine ) = $sth->fetchrow) {
(my $sqlServerVersion, my @dummy) = split(/\n/, $sqlServerVersion);

print "    SQL Server Instance : $serverName\n";
print "    SQL Server Version  : $sqlServerVersion\n";
print "    Current Database    : $currentDatabase\n";
print "    Current User        : $currentUser\n";
print "    Client Machine      : $clientMachine\n";
}

print "\n";
$sth->finish;
print "Available Databases\n";
print "-----------------------------------------------------------------\n";
$sqlStatement = "select isnull(db_name(dbid), 'MSSQL SYSTEM RESOURCE') as Name,
  str(convert(dec(15),sum(size))* 8192/ 1048576,10,2)+ N' MB' as Size
from sys.sysaltfiles group by dbid order by 2 desc";
unless ($sth = $dbh->prepare($sqlStatement)) {
$dbh->disconnect;
die "ERROR: Failed to prepare SQL statement.\nSQL: $sqlStatement\nERROR MESSAGE: $DBI::errstr";
}
unless ($sth->execute) {
$dbh->disconnect;
die "ERROR: Failed to execute query.\nSQL: $sqlStatement\nERROR MESSAGE: $DBI::errstr";
}
while (( my $databaseName, my $databaseSize) = $sth->fetchrow) {
printf("    %-40s %20s\n", $databaseName, $databaseSize);  
}

print "\n";
$sth->finish;
print "Disconnecting from SQL Server.\n\n";
$dbh->disconnect;
exit(0);

[root@cacti SQLServer]# 

Read More

ORACLE - ADD DISK ASM/Diskgroup - Automatic Storage Management (ASM)

Automatic Storage Management (ASM)

login as: root
root@192.168.0.239's password:
Last login: Tue Dec  9 19:10:42 2014 from 192.168.0.206
[root@db1 ~]# fdisk -l

Disk /dev/sda: 150.3 GB, 150323855360 bytes
255 heads, 63 sectors/track, 18275 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: 0x000dc94c

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *           1          26      204800   83  Linux
Partition 1 does not end on cylinder boundary.
/dev/sda2              26        4203    33554432   82  Linux swap / Solaris
/dev/sda3            4203        9303    40960000   83  Linux
/dev/sda4            9303       18276    72080384    5  Extended
/dev/sda5            9303       18276    72079360   83  Linux

Disk /dev/sdc: 53.7 GB, 53687091200 bytes
255 heads, 63 sectors/track, 6527 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: 0x90a18a8c

   Device Boot      Start         End      Blocks   Id  System
/dev/sdc1               1        6527    52428096   83  Linux

Disk /dev/sdd: 53.7 GB, 53687091200 bytes
255 heads, 63 sectors/track, 6527 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: 0x3a45183b

   Device Boot      Start         End      Blocks   Id  System
/dev/sdd1               1        6527    52428096   83  Linux

Disk /dev/sde: 53.7 GB, 53687091200 bytes
255 heads, 63 sectors/track, 6527 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: 0xde587821

   Device Boot      Start         End      Blocks   Id  System
/dev/sde1               1        6527    52428096   83  Linux

Disk /dev/sdb: 53.7 GB, 53687091200 bytes
255 heads, 63 sectors/track, 6527 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: 0x50b618df

   Device Boot      Start         End      Blocks   Id  System
/dev/sdb1               1        6527    52428096   83  Linux

Disk /dev/sdf: 53.7 GB, 53687091200 bytes
255 heads, 63 sectors/track, 6527 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: 0x2932987d

   Device Boot      Start         End      Blocks   Id  System
/dev/sdf1               1        6527    52428096   83  Linux

Disk /dev/sdh: 107.4 GB, 107374182400 bytes
255 heads, 63 sectors/track, 13054 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: 0x00000000


Disk /dev/sdg: 214.7 GB, 214748364800 bytes
255 heads, 63 sectors/track, 26108 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: 0xbe2c0835

   Device Boot      Start         End      Blocks   Id  System
/dev/sdg1               1       26108   209712478+  83  Linux
[root@db1 ~]# fdisk -l /dev/sdh

Disk /dev/sdh: 107.4 GB, 107374182400 bytes
255 heads, 63 sectors/track, 13054 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: 0x00000000

[root@db1 ~]# cat /proc/partitions
major minor  #blocks  name

   2        0          4 fd0
  11        0    1048575 sr0
   8        0  146800640 sda
   8        1     204800 sda1
   8        2   33554432 sda2
   8        3   40960000 sda3
   8        4          1 sda4
   8        5   72079360 sda5
   8       32   52428800 sdc
   8       33   52428096 sdc1
   8       48   52428800 sdd
   8       49   52428096 sdd1
   8       64   52428800 sde
   8       65   52428096 sde1
   8       16   52428800 sdb
   8       17   52428096 sdb1
   8       80   52428800 sdf
   8       81   52428096 sdf1
   8      112  104857600 sdh
   8       96  209715200 sdg
   8       97  209712478 sdg1

[root@db1 ~]# fdisk /dev/sdh
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel with disk identifier 0xbe78fa85.
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-13054, default 1):
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-13054, default 13054):
Using default value 13054

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

Calling ioctl() to re-read partition table.
Syncing disks.
[root@db1 ~]# cat /proc/partitions
major minor  #blocks  name

   2        0          4 fd0
  11        0    1048575 sr0
   8        0  146800640 sda
   8        1     204800 sda1
   8        2   33554432 sda2
   8        3   40960000 sda3
   8        4          1 sda4
   8        5   72079360 sda5
   8       32   52428800 sdc
   8       33   52428096 sdc1
   8       48   52428800 sdd
   8       49   52428096 sdd1
   8       64   52428800 sde
   8       65   52428096 sde1
   8       16   52428800 sdb
   8       17   52428096 sdb1
   8       80   52428800 sdf
   8       81   52428096 sdf1
   8      112  104857600 sdh
   8      113  104856223 sdh1
   8       96  209715200 sdg
   8       97  209712478 sdg1
[root@db1 ~]# partprobe
Warning: WARNING: the kernel failed to re-read the partition table on /dev/sda (Device or resource busy).  As a result, it may not reflect all of your changes until after reboot.
Warning: WARNING: the kernel failed to re-read the partition table on /dev/sdb (Device or resource busy).  As a result, it may not reflect all of your changes until after reboot.
Warning: WARNING: the kernel failed to re-read the partition table on /dev/sdc (Device or resource busy).  As a result, it may not reflect all of your changes until after reboot.
Warning: WARNING: the kernel failed to re-read the partition table on /dev/sdd (Device or resource busy).  As a result, it may not reflect all of your changes until after reboot.
Warning: WARNING: the kernel failed to re-read the partition table on /dev/sde (Device or resource busy).  As a result, it may not reflect all of your changes until after reboot.
Warning: WARNING: the kernel failed to re-read the partition table on /dev/sdf (Device or resource busy).  As a result, it may not reflect all of your changes until after reboot.
Warning: WARNING: the kernel failed to re-read the partition table on /dev/sdg (Device or resource busy).  As a result, it may not reflect all of your changes until after reboot.
[root@db1 ~]# /etc/init.d/oracleasm listdisks
ASM01
ASM02
ASM03
ASM04
ASM05
[root@db1 ~]# su - oracle
[oracle@db1 ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Wed Dec 10 12:04:36 2014

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

SQL> connect sys as sysdba
Enter password:
Connected to an idle instance.
SQL> quit
Disconnected
[oracle@db1 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 10-DEC-2014 12:05:09

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                09-DEC-2014 18:35:20
Uptime                    0 days 17 hr. 29 min. 49 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/app/oracle/product/11.2.0/grid/network/admin/listener.ora
Listener Log File         /opt/app/oracle/diag/tnslsnr/db1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=blogger.blogger.intranet)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@db1 ~]$ lsnrctl stop

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 10-DEC-2014 12:05:17

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
The command completed successfully
[oracle@db1 ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Wed Dec 10 12:05:26 2014

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

SQL> connect sys as sysdba
Enter password:
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.

Total System Global Area 1.5667E+10 bytes
Fixed Size                  2268632 bytes
Variable Size            2113929768 bytes
Database Buffers         1.3522E+10 bytes
Redo Buffers               28127232 bytes
SQL> select name, path from v$asm_disk;

NAME                     PATH
---------------------    ------------------------------------
DSKDATA_0000   /dev/oracleasm/disks/ASM01

DSKDATA_0001   /dev/oracleasm/disks/ASM02

DSKDATA_0002    /dev/oracleasm/disks/ASM03

DSKDATA_0003   /dev/oracleasm/disks/ASM04

DSKDATA_0004   /dev/oracleasm/disks/ASM05


SQL> quit
Disconnected from Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
[oracle@db1 ~]$ su -
Password:
[root@db1 ~]# /etc/init.d/oracleasm createdisk AMS06 /dev/sdh1
Marking disk "AMS06" as an ASM disk:                       [  OK  ]
[root@db1 ~]# su - oracle
[oracle@db1 ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Wed Dec 10 12:10:44 2014

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

SQL> connect sys as sysdba
Enter password:
Connected.
SQL> select path from v$asm_disk;

PATH
--------------------------------------------------------------------------------
/dev/oracleasm/disks/AMS06
/dev/oracleasm/disks/ASM03
/dev/oracleasm/disks/ASM02
/dev/oracleasm/disks/ASM04
/dev/oracleasm/disks/ASM01
/dev/oracleasm/disks/ASM05

6 rows selected.

SQL> select name, total_mb, free_mb from v$asm_diskgroup;

NAME                             TOTAL_MB    FREE_MB
------------------------------ ----------            ----------
DSKDATA                       255995             57

SQL> quit
Disconnected from Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
With the Automatic Storage Management option
[oracle@db1 ~]$ cat /etc/oratab
#Backup file is  /opt/app/oracle/product/11.2.0/grid/srvm/admin/oratab.bak.db1 line added by Agent
#



# This file is used by ORACLE utilities.  It is created by root.sh
# and updated by either Database Configuration Assistant while creating
# a database or ASM Configuration Assistant while creating ASM instance.

# A colon, ':', is used as the field terminator.  A new line terminates
# the entry.  Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
#   $ORACLE_SID:$ORACLE_HOME:<N|Y>:
#
# The first and second fields are the system identifier and home
# directory of the database respectively.  The third filed indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
+ASM:/opt/app/oracle/product/11.2.0/grid:N
TESTE:/opt/app/oracle/product/11.2.0/db_1:N             # line added by Agent
PRD:/opt/app/oracle/product/11.2.0/db_1:N          # line added by Agent
[oracle@db1 ~]$ export ORACLE_SID=+ASM
[oracle@db1 ~]$ export ORACLE_HOME=/opt/app/oracle/product/11.2.0/grid
[oracle@db1 ~]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@db1 ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Wed Dec 10 12:13:09 2014

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

SQL> connect sys as sysasm
Enter password:
Connected.
SQL> alter diskgroup DSKDATA add disk '/dev/oracleasm/disks/AMS06';

Diskgroup altered.

SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Automatic Storage Management option
[oracle@db1 ~]$ export ORACLE_SID=PROD
[oracle@db1 ~]$ export ORACLE_HOME=/opt/app/oracle/product/11.2.0/db_1
[oracle@db1 ~]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@db1 ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Wed Dec 10 12:17:15 2014

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

SQL> connect sys as sysdba
Enter password:
Connected.
SQL> select name, total_mb, free_mb from v$asm_diskgroup;

NAME                             TOTAL_MB    FREE_MB
------------------------------ ----------            ----------
DSKDATA                       358393              102453

SQL>



 

Read More

ORACLE - ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

-bash-4.1$ oratop siltech/123456@XE

oratop: Release 13.2.4 Production on Mon Dec  8 12:26:17 2014

Copyright (c) 2011, Oracle.  All rights reserved.

Processing ...

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

-bash-4.1$ 

-bash-4.1$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 08-DEC-2014 12:25:50

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=XE))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XE)))

STATUS of the LISTENER

------------------------

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production

Start Date                08-DEC-2014 11:32:30

Uptime                    0 days 0 hr. 53 min. 20 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Default Service           XE

Listener Parameter File   /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora

Listener Log File         /u01/app/oracle/diag/tnslsnr/cacti/listener/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=XE))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XE)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.20.30.200)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XE)))

The listener supports no services

The command completed successfully

-bash-4.1$ 

-bash-4.1$ lsnrctl servite XE

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 08-DEC-2014 12:33:01

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

NL-00853: undefined command "servite".  Try "help"

-bash-4.1$ lsnrctl service XE

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 08-DEC-2014 12:33:06

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.20.30.200)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XE)))

The listener supports no services

The command completed successfully

-bash-4.1$ 

-bash-4.1$ sqlplus siltech/123456

SQL*Plus: Release 11.2.0.2.0 Production on Mon Dec 8 12:46:02 2014

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

Connected to:

Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> show parameter service_names;

NAME     TYPE VALUE

------------------------------------ ----------- ------------------

service_names     string XE

SQL> 

x-bash-4.1$ pwd

/u01/app/oracle/product/11.2.0/xe/network/admin

-bash-4.1$ cat listener.ora 

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST =10.20.30.200 )(PORT = 1521))

)

)

DEFAULT_SERVICE_LISTENER = (XE)

-bash-4.1$ 

-bash-4.1$ vim listener.ora 

-bash-4.1$ cat listener.ora 

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = XE)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe)

)

         )

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST =10.20.30.200 )(PORT = 1521))

)

)

DEFAULT_SERVICE_LISTENER = (XE)

-bash-4.1$ 

-bash-4.1$ lsnrctl reload

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 08-DEC-2014 16:33:07

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=cacti.siltechconsult.com.br)(PORT=1521)))

The command completed successfully

-bash-4.1$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 08-DEC-2014 16:33:10

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=cacti.siltechconsult.com.br)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production

Start Date                08-DEC-2014 16:07:07

Uptime                    0 days 0 hr. 26 min. 2 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Default Service           XE

Listener Parameter File   /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora

Listener Log File         /u01/app/oracle/diag/tnslsnr/cacti/listener/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=cacti.siltechconsult.com.br)(PORT=1521)))

Services Summary...

Service "XE" has 1 instance(s).

  Instance "XE", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

-bash-4.1$ 

-bash-4.1$ sqlplus siltech/123456@XE

SQL*Plus: Release 11.2.0.2.0 Production on Mon Dec 8 16:34:26 2014

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

Connected to:

Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> 

        

          

Read More

Script Rename File / Windows / Expdp

@echo off
Rem Get Day,Mth & Year from %Date%
set Day=%Date:~0,2%
set Mth=%Date:~3,2%
set Yr=%Date:~6,4%
ren 1.txt 1_%Day%-%Mth%-%Yr%_I.txt

@echo off
ren texte.txt texte_%Date:~0,2%%Date:~3,2%%Date:~6,4%.txt


set ORACLE_SID=TEST

C:\oracle\app\Administrador\product\11.2.0\dbhome_1\BIN\expdp.exe siltech/siltech@INSTANCE parfile=F:\Oracle\Backup\expdp.par

rename F:\export\expfull_TEST.log expfull_TEST_%Date:~0,2%%Date:~3,2%%Date:~6,4%.log
       
rename F:\export\EXPFULL_TEST.dmp expfull_TEST_%Date:~0,2%%Date:~3,2%%Date:~6,4%.dmp
       
forfiles -p "F:\export\" -d -5 -m *.dmp -c "cmd /c del /f /q @path"

Read More

Bytecode [ Perlcc is dead on RedHat 6.* -> PP new Solution ]

#Commands
CD_IN='cd'
CD_OUT='cd ..'
TAR='tar -xvzf'
PERL_MAKEFILE='perl Makefile.PL'
MAKE='make'
MAKE_INSTALL='make install'
CHMOD='chmod +x Makefile'

#Module
M1=IO-Compress-2.060
M2=Archive-Zip-1.30
M3=Getopt-ArgvFile-1.11
M4=Module-ScanDeps-1.10
M5=PAR-1.007
M6=PAR-Dist-0.49
M7=AutoLoader-5.73
M8=Compress-Raw-Zlib-2.060
M9=AutoLoader-5.73
M10=CPAN-Meta-Requirements-2.122
M11=CPAN-Meta-YAML-0.008
M12=JSON-PP-2.27202
M13=Parse-CPAN-Meta-1.4404
M14=version-0.9902
M15=CPAN-Meta-2.130880
M16=Perl-OSType-1.003
M17=Module-Metadata-1.000011
M18=Module-Build-0.4004
M19=Text-ParseWords-3.29
M20=Test-Harness-3.26
M21=Time-Local-1.2300
M22=File-Path-2.09
M23=File-Temp-0.2301
M24=Test-Simple-0.98
M25=PathTools-3.40
M26=ExtUtils-MakeMaker-6.66
M27=IO-1.25

#Uncompress
FILE_GZ=`ls *.gz`
for i in ${FILE_GZ} ; do \
       ${TAR} ${i}
done;

#Execute Install
for i in ${M1} ${M2} ${M3} ${M4} ${M5} ${M6} ${M7} ${M8} ${M9} ${M10} ${M11} ${M12} ${M13} ${M14} ${M15} ${M16} ${M17} ${M18} ${M19} ${M20} ${M21} ${M22} ${M23} ${M24} ${M25} ${M26} ${M27} ${M28} ${M29} ; do \
${CD_IN} ${i}
${PERL_MAKEFILE}
${CHMOD}
${MAKE}
${MAKE_INSTALL}
${CD_OUT}
done;

Read More

Oracle - [ Alter Database Datafile ]

 1* select file_id,file_name,bytes/1024/1024,maxbytes/1024/1024 from dba_data_files order by file_id
SQL> /

   FILE_ID FILE_NAME  BYTES/1024/1024 MAXBYTES/1024/1024
---------- ------------------------------ --------------- ------------------
1 +DSKDATA/oralee/datafile/syste      740  32767.9844
  m.334.783430381

2 +DSKDATA/oralee/datafile/sysau      810  32767.9844
  x.335.783430383

3 +DSKDATA/oralee/datafile/undot      815  32767.9844
  bs1.336.783430385

4 +DSKDATA/oralee/datafile/algor     4000 8000
  .338.783430389

5 +DSKDATA/oralee/datafile/algor     4000 8000
  _indices.339.783430395

6 +DSKDATA/oralee/datafile/undot      200  32767.9844
  bs2.340.783430401

7 +DSKDATA/oralee/datafile/users   161.25  32767.9844
  .341.783430401

SQL> alter database datafile 1 autoextend on next 500m maxsize 8000m;

Database altered.

SQL> alter database datafile 2 autoextend on next 500m maxsize 8000m;

Database altered.

SQL> l

  1* alter database datafile 2 autoextend on next 500m maxsize 8000m

SQL> c/2/3/

  1* alter database datafile 3 autoextend on next 500m maxsize 8000m

SQL> /

Database altered.

SQL> c/3/4/

  1* alter database datafile 4 autoextend on next 500m maxsize 8000m

SQL> c/4/6/

  1* alter database datafile 6 autoextend on next 500m maxsize 8000m

SQL> /

Database altered.

SQL> c/6/7/

  1* alter database datafile 7 autoextend on next 500m maxsize 8000m

SQL> /

Database altered.

SQL> set pages 120

SQL> set lines 100

SQL> column file_name format a30

SQL> /

   FILE_ID FILE_NAME  BYTES/1024/1024 MAXBYTES/1024/1024

---------- ------------------------------ --------------- ------------------

1 +DSKDATA/oralee/datafile/syste      740 8000

  m.334.783430381

2 +DSKDATA/oralee/datafile/sysau      810 8000

  x.335.783430383

3 +DSKDATA/oralee/datafile/undot      815 8000

  bs1.336.783430385

4 +DSKDATA/oralee/datafile/algor     4000 8000

  .338.783430389

5 +DSKDATA/oralee/datafile/algor     4000 8000

  _indices.339.783430395

6 +DSKDATA/oralee/datafile/undot      200 8000

  bs2.340.783430401

7 +DSKDATA/oralee/datafile/users   161.25 8000

  .341.783430401

7 rows selected.

Read More

Rman / ORACLE

$rman target / nocatalog

Recovery Manager: Release 10.2.0.5.0 - Production on Tue Dec 11 16:05:05 2012

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

connected to target database: SFT (DBID=2981456125)
using target database control file instead of recovery catalog

RMAN> delete noprompt archivelog until time 'sysdate-2/24';

Read More

Oracle Tablespace [ Check +ASM | Add Tablespace]

Check +ASM

SQL> select name, total_mb, free_mb from v$asm_diskgroup;

Check Disk

SQL> show parameter create

Add Tablespace

SQL> alter tablespace users add datafile size 1000m autoextend on next 500m maxsize 8000m;

Check Tablespace autoextend

SQL> desc dba_data_files;

SQL> select Tablespace_name, file_name, bytes/1024/1024 mbytes, AUTOEXTENSIBLE, MAXBYTES/1024/1024 maxbytes, INCREMENT_BY nextmb

from dba_data_files

Read More

Windows [ Add Variables PATH ] Oracle

C:\Users\Administrador>sqlplus /nolog
'sqlplus' não é reconhecido como um comando interno
ou externo, um programa operável ou um arquivo em lotes.

C:\Users\Administrador>echo %PATH%
C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\strawberry\c\bin;C:\s
trawberry\perl\site\bin;C:\strawberry\perl\bin;C:\Windows\System32\WindowsPowerS
hell\v1.0\

C:\Users\Administrador>set PATH=%PATH%;C:\oraclexe\app\oracle\product\11.2.0\ser
ver\bin

C:\Users\Administrador>echo %PATH%
C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\strawberry\c\bin;C:\s
trawberry\perl\site\bin;C:\strawberry\perl\bin;C:\Windows\System32\WindowsPowerS
hell\v1.0\;C:\oraclexe\app\oracle\product\11.2.0\server\bin

C:\Users\Administrador>sqlplus /nolog

SQL*Plus: Release 11.2.0.2.0 Production on Qua Nov 7 01:27:48 2012

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

SQL> connect sys as sysdba
Informe a senha:
Conectado.
SQL>
SQL>

Read More

Perl - Info about the module [ DBD/DBI ] Oracle

[root@localhost Desktop]# perl -e 'use DBD::Oracle; print $DBD::Oracle::VERSION,"\n";'
1.50
[root@localhost Desktop]# perl -e 'use DBI; print $DBI::VERSION,"\n";
1.622

Read More

Install [ Oracle 11g XE on Red Hat ]

#rpm -i oracle-xe-11.2.0-1.0.x86_64.rpm
Executing post-install steps...
You must run '/etc/init.d/oracle-xe configure' as the root user to configure the database.

[root@localhost Disk1]# /etc/init.d/oracle-xe configure

Oracle Database 11g Express Edition Configuration
-------------------------------------------------
This will configure on-boot properties of Oracle Database 11g Express
Edition.  The following questions will determine whether the database should
be starting upon system boot, the ports it will use, and the passwords that
will be used for database accounts.  Press <Enter> to accept the defaults.
Ctrl-C will abort.

Specify the HTTP port that will be used for Oracle Application Express [8080]:

Specify a port that will be used for the database listener [1521]:

Specify a password to be used for database accounts.  Note that the same
password will be used for SYS and SYSTEM.  Oracle recommends the use of
different passwords for each database account.  This can be done after
initial configuration:
Confirm the password:

Do you want Oracle Database 11g Express Edition to be started on boot (y/n) [y]:y

Starting Oracle Net Listener...Done
Configuring database...Done
Starting Oracle Database 11g Express Edition instance...Done
Installation completed successfully.

----------------------------------------------------------------------------------------------------------
 

Edit .bashrc

[root@localhost ~]# vim .bashrc
# User specific aliases and functions

alias rm='rm -i'
alias cp='cp -i'
alias mv='mv -i'


# Source global definitions
if [ -f /etc/bashrc ]; then
        . /etc/bashrc
fi

#ORACLE CONFIG
ORACLE_HOSTNAME=localhost.localdomain; export ORACLE_HOSTNAME
ORACLE_UNQNAME=DB11G; export ORACLE_UNQNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/xe; export ORACLE_HOME
ORACLE_SID=XE; export ORACLE_SID
NLS_LANG=`$ORACLE_HOME/bin/nls_lang.sh`; export NLS_LANG
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

if [ $USER = "oracle" ]; then
  if [ $SHELL = "/bin/ksh" ]; then
    ulimit -p 16384
    ulimit -n 65536
  else
    ulimit -u 16384 -n 65536
  fi
fi

----------------------------------------------------------------------------------------------------------
[root@localhost ~]# sqlplus

SQL*Plus: Release 11.2.0.2.0 Production on Wed Sep 19 10:52:11 2012

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

Enter user-name: system
Enter password:

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL>

Read More

Bioinformatics BIT/LGMB

Bioinformatics BIT/LGMB

Data validation IC/TCC

Jobs ...

 

 Find the error !!!

Thanks all ...

Prof. Wilson, Prof. Israel, Daniel, Marcelo, Rodrigo, Meire, Jorge, Rippel, Burger, Thais, Erico, Alonso, Gilson.  

Read More

Perl - [ Perl DBI Oracle connect from Windows ]

use strict;
use warnings;
use DBD::Oracle qw(:ora_types);
use DBI;

use constant SqlOracle => 'C:\oraclexe\app\oracle\product\10.2.0\server\BIN\sqlplus.exe\\';
use constant User => 'system';
use constant Pass => '12345';
use constant DB => 'XE';

print "Create connection Oracle Database 10g Express Edition  ... 1\n";
my $dbh = DBI->connect("dbi:Oracle:".DB,User,Pass) or die $!;

print "Disconnect Oracle Database 10g Express Edition ...\n";
$dbh->disconnect;

print "Create connection Oracle Database 10g Express Edition  ... 2\n";
$dbh = DBI->connect("dbi:Oracle:".DB,User."/".Pass) or die $!;

print "Disconnect Oracle Database 10g Express Edition ...\n";
$dbh->disconnect;
print "Create connection Oracle Database 10g Express Edition  ... 3\n";
$dbh = DBI->connect("dbi:Oracle:",User."@".DB,Pass) or die $!;

print "Disconnect Oracle Database 10g Express Edition ...\n";
$dbh->disconnect;

Read More