redeploying OEM agent after cloning VM

Environment:

Oracle Linux 7.3, OEM agent 12.1.0.5.0

Solution:

prepare or re-use (if it was used for deployment) a response file, e.g. agent.rsp.
If reusing the existing file, you have to add OMS_HOST and EM_UPLOAD_PORT to the file.
If the original agent wast deployed by Pull method, you can get EM_UPLOAD_PORT from AgentPull.sh:
grep -i omsPort= AgentPull.sh
omsPort=4900

so an example of the response file:
OMS_HOST=oms_host_name
EM_UPLOAD_PORT=4900
LOGIN_USER=sysman
LOGIN_PASSWORD=pasvorto1
PLATFORM="Linux x86-64"
VERSION=12.1.0.5.0
AGENT_REGISTRATION_PASSWORD=pasvorto2

Delete files from AGENT_BASE_DIR/agent_inst
cd /opt/oracle/product/agent/agent_inst
rm -rf *

and run agentDeploy.sh script to redeloy the agent:
cd /opt/oracle/product/agent
./agentDeploy.sh AGENT_BASE_DIR=/opt/oracle/product/agent RESPONSE_FILE=/opt/oracle/product/agent.rsp

Validating the OMS_HOST & EM_UPLOAD_PORT
Executing command : /opt/oracle/product/agent/core/12.1.0.5.0/jdk/bin/java -classpath /opt/oracle/product/agent/core/12.1.0.5.0/jlib/agentInstaller.jar:/opt/oracle/product/agent/core/12.1.0.5.0/oui/jlib/OraInstaller.jar oracle.sysman.agent.installer.AgentInstaller /opt/oracle/product/agent/core/12.1.0.5.0 /opt/oracle/product/agent /opt/oracle/product/agent AGENT_BASE_DIR=/opt/oracle/product/agent RESPONSE_FILE=/opt/oracle/product/agent.rsp -prereq

Validating oms host & port with url: http://dtooem2:4900/empbs/genwallet
Validating oms host & port with url: https://dtooem2:4900/empbs/genwallet
Return status:3-oms https port is passed
Unzipping the agentcoreimage.zip to /opt/oracle/product/agent ....
plugin.zip
Executing command : /opt/oracle/product/agent/unzip -o /opt/oracle/product/agent/plugin.zip -d /opt/oracle/product/agent
Successfully unzipped /opt/oracle/product/agent/plugin.zip to /opt/oracle/product/agent !
Executing command : /opt/oracle/product/agent/core/12.1.0.5.0/jdk/bin/java -classpath /opt/oracle/product/agent/core/12.1.0.5.0/oui/jlib/OraInstaller.jar:/opt/oracle/product/agent/core/12.1.0.5.0/oui/jlib/xmlparserv2.jar:/opt/oracle/product/agent/core/12.1.0.5.0/oui/jlib/srvm.jar:/opt/oracle/product/agent/core/12.1.0.5.0/oui/jlib/emCfg.jar:/opt/oracle/product/agent/core/12.1.0.5.0/jlib/agentInstaller.jar:/opt/oracle/product/agent/core/12.1.0.5.0/oui/jlib/share.jar oracle.sysman.agent.installer.AgentInstaller /opt/oracle/product/agent/core/12.1.0.5.0 /opt/oracle/product/agent /opt/oracle/product/agent /opt/oracle/product/agent/agent_inst AGENT_BASE_DIR=/opt/oracle/product/agent


Executing agent install prereqs...
Executing command: /opt/oracle/product/agent/core/12.1.0.5.0/oui/bin/runInstaller -debug -ignoreSysPrereqs   -prereqchecker -silent -ignoreSysPrereqs -waitForCompletion  -prereqlogloc /opt/oracle/product/agent/core/12.1.0.5.0/cfgtoollogs/agentDeploy -entryPoint oracle.sysman.top.agent_Complete -detailedExitCodes PREREQ_CONFIG_LOCATION=/opt/oracle/product/agent/core/12.1.0.5.0/prereqs  -J-DAGENT_BASE_DIR=/opt/oracle/product/agent

Agent install prereqs completed successfully

