[20200407]nid修改DBID還原.txt

[20200407]nid修改DBID還原.txt

--//別人問的問題，改錯dbid是否可以修改回來。

$  nid
DBNEWID: Release 11.2.0.4.0 - Production on Tue Apr 7 11:36:23 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Keyword     Description                    (Default)
----------------------------------------------------
TARGET      Username/Password              (NONE)
DBNAME      New database name              (NONE)
LOGFILE     Output Log                     (NONE)
REVERT      Revert failed change           NO
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SETNAME     Set a new database name only   NO
APPEND      Append to output log           NO
HELP        Displays these messages        NO
--//nid有一個REVERT引數可以反轉，看看是否有效。

1.環境：
SCOTT@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SCOTT@book> select name,open_mode,dbid,db_unique_name from v$database;
NAME OPEN_MODE        DBID DB_UNIQUE_NAME
---- ---------- ---------- ---------------
BOOK READ WRITE 1337401710 book
--//dbid= 1337401710

2.使用nid修改dbid

--//關閉資料庫啟動到mount狀態。

SYS@book> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SYS@book> startup mount
ORACLE instance started.
Total System Global Area  643084288 bytes
Fixed Size                  2255872 bytes
Variable Size             205521920 bytes
Database Buffers          427819008 bytes
Redo Buffers                7487488 bytes
Database mounted.

$ nid TARGET=/
DBNEWID: Release 11.2.0.4.0 - Production on Tue Apr 7 11:43:18 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to database BOOK (DBID=1337401710)
Connected to server version 11.2.0

Control Files in database:
    /mnt/ramdisk/book/control01.ctl
    /mnt/ramdisk/book/control02.ctl

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

Proceeding with operation
Changing database ID from 1337401710 to 1477856022
    Control File /mnt/ramdisk/book/control01.ctl - modified
    Control File /mnt/ramdisk/book/control02.ctl - modified
    Datafile /mnt/ramdisk/book/system01.db - dbid changed
    Datafile /mnt/ramdisk/book/sysaux01.db - dbid changed
    Datafile /mnt/ramdisk/book/undotbs01.db - dbid changed
    Datafile /mnt/ramdisk/book/users01.db - dbid changed
    Datafile /mnt/ramdisk/book/example01.db - dbid changed
    Datafile /mnt/ramdisk/book/tea01.db - dbid changed
    Datafile /mnt/ramdisk/book/temp01.db - dbid changed
    Control File /mnt/ramdisk/book/control01.ctl - dbid changed
    Control File /mnt/ramdisk/book/control02.ctl - dbid changed
    Instance shut down

Database ID for database BOOK changed to 1477856022.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database ID.
DBNEWID - Completed succesfully.

3.檢查驗證：
SYS@book> startup mount
ORACLE instance started.
Total System Global Area  643084288 bytes
Fixed Size                  2255872 bytes
Variable Size             205521920 bytes
Database Buffers          427819008 bytes
Redo Buffers                7487488 bytes
Database mounted.

SYS@book> select name,open_mode,dbid,db_unique_name from v$database;
NAME OPEN_MODE       DBID DB_UNIQUE_NAME
---- --------- ---------- --------------
BOOK MOUNTED   1477856022 book
--//dbid = 1477856022

$ nid TARGET=/ REVERT=YES
DBNEWID: Release 11.2.0.4.0 - Production on Tue Apr 7 11:45:44 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to database BOOK (DBID=1477856022)
NID-00128: Cannot revert change of database, change not in progress
Reversion of database changes failed during validation - database is intact.
DBNEWID - Completed with validation errors.

--//已經無法該會。我估計如果的當時儲存控制檔案還在，也許可以改會。

4.只能使用bbed修改。

BBED> p /d dba 1,1 kcvfh.kcvfhhdr.kccfhdbi
ub4 kccfhdbi                                @28       1477856022

$ seq 6 | xargs -IQ echo "p /d dba Q,1 kcvfh.kcvfhhdr.kccfhdbi" | rlbbed  | grep kccfhdbi
BBED> ub4 kccfhdbi                                @28       1477856022
BBED> ub4 kccfhdbi                                @28       1477856022
BBED> ub4 kccfhdbi                                @28       1477856022
BBED> ub4 kccfhdbi                                @28       1477856022
BBED> ub4 kccfhdbi                                @28       1477856022
BBED> ub4 kccfhdbi                                @28       1477856022
--//OK正確。
$ seq 6 | xargs -IQ echo " assign dba Q,1 kcvfh.kcvfhhdr.kccfhdbi = 1337401710 "
 assign dba 1,1 kcvfh.kcvfhhdr.kccfhdbi = 1337401710
 assign dba 2,1 kcvfh.kcvfhhdr.kccfhdbi = 1337401710
 assign dba 3,1 kcvfh.kcvfhhdr.kccfhdbi = 1337401710
 assign dba 4,1 kcvfh.kcvfhhdr.kccfhdbi = 1337401710
 assign dba 5,1 kcvfh.kcvfhhdr.kccfhdbi = 1337401710
 assign dba 6,1 kcvfh.kcvfhhdr.kccfhdbi = 1337401710
