Manual Oracle database creation Guide

I. Set the environment variable [Oracle @ orcl ~] $ Cat. bash_profile #. bash_profile # Getthealiasesandfunctionsif [-f ~. Bashrc];

I. Set the environment variable [Oracle @ orcl ~] $ Cat. bash_profile #. bash_profile # Get the aliases and functionsif [-f ~ /. Bashrc];

I. Set Environment Variables

[Oracle @ orcl ~] $ Cat. bash_profile
#. Bash_profile

# Get the aliases and functions
If [-f ~ /. Bashrc]; then
.~ /. Bashrc
Fi

# User specific environment and startup programs

ORACLE_SID = orcl
ORACLE_BASE =/u01/app/oracle
ORACLE_HOME = $ ORACLE_BASE/product/10.2.0/db_1
EDITOR = vim
NLS_LANG = american_america.AL32UTF8
NLS_DATE_FORMAT = YYYY-MM-DD: HH24: MI: SS
PATH = $ PATH: $ HOME/bin: $ ORACLE_HOME/bin: $ PATH
Export PATH ORACLE_SID ORACLE_BASE ORACLE_HOME EDITOR NLS_LANG NLS_DATE_FORMAT
Alias sqlplus = '/usr/local/rlwrap/bin/rlwrap sqlplus'
Alias rman = '/usr/local/rlwrap/bin/rlwrap rman'

2. Prepare password files, initialize parameter files, and create database scripts.

[Root @ orcl ~] # Su-oracle
[Oracle @ orcl ~] $ Cd $ ORACLE_HOME/dbs
[Oracle @ orcl dbs] $ orapwd file = orapworcl password = 123456 entries = 5

*. Db_name = orcl
*. Db_block_size = 8192
*. Sga_target = 1677721600
*. Undo_management = 'auto'
*. Control_files = ('/u01/app/oracle/oradata/orcl/control01.ctl', '/u01/app/oracle/oradata/orcl/control012ctl ', '/u01/app/oracle/oradata/orcl/control03.ctl ')
*. User_dump_dest = '/u01/app/oracle/admin/orcl/udump'
*. Background_dump_dest = '/u01/app/oracle/admin/orcl/bdump'
*. Core_dump_dest = '/u01/app/oracle/admin/orcl/cdump'

[Oracle @ orcl ~] $ Cat $ ORACLE_HOME/dbs/initorcl. ora
*. Db_name = orcl
*. Db_block_size = 8192
*. Db_file_multiblock_read_count = 16
*. Db_unique_name = 'orcl'
*. Sga_target = 167772160
*. Undo_management = 'auto'
*. Undo_tablespace = 'undotbs1'
*. Open_cursors = 300
*. Pga_aggregate_target = 16777216
*. Processses = 1500
*. Control_files = ('/u01/app/oracle/oradata/orcl/control01.ctl', '/u01/app/oracle/oradata/orcl/control012ctl ', '/u01/app/oracle/oradata/orcl/control03.ctl ')
*. User_dump_dest = '/u01/app/oracle/admin/orcl/udump'
*. Background_dump_dest = '/u01/app/oracle/admin/orcl/bdump'
*. Audit_file_dest = '/u01/app/oracle/admin/orcl/adump'
*. Core_dump_dest = '/u01/app/oracle/admin/orcl/cdump'
*. Db_recovery_file_dest = '/u01/app/oracle/flash_recovery_area'
*. Db_recovery_file_dest_size = 2147483648

[Oracle @ orcl ~] $ Cat create_db. SQL
Create database orcl
User sys identified by "123456"
User system identified by "123456"
LOGFILE
GROUP 1 ('/u01/app/oracle/oradata/orcl/redo01_1.dbf','/u01/app/oracle/oradata/orcl/redo01_2.dbf ') SIZE 150 M,
GROUP 2 ('/u01/app/oracle/oradata/orcl/redo02_1.dbf','/u01/app/oracle/oradata/orcl/redo02_2.dbf ') SIZE 150 M,
GROUP 3 ('/u01/app/oracle/oradata/orcl/redo03_1.dbf', '/u01/app/oracle/oradata/orcl/redo03_2.dbf') SIZE 150 M
MAXLOGFILES 10
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
Character set AL32UTF8
National character set AL16UTF16
DATAFILE '/u01/app/oracle/oradata/orcl/system01.dbf' SIZE 2000 M REUSE EXTENT MANAGEMENT LOCAL
Sysaux datafile '/u01/app/oracle/oradata/orcl/sysaux01.dbf' SIZE 800 M REUSE
Default temporary tablespace temp TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf' SIZE 20 M REUSE
Undo tablespace undotbs1 DATAFILE '/u01/app/oracle/oradata/orcl/undotbs1.dbf' SIZE 2000 m reuse autoextend on next 5120 k maxsize unlimited;

Iii. Related directories required for database creation

[Oracle @ orcl ~] $ Mkdir-p/u01/app/oracle/admin/orcl
[Oracle @ orcl ~] $ Mkdir-p/u01/app/oracle/oradata/orcl
[Oracle @ orcl ~] $ Mkdir-p/u01/app/oracle/admin/orcl/{adump, bdump, cdump, udump}
[Oracle @ orcl ~] $ Mkdir-p/u01/app/oracle/flash_recovery_area

4. Start database creation

