How to change DBID

DBID change

If the new database was created by copying files from an existing database, it will have the same DBID as the original database and cannot be registered in the same recovery catalog. Hence, rman will give the following error:

RMAN-20002: target database already registered in recovery catalog

Steps to change DBID:

1. Source into target database and check the DBID before change

   $sqlplus "/as sysdba"

  

   SQL> select dbid, name, open_mode, activation#, created from v$database;

         DBID NAME      OPEN_MODE  ACTIVATION# CREATED

   ---------- --------- ---------- ----------- ---------

   3220625138 ORCL   READ WRITE  3925125663 28-SEP-11

2. SQL> shutdown immediate;

   Database closed.

   Database dismounted.

   ORACLE instance shut down.

Note: If you have to do a shutdown abort, then start it up and shut it down again. Do not implement  this change against a database which has used the abort option to shutdown

3. SQL> startup mount

   ORACLE instance started.

   Total System Global Area  135338868 bytes

   Fixed Size                   453492 bytes

   Variable Size             109051904 bytes

   Database Buffers           25165824 bytes

   Redo Buffers                 667648 bytes

   Database mounted.

   SQL>exit

4. Now execute NID $ prompt

SQL> exit

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.8.0 – Production

omrdb<ORCL>$ nid target=/

DBNEWID: Release 9.2.0.8.0 - 64bit Production

Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

Connected to database ORCL (DBID=3220625138)

Control Files in database:

    /dev/oracle/oradata/Orcl/ctrl01

    /dev/oracle/oradata/Orcl/ctrl02

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

Proceeding with operation

Changing database ID from  3220625138  to 3220625138

    Control File /dev/oracle/oradata/Orcl/ctrl01 - modified

    Control File /dev/oracle/oradata/Orcl/ctrl02 - modified

    Datafile /dev/oracle/oradata/Orcl/sys01 - dbid changed

    Datafile /dev/oracle/oradata/Orcl/rb10101 - dbid changed

    Datafile /dev/oracle/oradata/Orcl/spare01 - dbid changed

    Datafile /dev/oracle/oradata/Orcl/tools01 - dbid changed

    Datafile /dev/oracle/oradata/Orcl/users01 - dbid changed

    Datafile /dev/oracle/oradata/Orcl/tab01 - dbid changed

    Datafile /dev/oracle/oradata/Orcl/idx01 - dbid changed

    Datafile /dev/oracle/oradata/Orcl/tab02 - dbid changed

    Datafile /dev/oracle/oradata/Orcl/idx02 - dbid changed

    Datafile /dev/oracle/oradata/Orcl/tab03 - dbid changed

    Datafile /dev/oracle/oradata/Orcl/tab04 - dbid changed

    Datafile /dev/oracle/oradata/Orcl/undo01 - dbid changed

    Datafile /dev/oracle/oradata/Orcl/tab05 - dbid changed

    Datafile /dev/oracle/oradata/Orcl/idx03 - dbid changed

    Datafile /dev/oracle/oradata/Orcl/tab06 - dbid changed

    Datafile /dev/oracle/oradata/Orcl/tab07 - dbid changed

    Datafile /dev/oracle/oradata/Orcl/users02 - dbid changed

    Control File /dev/oracle/oradata/Orcl/ctrl01 - dbid changed

    Control File /dev/oracle/oradata/Orcl/ctrl02 - dbid changed

Database ID for database ORCL changed to 3120624238.

All previous backups and archived redo logs for this database are unusable.

Shut down database and open with RESETLOGS option.

Succesfully changed database ID.

DBNEWID - Completed succesfully.

5. SQL> shutdown immediate;

   ORA-01109: database not open

   Database dismounted.

   ORACLE instance shut down.

6. Create the new passwordfile in $ORACLE_HOME/dbs directory using this command

$orapwd file=orapw$ORACLE_SID entries=12 password=SYS_PASSWORD

7. SQL> startup mount

   ORACLE instance started.

   Total System Global Area  135338868 bytes

   Fixed Size                   453492 bytes

   Variable Size             109051904 bytes

   Database Buffers           25165824 bytes

   Redo Buffers                 667648 bytes

   Database mounted.

8. SQL> alter database open resetlogs;

   Database altered.

9. Verify new DBID

    SQL> select dbid,name,open_mode,activation#,created from v$database;

DBID              NAME   OPEN_MODE  ACTIVATION# CREATED

----------------- ----------- ------------------ --------------------  -------------

3220625138   ORCL READ WRITE  3220625138         28-SEP-11

10. If you have Standby database for this database, you need to rebuild it  after DBID change.

Note:

REVERT option allows us to go back on the steps of executing.  If its YES then a failed change of DBID should be reverted but a successfully completed change of DBID cannot be reverted.  REVERT=YES is only valid when a DBID change failed.  Default value is NO.

     

