星期二, 5月 08, 2012

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

參考網頁
只要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



沒有留言:

LinkWithin-相關文件

Related Posts Plugin for WordPress, Blogger...