近期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