生活的方向

现在的状态很好，知道自己擅长什么，要学什么，爱做什么。
给自己定下了几个目标，虽然有些难，但是通过努力都是可以达到的。
现在这种状态真好。
哈哈，张磊加油！！

10G B14211-01

今天起开始阅读Oracle® Database Performance Tuning Guide 10g Release 2 (10.2)
这篇主要讲述10G性能调整的方方面面，虽然有关性能的话题总是牵连广泛，比如底层硬件，CPU，内存，网络，存储，firmware版本，RAID，OS，内核版本，内核参数....但是这里主要关注如何在其他因素确定的情况下，调整Oracle的性能。

最近在干什么

最近我在看10g RMAN的文档，如何利用rman进行高级复制，重建数据库。
还有就是对照OCM的考试大纲一个一个的练习和做实验。
准备今年内参加OCM的考试。
英语啊英语，我的口语怎么练习呢？
很想背一背，但是找不到好的材料。

记录一个批处理文件的写法

do.bat
@echo Straing load COC data from ADE tab flat file
@for /f %%A in ('"dir /a:d /b "') do @dir %%A /s /b >> temp.tmp
@rem 这里首先取到当前目录下面的所有子目录中的文件（只有一层），保存在temp.tmp里面
@find "TEX" temp.tmp > temp.names
@rem 过滤temp.tmp的内容，之保留带有TEX结尾的文件。
@for /f "skip=2" %%A in ('"type temp.names "') do @call extract %%A
@rem 用extract脚本收取每个文件当中的内容

@REM *********************
@for /f %%A in ('"dir /a:d /b "') do @rd %%A /q /s
@del temp.tmp
@del temp.names
@del temp.log
@del temp.bad
@rem 收尾工作
@echo wwwwwwwwwwwwwwwwork is doneeeeeeeeeeeeeeeeee

extract.bat
@echo in
@sqlldr userid=COC_COLLECTION/coc@mycim control=extractData.ctl data=%1 skip=200 log=temp.log bad=temp.bad
@sqlldr userid=COC_COLLECTION/coc@mycim control=extractParameter.ctl data=%1 log=temp.log bad=temp.bad
@sqlldr userid=COC_COLLECTION/coc@mycim control=extractOrder.ctl data=%1 log=temp.log bad=temp.bad
@sqlplus COC_COLLECTION/coc@mycim @run.txt
@echo out

extractData.ctl
OPTIONS (ERRORS=10000, SILENT=(ALL), READSIZE=2097152 )
load data
infile *
replace INTO TABLE data
(
CLASSNUM position(1:7) "case when to_char(:CLASSNUM) like '%****%' then null else to_number(:CLASSNUM) end",
AMOUNT position(*:16) "case when to_char(:AMOUNT) like '%****%' then null else to_number(:AMOUNT) end",
MAX position(*:24) "case when to_char(:MAX) like '%****%' then null else to_number(:MAX) end",
MIN position(*:33) "case when to_char(:MIN) like '%****%' then null else to_number(:MIN) end",
MEAN position(*:42) "case when to_char(:MEAN) like '%****%' then null else to_number(:MEAN) end",
DELTA position(*:51) "case when to_char(:DELTA) like '%****%' then null else to_number(:DELTA) end",
MAX_DEV position(*:60) "case when to_char(:MAX_DEV) like '%****%' then null else to_number(:MAX_DEV) end",
STD_DEV position(*:69) "case when to_char(:STD_DEV) like '%****%' then null else to_number(:STD_DEV) end",
CV position(*:78) "case when to_char(:CV) like '%****%' then null else to_number(:CV) end"
)

extractParameter.ctl
OPTIONS (ERRORS=10000, SILENT=(ALL), READSIZE=2097152 )
LOAD DATA
INFILE *
REPLACE
CONTINUEIF THIS (1) = '"'
INTO TABLE para
(
a terminated by '"' "case when to_char(:a) is null then 'notmeasured' else to_char(:a) end",
b terminated by 'Total'
)

extractOrder.ctl
Load data
infile *
replace
INTO TABLE po
WHEN (2:12) = 'Plant Order'
(
PO position(13:100)
)

X86_64笔记本上安装Red hat AS4u4 + Oracle 10g R2 + ASM

这篇文章主要向你介绍，如何在只有一块物理硬盘的linux环境下，安装带ASM的Oracle 10g。

首先用grub引导机器，进入grub的命令行。
kernel (hd0,10)/as4/vmlinuz
Initrd (hd0,10)/as4/initrd.img
boot

然后从硬盘选择iso镜像，安装Red hat AS4u4。

