分兩個階段描述，第一階段改sid，第二階段改dbname

下面描述詳細步驟

首先要更改sid

1、登入資料庫檢視先前的sid，總共三步

[oracle@localhost ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Fri Aug 9 05:53:08 2013

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 539848704 bytes

Fixed Size 1337748 bytes

Variable Size 360711788 bytes

Database Buffers 171966464 bytes

Redo Buffers 5832704 bytes

Database mounted.

Database opened.

SQL> select instance from v$thread ;

INSTANCE

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

orcl

2、關閉資料庫

[sql] view plaincopyprint?

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

3、編輯/etc/oratab檔案，把所有orcl換成mzh

[oracle@localhost ~]$ vim /etc/oratab

4、更改oracle使用者的 .bash_profile檔案

[oracle@localhost ~]$ vim .bash_profile

5、使改好的.bash_profile檔案生效

:%s/oral/mzh/g

[oracle@localhost ~]$ . .bash_profile

7、檢視系統環境變數

[oracle@localhost ~]$ env |grep ORACLE

ORACLE_SID=mzh

ORACLE_BASE=/u01/app

ORACLE_HOME=/u01/app/oracle

效果一生成。

8、進入$ORACLE_HOME/dbs檢視目錄，看那些有orcl

[oracle@localhost ~]$ cd $ORACLE_HOME/dbs

[oracle@localhost dbs]$ ll

total 64

-rw-rw---- 1 oracle oinstall 1544 Jun 29 00:35 hc_DBUA0.dat

-rw-rw---- 1 oracle oinstall 1544 Aug 9 05:56 hc_mzh.dat

-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora

-rw-r----- 1 oracle oinstall 24 Aug 8 06:46 lkHXY

-rw-r----- 1 oracle oinstall 24 Jun 29 00:47 lkMZH

-rw-r----- 1 oracle oinstall 2048 Aug 9 06:09 orapwmzh

drwx------ 2 oracle oinstall 4096 Jun 28 22:50 peshm_DBUA0_0

drwx------ 2 oracle oinstall 4096 Aug 8 06:45 peshm_hxy_0

drwx------ 2 oracle oinstall 4096 Jun 28 23:02 peshm_mzh_0

-rw-r----- 1 oracle oinstall 3584 Aug 9 05:53 spfilemzh.ora

9、更改檔名orcr>> mzh，ORCL>>MZH，命令如下：

[oracle@localhost dbs]$ mv hc_orcl.dat hc_mzh.dat

[oracle@localhost dbs]$ mv orapworcl orapwmzh

[oracle@localhost dbs]$ mv lkORCL lkMZH

[oracle@localhost dbs]$ mv peshm_orcl_0/ peshm_mzh_0/

[oracle@localhost dbs]$ mv spfileorcl.ora spfilemzh.ora

10、重行生成密碼檔案，並檢視

注意最好把原來的刪除掉

[oracle@localhost dbs]$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=sys entries=5 force=y

[oracle@localhost dbs]$ ls -lrt orap*

-rw-r----- 1 oracle oinstall 2048 Aug 9 06:09 orapwmzh

11、登入資料庫，並檢視例項名字，結果表明sid已由orcl變成mzh了

[oracle@localhost dbs]$ sqlplus "/as sysdba"

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 539848704 bytes

Fixed Size 1337748 bytes

Variable Size 327157356 bytes

Database Buffers 205520896 bytes

Redo Buffers 5832704 bytes

Database mounted.

Database opened.

SQL> select instance from v$thread ;

INSTANCE

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

mzh

接著開始第二部分，更改資料庫名dbname

2.1備份控制檔案

SQL> alter database backup controlfile to trace resetlogs;

Database altered.

2.2關閉並退出資料庫

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

2.3 orcale 11.2g的控制檔案的備份目錄為

/u01/app/oracle/diag/rdbms/mzh/mzh/trace

[oracle@localhost trace]$ ls -lrt

[oracle@mazh dbs]$ ls /u01/app/diag/rdbms/mzh/mzh/trace -lrth

total 116K

-rw-r----- 1 oracle oinstall 259 Aug 8 06:45 mzh_ora_4808.trm

-rw-r----- 1 oracle oinstall 15K Aug 8 06:45 mzh_ora_4808.trc

-rw-r----- 1 oracle oinstall 57 Aug 8 06:46 mzh_mman_4845.trm

-rw-r----- 1 oracle oinstall 796 Aug 8 06:46 mzh_mman_4845.trc

-rw-r----- 1 oracle oinstall 57 Aug 8 06:46 mzh_dbrm_4839.trm

-rw-r----- 1 oracle oinstall 839 Aug 8 06:46 mzh_dbrm_4839.trc

-rw-r----- 1 oracle oinstall 57 Aug 8 06:46 mzh_mmon_4857.trm

-rw-r----- 1 oracle oinstall 833 Aug 8 06:46 mzh_mmon_4857.trc

-rw-r----- 1 oracle oinstall 67 Aug 8 06:46 mzh_j004_4909.trm

-rw-r----- 1 oracle oinstall 912 Aug 8 06:46 mzh_j004_4909.trc

-rw-r----- 1 oracle oinstall 112 Aug 8 06:47 mzh_j000_4900.trm

-rw-r----- 1 oracle oinstall 2.8K Aug 8 06:47 mzh_j000_4900.trc

-rw-r----- 1 oracle oinstall 67 Aug 8 06:58 mzh_ckpt_4851.trm

-rw-r----- 1 oracle oinstall 905 Aug 8 06:58 mzh_ckpt_4851.trc

-rw-r----- 1 oracle oinstall 78 Aug 8 06:58 mzh_j000_5233.trm

-rw-r----- 1 oracle oinstall 1.8K Aug 8 06:58 mzh_j000_5233.trc

-rw-r----- 1 oracle oinstall 57 Aug 8 08:04 mzh_j000_7066.trm

-rw-r----- 1 oracle oinstall 1.1K Aug 8 08:04 mzh_j000_7066.trc

-rw-r----- 1 oracle oinstall 139 Aug 8 08:43 mzh_vktm_4831.trm

-rw-r----- 1 oracle oinstall 1.9K Aug 8 08:43 mzh_vktm_4831.trc

-rw-r----- 1 oracle oinstall 407 Aug 8 08:43 mzh_ora_4871.trm

-rw-r----- 1 oracle oinstall 6.3K Aug 8 08:43 mzh_ora_4871.trc

-rw-r----- 1 oracle oinstall 6.2K Aug 8 08:43 alert_mzh.log

[oracle@localhost trace]$ vim alert_mzh.log

可以在 alter_mzh.log裡找到contolfile的備份trc，<em>sid_</em>ora_nnnn.trc 最新的一個就是。

alter_mzh.log裡面有這樣一行字樣，告訴你哪個是控制備份檔案

Backup controlfile written to trace file /u01/app/diag/rdbms/orcl/mzh/trace/mzh_ora_4871.trc

2.5複製一份

[oracle@localhost trace]$ cp mzh_ora_4871.trc mzh.sql

2.6編輯 mzh.sql,也就是mzh_ora_4871.trc的複製品。

1）查詢STARTUP NOMOUNT語句，將這一行上面的所有行都刪除

2）查詢所有以--開始的行，把這些行刪除

3）查詢所有的orcl修改為mzh，所有的ORCL修改為mzh

4）找到CREATE CONTROLFILE REUSE DATABASE...語句，將其中的REUSE修改為SET

5）找到RECOVER DATABASE USING BACKUP CONTROLFILE語句，將其用雙橫線（--）註釋掉

結果如下：

[oracle@mazh dbs]$ cat /u01/app/diag/rdbms/mzh/mzh/trace/mzh.sql

STARTUP NOMOUNT

CREATE CONTROLFILE SET DATABASE "MZH" RESETLOGS NOARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/u01/app/oradata/mzh/redo01.log' SIZE 50M BLOCKSIZE 512,

GROUP 2 '/u01/app/oradata/mzh/redo02.log' SIZE 50M BLOCKSIZE 512,

GROUP 3 '/u01/app/oradata/mzh/redo03.log' SIZE 50M BLOCKSIZE 512

DATAFILE

'/u01/app/oradata/mzh/system01.dbf',

'/u01/app/oradata/mzh/sysaux01.dbf',

'/u01/app/oradata/mzh/undotbs01.dbf',

'/u01/app/oradata/mzh/users01.dbf',

'/u01/app/oradata/mzh/example01.dbf'

CHARACTER SET AL32UTF8

;

--RECOVER DATABASE USING BACKUP CONTROLFILE

ALTER DATABASE OPEN RESETLOGS;

ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oradata/mzh/temp01.dbf'

SIZE 30408704 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

2.7生成配置檔案

[oracle@localhost trace]$ sqlplus "/as sysdba"

Connected to an idle instance.

SQL> create pfile='?/dbs/initmzh.ora' from spfile;

File created.

SQL> exit

Disconnected

2.8目錄更改，這裡和oracle 10g不一樣，要注意。

[sql] view plaincopyprint?

[oracle@localhost ~]$ cd /u01/app/oracle/

[oracle@localhost oracle]$ ls

admin cfgtoollogs checkpoints diag flash_recovery_area oradata product

[oracle@localhost oracle]$ cd flash_recovery_area/

[oracle@localhost flash_recovery_area]$ ls

orcl ORCL

[oracle@localhost flash_recovery_area]$ mv orcl/ mzh/

[oracle@localhost flash_recovery_area]$ mv ORCL/ mzh/

[oracle@localhost flash_recovery_area]$ cd ..

[oracle@localhost oracle]$ ls

admin cfgtoollogs checkpoints diag flash_recovery_area oradata product

[oracle@localhost oracle]$ cd oradata/

[oracle@localhost oradata]$ ls

orcl

[oracle@localhost oradata]$ mv orcl/ mzh/

[oracle@localhost oradata]$ ls

mzh

[oracle@localhost oradata]$ cd ..

[oracle@localhost oracle]$ ls

admin cfgtoollogs checkpoints diag flash_recovery_area oradata product

[oracle@localhost oracle]$ cd diag/

[oracle@localhost diag]$ ls

rdbms tnslsnr

[oracle@localhost diag]$ cd rdbms/

[oracle@localhost rdbms]$ ls

orcl

[oracle@localhost rdbms]$ mv orcl/ mzh/

[oracle@localhost rdbms]$ ls

mzh

[oracle@localhost rdbms]$ cd mzh

[oracle@localhost mzh]$ ls

i_1.mif mzh

[oracle@localhost mzh]$ cd ..

[oracle@localhost rdbms]$ cd ..

[oracle@localhost diag]$ ls

rdbms tnslsnr

[oracle@localhost diag]$ cd ..

[oracle@localhost oracle]$ cd admin/

[oracle@localhost admin]$ ls

orcl

[oracle@localhost admin]$ cd orcl/

[oracle@localhost orcl]$ ls

adump dpdump pfile

[oracle@localhost orcl]$ cd ..

[oracle@localhost admin]$ mv orcl/ mzh/

[oracle@localhost admin]$ ls

mzh

[oracle@localhost admin]$ sqlplus / as sysdba

Connected to an idle instance.

SQL>

2.9刪除的控制檔案。

[oracle@localhost oradata]$ cd mzh/

[oracle@localhost mzh]$ ls

control01.ctl redo01.log redo03.log system01.dbf undotbs01.dbf

example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf

[oracle@localhost mzh]$ mv control01.ctl control01.ctl.aaa

[oracle@localhost mzh]$ ls

control01.ctl.aaa redo01.log redo03.log system01.dbf undotbs01.dbf

example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf

[oracle@localhost mzh]$ cd ..

[oracle@localhost oradata]$ ls

mzh

[oracle@localhost oradata]$ cd ..

[oracle@localhost oracle]$ ls

admin cfgtoollogs checkpoints diag flash_recovery_area oradata product

[oracle@localhost oracle]$ cd flash_recovery_area/

[oracle@localhost flash_recovery_area]$ ls

mzh mzh

[oracle@localhost flash_recovery_area]$ cd mzh/

[oracle@localhost mzh]$ ls

control02.ctl

[oracle@localhost mzh]$ mv control02.ctl control02.ctl.aaa

2.10登入oracle生成spfile檔案

[sql] view plaincopyprint?

[oracle@localhost mzh]$ sqlplus / as sysdba

Connected to an idle instance.

SQL> create spfile from pfile='?/dbs/initmzh.ora';

File created.

2.11呼叫前面步驟修改好的mzh.sql，目的是生成連結控制檔案等

SQL> @/u01/app/diag/rdbms/mzh/mzh/trace/mzh.sql

ORACLE instance started.

Total System Global Area 539848704 bytes

Fixed Size 1337748 bytes

Variable Size 327157356 bytes

Database Buffers 205520896 bytes

Redo Buffers 5832704 bytes

Control file created.

Database altered.

Tablespace altered.

2.12檢視結果

SQL> select open_mode from v$database;

OPEN_MODE

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

READ WRITE

SQL> show parameter name

NAME TYPE VALUE

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

db_file_name_convert string

db_name string mzh

db_unique_name string mzh

global_names boolean FALSE

instance_name string mzh

lock_name_space string

log_file_name_convert string

service_names string mzh.localdomain

SQL> select name from v$database;

NAME

---------

mzh

SQL>

有個小插曲在SQL> @/u01/app/diag/rdbms/mzh/mzh/trace/mzh.sql 時報錯如下：
[oracle@mazh trace]$  sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Fri Aug 9 09:03:56 2013

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

ERROR:
ORA-09925: Unable to create audit trail file
Linux Error: 2: No such file or directory
Additional information: 9925
ORA-01075: you are currently logged on

Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied

Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied

SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
[oracle@mazh trace]$ ps -ef|grep smo
root    470    7  0 07:10 ?       00:00:00 [kpsmoused]
oracle 5236    1  0 09:01 ?       00:00:00 ora_smon_mzh
oracle 5263  5047  0 09:04 pts/2 00:00:00 grep smo
[oracle@mazh trace]$ kill -9 5236
[oracle@mazh trace]$ ps -ef|grep smo
root    470    7  0 07:10 ?       00:00:00 [kpsmoused]
oracle 5265  5047  0 09:04 pts/2 00:00:00 grep smo
[oracle@mazh trace]$  sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Fri Aug 9 09:04:36 2013

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

Connected to an idle instance.

SQL> @mzh1.sql
ORA-09925: Unable to create audit trail file
Linux Error: 2: No such file or directory
Additional information: 9925
CREATE CONTROLFILE SET DATABASE "MZH" RESETLOGS NOARCHIVELOG
*
ERROR at line 1:
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0

ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0

ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oradata/mzh/temp01.dbf'
*
ERROR at line 1:
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0
在網上找了各種方法都不行，後來發現是
/u01/app/oracle/dbs/initmzh.ora裡的引數竟然搗的鬼

以下什麼原因導致的還不是很清楚，請各位高手給與指點下。
[oracle@mazh trace]$ vi /u01/app/oracle/dbs/initmzh.ora

orcl.__db_cache_size=83886080
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__oracle_base='/u01/app'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=79691776
orcl.__sga_target=239075328
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=138412032
orcl.__streams_pool_size=4194304
mzh.__db_cache_size=83886080
mzh.__java_pool_size=4194304
mzh.__large_pool_size=4194304
mzh.__oracle_base='/u01/app'#ORACLE_BASE set from environment
mzh.__pga_aggregate_target=79691776
mzh.__sga_target=239075328
mzh.__shared_io_pool_size=0
mzh.__shared_pool_size=109051904
mzh.__streams_pool_size=0
*.audit_file_dest='/u01/app/admin/mzh/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oradata/mzh/control01.ctl','/u01/app/flash_recovery_area/mzh/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='mzh'
*.db_recovery_file_dest='/u01/app/flash_recovery_area'
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest='/u01/app'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=mzhXDB)'
*.open_cursors=300
*.pga_aggregate_target=78643200
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=235929600
*.timed_statistics=TRUE
*.undo_tablespace='UNDOTBS1'

oracle 11g 更改sid和dbname

相關文章