Why we have so many numbers (1,2,3,4) in 11.2.0.3.4 ?. Alright, today I’ll try to explain significance of these numbers and it’s relevance.

11.X .X. X.X
Major Database Release Number:
Major database release number, major new edition, contains significant new functionality. It represents a major new version of the software that contains significant new functionality.

X. 2. X. X
Database Maintenance Release Number:
Number increases when bug fixes or new features to existing programs become available.

X . X .O. X .X
Middleware Release Number:
Release level of Oracle Middleware. In case of 10g/9i it was Oracle Application Server Release Number.

X. X . X. 3. X
Component-Specific/Patch Release Number:
A Patch release contains fixes for serious bugs that cannot wait until the next maintenance releasefor example, component patch sets or interim releases.

X. X . X. X. 4
Platform-Specific Release Number:
Usually this is a patch set Updates.
Used to identify a particular emergency patch release of a software product on that operating system, it usually fixes or works around a particular, critical problem

Views:
SQL> select banner from v$version;
SQL> SELECT * FROM PRODUCT_COMPONENT_VERSION;

When Oracle allocates space to a segment (like a table or index), a group of contiguous free blocks, called an extent, is added to the segment. Metadata regarding extent allocation and unallocated extents are either stored in the data dictionary, or in the tablespace itself. Tablespaces that record extent allocation in the dictionary, are called dictionary managed tablespaces, and tablespaces that record extent allocation in the tablespace header, are called locally managed tablespaces.

SQL> select tablespace_name, extent_management, allocation_type from dba_tablespaces;

TABLESPACE_NAME                EXTENT_MAN ALLOCATIO
—————————— ———- ———
SYSTEM                         DICTIONARY USER
SYS_UNDOTS                     LOCAL      SYSTEM
TEMP                           LOCAL      UNIFORM

Oracle use the data dictionary (tables in the SYS schema) to track allocated and free extents for tablespaces that is in “dictionary managed” mode. Free space is recorded in the SYS.FET$ table, and used space in the SYS.UET$ table.

SQL> CREATE TABLESPACE etr1 DATAFILE ‘/oradata/etr1_01.dbf’ SIZE 80M
EXTENT MANAGEMENT DICTIONARY
Using LMT, each tablespace manages it’s own free and used space within a bitmap structure stored in one of the tablespace’s data files.

SQL> CREATE TABLESPACE etr2 DATAFILE ‘/oradata/etr2_01.dbf’ SIZE 80M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

Advantages in using Locally Managed Tablespaces Over Dictionary Managed Tablespaces

• Because locally managed tablespaces do not record free space in data dictionary, it reduces contention on these tables.
• Local management of extents automatically tracks adjacent free space, eliminating the need to coalesce free extents.
• Sizes of extents that are managed locally can be determined automatically by the system. Alternatively, all extents can have the same size in a locally managed tablespace.
• Reduced fragmentation

Differences:

1. by default datafiles and flash recovery options are located one level beloe the ORACLE BASE location but in 10g it is used to be saved under oracle home directory.

2. ADR (Automatic Diagnostic Repository) – is a single directory location for all error and trace data in the database. disgnostic directories such as bdump, cdump and udump is replaced with this option in 11g.

3. Deprecated: iSQL*Plus, Oracle Enterprise Manager Java Console.

4. New Paramaters: Memory_max_target –> sets the maximum value for Memory_Target parameter.
SEC_CASE_SENSITIVE_LOGON: enable or disable password case sensitivity Default: True.
SEC_MAX_FAILED_LOGIN_ATTEMPTS: drops the connection after specified number of login attempts     fail for any user (Default – 10)

5. Oracle SQL Developer.

6. Oracle Database Vault: enabled you to secure business data even from DBA’s.

7. Oracle Wrehouse Builder: BI tool.

8. New privelage SYSASM – Should be granted to user’s who perform ASM related tasks.
The CONNECT command in Oracle 11g SQL*Plus is enhanced to allow connecting as ASM administrator.
CONN[ECT] [{logon | / } [AS {SYSOPER | SYSDBA | SYSASM}]]

SYSASM Privilege and OSASM Group
This feature introduces a new SYSASM privilege that is specifically intended for performing ASM administration tasks.

9. ‘Connect‘ role has now only create CREATE SESSION provilege.

Oracle 9i

SQL> SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = ‘CONNECT’;

