完整修改oracle数据库的sid

[size=small]目的：将oracle数据库的SID由原来的egmas改名为pams，所有涉及sid的部分都会修改过来。

备注：下面的例子是在ASM自动存储管理下实现的，同样也适用于文件系统。

1、创建pfile
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA_DG/egmas/spfileegmas.ora

SQL> create pfile='/tmp/pfileegmas.ora' from spfile;
File created.
shutdown immediate

# vi /etc/oratab
pams:/dba/oracle/product/11.2.0.3.0:N # line added by Agent

cnsz03pl0032:egmas > pwd
/dba/oracle/product/11.2.0.3.0/dbs

2、修改一些重要文件名
cnsz03pl0032:egmas > ls -l *egmas*
-rw-rw---- 1 oracle asmadmin 1544 Nov 29 14:31 hc_egmas.dat
-rw-r----- 1 oracle oinstall 40 Nov 21 16:31 initegmas.ora
-rw-r----- 1 oracle oinstall 1536 Nov 21 16:31 orapwegmas
-rw-r----- 1 oracle oinstall 9748480 Nov 21 20:04 snapcf_egmas.f
cnsz03pl0032:egmas > mv hc_egmas.dat hc_pams.dat
cnsz03pl0032:egmas > mv orapwegmas orapwpams
cnsz03pl0032:egmas > cp initegmas.ora initpams.ora

3、新建pams的adump目录
cnsz03pl0032:pams > cd $ORACLE_BASE/admin
cnsz03pl0032:pams > ll
total 84
drwxr-xr-x 3 oracle oinstall 4096 Sep 23 18:13 bamsst
drwxr-x--- 5 oracle oinstall 4096 Jul 13 02:30 bmpst
drwxr-x--- 5 oracle oinstall 4096 Jul 8 23:32 bspdbst
drwxr-x--- 5 oracle oinstall 4096 Jul 8 22:11 cmspst
drwxr-x--- 8 oracle oinstall 4096 Nov 1 16:40 ecsst
drwxr-x--- 5 oracle oinstall 4096 Nov 21 16:27 egmas
drwxr-x--- 5 oracle oinstall 4096 Jul 8 22:19 hncmspst
drwxr-xr-x 5 oracle oinstall 4096 Aug 9 09:37 hnsssst
drwxr-x--- 8 oracle oinstall 4096 Nov 21 14:26 iams
drwxr-x--- 5 oracle oinstall 4096 Jul 13 02:45 iiscst
drwxr-xr-x 8 oracle oinstall 4096 Oct 21 16:04 lscmst
drwxr-x--- 8 oracle oinstall 4096 Aug 30 17:50 omptest
drwxr-xr-x 8 oracle oinstall 4096 Oct 21 15:20 pamsst
drwxr-x--- 5 oracle oinstall 4096 Nov 13 14:36 rstdbst
drwxr-xr-x 3 oracle oinstall 4096 Jul 3 23:25 sfecpdb
drwxr-x--- 8 oracle oinstall 4096 Jul 9 07:32 sfgpsst
drwxr-x--- 5 oracle oinstall 4096 Oct 12 14:14 sfmpst
drwxr-xr-x 7 oracle oinstall 4096 Sep 24 14:22 sfossst
drwxr-x--- 5 oracle oinstall 4096 Jul 6 21:40 wbepinterfacest
drwxr-x--- 5 oracle oinstall 4096 Jul 4 23:53 wbepst
drwxr-x--- 5 oracle oinstall 4096 Jul 2 04:12 wqsdbst
cnsz03pl0032:pams > mkdir pams
cnsz03pl0032:pams > cd pams
cnsz03pl0032:pams > mkdir adump

