Oracle Life: March 29, 2005 Archives

March 29, 2005

使用Oracle10g提供的flashback drop 新特性

作者:eygle

出处:http://blog.eygle.com

为了加快用户错误操作的恢复，Oracle10g提供了flashback drop的功能。
而在以前的版本中，除了不完全恢复，通常没有一个好的解决办法。
Oracle 10g的flashback drop功能，允许你从当前数据库中恢复一个被drop了的对象，在执行drop操作时，现在Oracle不是真正删除它，而是将该对象自动将放入回收站。对于一个对象的删除，其实仅仅就是简单的重令名操作。
所谓的回收站，是一个虚拟的容器，用于存放所有被删除的对象。在回收站中，被删除的对象将占用创建时的同样的空间，你甚至还可以对已经删除的表查询，也可以利用flashback功能来恢复它，这个就是flashback drop功能。
回收站内的相关信息可以从recyclebin/user_recyclebin/dba_recyclebin等视图中获取，或者通过SQL*Plus的show recyclebin 命令查看。



SYS AS SYSDBA on 29-MAR-05 >connect eygle/eygle
Connected.

EYGLE  on 29-MAR-05 >create table t1 as select * from dba_users;

Table created.

EYGLE  on 29-MAR-05 >drop table t1;

Table dropped.

EYGLE  on 29-MAR-05 >show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
T1               BIN$83T2+h92CJvgNAgAIMR+0Q==$0 TABLE        2005-03-29:21:52:00
EYGLE  on 29-MAR-05 > FLASHBACK TABLE t1 TO BEFORE DROP;

Flashback complete.
EYGLE  on 29-MAR-05 >desc t1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USERNAME                                  NOT NULL VARCHAR2(30)
 USER_ID                                   NOT NULL NUMBER
 PASSWORD                                           VARCHAR2(30)
 ACCOUNT_STATUS                            NOT NULL VARCHAR2(32)
 LOCK_DATE                                          DATE
 EXPIRY_DATE                                        DATE
 DEFAULT_TABLESPACE                        NOT NULL VARCHAR2(30)
 TEMPORARY_TABLESPACE                      NOT NULL VARCHAR2(30)
 CREATED                                   NOT NULL DATE
 PROFILE                                   NOT NULL VARCHAR2(30)
 INITIAL_RSRC_CONSUMER_GROUP                        VARCHAR2(30)
 EXTERNAL_NAME                                      VARCHAR2(4000)

EYGLE  on 29-MAR-05 >show recyclebin;

如果想要彻底清除这些对象，可以使用Purge命令，如:



EYGLE  on 29-MAR-05 >show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
T                BIN$29ycNBfhan/gNAgAIMR+0Q==$0 TABLE        2004-06-02:15:26:35
EYGLE  on 29-MAR-05 >purge table t;

Table purged.

EYGLE  on 29-MAR-05 >show recyclebin;

使用 purge recyclebin可以清除回收站中的所有对象。
类似的我们可以通过purge user_recyclebin或者是purge dba_recyclebin来清除不同的回收站对象。
通过PURGE TABLESPACE TSNAME，PURGE TABLESPACE TSNAME USER USERNAME命令来选择清除回收站。
如果需要彻底删除一个表，不想放到回收站中，可以在drop语句中增加purge选项，如



drop table tablename purge

需要注意的是sysdba的Drop操作不会被记录，Oracle也从不推荐用户使用sysdba身份创建用户对象。

Posted by eygle at 9:56 PM | Comments (3)

使用Oracle10g Flashback database功能恢复用户错误

作者:eygle

出处:http://blog.eygle.com

Flashback Database是Oracle10g的新增功能，在启动Flashback Database之后，它定期将已发生变化的块写入闪回日志的日志文件中。这些日志不是由传统的Log Writer (LGWR) 过程写入，而是由一种称作Recovery Writer (RVWR)的新过程写入。这是Oracle10g的新增进程。



$ ps -ef|grep rvwr|grep -v grep
  oracle 27231     1  0 17:10:30 ?        0:00 ora_rvwr_eygle

与常规的重做日志（redo logs）不同，回闪日志既不需要由DBA创建，也不需要由他们维护；它们由Oracle Managed Files（OMF）自动在闪回恢复区域所指定的目录中创建。这些文件不会归档，所以，如果在该目录发生介质故障后就不可能再进行恢复。
1.启用闪回数据库特性



