关于linux中oracle用户进程占用内存猜测

本文是针对linux下面显示oracle用户进程占用大量内存的一个猜想性说明,希望各位专家和我一起继续探讨该问题
ORACLE用户进程占用私有内存分析
top命令结果

[oracle@ora02 31500]$ top -c
top - 12:13:16 up 254 days, 12:14,  2 users,  load average: 1.53, 1.62, 1.33
Tasks: 293 total,   3 running, 290 sleeping,   0 stopped,   0 zombie
Cpu(s):  3.4% us,  0.8% sy,  0.0% ni, 94.7% id,  1.1% wa,  0.0% hi,  0.0% si
Mem:   4147172k total,  4129724k used,    17448k free,    20348k buffers
Swap:  4192956k total,   217772k used,  3975184k free,  2575320k cached
  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
12505 oracle    17   0 1561m 972m 970m R  100 24.0   3:45.89 oracletxzldb (LOCAL=NO)
12475 oracle    16   0 1561m 931m 929m S    4 23.0   4:08.65 oracletxzldb (LOCAL=NO)
12477 oracle    16   0 1561m 945m 942m S    2 23.3   3:36.92 oracletxzldb (LOCAL=NO)
12479 oracle    16   0 1561m 944m 941m S    2 23.3   4:27.29 oracletxzldb (LOCAL=NO)
12483 oracle    16   0 1561m 939m 937m R    2 23.2   3:49.00 oracletxzldb (LOCAL=NO)
12493 oracle    16   0 1561m 958m 955m S    2 23.7   3:35.35 oracletxzldb (LOCAL=NO)

补充说明:

VIRT	进程使用的虚拟内存总量
RES	进程使用的、未被换出的物理内存大小
SHR	共享内存大小

通过这里可以得出几个信息
12505进程实际使用内存情况:972-970=2M
12505进程%MEM:972*1024/4147172=0.24000162
12505进程在数据库中占用内存

SQL> select PGA_ALLOC_MEM/1024/1024 MEM from v$process where spid=12505;
       MEM
----------
1.90997028

通过这里可以看出12505进程实际上是占用了970M的共享内存,占用2M的PGA内存

ORACLE用户进程占用共享内存分析
分析12505进程的当前进程状态

[oracle@ora02 31500]$ cd /proc/12505
[oracle@ora02 12505]$ cat status
Name:   oracle
State:  S (sleeping)
SleepAVG:       92%
Tgid:   12505
Pid:    12505
PPid:   1
TracerPid:      0
Uid:    501     501     501     501
Gid:    502     502     502     502
FDSize: 32
Groups: 501 502
VmSize:  1599004 kB    <--使用内存(包括虚拟内存)总量1599004/1024=1561.52734和top中VIRT基本吻合
VmLck:         0 kB
VmRSS:    996132 kB    <--实际使用内存996132/1024 =972.785156和top看到RES基本吻合
VmData:      832 kB
VmStk:       120 kB
VmExe:     37307 kB
VmLib:      4641 kB
StaBrk: 0ad6e000 kB
Brk:    0adf2000 kB
StaStk: bffff850 kB
ExecLim:        ffffffff
Threads:        1
SigPnd: 0000000000000000
ShdPnd: 0000000000000000
SigBlk: 0000000000000000
SigIgn: 0000000006005203
SigCgt: 00000001c9802cfc
CapInh: 0000000000000000
CapPrm: 0000000000000000
CapEff: 0000000000000000

pmap命令分析

[oracle@ora02 12505]$ pmap -d 12505
12505:   oracletxzldb (LOCAL=NO)
Address   Kbytes Mode  Offset           Device    Mapping
0013f000      88 r-x-- 0000000000000000 008:00002 ld-2.3.4.so
00155000       4 r-x-- 0000000000015000 008:00002 ld-2.3.4.so
00156000       4 rwx-- 0000000000016000 008:00002 ld-2.3.4.so
00159000    1176 r-x-- 0000000000000000 008:00002 libc-2.3.4.so
0027f000       8 r-x-- 0000000000125000 008:00002 libc-2.3.4.so
00281000       8 rwx-- 0000000000127000 008:00002 libc-2.3.4.so
00283000       8 rwx-- 0000000000283000 000:00000   [ anon ]
00287000     132 r-x-- 0000000000000000 008:00002 libm-2.3.4.so
002a8000       4 r-x-- 0000000000020000 008:00002 libm-2.3.4.so
002a9000       4 rwx-- 0000000000021000 008:00002 libm-2.3.4.so
002ac000       8 r-x-- 0000000000000000 008:00002 libdl-2.3.4.so
002ae000       4 r-x-- 0000000000001000 008:00002 libdl-2.3.4.so
002af000       4 rwx-- 0000000000002000 008:00002 libdl-2.3.4.so
003b5000      56 r-x-- 0000000000000000 008:00002 libpthread-2.3.4.so
003c3000       4 r-x-- 000000000000d000 008:00002 libpthread-2.3.4.so
003c4000       4 rwx-- 000000000000e000 008:00002 libpthread-2.3.4.so
003c5000       8 rwx-- 00000000003c5000 000:00000   [ anon ]
00ba4000      72 r-x-- 0000000000000000 008:00002 libnsl-2.3.4.so
00bb6000       4 r-x-- 0000000000011000 008:00002 libnsl-2.3.4.so
00bb7000       4 rwx-- 0000000000012000 008:00002 libnsl-2.3.4.so
00bb8000       8 rwx-- 0000000000bb8000 000:00000   [ anon ]
08048000   37308 r-x-- 0000000000000000 0fd:00001 oracle
0a4b7000    8804 rwx-- 000000000246f000 0fd:00001 oracle
0ad50000     648 rwx-- 000000000ad50000 000:00000   [ anon ]
50000000 1540096 rwxs- 0000000000000000 000:00006   [ shmid=0x9000e ]
ae000000       4 r-xs- 000000005e000000 000:00006   [ shmid=0x9000e ]
ae001000    1156 rwxs- 000000005e001000 000:00006   [ shmid=0x9000e ]
ae122000       4 r-xs- 000000005e122000 000:00006   [ shmid=0x9000e ]
ae123000    2932 rwxs- 000000005e123000 000:00006   [ shmid=0x9000e ]
b79d4000    1024 rwx-- 00000000000f4000 000:0000d zero
b7ad4000     512 rwx-- 0000000000074000 000:0000d zero
b7b54000     512 rwx-- 0000000000000000 000:0000d zero
b7bd4000      36 r-x-- 0000000000000000 008:00002 libnss_files-2.3.4.so
b7bdd000       4 r-x-- 0000000000008000 008:00002 libnss_files-2.3.4.so
b7bde000       4 rwx-- 0000000000009000 008:00002 libnss_files-2.3.4.so
b7bdf000     148 rwx-- 00000000b7bdf000 000:00000   [ anon ]
b7c04000    2940 r-x-- 0000000000000000 0fd:00001 libjox9.so
b7ee3000    1088 rwx-- 00000000002de000 0fd:00001 libjox9.so
b7ff3000       8 rwx-- 00000000b7ff3000 000:00000   [ anon ]
b7ff5000       4 r-x-- 0000000000000000 0fd:00001 libskgxn9.so
b7ff6000       8 rwx-- 0000000000000000 0fd:00001 libskgxn9.so
b7ff8000       4 r-x-- 0000000000000000 0fd:00001 libskgxp9.so
b7ff9000       4 --x-- 0000000000001000 0fd:00001 libskgxp9.so
b7ffa000       4 rwx-- 0000000000001000 0fd:00001 libskgxp9.so
b7ffb000       4 r-x-- 0000000000000000 0fd:00001 libodmd9.so
b7ffc000       4 rwx-- 0000000000000000 0fd:00001 libodmd9.so
b7ffd000       4 r-x-- 0000000000000000 008:00002 libcwait.so
b7ffe000       4 rwx-- 0000000000000000 008:00002 libcwait.so
b7fff000       4 rwx-- 00000000b7fff000 000:00000   [ anon ]
bffe2000     120 rwx-- 00000000bffe2000 000:00000   [ stack ]
ffffe000       4 ----- 0000000000000000 000:00000   [ anon ]
mapped: 1599008K    writeable/private: 12944K    shared: 1544192K