Steps to change DBNAME only

  1. Source in to target database

  2. Shutdown target database

      SQL>shutdown immediate;

      SQL>startup mount;

  3. Open one session and run NID with sysdba privileges

      $nid TARGET=/ DBNAME=<new dbname> SETNAME=Y

    

     - the value of DBNAME is the new dbname of the database

     - SETNAME must be set to Y. The default is N and causes the

       DBID to be changed also.

 

5. Shutdown the database

    Shutdown immediate;

6. Set the DB_NAME initialization parameter in the initialization parameter

     file to the new database name

7. Create a new password file

    $cd $ORACLE_HOME/dbs

    $orapwd file=orapw$ORACLE_SID entries=12 password=SYS_PASSWORD

  8. Startup of the database (without resetlogs)

         

Validate RMAN Backup

[oracle@omr dbs]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sat May 26 12:26:58 2012

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

connected to target database: OMRDB (DBID=1430679315)

RMAN> 

RMAN> backup database;
Starting backup at 26-MAY-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=30 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001
name=/u01/app/oracle/oradata/omrdb/system01.dbf
input datafile file number=00002
name=/u01/app/oracle/oradata/omrdb/sysaux01.dbf
input datafile file number=00003
name=/u01/app/oracle/oradata/omrdb/undotbs01.dbf
input datafile file number=00004
name=/u01/app/oracle/oradata/omrdb/users01.dbf
channel ORA_DISK_1: starting piece 1 at 26-MAY-12
channel ORA_DISK_1: finished piece 1 at 26-MAY-12
piece handle=/u01/app/oracle/flash_recovery_area/OMRDB/backupset/2012_05_26/o
1_mf_nnndf_TAG20120526T081552_7w0sr8g2_.bkp tag=TAG20120526T081552
comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:04:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 26-MAY-12
channel ORA_DISK_1: finished piece 1 at 26-MAY-12
piece handle=/u01/app/oracle/flash_recovery_area/OMRDB/backupset/2012_05_26/o
1_mf_ncnnf_TAG20120526T081552_7w0szwnd_.bkp tag=TAG20120526T081552
comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 26-MAY-12

