Oracle数据库通过RMAN还原数据文件实验过程

最新推荐文章于 2022-09-30 18:02:16 发布
xiaohacute
最新推荐文章于 2022-09-30 18:02:16 发布
阅读量220
点赞数
分类专栏： Oracle
本文链接：https://blog.csdn.net/qq_44906445/article/details/105724108
版权
Oracle 专栏收录该内容
4 篇文章 0 订阅
订阅专栏
Oracle数据库通过RMAN还原数据文件实验过程

[oracle@xiaoha ~]$ lsnrctl start     #开启数据库监听

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 24-APR-2020 15:32:18

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/xiaoha/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xiaoha)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xiaoha)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                24-APR-2020 15:32:18
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/xiaoha/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xiaoha)(PORT=1521)))
Services Summary...
Service "oltp" has 1 instance(s).
  Instance "oltp", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

[oracle@xiaoha ~]$ sqlplus / as sysdba    #进入数据库（默认是以SYS用户进去的）

SQL*Plus: Release 11.2.0.1.0 Production on Fri Apr 24 15:36:54 2020

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

Connected to an idle instance.

SQL> startup mount;    #开启数据库到mount模式下
ORACLE instance started.

Total System Global Area  830930944 bytes
Fixed Size                  2217912 bytes
Variable Size             520095816 bytes
Database Buffers          301989888 bytes
Redo Buffers                6627328 bytes
Database mounted.

SQL> archive log list;    #查看是否处于归档模式（不在归档模式下需要修改，归档模式下才能进行备份实验）
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     18
Next log sequence to archive   20
Current log sequence           20

SQL> alter database open;    #修改数据库状态到open模式下

Database altered.

SQL> select status from v$instance;    #查看数据库状态

STATUS
------------
OPEN

SQL> create tablespace xiaohaspace datafile '/u01/app/oracle/oradata/oltp/xiaohaspace01.dbf' size 10m;   #创建表空间

Tablespace created.

SQL> alter tablespace xiaohaspace add datafile '/u01/app/oracle/oradata/oltp/xiaohaspace02.dbf' size 10m;   #增大表空间的容量

Tablespace altered.

SQL> exit    #退出SQL
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@xiaoha ~]$ cd /u01/app/oracle/oradata/oltp/    #进入Oracle数据文件目录
[oracle@xiaoha oltp]$ ls -lh   #查看文件的大小（刚刚创建的表空间的文件就会在这里显示）
total 1.6G
-rw-r----- 1 oracle oinstall 9.3M Apr 24 15:51 control01.ctl
-rw-r----- 1 oracle oinstall 101M Apr 24 15:44 example01.dbf
-rw-r----- 1 oracle oinstall  51M Apr 24 15:44 redo01.log
-rw-r----- 1 oracle oinstall  51M Apr 24 15:50 redo02.log
-rw-r----- 1 oracle oinstall  51M Apr 24 15:44 redo03.log
-rw-r----- 1 oracle oinstall 541M Apr 24 15:50 sysaux01.dbf
-rw-r----- 1 oracle oinstall 681M Apr 24 15:50 system01.dbf
-rw-r----- 1 oracle oinstall  30M Apr 24 10:24 temp01.dbf
-rw-r----- 1 oracle oinstall 116M Apr 24 15:50 undotbs01.dbf
-rw-r----- 1 oracle oinstall 5.1M Apr 24 15:44 users01.dbf
-rw-r----- 1 oracle oinstall  11M Apr 24 15:48 xiaohaspace01.dbf
-rw-r----- 1 oracle oinstall  11M Apr 24 15:49 xiaohaspace02.dbf

SQL> create table HHH(id number,name varchar(20),to_attend date) tablespace xiaohaspace;    #在表空间xiaohaspace创建一张表命名HHH

Table created.

SQL> insert into HHH values(1,'xiaoha',to_date('2020-04-24','YYYY-MM-DD'));    #在这张表中插入数据

1 row created.

SQL> commit;   #提交（插入数据或修改数据后一定需要提交）

