Archive

Archive for October, 2011

How to (re)configure Oracle Enterprise Manager.

When you’ve installed Oracle Database 10g or 11g, you can’t change the Windows hostname and/or IP address without reinstalling Oracle Enterprise Manager.
These are the steps to reconfigure Oracle EM:

1. Change the %ORACLE_HOME%\network\admin\listener.ora file from an IP address to a machine name.

2. Change the %ORACLE_HOME%\network\admin\tnsnames.ora file from an IP address to a machine name.

3. Change the C:\WINDOWS\System32\drivers\etc\hosts file by adding this line beneath the default localhost line (for the new hostname value):

127.0.0.1       localhost
192.168.111.11  new-hostname

Don’t forget to add the empty line after the last line (sometimes Windows can’t read the last line).

4. Change the Windows hostname.

5. Reboot the machine, and then drop the Oracle EM configuration with the following commands:

set ORACLE_SID=orcl
emca -deconfig dbcontrol db -repos drop

6. You’ll receive the following prompts, enter the Oracle SID, port number and all passwords:

STARTED EMCA at Jul 13, 2008 8:26:42 AM
EM Configuration Assistant, Version 11.1.0.5.0 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
 
Enter the following information:
Database SID: orcl
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:
Password for SYSMAN user:
Do you wish to continue? [yes(Y)/no(N)]: y

7. You should then drop the SYSMAN user manually just in case. If the SYSMAN schema hasn’t been droped, then you’ll raise an error when you try to recreate the EM:

CONFIG: ORA-20001: SYSMAN already EXISTS..
ORA-06512: at line 17
 
oracle.sysman.assistants.util.sqlEngine.SQLFatalErrorException: ORA-20001: SYSMAN already EXISTS..
ORA-06512: at line 17

Drop the user and dependent on version a few other objects, like:

DROP USER sysman CASCADE;
DROP PUBLIC SYNONYM setemviewusercontext;
DROP ROLE mgmt_user;
DROP PUBLIC SYNONYM mgmt_target_blackouts;
DROP USER mgmt_view;

8. You can then create the EM environment with the following command:

emca -config dbcontrol db -repos create

9. Again, you’ll receive the following prompts, enter the Oracle SID, port number and all passwords:

STARTED EMCA at Jul 13, 2008 8:28:48 AM
EM Configuration Assistant, Version 11.1.0.5.0 Production
Copyright (c) 2003, 2005, Oracle.  ALL rights reserved.
 
Enter the following information:
DATABASE SID: orcl
Listener port NUMBER: 1521
Password FOR SYS USER:
Password FOR DBSNMP USER:
Password FOR SYSMAN USER:
Password FOR SYSMAN USER: Email address FOR notifications (optional):
Outgoing Mail (SMTP) server FOR notifications (optional):
-----------------------------------------------------------------
 
You have specified the following settings
 
DATABASE ORACLE_HOME ................ C:\app\Administrator\product\11.1.0\db_1
 
LOCAL hostname ................ new-hostname
Listener port NUMBER ................ 1521
DATABASE SID ................ orcl
Email address FOR notifications ...............
Outgoing Mail (SMTP) server FOR notifications ...............
 
-----------------------------------------------------------------
 
Do you wish TO continue? [yes(Y)/no(N)]: y

When you have a problem with creating the Oracle EM via command line (you’ve got the insufficient privileges error) try to use the Database Configuration Assistant.

That’s should be all.

Advertisements

How to change the character set of the database.

Caution! Changing the character set can sometimes cause data loss or data corruption.
I strongly encourage to make a full backup of the database, before attempting to migrate the data to a new character set.
It is also a good idea to read something about changing character set in the Oracle, here are some useful links:

Anyway, here is the procedure to change the character set of the Oracle database.

1. Check the current database character set.

select value
from nls_database_parameters
where parameter='NLS_CHARACTERSET';

2. Sometimes it could be not enough – here is the query to check ALL database character sets.

select distinct(nls_charset_name(charsetid)) CHARACTERSET,
decode(type#, 1, decode(charsetform, 1, 'VARCHAR2', 2, 'NVARCHAR2','UNKOWN'),
9, decode(charsetform, 1, 'VARCHAR', 2, 'NCHAR VARYING', 'UNKOWN'),
96, decode(charsetform, 1, 'CHAR', 2, 'NCHAR', 'UNKOWN'),
112, decode(charsetform, 1, 'CLOB', 2, 'NCLOB', 'UNKOWN')) TYPES_USED_IN
from sys.col$
where charsetform in (1,2)
and type# in (1, 9, 96, 112);

3. Log into the database and do a clean shutdown of the database.

SHUTDOWN IMMEDIATE;

If for whatever reason, the database does not get shut down cleanly (via a shutdown immediate command), start it back up in restrict mode and shut it down again.

4. Mount the database.

STARTUP MOUNT;

5. Restrict logon to the database, disable job processes and queue processes.

ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;

6. Open the database.

ALTER DATABASE OPEN;

7. Change the character set (instead of &CHARSET use the proper character set, e.g. ‘EE8MSWIN1250’).

ALTER DATABASE CHARACTER SET INTERNAL_USE &CHARSET;

8. You can also change the national character set (instead of &NCHARSET use the proper character set, e.g. ‘AL16UTF16’)

ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE &NCHARSET;

9. Make a clean shutdown of the database.

SHUTDOWN IMMEDIATE;

10. Start it up.

STARTUP;

That’s should be all.

Categories: Oracle, RDBMS Tags: , ,

Coming soon…

Stay tuned 🙂

Categories: Uncategorized