Master Oracle Database Creation, Deletion, Backup, and Restore Commands

1. Oracle Database Creation and Deletion Commands

For Oracle 11g a typical silent creation uses dbca -silent -createDatabase with the template

/u01/oracle/product/11.2.0/dbhome_1/assistants/dbca/templates/General_Purpose.dbc

, matching gdbname and sid, and setting character sets, passwords, and memory size.

[oracledb@ ~]$ dbca -silent -createDatabase -templateName /u01/oracle/product/11.2.0/dbhome_1/assistants/dbca/templates/General_Purpose.dbc -gdbname GDBNAME -sid SIDNAME -characterSet AL32UTF8 -NATIONALCHARACTERSET UTF8 -sysPassword SYSPASSWORD -systemPassword SYSTEMPASSWORD -TOTALMEMORY 2048

Two deletion methods are shown. The first uses a one‑line dbca -silent -deleteDatabase command; the second uses a response file ( dbca.rsp) with OPERATION_TYPE="deleteDatabase" and the SID name.

[oracledb@ ~]$ dbca -silent -deleteDatabase -sourceDB SIDNAME -sysDBAUserName sys -sysDBAPassword SYSPASSWORD

# Step 1: create response file
[oracledb@ ~]$ cat /u01/oracle/response/dbca.rsp
OPERATION_TYPE = "deleteDatabase"
SOURCEDB = "SIDNAME"
SYSDBAUSERNAME = "sys"
SYSDBAPASSWORD = "SYSPASSWORD"
# Step 2: run deletion
[oracledb@ ~]$ dbca -silent -responseFile /u01/oracle/response/dbca.rsp

For Oracle 12c the creation command is identical except for the template path; after deletion, residual directories must be removed manually.

[oracledb@ ~]$ dbca -silent -createDatabase -templateName /u01/oracle/product/orahome/assistants/dbca/templates/General_Purpose.dbc -gdbname GDBNAME -sid SIDNAME -characterSet AL32UTF8 -NATIONALCHARACTERSET UTF8 -sysPassword SYSPASSWORD -systemPassword SYSTEMPASSWORD -TOTALMEMORY 2048

2. Creating Users, Tablespaces, and Permissions

Switch to the target SID and set the character set environment variable.

[oracledb@ ~]$ export ORACLE_SID=SIDNAME
[oracledb@ ~]$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8   # Linux
# or on Windows
[oracledb@ ~]$ set NLS_LANG=AMERICAN_AMERICA.AL32UTF8

Create temporary and permanent tablespaces, then a user with appropriate default tablespaces and privileges.

SQL> create temporary tablespace SIDNAME_temp tempfile '/u01/oracle/oradata/SIDNAME/SIDNAME_temp.dbf' size 64m autoextend on next 64m maxsize unlimited extent management local;
SQL> create tablespace SIDNAME_data logging datafile '/u01/oracle/oradata/SIDNAME/SIDNAME_data.dbf' size 64m autoextend on next 64m maxsize 2048m extent management local;
SQL> create user USERNAME identified by USERPASSWORD default tablespace SIDNAME_data temporary tablespace SIDNAME_temp;
SQL> grant connect,resource to USERNAME;
SQL> grant create view to USERNAME;
SQL> grant unlimited tablespace to USERNAME;
SQL> grant create public synonym, drop public synonym to USERNAME;
SQL> create or replace directory dir_dump as '/u01/oracle/backup';
SQL> grant read, write on directory dir_dump to USERNAME;
SQL> alter profile default limit password_life_time unlimited;

Adjust processes and sessions parameters as needed and restart the database.

SQL> alter system set processes=1000 scope=spfile;
SQL> alter system set sessions=1105 scope=spfile;

Note: In 11g, sessions should be > 1.1*processes+5; in 12c the rule is 1.1*processes+22.

3. Backup and Restore with Data Pump (expdp/impdp)

Check the directory object used for dump files: SQL> select * from dba_directories; Full database backup (adjust parallel based on memory):

[oracledb@ ~]$ expdp USERNAME/USERPASSWORD@SIDNAME schemas=SCHEMASNAME dumpfile=SIDNAME`date +%Y%m%d`.dmp directory=dir_dump parallel=2

Restore scenarios:

Different source and target SID: use remap_schema and remap_tablespace.

Same SID: simple impdp with optional EXCLUDE=STATISTICS.

# Different SID
[oracledb@ ~]$ impdp USERNAME/USERPASSWORD@SIDNAME schemas=SCHEMASNAME DUMPFILE=XXXX.dmp DIRECTORY=dir_dump remap_schema=src_schema:tgt_schema remap_tablespace=src_data:tgt_data
# Same SID
[oracledb@ ~]$ impdp USERNAME/USERPASSWORD@SIDNAME schemas=SCHEMASNAME DUMPFILE=XXXX.dmp DIRECTORY=dir_dump EXCLUDE=STATISTICS

After restoring with EXCLUDE=STATISTICS, gather statistics:

SQL> exec dbms_stats.gather_schema_stats(ownname=>'SIDNAME', estimate_percent=>10, degree=>8, cascade=>true, granularity=>'ALL');

Table‑level backup and restore:

# Export a single table
[oracledb@ ~]$ expdp USERNAME/USERPASSWORD@SIDNAME dumpfile=tablenameXXXX.dmp DIRECTORY=dir_dump tables=TABLENAME
# Import a single table
[oracledb@ ~]$ impdp USERNAME/USERPASSWORD@SIDNAME dumpfile=tablenameXXXX.dmp DIRECTORY=dir_dump TABLES=TABLENAME TABLE_EXISTS_ACTION=REPLACE

Four TABLE_EXISTS_ACTION options: skip – default, do nothing. replace – drop, recreate, then load data. append – insert data in addition to existing rows. truncate – truncate the table before loading.

Multi‑table export/import uses the TABLES list and optional remap_table for renaming.

4. Advanced Data Pump Options

Export from a higher version to a lower one by adding VERSION=11.1.0.2.0.

Split large dumps into 5 GB files: filesize=5G parallel=16.

Exclude specific tables: exclude=TABLE:"IN ('TABLENAME')".

Use TABLE_EXISTS_ACTION=truncate when restoring to a different database without changing structure.

Export only metadata (no data) with query=TABLE1:"where 1=2",TABLE2:"where 1=2".

Estimate size without exporting using estimate_only=y.

Import a dump into a temporary table: create the table, delete its rows, then run impdp with remap_table and EXCLUDE=STATISTICS,EXCLUDE=INDEX.

5. Managing Data Pump Jobs

Monitor jobs via dba_datapump_jobs, stop with stop_job=immediate, and use interactive commands such as KILL_JOB, PARALLEL, START_JOB, and STATUS.

6. Cleaning Up After Errors

Drop tablespaces, temporary tablespaces, and users when needed:

# Drop a tablespace
[oracledb@ ~]$ sqlplus / as sysdba
SQL> drop tablespace mepro_data including contents and datafiles cascade constraint;
# Drop a temporary tablespace
SQL> drop tablespace mepro_temp including contents and datafiles cascade constraints;
# Drop a user
SQL> drop user srmhdld cascade;

If a user cannot be dropped because of active sessions, either restart the database and drop immediately, or kill the sessions:

# Find active sessions for USERNAME
SQL> select username, serial#, sid, program, machine, status from v$session where username='USERNAME' and status='ACTIVE';
# Kill a session
SQL> alter system kill session 'serial#,sid';