Database & Middleware Magic

Thursday, November 27, 2014

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

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