Remove an Oracle Reports server

Assume %OMH% is Oracle Middle Tier Home

Go to %OMH% /sysman/emd

Open file targets.xml for editing.

Check for [Target] tag where its attribute "NAME" should matches as follows:
“[ias_instance_name]_Reports_Server: [reports_server_to_be_removed]”
In our example it is oracleas.dbserver_Reports_anand
Where
"oracleas.dbserver" is ias_instance_name
"anand" is reports server name
Select from Starting Tag [Target..] to ending tag [/Target] and delete selected lines.
[Target TYPE=”oracle_repserv” NAME=”[ias_instance_name]_Reports_Server: [reports_server_to_be_removed]” …]…[/Target]

Save and Close targets.xml file.

Now open console and go to %OMH%/dcm/bin

Give following commands to update and resync console with new configuration:

1. dcmctl updateconfig -ct opmn -v -d
2. dcmctl resyncinstance -v -d

Once done without any error, go to %OMH%/bin
Give following commands to restart the console:

1. emctl stop iasconsole
2. emctl start iasconsole
That's it, Report Server is removed now!

You can also delete from:
ORACLE_HOME/opmn/conf/opmn.xml
the:
ias-component id="MyRepServerName" .....

Create a New Report Server

%OMH% = Oracle Middle Tier Home
Open command prompt:
C:\>rwserver server=%newreportserver_name% start
Shut down the opmn:
C:\>%OMH%\opmn\bin\opmnctl stopall
Add new server target to opmn.xlm
C:\>%OMH%\bin\addNewServerTarget.bat %newreportserver_name%
The DOS window will close automatically.

Open command prompt again:

Update the config with new settings
C:\>%OMH%\dcm\bin\dcmctl.bat updateconfig -ct opmn -v -d
C:\>%OMH%\dcm\bin\dcmctl.bat resyncinstance -v -d
Close the report server window if it's opened

Start the opmnC:\>%OMH%\opmn\bin\opmnctl startall

Find the Binary value of a number (<1024)

SELECT e.val,
nullif(sign(bitand(e.val, power(2, 0))), 0) b0,
nullif(sign(bitand(e.val, power(2, 1))), 0) b1,
nullif(sign(bitand(e.val, power(2, 2))), 0) b2,
nullif(sign(bitand(e.val, power(2, 3))), 0) b3,
nullif(sign(bitand(e.val, power(2, 4))), 0) b4,
nullif(sign(bitand(e.val, power(2, 5))), 0) b5,
nullif(sign(bitand(e.val, power(2, 6))), 0) b6,
nullif(sign(bitand(e.val, power(2, 7))), 0) b7,
nullif(sign(bitand(e.val, power(2, 8))), 0) b8
FROM (SELECT 55 val
FROM dual) e;

recompile function by - Solomon Yakobson

CREATE OR REPLACE FUNCTION recompile(o_owner IN VARCHAR2 := '%',


o_name IN VARCHAR2 := '%',

o_type IN VARCHAR2 := '%',

o_status IN VARCHAR2 := 'INVALID',

display IN BOOLEAN := TRUE)

RETURN NUMBER AUTHID CURRENT_USER IS

-- _____________________________________________________________________

--



--
Recompile Utility


--
____________________________________________________________________


--

-- FILE: recompile.sql

-- TITLE: Recompile Utility

-- TYPE: ORACLE PL/SQL Stored Function

-- VERSION: 2.0

-- CREATED: August 3, 1998

-- AUTHOR: Solomon Yakobson

-- WARNING:

-- SCOPE: Recompile Utility can be used for Oracle 7.3 and above object compilation.

--

-- MODIFICATION

-- HISTORY: September 9, 1998 - fixed obj_cursor to include objects

-- with no dependencies.

--

-- May 12, 1999 - fix for DBMS_SQL behavior change in

-- Oracle 8 (most likely it is a bug).

-- If object recompilation has errors,

-- ORACLE 8 DBMS_SQL raises exception:

-- ORA-24333: success with compilation

-- error, followed by host environment

-- (e.g. SQL*Plus) internal error and

-- Unsafe to proceed message.

--

-- May 12, 1999 - added COMPILE_ERRORS return code.

--

-- May 12, 1999 - added TYPE and TYPE BODY objects.

--

-- Jan 10, 2006 - upgraded by Steven Feuerstein

--

-- DESCRIPTION: Recompile Utility is designed to compile the following

-- types of objects:

--

-- PROCEDURE (ORACLE 7 & 8),

-- FUNCTION (ORACLE 7 & 8),

-- PACKAGE - specification and body (ORACLE 7 & 8),

