Changing DB ID for the Oracle Database 10g&11g

When you clone the database, the DB ID remains same as like the source database, if you need to change to the different DB ID, then this note will be useful. This is very much useful as in the case of working with RMAN.

Follow the below steps to change the DB ID in Oracle10g and Oracle11g databases:

1. Identify the DBID of the database

SQL> select dbid from v$database;

DBID

———-

1272957858

2. Shutdown the database in normal mode

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

3. Start the database to mount phase

SQL> startup mount

ORACLE instance started.

Total System Global Area    150667264 bytes

Fixed Size                    1335080 bytes

Variable Size                92274904 bytes

Database Buffers             50331648 bytes

Redo Buffers                  6725632 bytes

Database mounted.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

4. In the Terminal Window, execute the nid command

[oracle@apps ~]$ which nid

/u01/app/oracle/product/11.2.0/dbhome_1/bin/nid

[oracle@apps ~]$ nid target=/

DBNEWID: Release 11.2.0.1.0 – Production on Fri Mar 25 16:32:17 2016

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

Connected to database RC (DBID=1272957858)

Connected to server version 11.2.0

Control Files in database:

/u01/app/oracle/oradata/rc/control01.ctl

/u01/app/oracle/oradata/rc/control02.ctl

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

Proceeding with operation

Changing database ID from 1272957858 to 2943969233

Control File /u01/app/oracle/oradata/rc/control01.ctl – modified

Control File /u01/app/oracle/oradata/rc/control02.ctl – modified

Datafile /u01/app/oracle/oradata/rc/system01.db – dbid changed

Datafile /u01/app/oracle/oradata/rc/sysaux01.db – dbid changed

Datafile /u01/app/oracle/oradata/rc/undotbs01.db – dbid changed

Datafile /u01/app/oracle/oradata/rc/def_perm01.db – dbid changed

Control File /u01/app/oracle/oradata/rc/control01.ctl – dbid changed

Control File /u01/app/oracle/oradata/rc/control02.ctl – dbid changed

Instance shut down

Database ID for database RC changed to 2943969233.

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.

5. Bounce back the database to mount phase

[oracle@apps ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Mar 25 16:33:14 2016

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area    150667264 bytes

Fixed Size                    1335080 bytes

Variable Size                92274904 bytes

Database Buffers             50331648 bytes

Redo Buffers                  6725632 bytes

Database mounted.

6. Open the database with resetlog option

SQL> alter database open resetlogs ;

Database altered.

SQL>

7. Identify the new changed DBID

SQL> select dbid from v$database;

DBID

———-

2943969233

Read more

How to recover datafile block corruption using RMAN?

In this Article, you will be learning how to recover a corrupted block/blocks using RMAN. 

Steps are involved here are : 

 1.I am going to create a table in "USERS" tablespace . 

 2.Just Identify the blocks belonging to that newly created table("mytab").

 3.Corrupt all or some of those blocks using the Unix dd command. 

4.Flush the buffer cache to ensure we read blocks from disk and not from memory(buffer cache). 

5.Verify block corruptions from V$DATABASE_BLOCK_CORRUPTION

 Let's Create a Table in "USERS" tablespace. =========================================================

SQL> create table mytab tablespace users as select * from tab; Table created. SQL> select count(*) from mytab; COUNT(*) 

----------

 4741

Just Identify the blocks belonging to that newly created table("mytab"). ============================================================

SQL> select * from (select distinct dbms_rowid.rowid_block_number(rowid)from myt ab)where rownum < 6;

 DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) 

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

 2123 2124 2125 2126 2127

Now check if any blocks got corrupted before we make it corrupt.(Intentionally we are going to corrupt one block here ) ===================================================================

SQL> select * from v$database_block_corruption;

no rows selected There no blocks corrupted as of now. now we will corrupt one block. ===========================================================

[oracle@machine1 ~]$ dd of=/u01/app/oracle/oradata/TEST/users01.dbf bs=8192 seek=2127 conv=notrunc count=1 if=/dev/zero 1+0 records in 1+0 records out 8192 bytes (8.2 kB) copied, 0.0107044 s, 765 kB/s [oracle@machine1 ~]$

Flush the buffer cache to ensure we read blocks from disk and not from memory(buffer cache). 

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

SQL> alter system flush buffer_cache; System altered. 

 SQL> select * from mytab; 

 TNAME TABTYPE CLUSTERID 

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

 ACCESS$ TABLE ALERT_QT TABLE ALL$OLAP2_AWS VIEW ALL_ALL_TABLES VIEW ALL_APPLY VIEW ALL_APPLY_CHANGE_HANDLERS VIEW ALL_APPLY_CONFLICT_COLUMNS VIEW ALL_APPLY_DML_HANDLERS VIEW ALL_APPLY_ENQUEUE VIEW ALL_APPLY_ERROR VIEW ALL_APPLY_EXECUTE VIEW ALL_APPLY_KEY_COLUMNS VIEW ALL_APPLY_PARAMETERS VIEW ALL_APPLY_PROGRESS VIEW ALL_APPLY_TABLE_COLUMNS VIEW ALL_ARGUMENTS VIEW ALL_ASSEMBLIES VIEW . . . so on...

