Thursday, November 27, 2014

Deinstalling Oracle Management Agent in Silent Mode Using AgentDeinstall.pl Script

Environment Details:
OS: Linux Server 5.8 64bit
OEM Agent Version: 12.1.0.4.0
Agent Base: /opt/oracle/product/12.1.0.1.AGT

Agent Home: /opt/oracle/product/12.1.0.1.AGT/core/12.1.0.4.0

1. Stop the agent
[oracle@linux01 ~]$ /apps/oracle/product/12.1.0.1.AGT/agent_inst/bin/emctl stop agent
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
Stopping agent ..... stopped.

2. Deinstall the agent:
Command: $<AGENT_HOME>/perl/bin/perl <AGENT_HOME>/sysman/install/AgentDeinstall.pl -agentHome <AGENT_HOME>

[oracle@linux01 ~]$ /apps/oracle/product/12.1.0.1.AGT/core/12.1.0.4.0/perl/bin/perl /apps/oracle/product/12.1.0.1.AGT/core/12.1.0.4.0/sysman/install/AgentDeinstall.pl -agentHome /apps/oracle/product/12.1.0.1.AGT/core/12.1.0.4.0

Agent Oracle Home: /apps/oracle/product/12.1.0.1.AGT/core/12.1.0.4.0

agentHome = /apps/oracle/product/12.1.0.1.AGT/core/12.1.0.4.0

NOTE: The agent base directory: /opt/oracle/product/12.1.0.1.AGT will be removed after successful deinstallation of agent home.

 DetachHome Command executed:/apps/oracle/product/12.1.0.1.AGT/core/12.1.0.4.0/oui/bin/runInstaller -detachHome -force -depHomesOnly -silent ORACLE_HOME=/apps/oracle/product/12.1.0.1.AGT/core/12.1.0.4.0 -waitForCompletion -invPtrLoc /apps/oracle/product/12.1.0.1.AGT/core/12.1.0.4.0/oraInst.loc
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 17190 MB    Passed
The inventory pointer is located at /apps/oracle/product/12.1.0.1.AGT/core/12.1.0.4.0/oraInst.loc
'DetachHome' was successful.
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 17190 MB    Passed
The inventory pointer is located at /apps/oracle/product/12.1.0.1.AGT/core/12.1.0.4.0/oraInst.loc
The Oracle home '/opt/oracle/product/12.1.0.1.AGT/sbin' could not be updated as it does not exist.

Deinstall Command executed:/apps/oracle/product/12.1.0.1.AGT/core/12.1.0.4.0/oui/bin/runInstaller -deinstall -silent "REMOVE_HOMES={/apps/oracle/product/12.1.0.1.AGT/core/12.1.0.4.0}" -waitForCompletion -removeAllFiles -invPtrLoc /apps/oracle/product/12.1.0.1.AGT/core/12.1.0.4.0/oraInst.loc
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 17190 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2014-11-26_11-56-07PM. Please wait ...Oracle Universal Installer, Version 11.1.0.12.0 Production
Copyright (C) 1999, 2014, Oracle. All rights reserved.

Starting deinstall

Deinstall in progress (Wednesday, November 26, 2014 11:56:18 PM CST)
Configuration assistant "Agent Deinstall Assistant" succeeded
............................................................... 100% Done.

Deinstall successful

End of install phases.(Wednesday, November 26, 2014 11:56:27 PM CST)
End of deinstallations
Please check '/opt/oracle/oraInventory/logs/silentInstall2014-11-26_11-56-07PM.log' for more details.

3. Remove the Agent Base directory
[oracle@linux01 ~]$ rm -rf /opt/oracle/product/12.1.0.1.AGT

4. Remove the targets associated to that agent & agent from OEM

How to relocate scan listener

Environment Details:
OS: Linux Server 5.8 64bit
GRID Version: 11.2.0.4.0
GRID Home: /opt/oracle/product/11.2.0.4.GRD
Node 1: linux01
Node 2: linux02

The scan listener is running from GRID Home, set this home.

Command: srvctl relocate scan_LISTENER -i <ordinal_number> -n <node_name>

Steps to relocate a scan listener

