Category Archives: Oracle

Installing Enterprise Manager agent on Oracle Linux 6.7 SPARC

I have access to an EM13 Enterprise Manager server, and I am going to add my Oracle Linux 6.7 SPARC to this system for monitoring.

First – check that you have the latest plugins and agents installed for the platform.

Screenshot-Self Update: Agent Software - Oracle Enterprise Manager - Mozilla Firefox

Next, on the hosts, create a user to ‘own’ the agent software

[root@host-8-160 ~]# groupadd -g 10001 oinstall
[root@host-8-160 ~]# useradd -g oinstall -s /bin/bash -d /home/agent13 -m agent13
[root@host-8-160 ~]# passwd agent13
Changing password for user agent13.

Create a directory structure for the software

[root@host-8-160 ~]# mkdir -p /u01/app
[root@host-8-160 ~]# chgrp -R oinstall /u01
[root@host-8-160 ~]# chmod g+rwx /u01

Now, back in Enterprise Manager go.. Setup -> Add Target -> Add Target Manually -> Install Agent on Host.

Enter the fully qualified domain name of your host, and the correct Platform

Screenshot-Add Host Targets : Host and Platform - Mozilla Firefox

 

Enter the installation location

Enter the credentials for agent13 and root user and hit next.

Then you can hit deploy agent.

 

Post install configuration/Worries

 

The agent installed successfully – but the host target is not being marked as available.

Looking at the output of emctl status agent I have 2 concerns.

[agent13@host-8-160 bin]$ ./emctl status agent
Oracle Enterprise Manager Cloud Control 13c Release 2 
Copyright (c) 1996, 2016 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent Version : 13.2.0.0.0
OMS Version : 13.2.0.0.0
Protocol Version : 12.1.0.1.0
Agent Home : /u01/app/agent13/ngc13/agent_inst
Agent Log Directory : /u01/app/agent13/ngc13/agent_inst/sysman/log
Agent Binaries : /u01/app/agent13/ngc13/agent_13.2.0.0.0
Core JAR Location : /u01/app/agent13/ngc13/agent_13.2.0.0.0/jlib
Agent Process ID : 20636
Parent Process ID : 20497
Agent URL : https://host-8-160.blah.com:3876/emd/main/
Local Agent URL in NAT : https://host-8-160.blah.com:3876/emd/main/
Repository URL : https://ngc13c.blah.com:4901/empbs/upload
Started at : 2017-06-13 10:52:26
Started by user : agent13
Operating System : Linux version 4.1.12-94.3.4.el6uek.sparc64 (sparcv9)
Number of Targets : (none)
Last Reload : (none)
Last successful upload : (none)
Last attempted upload : (none)
Total Megabytes of XML files uploaded so far : 0
Number of XML files pending upload : 0
Size of XML files pending upload(MB) : 0
Available disk space on upload filesystem : 98.09%
Collection Status : Collections enabled
Heartbeat Status : Ok
Last attempted heartbeat to OMS : 2017-06-13 10:57:31
Last successful heartbeat to OMS : 2017-06-13 10:57:31
Next scheduled heartbeat to OMS : 2017-06-13 10:58:31

 

There are no targets, and there has not been a successful upload.

[agent13@host-8-160 bin]$ ./emctl pingOMS
Oracle Enterprise Manager Cloud Control 13c Release 2 
Copyright (c) 1996, 2016 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
EMD pingOMS completed successfully

[agent13@host-8-160 bin]$ ./emctl upload agent
Oracle Enterprise Manager Cloud Control 13c Release 2 
Copyright (c) 1996, 2016 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
EMD upload completed successfully

If I look in my targets.xml it is pretty empty

[agent13@host-8-160 ngc13]$ cat ./agent_inst/sysman/emd/targets.xml
<Targets AGENT_TOKEN="67DBE4C8ECBA03FA5DC991893B75619C55C9B1CEACAA6ED68074AB9C65CFF973"/>

[agent13@host-8-160 bin]$ ./emctl config agent listtargets
Oracle Enterprise Manager Cloud Control 13c Release 2 
Copyright (c) 1996, 2016 Oracle Corporation. All rights reserved.
[agent13@host-8-160 bin]$

