Rename Oracle DataBase

Some times we may need to rename our database. Controlfile is the file which will have the database name. This controlfile is read when database is mounted and instance comes to know  the database name and mounts the database with this name.So if we recreate the controlfile and we keep new database name in controlfile recreation script then the database will have new name.

Follow the below steps to rename the database

 SQL> select name from v$database;

NAME                                                                           
---------                                                                      
ORCL                                                                           

SQL> sho parameter db_name

NAME                                 TYPE        VALUE                         
------------------------------------ ----------- ------------------------------
db_name                              string      orcl                          


1. Take the controlfile backup as ASCII file using below statement

SQL>alter database backup controlfile to trace;

Database altered.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

2. Contents of controlfile backup as trace with modified values

CREATE CONTROLFILE set DATABASE "ORCLNEW" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 'H:\ORCL\ORCL\REDO01.LOG'  SIZE 50M,
  GROUP 2 'H:\ORCL\ORCL\REDO02.LOG'  SIZE 50M,
  GROUP 3 'H:\ORCL\ORCL\REDO03.LOG'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  'H:\ORCL\ORCL\SYSTEM01.DBF',
  'H:\ORCL\ORCL\UNDOTBS01.DBF',
  'H:\ORCL\ORCL\SYSAUX01.DBF',
  'H:\ORCL\ORCL\USERS01.DBF'
CHARACTER SET WE8MSWIN1252
;

 3.Modify the parameter file and give name to db_name parameter(highlited in red).

orcl.__db_cache_size=134217728

orcl.__java_pool_size=4194304

orcl.__large_pool_size=4194304

orcl.__shared_pool_size=58720256

orcl.__streams_pool_size=0

*.audit_file_dest='H:\oracle\product/admin/orcl/adump'

*.background_dump_dest='H:\oracle\product/admin/orcl/bdump'

*.compatible='10.2.0.1.0'

*.control_files='H:\orcl\orcl\control01.ctl','H:\orcl\orcl\control02.ctl','H:\orcl\orcl\control03.ctl'

*.core_dump_dest='H:\oracle\product/admin/orcl/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='ORCLNEW'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

*.job_queue_processes=10

*.log_archive_dest_1='LOCATION=h:\orcl\arch'

*.log_archive_format='ARC%S_%R.%T'

*.open_cursors=300

*.pga_aggregate_target=104857600

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=209715200

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='H:\oracle\product/admin/orcl/udump'


4.Startup database in nomount and execute the script

SQL> startup nomount
ORACLE instance started.

Total System Global Area  209715200 bytes                                      
Fixed Size                  1248116 bytes                                      
Variable Size              67110028 bytes                                      
Database Buffers          134217728 bytes                                      
Redo Buffers                7139328 bytes                                      
SQL> sho parameter db_name

NAME                                 TYPE        VALUE                         
------------------------------------ ----------- ------------------------------
db_name                              string      ORCLNEW                       

SQL> @h:\rename.trc

Control file created.

SQL> alter database open resetlogs;

Database altered.

SQL> select name from v$database;

NAME                                                                           
---------                                                                      

ORCLNEW                                                                        

Migration of Non ASM oracle database to ASM databsae

MIGRATION FROM NON ASM TO ASM

1 First we need to set below parameter in parameter file for controlfile.

NOTE: I have two disk group here I am using "DATA" disk group.

control_files='+data'

Step: 2 Now we need to restart the database so that new parameter value will take effect.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 263639040 bytes

Fixed Size 1332552 bytes

Variable Size 218106552 bytes

Database Buffers 37748736 bytes

Redo Buffers 6451200 bytes

Step:3 We will restore controlfile on ASM file system .

]$rman target /

RMAN> restore controlfile from ‘/disk1/oradata/test/CONTROL01.ctl';

Starting restore at 10-dec-11using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=151 device type=DISK

channel ORA_DISK_1: copied control file copyoutput file name=+data/test/controlfile/current.256.652270419Finished restore at 10-dec-11

Step:4 Mount Oracle Database and take backup of database.

RMAN> alter database mount;

database mountedreleased channel: ORA_DISK_1

RMAN> backup as copy database format '+data';

RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DATA/test/datafile/system.257.652270565"

datafile 2 switched to datafile copy "+DATA/test/datafile/sysaux.258.652270761"

datafile 3 switched to datafile copy "+DATA/test/datafile/undotbs1.259.652270927"

datafile 4 switched to datafile copy "+DATA/test/datafile/users.261.652270989"

Step:5 Now you can open the database .

SQL> alter database open resetlogs;

Database altered.

Step:6 Drop old temporary tablespace  and create new temporay tablespace

SQL> create temporary tablespace temp2 tempfile '/disk1/oradata/test/temp2.dbf'

Tablespace created.

SQL> alter database default temporary tablespace temp2;

Database altered.

SQL> select tablespace_name, file_name, bytes from dba_temp_files;

TABLESPACE_NAME FILE_NAME BYTES

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

TEMP2                              +DATA/test/tempfile/temp2.dbf

Step:7 Recreate All redolog group on ASM diskgroup

SQL> select group#, member from v$logfile

GROUP# MEMBER

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

3           /DISK1/ORADATA/TEST/REDO03.LOG

2           /DISK1/ORADATA/TEST/REDO02.LOG

1           /DISK1/ORADATA/TEST/REDO01.LOG

SQL> select group#,status from v$log;

GROUP# STATUS

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

1 CURRENT

2 UNUSED

3 UNUSED

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select group#,status from v$log;

GROUP# STATUS

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

1 ACTIVE

2 ACTIVE

3 CURRENT

SQL>alter database add logfile group 4 (‘+data/redolog4.log’) size 10m;

SQL>alter database add logfile group 5 (‘+data/redolog5.log’) size 10m;

SQL>alter system switch logfile;

SQL>alter system switch logfile;

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database drop logfile group 3 ;

Database altered.

Key Differences in Oracle 9i and Oracle 10g

Following are the key differences in Oracle 9i and Oracle 10g

1. There are more implementation differences. In terms of architecture, 9i is based on Internet technology while 10g is grid computing based one. Many DBA features like Automated Storage Management (ASM), Automatic Workload Repository (AWR), Automatic Database Diagnostic Monitor (ADDM) were introduced 10g version of oracle.

2. For developers, 10g is more stable than 9i. All the earlier bugs reported were fixed in 10g release.

3. Performance wise, 10g is more impressive. It has increased the data chunk size during I/O between the SQL and PL/SQL engines. Use of collections is recommended in 10g.
PLS_INTEGER, a new data type was added which enhances performance. ANYDATA data type was introduced to hold a data of variant feature.

4. FLASHBACK option was made more stronger like TABLE FLASHBACK was introduced. Oracle Recyclebin was introduced. The dropped objects can be flashed back like tables.

5. Support for bigfile tablespaces that is up to 8 Exabytes in size has been introduced
 
6. Data Pump has been introduced for faster data movement with expdp and impdp utilities.

7. In Oracle 9i after drop we can't rollback the drop operation but we can do it in 10g.
 
Moreover, 10g has additional 149 features than 9i.