Large queries on a standby database

[GERPAT]

I'm have a standby database running Oracle 8.1.7. When I run a large query or do any sorting I get the following error message:

ORA-01647: tablespace 'TEMP' is read-only, cannot allocate space in it

This is because the standby database is in read only mode and queries running on a read only database cannot use disk space for sorting.

Its not practical for me to eliminate disk based sorting altogether, so what do I do? Any ideas?

 

[Turkbear]

Hi,
Why is the sorting running on the 'standby' database?

 

[GERPAT]

I just wanted to test that my web application works ok on the standby server even though its only read only.

Do you mean why is the sorting taking place on disk? This is because there is not enough resources in memory.

 

[karluk]

This issue is discussed in Metalink note 67488.1. The workaround in Oracle 8i is to create a locally managed temporary tablespace using a tempfile for storage. This has to be done on the primary database so that it gets duplicated on the standby db. Then make this the temporary tablespace of all users who will be doing disk sorts. Create the standby database and ON THE STANDBY alter the tablespace to add a tempfile. Then you will be able to do disk sorts on the standby. The basic idea is that the locally managed tablespace is not aware of the read-only nature of the database.

This is just an outline of what needs to be done. Please see the Metalink note for details.

 

[GERPAT]

I've come across this solution before but I have reservations for two reasons:

First of all I'm a bit nervous about placing all my users in a temporary tablespace. I know what the pros are for doing this but not the cons.

Also if I use this method I will have to separate out my users, one set of users for the primary database and another for the standby. I could do this but it sounds a bit messy.

Can anybody set my mind at ease?

 

[carp]

I'm not sure about your concerns.

By definition, all of your users will have a temporary tablespace defined; if you don't specify which tablespace to use, Oracle will assume you meant for them to use the SYSTEM tablespace - which, of course, you DON'T want them to use! So it makes sense to explicitly set a temporary tablespace for them.

Regarding your second concern - segregating who uses which database. Won't you have to do this anyway?