ITPub博客

Using Multiple TablespacesAssigning Tablespace Quotas to Usersalter tablespace tt read only;-- read writealter tablespace tt rename to readonlySQL> alter tablespace readonly offline; Tablespace altered SQL> SQL> alter tablespace readonly online; Tablespace alteredDBA_TABLESPACESUSER_TABLESPACESV$TABLESPACEDBA_TABLESPACE_GROUPSCREATE TEMPORARY TABLESPACE lmtemp2 TEMPFILE '/u02/oracle/data/lmtemp201.dbf'SIZE 50MTABLESPACE GROUP group1;ALTER TABLESPACE lmtemp TABLESPACE GROUP group2;DB_nK_

archived log对应redo log，archive log文件大小稍微比redo小一些，大小基本一致，除非在redo log未填满的情况下手工切换在线日志归档，归档日志才会小。参数LOG_ARCHIVE_MAX_PROCESSES是动态的，不需要修改，如果修改：ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=3;多路归档成功与否：参数LOG_ARCHIVE_MIN_SUCCEED_DESTLOG_ARCHIVE_MIN_SUCCEED_DEST=n (n>=1)MANDATORY >=1MANDATORY OPTIONALREOPENif (LOG_ARCHIVE_MIN_SUCCEED_DEST < MANDATORY ) LOG_ARCHIVE_MIN_SUCCEED_DEST 此参数忽略end if;MANDATORY + OPTIONAL &gt

Oracle DBA职责及日常工作分析DBA 职责及日常工作职责:1.安装和升级数据库服务器,以及应用程序工具构建和配置网络环境.2.熟悉数据库系统的存储结构预测未来的存储需求,制订数据库的存储方案.3.根据开发人员设计的应用系统需求创建数据库存储结构.4.根据开发人员设计的应用系统需求创建数据库对象5.根据开发人员的反馈信息,在必要的时候修改数据库的结构6.管理数据库的用户维护数据库的安全性7.控制和监控用户对数据库的存取访问8.监控和优化数据库的性能9.制定数据库备份计划,灾难出现时对数据库信息进行恢复10.备份和恢复数据库11.联系数

LINUX所有配置文件的路径和文件名称 2008年02月25日 星期一 14:34 系统管理文件 1:/etc/sysconfig/i18n(语言配置文件). 2:/etc/sysconfig/network-scripts/ifcfg-eth0(eth0配置文件). 3:/boot/grub/grub.conf(grup配置文件) 或/boot/grub/menu.list. 4:/etc/passwd:系统用户数据库文件. 5:/etc/group:组的数据库文件. 6:/etc/gshadow(存放密码). 7:/etc/shadow(存放密码). 8:/e

系统 # uname -a # 查看内核/操作系统/CPU信息# head -n 1 /etc/issue # 查看操作系统版本# cat /proc/cpuinfo # 查看CPU信息# hostname # 查看计算机名# lspci -tv # 列出所有PCI设备# lsusb -tv # 列出所有USB设备# lsmod # 列出加载的内核模块# env # 查看环境变量 资源 # free -m # 查看内存使用量和交换区使用量# df -h # 查看各分区使用情况# du -sh <目录名> # 查看指定目录的

提交完成标志：Only when all redo recordsassociated with a given transaction are safely on disk in the online logs is the userprocess notified that the transaction has been committed.■ If archiving is disabled (the database is in NOARCHIVELOG mode), a filled redo logfile is available after the changes recorded in it have been written to the datafiles.■ If archiving is enabled (the database is in ARCHIVELOG mode), a filled redo log fileis available to LGWR after the changes recorded in it have b

SQL> startup restrict;ORACLE 例程已经启动。Total System Global Area 524288000 bytesFixed Size 1249968 bytesVariable Size 205524304 bytesDatabase Buffers 310378496 bytesRedo Buffers 7135232 bytes数据库装载完毕。数据库已经打开。SQL> alter system disable restricted session;系统已更改。SQL>restrict 只有管理员能操作database，其他用户连接不上，restrict用于维护数据库时使用[@more@]

1、备份控制文件：ALTER DATABASE BACKUP CONTROLFILE TO '/oracle/backup/control.bkp';ALTER DATABASE BACKUP CONTROLFILE TO TRACE;2、损坏 重建CREATE CONTROLFILESET DATABASE prodLOGFILE GROUP 1 ('/u01/oracle/prod/redo01_01.log','/u01/oracle/prod/redo01_02.log'),GROUP 2 ('/u01/oracle/prod/redo02_01.log','/u01/oracle/prod/redo02_02.log'),GROUP 3 ('/u01/oracle/prod/redo03_01.log','/u01/oracle/prod/redo03_02.log')RESETLOGSDATAFILE '/u01/oracle/prod/system01.dbf' SIZE 3M,'/u01/oracle/prod/rbs01.dbs' SIZE

shutdown 执行过程 是否需要恢复 normal 不允许新的连接 不需要 等待用户断开连接 immediate 不允许新的连接 不需要 不允许新的事务 回滚未提交的事务 隐式断开连接，并回滚 transactional 不允许新的连接 不需要 不允许新的事务 活动事务完成后 隐式断开连接 abort 不允许新的连接 需要 不允许新的

