oracle database recover database （上篇）

数据库演示版本为 12.1.0.2

1. 概念解释

　　首先你得知道，recover database 这个命令是用作对所有数据库的文件内容及结构复原的，而且是完全复原。

　　有个细节注意下，这个命令分为两种，一种用在 SQL 命令行，一种用在 RMAN：

　　SQL > recover database 仅对所有数据文件进行完全恢复，前提是控制文件为当前最新状态，不可以是还原过来的老版本或是手工重建，否则 SQL 命令行执行将返回错误。

　　RMAN > recover database 对所有数据文件和控制文件进行完全恢复

　　本例中仅演示对数据文件的完全恢复，此命令对控制文件的恢复将在下篇讨论。

　　一句话：recover database 就是对所有数据进行完全恢复操作（控制文件、归档日志、联机日志完好无损的情况下）

2. 情况说明

　　当前系统中，控制文件、归档日志、联机日志都完好无损，删除数据文件，进行还原，使用 recover database 进行完全恢复。

3. 实验过程

3.1 备份 CDB

备份整个 CDB（CDB$ROOT / PDBPROD1 / PDBPROD2 / PDB$SEED）

进行完全备份

RMAN> backup as compressed backupset database tag='full_backup' format '/u01/app/oracle/fast_recovery_area/PRODCDB/%s_%d_%U.full

RMAN> backup as compressed backupset database tag='full_backup' format '/u01/app/oracle/fast_recovery_area/PRODCDB/%s_%d_%U.full';

Starting backup at 01-DEC-23
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=43 device type=DISK
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/app/oracle/oradata/PRODCDB/system01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/PRODCDB/undotbs01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/PRODCDB/sysaux01.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/PRODCDB/users01.dbf
channel ORA_DISK_1: starting piece 1 at 01-DEC-23
channel ORA_DISK_1: finished piece 1 at 01-DEC-23
piece handle=/u01/app/oracle/fast_recovery_area/PRODCDB/1_PRODCDB_012cugd4_1_1.full tag=FULL_BACKUP 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
input datafile file number=00008 name=/u01/app/oracle/oradata/PRODCDB/PDBPROD1/sysaux01.dbf
input datafile file number=00013 name=/u01/app/oracle/oradata/PRODCDB/PDBPROD1/example01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/PRODCDB/PDBPROD1/system01.dbf
input datafile file number=00009 name=/u01/app/oracle/oradata/PRODCDB/PDBPROD1/PDBPROD1_users01.dbf
input datafile file number=00014 name=/u01/app/oracle/oradata/PRODCDB/PDBPROD1/gao.dbf
channel ORA_DISK_1: starting piece 1 at 01-DEC-23
channel ORA_DISK_1: finished piece 1 at 01-DEC-23
piece handle=/u01/app/oracle/fast_recovery_area/PRODCDB/2_PRODCDB_022cugfg_1_1.full tag=FULL_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00011 name=/u01/app/oracle/oradata/PRODCDB/PDBPROD2/sysaux01.dbf
input datafile file number=00010 name=/u01/app/oracle/oradata/PRODCDB/PDBPROD2/system01.dbf
input datafile file number=00012 name=/u01/app/oracle/oradata/PRODCDB/PDBPROD2/PDBPROD2_users01.dbf
channel ORA_DISK_1: starting piece 1 at 01-DEC-23
channel ORA_DISK_1: finished piece 1 at 01-DEC-23
piece handle=/u01/app/oracle/fast_recovery_area/PRODCDB/3_PRODCDB_032cuggt_1_1.full tag=FULL_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00004 name=/u01/app/oracle/oradata/PRODCDB/pdbseed/sysaux01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/PRODCDB/pdbseed/system01.dbf
channel ORA_DISK_1: starting piece 1 at 01-DEC-23
channel ORA_DISK_1: finished piece 1 at 01-DEC-23
piece handle=/u01/app/oracle/fast_recovery_area/PRODCDB/4_PRODCDB_042cughm_1_1.full tag=FULL_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 01-DEC-23

Starting Control File and SPFILE Autobackup at 01-DEC-23
piece handle=/u01/app/oracle/fast_recovery_area/PRODCDB/autobackup/2023_12_01/o1_mf_s_1154433616_lplpljkm_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 01-DEC-23

