Total Pageviews

Sunday 30 November 2014

ORA-14074: partition bound must collate higher than that of the last partition



ALTER TABLE "HR"."EMPLOYEE_YEAR_PART" ADD PARTITION "HIST_DATA_2015"  VALUES LESS THAN (TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE EDW_STG_PART_2015;
--> 
ORA-14074: partition bound must collate higher than that of the last partition

Solution:

RANGE Partition with last partition as MAXVALUE and we want to add a partition before MAXVALUE. Solution is to split the MAXVALUE partiton into

MAXVALUE_PARTITION = NEW_PARTITION_YOU_WANT_TO_ADD &  MAXVALUE

Table Name: EMPLOYEE_YEAR_PART

select table_name,TABLESPACE_NAME,partition_name,high_value from dba_tab_partitions where table_name='EMPLOYEE_YEAR_PART'  order by partition_position;

TABLE_NAME                     TABLESPACE_NAME      PARTITION_NAME                 HIGH_VALUE
------------------------------ -------------------- ------------------------------ --------------------------------------------------------------------------------
EMPLOYEE_YEAR_PART  HR_PART_2013    HIST_DATA_2013                 TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

EMPLOYEE_YEAR_PART  HR_PART_2014    HIST_DATA_2014                 TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

EMPLOYEE_YEAR_PART  HR_PART_MAX     HIST_DATA_MAX                  MAXVALUE

alter table "HR"."EMPLOYEE_YEAR_PART" split partition HIST_DATA_MAX at (to_date('2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) into
( PARTITION HIST_DATA_2015 TABLESPACE HR_PART_2015 ,partition HIST_DATA_MAX TABLESPACE HR_PART_MAX) update global indexes;

After Split:
============

 select table_name,TABLESPACE_NAME,partition_name,high_value from dba_tab_partitions where table_name='EMPLOYEE_YEAR_PART'  order by partition_position;

TABLE_NAME                     TABLESPACE_NAME      PARTITION_NAME                 HIGH_VALUE
------------------------------ -------------------- ------------------------------ --------------------------------------------------------------------------------
EMPLOYEE_YEAR_PART  HR_PART_2013    HIST_DATA_2013                 TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
                                                                                   N')

EMPLOYEE_YEAR_PART  HR_PART_2014    HIST_DATA_2014                 TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
                                                                                   N')

EMPLOYEE_YEAR_PART  HR_PART_2015    HIST_DATA_2015                 TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
                                                                                   N')

EMPLOYEE_YEAR_PART  HR_PART_MAX     HIST_DATA_MAX                  MAXVALUE

Wednesday 26 November 2014

Increase Mount-Point Size Oracle VM


[root@node11g ~]# df -kh
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/vg_node1-lv_root
                       24G   22G   57M 100% /
tmpfs                1012M  272K 1011M   1% /dev/shm
/dev/sda1             485M   53M  408M  12% /boot
Softwares             200G   88G  113G  44% /media/sf_Softwares
/dev/sr0               55M   55M     0 100% /media/VBOXADDITIONS_4.2.6_82870


[root@node11g ~]# df -hP
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/vg_node1-lv_root   24G   22G   57M 100% /
tmpfs                1012M  272K 1011M   1% /dev/shm
/dev/sda1             485M   53M  408M  12% /boot
Softwares             200G   88G  113G  44% /media/sf_Softwares
/dev/sr0               55M   55M     0 100% /media/VBOXADDITIONS_4.2.6_82870


1. create a fixed size disk using oracle vm storage option.


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

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

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

[root@node11g ~]#  pvcreate /dev/sde1
  Physical volume "/dev/sde1" successfully created

Add the physical volume to the existing volume group using the vgextend command. use  df -h to find the name of the volume group.

[root@node11g ~]# df -hP
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/vg_node1-lv_root   24G   22G   57M 100% /
tmpfs                1012M  272K 1011M   1% /dev/shm
/dev/sda1             485M   53M  408M  12% /boot
Softwares             200G   88G  113G  44% /media/sf_Softwares
/dev/sr0               55M   55M     0 100% /media/VBOXADDITIONS_4.2.6_82870


[root@node11g ~]# vgextend vg_node1 /dev/sde1
  Volume group "vg_node1" successfully extended

Get the free space available on the physical volume type vgdisplay [volume group]  and use pvdisplay to check if everything is fine or not .

 [root@node11g ~]# vgdisplay vg_node1
  --- Volume group ---
  VG Name               vg_node1
  System ID          
  Format                lvm2
  Metadata Areas        2
  Metadata Sequence No  4
  VG Access             read/write
  VG Status             resizable
  MAX LV                0
  Cur LV                2
  Open LV               2
  Max PV                0
  Cur PV                2
  Act PV                2
  VG Size               42.04 GiB
  PE Size               4.00 MiB
  Total PE              10763
  Alloc PE / Size       7059 / 27.57 GiB
  Free  PE / Size       3704 / 14.47 GiB
  VG UUID               6hNRBO-pgiT-5EYj-jT3i-1jJs-Z5vw-OrOO7A
 
 [root@node11g ~]# pvdisplay
  --- Physical volume ---
  PV Name               /dev/sda2
  VG Name               vg_node1
  PV Size               27.58 GiB / not usable 3.00 MiB
  Allocatable           yes (but full)
  PE Size               4.00 MiB
  Total PE              7059
  Free PE               0
  Allocated PE          7059
  PV UUID               jQ2lO6-W7Qu-t6Ac-8f8Z-u7cn-o12I-ofBwIy
 
  --- Physical volume ---
  PV Name               /dev/sde1
  VG Name               vg_node1
  PV Size               14.47 GiB / not usable 1.74 MiB
  Allocatable           yes
  PE Size               4.00 MiB
  Total PE              3704
  Free PE               3704
  Allocated PE          0
  PV UUID               MuSKdx-Ebz3-DFO2-jrNV-356l-1xCl-JyBVhA
 
[root@node11g ~]#
Extend the logical volume by the amount of free space:

[root@node11g ~]# lvdisplay
  --- Logical volume ---
  LV Path                /dev/vg_node1/lv_root
  LV Name                lv_root
  VG Name                vg_node1
  LV UUID                0Uz7cN-4BS5-hvMI-8Myb-Gf0V-j8gd-AbhS9x
  LV Write Access        read/write
  LV Creation host, time localhost.localdomain, 2014-12-16 05:08:21 -0500
  LV Status              available
  # open                 1
  LV Size                23.70 GiB
  Current LE             6066
  Segments               2
  Allocation             inherit
  Read ahead sectors     auto
  - currently set to     256
  Block device           252:0
 
  --- Logical volume ---
  LV Path                /dev/vg_node1/lv_swap
  LV Name                lv_swap
  VG Name                vg_node1
  LV UUID                f2LAgl-3iwl-m3tW-lyDE-39wx-CHQ6-gc2Evk
  LV Write Access        read/write
  LV Creation host, time localhost.localdomain, 2014-12-16 05:08:27 -0500
  LV Status              available
  # open                 2
  LV Size                3.97 GiB
  Current LE             1016
  Segments               1
  Allocation             inherit
  Read ahead sectors     auto
  - currently set to     256
  Block device           252:1
 
[root@node11g ~]# pvscan
  PV /dev/sda2   VG vg_node1   lvm2 [27.57 GiB / 0    free]
  PV /dev/sde1   VG vg_node1   lvm2 [14.47 GiB / 14.47    free]


[root@node11g ~]# lvextend /dev/vg_node1/lv_root /dev/sde1
  Extending logical volume lv_root to 38.07 GiB
  Logical volume lv_root successfully resized

[root@node11g ~]# resize2fs /dev/vg_node1/lv_root
resize2fs 1.41.12 (17-May-2010)
Filesystem at /dev/vg_node1/lv_root is mounted on /; on-line resizing required
old desc_blocks = 2, new_desc_blocks = 3
Performing an on-line resize of /dev/vg_node1/lv_root to 9980928 (4k) blocks.
The filesystem on /dev/vg_node1/lv_root is now 9980928 blocks long.

[root@node11g ~]# df -kh
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/vg_node1-lv_root
                       38G   23G   15G  60% /
tmpfs                1012M  486M  526M  48% /dev/shm
/dev/sda1             485M   53M  408M  12% /boot
Softwares             200G   88G  113G  44% /media/sf_Softwares
/dev/sr0               55M   55M     0 100% /media/VBOXADDITIONS_4.2.6_82870

Monday 24 November 2014

Every thing about RAC and GRID Infra

RAC and Oracle Clusterware Best Practices and Starter Kit (Platform Independent) (Doc ID 810394.1) 

Export issue : UDE-00014: invalid value for parameter, 'attach'

Running expdp with sys as sysdba user:
============================

[oracle@RAC1 ~]$   /opt/oracle/product/11.2.0.3/db_1/bin/expdp \'/ as sysdba \'/ directory=expdp_22Nov14 dumpfile=expdp_22Nov14_LISTING_content_%U logfile=expdp_22Nov14_LISTING_content.log tables='EME.BMLS_LISTING' content=METADATA_ONLY exclude=constraint,ref_constraint,index,statisstatistics parallel=4

Export: Release 11.2.0.3.0 - Production on Mon Nov 24 01:45:54 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
UDE-00014: invalid value for parameter, 'attach'.

Correct Syntax:
==========


[oracle@RAC1 ~]$  /opt/oracle/product/11.2.0.3/db_1/bin/expdp  \'/ as sysdba\' directory=EXPDP_21NOV14 dumpfile=expdp_24Nov14_LISTING_content_%U content=METADATA_ONLY
 logfile=expdp_24Nov14_LISTING_content.log tables='EME.BMLS_LISTING' content=METADATA_ONLY exclude=constraint,ref_constraint,index,statisstatistics parallel=4


Export: Release 11.2.0.3.0 - Production on Mon Nov 24 01:56:51 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01":  "/******** AS SYSDBA" directory=EXPDP_21NOV14 dumpfile=expdp_24Nov14_LISTING_content_%U content=METADATA_ONLY logfile=expdp_24Nov14_LISTING_content.log tables='EME.BMLS_LISTING'  content=METADATA_ONLY exclude=constraint,ref_constraint,index,statisstatistics parallel=4
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /orabackup/expdp_21Nov14/expdp_24Nov14_LISTING_content_01.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 01:57:45


Also you can try:

expdp \"SYS@service AS SYSDBA\"


ORA-17503: ksfdopn:2 Failed to open file +DATA/ractp/spfileractp.ora


[oracle@RAC1 ~]$ srvctl start instance -i ractp1 -d ractp
PRCR-1079 : Failed to start resource ora.ractp.db
CRS-5017: The resource action "ora.ractp.db start" encountered the following error:
ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '+DATA/ractp/spfileractp.ora'
ORA-17503: ksfdopn:2 Failed to open file +DATA/ractp/spfileractp.ora
ORA-01034: ORACLE not available
ORA-27123: unable to attach to shared memory segment
Linux-x86_64 Error: 13: Permission denied
. For details refer to "(:CLSN00107:)" in "/u01/app/11.2.0.3/grid_11203/log/rac1/agent/crsd/oraagent_oracle/oraagent_oracle.log".
CRS-2674: Start of 'ora.ractp.db' on 'rac1' failed
CRS-2632: There are no more servers to try to place resource 'ora.ractp.db' on that would satisfy its placement policy

User: oracle

RAC 1  :  -rwsr-s--x. 1 oracle oinstall 222M Nov 21 01:41 oracle
RAC 2  :  -rwsr-s--x. 1 oracle oinstall 222M Nov 21 02:09 oracle

User Grid:

RAC  1 : -rwxrwxr-x. 1 grid oinstall 203972171 Nov 19 12:55 /u01/app/11.2.0.3/grid_11203/bin/oracle
RAC  2 : -rwsr-s--x. 1 grid oinstall 203972171 Nov 19 13:04 /u01/app/11.2.0.3/grid_11203/bin/oracle

Switch to root and change the permission on RAC1 to 6751 :

chmod 6751 /u01/app/11.2.0.3/grid_11203/bin/oracle

-rwsr-s--x. 1 grid oinstall 203972171 Nov 19 12:55 /u01/app/11.2.0.3/grid_11203/bin/oracle

Switch to oracle user:

[oracle@RAC1 ~]$ srvctl start instance -i ractp1 -d ractp

[oracle@RAC1 ~]$ ps -ef|grep pmon
grid      8860     1  0 22:05 ?        00:00:00 asm_pmon_+ASM1
oracle   10676     1  0 23:00 ?        00:00:00 ora_pmon_ractp1

[oracle@RAC1 ~]$ srvctl status database -d ractp
Instance ractp1 is running on node rac1
Instance ractp2 is running on node rac2




Saturday 22 November 2014

RAC listener Commands

[grid@RAC1 ~]$ ps -ef|grep LISTENER

grid      4008     1  0 00:36 ?        00:00:00 /u01/app/11.2.0.3/grid_11203/bin/tnslsnr LISTENER -inherit
grid      4179     1  0 00:39 ?        00:00:00 /u01/app/11.2.0.3/grid_11203/bin/tnslsnr LISTENER_SCAN1 -inherit


oracle Local listener:

lsnrcl status check run in grid home :  check the user who is running listerner from ps -ef output:

[grid@RAC1 ~]$ lsnrctl status LISTENER_SCAN1
[grid@RAC1 ~]$ lsnrctl status LISTENER

[grid@RAC1 admin]$ srvctl status scan
SCAN VIP scan1 is enabled
SCAN VIP scan1 is running on node rac1
SCAN VIP scan2 is enabled
SCAN VIP scan2 is running on node rac2
SCAN VIP scan3 is enabled
SCAN VIP scan3 is running on node rac2


[grid@RAC1 admin]$ srvctl status scan_listener
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is running on node rac1
SCAN Listener LISTENER_SCAN2 is enabled
SCAN listener LISTENER_SCAN2 is running on node rac2
SCAN Listener LISTENER_SCAN3 is enabled
SCAN listener LISTENER_SCAN3 is running on node rac2

[oracle@RAC1 ~]$ show parameter listener
------------------------------------------
local_listener     string (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.81)(PORT=1521))))
remote_listener     string RAC-SCAN.localdomain:1521

GRID_HOME:
cd $ORACLE_HOME/network/admin/
cd /u01/app/11.2.0.3/grid_11203/network/admin

cat $GRID_HOME/network/admin/listener.oracle
---------------------------------------------

LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent
LISTENER_SCAN3=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3)))) # line added by Agent
LISTENER_SCAN2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2)))) # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent

Listener Log File         /u01/app/grid/diag/tnslsnr/RAC1/listener/alert/log.xml


cat $GRID_HOME/network/admin/endpoints_listener.ora
----------------------------------------------------
LISTENER_RAC1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=RAC1-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.71)(PORT=1521)(IP=FIRST)))) # line added by Agent

Listener Log File: $GRID_HOME/log/diag/tnslsnr/RAC1/listener_scan1/alert/log.xml


[grid@RAC1 admin]$ srvctl config scan
SCAN name: RAC-SCAN.localdomain, Network: 1/192.168.56.0/255.255.255.0/eth0
SCAN VIP name: scan1, IP: /RAC-SCAN.localdomain/192.168.56.91
SCAN VIP name: scan2, IP: /RAC-SCAN.localdomain/192.168.56.92
SCAN VIP name: scan3, IP: /RAC-SCAN.localdomain/192.168.56.93

[grid@RAC1 admin]$ srvctl config scan_listener

SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1521
SCAN Listener LISTENER_SCAN2 exists. Port: TCP:1521
SCAN Listener LISTENER_SCAN3 exists. Port: TCP:1521

[grid@RAC1 admin]$ crsctl status resource -w 'TYPE = ora.scan_vip.type' -t
NAME           TARGET      STATE        SERVER                   STATE_DETAILS    
-------------------------------------------------------------------------------
ora.scan1.vip    1        ONLINE        ONLINE                    rac1                                      
ora.scan2.vip    1 `       ONLINE       ONLINE    rac2
ora.scan3.vip    1        ONLINE        ONLINE                    rac2


NODE RAC2:

[grid@RAC2 ~]$ ps -ef|grep LISTENER

[grid@RAC2 admin]$ crsctl status resource -w 'TYPE = ora.scan_vip.type' -t
NAME           TARGET      STATE        SERVER                   STATE_DETAILS    
-------------------------------------------------------------------------------
ora.scan1.vip    1        ONLINE        ONLINE                    rac1                                      
ora.scan2.vip    1 `       ONLINE       ONLINE    rac2
ora.scan3.vip    1        ONLINE        ONLINE                    rac2

[grid@RAC2 ~]$ ps -ef|grep LISTENER

grid      3948     1  0 00:35 ?        00:00:00 /u01/app/11.2.0.3/grid_11203/bin/tnslsnr LISTENER_SCAN3 -inherit
grid      3950     1  0 00:35 ?        00:00:00 /u01/app/11.2.0.3/grid_11203/bin/tnslsnr LISTENER_SCAN2 -inherit
grid      4016     1  0 00:35 ?        00:00:00 /u01/app/11.2.0.3/grid_11203/bin/tnslsnr LISTENER -inherit

Compare Scan on both nodes:
----------------------------
[grid@RAC1 admin]$ cluvfy comp scan  

Issue #3: Service not registered with SCAN listener after SCAN listener failover:

After SCAN VIP and SCAN listener failover, instance does not register with the SCAN listener. It might happen for only 1 of the scan listener. Client connection
gets intermittent ORA-12514 TNS:listener does not currently know of service requested in connect descriptor.

Cause:
1. Unpublished Bug 12659561 after scan listener failover, database instance might not register to the scan listener (refer Note 12659561.8), fixed in 11.2.0.3.2,
merge patch 13354057 for 11.2.0.2 available for certain platform.
2. Unpublished Bug 13066936 Instance does not register services when scan fails over (refer Note 13066936.8)

Solution:

1) For both above bugs, the workaround is to unregister and register remote listener on the database instance which does not
register to a SCAN listener with following steps.
show parameter remote_listener
alter system set remote_listener='';
alter system register;
alter system set remote_listener='<scan>:<port>';
alter system register;
2) Other points to check if service is not registered with SCAN listener:
a. remote_listener and local_listener is defined correctly
b. EZCONNECT is defined in sqlnet.ora, eg: NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
c. SCAN name is defined in /etc/hosts or DNS and whether there is any mismatch if it is defined in both places
d. nslookup <scan> should display SCAN VIP in round-robin fashion
e. do not set SECURE_REGITER_<listener> in listener.ora if the class of secure transports (COST) is not configured.



11G R2: Oracle Cluster SCAN Listeners: Use easy connect naming method
=========================================================

Just recently I came across another unclear error message while troubleshooting an Oracle Instance registration with SCAN Listener. This post is one of the posts which may save quite a bit of time for people who will hit the same issue.

The errors message I got configuring a database just moved to run under 11GR2 Oracle Cluster control is as following:

1
2
3
4
5
6
7
8
9
10
SQL> alter system set remote_listener='scan.usolygrid-prod.cinram.com:1521';
alter system set remote_listener='scan.your.domain.com:1521'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00119: invalid specification for system parameter REMOTE_LISTENER
ORA-00132: syntax error or unresolved network name
'scan.your.domain.com:1521'

SQL>
I was quite sure that the syntax I used was 100% correct as other instances running on the same host used the same remote_listener parameter value.

It appears that someone adjusted the following string in sqlnet.ora file on the server:

1
2
3
4
from
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
to
NAMES.DIRECTORY_PATH= (TNSNAMES)
The change makes sense to me as it always good to have strict control on where tns names are sourced from on the servers side.

However it appears that Oracle use an easy connect naming method to register Oracle Instances in SCAN Listeners by default. Therefore EZCONNECT keyword should be present in sqlnet.ora file. Strictly speaking you can use other tns names as the resolution method for the remote_listener parameter but by default it uses the easy connect naming method and you may face the error message shown above if you omit the EZCONNECT keyword.

Top 5 Issues That Cause Troubles with Scan VIP and Listeners (Doc ID 1373350.1)
--------------------------------------------------------------------------------

How to Find Sessions Generating Lots of Redo or Archive logs

To find sessions generating lots of redo, you can use either of the following
methods. Both methods examine the amount of undo generated. When a transaction
generates undo, it will automatically generate redo as well.

The methods are:
1) Query V$SESS_IO. This view contains the column BLOCK_CHANGES which indicates
   how much blocks have been changed by the session. High values indicate a
   session generating lots of redo.

   The query you can use is:
       SQL> SELECT s.sid, s.serial#, s.username, s.program,
           i.block_changes
           FROM v$session s, v$sess_io i
           WHERE s.sid = i.sid
           ORDER BY 5 desc, 1, 2, 3, 4;

   Run the query multiple times and examine the delta between each occurrence
   of BLOCK_CHANGES. Large deltas indicate high redo generation by the session.

2) Query V$TRANSACTION. This view contains information about the amount of
   undo blocks and undo records accessed by the transaction (as found in the
   USED_UBLK and USED_UREC columns).

  The query you can use is:
      SQL> SELECT s.sid, s.serial#, s.username, s.program,
          t.used_ublk, t.used_urec
          FROM v$session s, v$transaction t
          WHERE s.taddr = t.addr
          ORDER BY 5 desc, 6 desc, 1, 2, 3, 4;

   Run the query multiple times and examine the delta between each occurrence
   of USED_UBLK and USED_UREC. Large deltas indicate high redo generation by
   the session.

You use the first query when you need to check for programs generating lots of
redo when these programs activate more than one transaction. The latter query
can be used to find out which particular transactions are generating redo.

Saturday 15 November 2014

How to check if your DB is open using spfile or pfile

If your DB is open using pfile:
-----------------------------------------

SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type" 
   FROM sys.v_$parameter WHERE name = 'spfile';
--- PFILE

If your DB is open using SPFILE:
--------------------------------------------

SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type" 
   FROM sys.v_$parameter WHERE name = 'spfile';
--SPFILE