Flashback Database

在Flashback Database中创建基于guarantee的restore point,是可以进行快速备份和恢复的方法。这对于RAT中的播放之后的数据库恢复特别有用!

下面的notes详细描述了创建和恢复restore的过程:

Restore point: 

Restore point is nothing but a name associated with a timestamp or an SCN of the database. One can create either a normal restore point or a guaranteed restore point. The difference between the two is that guaranteed restore point allows you to flashback to the restore point regardless of the DB_FLASHBACK_RETENTION_TARGET initialization parameter i.e. it is always available (assuming you have enough space in the flash recovery area).

NOTE: In this article Flashback logging was not turned ON.

Guaranteed Restore point:

Prerequisites: Creating a guaranteed restore point requires the following prerequisites:

The user must have the SYSDBA system privileges
Must have created a flash recovery area
The database must be in ARCHIVELOG mode
Create a guaranteed restore point:
After you have created or migrated a fresh database, first thing to do is to create a guaranteed restore point so you can flashback to it each time before you start a new workload. The steps are as under:

$> su – oracle
$> sqlplus / as sysdba;

Find out if ARCHIVELOG is enabled

SQL> select log_mode from v$database;

If step 3 shows that ARCHIVELOG is not enabled then continue else skip to step 8 below.

SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;
SQL> create restore point CLEAN_DB guarantee flashback database;

where CLEAN_DB is the name given to the guaranteed restore point.
Viewing the guaranteed restore point

SQL> select * from v$restore_point;

Verify the information about the newly created restore point. Also, note down the SCN# for reference and we will refer to it as “reference SCN#”

Flashback to the guaranteed restore point
Now, in order to restore your database to the guaranteed restore point, follow the steps below:

$> su – oracle
$> sqlplus / as sysdba;
SQL> select current_scn from v$database;
SQL> shutdown immediate;
SQL> startup mount;
SQL> select * from v$restore_point;
SQL> flashback database to restore point CLEAN_DB;
SQL> alter database open resetlogs;
SQL> select current_scn from v$database;

Compare the SCN# from step 9 above to the reference SCN#.

NOTE: The SCN# from step 9 above may not necessarily be the exact SCN# as the reference SCN# but it will be close enough.

 

Posted in 生活点滴 | Tagged | Leave a comment

SPA使用详解

这里的SPA不是说美容,而是Oracle的SQL性能优化分析器,SQL Performance Analyzer。说到SPA就不得不提Oracle关于SQL优化的几个常见的术语,SQL Tuning Advisor, SQL Tuning Set,SQL Performance Analyzer。

STS=SQL Tuning Set,SQL调优集,是一系列Oracle SQL的集合,这些SQL可以从AWR或者cursor cache中获得。可以使用DBMS_SQLTUNE包中SQL Tuning Set相关的函数和过程进行操作。

STA=SQL Tuning Advisor,SQL优化顾问,是针对SQL调优集或者SPA甚至单纯的SQL进行优化。可以使用DBMS_SQLTUNE包中SQL Tuning Advisor相关的函数和过程进行操作。

SPA=SQL Performance Analyzer,SQL性能分析器。针对数据库变更进行的性能分析。变更包括数据库升级,平台/版本/架构变更,参数调整,统计信息收集等等。SPA其实也是Oracle Real Application Testing的一个组件,另外一个组件就是前面介绍过的Database Replay。SPA的原理是通过变更前对收集的STS进行执行,获取基线数据,变更后再次进行执行,然后进行对比,从多个维度比如CPU时间,I/O,buffer get等生成详细的变更对比报告。

典型用法是捕捉STS,然后通过SPA生成对比报告,然后对变更后衰减的SQL调用STA进行优化。

下面是一个SPA的使用详解,其中的输入就是从AWR两个快照之间取的STS:


--Drop STS:
SQL> exec DBMS_SQLTUNE.DROP_SQLSET(sqlset_name => 'my_sts'); 

--Create STS:
SQL> exec DBMS_SQLTUNE.CREATE_SQLSET(sqlset_name => 'my_sts',  description => 'SQL Tuning Set for loading plan into SQL Plan Baseline'); 

--Load STS:

SQL> DECLARE 
  cur sys_refcursor;  
BEGIN 
  OPEN cur FOR 
     SELECT VALUE(P)  
     FROM TABLE(  
        dbms_sqltune.select_workload_repository(begin_snap=>257, end_snap=>258,basic_filter=>'sql_id = ''1bq7qgz7p6x46''',attribute_list=>'ALL')  
               ) p;  
     DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name=> 'my_sts', populate_cursor=>cur);  
   CLOSE cur;  
END; 
/

--Create SPA TASK

SQL> declare v_spa_name varchar2(100);
  2  begin
  3  v_spa_name:=dbms_sqlpa.create_analysis_task(
  4  sqlset_name=>'my_sts',
  5  task_name=>'my_spa_task');
  6  dbms_output.put_line('spa_name='||v_spa_name);
  7  end;
  8  /
spa_name=my_spa_task

PL/SQL procedure successfully completed.

--Execute SPA TASK before change

