What's New in Oracle 10g (New features in Oracle 10g)

What's New in Oracle 10g (New features in Oracle 10g)

The following new features were introduced with Oracle 10g:

Oracle 10g Release 1 (10.1.0) - January 2004

• Grid computing - an extension of the clustering feature (Real Application Clusters).


• SYSAUX tablespace has been introduced as an auxiliary to SYSTEM, as LOCAL managed tablespace.


• Data Pump- faster data movement with expdp and impdp, successor for normal exp/imp.


• NID utility has been introduced to change the database name and id.


• Oracle Enterprise Manager (OEM) became browser based. Through any browser we can access data of a database in Oracle Enterprise Manager Database Control. Grid Control is used for accessing/managing multiple instances.


• Automated Storage Management (ASM). ASMB, RBAL, ARBx are the new background processes related to ASM.


• Manageability improvements (self-tuning features).


• Performance and scalability improvements.


• Automatic Workload Repository (AWR).


• Automatic Database Diagnostic Monitor (ADDM).


• Active Session History (ASH).


• Flashback operations available on row, transaction, table or database level.


• Ability to UNDROP (Flashback Drop) a table using a recycle bin.


• Ability to rename tablespaces (except SYSTEM and SYSAUX), whether permanent or temporary, using the following command:
  SQL> ALTER TABLESPACE oldname RENAME TO newname;


• Ability to transport tablespaces across platforms (e.g. Windows to Linux, Solaris to HP-UX), which has same ENDIAN formats. If ENDIAN formats are different we have to use RMAN.


• In Oracle 10g, undo tablespace can guarantee the retention of unexpired undo extents.
  SQL> CREATE UNDO TABLESPACE ... RETENTION GUARANTEE;
  SQL> ALTER TABLESPACE UNDO_TS RETENTION GUARANTEE;


• New 'drop database' statement, will delete the datafiles, redolog files mentioned in control file and will delete SP file also.
  SQL> STARTUP RESTRICT MOUNT EXCLUSIVE;
  SQL> DROP DATABASE;


• New memory structure in SGA i.e. Streams pool (streams_pool_size parameter), useful for datapump activities & streams replication.


• Introduced new init parameter, sga_target, to change the value of SGA dynamically. This is called Automatic Shared Memory Management (ASMM). It includes buffer cache, shared pool, java pool and large pool. It doesn't include log buffer, streams pool and the buffer pools for nonstandard block sizes and the non-default ones for KEEP or RECYCLE.
  SGA_TARGET = DB_CACHE_SIZE + SHARED_POOL_SIZE + JAVA_POOL_SIZE + LARGE_POOL_SIZE


• New background processes in Oracle 10g
  • Memory Manager (maximum 1) MMAN - MMAN dynamically adjust the sizes of the SGA components like DBC, large pool, shared pool and java pool. It is a new process added to Oracle 10g as part of automatic shared memory management.
  • Memory Monitor (maximum 1) MMON - MMON monitors SGA and performs various manageability related background tasks.
  • Memory Monitor Light (maximum 1) MMNL - New background process in Oracle 10g.
  • Change Tracking Writer (maximum 1) CTWR - CTWR will be useful in RMAN.
  • ASMB - This ASMB process is used to provide information to and from cluster synchronization services used by ASM to manage the disk resources. It's also used to update statistics and provide a heart beat mechanism.
  • Re-Balance RBAL - RBAL is the ASM related process that performs rebalancing of disk resources controlled by ASM.
  • Actual Rebalance ARBx - ARBx is configured by ASM_POWER_LIMIT.


• DBA can specify a default tablespace for the database.


• Temporary tablespace groups to group multiple temporary tablespaces into a single group.
  


• From Oracle Database 10g, the ability to prepare the primary database and logical standby for a switchover, thus reducing the time to complete the switchover.
  On primary,
  ALTER DATABASE PREPARE TO SWITCHOVER TO LOGICAL STANDBY;
  On logical standby,
  ALTER DATABASE PREPARE TO SWITCHOVER TO PRIMARY;


• New packages
  • DBMS_SCHEDULER, which can call OS utilities and programs, not just PL/SQL program units like DBMS_JOB package. By using this package we can create jobs, programs, schedules and job classes.
  • DBMS_FILE_TRANSFER package to transfer files.
  • DBMS_MONITOR, to enable end-to-end tracing (tracing is not done only by session, but by client identifier).
  • DBMS_ADVISOR, will help in working with several advisors.
  • DBMS_WORKLOAD_REPOSITORY, to aid AWR, ADDM, ASH.