On the enterprise manager server I had errors similar to this

 
Metric evaluation error start - Unable to connect to the agent at https://host-8-161.blah.com:3876/emd/main/ [No route to host]

Tried putting that URL into my browser… cannot connect to it.

Firewall! DOH! Of course!

Temporarily disabled the iptables firewall

[root@host-8-161 /]# service iptables stop
iptables: Setting chains to policy ACCEPT: filter [ OK ]
iptables: Flushing firewall rules: [ OK ]
iptables: Unloading modules: [ OK ]

Now I can connect to the agent address in my browser.

 

So.. the choice is disable the firewall, or alter the rules. As I’m in a lab, I’m going straight to disabling the firewall.

[root@host-8-161 /]# chkconfig iptables off

Now, try to get the agent to generate the internal target list (host, ORACLE_HOME)

[agent13@host-8-161 bin]$ ./emctl config agent listtargets
Oracle Enterprise Manager Cloud Control 13c Release 2 
Copyright (c) 1996, 2016 Oracle Corporation. All rights reserved.

[agent13@host-8-161 bin]$ ./emctl config agent addinternaltargets
Oracle Enterprise Manager Cloud Control 13c Release 2 
Copyright (c) 1996, 2016 Oracle Corporation. All rights reserved.
2017-06-13 12:00:37,234 [main] WARN oracle.sysman.gcagent.comm.agent.http.SSLInit - User requested cipher suite SSL_RSA_WITH_RC4_128_MD5, which is not supported for SSLContext TLSv1.2
2017-06-13 12:00:37,242 [main] WARN oracle.sysman.gcagent.comm.agent.http.SSLInit - User requested cipher suite SSL_RSA_WITH_RC4_128_SHA, which is not supported for SSLContext TLSv1.2

[agent13@host-8-161 bin]$ ./emctl config agent listtargets
Oracle Enterprise Manager Cloud Control 13c Release 2 
Copyright (c) 1996, 2016 Oracle Corporation. All rights reserved.

Now when I look at my targets.xml it has entries

[agent13@host-8-161 agent_inst]$ cat ./sysman/emd/targets.xml
<Targets AGENT_TOKEN="6A415CAF76EC952756AE3BC675B0080ADAEE066B3F9B10B1B4A6410870130843">
 <Target TYPE="host" NAME="host-8-161.blah.com" DISPLAY_NAME="host-8-161.osc.uk.oracle.com" EMD_URL="https://host-8-161.blah.com:3876/emd/main/" TIMEZONE_REGION="" IDENTIFIER="TARGET_GUID=51D4595ED5982DE8E0539011038AD7DB"/>
 <Target TYPE="oracle_emd" NAME="host-8-161.blah.com:3876" DISPLAY_NAME="host-8-161.blah.com:3876" EMD_URL="https://host-8-161.blah.com:3876/emd/main/" TIMEZONE_REGION="" IDENTIFIER="TARGET_GUID=0958C84AFB17CE4D3F9FB85C81250615"/>
 <Target TYPE="oracle_home" NAME="agent13c1_1_host-8-161.blah.com_1639" DISPLAY_NAME="agent13c1_1_host-8-161.blah.com_1639" EMD_URL="https://host-8-161.blah.com:3876/emd/main/" TIMEZONE_REGION="" IDENTIFIER="TARGET_GUID=C13E4BCE40F4509C3FC788A3C08EED68">
 <Property NAME="HOME_TYPE" VALUE="O"/>
 <Property NAME="INVENTORY" VALUE="/u01/app/oraInventory"/>
 <Property NAME="INSTALL_LOCATION" VALUE="/u01/app/agent13/ngc13/agent_13.2.0.0.0"/>
 </Target>
</Targets>

When I look at the hosts in Enterprise Manager they are now marked as up.

 

linux

Thoughts and other questions..

The agent13 user on the primary domain has automatically been given the permission to run read only ldm commands (similar to the privileges that need to be manually applied to the user on Solaris).

