Upgrading Oracle database from 10g to 11g... what steps need to be followed?

[kgl_m]

So I've got a requirement that I haven't really had to think about until now. The client wants to upgrade their database from Oracle 10g to 11g, and want to know what the steps are to get Cognos working on it seamlessly.

What I'm thinking is...

1. Backup and restore the Content Store schema/database on the new database. This will automatically convert the database and should work seamlessly unless there are some Unicode etc changes in the structure that I'm not aware of. Thoughts? I'm not a DBA so I don't know if its as simple as backing up and restoring a schema to go from a 10g instance to an 11g instance.

2. Change the references in the Cognos Administration data source connections to point to the new database. Test the reports.

Is that all or am I missing something?

[cognostechie]

Which database are you talking about?  The DB used as Content Store or the DB that contains data?

[kgl_m]

Which database are you talking about?  The DB used as Content Store or the DB that contains data?

Both! It is a POC since eventually all the DB's are going to move to 11g.

[cognostechie]

It will depend on how your DBAs do the upgrade. If they want to have both versions running in parellel until a cut-off date and if the server is the same, then  you will have to change the data source connections to point to the new version because the port nos might be different if both versions run on the same machine. The tnsnames.ora would also have different connection strings. This is the file that connects Cognos to Oracle.

For the Content store, you can create a deployment archive from 10g, change the content store connection to point to 11g and then import the archive. This will do the same as backup and restore.

[sir_jeroen]

Don't forget to update the Oracle 10 ojdbc<xx>.jar to Oracle 11 ojdbc16.jar that you copied to the Webapps/lib directory for connecting to the contentstore. It's not required, but advisable!

[kgl_m]

For the Content store, you can create a deployment archive from 10g, change the content store connection to point to 11g and then import the archive. This will do the same as backup and restore.

Thank you for this suggestion. How would one do this, if the backup/restore by the DBA fails?

[kgl_m]

Don't forget to update the Oracle 10 ojdbc<xx>.jar to Oracle 11 ojdbc16.jar that you copied to the Webapps/lib directory for connecting to the contentstore. It's not required, but advisable!

Excellent suggestion, I hadn't thought about this, but they're using the Oracle 10g client to connect to the 11g server so I think the ojdbc.jar version will remain the same for now, yes?

[sir_jeroen]

i would always upgrade to the 11g version.
The ojdbcXX.jar is independent of the installed oracle client (10g).
Ojdbcxx.jar is used by the Application Server (tomcat) to connect  to Oracle.
The oracle client is used by the Report and Batch Report service.

[cognostechie]

Thank you for this suggestion. How would one do this, if the backup/restore by the DBA fails?

You can do the step marked in Blue just before the upgrade process starts (to ensure that you get the most recent Content Store). No need for backup of Content Store in this case but this presumes that you will be using a new DB for Content Store in 11g.

Go to Cognos Administration -> Configuration Tab -> Content Administration from the left pane -> Click 'New Export' icon from the upper right
corner -> Select 'Entire Content Store' and 'User Account Information' from the 2nd screen , follow the rest of the steps by default.

Once the DB has been upgraded, point the Cognos configuration to the new Content Store DB of 11G, restart the Cognos service and then
go to Cognos Administration. Now create a 'New Import' and use the same file that was created during the Export
process. This will bring in the entire Content Store into the new DB of 11 G.

[kgl_m]

Thank you, Reportnet Addict and cognostechie. I appreciate the help.  :)

[sir_jeroen]

In regards to CognosTechie's answer: That solution works great, but your configuration settings (e.g. CSV delimiters, Archive Locations, etc.) aren't copied. There's a advanced setting that you could use to also export configuration settings but I haven't tried that.
The last couple of months i've been doing upgrades where I do a database copy (for oracle: export C8 CStore, import as C10 Cstore) and then upgrade the new database to C10. In this way all config settings are also upgraded.

[vasudev_chavan]

DB schema(Content store DB) migration is the best practice.

Incase you are migrating to different Authentication source you can use the Content administration where the My folder content wont be moved.


best Practice will be to run Internal and external consistency check and report upgrade Jobs once the schema(Content store DB) is moved to new version.