Verify where the scan listener is running
[oracle@linux01 ~]$ srvctl status scan_listener
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is running on node linux01
SCAN Listener LISTENER_SCAN2 is enabled
SCAN listener LISTENER_SCAN2 is running on node linux02
SCAN Listener LISTENER_SCAN3 is enabled
SCAN listener LISTENER_SCAN3 is running on node linux02

[oracle@linux01 ~]$ srvctl status scan
SCAN VIP scan1 is enabled
SCAN VIP scan1 is running on node linux01
SCAN VIP scan2 is enabled
SCAN VIP scan2 is running on node linux02
SCAN VIP scan3 is enabled
SCAN VIP scan3 is running on node linux02

[oracle@linux01 ~]$ ps -ef|grep -i scan
oracle   30985     1  0 Nov26 ?        00:00:08 /opt/oracle/product/11.2.0.4.GRD/bin/tnslsnr LISTENER_SCAN1 -inherit

[oracle@linux02 ~]$ ps -ef|grep -i scan
oracle    9954     1  0 Nov25 ?        00:00:07 /opt/oracle/product/11.2.0.4.GRD/bin/tnslsnr LISTENER_SCAN2 -inherit
oracle   11058     1  0 Nov25 ?        00:00:08 /opt/oracle/product/11.2.0.4.GRD/bin/tnslsnr LISTENER_SCAN3 -inherit

Relocating the scan listener to another node
[oracle@linux02 ~]$ srvctl relocate scan_LISTENER -i 2 -n linux01

[oracle@linux01 ~]$ ps -ef|grep -i scan
oracle   30985     1  0 Nov26 ?        00:00:08 /opt/oracle/product/11.2.0.4.GRD/bin/tnslsnr LISTENER_SCAN1 -inherit
oracle    9954     1  0 Nov27 ?        00:00:07 /opt/oracle/product/11.2.0.4.GRD/bin/tnslsnr LISTENER_SCAN2 -inherit

[oracle@linux02 ~]$ ps -ef|grep -i scan
oracle   11058     1  0 Nov25 ?        00:00:08 /opt/oracle/product/11.2.0.4.GRD/bin/tnslsnr LISTENER_SCAN3 -inherit

[oracle@linux01 ~]$ srvctl status scan
SCAN VIP scan1 is enabled
SCAN VIP scan1 is running on node linux01
SCAN VIP scan2 is enabled
SCAN VIP scan2 is running on node linux01
SCAN VIP scan3 is enabled
SCAN VIP scan3 is running on node linux02


Wednesday, November 26, 2014

How to send mail from oracle database | UTL_MAIL

Applicable for databases 10G onwards

Prerequisites: Get the smtp mail server details from the administrator.


Steps that needs to be followed for sending mails from oracle database.


Step 1: Install UTL_MAIL package by running the below files as SYS user

$ORACLE_HOME/rdbms/admin/utlmail.sql
$ORACLE_HOME/rdbms/admin/prvtmail.plb

Step 2: Grant permissions to PUBLIC or to the respective users

SQL> GRANT EXECUTE ON UTL_MAIL TO PUBLIC;

Step 3: Set SMTP_OUT_SERVER parameter in the pfile/spfile

SQL> ALTER SYSTEM SET smtp_out_server='smtp.domain.com' SCOPE=both; 

Step 4: Create a test procedure to send email

CREATE OR REPLACE PROCEDURE test_email AS
BEGIN
  UTL_MAIL.send(sender => 'testuser1@domain.com',
            recipients => 'testuser2@domain.com',
    cc => 'testuser3@domain.com',
    bcc => 'testuser4@domain.com',
            subject => 'Test Mail',
            message => 'Hello World',
            mime_type => 'text; charset=us-ascii');
END;
/

Step 5: Execute Create a test procedure to send email

SQL> exec test_email; 

Note: You need to create ACL for the users who can send mail except sys user.

Steps as mentioned below:

BEGIN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
    acl => 'utl_smtp.xml',
    description => 'Network Access Control for SYSTEM',
    principal => 'SYSTEM', -- here my user is system
    is_grant => TRUE,
    privilege => 'connect');
END;
/

begin
dbms_network_acl_admin.assign_acl (
acl => 'utl_smtp.xml',
host => 'smtp.domain.com',
lower_port => 25);
commit;
end;
/

