MERHABA

Uzun zamandır düşünüpte hayata geçiremediğim blog dünyasına bu yazı ile merhaba diyorum.  Tasarlarken, düşünürken faydalı,güzel yazılar yazarak, karşılaştığım durumları paylaşarak,  özellikle Türkçe kaynak arayanlara faydalı olmayı planlıyordum. umarım düşündüğüm şeyleri burada hayata geçirebilirim.

Faydalı,sürekli yeni bilgilerin paylaşıldığı bir blog olması dileğiyle,

Merhaba Dünya 🙂

Advertisements

Linux disk wait time

Linux disk latency

–when disk red-write latency problems occurs, we need to understand the problem side. It depends on os site, network side or storage side.
–in this blog I will show you how to check latency for an ASM disk, currently and historicaly.

–get name of the disk which we interested.

	SQL> column path format a40
	SQL> select GROUP_NUMBER, DISK_NUMBER, MOUNT_STATUS, HEADER_STATUS, STATE, PATH,label from v$asm_disk where group_number=9

	GROUP_NUMBER DISK_NUMBER MOUNT_S HEADER_STATU STATE    PATH					
	------------ ----------- ------- ------------ -------- ---------------------------------------- 
		   9	       0 CACHED  MEMBER       NORMAL   /dev/mapper/asmdata76

–for historical monitor, we need major-minur numbers.

[oracle@db_host sa]$ ll /dev/mapper/asmdata76
lrwxrwxrwx. 1 root root 8 Mar  2 12:01 /dev/mapper/asmdata76 -> ../dm-86

[oracle@toryum01 sa]$ ll /dev/dm-86
brw-rw----. 1 oracle dba 253, 86 Mar  2 12:02 /dev/dm-86

–major-minor number can be seen in ll /dev/dm-86 output. 253-86 is major-minor numbers for ASM disk refer to /dev/mapper/asmdata76

–now we can monitor the disk with sar comamnd’s historical logs.
–in /var/log/sa, logs createt daily for sar command.

[oracle@toryum01 sa]$  sar -d -f sa01 | egrep "DEV|253-86" | more
12:00:01 AM       DEV       tps  rd_sec/s  wr_sec/s  avgrq-sz  avgqu-sz     await     svctm     %util
12:10:02 AM dev253-86     17.83   4926.50     60.83    279.64      0.02      1.03      0.33      0.59
12:20:01 AM dev253-86      7.70    193.56     41.60     30.54      0.01      0.70      0.67      0.51
12:30:01 AM dev253-86      7.71    157.58     41.37     25.81      0.00      0.59      0.56      0.43
12:40:01 AM dev253-86     16.92   4935.31     40.52    294.11      0.02      1.10      0.35      0.59
12:50:01 AM dev253-86      7.46    190.12     39.54     30.77      0.00      0.50      0.49      0.37
01:00:01 AM dev253-86      7.19    143.56     39.72     25.50      0.00      0.41      0.40      0.29
01:10:01 AM dev253-86     18.10   4949.05     62.60    276.84      0.02      1.18      0.47      0.84
01:20:02 AM dev253-86      7.48    188.09     41.25     30.64      0.00      0.62      0.61      0.46
01:30:01 AM dev253-86      7.72    160.01     40.08     25.91      0.00      0.30      0.30      0.23


[oracle@toryum01 sa]$  sar -d -f sa02 | egrep "DEV|253-86" | more
12:00:01 AM       DEV       tps  rd_sec/s  wr_sec/s  avgrq-sz  avgqu-sz     await     svctm     %util
12:10:01 AM dev253-86     17.60   4903.26     58.58    281.87      0.02      1.22      0.43      0.76
12:20:02 AM dev253-86      7.50    189.63     38.61     30.45      0.00      0.39      0.37      0.28
12:30:01 AM dev253-86      7.80    160.11     41.46     25.85      0.00      0.51      0.49      0.38
12:40:01 AM dev253-86     16.85   4923.18     41.11    294.59      0.02      1.10      0.39      0.65
12:50:02 AM dev253-86      7.55    192.72     39.25     30.72      0.00      0.42      0.41      0.31
01:00:01 AM dev253-86      7.16    144.09     39.04     25.59      0.00      0.63      0.61      0.43
01:10:01 AM dev253-86     17.89   4957.48     62.35    280.54      0.02      1.06      0.35      0.63
01:20:01 AM dev253-86      7.45    191.03     38.16     30.75      0.00      0.59      0.51      0.38
01:30:01 AM dev253-86      7.59    157.50     39.93     26.01      0.00      0.59      0.59      0.44

–for real time monitoring

