10g DATABASE Creation (OFA)
Creating Oracle Database Manually CREATE DATABASE testdb USER SYS IDENTIFIED BY pz6r58 USER SYSTEM IDENTIFIED BY y1tz5p LOGFILE GROUP 1 ('/u01/oradata/testdb/redo ('/u01/oradata/testdb/redo01.log') 01.log') SIZE 100M REUSE , GROUP 2 ('/u01/oradata/testdb/redo02.log') ('/u01/oradata/testdb/redo02.log') SIZE 100M REUSE , GROUP 3 ('/u01/oradata/testdb/redo03.log') ('/u01/oradata/testdb/redo03.log') SIZE 100M REUSE MAXLOGFILES 5 MAXLOGMEMBERS 5 MAXLOGHISTORY 1 MAXDATAFILES 100 MAXINSTANCES 1 CHARACTER SET US7ASCII NATIONAL CHARACTER SET AL16UTF16 ARCHIVELOG DATAFILE '/u01/oradata/testdb/system01.dbf' '/u01/oradata/testdb/system01.dbf' SIZE 400M REUSE EXTENT MANAGEMENT LOCAL SYSAUX DATAFILE '/u01/oradata/testdb/sysaux01.dbf' '/u01/oradata/testdb/sysaux01.dbf' SIZE 400M REUSE DEFAULT TABLESPACE users DEFAULT TEMPORARY TABLESPACE temp TEMPFILE '/u01/oradata/testdb/temp01.dbf' '/u01/oradata/testdb/temp01.dbf' SIZE 20M REUSE UNDO TABLESPACE undotbs DATAFILE '/u01/oradata/testdb/u '/u01/oradata/testdb/undotbs01.dbf' ndotbs01.dbf' SIZE 200M 200M REUSE REUSE AUTOEXTEND AUTOEXTEND ON MAXSIZE UNLIMITED ;
Database name is that itself must be eight (8) characters or less. ** File sizes above as the minimum requirement **
Exploring the Oracle DBA Technology by Gunasekaran ,Thiyagu
10g DATABASE Creation (OFA)
SAMPLE “ora.init” File *.background_dump_dest='/u01/admi *.background_dump_dest='/u01/admin/test/dump/bdum n/test/dump/bdump' p' *.compatible='10.2.0' *.control_files='/u01/oradata/tes *.control_files='/u01/oradata/testdb/ctrl/ctrl01.ctl' tdb/ctrl/ctrl01.ctl','/u01/oradata/te ,'/u01/oradata/testdb/ctrl/ stdb/ctrl/ ctrl02.ctl', '/u01/oradata/testdb/ctrl/ctrl03.ctl' '/u01/oradata/testdb/ctrl/ctrl03.ctl' *.core_dump_dest='/u01/admin/t *.core_dump_dest='/u01/admin/testdb/cdump' estdb/cdump' *.db_block_size=8192 *.db_file_multiblock_read_count=16 *.instance_name='amigo' *.db_name='test' *.job_queue_processes=10 *.log_archive_dest_1='location=/u01 *.log_archive_dest_1='location=/u01/oradata/testdb/arc /oradata/testdb/arch/' h/' log_archive_format='%t_%s_%r.dbf' *.open_cursors=1024 *.pga_aggregate_target=300m *.processes=300 *.remote_login_passwordfi *.remote_login_passwordfile='EXCLUSIVE le='EXCLUSIVE'' *.sessions=300 *.sga_target=800m *.undo_management='AUTO' *.undo_tablespace='UNDOTBS' *.user_dump_dest='/u01/admin/testdb *.user_dump_dest='/u01/admin/testdb/dump/udump' /dump/udump' *.audit_file_dest='/u01/admin/te *.audit_file_dest='/u01/admin/testdb/dump/audit' stdb/dump/audit' *.db_recovery_file_dest=’/u01/app/oracle/flash_recovery_area’ *.db_recovery_file_dest_size=2G *.dispatchers='(PROTOCOL=TCP) *.dispatchers='(PROTOCOL=TCP) (SERVICE=prodXDB)'
Exploring the Oracle DBA Technology by Gunasekaran ,Thiyagu
10g DATABASE Creation (OFA)
Manual DATABASE Creation Steps
Create directory for Admin log files Enter as oracle user $
user_oracle
$ cd /u01/app/oracle/admin $ cd /u01/oradata
Create
directories under admin and oradata $ mkdir testdb $ cd testdb
Entering into /testdb
$ mkdir adump cdump bdump dpdump pfile udump script
Create / Modify the Parameters in init.ora file “Setup Initialization parameters” according to project requirements.”
Create folders for Physical Files { Datafiles, Redofiles, Controlfiles } $ cd /u01/oradata/
(Example db_name=testdb) $ mkdir testdb
Create the Password for the database { Remote Connectivity } ** Password Should be create under /DBS directory. ** $ cd /u01/app/oracle/product/10.2.0/dbs/ $ export ORACLE_SID= $ orapwd file=oraxyzpw.ora password=sham entries=3 force=y $ cd $HOME
return to user home directory i.e. /home/oracle.
Exploring the Oracle DBA Technology by Gunasekaran ,Thiyagu
10g DATABASE Creation (OFA)
Connect to SQLPLUS $ sqlplus /nolog
SQL> connect sys as sysdba Password: xxxxxx
Startup the Instance at “Nomount” SQL> startup pfile=’/u01/app/… pfile=’/u01/app/….. .. /admin/test/pfi /admin/test/pfile/init.ora‘ le/init.ora‘ nomount
Create Database script (Createdb.SQL) Enter full path of the file prefix “dbcreate.SQL”.
Ex: /u01/app/oracle/admin//script/dbcreate.SQL SQL >@ dbcreate.SQL Database created. Please ensure that the name given in init.ora file and database creation script are same. If both are different , when creating creating DB , Oracle will throw error. error. Now DATABASE created . Need to execute (catalog.sql, catproc.sql) scripts.
Execute catalog.SQL , catproc.SQL Scripts : Catalog - Creates data dictionary views. (DBA_TABLES) Catproc - Create in built PL/SQL Procedures, Packages . (DBMS_STATS)
SQL> @/app/oracle/pro @/app/oracle/product/10.2.0/rdbms/ad duct/10.2.0/rdbms/admin/catalog.SQL min/catalog.SQL SQL> @/app/oracle/pro @/app/oracle/product/10.2.0/rdbms/ad duct/10.2.0/rdbms/admin/catproc.SQL min/catproc.SQL
or
SQL> @?/rdbms/ad @?/rdbms/admin/catalog.SQL min/catalog.SQL SQL> @?/rdbms/ad @?/rdbms/admin/cataproc.SQL min/cataproc.SQL
Exploring the Oracle DBA Technology by Gunasekaran ,Thiyagu
10g DATABASE Creation (OFA)
Okay, now database is ready to use. Lets check confirmed by given query SQL> select status from v$instance; SQL> select name from v$database
Checking Presently Installed components : DBA_REGISTRY displays info about the components loaded into the database.
SQL>select comp_id, status, version from dba_registry; SQL> SELECT comp_name comp_name || ' : '||version|| ' : '|| status FROM dba_registry; SQL> select comp_id, comp_id, comp_name, version, version, status from dba_registry ;
Query to find valid/invalid Objects and installed Products SQL> select object_name, object_type from dba_objects where owner owner = 'SYS' and status = 'VALID'; 'VALID';
SQL> select object_name, object_type from dba_objects where owner = 'SYS' and status = 'INVALID'; 'INVALID';
SQL> Select decode(detected_usages,0,2,1) nop, name, version, detected_usages, currently_used, to_char(first_usage_date,'DD/MM to_char(first_usa ge_date,'DD/MM/YYYY') /YYYY') first_usage_date, to_char(last_usage_date,'DD/MM/ to_char(last_usage_ date,'DD/MM/YYYY') YYYY') last_usage_date from dba_feature_usage_st dba_feature_usage_statistics atistics order by nop, 1, 2
Exploring the Oracle DBA Technology by Gunasekaran ,Thiyagu
10g DATABASE Creation (OFA)
Different Mount Point Directories
Tips to set control files and log files to different locations : /u01 : The Oracle database software will be installed to /u01/app/oracle. /u02 : This mount point will contain the physical Oracle files: Control File1 Online Redo Log File - Group Group 1 / Member Member 1 Online Redo Log File - Group Group 2 / Member Member 1 Online Redo Log File - Group Group 3 / Member Member 1
/u03 : This mount point will contain the physical Oracle files: Control File2 Online Redo Log File - Group Group 1 / Member Member 2 Online Redo Log File - Group Group 2 / Member Member 2 Online Redo Log File - Group Group 3 / Member Member 2
/u04 : This mount point will contain the physical Oracle files: Control File3 Online Redo Log File - Group Group 1 / Member Member 3 Online Redo Log File - Group Group 2 / Member Member 3 Online Redo Log File - Group Group 3 / Member Member 3
/u05 : This mount point will contain the all physical Oracle data files. Tablespaces are SYSTEM, UNDO, TEMP, USERS, SYSAUX , etc .. .. In a PROD ENV , these directories would be created on a separate file system. “
The main purpose to multiplex these important files is to overcome a disk
crash”. This is perfect idea creating files in different locations in prod env.
Exploring the Oracle DBA Technology by Gunasekaran ,Thiyagu
10g DATABASE Creation (OFA)
Sample Directory Creation for DB Files # mkdir -p /u02/oradata/testdb/ # chown -R oracle:oinstall oracle:oinstall /u02/oradata/testdb/ /u02/oradata/testdb/ # chmod -R 775 /u02/oradata/testdb / u02/oradata/testdb// # mkdir -p /u03/oradata/testdb/ # chown -R oracle:oinstall oracle:oinstall /u03/oradata/testdb/ /u03/oradata/testdb/ # chmod -R 775 /u03/oradata/testdb / u03/oradata/testdb//
# mkdir -p /u04/oradata/testdb/ # chown -R oracle:oinstall oracle:oinstall /u04/oradata/testdb/ /u04/oradata/testdb/ # chmod -R 775 /u04/oradata/testdb/ # mkdir -p /u05/oradata/testdb/ # chown -R oracle:oinstall oracle:oinstall /u05/oradata/testdb/ /u05/oradata/testdb/ # chmod -R 775 /u05/oradata/testdb/
Checking Ownership and Permission for {u02, u03,u04,u05} [root@linuxserver [root@lin uxserver ~]# ls -ld /u0*/ora* drwxrwxr-x 2 oracle oinstall 4096 Feb 2 20:28 /u02/oradata drwxrwxr-x 2 oracle oinstall 4096 Feb 2 20:28 /u03/oradata drwxrwxr-x 2 oracle oinstall 4096 Feb 2 20:28 /u04/oradata drwxrwxr-x 2 oracle oinstall 4096 Feb 2 20:28 /u05/oradata Check stat command shows user and group along with id ; # stat u0*/or*
Exploring the Oracle DBA Technology by Gunasekaran ,Thiyagu
10g DATABASE Creation (OFA)
OFA Compliant Directory Structure /
Root Directory
/u01/
Mount Point 1
/u01/app/
Application Software Directory
/u01/app/oracle/
Oracle Base Directory
/u01/app/oracle/admin/
Oracle Admin Directory Files
/u01/app/oracle/product/
Distribution Files
/u01/app/oracle/product/10.2.0/db_1Home Directory for Oracle 10.2.0
/u02/
User data Mount point 2
/u01/oradata/
Sub tree for Oracle Data Area 1
/u01/oradata/db_name1/
Sub tree for db_name1 DB files
/u01/oradata/db_name2/
Sub tree for db_name2 DB files
Oracle - Optimal Flexible Architecture (OFA) OFA is an oracle standard file placement and it is recommended by oracle. OFA is the set of database file configuration that are easy to maintain. OFA is designed to promote , a standard disc configuration or DIR DIR structure
for a consistent way of managing disks and directories. software executables from from database files. files. OUI separates Oracle software Using OFA , Oracle Oracle Universal Installer Installer puts by default ,
Oracle software in ORACLE_BASE\ORACLE_HOME.
Oracle database files in ORACLE_BASE\ORADATA.
Administrative files in ORACLE_BASE\ADMIN\
Exploring the Oracle DBA Technology by Gunasekaran ,Thiyagu
10g DATABASE Creation (OFA)
Oracle Optimal Flexible Arichitecture Directory Sructrure (OFA) OFA standard is a series of recommendati recommendations ons for for naming naming files files and and directories when installing and implementing implementing an Oracle database. OFA mainly helps to keep ,
Maximize good performance.
Simplifying administration task.
Assist switch between databases.
organize large amounts of complicated software and data on disk.
The first objective of of the OFA is to to segregate the location location of all all Oracle files files from other files or products which might reside reside on on the the same same system. system.
Create Admin Directory
It is used to store administration files.
It should should be in in format of ORACLE_BASE>/admin//.
Some of the directories that the admin directory normally contains.
/adump /bdump
background process trace files.
/cdump
core dump files.
/create
database creation files.
/pfile
initialization initialization parameter files.
/udump
user SQL trace files.
/arch
archived redolog files.
** adump ,bdump, ,bdump, cdump, cdump, udump udump - these are audit and trace files **
Exploring the Oracle DBA Technology by Gunasekaran ,Thiyagu
10g DATABASE Creation (OFA)
Create Oradata Directory
Oracle database files in ORACLE_BASE\ORADATA.
The oradata dir should be \oradata\.
Oracle DB related files (data files, online redolog files and control files).
Above mentioned files should should be kept in an directory directory called "ORADATA".
Naming Files In Oradata Directory Files are named so that Control files, redo log files, and datafiles are easily identifiable for each database.
Datafiles should be in format .dbf.
Example : tbs1.dbf
Online redolog files should be in format redolog .log.
Example: redolog01.lo redolog01.log g , redolog02.log, redolog03.log.
Control files should be in format control.ctl control.ctl .
Example: control01.ctl, control02.ctl, control03.ctl.
Defining an ORACLE_BASE location
ORACLE_BASE is the root of the OFA compliant dir tree.
ORACLE_BASE is the location for all Oracle files for a server.
It should be in format of
ORACLE_BASE is specified by /pm/h/u.
Example : /u01/app/oracle
To Confirm execute following command;
$ echo $ORACLE_BASE
Oracle is the OS OS user which owns the database database software installation. installation.
Exploring the Oracle DBA Technology by Gunasekaran ,Thiyagu
10g DATABASE Creation (OFA)
Defining an ORACLE_HOME location The next objective of OFA is to isolate the ORACLE_HOME files from other database files.
It should be in format of
ORACLE_HOME specified by /pm/h/u/product/v/type_[n]
Example : /u01/app/oracle/product/10.2.0/db_1/
To Confirm execute following command;
$ echo $ORACLE_HOME
where type is the purpose of installation (db,app,client (db,app,client etc.)
File System names and Mount Points :All mount points using the syntax: /pm p is a constant string.
m is a unique two –digit number. Ex : /u01 , /u02 , /u03 , etc ..
Ex of how to use OFA , to easily access different files on Linux ?
Variable
Description
pm
A mount point name
h
A standard directory name
u
The name of the owner of the directory
q
Oracle data data is stored ex : oradata
v
The version of the software
n
A TWO-DIGIT string.
dm
The value of the initialization parameter DB_NAME
Exploring the Oracle DBA Technology by Gunasekaran ,Thiyagu
10g DATABASE Creation (OFA)
Mount Points for Very Large Databases (VLDBs) /pm/q/dm => This is similar to set ORACLE DATA STORAGE FILES. pm is a Mount point name.
data is stored in this directory. directory. q is a Oracle data dm is a value of the initialization parameter db_name.
Ex:/u01/oradata/testdb/, u02/oradata/testdb/ u02/oradata/testdb/ , etc .. NOTE : Multiple drives for the same database help to prevent I/O bottlenecks.
Software Directories Oracle have to
separate the database software software from one version version of the
database to another. Multiple versions of Oracle Oracle , each version of of the Oracle software is maintained in separate directory path.
/Mount_point/Std_directory_ /Mount_point/Std_directory_name/Owner_of_d name/Owner_of_dir/product/versio ir/product/version n /u01/app/oracle/product/9.2.0.1.0 /Mount_point/Std_dir_name/ /Mount_point/Std_dir_name/Owner_of_dir/produ Owner_of_dir/product/vers/Type[_N] ct/vers/Type[_N] /u01/app/oracle/product/10.2.0/type_n type is the type of Oracle home, for example Oracle Database (db) or Oracle
Client (client), and
“
n
is an optional counter”.
Spreading Files IO Contention Contention between datafiles placed on the same disk can be a performance problem. This can be resolved by putting putting datafiles in in sepearte disks.
Exploring the Oracle DBA Technology by Gunasekaran ,Thiyagu
10g DATABASE Creation (OFA)
Spreading Database files across disk Contention Contention occurs when multiple accesses are performed in same same time. time. This type of contention contention can be reduced by separating tables, indexes, indexes, rollback segments and redo logs to separate disks. “Storing data on multiple disks for better performance and reliability.”
/u02/oradata/testdb/redolog01.log /u03/oradata/testdb/redolog02.log /u04/oradata/testdb/redolog03.log
/u01 should be used for the Oracle S/w only. /u02, /u03, /u04, /u05, .. should be be used for database files .
Understanding Filename Extensions .dmp
: Export file.
.trc
: Trace file.
.SQL , .tab : SQL script file. .dbf
: Tablespace data file.
.dat
: SQL*Loader datafile.
.ora
configuration file (init.ora). : Oracle configuration
.log
: Installation log files; Oracle Server redo log file.
.ctl
: SQL*Loader control file; Oracle Server control control file.
Tools to to interact interact with Oracle. SQL, PL/SQL, SQLPlus, iSQLPlus, DBCA and Enterprise Manager.
Exploring the Oracle DBA Technology by Gunasekaran ,Thiyagu
10g DATABASE Creation (OFA)
Points to Remember
Oracle Database Creation in “3” Ways . They are ,
Database Creation at the time of Installation or Oracle
Using Oracle DBCA [ Database Configuration assistant ]
Manually.
DB Creation using DBCA
DBCA Stands for Database Configuration Assistant (DBCA).
DBCA can be launched by the Oracle Universal Installer (OUI).
DBCA located at : /u01/app/oracle/product/10.2.0/db_1/assistants.
It automated approach and preferred way to create a database .
Version of the Oracle Database Database SQL>select * from v$version; SQL>select version from v$instance; SQL>select * from v$version where banner like 'oracle%'; SQL>select * from PRODUCT_COMPONENT_VERSION where product like 'Oracle Database%'; SQL> select comp_name, version from dba_registry; SQL>select * from PRODUCT_CO PRODUCT_COMPONENT_VERSIO MPONENT_VERSION; N; PRODUCT
VERSION
STATUS
NLSRTL
10.2.0.1.0
PRODUCTION PRODUCTION
Oracle Database 10g Enterprise Edition
10.2.0.1.0
PROD
PL/SQL
10.2.0.1.0
Production
TNS for Linux
10.2.0.1.0
PRODUCT
Exploring the Oracle DBA Technology by Gunasekaran ,Thiyagu
10g DATABASE Creation (OFA)
Oracle Version - Release Number Format Format SQL> select select * from v$version; BANNER -------------------------------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod PL/SQL Release 10.2.0.1.0 - Production CORE 10.2.0.1.0
Production
TNS for Linux: Version 10.2.0.1.0 - Production NLSRTL Version 10.2.0.1.0 - Production
10.2.0.1.0
1st digit
Major database release number.
2nd digit
Database maintenance release number. number.
3rd digit
Application server release number.
4th digit
Component Component specific release number. number.
5th digit
Platform specific release number. number.
Database Compatible Level The Compatibility level of database corresponds
to the value of the
COMPATIBLE initialization initialization parameter. If we set the COMPATIBLE initialization initialization parameter to 10.2.0 , then the database runs at 10.2.0 compatibility level. COMPATIBILITY allows to use a new release of Oracle. SQL>show parameter compatible; SQL>select * from database_compatible_level; database_compatible_level; SQL>select value from v$parameter where name='compatible';
Exploring the Oracle DBA Technology by Gunasekaran ,Thiyagu
10g DATABASE Creation (OFA)
Once the upgrade is complete, we can increase the maximum level for the Oracle Database. After the database upgrade, the compatible parameter is set to database database version version (new feature stores on disk including data dictionary dictionary changes) that cannot be processed with your previous release However after upgrade if compatible parameter is not changed then new feature of the upgraded version will not be available. For example if database version is 10.2.0.4 10.2.0.4 but compatible compatible parameter is set to 9.2 then certain certain feature like RMAN compression will not work.
SQL* PLUS SQL*plus is a command line interface (oracle utility). SQL*plus is installed with every oracle oracle database database installation. installation.
$ORACLE_HOME/bin directory. SQL*plus is located in $ORACLE_HOME/bin SQL*plus tool that provides access to the Oracle RDBMS. SQL*plus is frequently used by DBAs and Developers to interact with the
Oracle database. It is enable to perform
Printing query results
Connect to an oracle database
Startup and shutdown an oracle database
ENTER/EXECUTE SQL commands and PL/SQL blocks (sub – programs).
OPERATING SYSTEM commands entered from a SQL*Plus session using the HOST command that don’t affect the current SQL*Plus session.
Various client-based tools that can be used to run SQL, including “SQL*Plus, iSQL*Plus, SQL*Plus Worksheet, third-party tools, Open Database Connectivity (ODBC), Java Database Connectivity (JDBC), and Oracle Call Interface (OCI)”.
Exploring the Oracle DBA Technology by Gunasekaran ,Thiyagu
10g DATABASE Creation (OFA)
Difference between sql*plus and isql*plus SQL*Plus and iSQL*Plus is an environment to communicate, and execute the
SQL's statements with database server. iSQL*Plus is a component of the SQL*Plus product. SQL*Plus is command line mode, does not gives formatted output. SQL*Plus requires Oracle client to be installed to run this. iSQL*Plus is a web browser-based interface to SQL*Plus iSQL*Plus is html based and gives gives a formatted output.
browser connected to the Internet or or intranet. iSQL*Plus runs in a web browser iSQL*Plus doesn't require any software to be installed to use it. iSQL*Plus allows to save queries and provides formatted results in HTML table.
Using iSQL*Plus cannot log on as SYSDBA to perform any maintenance. isql*Plus is enable to perform ,
Enter, edit, run and save SQL commands and PL/SQL blocks.
Calculate, and print query results.
List column definitions for any table.
SQL*Plus , iSQL*Plus , Oracle Oracle Enterprise Manager , and and Third party tools; tools; SQL*Plus
It is a interface between user and Oracle database. It Provide an environment to use the SQL (a (a query language) to communicate communicate with with oracle database. database. database. iSQL*PLUS : is a browser-based (sql*plus) interface to an Oracle database. ORACLE ENTERPRISE MANAGER (OEM) : is a GUI- based tool to perform
administrative tasks tasks on database objects objects and structures.
Exploring the Oracle DBA Technology by Gunasekaran ,Thiyagu
10g DATABASE Creation (OFA)
Why iSQL*PLUS Commands
can’t
access the Database ?
We have two things (commands and statements) using either SQL*Plus or iSQL*Plus. DDL, DML, DCL
these are
SQL
Statements
not
iSQL*Plus'
commands. In these statements we have create, alter, drop, insert, update, delete, commit, commit, rollback, rollback, grant, revoke, select etc .. The iSQL* Plus commands can't access the database. iSQL* Plus commands don't include the SQL commands such as SELECT or INSERT even though the SQL commands. iSQL*Plus commands are edit, save, run, set pagesize, set linesize, set
arraysize
etc etc .. The iSQL*Plus commands commands function is
formatting the output or setting the parameters.
SQL* PLUS Commands
To describe a table SQL> desc emp;
To re-execute a command SQL> / Note: the buffer can only hold one previous statement.
To find out what is in that buffer SQL> l or SQL> list
Changing part of previous query in the buffer before running it SQL> c/// n-name>
To save the query in the buffer to a file SQL> save .sq .sqll
Exploring the Oracle DBA Technology by Gunasekaran ,Thiyagu
10g DATABASE Creation (OFA)
To find out more sqlplus commands SQL> help index
To switch between sqlplus and your host prompt SQL> host
To echo something SQL> prompt "text here"
To make changes to sql statement in an editor SQL> ed
To know currently using editor to run sql statements SQL> define _editor
If forget a specific SQL Command ; SQL> HELP ; SQL> HELP menu
SQL Command outputs to a file SQL> SPOOL ; SQL> SPOOL file1 SHOW ALL command displays current current settings. SQL> SHOW ALL POINTS TO NOTE :
To start iSQL*Plus, need to start the server-side listener process. iSQL*Plus , does not allow manipulation of values in the database. iSQL*Plus is no more exists in 11g. i.e. It is desupported from 11g iSQL*Plus can connect through internet i.e. this is browser based sql*plus.
Exploring the Oracle DBA Technology by Gunasekaran ,Thiyagu
10g DATABASE Creation (OFA)
Oracle Database Environment We can differentiate database environments into three categories:
Development Environment.
Test Environment.
Production Environment.
TEST/PRODUCTION TEST/PRODUCTI ON : Thse environments must be fully licensed. DEVELOPMENT : We can use full database license for development
or
optionally can download absolutely free database software from Oracle Technology Network (OTN). (OTN). In order to download an Oracle product from OTN, we have to accept the OTN Development License. According According to this agreement, user can use the licensed product for development purpose and has not the right to deploy applications.
Oracle Database Editions. Oracle database comes in five flavors or edition , Express Edition Personal Edition Standard Edition one Standard Edition Enterprise Edition
Oracle Database 10g Express Edition : This is an entry level, Free version of Oracle that is limited to 1 processor, 1 GB RAM and 4 GB of data and can be easily upgraded to standard or Enterprise edition. (one database per machine).
Exploring the Oracle DBA Technology by Gunasekaran ,Thiyagu
10g DATABASE Creation (OFA)
Oracle Database 10g Standard Edition One: Full - featured database (-small to medium size business -) for servers with up to two sockets. It should be licensed on servers supporting supporting up to two CPUs.
Oracle Database Database 10g 10g Standard Edition Full - featured database (-medium-sized business env-) for servers with up to four sockets. Standard Edition can be licensed on single or clustered servers with up to four processors. Standard Edition supports Real Application Clusters
Oracle Database 10g Enterprise Edition The Enterprise Edition (EE) the performance, availability, scalability, and security required for critical application applicationss such as ( OLTP , OLAP ,INTERNET APPLICATIONS) without any restriction. Enterprise Edition contains all of the
components of the Oracle Database.
Oracle Personal Edition Personal Edition is available on Windows platforms only. Avail with EE and exception for Oracle RAC option, which cannot be used with Personal Edition.
SYS_CONTEXT ( Retrives Oracle Environment ) This function is used to view some v$views without DBA privilege.
SQL> conn user1 /user1 SQL>select sys_context('userenv', 'db_name') from dual; SQL>select sys_context('userenv', 'instance_name') from dual;
Exploring the Oracle DBA Technology by Gunasekaran ,Thiyagu
10g DATABASE Creation (OFA)
PLSQL Code SQL> set serverout on on size 10000 SQL> begin dbms_output.put_line(sys_co dbms_outpu t.put_line(sys_context('userenv' ntext('userenv',, 'instance_name 'instance_name')); ')); end; /
SYS_CONTEXT FUNCTION can be used to retrieve information about the Oracle environment. SYS_CONTEXT returns the value of parameter associated with the context “namespace”. Oracle provides a built-in namespace called “USERENV”, which describes the current session. session.
SYS_CONTEXT ( NAMESPACE, NAMESPACE, PARAMETER)
SID SQL>select sys_context sys_context('userenv', ('userenv', 'sid') from dual;
Sysdba or not? (boolean) SQL>select sys_context ('userenv', 'isdba') 'isdba') from dual
Terminal SQL>select sys_context sys_context('userenv', ('userenv', 'terminal') 'terminal') from dual; dual;
Session/user and id SQL>select sys_context(' sys_context('userenv', userenv', 'session_us 'session_user') er') from dual; SQL>select sys_context(' sys_context('userenv', userenv', 'current_schema') from dual SQL>select sys_context(' sys_context('userenv', userenv', 'current_schemai 'current_schemaid') d') from dual;
Exploring the Oracle DBA Technology by Gunasekaran ,Thiyagu
10g DATABASE Creation (OFA)
Session_id SQL>select sys_context sys_context('userenv', ('userenv', 'sessionid') 'sessionid') from dual;
Session_userid SQL>select sys_context sys_context('userenv', ('userenv', 'session_userid') 'session_userid') from dual; dual;
Server and host SQL>select sys_context sys_context('userenv', ('userenv', 'server_host') 'server_host') from dual dual ;
Service_name SQL>select sys_context sys_context('userenv', ('userenv', 'service_name') 'service_name') from dual;
Os_user SQL>select sys_context sys_context('userenv', ('userenv', 'os_user') 'os_user') from dual
Authentication SQL>select sys_context('userenv', 'authentication_type') from dual;
Calculate size of the Database : An oracle database consists of “Data files, redo log files, control files, temporary files”. Whenever we say the size of the database actually means the summation of these all files. “ The biggest portion of a database's size comes from the datafiles. “
To calculate size of the DATA files SQL> select sum(bytes)/1024/1024 "SIZE" from dba_data_files;
To calculate size of all TEMP files SQL> select nvl(sum(bytes), 0)/1024/1024 "SIZE" from dba_temp_fil dba_temp_files; es;
Exploring the Oracle DBA Technology by Gunasekaran ,Thiyagu
10g DATABASE Creation (OFA)
To calculate the size of the on-line redo-logs: SQL>select sum(bytes)/1024/1024 sum(bytes)/1024/1024 "SIZE" from sys.v_$log;
To calculate the size of the control files SQL>select sum (BLOCK_SIZE*FILE_SIZE_BLKS/1024/1024) "SIZE" from v$controlfile;
To calculate the total size of the database SQL>select a.data_size + b.temp_size b.temp_size + c.redo_size + d.controlfile_size d.controlfile_size " total_size in MB" from (select sum(bytes)/1024/1024 data_size from dba_data_files ) a, ( select nvl(sum(bytes),0)/10 nvl(sum(bytes),0)/1024/1024 24/1024 temp_size from dba_temp_files ) b, ( select sum(bytes)/1024/1024 redo_size from sys.v_$log ) c, (select sum(BLOCK_SIZE*FIL sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/102 E_SIZE_BLKS)/1024/1024 4 controlfile_ controlfile_size size from v$controlfile) d;
Clearn script for DB size calculation The size of the database is the total size of the datafiles that make up the tablespaces of the database. database. To findout DB growth growth , check the below link : http://neeraj-dba.blogspot.com/2011/04/tracking-oracle-database-growth.html http://neeraj-dba.blogspot.com/2011/12/estimate-tablespace-growth-in-oracle.html
Following Following script is pretty clean which which outputs quite a few useful useful statistics of your Oracle database (very much similar to 'df' command).
Exploring the Oracle DBA Technology by Gunasekaran ,Thiyagu
10g DATABASE Creation (OFA)
dba_file_space_usage.sql SQL> SELECT /*+ ordered */ d.tablespace_name
tablespace
, d.file_name
filename
, d.file_id
file_id
, d.bytes
filesize
, NVL((d.bytes - s.bytes), d.bytes)
used
, TRUNC(((NVL((d.bytes TRUNC(((NVL((d.bytes - s.bytes) , d.bytes)) d.bytes)) / d.bytes) * 100) pct_used FROM sys.dba_data_files sys.dba_data_fi les d , v$datafile v , ( select file_id, SUM(bytes) bytes from sys.dba_free_spac sys.dba_free_space e GROUP BY file_id) s WHERE (s.file_id (+)= d.file_id) AND (d.file_name = v.name) UNION SELECT d.tablespace_name
tablespace
, d.file_name
filename
, d.file_id
file_id
, d.bytes
filesize
, NVL(t.bytes_cached, 0)
used
, TRUNC((t.bytes_cached / d.bytes) * 100) pct_used FROM
Exploring the Oracle DBA Technology by Gunasekaran ,Thiyagu
10g DATABASE Creation (OFA)
sys.dba_temp_files d , v$temp_extent_pool t , v$tempfile v WHERE (t.file_id (+)= d.file_id) AND (d.file_id = v.file#) /
POINTS TO REMEMBER :V$DATABASE shows DB_NAME V$THREAD shows ORACLE_SID SQL>show parameters DB_NAME; SQL> show parameters INSTANCE_NAME; ORACLE_SID is case sensitive in Unix / Linux environments.. SID_NAME is system identifier and it’s uniq for ORACLE DATABASE.
By default SID_NAME , INSTANCE_NAME are same (we (we can change). Instance is combination of memory and background process. SERVICE_NAME is a logical way to connect REMOTE DATABASE.
Differences between Enterprise, Standard and Personal Editions on Oracle 10.2 [ID 465465.1]. Oracle starts an instance it reads the spfile or pfile to determine the initialization parameters. It uses these paramters to allocate the SGA and create background processes. At NOMOUNT stage the instance is started and also also Oracle background process (PMON, SMON, LGWR, DBWR) and etc .. **When ** When the instance instance is in NOMOUNT state, the DB is not available available for use.** use.**
Exploring the Oracle DBA Technology by Gunasekaran ,Thiyagu