Database & Middleware Magic

Wednesday, August 19, 2015

Discover host targets manually in OEM 12c - emctl config agent addinternaltargets

In case of silent installation of OEM 12c Agent sometimes the host target monitoring is not picked up and it is not visible in OEM console as well.

When checked in the /u01/app/oracle/agent_inst/sysman/emd/targets.xml file we couldn't find information related to the host.

To fix this we need to run "emctl config agent addInternalTargets". This forces the agent to rediscover the host.

linux01(oracle:NONE)/u01/app/oracle/agent_inst/bin: ./emctl status agent
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent Version : 12.1.0.4.0
OMS Version : 12.1.0.4.0
Protocol Version : 12.1.0.1.0
Agent Home : /u01/app/oracle/agent_inst
Agent Log Directory : /u01/app/oracle/agent_inst/sysman/log
Agent Binaries : /u01/app/oracle/core/12.1.0.4.0
Agent Process ID : 3770
Parent Process ID : 3494
Agent URL : https://linux01:1830/emd/main/
Local Agent URL in NAT : https://linux01:1830/emd/main/
Repository URL : https://oem12c.test.com:1159/empbs/upload
Started at : 2015-08-18 13:46:01
Started by user : oracle
Operating System : Linux version 2.6.32-573.1.1.el6.x86_64 (amd64)
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 : 94.03%
Collection Status : Collections enabled
Heartbeat Status : Ok
Last attempted heartbeat to OMS : 2015-08-19 00:38:02
Last successful heartbeat to OMS : 2015-08-19 00:38:02
Next scheduled heartbeat to OMS : 2015-08-19 00:39:02

---------------------------------------------------------------
Agent is Running and Ready

linux01(oracle:NONE)/u01/app/oracle/agent_inst/bin: ./emctl config agent listtargets
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved.
[linux01:1830, oracle_emd]

linux01(oracle:NONE)/u01/app/oracle/agent_inst/bin: ./emctl config agent addInternalTargets
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved.

linux01(oracle:NONE)/u01/app/oracle/agent_inst/bin: ./emctl config agent listtargets
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved.
[linux01, host]
[linux01:1830, oracle_emd]

Tuesday, April 7, 2015

How to change SYSMAN Password in OEM 12c

1. Stop all OMS instances running emctl stop oms.

$OMS_Home/bin/emctl stop oms

Execute the same command on all the OMS machines including the primary OMS machine. Do not include '-all' as the Admin Server needs to be up during this operation.

2. Modify the SYSMAN password:

$OMS_Home/bin/emctl config oms -change_repos_pwd

Provide Repository User's Current Password and Repository User's New Password in command prompt

3. Stop the Admin server on the primary OMS machine and re-start all the OMS:

$OMS_Home/bin/emctl stop oms –all

4. Restart all the Management Services:

$OMS_Home/bin/emctl start oms

oem12c.us.com(oracle)/u01/oracle/product/Middleware/oms/bin: emctl stop oms
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved.
Stopping WebTier...
WebTier Successfully Stopped
Stopping Oracle Management Server...
Oracle Management Server Successfully Stopped
Oracle Management Server is Down

oem12c.us.com(oracle)/u01/oracle/product/Middleware/oms/bin: emctl status oms -details
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved.
Enter Enterprise Manager Root (SYSMAN) Password :
Console Server Host : oem12c.us.com
HTTP Console Port : 7789
HTTPS Console Port : 7801
HTTP Upload Port : 4890
HTTPS Upload Port : 4901
EM Instance Home : /u01/oracle/product/gc_inst/em/EMGC_OMS1
OMS Log Directory Location : /u01/oracle/product/gc_inst/em/EMGC_OMS1/sysman/log
SLB or virtual hostname: oemtest.us.com
HTTPS SLB Upload Port : 4901
HTTPS SLB Console Port : 7801
Agent Upload is unlocked.
OMS Console is locked.
Active CA ID: 1
Console URL: https://oemtest.us.com:7801/em
Upload URL: https://oemtest.us.com:4901/empbs/upload

WLS Domain Information
Domain Name : GCDomain
Admin Server Host : oem12c.us.com
Admin Server HTTPS Port: 7102
Admin Server is RUNNING

Oracle Management Server Information
Managed Server Instance Name: EMGC_OMS1
Oracle Management Server Instance Host: oem12c.us.com
WebTier is Down
Oracle Management Server is Down

BI Publisher is not configured to run on this host.
oem12c.us.com(oracle)/u01/oracle/product/Middleware/oms/bin: emctl config oms -change_repos_pwd
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved.
Enter Repository User's Current Password :
Enter Repository User's New Password :

Changing passwords in backend ...
Passwords changed in backend successfully.
Updating repository password in Credential Store...
Successfully updated Repository password in Credential Store.
Restart all the OMSs using 'emctl stop oms -all' and 'emctl start oms'.
Successfully changed repository password.

