近期oracle upgrade book and rac manual 手记

1,whenever sqlerror exit --表示当在sqlplus中执行命令不正确时，马上退出sqlplus
  whenever oserror exit  --同上
2,set numwidth 30  --配置number类型的列显示宽度
3,rem(remark) --注解sql
  --下列/*...*/表示注解多行语句
  /*
  select * from v$session
  */

  参看此例：
    SELECT * FROM hr.employees
    WHERE job_id LIKE '%CLERK'; /* Includes only clerks. */
4，--同上表注解

B Migrating from Server Manager to SQL*Plus  ---to the part

5,base directory 不能包含空格（11g和10g)
6,sqlplus下的set sqlblanklines on就是运行sql语句中出现空行，off不允许出现空行
7，续行符（sqlplus中）
   如下：
SELECT employee_id, first_name, last_name FROM hr.employees -
WHERE job_id LIKE '%MAN';

8,为了转义特殊字符如：&等
在sqlplus中可采用如下配置
set escape \
create table "emp\&tab"(a int,b int);

或者
set define off
create table "emp&tab"(a int,b int);

9,保存数据库schema的统计，以便用于导回数据库
Creating a Statistics Table


EXEC DBMS_STATS.DELETE_SCHEMA_STATS('SYS');--删除sys用户的统计信息
EXEC DBMS_STATS.IMPORT_SCHEMA_STATS('SYS','dictstattab');--从统计表dictstattab中导入统计信息到sys用户

To run the following script, connect to the database AS SYSDBA using SQL*Plus.

spool sdict

grant analyze any to sys;--授权

exec dbms_stats.create_stat_table('SYS','dictstattab');--先构建一个统计用于保存sys用户的统计导出信息

exec dbms_stats.export_schema_stats('WMSYS','dictstattab',statown => 'SYS');--导出统计信息，以wmsys用户，下同
exec dbms_stats.export_schema_stats('MDSYS','dictstattab',statown => 'SYS');
exec dbms_stats.export_schema_stats('CTXSYS','dictstattab',statown => 'SYS');
exec dbms_stats.export_schema_stats('XDB','dictstattab',statown => 'SYS');
exec dbms_stats.export_schema_stats('WKSYS','dictstattab',statown => 'SYS');
exec dbms_stats.export_schema_stats('LBACSYS','dictstattab',statown => 'SYS');
exec dbms_stats.export_schema_stats('OLAPSYS','dictstattab',statown => 'SYS');
exec dbms_stats.export_schema_stats('DMSYS','dictstattab',statown => 'SYS');
exec dbms_stats.export_schema_stats('ODM','dictstattab',statown => 'SYS');
exec dbms_stats.export_schema_stats('ORDSYS','dictstattab',statown => 'SYS');
exec dbms_stats.export_schema_stats('ORDPLUGINS','dictstattab',statown => 'SYS');
exec dbms_stats.export_schema_stats('SI_INFORMTN_SCHEMA','dictstattab',statown => 'SYS');
exec dbms_stats.export_schema_stats('OUTLN','dictstattab',statown => 'SYS');
exec dbms_stats.export_schema_stats('DBSNMP','dictstattab',statown => 'SYS');
exec dbms_stats.export_schema_stats('SYSTEM','dictstattab',statown => 'SYS');
exec dbms_stats.export_schema_stats('SYS','dictstattab',statown => 'SYS');

spool off

10,晚上根据网速，查看blog中关于rac的配置手册，对照官方手续，deploy rac(for solaris x86),请关注：共享存储这块，再查看下虚拟ip(vmware net1是否可以用于rac)


11,开始学习oracle10g管理员手册

自动调优只支持没有启用autoextend 的固定尺寸的undo tablespace