SQL> exec dbms_sqlpa.execute_analysis_task(task_name=>'my_spa_task',execution_type=>'test execute', execution_name=>'my_exec_before_change');

PL/SQL procedure successfully completed.


--make change now....

SQL> select index_name from dba_indexes where table_name='MY_OBJECTS';

INDEX_NAME
------------------------------
MY_OBJECTS_INDEX_01

SQL> drop index spa_test_user.MY_OBJECTS_INDEX_01;

Index dropped.

--Execute SPA TASK after change

SQL> exec dbms_sqlpa.execute_analysis_task(task_name=>'my_spa_task',execution_type=>'test execute', execution_name=>'my_exec_after_change');

PL/SQL procedure successfully completed.

--Get report or compare report

SQL> var rep clob;
SQL> exec :rep:=dbms_sqlpa.report_analysis_task('my_spa_task','text','typical','summary');

PL/SQL procedure successfully completed.

SQL> set long 100000 longchunksize 100000 linesize 130
SQL> print :rep

REP
----------------------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name		  : my_spa_task
Tuning Task Owner		  : SYS
Workload Type			  : SQL Tuning Set
Execution Count 		  : 2
Current Execution		  : my_exec_after_change
Execution Type			  : TEST EXECUTE
Scope				  : COMPREHENSIVE
Completion Status		  : COMPLETED
Started at			  : 03/14/2013 14:58:00
Completed at			  : 03/14/2013 14:58:08
SQL Tuning Set (STS) Name	  : my_sts
SQL Tuning Set Owner		  : SYS
Number of Statements in the STS   : 79
Number of SQLs Analyzed 	  : 79
Number of SQLs in the Report	  : 79
Number of SQLs with Findings	  : 1
Number of SQLs with Errors	  : 5

-------------------------------------------------------------------------------
SUMMARY SECTION
-------------------------------------------------------------------------------
		     SQL Statements Ordered by Elapsed Time
-------------------------------------------------------------------------------
			 Parse	  Elapsed    CPU     Buffer  Optimizer
object ID  SQL ID	 Time (s) Time (s) Time (s)   Gets     Cost
---------- ------------- -------- -------- -------- -------- ---------
	86 1bx8mgs8by25x .005597  .157076  .108483     3214	  144
       147 dayq182sk41ks .000288  .143791  .089486	  0	    1
       137 bm2pwrpcr8ru6 .000155  .143363  .093819	  0	    1
       139 bunssq950snhf .000356  .142137  .091986	  0	    1
	87 1cq3qr774cu45 .001988  .036542  .003221	  0	    5
       107 5dfmd823r8dsp .002836  .030445  .019774	  0	    2
	97 350myuyx0t1d6 .006113  .008463  .000999	 12	    3
       152 f0s0bk5k713yb .000248  .005393  .003666	  0	    2
       119 7sxujr2u5ddy4 .000271  .004132  .000666	  0	    1
	91 1sd9s6z4gs4c5 .000135  .003598  .002444	816	  229
	96 32mk33ry1g665  .00201  .003385  .000333	  8	    2
       133 ab9rc63zxa4bj .062683  .003121  .001222	205	   13
       116 6xpsr8v27pmy2 .001445  .002782  .001888	  0	    1
       146 d0hs62mdytbk1 .004535  .002498  .001777	 32	   16
       121 7xshr7maxf6rc .004096  .002443  .001777	242	   13
       141 by4un3fmx698z .010119  .002443  .001666	 62	   13
       127 95jvkhg3t7rqj .163061   .00239  .001666	184	   23
       114 6m6b8kbv1dt4c  .02843  .002303  .001666	188	   50
       157 gcpj8av4kx6j2 .011175  .002274  .001666	 62	   13
	89 1nay5fd0d17km .009615  .002213  .001444	 62	   13
       100 4002tnht9dwhw .012735  .002199  .001444	 62	   13
       113 6d1jnnuhkt8wy .035774  .002051  .001333	214	   29
       104 4ku91umkgpmmr .027785  .001845   .00111	188	   47
       132 9vqb56yc6w899 .012859  .001827  .000777	  7	   13
       123 86v8m3jqnpxmu .012963   .00152  .001222	 12	   14
       135 b5576mqw15vum .023645  .001499  .001222	 12	   14
	83 0n1919jrgz485 .036324  .001243  .000666	226	   17
       150 dnwpm0gdccrph .000486  .000846  .000444	  0	    1
       149 ddnxm4tfc08nm .000813  .000633  .000333	 10	    8
	90 1rbz9nrbs8wyg .031264  .000401  .000222	 56	   21
       122 7yjzq240hcd2c .006697  .000209  .000222	  6	  703
       134 aca4xvmz0rzup  .00341  .000199	 0	  1	    1
       115 6wm3n4d7bnddg .019572   .00019  .000111	 16	   27
       158 gdfb17u17m2k2 .016272  .000187  .000222	 56	   19
       138 bq584rmaa85ua  .00917  .000156  .000222	 62	   19
       102 43wq1yur8h8u2 .000796  .000132	 0	  4	    3
       101 40mgxk6ty822a .001024  .000115  .000111	 11	    5
       109 5km6gkvuh3n43 .000391  .000103  .000111	  2	    1
       140 bwsz40d3hc8q7 .000498  .000103	 0	  0	    0
       128 971vpk0tp7ahm .000176  .000093	 0	  2	    3
       111 5n1fs4m2n2y0r .000199  .000089  .000111	  3	    2
	84 12hhcr6ucw6mb .004583  .000076  .000111	  8	    7
       118 7ntngwmf9bf4p  .01232   .00007  .000111	 16	   12
       129 9n8xc314xdm0t .000665  .000061	 0	  0	    4
	99 3ktacv9r56b51 .000519  .000052	 0	  7	   10
       148 dcstr36r0vz0d  .00067  .000046	 0	  3	    4
       117 7ng34ruy5awxq  .00687  .000044	 0	  7	    5
       142 c6awqs517jpj0 .000071  .000042  .000111	  2	    3
       112 69k5bhm12sz98 .000619  .000039	 0	  6	    3
       126 8wutkrpf8j81q .000162  .000039  .000111	  2	    2
       108 5j4c2v06qdhqq .000549  .000036	 0	  2	    2
	88 1gu8t96d0bdmu .000046  .000035	 0	  4	    2
       105 53saa2zkr6wc3 .000243  .000032	 0	  3	    3
	95 32hbap2vtmf53 .000445  .000031	 0	  4	    3
       124 87gaftwrm2h68 .000416  .000029	 0	  3	    3
       130 9q5jas50b88ud .006783  .000026	 0	  3	    3
       103 46zsasxjf8hb9 .000157  .000025  .000111	  0	    0
       131 9tgj4g8y4rwy8 .000233  .000025	 0	  3	    2
       151 dzan2tgvtkzdw .009243  .000025	 0	  3	    2
	93 2xgubd6ayhyb1 .000061  .000024	 0	  2	    2
       125 8swypbbr0m372 .000319  .000024	 0	  2	    3
       154 fa0uzvspp236d .006965  .000024	 0	  1	   27
       159 gj6uc50hpnr1p .000297  .000023	 0	  0	    1
       156 ga9j9xk5cy9s0 .000461  .000021	 0	  2	    3
       144 cqqwzhmfk5acd .000064   .00002	 0	  1	    0
       145 cvn54b7yz0s8u .000282   .00002	 0	  2	    3
	98 39m4sx9k63ba2 .000262  .000019	 0	  2	    3
       136 bgjhtnqhr5u9h .000269  .000019	 0	  2	    3
       155 g3wrkmxkxzhf2 .000679  .000019	 0	  3	    2
	94 317v5hnvvd49h .013503  .000018	 0	  0	   74
       110 5ms1dhxbadq64 .001107  .000018	 0	  1	    1
       161 grwydz59pu6mc .000122  .000013	 0	  0	    1
       153 f0wj261bm8snd .000329  .000012	 0	  1	    1