--//執行以上指令碼。注意如果透過pipe，必須在第1，2行之間插入1行，內容為Y。

$ seq 6 | xargs -IQ echo " sum apply  dba Q,1 "
 sum apply  dba 1,1
 sum apply  dba 2,1
 sum apply  dba 3,1
 sum apply  dba 4,1
 sum apply  dba 5,1
 sum apply  dba 6,1
--//執行以上指令碼。

5.驗證看看：
--//重建控制檔案：
SYS@book> alter database backup controlfile to trace  ;
Database altered.

--//抽取執行指令碼：
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "BOOK" NORESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/mnt/ramdisk/book/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/mnt/ramdisk/book/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/mnt/ramdisk/book/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
--   GROUP 4 '/mnt/ramdisk/book/redostb01.log'  SIZE 50M BLOCKSIZE 512,
--   GROUP 5 '/mnt/ramdisk/book/redostb02.log'  SIZE 50M BLOCKSIZE 512,
--   GROUP 6 '/mnt/ramdisk/book/redostb03.log'  SIZE 50M BLOCKSIZE 512,
--   GROUP 7 '/mnt/ramdisk/book/redostb04.log'  SIZE 50M BLOCKSIZE 512
DATAFILE
  '/mnt/ramdisk/book/system01.dbf',
  '/mnt/ramdisk/book/sysaux01.dbf',
  '/mnt/ramdisk/book/undotbs01.dbf',
  '/mnt/ramdisk/book/users01.dbf',
  '/mnt/ramdisk/book/example01.dbf',
  '/mnt/ramdisk/book/tea01.dbf'
CHARACTER SET ZHS16GBK
;

-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');
-- Configure RMAN configuration record 2
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','DISK PARALLELISM 3 BACKUP TYPE TO BACKUPSET');
-- Configure RMAN configuration record 3
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEFAULT DEVICE TYPE TO','DISK');
--//執行以上內容。

-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/archivelog/book/1_1_896605872.dbf';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE
-- All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
-- Database can now be opened normally.
ALTER DATABASE OPEN;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/mnt/ramdisk/book/temp01.dbf' REUSE;
-- End of tempfile additions.
--
--
--
----------------------------------------------------------

SYS@book> select name,open_mode,dbid,db_unique_name from v$database;
NAME OPEN_MODE       DBID DB_UNIQUE_NAME
---- --------- ---------- --------------
BOOK MOUNTED   1337401710

SYS@book> alter database open ;
Database altered.

SYS@book> ALTER TABLESPACE TEMP ADD TEMPFILE '/mnt/ramdisk/book/temp01.dbf' REUSE;
Tablespace altered.

SYS@book> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/archivelog/book/
Oldest online log sequence     927
Next log sequence to archive   929
Current log sequence           929

SYS@book> select name,open_mode,dbid,db_unique_name from v$database;
NAME OPEN_MODE        DBID DB_UNIQUE_NAME
---- ---------- ---------- ---------------
BOOK READ WRITE 1337401710

--//有點奇怪的是這樣恢復的DB_UNIQUE_NAME是空。
SYS@book> show parameter DB_UNIQUE_NAME
NAME           TYPE   VALUE
-------------- ------ ------
db_unique_name string book

--//估計建立的控制檔案一些資訊沒有填充，我記憶裡面第1次啟動要改寫控制檔案內容寫入DB_UNIQUE_NAME，也許重啟資料庫就ok了。
SYS@book> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@book> startup mount
ORACLE instance started.
Total System Global Area  643084288 bytes
Fixed Size                  2255872 bytes
Variable Size             205521920 bytes
Database Buffers          427819008 bytes
Redo Buffers                7487488 bytes
Database mounted.
SYS@book> select name,open_mode,dbid,db_unique_name from v$database;
NAME OPEN_MODE       DBID DB_UNIQUE_NAME
---- --------- ---------- --------------
BOOK MOUNTED   1337401710 book

--//OK.實際上這個問題就在於一臺伺服器執行多個例項，登入會話之間切來切去，很容易搞亂。
--//在執行前小心驗證就不會出現這個情況了。建立standby redo。
-- The following script can be used on the standby database
-- to re-populate entries for a standby controlfile created
-- on the primary and copied to the standby site.
----------------------------------------------------------
ALTER DATABASE ADD STANDBY LOGFILE '/mnt/ramdisk/book/redostb01.log' SIZE 50M BLOCKSIZE 512 REUSE;
ALTER DATABASE ADD STANDBY LOGFILE '/mnt/ramdisk/book/redostb02.log' SIZE 50M BLOCKSIZE 512 REUSE;
ALTER DATABASE ADD STANDBY LOGFILE '/mnt/ramdisk/book/redostb03.log' SIZE 50M BLOCKSIZE 512 REUSE;
ALTER DATABASE ADD STANDBY LOGFILE '/mnt/ramdisk/book/redostb04.log' SIZE 50M BLOCKSIZE 512 REUSE;