Creating or Dropping a database in Oracle 11g

CREATING A NEW DATABASE

  • Creating a initialization parameter file at location $ORACLE_HOME/dbs which is the default location where oracle looks for a init file .

Lets create a database by name ‘test’ :

[[email protected]]$ cat $ORACLE_HOME/dbs/inittest.ora
db_name=test
db_block_size=8192
control_files=(/home/oracle/app/oracle/oradata/test/control01.ctl,/home/oracle/app/oracle/oradata/test/control02.ctl)
  • Startup instance in nomount mode and create a database
[[email protected] ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon Feb 18 23:55:15 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 150667264 bytes
Fixed Size 1335080 bytes
Variable Size 92274904 bytes
Database Buffers 50331648 bytes
Redo Buffers 6725632 bytes

SQL> select status from v$instance;

STATUS
------------
NOMOUNT

SQL> CREATE DATABASE test
LOGFILE
GROUP 1 ('/home/oracle/app/oracle/oradata/test/redo01a.log', '/home/oracle/app/oracle/oradata/test/redo01b.log') SIZE 10M,
GROUP 2 ('/home/oracle/app/oracle/oradata/test/redo02a.log', '/home/oracle/app/oracle/oradata/test/redo02b.log') SIZE 10M
DATAFILE
 '/home/oracle/app/oracle/oradata/test/system01.dbf' SIZE 200M
SYSAUX DATAFILE '/home/oracle/app/oracle/oradata/test/sysaux01.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 2000M
UNDO TABLESPACE "UNDOTBS1" DATAFILE '/home/oracle/app/oracle/oradata/test/undotbs01.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/home/oracle/app/oracle/oradata/test/temp01.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 500M
DEFAULT TABLESPACE USERS DATAFILE '/home/oracle/app/oracle/oradata/test/users01.dbf' SIZE 50M AUTOEXTEND ON MAXSIZE 200M
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16;

Database created.


SQL> select status from v$instance;

STATUS
------------
OPEN

Read More

Character set conversion in Oracle

Here is the procedure to convert Oracle database (11G) from  US7ASCII to  UTF8/ALT32UTF8, we must be using similar steps to convert to a different character set.

In this example we will be using csscan utility to find lossy and convertible data in database and csalter utility to alter characterset.

  • Install csscan utility :
SQL> @$ORACLE_HOME/rdbms/admin/csminst.sql
  • Verify current characterset :
SQL> select * from nls_database_parameters where parameter='NLS_CHARACTERSET';
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_CHARACTERSET US7ASCII
  • Check for invalid objects :
SQL> SELECT owner,
object_name,
object_type,
status
FROM dba_objects
WHERE status ='INVALID'
/

Recompile all invalid objects in the database : Read More