You do not have to use the same storage option for each type of file;

Before you install Oracle Cluster Ready Services, you must choose the storage option that you want to use for the Oracle Cluster Registry and CRS voting disk. You cannot use Automatic Storage Management to store these files

If you are not using HACMP, you must use a GPFS file system to store the Oracle CRS files

Somebody ask about time-based recovery,I told him before invoking rman,need to set nls_lang/data_format;for the sake of much of the rman list output is data/time delated

Oracle Database 12g将不支持裸设备

Metalink Note:578455.1

检查是否启用了跟踪

select sid, serial#, sql_trace, sql_trace_waits, sql_trace_binds
from v$session 
where username = 'HR'

传输 AWR 数据
 查看全文
	   

 

环境PC OS： RedHat AS2.1 DB：Oracle10g Beta 经过实践操作在LINUX上面实现了ASM 具体过程大致如下：

相信rman对任何一个dba都比较熟悉了；然而如题有更多的方式未被大家都熟知，下面给一些提示：

1) 在目录数据库中创建恢复目录所用表空间：

解决方法：
alter database recover managed standby database cancel ;
.把需要转用的目录加进去
ALTER SYSTEM SET db_file_name_convert='xxx,xxx' scope=both;
shutdown dg db,and mv files and mount dg,then ALTER SYSTEM SET standby_file_management='MANUAL' SCOPE=BOTH;
alter database create datafile '/u01/oracle/product/10.2.0.3/dbs/UNNAMED00546' as '/dgjbdata/indx/rsm_idx81.dbf';
alter system set standby_file_management=AUTO scope=both;

详细见下

Parameters that Should Have Identical Settings on All Instances

Oracle recommends that you set the values for the following parameters to the same value on all instances. Although you can have different settings for these parameters on different instances, setting each parameter to the same value on all instances simplifies administration:

• ARCHIVE_LAG_TARGET

  Different values for instances in your Oracle RAC database are likely to increase overhead because of additional automatic synchronization performed by the database processing.

  When using Oracle Streams with your Oracle RAC database, the value should be greater than zero.

• LICENSE_MAX_USERS

  This parameter determines a databasewide limit on the number of users defined in the database and it is useful to have the same value on all instances of your database so you can see the current value no matter which instance you are using. Setting different values may cause additional warning messages to be generated during instance startup or cause commands related to database user management to fail on some instances.

• LOG_ARCHIVE_FORMAT

  If you do not use the same value for all your instances, then you unnecessarily complicate media recovery. The recovering instance expects the required archive log file names to have the format defined by its own value of LOG_ARCHIVE_FORMAT, regardless of which instance created the archive log files.

  Databases that support Oracle Data Guard, either to send or receive archive log files, must use the same value of LOG_ARCHIVE_FORMAT for all instances.

• SPFILE

  If this parameter does not identify the same file to all instances, then each instance may act differently and unpredictably in failover, load-balancing, or standard operations. Additionally, a change you make to the SPFILE with an ALTER SYSTEM SET or ALTER SYSTEM RESET command is saved only in the SPFILE used by the instance where you run the command. Your change will not be reflected in instances using different SPFILEs.

  If the SPFILE values are different in instances for which the values were set by the server, then you should restart the instances that are not using the default SPFILE.

• UNDO_RETENTION

  By setting different values for UNDO_RETENTION in each instance, you are likely to reduce scalability and encounter unpredictable actions following a failover. Therefore, you should carefully consider whether or not you will accrue any benefits before you assign different values for this parameter to the instances in your Oracle RAC database.

一、概述

在Oracle 10i之前，管理一个大型数据库成千上万个的数据文件对数据库官员来说是一个既无技术含量又枯燥无味的负担，而使用自动存储管理将大大减轻这方面的工作量，数据库管理员只需要管理少数几个磁盘组即可。一个磁盘组是ASM管理的一个逻辑单元，由一组磁盘设备组成。我们可以定义一个磁盘组作为数据库的默认磁盘组，Oracle会自动管理存储，包括创建、删除数据文件等。Oracle会自动将这些文件与一个合适的数据库对象做关联，这样我们在管理这些对象时只需要提供对象的名称，而无需像以前那样提供详细的文件名。

二、Oracle存储管理器的好处