[oracle@db_host sa]$ iostat -x 2 dm-86
Linux 2.6.32-696.3.1.el6.x86_64 (toryum01) 	03/02/2018 	_x86_64_	(32 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           3.94    0.00    2.98    0.51    0.00   92.57

Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
dm-86             0.03     0.00    6.79    2.97  1276.77    45.23   135.45     0.01    0.75    0.89    0.42   0.39   0.38

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           6.00    0.00    2.68    0.14    0.00   91.17

Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
dm-86             0.00     0.00    5.50    4.00   176.00    40.50    22.79     0.00    0.16    0.09    0.25   0.11   0.10

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           5.67    0.00    2.02    0.06    0.00   92.25

Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
dm-86             0.00     0.00    4.00    1.50   128.00    32.50    29.18     0.00    0.18    0.12    0.33   0.18   0.10

–await is system+storage service time
–svctm is storage service time

Try to recover ASM disk

Because of some errors ASM diskgroup con not mounted. errors are in ASM alert.log:

WARNING: Disk Group DATA containing spfile for this instance is not mounted
ORA-15032: not all alterations performed
ORA-15040: diskgroup is incomplete
ORA-15042: ASM disk "20" is missing from group number "2"
ERROR: ALTER DISKGROUP ALL MOUNT /* asm agent call crs *//* {0:0:2} */
SQL> ALTER DISKGROUP ALL ENABLE VOLUME ALL /* asm agent *//* {0:0:2} */
SUCCESS: ALTER DISKGROUP ALL ENABLE VOLUME ALL /* asm agent *//* {0:0:2} */
NOTE: diskgroup resource ora.ARCH.dg is online
NOTE: diskgroup resource ora.REDO1.dg is online
NOTE: diskgroup resource ora.REDO2.dg is online

–diskgroup and db resources can not get online.

[grid@sigmafulltdb ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.ARCH.dg
               ONLINE  ONLINE       sigmafulltdb
ora.DATA.dg
               ONLINE  OFFLINE      sigmafulltdb
ora.LISTENER.lsnr
               ONLINE  ONLINE       sigmafulltdb
ora.REDO1.dg
               ONLINE  ONLINE       sigmafulltdb
ora.REDO2.dg
               ONLINE  ONLINE       sigmafulltdb
ora.asm
               ONLINE  ONLINE       sigmafulltdb             Started
ora.ons
               OFFLINE OFFLINE      sigmafulltdb
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        ONLINE  ONLINE       sigmafulltdb
ora.diskmon
      1        OFFLINE OFFLINE
ora.evmd
      1        ONLINE  ONLINE       sigmafulltdb
ora.test.db
      1        ONLINE  OFFLINE
ora.synotest.db
      1        ONLINE  OFFLINE
ora.maptest.db
      1        ONLINE  OFFLINE

it wouldn’t be possible to mount diskgroup either mount normal or mount force.

SQL> alter diskgroup data mount;
alter diskgroup data mount
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15040: diskgroup is incomplete

SQL> alter diskgroup data mount  force;
alter diskgroup data mount  force
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15040: diskgroup is incomplete
ORA-15042: ASM disk "20" is missing from group number "2"

Essentually, the disk 20 is member of ‘DATA’ diskgroup. but it seen as ‘candidate’

GROUP_NUMBER NAME                           DISK_NUMBER MOUNT_S HEADER_STATU STATE    NAME                           PATH
------------ ------------------------------ ----------- ------- ------------ -------- ------------------------------ ----------------------------------------
           0 DATA                                     0 CLOSED  MEMBER       NORMAL                                  /dev/sdaj
           0 DATA                                     1 CLOSED  MEMBER       NORMAL                                  /dev/sdao
           0 DATA                                    28 CLOSED  MEMBER       NORMAL                                  /dev/sdd
           0 DATA                                    27 CLOSED  MEMBER       NORMAL                                  /dev/sdf
           0 DATA                                    26 CLOSED  MEMBER       NORMAL                                  /dev/sde
           0 DATA                                    25 CLOSED  MEMBER       NORMAL                                  /dev/sdg
           0 DATA                                    24 CLOSED  CANDIDATE    NORMAL                                  /dev/sdp
           0 DATA                                    23 CLOSED  MEMBER       NORMAL                                  /dev/sdt
           0 DATA                                    22 CLOSED  MEMBER       NORMAL                                  /dev/sdu
           0 DATA                                    21 CLOSED  MEMBER       NORMAL                                  /dev/sdw
           0 DATA                                    20 CLOSED  MEMBER       NORMAL                                  /dev/sdv
           0 DATA                                    19 CLOSED  CANDIDATE    NORMAL                                  /dev/sdx
           0 DATA                                     2 CLOSED  MEMBER       NORMAL                                  /dev/sdad
           0 DATA                                     3 CLOSED  MEMBER       NORMAL                                  /dev/sdaq
           0 DATA                                     4 CLOSED  MEMBER       NORMAL                                  /dev/sdak
           0 DATA                                     5 CLOSED  MEMBER       NORMAL                                  /dev/sdap
           0 DATA                                     6 CLOSED  MEMBER       NORMAL                                  /dev/sdan
           0 DATA                                     7 CLOSED  MEMBER       NORMAL                                  /dev/sdab
           0 DATA                                     8 CLOSED  MEMBER       NORMAL                                  /dev/sdam
           0 DATA                                     9 CLOSED  MEMBER       NORMAL                                  /dev/sdal
           0 DATA                                    10 CLOSED  MEMBER       NORMAL                                  /dev/sdai
           0 DATA                                    11 CLOSED  MEMBER       NORMAL                                  /dev/sdaf
           0 DATA                                    12 CLOSED  MEMBER       NORMAL                                  /dev/sdag
           0 DATA                                    13 CLOSED  MEMBER       NORMAL                                  /dev/sdah
           0 DATA                                    14 CLOSED  MEMBER       NORMAL                                  /dev/sdae
           0 DATA                                    15 CLOSED  CANDIDATE    NORMAL                                  /dev/sdy
           0 DATA                                    16 CLOSED  MEMBER       NORMAL                                  /dev/sdac
           0 DATA                                    17 CLOSED  CANDIDATE    NORMAL                                  /dev/sdaa
           0 DATA                                    18 CLOSED  CANDIDATE    NORMAL                                  /dev/sdz

–disk header is destroyed as I look into disk header.

[grid@sigmafulltdb ~]$ kfed read /dev/sdx
kfbh.endian:                          0 ; 0x000: 0x00
kfbh.hard:                            0 ; 0x001: 0x00
kfbh.type:                            0 ; 0x002: KFBTYP_INVALID
kfbh.datfmt:                          0 ; 0x003: 0x00
kfbh.block.blk:                       0 ; 0x004: blk=0
kfbh.block.obj:                       0 ; 0x008: file=0
kfbh.check:                           0 ; 0x00c: 0x00000000
kfbh.fcn.base:                        0 ; 0x010: 0x00000000
kfbh.fcn.wrap:                        0 ; 0x014: 0x00000000
kfbh.spare1:                          0 ; 0x018: 0x00000000
kfbh.spare2:                          0 ; 0x01c: 0x00000000
7EFF2AB01400 00000000 00000000 00000000 00000000  [................]
  Repeat 255 times
KFED-00322: Invalid content encountered during block traversal: [kfbtTraverseBlock][Invalid OSM block type][][0]

"kfed repair" can not repair the disk.
[grid@sigmafulltdb ~]$ kfed repair /dev/sdx
KFED-00320: Invalid block num1 = [0], num2 = [1], error = [endian_kfbh]

The disks that have broken headers can be seen with kfod;

[grid@sigmafulltdb ~]$ kfod status=TRUE asm_diskstring='/dev/sd*' disk=all dscvgroup=TRUE
--------------------------------------------------------------------------------
 Disk          Size Header    Path                                     Disk Group   User     Group
================================================================================
   1:     204800 Mb CANDIDATE /dev/sdaa                                #            oracle   dba
   2:     204800 Mb MEMBER    /dev/sdab                                DATA         oracle   dba
   3:     204800 Mb MEMBER    /dev/sdac                                DATA         oracle   dba
   4:     204800 Mb MEMBER    /dev/sdad                                DATA         oracle   dba
   5:     204800 Mb MEMBER    /dev/sdae                                DATA         oracle   dba
   6:     204800 Mb MEMBER    /dev/sdaf                                DATA         oracle   dba
   7:     204800 Mb MEMBER    /dev/sdag                                DATA         oracle   dba
   8:     204800 Mb MEMBER    /dev/sdah                                DATA         oracle   dba
   9:     204800 Mb MEMBER    /dev/sdai                                DATA         oracle   dba
  10:     204800 Mb MEMBER    /dev/sdaj                                DATA         oracle   dba
  11:     204800 Mb MEMBER    /dev/sdak                                DATA         oracle   dba
  12:     204800 Mb MEMBER    /dev/sdal                                DATA         oracle   dba
  13:     204800 Mb MEMBER    /dev/sdam                                DATA         oracle   dba
  14:     204800 Mb MEMBER    /dev/sdan                                DATA         oracle   dba
  15:     204800 Mb MEMBER    /dev/sdao                                DATA         oracle   dba
  16:     204800 Mb MEMBER    /dev/sdap                                DATA         oracle   dba
  17:     204800 Mb MEMBER    /dev/sdaq                                DATA         oracle   dba
  18:     204800 Mb MEMBER    /dev/sdd                                 DATA         oracle   dba
  19:     204800 Mb MEMBER    /dev/sde                                 DATA         oracle   dba
  20:     204800 Mb MEMBER    /dev/sdf                                 DATA         oracle   dba
  21:     204800 Mb MEMBER    /dev/sdg                                 DATA         oracle   dba
  22:     204800 Mb MEMBER    /dev/sdh                                 ARCH         oracle   dba
  23:       4096 Mb MEMBER    /dev/sdi                                 REDO1        oracle   dba
  24:       4096 Mb MEMBER    /dev/sdj                                 REDO1        oracle   dba
  25:       4096 Mb MEMBER    /dev/sdk                                 REDO1        oracle   dba
  26:       4096 Mb MEMBER    /dev/sdl                                 REDO1        oracle   dba
  27:       4096 Mb MEMBER    /dev/sdm                                 REDO1        oracle   dba
  28:       4096 Mb MEMBER    /dev/sdn                                 REDO2        oracle   dba
  29:       4096 Mb MEMBER    /dev/sdo                                 REDO2        oracle   dba
  30:      65536 Mb CANDIDATE /dev/sdp                                 #            oracle   dba
  31:       4096 Mb MEMBER    /dev/sdq                                 REDO2        oracle   dba
  32:       4096 Mb MEMBER    /dev/sdr                                 REDO2        oracle   dba
  33:       4096 Mb MEMBER    /dev/sds                                 REDO2        oracle   dba
  34:     204800 Mb MEMBER    /dev/sdt                                 DATA         oracle   dba
  35:     204800 Mb MEMBER    /dev/sdu                                 DATA         oracle   dba
  36:     204800 Mb MEMBER    /dev/sdv                                 DATA         oracle   dba
  37:     204800 Mb MEMBER    /dev/sdw                                 DATA         oracle   dba
  38:     204800 Mb CANDIDATE /dev/sdx                                 #            oracle   dba
  39:     204800 Mb CANDIDATE /dev/sdy                                 #            oracle   dba
  40:     204800 Mb CANDIDATE /dev/sdz                                 #            oracle   dba
--------------------------------------------------------------------------------
ORACLE_SID ORACLE_HOME
================================================================================
      +ASM /grid/11.2.0/grid

we can unerstand the problem with reading blocks from a normal disk and a broken disk.

[grid@sigmafulltdb ~]$ dd if=/dev/sdx of=/tmp/sdx.txt bs=8192 count=3
3+0 records in
3+0 records out
24576 bytes (25 kB) copied, 0.000737703 s, 33.3 MB/s
[grid@sigmafulltdb ~]$ dd if=/dev/sdw of=/tmp/sdw.txt bs=8192 count=3
3+0 records in
3+0 records out
24576 bytes (25 kB) copied, 0.00126345 s, 19.5 MB/s
[grid@sigmafulltdb ~]$
[grid@sigmafulltdb ~]$ od -c /tmp/sdw.txt | head -10
0000000 001 202 001 001  \0  \0  \0  \0 030  \0  \0 200   @   f   I 256
0000020 234 033 001  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0
0000040   O   R   C   L   D   I   S   K  \0  \0  \0  \0  \0  \0  \0  \0
0000060  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0
0000100  \0  \0      \v 030  \0 001 003   D   A   T   A   _   0   0   2
0000120   4  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0
0000140  \0  \0  \0  \0  \0  \0  \0  \0   D   A   T   A  \0  \0  \0  \0
0000160  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0
0000200  \0  \0  \0  \0  \0  \0  \0  \0   D   A   T   A   _   0   0   2
0000220   4  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0
[grid@sigmafulltdb ~]$
[grid@sigmafulltdb ~]$
[grid@sigmafulltdb ~]$
[grid@sigmafulltdb ~]$ od -c /tmp/sdx.txt | head -10
0000000  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0
*
0060000

The header block do not have any data. There is 2 cases
1- /dev/sdx is really a new disk
2- the disk has been deleted, so disk can not be repaired. because header block deleted completely.

In our case head blocks destroyed completely, so disk couldn’t repaired.

create partiton,volume group, logical volume and mount point

RHEL 7 create new partition on one disk

[root@seyhgalip ~]# fdisk /dev/sdr
Welcome to fdisk (util-linux 2.23.2).

Changes will remain in memory only, until you decide to write them.
Be careful before using the write command.

Device does not contain a recognized partition table
Building a new DOS disklabel with disk identifier 0xc79b3c02.

Command (m for help): n
Partition type:
   p   primary (0 primary, 0 extended, 4 free)
   e   extended
Select (default p): p
Partition number (1-4, default 1): 1
First sector (2048-62914559, default 2048): 
Using default value 2048
Last sector, +sectors or +size{K,M,G} (2048-62914559, default 62914559): 
Using default value 62914559
Partition 1 of type Linux and of size 30 GiB is set

Command (m for help): p

Disk /dev/sdr: 32.2 GB, 32212254720 bytes, 62914560 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk label type: dos
Disk identifier: 0xc79b3c02

   Device Boot      Start         End      Blocks   Id  System
/dev/sdr1            2048    62914559    31456256   83  Linux

Command (m for help): t
Selected partition 1
Hex code (type L to list all codes): L

 0  Empty           24  NEC DOS         81  Minix / old Lin bf  Solaris        
 1  FAT12           27  Hidden NTFS Win 82  Linux swap / So c1  DRDOS/sec (FAT-
 2  XENIX root      39  Plan 9          83  Linux           c4  DRDOS/sec (FAT-
 3  XENIX usr       3c  PartitionMagic  84  OS/2 hidden C:  c6  DRDOS/sec (FAT-
 4  FAT16 <32M      40  Venix 80286     85  Linux extended  c7  Syrinx         
 5  Extended        41  PPC PReP Boot   86  NTFS volume set da  Non-FS data    
 6  FAT16           42  SFS             87  NTFS volume set db  CP/M / CTOS / .
 7  HPFS/NTFS/exFAT 4d  QNX4.x          88  Linux plaintext de  Dell Utility   
 8  AIX             4e  QNX4.x 2nd part 8e  Linux LVM       df  BootIt         
 9  AIX bootable    4f  QNX4.x 3rd part 93  Amoeba          e1  DOS access     
 a  OS/2 Boot Manag 50  OnTrack DM      94  Amoeba BBT      e3  DOS R/O        
 b  W95 FAT32       51  OnTrack DM6 Aux 9f  BSD/OS          e4  SpeedStor      
 c  W95 FAT32 (LBA) 52  CP/M            a0  IBM Thinkpad hi eb  BeOS fs        
 e  W95 FAT16 (LBA) 53  OnTrack DM6 Aux a5  FreeBSD         ee  GPT            
 f  W95 Ext'd (LBA) 54  OnTrackDM6      a6  OpenBSD         ef  EFI (FAT-12/16/
10  OPUS            55  EZ-Drive        a7  NeXTSTEP        f0  Linux/PA-RISC b
11  Hidden FAT12    56  Golden Bow      a8  Darwin UFS      f1  SpeedStor      
12  Compaq diagnost 5c  Priam Edisk     a9  NetBSD          f4  SpeedStor      
14  Hidden FAT16 ❤ 61  SpeedStor       ab  Darwin boot     f2  DOS secondary  
16  Hidden FAT16    63  GNU HURD or Sys af  HFS / HFS+      fb  VMware VMFS    
17  Hidden HPFS/NTF 64  Novell Netware  b7  BSDI fs         fc  VMware VMKCORE 
18  AST SmartSleep  65  Novell Netware  b8  BSDI swap       fd  Linux raid auto
1b  Hidden W95 FAT3 70  DiskSecure Mult bb  Boot Wizard hid fe  LANstep        
1c  Hidden W95 FAT3 75  PC/IX           be  Solaris boot    ff  BBT            
1e  Hidden W95 FAT1 80  Old Minix      
Hex code (type L to list all codes): 8e
Changed type of partition 'Linux' to 'Linux LVM'

Command (m for help): p

Disk /dev/sdr: 32.2 GB, 32212254720 bytes, 62914560 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk label type: dos
Disk identifier: 0xc79b3c02

   Device Boot      Start         End      Blocks   Id  System
/dev/sdr1            2048    62914559    31456256   8e  Linux LVM

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

Calling ioctl() to re-read partition table.
Syncing disks.

we created parititon from disk. now we can create parititon volumne

[root@seyhgalip ~]# pvcreate /dev/sdr1
  Physical volume "/dev/sdr1" successfully created.
[root@seyhgalip ~]# pvdisplay

  --- NEW Physical volume ---
  PV Name               /dev/sdr1
  VG Name               
  PV Size               <30.00 GiB
  Allocatable           NO
  PE Size               0   
  Total PE              0
  Free PE               0
  Allocated PE          0
  PV UUID               hXa5Bf-7uFI-3yMQ-67dB-oQOY-znXy-7E2qYa

next step is create volume group

[root@seyhgalip ~]# vgcreate vg_test /dev/sdr1
  Volume group "vg_test" successfully created

[root@seyhgalip ~]# vgdisplay 

  --- Volume group ---
  VG Name               vg_test
  System ID             
  Format                lvm2
  Metadata Areas        1
  Metadata Sequence No  1
  VG Access             read/write
  VG Status             resizable
  MAX LV                0
  Cur LV                0
  Open LV               0
  Max PV                0
  Cur PV                1
  Act PV                1
  VG Size               <30.00 GiB
  PE Size               4.00 MiB
  Total PE              7679
  Alloc PE / Size       0 / 0   
  Free  PE / Size       7679 / <30.00 GiB
  VG UUID               d7eF3q-JCsZ-T1PC-aceJ-6Uwl-eJe5-a41oqI

next step is create logical volume.

[root@seyhgalip ~]# lvcreate --name lv_test -l 100%FREE vg_test
  Logical volume "lv_test" created.
[root@seyhgalip ~]# lvdisplay
   
  --- Logical volume ---
  LV Path                /dev/vg_test/lv_test
  LV Name                lv_test
  VG Name                vg_test
  LV UUID                wvQy39-MxeZ-HFl1-UWcK-PEPf-MpPz-NykTs7
  LV Write Access        read/write
  LV Creation host, time seyhgalip, 2018-02-16 10:20:03 +0300
  LV Status              available
  # open                 0
  LV Size                <30.00 GiB
  Current LE             7679
  Segments               1
  Allocation             inherit
  Read ahead sectors     auto
  - currently set to     8192
  Block device           253:4

next step is formating logical volume for file system.

[root@seyhgalip ~]# mkfs.ext4 /dev/vg_test/lv_test 
mke2fs 1.42.9 (28-Dec-2013)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
Stride=0 blocks, Stripe width=0 blocks
1966080 inodes, 7863296 blocks
393164 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=2155872256
240 block groups
32768 blocks per group, 32768 fragments per group
8192 inodes per group
Superblock backups stored on blocks: 
	32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208, 
	4096000

Allocating group tables: done                            
Writing inode tables: done                            
Creating journal (32768 blocks): done
Writing superblocks and filesystem accounting information: done 

last step is create mount point and mount the LVM.

[root@seyhgalip ~]# mkdir -p /test
[root@seyhgalip ~]# 
[root@seyhgalip ~]# mount /dev/vg_
vg_default/  vg_oracle/   vg_postgres/ vg_test/     
[root@seyhgalip ~]# mount /dev/vg_test/lv_test /test
[root@seyhgalip ~]# 
[root@seyhgalip ~]# df -h
Filesystem                           Size  Used Avail Use% Mounted on
/dev/mapper/vg_default-root           52G  5.3G   47G  11% /
devtmpfs                              12G     0   12G   0% /dev
tmpfs                                 12G     0   12G   0% /dev/shm
tmpfs                                 12G   18M   12G   1% /run
tmpfs                                 12G     0   12G   0% /sys/fs/cgroup
/dev/sda1                            497M  199M  298M  41% /boot
tmpfs                                2.4G   12K  2.4G   1% /run/user/42
tmpfs                                2.4G     0  2.4G   0% /run/user/1001
tmpfs                                2.4G     0  2.4G   0% /run/user/0
/dev/mapper/vg_oracle-lv_oracle       59G   52M   57G   1% /oracle
/dev/mapper/vg_postgres-lv_postgres   30G   45M   28G   1% /postgres
/dev/mapper/vg_test-lv_test           30G   45M   28G   1% /test

in terms;
disk :/dev/sdr
parititon :/dev/sdr1
Physical volume: /dev/sdr1
volume group : vg_test
logical volume:lv_test
mount point:/test

list raw disks

list raw disks on RHEL 7

[root@seyhgalip ~]# fdisk -l

Disk /dev/sda: 64.4 GB, 64424509440 bytes, 125829120 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk label type: dos
Disk identifier: 0x0009b1ab

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *        2048     1026047      512000   83  Linux
/dev/sda2         1026048   125829119    62401536   8e  Linux LVM

Disk /dev/sdb: 10.7 GB, 10737418240 bytes, 20971520 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes


Disk /dev/sdc: 10.7 GB, 10737418240 bytes, 20971520 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes


Disk /dev/sdd: 10.7 GB, 10737418240 bytes, 20971520 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes


Disk /dev/sde: 10.7 GB, 10737418240 bytes, 20971520 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes


Disk /dev/sdf: 10.7 GB, 10737418240 bytes, 20971520 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes


Disk /dev/sdg: 32.2 GB, 32212254720 bytes, 62914560 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes


Disk /dev/sdh: 32.2 GB, 32212254720 bytes, 62914560 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk label type: dos
Disk identifier: 0x5f3cb448

   Device Boot      Start         End      Blocks   Id  System
/dev/sdh1            2048    62914559    31456256   8e  Linux LVM

Disk /dev/sdi: 32.2 GB, 32212254720 bytes, 62914560 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk label type: dos
Disk identifier: 0xa9b2f815

   Device Boot      Start         End      Blocks   Id  System
/dev/sdi1            2048    62914559    31456256   8e  Linux LVM

Disk /dev/sdj: 32.2 GB, 32212254720 bytes, 62914560 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes


Disk /dev/sdk: 32.2 GB, 32212254720 bytes, 62914560 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes


Disk /dev/sdm: 32.2 GB, 32212254720 bytes, 62914560 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes


Disk /dev/sdl: 32.2 GB, 32212254720 bytes, 62914560 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes


Disk /dev/sdn: 32.2 GB, 32212254720 bytes, 62914560 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes


Disk /dev/sdo: 32.2 GB, 32212254720 bytes, 62914560 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes


Disk /dev/sdp: 32.2 GB, 32212254720 bytes, 62914560 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes


Disk /dev/sdq: 32.2 GB, 32212254720 bytes, 62914560 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes


Disk /dev/sdt: 32.2 GB, 32212254720 bytes, 62914560 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes


Disk /dev/sdu: 32.2 GB, 32212254720 bytes, 62914560 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes


Disk /dev/sds: 32.2 GB, 32212254720 bytes, 62914560 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes


Disk /dev/sdr: 32.2 GB, 32212254720 bytes, 62914560 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes


Disk /dev/mapper/vg_default-swap: 8388 MB, 8388608000 bytes, 16384000 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes


Disk /dev/mapper/vg_default-root: 55.5 GB, 55507419136 bytes, 108412928 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes


Disk /dev/mapper/vg_oracle-lv_oracle: 64.4 GB, 64416120832 bytes, 125812736 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes


Disk /dev/mapper/vg_postgres-lv_postgres: 32.2 GB, 32208060416 bytes, 62906368 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes

Database migration with backup-restore

1-) on old db host

RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/backup/%U';
RMAN> backup database plus archivelog;
RMAN> backup current controlfile;
SQL> create pfile='/backup/pfile_YNPRTL.ora' from spfile;

2-)on old host
copy backup files and pfile to new host

scp /backup/e* 10.6.11.11:/backup
scp /backup/pfile_YNPRTL.ora 10.6.11.11:/backup

3-) on new host
change pfile parameters for new host (audit_file_dest, etc..)

SQL> startup nomount pfile='/oracle/product/11.2.0.4/db/dbs/pfile_YNPRTL.ora'
ORACLE instance started.

Total System Global Area 1603411968 bytes
Fixed Size		    2253664 bytes
Variable Size		 1040190624 bytes
Database Buffers	  536870912 bytes
Redo Buffers		   24096768 bytes

3-)on new host
restore controlfile

RMAN> connect target /;

connected to target database: YNPRTL (not mounted)

RMAN>  SET DBID 1677179977;

executing command: SET DBID

RMAN> RESTORE CONTROLFILE FROM "/backup/edsr9ta1_1_1"; 

Starting restore at 15-FEB-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2131 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
output file name=+YNPRTL/ynprtl/controlfile/current.256.968163303
Finished restore at 15-FEB-18

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1


RMAN> restore database;

Starting restore at 15-FEB-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2202 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +TTVPN/ynprtl/datafile/system.2490.783592203
channel ORA_DISK_1: restoring datafile 00002 to +TTVPN/ynprtl/datafile/sysaux.2495.783592205
channel ORA_DISK_1: restoring datafile 00003 to +TTVPN/ynprtl/datafile/undotbs1.2501.783592205
channel ORA_DISK_1: restoring datafile 00004 to +TTVPN/ynprtl/datafile/users.2514.783592205
channel ORA_DISK_1: restoring datafile 00005 to +TTVPN/ynprtl/datafile/data.2599.783946315
channel ORA_DISK_1: reading from backup piece /backup/test/easr9jac_1_1
channel ORA_DISK_1: piece handle=/backup/test/easr9jac_1_1 tag=TAG20180215T103908
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 15-FEB-18

RMAN> RECOVER DATABASE;

RMAN> ALTER DATABASE OPEN RESETLOGS;

12c agent silent install

1-) on em server host

