AWS Administration - 100% Lab - Online Training | AKSWAVE
10
Jul

12c Oracle Database Creation manually (step-by-step)

  • 4.8
  • Reviews : 640

Using the CREATE DATBASE SQL statement is a more manual approach to creating a database. If you use the CREATE DATABASE statement, you must complete additional actions before you have an operational database. These actions include building views on the data dictionary tables and installing standard PL/SQL packages. You perform these actions by running prepared scripts.

Course : ORACLE 12C DBA


Overview : In this practice you will create database step-by-step manually.

Using the CREATE DATBASE SQL statement is a more manual approach to creating a database. If you use the CREATE DATABASE statement, you must complete additional actions before you have an operational database. These actions include building views on the data dictionary tables and installing standard PL/SQL packages. You perform these actions by running prepared scripts.

Specify an Instance Identifier (SID) & Environment Variables ORACLE_SID, unique Oracle system identifier (SID) is used to distinguish this instance from other Oracle Database instances that you may create later and run concurrently on the same host computer. The maximum number of characters for ORACLE_SID is 12, and only letters and numeric digits are permitted.

In our case ORACLE_HOME is on /u01/app/oracle/product/12.1.0.2/dbhome_1 path where we have Oracle software Installed.

$ cd

$ vi .bash_profile

if [ -f ~/.bashrc ]; then

. ~/.bashrc

fi

 

# User specific environment and startup programs

 

export ORACLE_SID=orcl

export ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1

export PATH=$ORACLE_HOME/bin:$PATH:$HOME/bin

$ . .bash_profile

Ensure That the Required Environment Variables Are Set

$ echo $ORACLE_SID

$ echo $ORACLE_HOME

$ echo $PATH

Create the Initialization Parameter File

$ cd $ORACLE_HOME/dbs

$ cp init.ora initorcl.ora--(orcl is SID here )

Open your pfile and edit with the following parameters

$ vi initorcl.ora

db_block_size=8192

db_name='orcl'

open_cursors=300

processes=150

shared_pool_size=150m

sga_target=400m

undo_tablespace='UNDOTBS1'

control_files='/disk1/oradata/orcl/control01.ctl'

db_create_file_dest='/disk1/oradata/orcl'

diagnostic_dest='/disk3/oradata/orcl'

compatible='12.1.0.2'

Create directory structure

$ mkdir -p /disk1/oradata/orcl

$ mkdir -p /disk2/oradata/orcl

$ mkdir -p /disk3/oradata/orcl

Connect and startup your instance in nomount mode

$ sqlplus / as SYSDBA

SQL> startup NOMOUNT

SQL> EXIT

Issue the CREATE DATABASE Statement

$ vi $HOME/cr8db.sql

CREATE DATABASE orcl

LOGFILE GROUP 1 ('/disk1/oradata/orcl/redo01a.log') SIZE 5M,

GROUP 2 ('/disk1/oradata/orcl/redo02a.log') SIZE 5M

DATAFILE '/disk1/oradata/orcl/system01.dbf' SIZE 200M REUSE autoextend on next 10m

SYSAUX DATAFILE '/disk1/oradata/orcl/sysaux01.dbf' SIZE 200M REUSE autoextend on

Default TEMPORARY TABLESPACE TEMPTS

TEMPFILE '/disk1/oradata/orcl/tempts01.dbf' SIZE 20M REUSE

UNDO TABLESPACE UNDOTBS1

DATAFILE '/disk1/oradata/orcl/undotbs01.dbf' SIZE 25M REUSE;

$ sqlplus / as sysdba

SQL> @$HOME/cr8db.sql

Database created

Build Data Dictionary Views

Run the scripts necessary to build data dictionary views, synonyms, and PL/SQL packages, and to support proper functioning of SQL*Plus:

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

SQL> shut Immediate

SQL> startup upgrade

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

SQL> shut Immediate

SQL> startup

SQL> connect system/manager

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



SAMPLE QUERIES:

SQL> select name,value from v$sga;

SQL> select instance_name, version, startup_time,status, database_status from v$instance;

SQL> select name,created,open_mode,current_scn from v$database;

SQL> select banner from v$version;

 

Data Dictionary VIEWS to Query

 

·      V$SGA

·      V$INSTANCE

·      V$DATABASE

·      V$PROCESS

·      V$SYSAUX_OCCUPANTS

 

 


Anusha
Software Trainer

Test

Related Course

Oracle 12C DBA

( 5)
Reviews : 2,567

Classroom

 10,000

 14,000

29% Off

Online

 20,000

 22,000

9% Off

Related Documents

Oracle 12c Container database creation manually ( step-by-step)

Oracle 12c database is a MULTITENANT CONTAINER DATABASE provides space for creating pluggable databases (Eg Pluggable databases are applications like any OLTP databases etc.,) Oracle 12c database is a Multi-tenant container database in which a seed  database called pdb$seed comes with it, Using which we can create pluggable databases with in the container database.  In One Container 253 pdbs can be created including pdbseed.

Oracle 12c Physical standby database configuration (step-by-step)

Here will build a physical standby setup. Our assumption is primary database is already up and running fine And ORACLE_HOME is also installed on standby server.

12c Oracle Database Creation manually (step-by-step)

Using the CREATE DATBASE SQL statement is a more manual approach to creating a database. If you use the CREATE DATABASE statement, you must complete additional actions before you have an operational database. These actions include building views on the data dictionary tables and installing standard PL/SQL packages. You perform these actions by running prepared scripts.

0 Comments

Post a Comment