Unlike on other platforms (e.g. SuperCluster) the hierachy of LDOMs does not seem to be recorded.

Scribbled notes on installing the Oracle database on Oracle Linux 6.7 SPARC

I had a very short time to play with my Oracle Linux SPARC box before I handed it to my customers, so I only had a very quick attempt to install the Oracle RDBMS and start a database. I did only a very basic install using database storage on filesystem, and allowed the installer to create the DB. So these notes are even more scrappy than usual.

While not yet a certified platform, you can download the Oracle Database 12.1.0.2 for Oracle Linux 6.7 SPARC images on e-delivery. There is not a publicly available install document, so I’m going to follow the install guide for Linux

Preparing for the Install

https://docs.oracle.com/database/121/LADBI/olinrpm.htm#LADBI7477

I couldn’t find the pre-installation rpm for Linux-SPARC on ULN. So I am going to have to follow the documentation and hope I have all the packages.

Verify openssh is installed

[root@host-8-161 yum.repos.d]# rpm -qa |grep ssh
 openssh-clients-5.3p1-117.el6.sparc64
 openssh-5.3p1-117.el6.sparc64
 openssh-server-5.3p1-117.el6.sparc64
 libssh2-1.4.2-2.el6_7.1.sparc64

Check that the required packages are installed.

https://docs.oracle.com/database/121/LADBI/pre_install.htm#LADBI7534

I added the following packages..

  • compat-libcap1
  • compat-libstdc++-33

On the installation media the is an additional rpm to install cvuqdisk-1.0.9-1.rpm – but this requires an oracle user….

So.. lets create my user and groups for now

[root@host-8-161 rpm]# groupadd -g 1001 oinstall
 [root@host-8-161 rpm]# groupadd -g 1002 dba
 [root@host-8-161 rpm]# useradd -g dba -G oinstall -s /bin/bash -d /home/oracle/ oracle
 [root@host-8-161 rpm]# passwd oracle

Now retry installing the package

[root@host-8-161 rpm]# rpm -i cvuqdisk-1.0.9-1.rpm

Using default group oinstall to install package

Create directories

[root@host-8-161 rpm]# mkdir /u01
[root@host-8-161 rpm]# mkdir -p /u01/app/oracle
[root@host-8-161 rpm]# chown -R oracle:dba /u01

 

Set the Oracle user resource limits

[root@host-8-161 rpm]# cat /etc/security/limits.conf

# End of file
 oracle soft nofile 1024
 oracle hard nofile 65536
 oracle soft nproc 2047
 oracle hard nproc 16384
 oracle soft stack 10240
 oracle hard stack 10240
 oracle soft memlock 3145728
 oracle hard memlock 3145728

 

  • Set the display and try running runinstaller
  • .. fails with PRVF-0002 Unable to retrieve local node name
  • Added the hostname and IP to the local /etc/hosts and the install continued.
  • Pre-installation checks give warning about kernel parameters and swap size… but it does offer me a fixit script for the kernel parameters. Need to ensure that these changes to parameters are added to /etc/sysctl.conf
  • Ran the fixit, and still some parameters giving a warning – semms etc – I guess these would need a reboot. So the kernel parameters will need reviewing

Things I might want to consider adding to the /etc/sysctl.conf (stolen from another system)


kernel.msgmni = 2878
kernel.msgmax = 8192
kernel.msgmnb = 65536
kernel.shmmni = 4096
kernel.shmmax = 229916494233
kernel.shmall = 28065978

 

After the install completed, the database started ok.

 

 

 

 

Adding an Oracle Exadata Storage Server to Enterprise Manager using the command line

 

Ok, I’m just noodling around here… I have some ‘spare’ storage servers that are in the same fabric as my SuperCluster and I wanted to discover them in EM.

oracle@odc-em-sc7a:/u01/app/oracle/agent13/agent_inst/sysman/log$ emcli add_target -type=oracle_exadata -name=”expbcel09.osc.uk.oracle.com” -host=”sc7ach00pd00-d1.osc.uk.oracle.com” -properties=”CellName:expbcel09.osc.uk.oracle.com;MgmtIPAddr:138.3.3.82″
Target “expbcel09.osc.uk.oracle.com:oracle_exadata” added successfully

