How to Change the DBID and/or DB_NAME of an Oracle Database

How to change the DBID (and DB_NAME)

Step 1. The database should be backed up.

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

SQL> SHUDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

Step 3. Invoke the DBNEWID utility (nid) specifying the new DBNAME (if also changing the DB_NAME) from the command line using a user with SYSDBA privilege:

 

Note: To change the database ID without changing the database name, in Step 3 do not specify the optional database name (DBNAME).

 

 

$ nid TARGET=SYS/XXXX@test DBNAME=test_db

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 PROD (DBID=86997811)  

Connected to server version 10.1.0  

Control Files in database:  

/oracle/TEST_DB/data/cf1.f  

/oracle/TEST_DB/data/cf2.f  

The following datafiles are offline clean:  

/oracle/TEST_DB/data/tbs_61.f (23)  

/oracle/TEST_DB/data/tbs_62.f (24)  

/oracle/TEST_DB/data/temp3.f (3) 

These files must be writable by this utility.  

The following datafiles are read-only:  

/oracle/TEST_DB/data/tbs_51.f (15)  

/oracle/TEST_DB/data/tbs_52.f (16)  

/oracle/TEST_DB/data/tbs_53.f (22) 

These files must be writable by this utility.  

Changing database ID from 86997811 to 1250654267 

Changing database name from PROD to TEST_DB  

Control File /oracle/TEST_DB/data/cf1.f - modified  

Control File /oracle/TEST_DB/data/cf2.f - modified  

Datafile /oracle/TEST_DB/data/tbs_01.f - dbid changed, wrote new name  

Datafile /oracle/TEST_DB/data/tbs_ax1.f - dbid changed, wrote new name  

Datafile /oracle/TEST_DB/data/tbs_02.f - dbid changed, wrote new name  

Datafile /oracle/TEST_DB/data/tbs_11.f - dbid changed, wrote new name  

Datafile /oracle/TEST_DB/data/tbs_12.f - dbid changed, wrote new name  

Datafile /oracle/TEST_DB/data/temp1.f - dbid changed, wrote new name  

Control File /oracle/TEST_DB/data/cf1.f - dbid changed, wrote new name  

Control File /oracle/TEST_DB/data/cf2.f - dbid changed, wrote new name  

Instance shut down  Database name changed to TEST_DB. 

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

Database ID for database TEST_DB changed to 1250654267. 

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

Database has been shutdown, open database with RESETLOGS option. 

Successfully changed database name and ID. 

DBNEWID - Completed successfully. 

Note: If validation is not successful, then DBNEWID terminates and leaves the target database intact, as shown in the following sample output. You can open the database, fix the error, and then either resume the DBNEWID operation or continue using the database without changing its DBID.

 

Step 4. 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=test_db SCOPE=spfile;

SQL> SHUTDOWN IMMEDIATE;

Step 5. Create a new password file

On Unix:

$ orapwd file=/oracle/product/10.2.0/db_1/dbs/pwdtest_db.ora password=xxxxxxx entries=10

Step 6. Open the database in RESETLOGS mode and resume normal use. For example:

SQL> STARTUP MOUNT;

 

SQL> ALTER DATABASE OPEN RESETLOGS;

Note: To change only the DBNAME, use the SETNAME parameter in Step 3 which tells the DBNEWID utility to only alter the database name and not the database ID, it is then not necessary to use the RESETLOGS option when opening the database.

 

 

Step 7. Backup the whole database again.

How to Create Non-Sparse TEMP files

Temp Files

Temporary data files in Oracle are a special type of data file. Oracle will use temporary files to store the intermdiate results of a large sort operation, and hash operations, as well as to store global temporary table data, or result data when there is insufficient memory to hold it all in RAM. Temp files also never have redo generated for them, though they can generate undo. Temporary data files never need to have backups done and doing so is a waste of time since upon recovery they need to be recreated.

Once of the nuances with temp files is that if the OS permits, the temporary files will be created sparse, i.e. they will not actually consume disk storage until they need to. This is mostly the case from my experience on UNIX/Linux systems and can be seen by comparing the output of df anddu or ls for the file system on which the temp files were created.

This results in temp files being created almost instantneously since no storage is allocated, however, since you can create files larger than the space you actually have available, you will quickly run into errors such as 'no more space' when that space is then being requested.

