Time for action – fixing NOLOGGING changes in the standby database with incremental database backups

  1. Determine the SCN that we'll use in the RMAN incremental database backup by querying the minimum FIRST_NONLOGGED_SCN column of the V$DATAFILE view in the standby database:
    SQL> SELECT MIN(FIRST_NONLOGGED_SCN) FROM V$DATAFILE WHERE FIRST_NONLOGGED_SCN>0;

MIN(FIRST_NONLOGGED_SCN)
------------------------
                20606544
  2. Stop Redo Apply on the standby database:
    SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
  3. Now we'll take an incremental backup of the database using the FROM SCN keyword. The SCN value will be the output of the execution of the query in the first step. Connect to the primary database as the RMAN target and execute the following RMAN BACKUP statement:
    RMAN> BACKUP INCREMENTAL FROM SCN 20606344 DATABASE FORMAT '/data/DB_Inc_%U' TAG 'FOR STANDBY';
  4. Copy the backup files from the primary site to the standby site with FTP or SCP:
    scp /data/DB_Inc_* standbyhost:/data/
  5. Connect to the physical standby database as the RMAN target and catalog the copied backup files to the control file with the RMAN CATALOG command:
    RMAN> CATALOG START WITH '/data/DB_Inc_';
  6. Recover the standby database by connecting it as the RMAN target. RMAN will use the incremental backup automatically because those files were registered to the control file previously:
    RMAN> RECOVER DATABASE NOREDO;
  7. Run the query in the first step again to ensure that there're no more datafiles with NOLOGGING changes:
    SQL> SELECT FILE#, FIRST_NONLOGGED_SCN FROM V$DATAFILE WHERE FIRST_NONLOGGED_SCN > 0;
  8. Start Redo Apply on the standby database:
    SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

    Tip

    If the state of a tablespace that includes the affected datafiles is READ ONLY, those files will not be backed up with the RMAN BACKUP command. We need to put these tablespaces in the read-write mode before the backup operation. Change the state of a tablespace with the following statements:

    SQL> ALTER TABLESPACE <TABLESPACE_NAME> READ WRITE;
SQL> ALTER TABLESPACE <TABLESPACE_NAME> READ ONLY;
  9. Put the primary database in the FORCE LOGGING mode:
    SQL> ALTER DATABASE FORCE LOGGING;

What just happened?

We've fixed the adverse affect of executing the NOLOGGING operation in the primary database in a Data Guard configuration. If this problem is not fixed in the standby database, we'll face the ORA-26040 error when we attempt to open the standby database as read-only or read-write.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.137.178.9