http://www.itpub.net/thread-1018049-1-1.html

Applies to:
Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 10.2.0.1
Information in this document applies to any platform.

Goal
Question 1: What is gcs_resources ?

Question 2: What happens if it exceeds its limit?

Question 3: How do we adjust the limit?

Solution
Answer 1: gcs_resources is global cache resources which are essentially structures we use to protect cache structures (buffers). Each instance "masters" cache resources and the implementation of this mastership is done via a gc resource structure

Answer 2 :Nothing - this is simply stating how high the value has reached for a particular resource. If we need more gcs_resources for some reason, they are obtained from the shared pool.

Answer 3 : RAC specific memory is allocated at the time of SGA creation, mostly in the shared pool. However, the memory does not count in the SHARED_POOL_SIZE as defined in the init.ora parameter file.Therefore, when migrating Oracle from single instance to RAC, you do not need to adjust the SHARED_POOL_SIZE parameter to accommodate the additional memory that RAC uses; Oracle does that automatically. Note that the memory for the KCL global cache lock elements will be allocated in the buffer cache, and not in the shared pool.

You can monitor the exact RAC memory resource usage of the shared pool by querying V$SGASTAT for GCS, GES and KCL related entries. Also, the current and maximum number of GCS resources / shadows (resource names gcs_resources and gcs_shadows) and GES resources / enqueues (resource names ges_ress and ges_locks) can be obtained from V$RESOURCE_LIMIT. In case we are running out of reserved memory for GCS / GES related components, Oracle will dynamically allocate memory from the free memory pool in the shared pool. However, it is recommended to carefully calculate the resources and enqueues as needed since dynamic memory allocation from the shared pool is an expensive operation.

We recommend you monitor for more time to see if this limit is reach and it is a constant situation, then you could considered to increase the value of gcs_resources by instance.

前几天使用logminer查询一些日志来恢复一些数据，发现在toad中使用logminer存在一个小问题，估计在命令行使用也存在这个问题，就是sql_redo的内容中，有关日期的变成了TO_DATE ('16-JUN-08', 'DD-MON-RR')。丢掉了时分秒的信息，修改环境变量NLS_DATE_FORMAT=YYYY-MM-DD HH24:MI:SS后正常。

特殊需要为了稳定执行计划，需要系统不要分析表，执行如下：

锁定统计信息：

exec DBMS_STATS.lock_table_stats(ownname=>'XXX',TABNAME=>'YYY')

取消锁定统计信息：
exec DBMS_STATS.unlock_table_stats(ownname=>'XXX',TABNAME=>'YYY')

查询视图可以知道查询锁定了那些表：
SELECT *
FROM dba_tab_statistics
WHERE stattype_locked = 'ALL' AND owner NOT IN ('SYSTEM', 'SYS', 'SYSMAN')

今天一上班，登陆测试库，出现

ORA-00257: archiver error. Connect internal only, until freed.

看alert.log 文件，出现如下：

Sun May 4 11:53:29 2008
ARCH: Archival stopped, error occurred. Will continue retrying
Sun May 4 11:53:29 2008
ORACLE Instance torcl - Archival Error
Sun May 4 11:53:29 2008
ORA-16038: log 1 sequence# 2455 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/torcl/redo01.log'
Sun May 4 11:53:29 2008
Errors in file /u01/app/oracle/admin/torcl/bdump/torcl_arc1_9325.trc:
ORA-16038: log 1 sequence# 2455 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/torcl/redo01.log'
Sun May 4 11:53:30 2008
Errors in file /u01/app/oracle/admin/torcl/bdump/torcl_arc1_9325.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 2147483648 bytes is 94.44% used, and has 119342592 remaining bytes available.
************************************************************************
You have following choices to free up space from flash recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.
************************************************************************
Archiver process freed from errors. No longe

很明显是db_recovery_file_dest 的空间满了。

执行rman> delete archivelog until time 'sysdate-1' ;

删除不需要的archive log文件。一切OK。