RMAN>

View Code

3.2 PDBPROD1 创建测试数据

连接 PDBPROD1 查看当前数据文件

[oracle@host01 PRODCDB]$ sqlplus sys/oracle@pdbprod1 as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Dec 1 13:17:46 2023

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics
and Real Application Testing options

SYS@pdbprod1> set pagesize 50;
SYS@pdbprod1> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/PRODCDB/undotbs01.dbf
/u01/app/oracle/oradata/PRODCDB/PDBPROD1/system01.dbf
/u01/app/oracle/oradata/PRODCDB/PDBPROD1/sysaux01.dbf
/u01/app/oracle/oradata/PRODCDB/PDBPROD1/PDBPROD1_users01.dbf
/u01/app/oracle/oradata/PRODCDB/PDBPROD1/example01.dbf
/u01/app/oracle/oradata/PRODCDB/PDBPROD1/gao.dbf

6 rows selected.

创建测试表表空间gao以及测试表 recover_db，插入数据 1，2，3 并 commit 提交

SYS@pdbprod1> create table recover_db(id number) tablespace gao;

Table created.

SYS@pdbprod1> insert into recover_db values(1);

1 row created.

SYS@pdbprod1> insert into recover_db values(2);

1 row created.

SYS@pdbprod1> insert into recover_db values(3);

1 row created.

SYS@pdbprod1> commit;

Commit complete.

3.3 删除 PDBPROD1 所有数据文件