Changing attributes on ASM Diskgroups

You can see the attributes set on your ASM diskgroups in 2 ways, via the view  v$asm_attribute

SQL> select a.name, b.name, b.value from v$asm_attribute b, v$asm_diskgroup a where a.group_number=b.group_number and a.name='DATAX6C1' ;

Or via asmcmd and the lsattr

grid@sc7ach00pd00-d1:~$ asmcmd

ASMCMD> cd DATAX6C1
ASMCMD> lsattr -G DATAXC1 -l

Attributes you might want to pay attention to in an Exadata environment are

  • compatible.advm
  • cell.smart_scan_capable
  • appliance.mode
  • compatible.asm
  • compatible.rdbms

If you manually create a diskgroup via asmca these attributes will not normally be set, and so you may want to go manually set them.

SQL> ALTER DISKGROUP DATAX6C1 SET ATTRIBUTE 'appliance.mode'='TRUE';

The attribute is set immediately, but based on my experience, it does not come into effect until the disk group has  been rebalanced.

SQL> alter diskgroup DATAX6C1 rebalance power 2;

Redirecting Oracle Management agent to another OMS

I have an Enterprise Manager 13.1 system that is regularly reset to a ‘clean’ state using zfs snapshots. If you already have the management agents installed on other hosts you have the choice of

  1. De-installing the existing agent and installing a clean one
  2. Persuading the agent to talk to your ‘new’ server

A way of doing 2) is to follow the steps outlined in the MOS note EM 12c : Redirecting Oracle Management Agent to Another Oracle Management Service (Doc ID 1390717.1) which despite being for 12c appears to work for Enterprise Manager 13.

 

agent13@$ rm -rf agent_inst
agent13@$ mkdir agent_inst
agent13@$ /u01/app/agent13/agent_13.1.0.0.0/sysman/install/agentDeploy.sh \
AGENT_BASE_DIR=/u01/app/agent13 OMS_HOST=odc-em-ssc \
EM_UPLOAD_PORT=1159 AGENT_REGISTRATION_PASSWORD=welcome1 \
AGENT_INSTANCE_HOME=/u01/app/agent13/agent_inst \
AGENT_PORT=3872 -configOnly

 

This takes about 5 minutes to re-configure the agent to talk to the new server and then promote the targets. You will then be prompted to re-run the root.sh associated with the agent’s Oracle Home.

 

Allowing a non-privileged user to view LDOM configuration (updated for EM13.2)

In Solaris 11.2 (and probably other releases – I haven’t checked) if you try to view the ldom configuration as a non-privileged user you will get the following message

emagent @sscadb01:~$ ldm ls
Authorization failed

You can grant the user the ability to view the LDOM config  using the built in ‘LDoms Review’ profile

# usermod -P 'LDoms Power Mgmt Observability' emagent
# usermod -P 'LDoms Review' emagent

Login and out again and it should work.

emagent@sscadb01:~$ ldm ls
NAME STATE FLAGS CONS VCPU MEMORY UTIL NORM UPTIME
primary active -n-cv- UART 128 523776M 3.7% 3.7% 41d 6h 9m
ssccn1-app1 active -n---- 5001 128 512G 0.4% 0.2% 41d 6h 32m

Enterprise Manager 13 is LDOMs aware, and you will need to add this privilege to the agent software owner if you want the virtualization to be shown in this tool.

The Enterprise Manager 13.2 manual now lists slightly different privileges as required for the Enterprise Manager Systems Integration plugin. http://docs.oracle.com/cd/E73210_01/EMADM/GUID-62C91671-4F42-40A0-B929-22CBFEE73672.htm#EMADM15592

# usermod -P 'LDoms Power Mgmt Observability' emagent
# usermod -A solaris.ldoms.read,solaris.ldoms.ldmpower emagent

 

Removing a stubborn target it Enterprise Manager 12c R4

