Changing the current Database Name and DB ID

CHANGING DBNAME & DBID
-----------------------

SQL>SHUTDOWN IMMEDIATE
SQL>STARTUP MOUNT

$nid TARGET=sys/sys@swdm DBNAME=swm


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

SQL>SHUTDOWN IMMEDIATE

Modify the DB_NAME parameter in the initialization parameter file. The startup will result in an error but proceed anyway.

SQL>STARTUP MOUNT
SQL>ALTER SYSTEM SET DB_NAME=SWM SCOPE=SPFILE;
SQL>SHUTDOWN IMMEDIATE

Create a new password file:

$cd /u01/app/oracle/product/10.1.0/db_1/dbs/

$orapwd file=/u01/app/oracle/product/10.1.0/db_1/dbs/orapw password=sys entries=10

If you are using UNIX/Linux simply reset the ORACLE_SID environment variable:

$ORACLE_SID=SWM; export ORACLE_SID

Rename the SPFILE to match the new DBNAME.

If you are using Windows you must recreate the service so the correct name and parameter file are used:

oradim -delete -sid SWDM
oradim -new -sid SWM -intpwd password -startmode a -pfile c:\oracle\920\database\spfileSWM.ora

Alter the listener.ora and tnsnames.ora setting to match the new database name and restart the listener:

$lsnrctl reload

Open the database with RESETLOGS:

SQL>STARTUP MOUNT
SQL>ALTER DATABASE OPEN RESETLOGS;

Backup the database.

==========================================================================
CHANGING DBNAME Only
---------------------

Repeat the process as before except use the following command to start the DBNEWID utility:

$nid TARGET=sys/sys@SWDM DBNAME=swm SETNAME=YES

SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP MOUNT;
SQL>ALTER SYSTEM SET DB_NAME=SWM SCOPE=spfile;
SQL>SHUDOWN IMMEDIATE;

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

On Unix:
$ orapwd file=/u01/app/oracle/product/10.1.0/db_1/dbs/orapw password=sys entries=10

SQL>STARTUP;
SQL>SHOW PARAMETER DB_NAME

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

Backup the database.
===========================================================================

CHANGING DBID Only
-------------------
Backup the database.
Mount the database after a clean shutdown:

SQL>SHUTDOWN IMMEDIATE
SQL>STARTUP MOUNT

Invoke the DBNEWID utility (nid) from the command line using a user with SYSDBA privilege. Do not specify a new DBNAME:

$nid TARGET=sys/password@SWDM

Shutdown and open the database with RESETLOGS:

SQL>SHUTDOWN IMMEDIATE
SQL>STARTUP MOUNT
SQL>ALTER DATABASE OPEN RESETLOGS;

Backup the database.

Resize undo tablespace

You created undo tablespace using AUTOEXTEND ON MAXSIZE UNLIMITED to avoid error.

Your have just done purging (deleting millions of rows) on your production database and you noticed that your undo tablespace datafile is huge in size.

You tried to use “ALTER DATABASE DATAFILE .. RESIZE”, and this command failed with ORA-3297 error. , i.e.: “file contains ~~ blocks of data beyond requested RESIZE value”.

So you should drop and recreate undo tablespace using the following commands:

1, Connect to Oracle

sqlplus /nolog
connect / as sysdba

2, Find out which undo tablespace is being used:

SQL>select name,value from v$parameter where name in ('undo_management','undo_tablespace');

NAME VALUE
——————- ——————-
undo_management AUTO
undo_tablespace UNDOTBS1

3, Create new undo tablespace UNDOTBS2 with smaller size.

SQL> create undo tablespace UNDOTBS2 datafile '/u01/app/oracle/oradata/edr/undotbs02.dbf' size 500m reuse;


4, Tell Oracle to use new undo tablespace.

SQL> alter system set undo_tablespace=UNDOTBS2;

5, Now you can safely drop and recreate tablespace UNDOTBS1.

SQL> drop tablespace UNDOTBS1 including contents and datafiles;

Tablespace dropped.

6, SQL> CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE '/u01/app/oracle/oradata/edr/undotbs01.dbf' SIZE 250m reuse AUTOEXTEND ON ;

Tablespace created.

7, SQL> alter system set undo_tablespace=UNDOTBS1;

8, Now you can safely drop tablespace UNDOTBS2.

SQL> drop tablespace UNDOTBS2 including contents and datafiles;

Tablespace dropped.

Recreate Temp tablespace

You created temp tablespace using AUTOEXTEND ON MAXSIZE UNLIMITED to avoid error.
On your production database you noticed that your temp tablespace datafile is huge in size. So you should drop and recreate temp tablespace using the following commands,

CREATE TEMPORARY TABLESPACE temp2
TEMPFILE '/u01/app/oracle/oradata/edr/temp02.dbf' SIZE 5M REUSE
AUTOEXTEND ON NEXT 1M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;
------------------------------------------------------------------------------------------
CREATE TEMPORARY TABLESPACE temp
TEMPFILE '/u01/app/oracle/oradata/edr/temp01.dbf' SIZE 500M REUSE
AUTOEXTEND ON NEXT 100M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
------------------------------------------------------------------------------------------
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;