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.ora3. Make directory for instance data
mkdir /home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/tempdb4. Set ORACLE_SID
export ORACLE_SID=tempdb5. Start sqlplus as DBA
sqlplus / as sysdba6. 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 shut7. 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:Y9. Start database
dbstart10. 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