Drop a database in oracle

are you sure you want to drop your database . Be careful........................

To drop a database DBA mostly use sql prompt. Steps are basically first mount the database in exclusive mode then drop the database.


Ex:-
sql> startup mount exclusive restrict
ORACLE instance started.

Total System Global Area 209715200 bytes
Fixed Size 1248164 bytes
Variable Size 142607452 bytes
Database Buffers 62914560 bytes
Redo Buffers 2945024 bytes
Database mounted.

sql> drop database;
we can also drop the database using RMAN prompt after mounting the database in exclusive mode.
(connect to rman and issue the following)

RMAN> drop database;

database name is "TEST" and DBID is 396604920
Do you really want to drop the database (enter YES or NO)?

so, if you really want to drop the database type yes and enter.
you can easily overstep the confirmation prompt by adding "noprompt" in the above mention command.

drop database command will delete all the datafiles,control files and redo files. if you want to delete all the corresponding backups also just issue the following command.

RMAN> drop database including backups ;

Oracle Database cloning steps

A database cloning procedure is especially useful for the DBA who wants to give his developers a full-sized TEST and DEVELOPMENT  instance by cloning the PRODUCTION  instance into the development server areas.

This Oracle clone procedure can be used to quickly migrate a system from one UNIX server to another.  It clones the Oracle database and this Oracle cloning procedures is often the fastest way to copy an Oracle database.

STEP 1: On the old system, go into SQL*Plus, sign on as SYSDBA and issue: “alter database backup controlfile to trace”. This will put the create database syntax in the trace file directory. The trace keyword tells oracle to generate a script containing a create controlfile command and store it in the trace directory identified in the user_dump_dest parameter of the init.ora file. It will look something like this:

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "OLDLSQ" NORESETLOGS
NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 240
MAXINSTANCES 1
MAXLOGHISTORY 113
LOGFILE
GROUP 1 ('/u03/oradata/oldlsq/log1a.dbf',
'/u03/oradata/olslsq/log1b.dbf') SIZE 30M,
GROUP 2 ('/u04/oradata/oldlsq/log2a.dbf',
'/u04/oradata/oldlsq/log2b.dbf') SIZE 30M
DATAFILE
'/u01/oradata/oldlsq/system01.dbf',
'/u01/oradata/oldlsq/mydatabase.dbf'
;
# Recovery is required if any of the datafiles are restored
# backups, or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# Database can now be opened normally.
ALTER DATABASE OPEN;
 

STEP 2: Shutdown the old database

STEP 3: Copy all data files into the new directories on the new server. You may change the file names if you want, but you must edit the controlfile to reflect the new data files names on the new server.

