Copy the source database oraprd into a target test database oratest (created beforehand). The copy stops oratest and replaces its files with oraprd’s, then makes them take effect.
1ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
This writes a trace file into user_dump_dest. The relevant part looks like:
1STARTUP NOMOUNT
2CREATE CONTROLFILE REUSE DATABASE "oraprd" NORESETLOGS ARCHIVELOG
3MAXLOGFILES 5
4MAXLOGMEMBERS 3
5MAXDATAFILES 100
6MAXINSTANCES 1
7MAXLOGHISTORY 908
8LOGFILE
9 GROUP 1 'G:\ORACLE\ORADATA\oraprd\REDO01.LOG' SIZE 10M,
10 GROUP 1 'G:\ORACLE\ORADATA\oraprd\REDO02.LOG' SIZE 10M,
11 GROUP 2 'F:\ORACLE\ORADATA\oraprd\REDO03.LOG' SIZE 10M,
12 GROUP 2 'F:\ORACLE\ORADATA\oraprd\REDO04.LOG' SIZE 10M
13DATAFILE
14 'F:\ORACLE\ORADATA\oraprd\SYSTEM01.DBF',
15 'F:\ORACLE\ORADATA\oraprd\CWMLITE01.DBF',
16 'F:\ORACLE\ORADATA\oraprd\DATA\DATPRD.DBF',
17 ... (the whole list of datafiles)
18CHARACTER SET WE8MSWIN1252
19;
20
21RECOVER DATABASE
22ALTER SYSTEM ARCHIVE LOG ALL;
23ALTER DATABASE OPEN;
24ALTER TABLESPACE TEMP ADD TEMPFILE 'G:\ORACLE\ORADATA\oraprd\TEMP02.DBF' SIZE 2000M REUSE AUTOEXTEND OFF;
Once oraprd’s files are copied over oratest, adapt the control-file script to the new paths (e.g. F:\ORACLE\ORADATA\oraprd and G:\... โ D:\ORACLE\ORADATA\oratest), and change the database name:
1-- CREATE CONTROLFILE REUSE DATABASE "oraprd" NORESETLOGS ARCHIVELOG
2-- becomes
3CREATE CONTROLFILE SET DATABASE "oratest" RESETLOGS ARCHIVELOG
Comment the RECOVER DATABASE; line (the source was shut down cleanly, no recovery needed), and open with RESETLOGS:
1-- ALTER DATABASE OPEN; becomes
2ALTER DATABASE OPEN RESETLOGS;
Final script (shortened):
1STARTUP NOMOUNT
2CREATE CONTROLFILE SET DATABASE "oratest" RESETLOGS ARCHIVELOG
3MAXLOGFILES 5
4MAXLOGMEMBERS 3
5MAXDATAFILES 100
6MAXINSTANCES 1
7MAXLOGHISTORY 908
8LOGFILE
9 GROUP 1 'D:\ORACLE\ORADATA\oratest\REDO01.LOG' SIZE 10M,
10 GROUP 1 'D:\ORACLE\ORADATA\oratest\REDO02.LOG' SIZE 10M,
11 GROUP 2 'D:\ORACLE\ORADATA\oratest\REDO03.LOG' SIZE 10M,
12 GROUP 2 'D:\ORACLE\ORADATA\oratest\REDO04.LOG' SIZE 10M
13DATAFILE
14 'D:\ORACLE\ORADATA\oratest\SYSTEM01.DBF',
15 'D:\ORACLE\ORADATA\oratest\DATA\DATPRD.DBF',
16 ... (the same list, adapted to the new paths)
17CHARACTER SET WE8MSWIN1252
18;
19
20ALTER DATABASE OPEN RESETLOGS;
21ALTER TABLESPACE TEMP ADD TEMPFILE 'D:\ORACLE\ORADATA\oratest\TEMP02.DBF' SIZE 2000M REUSE AUTOEXTEND OFF;
Finally, if the init file was also copied, rename initORAPRD.ora โ initORATEST.ora and update: db_name, control_files, user_dump_dest, background_dump_dest, core_dump_dest, utl_file_dir. Also shrink the database and disable ARCHIVELOG if not needed.
Connect AS SYSDBA and run the script:
1sqlplus "/ as sysdba"
2SQL> @control_file_oratest.sql
3SQL> shutdown immediate
4SQL> startup
On Windows (as a service):
1net start OracleServiceoratest
2sqlplus "/ as sysdba"
3SQL> @control_file_oratest.sql
4SQL> shutdown immediate
5SQL> startup
It takes ~30 minutes (excluding the file copy); check alert_oratest.log to confirm everything is operational.