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选项。
--非常好，把课上的知识自己测试后写成文档，多谢分享
后面老师课程中重要的实验，我都尽量整理一下，分享出来，一起和大家探讨。
--我这里也有个给你们完成各个案例的文档，如果最后都能完成，那么备份和恢复部分就毕业了
--本资料由同学【星动行働】制作整理