RMAN> backup archivelog all;
Starting backup at 26-MAY-12
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=5 RECID=1 STAMP=784282821
input archived log thread=1 sequence=6 RECID=2 STAMP=784282833
input archived log thread=1 sequence=7 RECID=3 STAMP=784282835
input archived log thread=1 sequence=8 RECID=4 STAMP=784282835
input archived log thread=1 sequence=9 RECID=5 STAMP=784282842
input archived log thread=1 sequence=10 RECID=6 STAMP=784282843
input archived log thread=1 sequence=11 RECID=7 STAMP=784282850
channel ORA_DISK_1: starting piece 1 at 26-MAY-12
channel ORA_DISK_1: finished piece 1 at 26-MAY-12
piece handle=/u01/app/oracle/flash_recovery_area/OMRDB/backupset/2012_05_26/o
1_mf_annnn_TAG20120526T082050_7w0t1m34_.bkp tag=TAG20120526T082050
comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 26-MAY-12
RMAN> validate recovery area;
Starting validate at 26-MAY-12
using channel ORA_DISK_1
specification does not match any datafile copy in the repository
channel ORA_DISK_1: starting validation of archived log
channel ORA_DISK_1: specifying archived log(s) for validation
input archived log thread=1 sequence=5 RECID=1 STAMP=784282821
input archived log thread=1 sequence=6 RECID=2 STAMP=784282833
input archived log thread=1 sequence=7 RECID=3 STAMP=784282835
input archived log thread=1 sequence=8 RECID=4 STAMP=784282835
input archived log thread=1 sequence=9 RECID=5 STAMP=784282842
input archived log thread=1 sequence=10 RECID=6 STAMP=784282843
input archived log thread=1 sequence=11 RECID=7 STAMP=784282850
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Archived Logs
=====================
Thrd Seq     Status Blocks Failing Blocks Examined Name
---- ------- ------ -------------- --------------- ---------------
1    5       OK     0              1960          
/u01/app/oracle/flash_recovery_area/OMRDB/archivelog/2012_05_26/o1_mf_1_5_7w0t0og9_.arc
1    6       OK     0              1              
/u01/app/oracle/flash_recovery_area/OMRDB/archivelog/2012_05_26/o1_mf_1_6_7w0t11bd_.arc
1    7       OK     0              4              
/u01/app/oracle/flash_recovery_area/OMRDB/archivelog/2012_05_26/o1_mf_1_7_7w0t12yb_.arc
1    8       OK     0              1              
/u01/app/oracle/flash_recovery_area/OMRDB/archivelog/2012_05_26/o1_mf_1_8_7w0t12z1_.arc
1    9       OK     0              1              
/u01/app/oracle/flash_recovery_area/OMRDB/archivelog/2012_05_26/o1_mf_1_9_7w0t1b0w_.arc
1    10      OK     0              1              
/u01/app/oracle/flash_recovery_area/OMRDB/archivelog/2012_05_26/o1_mf_1_10_7w0t1c1k_.arc
1    11      OK     0              6              
/u01/app/oracle/flash_recovery_area/OMRDB/archivelog/2012_05_26/o1_mf_1_11_7w0t1lts_.arc
channel ORA_DISK_1: input backup set: count=2, stamp=784282460, piece=1
channel ORA_DISK_1: starting piece 1 at 26-MAY-12
channel ORA_DISK_1: backup piece
/u01/app/oracle/flash_recovery_area/OMRDB/backupset/2012_05_26/o1_mf_nc
nnf_TAG20120526T081419_7w0sohnq_.bkp
piece handle=/u01/app/oracle/flash_recovery_area/OMRDB/backupset/2012_05_26/o
1_mf_ncnnf_TAG20120526T081419_7w0t24o2_.bkp comment=NONE
channel ORA_DISK_1: finished piece 1 at 26-MAY-12
channel ORA_DISK_1: backup piece complete, elapsed time: 00:00:03
channel ORA_DISK_1: input backup set: count=3, stamp=784282552, piece=1
channel ORA_DISK_1: starting piece 1 at 26-MAY-12
channel ORA_DISK_1: backup piece
/u01/app/oracle/flash_recovery_area/OMRDB/backupset/2012_05_26/o1_mf_nn
ndf_TAG20120526T081552_7w0sr8g2_.bkp
piece handle=/u01/app/oracle/flash_recovery_area/OMRDB/backupset/2012_05_26/o
1_mf_nnndf_TAG20120526T081552_7w0t27p2_.bkp comment=NONE
channel ORA_DISK_1: finished piece 1 at 26-MAY-12
channel ORA_DISK_1: backup piece complete, elapsed time: 00:01:45
channel ORA_DISK_1: input backup set: count=4, stamp=784282793, piece=1
channel ORA_DISK_1: starting piece 1 at 26-MAY-12
channel ORA_DISK_1: backup piece
/u01/app/oracle/flash_recovery_area/OMRDB/backupset/2012_05_26/o1_mf_nc
nnf_TAG20120526T081552_7w0szwnd_.bkp
piece handle=/u01/app/oracle/flash_recovery_area/OMRDB/backupset/2012_05_26/o
1_mf_ncnnf_TAG20120526T081552_7w0t5jvw_.bkp comment=NONE
channel ORA_DISK_1: finished piece 1 at 26-MAY-12
channel ORA_DISK_1: backup piece complete, elapsed time: 00:00:03
channel ORA_DISK_1: input backup set: count=5, stamp=784282821, piece=1
channel ORA_DISK_1: starting piece 1 at 26-MAY-12
channel ORA_DISK_1: backup piece
/u01/app/oracle/flash_recovery_area/OMRDB/backupset/2012_05_26/o1_mf_an
nnn_TAG20120526T082021_7w0t0p12_.bkp
piece handle=/u01/app/oracle/flash_recovery_area/OMRDB/backupset/2012_05_26/o
1_mf_annnn_TAG20120526T082021_7w0t5mvx_.bkp comment=NONE
channel ORA_DISK_1: finished piece 1 at 26-MAY-12
channel ORA_DISK_1: backup piece complete, elapsed time: 00:00:01
channel ORA_DISK_1: input backup set: count=6, stamp=784282850, piece=1
channel ORA_DISK_1: starting piece 1 at 26-MAY-12
channel ORA_DISK_1: backup piece
/u01/app/oracle/flash_recovery_area/OMRDB/backupset/2012_05_26/o1_mf_an
nnn_TAG20120526T082050_7w0t1m34_.bkp
piece handle=/u01/app/oracle/flash_recovery_area/OMRDB/backupset/2012_05_26/o
1_mf_annnn_TAG20120526T082050_7w0t5o0h_.bkp comment=NONE
channel ORA_DISK_1: finished piece 1 at 26-MAY-12
channel ORA_DISK_1: backup piece complete, elapsed time: 00:00:01
Finished validate at 26-MAY-12

-- Validate database

RMAN> restore database validate;
Starting restore at 26-MAY-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile backup set
channel ORA_DISK_1: reading from backup piece
/u01/app/oracle/flash_recovery_area/OMRDB/backupset/2012_05_26/o1_mf_nn
ndf_TAG20120526T083547_7w0txmg6_.bkp
channel ORA_DISK_1: piece
handle=/u01/app/oracle/flash_recovery_area/OMRDB/backupset/2012_05_26/o
1_mf_nnndf_TAG20120526T083547_7w0txmg6_.bkp tag=TAG20120526T083547
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:15
Finished restore at 26-MAY-12

-- Validate Control file 