ASM提供了很多有用的存储技术，如RAID和LVM（逻辑卷管理）等。像这些技术一样，ASM允许你在一组独立的磁盘上创建一个单独的磁盘组。这样就实现了单个磁盘组的I/O均衡。同时ASM还实现了条带化(Striping)和磁盘镜像(Mirroring)以提高I/O的性能和数据可靠性。与RAID或LVM不同的是，ASM是在文件级实现的条带化和镜像，这样的实现方式给用户带了很大选择自由度，我们可以在同一个磁盘组中对不同的文件配置不同的存储属性，实现不同的存储方式。

三、磁盘组和故障组

一个磁盘组可以包含多个磁盘设备，每个磁盘设备可以是一个独立的物理硬盘，也可以是多个物理硬盘的组合，如RAID阵列或一个逻辑卷，甚至可以是一个物理硬盘的一个分区。也就是说，这里的磁盘设备完全是一个ORACLE的逻辑概念。当然，在实际应用中，一个磁盘组通常都是由一个或多个独立的物理磁盘组成。为了让ASM很好地实现I/O均衡、存储能力和性能，磁盘组中的所有设备应该尽可能的一致。当我们添加一个设备到磁盘组中时，我们可以指定为那个设备指定故障组。故障组确定了有一般故障的磁盘设备。例如：设备都是连在控制器上的，如果控制器坏了，所有连在该控制器上的设备都会变得不可用。默认时，每个设备都属于它们自己的故障组。通过设定故障组，ASM可以将数据分布在一个磁盘组中不同的设备上，以最小化因组件失败而导致数据丢失的风险。

值得注意的是，不要在一个单个物理硬盘上指定多个分区作为ASM磁盘组的设备。ASM希望每一个磁盘设备都是一个独立的物理硬盘。尽管我们也可以在ASM磁盘组中使用逻辑卷，Oracle公司不推荐这样用，因为逻辑卷管理器将屏蔽物理磁盘的属性，ASM将无法有效操作磁盘组中的逻辑卷。

四、冗余级别

ASM提供了三个级别的镜像，称之为冗余级别。我们可以在创建磁盘组时指定它们。

1、扩展冗余

使用扩展冗余的磁盘组，ASM不会对磁盘组中的内容做镜像，也就是说，ASM没有做冗余，在下面的情况下我们可以选择这一级别的冗余方式：

>>磁盘组的设备拥有自己的数据保护措施，如RAID设备等。

>>数据库的数据并不要求不间断的访问，如一个拥有备份的开发环境。

2、标准冗余

使用标准冗余的磁盘组，其内容默认采用双向镜像。当然在创建文件时，我们也可以选择不做镜像。为了在建立磁盘组时使用标准冗余，我们至少需要两个故障组（最少两个设备）。使用标准冗余的磁盘组磁盘空间利用率为50%。

3、高冗余

使用高冗余的磁盘组，其内容默认采用三向镜像。当然在创建文件时，我们也可以选择双向镜像或不做镜像。为了在建立磁盘组时使用高冗余，我们至少需要三个故障组（最少三个设备）。使用高冗余的磁盘组磁盘有效空间为总空间的1/3。

WORKLOAD REPOSITORY report for

DB Name DB Id Instance Inst Num Startup Time Release RAC
------------ ----------- ------------ -------- --------------- ----------- ---
ORA11G 4058250754 ora11g 1 20-8月 -08 11:11 11.1.0.6.0 NO

Host Name Platform CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
ANGELO Microsoft Windows IA (32-bit) 1 1.18

Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 335 20-8月 -08 14:00:21 22 1.3
End Snap: 337 20-8月 -08 16:00:34 24 1.3
Elapsed: 120.22 (mins)
DB Time: 0.07 (mins)

Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 196M 196M Std Block Size: 8K
Shared Pool Size: 152M 152M Log Buffer: 5,780K

Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ --------------- --------------- ---------- ----------
DB Time(s): 0.0 0.2 0.00 0.03
DB CPU(s): 0.0 0.1 0.00 0.02
Redo size: 312.4 112,665.6
Logical reads: 3.2 1,151.7
Block changes: 0.8 282.0
Physical reads: 0.0 15.7
Physical writes: 0.1 52.1
User calls: 0.0 6.2
Parses: 0.3 92.8
Hard parses: 0.0 0.5
W/A MB processed: 4,275.8 1,542,092.8
Logons: 0.0 1.6
Executes: 0.4 156.0
Rollbacks: 0.0 0.0
Transactions: 0.0

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 98.66 In-memory Sort %: 100.00
Library Hit %: 99.11 Soft Parse %: 99.46
Execute to Parse %: 40.53 Latch Hit %: 99.96
Parse CPU to Parse Elapsd %: 0.01 % Non-Parse CPU: 19.21

