修改DB Name

修改DB Name

1.創建pfile

後面需要修改pfile中的db_name,所以之前最好有最新的pfile在

———————————————

SQL> create pfile from spfile;

File created.

———————————————

在對DB修改之前我習慣備份老的pfile 和spfile。

———————————————

[oracle@wmst01 dbs]$ cp initNHWMSDB.ora initNHWMSDB.ora.0718

[oracle@wmst01 dbs]$ cp spfileNHWMSDB.ora spfileNHWMSDB.ora.0718

[oracle@wmst01 dbs]$ ll

total 92

drwxr-xr-x    2 oracle   dba         20480 Dec 25  2006 arch

-rw-rw—-    1 oracle   dba          1544 Nov 21  2006 hc_NHWMSDB.dat

-rw-r—–    1 oracle   dba          1544 Dec 21  2005 hc_WMST01.dat

-rw-r–r–    1 oracle   dba         12920 Dec 17  2005 initdw.ora

-rw-r–r–    1 oracle   dba           804 Jul 18 13:39 initNHWMSDB.ora

-rw-r–r–    1 oracle   dba           804 Jul 18 13:39 initNHWMSDB.ora.0718

-rw-r–r–    1 oracle   dba          8385 Dec 17  2005 init.ora

-rw-rw—-    1 oracle   dba            24 Dec  7  2006 lkNHWMSDB

-rw-r—–    1 oracle   dba            24 Dec 25  2006 lkWMST01

-rwSr—–    1 oracle   dba          1536 Nov 28  2006 orapwNHWMSDB

-rwSr—–    1 oracle   dba          1536 Nov 29  2006 orapwWMST01

-rw-r—–    1 oracle   dba          2560 Dec  7  2006 spfileNHWMSDB.ora

-rw-r—–    1 oracle   dba          2560 Jul 18 13:40 spfileNHWMSDB.ora.0718

———————————————

2.登陆数据库把数据库变成mount状态

如果原來機器是開啓的,用shutdown immediate

———————————————

