Oracle creates instances manually


Location of audit and other documents

Some sql statements for building packages and stored procedures



(0) specify environment variable
export ORACLE_SID=lltf
(1) Create password file

orapwd file=orapwdlltf password=czty_lltf entries=30

Create parameter file pfile (actually named init < db_name >. ORA)
The audit file, control file path and database name will be specified in pfile

(2) Create the path specified in pfile

(3) Generate server parameter file spfile according to pfile

    SQL> sqlplus / as sysdba
    SQL> create spfile from  pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initnewdb.ora'

(4)startup nomount

(5) Create database

    create database newdb
    datafile '/u01/app/oracle/oradata/newdb/system.dbf' size 1000m extent management local
    sysaux datafile '/u01/app/oracle/oradata/newdb/sysaux.dbf' size 1000m
    default temporary tablespace temp tempfile '/u01/app/oracle/oradata/newdb/temp01.dbf' size 1000m
    extent management local uniform size 1m
    undo tablespace undotbs1 datafile '/u01/app/oracle/oradata/newdb/undo1.dbf' size 1000m
    group 1 ('/u01/app/oracle/oradata/newdb/log1.dbf') size 100m,
    group 2 ('/u01/app/oracle/oradata/newdb/log2.dbf') size 100m,
    group 3 ('/u01/app/oracle/oradata/newdb/log3.dbf') size 100m;

(6) Create data dictionary

    SQL> @$ORACLE_HOME/rdbms/admin/catalog.sql
    SQL> @$ORACLE_HOME/rdbms/admin/catproc.sql
    SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

    SQL> conn system/manager

    SQL> @$ORACLE_HOME/sqlplus/admin/pupbld.sql 

(7) Create Users tablespace and set it as default
The default table space of the system is system. If the system is used as the default table space, the management and performance of the database will be affected

create tablespace users
datafile '/u01/app/oracle/oradata/newdb/user01.dbf' size 1000m;

alter database default tablespace users;

    SQL> col name for a60
    SQL> select file#,name from v$datafile union select file#,name from v$tempfile;

        FILE# NAME
    ---------- ------------------------------------------------------------
            1 /u01/app/oracle/oradata/secooler/dfile/system01.dbf
            1 /u01/app/oracle/oradata/secooler/dfile/temp01.dbf
            2 /u01/app/oracle/oradata/secooler/dfile/undotbs01.dbf
            3 /u01/app/oracle/oradata/secooler/dfile/sysaux01.dbf
            4 /u01/app/oracle/oradata/secooler/dfile/tbs_1.dbf

    //Adjust it to automatic expansion
    SQL> alter database datafile 1 autoextend on;
    SQL> alter database datafile 2 autoextend on;
    SQL> alter database datafile 3 autoextend on;
    SQL> alter database datafile 4 autoextend on;
    SQL> alter database tempfile 1 autoextend on;

When the initlltf.ora file is configured in dbs, it indicates that the instance name is lltf
In initlltf.ora, there is a parameter named db name

Tags: SQL Oracle Database sqlplus

Posted on Sun, 03 May 2020 23:24:15 -0400 by MasterHernan