-- 11.2 Cloning or renaming a database in Windows

-- 1. Prep work
-- 1.1 Generate a control file trace (text format)
       Logon to source db and run:
       alter database backup controlfile to trace;
       
       This will generate a control file trace in the diag directory that you will later use to recreate the
       control files for the target database.
       
-- 1.2 Generate the pfile from the source database:
       Logon to source db and run:
       create pfile from spfile;
       
-- 1.2 Copy the source database (needs to be shutdown) or a cold backup to the target
       Do not copy the source control files as they are not required
       
-- 2. Create the target database
-- 2.1 Create the windows Oracle service for the target
set ORACLE_SID=DBTARGET
mkdir F:\admin\%ORACLE_SID%
mkdir F:\admin\%ORACLE_SID%\adump 
mkdir F:\oracle\ORADATA\%ORACLE_SID%
mkdir F:\admin\%ORACLE_SID%\scripts 
oradim.exe -new -sid %ORACLE_SID% -startmode manual -spfile  
oradim.exe -edit -sid %ORACLE_SID% -startmode auto -srvcstart system     

-- 2.2 Copy the init.ora (in ORACLE_HOME/database) from the source system to the target.  
       Modify the init.ora with the new target DBNAME and adjust other parameters.
       Start the target database in NOMOUNT with the target pfile:
       set ORACLE_SID=DBTARGET
       sqlplus / as sysdba
       STARTUP NOMOUNT pfile=?\database\initDBTARGET.ora
       
-- 2.3 Recreate the control file on the target, extract the CREATE CONTROLFILE from the trace generated in step 1.1
-- Replace the source dbname with the target dbname
-- Change the CREATE CONTROLFILE statement to the following, remove REUSE, add SET and change NORESETLOGS to RESETLOGS
-- If the target database is to be in archive log specify ARCHIVELOG otherwise specify NOARCHIVELOG
CREATE CONTROLFILE SET DATABASE DBTARGET RESETLOGS NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 584
LOGFILE
  GROUP 1 (
    'D:\ORADATA\DBTARGET\REDO01A.LOG',
    'D:\ORADATA\DBTARGET\REDO01B.LOG'
  ) SIZE 1024M,
  GROUP 2 (
    'D:\ORADATA\DBTARGET\REDO02A.LOG',
    'D:\ORADATA\DBTARGET\REDO02B.LOG'
  ) SIZE 1024M,
  GROUP 3 (
    'D:\ORADATA\DBTARGET\REDO03A.LOG',
    'D:\ORADATA\DBTARGET\REDO03B.LOG'
  ) SIZE 1024M
-- STANDBY LOGFILE
DATAFILE
  'D:\ORADATA\DBTARGET\SYSTEM01.DBF',
  'D:\ORADATA\DBTARGET\UNDOTBS01.DBF',
  'D:\ORADATA\DBTARGET\SYSAUX01.DBF',
  'D:\ORADATA\DBTARGET\USERS01.DBF',
  'D:\ORADATA\DBTARGET\APPDATA01.DBF',
  'D:\ORADATA\DBTARGET\TOOLS01.DBF'
CHARACTER SET AL32UTF8
;
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE 'D:\ORADATA\DBTARGET\TEMP01.DBF'
     SIZE 3665M REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

-- 2.3 Generate the spfile and activate it
sqlplus / as sysdba
create spfile FROM pfile;
shutdown immediate;
startup ;