Very rarely you can encounter this problem in enterprise manager – a host or agent target that you cannot remove via the web interface. This is usually caused in my environment by decommissioning a host BEFORE the targets have all been dropped (Basically being a gungho dumbass and not considering things properly).

You can do a lot of operations by the emcli command line interface from your enterprise manager host

 

Login to Enterprise Manager

-bash-4.1$ ./emcli login -username=SYSMAN
Enter password : 

Login successful

Run the sync command to synchronize with the current OMS

-bash-4.1$ ./emcli sync
Synchronized successfully

Get the target that you want to remove – in my case it is a zone host that has been trashed.

-bash-4.1$ ./emcli get_targets |grep ssca-z7
4       Agent Unreachab  host                  ssca-z7-mgmt.osc.uk.oracle.com     
4       Agent Unreachab  oracle_emd            ssca-z7-mgmt.osc.uk.oracle.com:3872

I need to remove both the agent and the host

-bash-4.1$ ./emcli delete_target -name="ssca-z7-mgmt.osc.uk.oracle.com:3872" -type="oracle_emd" -delete_monitored_target

And then you can login to the GUI and check that it has gone!

Removing oracle’s shared memory segments

If there has been a problem on the system (unexplained crash/sysadmin a bit pkill happy) shared memory segments can be left behind.

-bash-4.1$ ipcs -a
IPC status from  as of Mon Aug  4 12:21:42 BST 2014
T         ID      KEY        MODE        OWNER    GROUP  CREATOR   CGROUP CBYTES  QNUM QBYTES LSPID LRPID   STIME    RTIME    CTIME
Message Queues:
T         ID      KEY        MODE        OWNER    GROUP  CREATOR   CGROUP NATTCH      SEGSZ   CPID  LPID   ATIME    DTIME    CTIME
Shared Memory:
m  251658260   0xc0e79a34 --rw-r-----   oracle oinstall   oracle oinstall      1      12288 22170 26178 12:21:25 12:21:25 11:19:24
m  251658259   0x0        --rw-r-----   oracle oinstall   oracle oinstall      1  796917760 22170 26178 12:21:25 11:23:54 11:19:24
m  251658258   0x0        --rw-r-----   oracle oinstall   oracle oinstall      1    8388608 22170 26178 12:21:25 11:23:54 11:19:24
T         ID      KEY        MODE        OWNER    GROUP  CREATOR   CGROUP NSEMS   OTIME    CTIME
Semaphores:
s         11   0x798a1154 --ra-r-----   oracle oinstall   oracle oinstall   304 11:23:54 11:19:24

You can delete these segments using the ipcrm command – but if you have a lot of running databases you may not want to risk selecting the wrong key values.

So, oracle has a utility sysresv that you can use. Set your ORACLE_SID so it knows which database you are working on..

First run it without any parameters to check the segments

-bash-4.1$ sysresv

IPC Resources for ORACLE_SID "oms" :
Shared Memory:
ID              KEY
251658258       0x00000000
251658259       0x00000000
251658260       0xc0e79a34
Semaphores:
ID              KEY
11              0x798a1154
Oracle Instance not alive for sid "oms"

Then run it with the -i flag to interactively remove segments

-bash-4.1$ sysresv -i

IPC Resources for ORACLE_SID "oms" :
Shared Memory:
ID              KEY
251658258       0x00000000
251658259       0x00000000
251658260       0xc0e79a34
Semaphores:
ID              KEY
11              0x798a1154
Oracle Instance not alive for sid "oms"
Remove ipc resources for sid "oms" (y/n)?y
Done removing ipc resources for sid "oms"

Creating an Infiniband listener on SuperCluster

There is a common IB network between all nodes in a SuperCluster. This is primarily used for access to the ZFS storage appliance, but it can also be used to provide IB access to the database.

I have a 2 node RAC cluster, and both nodes have a network adapter similar to this

# ifconfig stor_ipmp0 
stor_ipmp0: flags=108001000843 mtu 65520 index 2
        inet 192.168.28.2 netmask fffffc00 broadcast 192.168.31.255
        groupname stor_ipmp0

