Changing an Oracle Database Name in Oracle 10g

As per the requirement for our development team, we needed to perform a fresh installation of Oracle 10g and Creation of a database on Solaris 10, and import the production data. During the database creation, we had to use the same scrip what we used for Production Database Creation. After the database creation of database for development environment we realized that the Database name is also same as Production Database. So, we had used the oracle DBNEWID utility to change the Database Name to avoid the confusion between Prod DB and Dev DB.

Below is the snapshot the task.

Make sure that you have a whole database backup.

Note: In our case, we had not taken the backup as the database was created recently and we had an export dump of a production DB.

Ensure to perform clean shut down of a database and startup in mount stage (but not open).

SQL> SHUDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

Invoke the DBNEWID utility (nid) specifying the new DBNAME from the command line using a user with SYSDBA privilege:

$ nid TARGET=SYS/XXXX@unc DBNAME=uncdev SETNAME=YES

DBNEWID performs validations in the headers of the control files (not the data files) before attempting I/O to the files. If validation is successful, then DBNEWID prompts for confirmation, changes the database name in the control files, shuts down the database and exits.

DBNEWID: Release 10.2.0.1.0 - Production on Mon Mar 26 20:04:26 2007

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

Connected to database UNIC (DBID=3680129277)

Connected to server version 10.2.0

Control Files in atabase:

/s/oracle10g/product/10.2.0/uncdev/oradata/control01.ctl
/s/oracle10g/product/10.2.0/uncdev/oradata/control02.ctl
/s/oracle10g/product/10.2.0/uncdev/oradata/control03.ctl

Change database ID and database name UNIC to UNCDEV? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 3680129277 to 975738954
Changing database name from UNIC to UNCDEV Control File /s/oracle10g/product/10.2.0/uncdev/oradata/control01.ctl - modified Control File /s/oracle10g/product/10.2.0/uncdev/oradata/control02.ctl - modified Control File /s/oracle10g/product/10.2.0/uncdev/oradata/control03.ctl - modified Datafile /s/oracle10g/product/10.2.0/oradata/system01.dbf - dbid changed, wrote new name Datafile /s/oracle10g/product/10.2.0/oradata/undotbs01.dbf - dbid changed, wrote new name Datafile /s/oracle10g/product/10.2.0/oradata/sysaux01.dbf - dbid changed, wrote new name Datafile /s/oracle10g/product/10.2.0/oradata/users01.dbf - dbid changed, wrote new name Datafile /s/oracle10g/product/10.2.0/oradata/temp01.dbf - dbid changed, wrote new name Control File /s/oracle10g/product/10.2.0/uncdev/oradata/control01.ctl - dbid changed, wrote new name Control File /s/oracle10g/product/10.2.0/uncdev/oradata/control02.ctl - dbid changed, wrote new name Control File /s/oracle10g/product/10.2.0/uncdev/oradata/control03.ctl - dbid changed, wrote new name

Instance shut down
Database name changed to UNCDEV.
Modify parameter file and generate a new password file before restarting.
Database ID for database UNCDEV changed to 975738954.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Successfully changed database name and ID.
DBNEWID - Completed successfully.

Connect to SQL* Plus and shutdown the database

SQL> SHUDOWN IMMEDIATE;

Change DB_NAME initialization parameter in the initialization parameter file (PFILE) to the new database name.

SQL> STARTUP MOUNT;

SQL > ALTER SYSTEM SET DB_NAME=uncdev SCOPE=spfile;

SQL> SHUDOWN IMMEDIATE;

Create a new password file

On Windows:
C:\> orapwd file= C:\oracle\product\10.2.0\db_1\database\pwdUNCDEV.ora password=xxxxxxx entries=10

On Unix:
$ orapwd file= /s/oracle10g/product/10.2.0/uncdev/dbs/ pwdUNCDEV.ora password=xxxxxxx entries=10

We have changed only the database name, The SETNAME parameter tells the DBNEWID utility to only alter the database name. and not the database ID, it is not necessary to use the RESETLOGS option when you open the database

SQL> STARTUP;

SQL> SHOW PARAMETER DB_NAME

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string UNCDEV

Backup the whole database again