Commit complete.

SQL> select * from HHH;   #查看HHH表

        ID NAME                 TO_ATTEND
---------- -------------------- ---------
         1 xiaoha               24-APR-20

[oracle@xiaoha oltp]$ rman target /   #进入RMAN中进行备份操作（数据库至少在mount模式下才能实现备份操作）

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Apr 24 16:01:31 2020

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

connected to target database: OLTP (DBID=1618378223)

RMAN> backup database plus archivelog;    #进行备份（全备）

Starting backup at 24-APR-20
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=143 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=6 RECID=1 STAMP=1038499145
input archived log thread=1 sequence=7 RECID=2 STAMP=1038520807
input archived log thread=1 sequence=8 RECID=3 STAMP=1038521650
input archived log thread=1 sequence=9 RECID=4 STAMP=1038542733
input archived log thread=1 sequence=10 RECID=5 STAMP=1038565441
input archived log thread=1 sequence=11 RECID=6 STAMP=1038567527
input archived log thread=1 sequence=12 RECID=7 STAMP=1038567591
input archived log thread=1 sequence=13 RECID=8 STAMP=1038569074
input archived log thread=1 sequence=14 RECID=9 STAMP=1038570114
input archived log thread=1 sequence=15 RECID=10 STAMP=1038570149
input archived log thread=1 sequence=16 RECID=11 STAMP=1038578534
input archived log thread=1 sequence=17 RECID=12 STAMP=1038578570
input archived log thread=1 sequence=18 RECID=13 STAMP=1038579541
input archived log thread=1 sequence=19 RECID=14 STAMP=1038579577
input archived log thread=1 sequence=20 RECID=15 STAMP=1038585913
channel ORA_DISK_1: starting piece 1 at 24-APR-20
channel ORA_DISK_1: finished piece 1 at 24-APR-20
piece handle=/u01/app/oracle/flash_recovery_area/OLTP/backupset/2020_04_24/o1_mf_annnn_TAG20200424T160514_hb57ftvx_.bkp tag=TAG20200424T160514 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 24-APR-20

Starting backup at 24-APR-20
using channel ORA_DISK_1
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/oltp/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/oltp/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/oltp/undotbs01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/oltp/example01.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/oltp/xiaohaspace01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/oltp/xiaohaspace02.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/oltp/users01.dbf
channel ORA_DISK_1: starting piece 1 at 24-APR-20
channel ORA_DISK_1: finished piece 1 at 24-APR-20
piece handle=/u01/app/oracle/flash_recovery_area/OLTP/backupset/2020_04_24/o1_mf_nnndf_TAG20200424T160522_hb57g2r9_.bkp tag=TAG20200424T160522 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
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 24-APR-20
channel ORA_DISK_1: finished piece 1 at 24-APR-20
piece handle=/u01/app/oracle/flash_recovery_area/OLTP/backupset/2020_04_24/o1_mf_ncsnf_TAG20200424T160522_hb57gwsw_.bkp tag=TAG20200424T160522 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 24-APR-20

Starting backup at 24-APR-20
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=21 RECID=16 STAMP=1038585949
channel ORA_DISK_1: starting piece 1 at 24-APR-20
channel ORA_DISK_1: finished piece 1 at 24-APR-20
piece handle=/u01/app/oracle/flash_recovery_area/OLTP/backupset/2020_04_24/o1_mf_annnn_TAG20200424T160549_hb57gy68_.bkp tag=TAG20200424T160549 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 24-APR-20

RMAN> list backup;    #查看备份片（会生成17/18/19/20四个备份片,17是日志文件备份片，18是数据文件备份片，19是控制文件和参数文件的备份片，20又是日志文件的备份片）

