【引數】ORACLE修改資料庫名之完整版

leonarding發表於2012-05-22

引題:在實際生產環境下我們可能需要快速的複製現有的資料庫,又希望新庫與原庫不重名,或者我們需要複製生產庫用來做研發測試,如果我們採用DBCA的方式,缺點是慢並且還沒有資料,就需要從新導資料,費時又麻煩。下面我就詳細介紹如何修改資料庫名來完成使命,採用2種方法(1. 重建控制檔案修改資料庫名2.使用nid工具修改資料庫名)

一、重建控制檔案修改資料庫名

步驟概述

  • 生成controlfile建立指令碼並寫入trace檔案
  • 執行查詢trace檔案語句,獲取當前trace檔案路徑
  • 停掉資料庫
  • 只複製資料檔案、redo日誌(不需要複製控制檔案哦!)
  • 生成新的initLEO2.orac純文字引數檔案
  • 編輯新生成的initLEO2.ora 純文字引數檔案
  • 利用initLEO2.ora 純文字引數檔案建立spfileLEO2.ora二進位制引數檔案、
  • 資料庫啟動到nomount狀態,載入引數檔案
  • 刪除原庫控制檔案(如果複製的時候包括了控制檔案的話)
  • 修改/u01/app/oracle/admin/LEO/udump/leo_ora_25621.trc檔案的內容

目標:我們用原庫LEO複製出新庫LEO2

1.生成controlfile建立指令碼並寫入trace檔案

SYS@LEO> alter database backup controlfile to trace;

2.執行查詢trace檔案語句,獲取當前trace檔案路徑

SYS@LEO> select c.value || '/' || d.instance_name || '_ora_' || to_char(a.spid,'fm99999') || '.trc' from v$process a, v$session b, v$parameter c, v$instance d  where a.addr = b.paddr and b.audsid = userenv('sessionid') and c.name = 'user_dump_dest';

Trace檔案路徑

C.VALUE||'/'||D.INSTANCE_NAME||'_ORA_'||TO_CHAR(A.SPID,'FM99999')||'.TRC'

--------------------------------------------------------------------------------

/u01/app/oracle/admin/LEO/udump/LEO_ora_25621.trc

3.停掉資料庫

SYS@LEO> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

4.只複製資料檔案、redo日誌(不需要複製控制檔案哦!)

從原來的/u01/app/oracle/oradata/LEO/disk1 複製到

/u01/app/oracle/oradata/LEO2/disk1

5.生成新的initLEO2.orac純文字引數檔案

export ORACLE_SID=LEO

sqlplus / as sysdba          進入原庫執行命令

SYS@LEO> create pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initLEO2.ora' from spfile;

6.編輯新生成的initLEO2.ora 純文字引數檔案

vim initLEO2.ora

需要修改的內容:

1)路徑

*.audit_file_dest='/u01/app/oracle/admin/LEO2/adump'

*.background_dump_dest='/u01/app/oracle/admin/LEO2/bdump'

*.control_files='/u01/app/oracle/oradata/LEO2/disk1/control01.ctl','/u01/app/oracle/oradata/LEO2/disk2/control02.ctl','/u01/app/oracle/oradata/LEO2/disk3/control03.ctl'

*.core_dump_dest='/u01/app/oracle/admin/LEO2/cdump'

*.user_dump_dest='/u01/app/oracle/admin/LEO2/udump'

2)資料庫名

*.db_name='LEO2'

7.利用initLEO2.ora 純文字引數檔案建立spfileLEO2.ora二進位制引數檔案

export ORACLE_SID=LEO2

sqlplus / as sysdba

SYS@LEO2> create spfile from pfile;        建立spfile

8.資料庫啟動到nomount狀態,載入引數檔案

SYS@LEO2> startup nomount

ORACLE instance started.

Total System Global Area  314572800 bytes

Fixed Size                       1219184 bytes

Variable Size                  96470416 bytes

Database Buffers          213909504 bytes

Redo Buffers                 2973696 bytes

SYS@LEO2> show parameter name

NAME                                      TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_file_name_convert           string

