XX城市的中考试卷，要用我们的阅卷系统，弄数据库的只有我一个人在弄，老师昨天都说了问题的严重性
关键是我刚开始学oracle不到1个月！
弄不好死都不知道怎么死的，情况这样：
中考 2万多人，试卷 30多万，一张试卷就不知道多少道题了（小题也包括）， 阅卷系统是由五百个老师同时在线批改试卷，一共改6天。
系统里有个小题表，以前测试过 一天就有900多万的记录写入。
服务器的配置是 ： linux redhat 64bit，4g内存，130多g硬盘， oracle 10g
我给自己定了个plan：不知道行不行~~~~希望大家多多提议，指教指教！！谢谢
1 。表空间的大小规划
system 1.5g, index_ts 3g, temp 500m undo 3g users 4.5g
SQL> select file_id,file_name,tablespace_name
2 from dba_data_files;
FILE_ID
----------
FILE_NAME
----------------------------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
4
/usr/app/oracle/oradata/orc1/users01.dbf
USERS
3
/usr/app/oracle/oradata/orc1/sysaux01.dbf
SYSAUX
2
/usr/app/oracle/oradata/orc1/undotbs01.dbf
UNDOTBS1
1
/usr/app/oracle/oradata/orc1/system01.dbf
SYSTEM
5
/usr/app/oracle/oradata/orc1/undotbs02.dbf
UNDOTBS1
6
/usr/app/oracle/oradata/orc1/users02.dbf
USERS
7
/usr/app/oracle/oradata/orc1/users03.dbf
USERS
8
/usr/app/oracle/oradata/orc1/index_ts01.dbf
INDEX_TS
9
/usr/app/oracle/oradata/orc1/index_ts02.dbf
INDEX_TS
10
/usr/app/oracle/oradata/orc1/ts_backup01.dbf
TS_BACKUP
已选择10行。
已用时间: 00: 00: 00.26
SQL>
2 。 redo：
5组，每组1.5g，两个成员。放的路径是这样的
GROUP# STATUS TYPE
---------- ------- -------MEMBER
----------------------------------------------------------------------------------------------------
IS_
---
4 ONLINE
/usr/app/oracle/oradata/orc1/redo04_01.log
NO
4 ONLINE
/usr1/redo04_02.log
NO
5 ONLINE
/usr/app/oracle/oradata/orc1/redo05_01.log
NO
5 ONLINE
/usr1/redo05_02.log
NO
6 ONLINE
/usr/app/oracle/oradata/orc1/redo06_01.log
NO
6 ONLINE
/usr1/redo06_02.log
NO
7 ONLINE
/usr/app/oracle/oradata/orc1/redo07_01.log
NO
7 ONLINE
/usr1/redo07_02.log
NO
8 ONLINE
/usr/app/oracle/oradata/orc1/redo08_01.log
NO
8 ONLINE
/usr1/redo08_02.log
NO
3. 开启归档（这对我 来说是个新东西！），开启flashback 功能！！
4. open_cursor=1000; processes=400( 上次请教：说的是400人同时在线的用了 processes=300，现在是500人，所以改了)
5. shared_pool_size=300
6. db_recovery_file_dest_size big integer 15G（这个每天用rman备份注意调整）
7.恢复策略：
***************************************************************************
恢复策略：
1. 丢失 controlfile ;
步骤：
1. startup nomount;
2. restore controlfile;
3. alter database mount;
4. restore database;
5. recover database;
6. alter database open resetlogs;
2. 丢失 datafile
步骤：
1. startup mount;
2. restore datefile ‘num’;
3. recover datafile ‘num’;
4 .alter database open;
（这里的 ‘num’ 是通过 dba_data_files 查询得出）
3. 丢失redo
步骤：
1. sql> startup
2. sql> recover database until cancel;
3. sql>alter database open resetlogs;
4, 表空间损坏（这时数据库可以启动）比如：spring_zone坏了
步骤：
1. sql>alter tablespace spring_zone offline;
2. sql>restore tablespace spring_zone;
3. sql>recover tablespace spring_zone;
4. sql>alter tablespace spring_zone online;
应急措施：
Flashback 系列：
1. alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
2. select sysdate from dual;(恢复时间点)
3. select * from v$flashback_database_log;（可以恢复到的最早时间点）
======模拟丢失=======
4. shutdown immediate
5. startup mount;
6. flashback database to timestamp(to_date('2008-04-26 14:39:26','yyyy-mm-dd hh24:mi:ss'));
7. alter database open resetlogs;
********************************************************************************
8. 内存参数：
SQL> show sga
Total System Global Area 2701131776 bytes
Fixed Size 2023240 bytes
Variable Size 1056964792 bytes
Database Buffers 1627389952 bytes
Redo Buffers 14753792 bytes
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 684M
SQL>
9. 备份使用rman 来进行备份恢复，
恢复的策略是：
每天中午，晚上各备份一次
C
ocuments and SettingsAdministrator>rman target/ catalog rman/rman@recover
恢复管理器: Release 10.2.0.1.0 - Production on 星期六 4月 26 18:38:22 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到目标数据库: LEE (DBID=485914818)
连接到恢复目录数据库
RMAN> show all;
RMAN 配置参数为:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'H:/oracle/leeba
ck/incre/control_%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 3 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'H:ORACLEPRODUCT10.2.0DB_1DATABASES
NCFLEE.ORA'; # default
RMAN>
这个是以设置default的
备份用这个：
RUN {
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backup/fullback/control_%F';
ALLOCATE CHANNEL CH1 DEVICE TYPE DISK FORMAT '/backup/fullback/ch1_%U';
ALLOCATE CHANNEL CH2 DEVICE TYPE DISK FORMAT '/backup/fullback/ch2_%U';
ALLOCATE CHANNEL CH3 DEVICE TYPE DISK FORMAT '/backup/fullback/ch3_%U';
BACKUP DATABASE SKIP INACCESSIBLE FILESPERSET 10
PLUS ARCHIVELOG FILESPERSET 20
DELETE ALL INPUT;
RELEASE CHANNEL CH1;
RELEASE CHANNEL CH2;
RELEASE CHANNEL CH3;
}
ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE DISK;
CROSSCHECK BACKUPSET;
DELETE NOPROMPT OBSOLETE;
觉得留两个冗余就好了，怕空间不够。
请大家多多提议！！谢谢各位，还有谢谢三思的资料！！
目前只能做这么多了，自己~~~~
不知道 后果怎么样..........
******************************************************************************************
昨天下午叫了300人来测试，发现有些问题，怪怪的：（表面上没什么，但就觉得很有问题！）
1. 首先是sga 建议图。 附件 里 图1~3.
2. ash报告 附件里
3. 是addm 这个贴出来：------------------------------------------------------------------------------------
DETAILED ADDM REPORT FOR TASK 'ADDM:1284598953_1_116' WITH ID 222
-----------------------------------------------------------------
Analysis Period: 27-APR-2008 from 16:49:10 to 17:20:13
Database ID/Instance: 1284598953/1
Database/Instance Names: ORC1/orc1
Host Name: localhost.localdomain
Database Version: 10.2.0.1.0
Snapshot Range: from 115 to 116
Database Time: 1854 seconds
Average Database Load: 1 active sessions
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
FINDING 1: 71% impact (1308 seconds)
------------------------------------
Waits on event "log file sync" while performing COMMIT and ROLLBACK operations
were consuming significant database time.
RECOMMENDATION 1: Application Analysis, 71% benefit (1308 seconds)
ACTION: Investigate application logic for possible reduction in the
number of COMMIT operations by increasing the size of transactions.
RATIONALE: The application was performing 5879 transactions per minute
with an average redo size of 2503 bytes per transaction.
RECOMMENDATION 2: Host Configuration, 71% benefit (1308 seconds)
ACTION: Investigate the possibility of improving the performance of I/O
to the online redo log files.
RATIONALE: The average size of writes to the online redo log files was 2
K and the average time per write was 4 milliseconds.
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Wait class "Commit" was consuming significant database time.
(71% impact [1308 seconds])
FINDING 2: 17% impact (317 seconds)
-----------------------------------
SQL statements consuming significant database time were found.
RECOMMENDATION 1: SQL Tuning, 5.3% benefit (98 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"4jjxyxwf6my91".
RELEVANT OBJECT: SQL statement with SQL_ID 4jjxyxwf6my91 and
PLAN_HASH 1262121372
update t_item_score set user_id2=:1 , score2=:2 ,type=:3,time2=:4
where subject_id=:5 and item_id=:6 and stu_id=:7
RATIONALE: SQL statement with SQL_ID "4jjxyxwf6my91" was executed 65768
times and had an average elapsed time of 0.0012 seconds.
RECOMMENDATION 2: SQL Tuning, 5% benefit (93 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"0sa0u3kqs005q".
RELEVANT OBJECT: SQL statement with SQL_ID 0sa0u3kqs005q and
PLAN_HASH 1262121372
update t_item_score set user_id1=:1 , score1=:2 ,type=:3,time1=:4
where subject_id=:5 and item_id=:6 and stu_id=:7
RATIONALE: SQL statement with SQL_ID "0sa0u3kqs005q" was executed 66380
times and had an average elapsed time of 0.0011 seconds.
RECOMMENDATION 3: SQL Tuning, 3.2% benefit (60 seconds)
ACTION: Investigate the SQL statement with SQL_ID "9yz2mmcy17bg7" for
possible performance improvements.
RELEVANT OBJECT: SQL statement with SQL_ID 9yz2mmcy17bg7 and
PLAN_HASH 1259534581
select mark_num,count(mark_num) from t_item_score WHERE SUBJECT_ID=5
and ITEM_ID=37 group by mark_num order by mark_num asc
RATIONALE: SQL statement with SQL_ID "9yz2mmcy17bg7" was executed 49
times and had an average elapsed time of 1.2 seconds.
RATIONALE: At least one execution of the statement ran in parallel.
RECOMMENDATION 4: SQL Tuning, 3% benefit (56 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"gfkus2p5jhtm1".
RELEVANT OBJECT: SQL statement with SQL_ID gfkus2p5jhtm1 and
PLAN_HASH 1534102770
update t_item_score set user_id3=:1 where subject_id=:2 and
item_id=:3 and mark_num=2 and type=0 and user_id1:4 and
user_id2:5 and (user_id3 is null or user_id3=:6) and final_score is
null and rownum:3 and
user_id2:4 and (user_id3 is null or user_id3=:5) and final_score is
null and rownum:4 and
user_id2:5 and (user_id3 is null or user_id3=:6) and final_score is
null and rownum:3 and
user_id2:4 and (user_id3 is null or user_id3=:5) and final_score is
null and rownum
----------------------------------------------------------------------------------------------------------------------------
在71%那里可以看到，说是应用的问题是吧，commit太多，引起了system io的争抢....
RECOMMENDATION 2: Host Configuration, 71% benefit (1308 seconds)
ACTION: Investigate the possibility of improving the performance of I/O
to the online redo log files.
RATIONALE: The average size of writes to the online redo log files was 2
K and the average time per write was 4 milliseconds.
这里的内容应该怎么条优呢？？大家建议一下，我的redo是1.5g太大了，那天弄了1个半小时都没有归档，决定把它调小一点：400m。
看过好像oracle 有建议redo大小的功能吧，现在还不知道怎么用。。。。觉得现在就是redo的写太频繁了！！！
------------------------------------------------------------------------
其他的就是sql的调优了！
还有linux下的内存使用：主库的oracle 使用内存是99%一直...晕死 （图在附件里，22和44图）， 开了恢复目录的数据库recover 这个是正常的（没调过什么参数）。
所以觉得是不是sga那些调错了！！
----------------------------------------------------------
[
本帖最后由 spring7777777 于 2008-5-7 14:03 编辑 ]