SYS AS SYSDBA on 2005-03-29 16:42:19 >startup mount;
ORACLE instance started.

Total System Global Area  314572800 bytes
Fixed Size                  1301704 bytes
Variable Size             261890872 bytes
Database Buffers           50331648 bytes
Redo Buffers                1048576 bytes
Database mounted.

SYS AS SYSDBA on 29-MAR-05 >alter database archivelog;

Database altered.

SYS AS SYSDBA on 29-MAR-05 >alter database flashback on;

Database altered.

SYS AS SYSDBA on 29-MAR-05 >alter database open;

Database altered.

SYS AS SYSDBA on 29-MAR-05 >alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';


Session altered.


SYS AS SYSDBA on 2005-03-29 17:01:42 >SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME
  2  FROM V$FLASHBACK_DATABASE_LOG;

OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TI
-------------------- -------------------
            10642627 2005-03-29 17:01:02

2.闪回日志
Oracle会在闪回区记录日志.



$ pwd
/data5/flash_recovery_area/EYGLE/flashback
$ ls -l
total 32064
-rw-r-----   1 oracle   dba      8200192 Mar 29 16:49 o1_mf_14l5bclp_.flb
-rw-r-----   1 oracle   dba      8200192 Mar 29 17:31 o1_mf_14l6w5h4_.flb

3.模拟用户错误



SYS AS SYSDBA on 2005-03-29 17:01:44 >archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     0
Next log sequence to archive   1
Current log sequence           1
SYS AS SYSDBA on 2005-03-29 17:01:55 >select sysdate from dual;

SYSDATE
-------------------
2005-03-29 17:02:09

SYS AS SYSDBA on 2005-03-29 17:02:16 >drop table t3;

Table dropped.

SYS AS SYSDBA on 2005-03-29 17:02:22 >select sysdate from dual;

SYSDATE
-------------------
2005-03-29 17:02:28

SYS AS SYSDBA on 2005-03-29 17:02:28 >create table t1 as select * from dba_users;

Table created.


SYS AS SYSDBA on 2005-03-29 17:02:42 >select sysdate from dual;

SYSDATE
-------------------
2005-03-29 17:02:48

SYS AS SYSDBA on 2005-03-29 17:02:48 >create table t2 as select * from dba_tablespaces;

Table created.

SYS AS SYSDBA on 2005-03-29 17:03:01 >select sysdate from dual;

SYSDATE
-------------------
2005-03-29 17:03:06

SYS AS SYSDBA on 2005-03-29 17:03:06 >select sysdate from dual;

SYSDATE
-------------------
2005-03-29 17:03:17

4.进行闪回操作



SYS AS SYSDBA on 2005-03-29 17:03:43 >shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS AS SYSDBA on 2005-03-29 17:04:04 >startup mount;
ORACLE instance started.

Total System Global Area  314572800 bytes
Fixed Size                  1301704 bytes
Variable Size             261890872 bytes
Database Buffers           50331648 bytes
Redo Buffers                1048576 bytes
Database mounted.

SYS AS SYSDBA on 29-MAR-05 >alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SYS AS SYSDBA on 2005-03-29 17:05:31 >select * from V$FLASHBACK_DATABASE_LOG;

OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TI RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- ------------------- ---------------- -------------- ------------------------
            10642627 2005-03-29 17:01:02             1440        8192000                        0


SYS AS SYSDBA on 2005-03-29 17:06:38 >flashback database to timestamp
  2  to_timestamp ('2005-03-29 17:02:28','yyyy-mm-dd hh24:mi:ss');

Flashback complete.

5.只读打开，验证数据



SYS AS SYSDBA on 2005-03-29 17:06:58 >alter database open read only;

Database altered.

SYS AS SYSDBA on 2005-03-29 17:07:07 >desc t3    
ERROR:
ORA-04043: object t3 does not exist


SYS AS SYSDBA on 2005-03-29 17:07:39 >desc t1
ERROR:
ORA-04043: object t1 does not exist

6.继续修正恢复



SYS AS SYSDBA on 2005-03-29 17:07:42 >alter database close;

Database altered.

SYS AS SYSDBA on 2005-03-29 17:07:49 >flashback database to timestamp
  2  to_timestamp ('2005-03-29 17:02:48','yyyy-mm-dd hh24:mi:ss');

