oracleasm utility

Oracle ASM library (ASMLib) driver

#/etc/init.d/oracleasm start

#/etc/init.d/oracleasm stop

#/etc/init.d/oracleasm restart

#/etc/init.d/oracleasm status


#/etc/init.d/oracleasm enable

#/etc/init.d/oracleasm disable


#/etc/init.d/oracleasm configure

#/etc/init.d/oracleasm createdisk DISK_NAME PARTITION_NAME
#/etc/init.d/oracleasm createdisk VOL3 /dev/sdd1
#/etc/init.d/oracleasm createdisk ASM_DATA /dev/cciss/c0d1p1
#/etc/init.d/oracleasm scandisks

#/etc/init.d/oracleasm listdisks

#/etc/init.d/oracleasm querydisk DISK_NAME
#/etc/init.d/oracleasm querydisk -d LABEL
#/etc/init.d/oracleasm querydisk /dev/sdc6
#/etc/init.d/oracleasm querydisk -d VOL6
#/etc/init.d/oracleasm querydisk -p VOL1
#/etc/init.d/oracleasm renamedisk PARTITION_NAME DISK_NAME
#/etc/init.d/oracleasm renamedisk /dev/sdc1 VOL2
#/etc/init.d/oracleasm deletedisk DISK_NAME
#/etc/init.d/oracleasm deletedisk /dev/sdc9

/etc/sysconfig/oracleasm  — Oracle ASMLib configuration file

Advertisements

lsnrctl commands in Oracle

$ lsnrctl start [listener_name]

$ lsnrctl stop [listener_name]
$ lsnrctl status [listener_name]
$ lsnrctl reload [listener_name]
$ lsnrctl services [listener_name]
LSNRCTL> help
LSNRCTL> version [listener_name]    — get the version information of the listener
LSNRCTL> start [listener_name]   — starts the listener
LSNRCTL> stop [listener_name]    — stops the listener
LSNRCTL> status [listener_name]   — get the status of listener
LSNRCTL> services [listener_name]   — get the service information of the listener
LSNRCTL> reload [listener_name]    — reload the parameter files and SIDs
LSNRCTL> save_config [listener_name]    — saves configuration changes to parameter file
LSNRCTL> trace OFF | USER | ADMIN | SUPPORT [listener_name]    — set tracing to the specified level
LSNRCTL> spawn [listener_name] spawn_alias [(ARGUMENTS=’arg0, arg1,…’)]
LSNRCTL> change_password [listener_name]   –– changes the password of the listener

LSNRCTL> set [below_modifier]
password                           rawmode
displaymode                        trc_file
trc_directory                      trc_level
log_file                           log_directory
log_status                         current_listener
inbound_connect_timeout            startup_waittime
save_config_on_stop                dynamic_registration
enable_global_dynamic_endpoint     connection_rate_limit
LSNRCTL> show [below_modifier]
rawmode                            displaymode
rules                              trc_file
trc_directory                      trc_level
log_file                           log_directory
log_status                         current_listener
inbound_connect_timeout            startup_waittime
snmp_visible                       save_config_on_stop
dynamic_registration               enable_global_dynamic_endpoint
oracle_home                        pid
connection_rate_limit

LSNRCTL> quit
LSNRCTL> exit
default listener_name is LISTENER

emca Enterprise Manager Configuration Assistant (emca)

emca Enterprise Manager Configuration Assistant

emca  — Enterprise Manager Configuration Assistant, in Oracle
$ORACLE_HOME/bin/emca [operation] [mode] [dbType] [flags] [parameters]
emca -h or emca –h or emca -help or emca –help    — prints this help message
emca -version      — prints the version
emca -config dbcontrol db [-repos (create | recreate)] [-cluster] [-silent] [-backup] [parameters]        — configure Database Control for a database
emca -config dbcontrol db
emca -config dbcontrol db -repos recreate
emca -config centralAgent (db | asm) [-cluster] [-silent] [parameters]        — configure central agent management
emca -config centralAgent db
emca -config all db [-repos (create | recreate)] [-cluster] [-silent] [-backup] [parameters]    — configure both Database Control and central agent management
emca -deconfig dbcontrol db [-repos drop] [-cluster] [-silent] [parameters]       — de-configure Database Control
emca -deconfig dbcontrol db
emca -deconfig centralAgent (db | asm) [-cluster] [ -silent] [parameters]        — de-configure central agent management
emca -deconfig centralAgent db
emca -deconfig all db [-repos drop] [-cluster] [-silent] [parameters]        — de-configure both Database Control and central agent management
emca -reconfig ports [-cluster] [parameters]        — explicitly reassign Database Control ports
emca -reconfig dbcontrol -cluster [-silent] [parameters]       — reconfigures RAC Database Control deployment
emca -displayConfig dbcontrol -cluster [-silent] [parameters]        — displays information about the RAC Database Control configuration

emca -addNode (db | asm) [-silent] [parameters]          — configure EM for a newly added node for a database
emca -deleteNode (db | asm) [-silent] [parameters]        — de-configure EM for for a database on node getting deleted
emca -addInst (db | asm) [-silent] [parameters]           — configure EM for a new RAC instance
emca -deleteInst (db | asm) [-silent] [parameters]        — de-configure EM for a specified RAC instance
emca -migrate -from dbcontrol -to centralAgent  [-repos drop] [-cluster] [-silent] [parameters]   — migrates EM configuration from Database Control to central agent
emca -upgrade (db | asm | db_asm) [-cluster] [-silent] [parameters]        — upgrades an earlier version of the EM configuration to the current version
emca -restore (db | asm | db_asm) [-cluster] [-silent] [parameters]        — restores the current version of the EM configuration to an earlier version
emca -updateTargets crs [-silent] [parameters]        — updates new crs home for all dbcontrol and central agents on crs upgrade
emca -repos create
emca -repos drop
Parameters and Options:
[parameters]: [ -respFile fileName ] [ -paramName paramValue ]*
db: perform configuration operation for a database (including databases that use ASM)
asm: perform configuration operation for an ASM-only instance
db_asm: perform upgrade/restore operation for a database and an ASM instance
-repos create: create a new Database Control repository
-repos drop: drop the current Database Control repository
-repos recreate: drop the current Database Control repository and recreate a new one
-cluster: perform configuration operation for a RAC database
-silent: perform configuration operation without prompting for parameters
-backup: configure automatic backup for a database
Parameters for single instance databases
ORACLE_HOSTNAME: Local hostname
SID: Database SID
PORT: Listener port number
ORACLE_HOME: Database ORACLE_HOME
LISTENER_OH: Listener ORACLE_HOME
HOST: Listener Host
HOST_USER: Host username for automatic backup
HOST_USER_PWD: Host user password for automatic backup
BACKUP_SCHEDULE: Automatic backup schedule (HH:MM)
EMAIL_ADDRESS: Email address for notifications
MAIL_SERVER_NAME: Outgoing Mail (SMTP) server for notifications
ASM_OH: ASM ORACLE_HOME
ASM_SID: ASM SID
ASM_PORT: ASM port
ASM_USER_ROLE: ASM user role
ASM_USER_NAME: ASM username
ASM_USER_PWD: ASM user password
SRC_OH: ORACLE_HOME for the database to be upgraded
DBSNMP_PWD: Password for DBSNMP user
SYSMAN_PWD: Password for SYSMAN user
SYS_PWD: Password for SYS user
DBCONTROL_HTTP_PORT: Database Control HTTP port
AGENT_PORT: EM agent port
RMI_PORT: RMI port for Database Control
JMS_PORT: JMS port for Database Control
EM_SWLIB_STAGE_LOC:  Software library location
PORTS_FILE: Path to a static file specifying the ports to use (Default value : ${ORACLE_HOME}/install/staticports.ini)
Additional Parameters for cluster databases
CLUSTER_NAME: Cluster name
DB_UNIQUE_NAME: Database unique name
SERVICE_NAME: Service name
EM_NODE: Database Control node name
EM_NODE_LIST: Agent Node list [comma separated]
Note: For Desktop Class Install always pass parameter ORACLE_HOSTNAME as “localhost” to any emca command

dgmgrl utility

dgmgrl utility

dgmgrl – Data Guard Manager (Observer) Utility, in Oracle

$dgmgrl [-silent | -echo] [username/password[@connect_identifier] [dgmgrl_command]]

