How Oracle backups go to a different machine restore ____oracle

Scenario Description:

A friend asked, MSSQLServer to restore the database backup to other machines is very convenient, basically is a fool operation, Oracle has control files, parameter files a pile of things,

Feel that the restoration is very complex; in fact, I took some time to simply demonstrate the case, friends, under the reference. For simplicity and speed, I use Rman backup and restore

--Description:
(1). The db_name needs to be the same when Rman is backed up to a different machine recovery.

If you want to change to another instance name, you can change it with the NID command after the restore succeeds. The information for the instance name will
Log in to the control file, so if the instance name is inconsistent at the time of recovery, the error will be returned.

(2). When the path to recovery is inconsistent with the source library, you need to specify a new location using the SET command in the Restore command.
and use switch datafile all to update the change information to the control file.

Test environment:

Source database Server A, installed in E, backup directory already in e disk;

Source database Server B, installed in Disk F.

------------------------------------

First, the source database preparation work

------------------------------------

--1. Query dbid

Sql> select Name,dbid from V$database;

NAME DBID
--------- ----------
ORCL 1320546556

--2. Backup source Data db

Run {
Configure Retention Policy to recovery window of the days;
Configure Controlfile autobackup on; --Automatically open control file backup
Configure Controlfile autobackup format for device type disk to ' E:\backup\controlfile\bak_%F ';
Allocate channel C1 device type disk format ' E:\backup\data\bak_%u ';
Allocate channel C2 device type disk format ' E:\backup\data\bak_%u ';
SQL ' alter system archive log current ';
Backup incremental level=0 Database skip inaccessible
Plus Archivelog Filesperset 20
Delete all input;
Release channel C1;
Release channel C2;
}
Allocate channel for maintenance device type disk;
Crosscheck backupset;
Delete NoPrompt obsolete;

--or

Run {
Configure Retention Policy to recovery window of the days;
Allocate channel C1 device type disk format ' E:\backup\data\bak_%u ';
Allocate channel C2 device type disk format ' E:\backup\data\bak_%u ';
SQL ' alter system archive log current ';
Backup incremental level=0 Database skip inaccessible
Plus Archivelog Filesperset 20
Delete all input;
--Manually indicate the backup file name and path directly
Backup current Controlfile tag= ' bak_ctrollfile ' format= ' E:\backup\controlfile\bak_ctl_file_%U_%T ';
Backup SPFile tag= ' bak_spfile ' format= ' E:\backup\controlfile\bak_spfile_%U_%T ';
Release channel C1;
Release channel C2;
}
Allocate channel for maintenance device type disk;
Crosscheck backupset;
Delete NoPrompt obsolete;

--3. Manual Backup SPFile

Create pfile= ' E:\backup\inittest.ora ' from SPFile;

------------------------------------

Second, the target library preparation work:

------------------------------------

---step 1. Create password file

--If you do, you don't need to create a new one.

Orapwd File=f:\app\administrator\product\11.2.0\dbhome_1\database\pwdorcl.ora Password=password

--Step 2. Recovery parameter file

--Method 1. Source database SPFile and copy to B database server

\\192.168.2.25\e$\backup\inittest. ORA
Copied to
E:\bk\inittest. ORA

--Step 3: Edit the file path in the parameter file

If the path is the same as the original, you do not need to modify

eg
-*_dump_dest
-log_archive_dest*
-Control_files

---instance:

orcl.__db_cache_size=939524096
orcl.__java_pool_size=16777216
orcl.__large_pool_size=16777216
Orcl.__oracle_base= ' F:\app\Administrator ' #修改路径
orcl.__pga_aggregate_target=855638016
orcl.__sga_target=2550136832
Orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=1526726656
orcl.__streams_pool_size=16777216
*.audit_file_dest= ' F:\app\Administrator\admin\orcl\adump ' #修改路径
*.audit_trail= ' DB '
*.compatible= ' 11.2.0.0.0 '
#修改路径
*.control_files= ' F:\app\Administrator\oradata\orcl\control01.ctl ', ' F:\app\Administrator\oradata\orcl\ Control02.ctl ', ' F:\app\Administrator\oradata\orcl\control03.ctl '
*.db_block_size=8192
*.db_domain= '
*.db_name= ' ORCL '
*.db_recovery_file_dest= ' F:\arch ' #修改路径
*.db_recovery_file_dest_size=6442450944
*.diagnostic_dest= ' F:\app\Administrator ' #修改路径
*.dispatchers= ' (protocol=tcp) (SERVICE=ORCLXDB) '
*.log_archive_dest_1= ' Location=f:\arch ' #修改路径
*.nls_date_format= ' Yyyy-mm-dd hh:mi:ss '
*.open_cursors=300
*.optimizer_capture_sql_plan_baselines=true
*.pga_aggregate_target=845152256
*.processes=150
*.remote_login_passwordfile= ' EXCLUSIVE '
*.sga_target=2536505344
*.skip_unusable_indexes=true
*.undo_tablespace= ' UNDOTBS1 '