Flashback complete.

SYS AS SYSDBA on 2005-03-29 17:08:17 >alter database open;
alter database open
*
ERROR at line 1:
ORA-16196: database has been previously opened and closed


SYS AS SYSDBA on 2005-03-29 17:08:24 >alter database dismount;

Database altered.

SYS AS SYSDBA on 2005-03-29 17:08:35 >shutdown
ORA-01507: database not mounted


ORACLE instance shut down.
SYS AS SYSDBA on 2005-03-29 17:08:48 >startup mount;
ORACLE instance started.

Total System Global Area  314572800 bytes
Fixed Size                  1301704 bytes
Variable Size             261890872 bytes
Database Buffers           50331648 bytes
Redo Buffers                1048576 bytes
Database mounted.
SYS AS SYSDBA on 29-MAR-05 >alter database open read only;

Database altered.

SYS AS SYSDBA on 29-MAR-05 >select count(*) from t1;

  COUNT(*)
----------
        12

7.resetlogs打开数据库



SYS AS SYSDBA on 29-MAR-05 >shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS AS SYSDBA on 29-MAR-05 >startup mount
ORACLE instance started.

Total System Global Area  314572800 bytes
Fixed Size                  1301704 bytes
Variable Size             261890872 bytes
Database Buffers           50331648 bytes
Redo Buffers                1048576 bytes
Database mounted.

SYS AS SYSDBA on 29-MAR-05 >alter database open resetlogs;

Database altered.

SYS AS SYSDBA on 29-MAR-05 >

注意，一旦resetlogs之后，将不能再flashback至resetlogs之前的时间点。

Posted by eygle at 5:35 PM | Comments (8)

如何启用Oracle10g闪回数据库特性

作者:eygle

出处:http://blog.eygle.com

1.确认当前模式



SYS AS SYSDBA on 29-MAR-05 >select flashback_on from v$database;

FLA
---
NO

2.检查/修改恢复区设置



SYS AS SYSDBA on 29-MAR-05 >show parameter db_recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /data5/flash_recovery_area
db_recovery_file_dest_size           big integer 10G

3.检查/修改闪回时间设置



SYS AS SYSDBA on 29-MAR-05 >show parameter db_flashback_retention_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     60
SYS AS SYSDBA on 29-MAR-05 >alter system set db_flashback_retention_target=1440;

System altered.

4.重新启动数据库到Mount状态
启动flashback database选项。



SYS AS SYSDBA on 29-MAR-05 >shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS AS SYSDBA on 29-MAR-05 >startup mount;
ORACLE instance started.

Total System Global Area  314572800 bytes
Fixed Size                  1301704 bytes
Variable Size             261890872 bytes
Database Buffers           50331648 bytes
Redo Buffers                1048576 bytes
Database mounted.
SYS AS SYSDBA on 29-MAR-05 >alter database flashback on;

Database altered.

SYS AS SYSDBA on 29-MAR-05 >alter database open;

Database altered.

Posted by eygle at 5:07 PM | Comments (0)

启用Block Change Tracking-10g新特性

作者:eygle

出处:http://blog.eygle.com

Block chage tracking是Oracle10g的一个新特性，Block change tracking进程记录自从上一次备份以来数据块的变化，并把这些信息记录在跟踪文件中。RMAN使用这个文件判断增量备份中需要备份的变更数据。这极大的促进了备份性能，RMAN可以不再扫描整个文件以查找变更数据。

为此Oracle引入了一个新的后台进程，CTWR，其全称为Change Tracking Writer，用于记录变化的块并将变化写入相应的日志文件中。
启用跟踪可以使用如下命令:



alter database enable block change tracking using
file '/data5/flash_recovery_area/EYGLE/eygle_block_track.log';

判断当前设置可以查询:



select filename,status,bytes from v$block_change_tracking;

关闭跟踪:



alter database disable block change tracking;

以下步骤供参考:



SYS AS SYSDBA on 29-MAR-05 >alter database enable block change tracking using
  2  file '/data5/flash_recovery_area/EYGLE/eygle_block_track.log';

Database altered.

SYS AS SYSDBA on 29-MAR-05 >select filename,status,bytes from v$block_change_tracking;