RMAN> restore controlfile validate;
Starting restore at 26-MAY-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile backup set
channel ORA_DISK_1: reading from backup piece
/u01/app/oracle/flash_recovery_area/OMRDB/autobackup/2012_05_26/o1_mf_n
_784283872_7w0v1k3f_.bkp
channel ORA_DISK_1: piece
handle=/u01/app/oracle/flash_recovery_area/OMRDB/autobackup/2012_05_26/
o1_mf_n_784283872_7w0v1k3f_.bkp tag=TAG20120526T083752
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:00
Finished restore at 26-MAY-12

-- Validate Spfile 

RMAN> restore spfile validate;

Starting restore at 26-MAY-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile backup set
channel ORA_DISK_1: reading from backup piece
/u01/app/oracle/flash_recovery_area/OMRDB/autobackup/2012_05_26/o1_mf_s
_784296870_7w17qqmw_.bkp
channel ORA_DISK_1: piece
handle=/u01/app/oracle/flash_recovery_area/OMRDB/autobackup/2012_05_26/
o1_mf_s_784296870_7w17qqmw_.bkp tag=TAG20120526T121430
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:00
Finished restore at 26-MAY-12

-- Validate archive logs

RMAN> restore archivelog from time 'SYSDATE-1' validate;
Starting restore at 26-MAY-12
using channel ORA_DISK_1
channel ORA_DISK_1: scanning archived log
/u01/app/oracle/flash_recovery_area/OMRDB/archivelog/2012_05_26/o1_mf_1
_5_7w0t0og9_.arc
channel ORA_DISK_1: scanning archived log
/u01/app/oracle/flash_recovery_area/OMRDB/archivelog/2012_05_26/o1_mf_1
_6_7w0t11bd_.arc
channel ORA_DISK_1: scanning archived log
/u01/app/oracle/flash_recovery_area/OMRDB/archivelog/2012_05_26/o1_mf_1
_7_7w0t12yb_.arc
channel ORA_DISK_1: scanning archived log
/u01/app/oracle/flash_recovery_area/OMRDB/archivelog/2012_05_26/o1_mf_1
_8_7w0t12z1_.arc
channel ORA_DISK_1: scanning archived log
/u01/app/oracle/flash_recovery_area/OMRDB/archivelog/2012_05_26/o1_mf_1
_9_7w0t1b0w_.arc
channel ORA_DISK_1: scanning archived log
/u01/app/oracle/flash_recovery_area/OMRDB/archivelog/2012_05_26/o1_mf_1
_10_7w0t1c1k_.arc
channel ORA_DISK_1: scanning archived log
/u01/app/oracle/flash_recovery_area/OMRDB/archivelog/2012_05_26/o1_mf_1
_11_7w0t1lts_.arc
channel ORA_DISK_1: scanning archived log
/u01/app/oracle/flash_recovery_area/OMRDB/archivelog/2012_05_26/o1_mf_1
_12_7w0wd3pf_.arc
Finished restore at 26-MAY-12

-- Validate USERS tablespace

RMAN> restore tablespace users validate;

Starting restore at 26-MAY-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile backup set
channel ORA_DISK_1: reading from backup piece
/u01/app/oracle/flash_recovery_area/OMRDB/backupset/2012_05_26/o1_mf_nn
ndf_TAG20120526T083547_7w0txmg6_.bkp
channel ORA_DISK_1: piece
handle=/u01/app/oracle/flash_recovery_area/OMRDB/backupset/2012_05_26/o
1_mf_nnndf_TAG20120526T083547_7w0txmg6_.bkp tag=TAG20120526T083547
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
Finished restore at 26-MAY-12


-- Validate backup sets

RMAN> list backup summary;

List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
1       B  F  A DISK        26-MAY-12       1       2       NO         TAG20120526T081419
2       B  F  A DISK        26-MAY-12       1       2       NO         TAG20120526T081552
3       B  F  A DISK        26-MAY-12       1       2       NO         TAG20120526T081552
4       B  A  A DISK        26-MAY-12       1       2       NO         TAG20120526T082021
5       B  A  A DISK        26-MAY-12       1       2       NO         TAG20120526T082050

