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
| C:\oracle\product\10.2.0\flash_recovery_area\em_before_upgrade>dir 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
| C:\oracle\product\10.2.0\flash_recovery_area\em_before_upgrade\backup>dir 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
| C:\oracle\product\10.2.0\flash_recovery_area\em_before_upgrade\logs>dir 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 11.1.0.5.0 Production Copyright (c) 2003, 2005, Oracle. All rights reserved. Enter the following information: |
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? 2******* |
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: 10.2.0.4 Windows 32
Upgraded DB: 11.1.0.6 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