-------------------------------------------------------------------------------
			     Statements with Errors
-------------------------------------------------------------------------------
object ID  SQL ID	 Error
---------- ------------- ------------------------------------------------------
	92 2tv1pqg3v08aa ORA-00932: inconsistent datatypes: expected UDT got...
       106 572fbaj0fdw2b ORA-20020: Database/Instance / does not exist in DB...
       120 7u3jnmmaw8gg1 ORA-00932: inconsistent datatypes: expected UDT got...
       143 cj86nch2p483y ORA-00932: inconsistent datatypes: expected UDT got...
       160 gman4yn14jyfz ORA-00932: inconsistent datatypes: expected UDT got...

-------------------------------------..." - rest of line ignored.-------------
SQL> 

SQL> exec dbms_sqlpa.execute_analysis_task(task_name=>'my_spa_task',execution_type=>'compare performance', execution_name=>'compare');

PL/SQL procedure successfully completed.

SQL> var rep clob;
SQL> exec :rep:=dbms_sqlpa.report_analysis_task('my_spa_task','text','typical','summary');

PL/SQL procedure successfully completed.

SQL> print :rep

REP
----------------------------------------------------------------------------------------------------------------------------------
General Information
---------------------------------------------------------------------------------------------

 Task Information:				Workload Information:
 ---------------------------------------------	---------------------------------------------
  Task Name    : my_spa_task			 SQL Tuning Set Name	    : my_sts
  Task Owner   : SYS				 SQL Tuning Set Owner	    : SYS
  Description  :				 Total SQL Statement Count  : 79

Execution Information:
---------------------------------------------------------------------------------------------
  Execution Name	     : compare		      Started		  : 03/14/2013 15:00:59
  Execution Type	     : COMPARE PERFORMANCE    Last Updated	  : 03/14/2013 15:01:00
  Description		     :			      Global Time Limit   : UNLIMITED
  Scope 		     : COMPREHENSIVE	      Per-SQL Time Limit  : UNUSED
  Status		     : COMPLETED	      Number of Errors	  : 5
  Number of Unsupported SQL  : 1