在以前的的Oracle版本中，假如用户commit，后台的LGWR进程必将会把内存中的redo数据写入到online redo log的文件里，之后又会将控制权返回给用户，需要注意的是，其实这段很可能并不是磁盘操作，而是写入到磁盘缓冲中的。假如应用中有过于频繁的用户commit，那么很可能会产生明显的log file sync的等待事件。
而Oracle10g中的新功能－Asynchronous Commit可能是解决这个问题的一个最新方法。
Oracle10g中，我们可以设置commit的行为来做到在commit之后，控制权立刻返回给用户，而Oracle会在恰当的时候来唤醒LGWR，批量更新online redo log文件。
我们可以作系统级的更改：
ALTER SYSTEM SET COMMIT_WRITE = BATCH, NOWAIT
同样我们也可以在commit时单独使用，这样作虽然意味着即使commit了事务，在数据库恢复时也是不一定找得回来的。但从安全换效率的角度考虑，此做法也不失为权益之计。
COMMIT WRITE BATCH NOWAIT

http://www.itpub.net/viewthread.php?tid=829560&pid=10201894&page=1&extra=#pid10201894

主题: Different type of RMAN backup compression in 11G
文档 ID: 注释:427581.1 类型: REFERENCE
上次修订日期: 31-JUL-2007 状态: REVIEWED
In this Document
Purpose
Different type of RMAN backup compression in 11G
--------------------------------------------------------------------------------
Applies to:
Oracle Server - Enterprise Edition - Version: 11.0
Information in this document applies to any platform.
Purpose
In addition to the existing BZIP2 algorithm for binary compression of backup in oracle 10G, RMAN 11G executable also support ZLIB algorithm for the compress backup.ZLIB is the default compression for RMAN in 11G
Different type of RMAN backup compression in 11G
RMAN support binary compression of backup sets.You can choose which compression algorithm that RMAN uses for backups.By default, RMAN uses ZLIB compression.
ZLIB Compression is very fast but compression ratio is not as good as other algorithm.
BZIP2 Compression ratio is very good but is slower than ZLIB
If the COMPATIBLE initialization parameter is set to 11.0 or higher , then the default compression for RMAN backups is ZLIB. IF the COMPATIBILITY initialization parameter is set lower than 11.0 and greater than 10.2 then the default and only possible compression algorithm is BZIP2
Compression can be used for backupset of datafile, archive log and controlfiles.
e.g
RMAN> backup as compressed backupset archivelog all;
RMAN> backup as compressed backupset database;
RMAN> backup as compressed backupset current controlfile;
RMAN compress the backupset contents before writing to disk.No extra decompression steps are required during recovery for rman compressed backup;
To configure the compression algorithm
CONFIGURE COMPRESSION ALGORITHM '<alg_name>';
--------------------------------------------------------------------------------
Help us improve our service. Please email us your comments for this document. .

http://www.itpub.net/viewthread.php?tid=976546

昨天安装zabbix，遇到一个问题，< 被解析成要求输入变量了。

按照zalbb 的提示，加入如下设置OK。

set define off;

http://soft.zdnet.com.cn/software_zone/2007/0719/427418.shtml

