Re: Question on database, schemas and backups

[Maison Mo <mo...@yahoo.fr>]

--- En date de : Ven 28.6.13, Rick Hillegas <ri...@oracle.com> a écrit :
> > For a multi-tenant application, we evaluate the
> multiple databases pattern and single database / multiple
> schemas pattern.
> > Regarding the backup process, it seems that the whole
> database is backed up by the
> SYSCS_UTIL.SYSCS_BACKUP_DATABASE(), thus all schemas in this
> DB : is that correct ?
> > Is it then possible to restore a single schema ?
> I don't believe so. There is no schema-isolating switch
> associated with 
> the restoreFrom connection attribute. It would be an
> interesting problem 
> to work on. It would involve subsetting the data in the
> system catalogs 
> and computing the referential closure of all tables, views,
> constraints, 
> and routines in the target schema. Those objects could
> reference objects 
> in other schemas.

Thank you for this information.
In mycase all schemas would be separated (no shared table, procedure or constraint...), if it can simplify things.
So in order to restore a backup for schema X only, I would need to do things manually :
- restore the backup in a temp DB
- expurge any schema X related object from main DB
- manually "merge" the temp DB schema X objects into the main DB

This does not look very exciting.
Can you imagine another way of proceeding ?

  M.

Re: Question on database, schemas and backups

[Rick Hillegas <ri...@oracle.com>]

On 6/30/13 11:56 PM, Maison Mo wrote:
>
> --- En date de : Ven 28.6.13, Rick Hillegas<ri...@oracle.com>  a écrit :
>>> For a multi-tenant application, we evaluate the
>> multiple databases pattern and single database / multiple
>> schemas pattern.
>>> Regarding the backup process, it seems that the whole
>> database is backed up by the
>> SYSCS_UTIL.SYSCS_BACKUP_DATABASE(), thus all schemas in this
>> DB : is that correct ?
>>> Is it then possible to restore a single schema ?
>> I don't believe so. There is no schema-isolating switch
>> associated with
>> the restoreFrom connection attribute. It would be an
>> interesting problem
>> to work on. It would involve subsetting the data in the
>> system catalogs
>> and computing the referential closure of all tables, views,
>> constraints,
>> and routines in the target schema. Those objects could
>> reference objects
>> in other schemas.
> Thank you for this information.
> In mycase all schemas would be separated (no shared table, procedure or constraint...), if it can simplify things.
> So in order to restore a backup for schema X only, I would need to do things manually :
> - restore the backup in a temp DB
> - expurge any schema X related object from main DB
> - manually "merge" the temp DB schema X objects into the main DB
>
> This does not look very exciting.
> Can you imagine another way of proceeding ?
If the backup is complete (i.e., you don't need to perform rollforward 
recovery on the database after restoring the backup), then you may be 
able to use the foreignViews optional tool in order to siphon data out 
of the backup. For more information on this tool, please see 
http://db.apache.org/derby/docs/10.10/tools/rtoolsoptforeignviews.html

Hope this helps,
-Rick
>    M.
>
>