$emcli	login -username=sysman -password
Login successful

2-)on em server host

$emcli sync

3-) on em server host

$emcli get_supported_platforms

3-)on em web page
Setup -> Extensibility ->Self Update ->check updates

–you can see supported agent links in downloaded file. the links are in core_Agent_core_image.xml file. download the agent for your client host proper for your OS.
https://updates.oracle.com/Orion/Download/download_patch/p9348486_112000_Generic.zip

4-)on em client host
create directory for agent software and a directory for agent home

$mkdir -p /oracle/product/agent12c
$mkdir -p /oracle/agent12c_software

and copy the downloaded agent patch into agent12c_software directory.

5-) on em client host
–unzip files
unzip p21207108_112000_Generic.zip
unzip 12.1.0.5.0_AgentCore_226.zip

–change agent.rsp for your configuration

#OMS_HOST=<OMS server hostname>
OMS_HOST=chicago
EM_UPLOAD_PORT=4903
AGENT_REGISTRATION_PASSWORD=<sysman password on OMS>
AGENT_INSTANCE_HOME=/oracle/product/agent12c
AGENT_PORT=3875
b_startAgent=true
#ORACLE_HOSTNAME=<client hostname>
ORACLE_HOSTNAME=newyork.mydomain

–update /etc/hosts files on em server and em client.

