ORACLE学习笔记

1、ORACLE学习笔记环境变量l PCLERPDB2.posco-:10g:/DBMS/PCMRK> cat .profile一般安装过程l 确认OS版本，是否支持DB。 （）l 创建ORACLE账号，编辑环境变量PROFILEn # Groupadd dba /组建立n # Useradd m g dba orapcmrk /-m 创建用户主目录, -g 所属用户组n 对于DB安装的目录 需要给user权限u Chown R orapcmrk:dba /DBMS/PCMRK /将目录下的所有文件-R所属者改变为orapcmrku Chmod R 775 /DBMS/PCMRK /所有者具

2、有所有权限,组和其他成员具有读和执行权限l runInstaller 安装ORACLE软件l DBCA建立数据库l 定义监听手工创建数据库l 初始化参数文件，copy然后修改l 创建相关目录（参数文件中指定的目录）n Chown R oracle /dirl 口令文件创建 （OS命令）n Orapwd file=$ORACLE_HOME/dbs/orapwPCMRK.ora password=oracle - LINUXn Orapwd file=$ORACLE_HOME/database/orapwPCMRK.ora password=oracle - WINl 启动实例n Export s

3、id = ; sqlplus / as sysdbal 创建数据库命令n 建立数据库n 创建控制文件n 连接日志组n 创建系统表空间和数据文件n 创建system用户n 安装并且打开数据库l 安装数据字典n Sql> $ORACLE_HOME/rdbms/admin/catalog.sqll 安装系统包n Sql>$ORACLE_HOME/rdbms/admin/catproc.sqll 安装用户资源集n Sql>$ORACLE_HOME/sqlplus/admin/pupbld.sqll 配置监听网络l 临时表空间创建l 配置EM数据库结构l 口令文件: 有sys用户的口令

4、n 普通数据库用户忘记口令u Sys、system用户可以修改u Alter user user01 identified by pass;n DB管理员忘记口令u 服务器端OS认证方式连接后修改PASSu Sysplus / as sysdba; alter user sys identified by pass;n 口令文件损坏u 删除错误口令文件，重建口令文件 OS命令u Orapwd file=$ORACLE_HOME/dbs/orapwPCMRK.ora password=oracle - LINUXu Orapwd file=$ORACLE_HOME/database/orapwP

5、CMRK.ora password=oracle - WINu 10G是不需要重启DB的 l 参数文件：NOMOUNT , 指定控制文件位置l 控制文件：MOUNT，指定数据文件和联机日志文件地址n 多路复用，内容一致，用于恢复用l 数据文件：OPEN n 逻辑上位于表空间l 联机重做日志文件：OPENn 记录所有操作，多组多成员* alter user scott identified by tiger account unlock; 解锁账号SCOTTl 表空间 à 段 à 盘区 à 数据块 à OS块数据库后台进程l DBWR 写进程u 高速缓存到

6、数据文件u 脏数据过多，空余空间太少u 超时u CKPT进程触发，日志组切换l LGWR日志写进程u 重做日志缓冲区写到联机日志中u Commit时候，每隔3秒u DBWR写前l 服务器进程SNNNu 数据文件到数据库高速缓存l 系统监视进程SMONu 应急自动恢复 断电。l 进程监视PMONl 检查点进程CKPTu 同步数据文件和高速缓存区（通知DBWR把高速缓存区数据写到数据文件）l 存档进程ARCH服务器进程l 独占式服务器 l 共享式服务器*DDL语句不用COMMIT CREATE DROP RENAME GRANT回退段 会于 UNDOTBS1表空间， 用于COMMIT之前的回退。锁

7、 表锁（DDL） 行锁 （禁止修改）系统改变号l SCN 对应每一个事务，这个事务中所有操作前都带SCN号，确定这个操作是属于哪个SCN的。数据字典视图l USER开头：用户自己拥有的对象l ALL开头：用户可以访问的对象l DBA开头：整个DB拥有的对象l 其他重要视图u 动态的 和性能有关u DUALu 。安装数据库l DBCAl 手动安装u 清源并行安装报告u 配置EMl 需要确认 现在有错误l Emca config dbcontrol db repos createl Emctl start dbconsolel 测试PCERP可以安装，其他的DB可能缺少相关文件，DB安装时候的问题

