Oracle 9i以上版本 修改DB INSTANCE/SID/SERVICE_NAME 方法

參考網頁

http://www.oracle-base.com/articles/9i/dbnewid.php

只要Oracle 9i以上版本 即可修改DB INSTANCE

oracle@oracle-lab $ env |grep SID

ORACLE_SID=orcl

oracle@oracle-lab $ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.8.0 - Production on Tue May 8 14:02:14 2012

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.8.0 - Production

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SQL> startup mount;

ORACLE instance started.

Total System Global Area  269554120 bytes

Fixed Size                   451016 bytes

Variable Size             201326592 bytes

Database Buffers           67108864 bytes

Redo Buffers                 667648 bytes

Database mounted.

SQL> exit

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.8.0 - Production

oracle@oracle-lab $  nid TARGET=sys/oracle@orcl DBNAME=test123

Proceeding with operation

Changing database ID from 1306390548 to 4113119021

Changing database name from ORCL to test123

    Control File /ora_test123/oradata/control01.ctl - modified

    Control File /ora_test123/oradata/control02.ctl - modified

    Control File /ora_test123/oradata/control03.ctl - modified

    Datafile /ora_test123/oradata/system01.dbf - dbid changed, wrote new name

    Datafile /ora_test123/oradata/undotbs01.dbf - dbid changed, wrote new name

    Datafile /ora_test123/oradata/cwmlite01.dbf - dbid changed, wrote new name

    Datafile /ora_test123/oradata/drsys01.dbf - dbid changed, wrote new name

    Datafile /ora_test123/oradata/example01.dbf - dbid changed, wrote new name

    Datafile /ora_test123/oradata/indx01.dbf - dbid changed, wrote new name

    Datafile /ora_test123/oradata/odm01.dbf - dbid changed, wrote new name

    Datafile /ora_test123/oradata/tools01.dbf - dbid changed, wrote new name

    Datafile /ora_test123/oradata/users01.dbf - dbid changed, wrote new name

    Datafile /ora_test123/oradata/xdb01.dbf - dbid changed, wrote new name

    Datafile /ora_test123/oradata/temp01.dbf - dbid changed, wrote new name

    Control File /ora_test123/oradata/control01.ctl - dbid changed, wrote new name

    Control File /ora_test123/oradata/control02.ctl - dbid changed, wrote new name

    Control File /ora_test123/oradata/control03.ctl - dbid changed, wrote new name

Database name changed to test123.

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

Database ID for database test123 changed to 4113119021.

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

Shut down database and open with RESETLOGS option.

Succesfully changed database name and ID.

DBNEWID - Completed succesfully.

oracle@oracle-lab $

orapwd file=/oracle/db920/dbs/orapwtest123 password=wgsys entries=10

SQL> select status , instance_name from v$instance;

STATUS       INSTANCE_NAME

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

MOUNTED      orcl

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL>

ORACLE_SID=test123; export ORACLE_SID

Alter the listener.ora and tnsnames.ora and pfile parameter setting to match the new database name and restart the listener.

   lsnrctl reload

Open the database with RESETLOGS.

   STARTUP MOUNT pfile='/tmp/pfileorcl.ora';

   ALTER DATABASE OPEN RESETLOGS;

SQL> select instance_name , status from v$instance;

INSTANCE_NAME    STATUS

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

test123            OPEN

SQL> show parameter name

NAME                                 TYPE        VALUE

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

db_file_name_convert                 string

db_name                              string      test123

global_names                         boolean     FALSE

instance_name                        string      test123

lock_name_space                      string

log_file_name_convert                string

oracle_trace_collection_name         string

oracle_trace_facility_name           string      oracled

plsql_native_make_file_name          string

service_names                        string      test123