1600 PMP mock questions 1400 CAPM mock questions 800 SCJP 6 mock questions 600 OCAJP 7 mock questions 590 OCPJP 7 mock questions 556 SCWCD 5 mock questions 500 OCEJWCD 6 mock questions pdfDownload (java,struts, hibernet etc)

ORACLE Tutorials

Creation of a Database instance in Oracle

Creation of a Database instance in Oracle
Step 01
Decide on the location of
Three control files (minimum) on different disks/ drives/volumes
Three groups of redo log files, with each group having two members (minimum) on different disks/ drives/volumes
Create a file structure for the data files
Step 02
Creating database manually without auto undo management:
1. a. Decide on a unique instance name on NT/2000 machines up to 8.0.x cannot have a name with more than 4 characters and 8.1.x cannot have more than 8 characters

b. Database character set. Modifying the character set at a later date lands in many issues.

2. Set the operating system variables
(UNIX)
ORACLE_HOME
ORACLE_SID
ORACLE_BASE
ORA_NLS33
PATH

(NT)
ORADIM80 -NEW -SID u16
-INTPWD password -STARTMODE auto
-PFILE ORACLE_HOME\DATABASE\initu16.ora

You must decide the SID, Password, and
Create init<sid_name>.ora file

SET ORACLE_SID=<sid_name>

Make <sid_name> the current SID


3. Prepare the parameter file

use init.ora as a parameter file template

db_name = Eight characters or fewer that identify the database

control_files = the location of three control files

DB_BLOCK_SIZE = Determines the database block size (can not
change after the database has been created)



4. Create a password file

5. Start the instance

STARTUP NOMOUNT pfile=init.ora

6. Create the database

MANUALLY FROM Server Manager

CREATE DATABASE "<sid_name>"
MAXLOGFILES 6
MAXLOGMEMBERS 6
MAXDATAFILES 30
MAXLOGHISTORY 100
ARCHIVELOG

LOGFILE
GROUP 1
'E:\DATA\ sid \GROUP1\log1a.rdo' SIZE 200K,
'E:\DATA\ sid \GROUP1\log1b.rdo' SIZE 200K
GROUP 2
'E:\DATA\ sid \GROUP2\log2a.rdo' SIZE 200K,
'E:\DATA\ sid \GROUP2\log2b.rdo' SIZE 200K
GROUP 3
'E:\DATA\ sid \GROUP3\log3a.rdo' SIZE 200K,
'E:\DATA\ sid \GROUP3\log3b.rdo' SIZE 200K
DATAFILE
'E:\DATA\ sid \DATAFILES\system01.dbf'
size 30M

CHARACTER SET WE8ISO8859P1;

Database
select name,created,log_mode from v$database;

Thread
select status, instance from v$thread;

Datafiles
select name from v$datafile;

Logfiles
select member from v$logfile;

Controlfiles
select name from v$controlfile;

Verify System Creation

select file_name from dba_data_files
where tablespace_name='SYSTEM';

Look at the database users

select username, created from dba_users;


7. Run scripts to generate the data dictionary and
accomplish postcreation steps.


Create the data dictionary
CATALOG.SQL

prostcreation objects
CATPROC.SQL
8. to know/determine the archive mode of the database
svrmgrl> archive log list
or issue the following statement
select log_mode from v$database;
also issue the following statements to know whether the archive process is enabled or not
select archiver from v$instance;
The output determines whether the process is started or not.
Creating a Database using Instance Manager


Step 1. Start Instance Manager
Press the New Button

Input a SID (4 characters) ->abcd

Step 2. Enter the DBA Authorization password and authenication

Step 3. Press the Advanced Button

* The Database Name must be the same as the Database Name
in the initabcd.ora file
* Enter the max logfile value and the max group member value

* Design the location of your datafiles, logfiles,
archive files, and control files

* Change the logfile location and name to meet your design

* Set the location of the Parameter file


Defining Parameters in the Parameter File

db_name = {myDBName}#database name using
to identify the database

db_files = 30 #maximum allowable
number of database files

#control file list
(Created by the Instance Manager)

control_files = (D:\orant\DATABASE\ctl1{SID}orcl.ora,
D:\orant\DATABASE\ctl2{SID}orcl.ora,
D:\orant\DATABASE\ctl3{SID}orcl.ora)

#database will be compatible with
software of this version

compatible = 7.3.0.0.0

#db_file_multiblock_read_count= number of database
blocks to read with each I/O.

#db_file_multiblock_read_count = 8 # INITIAL
# db_file_multiblock_read_count = 8 # SMALL
db_file_multiblock_read_count = 16 # MEDIUM
# db_file_multiblock_read_count = 32 # LARGE

# db_block_buffers = number of database blocks
cached in memory

db_block_buffers tells the oracle kernel the
size of the area that stores database read from the disk

#db_block_buffers = 200 # INITIAL
# db_block_buffers = 200 # SMALL
db_block_buffers = 550 # MEDIUM
# db_block_buffers = 3200 # LARGE