支持在线segment shrink(包含：lob segment,iot overflow segment等）

scheduler支持一种新的类型：chain,一组程序集合，达到一个目标

oracle10.1 enhanced features:

resumeable_timeout可配置继续空间分配，设置（在所有会话）一个超时

支持跨resetlogs恢复的简化，通过archive_log_format,增加了resetlog_id

v$sgainfo引入，有resizeable及bytes列，可以看到一行表明还有多少空闲sga

dbms_file_transfer可以在文件系统与asm间传输数据

下列两个文件用于简化配置环境变量，适用于unix
oraenv and coraenv, that can be used to easily set these environment variables


查看数据库组件的发布号
select * from product_component_version;


conn sys/system as sysoper

--sysoper角色权力如下：
不可以drop database
      进行不完全恢复
      查看dba字典及动态视图v$session
show user  --显示为public


os认证优先于password file认证

sysoper角色对应于os认证，unix中的组oper,windows为ora_oper，sysdba同理

remote_login_passwordfile=shared --alter user sys identified by system提示出错,用于rac有用或多个数据库时

v$pwfile_users --联上

awr一小时取样一次,采样存储7天


看到---creating an oracle database

1,昨天rac安装,同时查自己的blog,及vmware关于ocr and voting disk配置
2,如rac安装成功，测试各项启停rac组件命令
3,再者添加rac节点（先看文档）



20091209 星期二学习笔记--rac

1,以前版本叫crs,现在叫oracle clusterware
2,cvu验证：集群，rac组件，比如，共享存储，网络配置，系统条件，用户组及用户，也可以用cvu进行rac安装前后的检测，进行错误检测及节点安装前后检测，
3，oracle load balancing advisory帮助您在分配资源间均摊资源，对于连接rac应用;
  应用通过订阅fan event或使用整合客户端（它使用运行时连接负荷均衡特性）
4，改变归档，只能任何rac未open或者处于mount


5,gcs_server_processes ---静态参数，指定rac实例间的global cache service进程的数量

6,oracle cluster可以让单实例实现ha功能
7,voting disk:节点间的关系
  ocr:节点间的配置信息
8,oracle推荐使用千m网作为interconnect,udp协议
9,oracle clusterware进程相关
  css 管理节点配置，通过控制哪些节点是集群中的，当节点增删通知集群，当你用第三方时，它和你的集群来管理节点关系信息
  crs 管理集群的高可用性，它根据ocr配置管理集群资源，它控制实例，监听，当某些组件出错，会检测并自动修复重启，通常会5次重启尝试
  evm 由它发布或传递由crs创建的事件
  ons(oracle notification service)对于联系fast application notification events的发布和订阅者
  racg--当fan事件发生，运行服务调用脚本。扩展集群来支持相关的oracle条件
  process monitor daemon(oprocd) 此进程锁定于内存中，监控集群，提供i/0 fencing,如果此进程不正常会导致节点重启，在linux使用hangchecdk
 
10，可以最多支持100个实例

11,cache fasion:传递块于不同的rac instance间，
lms --global cache service process
lmd --global enqueue service daemon
lmon --global enqueue service monitor
lck0 -instance enqueue process
当使用nas(network attached storage),要配置第二个private network,

11,在rac中多用反向键索引，当高并发插入（比如是日期列）特别有用

12，当用hash partitioning,不能用index range scan

13,rac相关工具
oem
cvu
srvctl
crsctl
oifcfg
ocfconfig

14,gv$ view,使用catclustdb.sql构建它


--看到services in oracle database 10g
Introduction to Oracle Clusterware and Oracle RAC Administration and Deployment


15,可以用dd if=backup_vote f=voting_disk_name  --恢复voting disk
  可以采用如下，把备份导回到voting disk  --notes:run as root user
   crsctl delete css votedisk path
   crsctl add css votedisk path

但进行以上配置时，先让ocssd进程停掉，当然你可以加上force,但如果有活动的节点，会损坏配置



16,ocrconfig不影响未运行的cluster node (notes:run as root user)
   ocrconfig -replace ocr destination_file or disk  --- 添加ocr
   ocrconfig -replace ocrmirror destination_file or disk --add an ocr mirror

17,当某些rac node down时，运行ocrconfig -repair在down node,以使停机node在重启后加入到cluster中

18,ocrconfig -repair --只能运行在未运行cluster进程的rac node

19,每4小时，自动备份ocr;在unix生成的默认ocr backup file在：crs_home/cdata/cluster_name
   ocr坏，先运行ocrcheck
20,恢复ocr
   a,ocrconfig -showbacup --查backup的ocr
   b,init. crs stop --stop oracle clusterware process
   c,ocrconfig -restore file_name --file_name取自上述a节结果
   d,init.crs start --restart crs
   e,cluvfy comp ocr -n all [-verbose] --verify ocr integrity

21，诊断ocr相关问题
  using ocrdump utility
    write the ocr contents to a file so that you can examine the ocr content
  using ocrcheck utility.,
    verify ocr integrity
22 ,cluster_interconnects --specify alternative interconnect ip,be separated by :
   if use cluster_interconnects,failover and failback are disabled
   notes:in aix,failover and failback and cluster_interconnects are not supported


23,dbca -silent -nodeList nodelist -configureASM -asmpassword asm_pwd [-diskList disk_list] [-redundancy redundancy_option] [-diskGroupName dgname] [-diskString disk_discovery_string] [-recoveryGroupName recovery_dgname] [-recoveryRedundancy redundancy_option]


24,administering asm instances with srvctl
srvctl add asm -n nodename -i asm_instance_name -o oracle_home  --add confiruation about an existing asm instance
       remove  --remove asm instance
       enable  --enable asm instance
       disable --same as the below
      
srvctl start asm -n nodename -i asm_instance_name -o start_options -c connect_str|q
       stop
srvctl config asm -n nodename ---show the configuration of an asm instance
srvctl status asm -n nodename --obtain the status of an asm instance


srvctl stop|start instance -d orcl -i "orcl1,orcl2"  -o immediate -c "sysback/oracle as sysoepr"

srvctl start database -d orcl -o mount

定制oracle rac db
两类模式：automatic and manual policy

srvctl config database -d databasename -a --display the current policy
srcctl modify database -d databasename -y policy_name  --modify current policy
srvctl add database -d databasename -y policyname  ---the default is automatic,set the current policy to ocr key


25,把spfile参数回退默认
alter system reset open_cursors scope=spfile sid='*|prod1"


看到
Introduction to Workload Management


26,resource profiles自动创建，当构建service时，它定义集群如何可管理服务，当首选实例down,service fail over to which instance;also define dependencies for the instance and the database;
   this is,if you stop a database,then the instances and service are automatically stopped in the currect order;
   services are integrated with resource manager which enables you to restrict the resources that are used by a service within an instance.


27,oracle net service provides connect load balancing ,you can define the connection load balacing goal(clb_goal).
   taf policy(failover_method and failover_type)
   oracle rac uses fan to notify applications about configuration changes and the current
   service level that is provided by each instance where the service is enabled;

28,fan:has two methods for publishing event to clients,the oracle notification service(ons),
   which is used by jdbc client including oracle application server 10g and stream,ap

29,to enable the load balancing advisory,set the GOAL parameter on the service.

30,default service connections:
   sys$background is used by the background processes only
   sys$users is the default service for user session that are not associated with any application service

31,connection load balancing
   oracle net services provices the ablity to balance client connections across the instance in an rac .
   there are two types of load balancing that you can implement:client-side and server-side
   load balancing.
   client-sise load balancing balances the connection requests across the listeners
   with server-side load balancing,the listener directs a connection request to the best instance correctly providing the service by using the load balancing advisory.


32,in  an rac ,client connections should use both types of connection load balancing.

   long  ---for applications that have long-lived connections
     execute dbms_service.modify_service(service_name=>'postman',clb_goal=>dbms_service.clb_goal_long);
   short ---for short-lived connections
     the same as the below

taf policy apply to service
session can use service

33,client-side load balancing is defined in your client connection defination by settting the paramneter load_balance=on
   when you create oracle rac db with dbca,by default configure and enable server-side load balancing


34,fan --会对应用的状态转化及集群配置变化，通知应用（node,application,service and so on)
   you can take advantage of fan events in the following three ways:
   1,your application need to no change if you use an integrated oracle client.


