一次rman恢复引起的nologging问题模拟

发布时间：2020-08-18 23:23:21 来源：ITPUB博客阅读：514 作者：gycixput 栏目：建站服务器

测试环境：

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> startup

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

SQL> select FORCE_LOGGING from v$database;

FOR

---

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(*)

----------

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(*)

----------

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

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