November 15th 2012

Merubah database name menggunakan DBNEWID Utility

Banyak cara yang dapat dilakukan untuk mengubah / rename database name seperti dengan recreate controlfile (dengan mengubah database name), bisa menggunakan RMAN, atau bisa menggunakan utilitas DBNEWID. Pada kali ini ingin sharing menggunakan fitur DBNEWID. Database yang digunakan kali ini yaitu Oracle 11gR2 Express Edition yang default dbname saat instalasi yaitu : XE dan ingin dirubah menjadi APPDB.

Berikut ini langkah-langkah untuk merubah database name (beserta dbid) :

1. Create terlebih dahulu pfile (bila eksisting parameter file menggunakan spfile).

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
[oracle@APPDB01 ~]$ env | grep ORACLE
ORACLE_SID=XE
ORACLE_BASE=/u01/app/oracle
ORACLE_HOSTNAME=APPDB01
ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe
[oracle@APPDB01 ~]$ sqlplus /nolog
 
SQL*Plus: Release 11.2.0.2.0 Production ON Fri Nov 9 22:30:12 2012
 
Copyright (c) 1982, 2011, Oracle.  ALL rights reserved.
 
SQL> conn /AS sysdba
Connected.
SQL> CREATE pfile FROM spfile;
 
File created.

2. Shutdown database lalu kemudian startup mount.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SQL> shutdown immediate;
DATABASE closed.
DATABASE dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
 
Total System Global Area  839282688 bytes
Fixed SIZE                  2231136 bytes
Variable SIZE             494929056 bytes
DATABASE Buffers          339738624 bytes
Redo Buffers                2383872 bytes
DATABASE mounted.
SQL> exit
Disconnected FROM Oracle DATABASE 11g Express Edition Release 11.2.0.2.0 - 64bit Production

3. Kemudian jalankan command nid.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
[oracle@APPDB01 ~]$ nid target=system/zzzzzzzz dbname=APPDB
 
DBNEWID: Release 11.2.0.2.0 - Production ON Fri Nov 9 22:31:01 2012
 
Copyright (c) 1982, 2009, Oracle AND/OR its affiliates.  ALL rights reserved.
 
Connected TO DATABASE XE (DBID=2680055995)
 
Connected TO server version 11.2.0
 
Control Files IN DATABASE:
    /u01/app/oracle/oradata/XE/control.dbf
 
CHANGE DATABASE ID AND DATABASE name XE TO APPDB? (Y/[N]) => Y
 
Proceeding WITH operation
Changing DATABASE ID FROM 2680055995 TO 3912404261
Changing DATABASE name FROM XE TO APPDB
    Control File /u01/app/oracle/oradata/XE/control.dbf - modified
    Datafile /u01/app/oracle/oradata/XE/system.db - dbid changed, wrote NEW name
    Datafile /u01/app/oracle/oradata/XE/sysaux.db - dbid changed, wrote NEW name
    Datafile /u01/app/oracle/oradata/XE/undotbs1.db - dbid changed, wrote NEW name
    Datafile /u01/app/oracle/oradata/XE/users.db - dbid changed, wrote NEW name
    Datafile /u01/app/oracle/oradata/XE/temp.db - dbid changed, wrote NEW name
    Control File /u01/app/oracle/oradata/XE/control.dbf - dbid changed, wrote NEW name
    Instance shut down
 
DATABASE name changed TO APPDB.
MODIFY parameter file AND generate a NEW password file BEFORE restarting.
DATABASE ID FOR DATABASE APPDB changed TO 3912404261.
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 name AND ID.
DBNEWID - Completed succesfully.

4. Setelah menjalankan command nid, database akan berada dalam kondisi mati. Lakukan perubahan parameter db_name pada pfile yang sudah dibuat dan startup menggunakan pfile tersebut.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
[oracle@APPDB01 dbs]$ pwd
/u01/app/oracle/product/11.2.0/xe/dbs
[oracle@APPDB01 dbs]$ ls
hc_XE.dat  init.ora  initXE.ora  lkXE  orapwXE  spfileXE.ora
[oracle@APPDB01 dbs]$ sqlplus /nolog
 
SQL*Plus: Release 11.2.0.2.0 Production ON Fri Nov 9 22:32:35 2012
 
Copyright (c) 1982, 2011, Oracle.  ALL rights reserved.
 
SQL> conn /AS sysdba
Connected TO an idle instance.
SQL> startup mount pfile='/u01/app/oracle/product/11.2.0/xe/dbs/initXE.ora';
ORACLE instance started.
 
Total System Global Area  839282688 bytes
Fixed SIZE                  2231136 bytes
Variable SIZE             494929056 bytes
DATABASE Buffers          339738624 bytes
Redo Buffers                2383872 bytes
DATABASE mounted.

5. Open database menggunakan resetlogs option.

1
2
3
SQL> ALTER DATABASE OPEN resetlogs;
 
DATABASE altered.

6. Jangan lupa untuk create password file lalu merubah listener file dan tnsnames file dengan service name yang baru. Disamping itu edit dan rubah SID pada bash profile user oracle.

1
[oracle@APPDB01 ~]$ orapwd file=/u01/app/oracle/product/11.2.0/xe/dbs/initAPPDB password=zzzzzzzz entries=5 force=Y

Incoming search terms:

  • cara menggunakan oracle sql developer
  • cara merubah SID oracle 10g
  • merubah data created dan data modified
  • oracle cara edit database
  • ubah target db oracle command
  • yhs-default

1 Comment »