重庆思庄Oracle、KingBase、PostgreSQL、Redhat认证学习论坛

标题: Oracle没有控制文件的RMAN恢复过程 [打印本页]

作者: mahan 时间: 2025-8-10 18:44
标题: Oracle没有控制文件的RMAN恢复过程
当前RMAN备份

注：本次实验完成只有RMAN的数据文件备份，没有控制文件备份下的恢复过程，用到了Oracle的恢复包来完成恢复工作。
查看当前RMAN备份：
RMAN> list backup;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
12    75.49M DISK 00:00:00    02-APR-18
      BP Key: 12 Status: AVAILABLE  Compressed: NO  Tag: ARCHIVELOG_BACKUP
      Piece Name: u01/app2/arch/oracle-0jsvc2mv_1_1.arc

  List of Archived Logs in backup set 12
  Thrd Seq Low SCN Low Time Next SCN Next Time
  ---- ------- ---------- --------- ---------- ---------
1 175    1636770 26-MAR-181689199 02-APR-18
1 176    1689199 02-APR-181689371 02-APR-18
1 177    1689371 02-APR-181702320 02-APR-18
1 178    1702320 02-APR-181704205 02-APR-18
1 179    1704205 02-APR-181704275 02-APR-18

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
13    Full 338.81M DISK 00:00:08    02-APR-18
      BP Key: 13 Status: AVAILABLE  Compressed: NO  Tag: FULL_DB_BACKUP
      Piece Name: u01/app2/arch/oracle-0ksvc2n0_1_1.db
  List of Datafiles in backup set 13
  File LV Type Ckp SCN Ckp Time Name
  ---- -- ---- ---------- --------- ----
1    Full 1704284 02-APR-18 /u01/app2/ora11/oradata/primary/system01.dbf
2    Full 1704284 02-APR-18 /u01/app2/ora11/oradata/primary/sysaux01.dbf
3    Full 1704284 02-APR-18 /u01/app2/ora11/oradata/primary/undo.dbf
4    Full 1704284 02-APR-18 /u01/app2/ora11/oradata/primary/tbs101.dbf
5    Full 1704284 02-APR-18 /u01/app2/ora11/oradata/primary/tbs201.dbf
6    Full 1704284 02-APR-18 /u01/app2/ora11/oradata/primary/user01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
14    Full 19.86M DISK 00:00:01    02-APR-18
      BP Key: 14 Status: AVAILABLE  Compressed: NO  Tag: FULL_DB_BACKUP
      Piece Name: u01/app2/arch/oracle-0lsvc2nf_1_1.db
  SPFILE Included: Modification time: 02-APR-18
  SPFILE db_unique_name: PRIMARY
  Control File Included: Ckp SCN: 1704298    Ckp time: 02-APR-18

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
15    11.50K DISK 00:00:00    02-APR-18
      BP Key: 15 Status: AVAILABLE  Compressed: NO  Tag: ARCHIVELOG_BACKUP
      Piece Name: u01/app2/arch/oracle-0msvc2nh_1_1.arc

  List of Archived Logs in backup set 15
  Thrd Seq Low SCN Low Time Next SCN Next Time
  ---- ------- ---------- --------- ---------- ---------
1 180    1704275 02-APR-181704305 02-APR-18

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
16    Full 19.83M DISK 00:00:02    02-APR-18
      BP Key: 16 Status: AVAILABLE  Compressed: NO  Tag: TAG20180402T222010
      Piece Name: u01/app2/arch/ctl_PRIMARY_23_1
  Control File Included: Ckp SCN: 1704360    Ckp time: 02-APR-18

二

实际恢复步骤

