ORACLE12C 非归档模式下RMAN备份与恢复

一.查看rman相关的默认配置
[oracle@host01 ~]$ rman target /

Recovery Manager: Release 18.0.0.0.0 - Production on Sun Jan 17 16:12:09 2021
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.
connected to target database: ORCL (DBID=1535621266)

RMAN> show all
2> ;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default 默认开启了控制文件的备份
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/18c/dbs/snapcf_orcl.f'; # default
备注：rman target /  是11g之前版本的命令格式 12c推荐使用 rman target "'/ as sysbackup'"

二.查看rman的备份路径
SQL> show parameter recover

NAME_COL_PLUS_SHOW_PARAM		 TYPE				VALUE_COL_PLUS_SHOW_PARAM
---------------------------------------- ------------------------------ ------------------------------------------------------------
db_recovery_file_dest			 string 			/u01/app/oracle/fast_recovery_area/ORCL
db_recovery_file_dest_size		 big integer			8106M
db_unrecoverable_scn_tracking		 boolean			TRUE
recovery_parallelism			 integer			0
remote_recovery_file_dest		 string


三.归档模式与非归档模式的切换

确认当前为非归档模式
SQL> archive log list
Database log mode	       No Archive Mode
Automatic archival	       Disabled
Archive destination	       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     10
Current log sequence	       12

非归档模式切换为归档模式
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area			  570422456 bytes
Fixed Size					    8660152 bytes
Variable Size					  297795584 bytes
Database Buffers				  260046848 bytes
Redo Buffers					    3919872 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list;
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     10
Next log sequence to archive   12
Current log sequence	       12

归档模式切换为非归档模式
SQL> shut immediate 
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area			  570422456 bytes
Fixed Size					    8660152 bytes
Variable Size					  297795584 bytes
Database Buffers				  260046848 bytes
Redo Buffers					    3919872 bytes
Database mounted.
SQL> alter database noarchivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list
Database log mode	       No Archive Mode
Automatic archival	       Disabled
Archive destination	       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     10
Current log sequence	       12

四.测试前准备工作
以DBA管理员登录
conn / as sysdba;

给用户hr授权--“create tablespace"
SQL> grant create tablespace to hr;

Grant succeeded.

数据库服务端本机，切换到hr用户
conn hr/hr
创建表空间noarchivearea
SQL> create tablespace noarchivearea
  2  datafile '/u01/app/oracle/oradata/ORCL/noarchivetest.dbf'
  3  size 1M;

Tablespace created.

在表空间noarchivearea创建表noarchivelogtest
SQL> create table noarchivelogtest ( num NUMBER(8), name VARCHAR2(10)) 
  2  tablespace noarchivearea;

Table created.

SQL> select * from noarchivelogtest;

no rows selected

SQL> desc noarchivelogtest;
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 NUM						    NUMBER(8)
 NAME						    VARCHAR2(10)

向表中写入数据


SQL> insert into noarchivelogtest values(1,'aaa');

1 row created.

SQL> insert into noarchivelogtest values(2,'bbb');

1 row created.

提交更改


SQL> commit;

Commit complete.

SQL> select * from noarchivelogtest;

       NUM NAME
---------- --------------------
	 1 aaa
	 2 bbb
用于测试的数据准备完毕！


五.RMAN备份数据库
[oracle@host01 ~]$ rman target "'/ as sysbackup'"

Recovery Manager: Release 18.0.0.0.0 - Production on Sun Jan 17 18:22:02 2021
Version 18.3.0.0.0

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

connected to target database: ORCL (DBID=1535621266)

当前备份目录下，目前没有备份


RMAN> list backupset;

using target database control file instead of recovery catalog
specification does not match any backup in the repository

首次备份失败，原因数据库当前是打开的状态


RMAN> backup database;

Starting backup at 17-JAN-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=62 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 01/17/2021 18:24:26
RMAN-06149: cannot BACKUP DATABASE in NOARCHIVELOG mode


