Renaming an Oracle database

Follow below steps to rename database name from UATDB to UATNEWDB.

Step1: In UATDB, connect to SQLPLUS and issue command "alter database backup controlfile to trace;". this command generate a script containg a create controlfile

command and stored it in trace (Udump) directory. you can find location as USER_DUMP_DEST paramenter in parameter file.

Step2: The trace file output looks like below.

STARTUP NOMOUNT
CREATE CONTROLFILE set DATABASE "UATDB" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 2336
LOGFILE
  GROUP 1 '/oracle/oradata/UATDB/redo01.log'  SIZE 50M,
  GROUP 2 '/oracle/oradata/UATDB/redo02.log'  SIZE 50M,
  GROUP 3 '/oracle/oradata/UATDB/redo03.log'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  '/oracle/oradata/UATDB/system01.dbf',
  '/oracle/oradata/UATDB/sysaux01.dbf',
  '/oracle/oradata/UATDB/users01.dbf',
  '/oracle/oradata/UATDB/undotbs0.dbf',
  '/oracle/oradata/UATDB/DATA01.dbf'
CHARACTER SET WE8ISO8859P1
;

Step3: Shutdown the UATDB database

Step4: Change all locations to new DB name in Spfile, tnsnames and listener etc..

Step5: Change the database name in trace file that pointing to new DB name "UATNEWDB" like below

STARTUP NOMOUNT
CREATE CONTROLFILE set DATABASE "UATNEWDB" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 2336
LOGFILE
  GROUP 1 '/oracle/oradata/UATNEWDB/redo01.log'  SIZE 50M,
  GROUP 2 '/oracle/oradata/UATNEWDB/redo02.log'  SIZE 50M,
  GROUP 3 '/oracle/oradata/UATNEWDB/redo03.log'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  '/oracle/oradata/UATNEWDB/system01.dbf',
  '/oracle/oradata/UATNEWDB/sysaux01.dbf',
  '/oracle/oradata/UATNEWDB/users01.dbf',
  '/oracle/oradata/UATNEWDB/undotbs0.dbf',
  '/oracle/oradata/UATNEWDB/DATA01.dbf'
CHARACTER SET WE8ISO8859P1
;

Step6: Run the above script to create new controlfiles and open database in resetlogs option.

Practical demonstration for renaming database name from UATDB to UATNEWDB:

$ export ORACLE_SID=UATDB

$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 6 18:19:31 2012

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> alter database backup controlfile to trace;

Database altered.

SQL> create pfile from spfile;
File created.

SQL> show parameter dump;  -- find the udump location where trace control file created

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

Modify the trace file like below

$ cat UATDB_ctrl.sql

STARTUP NOMOUNT
CREATE CONTROLFILE set DATABASE "UATNEWDB" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 2336
LOGFILE
  GROUP 1 '/oracle/oradata/UATNEWDB/redo01.log'  SIZE 50M,
  GROUP 2 '/oracle/oradata/UATNEWDB/redo02.log'  SIZE 50M,
  GROUP 3 '/oracle/oradata/UATNEWDB/redo03.log'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  '/oracle/oradata/UATNEWDB/system01.dbf',
  '/oracle/oradata/UATNEWDB/sysaux01.dbf',
  '/oracle/oradata/UATNEWDB/users01.dbf',
  '/oracle/oradata/UATNEWDB/undotbs0.dbf',
  '/oracle/oradata/UATNEWDB/DATA01.dbf'
CHARACTER SET WE8ISO8859P1
;

$

Modify prameters in initUATDB.ora file and change file to initUATNEWDB.ora

$ mv initUATDB.ora initUATNEWDB.ora

Just backup of old controlfiles

$ mv control01.ctl control01.ctl_bkp
$ mv control02.ctl control02.ctl_bkp
$ mv control03.ctl control03.ctl_bkp
$

$ export ORACLE_SID=UATNEWDB
$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 6 19:10:39 2012

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

Connected to an idle instance.
SQL>
SQL> @UATDB_ctrl.sql
ORACLE instance started.

Total System Global Area 1526726656 bytes
Fixed Size                  2020960 bytes
Variable Size             285215136 bytes
Database Buffers         1224736768 bytes
Redo Buffers               14753792 bytes

Control file created.

SQL> alter database open resetlogs;

Database altered.

SQL> select file_name from dba_data_files;

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

$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 6 19:15:06 2012
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> create spfile from pfile;