./agentDeploy.sh AGENT_BASE_DIR=/oracle/product/agent12c RESPONSE_FILE=/oracle/agent12c_software/archives/agent.rsp

6-)on em client host
–run root.sh on em client host

/u01/app/oracle/agent12g/core/12.1.0.1.0/root.sh

7-)on em client host

$emctl status agent
$emctl start agent

Generate rows

hello,

generate rows can be useful when we need data for creating test data. here is some examples for  generate   data.

 

drop table my_sc.test_index;

CREATE TABLE my_sc.test_index
(
id NUMBER primary key,
age NUMBER not null,
name VARCHAR2 (20),
surname VARCHAR2 (50),
adress VARCHAR (300),
op_date date not null
);

now insert some rows to table

insert into my_sc.test_index (id, age,name,surname,adress,op_date)
(
SELECT LEVEL id,
ROUND (DBMS_RANDOM.VALUE (18, 99)) age,
DBMS_RANDOM.string ('L', 20) name,
DBMS_RANDOM.string ('U', 30) surname,
dbms_random.string('A', 200) adress,
TRUNc(SYSDATE + DBMS_RANDOM.value(0,366)) AS op_date
FROM DUAL
CONNECT BY LEVEL  100
CONNECT BY LEVEL < 200
);

and then if we need to add some new rows

