Create a database using CREATE DATABASE statements
This article refers to the way you create a database using create database in Chapter 2 of "Administrator's Guide." Generally speaking, the first thing we come across is to use dbca to create a database, compared to which using create database is more complex.
-
Using the create database command to create a database requires the following steps:
Step 1: specify the instance identifier
Export ORACLE_SID=mynewdb
Step 2: set the relevant environment variables
ORACLE_HOME and ORACLE_SID must be specified, PATH variable is optional
Export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
Export PATH=$PATH:$ORACLE_HOME/bin
Step 3: select the authentication method for the database administrator
The authentication methods of database administrators include password file authentication and operating system authentication.
Password file authentication: a password is required when creating a password file.
Operating system authentication: add users to the dba group.
Step 4: create a static parameter file
You can copy static parameter files from other databases, or you can create parameter files by modifying samples of initialization parameters.
The values of DB_NAME, CONTROL_FILES, and MEMORY_TARGET must be specified.
The static parameter file is named initSID.ora.
[oracle@mycentos dbs] $cp init.ora initmynewdb.ora
Note: because it is only a test, the sample parameter file has not been modified too much. It should be noted that the corresponding directory in the parameter file should be established.
Step 5: connect to the database
Connection method with password file as authentication:
[oracle@mycentos mynewdb] $sqlplus / nolog
SQL > CONNECT SYS AS SYSDBA
Connection mode with operating system authentication
$sqlplus / nolog
SQL > CONNECT / AS SYSDBA
Step 6: create a dynamic parameter file
The CREATE SPFILE FROM PFILE;# step can be carried out later.
Step 7: start the instance
STARTUP NOMOUNT
Step 8: issue the create database instruction
CREATE DATABASE mynewdb
USER SYS IDENTIFIED BY asd
USER SYSTEM IDENTIFIED BY asd
LOGFILE GROUP 1 ('/ app/oracle/oradata/mynewdb/redo01a.log','/app/oracle/oradata/mynewdb/redo01b.log') SIZE 100m BLOCKSIZE 512
GROUP 2 ('/ app/oracle/oradata/mynewdb/redo02a.log','/app/oracle/oradata/mynewdb/redo02b.log') SIZE 100m BLOCKSIZE 512
GROUP 3 ('/ app/oracle/oradata/mynewdb/redo03a.log','/app/oracle/oradata/mynewdb/redo03b.log') SIZE 100m BLOCKSIZE 512
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
DATAFILE'/ app/oracle/oradata/mynewdb/system01.dbf' SIZE 325m REUSE
SYSAUX DATAFILE'/ app/oracle/oradata/mynewdb/sysaux01.dbf' SIZE 325m REUSE
DEFAULT TABLESPACE users
DATAFILE'/ app/oracle/oradata/mynewdb/users01.dbf'
SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE'/ app/oracle/oradata/mynewdb/temp01.dbf'
SIZE 20M REUSE
UNDO TABLESPACE UNDOTBS1
DATAFILE'/ app/oracle/oradata/mynewdb/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
Step 9: create additional tablespaces
CREATE TABLESPACE apps_tbs LOGGING
DATAFILE'/ app/oracle/oradata/mynewdb/apps01.dbf'
SIZE 500M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
-- create a tablespace for indexes, separate from user tablespace (optional)
CREATE TABLESPACE indx_tbs LOGGING
DATAFILE'/ app/oracle/oradata/mynewdb/indx01.dbf'
SIZE 100M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
Step 10: run the script to build the data dictionary view
Execute with a user with sysdba privileges:
@? / rdbms/admin/catalog.sql
@? / rdbms/admin/catproc.sql
@? / rdbms/admin/utlrp.sql
Execute with SYSTEM user
@? / sqlplus/admin/pupbld.sql
Step 11: back up the database
You can switch the database to archive mode for RMAN backup.
-
