40. Oracle_11g_Sql_Plsql_New_Features1. Oracle_10g_2_Day_DBA_for_windows
Creating Source Module from flat files :
- First create a flat file source module
- Use Create Module Wizard
- Create Flat file definitions by importing Metadata from the flat files
- Use import metadata wizard to import flat files
- Use flat file sample wizard to sample flat files
Source and Target Modules
- Modules are storage objects that logically group source and target object definitions
- Source modules are for data warehouse center or partner application to read data from.
- Target modules are for the Data Warehouse center or partner application to write data to.
Defining a Target Warehouse Module
- Create Target User, Target Module and Target schema
- Creating external tables
- Designing dimensions
- Designing cubes
ETL (Extract, Transform, Load) Data Flow Mappings
- Examine an existing map
- Use of mapping editor tool in warehouse builder
- Design mapping between external table and dimensions
- Design mapping between external table and cubes
- And finally generate code for these mapping using mapping editor
Deploying Objects in Target Warehouse
Deployment : Final step in creating physical instance of the data warehouse target modules :
- Deployment process generates scripts for creating data objects such as tables
- Deployment process also generated scripts that load data into these objects
Use Control center Manager provided in Oracle Warehouse builder to
- Deploy the external table
- Deploy the cube and the referenced dimensions
- Deploy and execute the ETL mappings
Check Metadata for Dependency
Inter Row Calculations using SQL model Clause
- SQL model clause is a Business Intelligence tool
- Query ouputs can be processed with inter row calculations
- Reduces the need for importing database query outputs to spreadsheets to get business results
- Increases performance, scalability and manageability of enterprise data warehousing applications
Filling Gaps in Sparse Data
- Often a row may not have data for a particular column or dimension
- Example : daily sales data with no sales on a particular day
- This is known as sparse data
- Difficult to run business analytics
- Partitioned Outer join is an oracle feature that fills these gaps in sparse data
- This is called densification of sparse data
- This makes it easier to apply data warehousing applications to sparse data
High speed data loading adapted to data warehousing applications
- Load flat flies as external tables
- Loading in parallel is allowed
- Faster than the corresponding SQL *Loader available in previous Oracle Versions
Rolling windows operations using oracle partition
- Segregate past data using oracle partition
- Use rolling window to
- Add new data to new partition
- Remove old partition
- Reduces cost by
- Improved disk utilization
- Reduced impact on end user
Oracle ETL Infrastructure features
Oracle Application Express
Oracle Warehouse builder
- Enterprise business intelligence integration design tool.
- Manages the full life-cycle of data and metadata for the Oracle database
- Provides an easy to use, graphical environmetn to rapidly desgitn, deploy, and manage business intelligence systems.
- Formerly called Oracle Business Ingelligence Warehouse Builder
What is ILM ?
- ILM is the process of managing data form its creation until the time it can be disposed of. It involves understanding :
- How the data evolves
- How it grows in size
- How its usage changes over time, and
- How long it is required to be kept
- ILM is required in order to satisfy compliance with legal and regulatory frameworks
ILM in Oracle
Oracle is an ideal platform for ILM because it has :
- Fine grained auditing
- Enforceable compliance policies
- Transparent data classification, and
- Ability to store various kinds of data in one architectural framework
ILM Implementation and Management
- ILM can be implemented using Oracle Database by following these steps :
- Define the data classes
- Create storage tiers for the data classes
- Create data access and migration policies
- Define and enforce compliance policies
- Oracle ILM assistant – a GUI based tool for managing ILM environment
What is a cluster database
- Oracle RAC is a software option to Oracle database
- Multiple servers running Oracle software simultaneously and still accessing a single database
- RAC harnesses the power of multiple low-cost computers to server as a single large computer for database processing
RAC vs non RAC
- Single database
- Single instance on single server
- Single database
- Multiple servers running an instance in each
Ease of RAC operation
- Cluster database home page provides a unified and simple way to view
- All the targets in the clusters
- All the instances of the database
- The state of interconnects in the cluster
- Cluster database instance home page displays
- Current state of an instance
What is Virtual Private Database or VPD?
- VPD provides row level access control
- VPD enables access control with assurance of physical data control
Benefits of VPD
What are the benefits of VPD?
VPD works by:
- Associating one or more security policies with tables of views
- These policies enforce access condition which are appended to user’s SQL query statements
- VPD can be implemented using stored procedures
Benefits of VPD
- More robust security mechanism
- Security enforced by the database
- Lower cost of application development
- Lower cost of application deployment
What is Fine Grained Auditing or FGA?
- Standar auditing tools allow for observing and tracking activities within a database’s tables, sessions, and objects
- Fine-Grained Auditing (FGA) capability in Oracle 10g allows implementation of auditing at extremely low level of granularity against any table in the database through implementation of FGA policy.
FGA – improvements over standar audit tools
- Security audit is done using triggers to log updates to sensitive data
- Fine-grained auditing is a superior tool for detecting security violations by tracking unauthorized access and changes within database tables and views.
- Can track execution of SELECT statements for which triggers are not available
- Can be used to check access to specific rows, column, or any other specific datasets
- Significantly better and high performance compared to triggers
- Can be used in combination with existing audit tools and trails
- Can trigger an action such as sending email when an audit violation occurs
Oracle Label Security – OLS
What is Oracle Label Security or OLS?
- Offers a powerful implementation of row-based security for restricting user access to specific data
- Useful especially in a data warehousing environment.
- Makes use of and expands the scope of Virtual Private Database (VPD) technology
How does OLS work?
- Security policies are established to identify how the data needs to be secured.
- User labels are used to define row-level security policies
- Access mediation process used to determine
- Permissions required to access the row
- Actions permitted on the row once accessed.
Transparent Data Encryption
What is TDE?
- A mechanism to encrypt sensitive information in the database.
- Encryption is done by Oracle before writing to the disk.
- Individual applications are not burdened with encryption if TDE facility is used.
What are the advantages of TDE?
- Complexity of application program is reduced.
- Applications can run as is without modification after encryption.
- Encryption management can be done across the enterprise with common policy enforced by Oracle.
How to set up TDE?
- Prepare the Database for encryption by editing the sqlnet.ora file to include an entry
- Connect to the database as SYSDBA and create the master encryption key by executing the command
alter system set key identified by “username”
- For later sessions the wallet needs to be open, This is done by executing the command
alter system set wallet open identified by “username”
What are the applications of TDE?
- Creating Tables with encrypted columns
- Creating Index on encrypted data
- Making changes to the encrypted data
Oracle memiliki fitur yang dinamakan Flashback recovery yang digunakan untuk mengembalikan table yang telah kita delete. Table yang telah kita delete oleh oracle disimpan didalam recycle bin untuk ditampung sementara sebelum dibersihkan.
- Saya membuat satu table di schema HR yang diberi nama TESTLAH yang disimpan didalam tablespace USERS. Lalu saya akan mendelete table tersebut dengan mengklik button Delete with options
- Tampilan sesudah button delete with options ditekan. Pilih delete yang paling atas, yang akan mendelete semua isi table.
- Pesan yang menunjukkan table telah berhasil didelete
- Table sudah terhapus dan selanjutnya klik button recycle bin
- Pilih Schema dan tekan button Flashback drop untuk mengembalikan table yang telah didelete
- Show SQL