RMAN> validate backupset 1,2,3,4,5 ;
Starting validate at 26-MAY-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile backup set
channel ORA_DISK_1: reading from backup piece
/u01/app/oracle/flash_recovery_area/OMRDB/backupset/2012_05_26/o1_mf_nc
nnf_TAG20120526T081419_7w0t24o2_.bkp
channel ORA_DISK_1: piece
handle=/u01/app/oracle/flash_recovery_area/OMRDB/backupset/2012_05_26/o
1_mf_ncnnf_TAG20120526T081419_7w0t24o2_.bkp tag=TAG20120526T081419
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting validation of datafile backup set
channel ORA_DISK_1: reading from backup piece
/u01/app/oracle/flash_recovery_area/OMRDB/backupset/2012_05_26/o1_mf_nn
ndf_TAG20120526T081552_7w0sr8g2_.bkp
channel ORA_DISK_1: piece
handle=/u01/app/oracle/flash_recovery_area/OMRDB/backupset/2012_05_26/o
1_mf_nnndf_TAG20120526T081552_7w0sr8g2_.bkp tag=TAG20120526T081552
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting validation of datafile backup set
channel ORA_DISK_1: reading from backup piece
/u01/app/oracle/flash_recovery_area/OMRDB/backupset/2012_05_26/o1_mf_nc
nnf_TAG20120526T081552_7w0t5jvw_.bkp
channel ORA_DISK_1: piece
handle=/u01/app/oracle/flash_recovery_area/OMRDB/backupset/2012_05_26/o
1_mf_ncnnf_TAG20120526T081552_7w0t5jvw_.bkp tag=TAG20120526T081552
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:00
channel ORA_DISK_1: starting validation of archived log backup set
channel ORA_DISK_1: reading from backup piece
/u01/app/oracle/flash_recovery_area/OMRDB/backupset/2012_05_26/o1_mf_an
nnn_TAG20120526T082021_7w0t0p12_.bkp
channel ORA_DISK_1: piece
handle=/u01/app/oracle/flash_recovery_area/OMRDB/backupset/2012_05_26/o
1_mf_annnn_TAG20120526T082021_7w0t0p12_.bkp tag=TAG20120526T082021
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:00
channel ORA_DISK_1: starting validation of archived log backup set
channel ORA_DISK_1: reading from backup piece
/u01/app/oracle/flash_recovery_area/OMRDB/backupset/2012_05_26/o1_mf_an
nnn_TAG20120526T082050_7w0t1m34_.bkp
channel ORA_DISK_1: piece
handle=/u01/app/oracle/flash_recovery_area/OMRDB/backupset/2012_05_26/o
1_mf_annnn_TAG20120526T082050_7w0t1m34_.bkp tag=TAG20120526T082050
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:00
Finished validate at 26-MAY-12



-- Validate recovery area

RMAN> validate recovery area;

Starting validate at 26-MAY-12

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=33 device type=DISK

specification does not match any datafile copy in the repository

channel ORA_DISK_1: starting validation of archived log

channel ORA_DISK_1: specifying archived log(s) for validation

input archived log thread=1 sequence=12 RECID=8 STAMP=784285238

channel ORA_DISK_1: validation complete, elapsed time: 00:00:01

List of Archived Logs

=====================

Thrd Seq     Status Blocks Failing Blocks Examined Name

---- ------- ------ -------------- --------------- ---------------

1    12      OK     0              68339           /u01/app/oracle/flash_recovery_area/OMRDB/archivelog/2012_05_26/o1_mf_1_12_7w0wd3pf_.arc

channel ORA_DISK_1: input backup set: count=8, stamp=784283747, piece=1

channel ORA_DISK_1: starting piece 1 at 26-MAY-12

channel ORA_DISK_1: backup piece /u01/app/oracle/flash_recovery_area/OMRDB/backupset/2012_05_26/o1_mf_nnndf_TAG20120526T083547_7w0txmg6_.bkp

piece handle=/u01/app/oracle/flash_recovery_area/OMRDB/backupset/2012_05_26/o1_mf_nnndf_TAG20120526T083547_7w190t2j_.bkp comment=NONE

channel ORA_DISK_1: finished piece 1 at 26-MAY-12

channel ORA_DISK_1: backup piece complete, elapsed time: 00:00:35

channel ORA_DISK_1: input backup set: count=11, stamp=784296870, piece=1

channel ORA_DISK_1: starting piece 1 at 26-MAY-12

skipping backup piece handle /u01/app/oracle/flash_recovery_area/OMRDB/autobackup/2012_05_26/o1_mf_s_784296870_7w17qqmw_.bkp; already exists

channel ORA_DISK_1: finished piece 1 at 26-MAY-12

channel ORA_DISK_1: backup piece complete, elapsed time: 00:00:00

Finished validate at 26-MAY-12

RMAN> 

ORA-27154: post/wait create failed

ORA-27154: post/wait create failed
ORA-27300: OS system dependent operation:semget failed with status: 28
ORA-27301: OS failure message: No space left on device
ORA-27302: failure occurred at: sskgpsemsper

Correcting the kernel setting in sysctl.conf should resolve the issue :
as root:

[root@omr ~]# vi /etc/sysctl.conf

kernel.sem 250 3200 100 256

[root@omr ~]# sysctl -p

ORA-01994: GRANT failed: password file missing or disabled

ORA-01994: GRANT failed: password file missing or disabled

Reason:
The oracle software owner is not the owner of the passwordfile.

Solutions:
1)Log on to Unix box as the same user who owns the file $ORACLE_HOME
and create the password file as follows.

$orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=password entries=5 force=y

2)Now grant sysdba privilege to the users that you need. Check V$PWFILE_USERS for the entry.
SQL>GRANT SYSDBA to SYSTEM;

3)Check the owner of $ORACLE_HOME/dbs/orapw$ORACLE_SID verify the password file,

