Hand’s-On Guide FOR ORACLE DBA’s
By
All rights reserved. No part of this publication may be reproduced, stored in retrieval system or transmitted, in any form or by any means, electronic, mechanical, photocopying, recording, or
otherwise, without the prior permission of the author.
@ Anup Kumar Srivastav Published by Anup Kumar Srivastav
Powered by Pothi.com http://pothi.com
ABOUT ME I am Anup Kumar Srivastav. I have been over 7+ years of experience in IT Industries, of which 3-year as ERP techno-Functional, 1.2 years as a Oracle Core DBA and 3 years 8 months as a Oracle Application DBA, with major clients like Container Corporation of India, FLEX Industries Ltd, Shiv-Vani Oil & Exploration Services Ltd, Jhunjulwala Vanaspati Ltd and Lord Distillery.
I have a broad experience in Implementation, Managing and up-gradation of Oracle Database and Oracle Application (EBS) on Windows, Linux and Sun Solaris platform.
At present I am working as Senior System Engineer with HCL COMNET LIMITED, Noida. Thanking You.
Yours Truly,
Content Description of Content Page No
1. Oracle Architecture 7-16
How to Oracle work 7
Oracle Architecture (Diagram) 7
Basics of Oracle Architecture 7
Starting up a database 11
Shutdown the database 12
Managing an Oracle Instance 13
Oracle Background Processes 13
2. Oracle 10gR12 Software/binary installation 17-22
10gR2 Installation on Linux 17
10gR2 installation on Solaris 20
3. Database Creation (Manually) 23-45
For File System Storage Mechanism on Linux/Solaris 23 For OMF Enable Database on Linux/Solaris 24
For ASM Mechanism on Linux 25
For ASM Mechanism on Solaris 33
For RAW Device on Linux 37
For RAW Device on Solaris 41
For ASM Mechanism on Windows 42
4. Managing Tablespace 46-49
How to Manage Tablespace 46
Query Related to Tablespace 48
5. Managing Database 50-64
Managing Data file 50
How to Drop Data file 52
Managing Control File 54
Managing Redo Log File 56
Managing Temporary Tablespace 58
Managing Undo Tablespace 61
6. Creating and Managing 10G ASM 65-80
About ASM, Functionality and Advantage 65 ASM instance, archicture and management 66
Managing ASM Directory 66
Managing ASM Disk Group 69
Move Non-ASM database to ASM 72
How to access ASM file 75
ASM Disk Crash Case Study 76
Useful Query for ASM 78
User-Managed Backup Terminology 81
Type of Recovery and Diff. between Resetlog and No resetlogs Option 83 Recovery from missing or corrupted datafile 85
Recovery from missing or corrupted redo log group 86
Disaster recovery 87
8. Recovery Manager (RMAN) 91-104
Basic RMAN Tutorial 91
Type of RMAN Backup 93
RMAN: RESTORE Concept 96
How to Configure RMAN 101
Use RMAN with a Recovery Catalog 103
9. RMAN Recovery Case Study 105-120
Datafile Recovery 105
Controlfile Recovery 109
Redolog file Recovery 112
Disaster Recovery 114
10 Standby Database 121-136
Creating standby database using Manual 121
Creating standby database using RMAN 127
Standby Database Maintenance 131
Database Switchover/Failover 131
Standby Diagnosis Query for Primary Node 132 Standby Diagnosis Query for Standby Node 135
11. Oracle Database Up-gradation 137-170
Why upgrade oracle database to higher version 137 Role of DBA during database up-gradation 137 Different option for upgrade method, difference and process 137 Difference between Up-gradation and Migration 140
About Oracle release and Upgrade path 140
Converting database to 64 bit 143
Moving from standard edition to enterprise edition and via-verse 145 Upgrade Project Project A 147 Upgrade 8.1.x(x>7) to 8.1.7 Project B 154 Upgrade 8.1.7 to 9.2.0 Project C 163 Upgrade 8.1.7 to 10.2.0 Project D 168 Upgrade 9.2.0 to 10.2.0
Migrate through Export / Import 171 Migrate through ―RMAN Convert database‖ on source host 172 Migrate through ―RMAN Convert database‖ on destination host 176 Migrate by using Transporting Tablespace 181
Oracle Architecture How oracle works?
An instance is currently running on the computer that is executing Oracle called database server. A computer is running an application (local machine) runs the application in a user process. The client application attempts to establish a connection to the server using the proper Net8 driver.
When the oracle server detects the connection request from the client it‘s check client authentication, if authentication pass the oracle server creates a (dedicated) server process on behalf of the user process. When the user executes a SQL statement and commits the transaction. For example, the user changes a name in a row of a table. The server process receives the statement and checks the shared pool for any shared SQL area that contains an identical SQL statement. If a shared SQL area is found, the server process checks the user's access privileges to the requested data and the previously existing shared SQL area is used to process the statement; if not, a new shared SQL area is allocated for the statement so that it can be parsed and processed. The server process retrieves any necessary data values from the actual datafile or those stored in the system global area. The server process modifies data block in the system global area. The DBWn process writes modified blocks permanently to disk when doing so is efficient. Because the transaction committed, the LGWR process immediately records the transaction in the online redo log file. If the transaction is successful, the server process sends a message across the network to the application. If it is not successful, an appropriate error message is transmitted. Throughout this entire procedure, the other background processes run, watching for conditions that require intervention.
Oracle Architecture Diagram
The Basics of Oracle Architecture
As an Oracle DBA, you must be understand the concepts of Oracle architecture clearly. It is a basic step or main point that you need before you go to manage your database. By this article, I will try to share my knowledge about it. Hope it can be useful for you.
What is An Oracle Database?
Basically, there are two main components of Oracle database –– instance and database itself. An instance consists of some memory structures (SGA) and the background processes.
Above figure show, two main components of Oracle database Instance
As we cover above, instance is consist of the memory structures and background processes. The memory structure itself consists of System Global Area (SGA), Program Global Area (PGA). In the other hand, the mandatory background processes are Database Writer (DBWn), Log Writer (LGWR), Checkpoint (CKPT), System Monitor (SMON), and Process Monitor (PMON). And another optional background processes are Archiver (ARCn), Recoverer (RECO), etc.
Figure show the instance components System Global Area
SGA is the primary memory structures. This area is broken into a few of part memory –– Buffer Cache, Shared Pool, Redo Log Buffer, Large Pool, and Java Pool.
Buffer Cache
Buffer cache is used to stores the copies of data block that retrieved from datafiles. That is, when user retrieves data from database, the data will be stored in buffer cache. Its size can be manipulated via DB_CACHE_SIZE parameter in init.ora initialization parameter file.
Shared Pool
Shared pool is broken into two small part memories –– Library Cache and Dictionary Cache. The library cache is used to stores information about the commonly used SQL and PL/SQL statements; and is managed by a Least Recently Used (LRU) algorithm. It is also enables the sharing those statements among users. In the other hand, dictionary cache is used to stores information about object definitions in the database, such as columns, tables, indexes, users, privileges, etc.
The shared pool size can be set via SHARED_POOL_SIZE parameter in init.ora initialization parameter file.
Redo Log Buffer
Each DML statement (insert, update, and delete) executed by users will generates the redo entry. What is a redo entry? It is an information about all data changes made by users. That redo entry is stored in redo log buffer before it is written into the redo log files. To manipulate the size of redo log buffer, you can use the LOG_BUFFER parameter in init.ora initialization parameter file. Large Pool
Large pool is an optional area of memory in the SGA. It is used to relieves the burden place on the shared pool. It is also used for I/O processes. The large pool size can be set by LARGE_POOL_SIZE parameter in init.ora initialization parameter file. Java Pool
As its name, Java pool is used to services parsing of the Java commands. Its size can be set by JAVA_POOL_SIZE parameter in init.ora initialization parameter file. Oracle Background Processes
Oracle background processes is the processes behind the scene that work together with the memories.
DBWn
Database writer (DBWn) process is used to write data from buffer cache into the datafiles. Historically, the database writer is named DBWR. But since some of Oracle version allows us to have more than one database writer, the name is changed to DBWn, where n value is a number 0
to 9.
LGWR
Log writer (LGWR) process is similar to DBWn. It writes the redo entries from redo log buffer into the redo log files.
CKPT
Checkpoint (CKPT) is a process to give a signal to DBWn to writes data in the buffer cache into datafiles. It will also updates datafiles and control files header when log file switch occurs. SMON
System Monitor (SMON) process is used to recover the system crach or instance failure by applying the entries in the redo log files to the datafiles. PMON
Process Monitor (PMON) process is used to clean up work after failed processes by rolling back
the transactions and releasing other resources.
Database
We can broken up database into two main structures –– Logical structures and Physical structures.
Logical Structures
The logical units are tablespace, segment, extent, and data block. Bellow Figure, will illustrate the relationships between those units.
Tablespace
A Tablespace is a grouping logical database objects. A database must have one or more tablespaces. In the Figure 3, we have three tablespaces –– SYSTEM tablespace, Tablespace 1, and Tablespace 2. Tablespace is composed by one or more datafiles. Segment
A Tablespace is further broken into segments. A segment is used to stores same type of objects. That is, every table in the database will store into a specific segment (named Data Segment) and every index in the database will also store in its own segment (named Index Segment). The other
segment types are Temporary Segment and Rollback Segment. Extent
A segment is further broken into extents. An extent consists of one or more data block. When the database object is enlarged, an extent will be allocated. Unlike a tablespace or a segment, an
extent cannot be named.
Data Block
A data block is the smallest unit of storage in the Oracle database. The data block size is a specific number of bytes within tablespace and it has the same number of bytes. Physical Structures
The physical structures are structures of an Oracle database (in this case the disk files) that are not directly manipulated by users. The physical structure consists of datafiles, redo log files, and control files.
Datafiles
A datafile is a file that correspondens with a tablespace. One datafile can be used by one tablespace, but one tablespace can has more than one datafiles.
Redo Log Files
Redo log files are the files that store the redo entries generated by DML statements. It can be used for recovery processes.
Control Files
Control files are used to store information about physical structure of database, such as datafiles size and location, redo log files location, etc.
Starting up a database
This article explains the procedures involved in starting an Oracle instance and database. First Stage: Oracle engine start an Oracle Instance
When Oracle starts an instance, it reads the initialization parameter file to determine the values of initialization parameters. Then, it allocates an SGA, which is a shared area of memory used for database information, and creates background processes. At this point, no database is associated with these memory structures and processes.
Second Stage: Mount the Database
To mount the database, the instance finds the database control files and opens them. Control files are specified in the CONTROL_FILES initialization parameter in the parameter file used to start the instance. Oracle then reads the control files to get the names of the database's datafiles and redo log files.
At this point, the database is still closed and is accessible only to the database administrator. The database administrator can keep the database closed while completing specific maintenance operations. However, the database is not yet available for normal operations.
Opening a mounted database makes it available for normal database operations. Usually, a database administrator opens the database to make it available for general use.
When you open the database, Oracle opens the online datafiles and online redo log files. If a tablespace was offline when the database was previously shut down, the tablespace and its corresponding datafiles will still be offline when you reopen the database.
If any of the datafiles or redo log files are not present when you attempt to open the database, then Oracle returns an error. You must perform recovery on a backup of any damaged or missing files before you can open the database.
Open a Database in Read-Only Mode
You can open any database in read-only mode to prevent its data from being modified by user transactions. Read-only mode restricts database access to read-only transactions, which cannot write to the datafiles or to the redo log files.
Disk writes to other files, such as control files, operating system audit trails, trace files, and alert files, can continue in read-only mode. Temporary tablespaces for sort operations are not affected by the database being open in read-only mode. However, you cannot take permanent tablespaces offline while a database is open in only mode. Also, job queues are not available in read-only mode.
Read-only mode does not restrict database recovery or operations that change the database's state without generating redo data. For example, in read-only mode: Datafiles can be taken offline and online
Offline datafiles and tablespaces can be recovered
The control file remains available for updates about the state of the database one useful application of read-only mode is that standby databases can function as temporary reporting databases.
Database Shutdown
The three steps to shutting down a database and its associated instance are: Close the database.
Unmount the database. Shut down the instance. Close a Database
When you close a database, Oracle writes all database data and recovery data in the SGA to the datafiles and redo log files, respectively. Next, Oracle closes all online datafiles and online redo log files. At this point, the database is closed and inaccessible for normal operations. The control files remain open after a database is closed but still mounted.
Close the Database by Terminating the Instance
In rare emergency situations, you can terminate the instance of an open database to close and completely shut down the database instantaneously. This process is fast, because the operation of writing all data in the buffers of the SGA to the datafiles and redo log files is skipped. The subsequent reopening of the database requires recovery, which Oracle performs automatically.
Un mount a Database
After the database is closed, Oracle un mounts the database to disassociate it from the instance. At this point, the instance remains in the memory of your computer.
After a database is un mounted, Oracle closes the control files of the database. Shut Down an Instance
The final step in database shutdown is shutting down the instance. When you shut down an instance, the SGA is removed from memory and the background processes are terminated. Abnormal Instance Shutdown
In unusual circumstances, shutdown of an instance might not occur cleanly; all memory structures might not be removed from memory or one of the background processes might not be terminated. When remnants of a previous instance exist, a subsequent instance startup most likely will fail. In such situations, the database administrator can force the new instance to start up by first removing the remnants of the previous instance and then starting a new instance, or by issuing a SHUTDOWN ABORT statement in Enterprise Manager.
Managing an oracle instance
When Oracle engine starts an instance, it reads the initialization parameter file to determine the values of initialization parameters. Then, it allocates an SGA and creates background processes. At this point, no database is associated with these memory structures and processes.
Type of initialization file:
Static (PFILE) Persistent (SPFILE)
Text file Binary file
Modification with an OS editor Cannot Modified
Modification made manually Maintained by the Server
Initialization parameter file content: Instance parameter
Name of the database
Memory structure of the SGA Name and location of control file Information about undo segments
Location of udump, bdump and cdump file Creating an SPFILE:
Create SPFILE=‘………..ORA‘ From PFILE=‘………..ORA‘; Note:
Required SYSDBA Privilege. Execute before or after instance startup. Oracle Background Processes
An Oracle instance runs two types of processes – Server Process
Background Process
Before work user must connect to an Instance. When user LOG on Oracle Server Oracle Engine create a process called Server processes. Server process communicate with oracle instance on the behalf of user process.
Each background process is useful for a specific purpose and its role is well defined.
Background processes are invoked automatically when the instance is started. Database Writer (DBW’r)
Process Name: DBW0 through DBW9 and DBWa through DBWj Max Processes: 20
This process writes the dirty buffers for the database buffer cache to data files. One database writer process is sufficient for most systems; more can be configured if essential. The initialisation parameter, DB_WRITER_PROCESSES, specifies the number of database writer processes to start.
The DBWn process writes dirty buffer to disk under the following conditions: When a checkpoint is issued.
When a server process cannot find a clean reusable buffer after scanning a threshold number of buffers.
Every 3 seconds.
When we place a normal or temporary table space offline and read only mode When we drop and truncate table.
When we put a table space in backup mode; Log Writer (LGWR)
Process Name: LGWR Max Processes: 1
The log writer process writes data from the redo log buffers to the redo log files on disk.
The writer is activated under the following conditions:
When a transaction is committed, a System Change Number (SCN) is generated and tagged to it. Log writer puts a commit record in the redo log buffer and writes it to disk immediately along with the transaction's redo entries.
Every 3 seconds.
When the redo log buffer is 1/3 full.
When DBWn signals the writing of redo records to disk. All redo records associated with changes in the block buffers must be written to disk first (The write-ahead protocol). While writing dirty buffers, if the DBWn process finds that some redo information has not been written, it signals the LGWR to write the information and waits until the control is returned.
Log writer will write synchronously to the redo log groups in a circular fashion. If any damage is identified with a redo log file, the log writer will log an error in the LGWR trace file and the system Alert Log. Sometimes, when additional redo log buffer space is required, the LGWR will
even write uncommitted redo log entries to release the held buffers. LGWR can also use group commits (multiple committed transaction's redo entries taken together) to write to redo logs when a database is undergoing heavy write operations.
The log writer must always be running for an instance. System Monitor
Process Name: SMON Max Processes: 1
This process is responsible for instance recovery, if necessary, at instance startup. SMON also cleans up temporary segments that are no longer in use. SMON wakes up about every 5 minutes to perform housekeeping activities. SMON must always be running for an instance.
Process Monitor Process Name: PMON Max Processes: 1
This process is responsible for performing recovery if a user process fails. It will rollback uncommitted transactions. PMON is also responsible for cleaning up the database buffer cache and freeing resources that were allocated to a process. PMON also registers information about the instance and dispatcher processes with network listener.
PMON wakes up every 3 seconds to perform housekeeping activities. PMON must always be running for an instance.
Checkpoint Process Process Name: CKPT Max processes: 1
Checkpoint process signals the synchronization of all database files with the checkpoint information. It ensures data consistency and faster database recovery in case of a crash.
CKPT ensures that all database changes present in the buffer cache at that point are written to the data files, the actual writing is done by the Database Writer process. The datafile headers and the control files are updated with the latest SCN (when the checkpoint occurred), this is done by the log writer process.
The CKPT process is invoked under the following conditions:
When a log switch is done.
When the time specified by the initialization parameter LOG_CHECKPOINT_TIMEOUT exists between the incremental checkpoint and the tail of the log; this is in seconds.
When the number of blocks specified by the initialization parameter LOG_CHECKPOINT_INTERVAL exists between the incremental checkpoint and the tail of the log; these are OS blocks.
The number of buffers specified by the initialization parameter FAST_START_IO_TARGET required to perform roll-forward is reached.
Oracle 9i onwards, the time specified by the initialization parameter FAST_START_MTTR_TARGET is reached; this is in seconds and specifies the time required for a crash recovery. The parameter FAST_START_MTTR_TARGET replaces
LOG_CHECKPOINT_INTERVAL and FAST_START_IO_TARGET, but these parameters can still be used.
When the ALTER SYSTEM SWITCH LOGFILE command is issued. When the ALTER SYSTEM CHECKPOINT command is issued.
Incremental Checkpoints initiate the writing of recovery information to datafile headers and controlfiles. Database writer is not signaled to perform buffer cache flushing activity here.
Oracle 10gR12 Software/binary installation (10gR2 Installation on Linux)
Note: Bellow mention steps are for 10g Release 1 & 2 (32-bit/64-bit) on Red Hat Enterprise Linux 4.
Note: When you install Linux for Oracle you should create separate file system for ORACLE Software and Oracle Database
2.5 GB of disk space should be required by Oracle Software
1.3 GB of disk space should be required by General Purpose Database. Here we have created two mount point /oracle and /database for oracle software and database. Prerequisite Steps:
Check Memory
Memory should be 512 MB of RAM. How to check the size of physical memory? $ grep MemTotal /proc/meminfo
Check swap space
Swap space should be 1 GB or Twice the size of RAM How to check the size of Swap space?
$ grep SwapTotal /proc/meminfo
If you don‘t have 1 GB of twice of the size of RAM. You must add temporary swap space to your system by creating a temporary swap file. Bellow I am describing How to Add Swap Space?
How to Add Swap Space? Log in as a ROOT
$ dd if=/dev/zero of=tmpswap bs=1k count=900000 $ chmod 600 tmpswap
$ mkswap tmpswap $ swapon tmpswap Check TMP Space
Oracle Universal Installer (OUI) requires up to 400 MB of free space in the /tmp directory. How to check the space in /tmp?
$ df /tmp
If you don‘t have enough space in the /tmp filesystem, you must temporarily create a tmp directory in another filesystem. Bellow I am describing all steps for adding temp space?
Step 1 Log in as a ROOT user Step 2 mkdir //tmp
Step 3 chown root.root //tmp Step 4 chmod 1777 //tmp Step 5 export TEMP=/ Step 6 export TMPDIR=/
After Completion of Installation, you must remove the temporary /temp directory. Bellow I am describing How to remove temporary /temp directory?
Step 1 Log in as a Root Step 2 $ rmdir //tmp Step 3 $unset TEMP Step 4 $unset TMPDIR
Check Software RPM Package
Before install of Oracle Database 10g software you need to check the system for required RPMs. make-3.79
binutils-2.14 gcc-3.2 libaio-0.3.96 glibc-2.3.2-95.27
Check Kernel parameter
How to see all kernel parameters? Step 1 Log in as a Root user Step 2 $ sysctl –a
Bellow I am describing kernel parameters, which have to be set to values greater than or equal to the recommended values which can be changed in the proc filesystem:
shmmax = 2147483648 shmmni = 4096 shmall = 2097152 shmmin = 1 shmseg = 10 semmsl = 250 semmns = 32000 semopm = 100 semmni = 128 file-max = 65536 ip_local_port_range = 1024 65000
NOTE: Do not change the value of any kernel parameter on a system where it is already higher than listed as minimum requirement.
Note: Just you add minimum required value in /etc/sysctl.conf file which is used during the boot process: kernel.shmmax=2147483648 kernel.sem=250 32000 100 128 fs.file-max=65536 net.ipv4.ip_local_port_range=1024 65000 net.core.rmem_default=1048576 net.core.rmem_max=1048576 net.core.wmem_default=262144 net.core.wmem_max=262144
become effective immediately, execute the following command: Step 1 Log in as a Root
Step 2 $ sysctl –p Installation Steps:
Creating Oracle User Accounts and Group Step 1 Log in as a Root
Step 2 $groupadd dba # group of users to be granted SYSDBA system privilege Step 3 $groupadd oinstall # group owner of Oracle files
Step 4 useradd -c "Oracle software owner" –d /home/oracle -g oinstall -G dba oracle Step 5 chown oracle:dba /home/oracle /oracle
Step 6 chown oracle:dba /home/oracle /database Step 5 passwd oracle
Start Installation
Starting Oracle Universal InstallerInsert the Oracle CD that contains the image of the software. If you install Oracle10g from a CD, mount the CD by running the following commands in another terminal:
su – root
mount /mnt/cdrom run installer ./ runInstaller
Note: Message: - Can not connect to X11 windows server Step Log in as a Root user
Step 2 $xhost + Step 3 su – oracle
Step 4 Export Display=localhost:0.0 Step 5 ./runinstaller
Post Installation Steps:
Put following entries in bash file.
export ORACLE_HOME=$ORACLE_BASE/oracle/product/10.2.0/db_1 export PATH=$PATH:$ORACLE_HOME/bin
Oracle 10gR12 Software/binary installation (10gR2 Installation on Solaris)
Note: Bellow mention steps are for 10g Release 1 & 2 (32-bit/64-bit) on Solaris 10.
Note: When you install Solaris for Oracle you should create separate file system for ORACLE Software and Oracle Database
2.5 GB of disk space should be required by Oracle Software
1.3 GB of disk space should be required by General Purpose Database. Here we have created two mount point /oracle and /database for oracle software and database. Note: No specified operating system patches are required with Solaris 10 OS.
Per-requisite Steps:
Make sure that following software packages has been installed. SUNWarc SUNWbtool SUNWhea SUNWlibm SUNWlibms SUNWsprot SUNWtoo SUNWilof SUNWxwfnt SUNWilcs SUNWsprox SUNWil5cs
we can verify that packages are installed or not by using following command: $pkginfo -i. Check following executable file must be presents in /usr/ccs/bin
make ar il nm
Checks swap space
Swap space should be 512MB or Twice the size of RAM. Use following command to know about Physical Memory and Swap space:
$ /usr/sbin/prtconf grep size $ /usr/sbin/swap –l
Need at least 400 MB of free space in /tmp directory. Check kernel parameter
Set following kernel parameter in /etc/system file and reboot the server. Set shmsys:shminfo_shmmax=4294967295 Set shmsys:shminfo_shmmni=100 Set semsys:seminfo_semmsl=256 Set semsys:seminfo_semmni=100 Create a group. $ groupadd –g 300 dba * $ groupadd –g 301 oinstall **
* ―DBA‖ group will be use by the Oracle Software Owner and Database Administrators.
** ―OINSTALL‖ group will be use when you installing multiple copies of the oracle software on one server and you will want some logins to be able to log onto some databases with DBA privileges but not others.
Create User
Create a UNIX user that will be the Oracle Software Owner.
$ useradd –c ‗Oracle Software Owner‘ –d /oracle –g oinstall –G dba –m –u 300 –s /usr/bin/ksh oracle
$passwd oracle
Create Directory Structure
Create directory for oracle software and Database. $ mkdir /oracle/app /oracle/oradata
$chown oracle:dba /oracle $chmod 775 /oracle
Create the /var/opt/oracle directory $mkdir /var/opt/oracle
$chown oracle:dba /var/opt/oracle $chmod 755 /var/opt/oracle Edit profile file
edit .profile file and type following endearments export ORACLE_BASE=/oracle
export PATH=$PATH:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib Set X window enveiroment.
Log in as a root with CDE (Common Desktop Environment Session) $ DISPLAY=:0.0 $export DISPLAY $xhost + $ su – oracle $DISPLAY=:0.0 $export DISPLAY $/usr/openwin/bin/xclock Execute runInstaller
Log in as a ORACLE user and execute run installer. $./runInstaller
Database Creation (Manually) (For File System Storage Mechanism on Linux/Solaris)
Step 1 Create a initSID.ora (Example: initTEST.ora) file in $ORACLE_HOME/dbs/ directory. Example: $ORACLE_HOME/dbs/initTEST.ora
Put following entry in initTEST.ora file
background_dump_dest=<put BDUMP log destination> core_dump_dest=<put CDUMP log destination>
user_dump_dest=<put UDUMP log destination>
control_files = (/<Destination>/control1.ctl,/ <Destination>/control2.ctl,/ <Destination>/control3.ctl) undo_management = AUTO undo_tablespace = UNDOTBS1 db_name = TEST db_block_size = 8192 sga_max_size = 1073741824 sga_target = 1073741824 Step 2 Create a password file
$ORACLE_HOME/bin/orapwd file=$ORACLE_HOME/dbs/pwd<sid>.ora password=<password> entries=5
Step 3 Set your ORACLE_SID $ export ORACLE_SID=test
$ export ORACLE_HOME=/<Destination>
Step 4 Run the following sqlplus command to connect to the database and startup the instance. $sqlplus '/ as sysdba'
SQL> startup nomount
Step 5 Create the Database. Use following scripts create database test
logfile group 1 ('<Destination>/redo1.log') size 100M, group 2 ('<Destination>/redo2.log') size 100M,
group 3 ('<Destination>/redo3.log') size 100M character set WE8ISO8859P1
national character set utf8
datafile '<Destination>/system.dbf' size 500M autoextend on next 10M maxsize unlimited extent management local
sysaux datafile '<Destination>/sysaux.dbf' size 100M autoextend on next 10M maxsize unlimited undo tablespace undotbs1 datafile '<Destination>/undotbs1.dbf' size 100M
default temporary tablespace temp tempfile '<Destination>/temp01.dbf' size 100M; Step 6 Run the scripts necessary to build views, synonyms, etc.:
CATALOG.SQL-- creates the views of data dictionary tables and the dynamic performance views.
CATPROC.SQL-- establishes the usage of PL/SQL functionality and creates many of the PL/SQL Oracle supplied packages.
(For OMF Enable Database on Linux/Solaris)
Step 1 Create a initSID.ora(Example: initTEST.ora) file in $ORACLE_HOME/dbs/ directory. Example: $ORACLE_HOME/dbs/initTEST.ora
Put following entry in initTEST.ora file
background_dump_dest=<put BDUMP log destination> core_dump_dest=<put CDUMP log destination>
user_dump_dest=<put UDUMP log destination>
control_files = (/<Destination>/control1.ctl,/ <Destination>/control2.ctl,/ <Destination>/control3.ctl) undo_management = AUTO undo_tablespace = UNDOTBS1 db_name = test db_block_size = 8192 sga_max_size = 1073741824 sga_target = 1073741824
db_create_file_dest = /<Put DB File Destination> #OMF
db_create_online_log_dest_1 = /<Put first redo and control file destination> #OMF db_create_online_log_dest_2 = /<Put second redo and control file destination> #OMF db_recovery_file_dest = /<put flash recovery area destination> #OM
Step 2 Create a password file
$ORACLE_HOME/bin/orapwd file=$ORACLE_HOME/dbs/pwd<sid>.ora password=<password> entries=5
Step 3 Set your ORACLE_SID export ORACLE_SID=test
export ORACLE_HOME=/<oracle home path>
Step 4 Run the following sqlplus command to connect to the database and startup the instance. sqlplus '/ as sysdba'
SQL> startup nomount Step 5 Create the database create database test
character set WE8ISO8859P1 national character set utf8 undo tablespace undotbs1
default temporary tablespace temp; Step 6 Run catalog and catproc
@?/rdbms/admin/catalog.sql @?/rdbms/admin/catproc.sql
(Implement ASM Enable Database on LHEL 4)
Now we are ready to implement ASM Enable Database on RHEL. We divided all task in following Section:
Section A: Prepare/Install RHEL Machine Section B: Prepare ASM Disk.
Section C: Configure Oracle Automatic Storage Management * Section D: Create ASM Instance – Manually
Section E: Create ASM Enable Database
*Important: We can use two methods to configure Oracle Automatic Storage Management on Linux:
ASM With ASMLib I/O – Database created on raw block devices with this method.
ASM With Standard Linux I/O – Database created on raw character devices with this method. NOTE: Here we use ASM with ASMLib I/O method in our task and also discuss about ASM with Standard Linux I/O in Section C.
SECTION - A First we prepare RHEL4 machine. Step 1 Install the RHEL 4
Here, we create partition (/dev/sda2) for Oracle Software at the time of OS Installation. Step 2 Create the oracle user (log in as a ROOT and execute following)
# groupadd oinstall # groupadd dba
# useradd -d /oracle -g oinstall -G dba -s /bin/ksh oracle # chown oracle:dba /oracle
# passwd oracle New Password:
passwd: password successfully changed for oracle Step 3 Install oracleasmlib package.
oracleasmlib-2.0.2-1.i386.rpm, oracleasm-support-2.0.3-2 and oracleasm-2.6.9-42.0.0.0.1.ELsmp-2.0.3-2
EXAMPLE: # rpm -Uvh oracleasmlib-2.0.2-1.i386.rpm Step 4 Configure the kernel parameters.
Add below listed to /etc/sysctl.conf file. To make the changes effective immediately, execute /sbin/sysctl –p. # more /etc/sysctl.conf kernel.shmall = 2097152 kernel.shmmax = 2147483648 kernel.shmmni = 4096 kernel.sem = 250 32000 100 128 fs.file-max = 65536 net.ipv4.ip_local_port_range = 1024 65000 net.core.rmem_default = 1048576 net.core.rmem_max = 1048576 net.core.wmem_default = 262144 net.core.wmem_max = 262144
Step 5 Create the oracle user environment file. /oracle/.profile export EDITOR=vi export ORACLE_SID=CONPROD export ORACLE_BASE=/oracle export ORACLE_HOME=$ORACLE_BASE/10.2.0 export LD_LIBRARY_PATH=$ORACLE_HOME/lib export PATH=$ORACLE_HOME/bin:/bin:/bin: /usr/bin:/usr/sbin:/usr/local/bin:/usr/X11R6/bin
SECTION-B Prepare ASM Disk.
We will create 2 physical partitions on /dev/sdb and /dev/sdc. We must create Single Partition on whole Device. Reason is we have one controller per disk in this case so as faster IO and redundancy.
Step 1 Down the Machine and Attached Hard Disk with Machine and Start the machine
Step 2 Create Disk Partition for Oracle ASM storage (Prepare a set of raw disks for Oracle ASM (/dev/sdb, /dev/sdc)
You will be seeing here new added disk. Step 4 Follow following Screen Shot
SECTION - C Configure Oracle Automatic Storage Management (ASM) Follow bellow steps, If you want ASM With ASMLib I/O
Step 1 Configure ASMLib. Execute following command: # /etc/init.d/oracleasm configure
Configuring the Oracle ASM library driver.
This will configure the on-boot properties of the Oracle ASM library driver. The following questions will determine whether the driver is loaded on boot and what permissions it will have. The current values will be shown in brackets ('[]'). Hitting without typing an
answer will keep that current value. Ctrl-C will abort. Default user to own the driver interface []: oracle
Default group to own the driver interface []: dba
Start Oracle ASM library driver on boot (y/n) [n]: y Fix permissions of Oracle ASM disks on boot (y/n) [y]: y Writing Oracle ASM library driver configuration: [ OK ] Loading module "oracleasm": [ OK ]
Mounting ASMlib driver filesystem: [ OK ] Scanning system for ASM disks: [ OK ]
Step 2 Create ASM disks. Log ia as a root user and execute: # /etc/init.d/oracleasm createdisk VOL1 /dev/sdb1
# /etc/init.d/oracleasm createdisk VOL2 /dev/sdc1 Marking disk "/dev/sdc1" as an ASM disk: [ OK ] Verify that the ASM disks are visible from every node. # /etc/init.d/oracleasm scandisks
Scanning system for ASM disks: [ OK ] # /etc/init.d/oracleasm listdisks
VOL1 VOL2
Follow bellow steps, if you want ASM with Standard Linux I/O Step 1 Map raw devices for ASM disks.
A raw device mapping is required only if you are planning on creating ASM disks using standard Linux I/O. The raw devices have to bind with the block devices each time a node boots.
Add the following lines in /etc/sysconfig/rawdevices. /etc/sysconfig/rawdevices
/dev/raw/raw1 /dev/sdb1 /dev/raw/raw2 /dev/sdc1
To make the mapping effective immediately, execute the following commands as the root user: # /sbin/service rawdevices restart
Assigning devices:
/dev/raw/raw1 --> /dev/sdb1
/dev/raw/raw1: bound to major 8, minor 33 /dev/raw/raw2 --> /dev/sdc1
/dev/raw/raw2: bound to major 8, minor 49 done
# chown oracle:dba /dev/raw/raw[1-2] # chmod 660 /dev/raw/raw[1-2] # ls -lat /dev/raw/raw*
crw-rw---- 1 oracle dba 162, 2 Nov 4 07:04 /dev/raw/raw2 crw-rw---- 1 oracle dba 162, 1 Nov 4 07:04 /dev/raw/raw1 Modify /etc/udev/permissions.d/50-udev.permissions.
Raw devices are remapped on boot. The ownership of the raw devices will change to the root user by default upon boot. ASM will have problem accessing the shared partitions if the ownership is not the oracle user. Comment the original line, ―raw/*:root:disk:0660‖ in /etc/udev/permissions.d/50-udev.permissions and add a new line,
―raw/*:oracle:dba:0660.‖
# raw devices ram*:root:disk:0660 #raw/*:root:disk:0660 raw/*:oracle:dba:0660
SECTION-D Create ASM Instance (Manually) Step 1 If CSS service is not there; create it by executing the following batch file: $<ORACLE_HOME/bin/localconfig add
Important: Cluster Synchronization Services (CSS) is required to enable synchronization
between an Automatic Storage Management (ASM) instance and the database instances. Step 2 Create Admin Directories
$mkdir /oracle/ASM/bdump $mkdir /oracle/ASM/cdump $mkdir /oracle/ASM/udump $mkdir /oracle/ASM/pfile
Step 3 Create ASM Instance Parameter file. In /oracle/ASM/pfile directory INSTANCE_TYPE=ASM DB_UNIQUE_NAME=+ASM LARGE_POOL_SIZE=16M BACKGROUND_DUMP_DEST = '/oracle/ASM/bdump' USER_DUMP_DEST=‘/oracle/ASM/udump‘ CORE_DUMP_DEST = '/oracle/ASM/cdump' ASM_DISKGROUPS='DB_DATA' ASM_DISKSTRING ='ORCL: *‘ Important:
If you do not want to use above parameter just set only one parameter INSTANCE_TYPE=ASM. ASM instance will start with default values for other parameters.
Bellow I am giving you 5 key parameter that you must configure for ASM instance. INSTANCE_TYPE
DB_UNIQUE_NAME
ASM_POWER_LIMIT (Indicate the maximum speed to be used by this ASM instance during a disk rebalancing operation. Default is 1 and the range 1 to 11)
ASM_DISKSTRING (set the disk location for oracle to consider during a disk-discovery process)
ASM_DISKGROUP (specify the name of any disk group that you want the ASM instance to automatically mount at instance startup)
ASM instance uses the LARGE_POOL_SIZE memory buffer. We should allocate at least 8MB .
Step 4 Creating ASM Instance $export ORACLE_SID=+ASM $sqlplus /nolog
SQL> startup nomount pfile='/oracle/ASm/pfile/init+ASM.ora'
IMPORTANT: We can find error "ORA-29701: unable to connect to Cluster Manager" at the time of ASM instance startup. The Cause of error is Connect to CM failed or timed out. Just delete the CM local configuration and add local configuration by using. /localconfig delete and. /localconfig add command. This shall script is reside in $ORACLE_HOME/bin directory.
Step 5 Create ASM Disk Group
Let's start by determining if Oracle can find these four new disks: The view V$ASM_DISK can be queried from the ASM instance to determine which disks are being used or may potentially be used as ASM disks.
$export oracle_sid=+ASM $sqlplus "/ as sysdba"
SQL> SELECT group_number, disk_number, mount_status, header_status, state, path FROM v$asm_disk
Note:
The value of zero in the GROUP_NUMBER column for all four disks. This indicates that a disk is available but hasn't yet been assigned to a disk group.
Using SQL*Plus, the following will create a disk group with normal redundancy and two failure groups:
SQL> CREATE DISKGROUP DB_DATA NORMAL REDUNDANCY FAILGROUP controller1 DISK 'ORCL:VOL1'
FAILGROUP controller2 DISK 'ORCL:VOL2'; Diskgroup created.
Step 6 ALTER DISKGROUP ALL MOUNT;
SECTION - E Create ASM Enable Database Step 1 Set your ORACLE_SID
export ORACLE_SID=INDIAN
Step 2 Create a minimal init.ora ( In Default Location -$ORACLE_HOME/dbs/init<sid>.ora) control_files = +DB_DATA undo_management = AUTO db_name = test db_block_size = 8192 sga_max_size = 1073741824 sga_target = 1073741824 db_create_file_dest = +DB_DATA db_create_online_log_dest_1 = +DB_DATA
Step 3 Create a password file
$ORACLE_HOME/bin/orapwd file=$ORACLE_HOME/dbs/pwd<sid>.ora password=oracle entries=5
Step 4 Start the instance
sqlplus / as sysdba startup nomount
Step 5 Create the database
create database indian
character set WE8ISO8859P1 national character set utf8 undo tablespace undotbs1
default temporary tablespace temp;
Step 6 Run catalog and catproc @?/rdbms/admin/catalog.sql @?/rdbms/admin/catproc.sql
(Implement ASM Enable Database on Solaris)
Now we are ready to implement ASM Instance on Solaris. We divided all task in following Section:
Section A: Install Oracle binaries
Section B: Prepare Disk for ASM (Read Adding Disk/ Create Partition in Solaris section) Section C: Set ownership of Disk
Section D: Create ASM Instance – Manually Section E: Create ASM Enable Database
SECTION-A Install Oracle binaries Go to page 20.
SECTION - B Prepare Disk for ASM Take help of System Administrator
SECTION - C Set ownership of Disk
Default owner is root:sys needs to be changed to oracle:dba .We need to set oracle:dba ( here oracle is a Oracle owner and dba is a group) ownership of the disk. As root we have take partition s0 of disk (c0d1s0 and c1d1s0)
Check ownership of Disk. Execute following command as a root user. bash-3.00# ls -lhL /dev/rdsk/c0d1s0
crw-r--- 1 root sys 118, 64 Feb 16 02:10 /dev/rdsk/c0d1s0 bash-3.00# ls -lhL /dev/rdsk/c1d1s0
crw-r--- 1 root sys 118, 64 Feb 16 02:10 /dev/rdsk/c1d1s0 bash-3.00# chown oracle:dba /dev/rdsk/c0d1s0
bash-3.00# chown oracle:dba /dev/rdsk/c1d1s0
SECTION - D Create ASM Instance (Manually) Step 1 If CSS service is not there; create it by executing the following batch file: $<ORACLE_HOME/bin/localconfig add
Important: Cluster Synchronization Services (CSS) is required to enable synchronization
between an Automatic Storage Management (ASM) instance and the database instances. Step 2 Create Admin Directories
$mkdir /oracle/ASM/bdump $mkdir /oracle/ASM/cdump $mkdir /oracle/ASM/udump
$mkdir /oracle/ASM/pfile
Step 3 Create ASM Instance Parameter file. In /oracle/ASM/pfile directory INSTANCE_TYPE=ASM DB_UNIQUE_NAME=+ASM LARGE_POOL_SIZE=16M BACKGROUND_DUMP_DEST = '/oracle/ASM/bdump' USER_DUMP_DEST=‘/oracle/ASM/udump‘ CORE_DUMP_DEST = '/oracle/ASM/cdump' ASM_DISKGROUPS='DB_DATA' ASM_DISKSTRING =‘/dev/rdsk/*‘ Important:
If you do not want to use above parameter just set only one parameter INSTANCE_TYPE=ASM. ASM instance will start with default values for other parameters.
Bellow I am giving you 5 key parameter that you must configure for ASM instance. INSTANCE_TYPE
DB_UNIQUE_NAME
ASM_POWER_LIMIT (Indicate the maximum speed to be used by this ASM instance during a disk rebalancing operation. Default is 1 and the range 1 to 11)
ASM_DISKSTRING (set the disk location for oracle to consider during a disk-discovery process)
ASM_DISKGROUP (specify the name of any disk group that you want the ASM instance to automatically mount at instance startup)
ASM instance uses the LARGE_POOL_SIZE memory buffer. We should allocate at least 8MB .
Step 4 Creating ASM Instance $export ORACLE_SID=+ASM $sqlplus /nolog
SQL> connect / as sysdba
SQL> startup nomount pfile='/oracle/ASm/pfile/init+ASM.ora'
IMPORTANT: We can find error "ORA-29701: unable to connect to Cluster Manager" at the time of ASM instance startup. The Cause of error is Connect to CM failed or timed out. Just delete the CM local configuration and add local configuration by using. /localconfig delete and. /localconfig add command. This shall script is reside in $ORACLE_HOME/bin directory.
Let's start by determining if Oracle can find these four new disks: The view V$ASM_DISK can be queried from the ASM instance to determine which disks are being used or may potentially be used as ASM disks.
$export oracle_sid=+ASM $sqlplus "/ as sysdba"
SQL> SELECT group_number, disk_number, mount_status, header_status, state, path FROM v$asm_disk
Note:
The value of zero in the GROUP_NUMBER column for all four disks. This indicates that a disk is available but hasn't yet been assigned to a disk group.
Using SQL*Plus, the following will create a disk group with normal redundancy and two failure groups:
SQL> CREATE DISKGROUP DB_DATA NORMAL REDUNDANCY FAILGROUP controller1 DISK ' c0d1s0'
FAILGROUP controller2 DISK ' c1d1s0'; Diskgroup created.
Step 6 ALTER DISKGROUP ALL MOUNT;
Now your ASM Instance has been created. Restart the ASM Instance. SECTION - E Create (OMF) Database Step 1 Set your ORACLE_SID
export ORACLE_SID=INDIAN
Step 2 Create a minimal init.ora ( In Default Location -$ORACLE_HOME/dbs/init<sid>.ora) control_files = +DB_DATA undo_management = AUTO db_name = test db_block_size = 8192 sga_max_size = 1073741824 sga_target = 1073741824 db_create_file_dest = +DB_DATA db_create_online_log_dest_1 = +DB_DATA
Step 3 Create a password file
$ORACLE_HOME/bin/orapwd file=$ORACLE_HOME/dbs/pwd<sid>.ora password=oracle entries=5
Step 4 Start the instance
sqlplus / as sysdba startup nomount
Step 5 Create the database
create database indian
character set WE8ISO8859P1 national character set utf8 undo tablespace undotbs1
default temporary tablespace temp;
Step 6 Run catalog and catproc @?/rdbms/admin/catalog.sql @?/rdbms/admin/catproc.sql
(Implement Database on Linux RAW Device) Now we will implement database on Linux RAW Device.
How we will achieve this:
We will use the entire /dev/sdb hard drive for the Oracle database.
Next, we will create an LVM Physical Volume called /dev/pv1 for the entire /dev/sdb hard drive.
Next, we will create an LVM Volume Group.
After creating the LVM Volume Group, then we will create an LVM Logical Volume for each datafile, controlfile, and online redo log file for our new database.
Next , we bind the RAW devices to LVM Logical Volume Step 1 Removing Any Partitions on Hard Drive
# dd if=/dev/zero of=/dev/sdb bs=1K count=1 1+0 records in
1+0 records out
# blockdev --rereadpt /dev/sdb
At this point, you should have a hard drive recognized as /dev/sdb with no partitions Step 2 Create the LVM Physical Volume
We will create an LVM Physical Volume called /dev/pv1 for the entire hard disk /dev/sdb. pvcreate /dev/sdb
pvcreate -- physical volume "/dev/sdb" successfully created Step 3 Create the LVM Volume Group
We will create an LVM Volume Group that will contain the LVM Physical Volume /dev/pv1. # vgcreate -l 256 -p 256 -s 128k /dev/vg1 /dev/sdb
vgcreate -- volume group "vg1" successfully created and activated Step 4 Create Logical Volumes for database
Now we create all of the Logical Volumes that will be used to house all of the Oracle database files.
# lvcreate –L 15m /dev/vg1 #Control File 1 lvcreate -- logical volume "lvol0" successfully created
# lvcreate –L 15m /dev/vg1 #Control File 2 lvcreate -- logical volume "lvol1" successfully created
# lvcreate –L 15m /dev/vg1 #Control file 3 lvcreate -- logical volume "lvol2" successfully created
# lvcreate –L 20m /dev/vg1 # Redo group 1 lvcreate -- logical volume "lvol3" successfully created
# lvcreate –L 20m /dev/vg1 # redo group 2 lvcreate -- logical volume "lvol4" successfully created
# lvcreate –L 20m /dev/vg1 #Redo group 3 lvcreate -- logical volume "lvol5" successfully created
# lvcreate –L 500m /dev/vg1 # System tablesapce lvcreate -- logical volume "lvol6" successfully created
# lvcreate –L 500m /dev/vg1 #SysAux Tablesapce lvcreate -- logical volume "lvol7" successfully created
# lvcreate –L 1024m /dev/vg1 #UNDO lvcreate -- logical volume "lvol8" successfully created
# lvcreate –L 1024m /dev/vg1 #TEMP lvcreate -- logical volume "lvol9" successfully created Step 5 Create RAW Bindings
# vgchange -a y /dev/vg1
vgchange -- volume group "vg1" successfully activated # /usr/bin/raw /dev/raw/raw1 /dev/vg1/lvol0 # /usr/bin/raw /dev/raw/raw2 /dev/vg1/lvol1 # /usr/bin/raw /dev/raw/raw3 /dev/vg1/lvol2 # /usr/bin/raw /dev/raw/raw4 /dev/vg1/lvol3 # /usr/bin/raw /dev/raw/raw5 /dev/vg1/lvol4 # /usr/bin/raw /dev/raw/raw6 /dev/vg1/lvol5 # /usr/bin/raw /dev/raw/raw7 /dev/vg1/lvol6 # /usr/bin/raw /dev/raw/raw8 /dev/vg1/lvol7 # /usr/bin/raw /dev/raw/raw9 /dev/vg1/lvol8
# /usr/bin/raw /dev/raw/raw10 /dev/vg1/lvol9
Step 6 modify ownership on RAW Devices # /bin/chmod 600 /dev/raw/raw1 # /bin/chmod 600 /dev/raw/raw2 # /bin/chmod 600 /dev/raw/raw3 # /bin/chmod 600 /dev/raw/raw4 # /bin/chmod 600 /dev/raw/raw5 # /bin/chmod 600 /dev/raw/raw6 # /bin/chmod 600 /dev/raw/raw7 # /bin/chmod 600 /dev/raw/raw8 # /bin/chmod 600 /dev/raw/raw9 # /bin/chmod 600 /dev/raw/raw10 # /bin/chown oracle:dba /dev/raw/raw1 # /bin/chown oracle:dba /dev/raw/raw2 # /bin/chown oracle:dba /dev/raw/raw3 # /bin/chown oracle:dba /dev/raw/raw4 # /bin/chown oracle:dba /dev/raw/raw5 # /bin/chown oracle:dba /dev/raw/raw6 # /bin/chown oracle:dba /dev/raw/raw7 # /bin/chown oracle:dba /dev/raw/raw8 # /bin/chown oracle:dba /dev/raw/raw9 # /bin/chown oracle:dba /dev/raw/raw10 Step 7 creates Database.
Now we are ready to create RAW Enable database.
7.1 Create initSID.ora file and put in default location. Put control_file parameter as RAW. 7.2 Set bellow mention ENV.
Export ORACLE_HOME=<Oracle_Home Destination> Export ORCALE_SID=PROD
Export PATH=$PATH:$ORACLE_HOME/bin
Export LD_LIBRARY_PATH=$ORACLE_HOME/lib
7.2 Invoke SQLPLUS, startup nomount database and create database. $sqlplus /nolog
SQL> conn / as sysdba SQL>startup nomount SQL> Create database prod logfile group 1 (‗/dev/raw/raw4‘), group 2 (‗/dev/raw/raw5‘),
group 3 (‗/dev/raw/raw6‘) datafile ‗/dev/raw/raw7‘
undo Tablespace undotbs1 datafile ‗/dev/raw/raw9‘
default temporary Tablespace temp tempfile ‗/dev/raw/raw10‘; 7.3 Run catalog and catproc
@?/rdbms/admin/catalog.sql @?/rdbms/admin/catproc.sql
(Implement Database on Solaris RAW Device) Now we will implement database on Solaris RAW Device.
How we will achieve this:
We will use the entire c0d1 hard drive for the Oracle database. Next, we will create an RAW Partitions
We will create bellow mention raw partition:
Disk Slice Size Use for
c0d1 s0 50MB Control file c0d1 s1 10MB Redo group1 c0d1 s3 10MB Redo group 2 c0d1 s4 500MB System c0d1 s5 500MB Sysaux c0d1 s7 1024MB Undo c1d1 s0 1024MB Temp
Step 1 Change owner ship:
Default owner is root:sys needs to be changed to oracle:dba .We need to set oracle:dba ( here oracle is a Oracle owner and dba is a group) ownership of the disk. As root we have take partition s0 of disk (c0d1s0 and c1d1s0)
Check ownership of Disk. Execute following command as a root user. bash-3.00# ls -lhL /dev/rdsk/c0d1s0
crw-r--- 1 root sys 118, 64 Feb 16 02:10 /dev/rdsk/c0d1s0 bash-3.00# ls -lhL /dev/rdsk/c1d1s0
crw-r--- 1 root sys 118, 64 Feb 16 02:10 /dev/rdsk/c1d1s0 bash-3.00# chown oracle:dba /dev/rdsk/c0d1s0
bash-3.00# chown oracle:dba /dev/rdsk/c1d1s0 Note: Execute one by one for all slices
Step 2 creates Database.
Now we are ready to create RAW Enable database.
2.1 Create initSID.ora file and put in default location. Put control_file parameter as RAW. 2.2 Set bellow mention ENV.
Export ORACLE_HOME=<Oracle_Home Destination> Export ORCALE_SID=PROD
Export PATH=$PATH:$ORACLE_HOME/bin
Export LD_LIBRARY_PATH=$ORACLE_HOME/lib
2.3 Invoke SQLPLUS, startup nomount database and create database. $sqlplus /nolog
SQL> conn / as sysdba SQL>startup nomount
SQL> Create database prod logfile group 1 ‗/dev/rdsk/raw1‘, group 2 ‗/dev/rdsk/raw3‘
datafile ‗/dev/rdsk/raw4‘
sysaux datafile ‗/dev/rdsk/raw5‘; 2.4 Create Undo Tablespace
CREATE UNDO TABLESPACE UNDOTBS
DATAFILE '/dev/rdsk/c0d1s7' REUSE AUTOEXTEND ON; 2.4 Create Temp Tablespace
CREATE TEMPORARY TABLESPACE temp TEMPFILE '/dev/rdsk/c1d1s0'
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp; 2.5 Run catalog and catproc
@?/rdbms/admin/catalog.sql @?/rdbms/admin/catproc.sql
(Implement ASM Enable Database on Windows) Step 1 Create New Partation for Device Files
G:\ H:\
Step 2 If CSS service is not there; create it by executing the following batch file: <orahome>\bin\localconfig add
Step 3 Building the ASM Candidate "disks". ASMTOOL -create G:\DISK1 1024
ASMTOOL -create H:\DISK2 1024 ASMTOOL –create G:\DISK3 1024 ASMTOOL –create H:\DISK4 1024 Step 4 Create Admin Directories D:\DATABASE\admin\+ASM\bdump D:\DATABASE\admin\+ASM\cdump D:\DATABASE\admin\+ASM\hdump D:\DATABASE\admin\+ASM\pfile D:\DATABASE\admin\+ASM\udump
Step 5 Create ASM Instance Parameter File INSTANCE_TYPE=ASM _ASM_ALLOW_ONLY_RAW_DISKS = FALSE DB_UNIQUE_NAME = +ASM ASM_DISKSTRING ='G:\DISK*','H:\DISK*' LARGE_POOL_SIZE = 16M BACKGROUND_DUMP_DEST = 'D:\DATABASE\admin\+ASM\bdump' USER_DUMP_DEST = 'D:\DATABASE\admin\+ASM\udump' CORE_DUMP_DEST = 'D:\DATABASE\admin\+ASM\cdump' ASM_DISKGROUPS='DB_DATA'
Step 6 Creating ASM Instance
C:\> oradim -new -asmsid +ASM -syspwd asm123 –pfile d:\database\admin\+ASM\pfile\init.ora -startmode a
Step 7 Starting the ASM Instance
SQL> startup nomount pfile=' d:\database\admin\+ASM\pfile\init.ora'; ASM instance started
Total System Global Area 125829120 bytes Fixed Size 769268 bytes
Variable Size 125059852 bytes Database Buffers 0 bytes Redo Buffers 0 bytes Step 8 Create ASM Disk Groups
Let's start by determining if Oracle can find these four new disks:
The view V$ASM_DISK can be queried from the ASM instance to determine which disks are being used or may potentially be used as ASM disks.
set oracle_sid=+ASM C:>sqlplus "/ as sysdba"
SQL> SELECT group_number, disk_number, mount_status, header_status, state, path FROM v$asm_disk
GROUP_NUMBER DISK_NUMBER MOUNT_S HEADER_STATU STATE PATH --- --- --- --- ---
---
0 0 CLOSED CANDIDATE NORMAL C:\ASMDISKS\_FILE_DISK1 0 1 CLOSED CANDIDATE NORMAL C:\ASMDISKS\_FILE_DISK2 0 2 CLOSED CANDIDATE NORMAL C:\ASMDISKS\_FILE_DISK3 0 3 CLOSED CANDIDATE NORMAL C:\ASMDISKS\_FILE_DISK4 Note:
is available but hasn't yet been assigned to a disk group. Using SQL*Plus, the following will create a disk group with normal redundancy and two failure groups:
set oracle_sid=+ASM sqlplus "/ as sysdba"
SQL> CREATE DISKGROUP DB_DATA NORMAL REDUNDANCY FAILGROUP controller1
DISK 'G:\DISK1', 'H:\DISK2'
FAILGROUP controller2 DISK 'G:\DISK3', 'H:\DISK4'; Diskgroup created.
Step 9 ALTER DISKGROUP ALL MOUNT;
Now your ASM Instance has been created. Restart the ASM Instance.
Step 10 Create a initSID.ora(Example: initTEST.ora) file in $ORACLE_HOME/database/ directory.
Example: $ORACLE_HOME/dbs/initTEST.ora Put following entry in initTEST.ora file
background_dump_dest=<put BDUMP log destination> core_dump_dest=<put CDUMP log destination>
user_dump_dest=<put UDUMP log destination> control_files = +DB_DATA undo_management = AUTO undo_tablespace = UNDOTBS1 db_name = test db_block_size = 8192 sga_max_size = 1073741824 sga_target = 1073741824
db_create_file_dest = +DB_DATA #OMF
db_create_online_log_dest_1 = +DB_DATA #OMF db_create_online_log_dest_2 = +DB_DATA #OMF #db_recovery_file_dest = +DB_DATA
Step 11 Creating Database TEST Instance
C :\> oradim -new -sid TEST -syspwd test123 -startmode a Step 12 Starting the Database Instance
SQL> startup nomount Step 13 Create the database create database prod
character set WE8ISO8859P1 national character set utf8 undo tablespace undotbs1
default temporary tablespace temp; Step 14 Run catalog and catproc @?/rdbms/admin/catalog.sql @?/rdbms/admin/catproc.sql
Managing Tablespace How to Manage Tablespace
Tablespace is a logical storage unit. Why we are say logical because a tablespace is not visible in the file system. Oracle store data physically is datafiles. A tablespace consist of one or more datafile.
Type of Tablespace System Tablespace
Created with the database Required in all database Contain the data dictionary Non System Tablespace:
Separate undo, temporary, application data and application index segments Control the amount of space allocation to the user‘s objects
Enable more flexibility in database administration How to Create Tablespace?
CREATE TABLESPACE "tablespace name"
DATAFILE <DATAFILE LOCATION> SIZE <Size of Datafile> REUSE
MENIMUM EXTENT (This ensure that every used extent size in the tablespace is a multiple of the integer)
BLOCKSIZE
LOGGING | NOLOGGING (Logging: By default tablespace have all changes written to redo, Nologging: tablespace do not have all changes written to redo)
ONLIN | OFFLINE (OFFLINE: tablespace unavailable immediately after creation)
PERMANENT | TEMPORARY (Permanent: tablespace can used to hold permanent object, temporary: tablespace can used to hold temp object)
EXTENT MANAGEMENT clause Example:
CREATE TABLESPACE "USER1"
DATAFILE 'C: \LOCAL\ORADATA\USER_DATA.DBF' SIZE 10m REUSE BLOCKSIZE 8192
LOGGING ONLINE PERMANENT
EXTENT MANAGEMENT LOCAL
How to manage space in Tablespace? Note: Tablespace allocate space in extent.
The extents are managed with in the tablespace via bitmaps. In locally managed tablespace, all tablespace information store in datafile header and don‘t use data dictionary table for store information. Advantage of locally managed tablespace is that no DML generate and reduce contention on data dictionary tables and no undo generated when space allocation or deallocation occurs.
Extent Management [Local | Dictionary]
The storage parameters NEXT, PCTINCREASE, MINEXTENTS, MAXEXTENTS, and DEFAULT STORAGE are not valid for segments stored in locally managed tablespaces.
To create a locally managed tablespace, you specify LOCAL in the extent management clause of the CREATE TABLESPACE statement. You then have two options. You can have Oracle manage extents for you automatically with the AUTOALLOCATE option, or you can specify that the tablespace is managed with uniform extents of a specific size (UNIFORM SIZE).
If the tablespace is expected to contain objects of varying sizes requiring different extent sizes and having many extents, then AUTOALLOCATE is the best choice.
If you do not specify either AUTOALLOCATE or UNIFORM with the LOCAL parameter, then AUTOALLOCATE is the default.
Dictionary Managed tablespace
When we declaring a tablespace as a Dictionary Managed, the data dictionary manages the extents. The Oracle server updates the appropriate tables (sys.fet$ and sys.uet$) in the data dictionary whenever an extent is allocated or deallocated.
How to Create a Locally Managed Tablespace?
The following statement creates a locally managed tablespace named USERS, where AUTOALLOCATE causes Oracle to automatically manage extent size.
CREATE TABLESPACE users
DATAFILE 'C:\LOCAL\ORADATA\USER_DATA.DBF' SIZE 50M EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
Alternatively, this tablespace could be created specifying the UNIFORM clause. In this example, a 512K extent size is specified. Each 512K extent (which is equivalent to 64 Oracle blocks of 8K) is represented by a bit in the bitmap for this file.
CREATE TABLESPACE users
DATAFILE 'C:\LOCAL\ORADATA\USER_DATA.DBF' SIZE 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K;
How to Create a Dictionary Managed Tablespace?
The following is an example of creating a DICTIONARY managed tablespace in Oracle9i: CREATE TABLESPACE users
DATAFILE 'C:\LOCAL\ORADATA\USER_DATA.DBF' SIZE 50M EXTENT MANAGEMENT DICTIONARY
DEFAULT STORAGE ( INITIAL 64K NEXT 64K MINEXTENTS 2 MAXEXTENTS 121 PCTINCREASE 0);
What is Segment Space Management Options?
Two choices for segment-space management, one is manual (the default) and another auto. Manual: This is default option. This option use free lists for managing free space within segments. What are free lists: Free lists are lists of data blocks that have space available for inserting new rows.
Auto: This option use bitmaps for managing free space within segments. This is typically called automatic segment-space management
Example:
CREATE TABLESPACE users
DATAFILE 'C:\LOCAL\ORADATA\USER_DATA.DBF' SIZE 10M REUSE EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K
SEGMENT SPACE MANAGEMENT AUTO PERMANENT
ONLINE;
How to Convert between LMT and DMT Tablespace?
The DBMS_SPACE_ADMIN package allows DBAs to quickly and easily convert between LMT and DMT mode. Look at these examples:
SQL> exec dbms_space_admin.Tablespace_Migrate_TO_Local('ts1'); PL/SQL procedure successfully completed.
SQL> exec dbms_space_admin.Tablespace_Migrate_FROM_Local('ts2'); PL/SQL procedure successfully completed.
Important Query How to retrieve tablespace default storage Parameters? SELECT TABLESPACE_NAME "TABLESPACE", INITIAL_EXTENT "INITIAL_EXT",
NEXT_EXTENT "NEXT_EXT", MIN_EXTENTS "MIN_EXT", MAX_EXTENTS "MAX_EXT",
PCT_INCREASE
FROM DBA_TABLESPACES;
How to retrieve information tablesapce and associated datafile? SELECT FILE_NAME, BLOCKS, TABLESPACE_NAME
FROM DBA_DATA_FILES;
How to retrieve Statistics for Free Space (Extents) of Each Tablespace? SELECT TABLESPACE_NAME "TABLESPACE", FILE_ID,
COUNT(*) "PIECES", MAX(blocks) "MAXIMUM", MIN(blocks) "MINIMUM", AVG(blocks) "AVERAGE", SUM(blocks) "TOTAL" FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME, FILE_ID;
PIECES shows the number of free space extents in the tablespace file, MAXIMUM and MINIMUM show the largest and smallest contiguous area of space in database blocks, AVERAGE shows the average size in blocks of a free space extent, and TOTAL shows the amount of free space in each tablespace file in blocks. This query is useful when you are going to create a new object or you know that a segment is about to extend, and you want to make sure that there is enough space in the containing tablespace.
