12C Grid Installation – “PRVF-0002: could not retrieve local node name”

Selamlar,

12C grid kurulumuna başlarken ./runInstaller’ı çalıştırdıktan sonra aşağıdaki gibi bir hata aldım;

“PRVF-0002: could not retrieve local node name”

grid12c

Çözüm;

/etc/hosts dosyasını kontrol etmek gerekiyor. Hostname yazılmamış ve ya yanlış yazılmış olabilir.

[root@oel65 grid]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain

192.168.60.103 oel65.12c.asm..gantek.com oel6.5

/etc/hosts’u değiştirdim ve sorun çözüldü.

[root@oel65 grid]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain

192.168.60.103 oel65.12c.asm.gantek.com oel6.5

Kolay gelsin.

 

OEL 6.5 DNS server configuration

Selamlar,

RAC kurulumları için local’de DNS server kurmak gerekebilir. DNS server yapılandırması için aşağıdaki adımları uygulayabilirsiniz. DNS’e giriş yapacağım ip’ler ve hostname’leri aşağıdaki gibi;

192.168.56.50    dns.erman.com
192.168.56.10    hebmuller1.erman.com
192.168.56.11    hebmuller2.erman.com

1. bind RPM’ini yüklüyorum

yum install bind

2. /etc/named.conf dosyasını editliyorum.

[root@dns named]# cat /etc/named.conf
options {
        listen-on port 53 { any; };
        listen-on-v6 port 53 { ::1; };
        directory       "/var/named";
        dump-file       "/var/named/data/cache_dump.db";
        statistics-file "/var/named/data/named_stats.txt";
        memstatistics-file "/var/named/data/named_mem_stats.txt";
        allow-query     { any; };
        recursion yes;

        dnssec-enable yes;
        dnssec-validation yes;
        dnssec-lookaside auto;

        /* Path to ISC DLV key */
        bindkeys-file "/etc/named.iscdlv.key";

        managed-keys-directory "/var/named/dynamic";
};

logging {
        channel default_debug {
                file "data/named.run";
                severity dynamic;
        };
};

zone "." IN {
        type hint;
        file "named.ca";
};

zone "erman.com" IN {
        type master;
        file "forward.erman.com";
        allow-update { none; };
};


zone "56.168.192.in-addr.arpa" IN {
        type master;
        file "reverse.erman.com";
        allow-update { none; };
};

include "/etc/named.rfc1912.zones";
include "/etc/named.root.key";

3. /var/named/forward.erman.com dosyasını editliyorum.

[root@dns named]# cat /var/named/forward.erman.com
$TTL 1D
@       IN SOA  dns.erman.com. root.erman.com. (
                                        0       ; serial
                                        1D      ; refresh
                                        1H      ; retry
                                        1W      ; expire
                                        3H )    ; minimum

@       IN      NS      dns.erman.com.

@       IN      A       192.168.56.50
@       IN      A       192.168.56.10
@       IN      A       192.168.56.11

dns     IN      A       192.168.56.50
hebmuller1   IN      A       192.168.56.10
hebmuller2   IN      A       192.168.56.11

4. /var/named/reverse.erman.com dosyasını editliyorum.

[root@dns named]# cat /var/named/reverse.erman.com
$TTL 1D
@       IN SOA  dns.erman.com. root.erman.com. (
                                        0       ; serial
                                        1D      ; refresh
                                        1H      ; retry
                                        1W      ; expire
                                        3H )    ; minimum

@       IN      NS      dns.erman.com.
@       IN      PTR     erman.com.

dns     IN      A       192.168.56.50
hebmuller1   IN      A       192.168.56.10
hebmuller2   IN      A       192.168.56.11

50      IN      PTR     dns.erman.com.
10      IN      PTR     hebmuller1.erman.com.
11      IN      PTR     hebmuller2.erman.com.

5. kontrolleri yapıyorum