• Support for bigfile tablespaces are up to 8EB (Exabytes) in size.


• Rules-Based Optimizer (RBO) is desupported (not deprecated).


• Auditing: FGA (Fine-grained auditing) now supports DML statements in addition to selects.


• New features in RMAN
  • Managing recovery related files with flash/fast recovery area.
  • Optimized incremental backups using block change tracking (Faster incremental backups) using a file (named block change tracking file). CTWR (Change Tracking Writer) is the background process responsible for tracking the blocks.
  • Reducing the time and overhead of full backups with incrementally updated backups.
  • Comprehensive backup job tracking and administration with Enterprise Manager.
  • Backup set binary compression.
  • New compression algorithm BZIP2 brought in.
  • Automated Tablespace Point-in-Time Recovery.
  • Automatic channel failover on backup & restore.
  • Cross-platform tablespace conversion.
  • Ability to preview the backups required to perform a restore operation.
    RMAN> restore database preview [summary];
    RMAN> restore tablespace tbs1 preview;


• SQL*Plus enhancements
  1. The default SQL> prompt can be changed by setting the below parameters in $ORACLE_HOME/sqlplus/admin/glogin.sql
     • _connect_identifier (will prompt DBNAME>)
     • _date (will prompt DATE>)
     • _editor
     • _o_version
     • _o_release
     • _privilege (will prompt AS SYSDBA> or AS SYSOPER> or AS SYSASM>)
     • _sqlplus_release
     • _user (will prompt USERNAME>)
  
  
  2. From 10g, the login.sql file is not only executed at SQL*Plus startup time, but also at connect time as well. So SQL prompt will be changed after connect command.
  
  
  3. Now we can login as SYSDBA without the quotation marks.
     sqlplus / as sysdba
     (as well as old sqlplus "/ as sysdba" or sqlplus '/ as sysdba'). This enhancement not only means we have two fewer characters to type, but provides some additional benefits such as not requiring escape characters in operating systems such as Unix.
  
  
  4. From Oracle 10g, the spool command can append to an existing one.
     SQL> spool result.log append
  
  
  5. 10g allows us to save statements as appended to the files.
     SQL> Query1 ....
     SQL> save myscripts
     SQL> Query2 ....
     SQL> save myscripts append
  
  
  6. describe command can give description of rules and rule sets.


• Virtual Private Database (VPD) has grown into a very powerful feature with the ability to support a variety of requirements, such as masking columns selectively based on the policy and applying the policy only when certain columns are accessed. The performance of the policy can also be increased through multiple types of policy by exploiting the nature of the application, making the feature applicable to multiple situations.


• We can now shrink segments, tables and indexes to reclaim free blocks, provided that Automatic Segment Space Management (ASSM) is enabled in the tablespace.
  SQL> alter table table-name shrink space;


• From 10g, statistics are collected automatically if STATISTIC_LEVEL is set to TYPICAL or ALL. No need of ALTER TABLE ... MONITORING command.


• Statistics can be collected for SYS schema, data dictionary objects and fixed objects (x$ tables).


• Complete refresh of materialized views will do delete, instead of truncate, by setting ATOMIC_REFRESH to TRUE.
• Oracle 10g introduced the trcsess utility, which allows trace information from multiple trace files to be identified and consolidated into a single trace file.


• Introduced Advisors
  • SQL Access Advisor
  • SQL Tune Advisor
  • Memory Advisor
  • Undo Advisor
  • Segment Advisor
  • MTTR (Mean Time To Recover) Advisor

Oracle 10g Release 2 (10.2.0) - September 2005

• New asmcmd utility for managing ASM storage.


• Async COMMITs.


• Passwords for DB Links are encrypted.


• Transparent Data Encryption.
• Fast Start Failover for Data Guard was introduced in Oracle 10g R2.


• The CONNECT role can now only connect (CREATE privileges are removed).
  
  
  Before 10g,
  SQL> select PRIVILEGE from role_sys_privs where ROLE='CONNECT';
  PRIVILEGE

  ----------------------------------------

  CREATE VIEW

  CREATE TABLE

  ALTER SESSION

  CREATE CLUSTER

  CREATE SESSION

  CREATE SYNONYM

  CREATE SEQUENCE

  CREATE DATABASE LINK

  
  

  From 10g,

  SYS> select PRIVILEGE from role_sys_privs where ROLE='CONNECT';

  PRIVILEGE

  ----------------------------------------

  CREATE SESSION