Drop database including backups using RMAN

Prior to 10g, dropping a database consisted of shutting the instance down and manually removing its associated datafiles, logfiles, controlfiles and associated init.ora file at operating system level. You might also want to remove any reference to it in /etc/oratab or /var/opt/oracle/oratab.

It could be a problem to get rid of all the Backup information in the RMAN CATALOG after you had deleted a database with DBCA for instance. You had to manually remove all the records from the catalog.
Starting with Oracle 10gR1 we can now use RMAN to drop a database and remove all its records from the RMAN CATALOG.

Here is a demo how it works:

Login to the target database server and make sure you export the correct ORACLE_SID

$Export ORACLE_SID=target_db_name

$ rman target / catalog rman_user@rman_db

RMAN> startup mount

RMAN> sql ‘alter system enable restricted session’;

RMAN> drop database including backups;

Database name is “ORCL” and DBID is 1114494726
Do you really want to drop all backups and the database (enter YES or NO)? Yes
…
database name is “ORCL” and DBID is 1114494726
database dropped
database name is “ORCL” and DBID is 1114494726
database unregistered from the recovery catalog

RMAN> exit

RMAN will not delete the entry in the /etc/oratab, so we have to do this either by editing /etc/oratab or by using dbca to delete the service

Change database name using DBNEWID utility

Make a whole database backup before changing the database name.

Invoke SQL*Plus and connect as a user with SYSDBA privileges.

          $sqlplus / as sysdba

Issue the following query to determine the current database name:

SQL>SELECT name FROM v$database;

Shut down the instance using the NORMAL, IMMEDIATE, or TRANSACTIONAL.

SQL>SHUTDOWN IMMEDIATE

Start the instance and mount the database.

SQL>STARTUP MOUNT

Invoke the DBNEWID utility on the command line, specifying a valid user with the SYSDBA privilege. You must specify the DBNAME parameter and supply your new database name. You must also specify the YES value for the SETNAME parameter to indicate that only the DBNAME is to be changed. DBNEWID performs validations of the headers of the control files, but 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, and exits. After DBNEWID completes successfully, the database is left mounted but is not yet usable.

$nid TARGET=username/password@alias DBNAME=newdbname SETNAME=YES LOGFILE=db_rename.log

After DBNEWID successfully changes the database name, shut down the instance:

SQL>SHUTDOWN IMMEDIATE

Change the DB_NAME initialization parameter to your new database name.

          $vi init$ORACLE_SID.ora

          $DB_NAME=new_db_name

Create a new password file using the ORAPWD utility:

$orapwd file=orapw password= entries=

Start the instance and open the database:

SQL>STARTUP

Verify the change to the database name by issuing the following query:

SQL>SELECT name FROM v$database;

We can check the options used for DBNEWID from below command

          $nid help=y

Install Oracle Internet Directory 10g

Uncompress the files 

        #cpio -icd < disk1.cpio

        #cpio -icd < disk2.cpio

        #cpio -icd < disk3.cpio

        #cpio -icd < disk4.cpio

Rename existing tnsnames.ora file

       $mv tnsnames.ora tnsnames.bkp

Unset some of the environment variables

        $unset TNS_ADMIN

Set the DISPLAY variable if not installing directly on server.

       $export DISPLAY=host-ip:0.0

Start the installation after  navigating to DISK1 directory which you extracted in the previous step.

     $cd Disk1

      $./runInstaller

Click Next in the Welcome Screen

Specify the Name of the installation
and location to install the software.

Click Next.

Select the option Oracle Application
server infrastructure 10g.

Click Next.

Select the option Identity management
and Metadata repository.


Click Next.

Make sure all the checks
are successful if not fix them before
proceeding to the next step.


Click Next.

Select the checkbox that
pre installation requirements are met.


Click Next.

Select the components that you wish

to install and configure.

Click Next

Select the method of port configuration.
In my case i choose Automatic.


Click Next.

Select the type of Namespace for OID.
Go with the default option.


Click Next.

Mention Global DB name and SID for the
OID instance.



Click Next.

Confirm the password for the
database schema's. You Can
either go with different passwords
or same password for all accounts.


Click Next.

Mention the Instance Name and
Ias_admin password.



Click Next

Go through the summary of the
Installation.


Click Install

You need to run the root.sh script
as root user from another session and
click OK after you done with root.sh.

All the components you selected earlier will
be configured during this stage.




Click Next

Keep note of the summary displayed
at the end of the installation.



Click Exit.

Database creation using DBCA

Before stating the dbca you need to set the display variable.

#export DISPLAY=host-ip:0.0

#xhost +

You can start the DBCA using the below command

$dbca

Click Next on the welcome screen