Cloning the agent home...
Executing command: /opt/oracle/product/agent/core/12.1.0.5.0/oui/bin/runInstaller -debug -ignoreSysPrereqs   -clone -forceClone -silent -waitForCompletion -nowait ORACLE_HOME=/opt/oracle/product/agent/core/12.1.0.5.0 -responseFile /opt/oracle/product/agent.rsp  AGENT_BASE_DIR=/opt/oracle/product/agent AGENT_BASE_DIR=/opt/oracle/product/agent RESPONSE_FILE=/opt/oracle/product/agent.rsp -noconfig  ORACLE_HOME_NAME=agent12c2 -force b_noUpgrade=true AGENT_PORT=-1 EMCTLCFG_MODE=NONE

Cloning of agent home completed successfully

Attaching sbin home...
Executing command: /opt/oracle/product/agent/core/12.1.0.5.0/oui/bin/runInstaller -debug -ignoreSysPrereqs   -attachHome -waitForCompletion -nowait ORACLE_HOME=/opt/oracle/product/agent/sbin ORACLE_HOME_NAME=sbin12c2 -force

Attach home for sbin home completed successfully.

Updating home dependencies...
Executing command: /opt/oracle/product/agent/core/12.1.0.5.0/oui/bin/runInstaller -debug -ignoreSysPrereqs   -updateHomeDeps -waitForCompletion HOME_DEPENDENCY_LIST={/opt/oracle/product/agent/sbin:/opt/oracle/product/agent/core/12.1.0.5.0} -invPtrLoc /opt/oracle/product/agent/core/12.1.0.5.0/oraInst.loc -force

Update home dependency completed successfully.
Executing command: /opt/oracle/product/agent/core/12.1.0.5.0/oui/bin/runConfig.sh ORACLE_HOME=/opt/oracle/product/agent/core/12.1.0.5.0 RESPONSE_FILE=/opt/oracle/product/agent/core/12.1.0.5.0/agent.rsp ACTION=configure MODE=perform COMPONENT_XML={oracle.sysman.top.agent.11_1_0_1_0.xml} RERUN=true
Agent Configuration completed successfully

The following configuration scripts need to be executed as the "root" user.
#!/bin/sh
#Root script to run
 /opt/oracle/product/agent/core/12.1.0.5.0/root.sh
To execute the configuration scripts:
1. Open a terminal window
2. Log in as "root"
3. Run the scripts
Agent Deployment Successful.
Agent deployment log location: /opt/oracle/product/agent/core/12.1.0.5.0/cfgtoollogs/agentDeploy/agentDeploy_2017-05-12_15-28-29-PM.log
Agent deployment completed successfully.

Some observations:

agentDeploy.sh script runs with ORACLE_HOME_NAME=sbin12c2, but old default name sbin12c1 remains in central inventory. You can see it in oraInventory/ContentsXML/inventory.xml or by "opatch lsinv -all".

Indexing NULL values

Environment: 

Oracle database 11.2.0.4

Solution:

Create an index and add a constant to the end of the index so NULL values are stored.

For the following statement and regular index on PAYMENT_CLASSIFICATION_ID
select * from CDR.F_PAYMENT_ITEM where PAYMENT_CLASSIFICATION_ID is null;

Plan hash value: 2353806608
------------------------------------------------------------------------------------
| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |   434 | 38626 |   423K  (1)| 01:24:38 |
|*  1 |  TABLE ACCESS FULL| F_PAYMENT_ITEM |   434 | 38626 |   423K  (1)| 01:24:38 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
    1 - filter("PAYMENT_CLASSIFICATION_ID" IS NULL)


DROP INDEX CDR.FPI_PAYMNT_CLS_ID;

CREATE INDEX CDR.FPI_PAYMNT_CLS_ID ON CDR.F_PAYMENT_ITEM
(PAYMENT_CLASSIFICATION_ID,1);

Plan with the new index:
-------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                   |   434 | 38626 |    82   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| F_PAYMENT_ITEM    |   434 | 38626 |    82   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | FPI_PAYMNT_CLS_ID |   434 |       |     5   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("PAYMENT_CLASSIFICATION_ID" IS NULL)


Index type of the new index is FUNCTION-BASED NORMAL (from DBA_INDEXES).
One of disadvantages of Function-Based Indexes, according to Database Advanced Application Developer's Guide, chapter 4, Using Indexes in Database Applications:
- The database does not use function-based indexes when doing OR expansion.

but for this particular index, it works fine:
select * from CDR.F_PAYMENT_ITEM where PAYMENT_CLASSIFICATION_ID in (122,1);