8、。口令文件l 存放sys用户密码l 如果口令文件出错，需要重新建立u Orapwd file=$ORACLE_HOME/dbs/ password=oracleu Restart DB 10G不用重启参数文件读取顺序 spfileSID à spfile à initSID à 报错l 文本参数文件u InitDBID, 必须手动修改 （动态参数可以在命令行修改，但是不改文件）l 服务器参数文件u SpfileDBID，命令修改l 具体参数查询ORACLE文档l Create spfile from pfilel Create pfile from spfilel

9、 Scope 含义u SPFILE 指定SCOPE参数 一旦已经有了一个SPFILE，你就可以使用ALTER SYSTEM命令来改变任一初始化参数。然而，有一个相当重要的SCOPE参数你必须理解。 SCOPE参数具有三个值：SPFILE，MEMORY，以及BOTH。现在让我们看一看每一个值的范例。 Alter system set db_2k_cache_size=100m SCOPE=SPFILE; 如果你想在SPFILE中改变一个参数而不希望影响当前的例子，可以使用ALTER SYSTEM语句中的SCOPE=SPFILE选择。这对于从下一次启动中改变启动非常有好处。 Alter syste

10、m set db_2k_cache_size=100m SCOPE=MEMORY; 在以上的范例中，SCOPE=MEMORY选择告诉Oracle9i从例子中改变到下一次数据库获得的缺省值。 Alter system set db_2k_cache_size=100m SCOPE=BOTH; 当你指定SCOPE=BOTH，Oralce9i立即执行改变，并永久地使用改变，即使在下一次被数据库使用。 数据库存储结构表空间和数据文件l 常用表空间：u SYSTEM : 系统表和系统回滚段u SYSAUX：某些系统表位置u TEMP：临时段u UNDOTBS1：回滚段u USERS：用户数据所在l 表空

11、间类型u 永久：表。u 临时：TEMP表空间u 还原：回滚段UNDOTBS1 * Select * from dba_tablespaces;l 表空间管理u 区管理本地管理 位图表示空余盘区 现在使用u 区管理词典管理 系统表表示空余盘区 过时l 命令创建表空间u Create tablespace tablespace_name 永久表空间 默认datafile c:xtdbuser_data1.dbf size 10mextent management local 本地管理 （默认）segment space management auto  段自动管理u Create tem

12、porary tablespace tablespace_nametempfile size 10mextent management local uniform size 10k 统一大小* alter tablespace user online / offline* alter tablespace user read only / write * drop tablespace user including contents and datafilesl 表空间尺寸修改u Alter tablespace user add datafile size 200m;u Alter data

13、base datafile resize 200m;u Alter database datafile . autoextend on next 10m maxsize 500m;l 表空间数据文件移动u 表空间脱机u 复制数据文件到新地方u Alter tablespace user rename datafile to 修改控制文件u Online 表空间还原管理l 事务结束之前保存原数据,任何更改事务都会产生undo data, 事务结束前(commit前), 其他用户会使用undo data, 事务越大，需要的UNDO空间就越大。l 闪回 commit之后还有一段时间 可以还原数据，u

14、ndo_retention定义这个时间 l 断电后自动恢复l 还原表空间足够大 支持大的事务周期l 快照太旧 错误 防止 u increase the undo_retention and undo tablespace sizeu T1开始更改事务，原数据保存到UNDOu T2开始查询UNDO数据u T1 commit, T2还没结束查询u 如果 undo_retention 时间到，T2还没结束 报错u 如果 UNDO 数据已经被覆盖了，T2还没结束 报错u 也有可能LOB字段引起ul Alter tablespace undotbs1 retention guarantee;u 保留时间

15、内不能被覆盖，还原空间不够 直接报错u 默认是不保证临时段管理l 用于排序有关操作u 首先会在SORT_AREA_SIZE指定的内存中排序，如果不够才会使用临时段l 创建用户指定临时表空间u Create user scott identified by tiger default tabalespace users temporary tablespace temp;临时表空间l Select name,value from v$sysstat where name like sort% u 可以查询数据库的排序信息，内存排序 磁盘排序l select user from v$sort_us

