在丢失临时文件情况下修改数据库DB NAME
Jun232015
前几天发表了一篇使用NID工具修改数据库的DBID和DBNAME的文章(详见：http://www.dbdream.com.cn/2015/06/19/%E4%BD%BF%E7%94%A8nid%E4%BF%AE%E6%94%B9%E6%95%B0%E6%8D%AE%E5%BA%93db-name/)，有朋友问如果修改的数据库是通过RMAN恢复的，修改时如果没有创建临时文件，怎么办？这样的案例我也没有遇到过，下面是我在10.2.0.1.0版本的数据库模拟丢失临时文件修改DB NAME的试验：
SYS@EMREP> select name from v$tempfile; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/EMREP/temp01.dbf SYS@EMREP> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SYS@EMREP> startup mount ORACLE instance started. Total System Global Area 587202560 bytes Fixed Size 1220724 bytes Variable Size 205524876 bytes Database Buffers 377487360 bytes Redo Buffers 2969600 bytes Database mounted. [oracle@dbdream dbs]$ rm /u01/app/oracle/oradata/EMREP/temp01.dbf
删除掉临时文件，使用NID工具尝试修改DBNAME，这里制定了SETNAME参数，只修改DBNAME而不修改DBID。
[oracle@dbdream dbs]$ nid target= / as sysdba dbname=dbdream SETNAME=yes
DBNEWID: Release 10.2.0.1.0 - Production on Tue Jun 23 10:49:53 2015
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Password:
Connected to database EMREP (DBID=4065571978)
Operation already in progress, continuing
Connected to server version 10.2.0
Control Files in database:
/u01/app/oracle/oradata/EMREP/control01.ctl
/u01/app/oracle/oradata/EMREP/control02.ctl
/u01/app/oracle/oradata/EMREP/control03.ctl
Change database name of database EMREP to DBDREAM? (Y/[N]) => y
Proceeding with operation
Changing database name from EMREP to DBDREAM
Control File /u01/app/oracle/oradata/EMREP/control01.ctl - modified
Control File /u01/app/oracle/oradata/EMREP/control02.ctl - modified
Control File /u01/app/oracle/oradata/EMREP/control03.ctl - modified
Datafile /u01/app/oracle/oradata/EMREP/system01.dbf - already changed
Datafile /u01/app/oracle/oradata/EMREP/undotbs01.dbf - already changed
Datafile /u01/app/oracle/oradata/EMREP/sysaux01.dbf - already changed
Datafile /u01/app/oracle/oradata/EMREP/users01.dbf - already changed
Datafile /u01/app/oracle/oradata/EMREP/mgmt.dbf - already changed
Datafile /u01/app/oracle/oradata/EMREP/mgmt_ecm_depot1.dbf - already changed
Datafile /u01/app/oracle/oradata/EMREP/rc_data.dbf - already changed
NID-00111: Oracle error reported from target database while executing
begin dbms_backup_restore.nidprocessdf(:fno, :istemp, :skipped, :idchged, :nmchged); end;
ORA-01116: error in opening database file /u01/app/oracle/oradata/EMREP/temp01.dbf
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 6164
ORA-06512: at line 1
Change of database name failed.
Must finish change or REVERT changes before attempting any database operation.
DBNEWID - Completed with errors.
这里由于找不到临时文件而报错，此时的数据库已经无法正常打开。修改参数文件中DBNAME参数，启动到MOUNT状态。
[oracle@dbdream dbs]$ vi initEMREP.ora *.db_name=’DBDREAM’ [oracle@dbdream dbs]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jun 23 10:48:38 2015 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to an idle instance. SYS@EMREP> startup mount ORACLE instance started. Total System Global Area 587202560 bytes Fixed Size 1220724 bytes Variable Size 205524876 bytes Database Buffers 377487360 bytes Redo Buffers 2969600 bytes Database mounted.
此时控制文件中的DBNAME并没有被修改。
SYS@EMREP> show parameter name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_file_name_convert string db_name string EMREP db_unique_name string EMREP global_names boolean FALSE instance_name string EMREP lock_name_space string log_file_name_convert string service_names string EMREP
尝试打开数据库会报ORA-19951错误。
SYS@EMREP> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-19951: cannot modify control file until DBNEWID is completed
备份控制文件。
SYS@EMREP> alter database backup controlfile to trace; Database altered.
根据控制文件备份，重建控制文件。
[oracle@dbdream dbs]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jun 23 10:54:54 2015 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to an idle instance. SYS@EMREP> create spfile from pfile; File created. SYS@EMREP> startup nomount ORACLE instance started. Total System Global Area 587202560 bytes Fixed Size 1220724 bytes Variable Size 163581836 bytes Database Buffers 419430400 bytes Redo Buffers 2969600 bytes SYS@EMREP> CREATE CONTROLFILE REUSE DATABASE "DBDREAM" RESETLOGS ARCHIVELOG 2 MAXLOGFILES 16 3 MAXLOGMEMBERS 3 4 MAXDATAFILES 100 5 MAXINSTANCES 8 6 MAXLOGHISTORY 292 7 LOGFILE 8 GROUP 1 '/u01/app/oracle/oradata/EMREP/redo01.log' SIZE 50M, 9 GROUP 2 '/u01/app/oracle/oradata/EMREP/redo02.log' SIZE 50M, 10 GROUP 3 '/u01/app/oracle/oradata/EMREP/redo03.log' SIZE 50M 11 DATAFILE 12 '/u01/app/oracle/oradata/EMREP/system01.dbf', 13 '/u01/app/oracle/oradata/EMREP/undotbs01.dbf', 14 '/u01/app/oracle/oradata/EMREP/sysaux01.dbf', 15 '/u01/app/oracle/oradata/EMREP/users01.dbf', 16 '/u01/app/oracle/oradata/EMREP/mgmt.dbf', 17 '/u01/app/oracle/oradata/EMREP/mgmt_ecm_depot1.dbf', 18 '/u01/app/oracle/oradata/EMREP/rc_data.dbf' 19 CHARACTER SET US7ASCII 20 ; Control file created.
打开数据库。
SYS@EMREP> alter database open resetlogs; Database altered. SYS@EMREP> show parameter name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_file_name_convert string db_name string DBDREAM db_unique_name string DBDREAM global_names boolean FALSE instance_name string EMREP lock_name_space string log_file_name_convert string service_names string DBDREAM
添加临时文件。
SYS@EMREP> select name from v$tempfile; no rows selected SYS@EMREP> alter tablespace temp add tempfile '/u01/app/oracle/oradata/EMREP/temp_1.dbf' size 50M autoextend on; Tablespace altered. SYS@EMREP> select name from v$tempfile; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/EMREP/temp_1.dbf
最后，重建密码文件即可。
在丢失临时文件情况下修改数据库DB NAME：等您坐沙发呢！
发表评论