First create entries for the IB VIP in /etc/hosts on all nodes in the cluster and on the hosts who want to access the IB Listener (e.g. Exalogic, application nodes in the SuperCluster)

192.168.28.102  sc5a01-d1-ibvip sc5a01-d1-ibvip.osc.uk.oracle.com
192.168.28.103  sc5a02-d1-ibvip sc5a02-d1-ibvip.osc.uk.oracle.com

Create the required oracle network resources on one node of the cluster. If you’re uncertain about the subnet to specify you can use one of the online calculators such as http://www.subnet-calculator.com/

Define the network interface as a global public cluster interface as root

# /u01/app/11.2.0.3/grid/bin/oifcfg setif -global stor_ipmp0/192.168.28.0:public


# /u01/app/11.2.0.3/grid/bin/oifcfg getif                                       
bondeth0  138.3.16.0  global  public
bondib0  192.168.0.0  global  cluster_interconnect
bondib1  192.168.0.0  global  cluster_interconnect
bondib2  192.168.0.0  global  cluster_interconnect
bondib3  192.168.0.0  global  cluster_interconnect
stor_ipmp0  192.168.28.0  global  public

Create the new network resource in the grid infrastructure as root

# /u01/app/11.2.0.3/grid/bin/srvctl add network -k 2 -S 192.168.28.0/255.255.252.0/stor_ipmp0

# ./srvctl config network
Network exists: 1/138.3.16.0/255.255.254.0/bondeth0, type static
Network exists: 2/192.168.28.0/255.255.252.0/stor_ipmp0, type static

Create your VIP resources as root

# ./srvctl add vip -n sc5acn01-d1 -A sc5a01-d1-ibvip/255.255.252.0/stor_ipmp0 -k 2
# ./srvctl add vip -n sc5acn02-d1 -A sc5a02-d1-ibvip/255.255.252.0/stor_ipmp0 -k 2

Create IB Listener as the grid home owner

$ srvctl add listener -l LISTENER_IB -k 2 -p "TCP:1522,/SDP:1522" -s

Add entries to the grid home tnsnames.ora. There are slightly different configuartions per node, as we will explicitly name the ‘local’ and ‘remote’ nodes. If we had more than 2 nodes, then our entries for LISTENER_IBREMOTE and LISTENER_IPREMOTE would list all of the non-local nodes.

Node 1

## IB Listener configuration


DBM01_IB =
  (DESCRIPTION =
    (LOAD_BALANCE=on)
    (ADDRESS = (PROTOCOL = TCP)(HOST = sc5a01-d1-ibvip)(PORT = 1522))
    (ADDRESS = (PROTOCOL = TCP)(HOST = sc5a02-d1-ibvip)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dbm01)
    )
  )

LISTENER_IBREMOTE =
  (DESCRIPTION =
        (ADDRESS_LIST = 
            (ADDRESS = (PROTOCOL = TCP)(HOST = sc5a02-d1-ibvip)(PORT = 1522))
    )
  )  

LISTENER_IBLOCAL =
  (DESCRIPTION =
        (ADDRESS_LIST = 
            (ADDRESS = (PROTOCOL = TCP)(HOST = sc5a01-d1-ibvip)(PORT = 1522))
            (ADDRESS = (PROTOCOL = SDP)(HOST = sc5a01-d1-ibvip)(PORT = 1522))
    )
  )
LISTENER_IPLOCAL =
  (DESCRIPTION =
        (ADDRESS_LIST = 
            (ADDRESS = (PROTOCOL = TCP)(HOST = sc5a01-d1-vip)(PORT = 1521))
    )
  )
LISTENER_IPREMOTE =
  (DESCRIPTION =
        (ADDRESS_LIST = 
            (ADDRESS = (PROTOCOL = TCP)(HOST = sc5a02-d1-vip)(PORT = 1521))
    )
  )

Node 2


## IB Listener configuration


DBM01_IB =
  (DESCRIPTION =
    (LOAD_BALANCE=on)
    (ADDRESS = (PROTOCOL = TCP)(HOST = sc5a01-d1-ibvip)(PORT = 1522))
    (ADDRESS = (PROTOCOL = TCP)(HOST = sc5a02-d1-ibvip)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dbm01)
    )
  )

