Oracle學習心得-Change Database Name－香蕉皮

//找出參數檔
檔案位置: 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不一樣‧