Simulation of nologging problems caused by a rman recovery 01/15 Update SLTechnology News&Howtos

Simulation of nologging problems caused by a rman recovery

2025-01-15 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Servers >

Shulou(Shulou.com)06/02 Report--

Test environment:

Oracle 11.2.0.4

SLES 11SP4

The conclusions are as follows:

No force logging,table nologging, dml nologging, ora-26040 appears

No force logging,table logging, dml nologging, no ora-26040

Force logging, table nologging/logging dml nologging, no ora-26040

Force logging, table nologging dml logging, no ora-26040

Force logging, table nologging dml nologging, no ora-26040

Only in the case of no force logging, table nologging and dml nologging, ora-26040 will appear in recover data, and all other conditions are normal.

The test process is as follows:

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

one hundred and twenty

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

one hundred and twenty

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 medium ^ 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

^ C

ORA-00443: background process "VKTM" did not start

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

one hundred and twenty

= = conclusion: no force logging,table nologging, dml nologging, ora-26040 appears

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

three hundred and sixty

SQL > cAccord 3 Compact 1

1 * select count (*) from gyc01

SQL > /

COUNT (*)

one hundred and twenty

SQL >

= = conclusion: no force logging,table logging, dml nologging, no 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 logging ^ 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 (*)

one hundred and twenty

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