【備份恢復】noarchive模式下使用增量備份恢復資料庫
使用增量備份恢復處於NOARCHIVELOG 模式的資料庫
增量備份前提是有0即全庫備份,另外預設的增量備份是指增量差異備份(backup as backupset incremental level 1database),如果要改為增量累計備份還需要加命令cumulative。
1.撤銷資料庫archivelog模式
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 noarchivelog;
Database altered.
SYS@ORA11GR2>archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 3
Current log sequence 5
SYS@ORA11GR2>alter database open;
Database altered.
2.建立測試用表及資料
SYS@ORA11GR2>conn scott/tiger
Connected.
SCOTT@ORA11GR2>create table t(x int) tablespace users;
Table created.
SCOTT@ORA11GR2>insert into t values(1);
1 row created.
SCOTT@ORA11GR2>commit;
Commit complete.
3.在noarchivelog模式下,0級備份
[oracle@wang ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Sat Oct 8 12:47:03 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA11GR2 (DBID=237843809)
RMAN> shutdown immediate;
using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down
RMAN> startup mount;
(RMAN備份,位於mount下是將備份資訊記錄在控制檔案裡,控制檔案資料檔案庫)
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 730714112 bytes
Fixed Size 2256832 bytes
Variable Size 402653248 bytes
Database Buffers 322961408 bytes
Redo Buffers 2842624 bytes
——執行0級增量備份(預設是以備份集形式備份的)
RMAN> backup as backupset incremental level 0 database;
Starting backup at 08-OCT-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
channel ORA_DISK_1: starting incremental level 0 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/oracle/product/11.2.0/dbhome_1/dbs/01rhria3_1_1 tag=TAG20161008T124903 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:36
channel ORA_DISK_1: starting incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
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/oracle/product/11.2.0/dbhome_1/dbs/02rhrid4_1_1 tag=TAG20161008T124903 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 08-OCT-16
——啟庫:
RMAN> alter database open;
database opened
4.執行操作:
SCOTT@ORA11GR2>insert into t values(2);
1 row created.
SCOTT@ORA11GR2>commit;
Commit complete.
5.在noarchivelog模式下,1級備份
RMAN> run{
2> shutdown immediate;
3> startup mount;
4> backup as backupset incremental level 1 database;
5> alter database open;
6> }
database closed
database dismounted
Oracle instance shut down
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 730714112 bytes
Fixed Size 2256832 bytes
Variable Size 402653248 bytes
Database Buffers 322961408 bytes
Redo Buffers 2842624 bytes
Starting backup at 08-OCT-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
channel ORA_DISK_1: starting incremental level 1 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/oracle/product/11.2.0/dbhome_1/dbs/03rhriha_1_1 tag=TAG20161008T125257 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
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/oracle/product/11.2.0/dbhome_1/dbs/04rhriie_1_1 tag=TAG20161008T125257 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 08-OCT-16
database opened
6.刪除所有控制檔案、日誌檔案、資料檔案、引數檔案
[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]$
[oracle@wang ORA11GR2]$ cd $ORACLE_HOME/dbs
[oracle@wang dbs]$ ls spfileORA11GR2.ora
spfileORA11GR2.ora
[oracle@wang dbs]$ rm spfileORA11GR2.ora
[oracle@wang dbs]$ ls spfileORA11GR2.ora
ls: spfileORA11GR2.ora: No such file or directory
[oracle@wang dbs]$
7.執行恢復
[oracle@wang dbs]$ echo $ORACLE_SID
ORA11GR2
[oracle@wang dbs]$ export ORACLE_SID=ORA
[oracle@wang dbs]$ echo $ORACLE_SID
ORA
[[oracle@wang dbs]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Sat Oct 8 12:57:29 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
--強制啟動到nomount模式(rman優勢,即在無引數檔案狀態下可以啟動到nomount)
RMAN> startup force 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/initORA.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
--恢復引數檔案,最好使用最後一次增量備份的備份介質
RMAN> restore spfile from '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/04rhriie_1_1';
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/oracle/product/11.2.0/dbhome_1/dbs/04rhriie_1_1
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 08-OCT-16
--完成引數檔案的恢復,利用恢復的引數檔案啟動到nomount模式
RMAN> shutdown abort
Oracle instance shut down
RMAN> startup nomount;
(開啟例項)
connected to target database (not started)
Oracle instance started
Total System Global Area 730714112 bytes
Fixed Size 2256832 bytes
Variable Size 482345024 bytes
Database Buffers 243269632 bytes
Redo Buffers 2842624 bytes
--恢復控制檔案,注意:此處恢復控制檔案,一定要使用最後一次增量備份的備份介質,否則會因為版本的問題導致恢復出現問題
RMAN> restore controlfile from '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/04rhriie_1_1';
Starting restore at 08-OCT-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 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;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 10/08/2016 13:04:48
ORA-01102: cannot mount database in EXCLUSIVE mode
——解決:
[oracle@wang ~]$ ps -ef |grep ORA11GR2|awk '{print $2}' | xargs kill -9
kill 30985: No such process
[oracle@wang ~]$
——再次啟動資料庫到mount:
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
--執行全庫的還原
RMAN> restore database;
Starting restore at 08-OCT-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
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/oracle/product/11.2.0/dbhome_1/dbs/01rhria3_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/01rhria3_1_1 tag=TAG20161008T124903
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 08-OCT-16
--由於非歸檔,且聯機重做日誌丟失,所以使用noredo子句進行恢復
RMAN> recover database noredo;
Starting recover at 08-OCT-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/app/oracle/oradata/ORA11GR2/system01.dbf
destination for restore of datafile 00002: /u01/app/oracle/oradata/ORA11GR2/sysaux01.dbf
destination for restore of datafile 00003: /u01/app/oracle/oradata/ORA11GR2/undotbs01.dbf
destination for restore of datafile 00004: /u01/app/oracle/oradata/ORA11GR2/users01.dbf
destination for restore of datafile 00005: /u01/app/oracle/oradata/ORA11GR2/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/11.2.0/dbhome_1/dbs/03rhriha_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/03rhriha_1_1 tag=TAG20161008T125257
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished recover at 08-OCT-16
--恢復完成,resetlogs方式開啟資料庫,完成所有檔案丟失的恢復
RMAN> alter database open resetlogs;
database opened
8.驗證在測試前(增量備份前)插入的資料
——先將spfile改名:
[oracle@wang ~]$ echo $ORACLE_SID
ORA11GR2
[oracle@wang ~]$ cd $ORACLE_HOME/dbs
[oracle@wang dbs]$ ls
hc_ORA11GR2.dat lkORA11GR2
hc_ORA.dat orapwORA11GR2
init.ora snapcf_ORA11GR2.f
initORA11GR2.ora spfileORA.ora
[oracle@wang dbs]$
[oracle@wang dbs]$ mv spfileORA.ora spfileORA11GR2.ora
[oracle@wang dbs]$ ls spfileORA11GR2.ora
spfileORA11GR2.ora
[oracle@wang dbs]$ ls spfileORA.ora
ls: spfileORA.ora: No such file or directory
——連線到資料庫:
[oracle@wang dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Oct 8 13:19:49 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SYS@ORA11GR2>startup
ORACLE instance started.
Total System Global Area 730714112 bytes
Fixed Size 2256832 bytes
Variable Size 402653248 bytes
Database Buffers 322961408 bytes
Redo Buffers 2842624 bytes
ORA-01102: cannot mount database in EXCLUSIVE mode
——解決:
[oracle@wang dbs]$ ps -ef |grep ORA|awk '{print $2}' | xargs kill -9
kill 31795: No such process
[oracle@wang dbs]$
——再次啟庫:
[oracle@wang dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Oct 8 13:22:01 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SYS@ORA11GR2>startup
ORACLE instance started.
Total System Global Area 730714112 bytes
Fixed Size 2256832 bytes
Variable Size 402653248 bytes
Database Buffers 322961408 bytes
Redo Buffers 2842624 bytes
Database mounted.
Database opened.
SYS@ORA11GR2>
——驗證:
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>conn scott/tiger
Connected.
SCOTT@ORA11GR2>select * from t;
X
----------
1
2
驗證資料都回來!!!!!!!!!!!!!
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/31397003/viewspace-2126550/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- NOARCHIVELOG 模式下使用增量備份恢復資料庫Hive模式資料庫
- 【備份恢復】從備份恢復資料庫資料庫
- 使用innobackupex線上增量備份和再增量備份及恢復mysql資料庫MySql資料庫
- 備份與恢復:polardb資料庫備份與恢復資料庫
- 【備份恢復】在 ARCHIVELOG 模式下執行資料庫還原和恢復操作(源庫備份源庫恢復)Hive模式資料庫
- mysql innobackupex增量備份恢復MySql
- 備份&恢復之四:非歸檔模式下的備份與恢復模式
- Oracle資料庫備份與恢復之三:OS備份/使用者管理的備份與恢復Oracle資料庫
- 【備份恢復】Oracle 資料備份與恢復微實踐Oracle
- 【備份恢復】資料恢復指導資料恢復
- oracle 增量備份恢復驗證Oracle
- 【MySql】innobackupex增量備份和恢復MySql
- 【MySql】innobackupex 增量備份和恢復MySql
- mysql之 Innobackupex(全備+增量)備份恢復MySql
- 【管理篇備份恢復】備份恢復基礎
- 達夢資料庫備份恢復資料庫
- postgresql備份與恢復資料庫SQL資料庫
- mongo資料庫備份與恢復Go資料庫
- 資料庫的備份與恢復資料庫
- Informix資料庫備份與恢復ORM資料庫
- 備份和恢復postgreSQL資料庫SQL資料庫
- Oracle備份恢復之熱備份恢復及異機恢復Oracle
- 使用Mysqldump備份和恢復MySQL資料庫MySql資料庫
- 【備份恢復】無備份線上恢復非關鍵資料檔案
- 【備份恢復】RMAN catalog 恢復目錄資料庫資料庫
- mysql 利用binlog增量備份、恢復MySql
- MySQL增量備份與恢復例項MySql
- 簡單恢復模式下的備份模式
- 【Xtrabackup】Xtrabackup全備、增量備份及恢復示例
- 資料庫備份與異機恢復——熱備份方式資料庫
- 【物理熱備】(下)備份恢復系統表空間 手工備份恢復
- 【備份恢復】歸檔模式下丟失系統關鍵資料檔案 利用RMAN備份恢復模式
- 資料庫資料的恢復和備份資料庫
- 備份與恢復--從備份的歸檔日誌中恢復資料
- 2 Day DBA-管理方案物件-執行備份和恢復-備份資料庫-資料檔案增量備份物件資料庫
- PostgreSql資料庫的備份和恢復SQL資料庫
- Mongo 資料庫備份和恢復命令Go資料庫
- 直接透過備份恢復資料庫資料庫