--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |  2756 |   239K|   497   (0)| 00:00:06 |
|   1 |  INLIST ITERATOR             |                   |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| F_PAYMENT_ITEM    |  2756 |   239K|   497   (0)| 00:00:06 |
|*  3 |    INDEX RANGE SCAN          | FPI_PAYMNT_CLS_ID |  2756 |       |    11   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("PAYMENT_CLASSIFICATION_ID"=1 OR "PAYMENT_CLASSIFICATION_ID"=122)

After cloning VM with Oracle database

Contents

Environment. 1

Modifying Oracle Home. 1

Changing the DBID and Database Name with DBNEWID utility. 1

Re-creating control files. 1

Changing the DBID.. 1

 

Environment

Oracle Linux Server release 7.3, Oracle database 12.1.0.2

Modifying Oracle Home

Check /etc/oratab:

1.       Rename database in /etc/oratab (after recreating control file if doing so)

2.       Remove/comment out other databases in /etc/oratab if needed.

Check tnsnames.ora:

1.       Modify if necessary. It can contain some Listener entries.

2.       Create an entry for the new database

Listener:

·         Change HOST in listener.ora

·         Startup listener

Changing the DBID and Database Name with DBNEWID utility

If you just need to change DBID and DB name use nid, otherwise consider recreating control file.

Changing the DBID and Database Name with DBNEWID utility (nid):

https://docs.oracle.com/database/121/SUTIL/GUID-05B4733C-9EAA-438F-A2A4-4E84EB1E1E65.htm#SUTIL1544

sqlplus / as sysdba

shutdown immediate

startup mount

exit

 

nid TARGET=SYS DBNAME=new_name

 

Re-creating control files

If you need to change more than just DB name, e.g. directory name, then it is easier to recreate the control file.

sqlplus / as sysdba

alter database backup controlfile to trace;

 

Go to “trace” directory e.g.:

/opt/oracle/diag/rdbms/mdrt115/MDRT115/trace

Or find where the trace directory is:

select * from V$DIAG_INFO;

Copy the trace file to SQL file, e.g. recreate_cf.sql

Modify the SQL script:

1.       Remove all the lines upto the statement “STARTUP NOMOUNT”

2.       Change the database name

3.       Rename directories for datafiles and redo files.

4.       Add SET after REUSE

5.       RESETLOGS  NOARCHIVELOG

 

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE set DATABASE "MDRP115" RESETLOGS  NOARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 '/data001/oradata/MDRP115/redo01.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 2 '/data001/oradata/MDRP115/redo02.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 3 '/data001/oradata/MDRP115/redo03.log'  SIZE 50M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

  '/data001/oradata/MDRP115/system01.dbf',

  '/data001/oradata/MDRP115/sysaux01.dbf',

  '/data001/oradata/MDRP115/undotbs01.dbf',

  '/data001/oradata/MDRP115/users01.dbf',

  '/data001/oradata/MDRP115/XMETA001.dbf',

  '/data001/oradata/MDRP115/XMETASR001.dbf',

  '/data001/oradata/MDRP115/IAUSER001.dbf',

  '/data001/oradata/MDRP115/DSODBSPACE01.dbf',

  '/data001/oradata/MDRP115/QSSRDSPACE01.dbf',

  '/data001/oradata/MDRP115/ESDBSPACE01.dbf'

CHARACTER SET AL32UTF8

;

 

·         Shutdown the database with immediate option. Do not abort.

shutdown immediate

·         create pfile from spfile;

·         rename data file directories if needed (and mount points in /etc/fstab)

·         rename the just created pfile

mv initMDRT115.ora initMDRP115.ora

·         edit the new pfile initMDRP115.ora and change dbname and directory names:

.audit_file_dest

.control_files

.db_name

.dispatchers

·         Make sure directory for parameter audit_file_dest exists.

·         Modify /etc/oratab, create an entry for the new database.

·         Set new env variables (oraenv)

·         Create new spfile from pfile

create spfile from pfile;

·         Run SQL script to re-create control file

@recreate_cf.sql

ORACLE instance started.

 

Total System Global Area 1493172224 bytes

Fixed Size                  2924592 bytes

Variable Size             536874960 bytes

Database Buffers          939524096 bytes

Redo Buffers               13848576 bytes

 