Analysis Information:
---------------------------------------------------------------------------------------------
 Before Change Execution:			After Change Execution:
 ---------------------------------------------	---------------------------------------------
  Execution Name      : my_exec_before_change	 Execution Name      : my_exec_after_change
  Execution Type      : TEST EXECUTE		 Execution Type      : TEST EXECUTE
  Scope 	      : COMPREHENSIVE		 Scope		     : COMPREHENSIVE
  Status	      : COMPLETED		 Status 	     : COMPLETED
  Started	      : 03/14/2013 14:54:39	 Started	     : 03/14/2013 14:58:00
  Last Updated	      : 03/14/2013 14:54:47	 Last Updated	     : 03/14/2013 14:58:08
  Global Time Limit   : UNLIMITED		 Global Time Limit   : UNLIMITED
  Per-SQL Time Limit  : UNUSED			 Per-SQL Time Limit  : UNUSED
  Number of Errors    : 5			 Number of Errors    : 5

 ---------------------------------------------
 Comparison Metric: ELAPSED_TIME
 ------------------
 Workload Impact Threshold: 1%
 --------------------------
 SQL Impact Threshold: 1%
 ----------------------

Report Summary
---------------------------------------------------------------------------------------------

Projected Workload Change Impact:
-------------------------------------------
 Overall Impact      :	-1.49%
 Improvement Impact  :	0%
 Regression Impact   :	-1.49%

SQL Statement Count
-------------------------------------------
 SQL Category  SQL Count  Plan Change Count
 Overall	      79		  1
 Regressed	       1		  0
 Unchanged	      72		  1
 with Errors	       5		  0
 Unsupported	       1		  0

