一次rman恢复引起的nologging问题模拟
测试环境:
创新互联专注为客户提供全方位的互联网综合服务,包含不限于成都网站建设、成都网站设计、双河网络推广、重庆小程序开发、双河网络营销、双河企业策划、双河品牌公关、搜索引擎seo、人物专访、企业宣传片、企业代运营等,从售前售中售后,我们都将竭诚为您服务,您的肯定,是我们最大的嘉奖;创新互联为所有大学生创业者提供双河建站搭建服务,24小时服务热线:18980820575,官方网址:www.cdcxhl.com
Oracle 11.2.0.4
SLES 11SP4
结论如下:
no force logging,table nologging, dml nologging,出现ora-26040
no force logging,table logging, dml nologging,未出现ora-26040
force logging, table nologging/logging dml nologging,未出现ora-26040
force logging, table nologging dml logging,未出现ora-26040
force logging, table nologging dml nologging,未出现ora-26040
只有在no force logging、table nologging、dml nologging情况下,recover数据会出现ora-26040,其他情况均正常。
测试过程如下:
SQL> create table gyc01 as select * from dba_users;
Table created.
SQL> insert into gyc01 select * from gyc01;
30 rows created.
SQL> /
60 rows created.
SQL> commit;
Commit complete.
SQL> select table_name,logging from dba_tables where table_name='GYC01';
TABLE_NAME LOG
------------------------------ ---
GYC01 YES
SQL> alter table gyc01 nologging;
Table altered.
SQL> select table_name,logging from dba_tables where table_name='GYC01';
TABLE_NAME LOG
------------------------------ ---
GYC01 NO
SQL> select count(*) from gyc01;
COUNT(*)
----------
120
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> create table gyc02 nologging as select * from gyc01 nologging;
Table created.
SQL> select count(*) from gyc02;
COUNT(*)
----------
120
SQL> insert into gyc02 select * from gyc01 nologging;
120 rows created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
/
System altered.
SQL>
System altered.
SQL> shutdown immediate^Cs
SQL>
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/app/oracle/product/10.2.0/oradata/gycdb/system01.dbf
/app/oracle/product/10.2.0/oradata/gycdb/sysaux01.dbf
/app/oracle/product/10.2.0/oradata/gycdb/undotbs01.dbf
/app/oracle/product/10.2.0/oradata/gycdb/users01.dbf
SQL> shutdown immediate
;Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
SP2-0734: unknown command beginning ";startup m..." - rest of line ignored.
SQL>
SQL>
SQL> startup
^C
ORA-00443: background process "VKTM" did not start
SQL> SQL> SQL> shutdown immediate;
ORA-01012: not logged on
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size 2230072 bytes
Variable Size 503318728 bytes
Database Buffers 1627389952 bytes
Redo Buffers 4947968 bytes
RMAN> recover database;
Starting recover at 07-MAY-18
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 4 is already on disk as file /arch/1_4_975518966.dbf
archived log for thread 1 with sequence 5 is already on disk as file /arch/1_5_975518966.dbf
archived log for thread 1 with sequence 6 is already on disk as file /arch/1_6_975518966.dbf
archived log for thread 1 with sequence 7 is already on disk as file /arch/1_7_975518966.dbf
archived log for thread 1 with sequence 8 is already on disk as file /arch/1_8_975518966.dbf
archived log for thread 1 with sequence 9 is already on disk as file /arch/1_9_975518966.dbf
archived log for thread 1 with sequence 10 is already on disk as file /arch/1_10_975518966.dbf
archived log file name=/arch/1_4_975518966.dbf thread=1 sequence=4
archived log file name=/arch/1_5_975518966.dbf thread=1 sequence=5
archived log file name=/arch/1_6_975518966.dbf thread=1 sequence=6
archived log file name=/arch/1_7_975518966.dbf thread=1 sequence=7
archived log file name=/arch/1_8_975518966.dbf thread=1 sequence=8
archived log file name=/arch/1_9_975518966.dbf thread=1 sequence=9
archived log file name=/arch/1_10_975518966.dbf thread=1 sequence=10
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/07/2018 18:46:50
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/arch/1_10_975518966.dbf'
ORA-00283: recovery session canceled due to errors
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/app/oracle/product/10.2.0/oradata/gycdb/redo02.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
RMAN> recover database;
Starting recover at 07-MAY-18
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 07-MAY-18
RMAN>
Database mounted.
SQL> alter database open;
Database altered.
SQL> select count(*) from gyc02;
select count(*) from gyc02
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 1, block # 89241)
ORA-01110: data file 1: '/app/oracle/product/10.2.0/oradata/gycdb/system01.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
SQL> select count(*) from gyc01;
COUNT(*)
----------
120
==========================结论:no force logging,table nologging, dml nologging,出现ora-26040
RMAN> restore database;
Starting restore at 07-MAY-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=189 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 /app/oracle/product/10.2.0/oradata/gycdb/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /app/oracle/product/10.2.0/oradata/gycdb/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /app/oracle/product/10.2.0/oradata/gycdb/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /app/oracle/product/10.2.0/oradata/gycdb/users01.dbf
channel ORA_DISK_1: reading from backup piece /backup/gycdb_01t2afu8_1_1.bak
channel ORA_DISK_1: piece handle=/backup/gycdb_01t2afu8_1_1.bak tag=TAG20180507T174128
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 07-MAY-18
RMAN> recover database;
Starting recover at 07-MAY-18
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 4 is already on disk as file /arch/1_4_975518966.dbf
archived log for thread 1 with sequence 5 is already on disk as file /arch/1_5_975518966.dbf
archived log for thread 1 with sequence 6 is already on disk as file /arch/1_6_975518966.dbf
archived log for thread 1 with sequence 7 is already on disk as file /arch/1_7_975518966.dbf
archived log for thread 1 with sequence 8 is already on disk as file /arch/1_8_975518966.dbf
archived log for thread 1 with sequence 9 is already on disk as file /arch/1_9_975518966.dbf
archived log for thread 1 with sequence 10 is already on disk as file /arch/1_10_975518966.dbf
archived log for thread 1 with sequence 11 is already on disk as file /arch/1_11_975518966.dbf
archived log for thread 1 with sequence 12 is already on disk as file /arch/1_12_975518966.dbf
archived log for thread 1 with sequence 13 is already on disk as file /arch/1_13_975518966.dbf
archived log for thread 1 with sequence 14 is already on disk as file /arch/1_14_975518966.dbf
archived log file name=/arch/1_4_975518966.dbf thread=1 sequence=4
archived log file name=/arch/1_5_975518966.dbf thread=1 sequence=5
archived log file name=/arch/1_6_975518966.dbf thread=1 sequence=6
archived log file name=/arch/1_7_975518966.dbf thread=1 sequence=7
archived log file name=/arch/1_8_975518966.dbf thread=1 sequence=8
archived log file name=/arch/1_9_975518966.dbf thread=1 sequence=9
archived log file name=/arch/1_10_975518966.dbf thread=1 sequence=10
archived log file name=/arch/1_11_975518966.dbf thread=1 sequence=11
archived log file name=/arch/1_12_975518966.dbf thread=1 sequence=12
media recovery complete, elapsed time: 00:00:01
Finished recover at 07-MAY-18
SQL> create table gyc03 as select * from gyc01;
Table created.
SQL> alter system switch logfile;
System altered.
SQL> select table_name,logging from dba_tables where table_name like 'GYC%';
TABLE_NAME LOG
------------------------------ ---
GYC03 YES
GYC01 NO
SQL> truncate table gyc03;
Table truncated.
SQL> insert into gyc03 select * from gyc01 nologging;
120 rows created.
SQL> /
120 rows created.
SQL> /
120 rows created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size 2230072 bytes
Variable Size 503318728 bytes
Database Buffers 1627389952 bytes
Redo Buffers 4947968 bytes
Database mounted.
SQL> alter database open;
Database altered.
SQL> select count(*) from gyc03;
COUNT(*)
----------
360
SQL> c/3/1
1* select count(*) from gyc01
SQL> /
COUNT(*)
----------
120
SQL>
==========================结论:no force logging,table logging, dml nologging,未出现ora-26040
force logging table nologging dml nologging
force logging table nologging dml nologging
SQL> select FORCE_LOGGING from v$database;
FOR
---
NO
SQL> select table_name,logging from dba_tables where table_name like 'GYC%';
TABLE_NAME LOG
------------------------------ ---
GYC01 NO
GYC03 YES
SQL> create table gyc02 nologging as select * from gyc01;
Table created.
SQL> select table_name,logging from dba_tables where table_name like 'GYC%';
TABLE_NAME LOG
------------------------------ ---
GYC01 NO
GYC03 YES
GYC02 NO
SQL> alter system switch logfie;
alter system switch logfie
*
ERROR at line 1:
ORA-01900: LOGFILE keyword expected
SQL> alter system switch logfile;
System altered.
SQL> insert into gyc02 select * from gyc03 nologging^C
SQL>
SQL> alter database force logging;
Database altered.
SQL> select FORCE_LOGGING from v$database;
FOR
---
YES
SQL> select table_name,logging from dba_tables where table_name like 'GYC%';
TABLE_NAME LOG
------------------------------ ---
GYC01 NO
GYC03 YES
GYC02 NO
SQL> select count(*) from gyc02;
COUNT(*)
----------
120
SQL> truncate table gyc02;
Table truncated.
SQL> select count(*) from gyc02;
COUNT(*)
----------
0
SQL> insert into gyc02 select * from gyc03 nologging;
360 rows created.
SQL> /
360 rows created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
alter system switch logfile;
System altered.
SQL>
System altered.
SQL>
SQL> create table gyc04 as select * from gyc01 where 1=2;
Table created.
SQL> select table_name,logging from dba_tables where table_name like 'GYC%';
TABLE_NAME LOG
------------------------------ ---
GYC01 NO
GYC04 YES
GYC03 YES
GYC02 NO
SQL> select count(*) from gyc04;
COUNT(*)
----------
0
SQL> insert into gyc04 select * from gyc03 nologging;
360 rows created.
SQL> /
360 rows created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size 2230072 bytes
Variable Size 503318728 bytes
Database Buffers 1627389952 bytes
Redo Buffers 4947968 bytes
Database mounted.
SQL>
Recovery Manager complete.
[oracle@DBA180321R00P ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Tue May 8 10:38:24 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: GYCDB (DBID=1624787828, not open)
RMAN> restore database;
Starting restore at 08-MAY-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=189 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 /app/oracle/product/10.2.0/oradata/gycdb/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /app/oracle/product/10.2.0/oradata/gycdb/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /app/oracle/product/10.2.0/oradata/gycdb/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /app/oracle/product/10.2.0/oradata/gycdb/users01.dbf
channel ORA_DISK_1: reading from backup piece /backup/gycdb_01t2afu8_1_1.bak
channel ORA_DISK_1: piece handle=/backup/gycdb_01t2afu8_1_1.bak tag=TAG20180507T174128
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 08-MAY-18
RMAN> recover database;
Starting recover at 08-MAY-18
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 4 is already on disk as file /arch/1_4_975518966.dbf
archived log for thread 1 with sequence 5 is already on disk as file /arch/1_5_975518966.dbf
archived log for thread 1 with sequence 6 is already on disk as file /arch/1_6_975518966.dbf
archived log for thread 1 with sequence 7 is already on disk as file /arch/1_7_975518966.dbf
archived log for thread 1 with sequence 8 is already on disk as file /arch/1_8_975518966.dbf
archived log for thread 1 with sequence 9 is already on disk as file /arch/1_9_975518966.dbf
archived log for thread 1 with sequence 10 is already on disk as file /arch/1_10_975518966.dbf
archived log for thread 1 with sequence 11 is already on disk as file /arch/1_11_975518966.dbf
archived log for thread 1 with sequence 12 is already on disk as file /arch/1_12_975518966.dbf
archived log for thread 1 with sequence 13 is already on disk as file /arch/1_13_975518966.dbf
archived log for thread 1 with sequence 14 is already on disk as file /arch/1_14_975518966.dbf
archived log for thread 1 with sequence 15 is already on disk as file /arch/1_15_975518966.dbf
archived log for thread 1 with sequence 16 is already on disk as file /arch/1_16_975518966.dbf
archived log for thread 1 with sequence 17 is already on disk as file /arch/1_17_975518966.dbf
archived log for thread 1 with sequence 18 is already on disk as file /arch/1_18_975518966.dbf
archived log for thread 1 with sequence 19 is already on disk as file /arch/1_19_975518966.dbf
archived log for thread 1 with sequence 20 is already on disk as file /arch/1_20_975518966.dbf
archived log for thread 1 with sequence 21 is already on disk as file /arch/1_21_975518966.dbf
archived log file name=/arch/1_4_975518966.dbf thread=1 sequence=4
archived log file name=/arch/1_5_975518966.dbf thread=1 sequence=5
archived log file name=/arch/1_6_975518966.dbf thread=1 sequence=6
archived log file name=/arch/1_7_975518966.dbf thread=1 sequence=7
archived log file name=/arch/1_8_975518966.dbf thread=1 sequence=8
archived log file name=/arch/1_9_975518966.dbf thread=1 sequence=9
archived log file name=/arch/1_10_975518966.dbf thread=1 sequence=10
archived log file name=/arch/1_11_975518966.dbf thread=1 sequence=11
archived log file name=/arch/1_12_975518966.dbf thread=1 sequence=12
archived log file name=/arch/1_13_975518966.dbf thread=1 sequence=13
archived log file name=/arch/1_14_975518966.dbf thread=1 sequence=14
archived log file name=/arch/1_15_975518966.dbf thread=1 sequence=15
archived log file name=/arch/1_16_975518966.dbf thread=1 sequence=16
archived log file name=/arch/1_17_975518966.dbf thread=1 sequence=17
archived log file name=/arch/1_18_975518966.dbf thread=1 sequence=18
archived log file name=/arch/1_19_975518966.dbf thread=1 sequence=19
media recovery complete, elapsed time: 00:00:03
Finished recover at 08-MAY-18
SQL> alter database open;
Database altered.
SQL> select count(*) from gyc02;
COUNT(*)
----------
720
SQL> c/2/4
1* select count(*) from gyc04
SQL> /
COUNT(*)
----------
720
SQL> select count(*) from gyc02;
COUNT(*)
----------
720
SQL>
===============结论 force logging, table nologging/logging dml nologging,数据无影响。
force logging, table nologging dml logging
SQL> create table gyc05 nologging as select * from gyc01 where 1=2;
Table created.
SQL> select table_name,logging from dba_tables where table_name like 'GYC%';
TABLE_NAME LOG
------------------------------ ---
GYC01 NO
GYC04 YES
GYC03 YES
GYC02 NO
GYC05 NO
SQL> insert into gyc05 select * from gyc01;
120 rows created.
SQL> /
120 rows created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
shut
System altered.
SQL> down immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size 2230072 bytes
Variable Size 503318728 bytes
Database Buffers 1627389952 bytes
Redo Buffers 4947968 bytes
Database mounted.
RMAN> restore database;
Starting restore at 08-MAY-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=189 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 /app/oracle/product/10.2.0/oradata/gycdb/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /app/oracle/product/10.2.0/oradata/gycdb/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /app/oracle/product/10.2.0/oradata/gycdb/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /app/oracle/product/10.2.0/oradata/gycdb/users01.dbf
channel ORA_DISK_1: reading from backup piece /backup/gycdb_01t2afu8_1_1.bak
channel ORA_DISK_1: piece handle=/backup/gycdb_01t2afu8_1_1.bak tag=TAG20180507T174128
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 08-MAY-18
RMAN> recover database;
Starting recover at 08-MAY-18
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 4 is already on disk as file /arch/1_4_975518966.dbf
archived log for thread 1 with sequence 5 is already on disk as file /arch/1_5_975518966.dbf
archived log for thread 1 with sequence 6 is already on disk as file /arch/1_6_975518966.dbf
archived log for thread 1 with sequence 7 is already on disk as file /arch/1_7_975518966.dbf
archived log for thread 1 with sequence 8 is already on disk as file /arch/1_8_975518966.dbf
archived log for thread 1 with sequence 9 is already on disk as file /arch/1_9_975518966.dbf
archived log for thread 1 with sequence 10 is already on disk as file /arch/1_10_975518966.dbf
archived log for thread 1 with sequence 11 is already on disk as file /arch/1_11_975518966.dbf
archived log for thread 1 with sequence 12 is already on disk as file /arch/1_12_975518966.dbf
archived log for thread 1 with sequence 13 is already on disk as file /arch/1_13_975518966.dbf
archived log for thread 1 with sequence 14 is already on disk as file /arch/1_14_975518966.dbf
archived log for thread 1 with sequence 15 is already on disk as file /arch/1_15_975518966.dbf
archived log for thread 1 with sequence 16 is already on disk as file /arch/1_16_975518966.dbf
archived log for thread 1 with sequence 17 is already on disk as file /arch/1_17_975518966.dbf
archived log for thread 1 with sequence 18 is already on disk as file /arch/1_18_975518966.dbf
archived log for thread 1 with sequence 19 is already on disk as file /arch/1_19_975518966.dbf
archived log for thread 1 with sequence 20 is already on disk as file /arch/1_20_975518966.dbf
archived log for thread 1 with sequence 21 is already on disk as file /arch/1_21_975518966.dbf
archived log for thread 1 with sequence 22 is already on disk as file /arch/1_22_975518966.dbf
archived log for thread 1 with sequence 23 is already on disk as file /arch/1_23_975518966.dbf
archived log for thread 1 with sequence 24 is already on disk as file /arch/1_24_975518966.dbf
archived log file name=/arch/1_4_975518966.dbf thread=1 sequence=4
archived log file name=/arch/1_5_975518966.dbf thread=1 sequence=5
archived log file name=/arch/1_6_975518966.dbf thread=1 sequence=6
archived log file name=/arch/1_7_975518966.dbf thread=1 sequence=7
archived log file name=/arch/1_8_975518966.dbf thread=1 sequence=8
archived log file name=/arch/1_9_975518966.dbf thread=1 sequence=9
archived log file name=/arch/1_10_975518966.dbf thread=1 sequence=10
archived log file name=/arch/1_11_975518966.dbf thread=1 sequence=11
archived log file name=/arch/1_12_975518966.dbf thread=1 sequence=12
archived log file name=/arch/1_13_975518966.dbf thread=1 sequence=13
archived log file name=/arch/1_14_975518966.dbf thread=1 sequence=14
archived log file name=/arch/1_15_975518966.dbf thread=1 sequence=15
archived log file name=/arch/1_16_975518966.dbf thread=1 sequence=16
archived log file name=/arch/1_17_975518966.dbf thread=1 sequence=17
archived log file name=/arch/1_18_975518966.dbf thread=1 sequence=18
archived log file name=/arch/1_19_975518966.dbf thread=1 sequence=19
archived log file name=/arch/1_20_975518966.dbf thread=1 sequence=20
archived log file name=/arch/1_21_975518966.dbf thread=1 sequence=21
archived log file name=/arch/1_22_975518966.dbf thread=1 sequence=22
media recovery complete, elapsed time: 00:00:04
Finished recover at 08-MAY-18
SQL> alter database open;
Database altered.
SQL> select count(*) from gyc03;
COUNT(*)
----------
360
SQL> c/3/5
1* select count(*) from gyc05
SQL> /
COUNT(*)
----------
240
===============结论 force logging, table nologging dml nlogging,数据无影响。
force nologging, table logging/nologging dml nologging
QSQL> create table gyc6 nologging as select * from dba_users nologging;
Table created.
SQL> insert into gyc6 select * from gyc6 nlogging;
30 rows created.
SQL> /
60 rows created.
SQL> commit;
Commit complete.
SQL>
SQL>
SQL>
SQL> create table gyc7 as select * from dba_users nologging;
Table created.
SQL> insert into gyc7 select * from gyc7 nlogging;
30 rows created.
SQL> /
60 rows created.
SQL> create table gyc8 nologging as select * from dba_users nologging;
Table created.
SQL> insert into gyc8 select * from gyc8 nologging;
30 rows created.
SQL> /
60 rows created.
SQL> /
120 rows created.
SQL> commit;
Commit complete.
SQL>
SQL>
SQL> create table gyc9 as select * from dba_users nologging;
Table created.
SQL> insert into gyc9 select * from gyc9 nologging;
30 rows created.
SQL> /
60 rows created.
SQL> /
120 rows created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
/
System altered.
SQL>
System altered.
SQL>
SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
RMAN> restore database;
Starting restore at 08-MAY-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=189 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 /app/oracle/product/10.2.0/oradata/gycdb/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /app/oracle/product/10.2.0/oradata/gycdb/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /app/oracle/product/10.2.0/oradata/gycdb/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /app/oracle/product/10.2.0/oradata/gycdb/users01.dbf
channel ORA_DISK_1: reading from backup piece /backup/gycdb_01t2afu8_1_1.bak
channel ORA_DISK_1: piece handle=/backup/gycdb_01t2afu8_1_1.bak tag=TAG20180507T174128
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 08-MAY-18
RMAN> recover database;
Starting recover at 08-MAY-18
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 4 is already on disk as file /arch/1_4_975518966.dbf
archived log for thread 1 with sequence 5 is already on disk as file /arch/1_5_975518966.dbf
archived log for thread 1 with sequence 6 is already on disk as file /arch/1_6_975518966.dbf
archived log for thread 1 with sequence 7 is already on disk as file /arch/1_7_975518966.dbf
archived log for thread 1 with sequence 8 is already on disk as file /arch/1_8_975518966.dbf
archived log for thread 1 with sequence 9 is already on disk as file /arch/1_9_975518966.dbf
archived log for thread 1 with sequence 10 is already on disk as file /arch/1_10_975518966.dbf
archived log for thread 1 with sequence 11 is already on disk as file /arch/1_11_975518966.dbf
archived log for thread 1 with sequence 12 is already on disk as file /arch/1_12_975518966.dbf
archived log for thread 1 with sequence 13 is already on disk as file /arch/1_13_975518966.dbf
archived log for thread 1 with sequence 14 is already on disk as file /arch/1_14_975518966.dbf
archived log for thread 1 with sequence 15 is already on disk as file /arch/1_15_975518966.dbf
archived log for thread 1 with sequence 16 is already on disk as file /arch/1_16_975518966.dbf
archived log for thread 1 with sequence 17 is already on disk as file /arch/1_17_975518966.dbf
archived log for thread 1 with sequence 18 is already on disk as file /arch/1_18_975518966.dbf
archived log for thread 1 with sequence 19 is already on disk as file /arch/1_19_975518966.dbf
archived log for thread 1 with sequence 20 is already on disk as file /arch/1_20_975518966.dbf
archived log for thread 1 with sequence 21 is already on disk as file /arch/1_21_975518966.dbf
archived log for thread 1 with sequence 22 is already on disk as file /arch/1_22_975518966.dbf
archived log for thread 1 with sequence 23 is already on disk as file /arch/1_23_975518966.dbf
archived log for thread 1 with sequence 24 is already on disk as file /arch/1_24_975518966.dbf
archived log for thread 1 with sequence 25 is already on disk as file /arch/1_25_975518966.dbf
archived log for thread 1 with sequence 26 is already on disk as file /arch/1_26_975518966.dbf
archived log for thread 1 with sequence 27 is already on disk as file /arch/1_27_975518966.dbf
archived log for thread 1 with sequence 28 is already on disk as file /arch/1_28_975518966.dbf
archived log for thread 1 with sequence 29 is already on disk as file /arch/1_29_975518966.dbf
archived log for thread 1 with sequence 30 is already on disk as file /arch/1_30_975518966.dbf
archived log for thread 1 with sequence 31 is already on disk as file /arch/1_31_975518966.dbf
archived log for thread 1 with sequence 32 is already on disk as file /arch/1_32_975518966.dbf
archived log for thread 1 with sequence 33 is already on disk as file /arch/1_33_975518966.dbf
archived log for thread 1 with sequence 34 is already on disk as file /arch/1_34_975518966.dbf
archived log for thread 1 with sequence 35 is already on disk as file /arch/1_35_975518966.dbf
archived log for thread 1 with sequence 36 is already on disk as file /arch/1_36_975518966.dbf
archived log file name=/arch/1_4_975518966.dbf thread=1 sequence=4
archived log file name=/arch/1_5_975518966.dbf thread=1 sequence=5
archived log file name=/arch/1_6_975518966.dbf thread=1 sequence=6
archived log file name=/arch/1_7_975518966.dbf thread=1 sequence=7
archived log file name=/arch/1_8_975518966.dbf thread=1 sequence=8
archived log file name=/arch/1_9_975518966.dbf thread=1 sequence=9
archived log file name=/arch/1_10_975518966.dbf thread=1 sequence=10
archived log file name=/arch/1_11_975518966.dbf thread=1 sequence=11
archived log file name=/arch/1_12_975518966.dbf thread=1 sequence=12
archived log file name=/arch/1_13_975518966.dbf thread=1 sequence=13
archived log file name=/arch/1_14_975518966.dbf thread=1 sequence=14
archived log file name=/arch/1_15_975518966.dbf thread=1 sequence=15
archived log file name=/arch/1_16_975518966.dbf thread=1 sequence=16
archived log file name=/arch/1_17_975518966.dbf thread=1 sequence=17
archived log file name=/arch/1_18_975518966.dbf thread=1 sequence=18
archived log file name=/arch/1_19_975518966.dbf thread=1 sequence=19
archived log file name=/arch/1_20_975518966.dbf thread=1 sequence=20
archived log file name=/arch/1_21_975518966.dbf thread=1 sequence=21
archived log file name=/arch/1_22_975518966.dbf thread=1 sequence=22
archived log file name=/arch/1_23_975518966.dbf thread=1 sequence=23
archived log file name=/arch/1_24_975518966.dbf thread=1 sequence=24
archived log file name=/arch/1_25_975518966.dbf thread=1 sequence=25
archived log file name=/arch/1_26_975518966.dbf thread=1 sequence=26
archived log file name=/arch/1_27_975518966.dbf thread=1 sequence=27
archived log file name=/arch/1_28_975518966.dbf thread=1 sequence=28
archived log file name=/arch/1_29_975518966.dbf thread=1 sequence=29
archived log file name=/arch/1_30_975518966.dbf thread=1 sequence=30
archived log file name=/arch/1_31_975518966.dbf thread=1 sequence=31
archived log file name=/arch/1_32_975518966.dbf thread=1 sequence=32
archived log file name=/arch/1_33_975518966.dbf thread=1 sequence=33
archived log file name=/arch/1_34_975518966.dbf thread=1 sequence=34
media recovery complete, elapsed time: 00:00:06
Finished recover at 08-MAY-18
SQL> startup mount;
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size 2230072 bytes
Variable Size 503318728 bytes
Database Buffers 1627389952 bytes
Redo Buffers 4947968 bytes
Database mounted.
SQL> alter database open;
Database altered.
SQL> select count(*) from gyc9;
COUNT(*)
----------
240
SQL> c/9/8
1* select count(*) from gyc8
SQL> /
select count(*) from gyc8
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 1, block # 89305)
ORA-01110: data file 1: '/app/oracle/product/10.2.0/oradata/gycdb/system01.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
SQL> c/8/7
1* select count(*) from gyc7
SQL> /
COUNT(*)
----------
120
SQL> c/7/6
1* select count(*) from gyc6
SQL> /
select count(*) from gyc6
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 1, block # 89289)
ORA-01110: data file 1: '/app/oracle/product/10.2.0/oradata/gycdb/system01.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
===============结论 noforce logging, table nologging dml nologging,数据报ora-26040,
分享文章:一次rman恢复引起的nologging问题模拟
分享地址:http://hbruida.cn/article/gpdsii.html