Control file created.

 

·         Open database

ALTER DATABASE OPEN RESETLOGS;

·         Add TEMP

ALTER TABLESPACE TEMP ADD TEMPFILE '/data001/oradata/MDRP115/temp01.dbf'   SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

·         Create password file

cd $OH/dbs

orapwd FILE=orapwMDRP115

 

Re-creating control file does not change DBID!

 

Changing the DBID

sqlplus / as sysdba

shutdown immediate

startup mount

exit

 

nid TARGET=SYS

 

DBNEWID: Release 12.1.0.2.0 - Production on Thu Apr 27 13:57:30 2017

 

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

 

Password:

Connected to database MDRP115 (DBID=3410461342)

 

Connected to server version 12.1.0

 

Control Files in database:

    /data001/oradata/MDRP115/control01.ctl

    /data001/oradata/MDRP115/control02.ctl

 

Change database ID of database MDRP115? (Y/[N]) => y

 

Proceeding with operation

Changing database ID from 3410461342 to 356948765

    Control File /data001/oradata/MDRP115/control01.ctl - modified

    Control File /data001/oradata/MDRP115/control02.ctl - modified

    Datafile /data001/oradata/MDRP115/system01.db - dbid changed

    Datafile /data001/oradata/MDRP115/sysaux01.db - dbid changed

    Datafile /data001/oradata/MDRP115/undotbs01.db - dbid changed

    Datafile /data001/oradata/MDRP115/users01.db - dbid changed

    Datafile /data001/oradata/MDRP115/XMETA001.db - dbid changed

    Datafile /data001/oradata/MDRP115/XMETASR001.db - dbid changed

    Datafile /data001/oradata/MDRP115/IAUSER001.db - dbid changed

    Datafile /data001/oradata/MDRP115/DSODBSPACE01.db - dbid changed

    Datafile /data001/oradata/MDRP115/QSSRDSPACE01.db - dbid changed

    Datafile /data001/oradata/MDRP115/ESDBSPACE01.db - dbid changed

    Datafile /data001/oradata/MDRP115/temp01.db - dbid changed

    Control File /data001/oradata/MDRP115/control01.ctl - dbid changed

    Control File /data001/oradata/MDRP115/control02.ctl - dbid changed

    Instance shut down

 

Database ID for database MDRP115 changed to 356948765.

All previous backups and archived redo logs for this database are unusable.

Database is not aware of previous backups and archived logs in Recovery Area.

Database has been shutdown, open database with RESETLOGS option.

Succesfully changed database ID.

DBNEWID - Completed succesfully.

 

sqlplus / as sysdba

startup mount

ALTER DATABASE OPEN RESETLOGS;

 

QUERY parameter in Oracle datapump import

Environment

Oracle 11.2.0.4

Symptoms

Using parameters
TABLES= CDR.F_CLAIM_WORK_CAPACITY
QUERY="where claim_ID in (select claim_id from Z.E_ROLLING_CLAIM )"

output
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
UDI-00014: invalid value for parameter, 'query'

I believe Oracle cannot identify a table of the first claim_ID.

Solutions

Syntax

QUERY = [schema.][table_name:] query_clause

Using [schema.][table_name:] for the same query
TABLES= CDR.F_CLAIM_WORK_CAPACITY
QUERY=CDR.F_CLAIM_WORK_CAPACITY:"where claim_ID in (select claim_id from Z.E_ROLLING_CLAIM )"

that works (the schema was remapped from CDR to Z)
. . imported "Z"."F_CLAIM_WORK_CAPACITY"    9.391 MB  154122 out of 157095 rows

The table alias used by Data Pump for the table being unloaded is KU$. Using KU$.
TABLES= CDR.F_CLAIM_WORK_CAPACITY
QUERY="where KU$.claim_ID in (select claim_id from Z.E_ROLLING_CLAIM )"
works
. . imported "Z"."F_CLAIM_WORK_CAPACITY"     9.391 MB  154122 out of 157095 rows

It works for multiple tables too, elapsed 0 00:17:43
TABLES= CDR.F_CLAIM_WORK_CAPACITY,CDR.K_CLAIM_KEY_FIGURES
. . imported "Z"."K_CLAIM_KEY_FIGURES"     4.088 GB 36215408 out of 41843789 rows
. . imported "Z"."F_CLAIM_WORK_CAPACITY"   9.391 MB  154122 out of 157095 rows

