Search
Navigation
Recent blog posts
- Oracle IN Condition and Multi Column Subqueries
- SQL For Dinosaurs
- Quickie script to run dbv on your database...
- Permissions for Autotrace
- Accuracy of PROFILER.SQL from Metalink Doc: 243755.1
- Recording Oracle System Stats for historical analysis...
- Shell Script to Run a SQLPLUS against all databases running on a server...
- Viewing command line args with Solaris ps utility...
- Orion IO Test Tool
- Documented Hints available in 11.2...
Books
User login
Copying Databases
dmann — Thu, 01/21/2010 - 21:06
1) Starting with a Cold Backup, copy files to new destination.
Including datafiles, control files, temp files, online redo logs.
2) Set up admin directory
You can start with a copy of the original DB's admin directory if that makes things easier for you.
3) Set up $ORACLE_HOME/dbs
3a) init file
3b) password file
4) Startup Mount and Make Controlfile Changes
If at any time you need to see the contents of the controlfile and your database is in MOUNT mode, this command will write a text version of the controlfile to /udump:
STARTUP MOUNT; ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
4a) Rename datafiles
You can get the old files and paths from the Controlfile Backup or if you have access to the source database you can use a statement liek this to generate the statements for you.
select 'ALTER DATABASE RENAME FILE '''||FILE_NAME||''' to '''|| translate(FILE_NAME, 'ivr92prd','ivr92old')||''';' FROM dba_data_files;
4b) Rename Redo Log Files
You can get the old files and paths from the Controlfile Backup or if you have access to the source database you can use a statement liek this to generate the statements for you.
select 'ALTER DATABASE RENAME FILE '''||member||''' to '''|| translate(member, 'ivr92prd','ivr92old')||''';' from v$logfile;
4c) Start up database, create new temp tablespace from scratch
CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/u02/oradata/ivr92old/temp01.dbf' SIZE 2096896K AUTOEXTEND ON NEXT 8K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K;
Now you have all files ready to go and have a working database.
5) Rename the database
nid target=sys/password dbname=newname