| Is exp/imp the best copy solution? [message #69737] |
Thu, 28 February 2002 00:02  |
Sven
Messages: 10
Registered: July 2001
|
Junior Member |
|
|
Hi all.
I have a delicate issue to handle. Here we have a database with two instances, one production and one test. The thing is that they are now pretty much out of sync and we would like to take a copy of prod into test without stopping it. I've heard that by doing that with RMan I must recreate the init files and it's not sure that the production environment will be unaffected (one rman user).
So, my idea is making an export and import. Is it the best solution and are there any things I have to think of when doing the operation?
Best regards,
Sven
|
|
|
|
| Re: Is exp/imp the best copy solution? [message #69744 is a reply to message #69737] |
Thu, 28 February 2002 16:39  |
seng
Messages: 191
Registered: February 2002
|
Senior Member |
|
|
Here is some issues, which is needed to concern.
1.
EXP/IMP
- The database size. if the database size is very huge then EXP/IMP might need very long time to finish. E.g few days .
- Space, need some space for EXP/IMP file. It might be few G if the database is huge.
- Space constraint. Some of OS has max file size limit.
2.
Other way is using script if you know your prod database structure. In this script. COPY(V8) and CREATE TABLS <> AS SELECT * FROM
are used and you need to create script to duplicate your database. This method, you don't need to concern the Space and Space Constraint. But It take some time, network usage(db link is used) and time to create the script and test.
3. Duplicate is more easy way to have same database as prod database. But your database can't shutdown.
|
|
|
|
| Sorry! [message #69747 is a reply to message #69744] |
Fri, 01 March 2002 02:03 |
Sven
Messages: 10
Registered: July 2001
|
Junior Member |
|
|
SORRY! I was tired this morning and I didn't really understand what you wrote. Now I DO understand... :-)
Thanks,
Sven
|
|
|
|
| Re: Is exp/imp the best copy solution? [message #69751 is a reply to message #69737] |
Fri, 01 March 2002 07:58 |
Grant
Messages: 578
Registered: January 2002
|
Senior Member |
|
|
|
You need to clearify. Do you mean you have one instance/DB with a prod and test schema or do you have two instances/DB one prod and one test. If you have two DBs are they on the same server or seperate servers?
|
|
|
|
|
|
| Re: Is exp/imp the best copy solution? [message #69767 is a reply to message #69751] |
Mon, 04 March 2002 04:51 |
Grant
Messages: 578
Registered: January 2002
|
Senior Member |
|
|
Export/import would be the best way to go. Because the prod and test schemas are in the same database there are a few things to be concrened about. I am sure there are more but I will brief you on how I do without overwriting data in the wrong schema.
1. Be carefull with privileges and quota's. Do not give more privileges than the schema requires to create objects. You can always give privs back when your done. Here is how I create a schema for import:
create user scott identified by tiger
default tablespace users
temporary tablespace temp
quota unlimited on users;
grant connect to scott;
grant create procedure to scott;
grant create trigger to scott;
grant create type to scott;
alter user scott quota 0M on system;
1. I do not give them RESOURCE (which gives then UNLIMITED TABLESPACE) or DBA. All the privileges given with CONNECT and the additional privs given is enough for objects to be created in their schema.
2. Give them quota on the target tablespace. Even if this tablespace has a different name IGNORE=Y will put them in their default tablespace. You do not have to precreate the objects.
3. Make sure you have enough space.
4. Consider not using public/global synonyms.
5. I always export the target user then drop all their objects. I do not drop the user so I do not have to deal with granted privileges. They will be retained.
6. Import the user with fromuser="prod" touser="test" ignore=Y.
7. Once done compile invalid objects and compute statistics.
I use kourne and sql scripts to do all this. If you are using unix send email to me at my hotmail account and I will give you the scripts to do all this.
|
|
|
|
| Re: Is exp/imp the best copy solution? [message #69785 is a reply to message #69744] |
Tue, 05 March 2002 15:54 |
seng
Messages: 191
Registered: February 2002
|
Senior Member |
|
|
You can't find any script for this because your data structure(business logic) might be diffecent. The script must create by someone is very well know the data structure before to the data migration.
Hoe this is helping. Thanks
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