db_name                                string         LEO2

db_unique_name                   string         LEO2

global_names                        boolean     FALSE

instance_name                      string         LEO2

lock_name_space                  string

log_file_name_convert          string

service_names                       string         LEO2

SYS@LEO2> select instance_name from v$instance;

INSTANCE_NAME

----------------

LEO2

9.刪除原庫控制檔案(如果複製的時候包括了控制檔案的話)

路徑:

/u01/app/oracle/oradata/LEO2/disk1/control01.ctl,

/u01/app/oracle/oradata/LEO2/disk2/control02.ctl,

/u01/app/oracle/oradata/LEO2/disk3/control03.ctl

10.修改/u01/app/oracle/admin/LEO/udump/leo_ora_25621.trc檔案的內容

vim  leo_ora_25621.trc

把內容複製到vim  test.sql 臨時檔案中修改

定位--     Set #2. RESETLOGS case  位置

1)把CREATE CONTROLFILE REUSE DATABASE "LEO" RESETLOGS  NOARCHIVELOG

修改成

CREATE CONTROLFILE SET DATABASE "LEO2" RESETLOGS  NOARCHIVELOG

2)把線上日誌檔案和資料檔案的路徑中的“PROD”替換成“LEO2”,修改路徑

例子:/u01/app/oracle/oradata/PROD/disk1/  替換

/u01/app/oracle/oradata/LEO2/disk1/

使用:%s#PROD#LEO2# 方法替換所有“PROD

最後的完成版並執行此指令碼,就完成了控制檔案的建立

CREATE CONTROLFILE SET DATABASE "LEO2" RESETLOGS  NOARCHIVELOG

    MAXLOGFILES 30

    MAXLOGMEMBERS 5

    MAXDATAFILES 100

    MAXINSTANCES 1

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 (

    '/u01/app/oracle/oradata/LEO2/disk1/redo01.log',

    '/u01/app/oracle/oradata/LEO2/disk2/redo01_2.log'

  ) SIZE 100M,

  GROUP 2 (

    '/u01/app/oracle/oradata/LEO2/disk1/redo02.log',

    '/u01/app/oracle/oradata/LEO2/disk2/redo02_2.log'

  ) SIZE 100M,

  GROUP 3 (

    '/u01/app/oracle/oradata/LEO2/disk1/redo03.log',

    '/u01/app/oracle/oradata/LEO2/disk2/redo03_2.log'

  ) SIZE 100M,

  GROUP 4 (

    '/u01/app/oracle/oradata/LEO2/disk1/redo04_1.log',

    '/u01/app/oracle/oradata/LEO2/disk2/redo04_2.log'

  ) SIZE 100M,

GROUP 5 (

    '/u01/app/oracle/oradata/LEO2/disk1/redo05_1.log',

    '/u01/app/oracle/oradata/LEO2/disk2/redo05_2.log'

) SIZE 100M

DATAFILE

  '/u01/app/oracle/oradata/LEO2/disk1/system01.dbf',

  '/u01/app/oracle/oradata/LEO2/disk1/undotbs01.dbf',

  '/u01/app/oracle/oradata/LEO2/disk1/sysaux01.dbf',

  '/u01/app/oracle/oradata/LEO2/disk1/example01.dbf',

  '/u01/app/oracle/oradata/LEO2/disk1/indx01.dbf',

  '/u01/app/oracle/oradata/LEO2/disk1/tools01.dbf',

  '/u01/app/oracle/oradata/LEO2/disk1/user01.dbf',

  '/u01/app/oracle/oradata/LEO2/disk1/oltp01.dbf'

CHARACTER SET US7ASCII

;

11.把資料庫啟動到mount狀態,在啟動到open狀態

注:必須新增 resetlogs 選項開啟資料庫,resetlogs 所有以前的備份和歸檔日誌均不可用

注:當我們執行完控制檔案建立指令碼後,資料庫就已經是mount狀態了。

SYS@LEO2> select open_mode,log_mode,name,dbid,db_unique_name from v$database;

 

