189 8069 5689

一次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://jkwzsj.com/article/gpdsii.html

其他资讯