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

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.