Size in bytes of the shared pool

#shared_pool_size = 6500000 # INITIAL
# shared_pool_size = 3500000 # SMALL
shared_pool_size = 6000000 # MEDIUM
# shared_pool_size = 9000000 # LARGE

Number of redo blocks for checkpoint
threshold

log_checkpoint_interval = 10000

Maximum number of user processes

#processes = 50 # INITIAL
# processes = 50 # SMALL
processes = 100 # MEDIUM
# processes = 200 # LARGE

DML locks - one for each table
modified in a transaction

#dml_locks = 100 # INITIAL
# dml_locks = 100 # SMALL
dml_locks = 200 # MEDIUM
# dml_locks = 500 # LARGE

Redo circular buffer size

#log_buffer = 8192 # INITIAL
# log_buffer = 8192 # SMALL
log_buffer = 32768 # MEDIUM
# log_buffer = 163840 # LARGE

Number of sequence cache entries

#sequence_cache_entries = 10 # INITIAL
# sequence_cache_entries = 10 # SMALL
sequence_cache_entries = 30 # MEDIUM
# sequence_cache_entries = 100 # LARGE

#sequence_cache_hash_buckets = 10 # INITIAL
# sequence_cache_hash_buckets = 10 # SMALL
sequence_cache_hash_buckets = 23 # MEDIUM
# sequence_cache_hash_buckets = 89 # LARGE

# audit_trail = true # if you want auditing
# timed_statistics = true # if you want timed statistics
max_dump_file_size = 10240 # limit trace file size to 5 Meg each

Start the Archiver Process

log_archive_start = true # if you want automatic archiving

LOG_ARCHIVE_DEST = E:\{db_name}\ARCHIVE
#location of the archive directory


# define directories to store trace and alert files

background_dump_dest=%RDBMS73%\trace
user_dump_dest=%RDBMS73%\trace

Size of database block in bytes. Db_block_size can not
be changed after database creation

db_block_size must be a multiple of 512K

db_block_size = 8192

Number of job queue processes to start

snapshot_refresh_processes = 1

Password file usage

remote_login_passwordfile = shared

Enable text searching

text_enable = true


Step 4. Create your database

Once the instance has been started, it is an idle instance.

Steps to starting the database

From a DOS Prompt

set ORACLE_SID=abcd
set LOCAL =2:abcd

svrmgrl (NT)

connect internal/password

startup pfile=initabcd.ora
Creating a Database with AUTO-UNDO management
Create database mydb
Controlfile reuse
Logfile group 1( ?d:\oradata\mydb\redo\mydb_redo_01a.log?,
?e:\oradata\mydb\redo\mydb_redo_01b.log?) size 10m,
group 2( ?e:\oradata\mydb\redo\mydb_redo_02a.log?,
?f:\oradata\mydb\redo\mydb_redo_02b.log?) size 10m,
group 1( ?f:\oradata\mydb\redo\mydb_redo_03a.log?,
?d:\oradata\mydb\redo\mydb_redo_03b.log?) size 10m
maxinstances 1
maxlogfiles 5
maxloghistory 100
maxdatafiles 100
archivelog
datafile ?d:\oradata\ mydb\system\mydb_system_01.dbf? size 100M autoextend on next 20M maxsize unlimited,
default temporary tablespace temp
tempfile ?f:\oradata\ mydb\temp\mydb_temp_01.dbf? size 100M
undo tablespace undo_tbsp datafile
?e:\oradata\ mydb\rbs\mydb_undo_01.dbf? size 100M autoextend off;
Creating a Database with OMF (Oracle Managed File system)
Step 01
The following parameters are to be included in the init.ora parameter file of the database.
-- Defining the destination for the redo log files
db_create_online_dest_1 = d:\oradata\mydb\redo
db_create_online_dest_2 = e:\oradata\mydb\redo
db_create_online_dest_3 = f:\oradata\mydb\redo
-- Defining the destination for the data files
db_create_file_dest= d:\oradata\mydb\data
-- defining the undo auto management
undo_management=auto
undo_tablespace=undo_tbsp
Step 02
Create database mydb
Datafile size 200M
Logfile group 1 size 20M, group 2 size 20M, group 3 20M
Default temporary tablespace temp tempfile size 100M
Undo tablespace undo_tbsp datafile size 100M
Maxlogfiles=5
Maxlogmembers=5
Maxdatafiles=200
Noarchivelog;
This is not advised to be used for a production environment. Multiple datafile destinations are not supported and your ability to balance the IO is crippled.
This is recommended for the development environment to enable to developers to play with database objects easily.

Reviews and Comments


PMP, CAPM, PMI is a registered certification mark of the Project Management Institute, Inc

Copyright © www.techfaq360.com 2016


About US | Contact US | Privacy Policy | Terms and Conditions  | Website disclaimer  | Cancellation and Refund Policy  | Shipping & Delivery Policy