本文模拟異機RMAN增量方式進行恢複資料庫
1.原庫建立表空間/temp表空間/使用者
create tablespace usergy datafile '/u01/app/oracle/oradata/orcl/usergy_data01.dbf’size 32m
autoextend on
next 32m maxsize 2048m
extent management local;
create temporary tablespace temp1
tempfile '/u01/app/oracle/oradata/orcl/user_temp1.dbf'
size 32m
autoextend on
next 32m maxsize 2048m
extent management local;
create user gy identified by gy default tablespace usergy temporary tablespace temp1;
grant connect,resource,create session to gy;
grant create any table to gy;
2.模拟資料點1.（建立表1）
sqlplus /nolog
conn gy/gy;
create table AA(id number(4) primary key,name varchar2(20),age number(4));
insert into AA values(1,‘Mrs wang’,25);
insert into AA values(2,‘Mr gui’,25);
commit;
檢查資料點
SQL> select * from AA;
ID NAME AGE
---------- -------------------- ----------
1 Mrs wang 25
2 Mr gui 25
SQL>
3.原庫資訊查詢
3.1資料檔案資訊
set linesize 300
col file_name for a90
select file_name from dba_data_files;
FILE_NAME
------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/system01.dbf
3.2 控制檔案資訊
SQL> show parameter control
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /u01/app/oracle/oradata/orcl/c
ontrol01.ctl, /u01/app/oracle/
oradata/orcl/control02.ctl
control_management_pack_access string DIAGNOSTIC+TUNING
SQL>
3.3 執行個體名/DB_NAME/DB_UNIQUE_NMAE
SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name string
db_file_name_convert string /u01/app/oracle/oradata/orcldg
, /u01/app/oracle/oradata/orcl
db_name string orcl
db_unique_name string ORCL
global_names boolean FALSE
instance_name string orcl
lock_name_space string
log_file_name_convert string /u01/app/oracle/oradata/orcldg
, /u01/app/oracle/oradata/orcl
processor_group_name string
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string ORCL
3.4 Onlinelog 資訊
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ------------------------------------------------------------------------------------------ ---
3 ONLINE /u01/app/oracle/oradata/orcl/redo03.log NO
2 ONLINE /u01/app/oracle/oradata/orcl/redo02.log NO
1 ONLINE /u01/app/oracle/oradata/orcl/redo01.log NO
4 STANDBY /u01/app/oracle/oradata/stdredo/redo04.log NO
5 STANDBY /u01/app/oracle/oradata/stdredo/redo05.log NO
6 STANDBY /u01/app/oracle/oradata/stdredo/redo06.log NO
7 STANDBY /u01/app/oracle/oradata/stdredo/redo07.log NO
7 rows selected.
SQL>
3.5 記錄dbid（恢複的controlfile中會記錄dbid，這裡我們可以提前記錄dbid）
SQL> select dbid from v$database;
DBID
----------
1509591956
SQL>
4.原庫level 0 備份
4.1 建立備份目錄
[[email protected] ~]# cd /
[[email protected] /]# mkdir backup
[r[email protected] /]# chown -R oracle:oinstall backup/
[[email protected] /]#
4.2 開始備份
#!/bin/bash
ORACLE_BASE=/u01/app/oracle
export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export ORACLE_HOME
ORACLE_SID=orcl
export ORACLE_SID
PATH=$ORACLE_HOME/bin:$PATH:$HOME/bin
export PATH
rman target / msglog=/backup/rman_bk_`date ''+%Y%m%d%H%M%S''`.log append <<EOF;
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
backup incremental level 0 tag 'db0' format '/backup/db0%u_%s_%p' database;
sql 'alter system archive log current';
backup format '/backup/arch%u_%s_%p' archivelog all; #備份歸檔可選，可以單獨定期備份
backup current controlfile format '/backup/ctl-%T-%U.bak';
release channel c1;
release channel c2;
release channel c3;
}
4.3 備份level0 日志輸出
[[email protected] ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Tue Dec 25 03:49:22 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1509591956)
RMAN> run{
2> allocate channel c1 type disk;
3> allocate channel c2 type disk;
4> allocate channel c3 type disk;
5> backup incremental level 0 tag 'db0' format '/backup/db0%u_%s_%p' database;
6> sql 'alter system archive log current';
7> backup format '/backup/arch%u_%s_%p' archivelog all; #備份歸檔可選，可以單獨定期備份
8> backup current controlfile format '/backup/ctl-%T-%U.bak';
9> release channel c1;
10> release channel c2;
11> release channel c3;
12> }
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=38 device type=DISK
allocated channel: c2
channel c2: SID=33 device type=DISK
allocated channel: c3
channel c3: SID=42 device type=DISK
Starting backup at 25-DEC-18
channel c1: starting incremental level 0 datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
channel c1: starting piece 1 at 25-DEC-18
channel c2: starting incremental level 0 datafile backup set
channel c2: specifying datafile(s) in backup set
input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
channel c2: starting piece 1 at 25-DEC-18
channel c3: starting incremental level 0 datafile backup set
channel c3: specifying datafile(s) in backup set
input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/usergy_data01.dbf
channel c3: starting piece 1 at 25-DEC-18
channel c3: finished piece 1 at 25-DEC-18
piece handle=/backup/db037tlkjap_103_1 tag=DB0 comment=NONE
channel c3: backup set complete, elapsed time: 00:00:03
channel c1: finished piece 1 at 25-DEC-18
piece handle=/backup/db035tlkjap_101_1 tag=DB0 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:15
channel c2: finished piece 1 at 25-DEC-18
piece handle=/backup/db036tlkjap_102_1 tag=DB0 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:15
Finished backup at 25-DEC-18
Starting Control File and SPFILE Autobackup at 25-DEC-18
piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/c-1509591956-20181225-0a comment=NONE
Finished Control File and SPFILE Autobackup at 25-DEC-18
sql statement: alter system archive log current
Starting backup at 25-DEC-18
current log archived
channel c1: starting archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=27 RECID=33 STAMP=995755963
input archived log thread=1 sequence=28 RECID=34 STAMP=995755968
input archived log thread=1 sequence=29 RECID=35 STAMP=995758248
input archived log thread=1 sequence=30 RECID=36 STAMP=995758248
input archived log thread=1 sequence=31 RECID=37 STAMP=995758248
input archived log thread=1 sequence=32 RECID=38 STAMP=995758248
input archived log thread=1 sequence=33 RECID=39 STAMP=995758285
input archived log thread=1 sequence=34 RECID=40 STAMP=995758285
input archived log thread=1 sequence=35 RECID=41 STAMP=995758285
input archived log thread=1 sequence=36 RECID=42 STAMP=995758285
input archived log thread=1 sequence=37 RECID=43 STAMP=995758307
channel c1: starting piece 1 at 25-DEC-18
channel c2: starting archived log backup set
channel c2: specifying archived log(s) in backup set
input archived log thread=1 sequence=38 RECID=44 STAMP=995762413
input archived log thread=1 sequence=39 RECID=45 STAMP=995762413
input archived log thread=1 sequence=40 RECID=46 STAMP=995762415
input archived log thread=1 sequence=41 RECID=47 STAMP=995762415
input archived log thread=1 sequence=42 RECID=48 STAMP=995762427
input archived log thread=1 sequence=43 RECID=49 STAMP=995766769
channel c2: starting piece 1 at 25-DEC-18
channel c3: starting archived log backup set
channel c3: specifying archived log(s) in backup set
input archived log thread=1 sequence=44 RECID=50 STAMP=995766769
input archived log thread=1 sequence=45 RECID=51 STAMP=995766816
input archived log thread=1 sequence=46 RECID=52 STAMP=995766816
input archived log thread=1 sequence=47 RECID=53 STAMP=995767134
input archived log thread=1 sequence=48 RECID=54 STAMP=995767134
input archived log thread=1 sequence=49 RECID=55 STAMP=995768744
input archived log thread=1 sequence=50 RECID=56 STAMP=995768745
input archived log thread=1 sequence=51 RECID=57 STAMP=995768845
input archived log thread=1 sequence=52 RECID=58 STAMP=995768846
input archived log thread=1 sequence=53 RECID=59 STAMP=995772539
input archived log thread=1 sequence=54 RECID=60 STAMP=995772539
channel c3: starting piece 1 at 25-DEC-18
channel c1: finished piece 1 at 25-DEC-18
piece handle=/backup/arch39tlkjba_105_1 tag=TAG20181225T035001 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
channel c1: starting archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=55 RECID=61 STAMP=995773608
input archived log thread=1 sequence=56 RECID=62 STAMP=995773608
input archived log thread=1 sequence=57 RECID=63 STAMP=995773801
input archived log thread=1 sequence=58 RECID=64 STAMP=995773801
channel c1: starting piece 1 at 25-DEC-18
channel c2: finished piece 1 at 25-DEC-18
piece handle=/backup/arch3atlkjba_106_1 tag=TAG20181225T035001 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:01
channel c3: finished piece 1 at 25-DEC-18
piece handle=/backup/arch3btlkjba_107_1 tag=TAG20181225T035001 comment=NONE
channel c3: backup set complete, elapsed time: 00:00:01
channel c1: finished piece 1 at 25-DEC-18
piece handle=/backup/arch3ctlkjbb_108_1 tag=TAG20181225T035001 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 25-DEC-18
Starting backup at 25-DEC-18
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
channel c1: starting piece 1 at 25-DEC-18
channel c1: finished piece 1 at 25-DEC-18
piece handle=/backup/ctl-20181225-3dtlkjbd_1_1.bak tag=TAG20181225T035005 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 25-DEC-18
Starting Control File and SPFILE Autobackup at 25-DEC-18
piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/c-1509591956-20181225-0b comment=NONE
Finished Control File and SPFILE Autobackup at 25-DEC-18
released channel: c1
released channel: c2
released channel: c3
RMAN>
5.原庫資料點2（建立表2）
[[email protected] backup]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 24 23:32:33 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn gy/gy;
Connected.
SQL>
SQL> create table BB(id number(4) primary key,name varchar2(20),age number(4));
Table created.
SQL> insert into BB values(1,'p',2);
insert into BB values(2,'o',3);
insert into BB values(3,'n',4);
insert into BB values(4,'m',5);
insert into BB values(5,'k',6);
insert into BB values(6,'j',7);
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL> insert into BB values(7,'i',8);
1 row created.
SQL> insert into BB values(8,'h',9);
1 row created.
SQL> insert into BB values(9,'g',0);
1 row created.
SQL> insert into BB values(10,'f',10);
1 row created.
SQL> insert into BB values(11,'e',11);
1 row created.
SQL> insert into BB values(12,'d',12);
1 row created.
SQL> insert into BB values(13,'c',13);
1 row created.
insert into BB values(14,'b',14);
1 row created.
SQL> insert into BB values(15,'a',15);
1 row created.
SQL>
SQL>
SQL> commit;
Commit complete.
SQL>
檢查表2
SQL> select * from BB;
ID NAME AGE
---------- -------------------- ----------
1 p 2
2 o 3
3 n 4
4 m 5
5 k 6
6 j 7
7 i 8
8 h 9
9 g 0
10 f 10
11 e 11
ID NAME AGE
---------- -------------------- ----------
12 d 12
13 c 13
14 b 14
15 a 15
15 rows selected.
SQL>
6.原庫為新庫建立參數檔案
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/spfileorcl.ora
SQL> create pfile='/backup/pfile_orcl_new.ora' from spfile;
File created.
SQL>
7.傳輸level0 備份集到新機器
[[email protected] /]# mkdir rman
[[email protected] /]# chown -R oracle:oinstall rman/
[[email protected] backup]$ scp arch_20181224_0* control_20181224_0gtlk474_1_1.ctl db_20181224_0* pfile_orcl_new.ora 192.168.56.95:/rman
The authenticity of host '192.168.56.95 (192.168.56.95)' can't be established.
RSA key fingerprint is 00:2e:0c:88:51:39:18:7d:df:89:9e:5d:c7:6e:36:05.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.56.95' (RSA) to the list of known hosts.
[email protected]'s password:
arch_20181224_08tlk46e_1_1 100% 36MB 36.1MB/s 00:01
arch_20181224_09tlk46e_1_1 100% 9082KB 8.9MB/s 00:00
arch_20181224_0atlk46f_1_1 100% 23KB 23.0KB/s 00:00
arch_20181224_0ftlk473_1_1 100% 11KB 11.0KB/s 00:00
control_20181224_0gtlk474_1_1.ctl 100% 9568KB 9.3MB/s 00:01
db_20181224_0btlk46h_1_1 100% 635MB 25.4MB/s 00:25
db_20181224_0ctlk46h_1_1 100% 381MB 23.8MB/s 00:16
db_20181224_0dtlk470_1_1 100% 9568KB 9.3MB/s 00:00
db_20181224_0etlk470_1_1 100% 96KB 96.0KB/s 00:00
pfile_orcl_new.ora 100% 1495 1.5KB/s 00:00
[[email protected] backup]
8.新庫開始恢複level0
8.1 修改.bash_profile
export ORACLE_SID=orcl
8.2 編輯pfile
[[email protected] rman]$ vi pfile_orcl_new.ora
---除去不必要參數，記憶體什麼的均可後續再調整
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='DB'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_unique_name='ORCL'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=3
*.open_cursors=300
*.pga_aggregate_target=291504128
*.processes=500
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=555
*.sga_target=874512384
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
8.3 建立新庫目錄（所有原庫需要的目錄）
mkdir -p /u01/app/oracle/admin/orcl/adump
8.4 使用該pfile啟動執行個體到nomount
[[email protected] rman]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Dec 25 14:31:21 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SYS@ orcl>startup nomount pfile='/home/oracle/pfile_orcl_new.ora';
ORACLE instance started.
Total System Global Area 872685568 bytes
Fixed Size 2258200 bytes
Variable Size 394267368 bytes
Database Buffers 469762048 bytes
Redo Buffers 6397952 bytes
SYS@ orcl>
8.5 新庫恢複控制檔案
[[email protected] ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Tue Dec 25 18:11:14 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database (not mounted)
RMAN> restore controlfile from '/rman/ctl-20181225-3dtlkjbd_1_1.bak';
Starting restore at 25-DEC-18
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 control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/orcl/control01.ctl
output file name=/u01/app/oracle/oradata/orcl/control02.ctl
Finished restore at 25-DEC-18
RMAN>
8.6 新庫啟動到mount
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN>
8.7 因為備份目錄不一緻，需要手動注冊
RMAN> CATALOG START WITH '/rman/';
searching for all files that match the pattern /rman/
List of Files Unknown to the Database
=====================================
File Name: /rman/arch3ctlkjbb_108_1
File Name: /rman/db037tlkjap_103_1
File Name: /rman/arch39tlkjba_105_1
File Name: /rman/db035tlkjap_101_1
File Name: /rman/db036tlkjap_102_1
File Name: /rman/arch3btlkjba_107_1
File Name: /rman/ctl-20181225-3dtlkjbd_1_1.bak
File Name: /rman/arch3atlkjba_106_1
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /rman/arch3ctlkjbb_108_1
File Name: /rman/db037tlkjap_103_1
File Name: /rman/arch39tlkjba_105_1
File Name: /rman/db035tlkjap_101_1
File Name: /rman/db036tlkjap_102_1
File Name: /rman/arch3btlkjba_107_1
File Name: /rman/ctl-20181225-3dtlkjbd_1_1.bak
File Name: /rman/arch3atlkjba_106_1
RMAN>
8.8 開始restore
RMAN> restore database;
Starting restore at 25-DEC-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 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 00003 to /u01/app/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/orcl/usergy_data01.dbf
channel ORA_DISK_1: reading from backup piece /backup/db037tlkjap_103_1
channel ORA_DISK_1: errors found reading piece handle=/backup/db037tlkjap_103_1
channel ORA_DISK_1: failover to piece handle=/rman/db037tlkjap_103_1 tag=DB0
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
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 00002 to /u01/app/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: reading from backup piece /backup/db036tlkjap_102_1
channel ORA_DISK_1: errors found reading piece handle=/backup/db036tlkjap_102_1
channel ORA_DISK_1: failover to piece handle=/rman/db036tlkjap_102_1 tag=DB0
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
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/orcl/system01.dbf
channel ORA_DISK_1: reading from backup piece /backup/db035tlkjap_101_1
channel ORA_DISK_1: errors found reading piece handle=/backup/db035tlkjap_101_1
channel ORA_DISK_1: failover to piece handle=/rman/db035tlkjap_101_1 tag=DB0
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 25-DEC-18
RMAN>
8.9 恢複新庫
RMAN> recover database;
Starting recover at 25-DEC-18
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=57
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=58
channel ORA_DISK_1: reading from backup piece /backup/arch3ctlkjbb_108_1
channel ORA_DISK_1: errors found reading piece handle=/backup/arch3ctlkjbb_108_1
channel ORA_DISK_1: failover to piece handle=/rman/arch3ctlkjbb_108_1 tag=TAG20181225T035001
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_57_981811220.dbf thread=1 sequence=57
archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_58_981811220.dbf thread=1 sequence=58
unable to find archived log
archived log thread=1 sequence=59
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/25/2018 18:34:42
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 59 and starting SCN of 1073654
RMAN>
原庫list backup 發現 并沒有備份到最新的歸檔59，
也就是說最新歸檔 59 的起始scn 是1073654。
RMAN> list backup ;
List of Archived Logs in backup set 94
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 55 1072085 25-DEC-18 1073347 25-DEC-18
1 56 1073347 25-DEC-18 1073355 25-DEC-18
1 57 1073355 25-DEC-18 1073646 25-DEC-18
1 58 1073646 25-DEC-18 1073654 25-DEC-18
這裡有幾種情況：
A.當使用level0 + leve1 方式恢複時（中間間隔或者需要開庫）建議使用read only方式在這裡直接開庫。
B.假如新庫就隻做到這，不需要追增，可以考慮使用recover database until scn 1073654 恢複然後resetlogs 開庫。
我了解的是這裡備份控制檔案時已經是 到 scn 1073654，而備份隻是到 1073653，故隻能不完全恢複至1053134。
RMAN> recover database until scn 1073654;
Starting recover at 25-DEC-18
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 25-DEC-18
RMAN> alter database open resetlogs;
database opened
RMAN>
這裡我們需要追增，故使用方式A。
檢查可用資料：
[[email protected] rman]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Dec 25 18:36:49 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@ orcl>alter database open read only;
Database altered.
SYS@ orcl>
SYS@ orcl>select status from v$instance;
STATUS
------------
OPEN
SYS@ orcl>conn gy/gy;
Connected.
GY@ orcl>select * from AA;
ID NAME AGE
---------- -------------------- ----------
1 Mrs wang 25
2 Mr gui 25
GY@ orcl>select * from BB;
select * from BB
*
ERROR at line 1:
ORA-00942: table or view does not exist
GY@ orcl>
9.原庫備份level1
9.1 備份腳本 level1
#!/bin/bash
ORACLE_BASE=/u01/app/oracle
export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export ORACLE_HOME
ORACLE_SID=orcl
export ORACLE_SID
PATH=$ORACLE_HOME/bin:$PATH:$HOME/bin
export PATH
rman target / msglog=/backup/rman_bk_`date ''+%Y%m%d%H%M%S''`.log append <<EOF;
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
backup incremental level 1 tag 'db1' format '/backup/level1/db1%u_%s_%p' database include current controlfile;
sql 'alter system archive log current';
backup format '/backup/level1/arch%u_%s_%p' archivelog all; #備份歸檔可選，可以單獨定期備份
backup current controlfile format '/backup/level1/ctl-%T-%U.bak';
release channel c1;
release channel c2;
release channel c3;
}
9.2 備份日志
[[email protected] ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Tue Dec 25 00:37:42 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1509591956)
RMAN> run{
2> allocate channel c1 type disk;
3> allocate channel c2 type disk;
4> allocate channel c3 type disk;
backup incremental level 1 tag 'db1' format '/backup/level1/db1%u_%s_%p' database include current controlfile;
6> sql 'alter system archive log current';
7> backup format '/backup/level1/arch%u_%s_%p' archivelog all; #備份歸檔可選，可以單獨定期備份
backup current controlfile format '/backup/level1/ctl-%T-%U.bak';
9> release channel c1;
10> release channel c2;
11> release channel c3;
12> }
allocated channel: c1
channel c1: SID=38 device type=DISK
allocated channel: c2
channel c2: SID=33 device type=DISK
allocated channel: c3
channel c3: SID=42 device type=DISK
Starting backup at 25-DEC-18
channel c1: starting incremental level 1 datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
channel c1: starting piece 1 at 25-DEC-18
channel c2: starting incremental level 1 datafile backup set
channel c2: specifying datafile(s) in backup set
input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
channel c2: starting piece 1 at 25-DEC-18
channel c3: starting incremental level 1 datafile backup set
channel c3: specifying datafile(s) in backup set
input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/usergy_data01.dbf
channel c3: starting piece 1 at 25-DEC-18
channel c3: finished piece 1 at 25-DEC-18
piece handle=/backup/level1/db13htlkjpv_113_1 tag=DB1 comment=NONE
channel c3: backup set complete, elapsed time: 00:00:03
channel c3: starting incremental level 1 datafile backup set
channel c3: specifying datafile(s) in backup set
channel c2: finished piece 1 at 25-DEC-18
piece handle=/backup/level1/db13gtlkjpv_112_1 tag=DB1 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:06
channel c1: finished piece 1 at 25-DEC-18
piece handle=/backup/level1/db13ftlkjpv_111_1 tag=DB1 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:08
including current control file in backup set
channel c3: starting piece 1 at 25-DEC-18
channel c3: finished piece 1 at 25-DEC-18
piece handle=/backup/level1/db13itlkjq4_114_1 tag=DB1 comment=NONE
channel c3: backup set complete, elapsed time: 00:00:01
Finished backup at 25-DEC-18
Starting Control File and SPFILE Autobackup at 25-DEC-18
piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/c-1509591956-20181225-0c comment=NONE
Finished Control File and SPFILE Autobackup at 25-DEC-18
sql statement: alter system archive log current
Starting backup at 25-DEC-18
current log archived
channel c1: starting archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=27 RECID=33 STAMP=995755963
input archived log thread=1 sequence=28 RECID=34 STAMP=995755968
input archived log thread=1 sequence=29 RECID=35 STAMP=995758248
input archived log thread=1 sequence=30 RECID=36 STAMP=995758248
input archived log thread=1 sequence=31 RECID=37 STAMP=995758248
input archived log thread=1 sequence=32 RECID=38 STAMP=995758248
input archived log thread=1 sequence=33 RECID=39 STAMP=995758285
input archived log thread=1 sequence=34 RECID=40 STAMP=995758285
input archived log thread=1 sequence=35 RECID=41 STAMP=995758285
input archived log thread=1 sequence=36 RECID=42 STAMP=995758285
input archived log thread=1 sequence=37 RECID=43 STAMP=995758307
input archived log thread=1 sequence=38 RECID=44 STAMP=995762413
channel c1: starting piece 1 at 25-DEC-18
channel c2: starting archived log backup set
channel c2: specifying archived log(s) in backup set
input archived log thread=1 sequence=39 RECID=45 STAMP=995762413
input archived log thread=1 sequence=40 RECID=46 STAMP=995762415
input archived log thread=1 sequence=41 RECID=47 STAMP=995762415
input archived log thread=1 sequence=42 RECID=48 STAMP=995762427
input archived log thread=1 sequence=43 RECID=49 STAMP=995766769
input archived log thread=1 sequence=44 RECID=50 STAMP=995766769
input archived log thread=1 sequence=45 RECID=51 STAMP=995766816
input archived log thread=1 sequence=46 RECID=52 STAMP=995766816
input archived log thread=1 sequence=47 RECID=53 STAMP=995767134
input archived log thread=1 sequence=48 RECID=54 STAMP=995767134
input archived log thread=1 sequence=49 RECID=55 STAMP=995768744
input archived log thread=1 sequence=50 RECID=56 STAMP=995768745
channel c2: starting piece 1 at 25-DEC-18
channel c3: starting archived log backup set
channel c3: specifying archived log(s) in backup set
input archived log thread=1 sequence=51 RECID=57 STAMP=995768845
input archived log thread=1 sequence=52 RECID=58 STAMP=995768846
input archived log thread=1 sequence=53 RECID=59 STAMP=995772539
input archived log thread=1 sequence=54 RECID=60 STAMP=995772539
input archived log thread=1 sequence=55 RECID=61 STAMP=995773608
input archived log thread=1 sequence=56 RECID=62 STAMP=995773608
input archived log thread=1 sequence=57 RECID=63 STAMP=995773801
input archived log thread=1 sequence=58 RECID=64 STAMP=995773801
input archived log thread=1 sequence=59 RECID=65 STAMP=995774281
input archived log thread=1 sequence=60 RECID=66 STAMP=995774281
channel c3: starting piece 1 at 25-DEC-18
channel c1: finished piece 1 at 25-DEC-18
piece handle=/backup/level1/arch3ktlkjqa_116_1 tag=TAG20181225T035801 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
channel c2: finished piece 1 at 25-DEC-18
piece handle=/backup/level1/arch3ltlkjqa_117_1 tag=TAG20181225T035801 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:01
channel c3: finished piece 1 at 25-DEC-18
piece handle=/backup/level1/arch3mtlkjqa_118_1 tag=TAG20181225T035801 comment=NONE
channel c3: backup set complete, elapsed time: 00:00:01
Finished backup at 25-DEC-18
Starting backup at 25-DEC-18
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
channel c1: starting piece 1 at 25-DEC-18
channel c1: finished piece 1 at 25-DEC-18
piece handle=/backup/level1/ctl-20181225-3ntlkjqb_1_1.bak tag=TAG20181225T035803 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 25-DEC-18
Starting Control File and SPFILE Autobackup at 25-DEC-18
piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/c-1509591956-20181225-0d comment=NONE
Finished Control File and SPFILE Autobackup at 25-DEC-18
released channel: c1
released channel: c2
released channel: c3
RMAN>
10. copy level1 備份集到新庫
[[email protected] level1]$ scp * 192.168.56.95:/rman/level1/
[email protected]'s password:
arch3ktlkjqa_116_1 100% 12MB 12.1MB/s 00:00
arch3ltlkjqa_117_1 100% 7014KB 6.9MB/s 00:01
arch3mtlkjqa_118_1 100% 4870KB 4.8MB/s 00:00
ctl-20181225-3ntlkjqb_1_1.bak 100% 10MB 9.9MB/s 00:00
db13ftlkjpv_111_1 100% 352KB 352.0KB/s 00:00
db13gtlkjpv_112_1 100% 232KB 232.0KB/s 00:00
db13htlkjpv_113_1 100% 232KB 232.0KB/s 00:00
db13itlkjq4_114_1 100% 10MB 9.9MB/s 00:01
[[email protected] level1]$
11.增量恢複
基于前面 level0 開庫的情況下。
RMAN> shutdown immediate;
database closed
database dismounted
Oracle instance shut down
RMAN>
11.1 使用最新controlfile
因為level0 的備份控制檔案沒有後續的記錄，故這裡需要用level1的備份控制檔案。
RMAN> startup nomount pfile='/home/oracle/pfile_orcl_new.ora';
connected to target database (not started)
Oracle instance started
Total System Global Area 872685568 bytes
Fixed Size 2258200 bytes
Variable Size 394267368 bytes
Database Buffers 469762048 bytes
Redo Buffers 6397952 bytes
RMAN> restore controlfile from '/rman/level1/ctl-20181225-3ntlkjqb_1_1.bak';
Starting restore at 25-DEC-18
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/orcl/control01.ctl
output file name=/u01/app/oracle/oradata/orcl/control02.ctl
Finished restore at 25-DEC-18
RMAN>
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
備注：（兩個備份目錄可以同時注冊兩次備份的路徑）
RMAN> CATALOG START WITH '/rman/level1/';
searching for all files that match the pattern /rman/level1/
List of Files Unknown to the Database
=====================================
File Name: /rman/level1/ctl-20181225-3ntlkjqb_1_1.bak
File Name: /rman/level1/arch3ktlkjqa_116_1
File Name: /rman/level1/arch3mtlkjqa_118_1
File Name: /rman/level1/db13htlkjpv_113_1
File Name: /rman/level1/arch3ltlkjqa_117_1
File Name: /rman/level1/db13ftlkjpv_111_1
File Name: /rman/level1/db13gtlkjpv_112_1
File Name: /rman/level1/db13itlkjq4_114_1
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /rman/level1/ctl-20181225-3ntlkjqb_1_1.bak
File Name: /rman/level1/arch3ktlkjqa_116_1
File Name: /rman/level1/arch3mtlkjqa_118_1
File Name: /rman/level1/db13htlkjpv_113_1
File Name: /rman/level1/arch3ltlkjqa_117_1
File Name: /rman/level1/db13ftlkjpv_111_1
File Name: /rman/level1/db13gtlkjpv_112_1
File Name: /rman/level1/db13itlkjq4_114_1
RMAN>
這裡先直接recover 。
RMAN> recover database;
Starting recover at 25-DEC-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
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 00003: /u01/app/oracle/oradata/orcl/undotbs01.dbf
destination for restore of datafile 00005: /u01/app/oracle/oradata/orcl/usergy_data01.dbf
channel ORA_DISK_1: reading from backup piece /backup/level1/db13htlkjpv_113_1
channel ORA_DISK_1: errors found reading piece handle=/backup/level1/db13htlkjpv_113_1
channel ORA_DISK_1: failover to piece handle=/rman/level1/db13htlkjpv_113_1 tag=DB1
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
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 00002: /u01/app/oracle/oradata/orcl/sysaux01.dbf
destination for restore of datafile 00004: /u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: reading from backup piece /backup/level1/db13gtlkjpv_112_1
channel ORA_DISK_1: errors found reading piece handle=/backup/level1/db13gtlkjpv_112_1
channel ORA_DISK_1: failover to piece handle=/rman/level1/db13gtlkjpv_112_1 tag=DB1
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
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/orcl/system01.dbf
channel ORA_DISK_1: reading from backup piece /backup/level1/db13ftlkjpv_111_1
channel ORA_DISK_1: errors found reading piece handle=/backup/level1/db13ftlkjpv_111_1
channel ORA_DISK_1: failover to piece handle=/rman/level1/db13ftlkjpv_111_1 tag=DB1
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=59
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=60
channel ORA_DISK_1: reading from backup piece /backup/level1/arch3mtlkjqa_118_1
channel ORA_DISK_1: errors found reading piece handle=/backup/level1/arch3mtlkjqa_118_1
channel ORA_DISK_1: failover to piece handle=/rman/level1/arch3mtlkjqa_118_1 tag=TAG20181225T035801
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_59_981811220.dbf thread=1 sequence=59
archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_60_981811220.dbf thread=1 sequence=60
unable to find archived log
archived log thread=1 sequence=61
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/25/2018 18:45:09
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 61 and starting SCN of 1074281
RMAN>
這裡同樣提示 seq 61 沒有備份過來。list backup 可以看到隻備份了seq 60 .
RMAN> list backup;
List of Archived Logs in backup set 103
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 51 1067252 25-DEC-18 1067465 25-DEC-18
1 52 1067465 25-DEC-18 1067473 25-DEC-18
1 53 1067473 25-DEC-18 1072077 25-DEC-18
1 54 1072077 25-DEC-18 1072085 25-DEC-18
1 55 1072085 25-DEC-18 1073347 25-DEC-18
1 56 1073347 25-DEC-18 1073355 25-DEC-18
1 57 1073355 25-DEC-18 1073646 25-DEC-18
1 58 1073646 25-DEC-18 1073654 25-DEC-18
1 59 1073654 25-DEC-18 1074273 25-DEC-18
1 60 1074273 25-DEC-18 1074281 25-DEC-18
故這裡我們使用基于scn 點的方式進行恢複；
recover database until scn 1074281;
RMAN> recover database until scn 1074281;
Starting recover at 25-DEC-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 25-DEC-18
RMAN>
RMAN> alter database open resetlogs;
database opened
RMAN>
12. 檢查
[[email protected] level1]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Dec 25 18:52:01 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@ orcl>show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name string
db_file_name_convert string
db_name string orcl
db_unique_name string ORCL
global_names boolean FALSE
instance_name string orcl
lock_name_space string
log_file_name_convert string
processor_group_name string
service_names string ORCL
SYS@ orcl>
SYS@ orcl>select status from v$instance;
STATUS
------------
OPEN
SYS@ orcl>conn gy/gy;
Connected.
GY@ orcl>select * from AA;
ID NAME AGE
---------- -------------------- ----------
1 Mrs wang 25
2 Mr gui 25
GY@ orcl>select * from BB;
ID NAME AGE
---------- -------------------- ----------
1 p 2
2 o 3
3 n 4
4 m 5
5 k 6
6 j 7
7 i 8
8 h 9
9 g 0
10 f 10
11 e 11
ID NAME AGE
---------- -------------------- ----------
12 d 12
13 c 13
14 b 14
15 a 15
15 rows selected.
GY@ orcl>
連結：
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/21/2014 01:28:11
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 7 and starting SCN of 2292709
在11g官方文檔error messages中對錯誤描述如下：
RMAN-06054: media recovery requesting unknown archived log for thread string with sequence string and starting SCN of string
Cause: Media recovery is requesting a log whose existence is not recorded in the recovery catalog or target database control file.
Action: If a copy of the log is available, then add it to the recovery catalog and/or control file via a CATALOG command and then retry the RECOVER command. If not, then a point-in-time recovery up to the missing log is the only alternative and database can be opened using ALTER DATABASE OPEN RESETLOGS command.
可見，出先此錯誤的原因是恢複需要的日志記錄在控制檔案或恢複目錄中找不到。解決方法分兩種情況：
1.如果相關的日志存在且可用的話，就将此日志記錄添加到控制檔案或恢複目錄中。
2.如果相關的日志已經被删除了或不可用了，那麼就按照錯誤的提示scn将資料庫恢複到此scn，本案例是1074281。也就是說此時資料庫隻能進行不完全恢複了，在打開資料庫時得使用resetlogs打開。
RMAN> recover database until scn 1074281;
Starting recover at 25-DEC-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 25-DEC-18
郵箱：[email protected]
昵稱：A這貨不是DBA
有些事不做就老了，有些人不學就懶了。
歡迎指正，共同學習！
![oracle中的start with connect by用法[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)