當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

oracle的scn增量备份,Oracle技术之利用scn增量备份实现数据库增量恢复

發布時間：2024/2/28 数据库 33 豆豆

生活随笔收集整理的這篇文章主要介紹了 oracle的scn增量备份,Oracle技术之利用scn增量备份实现数据库增量恢复小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

數據庫版本

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

PL/SQL Release 11.2.0.3.0 - Production

CORE ? ?11.2.0.3.0 ? ? ?Production

TNS for Linux: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

全備數據庫

RMAN> backup as compressed backupset database ?format '/u01/oracle/oradata/tmp/ora11g_0_%U';

Starting backup at 18-APR-12

using channel ORA_DISK_1

channel ORA_DISK_1: starting compressed full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/oracle/oradata/ora11g/system01.dbf

input datafile file number=00002 name=/u01/oracle/oradata/ora11g/sysaux01.dbf

input datafile file number=00003 name=/u01/oracle/oradata/ora11g/undotbs01.dbf

input datafile file number=00004 name=/u01/oracle/oradata/ora11g/users01.dbf

input datafile file number=00006 name=/u01/oracle/oradata/ora11g/xifenfei02.dbf

channel ORA_DISK_1: starting piece 1 at 18-APR-12

channel ORA_DISK_1: finished piece 1 at 18-APR-12

piece handle=/u01/oracle/oradata/tmp/ora11g_0_07n8p916_1_1 tag=TAG20120418T234958 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15

channel ORA_DISK_1: starting compressed 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 18-APR-12

channel ORA_DISK_1: finished piece 1 at 18-APR-12

piece handle=/u01/oracle/oradata/tmp/ora11g_0_08n8p93h_1_1 tag=TAG20120418T234958 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 18-APR-12

創建測試數據庫驗證恢復標準

SQL> conn chf/xifenfei

Connected.

SQL> drop table xifenfei purge;

Table dropped.

SQL> create table xifenfei as

2 ?select * from dba_objects;

Table created.

SQL> insert into xifenfei

2 ?select * from dba_objects;

74534 rows created.

SQL> /

74534 rows created.

SQL> /

74534 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from xifenfei;

COUNT(*)

----------

298136

SQL> create table xifenfei01 as

2 ?select * from dba_objects;

Table created.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> create table chf.xifenfei02 as

2 ?select * from dba_objects;

Table created.

SQL> alter system switch logfile;

System altered.

異機恢復庫

RMAN> restore controlfile from '/u01/oracle/oradata/tmp/ora11g_0_08n8p93h_1_1';

Starting restore at 19-APR-12

using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/u01/oracle/oradata/ora11g/control01.ctl

output file name=/u01/oracle/oradata/ora11g/control02.ctl

Finished restore at 19-APR-12

RMAN> alter database mount;

database mounted

released channel: ORA_DISK_1

RMAN> list backup;

List of Backup Sets

===================

BS Key ?Type LV Size ? ? ? Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

7 ? ? ? Full ? ?262.79M ? ?DISK ? ? ? ?00:01:08 ? ? 18-APR-12

BP Key: 7 ? Status: AVAILABLE ?Compressed: YES ?Tag: TAG20120418T234958

Piece Name: /u01/oracle/oradata/tmp/ora11g_0_07n8p916_1_1

List of Datafiles in backup set 7

File LV Type Ckp SCN ? ?Ckp Time ?Name

---- -- ---- ---------- --------- ----

1 ? ? ? Full 1155510 ? ?18-APR-12 /u01/oracle/oradata/ora11g/system01.dbf

2 ? ? ? Full 1155510 ? ?18-APR-12 /u01/oracle/oradata/ora11g/sysaux01.dbf

3 ? ? ? Full 1155510 ? ?18-APR-12 /u01/oracle/oradata/ora11g/undotbs01.dbf

4 ? ? ? Full 1155510 ? ?18-APR-12 /u01/oracle/oradata/ora11g/users01.dbf

6 ? ? ? Full 1155510 ? ?18-APR-12 /u01/oracle/oradata/ora11g/xifenfei02.dbf

--這里可以發現備份時的scn,增量備份時取這里的最小scn為起點

--為了排除影響,刪除所有歸檔日志記錄

RMAN> delete archivelog all;

RMAN> list archivelog all;

specification does not match any archived log in the repository

RMAN> restore database;

Starting restore at 19-APR-12

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/oracle/oradata/ora11g/system01.dbf