GRANTEE                        PRIVILEGE                                ADM
—————————— —————————————- —
CONNECT                        CREATE VIEW                              NO
CONNECT                        CREATE TABLE                             NO
CONNECT                        ALTER SESSION                            NO
CONNECT                        CREATE CLUSTER                           NO
CONNECT                        CREATE SESSION                           NO
CONNECT                        CREATE SYNONYM                           NO
CONNECT                        CREATE SEQUENCE                          NO
CONNECT                        CREATE DATABASE LINK                     NO
Oracle 11g/10g

SQL> SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = ‘CONNECT’;

GRANTEE                        PRIVILEGE                                ADM
—————————— —————————————- —
CONNECT                        CREATE SESSION                           NO

For security consideration, Oracle change right scheme of CONNECT in oracle 11g (The same was available in 10g as well) 

10. Use ALTER FLASHBACK command to alter a flashback archive object. Below are examples of using this command:
ALTER FLASHBACK ARCHIVE myflash SET DEFAULT;

11. Read-Only Tables
In Oracle 11g, you can set a table to be read only, i.e. users can only query from the table but no DML statement is allowed on     the table. Following are the commands to achieve this:
ALTER TABLE employees READ ONLY;
ALTER TABLE employees READ WRITE;

12. Shrinking Temporary Tablespaces and Tempfiles
In Oracle 11g, you can shrink temporary tablespaces and tempfiles. Following are the commands to achieve that:
ALTER TABLESPACE temp SHRINK SPACE KEEP 100M; — the KEEP keyword is optional.
ALTER TABLESPACE temp SHRINK TEMPFILE ‘/u01/app/oracle/oradata/ora11g/temp01.dbf’;

13. Creating an Initialization Parameter File from Memory
In Oracle 11g, you can create a pfile or spfile from the current values of the initialization parameters (active in the  instance). Following is the code example to do so:
CREATE PFILE FROM MEMORY;
CREATE SPFILE FROM MEMORY;

14. Restore Point Enhancements
Creating Restore Point “as of” an SCN or a Timestamp
With Oracle 11g, you can create a restore point for a specific SCN in the past or a past point in time.
CREATE RESTORE POINT res_jun08 AS OF SCN 2340009;

15. When a client process or thread connects to the database, the connection is established between the client and the connection broker (it is a new background process named as connection monitor CMON) in Oracle database.

16. SQL*Plus Error Logging
In Oracle 11g SQL*Plus, the new command SET ERRORLOGGIN can be used to store all errors resulting from executed SQL, PL/SQL and     SQL*Plus commands in special error logging table (by default it is SPERRORLOG).
Following are the commands to enable and disable error logging in SQL*Plus:
SQL>SHOW ERRORLOGGING
SQL>SET ERRORLOGGIN ON
SQL>SET ERRORLOGGIN OFF

The SET ERRORLOGGIN ON command creates the error log table. To view the error stored in the table, use a query like the following:
SELECT USERNAME, STATEMENT, MESSAGE
FROM SPERRORLOG;

17. Oracle Database 11g has new option named as Oracle Advanced Table Compression option which aims at reducing space occupied by     data for both OLTP and warehouse databases.
<<<Using Advanced Compression Option in Oracle 11g requires a separate license from Oracle.>>>
Compression in RMAN Backups
The Oracle Advanced Compression option reduces the compression ratio in RMAN backup (by 20%) and increases the backup performance (by 40%) than it does in Oracle 10g.

18. Enhanced TRUNCATE Statement
DROP ALL STORAGE is a new option added to TRUNCATE statement in Oracle 11g R2. It drops the whole segment. In addition to the     TRUNCATE TABLE statement, DROP ALL STORAGE also applies to the ALTER TABLE TRUNCATE (SUB)PARTITION statement.
TRUNCATE TABLE emp DROP ALL STORAGE;

19. Default Retention of AWR Snapshots Changed
By default, Oracle Database 11g will now retain eight days of AWR snapshot information (as opposed to seven in Oracle 10g).

20. Stronger Password Hash Algorithm
In Oracle Database 11g, the SHA-1 standard became the new algorithm for password hashing.

21. Case-Sensitive Passwords
Oracle 11g introduces case-sensitive passwords for databases created with the default Oracle Database 11g enhanced security.
The SEC_CASE_SENTITIVE_LOGON parameter must be set to TRUE to enable case-sensitive database passwords.
alter system set SEC_CASE_SENSITIVE_LOGON = TRUE;

