1.备份users表空间
[oracle@host01 ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Dec 20 14:32:46 2016

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

connected to target database: PROD1 (DBID=2148633869)

2.采用nologging属性创建表，并且以直接路径加载的方式插入数据
RMAN> backup as backupset tablespace users;

Starting backup at 20-DEC-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 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=00004 name=/u01/app/oracle/oradata/PROD1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 20-DEC-16
channel ORA_DISK_1: finished piece 1 at 20-DEC-16
piece handle=/u01/app/oracle/fast_recovery_area/PROD1/backupset/2016_12_20/o1_mf_nnndf_TAG20161220T143300_d5kn4wxc_.bkp tag=TAG20161220T143300 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 20-DEC-16

SCOTT@PROD1>create table t_objects nologging tablespace users as select * from all_objects where 1=2;

Table created.

SCOTT@PROD1>insert /*+ append */ into t_objects select * from all_objects;

68326 rows created.

SCOTT@PROD1>commit;

Commit complete.

3.RMAN中列出包含不可恢复数据的数据文件

RMAN> report unrecoverable database;

Report of files that need backup due to unrecoverable operations
File Type of Backup Required Name
---- ----------------------- -----------------------------------
4 full or incremental /u01/app/oracle/oradata/PROD1/users01.dbf

4.删除users表空间，测试nologging的不可恢复性
SYS@PROD1>select file_name from dba_data_files where tablespace_name='USERS';

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD1/users01.dbf

SYS@PROD1>! ls /u01/app/oracle/oradata/PROD1/users01.dbf
/u01/app/oracle/oradata/PROD1/users01.dbf

SYS@PROD1>! rm /u01/app/oracle/oradata/PROD1/users01.dbf

SYS@PROD1>! ls /u01/app/oracle/oradata/PROD1/users01.dbf
ls: /u01/app/oracle/oradata/PROD1/users01.dbf: No such file or directory

5.RMAN执行恢复users表空间操作

正常关闭数据库失败，采用abort方式
SYS@PROD1>shutdown immediate;
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/u01/app/oracle/oradata/PROD1/users01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

SYS@PROD1>shutdown abort;
ORACLE instance shut down.

[oracle@host01 ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Dec 20 14:48:06 2016

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

connected to target database: PROD1 (DBID=2148633869, not open)

还原表空间
RMAN> restore tablespace users;

Starting restore at 20-DEC-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 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 00004 to /u01/app/oracle/oradata/PROD1/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/PROD1/backupset/2016_12_20/o1_mf_nnndf_TAG20161220T143300_d5kn4wxc_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/PROD1/backupset/2016_12_20/o1_mf_nnndf_TAG20161220T143300_d5kn4wxc_.bkp tag=TAG20161220T143300
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 20-DEC-16

恢复表空间
RMAN> recover tablespace users;

Starting recover at 20-DEC-16
using channel ORA_DISK_1

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

Finished recover at 20-DEC-16

打开数据库
SYS@PROD1>alter database open;

Database altered.

6.验证NOLOGGING的不可恢复性
SCOTT@PROD1>select count(*) from t_objects;
select count(*) from t_objects
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 667)
ORA-01110: data file 4: '/u01/app/oracle/oradata/PROD1/users01.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option

总结
NOLONGGING能让你”一时之快”，但是风险是极大的，所以要尽快备份或在空闲时间重建表。

ITPub博客

不可恢复的NOLOGGING 数据库对象

最新文章