Database & Middleware Magic

Wednesday, January 7, 2015

How to move system datafile from one ASM diskgroup to another

Follow the below steps to move system datafile from one ASM diskgroup to another.

1. Check the file which you need to move.

2. Stop the database and start it in mount mode.

3. Connect to rman

4. issue the copy command from the rman prompt

5. login to the sql prompt and issue rename file command

6. stop and start the database

Note: Be very cautious while executing the rename file command as it will remove the original file from the ASM.(tested in 11.2.0.4)

[oracle@linux01 ~]$ sqlplus sys as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Jan 2 03:09:48 2015

Enter password:

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> select file_name from dba_data_files;

FILE_NAME

--------------------------------------------------------------------------------

+FRA_DG01/orcl/datafile/system.144021.867898701

[oracle@linux01 ~]$ srvctl stop database -d ORCL

[oracle@linux01 ~]$ srvctl start database -d ORCL -o mount

[oracle@linux01 ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Jan 2 03:05:00 2015

connected to target database: ORCL (DBID=408657265, not open)

RMAN> copy datafile '+FRA_DG01/orcl/datafile/system.144021.867898701' to '+DATA_DG01';

Starting backup at 02-JAN-15

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=81 instance=ORCL1 device type=DISK

channel ORA_DISK_1: starting datafile copy

input datafile file number=00001 name=+FRA_DG01/orcl/datafile/system.144021.867898701

output file name=+DATA_DG01/orcl/datafile/system.307.867899135 tag=TAG20150102T030534 RECID=2 STAMP=867899143

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15

Finished backup at 02-JAN-15

RMAN> exit

Recovery Manager complete.

[oracle@linux01 ~]$sqlplus sys as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Jan 2 03:06:19 2015

Enter password:

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 '+FRA_DG01/orcl/datafile/system.144021.867898701' to '+DATA_DG01/orcl/datafile/system.307.867899135';

Database altered.

SQL> exit

[oracle@linux01 ~]$ srvctl stop database -d ORCL

[oracle@linux01 ~]$ srvctl start database -d ORCL

[oracle@linux01 ~]$ sqlplus sys as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Jan 2 03:09:48 2015

Enter password:

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> select file_name from dba_data_files;

FILE_NAME

--------------------------------------------------------------------------------

+DATA_DG01/orcl/datafile/system.307.867899135

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