begin
DBMS_NETWORK_ACL_ADMIN.add_privilege (
acl => 'utl_smtp.xml',
principal => 'SYSTEM', -- here my user is system
is_grant => TRUE,
privilege => 'connect');
COMMIT;
end;

/

SELECT host, lower_port, upper_port, privilege, status FROM   user_network_acl_privileges;


SELECT host, lower_port, upper_port, acl FROM dba_network_acls ;

Tuesday, November 25, 2014

Deinstalling Oracle Management Agent in Silent Mode

Environment Details:
OS: Linux Server 5.8 64bit
OEM Agent Version: 12.1.0.4.0
Agent Base: /opt/oracle/product/12.1.0.1.AGT
Agent Home: /opt/oracle/product/12.1.0.1.AGT/core/12.1.0.4.0

1. Stop the agent
[oracle@linux01 ~]$ cd /opt/oracle/product/12.1.0.1.AGT/agent_inst/bin
[oracle@linux01 bin]$ ./emctl stop agent
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
Stopping agent ..... stopped.

2. Deinstall the plug-in homes:

Command: $<AGENT_HOME>/oui/bin/runInstaller -deinstall ORACLE_HOME=<absolute_path_to_agent_home> [-removeallfiles] [-invPtrLoc <absolute_path_to_oraInst.loc>]

[oracle@linux01 bin]$ cd /opt/oracle/product/12.1.0.1.AGT/plugins
[oracle@linux01 plugins]$ls -lrt 
drwxr-xr-x  6 oracle oradba 4096 Oct  8 03:37 oracle.sysman.db.discovery.plugin_12.1.0.6.0
drwxr-xr-x  6 oracle oradba 4096 Oct  8 03:37 oracle.sysman.oh.discovery.plugin_12.1.0.4.0
drwxr-xr-x  8 oracle oradba 4096 Oct  8 03:37 oracle.sysman.emas.discovery.plugin_12.1.0.6.0
drwxr-xr-x 11 oracle oradba 4096 Oct  8 03:46 oracle.sysman.oh.agent.plugin_12.1.0.4.0
drwxr-xr-x 11 oracle oradba 4096 Oct  8 04:03 oracle.sysman.db.agent.plugin_12.1.0.6.0

[oracle@linux01 plugins]$ /opt/oracle/product/12.1.0.1.AGT/core/12.1.0.4.0/oui/bin/runInstaller -deinstall -silent "REMOVE_HOMES={/opt/oracle/product/12.1.0.1.AGT/plugins/oracle.sysman.db.discovery.plugin_12.1.0.6.0,/opt/oracle/product/12.1.0.1.AGT/plugins/oracle.sysman.oh.discovery.plugin_12.1.0.4.0,/opt/oracle/product/12.1.0.1.AGT/plugins/oracle.sysman.emas.discovery.plugin_12.1.0.6.0,/opt/oracle/product/12.1.0.1.AGT/plugins/oracle.sysman.oh.agent.plugin_12.1.0.4.0,/opt/oracle/product/12.1.0.1.AGT/plugins/oracle.sysman.db.agent.plugin_12.1.0.6.0}" ORACLE_HOME=/opt/oracle/product/12.1.0.1.AGT/core/12.1.0.4.0 -removeAllFiles -invPtrLoc /opt/oracle/oraInventory/oraInst.loc                                          
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 16234 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2014-11-25_12-20-35AM. Please wait ...[oracle@linux01 ~]$/opt/oracle/product/12.1.0.1.AGT/plugins: Oracle Universal Installer, Version 11.1.0.12.0 Production
Copyright (C) 1999, 2014, Oracle. All rights reserved.

Starting deinstall

Deinstall in progress (Tuesday, November 25, 2014 12:20:39 AM CST)
............................................................... 100% Done.

Deinstall successful

End of install phases.(Tuesday, November 25, 2014 12:20:40 AM CST)
End of deinstallations
Please check '/opt/oracle/oraInventory/logs/silentInstall2014-11-25_12-20-35AM.log' for more details.

3. Deinstall the sbin home:
Command: $<AGENT_HOME>/oui/bin/runInstaller -deinstall -silent "REMOVE_HOMES={absolute_path_to_sbin_directory}" ORACLE_HOME=<absolute_path_to_agent_home> [-removeAllFiles] [-invPtrLoc <absolute_path_to_oraInst.loc>]