oem12c.us.com(oracle)/u01/oracle/product/Middleware/oms/bin: emctl stop oms -all
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved.
Stopping WebTier...
WebTier Successfully Stopped
Stopping Oracle Management Server...
Oracle Management Server Already Stopped
AdminServer Successfully Stopped
Oracle Management Server is Down

oem12c.us.com(oracle)/u01/oracle/product/Middleware/oms/bin: emctl start oms
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved.
Starting Oracle Management Server...
Starting WebTier...
WebTier Successfully Started
Oracle Management Server Successfully Started
Oracle Management Server is Up

oem12c.us.com(oracle)/u01/oracle/product/Middleware/oms/bin: emctl status oms -details
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved.
Enter Enterprise Manager Root (SYSMAN) Password :
Console Server Host : oem12c.us.com
HTTP Console Port : 7789
HTTPS Console Port : 7801
HTTP Upload Port : 4890
HTTPS Upload Port : 4901
EM Instance Home : /u01/oracle/product/gc_inst/em/EMGC_OMS1
OMS Log Directory Location : /u01/oracle/product/gc_inst/em/EMGC_OMS1/sysman/log
SLB or virtual hostname: oemtest.us.com
HTTPS SLB Upload Port : 4901
HTTPS SLB Console Port : 7801
Agent Upload is unlocked.
OMS Console is locked.
Active CA ID: 1
Console URL: https://oemtest.us.com:7801/em
Upload URL: https://oemtest.us.com:4901/empbs/upload

WLS Domain Information
Domain Name : GCDomain
Admin Server Host : oem12c.us.com
Admin Server HTTPS Port: 7102
Admin Server is RUNNING

Oracle Management Server Information
Managed Server Instance Name: EMGC_OMS1
Oracle Management Server Instance Host: oem12c.us.com
WebTier is Up
Oracle Management Server is Up

BI Publisher is not configured to run on this host.

Wednesday, March 4, 2015

How to reset spfile parameter value

How to reset parameter value in oracle spfile

The following command can be used reset any parameter specified in spfile. Please note that it will only delete the entry from the spfile but the original value set at instance level will remain the same.

ALTER SYSTEM RESET <parameter_name>;
ALTER SYSTEM RESET <parameter_name> SCOPE=SPFILE SID='INSTANCE_NAME'; (incase of RAC)

SQL> select * from v$spparameter where name like 'processes';

INST_ID SID NAME TYPE VALUE DISPLAY_VALUE ISSPEC ORDINAL UPDATE_COMMENT
------- ----- ---------- -------- ------ -------------- ------ ------- --------------
1 * processes integer 300 300 TRUE 1
1 ORCL1 processes integer 300 300 TRUE 1
1 ORCL2 processes integer 300 300 TRUE 1

SQL> alter system reset processes scope=spfile sid='ORCL2';

System altered.

SQL> select * from v$spparameter where name like 'processes';

INST_ID SID NAME TYPE VALUE DISPLAY_VALUE ISSPEC ORDINAL UPDATE_COMMENT
------- ----- ---------- -------- ------ -------------- ------ ------- --------------
1 * processes integer 300 300 TRUE 1
1 ORCL1 processes integer 300 300 TRUE 1

SQL> alter system reset processes scope=spfile sid='ORCL2';

System altered.

SQL> select * from v$spparameter where name like 'processes';

INST_ID SID NAME TYPE VALUE DISPLAY_VALUE ISSPEC ORDINAL UPDATE_COMMENT
------- ----- ---------- -------- ------ -------------- ------ ------- --------------
1 * processes integer 300 300 TRUE 1

Friday, January 30, 2015

Oracle Opatch failed with "CheckActiveFilesAndExecutables"

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

Issue: While applying patch, the opatch failed with the below error

Following executables are active :
/opt/oracle/product/11.2.0.4.GRD/lib/libclntsh.so.11.1
UtilSession failed: Prerequisite check "CheckActiveFilesAndExecutables" failed.

Solution: The above mentioned file is still active or in use.

Check which process/tool is using the file and stop the process cleanly else kill the process.

you can use lsof(list open files) or fuser (identify processes using files or sockets) to identify the process using the file.

[oracle@linux01 ~]$ lsof /opt/oracle/product/11.2.0.4.GRD/lib/libclntsh.so.11.1
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
ons 2342 oracle mem REG 253,53 53817421 21213079 /apps/oracle/product/11.2.0.4.GRD/lib/libclntsh.so.11.1
ons 2344 oracle mem REG 253,53 53817421 21213079 /apps/oracle/product/11.2.0.4.GRD/lib/libclntsh.so.11.1

[oracle@linux01 ~]$ /sbin/fuser -cv /opt/oracle/product/11.2.0.4.GRD/lib/libclntsh.so.11.1
USER PID ACCESS COMMAND
/opt/oracle/product/11.2.0.4.GRD/lib/libclntsh.so.11.1:
oracle 2342 F.ce. ons
oracle 2344 F.ce. ons

Once the processes are identified, stop/kill them:
[oracle@linux01 ~]$ kill -9 2342
[oracle@linux01 ~]$ kill -9 2344

