Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle Data Recovery - LONG

Re: Oracle Data Recovery - LONG

From: Paul Drake <paled_at_home.com>
Date: Sun, 19 Aug 2001 19:58:02 GMT
Message-ID: <3B801A31.D1CFE9A6@home.com> 





Simon Gottesman wrote:


> 

> > as you could have mounted the database after re-installing the oracle

> > binaries, created a service, etc.

> 

> Out of curiosity, can you elaborate on the "etc." part of this equation?  I

> would be very interested to learn, for future reference, how to perform this

> type of recovery in the correct manner.

> 




Sure. Please understand that I am writing this from memory, and am not
performing this as we go.


You'll have to test this out for yourself, but I believe that I've
covered everything in detail.


I'm sketchy on 8.0.5 - as my environment went straight from 7.3.4 to
8.1.6.


My directory structure is for Oracle8i - not 8. Sorry about that.
Most likely, you oracle_home was C:\Orant.
Might as well get it ready for 8.1.7 - as the 8.0 series is pretty much
done. 


8.0.6 on NT is still supported, but not for more than another month.
You might want to install the 8.0.5 binaries into C:\Oracle\Ora805.



Lets assume that you have the following:



RAID 1 mirrored set

C:	operating system - NT or W2K
	oracle binaries, admin directory

