Manually Backup - Restore or Clone a Database to Another Node [ID 562556.1]

rongshiyuan發表於2013-01-25
RESTDatabase
How to Manually Backup - Restore or Clone a Database to Another Node [ID 562556.1]

In this Document

Applies to:

Oracle Server - Enterprise Edition - Version 7.3.4.0 and later
Information in this document applies to any platform.

Goal

This note gives a step by step guide about how to manually clone or restore the database to a different host.

Fix

1) Prerequisites
----------------

- The copy of the datafiles must be done with the database closed or the database must be in ARCHIVELOG mode.
To determine the Database is in Archivemode or Noarchivemode use:

SQL> select name, log_mode from v$database;

2) Init.ora or Spfile.ora and Controlfile
----------------------------------------------------
- You need to copy the init.ora or spfile file to the target host
and locate it in ORACLE_HOME\dbs

- Copy the
Controlfile(s),
all the Datafiles
all the Archivelogs generated,
to the target host.

# To copy the Controlfile,
- either do a clean shutdown the Database, then take a cold copy of the controlfile
- or if database is open and Online Backup is taken do:

SQL> alter database backup controlfile to '/path/cf_name.ctl' ; -- this takes a binary copy of the controlfile

-- Hint:
Do this to get a creation script. for the controlfile, in case needed.
The following statement writes a tracefile to the 'trace' directory containing 'Create Controlfile' Statements
.
SQL> alter database backup controlfile to trace ;

# To backup the Database if database is open then, you need to put all the tablespaces in BACKUP MODE,
before starting the copy of the database/datafiles :


SQL> select tablespace_name from dba_tablespaces;

SQL> ALTER TABLESPACE BEGIN BACKUP;
.
> copy all the tablespace 'datafiles'
.
SQL> ALTER TABLESPACE END BACKUP;

# ==> Do this copy for 'ALL THE TABLESPACES/Datafiles' in the Database !!


# Comment: Starting with Oracle 10g:
you can use the BEGIN BACKUP on 'database' level, instead of 'tablespace' level :

SQL> alter DATABASE begin backup;
.
> copy all the tablespace 'datafiles'
.
SQL> alter DATABASE end backup;

3) Set the oracle environment
-------------------------

C:\> set ORACLE_SID=
C:\> sqlplus "sys/password as sysdba"

- Check the init.ora parameters that reference 'path/dir' location

control_files =
background_dump_dest = bdump>
core_dump_dest = cdump>
user_dump_dest = udump>
log_archive_dest_1 =


4) Set up a password file for the duplicated database

$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=<...>

5) If Windows Platform, create a new NT service for the 'cloned' duplicated database using oradim.

C:\> oradim -new -sid -intpwd -maxusers 10 -startmode manual -spfile ''

# or

C:\> oradim -new -sid -intpwd -maxusers 10 -startmode manual -pfile ''

6) Startup the database in mount status

SQL> startup mount

-- Rename any of the datafiles to the new location, if necessary:

SQL> ALTER DATABASE RENAME FILE
'/FULL_PATH_OF_OLD_LOCATION/AND_DATAFILE_NAME.DBF'
TO
'/FULL_PATH_OF_NEW_LOCATION/AND_DATAFILE_NAME.DBF';


-- Rename the logfiles to the new location if necessary

SQL> alter database rename file '' to '';



7) Check that all the datafiles are in the right location and ONLINE:

SQL> SELECT FILE#, STATUS, NAME FROM V$DATAFILE;


8) Perform. incomplete recovery:

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;

Forward the database applying archived redo log files until you decide
to stop recovery by typing 'CANCEL' at the prompt
(assuming that you have required archived redo log files in the log_archive_dest directory)
.
You may archive the source database redo log files and apply them at
the target database if required.

SQL> ALTER DATABASE OPEN RESETLOGS;


9) In Windows platforms, if you want that the database will start automatically then edit the registry:

regedit
go to
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEX
.
change the key : ORA__AUTOSTART=TRUE

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/17252115/viewspace-753280/，如需轉載，請註明出處，否則將追究法律責任。

相關文章