It’s true that we shouldn’t consider DR solution using the manual Logging Shipping process in Standard Edition but we also know that Oracle Enterprise Edition is very expensive 🙂
In this article we have words like unrecoverable, nologging, logging and force logging so if you’re not familiar with this words maybe you should read The Gains and Pains of Nologging Operations (Doc ID 290161.1).
The first thing to keep in mind when you’re building a Standby Database is to know if the database has nologging operations that could affect your strategy in case of disaster. Of course if you want to guarantee that Oracle log all operations you can just put the database in force logging mode and done but sometimes the life is not so easy as it seems especially when you have a Standard Edition (no redo apply etc.) and must do the whole job manually count only with your good friends (shell scripts and crontab).
So if the force nologging is not an option there are few steps that you might consider to check before build a Standby in Oracle Standard Edition (11.2.0.4)
Step #1 – Unrecoverable changes.
Check for unrecoverable changes in v$datafile. If there is any operations using nologging it should appear in this view.
set lines 400
col tablespace_name for a30
col file_name for a54
select df.tablespace_name,
df.file_name,
v.file#,
v.unrecoverable_time,
v.unrecoverable_change#
from dba_data_files df,
v$datafile v
where v.file# = df.file_id
and v.unrecoverable_change# > 0
order by unrecoverable_time,tablespace_name, file_name;
|
Step #2 – Finding nologging operations.
Tip: if there is no application running, nothing running and even so Oracle returns unrecoverable changes so what could be?
Check for Oracle internal jobs (Auto Tasks) like ORA$AT_SA_SPC%. In version 11.2.0.4 Oracle has changed internally the way to gathering some stuffs and there is an internal command that execute nologging operations and create table with this standard (extract from a trace file).
In this case was for a LOB but it might exist command like this for non-lob data as well.
Command:
----- Current SQL Statement for this session (sql_id=9mksnadjuygkc) -----
create table "SCHEMA_NAME".CMP3$91777 tablespace "SCHEMA_TABLESPACE_NAME"
nologging lob (XXX) store as (tablespace "SCHEMA_TABLESPACE_NAME"
enable storage in row nocache nologging)
as select /*+ DYNAMIC_SAMPLING(0) FULL("SCHEMA_NAME"."QM_LOB_NAME") */ *
from "SCHEMA_NAME"."XXX" sample block( 44.64) mytab
----- PL/SQL Stack -----
|
There is a bug # “24374872 excessive sampling by compression advisor causes high redo and table space usage” and there is an also generic patch p24374872_112040_Generic.zip for this. Just to clarify that this patch supposes just to fix the “excessive sampling by compression advisor causes high redo and table space usage” and probably the Oracle internal nologging operations might continue to happen.
* This bug has been logged specific for Oracle 11.2.0.4.
Step #3 – Workaround.
If you are not able to put your database in force logging mode for any reason maybe the workaround for this is to disable those Oracle Auto Tasks to avoid Oracle doing nologging operations.
Check the Oracle Auto tasks job you can execute the query below:
*** Before doing this check the impact and if you don’t know that could cause please log a Service Request in Oracle support) ***
select client_name,status from dba_autotask_client; |
You can disable these Auto Tasks with the command:
BEGIN dbms_auto_task_admin.disable(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL); END; / BEGIN dbms_auto_task_admin.disable(client_name => 'auto space advisor',operation => NULL,window_name => NULL); END; / |
Doing this Oracle will stop to execute the jobs that background internally executing nologging operations. After this the unrecoverable changes in v$datafile should stop to appear.
Step #4 – Take a full or incremental backups after nologging operations.
The example below is just to simulate and demonstrate how to deal with the nologging operations in the database:
Scenario: after full or incremental backup nologging operations has been done into the database:
- Execute RMAN report command to check for unrecoverable into the database;
RMAN> report unrecoverable database; 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 ---- ----------------------- ----------------------------------- RMAN> |
- Generating nologging data:
create table test_nologging tablespace users nologging as select * from dba_source; 14:08:30 > create table test_nologging tablespace users nologging as select * from dba_source; Table created. |
- Execute the RMAN report command again to check for unrecoverable into the database. Here RMAN will notify that just after the full or incremental backup some nologging operations has been made in the database;
RMAN> report unrecoverable database; Report of files that need backup due to unrecoverable operations File Type of Backup Required Name ---- ----------------------- ----------------------------------- 4 full or incremental /u01/oradata/db01/users01.dbf RMAN> |
- Take a full or incremental backup after nologging operation (in my case I just did an incremental for tablespace users);
RMAN> backup incremental level 1 cumulative tag 'lvl_1_tablespace_users' tablespace users; Starting backup at 30-JUN-2017 09:11:53 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=2645 device type=DISK channel ORA_DISK_1: starting incremental level 1 datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00004 name=/u01/oradata/db01/users01.dbf channel ORA_DISK_1: starting piece 1 at 30-JUN-2017 09:11:53 channel ORA_DISK_1: finished piece 1 at 30-JUN-2017 09:11:54 piece handle=/u00/app/oracle/product/11.2.0.4/db_1/dbs/5ss832qp_1_1 tag=LVL_1_TABLESPACE_USERS comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 30-JUN-2017 09:11:54 Starting Control File and SPFILE Autobackup at 30-JUN-2017 09:11:54 piece handle=/u07/app/oracle/rmanbackups/cf_c-3816558725-20170630-01_20170630 comment=NONE Finished Control File and SPFILE Autobackup at 30-JUN-2017 09:11:55 |
- Execute the RMAN report command again to check for unrecoverable into the database;
RMAN> report unrecoverable database; Report of files that need backup due to unrecoverable operations File Type of Backup Required Name ---- ----------------------- ----------------------------------- RMAN> |
Step #5 – Documentation.
I’ve select few interesting documents regarding this matter and might help to understand better the way that Oracle working with nologging operations.
- Doc ID 33405.1: For tables please see “Extracting Data from a Corrupt Table using dbms_repair or Event 10231;
- Doc ID 269274.1: Check For Logging / Nologging On DB Object(s);
- Doc ID 794505.1: ORA-1578 / ORA-26040 Corrupt blocks by NOLOGGING – Error explanation and solution;
- Doc ID 1459778.1: Use RMAN to format corrupt data block which is not part of any object;
- Doc ID 336133.1: How to Format Corrupted Block Not Part of Any Segment.
Leonardo Bissoli.