select create database option.


Click Next.

                                                              

Select the type of database template to create a database.



Click Next.

Mention the Global DB Name and
SID for the database.



Click Next.

Select the check box if OEM need
to be configured.



Click Next.

Confirm the password for each user or you can mention same password for all the users.



Click Next.

Select the type of storage for the database files. In my case i used Unix file systems.


Click Next.

Specify the location to store the database files.






Click Next.

Select the check box "specify flash recovery area" to store archive logs, backups etc.

To keep the database in archive log mode select the check box "Enable archive log"


Click Next.

Select the check box if we need the sample schema's.






Click Next.

Allocate the memory for SGA components.

Click on the "All initialization parameters" button to check all the parameters.

Click Next.

check the physical location of the database files.

Click Next.

Select the check box "create database" and click finish.




Click Next.

click ok.

Database creation in progress.  If you need to stop the DB creation click on stop button.














Click Exit.

Rename an Oracle instance

In this document I’m renaming the instance name “TEST” as “DEV”:

1. Check the instance name before we rename

              SQL>Select instance_name from v$instance;

                       instance_name

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

                        TEST

2. Shutdown the instance which we desired to rename.

              SQL>shutdown immediate

              Note: Database must be shutdown with SHUTDOWN NORMAL or SHUTDOWN IMMEDIATE.

3. Modify the files profile, oratab, tnsnames.ora, listener.ora with the new ORACLE_SID.
4. Go to cd $ORACLE_HOME/dbs/ and modify the initDEV.ora with the new instance_name.

             vi initDEV.ora

                 Instance_name=DEV

5. Recreate the password file with the new sid.

               $Orapwd file=$ORACLE_HOME/dbs/orapwDEV password=xxx

6. Export ORACLE_SID=DEV
6. Start up the database and verify that it works.

               SQL>startup

7. When the instance is started, the control file is updated with the new ORACLE_SID i.e. "DEV".

               SQL>Select instance_name from v$instance;

                     instance_name

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

                        DEV

Rename a database

This document explains you steps for renaming an oracle database manually:

Check the name of our database.

SQL> select name from v$database;

            NAME                                                   

            ---------

            TEST

Create a trace file of the controlfile

SQL> alter database backup controlfile to trace;      

          Above command will create a trace file of controlfile in the user_dump_dest location

          /oracle/admin/TEST/udump/TEST_ora_xxxx.trc

          Note: Take a backup of the above trace file before you edit.

                  

Shutdown the database we planned to rename.

         SQL> shutdown immediate

Modify (TEST_ora_xxxx.trc) the created trace file:

1.     Find the line reading # Set #2. RESETLOGS case

2.     Remove all lines above this line.

3.     Change the line containing the database name from

CREATE CONTROLFILE REUSE DATABASE "TEST" NORESETLOGS NOARCHIVELOG
to
CREATE CONTROLFILE SET DATABASE "DEV" RESETLOGS NOARCHIVELOG
Note: In my case, the database is running in noarchive log mode.

4.     Remove the line “RECOVER DATABASE USING BACKUP CONTROLFILE”.

5.     Remove lines starting with #.

                 After the modification it looks like this:

STARTUP NOMOUNT

CREATE CONTROLFILE SET DATABASE "DEV” RESETLOGS NOARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 2

    MAXDATAFILES 512

    MAXINSTANCES 1

    MAXLOGHISTORY 13146

LOGFILE

  GROUP 2 (

    '/ database/oradata/TEST /redo02a.log',

    ' database/oradata/TEST /redo02b.log'

  ) SIZE 25M,

  GROUP 3 (

    ' database/oradata/TEST /redo03a.log',

    ' database/oradata/TEST /redo03b.log'

  ) SIZE 25M,

  GROUP 4 (

    ' database/oradata/TEST /redo04a.log',

    ' database/oradata/TEST /redo04b.log'

  ) SIZE 25M

-- STANDBY LOGFILE

DATAFILE

  ' /database/oradata/TEST /system.dbf01',

  ' /database/oradata/TEST /s_srs_data_07',

                  .

                  .

                  .

CHARACTER SET US7ASCII

;

 Move the controlfiles away so that they can be re-created..

              $ mv /database/oradata/TEST/control.ctl   control.ctl.old 

              $ mv /database/oradata/TEST /control.ctl   control.ctl.old 

The database name must be change in the initDEV.ora:

                      db_name =DEV

                   instance_name=TEST

                  service_names= DEV           

               SQL>sqlplus / as sysdba

Run the modified control file trace.

               SQL>@ TEST_ora_xxxx.trc (modified control file trace file name)

               SQL> select name from v$database;

                               NAME

                               ---------

                               DEV