chgrp named /var/named/*.erman.*
named-checkzone gantek.com /var/named/forward.erman.com
named-checkzone 56.168.192.in-addr.arpa /var/named/reverse.erman.com

6. named servisini restart ediyorum.

[root@dns named]# service named restart
Stopping named:                                            [  OK  ]
Starting named:                                            [  OK  ]

7. Client tarafında /etc/resolv.conf dosyasını editliyorum.

[root@hebmuller1 ~]# cat /etc/resolv.conf
# No nameservers found; try putting DNS servers into your
# ifcfg files in /etc/sysconfig/network-scripts like so:
#
# DNS1=xxx.xxx.xxx.xxx
# DNS2=xxx.xxx.xxx.xxx
# DOMAIN=lab.foo.com bar.foo.com
search erman.com
nameserver 192.168.56.50

8. nslookup ile kontrol ediyorum.

[root@hebmuller1 ~]# nslookup hebmuller2
Server:         192.168.56.50
Address:        192.168.56.50#53

Name:   hebmuller2.erman.com
Address: 192.168.56.11

[root@hebmuller1 ~]# nslookup 192.168.56.11
Server:         192.168.56.50
Address:        192.168.56.50#53

11.56.168.192.in-addr.arpa      name = hebmuller2.erman.com.

Kolay gelsin.

Fractured Block Corruption Solution

Selamlar,

Prod ortamında bir datafile’da “fractured” block corruption ile karşılaştık. Aldığımız hatalar;

Hex dump of (file 73, block 2525) in trace file /oracle/app/diag/rdbms/spark/spark/trace/spark_ora_22533.trc
Corrupt block relative dba: 0x124009dd (file 73, block 2525)
Fractured block found during backing up datafile
Data in bad block:
type: 6 format: 2 rdba: 0x124009dd
last change scn: 0x0000.23e58731 seq: 0x1 flg: 0x06
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x00000000
check value in block header: 0xb5
computed block checksum: 0x90bf
Reread of blocknum=2525, file=+DATA1/spark/datafile/ts_gprs_cdr_001ind_32k.428.877783237. found same corrupt data
Reread of blocknum=2525, file=+DATA1/spark/datafile/ts_gprs_cdr_001ind_32k.428.877783237. found same corrupt data
Reread of blocknum=2525, file=+DATA1/spark/datafile/ts_gprs_cdr_001ind_32k.428.877783237. found same corrupt data
Reread of blocknum=2525, file=+DATA1/spark/datafile/ts_gprs_cdr_001ind_32k.428.877783237. found same corrupt data
Reread of blocknum=2525, file=+DATA1/spark/datafile/ts_gprs_cdr_001ind_32k.428.877783237. found same corrupt data
RMAN-03009: failure of backup command on ch00 channel at 08/02/2015 08:04:42
ORA-19566: exceeded limit of 0 corrupt blocks for file +DATA1/spark/datafile/ts_gprs_cdr_001ind_32k.428.877783237

Nedir bu “fractured” block corruption derseniz Oracle’ın açıklaması şu şekilde;

A Fractured Block means that the block is incomplete and considered a Physical Corruption.  Information from the block header does not match the block end / tail.  It is a clear symptom about issues within the Operating System (OS) / Hardware layers.

In order to understand why a Fractured Block happens, we need to understand how a block is written into disk. The block size at OS level does not match the block size at Oracle level, so in order to write an Oracle block, the Operating System needs to perform more than one write.  As an example: if OS block size is 512 bytes and Oracle block size is 8K, the OS performs 16 writes in order to complete the write process.

Oracle keeps track off the header of each block and before writing down to disk updates a 4 byte field/value in the tail of each block (tailchk) to guarantee afterwards consistency check that the block is complete after written.

Corruption’ı Tespit Etme

İlk önce aşağıdaki yöntemler ile corruption’ın tam olarak hangi datafile’da ve block’da olduğunu tespit ediyorum.

  1. RMAN ile advise alabilirsiniz.
    RMAN> list failure;
    List of Database Failures
    =========================
    Failure ID Priority Status    Time Detected Summary
    ---------- -------- --------- ------------- -------
    67041      HIGH     OPEN      28-JUN-15     Datafile 73: '+DATA1/spark/datafile/ts_gprs_cdr_001ind_32k.428.877783237' contains one or more corrupt blocks
    RMAN> advise failure;
    List of Database Failures
    =========================
    Failure ID Priority Status    Time Detected Summary
    ---------- -------- --------- ------------- -------
    67041      HIGH     OPEN      28-JUN-15     Datafile 73: '+DATA1/spark/datafile/ts_gprs_cdr_001ind_32k.428.877783237' contains one or more corrupt blocks
    analyzing automatic repair options; this may take some time
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=582 device type=DISK
    analyzing automatic repair options complete
    Mandatory Manual Actions
    ========================
    1. No backup of block 2525 in file 73 was found. Drop and re-create the associated object (if possible), or use the DBMS_REPAIR package to repair the block corruption
    2. Contact Oracle Support Services if the preceding recommendations cannot be used, or if they do not fix the failures selected for repair
    Optional Manual Actions
    =======================
    no manual actions available
    Automated Repair Options
    ========================
    no automatic repair options available
  2. Database üzerinden bakıyorum.
    SQL>  select * from V$DATABASE_BLOCK_CORRUPTION;
         FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
    ---------- ---------- ---------- ------------------ ---------
            73       2525          1                  0 FRACTURED
  3. dbverify ile bakıyorum.
    oracle@fmsdb $ dbv file="+DATA1/spark/datafile/ts_gprs_cdr_001ind_32k.428.877783237" blocksize=32768 userid=sys/0racle
    DBVERIFY: Release 11.2.0.4.0 - Production on Sat Aug 15 18:01:14 2015
    Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
    DBVERIFY - Verification starting : FILE = +DATA1/spark/datafile/ts_gprs_cdr_001ind_32k.428.877783237
    Page 2525 is marked corrupt
    Corrupt block relative dba: 0x124009dd (file 73, block 2525)
    Bad header found during dbv:
    Data in bad block:
    type: 11 format: 2 rdba: 0x12400001
    last change scn: 0x0000.00000000 seq: 0x1 flg: 0x04
    spare1: 0x0 spare2: 0x0 spare3: 0x0
    consistency value in tail: 0x00000b01
    check value in block header: 0x67e1
    computed block checksum: 0x0
    DBVERIFY - Verification complete
    Total Pages Examined         : 229376
    Total Pages Processed (Data) : 0
    Total Pages Failing   (Data) : 0
    Total Pages Processed (Index): 229279
    Total Pages Failing   (Index): 0
    Total Pages Processed (Other): 79
    Total Pages Processed (Seg)  : 0
    Total Pages Failing   (Seg)  : 0
    Total Pages Empty            : 17
    Total Pages Marked Corrupt   : 1
    Total Pages Influx           : 0
    Total Pages Encrypted        : 0
    Highest block SCN            : 0 (0.0)

Okumaya devam et

Convert Physical Standby to Snapshot Standby database

Selamlar,

physical standby database’i snapshot standby database’e çevirmek migration öncesi testlerde işimize yarayabilir. Örneğin data guard ile migration yapılmadan önce standby database snapshot moda alınıp uygulama testleri yapılabilir. Bu işlemi gerçekleştirmek için aşağıdaki adımları izleyebilirsiniz.

Convert Physical Standby to Snapshot Standby database

  1. [Primary] flashback için aşağıdaki parametreler değiştirilir ve primary database kapatılır.
    SQL> alter system set db_recovery_file_dest_size=5566M scope=both;
    SQL> alter system set db_recovery_file_dest='+FRA' scope=both;
    SQL> alter system set db_flashback_retention_target=60 scope=both;
    SQL> shutdown immediate;
  2. [Standby] Standby database’de de flashback için aşağıdaki parametreler değiştirilir ve database kapatılır.
    SQL> alter system set db_recovery_file_dest_size=5566M scope=both;
    SQL> alter system set db_recovery_file_dest='+FRA' scope=both;
    SQL> alter system set db_flashback_retention_target=60 scope=both;
  3. [Primary] flashback enable edilir.
    SQL> startup mount;
    SQL> alter database flashback on;
    SQL> alter database open;
  4. [Standby] flashback enable edilir.
    SQL> startup mount;
    SQL> alter database flashback on;
  5. Her iki database’de de flashback ve database role kontrol edilir.
    SQL> select name,database_role,flashback_on from v$database;
    NAME            DATABASE_ROLE       FLASHBACK_ON
    --------------- ------------------ ---------------
    RABBIT          PRIMARY             YES
    SQL> select name,database_role,flashback_on from v$database;
    NAME            DATABASE_ROLE       FLASHBACK_ON
    --------------- ------------------ ---------------
    RABBIT          PHYSICAL STANDBY    YES
  6. [Standby] standby database’e bağlanıp convert ediyorum ve kapatıp açıyorum.
    SQL> alter database convert to snapshot standby;
    SQL> shutdown immediate;
    SQL> startup
  7. [Standby] Standby database’e bağlanıp bir tabloyu boşaltıyorum ve işlem başarılı.
    SQL> delete from hr.employees;
    72 rows deleted.
    SQL> commit;
    Commit complete.

Okumaya devam et

Undo usage script

Selamlar,

aşağıdaki query ile o an undo tablespace’i kullanan user’ı, session di’sini, transaction’ı başlattığı zamanı ve son çalıştırdığı sql’i bulabilirsiniz.

Öncelikle test tablosu oluşturuyorum ve bir transaction başlatıyorum.

SQL> CREATE TABLE TEST_ERMAN AS SELECT * FROM DBA_USERS;
SQL> delete from test_erman;
36 rows deleted.

alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
select ses.sid, ses.serial#, ses.username, ses.schemaname, ses.osuser, ses.machine, ses.prev_sql_id, sq.sql_fulltext, ses.prev_exec_start, ses.logon_time, t.start_time
 from V$TRANSACTION t, v$LOCK l, v$session ses, v$sql sq
 where t.addr = l.addr
 and l.sid = ses.sid
 and ses.prev_sql_id = sq.sql_id;
Capture

Kolay gelsin.

Tablespace usage script

Selamlar,

aşağıdaki script ile tablespace kullanımı monitor edilebilir.

SELECT a1.tablespace_name TS_NAME,
       a1.size_mb SIZE_MB,
       a2.free_mb FREE_MB,
       a1.max_size_mb MAX_SIZE_MB,
       case when a1.max_size_mb = 0 then
             ROUND ( (a1.size_mb - a2.free_mb) * 100 / a1.size_mb)
            when a1.max_size_mb > 0 then
             ROUND ( (a1.size_mb - a2.free_mb) * 100 / a1.max_size_mb)
        end PCT_OF_TOTAL_USE
 FROM ( SELECT tablespace_name,
               ROUND (SUM (BYTES) / 1024 / 1024, 2) size_mb,
               ROUND(SUM(case when AUTOEXTENSIBLE = 'NO' then
                               ((BYTES) / 1024 / 1024)
                              when AUTOEXTENSIBLE = 'YES' then
                               ((maxbytes) / 1024 / 1024)
                          end)) max_size_mb
        FROM dba_data_files a
        GROUP BY tablespace_name) a1,
      ( SELECT tablespace_name, 
               ROUND (SUM (bytes) / 1024 / 1024) free_mb
        FROM dba_free_space
        GROUP BY tablespace_name) a2
WHERE a1.tablespace_name = a2.tablespace_name
 --AND ROUND ( (a1.size_mb - a2.free_mb) * 100 / a1.max_size_mb, 2) > 90
ORDER BY 5 DESC;

Kolay gelsin.