List of Backup Sets
===================

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
17      196.21M    DISK        00:00:04     24-APR-20
        BP Key: 17   Status: AVAILABLE  Compressed: NO  Tag: TAG20200424T160514
        Piece Name: /u01/app/oracle/flash_recovery_area/OLTP/backupset/2020_04_24/o1_mf_annnn_TAG20200424T160514_hb57ftvx_.bkp

  List of Archived Logs in backup set 17
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    6       1027193    23-APR-20 1039801    23-APR-20
  1    7       1039801    23-APR-20 1066997    23-APR-20
  1    8       1066997    23-APR-20 1081357    23-APR-20
  1    9       1081357    23-APR-20 1112223    24-APR-20
  1    10      1112223    24-APR-20 1157390    24-APR-20
  1    11      1157390    24-APR-20 1160474    24-APR-20
  1    12      1160474    24-APR-20 1160515    24-APR-20
  1    13      1160515    24-APR-20 1181122    24-APR-20
  1    14      1181122    24-APR-20 1183191    24-APR-20
  1    15      1183191    24-APR-20 1183218    24-APR-20
  1    16      1183218    24-APR-20 1186895    24-APR-20
  1    17      1186895    24-APR-20 1186926    24-APR-20
  1    18      1186926    24-APR-20 1189361    24-APR-20
  1    19      1189361    24-APR-20 1189383    24-APR-20
  1    20      1189383    24-APR-20 1195936    24-APR-20

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
18      Full    1.05G      DISK        00:00:21     24-APR-20
        BP Key: 18   Status: AVAILABLE  Compressed: NO  Tag: TAG20200424T160522
        Piece Name: /u01/app/oracle/flash_recovery_area/OLTP/backupset/2020_04_24/o1_mf_nnndf_TAG20200424T160522_hb57g2r9_.bkp
  List of Datafiles in backup set 18
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 1195947    24-APR-20 /u01/app/oracle/oradata/oltp/system01.dbf
  2       Full 1195947    24-APR-20 /u01/app/oracle/oradata/oltp/sysaux01.dbf
  3       Full 1195947    24-APR-20 /u01/app/oracle/oradata/oltp/undotbs01.dbf
  4       Full 1195947    24-APR-20 /u01/app/oracle/oradata/oltp/users01.dbf
  5       Full 1195947    24-APR-20 /u01/app/oracle/oradata/oltp/example01.dbf
  6       Full 1195947    24-APR-20 /u01/app/oracle/oradata/oltp/xiaohaspace01.dbf
  7       Full 1195947    24-APR-20 /u01/app/oracle/oradata/oltp/xiaohaspace02.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
19      Full    9.36M      DISK        00:00:01     24-APR-20
        BP Key: 19   Status: AVAILABLE  Compressed: NO  Tag: TAG20200424T160522
        Piece Name: /u01/app/oracle/flash_recovery_area/OLTP/backupset/2020_04_24/o1_mf_ncsnf_TAG20200424T160522_hb57gwsw_.bkp
  SPFILE Included: Modification time: 24-APR-20
  SPFILE db_unique_name: OLTP
  Control File Included: Ckp SCN: 1195961      Ckp time: 24-APR-20

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
20      11.50K     DISK        00:00:00     24-APR-20
        BP Key: 20   Status: AVAILABLE  Compressed: NO  Tag: TAG20200424T160549
        Piece Name: /u01/app/oracle/flash_recovery_area/OLTP/backupset/2020_04_24/o1_mf_annnn_TAG20200424T160549_hb57gy68_.bkp

  List of Archived Logs in backup set 20
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    21      1195936    24-APR-20 1195967    24-APR-20

[oracle@xiaoha ~]$ cd /u01/app/oracle/oradata/oltp/
[oracle@xiaoha oltp]$ ls
control01.ctl  redo02.log    system01.dbf   users01.dbf
example01.dbf  redo03.log    temp01.dbf     xiaohaspace01.dbf
redo01.log     sysaux01.dbf  undotbs01.dbf  xiaohaspace02.dbf
[oracle@xiaoha oltp]$ rm xiaohaspace01.dbf     #删除表空间xiaohaspace01.dbf文件
[oracle@xiaoha oltp]$ rm xiaohaspace02.dbf     #删除表空间xiaohaspace02.dbf文件

