You can recover the database in case if have last all control files.

Data used by Recovery Manager will be lost If you were using the control file as the RMAN repository.

Assuming you are doing this on Windows, the files will be locked. This is the directory snap where your datafile, controlfile and redolog files are residing.

sc_BackupGames2_image001

The control files will be renamed and then a startup will be attempted.

-- Clean shutdown to release the locks on the files
SQL> conn sys/oracle AS sysdba
SQL> shutdown immediate

-- Rename all control files using OS commands
--Startup issued with the control files missing

SQL> startup
ORA-00205: error IN identifying control file,CHECK alert log FOR more info

Now we can treat this situation as having to create control files. If the control files are missing, then the database cannot be mounted, Any attempt to mount will fail because of the missing files.

SQL>SELECT STATUS FROM v$instance;
STATUS
------------
STARTED
SQL>ALTER DATABASE mount;
ERROR at line 1:
ORA-00205: error IN identifying control file,CHECK alert log FOR more info

No problem, we’ll use the command to create a control file from trace.

SQL>ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS 'C:\TRACE.TXT'; 
ERROR at line 1: ORA-01507: DATABASENOT mounted 

As you can see, not being mounted is a show stopper for this approach. What is required is to manually create a statement. We need the names of all of the datafiles and redolog files in the database for this step (see the first diagram).

We have to match the redo log filename to the redo log group. That can be tricky because with N groups, there will be N! ways of making those assignments.

Using what Windows is reporting as the file sizes (in KB), we can construct a CREATE CONTROLFILE statement as shown:

CREATE CONTROLFILE 
       REUSE --Existing data files will be used
       DATABASE"DEVTEST" --Name of the DB
       NORESETLOGS --Existing log files will be used
       ARCHIVELOG --Database was in archiveing mode
MAXLOGFILES 16 --A Typical value of oracle 10g
MAXLOGMEMBERS 3 --A Typical value of oracle 10g
MAXDATAFILES 100 --A Typical value of oracle 10g
MAXINSTANCES 8 --A Typical value of oracle 10g
MAXLOGHISTORY 292 --A Typical value of oracle 10g

LOGFILE
GROUP1'C:\oracle_data_files\DEVTEST\redo01.log' SIZE 50M,
GROUP2'C:\oracle_data_files\DEVTEST\redo02.log' SIZE 50M,
GROUP3'C:\oracle_data_files\DEVTEST\redo03.log' SIZE 50M

DATAFILE
'C:\oracle_data_files\DEVTEST\EXAMPLE01.DBF',
'C:\oracle_data_files\DEVTEST\SYSAUX01.DBF',
'C:\oracle_data_files\DEVTEST\SYSTEM01.DBF',
'C:\oracle_data_files\DEVTEST\UNDOTBS01.DBF',
'C:\oracle_data_files\DEVTEST\USERS01.DBF' ;

If get the error look like this you need to adjust redo log or datafile sizes

ORA-01503: CREATE CONTROLFILE failed 
ORA-01163: SIZE clause indicates 9921(blocks), but should match header 9920
ORA-01110: DATA file 4: 'D:\oracle\product\10.2.0\oradata\db10\users01.dbf'

--[For Datafiles]---
SQL>SHOW parameter db_block --get the DB block size default is 8192 in 10g

Expected size = Expected # of blocks * db_block_size / 1024
9920*8192/1024= 79360K

--[For RedoLog files]---
Expected size = Expected # of blocks * 512 / 1024
9920*512/1024= 4960K

Now that the control file(s) has been created, what is the state of the database? Verify that your files have been created, and then select the status from V$INSTANCE and see MOUNTED. If the database is mounted, can it be opened? The answer is yes.

SQL>ALTER DATABASE MOUNT; 
SQL>ALTER DATABASE open;

You can notice there is no temp file associated with database we need to add temp file.

ALTER TABLESPACE "TEMP" 
ADD TEMPFILE 'C:\ORACLE_DATA_FILES\DEVTEST\TEMP01'
SIZE 20M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 500M;

Your DB is now opened and full functional.

 

Go to top
JSN Boot template designed by JoomlaShine.com