关闭数据库


RMAN> shutdown immediate;

database closed
database dismounted
Oracle instance shut down

mount模式下运行数据库


RMAN> startup mount;

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

Total System Global Area     570422456 bytes

Fixed Size                     8660152 bytes
Variable Size                297795584 bytes
Database Buffers             260046848 bytes
Redo Buffers                   3919872 bytes

再次备份数据库


RMAN> backup database;

Starting backup at 17-JAN-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=36 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
input datafile file number=00005 name=/u01/app/oracle/oradata/ORCL/noarchivetest.dbf
channel ORA_DISK_1: starting piece 1 at 17-JAN-21
channel ORA_DISK_1: finished piece 1 at 17-JAN-21
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/ORCL/backupset/2021_01_17/o1_mf_nnndf_TAG20210117T182716_j084850o_.bkp tag=TAG20210117T182716 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:03:00
Finished backup at 17-JAN-21

Starting Control File and SPFILE Autobackup at 17-JAN-21
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/ORCL/autobackup/2021_01_17/o1_mf_s_1062095142_j084fvvy_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 17-JAN-21

查看当前备份信息
RMAN> list backupset;


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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1       Full    1.22G      DISK        00:02:46     17-JAN-21      
        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20210117T182716
        Piece Name: /u01/app/oracle/fast_recovery_area/ORCL/ORCL/backupset/2021_01_17/o1_mf_nnndf_TAG20210117T182716_j084850o_.bkp
  List of Datafiles in backup set 1
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  1       Full 2019591    17-JAN-21              NO    /u01/app/oracle/oradata/ORCL/system01.dbf
  3       Full 2019591    17-JAN-21              NO    /u01/app/oracle/oradata/ORCL/sysaux01.dbf
  4       Full 2019591    17-JAN-21              NO    /u01/app/oracle/oradata/ORCL/undotbs01.dbf
  5       Full 2019591    17-JAN-21              NO    /u01/app/oracle/oradata/ORCL/noarchivetest.dbf
  7       Full 2019591    17-JAN-21              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:01     17-JAN-21      
        BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: TAG20210117T183018
        Piece Name: /u01/app/oracle/fast_recovery_area/ORCL/ORCL/autobackup/2021_01_17/o1_mf_s_1062095142_j084fvvy_.bkp
  SPFILE Included: Modification time: 17-JAN-21
  SPFILE db_unique_name: ORCL
  Control File Included: Ckp SCN: 2019591      Ckp time: 17-JAN-21

六.运用系统命令删除datafile，模拟日常磁盘损坏
[oracle@host01 2021_01_17]$ cd /u01/app/oracle/oradata/ORCL/
[oracle@host01 ORCL]$ ls
control01.ctl  noarchivetest.dbf  redo01.log  redo02.log  redo03.log  sysaux01.dbf  system01.dbf  temp01.dbf  undotbs01.dbf  users01.dbf

删除noarchivetest.dbf,模拟磁盘坏道

[oracle@host01 ORCL]$ rm noarchivetest.dbf 
[oracle@host01 ORCL]$ ls
control01.ctl  redo01.log  redo02.log  redo03.log  sysaux01.dbf  system01.dbf  temp01.dbf  undotbs01.dbf  users01.dbf


[oracle@host01 ~]$ sqlplus hr/hr
此时表格，依然可以查询和编辑
SQL> select * from noarchivelogtest;

       NUM NAME
---------- --------------------
	 1 aaa
	 2 bbb

SQL> update noarchivelogtest set NAME='ccc' where num=2;

1 row updated.
SQL> insert into  noarchivelogtest values(3,'ddd');
1 row created.


但是此时当关闭数据库时，会发现报错，无法关闭，可在rman中将数据关闭
SQL> shutdown immediate;
ORA-01116: error in opening database file 5
ORA-01110: data file 5: '/u01/app/oracle/oradata/ORCL/noarchivetest.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

