Saturday, May 26, 2012

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> 



Friday, May 25, 2012

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

Saturday, May 12, 2012

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

Friday, May 11, 2012

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

Saturday, April 28, 2012

How to solve ORA-00354: corrupt redo log block header


How to solve ORA-00354: corrupt redo log block header 

To resolve ORA-00354 :

SQL>alter system checkpoint;
SQL>alter system switch logfile;
SQL>alter database backup controlfile to trace;
SQL>alter system drop logfile group n;

If the above command fail with some archiving issue then use the below mentioned command

SQL>alter database clear unarchived logfile group <n>;
SQL>alter database drop logfile group <n>;
SQL>alter database add logfile group n ('/dev/................') size nnnnM reuse;

To check the status
SQL>select * from v$logfile;

It should show unused as the status of the recently added log file
Now, switch log file 3-4 times and check the v$logfile again
SQL>alter system switch logfile;  
SQL>select * from v$logfile;

Privileges assigned to roles and available to the currently logged in user

Data Dictionary Views Describing Roles


Privileges assigned to roles and available to the currently logged in user

SQL> desc ROLE_SYS_PRIVS;


SQL> connect scott/tiger
Connected.

SQL> select * from ROLE_SYS_PRIVS;

ROLE                           PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
RESOURCE                       CREATE TRIGGER                           NO
RESOURCE                       CREATE SEQUENCE                          NO
RESOURCE                       CREATE TYPE                              NO
RESOURCE                       CREATE PROCEDURE                         NO
RESOURCE                       CREATE CLUSTER                           NO
CONNECT                        CREATE SESSION                           NO
RESOURCE                       CREATE OPERATOR                          NO
RESOURCE                       CREATE INDEXTYPE                         NO
RESOURCE                       CREATE TABLE                             NO

9 rows selected.



Delete duplicate rows


DELETE FROM <table> A WHERE ROWID > (
SELECT min(rowid) FROM <table> B
WHERE A.key_column = B.key_column);

Friday, April 27, 2012

Oracle 11g Home Cloning


Oracle 11g Home Cloning
1- Shutdown the database and other services of the source database
2- Make a zip file of the source Oracle home (only home folder like DB_1, or DBHOME_1)
3- Copy it over to the target machine
4- Unzip the zipped file
5- Go to the $ORACLE_HOME/oui/bin directory
6- From the $ORACLE_HOME/oui/bin directory, run Oracle Universal Installer in clone mode for the unzipped Oracle home. Use the following syntax:
$ORACLE_HOME/oui/bin/runInstaller -silent -clone ORACLE_BASE="/app/administrator" ORACLE_HOME="/app/administrator/product/11.2.0/dbhome_2" ORACLE_HOME_NAME="dbhome_2"

It will clone the Oracle home, check Oracle Inventory to verify.

Archival Backup


Archival Backup

Archival backup is a new RMAN feature in Oracle 11g, which allows to create a backup that will not be affected by the normal retention policy defined for backups taken using RMAN.

-- This backup consists of every datafile, controlfile, server parameter file needed for restore and recovery in a single disk or tape
-- It also automatically generates archive log backup to ensure the consistency of backup
-- Automatic archive log backup ensures database can be restored and recoverd to a consistent state.
-- Overrides the existing backup retention policy.
-- These backups can be kept for upto a certain date or forever.
-- The existing full database backup can be modified to be made Archival backup by simple commands.
-- This can be implemented by using KEEP FOREVER caluse (requires a recovery catalog) or KEEP UNTIL TIME caluse of backup command.
-- It will not be listed as obsolete through RMAN reporting commands.
-- Purpose of these kinds of backup could be business policy or legal.
-- The backup should be in non_FRA area, Flash Recovery Area cannot be used to keep the backup because retention policy applies to FRA. It will give ORA-19811 error

Such backups once created may be exempted from the normal backup and recovery strategy.

These backups may be created on separate backup media other than the flash recovery area to be retained for a long time. These kinds of backups may require to be maintained for business or legal requirements. They will not be displayed when you view the obsolete backups based on your retention policy.

This backup is "all-inclusive" and every datafile of the database, control file, server parameter file needed for restore and recovery is backed up to a single disk or tape device. It also automatically generates an archive redo log backup to ensue that the database backup can be recovered to a consistent state. Typically these backups are done to tape.

An archival backup are created with the BACKUP…KEEP command. The KEEP option indicates that the backupset should be exempt from the retention policy either forever (KEEP FOREVER clause) or for a specified time period (KEEP UNTIL TIME). The KEEP FOREVER clause requires the use of a recovery catalog database.

The RMAN command for archival backup:

KEEP {FOREVER| UNTIL TIME 'SYSDATE +n'}
         RESTORE POINT restore_point_name

where the restore point is a user-defined name associated with an SCN of the database corresponding to the time of the creation of the restore point. The restore point would act as a label for the SCN to which the backup must be recovered to be made consistent. The restore point option is not allowed with the CHANGE…KEEP command described later.