16、age;u 可以查询正在排序的用户l Select file#, bytes/1024/1024 from v$tempfile;u 检查临时表空间容量l 创建临时表空间u Create temporary tablespace temp tempfile size 10M;u DB open状态就可以做l 修改默认临时表空间u Alter database default temporary tablespace temp;l 如果临时表空间没人使用，可以删除u Drop tablespace temp;l 查询现在的默认表空间u select * from database_propert

17、ies where property_name = 'DEFAULT_TEMP_TABLESPACE'联机重做日志l 手动检查点 触发DBWR 进程n Alter system checkpointn Log_checkpoint_timeout=60sl 强制日志切换 归档n Alter system switch logfile;l 增加日志组n Alter database add logfile group 3 size 3ml 增加日志组成员n Alter database ass logfile member to group 3;l 重做日志丢失的故障处理n 如果

18、日志错误，DB会崩溃，打开到MOUNT状态n 恢复方法1：首先检查重做日志文件状态，看看报错的日志文件的状态是否为CurrentSQL> select * from v$log;      SQL> select * from v$logfile;2：如果重做日志文件状态为Inactive，我们可以直接清除该日志文件的内容 然后重建：SQL> alter database drop logfile group 1;  SQL> alter database add logfile group 1 ( , ) size 4

19、m;3: 如果重做日志文件状态为Current，1) 通过下面步骤，数据库顺利打开SQL> recover database until cancel;                Type Cancel when prompted       SQL>alter database open resetlogs;2) 第一种情况的'recover database until cancel' 操作遇到ORA-01547，ORA

20、-01194，ORA-01110错误，需要整个数据库的物理备份，并根据归档日志恢复到错误时间点，前提是数据库是归档模式。restore old backup      SQL> startup mount      SQL> recover database until cancel using backup controlfile;      SQL> alter database open resetlogs;  这种情况就是下面的不

21、完全恢复实例控制文件 。l 控制文件单个错误解决u Init 修改参数文件 删除u Spfile 命令 alter system set control_file= scope=spfile;u 重启DBl 重建控制文件u 使用原来保存的创建命令u Alter database backup controlfile to trace.用户管理见清源并行安装文档With admin option 系统权限 给 用户1， 用户1可以再给用户2，回收用户1权限时候，用户2 的权限不回收。With grant option用户权限 给 用户1， 用户1可以再给用户2，回收用户1权限时候，用户2 的权限

22、也回收。备份与恢复l MTBF 最大化系统可用时间l MTTR 最小化恢复时间l 备份类型u 物理备份u 一致性备份 冷备份u 不一致性备份 热备份u 逻辑备份l 恢复u 实例恢复 断电引起 自动的u 介质恢复u Restore 装载 recover 恢复l 介质失败情况u 参数文件：无备份 就重建u 口令文件：重新建立u 控制文件：COPY或者创建u 临时文件损坏，DB自动重建，不用恢复u 数据文件和日志文件 下面l RAC+Dataguard 双机热备。l Sql> Archive log list; Ø 获取当前数据库的归档状态l 改变数据库归档模式Ø 修改初始

23、化参数u Show parameter 参数l Log_archive_format 归档文件格式l Log_archive_dest 归档文件路径u init 直接修改参数u spfile 采用命令修改n Alter system set log_archive_format = scopespfileu 加了scopespfile就是说只是在SPFILE里改了, 在当前的SESSION里没有改. 在ORACLE重启后就会生效.n Alter system set log_archive_dest = Ø 关闭DBØ 启动到 mount 下， - startup moun

24、tØ 启用 停止归档u Alter database archivelog; u Alter database noarchivelogl Oracle 10g 闪回恢复区Ø 我们服务器没有使用Ø 参数 db_recovery_file_dest ; db_recovery_file_dest_size;Ø 闪回恢复区可以自动删除过期的备份和归档日志文件Ø 如果满了，数据库停止u 删除归档日志l 手工删除，空间不能释放n Select * from substr(name,1,30), space_used from v$recovery_fi

25、le_dest;l 使用RMANn Crosscheck archivelog all;n Delete expired archivelog all;u 闪回区内容备份到其他介质l Backup recovery area;u 扩展闪回恢复区空间l Alter system set db_recovery_file_dest_size= scope=both逻辑备份和恢复 (数据级别)l EXP 逻辑备份Ø 设置导出字符u > set nls_lang = AMERICAN_AMERICA.ZHS16GBKu > export nls_lang = AMERICAN_A