4、修改参数文件
---重点修改db_unique_name为新的sid，但此时千万不要修改db_name，其他的也都该成新的sid即可。
cnsz03pl0032:pams > vi /tmp/pfileegmas.ora
pams.__db_cache_size=3439329280
pams.__java_pool_size=16777216
pams.__large_pool_size=16777216
pams.__oracle_base='/dba/oracle'#ORACLE_BASE set from environment
pams.__pga_aggregate_target=7063207936
pams.__sga_target=4294967296
pams.__shared_io_pool_size=0
pams.__shared_pool_size=788529152
pams.__streams_pool_size=0
*.archive_lag_target=900
*.audit_file_dest='/dba/oracle/admin/egmas/adump'
*.audit_sys_operations=TRUE
*.audit_trail='DB_EXTENDED'
*.compatible='11.2.0.0.0'
*.control_files='+DATA_DG/pams/controlfile/current.612.832798423'#Restore Controlfile
*.db_block_size=8192
*.db_cache_size=5368709120
*.db_create_file_dest='+DATA_DG'
*.db_domain=''
*.db_files=2000
*.db_keep_cache_size=1073741824
*.db_name='egmas' ---注意这里千万不要修改
*.db_unique_name='pams' --这里指定新的sid
*.db_recovery_file_dest='+FRA_DG'
*.db_recovery_file_dest_size=21474836480
*.db_unique_name='pams'
*.diagnostic_dest='/dba/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=pamsXDB)'
*.java_pool_size=83886080
*.large_pool_size=314572800
*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST'
*.log_archive_format='arch_%t_%s_%r.arc'
*.log_archive_max_processes=5
*.memory_target=0
*.open_cursors=300
*.pga_aggregate_target=2147483648
*.processes=1000
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_limit=FALSE
*.resource_manager_plan=''
*.sessions=1105
*.sga_max_size=0
*.sga_target=0
*.shared_pool_size=1073741824
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'

5、以新的db_unique_name 启动数据库到mount状态
SQL> startup mount pfile='/tmp/pfileegmas.ora';
ORACLE instance started.

Total System Global Area 7933816832 bytes
Fixed Size 2243872 bytes
Variable Size 1476395744 bytes
Database Buffers 6442450944 bytes
Redo Buffers 12726272 bytes
Database mounted.

6、此时我们新建日志组的话就会自动创建到+data_dg/pams下面
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
+DATA_DG/egmas/onlinelog/group_4.594.832094113
+DATA_DG/egmas/onlinelog/group_5.595.832094149
+DATA_DG/egmas/onlinelog/group_6.596.832094161
+DATA_DG/egmas/onlinelog/group_7.597.832094171
+DATA_DG/egmas/onlinelog/group_8.598.832094179
+DATA_DG/egmas/onlinelog/group_9.599.832094189
6 rows selected.

SQL> alter database add logfile group 10 '+DATA_DG' size 100m;
SQL> alter database add logfile group 11 '+DATA_DG' size 100m;
SQL> alter database add logfile group 12 '+DATA_DG' size 100m;
随便先建几组，后面改就可以了。

[grid@cnsz03pl0032 ~]$ asmcmd
ASMCMD> cd DATA_DG
ASMCMD> cd PAMS
ASMCMD> cd ONLINELOG
ASMCMD> ls
group_10.606.832793705
group_11.594.832793715
group_12.613.832793723

就可以看到了，使用命令SQL> select member from v$logfile; 也可以查到，之前的我们并不用删除。

7、复制数据文件。
RMAN> copy datafile '+DATA_DG/EGMAS/DATAFILE/AUD_DATA.591.832094553' to '+DATA_DG';
copy datafile '+DATA_DG/EGMAS/DATAFILE/

ASMCMD> ls
AUD_DATA.615.832794687
AUD_INDEX.607.832794761
DBADATA.608.832794821
SYSAUX.609.832794857
SYSTEM.610.832794891
UNDOTBS1.611.832794927
USERS.614.832794595
一样的操作，拷贝过来，虽然会报错，control有关，不用理会，数据文件可以拷贝过来了。

8、数据文件和日志文件都有了，下面是控制文件：
SQL> alter database backup controlfile to trace; 这里的会话千万不要退出
Database altered.

SQL> select * from v$diag_info;
INST_ID NAME
---------- ----------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
1 Diag Enabled
TRUE

1 ADR Base
/dba/oracle

1 ADR Home
/dba/oracle/diag/rdbms/egmas/pams


INST_ID NAME
---------- ----------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
1 Diag Trace
/dba/oracle/diag/rdbms/egmas/pams/trace

1 Diag Alert
/dba/oracle/diag/rdbms/egmas/pams/alert

