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

(1) brief introduction of bad blocks (ORA-01578 and ORA-26040) caused by NOLOGGING operation

If it's just an error ORA-01578 without accompanying ORA-26040, then this bad block is a bad block caused by something else, and you can try to use RMAN's BMR (Block Media Recovery) to fix it.

If the data segment (table segment, index segment) is defined as a NOLOGGING attribute, the online redo log records very little log information when NOLOGGING plus APPEND, UNRECOVERABLE operation modifies the segment, or uses the data pump (DATAPUMP) impdp parameter DISABLE_ARCHIVE_LOGGING:Y. If these online redo or archive logs are used to recover data files, Oracle marks the corresponding blocks as Soft Corrupt and reports ORA-01578 and ORA-26040 errors the next time they are accessed.

For example:

SQL > select * from test_nologging

ORA-01578: ORACLE data block corrupted (file # 11, block # 84)

ORA-01110: data file 4:'/ oradata/users.dbf'

ORA-26040: Data block was loaded using the NOLOGGING option

The LOGGING column in the data dictionary view DBA_TABLES, DBA_INDEXES, DBA_LOBS, DBA_TAB_PARTITIONS, DBA_LOB_PARTITIONS, DBA_TAB_SUBPARTITIONS records the NOLOGGING attribute. If LOGGING='NO', it means NOLOGGING.

The impdp parameter DISABLE_ARCHIVE_LOGGING:Y of the data pump DATAPUMP disables the LOGGING definition during import, resulting in a NOLOGGING operation. If the corresponding datafile is restored and recovered, then the next query involving the target table will error ORA-1578 and ORA-26040. If the database is in FORCE LOGGING mode, the DISABLE_ARCHIVE_LOGGING option does not turn off LOGGING.

An example of impdp using the parameter "DISABLE_ARCHIVE_LOGGING:Y":

Impdp scott/tiger directory=DATA_PUMP_DIR dumpfile=dp transform=disable_archive_logging:y

Bad blocks caused by NOLOGGING do not cause RMAN backups to fail. Generally speaking, soft corrupt block does not cause RMAN backups to fail, and you do not need to set up MAXCORRUPT. There is soft corrupt block in the database backup, and if you use these backups to restore the data, then the restored data also contains soft corrupt block.

In addition to ORA-26040 errors, block dump may be generated when some other general information appears. If the block dump of the data block contains byte 0xff information or belongs to a segment, the ORA-1578 and ORA-26040 will occur because the part of the media that has recovered the NOLOGGING will cause the corruption.

(2) use RMAN and DBV to detect bad blocks caused by NOLOGGING

When DBV detects bad blocks, if the RDBMS version is less than 10.2.0.4, then the DBV print error DBV-200, and if the RDBMS version is greater than or equal to 10.2.0.4, then the DBV print error DBV-201:

DBV-00200: Block, dba 46137428, already marked corrupted

DBV-00201: Block, DBA 46137428, marked corrupt for invalid redo application

RMAN's VALIDATE command can be used to detect NOLOGGING blocks, and the results are recorded in view V$DATABASE_BLOCK_CORRUPTION (version less than 12c) and V$NONLOGGED_BLOCK (12c and above).

In the following example, check that DATAFILE 4 has 933 bad blocks, query V$DATABASE_BLOCK_CORRUPTION or V$NONLOGGED_BLOCK.

RMAN > VALIDATE DATABASE

...

.

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

-

4 OK 933 1 6401 2275124

File Name: / oracle/dbs/users.dbf

When RMAN detects bad blocks, if the RDBMS version is less than 10.2.0.5 and 11.1.0.7 Magi rman prints the following error:

10.2.0.4 and lower, 11.1.0.6, 11.1.0.7:

RMAN validate reports it in v$database_block_corruption with CORRUPTION_TYPE=LOGICAL

If the RDBMS version is greater than or equal to 10.2.0.5 and 11.2.0.1 Magi rman reports, view the record of CORRUPTION_TYPE=NOLOGGING in the view v$database_block_corruption.

10.2.0.5 and 11.2.0.1:

RMAN validate reports it in v$database_block_corruption with CORRUPTION_TYPE=NOLOGGING

In 12c and later, the result of RMAN validate is not in the view v$database_block_corruption, but in the view v$nonlogged_block. Starting with version 12.2, you can use the new command: "validate.. nonlogged block" to verify the block of nologging.

In the following example, data files 5 and 6 have block for nologged:

RMAN > validate database nonlogged block

Starting validate at...

Using target database control file instead of recovery catalog

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: SID=133 device type=DISK

Channel ORA_DISK_1: starting validation of datafile

Channel ORA_DISK_1: validation complete, elapsed time: 00:00:35

List of Datafiles

=

File Status Nonlogged Blocks Blocks Examined Blocks Skipped

-

1 OK 0 106363 0

2 OK 0 78919 0

3 OK 0 96639 0

4 OK 0 4991 0

5 OK 400 2559 0

6 OK 569 2559 0

Details of nonlogged blocks can be queried from v$nonlogged_block view

The following information is updated in the alarm log:

Started Nonlogged Block Replacement recovery (validate) on file 5 (ospid 26351 rcvid 10616970560844821494)

Finished Nonlogged Block Replacement recovery (validate) on file 5. 400 blocks found

Started Nonlogged Block Replacement recovery (validate) on file 6 (ospid 26351 rcvid 10616970560844821494)

Finished Nonlogged Block Replacement recovery (validate) on file 6.569 blocks found

(3) Monitoring NOLOGGING operations

If the NOLOGGING operation is performed, and then without backup, the RMAN command "REPORT UNRECOVERABLE" can query the affected datafile.

RMAN > report unrecoverable

Using target database control file instead of recovery catalog

Report of files that need backup due to unrecoverable operations

File Type of Backup Required Name

4 full or incremental / oracle/dbs/users.dbf

If the initialization parameter db_unrecoverable_scn_tracking is set to true (the default value, which is not available in 10g), the following will be updated in V$DATAFILE

SYS@lhr121 > select UNRECOVERABLE_CHANGE#

2 UNRECOVERABLE_TIME

3 FIRST_NONLOGGED_SCN

4 FIRST_NONLOGGED_TIME from v$datafile where file#=6

UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME FIRST_NONLOGGED_SCN FIRST_NONLOGGED_TIM

--

2878238 2018-04-10 10:53:47 2878238 2018-04-10 10:53:47

In version 11.2.0.4 or 12.1.0.2 +, when event 16490 is set, the MRP process of the physical slave will detect the NOLOGGING change and record it in alert log.

ORA-16490 "logging invalidated blocks on standby due to invalidation redo"

"INVD_BLKS: Invalidating (file, bno)"

"fname: 'Datafile name'. rdba:..."

(4) identify when the data block is marked as NOLOGGING

To identify when a data block is marked as NOLOGGING, you can convert the CORRUPTION_CHANGE# value of the data block SCN in the trace file or in the v$database_block_coruption view to time:

① uses the block SCN in the trace file, for example:

Start dump data blocks tsn: 60 file#: 4 minblk 84 maxblk 84

Buffer tsn: 3 rdba: 0x02c00054 (11 Compact 84)

Scn: 0x0771.4fa24eb5 seq: 0xff flg: 0x04 tail: 0x4eb500ff

Extract the SCN value 0x0771.4fa24eb5, delete'.', and then convert 0x07714fa24eb to decimal 511453045995.

② uses the CORRUPTION_CHANGE# value in the v$database_block_coruption view

If you run the RMAN validate command and corruption_type='NOLOGGING' (10.2.0.5 and 11.2.0.1 +) in the v$database_block_coruption view, the value of the CORRUPTION_CHANGE# column is the decimal SCN value. The SCN Timestamp time can be obtained using the following methods:

Select scn_to_timestamp (& & decimal_scn) from dual

If you run RMAN validate:

Select file#, block#, scn_to_timestamp (CORRUPTION_CHANGE#)

From v$database_block_corruption

Where CORRUPTION_TYPE='NOLOGGING'

In 12c:

Select file#, block#, scn_to_timestamp (NONLOGGED_START_CHANGE#) from v$nonlogged_block

If the query gv$archived_log or gv$log_history encounters an error ORA-08181:

Alter session set nls_date_format = 'DD-MON-YY HH24:MI:SS'

Select first_time, next_time

From gv$archived_log

Where & decimal_scn between first_change# and next_change#

Or

Select first_time

From gv$log_history

Where & decimal_scn between first_change# and next_change#

If you run RMAN validate:

Alter session set nls_date_format = 'DD-MON-YY HH24:MI:SS'

Select file#, block#, first_time, next_time

From v$archived_log, v$database_block_corruption

Where CORRUPTION_CHANGE# between first_change# and next_change#

And CORRUPTION_TYPE='NOLOGGING'

Or

Select file#,block#,first_time

From v$log_history, v$database_block_corruption

Where CORRUPTION_CHANGE# between first_change# and next_change#

And CORRUPTION_TYPE='NOLOGGING'

12c:

Alter session set nls_date_format = 'DD-MON-YY HH24:MI:SS'

Select file#, block#, first_time, next_time

From v$nonlogged_block, v$archived_log

Where NONLOGGED_START_CHANGE# between first_change# and next_change#

Or

Select file#, block#, first_time

From v$nonlogged_block, v$log_history

Where NONLOGGED_START_CHANGE# between first_change# and next_change#

(5) NOARCHIVELOG and NOLOGGING problems occur in SYSAUX tablespaces, AWR, EM, etc.

If the database version is 11.1.0.6 or 11.1.0.7 or 11.2.0.1, DIRECT PATH has been performed on the NOLOGGING object, and the RECOVER DATABASE command has been subsequently executed, even if the database FORCE LOGGING is open, there will be ORA-1578 and ORA-26040 errors. This problem often occurs with AWR or EM objects in SYSAUX tablespaces. Please refer to Note 1071869.1. Note that the current version of the database may be greater than 11.1 or 11.2.0.1, but the problem may have occurred before the upgrade. This constraint is removed in version 11.2.0.2 and this problem does not occur in 10g.

RDBMS version changes:

RDBMS version

change

10.2.0.4 +

DBverify reports NOLOGGING block error message "DBV-00201: Block, DBA, marked corrupt for invalid redo application"

10.2.0.5, 10.2.0.1 +

The RMAN validate command checks NOLOGGING block and records corruption_type='NOLOGGING' in the v$database_block_coruption view

11g +

Introduce db_unrecoverable_scn_tracking parameter

11.1.0.6 or 11.1.0.7 or 11.2.0.1

NOARCHIVELOG schema database, performs DIRECT PATH operations on NOLOGGING objects, and later manually recovers the database, reporting ORA-1578 and ORA-26040 even if FORCE LOGGING is opened. This constraint is removed in version 11.2.0.2 and this problem does not occur at 10g.

12c

The result of RMAN validate is not in the view v$database_block_corruption, but in the view v$nonlogged_block

12.2

The following RMAN commands are introduced:

RMAN > validate [database / datafile] nonlogged block

RMAN > recover [database / datafile] nonlogged block;-> for Standby databases

(6) solution

Bad blocks caused by NOLOGGING operations cannot be repaired, such as "Media Recovery" or "RMAN blockrecover". The feasible way is to back up the corresponding data files immediately after the NOLOGGING operation.

If the error occurs after executing RMAN DUPLICATE or RESTORE, open FORCE LOGGING in the source library, and then rerun RMAN DUPLICATE or RESTORE.

Alter database force logging

If the error occurs in the physical STANDBY database, the affected data files can be recovered from the main database (only if the main database does not have this problem). Reference document Doc ID 958181.1. You can use the RMAN options RECOVER NONLOGGED BLOCK with DATAFILE, TABLESPACE, DATABASE in Oracle 12c. For example:

RMAN > RECOVER DATABASE NONLOGGED BLOCK

To avoid this problem, force the production log in the main library:

Alter database force logging

If the data block of the same datafile appears nologging bad block in the main database, but the standby database does not have it, you can skip the bad block manually or set event 10231. The occurrence of nologging bad blocks in the master database may be due to the backup and recovery of the master database or the execution of switchover in the previous backup database.

If the NOLOGGING data block is in an idle data block (which can be queried by the dba_free_space view), then the DBVerify check will find this problem, report an error DBV-00201 or display it in the v$database_block_corruption view. In this case, you can wait for the data block to be formatted automatically or manually when it is reused.

If it is an index, then you can drop/create the index. If it is a table, you can use the stored procedure DBMS_REPAIR.SKIP_CORRUPT_BLOCKS to skip the bad block and then consider whether to rebuild the table.

After deleting a segment with a bad block, the bad block is idle and can later be assigned to other objects or segments, and when the bad block is assigned to other objects or segments, the data block is reformatted. If the v$database_block_corruption view still appears as a bad block, you can manually run rman validate to clear the information in the view.

If it is LOB, please refer to Note 293515.1.

Experiment 1: DISABLE_ARCHIVE_LOGGING:Y

RMAN > list backupset of datafile 6

List of Backup Sets

=

BS Key Type LV Size Device Type Elapsed Time Completion Time

12 Full 352.78M DISK 00:03:21 2018-04-09 14:50:59

BP Key: 12 Status: AVAILABLE Compressed: YES Tag: TAG20180409T144738

Piece Name: / u04/flash_recovery_area/LHR121/backupset/2018_04_09/o1_mf_nnndf_TAG20180409T144738_fdp34bfm_.bkp

List of Datafiles in backup set 12

File LV Type Ckp SCN Ckp Time Name

6 Full 2865977 2018-04-09 14:47:38 / u04/oradata/lhr121/users01.dbf

[oracle@rhel6lhr env_oracle] $impdp scott/tiger dumpfile=log.dmp tables=T_LOG TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y remap_schema=LHR:SCOTT

Import: Release 12.1.0.2.0-Production on Tue Apr 10 10:53:17 2018

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0-64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

ORA-39002: invalid operation

ORA-39166: Object SCOTT.T_LOG was not found or could not be exported or imported.

[oracle@rhel6lhr env_oracle] $impdp lhr/lhr dumpfile=log.dmp tables=T_LOG TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y remap_schema=LHR:SCOTT

Import: Release 12.1.0.2.0-Production on Tue Apr 10 10:53:40 2018

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0-64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Master table "LHR". "SYS_IMPORT_TABLE_02" successfully loaded/unloaded

Starting "LHR". "SYS_IMPORT_TABLE_02": lhr/* dumpfile=log.dmp tables=T_LOG TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y remap_schema=LHR:SCOTT

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . Imported "SCOTT". "T_LOG" 34.24 KB 9 rows

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

Job "LHR". "SYS_IMPORT_TABLE_02" successfully completed at Tue Apr 10 10:54:14 2018 elapsed 0 00:00:32

[oracle@rhel6lhr env_oracle] $rm-rf / u04/oradata/lhr121/users01.dbf

[oracle@rhel6lhr env_oracle] $rman target /

Recovery Manager: Release 12.1.0.2.0-Production on Tue Apr 10 10:55:09 2018

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

Connected to target database: LHR121 (DBID=3221842516)

RMAN > restore datafile 6

Starting restore at 2018-04-10 10:55:17

Using target database control file instead of recovery catalog

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: SID=29 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 00006 to / u04/oradata/lhr121/users01.dbf

Channel ORA_DISK_1: reading from backup piece / u04/flash_recovery_area/LHR121/backupset/2018_04_09/o1_mf_nnndf_TAG20180409T144738_fdp34bfm_.bkp

RMAN-00571: =

RMAN-00569: = ERROR MESSAGE STACK FOLLOWS =

RMAN-00571: =

RMAN-03002: failure of restore command at 04/10/2018 10:55:20

ORA-19870: error while restoring backup piece / u04/flash_recovery_area/LHR121/backupset/2018_04_09/o1_mf_nnndf_TAG20180409T144738_fdp34bfm_.bkp

ORA-19573: cannot obtain exclusive enqueue for datafile 6

RMAN > startup force mount

Oracle instance started

Database mounted

Total System Global Area 658505728 bytes

Fixed Size 2927864 bytes

Variable Size 285213448 bytes

Database Buffers 364904448 bytes

Redo Buffers 5459968 bytes

RMAN > restore datafile 6

Starting restore at 2018-04-10 10:57:02

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: SID=12 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 00006 to / u04/oradata/lhr121/users01.dbf

Channel ORA_DISK_1: reading from backup piece / u04/flash_recovery_area/LHR121/backupset/2018_04_09/o1_mf_nnndf_TAG20180409T144738_fdp34bfm_.bkp

Channel ORA_DISK_1: piece handle=/u04/flash_recovery_area/LHR121/backupset/2018_04_09/o1_mf_nnndf_TAG20180409T144738_fdp34bfm_.bkp tag=TAG20180409T144738

Channel ORA_DISK_1: restored backup piece 1

Channel ORA_DISK_1: restore complete, elapsed time: 00:00:03

Finished restore at 2018-04-10 10:57:06

RMAN > recover datafile 6

Starting recover at 2018-04-10 10:57:13

Using channel ORA_DISK_1

Starting media recovery

Archived log for thread 1 with sequence 39 is already on disk as file / u04/flash_recovery_area/LHR121/archivelog/2018_04_09/o1_mf_1_39_fdp3bvmm_.arc

Archived log for thread 1 with sequence 40 is already on disk as file / u04/flash_recovery_area/LHR121/archivelog/2018_04_09/o1_mf_1_40_fdp3bvoz_.arc

Archived log for thread 1 with sequence 41 is already on disk as file / u04/flash_recovery_area/LHR121/archivelog/2018_04_09/o1_mf_1_41_fdp7bhhl_.arc

Archived log for thread 1 with sequence 42 is already on disk as file / u04/flash_recovery_area/LHR121/archivelog/2018_04_09/o1_mf_1_42_fdp7ccfv_.arc

Archived log for thread 1 with sequence 43 is already on disk as file / u04/flash_recovery_area/LHR121/archivelog/2018_04_10/o1_mf_1_43_fdr7xxg4_.arc

Archived log for thread 1 with sequence 44 is already on disk as file / u04/flash_recovery_area/LHR121/archivelog/2018_04_10/o1_mf_1_44_fdr7yc13_.arc

Archived log file name=/u04/flash_recovery_area/LHR121/archivelog/2018_04_09/o1_mf_1_39_fdp3bvmm_.arc thread=1 sequence=39

Archived log file name=/u04/flash_recovery_area/LHR121/archivelog/2018_04_09/o1_mf_1_40_fdp3bvoz_.arc thread=1 sequence=40

Archived log file name=/u04/flash_recovery_area/LHR121/archivelog/2018_04_09/o1_mf_1_41_fdp7bhhl_.arc thread=1 sequence=41

Archived log file name=/u04/flash_recovery_area/LHR121/archivelog/2018_04_09/o1_mf_1_42_fdp7ccfv_.arc thread=1 sequence=42

Media recovery complete, elapsed time: 00:00:01

Finished recover at 2018-04-10 10:57:15

RMAN > alter database open

Statement processed

RMAN >

SYS@lhr121 > select * from v$nonlogged_block

FILE# BLOCK# BLOCKS NONLOGGED_START_CHANGE# NONLOGGED_START_TIM NONLOGGED_END_CHANGE# NONLOGGED_END_TIME RESETLOGS_CHANGE# RESETLOGS_TIME OBJECT# REASON CON_ID

- -

6 1939 1 2878238 2878238 UNKNOWN 0

SYS@lhr121 > select file#, block#, scn_to_timestamp (NONLOGGED_START_CHANGE#)

2 from v$nonlogged_block

FILE# BLOCK# SCN_TO_TIMESTAMP (NONLOGGED_START_CHANGE#)

-

6 1939 10-APR-18 10.52.44.000000000 AM

SYS@lhr121 > SELECT TABLESPACE_NAME

2 SEGMENT_TYPE

3 OWNER

4 SEGMENT_NAME

5 PARTITION_NAME

6 FROM DBA_EXTENTS

7 WHERE FILE_ID = 6

8 AND 1939 BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS-1

9

TABLESPACE_NAME SEGMENT_TYPE OWNER SEGMENT_NAME PARTITION_NAME

--

USERS TABLE SCOTT T_LOG

SYS@lhr121 > SELECT DBMS_ROWID.ROWID_OBJECT (ROWID) OBJECT_ID

2 DBMS_ROWID.ROWID_RELATIVE_FNO (ROWID) FILE_ID

3 DBMS_ROWID.ROWID_BLOCK_NUMBER (ROWID) BLOCK_ID

4 COUNT (1) COUNTS

5 FROM scott.t_log

6 GROUP BY DBMS_ROWID.ROWID_OBJECT (ROWID)

7 DBMS_ROWID.ROWID_RELATIVE_FNO (ROWID)

8 DBMS_ROWID.ROWID_BLOCK_NUMBER (ROWID)

9 ORDER BY DBMS_ROWID.ROWID_BLOCK_NUMBER (ROWID)

OBJECT_ID FILE_ID BLOCK_ID COUNTS

--

94411 6 1939 9

SYS@lhr121 >

SYS@lhr121 > select * from scott.t_log

Select * from scott.t_log

*

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 6, block # 1939)

ORA-01110: data file 6:'/ u04Universe oradatabel lhr121max users01.dbf'

ORA-26040: Data block was loaded using the NOLOGGING option

SYS@lhr121 > select UNRECOVERABLE_CHANGE#

2 UNRECOVERABLE_TIME

3 FIRST_NONLOGGED_SCN

4 FIRST_NONLOGGED_TIME from v$datafile where file#=6

UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME FIRST_NONLOGGED_SCN FIRST_NONLOGGED_TIM

--

2878238 2018-04-10 10:53:47 2878238 2018-04-10 10:53:47

Experiment 2: NOLOGGING+APPEND

LHR@ora11g > create table test_nologging as select * from user_tables

Table created.

LHR@ora11g > alter table test_nologging nologging

Table altered.

LHR@ora11g > select tablespace_name from user_tables where table_name='TEST_NOLOGGING'

TABLESPACE_NAME

-

USERS

SYS@ora11g > alter database no force logging

Database altered.

SYS@ora11g > select force_logging from v$database

FOR

-

NO

SYS@ora11g > alter database drop supplemental log data

Database altered.

SYS@ora11g > alter database drop supplemental log data (all,primary key,unique,foreign key) columns

Database altered.

SYS@ora11g > SELECT supplemental_log_data_min min

2 supplemental_log_data_pk competition

3 supplemental_log_data_ui ui

4 supplemental_log_data_fk fk

5 supplemental_log_data_all allc

6 FROM v$database

MIN competes for UI FK ALL

NO NO NO NO NO

LHR@ora11g > exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@rhel6lhr env_oracle] $rman target /

Recovery Manager: Release 11.2.0.3.0-Production on Tue Apr 10 13:27:25 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to target database: ORA11G (DBID=4270446895)

RMAN > backup datafile 4

Starting backup at 2018-04-10 13:27:37

Using target database control file instead of recovery catalog

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: SID=155 device type=DISK

Channel ORA_DISK_1: starting full datafile backup set

Channel ORA_DISK_1: specifying datafile (s) in backup set

Input datafile file number=00004 name=/u01/app/oracle/oradata/ora11g/users01.dbf

Channel ORA_DISK_1: starting piece 1 at 2018-04-10 13:27:39

Channel ORA_DISK_1: finished piece 1 at 2018-04-10 13:29:36

Piece handle=/u05/app/oracle/flash_recovery_area/ORA11G/backupset/2018_04_10/o1_mf_nnndf_TAG20180410T132739_fdrltcot_.bkp tag=TAG20180410T132739 comment=NONE

Channel ORA_DISK_1: backup set complete, elapsed time: 00:01:58

Finished backup at 2018-04-10 13:29:37

RMAN > exit

Recovery Manager complete.

[oracle@rhel6lhr oradata] $sas

SQL*Plus: Release 11.2.0.3.0 Production on Tue Apr 10 14:14:15 2018

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@ora11g > conn lhr/lhr

Connected.

LHR@ora11g > insert / * + append * / into TEST_NOLOGGING select * from test_nologging

3264 rows created.

LHR@ora11g > commit

Commit complete.

LHR@ora11g > exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@rhel6lhr oradata] $mv / u01/app/oracle/oradata/ora11g/users01.dbf / u01/app/oracle/oradata/ora11g/users01.dbf_bk

[oracle@rhel6lhr oradata] $

[oracle@rhel6lhr oradata] $sas

SQL*Plus: Release 11.2.0.3.0 Production on Tue Apr 10 14:11:34 2018

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@ora11g > startup force mount

Exit

ORACLE instance started.

Total System Global Area 409194496 bytes

Fixed Size 2228864 bytes

Variable Size 322964864 bytes

Database Buffers 75497472 bytes

Redo Buffers 8503296 bytes

Database mounted.

SYS@ora11g > Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@rhel6lhr oradata] $

[oracle@rhel6lhr oradata] $

[oracle@rhel6lhr oradata] $

[oracle@rhel6lhr oradata] $

[oracle@rhel6lhr oradata] $rman target /

Recovery Manager: Release 11.2.0.3.0-Production on Tue Apr 10 14:11:57 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to target database: ORA11G (DBID=4270446895, not open)

RMAN > restore datafile 4

Starting restore at 2018-04-10 14:12:07

Using target database control file instead of recovery catalog

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: SID=133 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 00004 to / u01/app/oracle/oradata/ora11g/users01.dbf

Channel ORA_DISK_1: reading from backup piece / u05/app/oracle/flash_recovery_area/ORA11G/backupset/2018_04_10/o1_mf_nnndf_TAG20180410T132739_fdrltcot_.bkp

Channel ORA_DISK_1: piece handle=/u05/app/oracle/flash_recovery_area/ORA11G/backupset/2018_04_10/o1_mf_nnndf_TAG20180410T132739_fdrltcot_.bkp tag=TAG20180410T132739

Channel ORA_DISK_1: restored backup piece 1

Channel ORA_DISK_1: restore complete, elapsed time: 00:01:16

Finished restore at 2018-04-10 14:13:24

RMAN > recover datafile 4

Starting recover at 2018-04-10 14:13:34

Using channel ORA_DISK_1

Starting media recovery

Archived log for thread 1 with sequence 330 is already on disk as file / u05/app/oracle/flash_recovery_area/ORA11G/archivelog/2018_04_10/o1_mf_1_330_fdrm7pm4_.arc

Archived log for thread 1 with sequence 331 is already on disk as file / u05/app/oracle/flash_recovery_area/ORA11G/archivelog/2018_04_10/o1_mf_1_331_fdrmly0v_.arc

Archived log for thread 1 with sequence 332 is already on disk as file / u05/app/oracle/flash_recovery_area/ORA11G/archivelog/2018_04_10/o1_mf_1_332_fdrn29bv_.arc

Archived log for thread 1 with sequence 333 is already on disk as file / u05/app/oracle/flash_recovery_area/ORA11G/archivelog/2018_04_10/o1_mf_1_333_fdrnohdf_.arc

Archived log for thread 1 with sequence 334 is already on disk as file / u05/app/oracle/flash_recovery_area/ORA11G/archivelog/2018_04_10/o1_mf_1_334_fdrnwqqw_.arc

Archived log file name=/u05/app/oracle/flash_recovery_area/ORA11G/archivelog/2018_04_10/o1_mf_1_330_fdrm7pm4_.arc thread=1 sequence=330

Archived log file name=/u05/app/oracle/flash_recovery_area/ORA11G/archivelog/2018_04_10/o1_mf_1_331_fdrmly0v_.arc thread=1 sequence=331

Archived log file name=/u05/app/oracle/flash_recovery_area/ORA11G/archivelog/2018_04_10/o1_mf_1_332_fdrn29bv_.arc thread=1 sequence=332

Media recovery complete, elapsed time: 00:00:01

Finished recover at 2018-04-10 14:13:37

RMAN > alter database open

Database opened

RMAN > exit

Recovery Manager complete.

[oracle@rhel6lhr oradata] $sas

SQL*Plus: Release 11.2.0.3.0 Production on Tue Apr 10 14:14:15 2018

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@ora11g > select count (1) from lhr.test_nologging

Select count (1) from lhr.test_nologging

*

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 4, block # 180937)

ORA-01110: data file 4:'/ u01 *

ORA-26040: Data block was loaded using the NOLOGGING option

SYS@ora11g > select UNRECOVERABLE_CHANGE#

2 UNRECOVERABLE_TIME

3 FIRST_NONLOGGED_SCN

4 FIRST_NONLOGGED_TIME

5 from v$datafile

6 where file#=4

UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME FIRST_NONLOGGED_SCN FIRST_NONLOGGED_TIM

--

60522292 2018-04-10 14:11:22 60522291 2018-04-10 14:11:22

SYS@ora11g > select * from v$database_block_corruption

No rows selected

SYS@ora11g > exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@rhel6lhr oradata] $rman target /

Recovery Manager: Release 11.2.0.3.0-Production on Tue Apr 10 14:15:42 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to target database: ORA11G (DBID=4270446895)

RMAN > validate datafile 4

Starting validate at 2018-04-10 14:15:50

Using target database control file instead of recovery catalog

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: SID=21 device type=DISK

Channel ORA_DISK_1: starting validation of datafile

Channel ORA_DISK_1: specifying datafile (s) for validation

Input datafile file number=00004 name=/u01/app/oracle/oradata/ora11g/users01.dbf

Channel ORA_DISK_1: validation complete, elapsed time: 00:00:35

List of Datafiles

=

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

-

4 OK 103 64922 196829 60543025

File Name: / u01/app/oracle/oradata/ora11g/users01.dbf

Block Type Blocks Failing Blocks Processed

Data 0 19747

Index 0 5352

Other 0 106779

Finished validate at 2018-04-10 14:16:26

RMAN > exit

Recovery Manager complete.

[oracle@rhel6lhr oradata] $sas

SQL*Plus: Release 11.2.0.3.0 Production on Tue Apr 10 14:16:44 2018

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@ora11g > select * from v$database_block_corruption

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO

--

4 180937 15 60522291 NOLOGGING

4 180994 88 60522292 NOLOGGING

SYS@ora11g > exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@rhel6lhr oradata] $rmant target /

-bash: rmant: command not found

[oracle@rhel6lhr oradata] $rman target /

Recovery Manager: Release 11.2.0.3.0-Production on Tue Apr 10 14:21:04 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to target database: ORA11G (DBID=4270446895)

RMAN > BLOCKRECOVER CORRUPTION LIST

Starting recover at 2018-04-10 14:21:08

Using target database control file instead of recovery catalog

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: SID=133 device type=DISK

Starting media recovery

Media recovery complete, elapsed time: 00:00:00

Finished recover at 2018-04-10 14:21:10

RMAN > exit

Recovery Manager complete.

[oracle@rhel6lhr oradata] $sas

SQL*Plus: Release 11.2.0.3.0 Production on Tue Apr 10 14:21:17 2018

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@ora11g > select * from v$database_block_corruption

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO

--

4 180937 15 60522291 NOLOGGING

4 180994 88 60522292 NOLOGGING

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.