Conclusion: The above series of steps are only for changing the database name, if you wanna change Database ID, or Database ID and Database Name both, please refer the below URLs.

http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14215/dbnewid.htm
http://www.oracle-base.com/articles/9i/DBNEWID.php

Regards,
Sabdar Syed.

Upgrading Oracle Database Server 10g Release 10.2.0.1 to Oracle Database 10g Release 10.2.0.3 on Linux/Fedora core 6

We had recently upgraded one of our test databases from 10.2.0.1 to 10.2.0.3 on Fedora Core 6. I would like to share the steps followed for this task.

Note: This is only for Oracle Database Server Upgrade.
If you have Oracle E-Business Suite, Physical/Logical Standby database, RAC, and Other separate options (i.e. RMAN) enabled in place, please read the README.html document included in the Patchset (5337014).

Details:

Operating Version Details: Linux localhost.localdomain 2.6.18-1.2798.fc6 #1 SMP Mon Oct 16 14:54:20 EDT 2006 i686 i686 i386 GNU/Linux
Oracle Version Details (Current) : Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
Oracle Instance Name: testdb
Patchset (to be applied on) : p5337014_10203_LINUX.zip (Downloaded from http://www.metalink.oracle.com/)

Requirements for installing the Patchset # 5337014

Oracle Universal Installer Version Requirements
The Oracle Universal Installer release must be 10.1.0.3 (This includes in the Patchset).

System Requirements
* Operating System must be redhat-3, SuSE-9, SuSE-10, redhat-4, UnitedLinux-1.0, asianux-1 or asianux-2
Note: Installation/Upgrade of Oracle 10g on Fedora Core 6 Is not supported by Oracle, but as a workaround, we had to change the the /etc/redhat-release file replacing the current release information (Fedora Core release 6 (Zod)) with redhat-4
* Oracle Database 10g (10.2.0.1) or later

Preinstallation Tasks

To download and extract the patch set installation software:
* Download the p5337014_10203_LINUX.zip patch set installation archive to a directory "/home/oracle/patches" that is not the Oracle home directory or under the Oracle home directory.
* To unzip and extract the installation files:
$ unzip p5337014_10203_LINUX.zip

Set the ORACLE_HOME and ORACLE_SID Environment Variables
Bourne, Bash, or Korn shell:·
$ ORACLE_HOME=/u01/app/oracle
$ ORACLE_SID=testdb·
$ export ORACLE_HOME ORACLE_SID
C shell:·
% setenv ORACLE_HOME /u01/app/oracle
% setenv ORACLE_SID testdb
The oracle_home is the Oracle home directory where the Oracle10g installation that you want to upgrade is installed, and sid is the SID of the database that you want to upgrade.


Stopping All Processes for a Single Instance Installation
### To stop Listener Service
$lsnrctl
LSNRCTL> stop
### To stop Enterprise Manager Service
$ emctl stop dbconsole
### To stop iSQLPlus Service
$ isqlplusctl stop
### To Shut Down Oracle Databases
$ sqlplus /nolog
$ connect /as sysdba
SQL> SHUTDOWN IMMEDIATE;

Back Up the System
Oracle recommends that you create a backup of the Oracle10g installation before you install the patch set.We took the backup of Oracle home and all the database files.

Installation Tasks

Installing the Oracle Database 10g Patch Set Interactively
** Log in as the oracle user.
su - oracle
password:
** If you are not installing the software on the local system then, run the following command on remote machine:
Bourne, Bash, or Korn shell:
$ export DISPLAY=localhost:0.0
C shell:
% setenv DISPLAY local_host:0.0
Now to enable X applications, run the following command on the machine that you want to use to display Oracle Universal Installer (your workstation or PC):
$ xhost + [fully_qualified_remote_host_name]
** To start Oracle Universal Installer, where patchset_directory is the directory where you unpacked the patch set software
$ cd /home/oracle/patches/Disk1
$ ./runInstaller
** On the Welcome screen, click Next.
** In the Specify Home Details screen, select the name of the Oracle home that you want to update, or select the Path that you want to update from the list, then click Next.
** On the Summary screen, click Install.
This screen lists all of the patches available for installation.
** When prompted, run the $ORACLE_HOME/root.sh script as the root user.
** On the End of Installation screen, click Exit, then click Yes to exit from Oracle Universal Installer.

Postinstallation Tasks

Upgrading of Oracle 10g Software from Release 10.2.0.1 to 10.2.0.3 is now successfully done. Now, earlier Oracle Database needs to be upgraded from 10.2.0.1 to 10.2.0.3. Oracle recommends two ways to upgrade the database, one is using DBUA (Database Upgrade Assistance), and another is Manual database upgrade. Here we have followed manual method for upgrade.

Manually Upgrading a Release 10.2 Database

* Required Preupgrade Checks
** Check the SYSTEM Tablespace Size
SQL> SELECT sum(bytes/1024/1024) "Size in MB"
2 FROM dba_free_space
3 WHERE tablespace_name='SYSTEM';

Size in MB
----------
97.125

We have enough free space in SYSTEM tablespace.
If not enough free space in SYSTEM tablespace, then add/resize the datafile as below.
## TO add a datafile
ALTER TABLESPACE SYSTEM
ADD ' /u02/app/oradata/unic/system02.dbf'
SIZE 100M;
## To resize a existing datafile.
ALTER DATABASE DATAFILE ' /u02/app/oradata/unic/system01.dbf'
RESIZE 500M;

* Set the SHARED_POOL_SIZE and JAVA_POOL_SIZE Initialization Parameters
##Start the database with the NOMOUNT option:
SQL> STARTUP NOMOUNT
## To check initialization parameter file (initsid.ora or spfilesid.ora)
SQL> SHOW PARAMETER PFILE;
## To determine the current values of these parameters:
SQL> SHOW PARAMETER SHARED_POOL_SIZE
SQL> SHOW PARAMETER JAVA_POOL_SIZE
##If the system is using a server parameter file:
If necessary, set the value of the SHARED_POOL_SIZE initialization parameter to at least 150 MB:
SQL> ALTER SYSTEM SET SHARED_POOL_SIZE='150M' SCOPE=spfile;
If necessary, set the value of the JAVA_POOL_SIZE initialization parameter to at least 150 MB:
SQL> ALTER SYSTEM SET JAVA_POOL_SIZE='150M' SCOPE=spfile;
##If the system uses an initialization parameter file
If necessary change the values of the SHARED_POOL_SIZE and the JAVA_POOL_SIZE initialization parameters to at least 150 MB in the initialization parameter file (initsid.ora).

Shut down the database:
SQL> SHUTDOWN

Upgrading a Release 10.2 Database

##Login as Oracle User
Su – oracle
Password:
##Start the Listener
$lsnrctl
LSNRCTL> START
##Connect to sqlplus as sysdba and start the database in upgrade mode
$sqlplus /nolog
SQL> connect /as sysdba
SQL> STARTUP UPGRADE;
##Run the catupgrd.sql scrips

SQL> SPOOL patch.log

SQL> @?/rdbms/admin/catupgrd.sql

SQL> SPOOL OFF;

Review the patch.log file for errors and inspect the list of components that is displayed at the

end of catupgrd.sql script.

## Restart the database:

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP;

Run the utlrp.sql script to recompile all invalid PL/SQL packages now instead of when the packages are accessed for the first time.

SQL> @?/rdbms/admin/utlrp.sql

Start All Oracle Related Services

To Check Current Oracle Version

SQL> select * from v$version;
BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - ProdPL/SQL Release 10.2.0.3.0 - ProductionCORE 10.2.0.3.0 ProductionTNS for Linux: Version 10.2.0.3.0 - ProductionNLSRTL Version 10.2.0.3.0 - Production

Common Errors:

** If you attempt to install this patch set in an Oracle home directory that does not contain an Oracle Database 10g release 10.2.0.1 or 10.2.0.2 installation, Oracle Universal Installer displays a warning dialog with the following error:

OUI-10091: There are no patches that need to be applied from the patch set Oracle Database 10g Release 2 Patch Set 2
10.2.0.3

The Oracle Universal Installer does not allow the installation to proceed. Click OK, then click Cancel to end the installation.

** If you do not run the Oracle Database Upgrade Assistant as described in this section, then the following errors are displayed:
ORA-01092: ORACLE instance terminated.
ORA-39700: database must be opened with UPGRADE option.

$sqlplus /nolog
SQL> connect /as sysdba
SQL> STARTUP UPGRADE;

Reference:

Follow the README.html document of patch set # 5337014

Regards,

Sabdar Syed.

WARNING: "Memory Notification: Library Cache Object Loaded Into SGA"

Dear Friends,

In one of our Databases, we have found that the WARNING message "Memory Notification: Library Cache Object Loaded Into SGA" is being written continuously in alert log file. As per the Oracle Support Documentation, These are warning messages generated in Oracle 10g (Release 2).

To prevent or avoid these messages being generated, we need to adjust the size of a hidden initialization parameter "_kgl_large_heap_warning_threshold" to a high value or zero. Note: In 10.2.0.2, the threshold is increased to 50MB after regression tests, so this should be a reasonable and recommended value.

To find out a hidden parameter value in the database.

SELECT a.ksppinm "Parameter",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value"
FROM x$ksppi a, x$ksppcv b, x$ksppsv c
WHERE a.indx = b.indx AND
a.indx = c.indx AND
a.ksppinm LIKE '%kgl_large_heap%';

Parameter Session Value Instance Value
------------------------------------ -------------------- --------------------_kgl_large_heap_warning_threshold 2097152 2097152

To change the hidden parameter value.

If you are using spfile, login as sysdba

$sqlplus /nolog
SQL> connect /as sysdba
SQL> alter system set "_kgl_large_heap_warning_threshold"=8388608 scope=spfile ;

System altered.

Or if you are using "old-style" init prameter file

Then, edit the initialization parameter file and add the below line.

_kgl_large_heap_warning_threshold=8388608

Shut down and re-start the database after changes

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP;

SQL> show parameter _kgl_large_heap_warning_threshold

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------_kgl_large_heap_warning_threshold integer 8388608

Regards,
Sabdar Syed.

Database Cloning Mistake !!!!!

Dear Friends,

The following errors were generated continuously in the test database alert log file.

1). ORA-25153: Temporary Tablespace is Empty

