Oracle修改(db_name,db_unique_name, servie_names,db_domain)方法

lovehewenyu發表於2013-04-11

Oracle 修改資料庫名db_name 方法及(db_unique_name, servie_names,db_domain修改方法)

 

前提:

備份引數檔案、控制檔案、使用者口令檔案、引數檔案、tnsnames.ora(個人習慣:什麼工作都會備份為先)

 

實際操作方法:

1、 備份文字控制檔案,並修改、編寫新的生成控制檔案SQL

2、 vi pfile ,修改db_name,dispatchers成你需要的資料庫名

3、 關閉資料庫(shutdown immedaite)

4、 開啟資料庫nomount(startup nomount pfile= xxx ;)

5、 使用編寫好的控制檔案SQL生成新的控制檔案

6、 新增臨時檔案

7、 修改使用者口令檔案、tnsnames.ora、並使用遠端登陸資料庫驗證

 

操作方法記錄如下:

1

備份文字控制檔案

sys@TEST> alter database backup controlfile to trace as '/home/ora/control.ctl';

Database altered.

編寫建立新控制檔案SQL

--      Set #2. RESETLOGS case

CREATE CONTROLFILE REUSE DATABASE "TEST" RESETLOGS   ARCHIVELOG

修改成 Oracle修改(db_name,db_unique_name, servie_names,db_domain)方法 Oracle修改(db_name,db_unique_name, servie_names,db_domain)方法 CREATE CONTROLFILE SET DATABASE " DOUDOU " RESETLOGS ARCHIVELOG

刪除下面一行

# RECOVER DATABASE USING BACKUP CONTROLFILE

因為我們是shutdown immediate關閉資料庫,沒有必要恢復SCN了!shutdown immediate會checkpoint ,所有的資料檔案SCN都會一致

 

生成新的控制檔案SQL如下:

Oracle修改(db_name,db_unique_name, servie_names,db_domain)方法 Oracle修改(db_name,db_unique_name, servie_names,db_domain)方法 CREATE CONTROLFILE SET DATABASE "DOUDOU" RESETLOGS ARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 '/opt/oracle/oradata/test/redo01.log'   SIZE 50M,

  GROUP 2 '/opt/oracle/oradata/test/redo02.log'   SIZE 50M,

  GROUP 3 '/opt/oracle/oradata/test/redo03.log'   SIZE 50M

DATAFILE

  '/opt/oracle/oradata/test/system01.dbf',

  '/opt/oracle/oradata/test/undotbs1.dbf',

  '/opt/oracle/oradata/test/sysaux01.dbf',

  '/opt/oracle/oradata/test/users01.dbf',

  '/opt/oracle/oradata/test/perfstat.dbf',

  '/opt/oracle/oradata/test/doudou.dbf',

  '/opt/oracle/oradata/test/doudou_index.dbf',

  '/opt/oracle/oradata/test/maclean.dbf',

  '/opt/oracle/oradata/test/test.dbf',

  '/opt/oracle/oradata/test/gaokao.dbf'

CHARACTER SET AL32UTF8

;

 

2

修改引數檔案

sys@TEST> alter system set db_name='doudou' scope=spfile;

alter system set db_name='doudou' scope=spfile

*

ERROR at line 1:

ORA-32017: failure in updating SPFILE

ORA-32016: parameter "db_name" cannot be updated in SPFILE

注:db_name不允許直接使用spfile檔案修改,所以選擇建立pfile,並修改

sys@TEST> create pfile from spfile;

File created.

修改以下2項引數

*.db_name=' doudou'

*.dispatchers='(PROTOCOL=TCP) (SERVICE= doudouXDB)'

 

3

關閉資料庫

sys@TEST> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

 

4

Startup nomount 開啟資料庫

SQL> startup nomount pfile='/opt/oracle/product/10.2.0/db_1/dbs/initdoudou.ora';

SQL> show parameter name

 

NAME                                  TYPE                    VALUE

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

db_file_name_convert                  string

db_name                               string                  doudou

db_unique_name                        string                  doudou

global_names                          boolean                 FALSE

instance_name                         string                  test

lock_name_space                       string

log_file_name_convert                 string

service_names                         string                  doudou

為什麼我們修改了db_name而例項名沒有變化呢,因為我們沒有設定例項名,這裡預設了oracle_sid(這也說明了oracle_sid和例項的關係,例項的唯一標識)

 

