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: Simon Gottesman <sgotte_at_prodigy-nospamplease.net>
Date: Mon, 20 Aug 2001 01:44:36 GMT
Message-ID: <8YZf7.8954$kl4.1090813978@newssvr15.news.prodigy.com>


Thanks, I'll try it out on my 8.1.7 db.

"Paul Drake" <paled_at_home.com> wrote in message news:3B801A31.D1CFE9A6_at_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\PRO D\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 - 20:44:36 CDT

Original text of this message

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