$dgmgrl sys/pwd
$dgmgrl sys/pwd@oltp
$dgmgrl -logfile observer.log / “stop observer”
$dgmgrl sys/test@dgprimary “show database ‘prod'”

$dgmgrl -silent sys/test@dgprimary “show configuration verbose”

ADD – Adds a standby database to the broker configuration.

DGMGRL> ADD DATABASE db_name [AS CONNECT IDENTIFIER IS conn_identifier] [MAINTAINED AS {PHYSICAL|LOGICAL}];
DGMGRL> ADD DATABASE ‘testdb’ AS CONNECT IDENTIFIER IS testdb MAINTAINED AS PHYSICAL;
DGMGRL> ADD DATABASE ‘logdb’ AS CONNECT IDENTIFIER IS logdb MAINTAINED AS LOGICAL;
DGMGRL> ADD DATABASE ‘devdb’ AS CONNECT IDENTIFIER IS devdb.foo.com;

CONNECT – Connects to an Oracle database instance.
DGMGRL> CONNECT username/password[@connect_identifier]
DGMGRL> CONNECT sys;
DGMGRL> CONNECT sys@test;

DGMGRL> CONNECT sys/pwd;

DGMGRL> CONNECT sys/pwd@dwh;

$dgmgrl connect sys

CONVERT – Converts a database from one type to another (from Oracle 11g).
DGMGRL> CONVERT DATABASE database_name TO {SNAPSHOT STANDBY|PHYSICAL STANDBY};
DGMGRL> CONVERT DATABASE ‘devdb’ to SNAPSHOT STANDBY;
DGMGRL> CONVERT DATABASE ‘devdb’ to PHYSICAL STANDBY;
CREATE – Creates a broker configuration.

DGMGRL> CREATE CONFIGURATION config_name AS PRIMARY DATABASE IS db_name CONNECT IDENTIFIER IS conn_ident;
DGMGRL> CREATE CONFIGURATION ‘dg’ AS PRIMARY DATABASE IS ‘prod’ CONNECT IDENTIFIER IS prod.foo.com;
DGMGRL> CREATE CONFIGURATION ‘dg_test’ AS PRIMARY DATABASE IS ‘test’ CONNECT IDENTIFIER IS test;

DISABLE – Disables a configuration, a database, or fast-start failover (FSFO).
DGMGRL> DISABLE CONFIGURATION;
DGMGRL> DISABLE CONFIGURATION;

DGMGRL> DISABLE DATABASE database_name;

DGMGRL> DISABLE DATABASE ‘devdb’;

DGMGRL> DISABLE FAST_START FAILOVER [FORCE | CONDITION condition];

DGMGRL> DISABLE FAST_START FAILOVER;
DGMGRL> DISABLE FAST_START FAILOVER FORCE;

EDIT – Edits a configuration, database, or instance.
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS {MaxProtection|MaxAvailability|MaxPerformance};
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXPROTECTION;
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXPERFORMANCE;

DGMGRL> EDIT CONFIGURATION SET PROPERTY property_name = value;

DGMGRL> EDIT CONFIGURATION SET PROPERTY FastStartFailoverThreshold = 45;


DGMGRL> EDIT DATABASE database_name SET PROPERTY property_name = value;

DGMGRL> EDIT DATABASE devdb SET PROPERTY ‘LogArchiveFormat’=’log_%t_%s_%r_%d.arc’;
DGMGRL> EDIT DATABASE prodb SET PROPERTY LogXptMode=SYNC;
DGMGRL> EDIT DATABASE prodb SET PROPERTY LogXptMode=ASYNC;
DGMGRL> EDIT DATABASE prodb SET PROPERTY LogXptMode=ARCH;
DGMGRL> EDIT DATABASE devdb SET PROPERTY LogShipping=OFF;
DGMGRL> EDIT DATABASE prodb SET PROPERTY LogArchiveTrace=8;
DGMGRL> EDIT DATABASE prodb SET PROPERTY NetTimeout=60;
DGMGRL> EDIT DATABASE devdb SET PROPERTY ‘ReopenSecs’=300;
DGMGRL> EDIT DATABASE prodb SET PROPERTY ArchiveLagTarget=1200;
DGMGRL> EDIT DATABASE prodb SET PROPERTY FastStartFailoverTarget=’standby’;
DGMGRL> EDIT DATABASE devdb SET PROPERTY ‘StandbyArchiveLocation’=’/oradata/archive/’;

DGMGRL> EDIT DATABASE “black” SET PROPERTY ‘DbFileNameConvert’ = ‘/u01/od01/datafile/, /oradisk/od01/datafile/’;
DGMGRL> EDIT DATABASE ubp SET PROPERTY DelayMins=’720′;
DGMGRL> EDIT DATABASE database_name RENAME TO new database_name;
DGMGRL> EDIT DATABASE ‘devdbb’ RENAME TO ‘devdb’;

DGMGRL> EDIT DATABASE database_name SET STATE = state [WITH APPLY INSTANCE = instance_name];
DGMGRL> EDIT DATABASE devdb SET STATE=’READ-ONLY’;
DGMGRL> EDIT DATABASE devdb SET STATE=’OFFLINE’;
DGMGRL> EDIT DATABASE devdb SET STATE=’APPLY-OFF’;
DGMGRL> EDIT DATABASE devdb SET STATE=’APPLY-ON’;
DGMGRL> EDIT DATABASE devdb SET STATE=’TRANSPORT-OFF’;
DGMGRL> EDIT DATABASE devdb SET STATE=’TRANSPORT-ON’;
DGMGRL> EDIT DATABASE prodb SET STATE=’LOG-TRANSPORT-OFF’;
DGMGRL> EDIT DATABASE devdb SET STATE=’ONLINE’ WITH APPLY INSTANCE=devdb2;

DGMGRL> EDIT INSTANCE instance_name [ON DATABASE database_name] SET AUTO PFILE [={init_file_path|OFF}];

DGMGRL> EDIT INSTANCE ‘devdb1’ ON DATABASE ‘devdb’ SET AUTO PFILE=’initdevdb1.ora’;


DGMGRL> EDIT INSTANCE instance_name [ON DATABASE database_name] SET PROPERTY property_name = value;
DGMGRL> EDIT INSTANCE * ON DATABASE database_name SET PROPERTY property_name = value;
DGMGRL> EDIT INSTANCE ‘proddb’ ON DATABASE ‘proddb’ SET PROPERTY ‘StandbyArchiveLocation’=’/oradata/arch/’;
ENABLE – Enables a configuration, a database, or fast-start failover (FSFO).

DGMGRL> ENABLE CONFIGURATION;
DGMGRL> ENABLE CONFIGURATION;

DGMGRL> ENABLE DATABASE database_name;

DGMGRL> ENABLE DATABASE ‘devdb’;

DGMGRL> ENABLE FAST_START FAILOVER [CONDITION condition];

DGMGRL> ENABLE FAST_START FAILOVER;

EXIT – Exits the program.
DGMGRL> EXIT;

FAILOVER – Changes a standby database to be the primary database.
DGMGRL> FAILOVER TO standby_database_name [IMMEDIATE]
DGMGRL> FAILOVER TO “testdb”;
DGMGRL> FAILOVER TO “snapdb” IMMEDIATE;

HELP – Displays description and syntax for a command.
DGMGRL> HELP [command];
DGMGRL> HELP REINSTATE
DGMGRL> HELP EDIT

QUIT – Exits the program.
DGMGRL> QUIT;

REINSTATE – Changes a database marked for reinstatement into a viable standby.
DGMGRL> REINSTATE DATABASE database_name;
DGMGRL> REINSTATE DATABASE prim1;

REM – Comment to be ignored by DGMGRL.
DGMGRL> REM [comment];

REMOVE – Removes a configuration, Oracle database, or instance.
DGMGRL> REMOVE CONFIGURATION [PRESERVE DESTINATIONS];
DGMGRL> REMOVE CONFIGURATION;

DGMGRL> REMOVE CONFIGURATION PRESERVE DESTINATIONS;



DGMGRL> REMOVE DATABASE database_name [PRESERVE DESTINATIONS];

DGMGRL> REMOVE DATABASE devdb;
DGMGRL> REMOVE DATABASE standby PRESERVE DESTINATIONS;

DGMGRL> REMOVE INSTANCE instance_name [ON DATABASE database_name];

