Friday, November 23, 2012

Startup of the databases fail with ORA-00119, ORA-00132

 

Errors:

ORA-00119: invalid specification for system parameter REMOTE_LISTENER

ORA-00132: syntax error or unresolved network name 'usfsuad-scan.folsom.calwin.eds.com:60000'

SQL> startup mount

ORA-00119: invalid specification for system parameter REMOTE_LISTENER

ORA-00132: syntax error or unresolved network name ‘myDBsrvr-scan.siva.com:60000'

SQL> exit

Resolution:

To fix the issue, make sure EZCONNECT is in effective sqlnet.ora:   NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

Oracle uses an easy connect naming method to register Oracle Instances in SCAN Listeners by default. Therefore EZCONNECT keyword should be present in sqlnet.ora file.

Alternative:

Change the remote_listener setting to a tnsnames resolvable name allows the database to  start.

Ex:  remote_listener='myDBsrvr-scan.siva.com:60000' to be changed to testDB.siva

References:

    11gR2 Database Fails to Start With Error ORA-00132: syntax error or unresolved network name 'scanname:1521' [ID 1318026.1]

    SCAN: ORA-00119, ORA-00132 Incorrect Value for REMOTE_LISTENER [ID 1254929.1]

Tuesday, November 20, 2012

Data Deduplication

 

Data Deduplication

It eliminates the  of data and offer bandwidth optimization and storage space savings.

Environment it suits for;

  • File systems
  • Low rate Databases
  • Virtualizations
  • LAN/ SAN
  • Archive
  • Backup ( where more redundant data are there )

Deployment:
       Source ( Before ):
        Filesystem
        Virtualization
         Remote office Branch Office

      Target  ( After ):

        Database
        LAN / SAN

In computing, data deduplication is a specialized data compression technique for eliminating duplicate copies of repeating data. Related and somewhat synonymous terms are intelligent (data) compression and single-instance (data) storage. The technique is used to improve storage utilization and can also be applied to network data transfers to reduce the number of bytes that must be sent. In the deduplication process, unique chunks of data, or byte patterns, are identified and stored during a process of analysis. As the analysis continues, other chunks are compared to the stored copy and whenever a match occurs, the redundant chunk is replaced with a small reference that points to the stored chunk. Given that the same byte pattern may occur dozens, hundreds, or even thousands of times (the match frequency is dependent on the chunk size), the amount of data that must be stored or transferred can be greatly reduced

For example a typical email system might contain 100 instances of the same one megabyte (MB) file attachment. Each time the email platform is backed up, all 100 instances of the attachment are saved, requiring 100 MB storage space. With data deduplication, only one instance of the attachment is actually stored; the subsequent instances are referenced back to the saved copy for deduplication ratio of roughly 100 to 1.

Why deduplicate data?

Eliminating redundant data can significantly shrink storage requirements and improve bandwidth efficiency. Because primary storage has gotten cheaper over time, enterprises typically store many versions of the same information so that new work can re-use old work. Some operations like Backup store extremely redundant information. Deduplication lowers storage costs since fewer disks are needed, and shortens backup/recovery times since there can be far less data to transfer. In the context of backup and other nearline data, we can make a strong supposition that there is a great deal of duplicate data. The same data keeps getting stored over and over again consuming a lot of unnecessary storage space (disk or tape), electricity (to power and cool the disk or tape drives), and bandwidth (for replication), creating a chain of cost and resource inefficiencies within the organization

How does data deduplication work?

Deduplication segments the incoming data stream, uniquely identifies the data segments, and then compares the segments to previously stored data. If an incoming data segment is a duplicate of what has already been stored, the segment is not stored again, but a reference is created to it. If the segment is unique, it is stored on disk.

For example, a file or volume that is backed up every week creates a significant amount of duplicate data. Deduplication algorithms analyze the data and can store only the compressed, unique change elements of that file. This process can provide an average of 10-30 times or greater reduction in storage capacity requirements, with average backup retention policies on normal enterprise data. This means that companies can store 10TB to 30TB of backup data on 1 TB of physical disk capacity, which has huge economic benefits.

In-line deduplication