-- PACKAGE BODY - body only (ORACLE 7 & 8),

-- TRIGGER (ORACLE 7 & 8),

-- VIEW (ORACLE 7 & 8),

-- TYPE - specification only (ORACLE 8),

-- TYPE BODY - body only (ORACLE 8).

--

-- Objects are recompiled based on object dependencies and

-- therefore compiling all requested objects in one path.

-- Recompile Utility skips every object which is either of

-- unsupported object type or depends on INVALID object(s)

-- outside of current request (which means we know upfront

-- compilation will fail anyway). If object recompilation

-- is not successful, Recompile Utility continues with the

-- next object. Recompile Utility has five parameters:

--

-- o_owner - IN mode parameter is a VARCHAR2 defining

-- owner of to be recompiled objects. It

-- accepts operator LIKE widcards. Backslash

-- (\) is used for escaping wildcards. If

-- omitted, parameter defaults to USER.

-- o_name - IN mode parameter is a VARCHAR2 defining

-- names of to be recompiled objects. It

-- accepts operator LIKE widcards. Backslash

-- (\) is used for escaping wildcards. If

-- omitted, it defaults to '%' - any name.

-- o_type - IN mode parameter is a VARCHAR2 defining

-- types of to be recompiled objects. It

-- accepts operator LIKE widcards. Backslash

-- (\) is used for escaping wildcards. If

-- omitted, it defaults to '%' - any type.

-- o_status - IN mode parameter is a VARCHAR2 defining

-- status of to be recompiled objects. It

-- accepts operator LIKE widcards. Backslash

-- (\) is used for escaping wildcards. If

-- omitted, it defaults to 'INVALID'.

-- display - IN mode parameter is a BOOLEAN defining

-- whether object recompile status is written

-- to DBMS_OUTPUT buffer. If omitted, it

-- defaults to TRUE.

--

-- Recompile Utility returns the following values or their

-- combinations:

--

-- 0 - Success. All requested objects are recompiled and

-- are VALID.

-- 1 - INVALID_TYPE. At least one of to be recompiled

-- objects is not of supported object type.

-- 2 - INVALID_PARENT. At least one of to be recompiled

-- objects depends on an invalid object outside of

-- current request.

-- 4 - COMPILE_ERRORS. At least one of to be recompiled

-- objects was compiled with errors and is INVALID.

--

-- If parameter display is set to TRUE, Recompile Utility

-- writes the following information to DBMS_OUTPUT buffer:

--

-- RECOMPILING OBJECTS

--

-- Object Owner is o_owner

-- Object Name is o_name

-- Object Type is o_type

-- Object Status is o_status

--

-- TTT OOO.NNN is recompiled. Object status is SSS.

-- TTT OOO.NNN references invalid object(s) outside of

-- this request.

-- OOO.NNN is TTT and can not be recompiled.

--

-- where o_owner is parameter o_owner value, o_name is

-- parameter o_name value, o_type is parameter o_type

-- value and o_status is is parameter o_status value. TTT

-- is object type, OOO is object owner, NNN is object name

-- and SSS is object status after compilation.

--

-- NOTES: If parameter display is set to TRUE, you MUST ensure

-- DBMS_OUTPUT buffer is large enough for produced output.

-- Otherwise Recompile Utility will not recompile all the

-- objects. If used in SQL*Plus, issue:

--

-- SET SERVEROUTPUT ON SIZE xxx FORMAT WRAPPED

--

-- FORMAT WRAPPED is needed for text alignment.

-- ______________________________________________________________________

--



-- Exceptions

successwithcompilationerror EXCEPTION;

PRAGMA EXCEPTION_INIT(successwithcompilationerror, -24344);

-- Return Codes

invalid_type CONSTANT INTEGER := 1;

invalid_parent CONSTANT INTEGER := 2;

compile_errors CONSTANT INTEGER := 4;

cnt NUMBER;

dyncur INTEGER;

type_status INTEGER := 0;

parent_status INTEGER := 0;

recompile_status INTEGER := 0;

object_status VARCHAR2(30);



CURSOR invalid_parent_cursor(oowner VARCHAR2, oname VARCHAR2, otype VARCHAR2, ostatus VARCHAR2, OID NUMBER) IS

SELECT /*+ RULE */

o.object_id

FROM public_dependency d, all_objects o

WHERE d.object_id = OID

AND o.object_id = d.referenced_object_id

AND o.status != 'VALID'

MINUS

SELECT /*+ RULE */

object_id

FROM all_objects

WHERE owner LIKE upper(oowner)

AND object_name LIKE upper(oname)

AND object_type LIKE upper(otype)

AND status LIKE upper(ostatus);