[oracle@linux01 ~]$ /opt/oracle/product/12.1.0.1.AGT/core/12.1.0.4.0/oui/bin/runInstaller -deinstall -silent "REMOVE_HOMES={/opt/oracle/product/12.1.0.1.AGT/sbin}" ORACLE_HOME=/opt/oracle/product/12.1.0.1.AGT/core/12.1.0.4.0 -removeAllFiles -invPtrLoc /opt/oracle/oraInventory/oraInst.loc
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 16234 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2014-11-25_12-22-02AM. Please wait ...[oracle@linux01 ~]$/opt/oracle/product/12.1.0.1.AGT: Oracle Universal Installer, Version 11.1.0.12.0 Production
Copyright (C) 1999, 2014, Oracle. All rights reserved.

Starting deinstall


Deinstall in progress (Tuesday, November 25, 2014 12:22:05 AM CST)
............................................................... 100% Done.

Deinstall successful

End of install phases.(Tuesday, November 25, 2014 12:22:07 AM CST)
End of deinstallations
Please check '/opt/oracle/oraInventory/logs/silentInstall2014-11-25_12-22-02AM.log' for more details.

4. Deinstall the Management Agent:
Command: $<AGENT_HOME>/oui/bin/runInstaller -deinstall -silent "REMOVE_HOMES={absolute_path_to_agent_oracle_home}" ORACLE_HOME=<absolute_path_to_agent_home> -removeAllFiles -invPtrLoc <absolute_path_to_oraInst.loc>

[oracle@linux01 ~]$ /opt/oracle/product/12.1.0.1.AGT/core/12.1.0.4.0/oui/bin/runInstaller -deinstall -silent "REMOVE_HOMES={/opt/oracle/product/12.1.0.1.AGT/core/12.1.0.4.0}" ORACLE_HOME=/opt/oracle/product/12.1.0.1.AGT/core/12.1.0.4.0 -removeAllFiles -invPtrLoc /opt/oracle/oraInventory/oraInst.loc  
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 16234 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2014-11-25_12-23-29AM. Please wait ...[oracle@linux01 ~]$/opt/oracle/product/12.1.0.1.AGT: Oracle Universal Installer, Version 11.1.0.12.0 Production
Copyright (C) 1999, 2014, Oracle. All rights reserved.

Starting deinstall


Deinstall in progress (Tuesday, November 25, 2014 12:23:33 AM CST)
Configuration assistant "Agent Deinstall Assistant" succeeded
............................................................... 100% Done.

Deinstall successful

End of install phases.(Tuesday, November 25, 2014 12:23:40 AM CST)
End of deinstallations
Please check '/opt/oracle/oraInventory/logs/silentInstall2014-11-25_12-23-29AM.log' for more details.

5. Remove the Agent Base directory

[oracle@linux01 ~]$ rm -rf /opt/oracle/product/12.1.0.1.AGT
[oracle@linux01 ~]$

6. Remove the targets associated to that agent from OEM

Friday, September 19, 2014

How to restore spfile from RMAN backup


Following are the steps which needs to be followed to recover spfile.

1. Create a pfile with only parameter db_name in it.
2. Start the instance using this pfile
3. Connect to rman 
4. Set the dbid
5. Restore spfile from autobackup
6. Remove the pfile and start the instance using restored spfile