RAID 5 volume
D:	Oracle control files, log files and data files	(yes, its awful to


have logs on RAID 5. We'll move those.)



ORACLE_SID=PROD



ORACLE_HOME=C:\Oracle\Ora81


Further assume that the RAID 1 volume was lost, and had to be rebuilt.
The operating system is re-installed, as no backup was available (tsk
tsk).


The oracle binaries were installed and patched to the same level as
before.


All files on D: are available - none were overwritten as the orignal
poster stated.


The final steps vary greatly depending upon whether a valid control file
is available or not.


As the %ORACLE_BASE%\admin\PROD\udump directory was lost - we'll assume
that no backup controlfile was created to trace.



    
  
  1.   create a password file
C:\>orapwd file=C:\Oracle\Ora81\Database\pwdPROD.ora password=timmason
entries=5






Usage: orapwd file=<fname> password=<password> entries=<users>



  where


    file - name of password file (mand),
    password - password for SYS and INTERNAL (mand),
    entries - maximum number of distinct DBA and OPERs (opt),
  There are no spaces around the equal-to (=) character.



2. create a service


C:\>oradim -NEW -SID PROD -INTPWD timmason -STARTMODE m -PFILE
C:\Oracle\Admin\PROD\pfile\initPROD.ora



ORADIM: <command> [options].  Please refer to the manual.
Please enter one of the following command:
  Create an instance by specifying the following parameters:
    -NEW -SID sid | -SRVC service [-INTPWD password] [-MAXUSERS number]
[-STARTM



ODE a|m] [-PFILE file] [-TIMEOUT secs]


  Edit an instance by specifying the following parameters:
 -EDIT -SID sid [-NEWSID sid] [-INTPWD passwd] [-STARTMODE a|m] [-PFILE
file] [-


SHUTMODE a|i|n] [-SHUTTYPE srvc|inst]


  Delete instances by specifying the following:
    -DELETE -SID sid | -SRVC service name
  Startup services and instance by specifying the following parameters:
    -STARTUP -SID sid [-USRPWD password] [-STARTTYPE
srvc|inst|srvc,inst] [-PFIL


E filename]


  Shutdown services and instance by specifying the following parameters:
    -SHUTDOWN -SID sid [-USRPWD password] [-SHUTTYPE
srvc|inst|srvc,inst] [-SHUT


MODE a | i | n]


  Query for help by specifying the following parameters: -? | -h | -help




3. create an ifile pointer to the init.ora file
echo IFILE='C:\Oracle\Admin\PROD\pfile\initPROD.ora' >
C:\Oracle\Ora81\Database\initPROD.ora



4. create an initPROD.ora file - as the old one was lost
copy D:\Oracle\Ora81\admin\sample\pfile\initsmpl.ora
D:\Oracle\Admin\PROD\pfile\initPROD.ora


open the file in notepad and replace the occurrences of %AVAILABLE_SID%
with 'PROD'.


edit the path of the control files:



control_files=('C:\Oracle\Oradata\PROD\control01.ctl','D:\Oracle\Oradata\PROD\control02.ctl')



replace the occurrences of %ORACLE_HOME% for bdump and udump with their
OFA locations:


background_dump_dest=C:\Oracle\Admin\PROD\bdump
user_dump_dest=C:\Oracle\Admin\PROD\udump



set the parameters: df_files, db_block_size, compatible, processes,
log_buffer, shared_pool_size, db_block_buffers.
with minimal values


e.g:


db_files=64


db_block_size=8192


compatible=8.0.5.0.0


processes=250


log_buffer=163840

shared_pool_size=67108864	# 64 MB
db_block_buffers=8192		# 64 MB
large_pool_size=1048576		# 1 MB





save this and exit notepad.



5. Networking



Check that the file C:\Oracle\Ora81\Network\Admin\listener.ora exists,
or use Net*8 Assistant to create one.


Check that the file tnsnames.ora exists, and that a valid entry exists
for the database name.



C:\> net start OracleTNSListener



If no listener exists, type the following:



C:\> lsnrctl start listener



A service named "OracleTNSListener" should be created. 



start the oracle service



C:\> net start OracleServicePROD




6. if you have a valid controlfile, copy it to the destinations
described in the initPROD.ora file "control_files".
make sure that the file name and paths are valid.



rem does not have online redo logs


startup nomount


alter database mount


rem check to see that the file_names are valid and datafiles were
healthy


select file#,rpad(name,50),status,enabled,checkpoint_change#
  from v$datafile;


recover database until cancel;  -- this is to force recovery such that a
resetlogs does not fail.


cancel


alter database open resetlogs;



NOTE- if you had the online redo logs and a valid controlfile, recovery
is most likely not needed:



rem has online redo logs


startup nomount


alter database mount


rem check to see that the file_names are valid and datafiles were
healthy


select file#,rpad(name,50),status,enabled,checkpoint_change#
  from v$datafile;


alter database open noresetlogs;




7. if you do not have a control file - create a new one (here we're
creating new redo logs on C:)


# The following commands will create a new control file and use it
# to open the database.
# Data used by the recovery manager will be lost. Additional logs may
# be required for media recovery of offline data files. Use this
# only if the current version of all online logs are available.
# NOTE: Assuming here that the online redo logs were lost
# save this file as create_new_controlfile.sql and execute it in svrmgrl


STARTUP NOMOUNT



CREATE CONTROLFILE REUSE DATABASE "PROD" RESETLOGS NOARCHIVELOG

    MAXLOGFILES 16



    MAXLOGMEMBERS 2



    MAXDATAFILES 64



    MAXINSTANCES 1



    MAXLOGHISTORY 907



LOGFILE


  GROUP 1 'C:\ORACLE\ORADATA\PROD\REDO01.LOG'  SIZE 16M,
  GROUP 2 'C:\ORACLE\ORADATA\PROD\REDO02.LOG'  SIZE 16M,
  GROUP 3 'C:\ORACLE\ORADATA\PROD\REDO03.LOG'  SIZE 16M,
  GROUP 4 'C:\ORACLE\ORADATA\PROD\REDO04.LOG'  SIZE 16M


DATAFILE


  'D:\ORACLE\ORADATA\PROD\SYSTEM01.DBF',
  'D:\ORACLE\ORADATA\PROD\RBS_A01.DBF',
  'D:\ORACLE\ORADATA\PROD\RBS_B01.DBF',
  'D:\ORACLE\ORADATA\PROD\USERS01.DBF',
  'D:\ORACLE\ORADATA\PROD\TOOLS01.DBF',
  'D:\ORACLE\ORADATA\PROD\INDEXES01.DBF',
  'D:\ORACLE\ORADATA\PROD\DR01.DBF'




CHARACTER SET WE8ISO8859P1



;

# Recovery is required if any of the datafiles are restored backups,
# or if the last shutdown was not normal or immediate.
# RECOVER DATABASE
# Database can now be opened normally.




ALTER DATABASE OPEN;


# Commands to add tempfiles to temporary tablespaces.
# Online tempfiles have complete space information.
# Other tempfiles may require adjustment.


ALTER TABLESPACE TEMP ADD TEMPFILE 'D:\ORACLE\ORADATA\PROD\TEMP01.DBF'

REUSE;



# End of tempfile additions.


#



8. If you had the online redo logs - edit the script above 



9. execute the create controlfile script:



c:\> set oracle_sid=PROD



C:\> svrmgrl



SVRMGR> connect / as sysdba


Connected.


SVRMGR> @C:\Oracle\Admin\PROD\create_new_controlfile.sql;
Statement Processed.	-- you hope


SVRMGR> quit




At this point, you have 2 control files, and an open database.
Shut it down (shutdown normal) and back it up.
Start it up and export it.


Kick it into ARCHIVELOG mode.


create a backup control file with an 


"ALTER DATABASE BACKUP CONTROLFILE TO TRACE;"


Order the Oracle 8 Backup and Recovery Handbook (or 8i).
sleep.



hth,



Paul





> "Paul Drake" <paled_at_home.com> wrote in message

> news:3B7E3520.FA1D58BE_at_home.com...

> > Tim Mason wrote:

> > >

> > > A customer have two web servers running Windows NT, Cold Fusion and

> > > Oracle 8.05.  The systems drives are arranged with the first 2 drives

> > > in Raid 1 as C:, the last 3 drives are Raid 5 as D:.

> > >

> > > The Oracle database files reside on the D:  Last week an NT admin

> > > crashed both system and had to rebuild the systems C:, this also wiped

> > > out the installation of Oracle.  We have been unable to access the ORA

> > > files.  We originally attempted to create a table space and point it

> > > at an existing ORA file.  This action appeared to clear the ora file

> > > and was stopped before it deleted all the data.  Is there a way to

> > > recover the data from these ORA files?  If there is can anyone give me

> > > any pointers as to how to do this.

> > >

> > >                                                 Thanks,

> > >                                                         Tim Mason

> >

> > Tim,

> >

> > You were fine up to the point in which you used the command:

> >

> > CREATE TABLESPACE <tablespace_name> DATAFILE <file_name> REUSE;

> >

> > as you could have mounted the database after re-installing the oracle

> > binaries, created a service, etc.

> >

> > At this point - you're hosed. The headers in the datafile are gone.

> >

> > Do you have *any* backups of the data files?

> >

> > Oracle Worldwide Support is your best bet - the data unloader tool may

> > be able to salvage some data.

> > It starts at around $10,000 for a tech to show up with the tool.

> >

> > As far as the remaining datafiles, you may still be able to mount them.

> > So you still have the SYSTEM tablespace's datafile?

> >

> > Backup everything that you do have before doing anything else.

> >

> > I wish you luck,

> >

> > Paul

Received on Sun Aug 19 2001 - 14:58:02 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US