Oracle System Talks with Veridata and LinkPlus

Advertisements

Which One? LOGGING or NOLOGGING?

LOGGING is a keyword that used on creating the index, table or tablespace. If we use LOGGING when creating the object then DML operations on the objects are logged in redo log file. If we use NOLOGGING when creating the object, in some cases DML operations on the objects are not logged in redo log file. There are a lot of questions asked on the forums about LOGGING/NOLOGGING. Some of the questions as follows: What is the advantage of NOLOGGING? When should we use NOLOGGING ? If NOLOGGING is used on the tablespace level, can I use LOGGING on the tables which are in the NOLOGGING tablespace?

OK. Let us examine one by one.

let’s create a NOLOGGING tablespace.

CREATE TABLESPACE NOLOGGING_TS DATAFILE

‘/data_TALIPDB/nologging_ts.dbf’ SIZE 1024M AUTOEXTEND OFF

NOLOGGING

ONLINE

PERMANENT

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M

BLOCKSIZE 8K

SEGMENT SPACE MANAGEMENT AUTO

FLASHBACK ON;

Now, I will create a table in this tablespace. And I will not specify LOGGING or NOLOGGING when creating the table.

CREATE TABLE talip_test(

id NUMBER

)

TABLESPACE nologging_ts;

Check the table LOGGING status with following query. You will see LOGGING=NO

SELECT table_name, logging

FROM dba_tables

WHERE tablespace_name = ‘NOLOGGING_TS’

TABLE_NAME                   LOGGING

—————————— ——-

TALIP_TEST                         NO

Now, I will create table in NOLOGGING_TS tablespace with LOGGING clause.

CREATE TABLE talip_test2 (

id NUMBER

)

TABLESPACE nologging_ts

LOGGING;

Rerun above query to see table LOGGING status. You will see LOGGING=YES

SELECT tablespace_name, logging

FROM dba_tables

WHERE table_name = ‘TALIP_TEST2’

TABLE_NAME                  LOGGING

—————————— ——-

TALIP_TEST                         NO

TALIP_TEST2                       YES

In that case, If you specify NOLOGGING on the tablespace level, then default logging status will be  NOLOGGING that the objects to be created within the tablespace.

If you create a table as NOLOGGING then you can change its logging status to LOGGING. Or the opposite might happen.

ALTER TABLE talip_test LOGGING;

ALTER TABLE talip_test2 NOLOGGING;

This situation applies in tablespace. If you create a tablespace as NOLOGGING then you can change its logging status to LOGGING.

ALTER TABLESPACE nologging_ts LOGGING;

After changing a tablespace logging status to LOGGING, NOLOGGING created objects will remain as NOLOGGING. If you create a new table in this tablespace then created table logging status will be LOGGING. In some cases, logging can continue on NOLOGGING. So what are these conditions?

Table mode Insert mode Archive Log Mode Redo generation
LOGGING APPEND ARCHIVE LOG REDO generated
NOLOGGING APPEND ARCHIVE LOG no REDO
LOGGING No APPEND ARCHIVE LOG REDO generated
NOLOGGING No APPEND ARCHIVE LOG REDO generated
LOGGING APPEND NO ARCHIVE LOG no REDO
NOLOGGING APPEND NO ARCHIVE LOG no REDO
LOGGING No APPEND NO ARCHIVE LOG REDO generated
NOLOGGING No APPEND NO ARCHIVE LOG REDO generated

“Direct Path Load” operations on the NOLOGGING table are not generated redo. If “force logging” is enabled then always redo is generated. Even if table is LOGGING mode, the “Direct Path Load” operations doesn’t generate redo log  in NO ARCHIVE LOG. Even if the database in ARCHIVE LOG mode, redo is not generated on NOLOGGING table.

Direct Load Insert is a faster way of running an INSERT statement. It is particularly useful for inserting large numbers of rows. Direct Load Insert differs from Conventional Insert in that it bypasses the buffer cache.

Creating a table as NOLOGGING takes less time than to create LOGGING. Let a simple test.

SQL> set timing on

SQL> create table talip_logging logging as select * from dba_tables;

Table created.

Elapsed: 00:00:01.20

Now let’s create it with nologging.

SQL> create table talip_nologging nologging as select * from dba_tables;

Table created.

Elapsed: 00:00:00.60

And there is an important point. If you delete a row from NOLOGGING table then you can rollback. Baceause of  it is not depended on LOGGING/NOLOGGING. It is depended on UNDO segments. Let’s do a simple test.

Create a NOLOGGING table.

CREATE TABLE talip_test3 (

isim varchar2(10)

)

NOLOGGING;

insert a row;

insert into talip_test3 values(‘talip’);

commit;

delete a row;

delete from talip_test3;

After rollback, you can see deleted row. So, the rollback processis not associated with  LOGGING / NOLOGGING .

rollback;

select * from talip_test3;

Talip Hakan Öztürk

RMAN Block Change Tracking and “_bct_public_dba_buffer_size” Hidden Parameter

One important feature  “block change tracking” that comes with 10g, keeps log of the blocks changed since the last backup. During the next backup it uses the log file “block change tracking” to detect the changed blocks  instead of scanning  all data files.  Changed blocks are determined and written to the log file by the process CTWR. After enabling “block change tracking” RMAN incremantal backups will run effectively.

You can enable or disable block change tracking as below:

SQL>alter database enable block change tracking using file '/rman_bkups/change.log';

SQL>alter database disable block change tracking;

During RMAN incrremental backup you can see  ‘block change tracking buffer space’ wait events. This wait event appears especially in large databases. It affects between 10.2 and 11.2 databases.

According to metalink documentation [ID 1311518.1] you must do following operations to solve this problem.

1- Do not put “Block Change Tracking” log file to disk which has higher  I/O ratio.

log dosyası çok fazla I/O gören verilerin bulunduğu diskde olmamalıdır.

2- The value of Large_pool_size must be examined. If it is lower then must be increased .

3- Set hidden parameter “_bct_public_dba_buffer_size”.

Ypu can query the memory area which is allocated for change tracking.

select dba_buffer_count_public*dba_entry_count_public*dba_entry_size from x$krcstat;

Multiply by 2 the obtained value and you will calculate value of “_bct_public_dba_buffer_size” parameter.

RMAN-03002 and RMAN-06091 Errors when Deleting Obsolete Backups

When I attempting to delete obsolete backups from RMAN using the following command, the following error was occuring.

RMAN> delete obsolete;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of delete command at 02/04/2018 20:28:22
RMAN-06091: no channel allocated for maintenance (of an appropriate type)

So Tape channel had not being allocated when attempt to delete obsolete backup on tape.

Please run the following commands to delete obsolete backup sets on both disk and tape:

RMAN> allocate channel for maintenance type disk;
RMAN> allocate channel for maintenance device type ‘sbt_tape’ PARMS ‘…’;
RMAN> delete obsolete;

If you want to delete only obsolete backup sets on disk, you must use the following command.

RMAN> allocate channel for maintenance type disk;
RMAN> delete obsolete device type disk;