We have a challenge in getting clean Image copy for all the test tablespaces in our test environment. Developers are running LOAD REPLACE and leaving many tablespaces on Copy pending and Check Pending states. Due to which our backup jobs are often failing with -904, Resource unavailable error. We are trying to set up something that identifies only the tablespaces with COPY pending and CHECK pending states and clear them because in our environment they are often caused by LOAD REPLACE.
I looked at Repair utility but that needs manual intervention to identify the tablespaces in restricted mode to use REPAIR SET command.
Does any of have run into similar situation? if so, could you please provide some ideas to solve my issue? We are trying to run Full copy twice in a week.
I believe the best thing is to avoid doing what is causing the problem.
We are trying to set up something that identifies only the tablespaces with COPY pending and CHECK pending states and clear them
IMHO, this is not a good idea. Suppose, you were able to set up something like that and cleared all the copy/check pending status. But then, the very next second some user/developer runs the LOAD REPLACE and put the table in copy/check pending status. The purpose of setting up a procedure (to clear the pending status) will fail the very next second.
I see what you are saying. The whole purpose of this is to get a good backup. We will run backup during non business hours and we believe developers won't be available at that time. Currently our dev environment does not have a backup system at all and that worries us even more.