[ora@dg-pp dbs]$ ORACLE_SID=doudou

SQL> startup nomount pfile='/opt/oracle/product/10.2.0/db_1/dbs/initdoudou.ora';

SQL> show parameter name

 

NAME                                  TYPE                    VALUE

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

db_file_name_convert                  string

db_name                               string                  doudou

db_unique_name                        string                  doudou

global_names                          boolean                 FALSE

instance_name                         string                  doudou

lock_name_space                       string

log_file_name_convert                 string

service_names                         string                  doudou

Resetlogs 開啟資料庫

Alter database open resetlogs

 

5

使用新的控制檔案SQL建立新的控制檔案

CREATE CONTROLFILE SET DATABASE "DOUDOU" RESETLOGS ARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 '/opt/oracle/oradata/test/redo01.log'   SIZE 50M,

  GROUP 2 '/opt/oracle/oradata/test/redo02.log'   SIZE 50M,

  GROUP 3 '/opt/oracle/oradata/test/redo03.log'   SIZE 50M

DATAFILE

  '/opt/oracle/oradata/test/system01.dbf',

  '/opt/oracle/oradata/test/undotbs1.dbf',

  '/opt/oracle/oradata/test/sysaux01.dbf',

  '/opt/oracle/oradata/test/users01.dbf',

  '/opt/oracle/oradata/test/perfstat.dbf',

  '/opt/oracle/oradata/test/doudou.dbf',

  '/opt/oracle/oradata/test/doudou_index.dbf',

  '/opt/oracle/oradata/test/maclean.dbf',

  '/opt/oracle/oradata/test/test.dbf',

  '/opt/oracle/oradata/test/gaokao.dbf'

CHARACTER SET AL32UTF8

;

執行成功後,資料庫為mount狀態

 

6

新增臨時檔案

col tablespace_name for a30

SQL> select   TABLESPACE_NAME,SEGMENT_SPACE_MANAGEMENT,CONTENTS from dba_tablespaces where contents='TEMPORARY';

TABLESPACE_NAME                 SEGMENT_SPAC CONTENTS

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

TEMP                            MANUAL        TEMPORARY

TEMP01                          MANUAL        TEMPORARY

DOUDOU_TEMP                     MANUAL        TEMPORARY

alter tablespace temp add tempfile '/opt/oracle/oradata/test/temp.dbf' reuse;

alter tablespace temp01 add tempfile '/opt/oracle/oradata/test/temp01.dbf' reuse;

alter tablespace doudou_temp add tempfile '/opt/oracle/oradata/test/doudou_temp.dbf' reuse;

 

7

修改使用者口令檔案,tnsnames.ora

[ora@dg-pp dbs]$ cp orapwtest orapwdoudou

vi   tnsnames.ora

doudou =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = dg-pp)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

     (SERVICE_NAME = doudou)

    )

  )

 

使用sqlplus 登陸資料庫驗證

[ora@dg-pp admin]$ sqlplus sys/oracle@doudou as sysdba (登陸成功)

SQL> col name for a30

SQL> col type for a10

SQL> col value for a20

SQL> show parameter name

 

NAME                                  TYPE        VALUE

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

db_file_name_convert                  string

db_name                               string      doudou

db_unique_name                        string      doudou

global_names                          boolean     FALSE

instance_name                         string      doudou

lock_name_space                       string

log_file_name_convert                 string

service_names                         string      doudou

 

 

附表:

Db_name                                  ----( 只能pfile修改,並重啟生效)

Db_domain                                ---- (pfile,spfile都可修改,並重啟生效)

Db_unique_name                           ----( 只能pfile修改,並重啟生效)

Service_names                            ---- (pfile,spfile都可修改,立刻生效,無需重啟)

 

Oracle修改(db_name,db_unique_name, servie_names,db_domain)方法 Oracle修改(db_name,db_unique_name, servie_names,db_domain)方法 1 、db_domain

修改db_domain (spfile啟動)

alter system set db_domain='com' scope=spfile;

    重啟資料庫生效

    shutdown immediate

    startup

 2 、db_unique_name

修改db_unique_name(spfile啟動)

    create pfile from spfile; (建立pfile檔案修改db_unique_name)

    vi pfile

    startup pfile='/opt/oracle/product/10.2.0/db_1/dbs/initdoudou.ora';

3 、service_names(spfile啟動)

    alter system set service_names=' ;

   