This is the process where the deduplication hash calculations are created on the target device as the data enters the device in real time. If the device spots a block that it already stored on the system it does not store the new block, just references to the existing block. The benefit of in-line deduplication over post-process deduplication is that it requires less storage as data is not duplicated. On the negative side, it is frequently argued that because hash calculations and lookups takes so long, it can mean that the data ingestion can be slower thereby reducing the backup throughput of the device. However, certain vendors with in-line deduplication have demonstrated equipment with similar performance to their post-process deduplication counterparts.

Target deduplication is the process of removing duplicates of data in the secondary store. Generally this will be a backup store such as a data repository or a virtual tape library

  • File Level Deduplication
  • Block Level Deduplication

Thursday, November 01, 2012

Creating Hard link and Soft Links in Unix

 

Hard Link Soft Link / symbolic  Link
If the inode entries are the same and if the count is 2 then they are hard linked.

if you see filename --> linked_directory
Then its a symbolic link.

la –lia ls -a
ln <source> <target file> (without the option –s which stands for soft link). ln  -s <source> <target file> (with the option –s which stands for soft link).
It is essentially create identical copies, No matter  which one you update,  both of them will get updated  
both links must reside on the same filesystem,
the source file must exist
that are not applicable to softlinks (source and target can be on seperate file systems, source does not have to exist, etc.)
additional I/O necessary to complete file access, additional storage taken up by softlink file's data
  A "soft" link does not increase the inode count for the referenced file, a "hard" link does.

If the count = 1, it's a soft link. If the count is > 1, it's a hard link.

Example of Viewing existing Hard link

ls –lia

1. Same iNode number  : 245787
2.  After permission column it lists 2

oracle@test:/app/oracle/product/11.2.0.3/grid/network/admin $ ls -lia
total 120
153544 drwxr-xr-x   4 oracle     dba           1024 Sep 12 04:27 ./
153543 drwxr-xr-x  11 oracle     dba           1024 Aug 27 17:39 ../
250089 drwxr-xr-x   2 oracle     dba           1024 Oct 17 12:58 OLD/
167535 -rw-r--r--   1 oracle     dba           1580 Sep  4 05:01 listener.old
153001 -rw-r--r--   1 oracle     dba           4524 Oct 17 13:01 listener.ora
159439 -rw-r--r--   1 oracle     dba            205 May 11  2011 shrept.lst
167531 -rw-r--r--   1 oracle     dba            881 Aug 27 18:17 sqlnet.ora
245787 -rw-r--r--   2 oracle     dba          43224 Sep 25 01:38 tnsnames.ora

oracle@test:/app/oracle/product/11.2.0.3/grid/network/admin $ cd $TNS_ADMIN

oracle@test:/app/oracle/product/11.2.0.3/db/network/admin $ ls -lia
total 102
168564 drwxr-xr-x   4 oracle     dba           1024 Sep  7 00:29 ./
168563 drwxr-xr-x  11 oracle     dba           1024 Aug 27 18:11 ../
158839 drwxr-xr-x   2 oracle     dba           1024 Sep 25 01:35 OLD/
164004 -rw-r--r--   1 oracle     dba           2690 Sep  7 00:29 listener.ora
168565 drwxr-xr-x   2 oracle     dba             96 Aug 27 18:00 samples/
178592 -rw-r--r--   1 oracle     dba            205 May 11  2011 shrept.lst
245790 -rw-r--r--   1 oracle     dba            881 Aug 27 18:17 sqlnet.ora
245787 -rw-r--r--   2 oracle     dba          43224 Sep 25 01:38 tnsnames.ora

Example of viewing SoftLink

oracle@test1:/app/grid/product/11.2.0.3/network/admin $ ls -lia
total 82
   343 drwxr-xr-x   4 oracle     dba           1024 Aug 24 17:01 ./
   342 drwxr-xr-x  11 oracle     dba           1024 Aug  9 09:55 ../
16349 drwxr-xr-x   2 oracle     dba           1024 Sep  7 12:43 OLD/
12489 -rw-r--r--   1 oracle     dba            186 Aug  9 10:26 endpoints_listener.ora
12165 -rw-r--r--   1 oracle     dba           1568 Sep  7 12:46 listener.ora
12488 -rw-r--r--   1 oracle     dba            534 Aug  9 10:26 listener.ora.bak.usfspfd1
12477 -rw-r--r--   1 oracle     dba            368 Aug  9 10:26 listener12080910AM2605.bak
   344 drwxr-xr-x   2 oracle     dba             96 Aug  9 09:49 samples/
  5108 -rw-r--r--   1 oracle     dba            205 May 11  2011 shrept.lst