Oracle 10g的安装选择silent模式。
1. 省略前期的内核配置，用户权限配置，用户环境变量配置。
2. 录制安装脚本
$./runInstaller –record –destinationFile /tmp/install_scripts.rsp
进入安装界面后，按照需求选择，最后一步的时候cancel掉。(注意选择software only)
3. 使用录制脚本安装
$./runInstaller –silent –force –ignoreSysprereqs –responseFile /tmp/install_scripts.rsp
4. Post installation actions
步骤3结束以后，需要运行两个脚本。
$ORACLE_BASE/oraInvntory/orainstRoot.sh
$ORACLE_HOME/root.sh
至此Oracle 10g R2安装结束。
5. 卸载
$./runInstaller –silent –deinstall –removeallfiles –removeAllPatches –responseFile /tmp/install_scripts.rsp

ASM
Automatic Storage Management是专门为Oracle数据库提供的一个逻辑卷管理器，提供数据条带化，磁盘IO负载均衡以及数据冗余。
通常情况下，ASM建立在若干硬盘之上，这样可以保证数据冗余以及IO负载均衡。
由于是在笔记本上搭建这样的环境，我只有一个硬盘，那么就需要通过loopback devices来模拟多个硬盘。
下面介绍整个ASM的安装过程
1. 制作“本地磁盘”
创建一个目录
mkdir /asmdisks
创建若干用来模拟本地硬盘的大文件。由于在linux下，touch命令产生的稀疏文件，所以这里使用dd命令。
dd if=/dev/zero of=/asmdisks/disk1 bs=1024k count=1000
dd if=/dev/zero of=/asmdisks/disk2 bs=1024k count=1000
dd if=/dev/zero of=/asmdisks/disk3 bs=1024k count=1000
dd if=/dev/zero of=/asmdisks/disk4 bs=1024k count=1000
dd if=/dev/zero of=/asmdisks/disk5 bs=1024k count=1000
我在这里生成了5个1GB的文件，并且全部用0来填充。losetup命令可以将这5个大文件‘变成‘5个本地磁盘。
losetup /dev/loop1 /asmdisks/disk1
losetup /dev/loop2 /asmdisks/disk2
losetup /dev/loop3 /asmdisks/disk3
losetup /dev/loop4 /asmdisks/disk4
losetup /dev/loop5 /asmdisks/disk5
现在就可以对这5个设备进行格式化了，使用任何一种我们喜欢的文件系统。

2. 安装ASM类库
ASM的安装需要Oracle提供的asm类库支持，可以在这里http://www.oracle.com/technology/tech/linux/asmlib/index.html 下载到。下载的时候注意自己机器的配置，用uname -a查看。
oracleasm-support.rpm
oracleasm-2.6.9-42-x86_64.EL.rpm
oracleasmlib.rpm
为了让机器每次重启的时候，都让系统自动加载ASM并配置好的权限，我们需要运行以下命令。
/etc/init.d/oracleasm configure
oracle
oinstall
y
y

3. 配置ASM磁盘组
/etc/init.d/oracleasm createdisk ASMD1 /dev/loop1
/etc/init.d/oracleasm createdisk ASMD2 /dev/loop2
/etc/init.d/oracleasm createdisk ASMD3 /dev/loop3
/etc/init.d/oracleasm createdisk ASMD4 /dev/loop4
/etc/init.d/oracleasm createdisk ASMD5 /dev/loop5

4. 创建ASM实例
传统的逻辑卷都有逻辑卷管理器，ASM的逻辑卷管理器就是一种Oracle实例，但是一种特殊的实例。没有buffer cache和log buffer因为它不需要open一个database。ASM实例有large pool和shared pool以及一些特殊的后台进程
以root身份运行
/oracle/10g/bin/localconfig add

换成oracle用户，编辑+ASM实例的参数文件
INSTANCE_TYPE=ASM
DB_UNIQUE_NAME=+ASM
LARGE_POOL_SIZE=8M
ASM_DISKSTRING=ORCL:*

启动ASM实例
export ORACLE_SID=+ASM
sqlplus / as sysdba

create diskgroup DGROUP1 normal redundancy
disk 'ORCL:ASMD1','ORCL:ASMD2','ORCL:ASMD3',
'ORCL:ASMD4','ORCL:ASMD5';

在/etc/rc.local中加入如下内容：
/sbin/losetup /dev/loop1 /asmdisks/disk1
/sbin/losetup /dev/loop2 /asmdisks/disk2
/sbin/losetup /dev/loop3 /asmdisks/disk3
/sbin/losetup /dev/loop4 /asmdisks/disk4
/sbin/losetup /dev/loop5 /asmdisks/disk5
/etc/init.d/oracleasm createdisk ASMD1 /dev/loop1
/etc/init.d/oracleasm createdisk ASMD2 /dev/loop2
/etc/init.d/oracleasm createdisk ASMD3 /dev/loop3
/etc/init.d/oracleasm createdisk ASMD4 /dev/loop4
/etc/init.d/oracleasm createdisk ASMD5 /dev/loop5