DGMGRL> REMOVE INSTANCE inst1 ON DATABASE racdb;

SHOW – Displays information about a configuration, database, instance or FSFO.
DGMGRL> SHOW CONFIGURATION [VERBOSE];

DGMGRL> SHOW CONFIGURATION;
DGMGRL> SHOW CONFIGURATION VERBOSE;

DGMGRL> SHOW DATABASE [VERBOSE] db_name [property_name];

DGMGRL> SHOW DATABASE ‘devdb’;
DGMGRL> SHOW DATABASE VERBOSE ‘test’;

DGMGRL> SHOW DATABASE ‘dwhdb’ ‘StatusReport’;
DGMGRL> SHOW DATABASE ‘proddb’ ‘LogXptStatus’;
DGMGRL> SHOW DATABASE ‘proddb’ ‘InconsistentProperties’;
DGMGRL> SHOW DATABASE ‘proddb’ ‘InconsistentLogXptProps’;
DGMGRL> SHOW DATABASE ‘testdb’ ‘ArchiveLagTarget’;
DGMGRL> SHOW DATABASE ‘testdb’ ‘LogShipping’;
DGMGRL> SHOW DATABASE ‘testdb’ ‘PreferredApplyInstance’;
DGMGRL> SHOW DATABASE ‘proddb’ ‘StatusReport’;
DGMGRL> SHOW DATABASE ‘testdb’ ‘RecvQEntries’;
DGMGRL> SHOW DATABASE ‘proddb’ ‘SendQEntries’;

DGMGRL> SHOW INSTANCE [VERBOSE] instance_name [property_name] [ON DATABASE db_name];

DGMGRL> SHOW INSTANCE inst1;
DGMGRL> SHOW INSTANCE VERBOSE inst3;
DGMGRL> SHOW INSTANCE testdb ‘TopWaitEvents’;

DGMGRL> SHOW FAST_START FAILOVER;
DGMGRL> SHOW FAST_START FAILOVER;

SHUTDOWNShuts down a currently running Oracle instance.
DGMGRL> SHUTDOWN [NORMAL | IMMEDIATE | ABORT];
DGMGRL> SHUTDOWN;
DGMGRL> SHUTDOWN NORMAL;
DGMGRL> SHUT IMMEDIATE;
DGMGRL> SHUT ABORT;

SQL – Executes a SQL statement
DGMGRL> SQL “sql_statement”;
START – Starts the fast-start failover(FSFO) observer.
DGMGRL> START OBSERVER [FILE=observer_configuration_file];
DGMGRL> START OBSERVER;

STARTUPStarts an Oracle database instance.
DGMGRL> STARTUP [RESTRICT] [FORCE] [PFILE=filespec]
[NOMOUNT  |  MOUNT | OPEN [READ ONLY|READ WRITE]];
DGMGRL> STARTUP;
DGMGRL> STARTUP NOMOUNT;
DGMGRL> STARTUP MOUNT;
DGMGRL> STARTUP OPEN;

DGMGRL> STARTUP FORCE;
DGMGRL> STARTUP FORCE RESTRICT NOMOUNT;
DGMGRL> STARTUP PFILE=initdwh.ora NOMOUNT;

STOP – Stops the fast-start failover(FSFO) observer.
DGMGRL> STOP OBSERVER;

SWITCHOVER – Switches roles between a primary and standby database.
DGMGRL> SWITCHOVER TO standby_database_name;
DGMGRL> SWITCHOVER TO “standby”;

VALIDATE – command to checks whether the database is ready for a role transition or not.

DGMGRL> VALIDATE DATABASE …;    — From Oracle Database 12c
 

$ORACLE_HOME/rdbms/log/drc*.log

alter system set dg_broker_start=false;
alter system set dg_broker_start=false sid=’*’;
alter system set dg_broker_start=FALSE SCOPE=spfile SID=’*’;
alter system set dg_broker_start=true;
alter system set dg_broker_start=true sid=’*’;
alter system set dg_broker_start=TRUE SCOPE=spfile SID=’*’;

alter system set dg_broker_config_file1=’/u01/dg_broker_config_files/dr1TESTPRI.dat’ sid=’*’;
alter system set dg_broker_config_file2=’/u01/dg_broker_config_files/dr2TESTPRI.dat’ sid=’*’;


Source: Internet

What’s New in Oracle 9i (New features in Oracle 9i)

The following new features were introduced with Oracle 9i:

Oracle 9i Release 1 (9.0.1) – June 2001

    • Traditional Rollback Segments (RBS) are still available, but can be replaced with automated System Managed Undo (SMU). Using SMU, Oracle will create it’s own “Rollback Segments” and size them automatically without any DBA involvement.
    • Flashback query (dbms_flashback.enable) – one can query data as it looked at some point in the past. This feature will allow users to correct wrongly committed transactions without contacting the DBA to do a database restore.
    • Use Oracle Ultra Search for searching databases, file systems, etc. The UltraSearch crawler fetches data and hand it to Oracle Text to be indexed.
    • Oracle Nameserver is still available, but deprecate in favor of LDAP Naming (using the Oracle Internet Directory Server). A nameserver proxy is provided for backwards compatibility as pre-8i client cannot resolve names from an LDAP server.
    • Oracle Parallel Server’s (OPS) scalability was improved – now called Real Application Cluster (RAC). Full Cache Fusion implemented. Any application can scale in a database cluster. Applications don’t need to be cluster aware anymore.
    • The Oracle Standby DB feature renamed to Oracle Data Guard. New Logical Standby databases replay SQL on standby site allowing the database to be used for normal read write operations. The Data Guard Broker allows single step fail-over when disaster strikes.
    • Scrolling cursor support. Oracle9i allows fetching backwards in a result set.
    • Dynamic Memory Management – Buffer Pools and shared pool can be resized on-the-fly. Introduced sga_max_size parameter. This eliminates the need to restart the database each time parameter changes were made.
    • On-line table and index reorganization.
    • VI (Virtual Interface) protocol support, an alternative to TCP/IP, available for use with Oracle Net (SQL*Net). VI provides fast communications between components in a cluster.
    • Build in XML Developers Kit (XDK). New data types for XML (XMLType), URI’s, etc. XML integrated with AQ.
    • Cost Based Optimizer now also considers memory and CPU, not only disk access cost as before.
    • Automatic Segment Space Management introduced in Oracle 9.0.1
    • PL/SQL programs can be natively compiled to binaries.
    • A new SQL feature introduced in Oracle Database 9i, which allowing us to return values from DML, by using DML RETURNING clause. For example: INSERT INTO customers (…) VALUES (…) RETURNING cust_id into l_cust_id;
    • Deep data protection – fine grained security and auditing. Put security on DB level. SQL access does not mean unrestricted access.
    • Resumable backups and statements – suspend statement instead of rolling back immediately.
  • List partitioning– partitioning on a list of values.
  • ETL (eXtract, Transformation, Load) Operations – with external tables and pipelining.
  • Oracle OLAP- Express functionality included in the DB.
  • Data Mining – Oracle Darwin’s features included in the DB.
  • DBA can specify a default temporary tablespace for the database.
  • In Oracle9i, significant improvements have been made to materialized view refresh:
    • Fast refresh is now possible on materialized views that contain joins and aggregates even when base table data has changed using DMLs. In Oracle8i, fast refresh was possible, on materialized views that contain joins and aggregates, only if base table data was inserted using SQL*Loader direct path.
    • Fast refresh is possible after partition maintenance operations, such as TRUNCATE PARTITION, on tables referenced in the materialized view.
    • A new mechanism called Partition Change Tracking (PCT) has been introduced. This mechanism keeps track of the base table partitions that have been updated since the materialized view was last refreshed. This allows Oracle to identify fresh data in the materialized view.

Oracle 9i Release 2 (9.2.0) – May 2002

  • Locally Managed SYSTEM tablespaces.
  • Oracle Streams – new data sharing/replication feature (can potentially replace Oracle Advance Replication and Standby Databases).
  • XML DB (Oracle is now a standards compliant XML database).
  • Data segment compression (compress keys in tables – only when loading data).
  • Cluster File System (CFS) for Windows and Linux (raw devices are no longer required).
  • Create logical standby databases with Data Guard.
  • Java JDK 1.3 used inside the database (JVM).
  • New system privilege, “GRANT ANY OBJECT PRIVILEGE” is introduced to control the grant and revoke object privileges.
  • Oracle Data Guard Enhancements (SQL Apply mode – logical copy of primary database, automatic failover).
  • DBMS_XPLAN Introduced in Oracle 9.2, displays execution plans in EXPLAIN plan table.
  • Security Improvements – Default Install Accounts locked, VPD on synonyms, AES, Migrate Users to Directory.