CURSOR recompile_cursor(OID NUMBER) IS

SELECT /*+ RULE */

'ALTER '

decode(object_type,

'PACKAGE BODY',

'PACKAGE',

'TYPE BODY',

'TYPE',

object_type)

' '

owner

'.'

object_name



' COMPILE '

decode(object_type,

'PACKAGE BODY',

' BODY',

'TYPE BODY',

'BODY',

'TYPE',

'SPECIFICATION',

'')

decode(object_type, 'VIEW', '', ' REUSE SETTINGS') stmt,

object_type,

owner,

object_name

FROM all_objects

WHERE object_id = OID;



recompile_record recompile_cursor%ROWTYPE;



CURSOR obj_cursor(oowner VARCHAR2, oname VARCHAR2, otype VARCHAR2, ostatus VARCHAR2) IS

SELECT /*+ RULE */

MAX(LEVEL) dlevel, object_id

FROM sys.public_dependency

START WITH object_id IN

(SELECT object_id

FROM all_objects

WHERE owner LIKE upper(oowner)

AND object_name LIKE upper(oname)

AND object_type LIKE upper(otype)

AND status LIKE upper(ostatus))

CONNECT BY NOCYCLE object_id = PRIOR referenced_object_id

GROUP BY object_id

HAVING MIN(LEVEL) = 1

UNION ALL

SELECT 1 dlevel, object_id

FROM all_objects o

WHERE owner LIKE upper(oowner)

AND object_name LIKE upper(oname)

AND object_type LIKE upper(otype)

AND status LIKE upper(ostatus)

AND NOT EXISTS (SELECT 1

FROM sys.public_dependency d

WHERE d.object_id = o.object_id)

ORDER BY 1 DESC;



TYPE integer_tt IS TABLE OF PLS_INTEGER INDEX BY BINARY_INTEGER;



l_dlevel integer_tt;

l_object_id integer_tt;



CURSOR status_cursor(OID NUMBER) IS

SELECT /*+ RULE */

status

FROM all_objects

WHERE object_id = OID;

BEGIN

-- Recompile requested objects based on their dependency levels.

IF display THEN

dbms_output.put_line(chr(0));

dbms_output.put_line(' RECOMPILING OBJECTS');

dbms_output.put_line(chr(0));

dbms_output.put_line(' Object Owner is '



o_owner);

dbms_output.put_line(' Object Name is '



o_name);

dbms_output.put_line(' Object Type is '



o_type);

dbms_output.put_line(' Object Status is '



o_status);

dbms_output.put_line(chr(0));

END IF;



dyncur := dbms_sql.open_cursor;



OPEN obj_cursor(o_owner, o_name, o_type, o_status);



FETCH obj_cursor BULK COLLECT

INTO l_dlevel, l_object_id;



FOR indx IN 1 .. l_dlevel.COUNT LOOP

OPEN recompile_cursor(l_object_id(indx));



FETCH recompile_cursor

INTO recompile_record;



CLOSE recompile_cursor;



-- We can recompile only Functions, Packages, Package Bodies,

-- Procedures, Triggers, Views, Types and Type Bodies.

IF recompile_record.object_type IN

('FUNCTION', 'PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'TRIGGER',

'VIEW', 'TYPE', 'TYPE BODY') THEN

-- There is no sense to recompile an object that depends on

-- invalid objects outside of the current recompile request.

OPEN invalid_parent_cursor(o_owner,

o_name,

o_type,

o_status,

l_object_id(indx));



FETCH invalid_parent_cursor

INTO cnt;



IF invalid_parent_cursor%NOTFOUND THEN

-- Recompile object.

BEGIN

dbms_sql.parse(dyncur, recompile_record.stmt, dbms_sql.native);

EXCEPTION

WHEN successwithcompilationerror THEN

NULL;

END;



OPEN status_cursor(l_object_id(indx));



FETCH status_cursor

INTO object_status;



CLOSE status_cursor;



IF display THEN

dbms_output.put_line(recompile_record.object_type

' '



recompile_record.owner

'.'



recompile_record.object_name



' is recompiled. Object status is '



object_status

'.');

END IF;



IF object_status <> 'VALID' THEN

recompile_status := compile_errors;

END IF;

ELSE

IF display THEN

dbms_output.put_line(recompile_record.object_type

' '



recompile_record.owner

'.'



recompile_record.object_name



' references invalid object(s)'



' outside of this request.');

END IF;



parent_status := invalid_parent;

END IF;



CLOSE invalid_parent_cursor;

ELSE

IF display THEN