insert into my_sc.test_index (id, age,name,surname,adress,op_date)
(
       /* Formatted on 05/02/2018 15:15:42 (QP5 v5.277) */
    SELECT LEVEL id,
           ROUND (DBMS_RANDOM.VALUE (18, 99)) age,
           DBMS_RANDOM.string ('L', 20) name,
           DBMS_RANDOM.string ('U', 30) surname,
           dbms_random.string('A', 200)  adress,
           TRUNc(SYSDATE + DBMS_RANDOM.value(0,366)) AS op_date
      FROM DUAL
      where level > 100
CONNECT BY LEVEL < 200
);

ORA-17628: Oracle error 19505 returned by remote Oracle server

when creatin standby with rman duplicate


RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 11/02/2017 17:20:15
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on t1 channel at 11/02/2017 17:20:15
ORA-17628: Oracle error 19505 returned by remote Oracle serve

erros occured.

durinf duplicate if CONTROL_FILE parameter is empty this error can be trigger.  check for control file parameter on primary side.

 

other reason is convert parameters can be unset.  check on primary side:


SQL> show parameter convert

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string
log_file_name_convert string

SQL> alter system set db_file_name_convert='/ats','+ATSPREDATA' scope=spfile;

System altered.

SQL> alter system set log_file_name_convert='/ats','+ATSPREDATA' scope=spfile;