Example:

RMAN> BACKUP TAG FORBKP
           DATABASE KEEP FOREVER;

RMAN>BACKUP DATABASE
          FORMAT '<path>/%U.bkp'
          KEEP UNTIL TIME 'sysdate + 365';

The CHANGE command and archival backups :

The CHANGE... NOKEEP may be used to remove the KEEP attributes of a backup, making it subject to the backup retention policy.

The CHANGE…KEEP command may be used to change a consistent backup into an archival backup.

Example:

RMAN> CHANGE BACKUP TAG 'fulldb_backup' KEEP FOREVER;

Archival Backups may be created using Enterprise Manager:

Database Control Home Page
-> Availability tab
-> Schedule Backup
-> Schedule Customized Backup (Wizard)
-> Setting Page
-> Override Current Settings Button
-> Policy
-> Over Retention Policy Section
-> Select to keep a backup for a specified number of days.

Server Control Utility (SRVCTL)


Server Control Utility (SRVCTL) 

Login as oracle user to Linux

oracle@rac1$

Start and Stop database:

srvctl stop database -d racdb
srvctl start database -d racdb

Start and Stop instance

srvctl stop instance -d racdb -i racdb2
srvctl start instance -d racdb1 -i racdb2

start and Stop ASM

srvctl stop asm -n rac2
srvctl start asm -n rac2

Start and Stop Listener

srvctl stop listener -n rac2
srvctl start listener -n rac2

Start and Stop nodeapps

srvctl stop nodeapps -n rac2
srvctl start nodeapps -n rac2

How to add service using srvctl

srvctl add service -s TEST1 -d racdb -r racdb1 -a racdb2 -r preferred -a available

srvctl start service -s TEST1 -d racdb

this step is to add TNS entry for the service created through srvctl

srvctl config database -d racdb -t

How to modiy the service

srvctl config service -s TEST1 -d racdb

Make racdb2 also as preferred for service TEST1

srvctl config service -s TEST1 -d racdb -i racdb2 -r
srvctl config service -s TEST1 -d racdb

$srvctl stop/start/disable service -s TEST1 -d racdb -i racdb1

Changing between manual and auto

srvctl config database -d racdb -a
srvctl modify database -d racdb -y MANUAL

Oracle 11g Total Recall Feature


Oracle 11g Total Recall Feature

The Flashback Data Archive (FDA) feature makes it possible to automatically and transparently track all the changes to any set of tables, and to easily query data in those tables as of any point in time over any interval within the specified period with minimal performance impact.

FDA can be used in

- data forensic: find and revert changes made by a disgruntled employee
- retention policy enforcement: automatically purge history older than  years.
- Historical reporting: analyze product changes over time
- Error recovery: restore records that were erroneously removed or updated
- Employee fraud detection: find assets tht were deleted but never sold
- Tamper-proof history: retain a SecureFile file even when the original is destroyed.

Benefits:

Flash Data Archive overcomes the limitations inheritent in current apporach as historical data management. It automatically tracks every change made to FDA enabled data stored in the database, amd maintains a secure, efficient and easily accessible archive of historical data. The historical data can be returned as long as needed, and is easily accessible with standard SQL statements. Flashback Data Archive can be enabled on both existing and new tables instantaneously and in a completely application transparent manner.

- Application transparency :

Enabling FDA on one or more tables can be done instanttaneously with no application changes.

- Seamless Access

Applications can seamlessly query the historical data as it existed at any point in time or during any times interval within the retention period.

- Security :
Access to internal historical tables is restricted to read only. No DML is allowed on historical data for any users including administrators.

- Minimal performance overhead:
FDA employes a lightweight mechanism to mark DML operations on FDA enabled tables. The actual history generation and archiving is done asynchronously with background processes.

- Storage mechanism

Both compression and partitioning in FDA are managed automatically and require no special administration

- Centralized management:

we can create group of tables and set a common retention policy for each group. when a new table is added to a FDA, it automatically inherits the retention parameter from archive. The Oracle database automatically purges aged out history data for all the tracked tables based on the specified retention policy.

Flashback Data Archive requirements :

1- FDA tablespaces must be managed with automatic segment space mamanegment (ASSM).
2- Automatic Undo Management must be enabled

Implementation:

The are 3 steps process to FDA
1- Either create a new tablespace or use an existing tablespace( ASSM)

2- create a flashback data archive:
CREATE FLASHBACK ARCHIVE fda1
TABLESPACE fdatbs
RETENTION 5 YEARS;

This will create a new FDA calls fda1 on tablespace tbs1 with a retention period of 5 years, with an unlimited quota on the tablespace.

--FLASHBACK_ARCHIVE ADMINISTRATOR system privilege is required for creating a new flashback data archive.

--  FDA dictionary views

