场景一：重建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>  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 查看评论
        
      

      
      
    

      

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

相关表：

DBA_DATA_FILES describes database files.

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.

@$ORACLE_HOME/rdbms/admin/awrgdrpi.sql

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

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