What is DBID and how to change it?
DBID is called the internal database identifier. From oracle 9i DBNEWID utility is introduced to perform this task very easily. Prior to this you have to manually create a copy of a database and give it a new database name (DBNAME) by re-creating the control file. However, you could not give the database a new identifier (DBID).
Changing the DBID of a database is a serious procedure. When the DBID of a database is changed, all previous backups and archived logs of the database become unusable. After you change the DBID, you must open the database with the RESETLOGS option, which re-creates the online redo logs and resets their sequence to 1
Changing the DBNAME without changing the DBID does not require you to open with the RESETLOGS option, so database backups and archived logs are not invalidated.
Change DBID and DBNAME:
First take the full backup of your database:
C:\>sqlplus sys/sys@test as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Fri Apr 15 12:36:11 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Shutdown your database
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
Mount the database now!
SQL> startup mount
ORACLE instance started.
Total System Global Area 431038464 bytes
Fixed Size 1333676 bytes
Variable Size 327157332 bytes
Database Buffers 96468992 bytes
Redo Buffers 6078464 bytes
Database mounted.
Type the below command…
SQL> host nid target=sys/sys@test dbname=prod
DBNEWID: Release 11.1.0.6.0 - Production on Fri Apr 15 12:38:29 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to database TEST (DBID=93940391)
Connected to server version 11.1.0
Control Files in database:
D:\ORACLE\ORADATA\TEST\CONTROL01.CTL
D:\ORACLE\ORADATA\TEST\CONTROL02.CTL
D:\ORACLE\ORADATA\TEST\CONTROL03.CTL
Change database ID and database name TEST to PROD? (Y/[N]) => y
Proceeding with operation
Changing database ID from 93940391 to 160375751
Changing database name from TEST to PROD
Control File D:\ORACLE\ORADATA\TEST\CONTROL01.CTL - modified
Control File D:\ORACLE\ORADATA\TEST\CONTROL02.CTL - modified
Control File D:\ORACLE\ORADATA\TEST\CONTROL03.CTL - modified
Datafile D:\ORACLE\ORADATA\TEST\SYSTEM01.DBF - dbid changed, wrote new name
Datafile D:\ORACLE\ORADATA\TEST\SYSAUX01.DBF - dbid changed, wrote new name
Datafile D:\ORACLE\ORADATA\TEST\UNDOTBS01.DBF - dbid changed, wrote new name
Datafile D:\ORACLE\ORADATA\TEST\USERS01.DBF - dbid changed, wrote new name
Datafile D:\ORACLE\ORADATA\TEST\TEMP01.DBF - dbid changed, wrote new name
Control File D:\ORACLE\ORADATA\TEST\CONTROL01.CTL - dbid changed, wrote new
name
Control File D:\ORACLE\ORADATA\TEST\CONTROL02.CTL - dbid changed, wrote new
name
Control File D:\ORACLE\ORADATA\TEST\CONTROL03.CTL - dbid changed, wrote new
name
Instance shut down
Database name changed to PROD.
Modify parameter file and generate a new password file before restarting.
Database ID for database PROD changed to 160375751.
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.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> create pfile='C:\test\testinit.ora' from spfile;
File created.
Edit this initialization file:
test.__db_cache_size=96468992
test.__java_pool_size=12582912
test.__large_pool_size=4194304
test.__oracle_base='D:\oracle'#ORACLE_BASE set from environment
test.__pga_aggregate_target=113246208
test.__sga_target=318767104
test.__shared_io_pool_size=0
test.__shared_pool_size=192937984
test.__streams_pool_size=8388608
*.audit_file_dest='D:\oracle\admin\test\adump'
*.audit_trail='db'
*.compatible='11.1.0.0.0'
*.control_files='D:\oracle\oradata\test\control01.ctl','D:\oracle\oradata\test\control02.ctl','D:\oracle\oradata\test\control03.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='prod'
*.db_recovery_file_dest='D:\oracle\flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.diagnostic_dest='D:\oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)'
*.log_archive_dest_1='location=c:\archive\'
*.memory_target=428867584
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
SQL> create spfile from pfile='C:\test\testinit.ora';
File created.
SQL> startup mount
ORACLE instance started.
Total System Global Area 431038464 bytes
Fixed Size 1333676 bytes
Variable Size 327157332 bytes
Database Buffers 96468992 bytes
Redo Buffers 6078464 bytes
Database mounted.
Create the password file …
SQL> host orapwd file=D:\oracle\product\11.1.0\db_1\dbs\pwdprod.ora password=prod entries=10
If you are using Windows you must recreate the service so the correct name and parameter file are used:
SQL> host oradim -delete -sid test
Instance deleted.
SQL> host oradim -new -sid prod -intpwd prod -startmode a
Instance created.
Configure listener for new DBNAME
SQL> lsnrctl
SP2-0042: unknown command "lsnrctl" - rest of line ignored.
SQL> host lsnrctl
LSNRCTL for 32-bit Windows: Version 11.1.0.6.0 - Production on 15-APR-2011 13:03
:34
Copyright (c) 1991, 2007, Oracle. All rights reserved.
Welcome to LSNRCTL, type "help" for information.
LSNRCTL> reload
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ramtech-117)(PORT=1521))
)
The command completed successfully
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ramtech-117)(PORT=1521))
)
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 11.1.0.6.0 - Produ
ction
Start Date 15-APR-2011 10:04:39
Uptime 0 days 2 hr. 59 min. 6 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File D:\oracle\product\11.1.0\db_1\network\admin\listener.o
ra
Listener Log File d:\oracle\diag\tnslsnr\ramtech-117\listener\alert\log.
xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ramtech-117)(PORT=1521)))
Services Summary...
Service "prod" has 1 instance(s).
Instance "prod", status UNKNOWN, has 1 handler(s) for this service...
Service "refer" has 2 instance(s).
Instance "refer", status UNKNOWN, has 1 handler(s) for this service...
Instance "refer", status READY, has 1 handler(s) for this service...
Service "referXDB" has 1 instance(s).
Instance "refer", status READY, has 1 handler(s) for this service...
Service "refer_XPT" has 1 instance(s).
Instance "refer", status READY, has 1 handler(s) for this service...
Service "test" has 1 instance(s).
Instance "test", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL> exit
C:\>sqlplus sys/prod as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Fri Apr 15 13:09:01 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile from pfile='C:\test\testinit.ora' ;
File created.
SQL> startup mount
ORACLE instance started.
Total System Global Area 431038464 bytes
Fixed Size 1333676 bytes
Variable Size 255854164 bytes
Database Buffers 167772160 bytes
Redo Buffers 6078464 bytes
Database mounted.
SQL> alter database open resetlogs;
Database altered.
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- ----------
PROD READ WRITE
See DBID & DBNAME has been changed.
Changing DBNAME only:
Backup the database.
Shutdown and mount the database..
C:\>sqlplus sys/sys@prod as sysdba
SQL> startup mount
ORACLE instance started.
Total System Global Area 431038464 bytes
Fixed Size 1333676 bytes
Variable Size 260048468 bytes
Database Buffers 163577856 bytes
Redo Buffers 6078464 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
C:\>nid target=sys/sys dbname=stand setname=yes
DBNEWID: Release 11.1.0.6.0 - Production on Fri Apr 15 15:34:27 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to database PROD (DBID=160375751)
Connected to server version 11.1.0
Control Files in database:
D:\ORACLE\ORADATA\TEST\CONTROL01.CTL
D:\ORACLE\ORADATA\TEST\CONTROL02.CTL
D:\ORACLE\ORADATA\TEST\CONTROL03.CTL
Change database name of database PROD to STAND? (Y/[N]) => y
Proceeding with operation
Changing database name from PROD to STAND
Control File D:\ORACLE\ORADATA\TEST\CONTROL01.CTL - modified
Control File D:\ORACLE\ORADATA\TEST\CONTROL02.CTL - modified
Control File D:\ORACLE\ORADATA\TEST\CONTROL03.CTL - modified
Datafile D:\ORACLE\ORADATA\TEST\SYSTEM01.DBF - wrote new name
Datafile D:\ORACLE\ORADATA\TEST\SYSAUX01.DBF - wrote new name
Datafile D:\ORACLE\ORADATA\TEST\UNDOTBS01.DBF - wrote new name
Datafile D:\ORACLE\ORADATA\TEST\USERS01.DBF - wrote new name
Datafile D:\ORACLE\ORADATA\TEST\TEMP01.DBF - wrote new name
Control File D:\ORACLE\ORADATA\TEST\CONTROL01.CTL - wrote new name
Control File D:\ORACLE\ORADATA\TEST\CONTROL02.CTL - wrote new name
Control File D:\ORACLE\ORADATA\TEST\CONTROL03.CTL - wrote new name
Instance shut down
Database name changed to STAND.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID - Completed succesfully.
SQL> create spfile from pfile='C:\test\testinit.ora';
File created.
SQL> startup mount
ORACLE instance started.
Total System Global Area 431038464 bytes
Fixed Size 1333676 bytes
Variable Size 255854164 bytes
Database Buffers 167772160 bytes
Redo Buffers 6078464 bytes
Database mounted.
SQL> alter database open;
Database altered.
As u see in case of changing DBNAME there is no need to open the database with reset logs option
Changing DBID Only:
Please follow the same steps as for Changing DBNAME and type the below command as below:
C:\>nid target=sys/sys@prod
This time you need to open the database with reset logs option.
No comments:
Post a Comment