System altered

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 6463787008 bytes
Fixed Size 2233600 bytes
Variable Size 3657435904 bytes
Database Buffers 2785017856 bytes
Redo Buffers 19099648 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL> show parameter convert

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string /ats, +ATSPREDATA
log_file_name_convert string /ats, +ATSPREDATA

single physical standby install (non ASM, datafile on fileystem)

 

 

0-on primary side
Primary db force logging mode’da olmalı.

 SQL&amp;amp;amp;gt; select force_logging from v$database;
 FOR
 ---
 NO

SQL&amp;amp;amp;gt; alter database force logging;

Database altered.

SQL&amp;amp;amp;gt;

1-on stb side
Sadece oracle software kur.
2- on stb and primary side
Her iki sunucu arasında scp ile dosya kopyalanabildiğini, ssh yapılabildiğini, listener portlarının açık olduğunu test et.
3-on stb side
Db asm için profile dosyası oluştur.
Db profile örneği;


export ORACLE_BASE=/oracle/product
export ORACLE_HOSTNAME=serv01
export ORACLE_UNQNAME=ATSB_ST
export ORACLE_SID=ATSB_N
export ORACLE_HOME=/oracle/product/11.2.0/db
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32
export LIBPATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32
export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P9
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:/bin:/usr/bin:/etc:/opt/bin:/usr/ccs/bin:/usr/local/bin:$PATH; export PATH

&nbsp;

4-on primary side

Db ORACLE_HOME altındaki Tnsnames.ora dosyasına stb’nin tnsini ekle.


ATSB_ST_TNS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.6.41.2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ATSB_ST)
)
)

5-on stb side
Db ORACLE_HOME altındaki Tnsnames.ora dosyasına primary’ın tnsini ekle.

ATSBAUY_TNS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.6.155.6)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ATSBAUY)
)
)

6-on primary side
ORACLE_HOME/dbs altındaki orapwd dosyasını stb’de aynı path’e kopyala. Orapwd dosya isim formatı orapw[INSTABCE_NAME]


scp 10.6.155.6:/oracle/product/11.2.0/db/dbs/orapwATSBAUY /oracle/product/11.2.0/db/dbs
mv orapwATSBAUY orapwATSB_N

7-on stb side
$ORACLE_HOME/network/admin/listener.ora dosyası düzenlenir. netca ile oluşturulmamışsa aşağıdaki şekilde manule olarak listener oluşturlabilir.

 