Top 73 SQL Sorted by Absolute Value of Change Impact on the Workload
---------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------
|	    |		    | Impact on | Execution | Metric | Metric | Impact	 | Plan   |
| object_id | sql_id	    | Workload	| Frequency | Before | After  | on SQL	 | Change |
-------------------------------------------------------------------------------------------
|	166 | 1cq3qr774cu45 |	 -1.49% |	  1 |  25755 |	36542 |  -41.88% | n	  |
|	226 | dayq182sk41ks |	  -.71% |	  1 | 138657 | 143791 |    -3.7% | n	  |
|	204 | 8swypbbr0m372 |	   .65% |	 72 |	  89 |	   24 |   73.03% | n	  |
|	176 | 350myuyx0t1d6 |	   .58% |	  1 |  12668 |	 8463 |   33.19% | n	  |
|	216 | bm2pwrpcr8ru6 |	   .41% |	  1 | 146357 | 143363 |    2.05% | n	  |
|	165 | 1bx8mgs8by25x |	  -.36% |	  1 | 154450 | 157076 |    -1.7% | n	  |
|	178 | 3ktacv9r56b51 |	   .31% |	 72 |	  83 |	   52 |   37.35% | n	  |
|	186 | 5dfmd823r8dsp |	  -.26% |	  1 |  28546 |	30445 |   -6.65% | n	  |
|	218 | bunssq950snhf |	  -.21% |	  1 | 140601 | 142137 |   -1.09% | n	  |
|	212 | ab9rc63zxa4bj |	  -.21% |	  1 |	1613 |	 3121 |  -93.49% | n	  |
|	240 | grwydz59pu6mc |	  -.18% |	639 |	  11 |	   13 |  -18.18% | n	  |
|	211 | 9vqb56yc6w899 |	  -.17% |	  1 |	 620 |	 1827 | -194.68% | n	  |
|	198 | 7sxujr2u5ddy4 |	   -.1% |	  1 |	3416 |	 4132 |  -20.96% | n	  |
|	174 | 32hbap2vtmf53 |	  -.09% |	 66 |	  21 |	   31 |  -47.62% | n	  |
|	170 | 1sd9s6z4gs4c5 |	  -.08% |	  1 |	2995 |	 3598 |  -20.13% | y	  |
|	231 | f0s0bk5k713yb |	  -.06% |	  1 |	4960 |	 5393 |   -8.73% | n	  |
|	184 | 53saa2zkr6wc3 |	   .06% |	 36 |	  44 |	   32 |   27.27% | n	  |
|	190 | 5n1fs4m2n2y0r |	  -.05% |	  6 |	  23 |	   89 | -286.96% | n	  |
|	229 | dnwpm0gdccrph |	  -.05% |	  1 |	 514 |	  846 |  -64.59% | n	  |
|	188 | 5km6gkvuh3n43 |	  -.04% |	  4 |	  23 |	  103 | -347.83% | n	  |
|	207 | 971vpk0tp7ahm |	  -.04% |	  4 |	  18 |	   93 | -416.67% | n	  |
|	213 | aca4xvmz0rzup |	  -.04% |	  2 |	  54 |	  199 | -268.52% | n	  |
|	225 | d0hs62mdytbk1 |	  -.04% |	  1 |	2229 |	 2498 |  -12.07% | n	  |
|	200 | 7xshr7maxf6rc |	   .03% |	  1 |	2665 |	 2443 |    8.33% | n	  |
|	192 | 6d1jnnuhkt8wy |	   .03% |	  1 |	2240 |	 2051 |    8.44% | n	  |
|	179 | 4002tnht9dwhw |	  -.02% |	  1 |	2031 |	 2199 |   -8.27% | n	  |
|	214 | b5576mqw15vum |	  -.02% |	  1 |	1346 |	 1499 |  -11.37% | n	  |
|	193 | 6m6b8kbv1dt4c |	  -.02% |	  1 |	2163 |	 2303 |   -6.47% | n	  |
|	220 | by4un3fmx698z |	   .02% |	  1 |	2571 |	 2443 |    4.98% | n	  |
|	196 | 7ng34ruy5awxq |	   .02% |	  6 |	  64 |	   44 |   31.25% | n	  |
|	201 | 7yjzq240hcd2c |	  -.02% |	  1 |	  92 |	  209 | -127.17% | n	  |
|	169 | 1rbz9nrbs8wyg |	  -.02% |	  1 |	 287 |	  401 |  -39.72% | n	  |
|	162 | 0n1919jrgz485 |	   .01% |	  1 |	1350 |	 1243 |    7.93% | n	  |
|	221 | c6awqs517jpj0 |	  -.01% |	  4 |	  19 |	   42 | -121.05% | n	  |
|	195 | 6xpsr8v27pmy2 |	   .01% |	  1 |	2873 |	 2782 |    3.17% | n	  |
|	181 | 43wq1yur8h8u2 |	  -.01% |	  1 |	  50 |	  132 |    -164% | n	  |
|	206 | 95jvkhg3t7rqj |	   .01% |	  1 |	2465 |	 2390 |    3.04% | n	  |
|	228 | ddnxm4tfc08nm |	  -.01% |	  1 |	 559 |	  633 |  -13.24% | n	  |
|	227 | dcstr36r0vz0d |	   .01% |	  4 |	  64 |	   46 |   28.13% | n	  |
|	194 | 6wm3n4d7bnddg |	  -.01% |	  1 |	 121 |	  190 |  -57.02% | n	  |
|	219 | bwsz40d3hc8q7 |	   .01% |	  1 |	 172 |	  103 |   40.12% | n	  |
|	168 | 1nay5fd0d17km |	  -.01% |	  1 |	2146 |	 2213 |   -3.12% | n	  |
|	234 | g3wrkmxkxzhf2 |	   .01% |	 67 |	  20 |	   19 |       5% | n	  |
|	175 | 32mk33ry1g665 |	  -.01% |	  1 |	3321 |	 3385 |   -1.93% | n	  |
|	205 | 8wutkrpf8j81q |	  -.01% |	  4 |	  23 |	   39 |  -69.57% | n	  |
|	237 | gdfb17u17m2k2 |	  -.01% |	  1 |	 130 |	  187 |  -43.85% | n	  |
|	203 | 87gaftwrm2h68 |	  -.01% |	  8 |	  22 |	   29 |  -31.82% | n	  |
|	230 | dzan2tgvtkzdw |	   .01% |	  1 |	  81 |	   25 |   69.14% | n	  |
|	167 | 1gu8t96d0bdmu |	  -.01% |	  6 |	  26 |	   35 |  -34.62% | n	  |
|	189 | 5ms1dhxbadq64 |	   .01% |	  1 |	  72 |	   18 |      75% | n	  |
|	187 | 5j4c2v06qdhqq |	   .01% |	  4 |	  49 |	   36 |   26.53% | n	  |
|	236 | gcpj8av4kx6j2 |	   .01% |	  1 |	2322 |	 2274 |    2.07% | n	  |
|	180 | 40mgxk6ty822a |	   .01% |	  1 |	 156 |	  115 |   26.28% | n	  |
|	217 | bq584rmaa85ua |	     0% |	  1 |	 121 |	  156 |  -28.93% | n	  |
|	209 | 9q5jas50b88ud |	     0% |	  1 |	  50 |	   26 |      48% | n	  |
|	197 | 7ntngwmf9bf4p |	     0% |	  1 |	  93 |	   70 |   24.73% | n	  |
|	173 | 317v5hnvvd49h |	     0% |	  1 |	  39 |	   18 |   53.85% | n	  |
|	191 | 69k5bhm12sz98 |	     0% |	  2 |	  48 |	   39 |   18.75% | n	  |
|	215 | bgjhtnqhr5u9h |	     0% |	  4 |	  22 |	   19 |   13.64% | n	  |
|	183 | 4ku91umkgpmmr |	     0% |	  1 |	1856 |	 1845 |     .59% | n	  |
|	235 | ga9j9xk5cy9s0 |	     0% |	  4 |	  19 |	   21 |  -10.53% | n	  |
|	208 | 9n8xc314xdm0t |	     0% |	  1 |	  67 |	   61 |    8.96% | n	  |
|	210 | 9tgj4g8y4rwy8 |	     0% |	  6 |	  24 |	   25 |   -4.17% | n	  |
|	232 | f0wj261bm8snd |	     0% |	  2 |	  15 |	   12 |      20% | n	  |
|	202 | 86v8m3jqnpxmu |	     0% |	  1 |	1515 |	 1520 |    -.33% | n	  |
|	163 | 12hhcr6ucw6mb |	     0% |	  1 |	  72 |	   76 |   -5.56% | n	  |
|	172 | 2xgubd6ayhyb1 |	     0% |	  4 |	  23 |	   24 |   -4.35% | n	  |
|	177 | 39m4sx9k63ba2 |	     0% |	  4 |	  18 |	   19 |   -5.56% | n	  |
|	224 | cvn54b7yz0s8u |	     0% |	  4 |	  19 |	   20 |   -5.26% | n	  |
|	182 | 46zsasxjf8hb9 |	     0% |	  1 |	  22 |	   25 |  -13.64% | n	  |
|	223 | cqqwzhmfk5acd |	     0% |	  1 |	  18 |	   20 |  -11.11% | n	  |
|	238 | gj6uc50hpnr1p |	     0% |	  1 |	  24 |	   23 |    4.17% | n	  |
|	233 | fa0uzvspp236d |	     0% |	  1 |	  24 |	   24 |       0% | n	  |
-------------------------------------------------------------------------------------------
Note: time statistics are displayed in microseconds
---------------------------------------------------------------------------------------------

