:: Stracca Blog :: Stefano Stracuzzi TECH blog

Recreate Enterprise Manager Repository on 10g Windows with multiple ip addresses

If you have a Windows Server with multiple IP addresses and you install a Database Instance with Enterprise Manager Repository and you bind your

LISTENER to a different

ip address than the one that respond to the machine name you had to manually drop and recreate the EM.

You had to open a dos command prompt and setup your SID and you correct IP Address:

C:\> set ORACLE_SID=YOUR_SID
C:\>

  set ORACLE_HOSTNAME=10.1.0.100

Unlock DBSNMP and SYSMAN user and reset their password connecting :

sqlplus "sys/YOUR_PASSWORD@YOUR_SID as sysdba"
alter user dbsnmp account unlock identified by YOUR_PASSWORD;
alter user sysman account unlock identified by YOUR_PASSWORD;

Go to %ORACLE_HOME%\bin directory and drop EM Repository:

C:\oracle\product\10.2.0\db1\bin> emca -deconfig dbcontrol db -repos drop

Clear some DB Object that can remain:

sqlplus "sys/YOUR_PASSWORD@YOUR_SID as sysdba"
drop public synonym SETEMVIEWUSERCONTEXT;
drop ROLE MGMT_USER;
drop

  PUBLIC SYNONYM MGMT_TARGET_BLACKOUTS;
drop user sysman cascade;
drop user MGMT_VIEW cascade;
exit;

Recreate the EM Repository:

C:\oracle\product\10.2.0\db1\bin> emca -config dbcontrol db -repos create

When you start emca operations follow online instructions.

Oracle: drop a big TEMP tablespace

In order to drop a TEMPORARY tablespace the always working way is to create a new one, set as default and the after all opened session have finished to use the older one, drop it:

--- Create a New Temporary tablespace
CREATE TEMPORARY TABLESPACE TEMP2
TEMPFILE '/oradata/DBNAME/temp2_01.dbf' SIZE 512M REUSE
AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
/
--- Set as DEFAUL TEMPORARY TABLESPACE
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2
/

--- After some time DROP

  the original one
DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES
/

If you want you can recreate the original one and set it as default:

CREATE TEMPORARY TABLESPACE TEMP
TEMPFILE '/oradata/DBNAME/temp_01.dbf' SIZE 512M REUSE
AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
/
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP
/
DROP TABLESPACE TEMP2 INCLUDING CONTENTS AND DATAFILES
/

Oracle: Move tables on an other tablespace

MoveTables.sql

accept uni prompt 'UserName: ' accept ftbsi prompt 'Tablespace Name FROM: ' accept ttbsi prompt 'Tablespace Name TO: ' SET FEEDBACK OFF SET HEAD OFF SET PAGESIZE 10000 SET VERIFY OFF SPOOL %TEMP%\MoveTables(&uni).sql select 'ALTER

 TABLE "' || owner || '"."' || table_name || '" MOVE TABLESPACE ' || upper('&ttbsi') || ' NOLOGGING;' from dba_tables where owner = upper('&uni') and tablespace_name = upper('&ftbsi'); SPOOL OFF SET FEEDBACK 6 SET HEAD ON SET PAGESIZE 24 SET VERIFY ON PAUSE "Press Enter to move tables or CTRL-C to cancel" SPOOL %TEMP%\MoveTables(&uni).err @%TEMP%\MoveTables(&uni).sql SPOOL OFF

Oracle: Move indexes on an other tablespace

MoveIndexes.sql:

accept uni prompt 'UserName: '
accept tbsi prompt 'Tablespace Name where moving indexes: '

SET FEEDBACK OFF
SET HEAD OFF
SET PAGESIZE 10000
SET VERIFY OFF
SPOOL %TEMP%\MoveIndexes(&uni).sql

select 'ALTER INDEX "' || owner || '"."' ||

  index_name || '" REBUILD TABLESPACE ' || upper('&tbsi') || ';' from dba_indexes where owner = upper('&uni') and tablespace_name <> upper('&tbsi');

SPOOL OFF
SET FEEDBACK 6
SET HEAD ON
SET PAGESIZE 24
SET VERIFY ON

PAUSE "Press Enter to move indexes or CTRL-C to cancel"

SPOOL %TEMP%\MoveIndexes(&uni).err
@%TEMP%\MoveIndexes(&uni).sql
SPOOL OFF