[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

user managed recovery to a new host

Posted on 2010-03-26
6
Medium Priority
?
385 Views
Last Modified: 2013-12-18
Hi,

Can someone tell me the steps that i may have to perform, if a disk  where my oracle 10g database(linux) resides crashes, but i have (user managed backup last night) image copies of datafiles, control files,pfiles..etc available.
I want to restore this on a new machine which is running windows. (this machine has oracle 11g on it..)
I am familiar with steps involving rman to a new host..but not so much with user managed recovery..so can somebody point me to a good link or give me the essential steps?
Can i restore the copies from a 10g database to a 11g database???

Thanks so much.

0
Comment
Question by:Pra4444
  • 2
  • 2
5 Comments
 
LVL 15

Expert Comment

by:Franck Pachot
ID: 28699824
Hi, you didn't tell if it is a cold backup.

Just restore all datafiles on the new server. If the file paths are the same, you have nothing else to do: you can then follow the steps for migration from 10g to 11g.

If path are sifferent, you will have to change some pfiles parameters, startup mount, and rename some database files.

Regards,
Franck.
0
 
LVL 12

Author Comment

by:Pra4444
ID: 28706535
yes, its a cold backup. So its a consistent backup.
0
 
LVL 15

Expert Comment

by:Franck Pachot
ID: 28711003
Ok, so that is not more difficult than a database upgrade. Regards, Franck.
0
 
LVL 48

Expert Comment

by:schwertner
ID: 28712588
The steps in restoring and recovering all datafiles are:

-      stop the Oracle database;
-      make if possible a full cold database backup (if you succeed to stop clean the database) or save images of the disks;
-      repair the hardware in the case of damaged hardware components;
-      ensure a working operating system either by installing a new operating system or by fixing the installed operation system. Make sure that the parameters of the operating system fit the requirements of the installation of Oracle (companion document available);
-      analyze what caused the need of recovery. You have to figure out if the Oracle instance (mainly Oracle software reside in ORACLE_HOME) or the Oracle database is damaged. In the first case do a new installation of Oracle and creation of new database. Before this remove the old Oracle installation. The steps of removing the old installation are:
-      remove the database using Oracle Configuration Assistant (dbassist);
-      Backup all important files. (This includes the orainst.loc and init.ora files and any data files that have not already been backed up.);
-      Remove the $ORACLE_HOME directory;
-      Remove the oraInventory directory. (Most of the time it will be in the $ORACLE_BASE directory) ;
-      Remove the orainst.loc file. This file is in different places depending on which OS you have. (Sun is /var/opt/oracle, LINUX is in /etc.).
In the second case remove only the database using Oracle Configuration Assistant (dbassist) and create a new database. Follow strictly all steps of the document “ORACLE 8.1.7 Installation Guide for x86 Linux”. At the end make sure that Oracle clients can connect to the new empty database.
Close the database: SHUTDOWN IMMEDIATE
Copy Backup files to the Oracle Host:
    Copy the
•      backup datafiles
•      standby controlfile
•      all available archived redo logs    
•      !!!!!!   online redo logs
•      password file
from the backup file set to the Oracle host
0
 
LVL 48

Accepted Solution

by:
schwertner earned 2000 total points
ID: 28712661
This is the instruction I use for cold backup: You will see some investigation in advace is needed!!!!

OFFLINE COLD BACKUP

1.      Preparation :
Obtain a list of the relevant files by querying V$DATAFILE, V$LOGFILE, V$CONTROLFILE and investigate all available redo log files. The connection should be made with sysdba privilege.

Connected to:
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production

SQL> select name from v$datafile;  

NAME
--------------------------------------------------------------------------------
C:\ORACLE\ORADATA\MAIN\SYSTEM01.DBF
C:\ORACLE\ORADATA\MAIN\UNDOTBS01.DBF
C:\ORACLE\LSDCONTACT00.DBF
C:\ORACLE\ORADATA\MAIN\TOOLS01.DBF
C:\ORACLE\ORADATA\MAIN\USERS01.DBF
C:\ORACLE\LSICONTACT00.DBF
C:\ORACLE\LSDDOCUMENT00.DBF
C:\ORACLE\LSIDOCUMENT00.DBF
C:\ORACLE\LSDIDMAP00.DBF
C:\ORACLE\LSIIDMAP00.DBF
C:\ORACLE\LSDMEDICAL00.DBF
C:\ORACLE\LSIMEDICAL00.DBF
C:\ORACLE\LSDSYSTEM00.DBF
C:\ORACLE\LSISYSTEM00.DBF

14 rows selected.

SQL> select member from v$logfile;  

MEMBER
--------------------------------------------------------------------------------
C:\ORACLE\ORADATA\MAIN\REDO01.LOG
C:\ORACLE\ORADATA\MAIN\REDO02.LOG
C:\ORACLE\ORADATA\MAIN\REDO03.LOG

SQL> show parameter control_files;  

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      C:\oracle\oradata\main\control
                                                            01.ctl, C:\oracle\oradata\main
                                                             \control02.ctl, C:\oracle\orad
                                                               ata\main\control03.ctl


or

 SQL> SELECT value FROM v$parameter
WHERE name = 'control_files';


Run
ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS <Path and Controlfilename>;

ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS ‘/u03/ctl_file’;

This will create a file with a copy of the physical structure of the database in case it is needed to recreate it at any time.

The command ALTER DATABASE BACKUP CONTROLFILE TO
TRACE provides a script to create the control file.
 In addition, the individual control files should also be backed-up by using the
command ALTER DATABASE BACKUP CONTROLFILE to <filename>.
This provides a binary copy of the control file at that time.


 
Locate the relevant parameter files :
init<sid>.ora file;        
config<sid>.ora file;
password file if used.
Also locate and back up the initialization parameter file and other Oracle product initialization files. To find them, do a search for *.ora starting in your Oracle home directory and recursively search all of its subdirectories.

Investigate where are all available redo log files:
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u03/oradata/oralin1/arch
Oldest online log sequence     30
Next log sequence to archive   32
Current log sequence           32


Investigate the location of Archive Log files  and ensure they are transferred in a save location on another computer:

log_archive_dest
log_archive_dest_1
log_archive_dest_10
log_archive_dest_2
log_archive_dest_3
log_archive_dest_4
log_archive_dest_5
log_archive_dest_6

log_archive_dest_7
log_archive_dest_8
log_archive_dest_9
Note: If you are forced to perform a restore operation, you must restore the control files to all locations specified in the parameter file. Consequently, it is better to make copies of each multiplexed control file—even if the control files are identical—to avoid problems at restore time.  

Locate the initialization parameter file and other Oracle product initialization files. To find them, do a search for *.ora starting in your Oracle home directory and recursively search all of its subdirectories:
root@oralin1:/ > locate *.ora

/u01/app/oracle/admin/oralin1/pfile/initorcf.ora
/u01/app/oracle/product/8.1.7/dbs/init.ora
/u01/app/oracle/product/8.1.7/dbs/initdw.ora
/u01/app/oracle/product/8.1.7/dbs/initorcf.ora
/u01/app/oracle/product/8.1.7/hs/admin/inithsodbc.ora
/u01/app/oracle/product/8.1.7/hs/admin/listener.ora
/u01/app/oracle/product/8.1.7/hs/admin/tnsnames.ora
/u01/app/oracle/product/8.1.7/network/admin/listener.ora
/u01/app/oracle/product/8.1.7/network/admin/samples/cman.ora
/u01/app/oracle/product/8.1.7/network/admin/samples/listener.ora
/u01/app/oracle/product/8.1.7/network/admin/samples/names.ora
/u01/app/oracle/product/8.1.7/network/admin/samples/sqlnet.ora
/u01/app/oracle/product/8.1.7/network/admin/samples/tnsnames.ora
/u01/app/oracle/product/8.1.7/network/admin/sqlnet.ora
/u01/app/oracle/product/8.1.7/network/admin/tnsnames.ora

oracle@oralin1:~ > locate orapw
/u01/app/oracle/product/8.1.7/bin/orapwd
/u01/app/oracle/product/8.1.7/bin/orapwdO
/u01/app/oracle/product/8.1.7/dbs/orapworcf

To do this prepare a shell script like:
#!/bin/sh

# Data files
cp /u01/app/oracle/oradata/oralin1/*.dbf   /u03/oradata/oralin1/cold_bku/

# Online log files
cp /u01/app/oracle/oradata/oralin1/*.log   /u03/oradata/oralin1/cold_bku/

#Control files
cp /u01/app/oracle/oradata/oralin1/*.ctl   /u03/oradata/oralin1/cold_bku/

#Archived log files
cp /u03/oradata/oralin1/arch/*.arc         /u03/oradata/oralin1/cold_bku/

# init<sid>.ora file
cp /u01/app/oracle/admin/oralin1/pfile/initorcf.ora   /u03/oradata/oralin1/cold_bku/

# NET ora files
cp /u01/app/oracle/product/8.1.7/network/admin/*.ora  /u03/oradata/oralin1/cold_bku/

# password file
cp /u01/app/oracle/product/8.1.7/dbs/orapworcf  /u03/oradata/oralin1/cold_bku/

Pay attention to the SPFILE placed in the location pointed by the parameter

SQL> show parameter SPFILE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      C:\ORACLE11G\PRODUCT\11.1.0\DB
                                                 _1\DATABASE\SPFILEORCL.ORA
SQL>



Archive log investigation:


SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     37
Current log sequence           39



Execution :  

Use operating system commands or a backup utility to make backups of all
datafiles and all control files specified by the CONTROL_FILES parameter of
the initialization parameter file. Also back up the initialization parameter file
and other Oracle product initialization files. To find them, do a search for
*.ora starting in your Oracle home directory and recursively search all of its
subdirectories.

The database must be shut down cleanly (ie NORMAL, or IMMEDIATE).
Do not make a whole database backup when the instance is aborted or stopped because of a failure. Reopen the database and shut it down cleanly first.  
Take OS copies of the database files, redo log files, controlfiles and parameter files as specified above.  
Consider taking a copy of any relevant archive logs.  
After this is complete you may open the database again.  

A backup strategy is useless if the database being backed up is corrupt to begin with. Oracle contains online utilities to verify the correctness of a database; these tools should be used prior to starting the database backup operation. No database backup operation should be allowed to proceed if the database has not been recently verified as consistent in all respects.
It is recognized that it may not always be feasible to verify the integrity of the database before every database backup. However, an attempt should be made to verify the database integrity as often as operationally possible. For some details read section 2.6  of “Database Backup and Recovery Strategy”.
 





Avoiding the Backup of Online Redo Logs
Although it may seem that you should back up online redo logs along with the
datafiles and control file, this technique is dangerous. You should not back up
online redo logs for the following reasons:
•      The best method for protecting the online logs against media failure is by multiplexing them, that is, having multiple log members per group, on different disks and disk controllers.
•      If your database is in ARCHIVELOG mode, then ARCn is already archiving the redo logs.
•      If your database is in NOARCHIVELOG mode, then the only type of backups that you should perform are closed, consistent, whole database backups. The files in this type of backup are all consistent and do not need recovery, so the online logs are not needed.



0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This article will show a step by step guide on how to mask column values in Oracle 12c using DBMS_REDACT full redaction option. This option is available on licensed Oracle Enterprise edition as part of Oracle's Advanced Security.
This video shows how to recover a database from a user managed backup
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

590 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question