April 22, 2009
Oracle EXP/IMP与临时对象的管理方式
作者:eygle
出处:http://blog.eygle.com
今天做了一整天的数据导入工作，有一点点收获就是，清晰的看到了导入时临时对象的生成。这是一个Oracle9iR2的环境，在漫长的导入过程中，系统中可以看到大量这样的临时对象，在对象创建完成之后，对象的临时属性被更改为固定:
SQL> select segment_name,segment_type,tablespace_name,blocks
2 from user_segments where segment_type='TEMPORARY';
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BLOCKS
------------------ ------------------ ------------------------------ ----------
85.64060 TEMPORARY GLOB09 12800
85.76860 TEMPORARY GLOB09 12800
85.89660 TEMPORARY GLOB09 12800
85.102460 TEMPORARY GLOB09 12800
85.115260 TEMPORARY GLOB09 12800
85.128060 TEMPORARY GLOB09 12800
87.192060 TEMPORARY GLOB09 12800
87.204860 TEMPORARY GLOB09 12800
87.217660 TEMPORARY GLOB09 12800
87.230460 TEMPORARY GLOB09 12800
87.243260 TEMPORARY GLOB09 12800
87.256060 TEMPORARY GLOB09 12800
87.268860 TEMPORARY GLOB09 12800
87.281660 TEMPORARY GLOB09 12800
87.294460 TEMPORARY GLOB09 12800
87.307260 TEMPORARY GLOB09 12800
87.320060 TEMPORARY GLOB09 12800
87.332860 TEMPORARY GLOB09 12800
87.345660 TEMPORARY GLOB09 12800
87.358460 TEMPORARY GLOB09 12800
临时对象这个命名规则，大家还记得1.417对象么？
Posted by eygle at 5:30 PM | Comments (2)
李宗盛、罗大佑 - 两个青春偶像挥不去
作者:eygle
出处:http://blog.eygle.com
从网上找来的两张图片，纪念一下这些越老越帅的老男人们。
李宗盛 + 罗大佑，这两个人搞一个二人组合也不错：
罗大佑，青春永不老：
Posted by eygle at 2:37 PM | Comments (2)
RMAN结合Read Only、Exclude的备份策略
作者:eygle
出处:http://blog.eygle.com
启动数据库，我是通过一个批处理脚本来完成的：E:\>9i
E:\>echo off
OracleServiceEEYGLE 服务正在启动 .........................................................
OracleServiceEEYGLE 服务已经启动成功。
登陆数据库，将部分表空间更改为Read Only：
E:\>sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.8.0 - Production on Tue Apr 21 09:09:45 2009
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
D:\ORACLE\ORADATA\EEYGLE\SYSTEM01.DBF
D:\ORACLE\ORADATA\EEYGLE\UNDOTBS01.DBF
D:\ORACLE\ORADATA\EEYGLE\CWMLITE01.DBF
D:\ORACLE\ORADATA\EEYGLE\DRSYS01.DBF
D:\ORACLE\ORADATA\EEYGLE\EXAMPLE01.DBF
D:\ORACLE\ORADATA\EEYGLE\INDX01.DBF
D:\ORACLE\ORADATA\EEYGLE\ODM01.DBF
D:\ORACLE\ORADATA\EEYGLE\TOOLS01.DBF
D:\ORACLE\ORADATA\EEYGLE\USERS01.DBF
D:\ORACLE\ORADATA\EEYGLE\XDB01.DBF
D:\ORACLE\ORADATA\EEYGLE\PERFSTAT.DBF
11 rows selected.
SQL> select sum(bytes)/1024/1024/1024 from v$datafile;
SUM(BYTES)/1024/1024/1024
-------------------------
1.65161133
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Disabled
Archive destination D:\oracle\oradata\EEYGLE\ARCHIVE
Oldest online log sequence 52
Next log sequence to archive 53
Current log sequence 54
SQL> alter tablespace tools read only;
Tablespace altered.
SQL> alter tablespace perfstat read only;
Tablespace altered.
SQL> select 'alter tablespace '||tablespace_name ||' read only;' from dba_tablespaces;
'ALTERTABLESPACE'||TABLESPACE_NAME||'READONLY;'
----------------------------------------------------------
alter tablespace SYSTEM read only;
alter tablespace UNDOTBS1 read only;
alter tablespace TEMP read only;
alter tablespace CWMLITE read only;
alter tablespace DRSYS read only;
alter tablespace EXAMPLE read only;
alter tablespace INDX read only;
alter tablespace ODM read only;
alter tablespace TOOLS read only;
alter tablespace USERS read only;
alter tablespace XDB read only;
'ALTERTABLESPACE'||TABLESPACE_NAME||'READONLY;'
----------------------------------------------------------
alter tablespace PERFSTAT read only;
12 rows selected.
SQL> alter tablespace CWMLITE read only;
Tablespace altered.
SQL> alter tablespace DRSYS read only;
Tablespace altered.
SQL> alter tablespace EXAMPLE read only;
Tablespace altered.
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
使用RMAN进行一次0级备份，注意根据skip readonly子句，Read ONLY表空间会被自动跳过：
E:\>rman target /
Recovery Manager: Release 9.2.0.8.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: EEYGLE (DBID=1052376487)
RMAN> run{
2> allocate channel c1 type disk;
3> backup incremental level 0 tag 'db0' format 'e:\temp\db0%u_%s_%p'
4> database skip readonly;
5> sql 'alter system archive log current';
6> backup filesperset 3 format 'e:\temp\arch%u_%s_%p' archivelog all delete input;
7> release channel c1;
8> }
using target database controlfile instead of recovery catalog
allocated channel: c1
channel c1: sid=10 devtype=DISK
Starting backup at 21-APR-09
skipping read-only file 3
skipping read-only file 4
skipping read-only file 5
skipping read-only file 8
skipping read-only file 11
channel c1: starting incremental level 0 datafile backupset
channel c1: specifying datafile(s) in backupset
including current SPFILE in backupset
including current controlfile in backupset
input datafile fno=00002 name=D:\ORACLE\ORADATA\EEYGLE\UNDOTBS01.DBF
input datafile fno=00001 name=D:\ORACLE\ORADATA\EEYGLE\SYSTEM01.DBF
input datafile fno=00010 name=D:\ORACLE\ORADATA\EEYGLE\XDB01.DBF
input datafile fno=00009 name=D:\ORACLE\ORADATA\EEYGLE\USERS01.DBF
input datafile fno=00006 name=D:\ORACLE\ORADATA\EEYGLE\INDX01.DBF
input datafile fno=00007 name=D:\ORACLE\ORADATA\EEYGLE\ODM01.DBF
channel c1: starting piece 1 at 21-APR-09
channel c1: finished piece 1 at 21-APR-09
piece handle=E:\TEMP\DB008KD10DI_8_1 comment=NONE
channel c1: backup set complete, elapsed time: 00:04:08
Finished backup at 21-APR-09
sql statement: alter system archive log current
Starting backup at 21-APR-09
current log archived
channel c1: starting archive log backupset
channel c1: specifying archive log(s) in backup set
input archive log thread=1 sequence=51 recid=76 stamp=656093277
input archive log thread=1 sequence=52 recid=77 stamp=656093480
input archive log thread=1 sequence=53 recid=78 stamp=684753587
channel c1: starting piece 1 at 21-APR-09
channel c1: finished piece 1 at 21-APR-09
piece handle=E:\TEMP\ARCH09KD10M8_9_1 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:09
channel c1: deleting archive log(s)
archive log filename=D:\ORACLE\ORADATA\EEYGLE\ARCHIVE\ARC00051.001 recid=76 stamp=656093277
archive log filename=D:\ORACLE\ORADATA\EEYGLE\ARCHIVE\ARC00052.001 recid=77 stamp=656093480
archive log filename=D:\ORACLE\ORADATA\EEYGLE\ARCHIVE\ARC00053.001 recid=78 stamp=684753587
channel c1: starting archive log backupset
channel c1: specifying archive log(s) in backup set
input archive log thread=1 sequence=54 recid=79 stamp=684753596
channel c1: starting piece 1 at 21-APR-09
channel c1: finished piece 1 at 21-APR-09
piece handle=E:\TEMP\ARCH0AKD10MJ_10_1 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:17
channel c1: deleting archive log(s)
archive log filename=D:\ORACLE\ORADATA\EEYGLE\ARCHIVE\ARC00054.001 recid=79 stamp=684753596
channel c1: starting archive log backupset
channel c1: specifying archive log(s) in backup set
input archive log thread=1 sequence=55 recid=80 stamp=684753601
channel c1: starting piece 1 at 21-APR-09
channel c1: finished piece 1 at 21-APR-09
piece handle=E:\TEMP\ARCH0BKD10N5_11_1 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:04
channel c1: deleting archive log(s)
archive log filename=D:\ORACLE\ORADATA\EEYGLE\ARCHIVE\ARC00055.001 recid=80 stamp=684753601
Finished backup at 21-APR-09
released channel: c1
RMAN> exit
Recovery Manager complete.
备份完成之后修改部分数据：
E:\>sqlplus /nolog
SQL*Plus: Release 9.2.0.8.0 - Production on Tue Apr 21 09:39:34 2009
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> connect eygle/eygle
Connected.
SQL> create table eygle as select * from dba_objects;
Table created.
SQL> alter system switch logfile;
System altered.
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
进行Level 1级的增量备份：
E:\>rman target /
Recovery Manager: Release 9.2.0.8.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: EEYGLE (DBID=1052376487)
RMAN> run{
2> allocate channel c1 type disk;
3> backup incremental level 1 tag 'db1' format 'e:\temp\db1%u_%s_%p'
4> database skip readonly;
5> sql 'alter system archive log current';
6> backup filesperset 3 format 'e:\temp\arch%u_%s_%p'
7> archivelog all delete input;
8> release channel c1;
9> }
using target database controlfile instead of recovery catalog
allocated channel: c1
channel c1: sid=11 devtype=DISK
Starting backup at 21-APR-09
skipping read-only file 3
skipping read-only file 4
skipping read-only file 5
skipping read-only file 8
skipping read-only file 11
channel c1: starting incremental level 1 datafile backupset
channel c1: specifying datafile(s) in backupset
including current SPFILE in backupset
including current controlfile in backupset
input datafile fno=00002 name=D:\ORACLE\ORADATA\EEYGLE\UNDOTBS01.DBF
input datafile fno=00001 name=D:\ORACLE\ORADATA\EEYGLE\SYSTEM01.DBF
input datafile fno=00010 name=D:\ORACLE\ORADATA\EEYGLE\XDB01.DBF
input datafile fno=00009 name=D:\ORACLE\ORADATA\EEYGLE\USERS01.DBF
input datafile fno=00006 name=D:\ORACLE\ORADATA\EEYGLE\INDX01.DBF
input datafile fno=00007 name=D:\ORACLE\ORADATA\EEYGLE\ODM01.DBF
channel c1: starting piece 1 at 21-APR-09
channel c1: finished piece 1 at 21-APR-09
piece handle=E:\TEMP\DB10CKD120F_12_1 comment=NONE
channel c1: backup set complete, elapsed time: 00:01:07
Finished backup at 21-APR-09
sql statement: alter system archive log current
Starting backup at 21-APR-09
current log archived
channel c1: starting archive log backupset
channel c1: specifying archive log(s) in backup set
input archive log thread=1 sequence=56 recid=81 stamp=684755029
input archive log thread=1 sequence=57 recid=82 stamp=684755031
input archive log thread=1 sequence=58 recid=83 stamp=684755033
channel c1: starting piece 1 at 21-APR-09
channel c1: finished piece 1 at 21-APR-09
piece handle=E:\TEMP\ARCH0DKD122P_13_1 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:05
channel c1: deleting archive log(s)
archive log filename=D:\ORACLE\ORADATA\EEYGLE\ARCHIVE\ARC00056.001 recid=81 stamp=684755029
archive log filename=D:\ORACLE\ORADATA\EEYGLE\ARCHIVE\ARC00057.001 recid=82 stamp=684755031
archive log filename=D:\ORACLE\ORADATA\EEYGLE\ARCHIVE\ARC00058.001 recid=83 stamp=684755033
Finished backup at 21-APR-09
released channel: c1
RMAN> exit
Recovery Manager complete.
再更改数据库内容：
E:\>sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.8.0 - Production on Tue Apr 21 09:45:52 2009
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
SQL> connect eygle/eygle
Connected.
SQL> drop table test;
Table dropped.
SQL> drop table eygle;
Table dropped.
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
使用RMAN配置Exclude排除表空间，注意此处测试排除多个表空间的备份：
E:\>rman target /
Recovery Manager: Release 9.2.0.8.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: EEYGLE (DBID=1052376487)
RMAN> configure exclude for tablespace INDX;
using target database controlfile instead of recovery catalog
tablespace INDX will be excluded from future whole database backups
new RMAN configuration parameters are successfully stored
RMAN> show exclude;
RMAN configuration parameters are:
CONFIGURE EXCLUDE FOR TABLESPACE 'INDX';
RMAN> configure exclude for tablespace ODM;
tablespace ODM will be excluded from future whole database backups
new RMAN configuration parameters are successfully stored
RMAN> show exclude;
RMAN configuration parameters are:
CONFIGURE EXCLUDE FOR TABLESPACE 'INDX';
CONFIGURE EXCLUDE FOR TABLESPACE 'ODM';
RMAN> configure exclude for tablespace TOOLS;
tablespace TOOLS will be excluded from future whole database backups
new RMAN configuration parameters are successfully stored
RMAN>
RMAN> configure exclude for tablespace XDB;
tablespace XDB will be excluded from future whole database backups
new RMAN configuration parameters are successfully stored
RMAN> show exclude;
RMAN configuration parameters are:
CONFIGURE EXCLUDE FOR TABLESPACE 'INDX';
CONFIGURE EXCLUDE FOR TABLESPACE 'ODM';
CONFIGURE EXCLUDE FOR TABLESPACE 'TOOLS';
CONFIGURE EXCLUDE FOR TABLESPACE 'XDB';
RMAN> configure exclude for tablespace USERS;
tablespace USERS will be excluded from future whole database backups
new RMAN configuration parameters are successfully stored
RMAN> show exclude;
RMAN configuration parameters are:
CONFIGURE EXCLUDE FOR TABLESPACE 'INDX';
CONFIGURE EXCLUDE FOR TABLESPACE 'ODM';
CONFIGURE EXCLUDE FOR TABLESPACE 'TOOLS';
CONFIGURE EXCLUDE FOR TABLESPACE 'USERS';
CONFIGURE EXCLUDE FOR TABLESPACE 'XDB';
RMAN> run{
2> allocate channel c1 type disk;
3> backup incremental level 1 tag 'db1' format 'e:\temp\db1%u_%s_%p'
4> database skip readonly;
5> sql 'alter system archive log current';
6> backup filesperset 3 format 'e:\temp\arch%u_%s_%p'
7> archivelog all delete input;
8> release channel c1;
9> }
allocated channel: c1
channel c1: sid=10 devtype=DISK
Starting backup at 21-APR-09
skipping read-only file 3
skipping read-only file 4
skipping read-only file 5
file 6 is excluded from whole database backup
file 7 is excluded from whole database backup
file 8 is excluded from whole database backup
file 9 is excluded from whole database backup
file 10 is excluded from whole database backup
skipping read-only file 11
channel c1: starting incremental level 1 datafile backupset
channel c1: specifying datafile(s) in backupset
including current SPFILE in backupset
including current controlfile in backupset
input datafile fno=00002 name=D:\ORACLE\ORADATA\EEYGLE\UNDOTBS01.DBF
input datafile fno=00001 name=D:\ORACLE\ORADATA\EEYGLE\SYSTEM01.DBF
channel c1: starting piece 1 at 21-APR-09
channel c1: finished piece 1 at 21-APR-09
piece handle=E:\TEMP\DB10EKD12B2_14_1 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:57
Finished backup at 21-APR-09
sql statement: alter system archive log current
Starting backup at 21-APR-09
current log archived
channel c1: starting archive log backupset
channel c1: specifying archive log(s) in backup set
input archive log thread=1 sequence=59 recid=84 stamp=684755357
input archive log thread=1 sequence=60 recid=85 stamp=684755360
channel c1: starting piece 1 at 21-APR-09
channel c1: finished piece 1 at 21-APR-09
piece handle=E:\TEMP\ARCH0FKD12D1_15_1 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:04
channel c1: deleting archive log(s)
archive log filename=D:\ORACLE\ORADATA\EEYGLE\ARCHIVE\ARC00059.001 recid=84 stamp=684755357
archive log filename=D:\ORACLE\ORADATA\EEYGLE\ARCHIVE\ARC00060.001 recid=85 stamp=684755360
Finished backup at 21-APR-09
released channel: c1
RMAN>
通过排除多个表空间、跳过Read Only表空间，可以简化我们的备份策略，在特定条件下，有助于我们的数据库管理与维护。
-The End-
Posted by eygle at 10:01 AM | Comments (3)