FILENAME
--------------------------------------------------------------------------------
STATUS          BYTES
---------- ----------
/data5/flash_recovery_area/EYGLE/eygle_block_track.log
ENABLED      11599872


SYS AS SYSDBA on 29-MAR-05 >alter database disable block change tracking;

Database altered.

SYS AS SYSDBA on 29-MAR-05 >select filename,status,bytes from v$block_change_tracking;

FILENAME
--------------------------------------------------------------------------------
STATUS          BYTES
---------- ----------

DISABLED

Posted by eygle at 11:57 AM | Comments (1)

Oracle10g备份集压缩新特性(Backupset Compression)

作者:eygle

出处:http://blog.eygle.com

Oracle10g允许你和BACKUP命令伴随使用AS COMPRESSED BACKUPSET字句，对备份集进行压缩。
这个压缩备份在恢复时无需解压缩，这一特性在以下条件下可能大为有益:
1.你的备份存储空间有限
2.通过网络备份而带宽有限
3.直接备份至磁带、CD，DVD介质而压缩不可用

你可以选择在备份命令里显示指定压缩选项:
BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;
--仅对1,5文件压缩备份
BACKUP AS COMPRESSED BACKUPSET DATAFILE 1,5;

也可以配置RMAN指定压缩选项:
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO COMPRESSED BACKUPSET;

但是压缩备份会消耗更多的资源以及时间。
我们看一下以下测试:
1.压缩备份



RMAN> backup full filesperset 11 database ;

Starting backup at 29-MAR-05
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=161 devtype=DISK
channel ORA_DISK_1: starting compressed full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00009 name=/data1/oradata/systemfile/bigtbs.dbf
input datafile fno=00001 name=/opt/oracle/oradata/eygle/system01.dbf
input datafile fno=00006 name=/opt/oracle/oradata/eygle/EYGLE/datafile/o1_mf_test_03xv34ny_.dbf
input datafile fno=00002 name=/opt/oracle/oradata/eygle/undotbs01.dbf
input datafile fno=00003 name=/opt/oracle/oradata/eygle/sysaux01.dbf
input datafile fno=00004 name=/opt/oracle/oradata/eygle/users01.dbf
input datafile fno=00007 name=/opt/oracle/oradata/eygle/EYGLE/datafile/o1_mf_itpub_03xv5g66_.dbf
input datafile fno=00010 name=/opt/oracle/oradata/eygle/dfmbrc.dbf
input datafile fno=00008 name=/opt/oracle/oradata/eygle/trans01.dbf
input datafile fno=00005 name=/data1/oradata/systemfile/eygle01.dbf
channel ORA_DISK_1: starting piece 1 at 29-MAR-05
channel ORA_DISK_1: finished piece 1 at 29-MAR-05
piece handle=/data5/flash_recovery_area/EYGLE/backupset/
  2005_03_29/o1_mf_nnndf_TAG20050329T062814_14k1813y_.bkp comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:08:35
channel ORA_DISK_1: starting compressed full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00011 name=/opt/oracle/oradata/eygle/t2k01.dbf
channel ORA_DISK_1: starting piece 1 at 29-MAR-05
channel ORA_DISK_1: finished piece 1 at 29-MAR-05
piece handle=/data5/flash_recovery_area/EYGLE/backupset/2005_03_29/
  o1_mf_nnndf_TAG20050329T062814_14k1r4fh_.bkp comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 29-MAR-05

Starting Control File and SPFILE Autobackup at 29-MAR-05
piece handle=/data5/flash_recovery_area/EYGLE/autobackup/
  2005_03_29/o1_mf_s_554193419_14k1rdyr_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 29-MAR-05

注意压缩备耗时:00:08:35 + 00:00:07 = 8:42 = 522s

2.非压缩备份



RMAN> configure device type disk backup type to compressed backupset;

old RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;
new RMAN configuration parameters are successfully stored
released channel: ORA_DISK_1



RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO backupset;

old RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET PARALLELISM 1;
new RMAN configuration parameters are successfully stored

RMAN> backup full filesperset 11 database ;

