Monday, August 3, 2009

Dropping oracle10g Database

Deleting two databases (PROD/RMAN)

1. Deleting Target database using RMAN (PROD using RMAN)

Login as PROD user:

$ sqlplus /nolog

Sql> connect sys/pwd@connect_string as sysdba

Sql> startup mount exclusive

Sql> alter system enable restricted session

Sql> exit

Login as rman user

$ rman target sys/pwd@connect_string catalog rman/pwd@rman

RMAN> drop database including backup;

2. Deleting RMAN Database:

i. Connect to drop catalog

$ connect catalog rman/pwd@rman

RMAN> drop catalog;

RMAN> exit

ii. Check Datafile, Controlfile and Redo logs;

SQL> select name from v$datafile/ v$logfile / v$controlfile;

iii. Drop Database

SQL> startup mount exclusive restrict;
SQL> drop database;

3. Removing Oracle10g Software:

Login as software owner

Run /bin/runinstaller and uninstall oracle software

Restrictions and Usage Notes (FROM ORACLE DOCS)

  • This command can only be run from RMAN.
  • You must be connected to the target database from RMAN while the database is mounted in EXCLUSIVE mode with RESTRICTED SESSION enabled.
  • When using the "DROP DATABASE" command, RMAN drops the target database including the following files at the operating system level:
    • Datafiles
    • Online Redo Log Files
    • Controlfiles
    • SPFILE (if it exists)
  • When including the "INCLUDING BACKUPS" clause, RMAN will delete the files listed above as well as the following files at the operating system level:
    • Archive Redo Logs
    • Backup pieces generated by RMAN for the target database
  • When using the "DROP DATABASE" command with RMAN connected to a recovery catalog, RMAN will unregister the target database.
  • The "DROP DATABASE" command does not delete the following files:
    • init.ora (text version of the Oracle initialization file)
    • password file

Dropping 9i Database (PROD/RMAN)

1. Drop backups using rman

rman target=sys/pwd@connect_string catalog rman/pwd@rman

RMAN> LIST BACKUP SUMMARY;

RMAN> DELETE BACKUP
 2. Unregister database

SQL> CONNECT rman/rman@rman Connected.

SQL> SELECT db_key, db_id FROM db;


DB_KEY DB_ID

1 1465981354

SQL> EXECUTE dbms_rcvcat.unregisterdatabase(1, 1465981354);

PL/SQL procedure successfully completed.

3. Drop rman database

Use dbca to drop database

4. Drop prod database

Use dbca to drop database

Note: If Database is created manually without using DBCA utility then add the entry in ORATAB for DBCA to identify the database

5. Removing 9i Software:

Login as software owner

Run /install/runinstaller and uninstall oracle software

Registering Manual Database with DBCA

Database created manually using traditional CREATE DATABASE command cannot be identified using DBCA Utility for following purposes

Create or Manage templates

Drop database

Change database options

The DBCA will not identify SID for manually created database unless the entry is added in ORATAB file.

Sample:

$ vi /var/opt/oracle/oratab

prod:/u00/app/oracle/product/9.2.0.1.0:N

Drop Database using DBCA

When you drop database using DBCA it will create ShutdownInst.log in $ORACLE_HOME/assistants/dbca/logs; If this file already exists then drop database might fail. So move the old file and run DBCA again.

Sample:

$ more ShutdownInst.log

/u00/app/oracle/oradata/prod/control01.ctl

/u00/app/oracle/oradata/prod/control02.ctl

/u00/app/oracle/oradata/prod/control03.ctl

/u01/prod/database/drsys_prod.dbf

/u01/prod/database/log1a_prod.rdo

/u01/prod/database/log2a_prod.rdo

/u01/prod/database/log3a_prod.rdo

/u01/prod/database/log4a_prod.rdo

/u01/prod/database/system_prod.dbf

/u01/prod/database/tools_prod.dbf

/u01/prod/database/xdb_prod.dbf

/u02/prod/example_prod.dbf

/u02/prod/users01_prod.dbf

/u02/prod/users02_prod.dbf

/u03/prod/log1b_prod.rdo

/u03/prod/log2b_prod.rdo

/u03/prod/log3b_prod.rdo

/u03/prod/log4b_prod.rdo

/u04/prod/control04.ctl

/u04/prod/index01_prod.dbf

/u05/prod/temp01_prod.dbf

/u05/prod/undotbs01_prod.dbf

Database closed.

Database dismounted.

ORACLE instance shut down.