dbms_output.put_line(recompile_record.owner

'.'



recompile_record.object_name

' is a '



recompile_record.object_type



' and can not be recompiled.');

END IF;



type_status := invalid_type;

END IF;

END LOOP;



dbms_sql.close_cursor(dyncur);

RETURN type_status + parent_status + recompile_status;

EXCEPTION

WHEN OTHERS THEN

IF obj_cursor%ISOPEN THEN

CLOSE obj_cursor;

END IF;



IF recompile_cursor%ISOPEN THEN

CLOSE recompile_cursor;

END IF;



IF invalid_parent_cursor%ISOPEN THEN

CLOSE invalid_parent_cursor;

END IF;



IF status_cursor%ISOPEN THEN

CLOSE status_cursor;

END IF;



IF dbms_sql.is_open(dyncur) THEN

dbms_sql.close_cursor(dyncur);

END IF;



RAISE;

END;

/

sys_context - userenv

SELECT sys_context('USERENV', 'ACTION') action,
sys_context('USERENV', 'AUDITED_CURSORID') audited_cursorid,
sys_context('USERENV', 'AUTHENTICATED_IDENTITY') authenticated_identity,
sys_context('USERENV', 'AUTHENTICATION_DATA') authentication_data,
sys_context('USERENV', 'AUTHENTICATION_METHOD') authentication_method,
sys_context('USERENV', 'BG_JOB_ID') bg_job_id,
sys_context('USERENV', 'CLIENT_IDENTIFIER') client_identifier,
sys_context('USERENV', 'CLIENT_INFO') client_info,
sys_context('USERENV', 'CURRENT_BIND') current_bind,
sys_context('USERENV', 'CURRENT_SCHEMA') "current_schema",
sys_context('USERENV', 'CURRENT_SCHEMAID') current_schemaid,
sys_context('USERENV', 'CURRENT_SQL') current_sql,
sys_context('USERENV', 'CURRENT_SQL_LENGTH') current_sql_length,
sys_context('USERENV', 'DB_DOMAIN') db_domain,
sys_context('USERENV', 'DB_NAME') db_name,
sys_context('USERENV', 'DB_UNIQUE_NAME') db_unique_name,
sys_context('USERENV', 'ENTRYID') entryid,
sys_context('USERENV', 'ENTERPRISE_IDENTITY') enterprise_identity,
sys_context('USERENV', 'FG_JOB_ID') fg_job_id,
sys_context('USERENV', 'GLOBAL_CONTEXT_MEMORY') global_context_memory,
sys_context('USERENV', 'GLOBAL_UID') global_uid,
sys_context('USERENV', 'HOST') host,
sys_context('USERENV', 'IDENTIFICATION_TYPE') identification_type,
sys_context('USERENV', 'INSTANCE') "instance",
sys_context('USERENV', 'INSTANCE_NAME') instance_name,
sys_context('USERENV', 'IP_ADDRESS') ip_address,
sys_context('USERENV', 'ISDBA') isdba,
sys_context('USERENV', 'LANG') lang,
sys_context('USERENV', 'LANGUAGE') "language",
sys_context('USERENV', 'MODULE') "module",
sys_context('USERENV', 'NETWORK_PROTOCOL') network_protocol,
sys_context('USERENV', 'NLS_CALENDAR') nls_calendar,
sys_context('USERENV', 'NLS_CURRENCY') nls_currency,
sys_context('USERENV', 'NLS_DATE_FORMAT') nls_date_format,
sys_context('USERENV', 'NLS_DATE_LANGUAGE') nls_date_language,
sys_context('USERENV', 'NLS_SORT') nls_sort,
sys_context('USERENV', 'NLS_TERRITORY') nls_territory,
sys_context('USERENV', 'OS_USER') os_user,
sys_context('USERENV', 'POLICY_INVOKER') policy_invoker,
sys_context('USERENV', 'PROXY_ENTERPRISE_IDENTITY') proxy_enterprise_identity,
-- sys_context('USERENV', 'PROXY_GLOBAL_UID') proxy_global_uid,
sys_context('USERENV', 'PROXY_USER') proxy_user,
sys_context('USERENV', 'PROXY_USERID') proxy_userid,
sys_context('USERENV', 'SERVER_HOST') server_host,
sys_context('USERENV', 'SERVICE_NAME') service_name,
sys_context('USERENV', 'SESSION_USER') session_user,
sys_context('USERENV', 'SESSION_USERID') session_userid,
sys_context('USERENV', 'SESSIONID') sessionid,
sys_context('USERENV', 'SID') sid,
sys_context('USERENV', 'STATEMENTID') statementid,
sys_context('USERENV', 'TERMINAL') terminal
FROM dual;