DBA/USER_FLASHBACK_ARCHIVE - Displays information about flashback data archives

DBA/USER_FLASHBACK_ARCHIVE_TS - Displays tablespaces and the mapping to flashback data archive

3. Enable flashback data architect on the desired table

ALTER TABLE EMPLOYEE FLASHBACK ARCHIVE fda1;

The employee table is now enabled for flashback data archive, and its history data will be stored in the flashback data archive fda1. when the first DML operation occurs, the internal history table is created in tbs1 tablespace.

DBA/USER_FLASHBACK_TABLES - views displays information about tables that are enabled for flashback data archive.

Querying a FDA table:

SELECT LAST_NAME,FIRST_NAME,SALARY
FROM SEMP
WHERE TIMESTAMP TP_TIMESTAMP('2012-03-01 00:00:00','YYYY-MM-DD:HH24:MI:SS')
WHERE EMPLOYEE_ID=110;

Version query:

SELECT LAST_NAME,FIRST_NAME,SALARY
FROM SEMP
VERSION BETWEEN TIMESTAMP
TO_TIMESTAMP('2011-03-01 00:00:00','YYYY-MM-DD HH24:MI:SS') AND
TO_TIMESTAMP('2012-03-01 00:00:00','YYYY-MM-DD HH24:MI:SS') AND
WHERE EMPLOYEE_ID=110;

Voting disk and OCR Management


Voting disk and OCR Management

The recommendations for the addition or removal of a voting disk is shutdown Oracle Clusterware first on all nodes and then utilize the commands below as root user were path is completely qualified path for the additional voting disk. In case of new voting disk over the network file system NFS, create an empty voting disk file location with the correct owner and permissions before execute the commands mentioned below. Three extra raw devices partitions /dev/raw/raw3, /dev/raw/raw4, /dev/raw/raw5 have been created to practice following exercises:

$ crsctl query css votedisk
on Node1
$ crsctl stop crs
on Node2
$crsctl stop crs

$crsctl add css votedisk /dev/raw/raw3 force

after successful addition of votedisk

Node1
$crsctl start crs

Node2
$crsctl start crs

$crsctl query css votedisk
[oracle@crs1 ~]$ crsctl query css votedisk
 0.     0    /dev/raw/raw2
 1.     0    /dev/raw/raw3

located 2 votedisk(s).

---deleting a votedisk
# crsctl delete css votedisk path

-- backup and recovery of votedisk
dd if=voting_disk_name of=backup_file_name

dd if=/dev/raw/raw2 of=/home/oracle/backup/votedisk1

recovering votedisk

dd if=backup_filename of=voting_disk_name

--administering Oracle Cluster registry

OCR file location is in following file

Solaris /var/opt/oracle.ocr.loc
Linux /etc/oracle/ocr.loc

If ocr files resides on a cluster file system file or if the ocr is on network file system, then create the target OCR file before adding OCR.

Use OCRCHECK utility to verify ocr integrity

It will display
- the version of the OCR's block format
- total space used and available
- OCRID
- and OCR locations that have been configured

A block by block checksum operation for all the blocks in all the configured OCRs is performed by OCRCHECK. It also returns an individual status for each file as well as a result OCR integrity check


[oracle@crs1 ~]$ ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          2
         Total space (kbytes)     :     524184
         Used space (kbytes)      :       4592
         Available space (kbytes) :     519592
         ID                       : 1059761621
         Device/File Name         : /dev/raw/raw1
                                    Device/File integrity check succeeded

                                    Device/File not configured

         Cluster registry integrity check succeeded


OCRCHECK also create a log file in CRS_HOME/log/hostname/client.
to change amoun of logging edit the CRS_HOME/srvm/admin/ocrlog.ini

-- Run the following command to add OCR mirror location using either destination_file or disk to designate the target location of the additional OCR

-- First make sure that proper backup of OCR is available --

#ocrconfig -showbackup

#ocrconfig -replace ocr destination_file or disk
#ocrconfig -replace ocrmirror destination_file or disk

First select the raw device

If your OCR configuration were altered while a particular node is stopped, you would be required to repair OCR configuration on that particular node, for instance, there may be need to repair the OCR on a node that was not up at the time of removing, replacing or adding an OCR.
For repairing an OCR configuration, run the following command on the node on which Oracle Clusterware daemon was stopped.

#ocrconfig -repaire ocrmirror device_name



Monday, April 11, 2011

Recover archive logs from RMAN backup

1. run {
allocate channel c1 device type disk;
restore archivelog sequence 1017;
restore archivelog sequence 1018;
restore archivelog sequence 1019;
}

OR

2. run {
         allocate channel c1 device type disk;
         # Set the destination if you want it to be recovered into
         # different location. By default it would be restored to
         # the default location
         set archivelog destination to 'c:\syed\archlogs';
         restore archivelog from logseq 1017 until logseq 1019;
         release channel c1;
}