[Oracle @ orcl ~] $ Env | grep SID
ORACLE_SID = orcl
[Oracle @ orcl ~] $ Sqlplus/nolog
SQL * Plus: Release 10.2.0.1.0-Production on Thu Sep 29 11:55:01 2011
Copyright (c) 1982,200 5, Oracle. All rights reserved.

SQL> conn/as sysdba
Connected to an idle instance.
SQL> create spfile from pfile;
File created.

SQL> startup nomount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 121637108 bytes
Database Buffers 41943040 bytes
Redo Buffers 2973696 bytes

SQL> @/home/oracle/create_db. SQL;
Database created

[Oracle @ orcl ~] $ Ls-lh/u01/app/oracle/oradata/orcl/
Total 5.6 GB
-Rw-r ----- 1 oracle oinstall 6.6 M Sep 29 14:30 control012ctl
-Rw-r ----- 1 oracle oinstall 6.6 M Sep 29 14:30 control01.ctl
-Rw-r ----- 1 oracle oinstall 6.6 M Sep 29 14:30 control03.ctl
-Rw-r ----- 1 oracle oinstall 151 M Sep 29 14:25 redo01_1.dbf
-Rw-r ----- 1 oracle oinstall 151 M Sep 29 14:25 redo01_2.dbf
-Rw-r ----- 1 oracle oinstall 151 M Sep 29 14:22 redo02_1.dbf
-Rw-r ----- 1 oracle oinstall 151 M Sep 29 14:22 redo02_2.dbf
-Rw-r ----- 1 oracle oinstall 151 M Sep 29 14:22 redo03_1.dbf
-Rw-r ----- 1 oracle oinstall 151 M Sep 29 14:22 redo03_2.dbf
-Rw-r ----- 1 oracle oinstall 801 M Sep 29 14:25 sysaux01.dbf
-Rw-r ----- 1 oracle oinstall 2.0G Sep 29 system01.dbf
-Rw-r ----- 1 oracle oinstall 21 M Sep 29 14:25 temp01.dbf
-Rw-r ----- 1 oracle oinstall 2.0G Sep 29 undotbs1.dbf

SQL> @ $ ORACLE_HOME/rdbms/admin/catalog. SQL;
PL/SQL procedure successfully completed.

SQL> @ $ ORACLE_HOME/rdbms/admin/catproc. SQL;
PL/SQL procedure successfully completed.
SQL> shutdown immediate
SQL> startup
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 138414324 bytes
Database Buffers 25165824 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.

V. Verification

[Oracle @ orcl ~] $ Rman target/
Recovery Manager: Release 10.2.0.1.0-Production on Thu Sep 29 14:52:27 2011
Copyright (c) 1982,200 5, Oracle. All rights reserved.
Connected to target database: ORCL (DBID = 1290917246)

RMAN> list incarnation;

Using target database control file instead of recovery catalog

List of Database Incarnations
DB Key Inc Key DB Name db id status Reset SCN Reset Time
-------------------------------------------------------------
1 1 ORCL 1290917246 CURRENT 1: 14: 21: 50

RMAN> report schema;

Report of database schema

List of Permanent Datafiles
======================================
File Size (MB) Tablespace RB segs Datafile Name
---------------------------------------------------------------
1 2000 SYSTEM ***/u01/app/oracle/oradata/orcl/system01.dbf
2 2000 UNDOTBS1 ***/u01/app/oracle/oradata/orcl/undotbs1.dbf
3 800 SYSAUX ***/u01/app/oracle/oradata/orcl/sysaux01.dbf

List of Temporary Files
======================================
File Size (MB) Tablespace Maxsize (MB) Tempfile Name
---------------------------------------------------------------
1 20 TEMP 20/u01/app/oracle/oradata/orcl/temp01.dbf

SQL> create tablespace users datafile '/u01/app/oracle/oradata/orcl/users01.dbf' size 500 M
2 autoextend on maxsize 5G;
Tablespace created

SQL> alter database default tablespace users;
Database altered

6. Configure EM

Create and configure an EM Database
SQL> @? /Sysman/admin/emdrep/SQL/emreposcre/u01/app/oracle/product/10.2.0/db_1 SYSMAN 123456 TEMP ON;
SQL> alter user SYSMAN identified by "123456" account unlock;
SQL> alter user DBSNMP identified by "123456" account unlock;
SQL> exit
[Oracle @ orcl admin] $ emca-config dbcontrol db-silent-DB_UNIQUE_NAME orcl-PORT 1521-EM_HOME/u01/app/oracle/product/10.2.0/db_1-LISTENER-SERVICE_NAME orcl.herostart.com -SYS_PWD 123456-SID orcl-ORACLE_HOME/u01/app/oracle/product/10.2.0/db_1-DBSNMP_PWD 123456-HOST orcl.herostart.com-LISTENER_OH/u01/app/oracle/product/10.2.0/db_1 -LOG_FILE/u01/app/oracle/product/10.2.0/db_1/log/emConfig. log-SYSMAN_PWD 123456
[Oracle @ orcl admin] $ emctl status dbconsole
TZ set to PRC
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996,200 5 Oracle Corporation. All rights reserved.
: 1158/em/console/aboutApplication
Oracle Enterprise Manager 10g is running.
------------------------------------------------------------------
Logs are generated in directory/u01/app/oracle/product/10.2.0/db_1/orcl.herostart.com _ orcl/sysman/log

,