listsner.ora için Örnek içerik;

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /oracle/product/11.2.0.3/db)
(SID_NAME = ATSB_N)
)
)

&nbsp;

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.6.41.2)(PORT = 1521))
)
)

listener’ı kontrol edelim.


{oramigtest:oracle}/oracle/product/11.2.0.3/db/network/admin/>lsnrctl start

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production on 25-OCT-2017 17:34:22

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

Starting /oracle/product/11.2.0.3/db/bin/tnslsnr: please wait...

TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
System parameter file is /oracle/product/11.2.0.3/db/network/admin/listener.ora
Log messages written to /oracle/product/diag/tnslsnr/oramigtest/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.6.41.2)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.6.41.2)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
Start Date 25-OCT-2017 17:34:22
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/product/11.2.0.3/db/network/admin/listener.ora
Listener Log File /oracle/product/diag/tnslsnr/oramigtest/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.6.41.2)(PORT=1521)))
Services Summary...
Service "ATSB_N" has 1 instance(s).
Instance "ATSB_N", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
{oramigtest:oracle}/oracle/product/11.2.0.3/db/network/admin/>lsnrctl status

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production on 25-OCT-2017 17:34:29

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.6.41.2)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
Start Date 25-OCT-2017 17:34:22
Uptime 0 days 0 hr. 0 min. 6 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/product/11.2.0.3/db/network/admin/listener.ora
Listener Log File /oracle/product/diag/tnslsnr/oramigtest/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.6.41.2)(PORT=1521)))
Services Summary...
Service "ATSB_N" has 1 instance(s).
Instance "ATSB_N", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
{oramigtest:oracle}/oracle/product/11.2.0.3/db/network/admin/>

 

8-on stb side
ORACLE_HOME/dbs altında pfile oluşturulur. pfile dosyası init[instance_name].ora ile oluşturulur.

 

db_name=ATSBAUY
db_unique_name=ATSB_ST
instance_name=ATSB_N

 

pfile içinde olması tavsiye edilen parametreler;
diagnostic_dest
audit_file_dest
core_dump_dest
db_create_file_dest
db_create_online_log_dest_1
db_create_online_log_dest_2
db_recovery_file_dest
db_recovery_file_dest_size
db_file_name_convert
log_file_name_convert
db_domain
log_archive_config
log_archive_dest_1
log_archive_dest_2
log_archive_dest_state_1
log_archive_dest_state_2
log_archive_format
fal_client
fal_server
sga_target
pga_aggregate_target
(memory_target)
standby_file_management
undo_tablespace

 

9-on primary side
Aşağıdaki parametrelerden olmayanları ekleyin, olanları stb için yeniden düzenleyin
LOG_ARCHIVE_CONFIG
STANDBY_FILE_MANAGEMENT


SQL> alter system set log_archive_config='DG_CONFIG=(ATSBAUY, ATSB_ST)';

System altered.

SQL> alter system set standby_file_management='AUTO';

System altered.

10-on primary side
Primary db de standby log file yoksa ekleyin. Standby log file size online redolog ile aynı olmalı. Standby log files group/sayısı online redolog sayısı/group’tan bir adet fazla olmalı.

SQL>select * from SYS.V_$STANDBY_LOG;
No rows selected.

SQL>select count(*) from v$log;
Count(*)
------
3

SQL> alter database add standby logfile size 50M;
Database altered.
SQL> alter database add standby logfile size 50M;
Database altered.
SQL> alter database add standby logfile size 50M;
Database altered.
SQL> alter database add standby logfile size 50M;
Database altered.

11-on stb side
Önceki adımlarda hazırlanan pfile ile db nomount modda açılır.

. /home/oracle/profile_new_standby

sqlplus / as sysdba;
startup nomount pifle=’/tmp/pifle.ora’

startup nomount pfile=’$ORACLE_HOME/dbs/pfileATSB_N.ora’

12-on stb side
Listener’ın ilgili db servisini unknown olarak dinlediği görülmelidir.


{oramigtest:oracle}/home/oracle/>lsnrctl stat listener

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production on 02-NOV-2017 15:00:39

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.6.41.2)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
Start Date 26-OCT-2017 14:11:48
Uptime 7 days 0 hr. 48 min. 51 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/product/11.2.0.3/db/network/admin/listener.ora
Listener Log File /oracle/product/diag/tnslsnr/oramigtest/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.6.41.2)(PORT=1521)))
Services Summary...
Service "ATSB_ST" has 2 instance(s).
Instance "ATSB_N", status UNKNOWN, has 1 handler(s) for this service...
Instance "ATSB_N", status BLOCKED, has 1 handler(s) for this service...

13-on stb side
Primary db’ye başarılı tnsping atıldığı görülmeli.
Sqlplus’dan sys ile primary’e bağlantı sağlanıp, primary db!den sorgu çekilmeli.
Sys ile bağlanırken sqplus sys/password@STB as sysdb şeklinde parolaile bağlanmak iyi olacakır.


chevron20:/home/oracle#tnsping ATSB_ST_TNS

TNS Ping Utility for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production on 25-OCT-2017 17:42:57

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

Used parameter files:

&nbsp;

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.6.41.2)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ATSB_ST)))
OK (0 msec)

14-on primary side
STB db’ye başarılı tnsping atıldığı görülmeli.
Sqlplus’dan sys ile stb’ye bağlantı sağlanıp, stb db’den sorgu çekilmeli.
Sys ile bağlanırken sqplus sys/password@STANDBY as sysdb şeklinde parolaile bağlanmak iyi olacakır.


chevron20:/home/oracle#tnsping ATSB_ST_TNS

TNS Ping Utility for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production on 02-NOV-2017 15:03:15

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

Used parameter files:

&nbsp;

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.6.41.2)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ATSB_ST)))
OK (0 msec)

&nbsp;

chevron20:/home/oracle#sqlplus sys/syspass@ATSB_ST_TNS as sysdba;

SQL*Plus: Release 11.2.0.3.0 Production on Thu Nov 2 15:04:23 2017

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

&nbsp;

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

SQL> select INSTANCE_NAME, HOST_NAME from v$instance

INSTANCE_NAME HOST_NAME
---------------- ----------------------------------------------------------------
ATSB_N oramigtest

15-on primary side
Rman duplicate scripti çalıştırılır.