--Step 4: Restart the instance, using the edited parameter file

The B database server will be booted to the Nomout state with Pfile

Rman Target/

Startup Nomount pfile= ' E:\bk\inittest.ora '

--Step 5, restore control file, Mount database

Rman Target/

Set dbid 1320546556

Restore Controlfile from ' e:\bk\controlfile\BAK_CTL_FILE_6LO6SON4_1_1_20130412 ';

Start restore at 2013-04-11 13:58:58
Replacing the recovery directory with the target database control file
Allocated channel: Ora_disk_1
Channel ora_disk_1:sid=129 Device Type =disk

Channel ora_disk_1: Restoring Control files
Channel Ora_disk_1: Restore Complete, spents: 00:00:01
Output filename =f:\app\administrator\oradata\orcl\control01. Ctl
Output filename =f:\app\administrator\oradata\orcl\control02. Ctl
Output filename =f:\app\administrator\oradata\orcl\control03. Ctl
Completing restore at 2013-04-11 13:59:00

--Step 6: Start db to Mout State

ALTER DATABASE Mount;

The database is loaded
Channel Freed: Ora_disk_1

--Load Backup directory

CATALOG START with ' E:\BK ';

Search for all files that match the style E:\BK

List of database unknown files
====================================
FileName: E:\bk\controlfile\BAK_C-1320546556-20130411-03
FileName: E:\bk\controlfile\BAK_CTL_FILE_65O6QILO_1_1_20130411
FileName: E:\bk\controlfile\BAK_SPFILE_66O6QILQ_1_1_20130411
FileName: E:\bk\data\BAK_60O6QIGV
FileName: E:\bk\data\BAK_61O6QIGV
FileName: E:\bk\data\BAK_62O6QIH1
FileName: E:\bk\data\BAK_63O6QIH1
FileName: E:\bk\data\BAK_64O6QILM

Are you sure you want to include the above file in the directory (enter YES or NO)? Yes
Compiling file directory ...
Catalog compilation Complete

List of files that have been included in the catalog
======================
FileName: E:\bk\controlfile\BAK_C-1320546556-20130411-03
FileName: E:\bk\controlfile\BAK_CTL_FILE_65O6QILO_1_1_20130411
FileName: E:\bk\controlfile\BAK_SPFILE_66O6QILQ_1_1_20130411
FileName: E:\bk\data\BAK_60O6QIGV
FileName: E:\bk\data\BAK_61O6QIGV
FileName: E:\bk\data\BAK_62O6QIH1
FileName: E:\bk\data\BAK_63O6QIH1
FileName: E:\bk\data\BAK_64O6QILM

--Step 7.restore database

7.1 Recovery Catalog different conditions:

Check to target library for:
Sql> Select Trim (file_id), Trim (file_name) from Dba_data_files order by file_id ASC;

1 E:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01. Dbf
2 E:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSAUX01. Dbf
3 E:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01. Dbf
4 E:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01. Dbf
6 E:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY02. Dbf
7 E:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY03. Dbf
8 E:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY04. Dbf
9 E:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY05. Dbf
One by one E:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY06. Dbf
E:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY07. Dbf
E:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY08. Dbf
E:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY09. Dbf
E:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY10. Dbf
E:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY01. Dbf
E:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY11. Dbf
E:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY12. Dbf
E:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY13. Dbf
E:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY14. Dbf
E:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY15. Dbf
E:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY16. Dbf
E:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY17. Dbf
E:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY18. Dbf
E:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY19. Dbf
E:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY20. Dbf

Sql>select Trim (file_id), Trim (file_name) from Dba_temp_files;

1 E:\APP\ADMINISTRATOR\ORADATA\ORCL\TEMP01. Dbf