2.1 启动数据到nomount状态
[ora11@prim ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.4.0 Production on Mon Apr 222:22:462018

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

Connected toan idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 889389056 bytes
Fixed Size 2258360 bytes
Variable Size 281021000 bytes
Database Buffers 599785472 bytes
Redo Buffers 6324224 bytes

2.2 还原system01.dbf系统文件
DECLARE
  devtype varchar2(256);
  done boolean;
  BEGIN
  devtype := dbms_backup_restore.DeviceAllocate(type=>null, ident=>'d1');
  dbms_backup_restore.RestoreSetDatafile;
  dbms_backup_restore.RestoreDatafileTo(dfnumber => 1,toname => '/u01/app2/ora11/oradata/primary/system01.dbf');
  dbms_backup_restore.RestoreBackupPiece(done => done,handle =>'/u01/app2/arch/oracle-0ksvc2n0_1_1.db', params => null);
  dbms_backup_restore.DeviceDeallocate;
END;
/

SQL> DECLARE
2 devtype varchar2(256);
3 done boolean;
4 BEGIN
5 devtype := dbms_backup_restore.DeviceAllocate(type=>null, ident=>'d1');
6 dbms_backup_restore.RestoreSetDatafile;
7 dbms_backup_restore.RestoreDatafileTo(dfnumber => 1,toname => '/u01/app2/ora11/oradata/primary/system01.dbf');
8 dbms_backup_restore.RestoreBackupPiece(done => done,handle =>'/u01/app2/arch/oracle-0ksvc2n0_1_1.db', params => null);
9 dbms_backup_restore.DeviceDeallocate;
10 END;
11

PL/SQL procedure successfully completed.

[root@prim primary]# pwd
/u01/app2/ora11/oradata/primary
[root@prim primary]# ls -ltr
total 235764
-rw-r----- 1 ora11 oinstall 241180672 Apr 222:38 system01.dbf

2.3 创建只有一个文件system01.dbf的控制文件
CREATE CONTROLFILE REUSE DATABASE primary RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1'/u01/app2/ora11/oradata/primary/redo01.log' SIZE 50M,
GROUP 2'/u01/app2/ora11/oradata/primary/redo02.log' SIZE 50M
DATAFILE
'/u01/app2/ora11/oradata/primary/system01.dbf'
CHARACTER SET ZHS16GBK
;

SQL> SELECT name FROM v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app2/ora11/oradata/primary/system01.dbf

2.4 将备份集注册到当期控制文件中
RMAN> catalog start with'/u01/app2/arch';
using target database control file instead of recovery catalog
searching for all files that match the pattern u01/app2/arch
ist of Files Unknown to the Database
=====================================
File Name: /u01/app2/arch/1_176_948022980.dbf
File Name: /u01/app2/arch/oracle-0jsvc2mv_1_1.arc
File Name: /u01/app2/arch/oracle-0ksvc2n0_1_1.db
File Name: /u01/app2/arch/oracle-0msvc2nh_1_1.arc
File Name: /u01/app2/arch/oracle-0lsvc2nf_1_1.db
File Name: /u01/app2/arch/ctl_PRIMARY_23_1
File Name: /u01/app2/arch/1_179_948022980.dbf
File Name: /u01/app2/arch/1_180_948022980.dbf
File Name: /u01/app2/arch/1_175_948022980.dbf
File Name: /u01/app2/arch/1_178_948022980.dbf
File Name: /u01/app2/arch/1_177_948022980.dbf
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/app2/arch/1_176_948022980.dbf
File Name: /u01/app2/arch/oracle-0jsvc2mv_1_1.arc
File Name: /u01/app2/arch/oracle-0ksvc2n0_1_1.db
File Name: /u01/app2/arch/oracle-0msvc2nh_1_1.arc
File Name: /u01/app2/arch/oracle-0lsvc2nf_1_1.db
File Name: /u01/app2/arch/ctl_PRIMARY_23_1
File Name: /u01/app2/arch/1_179_948022980.dbf
File Name: /u01/app2/arch/1_180_948022980.dbf
File Name: /u01/app2/arch/1_175_948022980.dbf
File Name: /u01/app2/arch/1_178_948022980.dbf
File Name: /u01/app2/arch/1_177_948022980.dbf

RMAN>

RMAN> list backup;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
1    75.49M DISK 00:00:00    02-APR-18
      BP Key: 1 Status: AVAILABLE Compressed: NO Tag: ARCHIVELOG_BACKUP
      Piece Name: /u01/app2/arch/oracle-0jsvc2mv_1_1.arc

  List of Archived Logs in backup set 1
  Thrd Seq Low SCN Low Time Next SCN Next Time
  ---- ------- ---------- --------- ---------- ---------
1 175    1636770 26-MAR-181689199 02-APR-18
1 176    1689199 02-APR-181689371 02-APR-18
1 177    1689371 02-APR-181702320 02-APR-18
1 178    1702320 02-APR-181704205 02-APR-18
1 179    1704205 02-APR-181704275 02-APR-18

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2    Full 338.81M DISK 00:00:00    02-APR-18
      BP Key: 2 Status: AVAILABLE Compressed: NO Tag: FULL_DB_BACKUP
      Piece Name: /u01/app2/arch/oracle-0ksvc2n0_1_1.db
  List of Datafiles in backup set 2
  File LV Type Ckp SCN Ckp Time Name
  ---- -- ---- ---------- --------- ----
1    Full 1704284 02-APR-18 /u01/app2/ora11/oradata/primary/system01.dbf
2    Full 1704284 02-APR-18
3    Full 1704284 02-APR-18
4    Full 1704284 02-APR-18
5    Full 1704284 02-APR-18
6    Full 1704284 02-APR-18

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
3    11.50K DISK 00:00:00    02-APR-18
      BP Key: 3 Status: AVAILABLE Compressed: NO Tag: ARCHIVELOG_BACKUP
      Piece Name: /u01/app2/arch/oracle-0msvc2nh_1_1.arc

  List of Archived Logs in backup set 3
  Thrd Seq Low SCN Low Time Next SCN Next Time
  ---- ------- ---------- --------- ---------- ---------
1 180    1704275 02-APR-181704305 02-APR-18

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4    Full 19.86M DISK 00:00:00    02-APR-18
      BP Key: 4 Status: AVAILABLE Compressed: NO Tag: FULL_DB_BACKUP
      Piece Name: /u01/app2/arch/oracle-0lsvc2nf_1_1.db
  SPFILE Included: Modification time: 02-APR-18
  SPFILE db_unique_name: PRIMARY
  Control File Included: Ckp SCN: 1704298    Ckp time: 02-APR-18

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5    Full 19.83M DISK 00:00:00    02-APR-18
      BP Key: 5 Status: AVAILABLE Compressed: NO Tag: TAG20180402T222010
      Piece Name: /u01/app2/arch/ctl_PRIMARY_23_1
  Control File Included: Ckp SCN: 1704360    Ckp time: 02-APR-18

2.5 使用DBMS_BACKUP_RESTORE包，对其它文件进行恢复
注：文件名不要求和原来一样，只要唯一即可。
DECLARE
  devtype varchar2(256);
  done boolean;
BEGIN
  devtype := dbms_backup_restore.DeviceAllocate(type=>null, ident=>'d1');
  dbms_backup_restore.RestoreSetDatafile;
  dbms_backup_restore.RestoreDatafileTo(dfnumber => 1,toname => '/u01/app2/ora11/oradata/primary/system01.dbf');
  dbms_backup_restore.RestoreDatafileTo(dfnumber => 2,toname => '/u01/app2/ora11/oradata/primary/sysaux01.dbf');
  dbms_backup_restore.RestoreBackupPiece(done => done,handle =>'/u01/app2/arch/oracle-0ksvc2n0_1_1.db', params => null);
  dbms_backup_restore.DeviceDeallocate;
END;
/

DECLARE
  devtype varchar2(256);
  done boolean;
BEGIN
  devtype := dbms_backup_restore.DeviceAllocate(type=>null, ident=>'d1');
  dbms_backup_restore.RestoreSetDatafile;
  dbms_backup_restore.RestoreDatafileTo(dfnumber => 3,toname => '/u01/app2/ora11/oradata/primary/undo.dbf');
  dbms_backup_restore.RestoreBackupPiece(done => done,handle =>'/u01/app2/arch/oracle-0ksvc2n0_1_1.db', params => null);
  dbms_backup_restore.DeviceDeallocate;
END;
/

DECLARE
  devtype varchar2(256);
  done boolean;
BEGIN
  devtype := dbms_backup_restore.DeviceAllocate(type=>null, ident=>'d1');
  dbms_backup_restore.RestoreSetDatafile;
  dbms_backup_restore.RestoreDatafileTo(dfnumber => 4,toname => '/u01/app2/ora11/oradata/primary/user01.dbf');
  dbms_backup_restore.RestoreBackupPiece(done => done,handle =>'/u01/app2/arch/oracle-0ksvc2n0_1_1.db', params => null);
  dbms_backup_restore.DeviceDeallocate;
END;
/

DECLARE
  devtype varchar2(256);
  done boolean;
BEGIN
  devtype := dbms_backup_restore.DeviceAllocate(type=>null, ident=>'d1');
  dbms_backup_restore.RestoreSetDatafile;
  dbms_backup_restore.RestoreDatafileTo(dfnumber => 5,toname => '/u01/app2/ora11/oradata/primary/tbs101.dbf');
  dbms_backup_restore.RestoreBackupPiece(done => done,handle =>'/u01/app2/arch/oracle-0ksvc2n0_1_1.db', params => null);
  dbms_backup_restore.DeviceDeallocate;
END;
/

DECLARE
  devtype varchar2(256);
  done boolean;
BEGIN
  devtype := dbms_backup_restore.DeviceAllocate(type=>null, ident=>'d1');
  dbms_backup_restore.RestoreSetDatafile;
  dbms_backup_restore.RestoreDatafileTo(dfnumber => 6,toname => '/u01/app2/ora11/oradata/primary/tbs201.dbf');
  dbms_backup_restore.RestoreBackupPiece(done => done,handle =>'/u01/app2/arch/oracle-0ksvc2n0_1_1.db', params => null);
  dbms_backup_restore.DeviceDeallocate;
END;
/

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app2/ora11/oradata/primary/system01.dbf

2.6 元数据只显示文件1的信息，需要重建控制文件
CREATE CONTROLFILE REUSE DATABASE primary RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1'/u01/app2/ora11/oradata/primary/redo01.log' SIZE 50M,
GROUP 2'/u01/app2/ora11/oradata/primary/redo02.log' SIZE 50M
DATAFILE
'/u01/app2/ora11/oradata/primary/system01.dbf',
'/u01/app2/ora11/oradata/primary/sysaux01.dbf',
'/u01/app2/ora11/oradata/primary/undo.dbf',
'/u01/app2/ora11/oradata/primary/user01.dbf',
'/u01/app2/ora11/oradata/primary/tbs101.dbf',
'/u01/app2/ora11/oradata/primary/tbs201.dbf'
CHARACTER SET ZHS16GBK
;

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app2/ora11/oradata/primary/system01.dbf
/u01/app2/ora11/oradata/primary/sysaux01.dbf
/u01/app2/ora11/oradata/primary/undo.dbf
/u01/app2/ora11/oradata/primary/user01.dbf
/u01/app2/ora11/oradata/primary/tbs101.dbf
/u01/app2/ora11/oradata/primary/tbs201.dbf

2.7 启动数据库
SQL>  alter database open resetlogs;

Database altered.

SQL> SELECT count(*) FROM t1;
SELECT count(*) FROM t1
                  *
ERROR at line 1:
ORA-25153: Temporary Tablespace is Empty

SQL> alter tablespace temp add tempfile '/u01/app2/ora11/oradata/primary/temp01.dbf' size 100M;

Tablespace altered.

SQL> conn sun/sun
Connected.
SQL> SELECT count(*) FROM t1;

  COUNT(*)
----------
      3

如果文件包含在多个备份文件中使用initmsr函数：
DECLARE
devtype varchar2(256);
done boolean;
BEGIN
devtype := dbms_backup_restore.DeviceAllocate(type=>null, ident=>'d1');
dbms_backup_restore.RestoreSetDatafile;
dbms_backup_restore.initmsr(1,'/u01/app2/ora11/oradata/primary/system01.dbf');
dbms_backup_restore.RestoreDatafileTo(dfnumber => 1,toname => '/u01/app2/ora11/oradata/primary/system01.dbf');
dbms_backup_restore.RestoreBackupPiece(done => done,handle =>'/u01/app2/arch/oracle-0ksvc2n0_1_1.db', params => null);
dbms_backup_restore.RestoreBackupPiece(done => done,handle =>'oracle-0lsvc2nf_1_1.db', params => null);
END;
/

In order to restore all datafile you need to iterate over all datafiles (e.g. 1…n).

DECLARE
v_dev varchar2(50); -- device type allocated for restore
v_done boolean; -- has the controlfile been fully extracted yet
type t_fileTable is table of varchar2(255)
index by binary_integer;
v_fileTable t_fileTable; -- Stores the backuppiece names
v_maxPieces number:=1; -- Number of backuppieces in backupset
BEGIN
-- Initialise the filetable & number of backup pieces in the backupset
-- This section of code MUST be edited to reflect the customer's available
-- backupset before the procedure is compiled and run. In this example, the
-- backupset consists of 4 pieces:
v_fileTable(1):='C:\backup_test\ORA_DF631909818_S143_P1_C1';
v_fileTable(2):='C:\backup_test\ORA_DF631909818_S144_P1_C1';
-- add all other backupsets if needed
v_maxPieces:=2;
-- Allocate a device. In this example, I have specified 'sbt_tape' as I am
-- reading backuppieces from the media manager. If the backuppiece is on disk,
-- specify type=>null
v_dev:=sys.dbms_backup_restore.deviceAllocate(type=>null, ident=>'d1');
-- Begin the restore conversation
sys.dbms_backup_restore.restoreSetDatafile;
dbms_backup_restore.RestoreDatafileTo(dfnumber => 1);

-- Restore the datafile
FOR i IN 1..v_maxPieces LOOP
      sys.dbms_backup_restore.restoreBackupPiece(done=>v_done, handle=>v_fileTable(i), params=>null);
      IF v_done THEN
            GOTO all_done;
      END IF;
END LOOP;
<<all_done>>
-- Deallocate the device
sys.dbms_backup_restore.deviceDeallocate;
END;
/

欢迎光临重庆思庄Oracle、KingBase、PostgreSQL、Redhat认证学习论坛 (http://bbs.cqsztech.com/)