rcp /u01/oradata/oldlsq/* newhost:/u01/oradata/newlsq
rcp /u01/oradata/oldlsq/* newhost:/u01/oradata/newlsq
rcp /u03/oradata/oldlsq/* newhost:/u03/oradata/newlsq
rcp /u04/oradata/oldlsq/* newhost:/u04/oradata/newlsq
 

STEP 4: Copy and Edit the Control file – Using the output syntax from STEP 1, modify the controlfile creation script by changing the following:

Old:

CREATE CONTROLFILE REUSE DATABASE "OLDDB" NORESETLOGS

New:

CREATE CONTROLFILE SET DATABASE "NEWDB" RESETLOGS

STEP 5: Remove the “recover database” and “alter database open” syntax from control file.

# Recovery is required if any of the datafiles are restored
# backups, or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# Database can now be opened normally.
ALTER DATABASE OPEN;
 

STEP 6: Re-names of the data files names that have changed.

Save as db_create_controlfile.sql.

Old:

DATAFILE
'/u01/oradata/olddb/system01.dbf',
'/u01/oradata/olddb/mydatabase.dbf'

New:

DATAFILE
'/u01/oradata/newdb/system01.dbf',
'/u01/oradata/newdb/mydatabase.dbf'

STEP 7: Create the bdump, udump and cdump directories

cd $DBA/admin
mkdir newlsq
cd newlsq
mkdir bdump
mkdir udump
mkdir cdump
mkdir pfile

STEP 8: Copy-over the old init.ora file

rcp $DBA/admin/olslsq/pfile/*.ora newhost:/u01/oracle/admin/newlsq/pfile

STEP 9: Start the new database

@db_create_controlfile.sql

STEP 10: Place the new database in archivelog mode if needed.

Limitation of Flashback Database

The Flashback Database allows you to get back the entire database to a specific point-in-time.
It is best used as a replacement for incomplete recovery of a complete database. The main benefit of the Oracle Flashback Database over incomplete database recovery is that the Flashback Database is much quicker and more efficient. The Flashback Database is based on flashback logs.

It has some limitations such as,

•Flashback Database can only undo changes to a datafile made by an Oracle database. If any media failure occurs in database then flashback database can't be used. That means if a datafile is dropped then we can't use flashback feature.

•If you have shrink your any datafile then flashback database can't be used.

•If the database control file is restored from backup or re-created, then you cannot use FLASHBACK DATABASE to return to a point in time before the restore or re-creation of a control file.

•If you get back to a time by flashback database when nologging operation was running then it is likely happen block corruption on database.

Physical and Logical Database Limits in Oracle

Logical Database Limits in Oracle

1)CREATE MATERIALIZED VIEW definition size Limit
Maximum size: 64K Bytes

2)GROUP BY clause size Limit
Maximum length: Must fit within a single database block.

3)Indexes Limit
Maximum per table: Unlimited
Total size of indexed column: 75% of the database block size minus some overhead

4)Columns Limit
Maximum Per table: 1000
Maximum Per index (or clustered index): 32
Maximum Per bitmapped index: 30

5)Constraints
Maximum per column: Unlimited

6)Subqueries
Maximum levels of subqueries in a SQL statement in the FORM clause of top-level query: Unlimited
Maximum levels of subqueries in a SQL statement in the WHERE clause: 255

7)Partitions
Maximum length of linear partitioning key: 4 KB - overhead
Maximum number of columns in partition key: 16 columns
Maximum number of partitions allowed per table or index: 1024K - 1

8)Rows
Maximum number per table: Unlimited

9)Stored Packages
Maximum size: Limits typically range from 2000 to 3000 lines of code.

10)Trigger Cascade Limit
Maximum value: Operating system-dependent, typically 32

11)Users and Roles
Maximum: 2,147,483,638

12)Tables
Maximum per clustered table: 32 tables
Maximum per database: Unlimited

Physical Database Limits in oracle 

1)Data Block Size Limit
Minimum: 2048 bytes; must be a multiple of operating system physical block size
Maximum: Operating system dependent; Not more than 32 KB

2)Data Blocks Limit
Minimum in initial extent of a segment: 2 data blocks
Maximum per datafile: Platform dependent; typically power(2,22) - 1 blocks

3)Controlfiles Limit
Number of control files: 1 minimum; 2 or more (on separate devices) strongly recommended.
Size of a control file: Maximum of 20,000 * (database block size)

4)Datafiles Limit
Maximum per tablespace: 1022
Maximum per database: 65533

Also, limited by the DB_FILES initialization parameter for a particular instance.

5)Database extents Limit
Maximum per dictionary managed tablespace: 4 GB * physical block size.
Maximum per locally managed (uniform) tablespace: 2 GB * physical block size.

6)Database file size Limit
Limited by maximum operating system file size.

7)MAXEXTENTS Limit
Default value: Derived from tablespace default storage or DB_BLOCK_SIZE initialization parameter
Maximum: Unlimited

8)Redo Log Files Limit
Maximum number of logfiles: Limited by value of MAXLOGFILES parameter in the CREATE DATABASE statement.
Maximum number of logfiles per group: Unlimited

9)Redo Log File Size Limit
Minimum size: 50 KB
Maximum size: Operating system limit.

10)Tablespaces:
Maximum number per database: 65533

11)Bigfile Tablespaces
Number of blocks: power(2,32)

12)Smallfile Tablespaces:
Number of blocks: power(2,22)

13)External Tables file
Maximum size: OS dependent.

Process and Runtime Limits

1)Instances per database
Maximum number of cluster database instances per database: Operating system-dependent

2)Locks
Row-level: Unlimited
Distributed Lock Manager: Operating system dependent

3)SGA size
Maximum value: Operating system-dependent;
For 32 bit OS, 2GB - 4GB.
For 64 bit OS, >4GB.

4)Advanced Queuing Processes
Maximum per instance: 10

5)Job Queue Processes
Maximum per instance: 1000

6)I/O Slave Processes
Maximum per background process (DBWR, LGWR, ....): 15
Maximum per Backup session: 15

7)Sessions
Maximum per instance: 32 KB; Restricted by the PROCESSES and SESSIONS initialization parameters.

8)Global Cache Service Processes
Maximum per instance: 10

9)Shared Servers
Maximum per instance: Unlimited. Restricted by the PROCESSES and SESSIONS initialization parameters.

10)Dispatchers
Maximum per instance: Unlimited. Restricted by the PROCESSES and SESSIONS initialization parameters.

11)Parallel Execution Slaves
Maximum per instance: Unlimited. Restricted by the PROCESSES and SESSIONS initialization parameters.

12)Backup Sessions
Maximum per instance: Unlimited. Restricted by the PROCESSES and SESSIONS initialization parameters.

ORA-01450: maximum key length (3215) exceeded

Error Description
Whenever I try to rebuild index online then it fails with message ORA-00604: error occurred at recursive SQL level 1 along with ORA-01450: maximum key length (3215) exceeded. Below is the scenario.
SQL> create table tab1(a varchar2(3000),b varchar2(2000));
Table created.

SQL> create index tab1_I on tab1(a,b);
Index created.

SQL> alter index tab1_I rebuild online;
alter index tab1_I rebuild online
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01450: maximum key length (3215) exceeded

Let's now create one table with column length 3000+199=3199 bytes and see what happens.
SQL> create table tab3(a varchar2(3000),b varchar2(199));
Table created.

SQL> create index tab3_I on tab3(a,b);
Index created.

Try to rebuild it online and it works.
SQL> alter index tab3_I rebuild online;
Index altered.

Now just add extra 1 bytes on column b. And whenever we try to rebuild it online it will fail.

SQL> alter table tab3 modify b varchar2(200);
Table altered.

SQL> alter index tab3_I rebuild online;
alter index tab3_I rebuild online
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01450: maximum key length (3215) exceeded

Cause of the Problem
When creating a index the total length of the index cannot exceed a certain value. Primarily this value depends on DB_BLOCK_SIZE.
If 2K block size then maximum index key length=758
If 4K block size then maximum index key length=1578
If 8K block size then maximum index key length=3218
If 16K block size then maximum index key length=6498
How the maximum index key length is measured by?
Maximum index key length=Total index length (Sum of width of all indexed column+the number of indexed columns)+Length of the key(2 bytes)+ROWID(6 bytes)+the length of the rowid(1 byte)

The index key size is limited by the value of db_block_size, because a key value may not span multiple blocks. So, based on the size of the block size of index depends. In fact, it is required that any index block must contain at least TWO index entries per block.

So we can say that the maximum key length for an index will be less than half of
the DB_BLOCK_SIZE. But we know that in a block there also needed space for PCTFREE, INITRANS and space for block overhead(Block Header,ROW Directory, Table Directory, etc). After considering these bytes the actual space that can be used for the Index key is actually just over 1/3 of the DB_BLOCK_SIZE.

Solution of the Problem
The causes already indicates what might be the solutions. Solution may be,
1)Increase your database block size. Create a tablespace with bigger block size and create index on that tablespace.

2)If you have index on multiple columns then you can split index to single or 2 columns so that size does not extended over it can handle.

3)Rebuild the index without online clause. That is
ALTER INDEX index_name REBUILD;
Because The online rebuild of the index creates a journal table and index. This internal journal IOT table contains more columns in its index. This is a feature of online rebuild. This time the error arises because that current value of the initialization parameter db_block_size is not large enough to create internal journal IOT.