oracle表设置nologing,表设置为nologging能恢复出来吗？

首先创建一张测试表

JZH@test>create table t2 as select * from dba_objects where 0=1;

Table created.

JZH@test>select * from t2;

no rows selected

设置nologging

JZH@test>alter table t2 nologging;

Table altered.

插入数据

JZH@test>insert /*+ append */ into t2 select * from dba_objects;

75301 rows created.

JZH@test>commit;

Commit complete.

JZH@test>select count(*) from t2;

COUNT(*)

----------

75301

备份数据库

RMAN> backup database format '/u01/app/oracle/arch/test_%u_%p_%s.rmn';

Starting backup at 29-SEP-14

using channel ORA_DISK_1

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/test/system01.dbf

input datafile file number=00002 name=/u01/app/oracle/oradata/test/sysaux01.dbf

input datafile file number=00005 name=/u01/app/oracle/oradata/test/example01.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/test/undotbs01.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/test/users01.dbf

channel ORA_DISK_1: starting piece 1 at 29-SEP-14

channel ORA_DISK_1: finished piece 1 at 29-SEP-14

piece handle=/u01/app/oracle/arch/test_0cpjm86h_1_12.rmn tag=TAG20140929T013721 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:01:25

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

including current SPFILE in backup set

channel ORA_DISK_1: starting piece 1 at 29-SEP-14

channel ORA_DISK_1: finished piece 1 at 29-SEP-14

piece handle=/u01/app/oracle/arch/test_0dpjm896_1_13.rmn tag=TAG20140929T013721 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02

Finished backup at 29-SEP-14

关闭数据库启动到mount状态

SYS@test>shu immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@test>startup mount

ORACLE instance started.

Total System Global Area  626327552 bytes

Fixed Size                  2230952 bytes

Variable Size             444597592 bytes

Database Buffers          176160768 bytes

Redo Buffers                3338240 bytes

Database mounted.

rman恢复

RMAN> restore database;

Starting restore at 29-SEP-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=21 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/test/system01.dbf

channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/test/sysaux01.dbf

channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/test/undotbs01.dbf

channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/test/users01.dbf

channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/test/example01.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/arch/test_0cpjm86h_1_12.rmn

channel ORA_DISK_1: piece handle=/u01/app/oracle/arch/test_0cpjm86h_1_12.rmn tag=TAG20140929T013721

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:01:15

Finished restore at 29-SEP-14

RMAN> recover database;

Starting recover at 29-SEP-14

using channel ORA_DISK_1

starting media recovery

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

Finished recover at 29-SEP-14

查询T2表

JZH@test>select count(*) from t2;

COUNT(*)

----------

75301

OK了，恢复出来了！

BTW：restore是从备份中还原数据库，如果数据写回磁盘了，是可以恢复出来的，当然如果备份是采用insert之前的，肯定是恢复不了。