【備份恢復】上：還原和恢復資料庫（即將一個庫還原到另外一個庫）
還原和恢復資料庫
本實驗是
將一個庫還原到另外一個庫。
說明：
源庫：
192.168.10.2 ORACLE_SID=ORA11GR2
DBID=237843809
目標庫：
192.168.10.3 ORACLE_SID=
ORA11GR2
（保持與源庫一直的SID）
1.
準備工作
源庫 資料庫全備及 開啟 控制檔案 自動備份，當然也包括引數檔案
RMAN> backup as backupset database;
Starting backup at 02-OCT-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=40 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/ORA11GR2/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/ORA11GR2/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/ORA11GR2/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/ORA11GR2/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ORA11GR2/users01.dbf
channel ORA_DISK_1: starting piece 1 at 02-OCT-16
channel ORA_DISK_1: finished piece 1 at 02-OCT-16
piece handle= /u01/app/FRA/ORA11GR2/backupset/2016_10_02/o1_mf_nnndf_TAG20161002T195139_cz1x6crk_.bkp tag=TAG20161002T195139 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 02-OCT-16
Starting Control File and SPFILE Autobackup at 02-OCT-16
piece handle= /u01/app/FRA/ORA11GR2/autobackup/2016_10_02/o1_mf_s_924205924_cz1x757l_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 02-OCT-16
2.複製
RMAN
備份檔案
在目標庫 建立相同目錄並將源庫 RMAN 備份檔案 複製到目標庫
[oracle@bing ~]$ mkdir -p /u01/app/FRA/ORA11GR2/ autobackup /2016_10_02/
[oracle@bing ~]$ mkdir -p /u01/app/FRA/ORA11GR2 /backupset /2016_10_02/
[oracle@bing ~]$
[oracle@bing 2016_10_02]$ pwd
/u01/app/FRA/ORA11GR2/ autobackup /2016_10_02
[oracle@bing 2016_10_02]$
[oracle@bing 2016_10_02]$ scp 192.168.10.2:/u01/app/FRA/ORA11GR2/ autobackup /2016_10_02/o1_mf_s_924205924_cz1x757l_.bkp .
oracle@192.168.10.2's password:
o1_mf_s_924205924_cz1x757l_.bkp 100% 9600KB 9.4MB/s 00:01
[oracle@bing 2016_10_02]$
[oracle@bing 2016_10_02]$ cd /u01/app/FRA/ORA11GR2/ backupset /2016_10_02/
[oracle@bing 2016_10_02]$ pwd
/u01/app/FRA/ORA11GR2/ backupset /2016_10_02
[oracle@bing 2016_10_02]$
[oracle@bing 2016_10_02]$
[oracle@bing 2016_10_02]$ scp 192.168.10.2:/u01/app/FRA/ORA11GR2/backupset/2016_10_02/o1_mf_nnndf_TAG20161002T195139_cz1x6crk_.bkp .
oracle@192.168.10.2's password:
o1_mf_nnndf_TAG20161002T195139_cz1x6crk_.bkp 100% 1160MB 36.3MB/s 00:32
[oracle@bing 2016_10_02]$
3.
配置目標庫
目標庫設定環境變數，啟動 rman 到 nomount 狀態， 設定 dbid （即源庫 dbid ）
[oracle@bing 2016_10_02]$ echo $ORACLE_SID
PROD
[oracle@bing 2016_10_02]$ export ORACLE_SID=ORA11GR2
（ 此步驟可以取與源庫不同的 ORACLE_SID ，也可以相同，本例子修改相同）
[oracle@bing 2016_10_02]$ echo $ORACLE_SID
ORA11GR2
[oracle@bing 2016_10_02]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Sun Oct 2 20:09:40 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> set dbid 237843809
executing command: SET DBID
4.
目標庫啟動到
nomount
模式
此時會報錯，原因是，目標庫沒有引數檔案，但也是能啟動到 nomount 模式，分配記憶體、啟動後臺程式
RMAN> startup nomount;
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initORA11GR2.ora'
starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started
Total System Global Area 1068937216 bytes
Fixed Size 2260088 bytes
Variable Size 281019272 bytes
Database Buffers 780140544 bytes
Redo Buffers
5517312 bytes
5.
恢復引數檔案
1）. 恢復的 pfile 檔案
RMAN> restore spfile to pfile '?/dbs/initORA11GR2.ora' from '/u01/app/FRA/ORA11GR2/ autobackup /2016_10_02/o1_mf_s_924205924_cz1x757l_.bkp';
Starting restore at 02-OCT-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/app/FRA/ORA11GR2/autobackup/2016_10_02/o1_mf_s_924205924_cz1x757l_.bkp
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 02-OCT-16
RMAN>
2）. 檢視恢復的 pfile 檔案
[oracle@bing ~]$ ls $ORACLE_HOME/dbs/initORA11GR2*
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initORA11GR2.ora
[oracle@bing ~]$
6.
編輯
pfile
引數檔案
1）. 檢視 pfile 引數檔案：
[oracle@bing dbs]$ cat initORA11GR2.ora
----------------------------------------------------------------------------
ORA11GR2.__db_cache_size=381681664
ORA11GR2.__java_pool_size=4194304
ORA11GR2.__large_pool_size=8388608
ORA11GR2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
ORA11GR2.__pga_aggregate_target=289406976
ORA11GR2.__sga_target=545259520
ORA11GR2.__shared_io_pool_size=0
ORA11GR2.__shared_pool_size=138412032
ORA11GR2.__streams_pool_size=4194304
*.audit_file_dest='/u01/app/oracle/admin/ORA11GR2/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/ORA11GR2/control01.ctl','/u01/app/oracle/oradata/ORA11GR2/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='ORA11GR2'
*.db_recovery_file_dest_size=3221225472
*.db_recovery_file_dest='/u01/app/FRA'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORA11GR2XDB)'
*.memory_target=833617920
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
[oracle@bing dbs]$
——————————————————————————————————————————
因為我之前取了與源庫相同的例項名 ORACLE_SID, 所以不需要修改 pfile 引數檔案；需要注意一點，即使改了 ORACLE_SID ，在修改 pfile 引數檔案時唯一不能改的是 db_name 的值！！！！！！
2）. 根據 pfile 檔案建立相應的目錄
[oracle@bing dbs]$ mkdir -p /u01/app/oracle/admin/ORA11GR2/adump
[oracle@bing dbs]$ mkdir -p /u01/app/oracle/oradata/ORA11GR2/
[oracle@bing
dbs]$ mkdir -p /u01/app/FRA
3）. 透過 pfile 啟動資料庫到 nomount 模式， 測試 pfile 是否有修改引數
[oracle@bing dbs]$ echo $ORACLE_SID
ORA11GR2
[oracle@bing dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun Oct 2 20:43:06 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SYS@ORA11GR2>startup nomount;
ORACLE instance started.
Total System Global Area 830930944 bytes
Fixed Size 2257800 bytes
Variable Size 503319672 bytes
Database Buffers 322961408 bytes
Redo Buffers 2392064 bytes
SYS@ORA11GR2>
SYS@ORA11GR2>show parameter pfile
NAME TYPE VALUE
------------------------------------ -----------
spfile
string
7.生成
spfile
SYS@ORA11GR2> create spfile from pfile;
File created.
SYS@ORA11GR2>shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
——以 spfile 引數檔案開啟例項 ORA11GR1(ORACLE_SID)
SYS@ORA11GR2> startup nomount;
ORACLE instance started.
Total System Global Area 830930944 bytes
Fixed Size 2257800 bytes
Variable Size 503319672 bytes
Database Buffers 322961408 bytes
Redo Buffers 2392064 bytes
SYS@ORA11GR2>
SYS@ORA11GR2>show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- -
spfile string /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/spfileORA11GR2.ora
8.還原控制檔案
還原 控制檔案 並啟動到 mount 模式
[oracle@bing adump]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Sun Oct 2 20:49:58 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA11GR2 (not mounted)
RMAN> restore controlfile from '/u01/app/FRA/ORA11GR2 /autobackup/ 2016_10_02/o1_mf_s_924205924_cz1x757l_.bkp';
Starting restore at 02-OCT-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/ORA11GR2/control01.ctl
output file name=/u01/app/oracle/oradata/ORA11GR2/control02.ctl
Finished restore at 02-OCT-16
——連線資料庫（ mount 狀態）
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
9. 還原資料檔案set newname操作
（即將資料檔案轉換路徑到 /u01/app/oracle/oradata/ORA11GR2/ 下）
RMAN> run{ set newname for datafile 1 to '/u01/app/oracle/oradata/ORA11GR2/system01.dbf';
2> set newname for datafile 2 to '/u01/app/oracle/oradata/ORA11GR2/sysaux01.dbf';
3> set newname for datafile 3 to '/u01/app/oracle/oradata/ORA11GR2/undotbs01.dbf';
4> set newname for datafile 4 to '/u01/app/oracle/oradata/ORA11GR2/users01.dbf';
5> set newname for datafile 5 to '/u01/app/oracle/oradata/ORA11GR2/example01.dbf';
6> restore database;
7> switch datafile all;
8> recover database; }
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 02-OCT-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/ORA11GR2/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/ORA11GR2/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/ORA11GR2/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/ORA11GR2/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/ORA11GR2/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/FRA/ORA11GR2/backupset/2016_10_02/o1_mf_nnndf_TAG20161002T195139_cz1x6crk_.bkp
channel ORA_DISK_1: piece handle=/u01/app/FRA/ORA11GR2/backupset/2016_10_02/o1_mf_nnndf_TAG20161002T195139_cz1x6crk_.bkp tag=TAG20161002T195139
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 02-OCT-16
Starting recover at 02-OCT-16
using channel ORA_DISK_1
starting media recovery
unable to find archived log
archived log thread=1 sequence=14
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/02/2016 21:16:09
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 14 and s tarting SCN of 1199408
-- 此處我們會發現，在 recover 的時候，由於沒有歸檔日誌，所以，提示只能恢復到 SCN 1199408
解決：
RMAN> run{
2> set until scn 1199408;
3> restore database;
4> switch datafile all;
5> recover database;
6> }
executing command: SET until clause
Starting restore at 02-OCT-16
using channel ORA_DISK_1
skipping datafile 1; already restored to file /u01/app/oracle/oradata/ORA11GR2/system01.dbf
skipping datafile 2; already restored to file /u01/app/oracle/oradata/ORA11GR2/sysaux01.dbf
skipping datafile 3; already restored to file /u01/app/oracle/oradata/ORA11GR2/undotbs01.dbf
skipping datafile 4; already restored to file /u01/app/oracle/oradata/ORA11GR2/users01.dbf
skipping datafile 5; already restored to file /u01/app/oracle/oradata/ORA11GR2/example01.dbf
restore not done; all files read only, offline, or already restored
Finished restore at 02-OCT-16
Starting recover at 02-OCT-16
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
10. resetlogs 方式開啟資料庫
RMAN> alter database open resetlogs;
database opened
——驗證：
SYS@ORA11GR2>select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
ORA11GR2
OPEN
11. 收尾工作
此時已經
open
了資料庫，
再驗證
臨時表空間資料檔案
及日誌檔案
：
SYS@ORA11GR2> select file_name from dba_temp_files;
FILE_NAME
----------------------------------------------------------------------------
/u01/app/oracle/oradata/ORA11GR2/temp01.dbf
SYS@ORA11GR2> select member from v$logfile;
MEMBER
----------------------------------------------------------------------------
/u01/app/oracle/oradata/ORA11GR2/redo03.log
/u01/app/oracle/oradata/ORA11GR2/redo02.log
/u01/app/oracle/oradata/ORA11GR2/redo01.log
SYS@ORA11GR2> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ----------
cell_offloadgroup_name string
db_file_name_convert string
db_name string ORA11GR2
db_unique_name string ORA11GR2
global_names boolean FALSE
instance_name string ORA11GR2
lock_name_space string
log_file_name_convert string
processor_group_name string
service_names string ORA11GR2
SYS@ORA11GR2>
注意：當改了例項名後，則恢復過程有一些不同，最後收尾也不同，需注意！！！！！！！！！！！！！！！！！！
注意：在open resetlogs前都需要檢查檔案位置是否正確，尤其是redo檔案的位置
alter database rename file '/u01/oradata/orcl/temp01.dbf' to '/u01/oradata/testdb/temp01.dbf';
alter database rename file '/u01/oradata/orcl/redo03.log' to '/u01/oradata/testdb/redo03.log';
alter database rename file '/u01/oradata/orcl/redo02.log' to '/u01/oradata/testdb/redo02.log';
alter database rename file '/u01/oradata/orcl/redo01.log' to '/u01/oradata/testdb/redo01.log';
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31397003/viewspace-2126552/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【備份恢復】下：還原和恢復資料庫（即將一個庫還原到另外一個庫）資料庫
- SQL Server 資料庫備份還原和資料恢復SQLServer資料庫資料恢復
- 【備份恢復】在 ARCHIVELOG 模式下執行資料庫還原和恢復操作（源庫備份源庫恢復）Hive模式資料庫
- 使用RMAN還原和恢復資料庫資料庫
- 將 SQL Server 資料庫還原到某個時點（完整恢復模式）SQLServer資料庫模式
- SQLSERVER完整資料庫還原（完整恢復模式）SQLServer資料庫模式
- 【備份恢復】閃回資料庫（四）基於可靠還原點閃回資料庫資料庫
- 【備份恢復】從備份恢復資料庫資料庫
- postgresql 使用pg_rman恢復還原資料庫SQL資料庫
- 使用儲存指令碼還原恢復資料庫指令碼資料庫
- 簡單恢復模式執行資料庫完整還原模式資料庫
- 完整恢復模式下執行資料庫完整還原模式資料庫
- 備份和恢復postgreSQL資料庫SQL資料庫
- DM7使用DMRMAN執行資料庫還原和恢復資料庫
- oracle資料恢復還原Oracle資料恢復
- RMAN備份恢復整個庫
- 「MySQL」資料庫備份和還原MySql資料庫
- 批量備份和還原資料庫資料庫
- 寫一個遠端資料庫備份＆還原的javabean資料庫JavaBean
- Oracle12c多租戶資料庫備份與恢復 - 恢復一個PDBOracle資料庫
- 資料庫備份與恢復----第一課資料庫
- 跨平臺還原、恢復資料庫(Windows->Linux)資料庫WindowsLinux
- 簡單恢復模式下執行資料庫完整還原模式資料庫
- 備份與恢復：polardb資料庫備份與恢復資料庫
- MSSQL 備份資料庫還原SQL資料庫
- 資料庫資料的恢復和備份資料庫
- PostgreSql資料庫的備份和恢復SQL資料庫
- Mongo 資料庫備份和恢復命令Go資料庫
- 備份和恢復SQL Server資料庫SQLServer資料庫
- 在DG備庫備份資料庫並恢復到一個主機上，報錯RMAN-06820資料庫
- Mysql備份和還原資料庫-mysqldumpMySql資料庫
- 2 Day DBA-管理方案物件-執行備份和恢復-備份資料庫-為還原操作驗證備份物件資料庫
- 【Mysql】innobackupex備份還原單個庫MySql
- 【備份恢復】noarchive模式下使用增量備份恢復資料庫Hive模式資料庫
- 【RMAN】利用備份片還原資料庫(上)資料庫
- rman資料庫全庫備份與恢復資料庫
- 備份與恢復系列 八 丟失所有資料檔案的還原與恢復
- 達夢資料庫備份恢復資料庫