Linux環境下oracle數庫庫改名，例項改名

Linux環境下oracle數庫庫改名，例項改名

轉自：http://seteor.itpub.net/post/42575/502279

SQL> select instance_name from v$instance; --檢視當前例項名

INSTANCE_NAME
----------------
test

SQL> select name from v$database; --檢視當前dbname

NAME
---------
TEST

SQL> shutdown immediate --關閉資料庫

Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 205520896 bytes
Fixed Size 1218532 bytes
Variable Size 67110940 bytes
Database Buffers 134217728 bytes
Redo Buffers 2973696 bytes
Database mounted.

[@more@]SQL> host nid target=sys/test123 dbname=test2; --更改dbname

DBNEWID: Release 10.2.0.1.0 - Production on Wed Jul 21 16:31:20 2010

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

Connected to database TESTORA (DBID=547002923)

Connected to server version 10.2.0

Control Files in database:
/u01/oradata/oratest/control01.ctl
/u01/oradata/oratest/control02.ctl
/u01/oradata/oratest/control03.ctl

Change database ID and database name TESTORA to TEST? (Y/[N]) => y --程式詢問

Proceeding with operation
Changing database ID from 547002923 to 2023088600
Changing database name from TESTORA to TEST2
Control File /u01/oradata/oratest/control01.ctl - modified
Control File /u01/oradata/oratest/control02.ctl - modified
Control File /u01/oradata/oratest/control03.ctl - modified
Datafile /u01/oradata/oratest/system01.dbf - dbid changed, wrote new name
Datafile /u01/oradata/oratest/undotbs01.dbf - dbid changed, wrote new name
Datafile /u01/oradata/oratest/sysaux01.dbf - dbid changed, wrote new name
Datafile /u01/oradata/oratest/users01.dbf - dbid changed, wrote new name
Datafile /u01/oradata/oratest/test.dbf - dbid changed, wrote new name
Datafile /u01/oradata/oratest/temp02.dbf - dbid changed, wrote new name
Control File /u01/oradata/oratest/control01.ctl - dbid changed, wrote new name
Control File /u01/oradata/oratest/control02.ctl - dbid changed, wrote new name
Control File /u01/oradata/oratest/control03.ctl - dbid changed, wrote new name
Instance shut down

Database name changed to TEST2.
Modify parameter file and generate a new password file before restarting.
Database ID for database TEST changed to 2023088600.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.

SQL> alter system set db_name=test2 scope=spfile; --因為dbname已更改，連線丟失，需重新連線

alter system set db_name=test2 scope=spfile
*
ERROR at line 1:
ORA-03135: connection lost contact --因為dbname已更改，連線丟失，需重新連線

SQL> startup mount
ORACLE instance started.

Total System Global Area 205520896 bytes
Fixed Size 1218532 bytes
Variable Size 67110940 bytes
Database Buffers 134217728 bytes
Redo Buffers 2973696 bytes
ORA-01103: database name 'TEST2' in control file is not 'TEST' --庫名已改為test2，但當前啟動的資料庫的spfile還是'TEST'

SQL> alter system set db_name=test2 scope=spfile; --spfile中改為test2

System altered.
SQL> shutdown immediate; --更改db_name後關閉資料庫

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup mount --重新mount，沒有再報ORA-01103錯誤ORACLE instance started.

Total System Global Area 205520896 bytes
Fixed Size 1218532 bytes
Variable Size 67110940 bytes
Database Buffers 134217728 bytes
Redo Buffers 2973696 bytes
Database mounted.

SQL> alter database open resetlogs; --resetlogs

Database altered.

SQL> select dbid,name from v$database; --驗證新的dbid和name

DBID NAME
---------- ---------
808938177 TEST2

SQL> select instance_name from v$instance; --例項名仍然未變化（如果不需要改例項名也可以,如果不改sid,只需更改）

INSTANCE_NAME
----------------
test

----------------------------------------------------------------------------改sid----------------------------------------------------------------
SQL> host orapwd file=/u01/oracle/product/10.2.0/db_1/dbs/orapwtest2 password=123 entries=10; --生成新SID對應的密碼檔案

SQL> create pfile='/u01/oracle/product/10.2.0/db_1/dbs/inittest2.ora' from spfile; --生成新SID對應的pfile檔案

File created.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

另開一終端修改.bash_profile檔案中的ORACLE_SID

[oracle@wwnode2 ~]$ source .bash_profile; --修改後立刻生效

[oracle@wwnode2 ~]$ echo $ORACLE_SID --驗證是否更改成功test2
[oracle@wwnode2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jul 21 16:51:28 2010

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 205520896 bytes
Fixed Size 1218532 bytes
Variable Size 62916636 bytes
Database Buffers 138412032 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.

SQL> select instance_name from v$instance; --驗證SID

INSTANCE_NAME
----------------
test2

SQL> select name from v$database; --驗證dbname

NAME
---------
TEST2