程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> 如何用Dummy實例執行數據庫的還原和恢復,dummy實例執行數據庫

如何用Dummy實例執行數據庫的還原和恢復,dummy實例執行數據庫

編輯:Oracle教程

如何用Dummy實例執行數據庫的還原和恢復,dummy實例執行數據庫


今天實驗了一下,如何在所有文件,包括數據文件,在線日志文件,控制文件都丟失的情況下,利用RMAN備份恢復和還原數據庫。該實驗的重點是用到了Dummy實例。

具體步驟如下:

備份數據庫

[oracle@node2 ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Mon May 25 23:25:51 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TEST (DBID=2176055307)

RMAN> backup database;

Starting backup at 25-MAY-15
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: 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/test/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/test/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/test/undotbs01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/test/users02.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/test/undotbs02.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/test/users01.dbf
channel ORA_DISK_1: starting piece 1 at 25-MAY-15
channel ORA_DISK_1: finished piece 1 at 25-MAY-15
piece handle=/u01/app/oracle/fast_recovery_area/TEST/backupset/2015_05_25/o1_mf_nnndf_TAG20150525T232624_bp6hs0oo_.bkp tag=TAG20150525T232624 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:01
channel ORA_DISK_1: starting full 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 25-MAY-15
channel ORA_DISK_1: finished piece 1 at 25-MAY-15
piece handle=/u01/app/oracle/fast_recovery_area/TEST/backupset/2015_05_25/o1_mf_ncsnf_TAG20150525T232624_bp6hx32w_.bkp tag=TAG20150525T232624 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08
Finished backup at 25-MAY-15

RMAN> quit

模擬數據丟失

[oracle@node2 ~]$ cd /u01/app/oracle/oradata/test
[oracle@node2 test]$ ls
control01.ctl  redo01.log  redo03.log  sysaux01.dbf  temp01.dbf     undotbs02.dbf  users02.dbf
control01.dbf  redo02.log  redo04.log  system01.dbf  undotbs01.dbf  users01.dbf
[oracle@node2 test]$ rm ./*

試著登錄數據庫查詢數據,會報以下錯誤:

[oracle@node2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon May 25 23:35:49 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected.
SQL> select * from scott.dept;
select * from scott.dept
*
ERROR at line 1:
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0

看看ORACLE相關進程,會發現實例並沒有奔潰,進程還在

[oracle@node2 ~]$ ps -ef |grep ora
root      1362  1320  0 19:25 ?        00:00:08 hald-addon-storage: polling /dev/sr0 (every 2 sec)
root      2809  1384  0 22:22 ?        00:00:00 sshd: oracle [priv]
oracle    2811  2809  0 22:22 ?        00:00:01 sshd: oracle@pts/7
oracle    2812  2811  0 22:22 pts/7    00:00:00 -bash
root      3006  1384  0 22:31 ?        00:00:00 sshd: oracle [priv]
oracle    3008  3006  0 22:31 ?        00:00:00 sshd: oracle@pts/1
oracle    3009  3008  0 22:31 pts/1    00:00:00 -bash
root      3120  1384  0 22:34 ?        00:00:00 sshd: oracle [priv]
oracle    3122  3120  0 22:34 ?        00:00:00 sshd: oracle@pts/8
oracle    3123  3122  0 22:34 pts/8    00:00:00 -bash
root      3482  1384  0 22:57 ?        00:00:00 sshd: oracle [priv]
oracle    3484  3482  0 22:57 ?        00:00:01 sshd: oracle@pts/3
oracle    3485  3484  0 22:57 pts/3    00:00:00 -bash
root      3566  1384  0 23:01 ?        00:00:00 sshd: oracle [priv]
oracle    3568  3566  0 23:01 ?        00:00:00 sshd: oracle@pts/9
oracle    3569  3568  0 23:01 pts/9    00:00:00 -bash
oracle    4035     1  0 23:23 ?        00:00:00 ora_pmon_test
oracle    4037     1  0 23:23 ?        00:00:00 ora_psp0_test
oracle    4039     1  4 23:23 ?        00:00:32 ora_vktm_test
oracle    4043     1  0 23:23 ?        00:00:00 ora_gen0_test
oracle    4045     1  0 23:23 ?        00:00:00 ora_diag_test
oracle    4047     1  0 23:23 ?        00:00:00 ora_dbrm_test
oracle    4049     1  0 23:23 ?        00:00:00 ora_dia0_test
oracle    4051     1  0 23:23 ?        00:00:00 ora_mman_test
oracle    4053     1  0 23:23 ?        00:00:00 ora_dbw0_test
oracle    4055     1  0 23:23 ?        00:00:00 ora_lgwr_test
oracle    4057     1  0 23:23 ?        00:00:00 ora_ckpt_test
oracle    4059     1  0 23:23 ?        00:00:00 ora_smon_test
oracle    4061     1  0 23:23 ?        00:00:00 ora_reco_test
oracle    4063     1  0 23:23 ?        00:00:02 ora_mmon_test
oracle    4065     1  0 23:23 ?        00:00:00 ora_mmnl_test
oracle    4067     1  0 23:23 ?        00:00:00 ora_d000_test
oracle    4069     1  0 23:23 ?        00:00:00 ora_s000_test
oracle    4081     1  0 23:23 ?        00:00:00 ora_arc0_test
oracle    4083     1  0 23:23 ?        00:00:00 ora_arc1_test
oracle    4085     1  0 23:23 ?        00:00:00 ora_arc2_test
oracle    4087     1  0 23:23 ?        00:00:00 ora_arc3_test
oracle    4089     1  0 23:23 ?        00:00:00 ora_qmnc_test
oracle    4103     1  0 23:24 ?        00:00:01 ora_cjq0_test
oracle    4105     1  0 23:24 ?        00:00:00 ora_q000_test
oracle    4107     1  0 23:24 ?        00:00:00 ora_q001_test
oracle    4109     1  0 23:24 ?        00:00:05 ora_vkrm_test
oracle    4129     1  0 23:24 ?        00:00:00 ora_smco_test
oracle    4131     1  0 23:24 ?        00:00:00 ora_w000_test
oracle    4149     1  0 23:29 ?        00:00:00 ora_w001_test
oracle    4151     1  0 23:29 ?        00:00:00 ora_w002_test
oracle    4153     1  0 23:30 ?        00:00:00 ora_w003_test
oracle    4178  3123  0 23:35 pts/8    00:00:00 sqlplus   as sysdba
oracle    4186  4178  0 23:36 ?        00:00:00 oracletest (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    4187  3485  0 23:36 pts/3    00:00:00 ps -ef
oracle    4188  3485  0 23:36 pts/3    00:00:00 grep ora

關閉數據庫

SQL> shutdown abort
ORACLE instance shut down.

現在開始恢復數據庫,在這裡會借助Oracle的dummy實例,即啞實例。

[oracle@node2 ~]$ export ORACLE_SID=dummy
[oracle@node2 ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Mon May 25 23:44:28 2015

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.1/db_1/dbs/initdummy.ora'

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area    1071333376 bytes

Fixed Size                     1369420 bytes
Variable Size                281021108 bytes
Database Buffers             784334848 bytes
Redo Buffers                   4608000 bytes

RMAN> restore spfile to '/u01/app/oracle/product/11.2.0.1/db_1/dbs/spfiletest.ora' from '/u01/app/oracle/fast_recovery_area/TEST/backupset/2015_05_25/o1_mf_ncsnf_TAG20150525T232624_bp6hx32w_.bkp';

Starting restore at 25-MAY-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=171 device type=DISK

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/app/oracle/fast_recovery_area/TEST/backupset/2015_05_25/o1_mf_ncsnf_TAG20150525T232624_bp6hx32w_.bkp
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 25-MAY-15

RMAN> shutdown immediate

Oracle instance shut down

在這裡,我們將spfile恢復到實例默認的目錄,即$ORACLE_HOME/dbs,采用的是默認值,即spfile實例名.ora。這樣,在將數據庫啟動到nomount狀態下,直接startup nomount即可。

恢復控制文件

[oracle@node2 ~]$ export ORACLE_SID=test
[oracle@node2 ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Mon May 25 23:54:50 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

RMAN> startup nomount

Oracle instance started

Total System Global Area     313860096 bytes

Fixed Size                     1364340 bytes
Variable Size                268439180 bytes
Database Buffers              37748736 bytes
Redo Buffers                   6307840 bytes

RMAN> restore controlfile from '/u01/app/oracle/fast_recovery_area/TEST/backupset/2015_05_25/o1_mf_ncsnf_TAG20150525T232624_bp6hx32w_.bkp';

Starting restore at 26-MAY-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=136 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/test/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/test/control02.ctl
Finished restore at 26-MAY-15

注意,要重新設置ORACLE_SID的值,不然啟動的依舊是dummy實例。

將數據庫啟動到mount階段

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

還原和恢復數據庫

RMAN> restore database;

Starting restore at 26-MAY-15
Starting implicit crosscheck backup at 26-MAY-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=136 device type=DISK
Crosschecked 4 objects
Finished implicit crosscheck backup at 26-MAY-15

Starting implicit crosscheck copy at 26-MAY-15
using channel ORA_DISK_1
Finished implicit crosscheck copy at 26-MAY-15

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/TEST/backupset/2015_05_25/o1_mf_ncsnf_TAG20150525T232624_bp6hx32w_.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/test/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/test/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/test/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/test/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/test/users02.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/test/undotbs02.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/TEST/backupset/2015_05_25/o1_mf_nnndf_TAG20150525T232624_bp6hs0oo_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/TEST/backupset/2015_05_25/o1_mf_nnndf_TAG20150525T232624_bp6hs0oo_.bkp tag=TAG20150525T232624
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:06
Finished restore at 26-MAY-15

RMAN> recover database;

Starting recover at 26-MAY-15
using channel ORA_DISK_1

starting media recovery

unable to find archived log
archived log thread=1 sequence=1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/26/2015 00:19:20
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 1 and starting SCN of 956515

RMAN> alter database open resetlogs;

database opened

至此,數據庫恢復完畢,從上面的最後一個命令alter database open resetlogs可以看出來,該恢復屬於不完全恢復,畢竟,在線日志文件在“模擬數據丟失”那一步已被刪除,完全恢復已不可能,同時,上述倒數第二步recover database提示所需的歸檔文件不存在,指的也是已被刪除的在線日志文件。在這裡,直接resetlogs即可。

總結:

1> 如果啟用了RMAN的自動備份功能configure autobackup on,在恢復spfile和controlfile那一步可直接執行restore spfile from autobackup和restore controlfile from autobackup,不用上述那麼麻煩,需要指定備份集。當然,在這種情況下,需指定dbid。

2> OCP官方教材裡面其實也提供了在一切都丟失的情況下,如何執行數據庫的還原和恢復的腳本,具體如下:

RMAN> run{startup nomount pfile=dummy.pfile;
2> set dbid=2176055307;
3> restore spfile from autobackup;
4> shutdown abort;
5> startup nomount;
6> restore controlfile from autobackup;
7> alter database mount;
8> restore database;
9> recover database;
10> alter database open resetlog;}

關於這個腳本,說明如下:

第1行,使用只有一個參數(DB_NAME)的啞參數文件啟動實例

第2行,告訴RMAN所使用的數據庫的DBID

第3行,根據默認目錄和默認值,從最近的自動備份集中提取spfile

第4行,關閉啞實例

第5行,利用還原的spfile啟動實例

第6行,從最近的備份集中提取控制文件

第7行,加載控制文件

第8行,還原所有數據文件

第9行,通過應用增量備份以及歸檔日志文件和聯機日志文件執行完整恢復。

第10行,打開數據庫並重新初始化在線重做日志文件,在還原控制文件後總是需要resetlogs。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved