169it科技资讯


当前位置:  数据库>其它
本页文章导读:
    ▪Control File 恢复      场景一:重建control File •You’ve experienced a failure and lost all of your control files, and you belatedly realizethat you don’t have a good binary backup of the control file. •You want to change a database setting that can be modified .........
    ▪Oracle数据文件收缩      随着数据库的使用,数据文件越来越大,有没有办法将标记为free的block挤掉 相关表: DBA_DATA_FILES DBA_DATA_FILES describes database files. Column Datatype NULL Description FILE_NAME VARCHAR2(513)   N.........
    ▪Oracle 11g AWR 系列五:如何生成 AWR 报告?      1.生成单实例 AWR 报告: @$ORACLE_HOME/rdbms/admin/awrrpt.sql 2.生成 Oracle RAC AWR 报告: @$ORACLE_HOME/rdbms/admin/awrgrpt.sql 3.生成 RAC 环境中特定数据库实例的 AWR 报告: @$ORACLE_HOME/rdbms/admin/awrrpti.sql 4..........

[1]Control File 恢复
    来源: 互联网  发布时间: 2013-11-07

场景一:重建control File
•You’ve experienced a failure and lost all of your control files, and you belatedly realizethat you don’t have a good binary backup of the control file.
•You want to change a database setting that can be modified only by re-creating the con-trol file.
•You are relocating a large number of datafiles, and you find it easier to re-create the control file with the new names and locations (instead of manually renaming the datafiles)

解决:重建Control FIles

SQL> show parameter user_dump_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest                       string      /u01/app/oracle/diag/rdbms/pur
                                                 ple/purple1/trace
技巧:NAMING A TRACE FILE
Trace文件命名规则 <SID><Oracle process id><tracefile_identifier>.trc,我们可以通过设置tracefile_identifier来识别Trace文件。
SQL> alter session set tracefile_identifier=mytrace 

Session altered.

SQL> select distinct tracefile from v$process p,v$session s ,v$mystat m  where p.addr=s.paddr   and s.sid=m.sid;--查看当前TraceFile

TRACEFILE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/purple/purple1/trace/purple1_ora_14669_MYTRACE.trc


STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "PURPLE" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 192
    MAXLOGMEMBERS 3
    MAXDATAFILES 1024
    MAXINSTANCES 32
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '+DATA/purple/onlinelog/group_1.257.805043621'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '+DATA/purple/onlinelog/group_2.258.805043623'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '+DATA/purple/datafile/system.264.805043525',
  '+DATA/purple/datafile/sysaux.262.805043527',
  '+DATA/purple/datafile/undotbs1.256.805043527',
  '+DATA/purple/datafile/users.260.805043527',
  '+DATA/purple/datafile/undotbs2.263.805043817'
CHARACTER SET ZHS16GBK
;
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "PURPLE" NORESETLOGS ARCHIVELOG 

    MAXLOGFILES 192
    MAXLOGMEMBERS 3
    MAXDATAFILES 1024
    MAXINSTANCES 32
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '+DATA/purple/onlinelog/group_1.257.805043621'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '+DATA/purple/onlinelog/group_2.258.805043623'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '+DATA/purple/onlinelog/group_3.271.805043939'  SIZE 50M BLOCKSIZE 512,
  GROUP 4 '+DATA/purple/onlinelog/group_4.272.805043941'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '+DATA/purple/datafile/system.264.805043525',
  '+DATA/purple/datafile/sysaux.262.805043527',
  '+DATA/purple/datafile/undotbs1.256.805043527',
  '+DATA/purple/datafile/users.260.805043527',
  '+DATA/purple/datafile/undotbs2.263.805043817'
CHARACTER SET ZHS16GBK
;


场景二:多路复用控制文件的恢复

SQL> startup
ORACLE instance started.

Total System Global Area 1653518336 bytes
Fixed Size                  2213896 bytes
Variable Size            1090521080 bytes
Database Buffers          553648128 bytes
Redo Buffers                7135232 bytes
ORA-00205: error in identifying control file, check alert log for more info

检查alter.log
ORA-00210: ???????????
ORA-00202: ????: ''+DATA/purple/controlfile/current03''
ORA-17503: ksfdopn: 2 ?????? +DATA/purple/controlfile/current03
ORA-15173: entry 'current03' does not exist in directory 'controlfile'
SQL> alter system set control_files='+DATA/purple/controlfile/current01','+DATA/purple/controlfile/current02' scope=spfile sid='*'; --修改control Files
System altered.
SQL> startup
SQL> shutdown immediate 
ASMCMD> cp current02 current03 --拷贝好的控制文件 
copying +data/purple/controlfile/current02 -> +data/purple/controlfile/current03
SQL>startup nomount;
SQL> alter system set control_files='+DATA/purple/controlfile/current01','+DATA/purple/controlfile/current02','+DATA/purple/controlfile/current03' scope=spfile id='*';

场景三:使用RMAN 恢复 Control Files

RMAN>  restore controlfile to '+DATA/purple/controlfile/current04' from '+DATA/purple/controlfile/current01';

Starting restore at 22-JAN-2013 11:12:29
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=194 instance=purple1 device type=DISK

channel ORA_DISK_1: copied control file copy
Finished restore at 22-JAN-2013 11:12:33












        
作者:oracle_baidu 发表于2013-3-11 15:53:43 原文链接
阅读:20 评论:0 查看评论

    
[2]Oracle数据文件收缩
    来源: 互联网  发布时间: 2013-11-07

随着数据库的使用,数据文件越来越大,有没有办法将标记为free的block挤掉

相关表:

DBA_DATA_FILES

DBA_DATA_FILES describes database files.

Column Datatype NULL Description FILE_NAME VARCHAR2(513)   Name of the database file FILE_ID NUMBER NOT NULL File identifier number of the database file TABLESPACE_NAME VARCHAR2(30) NOT NULL Name of the tablespace to which the file belongs BYTES NUMBER   Size of the file in bytes BLOCKS NUMBER NOT NULL Size of the file in Oracle blocks STATUS VARCHAR2(9)   File status: AVAILABLE or INVALID (INVALID means that the file number is not in use, for example, a file in a tablespace that was dropped) RELATIVE_FNO NUMBER   Relative file number AUTOEXTENSIBLE VARCHAR2(3)   Autoextensible indicator MAXBYTES NUMBER   Maximum file size in bytes MAXBLOCKS NUMBER   Maximum file size in blocks INCREMENT_BY NUMBER   Number of Oracle blocks used as autoextension increment USER_BYTES NUMBER   The size of the file available for user data. The actual size of the file minus the USER_BYTES value is used to store file related metadata. USER_BLOCKS NUMBER   Number of blocks which can be used by the data ONLINE_STATUS VARCHAR2(7)   Online status of the file:
  • SYSOFF

  • SYSTEM

  • OFFLINE

  • ONLINE

  • RECOVER



DBA_FREE_SPACE

DBA_FREE_SPACE describes the free extents in all tablespaces in the database.

Note that if a datafile (or entire tablespace) is offline in a locally managed tablespace, you will not see any extent information. If an object has extents in an online file of the tablespace, you will see extent information about the offline datafile. However, if the object is entirely in the offline file, a query of this view will not return any records.


    
[3]Oracle 11g AWR 系列五:如何生成 AWR 报告?
    来源: 互联网  发布时间: 2013-11-07
1.生成单实例 AWR 报告:


@$ORACLE_HOME/rdbms/admin/awrrpt.sql


2.生成 Oracle RAC AWR 报告:


@$ORACLE_HOME/rdbms/admin/awrgrpt.sql


3.生成 RAC 环境中特定数据库实例的 AWR 报告:


@$ORACLE_HOME/rdbms/admin/awrrpti.sql


4.生成 Oracle RAC 环境中多个数据库实例的 AWR 报告的方法:


@$ORACLE_HOME/rdbms/admin/awrgrpti.sql


5.生成 SQL 语句的 AWR 报告:


@$ORACLE_HOME/rdbms/admin/awrsqrpt.sql


6.生成特定数据库实例上某个 SQL 语句的 AWR 报告:


@$ORACLE_HOME/rdbms/admin/awrsqrpi.sql


--生成 AWR 时段对比报告


7.生成单实例 AWR 时段对比报告


@$ORACLE_HOME/rdbms/admin/awrddrpt.sql


9.生成 Oracle RAC AWR 时段对比报告


@$ORACLE_HOME/rdbms/admin/awrgdrpt.sql


10.生成特定数据库实例的 AWR 时段对比报告


@$ORACLE_HOME/rdbms/admin/awrddrpi.sql


11.生成 Oracle RAC 环境下特定(多个)数据库实例的 AWR 时段对比报告


@$ORACLE_HOME/rdbms/admin/awrgdrpi.sql



转载请注明作者出处及原文链接:


http://blog.csdn.net/xiangsir/article/details/8661181














作者:xiangsir 发表于2013-3-11 22:02:35 原文链接
阅读:52 评论:0 查看评论

    
最新技术文章:
▪gc buffer busy/gcs log flush sync与log file sync    ▪让你的PL/SQL更好用    ▪ADO.NET中的非脱机数据库查询
▪参数job_queue_processes与Oracle jobs    ▪11gR2游标共享新特性带来的一些问题以及_cursor...    ▪_library_cache_advice和latch:shared pool、latch:shared poo...
▪SQL: Date Utility    ▪DB2 分区表增加分区    ▪DB2第一步 — 创建表
▪oracle 数据库    ▪插入10万条记录测试    ▪rebuild index VS. rebuild index online
▪如何处理undo tablespace 表空间太大的问题    ▪ado执行存储过程中包含结果集获取输出参数为...    ▪oracle函数的demo
▪Entity Framework 学习建议及自学资源    ▪存储过程的编写    ▪Linux/Unix shell 自动发送AWR report(二)
▪第二章 Oracle恢复内部原理(基础数据结构)    ▪Redis源码学习之【Tcp Socket封装】    ▪Java Jdbc减少与Oracle之间交互提升批量处理性能...
▪南大通用GBase8a Vs Oracle11g 单机测试亲测    ▪oracle 中行列转换    ▪rhel下安装oracle10g+asm---测试环境搭建
▪Redis系列-主从复制配置    ▪MySQL索引与查询优化    ▪INDEX受到NULL值的影响
▪测试人员的SQL语言 系列    ▪SQL数据库基本语句    ▪MySQL Replication常见错误整理[持续更新...]
▪eclipse下建立esper的demo    ▪把oracle rac 转化为单机数据库    ▪Redis系列-存储篇sorted set主要操作函数小结
▪基本的SQL*Plus报表和命令    ▪druid简单教程    ▪11g调度--scheduler使用
▪EF基础一    ▪db2存储过程中循环语句while do的continue有没有...    ▪oracle 创建DBLINK
▪DB2数据库备份还原    ▪Warning: prerequisite DBD::mysql 1 not found错误解决方...    ▪innotop性能监视mysql,innodb工具
▪数据迁移:DataGuard配置    ▪QX项目实战-19.跨库数据同步    ▪Mysql EXPLAIN
▪Oracle 11g AWR 系列七:Active Session History (ASH) 报...    ▪Oracle 11G新特性(共36个)    ▪父子节点问题
▪OEM简介及按钮乱码问题    ▪NoSql之MongoDB的常用类管理    ▪ORA-39700: database must be opened with UPGRADE option
▪node.js 访问redis数据库,pub/sub    ▪使用DBMS_REDEFINITION在线重定义分区表    ▪SQL Developer 使用问题与解决方法汇总
▪oralce 11g dataguard 概念    ▪ORA-30004 错误处理    ▪oracle分组函数rollup,cube
▪Sql Developer 使用问题与解决方法汇总    ▪Configure Oracle Dataguard Primary-ASM to Physical-ASM    ▪Oracle Data Guard 理论知识
▪Control File 恢复    ▪Oracle数据文件收缩    ▪Oracle 11g AWR 系列五:如何生成 AWR 报告?
▪Wireshark数据包分析实战(第2版)    ▪MySql用户权限控制    ▪db2和oracle查询序列区别
▪更新blob字段的存储过程    ▪MySQLReport分析报告三    ▪DB2中的序列
▪Oracle中DBMS_RANDOM.STRING 的用法    ▪SQL SERVER无法安装成功,sqlstp.log文件提示[未发...    ▪Data Guard 部署物理备库的 10 大注意事项
▪万能数据库查询分析器使用技巧之(九)    ▪SQL 自定义Split函数    ▪视图 v$sql,v$sqlarea,$sqltext,v$sqltext_with_newlines 的...
▪Data Guard Standby_archive_dest 和 Log_archive_dest_n 的...    ▪机房收费系统数据库设计(一)    ▪利用putty的SSH tunnel连接Oracle
▪DBCA建库偶遇ORA-27125    ▪使用PowerPivot建立简单的分析模型    ▪Linux/Unix shell 自动发送AWR report
▪写入到blob字段的存储过程    ▪关于JDBC中ResultSet接口的一点细节探究    ▪Data Guard 配置 Standby Redo Log
▪linux下redis的安装    ▪windows下redis的安装    ▪手动创建数据库步骤(简单翻译官方文档)
▪Ubuntu安装Mongodb    ▪SQL CLR应用    ▪redis的配置文件参数--详细说明
 


站内导航:


特别声明:169IT网站部分信息来自互联网,如果侵犯您的权利,请及时告知,本站将立即删除!

©2012-2017,169IT.COM,E-mail:www_169it_com#163.com(请将#改为@)

浙ICP备11055608号