Starting backup at 29-MAR-05
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=161 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00009 name=/data1/oradata/systemfile/bigtbs.dbf
input datafile fno=00001 name=/opt/oracle/oradata/eygle/system01.dbf
input datafile fno=00006 name=/opt/oracle/oradata/eygle/EYGLE/datafile/o1_mf_test_03xv34ny_.dbf
input datafile fno=00002 name=/opt/oracle/oradata/eygle/undotbs01.dbf
input datafile fno=00003 name=/opt/oracle/oradata/eygle/sysaux01.dbf
input datafile fno=00004 name=/opt/oracle/oradata/eygle/users01.dbf
input datafile fno=00007 name=/opt/oracle/oradata/eygle/EYGLE/datafile/o1_mf_itpub_03xv5g66_.dbf
input datafile fno=00010 name=/opt/oracle/oradata/eygle/dfmbrc.dbf
input datafile fno=00008 name=/opt/oracle/oradata/eygle/trans01.dbf
input datafile fno=00005 name=/data1/oradata/systemfile/eygle01.dbf
channel ORA_DISK_1: starting piece 1 at 29-MAR-05
channel ORA_DISK_1: finished piece 1 at 29-MAR-05
piece handle=/data5/flash_recovery_area/EYGLE/backupset/2005_03_29/
  o1_mf_nnndf_TAG20050329T065513_14k2tmx7_.bkp comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:55
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00011 name=/opt/oracle/oradata/eygle/t2k01.dbf
channel ORA_DISK_1: starting piece 1 at 29-MAR-05
channel ORA_DISK_1: finished piece 1 at 29-MAR-05
piece handle=/data5/flash_recovery_area/EYGLE/backupset/2005_03_29/
  o1_mf_nnndf_TAG20050329T065513_14k2y7go_.bkp comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 29-MAR-05

Starting Control File and SPFILE Autobackup at 29-MAR-05
piece handle=/data5/flash_recovery_area/EYGLE/autobackup/2005_03_29/
  o1_mf_s_554194638_14k2yhwz_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 29-MAR-05

RMAN>

非压缩备份耗时: 00:01:55 + 00:00:07 = 2:02 = 122s
压缩备份使用的时间是非压缩方式的: 522 / 122 = 4.28 倍.

3.压缩比率



$ ls -l
total 2585568
-rw-r-----   1 oracle   dba      190103552 Mar 29 06:36 o1_mf_nnndf_TAG20050329T062814_14k1813y_.bkp
-rw-r-----   1 oracle   dba      1093632 Mar 29 06:36 o1_mf_nnndf_TAG20050329T062814_14k1r4fh_.bkp
-rw-r-----   1 oracle   dba      1130618880 Mar 29 06:57 o1_mf_nnndf_TAG20050329T065513_14k2tmx7_.bkp
-rw-r-----   1 oracle   dba      1302528 Mar 29 06:57 o1_mf_nnndf_TAG20050329T065513_14k2y7go_.bkp

压缩备份大小: 190103552 + 1093632 = 182.33984375 M
非压缩备份集: 1130618880 + 1302528 = 1079.484375 M

非压缩较压缩大小: 1079.484375/182.33984375 = 5.9 倍
压缩方式大大节省了空间。

4.CPU消耗
压缩备份时CPU消耗:



   PID USERNAME THR PR NCE  SIZE   RES STATE   TIME FLTS    CPU COMMAND
 15252 oracle    11 10   0  430M  353M cpu01   1:38    0 26.35% oracle

大约在26%左右。
而非压缩模式下CPU消耗:



   PID USERNAME THR PR NCE  SIZE   RES STATE   TIME FLTS    CPU COMMAND
 15252 oracle    11 52   0  445M  364M sleep   9:07   57 16.43% oracle

大约为16%
压缩模式较非压缩模式多消耗10%左右的CPU。
看来选择那种模式要依赖你的环境及需要，但是毕竟Oracle提供了更多的可选项给我们。

Posted by eygle at 9:02 AM | Comments (1)

www.eygle.com

March 29, 2005

使用Oracle10g提供的flashback drop 新特性

作者:eygle

出处:http://blog.eygle.com

使用Oracle10g Flashback database功能恢复用户错误

作者:eygle

出处:http://blog.eygle.com

如何启用Oracle10g闪回数据库特性

作者:eygle

出处:http://blog.eygle.com

启用Block Change Tracking-10g新特性

作者:eygle

出处:http://blog.eygle.com

Oracle10g备份集压缩新特性(Backupset Compression)

作者:eygle

出处:http://blog.eygle.com