5. 创建数据库
编辑参数文件init.ora
db_name=smart
sga_target=128M
db_create_file_dest=+DGROUP1
这里要注意，sga的不能太小，否则在创建数据库的时候会出现shared pool太小的错误。设置db_create_file_dest说明我们使用了OMF，Oracle Managed Files。
sqlplus “/ as sysdba”
create spfile from pfile;
shutdown immediate
startup nomount
create database;
create temporary tablespace temp;
create undo tablespace undotbs1;
@?/rdbms/admin/catalog
@?/rdbms/admin/catproc
alter database default temporary tablespace temp;
alter system set undo_tablespace=undotbs1 scope=spfile;
alter system set undo_management=auto scope=spfile;
startup force

6. 维护ASM
select name from v$datafile;
create tablespace DATA;
alter database datafile '+DGROUP1/smart/datafile/undotbs1.262.591694817' resize 120m;
alter database datafile '+DGROUP1/smart/datafile/undotbs1.262.591694817' autoextend off;
alter database datafile '+DGROUP1/smart/datafile/undotbs1.262.591694817' autoextend on next 10m;

7. 维护ASM文件
export ORACLE_SID=+ASM
asmcmd
添加ASM文件
$ su - root
# dd if=/dev/zero of=/asmdisks/disk6 bs=1024k count=1000
# /sbin/losetup /dev/loop6 /asmdisks/disk6
# /etc/init.d/oracleasm createdisk ASMD6 /dev/loop6

alter diskgroup DGROUP1 add disk 'ORCL:ASMD6';
alter diskgroup dgroup1 drop disk asmd6;

自我感觉，ASM就是一个专门为Oracle服务的，支持Oracle集群的软Raid。

参考 http://www.dizwell.com/prod/node/34

CLOB,LONG,NCLOB在SQL*PLUS的显示长度

SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','BLANK') FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE','BLANK')
--------------------------------------------------------------------------------

  CREATE TABLE "TEST"."BLANK"
   (    "A" VARCHAR2(100)
   ) PCTFREE 10 PCTUSED 4


SQL> SET LONG 2000
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','BLANK') FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE','BLANK')
--------------------------------------------------------------------------------

  CREATE TABLE "TEST"."BLANK"
   (    "A" VARCHAR2(100)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRAN
S 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEX
TENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS
1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"


DBMS_METADATA.GET_DDL('TABLE','BLANK')
--------------------------------------------------------------------------------


SQL>

Physical Standby Database Creation and Simple Management

Compared with RAC environment, standby database is much less complicated to set up.
A process of standby database creation generally contains these steps:
  1.  Force logging on primary database
  2.  Enable primary archive log mode
  3.  Generate pfile and shutdown primary database
  4.  Transport data files to standby node
  5.  Prepare both primary and standby parameter files
  6.  Standby instance service and password file creation
  7.  Set ORACLE_SID variable properly
  8.  Configure listeners and TNS names for both kind of nodes
  9.  Standby database control file generation
  10.  Startups

Here come more detailed processes.
1. Force logging on primary database
Force logging makes database running in logging-everything state, and by pass nologging clause.
  SQL> ALTER DATABASE FORCE LOGGING;

2. Enable primary archive log mode
Archive log mode is required to primary node but this is not prerequisite of standby nodes.
  SQL> SHUTDOWN IMMEDIATE
  SQL> STARTUP MOUNT
  SQL> ALTER DATABASE ARCHIVELOG;
  SQL> ALTER DATABASE OPEN;

3. Generate pfile and shutdown primary database
  SQL> CREATE PFILE=’D:\INITSMART.ORA’ FROM SPFILE;
  SQL> SHUTDOWN IMMEDIATE

4. Transport data files to standby node
Here you could make use of OS utilities to copy data files from primary node to standby nodes.

5. Prepare both primary and standby parameter files
Parameter file configuration is key step of the whole standby environment set up. Each node should have known others’ name, archive destination, path name convert method and how many processes serve for log archiving etc. Open pfile which I took from step 3, and add it with entries like this:

DB_NAME='smart'
DB_UNIQUE_NAME='smart'
LOG_ARCHIVE_CONFIG='DG_CONFIG=(smart,stb)'
LOG_ARCHIVE_DEST_1=
 'LOCATION=E:\oracle\product\10.2.0\oradata\smart
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=smart'
LOG_ARCHIVE_DEST_2=
 'SERVICE=stb LGWR ASYNC
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) 
  DB_UNIQUE_NAME=stb'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.remote_login_passwordfile='EXCLUSIVE'
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30

FAL_SERVER=stb
FAL_CLIENT=smart
DB_FILE_NAME_CONVERT='stb','smart'
LOG_FILE_NAME_CONVERT='F:\oracle\product\10.2.0\oradata\stb\','E:\oracle\product\10.2.0\oradata\smart\'
STANDBY_FILE_MANAGEMENT=AUTO

