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 - ORA-00205: error in identifying control file

[root@cacti XE]# su - oracle

-bash-4.1$ ls

admin  connect  diag  exit  fast_recovery_area  oradata  oradiag_oracle  product

-bash-4.1$ cd oradata/

-bash-4.1$ cd XE/

-bash-4.1$ ls

control.dbf  sysaux.dbf  system.dbf  temp.dbf  undotbs1.dbf  users.dbf

-bash-4.1$ ll

total 1285700

-rw-r-----. 1 oracle dba   9748480 Dec 10 07:11 control.dbf

-rw-r-----. 1 oracle dba 744497152 Dec 10 07:10 sysaux.dbf

-rw-r-----. 1 oracle dba 377495552 Dec 10 07:10 system.dbf

-rw-r-----. 1 oracle dba  20979712 Dec  9 22:00 temp.dbf

-rw-r-----. 1 oracle dba  78651392 Dec 10 07:05 undotbs1.dbf

-rw-r-----. 1 oracle dba 104865792 Dec  8 12:41 users.dbf

-bash-4.1$ pwd

/u01/app/oracle/oradata/XE

-bash-4.1$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Dec 11 10:47:30 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> alter database mount;

alter database mount

*

ERROR at line 1:

ORA-00205: error in identifying control file, check alert log for more info

SQL> alter system set control_files='/u01/app/oracle/oradata/XE/control.dbf' scope=spfile;

System altered.

SQL> startup force;

ORACLE instance started.

Total System Global Area  521936896 bytes

Fixed Size     2228080 bytes

Variable Size   322961552 bytes

Database Buffers   192937984 bytes

Redo Buffers     3809280 bytes

Database mounted.

Database opened.

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME

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

HOST_NAME

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

VERSION   STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT

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

LOGINS   SHU DATABASE_STATUS INSTANCE_ROLE     ACTIVE_ST BLO EDITION

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

      1 XE

cacti.siltechconsult.com.br

11.2.0.2.0   11-DEC-14 OPEN NO       1 STOPPED

ALLOWED    NO  ACTIVE PRIMARY_INSTANCE   NORMAL    NO  XE

SQL> 

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 - Swiss penknife QUERY

Swiss penknife QUERY

SHOW AND KILL USER CONNECTED 

SQL> alter system kill session ''' ||sid|| ',' || serial#|| ''' immediate;' from v$session where username='TESTE';

SHOW SIZE BY DATAFILE

SQL> select file_id, block_id, blocks*8192/1024 MB,

owner || '.' || segment_name "Name", block_id*8192/1024 "Position MB"

from  sys.dba_Extents

where file_id = &&fileid

union

select file_id, block_id, blocks*8192/1024, 'Free' "Name", block_id*8192/1024 "P

osition MB"

from sys.dba_free_space

where file_id = &fileid

order by 1,2,3

/

SHOW INFO BY OBJECTS

SQL> select owner, object_name, object_type, status from all_objects where object_name='TSILGT';

OWNER       OBJECT_NAME       OBJECT_TYPE   STATUS

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

TREINA       STP_GRAVATABLOG       PROCEDURE   VALID

SANKHYA        STP_GRAVATABLOG       PROCEDURE   VALID

WMS       STP_GRAVATABLOG       PROCEDURE   VALID

TESTE       STP_GRAVATABLOG       PROCEDURE   INVALID

SQL> alter procedure teste.STP_GRAVATABLOG compile;

Warning: Procedure altered with compilation errors.

SQL> show error

Errors for PROCEDURE TESTE.STP_GRAVATABLOG:

LINE/COL ERROR

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

33/5 PL/SQL: SQL Statement ignored

46/9 PL/SQL: ORA-00942: table or view does not exist

SQL> SELECT text FROM all_source WHERE name = 'STP_GRAVATABLOG'  and owner='TESTE' ORDER BY line;

SHOW SIZE BY TABLE OR SEGMENTS

SQL>select sum(bytes/1024/1024) as Tamanho_MB from dba_segments

where owner = 'SVP'

and segment_type = 'TABLE'

and segment_name in ('TABLE_1','TABLE_2','TABLE_N')

SQL>select onwer, segment_name, segment_type, tablespace_name, bytes/1024/1024 

from dba_segments where segment_name='TABLE_NAME';

SHOW DATAFILES INFO GENERAL/STATUS 

SQL> select tablespace_name, file_name, bytes/1024/1024 mbytes, AUTOEXTENSIBLE, MAXBYTES/1024/1024 maxbytes, INCREMENT_BY/1024/1024 nextmb from dba_data_files;

TABLESPACE FILE_NAME MBYTES AUT   MAXBYTES   NEXTMB

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

USERS   /u01/app/oracle/oradata/XE/users.dbf    100 YES 11264 .001220703

SYSAUX   /u01/app/oracle/oradata/XE/sysaux.dbf   710 YES 32767.9844 .001220703

UNDOTBS1   /u01/app/oracle/oradata/XE/undotbs1.dbf     75 YES 32767.9844 .000610352

SYSTEM   /u01/app/oracle/oradata/XE/system.dbf   360 YES   600 .001220703

SHOW ALL INX / REBUILD

SQL>select 'alter index ' || owner || '.' || index_name ||' rebuild;'
from dba_indexes
where status ='UNUSABLE'
order by index_name
/

in construction ... 

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