How to move datafile from one ASM diskgroup to another

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

Case 1: Make the tablespace offline and move the datafile
1. Check the file which you need to move.
2. Make the tablespace offline
3. Connect to rman
4. issue the copy command from the rman prompt
5. login to the sql prompt and issue rename datafile command
6. Make the tablespace online

Case 2: Make the datafile offline and move the datafile
1. Check the file which you need to move.
2. Make the datafile offline
3. Connect to rman
4. issue the copy command from the rman prompt
5. login to the sql prompt and issue rename datafile command
6. Recover the datafile (In case you make datafile offline you need to recover the datafile)
7. Make the datafile online

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)

Case 1:
SQL> select tablespace_name, file_name, online_status from dba_data_files;
TABLESPACE_NAME FILE_NAME ONLINE_STATUS
---------------- ---------------------------------------------------- -------------
TEST1 +DATA_DG01/orcl/datafile/test1.1117.870325481 ONLINE

SQL> alter tablespace test1 offline;
Tablespace altered.

SQL> select tablespace_name, file_name, online_status from dba_data_files;
TABLESPACE_NAME FILE_NAME ONLINE_STATUS
---------------- ---------------------------------------------------- -------------
TEST1 +DATA_DG01/orcl/datafile/test1.1117.870325481 OFFLINE

[oracle@linux01 ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Fri Jan 30 05:24:02 2015

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

connected to target database: ORCL (DBID=408657265)

RMAN> copy datafile '+DATA_DG01/orcl/datafile/test1.1117.870325481' to '+FRA_DG01';

Starting backup at 30-JAN-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=+DATA_DG01/orcl/datafile/test1.1117.870325481
output file name=+FRA_DG01/orcl/datafile/test1.263233.870326681 tag=TAG20150130T052441 RECID=7 STAMP=870326681
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 30-JAN-15

SQL> alter database rename file '+DATA_DG01/orcl/datafile/test1.1117.870325481' to '+FRA_DG01/orcl/datafile/test1.263233.870326681';
Database altered.

SQL> select tablespace_name, file_name, online_status from dba_data_files;
TABLESPACE_NAME FILE_NAME ONLINE_STATUS
---------------- ---------------------------------------------------- -------------
TEST1 +FRA_DG01/orcl/datafile/test1.263233.870326681 OFFLINE

SQL> alter tablespace test1 online;
Tablespace altered.

SQL> select tablespace_name, file_name, online_status from dba_data_files;
TABLESPACE_NAME FILE_NAME ONLINE_STATUS
---------------- ---------------------------------------------------- -------------
TEST1 +FRA_DG01/orcl/datafile/test1.263233.870326681 ONLINE

====================================================================

Case 2:
SQL> select tablespace_name, file_name, ONLINE_STATUS from dba_data_files;
TABLESPACE_NAME FILE_NAME ONLINE_STATUS
---------------- ------------------------------------------------ -------------
TEST1 +FRA_DG01/orcl/datafile/test1.256846.868340771 ONLINE

SQL> alter database datafile '+FRA_DG01/orcl/datafile/test1.256846.868340771' offline;
Database altered.

SQL> select tablespace_name, file_name, ONLINE_STATUS from dba_data_files;
TABLESPACE_NAME FILE_NAME ONLINE_STATUS
---------------- ------------------------------------------------ -------------
TEST1 +FRA_DG01/orcl/datafile/test1.256846.868340771 RECOVER

[oracle@linux01 ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Fri Jan 30 05:03:51 2015

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

connected to target database: ORCL (DBID=408657265)

RMAN> copy datafile '+FRA_DG01/orcl/datafile/test1.256846.868340771' to '+DATA_DG01';

Starting backup at 30-JAN-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=245 instance=RL3DB151 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=+FRA_DG01/orcl/datafile/test1.256846.868340771
output file name=+DATA_DG01/orcl/datafile/test1.1117.870325481 tag=TAG20150130T050439 RECID=6 STAMP=870325480
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 30-JAN-15

SQL> alter database rename file '+FRA_DG01/orcl/datafile/test1.256846.868340771' to '+DATA_DG01/orcl/datafile/test1.1117.870325481';
Database altered.

SQL> recover datafile '+DATA_DG01/orcl/datafile/test1.1117.870325481';
Media recovery complete.

SQL> select tablespace_name, file_name, ONLINE_STATUS from dba_data_files;
TABLESPACE_NAME FILE_NAME ONLINE_STATUS
---------------- ------------------------------------------------ -------------
TEST1 +DATA_DG01/orcl/datafile/test1.1117.870325481 OFFLINE

SQL> alter database datafile '+DATA_DG01/orcl/datafile/test1.1117.870325481' online;
Database altered.

SQL> select tablespace_name, file_name, ONLINE_STATUS from dba_data_files;
TABLESPACE_NAME FILE_NAME ONLINE_STATUS
---------------- ------------------------------------------------ -------------
TEST1 +DATA_DG01/orcl/datafile/test1.1117.870325481 ONLINE