22. Enhanced Block Media Recovery
In Oracle Database 11g, there is a new command to perform block media recovery, named the recover … block command replacing the old blockrecover command. The new command is more efficient since because it searches the flashback logs for older uncorrupted     versions of the corrupt blocks. This requires the database to work in archivelog mode and has the Database Flashback enabled.
Following are examples of using the new command:
RECOVER DATAFILE 2 BLOCK 24 DATAFILE 4 BLOCK 10;
RECOVER DATAFILE 2 BLOCK 24 DATAFILE 4 BLOCK 10 FROM TAG = sundaynight;
RECOVER DATAFILE 2 BLOCK 24 DATAFILE 4 BLOCK 10 FROM BACKUPSET = 11;

23. New RMAN Configuration Parameters
The COMPRESSION ALGORITHM and ARCHIVELOG DELETION POLICY parameters are new RMAN configuration parameters in the Oracle Database 11g.

You can use RMAN to create a persistent configuration that controls when archived redo logs are eligible for deletion from disk or tape. This deletion policy applies to all archiving destinations, including the flash recovery area. When
Following are examples:

— archived redo logs are eligible for deletion when there are at least two backups of them on the tape
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO SBT;

24. VALIDATE Command
You can use the new command VALIDATE to manually check for physical and logical corruptions in datafiles, backup sets, and even     individual data blocks. The command by default checks for physical corruption. You can optionally specify CHECK LOGICAL.     Corrupted blocks are reported in V$DATABASE_BLOCK_CORRUPTION.
Following are examples of some VALIDATE command options:
validate [CHECK LOGICAL] database;
validate SKIP INACCESSIBLE database;
validate copy of database;
validate tablespace hr_tbs;
validate copy of tablespace hr_tbs;
validate backupset 17,18;
validate datafile 3 block 24;

25. Active Database Duplication
In Oracle Database 11g, you can directly duplicate a database over the network without having to back up and provide the source  database files. This direct database duplication is called active database duplication. It can be done either with Database  Control or through RMAN.

Create a password file in the destination server with the same SYSDBA password as the source database. You can create the password file manually or by specifying the PASSWORD FILE option on the DUPLICATE command.

— use PASSWORD FILE option
RMAN>DUPLICATE TARGET DATABASE … PASSWORD FILE …

— duplicating a database to a remote host with the same directory structure
DUPLICATE TARGET DATABASE
TO dupdb
PASSWORD FILE — RMAN should duplicate the password file from the source
FROM ACTIVE DATABASE
SPFILE
NOFILENAMECHECK;

26. Data Pump Utilities
Compression Enhancement
In Oracle Database 11g, Oracle provides the mechanism to compress both data and metadata during the extract operation. The     available options for the COMPRESSION parameter are as follows:
compression={all | data_only | metadata_only | none}

27. Reusing a Dump File
In Oracle 11g data pump export utility, the new parameter REUSE_DUMPFILES enables you to overwrite a preexisting dump file.
Following is an example:
expdp hr DIRECTORY=dp_dir DUMPFILE=hr.dmp TABLES=employees REUSE_DUMPFILES=y

What are SYS and SYSTEM users ?

SYS:

•automatically created when Oracle database is installed
•automatically granted the DBA role
•has a default password: CHANGE_ON_INSTALL
•owns the base tables and views for the database data dictionary

SYSTEM:

•automatically created when Oracle database is installed
•automatically granted the DBA role
•has a default password: MANAGER (make sure you change it)

When you create an Oracle Database, the user SYSTEM is also automatically created and granted the DBA role.
The SYSTEM username is used to create additional tables and views that display administrative information, and internal tables and views used by various Oracle Database options and tools. Never use the SYSTEM schema to store tables of interest to non-administrative users.

What are SYSDBA & SYSOPER Privileges?

SYSDBA role permissions:

CREATE DATABASE
CREATE SPFILE
STARTUP and SHUTDOWN
ALTER DATABASE: open, mount, back up, or change character set.
ARCHIVELOG and RECOVERY
Includes the RESTRICTED SESSION privilege

SYSOPER role permissions:

CREATE SPFILE
STARTUP and SHUTDOWN
ALTER DATABASE: open, mount, back up
ARCHIVELOG and RECOVERY
Includes the RESTRICTED SESSION privilege