//找出參數檔
檔案位置: C:\oracle\product\10.2.0\db_1\database\initorcl.ora
檔案內容: SPFILE='C:\oracle\product\10.2.0\db_1/dbs/spfileorcl.ora'
檔案位置: C:\oracle\product\10.2.0\db_1\dbs\SPFILEORCL.ORA
檔案內容: orcl.__java_pool_size=4194304
          orcl.__large_pool_size=4194304
          orcl.__shared_pool_size=192937984
          orcl.__streams_pool_size=0
          *.audit_file_dest='C:\oracle\product\10.2.0/admin/orcl/adump'
          *.background_dump_dest='C:\oracle\product\10.2.0/admin/orcl/bdump'
          *.compatible='10.2.0.1.0'
          *.control_files='C:\oracle\product\10.2.0\oradata\orcl\control01.ctl',
                          'C:\oracle\product\10.2.0\oradata\orcl\control02.ctl',
                          'C:\oracle\product\10.2.0\oradata\orcl\control03.ctl'
          *.core_dump_dest='C:\oracle\product\10.2.0/admin/orcl/cdump'
          *.db_block_size=8192
          *.db_domain=''
          *.db_file_multiblock_read_count=16
          *.db_name='orcl'
          *.db_recovery_file_dest='C:\oracle\product\10.2.0/flash_recovery_area'
          *.db_recovery_file_dest_size=2147483648
          *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
          *.job_queue_processes=10
          *.open_cursors=300
          *.pga_aggregate_target=177209344
          *.processes=150
          *.remote_login_passwordfile='EXCLUSIVE'
          *.sga_target=533725184
          *.undo_management='AUTO'
          *.undo_tablespace='UNDOTBS1'
          *.user_dump_dest='C:\oracle\product\10.2.0/admin/orcl/udump'
說明: 因為Oracle從initorcl.ora(PFILE型態)的內容,得知真的參數是用spfileorcl.ora(SPFILE型態),但spfile是binary ,無法修改,需先轉一下‧

//將參數檔從SPFILE轉成PFILE
步驟: SQL> CREATE PFILE FROM SPFILE;
      已建立檔案.

//修改參數檔內的 "db_name" 成你所要的名稱
檔案位置: C:\oracle\product\10.2.0\db_1\database\initorcl.ora
檔案內容: orcl.__db_cache_size=327155712
          orcl.__java_pool_size=4194304
          orcl.__large_pool_size=4194304
          orcl.__shared_pool_size=197132288
          orcl.__streams_pool_size=0
          *.audit_file_dest='C:\oracle\product\10.2.0/admin/orcl/adump'
          *.background_dump_dest='C:\oracle\product\10.2.0/admin/orcl/bdump'
          *.compatible='10.2.0.1.0'
          *.control_files='C:\oracle\product\10.2.0\oradata\orcl\control01.ctl',
                          'C:\oracle\product\10.2.0\oradata\orcl\control02.ctl',
                          'C:\oracle\product\10.2.0\oradata\orcl\control03.ctl'
          *.core_dump_dest='C:\oracle\product\10.2.0/admin/orcl/cdump'
          *.db_block_size=8192
          *.db_domain=''
          *.db_file_multiblock_read_count=16
          *.db_name='rock_db'
          *.db_recovery_file_dest='C:\oracle\product\10.2.0/flash_recovery_area'
          *.db_recovery_file_dest_size=2147483648
          *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
          *.job_queue_processes=10
          *.open_cursors=300
          *.pga_aggregate_target=177209344
          *.processes=150
          *.remote_login_passwordfile='EXCLUSIVE'
          *.sga_target=533725184
          *.undo_management='AUTO'
          *.undo_tablespace='UNDOTBS1'
          *.user_dump_dest='C:\oracle\product\10.2.0/admin/orcl/udump'

//匯出重建control file的Trace Log File
步驟: SQL> alter database backup controlfile to trace;
      已更改資料庫.