Select ' SET NEWNAME for datafile ' | | ' '|| file_id| | ' '||' To ' | | ' '||''''|| Trim (file_name) | | | '
From Dba_data_files-file_id ASC;

---The following example:

RUN
{
# Rename the Datafiles
SET NEWNAME for datafile 1 to ' F:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01. DBF ';
SET NEWNAME for datafile 2 to ' F:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSAUX01. DBF ';
SET NEWNAME for datafile 3 to ' F:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01. DBF ';
SET NEWNAME for datafile 4 to ' F:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01. DBF ';
SET NEWNAME for datafile 6 to ' F:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY02. DBF ';
SET NEWNAME for datafile 7 to ' F:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY03. DBF ';
SET NEWNAME for datafile 8 to ' F:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY04. DBF ';
SET NEWNAME for DataFile 9 to ' F:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY05. DBF ';
SET NEWNAME for datafile one to ' F:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY06. DBF ';
SET NEWNAME for DataFile to ' F:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY07. DBF ';
SET NEWNAME for DataFile to ' F:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY08. DBF ';
SET NEWNAME for DataFile ' F:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY09. DBF ';
SET NEWNAME for DataFile ' F:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY10. DBF ';
SET NEWNAME for DataFile ' F:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY01. DBF ';
SET NEWNAME for DataFile to ' F:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY11. DBF ';
SET NEWNAME for DataFile ' F:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY12. DBF ';
SET NEWNAME for DataFile ' F:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY13. DBF ';
SET NEWNAME for DataFile to ' F:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY14. DBF ';
SET NEWNAME for DataFile to ' F:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY15. DBF ';
SET NEWNAME for DataFile to ' F:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY16. DBF ';
SET NEWNAME for DataFile to ' F:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY17. DBF ';
SET NEWNAME for DataFile to ' F:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY18. DBF ';
SET NEWNAME for DataFile to ' F:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY19. DBF ';
SET NEWNAME for DataFile to ' F:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY20. DBF ';
SQL "ALTER DATABASE RENAME FILE" E:\app\Administrator\oradata\orcl\REDO01. LOG ""
To ' F:\app\Administrator\oradata\orcl\REDO01. LOG ' ";
SQL "ALTER DATABASE RENAME FILE" E:\app\Administrator\oradata\orcl\REDO02. LOG ""
To ' F:\app\Administrator\oradata\orcl\REDO02. LOG ' ";
SQL "ALTER DATABASE RENAME FILE" E:\app\Administrator\oradata\orcl\REDO03. LOG ""
To ' F:\app\Administrator\oradata\orcl\REDO03. LOG ' ";
RESTORE DATABASE;
SWITCH datafile All;
}

Description of Switch datafile all:
-For Nocatalog mode, the information for Rman backups is stored in the control file, including the path information for the file. The role of the switch datafile all here is to update the information in the control file.

--Step 8.recover database

RECOVER DATABASE;

/*** Common errors:

Archive log thread =1 sequence =1696
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR message STACK follows ===============
RMAN-00571: ===========================================================
Rman-03002:recover command (on 04/11/2013 15:18:07) failed
RMAN-06054: Media recovery is requesting an archive log of the unknown thread 1 sequence 1696 and the start SCN 240793
29

---solution:

rman> recover database until SCN 24159677;

Start recover at 2013-04-11 15:29:05
Using channel Ora_disk_1

Beginning recovery of media
Media restore completed, when: 00:00:02

Completed recover at 2013-04-11 15:29:11

***/

----Step 9.open...resetlogs Open the database

ALTER DATABASE open resetlogs;

/******* Common errors:

rman> ALTER DATABASE open resetlogs;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR message STACK follows ===============
RMAN-00571: ===========================================================
Rman-03002:alter DB Command (on 04/11/2013 15:38:25) failed
ORA-00392: Log 1 (for thread 1) is being cleared and operation is not allowed
ORA-00312: Online log 1 thread 1: ' F:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01. LOG '

----Solution:

Sql> Select Group#,bytes/1024/1024| | ' M ', status from V$log;

group# bytes/1024/1024| | ' M ' STATUS
---------- ----------------------------------------- ----------------
1 50M clearing_current
3 50M Clearing
2 50M Clearing

Sql> ALTER DATABASE clear logfile Group 1; --Empty log files

Sql> ALTER DATABASE clear logfile Group 2;

Sql> ALTER DATABASE clear logfile Group 3;

Sql> Select Group#,bytes/1024/1024| | ' M ', status from V$log;

group# bytes/1024/1024| | ' M ' STATUS
--------- ----------------------------------------- ----------------
1 50M Current
3 50M Unused
2 50M Unused

*******/

---step 10: Process temp temporary table space

Sqlplus/as SYSDBA

Sql> select name from V$tempfile;

NAME

--------------------------------------------------------------------------------

E:\app\Administrator\oradata\orcl\TEMP01. DBF-The path in the control file is still in E disk

--A database table Space offline

sql> ALTER DATABASE Tempfile ' E:\app\Administrator\oradata\orcl\TEMP01. DBF ' offline;

Database altered.

--Move temp data files to the B database server at the OS level

Sql> MV E:\app\Administrator\oradata\orcl\TEMP01. DBF F:\app\Administrator\oradata\orcl\TEMP01. Dbf

--Modify the information for the temp file in the control file

sql> ALTER DATABASE Rename file ' E:\app\Administrator\oradata\orcl\TEMP01. DBF ' to ' F:\app\Administrator\oradata\orcl\TEMP01. DBF ';

--temp table Space Online

sql> ALTER DATABASE Tempfile ' F:\app\Administrator\oradata\orcl\TEMP01. DBF ' online;

Database altered.

--Verify

Sql> select name from V$tempfile;

NAME

--------------------------------------------------------------------------------

F:\app\Administrator\oradata\orcl\TEMP01. Dbf