Select name from v$datafile;
Select member from v$logfile;
Select name from v$controlfile;
If troy database running on spfile
Create pfile=/u01/backup/inittroy.ora from spfile;
If database running in pfile using os command to copy the pfile and placed in backup path.
Alter database backup controlfile to trace as /u01/backup/control01.ora;
(Database in windows we need to create the service id using oradim utility)
Using backup controlfile trace to generate the create controlfile scripts.
Change the Database name & files path, also change ‘REUSE’ needs to be changed to ‘SET’.
CREATE CONTROLFILE SET DATABASE “clone” RESETLOGS FORCE LOGGING NOARCHIVELOG
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 453
LOGFILE
GROUP 1 ‘/U01/oradata/clone/redo01.log’ SIZE 200M,
GROUP 2 ‘/U01/oradata/clone/redo02.log’ SIZE 200M,
GROUP 3 ‘/U01/oradata/clone/redo03.log’ SIZE 200M
DATAFILE
‘/U01/oradata/clone/system01.dbf’,
‘/U01/oradata/clone/undotbs01.dbf’,
‘/U01/oradata/clone/users01.dbf’,
CHARACTER SET WE8ISO8859P1;
Note: placed the script in sql prompt. Now controlfile created.
Alter database open resetlogs;
Note: Check the logfile, datafiles & instance status
**********************************************************************************
Database must be in Archive log mode.
Select tablespace_name, file_name from dba_data_files order by 1;
If troy database running on spfile
Create pfile=/u01/backup/inittroy.ora from spfile;
If database running in pfile using os command to copy the pfile and placed in backup path.
Alter database backup controlfile to trace as /u01/backup/control01.ora;
To ensure the begin backup mode tablespace using below query
Select * from v$backup; (refer the Change#, Time column)
To ensure the begin backup mode tablespace using below query
Select * from v$backup;
(Database in windows we need to create the service id using oradim utility)
Change the Database name & files path, also change ‘REUSE’ needs to be changed to ‘SET’.
CREATE CONTROLFILE SET DATABASE “clone” RESETLOGS FORCE LOGGING NOARCHIVELOG
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 453
LOGFILE
GROUP 1 ‘/U01/oradata/clone/redo01.log’ SIZE 200M,
GROUP 2 ‘/U01/oradata/clone/redo02.log’ SIZE 200M,
GROUP 3 ‘/U01/oradata/clone/redo03.log’ SIZE 200M
DATAFILE
‘/U01/oradata/clone/system01.dbf’,
‘/U01/oradata/clone/undotbs01.dbf’,
‘/U01/oradata/clone/users01.dbf’,
CHARACTER SET WE8ISO8859P1;
Note: placed the script in sql prompt. Now controlfile created.
Recover database using backup controlfile until cancel;
Alter database open resetlogs;
Note: Check the logfile, datafiles status.
I Hope this article helped you to understand the cloning oracle database.Suggestions are welcome.