RMAN除了单纯的备份恢复功能，已经被赋予了越来越多的责任，比如创建Standby数据库，比如跨平台传输表空间中的表空间转换。Oracle11g的RMAN倒是没有太多飞跃性的更新。
1. 自定义archivelog删除策略
我们知道在11g之前，只有backupset的删除策略可以定义，比如保留多长时间的备份或者保留多少份有效备份，而删除归档日志只有在delete命令中定义删除全部备份完毕的或者删除从哪一个时间点到哪一个时间点的。而在11g中我们已经可以通过configure命令来定义归档日志的删除策略的，比如增加了下面的语法，只有在磁带上备份了2次的归档日志才会被delete命令删除。
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO DEVICE TYPE sbt;
当然，仅仅是增加语法那就只能称为比较无聊的新功能了，除了configure语法之外，现在在11g中通过APPLIED ON
STANDBY关键字可以定义只有对于所有的standby站点都已经applied的归档日志才会被删除，或者定义所有被成功传送到standby站点的归档日志就可以被删除。而以前这些都需要DBA自己撰写脚本从数据字典中查询到相关信息然后再通过脚本删除。
2. 直接通过网络复制数据库
在11g之前如果要使用duplicate命令来复制一份数据库，那么则需要源数据库，需要在目标机器上的一份有效备份，需要目标数据库，在11g中这一切被大大简化。通过FROM ACTIVE DATABASE关键字，我们只需要有一个源数据库，就可以简单地通过网络在另外一台机器上复制一个相同的数据库了。Oracle会通过一系列Memory Script在内存中recover并且open目标数据库。
另外，在11g之前，duplicate数据库是不会自动复制spfile的，而现在，我们通过下面的语句，就可以让Oracle在复制过程中自动生成一份spfile，并且其中的初始化参数允许额外定义。
DUPLICATE TARGET DATABASE
TO aux_db
FROM ACTIVE DATABASE
SPFILE PARAMETER_VALUE_CONVERT '/u01', '/u02'
SET SGA_MAX_SIZE = '200M'
SET SGA_TARGET = '125M'
SET LOG_FILE_NAME_CONVERT = '/u01','/u02'
DB_FILE_NAME_CONVERT '/u01','/u02';

在11g中使用duplicate复制一个数据库的准备步骤只需要目标数据库（AUXILIARY实例）：
a. 通过一个最简单的pfile把实例启动到nomount状态，这个pfile中只需要包含DB_NAME和REMOTE_LOGIN_PASSWORFILE参数即可
b. password文件必须事先建好，而且SYS密码需要跟source数据库中相同，这个通过orapwd可以轻松完成
c. 目录结构需要事先创建好并且具有正确的权限

3. 并行备份大文件
现在Oracle数据库中单个数据文件可以最大到128T，而在以前的版本中RMAN的最小备份单位就是datafile，那么对于以后可能出现的这种超大数据文件，RMAN备份就几乎无法操作了。在11g中，通过backup命令中的SECTION SIZE关键字，我们可以对数据文件指定section了，每个section都作为一个独立单位来处理，每个数据文件可以最多指定256个section。
Section的好处在于，一可以并行备份多个section，提高备份速度；二可以分多个时间分别备份一个大文件的多个section，时间上化整为零，更具有操作性。
4. RMAN Catalog管理性增强
IMPORT CATALOG命令允许我们将一个catalog库中的信息转储到另外一个catalog库，这在以前完全需要手工操作。
推出Virtual Recovery Catalogs概念，这是VPD的实例应用，对于一个集中管理的catalog设置多个用户的虚拟catalog，每个用户只能管理自己的数据，安全性的进一步提高。

alter system switch logfile 是强制日志切换，不一定就归档当前的重做日志文件（若自动归档打开，就归档前的重做日志，若自动归档没有打开，就不归档当前重做日志。）alter system archive log current 是归档当前的重做日志文件，不管自动归档有没有打都归档。主要的区别在于:ALTER SYSTEM SWITCH LOGFILE对单实例数据库或RAC中的当前实例执行日志切换;而ALTER SYSTEM ARCHIVE LOG CURRENT会对数据库中的所有实例执行日志切换。alter system archive log current 这样后就可以将所有的归档
都备份出来了。

http://www.itpub.net/viewthread.php?tid=939202&pid=9679540&page=1&extra=#pid9679540

