| Refreshing the "development" instance from the "production" instance [message #53012] |
Fri, 23 August 2002 09:07  |
Arpit
Messages: 99
Registered: March 2001
|
Member |
|
|
Hello,
I have two instances "adm" and "dev". One is for the production database and other for the development database.
The development database has old data as it doesn't refresh from the production every often.
How can i refresh my development database from the production database having the same data in both the instances.
Any help on this would be highly appreciated.
Thanks
|
|
|
|
|
|
|
|
| Re: Refreshing the "development" instance from the "production" instance [message #53082 is a reply to message #53012] |
Tue, 27 August 2002 11:48  |
Grant
Messages: 578
Registered: January 2002
|
Senior Member |
|
|
I guess it depends on how large it is. If it is large go with the cloning method.
http://www.samoratech.com/TopicOfInterest/swCloneDB.htm
If smaller I refresh by dropping all objects in the schema(s) or drop the user(s) cascade and do a full export/import.
exp system/manager file=exp_full.dmp log=exp_full.log parfile=exp_full.par
Export Parameter File:
BUFFER=64000
COMPRESS=Y
GRANTS=Y
FULL=Y
INDEXES=Y
Note don't use COMPRESS=Y if using partioning.
copy the file to dev sever with FTP binary mode.
imp system/manager file=exp_full.dmp log imp_full.log parfile=imp_full.par
Import Parameter File:
BUFFER=64000
FULL=Y
IGNORE=Y
COMMIT=Y
INDEXES=Y
ROWS=Y
GRANTS=Y
You can also do schema level export/import which is what I use most of the time.
exp system/manager file=exp_schema.dmp log=exp_schema.log owner=schema
Export User Parameter File:
BUFFER=64000
COMPRESS=Y
GRANTS=Y
INDEXES=Y
ROWS=Y
CONSTRAINTS=Y
ftp the file
drop the target schema objects. Don't drop the user. Keeps all privs, quotas, etc...
imp system/manager file=exp_schema.dmp log=imp_schema.log parfile=imp_user.par fromuser=schema touser=target_schema
replace target schema with your schema name for dev
Import User Parameter File:
BUFFER=64000
GRANTS=Y
INDEXES=Y
IGNORE=Y
ROWS=Y
Here is a script I use to drop schema objects.
rem =========================================================================
rem
rem drop_user_objects.sql
rem
rem Copyright (C) Oriole Software, 1999
rem
rem Downloaded from http://www.oriolecorp.com
rem
rem This script for Oracle database administration is free software; you
rem can redistribute it and/or modify it under the terms of the GNU General
rem Public License as published by the Free Software Foundation; either
rem version 2 of the License, or any later version.
rem
rem This script is distributed in the hope that it will be useful,
rem but WITHOUT ANY WARRANTY; without even the implied warranty of
rem MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
rem GNU General Public License for more details.
rem
rem You should have received a copy of the GNU General Public License
rem along with this program; if not, write to the Free Software
rem Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
rem
rem =========================================================================
rem
rem This script, to be run by a DBA, generates a script to drop
rem all of a user's objects - while retaining the Oracle account with
rem all its privileges. This is especially useful before reimporting
rem data on a per-user basis, for instance to update the contents of
rem a test database.
rem
rem Usage : @drop_user_objects <username>
rem
rem It generates drop_<username>_objects.sql
rem
rem Note that it take cares of dropping only objects which do not
rem depend on other objects (indexes and triggers for instance are
rem dropped with the associated tables, as are package bodies with
rem the associated packages) in order to avoid harmless but irritating
rem error messages. However, no in-depth checking of dependencies is
rem performed.
rem This may lead to a number of misleading error messages :
rem - if you are using clusters (God forbid!) the generated script will
rem try to drop the clustered tables after the cluster is gone.
rem - with Oracle8, 'hidden' tables such as overflow tables for index
rem organized tables will be explicitly dropped after the main
rem table has been dropped.
rem - etc.
rem
rem As a rule, do not worry about '... does not exist' messages
rem
rem Note that, when run, the script which actually drops the objects
rem will generate a spool file.
rem
set pause off
set echo off
set scan on
set verify off
set pagesize 0
set feedback off
set recsep off
set termout off
spool drop_&1._objects.sql
select 'spool drop_&1._objects.log'
from dual
/
select 'drop ' || object_type || ' ' || owner || '.' || object_name
|| decode(object_type,
'CLUSTER', ' including tables cascade constraints;',
'TABLE', ' cascade constraints;',
';')
from dba_objects
where owner = upper('&1')
and object_type in ('CLUSTER', 'TABLE', 'VIEW', 'SEQUENCE', 'SYNONYM',
'FUNCTION',
'PROCEDURE', 'PACKAGE')
/
select 'spool off'
from dual
/
spool off
set feedback on
set pagesize 24
set termout on
prompt Run @drop_&1._objects to drop &1's objects ...
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
