Wednesday, September 14, 2016
Oracle Exalogic Elastic Cloud is the world’s first and only integrated middleware machine—a combined hardware and software offering designed to revolutionize data center consolidation. Enterprises can bring together tens, hundreds, or even thousands of disparate, mission-critical, performance-sensitive workloads on Oracle Exalogic with maximum reliability, availability, and security. Oracle Exalogic’s unique high-bandwidth, low-latency interconnect fabric means that complex, distributed applications can run with a responsiveness simply not achievable with typical servers used in data centers today.
Oracle Exadata Database Machine provides an optimal solution for all database workloads, ranging from scan-intensive data warehouse applications to highly concurrent OLTP applications. With its combination of smart Oracle Exadata Storage Server Software, complete and intelligent Oracle Database software, and the latest industry standard hardware components from Sun, the Database Machine delivers extreme performance in a highly-available, highly-secure environment. With Oracle’s unique clustering and workload management capabilities, the Database Machine is also well-suited for consolidating multiple databases onto a single grid. Delivered as a complete pre-optimized and pre-configured package of software, servers, and storage, the Sun Oracle Database Machine is simple and fast to implement and ready to tackle your large-scale business applications.
Oracle Exadata Database Machine provides an optimal solution for all database workloads, ranging from scan-intensive data warehouse applications to highly concurrent OLTP applications. With its combination of smart Oracle Exadata Storage Server Software, complete and intelligent Oracle Database software, and the latest industry standard hardware components from Sun, the Database Machine delivers extreme performance in a highly-available, highly-secure environment. With Oracle’s unique clustering and workload management capabilities, the Database Machine is also well-suited for consolidating multiple databases onto a single grid. Delivered as a complete pre-optimized and pre-configured package of software, servers, and storage, the Sun Oracle Database Machine is simple and fast to implement and ready to tackle your large-scale business applications.
Saturday, September 10, 2016
Recovery Manager (RMAN) is a utility that can manage your entire Oracle backup and recovery activities.
Backed files with RMAN
Database Files (with RMAN)
Control Files (with RMAN)
Offline Redolog Files (with RMAN)
INIT.ORA (manually)
Password Files (manually)
Architectural components of RMAN
1.RMAN executable
2.Server processes
3.Channels
4.Target database
5.Recovery catalog database (optional)
6.Media management layer (optional)
7.Backups, backup sets, and backup pieces
Channels :
A channel is an RMAN server process started when there is a need to communicate with an I/O device, such as a disk or a tape. A channel is what reads and writes RMAN backup files. It is through the allocation of channels that you govern I/O characteristics such as:
1.Type of I/O device being read or written to, either a disk or an sbt_tape
2.Number of processes simultaneously accessing an I/O device
3.Maximum size of files created on I/O devices
4.Maximum rate at which database files are read
5.Maximum number of files open at a time
Backup set :
A logical grouping of backup files -- the backup pieces -- that are created when you issue an RMAN backup command. A backup set is RMAN's name for a collection of files associated with a backup. A backup set is composed of one or more backup pieces.
Backup piece :
A physical binary file created by RMAN during a backup. Backup pieces are written to your backup medium, whether to disk or tape. They contain blocks from the target database's datafiles, archived redo log files, and control files.
Benefits of using RMAN
1. Incremental backups that only copy data blocks that have changed since the last backup.
2. Tablespaces are not put in backup mode, thus there is noextra redo log generation during online backups.
3. Detection of corrupt blocks during backups.
4. Parallelization of I/O operations.
5. Automatic logging of all backup and recovery operations.
6. Built-in reporting and listing commands.
Differential backup: This is the default type of incremental backup which backs up all blocks changed after the most recent backup at level n or lower.
Cumulative backup: Backup all blocks changed after the most recent backup at level n-1 or lower.
Restoring : It involves copying backup files from secondary storage (backup media) to disk. This can be done to replace damaged files or to copy/move a database to a new location.
Recovery : It is the process of applying redo logs to the database to roll it forward. One can roll-forward until a specific point-in-time (before the disaster occurred), or roll-forward until the last transaction recorded in the log files.
Hot backup : when the database is online
Cold backup : taken during shut down period
Backed files with RMAN
Database Files (with RMAN)
Control Files (with RMAN)
Offline Redolog Files (with RMAN)
INIT.ORA (manually)
Password Files (manually)
Architectural components of RMAN
1.RMAN executable
2.Server processes
3.Channels
4.Target database
5.Recovery catalog database (optional)
6.Media management layer (optional)
7.Backups, backup sets, and backup pieces
Channels :
A channel is an RMAN server process started when there is a need to communicate with an I/O device, such as a disk or a tape. A channel is what reads and writes RMAN backup files. It is through the allocation of channels that you govern I/O characteristics such as:
1.Type of I/O device being read or written to, either a disk or an sbt_tape
2.Number of processes simultaneously accessing an I/O device
3.Maximum size of files created on I/O devices
4.Maximum rate at which database files are read
5.Maximum number of files open at a time
Backup set :
A logical grouping of backup files -- the backup pieces -- that are created when you issue an RMAN backup command. A backup set is RMAN's name for a collection of files associated with a backup. A backup set is composed of one or more backup pieces.
Backup piece :
A physical binary file created by RMAN during a backup. Backup pieces are written to your backup medium, whether to disk or tape. They contain blocks from the target database's datafiles, archived redo log files, and control files.
Benefits of using RMAN
1. Incremental backups that only copy data blocks that have changed since the last backup.
2. Tablespaces are not put in backup mode, thus there is noextra redo log generation during online backups.
3. Detection of corrupt blocks during backups.
4. Parallelization of I/O operations.
5. Automatic logging of all backup and recovery operations.
6. Built-in reporting and listing commands.
Differential backup: This is the default type of incremental backup which backs up all blocks changed after the most recent backup at level n or lower.
Cumulative backup: Backup all blocks changed after the most recent backup at level n-1 or lower.
Restoring : It involves copying backup files from secondary storage (backup media) to disk. This can be done to replace damaged files or to copy/move a database to a new location.
Recovery : It is the process of applying redo logs to the database to roll it forward. One can roll-forward until a specific point-in-time (before the disaster occurred), or roll-forward until the last transaction recorded in the log files.
Hot backup : when the database is online
Cold backup : taken during shut down period
Create User :
SQL> Create user hameed identified by hameed default tablespace hyd temporary tablespace temp quota unlimited on hyd;
Unlock the user account :
SQL> alter user hameed account unlock;
Force user to change the Password :
SQL> alter user hameed password expire;
Granting privileges to users :
SQL> grant create session, create table, create sequence to hameed;
SQL> conn hameed/hameed;
SQL> select * from session_privs;
SQL> select * from role_sys_privs;
Creating and assigning the custom roles :
SQL> create role basicrole;
SQL> grant create session, create table, create sequence to basicrole;
SQL> grant basicrole to hameed;
SQL> select role,privilege from role_sys_privs where role=’basicrole’;
SQL>select * from dba_roles_privs where granted_role=’basicrole’
Revoking the privileges from user :
SQL> revoke insert,update on hameed.info from hameed;
Dropping the user Account :
SQL> drop user hameed;
SQL> drop user hameed cascade;
SQL> Create user hameed identified by hameed default tablespace hyd temporary tablespace temp quota unlimited on hyd;
Unlock the user account :
SQL> alter user hameed account unlock;
Force user to change the Password :
SQL> alter user hameed password expire;
Granting privileges to users :
SQL> grant create session, create table, create sequence to hameed;
SQL> conn hameed/hameed;
SQL> select * from session_privs;
SQL> select * from role_sys_privs;
Creating and assigning the custom roles :
SQL> create role basicrole;
SQL> grant create session, create table, create sequence to basicrole;
SQL> grant basicrole to hameed;
SQL> select role,privilege from role_sys_privs where role=’basicrole’;
SQL>select * from dba_roles_privs where granted_role=’basicrole’
Revoking the privileges from user :
SQL> revoke insert,update on hameed.info from hameed;
Dropping the user Account :
SQL> drop user hameed;
SQL> drop user hameed cascade;
Friday, September 9, 2016
Creating tablespace :
Sql> create tablespace hyd datafile '/u01/home/prod/hyd.dbf' size 10m autoextend on maxsize 100m default
storage (next 10m);
To check tablespace
information :
Sql> select tablespace_name
from dba_tablespaces;
Adding a datafile to a tablespace :
Sql> alter tablespace hyd add
datafile ‘/u02/demo/data/hyd02.dbf’
size 100m autoextend on;
Deleting a datafile from
a tablespace
Sql> alter tablespace hyd drop
datafile ‘/u02/demo/data/hyd02.dbf’;
Droping a tablespace
Sql> drop tablespace hyd; --> it will drop tablespace logically at database level.
Sql> drop tablespace hyd including contents and datafiles;
--> it will drop tablespace logically(database level) and physically(o/s level)
--> it will drop tablespace logically(database level) and physically(o/s level)
Reusing orphan
datafile
Sql> create tablespace hyd datafile ‘/u01/demo/data/hyd01.dbf’ reuse;
Resize a Datafile
Sql> alter database
datafile ‘/u01/demo/data/hyd.dbf’ resize 50m;
Making a tablespace as read only
Sql> alter tablespace hyd read
only;
Sql> select
tablespace_name,status from dba_tablespaces;
Sql> alter tablespace hyd read
write;
Making a tablespace
offline.
Sql> alter tablespace hyd offline; --> Users can not access this tablespace in this state.
Sql> alter tablespace hyd online;
Renaming of
tablespace
Sql> alter tablespace hyd rename
to hyd1;
Starting up :
SQL> STARTUP NOMOUNT
This state is used for creating new database or creating new control file. At this state, Oracle allocates SGA and starts background processes.
SQL> STARTUP MOUNT
This state is used for performing specific maintenance operations like renaming data files, enabling/disabling archive log mode, adding/dropping/renaming redo log files, recovering database etc. Control file is read at this stage but the data files are not open.
SQL> STARTUP OPEN or simply SQL>STARTUP
Database is available for normal operations.
Shutting down :
SQL> SHUTDOWN NORMAL or SQL> SHUTDOWN
Waits for all database users to disconnect then closes database.
SQL> SHUTDOWN IMMEDIATE
Terminates all user connections, rolls back uncommitted transactions, closes database.
SQL> SHUTDOWN TRANSACTIONAL
Waits for all transactions to commit or roll back, then closes database.
SQL> SHUTDOWN ABORT
Immediately closes database leaving it in inconsistent state. SMON automatically performs instance recovery during next startup.
SQL> STARTUP NOMOUNT
This state is used for creating new database or creating new control file. At this state, Oracle allocates SGA and starts background processes.
SQL> STARTUP MOUNT
This state is used for performing specific maintenance operations like renaming data files, enabling/disabling archive log mode, adding/dropping/renaming redo log files, recovering database etc. Control file is read at this stage but the data files are not open.
SQL> STARTUP OPEN or simply SQL>STARTUP
Database is available for normal operations.
Shutting down :
SQL> SHUTDOWN NORMAL or SQL> SHUTDOWN
Waits for all database users to disconnect then closes database.
SQL> SHUTDOWN IMMEDIATE
Terminates all user connections, rolls back uncommitted transactions, closes database.
SQL> SHUTDOWN TRANSACTIONAL
Waits for all transactions to commit or roll back, then closes database.
SQL> SHUTDOWN ABORT
Immediately closes database leaving it in inconsistent state. SMON automatically performs instance recovery during next startup.
Step 1: Create Necessary Directory Structure.
oradata , udump , bdump , cdump
Step 2 : Export Environment Variables
$ export ORACLE_SID=PROD
$ export ORACLE_HOME=/u01/home/app/product/11.2.0/dbhome_1
$ export PATH=$ORACLE_HOME/bin:$PATH:
Step 3 : Create Parameter file
Vi Initprod.ora (Parameter File)
db_name=prod --- defines database name
Instance_name=prod --- defines instance name
Control_files=’/u01/prod/control01.ctl’ --- name and location of controlfile
Db_block_size = 8192 --- to set db block size in bytes (8k)
Undo_management=’auto’ ---undo segment management values ‘manual’ or ‘auto’
Undo_tablespace=undotbs1 ---undo tablespace name
Undo_retention=900 --- undo database retention periold
Compatible=11.2.0 --- oracle software version
memory_max_target=400m --- to set upper limit of sga
memory_target=300m ---to set actual size of sga component
+ PGA Aggregate (except log buffers)
Log_buffer = 100 --- to set log buffer size
Workarea_size_policy = auto --- auto sizing of the pga within aggregate pga
diagnostic_dest=’/u01/prod/diag’ --- location of alert log and trace files
Step 4 : Export database & Startup in NOMOUNT STAGE
$ export ORACLE_SID=PROD
$ sqlplus / as sysdba
SQL> startup nomount pfile='/u01/app/oracle/admin/pfile/initprod.ora';
Step 5 : Create the Control File
Vi dbcreate_script.sql
CREATE DATABASE prod
USER SYS IDENTIFIED BY manager
USER SYSTEM IDENTIFIED BY manager
LOGFILE GROUP 1 ('/u01/hameed/prod/redo01.log') SIZE 50M,
GROUP 2 ('/u01/hameed/prod/redo02.log') SIZE 50M,
GROUP 3 ('/u01/hameed/prod/redo03.log’) SIZE 50M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 50
MAXDATAFILES 100
MAXINSTANCES 1
DATAFILE '/u01/hameed/prod/system01.dbf' SIZE 100M autoextend on
SYSAUX DATAFILE '/u01/hameed/prod/sysaux01.dbf' SIZE 100M autoextend on
DEFAULT TABLESPACE users datafile ‘/u01/hameed/prod/users01.dbf’ size 100m autoextend on
DEFAULT TEMPORARY TABLESPACE temp TEMPFILE /u01/hameed/prod/temp01.dbf' SIZE 50m
UNDO TABLESPACE undotbs1 DATAFILE '/u01/hameed/prod/undotbs01.dbf' SIZE 200M;
Step 6 : Run the control script at database level
SQL>@/u01/app/oracle/admin/scripts/dbcreate_script.sql
** Check the creation of Control , Redo , Data files.
Step 7 : Run this minimal scripts for creating database
@$ORACLE_HOME/rdbms/admin/catalog.sql --> script to create data Dictionary tables.
@$ORACLE_HOME/rdbms/admin/catproc.sql --> script to create procedure and packages
Connect system/manager
@$ORACLE_HOME/sqlplus/admin/pupbld.sql --> script to create users Profile
Step 8 :Shutdown the instance and startup the database. Your database is ready for use!
SQL>shutdown immediate;
SQL>startup;
INSTANCE : An Oracle instance consists of memory structures and Oracle background processes (DBWn, LGWR, CKPT, SMON, PMON .. etc )
SGA :
System Global Area (SGA) is shared memory area ( RAM ). All users of database share information maintained in this.
The SGA's size and function are controlled by initialization (INIT.ORA or SPFILE) parameters.
SGA size is limited by SGA_MAX_SIZE initialization parameter. From 11g, Oracle can manage SGA and PGA completely automatically.
PGA :
The PGA (Program or Process Global Area) is a memory area (RAM) that stores data and control information for a single process. For example, it typically contains a sort area, hash area, session cursor cache
Oracle Background Processes
SMON :
The system monitor process. The usage and function of this Oracle background process is twofold. First, in the event of an instance failure—when the memory structures and processes that comprise the Oracle instance cannot continue to run—the SMON process handles recovery from that instance failure. Second, the SMON process handles disk space management issues on the database by taking smaller fragments of space and "coalescing" them, or piecing them together.
PMON :
The process monitor process. PMON watches the user processes on the database to make sure that they work correctly. If for any reason a user process fails during its connection to Oracle, PMON will clean up the remnants of its activities and make sure that any changes it may have made to the system are "rolled back," or backed out of the database and reverted to their original form.
DBWR :
The database writer process. This background process handles all data block writes to disk. It works in conjunction with the Oracle database buffer cache memory structure. It prevents users from ever accessing a disk to perform a data change such as update, insert, or delete.
LGWR :
The log writer process. This background process handles the writing of redo log entries from the redo log buffer to online redo log files on disk. This process also writes the log sequence number of the current online redo log to the datafile headers and to the control file. Finally, LGWR handles initiating the process of clearing the dirty buffer write queue. At various times, depending on database configuration, those updated blocks are written to disk by DBWR. These events are called checkpoints. LGWR handles telling DBWR to write the changes.
RECO (optional) :
The recoverer process. In Oracle databases using the distributed option, this background process handles the resolution of distributed transactions against the database.
ARCH (optional) :
The archiver process. In Oracle databases that archive their online redo logs, the ARCH process handles automatically moving a copy of the online redo log to a log archive destination.
CKPT (optional) :
The checkpoint process. In high-activity databases, CKPT can be used to handle writing log sequence numbers to the datafile headers and control file, alleviating LGWR of that responsibility.
ORACLE DATABASE STRUCTURE :
Control file
A control file is a small binary file that is part of an Oracle database. The control file is used to keep track of the database's status and physical structure.
Every Oracle Database must have at least one control file. However, it is recommended to create more than one, up to a maximum of 8.
Redo log
A redo log is a file that is part of an Oracle database. When a transaction is committed, the transaction's details in the redo log buffer is written to a redo log file.
Data file
A data file is a file that is part of an Oracle database. Datafiles are used to store data - including user data and undo data. Data files are grouped together into tablespaces.
ORACLE DATABASE STRUCTURE :
Control file
A control file is a small binary file that is part of an Oracle database. The control file is used to keep track of the database's status and physical structure.
Every Oracle Database must have at least one control file. However, it is recommended to create more than one, up to a maximum of 8.
Redo log
A redo log is a file that is part of an Oracle database. When a transaction is committed, the transaction's details in the redo log buffer is written to a redo log file.
Data file
A data file is a file that is part of an Oracle database. Datafiles are used to store data - including user data and undo data. Data files are grouped together into tablespaces.