SQL> insert into HHH values(2,'xiaoli',to_date('2020-04-24','YYYY-MM-DD'));   #插入第二条数据

1 row created.

SQL> commit;   #提交

Commit complete.

SQL> select * from HHH;   #此时查看表HHH会发现有2条数据

        ID NAME                 TO_ATTEND
---------- -------------------- ---------
         2 xiaoli               24-APR-20
         1 xiaoha               24-APR-20

SQL> shutdown immediate;    #正常关闭报错
ORA-01116: error in opening database file 6
ORA-01110: data file 6: '/u01/app/oracle/oradata/oltp/xiaohaspace01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

SQL> shutdown abort;    #强制关闭数据库
ORACLE instance shut down.

SQL> startup open;    #数据库开启到open状态下报错（无法识别或锁定数据文件6）
ORACLE instance started.

Total System Global Area  830930944 bytes
Fixed Size                  2217912 bytes
Variable Size             520095816 bytes
Database Buffers          301989888 bytes
Redo Buffers                6627328 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/u01/app/oracle/oradata/oltp/xiaohaspace01.dbf'

RMAN> restore datafile 6;    #还原数据文件6（只还原指定文件可以减少对数据库的影响）

Starting restore at 24-APR-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 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 00006 to /u01/app/oracle/oradata/oltp/xiaohaspace01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/OLTP/backupset/2020_04_24/o1_mf_nnndf_TAG20200424T160522_hb57g2r9_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/OLTP/backupset/2020_04_24/o1_mf_nnndf_TAG20200424T160522_hb57g2r9_.bkp tag=TAG20200424T160522
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 24-APR-20

SQL> alter database open;    #修改数据库状态到open模式依旧报错，需要datafile6去recover
alter database open
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/u01/app/oracle/oradata/oltp/xiaohaspace01.dbf'

RMAN> recover datafile 6;    #用datafile6文件进行覆盖（需要recover主要是数据文件还原之后的SCN=1195947,而日志文件是一直在记录的，数据库在进行备份之后插入了第2条数据，SCN=1195936，SCN号不一致所以数据库无法正常open）

Starting recover at 24-APR-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=125 device type=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 24-APR-20

SQL> alter database open;    #再次修改数据库状态发现datafile7也需要还原和覆盖（依照上述步骤restore datafile7和recover datafile 7）
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/u01/app/oracle/oradata/oltp/xiaohaspace02.dbf'

RMAN> restore datafile 7;     #对datafile7进行还原

Starting restore at 24-APR-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 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 00007 to /u01/app/oracle/oradata/oltp/xiaohaspace02.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/OLTP/backupset/2020_04_24/o1_mf_nnndf_TAG20200424T160522_hb57g2r9_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/OLTP/backupset/2020_04_24/o1_mf_nnndf_TAG20200424T160522_hb57g2r9_.bkp tag=TAG20200424T160522
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 24-APR-20

RMAN> recover datafile 7;   #对datafile7进行覆盖

Starting recover at 24-APR-20
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 24-APR-20

SQL> alter database open;    #修改数据库状态到open模式下成功

Database altered.

SQL> select * from HHH;    #查看数据库，备份之前的第一条数据在，备份之后删除删除表空间之后插入的数据也在

        ID NAME                 TO_ATTEND
---------- -------------------- ---------
         2 xiaoli               24-APR-20
         1 xiaoha               24-APR-20

# 补充：在数据文件都restore和recover之后查询表HHH的时候可能会出现一个报错（是因为数据文件异常导致的无法读取）
SQL> select * from HHH;
select * from HHH

*

ERROR at line 1:

ORA-00376: file 6 cannot be read at this time
 
ORA-01110: data file 6: '/tu01/app/oracle/oradata/oltp/xiaohaspace01.dbf'

SQL> alter database datafile 6 offline;     #解决方法：将datafile6文件先脱机再联机就可以了

Database altered. 

SQL> alter database datafile 6 online; 

Database altered.