//查目前的Trace File的正確檔案和位置
步驟: SQL> select c.value || '\' || d.instance_name || '_ora_' || a.spid || '.trc' trace
        2  from v$process a,v$session b, v$parameter c, v$instance d
        3  where a.addr = b.paddr
        4        and b.audsid = userenv('sessionid')
        5        and c.name = 'user_dump_dest';
      TRACE
      --------------------------------------------------------------------------------
      C:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP\orcl_ora_2628.trc

//從Trace File取出重建Control File的Script,並將 "CREATE CONTROLFILE REUSE DATABASE"
//修改成 "CREATE CONTROLFILE SET DATABASE",待後面執行使用
檔案位置: C:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP\orcl_ora_2628.trc
修改內容: CREATE CONTROLFILE SET DATABASE "ORCL" RESETLOGS  NOARCHIVELOG
              MAXLOGFILES 16
              MAXLOGMEMBERS 3
              MAXDATAFILES 100
              MAXINSTANCES 8
              MAXLOGHISTORY 292
          LOGFILE
            GROUP 1 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG'  SIZE 50M,
            GROUP 2 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG'  SIZE 50M,
            GROUP 3 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG'  SIZE 50M
          -- STANDBY LOGFILE
          DATAFILE
            'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF',
            'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF',
            'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF',
            'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF'
          CHARACTER SET ZHT16MSWIN950

//Shutdown Database
步驟: SQL> shutdown immediate;
      資料庫關閉.
      資料庫已卸載.
      已關閉 ORACLE 執行處理.

//刪除原始的Control File
步驟: C:\oracle\product\10.2.0\oradata\orcl>dir *.ctl
       磁碟區 C 中的磁碟沒有標籤。
       磁碟區序號:  98B9-B271
      C:\oracle\product\10.2.0\oradata\orcl 的目錄
      2009/06/21  22:54         7,061,504 CONTROL01.CTL
      2009/06/21  22:54         7,061,504 CONTROL02.CTL
      2009/06/21  22:54         7,061,504 CONTROL03.CTL
                     3 個檔案      21,184,512 位元組
                     0 個目錄     521,949,184 位元組可用
      C:\oracle\product\10.2.0\oradata\orcl>del *.ctl

//將DB開啟至nomount狀態下,並重建Control File
步驟: SQL> startup nomount;
      ORACLE 執行處理已啟動.
      Total System Global Area  536870912 bytes
      Fixed Size                  1250016 bytes
      Variable Size             201329952 bytes
      Database Buffers          331350016 bytes
      Redo Buffers                2940928 bytes
      SQL> CREATE CONTROLFILE SET DATABASE "ROCK_DB" RESETLOGS  NOARCHIVELOG
        2      MAXLOGFILES 16
        3      MAXLOGMEMBERS 3
        4      MAXDATAFILES 100
        5      MAXINSTANCES 8
        6      MAXLOGHISTORY 292
        7  LOGFILE
        8    GROUP 1 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG'  SIZE 50M,
        9    GROUP 2 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG'  SIZE 50M,
       10    GROUP 3 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG'  SIZE 50M
       11  -- STANDBY LOGFILE
       12  DATAFILE
       13    'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF',
       14    'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF',
       15    'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF',
       16    'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF'
       17  CHARACTER SET ZHT16MSWIN950;
      已建立控制檔.

//開啟Database並做Resetlogs
步驟: SQL> alter database open resetlogs;
      已更改資料庫.

//查看Instance Name                                                                                              
步驟: SQL> SELECT instance_name,status FROM V$INSTANCE;                                                          
           INSTANCE_NAME    STATUS                                                                               
           ---------------- ------------                                                                         
           orcl             OPEN                                                                                 
                                                                                                                 
//查看Database Name                                                                                              
步驟: SQL> SELECT name,created,resetlogs_time FROM V$DATABASE;                                                   
           NAME      CREATED        RESETLOGS_TIME                                                               
           --------- -------------- --------------                                                               
           ROCK_DB   21-6?  -09     21-6?  -09                                                                   
                                                                                                                 
說明: 由此可知修改db_name參數,並不會連動修改原始的instance name‧Oracle是允許Instance Name和Database Name不一樣‧                                                                                                         
創作者介紹

香蕉皮

香 香蕉皮 發表在 痞客邦 PIXNET 留言(0) 人氣()