【非归档模式下使用RMAN进行全库备份（适用于培训和测试环境）】

前言

记录Oracle学习过程，供大家参考。

NOARCHIVELOG mode 非归档模式	ARCHIVELOG mode 归档模式
全库备份必须关闭数据库	在开启数据库（OPEN）的状态下全库备份
无法恢复到最后一次提交的事务	可以恢复到最后一次提交的事务
适用于培训和测试环境，适用于数据仓库	适用于生产环境。但是有些数仓模式不适用

Redo和Undo：

环境

系统：Oracle Linux Server 7.9
数据库：Oracle 19c

实验步骤

准备阶段

1. 检查归档状态

• 方式一：

SQL> select name,log_mode from v$database;

NAME		   LOG_MODE
------------------ ------------------------
ORCL		   NOARCHIVEL  # 表示数据库处于非归档模式

• 方式二：

SQL> show user;
USER is "SYS"
SQL> archive log list;
Database log mode	       No Archive Mode  # 表示数据库处于非归档模式
Automatic archival	       Disabled
Archive destination	       /u01/app/oracle/oradata/ORCL/arch
Oldest online log sequence     12
Current log sequence	       14

其中：Oracle默认为非归档模式，本文的实验是也是基于非归档模式下完成的。开启归档等教程如有需要请百度一下。

2. 建立备份目录root用户

[oracle@oracle01 ~]$ mkdir /full
[oracle@oracle01 ~]$ chown -R oracle:oinstall /full

全库备份（数据库为mount状态）

1. 查看数据库状态，确保数据库为关闭状态

[oracle@oracle01 ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Nov 17 19:55:33 2022
Version 19.3.0.0.0

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

connected to target database: ORCL (DBID=1644784921)

RMAN> select status from v$instance;

using target database control file instead of recovery catalog
STATUS      
------------
OPEN 

2. 若在open状态执行备份，则会报错

RMAN> backup database;

Starting backup at 2022:11:1719:56:52
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=256 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 11/17/2022 19:56:52
RMAN-06149: cannot BACKUP DATABASE in NOARCHIVELOG mode

3. 关闭数据库，启动至mount状态

RMAN> shutdown immediate

database closed
database dismounted
Oracle instance shut down

RMAN> startup mount

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area    2415917880 bytes

Fixed Size                     8899384 bytes
Variable Size                570425344 bytes
Database Buffers            1828716544 bytes
Redo Buffers                   7876608 bytes

RMAN> select status from v$instance;

STATUS      
------------
MOUNTED     

4. 执行全库备份

RMAN> backup format '/full/full_%d_%s_%T_%p.bak' database;   # 此处也可以仅仅使用backup database进行备份 

Starting backup at 2022:11:1720:01:44
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=862 device type=DISK
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/ORCL/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/ORCL/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ORCL/undotbs01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/ORCL/users01.dbf
channel ORA_DISK_1: starting piece 1 at 2022:11:1720:01:44
channel ORA_DISK_1: finished piece 1 at 2022:11:1720:01:59
piece handle=/full/full_ORCL_1_20221117_1.bak tag=TAG20221117T200144 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 2022:11:1720:01:59

# 我们能从最后一段提示可以看出控制文件与SPFILE文件也进行了自动备份，他存放在了默认的位置
Starting Control File and SPFILE Autobackup at 2022:11:1720:02:00
piece handle=/u01/app/oracle/product/19.3.0/dbhome_1/dbs/c-1644784921-20221117-00 comment=NONE
Finished Control File and SPFILE Autobackup at 2022:11:1720:02:01

注：对 xxxx_%d_%s_%T_%p 进行解释
%d：数据库名
%T：备份的日期
%s：备份集
%p：备份片

5. 检查备份结果

RMAN> list backup;


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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time   
------- ---- -- ---------- ----------- ------------ ------------------
1       Full    1.32G      DISK        00:00:14     2022:11:1720:01:58
        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20221117T200144
        Piece Name: /full/full_ORCL_1_20221117_1.bak
  List of Datafiles in backup set 1
  File LV Type Ckp SCN    Ckp Time           Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- ------------------ ----------- ------ ----
  1       Full 2779771    2022:11:1719:58:53              NO    /u01/app/oracle/oradata/ORCL/system01.dbf
  3       Full 2779771    2022:11:1719:58:53              NO    /u01/app/oracle/oradata/ORCL/sysaux01.dbf
  4       Full 2779771    2022:11:1719:58:53              NO    /u01/app/oracle/oradata/ORCL/undotbs01.dbf
  7       Full 2779771    2022:11:1719:58:53              NO    /u01/app/oracle/oradata/ORCL/users01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time   
------- ---- -- ---------- ----------- ------------ ------------------
2       Full    10.20M     DISK        00:00:00     2022:11:1720:02:00
        BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: TAG20221117T200200
        Piece Name: /u01/app/oracle/product/19.3.0/dbhome_1/dbs/c-1644784921-20221117-00
  SPFILE Included: Modification time: 2022:11:1720:00:14
  SPFILE db_unique_name: ORCL
  Control File Included: Ckp SCN: 2779771      Ckp time: 2022:11:1719:58:53

6. 至此备份成功，将数据库启动至open状态

RMAN> alter database open;

Statement processed

RMAN> SELECT STATUS FROM V$INSTANCE;

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

数据库关闭，删除所有数据文件，并使用备份文件恢复

1. 上面阶段为全部备份，本次实验先删除数据库的数据文件，再使用上面的备份文件进行恢复数据库。在做实验之前，先新建一个表，用于实验恢复后的效果（此步骤为可选项）

RMAN> create table aaa (id number);
RMAN> insert into aaa values (666)