channel ORA_DISK_1: restoring datafile 00002 to /u01/oracle/oradata/ora11g/sysaux01.dbf

channel ORA_DISK_1: restoring datafile 00003 to /u01/oracle/oradata/ora11g/undotbs01.dbf

channel ORA_DISK_1: restoring datafile 00004 to /u01/oracle/oradata/ora11g/users01.dbf

channel ORA_DISK_1: restoring datafile 00006 to /u01/oracle/oradata/ora11g/xifenfei02.dbf

channel ORA_DISK_1: reading from backup piece /u01/oracle/oradata/tmp/ora11g_0_07n8p916_1_1

channel ORA_DISK_1: piece handle=/u01/oracle/oradata/tmp/ora11g_0_07n8p916_1_1 tag=TAG20120418T234958

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:01:36

Finished restore at 19-APR-12

[oracle@xifenfei oradata]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 19 00:54:42 2012

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select file#,to_char(checkpoint_change#,'9999999999999999') "SCN",

2 ?to_char(RESETLOGS_CHANGE#,'9999999999999999') "RESETLOGS SCN"

3 ?from v$datafile_header;

FILE# SCN ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?RESETLOGS SCN

---------- ---------------------------------- ----------------------------------

1 ? ? ? ? ? 1155510 ? ? ? ? ? ? ? ? ? ? ? ? ? ? 787897

2 ? ? ? ? ? 1155510 ? ? ? ? ? ? ? ? ? ? ? ? ? ? 787897

3 ? ? ? ? ? 1155510 ? ? ? ? ? ? ? ? ? ? ? ? ? ? 787897

4 ? ? ? ? ? 1155510 ? ? ? ? ? ? ? ? ? ? ? ? ? ? 787897

6 ? ? ? ? ? 1155510 ? ? ? ? ? ? ? ? ? ? ? ? ? ? 787897

SQL> select file#,to_char(checkpoint_change#,'999999999999999') "SCN",

2 ?to_char(last_change#,'999999999999999')"STOP_SCN" from v$datafile;

FILE# SCN ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?STOP_SCN

---------- -------------------------------- --------------------------------

1 ? ? ? ? ?1155510

2 ? ? ? ? ?1155510

3 ? ? ? ? ?1155510

4 ? ? ? ? ?1155510

6 ? ? ? ? ?1155510

基于scn增量備份

RMAN> BACKUP INCREMENTAL FROM SCN 1155510 DATABASE

2> FORMAT '/u01/oracle/oradata/tmp/ora11_scn_%U' tag 'XIFENFEI';

Starting backup at 19-APR-12

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=125 device type=DISK

backup will be obsolete on date 26-APR-12

archived logs will not be kept or backed up

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/oracle/oradata/ora11g/system01.dbf

input datafile file number=00002 name=/u01/oracle/oradata/ora11g/sysaux01.dbf

input datafile file number=00003 name=/u01/oracle/oradata/ora11g/undotbs01.dbf

input datafile file number=00004 name=/u01/oracle/oradata/ora11g/users01.dbf

input datafile file number=00006 name=/u01/oracle/oradata/ora11g/xifenfei02.dbf

channel ORA_DISK_1: starting piece 1 at 19-APR-12

channel ORA_DISK_1: finished piece 1 at 19-APR-12

piece handle=/u01/oracle/oradata/tmp/ora11_scn_0bn8pbsd_1_1 tag=XIFENFEI comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45

using channel ORA_DISK_1

backup will be obsolete on date 26-APR-12

archived logs will not be kept or backed up

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

channel ORA_DISK_1: starting piece 1 at 19-APR-12

channel ORA_DISK_1: finished piece 1 at 19-APR-12

piece handle=/u01/oracle/oradata/tmp/ora11_scn_0cn8pbtq_1_1 tag=XIFENFEI comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 19-APR-12

增量恢復

RMAN> catalog start with '/u01/oracle/oradata/tmp/ora11_scn_09n8pa5h_1_1';

searching for all files that match the pattern /u01/oracle/oradata/tmp/ora11_scn_09n8pa5h_1_1

List of Files Unknown to the Database

=====================================

File Name: /u01/oracle/oradata/tmp/ora11_scn_09n8pa5h_1_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: /u01/oracle/oradata/tmp/ora11_scn_09n8pa5h_1_1

RMAN> recover database;

Starting recover at 19-APR-12

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=125 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 00001: /u01/oracle/oradata/ora11g/system01.dbf

destination for restore of datafile 00002: /u01/oracle/oradata/ora11g/sysaux01.dbf

destination for restore of datafile 00003: /u01/oracle/oradata/ora11g/undotbs01.dbf

destination for restore of datafile 00004: /u01/oracle/oradata/ora11g/users01.dbf

destination for restore of datafile 00006: /u01/oracle/oradata/ora11g/xifenfei02.dbf

channel ORA_DISK_1: reading from backup piece /u01/oracle/oradata/tmp/ora11_scn_0bn8pbsd_1_1

channel ORA_DISK_1: piece handle=/u01/oracle/oradata/tmp/ora11_scn_0bn8pbsd_1_1.bak tag=XIFENFEI

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:07

starting media recovery

--這里表現出來的是利用歸檔日志恢復，實質是基于scn增量備份的備份集恢復

archived log file name=/u01/oracle/oradata/archivelog/ora11g/1_49_777766629.dbf thread=1 sequence=49

archived log file name=/u01/oracle/oradata/archivelog/ora11g/1_50_777766629.dbf thread=1 sequence=50

archived log file name=/u01/oracle/oradata/archivelog/ora11g/1_51_777766629.dbf thread=1 sequence=51

archived log file name=/u01/oracle/oradata/archivelog/ora11g/1_52_777766629.dbf thread=1 sequence=52

archived log file name=/u01/oracle/oradata/archivelog/ora11g/1_53_777766629.dbf thread=1 sequence=53

archived log file name=/u01/oracle/oradata/archivelog/ora11g/1_54_777766629.dbf thread=1 sequence=54

archived log file name=/u01/oracle/oradata/archivelog/ora11g/1_55_777766629.dbf thread=1 sequence=55

archived log file name=/u01/oracle/oradata/archivelog/ora11g/1_56_777766629.dbf thread=1 sequence=56

archived log file name=/u01/oracle/oradata/archivelog/ora11g/1_57_777766629.dbf thread=1 sequence=57

unable to find archived log

archived log thread=1 sequence=58

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 04/19/2012 00:55:48

RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 58 and starting SCN of 1157379

SQL> select file#,to_char(checkpoint_change#,'9999999999999999') "SCN",

2 ?to_char(RESETLOGS_CHANGE#,'9999999999999999') "RESETLOGS SCN"

3 ?from v$datafile_header;

FILE# SCN ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?RESETLOGS SCN

---------- ---------------------------------- ----------------------------------

1 ? ? ? ? ? 1157379 ? ? ? ? ? ? ? ? ? ? ? ? ? ? 787897

2 ? ? ? ? ? 1157379 ? ? ? ? ? ? ? ? ? ? ? ? ? ? 787897

3 ? ? ? ? ? 1157379 ? ? ? ? ? ? ? ? ? ? ? ? ? ? 787897

4 ? ? ? ? ? 1157379 ? ? ? ? ? ? ? ? ? ? ? ? ? ? 787897

6 ? ? ? ? ? 1157379 ? ? ? ? ? ? ? ? ? ? ? ? ? ? 787897

SQL> select file#,to_char(checkpoint_change#,'999999999999999') "SCN",

2 ?to_char(last_change#,'999999999999999')"STOP_SCN" from v$datafile;

FILE# SCN ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?STOP_SCN

---------- -------------------------------- --------------------------------

1 ? ? ? ? ?1157379

2 ? ? ? ? ?1157379

3 ? ? ? ? ?1157379

4 ? ? ? ? ?1157379

6 ? ? ? ? ?1157379

SQL> alter database open resetlogs;

Database altered.

SQL> select count(*) from chf.xifenfei;

COUNT(*)

----------

298136

SQL> select TABLE_NAME from dba_tables where table_name LIKE 'XIFENFEI%';

TABLE_NAME

------------------------------------------------------------

XIFENFEI02

XIFENFEI01

XIFENFEI

使用rman基于scn實現數據庫增量恢復是在dg中修復gap的時候常見的方法,其實該方法也可以使用常規的增量恢復,通過人工控制,實現數據庫的某種特殊的業務需求(特殊的數據遷移).處理思路主要是獲得備庫的數據文件最小scn(這個scn可能是通過全備恢復或者增量恢復產生),然后基于該SCN實現數據庫增量備份,然后利用該備份進行增量恢復.

總結

以上是生活随笔為你收集整理的oracle的scn增量备份,Oracle技术之利用scn增量备份实现数据库增量恢复的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇：在php中如何便利字符串,php字符串可
下一篇： linux perl 安装目录,肿么查看