File created.

SQL> startup;
ORACLE instance started.

Total System Global Area 1526726656 bytes
Fixed Size                  2020960 bytes
Variable Size             369101216 bytes
Database Buffers         1140850688 bytes
Redo Buffers               14753792 bytes
Database mounted.
Database opened.
SQL>

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
$

Finally create Temporary tablespace, make it as default and create password file for new DB "UATNEWDB"

Create tempfile and make aa a default:

SQL> create temporary tablespace TEMP01 tempfile '/oracle1/oradata/uatnewdb/temp01.dbf' size 1024M autoextend on;

SQL> alter database default temporary tablespace TEMP01;


make an entry to /etc/oratab:

$ vi  /etc/oratab

CREATE PASSWORD FILE FOR NEW DB:

orapwd force=y file=orapwUATNEWDB password=sys entries=5

Thats It!!!

Hope this will help you...:)

Best Regards,

Installation of Oracle Database 11gR2 on Oracle Enterprise Linux 5.7

Prerequisite:  Oracle Enterprise Linux 5.7 Installation

Start PrimDB VMachine

Follow below steps to Installation of oracle database 11gR2 and creation of PRIMDB database:

Create group and User:

[root@primdb ~]# groupadd -g 1000 oinstall
[root@primdb ~]# useradd -u 1100 -g oinstall oracle
[root@primdb ~]# passwd oracle
Changing password for user oracle.
New UNIX password:
BAD PASSWORD: it is based on a dictionary word
Retype new UNIX password:
passwd: all authentication tokens updated successfully.
[root@primdb ~]#

Create directories and give folder permissions in which Oracle software will be installed:

[root@primdb ~]# mkdir -p /u01/app/oracle/product/11.2.0/dbhome_1
[root@primdb ~]# chown -R oracle:oinstall /u01
[root@primdb ~]# chmod -R 775 /u01

Copy 11.2.0.3 database software to /u01/database location:

[root@primdb ~]# cd /u01/
[root@primdb ~]# ls
app database
[root@primdb ~]# chown -R oracle:oinstall /u01
[root@primdb ~]# chmod -R 775 /u01
[root@primdb ~]# su  –  oracle
[root@primdb ~]$ cd /u01/database/

Before running runInstaller open a newterminal and run xhost + as a root user:

[root@primdb ~]# xhost +
access control disabled, clients can connect from any host

Start Installation with runInstaller:

[root@primdb database]$ ./runInstaller

Uncheck the Updates box and Click Next

Click Yes

Select Skip Software updates and Click Next

Select Create and Configure a database and Click Next

Select Server Class and Click Next

Select Single instance database installation and Click Next

 Select Advanced Install and Click Next

Select Enterprise Edition and Click Next

Run the scripts as shown below

That's it..Completed Oracle 11gR2 database installation and created new PRIMDB database.

Hope this will help you!!! :)

Best Regards,

Script: Shell Script to export Full DB or Single or Multiple Schemas

Just recently I have written below script for export full database or single/multiple schemas.

Follow the steps in comments section.

#!/bin/bash
###################################################################################################** Logical Database/Schema's backup Script**###########################
############## **Chowdari's Oracle DBA Blog - mbc-dba.blogspot.com ** ####################
########################################################################################################## Please create exp_user and provide grants #######################
# create user EXP_USR identified by EXP_USR;                                                                             #####
# grant CONNECT,EXP_FULL_DATABASE,IMP_FULL_DATABASE to EXP_USR;           #######
#################################################################################
# If you want schema wise backup, Please add list of schemas with single space otherwise                
# keep it blank.  Follow below examples                                                              ###################
# Example for schemawise backup: SchemaName=(ABC XYZ MNO) - Here ABC,XYZ, MNO are  ### #  schema names                                                                                                        ################
# Example for full db backup: SchemaName=()                                                          ################
#################################################################################
# Please assign database name in SID variable. Example I have given TESTDB            ###############
#################################################################################  

SID=TESTDB
BKP_LOCATION=`pwd`
SchemaName=(CRM EMP)
#SchemaName=()
SchLen=${#SchemaName[@]}

if [ ${SchLen} -eq 0 ]; then
 exp EXP_USR/EXP_USR@$SID FILE=$BKP_LOCATION/EXP_FULL.DMP LOG=$BKP_LOCATION/EXP_FULL.LOG FULL=Y STATISTICS=NONE
else
 echo "Schema Bkp"
 for (( i = 0; i <= `expr ${SchLen} - 1`; i++ ))
 do
  exp EXP_USR/EXP_USR@$SID FILE=$BKP_LOCATION/${SchemaName[$i]}_EXP.DMP LOG=$BKP_LOCATION/${SchemaName[$i]}_EXP.LOG OWNER=${SchemaName[$i]} STATISTICS=NONE
 done
fi


Hope this will help you... :)