LISTENER_IBREMOTE =
  (DESCRIPTION =
        (ADDRESS_LIST = 
            (ADDRESS = (PROTOCOL = TCP)(HOST = sc5a01-d1-ibvip)(PORT = 1522))
    )
  )

LISTENER_IBLOCAL =
  (DESCRIPTION =
        (ADDRESS_LIST = 
            (ADDRESS = (PROTOCOL = TCP)(HOST = sc5a02-d1-ibvip)(PORT = 1522))
            (ADDRESS = (PROTOCOL = SDP)(HOST = sc5a02-d1-ibvip)(PORT = 1522))
    )
  )
LISTENER_IPLOCAL =
  (DESCRIPTION =
        (ADDRESS_LIST = 
            (ADDRESS = (PROTOCOL = TCP)(HOST = sc5a02-d1-vip)(PORT = 1521))
    )
  )
LISTENER_IPREMOTE =
  (DESCRIPTION =
        (ADDRESS_LIST = 
            (ADDRESS = (PROTOCOL = TCP)(HOST = sc5a01-d1-vip)(PORT = 1521))
    )
  )

Start your new listener as the grid home owner

grid@sc5acn01-d1:/u01/app/11.2.0.3/grid/network/admin$ srvctl start listener -l LISTENER_IB

Copy your new tnsnames.ora entries to your $ORACLE_HOME/network/admin/tnsnames.ora

Login as to your database as sysdba and set the listener_networks parameter

SQL> alter system set listener_networks='((NAME=network2)(LOCAL_LISTENER=LISTENER_IBLOCAL)(REMOTE_LISTENER=LISTENER_IBREMOTE))','((NAME=network1)(LOCAL_LISTENER=LISTENER_IPLOCAL)(REMOTE_LISTENER=LISTENER_IPREMOTE))' scope=both sid='*';

This will cause the database to register with the new infiniband listener.

Moving a datafile from filesystem to ASM

I had a database where someone had created a datafile outside of ASM and on the Oracle Home filesystem. Whilst this would not cause a problem for backups as they are done via RMAN and it knows where all the files are thanks to the control file, it will have performance implications as my ASM area is on Exadata cells, and the Oracle Home filesystem is on an internal disk.

The method I am going to describe assumes that you can take the tablespace offline to carry out the maintenance.

 

Find out the filename name of your mis-located datafile

SQL> select tablespace_name, file_name from dba_data_files where file_name not like '%DATA%';
TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
CMPIND_XX
/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/.ora

 

Alter the affected tablespace offline so no changes will be made to the datafile while we move it.


SQL> alter tablespace cmpind_xx offline;

 

Use RMAN to copy the file (in bold below).

oracle@mydbhost:~/mel$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Tue Apr 1 08:11:53 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: mydb (DBID=3455727309)
RMAN> copy datafile '/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/.ora' to '+DATA_EXPA';
Starting backup at 01-APR-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=856 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=916 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00172 name=/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/.ora
output file name=+DATA_EXPA/mydb/datafile/cmpind_xx.657.843725535 tag=TAG20140401T081214 RECID=511 STAMP=843725535
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 01-APR-14
Starting Control File and SPFILE Autobackup at 01-APR-14
piece handle=/stage/rman/12Mar14/c-3455727309-20140401-00 comment=NONE
Finished Control File and SPFILE Autobackup at 01-APR-14

Next step is to rename the datafile from the original name to the new OMF name that was generated during the RMAN copy (highlighted in red above) and bring the tablespace back online.

oracle@mydbhost:~/mel$ sqlplus '/ as sysdba'
SQL*Plus: Release 11.2.0.3.0 Production on Tue Apr 1 08:12:46 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> alter database rename file '/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/.ora' to '+DATA_EXPA/mydb/datafile/cmpind_xx.657.843725535';
Database altered.
SQL> alter tablespace cmpind_xx online;
Tablespace altered.