수동 Database 생성

1. 디렉토리 생성
   
   OS] cd $ORACLE_BASE/oradata
   OS] rm -rf devdb
   OS] mkdir devdb
   
   
2. 파라미터 파일 생성 및 Startup nomount
   
   OS] export ORACLE_SID=devdb
   OS] env|grep SID
   ORACLE_SID=devdb
   
   OS] vi $ORACLE_HOME/dbs/initdevdb.ora
   
   db_name       = devdb
   instance_name = devdb
   compatible    = 10.2.0
   processes     = 100
   
   undo_management = auto
   undo_tablespace = undotbs01
   
   db_cache_size    = 64m
   shared_pool_size = 72m
   db_block_size    = 4096
   
   control_files = ('$ORACLE_BASE/oradata/devdb/control01.ctl',
                    '$ORACLE_BASE/oradata/devdb/control02.ctl')
   
   remote_login_passwordfile = exclusive
   
   OS] sqlplus / as sysdba
   SQL> startup nomount
   
   
3. Create database 명령 실행
   
   create database devdb
       MAXLOGFILES 30
       MAXLOGMEMBERS 5
       MAXDATAFILES 30
       MAXINSTANCES 1
       MAXLOGHISTORY 292
   logfile group 1 ('$ORACLE_BASE/oradata/devdb/redo01_a.log',
                    '$ORACLE_BASE/oradata/devdb/redo01_b.log') size 20m,
           group 2 ('$ORACLE_BASE/oradata/devdb/redo02_a.log',
                    '$ORACLE_BASE/oradata/devdb/redo02_b.log') size 20m
   datafile '$ORACLE_BASE/oradata/devdb/system01.dbf' size 200m autoextend on next 20m maxsize unlimited
   sysaux datafile '$ORACLE_BASE/oradata/devdb/sysaux01.dbf' size 200m autoextend on next 20m maxsize unlimited
   undo tablespace undotbs01 datafile '$ORACLE_BASE/oradata/devdb/undotbs01.dbf' size 100m autoextend on next 20m maxsize 2G
   default temporary tablespace temp tempfile '$ORACLE_BASE/oradata/devdb/temp01.tmp' size 20m autoextend on next 20m maxsize 2G;
   
   OS] ls -l /u01/app/oracle/product/10.2.0/db_1/rdbms/admin/sql*
   OS] more /u01/app/oracle/product/10.2.0/db_1/rdbms/admin/sql.bsq
   
   
4. 필수 스크립트 실행
   
   SQL> alter user sys identified by oracle;        -- change_on_install
   SQL> alter user system identified by oracle;     -- manager
   
   SQL> define _editor=vi
   SQL> ed after_db_create.sql
   
   conn sys/oracle as sysdba
   @?/rdbms/admin/catalog.sql
   @?/rdbms/admin/catproc.sql
   
   conn system/oracle
   @?/sqlplus/admin/pupbld.sql
   
   SQL> @ after_db_create.sql
   SQL> exit
   
   
5. Database 생성 후 필수설정
   
   # Create password file
   
   OS] ls $ORACLE_HOME/dbs
   OS] orapwd file=$ORACLE_HOME/dbs/orapwdevdb password=nemam entries=5
   
   # Test
   
   OS] export ORACLE_SID=orcl
   OS] sqlplus / as sysdba
   
   SQL> select instance_name from v$instance;
   SQL> shutdown abort
   SQL> startup
   
   OS] export ORACLE_SID=devdb
   OS] sqlplus / as sysdba
   
   SQL> select instance_name from v$instance;
   SQL> shutdown abort
   SQL> startup
   
   # Setting Network
   
   OS] cd $ORACLE_HOME/network/admin
   OS] vi listener.ora
   
   LISTENER =
     (DESCRIPTION_LIST =
       (DESCRIPTION =
         (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
         (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.10)(PORT = 1521))
       )
     )
   
   SID_LIST_LISTENER =
     (SID_LIST =
       (SID_DESC =
         (SID_NAME = PLSExtProc)
         (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
         (PROGRAM = extproc)
       )
       (SID_DESC =
         (SID_NAME = orcl)
         (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
         (GLOBAL_DBNAME = orcl)
       )
       (SID_DESC =
         (SID_NAME = devdb)
         (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
         (GLOBAL_DBNAME = devdb)
       )
     )
   
   OS] lsnrctl stop
   OS] lsnrctl start
   
   cf. Windows에서
   
    C:\Documents and Settings\STU> notepad devdb_system.bat
        set path=D:\ora\instructor\software\instantclient-11.1;%path%
        sqlplus system/oracle@192.168.0.10:1521/devdb
   
    C:\Documents and Settings\STU> devdb_system
    SYSTEM SQL> exit
   
    C:\Documents and Settings\STU> notepad devdb_sys.bat
        set path=D:\ora\instructor\software\instantclient-11.1;%path%
        sqlplus sys/nemam@192.168.0.10:1521/devdb as sysdba
   
    C:\Documents and Settings\STU> devdb_sys
    
    SYS SQL> shutdown abort
    SYS SQL> startup
    SYS SQL> exit
   
   OS] vi tnsnames.ora
   
   a =
     (DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.10)(PORT = 1521))
       (CONNECT_DATA =
         (SERVER = DEDICATED)
         (SERVICE_NAME = orcl)
       )
     )
   
   b =
     (DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.10)(PORT = 1521))
       (CONNECT_DATA =
         (SERVER = DEDICATED)
         (SERVICE_NAME = devdb)
       )
     )
   
   OS] tnsping a
   OS] tnsping b
   
   OS] sqlplus system/oracle@a
   SQL> exit
   
   OS] sqlplus system/oracle@b
   SQL> exit
   
   # First Web browser : http://192.168.0.10:5560/isqlplus
   
        Username           : system
        Password           : oracle
        Connect Identifier : a
   
            select * from v$instance;
   
     Second Web browser : http://192.168.0.10:5560/isqlplus
   
        Username           : system
        Password           : oracle
        Connect Identifier : b
   
            select * from v$instance;