ASM--如何从远程客户端连接到ASM类型的实例
ASM--How to connect to ASM instance from a remote client
===========================================================
作者: vongates(http://vongates.itpub.net)
发表于: 2008.02.13 13:59
分类: 10gR2
出处: http://vongates.itpub.net/post/2553/454944
---------------------------------------------------------------
随着ASM被广泛的使用，最近重新整理一些管理ASM实例的脚本方便以后使用，之前习惯在主机上做脚本测试，所以没有想过在客户端来查询管理ASM实例。我们都知道ASM类型的实例，在初始化文件中需指定instance_type=ASM,在ASM实例建立后需手工设定listener，可以通过修改listener.ora静态注册或修改tnsnames.ora和local_listener初始化参数动态注册来完成。动态注册需在TNS的connect_data字串加入(UR=A)，此字串到11G不再需要（还没有来得及测试呢），对UR＝A用于在listener中的服务状态为blocked service时建立通信。
1.TNS配置文件的设定
$ vi tnsnames.ora
"tnsnames.ora" 63 lines, 1360 characters
# tnsnames.ora Network Configuration File: /opt/oracle/10.2.0/db/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_+ASM =
(ADDRESS = (PROTOCOL = TCP)(HOST = ASMTEST01)(PORT = 1598))
ASM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ASMTEST01)(PORT = 1598))
(CONNECT_DATA =
(SERVER = DEDICATED)
(UR=A)
(SERVICE_NAME = +ASM)
)
)
2.listener在初始化参数据的设定
SQL> show parameter list
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string LISTENER_+ASM
3.连接测试OK
SQL> conn sys@asm as sysdba
Enter password:
Connected.
SQL>

http://www.itpub.net/thread-934862-1-1.html

In SQL, the term cardinality refers to the uniqueness of data values contained in a particular column (attribute) of a database table.
When dealing with columnar value sets, there are 3 types of cardinality: high-cardinality, normal-cardinality, and low-cardinality.
High-Cardinality
High-cardinality refers to data table column values that are very uncommon. High-cardinality column values are typically identification numbers, email addresses, or user names. An example of a data table column with high-cardinality would be a USERS table with a column named USER_ID. This column would contain unique values of 1-n. Each time a new user is created in the USERS table, a new number would be created in the USER_ID column to identify them uniquely. Since the values held in the USER_ID column are unique, this column's cardinality type would be referred to as high-cardinality.
Normal-Cardinality
Normal-cardinality refers to data table column values that are somewhat uncommon. Normal-cardinality column values are typically names, street addresses, or vehicle types. An example of a data table column with normal-cardinality would be a CUSTOMER table with a column named LAST_NAME. This column would contain the last names of customers. While some people have common last names, such as Smith, others have uncommon last names. Therefore, an examination of all of the values held in the LAST_NAME column would show "clumps" of names in some places (e.g.: a lot of Smith's ) surrounded on both sides by a long series of unique values. Since there is a variety of possible values held in this column, its cardinality type would be referred to as normal-cardinality.
Low-Cardinality
Low-cardinality refers to data table column values that are not very unusual. Low-cardinality column values are typically status flags, boolean values, or major classifications such as gender. An example of a data table column with low-cardinality would be a CUSTOMER table with a column named NEW_CUSTOMER. This column would contain only 2 distinct values: Y or N, denoting whether the customer was new or not. Since there are only 2 possible values held in this column, its cardinality type would be referred to as low-cardinality.
Retrieved from "http://en.wikipedia.org/wiki/Cardinality_%28SQL_statements%29"

http://www.itpub.net/thread-915530-1-1.html

今天一上班，大量的用户反映系统缓慢，查询等待时间主要是
read by other session，gc cr multi block request。google想查询read by other session主要是什么问题，也没有发现有用的线索。 内联网络地址相互对ping，发现网络延迟很大，到达24ms，怀疑网线没有接好，接入一台笔记本到内联的网段，发现其中的一台机器网络延迟很大，关闭其实例，重新更换网线以后，开机再相互对ping，一些Ok，整个系统恢复正常。

 查看全文

To transfer all personalized settings
Install Toad on the new machine.
* Copy the Toad for Oracle|User Files folder from your old machine to the new machine, making sure the file structure remains the same

在帮助文档里面刚看到的，呵呵这个功能找了好久~~
希望对大家有用