Dear Christopher,
Here is another way to move from one database to another not using export
and import utilities.
There about ten steps of them and they are good for databases with
archivelog mode on.
Assume: 1. Archive on for both databases 2.log_archive_format are the same
Steps:
- In your Production, there is no need of any shutdown, do a
alter database backup controlfile to trace;
- Locate the file and modify it. The file should look like the following
CREATE CONTROLFILE SET DATABASE "AWT1" RESETLOGS ARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXDATAFILES 32
MAXINSTANCES 16
MAXLOGHISTORY 1815
LOGFILE
GROUP 1 'C:\ORACLE\ORADATA\AWT1\REDO03.LOG' SIZE 1M,
GROUP 2 'C:\ORACLE\ORADATA\AWT1\REDO02.LOG' SIZE 1M,
GROUP 3 'C:\ORACLE\ORADATA\AWT1\REDO01.LOG' SIZE 1M
DATAFILE
'C:\ORACLE\ORADATA\AWT1\SYSTEM01.DBF',
'C:\ORACLE\ORADATA\AWT1\RBS01.DBF',
'C:\ORACLE\ORADATA\AWT1\USERS01.DBF',
'C:\ORACLE\ORADATA\AWT1\TEMP01.DBF',
'C:\ORACLE\ORADATA\AWT1\TOOLS01.DBF',
'C:\ORACLE\ORADATA\AWT1\INDX01.DBF',
'C:\ORACLE\ORADATA\AWT1\DR01.DBF',
'C:\ORACLE\ORADATA\AWT1\MISC01.DBF'
CHARACTER SET WE8ISO8859P1
;
- Rename the file to create_controlfile.sql
- Shutdown your target database where you want to move the production
database to. Call it test
- Delete all the database files for test eg. all *.dbf, *.ctl, *.rdo.
Delete also all the archive logs.
- Run hotbackup.sql at production. Example of hotbackup.sql should be
as follow:
alter tablespace users begin backup;
!copy c:\oracle\oradata\awt\users01.dbf c:\oracle\oradata\awt1\users01.dbf
alter tablespace users end backup;
alter tablespace drsys begin backup;
!copy c:\oracle\oradata\awt\dr01.dbf c:\oracle\oradata\awt1\dr01.dbf
alter tablespace drsys end backup;
alter tablespace tools begin backup;
!copy c:\oracle\oradata\awt\tools01.dbf c:\oracle\oradata\awt1\tools01.dbf
alter tablespace tools end backup;
alter tablespace indx begin backup;
!copy c:\oracle\oradata\awt\indx01.dbf c:\oracle\oradata\awt1\indx01.dbf
alter tablespace indx end backup;
alter tablespace rbs begin backup;
!copy c:\oracle\oradata\awt\rbs01.dbf c:\oracle\oradata\awt1\rbs01.dbf
alter tablespace rbs end backup;
alter tablespace temp begin backup;
!copy c:\oracle\oradata\awt\temp01.dbf c:\oracle\oradata\awt1\temp01.dbf
alter tablespace temp end backup;
alter tablespace system begin backup;
!copy c:\oracle\oradata\awt\system01.dbf c:\oracle\oradata\awt1\system01.dbf
alter tablespace system end backup;
alter tablespace misc begin backup;
!copy c:\oracle\oradata\awt\misc01.dbf c:\oracle\oradata\awt1\misc01.dbf
alter tablespace misc end backup;
7. copy all logs from production to test.
8. startup nomount for test and run create_controlfile.sql
9. Do a recover database using backup controlfile until cancel.
Note: To proceed the recovery, press enter, to cancel type CANCEL. At this
point if you want to get the current log from production, do a
alter system switch logfile and copy that log into test archiving directory
and type 'recover database blah...' again. Make sure you do it in startup
mount. Once you open the database with resetlogs, you cannot go back to
apply the most current log any longer
- Alter database open resetlogs;
Note: all of these steps should be incorporated into a script.
Allan W. Tham
DBA
Received on Thu Sep 06 2001 - 00:57:06 CDT
