Re: recovering a subset database
From: Michael Austin <maustin_at_firstdbasource.com>
Date: Mon, 09 Feb 2009 08:28:46 -0600
Message-ID: <B4Xjl.10133$hc1.4885_at_flpi150.ffdc.sbc.com>
vick wrote:
> I have problem creating a subset of a 10g database including the
> SYSTEM tablespace on the same server as the source database.
>
> On source database, I have the following tablespaces:
> system, undots, users, sysaux, data1, data2
>
> On the new subset database, I want only the following tablespaces:
> system, undots, users, sysaux
>
>
> On source database (host A),
> 1. alter database begin backup.
> 2. copied datafiles for system,undots,users,sysaux,redo to the new
> mount points
> 3. alter database end backup.
> 4. alter database backup control to trace
> 5. modified the create backup control file script to set the new
> database name, specified openresetlog and removed the datafile entries
> for data1 and data2
> 6. modified the init.ora for the new database
>
>
> Still on host A, create the new database,
> 1. ran the backup control file script to startup the database nomount
> and created the control file for the subset database.
> 2. Next I issued, "recover database using backup controlfile until
> cancel". It prompted for a non-existent archived log. When I entered
> "cancel" and then "alter database open resetlogs", it complained that
> file 1 system tablespace need more media recovery.
>
> Did I miss any steps?
>
> Thanks.
>
Date: Mon, 09 Feb 2009 08:28:46 -0600
Message-ID: <B4Xjl.10133$hc1.4885_at_flpi150.ffdc.sbc.com>
vick wrote:
> I have problem creating a subset of a 10g database including the
> SYSTEM tablespace on the same server as the source database.
>
> On source database, I have the following tablespaces:
> system, undots, users, sysaux, data1, data2
>
> On the new subset database, I want only the following tablespaces:
> system, undots, users, sysaux
>
>
> On source database (host A),
> 1. alter database begin backup.
> 2. copied datafiles for system,undots,users,sysaux,redo to the new
> mount points
> 3. alter database end backup.
> 4. alter database backup control to trace
> 5. modified the create backup control file script to set the new
> database name, specified openresetlog and removed the datafile entries
> for data1 and data2
> 6. modified the init.ora for the new database
>
>
> Still on host A, create the new database,
> 1. ran the backup control file script to startup the database nomount
> and created the control file for the subset database.
> 2. Next I issued, "recover database using backup controlfile until
> cancel". It prompted for a non-existent archived log. When I entered
> "cancel" and then "alter database open resetlogs", it complained that
> file 1 system tablespace need more media recovery.
>
> Did I miss any steps?
>
> Thanks.
>
If you have sufficient space - you can use the 10g clone feature and then drop the objects you don't want. Much easier.
In the current system tablespace is metadata for the tablespaces which you did not copy. These are missing. You cannot just copy "part" of a database in this manner without a whole lot of pain.
create a shell (empty) database
Use EXP/IMP to create copy the data files/data you need.
Received on Mon Feb 09 2009 - 08:28:46 CST