Source: Internet

New features in Oracle Database 12c Release 1

Oracle Database 12c, c for cloud, a multi-tenant database management system, with nearly 500 new features.

Released on June 26, 2013

SQL:
  • Increased size limit for VARCHAR2, NVARCHAR2, and RAW datatypes to 32K  (from 4K).
  • We can make a column invisible.
    SQL> create table test (column-name column-type invisible);
    SQL> alter table table-name modify column-name invisible;
    SQL> alter table table-name modify column-name visible;
  • Oracle Database 12c has new feature called “Identity Columns” which are auto-incremented at the time of insertion (like in MySQL).
    SQL> create table dept (dept_id number generated as identity, dept_name varchar);
    SQL> create table dept (dept_id number generated as identity (start with 1 increment by 1 cache 20 noorder), dept_name varchar);
  • Temporary undo (for global temporary tables) will not generate undo. We can manage this by using init parameter temp_undo_enabled=false.
  • No need to shutdown database for changing archive log mode.
  • Duplicate Indexes – Create duplicate indexes on the same set of columns. Till Oracle 11.2, if we try to create an index using the same columns, in the same order, as an existing index, we’ll get an error. In some cases, we might want two different types of index on the same data (such as in a datawarehouse where we might want a bitmap index on the leading edge of a set of columns that exists in a Btree index).
  • PL/SQL inside SQL: this new feature allows to use DDL inside SQL statements (i.e.: to create a one shot function)
  • The object DEFAULT clause has been enhanced. Adding a column to an existing table with a default value (much faster with Oracle 12c and it consumes less space than before, pointer to the Oracle Data Dictionary), applies also to sequences, identity types etc…
  • Pagination query, SQL keywords to limit the number of records to be displayed, and to replace ROWNUM records.
    SQL> select … fetch first n rows only;
    SQL> select … offset m rows fetch next n rows only;
    SQL> select … fetch first n percent rows only;
    SQL> select … fetch first n percent rows with ties;
  • Moving and Renaming datafile is now ONLINE, no need to put datafile in offline.
    SQL> alter database move datafile ‘path’ to ‘new_path’;
  • The TRUNCATE command has been enhanced with a CASCADE option which follows child records.
  • Reduces contents of regular UNDO, allowing better flashback operations.

PL/SQL:

  • PL/SQL Unit Security – A role can now be granted to a code unit. That means you can determine at a very fine grain, who can access a specific unit of code.
  • SQL WITH Clause Enhancement – In Oracle 12c, we can declare PL/SQL functions in the WITH Clause of a select statement.
  • Implicit Result Sets – create a procedure, open a ref cursor, return the results. No types, not muss, no mess. Streamlined data access (kind of a catch up to other databases).
  • MapReduce in the Database – MapReduce can be run from PL/SQL directly in the database.
  • We can use Booleans values in dynamic PL/SQL. Still no Booleans as database types.


Database:

  • New background processes – LREG (Listener Registration), SA (SGA Allocator), RM.
  • Like sysdba, sysoper & sysasm, we have new privileges, in Oracle 12c.
    sysbackup for backup operations
    sysdg for Data Guard operations
    syskm for key management
  • Oracle Database 12c Data Pump will allow turning off redo for the import operation (only).
    $ impdp … transform=disable_archive_logging:y
  • expdp has transport view, view_as_tables options.
  • Enhanced statistics (Hybrid histograms for more than 254 distinct values, dynamic sampling up to eleven, and stats automatically gathered during load).
  • Row pattern matching – “MATCH_RECOGNIZATION” (identification of patterns within a table ordered/sorted by the SQL statement).
  • Adaptive execution plans (change of the plan at runtime).
  • Oracle 12c includes database level redaction, allowing granular control of access to sensitive data.
  • Multi threaded database with parameter threaded_executions.
  • Oracle introduced parameter PGA_AGGREGATE_LIMIT which is a real/PGA memory limit.
  • UNDO for temporary tables can now be managed in TEMP, rather than the regular UNDO tablespace.
  • Oracle Enterprise Manage Express (lightweight EM Cloud Control 12c version), replaces the Oracle Database console and, is installed automatically.
  • enable_ddl_logging
  • Monitor the privilege assignments easy in 12c with DBMS_PRIVILEGE_CAPTURE.
  • Reduces the size of redo associated with recovering the regular UNDO tablespace.


ASM: (Oracle Grid Infrastructure new features)

  • Introduction of Flex ASM, ASM would run only on 3 instances on a cluster even if more than 3, the instances that not have an ASM would request remote ASM, typically like SCAN. In normal conditions in a node if ASM fails the entire node will be useless, where in 12c the ability to get the extent map from remote ASM instance makes the node useful.
  • Introduction of Flex Cluster, with light weight cluster stack, leaf node and traditional stack hub node, application layer is the typical example of leaf nodes where they don’t require any network heartbeat.


RMAN:

  • RMAN TABLE Point-In-Time Recovery (combination of Data Pump and RMAN, auxiliary instance required).
    RMAN> recover table table_name until scn scn_number auxiliary destination on ‘path’;
  • Running SQL commands in RMAN without SQL keyword.
    RMAN> select * from v$session;
  • Recover or copy files from Standby databases.
    Refresh a single datafile on the primary from the standby (or standby from primary).
  • Table level restoration i.e object level.
  • Incremental recovery more faster, many of the tasks removed.
  • Rolling forward/Synchronizing a standby database.


Partitioning:

  • Partitioning enhancements (partition truncate, cascading, global index cleanup, online moving of a partition, …)
  • Multiple partition operations in a single DDL.
  • Interval-Ref Partitions – we can create a ref partition (to relate several tables with the same partitions) as a sub-partition to the interval type.
  • Cascade for TRUNCATE and EXCHANGE partition.
  • Asynchronous Global Index maintenance for DROP and TRUNCATE. Command returns instantly, but index cleanup happens later.
  • Online move of a partition(without DBMS_REDEFINTIION).

Patching:

  • Centralised patching.
  • We can test patches on database copies, rolling patches out centrally once testing is complete.


Compression:

  • Automated compression with heat map.
  • Optimisation can be run on live databases with no disruption. Data optimization will monitor the data usage and with policy archive old data and hot data will be compressed for faster access. Inactive data can be more aggressively compressed or archived, greatly reducing storage costs.
  • Advanced Row compression (for Hot Data).
  • Columnar Query compression (for Warm Data).
  • Columnar Archive compression (for Archive Data).

Data Guard:

  • Oracle Database 12c introduces a new redo transportation method which omits the acknowledgement (to primary) of the transaction on the standby. This feature is called “Fast Sync” redo transport.
  • Creating a new type of redo destination called “Far Sync Standby”. A “Far Sync Standby” is composed only of the standby control files, the standby redo logs and some disk space for archive logs which shall be sent to the Standby database. Failover & Switchover operations are totally transparent as the “Far Sync Standby” cannot be used as the target.
  • Data Guard Broker commands have been extended. The “validate database” command to checks whether the database is ready for role transition or not.
  • Dataguard Broker now supports cascaded standby.
  • Global Temporary Tables can now be used on an Active Guard standby database.

Pluggable Databases:
In Oracle 12c, in a pluggable database environment, we can create a single database container, and plug multiple databases into this container. All these databases then share the exact same oracle server/background processes and memory, unlike the previous versions where each database has its own background processes and shared memory. This helps in database consolidation and reduces the overhead of managing multiple desperate databases.

Container Database (CDB): Are the core data dictionary objects that come after an Oracle database installation.
Pluggable Database (PDB): Data dictionary objects and data related to the application. We can have many PDB plugged into a single CDB.

A new admin role “CDB Administrator” has been introduced in Oracle 12.1 release databases.
Multiple databases can then share a master LGWR process, but have their own dedicated LGWR process within the container.

All Oracle database options/features are available on the PDB level.
RMAN backup at CDB level.