SYS@pdbprod1> !rm -rf /u01/app/oracle/oradata/PRODCDB/PDBPROD1/*

继续插入数据 4，5，6 不提交，执行触发完全检查点，刷缓存数据，Oracle database 19.3 （12.2.0.3）报错（请思考：物理文件删除，是否还可以继续插入数据？为什么），但12.1.0.2版本不会报错。

SQL> insert into recover_db values(4);

1 row created.

SQL> insert into recover_db values(5);

1 row created.

SQL> insert into recover_db values(6);

1 row created.

SQL> alter system checkpoint;

System altered.

SQL> /
alter system checkpoint
*
ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 30992
Session ID: 273 Serial number: 51273

12.1.0.2版本查看trace路径下的alert日志，显示错误但当前会话链接未丢失

ri Dec 01 13:00:55 2023
Thread 1 advanced to log sequence 163 (LGWR switch)
  Current log# 1 seq# 163 mem# 0: /u01/app/oracle/oradata/PRODCDB/redo01.log
Fri Dec 01 13:00:56 2023
Archived Log entry 1 added for thread 1 sequence 162 ID 0xac5e8623 dest 1:
Fri Dec 01 13:23:55 2023
Checker run found 5 new persistent data failures

[oracle@host01 trace]$ cat PRODCDB_m002_8330.trc

 Hot Backup end marker scn: 0x0000.00000000
 aux_file is NOT DEFINED 
 Plugged readony: NO
 Plugin scnscn: 0x0000.00000000
 Plugin resetlogs scn/timescn: 0x0000.00000000 01/01/1988 00:00:00
 Foreign creation scn/timescn: 0x0000.00000000 01/01/1988 00:00:00
 Foreign checkpoint scn/timescn: 0x0000.00000000 01/01/1988 00:00:00
 Online move state: 0
DDE: Problem Key 'ORA 1110' was flood controlled (0x1) (no incident)
ORA-01110: data file 7: '/u01/app/oracle/oradata/PRODCDB/PDBPROD1/system01.dbf'
ORA-01565: error in identifying file '/u01/app/oracle/oradata/PRODCDB/PDBPROD1/system01.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory

因为对表的所有操作都是在内存里面，此时和物理数据文件无关，所以可以继续。

触发检查点的目的是为了将缓存数据刷入磁盘，而这时就和数据文件有关系了，第一次触发完全检查点刷数据，没反应过来，第二次刷报错，因为缓存数据要写入磁盘，可是对应的磁盘文件被删除，因此报错。

请留意：插入 4，5，6 三条数据后的操作，是否有提交？完全恢复是否可以恢复出 4，5，6 ？

切换 CDB 查看 PDBPROD1 状态，并尝试启动（这里为什么 PDBPROD1 是 MOUNT ？）

[oracle@host01 PRODCDB]$ sql

SQL*Plus: Release 12.1.0.2.0 Production on Fri Dec 1 13:56:43 2023

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics
and Real Application Testing options

SYS@PRODCDB> alter pluggable database pdbprod1 close;

Pluggable database altered.

SYS@PRODCDB> show pdbs;

    CON_ID CON_NAME              OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
     2 PDB$SEED              READ ONLY  NO
     3 PDBPROD1              MOUNTED
     4 PDBPROD2              MOUNTED
SYS@PRODCDB> alter pluggable database pdbprod1 open;
alter pluggable database pdbprod1 open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 14 - see DBWR trace file
ORA-01110: data file 14: '/u01/app/oracle/oradata/PRODCDB/PDBPROD1/gao.dbf'

因为多租户体系中，参数文件和控制文件都处于 CDB 级别，因此，只要 CDB 是开启的，下面所有 PDB 最低级别都是 MOUNT（哪怕这个库已经出了问题，停止了，依然显示MOUNT）

3.4 还原 PDBPROD1 所有数据文件

还原数据文件

你可以连接 PDBPROD1 还原自己

[oracle@host01 trace]$ rman target sys/oracle@pdbprod1

Recovery Manager: Release 12.1.0.2.0 - Production on Fri Dec 1 14:04:13 2023

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

connected to target database: PRODCDB (DBID=2891862819, not open)

RMAN> restore database from tag='full_backup';

Starting restore at 01-DEC-23
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=30 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/PRODCDB/PDBPROD1/system01.dbf
channel ORA_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/PRODCDB/PDBPROD1/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/PRODCDB/PDBPROD1/PDBPROD1_users01.dbf
channel ORA_DISK_1: restoring datafile 00013 to /u01/app/oracle/oradata/PRODCDB/PDBPROD1/example01.dbf
channel ORA_DISK_1: restoring datafile 00014 to /u01/app/oracle/oradata/PRODCDB/PDBPROD1/gao.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/PRODCDB/2_PRODCDB_022cugfg_1_1.full
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/PRODCDB/2_PRODCDB_022cugfg_1_1.full tag=FULL_BACKUP
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 01-DEC-23

还可以连接 CDB ，还原 PDBPROD1

RMAN> restore pluggable database pdbprod1 from tag='full_backup';

3.5 恢复 PDBPROD1 所有数据文件

复原数据数据结构

你可以连接 PDBPROD1复原自己

[oracle@host01 trace]$ rman target sys/oracle@pdbprod1

Recovery Manager: Release 12.1.0.2.0 - Production on Fri Dec 1 14:13:04 2023

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

connected to target database: PRODCDB (DBID=2891862819, not open)

RMAN> recover database;

Starting recover at 01-DEC-23
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=264 device type=DISK

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

Finished recover at 01-DEC-23

还可以连接 CDB ，复原 PDBPROD1

RMAN> recover pluggable database pdbprod1;

3.6 开启 PDB1 进行数据验证

打开数据库

你可以连接 PDBPROD1 打开自己

RMAN> alter database open;

Statement processed

还可以连接 CDB ，打开 PDBPROD1

RMAN> alter pluggable database pdb1 open;

Statement processed

验证数据

请问，完全恢复之后，recover_db 表中有几条数据？为什么？

[oracle@host01 PRODCDB]$ sqlplus sys/oracle@pdbprod1 as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Dec 1 14:17:50 2023

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics
and Real Application Testing options

SYS@pdbprod1> select * from recover_db;

    ID
----------
     1
     2
     3
     4
     5
     6

6 rows selected.

正常情况下因为插入 4，5，6 没有提交，事务没有结束，因此不具有持久性。完全恢复是恢复到宕机前最后一次 commit 的状态，Oracle database 19.3 （12.2.0.3）版本测试就只有前三条数据，而当前的版本为 12.1.0.2是可以看到6条数据，所以Oracle database 19.3 （12.2.0.3）版本更符合逻辑。

Oracle database 19.3 （12.2.0.3）请参见：AskScuti：说说 RECOVER DATABASE（上）