At the end of the line we will see error like ========================================================= 

ERROR: ORA-01578: ORACLE data block corrupted (file # 4, block # 2127) ORA-01110: data file 4: '/u01/app/oracle/oradata/TEST/users01.dbf' 930 rows selected . 

 Now we are seeing one block id #2127 got corrupted 

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

SQL> select * from v$database_block_corruption; 

 FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO 

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

 4 2127 1 0 ALL ZERO 

 SQL>

Recover the corrupted blocks using the rman : ========================================================

[oracle@machine1 ~]$ export ORACLE_SID=TEST 

[oracle@machine1 ~]$ rman target / 

 Recovery Manager: Release 11.2.0.1.0 - Production on Sat Mar 19 17:13:33 2016 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. 

 connected to target database: TEST (DBID=2204857542) 

 RMAN> recover datafile 4 block 2127; 

 Starting recover at 19-MAR-2016 17:13:47 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=46 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=49 device type=DISK channel ORA_DISK_1: restoring block(s) channel ORA_DISK_1: specifying block(s) to restore from backup set restoring blocks of datafile 00004 channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/TEST/backupset/2016_03_19/o1_mf_nnndf_TAG20160319T002440_cgrmqp9m_.bkp channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/TEST/backupset/2016_03_19/o1_mf_nnndf_TAG20160319T002440_cgrmqp9m_.bkp tag=TAG20160319T002440 channel ORA_DISK_1: restored block(s) from backup piece 1 channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01 starting media recovery archived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/flash_recovery_area/TEST/archivelog/2016_03_19/o1_mf_1_5_cgrmvn31_.arc archived log for thread 1 with sequence 6 is already on disk as file /u01/app/oracle/flash_recovery_area/TEST/archivelog/2016_03_19/o1_mf_1_6_cgt8jbjz_.arc archived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/flash_recovery_area/TEST/archivelog/2016_03_19/o1_mf_1_7_cgtbg5x2_.arc archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/flash_recovery_area/TEST/archivelog/2016_03_19/o1_mf_1_8_cgtbj5dn_.arc media recovery complete, elapsed time: 00:00:08 Finished recover at 19-MAR-2016 17:14:03 

 RMAN> SQL> select * from v$database_block_corruption;

 no rows selected 

 SQL>

Read more

How to Create a Recovery Catalog Database Using RMAN

Assume that -

my target database name ==> PROD    (@TPROD is TNSNAME)

my Catalog database name==> TEST     (@TTEST is TNSNAME)

$export ORACLE_SID=TEST (here i want to create catalog in this TEST database)

$ sqlplus / as sysdba

Create a user which will be owner of the recovery catalog;

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

SQL> create user rmanuser identified by rmanuser;

- Grant necessary roles, privileges to user "rmanuser"

SQL> grant connect, resource ,recovery_catalog_owner to rmanuser;

Connect to Catalog database(TEST) as a rmanuser and Create Recovery Catalog

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

RMAN> connect catalog rmanuser/rmanuser@TTEST

connected to recovery catalog database

Connect to target database now.

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

RMAN> connect target sys /syspassword@TPROD

connected to target database: PROD (DBID=314074644)

Create catalog and register target database in to catalog.

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

RMAN> create catalog;

recovery catalog created

RMAN> register database;

database registered in recovery catalog

starting full resync of recovery catalog

full resync complete

Verify that the registration was successful by running REPORT SCHEMA

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

RMAN> report schema;

Report of database schema for database with db_unique_name PROD

List of Permanent Datafiles

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

File Size(MB) Tablespace           RB segs Datafile Name

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

1    680      SYSTEM               YES     /u01/app/oracle/oradata/PROD/system01.dbf

2    480      SYSAUX               NO      /u01/app/oracle/oradata/PROD/sysaux01.dbf

3    40       UNDOTBS1             YES     /u01/app/oracle/oradata/PROD/undotbs01.dbf

4    5        USERS                NO      /u01/app/oracle/oradata/PROD/users01.dbf

5    100      EXAMPLE              NO      /u01/app/oracle/oradata/PROD/example01.dbf

6    100      USERS                NO      /u01/app/oracle/oradata/PROD/users02.dbf

List of Temporary Files

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

File Size(MB) Tablespace           Maxsize(MB) Tempfile Name

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

1    20       TEMP                 32767       /u01/app/oracle/oradata/PROD/temp01.dbf

RMAN>exit

Read more