Master Oracle DB: Create, Delete, Backup, and Restore Commands

1. Oracle Database Creation and Deletion Commands

(1) Oracle 11g

Database creation (commonly set gdbname and sid the same, sys and system passwords the same for convenience)

[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

Deletion method 1:

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

Deletion method 2 (using response file):

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

(2) Oracle 12c creation

[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

Deletion command same as above; after deletion manually remove residual directories.

(3) Paths automatically removed after deletion

A: /u01/oracle/admin/SIDNAME
B: cat /etc/oratab
C: /u01/oracle/oradata/SIDNAME

Paths requiring manual cleanup

D: /u01/oracle/cfgtoollogs/dbca/SIDNAME
E: /u01/oracle/diag/rdbms/SIDNAME
F: /u01/oracle/product/11.2.0/dbhome_1/dbs/hc_SIDNAME.dat

2. Creating Users and Passwords for the Database

1. Switch SID export ORACLE_SID=SIDNAME 2. Set character set

# Query character set
SQL> select userenv('language') from dual;
# Query NLS_CHARACTERSET
SQL> select * from nls_database_parameters where parameter='NLS_CHARACTERSET';
# Set NLS_LANG for Windows
set NLS_LANG=AMERICAN_AMERICA.AL32UTF8
# Set NLS_LANG for Linux
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

3. Create tablespaces and user

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 to USERNAME;
SQL> grant 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;
SQL> alter system set processes=1000 scope=spfile;
SQL> alter system set sessions=1105 scope=spfile;

Knowledge point: for Oracle 11g, sessions should be > 1.1*processes+5; for Oracle 12c, use 1.1*processes+22.

# Query active sessions
SQL> select count(*) from v$session where status='ACTIVE';
# Sessions per user
SQL> select username, count(username) from v$session where username is not null group by username;
# List all users
select * from all_users;
# Current process count
select count(*) from v$process;
# Max allowed connections
select value from v$parameter where name='processes';

3. Database Backup and Restore Commands

Check Data Pump export directory: sql> select * from dba_directories; 1. Full database backup and restore

Backup command

# Backup
expdp USERNAME/USERPASSWORD@SIDNAME schemas=SCHEMASNAME dumpfile=SIDNAME`date +%Y%m%d`.dmp directory=dir_dump parallel=2

Restore when source and target SID differ

# Restore (different SID)
impdp USERNAME/USERPASSWORD@SIDNAME schemas=SCHEMASNAME DUMPFILE=XXXX.dmp DIRECTORY=dir_dump remap_schema=source_schema:target_schema remap_tablespace=source_data:target_data

Restore when SID is the same

# Restore (same SID)
impdp USERNAME/USERPASSWORD@SIDNAME schemas=SCHEMASNAME DUMPFILE=XXXX.dmp DIRECTORY=dir_dump EXCLUDE=STATISTICS

2. Single‑table backup and restore

Backup a single table

expdp USERNAME/USERPASSWORD@SIDNAME dumpfile=tablenameXXXX.dmp DIRECTORY=dir_dump tables=TABLENAME

Restore a single table

impdp USERNAME/USERPASSWORD@SIDNAME dumpfile=tablenameXXXX.dmp DIRECTORY=dir_dump TABLES=TABLENAME TABLE_EXISTS_ACTION=REPLACE

table_exists_action options: skip (default), replace, append, truncate.

Backup multiple tables

expdp USERNAME/USERPASSWORD@SIDNAME dumpfile=tablenameXXXX.dmp DIRECTORY=dir_dump TABLES=TABLE1,TABLE2,...

Restore multiple tables

impdp USERNAME/USERPASSWORD@SIDNAME dumpfile=tablenameXXXX.dmp DIRECTORY=dir_dump remap_table=source1:target1 TABLE_EXISTS_ACTION=REPLACE
impdp USERNAME/USERPASSWORD@SIDNAME dumpfile=tablenameXXXX.dmp DIRECTORY=dir_dump remap_table=source2:target2 TABLE_EXISTS_ACTION=REPLACE

Additional tips:

Cross‑version export: add version=11.1.0.2.0 when exporting from 12c to 11g.

Specify file size for large exports: filesize=5G parallel=16.

Exclude specific tables: exclude=TABLE:"IN ('TABLE1','TABLE2')".

Use TABLE_EXISTS_ACTION=TRUNCATE to keep structure but empty target tables.

Estimate only (no data export): estimate_only=y.

Stop and resume Data Pump jobs using stop_job, start_job, kill_job, etc.

4. Cleanup Procedures

1. Drop a tablespace

# Drop data tablespace
sqlplus / as sysdba
SQL> drop tablespace mepro_data including contents and datafiles cascade constraint;

2. Drop a temporary tablespace

SQL> drop tablespace mepro_temp including contents and datafiles cascade constraints;

3. Drop a user SQL> drop user srmhdld cascade; 4. If a user cannot be dropped because of active sessions, either restart the database and drop immediately, or kill the sessions:

# Query active sessions for a user
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';