35,configuring your environment to use the load balancing advisory
exec dbms_service.modify_service(service_name=>'oe',goal=>dbms_service.goal_service_time,clb_goal=>dbms_service.clb_goal_short);


36,enabling distributed transaction processing for services --dtp for service
srvctl add service -d crm -s xa-01 -r rac01 -a rac02,rac03  ---service can migrate

37,use data from the gv$active_services to determine whether to do this

38,the characteristics of a service include:
a unique global name to identify the service
a net service anme that a client use to connect to the service
the preferred instance
the available instances
a service goal
an indicator that determines whether the service is used for dtp
an indicator that determines whether oracle rac availability are sent to oci and odp.net that are integrated to receive them through advaned quueueing

39,use instance_groups and parallel_instance_groups to restrict parallel execution processing to a subset of instances in an rac db


40,administering services with srvctl
srvctl add service -d databasename -s servicename -r prefefrred_list -a available_list -p TAF_policy
       start|stop -i instancename -o start_options -c connect_strings -q
       enable|disable
srvctl relocate service -d apps -s crm -i apps1 -t apps3 --reloate service crm from instance apps1 to instance apps3

srvctl status service -d apps -s crm --obtaining the statuses of services

srvctl config service -d apps -s crm -a ---obtaining confiruation of services


