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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.