記一次通過DBNEWID工具修改DBID時的NID-00135: There are 1 active threads錯誤

ORACLE 10G

REDHAT 5.3 64BIT

錯誤原因：

通過DBNEWID 工具修改DBID

[@more@]

由於通過RMAN的recover新建備用庫後需要將新庫的DBID進行修改，但是按照正常的操作執行到修改DBID的時候居然報錯，錯誤內容如下：

[oracle@qqdb bk]$ nid target=sys/sys123@back

DBNEWID: Release 10.2.0.1.0 - Production on Fri Dec 3 16:43:29 2010

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

Connected to database BACK (DBID=4055002079)

NID-00135: There are 1 active threads


Change of database ID failed during validation - database is intact.
DBNEWID - Completed with validation errors.

通過檢查各個環節發現沒有什麼異常：

[oracle@qqdb bk]$ ps -ef|grep -u oracle
oracle 2650 1 0 15:47 ? 00:00:01 /u01/app/10.2.1/db_1/bin/tnslsnr LISTENER -inherit
root 3334 3295 0 15:53 pts/0 00:00:00 su - oracle
oracle 3335 3334 0 15:53 pts/0 00:00:00 -bash
root 3407 3376 0 15:54 pts/1 00:00:00 su - oracle
oracle 3408 3407 0 15:54 pts/1 00:00:01 -bash
oracle 3733 3335 0 16:26 pts/0 00:00:00 sqlplus as sysdba
oracle 3736 1 0 16:26 ? 00:00:00 ora_pmon_back
oracle 3738 1 0 16:26 ? 00:00:00 ora_psp0_back
oracle 3740 1 0 16:26 ? 00:00:00 ora_mman_back
oracle 3742 1 0 16:26 ? 00:00:00 ora_dbw0_back
oracle 3744 1 0 16:26 ? 00:00:00 ora_lgwr_back
oracle 3746 1 0 16:26 ? 00:00:01 ora_ckpt_back
oracle 3748 1 0 16:27 ? 00:00:00 ora_smon_back
oracle 3750 1 0 16:27 ? 00:00:00 ora_reco_back
oracle 3752 1 0 16:27 ? 00:00:00 ora_mmon_back
oracle 3754 1 0 16:27 ? 00:00:00 ora_mmnl_back
oracle 3830 3408 0 16:43 pts/1 00:00:00 ps -ef
oracle 3831 3408 0 16:43 pts/1 00:00:00 grep -u oracle

SQL> startup mount;
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 2019320 bytes
Variable Size 113246216 bytes
Database Buffers 50331648 bytes
Redo Buffers 2174976 bytes
Database mounted.
SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL>

最後的解決方案：

原來在使用DBNEWID工具的時候首先要保證

1、資料庫處於mount狀態

2、資料庫處於歸檔狀態

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/10.2.1/db_1/dbs/arch
Oldest online log sequence 0
Next log sequence to archive 1
Current log sequence 1

3、歸檔目錄必須存在

[oracle@qqdb bk]$ cd /u01/app/10.2.1/db_1/dbs/arch
-bash: cd: /u01/app/10.2.1/db_1/dbs/arch: No such file or directory

4、資料檔案必須處於online狀態

FILE# STATUS NAME
---------- ------- ----------------------------------------
1 SYSTEM /u01/oradata/back/system01.dbf
2 ONLINE /u01/oradata/back/undotbs01.dbf
3 ONLINE /u01/oradata/back/sysaux01.dbf
4 ONLINE /u01/oradata/back/users01.dbf
5 ONLINE /u01/oradata/back/test_tran01.dbf

很明顯在我的環境中第三個要素沒有滿足，也就是歸檔目錄不存在。

下面指定一個存在的歸檔目錄：

[oracle@qqdb rman]$ pwd
/u02/rman

SQL> alter system set log_archive_dest_1='location=/u02/rman';

System altered.

SQL> alter database open;

Database altered.

SQL> alter system archive log current;

System altered.

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

Total System Global Area 167772160 bytes
Fixed Size 2019320 bytes
Variable Size 113246216 bytes
Database Buffers 50331648 bytes
Redo Buffers 2174976 bytes
Database mounted.

然後重新執行，問題才得以解決：

[oracle@qqdb bk]$ nid target=sys/sys123@back

DBNEWID: Release 10.2.0.1.0 - Production on Fri Dec 3 16:50:30 2010

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

Connected to database BACK (DBID=4055002079)

NID-00121: Database should not be open


Change of database ID failed during validation - database is intact.
DBNEWID - Completed with validation errors.

[oracle@qqdb bk]$ nid target=sys/sys123@back

DBNEWID: Release 10.2.0.1.0 - Production on Fri Dec 3 16:51:14 2010

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

Connected to database BACK (DBID=4055002079)

Connected to server version 10.2.0

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

Change database ID of database BACK? (Y/[N]) => y

Proceeding with operation
Changing database ID from 4055002079 to 4055580995
Control File /u01/oradata/back/control01.ctl - modified
Control File /u01/oradata/back/control02.ctl - modified
Control File /u01/oradata/back/control03.ctl - modified
Datafile /u01/oradata/back/system01.dbf - dbid changed
Datafile /u01/oradata/back/undotbs01.dbf - dbid changed
Datafile /u01/oradata/back/sysaux01.dbf - dbid changed
Datafile /u01/oradata/back/users01.dbf - dbid changed
Datafile /u01/oradata/back/temp01.dbf - dbid changed
Control File /u01/oradata/back/control01.ctl - dbid changed
Control File /u01/oradata/back/control02.ctl - dbid changed
Control File /u01/oradata/back/control03.ctl - dbid changed
Instance shut down

Database ID for database BACK changed to 4055580995.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database ID.
DBNEWID - Completed succesfully.

[oracle@qqdb bk]$

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/10130206/viewspace-1042560/，如需轉載，請註明出處，否則將追究法律責任。