Shared Pool Statistics Begin End
------ ------
Memory Usage %: 47.37 47.96
% SQL with executions>1: 76.54 77.36
% Memory for SQL w/exec>1: 74.55 73.09

Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

太多，放在下面，可以对比先前版本的层次

His responsibilities are listed as below:

The STATISTICS_LEVEL parameter should be set to TYPICAL or ALL . The default setting is TYPICAL.

Setting the STATISTICS_LEVEL parameter to BASIC,and is not recommended

>=10g

如果设置statistics_level=baisc，竟然连V$SESSION_LONGOPS这个有用的功能就无法使用了

ora11g

exec dbms_stats.gather_table_stats('Schema_name','Table_name',
method_opt=>'for columns (C1,C2) size AUTO');

偶尔再用show_space;多了一些味道;

参见曾经写的

http://2jliu.itpub.net/post/21805/286381

It's clear that 11g data compression offers these huge benefits, but the exact overhead costs remain unknown:

• Up to a 3x disk savings - Depending on the nature of your data, Oracle compression will result in huge savings on disk space.

• Cheaper solid-state disk - Because compressed tables reside on fewer disk blocks, shops that might not otherwise be able to afford Oracle flash disks can now enjoy I/O speeds up to 300x faster than platter disk.

• Faster full scan/range scan operations - Because tables will reside on less data blocks, full table scans and index range scans can retrieve the rows with less disk I/O.
  

• Reduced network traffic - Because the data blocks are compressed/decompressed only within Oracle, the external network packets will be significantly smaller.

The declarative nature of the SQL syntax has always made it difficult to perform SQL tuning. The basic tenet of cost-based SQL optimization is that the person who writes a SQL query simply "declares" what columns they want to see (the SELECT clause), the tables where the columns reside (the FROM clause), and the filtering conditions (the WHERE clause). It's up to the SQL optimizer to always determine the optimal execution plan. This is a formidable challenge, especially in a dynamic environment, which is why Oracle introduced the 10g new feature of CBO dynamic sampling.

Oracle tuning consultants have know for many years that the best way to tune an Oracle system is to take a top-down approach, finding the optimal configuration for external factors (i.e. OS kernel settings, disk I/O subsystem), and determining the best overall setting for the Oracle instance (i.e. init.ora parameters).

Holistic tuning involves tuning a representative workload, adjusting global parameters in order to optimize as much SQL as possible. Only then, is it prudent to start tuning individual SQL statements. Many Oracle professional who adopt a bottom-up approach (tune the SQL first), find all of their hard-work un-done when a change is made to a global setting, such as one of the SQL optimizer parameters or recomputing optimizer statistics. Oracle’s holistic SQL tuning approach is new, and many Oracle professionals find it difficult to embrace, but this is about to change. The Oracle 11g SQL Performance Analyzer (SPA), is primarily designed to speed up the holistic SQL tuning process, automating much of the tedium.

Once you create a workload (called a SQL Tuning Set, or STS), Oracle will repeatedly execute the workload, using sophisticated predictive models (using a regression testing approach) to accurately identify the salient changes to execution plans, based on your environmental changes.

Using SPA, we can predict the impact of system changes on a workload, and we can forecast changes in response times for SQL after making any change, like parameter changes, schema changes, hardware changes, OS changes, or Oracle upgrades; any change that influence SQL plans is a good candidate for SPA.

rman备份不能成功,呵呵他们老板也很懂;

仔细查看邮件一时也找不出原因,远程登入看看;问题找到,对照以前一直没有没有问题,查找原因,果然是先前负责的刻意更改了默写值,无语

1、切换服务器归档模式，如果已经是归档模式可跳过此步：

对巨大系统很有效,可以合理的利用时间(比如一周时间来作步创建)避免系统的持续负载

http://www.itpub.net/887045.html

AS topic!

----------system------------