We can unplug a PDB from a CDB to another CDB.
PDB’s can be cloned inside the CDB.
Management of PDB (clone/creation/plug/unplug/drop) are implemented as SQLs.
Extremely fast PDB-provisioning (clone inside the CDB), because each CDB comes with a “PDB Seed”.
Database patch/upgrade management very quick as CDB is a single point of installation.
Each PDB has its own data dictionary.
Data Guard configuration on CDB as whole.
RMAN point-in-time recovery at PDB level (while other PDB’s remains open).
Resource Manager is extended for creating, unplugging, plugging in, and cloning, dropping or even setting up for the open mode of the PDB.
Flashback of a PDB should be available for Oracle 12c Release 2.

Entire containers can be backed up in single run, regardless of how many databases they contain.
Upgrade one container database and all pluggable databases are upgraded.

New Commands
create pluggable database …
alter pluggable database …
drop pluggable database …

New Views/Packages in Oracle 12c Release1
dba_pdbs
v$pdbs
cdb_data_files

dbms_pdb
dbms_qopatch

What’s New in Oracle 11g Release 2

Oracle 11g Release 2 (11.2.0) – Sept 2009

    • A separate tool, named deinstall, introduced for deinstallation and deconfiguration of Oracle products. Oracle Universal Installer no longer removes Oracle software.


  • chopt tool, a command-line utility, to configure the database options. Oracle Universal Installer no longer provides the custom installation option of individual components.


  • Unusable indexes and index partitions no longer consume space in the database because they become segmentless.


  • Complete IPv6 Support for JDBC Thin Clients.


  • From this release, Oracle/ASM will Support 4KB Sector Disk Drives.


  • Edition-based redefinition allows an application’s database objects to be changed without interrupting the application’s availability by making the changes in the privacy of a new edition.


  • CREATE or REPLACE TYPE will allow FORCE option. The FORCE option can now be used in conjunction with the CREATE or REPLACE TYPE command.


  • New SQL*Plus command SET EXITCOMMIT specifies whether the default EXIT behavior is COMMIT or ROLLBACK.
    SET EXITC[OMMIT] {ON|OFF}


  • LISTAGG Analytic Function
    This function making very easy to aggregate strings. It also allows us to order the elements in the concatenated list.
    COLUMN employees FORMAT A50
    SQL> SELECT deptno, LISTAGG(ename, ‘,’) WITHIN GROUP (ORDER BY ename) AS employees FROM emp GROUP BY deptno;
    DEPTNO EMPLOYEES
    ———- ————————————————–
    10 CLARK,KING,MILLER
    20 ADAMS,FORD,JONES,SCOTT,SMITH
    30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD


  • Oracle Database 11g Release 2, provides the new PRECEDES keyword in trigger definition which allows trigger-upon-trigger dependencies.


  • Audit filename will be prefixed with the instance name and ends with a sequence number. For example:
    SID_ora_pid_seqNumber.aud or SID_ora_pid_seqNumber.xml
    An existing audit file is never appended.


  • From Oracle 11g R2, we can change audit table’s (SYS.AUD$ and SYS.FGA_LOG$) tablespace and we can periodically delete the audit trail records using DBMS_AUDIT_MGMT.
  • The initial segment creation for non partitioned tables and indexes can be delayed until data is first inserted into an object. Depending on the module usage, only a subset of the objects is really being used. With delayed segment creation, empty database objects do not consume any space, reducing the installation footprint and speeding up the installation.


  • Flashback Data Archive support for DDL.


  • In Oracle Database 11g Release 2 (11.2), support for the LZO compression algorithm on SecureFiles has been added. The new compression option is designated as COMPRESS LOW.

Fast decompression – LZO compression is 2 times faster than ZLIB. Fast compression – LZO compression is 3 times faster than ZLIB.

  • IGNORE_ROW_ON_DUPKEY_INDEX hint for INSERT Statement With INSERT INTO TARGET … SELECT … FROM SOURCE, a unique key for some to-be-inserted rows may collide with existing rows. The IGNORE_ROW_ON_DUPKEY_INDEX allows the collisions to be silently ignored and the non-colliding rows to be inserted.
  • Oracle Database Smart Flash Cache is a new feature, for Oracle Linux & Oracle Solaris, which increases the size of the database buffer cache without having to add RAM to host.
  • Oracle Database 11g Release 2 introduces two new SQL*Net parameters that can be used on for connection strings of individual clients. The first parameter is CONNECT_TIMEOUT, it specifies the timeout duration (in seconds) for a client to establish an Oracle Net connection to an Oracle database, this parameter overrides SQLNET.OUTBOUT_CONNECT_TIMEOUT in the SQLNET.ORA. The second parameter is RETRY_COUNT and it specifies the number of times an ADDRESS_LIST is traversed before the connection attempt is terminated.
  • Concurrent Statistics gathering feature is introduced in Oracle 11g release 2, which enables user to gather statistics on multiple tables in a schema, and multiple (sub)partitions within a table concurrently.

    ASM

    • ASM Configuration Assistant (ASMCA) is a new tool to install and configure ASM.


  • ASM Cluster File System (ACFS) provides support for files such as Oracle binaries, Clusterware binaries, report files, trace files, alert logs, external files, and other application datafiles. ACFS can be managed by ACFSUTIL, ASMCMD, OEM, ASMCA, SQL command interface.


  • ASM Dynamic Volume Manager (ADVM) provides volume management services and a standard device driver interface to its clients (ACFS, ext3, OCFS2 and third party files systems).


  • ACFS Snapshots are read-only on-line, space efficient, point in time copy of an ACFS file system. ACFS snapshots can be used to recover from inadvertent modification or deletion of files from a file system.


  • ASM can hold and manage OCR (Oracle Cluster Registry) file and voting file.


  • ASM diskgroups can be renamed, by using renamedg command.


  • From Oracle 11g R2, ASMCMD utility can do
    • ASMCMD Instance Management Commands – dsget, dsset, lsop, lspwusr, orapwusr, shutdown, spbackup,spcopy, spget, spmove, spset, startup.
    • Managing diskgroups (create, mount, alter, drop) through ASMCMD Disk Group Management Commands – chdg, chkdg, dropdg, iostat, lsattr, lsdg, lsdsk, lsod, md_backup,md_restore, mkdg, mount, offline, online, rebal, remap, setattr, umount.
    • User management and File access control through ASMCMD File Access Control Commands – chgrp, chmod, chown, groups, grpmod, lsgrp, lsusr, mkgrp, mkusr, passwd, rmgrp, rmusr.
    • Template management through ASMCMD Template Management Commands – chtmpl, lstmpl, mktmpl, rmtmpl.
    • Volume management through ASMCMD Volume Management Commands – volcreate, voldelete, voldisable, volenable, volinfo, volresize, volset, volstat.
    • We can execute OS commands at asmcmd by using !, in the same we do at SQL prompt.

Data Guard

  • Automatic Block Repair – Automatic block repair allows corrupt blocks on the primary database or physical standby database to be automatically repaired, as soon as they are detected, by transferring good blocks from the other destination.
  • The number of standby databases that a primary database can support is increased from 9 to 30 in this release.
  • RMAN duplicate standby from active database
    RMAN > duplicate target database for standby from active database;
  • Compressed table support in logical standby databases and Oracle LogMiner.
  • Archived log deletion policy enhancements – we can CONFIGURE an archived redo log deletion policy so that logs are eligible for deletion only after being applied on or transferred to (all) standby database destinations.
  • Increase in redo apply performance.
  • Heterogeneous Data Guard Configuration.


Tablespace Point-In-Time Recovery (TSPITR)

  • We have the ability to recover a dropped tablespace.
  • TSPITR can be repeated multiple times for the same tablespace. Previously, once a tablespace had been recovered to an earlier point-in-time, it could not be recovered to another earlier point-in-time.
  • DBMS_TTS.TRANSPORT_SET_CHECK is automatically run to ensure that TSPITR is successful.
  • AUXNAME is no longer used for recovery set datafiles.


Oracle Scheduler

  • E-mail Notification – Oracle Database 11g Release 2 (11.2) users can now get e-mail notifications on any job activity.
  • File Watcher – File watcher enables jobs to be triggered when a file arrives on a given machine.


RMAN
The following are new clauses and format options for the SET NEWNAME command:

  • A single SET NEWNAME command can be applied to all files in a database or tablespace.

