switching between instances without down time

I am installing a new warehouse. I will be running two instances on the same physical server. I plan to load data into one instance while the other is being used for reporting. At the completion of the load process, I need to point the new user connections to the just loaded instance and allow the old user connections in the reporting database to finish their reports. I need to do this without requiring the a change on either the user desktop or on the application tier (business objects) server. I'm looking for suggestions/ideas on how to best accomplish the task.

Answer Wiki

I assume the connections are straight SQL*NET. If so, each desktop would have its own tnsnames.ora file. At a minimum, these would have to be updated to be made aware of the new instance. The last couple of places I worked had a PC support team with software that could push a new tnsnames.ora to each desktop without having to physically be at each one. I understand Oracle Names might be able to do this more easily, but I’m not that familiar with it, and Oracle is moving away from it anyway.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy

Processing your response...

Discuss This Question: 4 &nbspReplies

There was an error processing your information. Please try again later.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy

You can set up an LDAP server instance which is the replacement for Oracle Names. This will contain the information needed for instance resolution and it will require 2 files on each client to point the client to the LDAP server. Any subsequent instances changes only need to made to the LDAP server. No client updates are necessary.
Definitly a plus as far as client maintenance is concerned especially if you have a large number of clients - whether they be individual PC's or mid tier application servers.

If I understand your requirements, you want a connect string to be "flexible". That is, while the data loads in instance two the string points to instance one and when the load of instance two is finished the same connect string will point to instance two.
If you are using tnsnames.ora for the connection resolution you could use a centralized ifile (anywhere on the same network) that gets pointed to from the client's sqlnet.ora file. Simply change the sid="" value in that centralized ifile. When a new connection is attempted the connect string would be pointing to the changed value. Old connections would continue in the instance they connected to originally. Note however that if they lost their connection and wanted to go back to instance one they would not be able to.
If you are using onames then you would need to unregister the old and re-register the new. This way the connect string will be flexible based on what gets registered.

A quick and dirty might be to change the listener.ora on the server so that it would point to the new instance, as long as your clients connect to a service_name rather than a SID. That might work although I've not tried it.
Otherwise the ifile onames ldap route is the sensible way to do these things.
But if your clients are connecting through a mid tier you could easily change the tnsnames on there, in any case you'd have to restart that tier or at the very least it's connection pools once your new instance is off and running.

Hi,
The scheme of having a listener for each instance works. Have a new listener point to another port than 1521 on each instance. Then only start the listener on the instance you want to allow access. Have the tnsnames contain one entry that points to both instances:
XXX =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = nt000663)(PORT = 1021))
(ADDRESS = (PROTOCOL = TCP)(HOST = nt000137)(PORT = 1021))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XXX)
)
)

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy

Processing your reply...

Ask a Question

Free Guide: Managing storage for virtual environments

Complete a brief survey to get a complimentary 70-page whitepaper featuring the best methods and solutions for your virtual environment, as well as hypervisor-specific management advice from TechTarget experts. Don’t miss out on this exclusive content!

Share this item with your network:

To follow this tag...

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy