watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=

          subject         part

stay Oracle in , How to fix due to the main library NOLOGGING Standby caused by ORA-01578 and ORA-26040 error ?


     
          Answer section          


as everyone knows ,DG Data synchronization is based on log flow , That's why you're configuring DG The stage needs to set the main library to FORCE LOGGING Why . however , It's going to bring a lot of problems , for example , It can lead to DML Type of SQL Slow down in execution , Especially when a large number of data are updated or imported .DBA When using data pump for migration, we hope to complete it within the minimum downtime , At this time, we may consider the minimum log import method to speed up the import speed , Then resynchronize the standby library . In these scenarios ,DBA May use NOLOGGING Operation to save a lot of data insertion time , And the problem with this operation is , If the library has a backup Library , Because the main library NOLOGGING The insert operation does not generate Redo, So it will not be transferred and applied on the standby database , This will cause problems with the data in the standby database , newspaper ORA-01578 and ORA-26040 Error of .

On a master database with master-slave relationship FORCE_LOGGING Set to NOLOGGING Pattern , Then create a table LHR.TESTDGNOLOG, Set to NOLOGGING Pattern :

1SQL> ALTER DATABASE NO FORCE LOGGING;
2SQL> CREATE TABLE LHR.TESTDGNOLOG TABLESPACE USERS PCTFREE 99 AS SELECT ROWNUM N FROM XMLTABLE('1 TO 100');
3SQL> ALTER TABLE LHR.TESTDGNOLOG NOLOGGING;
     


Then use /* +append*/ Insert data and submit :

1SQL> INSERT /*+ APPEND */ INTO LHR.TESTDGNOLOG SELECT ROWNUM N FROM XMLTABLE('1 TO 1000');
2SQL> COMMIT
     


At this time, when you query the table in the standby database, you will see the following error message :

1SQL>SELECT COUNT(1) FROM LHR.TESTDGNOLOG;
2SELECT COUNT(1) FROM LHR.TESTDGNOLOG
3                 *
4ERROR at line 1:
5ORA-01578: ORACLE data block corrupted (file # 4, block # 819)
6ORA-01110: data file 4: '/data/data1/ORCL2/datafile/o1_mf_users_3ft1e9qb_.dbf'
7ORA-26040: Data block was loaded using the NOLOGGING option
     


In this case , stay Oracle There are different ways to deal with it in different versions of .

( One )Oracle 11g

stay Oracle 11g in , If there is such a problem , The problem can be solved by copying the data file containing the missing data from the main database to the physical backup database, and then renaming the data file .

1、 Query the main database

1SQL> SELECT NAME, UNRECOVERABLE_CHANGE# FROM V$DATAFILE;
2NAME                                        UNRECOVERABLE_CHANGE#
3-------------------------------------------- ---------------------
4+DATADG/orcl/datafile/system.270.972381717                      0
5+DATADG/orcl/datafile/sysaux.265.972381717                      0
6+DATADG/orcl/datafile/undotbs1.261.972381717                    0
7+DATADG/orcl/datafile/users.259.972381717                 6252054
     


2、 Query backup database

[email protected]>SELECT NAME, UNRECOVERABLE_CHANGE# FROM V$DATAFILE;
2NAME                                                      UNRECOVERABLE_CHANGE#
3--------------------------------------------------------- ---------------------
4/data/data1/ORCLDG/datafile/o1_mf_system_3dt1e9op_.dbf                       0
5/data/data1/ORCLDG/datafile/o1_mf_sysaux_3ct1e9nb_.dbf                       0
6/data/data1/ORCLDG/datafile/o1_mf_undotbs1_3gt1e9qq_.dbf                     0
7/data/data1/ORCLDG/datafile/o1_mf_users_3ft1e9qb_.dbf                  5383754
     


3、 Compare the query results of the primary database and the standby database

In the above two query results , Compare UNRECOVERABLE_CHANGE# The value of the column . If it's in the main library UNRECOVERABLE_CHANGE# The value of the column is greater than the same column in the standby library , Then you need to restore these data files in the standby database .

Copy the data file corresponding to the main database to the standby database :

1SQL> ALTER TABLESPACE USERS BEGIN BACKUP;
2SQL> EXIT
3ASMCMD>cp +DATADG/orcl/datafile/users.259.972381717 /tmp
4$ scp /tmp/users.259.972381717 10.10.10.123:/data/data1/ORCL2/datafile/ 
5SQL> ALTER TABLESPACE USERS END BACKUP;
     


On the back-up , Put the old data file RENAME To a new data file :

1SQL> STARTUP MOUNT FORCE
2SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
3SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL; # Execute in the standby database RENAME In operation , This parameter is required to be MANUAL
4SQL> ALTER DATABASE  RENAME FILE '/data/data1/ORCLDG/datafile/o1_mf_users_3ft1e9qb_.dbf' TO '/data/data1/ORCLDG/datafile/users.259.972381717';
5SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
6SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
     


Then you can query the instance table in the standby database LHR.TESTDGNOLOG:

1SQL> SELECT COUNT(1) FROM LHR.TESTDGNOLOG;
2  COUNT(1)
3----------
4    1100
     


( Two )Oracle 12.1

In this case , stay Oracle 12.1 In the version ,RMAN Provides a convenient way to DBA It is no longer necessary to backup and transfer data files in the main database, but can be directly used in the backup database restore database (or datafile ) from service Resume .

Of course , If the data file is in a normal state ,RMAN We can do jump recovery according to their data file header . If , because NOLOGGING The operation caused some blocks to be marked as corrupt , Then this part of the data file needs to be recovered . In the recovery command there are FORCE Options . Because sometimes data files are synchronized , The real-time log application process is still running . This is the time , In order to recover , Need to stop applying . Once the app stops , Then there's no need to execute RESOTORE DATABASE FORCE operation , Because the status of the data file is too old now , Even without FORCE Options RMAN I won't skip these data files .

The standby database turns off the real-time log application , And restart to MOUNT state :

1SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
2SQL> SHUTDOWN IMMEDIATE
3Database closed.
4Database dismounted.
5ORACLE instance shut down.
6SQL> STARTUP MOUNT
7ORACLE instance started
     


Backup library login RMAN, Use restore database (or datafile ) from service Resume :

 1RMAN> RESTORE DATABASE FROM SERVICE 'primary_db'; # there primary_db It's from the backup library to the main library TNS Alias of connection string
 2Starting restore at 2018-07-03 17:00:35
 3using target database control file instead of recovery catalog
 4allocated channel: ORA_DISK_1
 5channel ORA_DISK_1: SID=29 device type=DISK
 6channel ORA_DISK_1: starting datafile backup set restore
 7channel ORA_DISK_1: using network backup set from service primary_db
 8channel ORA_DISK_1: specifying datafile(s) to restore from backup set
 9channel ORA_DISK_1: restoring datafile 00001 to /data/data1/ORCLDG/datafile/o1_mf_system_02t1t9ck_.dbf
10channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
11channel ORA_DISK_1: starting datafile backup set restore
12channel ORA_DISK_1: using network backup set from service primary_db
13channel ORA_DISK_1: specifying datafile(s) to restore from backup set
14channel ORA_DISK_1: restoring datafile 00003 to /data/data1/ORCLDG/datafile/o1_mf_sysaux_03t1t9d3_.dbf
15channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
16channel ORA_DISK_1: starting datafile backup set restore
17channel ORA_DISK_1: using network backup set from service primary_db
18channel ORA_DISK_1: specifying datafile(s) to restore from backup set
19channel ORA_DISK_1: restoring datafile 00004 to /data/data1/ORCLDG/datafile/o1_mf_undotbs1_04t1t9di_.dbf
20channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
21channel ORA_DISK_1: starting datafile backup set restore
22channel ORA_DISK_1: using network backup set from service primary_db
23channel ORA_DISK_1: specifying datafile(s) to restore from backup set
24channel ORA_DISK_1: restoring datafile 00006 to /data/data1/ORCLDG/datafile/o1_mf_users_05t1t9dm_.dbf
25channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
26Finished restore at 2018-07-03 17:01:34
     


Of course, remember to start the library and start the real-time log application process . The above recovery process can also directly recover the relevant data files :

 1RMAN> RESTORE DATAFILE 7 FROM SERVICE 'LHR122';
 2
 3Starting restore at 2018-07-20 09:39:28
 4using target database control file instead of recovery catalog
 5allocated channel: ORA_DISK_1
 6channel ORA_DISK_1: SID=24 device type=DISK
 7
 8channel ORA_DISK_1: starting datafile backup set restore
 9channel ORA_DISK_1: using network backup set from service lhr122
10channel ORA_DISK_1: specifying datafile(s) to restore from backup set
11channel ORA_DISK_1: restoring datafile 00007 to /u04/oradata/lhr122dg/LHR122DG/datafile/users01_bk.dbf
12channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
13Finished restore at 2018-07-20 09:39:32
14
15RMAN> alter database open;
16
17Statement processed
     


( 3、 ... and )Oracle 12.2

stay Oracle 12.2 in ,Oracle It provides a more convenient way to recover. The main library will send the list of unrecorded blocks to the standby library , And record it in the backup control file ,DBA From the backup Library V$NONLOGGED_BLOCK This view looks at the relevant information . There is no need to send the entire data file of the main library , But in RMAN Execute a simple command to restore them :

1RECOVER DATABASE NONLOGGED BLOCK
     

First , Stop the real-time log application in the standby database :

1SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
     

Backup library login RMAN perform :

1RECOVER DATABASE NONLOGGED BLOCK
     

Be careful : Before performing this step, please confirm the LOG_ARCHIVE_CONFIG Parameters have been set :

 1RMAN> Recover Database Nonlogged Block;
 2Starting recover at 2018-07-03 14:54:22
 3using target database control file instead of recovery catalog
 4allocated channel: ORA_DISK_1
 5channel ORA_DISK_1: SID=56 device type=DISK
 6starting recovery of nonlogged blocks
 7List of Datafiles
 8=================
 9File Status Nonlogged Blocks Blocks Examined Blocks Skipped
10---- ------ ---------------- --------------- --------------
11File Status Nonlogged Blocks Blocks Examined Blocks Skipped
12---- ------ ---------------- --------------- --------------
131    OK     0                0               102399        
143    OK     0                0               63999         
154    OK     0                0               8959          
167    OK     0                3403            2836          
17Details of nonlogged blocks can be queried from v$nonlogged_block view
18recovery of nonlogged blocks complete, elapsed time: 00:00:08
19Finished recover at 2018-07-03 14:54:32
     


When the recovery is complete ,V$NONLOGGED_BLOCK There's no more data in the view . Finally, don't forget to start the real-time log application process .

To sum up , stay Oracle 12.2 This feature can be tried in some scenarios such as data warehouse . In the past DBA Turn on FORCE_LOGGING Cause a lot of Redo Log and affect part of DML Statement execution efficiency . stay Oracle 12.2 You can try to use NOLOGGING Operation to save a lot of data insertion time , Then the standby database is restored in the idle time of the system . however , There are also drawbacks to this operation , Because the availability of standby database is greatly reduced .