Blog do projektu Open Source JavaHotel

niedziela, 24 listopada 2013

How to quickly create and remove Oracle database

Introduction
Sometimes it is necessary for me to quickly create an Oracle database, load data, perform some test and analysis and clear when it is done. I realized that it is much better to create a new database (instance) instead of constantly fleshing out the existing one. By removing this instance I can also reclaim disk space.
Create a database 
I want to create a tempdb database.
1. Prepare initialization parameter file.
tempdb.__oracle_base='/home/oracle/app/oracle'#ORACLE_BASE set from environment
*.db_create_file_dest='tempdb'
*.db_create_online_log_dest_1='tempdb'
*.db_name='tempdb'
*.db_recovery_file_dest='tempdb'
*.db_recovery_file_dest_size=10G
*.diagnostic_dest='tempdb'
2. Put this file in proper place (/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/inittempdb.ora in my environment)
vi /home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/inittempdb.ora 
3. Make directory for instance data
mkdir /home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/tempdb 
4. Set ORACLE_SID
export ORACLE_SID=tempdb
5. Start sqlplus as DBA
sqlplus / as sysdba 
6. Create database, test user and run system scripts.
create spfile from pfile; 
startup nomount 
create database tempdb; 
create user test identified by secret;
grant all provileges to test;
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/sqlplus/admin/pupbld.sql
shut
7. Prepare settings for remote access (file /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora) Add entries like:
LISTENER_TEMPDB =
  (ADDRESS = (PROTOCOL = TCP)(HOST = think)(PORT = 1521))

TEMPDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = sb-ThinkPad-T42)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = tempdb)
    )
  )
8. Add entry to /etc/oratab
tempdb:/home/oracle/app/oracle/product/11.2.0/dbhome_1:Y
9. Start database
dbstart
10. Prepare entry for sqldeveloper
11. Connect and act

Remove database
It is pretty easy (look also).

export ORACLE_SID=mydb
sqlplus / as sysdba
startup force mount
alter system enable restricted session;
drop database;
Remove also entries from /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora Reclaim disk space
rm -rf /home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/tempdb

Brak komentarzy:

Prześlij komentarz