The following two parameters do not work and return the same error:
UDI-00014: invalid value for parameter, 'query'

QUERY="where exists (select 1 from Z.E_ROLLING_CLAIM rc where rc.claim_id=ku$.claim_ID )"
QUERY="where exists (select claim_ID from Z.E_ROLLING_CLAIM rc where rc.claim_id=ku$.claim_ID )"
I understand that "select claim_ID" is ambiguously defined, but I expected "select 1" would work.
Anyway the following works fine.
QUERY="where exists (select rc.claim_ID from Z.E_ROLLING_CLAIM rc where rc.claim_id=ku$.claim_ID )"

. . imported "Z"."K_CLAIM_KEY_FIGURES"    4.088 GB 36215408 out of 41843789 rows
. . imported "Z"."F_CLAIM_WORK_CAPACITY"  9.391 MB  154122 out of 157095 rows

and it is a bit faster (elapsed 0 00:12:27) than "where claim_id in ()". Maybe just because of cache :-)

Cron jobs are no longer running

Environment: Red Hat Enterprise Linux Server release 6.6 (Santiago)

# crontab -e

Authentication token is no longer valid; new one required
You (root) are not allowed to access to (crontab) because of pam configuration.

there were no changes in /etc/security/access.conf

the problem is root`s expired password

# chage -l root

Last password change                                    : Mar 20, 2015

Password expires                                        : Apr 29, 2015

Password inactive                                       : never

Account expires                                         : never

Minimum number of days between password change          : 0

Maximum number of days between password change          : 40

Number of days of warning before password expires       : 7

# passwd root

and check 

# crontab -l

HTTP 500: Internal Server Error when login to a new cloned environment

Environment

Oracle e-Business Suite 12.1.3 (Oracle EBS)

Oracle Linux 5.7

Symptoms

The following error occurs when trying to login to the new cloned environment.

500 Internal Server Error

File application.log in $LOG_HOME/ora/10.1.3/j2ee/oacore/oacore_default_group_1 shows

Caused by: oracle.apps.jtf.base.resources.FrameworkException:

ORA-01578: ORACLE data block corrupted (file # 12, block # 180784)

ORA-01110: data file 12: '/data/oradata/TEST12/APPS_TS_SUMMARY01.dbf'

ORA-26040: Data block was loaded using the NOLOGGING option

Solution

To identify the corrupted object use:

select s.*

from dba_extents s

where file_id = 12 -- change

and 180784 -- change

between block_id and (block_id + blocks -1);

It happened to be APPLSYS.WF_LOCAL_USER_ROLES

The solution is described in Oracle Support Doc ID 781413.1

sqlplus apps/password

set serveroutput on size 100000;

declare

begin

WF_LOCAL_SYNCH.BULKSYNCHRONIZATION(

P_ORIG_SYSTEM => 'ALL',

P_PARALLEL_PROCESSES => null,

P_LOGGING => null,

P_RAISEERRORS => TRUE);

exception

when others then

dbms_output.put_line('sqlerrm = ' || sqlerrm);

end;

/

Useful info

Partitions in Workflow Local Tables are Automatically Switched to NOLOGGING (Doc ID 433280.1)

Http 500 : Internal Server Error When Login To A New Cloned Environment (Doc ID 781413.1)

How to find whether an oracle database patch was applied

Using SQL:
select * from sys.registry$history;
It is useful for patch set levels, but not for a particular bug/patch.

OPatch, in Linux
$ORACLE_HOME/OPatch/opatch lsinventory
$ORACLE_HOME/OPatch/opatch lsinventory | grep something
some useful flags
-bugs_fixed Reports bugs fixed by installed patches with bug descriptions and extra info
-patch ..order based on installed time
-patch_id ..order based on patch numbers

In OEM 12c
Targets > All Targrets > 
Targer Type > Others > Oracle Home > click your Target
on Oracle Home page, see tab Patches Applied. In the table below, you can find Bugs Fixed, Files and Components.

For multiple targets, you can create an OEM job to run a SQL script on multiple databases or opatch command on multiple hosts.

In OEM 12c
Enterprise > Configuration > Inventory and Usage Details
Show "Database Installations"
In the table below, column "Patches Applied", click "Yes" (or No, but in this case there is nothing to see)