Posted in 数据库 | Tagged , | Leave a comment

Java Developer | Beijing

尽管北京的空气质量可能是全国最差的,但是毫无疑问,对于IT这个行业来说,北京也是机会最多的地区之一。

受朋友之托,发一个职位,有意者请直接邮件给负责人(可以说在RickyZhu的blog看到的,^_^)

Java Developer – Common Platform Storage (Beijing) (Design, PL/SQL and app tuning)

RESPONSIBILITIES:

Work with a global team of developers and business professionals to:

Design and develop next generation large scale data storage system within Service Oriented Architecture framework.
Conduct research and prototyping alternative technologies that will improve the storage system.
Complete programming tasks assigned and corresponding functional/unit tests.
Maintain, optimize existing system for performance and stability.

REQUIREMENTS:

4-7 years software development experience using JAVA language.
Strong knowledge on java concurrency programming and performance tuning.
Experience working with large scale Oracle DB based application.
Strong knowledge on PL SQL, schema design and Oracle DB performance tuning.
Familiar with advanced Oracle Database concepts.
Ability to work well in a team environment.
Full software lifecycle experience
Bachelors degree in Computer Science, Engineering, or related field

DESIRED EXPERIENCE AND QUALIFICATIONS:

Strong programming skills in Java
Multi-tiered distributed architecture frameworks
Multithreaded, scalable design and development
Large-scale software development experience in a team environment
J2EE Application Server Technologies
SOAP, Web Services, XML schema/dtd design, XML< ->java binding
Relational databases, particularly SQL-Server and
Quick learner and good problem solver.

Posted in 求职招聘 | Tagged , | Leave a comment

打开Oracle 10g的Real Application Testing功能

Oracle 自11gR1开始提供了一个极好的feature– Real Application Testing,又叫做数据库回放。可以通过内置于数据库中的捕捉功能从数据库层面把所有的业务操作录制下来,并拿到适当的环境进行回放。这个对于想要模拟实际生产环境的负载来说,实在是非常有用的特性。传统的在测试环境要想模拟生产负载一般通过挑选典型场景,使用测试工具(如LoadRunner或者JMeter)或者编码的形式准备好测试脚本,然后进行加压,耗时长而且误差不可估计。 RAT因为是内置于数据库本身,所有就具有与生俱来的优势了。

除此之外,对于数据库的升级,软硬件架构变化等等各种变化可能对数据库带来的影响,RAT也是极好的一个衡量手段。在老的系统上录制下来的工作负载,拿到变更后的环境进行回放,就可以很好的看出变更带来的差异,性能的提升或者衰减。

之前介绍过RAT的使用方法,今天主要介绍如何在老版本上打开RAT功能。

10.2.0.4之前的版本,需要打一个补丁,然后再运行必须的程序包进行激活。10.2.0.4之后的版本,因为本身已经具备了RAT功能,所以只需要修改一个数据库参数即可。(之所以这个参数从10.2.0.4开始才有是因为11gR1发布的时候10.2.0.4尚未发布,所以这个feature就被backport进去了)。

10.2.0.2或者更老的版本有一个特殊之处是补丁打完,捕捉的时候还会报错,现象就是:

ERROR at line 1:
ORA-04063: package body “SYS.DBMS_WORKLOAD_CAPTURE” has errors
ORA-06508: PL/SQL: could not find program unit being called:
“SYS.DBMS_WORKLOAD_CAPTURE”
ORA-06512: at line 1

这个问题在bug#9094202中描述,这个bug可以通过替换之前版本的prvtawr.plb文件进行绕过。这个问题在10.2.0.3修复了。

P.S. 还有不到10个小时2012年就要结束了,今年太懒了,总共不到10篇博文,来年得好好反省反省。顺便祝本站的新老读者朋友新年开心!
BTW:玛雅人的预测太不靠谱了!他们预言的世界末日那一刻,我刚好在客户现场解决刚才的这个bug,等到搞定之后,旁边的哥们告诉我,世界末日已经过了,我立时有种劫后余生的感觉!!

Posted in 数据库 | Tagged , | Leave a comment

分区索引的重新rebuild

