oracle 11g資料庫遷移(不同目錄不同主機
1、建立監聽、服務
2、備份源庫的資料檔案、初始化參數檔案和控制檔案
RMAN> backup full database format 'd:\full_%U_%d.BKP';
開始 backup, 於 20-11月-14
使用目標資料庫控制檔替代復原目錄
配置的通道: ORA_DISK_1
通道 ORA_DISK_1: SID=23 裝置類型=DISK
通道 ORA_DISK_1: 啟動完整資料檔備份集
通道 ORA_DISK_1: 正在指定備份集中的資料檔
輸入資料檔檔案編號=00001 名稱=D:\APP\H2603027\ORADATA\TESTDB\SYSTEM01.DBF
輸入資料檔檔案編號=00003 名稱=D:\APP\H2603027\ORADATA\TESTDB\UNDOTBS01.DBF
輸入資料檔檔案編號=00002 名稱=D:\APP\H2603027\ORADATA\TESTDB\SYSAUX01.DBF
輸入資料檔檔案編號=00005 名稱=D:\APP\H2603027\ORADATA\TESTDB\TEST.DBF
輸入資料檔檔案編號=00004 名稱=D:\APP\H2603027\ORADATA\TESTDB\USERS01.DBF
通道 ORA_DISK_1: 啟動部份 1, 在 20-11月-14
通道 ORA_DISK_1: 已完成部份 1, 在 20-11月-14
片段處理=D:\FULL_14PO3B44_1_1_TESTDB.BKP 標記=TAG20141120T135204 註解=NONE
通道 ORA_DISK_1: 備份集完成, 經歷時間: 00:00:45
通道 ORA_DISK_1: 啟動完整資料檔備份集
通道 ORA_DISK_1: 正在指定備份集中的資料檔
包括備份集中目前的控制檔
包括備份集中目前的 SPFILE
通道 ORA_DISK_1: 啟動部份 1, 在 20-11月-14
通道 ORA_DISK_1: 已完成部份 1, 在 20-11月-14
片段處理=D:\FULL_15PO3B5I_1_1_TESTDB.BKP 標記=TAG20141120T135204 註解=NONE
通道 ORA_DISK_1: 備份集完成, 經歷時間: 00:00:01
完成 backup, 於 20-11月-14
---------------------------------------------------------------------------------------
RMAN> backup current controlfile format 'd:\ctl_%u_%d.bkp';
開始 backup, 於 20-11月-14
使用通道 ORA_DISK_1
通道 ORA_DISK_1: 啟動完整資料檔備份集
通道 ORA_DISK_1: 正在指定備份集中的資料檔
包括備份集中目前的控制檔
通道 ORA_DISK_1: 啟動部份 1, 在 20-11月-14
通道 ORA_DISK_1: 已完成部份 1, 在 20-11月-14
片段處理=D:\CTL_1BPO3EV4_TESTDB.BKP 標記=TAG20141120T145740 註解=NONE
通道 ORA_DISK_1: 備份集完成, 經歷時間: 00:00:01
完成 backup, 於 20-11月-14
---------------------------------------------------------------------------------------
RMAN> backup spfile format 'd:\SPF_%u_%d.bkp';
開始 backup, 於 20-11月-14
使用通道 ORA_DISK_1
通道 ORA_DISK_1: 啟動完整資料檔備份集
通道 ORA_DISK_1: 正在指定備份集中的資料檔
包括備份集中目前的 SPFILE
通道 ORA_DISK_1: 啟動部份 1, 在 20-11月-14
通道 ORA_DISK_1: 已完成部份 1, 在 20-11月-14
片段處理=D:\SPF_17PO3B7E_TESTDB.BKP 標記=TAG20141120T135350 註解=NONE
通道 ORA_DISK_1: 備份集完成, 經歷時間: 00:00:01
完成 backup, 於 20-11月-14
3、複制歸檔日志到目标資料庫
4、建立密碼檔案和OracleService testdb
5、恢複初始化參數檔案
使用生產環境的spfile，或者從備份集中恢復出spfile，并使用這個spfile，打開資料庫到nomount狀態，
或者創建一個初始化參數檔案INITtestdb.ora，裏面就一句話:db_name=testdb儲存，restore spfile to pfile ‘d:\app\h2603027\product\11.2.0\db_home\database\INITtestdb.ora’ from ‘備份路勁’；也可以恢復pfile，然後去修改相應的參數
6、恢復控制檔案(從current controlfile的備份檔案恢複)
Restore controlfile from ‘備份路勁’
恢復出控制檔案，并使用控制檔案把資料庫切換為mount狀態。
alter database mount；
7、restore database;
源庫的資料檔案放置的位置為'D:\APP\H2603027\ORADATA\TESTDB\SYSTEM01.DBF'，現在異機不同目錄恢複到'D:\APP\administrator\ORADATA\TESTDB\SYSTEM01.DBF'
run
{
set newname for datafile 1 to 'D:\APP\administrator\ORADATA\TESTDB\SYSTEM01.DBF';
set newname for datafile 3 to 'D:\APP\administrator\ORADATA\TESTDB\UNDOTBS01.DBF';
set newname for datafile 2 to 'D:\APP\administrator\ORADATA\TESTDB\SYSAUX01.DBF';
set newname for datafile 5 to 'D:\APP\administrator\ORADATA\TESTDB\TEST.DBF';
set newname for datafile 4 to 'D:\APP\administrator\ORADATA\TESTDB\USERS01.DBF';
restore database; --隻是将RMAN檔案還原到上面指定的位置
switch datafile all; --如果不交換，則recover時，仍會在原來的位置找該檔案
}
8、recover database
可以通過recover database preview來檢視消除資料庫恢複的模糊性；
通過catalog archivelog來指定恢複的最新的歸檔日志；
然後recover database。
或者根據list backup中的scn修複資料庫
備份集 12 中的存檔日誌清單
繫線順序 低 SCN Low Time 下一個 SCN 下一次時間
---- ------- ---------- ---------- ---------- ---------
1 73 959379 18-11月-14 959769 18-11月-14
1 74 959769 18-11月-14 959781 18-11月-14
rman>recover database until scn 959769;
9、rman>alter database open resetlogs
在此步驟之前，首先建立與源資料庫相同的日志檔案放置的位置，把日志檔案和臨時檔案放置在那裡，然後通過修改控制檔案把日志檔案和臨時檔案放置在和資料檔案相同的位置。
10、修改控制檔案
控制檔案對于資料庫來說是非常重要的資料結構，在進行資料恢複時通常是必不可少的.
Oracle提供兩種方式備份控制檔案:
1.生成可以重建控制檔案的腳本
2.備份二進制的控制檔案
我們看一下如何獲得可以重建控制檔案的腳本.
Oracle提供如下指令:
alter database backup controlfile to trace;
實際操作:
SQL> alter database backup controlfile to trace;
Database altered.
SQL> show parameter user_dump_dest;
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
user_dump_dest string
D:\app\H2603027\diag\rdbms\tes
tdb\testdb\trace
trace檔案内容:
-- The following are current System-scope REDO Log Archival related
-- parameters and can be included in the database initialization file.
--
-- LOG_ARCHIVE_DEST=''
-- LOG_ARCHIVE_DUPLEX_DEST=''
--
-- LOG_ARCHIVE_FORMAT=ARC%S_%R.%T
--
-- DB_UNIQUE_NAME="testdb"
--
-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'
-- LOG_ARCHIVE_MAX_PROCESSES=4
-- STANDBY_FILE_MANAGEMENT=MANUAL
-- STANDBY_ARCHIVE_DEST=%ORACLE_HOME%\RDBMS
-- FAL_CLIENT=''
-- FAL_SERVER=''
--
-- LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'
-- LOG_ARCHIVE_DEST_1='MANDATORY NOREOPEN NODELAY'
-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM EXPEDITE NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_1='NOREGISTER NOALTERNATE NODEPENDENCY'
-- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'
-- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
-- LOG_ARCHIVE_DEST_STATE_1=ENABLE
--
-- Below are two sets of SQL statements, each of which creates a new
-- control file and uses it to open the database. The first set opens
-- the database with the NORESETLOGS option and should be used only if
-- the current versions of all online logs are available. The second
-- set opens the database with the RESETLOGS option and should be used
-- if online logs are unavailable.
-- The appropriate set of statements can be copied from the trace into
-- a script file, edited as necessary, and executed when there is a
-- need to re-create the control file.
--
-- Set #1. NORESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- available.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "TESTDB" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 'D:\APP\H2603027\ORADATA\TESTDB\REDO01.LOG' SIZE 50M BLOCKSIZE 512,
GROUP 2 'D:\APP\H2603027\ORADATA\TESTDB\REDO02.LOG' SIZE 50M BLOCKSIZE 512,
GROUP 3 'D:\APP\H2603027\ORADATA\TESTDB\REDO03.LOG' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'D:\APP\H2603027\ORADATA\TESTDB\SYSTEM01.DBF',
'D:\APP\H2603027\ORADATA\TESTDB\SYSAUX01.DBF',
'D:\APP\H2603027\ORADATA\TESTDB\UNDOTBS01.DBF',
'D:\APP\H2603027\ORADATA\TESTDB\USERS01.DBF',
'D:\APP\H2603027\ORADATA\TESTDB\TEST.DBF'
CHARACTER SET AL32UTF8
;
-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','OFF');
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE 'D:\APP\H2603027\FAST_RECOVERY_AREA\TESTDB\ARCHIVELOG\2014_11_20\O1_MF_1_1_%U_.ARC';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE
-- All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
-- 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:\APP\H2603027\ORADATA\TESTDB\TEMP01.DBF'
SIZE 62914560 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
-- End of tempfile additions.
--
-- Set #2. RESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "TESTDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 'D:\APP\H2603027\ORADATA\TESTDB\REDO01.LOG' SIZE 50M BLOCKSIZE 512,
GROUP 2 'D:\APP\H2603027\ORADATA\TESTDB\REDO02.LOG' SIZE 50M BLOCKSIZE 512,
GROUP 3 'D:\APP\H2603027\ORADATA\TESTDB\REDO03.LOG' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'D:\APP\H2603027\ORADATA\TESTDB\SYSTEM01.DBF',
'D:\APP\H2603027\ORADATA\TESTDB\SYSAUX01.DBF',
'D:\APP\H2603027\ORADATA\TESTDB\UNDOTBS01.DBF',
'D:\APP\H2603027\ORADATA\TESTDB\USERS01.DBF',
'D:\APP\H2603027\ORADATA\TESTDB\TEST.DBF'
CHARACTER SET AL32UTF8
;
-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','OFF');
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE 'D:\APP\H2603027\FAST_RECOVERY_AREA\TESTDB\ARCHIVELOG\2014_11_20\O1_MF_1_1_%U_.ARC';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE 'D:\APP\H2603027\ORADATA\TESTDB\TEMP01.DBF'
SIZE 62914560 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
-- End of tempfile additions.
--
編輯這個trace檔案,我們就可以獲得建立控制檔案的腳本.
根據資料庫不同狀況，你可以選擇是使用RESETLOGS/NORESETLOGS來重建控制檔案.
我們獲得以下腳本:
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "TESTDB" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 'D:\APP\Administrator\ORADATA\TESTDB\REDO01.LOG' SIZE 50M BLOCKSIZE 512,
GROUP 2 'D:\APP\Administrator\ORADATA\TESTDB\REDO02.LOG' SIZE 50M BLOCKSIZE 512,
GROUP 3 'D:\APP\Administrator\ORADATA\TESTDB\REDO03.LOG' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'D:\APP\ADMINISTRATOR\ORADATA\TESTDB\SYSTEM01.DBF',
'D:\APP\ADMINISTRATOR\ORADATA\TESTDB\SYSAUX01.DBF',
'D:\APP\ADMINISTRATOR\ORADATA\TESTDB\UNDOTBS01.DBF',
'D:\APP\ADMINISTRATOR\ORADATA\TESTDB\USERS01.DBF',
'D:\APP\ADMINISTRATOR\ORADATA\TESTDB\TEST.DBF'
CHARACTER SET AL32UTF8
;
RECOVER DATABASE
ALTER SYSTEM ARCHIVE LOG ALL;
ALTER DATABASE OPEN;
ALTER TABLESPACE TEMP ADD TEMPFILE 'D:\APP\Administrator\ORADATA\TESTDB\TEMP01.DBF'
SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
把這個腳本儲存為createctl.sql
運作這個腳本即可重建控制檔案:
C:\Users\H2603027>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on 星期四 11月 20 16:07:18 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
連線至閒置的執行處理.
SQL> set echo on
SQL> @createctl
SQL> STARTUP NOMOUNT
ORACLE instance started.
Total System Global Area 135337420 bytes
Fixed Size 452044 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "TESTDB" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 'D:\APP\Administrator\ORADATA\TESTDB\REDO01.LOG' SIZE 50M BLOCKSIZE 512,
GROUP 2 'D:\APP\Administrator\ORADATA\TESTDB\REDO02.LOG' SIZE 50M BLOCKSIZE 512,
GROUP 3 'D:\APP\Administrator\ORADATA\TESTDB\REDO03.LOG' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'D:\APP\ADMINISTRATOR\ORADATA\TESTDB\SYSTEM01.DBF',
'D:\APP\ADMINISTRATOR\ORADATA\TESTDB\SYSAUX01.DBF',
'D:\APP\ADMINISTRATOR\ORADATA\TESTDB\UNDOTBS01.DBF',
'D:\APP\ADMINISTRATOR\ORADATA\TESTDB\USERS01.DBF',
'D:\APP\ADMINISTRATOR\ORADATA\TESTDB\TEST.DBF'
CHARACTER SET AL32UTF8
;
Control file created.
SQL> RECOVER DATABASE
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required
SQL> ALTER SYSTEM ARCHIVE LOG ALL;
System altered.
SQL> ALTER DATABASE OPEN;
Database altered.
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE 'D:\APP\Administrator\ORADATA\TESTDB\TEMP01.DBF'
2 SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
Tablespace altered
) [複制連結]