26、MERICA.ZHS16GBKØ 使用范例u > exp POSMRK/chinaPCMRK file=C:test.dmp tables=POS_CHN_PASSWORD query="where user_id='PA500009'"l IMP 逻辑恢复Ø > imp POSMRK/chinaPCMRK file = c:test.dmp fromuser= touser= grants=nl 10g EXPDPØ 整个DB导出u expdp POSMRK/chinaPCMRK full=y DIRECTORY=

27、dmpdir DUMPFILE=test.dmpØ 导出用户对象u expdp POSMRK/chinaPCMRK schemas=POSMRK DIRECTORY=dmpdir DUMPFILE=test.dmpØ 导出表u expdp POSMRK/chinaPCMRK DIRECTORY=dmpdir DUMPFILE=test.dmp tables= query=Ø 导出表空间u expdp POSMRK/chinaPCMRK tablespace= DIRECTORY=dmpdir DUMPFILE=test.dmpØ 参数DIRECTORYu

28、 EXPDP是在服务器端运行，所以需要一个服务器端的目录来放导出文件u Sql> select * from dba_directories where directory_name=DATA_PUMP_DIR 已经有的目录只要权限付一下就可以了。u 新建目录l Sql> create or replace directory testdir as /l Sql> grant read,write on directory testdir to POSMRK;l 10g IMPDPØ 整个DB导入u impdp POSMRK/chinaPCMRK full=y DI

29、RECTORY=dmpdir DUMPFILE=test.dmpØ 导入用户对象u impdp POSMRK/chinaPCMRK schemas=POSMRK DIRECTORY=dmpdir DUMPFILE=test.dmpØ 导入表u impdp POSMRK/chinaPCMRK DIRECTORY=dmpdir DUMPFILE=test.dmp tables= query=Ø 导入表空间u impdp POSMRK/chinaPCMRK tablespace= DIRECTORY=dmpdir DUMPFILE=test.dmp物理备份和恢复数据文件

30、，控制文件，联机日志文件，归档日志文件，参数文件，口令文件l 冷备份 脱机 一致性Ø 关机 备份重要文件HOST COPY 重启数据库Ø Select name from v$datafile;Ø Select member from v$logfile;Ø Select name from v$controlfile;l 热备份 联机 不一致性Ø 用户管理热备份u 发出begin backup命令u 使用OS copy命令拷贝u 发出end backup命令u 备份归档日志u 范例l Sql> Alter tablespace syst

31、em begin backupl Sql> Host copy E:DBF c:dbfl Sql> Alter tablespace system end backupu 10g 增强l Alter database begin backupl 所有表空间一次全被置于热备状况l Alter database end backupØ 用户管理热备份的完全恢复u 故障前提：数据库故障，数据文件丢失，控制文件和日志文件都在u 恢复步骤：l 复制备份数据文件到原来目录 restorel Sql> Startup mount 启动DBn Sql> recover dat

32、abase 基于数据库恢复方法的数据库恢复n Sql> recover tablespace tbspace1 à 基于表空间恢复方法的数据库恢复n Sql> recover datafile .dbf à 基于数据文件恢复方法的数据库恢复l Sql> alter database open实际操作例子：正常应该是要restore 备份文件，再recoverl 如果是磁盘损坏，需要将备份数据文件restore到其他地方Alter database rename file old to newRecover datafile new其他操作和上面一样

33、6; 用户管理热备份的不完全恢复 联机日志文件损坏u 恢复步骤：u 没有联机日志，只能MOUNT DBl 可以直接尝试下面命令恢复，不行再做下面的步骤n Recover database until canceln Alter database open resetlogs;u 关闭DBu CP 所有备份的数据文件回来（虽然数据文件没损坏）u 启动到MOUNT状态u Recover database until cancelu Alter database open resetlogs;u Recover database until cancelu Recover database unti

34、l time 1992.u Recover database until change 22000SCN号u Alter database open resetlogsØ Oracle管理热备份（RMAN）u 安装RMAN变量l which rmanu Rman target / à 登录rmanu RMAN命令类型l list命令列出控制文件、RMAN恢复目录中备份信息l report用于判断数据库当前可恢复状态、以及数据库已有备份的信息l backup备份命令l configure配置命令u Show all à 显示所有配置u Configure reten

35、tion policy to redundancy 1 à 修改配置信息u Report obsolete à 列出废弃备份, 一片一片的数据u Delete obsolete à 删除过期备份u Crosscheck backup à 检查备份记录实际文件是否存在u Delete expired backup à 删除上面检查不存在的文件u Crosscheck archivelog all à 验证归档日志文件, 实际归档日志和控制文件中列表是否一致u Delete expired archivelog all à 删除

36、已经缺失的归档日志文件, 就是删除控制文件中的记录u list backup of archivelog all à 显示RMAN备份的归档日志，没有备份的不显示u list archivelog all à 显示所有的归档日志RMAN备份命令u 数据库全备l Rman> run Allocate channel dl device type DISK backup database plus archivelog  u 数据文件备份l Rman> run Allocate channel dl device type DISK backup data

37、file . ; u 表空间备份l Rman> run Allocate channel dl device type DISK backup tablespace . ; u 归档日志备份l Rman> run Allocate channel dl device type DISK backup archivelog from sequence=234 delete input ; u 控制文件备份l Rman>configure controlfile autobackup on;l Rman> backup current controlfile format

38、à 备份控制文件u 镜像COPYl 使用备份集格式备份的RMAN，恢复只能使用RMAN恢复。l RMAN支持镜像格式，类似直接复制，恢复手工复制或者RMAN都可以。l 10g的DB全备l Rman> run Backup as copy tag “” database;u Rman> list backup à 显示备份集列表u Rman> list copy à 显示镜像COPY列表RMAN恢复命令u 控制文件恢复l Show controlfile autobackupn 显示是否自动备份控制文件n 由于控制文件保存有RMAN恢复目录信息，所

39、以很重要n 如果自动备份，只要数据库发生变化，控制文件就自动备份n 如果自动备份，只要备份数据库，控制文件也自动备份n 由于自动备份了控制文件，所以可以从自动备份中恢复控制文件u Rman> Restore spfile to from autobackup; à 恢复spfileu Rman> Restore controlfile from autobackup; à 恢复控制文件l 得到控制文件的创建命令n Sql> alter database backup controlfile to tracen Sql> gettrcname 

40、4; 得到生成的跟踪文件信息l 重新创建数据库 控制文件 n Create controlfile .u 参数 Noresetlogs Ø 如果redo日志都在，重新创建控制文件不需要重建日志文件（完全恢复）Ø 重新创建控制文件需要在nomount下Ø 建立控制文件后，恢复命令：sql> recover databaseØ Sql> alter database open.u 参数 resetlogs Ø 如果redo日志都在，重新创建控制文件需要重建日志文件（不完全恢复）Ø 重新创建控制文件需要在nomount下

41、6; 建立控制文件后，恢复命令：sql> recover database using backup controlfile until cancell 要理解recover database using backup controlfile，先理解 recover database ，也就是说，不加using backup controlfile的情况。在 普通的recover database 或者 recover tablespace, recover datafile时， Oracle会以当前controlfile所纪录的SCN为准，利用archive log和 redo log

42、的redo entry, 把相关的datafile 的 block恢复到“当前controlfile所纪录的SCN”而 某些情况下，Oracle需要把数据恢复到比当前controlfile所纪录的SCN还要靠后的位置（比如说，control file是backup controlfile , 或者 controlfile是根据trace create的。）,这时候，就需要用using backup controlfile. 恢复就不会受“当前controlfile所纪录的SCN”的限制。这时候的限制就来自于你的语句(until time , until scn)，或者可用的archive lo

43、g(until cancel) .Ø Sql> alter database open resetlogsl RMAN会对控制文件进行快照，这是最后一步，如果所有控制文件都没有的情况下。n linux目录: $ ORACLE_HOME/dbsn windows目录：$ORACLE_HOMEdatabasen 恢复步骤：n Rman target /n Rman> startup amountn Rman> restore controlfile from 快照目录;n Alter database mount;n Run set until scn 18909376

44、4;Restore database;Recover database; Or Run set until time 2008 08 00 Restore database; Recover database; n Alter database open resetlogs;u 基于镜像copy的表空间恢复 (数据文件)l Rman target /l Rman> list copyl Rman> startup mountl Rman> run set newname for datafile 8 to .dbf; restore datafile 8; switch da

