一次rman恢復引起的nologging問題模擬
測試環境:
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,
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/24585765/viewspace-2168700/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Nologging對恢復的影響(一)
- Nologging對恢復的影響(二)
- oracle ORA-01180 ORA-01110(rman恢復問題)Oracle
- RMAN增量恢復
- 記一次Git分支合併引起的問題和修復Git
- RMAN恢復實踐
- 一次跨域問題引起的思考跨域
- RMAN恢復之RMAN-06555處理
- 一次inmemory丟失引起的問題分析
- RMAN備份恢復技巧
- Oracle RMAN恢復測試Oracle
- rman 增量備份恢復
- 在rman恢復中incarnation的概念
- 查詢RMAN恢復進度
- Oracle RMAN 表空間恢復Oracle
- RMAN備份異機恢復
- RMAN備份恢復典型案例——異機恢復未知DBID
- RMAN備份恢復效能優化優化
- RMAN備份與恢復測試
- 【vsan資料恢復】vsan下虛擬機器磁碟元件出現問題的資料恢復案例資料恢復虛擬機元件
- 【RMAN】在多租戶環境下的RMAN備份及恢復
- ORACLE DG從庫 Rman備份恢復Oracle
- rman備份異機恢復(原創)
- Oracle 備份恢復篇之RMAN catalogOracle
- RMAN備份恢復典型案例——RMAN備份&系統變慢
- 記一次驚魂的Win10啟動卡死問題恢復過程Win10
- rman恢復控制檔案的一個小錯誤
- 【RMAN】Oracle12c以後rman 備份恢復命令參考Oracle
- RMAN備份恢復典型案例——ORA-00245
- Oracle 12C新特性-RMAN恢復表Oracle
- 12 使用RMAN備份和恢復檔案
- 透過搭建恢復目錄實現RMAN異地備份和恢復
- Oracle 修復由於主庫NOLOGGING引起的備庫ORA-01578和ORA-26040錯誤Oracle
- 修復由於主庫NOLOGGING操作引起的備庫ORA-01578和ORA-26040錯誤
- [20190116]rman的老問題.txt
- 由分號引起的問題
- Oracle9i RMAN 的優缺點及RMAN 備份及恢復步驟Oracle
- 紅警教育陸續推出多套資料恢復國賽模擬考題資料恢復