Recreate Database Using Only Datafile and Logfile

Let’s have look on the steps are used by Oracle DBA  for “Recreate Database Using Only Datafile and Logfile”.
Backup of the control file in trace

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

create password file and service

D:\> orapwd file=D:\oracle\product\10.2.0\db_1\database\PWDdba71.ora password=sys entries=10

D:\> oradim -new -sid dba71 -intpwd sys -maxusers 10 -startmode auto -pfile D:\oracle\product\10.2.0\admin\dba71\pfile\init.ora

set the environment and start the database in nomount

D:\>set oracle_home=D:\oracle\product\10.2.0\db_1
D:\>set oracle_sid=dba71
D:\>sqlplus/nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 30 09:15:02 2010
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> conn sys/sys as sysdba
Connected to an idle instance.

SQL> startup nomount pfile='D:\oracle\product\10.2.0\admin\dba71\pfile\init.ora;
ORACLE instance started.
Total System Global Area  167772160 bytes
Fixed Size                  1247876 bytes
Variable Size              62915964 bytes
Database Buffers           96468992 bytes
Redo Buffers                7139328 bytes

Create the controlfile

SQL> CREATE CONTROLFILE  REUSE SET DATABASE "dba71" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA71\REDO01.LOG'  SIZE 50M,
GROUP 2 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA71\REDO02.LOG'  SIZE 50M,
GROUP 3 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA71\REDO03.LOG'  SIZE 50M
DATAFILE
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA71\SYSTEM01.DBF',
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA71\UNDOTBS01.DBF',
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA71\SYSAUX01.DBF',
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA71\USERS01.DBF',
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA71\TEST02.DBF',
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA71\CHECK01.DBF',
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA71\PART_1.DBF',
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA71\PART_2.DBF',
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA71\PART_3.DBF',
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA71\PART_4.DBF',
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA71\ROW_CHAIN_01.DBF'
CHARACTER SET WE8MSWIN1252;

NOTE- If you want to set the new name of the database then use the following command:

CREATE CONTROLFILE  SET DATABASE "dba51" RESETLOGS ARCHIVELOG

Open the database

SQL> alter database open resetlogs;
Database altered.

Today’s Thought

“You have to act and act now.” – Larry Ellison

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Telegram Channel: https://t.me/helporacle

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp