<BETA>

The task is to copy an instance (abc) to an instance (xyz).
Let us assume the instance xyz it will reside completely on the mount point /xyz/v001.
This will be done by creating a xyz instance, creating its application tablespaces, and importing the export of abc.

We will assume the Oracle software is already loaded, and that there exists an export of abc, and that:

ORACLE_BASE=/oracle
ORACLE_HOME=/oracle/product/8.1.7

The steps required to complete the task at hand:

• Create instance xyz (See Create Instance)
• Create instance xyz tablespaces from instance abc tablespaces.

Create xyz tablespaces:

We need to generate a script create the tablespaceses and associated files. We can do this with SQL that generates SQL, or with perl. I use perl. In either case, it requires some editing to get things right.

smabc
@ci
spool tb0
select tablespace_name, sum(bytes)/1048576
 from dba_data_files
  group by tablespace_name;
spool off

This creates the file tb0.log.
Edit this file into tb1.log to remove the SYSTEM, RBS, TEMP, USERS, and TOOLS entrys (these tablespaces were created with the creation of the xyz instance) along with the heading and trailing lines.
Run the perl script:

tb1.pl > tb2.sql

Now create the xyz application tablespaces with:

smxyz
@ci
@tb2**

** Note: The tb1.pl script creates a create tablespace(s) sql script. It assumes the target file system to be /xyz/v001. This is unlikely to be the case in real life. Therefore it may be necessary to edit tb2.sql into tb3.sql and hand edit the tablespace files to thier appropriate file directorys. It may also possible to modify tb1.pl to accomplish and/or expedite this task.

The instance xyz is now ready for the import of data.

Import from the dwjv export:

imp sys/xyzdba full=y file=expabc log=impxyz

Home