数据仓库的场景中,在进行大数据量导入之前,通常的做法是先不建索引,因为这个过程中如果要维护索引,会大大降低导入的性能。那么如果在环境下索引已经建好而且不能删除的情况下,有什么好的办法可以绕过去?
在Oracle 11g中提供了不可用索引和不可视索引,今天主要介绍不可用索引,通过如下的SQL可以暂时使索引不可视:

alter index “index name” unusable;

这样在使用之前,只需要在会话级别设置
alter session set skip_unusable_indexes=true
即可。

在使用完成之后,可以通过如下命令
alter index “index name” rebuild tablespace “tbs name”;
在把索引的状态进行恢复。

但是对于分区索引,这个时候就会报错,因为对于分区索引来说,不能通过简单的一句话就把所有的分区都重建,还必须要指定具体的分区来逐一进行重建。索引的分区就是表对应的分区,从视图dba_tab_partitions可以查到表的分区,然后通过如下的命令:

alter index “index name” rebuild partition “partition name” tablespace “tbs name”;

即可。

EOF

PS:使用了代码插件,没想到”“居然被当成了代码,没显示出来。

Posted in 数据库 | Tagged | Leave a comment

ODI connect DB2

今天下午花了老鼻子劲把Oracle Data Integrator (ODI)连接DB2给搞定了,感谢weilin和Henry,感谢北京的shuoji,上海的ricky等童鞋。
后来发现不是ODI的问题,而是DB2服务端的问题,在这个问题搞定之后,通过SQL Developer也可以连接成功了。

首先第一步是从DB2的安装文件中把驱动文件copy到ODI的目录,两个文件分别是:

db2jcc.jar
db2jcc_license_cu.jar

然后把这两个驱动文件放到ODI的driver文件目录:

C:\Documents and Settings\< 用户名>\Application Data\odi\oracledi\userlib
或者目录是(~/.odi/oracledi/userlib,在Unix系统上)
D:\Oracle\Middleware\Oracle_ODI1\oracledi\agent\drivers (odi home)

在ODI的物理技术中配置一个UDB DB2,然后使用Universal JDBC driver,详细配置如下:

com.ibm.db2.jcc.DB2Driver
jdbc:db2://:60000/

问题就出在这里的端口60000,可是登陆到服务器发现60000端口根本没有监听。

netstat -an | grep 60000 无任何返回

确定db2是启动的,而且通过本地的db2 connect to dbname 是可以成功连接到实例和数据库。

检查了下db2的dbm也就是实例的配置参数,发现TCP的服务配置是空。

$db2 get dbm cfg | grep TCP
TCP/IP Service name (SVCENAME) =

$ db2 update dbm cfg using SVCENAME 60000
DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed
successfully.

这里的service name可以有两种方式,直接填写端口,或者使用/etc/services文件里面的映射。比如在/etc/services文件追加下面的内容,那么刚才的SVCENAME就可以写成db2c_db2。

db2c_db2 60000/tcp

设置好之后,发现60000端口的监听还是没有。

进一步研究发现除了db的配置和dbm的配置之外,还有一个系统的配置文件注册库没有设置对,问题就出在这里(感谢Weilin)

$ db2set -all
[i] DB2COMM=TCPIP (通过命令加上这个即可: db2set DB2COMM=TCPIP )
[g] DB2SYSTEM=x4170m3-200143
[g] DB2INSTDEF=db2irun
[g] DB2ADMINSERVER=dasusr1
$

这个时候重启数据库即可:

db2stop force
db2start

这个时候通过netstat -an | grep 60000可以看到监听已经起来,而且SQL developer和ODI均可以连接成功。

数据的ELT到Oracle当然也是没问题的。

Posted in 数据库 | Tagged , | Leave a comment

Post rootupgrade.sh for 11gR2 upgrade

When you upgrade Grid Infrastructure from pre 11.2.0.3 to 11.2.0.3, after execute rootupgrade.sh and lost you OUI windows by incident says: first node reboot. You might be confused what was missed and need to do afterward.

After rootupgrade.sh successfully executed on all nodes. please run below steps in order to complete the upgrade.

Basically, it consists of 4 steps if you can’t find the script and lost your OUI GUI after root.sh finish.

1. netca to create listener

2. export ORACLE_BASE=/u01/app/grid;
export ORACLE_HOME=/u01/app/11.2.0/grid;
/u01/app/11.2.0/grid/bin/asmca -silent -postConfigureASM -sysAsmPassword *** -asmsnmpPassword ***

3. $CRS_Home/oui/bin/runinstaller -updatenodelist cluster_nodes={node1,node2} oracle_home= CRS=true.

4. export ORACLE_BASE=/u01/app/grid;
export ORACLE_HOME=/u01/app/11.2.0/grid;
/u01/app/11.2.0/grid/bin/cluvfy stage -post crsinst -n node1,node2

Step1 is used to create the listener if you was upgrade from pre-11.2 since the listener resource is different in 11gR2.
Step2 is used to configure the ASM instance for pre-11.2.
Step3 is used to update the OraInventory to reflect the Grid Home change since GI in 11gR2 was out of place upgrade.
Step4 is used to do the post upgrade cluvfy check.

Posted in 数据库 | Tagged | Leave a comment

Oracle RAT介绍及最佳实践