42,enable module and action monitoring using dbms_monitor
exec dbms_monitor.serv_mod_act_enable(service_name=>'erp',module_name=>'payroll',action_name=>'exceptions pay');

use the dba_enabled_aggregations to verify that you have enabled monitoring


you can see the call speed for each service,module,and action name at each database instance using the v$serv_mod_act_stats


43,
SET PAGESIZE 60 COLSEP '|' NUMWIDTH 8 LINESIZE 132 VERIFY OFF FEEDBACK OFF
COLUMN service_name FORMAT A20 TRUNCATED HEADING 'Service'
COLUMN begin_time HEADING 'Begin Time' FORMAT A10
COLUMN end_time HEADING 'End Time' FORMAT A10
COLUMN instance_name HEADING 'Instance' FORMAT A10
COLUMN service_time HEADING 'Service Time|mSec/Call' FORMAT 999999999
COLUMN throughput HEADING 'Calls/sec'FORMAT 99.99
BREAK ON service_name SKIP 1
SELECT
    service_name
  , TO_CHAR(begin_time, 'HH:MI:SS') begin_time
  , TO_CHAR(end_time, 'HH:MI:SS') end_time
  , instance_name
  , elapsedpercall  service_time
  ,  callspersec  throughput
FROM 
    gv$instance i    
  , gv$active_services s    
  , gv$servicemetric m
WHERE s.inst_id = m.inst_id 
  AND s.name_hash = m.service_name_hash
  AND i.inst_id = m.inst_id
  AND m.group_id = 10
ORDER BY
   service_name
 , i.inst_id
 , begin_time ;




44,
Services and Thresholds Alerts Example

To check the thresholds for the payroll service, use the AWR report. You should record output from the report over several successive intervals during which time the system is running optimally. For example, assume that for an Email server, the AWR report runs each Monday during the peak usage times of 10am to 2pm. The AWR report would contain the response time, or DB time, and the CPU consumption time, or CPU time, for calls for each service. The AWR report would also provide a breakdown of the work done and the wait times that are contributing to the response times.

Using DBMS_MONITOR, set a warning threshold for the payroll service at 0.5 seconds and a critical threshold for the payroll service at 0.75 seconds. In Oracle Database 10g, you must set these thresholds at all instances within an Oracle RAC database. You can schedule actions using Enterprise Manager jobs for alerts, or you can schedule actions to occur programmatically when the alert is received. In this example, thresholds are added for the servall service and set as follows:

EXECUTE DBMS_SERVER_ALERT.SET_THRESHOLD(
METRICS_ID => DBMS_SERVER_ALERT.ELAPSED_TIME_PER_CALL
, warning_operator => DBMS_SERVER_ALERT.OPERATOR_GE
, warning_value => '500000'
, critical_operator => DBMS_SERVER_ALERT.OPERATOR_GE
, critical_value => '750000'
, observation_period => 30
, consecutive_occurrences => 5
, instance_name => NULL
, object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_SERVICE
, object_name => 'servall');