SET NEWNAME FOR DATABASE TO format;
SET NEWNAME FOR TABLESPACE
tsname TO format;

  • New format identifiers are as follows:
    %U – Unique identifier. data_D-%d_I-%I_TS-%N_FNO-%f
    %b – UNIX base name of the original datafile name. For example, if the original datafile name was $ORACLE_HOME/data/tbs_01.f, then %b is tbs_01.f.
  • Archived log deletion policy enhancements – we can CONFIGURE an archived redo log deletion policy so that logs are eligible for deletion only after being applied on or transferred to (all) standby database destinations.

What’s New in Oracle 11g Release 1

Oracle 11g Release 1 (11.1.0) – July 2007

Oracle added about 482 new features in the Oracle Database 11g Release 1.

New Datatypes
The new datatypes brought in Oracle 11g are:

  • Binary XML type – up to 15 times faster over XML LOBs.
  • DICOM (Digital Imaging and Communications in Medicine) medical images.
  • 3D spatial support.
  • RFID tag datatypes.
New background processes

  • ACMS – Atomic Controlfile to Memory Server
  • DBRM – Database Resource Manager
  • DIA0 – Diagnosibility process 0
  • DIAG – Diagnosibility process
  • FBDA – Flashback Data Archiver
  • GTX0 – Global Transaction Process 0
  • KATE – Konductor (Conductor) of ASM Temporary Errands
  • MARK – Mark Allocation unit for Resync Koordinator (coordinator)
  • SMCO – Space Manager
  • VKTM – Virtual Keeper of TiMe process
  • W000 – Space Management Worker Processes
  • ABP – Autotask Background Process
SQL*Plus

  • SQL*Plus can show the BLOB/BFILE columns in select query.
  • The errors while executing a script/SQL can be logged on to a table (SPERRORLOG, by default).
    SQL> set errorlogging on —>> errors will be logged onto SPERRORLOG.
    SQL> set errorlogging on table scott.error_log —>> errors will be logged onto user defined table.
    SQL> set errorlogging on truncate —>> will truncate all the rows in the table.
    SQL> set errorlogging on identifier identifier-name —>> useful to query the logging table

SQL

  • Automatic SQL tuning with self-learning capabilities.
  • Tables can have virtual columns (calculated from other columns).
    SQL> CREATE TABLE TABLE-NAME ( … , virtual-col-name virtual-col-type GENERATED ALWAYS AS condition VIRTUAL);
  • Indexes on virtual columns(VC) and partitioning on virtual columns.
  • Fast “alter table … add column” with default values.
  • Online rebuilding of indexes with no pause on DML activity. Online table and index redefinition.
    SQL> alter index index-name rebuild online;
  • From 11g, tables with materialized view logs can be redefined online. Materialized view logs are considered one of the dependent objects.
  • Ability to mark a table as read only.
    SQL> alter table table-name read only;
  • From Oracle 11g, we can create a restore point for a specific SCN in the past or a past point in time.
    SQL> CREATE RESTORE POINT res_jun10 AS OF SCN 2340009; SQL> CREATE RESTORE POINT res_jun10 AS OF TIMESTAMP to_date(’01-04-2010 07:30′,’DD-MM-YYYY HH24:MI’);
  • New PIVOT (to create a crosstab report on any relational table) and UNPIVOT (to convert any crosstab report to be stored as a relational table) operations. Pivot can produce the output in text or XML.
  • Table compression occurs on all DML activities. The blocks will be compressed, not the rows.
    SQL> create table table-name … compress for all operations;
 

PL/SQL

  • Native compilation no longer requires a C-compiler. But plsql_code_type parameter should be NATIVE.
    SQL> alter session set plsql_code_type = native;
  • New SIMPLE_INTEGER datatype – subtype of PLS_INTEGER, always NOT NULL, wraps instead of overflows and is faster than PLS_INTEGER. Will be faster in native compilation.
  • SQL and PL/SQL result caching (in SGA).
  • can create triggers as disabled.
  • Can specify trigger firing order (FOLLOWS clause).
    SQL> create trigger T2 … follows T1;
  • Compound triggers – a trigger can be before statement, after statement, before row, after row and all in one.
  • DML triggers are up to 25% faster – in particular, row level triggers doing updates against other tables.
  • New CONTINUE statement – starts the next iteration of the loop.
  • Finer grained dependency tracking – when parent table undergone structural changes, child/dependent objects are not invalidated simply.
  • Dynamic SQL enhancements.
  • Ability to reference sequences directly(no need to select seq.nextval into :n from dual). We can use :n := seq.nextval;
  • Dynamic cursor can be converted to ref cursor and vice versa.
  • Starting with Oracle11g, a CLOB can be passed as an input parameter to EXECUTE IMMEDIATE.
  • Adaptive Cursors – if a cursor has bind variable, the database observes cursor for a while to see what type of values are passed to the variable and if execution plan needs recalculation. Adaptive cursors are activated and used automatically.

ASM

  • Support for rolling upgrades.
  • We can maintain version compatibilities at diskgroup level.
    SQL> alter diskgroup dg-name set attribute ‘compatible.rdbms’=’11.1’;
    SQL> alter diskgroup dg-name set attribute ‘compatible.asm’=’11.1’;
  • ASM drops disks and if they remain offline for more than 3.6 hours. The diskgroups default time limit is altered by changing the DISK_REPAIR_TIME parameter with a unit of minutes(M/m) or hours(H/h).
    SQL> alter diskgroup dg-name set attribute ‘disk_repair_time’=’4.5h’;
  • Automatic bad block detection and repair.
  • Supports variable extent(allocation unit) sizes. The total number of extents in shared pool will be significantly reduced and improved performance.
    SQL> create diskgroup … attribute ‘au_size’ = ‘number-of-bytes’;
  • New SYSASM role (like SYSDBA, SYSOPER) & OSASM OS group (like OSDBA, OSOPER) to manage ASM instance only. This will separate storage administration from database administration.
    $ sqlplus “/as sysasm” or $ asmcmd -a sysasm
  • ASM Preferred Mirror Read or Preferred Read Failure Groups – ASM_PREFERRED_READ_FAILURE_GROUPS parameter is set to the preferred failure groups for each node.
  • Faster Mirror Resync – Fast mirror resync after temporary connectivity lost.
  • We can drop a diskgroup forcefully.
    SQL> drop diskgroup dg-name force including contents;
  • Can mount the disk in restricted mode, to rebalance faster.
    SQL> alter diskgroup dg-name mount restricted;
  • New commands in ASMCMD.
    • cp – to copy between ASM and local or remote destination.
    • md_backup – to backup metadata.
    • md_restore – to restore metadata.
    • lsdsk – to list(check) disks.
    • remap – to repair a range of physical blocks on disk.
Data Pump

  • New options in Data Pump export.
    DATA_OPTIONS, ENCRYPTION, ENCRYPTION_ALGORITHM, ENCRYPTION_MODE, REMAP_DATA, REUSE_DUMPFILES, TRANSPORTABLE
  • New options in Data Pump import.
    DATA_OPTIONS, PARTITION_OPTIONS, REMAP_DATA, REMAP_TABLE, TRANSPORTABLE
  • New option in Data Pump export interactive mode – REUSE_DUMPFILES.
  • In Data Pump import, we can specify how the partitions should transform by using PARTITION_OPTIONS.
  • Dumpfile can be compressed. In Oracle 10g, only metadata can be compressed. From 11g, both data & metadata can be compressed. Dumpfile will be uncompressed automatically before importing.
  • Encryption: The dumpfile can be encrypted while creating. This encryption occurs on the entire dumpfile, not just on the encrypted columns as it was in the Oracle Database 10g.
  • Masking: when we import data from production to test or development instances, we have to make sure sensitive data such as credit card details, etc. are obfuscated/remapped (altered in such a way that they are not identifiable). From 11g, Data Pump enables us do that by creating a masking function and then using that during import.