more stb_rman_duplicate.rman
connect target /;
connect auxiliary sys/sysATSBAUYdb@ATSB_ST_TNS;
RUN
{
ALLOCATE AUXILIARY CHANNEL aux1 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL aux2 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL aux3 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL aux4 DEVICE TYPE DISK;
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
allocate channel t4 type disk;
duplicate target database for standby from active database
nofilenamecheck;
release channel aux1;
release channel aux2;
release channel aux3;
release channel aux4;
release channel t1;
release channel t2;
release channel t3;
release channel t4;
}

nohup rman cmdfile=stb_duplicate.rman log=stb_duplicate.log &

ORA-12528: TNS:listener: all appropriate instances are blocking new connections

ORA-12528: TNS:listener: all appropriate instances are blocking new connections

oracle version : 11.2.0.3
OS :AIX

db_name=ATSBAUY
db_unique_name=ATSB_ST
instance_name=ATSB_N
db_domain=”

tns entry in tnsnames.ora;

ATSB_ST_TNS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.100)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ATSB_ST)
)
)

I am creating standby db instance started with nomount mode.

there is just one service created automatically from db_unique_name

SQL> show parameter service

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string ATSB_ST

listener.ora:


SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /oracle/product/11.2.0.3/db)
(SID_NAME = ATSB_N)
)
)

&nbsp;

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.100)(PORT = 1521))
)
)
)

lets start listener


{arcl_host:oracle}/oracle/product/11.2.0.3/db/network/admin/>lsnrctl start

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production on 26-OCT-2017 13:55:13

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

Starting /oracle/product/11.2.0.3/db/bin/tnslsnr: please wait...

TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
System parameter file is /oracle/product/11.2.0.3/db/network/admin/listener.ora
Log messages written to /oracle/product/diag/tnslsnr/arcl_host/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.10.100)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.100)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
Start Date 26-OCT-2017 13:55:13
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/product/11.2.0.3/db/network/admin/listener.ora
Listener Log File /oracle/product/diag/tnslsnr/arcl_host/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.10.100)(PORT=1521)))
Services Summary...
Service "ATSB_N" has 1 instance(s).
Instance "ATSB_N", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

listener is not listening for ATSB_ST service despite listenig for instance_name (ATSB_N)

lest open instance (nomount database)


SQL> startup nomount
ORACLE instance started.

Total System Global Area 1.0689E+10 bytes
Fixed Size 2230912 bytes
Variable Size 5435819392 bytes
Database Buffers 5234491392 bytes
Redo Buffers 16932864 bytes
SQL>

now lets check for listener status


{arcl_host:oracle}/oracle/product/11.2.0.3/db/network/admin/>lsnrctl status

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production on 26-OCT-2017 13:59:36

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.100)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
Start Date 26-OCT-2017 13:55:13
Uptime 0 days 0 hr. 4 min. 22 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/product/11.2.0.3/db/network/admin/listener.ora
Listener Log File /oracle/product/diag/tnslsnr/arcl_host/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.10.100)(PORT=1521)))
Services Summary...
Service "ATSB_N" has 1 instance(s).
Instance "ATSB_N", status UNKNOWN, has 1 handler(s) for this service...
Service "ATSB_ST" has 1 instance(s).
Instance "ATSB_N", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully

the right service(ATSB_ST) is blocked. when tring to connect with TNS ORA-12528 error occured.


{arcl_host:oracle}/oracle/product/11.2.0.3/db/network/admin/>sqlplus /@ATSB_ST_TNS as sysdba;

SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 26 14:00:55 2017

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

ERROR:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections

&nbsp;

Enter user-name:

try connect via sqlplus after change tnsnames.ora as;

ATSB_ST_TNS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.100)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ATSB_N)
)
)


{arcl_host:oracle}/oracle/product/11.2.0.3/db/dbs/>sqlplus sys/syspass@ATSB_ST_TNS as sysdba;

SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 26 14:04:32 2017

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

&nbsp;

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

we can coonnect to db that listner service is unknown. if we add GLOBAL_DBNAME to listener.ora, listner will listen on ATSB_ST service.

in listner.ora:

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ATSB_ST)
(ORACLE_HOME = /oracle/product/11.2.0.3/db)
(SID_NAME = ATSB_N)
)
)

 

change tnsnames.ora for right service name:

ATSB_ST_TNS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.100)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ATSB_ST)
)
)

now stop and start listener:


{arcl_host:oracle}/oracle/product/11.2.0.3/db/network/admin/>lsnrctl stop

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production on 26-OCT-2017 14:11:39

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.100)(PORT=1521)))
The command completed successfully
{arcl_host:oracle}/oracle/product/11.2.0.3/db/network/admin/>
{arcl_host:oracle}/oracle/product/11.2.0.3/db/network/admin/>
{arcl_host:oracle}/oracle/product/11.2.0.3/db/network/admin/>lsnrctl start

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production on 26-OCT-2017 14:11:48

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

Starting /oracle/product/11.2.0.3/db/bin/tnslsnr: please wait...

TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
System parameter file is /oracle/product/11.2.0.3/db/network/admin/listener.ora
Log messages written to /oracle/product/diag/tnslsnr/arcl_host/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.10.100)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.100)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
Start Date 26-OCT-2017 14:11:48
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/product/11.2.0.3/db/network/admin/listener.ora
Listener Log File /oracle/product/diag/tnslsnr/arcl_host/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.10.100)(PORT=1521)))
Services Summary...
Service "ATSB_ST" has 1 instance(s).
Instance "ATSB_N", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

after a while the listner start to listen on ATSB_ST service;


{arcl_host:oracle}/oracle/product/11.2.0.3/db/network/admin/>lsnrctl status

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production on 26-OCT-2017 14:13:02

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.100)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
Start Date 26-OCT-2017 14:11:48
Uptime 0 days 0 hr. 1 min. 13 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/product/11.2.0.3/db/network/admin/listener.ora
Listener Log File /oracle/product/diag/tnslsnr/arcl_host/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.10.100)(PORT=1521)))
Services Summary...
Service "ATSB_ST" has 2 instance(s).
Instance "ATSB_N", status UNKNOWN, has 1 handler(s) for this service...
Instance "ATSB_N", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully

now we can connect with new TNS;

{arcl_host:oracle}/oracle/product/11.2.0.3/db/network/admin/>sqlplus sys/syspass@ATSB_ST_TNS as sysdba;

SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 26 14:14:30 2017

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

&nbsp;

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>