Oracle Real Application Testing(简称RAT)是11g的一个重要的feature,其推出的初衷是为了满足数据中心变更后有很好的方法和工具去衡量这些变更对于生产环境的应用带来的影响,更好的评估诸如硬件升级,软件升级,架构变化等等对于客户应用程序的影响。

Real Application Testing其实有两个解决方法,分别是Database Replay和SPA(SQL Performance Analyzer)。

Database Replay,数据库回放顾名思义可以理解为一个录像机,通过在源系统上把实时应用产生的负载录制下来,并拿到变更后的环境进行播放,从而进行一个对比。数据库回放分为四个阶段完成:录制、预处理、回放、结果分析和报告。

SPA就是针对更细颗粒的SQL给出变更前后的执行结果,并进行分析和建议。SPA一般配合在数据库回放中进行使用。

下面重点介绍数据库回放中的四个步骤如何完成。

1. 录制(为了支持老版本的数据库,RAT提供了一个打开捕捉的开关参数,在10.2的环境也可以使用)

1.1 首先创建一个操作系统目录,用于存放录制的文件
1.2 在数据库中创建一个目录,用于跟OS的目录对应:

create or replace directory “sample_cap_dirobj” as ‘/tmp/sample_cap_dir’;

1.3 设置捕捉的过滤条件,有时候我们需要限定只捕捉部分的工作负载,可以通过这个设置来达成:

exec dbms_workload_capture.add_filter(
‘sample_cap_filter’,
‘,
‘[VALUE]’);

比如,只捕捉SCOTT用户的负载:
exec dbms_workload_capture.add_filter(‘sample_cap_filter2’, ‘USER’, ‘SCOTT’);

1.4 检查下限定条件设定的是否正确:
select type, name, attribute, status, value from dba_workload_filters;

1.5 开始捕捉:
exec dbms_workload_capture.start_capture(‘sample_cap_name’, ‘sample_cap_dirobj’, NULL, ‘INCLUDE’);

1.6 使用下面的视图检查当前的捕捉情况和状态:

select name, directory, status, start_time, end_time, duration_secs, errors
from dba_workload_captures;

一般情况,捕捉的时候如果没有设定捕捉的时长,就需要通过手工的命令进行停止。

1.7 停止捕捉:
exec dbms_workload_capture.finish_capture;

1.8 为了分析变更前后的负载运行情况,推荐在捕捉结束之后进行导入一个AWR报告:
declare
capture_id number;
begin
select max(id) into capture_id
from dba_workload_captures
where status = ‘COMPLETED’;

dbms_workload_capture.export_awr(capture_id);
end;
/

2 预处理(预处理之前需要把捕捉好的文件copy到变更后的环境,预处理的数据库版本需要跟回放的版本保持一致)

2.1 同样创建一个目录的对象指向目标环境的录制后的文件

create or replace directory “sample_rep_dirobj” as ‘/tmp/sample_rep_dir’;

2.2 预处理:

exec dbms_workload_replay.process_capture(‘sample_rep_dirobj’);

2.3 初始化播放:

exec dbms_workload_replay.initialize_replay(‘sample_rep_name’, ‘sample_rep_dirobj’);

2.4 如果播放环境和捕捉环境数据库配置不同,则需要进行一个映射的过程:

exec dbms_workload_replay.remap_connection(, ‘‘);

比如:
exec dbms_workload_replay.remap_connection(1, ‘(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sample_host)(PORT=1234))(CONNECT_DATA=(SERVICE_NAME=sample_sid)))’);

2.5 设置播放模式,可以支持同步和异步两种方式:

exec dbms_workload_replay.prepare_replay(synchronization => TRUE, connect_time_scale => 100, think_time_scale => 100, think_time_auto_correct => FALSE);

3 回放(需要准备好相应的客户端,包括启动客户端和开始回放两个步骤)
3.1 准备客户端(RAT提供了客户端的工具wrc,位于ORACLE_HOME/bin/目录下)

根据工作负载的强度大小,准备相应的客户端,可以使用wrc的评估模式来衡量大约需要多少客户端。

$ORACLE_HOME/bin/wrc / mode=calibrate replaydir=/tmp/sample_rep_dir

3.2 启动客户端:这个时候客户端属于就绪状态,等待播放开始

$ORACLE_HOME/bin/wrc / replaydir=/tmp/sample_rep_dir

3.3 开始播放:

exec dbms_workload_replay.start_replay;

3.4 播放结束

exec dbms_workload_replay.cancel_replay;

4. 报告和分析
4.1 分别导入捕捉和回放时候的AWR:

select dbms_workload_capture.import_awr(, ‘SYSTEM’) from dual;
select dbms_workload_replay.import_awr(, ‘SYSTEM’) from dual;

4.2 查看捕捉和回放时候的报告:

select dbms_workload_capture.report(, ‘TEXT’) from dual;
select dbms_workload_replay.report(, ‘TEXT’) from dual;

上面全部是通过命令行的方式来完成的Database Replay的过程。如果你熟悉EM的操作,通过EM来完成同样的工作更加简单,而且直观,Oracle也是推荐你通过EM的方法来完成捕捉,准备,回放和报告的。

Posted in 数据库 | Tagged | 2 Comments