OS PLATFORM : Windows
Archive Log : Enable
Non Distributed Database System.
DBNEWID is a database utility that can change the internal, unique database identifier (DBID) and the database name (DBNAME) for an operational database.
Note:
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. This is similar to creating a database except that the data is already in the datafiles. After you change the DBID, backups and archive logs that were created prior to the change can no longer be used because they still have the original DBID, which does not match the current DBID. You must open the database with the RESETLOGS option, which re-creates the online redo logs and resets their sequence to 1.
STEPS
SQL> select DBID from v$database;
DBID
----------
13855521
1. Take Complete Recoverable Database BACKUP.
C:\>rman target=sys@ORA101
Recovery Manager: Release 10.1.0.2.0 - Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.
target database Password:
connected to target database: ORA101 (DBID=13855521)
RMAN> configure controlfile autobackup on;
using target database controlfile instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
RMAN> run
2> {
3> backup database;
4> backup archivelog all;
5> }
Starting backup at 02-APR-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=135 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\SYSTEM01.D
BF
input datafile fno=00003 name=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\SYSAUX01.D
BF
input datafile fno=00005 name=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\EXAMPLE01.
DBF
input datafile fno=00002 name=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\UNDOTBS01.
DBF
input datafile fno=00004 name=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\USERS01.DB
F
channel ORA_DISK_1: starting piece 1 at 02-APR-07
channel ORA_DISK_1: finished piece 1 at 02-APR-07
piece handle=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORA101\BACKUPSET\2007_
04_02\O1_MF_NNNDF_TAG20070402T125943_311KJ15X_.BKP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:45
Finished backup at 02-APR-07
Starting backup at 02-APR-07
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=7 recid=1 stamp=618839783
input archive log thread=1 sequence=8 recid=2 stamp=618842813
input archive log thread=1 sequence=9 recid=3 stamp=618843692
channel ORA_DISK_1: starting piece 1 at 02-APR-07
channel ORA_DISK_1: finished piece 1 at 02-APR-07
piece handle=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORA101\BACKUPSET\2007_
04_02\O1_MF_ANNNN_TAG20070402T130132_311KMG29_.BKP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:05
Finished backup at 02-APR-07
Starting Control File and SPFILE Autobackup at 02-APR-07
piece handle=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORA101\AUTOBACKUP\2007
_04_02\O1_MF_S_618843697_311KML4F_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 02-APR-07
RMAN>
2.Shutdown database and startup in mount mode.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORA-12528: TNS:listener: all appropriate instances are blocking new connections
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Pr
oduction
With the Partitioning, OLAP and Data Mining options
C:\>set oracle_sid=ora101
C:\>sqlplus sys as sysdba
SQL*Plus: Release 10.1.0.2.0 - Production on Mon Apr 2 16:12:42 2007
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> startup mount;
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> alter database mount;
Database altered.
3.Invoke the DBNEWID utility on the command line, specifying a valid user with the SYSDBA privilege
C:\>nid TARGET=SYS/oracle@ORA101
DBNEWID: Release 10.1.0.2.0 - Production
Copyright (c) 2001, 2004, Oracle. All rights reserved.
Connected to database ORA101 (DBID=13855521)
Connected to server version 10.1.0
Control Files in database:
C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\CONTROL01.CTL
C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\CONTROL02.CTL
C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\CONTROL03.CTL
Change database ID of database ORA101? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 13855521 to 13868607
Control File C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\CONTROL01.CTL - modifie
d
Control File C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\CONTROL02.CTL - modifie
d
Control File C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\CONTROL03.CTL - modifie
d
Datafile C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\SYSTEM01.DBF - dbid changed
Datafile C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\UNDOTBS01.DBF - dbid change
d
Datafile C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\SYSAUX01.DBF - dbid changed
Datafile C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\USERS01.DBF - dbid changed
Datafile C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\EXAMPLE01.DBF - dbid change
d
Datafile C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\TEMP01.DBF - dbid changed
Control File C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\CONTROL01.CTL - dbid ch
anged
Control File C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\CONTROL02.CTL - dbid ch
anged
Control File C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\CONTROL03.CTL - dbid ch
anged
Instance shut down
Database ID for database ORA101 changed to 13868607.
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.
4.startup database in mound mode.
C:\>set oracle_sid=ora101
C:\>sqlplus sys as sysdba
SQL*Plus: Release 10.1.0.2.0 - Production on Mon Apr 2 16:16:42 2007
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Enter password:
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145488364 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
Database mounted.
5.open database with RESETLOGS.
SQL> alter database open resetlogs;
Database altered.
SQL> select DBID from v$database;
DBID
----------
13868607
NOTE: Take New DATABASE BACKUP previous backup is no longer usable.
No comments:
Post a Comment