RMAN

  • Multisection backups of same file – RMAN can backup or restore a single file in parallel by dividing the work among multiple channels. Each channel backs up one file section, which is a contiguous range of blocks. This speeds up overall backup and restore performance, and particularly for bigfile tablespaces, in which a datafile can be sized upwards of several hundred GB to TB’s.
  • Recovery will make use of flashback logs in FRA (Flash/Fast Recovery Area).
  • Fast Backup Compression – in addition to the Oracle Database 10g backup compression algorithm (BZIP2), RMAN now supports the ZLIB algorithm, which offers 40% better performance, with a trade-off of no more than 20% lower compression ratio, versus BZIP2.
    RMAN> configure compression algorithm ‘ZLIB’ ;
  • Will backup uncommitted undo only, not committed undo.
  • Data Recovery Advisor (DRA) – quickly identify the root cause of failures; auto fix or present recovery options to the DBA.
  • Archived Redo log failover – this feature enables RMAN to complete backups even when some archiving destinations having missing logs or contain logs with corrupted blocks where local archive log destination is configured along with FRA.
  • Virtual Private Catalog – a recovery catalog administrator can grant visibility of a subset of registered databases in the catalog to specific RMAN users.
    RMAN> grant catalog for database db-name to user-name;
  • Catalogs can be merged/moved/imported from one database to another.
  • New commands in RMAN
    • RMAN> list failure;
    • RMAN> list failure errnumber detail;
    • RMAN> advise failure;
    • RMAN> repair failure;
    • RMAN> repair failure preview;
    • RMAN> validate database; — checks for corrupted blocks
    • RMAN> create virtual catalog;

Partitioning

  • Partition advisor – figure out what partitions to create.
  • Automated partitioning by interval (new partitions are added automatically).
  • Automated reference partitioning by Parent/Child reference (as partitions are created, partitions are created in tables that reference them).
  • Partitioning by virtual columns.
  • New composite partitioning types: Range-Range, List-Range, List-Hash, List-List, Interval-Range, Interval-List and Interval-Interval.
  • System partitioning is introduced.
  • Support for transportable partitions (tablespace transport of single partition) – for moving partitions between different databases/operating systems.
  • Staleness checking in partitions – only outdated partitions will be refreshed when we run dbms_mview.refresh().

Compression

  • Support compression on INSERT, UPDATE and DELETE operations. 10g only supported compression for bulk data-loading operations.
  • Advanced compression allows for a 2-3 X compression rate of structured and unstructured data.
  • From Oracle 11g, we can compress individual partitions also.

Performance improvements

  • RAC – 70% faster (ADDM has a better global view of the RAC cluster).
  • Streams – 30-50% faster.
  • Optimizer stats collection – 10x faster.
  • OLAP (Online Analytic Processing) based materialized views for fast OLAP cube building. Cube-organized MView supports automatic query rewrite and automatic refresh of the cube.
  • SQL Result Cache – new memory area in SGA for storing SQL query results, PL/SQL function results and OCI call results. When we execute a query with the hint result_cache, the results are stored in the SQL Result Cache. Query results caching is 25% faster. The size of the cache is determined by result_cache_max_size, result_cache_max_result, result_cache_mode, result_cache_remote_expiration.
  • Invisible indexes – indexes will be ignored by the optimizer. Handy for testing without dropping. To make it visible, recreate it.
    SQL> alter index index-name invisible;
  • Oracle secure files – 5x faster than normal file systems.

Availability improvements

  • Ability to apply many patches on-line without downtime (RAC and single instance databases).
  • XA transactions spanning multiple servers.
  • Improved runtime connection load balancing.
  • Flashback Transaction/Oracle Total Recall.

Security improvements

  • Support for case sensitive and multi-byte passwords (disabled by setting SEC_CASE_SENSITIVE_LOGON parameter to FALSE).
  • Transparent Data Encryption – support for tablespace level encryption.
  • Hardware based master key protection.
  • Encrypt backups.
  • Kerberos authentication – strong passwords.
  • Add Multi-factor DBA controls with Data Vault.
  • New parameters have been added to enhance the default security of the database.
    * SEC_RETURN_SERVER_RELEASE_BANNER
    * SEC_PROTOCOL_ERROR_FURTHER_ACTION
    * SEC_PROTOCOL_ERROR_TRACE_ACTION
    * SEC_MAX_FAILED_FAILED_LOGIN_ATTEMPTS
    * SEC_DISABLE_OLDER_ORACLE_RPCS

Manageability improvements

  • New MEMORY_TARGET, MEMORY_MAX_TARGET parameters. When we set MEMORY_TARGET, Oracle will dynamically assign memory to SGA & PGA as and when needed i.e. MEMORY_TARGET=SGA_TARGET+PGA_AGGREGATE_TARGET. New views related this are v$memory_dynamic_components, v$memory_resize_ops.
  • From Oracle 11g, SID clause in “alter system reset” command is optional.
    SQL> alter system [SID=instance-name] reset parameter-name;
  • New DIAGNOSTIC_DEST parameter as replacement for BACKGROUND_DUMP_DEST, CORE_DUMP_DEST and USER_DUMP_DEST. It defaults to $ORACLE_BASE/diag/.
  • From 11g, we have two alert log files. One is the traditional alert_SID.log (in DIAGNOSTIC_DEST/trace) and the other one is a log.xml file (in DIAGNOSTIC_DEST/alert). The xml file gives a lot more information than the traditional alert log file. We can have logging information for DDL operations in the alert log files. If log.xml reaches 10MB size, it will be renamed and will create new alert log file. log.xml can be accessed from ADR command line.
    ADRCI> show alert
  • Logging information for DDL operations will be written into alert log files, is not enabled by default and we must change the new parameter to TRUE.
    SQL> ALTER SYSTEM SET enable_ddl_logging=TRUE SCOPE=BOTH;
  • Parameter(p) file & server parameter(sp) file can be created from memory.
    SQL> create pfile[=location] from memory;
    SQL> create spfile[=location] from memory;
  • From 11g, server parameter file (spfile) is in new format that is compliant with Oracle Hardware Assisted Resilient Data(HARD).
  • DDL wait option – Oracle will automatically wait for the specified time period during DDL operations and will try to run the DDL again.
    SQL> ALTER SYSTEM/SESSION SET DDL_LOCK_TIMEOUT = n;
  • We can define the statistics to be pending, which means newly gather statistics will not be published or used by the optimizer — giving us an opportunity to test the new statistics before we publish them.
  • From Oracle Database 11g, we can create extended statistics on
    (i) expressions of values, not only on columns
    (ii) on multiple columns (column group), not only on single column.
  • Table level control of CBO statistics refresh threshold.
    SQL> exec dbms_stats.set_table_prefs(’HR’, EMP’, ‘STALE_PERCENT’, ‘20′);
  • Flashback Data Archive – flashback will make use of flashback logs, explicitly created for that table, in FRA (Flash/Fast Recovery Area), will not use undo. Flashback data archives can be defined on any table/tablespace. Flashback data archives are written by a dedicated background process called FBDA so there is less impact on performance. Can be purged at regular intervals automatically.
  • Analytic Workspace Manager (AWM) – a tool to manage OLAP objects in the database.
  • Users with default passwords can be found in DBA_USERS_WITH_DEFPWD.
  • Hash value of the passwords in DBA_USERS (in ALL_USERS and USER_USERS) will be blank. If you want to see the value, query USER$.
  • Default value for audit_trail is DB, not NULL. By default some system privileges will be audited.
  • LogMiner can be accessed from Oracle Enterprise Manager.

Data Guard improvements

  • Oracle Active Data Guard – Standby databases can now simultaneously be in read and recovery mode – so use it for running reports 24×7.
  • Online upgrades: Test on standby and roll to primary.
  • Snapshot standby database – physical standby database can be temporarily converted into an updateable one called snapshot standby database.
  • Creation of physical standby is become easier.
  • From Oracle 11g, we can control archive log deletion by setting the log_auto_delete initialization parameter to TRUE.  The log_auto_delete parameter must be coupled with the log_auto_del_retention_target parameter to specify the number of minutes an archivelog is maintained until it is purged. Default is 24 hours (1440 minutes).
  • Incremental backup on physical readable physical standby.
  • Offload: Complete database and fast incremental backups.
  • Logical standby databases now support XML and CLOB datatypes as well as transparent data encryption.
  • We can compress the redo data that goes to the standby server, by setting compression=enable.
  • From Oracle 11g, logical standby provides support for DBMS_SCHEDULER.
  • When transferring redo data to standby, if the standby does not respond in time, the log transferring service will wait for specified timeout value (set by net_timeout=n) and then give up.
  • In Oracle 11g, block change tracking is now supported in the standby database.
  • New package and procedure, DBMS_DG.INITIATE_FS_FAILOVER, introduced to programmatically initiate a failover.