补充说明:

mapped ：映射到文件的内存数量
writable/private ：进程所占用的私有地址空间数量
shared ：与其它进程共享的地址空间数量

ipcs 命令

[oracle@ora02 12505]$ ipcs -m
------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status
0x0000cace 65536      root      666        2          0
0x4d4e5251 98305      root      644        330752     0
0x55315352 131074     root      666        4096       0
0x44525354 163843     root      644        632832     0
0x53494152 196612     root      644        1024       0
0x00005643 229381     root      666        1024       1
0x00005654 262150     root      666        1024       1
0x992ad3dc 589838     oracle    640        1581252608 595

结合pmap和ipcs分析(shmid=0x9000e)

SQL> select to_number('9000e','xxxxxxxx') from dual;
TO_NUMBER('9000E','XXXXXXXX')
-----------------------------
                       589838
SQL> select 1540096+4+1156+4+2932 from dual;
1540096+4+1156+4+2932
---------------------
              1544192
SQL> select 1581252608/1024 from dual;
1581252608/1024
---------------
        1544192

通过这里可以得出12505进程中的共享内存,主要是数据库SGA中的共享内存

补充猜测

SQL> show sga;
Total System Global Area 1561926292 bytes
Fixed Size                   453268 bytes
Variable Size             603979776 bytes
Database Buffers          956301312 bytes
Redo Buffers                1191936 bytes
SQL> select 1561926292/1024 from dual;
1561926292/1024
---------------
     1525318.64

这里显示数据库配置的sga比ipcs中配置共享内存段小,但是进程在分配总的共享内存时候,使用的是ipcs设定的内存段大小,实际使用的内存可能是sga设置大小(未得到权威资料)

impdp报ORA-00904: "ORIGINAL_OBJECT_NAME": invalid identifier

发现问题
impdp导入数据库不成功,一直在报ORA-00904: “ORIGINAL_OBJECT_NAME”: invalid identifier错误

[oracle@back1 backup]$ impdp username/password  schemas=center_admin dumpfile=center_admin20120427.dmp
> logfile=center_admin20120427.log directory=impdir parallel=10 job_name=center_admin08;
Import: Release 11.1.0.6.0 - 64bit Production on Friday, 27 April, 2012 21:35:06
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39002: invalid operation
ORA-31694: master table "USERNAME"."CENTER_ADMIN08" failed to load/unload
ORA-02354: error in exporting/importing data
ORA-02373: Error parsing insert statement for table "USERNAME"."CENTER_ADMIN08".
ORA-00904: "ORIGINAL_OBJECT_NAME": invalid identifier

分析问题
看到这个错误,我第一个感觉根据ORA-31694,怀疑是没有创建相关用户,或者是该用户无权限权限CENTER_ADMIN08表.等我登陆目标数据库查看时候发现该用户存在,并且已经授予了DBA权限,所以不存在是用户相关问题导致.ORA-02354错误我怀疑是expdp导出来的文件在传输过程中发生意外(如使用ftp传输未使用二进制模式),当我使用md5sum命令检查发现两边一致,证明该文件传输正常.目标端不能检查明显故障,怀疑导出文件本身存在问题检查导出文件日志

