【備份恢復】在 ARCHIVELOG 模式下執行資料庫還原和恢復操作（源庫備份源庫恢復）
在 ARCHIVELOG 模式下執行資料庫還原和恢復操作
本實驗是在一個庫中操作備份，然後刪除三大檔案，最後利用rman備份進行恢復資料庫操作
1.檢視歸檔與否：
SYS@ORA11GR2>archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence 14
Current log sequence 16
SYS@ORA11GR2>show parameter recover
NAME TYPE VALUE
------------------------------------ -----------
db_recovery_file_dest string
db_recovery_file_dest_size big integer 0
db_unrecoverable_scn_tracking boolean TRUE
recovery_parallelism integer 0
SYS@ORA11GR2>
SYS@ORA11GR2>alter system set db_recovery_file_dest_size=3g;
System altered.
SYS@ORA11GR2>!mkdir -p /u01/app/FRA
SYS@ORA11GR2>alter system set db_recovery_file_dest='/u01/app/FRA';
System altered.
——設定歸檔為enable：
SYS@ORA11GR2>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ORA11GR2>
SYS@ORA11GR2>startup mount;
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
Database mounted.
SYS@ORA11GR2>
SYS@ORA11GR2>alter database archivelog;
Database altered.
SYS@ORA11GR2>archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 14
Next log sequence to archive 16
Current log sequence 16
SYS@ORA11GR2>alter database open;
Database altered.
2.將RMAN的自動備份控制檔案功能開啟：
（建議備份時也備份歸檔日誌，以實現完全恢復，否則則需進行不完全恢復）
[oracle@wang ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Sat Oct 8 14:44:58 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA11GR2 (DBID=237843809)
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORA11GR2 are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_ORA11GR2.f'; # default
RMAN> configure controlfile autobackup on;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
RMAN>
2.備份資料庫：
RMAN> backup database;
Starting backup at 08-OCT-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=49 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 08-OCT-16
channel ORA_DISK_1: finished piece 1 at 08-OCT-16
piece handle=/u01/app/FRA/ORA11GR2/backupset/2016_10_08/o1_mf_nnndf_TAG20161008T144726_czk5n060_.bkp tag=TAG20161008T144726 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:56
Finished backup at 08-OCT-16
Starting Control File and SPFILE Autobackup at 08-OCT-16
piece handle=/u01/app/FRA/ORA11GR2/autobackup/2016_10_08/o1_mf_s_924706105_czk5otjm_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 08-OCT-16
3.刪除所有控制檔案、資料檔案、日誌檔案，以及引數檔案：
[oracle@wang ~]$ cd /u01/app/oracle/oradata/ORA11GR2/
[oracle@wang ORA11GR2]$ ls
control01.ctl redo01.log sysaux01.dbf undotbs01.dbf
control02.ctl redo02.log system01.dbf users01.dbf
example01.dbf redo03.log temp01.dbf
[oracle@wang ORA11GR2]$ rm *
[oracle@wang ORA11GR2]$ ls
[oracle@wang ORA11GR2]$ cd $ORACLE_HOME/dbs
[oracle@wang dbs]$ ls spfileORA11GR2.ora initORA11GR2.ora
initORA11GR2.ora spfileORA11GR2.ora
[oracle@wang dbs]$ rm spfileORA11GR2.ora initORA11GR2.ora
[oracle@wang dbs]$ ls spfileORA11GR2.ora initORA11GR2.ora
ls: spfileORA11GR2.ora: No such file or directory
ls: initORA11GR2.ora: No such file or directory
[oracle@wang dbs]$
4.執行恢復：
1）修改環境變數$ORACLE_SID
[oracle@wang ~]$ echo $ORACLE_SID
ORA11GR2
[oracle@wang ~]$ export ORACLE_SID=SF
[oracle@wang ~]$ echo $ORACLE_SID
SF
2）登入RMAN(可以在沒有引數檔案的情況下登入例項)
[oracle@wang ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Sat Oct 8 14:56:11 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
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/initSF.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
3）恢復引數檔案：
RMAN> restore spfile to '$ORACLE_HOME/dbs/spfileORA11GR2.ora' from '/u01/app/FRA/ORA11GR2/autobackup/2016_10_08/o1_mf_s_924706105_czk5otjm_.bkp';
Starting restore at 08-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_08/o1_mf_s_924706105_czk5otjm_.bkp
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 08-OCT-16
——驗證：
[oracle@wang dbs]$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1/dbs
[oracle@wang dbs]$ ls spfileORA11GR2.ora
spfileORA11GR2.ora
4）關閉例項及重新以恢復的引數檔案（即ORA11GR2）登入到例項：
[oracle@wang ~]$ echo $ORACLE_SID
SF
[oracle@wang ~]$ export ORACLE_SID=ORA11GR2
[oracle@wang ~]$ echo $ORACLE_SID
ORA11GR2
[oracle@wang ~]$
——驗證可以登入到例項（以ORA11GR2）
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
5)繼續以RMAN恢復控制檔案：（重儲）
RMAN> restore controlfile from '/u01/app/FRA/ORA11GR2/autobackup/2016_10_08/o1_mf_s_924706105_czk5otjm_.bkp';
Starting restore at 08-OCT-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 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 08-OCT-16
——啟動資料庫到mount下：
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
6）恢復資料庫：（重儲資料庫）
RMAN> restore database;
Starting restore at 08-OCT-16
Starting implicit crosscheck backup at 08-OCT-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
Crosschecked 1 objects
Finished implicit crosscheck backup at 08-OCT-16
Starting implicit crosscheck copy at 08-OCT-16
using channel ORA_DISK_1
Finished implicit crosscheck copy at 08-OCT-16
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/FRA/ORA11GR2/autobackup/2016_10_08/o1_mf_s_924706105_czk5otjm_.bkp
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_08/o1_mf_nnndf_TAG20161008T144726_czk5n060_.bkp
channel ORA_DISK_1: piece handle=/u01/app/FRA/ORA11GR2/backupset/2016_10_08/o1_mf_nnndf_TAG20161008T144726_czk5n060_.bkp tag=TAG20161008T144726
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:15
Finished restore at 08-OCT-16
7）恢復資料庫（recover，因為是熱備資料庫且處於歸檔模式）
RMAN> recover database;
Starting recover at 08-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/08/2016 15:26:39
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 14 and starting SCN of 1201609
此處我們會發現，在recover的時候，由於備份歸檔日誌，所以，提示只能恢復到SCN 1201609
因為備份整庫時只是備份到當時的點，而且用的是舊版的控制檔案恢復資料庫，所以只能恢復到舊版控制為檔案記錄的最新scn
——解決：
RMAN> run{
2> SET UNTIL SCN 1201609;
3> RESTORE DATABASE;
4> SWITCH DATAFILE ALL;
5> RECOVER DATABASE;
6> }
executing command: SET until clause
Starting restore at 08-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 08-OCT-16
Starting recover at 08-OCT-16
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 08-OCT-16
——開啟資料庫：
RMAN> alter database open;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 10/08/2016 15:32:17
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
RMAN> alter database open resetlogs;（屬於不完全恢復）
database opened
8）驗證資料庫恢復回來了：
SYS@ORA11GR2>select status from v$instance;
STATUS
------------
OPEN
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>
SYS@ORA11GR2>!ls /u01/app/oracle/oradata/ORA11GR2/
control01.ctl example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf
control02.ctl redo01.log redo03.log system01.dbf undotbs01.dbf
源庫執行資料庫還原和恢復操作完成！！！！！！！！！！！！
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31397003/viewspace-2126537/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【備份恢復】從備份恢復資料庫資料庫
- 【備份恢復】noarchive模式下使用增量備份恢復資料庫Hive模式資料庫
- 【備份恢復】下：還原和恢復資料庫（即將一個庫還原到另外一個庫）資料庫
- 備份與恢復：polardb資料庫備份與恢復資料庫
- 備份和恢復postgreSQL資料庫SQL資料庫
- SQL Server 資料庫備份還原和資料恢復SQLServer資料庫資料恢復
- 【備份恢復】上：還原和恢復資料庫（即將一個庫還原到另外一個庫）資料庫
- 2 Day DBA-管理方案物件-執行備份和恢復-資料庫備份和恢復概念物件資料庫
- 2 Day DBA-管理方案物件-執行備份和恢復-資料庫備份和恢復概覽物件資料庫
- PostgreSql資料庫的備份和恢復SQL資料庫
- Mongo 資料庫備份和恢復命令Go資料庫
- 備份和恢復SQL Server資料庫SQLServer資料庫
- 資料庫資料的恢復和備份資料庫
- 達夢資料庫備份恢復資料庫
- postgresql備份與恢復資料庫SQL資料庫
- mongo資料庫備份與恢復Go資料庫
- 資料庫的備份與恢復資料庫
- Informix資料庫備份與恢復ORM資料庫
- 【備份恢復】RMAN catalog 恢復目錄資料庫資料庫
- 2 Day DBA-管理方案物件-執行備份和恢復-備份資料庫-為還原操作驗證備份物件資料庫
- rman資料庫全庫備份與恢復資料庫
- NOARCHIVELOG 模式下使用增量備份恢復資料庫Hive模式資料庫
- 【備份與恢復】archivelog模式中資料檔案的恢復Hive模式
- 使用Mysqldump備份和恢復MySQL資料庫MySql資料庫
- 非RMAN熱備份資料庫和恢復資料庫
- db2備份和恢復資料庫DB2資料庫
- 直接透過備份恢復資料庫資料庫
- mysql的資料庫備份與恢復MySql資料庫
- oracle資料庫的備份與恢復Oracle資料庫
- 資料庫備份與恢復技術資料庫
- 完整恢復模式下執行資料庫完整還原模式資料庫
- RMAN備份恢復整個庫
- 【備份恢復】資料恢復指導資料恢復
- Oracle資料庫備份與恢復之三：OS備份/使用者管理的備份與恢復Oracle資料庫
- 資料庫備份與異機恢復——熱備份方式資料庫
- DB2資料庫的備份和恢復DB2資料庫
- oracle資料庫備份和恢復的內容Oracle資料庫
- 在ARCHIVELOG模式中恢復資料庫Hive模式資料庫