SQL> !ls -l $ORACLE_HOME/dbs/orapw$ORACLE_SID
-rw-r----- 1 oracle oinstall 1536 May 25 13:31
/app/oracle/product/10.2.0/db_1/dbs/orapworcl1

4)If it is not to dba then change the ownership by
$chown oracle:dba $ORACLE_HOME/dbs/orapw$ORACLE_SID

Also change permission by,
$chmod 4640 $ORACLE_HOME/dbs/orapw$ORACLE_SID

Reference: Metalink

Oracle 10g Upgrade/Downgrade

Upgrading 10g to 11g

Pre-work for upgrade:
- Install 11g Home
- Run pre-upgrade script in the target database
- Review and complete recommendations made by pre-upgrade script
- Create a text copy of spfile and make necessary changes as recommended
- Please do not change COMPATIBLE parameter value to 11.2.0 if you have a plan to downgrade back to 10.2.0

After completing all the necessary recommendations to the existing database follow the steps:

Step 1. Compile all invalid objects in 11g
       SQL>@?/rdbms/admin/utlrp.sql
keep a list of INVALID objects to compare after upgrade INVALID objects.

Step 2. Stop dbconsole
      SQL>emctl stop dbconsole

Setp 3. Shutdown all the servies (Instance, listener, dbconsole) of the target database.
[oracle@prod db_1]$ sqlplus / as sysdba
SQL> Shutdown immediate
[oracle@prod db_1]$lsnrctl stop
[oracle@prod db_1]$emctl stop dbconsole

Step 4. On Unix/Linux environment, ensure that sid in Oratab is pointing to the 11g Oracle Home. Also, comment out the previous oratab  entry for this database, it might be required in case of downgrade.

#prod:/u01/app/oracle/product/10.2.0/db_1:Y
prod:/u01/app/oracle/product/11.2.0/db_1:Y

Source in the database again and change directory to verify the current

Oracle Home:
[oracle@prod db_1]$ . oraenv
ORACLE_SID = [prod] ?

[oracle@prod db_1]$ cd $ORACLE_HOME
[oracle@prod db_1]cd rdbms\admin

In Windows Environment

C:\> NET STOP OracleServicePROD
C:\> ORADIM -DELETE -SID PROD
C:\> SET ORACLE_HOME=E:\oracle\product\10.2.0\db_1
C:\> ORADIM -DELETE -SID PROD
C:\> ORADIM -NEW -SID PROD -INTPWD XXXXXXX -MAXUSERS USERS -
STARTMODE AUTO -PFILE %ORACLE_HOME%\DATABASE\INITPROD.ORA

Setp 4.
        SQL> startup upgrade
        SQL> spool upgrade.txt
        SQL> @catupgrd
        SQL> SPOOL OFF
        SQL> shutdown immediate

Step 5. Spool off and check upgrade.txt file for possible errors during upgrade.

Step 6. Bounce the database after successful upgrade.
SQL>startup

Step 7. compile the invalid objects by executing utlrp.sql
SQL>@utlrp.sql

Step 9.Shut down and restart the instance
     SQL> shutdown immediate
     SQL> startup

Step 10. Verify the component name, version and status of each component, it should show like below:
SQL> select comp_name,version, status from dba_registry;

COMP_NAME                                    VERSION    STATUS

-----------------------                               -------------- -----------

OLAP Catalog                                     11.2.0.3.0     VALID

Spatial                                                11.2.0.3.0     VALID

Oracle interMedia                                11.2.0.3.0     VALID

Oracle XML Database                         11.2.0.3.0     VALID

Oracle Text                                         11.2.0.3.0     VALID

Oracle Data Mining                             11.2.0.3.0     VALID

Oracle Expression Filter                       11.2.0.3.0     VALID

Oracle Rule Manager                           11.2.0.3.0     VALID

Oracle Workspace Manager                 11.2.0.3.0     VALID

Oracle Database Catalog Views            11.2.0.3.0     VALID

Oracle Database Packages and Types   11.2.0.3.0     VALID

JServer JAVA Virtual Machine             11.2.0.3.0     VALID

Oracle XDK                                       11.2.0.3.0     VALID

Oracle Database Java Packages            11.2.0.3.0     VALID

OLAP Analytic Workspace                  11.2.0.3.0     VALID

Oracle OLAP API                               11.2.0.3.0     VALID

16 rows selected.

Issues faced during 10.2.0.4 to 11.2.0.3 upgrade


Issue No 1.
The upgrade shows error altering DBMS_SQLTUNE_INTERNAL and/or DBMS_SQLTUNE package :

LINE/COL ERROR
-------        ----------------------------------------------------------
6370/5       PL/SQL: SQL Statement ignored
6376/18     PL/SQL: ORA-00980: synonym translation is no longer valid

Reason:Public synonym of XMLCONCAT exists. The package depends on this invalid synonym.

Workaround :