Creating non-sparse TEMP files

There are a few ways to create non-sparse files, I'm just showing the way I've used and know work.

1. Create the OS file:

$> dd if=/dev/zero of=/u02/oradata/ORCL/temp1_01.dbf bs=1024k count=1024

2. Create the TEMP tablespace and/or temp file:

SQL> CREATE TEMPORARY TABLESPACE temp1 

TEMPFILE '/u02/oradata/ORCL/temp1_01.dbf' REUSE;

Using Oracle SPFILEs

Using Server Parameter Files (SPFILEs)

SPFILEs remove the proliferation of parameter files, as well as the ability to maintain such files outside the database using text editors. The default naming convention is

spfile$ORACLE_SID.ora - UNIX/Linux environment
spfile%ORACLE_SID%.ora - Windows environment

I recommended to maintain the default location, i.e. $ORACLE_HOME/dbs, as doing otherwise defeats the simplicity SPFILEs represent. Also, moving the file elsewhere involves you telling Oracle this new location and otherwise keeping your own internal audit of that location, leading to the similar problems caused by using parameter files.

SPFILEs and Oracle RAC

When using Oracle RAC, all instances share the same SPFILE. This single SPFILE contains all the parameters for all instances with instance-specific settings identified by the SID.

*.cluster_database_instance=2
*.db_name='ORCL'
ORCL1.undo_tablespace='UNDO_ORCL1'
ORCL2.undo_tablespace='UNDO_ORCL2'

In the above example, the *.db_name='ORCL' indicates all instances using this SPFILE will be mounting a database named 'ORCL'. The ORCL1.undo_tablespace='UNDO_ORCL1' indicates the instance named ORCL1 will use that specific undo tablespace and so on.

Setting & Unsettting Values in SPFILEs

The command below shows the format of the command used to manipulate settings using an SPFILE.

ALTER SYSTEM SET parameter=value ;

Portions in <> are optional, and the presence of the pipe symbol indicates "one of the list".

The various sections are pretty straight forward so I will no go into most of them here.

• The DEFERRED option which specifies the system change is to take place for subsequent sessions only (not currently established sessions). This is important since by default changes will take effect immediately, but some parameters cannot be changed 'immediately' - they can only be changed for newly established sessions. The error received for such commands is 'ORA-02096: specified initialization parameter is not modifiable with this option'.

• The SID='sid|*' is mostly useful in a clustered environment; SID='*' is the default. This allows you to specify a parameter setting uniquely for any given instance in the cluster. However, if you need to 'unset' a parameter such that it does not show in the SPFILE and is therefore defaulted, you will need to make use of SID='sid|*' component. For example, to 'unset' the SORT_AREA_SIZE parameter:

ALTER SYSTEM RESET sort_area_size SCOPE=spfile SID='*';

The general format to 'unset' parameters is

ALTER SYSTEM RESET parameter SID='sid|*';

SPFILEs Copies

You can create backup copies of your SPFILE as either a one-time parameter file
CREATE pfile FROM spfile;

Using an OS copy command, or if on a UNIX/Linux platform you can extract your settings using:

strings spfile$ORACLE_SID.ora

On Windows you can just open in WordPad and copy and paste the text which will be formatted as clear text into another file. You can also obtain all the non-default parameters in the alert.log file since this is displayed following each instance start and build your pfile from those settings.

Installing and Configuring Enterprise Manager Client Interface (emcli)

Notes:

• Enteprise Manager Client Interface (emcli) needs to be installed on each host from which its usage is required.
• Environment was EM Grid Control 10.2.0.5
  

Installation

1. Obtain the EM CLI Client kit (emclikit.jar). The emclikit.jar file is physically located in the $OMS_HOME/sysman/jlib directory of the 10.2 Grid Control OMS home, it can also be downloaded from http(s)://host:port/em/console/emcli/download
2. Set your JAVA_HOME environment variable and ensure that it is part of your PATH. You must be running Java 1.4.1 or greater.
3. Install the EM CLI Client. You can install the client in any directory either on the same machine as the EM CLI Management Services or on any machine in your network (download the emclikit.jar to that machine). Run the followingcommand:

java -jar emclikit.jar client -install_dir=

Setup
1. Tell emcli how to find em repository with the following command

