继上篇文章，Oracle OCP考试1z0-007系列3:学会使用单行函数后，本篇是系列4，学会使用分组函数。 本篇是1z0-007课程的第四章，主要内容：
1 了解在SQL语句中可以使用的分组函数；
2 学会在SELECT语句中使用GROUP BY从句；
3 学会使用HAVING从句来过滤数据。
本篇内容相对比较难，具体文档和讲义如下：
Lesson04 学会使用分组函数
View more presentations from renguzi
解决一则row cache lock引起的性能故障
收到紧急邮件：说某客户生产系统在今天早上9:30左右，生产环境整个系统操作响应很慢，查询一票单子可能要耗时半分钟，有时候登录界面也卡住很久才响应
。
这是一套10.2.0.5.0的双节点RAC数据库，平时系统运行较为正常，现在突然变慢。好了，对于我这个优化菜鸟来讲，还是从AWR报告入手。下面给出分析步骤和解决办法：
1 从报告头中看到，在数据库出现性能问题的一个小时内，DB Time达到240分钟，(DB Time)/Elapsed=3.93，说明数据库应该是存在问题的。
| Snap Id | Snap Time | Sessions | Cursors/Session | |
|---|---|---|---|---|
| Begin Snap: | 10638 | 05-6月 -12 09:00:00 | 54 | 5.8 |
| End Snap: | 10639 | 05-6月 -12 10:01:03 | 102 | 5.8 |
| Elapsed: | 61.04 (mins) | |||
| DB Time: | 240.11 (mins) |
2 从报告的Load Profile节，看到用户的每秒调用高达680次，说明肯定存在问题了。
Load Profile
| Per Second | Per Transaction | |
|---|---|---|
| Redo size: | 12,939.46 | 6,115.60 |
| Logical reads: | 67,323.18 | 31,819.06 |
| Block changes: | 53.22 | 25.15 |
| Physical reads: | 1.02 | 0.48 |
| Physical writes: | 4.72 | 2.23 |
| User calls: | 679.70 | 321.25 |
| Parses: | 90.49 | 42.77 |
| Hard parses: | 0.35 | 0.16 |
| Sorts: | 1.94 | 0.92 |
| Logons: | 0.08 | 0.04 |
| Executes: | 316.75 | 149.71 |
| Transactions: | 2.12 |
3 继续分析报告，从Top 5中看到排在第一的等待事件是row cache lock,并且该等待事件的平均等待达到2128ms。
ROW CACHE LOCK等待事件是一个共享池相关的等待事件，是由于对于字典缓冲的访问造成的。通常直接的解决办可以通过调大共享池来解决，但是，并非在所有场景下都凑效。
Top 5 Timed Events
| Event | Waits | Time(s) | Avg Wait(ms) | % Total Call Time | Wait Class |
|---|---|---|---|---|---|
| row cache lock | 2,736 | 5,822 | 2,128 | 40.4 | Concurrency |
| CPU time | 4,305 | 29.9 | |||
| gc cr block busy | 2,293 | 2,633 | 1,148 | 18.3 | Cluster |
| gc buffer busy | 1,569 | 1,096 | 698 | 7.6 | Cluster |
| enq: TX – row lock contention | 2,029 | 998 | 492 | 6.9 | Application |
4 继续分析，发现基于时间的统计信息中，加载序列sequence的耗时排在了第二位。
Time Model Statistics
- Total time in database user-calls (DB Time): 14406.5s
- Statistics including the word "background" measure background process time, and so do not contribute to the DB time statistic
- Ordered by % or DB time desc, Statistic name
| Statistic Name | Time (s) | % of DB Time |
|---|---|---|
| sql execute elapsed time | 14,188.01 | 98.48 |
| sequence load elapsed time | 6,900.83 | 47.90 |
| DB CPU | 4,304.59 | 29.88 |
| PL/SQL execution elapsed time | 20.64 | 0.14 |
| parse time elapsed | 10.25 | 0.07 |
| hard parse elapsed time | 6.00 | 0.04 |
| PL/SQL compilation elapsed time | 1.17 | 0.01 |
| hard parse (sharing criteria) elapsed time | 1.07 | 0.01 |
| repeated bind elapsed time | 0.80 | 0.01 |
| hard parse (bind mismatch) elapsed time | 0.64 | 0.00 |
| connection management call elapsed time | 0.31 | 0.00 |
| failed parse elapsed time | 0.00 | 0.00 |
| DB time | 14,406.50 | |
| background elapsed time | 2,115.75 | |
| background cpu time | 20.52 |
5 看到最耗时的竟然是一条再简单不过的SQL语句，SELECT SEQ_NEWID.NEXTVAL FROM DUAL，取序列的值，竟然会如此的耗时？
SQL ordered by Elapsed Time
- Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
- % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100
- Total DB Time (s): 14,406
- Captured SQL account for 223.8% of Total
| Elapsed Time (s) | CPU Time (s) | Executions | Elap per Exec (s) | % Total DB Time | SQL Id | SQL Module | SQL Text |
|---|---|---|---|---|---|---|---|
| 6,910 | 0 | 281 | 24.59 | 47.96 | 1gd7ancd2px8m | FC.EdiService.Import.exe | SELECT SEQ_NEWID.NEXTVAL FROM … |
6 再看字典缓冲区的统计信息：取序列值一共287次，就失败了43.9%，看来的确是取序列值的地方出现问题，也就解释了为什么上一步骤中的分许出的那条SQL会如此耗时，因为差不多有一半的情况下都没有取到序列的值。
Dictionary Cache Stats
- "Pct Misses" should be very low (< 2% in most cases)
- "Final Usage" is the number of cache entries being used
| Cache | Get Requests | Pct Miss | Scan Reqs | Pct Miss | Mod Reqs | Final Usage |
|---|---|---|---|---|---|---|
| dc_awr_control | 65 | 1.54 | 0 | 1 | 1 | |
| dc_database_links | 304 | 0.00 | 0 | 0 | 1 | |
| dc_global_oids | 155 | 0.00 | 0 | 0 | 24 | |
| dc_histogram_data | 74,704 | 0.25 | 0 | 0 | 5,612 | |
| dc_histogram_defs | 71,400 | 0.26 | 0 | 0 | 4,945 | |
| dc_object_ids | 29,398 | 0.01 | 0 | 0 | 1,136 | |
| dc_objects | 3,912 | 0.23 | 0 | 0 | 860 | |
| dc_profiles | 150 | 0.00 | 0 | 0 | 1 | |
| dc_rollback_segments | 17,789 | 0.10 | 0 | 0 | 56 | |
| dc_segments | 8,927 | 0.06 | 0 | 4 | 896 | |
| dc_sequences | 287 | 43.90 | 0 | 279 | 3 | |
| dc_tablespace_quotas | 2 | 50.00 | 0 | 0 | 2 | |
| dc_tablespaces | 8,954 | 0.00 | 0 | 0 | 25 | |
| dc_usernames | 1,082 | 0.00 | 0 | 0 | 8 | |
| dc_users | 13,991 | 0.00 | 0 | 0 | 31 | |
| outstanding_alerts | 326 | 77.91 | 0 | 23 | 54 |
7 到此，解决问题的基本思路已经出来了，通过将序列缓存到内存中，基本可以解决问题。通过查看生产系统上的该序列的信息，发现创建序列的语句如下：
-- Create sequence create sequence SEQ_NEWID minvalue 1000 maxvalue 9999 start with 1000 increment by 1 nocache;
8 调整序列，使之cache到内存中，alter sequence SEQ_NEWID cache 3000;
9 附带，刚在生产系统上把序列cache到内存，另一同事就把RAC数据库给重启了，据他判断说是存储系统出了问题。可是，怎么会是存储出了问题呢？如果，能重现这次的性能问题该多好啊，只是没有如果！
在处理这个问题的时候，参照了老白的文章：白鳝说WAIT EVENT之ROW CACHE LOCK
如何获取Oracle数据库中某表及索引、约束、触发器、对象权限的创建语句？
在上一篇文章中，我们知道了如何获取Oracle数据库中某个用户下的所有表、索引、约束、触发器的创建脚本？那么对于数据库中特定的某张表而言，又该如何获取建表语句、及索引、约束、外键约束、触发器、对象权限的创建脚本呢？
在这里，给出一个实现上述功能的脚本：
OCM11g-> cat table_ddl.sql
-- Be sure the variable 'S' means the Schema name,the 'T' means table name.
-- All these variables should be UPPERCASE.
def S='&1'
def T='&2'
set lines 2000 pages 0 ver off echo off head off feed off
set newpage none
set trimspool on
set long 5000000
col output for a1000 word_wrapped
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',true);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'STORAGE',false);
spool &T..sql
select dbms_metadata.get_ddl('TABLE','&T','&S') output from dual;
select dbms_metadata.get_dependent_ddl('INDEX','&T','&S') output from dual;
select dbms_metadata.get_dependent_ddl('CONSTRAINT','&T','&S') output from dual;
select dbms_metadata.get_dependent_ddl('REF_CONSTRAINT','&T','&S') output from dual;
select dbms_metadata.get_dependent_ddl('TRIGGER','&T','&S') output from dual;
-- Uncomment to generate object level grants
select dbms_metadata.get_dependent_ddl('OBJECT_GRANT','&T','&S') output from dual;
spool off;
OCM11g->
下述是对于HR用户下的EMPLOYEES表的输出结果：
SQL> conn hr/hR
Connected.
SQL> @table_ddl.sql
Enter value for 1: HR
Enter value for 2: EMPLOYEES
CREATE TABLE "HR"."EMPLOYEES"
( "EMPLOYEE_ID" NUMBER(6,0),
"FIRST_NAME" VARCHAR2(20),
"LAST_NAME" VARCHAR2(25) CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL ENABLE,
"EMAIL" VARCHAR2(25) CONSTRAINT "EMP_EMAIL_NN" NOT NULL ENABLE,
"PHONE_NUMBER" VARCHAR2(20),
"HIRE_DATE" DATE CONSTRAINT "EMP_HIRE_DATE_NN" NOT NULL ENABLE,
"JOB_ID" VARCHAR2(10) CONSTRAINT "EMP_JOB_NN" NOT NULL ENABLE,
"SALARY" NUMBER(8,2),
"COMMISSION_PCT" NUMBER(2,2),
"MANAGER_ID" NUMBER(6,0),
"DEPARTMENT_ID" NUMBER(4,0),
CONSTRAINT "EMP_SALARY_MIN" CHECK (salary > 0) ENABLE,
CONSTRAINT "EMP_EMAIL_UK" UNIQUE ("EMAIL")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
TABLESPACE "EXAMPLE" ENABLE,
CONSTRAINT "EMP_EMP_ID_PK" PRIMARY KEY ("EMPLOYEE_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
TABLESPACE "EXAMPLE" ENABLE,
CONSTRAINT "EMP_MANAGER_FK" FOREIGN KEY ("MANAGER_ID")
REFERENCES "HR"."EMPLOYEES" ("EMPLOYEE_ID") ENABLE,
CONSTRAINT "EMP_JOB_FK" FOREIGN KEY ("JOB_ID")
REFERENCES "HR"."JOBS" ("JOB_ID") ENABLE,
CONSTRAINT "EMP_DEPT_FK" FOREIGN KEY ("DEPARTMENT_ID")
REFERENCES "HR"."DEPARTMENTS" ("DEPARTMENT_ID") ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
TABLESPACE "EXAMPLE" ;
CREATE UNIQUE INDEX "HR"."EMP_EMAIL_UK" ON "HR"."EMPLOYEES" ("EMAIL")
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
TABLESPACE "EXAMPLE" ;
CREATE UNIQUE INDEX "HR"."EMP_EMP_ID_PK" ON "HR"."EMPLOYEES" ("EMPLOYEE_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
TABLESPACE "EXAMPLE" ;
CREATE INDEX "HR"."EMP_DEPARTMENT_IX" ON "HR"."EMPLOYEES" ("DEPARTMENT_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
TABLESPACE "EXAMPLE" ;
CREATE INDEX "HR"."EMP_JOB_IX" ON "HR"."EMPLOYEES" ("JOB_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
TABLESPACE "EXAMPLE" ;
CREATE INDEX "HR"."EMP_MANAGER_IX" ON "HR"."EMPLOYEES" ("MANAGER_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
TABLESPACE "EXAMPLE" ;
CREATE INDEX "HR"."EMP_NAME_IX" ON "HR"."EMPLOYEES" ("LAST_NAME", "FIRST_NAME")
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
TABLESPACE "EXAMPLE" ;
ALTER TABLE "HR"."EMPLOYEES" MODIFY ("EMAIL" CONSTRAINT "EMP_EMAIL_NN" NOT NULL ENABLE);
ALTER TABLE "HR"."EMPLOYEES" ADD CONSTRAINT "EMP_EMAIL_UK" UNIQUE ("EMAIL")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
TABLESPACE "EXAMPLE" ENABLE;
ALTER TABLE "HR"."EMPLOYEES" ADD CONSTRAINT "EMP_EMP_ID_PK" PRIMARY KEY ("EMPLOYEE_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
TABLESPACE "EXAMPLE" ENABLE;
ALTER TABLE "HR"."EMPLOYEES" MODIFY ("HIRE_DATE" CONSTRAINT "EMP_HIRE_DATE_NN" NOT NULL ENABLE);
ALTER TABLE "HR"."EMPLOYEES" MODIFY ("JOB_ID" CONSTRAINT "EMP_JOB_NN" NOT NULL ENABLE);
ALTER TABLE "HR"."EMPLOYEES" MODIFY ("LAST_NAME" CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL ENABLE);
ALTER TABLE "HR"."EMPLOYEES" ADD CONSTRAINT "EMP_SALARY_MIN" CHECK (salary > 0) ENABLE;
ALTER TABLE "HR"."EMPLOYEES" ADD CONSTRAINT "EMP_DEPT_FK" FOREIGN KEY ("DEPARTMENT_ID")
REFERENCES "HR"."DEPARTMENTS" ("DEPARTMENT_ID") ENABLE;
ALTER TABLE "HR"."EMPLOYEES" ADD CONSTRAINT "EMP_JOB_FK" FOREIGN KEY ("JOB_ID")
REFERENCES "HR"."JOBS" ("JOB_ID") ENABLE;
ALTER TABLE "HR"."EMPLOYEES" ADD CONSTRAINT "EMP_MANAGER_FK" FOREIGN KEY ("MANAGER_ID")
REFERENCES "HR"."EMPLOYEES" ("EMPLOYEE_ID") ENABLE;
CREATE OR REPLACE TRIGGER "HR"."SECURE_EMPLOYEES"
BEFORE INSERT OR UPDATE OR DELETE ON employees
BEGIN
secure_dml;
END secure_employees;
/
ALTER TRIGGER "HR"."SECURE_EMPLOYEES" DISABLE;
CREATE OR REPLACE TRIGGER "HR"."UPDATE_JOB_HISTORY"
AFTER UPDATE OF job_id, department_id ON employees
FOR EACH ROW
BEGIN
add_job_history(:old.employee_id, :old.hire_date, sysdate,
:old.job_id, :old.department_id);
END;
/
ALTER TRIGGER "HR"."UPDATE_JOB_HISTORY" ENABLE;
GRANT SELECT, REFERENCES ON "HR"."EMPLOYEES" TO "OE";
SQL>
如何获取Oracle数据库中某个用户下的所有表、索引、约束、触发器的创建脚本？
在Oracle数据库中，如何快速获取某个用户下的所有表、索引、约束及触发器的创建SQL语句呢？本文给出一个简单的示例脚本，经过测试，除位于回收站下的对象及只读视图的约束语句不能获取，其它对象基本均可获取CREATE语句。
脚本内容如下：
[oracle@oracledg ~]$ cat get_ddl.sql
-- Be sure the variable 'S' means the Schema name,it should be UPPERCASE.
-- And this script COULDNOT get the constraints CREATE DDL,which CONSTRAINT_TYPE is 'O',cause it's READ ONLY on a view.
-- Author Info:Asher Huang
-- http://OracleOnlinux.cn
-- Mail:3dian14@gmail.com
def S='&Schema'
set serveroutput on
set long 20000
set lines 2000 pages 0 ver off echo off head off feed off
set newpage none
set trimspool on
set long 5000000
spool &S..sql
DECLARE
CURSOR C1 IS
SELECT '&S' AS u_name, table_name AS tab_name FROM user_tables;
CURSOR C2 IS
SELECT '&S' AS u_name, index_name AS idx_name FROM user_indexes;
CURSOR C3 IS
SELECT '&S' AS u_name, constraint_name AS cons_name FROM user_constraints where constraint_type not in('R','O');
CURSOR C4 IS
SELECT '&S' AS u_name, table_name AS tab_name FROM user_constraints where constraint_type in('R');
CURSOR C5 IS
SELECT '&S' AS u_name, trigger_name AS trig_name FROM user_triggers;
V_MSG CLOB;
BEGIN
DBMS_OUTPUT.put_line('The CREATE DDL for schema &S''s Tables are:');
FOR x IN C1 LOOP
select dbms_metadata.get_ddl('TABLE', x.tab_name, x.u_name) INTO V_MSG from dual;
DBMS_OUTPUT.put_line(V_MSG);
END LOOP;
DBMS_OUTPUT.put_line('The CREATE DDL for schema &S''s Indexes are:');
FOR x IN C2 LOOP
select dbms_metadata.get_ddl('INDEX', x.idx_name, x.u_name) INTO V_MSG from dual;
DBMS_OUTPUT.put_line(V_MSG);
END LOOP;
DBMS_OUTPUT.put_line('The CREATE DDL for schema &S''s Constraints are:');
FOR x IN C3 LOOP
select dbms_metadata.get_ddl('CONSTRAINT', x.cons_name, x.u_name) INTO V_MSG from dual;
DBMS_OUTPUT.put_line(V_MSG);
END LOOP;
DBMS_OUTPUT.put_line('The CREATE DDL for schema &S''s Reference Constraints are:');
FOR x IN C4 LOOP
select dbms_metadata.get_dependent_ddl('REF_CONSTRAINT', x.tab_name, x.u_name) INTO V_MSG from dual;
DBMS_OUTPUT.put_line(V_MSG);
END LOOP;
DBMS_OUTPUT.put_line('The CREATE DDL for schema &S''s Triggers are:');
FOR x IN C5 LOOP
select dbms_metadata.get_ddl('TRIGGER', x.trig_name, x.u_name) INTO V_MSG from dual;
DBMS_OUTPUT.put_line(V_MSG);
END LOOP;
END;
/
spool off;
[oracle@oracledg ~]$
测试获取SCOTT用户的所有表、索引、约束、触发器创建脚本如下：
[oracle@oracledg ~]$ cat SCOTT.sql
The CREATE DDL for schema SCOTT's Tables are:
CREATE TABLE "SCOTT"."DEPT"
( "DEPTNO" NUMBER(2,0),
"DNAME" VARCHAR2(14),
"LOC" VARCHAR2(13),
CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ENABLE,
CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
CREATE TABLE "SCOTT"."BONUS"
( "ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"SAL" NUMBER,
"COMM" NUMBER
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
CREATE TABLE "SCOTT"."SALGRADE"
( "GRADE" NUMBER,
"LOSAL" NUMBER,
"HISAL" NUMBER
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
The CREATE DDL for schema SCOTT's Indexes are:
CREATE UNIQUE INDEX "SCOTT"."PK_EMP" ON "SCOTT"."EMP" ("EMPNO")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
CREATE UNIQUE INDEX "SCOTT"."PK_DEPT" ON "SCOTT"."DEPT" ("DEPTNO")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
The CREATE DDL for schema SCOTT's Constraints are:
ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ENABLE
ALTER TABLE "SCOTT"."DEPT" ADD CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ENABLE
The CREATE DDL for schema SCOTT's Reference Constraints are:
ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
The CREATE DDL for schema SCOTT's Triggers are:
[oracle@oracledg ~]$
Oracle 11g Administrator’s Guide笔记2:如何手工创建、删除11gR2数据库？
在前一篇文章中提到阅读Oracle 11gR2 Administrator‘s Guide(文档编号E25494-01)时，简单描述了DDL_LOCK_TIMEOUT参数。在Oracle 11gR2 Administrator‘s Guide文档的chapter 2 Creating and Configuring an Oracle Database章节时，提到手工建库，本文简单记录一下，如何在OEL 5.5 X86_64位环境下，手工创建及删除11.2.0.1.0的数据库，将文件放到文件系统上存放。当然，利用DBCA来建库、删库比较简单，就不再赘述了。具体操作步骤如下：
1 前提条件，操作系统上已经安装好Oracle 11gR2 的软件，其中环境变量ORACLE_BASE=/u01/app/oracle，ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1；
[root@OCM11g ~]# su - oracle OCM11g-> env | grep ORA ORACLE_BASE=/u01/app/oracle ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 OCM11g->
2 确认环境变量，本例中选择ORACLE_SID=manualdb,ORACLE_HOME选用原有的/u01/app/oracle/product/11.2.0/db_1；
OCM11g-> id uid=1101(oracle) gid=1000(oinstall) groups=1000(oinstall),1201(asmdba),1300(dba),1301(oper) OCM11g-> uname -rm 2.6.18-194.el5 x86_64 OCM11g-> export ORACLE_SID=manualdb OCM11g-> env | grep ORA ORACLE_SID=manualdb ORACLE_BASE=/u01/app/oracle ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 OCM11g->
3 创建初始化参数文件，这里将参数文件放到系统默认的路径下，即$ORACLE_HOME/dbs,命名为initmanualdb.ora,其内容如下：
OCM11g-> cat /u01/app/oracle/product/11.2.0/db_1/dbs/initmanualdb.ora db_name=manualdb memory_target=600m OCM11g->
简单起见，我们只在初始化参数文件中指定了db_name,memory_target这两个参数。我们知道，启动数据库实例的最少参数只需要一个db_name即可，这里选择db_name=manualdb,附加一个memory_target=600m,这个是11g的新参数，用于控制SGA+PGA的总大小。当然，也可以在参数文件中指定control_files,如果该参数被忽略的话，那么Oracle会自动在$ORACLE_HOME/dbs路径下创建一个名为cntrl$ORACLE_SID.dbf的控制文件。
4 准备将来存放数据文件、日志文件的路径。这两类文件的路径如下：
datafile:
OCM11g-> mkdir -p /u02/manualdb/oradata
online log:
OCM11g-> mkdir -p /u02/manualdb/onlinelog OCM11g-> ll /u02/manualdb/ total 8 drwxr-xr-x 2 oracle oinstall 4096 Mar 30 10:56 onlinelog drwxr-xr-x 2 oracle oinstall 4096 Mar 30 10:56 oradata OCM11g->
5 启动manualdb实例到NOMOUNT状态，其实，在该环境下，我们的控制文件尚未生成，实例至多也只能加载到NOMOUNT状态：
OCM11g-> id uid=1101(oracle) gid=1000(oinstall) groups=1000(oinstall),1201(asmdba),1300(dba),1301(oper) OCM11g-> env | grep ORA ORACLE_SID=manualdb ORACLE_BASE=/u01/app/oracle ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 OCM11g-> sqlplus SQL*Plus: Release 11.2.0.1.0 Production on Fri Mar 30 10:59:32 2012 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 626327552 bytes Fixed Size 2215944 bytes Variable Size 373297144 bytes Database Buffers 243269632 bytes Redo Buffers 7544832 bytes ORA-00205: error in identifying control file, check alert log for more info SQL>
上述报错ORA-00205提示控制文件找不到，正是我们所预见的，因为当前控制文件还未生成，所以数据库没法MOUNT，说明数据库是NOMOUNT状态。这时，也可以看到相关的后台进程已经启动：
SQL> ! OCM11g-> ps -ef | grep ora_|grep -v grep oracle 14885 1 0 11:02 ? 00:00:00 ora_pmon_manualdb oracle 14887 1 0 11:02 ? 00:00:00 ora_vktm_manualdb oracle 14891 1 0 11:02 ? 00:00:00 ora_gen0_manualdb oracle 14893 1 0 11:02 ? 00:00:00 ora_diag_manualdb oracle 14895 1 0 11:02 ? 00:00:00 ora_dbrm_manualdb oracle 14897 1 0 11:02 ? 00:00:00 ora_psp0_manualdb oracle 14899 1 0 11:02 ? 00:00:00 ora_dia0_manualdb oracle 14903 1 6 11:02 ? 00:00:27 ora_mman_manualdb oracle 14905 1 0 11:02 ? 00:00:01 ora_dbw0_manualdb oracle 14907 1 0 11:02 ? 00:00:00 ora_lgwr_manualdb oracle 14909 1 0 11:02 ? 00:00:00 ora_ckpt_manualdb oracle 14911 1 0 11:02 ? 00:00:00 ora_smon_manualdb oracle 14913 1 0 11:03 ? 00:00:00 ora_reco_manualdb oracle 14915 1 0 11:03 ? 00:00:00 ora_mmon_manualdb oracle 14917 1 0 11:03 ? 00:00:00 ora_mmnl_manualdb OCM11g->
6 执行下述CREATE DATABASE的命令，开始手工创建Oracle 数据库：
SQL> conn / as sysdba;
Connected.
SQL> select open_mode from v$database;
select open_mode from v$database
*
ERROR at line 1:
ORA-01507: database not mounted
SQL> CREATE DATABASE manualdb
2 USER SYS IDENTIFIED BY oracle
3 USER SYSTEM IDENTIFIED BY oracle
4 LOGFILE GROUP 1 ('/u02/manualdb/onlinelog/redo01a.log') SIZE 50M BLOCKSIZE 512,
5 GROUP 2 ('/u02/manualdb/onlinelog/redo02a.log') SIZE 50M BLOCKSIZE 512
6 MAXLOGFILES 5
7 MAXLOGMEMBERS 5
8 MAXLOGHISTORY 1
9 MAXDATAFILES 100
10 CHARACTER SET AL32UTF8
11 NATIONAL CHARACTER SET AL16UTF16
12 EXTENT MANAGEMENT LOCAL
13 DATAFILE '/u02/manualdb/oradata/system01.dbf' SIZE 300M REUSE
SYSAUX DATAFILE '/u02/manualdb/oradata/sysaux01.dbf' SIZE 300M REUSE
DEFAULT TABLESPACE users
DATAFILE '/u02/manualdb/oradata/users01.dbf'
17 SIZE 5M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
18 DEFAULT TEMPORARY TABLESPACE tempts1
19 TEMPFILE '/u02/manualdb/oradata/temp01.dbf'
20 SIZE 20M REUSE
21 UNDO TABLESPACE undotbs
22 DATAFILE '/u02/manualdb/oradata/undotbs01.dbf'
23 SIZE 20M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
Database created.
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
MANUALDB READ WRITE
SQL>
7 创建数据字典视图，分别执行下述脚本来完成：
SQL> @?/rdbms/admin/catalog.sql ...... ...... SQL> @?/rdbms/admin/catproc.sql ...... ...... SQL> @?/sqlplus/admin/pupbld.sql ...... ......
上述3个脚本的说明见下表：
The Scripts and descriptions
|
Script |
Description |
|
CATALOG.SQL |
Creates the views of the data dictionary tables, the dynamic performance views,and public synonyms for many of the views.Grants PUBLIC access to the synonyms. |
|
CATPROC.SQL |
Runs all scripts required for or used with PL/SQL. |
|
PUPBLD.SQL |
Required for SQL*Plus. Enables SQL*Plus to disable commands by user. |
8 至此，我们完成了手工创建Oracle 数据库。
SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production PL/SQL Release 11.2.0.1.0 - Production CORE 11.2.0.1.0 Production TNS for Linux: Version 11.2.0.1.0 - Production NLSRTL Version 11.2.0.1.0 - Production 5 rows selected. SQL> select name,open_mode from v$database; NAME OPEN_MODE --------- -------------------- MANUALDB READ WRITE 1 row selected. SQL> select name from v$datafile 2 union 3 select name from v$controlfile 4 union 5 select member from v$logfile; NAME -------------------------------------------------------------------------------- /u01/app/oracle/product/11.2.0/db_1/dbs/cntrlmanualdb.dbf /u02/manualdb/onlinelog/redo01a.log /u02/manualdb/onlinelog/redo02a.log /u02/manualdb/oradata/sysaux01.dbf /u02/manualdb/oradata/system01.dbf /u02/manualdb/oradata/undotbs01.dbf /u02/manualdb/oradata/users01.dbf 7 rows selected. SQL>
9 最后，如果该数据库不需要的话。我们可以执行手工删除数据库，当然必须要将数据库启动到MOUNT RESTRICT状态来删除：
如何确认实例是否是RESTRICTED MODE：
SQL> select instance_name,status,startup_time,logins from v$instance; INSTANCE_NAME STATUS STARTUP_TIME LOGINS --------------- ------------ ------------------- ---------- manualdb MOUNTED 2012/03/30 13:38:52 ALLOWED 1 row selected. SQL> alter system enable restricted session; System altered. SQL> select instance_name,status,startup_time,logins from v$instance; INSTANCE_NAME STATUS STARTUP_TIME LOGINS --------------- ------------ ------------------- ---------- manualdb MOUNTED 2012/03/30 13:38:52 RESTRICTED 1 row selected. SQL> alter system disable restricted session; System altered. SQL> select instance_name,status,startup_time,logins from v$instance; INSTANCE_NAME STATUS STARTUP_TIME LOGINS --------------- ------------ ------------------- ---------- manualdb MOUNTED 2012/03/30 13:38:52 ALLOWED 1 row selected. SQL>
通过查看V$INSTANCE动态性能视图的LOGINS字段，如果该值为ALLOWED，说明实例是正常启动，并未进入RESTRICTED MODE，普通用户可以正常访问；如果该值为RESTRICTED说明是RESTRICTED MODE，即需要具有RESTRICTED SESSION系统权限的用户方可访问；
手工删除数据库：
SQL> select instance_name,status,startup_time,logins from v$instance; INSTANCE_NAME STATUS STARTUP_TIME LOGINS --------------- ------------ ------------------- ---------- manualdb MOUNTED 2012/03/30 13:38:52 ALLOWED 1 row selected. SQL> drop database; drop database * ERROR at line 1: ORA-12719: operation requires database is in RESTRICTED mode SQL> alter system enable restricted session; System altered. SQL> select instance_name,status,startup_time,logins from v$instance; INSTANCE_NAME STATUS STARTUP_TIME LOGINS --------------- ------------ ------------------- ---------- manualdb MOUNTED 2012/03/30 13:38:52 RESTRICTED 1 row selected. SQL> drop database; Database dropped. Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL>
此时，Oracle自动删除该数据库的所有数据文件、联机重做日志文件、控制文件以及初始化参数文件。从alert日志文件里可以看到下述信息：
Fri Mar 30 13:45:33 2012 drop database Deleted file /u02/manualdb/oradata/system01.dbf Deleted file /u02/manualdb/oradata/sysaux01.dbf Deleted file /u02/manualdb/oradata/undotbs01.dbf Deleted file /u02/manualdb/oradata/users01.dbf Deleted file /u02/manualdb/onlinelog/redo01a.log Deleted file /u02/manualdb/onlinelog/redo02a.log Deleted file /u02/manualdb/oradata/temp01.dbf Deleted file /u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_manualdb.f
10 简单总结，可见手工创建、删除数据库也很简单。当然，在第6步中执行手工建库的命令中还有诸多其它选项，比如选择OMF，这时，只要在参数文件中指定DB_CREATE_FILE_DEST参数，那么建库的命令将更加简单。
奇怪的ORA-04043错误及解决方法
在查询数据字典表，DBA_DATA_FILES时，遇见一则有趣的ORA-04043错误，错误重现及解决办法见下述：
1数据库版本，正常情况下，可以查看DBA_DATA_FILES数据字典表：
SQL> startup ORACLE instance started. Total System Global Area 626327552 bytes Fixed Size 2215944 bytes Variable Size 398462968 bytes Database Buffers 218103808 bytes Redo Buffers 7544832 bytes Database mounted. Database opened. SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production PL/SQL Release 11.2.0.1.0 - Production CORE 11.2.0.1.0 Production TNS for Linux: Version 11.2.0.1.0 - Production NLSRTL Version 11.2.0.1.0 - Production SQL> desc dba_data_files; Name Null? Type ----------------------------------------- -------- ---------------------------- FILE_NAME VARCHAR2(513) FILE_ID NUMBER TABLESPACE_NAME VARCHAR2(30) BYTES NUMBER BLOCKS NUMBER STATUS VARCHAR2(9) RELATIVE_FNO NUMBER AUTOEXTENSIBLE VARCHAR2(3) MAXBYTES NUMBER MAXBLOCKS NUMBER INCREMENT_BY NUMBER USER_BYTES NUMBER USER_BLOCKS NUMBER ONLINE_STATUS VARCHAR2(7) SQL>
2重现错误：
SQL> startup mount; ORACLE instance started. Total System Global Area 626327552 bytes Fixed Size 2215944 bytes Variable Size 398462968 bytes Database Buffers 218103808 bytes Redo Buffers 7544832 bytes Database mounted. SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production PL/SQL Release 11.2.0.1.0 - Production CORE 11.2.0.1.0 Production TNS for Linux: Version 11.2.0.1.0 - Production NLSRTL Version 11.2.0.1.0 - Production SQL> select open_mode from v$database; OPEN_MODE -------------------- MOUNTED SQL> desc dba_data_files; ERROR: ORA-04043: object dba_data_files does not exist SQL> alter database open; Database altered. SQL> select open_mode from v$database; OPEN_MODE -------------------- READ WRITE SQL> desc dba_data_files; ERROR: ORA-04043: object dba_data_files does not exist SQL>
3解决办法：
SQL> show user; USER is "SYS" SQL> select open_mode from v$database; OPEN_MODE -------------------- READ WRITE SQL> desc dba_data_files; ERROR: ORA-04043: object dba_data_files does not exist SQL> alter system flush shared_pool; System altered. SQL> desc dba_data_files; Name Null? Type ----------------------------------------- -------- ---------------------------- FILE_NAME VARCHAR2(513) FILE_ID NUMBER TABLESPACE_NAME VARCHAR2(30) BYTES NUMBER BLOCKS NUMBER STATUS VARCHAR2(9) RELATIVE_FNO NUMBER AUTOEXTENSIBLE VARCHAR2(3) MAXBYTES NUMBER MAXBLOCKS NUMBER INCREMENT_BY NUMBER USER_BYTES NUMBER USER_BLOCKS NUMBER ONLINE_STATUS VARCHAR2(7) SQL>
或者，先shutdown，然后startup，也可以避免ORA-04043错误。
描述：当数据库处于MOUNT状态时，如果试图去访问DBA_之类的数据字典表时，数据库会报出ORA-04043错误！即使，接下来，ALTER DATABASE OPEN，将数据库置于OPEN状态，再尝试去查看DBA_之类的数据字典表时，数据库依然会报出ORA-04043错误！解决的办法有两个：
- ① 重启数据库；
- ② 刷出共享池：ALTER SYSTEM FLUSH SHARED_POOL
补充，经测试，在数据库MOUNT状态尝试去查看SYS用户的普通表的话，也会报出同样的错误，看来不单是数据字典表了，具体见下述：
SQL> startup mount;
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2215944 bytes
Variable Size 398462968 bytes
Database Buffers 218103808 bytes
Redo Buffers 7544832 bytes
Database mounted.
SQL> desc dba_data_files;
ERROR:
ORA-04043: object dba_data_files does not exist
SQL> desc test_04043
ERROR:
ORA-04043: object test_04043 does not exist
SQL> desc hr.t;
ERROR:
ORA-04043: object hr.t does not exist
SQL> alter database open;
Database altered.
SQL> desc dba_data_files;
ERROR:
ORA-04043: object dba_data_files does not exist
SQL> desc test_04043
ERROR:
ORA-04043: object test_04043 does not exist
SQL> desc hr.t;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
SQL> alter system flush shared_pool;
System altered.
SQL> desc dba_data_files;
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE_NAME VARCHAR2(513)
FILE_ID NUMBER
TABLESPACE_NAME VARCHAR2(30)
BYTES NUMBER
BLOCKS NUMBER
STATUS VARCHAR2(9)
RELATIVE_FNO NUMBER
AUTOEXTENSIBLE VARCHAR2(3)
MAXBYTES NUMBER
MAXBLOCKS NUMBER
INCREMENT_BY NUMBER
USER_BYTES NUMBER
USER_BLOCKS NUMBER
ONLINE_STATUS VARCHAR2(7)
SQL> desc test_04043
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
NAME VARCHAR2(30)
SQL> desc hr.t;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
SQL> select * from test_04043;
ID NAME
---------- ------------------------------
1 The F**king ORA-04043 Error.
2 WWW.OracleOnLinux.CN
SQL>
从上，可以看出，在数据库MOUNT状态下，试图去查看数据字典表和SYS用户下的普通表，会报ORA-04043错误，将数据库OPEN之后，再去查看这些对象时，依然会报错，而普通用户的对象却不会出现这个错误！
究其原因，这是个Oracle的bug,Bug 2365821,可见Metalink上的ORA-4043 On DBA_* Views If They Are Described In Mount Stage [ID 296235.1]，受影响的数据库版本：Oracle Server – Enterprise Edition – Version: 8.1.7.4 to 11.2.0.1 – Release: 8.1.7 to 11.2，This problem can occur on any platform.
Oracle 11g Automatic Maintenance Tasks
从Oracle 11g开始，Oracle的自动化维护任务更智能化了，默认情况下，在安装数据库的过程中，如果启用自动化维护任务的话，数据库会在工作日的每晚22:00到第二天的凌晨2:00，周末的凌晨6:00到第二天的凌晨2:00,自动对数据库进行诸如优化器的统计信息收集、自动SQL的优化。且在自动化维护的过程中，数据库会使用较少的CPU资源，以防止自动化维护任务使用到过多的资源而影响到用户的正常使用。
其一，在使用DBCA建库的时候，我们可以看到这一自动维护任务的选项：
其二，如果启用了自动化维护任务的话，也可以从数据库的alert日志里看到下述信息：
Fri Mar 23 22:00:00 2012 Setting Resource Manager plan SCHEDULER[0x3007]:DEFAULT_MAINTENANCE_PLAN via scheduler window Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter Fri Mar 23 22:00:00 2012 Starting background process VKRM Fri Mar 23 22:00:00 2012 VKRM started with pid=24, OS id=10743 Fri Mar 23 22:00:33 2012 Begin automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK" Fri Mar 23 22:01:32 2012 End automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK" Fri Mar 23 22:05:56 2012 Thread 1 cannot allocate new log, sequence 8 Private strand flush not complete Current log# 1 seq# 7 mem# 0: +DATA/ocm11g/onlinelog/group_1.261.778667669 Current log# 1 seq# 7 mem# 1: +FLASH/ocm11g/onlinelog/group_1.257.778667673 Thread 1 advanced to log sequence 8 (LGWR switch) Current log# 2 seq# 8 mem# 0: +DATA/ocm11g/onlinelog/group_2.262.778667673 Current log# 2 seq# 8 mem# 1: +FLASH/ocm11g/onlinelog/group_2.258.778667675 Sat Mar 24 02:00:00 2012 Clearing Resource Manager plan via parameter Sat Mar 24 06:00:00 2012 Setting Resource Manager plan SCHEDULER[0x3008]:DEFAULT_MAINTENANCE_PLAN via scheduler window Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter Sat Mar 24 06:00:00 2012 Starting background process VKRM Sat Mar 24 06:00:01 2012 VKRM started with pid=29, OS id=15277 Sat Mar 24 06:00:21 2012 Begin automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK" Sat Mar 24 06:00:49 2012 End automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK" Sat Mar 24 06:02:05 2012 Thread 1 cannot allocate new log, sequence 9 Private strand flush not complete Current log# 2 seq# 8 mem# 0: +DATA/ocm11g/onlinelog/group_2.262.778667673 Current log# 2 seq# 8 mem# 1: +FLASH/ocm11g/onlinelog/group_2.258.778667675 Thread 1 advanced to log sequence 9 (LGWR switch) Current log# 3 seq# 9 mem# 0: +DATA/ocm11g/onlinelog/group_3.263.778667677 Current log# 3 seq# 9 mem# 1: +FLASH/ocm11g/onlinelog/group_3.259.778667677 Sat Mar 24 08:56:14 2012 Thread 1 advanced to log sequence 10 (LGWR switch) Current log# 1 seq# 10 mem# 0: +DATA/ocm11g/onlinelog/group_1.261.778667669 Current log# 1 seq# 10 mem# 1: +FLASH/ocm11g/onlinelog/group_1.257.778667673 Sat Mar 24 14:06:51 2012 Thread 1 cannot allocate new log, sequence 11 Private strand flush not complete Current log# 1 seq# 10 mem# 0: +DATA/ocm11g/onlinelog/group_1.261.778667669 Current log# 1 seq# 10 mem# 1: +FLASH/ocm11g/onlinelog/group_1.257.778667673 Thread 1 advanced to log sequence 11 (LGWR switch) Current log# 2 seq# 11 mem# 0: +DATA/ocm11g/onlinelog/group_2.262.778667673 Current log# 2 seq# 11 mem# 1: +FLASH/ocm11g/onlinelog/group_2.258.778667675 Sat Mar 24 18:11:25 2012 Thread 1 advanced to log sequence 12 (LGWR switch) Current log# 3 seq# 12 mem# 0: +DATA/ocm11g/onlinelog/group_3.263.778667677 Current log# 3 seq# 12 mem# 1: +FLASH/ocm11g/onlinelog/group_3.259.778667677 Sun Mar 25 02:00:00 2012 Clearing Resource Manager plan via parameter Sun Mar 25 02:00:30 2012 Thread 1 advanced to log sequence 13 (LGWR switch) Current log# 1 seq# 13 mem# 0: +DATA/ocm11g/onlinelog/group_1.261.778667669 Current log# 1 seq# 13 mem# 1: +FLASH/ocm11g/onlinelog/group_1.257.778667673 Sun Mar 25 06:00:00 2012 Setting Resource Manager plan SCHEDULER[0x3009]:DEFAULT_MAINTENANCE_PLAN via scheduler window Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter Sun Mar 25 06:00:00 2012 Starting background process VKRM Sun Mar 25 06:00:00 2012 VKRM started with pid=29, OS id=21059 Sun Mar 25 06:00:15 2012 Begin automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK" Sun Mar 25 06:00:33 2012 End automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK" Sun Mar 25 08:56:21 2012 Thread 1 advanced to log sequence 14 (LGWR switch) Current log# 2 seq# 14 mem# 0: +DATA/ocm11g/onlinelog/group_2.262.778667673 Current log# 2 seq# 14 mem# 1: +FLASH/ocm11g/onlinelog/group_2.258.778667675 Sun Mar 25 14:08:09 2012 Thread 1 cannot allocate new log, sequence 15 Private strand flush not complete Current log# 2 seq# 14 mem# 0: +DATA/ocm11g/onlinelog/group_2.262.778667673 Current log# 2 seq# 14 mem# 1: +FLASH/ocm11g/onlinelog/group_2.258.778667675 Thread 1 advanced to log sequence 15 (LGWR switch) Current log# 3 seq# 15 mem# 0: +DATA/ocm11g/onlinelog/group_3.263.778667677 Current log# 3 seq# 15 mem# 1: +FLASH/ocm11g/onlinelog/group_3.259.778667677 Sun Mar 25 22:08:35 2012 Thread 1 cannot allocate new log, sequence 16 Private strand flush not complete Current log# 3 seq# 15 mem# 0: +DATA/ocm11g/onlinelog/group_3.263.778667677 Current log# 3 seq# 15 mem# 1: +FLASH/ocm11g/onlinelog/group_3.259.778667677 Thread 1 advanced to log sequence 16 (LGWR switch) Current log# 1 seq# 16 mem# 0: +DATA/ocm11g/onlinelog/group_1.261.778667669 Current log# 1 seq# 16 mem# 1: +FLASH/ocm11g/onlinelog/group_1.257.778667673 Mon Mar 26 02:00:00 2012 Clearing Resource Manager plan via parameter Mon Mar 26 10:14:25 2012 Thread 1 advanced to log sequence 17 (LGWR switch) Current log# 2 seq# 17 mem# 0: +DATA/ocm11g/onlinelog/group_2.262.778667673 Current log# 2 seq# 17 mem# 1: +FLASH/ocm11g/onlinelog/group_2.258.778667675 Mon Mar 26 22:00:00 2012 Setting Resource Manager plan SCHEDULER[0x3003]:DEFAULT_MAINTENANCE_PLAN via scheduler window Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter Mon Mar 26 22:00:00 2012 Starting background process VKRM Mon Mar 26 22:00:00 2012 VKRM started with pid=24, OS id=9542 Mon Mar 26 22:00:30 2012 Begin automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK" Mon Mar 26 22:00:59 2012 End automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK" Mon Mar 26 22:02:17 2012 Thread 1 cannot allocate new log, sequence 18 Private strand flush not complete Current log# 2 seq# 17 mem# 0: +DATA/ocm11g/onlinelog/group_2.262.778667673 Current log# 2 seq# 17 mem# 1: +FLASH/ocm11g/onlinelog/group_2.258.778667675 Thread 1 advanced to log sequence 18 (LGWR switch) Current log# 3 seq# 18 mem# 0: +DATA/ocm11g/onlinelog/group_3.263.778667677 Current log# 3 seq# 18 mem# 1: +FLASH/ocm11g/onlinelog/group_3.259.778667677 Tue Mar 27 02:00:00 2012 Clearing Resource Manager plan via parameter
从上，上周五晚上22:00(Fri Mar 23 22:00:00 2012)到上周六凌晨2:00(Sat Mar 24 02:00:00 2012)数据库执行自动化维护任务，上周六、日凌晨6:00到次日凌晨2:00数据库执行自动化维护任务，本周一自动化维护任务的窗口又开始回到晚上22:00到次日凌晨2：00点之间。可见，Oracle 11g相比以前版本的数据库更加自动化、智能化。
Oracle 11g Administrator’s Guide笔记1：DDL_LOCK_TIMEOUT初始化参数介绍
今天在看Oracle 11gR2 Administrator‘s Guide(文档编号E25494-01)时，在第102页读到一个新的初始化参数：DDL_LOCK_TIMEOUT，该参数是从11g Release 1开始已经引入的，用来控制执行DDL语句时需要获取独占锁(exclusive locks)的延时时间，如果在该参数指定的时间范围内获取不到独占锁的话，那么DDL语句就会失败。
DDL_LOCK_TIMEOUT参数的取值范围是0~1000000秒，是一个整形参数，既可以在会话级别(ALTER SESSION)也可以在系统级别(ALTER SYSTEM)动态调整。其默认值是0，也就是说，执行DDL语句是要求立即获取独占锁资源，如果无法获取，那么所执行的DDL语句将立即报错。
我们也知道，执行DML语句时需要获取2个锁资源：
- ① 针对正在更新的一行或者多行的一个行级独占锁EXCLUSIVE，防止在事务结束之前有其它会话修改了被锁定行的数据，也叫排他锁；
- ② 针对要更新的表的 ROW EXCLUSIVE 表级锁定。这可避免在进行更改时由另一会话锁定整个表(可能会删除、截断该表，或修改了该表的表结构)，也叫共享锁；
正是由于锁的特征，结合DDL_LOCK_TIMEOUT初始化参数，实验如下：
会话1，创建一张表，并在该表上执行DML事务，且不提交，使该DML事务获取上述的两个锁资源：
SQL> conn / as sysdba;
Connected.
SQL> select * from v$version where rownum<2;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
SQL> show parameter ddl_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
ddl_lock_timeout integer 0
enable_ddl_logging boolean FALSE
SQL> conn hr/hR;
Connected.
SQL> create table t(id number);
Table created.
SQL> insert into t values(1);
1 row created.
SQL> select * from t;
ID
----------
1
SQL>
会话2，会话级别修改DDL_LOCK_TIMEOUT初始化参数超出0~1000000范围值，直接报错：
SQL> conn hr/hR; Connected. SQL> alter session set ddl_lock_timeout=1000001; ERROR: ORA-00068: invalid value 1000001 for parameter ddl_lock_timeout, must be between 0 and 1000000 SQL>
会话2，执行DDL语句，使之需要获取独占锁，立即抛出错误，因为DDL_LOCK_TIMEOUT初始化参数默认值是0：
SQL> alter table t add name varchar2(20) default 'www.OracleOnLinux.cn';
alter table t add name varchar2(20) default 'www.OracleOnLinux.cn'
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
SQL>
会话2，调整DDL_LOCK_TIMEOUT初始化参数为30秒，在30秒内获取不到独占锁，最终抛出错误：
SQL> alter session set ddl_lock_timeout=30;
Session altered.
SQL> set time on;
17:00:26 SQL> alter table t add name varchar2(20) default 'www.OracleOnLinux.cn';
alter table t add name varchar2(20) default 'www.OracleOnLinux.cn'
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
17:00:58 SQL>
会话2重新执行DDL，当会话1提交事务后，会话2在30秒内获取到独占锁，DDL执行成功：
17:03:13 SQL> alter table t add name varchar2(20) default 'www.OracleOnLinux.cn';
Table altered.
17:03:21 SQL> select * from t;
ID NAME
---------- --------------------
1 www.OracleOnLinux.cn
17:03:32 SQL>
Oracle 11gR2 Administrator's Guide，仍然需要多读多看，多做实验。
如何禁用Oracle 11g口令大小写？
我们知道，从Oracle 11g开始，默认情况下，数据库用户的口令严格区分大小写，这有别于以前版本的口令不区分大小写。
11g:
SQL> show user; USER is "SYS" SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production PL/SQL Release 11.2.0.1.0 - Production CORE 11.2.0.1.0 Production TNS for Linux: Version 11.2.0.1.0 - Production NLSRTL Version 11.2.0.1.0 - Production SQL> conn HR/hr; Connected. SQL> conn HR/HR; ERROR: ORA-01017: invalid username/password; logon denied Warning: You are no longer connected to ORACLE. SQL>
10g:
SQL> show user; USER is "SYS" SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi PL/SQL Release 10.2.0.5.0 - Production CORE 10.2.0.5.0 Production TNS for Linux: Version 10.2.0.5.0 - Production NLSRTL Version 10.2.0.5.0 - Production SQL> conn HUMAN/hr; Connected. SQL> conn HUMAN/HR; Connected. SQL>
而且，在DBA_USERS数据字典表的PASSWORD列中已经不再存储加密的口令，在11g以前版本的数据库中可以直接从DBA_USERS数据字典表中获取用户的加密口令，那么在11g数据库里如何查看用户加密后的口令呢？答案是需要查看查看USER$字典表，当然在10g版本的数据库中，也可以从USER$字典表中查看用户的加密口令：
11g:
SQL> show user; USER is "SYS" SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production PL/SQL Release 11.2.0.1.0 - Production CORE 11.2.0.1.0 Production TNS for Linux: Version 11.2.0.1.0 - Production NLSRTL Version 11.2.0.1.0 - Production SQL> select username,password from dba_users where username='HR'; USERNAME PASSWORD ------------------------------ ------------------------------ HR SQL> select name,password from user$ where name='HR'; NAME PASSWORD ------------------------------ ------------------------------ HR 4C6D73C3E8B0F0DA SQL>
10g:
SQL> show user; USER is "SYS" SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi PL/SQL Release 10.2.0.5.0 - Production CORE 10.2.0.5.0 Production TNS for Linux: Version 10.2.0.5.0 - Production NLSRTL Version 10.2.0.5.0 - Production SQL> select username,password from dba_users where username='HUMAN'; USERNAME PASSWORD ------------------------------ ------------------------------ HUMAN CBFA7677C00F9AE1 SQL> select name,password from user$ where name='HUMAN'; NAME PASSWORD ------------------------------ ------------------------------ HUMAN CBFA7677C00F9AE1 SQL>
Oracle为什么会这么做呢？应该是为了保护数据库的安全性，才这么做的吧。在11g中，需要拥有SELECT ANY DICTIONARY的角色才可用查看USER$字典表。当然，我们不建议将SYS.USER$表的访问权限给普通用户，也不建议将SELECT ANY DICTIONARY的角色给普通用户。
那么，在11g数据库中如何禁用口令大小写呢？
SQL> show parameter sensitive NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ sec_case_sensitive_logon boolean TRUE SQL> conn HR/hr; Connected. SQL> conn HR/HR; ERROR: ORA-01017: invalid username/password; logon denied Warning: You are no longer connected to ORACLE. SQL> conn / as sysdba; Connected. SQL> alter system set sec_case_sensitive_logon=false; System altered. SQL> show parameter sensitive NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ sec_case_sensitive_logon boolean FALSE SQL> conn HR/hr; Connected. SQL> conn HR/HR; Connected. SQL>
最后，从下，我们可以看到虽然用户的口令是大小写区分的，但是存放用户口令的密文却是一样的。
SQL> select name,password from user$ where name='HR'; NAME PASSWORD ------------------------------ ------------------------------ HR 4C6D73C3E8B0F0DA SQL> alter user hr identified by Hr; User altered. SQL> select name,password from user$ where name='HR'; NAME PASSWORD ------------------------------ ------------------------------ HR 4C6D73C3E8B0F0DA SQL> alter user hr identified by hR; User altered. SQL> select name,password from user$ where name='HR'; NAME PASSWORD ------------------------------ ------------------------------ HR 4C6D73C3E8B0F0DA SQL>
从上，我们可以看到，在11g中，不管用户的口令是大写还是小写，最终存放在数据库中的密文口令都是相同的。应该是，Oracle在对口令加密之前，统一转换为大写或者是小写后，然后开始对口令加密，最后形成加密口令。那么究竟是大写还是小写呢，或者是什么其他手段就不得而知了，还有就是Oracle采用的是什么加密算法，我想这些oracle是绝对不会对外公布的^_^
解决11gR2 RAC主库SWITCHOVER_STATUS为FAILED DESTINATION案例一则
简单记录一则处理11gR2 RAC主库的SWITCHOVER_STATUS为FAILED DESTINATION的案例：
主库为OEL 5.5 X86_64位的11gR2的双节点RAC，物理备库是OEL 5.5 X86_64位的单实例库，在主库的V$DATABASE动态性能表中看到节点2的SWITCHOVER_STATUS状态为FAILED DESTINATION：
SQL> select inst_id,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;
INST_ID CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS
------- ----------- -------------------- ------------- --- ---------- --------------------
2 16961718 MAXIMUM PERFORMANCE PRIMARY YES READ WRITE FAILED DESTINATION
1 16961719 MAXIMUM PERFORMANCE PRIMARY YES READ WRITE RESOLVABLE GAP
SQL>
并且在备库的alert文件里看到下述错误信息：
..... Fri Mar 16 09:29:11 2012 Error 1031 received logging on to the standby FAL[client, ARC1]: Error 1031 connecting to devdb2 for fetching gap sequence Errors in file /u01/app/oracle/diag/rdbms/phydb/phydb/trace/phydb_arc1_3897.trc: ORA-01031: insufficient privileges Errors in file /u01/app/oracle/diag/rdbms/phydb/phydb/trace/phydb_arc1_3897.trc: ORA-01031: insufficient privileges Fri Mar 16 09:33:06 2012 ...
同样看到/u01/app/oracle/diag/rdbms/phydb/phydb/trace/phydb_arc1_3897.trc里也有类似的信息：
*** 2012-03-16 09:29:11.683 OCISessionBegin failed. Error -1 .. Detailed OCI error val is 1031 and errmsg is 'ORA-01031: insufficient privileges ' OCISessionBegin failed. Error -1 .. Detailed OCI error val is 1031 and errmsg is 'ORA-01031: insufficient privileges ' *** 2012-03-16 09:29:11.738 4132 krsh.c Error 1031 received logging on to the standby *** 2012-03-16 09:29:11.741 4132 krsh.c FAL[client, ARC1]: Error 1031 connecting to devdb2 for fetching gap sequence ORA-01031: insufficient privileges ORA-01031: insufficient privileges
基本可以判断出是物理备库无法连接主库的节点2，提示权限不够，极有可能是口令文件的配置造成问题的原因。
果然，在主库上重新修改SYS用户口令之后，并将口令文件SCP至物理备库后，过一段时间后，物理备库alert文件出现下述正常信息：
Fri Mar 16 09:45:44 2012 RFS[5]: Assigned to RFS process 4212 RFS[5]: Identified database type as 'physical standby': Client is ARCH pid 6051 RFS[5]: Opened log for thread 2 sequence 239 dbid 676938241 branch 772208260 Archived Log entry 496 added for thread 2 sequence 239 rlc 772208260 ID 0x28b1d7da dest 2: RFS[5]: Opened log for thread 2 sequence 240 dbid 676938241 branch 772208260 Archived Log entry 497 added for thread 2 sequence 240 rlc 772208260 ID 0x28b1d7da dest 2: RFS[5]: Opened log for thread 2 sequence 241 dbid 676938241 branch 772208260 Archived Log entry 498 added for thread 2 sequence 241 rlc 772208260 ID 0x28b1d7da dest 2: Fri Mar 16 09:45:48 2012
到主库上重新查看SWITCHOVER_STATUS状态信息：
SQL> select inst_id,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;
INST_ID CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS
------- ----------- -------------------- ------------- --- ---------- --------------------
2 16961718 MAXIMUM PERFORMANCE PRIMARY YES READ WRITE TO STANDBY
1 16961719 MAXIMUM PERFORMANCE PRIMARY YES READ WRITE LOG SWITCH GAP
SQL>
备库执行Redo Apply：
SQL> alter database recover managed standby database using current logfile disconnect from session;
最后，主库双节点日志全部传输至物理备库，且已经全部被应用：
SQL> select name,thread#,sequence#,applied from v$archived_log ; NAME THREAD# SEQUENCE# APPLIED ------------------------------------------------------------ ---------- ---------- --------- /home/oracle/arch/ARC_1_0000000335_772208260.arc 1 335 YES /home/oracle/arch/ARC_2_0000000233_772208260.arc 2 233 YES /home/oracle/arch/ARC_2_0000000234_772208260.arc 2 234 YES /home/oracle/arch/ARC_2_0000000235_772208260.arc 2 235 YES /home/oracle/arch/ARC_2_0000000236_772208260.arc 2 236 YES /home/oracle/arch/ARC_1_0000000336_772208260.arc 1 336 YES /home/oracle/arch/ARC_1_0000000338_772208260.arc 1 338 YES /home/oracle/arch/ARC_1_0000000337_772208260.arc 1 337 YES /home/oracle/arch/ARC_1_0000000339_772208260.arc 1 339 YES /home/oracle/arch/ARC_2_0000000239_772208260.arc 2 239 YES /home/oracle/arch/ARC_2_0000000240_772208260.arc 2 240 YES NAME THREAD# SEQUENCE# APPLIED ------------------------------------------------------------ ---------- ---------- --------- /home/oracle/arch/ARC_2_0000000241_772208260.arc 2 241 YES /home/oracle/arch/ARC_1_0000000340_772208260.arc 1 340 IN-MEMORY /home/oracle/arch/ARC_2_0000000238_772208260.arc 2 238 YES /home/oracle/arch/ARC_2_0000000237_772208260.arc 2 237 YES /home/oracle/arch/ARC_2_0000000242_772208260.arc 2 242 YES
至此，解决问题。在本案例中导致物理备库的SWITCHOVER_STATUS为FAILED DESTINATION的原因是口令文件出错，导致的，简单记录一下！