Step 1. Drop the synonym:
SQL> drop public synonym XMLCONCAT;

Step 2. Recompile the package

SQL> alter package DBMS_SQLTUNE_INTERNAL compile body;
or
SQL> alter package DBMS_SQLTUNE compile body;

Step 3. Run utlrp.sql to recompile the other INVALID objects

Issue No 2. 

ORA-39714: upgrade script utlmmig.sql failed

Reference: Metalink Doc Id 761961.1
Reason : A normal database open was attempted, but the upgrade script utlmmig.sql failed to complete.

Workaround:
Step 1. SQL>STARTUP UPGRADE
Step 2. SQL>@utlmmig.sql

The script will execute and will shutdown the database.
Step 3. Start the database
     SQL>STARTUP

The database will start without any issues.

Downgrading database to 10g from 11g

Step 1. Compile all invalid objects in 11g
       SQL>@?/rdbms/admin/utlrp.sql

Step 2. stop dbconsole
     [oracle@prod db_1]$emctl stop dbconsole

Setp 3. SQL> shutdown immediate

Step 4. Startup database in upgrade mode and drop SYSMAN user and run the downgrade script
        SQL> startup downgrade
        SQL> drop user sysman cascade;
        SQL> spool downgrade.txt
        SQL> @catdwgrd
        SQL> spool off
        SQL> shutdown immediate

Step 5. On Unix/Linux, ensure that sid in Oratab is pointing to the 10g Oracle Home.
prod:/u01/app/oracle/product/10.2.0/db_1:Y
#prod:/u01/app/oracle/product/11.2.0/db_1:N

on Windows
       C:\> NET STOP OracleServicePROD

Change directory to Oracle 10g Home

       C:\> ORADIM -DELETE -SID PROD
       C:\> ORADIM -NEW -SID PROD -INTPWD XXXXXXX -MAXUSERS USERS -
STARTMODE AUTO -PFILE %ORACLE_HOME%\DATABASE\INITPROD.ORA

Step 6.  Change directory to 10g Oracle Home/rdbms/admin and start database in upgrade mode

SQL> startup upgrade
SQL> spool reload.log
SQL> @catrelod.sql

Step 7. Turn off the spooling
     SQL> spool off

Step 8. Check the spool file to verify that the packages and procedures compiled successfully.

Step 9.Shut down and restart the instance
     SQL> shutdown immediate
     SQL> startup

Step 10. Verify the component name, version and status of each component, it should show like below:
SQL> select comp_name,version,status from dba_registry;

COMP_NAME                                  VERSION     STATUS
---------------------------------         -------------- -----------
OLAP Catalog                                   10.2.0.4.0       VALID
Spatial                                                10.2.0.4.0       VALID
Oracle interMedia                               10.2.0.4.0       VALID
Oracle XML Database                        10.2.0.4.0      VALID
Oracle Text                                         10.2.0.4.0      VALID
Oracle Data Mining                             10.2.0.4.0      VALID
Oracle Expression Filter                      10.2.0.4.0      VALID
Oracle Rule Manager                          10.2.0.4.0      VALID
Oracle Workspace Manager               10.2.0.4.3      VALID
Oracle Database Catalog Views          10.2.0.4.0      VALID
Oracle Database Packages and Types 10.2.0.4.0      VALID
JServer JAVA Virtual Machine            10.2.0.4.0      VALID
Oracle XDK                                        10.2.0.4.0      VALID
Oracle Database Java Packages           10.2.0.4.0      VALID
OLAP Analytic Workspace                  10.2.0.4.0     VALID
Oracle OLAP API                               10.2.0.4.0     VALID

16 rows selected.

Step 11.  Start the listener
[oracle@prod db_1]$lsnrctl start

Step 12. Recreate the dbconsole
[oracle@prod db_1]$ emca -config dbcontrol db -repose create

ORA-16607: one or more databases have failed

DGMGRL> connect sys@db1db
Password:
Connected.
DGMGRL>
DGMGRL> show configuration

Configuration
  Name:                db1db
  Enabled:             YES
  Protection Mode:     MaxAvailability
  Fast-Start Failover: DISABLED
  Databases:
    dg1db - Physical standby database
    db1db  - Primary database

Current status for "db1db":
Warning: ORA-16607: one or more databases have failed

Reason:

Data Guard broker detected a failure for one or more databases in the Data Guard configuration.

Workaround:

Shutdown standby database, recreate SPFILE and mount standby database again.


SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> create spfile from pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initdg1db.ora';

File created.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area  536870912 bytes

Fixed Size                  1220460 bytes

Variable Size             155189396 bytes

Database Buffers          377487360 bytes

Redo Buffers                2973696 bytes

SQL> alter database mount standby database;

Database altered.

DGMGRL> show configuration;

Configuration
  Name:                db1db
  Enabled:             YES
  Protection Mode:     MaxAvailability
  Fast-Start Failover: DISABLED
  Databases:
    dg1db  - Physical standby database
    db1db  - Primary database