昨天用友u8服务器中招了，原来安装的symantec antivurs。想了下，再装个趋势，杀毒，2个一起岂不杀的更干净。于是乎装了个趋势在安全模式下，用symantec antivurs杀毒，查杀了接近2000个病毒，然后重启动，server怎么也起不来，重起了4-5次，看系统日志一点线索没有，郁闷，极其郁闷今天灵机一想，是不是symantec 和 趋势 2个防病毒软件之间不兼容，不能同时装阿于是卸载了趋势软件，重起机器，好了……幸亏用友u8用的人少，而且不怎么用了，换成axapta了，没出纰漏，要不然，还不知道怎么样呢真是的，折腾了我2天时间，狂晕[@more@]

select ksppinm,ksppdesc from x$ksppi where ksppinm like 'db%'结果如下1 dbwr_io_slaves DBWR I/O slaves2 db_file_name_convert datafile name convert patterns and strings for standby/clone db3 db_block_buffers Number of database blocks cached in memory4 db_block_checksum store checksum in db blocks and check during reads5 db_block_size Size of database block in bytes6 db_cache_size Size of DEFAULT buffer pool for standard block size buffers7 db_2k_cache_size Size of cache for 2K buffer

特别是ENABLE NOVALIDATE；可以不计前嫌，不验证之前的数据Constraint States■ ENABLE ensures that all incoming data conforms to the constraint■ DISABLE allows incoming data, regardless of whether it conforms to theconstraint■ VALIDATE ensures that existing data conforms to the constraint■ NOVALIDATE means that some existing data may not conform to the constraintIn addition:■ ENABLE VALIDATE is the same as ENABLE. The constraint is checked and isguaranteed to hold for all rows.■ ENABLE NOVALIDA

老婆怀孕5周孕酮低，开了琪宁，吃了一次，恶心，就没再吃。老婆特别怕冷，晚上得盖大厚被子，出汗。[@more@]

1)、简单分区rangelisthash2）、复合分区range listrange hashexample：1）、list partitioncreate table aaa_partitioned(id number,xm varchar2(100),location varchar2(100),note varchar2(100),constraint pk_aaa_partitioned primary key (id)) PARTITION BY LIST (location) ( PARTITION aaa_partitioned_beijing VALUES ('beijing'), PARTITION aaa_partitioned_shanghai VALUES ('shanghai'), PARTITION aaa_partitioned_tianjin VALUES ('tianjin'), PARTITION aaa_partitioned_chongqing VALUES ('chongqing'), PART

SQL> select count(*) from aaa_partitioned_table; COUNT(*)---------- 1000000 Executed in 0.375 seconds SQL> truncate table aaa_partitioned; Table truncated Executed in 2.172 seconds SQL> insert /*+ APPEND */ into aaa_partitioned nologging select * from aaa_partitioned_table; 1000000 rows inserted Executed in 29 seconds SQL> commit; Commit complete Executed in 0 seconds[@more@]

说明：冷备份，就是关闭db，物理copy数据文件、日志文件、控制文件、归档文件。rem ***** COLDBACKUP ORACLE DATABASE ON WINDOWS NT *****set ShutDownFile=C:ShutDown.SQLecho connect internal/oracle > %ShutDownFile%echo shutdown immediate >> %ShutDownFile%echo startup >> %ShutDownFile%echo shutdown normal >> %ShutDownFile%echo exit >> %ShutDownFile%rem ***** SHUTDOWN THE DATABASE *******set oracle_sid=dbprimsvrmgrl @%ShutDownFile%net stop OracleStart%oracle_sid%net stop OracleS

一、oracle startup 分3步1)nomount:启动实例，也就是加载sga和启动oracle后台进程2)mount：打开加载control file；把instrance和database关联起来3)open：加载db file；redo file等，并进行一致性检查startup;==startup nomount;alter database mount;alter database open;二、oracle shutdown 也分分3步1）close：把sga buffer写入db file、 redo file。关闭db redo file2）unmount：取消instance和database的关联，关闭control file3）shutdown：释放sga内存，关闭oracle后台进程。shutdown normalshutdown immediate;shutdown abort;[

1）private SQL area2）Session Memory（hold a session’s variables (logon information) and other information related to the session.）3）SQL Work Areas重要参数和动态性能视图PGA_AGGREGATE_TARGET 用来代替SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE and CREATE_BITMAP_AREA_SIZE.OPEN_CURSORS 每个会话打开的游标数，不需要太大，关键是应用程序要及时关闭游标。V$SYSSTATV$SESSTATV$PGASTATV$SQL_WORKAREAV$SQL_WORKAREA_ACTIVE[@more@]

SGA1、Followed components can be managed auto.1)shared pool(sql pl/sql execute, dictionary cache)2)buffer cache(data buffer read from /write to disk；使用两个list管理：write list/LRU list.)The write list holds dirty buffers, which contain data that has been modified but has not yet been written to disk. The LRU list holds free buffers, pinned buffers, and dirty buffers that have not yet been moved to the write list.3)redo buffer pool( redo buffer write to disk，log_buffer一般设置1M到3M,见注1)4)l

Creating a reverse key index, compared to a standard index, reverses the bytes of eachcolumn indexed (except the rowid) while keeping the column order. Such an arrangement can help avoid performance degradation with Real Application Clusters where modifications to the index are concentrated on a small set of leaf blocks. By reversing the keys of the index, the insertions become distributed across all leaf keys in the index.把索引的字段每个字段反转，反转索引的目的就是消除热点块，提高并发区别于DES