HOW TO CHANGE DATABASE NAME IN ORACLE

To change Database name, we have two options.

1. By recreating controlfile

2 Using nid (DBNEWID utility).

Let’s first see how to change DB name using manual method (By recreating control file)

METHOD 1: BY RE-CREATING CONTROLFILE

STEP1:- CREATE CONTROLFILE CREATATION SCRIPT.

[oracle@NVMBD1BZY150D00 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 24 16:29:40 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database backup controlfile to trace;

Database altered.

STEP2:- EDIT/MODIFY CONTROLFILE CREATION SCRIPT.

 

[oracle@oralinux trace]$ ls -lrt

total 16
-rw-r-----. 1 oracle oinstall 1.3K Dec 24 16:26 CLONEDB_dbrm_41909.trc

-rw-r-----. 1 oracle oinstall   73 Dec 24 16:27 CLONEDB_ora_42007.trm

-rw-r-----. 1 oracle oinstall 6.8K Dec 24 16:27 CLONEDB_ora_42007.trc

-rw-r-----. 1 oracle oinstall 494M Dec 24 16:27 alert_CLONEDB.log

[oracle@oralinux trace]$ cp CLONEDB_ora_42007.trc control_clonedb.sql
[oracle@oralinux trace]$ vi control_clonedb.sql

This will generate script in above location

[oracle@dbaserver1 ~ ]$ vi control_clonedb.sql
Here change the database name and replace word REUSE with SET and make sure it is having RESETLOGS

CREATE CONTROLFILE SET DATABASE "CLONEDBN" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 5
    MAXLOGMEMBERS 5
    MAXDATAFILES 100
    MAXINSTANCES 1
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/data/oracle/app/oracle/oradata/CLONEDB/redo1.log'  SIZE 10M BLOCKSIZE 512,
  GROUP 2 '/data/oracle/app/oracle/oradata/CLONEDB/redo2.log'  SIZE 10M BLOCKSIZE 512,
  GROUP 3 '/data/oracle/app/oracle/oradata/CLONEDB/redo3.log'  SIZE 10M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/data/oracle/app/oracle/oradata/CLONEDB/system.dbf',
  '/data/oracle/app/oracle/oradata/CLONEDB/user04.dbf',
  '/data/oracle/app/oracle/oradata/CLONEDB/sysaux.dbf',
  '/data/oracle/app/oracle/oradata/CLONEDB/undo.dbf',
  '/data/oracle/app/oracle/oradata/CLONEDB/test1_tmp.dbf',
  '/data/oracle/app/oracle/oradata/CLONEDB/test_corrupt.dbf'
CHARACTER SET UTF8; 

 STEP3: CHANGE DB_NAME PARAMETER IN SPFILE/PFILE.

SQL> sho parameter control

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      /data/oracle/app/oracle/oradat
                                                 a/CLONEDB/control01.ctl, /data
                                                 /oracle/app/oracle/oradata/CLO
                                                 NEDB/control02.ctl
control_management_pack_access       string      DIAGNOSTIC+TUNING

SQL> shut immediate;

ORA-01013: user requested cancel of current operation

SQL> shut abort;

 

ORACLE instance shut down.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area  601272320 bytes

Fixed Size                  2230712 bytes

Variable Size             276825672 bytes

Database Buffers          310378496 bytes

Redo Buffers               11837440 bytes

SQL> alter system set db_name=CLONEDBN scope=spfile;
System altered.

STEP4:- NOW REMOVE OR MOVE OLD CONTROL FILES.

 Before removing old controlfile do take backup of all controlfiles.

[oracle@NVMBD1BZY150D00 dbs]$cd

/data/oracle/app/oracle/product/11.2.0/dbhome_1/dbs

[oracle@NVMBD1BZY150D00 CLONEDB]$ mv control01.ctl control01.ctl_bkp

[oracle@NVMBD1BZY150D00 CLONEDB]$ mv control02.ctl control02.ctl_bkp

STEP5: SHUT DOWN THE DATABASE AND STARTUP IN NOMOUNT MODE TO REFLECT DB_NAME PARAMETER CHANGES.

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

[oracle@NVMBD1BZY150D00 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 24 16:34:38 2014

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> startup nomount;

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area  601272320 bytes
Fixed Size                  2230712 bytes
Variable Size             276825672 bytes
Database Buffers          310378496 bytes
Redo Buffers               11837440 bytes

STEP6: NOW EXECUTE NEWLY CREATED CONTROLFILE SCRIPT.

SQL> @control_clonedb.sql

Control file created.

STEP7: OPEN DATABASE WITH RESETLOGS OPTION.

SQL> alter database open resetlogs;

Database altered.

SQL> select database_name from v$database;

DATABASE_NAME

--------------------------------------------------------------------------------
CLONEDBN

METHOD 2: USING NID(DBNEWID UTILITY)

DBNEWID is a database utility that can change the internal database identifier (DBID) and the database name (DBNAME) for an operational database.

 

The DBNEWID utility solves this problem by allowing you to change any of the following:

 

• Only the DBID of a database
• Only the DBNAME of a database
• Both the DBNAME and DBID of a database

I prefer to change both DBNAME and DBID at the same time as a best practice during creation of test environments.

 

Step-1. We will change both db_name to CLONE and dbid belongs to cloned database.

Check db_id and db_name for new environment before operation.

SQL> select dbid,name from v$database;

 

DBID NAME

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

 953825422 CLONEDBN

Step-2. Startup instance with mount state.

export ORACLE_SID=CLONEDB
sqlplus / as sysdba
shutdown immediate;
startup mount;

Step-3. Execute nid command and check the log file “/tmp/nid.log”:

oracle@NVMBD1BZY150D00 dbs]$ nid target=/ dbname=CLONE logfile=/tmp/nid.log
[oracle@NVMBD1BZY150D00 dbs]$ cat /tmp/nid.log

DBNEWID: Release 11.2.0.3.0 - Production on Thu Dec 25 12:07:16 2014

 

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

 

Connected to database CLONEDBN (DBID=953825422)
 
Connected to server version 11.2.0

Control Files in database:

    /data/oracle/app/oracle/oradata/CLONEDB/control01.ctl

    /data/oracle/app/oracle/oradata/CLONEDB/control02.ctl

Changing database ID from 953825422 to 1066065334

Changing database name from CLONEDBN to CLONE

    Control File /data/oracle/app/oracle/oradata/CLONEDB/control01.ctl - modified

    Control File /data/oracle/app/oracle/oradata/CLONEDB/control02.ctl - modified

    Datafile /data/oracle/app/oracle/oradata/CLONEDB/system.db - dbid changed, wrote new name

    Datafile /data/oracle/app/oracle/oradata/CLONEDB/user04.db - dbid changed, wrote new name

    Datafile /data/oracle/app/oracle/oradata/CLONEDB/sysaux.db - dbid changed, wrote new name

    Datafile /data/oracle/app/oracle/oradata/CLONEDB/undo.db - dbid changed, wrote new name

    Datafile /data/oracle/app/oracle/oradata/CLONEDB/test1_tmp.db - dbid changed, wrote new name

    Datafile /data/oracle/app/oracle/oradata/CLONEDB/test_corrupt.db - dbid changed, wrote new name

    Control File /data/oracle/app/oracle/oradata/CLONEDB/control01.ctl - dbid changed, wrote new name

    Control File /data/oracle/app/oracle/oradata/CLONEDB/control02.ctl - dbid changed, wrote new name

    Instance shut down

 

Database name changed to CLONE.

Modify parameter file and generate a new password file before restarting.

Database ID for database CLONE changed to 1066065334.

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

Database has been shutdown, open database with RESETLOGS option.

Succesfully changed database name and ID.

DBNEWID - Completed succesfully.

After DBNEWID completed successful, instance has been also closed automatically.

Step-4. Startup instance with nomount option and change the db_name to CLONE.

Then shutdown and startup mount instance again for activate new db_name. At last, open database with resetlogs option.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  601272320 bytes

Fixed Size                  2230712 bytes

Variable Size             276825672 bytes

Database Buffers          310378496 bytes

Redo Buffers               11837440 bytes

SQL> show parameter db_name

NAME                                 TYPE        VALUE

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

db_name                              string      CLONE

SQL> alter system set db_name=CLONE scope=spfile;



System altered.

SQL> shutdown immediate;



ORA-01507: database not mounted
ORACLE instance shut down.

SQL> startup nomount;



ORACLE instance started.

Total System Global Area 1663012104 bytes
Fixed Size 744712 bytes
Variable Size 805306368 bytes
Database Buffers 855638016 bytes
Redo Buffers 1323008 bytes

SQL> show parameter db_name

 

NAME                                 TYPE        VALUE

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

db_name                              string      CLONE

 

SQL> alter database mount;

Database altered.

SQL> alter database open resetlogs;

Database altered.

Step-5. Control the value of dbid and name of the new database.

SQL> select dbid, name from v$database;

DBID NAME

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

1066065334 CLONE

Step-6. You should create new password file for the new environment if you need

cd $ORACLE_HOME/dbs
orapwd file=orapwCLONE password=clone entries=3

 

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!

 

Manually corrupting the data block in Linux and recovering it using BLOCKRECOVER command of RMAN

In order to test the RMAN’s  BLOCKRECOVER command, we need to corrupt the specific data block and recover it for testing purpose. To do it in Linux, use dd command. In the following example, let’s create a table and corrupt it manually

1. For the purpose of this practice, will create a separate tablespace and a new schema user.

a. Create a Tablespace say “test_corrupt”

[oracle@NVMBD1BZY150D00 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Dec 19 18:09:36 2014

 

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

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

SQL> create tablespace test_corrupt datafile '/data/oracle/app/oracle/oradata/TESTDB/test_corrupt.dbf' size 10m;

Tablespace created.

b. Create an user say “test” and assign quota to the “test_corrupt” tablespace

SQL> create user test identified by test123 default tablespace test_corrupt

quota unlimited on test_corrupt;

User created.

c. Grant the necessary privileges

SQL> grant create session, resource to test;

Grant succeeded.

 

2. Create a table as test user

SQL> conn test/test123

Connected.

SQL> create table emp(eno number(7)) tablespace test_corrupt;

Table created.

 

3. Insert some dummy records into the table by writing a simple ‘for loop’ block as shown:

SQL> begin

for i in 1..10000
loop
insert into emp values(i);
end loop;
end;
/

PL/SQL procedure successfully completed.

SQL> select count(*) from emp;

COUNT(*)

———-
10000

 

4. Take the RMAN backup, either full database or tablespace level backup before corrupting the block.

Connect to the RMAN

[oracle@NVMBD1BZY150D00 ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Dec 19 18:14:36 2014

 

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

 

connected to target database: TESTDB (DBID=2649358339)

Report the schema to know about the target database

RMAN> report schema;

using target database control file instead of recovery catalog

Report of database schema for database with db_unique_name TESTDB

 

List of Permanent Datafiles

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

File Size(MB) Tablespace           RB segs Datafile Name

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

1    100      SYSTEM               ***     /data/oracle/app/oracle/oradata/TESTDB/system.dbf

2    100      SYSTEM               ***     /data/oracle/app/oracle/oradata/TESTDB/user04.dbf

3    100      SYSAUX               ***     /data/oracle/app/oracle/oradata/TESTDB/sysaux.dbf

4    500      UNDOTBS              ***     /data/oracle/app/oracle/oradata/TESTDB/undo.dbf

5    1024     TEST                 ***     /data/oracle/app/oracle/oradata/TESTDB/test1_tmp.dbf

6    10       TEST_CORRUPT         ***     /data/oracle/app/oracle/oradata/TESTDB/test_corrupt.dbf

 

List of Temporary Files

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

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

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

1    1000     TEMP                 1000        /data/oracle/app/oracle/oradata/TESTDB/temp1.dbf

Take the backup of the tablespace

RMAN> backup tablespace test_corrupt;

Starting backup at 19-DEC-14

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=190 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=00006 name=/data/oracle/app/oracle/oradata/TESTDB/tes

channel ORA_DISK_1: starting piece 1 at 19-DEC-14

channel ORA_DISK_1: finished piece 1 at 19-DEC-14

piece handle=/data/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/0cpqitp8_1_1 ta

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 19-DEC-14

 

RMAN>

Recovery Manager complete.

5. Manually corrupting datablock.

[oracle@NVMBD1BZY150D00 ~]$ ssqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Fri Dec 19 18:16:14 2014

 

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

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

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

 

SQL> SELECT header_block FROM dba_segments WHERE segment_name='EMP';

HEADER_BLOCK

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

130

So the header of segment (table) is block 130, so if the block 131,132,133,… get some noise it will eventually end up as a datafile with corrupted blocks

[oracle@NVMBD1BZY150D00 ~]$  dd of=/data/oracle/app/oracle/oradata/TESTDB/test_corrupt.dbf bs=8192 conv=notrunc seek=131 << EOF

>  > testing corruption

> > EOF

>

> -bash: warning: here-document at line 18 delimited by end-of-file (wanted `EOF')

0+1 records in

0+1 records out

29 bytes (29 B) copied, 3.1915e-05 s, 909 kB/s

 

6. To query the data, connect to the “test” user and query the table “emp”

SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;

System altered.

 SQL> conn test/test123

Connected.

SQL> select * from emp;

select * from emp

              *

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 6, block # 131)

ORA-01110: data file 6:

'/data/oracle/app/oracle/oradata/TESTDB/test_corrupt.dbf'

The query returns complains of block corruption in file 6, and the block numbered 131 is being reported as corrupt.

This is the methods to be followed in order to corrupt the Oracle data blocks.

Lets us see, how to identify the corrupted blocks and recover them.

7. Let us see what are the blocks are corrupted in “test_corrupt01.dbf” datafile by running dbv (DBVERIFY) utility.

 [oracle@NVMBD1BZY150D00 ~]$ dbv file=/data/oracle/app/oracle/oradata/TESTDB/test_corrupt.dbf blocksize=8192

DBVERIFY: Release 11.2.0.3.0 - Production on Fri Dec 19 18:19:10 2014

 

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

DBVERIFY - Verification starting : FILE = /data/oracle/app/oracle/oradata/TESTDB/test_corrupt.dbf

Page 131 is marked corrupt

Corrupt block relative dba: 0x01800083 (file 6, block 131)

Bad header found during dbv:

Data in bad block:

 type: 32 format: 6 rdba: 0x69747365

 last change scn: 0x726f.6320676e seq: 0x72 flg: 0x75

 spare1: 0x20 spare2: 0x74 spare3: 0x6f69

 consistency value in tail: 0x5b5c0601

 check value in block header: 0x7470

 computed block checksum: 0xeb9b

 

 

DBVERIFY - Verification complete

 

Total Pages Examined         : 1280

Total Pages Processed (Data) : 19

Total Pages Failing   (Data) : 0

Total Pages Processed (Index): 0

Total Pages Failing   (Index): 0

Total Pages Processed (Other): 131

Total Pages Processed (Seg)  : 0

Total Pages Failing   (Seg)  : 0

Total Pages Empty            : 1129

Total Pages Marked Corrupt   : 1

Total Pages Influx           : 0

Total Pages Encrypted        : 0

Highest block SCN            : 678748 (0.678748)

This utility scans all the blocks in a given datafile and outputs the corrupt blocks. In this case, there was only one block marked as corrupt. Make a note of all the corrupted blocks as we need to recover them to previous state.

8. Start RMAN session and recover all the corrupted blocks.

The beauty of RMAN is that it recovers only corrupted blocks and we need to recover only those corrupt blocks instead of entire datafile.

[oracle@NVMBD1BZY150D00 ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Dec 19 18:22:41 2014

 

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

 

connected to target database: TESTDB (DBID=2649358339)

Report the Schema to know the status of target database, gathers the information from control file

RMAN> report schema;

using target database control file instead of recovery catalog

Report of database schema for database with db_unique_name TESTDB

 

List of Permanent Datafiles

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

File Size(MB) Tablespace           RB segs Datafile Name

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

1    100      SYSTEM               ***     /data/oracle/app/oracle/oradata/TESTDB/system.dbf

2    100      SYSTEM               ***     /data/oracle/app/oracle/oradata/TESTDB/user04.dbf

3    100      SYSAUX               ***     /data/oracle/app/oracle/oradata/TESTDB/sysaux.dbf

4    500      UNDOTBS              ***     /data/oracle/app/oracle/oradata/TESTDB/undo.dbf

5    1024     TEST                 ***     /data/oracle/app/oracle/oradata/TESTDB/test1_tmp.dbf

6    10       TEST_CORRUPT         ***     /data/oracle/app/oracle/oradata/TESTDB/test_corrupt.dbf

 

List of Temporary Files

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

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

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

1    1000     TEMP                 1000        /data/oracle/app/oracle/oradata/TESTDB/temp1.dbf

9. Recover the corrupted blocks using recover command

RMAN>  BLOCKRECOVER DATAFILE 6 BLOCK 131;

Starting recover at 19-DEC-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=197 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 00006

channel ORA_DISK_1: reading from backup piece /data/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/0cpqitp8_1_1

channel ORA_DISK_1: piece handle=/data/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/0cpqitp8_1_1 tag=TAG20141219T181504

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

media recovery complete, elapsed time: 00:00:01

 

Finished recover at 19-DEC-14

RMAN>

Recovery Manager complete.

10. Will query the table again by logging in to the SQL*Plus:

SQL> conn test/test123

Connected.

SQL> select * from emp order by eno;

ENO

———-

1

2

3

…

…

…

9997

9998

9999

10000

10000 rows selected.

 

SQL> select count(*) from emp;

COUNT(*)

———-

10000

This will executes successfully and the records will be restored back to the table.

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!