打开数据库也出现报错

SQL> startup;
ORACLE instance started.

Total System Global Area  570422456 bytes
Fixed Size		    8660152 bytes
Variable Size		  297795584 bytes
Database Buffers	  260046848 bytes
Redo Buffers		    3919872 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/u01/app/oracle/oradata/ORCL/noarchivetest.dbf'

七.RMAN恢复数据库
rman target "'/ as sysbackup'"

mount模式下，启动数据库
RMAN> startup mount;

Oracle instance started
database mounted

Total System Global Area     570422456 bytes

Fixed Size                     8660152 bytes
Variable Size                297795584 bytes
Database Buffers             260046848 bytes
Redo Buffers                   3919872 bytes

恢复数据库(物理恢复)


RMAN> restore database;

Starting restore at 17-JAN-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=36 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 00001 to /u01/app/oracle/oradata/ORCL/system01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/ORCL/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/ORCL/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/ORCL/noarchivetest.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/ORCL/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORCL/ORCL/backupset/2021_01_17/o1_mf_nnndf_TAG20210117T182716_j084850o_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORCL/ORCL/backupset/2021_01_17/o1_mf_nnndf_TAG20210117T182716_j084850o_.bkp tag=TAG20210117T182716
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 17-JAN-21

尝试打开数据库，报错，并提示data file 1需修复
RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 01/17/2021 20:30:08
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/app/oracle/oradata/ORCL/system01.dbf'

对数据库进行修复（介质修复）


RMAN> recover database;

Starting recover at 17-JAN-21
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 12 is already on disk as file /u01/app/oracle/oradata/ORCL/redo03.log
archived log for thread 1 with sequence 13 is already on disk as file /u01/app/oracle/oradata/ORCL/redo01.log
archived log file name=/u01/app/oracle/oradata/ORCL/redo03.log thread=1 sequence=12
archived log file name=/u01/app/oracle/oradata/ORCL/redo01.log thread=1 sequence=13
media recovery complete, elapsed time: 00:00:03
Finished recover at 17-JAN-21

打开数据库，提示需添加命令选项RESETLOGS 或者 NORESETLOGS
RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 01/17/2021 20:31:15
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

RMAN> alter database open noresetlogs;

Statement processed

到此非归档模式下的恢复工作已全部完成

使用hr用户登录数据库，查看之前的表格数据NOARCHIVELOGTEST
[oracle@host01 ~]$ sqlplus hr/hr

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Jan 17 20:33:37 2021
Version 18.3.0.0.0

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

Last Successful login time: Sun Jan 17 2021 20:20:41 +08:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SQL> select table_name from user_tables;

TABLE_NAME
--------------------------------------------------------------------------------
REGIONS
COUNTRIES
LOCATIONS
DEPARTMENTS
JOBS
EMPLOYEES
JOB_HISTORY
ST_TEST
NOARCHIVELOGTEST

9 rows selected.

SQL> select * from noarchivelogtest;

       NUM NAME
---------- --------------------
	 3 ddd
	 1 aaa
	 2 ccc

备注，数据和备份时有差异的原因是，在删除了datafile 做了破坏实验后，后面对文件进行过修改和插入数据的操作，online redo日志中存有记录，alter database open 增加了noresetlogs选项，SCN没有复位，redo的时间距离备份不是很久，因此使用最新的值,将备份至redo中的差异进行了恢复。

非归档模式的备份，简单做了一下。

就当课后练习。

只是恢复完成后，alter database open 增加了noresetlogs选项，我没有选择resetlogs选项。

--非常好，把课上的知识自己测试后写成文档，多谢分享

后面老师课程中重要的实验，我都尽量整理一下，分享出来，一起和大家探讨。

--我这里也有个给你们完成各个案例的文档，如果最后都能完成，那么备份和恢复部分就毕业了

--本资料由同学【星动行働】制作整理