SQL> create table p_table (sid integer,serial# integer)
2 partition by system
3 (partition p1 ,
4 partition p2);

Table created.

SQL> insert into p_table partition(p1) values(1,1);

1 row created.

SQL> insert into p_table partition(p2) values(2,2);

1 row created.

SQL> select * from p_table partition(p1);

SID SERIAL#
---------- ----------
1 1

SQL> select * from p_table partition(p2);

SID SERIAL#
---------- ----------
2 2

SQL>

new and good!

better for ebs more

result_cache_max_size

client_result_cache_size

在以前的版本中，如果spfile参数文件丢失，我们只能通过备份去恢复，或者从存在的pfile中创建...

实际上如果仅仅是参数文件丢失，数据库仍然在运行，那我们完全可以从数据库实例中得到当前的所有运行参数，从Oracle 11g开始，一个新的命令被引入，这个命令是：
create spfile from memory;

这个命令可以使用当前的参数设置在缺省位置创建一个spfile文件，当然我们也可以指定一个不同的位置：

SQL> create spfile='/tmp/spfile.ora' from memory;

File created.

当然创建出来的参数文件比现有的文件包含了更多的参数：

SQL> shutdown immedaite
SP2-0717: illegal SHUTDOWN option
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 410112000 bytes
Fixed Size 1300268 bytes
Variable Size 243271892 bytes
Database Buffers 159383552 bytes
Redo Buffers 6156288 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> alter database force logging;

Database altered.
SQL> alter system set log_archive_dest_1='LOCATION=/u01/app/oracle/arch';

System altered.

SQL> alter system set log_archive_dest_2='SERVICE=STDB REOPEN=120';

System altered.

SQL> ALTER SYSTEM SET log_archive_dest_state_2=DEFER;

SQL> SHOW PARAMETER REMOTE

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_dependencies_mode string TIMESTAMP
remote_listener string
remote_login_passwordfile string EXCLUSIVE
remote_os_authent boolean FALSE
remote_os_roles boolean FALSE
result_cache_remote_expiration integer 0
SQL> CREATE PFILE='/u01/app/oracle/arch/stdb.ora' from spfile;

File created.

SQL> alter database create standby controlfile as '/u01/app/oracle/arch/st.ctl';

Database altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 410112000 bytes
Fixed Size 1300268 bytes
Variable Size 243271892 bytes
Database Buffers 159383552 bytes
Redo Buffers 6156288 bytes
Database mounted.
Database opened.
SQL> ALTER SYSTEM SET log_archive_dest_state_2=enable;

System altered.

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/arch
Oldest online log sequence 25
Next log sequence to archive 27
Current log sequence 27
SQL> alter system archive log current;

System altered.

SQL> /

System altered.

Oracle 11g 新特性：Oracle11g 后台进程概述

shahand 2007-07-27

一。Oracle11g 后台进程与10gR2的比较
我们知道，oracle数据库的后台进程一般以ora_开头来命名，下面就来看一下在Oracle11g中的后台进程都有哪些，以单节点的Oracle实例

为例：
[root@ractest ~]# ps -ef|grep ora_ |sort +7
root 11768 5567 0 05:30 pts/4 00:00:00 grep ora_
oracle 28069 1 0 Jul20 ? 00:00:03 ora_cjq0_orcl
oracle 28063 1 0 Jul20 ? 00:02:10 ora_ckpt_orcl
oracle 28049 1 0 Jul20 ? 00:00:00 ora_dbrm_orcl
oracle 28059 1 0 Jul20 ? 00:00:08 ora_dbw0_orcl
oracle 28055 1 0 Jul20 ? 00:00:06 ora_dia0_orcl
oracle 28047 1 0 Jul20 ? 00:00:02 ora_diag_orcl
oracle 28085 1 0 Jul20 ? 00:00:02 ora_fbar_orcl
oracle 28061 1 0 Jul20 ? 00:00:05 ora_lgwr_orcl
oracle 28057 1 0 Jul20 ? 00:00:00 ora_mman_orcl
oracle 28073 1 0 Jul20 ? 00:00:09 ora_mmnl_orcl
oracle 28071 1 0 Jul20 ? 00:00:06 ora_mmon_orcl
oracle 28043 1 0 Jul20 ? 00:00:08 ora_pmon_orcl
oracle 28051 1 0 Jul20 ? 00:00:00 ora_psp0_orcl
oracle 28108 1 0 Jul20 ? 00:00:00 ora_q000_orcl
oracle 28110 1 0 Jul20 ? 00:00:00 ora_q001_orcl
oracle 28089 1 0 Jul20 ? 00:00:00 ora_qmnc_orcl
oracle 28067 1 0 Jul20 ? 00:00:00 ora_reco_orcl
oracle 28087 1 0 Jul20 ? 00:00:00 ora_smco_orcl
oracle 28065 1 0 Jul20 ? 00:00:14 ora_smon_orcl
oracle 28045 1 0 Jul20 ? 00:00:00 ora_vktm_orcl
oracle 11559 1 0 04:52 ? 00:00:00 ora_w000_orcl

比较一下Oracle10gR2中的后台进程：

oracle@lxsfrac03 $ ps -ef |grep ora_ |grep -v grep |sort +7
oracle 26479 1 0 15:02:44 ? 0:00 ora_cjq0_racg1
oracle 26473 1 0 15:02:44 ? 0:00 ora_ckpt_racg1
oracle 26464 1 0 15:02:44 ? 0:00 ora_dbw0_racg1
oracle 26469 1 0 15:02:44 ? 0:00 ora_dbw1_racg1
oracle 26416 1 0 15:02:44 ? 0:00 ora_diag_racg1
oracle 26518 1 0 15:02:46 ? 0:00 ora_lck0_racg1
oracle 26471 1 0 15:02:44 ? 0:00 ora_lgwr_racg1
oracle 26422 1 0 15:02:44 ? 0:00 ora_lmd0_racg1
oracle 26420 1 0 15:02:44 ? 0:00 ora_lmon_racg1
oracle 26424 1 0 15:02:44 ? 0:00 ora_lms0_racg1
oracle 26428 1 0 15:02:44 ? 0:00 ora_lms1_racg1
oracle 26452 1 0 15:02:44 ? 0:00 ora_mman_racg1
oracle 26483 1 0 15:02:44 ? 0:00 ora_mmnl_racg1
oracle 26481 1 0 15:02:44 ? 0:00 ora_mmon_racg1
oracle 26414 1 0 15:02:44 ? 0:00 ora_pmon_racg1
oracle 26418 1 0 15:02:44 ? 0:00 ora_psp0_racg1
oracle 26477 1 0 15:02:44 ? 0:00 ora_reco_racg1
oracle 26475 1 0 15:02:44 ? 0:00 ora_smon_racg1

二。Oracle11g 新的后台进程的功能
新多的后台进程有：ora_dbrm_orcl，ora_dia0_orcl，ora_psp0_orcl，ora_smco_orcl，ora_vktm_orcl，ora_w000_orcl，
这些后台进程的功能分别如下：
DBRM：数据库资源管理进程, (The database resource manager process)，负责设置资源计划和其他的资源管理的工作。
DIAG：数据库诊断进程， (The diagnosibility process) ，负责维护管理各种用于诊断的转储文件，并执行oradebug命令。
DIA0：另一个数据库诊断进程，负责检测Oracle数据库中的挂起(hang)和死锁的处理。
PSP0：process spawner，用于产生oracle进程
SMCO：space management coordinator，该进程负责空间管理协调管理工作，负责执行空间的分配和回收。
Wnnn；命名为W000，W001，W002.....，由smcO动态产生执行上述相关任务。
VKTM：virtual keeper of time，用于提供wall-clock time，（每秒钟更新一次）。提供每二十毫秒更新一次的reference-time counter，看起来有点类似计时器的功能。

三。Oracle11g 新引入的其他后台进程
再来认识一下Oracle11g中新引入的一些其他进程，因为一些特性在我的测试库中没有用到，比如asm，所以在ps -ef的结果中没有。

GMON：用于维护asm磁盘组的磁盘之间的关系。
KATE：当ASM的磁盘离线的时候，该进程负责asm的元文件的io读写。
MARK：如果有向asm离线磁盘的missed 写请求，该进程将ASM分配的单元的状态标记为stale
FBDA：涉及到flashback-data-archive新特性的一个进程，The flashback data archiver proces。用于将“轨表”(tracked tables)的历史数据进行归档。当“轨表”上的事务提交以后，fbda进程负责将数据的前镜像保存到flashback archive区域。该进程还负责flashback的数据归档的空间管理、分配、保留，跟踪tracked transactions。
什么是“轨表”(tracked tables): 是指启用了flashback archive特性的表。
RMSn：The Oracle RAC management processes,负责执行Oracle RAC的管理任务，比如RAC相关资源的创建和集群中新实例的添加。
DSKM：The slave diskmon process ， 负责oracle 实例、asm实例和磁盘的管理进程之间的io fencing 信息的交换。如果使用SAGE的存储，该进程还负责SAGE存储的一些信息的管理。

四。复习一下Oracle 10gR2的后台进程
最后 再来复习一下Oracle 10gR2中的一些后台进程：
Queue Monitor Processes (QMNn) 是供 Oracle Streams Advanced Queuing使用的可选的进程，用于监控消息队列。这两类进程出错不会导致整个实例出错。
MMON ：manageability monitor，与oracle10g的新特性－－诊断功能相关，负责管理一些后台任务，比收集最近修改过的 SQL 对象的统计信息
MMNL ：与oracle10g的新特性－－诊断功能相关，会根据调度从SGA将统计结果－－如会话的历史信息，刷新输出至数据库表。
MMAN ：与oracle10g的新特性－－诊断功能相关，负责执行一些数据库内部任务，自动设置SGA大小特性会使用这个进程。
RBAL ：Rebalance。进程负责协调磁盘组间的负载平衡工作，在使用了ASM的数据库实例中运行。当向ASM磁盘组增加或删除磁盘时，RBAL进行负责处理重新平衡的请求。它可以使多个实例同时访问一个 ASM 磁盘（global open）。最终由 ORBn 进程实际执行数据扩展的负载均衡。 实例中可以运行多个 ORBn 进程，分别为 ORB0，ORB1，以此类推。
ASMB: 在使用asm磁盘组的时候负责与asm实例的通信，向ASM实例提供更新统计信息

摘抄以学习

意外,可以更加顺畅的使用了

Decision Support and Expert Systems Technology

Oracle had made a commitment to Decision Support Systems (DSS) Technology starting in Oracle 9i when they started to publish “advisory” utilities, the result of monitoring the Oracle instance and coming up with estimated benefits for making a change to the database configuration. In the world of applied artificial intelligence, an expert system (e.g. AMM, ASM) solves a well-structured problem for the DBA, while a decision support system solves a semi-structured problem with the DBA, who supplies the human intuition required to solve a complex problem.

Oracle has made a commitment to distinguishing themselves in the database marketplace, and this is one of the major reasons that they command a major market share. One of the most exciting areas of Oracle technology is automation, especially the self-management features. Oracle has now automated many critical components, including memory advisors (AMM), automated storage management (ASM), and Oracle is now working to enhance more intelligent utilities including ADDM, the Automated Database Diagnostic Monitor, and the brand new 11g SQL Performance Advisory (SPA).

The Oracle 11g SPA functions as a DSS, helping the DBA by automating the well-structured components of a complex tuning task, such as hypothesis testing. In SPA, the DBA defines a representative workload and then tests this workload empirically, running the actual queries against the database and collecting performance metrics. SPA allows the DBA to obtain real-world performance results for several types of environmental changes:.

New System Testing Tools

As a DBA, one of the most bedeviling problems that I’ve regularly faced is to be able to predict accurately how the next set of changes to the database’s application code, database patch set, or hardware configuration will affect that database’s performance. That usually meant purchasing a relatively expensive third-party package (e.g. Mercury Interactive’s LoadRunner) to generate a sample workload against the database using the next version of the application code, and then comparing the results against baseline performance for the current application code version.

Fortunately, Oracle Database 11g has come to the rescue with two new utilities that offer monumental strides forward in system testing:

• Database Replay. Database Replay can capture generated workloads from production systems at the database level. Therefore, it’s no longer necessary to run actual application code to duplicate the load on the database, and this also improves accuracy of the simulated workload because it limits or removes other factors like network latency. These captured workloads can then be replayed on a quality assurance database so that the impact of application changes, software patches, and even hardware upgrades can be measured accurately. This feature is especially valuable in detecting performance issues that could potentially hamstring a production database’s performance that might go otherwise undetected until well after changes have been deployed.
• SQL Performance Analyzer. A robust complement to the Database Replay facility, the SQL Performance Analyzer (SPA) leverages existing Oracle Database 10g SQL tuning components. The SPA provides the ability to capture a specific SQL workload in a SQL Tuning Set, take a performance baseline before a major database or system change, make the desired change to the system, and then replay the SQL workload against the modified database or configuration. The before and after performance of the SQL workload can then be compared with just a few clicks of the mouse. The DBA only needs to isolate any SQL statements that are now performing poorly and tune them via the SQL Tuning Advisor.

I’ve often wished that the Oracle database would provide a method to retain in memory the result set from a complex query that contains what I like to call reference information. These are data that hardly ever change, but must still be read and used across multiple applications - for example, a list of all country codes and their corresponding names for lookup when processing addresses for new international customers, or a list of all ZIP Codes in the Midwestern US.

Oracle Database 11g fills this gap with three new structures called result caches, and each structure has a different purpose:

• The SQL query result cache is an area of memory in the Shared Global Area (SGA) that can retain the result sets that a query generates.
• The PL/SQL function result cache can store the results from a PL/SQL function call.
• Finally, the OCI client result cache can retain results from queries or functions on the application server from which the OCI call originated.

周三参加11g蜂会,顺便混沌饭吃吃

一、oracle 10g/11g

1、修改归档日志存放路径：

SQL> alter system set log_archive_dest_10='location=/oracle/oracle10g/log/archive_log';

2、关闭数据库

SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down.

3、启动数据mount状态：

SQL> startup mount;

4、修改数据库为归档模式：

SQL> alter database archivelog; Database altered.

5、打开数据库，查询：

SQL> alter database open;

修改日志文件命名格式：

SQL> alter system set log_archive_max_processes = 5;

SQL> alter system set log_archive_format = "archive_%t_%s_%r.log" scope=spfile;

1. Standby Statspack Usage

1.1 Statspack Installation

The installation script (sbcreate.sql) creates the schema to hold the standby snapshots. The script asks for:

1. A password for stdbyperf user
2. Default tablespace
3. Temporary tablespace

The script creates the ‘stdbyperf’ user and assigns it the selected default and temporary table space. After the user has been created it calls sbaddins.sql to add the first standby instance to the configuration.
Example:

SQL> @sbcreate

1.2 Add an Instance to Statspack Configuration

Log in to the primary as the 'stdbyperf' user and run the script sbaddins.sql to add a standby instance to the configuration. The script asks for:

1. The TNS alias of the standby database instance
2. The password of the perfstat user on the standby site

The script then creates a private database link to the perfstat schema on the standby site and a separate gathering package for each standby instance. Example:

SQL> connect stdbyperf/your_password
SQL> @sbaddins
Input inst2_alias as the tns alias.

1.3 Collect Performance Data from a Standby Instance
The script sbaddins.sql creates a separate PL SQL package for each standby instance. Run the snap procedure of those packages to collect the performance data. The statspack_<instance_name>.snap procedure accesses the data dictionary and stats$ views on the standby database via database link connected to the original perfstat user und stores the data to stats$ tables on the primary instance. For example, while the standby is opened read only, login to the primary database and create the snap:

SQL> connect stdbyperf/your_password
SQL> exec statspack_<instance_name>.snap

1.4 Generate Standby Statistics Report
The script sbreport.sql generates the standby statistics report. The script asks for: database id, instance number, high and low snapshots id to create the report. Example:

SQL>@sbreport

1.5 Purge a Set of Snapshots
The script sbpurge.sql purges a set of snapshots. The script asks for database id, instance number, low and high snapshots ids. The script purges all snapshots between the low and high snapshot ids for the given instance. Example:

SQL>@sbpurge

1.6 Delete an Instance from the Configuration

The script sbdelins.sql deletes an instance from the configuration, and deletes the associated PL SQL package. The scripts asks for instance name. The snapshots are not automatically purged when the instance is deleted. After deleting the instance, you are not able to generate reports for that instance. Example:

SQL> @sbdelins

1.7 Drop Statspack Schema
The script sbdrop.sql drops the stdbyperf user and tables. The script must be run when connected to SYS (or internal). Example:

SQL> connect / as sysdba
SQL>

SQL> show parameter sga

NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
lock_sga boolean
FALSE
pre_page_sga boolean
FALSE
sga_max_size big integer
372M
sga_target big integer
0
SQL> show parameter memory

NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
hi_shared_memory_address integer
0
memory_max_target big integer
372M
memory_target big integer
372M
shared_memory_address integer
0
SQL> show parameter pga

NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
pga_aggregate_target big integer
0

下面通过一个实验演示整个过程：

1.设置闪回恢复区
SQL> alter system set db_recovery_file_dest_size=2G;

系统已更改。

SQL> alter system set db_recovery_file_dest='e:/oracle/back';

系统已更改。

2.取消备库的自动恢复状态
SQL> alter database recover managed standby database cancel;

数据库已更改。

3.创建一个还原点
SQL> create restore point restore_point_test guarantee flashback database;

还原点已创建。

4.在主库归档当前日志，确保前一步创建还原点的scn的归档日志已经传到备库
SQL> alter system archive log current;

系统已更改。

5.将主库到备库的归档目的地的状态设置为defer
SQL> alter system set log_archive_dest_state_2=defer;

系统已更改。

6.激活备库到read/write状态
SQL> alter database activate standby database;

数据库已更改。

SQL> alter database open;

数据库已更改。

7.此时可以在备库执行需要的读写操作
SQL> create table t1 as select * from all_objects where rownum<101;

表已创建。

SQL> drop table t1;

表已删除。

8.将数据库flashback回原来保存的还原点
SQL> startup mount force;
ORACLE 例程已经启动。

Total System Global Area 142606336 bytes
Fixed Size 1247732 bytes
Variable Size 83887628 bytes
Database Buffers 50331648 bytes
Redo Buffers 7139328 bytes
数据库装载完毕。

SQL> flashback database to restore point restore_point_test;

闪回完成。

9.转换成备库
SQL> alter database convert to physical standby;

数据库已更改。

10.将备库至于自动恢复状态
SQL> startup mount force;
ORACLE 例程已经启动。

Total System Global Area 142606336 bytes
Fixed Size 1247732 bytes
Variable Size 83887628 bytes
Database Buffers 50331648 bytes
Redo Buffers 7139328 bytes
数据库装载完毕。

SQL> alter database recover managed standby database disconnect from session;

数据库已更改。

11.将主库到备库的归档目的地的状态设置为enable
SQL> alter system set log_archive_dest_state_2=enable;

系统已更改。

12.检查主备库，状态正常

主库检查归档目的状态
SQL> select dest_name,status from v$archive_dest;

DEST_NAME STATUS
------------------------------ ------------------
LOG_ARCHIVE_DEST_1 VALID
LOG_ARCHIVE_DEST_2 VALID
LOG_ARCHIVE_DEST_3 INACTIVE
LOG_ARCHIVE_DEST_4 INACTIVE
LOG_ARCHIVE_DEST_5 INACTIVE
LOG_ARCHIVE_DEST_6 INACTIVE
LOG_ARCHIVE_DEST_7 INACTIVE
LOG_ARCHIVE_DEST_8 INACTIVE
LOG_ARCHIVE_DEST_9 INACTIVE
LOG_ARCHIVE_DEST_10 INACTIVE

已选择10行。

备库检查相关进程
SQL> select process,status from v$managed_standby;

PROCESS STATUS
------------------ ------------------------
ARCH CONNECTED
ARCH CONNECTED
MRP0 WAIT_FOR_LOG
RFS IDLE

SQL> create table v_1 (
2 f1 int,
3 f2 int,
4 f3 as (f2*2-150)
5 );

Table created.

SQL> insert into v_1 (f1,f2) values (1,100);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from v_1;

F1 F2 F3
---------- ---------- ----------
1 100 50

SQL> 虚拟列上可以创建索引还可作为分区表分区键,参与函数/proc运算

SQL> select * from v_1;

F1 F2 F3
---------- ---------- ----------
1 100 50

SQL> alter table v_1 read only; ----只读

Table altered.

SQL>

SQL> alter table v_1 read write;

Table altered.

Oracle10g的rman已经可以执行压缩备份，采用的压缩算法是bzip2。Oracle11g引入了一种新的压缩算法zlib。zlib的压缩率不如bzip2，但是压缩速度则要快不少

RMAN> CONFIGURE COMPRESSION ALGORITHM 'ZLIB';

old RMAN configuration parameters:
CONFIGURE COMPRESSION ALGORITHM 'BZIP2';
new RMAN configuration parameters:
CONFIGURE COMPRESSION ALGORITHM 'ZLIB';
new RMAN configuration parameters are successfully stored

C:Documents and Settingsoracleserver.TVSN_COM_CN>rman target sys/ora10g@grid

恢复管理器: Release 10.2.0.1.0 - Production on 星期六 9月 8 14:59:46 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

连接到目标数据库: GRID (DBID=1837456649)

RMAN> show all;

使用目标数据库控制文件替代恢复目录
RMAN 配置参数为:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:ORACLEPRODUCTGRIDDATABASESNCFGRID
.ORA'; # default

RMAN>以下是 11g的

http://www.oracle.com/technology/global/cn/pub/articles/oracle-database-11g-top-features/index.html

面向 DBA 和开发人员的重要新特性

使用ora11g遇到的问题--不断解决和更新中?

http://www.itpub.net/842176.html