22677 -rw-r--r--   1 oracle     dba            561 Aug 13 18:33 sqlnet.ora
12487 -rw-r--r--   1 oracle     dba            646 Aug  9 10:26 sqlnet.ora_temp
22674 -rw-r--r--   1 oracle     dba          29904 Aug 23 16:09 tnsnames.old
 42504 lrwxr-xr-x   1 oracle     dba             58 Aug 24 17:01 tnsnames.ora@ -> /app/oracle/product/11.2.0.3/db/network/admin/tnsnames.ora
oracle@test1:/app/grid/product/11.2.0.3/network/admin $ cd $TNS_ADMIN
oracle@test1:/app/oracle/product/11.2.0.3/db/network/admin $ ls -lia
total 82
150342 drwxr-xr-x   4 oracle     dba           1024 Aug 21 16:29 ./
150341 drwxr-xr-x  11 oracle     dba           1024 Aug  9 11:36 ../
214228 drwxr-xr-x   2 oracle     dba           1024 Oct 30 22:55 OLD/
268044 -rw-r-----   1 oracle     dba           3554 Aug 14 14:43 listener.ora
150343 drwxr-xr-x   2 oracle     dba             96 Aug  9 11:23 samples/
170627 -rw-r--r--   1 oracle     dba            205 May 11  2011 shrept.lst
268082 -rw-r--r--   1 oracle     dba            561 Aug 14 14:50 sqlnet.ora
266832 -rw-r--r--   1 oracle     dba          32061 Oct  9 10:42 tnsnames.ora

Purging Oracle ADR (Automatic Diagnostic Repository) logs

 

When ADR logs become unexpectedly large, or too many (as in too many databases for one host), then manual purging is necessary. Following Scripts will be of handy during this time.


#!/bin/ksh
# Purge ADR logs
# Call oraenv first (or at least set $ORACLE_HOME)

# -- Customize the following variables
D1=1440    # to purge older than one day
D5=7200    # to purge older than five days
D7=10080   # to purge older than seven days
DD=        # set to your ADR_BASE (Ex. /app/oracle)
HP=        # set to your ADR_HOME (Ex. diag/rdbms/<dbname>/<instname>)

$ORACLE_HOME/bin/adrci <<_EOF
set base $DD
set homepath $HP
show homes
purge -age $D7 -type alert
purge -age $D7 -type incident
purge -age $D7 -type trace
purge -age $D7 -type cdump
quit
_EOF

Daily check on the database before business volume picks up

Session Count

SQL>
    SELECT a.inst_id, COUNT (1) "Session Count"
     FROM sys.gv_$session a
    GROUP BY a.inst_id
SQL> /

   INST_ID Session Count
---------- -------------
         1            67
         2           105

Long Operation – Time Remaining is greater than Zero.

SQL> l
    SELECT * FROM sys.gv_$session_longops
    WHERE  TIME_REMAINING > 0

DBMS Jobs

SELECT job,
       SUBSTR (what, 1, 30),
       schema_user,
       last_date,
       next_date,
       broken,
       NVL2 (this_date, 'Y', 'N') running
  FROM dba_jobs s
ORDER BY schema_user, last_date

Buffer Gets

Replace the v.sql text with the frequent SQL statements important to your application.

SELECT v.inst_id,
       ROUND (v.buffer_gets / v.executions, 1) avggets,
       v.executions,
       v.module,
       SUBSTR (v.sql_text, 1, 60) querysql
  FROM sys.gv_$sql v
WHERE v.buffer_gets > 0
   AND v.executions > 0
   AND v.executions IS NOT NULL
   AND DECODE (executions, 0, NULL, (v.buffer_gets / v.executions)) > 10
   AND (v.sql_text LIKE 'FROM TBL_ABC%'
     OR  v.sql_text LIKE 'FROM TBL_XYZ%') 
ORDER BY v.inst_id, avggets DESC;