[oracle@fcdb2 backup]$ more center_admin20120427.log
;;;
Export: Release 11.1.0.7.0 - 64bit Production on Friday, 27 April, 2012 17:32:30
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
;;;
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting "USERNAME"."CENTER_ADMIN08":USERNAME/**** schemas=center_admin directory=expdir dumpfile=center_admin20120427.dmp
er_admin20120427.log parallel=10 job_name=center_admin08
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 158.2 GB

发现新大陆,两边的数据库版本不一致,源端版本是11.1.0.7,目标端版本是11.1.0.6.这个时候我对问题的定位是可能版本兼用问题(毕竟是高版本到低版本)或者是bug.先查询datapump版本之间兼容性列表

     Version      Written by     Can be imported into Target:
   Data Pump   database with      10gR1      10gR2      11gR1      11gR2
Dumpfile Set   compatibility   10.1.0.x   10.2.0.x   11.1.0.x   11.2.0.x
------------ --------------- ---------- ---------- ---------- ----------
         0.1          10.1.x  supported  supported  supported  supported
         1.1          10.2.x         no  supported  supported  supported
         2.1          11.1.x         no         no  supported  supported
         3.1          11.2.x         no         no         no  supported

这里可以看出11.1.0.7和11.1.0.6之间是相互兼容的,不应该会存在上述问题,那么现在对于该问题的解释很可能是bug导致,继续查询资料发现[ID 752374.1]描述的正是该问题.

解决问题

1. Apply 11.1.0.7 Patch:6890831 on the target database.
2. Workaround this issue by re-running an expdp from the 11.1.0.7 database with an additional
parameter VERSION=10.2. This will create a new dump file compatible to be imported into 10gR2,
which will also import successfully into 11.1.0.6.
Unfortunately, please be aware that the new 11g specific features will not be
exported if expdp is run from 11.1.0.7 with the parameter VERSION=10.2.
If only normal objects and features are involved, this could be a good workaround.

ORA-00001 Unique Constraint SYS.I_JOB_JOB Violated

IMPDP导入数据发现ORA-00001 Unique Constraint SYS.I_JOB_JOB Violated错误

ORA-39083: Object type JOB failed to create with error:
ORA-00001: unique constraint (SYS.I_JOB_JOB) violated
Failing sql is:
 BEGIN DBMS_JOB.ISUBMIT( JOB=> 63, NEXT_DATE=> TO_DATE('2012-04-27 00:00:00',
'YYYY-MM-DD:HH24:MI:SS'), INTERVAL=> 'TRUNC(SYSDATE+1)', WHAT=> 'GBEAS1.UPDATE_EMP_INFO;',
NO_PARSE=> TRUE); END;
Job "GBEAS3"."SYS_IMPORT_FULL_01" completed with 8 error(s) at 16:05:58

错误原因(该job=63已经存在数据库中)

select job, what from   dba_jobs where job=63;
JOB     WHAT
-----   --------
63      proc_xifenfei

注意:如果该job正在运行,可能需要查询DBA_JOBS_RUNNING

解决办法

1.手工创建job,指定一个不存在的job 号
declare
  m_job number;
begin
  select max (job) + 1
  into   m_job
  from   dba_jobs;
BEGIN DBMS_JOB.ISUBMIT( JOB=> m_job, NEXT_DATE=> TO_DATE('2012-04-27 00:00:00',
'YYYY-MM-DD:HH24:MI:SS'), INTERVAL=> 'TRUNC(SYSDATE+1)', WHAT=> 'GBEAS1.UPDATE_EMP_INFO;',
NO_PARSE=> TRUE); END;
end;
/
2.删除原存在job
exec dbms_job.remove (63);

这样的情况，一般发生在expdp导出数据包含了job(如:全库导出,用户导出),然后导入到目标库,而该job号已经存在导致

awr导出/导入/分析

很多时候我们直接在客户机器上分析awr不太方便,需要通过收集客户awr信息到另一台机器上进行分析数据库性能等.这种情况下,就需要对客户的awr数据进行导出,然后导入到其他机器上,再进行深入分析.
导出awr数据

SQL> @?/rdbms/admin/awrextr.sql
~~~~~~~~~~~~~
AWR EXTRACT
~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~  This script will extract the AWR data for a range of snapshots  ~
~  into a dump file.  The script will prompt users for the         ~
~  following information:                                          ~
~     (1) database id                                              ~
~     (2) snapshot range to extract                                ~
~     (3) name of directory object                                 ~
~     (4) name of dump file                                        ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Databases in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   DB Id     DB Name      Host
------------ ------------ ------------
* 1393262699 XIFENFEI     XIFENFEI-PC
  3753332923 FDJDB        ora1
  3753332923 FDJDB        ora2
The default database id is the local one: '1393262699'.  To use this
database id, press <return> to continue, otherwise enter an alternative.
输入 dbid 的值:  3753332923    <--需要输入
Using 3753332923 for Database ID
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.
输入 num_days 的值:  1    <--需要输入
Listing the last day's Completed Snapshots
DB Name        Snap Id    Snap Started
------------ --------- ------------------
FDJDB              906 23 4月  2012 00:00
                   907 23 4月  2012 01:00
                   908 23 4月  2012 02:00
                   909 23 4月  2012 03:00
                   910 23 4月  2012 04:00
                   911 23 4月  2012 05:00
                   912 23 4月  2012 06:00
                   913 23 4月  2012 07:00
                   914 23 4月  2012 08:00
                   915 23 4月  2012 09:00
                   916 23 4月  2012 10:00
                   917 23 4月  2012 11:00
                   918 23 4月  2012 12:00
                   919 23 4月  2012 13:00
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
输入 begin_snap 的值:  906  <--需要输入
Begin Snapshot Id specified: 906
输入 end_snap 的值:  907    <--需要输入
End   Snapshot Id specified: 907
Specify the Directory Name
~~~~~~~~~~~~~~~~~~~~~~~~~~
Directory Name                 Directory Path
------------------------------ -------------------------------------------------
DATA_FILE_DIR                  E:\oracle\product\11.2.0\dbhome_1\demo\schema\sales_history\
DATA_PUMP_DIR                  E:\oracle\product\11.2.0\dbhome_1\rdbms\log\
LOG_FILE_DIR                   E:\oracle\product\11.2.0\dbhome_1\demo\schema\log\
MEDIA_DIR                      E:\oracle\product\11.2.0\dbhome_1\demo\schema\product_media\
ORACLE_OCM_CONFIG_DIR          E:\oracle\product\11.2.0\dbhome_1\ccr\state
SS_OE_XMLDIR                   E:\oracle\product\11.2.0\dbhome_1\demo\schema\ord er_entry\
SUBDIR                         E:\oracle\product\11.2.0\dbhome_1\demo\schema\order_entry\/2002/Sep
XMLDIR                         E:\oracle\product\11.2.0\dbhome_1\rdbms\xml
Choose a Directory Name from the above list (case-sensitive).
输入 directory_name 的值:  DATA_PUMP_DIR  <--需要输入(注意大小写)
Using the dump directory: DATA_PUMP_DIR
Specify the Name of the Extract Dump File
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The prefix for the default dump file name is awrdat_906_907.
To use this name, press <return> to continue, otherwise enter
an alternative.
输入 file_name 的值:  xifenfei_awr  <--需要输入
Using the dump file prefix: xifenfei_awr
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|  The AWR extract dump file will be located
|  in the following directory/file:
|   E:\oracle\product\11.2.0\dbhome_1\rdbms\log\
|   xifenfei_awr.dmp
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
|  *** AWR Extract Started ...
|
|  This operation will take a few moments. The
|  progress of the AWR extract operation can be
|  monitored in the following directory/file:
|   E:\oracle\product\11.2.0\dbhome_1\rdbms\log\
|   xifenfei_awr.log
|  可以通过查看E:\oracle\product\11.2.0\dbhome_1\rdbms\log\xifenfei_awr.log
|  监控导出awr数据进度
End of AWR Extract

导入awr数据

SQL> @E:\oracle\product\11.2.0\dbhome_1\RDBMS\ADMIN\awrload.sql
~~~~~~~~~~
AWR LOAD
~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~  This script will load the AWR data from a dump file. The   ~
~  script will prompt users for the following information:    ~
~     (1) name of directory object                            ~
~     (2) name of dump file                                   ~
~     (3) staging schema name to load AWR data into           ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Specify the Directory Name
~~~~~~~~~~~~~~~~~~~~~~~~~~
Directory Name                 Directory Path
------------------------------ -------------------------------------------------
DATA_FILE_DIR                  E:\oracle\product\11.2.0\dbhome_1\demo\schema\sales_history\
DATA_PUMP_DIR                  E:\oracle\product\11.2.0\dbhome_1\rdbms\log\
LOG_FILE_DIR                   E:\oracle\product\11.2.0\dbhome_1\demo\schema\log\
MEDIA_DIR                      E:\oracle\product\11.2.0\dbhome_1\demo\schema\product_media\
ORACLE_OCM_CONFIG_DIR          E:\oracle\product\11.2.0\dbhome_1\ccr\state
SS_OE_XMLDIR                   E:\oracle\product\11.2.0\dbhome_1\demo\schema\order_entry\
SUBDIR                         E:\oracle\product\11.2.0\dbhome_1\demo\schema\order_entry\/2002/Sep
XMLDIR                         E:\oracle\product\11.2.0\dbhome_1\rdbms\xml
Choose a Directory Name from the list above (case-sensitive).
输入 directory_name 的值:  DATA_PUMP_DIR  <--需要输入(注意大小写)
Using the dump directory: DATA_PUMP_DIR
Specify the Name of the Dump File to Load
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Please specify the prefix of the dump file (.dmp) to load:
输入 file_name 的值:  awrdat_751_919 <--需要输入(文件后缀名一定要是.dmp)
Loading from the file name: awrdat_751_919.dmp
Staging Schema to Load AWR Snapshot Data
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The next step is to create the staging schema
where the AWR snapshot data will be loaded.
After loading the data into the staging schema,
the data will be transferred into the AWR tables
in the SYS schema.
The default staging schema name is AWR_STAGE.
To use this name, press <return> to continue, otherwise enter
an alternative.
输入 schema_name 的值:  XFF_AWR  <--需要输入(临时创建用户)
Using the staging schema name: XFF_AWR
Choose the Default tablespace for the XFF_AWR user
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Choose the XFF_AWR users's default tablespace.  This is the
tablespace in which the AWR data will be staged.
TABLESPACE_NAME                CONTENTS  DEFAULT TABLESPACE
------------------------------ --------- ------------------
EXAMPLE                        PERMANENT
SYSAUX                         PERMANENT *
USERS                          PERMANENT
Pressing <return> will result in the recommended default
tablespace (identified by *) being used.
输入 default_tablespace 的值:  EXAMPLE  <--需要输入
Using tablespace EXAMPLE as the default tablespace for the XFF_AWR
Choose the Temporary tablespace for the XFF_AWR user
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Choose the XFF_AWR user's temporary tablespace.
TABLESPACE_NAME                CONTENTS  DEFAULT TEMP TABLESPACE
------------------------------ --------- -----------------------
TEMP                           TEMPORARY *
Pressing <return> will result in the database's default temporary
tablespace (identified by *) being used.
输入 temporary_tablespace 的值:  TEMP  <--需要输入
Using tablespace TEMP as the temporary tablespace for XFF_AWR
... Creating XFF_AWR user  (临时用户创建)
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|  Loading the AWR data from the following
|  directory/file:
|   E:\oracle\product\11.2.0\dbhome_1\rdbms\log\
|   awrdat_751_919.dmp
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
|  *** AWR Load Started ...
|
|  This operation will take a few moments. The
|  progress of the AWR load operation can be
|  monitored in the following directory/file:
|   E:\oracle\product\11.2.0\dbhome_1\rdbms\log\
|   awrdat_751_919.log
|
|  可以通过查看E:\oracle\product\11.2.0\dbhome_1\rdbms\log\awrdat_751_919.log
|  监控导出awr数据进度
... Dropping XFF_AWR user  (临时用户被删除)
End of AWR Load

查看awr报告

SQL> @?/RDBMS/admin/awrrpti.sql
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
输入 report_type 的值:  html   <--需要输入
Type Specified:  html
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
  3753332923        2 FDJDB        fdjdb2       ora2
  3753332923        1 FDJDB        fdjdb1       ora1
* 1393262699        1 XIFENFEI     xff          XIFENFEI-PC
输入 dbid 的值:   3753332923  <--需要输入
Using  3753332923 for database Id
输入 inst_num 的值:  1        <--需要输入
Using 1 for instance number
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.
输入 num_days 的值:  1  <--需要输入
Listing the last day's Completed Snapshots
                                                        Snap
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
fdjdb1       FDJDB              906 23 4月  2012 00:00     1
                                907 23 4月  2012 01:00     1
                                908 23 4月  2012 02:00     1
                                909 23 4月  2012 03:00     1
                                910 23 4月  2012 04:00     1
                                911 23 4月  2012 05:00     1
                                912 23 4月  2012 06:00     1
                                913 23 4月  2012 07:00     1
                                914 23 4月  2012 08:00     1
                                915 23 4月  2012 09:00     1
                                916 23 4月  2012 10:00     1
                                917 23 4月  2012 11:00     1
                                918 23 4月  2012 12:00     1
                                919 23 4月  2012 13:00     1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
输入 begin_snap 的值:  917  <--需要输入
Begin Snapshot Id specified: 917
输入 end_snap 的值:  918    <--需要输入
End   Snapshot Id specified: 918
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_917_918.html.  To use this name,
press <return> to continue, otherwise enter an alternative.
输入 report_name 的值:xifenfei_awr.html  <--需要输入

恢复备份控制文件避免resetlogs方式打开数据库

在很多时候,我们需要使用备份控制文件恢复数据库,在恢复完成后,准备打开库,很多人知道这个时候如果要打开这个库,需要使用resetlogs操作,虽然在oracle 10g及其以后版本中在恢复的时候可以跨越resetlogs操作,但是很多时候大家还是希望使用备份的控制文件能够正常的open一个库,而不是resetlogs.这里通过实验展示使用备份控制文件正常open库的过程,整体思路是:先使用备份控制文件正常恢复数据库,然后重建该控制文件,继而可以正常open库

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production
SQL> alter database backup controlfile to '/tmp/controlfile.bak';
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
--替换备份的控制文件
SQL> startup mount;
ORACLE instance started.
Total System Global Area  353441008 bytes
Fixed Size                   451824 bytes
Variable Size             184549376 bytes
Database Buffers          167772160 bytes
Redo Buffers                 667648 bytes
Database mounted.
SQL> recover database using backup controlfile;
ORA-00279: change 12286827844770 generated at 04/12/2012 00:21:54 needed for
thread 1
ORA-00289: suggestion : /u01/oracle/oradata/xifenfei/archive/1_4.dbf
ORA-00280: change 12286827844770 for thread 1 is in sequence #4
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 12286827844772 generated at 04/12/2012 00:21:55 needed for
thread 1
ORA-00289: suggestion : /u01/oracle/oradata/xifenfei/archive/1_5.dbf
ORA-00280: change 12286827844772 for thread 1 is in sequence #5
ORA-00278: log file '/u01/oracle/oradata/xifenfei/archive/1_4.dbf' no longer
needed for this recovery
ORA-00279: change 12286827844776 generated at 04/12/2012 00:21:58 needed for
thread 1
ORA-00289: suggestion : /u01/oracle/oradata/xifenfei/archive/1_6.dbf
ORA-00280: change 12286827844776 for thread 1 is in sequence #6
ORA-00278: log file '/u01/oracle/oradata/xifenfei/archive/1_5.dbf' no longer
needed for this recovery
ORA-00308: cannot open archived log
'/u01/oracle/oradata/xifenfei/archive/1_6.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
SQL>  recover database using backup controlfile;
ORA-00279: change 12286827844776 generated at 04/12/2012 00:21:58 needed for
thread 1
ORA-00289: suggestion : /u01/oracle/oradata/xifenfei/archive/1_6.dbf
ORA-00280: change 12286827844776 for thread 1 is in sequence #6
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/oracle/oradata/xifenfei/redo02.log
ORA-00310: archived log contains sequence 3; sequence 6 required
ORA-00334: archived log: '/u01/oracle/oradata/xifenfei/redo02.log'
SQL>  recover database using backup controlfile;
ORA-00279: change 12286827844776 generated at 04/12/2012 00:21:58 needed for
thread 1
ORA-00289: suggestion : /u01/oracle/oradata/xifenfei/archive/1_6.dbf
ORA-00280: change 12286827844776 for thread 1 is in sequence #6
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/oracle/oradata/xifenfei/redo03.log
Log applied.
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
--提示需要resetlogs
SQL> alter database backup controlfile to trace as '/tmp/1.txt';
Database altered.
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP NOMOUNT
Total System Global Area  353441008 bytes
Fixed Size                   451824 bytes
Variable Size             184549376 bytes
Database Buffers          167772160 bytes
Redo Buffers                 667648 bytes
--重建控制文件
--自动启动到mount状态
--数据库直接open成功
SQL> alter database open;
Database altered.

9I中清除特定表相关执行计划

在9i中因为某个执行计划因为Oracle Peeking绑定变量的控制导致现有的执行计划不正确,需要清除掉这条sql语句的执行计划.在10g中提供了dbms_shared_pool.purge(见:清除掉shared pool中某条sql语句方法),但是在9i中未提供好的方法,一般来说可以通过对相关表的DDL操作,收集统计信息,授权操作可以实现清除对于表执行计划.注:这些操作不会只清空特定SQL执行计划,而是会清除该表相关的所有执行计划,所以操作需要慎重(影响肯定比flush shared_pool小)
模拟测试数据

SQL> create table t_xifenfei (id number,name varchar2(100));
Table created.
SQL> insert into t_xifenfei values(1,'www.xifenfei.com');
1 row created.
SQL> commit;

清除执行计划1:修改表结构

SQL>  alter system flush shared_pool;
System altered.
SQL> select * from t_xifenfei;
        ID NAME
---------- -------------------
         1 www.xifenfei.com
SQL> select sql_text,HASH_VALUE from v$sqlAREA where sql_text like '%xifenfei';
SQL_TEXT                                           HASH_VALUE
-------------------------------------------------- ----------
select * from t_xifenfei                           1067507827
SQL> select OPERATION from v$sql_plan where hash_value=1067507827;
OPERATION
------------------------------------------------------------
SELECT STATEMENT
TABLE ACCESS
SQL> alter table t_xifenfei  add fei varchar2(10);
Table altered.
SQL> alter table t_xifenfei drop COLUMN fei;
Table altered.
SQL> select sql_text,HASH_VALUE from v$sqlAREA where sql_text like '%xifenfei';
SQL_TEXT                                           HASH_VALUE
-------------------------------------------------- ----------
select * from t_xifenfei                           1067507827
SQL>  select count(*) from v$sql_plan where hash_value=1067507827;
  COUNT(*)
----------
         0

清除执行计划2:重新收集统计信息

--DBMS_STATS收集统计信息
SQL> alter system flush shared_pool;
System altered.
SQL> select * from t_xifenfei;
        ID NAME
---------- -------------------
         1 www.xifenfei.com
SQL> select sql_text,HASH_VALUE from v$sqlAREA where sql_text like '%xifenfei';
SQL_TEXT                                           HASH_VALUE
-------------------------------------------------- ----------
select * from t_xifenfei                           1067507827
SQL> select OPERATION from v$sql_plan where hash_value=1067507827;
OPERATION
------------------------------------------------------------
SELECT STATEMENT
TABLE ACCESS
SQL> EXEC DBMS_STATS.gather_table_stats(user,'T_XIFENFEI');
PL/SQL procedure successfully completed.
SQL> select sql_text,HASH_VALUE from v$sqlAREA where sql_text like '%xifenfei';
SQL_TEXT                                           HASH_VALUE
-------------------------------------------------- ----------
select * from t_xifenfei                           1067507827
SQL> select OPERATION from v$sql_plan where hash_value=1067507827;
no rows selected
--analyze收集统计信息(不推荐)
SQL> alter system flush shared_pool;
System altered.
SQL> select * from t_xifenfei;
        ID NAME
---------- -------------------
         1 www.xifenfei.com
SQL> select sql_text,HASH_VALUE from v$sqlAREA where sql_text like '%xifenfei';
SQL_TEXT                                           HASH_VALUE
-------------------------------------------------- ----------
select * from t_xifenfei                           1067507827
SQL> select OPERATION from v$sql_plan where hash_value=1067507827;
OPERATION
------------------------------------------------------------
SELECT STATEMENT
TABLE ACCESS
SQL> analyze table  t_xifenfei compute statistics;
Table analyzed.
SQL> select sql_text,HASH_VALUE from v$sqlAREA where sql_text like '%xifenfei';
SQL_TEXT                                           HASH_VALUE
-------------------------------------------------- ----------
select * from t_xifenfei                           1067507827
SQL> select OPERATION from v$sql_plan where hash_value=1067507827;
no rows selected

清除执行计划3:创建INDEX

SQL> alter system flush shared_pool;
System altered.
SQL> select * from t_xifenfei;
        ID NAME
---------- -------------------
         1 www.xifenfei.com
SQL> select sql_text,HASH_VALUE from v$sqlAREA where sql_text like '%xifenfei';
SQL_TEXT                                           HASH_VALUE
-------------------------------------------------- ----------
select * from t_xifenfei                           1067507827
SQL>  select OPERATION from v$sql_plan where hash_value=1067507827;
OPERATION
------------------------------------------------------------
SELECT STATEMENT
TABLE ACCESS
SQL> create index i_txifenfei on t_xifenfei(id) online;
Index created.
SQL> drop index i_txifenfei ;
Index dropped.
SQL> select sql_text,HASH_VALUE from v$sqlAREA where sql_text like '%xifenfei';
SQL_TEXT                                           HASH_VALUE
-------------------------------------------------- ----------
select * from t_xifenfei                           1067507827
SQL> select OPERATION from v$sql_plan where hash_value=1067507827;
no rows selected

清除执行计划3:GRANT/REVOKE操作

SQL> alter system flush shared_pool;
System altered.
SQL> select * from t_xifenfei;
        ID NAME
---------- -------------------
         1 www.xifenfei.com
SQL> select sql_text,HASH_VALUE from v$sqlAREA where sql_text like '%xifenfei';
SQL_TEXT                                           HASH_VALUE
-------------------------------------------------- ----------
select * from t_xifenfei                           1067507827
SQL> select OPERATION from v$sql_plan where hash_value=1067507827;
OPERATION
------------------------------------------------------------
SELECT STATEMENT
TABLE ACCESS
SQL> GRANT SELECT ON T_XIFENFEI TO SYSTEM;
Grant succeeded.
SQL> select sql_text,HASH_VALUE from v$sqlAREA where sql_text like '%xifenfei';
SQL_TEXT                                           HASH_VALUE
-------------------------------------------------- ----------
select * from t_xifenfei                           1067507827
SQL> select OPERATION from v$sql_plan where hash_value=1067507827;
no rows selected

DB2备份恢复(不完全恢复)

全备数据库

[db2inst1@xifenfei ~]$ db2 backup db xff online to /tmp  include logs
Backup successful. The timestamp for this backup image is : 20120411213218
[db2inst1@xifenfei ~]$ db2 list history backup all for xff
                    List History File for xff
Number of matching file entries = 1
 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20120411213218001   N    D  S0000021.LOG S0000021.LOG
 ----------------------------------------------------------------------------
  Contains 3 tablespace(s):
 00001 SYSCATSPACE
 00002 USERSPACE1
 00003 SYSTOOLSPACE
 ----------------------------------------------------------------------------
    Comment: DB2 BACKUP XFF ONLINE
 Start Time: 20120411213218
   End Time: 20120411213229
     Status: A
 ----------------------------------------------------------------------------
  EID: 42 Location: /tmp

数据操作(包括误操作)

[db2inst1@xifenfei ~]$ db2 connect to xff
   Database Connection Information
 Database server        = DB2/LINUX 9.5.9
 SQL authorization ID   = DB2INST1
 Local database alias   = XFF
[db2inst1@xifenfei ~]$ db2 list tables
Table/View                      Schema          Type  Creation time
------------------------------- --------------- ----- --------------------------
T_01XFF                         DB2INST1        T     2012-04-11-18.23.05.723478
T_02XFF                         DB2INST1        T     2012-04-11-18.30.26.639326
  2 record(s) selected.
[db2inst1@xifenfei ~]$ db2 "create table t_03xff like t_01xff"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "insert into t_03xff select * from t_01xff"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ date
Wed Apr 11 21:33:42 CST 2012
[db2inst1@xifenfei ~]$ db2 "select count(*) from t_03xff"
1
-----------
        370
  1 record(s) selected.
--以下是错误操作,需要回滚
[db2inst1@xifenfei ~]$ date
Wed Apr 11 21:36:38 CST 2012
[db2inst1@xifenfei ~]$ db2 "insert into t_03xff select * from t_01xff"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "select count(*) from t_03xff"
1
-----------
        740
  1 record(s) selected.

还原数据库

db2inst1@xifenfei ~]$ db2 restore db xff from /tmp taken at 20120411213218
SQL2539W  Warning!  Restoring to an existing database that is the same as the
backup image database.  The database files will be deleted.
Do you want to continue ? (y/n) y
DB20000I  The RESTORE DATABASE command completed successfully.

恢复数据库

[db2inst1@xifenfei ~]$ db2 rollforward db xff to 2012-04-11-21.36.00.00000 using local time
                                 Rollforward Status
 Input database alias                   = xff
 Number of nodes have returned status   = 1
 Node number                            = 0
 Rollforward status                     = DB  working
 Next log file to be read               = S0000023.LOG
 Log files processed                    = S0000021.LOG - S0000021.LOG
 Last committed transaction             = 2012-04-11-21.33.27.000000 Local
DB20000I  The ROLLFORWARD command completed successfully.
[db2inst1@xifenfei ~]$ db2 connect to xff
SQL1117N  A connection to or activation of database "XIFENFEI" cannot be made
because of ROLL-FORWARD PENDING.  SQLSTATE=57019
--停止前滚
[db2inst1@xifenfei ~]$ db2 rollforward db xff stop
                                 Rollforward Status
 Input database alias                   = xff
 Number of nodes have returned status   = 1
 Node number                            = 0
 Rollforward status                     = not pending
 Next log file to be read               =
 Log files processed                    = S0000021.LOG - S0000022.LOG
 Last committed transaction             = 2012-04-11-21.33.27.000000 Local
DB20000I  The ROLLFORWARD command completed successfully.

验证数据

[db2inst1@xifenfei ~]$ db2 connect to xff
   Database Connection Information
 Database server        = DB2/LINUX 9.5.9
 SQL authorization ID   = DB2INST1
 Local database alias   = XFF
[db2inst1@xifenfei ~]$ db2 "select count(*) from t_03xff"
1
-----------
        370
  1 record(s) selected.

重建 Datapump Utility EXPDP/IMPDP

因为数据库内部错误,数据字典不一致等原因导致 DataPump不能被正常使用,这个时候可以尝试着通过重建 DataPump来解决问题
sysdba登录数据库

SQL> connect / as sysdba

For Oracle version 10.1

1. Catdp.sql orders the installation of all its components including
   the Metadata API which was previously installed separately.
   By default catproc.sql invoke this script.
SQL >@ $ORACLE_HOME/rdbms/admin/catdp.sql
2. dbmspump.sql will create DBMS procedures for dataPUMP
SQL >@ $ORACLE_HOME/rdbms/admin/dbmspump.sql

For Oracle version 10.2

1. Catdph.sql will Re-Install DataPump types and views
SQL >@ $ORACLE_HOME/rdbms/admin/catdph.sql
Use this code to verify if XDB is installed:
SQL> select substr(comp_name,1,30) comp_name, substr(comp_id,1,10)
     comp_id,substr(version,1,12) version,status
     from dba_registry where comp_id='XDB';
Sample output if XDB installed,
Oracle XML Database            XDB        -version-   VALID
Note: If XDB is installed, then it is required to run "catmetx.sql" script also.
SQL> @ $ORACLE_HOME/rdbms/admin/catmetx.sql
2. prvtdtde.plb will Re-Install tde_library packages
SQL >@ $ORACLE_HOME/rdbms/admin/prvtdtde.plb
3. Catdpb.sql will Re-Install DataPump packages
SQL >@ $ORACLE_HOME/rdbms/admin/catdpb.sql
4.Dbmspump.sql will Re-Install DBMS DataPump objects
SQL >@ $ORACLE_HOME/rdbms/admin/dbmspump.sql
5. To recompile  invalid objects, if any
SQL >@ $ORACLE_HOME/rdbms/admin/utlrp.sql

For Oracle version 11g

1. Catproc.sql
SQL >@ $ORACLE_HOME/rdbms/admin/catproc.sql
2. To recompile invalid objects, if any
SQL >@ $ORACLE_HOME/rdbms/admin/utlrp.sql

参考:How To Reload Datapump Utility EXPDP/IMPDP [ID 430221.1]

DB2备份恢复(增量备份与恢复)

全备数据库

[db2inst1@xifenfei ~]$ db2 backup db xff online to /tmp  include logs
Backup successful. The timestamp for this backup image is : 20120411181918
[db2inst1@xifenfei ~]$ db2 list history backup all for xff
                    List History File for xff
Number of matching file entries = 1
 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20120411181918001   N    D  S0000015.LOG S0000015.LOG
 ----------------------------------------------------------------------------
  Contains 3 tablespace(s):
 00001 SYSCATSPACE
 00002 USERSPACE1
 00003 SYSTOOLSPACE
 ----------------------------------------------------------------------------
    Comment: DB2 BACKUP XFF ONLINE
 Start Time: 20120411181918
   End Time: 20120411181925
     Status: A
 ----------------------------------------------------------------------------
  EID: 27 Location: /tmp

修改数据

[db2inst1@xifenfei ~]$ db2 list tables
Table/View                      Schema          Type  Creation time
------------------------------- --------------- ----- --------------------------
T_XFF                           DB2INST1        T     2012-04-05-09.45.29.148434
T_XIFENFEI                      DB2INST1        T     2012-04-06-05.50.11.111469
T_XIFENFEI01                    DB2INST1        T     2012-04-11-16.55.51.853649
  3 record(s) selected.
[db2inst1@xifenfei ~]$ db2 "drop table t_xff"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "drop table t_xifenfei"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "drop table t_xifenfei01"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "create table t_01xff like syscat.tables"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "insert into t_01xff select * from syscat.tables"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "select count(*) from t_01xff"
1
-----------
        370
  1 record(s) selected.
[db2inst1@xifenfei ~]$ db2 list tables
Table/View                      Schema          Type  Creation time
------------------------------- --------------- ----- --------------------------
T_01XFF                         DB2INST1        T     2012-04-11-18.23.05.723478
  1 record(s) selected.

增量备份SQL2426N解决

[db2inst1@xifenfei ~]$ db2 backup db xff online incremental to /tmp
SQL2426N  The database has not been configured to allow the incremental backup
operation. Reason code = "1".
[db2inst1@xifenfei ~]$ db2 ? SQL2426N
SQL2426N  The database has not been configured to allow the incremental
      backup operation. Reason code = "<reason-code>".
Explanation:
Incremental backups are not enabled for a table space until after
modification tracking has been activated for the database and a
non-incremental backup has been performed on the table space.
Possible reason codes:
1. The configuration parameter TRACKMOD has not been set for the
   database.
2. The TRACKMOD configuration parameter has been set but at least one
   table space has not had a non-incremental backup taken since the
   TRACKMOD parameter was set.
User response:
The action is based on the reason code as follows:
1. Activate modification tracking for the database by setting the
   TRACKMOD database configuration parameter to on, then perform a full
   database backup.
2. Consult the db2diag.log file to determine the name of the table
   space, then perform a full backup of that table space.
[db2inst1@xifenfei ~]$ db2 get db cfg for xff|grep TRACKMOD
 Track modified pages                         (TRACKMOD) = NO
[db2inst1@xifenfei ~]$ db2 update db cfg for xff using TRACKMOD ON
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
SQL1363W  One or more of the parameters submitted for immediate modification
were not changed dynamically. For these configuration parameters, the database
must be shutdown and reactivated before the configuration parameter changes
become effective.
[db2inst1@xifenfei ~]$ db2stop force
04/11/2012 17:49:59     0   0   SQL1064N  DB2STOP processing was successful.
SQL1064N  DB2STOP processing was successful.
[db2inst1@xifenfei ~]$ db2start
04/11/2012 17:50:09     0   0   SQL1063N  DB2START processing was successful.
SQL1063N  DB2START processing was successful.

累积增量备份

[db2inst1@xifenfei ~]$ db2 backup db xff online incremental to /tmp
Backup successful. The timestamp for this backup image is : 20120411182708
[db2inst1@xifenfei ~]$ db2 list history backup all for xff
                    List History File for xff
Number of matching file entries = 2
 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20120411181918001   N    D  S0000015.LOG S0000015.LOG
 ----------------------------------------------------------------------------
  Contains 3 tablespace(s):
 00001 SYSCATSPACE
 00002 USERSPACE1
 00003 SYSTOOLSPACE
 ----------------------------------------------------------------------------
    Comment: DB2 BACKUP XFF ONLINE
 Start Time: 20120411181918
   End Time: 20120411181925
     Status: A
 ----------------------------------------------------------------------------
  EID: 27 Location: /tmp
 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20120411182708001   O    D  S0000017.LOG S0000017.LOG
 ----------------------------------------------------------------------------
  Contains 3 tablespace(s):
 00001 SYSCATSPACE
 00002 USERSPACE1
 00003 SYSTOOLSPACE
 ----------------------------------------------------------------------------
    Comment: DB2 BACKUP XFF ONLINE
 Start Time: 20120411182708
   End Time: 20120411182712
     Status: A
 ----------------------------------------------------------------------------
  EID: 33 Location: /tmp

再次修改数据

[db2inst1@xifenfei ~]$ db2 "create table t_02xff like syscat.tables"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "insert into t_02xff select * from syscat.tables"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 list tables
Table/View                      Schema          Type  Creation time
------------------------------- --------------- ----- --------------------------
T_01XFF                         DB2INST1        T     2012-04-11-18.23.05.723478
T_02XFF                         DB2INST1        T     2012-04-11-18.30.26.639326
  2 record(s) selected.
[db2inst1@xifenfei ~]$ db2 "select count(*) from t_02xff"
1
-----------
        371
  1 record(s) selected.

迭代备份

[db2inst1@xifenfei ~]$ db2 backup db xff online incremental delta to /tmp
Backup successful. The timestamp for this backup image is : 20120411183129
[db2inst1@xifenfei ~]$ ll /tmp/XFF*
-rw-------  1 db2inst1 db2iadm1 122044416 Apr 11 18:19 /tmp/XFF.0.db2inst1.NODE0000.CATN0000.20120411181918.001
-rw-------  1 db2inst1 db2iadm1  55128064 Apr 11 18:27 /tmp/XFF.0.db2inst1.NODE0000.CATN0000.20120411182708.001
-rw-------  1 db2inst1 db2iadm1  55128064 Apr 11 18:31 /tmp/XFF.0.db2inst1.NODE0000.CATN0000.20120411183129.001
--这里可以看出最近一次的增量备份和迭代备份备份文件大小相同,说明迭代备份是在最近一次增量备份基础之上进行
[db2inst1@xifenfei ~]$ db2 list history backup all for xff
                    List History File for xff
Number of matching file entries = 3
 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20120411181918001   N    D  S0000015.LOG S0000015.LOG
 ----------------------------------------------------------------------------
  Contains 3 tablespace(s):
 00001 SYSCATSPACE
 00002 USERSPACE1
 00003 SYSTOOLSPACE
 ----------------------------------------------------------------------------
    Comment: DB2 BACKUP XFF ONLINE
 Start Time: 20120411181918
   End Time: 20120411181925
     Status: A
 ----------------------------------------------------------------------------
  EID: 27 Location: /tmp
 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20120411182708001   O    D  S0000017.LOG S0000017.LOG
 ----------------------------------------------------------------------------
  Contains 3 tablespace(s):
 00001 SYSCATSPACE
 00002 USERSPACE1
 00003 SYSTOOLSPACE
 ----------------------------------------------------------------------------
    Comment: DB2 BACKUP XFF ONLINE
 Start Time: 20120411182708
   End Time: 20120411182712
     Status: A
 ----------------------------------------------------------------------------
  EID: 33 Location: /tmp
 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20120411183129001   E    D  S0000019.LOG S0000019.LOG
 ----------------------------------------------------------------------------
  Contains 3 tablespace(s):
 00001 SYSCATSPACE
 00002 USERSPACE1
 00003 SYSTOOLSPACE
 ----------------------------------------------------------------------------
    Comment: DB2 BACKUP XFF ONLINE
 Start Time: 20120411183129
   End Time: 20120411183133
     Status: A
 ----------------------------------------------------------------------------
  EID: 36 Location: /tmp

还原数据库

[db2inst1@xifenfei ~]$ db2 restore db xff  incremental automatic from /tmp taken at 20120411183129
SQL2539W  Warning!  Restoring to an existing database that is the same as the
backup image database.  The database files will be deleted.
Do you want to continue ? (y/n) y
DB20000I  The RESTORE DATABASE command completed successfully.

尝试登陆数据库

[db2inst1@xifenfei ~]$ db2 connect to xff
SQL1117N  A connection to or activation of database "XIFENFEI" cannot be made
because of ROLL-FORWARD PENDING.  SQLSTATE=57019
--数据库需要前滚,因为是在线备份

恢复数据库

[db2inst1@xifenfei ~]$  db2 "rollforward db xff to end of logs and stop"
                                 Rollforward Status
 Input database alias                   = xff
 Number of nodes have returned status   = 1
 Node number                            = 0
 Rollforward status                     = not pending
 Next log file to be read               =
 Log files processed                    = S0000019.LOG - S0000019.LOG
 Last committed transaction             = 2012-04-11-10.31.30.000000 UTC
DB20000I  The ROLLFORWARD command completed successfully.

验证恢复过程

[db2inst1@xifenfei ~]$ db2 connect to xff
   Database Connection Information
 Database server        = DB2/LINUX 9.5.9
 SQL authorization ID   = DB2INST1
 Local database alias   = XFF
[db2inst1@xifenfei ~]$ db2 list tables
Table/View                      Schema          Type  Creation time
------------------------------- --------------- ----- --------------------------
T_01XFF                         DB2INST1        T     2012-04-11-18.23.05.723478
T_02XFF                         DB2INST1        T     2012-04-11-18.30.26.639326
  2 record(s) selected.
[db2inst1@xifenfei ~]$ db2 "select count(*) from t_01xff"
1
-----------
        370
  1 record(s) selected.
[db2inst1@xifenfei ~]$ db2 "select count(*) from t_02xff"
1
-----------
        371
  1 record(s) selected.

本篇主要测试了增量备份和数据库恢复,在下篇中将对不完全恢复进行测试,坚持逐步学习db2数据库相关知识

DB2备份恢复(全备与恢复)

日志模式

[db2inst1@xifenfei ~]$ db2 get db cfg for xff |grep -i log
 Log retain for recovery status                          = NO
 User exit for logging status                            = YES   --(1)
 Catalog cache size (4KB)              (CATALOGCACHE_SZ) = 260
 Log buffer size (4KB)                        (LOGBUFSZ) = 98
 Log file size (4KB)                         (LOGFILSIZ) = 1024
 Number of primary log files                (LOGPRIMARY) = 6
 Number of secondary log files               (LOGSECOND) = 4
 Changed path to log files                  (NEWLOGPATH) =
 Path to log files                                       = /home/db2inst1/xff/redolog/NODE0000/
 Overflow log path                     (OVERFLOWLOGPATH) =
 Mirror log path                         (MIRRORLOGPATH) =
 First active log file                                   = S0000013.LOG
 Block log on disk full                (BLK_LOG_DSK_FUL) = NO
 Block non logged operations            (BLOCKNONLOGGED) = NO
 Percent max primary log space by transaction  (MAX_LOG) = 0
 Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0
 Percent log file reclaimed before soft chckpt (SOFTMAX) = 520
 Log retain for recovery enabled             (LOGRETAIN) = OFF
 User exit for logging enabled                (USEREXIT) = OFF
 HADR log write synchronization mode     (HADR_SYNCMODE) = NEARSYNC
 First log archive method                 (LOGARCHMETH1) = DISK:/home/db2inst1/xff/archivelog/  --(2)
 Options for logarchmeth1                  (LOGARCHOPT1) =
 Second log archive method                (LOGARCHMETH2) = OFF
 Options for logarchmeth2                  (LOGARCHOPT2) =
 Failover log archive path                (FAILARCHPATH) =
 Number of log archive retries on error   (NUMARCHRETRY) = 5
 Log archive retry Delay (secs)         (ARCHRETRYDELAY) = 20
 Log pages during index build            (LOGINDEXBUILD) = OFF

由(1)和(2)可以判断该数据库处于归档日志模式下

查看当前存在备份

[db2inst1@xifenfei ~]$ db2 list history backup all for xff
                    List History File for xff
Number of matching file entries = 1
 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20120406053431001   F    D  S0000000.LOG S0000000.LOG
 ----------------------------------------------------------------------------
  Contains 2 tablespace(s):
 00001 SYSCATSPACE
 00002 USERSPACE1
 ----------------------------------------------------------------------------
    Comment: DB2 BACKUP XFF OFFLINE    --离线备份
 Start Time: 20120406053431
   End Time: 20120406053439
     Status: A
 ----------------------------------------------------------------------------
  EID: 1 Location: /tmp

在线全备

[db2inst1@xifenfei ~]$ db2 backup db xff online to /tmp  include logs
Backup successful. The timestamp for this backup image is : 20120411165312
[db2inst1@xifenfei ~]$ db2 list history backup all for xff
                    List History File for xff
Number of matching file entries = 2
 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20120406053431001   F    D  S0000000.LOG S0000000.LOG
 ----------------------------------------------------------------------------
  Contains 2 tablespace(s):
 00001 SYSCATSPACE
 00002 USERSPACE1
 ----------------------------------------------------------------------------
    Comment: DB2 BACKUP XFF OFFLINE  --本次试验的online备份
 Start Time: 20120406053431
   End Time: 20120406053439
     Status: A
 ----------------------------------------------------------------------------
  EID: 1 Location: /tmp
 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20120411165312001   N    D  S0000013.LOG S0000013.LOG
 ----------------------------------------------------------------------------
  Contains 3 tablespace(s):
 00001 SYSCATSPACE
 00002 USERSPACE1
 00003 SYSTOOLSPACE
 ----------------------------------------------------------------------------
    Comment: DB2 BACKUP XFF ONLINE
 Start Time: 20120411165312
   End Time: 20120411165322
     Status: A
 ----------------------------------------------------------------------------
  EID: 19 Location: /tmp

当前数据库当前数据

[db2inst1@xifenfei ~]$ db2 connect to xff
   Database Connection Information
 Database server        = DB2/LINUX 9.5.9
 SQL authorization ID   = DB2INST1
 Local database alias   = XFF
[db2inst1@xifenfei ~]$ db2 list tables
Table/View                      Schema          Type  Creation time
------------------------------- --------------- ----- --------------------------
T_XFF                           DB2INST1        T     2012-04-05-09.45.29.148434
T_XIFENFEI                      DB2INST1        T     2012-04-06-05.50.11.111469
  2 record(s) selected.
[db2inst1@xifenfei ~]$ db2 "create table t_xifenfei01 like t_xff"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "insert into t_xifenfei01
> select * from t_xff"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "insert into t_xifenfei01
select * from t_xff"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "select count(*) from t_xifenfei01"
1
-----------
        734
  1 record(s) selected.

恢复数据库

[db2inst1@xifenfei ~]$ db2 restore db xff from /tmp taken at 20120411165312
SQL2539W  Warning!  Restoring to an existing database that is the same as the
backup image database.  The database files will be deleted.
Do you want to continue ? (y/n) y
DB20000I  The RESTORE DATABASE command completed successfully.
[db2inst1@xifenfei ~]$ db2 "rollforward db xff to end of logs and stop"
                                 Rollforward Status
 Input database alias                   = xff
 Number of nodes have returned status   = 1
 Node number                            = 0
 Rollforward status                     = not pending
 Next log file to be read               =
 Log files processed                    = S0000013.LOG - S0000014.LOG
 Last committed transaction             = 2012-04-11-08.56.20.000000 UTC
DB20000I  The ROLLFORWARD command completed successfully.
[db2inst1@xifenfei ~]$ db connect to xff
-bash: db: command not found
[db2inst1@xifenfei ~]$ db2 connect to xff
   Database Connection Information
 Database server        = DB2/LINUX 9.5.9
 SQL authorization ID   = DB2INST1
 Local database alias   = XFF
[db2inst1@xifenfei ~]$ db2 list tables
Table/View                      Schema          Type  Creation time
------------------------------- --------------- ----- --------------------------
T_XFF                           DB2INST1        T     2012-04-05-09.45.29.148434
T_XIFENFEI                      DB2INST1        T     2012-04-06-05.50.11.111469
T_XIFENFEI01                    DB2INST1        T     2012-04-11-16.55.51.853649
  3 record(s) selected.
[db2inst1@xifenfei ~]$ db2 "select count(*) from t_xifenfei01"
1
-----------
        734
  1 record(s) selected.

备份恢复是dba最重要的职责,本篇做为db2学习过程中第一篇关于备份恢复文章,后续将继续学习db2增量备份恢复等知识.