全部博文（1144）
分类： Oracle
2011-06-24 01:00:06
总结如下：
注意append是一种hint;
一般我们可以这样来使用
insert /*+append+/ into mytable values(1,'alan');
数据库在归档模式下
当表模式为logging状态时，无论是append模式还是no append模式，redo都会生成。
当表模式为nologging状态时，只有append模式，不会生成redo.
数据库在非归档模式下
无论是在logging还是nologing的模式下，append的模式都不会生成redo,而no append模式下都会生成redo。
如果我想看一张表是否是logging状态,可以这样
select table_name,logging from dba_tables where table_name='tablename';
那么在Oracle内部还存在一个内部参数：_disable_logging 默认是false
通过更改为true可以让Oracle在修改表中的记录的时候完全不记录redo,这个参数要甚用。平时，我们只作为性能测试用。
force logging(强制日志)模式：
通过命令：
alter database force logging来使得Oracle无论什么操作都进行redo的写入。
通过select force_logging from v$database可以看到当前数据库强制日志模式的状态。
======================================================================
alter database force logging 含义
Oracle Dataguard
Chapter 3 - Implementing Standby Databases
Preparing To Create a Physical Standby Database
ALTER DATABASE ARCHIVELOG;
If the “automatic archival” is not enabled, it can be enabled using the following statement:
ALTER SYSTEM ARCHIVE LOG START;
Change the log_archive_start=true parameter in the initialization file so that it will remain enabled on next startup of the database.
Once the database is in archive log mode, the next step is to put it in FORCE LOGGING mode. This will ensure that all the transactions made on the primary database will be registered in the redo logs of the primary database and can be replicated on standby databases.
FORCE LOGGING Option
The FORCE LOGGING option is the safest method to ensure that all the changes made in the database will be captured and available for recovery in the redo logs. Force logging is the new feature added to the family of logging attributes.
Before the existence of FORCE LOGGING, Oracle provided logging and nologging options. These two options have higher precedence at the schema object level than the tablespace level; therefore, it was possible to override the logging settings at the tablespace level with nologging setting at schema object level.
就是说，设置FORCE LOGGING 之后。本来一些可以指定nologging减少redo log的操作 ，虽然不报错，但事实上redo log还是都纪录了。
等于是，没法nologging了
alter database force logging, data guard中为什么需要强制纪录日志，和一般纪录日志
最近在做Oracle9.2.0.4 Data Guard , 看到kamus 的physical and logical dataguard 的简明安装step , 第一步就是需要将primary database 置为 force logging 模式 ：alter database force logging .
force loggin 应该是比一般的logging 记录的多，具体有什么区别？
一般的dataguard 采用logical 还是physical data guard 好 ？
force logging并不比一般的logging记录的日志多,
数据库在force logging状态下,nologging选项将无
效,因为nologging将破坏dataguard的可恢复性.
force logging强制数据库在任何状态下必须记录
日志而已。
logical standby允许数据库在恢复的同时进行访问,
physical standby则在恢复时不允许进行访问
logical standby可以使机器的使用最大化
====================
请问alter database force logging后，怎么样改回去？是用哪个命令？
alter database no force logging ;
你可以查询 alter database 语法即可。
===========================================================
数据文件的unrecoverable
在Oracle的备份恢复过程中，需要注意数据文件的unrecoverable，不适当的操作很容易造成恢复后有大量的坏块。在视图v$datafile中，UNRECOVERABLE_CHANGE#和UNRECOVERABLE_TIME分别表示数据文件最后一个unrecoverable操作的change#和时间。unrecoverable通常就是指不记录日志的操作（nologging），这样当用一个旧的数据文件还原后，用日志进行恢复时，由于日志文件没有记录unrecoverable的操作时的日志，导致那些操作的数据块为逻辑坏块（实际上在日志文件中为这样的操作产生了一些重做日志项，在恢复时，根据这些重做日志项，直接将相应的数据块标记为坏块）。常见的以下几种情况：
1. 非归档模式下的create table as 操作和直接路径插入（如加了append hint的insert语句和直接路径装载）
2. 归档模式下的create table xxx nologging（即创建表时为表指定了nologging）和nologging表的直接路径插入。
在数据库（或表空间）为force logging时，任何操作都会记录日志。不会有unrecoverable操作。
下面先做个实验（数据库版本为9.2.0.1）来看看这两列：
数据库当前处于非归档模式;
SQL> select name,checkpoint_time,unrecoverable_time from v$datafile where file#=10;
NAME CHECKPOINT_TIME UNRECOVERABLE_TIME
---------------------------------------- ------------------- -------------------
D:\ORACLE\ORADATA\XJ\TEST01.DBF 2008-09-23 09:28:22
SQL> create table t tablespace test nologging as select * from dba_objects where rownum< =10;
表已创建。
SQL> select name,checkpoint_time,unrecoverable_time from v$datafile where file#=10;
NAME CHECKPOINT_TIME UNRECOVERABLE_TIME
---------------------------------------- ------------------- -------------------
D:\ORACLE\ORADATA\XJ\TEST01.DBF 2008-09-23 09:28:22
可以看到，unrecoverable_time为空。想一想就可以理解，unrecoverable操作都是将数据直接写入了数据文件，没有经过SGA的缓存，非归档模式下的物理备份都是一致的冷备份，不需要日志来进行恢复，因此对于非归档模式下并不存在unrecoverable操作。unrecoverable只是针对归档模式的。下面将数据库置为归档模式后，重复上述过程，进行验证：
SQL> create table t tablespace test nologging as select * from dba_objects where rownum< =10;
表已创建。
SQL> select name,checkpoint_time,unrecoverable_time,unrecoverable_change# from v$datafile where
file#=10;
NAME CHECKPOINT_TIME UNRECOVERABLE_TIME UNRECOVERABLE_CHANGE#
---------------------------------------- ------------------- ------------------- ---------------------
D:\ORACLE\ORADATA\XJ\TEST01.DBF 2008-09-23 09:45:03 2008-09-23 09:45:41 1298047
可以看到，v$datafile视图中unrecoverable_time和unrecoverable_change#已经有了值。
下面来看看unrecoverable_time是最后一次unrecoverable操作的开始时间还是结束时间？
创建一个具有延时功能的函数：
create or replace function f_cdate return date
as
begin
dbms_lock.sleep(10);
return sysdate;
end;
SQL> create table t (d date) nologging tablespace test;
表已创建。
SQL> begin
2 dbms_output.put_line(’start test:’||sysdate);
3 insert /*+ append */ into t select f_cdate from dba_objects where rownum< =10;
4 dbms_output.put_line('after insert:'||sysdate);
5 dbms_lock.sleep(60);
6 commit;
7 dbms_output.put_line('end test:'||sysdate);
8 end;
9 /
start test:2008-09-23 10:31:50
after insert:2008-09-23 10:33:33
end test:2008-09-23 10:34:34
PL/SQL 过程已成功完成。
SQL> select name,checkpoint_time,unrecoverable_time,unrecoverable_change# from v$datafile where
file#=10;
NAME CHECKPOINT_TIME UNRECOVERABLE_TIME UNRECOVERABLE_CHANGE#
---------------------------------------- ------------------- ------------------- ---------------------
D:\ORACLE\ORADATA\XJ\TEST01.DBF 2008-09-23 09:45:03 2008-09-23 10:33:33 1299032
SQL> begin
2 dbms_output.put_line(’start test:’||sysdate);
3 insert /*+ append */ into t select f_cdate from dba_objects where rownum< =10;
4 dbms_output.put_line('after insert:'||sysdate);
5 dbms_lock.sleep(60);
6 rollback;
7 dbms_output.put_line('end test:'||sysdate);
8 end;
9 /
start test:2008-09-23 10:37:59
after insert:2008-09-23 10:39:42
end test:2008-09-23 10:40:43
PL/SQL 过程已成功完成。
SQL> select name,checkpoint_time,unrecoverable_time,unrecoverable_change# from v$datafile where
file#=10;
NAME CHECKPOINT_TIME UNRECOVERABLE_TIME UNRECOVERABLE_CHANGE#
---------------------------------------- ------------------- ------------------- ---------------------
D:\ORACLE\ORADATA\XJ\TEST01.DBF 2008-09-23 09:45:03 2008-09-23 10:39:42 1299157
可以看到unrecoverable_time为unrecoverable操作完成的那个时间，不管事务是否提交。
对于数据库备份后的恢复，需要注意查询v$datafile视图中关于unrecoverable操作时间，如果unrecoverable操作时间在数据文件备份之后（更精确的比较是通过change#，比较文件的checkpoint_change#和unrecoverable_change#），则恢复会产生坏块。
建议重要的数据库，将数据库置为force logging（当然数据库应当是归档模式），避免无意的产生了unrecoverable操作。或者在做了unrecoverable操作之后立即进行数据文件的备份。
PS：关于不产生日志的操作，请参见metalink NOTE:269274.1 CHECK FOR LOGGING/NOLOGGING ON DB OBJECT(S)
================================================
Check for unrecoverable changes in datafiles
SQL query to check for unrecoverable changes in datafiles on the standby database:
SQL> select name, unrecoverable_change#, unrecoverable_time from v$datafile;
If unrecoverable_change# is not 0 that indicates that unrecoverable/nologging operations were used with object(s) in that datafile. In that case, if unrecoverable_time shows date after you created the standby database, then when you activate the standby database you will probably get the following error messages for one or more objects in that datafile:
ORA-01578: ORACLE data block corrupted (file # string, block # string)
ORA-01110: data file string: "string"
ORA-26040: Data block was loaded using the NOLOGGING option
Nologging operations should not be used with objects in that datafile.
Oracle introduced the "force logging" feature in 9.2 to avoid this problem.
In order to fix the issue follow the following steps:
To determine which datafile needs to be copied from the primary to the standby:
1.
Query the primary database:
SQL> SELECT NAME, UNRECOVERABLE_CHANGE# FROM V$DATAFILE;
NAME UNRECOVERABLE
----------------------------------------------------- -------------
/oracle/oradata/dbs/tbs01_1.dbf 5216
/oracle/oradata/dbs/tbs01_2.dbf 0
/oracle/oradata/dbs/tbs01_3.dbf 0
/oracle/oradata/dbs/tbs01_4.dbf 0
4 rows selected.
2.
Query the standby database:
SQL> SELECT NAME, UNRECOVERABLE_CHANGE# FROM V$DATAFILE;
NAME UNRECOVERABLE
----------------------------------------------------- -------------
/oracle/oradata/dbs/stdby/tbs_1.dbf 5186
/oracle/oradata/dbs/stdby/tbs_2.dbf 0
/oracle/oradata/dbs/stdby/tbs_3.dbf 0
/oracle/oradata/dbs/stdby/tbs_4.dbf 0
4 rows selected.
3.
Compare the query results of the primary and standby databases.
Compare the value of the UNRECOVERABLE_CHANGE# column in both query results.
If the value of the UNRECOVERABLE_CHANGE# column in the primary database is greater than the same column in the standby database,
then the datafile needs to be copied from the primary site to the standby site.
In this example, the value of the UNRECOVERABLE_CHANGE# in the primary database for the tbs01_1.dbf datafile is greater, so you need to copy the tbs01_1.dbf datafile to the standby site.
When copying the datafile, use the following method:
SQL> ALTER TABLESPACE tbs01 BEGIN BACKUP;
SQL> EXIT;
% scp tbs01_1.dbf
SQL> ALTER TABLESPACE tbs01 END BACKUP;
Shutdown the standby database and replace the existing datafile with the new datafile (make a backup copy first).
Restart the standby database and run Dbvisit.
========================================
"Please explain unrecoverable_time field in v$datafile."
You Asked
Tom,
I created table t1 with nologging option using "create table as subquery". Then queried
unrecoverable_change#, unrecoverable_time columns v$datafile view. I am not able to find
the last SCN and unrecoverable_time corresponding to my operation.
I also tried the same with "insert into t1 select * from scott.emp nologgig". Then I
quried from v$datafile to see the last unrecoverable_time. I am not seeing the time
corresponding to the time I did the operation.
I expect unrecoverable_time of v$datafile to show the time when I did the nologging
operation. Is my expectaion correct. If so, could you please explain what mistake I am
doing.
Log of my activities:
---- ---- ----------
> create table t1 as select * from scott.emp nologging;
Table created.
> select file#, unrecoverable_change#, to_char(unrecoverable_time,
'mm-dd-yyyy hh:mi:
ss') from v$datafile;
FILE# UNRECOVERABLE_CHANGE# TO_CHAR(UNRECOVERAB
--------- --------------------- -------------------
1 0
2 0
3 0
4 0
5 0
6 0 07-19-2000 12:04:14
7 0
8 0
8 rows selected.
> select to_char(sysdate, 'mm-dd-yyyy hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'MM
-------------------
10-14-2000 17:13:48
> commit;
Commit complete.
> insert into t1 select * from scott.emp nologging;
14 rows created.
> commit;
Commit complete.
> select file#, unrecoverable_change#, to_char(unrecoverable_time,
'mm-dd-yyyy hh:mi:
ss') from v$datafile;
FILE# UNRECOVERABLE_CHANGE# TO_CHAR(UNRECOVERAB
--------- --------------------- -------------------
1 0
2 0
3 0
4 0
5 0
6 0 07-19-2000 12:04:14
7 0
8 0
8 rows selected.
>
Regards
Ravi
and we said...
You've used the word "nologging" in the wrong place. In the above, it is being used as a
TABLE ALIAS. Your:
create table t1 as select * from scott.emp nologging;
is the same as:
create table t1 as select * from scott.emp A;
You want to:
create table t1 NOLOGGING as select * from scott.emp;
Here is an example that shows the create table as select and how to do an unrecoverable
INSERT (please read about the APPEND hint and make SURE you understand the ramifications
of using it before using it!)
> drop table t;
Table dropped.
> select file#,
2 to_char(unrecoverable_change#,'99999999999999999999'),
3 to_char(unrecoverable_time, 'mm-dd-yyyy hh:mi:ss')
4 from v$datafile
5 where file# = 7
6 /
FILE# TO_CHAR(UNRECOVERABLE TO_CHAR(UNRECOVERAB
---------- --------------------- -------------------
7 5966356510632 10-15-2000 09:53:22
>
> exec dbms_lock.sleep(2)
PL/SQL procedure successfully completed.
> create table t nologging as select * from scott.emp;
Table created.
>
> select file#,
2 to_char(unrecoverable_change#,'99999999999999999999'),
3 to_char(unrecoverable_time, 'mm-dd-yyyy hh:mi:ss')
4 from v$datafile
5 where file# = 7
6 /
FILE# TO_CHAR(UNRECOVERABLE TO_CHAR(UNRECOVERAB
---------- --------------------- -------------------
7 5966356510645 10-15-2000 09:53:48
>
> exec dbms_lock.sleep(2)
PL/SQL procedure successfully completed.
> insert /*+ APPEND */ into t select * from scott.emp;
14 rows created.
>
> select file#,
2 to_char(unrecoverable_change#,'99999999999999999999'),
3 to_char(unrecoverable_time, 'mm-dd-yyyy hh:mi:ss')
4 from v$datafile
5 where file# = 7
6 /
FILE# TO_CHAR(UNRECOVERABLE TO_CHAR(UNRECOVERAB
---------- --------------------- -------------------
7 5966356510655 10-15-2000 09:53:51