On stb node, the scenario is quite similar but we still need some modification to show the critical difference.

DN_NAME='smart'
DB_UNIQUE_NAME='stb'
LOG_ARCHIVE_CONFIG='DG_CONFIG=(smart,stb)'
LOG_ARCHIVE_DEST_1=
 'LOCATION=F:\oracle\product\10.2.0\oradata\stb
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=stb'
LOG_ARCHIVE_DEST_2=
 'SERVICE=smart LGWR ASYNC
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) 
  DB_UNIQUE_NAME=smart'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.remote_login_passwordfile='EXCLUSIVE'
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30

FAL_SERVER=smart
FAL_CLIENT=stb
DB_FILE_NAME_CONVERT='smart','stb'
LOG_FILE_NAME_CONVERT='E:\oracle\product\10.2.0\oradata\smart','F:\oracle\product\10.2.0\oradata\stb'
STANDBY_FILE_MANAGEMENT=AUTO

6. Standby instance service and password file creation
On windows platform, an instance service and password file will be required.
F:\ORACLE\PRODUCT\9.2\DATABASE\> ORADIM –NEW –SID STB –INTPWD ORACLE –STARTMODE M
On platform other than windows, you will also need password file to permit other nodes in standby environment to access local database with sys privilege.
[oracle@database dbs]$ orapwd file=PWDstb.ora password=oracle entries=20 force=y
I once forgot to generate a password file for the “stb” node with the same sys password on “smart”, then “smart” node failed to coordinate with “stb” even it tried thousands times before I shut it down.

7. Set ORACLE_SID variable properly
Windows:
C:\> set oracle_sid=stb
UNIX:
$export ORACLE_SID=STB

8. Configure listeners and TNS names for both kind of nodes
Listener.ora example:
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = F:\oracle\product\10.2.0\db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = whole)
      (ORACLE_HOME = F:\oracle\product\10.2.0\db_1)
      (SID_NAME = whole)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = stb)
      (ORACLE_HOME = F:\oracle\product\10.2.0\db_1)
      (SID_NAME = stb)
    )
  )

C:\> lsnrctl stop
C:\> lsnrctl start

Modify tnsnames.ora and tnsping each other to make sure the oracle net service running okay.

9. Standby database control file generation
On the primary database.
  SQL> STARTUP MOUNT PFILE=’INITSMART.ORA’
  SQL> ALTER DATABASE CREATE STANDBY DATABASE CONTROLFILE AS ‘D:\CONTROL01.CTL’;
  SQL> ALTER DATABASE OPEN;

10. Startups
Copy the standby control file to stb node and:
  SQL> STARTUP MOUNT PFILE=’INITSTB.ORA’
  SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Check out the result of our configuration on primary node with such command:
  SQL> ALTER SYSTEM SWITCH LOGFILE;
See if standby nodes have newly create archive log file.
  SQL> SELECT SEQUENCE# FROM V$ARCHIVED_LOG;

On the circumstance of primary node crash, we need a capable and functional database to take over the task as soon as possible. So, the first step is to finish managed recovery mode.
  SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
  SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
  SQL> SHUTDOWN IMMEDIATE
  Cold backup here.
  SQL> STARTUP

On the circumstance to open the standby database in read-only mode, in order to query something in the stand by database, we need these steps to achieve our goal.
  SQL> RECOVER CANCEL / RECOVER MANAGED STANDBY DATABASE CANCEL;
  SQL> ALTER DATABASE OPEN READ ONLY;
  Bring back standby database to manual or managed recovery mode.
  SQL> RECOVER STANDBY DATABASE / RECOVER MANAGED STANDBY DATABASE TIME OUT 15;

Manual switch to standby database:
  Current primary node to standby node:
  SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
  SQL> SHUTDOWN IMMEDIATE
  SQL> STARTUP MOUNT
  SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
  SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

  Current standby node to primary node:
  SQL> ALTER DATABASE TO SWITCHOVER TO PRIMARY;
  SQL> SHUTDOWN IMMEDIATE
  SQL> STARTUP
  SQL> ALTER SYSTEM SWITCH LOGFILE;

TNS-12541

OS:Windows XP pro
Oracle: 10.2.0.1 64bit

listener中

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
  )

其他机器用tnsping这台机器的话，会得到TNS-12541错误，no listener。
用netstat -an发现
TCP    127.0.0.1:1521    0.0.0.0:0    LISTENING
远程机器telnet 192.168.1.8 1521无法连接。

HOST要配置成机器名，才能避免这样的错误。

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = linux)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
  )

这个时候
TCP    0.0.0.0:1521    0.0.0.0:0    LISTENING

折腾了我一个下午啊，thanks acterm。