SecureFiles
SecureFiles provide faster access to unstructured data than normal file systems, provides the benefits of LOBs and external files. For example, write access to SecureFiles is faster than a standard Linux file system, while read access is about the same. SecureFiles can be encrypted for security, de-duplicated and compressed for more efficient storage, cached (or not) for faster access (or save the buffer cache space), and logged at several levels to reduce the mean time to recover (MTTR) after a crash.

create table table-name ( … lob-column lob-type [deduplicate] [compress high/low] [encrypt using ‘encryption-algorithm’] [cache/nocache] [logging/nologging] …) lob (lob-column) store as securefile …;

To create SecureFiles:
(i) The initialization parameter db_securefile should be set to PERMITTED (the default value).
(ii) The tablespace where we are creating the securefile should be Automatic Segment Space Management (ASSM) enabled (default mode in Oracle Database 11g).

Real Application Testing(RAT)
Real Application Testing (RAT) will make decision making easier in migration, upgradation, patching, initialization parameter changes, object changes, hardware replacements, and operating system changes and moving to RAC environment. RAT consists of two components:

  • Database Replay – capture production workload and replay on different (standby/test/development) environment. Capture the activities from source database in the form of capture files in capture directory. Transfer these files to target box. Replay the process on target database.
  • SQL Performance Analyzer (SPA) – identifies SQL execution plan changes and performance regressions. SPA allows us to get results of some specific SQL or entire SQL workload against various types of changes such as initialization parameter changes, optimizer statistics refresh, and database upgrades, and then produces a comparison report to help us assess their impact. Accessible through Oracle Enterprise Manager or dbms_sqlpa package.

Other features

  • Temporary tablespace or it’s tempfile can be shrinked, up to specified size.
    SQL> alter tablespace temp-tbs shrink space;
    SQL> alter tablespace temp-tbs shrink space keep n{K|M|G|T|P|E};
    SQL> alter tablespace temp-tbs shrink tempfile ‘…/temp03.dbf’ keep n{K|M|G|T|P|E};
    We can check free temp space in new view DBA_TEMP_FREE_SPACE.
  • From 11g, while creating global temporary tables, we can specify TEMPORARY tablespaces.
  • Online application upgrades and hot patching. Features based patching is also available.
  • Real-time SQL Monitoring, allows us to see the different metrics of the SQL being executed in real time. The stats are exposed through V$SQL_MONITOR, which is refreshed every second.
  • “duality” between SQL and XML – users can embed XML within PL/SQL and vice versa.
  • New binary XML datatype, a new XML index & better XQuery support.
  • Query rewriting will occur more frequently and for remote tables also.
  • Automatic Diagnostic Repository (ADR)- automated capture of fault diagnostics for faster fault resolution. The location of the files depends on DIAGNOSTIC_DEST parameter. This can be managed from Database control or command line. For command line, execute $ ./adrci
  • Repair advisors to guide DBAs through the fault diagnosis and resolution process.
  • SQL Developer is installed with the database server software (all editions). The Windows SQL*Plus GUI is deprecated.
  • APEX (Oracle Application Express), formerly known as HTML DB, shipped with the DB.
  • Checkers – DB Structure Integrity Checker, Data Block Integrity Checker, Redo Integrity Checker, Undo Segment Integrity Checker, Transaction Integrity Checker, Dictionary Integrity Checker.
  • 11g SQL Access Advisor provides recommendations with respect to the entire workload, including considering the cost of creation and maintaining access structure.
  • hangman Utility – hangman(Hang Manager) utility to detect database bottlenecks.
  • Health Monitor (HM) utility – Health Monitor utility is an automation of the dbms_repair corruption detection utility.
  • The dbms_stats package has several new procedures to aid in supplementing histogram data, and the state of these extended histograms can be seen in the user_tab_col_statistics view:
    dbms_stats.create_extended_stats
    dbms_stats.show_extended_stats_name
    dbms_stats.drop_extended_stats
  • New package DBMS_ADDM introduced in 11g.
  • Oracle 11g introduced server side connection pool called Database Resident Connection Pool (DRCP).

Desupported features
The following features are desupported/deprecated in Oracle Database 11g Release 1 (11.1.0):

  • Oracle export utility (exp). Imp is still supported for backwards compatibility.
  • Windows SQL*Plus GUI & iSQLPlus will not be shipped anymore. Use SQL Developer instead.
  • Oracle Enterprise Manager Java console.
  • copy command is deprecated.

Source:Internet

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

    • SYSAUX tablespace has been introduced as an auxiliary to SYSTEM, as LOCAL managed tablespace.
    • 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.
    • 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 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.
    • 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
    • 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>)
    •  
    • 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.
    •  
    • 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.
    •  
    • From Oracle 10g, the spool command can append to an existing one.
      SQL> spool result.log append
    •  
    • 10g allows us to save statements as appended to the files.
      SQL> Query1 ….
      SQL> save myscripts
      SQL> Query2 ….
      SQL> save myscripts append
    •  
    • 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

    • 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

Oracle wait events

db file sequential read => tune indexing, tune SQL (to do less I/O), tune disks, increase buffer cache. This event is indicative of disk contention on index reads. Make sure all objects are analyzed. Redistribute I/O across disks. The wait that comes from the physical side of the database. It related to memory starvation and non selective index use. Sequential read is an index read followed by table read because it is doing index lookups which tells exactly which block to go to.
db file scattered read => disk contention on full table scans. Add indexes, tune SQL, tune disks, refresh statistics, and create materialized view. caused due to full table scans may be because of insufficient indexes or unavailability of updated statistics.
db file parallel read => tune SQL, tune indexing, tune disk I/O, increase buffer cache. if you are doing a lot of partition activity then expect to see that wait even. it could be a table or index partition.
db file parallel write => if you are doing a lot of partition activity then expect to see that wait even. it could be a table or index partition.
db file single write => if you see this event than probably you have a lot of data files in your database.

control file sequential read
control file parallel write

log file sync => committing too often, archive log generation is more. Tune applications to commit less, tune disks where redo logs exist, try using nologging/unrecoverable options, log buffer could be too large.
log file switch completion => May need more log files per group.
log file parallel write => Deals with flushing out the redo log buffer to disk. Disks may be too slow or have an I/O bottleneck. Look for log file contention.
log buffer space => Increase LOG_BUFFER parameter or move log files to faster disks. Tune application, use NOLOGGING, and look for poor behavior that updates an entire row when only a few columns change.
log file switch (checkpoint incomplete) => May indicate excessive db files or slow IO subsystem.
log file switch (archiving needed)  => Indicates archive files are written too slowly.
redo buffer allocation retries => shows the number of times a user process waited for space in the redo log buffer.
redo log space wait time => shows cumulative time (in 10s of milliseconds) waited by all processes waiting for space in the log buffer.

buffer busy waits/ read by other session => Increase DB_CACHE_SIZE. Tune SQL, tune indexing, we often see this event along with full table scans, if the SQL is inserting data, consider increasing FREELISTS and/or INITRANS, if the waits are on segment header blocks, consider increasing extent sizes.
free buffer waits => insufficient buffers, process holding buffers too long or i/o subsystem is over loaded. Also check you db writes may be getting clogged up.
cache buffers lru chain => Freelist issues, hot blocks.
no free buffers => Insufficient buffers, dbwr contention.

latch free
latch: session allocation
latch: in memory undo latch => If excessive could be bug, check for your version, may have to turn off in memory undo.
latch: cache buffer chains => check hot objects.
latch: cache buffer handles => Freelist issues, hot blocks.

direct path write => You wont see them unless you are doing some appends or data loads.
direct path reads => could happen if you are doing a lot of parallel query activity.
direct path read temp or direct path write temp => this wait event shows Temp file activity (sort,hashes,temp tables, bitmap) check pga parameter or sort area or hash area parameters. You might want to increase them.

library cache load lock
library cache pin => if many sessions are waiting, tune shared pool, if few sessions are waiting, lock is session specific.
library cache lock => need to find the session holding the lock, look for DML manipulating an object being accessed, if the session is trying to recompile PL/SQL, look for other sessions executing the code.

undo segment extension => If excessive, tune undo.
wait for a undo record => Usually only during recovery of large transactions, look at turning off parallel undo recovery.

enque wait events => Look at V$ENQUEUE_STAT

SQL*Net message from client
SQL*Net message from dblink
SQL*Net more data from client
SQL*Net message to client
SQL*Net break/reset to client