Current status for "db1db":
SUCCESS

Changing Segment Space Management to Auto from Manual

Change segment space management to AUTO from MANUAL for USERS tablespace:


Step 1. Create a new tablespace users1 

SQL> create tablespace users1
  2  datafile '/u01/app/oracle/oradata/disk3/db1/users1.dbf'
  3  size 500M autoextend on
  4  extent management local
  5  segment space management auto;

Tablespace created.

SQL> select * from dba_tablespaces where tablespace_name='USERS1';

TABLESPACE_NAME                BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
------------------------------ ---------- -------------- ----------- -----------
MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS    CONTENTS  LOGGING   FOR EXTENT_MAN
----------- ------------ ---------- --------- --------- --------- --- ----------
ALLOCATIO PLU SEGMEN DEF_TAB_ RETENTION   BIG
--------- --- ------ -------- ----------- ---
USERS1                               8192          65536                       1
 2147483645                   65536 ONLINE    PERMANENT LOGGING   NO  LOCAL
SYSTEM    NO  AUTO   DISABLED NOT APPLY   NO

Step 2. Identify the objects belonging to USERS tablespace:


SQL> select owner,segment_name from dba_segments where tablespace_name='USERS';

OWNER                          SEGMENT_NAME
------------------------------ ------------------------------
SYS                            REPAIR_TABLE
SCOTT                          PK_DEPT
SCOTT                          PK_EMP
SCOTT                          BONUS
SCOTT                          SALGRADE
ROHAN                          T1
SCOTT                          T1
SYED                           T1

8 rows selected.

Step 3. Move objects from USERS tablespace to USERS1 tablespace
(we can also generate a single script and execute to move the objects in one go, but 
since we have very few objects we can move objects one by one):

SQL> alter table scott.dept move tablespace users1;

Table altered.

SQL> alter table scott.emp move tablespace users1;

Table altered.

SQL> alter table scott.bonus move tablespace users1;

Table altered.

SQL> alter table scott.t1 move tablespace users1;

Table altered.

SQL> alter table syed.t1 move tablespace users1;

Table altered.

SQL> alter table rohan.t1 move tablespace users1;

Table altered.

SQL> alter table scott.salgrade move tablespace users1;

Table altered.

SQL> alter table sys.repair_table move tablespace users1;

Table altered.

Step 4. Identify index segment on USERS tablespace and rebuild on USERS1 tablespace:

SQL> select owner,segment_type,segment_name from dba_segments
  2  where segment_type='INDEX' and tablespace_name='USERS';

OWNER                          SEGMENT_TYPE       SEGMENT_NAME
------------------------------ ------------------ ------------------------------
SCOTT                          INDEX              PK_DEPT
SCOTT                          INDEX              PK_EMP

SQL> alter index scott.pk_dept rebuild tablespace users1;

Index altered.

SQL> alter index scott.pk_emp rebuild tablespace users1;

Index altered.

Step 5. Verify all objects have been moved to USERS1 tablespace:

SQL> select count(*) from dba_segments where tablespace_name='USERS';

  COUNT(*)
----------
         0

Step 6. Drop tablespace USERS :

SQL> drop tablespace users including contents;
drop tablespace users including contents
*
ERROR at line 1:
ORA-12919: Can not drop the default permanent tablespace

Since USERS tablespace is the default tablespace, we cannot drop it.

First we have to make USERS1 as default tablespace:

SQL> alter database default tablespace users1;

Database altered.

Now, we can drop USESR tablespace:

SQL> drop tablespace users including contents;

Tablespace dropped.

Step 7. Rename USERS1 tablespace to USERS:

SQL> alter tablespace users1 rename to users;

Tablespace altered.

Now, verify tablespace has been renamed:

SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
UNDOTBS01
SYSAUX
TEMP
RCAT_TBS
USERS

6 rows selected.

Which datafile contains my table??

SQL> connect syed/syed
Connected.
SQL> create table t1(c1 number, c2 varchar2(200));

Table created.

SQL> begin
  2  for i in 1..2400
  3  loop
  4  insert into t1 values(i,'test data');
  5  end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> /

PL/SQL procedure successfully completed.

SQL> /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL>connect / as sysdba

SQL>select e.segment_name,e.partition_name,e.segment_type,d.file_name
from dba_extents e,dba_data_files d
where (e.file_id,e.block_id) in (select file_id,max(block_id) from dba_extents where
segment_name='T1' group by file_id)
 and e.file_id=d.file_id
and e.owner='SYED'
and e.segment_name='T1' ;

SQL>SEGMENT_NA SEGMENT_TYPE       FILE_NAME
---------- ------------------ --------------------------------------------------
T1         TABLE              /u01/app/oracle/oradata/disk3/db1/users02.dbf
T1         TABLE              /u01/app/oracle/oradata/disk3/db1/users01.dbf