- An environmental profile can set the environment vairables of the new database.
- A shell scirpt file can set the preparation works and pfile in one execution.
- A SQL script file to startup nomount and create database in one execution. We will startup to nomount state, then create the database.
[oracle@primary01 ~]$ vi smalldb_profile
ORACLE_SID=smalldb; export ORACLE_SID
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1; export ORACLE_HOMEWe call the new database "smalldb" and keep $ORACLE_HOME the same value as usual. The profile should be taken effective before creation.
[oracle@primary01 ~]$ vi create_init.sh
#!/bin/bash
# Create data files destination directory
if [ ! -d /u01/app/oracle/oradata/SMALLDB ] ;
then
mkdir /u01/app/oracle/oradata/SMALLDB
fi
cat << EOF > $ORACLE_HOME/dbs/initsmalldb.ora
control_files = (/u01/app/oracle/oradata/SMALLDB/control01.ctl,/u01/app/oracle/oradata/SMALLDB/control02.ctl,/u01/app/oracle/oradata/SMALLDB/control03.ctl)
undo_management=auto
compatible ='11.2.0'
db_name='SMALLDB'
db_domain=''
memory_target=256M
processes=60
db_block_size=8192
open_cursors=90
DB_CREATE_FILE_DEST='/u01/app/oracle/oradata'
undo_tablespace='UNDOTBS1'
service_names='SMALLDB'
audit_trail ='db'
remote_login_passwordfile='EXCLUSIVE'
cpu_count=1
EOFSince there're could be more preparations need to be done, we edit the pfile in an indirect way of executing a shell script file, in which, you can add more jobs before the cat command. Such as, mkdir the destination that database is going to reside, cp the data files from other database for reusing them, etc. The purpose is to prepare the new database infrastructure in one execution.
[oracle@primary01 ~]$ vi create_db.sql
--Startup nomount with the specific pfile
startup nomount pfile='/u01/app/oracle/product/11.2.0/db_2/dbs/init.ora';
--Create database SMALLDB
CREATE DATABASE SMALLDB
USER SYS IDENTIFIED BY password
USER SYSTEM IDENTIFIED BY password
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/SMALLDB/group_1.dbf' SIZE 50M,
GROUP 2 '/u01/app/oracle/oradata/SMALLDB/group_2.dbf' SIZE 50M
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 10
MAXINSTANCES 1
MAXLOGHISTORY 100
NOARCHIVELOG
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
DATAFILE '/u01/app/oracle/oradata/SMALLDB/system01.dbf' SIZE 500M REUSE AUTOEXTEND ON MAXSIZE 2G
SYSAUX DATAFILE '/u01/app/oracle/oradata/SMALLDB/sysaux01.dbf' SIZE 500M REUSE AUTOEXTEND ON MAXSIZE 2G
UNDO TABLESPACE UNDOTBS1 DATAFILE '/u01/app/oracle/oradata/SMALLDB/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON MAXSIZE 2G
DEFAULT TEMPORARY TABLESPACE TEMPTBS1 TEMPFILE '/u01/app/oracle/oradata/SMALLDB/temp01.dbf' SIZE 20M REUSE AUTOEXTEND ON MAXSIZE 2G
DEFAULT TABLESPACE USERS DATAFILE '/u01/app/oracle/oradata/SMALLDB/users01.dbf' SIZE 500M REUSE AUTOEXTEND ON MAXSIZE 2G
SET TIME_ZONE = '+08:00';