Solved

Restore backupset to new server

Posted on 2008-06-23
15
1,413 Views
Last Modified: 2013-12-18
Guys

I have a 2 node RAC environment running on enterpise 5 linix where my PROD database sits. I have taken a full database copy inc archive logs. Oracle created these on my ASM instance location.
I have moved these files to another server and would like to restore this backup set into another enterpise linux 5 destination.

is this possible and how would I go about it

Cheers

Darren
0
Comment
Question by:DarrenJackson
  • 7
  • 7
15 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 21852687
easiest way I know of...  on source system,  backup control file to trace.  copy that control file to your new server.  change the name (if you want) and the locations of the files to their new locations on the new server.  Then run that script.  Apply all archive logs and open the database.
0
 

Author Comment

by:DarrenJackson
ID: 21852907
sdstuber

Thanks for replying.

At the moment all I have are a few files with very long names these are the end product of when I used the EM to create a compressed whole archive backup set including archive_logs.
So Yes I can do what you are talking about but dont I need to use rman and create a new instance or something

can you be more specific.

Regards

Darren
0
 
LVL 47

Expert Comment

by:schwertner
ID: 21853005
1. You have to create new instance on the new server. Try to use the same directories for placing the files.

2. Copy (shutdown immediate the DB before this!) and transfer in binary mode the files: all .dbf, all controll files, password file, SPFILE, all online redo logs

3. Place them in the same folders where the same files of tne new created instance reside.

3. If the directory structure is not the same you have to mount the DB and 'rename'
all changed .dbf files:

SQL>connect
 ... idle instance
SQL>startup mount
.....
SQL>alter database rename file '/u02/main/system.dbf' to 'u02/some/enh/system.dbf'
.... this for all files .dbf with new location

SQL>alter system open
0
 
LVL 47

Accepted Solution

by:
schwertner earned 500 total points
ID: 21853025
You ask instruction how to backup without RMAN.
Here you are:

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

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 fileeven if the control files are identicalto 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/


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
 

Author Comment

by:DarrenJackson
ID: 21853051
Schwertner

Thanks for the reply but I think you miss read my question

I need to do this using RMAN and the location of the files are in different locations

Sorry for the missunderstanding

Darren
0
 

Author Comment

by:DarrenJackson
ID: 21853085
Just to add the source database needs to stay online shutting down isnt an option

Sorry
0
 
LVL 47

Expert Comment

by:schwertner
ID: 21853234
Excuse me!

I did it and take a protocol. It is long.
What you prefer - to post it here or to get it by mail?
I also have posted it in the past - possibly you will be able to find it.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 47

Expert Comment

by:schwertner
ID: 21853697
0
 

Author Comment

by:DarrenJackson
ID: 21854748
schwertner

Thanks again for the post s will look into these

But I have quickly looked over it and I dont see where you actually restore a backupset.
I have created a compressed backup set which comprises of 7 files but how do I get these files restored to another location so that I can do recovery using RMAN

Cheers

Darren
0
 
LVL 47

Expert Comment

by:schwertner
ID: 21854803
The backup set was registered in catalog of RMAN.
Catalog was on TEST instance and MAIN instance was backuped.

3. CREATING A FULL BACKUP OF THE main INSTANCE IN THE CATALOG

C:\>rman
RMAN>

RMAN>Connect target sys/manager@main
RMAN>Connect catalog rman/rman@test
RMAN>Shutdown immediate
RMAN>Startup mount
RMAN>Backup database;  -- full backup
RMAN>Backup current controlfile;
RMAN>alter system open



But if you are not familiar with RMAN better use the clasicall way I describer by error in my first postings.
0
 

Author Comment

by:DarrenJackson
ID: 21854920
im happy to use rman so copying the files to backup server how do they get included in the catalog
0
 
LVL 47

Expert Comment

by:schwertner
ID: 21855409
They get included in the Catalog only if you use the 'catalog' command of RMAN.
Catalog stands for schema in Oracle server that comprises the back up incarnation.

If you do not use Catalog you have to find the backup set (normaly in ORACLE_BASE directory)
and to transfer it to the restored computer.

You see RMAN is complex product and need education and training.
0
 

Author Comment

by:DarrenJackson
ID: 21855565
Thanks schwertner for the help I know what you mean by it is complex but I eager to learn :)

I need to know that I can restore a hot database to another location using RMAN using scripts as quickly as possible

This is my task set by my boss

So if you can give me as much info on what commands to use for rman to point to a backupset then restore then recover this would be good

I realise that this may be a lot and I can create another post if it will help and add more points if im allowed to do this

Darren
0
 
LVL 47

Expert Comment

by:schwertner
ID: 21855607
I have posted the main notes i did when I was in your situation and
learned how to do this.
To be honest the main source is Oracle .pdf documentation.
Given that you are in England there are many excellent American books
devoted to RMAN. It is separated Unversum - a very big utility and it is
hard to understand the notions and the principles.
0
 

Author Comment

by:DarrenJackson
ID: 21855707
OK then I will use what you have given me

Thank you for the help

Darren
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

705 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now