Verify the threshold configuration using the following SELECT statement:

SELECT METRICS_NAME, INSTANCE_NAME, WARNING_VALUE, CRITICAL_VALUE, OBSERVATION_PERIOD FROM dba_thresholds ;

Enable Service, Module, and Action Monitoring

You can enable performance data tracing for important modules and actions within each service. The performance statistics are available in the V$SERV_MOD_ACT_STATS view. As an example, set the following:

    *

      Under the ERP service, enable monitoring for the exceptions pay action in the module, payroll.
    *

      Under the ERP service, enable monitoring for the all actions in the module, payroll.
    *

      Under the HOT_BATCH service, enable monitoring for the all actions in the module, posting.

EXECUTE DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE(service_name => 'erp', module_name=> 'payroll', action_name => 'exceptions pay');
EXECUTE DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE(service_name => 'erp', module_name => 'payroll', action_name => NULL);
EXECUTE DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE(service_name => 'hot_batch', module
_name =>'posting', action_name => NULL);

Verify the enabled service, module, action configuration with the following SELECT statement:

COLUMN AGGREGATION_TYPE FORMAT A21 TRUNCATED HEADING 'AGGREGATION'
COLUMN PRIMARY_ID FORMAT A20 TRUNCATED HEADING 'SERVICE'
COLUMN QUALIFIER_ID1 FORMAT A20 TRUNCATED HEADING 'MODULE'
COLUMN QUALIFIER_ID2 FORMAT A20 TRUNCATED HEADING 'ACTION'
SELECT * FROM DBA_ENABLED_AGGREGATIONS ;

The output might appear as follows:

AGGREGATION            SERVICE                MODULE        ACTION
------------           --------------------   ----------    -------------
SERVICE_MODULE_ACTION  ERP                    PAYROLL       EXCEPTIONS PAY
SERVICE_MODULE_ACTION  ERP                    PAYROLL
SERVICE_MODULE_ACTION  HOT_BATCH              POSTING



45,
Configuring the RMAN Snapshot Control File Location

The snapshot control file is a temporary snapshot control file that RMAN creates to
 re-synchronize from a read-consistent version of the control file. RMAN only needs a snapshot control file when re-synchronizing
 with the recovery catalog or when making a backup of the current control file. In Oracle RAC, the snapshot control file is only needed on the nodes
on which RMAN performs backups; the snapshot control file does not need to be globally available to all instances in an Oracle RAC environment.


SHOW SNAPSHOT CONTROLFILE NAME;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '$ORACLE_HOME/dbs/scf/snap_prod.cf';



46,
Example 7-1 Example Configuration for the initialization parameters file

sid1.log_archive_dest_1 = (location=/arc_dest_1)
sid2.log_archive_dest_1 = (location=/arc_dest_2)
sid3.log_archive_dest_1 = (location=/arc_dest_3)

If you do not use a cluster file system, then the archived redo log files cannot be on raw devices. This is because raw devices do not enable sequential writing of consecutive archive log files.



看到
8 Managing Backup and Recovery



47,
definning network interfaces with oifcfg

network interface specification is in the form. of:
interface_name/subnet:interface_type
qfe0/204.152.65.32:cluster_interconnect

oifcfg -help

nodename --output from olsnodes
if_name --name by which the interface is configured in the system
subnet --subnet address of the interface
if_type --type of interface:public or cluster_interconnect

you can lit the interface
oifcfg iflist  ---interface name and subnets

oifcfg getif [[-global|-node nodename] [-if if_name[/subnet] [-type if_type]

oifcfg setif -global hme0/139.185.141.0:cluster_interconnect

without the -node or -global options,deletes either the given interface or all of the global and node-specific interfaces on all of the nodes in the cluster
oififg delif -global qfe0/204.152.65.0
or
oifcfg delif -global


48,changing vip addresses
 stop all db and asm instances
 stop the listeners,and node applications using the srvctl stop nodeapps
 cluvfy comp nodeconn -n all
 srvctl modify nodeapps,use crs_stat to identify all active node applications
 restart all of the instance and node applications that you stopped in step1 and 2
 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-622159/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-622159/