OPEN_MODE  LOG_MODE     NAME            DBID     DB_UNIQUE_NAME

---------- ------------ --------- ---------- -----------------------------------------------------------------------

MOUNTED NOARCHIVELOG     LEO2           1558123773  LEO2

SYS@LEO2> alter database open resetlogs;  

SYS@LEO2> select file_name,file_id,tablespace_name,status from dba_data_files;

FILE_NAME                                                            FILE_ID TABLESPACE_NAME      STATUS

-------------------------------------------------- ---------- --------

/u01/app/oracle/oradata/LEO2/disk1/oltp01.dbf         8        OLTP                     AVAILABLE

/u01/app/oracle/oradata/LEO2/disk1/user01.dbf        7        USERS                   AVAILABLE

/u01/app/oracle/oradata/LEO2/disk1/tools01.dbf              TOOLS                   AVAILABLE

/u01/app/oracle/oradata/LEO2/disk1/indx01.dbf        5         INDX                    AVAILABLE

/u01/app/oracle/oradata/LEO2/disk1/example01.dbf 4        EXAMPLE               AVAILABLE

/u01/app/oracle/oradata/LEO2/disk1/sysaux01.dbf    3        SYSAUX                  AVAILABLE

/u01/app/oracle/oradata/LEO2/disk1/undotbs01.dbf  2        UNDOTBS              AVAILABLE

/u01/app/oracle/oradata/LEO2/disk1/system01.dbf     1  SYSTEM               AVAILABLE

11.新建LEO2密碼檔案

orapwd file=orapwLEO2 password=oracle entries=30

 

二、使用nid工具修改資料庫名

Nidoracle軟體提供的一個小工具,可以修改資料庫名稱,位置:$ORACLE_HOME/bin/

優點:可以不用重建控制檔案就修改資料庫名

步驟概述

  • 驗證當前資料庫名,例項名,服務名引數
  • 停掉資料庫
  • 資料庫啟動到mount狀態,nid工具在mount狀態下才能用,直接修改控制檔案內容
  • 生成新的initLEO3.orac純文字引數檔案並修改資料庫名
  • 利用initLEO3.ora 純文字引數檔案建立spfileLEO3.ora二進位制引數檔案
  • 建立轉儲目錄
  • 啟動資料庫到open狀態
  • 檢查當前資料庫名,例項名,服務名引數
  • 新建LEO3密碼檔案
  • 修改listener.oratnsnames.ora 檔案內容

目標:我們用原庫LEO2修改成新庫LEO3,還用原來的控制檔案、資料檔案、線上日誌

1. 驗證當前資料庫名,例項名,服務名引數

SYS@LEO2> show parameter name

NAME                                     TYPE        VALUE

------------------------------------ ----------- --------------------------------------------

db_file_name_convert          string

db_name                               string        LEO2

db_unique_name                  string        LEO2

global_names                       boolean     FALSE

instance_name                     string        LEO2

lock_name_space                 string

log_file_name_convert         string

service_names                      string        LEO2

SYS@LEO2> select open_mode,log_mode,name,dbid,db_unique_name from v$database;

OPEN_MODE  LOG_MODE         NAME            DBID  DB_UNIQUE_NAME

---------- ------------ --------- ---------- ----------------------------------------------------------------------

READ WRITE NOARCHIVELOG    LEO2       1558123773  LEO2

2. 停掉資料庫

SYS@LEO2> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

3. 資料庫啟動到mount狀態,nid工具在mount狀態下才能用,直接修改控制檔案內容

export ORACLE_SID=LEO2

sqlplus / as sysdba

SYS@LEO2> startup mount

ORACLE instance started.

Total System Global Area  314572800 bytes

Fixed Size                       1219184 bytes

Variable Size                  96470416 bytes

Database Buffers          213909504 bytes

Redo Buffers                 2973696 bytes

Database mounted.

SYS@LEO2> exit

nid help=y

DBNEWID: Release 10.2.0.1.0 - Production on Mon May 21 16:57:06 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Keyword     Description                              (Default)

-----------------------------------------------------------------

TARGET       Username/Password               (NONE)