總結:service_names未設定情況下,service_names=db_unique_name.db_domain;若是設定了service_names時,已service_names為準

 

操作方法記錄

1 、db_domain 操作方法記錄

 

Oracle修改(db_name,db_unique_name, servie_names,db_domain)方法 Oracle修改(db_name,db_unique_name, servie_names,db_domain)方法 Oracle修改(db_name,db_unique_name, servie_names,db_domain)方法 col name for a30

col type for a10

col value for a20

show parameter db_domain

NAME                                  TYPE        VALUE

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

db_domain                             string

show parameter name

NAME                                  TYPE        VALUE

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

db_file_name_convert                  string

db_name                               string      doudou

db_unique_name                        string      doudou

global_names                          boolean     FALSE

instance_name                         string      doudou

lock_name_space                       string

log_file_name_convert                 string

service_names                         string      doudou

Oracle修改(db_name,db_unique_name, servie_names,db_domain)方法 alter system set db_domain='com' scope=spfile;

SQL> show parameter db_domain

NAME                                  TYPE        VALUE

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

db_domain                             string

SQL> show parameter name   

NAME                                  TYPE        VALUE

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

db_file_name_convert                  string

db_name                               string      doudou

db_unique_name                        string      doudou

global_names                          boolean     FALSE

instance_name                         string      doudou

lock_name_space                       string

log_file_name_convert                 string

service_names                         string      doudou

雖然我們修改成功了,但是沒有立刻生效,我們重啟資料庫

shutdown immediate

startup

SQL> show parameter db_domain

NAME                                  TYPE        VALUE

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

db_domain                             string      com

SQL> show parameter name

NAME                                  TYPE        VALUE

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

db_file_name_convert                  string

db_name                               string      doudou

db_unique_name                        string      doudou

global_names                          boolean     FALSE

instance_name                         string      doudou

lock_name_space                       string

log_file_name_convert                 string

service_names                         string      doudou.com

修改db_domain重啟資料庫後生效!還有一點需注意service_names自動變為doudou.com也就是說當你設定db_domain時, Oracle修改(db_name,db_unique_name, servie_names,db_domain)方法 Oracle修改(db_name,db_unique_name, servie_names,db_domain)方法 在不設定service_names的情況下, service_names =db_unique.db_domain (為什麼會有這個公式,下面會講)

 

2 、db_unique_name 操作方法記錄

SQL> show parameter name

NAME                                  TYPE        VALUE

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

db_file_name_convert                  string

db_name                               string      doudou

db_unique_name                        string      doudou

global_names                          boolean     FALSE

instance_name                         string      doudou

lock_name_space                       string

log_file_name_convert                 string

service_names                         string      doudou.com

SQL> create pfile from spfile;

vi pfile

新增

db_unique_name= xiaoyu

開啟資料庫

SQL> startup pfile='/opt/oracle/product/10.2.0/db_1/dbs/initdoudou.ora';

SQL> show parameter db_domain

NAME                                  TYPE        VALUE

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

db_domain                             string      com

SQL> show parameter name

NAME                                  TYPE        VALUE

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

db_file_name_convert                  string

db_name                               string      doudou

db_unique_name                        string      xiaoyu

global_names                          boolean     FALSE

instance_name                         string      doudou

lock_name_space                       string

log_file_name_convert                 string

service_names                         string      xiaoyu.com

在不設定service_names的情況下, service_names=db_unique_name.db_domain公式了

 

3 、service_names操作方法記錄

SQL> show parameter db_domain

NAME                                  TYPE        VALUE

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

db_domain                             string      com

SQL> show parameter name

NAME                                  TYPE        VALUE

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

db_file_name_convert                  string

db_name                               string      doudou

db_unique_name                        string      xiaoyu

global_names                          boolean     FALSE

instance_name                         string      doudou

lock_name_space                       string

log_file_name_convert                 string

service_names                         string      xiaoyu.com

SQL> alter system set service_names=' ;

SQL> show parameter db_domain

NAME                                  TYPE        VALUE

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

db_domain                             string      com

SQL> show parameter name

NAME                                  TYPE        VALUE

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

db_file_name_convert                  string

db_name                               string      doudou

db_unique_name                        string      xiaoyu

global_names                          boolean     FALSE

instance_name                         string      doudou

lock_name_space                       string

log_file_name_convert                 string

service_names                         string     

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

相關文章