step by step; copy a schema to a new server; no data

I am a new dba in Oracle;  could you please give me step by step coping database schema no data from one windows server to an other
shlvnAsked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Use export/import(there's new datapump versions depending on your version of Oracle but they require some additional setup):

exp system/manager owner=MYUSERTOCOPY rows=N

then
imp system/manager fromuser=MYUSERTOCOPY touser=NEWUSER
0
 
k_murli_krishnaCommented:
Copying a schema

If you only want to copy a schema to the new server, things are basically the same.

1. Perform a schema export:

exp userid=system/manager file=my_db.dmp log=my_db.log owner=SCOTT rows = N
The OWNER parameter exports a schema. In my example, that would be the SCOTT schema. Again, I also make sure to log the output of my export utility to a log file.
2. FTP the dump file (and log file) to the destination server. Make sure that you FTP in binary mode!

3. Precreate a new database on the destination server.

4. Precreate the tablespaces on the new database to match the same tablespace names of your source database.

5. Precreate the user in that database.

6. Import the dump file:

imp userid=system/manager file=my_db.dmp log=imp.log fromuser=SCOTT
Again, log the output to a file in case there are errors. The FROMUSER clause tells imp which schema to import. If you wish to change the objects to a new owner, use the TOUSER clause as well.
0
 
sixers_87Commented:
Just install Oracle on new server, without the database creating
Copy all data files, redo logs and control files when database is read-only
put all the data on new serwer with the same path it was
and create process
oradim -new -sid <SID_NAME> -intpwd <SYS_PASS> -startmode a –pfile <PFILE_PATH>
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
sdstuberCommented:
if you have 10g I recommend using dbms_datapump

just create a db_link from the destination db to the source db with a privileged account and run a script like this
DECLARE
    v_handle NUMBER;
BEGIN
    v_handle :=
        DBMS_DATAPUMP.
        open(
            operation     => 'IMPORT',
            job_mode      => 'SCHEMA',
            remote_link   => 'source_db__link'
        );

    --  put the schemas in quotes
    DBMS_DATAPUMP.
    metadata_filter(
        handle   => v_handle,
        name     => 'SCHEMA_LIST',
        VALUE    => '''SCHEMA1'',''SCHEMA2'''
    );

    --  remove this line if you want the data as well
    -- there is a bug, if you set the value to non 0 it will still exclude the rows
    -- just remove the call completely if you want the data
    DBMS_DATAPUMP.
    data_filter(handle => v_handle, name => 'INCLUDE_ROWS', VALUE => 0);

    DBMS_DATAPUMP.add_file(
        handle      => v_handle,
        filename    => 'your_log_file_name_here.log',
        directory   => 'DATA_PUMP_DIR',
        filetype    => 3
    );

    DBMS_DATAPUMP.set_parallel(v_handle, 4);

    DBMS_DATAPUMP.start_job(v_handle);
END;
/

Open in new window

0
 
jiruizCommented:
If your Oracle is 9i use de slightwv: example. If your two database are Oracle 10g, use better datapump

expdp system/manager DIRECTORY=my_dir DUMPFILE=expdp_s.dmp LOGFILE=expdp_s.log SCHEMAS=scott exclude=statistics

The option EXCLUDE only for performce issues

and the import in the other databasse

impdp system/manager DIRECTORY=my_dir DUMPFILE=expdp_s.dmp \
LOGFILE=impdp_s.log REMAP_SCHEMA=scott:scott1

The option REMAP_SCHEMA is neccesary if you has to change the name of the user.
0
 
slightwv (䄆 Netminder) Commented:
The reason I only mentioned datapump is because of the directory creation.  It is an additional step.  Unless the schema is huge, the old utilities will work just fine.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.