Best Regards,

AIOUG (All India Oracle User Group) Conference Sangam 13

The Oracle user conference, Sangam '13 is being conducted by AIOUG (All India Oracle User Group) at  Hyderabad, India on 8th and 9th November 2013. Registration is open now.

For more details please visit Sangam13 or AIOUG

Hope will see you there!!! :)

Have a nice day!!!

Managing Temp Tablespace

Here you will see how to manage temporary tablespace.

Temporary tablespace is used for Global temporary tables and sorting operations with the query that uses group by, order by, distinct etc.. which needed intermediate storage to produce the results. Global temporary tables  will release the storage either on commit or on disconnect, depending on how it was created.

In any case Temp tablespace will not shrink by itself, we have to do manually cleanup the temp tablespace.

Below are step by steps to cleanup Temperary tablespace:

1) Using below queries, find default temporary tablespace and size

   SQL> SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

   PROPERTY_NAME                        PROPERTY_VALUE     DESCRIPTION
   -------------------------------           ---------------               ----------------------------------------
   DEFAULT_TEMP_TABLESPACE   TEMP002                     Name of default temporary tablespace

   SQL> SELECT tablespace_name, file_name, bytes FROM dba_temp_files WHERE tablespace_name like 'TEMP%';

   TABLESPACE_NAME       FILE_NAME                                               BYTES
   ------------------------        -----------------                                           ---------
   TEMP002                           /Oracle/oradata/Prod/TEMP0002.DBF          10737418240

   SQL> select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;

   TABLESPACE_NAME                BYTES_USED     BYTES_FREE
   ------------------------------        ----------------      ----------
   TEMP002                                    10737418240          0

2) Create new temporary tablespace and make it as a default 

   SQL> CREATE TEMPORARY TABLESPACE TEMP01 TEMPFILE '/Oracle/oradata/Prod/TEMP01.DBF' SIZE 100M autoextend on;

   Tablespace created.

   SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP01;

   Database altered.

3) Crosscheck whether default temp tablespace changed or not

   SQL> SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

   PROPERTY_NAME                         PROPERTY_VALUE     DESCRIPTION
   --------------------------                    ---------------               --------------------------------------
   DEFAULT_TEMP_TABLESPACE     TEMP01                         Name of default temporary tablespace

4) Now drop OLD temp tablespace.

   SQL> SELECT tablespace_name, file_name, bytes FROM dba_temp_files WHERE tablespace_name like 'TEMP%';

   TABLESPACE_NAME    FILE_NAME                                                  BYTES
   ------------------------     -----------                                                       ------------
   TEMP002                         /Oracle/oradata/Prod/TEMP0002.DBF           10737418240         
   TEMP01                          /Oracle/oradata/Prod/TEMP01.DBF                104857600

   SQL> select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;

   TABLESPACE_NAME                BYTES_USED        BYTES_FREE
   ------------------------------           ----------                 ----------
   TEMP01                                       27262976               77594624
   TEMP002                                     10737418240          0

   SQL> DROP TABLESPACE TEMP002  INCLUDING CONTENTS AND DATAFILES;

Thats it.

Hope this will help you... :)

Best Regards,

Oracle Enterprise Linux 5.7 installation on Oracle VirtualBox

Follow below Step by Steps pictorial guide to installing OEL 5.7 on Oracle Virtual Box

Download VirtualBox
Download Oracle Enterprise Linux 5.7 64-bit

Select New virtual machine and click next

Select Linux operating system and select Oracle 64 bit version

 Select 2GB RAM

 Select new hard disk

Select VMDK

Select Dynamically allocated storage

Create New virtual machine

 

Select reboot

Install guest additions

Copy guest addition as shown below

Eject the guest addition CD

Mount Linux software

Go to Server directory in Linux Software and install the selected package

Go to Guest Additions software location and install Guest Additions now

 Once reboot the operating system now. Completed Oracle Enterprise Linux 5.7 installation on Oracle VirtualBox.

Hope this will help you... :)

Best Regards,