oracle修改表的logging状态,force_logging 强制记录LOG

最近在工作中正好用到了lob对象，发现网上很多lob相关表创建语句里都有nologging的，自然都是不写redo

log的，这必然给备份恢复带来了问题。

顺便多看了看这方面的东西

这里整理出了官方文档的说明：

关于nologging:

Oracle? Database Backup and Recovery Advanced User's Guide

10g Release 2 (10.2)

Part Number B14191-02

You can create tables and indexes with the CREATE TABLE AS

SELECT statement. You can also specify that the database

create them with the NOLOGGING option. When you create a table or

index as NOLOGGING, the database does not generate

redo log records for the operation. Thus, you cannot recover

objects created with NOLOGGING, even if you are

running in ARCHIVELOG mode.

Be aware that when you perform media recovery, and some tables

or indexes are created normally whereas others are created

with the NOLOGGING option, the NOLOGGING objects are marked

logically corrupt by the RECOVER operation.

Any attempt to access the unrecoverable objects returns an

ORA-01578 error message. Drop the NOLOGGING objects and

re-create

them if needed.

关于Force logging

Specifying FORCE LOGGING Mode

Some data definition language statements (such as CREATE TABLE)

allow the NOLOGGING clause, which causes some database operations

not to generate redo records in the database redo log. The

NOLOGGING setting can speed up operations that can be easily

recovered outside of the database recovery mechanisms, but it can

negatively affect media recovery and standby databases.

Oracle Database lets you force the writing of redo records even

when NOLOGGING has been specified in DDL statements. The database

never generates redo records for temporary tablespaces and

temporary segments, so forced logging has no affect for

objects.

nologging,logging,force

logging分为对象级(DDL创建表或索引的时候使用了nologging或其他)，表空间级，数据库级。force

logging会比nologging“权利” 更大

force logging就是：忽略nologging

其他：

查询数据库中nologging的表(索引)

SQL> select table_name,tablespace_name,logging from

user_tables where logging='NO';

TABLE_NAME TABLESPACE_NAME LOG

------------------------------ -------------------- ---

PAGERESULTS USERS NO

EXTENDEDPAGERESULTS USERS NO

检查数据库表空间的logging和force logging设定

SQL> select tablespace_name,logging,force_logging

from dba_tablespaces;

TABLESPACE_NAME LOGGING FOR

-------------------- --------- ---

SYSTEM LOGGING NO

UNDOTBS1 LOGGING NO

SYSAUX LOGGING NO

TEMP NOLOGGING NO

BLOBS NOLOGGING NO

INDX LOGGING NO

USERS LOGGING NO

WCAUDIT LOGGING NO

检查整个数据库的force_logging设定

SQL> select force_logging from v$database;

FOR

---

NO

修改表空间的(修改database,table,index也类似)

alter tablespace blobs force logging;

alter tablespace users force logging;

alter tablespace blobs logging;

SQL> select tablespace_name,LOGGING,FORCE_LOGGING

FROM DBA_TABLESPACES;

TABLESPACE_NAME LOGGING FOR

------------------------------ --------- ---

SYSTEM LOGGING NO

UNDOTBS1 LOGGING NO

SYSAUX LOGGING NO

TEMP NOLOGGING NO

BLOBS LOGGING YES

INDX LOGGING NO

USERS LOGGING YES

WCAUDIT LOGGING NO

再附送几个查询lob和logging状态的sql:

计算所有LOB的大小：

SELECT SUM(BYTES)/1024/1024/1024 FROM

user_segments

where segment_type='LOBSEGMENT';

计算所有nologging的大小

SELECT SUM(s.BYTES)/1024/1024/1024

FROM user_segments s,user_lobs l

where s.segment_type='LOBSEGMENT'

and l.LOGGING='NO' and l.SEGMENT_NAME=s.SEGMENT_NAME;

列出所有nologging/logging 的lobs和对应的表空间及其大小：

select

s.segment_name,s.bytes,s.TABLESPACE_NAME

FROM user_segments s,user_lobs l

where s.segment_type='LOBSEGMENT'

and l.LOGGING='NO' and l.SEGMENT_NAME=s.SEGMENT_NAME;

select s.segment_name,s.bytes,s.TABLESPACE_NAME

FROM user_segments s,user_lobs l

where s.segment_type='LOBSEGMENT'

and l.LOGGING='YES' and l.SEGMENT_NAME=s.SEGMENT_NAME;

计算数量

select count(*) from user_lobs where

logging='NO';

select count(*) from user_lobs where

logging='YES';