DBNAME     New database name               (NONE)

LOGFILE      Output Log                               (NONE)

REVERT       Revert failed change                NO

SETNAME    Set a new database name only NO

APPEND      Append to output log                NO

HELP           Displays these messages         NO

nid  target=sys/oracle dbname=LEO3

DBNEWID: Release 10.2.0.1.0 - Production on Mon May 21 16:59:10 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to database LEO2 (DBID=1558123773)       --已連線當前資料庫名

Connected to server version 10.2.0                   --已連線伺服器版本

Control Files in database:                           --控制檔案路徑

    /u01/app/oracle/oradata/LEO2/disk1/control01.ctl

    /u01/app/oracle/oradata/LEO2/disk2/control02.ctl

    /u01/app/oracle/oradata/LEO2/disk3/control03.ctl

--是否同意修改資料庫ID和資料庫名

Change database ID and database name LEO2 to LEO3? (Y/[N]) =>y

Proceeding with operation

Changing database ID from 1558123773 to 1231291551  --修改資料庫ID

Changing database name from LEO2 to LEO3                   --修改資料庫名

    Control File /u01/app/oracle/oradata/LEO2/disk1/control01.ctl – modified --控制檔案已修改

    Control File /u01/app/oracle/oradata/LEO2/disk2/control02.ctl – modified

    Control File /u01/app/oracle/oradata/LEO2/disk3/control03.ctl – modified

    Datafile /u01/app/oracle/oradata/LEO2/disk1/system01.dbf - dbid changed, wrote new name

    Datafile /u01/app/oracle/oradata/LEO2/disk1/undotbs01.dbf - dbid changed, wrote new name

    Datafile /u01/app/oracle/oradata/LEO2/disk1/sysaux01.dbf - dbid changed, wrote new name

    Datafile /u01/app/oracle/oradata/LEO2/disk1/example01.dbf - dbid changed, wrote new name

    Datafile /u01/app/oracle/oradata/LEO2/disk1/indx01.dbf - dbid changed, wrote new name

    Datafile /u01/app/oracle/oradata/LEO2/disk1/tools01.dbf - dbid changed, wrote new name

    Datafile /u01/app/oracle/oradata/LEO2/disk1/user01.dbf - dbid changed, wrote new name

    Datafile /u01/app/oracle/oradata/LEO2/disk1/oltp01.dbf - dbid changed, wrote new name

    Control File /u01/app/oracle/oradata/LEO2/disk1/control01.ctl - dbid changed, wrote new name

    Control File /u01/app/oracle/oradata/LEO2/disk2/control02.ctl - dbid changed, wrote new name

    Control File /u01/app/oracle/oradata/LEO2/disk3/control03.ctl - dbid changed, wrote new name

    Instance shut down         --當前資料庫已經關閉

 

Database name changed to LEO3.  資料庫名已經修改完畢

Modify parameter file and generate a new password file before restarting.

--修改引數檔案和在重啟之前生成新的密碼檔案

Database ID for database LEO3 changed to 1231291551.

--資料庫ID已經修改為:1231291551.

All previous backups and archived redo logs for this database are unusable.

--以前的備份和歸檔日誌均不可用

Database has been shutdown, open database with RESETLOGS option.

--資料庫已關閉,必須使用RESETLOGS選項開啟資料庫

Succesfully changed database name and ID.

--已成功修改資料庫名和資料庫ID

DBNEWID - Completed succesfully.

-- DBNEWID成功完成

4. 生成新的initLEO3.orac純文字引數檔案並修改資料庫名和路徑

SYS@LEO2> create pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initLEO3.ora' from spfile;

vim  initLEO3.ora

1)路徑

*.audit_file_dest='/u01/app/oracle/admin/LEO3/adump'

*.background_dump_dest='/u01/app/oracle/admin/LEO3/bdump'

*.control_files='/u01/app/oracle/oradata/LEO3/disk1/control01.ctl','/u01/app/oracle/oradata/LEO2/disk2/control02.ctl','/u01/app/oracle/oradata/LEO3/disk3/control03.ctl'