45、tafile all; recover datafile 8; u 使用RMAN进行完全恢复l 控制文件完好 日志文件完好 数据文件损失l 数据库恢复l Rman> start mountl Rman> run restore database; 恢复文件 recover database; 恢复数据库 Sql ” alter database open” ; l 表空间恢复l Rman> start mountl Rman> run Sql ”alter tablespace tbs offline immediate;restore tablespace tbs;

46、恢复文件 recover tablespace tbs; 恢复数据库 Sql ” alter tablespace tbs online” ; l 数据文件恢复并且移动到新位置l Rman> start mountl Rman> run Sql ”alter tablespace tbs offline immediate; Set newname for datafile to restore tablespace tbs; 恢复文件switch datafile 5; 假设数据文件是5 recover tablespace tbs; 恢复数据库 Sql ” alter tabl

47、espace tbs online” ; u 使用RMAN进行不完全恢复l 基本流程n Mount 数据库n Restore 数据文件n 基于time, sequence, SCN恢复数据库n 使用resetlog打开数据库l Until timel Rman> run set until time = 2007-03-09 16:30:00 restore database; recover database;alter database open resetlogs; l Until sequencel Rman> run set until sequence = 120 th

48、read 1; Alter database mount;restore database; recover database; # recovers through log 119alter database open resetlogs; u 完全恢复和resetlogsl 只要有当前日志文件，数据库就可以完全恢复。l 如果使用备份的控制文件，需要使用resetlogs打开数据库。l 如果有当前控制文件或者重新建控制文件，不需要resetlogs打开数据库。u 特殊情况下的备份恢复l 看书l 闪回数据库。l 性能监控Ø 监控范围u 内存分配u IO磁盘u 资源冲突u 网络瓶颈u

49、应用（索引，SQL） - 重点Ø 管理数据库性能u 无效对象u 无用的索引u 优化器统计l 优化器选择SQL的执行计划l 优化器是基于统计量l 统计量可以自动 也可以手动收集n DBA_TABLES, DBA_COLUMNS, DBA_INDEXSu 性能指标l 动态视图信息来自实例和控制文件l DBA ALL USER视图信息来自数据字典Ø 监视ORACLEu AWR 自动工作负荷仓库: l 性能和动作相关的统计信息l AWR 位于sysaux表空间内l AWR快照默认保存7天， ADDM报告默认保存30天u 诊断与调整顾问程序l ADDM 数据库自动诊断监视程序l SQ

50、L调整顾问程序l SQL访问顾问程序l 内存顾问程序l 平均恢复时间顾问程序l 段顾问程序l 撤销顾问程序u 服务器生成的告警l MMON后台进程负责l 性能优化Ø 安装STATSPACK （oracle 10g以前使用）u 需要独立表空间 u Sql> spcreate 执行u 测试STATSPACKl Sql> execute statspack.snapl Sql> execute statspack.snapl Sql> spreport.sql 生成报告u Sql> spautol 生成job,定期执行JOB生成statspack.snapl

51、可以修改运行时间u Sql> spreport.sqll 列出所有的statspack.snapl 输入begin_snap 和 end_snapl begin_snap 和 end_snap 之间不能有停机事件u sql> execute dbms_job.remove(no);l 生成报告后，尽量删除job,很重要。u 删除历史数据l Sql> select max(snap_id) from stats$snapshot; l Sql> delete from stats$snapshot where snap_id <= 166Ø Statspa

52、ck 报告说明u 数据库概要信息u 负载概要信息l 针对v$sysstat视图收集的统计信息生成的报告备份恢复再统计1：冷备份 （控制文件; RODO LOG; 数据文件；） 恢复只要把上面这些文件 COPY 到原地 即可打开。 异地还需要 环境变量 和 参数文件2：一般热备份 的 恢复 1：数据文件顺坏 完全恢复 à COPY 备份的数据文件到原目录下 (先尝试恢复，不行再复制文件回来) à start mount à recover database ; recover tablespace tba; recover datafile dbf; à alter database open 如果要恢复到其他位置 à COPY 备份的数据文件到新目录下 à start mount àalter database rename file old to