Oracle Exadata
Oracle Exadata is a pre-configured combination of Software and Hardware to host Orracle databases.
It contains DB Servers, Cell Storage, Infiniband Switch, Cisco Switch, PDU(Power Distribution Unit)
Smart Scan, Smart Flash cache, Storage index, IORM, Exadata Hybrid Columnar Compression are the features
Flash Cache: It is a hardware component in Exadata to deliver high performance reading and writing. It holds frequently accessed data so on next time access physical reading can be avoid.
Exadata comes in the following configuration:
Full Rack - DB Server: 8 (384 cores), Cell Storage: 14
Half Rack: DB Server: 4 (192 core), Cell Storage: 7
Quater Rack: DB Server: 2 (96 core), Cell Storage: 3
1/8th Rack.- DB Server: 2 (48 core) , Cell Storage: 3
A storage index is a structure store in memory that contains key statistics. This storage index enables Exadata to speed up disk I/O operations and queries by checking the index first to find relevant data
Smart Scan greatly accelerates query execution, eliminates bottlenecks, and significantly reduces the CPU usage of the database servers. Exadata Smart Scan works in collaboration with Exadata Storage Indexes, which reduces the amount of IO
A spine switch is used to connect or add more Exadata machines to the cluster.
DBRM is the feature of the database while IORM is the feature of storage server software.
Patching Methods: Rolling(Slowbut no down time) , Bundle patching(Fast but small down time, but no single disk failure)
Autosys:
AutoSys is a multi platform automated job control system. This ttool provides the capability of SCheduling, Monitoring and Reporting of Autosys jobs
It helps to reduce the cost and complexity of managing mission critical business processes, ensuring consistent and reliable service delivery.
multi-platform scheduling, scalability, extensive application support, built-in fault tolerance, self-service, and security & compliance.
- Cron is OS specific however AutoSys is for ditributed environment.
- Cron works on time dependency only however Autosys is event dependent. ( like work only if the job A works)
three types of jobs in AutoSys?
A classification of Autosys job types are below,
Command Job. A command job runs executable, windows batch, scripts (Unix, PowerShell..)
File watcher Job. A file watcher job will monitor arrival of file into the system.
Box Job. A box job will have a set of jobs used to control and organize process flow.
How to create autosys jobs:
1. Using Autosys GUI: Job attribute can be set to describe How when and where the job should run.
2. Using Jil via ccommandline
PL/SQL (Procedural Language extensions to SQL)
It was build by oracle to overcome the disadvantages of SQL for easier building and handling of critical application in comprehensive manner
Disadvantages of SQL:
- There is no provision for desicion making, Looping and Branching in SQL
- The speed of execution decreases in SQL on Heavy traffic
- There is no feature of error checking while manipulating the data.
PL/SQL Features
- Provision for desicion making, Looping and Branching
- Multiple queries can be processed in one block by making use of a single command
- The PL/SQL code can be reused by applications as they can be grouped and stored in databases as PL/SQL units like functions, procedures, packages, triggers, and types.
- supports error checking and validation of data before data manipulation.
- portable across computer hardware or operating system where there is an Oracle engine
# PL/SQL tables are nothing but objects of type tables that are modeled as database tables. They are a way to provide arrays that are nothing but temporary tables in memory for faster processing
Structure: Block structure consists 3 section
#Trigger: As the name indicates, ‘Trigger’ means to ‘activate’ something. In the case of PL/SQL, a trigger is a stored procedure that specifies what action has to be taken by the database when an event related to the database is performed.
#Comment: Single Line: -- , Multi Line comment: /* */
#SYSDATE: This keyword returns the current time and date on the local database server.
# ROLLBACK command is used for rolling back all the changes from the beginning of the transaction.
ROLLBACK TO command is used for undoing the transaction only till a SAVEPOINT.
# DBMS_OUTPUT prints the output to the standard console.
DBMS_DEBUG prints the output to the log file
SQL PL/SQL
- SQL is a natural language meant for the interactive processing of data in the database. PL/SQL is a procedural extension of SQL.
- Decision-making and looping are not allowed in SQL. PL/SQL supports all features of procedural language such as conditional and looping statements.
- All SQL statements are executed at a time by the database server which is why it becomes a time-consuming process. PL/SQL statements are executed one block at a time thereby reducing the network traffic.
There is no error handling mechanism in SQL. This supports an error handling mechanism.
--declaration statements (optional)
--execution statements Mandatory
--exception handling statements (optional)
CREATE USER smithj
IDENTIFIED BY pwd4smithj
DEFAULT TABLESPACE tbs_perm_01
TEMPORARY TABLESPACE tbs_temp_01
QUOTA 20M on tbs_perm_01;
The syntax for changing a password in Oracle is:
ALTER USER user_name IDENTIFIED BY new_password;
The syntax for the DROP USER statement in Oracle/PLSQL is:
DROP USER user_name [ CASCADE ]; //we can exclude cascade if the user is not own objects in its schema
The syntax to retrieve the users logged into Oracle is:
SELECT USERNAME FROM V$SESSION;
FOR Lcntr IN 1..20
LOOP
LCalc := Lcntr * 31;
END LOOP;