*.core_dump_dest='/u01/app/oracle/admin/LEO3/cdump'

*.user_dump_dest='/u01/app/oracle/admin/LEO3/udump'

2)資料庫名

*.db_name='LEO3'

5. 利用initLEO3.ora 純文字引數檔案建立spfileLEO3.ora二進位制引數檔案

SYS@LEO2> create spfile='/u01/app/oracle/product/10.2.0/db_1/dbs/spfileLEO3.ora' from pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initLEO3.ora';

6.建立轉儲目錄

mkdir -p $ORACLE_BASE/admin/LEO3/adump

mkdir -p $ORACLE_BASE/admin/LEO3/bdump

mkdir -p $ORACLE_BASE/admin/LEO3/cdump

mkdir -p $ORACLE_BASE/admin/LEO3/udump

7. 直接啟動資料庫到open狀態

注:必須新增 resetlogs 選項開啟資料庫,resetlogs 所有以前的備份和歸檔日誌均不可用

export ORACLE_SID=LEO3

sqlplus / as sysdba

SYS@LEO3> startup

ORACLE instance started.

Total System Global Area  314572800 bytes

Fixed Size                       1219184 bytes

Variable Size                  96470416 bytes

Database Buffers          213909504 bytes

Redo Buffers                 2973696 bytes

Database mounted.

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

必須使用RESETLOGSNORESETLOGS 選項開啟資料庫

SYS@LEO3> alter database open resetlogs;

8. 檢查當前資料庫名,例項名,服務名引數

SYS@LEO3> show parameter name

NAME                                     TYPE        VALUE

------------------------------------ ----------- ----------------------------------------------------

db_file_name_convert          string

db_name                               string        LEO3

db_unique_name                  string        LEO3

global_names                       boolean     FALSE

instance_name                     string         LEO3

lock_name_space                 string

log_file_name_convert         string

service_names                      string        LEO3

SYS@LEO3> select open_mode,log_mode,name,dbid,db_unique_name from v$database;

 

OPEN_MODE  LOG_MODE     NAME            DBID DB_UNIQUE_NAME

---------- ------------ --------- ---------- ------------------------------

READ WRITE NOARCHIVELOG    LEO3         1231291551   LEO3

SYS@LEO3> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS

---------------- ------------ ------------

LEO3              OPEN

9. 新建LEO3密碼檔案

orapwd file=orapwLEO3 password=oracle entries=30

10. 修改listener.oratnsnames.ora 檔案內容

cd  /u01/app/oracle/product/10.2.0/db_1/network/admin

修改vim listener.ora

LISTENER=

  (DESCRIPTION=

    (ADDRESS_LIST=

      (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.10)(PORT=1521))

      (ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))

 

SID_LIST_LISTENER=

  (SID_LIST=

    (SID_DESC=

      (GLOBAL_DBNAME=LEO3)

      (ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1)

      (SID_NAME=LEO3))

    (SID_DESC=

      (SID_NAME=plsextproc)

      (ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1)

      (PROGRAM=extproc)))

修改 vim tnsnames.ora

LEO3=

  (description =

    (address=(protocol=tcp)(host=192.168.1.10)(port=1521))

    (connect_data=

        (server = dedicated)

        (service_name = LEO3)

  )

  )

重啟監聽

lsnrctl reload listener

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 21-MAY-2012 17:34:29

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=secdb1)(PORT=1521)))

The command completed successfully

測試監聽

sqlplus system/oracle@LEO3

SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 21 17:35:25 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SYSTEM@LEO3>

 

小結:到此所有的資料庫名、例項名、服務名已經都被修改過來了,有了這2種方法我們可以快速的建立起帶資料的新資料庫。支援我們的研發和測試

注意:引數檔案修改

      控制檔案修改

      密碼檔案修改

      監聽檔案修改

      資料檔案和日誌檔案根據需要進行建立

 

Leonarding

2012.5.21

於天津&summer

bloghttp://space.itpub.net/26686207

 

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26686207/viewspace-730563/,如需轉載,請註明出處,否則將追究法律責任。

相關文章