Coskan’s Approach to Oracle

November 12, 2008

How to downgrade EM Repository

Filed under: Basics, Tips — coskan @ 3:05 pm

Nowadays, I am studying on to upgrade my OCP certificate to 11G. Despite that  none of the excellent data guard enhancements covered,  exam topics are still very satisfactory to cover new features for a DBA of Oracle Database 11G. If you try what you read you can go deep new features too. One of them is the necessity of separate EM Repository backup for a possible downgrade. There is a new tool, under 11gORACLE_HOME/bin directory,  called emdwgrd (enterprise manager downgrade) . It can backup your EM Repository before you upgrade and then restore it to the downgrade if you rollback the upgrade process. If you dont use this tool upgraded EM Repository cannot be used.

Here is how it works.

Before you start the actual upgrade (manual or DBUA) you ran the emdwgrd from new 11gORACLE_HOME/bin directory like below;

emdwgrd -save -sid old_SID -path save_directory

-sid is the SID of the database being upgraded
-path is the directory for the backup of em respository.

Path you choose must be emty otherwise you will get the error I got 🙂

C:\oracle\product\product\11.1.0\db_1\BIN>emdwgrd -save -sid ORACOS -path C:\oracle\product\10.2.0\flash_recovery_area
Enter sys password for database ORACOS?
Error: C:\oracle\product\10.2.0\flash_recovery_area must be an empty directory.

Output of the command ran  is like below

C:\oracle\product\product\11.1.0\db_1\BIN>mkdir C:\oracle\product\10.2.0\flash_recovery_area\em_before_upgrade
C:\oracle\product\product\11.1.0\db_1\BIN>emdwgrd -save -sid ORACOS -path C:\oracle\product\10.2.0\flash_recovery_area\em_before_upgrade
Enter sys password for database ORACOS?
Tue Nov 11 11:19:44 2008 – Verify EM DB Control files … pass
Tue Nov 11 11:19:44 2008 – Validating DB Connection to ORACOS … pass
shared = 0
Tue Nov 11 11:19:49 2008 – Creating directory … created
Tue Nov 11 11:19:50 2008 – Stopping DB Control … stopped
Tue Nov 11 11:20:10 2008 – Saving DB Control files
… saved
Tue Nov 11 11:21:46 2008 – Recompiling invalid objects … recompiled
Tue Nov 11 11:22:46 2008 – Exporting sysman schema for ORACOS … exported
Tue Nov 11 11:25:48 2008 – DB Control was saved successfully.
Tue Nov 11 11:25:48 2008 – Starting DB Control … started
Tue Nov 11 11:26:28 2008 – Dump directory was dropped successfully.

As you see it creates a temporary database directory in the database and dump the file using this directory including sysman schema export. After everything is finished it drops the temp database directory.

Directory structure of the backup  is like below. It has backup and logs directories

Directory of C:\oracle\product\10.2.0\flash_recovery_area\em_before_upgrade12/11/2008 11:39 backup
12/11/2008 11:38 logs
0 File(s) 0 bytes
4 Dir(s) 8,887,455,744 bytes free

In backup directory there are backups files including sysman schema export and copy of sysman and servername_ORACLESID directories related with EM under ORACLE_HOME directory

Directory of C:\oracle\product\10.2.0\flash_recovery_area\em_before_upgrade\backup11/11/2008 11:25 13,000,704 EXPORT.DMP
11/11/2008 11:25 30,461 export.log

11/11/2008 11:21 sysman

In the logs directory you can view the logs of the backup proces

Directory of C:\oracle\product\10.2.0\flash_recovery_area\em_before_upgrade\logs11/11/2008 11:21 115,427 copysave.log
11/11/2008 11:26 132 emctl_startsave.log
11/11/2008 11:20 124 emctl_stopsave.log
11/11/2008 11:22 0 export_dmp.log
11/11/2008 11:26 2,251 sqlsave.log

After this finish you can do your upgrade. Lets assume that you did your upgrade and decide to go back. After you finish the database downgrade steps you can now do post downgrade process for EM Repository. To do this you must first run the emca utility from 11gORACLE_HOME to copy the respository files to new oracle home.

C:\oracle\product\product\11.1.0\db_1\BIN>emca -restore dbSTARTED EMCA at 12-Nov-2008 11:57:06
EM Configuration Assistant, Version Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Enter the following information:
ORACLE_HOME for the database to be restored: C:\oracle\product\10.2.0\db_1
Database SID: ORACOS
Listener port number: 1522
Password for SYS user:
Password for SYS user:
Do you wish to continue? [yes(Y)/no(N)]: coskan12
Invalid input.
Do you wish to continue? [yes(Y)/no(N)]: Y
12-Nov-2008 11:59:42 oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at C:\oracle\product\cfgtoollogs\emca\oracos\emca_2008_11_12_11_57_04.log.
12-Nov-2008 11:59:43 oracle.sysman.emcp.EMDBPreConfig checkRestoreParams
WARNING: EM is not configured for this database. No EM-specific actions can be performed.
Enterprise Manager configuration completed successfully
FINISHED EMCA at 12-Nov-2008 11:59:51

As you see on the warning EM version must still be downgraded. When I connected to EM I can only be able to manage non EM specific actions as mentioned (sorry I couldnt upload the file 😦  proxy issues )

Its time to finish EM Downgrade operation. Command is similar with two difference we need to use -restore option instead of -save and we need to give default temporary tablespace name for sysman schema. If we don’t give temp tablespace it will fail.

emdwgrd -save -sid old_SID -path save_directory -tempTablespace temptablespace

C:\oracle\product\product\11.1.0\db_1\BIN>emdwgrd -restore -sid ORACOS -path C:\oracle\product\10.2.0\flash_recovery_area\em_before
_upgrade -tempTablespace TEMP
Enter sys password for database ORACOS?********
Enter sysman password for database ORACOS?

Wed Nov 12 11:59:59 2008 – Verify EM DB Control files … pass
Wed Nov 12 11:59:59 2008 – Validating DB Connection to ORACOS … pass
Wed Nov 12 12:00:06 2008 – Validating TEMP tablespace in ORACOS … pass
shared = 0
Wed Nov 12 12:00:08 2008 – Creating directory … created
Wed Nov 12 12:00:12 2008 – Stopping DB Control … stopped
Wed Nov 12 12:01:00 2008 – dropping sysman schema … dropped
Wed Nov 12 12:03:51 2008 – Recreating sysman user … recreated
Wed Nov 12 12:03:54 2008 – Restoring DB Control files
… restored
Wed Nov 12 12:03:54 2008 – Importing sysman schema … imported
Wed Nov 12 12:11:17 2008 – Recompiling invalid objects … recompiled
Wed Nov 12 12:11:46 2008 – restoring dbms_jobs … restored
Wed Nov 12 12:11:47 2008 – restoring dbms_registry … restored
Wed Nov 12 12:13:14 2008 – DB Control was restored successfully.
Wed Nov 12 12:13:14 2008 – Starting DB Control … started
Wed Nov 12 12:13:57 2008 – Dump directory was dropped successfully.

As you see sysman schema is dropped and recreated again. All your scheduler jobs will be restored so you don’t need to create them again.

I hope you wont need to downgrade but its nice to know how to.

Upgraded DB: Windows 32
Upgraded DB: Windows 32

References used :
Oracle® Database Upgrade Guide 11g Release 1 (11.1)

FYI: Manual is for linux only but nothing special to linux is on the doc for this operation


Create a free website or blog at