1 Diag Incident
/dba/oracle/diag/rdbms/egmas/pams/incident


INST_ID NAME
---------- ----------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
1 Diag Cdump
/dba/oracle/diag/rdbms/egmas/pams/cdump

1 Health Monitor
/dba/oracle/diag/rdbms/egmas/pams/hm

1 Default Trace File
/dba/oracle/diag/rdbms/egmas/pams/trace/pams_ora_29398.trc


INST_ID NAME

查看这个trace文件的中间内容：
more /dba/oracle/diag/rdbms/egmas/pams/trace/pams_ora_29398.trc

把创建控制文件的部分抠出来：
vi /tmp/controlpams.sql
CREATE CONTROLFILE REUSE DATABASE "EGMAS" NORESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 4 '+DATA_DG/egmas/onlinelog/group_4.594.832094113' SIZE 500M BLOCKSIZE 512,
GROUP 5 '+DATA_DG/egmas/onlinelog/group_5.595.832094149' SIZE 500M BLOCKSIZE 512,
GROUP 6 '+DATA_DG/egmas/onlinelog/group_6.596.832094161' SIZE 500M BLOCKSIZE 512,
GROUP 7 '+DATA_DG/egmas/onlinelog/group_7.597.832094171' SIZE 500M BLOCKSIZE 512,
GROUP 8 '+DATA_DG/egmas/onlinelog/group_8.598.832094179' SIZE 500M BLOCKSIZE 512,
GROUP 9 '+DATA_DG/egmas/onlinelog/group_9.599.832094189' SIZE 500M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'+DATA_DG/egmas/datafile/system.584.832091273',
'+DATA_DG/egmas/datafile/sysaux.585.832091273',
'+DATA_DG/egmas/datafile/undotbs1.586.832091273',
'+DATA_DG/egmas/datafile/users.587.832091273',
'+DATA_DG/egmas/datafile/aud_data.591.832094553',
'+DATA_DG/egmas/datafile/aud_index.590.832094565',
'+DATA_DG/egmas/datafile/dbadata.589.832094675'
CHARACTER SET AL32UTF8
;

将其中的内容修改，指向你自己建的目录：
CREATE CONTROLFILE REUSE DATABASE "PAMS" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 4 '+DATA_DG/pams/onlinelog/group_4.594.832094113' SIZE 500M BLOCKSIZE 512,
GROUP 5 '+DATA_DG/pams/onlinelog/group_5.595.832094149' SIZE 500M BLOCKSIZE 512,
GROUP 6 '+DATA_DG/pams/onlinelog/group_6.596.832094161' SIZE 500M BLOCKSIZE 512,
GROUP 7 '+DATA_DG/pams/onlinelog/group_7.597.832094171' SIZE 500M BLOCKSIZE 512,
GROUP 8 '+DATA_DG/pams/onlinelog/group_8.598.832094179' SIZE 500M BLOCKSIZE 512,
GROUP 9 '+DATA_DG/pams/onlinelog/group_9.599.832094189' SIZE 500M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'+DATA_DG/pams/datafile/system.584.832091273',
'+DATA_DG/pams/datafile/sysaux.585.832091273',
'+DATA_DG/pams/datafile/undotbs1.586.832091273',
'+DATA_DG/pams/datafile/users.587.832091273',
'+DATA_DG/pams/datafile/aud_data.591.832094553',
'+DATA_DG/pams/datafile/aud_index.590.832094565',
'+DATA_DG/pams/datafile/dbadata.589.832094675'
CHARACTER SET AL32UTF8
;

SQL>@/tmp/controlpams.sql --生成新的控制文件

SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
+DATA_DG/egmas/controlfile/current.588.832091379

但是，当我们执行这个命令的时候，发现，控制文件虽然新建成功，但是，却在原来的路径。如下：

RMAN> restore controlfile from '+DATA_DG/egmas/controlfile/current.588.832091379';

Starting restore at 2013-11-29 20:38:40
using channel ORA_DISK_1

channel ORA_DISK_1: copied control file copy
output file name=+DATA_DG/egmas/controlfile/current.588.832091379 --新的控制文件
Finished restore at 2013-11-29 20:38:41