[oracle@wmst01 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 10.1.0.4.0 – Production on Wed Jul 18 13:43:49 2007

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

Connected to an idle instance.

SQL> startup mount

orACLE instance started.

Total System Global Area  939524096 bytes

Fixed Size                   781984 bytes

Variable Size             237244768 bytes

Database Buffers          700448768 bytes

Redo Buffers                1048576 bytes

Database mounted.

SQL> exit

Disconnected from oracle Database 10g Enterprise Edition Release 10.1.0.4.0 – Production

With the Partitioning, OLAP and Data Mining options

3. NID命令修改DB Name

[oracle@wmst01 dbs]$ nid target=sys/manager@NHWMSDB dbname=wmsdev

DBNEWID: Release 10.1.0.4.0 – Production

Copyright (c) 2001, 2004, oracle.  All rights reserved.

Connected to database NHWMSDB (DBID=4077621663)

Connected to server version 10.1.0

Control Files in database:

    /WMS_TEST/nhwmsdb/system/control01.ctl

    /WMS_TEST/nhwmsdb/system/control02.ctl

    /WMS_TEST/nhwmsdb/system/control03.ctl

Change database ID and database name NHWMSDB to WMSDEV? (Y/[N]) => Y

Proceeding with operation

Changing database ID from 4077621663 to 3212003006

Changing database name from NHWMSDB to WMSDEV

    Control File /WMS_TEST/nhwmsdb/system/control01.ctl – modified

    Control File /WMS_TEST/nhwmsdb/system/control02.ctl – modified

    Control File /WMS_TEST/nhwmsdb/system/control03.ctl – modified

    Datafile /WMS_TEST/nhwmsdb/system01.dbf – dbid changed, wrote new name

    Datafile /WMS_TEST/nhwmsdb/undotbs01.dbf – dbid changed, wrote new name

    Datafile /WMS_TEST/nhwmsdb/sysaux01.dbf – dbid changed, wrote new name

    Datafile /WMS_TEST/nhwmsdb/undotbs02.dbf – dbid changed, wrote new name

    Datafile /WMS_TEST/nhwmsdb/users01.dbf – dbid changed, wrote new name

    Datafile /WMS_TEST/nhwmsdb/data/ASRS_01.dbf – dbid changed, wrote new name

    Datafile /WMS_TEST/nhwmsdb/data/ASRS_IDX_01.dbf – dbid changed, wrote new name

    Datafile /WMS_TEST/nhwmsdb/data/CYMS_01.dbf – dbid changed, wrote new name

    Datafile /WMS_TEST/nhwmsdb/data/CYMS_IDX_01.dbf – dbid changed, wrote new name

    Datafile /WMS_TEST/nhwmsdb/data/DTCT_01.dbf – dbid changed, wrote new name

    Datafile /WMS_TEST/nhwmsdb/data/DTCT_IDX_01.dbf – dbid changed, wrote new name

    Datafile /WMS_TEST/nhwmsdb/data/LARGE_01.dbf – dbid changed, wrote new name

    Datafile /WMS_TEST/nhwmsdb/data/LARGE_IDX_01.dbf – dbid changed, wrote new name

    Datafile /WMS_TEST/nhwmsdb/data/CYMS_02.dbf – dbid changed, wrote new name

    Datafile /WMS_TEST/nhwmsdb/data/DTCT_02.dbf – dbid changed, wrote new name

    Datafile /WMS_TEST/nhwmsdb/data/CYMS_IDX_02.dbf – dbid changed, wrote new name

    Datafile /WMS_TEST/nhwmsdb/data/DTCT_IDX_02.dbf – dbid changed, wrote new name

    Datafile /WMS_TEST/nhwmsdb/temp01.dbf – dbid changed, wrote new name

    Control File /WMS_TEST/nhwmsdb/system/control01.ctl – dbid changed, wrote new name

    Control File /WMS_TEST/nhwmsdb/system/control02.ctl – dbid changed, wrote new name

    Control File /WMS_TEST/nhwmsdb/system/control03.ctl – dbid changed, wrote new name

    Instance shut down

Database name changed to WMSDEV.

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

Database ID for database WMSDEV changed to 3212003006.

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

Database has been shutdown, open database with RESETLOGS option.

Succesfully changed database name and ID.

DBNEWID – Completed succesfully.

命令完成之後shutdown DB

[oracle@wmst01 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 10.1.0.4.0 – Production on Wed Jul 18 13:45:23 2007

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

Connected to an idle instance.

SQL> shutdown immediate

orA-01034: orACLE not available

orA-27101: shared memory realm does not exist

Linux Error: 2: No such file or directory

SQL> exit

Disconnected

4. 修改pfile,創建spfile

[oracle@wmst01 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 10.1.0.4.0 – Production on Wed Jul 18 13:50:28 2007

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

Connected to an idle instance.

SQL> create spfile=’/oracle/product/10.1/dbs/spfilewmsdev.ora’ from pfile=’/oracle/product/10.1/dbs/initNHWMSDB.ora’;

File created.

SQL> exit

Disconnected

5. 創建口令文件

$orapwd file=/oracle/product/10.1/dbs/orapwwmsdev password=oracle entries=5

6. 修改tnsname.ora、 listener.ora

修改$ORACLE_HOME/network/admin/下的tnsname.ora和listener.ora,將其中的service_name or sid

7. 開啓數據庫

[oracle@wmst01 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 10.1.0.4.0 – Production on Wed Jul 18 13:55:25 2007

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

Connected to an idle instance.

SQL> startup

orACLE instance started.

Total System Global Area  939524096 bytes

Fixed Size                   781984 bytes

Variable Size             237244768 bytes

Database Buffers          700448768 bytes

Redo Buffers                1048576 bytes

Database mounted.

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

SQL> alter database open resetlogs;

Database altered.

SQL> sho parameter name

NAME                                 TYPE        VALUE

———————————— ———– ——————————

db_file_name_convert                 string

db_name                              string      wmsdev

db_unique_name                       string      wmsdev

global_names                         boolean     FALSE

instance_name                        string      wmsdev

lock_name_space                      string

log_file_name_convert                string

service_names                        string      wmsdev

sp_name                              string      wmsdev

SQL>

SQL> select instance_name from v$instance;

INSTANCE_NAME

—————-

wmsdev

SQL>

  

至此,修改全部完成,且检查成功!!!  HOHO~~~

发表评论?

0 条评论。

发表评论


注意 - 你可以用以下 HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>