[oracle@linux01 ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Sep 19 07:20:28 2014

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

connected to target database: ORCL1 (not mounted)

RMAN> set dbid=1000680026

executing command: SET DBID

RMAN> restore spfile from autobackup;

Starting restore at 19-SEP-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1137 instance=ORCL1 device type=DISK

recovery area destination: +FRA_DG01
database name (or database unique name) used for search: ORCL1
channel ORA_DISK_1: AUTOBACKUP +fra_dg01/ORCL1/AUTOBACKUP/2014_09_19/s_858660592.8974.858660593 found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20140919
channel ORA_DISK_1: restoring spfile from AUTOBACKUP +fra_dg01/ORCL1/AUTOBACKUP/2014_09_19/s_858660592.8974.858660593

channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 19-SEP-14

RMAN> exit

ERROR: NMO not setuid-root (Unix-only)

When trying to perform some operations from OEM for the recently installed agents server I got the below mentioned error:

ERROR: NMO not setuid-root (Unix-only)

Cause: root.sh had not been executed from agent home

Solution: Execute the root.sh from agent home and restart the agent.

[root@lnx01 12.1.0.4.0]# pwd
/opt/oracle/product/12.1.0.1.AGT/core/12.1.0.4.0

[root@lnx01 12.1.0.4.0]# ls -lrt root.sh
-rwxr-xr-x 1 oracle oradba 3916 Jul 23 23:33 root.sh

[root@lnx01 12.1.0.4.0]# sh root.sh

Monday, July 7, 2014

Which ASM disk is mapped to which linux device


How to find which asm disk maps to which linux partition

[orcl@lnx01 ~]$ /etc/init.d/oracleasm querydisk -d `/etc/init.d/oracleasm listdisks -d`

Disk "ASM001" is a valid ASM disk on device /dev/dm-51[252,51]
Disk "ASM002" is a valid ASM disk on device /dev/dm-49[252,49]
Disk "ASM003" is a valid ASM disk on device /dev/dm-47[252,47]
Disk "ASM004" is a valid ASM disk on device /dev/dm-45[252,45]
Disk "ASM005" is a valid ASM disk on device /dev/dm-41[252,41]
Disk "ASM006" is a valid ASM disk on device /dev/dm-37[252,37]
Disk "ASM007" is a valid ASM disk on device /dev/dm-56[252,56]
Disk "ASM008" is a valid ASM disk on device /dev/dm-55[252,55]
Disk "ASM009" is a valid ASM disk on device /dev/dm-54[252,54]
Disk "ASM010" is a valid ASM disk on device /dev/dm-44[252,44]
Disk "ASM011" is a valid ASM disk on device /dev/dm-40[252,40]
Disk "ASM012" is a valid ASM disk on device /dev/dm-38[252,38]
Disk "ASM013" is a valid ASM disk on device /dev/dm-39[252,39]
Disk "ASM014" is a valid ASM disk on device /dev/dm-42[252,42]
Disk "ASM015" is a valid ASM disk on device /dev/dm-53[252,53]



Wednesday, May 14, 2014

Difference between crsctl start/stop crs and crsctl start/stop cluster


crsctl start/stop crs - This command starts/stops the OHASD process along with the complete Clusterware stack on the local node we are logged in.

crsctl start/stop cluster - This command starts/stops the Clusterware stack on a server except the OHASD process.

The following processes will still be running if we stop the cluster using crsctl stop cluster.

Process Owner Processes
------------- -------------------------------------------------
root     $GRID_HOME/bin/ohasd.bin reboot
root     $GRID_HOME/bin/orarootagent.bin
root   $GRID_HOME/bin/osysmond.bin
root    $GRID_HOME/bin/ologgerd -m linux02 -r -d $GRID_HOME/crf/db/linux01
oracle  $GRID_HOME/bin/gipcd.bin
oracle  $GRID_HOME/bin/oraagent.bin
oracle  $GRID_HOME/bin/mdnsd.bin
oracle   $GRID_HOME/bin/gpnpd.bin

Note: The advantage of crsctl stop cluster compared to stop crs command is that it prevent the relocation of 'certain' resources to other servers in the cluster before the Clusterware is stopped on that server.

Wednesday, May 7, 2014

How to find Cluster name on Oracle RAC

How to find Cluster name on Oracle RAC

Environment Details:
OS: Linux 5.8
User: oracle
Two Node RAC: linux01, linux02
Database Version: 11.2.0.4.0
Oracle Home: /u01/app/oracle/product/11.2.0/dbhome_1
Grid Home: /u01/app/11.2.0/grid

There are various method through which we can find out the cluster name. Few of them listed below

Set the GRID HOME & PATH Variable
[oracle@linux01 ~]export ORACLE_HOME=/u01/app/11.2.0/grid
[oracle@linux01 ~]$ export PATH=$ORACLE_HOME/bin:$PATH

Method 1: Execute the below command
[oracle@linux01 ~]$ cemutlo -n
racclu01

Method 2: Execute the below command
[oracle@linux01 ~]$ olsnodes -c
racclu01

Method 3: In Grid Home, there is a cdata directory, inside there will be a directory having the same name as cluster name
[oracle@linux01 ~]$ cd $ORACLE_HOME/cdata
[oracle@linux01 /u01/app/11.2.0/grid/cdata]$ ls
linux01  linux01.olr  localhost racclu01

Method 4: Do a ocrdump and open the dumpfile. Search for SYSTEM.css.clustername in the file.
[oracle@linux01 ~]$ ocrdump
[oracle@linux01 ~]$ vi OCRDUMPFILE

[SYSTEM.css.clustername]
ORATEXT : racclu01
SECURITY : {USER_PERMISSION : PROCR_ALL_ACCESS, GROUP_PERMISSION : PROCR_READ, OTHER_PERMISSION : PROCR_READ, USER_NAME : root, GROUP_NAME : root}


Monday, April 7, 2014

Password less ftp setup in linux

Environment Details:
Source Server Name: linux_src
Destination Server Name: linux_dest
Operating System: Red Hat Enterprise Linux Server 
Version: Release 6.4

When we connect to a server using ftp, we need to provide the username and password of the destination server.
We are going to setup a password less ftp connection authentication to the destination server

Please follow the below steps

Steps(Destination Server):
1. Verify whether vsftpd package is installed or not on the server, if not then install it.
[root@linux_dest bin]# rpm -qa|grep vsftpd
vsftpd-2.2.2-11.el6.x86_64

2. Take a backup of /etc/vsftpd/vsftpd.conf file and edit the value of anonymous_enable to YES
[root@linux_dest ~]# cp /etc/vsftpd/vsftpd.conf /etc/vsftpd/vsftpd.conf.bak
[root@linux_dest ~]# vi /etc/vsftpd/vsftpd.conf

3. Check whether ftp services are running or not, if not then start it
[root@linux_dest bin]# service vsftpd status
vsftpd is stopped
[root@linux_dest bin]# service vsftpd start
Starting vsftpd for vsftpd:                          [  OK  ]
[root@linux_dest bin]# service vsftpd status
vsftpd (pid 10652) is running...

Configuration on the destination server complete

Verify whether we are able to connect to the destination server using ftp or not(since we have not done any configuration on source server yet, it will ask for a username and password

[oracle@linux_src ~]$ ftp linux_dest
Connected to linux_dest (10.11.11.180).
220 (vsFTPd 2.2.2)
Name (linux_dest:testuser): oracle
331 Please specify the password.
Password:
230 Login successful.
Remote system type is UNIX.
Using binary mode to transfer files.

We are good, please follow the below steps on source server

Steps(Source Server from where we are planning to connect to the destination server):
1. In our home directory create a file .netrc and change its permission to 400
[oracle@linux_src ~]$ pwd
/home/oracle
[oracle@linux_src ~]$ touch .netrc
[oracle@linux_src ~]$ chmod 400 .netrc

2. Edit the file .netrc and put the value in the following format
machine <dest_server_name> login <username> password <password>
e.g. machine linux_dest login oracle password oracle

[oracle@linux_src ~]$ vi .netrc

3. cat .netrc
machine linux_dest login oracle password oracle

4. Now issue the ftp command from the source to connect to destination server

[oracle@linux_src ~]$ ftp linux_dest
Connected to linux_dest (10.11.11.180).
220 (vsFTPd 2.2.2)
331 Please specify the password.
230 Login successful.
Remote system type is UNIX.
Using binary mode to transfer files.

We are successfully connected to the destination server without supplying the username and password.

Saturday, March 29, 2014

Starting a Pluggable database in 12c

In Oracle 12c when we open a CDB (Container database), the PDBs(Pluggable Databases in the CDB) are by default in mount mode.

Solution: We either need to open the pluggable databases manually or we can create a trigger which can bring up the pluggable databases once the CDB is started.

If we want the PDBs to be opened manually issue the below command in the CDB:
alter pluggable database <pdb_name> open;

If we want the PDBs to be opened automatically, we can create a database event trigger that opens all the PDBs after STARTUP.

Create the below trigger in the CDB, which will bring up all the pluggable database when the CDB instance starts 

CREATE TRIGGER OPEN_ALL_PDBs
after startup on database
begin
execute immediate 'alter pluggable database all open';
end Open_ALL_PDBs;
/

Tuesday, February 18, 2014

How to move oracle datafile from Non ASM to ASM

How to move datafile from Non ASM (file system) to ASM

Solution: Verify the Name and location of datafile and in which tablespace the datafile resides

SQL> select tablespace_name "Tablespace",file_id "ID",file_name "File", bytes/1024/1024 "Size" from dba_data_files where TABLESPACE_NAME='TEST_TBS';

Tablespace ID File                                 Size
---------- --- -------------------------------------- ---------
TEST_TBS    8 /u01/oradata/orcl/test_tbs01.dbf         2016

Verify if you have sufficient space available on the ASM Disk Group

SQL> select name,total_mb,free_mb, state from v$asm_diskgroup;
NAME TOTAL_MB FREE_MB STATE
----- -------- ---------- ---------
DATA 101355 59017 MOUNTED


Take the tablespace offline

SQL> alter tablespace TEST_TBS offline;
Tablespace altered.


SQL> Select tablespace_name,status from dba_tablespaces where tablespace_name='TEST_TBS';

TABLESPACE_NAME STATUS
--------------- -------
TEST_TBS OFFLINE

Use the RMAN command to copy the datafile from the file system to asm disk
[oracle@linux01 ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Feb 18 06:15:45 2014

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

connected to target database: ORCL (DBID=2195477165)

RMAN> copy datafile 8 to '+DATA';

Starting backup at 18-FEB-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=212 instance=ORCL2 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00008 name=/u01/oradata/orcl/test_tbs01.dbf
output file name=+DATA/orcl/datafile/test_tbs01.259.123 tag=TAG20140218T061630 RECID=1 STAMP=839831287
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:11:45
Finished backup at 18-FEB-14

RMAN> switch datafile 8 to copy;

datafile 8 switched to datafile copy "+DATA/orcl/datafile/test_tbs01.259.123"


Bring back the tablespace online

[oracle@linux01 ~]$ sqlplus sys as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Feb 18 06:41:36 2014

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

Enter password: 

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

SQL> alter tablespace TEST_TBS online;
Tablespace altered.

Check the new location of the data file and the status

SQL> Select tablespace_name,status from dba_tablespaces where tablespace_name='TEST_TBS';

TABLESPACE_NAME STATUS
--------------- -------
TEST_TBS ONLINE

SQL> select tablespace_name "Tablespace",file_id "ID",file_name "File", bytes/1024/1024 "Size" from dba_data_files where TABLESPACE_NAME='TEST_TBS';

Tablespace ID File                                 Size
---------- --- -------------------------------------- ---------
TEST_TBS    8  +DATA/orcl/datafile/test_tbs01.259.123  2016


Remove the file from the OS file system:
rm /u01/oradata/orcl/test_tbs01.dbf

Friday, February 7, 2014

SP2-1503: Unable to initialize Oracle call interface

Issue: In one my environment where multiple databases of different versions(9i, 10gR2, 11gR1, 11gR2) are running simultaneously, after I have set the ORACLE_HOME & PATH variables I received the below error while logging into the server.

oracle@linux01=> export ORACLE_HOME=/u01/app/oracle/product/11.1.0.7.0
oracle@linux01=> export PATH=$ORACLE_HOME/bin:$PATH
oracle@linux01=> sqlplus sys as sysdba

SP2-1503: Unable to initialize Oracle call interface
SP2-0152: ORACLE may not be functioning properly

Solution: After carefully analyzing the environment variables(initial troubleshooting), I observed that the LD_LIBRARY_PATH variable was pointing to a different Oracle Home. Corrected that and was successfully able to login to the database.

Previous Value:
oracle@linux01=> echo $LD_LIBRARY_PATH
/u01/app/oracle/product/11.2.0.3.0/lib

Setting to correct value:
oracle@linux01=> export LD_LIBRARY_PATH=/u01/app/oracle/product/11.1.0.7.0/lib

After Modifying:
oracle@linux01=> echo $LD_LIBRARY_PATH
/u01/app/oracle/product/11.1.0.7.0/lib

Friday, January 24, 2014

Kill session on specific node of RAC

How to kill a user session on specific node of Oracle RAC


SQL> select INST_ID,  username, sid, serial#, osuser, status from gv$session where username in ('TEST');

   INST_ID USERNAME       SID    SERIAL# OSUSER          STATUS
---------- ----------- ------ ---------- --------------- --------
         1 TEST           597       1901 oraem           ACTIVE
         8 TEST           695       5391 oradba          ACTIVE

2 rows selected.

When we try to kill the session as we do in a single instance database it throws a error.

SQL> alter system kill session '695,5391' immediate;
alter system kill session '695,5391' immediate
*
ERROR at line 1:
ORA-00030: User session ID does not exist.


Solution: We need to provide the Instance ID along with the kill session command. i.e.

alter system kill session 'SID,SERIAL#,@<INST_ID>' immediate;

SQL> alter system kill session '695,5391,@8' immediate;

System altered.

Elapsed: 00:00:07.02
SQL> select INST_ID,  username, sid, serial#, osuser, status from gv$session where username in ('TEST');

   INST_ID USERNAME       SID    SERIAL# OSUSER          STATUS
---------- ----------- ------ ---------- --------------- --------
         1 TEST           597       1901 oraem           ACTIVE

1 row selected.

Thursday, January 23, 2014

Repository Creation Utility - Warning | ORA-01450: maximum key length (6398) exceed


Issue: While trying to create repository schema using rcu, we were getting the following error

Repository Creation Utility - Warning | ORA-01450: maximum key length (6398) exceed


Solution: The NLS_LENGTH_SEMANTICS parameter was set to a value of CHAR. Change the value to BYTE and again invoke the rcu, it will go through.

SQL> show parameter NLS_LENGTH_SEMANTICS

NAME                     TYPE        VALUE
------------------------ ----------- -------
nls_length_semantics     string      CHAR

SQL> alter system set nls_length_semantics=BYTE;

System altered.

SQL> show parameter NLS_LENGTH_SEMANTICS

NAME                     TYPE        VALUE
------------------------ ----------- -------
nls_length_semantics     string      BYTE


Note: Though this parameter is system modifiable, in one of the scenario, after setting this parameter still it was not working, when I bounced the database.. it worked.

Wednesday, January 15, 2014

mount: mount to NFS server failed: RPC Error: Program not registered.


Issue:
[root@linux01 ~]# mount -t nfs linux02:/u01/app /u01/app
mount: mount to NFS server 'linux02' failed: RPC Error: Program not registered.

[root@linux02 ~]# showmount
mount clntudp_create: RPC: Program not registered

Solution:
the nfs service was down on the source server so started the same and able to mount the drive successfully in the destination server.

[root@linux02 ~]# service nfs start
Starting NFS services:                           [  OK  ]
Starting NFS daemon:                             [  OK  ]
Starting NFS mountd:                             [  OK  ]

[root@linux01 ~]# mount -t nfs linux02:/u01/app /u01/app

Friday, January 10, 2014

Oracle 12c Express EM Configuration

Oracle 12c doesn't have the same EM which was used to be till 11g. The 12c comes with express EM.

How to configure a express EM in 12c.
Login to the database and execute the below command to verify whether Express EM is configured or not.
select dbms_xdb_config.gethttpsport () from dual;

If the above statement returns any row(that will be the port number), that means the Express EM is configured. Use that port number to connect to EM(use https) as
https://<hostname>:<port_number>/em

Else on the server first verify which port is free using the below mentioned command. If for a port number no output is returned that may mean its free and can be used(please verify that other applications are not using that port)
netstat -an|grep <port number> (We can use any port e.g. 1158)

Then connect to the database as sys user and execute the below command;
exec dbms_xdb_config.sethttpsport (1158);

select dbms_xdb_config.gethttpsport () from dual;

You can use the above port to connect to the Express EM.

The following points you must remember while configuring the Express EM
1. It uses https 
2. The parameter DISPATCHERS must be set.

SQL> show parameter dispatchers

NAME               TYPE        VALUE
------------------ ----------- ------------------------------
dispatchers        string      (PROTOCOL=TCP)(SERVICE=orclXDB)

If its not set, we need to set it 
e.g. for my SID orcl I have set using the below command
alter system set dispatchers='(PROTOCOL=TCP)(SERVICE=orclXDB)' scope=both;