那么我就需要指定路径！！但是control_files这个参数只能在spfile中修改，那么我们就新建spfile，然后以spfile启动。
SQL> create spfile='/tmp/spfilepams.ora' from pfile='/tmp/pfileegmas.ora';
File created.

SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.

SQL> startup nomount;
ORACLE instance started.
Total System Global Area 7933816832 bytes
Fixed Size 2243872 bytes
Variable Size 1476395744 bytes
Database Buffers 6442450944 bytes
Redo Buffers 12726272 bytes

SQL> show parameter spfile

NAME TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
spfile string
/dba/oracle/product/11.2.0.3.0
/dbs/spfilepams.ora
SQL> alter system set control_files='+DATA_DG/pams/controlfile/current.588.832091379' scope=spfile sid='*';
System altered.

SQL> commit;
Commit complete.

RMAN> restore controlfile from '+DATA_DG/egmas/controlfile/current.588.832091379';

Starting restore at 2013-11-29 20:53:42
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=+DATA_DG/pams/controlfile/current.612.832798423
Finished restore at 2013-11-29 20:53:44
这样我们就终于搞定了三大文件。

SQL> alter database mount;
Database altered.

SQL> alter database open;
Database altered.

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
+DATA_DG/pams/controlfile/current.612.832798423

SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
+DATA_DG/pams/datafile/dbadata.608.832794821
+DATA_DG/pams/datafile/aud_index.607.832794761
+DATA_DG/pams/datafile/aud_data.615.832794687
+DATA_DG/pams/datafile/users.614.832794595
+DATA_DG/pams/datafile/undotbs1.611.832794927
+DATA_DG/pams/datafile/sysaux.609.832794857
+DATA_DG/pams/datafile/system.610.832794891

7 rows selected.

SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
+DATA_DG/pams/onlinelog/group_12.613.832793723
+DATA_DG/pams/onlinelog/group_11.594.832793715
+DATA_DG/pams/onlinelog/group_10.606.832793705

4、将spfile移到DATA_DG中去
SQL> show parameter spfile;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /dba/oracle/product/11.2.0.3.0
/dbs/spfilepams.ora
SQL> create pfile='/tmp/pfilepams.ora' from spfile;
File created.
SQL> create spfile='+DATA_DG' from pfile='/tmp/pfilepams.ora';
File created.
SQL>

注意：要移走之前的spfile，并且修改initpams.ora的指向
cnsz03pl0032:pams > mv spfilepams.ora bak_spfilepams.ora
cnsz03pl0032:pams > cat initlscmst.ora
SPFILE='+DATA_DG/lscmst/spfilelscmst.ora'
cnsz03pl0032:pams > vi initpams.ora
SPFILE='+DATA_DG/PAMS/PARAMETERFILE/spfile.605.832798927'

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

Total System Global Area 7933816832 bytes
Fixed Size 2243872 bytes
Variable Size 1476395744 bytes
Database Buffers 6442450944 bytes
Redo Buffers 12726272 bytes
Database mounted.
Database opened.
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA_DG/pams/parameterfile/sp
file.605.832798927
SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string
db_name string pams
db_unique_name string pams
global_names boolean FALSE
instance_name string pams
lock_name_space string
log_file_name_convert string
processor_group_name string
service_names string pams
SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
+DATA_DG/pams/onlinelog/group_12.613.832793723
+DATA_DG/pams/onlinelog/group_11.594.832793715
+DATA_DG/pams/onlinelog/group_10.606.832793705

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
+DATA_DG/pams/datafile/dbadata.608.832794821
+DATA_DG/pams/datafile/aud_index.607.832794761
+DATA_DG/pams/datafile/aud_data.615.832794687
+DATA_DG/pams/datafile/users.614.832794595
+DATA_DG/pams/datafile/undotbs1.611.832794927
+DATA_DG/pams/datafile/sysaux.609.832794857
+DATA_DG/pams/datafile/system.610.832794891

7 rows selected.

SQL> show parameter control
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string +DATA_DG/egmas/controlfile/cur
rent.588.832091379
control_management_pack_access string DIAGNOSTIC+TUNING

到这里终于大功告成！！！！


[/size]