ORACLE DATABASE CHARACTERSET CONVERSION
Pre – Steps
1. Stop the Listener
2. Check for any remote transactions on the database in dba_2pc_pending,if exists purge
3. Check for datafiles in Hotbackup Mode
4. Check for snapshot refreshes in the database
5. Check the database for active connection, make sure all active users are disconnected.
6. Shutdown the database
7. Get the BCV cold backup started by unix team ask them to split the disks manually
8. Check the database every thing goes fine during cold backup
9. Ask storage team to initiate the TSM backup manually
10. Once the TSM Backup started ,Start up the database in restricted mode and start and exp backup(exp userid=”/
as sysdba” file=< location> full=y compress =y log=<log location))
11. Once the exp backup is complete check the log file for errors
12. Connect to the database as sysdba take the o/p of LOB column in user tables and spool it (select distinct OWNER,
TABLE_NAME from DBA_TAB_COLUMNS where DATA_TYPE in (‘CLOB’) and OWNER not in (‘SYS’,’SYSTEM’);
Select name,substr(value$,1,40) from props$; spool it
13. Take a copy of init file, controlfile of the database.
CHARACTERSET CONVERSION
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
ALTER DATABASE CHARACTER SET WE8MSWIN1252;
ALTER DATABASE CHARACTER SET UTF8;
ALTER DATABASE NATIONAL CHARACTER SET SET WE8MSWIN1252;
ALTER DATABASE NATIONAL CHARACTER SET UTF8;
Note: The alter database takes typically only a few minutes or less, it depends on the number of columns in the database, not the amount of data.
SHUTDOWN;
STARTUP RESTRICT;
SHUTDOWN;
STARTUP;
If you get
ORA-12712: new character set must be a superset of old character set
Then use the following command to skip the superset
ALTER DATABASE CHARACTER SET WE8MSWIN1252;
ALTER DATABASE CHARACTER SET INTERNAL_USE UTF8;
ALTER DATABASE NATIONAL CHARACTER SET WE8MSWIN1252;
ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE UTF8;
POST CONVERSION STEPS
1. Take and exp backup of the database in and different name
2. Check the exp log for any error
3. Release the Database for Application users to Test