當前位置：首頁 > 编程资源 > 编程问答 >内容正文

编程问答

oracle 11查询sid,oracle 11g 更改sid和dbname

發布時間：2025/4/16 编程问答 26 豆豆

生活随笔收集整理的這篇文章主要介紹了 oracle 11查询sid,oracle 11g 更改sid和dbname 小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

分兩個階段描述，第一階段改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，sid_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 2013Copyright (c) 1982, 2009, Oracle.??All rights reserved.ERROR:ORA-09925: Unable to create audit trail fileLinux Error: 2: No such file or directoryAdditional information: 9925ORA-01075: you are currently logged onEnter user-name:ERROR:ORA-01017: invalid username/password; logon deniedEnter user-name:ERROR:ORA-01017: invalid username/password; logon deniedSP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus[oracle@mazh trace]$ ps -ef|grep smoroot? ?? ? 470? ???7??0 07:10 ?? ?? ???00:00:00 [kpsmoused]oracle? ? 5236? ???1??0 09:01 ?? ?? ???00:00:00 ora_smon_mzhoracle? ? 5263??5047??0 09:04 pts/2? ? 00:00:00 grep smo[oracle@mazh trace]$ kill -9 5236[oracle@mazh trace]$ ps -ef|grep smoroot? ?? ? 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 2013Copyright (c) 1982, 2009, Oracle.??All rights reserved.Connected to an idle instance.

SQL> @mzh1.sqlORA-09925: Unable to create audit trail fileLinux Error: 2: No such file or directoryAdditional information: 9925CREATE CONTROLFILE SET DATABASE "MZH" RESETLOGS??NOARCHIVELOG*ERROR at line 1:ORA-01012: not logged onProcess ID: 0Session ID: 0 Serial number: 0ALTER DATABASE OPEN RESETLOGS*ERROR at line 1:ORA-01012: not logged onProcess ID: 0Session ID: 0 Serial number: 0ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oradata/mzh/temp01.dbf'*ERROR at line 1:ORA-01012: not logged onProcess ID: 0Session ID: 0 Serial number: 0在網上找了各種方法都不行，后來發現是/u01/app/oracle/dbs/initmzh.ora里的參數竟然搗的鬼

以下什么原因導致的還不是很清楚，請各位高手給與指點下。[oracle@mazh trace]$ vi /u01/app/oracle/dbs/initmzh.oraorcl.__db_cache_size=83886080orcl.__java_pool_size=4194304orcl.__large_pool_size=4194304orcl.__oracle_base='/u01/app'#ORACLE_BASE set from environmentorcl.__pga_aggregate_target=79691776orcl.__sga_target=239075328orcl.__shared_io_pool_size=0orcl.__shared_pool_size=138412032orcl.__streams_pool_size=4194304mzh.__db_cache_size=83886080mzh.__java_pool_size=4194304mzh.__large_pool_size=4194304mzh.__oracle_base='/u01/app'#ORACLE_BASE set from environmentmzh.__pga_aggregate_target=79691776mzh.__sga_target=239075328mzh.__shared_io_pool_size=0mzh.__shared_pool_size=109051904mzh.__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 11查询sid,oracle 11g 更改sid和dbname的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇： php+读取配置里的值,ThinkPHP
下一篇： oracle 作业断开原因,解惑 |