$> emcli setup -url=http://:4889/em -username=sysman

emcli will ask you for your password.

2. Tell emcli how find your sudo program (all on 1 line)

$> emcli create_privilege_delegation_setting -setting_name=sudo_setting -setting_type=SUDO -settings="SETTINGS:/usr/local/bin/sudo –S –u %RUNAS% %command%"

3. Apply this setting to your specific hosts (all on 1 line)

$> emcli apply_privilege_delegation_setting -setting_name=sudo_setting -target_type=host -target_names="host1;host2;host3;"

Setting Credentials for the Job System to work with Enterprise Manager

Windows systems require that you set the correct credentials for the Jobs system to work properly in Enterprise Manager. By default, the Management Agent service is installed as a LocalSystem user. When submitting jobs, such as stopping or starting the database, the user submitting the job must have the Log on as a batch job privilege enabled.

Perform the following steps to establish that privilege for any
operating system user who needs to submit an Enterprise Manager job.

1. Start the Local Security Policy tool:
   
   • Windows 2000: From the Start menu, select Control Panel, Administrative Tools, then Local Security Policy.
     
   • Windows 2003: From the Start menu, select Administrative Tools, then Local Security Policy.
     
   • Windows XP: From the Start menu, select Control Panel, Administrative Tools, then Local Security Policy.
     
   
   
2. Under the Security Settings list, expand the list to Local Policies.
   
3. Under Local Policies, double-click User Rights Assignment.
   
4. Under Policy, search for the Log on as a batch job policy.
   

   If the Management Agent service is installed as any other user (that is, not LocalSystem), then, in addition to granting the Log on as a batch job privilege, you must grant the "Windows service" user the following three privileges:

   
   
   • Act as part of the operating system
     
   • Adjust memory quotas for a process (This setting is named Increase memory quotas

     on Windows 2000.)

   • Replace a process level token
     
   
   
5. With each policy, perform the following steps:
   
   1. Double-click the policy name.
      
   2. In the Properties dialog box, click Add User or Group.
      
   3. In the Select Users or Groups dialog box, enter the name of the user (for example, jsmith, administrator, and so on.)
      
   4. Click Check Names to check that you have entered the name correctly.
      
   5. Click OK.
      
   
   
6. Click OK to exit the Properties dialog box, then exit Local Security Settings and Administrative Tools.
   
7. Restart your computer.
   

If a user exists locally and at the domain level, Windows gives the
local user precedence. To use the domain user, qualify the user name
with the domain name. For example, to use the user joe in the ACCOUNTS domain specify the user name as ACCOUNTS\joe.

Note: Validated for EM Grid Control 10.2.0.4 - 10.2.0.5

powered by performancing firefox

To establish user equivalence (SSH) between two servers

To establish user equivalence:

1. Create the directories for SSH:

mkdir ~/.ssh
chmod 700 ~/.ssh


2. Generate the user's public and private keys as the oracle user on all nodes in turn. Do not enter a passphrase when prompted, just press 'ENTER' 3 times to accept all defaults.

ssh-keygen -t dsa
ssh-keygen -t rsa


3. Create the authorized_keys file using both public keys for all nodes, copy the file to all nodes, and change the permissions (do this from one node only).