2). GATHER_STATS_JOB encountered errors. Check the trace file.

3). ORA-20000: Unable to analyze TABLE "SIRSI"."HEADING4", insufficient privileges or does not exist.

But when we query to see the information about temporary tablespace and tempfiles in the database, the following findings were observed.

## To check TEMP tablespace and tempfile details.

SQL> SELECT file_name, bytes FROM dba_temp_files;

no rows selected

SQL> SELECT * FROM v$tempfile;

no rows selected

SQL> SELECT * FROM v$tablespace WHERE name='TEMP';

TS# NAME INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
3 TEMP NO NO YES

SQL> SELECT tablespace_name, contents FROM dba_tablespaces WHERE tablespace_name='TEMP';

TABLESPACE_NAME CONTENTS
------------------------------ ---------
TEMP TEMPORARY

SQL> SELECT DISTINCT temporary_tablespace FROM dba_users;

TEMPORARY_TABLESPACE
------------------------------
TEMP

## Gather Schema Statistics.

In Oralce 10g, when the data of a table is grown to certain threshold value i.e. 10% of data, Automatically Gather Schema Statistics is performed on those tables.And also it needs enough free space in the temporary tablespace for sorting. Due to this reason above messages generated in the alert log file.

Cause: The TEST Database has recently been cloned from PRODuction Database. So, we realized that the step, adding a tempfile to the temp tablespace, was missed mistakenly. In fact the tempfile was copied and it is physically existing in the server, but not added to the TEMP tablespace as part of the cloning.

Solution:

ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/product/10.2.0/oradata/test/temp01.dbf' REUSE;

After adding a tempfile to the TEMP tablespace, no errors generated regarding Gather Schema Statistics and temp tablespace afterward !!!.

Note: if you are receiving an error "ORA-20000: Unable to analyze TABLE "SIRSI"."HEADING4", insufficient privileges or does not exist." due to other reasons. Please take a look at the Oracle Metalink Note ID: 333966.1

This article, written by me, is also available in Oracle Metalink. Note ID: 427974.1

Best Regards,
Sabdar Syed.