ssh salmon1 cat ~/.ssh/*.pub >> ~/.ssh/authorized_keys
ssh salmon2 cat ~/.ssh/*.pub >> ~/.ssh/authorized_keys
scp ~/.ssh/authorized_keys salmon1:.ssh/authorized_keys
scp ~/.ssh/authorized_keys salmon2:.ssh/authorized_keys
ssh salmon1 'chmod 600 ~/.ssh/authorized_keys'
ssh salmon2 'chmod 600 ~/.ssh/authorized_keys'


4. Visit each node in turn from every node (including itself). This ensure all nodes are added to each node's known_hosts file, and cluster operations can be run from any node in the cluster. This also serves to test the setup to ensure it is working.

[oracle@salmon1]$ ssh salmon1 'hostname ; date'
[oracle@salmon1]$ ssh salmon2 'hostname ; date'
[oracle@salmon2]$ ssh salmon1 'hostname ; date'
[oracle@salmon2]$ ssh salmon2 'hostname ; date'

Each of the above should return the date without prompting for a password.

Patching Oracle (9.2.0.6) to enable asynchronous I/O support w/RHEL

Patch Installation Instructions:
# --------------------------------
# To apply the patch, unzip the PSE container file:
#
# % unzip p3208258_9206_LINUX.zip
#
# Set your current directory to the directory where the patch
# is located:
#
# % cd 3208258
#
# Ensure that the directory containing the opatch script appears in
# your $PATH; then enter the following command:
#
# % opatch apply
#
# Patch Special Instructions:
# ---------------------------
# This patch is required to Enable asynchronous I/O support with Oracle running
# on RHEL 3. It should be applied only to 9.2.0.6.
#
# Instances under the ORACLE_HOME being patched do not need shutting
# down while opatch is run. opatch just copies libaio.so to locations in
# $ORACLE_HOME/lib/stubs.
#
# Once opatch has finished the following steps need to be completed. This
# does involve shutting down the Instances. Follow the steps below.
#
# 1. Ensure all instances in the ORACLE_HOME being patched are cleanly
# shutdown
#
# 2. cd to $ORACLE_HOME/rdbms/lib
#
# 3. Relink Oracle with asynchronous I/O enabled:
#
# % make -f ins_rdbms.mk async_on
#
# 4. Set the following init.ora parameters:
#
# set 'disk_asynch_io=true' # for raw devices and filesystem files
# set 'filesystemio_options=asynch' # for filesystem files
#
# 5. Restart All instances.

TRUNCATE privileges in Oracle

For future reference: The DROP ANY TABLE priv is required for the TRUNCATE option.

Error relinking Oracle RDBMS on Linux for Asynch IO support

Applies to:
Oracle Server - Enterprise Edition - Version: 9.2.0.4 to 9.2.0.7
Linux x86
Red Hat Advanced Server
Symptoms

While installing the 9.2.0.7.0 patchset on the linux x86 RedHat Server following error were encountered during the relink phase.

$ORACLE_HOME/install/make.log
------------------------------------------

/usr/lib/gcc/i386-redhat-linux/3.4.3/libgcc_s.so: undefined reference to `dl_iterate_phdr@GLIBC_2.2.4'
collect2: ld returned 1 exit status
make: *** [trcroute] Error 1

On checking
Note 303859.1 Requirements for Installing Oracle 9iR2 on RHEL 4
We find all the required packages has been already installed.

[root@incq038ad ~]# rpm -qa compat-oracle-rhel4
compat-oracle-rhel4-1.0-5

Cause

The problem is caused because System Administrator has installed the latest O/S updates from the RedHat.
And the Oracle patch "compat-oracle-rhel4-1.0-5" has been overwritten.

[root@incq038ad ~]# ls -l /usr/bin/gcc*
-rwxr-xr-x 2 root root 93120 May 17 01:36 /usr/bin/gcc
-rwxr-xr-x 2 root root 86364 Dec 1 2004 /usr/bin/gcc32
-rwxr-xr-x 1 root root 94784 May 17 01:36 /usr/bin/gcc.orig

[root@incq038ad ~]# rpm -qs compat-oracle-rhel4
normal /usr/bin/g++.tmp_1
normal /usr/bin/gcc.tmp_1
Solution

Please redownload the Patch 4198954 from Oracle Metalink.
And follow the instruction from the README to reapply the patch.

This time when you will reapply the patch you will receive the following error.

[root@incq035ad 4198954]# rpm -Uvh compat-oracle-rhel4-1.0-5.i386.rpm
Preparing... ########################################### [100%]
package compat-oracle-rhel4-1.0-5 is already installed

Please use the following command to reapply the patch.

[root@incq035ad 4198954]# rpm -Uvh --force compat-oracle-rhel4-1.0-5.i386.rpm
Preparing... ########################################### [100%]
1:compat-oracle-rhel4 ########################################### [100%]

[root@incq035ad bin]# ls -l /usr/bin/gcc*
-rwxr-xr-x 1 root root 914 Feb 23 2005 /usr/bin/gcc
-rwxr-xr-x 2 root root 84780 Dec 1 2004 /usr/bin/gcc32
-rwxr-xr-x 1 root root 93120 Sep 26 10:28 /usr/bin/gcc.orig

References
Note 303859.1 - Requirements for Installing Oracle 9iR2 on RHEL 4